Функции Microsoft Excel - ABCD42.RU

Функции Microsoft Excel

Excel для начинающих: 10 базовых функций программы

Как экономить нервы и время с Excel.

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

В этой статье собрали топ-10 базовых функций, которые чаще всего используются в Эксель.

#1. СУММ

число1 — обязательный аргумент.

Функция дает возможность найти сумму отдельных числовых значений, диапазонов, ссылок на ячейки с числовыми значениями или сумму всех этих 3-х видов. Часто используется в подбивании итоговых значений строк или столбцов при формировании отчетов.

Примеры использования

Допустим, у вас есть массив числовых значений и вам нужно посчитать сумму некоторых из них. Используя функцию СУММ, в области аргументов функции мы подставляем нужные ссылки на ячейки и получаем ответ — 312.

В кое-каких случаях в массивах не находятся значения, которые нужно так же просуммировать, и вместо ссылок можем добавить свои числа. Ответ в этом случае — 419.

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

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

Если в одной или нескольких ячейках диапазона окажется не числовое выражение, а текстовое, Excel будет приравнивать эти значения к нулю.

#2. СЧЁТ

значение1 — обязательный аргумент.

С помощью этой функции можно подсчитать ячейки, которые содержат только числовые значения в списке аргументов. Зачастую используется в расчетах средних значений, когда использование функции СРЗНАЧ в Excel нецелесообразно.

Следующая формула возвращает количество ячеек в диапазоне A1:E4, которые содержат числа.

Как мы видим, в нашем списке аргументов диапазон из пяти значений, но функция вернет три, ведь числовые значения содержатся только в столбцах A, B, C. В столбце D — текстовое выражение, а Е — незаполненная ячейка.

Близкие по применению функции:

=СЧЁТЗ(значение1;[значение2];…) — считает количество непустых значений в перечне аргументов.

=СЧИТАТЬПУСТОТЫ(диапазон) — считает количество пустых значений в указанном диапазоне.

#3. МИН

число1 — обязательный аргумент.

Функция позволяет найти минимальное числовое значение в указанном списке аргументов. Часто используется при построении финансовой отчетности, когда нужно определить дату начала периода отчета, минимальный чек покупки и другие параметры.

Пример

Допустим, у нас есть диапазон чисел и текстовых выражений, и нужно найти минимальное значение.

Например, минимальное значение среди двух выбранных диапазонов A1:D2, A4:D4 и числа 54 будет 21. Пустые поля и текстовые выражения функцией исключаются и в расчетах не используются.

Близкие по назначению функции

=МИНА(значение1;[значение2];…) — находит минимальное значение в списке аргументов, при этом текстовые и ложные логические выражения равняются к нулю, а логическое выражение «ИСТИНА» в ячейке равняется 1.

=МАКС(число1;[число2];…) — находит максимальное значение в списке аргументов, при этом текстовые и пустые выражения игнорируются.

=МАКСА(значение1;[значение2];…) — находит максимальное значение в списке аргументов, при этом текстовые и ложные логические выражения приравниваются к нулю, а логическое выражение «ИСТИНА» в ячейке равняется 1.

#4. СРЗНАЧ

число1 — обязательный аргумент.

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

Допустим, у нас есть диапазон из 6 ячеек: 4 из них заполнены числами, включая 0, одно значение — текстовое и еще одно — пустое. Функция просуммирует только числовые и поделит сумму на общее количество числовых — 4.

В результате мы получим среднее, равное 4. Давайте проверим формулой:

(4 + 5 + ТЕКСТ + 7 + 0 + ПУСТОЕ) / 4 = 16 / 4 = 4

ТЕКСТ и ПУСТОЕ игнорируются.

#5. ОКРУГЛ

Синтаксис: =ОКРУГЛ(число; число_разрядов)

число — аргумент.

число_разрядов — до какого разряда округляется число.

Функция ОКРУГЛ применяется для округления действительных чисел до требуемого количества знаков после запятой и возвращает округленное значение согласно математическому правилу округления.

К примеру, для округления числа 2,57525 до 2-х символов после запятой можно ввести формулу =ОКРУГЛ(2,57525;2), которая вернет значение 2,58. Эта функция часто используется при построении балансовых и других видов отчетности.

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

Microsoft Excel
Работа с мастером функций Основные функции Дополнительные возможности

Содержание

  • 1 МИН, МАКС
  • 2 СРЗНАЧ
  • 3 СТЕПЕНЬ
  • 4 СУММ
  • 5 СЧЁТ
  • 6 СЧЁТПУСТОТЫ
  • 7 ПИ
  • 8 ПРОИЗВЕД
  • 9 СЦЕПИТЬ()
  • 10 Функции СУММЕСЛИ и СЧЁТЕСЛИ
    • 10.1 СУММЕСЛИ
    • 10.2 СЧЁТЕСЛИ
  • 11 Логические функции ЕСЛИ, И, ИЛИ
    • 11.1 ЕСЛИ
    • 11.2 И
    • 11.3 ИЛИ
  • 12 Функция ВПР (Вертикальное Первое Равенство)
  • 13 ГПР ()
МИН, МАКС [ править ]

Не забываем всегда перед началом формулы писать «=». Синтаксис:

=МИН(число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 (по категориям)

Функции упорядочены по категориям в зависимости от функциональной области. Щелкните категорию, чтобы просмотреть относящиеся к ней функции. Вы также можете найти функцию, нажав CTRL+F и введя первые несколько букв ее названия или слово из описания. Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.

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

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

Эта функция возвращает разные значения в зависимости от того, соблюдается ли условие. Вот видео об использовании функции ЕСЛИ.

Читайте также  Оценка стоимости машин и оборудования 2

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

Эта функция используется для поиска данных в таблице или диапазоне по строкам. Например, можно найти фамилию сотрудника по его номеру или его номер телефона по фамилии (как в телефонной книге). Посмотрите это видео об использовании функции ВПР.

Данная функция применяется для поиска элемента в диапазоне ячеек с последующим выводом относительной позиции этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 7 и 38, то формула =MATCH(7,A1:A3,0) возвращает значение 2, поскольку элемент 7 является вторым в диапазоне.

Эта функция позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента «номер_индекса».

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

Функция РАЗНДАТ вычисляет количество дней, месяцев или лет между двумя датами.

Эта функция возвращает число дней между двумя датами.

Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую. Они возвращают начальную позицию первой текстовой строки относительно первого знака второй.

Эта функция возвращает значение или ссылку на него из таблицы или диапазона.

Эти функции в Excel 2010 и более поздних версиях были заменены новыми функциями с повышенной точностью и именами, которые лучше отражают их назначение. Их по-прежнему можно использовать для совместимости с более ранними версиями Excel, однако если обратная совместимость не является необходимым условием, рекомендуется перейти на новые разновидности этих функций. Дополнительные сведения о новых функциях см. в статьях Статистические функции (справочник) и Математические и тригонометрические функции (справочник).

Если вы используете Excel 2007, эти функции можно найти в категориях Статистические и Математические на вкладке Формулы.

Возвращает интегральную функцию бета-распределения.

Возвращает обратную интегральную функцию указанного бета-распределения.

Возвращает отдельное значение вероятности биномиального распределения.

Возвращает одностороннюю вероятность распределения хи-квадрат.

Возвращает обратное значение односторонней вероятности распределения хи-квадрат.

Возвращает тест на независимость.

Соединяет несколько текстовых строк в одну строку.

Возвращает доверительный интервал для среднего значения по генеральной совокупности.

Возвращает ковариацию, среднее произведений парных отклонений.

Возвращает наименьшее значение, для которого интегральное биномиальное распределение меньше заданного значения или равно ему.

Возвращает экспоненциальное распределение.

Возвращает F-распределение вероятности.

Возвращает обратное значение для F-распределения вероятности.

Округляет число до ближайшего меньшего по модулю значения.

Вычисляет, или прогнозирует, будущее значение по существующим значениям.

Возвращает результат F-теста.

Возвращает обратное значение интегрального гамма-распределения.

Возвращает гипергеометрическое распределение.

Возвращает обратное значение интегрального логарифмического нормального распределения.

Возвращает интегральное логарифмическое нормальное распределение.

Возвращает значение моды набора данных.

Возвращает отрицательное биномиальное распределение.

Возвращает нормальное интегральное распределение.

Возвращает обратное значение нормального интегрального распределения.

Возвращает стандартное нормальное интегральное распределение.

Возвращает обратное значение стандартного нормального интегрального распределения.

Возвращает k-ю процентиль для значений диапазона.

Возвращает процентную норму значения в наборе данных.

Возвращает распределение Пуассона.

Возвращает квартиль набора данных.

Возвращает ранг числа в списке чисел.

Оценивает стандартное отклонение по выборке.

Вычисляет стандартное отклонение по генеральной совокупности.

Возвращает t-распределение Стьюдента.

Возвращает обратное t-распределение Стьюдента.

Возвращает вероятность, соответствующую проверке по критерию Стьюдента.

Оценивает дисперсию по выборке.

Вычисляет дисперсию по генеральной совокупности.

Возвращает распределение Вейбулла.

Возвращает одностороннее P-значение z-теста.

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

Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.

Возвращает значение свойства элемента из куба. Используется для подтверждения того, что имя элемента внутри куба существует, и для возвращения определенного свойства для этого элемента.

Возвращает n-й, или ранжированный, элемент в множестве. Используется для возвращения одного или нескольких элементов в множестве, например лучшего продавца или 10 лучших студентов.

Определяет вычисленное множество элементов или кортежей путем пересылки установленного выражения в куб на сервере, который формирует множество, а затем возвращает его в Microsoft Office Excel.

Возвращает число элементов в множестве.

Возвращает агрегированное значение из куба.

13 функций в Excel обязательных для специалистов

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Работа каждого современного специалиста непременно связана с цифрами, с отчетностью и, возможно, финансовым моделированием.

Большинство компаний используют для финансового моделирования и управления Excel, т.к. это простой и доступный инструмент. Excel содержит сотни полезных для специалистов функций.

В этой статье мы расскажем вам о 13 популярных базовых функциях Excel, которые должен знать каждый специалист! Еще больше о функционале программы вы можете узнать на нашем открытом курсе «Аналитика с Excel».

Без опытного помощника разбираться в этом очень долго. Можно потратить годы профессиональной жизни, не зная и трети возможностей Excel, экономящих сотни рабочих часов в год.

Итак, основные функции, используемые в Excel.

1. Функция СУММ (SUM)

Русская версия: СУММ (Массив 1, Массив 2…)

Английская версия: SUM (Arr 1, Arr 2…)

Показывает сумму всех аргументов внутри формулы.

Пример: СУММ(1;2;3)=6 или СУММ (А1;B1;C1), то есть сумма значений в ячейках.

2. Функция ПРОИЗВЕД (PRODUCT)

Русская версия: ПРОИЗВЕД (Массив 1, Массив 2…..)

Английская версия: PRODUCT (Arr 1, Arr 2…..)

Выполняет умножение аргументов.

Пример: ПРОИЗВЕД(1;2;3)=24 или ПРОИЗВЕД(А1;B1;C1), то есть произведение значений в ячейках.

3. Функция ЕСЛИ (IF)

Русская версия: ЕСЛИ (Выражение 1; Результат ЕСЛИ Истина, Результат ЕСЛИ Ложь)

Английская версия: IF (Expr 1, Result IF True, Result IF False)

Для функции возможны два результата.

Первый результат возвращается в случае, если сравнение – истина, второй — если сравнение ложно.

Пример: А15=1. Тогда, =ЕСЛИ(А15=1;2;3)=2.

Если поменять значение ячейки А15 на 2, тогда получим: =ЕСЛИ(А15=1;2;3)=3.

С помощью функции ЕСЛИ строят древо решения:

Формула для древа будет следующая:

ЕСЛИ(А22=1; ЕСЛИ(А23 4. Функция СУММПРОИЗВ(SUMPRODUCT)

Русская версия: СУММПРОИЗВ(Массив 1; Массив 2;…)

Английская версия: SUMPRODUCT(Array 1; Array 2;…)

Умножает соответствующие аргументы заданных массивов и возвращает сумму произведений.

Пример: найти сумму произведений

Сумма произведений равна 6+120+504=630

Эти расчеты можно заменить функцией СУММПРОИЗВ.

= СУММПРОИЗВ(Массив 1; Массив 2; Массив 3)

5. Функция СРЗНАЧ (AVERAGE)

Русская версия: СРЗНАЧ (Массив 1; Массив 2;…..)

Английская версия: AVERAGE(Array 1; Array 2;…..)

Рассчитывает среднее арифметическое всех аргументов.

Пример: СРЗНАЧ (1; 2; 3; 4; 5)=3

6. Функция МИН (MIN)

Русская версия: МИН (Массив 1; Массив 2;…..)

Английская версия: MIN(Array 1; Array 2;…..)

Возвращает минимальное значение массивов.

Пример: МИН(1; 2; 3; 4; 5)=1

7. Функция МАКС (MAX)

Русская версия: МАКС (Массив 1; Массив 2;…..)

Английская версия: MAX(Array 1; Array 2;…..)

Обратная функции МИН. Возвращает максимальное значение массивов.

Пример: МАКС(1; 2; 3; 4; 5)=5

8. Функция НАИМЕНЬШИЙ (SMALL)

Русская версия: НАИМЕНЬШИЙ (Массив 1; Порядок k)

Английская версия: SMALL(Array 1, k-min)

Читайте также  Основания и фундаменты 2

Возвращает k наименьшее число после минимального. Если k=1, возвращаем минимальное число.

Пример: В ячейках А1;A5 находятся числа 1;3;6;5;10.

Результат функции =НАИМЕНЬШИЙ (A1;A5) при разных k:

9. Функция НАИБОЛЬШИЙ (LARGE)

Русская версия: НАИБОЛЬШИЙ (Массив 1; Порядок k)

Английская версия: LARGE(Array 1, k-min)

Возвращает k наименьшее число после максимального. Если k=1, возвращаем максимальное число.

Пример: в ячейках А1;A5 находятся числа 1;3;6;5;10.

Результат функции = НАИБОЛЬШИЙ (A1;A5) при разных k:

k=1; результат = 10

k=2; результат = 6

k=3; результат = 5

10. Функция ВПР(VLOOKUP)

Английская версия: VLOOKUP(lookup value, table, column number. <0;1>)

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

Пример: Есть таблица находящаяся в ячейках А1;С4

Нужно найти (ищем в ячейку А6):

1. Возраст сотрудника Иванова (3 столбец)
2. ВУЗ сотрудника Петрова (2 столбец)

Составляем формулы:

1. ВПР(А6; А1:С4; 3;0) Формула ищет значение «Иванов» в первом столбце таблицы А1;С4 и возвращает значение в строке 3 столбца. Результат функции – 22
2. ВПР(А6; А1:С4; 2;0) Формула ищет значение «Петров» в первом столбце таблицы А1;С4 и возвращает значение в строке 2 столбца. Результат функции – ВШЭ

11. Функция ИНДЕКС(INDEX)

Русская версия: ИНДЕКС (Массив;Номер строки;Номер столбца);

Английская версия: INDEX(table, row number, column number)

Ищет значение пересечение на указанной строки и столбца массива.

Пример: Есть таблица находящаяся в ячейках А1;С4

Необходимо написать формулу, которая выдаст значение «Петров».

«Петров» расположен на пересечении 3 строки и 1 столбца, соответственно, формула принимает вид:

12. Функция СУММЕСЛИ(SUMIF)

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

Английская версия: SUMIF(criterion range; criterion; sumrange)

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

Пример: в ячейках А1;C5

1. Количество столовых приборов сделанных из серебра.
2. Количество приборов ≤ 15.

1. Выражение =СУММЕСЛИ(А1:C5;«Серебро»; В1:B5). Результат = 40 (15+25).
2. =СУММЕСЛИ(В1:В5;« 13. Функция СУММЕСЛИМН(SUMIF)

Русская версия: СУММЕСЛИ(диапазон суммирования; диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)

Английская версия: SUMIFS(criterion range; criterion; sumrange; criterion 1; criterion range 1; criterion 2; criterion range 2;)

Суммирует значения в диапазоне, который попадает под определенные критерии.

Пример: в ячейках А1;C5 есть следующие данные

  1. Количество столовых приборов сделанных из серебра, единичное количество которых ≤ 20.

Решение:

    Выражение =СУММЕСЛИМН(В1:В5; С1:С5; «Серебро»; В1:B5;« Заключение

Excel позволяет сократить время для решения некоторых задач, повысить оперативность, а это, как известно, важный фактор для эффективности.

Многие приведенные формулы также используются в финансовом моделировании. Кстати, на нашем курсе «Финансовое моделирование» мы рассказываем обо всех инструментах Excel, которые упрощают процесс построения финансовых моделей.

В статье представлены только часть популярных функции Excel. А еще в Excel есть сотни других формул, диаграмм и массивов данных.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Самые популярные функции Excel и как с ними работать

Функции Excel — это готовые формулы, которые можно использовать для работы с разными значениями. Рассказываем о самых популярных.

В этой статье мы рассмотрим пять базовых функций:

  • СУММ — суммирование значений в ячейках диапазона
  • СРЗНАЧ — вычисление среднего арифметического из выбранных ячеек
  • МИН — определение минимального значения в диапазоне
  • МАКС — определение максимального значения в диапазоне
  • СЧЕТ — подсчёт количества ячеек, содержащих числа

Пишет про управление в Skillbox. Работала координатором проектов в Русском музее, писала для блога агентства
CRM-маркетинга Out of Cloud.

Функция СУММ

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

Как работать с функцией

Чтобы получить общую сумму чисел одного столбца, нужно:

  • Выделить диапазон: столбец или ячейки с числами, которые нужно сложить.
  • Выбрать Вставка → Функции → СУММ или ∑ → СУММ.

Если поменять действия местами, результат будет одинаковым.

Можно использовать второй вариант:

  • Выбрать первую пустую ячейку в конце диапазона.
  • Выбрать ∑ → СУММ.
  • Вписать в свободную ячейку значения нужных диапазонов.

Чтобы не использовать инструмент ∑, можно вручную ввести =СУММ в пустой ячейке в конце нужного диапазона.

На примере показана формула вычисления и получившееся в результате число. Видно, что общая сумма чисел в диапазоне A1:А5 — 100.

Функция СРЗНАЧ

Эта функция рассчитывает среднее арифметическое в выделенном диапазоне чисел.

Как работать с функцией

Вот как можно вычислить среднее значение:

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать ∑ → СРЗНАЧ.
  • Вписать в свободную ячейку значения диапазонов.

На примере видно, что среднее значение в диапазоне A1:А5 равно 20.

Функция МИН

Одна из статистических функций. Помогает определить минимальное значение из выбранного диапазона чисел. То есть найти самое маленькое число.

Как работать с функцией

Чтобы вычислить минимальное значение, нужно:

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать подходящую функцию из списка: ∑ → МИН.
  • Вписать в свободную ячейку значения нужных диапазонов.

На примере показано минимальное значение в диапазоне A1:А5, которое равно 10, и формула его вычисления.

Функция МАКС

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

Как работать с функцией

Максимальное значение можно вычислить так:

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать функцию ∑ → МАКС.
  • Вписать в свободную ячейку значения нужных диапазонов.

Получилось, что максимальное значение в диапазоне A1:A5 равно 30.

Функция СЧЕТ

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

Как работать с функцией

Чтобы вычислить количество ячеек, содержащих числа, нужно сделать следующее.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать функцию ∑ → СЧЕТ.
  • Вписать в свободную ячейку значения нужных диапазонов.

Количество числовых ячеек в диапазоне A1:A5 равно 5.

Заключение

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

Многие боятся Excel, но если научиться им пользоваться, он может упростить жизнь. Например, помочь предпринимателю определить VIP-клиентов. Представьте, что у вас есть таблица в 10 тысяч строк, в которой собраны платежи от разных клиентов за разные даты. Нужно отфильтровать список и оставить только тех, кто платил после 15 марта и не меньше 100 тысяч рублей. А потом из них выбрать тех, чьи платежи были такими не только в марте, но еще в феврале и январе.

Excel — это полезный инструмент, в котором не разобраться за один день. Если вы никогда с ним не работали, то на курсе Skillbox вам расскажут, как правильно использовать эту программу.

Excel + Google-таблицы
с 0 до PRO

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: