покупка
Предположим, у вас есть длинный список данных на вашем листе, и вам нужно просуммировать каждые следующие 5 строк следующим образом: = СУММ (B2: B6), = СУММ (B7: B11) и так далее, а затем поместить результат в другой столбец, как показано на следующем снимке экрана. В этой статье я расскажу о некоторых хороших формулах для решения этой проблемы.
Суммируйте каждые n строк в Excel с помощью формул
В этом примере я суммирую каждые 5 строк данных, пожалуйста, примените следующую формулу:
1. Введите эту формулу в пустую ячейку, в которую вы хотите поместить результат: = СУММ (СМЕЩЕНИЕ ($ 2 млрд; (СТРОКА () — СТРОКА ($ 2 млрд)) * 5,0,5,1))
Функции: В приведенной выше формуле B2 указывает начальный номер строки, которую вы хотите суммировать, и 5 обозначает увеличивающиеся номера строк. Если вы хотите суммировать каждые 10 строк в столбце B, вам просто нужно изменить 5 на 10.
2. Затем перетащите маркер заполнения к ячейкам, пока не отобразится 0. И вы получите нужный результат. Смотрите скриншот:
Ноты:
1. Иногда вам нужно, чтобы результаты для всех пяти строк отображались в последней строке столбца C, как показано на следующем снимке экрана:
В этом случае введите эту формулу: = ЕСЛИ (MOD (СТРОКА (); 5); «»; СУММ (СМЕЩЕНИЕ (B1 . — 5))) (B1 — первое значение ячейки, которое вы хотите просуммировать в столбце B, и 5 — порядковые номера строк) в ячейку C1, а затем перетащите маркер заполнения в ячейки, к которым вы хотите применить эту формулу.
Функции: указанная выше формула может быть успешно применена, если ваши данные начинаются с первой ячейки.
2. Если у вас есть список значений в строке, вы также можете использовать формулу для суммирования всех n столбцов, =SUM(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5)) (A1 ссылка на ячейку, в которой находится ваше первое значение, A3 обозначает ячейку, в которую вы вводите эту формулу, и 5 — порядковые номера столбцов), а затем перетащите маркер заполнения в правые ячейки, пока не отобразится 0, см. снимки экрана:
 |
 |
 |
Суммируйте каждые n строк в Excel с помощью Kutools for Excel
Если у вас есть Kutools for Excel, вы можете вставлять разрывы страниц через каждые n строк, а затем вычислять промежуточные итоги разбиения по страницам для получения результата.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите Кутулс Плюс > печать > Вставить разрыв страницы в каждую строку, см. снимок экрана:
2. В Вставить разрыв страницы в каждую строку В диалоговом окне укажите интервал строк, в который вы хотите вставить разрывы страниц, и разрывы страниц были вставлены в данные каждые 5 строк, см. снимок экрана:
3, Затем нажмите Кутулс Плюс > печать > Промежуточные итоги по страницам, см. снимок экрана:
4. В Промежуточные итоги по страницам В диалоговом окне проверьте заголовок столбца, в котором вы хотите выполнить некоторые вычисления, и выберите нужную функцию, см. снимок экрана:
5. Затем нажмите Ok, все данные каждые 5 строк суммированы, см. снимок экрана:
Демонстрация: сумма/среднее каждые n строк в Excel с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Статьи по теме:
Как суммировать отфильтрованные строки в Excel (с примером)
Самый простой способ получить сумму отфильтрованного диапазона в Excel — использовать следующий синтаксис:
SUBTOTAL( 109 , A1:A10 ) Обратите внимание, что значение 109 — это сокращение для получения суммы отфильтрованного диапазона строк.
В следующем примере показано, как использовать эту функцию на практике.
Пример: суммирование отфильтрованных строк в Excel
Предположим, у нас есть следующий набор данных, содержащий информацию о различных баскетбольных командах:
Далее давайте отфильтруем данные, чтобы показывать только игроков на Mavs или Warriors.
Для этого выделите диапазон ячеек A1:B10.Затем щелкните вкладку « Данные » на верхней ленте и нажмите кнопку « Фильтр ».
Затем щелкните стрелку раскрывающегося списка рядом с « Команда» , снимите флажок рядом с «Селтикс» и нажмите « ОК »:
Данные будут автоматически отфильтрованы, чтобы удалить строки с «Селтикс» в качестве команды:
Если мы попытаемся использовать функцию SUM() для суммирования столбца точек отфильтрованных строк, она фактически вернет сумму всех исходных значений:
Вместо этого мы можем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() :
Эта функция принимает сумму только видимых строк.
Мы можем вручную проверить это, взяв сумму видимых строк:
Сумма очков в видимых рядах: 99 + 94 + 93 + 104 + 109 + 84 = 583 .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Как в 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.
Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию.
Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $. К примеру, в ячейке D10 у нас вписана формула:
=СУММЕСЛИ(G5:G300;(«Покупки в магазинах»);H5:H300)
Далее мы хотим выводить сумму обедов в ячейке D11. Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11. Благодаря этому формула будет вставлена в D11, но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:
=СУММЕСЛИ(G6:G301;(«Покупки в магазинах»);H6:H301)
Произошло смещение. Если мы скопируем формулу в D12, то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $. Такие особенности EXCEL.
Предыдущая запись
Следующая запись
Здравствуйте.Как модно посчитать сумму чисел ищ одной ячейки?
Например в ячейке 35 и нужно найти сумму этих чисел
3+ 5=8
ДД, Нужна помощь, есть такая таблица, где есть регион, адрес, месяц:
МО Краснопрудная, 20 Декабрь
МО Краснопрудная, 3 Июнь
МО Краснопрудная, 3 Июнь
Москва Краснопрудная, 20 Декабрь
МО Краснопрудная, 20 Июнь
задача, сделать формулу, чтоб выбрав критерий «МО», а затем месяц «Июнь», одинаковые адреса превращались в значение 1 и к ним добавлялись уникальные адреса. Таким образом получим, что в МО в Июне было 2 уникальных адреса.
Суммирование каждой n-ой строки в Excel
Этот пример объяснит, как создать формулу массива, которая суммирует каждую n-ую строку в Excel. Мы покажем формулу на примере n = 3, но вы можете сделать это для любого другого числа.
- Функция ROW (СТРОКА) возвращает номер строки, в которой находится ячейка. =ROW(A1)
=СТРОКА(A1) - Функция MOD (ОСТАТ) выводит остаток от деления. Например:
- для первой строки результат равен 1, а 1 делится на 3 (0 раз) с остатком 1.
- для третьей строки результат равен 3, а 3 делится на 3 (ровно 1 раз) с остатком 0.
- В результате формула возвращает 0 для каждой третьей строки.
Примечание: Измените 3 на 4, чтобы суммировать каждую четвёртую строку, на 5 – каждую пятую и т.д.
- Слегка измените формулу, как показано ниже: =MOD(ROW(A1),3)=0
=ОСТАТ(СТРОКА(A1);3)=0
- Чтобы получить сумму произведения этих двух диапазонов (ЛОЖЬ = 0, ИСТИНА = 1), используйте функцию SUM (СУММ) и закончите ввод формулы сочетанием клавиш Ctrl+Shift+Enter. =SUM(A1:A9*(MOD(ROW(A1:A9),3)=0))
=СУММ(A1:A9*(ОСТАТ(СТРОКА(A1:A9);3)=0))
Примечание: Строка формул показывает, что это формула массива, заключая её в фигурные скобки <>. Их не нужно вводить самостоятельно. Они исчезнут, когда вы начнете редактировать формулу.
Пояснение:
- Произведение этих двух диапазонов (массив констант) хранится в памяти Excel, а не в ячейках листа.
- Массив констант выглядит следующим образом: .
- Этот массив констант используется в качестве аргумента для функции SUM (СУММ), давая результат 92.