9 способов как используется функция БДСУММ в Excel

Function BDSUMM 9 способов как используется функция БДСУММ в Excel Добрый день!

Продолжу свое стремление описать разнообразие функций в Excel и следующей на рассмотрении у нас функция БДСУММ. Это еще один из представителей функций суммирования, но со своими специфическими условиями. Функция БДСУММ в Excel ищет и суммирует числа в вашей таблице по определенным вами критериям, это ее основное свойство.

Положа руку на сердце могу сказать, что много расчётов и вычислений можно сделать и без нее используя разнообразные функции массивов, функцию СУММЕСЛИМН или СУММПРОИЗВ, но если же вам нужно сделать сложную выборку используя «подстановочные» знаки, то тогда вам точно нужно использовать героя нашей статьи.

Для начала рассмотрим синтаксис, который использует функция БДСУММ в Excel:

=БДСУММ(диапазон вашей базы данных; поле поиска; условие поиска), где

Function BDSUMM 2 9 способов как используется функция БДСУММ в Excel

  • Диапазон вашей базы данных – является обязательным критерием и указывается диапазон, из которого и будут по критериям изыматься нужные вам значения, обязательное условие, что бы первая строка содержала в себе заголовок вашей таблицы;
  • Поле поиска – это обязательный критерий, указывается для определения, по которому полю производить поиск и суммирование чисел. Указывать можно как название поля, то есть текстовое значение, типа «Продукт», «Страна», с обязательным взятием аргумента в кавычки, так и числовое значение, типа, 1,2,3… для определения номера поля или просто указать ссылку на нужное поле, решать вам;
  • Условие поиска – это обязательный аргумент, который содержит в себе диапазон с указанными в нём критериями для суммирования значений. Аналогичная структура таблицы используется при создании расширенного фильтра.

При работе с функцией БДСУММ стоить отметить несколько условий, на которые стоит обращать внимание при работе:

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

Итак, теоретическую часть, я считаю выполненной, приступим теперь к практическому применению функции БДСУММ в своей работе, для этого рассмотрим несколько примеров для выполнения, я примеры сделал по принципу расширенного фильтра, но вместо сбора значений по критерию будет суммирование:

  • С одним числовым критерием;
  • С одним текстовым критерием;
  • Суммирование по двум критериям по разным столбцам;
  • Суммирование по одному из двух условий в одном столбике;
  • Суммирование по одному из двух условий в двух разных столбиках;
  • Суммирование по двум текстовым критериям по двум столбикам;
  • Использование результата формулы для получения критерия отбора и суммирования;
  • Суммирование по трём критериям;
  • Суммирование по текстовому критерию с учётом регистра.

Функция БДСУММ с одним числовым критерием

Итак, для начала рассмотрим простой пример с одним числовым критерием, для этого выберем столбик «Урожай» и укажем что нам надо деревья с урожайность «>=10». Что бы получить результат нам нужна формула такого вида (советую использовать абсолютные ссылки):

=БДСУММ($B$6:$G$12;E6;E2:E3),

где, $B$6:$G$12 диапазон в котором мы будем суммировать, E6 – столбик в котором мы будем суммировать и E2:E3 диапазон в которые мы ввели критерии для суммирования. В итоге формула нашла 3 позиции на общую сумму 34.

Для получения аналогично результата вы можете воспользоваться также такими формулами:

=СУММЕСЛИ(E7:E12;»>=10″)

Function BDSUMM 3 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(E7:E12;E3)

Функция БДСУММ с одним текстовым критерием

Теперь рассмотрим, как ведет себя функция БДСУММ с текстовыми критериями, в общем всё остается так и в предыдущем примере за исключением того как указывается текстовый критерий, а он указывается только в таком виде: =»=с.Серово» и тогда результат у вас получится, иначе формула не сможет распознать ваш критерий. Теперь подставляем этот критерий в формулу и получаем:

=БДСУММ($B$6:$G$12;E6;C2:C3), как видим произошло только изменения диапазона критерия.

Для получения схожего результата вам пригодится функция СУМЕСЛИ:

Function BDSUMM 4 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(C7:C12;»с.Серово»;E7:E12)

Суммирование по двум критериям по разным столбцам

Пример усложняется применением двух критериев, но ничего принципиально нового мы применять не будем, укажем текстовый критерий «с.Серово» и числовой критерий «>=10», оставив поле суммирование «Урожай», мы получим изменение формулы только по последнему аргументу, как результат. Теперь наша формула будет выглядеть так:

=БДСУММ($B$6:$G$12;E6;C2:E3), снова видите изменения только адреса диапазона критерия.

Альтернативный вариант можно получить с помощью функции СУММЕСЛИМН и так:

=СУММЕСЛИМН(E7:E12;C7:C12;C3;E7:E12;E3)

Function BDSUMM 5 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИМН(E7:E12;C7:C12;»с.Серово»;E7:E12;»>=10″)

Суммирование по одному из двух условий в одном столбике

Рассмотрим еще один вариант как применяется функция БДСУММ, но сейчас будем использовать не единичный критерий, а двойной, но для одного поля. Обязательно два критерия должны быть в разных строках. Суть формулы сводится к тому что, она проходит один и тот же диапазон два раза считая каждый из критериев отдельно. Для этого примера формула будет выглядеть так:

=БДСУММ($B$6:$G$12;E6;C2:C4), здесь снова изменяем диапазон критерия, но не в ширину, а в высоту.

Также заменителем, вы можете использовать сумму функции СУММЕСЛИ:

Function BDSUMM 6 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(C7:C12;C3;E7:E12)+СУММЕСЛИ(C7:C12;C4;E7:E12).

Суммирование по одному из двух условий в двух разных столбиках

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

Function BDSUMM 7 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;C2:D4), принцип формирования формулы сохраняется, окромя диапазона критерия, который включает в себя три строки: заголовок и два критерия.

Суммирование по двум текстовым критериям по двум столбикам

В данном примере работы БДСУММ в Excel рассмотрим практически полный аналог ранее рассматриваемого примера, когда были два критерия в двух столбика, но там были числовой и текстовый критерий, а здесь рассмотрим суммирование по двум текстовым критериям и по двум столбикам. Используем критерии «=»=с.Иваново»» и «=»=Вишня»», которые мы и укажем в диапазоне критериев. Значит наша формула будет иметь следующий вид:

Function BDSUMM 8 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;B2:C3).

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

В данном примере функции БДСУММ я использую формулу для определения критерия отбора и суммирования, в данном случае найдем какие же деревья нам приносят доход и для определения условного аргумента возьмем среднее значение по продажам плодов с деревьев и всё что выше среднего показателя нас интересует. Для определения среднего значение которое станет нашим критерием «ИСТИНА» мы создаем формулу статистического направления:

Function BDSUMM 9 9 способов как используется функция БДСУММ в Excel

=G7>СРЗНАЧ($G$7:$G$12), не забываем об абсолютных ссылок для закрепления диапазона, что бы при переборе формулой значений они не сползли вниз, а вот значение G7 должно скользить по всему диапазону для определения «ЛОЖЬ» это или «ИСТИНА». Очень важно еще то чтобы названия заголовков не дублировались, имели отличие, поэтому поле критериев я назову «Среднее». И тогда формула начнёт работать, она переберет весь диапазон $G$7:$G$12 на наличие среднего значения и при получении положительного результата «ИСТИНА» она будет суммировать. С этой работой справится формула следующего вида:

=БДСУММ($B$6:$G$12;G6;$G$2:$G$3)

А если вам очень интересно альтернативное решение вопроса, то тогда испробуйте вариант с функцией СУММЕСЛИ в таком виде:

Function BDSUMM 10 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ($G$7:$G$12;»>»&СРЗНАЧ($G$7:$G$12))

Функция БДСУММ по трём критериям

В этом примере посчитаем среднее по продажам с выращенных плодов в двоих сёлах: «с.Иваново» и «с.Уютное». Основную идею отбора по критериям я уже описывал, поэтому повторятся не буду, просто скажу, что это будет соединение ранее рассмотренных критериев. Для получения результата нам нужна функция БДСУММ в таком виде:

Function BDSUMM 11 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;G6;$C$2:$G$4)

Суммирование по текстовому критерию с учётом регистра

Как я упоминал ранее, функция БДСУММ может производить поиск не только с подстановочными символами, но и с учётом регистра букв, вот сейчас именно такой вариант. Для начала определим условие по отбору критерия, если встречается название «с.ИВАНОВО», заглавными, то мы производим суммирование, для определения этого критерия нам нужна формула:

Function BDSUMM 12 9 способов как используется функция БДСУММ в Excel

=СОВПАД(«с.ИВАНОВО»;C7) А вот теперь мы можем прописать функцию БДСУММ, которая проверит диапазон на наличие указанного критерия и при получении значения «ИСТИНА» произведет суммирование. В примере я указал специально один раз по условию, и как видим, формула успешно отобрала все населенные пункты и нашла нужный и получили результат «9». Для этого была использована формула:

Function BDSUMM 13 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;$C$2:$C$3) Ну что же, я думаю, что функция БДСУММ в Excel, мною была описана деталь и во многих подробностях, поэтому вопросов будет мало, а пользы много. Если у вас возникли вопросы пишите комментарии, жду ваши лайки и отзывы. О других функциях вы можете ознакомиться в «Справочнике функций» на моем сайте.

Успехов вам в ваших делах!

«За деньги можно, конечно, купить очаровательного пса, но никакие деньги не смогут заставить его радостно вилять хвостом.
»
Д. Биллингс

Функция СУММ (SUM) в Excel

СУММ – самая простая и часто используемая функция. Синтаксис очень простой.

число1;число2;… это ссылки на ячейки или на целый диапазон.

Если диапазон суммирования имеет разрывы, то нужные участки следует выделять, удерживая клавишу Ctrl.

Суммирование функцией СУММ

Так как функция суммирования используется очень часто, то есть несколько способов ее вызвать. Стандартный вариант – через Мастер функций. Для вызова Мастера функций нужно стать в ячейку (то есть сделать ее активной), где будет прописана формула, затем нажать кнопку fx (слева от строки формул) и выбрать нужную функцию. На рисунке видно, где что нажимать:

Мастер функций

Кому такой способ не нравится, в панели инструментов на закладках Главная и Формулы предусмотрена специальная кнопочка — Автосумма.

Кнопка автосуммы

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

Однако истинные мастера Excel выбирают горячие клавиши. Автосумму можно вызвать комбинацией клавиш Alt + = (Альт и равно). Ниже показано, что происходит на экране при использовании автосуммы: активировали ячейку B21, нажали Alt + = (или кнопку на панели).

Автосумма

Диапазон автоматически выделился – это видно по контуру моргающей рамочки. Формула суммирования появилась как в ячейке, так и в строке формул. Осталось нажать Enter и все – сумма готова. Теперь вы знаете, как в Экселе суммировать столбец или отдельные ячейки.

Суммирование и подсчет значений удовлетворяющих определенным критериям

Попробуем подсчитать число значений больших 2 в диапазоне A3:A6 , содержащий значения 4, 8, 6, 1.

Если мы запишем формулу =СУММПРОИЗВ(A3:A6>2) , то получим результат 0. Выделив в Строке формул A3:A6>2 и нажав клавишу F9 , получим массив <ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ>, который говорит, что мы движемся в правильном направлении: в диапазоне A3:A6 больше 2 только первые 3 значения. Хотя значению ИСТИНА соответствует 1, а ЛОЖЬ – 0, мы не получим 3, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (—), что позволит привести массив в числовую форму <1:1:1:0>.

Итак, задача подсчета значений больше 2 решается следующим образом: =СУММПРОИЗВ(—(A3:A6>2))

Вместо двойного отрицания можно использовать другие формулы: =СУММПРОИЗВ(1*(A3:A6>2)) или =СУММПРОИЗВ(0+(A3:A6>2)) или даже так =СУММПРОИЗВ((A3:A6>2)^1) .

Запись >2 является критерием, причем можно указать любые операции сравнения ( =; =).

Критерии можно указывать в форме ссылки: =СУММПРОИЗВ(—(A3:A6>G8)) – ячейка G 8 должна содержать число 2.

Критерии можно применять и к текстовым значениям, например, =СУММПРОИЗВ(—(B3:B6=»яблоки»)) – вернет количество ячеек, содержащие слово яблоки (подробнее, например, в статье Подсчет значений с множественными критериями (Часть 1. Условие И)) .

Функцию СУММПРОИЗВ() можно использовать для отбора значений по нескольким критериям (с множественными условиями). Как известно, 2 критерия могут образовывать разные условия:

  • Условие ИЛИ . Например, подсчитать ячеек содержащих значение яблоки ИЛИ груши =СУММПРОИЗВ((B3:B6=»яблоки»)+ (B3:B6=»груши») ) ;
  • Условие И . Например, подсчитать количество значений больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 )
  • Условие И . Например, найти сумму Чисел больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 A3:A6 ) )

В файле примера приведены решения подобных задач.

Задача3 (2 критерия Дата)

Другой задачей может быть нахождение суммарных продаж за период (см. файл примера Лист “2 Даты” ). Используем другую исходную таблицу со столбцами Дата продажи и Объем продаж .

Формулы строятся аналогично задаче 2: = СУММЕСЛИМН(B6:B17;A6:A17;”>=”&D6;A6:A17;”

Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время

При необходимости даты могут быть введены непосредственно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;”>=15.01.2010″;A6:A17;”

Чтобы вывести условия отбора в текстовой строке используейте формулу =”Объем продаж за период с “&ТЕКСТ(D6;”дд.ММ.гг”)&” по “&ТЕКСТ(E6;”дд.ММ.гг”)

В последней формуле использован Пользовательский формат .

Суммирование значений в строке

Воспроизвести процесс суммирования значений в строке можно следующими методами:

  • арифметическая формула;
  • автосумма;
  • различные функции.
Читайте также:  Вторичная круговая диаграмма в Excel и способы ее построения

Каждый из этих способов подразделяется на дополнительные способы. Разберемся с ними более подробно.

Способ 1: арифметическая формула

Первоначально выясним, как, используя арифметическую формулу, возможно произвести суммирование в строчке. Разберем все на определенном примере. Допустим, у нас есть табличка, в которой отображена выручка 5 магазинов по определенным датам. Названия торговых точек – наименования строчек. Даты – наименования колонок.


1

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

  1. Производим выделение той ячейки, где в дальнейшем будет отражаться результат. Вводим в ячейку символ «=». Жмем ЛКМ по самой первой ячейке в этой строчке, содержащей числовые показатели. Замечаем, что после нажатия координаты ячейки отобразились в ячейке для подсчета результата. Вводим символ «+» и кликаем на следующую ячейку в строке. Продолжаем чередовать символ «+» с координатами ячеек строки первой торговой точки. В результате мы получаем формулу: =B3+C3+D3+E3+F3+G3+H3.


2

  1. После проведения всех манипуляций нажимаем на «Enter».
  2. Готово! Результат вывелся в ячейке, в которую мы вводили формулу для подсчёта суммы.


3

Обратите внимание! Как видите, этот метод является понятным и легким, но в нем существует один неприятный недостаток. Реализация этого метода занимает большое количество времени. Рассмотрим более быстрые варианты суммирования.

Способ 2: автосумма

Использование автоматической суммы – метод, который выполняется намного быстрее, чем вышерассмотренный. Пошаговое руководство выглядит так:

    Используя зажатую ЛКМ, производим выделение всех ячеек первой строки, которые имеют числовые данные. Передвигаемся в раздел «Главная», располагающийся в верхней части интерфейса табличного процессора. Находим блок команд «Редактирование» и кликаем на элемент под названием «Редактирование».


4

Рекомендация! Альтернативный вариант – переходим в раздел «Формулы» и нажимаем на кнопку «Автосумма», находящуюся в блоке «Библиотека функций». Третий вариант – использование комбинации клавиш «Alt» + «=» после выделения ячейки.


5

  1. Вне зависимости от того, какой вариант вы применили, правее от выделенных ячеек появилось числовое значение. Это число является суммой показателей строки.


6
Как вы заметили, этот способ производит суммирование в строчке намного быстрее, чем вышеописанный. Главный недостаток – результат выводится только правее от выбранного диапазона. Чтобы результат отображался в любом выбранном месте, необходимо воспользоваться другими методами.

Способ 3: функция СУММ

Использование интегрированной функции табличного процессора под названием СУММ не имеет недостатков, как у рассмотренных ранее способов. СУММ – математическая функция. Задача оператора – суммирование числовых значений. Общий вид оператора: =СУММ(число1;число2;…).

Важно! Аргументами этой функции могут являться как числовые значения, так и координаты ячеек. Максимальное число аргументов – 255.

Пошаговое руководство выглядит следующим образом:

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


7

  1. На экране отобразилось небольшое окошко под названием «Мастер функций». Раскрываем список около надписи «Категория:» и выбираем элемент «Математические». Чуть ниже в перечне «Выберите функцию:» находим оператор СУММ и щелкаем на него. После проведения всех манипуляций щелкаем на кнопочку «ОК», располагающуюся в нижней части окна.


8

  1. На дисплее появилось окошко под названием «Аргументы функции». В пустое поле «Число1» вводим адрес строки, значения в которой необходимо сложить. Для осуществления этой процедуры ставим указатель в эту строчку, а потом, используя зажатие ЛКМ, производим выделение всего диапазона с числовыми значениями. После проведения всех манипуляций щелкаем на «ОК».


9

  1. Готово! В выбранной изначально ячейке отобразился результат суммирования.


10

Функция СУММ(SUM) не работает

Иногда случается так, что оператор СУММ не работает. Основные причины неисправности:

  • неправильный формат чисел (текстовый) при импорте данных;
  • наличие скрытых символов и пробелов в ячейках с числовыми значениями.

Стоит отметить! Числовые значения всегда выравниваются по правой стороне, а текстовая информация – по левой.

Как найти сумму наибольших (наименьших) значений

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


11

Оператор НАИБОЛЬШИЙ позволяет возвратить максимальный показатель из выбранных данных. 2-й аргумент указывает, какой именно показатель необходимо вернуть. В нашем конкретном примере формула выглядит так: =СУММ(НАИБОЛЬШИЙ(B2:D13;<1;2;3>)).

Таким же образом работает и поиск наименьшего значения, только вместо оператора НАИБОЛЬШИЙ используется функция НАИМЕНЬШИЙ. Формула выглядит следующим образом: =СУММ(НАИМЕНЬШИЙ(B2:D13;<1;2;3>)).

Растягивание формулы/функции суммирования на другие строки

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

  1. Производим подсчет суммы одним из вышеописанных способов. Перемещаем указатель мышки на нижнюю правую рамку ячейки с выведенным результатом. Курсор примет вид небольшого плюсика темного цвета. Зажимаем ЛКМ и растягиваем формулу до самого низа таблички.


12

  1. Готово! Мы вывели итоговые результаты по всем названиям. Мы добились такого результата благодаря тому, что при копировании формулы адреса смещаются. Смещение координат происходит из-за того, что адреса являются относительными.


13

  1. Для 3-й строчки формула имеет вид: =B3+C3+D3+E3+F3+G3+H3.


14

Как вычислить сумму каждой N-ой строки.

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


15

Задача: произвести расчёт еженедельной прибыли за каждую неделю. Оператор СУММ позволяет суммировать данные не только в диапазоне, но и в массиве. Здесь необходимо использование вспомогательного оператора СМЕЩ. В операторе СМЕЩ указывается некоторое количество аргументов:

  1. Первая точка. Ячейка С2 вводится в виде абсолютной ссылки.
  2. Количество шагов вниз.
  3. Количество шагов вправо.
  4. Число шагов вниз.
  5. Количество столбиков в массиве. Попадание в последнюю точку массива показателей.

В конечном итоге мы получаем следующую формулу для первой недели: =СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1)). В результате оператор сумм произведет суммирование всех пяти числовых значений.

3-D сумма, или работаем с несколькими листами рабочей книги Excel

Для подсчета чисел из одинаковой формы диапазона на некотором количестве рабочих листов необходимо использовать особый синтаксис, который называется «3D-ссылкой». Допустим, на всех рабочих листах книги располагается табличка с информацией за неделю. Нам необходимо соединить это все в одно целое и вывести в месячный показатель. Для начала вам необходимо посмотреть следующий видеоролик:

У нас есть четыре идентичных таблички. Обычный способ подсчета прибыли выглядит так: =СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8). Здесь в роли аргументов выступают диапазоны ячеек.

Формула 3D-суммы выглядит следующим образом: =СУММ(неделя1:неделя4!B2:B8). Здесь говорится о том, что производится суммирование в диапазонах В2:В8, которые располагаются на рабочих листах: неделя (от 1 до 4). Происходит пошаговое увеличение номера рабочего листа на единицу.

Результат ввода ячеек Таблица для примера Выделяем столбец Ввод ячеек

Функция СУММ (SUM) в Excel

СУММ – самая простая и часто используемая функция. Синтаксис очень простой.

число1;число2;… это ссылки на ячейки или на целый диапазон.

Если диапазон суммирования имеет разрывы, то нужные участки следует выделять, удерживая клавишу Ctrl.

Так как функция суммирования используется очень часто, то есть несколько способов ее вызвать. Стандартный вариант – через Мастер функций. Для вызова Мастера функций нужно стать в ячейку (то есть сделать ее активной), где будет прописана формула, затем нажать кнопку fx(слева от строки формул) и выбрать нужную функцию. На рисунке видно, где что нажимать:

Кому такой способ не нравится, в панели инструментов на закладках Главная и Формулы предусмотрена специальная кнопочка — Автосумма.

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

Однако истинные мастера Excel выбирают горячие клавиши. Автосумму можно вызвать комбинацией клавиш Alt + = (Альт и равно). Ниже показано, что происходит на экране при использовании автосуммы: активировали ячейку B21, нажали Alt + = (или кнопку на панели).

Диапазон автоматически выделился – это видно по контуру моргающей рамочки. Формула суммирования появилась как в ячейке, так и в строке формул. Осталось нажать Enter и все – сумма готова. Теперь вы знаете, как в Экселе суммировать столбец или отдельные ячейки.

3 способа подсчитать итоги по условию в Excel

Те, кто много работает с разными данными, рано или поздно сталкиваются с необходимостью подсчитать итоговые суммы по одному или нескольким условиям. Например, из базы подневных расходов вытащить помесячные суммы. Или из базы продаж собрать итоги по городу, году и менеджеру и т.д.

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

Сегодня мы рассмотрим 3 способа это сделать:

1) Функция СУММЕСЛИМН

2) Функция СУММПРОИЗВ

3) Сводная таблица

Но сначала поделимся с Вами небольшой хитростью. В итоговом своде в ячейках с названиями месяцев на самом деле введен не текст («Январь», «Февраль» и т.д.), а даты первого дня каждого месяца (01.01.2017, 01.02.2017 и т.д.), а уже после к этим ячейкам применен пользовательский формат «ММММ».

Чтобы применить такой формат, выделите нужные ячейки, нажмите Ctrl+1 , выберите вкладку Число , найдите пункт « (все форматы) » и в строку Тип введите четыре прописные буквы М.

Этот небольшой трюк впоследствии нам очень поможет при подсчете итоговых сумм.

Способ 1. Функция СУММЕСЛИМН

Один из очевидных способов решения задачи — использование специальной функции суммирования по нескольким условиям. Это умеет делать функция СУММЕСЛИМН. Она суммирует значения заданного диапазона только в тех строках/столбцах, в которых выполняются заданные условия.

К сожалению, она умеет воспринимать диапазоны условий только в том виде, в котором они представлены на листе, и не может «на лету» обработать их. Это значит, что если нам нужно свести данные по месяцам, то функция СУММЕСЛИМН требует наличия дополнительной колонки с месяцем. Добавим колонку «Номер месяца», в которой пропишем формулу

и протянем ее вниз на всю высоту таблицы. Получим столбец с порядковым номером месяца (определяется по дате в первом столбце).

Теперь в ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:

$C:$C — столбец «Сумма затрат» (первым указывается тот диапазон, итоги по которому нужно подсчитать).

$D:$D — столбец проверки первого условия («Номер месяца»).

МЕСЯЦ(H$2) — первое условие. Ячейка H2 это «Январь». Так как мы вначале ввели туда 01.01.2017, а потом просто применили числовой формат, мы можем обработать эту ячейку функцией МЕСЯЦ и узнать порядковый номер месяца (и тогда функция сможет сравнить порядковый номер месяца в H2 и порядковые номера в столбце «Номер месяца»).

$B:$B — столбец проверки второго условия («Статья»).

$G3 — второе условие. Ячейка с названием статьи затрат, по которой подводим итог.

Обратите внимание на закрепление ссылок. Это сделано для того, чтобы формулу можно было копировать. После ввода формулы, в ячейке H3 будет подсчитан итог по Январю и статье 1. Скопируйте формулу в другие ячейки и получите нужный результат (при копировании в другие кварталы, не забудьте перетянуть ссылку на строку месяцев, как показано на гифке ниже).

Способ 2. Функция СУММПРОИЗВ

Избежать создания доп.столбца (как в первом способе) можно путем применения функции СУММПРОИЗВ. Ее особенность в том, что она может обрабатывать внутри себя массивы данных, но при этом не требует ввода через Ctrl+Shift+Enter, то есть формально не является формулой массива.

В ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:

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

Разберем пошагово, как эта формула работает:

  • Первая часть формулы (МЕСЯЦ($A$2:$A$1000)=МЕСЯЦ($H2)) делает следующее. Она берет диапазон А2:A1000 и к каждой ячейке применяет функцию МЕСЯЦ, то есть из каждой даты получает номер месяца. Затем каждый из полученных номеров сравнивается с номером месяца в ячейке H2 (это наш Январь в итоговом своде). Результат такого сравнения — столбец из значений ИСТИНА (если номера совпали) и ЛОЖЬ (если не совпали). Все эти вычисления происходят внутри формулы и не выносятся на лист. Обратите внимание, что сравнение нужно обязательно заключать в скобки!

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

  • На завершающем этапе функция складывает значения полученного столбца и выдает итоговую сумму.
Читайте также:  Функция ФИКСИРОВАННЫЙ для округления котировок курсов в Excel

Правила работы с функцией при подобных расчетах:

  • не указывать целые столбцы и строки в качестве аргументов;
  • перемножаемые диапазоны-аргументы должны быть равны (А2:А1000 и B2:B1000, например);
  • все выражения сравнения нужно заключать в скобки.

Способ 3. Сводная таблица

Третий способ — подсчет итогов через сводную таблицу. Поместите курсор в любую ячейку исходной базы, далее нажмите «Вставка» — «Сводная таблица». Для удобства, можно вставить таблицу на лист с базой и итоговым сводом.

Поместите поле «Дата» в область строк, поле «Статьи» в область столбцов, а поле «Сумма затрат» в область значений, как показано на рисунке ниже.

Если Excel сразу не сгруппировал даты помесячно, то кликните на столбце с датами в сводной таблице правой кнопкой мыши и выберите «Группировать» с шагом «Месяцы». Для красоты можете применить к таблице встроенный или собственный стиль. В результате получится примерно такая таблица.

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

  • простое копирование и вставка. Подходит, если сводная не будет обновляться и менять размеры (не будут добавляться новые статьи);
  • использование функции ИНДЕКС, для извлечения данных из сводной. Про эту функцию мы недавно подробно рассказывали . Этот способ подойдет, если сводная будет обновляться, но новые статьи не будут появляться;
  • использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Самый интересный способ, который позволяет гибко менять сводную и всё равно подтягивать данные. Разберем его подробнее.

Встаньте в ячейку Января и Статьи 1 итогового свода и попробуйте сослаться на соответствующую ячейку сводной таблицы. Скорее всего, Excel вместо простой ссылки, вроде =А15, вставит огромную формулу

Она то нам и нужна. Если формула не появилась, Вы можете ввести ее вручную или включить в настройках. Кликните на сводной таблице, найдите на ленте вкладку «Анализ», нажмите маленькую стрелочку рядом с кнопкой «Параметры» и поставьте галочку «Создать GetPivotData»:

Вернемся к функции. Она имеет 2 обязательных аргумента и дополнительные.

1) Поле сводной таблицы, которое находится в области значений. В нашем случае это поле «Сумма затрат». Можете оставить в аргументе этот текст, а можете сослаться на шапку соответствующего столбца базы, чтобы сократить формулу;

2) Адрес любой ячейки сводной таблицы. Указывается на случай, если на листе их несколько и Excel должен понять, с какой именно работать;

3) Дополнительные аргументы парные. Они состоят из названия поля, по которому задается условие отбора, и самого условия (похоже на функцию СУММЕСЛИМН).

В нашем случае нужно указать поле «Дата», в качестве условия для него — месяц. Так как в сводной месяц представлен в виде текста «янв», «фев» и т.д., нам нужно превратить наш заголовок в такой же текст. Так как мы вводили туда даты (делали это в начале статьи), получить нужное сочетание поможет формула

=ТЕКСТ(H2;»МММ»), где H2 — ячейка с месяцем в итоговом своде

Вторая пара условий — поле «Статья» и ссылка на название статьи в итоговом своде. В результате для ячейки Января и Статьи 1 получим формулу:

В данном примере сводная начинается в ячейке $M$2. Формулу можно копировать, как и все предыдущие.

Файл-пример, в котором реализованы все 3 способа подсчета, Вы можете скачать по этой ссылке .

Для закрепления материала, можете посмотреть ролик по данному уроку на нашем YouTube канале.

Поддержать наш проект и его дальнейшее развитие можно вот здесь .

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

СУММЕСЛИ

Чтобы суммировать значения ячеек на основе одного критерия (например, больше 9), используйте функцию СУММЕСЛИ (SUMIF). В данном случае для проверки условия и суммирования используется один столбец, поэтому в функции достаточно заполнить всего два аргумента:

СЧЁТ и СУММ в Excel

Чтобы суммировать значения ячеек на основе одного критерия (например, “green”), также используйте функцию СУММЕСЛИ (SUMIF). В данном случае для проверки условия и суммирования используются разные столбцы, поэтому в функции нужно заполнить три аргумента, последний – это диапазон для суммирования.

СЧЁТ и СУММ в Excel

Суммирование с условием в Excel

Часто нужно просуммировать только те значения, которые отвечают какому-то условию. Для этого используем функцию =СУММЕСЛИ(Диапазон, Критерий, [Диапазон суммирования]) . Как видим, для нее можно задать 3 аргумента:

  • Диапазон – массив, в котором происходит проверка условия. Это обязательный аргумент;
  • Критерий – условие для отбора значений (обязательный аргумент). Ячейки из «Диапазона» будут проверены на соответствие этому критерию. Если вы ищете равенство какому-то числу – просто запишите это число в качестве аргумента. Остальные критерии заключаются в кавычки. Например:
    • 12 – значение равно двенадцати
    • «>12» — значение больше двенадцати
    • «Обувь» — значение равно «Обувь»

    В этом примере я сложил оклады всех сотрудников отдела логистики:

    Сумма с условием в Эксель

    Сумма с условием

    А что, если нужно задать несколько условий? Эксель прекрасно справится с такой задачей, используем функцию:

    СУММЕСЛИМН(диапазон суммирования ; диапазон условия1; условие1; диапазон условия2 ; условие2 ; …) .

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

    Сумма с несколькими условиями в Excel

    Сумма с несколькими условиями

    Функция СУММ возвращает сумму предоставленных значений. Эти значения могут быть числами, ссылками на ячейки, диапазонами, массивами и константами в любой комбинации. SUM может обрабатывать до 255 отдельных аргументов.

    Функция СУММ — это встроенная функция Excel, относящаяся к категории математических / триггерных функций. Как функцию рабочего листа, функцию СУММ можно ввести как часть формулы в ячейку рабочего листа.

    Функция СУММ excel с примерами

    На изображении выше у нас в столбце А расположены значения, допустим вы хотите сложить значения 2 и 3 строки для этого пишем в ячейке которой вы хотите увидеть результат =СУММ(A2;A3).
    Если вы хотите суммировать весь диапазон значений то указывайте его через двоеточие, например в нашем случае это будет =СУММ(A2:A6).

    Для того чтобы не писать какие ячейки вы хотите суммировать, вы набираете =СУММ( или выбираете эту функцию в окне «Вставка функции» найти его можно на панели меню выбираете «Формулы»->»Вставка функции»

    Функция СУММ excel с примерами

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

    Cумма столбца в эксел

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

    Функция СУММ excel с примерами

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

    Если столбец, который вы хотите суммировать, имеет переменное количество строк (т. е. Новые ячейки могут быть добавлены, а существующие могут быть удалены в любое время), вы можете просуммировать весь столбец, указав ссылку на столбец, без указания нижней или верхней границы , Например: =SUM(B:B), но в этом случае вы не должны помещать формулу «Сумма столбца» в столбец, который вы хотите суммировать потому что это создаст циклическую ссылку на ячейку, и ваша формула СУММ вернет 0.

    Функция СУММ excel с примерами

    Автосумма столбца

    Если в конце столбца вам необходимо вывести сумму этого столбца то вам необходимо выполнить следующие шаги:

    1. Выделить ячейку в которой будет сумма строк выше
    2. Выбираем в меню «Главная» значек Сумма
    1. После выбора программа выделяет нужный диапазон, проверяем все ли правильно и нажимаем Enter.

    Функция СУММ excel с примерами

    Более быстрый способ выполнить автосумму в Excel — использовать

    Alt + =,

    Просто удерживайте клавишу Alt, нажмите клавишу со знаком равенства, а затем нажмите Enter, чтобы завершить автоматически вставленную формулу суммы.

    Сумма строки эксел

    Чтобы суммировать всю строку с неопределенным числом столбцов, укажите ссылку на всю строку в формуле Excel СУММ, например:

    Помните, что вы не должны вводить эту формулу «Сумма строки» в любой ячейке той же строки, чтобы избежать создания циклической ссылки, поскольку это приведет к неправильному вычислению, если таковое имеется:

    СУММЕСЛИ – условная сумма в Excel

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

    Например, следующая формула суммируем только положительные числа диапазона A1:A10. Обратите внимание, что условие заключено в двойные кавычки.

    Суммирование в Excel

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

    Суммирование в Excel

    Меняем условие, меняется и результат:

    Суммирование в Excel

    Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.

    Суммирование в Excel

    Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?

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

    В следующем примере мы просуммируем общую стоимость всех проданных фруктов. Для этого воспользуемся следующей формулой:

    Суммирование в Excel

    Нажав Enter получим результат:

    Суммирование в Excel

    Если одного условия Вам не достаточно, то Вы всегда можете воспользоваться функцией СУММЕСЛИМН, которая позволяет осуществлять условное суммирование в Excel на основе нескольких критериев.

    Суммирование – это одно из основных действий, которое пользователь выполняет в Microsoft Excel. Функции СУММ и СУММЕСЛИ созданы, чтобы облегчить эту задачу и дать пользователям максимально удобный инструмент. Надеюсь, что этот урок помог Вам освоить базовые функции суммирования в Excel, и теперь Вы сможете свободно применять эти знания на практике. Удачи Вам и успехов в изучении Excel!

    Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

    Можно, конечно, составлять формулы в ручном режиме, печатая «=A1+B1+C1» и т.п. Но в Excel есть более быстрые и удобные инструменты.

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

    Что нужно сделать, чтобы посчитать сумму определенных ячеек:

    1. сначала выделяем ячейки (см. скрин ниже);
    2. далее открываем раздел «Формулы»;
    3. следующий шаг жмем кнопку «Автосумма». Под выделенными вами ячейками появиться результат из сложения;
    4. если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу «=СУММ(E2:E7)».
    5. таким образом, написав формулу «=СУММ(xx)», где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк…

    Автосумма выделенных ячеек

    Как посчитать сумму с каким-нибудь условием

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

    Я в своей таблицы буду использовать всего 7 строк (для наглядности), реальная же таблица может быть намного больше. Предположим, нам нужно посчитать всю прибыль, которую сделал «Саша». Как будет выглядеть формула:

    1. «=СУММЕСЛИМН(F2:F7;A2:A7;»Саша»)» — (прим.: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге). Так же обратите внимание, что Excel при вбивании начала формулы (к примеру «СУММ…»), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
    2. F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
    3. A2:A7 — это столбик, по которому будет проверяться наше условие;
    4. «Саша» — это условие, те строки, в которых в столбце A будет «Саша» будут сложены (обратите внимание на показательный скриншот ниже).

    Сумма с условием

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

    Функция СУММПРОИЗВ может использоваться и в качестве формулы массива. Когда это нужно делать? Прежде всего, когда необходимо осуществлять операции с большим количеством значений. В этом случае можно не использовать формулу массива СУММ , а просто заменить ее функцией СУММПРОИЗВ . Есть несколько примеров, как можно это использовать на практике.

    =СУММПРОИЗВ(–ЕПУСТО(D2:D23)) – в этом случае ищутся пустые ячейки в диапазоне, подсчет которых осуществляется потом.

    =СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) – здесь осуществляется отбор значений, которые больше среднего арифметического, после чего подсчитывается их сумма.

    8 простых способов как посчитать в Excel сумму столбца

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

    • Как суммировать весь столбец либо строку.
    • Суммируем диапазон ячеек.
    • Как вычислить сумму каждой N-ой строки.
    • Сумма каждых N строк.
    • Как найти сумму наибольших (наименьших) значений.
    • 3-D сумма, или работаем с несколькими листами рабочей книги Excel.
    • Поиск нужного столбца и расчет его суммы.
    • Сумма столбцов из нескольких таблиц.

    Как суммировать весь столбец либо строку.

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

    Если поставить формулу суммы в G2

    то получим общую выручку по первому отделу.

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

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

    Все сказанное выше в полной мере относится и к работе со строками.

    Но суммирование столбца целиком встречается достаточно редко. Гораздо чаще область, с которой мы будем работать, нужно указывать более тонко и точно.

    Суммируем диапазон ячеек.

    Важно научиться правильно указать диапазон данных. Вот как это сделать, если суммировать продажи за 1-й квартал:

    Формула расчета выглядит так:

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

    В формуле последовательно перечисляем несколько диапазонов:

    Естественно, их может быть не два, а гораздо больше: до 255 штук.

    Как вычислить сумму каждой N-ой строки.

    В таблице расположены повторяющиеся с определенной периодичностью показатели — продажи по отделам. Необходимо рассчитать общую выручку по каждому из них. Сложность в том, что интересующие нас показатели находятся не рядом, а чередуются. Предположим, мы анализируем сведения о продажах трех отделов помесячно. Необходимо определить продажи по каждому отделу.

    Иначе говоря, нужно, двигаясь вниз, взять каждую третью строчку.

    Это можно сделать двумя способами.

    Первый – самый простой, «в лоб». Складываем все цифры нужного отдела обычной математической операцией сложения. Выглядит просто, но представьте, если у вас статистика, предположим, за 3 года? Придется обработать 36 чисел…

    Второй способ – для более «продвинутых», но зато универсальный.

    И затем нажимаем комбинацию клавиш CTRL+SHIFT+ENTER, поскольку используется формула массива. Excel сам добавит к фигурные скобки слева и справа.

    Как это работает? Нам нужна 1-я, 3-я, 6-я и т.д. позиции. При помощи функции СТРОКА() мы вычисляем номер текущей позиции. И если остаток от деления на 3 будет равен нулю, то значение будет учтено в расчете. В противном случае – нет.

    Для такого счетчика мы будем использовать номера строк. Но наше первое число находится во второй строке рабочего листа Эксель. Поскольку надо начинать с первой позиции и потом брать каждую третью, а начинается диапазон со 2-й строчки, то к порядковому номеру её добавляем 1. Тогда у нас счетчик начнет считать с цифры 3. Для этого и служит выражение СТРОКА(C2:C16)+1. Получим 2+1=3, остаток от деления на 3 равен нулю. Так мы возьмем 1-ю, 3-ю, 6-ю и т.д. позиции.

    Формула массива означает, что Excel должен последовательно перебрать все ячейки диапазона – начиная с C2 до C16, и с каждой из них произвести описанные выше операции.

    Когда будем находить продажи по Отделу 2, то изменим выражение:

    Ничего не добавляем, поскольку первое подходящее значение как раз и находится в 3-й позиции.

    Аналогично для Отдела 3

    Вместо добавления 1 теперь вычитаем 1, чтобы отсчет вновь начался с 3. Теперь брать будем каждую третью позицию, начиная с 4-й.

    Ну и, конечно, не забываем нажимать CTRL+SHIFT+ENTER.

    Примечание. Точно таким же образом можно суммировать и каждый N-й столбец в таблице. Только вместо функции СТРОКА() нужно будет использовать СТОЛБЕЦ().

    Сумма каждых N строк.

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

    Используем то, что СУММ() может складывать значения не только в диапазоне данных, но и в массиве. Такой массив значений ей может предоставить функция СМЕЩ.

    Напомним, что здесь нужно указать несколько аргументов:

    1. Начальную точку. Обратите внимание, что С2 мы ввели как абсолютную ссылку.

    2. Сколько шагов вниз сделать

    3. Сколько шагов вправо сделать. После этого попадаем в начальную (левую верхнюю) точку массива.

    4. Сколько значений взять, вновь двигаясь вниз.

    5. Сколько колонок будет в массиве. Попадаем в конечную (правую нижнюю) точку массива значений.

    Итак, формула для 1-й недели:

    В данном случае СТРОКА() – это как бы наш счетчик недель. Отсчет нужно начинать с 0, чтобы действия начать прямо с ячейки C2, никуда вниз не перемещаясь. Для этого используем СТРОКА()-2. Поскольку сама формула находится в ячейке F2, получаем в результате 0. Началом отсчета будет С2, а конец его – на 5 значений ниже в той же колонке.

    СУММ просто сложит предложенные ей пять значений.

    Для 2-й недели в F3 формулу просто копируем. СТРОКА()-2 даст здесь результат 1, поэтому начало массива будет 1*5=5, то есть на 5 значений вниз в ячейке C7 и до С11. И так далее.

    Как найти сумму наибольших (наименьших) значений.

    Задача: Суммировать 3 максимальных или 3 минимальных значения.

    Функция НАИБОЛЬШИЙ возвращает самое большое значение из перечня данных. Хитрость в том, что второй ее аргумент показывает, какое именно значение нужно вернуть: 1- самое большое, 2 – второе по величине и т.д. А если указать – значит, нужны три самых больших. Но при этом не забывайте применять формулу массива и завершать комбинацией клавиш CTRL+SHIFT+ENTER.

    Аналогично обстоит дело и с самыми маленькими значениями:

    3-D сумма, или работаем с несколькими листами рабочей книги Excel.

    Чтобы подсчитать цифры из одинаковой формы диапазона на нескольких листах, вы можете записывать координаты данных специальным синтаксисом, называемым «3d-ссылка».

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

    Посмотрите на этом небольшом видео, как применяются 3-D формулы.

    Как видите, у нас имеется 4 одинаковых таблицы. Стандартный метод нахождения выручки за месяц –

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

    Мы указываем программе, что нужно произвести вычисления с ячейками B2:B8, находящимися на листах неделя1, неделя2, неделя3, неделя4. Здесь номер листа последовательно увеличивается на 1.

    Важное замечание. Вы можете пользоваться 3D-ссылками и в других случаях. К примеру, выражение

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

    Поиск нужного столбца и расчет его суммы.

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

    Для поиска и выбора конкретного товара используем весьма популярную комбинацию функций ИНДЕКС+ПОИСКПОЗ.

    Расчет суммы в G3 выполним так:

    Итак, комбинация ИНДЕКС+ПОИСКПОЗ должны возвратить для дальнейших расчетов набор чисел в виде вертикального массива, который и будет потом просуммирован.

    Опишем это подробнее.

    ПОИСКПОЗ находит в шапке наименований таблицы B1:D1 нужный продукт (бананы) и возвращает его порядковый номер (иначе говоря, 2).

    Затем ИНДЕКС выбирает из массива значений B2:D21 соответствующий номер столбца (второй). Будет возвращен весь столбик данных с соответствующим номером, поскольку номер строки (первый параметр функции) указан равным 0. На нашем рисунке это будет С2:С21. Остается только подсчитать все значения в этой колонке.

    В данном случае, чтобы избежать ошибок при записи названия товара, мы рекомендовали бы использовать выпадающий список в F3, а значения для наполнения его брать из B1:D1.

    Сумма столбцов из нескольких таблиц.

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

    Для получения итогов сразу по нескольким таблицам также используем функцию СУММ и структурированные ссылки. Такие ссылки появляются при создании в Excel «умной» таблицы.

    При создании её Excel назначает имя самой таблице и каждому заголовку колонки в ней. Эти имена затем можно использовать в выражениях: они могут отображаться в виде подсказок в строке ввода.

    В нашем случае это выглядит так:

    Прямая ссылка Структурированная ссылка (Имя таблицы и столбца)
    B2:B21 Таблица2[Сумма]

    Для создания «умной» таблицы выделим диапазон A1:B21 и на ленте «Главная» выбираем «Форматировать как таблицу».

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

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

    Помимо этого, если используемые вами таблицы содержат строчку итогов, то нашу формулу перепишем так:

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

    Примечание: итоговая строчка в таблице должна быть включена. Если вы отключите её, то выражение вернет ошибку #ССЫЛКА.

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

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

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

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

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

    Сумма, если не равно. СУММЕСЛИ.

    Точное совпадение.

    Формула, как пример, выглядит вот так:

    =СУММЕСЛИ(E4:E18;» <>Белая машина»;F4:F18)

    Используем знаки <> .

    Нам нужно посчитать общую стоимость всех автомобилей, кроме тех, которые белого цвета.

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

    Выбираем ячейку, в которой должна быть сумма стоимости всех автомобилей, кроме автомобилей белого цвета (ИТОГО).

    В Диапазон выбираем диапазон (E4:E18), в котором будет происходить поиск согласно заданного Критерия.

    В Критерий вводим нужный тип машины, в нашем случае это белая машина. Белые машину будут исключены из суммы. Можно ввести вручную, можно предварительно скопировать с таблицы. Не забываем использовать знаки <>. Написание Критерия должно точно совпадать с тем, как он написан в Диапазоне его поиска.

    В Диапазон_суммирования выбираем диапазон ячеек (F4:F18), числа которые будут суммироваться согласно нашего Критерия.

    Нажимаем ОК. Получаем искомую сумму (Рис.3).

    Эксель формула СУММЕСЛИ

    Частичное совпадение.

    Формула, как пример, выглядит вот так:

    =СУММЕСЛИ(E4:E18;» <>*трактор*»;F4:F18)

    Используем знаки <> * .

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

    В Диапазон выбираем диапазон (E4:E18), в котором будет происходить поиск согласно заданного Критерия.

    В Критерий вводим критерий трактор. Так как нам нужно исключить из суммирования стоимость тракторов. А так как трактора разного цвета, мы используем частичное совпадение, которое даст нам возможность не обращать внимание на цвет трактора. Не забываем использовать знаки <> *. Написание Критерия должно точно совпадать с тем, как он написан в Диапазоне его поиска.

    В Диапазон_суммирования выбираем диапазон ячеек (F4:F18), числа в которых будут суммироваться согласно нашему Критерию.

    Нажимаем ОК. Получаем искомую сумму (Рис.4).

    Эксель формула СУММЕСЛИ

    Данная статья посвящена нескольким вариантам использования функции СУММЕСЛИ. Описание функции, ее варианты, примеры использования с иллюстрациями можно найти по ссылке: Функция СУММЕСЛИ в MS Excel. Варианты данной функции с описанием и примерами.

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

Ссылка на основную публикацию