Microsoft 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 они не охватываются.