Мера которая собирает сумму столбцов
Перейти к содержимому

Мера которая собирает сумму столбцов

  • автор:

Как суммировать данные с разных листов книг Excel

Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1 , которая находится на листе Лист2: =Лист2!С1+12 .

Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1) . В данном случае двоеточие разделяет название первого и последнего листов.

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

  1. Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ( .
  2. Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1 .
  3. Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
  4. Нажмите Enter, и формула будет введена в ячейку.

В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12) .

Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ(‘*’!C1) . Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1) .

Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ(‘Регион*»!C1) . Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1) .

Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.

Использование функции «Автосумма» для суммирования чисел

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для телефонов с Android Еще. Меньше

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

Кнопка

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

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

Формула, созданная нажатием кнопки «Автосумма» на вкладке «Главная»

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Результат автосуммирования в ячейке В7

  • Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
  • Автосумма находится в двух расположениях: домашняя >автосумма и формула >автосуммы.
  • Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
  • Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.
  • Также вы можете суммировать числа путем создания простых формул.

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

Автосумма macOS

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

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

Ячейка автосуммы macOS

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Формула автосуммы macOS

  • Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
  • Автосумма находится в двух расположениях: домашняя >автосумма и формула >автосуммы.
  • Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
  • Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.
  • Также вы можете суммировать числа путем создания простых формул.

На планшете или телефоне с Android

Автосумма в Excel на планшете с Android

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

Автосумма в Excel на планшете с Android

Коснитесь элемента Автосумма.

Сумма в Excel на планшете с Android

Нажмите Сумма.

Флажок

Коснитесь флажка.

Сумма получена

Готово!

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

Excel для веб-автосуммы

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

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

Excel для ячейки веб-автосуммы

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Excel для формулы автосуммы в Интернете

  • Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
  • Автосумма находится в двух расположениях: домашняя >автосумма и формула >автосуммы.
  • Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
  • Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.
  • Также вы можете суммировать числа путем создания простых формул.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).

Данные на листе Excel

Вот формула, с помощью которая можно сопровождать эту формулу:

=СУММЕСС(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)

Результат — значение 14 719.

Рассмотрим каждую часть формулы более подробно.

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

=СУММЕСЛИМН(D2:D11,

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

Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»). Обратите внимание на запятую между аргументами:

=СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;

Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.

=СУММЕСЛИМН(D2:D11;A2:A11,»Южный»;C2:C11,»Мясо»)

Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.

На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).

Использование автозаполнения формул при вводе функции СУММЕСЛИМН

По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.

Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.

Попробуйте попрактиковаться

Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.

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

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

Что следует ввести

Три способа рассчитать накопленную сумму в SQL

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

Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге. Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.

Что у нас есть?

Date Revenue
10.11.2021 1200
11.11.2021 1600
12.11.2021 800
13.11.2021 3000

Что мы хотим найти?

Date Revenue Cumulative Revenue
10.11.2021 1200 1200 ↓
11.11.2021 1600 2800↓
12.11.2021 800 3600 ↓
13.11.2021 3000 6600

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

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

Способ 1 — Идеальный — Используем оконные функции

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

SELECT date, revenue, SUM(revenue) OVER (ORDER BY date asc) as total FROM leftjoin.daily_sales_sample ORDER BY date;

Способ 2 — Хитрый — Решение без оконных функций

Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?
Чтобы провернуть все то же самое без оконных функций, нужно использовать INNER JOIN для присоединения таблицы к себе самой. Так, к каждой строке таблицы мы присоединяем строки, которые соответствуют всем предыдущим датам до текущей даты включительно. В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:

SELECT * FROM leftjoin.daily_sales_sample ds1 INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date ORDER BY ds1.date, ds2.date;

А его результат:

Date 1 Revenue 1 Date 2 Revenue 2
10.11.2021 1200 10.11.2021 1200
11.11.2021 1600 10.11.2021 1200
11.11.2021 1600 11.11.2021 1600
12.11.2021 800 10.11.2021 1200
12.11.2021 800 11.11.2021 1600
12.11.2021 800 12.11.2021 800
13.11.2021 300 10.11.2021 1200
13.11.2021 300 11.11.2021 1600
13.11.2021 300 12.11.2021 800
13.11.2021 300 13.11.2021 300

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

SELECT ds1.date, ds1.revenue, SUM(ds2.revenue) as total FROM leftjoin.daily_sales_sample ds1 INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date GROUP BY ds1.date, ds1.revenue ORDER BY ds1.date;

Способ 3 — Специфический — Решение с помощью массивов в ClickHouse

Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.

SELECT groupArray(date) dates, groupArray(revenue) as revs, groupArrayMovingSum(revenue) AS total FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample ORDER BY date)

Спасибо Дмитрию Титову из Altinity за комментарий про сортировку в подзапросе

Так, мы получим три массива значений:

dates revs total
[’10.11.2021’,’11.11.2021’,’12.11.2021’,’13.11.2021’] [1200, 1600, 800, 300] [1200, 2800, 3600, 3900]

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

SELECT dates, revs, total FROM (SELECT groupArray(date) dates, groupArray(revenue) as revs, groupArrayMovingSum(revenue) AS total FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample ORDER BY date)) as t ARRAY JOIN dates, revs, total;

Бонус — Оконные функции в Clickhouse

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

SELECT date, runningAccumulate(revenue) FROM ( SELECT date, sumState(revenue) AS revenue FROM leftjoin.daily_sales_sample GROUP BY date ORDER BY date ASC ) ORDER BY date

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *