Применение встроенных функций Excel.
В MS Excel содержится большое количество стандартных формул, называемых функциями. Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах.
Встроенные функции Excel – это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления. Они подобны специальным клавишам на некоторых калькуляторах, предназначенным для вычисления квадратных корней, логарифмов и статистических характеристик.
Всего в Excel более 900 встроенных функций. Помимо встроенных функций используются в вычислениях пользовательские функции. Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. При нарушении правил синтаксиса, Excel выдаст сообщение об ошибке.
Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой ";".
Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Не следует вставлять пробелы между названием функции и скобками. В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными.
Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.
Встроенные функции Excel делятся на следующие категории:
математические, логические, тригонометрические, статистические, финансовые, информационные, текстовые, функции даты и времени, инженерные, функции для работы с базой данных, функции просмотра и ссылок.
При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.
Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.
В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную матрицу, транспонировать.
Функции просмотра и ссылок позволяет "просматривать" информацию, хранящуюся в списке или таблице, а также обрабатывать ссылки.
Каждая функция состоит из трех обязательных элементов:
1. Знак равенства (=). Признак того, что в данную ячейку введена функция или формула, а не данные какого-либо другого типа (например, текстовый фрагмент, числовое значение или дата).
2. Имя функции. Характеризует тип выполняемых операций, например СУММ (суммировать), СРЗНАЧ (среднее значение), ПЛТ (платеж) и т.д.
3. Аргументы. Значения, на основе которых выполняются вычисления. Аргументы вводят в круглых скобках сразу после имени функции. Как правило, это адреса ячеек, значения которых используются в вычислениях. Например, адрес диапазона ячеек A5:D23. Аргументы могут быть и более сложными; это зависит от назначения функции.
Функции могут быть вложенными в формулы или служить аргументом других функций. Например, для того чтобы подсчитать сумму значений, введенных в диапазоне А5:А44, а затем прибавить к полученному результату значение ячейки В44, можно воспользоваться следующей формулой: =СУММ(А5:А44)+В44.
Ввод простых функций не составляет труда. Однако при вводе функций, которые предназначены для решения более сложных задач, необходимо соблюдать определенный синтаксис (строгая очередность аргументов).
Вручную вводить такие функции довольно сложно, поскольку необходимо помнить имя функции и, как правило, ее непростой синтаксис. Упростить эту процедуру поможет специальное программное средство — Мастер функций.
Чтобы с его помощью вставить в ячейку рабочего листа любую из встроенных функций программы Excel, необходимо выполнить ряд действий:
1. Активизировать ячейку, в которую необходимо вставить функцию.
2. Щелкнуть на кнопке “Вставить функцию” в строке формул.
На экране появится диалоговое окно Мастер функций с перечнем встроенных функций программы Excel. В нижней части диалогового окна Мастер функций приведено краткое описание и назначение функции, имя которой выбрано в списке 3. Выбрать функцию. Для получения более подробной информации щелкнуть на ссылке Справка по этой функции.
Из раскрывающегося списка Категория выбрать нужную группу функций. Если вы не знаете, в какой группе находится функция, выбрать из списка элемент Полный алфавитный перечень.
В списке Выберите функцию щелкнуть на имени интересующей вас функции, а затем на кнопке ОК. На экране появится диалоговое окно Аргументы функции , в котором есть текстовые поля, предназначенные для ввода каждого аргумента (или нескольких аргументов) выбранной функции.
4. Ввести в текстовые поля либо числовые значения аргументов, либо адреса ячеек, в которых содержатся эти числовые значения. Если нужные в данный момент ячейки рабочего листа скрыты за диалоговым окном Аргументы функции, щелкнуть на кнопке, которая расположена в правой части каждого текстового поля. В результате размеры окна будут существенно уменьшены, что позволит выбрать ячейки с нужным значением. Чтобы вернуть прежние размеры диалогового окна Аргументы функции, щелкнуть на этой кнопке еще раз.
Некоторые функции программы имеют необязательные аргументы. Названия таких аргументов отображены обычным шрифтом, а названия обязательных аргументов выделены полужирным шрифтом.
5. Щелкнуть на кнопке ОК или нажмите клавишу Enter .
Программа автоматически вставит в ячейку выбранную функцию с указанными вами аргументами. Результат вычисления функции отображается в той же ячейке, в которой находится функция.
Для того чтобы изменить аргументы ранее веденной функции, щелкните на ячейке, в которой эта функция находится. Затем щелкните на кнопке Вставить функцию. Появится диалоговое окно Аргументы функции с перечнем прежних значений аргументов. Введите новые значения аргументов и щелкните мышью на кнопке ОК.
Некоторые функции, такие как СУММ (SUM), SIN (SIN) и ФАКТР (FACT), являются эквивалентами длинных математических формул, которые можно создать самим. Другие функции, такие как ЕСЛИ (IF) и ВПР (VLOOKUP), в виде формул реализовать невозможно.
В тех случаях, когда нужна информация о функциях, следует обращаться к справочной системе Excel, где находится полное описание каждой встроенной функции.
Быстро получить информацию о функциях можно также с помощью кнопки Вставка функции.
Некоторые функции, такие как ПИ (PI) и ИСТИНА (TRUE), не имеют аргументов. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки.
При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, следующая формула указывает Excel, что необходимо перемножить числа в ячейках С1, С2 и С5:
=ПРОИЗВЕД(С1;С2;С5).
В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, следующая функция имеет три аргумента, но суммирует числа в 29 ячейках (первый аргумент, А1:А5, ссылается на диапазон пяти ячеек от А1 до А5 и т.д.):
=CУMM(A1:A5;C2:C10;D3:D17).
Указанные в ссылке ячейки, в свою очередь, могут содержать формулы, которые ссылаются на другие ячейки или диапазоны. Используя аргументы, можно легко создавать длинные цепочки формул для выполнения сложных операций.
Комбинацию функций можно использовать для создания выражения, которое Excel сводит к единственному значению и интерпретирует его как аргумент.
Аргумент – выражение, задающее значение при обращении к процедуре или функции, от которого зависит результат ее выполнения.
В качестве аргументов используются числовые, текстовые и логические значения, имена диапазонов, массивы и ошибочные значения. Некоторые функции возвращают значения этих типов и их в дальнейшем можно использовать в качестве аргументов в других функциях.
Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 327, 209 и 176:
=СУММ(327;209;176).
Обычно числа вводятся в ячейки листа, которые будут использоваться, а затем применяются ссылки на эти ячейки в качестве аргументов в функциях.
В качестве аргумента функции могут использоваться текстовые значения. Например:
=ТЕКСТ(ТДАТА();«Д МММ ГГГГ»).
В этой формуле второй аргумент функции ТЕКСТ «Д МММ ГГГГ», является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА(), в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.
Аргументы ряда функций могут принимать только логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например, первый аргумент функции ЕСЛИ (IF) в следующей формуле является логическим выражением, которое использует значение:
=ЕСЛИ(А1=ИСТИНА, «Новая», «Старая»)& «аппаратура».
Если значение в ячейке А1 равно ИСТИНА, то выражение А1=ИСТИНА возвращает значение ИСТИНА, и функция ЕСЛИ возвращает строку Новая, а формула в целом возвращает текстовое значение Новая аппаратура.
В качестве аргумента функции можно указать имя диапазона. Например, если выбрать команду Присвоить подменю Имя меню Вставка и назначить диапазону С3:С6 имя Получено, то для вычисления суммы чисел в ячейках С3, С4, С5 и С6 можно использовать формулу:
=СУММ(Получено).
Аргументом функции может быть массив. Некоторые функции, такие как ТЕНДЕНЦИЯ (TREND) и ТРАНСП (TRANSPOSE) требуют задания массива аргументов. Другие функции не требуют задания массива, но могут использовать такие аргументы. Массивы могут содержать числовые, текстовые или логические значения.
В одной функции можно использовать аргументы различных типов. Например, в следующей формуле аргументами являются имя диапазона (Группа 1), ссылка на ячейку (A3) и числовое выражение (5*3), а сама формула возвращает единственное числовое значение:
=СРЗНАЧ(Группа1;А3;5*3).
Вводить функции в рабочем листе можно прямо с клавиатуры или с помощью команды Функция меню Вставка. При вводе функции с клавиатуры лучше использовать строчные буквы. Когда закончится ввод функции, необходимо нажать клавишу Enter или выделить другую ячейку. Excelизменит буквы в имени функции на прописные, если оно было введено правильно. Если буквы не изменяются, это означает, что имя функции введено неверно.
Используя Мастер функций, можно избежать ошибок в набираемой формуле и получить подсказку по назначению и формату функции и ее аргументов.