Использование функций, мастер функций Excel, функция ВПР

Функция – это объединение нескольких вычислительных операций для решения определенной задачи. Иначе - функции в формулах используются для сокращения записи сложных вычислений. В Excel существуют сотни функций.

Способ задания всех функций один и тот же:

= ИМЯ ФУНКЦИИ (аргументы)

Различие заключается только в количестве аргументов.

В качестве аргументов могут выступать: константа, ссылка на ячейку или имя диапазона ячеек, другие функции. В этом случае говорят о вложенной функции. При этом вычисления производятся как бы «изнутри»: первой вычисляется функция, назначенная в качестве аргумента другой функции.

Ввод функции может осуществляться несколькими способами:

1) ручным набором имени функции и ее аргументов с клавиатуры или с помощью мышки;

2) в меню Вставка/Функция выбрать функцию из предлагаемого списка;

3) выбрать функцию, вызвав нажатием специальной клавиши на панели инструментов Мастера функций при активной строке ввода.

Наиболее употребимы пользователем лишь несколько десятков функций. С остальными функциями можно ознакомиться при необходимости. Так, для вычисления суммы предназначена функция СУММ. Аргументы функции должны быть указаны после ее названия в круглых скобках. Например =СУММ (А1:В5,С20,С25,3.14). Ввод функции: нажмите знак =; введите название функции каким-либо из указанных выше способов; введите открывающую круглую скобку; введите адреса ячеек с клавиатуры или выделите с помощью мыши ячейки, содержимое которых должно использоваться в качестве аргументов. Адрес выделяемого диапазона ячеек сразу отобразится в позиции курсора ввода. Завершите задание функции вводом закрывающей скобки и нажатием клавиши Enter.

С функциями наиболее удобно работать, используя Мастер функций (кнопка f_x на панели инструментов). В этом случае аргументы задаются в диалоговом окне.

В Мастер функций функции разбиты на категории. Полный список категорий находится в поле Категории. К категории Полный алфавитный перечень относятся все доступные в программе функции, а к категории 10 недавно использовавшихся - функции, применявшиеся последними. Чтобы выбрать функцию, необходимо выделить имя категории, к которой она принадлежит. При выделении такой функции под полем Категория появляется краткая информация о ней.

Если краткой информации недостаточно, можно нажать в диалоговом окне кнопку Справка (или клавишу F1). В результате на экране отобразится страница справочной подсистемы с информацией о выделенной функции.

После выбора функции выполняется щелчок на кнопке Далее для перехода в следующее диалоговое окно Мастера функций, в котором должны быть заданы аргументы. В этом диалоговом окне программа информирует пользователя о том, какие аргументы должны быть указаны обязательно, а какие – нет.

По завершению ввода аргумента следует нажать кнопку Готово, и результат вычисления будет подготовлен в соответствующей ячейке.

Поскольку сложение содержимого ячеек с использованием функций СУММ является очень распространенной задачей, в Excel имеется средство – кнопка на панели инструментов - ∑ - (автосуммирование), позволяющее выполнить эту операцию с помощью одного щелчка следующим образом:

1) нужно выделить все данные, которые подлежат сложению, включая пустые ячейки в столбце слева и в строке снизу.

2) щелкнуть на кнопке ∑ (или нажать клавиши ALT+ =) и все готово.

Можно выделить только пустые ячейки для формул и позволить средству Автосуммирование самому определить, содержимое каких ячеек нужно сложить. Если выделить только одну ячейку, то средство Автосуммирование создает функцию СУММ не полностью – и будет ждать, пока пользователь изменит диапазон, выступающий в качестве аргумента, или нажмет клавишу Enter для завершения.

Категории функций: математические, тригонометрические, статистические, дата и время, текстовые, логические, финансовые, функции работы с БД, информационные и функции выборки.

В состав Excel входит свыше 250 функций из нескольких полезных категорий:

- Математические включают арифметические и тригонометрические функции. Позволяют производить простые и сложные вычисления, например вычисление суммы диапазона ячеек, вычисление суммы ячеек диапазона, удовлетворяющих указанному условию, округление чисел и прочее.

- С помощью финансовых функций осуществляются такие типичные финансовые расчеты, как вычисление суммы платежа по ссуде, объем периодической выплаты по вложению или ссуде, стоимость вложения или ссуды по завершении всех отложенных платежей.

Аргументами финансовых функций часто являются следующие величины:

Будущее значение – стоимость вложения или ссуды по завершении всех отложенных платежей.

Выплата – объем периодической выплаты по вложению или ссуде.

Режим выплат – режим, в котором осуществляются выплаты (в конце или в начале месяца).

Ставка – процентная ставка или норма скидки по вложению или ссуде.

Текущее значение – начальная стоимость вложения или ссуды. Например, начальная стоимость ссуды равна сумме займа.

Число периодов – общее количество платежей или периодов выплат.

- Функции работы с датой и временем позволяют анализировать и работать со значениями даты и времени в формулах. Например, если требуется использовать в формуле текущую дату, воспользуйтесь функцией СЕГОДНЯ, возвращающей текущую дату по системных часов.

- Статистические функции позволяют выполнять статистический анализ диапазонов данных. Например, с помощью статистической функции можно провести прямую по группе значений, вычислить угол наклона и точку пересечения с осью Y и прочее.

- Если необходимо убедиться в том, что значения списка удовлетворяют условию, можно использовать функции для работы с базами данных. С их помощью, например, можно определить количество записей в таблице о продажах или извлечь те записи, в которых значение поля «Сумма» больше 1000, но меньше 2500.

Имена многих функций работы с базами данных часто начинаются с буквы " Д". Эти функции, которые иногда также называются «Дфункциями», имеют три аргумента — база данных, поле и условие.

Аргументом «база данных» является диапазон, содержащий список. Этот диапазон должен включать строку с названиями полей.

Аргумент «поле» должен содержать имя столбца, по которому следует вычислить итог.

Аргумент «условие» является адресом диапазона, содержащего условия.

- С помощью текстовых функций можно с помощью формул производить действия над строкам текста — например, изменить регистр или определить длину строки. Можно также объединить несколько строк в одну. В примере ниже показано, как с помощью функций СЕГОДНЯ и ТЕКСТ создать сообщение, содержащее текущую дату и привести его к виду "дд-ммм-гг".

="Балансовый отчет от "&ТЕКСТ(СЕГОДНЯ(),"дд-мм-гг")

- Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение если условие истинно, и другое — если оно ложно.

- Если необходимо осуществлять поиск в списках или таблицах или если необходимо найти ссылку к ячейке, воспользуйтесь функциями ссылки и массивы. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке — функцию ПОИСКПОЗ.

- Функции проверки свойств и значений связывают Excel c самим собой. Так, ЯЧЕЙКА сообщает информацию о заданной ячейке, а ЕОШИБКА сообщает, есть ли в ячейке ошибка.

Ка­ждая функция вызывается в соответствии с определенным синтак­сисом, т.е. она имеет определенную структуру, что позволяет Excel правильно обработать результаты. Для правильного вычисления функции необходимо задать значения всех обязательных аргументов и разделить их запятыми. Когда Excel вычисляет значение этой функции, результат помещается в ячейку, в которой находится функция.

Функция ВПР (рис.) ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных. Функция ГПР ищет значение в верхней строке таблицы или массива значений и возвращает значение в том же столбце из заданной строки таблицы или массива. Используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже.

Синтаксис функции ВПР:

 

ВПР (искомое_значение; табл_массив; номер_индекса_столбца; диапазон_просмотра)

 

«Искомое_значение» - это значение, которое должно быть найдено в первом столбце табл_массива. Искомое_значение может быть числовым, логическим значением, ссылкой или текстовой строкой.

«Табл_массив» - это таблица с информацией, в которой ищется строка, где содержимое первого поля соответствует Искомому_значению. Можно использовать ссылку на интервал или имя интервала. «Табл_массив» должен быть упорядочен по первому столбцу. Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По Возрастанию. Регистр не учитывается (т. е. строчные и заглавные буквы не различаются).

«Номер_индекса_столбца» - это номер столбца в «табл_массиве», из которого должно быть возвращено соответствующее значение. Если «номер_индекса_столбца» равен 1, то возвращается значение из первого столбца аргумента «табл_массив»; если «номер_индекса_столбца» равен 2, то возвращается значение из второго столбца аргумента «табл_массива» и так далее. Если «номер_индекса_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «номер_индекса_столбца» больше, чем количество столбцов в аргументе «табл_массив», то функция ВПР возвращает значение ошибки #ССЫЛ!.

«Диапазон _просмотра» - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА (1) или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем «искомое_значение». Если этот аргумент имеет значение ЛОЖЬ (0), то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.