4.4. Выполните задания для самостоятельной работы (по выбору):
5. Выполните задания с использованием функций проверки свойств и значений согласно
варианту:
6. Выполните задания с использованием инженерных функций согласно варианту:
7. Выполните задания с использованием функций ссылок и массивов:
7.1. Поменяйте ориентацию массива произвольного размера, используя функцию
ТРАНСП.
7.2. Два спортсмена участвовали в соревнованиях по 10-ти видам спорта и набрали разное
количество баллов. Определите фамилию спортсмена, показавшего лучший результат.
Используйте функции ПОИСКПОЗ и ИНДЕКС.
7.3. Фирма имеет два магазина. Известен доход каждого магазина за каждый месяц в году.
Определите название месяца, в котором доход был минимальный. Используйте функцию
ИНДЕКС либо ГПР.
7.4. Выполните задания для самостоятельной работы (по выбору)
8. Выполните задания с использованием финансовых функций:
8.1. Дана ссуда в n тыс. рублей на год под ставку p годовых с ежеквартальными начислениями (табл. 7.14). Определите сумму конечного платежа. За базовый период примите квартал. Используйте функцию БС, возвращающую будущую стоимость инвестиции на основе постоянных периодических платежей и постоянной процентной ставки:
8.2 На счет в банке вносится сумма n тыс. долларов в течение k лет равными долями в конце каждого месяца. Годовая ставка p%. Определите, какая сумма будет на счете через k лет. Используйте функцию БС, возвращающую будущую стоимость инвестиции на основе постоянных периодических платежей и постоянной процентной ставки:
8.3. Ссуда n тыс. долларов, выданная под p% годовых, погашается два раза в год равными платежами по k долларов. Рассчитайте срок, за который будет погашена ссуда, используя функцию КПЕР, возвращающую общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки:
8.4. Выполните задания для самостоятельной работы (по выбору):
Порядок выполнения заданий
1.1
1.1 Запустила MS Excel (Пуск/Все программы/Microsoft Excel);
1.2 Заполнила данными ячейки:
1.3 Ввела формулу =(B2-2)/5+(2+B2/B3*B3+3),заменив значения х и у согласно условию, (Вставить функцию/Математические/Ок);
1.4 Получила результат:
1.4 Сохранила документ (Файл/Сохранить…).
1.2
1.1 Заполнила данными ячейки:
1.2 Ввела формулу и заменила значения х и у согласно условию =(1+G2)/4*G3 (Вставить функцию/Математические/Ок):
1.3 Исправила формулу, прибавив к знаменателю 2 =(1+G2)/4*G3+2 (Вставить функцию/Математические/Ок):
1.4 Сохранила документ (Файл/Сохранить…).
1.3
1.1 Ввела данные в ячейки в соответствии с вариантом, где n – цена, k – количество тыс. рублей, до которого следует округлить n:
1.2 Округлила цену, используя формулу =ОКРУГЛТ(J2;J3) (Вставить функцию/ Математические/Ок):
1.3 Сохранила документ (Файл/Сохранить…).
1.4
1.1 Ввела данные для задания 3 в ячейки в соответствии с вариантом;
1.2 Используя функцию ЧЕТН округлила указанное в условии число до ближайшего целого =ЧЁТН(M2) (Вставить функцию/Математические/Ок):
1.3 Сохранила документ (Файл/Сохранить…).
2.1
1.1 Продолжила работу в MS Excel;
1.2 Ввела данные числа часов, число минут, число секунд в разные ячейки;
1.3 Преобразовала заданные часы, минуты, секунды в число в коде времени используя функцию ВРЕМЯ и подсчитала заданное время =ВРЕМЯ(A10;B10;C10) (Вставить функцию/Дата и время/Ок):
1.4 Сохранила документ (Файл/Сохранить…).
2.2
1.1 Ввела текущую дату и время с помощью функции ТДАТА (Вставить функцию/Дата и время/Ок);
1.2 Применив функцию СЕКУНДЫ, МИНУТЫ, ЧАС, из числового формата даты и времени, вернула только секунды, минуты, часы в разных ячейках соответственно
=СЕКУНДЫ(N9) , =МИНУТЫ(N9) , =ЧАС(N9)
(Вставить функцию/Дата и время/Ок):
2.3
1.1 Ввела текущую дату и время с помощью функции ТДАТА (Вставить функцию/Дата и время/Ок);
1.2 Определила, каким днем недели является текущая дата, используя функцию ДЕНЬНЕД со 2 типом отсчёта недели =ДЕНЬНЕД(Q9;2) (с Пн=1 до Вс=7):
1.3 Сохранила документ (Файл/Сохранить).
2.4
1.1 Ввела данные для задания 4;
1.2 Ввела текущую дату в ячейку, используя функцию ТДАТА (Вставить функцию/Дата и время/Ок);
1.3 Использовала функцию ДАТАМЕС для нахождения порядкового номера даты, отстоящей на 5 месяцев вперёд от текущей даты =ДАТАМЕС(S9;5) (Вставить функцию/Дата и время/Ок);
1.4 Определила, какой день недели, будет через 5 месяцев, используя функцию ДЕНЬНЕД со 2 типом отсчёта недели (с Пн=1 до Вс=7) =ДЕНЬНЕД(S10;2):
1.5 Сохранила документ (Файл/Сохранить).
3.1
1.1 Продолжила работу в MS Excel;
1.2 Ввела список чисел и их значений в ячейки в соответствии с вариантом;
1.3 Определила ранг числа k с 1 порядком с помощью функции РАНГ: =РАНГ(6;F16;1) (Вставить функцию/Статистические/Ок):
1.4 Определила количество чисел больше k, используя функцию СЧЁТЕСЛИ с критерием >6: =СЧЁТЕСЛИ(A16:F16;">6") (Вставить функцию/Статистические/Ок):
1.5 Сохранила документ (Файл/Сохранить).
3.2
1.1 Ввела значения таблицы, в которой записаны оценки каждого из 20 студентов группы, полученные в сессию на экзаменах по пяти предметам;
1.2 Определила среднюю оценку, полученную каждым студентом, используя функцию СРЗНАЧ: =СРЗНАЧ(N18:R18) (Вставить функцию/Статистические/Ок):
1.3 Определила величину средней оценки за сессию, используя функцию СРЗНАЧ: =СРЗНАЧ(N18:R37) (Вставить функцию/Статистические/Ок):
1.4 Определила наименьшую и наибольшую оценку по третьему предмету из всех полученных оценок с помощью функций НАИМЕНЬШИЙ: =НАИМЕНЬШИЙ(P18:P37;1), НАИБОЛЬШИЙ: =НАИБОЛЬШИЙ(P18:P37;1) с k=1
(Вставить функцию/Статистические/Ок):
1.5 Сохранила документ (Файл/Сохранить…).
3.3
1.1 Заполнила ячейки согласно варианту, n=16, задала размеры деталей:
1.2 Определила среднее значение измеренного размера детали =СРЗНАЧ(E23:E38) (Вставить функцию/Статистические/Ок):
1.3 Определила среднее отклонение размеров от величины, найденной в пункте 1.2, используя функцию СРОТКЛ: =СРОТКЛ(E23:E38;B23)
(Вставить функцию/Статистические/Ок):
1.4 Сохранила документ (Файл/Сохранить…).
3.4
1.1 Подготовила данные для задания 4, а именно: вступительные экзамены, число абитуриентов и ячейки для средней оценки:
1.2 Посчитала среднюю оценку по каждому из трёх вступительных экзаменов =СРЗНАЧ(V24:V38) , =СРЗНАЧ(W24:W38) , =СРЗНАЧ(X24:X38) (Вставить функцию/Статистические/Ок):
1.3 Сохранила документ (Файл/Сохранить…).
4.1
1.1 Продолжила работу в MS Excel;
1.2 В различные ячейки рабочего листа MS Excel занесла фамилию, имя и отчество
человека (Иовчук Андрей Сергеевич);
1.3 Для формирования строки, содержащей только фамилию и инициалы, нашла инициалы с помощью функции ЛЕВСИМВ: =ЛЕВСИМВ(C42;1) , =ЛЕВСИМВ(A43;1), где количество знаков, которые нужно извлечь, равно 1 (Вставить функцию/Текстовые/Ок):
1.4 Получив только инициалы, сцепила их с фамилией при помощь функции СЦЕПИТЬ: =СЦЕПИТЬ(A41;E42;E43) (Вставить функцию/Текстовые/Ок):
1.5 Сохранила документ (Файл/Сохранить как…).
4.2
1.1 Ввела в ячейки данные “Поле доход” и “Поле прибыль”;
1.2 Использовав функцию ЗАМЕНИТЬ, заменила “Поле доход” на “Поле прибыль” =ЗАМЕНИТЬ("Поле доход";1;10;N41) (Вставить функцию/Текстовые/Ок):
1.3 Сохранить документ (Файл/Сохранить…).
4.3
1.1 Ввела в одну ячейку число;
1.2 Преобразовала его в рубли с помощью функции РУБЛЬ, используя денежный формат =РУБЛЬ(P41;0) (Вставить функцию/Текстовые/Ок):
1.3 Сохранила документ (Файл/Сохранить как…).
4.4
1.1 Подготовила данные для задания 9, введя 2 различные фразы;
1.2 Проверила их идентичность, используя функцию СОВПАД: =СОВПАД(H41;I41) (Вставить функцию/Текстовые/Ок):
1.3 В результате получила ответ ЛОЖЬ, так как хоть фразы и имеют одинаковые части, они не идентичны;
1.4 Сохранила документ (Файл/Сохранить…).
5
1.1 Продолжила работу в MS Excel;
1.2 Подготовила данные для задания 3, введя в ячейку слово “Доход”;
1.3 Определила, является ли значение числом, используйте функцию ЕЧИСЛО: =ЕЧИСЛО(A46) (Вставить функцию/Проверка свойств и значений/Ок):
1.4 Функция вернула значение ЛОЖЬ, значит, “Доход” не число;
1.5 Сохранила документ (Файл/Сохранить…).
6
1.1 Продолжила работу в MS Excel;
1.2 Ввела десятичное число 200 согласно варианту;
1.3 Преобразовала десятичное число в шестнадцатиричное (см. рис. 1),
Рис. 1
используя функцию ДЕС.В.ШЕСТН. с разрядностью 4 =ДЕС.В.ШЕСТН(C46;4) (Вставить функцию/Инженерные/Ок):
1.4 Сохранила документ (Файл/Cохранить…).
7.1
1.1 Ввела массив произвольного размера в ячейки;
1.2 Поменяла ориентацию массива, используя функцию ТРАНСП: =ТРАНСП(A50:C50)
(Вставить функцию/Ссылок и массивов/Ок);
1.3 Применила функцию: Ctrl+Shift+Enter:
1.4 Сохранила документ (Файл/Сохранить…).
7.2
1.1 Создала таблицу из ячеек, которая включает в себя данные по участникам соревнования;
1.2 Посчитала средний балл, полученный участниками по всем соревнованиям с помощью функции СРЗНАЧ: =СРЗНАЧ(F51:F60), =СРЗНАЧ(G51:G60) (Вставить функцию/Статистические/Ок):
1.3 Определила фамилию спортсмена, показавшего лучший результат с помощью функции ПОИСКПОЗ: =ПОИСКПОЗ(10;F61:G61;1) (Вставить функцию/Ссылки и массивы/Ок);
1.4 Выбирала тип сопоставления 1 => функция ПОИСКПОЗ находит наибольшее значение, которое меньше или равно значению аргумента 10 (наилучшему возможному результату). Так как точного соответствия нет, возвращается позиция ближайшего меньшего элемента (6,6) в заданном диапазоне:
1.5 Сохранила документ (Файл/Сохранить…).
7.3
1.1 Ввела данные, а именно: № магазинов, доход и все месяцы года:
1.2 Определила название месяца, в котором доход был минимальный, используя функцию ГПР, где интервальный просмотр ЛОЖЬ, который определяет, что поиск должен производиться точно
7.4
1.1 Ввела данные для задания 3: числа; текст (начальная строка, начальный столбец, число строк, число столбцов);
1.2 Определила начальную строку, используя функцию СТРОКА: =СТРОКА(T50:U54) (Вставить функцию/Ссылки и массивы/Ок);
1.3 Определила начальный столбец, используя функцию СТОЛБЕЦ: =СТОЛБЕЦ(T50:U54)
(Вставить функцию/Ссылки и массивы/Ок);
1.4 Определила число строк, используя функцию ЧСТРОК: =ЧСТРОК(T50:U54)
(Вставить функцию/Ссылки и массивы/Ок);
1.5 Определила число строк, используя функцию ЧИСЛОСТОЛБ: =ЧИСЛСТОЛБ(T50:U54) (Вставить функцию/Ссылки и массивы/Ок):
1.6 Сохранила документ (Файл/Сохранить…).
8.1
1.1 Продолжила работу в MS Excel;
1.2 Ввела значения для выполнения задания в соответствии с вариантом: ссуду в n тыс. рублей на год под, ставку p годовых с ежеквартальными начислениями;
1.3 Определила сумму конечного платежа с помощью функции БС, где ставка квартальная и тип 0 так как значение типа отсутствует: =БС(B67/4;12;-B66;0;0)
(Вставить функцию/Финансовые/Ок):
1.4 Сохранила документ (Файл/Сохранить…).
8.2
1.1 Ввела данные для выполнения задания в ячейки: сумма n тыс. долларов, время k лет, годовая ставка p%;
1.2 Определила, какая сумма будет на счете через k лет, используя функцию БС: =БС(F68/12;60;-F66;0;0), где ставка месячная, кпер=12*5, тип 0 (Вставить функцию/Финансовые/Ок):
1.3 Сохранить документ (Файл/Сохранить…).
8.3
1.1 Ввела данные для выполнения задания в ячейки: ссуда n тыс. долларов, p% годовых, сумма k долларов;
1.2 Рассчитала срок, за который будет погашена ссуда, используя функцию КПЕР: =КПЕР(K68/6;K67;-K66;0;0), где ставка 19/6 и тип 0 (Вставить функцию/Финансовые/Ок):
1.3 Сохранила документ (Файл/Сохранить…).
8.4
1.1 Ввела значения для выполнения задания 1: данную сумму, сумму возврата, дату соглашения и дату погашения;
1.2 Вычислила процентную ставку за год, используя функцию ИНОРМА: =ИНОРМА(N68;N69;N66;N67;3), где базис 3 (Фактический/365) (Вставить функцию/ Финансовые/Ок):
1.3 Сохранила проделанную работу (Файл/Сохранить как…).
Контрольные вопросы:
1. Дайте определение функции, аргумента.
2. Особенности ввода аргументов функций.
3. Перечислите основные категории встроенных функций в MS Excel.
4. Работа с Мастером функций.
Ответы на контрольные вопросы
1. Функция Excel -- это заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат. Наиболее распространенные функции Excel являются краткой записью часто используемых формул. Функции работают быстрее, чем формулы, занимают меньше места в строке формул, уменьшают вероятность появления ошибок. Данные передаются функции с помощью аргумента, например: в формуле СУММ(В1:В5) СУММ -- имя функции, а В1:В5 -- аргумент. 2. Аргументы заключаются в скобки и располагаются после имени функции. Аргументами могут быть числа, ссылки (или на ячейку или диапазон), логические значения и другие функции. По умолчанию они разделяются точкой с запятой. 3. Функции можно вводить вручную с клавиатуры (при этом нужно знать ее имя и аргументы), а можно воспользоваться мастером функций (Вставить функцию/…/Ок). Использование мастера функции значительно облегчает их ввод. Мастер функции объясняет каждую функцию и все её аргументы.
В MS Excel представлены следующие категории функций: - математические функции; - функции для работы со значениями даты и времени; - финансовые функции; - аналитические функции; - логические функции; - функции для работы со ссылками и массивами; - функции для работы с базами данных; - статистические функции; - функции для проверки свойств и значений; - инженерные функции; - текстовые функции; - функции совместимости.
4. Мастер функций облегчает пользователю ввод формул и помогает избежать ошибок при вводе. Работа с Мастером функций, как и с любым другим мастером из пакета MS Office, идет по шагам. Мастер функций содержит два шага.В списке «Категория» перечислены основные группы функций, а также 10 недавно использовавшихся функций и их полный алфавитный перечень. В списке «Выберите функцию» перечислены в алфавитном порядке имена функций для выбранной категории. На первом шаге следует выбрать нужный тип функции из списка «Категория». Если вы не уверены в выбранной категории, посмотрите полный алфавитный перечень. Затем из списка «Функция» выберите необходимую функцию. Кнопка «ОК» служит для перехода к следующему шагу «Мастера функций». На втором шаге следует ввести аргументы функции в каждом поле обязательного аргумента, и, по желанию, в каждом поле необязательного аргумента. Обязательные аргументы выделены жирным шрифтом. Рядом с полем для ввода аргумента указывается тип аргумента (если поле пустое) или его значение.
Выводы:
Ознакомилась с основными категориями встроенных функций, приобрела
навыки работы с Мастером функций.
Функции работают быстрее, чем формулы, занимают меньше места в строке формул, уменьшают вероятность появления ошибок. В ходе проведения лабораторной работы производила различные действия со средствами работы MS Exсel: ознакомилась с основными категориями встроенных функций, приобрела навыки работы с Мастером функций и самим MS Excel.