Лекция 15. MS Excel. Использование функций. Построение диаграмм
Excel предоставляет в распоряжение пользователей множество специальных функций, которые можно применять в вычислениях. К особенностям Excel следует отнести широкий набор математических, статистических функций и методов оптимизации. Использование функций позволяет эффективно обрабатывать фрагменты текста, решать самые разнообразные задачи с использованием дат и времени, создавать произвольные условные операции, основанные на логических функциях.
Аргументы функции указываются после её названия в круглых скобках. Скобки вводятся с клавиатуры. Адрес диапазона ячеек, содержимое которых должно использоваться в качестве аргументов функции, вставляется в результате выделения диапазона с помощью мыши.
На формулы с функциями не накладываются ограничения, как на другие формулы. Их можно копировать обычным образом, учитывая тип ссылки (относительная или абсолютная). Допускается использование в качестве аргументов ссылок на диапазоны из других листов и книг.
Использование функций
Одной из самых используемых является функция суммирования. Именно поэтому панель инструментов Стандартная содержит кнопку Автосумма, которая экономит время при составлении формул.
Перед тем, как нажать эту кнопку, выделите ячейку, в которую нужно поместить конечный результат. Щелчок на кнопке Автосумма приведёт в автоматическому выделению соседних ячеек, которые будут использоваться в качестве аргумента функции, и помещению итоговой формулы в строку формул. Например, если выделить ячейку, расположенную под столбцом цифр, ваш экран будет выглядеть как показано на рис. 1. Нажмите клавишу Enter, если вас устраивает выделенный диапазон. Если нет – используйте кнопку мыши для выделения нужного набора ячеек. Для отмены команды Автосумма нажмите клавишу Esc.
Рис. 1. Результат нажатия кнопки Автосумма
Можно использовать функцию суммирования также для сложения значений нескольких диапазонов ячеек. Например, формула =СУММ(А3:А8;С3:С8) складывает значения шести ячеек в столбце А и шести ячеек в столбце С. Напомним, что выделение диапазонов ячеек, расположенных на некотором расстоянии друг от друга, осуществляется при нажатой клавише Ctrl.
Функция может служить аргументом другой функции. Такие функции называются вложенными. Рассмотрим, как применять вложенные функции.
Пример
Создайте таблицу с данными о температуре воздуха в некоторых городах мира за одну неделю (см. рис. 2). Необходимо определить минимальную температуру за неделю в различных городах, а также максимальное из минимальных значений.
Для этого введите в ячейку В12 формулу =МИН (В4:В10). Скопируйте эту формулу в ячейки С12: Е12. В результате применения функции, определяющей минимальное значение, мы установим минимальную температуру в разных городах. Чтобы определить максимальное из минимальных значений, введите в ячейку В13 формулу =МАКС (В12:Е12).
Полностью решить данную задачу можно и с помощью одной-единственной формулы (которую нужно вставить в ячейку В13):
=МАКС (МИН (В4:В10); МИН (С4:С10); МИН (D4:D10); МИН (Е4:Е10)).
Рис. 2. Пример использования вложенных функций
Формируя вложенные функции, следует учитывать, что первой вычисляется функция во внутренних скобках.
Существует множество задач (например, округление значений), решать которые на много легче, используя вложенные функции. Для округления чисел в Excel предназначена функция ОКРУГЛ, синтаксис которой несколько отличается от синтаксиса уже знакомых нам функций СУММ, МИН и МАКС. Аргументами функции ОКРУГЛ являются число или ссылка и количество десятичных разрядов результата.
В процессе работы с функциями следует использовать Мастер функций. В этом случае аргументы устанавливаются в диалоговом окне, что исключает появление ошибок, вероятность возникновения которых имеет место при вводе функции с клавиатуры.
В случае ошибочного ввода функции (например, пользователь забыл указать обязательный аргумент, количество закрывающих скобок не соответствует количеству открывающих и т.д.) появляется сообщение об ошибке.
Мастер функций
Мастер функций предназначен для упрощения работы с функциями. Чтобы запустить Мастер функций, щёлкните на кнопке 
в строке формул.
Для выбора функций предназначен список в левой части строки формул (появляется после ввода знака =). Этот список содержит имена десяти функций, использовавшихся последними. Если нужной функции нет в списке, следует выбрать элемент Другие функции, вследствие чего откроется диалоговое окно Мастер Функций (рис. 3). Это окно открывается также в результате вызова команды Вставка | Функция.
Все функции Excel сгруппированы по категориям, имена которых отображаются в списке Категория. В поле Выберите функцию приводится перечень функций выбранной категории. В нижней части окна отображается краткое описание отмеченной функции и её синтаксис.
Рис. 3. Диалоговое окно Мастер функций
После выбора функции щёлкните на кнопке ОК, при этом появится второе окно диалога Аргументы функции, с помощью которого задайте аргументы. Программа снабжает пользователя подсказкой о том, какие аргументы являются обязательными, а какие нет.
Существует несколько способов задания аргументов. Рассмотрим некоторые из них на примере вставки функции СРЗНАЧ для вычисления среднего значения. Количество аргументов функции не должно превышать 30. В окне диалога Аргументы функции для ввода каждого аргумента функции предусмотрено отдельное поле. Сначала таких полей два (рис. 4), но по мере ввода аргументов количество полей увеличивается.
Рис. 4. Диалоговое окно Аргументы функции после выбора аргументов
В качестве аргумента можно задавать числовое значение, адрес ячейки (абсолютный или относительный), адрес или имя диапазона. После ввода аргумента справа от поля ввода отображается значение из указанной ячейки.
Для завершения ввода функции нажмите кнопку ОК. Результат вычислений появится в итоговой ячейке.
Присвоение имен ячейкам и их диапазонам
Чтобы понять смысл функции было проще, можно присвоить имя ячейке или диапазону ячеек и затем использовать его в вычислениях. Например, вы можете присвоить ячейкам E4:E10 (рис. 10.3) имя Москва, и тогда формула для нахождения минимального значения для этих ячеек будет выглядеть следующим образом: =МИН (Москва).
Имена должны начинаться с буквы и не могут содержать пробелы. Рекомендуемая длина имени – 15 знаков.
Для того, чтобы присвоить имя ячейке или диапазону ячеек, выделите набор ячеек вместе с их заголовком (заголовок будет использован в качестве имени выделенного диапазона) и выберите команду Вставка | Имя | Создать. Нажмите кнопку ОК.
Можно также, выделив набор ячеек, ввести имя в текстовое поле Имя, расположенное в строке формул. Этот метод используется, когда нужно присвоить набору ячеек имя, отличное от их заголовка.
Чтобы избежать ошибок при написании имён в формулах, используйте команду Вставка | Имя | Вставить. Чтобы получить доступ к редактированию и удалению имён активизируйте команду Вставка | Имя | Присвоить. В диалоговом окне Присвоение имени вы можете выделить имя и напечатать новый вариант, при необходимости изменить диапазон ячеек.
Типы функций
Математические функции
Среди функций, которые предлагает пользователю Excel, одну из наиболее многочисленных категорий образуют математические и тригонометрические функции. Применение этих функций позволяет значительно ускорить и упростить процесс вычислений. В качестве аргументов математических функций выступают, как правило, числовые значения.
В списке математических функций есть все наиболее распространенные и часто используемые функции: тригонометрические функции, экспонента, логарифмы (включая натуральный и десятичный), квадратный корень, возведение в степень и т.п. Кроме того, есть несколько различных функций округления, функции преобразования из градусной меры в радианную и наоборот, преобразования числа из арабской системы исчисления в римскую и т.д.
Есть также функции, выполняющие действия с матрицами. Действия с матрицами имеют свои особенности. Поэтому рассмотрим несколько примеров.