Teres-1t.ru

Инженерные решения
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как в Excel посчитать сумму определенных ячеек

Как в Excel посчитать сумму определенных ячеек

Excel – офисная программа от компании Microsoft, которая предназначена для работы с данными в таблицах. С ее помощью можно удобно и быстро проводить операции с числовым и текстовым содержанием ячеек, включая подсчет суммы. Узнать сумму в Экселе возможно несколькими способами, которые рассмотрим в статье.

  1. 1 Как в Экселе посчитать сумму определенных ячеек
  2. 2 Формула суммы в Excel
  3. 3 Функция СУММ в Excel
    1. 3.1 СУММ с помощью горячих клавиш

    Суммирование каждой 2-й, 3-й. N-й ячейки

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

    sum-every-N-row1.png

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

    Если решать проблему "в лоб", то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:

    sum-every-N-row2.png

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

    Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию

    Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF) :

    sum-every-N-row3.png

    Первый аргумент этой функции — диапазон проверяемых ячеек, второй — критерий отбора (слово "Выручка"), третий — диапазон ячеек с числами для суммирования.

    Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF) , которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.

    Способ 2. Формула массива для суммирования каждой 2-й, 3-й . N-й строки

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

    sum-every-N-row4.png

    Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:

    • Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
    • Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
    • Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
    • И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
    • Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.

    Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:

    sum-every-N-row5.png

    А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:

    sum-every-N-row6.png

    И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:

    sum-every-N-row7.png

    Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.

    Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.

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

    sum-every-N-row8.png

    Способ 3. Функция БДСУММ и таблица с условием

    Формулы массива из предыдущего способа — штука красивая, но имеют слабое место — быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой — функцией БДСУММ (DSUM) . Перед использованием эта функция требует небольшой доработки, а именно — создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) — не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM) :

    sum-every-N-row10.png

    Способ 4. Суммирование каждой 2-й, 3-й. N-й строки

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

    sum-every-N-row11.png

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

    Как работает эта формула

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

    Давайте разложим формулу на составные части для понимания ее работы:

    Как суммировать значения между двумя датами

    • Первым делом мы указываем диапазон с данными продаж (B2:B28), среди которого нам нужно выбрать какие значения мы будем суммировать
    • Затем, мы указываем диапазон с данными, к которому будет применяться проверка на соответствие условию. В нашем случае это диапазон с датами (A2:A28)
    • Следующим шагом мы задаем условие по отношению к диапазону с датами, по которому формула должна определить какие данные суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
    • Заключительным шагом мы задаем второе условие к диапазону с датами (A2:A28), по которому формула должна суммировать данные за период меньший или равный 15.06.2018

    Как результат, функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.

    Как собирать кастомное СВО?

    1) Выбираем места в корпусе для крепления компонетов.

    2) Монтируем радиатор. Обычно ставится сверху или на передней панели.

    3) прикручиваем к нему вентиляторы.

    4) Устанавливаем резервуар с помпой. Обычно в середине корпусе. В классике сзади за задней крышкой. Во многих корпусах там даже отверстия под трубки есть и крепежная панель.

    5) Устанавливаем водоблок на видеокарту/ процессор.

    6) Устанавливаем видеокарты с процессором в посадочные места материнской платы.

    7) Соединям трубками и фитингами все компоненты.

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

    Бутылочка для заправки СВО-

    Вот так преобразился ПК после установки кастом СВО —

    Знаки, которые используются: >, <.

    У нас есть 25 строк с названием Пример и номер примера, в таблице. Напротив каждого примера стоит значение (Рис. 6).

    Сумма, если больше, Сумма, если меньше.

    Сумма, если больше.

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

    Напротив каждого примера стоит число от 100 до 1300. Нам нужно просуммировать, только значения, которые больше числа 500.

    Вызываем функцию, как описано выше и выбираем нужный нам диапазон в строке – Диапазон (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.

    Вводим критерий. В строке Критерий, пишем: >500, вручную. Можно скопировать значение критерия с самой таблицы и добавить в ручную только знак >. Кавычки появятся автоматически (Рис. 7).

    Сумма, если больше.

    Нажимаем ОК. Напротив ячейки с словом ИТОГО, появилась сумма чисел больше 500 (Рис. 8)

    Сумма, если больше.

    Сумма, если меньше.

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

    Допустим, нам нужно найти сумму в диапазоне значений (E3:E27) меньше числа 500.

    Алгоритм точно такой же. В критерии указывает знак меньше <, и необходимое значение. В нашем примере 500.

    Как в Excel суммировать ячейки только с определенным значением

    Пример 3. В таблице указаны данные о зарплате сотрудника на протяжении 12 месяцев прошлого года. Рассчитать доходы работника за весенние месяцы.

    Вид таблицы данных:

    Пример 3.

    Весенними месяцами являются месяца с номерами 3, 4 и 5. Для расчета используем формулу:

    Сумма зарплат с 6-го по 12-й месяц является подмножеством множества суммы зарплат с 3-го по 12-й месяц. Разница этих сумм является искомой величиной – суммой зарплат за весенние месяцы:

    формула для расчета 1.

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

    голоса
    Рейтинг статьи
    Читайте так же:
    Циклический алгоритм со счетчиком блок схема
Ссылка на основную публикацию
Adblock
detector