2015-05-10
3275
Применение в расчетах статистических, математических, логических, текстовых функций, функций даты и времени.
Цель занятия: Получение навыков по использованию в расчетах статистических, математических, финансовых, логических, текстовых, функций даты и времени. Закрепить умения по вводу простых и сложных формул с использованием арифметических операторов, встроенных функций и ссылок на ячейки.
Основная литература:
Уокенбах, Джон. Microsoft Excel 2010. Библия пользователя.: Пер.с англ. – М.: ООО «И.Д.Вильямс», 2011.-312 с.: ил. – Парал.тит.англ.
Дополнительная литература:
Волков В.Б. Понятный самоучитель Excel 2010. – СПб.:Питер, 2010.-256с.:ил.
Кулешова О.В., Центр Компьютерного обучения «Специалист», Microsoft Excel 2010. Анализ и визуализация данных. Решения практических задач. Методическое пособие, 2012.
Ход работы
Задание 1. Работа с математическими функциями (Лист 1).
1. Найти произведение чисел: 5,15,16,80,26,10,87,25,36,198. (Функция ПРОИЗВЕД). Числа расположите в диапазоне А1:А10, результат – в А11.
2. Найти значение выражения: 
.
3. Вычислить значение выражения: 
. (Функция СУММКВ).
4. Как будут выглядеть числа 14587, 258, 4785, 26 преобразованные в римские числа? (функция РИМСКОЕ).
5. Перемножьте матрицы: 
и 
. (функция МУМНОЖ).
Задание 2. Работа с логическими функциями (Лист 2 ).
1. Известен возраст двух человек – a, b. Определите, кто из них старше. (Функция ЕСЛИ).
2. В одну из ячеек будет введено число. В другой ячейке получить ответ на вопрос, является ли введенное число отрицательным.
3. В одну из ячейку будет введен год рождения первого человека, в другую ячейку — второго человека (значения годов рождения не равны между собой). Необходимо получить ответ на вопрос, кто старше — первый человек или второй.
4. Оформить лист для расчета значения у при заданном значении х:
Значение х должно вводиться в одну из ячеек.
5. Торговый агент получает вознаграждение в размере некоторой доли от суммы совершенной сделки: если объем сделки до 5000 руб., то в размере 5%; если выше — 7%.
6. В трех ячейках записаны числа. Если все они не нулевые, то вывести в какой-то четвертой ячейке 1, в противном случае — 0. Использовать сложное условие (функции и, или, НЕ).
Задание 3. Работа с текстовым функциями.
ДЛСТР возвращает количество знаков в текстовой строке.
ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста.
ЛЕВСИМВ возвращает указанное число знаков с начала текстовой строки.
Функция НАЙТИ находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста.
Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке.
ПОИСК Возвращает позицию первого вхождения знака или текстовой строки при поиске слева направо, начиная с нач_позиция.
ПРАВСИМВ возвращает заданное число последних знаков текстовой строки.
ПРОПИСН Делает все буквы в тексте прописными.
ПСТР возвращает указанное число знаков из текстовой строки, начиная с указанной позиции.
СТРОЧН Преобразует знаки в текстовой строке из верхнего регистра в нижний.
СЦЕПИТЬ Объединяет несколько текстовых строк в одну.
1. В ячейку вводится слово из 8 букв. Поменять местами его половины (соответствующее слово получить в ячейке ВЗ).
2. 
В ячейке записано некоторое слово, в котором имеются буквы "а". Найти номер позиции, которую занимает первая такая буква в слове.
3. В ячейке (рис.) записаны два слова, разделенных одним пробелом (начальных пробелов нет). Получить первое и второе слово. Решение оформить в следующем виде.
4. 
В ячейке получить текст, состоящий из фамилии и инициалов в виде Иванов Н.И. (рис.)
5. В ячейке записано некоторое слово, в котором имеются идущие подряд две буквы "н". Найти номер позиции, с которой начинается первое из сочетаний этих букв.
Задание 4. Работа с функциями даты и времени (Лист 4).
1. Заполните предложенную таблицу, используя функции:
ДЕНЬ Возвращает день в дате, заданной в числовом формате.
МЕСЯЦ Возвращает месяц в дате, заданной в числовом формате.
ГОД Возвращает год, соответствующий аргументу дата_в_числовом_формате.
2. По дате, указанной в ячейке, определить номер дня недели, на который приходилась эта дата (понедельник — 1, вторник — 2,..., воскресенье — 7). Определите день недели даты вашего рождения (Функция ДЕНЬНЕД).
3. Определить количество рабочих дней в период с 01.06.2010 по 28.08.2010.
4. Определите свой возраст в днях и неделях.
5. Определить стаж работы.
| Ячейка | Формула | Формат вывода | Описание |
| С4 | =СЕГОДНЯ()-B4+1 | ГГ | Возраст |
| Е4 | =ГОД(СЕГОДНЯ()-D4)-1900+МЕСЯЦ(СЕГОДНЯ()-D4)/12 | #0,00 | Стаж работы |
Задание 5. Работа со статистическими функциями (Лист 5).
1. Даны 20 чисел. Подготовить лист для определения количества чисел, больших некоторого значения, которое будет задаваться в отдельной ячейке. (функция СЧЕТЕСЛИ)
2. На листе оформите сведения о дате рождения (в формате Дата) 10 учащихся группы. Определите количество учащихся, которые родились после некоторой даты, указанной под таблицей.
3. Даны 20 чисел (среди которых есть отрицательные). Подготовить лист для определения количества чисел, больших суммы всех чисел.
Задание 6. Работа с финансовыми функциями (Лист 6).
При задании аргументов финансовых функций следует помнить следующее. Все аргументы, означающие расходы денежных средств (например, ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления (например, дивиденды) - положительными числами. Все даты как аргументы функции имеют числовой формат. Для логических аргументов используются константы ИСТИНА или ЛОЖЬ, ли функции категории Дополнительные
1. Рассчитать 20-летнюю ипотечную ссуду со ставкой 10% годовых при начальном взносе 25% и ежемесячной (ежегодной) выплате с помощью функции ППЛАТ.
2. Определить, какая сумма окажется на счете, если вклад размером 1000000 руб. положен под 8 % годовых на 10 лет, а проценты начисляются ежеквартально. (функция БС (ставка/4; кпер*4;;-пс)).
3. Какая сумма должна быть выплачена, если четыре года назад была выдана ссуда 200000 руб. под 20 % годовых с ежемесячным начислением процентов.
4. Сколько лет потребуется, чтобы платежи размером 1000000 руб. в конце каждого года достигли значения 10,897 млн.руб., если ставка процента 14,5 %?
5. Фонд размером 21 млн.руб. был сформирован за два года за счет отчислений по 770000 руб. в начале каждого месяца. Определить годовую ставку процента.
6. Рассчитать будущую стоимость облигации номиналом 200000 руб., выпущенной на 8 лет, если в первые три года проценты начисляются по ставке 18 %, а в остальные четыре года по ставке 21 %.
7. Какую сумму необходимо положить на депозит под 20 % годовых, чтобы через 3 года получить 90 млн. руб. при ежеквартальном начислении процентов?
8. Капитальные затраты по проекту составляют 570 млн. руб., и ожидается, что его реализация принесет следующие доходы за три года: 270, 330, 290 млн. руб. соответственно. Издержки привлечения капитала равны 17 %. Определить чистую текущую стоимость проекта.
Задание 7. Дан прямоугольный параллелепипед со сторонами а, Ь, с. Вычислить:
• объем V = abc;
• площадь поверхности S = 2(ab+bc+ac);
• длину диагонали 
;
• угол между диагональю и плоскостью основания 
;
• угол между диагональю и боковым ребром 
;
• объем шара, диаметром которого является диагональ 
,
Задание 8. В правильной четырехугольной пирамиде заданы: длина стороны основания а и высота h. Вычислить:
• объем 
• угол наклона бокового ребра к плоскости основания 
• длину бокового ребра 
;
• радиус описанного около пирамиды шара 
• угол наклона боковой грани к основанию 
• радиус вписанного в пирамиду шара 
;
• площадь полной поверхности пирамиды 
Задание 9. Сформировать и заполнить накопительную ведомость по переоценке основных средств производства по форме, которая приводится ниже:
Формулы для расчетов:
ОС = БС - ИО,
ВПС = БС * К,
ВОС = ОС * К,
где К - коэффициент, равный: 1) 3,3 - если БС меньше либо равен 650 млн. руб.; 2) 4,2 - если БС больше 650 млн. руб., но меньше 1000 млн. руб.; 3) 5,1 -если БС равен 1000 млн.руб. или более.
Отформатировать полученные в таблице результаты.
Задание 10. Сформировать и заполнить отчетную ведомость о результатах работы сети компьютерных клубов по форме, которая приводится ниже:
Для вычисления в столбце «Место» используется функция РАНГ.
Отформатировать полученные в таблице результаты.
Задание 11. Сформировать на рабочем листе ведомость «Расчет заработной платы работников научно-производственного отдела Альфа»
При расчетах в ведомости учитывать:
а) k, Надбавка за стаж. Итого, Процент налога, Удержать, Выдать - вычисляются с помощью соответствующих формул;
б) коэффициент k присваивается из следующего расчета: 0,1 - отработано до 5 лет ключительно, 0,2 - от 5 до 10 лет включительно, 0,25 — от 10 до 15 лет включительно, 0,3
свыше 15 лет;
в) Надбавка за стаж - денежный эквивалент за стаж работы;
г) Итого —тарифная ставка с учетом стажа;
д) Процент налога - учитывает, что: 2 % - начисление (по Итого) составляет до 400000 включительно, 10 % - от 400000 до 550000 включительно, 20 % - от 550000 до 700000 включительно, 35 % - превышающие 700000.
е) Удержать - денежный эквивалент налогов;
ж) Выдать - сумма к выдаче: Итого без Удержать.
Настроить условное форматирование так, чтобы стаж работы - до 5 лет - данные были представлены желтым цветом, от 5 до 10 - синим, от 10 до 15 - зеленым свыше 15-красным.
