Лабораторная работа 2 MS EXCEL. Использование простых функций в вычислениях Вопросы для подготовки 1. Назначение функций. 2. Синтаксис функций. Правила использования аргументов. 3. Работа с Мастером функций. 4. Функции СУММ, СРЗНАЧ, МАКС, МИН, СЧЕТЕСЛИ, СУММЕСЛИ. 5. Функции категории Дата и время. Задание 1. Сформируйте таблицу 1 и произведите необходимые расчеты. 2. Рассчитайте количество поступивших, проданных телевизоров разных фирм и сумму продаж для каждой фирмы и занесите результаты в табл. 2. (функции СЧЕТЕСЛИ, СУММЕСЛИ). Таблица 1 Таблица 2 , 3. Вычислите минимальную, максимальную и среднюю суммы продаж. Занесите результаты в табл. 3. Фирма Цена Поступило, шт. Продано, шт. Стоимость продаж % от стоимости продаж Sharp 4500 5 3 Samsung 12600 3 1 Sony 25900 4 2 Panasonic 16000 2 2 Hitachi 60000 6 3 Toshiba 8500 10 8 Philips 22000 3 1 Sharp 5200 5 3 Sharp 5500 5 4 Samsung 8000 4 2 Toshiba 10000 2 1 Toshiba 8500 3 1 Hitachi 32000 2 1 Hitachi 27000 2 1 Samsung 8000 4 3 Samsung 11000 2 1 Всего Фирма Поступило (кол-во) Продано (кол-во) Сумма продаж Sharp Samsung Sony Panasonic Hitachi Toshiba Philips Таблица 3 4. На основе данных таблицы 1 определите количество и сумму продаж по телевизорам:  с объемом продаж больше 5 %;  с объемом поступлений меньше 50 000 руб. Результаты занесите в табл. 4 Таблица 4 Методические указания Функция – заранее определенная формула, которая оперирует с одним или несколькими значениями и возвращает одно или несколько значений. Каждая функция вызывается в соответствии с определенным синтаксисом, т. е. она имеет определенную структуру, что позволяет MS Excel правильно ее обрабатывать и получать результаты. Функции позволяют рационально организовать процесс вычисления, например, вместо формулы =А1+B1+C1+D1+E1 целесообразнее использовать функцию =СУММ(А1:Е1). Синтаксически в функции можно выделить две части. 1. Имя функции, которое описывает операцию, выполняемую функцией. 2. Аргумент(ы), задающие значения, над которыми функция выполняет действия. Правила использования аргументов:  аргументы функции всегда заключаются в круглые скобки; Значение Сумма продаж Минимальное Максимальное Среднее Объем продаж и поступлений Количество продаж Сумма продаж Объем продаж больше 5% Объем поступлений меньше 50 000 руб.  если используются несколько аргументов, то они разделяются символом «точка с запятой» (;), например, =ЕСЛИ(А1>В1;А1+B1;A1–B1);  если функция не содержит аргументов, то все равно после имени функции ставятся круглые скобки, например ПИ() или Сегодня();  между аргументами и скобками не должно быть пробела;  в качестве аргументов используются константы, формулы или функции;  в каждом конкретном случае необходимо использовать соответствующий тип аргумента: число, текст, логическое значение (ИСТИНА и ЛОЖЬ), массивы, значение ошибки (например #Н/Д), или ссылки на ячейку;  в функциях можно использовать до 30 аргументов. Для работы с функциями в MS Excel используется Мастер функций, который вызывается кнопкой (Вставить функцию) или на вкладке Формулы в группе Библиотека функций. Мастер функций позволяет построить функцию за два шага. На первом шаге выбирается категория и функция в выбранной категории (рис. 1). Если пользователь не знает, к какой категории относится искомая функция, то следует просмотреть категории Полный алфавитный перечень имен функций или 10 недавно использовавшихся функций. Кроме того, в нижней части окна 1-го шага содержится краткое описание выполняемых функцией действий и приводится синтаксис функции. В MS Excel используются 12 основных категорий функций (рис. 1): финансовые, даты и времени, математические, статистические, ссылок и массивов, текстовые, работы с базой данных, логические, проверки свойств и значений, ссылки на массивы, а также полный алфавитный перечень и 10 недавно использованных функций. Рис. 1. Диалоговое окно Мастер функций (шаг 1 из 2) На втором шаге (рис. 2) задаются аргументы функции (обычно в виде ссылок на ячейки, или их диапазоны, или имена соответствующих ячеек и диапазонов). Для этого необходимо установить курсор ввода в соответствующее поле, после чего мышью выделить ячейку или диапазон ячеек. Рис. 2. Диалоговое окно Мастер функций (шаг 2 из 2) Если формула состоит только из функции, то для её формирования лучше воспользоваться Мастером. Если формула включает функцию лишь как один из элементов, то следует: 1. ввести участок формулы до места включения функции; 2. вызвать мастер функций или выбрать нужную функцию из списка поля имени в строке формул; 3. выбрать название требуемой функции и задать ее аргументы; 4. продолжить набор формулы (повторяя действия 2 и 3, можно вставить любое количество функций, но при этом общий объем формулы не должен превышать 255 символов); 5. завершая ввод функции, нажать клавишу . Функции СУММ, МАКС, МИН, СРЗНАЧ. Эти функции позволяют определить сумму, максимальное, минимальное и среднее (арифметическое) значение в указанном диапазоне ячеек. В качестве аргумента могут использоваться константы, ссылки на ячейки и их диапазоны, имена, массивы, а также другие функции. Следует учитывать, что эти функции игнорируют текстовые, логические и пустые ячейки, но ячейки с нулевыми значениями учитываются, что особенно важно при определении среднего значения. Кроме того, при выполнении этих операций константы с форматами Дата и Время преобразуются в Общий или Числовой формат (так, вместо даты 01.01.10 будет использовано число 40179). Все эти функции имеют одинаковый синтаксис: = имя_функции (число1; число2; число3;…) аргумент(ы) Рассмотрим примеры использования функций. 1. =СУММ(A3:A15;D3:D15) – в активную ячейку заносится сумма значений из ячеек, входящих в указанные диапазоны. 2. =МАКС(A3:A15;D3:D15) – в активную ячейку заносится максимальное значение из указанных диапазонов ячеек. 3. =МИН(A3:A15;D3:D15) ) – в активную ячейку заносится минимальное значение из указанных диапазонов ячеек. 4. =СРЗНАЧ(A3:A15;D3:D15) – в активную ячейку заносится среднее арифметическое значение из указанных диапазонов ячеек. 5. =СРЗНАЧ(СУММ(В3:В10);А3:А10;) – в данном случае одним из аргументов является функция СУММ, поэтому MS Excel сначала подсчитает сумму значений из ячеек указанного диапазона, а затем определит и занесет в активную ячейку величину среднего арифметического значения. 6. =СРЗНАЧ(МАКС(А1:А8);МИН(А1:А8)) – сначала MS Excel определит максимальное значение в диапазоне ячеек A1:A8, затем – минимальное значение, после чего подсчитает и занесет в активную ячейку величину среднего значения. Функции СЧЁТЕСЛИ, СУММЕСЛИ Функция СЧЁТЕСЛИ подсчитывает количество ячеек, удовлетворяющих заданному критерию, внутри указанного интервала. Функция СЧЁТЕСЛИ имеет следующий синтаксис: =СЧЁТЕСЛИ(интервал; критерий), аргументы где интервал – это интервал, в котором нужно подсчитать количество ячеек; критерий – это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 52, «12», «>10», «студент» и т. д. Рассмотрим примеры. Данные для анализа приведены на рис. 3. 1. =СЧЁТЕСЛИ(А1:А5;”>=100”) – функция подсчитывает количество ячеек, содержимое которых больше или равно 100. Функция возвратит число 3. 2. =СЧЁТЕСЛИ(А1:А5;”<100”) – функция возвратит значение 2. 3. =СЧЁТЕСЛИ(А1:А5;”студент”) – функция возвратит значение 0 (ноль). Рис. 3 Функция СУММЕСЛИ суммирует ячейки, содержимое которых удовлетворяет заданному критерию (условию). Функция СУММЕСЛИ имеет следующий синтаксис: =СУММЕСЛИ(интервал; критерий; интервал_суммирования), аргументы где аргументами функции являются: интервал – диапазон ячеек, содержимое которых проверяется на соответствие критерию; критерий – критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, крите рий может быть выражен как 52, «12», «>10», «студент»; интервал_суммирования – фактические ячейки, содержимое которых добавляется в искомую сумму. Ячейки в интервал_суммирования суммируются в том случае, когда соответствующие им ячейки в указанном диапазоне удовлетворяют критерию. Этот аргумент не является обязательным. Если он опущен, то суммируются ячейки из указанного диапазона. Рассмотрим примеры. Данные для анализа приведены на рис. 4. Рис. 28 Рис. 4 1. =СУММЕСЛИ(А1:А5;”>160”;B1:B5) – функция возвратит значение 93 (в сумму будут включены ячейки В2, В3, В4, В5, так как содержимое ячеек А2, А3, А4 и А5 удовлетворяет проверяемому условию ”>160”). 2. =СУММЕСЛИ(А1:А5;”>160”) – функция возвратит значение 1080. В данном примере в суммирование будут участвовать ячейки А2, А3, А4 и А5, так как интервал_суммирования опуще