Функция в экселе суммирования переменного числа слагаемых. Суммирование столбцов в Microsoft Excel

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

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

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

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

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

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

У Майка и Сьюзан есть только одна запись в наборе данных.
У Джона и Джорджа есть две записи в наборе данных.

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

Условно подсчет количества

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

У двух человек, Майка и Сьюзан, есть по одной записи.
Два человека, Джон и Джордж, имеют две записи.
Нет ни одного из трех или четырех записей.
Один человек, «Рассвет», имеет пять записей.
В нем нет более пяти записей.

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

Для примера возьмём категорию расходов «Покупки в магазинах». Нам надо, чтобы EXCEL находил все затраты по данной категории в детальной статистике, суммировал расходы по данной категории и записывал полученную сумму в ячейку D10 .

Сначала запишем готовую формулу, которую вставляем в ячейку D10 , а потом начнём разбираться в деталях. Готовая формула выглядит следующим образом (только для нашей статьи):

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

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

=СУММЕСЛИ ($G$5:$G$300 ;("Покупки в магазинах ");$H$5:$H$300 )

Цветом выделены различные условия, чтобы было наглядней. Разберём по порядку. В процессе описания смотрите на картинку выше, чтобы было понятней. Делая снимок специально были захвачены буквы столбцов и цифры строк. Итак, приступаем.

СУММЕСЛИ – этим условием мы говорим, что в ячейку надо записывать сумму значений определённых ячеек, если они соответствуют определённым условиям;
$G$5:$G$300 – здесь мы указываем EXCEL, в каком столбце нам надо искать условие для выборки. В нашем случае поиск происходит в столбце G начиная со строки 5 и заканчивая строкой 300 ;
(«Покупки в магазинах ») – здесь мы указываем искомое условие и по этому условию будут суммироваться значения ячеек, которые мы указываем далее…;
$H$5:$H$300 – здесь мы указываем столбец, из которого будут браться числа для суммирования. В нашем случае значения берутся в столбце H начиная со строки 5 и заканчивая строкой 300.

Подводя итог можно сказать, что EXCEL суммирует только те значения из диапазона H5: H300 , для которых соответствующие значения из диапазона G5: G300 равны «Покупки в магазинах» и записывает результат в ячейку D10 .

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

Пожалуйста, укажите приложение и версию, которые вы используете. Щелкните правой кнопкой мыши и выберите «Формат» в списке. В меню формата выберите «Выравнивание» и установите текст под углом 45 градусов. Нажмите на значок, который говорит «Стоп-листы».

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

Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию .

Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $ . К примеру, в ячейке D10 у нас вписана формула:

=СУММЕСЛИ(G5:G300;("Покупки в магазинах");H5:H300)

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

Далее мы хотим выводить сумму обедов в ячейке D11 . Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11 . Благодаря этому формула будет вставлена в D11 , но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:

=СУММЕСЛИ(G6:G301;("Покупки в магазинах");H6:H301)

Произошло смещение. Если мы скопируем формулу в D12 , то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $ . Такие особенности EXCEL.

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

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

Таблица «Учёт ежемесячных расходов» -

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

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

Способ 1: использование автосуммы

Прежде всего, давайте рассмотрим, как произвести суммирование колонок в Экселе при помощи такого инструмента, как автосумма.

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

Способ 2: использование простой математической формулы

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

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

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

Способ 3: применение функции СУММ

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

СУММ(число1;число2;…)

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

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

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

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

Но существуют ситуации, когда нужно сложить не все колонки таблицы, а только определенные. Ещё больше задача усложняется, если они не граничат друг с другом. Давайте рассмотрим, как производится данный вид сложения при помощи оператора СУММ на примере все той же таблицы. Предположим, что нам нужно сложить только значения столбцов «Магазин 1» , «Магазин 3» и «Магазин 5» . При этом требуется, чтобы результат подсчитывался без выведения промежуточных итогов по колонкам.

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

Давайте решим все проблемы на Вашем компьютере?