9.6 Основные функции Excel
Функция – это специальная заранее подготовленная формула, которая выполняет операции над заданными значениями и возвращает результат. Значения, над которыми функция выполняет операции, называются аргументами. В качестве аргументов могут выступать числа, текст, логические значения, ссылки. Аргументы могут быть представлены константами или формулами. Формулы в свою очередь могут содержать другие функции, т.е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной функцией. Excel допускает до семи уровней вложения функций в одной формуле.
В общем виде любая функция может быть записана в виде:
Существуют следующие правила ввода функций:
имя функции всегда вводится после знака «=»;
аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов;
между именем функции и знаком «(« пробел не ставится;
вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.
Для ввода функций можно использовать Мастер функций, вызываемый нажатием пиктограммы Вставить функцию группы Библиотека функций вкладки ФОРМУЛЫ. Мастер функций (рис. 9.7) позволяет выбрать нужную функцию из списка и выводит для нее панель формул. На панели формул отображаются имя и описание функции, количество и тип аргументов, поле ввода для формирования списка аргументов, возвращаемое значение.
Excel содержит широкий набор функций, позволяющих выполнять стандартные вычисления. К основным категориям функций относятся:
1). Математические позволяют производить простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, вычисление суммы ячеек диапазона, удовлетворяющих указанному условию, округление чисел и прочее.
Рис.9.7. Диалоговое окно Мастера функций.
2). Инженерные, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую).
3). Логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).
4).Статистические, предназначенные для выполнения статистического анализа данных.
5).Финансовые, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.
6).Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т.д.).
7).Функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах.
8).Функции ссылки и массивы осуществляют поиск в списках или таблицах. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке — функцию ПОИСКПОЗ.
9). Функции проверки свойств и значений позволяют перехватывать ошибки и значения #Н/Д и предотвращать их распространение по рабочему листу.
10).Аналитические функции предназначены для работы с аналитическими кубами, которые позволяют создавать отчеты по базе данных оперативной аналитической обработки (OLAP).
11).Функции работа с базой данных предназначены для анализа данных из списков или баз данных.
12) Функции надстроек и автоматизации позволяют расширить возможности Microsoft Excel за счет добавления пользовательских команд и новых функций.
Функции Excel
Microsoft Excel содержит так много функций, что даже опытные пользователи не могут похвастаться знанием всех функций Excel.
В следующей статье представлен список наиболее важных и полезных функций Excel.
Для удобства, функции Excel разделены на категории. Ниже представлены самые основные категории функций.
Математические функции
SUM
PRODUCT
SQRT
POWER
ABS
ROUND
ROUNDUP
ROUNDDOWN
INT
MOD
SUMIF
Статистические функции
MAX
MIN
AVERAGE
COUNT
COUNTA
COUNTIF
Текстовые функции
CONCATENATE
LEFT
RIGHT
LEN
UPPER
PROPER
LOWER
SEARCH
FIND
MID
SUBSTITUTE
REPLACE
TRIM
CODE
Логические функции
IF
AND
OR
IFERROR
Функции даты и времени
TODAY
MONTH
YEAR
WEEKDAY
Функции категории ссылки и массивы
VLOOKUP
HLOOKUP
LOOKUP
ROW
COLUMN
INDEX
MATCH
Функции категории проверка свойств и значений
ISERROR
ISNA
Хотите узнать какие функции есть в Excel и научиться правильно применять их в своей работе? Приходите на Тренинг Excel для продвинутых пользователей.
Microsoft Excel/Основные функции
Не забываем всегда перед началом формулы писать «=». Синтаксис:
=МИН(число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) и возвращает количество аргументов, являющиеся числами. Чаще всего функции просто передают адрес диапазона, а она подсчитывает количество ячеек с числами.
СЧЁТПУСТОТЫ [ править ]
СЧЁТПУСТОТЫ(left_up_cell:right_down_cell)
В этом синтаксисе оператора left_up_cell: right_down_cell — ячейки (левая верхняя и нижняя правая) с помощью которых задается диапазон в котором считается количество пустых ячеек. Потом количество пустых ячеек выводится в ту ячейку, в которой стоит формула с этим оператором. Принцип работы оператора и подсчета пустых ячеек. Программа встречая оператор СЧЕТПУСТОТЫ в ячейке «смотрит» на диапазон, который указан в круглых скобках. Далее начинает «пробегаться» построчно (по каждой строке диапазона). Найдя пустую ячейку программа прибавляет значение 1 к переменной, которая хранит значение пустых ячеек. При конце диапазона в ячейку выводится значение переменной. Это очень тесно граничит с программированием. Не поняли сейчас — поймете потом. Понять никогда не поздно.
ПИ [ править ]
Возвращает значение тригонометрической константы pi = 3,1415…
ПРОИЗВЕД [ править ]
ПРОИЗВЕД(арг1; арг2; . ; арг30)
Функция ПРОИЗВЕД принимает от 1 до 30 аргументов (в Office 2007 — до 255) и возвращает их произведение. В качестве аргументов можно передавать адреса диапазонов, в этом случае перемножатся все числа в диапазоне.
СЦЕПИТЬ() [ править ]
СЦЕПИТЬ(аргумент1:аргумент2) Этот оператор конкантенирует значения ячеек. Конкантенация — объединение строк. Внимание:
Функции СУММЕСЛИ и СЧЁТЕСЛИ [ править ]
СУММЕСЛИ [ править ]
Часто необходимо не просто просуммировать все значения в диапазоне, а включить в сумму только те, которые удовлетворяют определённому условию. Функция СУММ суммирует все, что ей дают, функция СУММЕСЛИ позволяет просуммировать только значения, соответствующие определённому условию
СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования] )
- диапазон: Проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе.
- критерий: Условие для суммирования, на соответствие которому проверяется каждая ячейка из проверяемого диапазона. Если необходимо использовать операцию сравнения, то «логическое выражение» указывается без левого операнда и заключается в двойные кавычки (например, «>=100» — суммировать все числа, большие 100). Также можно использовать текстовые значения (например, «яблоки» — суммировать все значения, находящиеся напротив текста «яблоки») и числовые (например, 300 — суммировать значения в ячейках, значения в которых 300).
- диапазон_суммирования: Необязательный аргумент, используется тогда, когда проверяемый диапазон и диапазон суммирования находятся в разных диапазонах. Если он не указан, то в качестве диапазона суммирования используется проверяемый диапазон (первый аргумент). Если он указан, то суммируются значения из ячеек этого диапазона, находящиеся «напротив» соответствующих ячеек проверяемого диапазона.
СЧЁТЕСЛИ [ править ]
Работает очень похоже на функцию СУММЕСЛИ. В отличие от СУММЕСЛИ, которая суммирует значения из ячеек, СЧЁТЕСЛИ подсчитывает количество ячеек, удовлетворяющих определённому условию. Если написать формулу СУММЕСЛИ(A1:A10; «>10») , будет подсчитана сумма значений из ячеек, значение в которых больше 10. Если же написать СЧЁТЕСЛИ(A1:A10; «>10») , будет подсчитано количество ячеек, значение в которых больше 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;ЛОЖЬ) В этом примере ищется значение находящееся в ячейке A1 и возвращается значение из столбца C диапазона B:D Эту функцию используют, когда необходимо найти соответствие в определенном столбце для значения, находящегося в первом столбце. Функция как-бы вытаскивает значение из указанного столбца. Допустим, есть таблица вида: ФИО и табельный номер. Функция ВПР() позволяет получить табельный номер по указанной фамилии и инициалам (в вышеприведенном примере фамилия должна находиться в ячейке A1).
Примечание: ВПР выбирает первое сверху вниз значение. Это означает, что если в списке работников будет более одной одинаковой фамилии, то функция вернет табельный номер для той фамилии, которая идет сверху вниз раньше.
ГПР () [ править ]
Функция ГПР(Горизонтальное первое равенство) очень похожа по принципу действия на функцию ВПР(). Только значение ищется в первой строке таблицы, а результирующее (то значение, которое возвращает ГПР берется из ячейки таблицы с номером столбца в котором встретилось найденное значение и заданном в параметре номера строки. В качестве примера можно привести следующий. Первая строка содержит даты, вторая строка, к примеру, отгрузку по северному направлению, а третья строка — отгрузку по южному направлению. Если эта таблица находится в диапазоне C2:G4, то, для того, чтобы получить отгрузку за дату указанную в ячейке A6 по южному направлению, необходимо воспользоваться формулой =ГПР(A6;C2:G4;3;ЛОЖЬ). При этом, если виды направления (северное и южное) находятся рядом с данными (например, в ячейках B3:B4), то при задании диапазона C2:G4 они не охватываются.
Функции и формулы в Excel с примерами
Пример как работает функция СУММПРОИЗВ с условием в Excel.
Презентация возможностей функции СУММПРОИЗВ с несколькими условиями критериев. Например, суммирование промежуточных итогов по условию критерия.
Пример как пользоваться функциями СЛЧИС и СЛУЧМЕЖДУ в Excel.
Генераторы случайных чисел в Excel с примерами практического использования. Как и где применять функции СЛУЧМЕЖДУ и СЛЧИС для генерации случайного числа?
Скачать пример СУММЕСЛИМН с датами, где много условий в Excel.
Шаблон с примерами эффективного использования широких возможностей функции СУММЕСЛИМН при различных условиях задач. Как использовать функцию если много условий с датами?
Назначение функции СУММЕСЛИ в Excel скачать примеры.
Практическая работа функции СУММЕСЛИ с несколькими условиями. Примеры и готовые решения тестов с формулами функций СУММЕСЛИ, СУММПРОИЗВ и ВПР.
Пошаговая инструкция функции просмотр в Excel скачать примеры.
Практические примеры использования функции ПРОСМОТР для поиска значений в столбце таблицы. Поиск последней заполненной ячейки в строке каждого диапазона данных.
Скачать примеры использования функции НАЙТИ в Excel.
Практические примеры работы функции НАЙТИ с картинками, описанием и файлом готовых решений. Формулы использования функции НАЙТИ для поиска значений по столбцам таблиц.
Примеры использования функции ПОИСК скачать в Excel.
Формулы с использованием функции ПОИСК для нахождения значений по столбцам таблицы. Образцы формул в картинках с описанием и исходным файлом готовых решений.
Назначение математической функции СУММ в Excel скачать пример.
Практическая работа в Excel по функции СУММ скачать примеры с картинками и описанием. Как максимально использовать все функции формулы для автоматического суммирования ячеек диапазона?
Скачать примеры использования логических функций И ИЛИ в Excel.
Как работать с формулой логических функций И ИЛИ ЕСЛИ для автоматизации анализа данных таблицы. Примеры в картинках и с подробным описанием образца файла таблиц или формул.
Примеры функции ЕСЛИ с несколькими условиями скачать в Excel.
Скачать примеры как использовать функцию ЕСЛИ несколько условий для операций алгоритма. Комбинация функций для формулы ЕСЛИ и ВПР.