Функции в Microsoft Excel

Размещено на http://www.allbest.ru/

Функции в Microsoft Excel

В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач.

Все функции в Excel характеризуются:

· Названием;

· Предназначением (что, собственно, она делает);

· Количеством аргументов (параметров);

· Типом аргументов (параметров);

· Типом возвращаемого значения.

В качестве примера разберем функцию «СТЕПЕНЬ»

· Название: СТЕПЕНЬ;

· Предназначение: возводит указанное число в указанную степень;

· Количество аргументов: РАВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!);

· Тип аргументов: оба аргумента должны быть числами, или тем, что в итоге преобразуется в число. Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместоодно из них написать логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает «ЛОЖЬ» равно 0, а истину -- любое другое ненулевое значение, даже ?1 равно «ИСТИНА». То есть логические значения в итоге преобразуются в числовые;

· Тип возвращаемого значения: число -- результат возведения в степень.

Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячкейке и нажать Enter, в ячейке будет число 1024. Здесь 2 и 10 -- аргументы (параметры), а 1024 -- возвращаемое функцией значение.

Пример формулы для вычисления длины окружности, содержащую функцию ПИ():

Синтаксис записи функции

Как вы видите, чтобы Excel не выдал ошибку, функция должна соответствовать определенному набору правил. Этот набор правил называется синтаксис записи функции.

Общий синтаксис записи любой функции в Excel:

имя_функции([аргумент_1; аргумент_2; … ; аргумент_N])

Список аргументов заключен в квадратные скобки, что говорит о том, что это необязательная часть.

· Некоторые функции вообще не принимают аргументов. Например, функция ПИ() просто возвращает в ячеку значение константы «3,1415…», а функция СЕГОДНЯ() вставляет в ячейку текущую дату. Однако, даже если функция не принимает аргументов, пустые круглые скобки писать обязательно, иначе Excel выдаст ошибку!

· Некоторые функции принимают РОВНО ОДИН аргумент. Например функции sin(число), cos(число) и т. п.

· Некоторые функции принимают больше, чем один аргумент. В таком случае аргументы разделяются между собой точкой с запятой «;».

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

Ввод функций вручную

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



К недостаткам набора формул вручную можно отнести:

· Руками ставить «=»;

· набирать имя функции;

· открывать/закрывать круглые скобки;

· расставлять точки с запятой;

· следить за порядком вложенности

· заключать текст в двойные кавычки;

· не иметь возможность посмотреть промежуточные расчеты;

· и т. п.

Слишком большая вероятность допустить ошибку, набирая вручную сложные и длинные формулы, и на это уходит много времени.

Ввод функции с помощью кнопки "сумма"

Одно из средств облегчить и ускорить работы с функциями -- кнопка на панели инструментов «Стандартная». В ней разработчики Microsoft «спрятали» пять часто используемых функций:

· СУММ (минимум один, максимум 30 аргументов). Суммирует свои аргументы.

Полезный совет: Чтобы быстро узнать сумму значений в диапазоне, выделяем его и смотрим на строку состояния -- там должна отображаться сумма;

· СРЗНАЧ(минимум один, максимум 30 аргументов). Находит среднее арифметическое аргументов;

· СЧЁТ(минимум один, максимум 30 аргументов). Подсчитывает количество чисел в списке аргументов (используется для подсчета количества ячеек с числами, пустые ячейки и текст игнорируются);

· МАКС(минимум один, максимум 30 аргументов). Возвращает максимальный аргумент;

· МИН(минимум один, максимум 30 аргументов). Возвращает минимальный аргумент.

·

Принцип работы:

1. активизируем ячейку, где должен быть результат (просто щелкаем);

2. Нажимаем на стрелочку справа от кнопки "сигма";

3. Выбираем нужную функцию. После выбора Excel сам вставит знак «=», имя функции, круглые скобки, и даже попытается угадать диапазон, который мы ходим выделить (хотя он редко угадывает);

4. Выделяем связный диапазон ячеек. Excelвствит в круглые скобки адрес выделенного диапазона;

5. Если нужно, например, проссуммировать числа из несвязных диапазонов, зажимаем Ctrl, и выделяем нужное количество диапазонов. Excel сам поставит точку с запятой, и вставит ссылку на другой диапазон;

6. Когда выделили все нужные диапазоны, для завершения нажимаем Enter;

В процессе выделения можно нажимать F4 для изменения типа ссылки и F3 для вставки именованных ссылок.

Работа с мастером функций

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

Существует 3 способа запуска мастера функций:

1. С помощью кнопки в строке формул;

2. С помощью команды "Другие функции..." кнопки ;

3. С помощью пункта меню "Вставка" --> "Функция";

Первый шаг

После выполнения одного из этих действий откроется окно мастера функций:

На первом шаге мы выбираем нужную нам функцию, пользуясь поиском или фильтром категорий. После выбора нужной функции нажимаем "ОК" и попадаем на второй шаг.

Второй шаг

Открывается следующее окно:

Вставка вложенной функции

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 -- до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию -- специальная кнопка на панели "Строка формул":

функция excel формула

После выбора нужной функции из выпадающего списка Excel вставит называние фукнции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным:

Переключение на другую функцию в формуле

Чтобы опять вернуться к окну для функции "СУММ", достаточно просто щелкнуть в строке формул на ее названии, и окно для степени сменится на окно для "СУММ". После этого функция "СУММ" в названии станет жирной, показывая, что в данный момент окно открыто именно для нее.

Типичные ошибки при работе с мастером функций

· Перед вставкой фукнции забывают переключиться (ткнуть мышкой) в нужное текстовое поле (в нужный аргумент). При этом в активное текстовое поле вставляется знак "+", затем название функции;

· После переключения на другую функцию (щелчка на ней в строке формул) забывают щелкнуть мышкой в одно из текстовых полей мастера функций и вставляют новую функцию. При этом название активной функции в строке формул заменяется на название вставляемой функции;

Полезные советы по работе с мастером функций

· Окно мастера функций можно перетаскивать за любую точку;

· В окне мастера функций, выделяя ссылку и нажимая "F4" один, или несколько раз, можно поменять тип ссылки (сделать абсолютной или смешанной);

· Если в окне мастера функций нажать "F3", откроется окно для вставки именованных ссылок (если они существуют);

· Если какую-то функцию в формуле нужно заменить на другую, выделяем эту функцию в строке формул и вставляем нужную функцию. Выделенная функция заменится на вставляемую;

· Клавиша "Tab" служит для переключения на следующий аргумент (текстовое поле), а сочетание "Shift+Tab" -- на предыдущее;

· Если выделить ячейку, содержащую формулы с функциями и нажать кнопку Fx, то откроется окно мастера функций для одной из функций в формуле;

· Для того, чтобы написать формулу типа "=СУММ(J1:J3)/СУММ(K1:K3)", откройте мастер функций, выберете функцию "СУММ", выделите нужный диапазон, затем щелкнете мышкой в строку формул и в ней вручную наберите "/", затем нажмите на кнопку для вставки функции, и вставьте вторую функцию "СУММ". Все будет работать;

· Если в текстовом поле нужно ввести ТОЛЬКО текст, не обязательно вручную ставить двойные кавычки. Можно написать текст без кавычек и нажать Tab, или просто щелкнуть в другое поле. Excel проставит кавычки автоматом (работает не во всех текстовых полях);

· В тех полях, где требуется ввести логическое значение "ЛОЖЬ" или "ИСТИНА", достаточно ввести "0" для "ЛОЖЬ", и любое ненулевое значение для "ИСТИНА" (принято использовать 1).

Основные функции

Простейшие математические и статистические функции

МИН, МАКС

Синтаксис:

МИН(число1; число2; ... ; число30)МАКС(число1; число2; ... ; число30)

Функции МИН и МАКС принимают от 1 до 30 аргументов (в Office 2007 -- до 255) и возвращает минимальный / максимальный из них. Если в качестве аргумента передать диапазон ячеек, из диапазона будет выбрано минимальное / максимальное значение. Эти функции также могут быть вставлены с помощью кнопки "сигма".

СРЗНАЧ

СРЗНАЧ(число1; число2; ... ; число30)

Функция СРЗНАЧ (среднее значение) принимает от 1 до 30 аргументов (в Office 2007 -- до 255) и возвращает их среднее арифметическое (сумма чисел, делённая на количество чисел). Эту функцию также можно вставить с помощью кнопки "сигма"

СТЕПЕНЬ

СТЕПЕНЬ(число; степень)

Функция СТЕПЕНЬ возвращает результат возведения первого аргумента ("число"), в степень, указанную во втором аргументе ("степень").

СУММ

=СУММ(арг1; арг2; ... ; арг30)

Функция СУММ принимает от 1 до 30 аргументов (в Office 2007 -- до 255) и возвращает их сумму. В качестве аргументов можно передавать адреса диапазонов (что чаще всего и делается), в этом случае просуммируются все числа в диапазоне.

СЧЁТ

СЧЁТ(арг1; арг2; ... ; арг30)

Функция СЧЁТ принимает от 1 до 30 аргументов (в Office 2007 -- до 255) и возвращает количество аргументов, являющиеся числами. Чаще всего функции просто передают адрес диапазона, а она подсчитывает количество ячеек с числами.

ПИ

ПИ()

Возвращает значение тригонометрической константы pi = 3,1415...

ПРОИЗВЕД

ПРОИЗВЕД(арг1; арг2; ... ; арг30)

Функция ПРОИЗВЕД принимает от 1 до 30 аргументов (в Office 2007 -- до 255) и возвращает их произведение. В качестве аргументов можно передавать адреса диапазонов, в этом случае перемножатся все числа в диапазоне.

Функции СУММЕСЛИ и СЧЁТЕСЛИ

СУММЕСЛИ

Часто необходимо не просто просумировать все значения в диапазоне, а включить в сумму только те, которые удовлетворяют определённому условию. Функция СУММ суммирует все, что ей дают, функция СУММЕСЛИ позволяет просуммировать только значения, соответствующие определённому условию

Синтаксис:

СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования] )

· диапазон: Проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе.

· критерий: Условие для суммирования, на соответствие которому проверяется каждая ячейка из проверяемого диапазона. Если необходимо использовать операцию сравнения, то "логическое выражение" указывается без левого операнда и заключается в двойные кавычки (например, ">=100" -- суммировать все числа, большие 100). Также можно использовать текстовые значения (например, "яблоки" -- суммировать все значения, находящиеся напротив текста "яблоки") и числовые (например, 300 -- суммировать значения в ячейках, значения в которых 300).

· диапазон_суммирования: Необязательный аргумент, используется тогда, когда проверяемый диапазон и диапазон суммирования находятся в разных диапазонах. Если он не указан, то в качестве диапазона суммирования используется проверяемый диапазон (первый аргумент). Если он указан, то суммируются значения из ячеек этого диапазона, находящиеся "напротив" соответствующих ячеек проверяемого диапазона.

СЧЁТЕСЛИ

Работает очень похоже на функцию СУММЕСЛИ. В отличие от СУММЕСЛИ, которая суммирует значения из ячеек, СЧЁТЕСЛИ подсчитывает количество ячеек, удовлетворяющих определённому условию. Если написать формулу СУММЕСЛИ(">10", A1:A10), будет подсчитана сумма значений из ячеек, значение в которых больше 10.Если же написать СЧЁТЕСЛИ(">10", A1:A10), будет подсчитано количество ячеек, значение в которых больше 10.

Синтаксис:

СЧЁТЕСЛИ(диапазон; критерий)

· диапазон: Проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе. Из этого же диапазона происходит подсчёт количества ячеек.

· критерий: Условие, на соответствие которому проверяется каждая ячейка из первого аргумента. Условие записывается аналогично СУММЕСЛИ.

·

В примере выше фактически подсчитывается количество ячеек, содержащих текст "Яблоки".

Логические функции ЕСЛИ, И, ИЛИ

ЕСЛИ

Синтаксис:

ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).

· Предназначение: Функция ЕСЛИ выполняет то ("Значение если ИСТИНА") или иное ("Значение если ЛОЖЬ") действие в зависимости от того, выполняется (равно ИСТИНА) условие или нет (равно ЛОЖЬ).

· аргумент1. Логическое выражение: Все, что дает в результате логические значения ЛОЖЬ или ИСТИНА. Обычно либо выражения отношения (A1>=12) либо функции, возвращающие логические значения (И, ИЛИ).

· аргумент2. Значение если ИСТИНА: любое допустимое в Excel выражение.

· аргумент3. Значение если ЛОЖЬ: любое допустимое в Excel выражение.

· возвращаемое значение: может возвращать значения любых типов, в зависимости от аргументов 2 и 3.

Функция ЕСЛИ позволяет организовать в формуле ветвление. Вспомните сказки: налево пойдешь -- коня потеряешь, прямо пойдешь -- в болото попадешь, направо пойдешь -- засосёт в чёрную дыру. Использование функций ЕСЛИ, И, ИЛИ граничит с программированием. Неудивительно, что для многих людей разобраться, как они работают, очень сложно. В голове должен быть чёткий алгоритм решения задачи и требуется хорошее

понимание понятия "тип данных"

Алгоритм перехода через дорогу на светофор

И

Синтаксис:

Логич_знач И( логич_знач1; логич_знач2; ... ; логич_знач30 )

· Предназначение: Функция И используется тогда, когда нужно проверить, выполняются ли несколько условий ОДНОВРЕМЕННО. Одно из наиболее часто испльзуемых применений функции И -- проверка, попадает ли число x в диапазон от x1 до x2.

· аргументы: Функция И принимает от 1 до 30 аргументов (в Office 2007 -- до 256), каждый из которых является логическим значением ЛОЖЬ или ИСТИНА, либо любым выражением или функцией, которое в результате дает ЛОЖЬ или ИСТИНА.

· возвращаемое значение: Функция И возвращает логическое значение. Если ВСЕ аргументы функции И равны ИСТИНА, возвращает ИСТИНА. Если хотя бы один аргумент имеет значение ЛОЖЬ, возвращает ЛОЖЬ.

Примечание: Функция И почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.

ИЛИ

Синтаксис:

Логич_знач ИЛИ( логич_знач1; логич_знач2; ... ; логич_знач30 )

· Предназначение: Функция ИЛИ используется тогда, когда нужно проверить, выполняется ли ХОТЯ-БЫ ОДНО из многих условий.

· аргументы: Функция ИЛИ принимает от 1 до 30 аргументов (в Office 2007 -- до 256), каждый из которых является логическим значением ЛОЖЬ или ИСТИНА, либо любым выражением или функцией, которое в результате дает ЛОЖЬ или ИСТИНА.

· возвращаемое значение: Функция ИЛИ возвращает логическое значение. Если ХОТЯ БЫ ОДИН аргумент имеет значение ИСТИНА, возвращает ИСТИНА. Если ВСЕ аргументы имеют значение ЛОЖЬ, возвращает ЛОЖЬ.

Примечание: Функция ИЛИ почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.

=== Функция ВПР === (Вертикальное Первое Равенство) Для функции необходимо внести следующие данные: какое значение искать (указывается ячейка содержащее значение), массив в котором происходит поиск и номер столбца в котором находится значение, номер повторного значения в массиве, значение которого и будет использовано , которое надо вернуть. Очень удобная Функция значительно расширяет свои возможности при использовании её в паре с логической функцией ЕСЛИ(). !!

Объясняю как пользоваться функцией: = ВПР (что ищем, таблица где ищем, из какого столбца взять значение, булевская переменная актуальная для очень больших отсортированных таблиц, для малых всегда ставим 0- полный перебор таблицы). К примеру = ВПР (A1, B:D, 2, 0) Эта функция нужна к примеру когда вам нужно копировать данные из одной таблицы в другую. Допустим, в одной таблице у вас производство по моделям, а в другой-продажи по моделям. В этих таблицах модели не по порядку. Вы можете добавить колонку "продажи" в таблицу "производство" и с помощью функции ВПР найти соответствующие значения продаж из другой таблицы.

Использование функции автоматически находит в указанной таблице нужное значение и избавляет от ручных копирований.

Примечание: ВПР выбирает только одно значение. Если у вас в таблицах модели повторяются, необходимо использовать функцию СУММЕСЛИ.

Примечание: что ищем всегда ищется в первом столбце таблицы и результат вызова функции - значение ячейки таблицы со столбцом указанном параметром "из какого столбца взять значение", и строкой в которой первый раз будет найденно искомое значение

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

Размещено на Allbest.ru