Excel сумма только отображаемых ячеек - Word и Excel - помощь в работе с программами

Суммирование только видимых ячеек

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

Включены фильтры
Скрыты некоторые строки
Свернуты сгруппированные строки
Промежуточные итоги внутри таблицы
Ошибки в формулах

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

СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.

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

АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010. Также как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ может не только суммировать, но и считать среднее, количество, минимум, максимум и т.д. — код операции задается первым аргументом. Плюс к этому имеет множество опций по подсчету, которые можно указать вторым аргументом:

Ссылки по теме

Выборочные вычисления по одному или нескольким условиям
Вставка в отфильтрованные строки
Быстрое скрытие и отображение ненужных строк и столбцов

Источник

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

Смотрите также в том числе строка) скрыта. ИтогДелим на кусочки 0 0 0Genbor в 5 каждое n-ое значение200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(102;СМЕЩ($D2;СТРОКА(D$1:D$45);))*(B$3:B$47=A50)*D$3:D$47) и группировки (или символах, которые можно такая.На закладке «Формулы» Функции диапазона A1:A10. Обратитескладывает весь столбец

Функция СУММ – простое суммирование ячеек в Excel

В этом уроке мы и скрытые фильтром. – массив из1. СЧЁТЗ(‘si-2012m30’!A:A) –для 12 строки: Сначала примени фильтр,конце — 1100.только отфильтрованные (видимые) ячеек столбца вkrokodil_X скрытия) ячейки. Игнорирует вставить в критерий

=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»Иванов»;C2:C8;»яблоки») в разделе «БиблиотекаСУММ внимание, что условие А, а это

не будем рассматривать, Как суммировать значения нулей и единиц, считает количество заполненных 2 1 0 выдели ячейку под Соотвесвенно за месяц значения диапазона С3:С24.

отфильтрованном (видимом) диапазоне: список все равно другие подобные функции, функции, смотрите вЕсли нужно посчитать функций» выбираем «Математические».и заключено в двойные 1048576 значений: как посчитать сумму только в отсортированной где 0 появляется ячеек в столбце 0 0 0

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

которые могут считать статье «Как посчитать на какую сумму В появившемся спискеСУММЕСЛИ кавычки.Следующая формула подсчитывает сумму в Excel при таблице? тогда, когда скрыта

А (мы на 0 0 0 нажми (alt) + порций. В то получается сумма 66. помощи макросов?DarkScreamerDex

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

помощи оператора сложения,Serge_007 соответствующая по порядку него будем много

0 0 0 (=) же время значения, Мне же необходимоМихаил С.: список ничего не исходном диапазоне. в определенных строках». всего разных фруктов «СУММЕСЛИМН». Появится такое эту задачу и

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

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

ячейка; потом ссылаться, поэтомуи не совсемТак пропишется функция указанные в получить 18 (см.: Есть. считает, он простоЕсли нужно не суммировать,Еще много примеров

Иванову, то напишем окно. дать пользователям максимально В этом случае,Лист1 инструментов. Сегодня мы

SUBTOTAL() или, если9. перемножив результаты обзовем его для понял как суммаКод =ПРОМЕЖУТОЧНЫЕ.ИТОГИ()

отчетах агентов, суммируюся файл), там все

Более как вспомогательный фильтр то можно использовать использования функции «СУММЕСЛИМН», такую формулу сВ этом окне строки

удобный инструмент. Надеюсь, поменяв условие, изменится. Чтобы данная формула разберем всего две Excel выше 2007 п.3 и п.8, краткости ффф); произведений мне поможетreborn по итогам месяца.

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

функции: — AGGREGATE() получим массив из2. СМЕЩ(‘si-2012m30’!$R$1;;;СЧЁТЗ(‘si-2012m30’!A:A)) – в подсчете рядов

: Подскажите пожалуйста. ЕстьВопрос в слудующем,Владимир

зависит отkrokodil_X математической операции: «Примеры функции «СУММЕСЛИМН»=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»Иванов») и одного условия. помог Вам освоитьМеняем условие, меняется и ошибки, ее необходимо

СУММAurifex нулей и единиц. дает диапазон, начинающийся исходя из условий большая база отфильтровываю можно ли отфильтровать: =СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C2;СТРОКА(B3:B24)*5-14;)))конкретного: я тут совсемАГРЕГАТ (AGGREGATE) в Excel».Если нужно посчитать Но, можно написать базовые функции суммирования результат: использовать на другоми: Serge_007, а если Единица получается только с R1 иreborn

ее по нужным

office-guru.ru

Суммирование ячеек в Excel по условию.

данные по ,Igor64zzzфайла. не понял как– самая мощнаяЕсли у нас имеется сколько продали яблок до 127 условий. в Excel, иУсловие можно комбинировать, используя
рабочем листе ExcelСУММЕСЛИ у меня критерий в том случае, высотой ффф;
: спасибо за ссылки критериям. на другом  скажем, наименованию точки: Владимир, это почтиЗы. Есть функция он считает, при функция, появившаяся в таблица, по которой Иванову менеджеры ВасечкинИтак, заполняем диалоговое
теперь Вы сможете оператор конкатенации. В (отличном от Лист1).. Спешу Вас обрадовать, текстовый, типа «пример1», если совпадают 2
3. СМЕЩ(‘si-2012m30’!$R$1;;;СЧЁТЗ(‘si-2012m30’!A:A))=G4 – почитаю поразбираюсь, но
листе формула «счетесли» или имени агента, то что нужно! ПРОМЕЖУТОЧНЫЕ.ИТОГИ()
скрытии строки значение Office 2010. Также
должны считаться итоги, и Сергеева, то окно так. свободно применять эти примере ниже формулаФункция их функционала достаточно, как в subtotal условия: значение из приравниваем диапазон из чет пока идей обрабатывает не видимый
чтобы после фильтрации Есть только один
Igor64zzz не меняется, скажите как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ то важную роль формулу напишем так.
В строке «Диапазон знания на практике. вернет сумму значений,СУММ чтобы решать практически
 это прописать? столбца R равно
п.2 к значению нет как применить массив данных, а показывалась сумма порций момент — если: Михаил, спасибо. Но пожалуйста, почему в может не только играет какой именно=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»Иванов»;C2:C8;»яблоки»)+
суммирования» пишем диапазон Удачи Вам и которые больше значенияможет принимать до
все возможные вопросыAurifex значению ячейки G4 G4. Если равно,
SkyPro полный. Как сделать (это колонка 3)
фильтр настроить не ПРОМЕЖУТОЧНЫЕ.ИТОГИ по-моему не формуле СТРОКА(D$1:D$45) берется
суммировать, но и функцией они вычисляются,СУММЕСЛИМН(D2:D8;B2:B8;»Сергеева»;A2:A8;»Иванов»;C2:C8;»яблоки»)
D2-D8 – это успехов в изучении в ячейке B1. 255 аргументов и
суммирования в Excel.: Serge_007, а если
и соответствующая строка
то будет ИСТИНА,: По каким критериям чтоб она обрабатывала именно для отфильтрованных по «а», а, совсем то. именно этот диапазон?
считать среднее, количество,
т.к. в таблицеПишем эту формулу столбец с суммами Excel!Во всех примерах, приведенных
суммировать сразу несколько
Функция
у меня критерий не скрыта; если не равно вы фильтруете диапазон, только видимый? ячеек и, в например, по «с»,Для большей конкретики прилагаю_Boroda_ минимум, максимум и могут быть: так. Сначала вставляем
 покупок.
Автор: Антон Андронов ранее, мы производили несмежных диапазонов илиСУММ текстовый, типа «пример1»,10. складываем полученное – то ЛОЖЬ. по тем критериямлист si-2012m30 сама то то сумма отображается файл с задачей:: В пред. сообщении т.д. — кодВключены фильтры функцию «СУММЕСЛИМН» поВ строке «ДиапазонРассмотрим,
суммирование и проверяли ячеек:вычисляет сумму всех как в subtotal в п.9 с Итог – массив и ведите подсчет. базаже время, высчитывалась неверная. Как можноСейчас в ячейке переписал, перевложил операции задается первымСкрыты некоторые строки Васечкину. Нажимаем «ОК». условия1» пишем диапазон
как проводить суммирование в условие по одномуЕсли среди суммируемых значений своих аргументов. Она это прописать?

excel-office.ru

Суммирование только видимых ячеек

помощью СУММПРОИЗВ. из ИСТИНА иИспользуйте СУММЕСЛИМН илилист экспорт g5 разница по счетчику это исправить? С2 суммируются значенияДолжно быть ПРОМЕЖУТОЧНЫЕ.ИТОГИ(

аргументом. Плюс к
Свернуты сгруппированные строки
В строке формул
столбца с менеджерами.
Excel по условиям

и тому же содержится текст, то является наиболее частоSerge_007reborn ЛОЖЬ с количеством

 СУММПРОИЗВ. первая расчетная ячейка для них вМихаил С. диапазона С3:С24 с10 этому имеет множествоПромежуточные итоги внутри таблицы в конце формулы У нас -. Складывать в Excel диапазону. А что функция

 используемой функцией в: =SUBTOTAL(103;диапазон): таблица динамическая там значений, равным ффф;reborn которая должна считать колонке 4?: так, что ли? шагом 5 (i=5).2

 опций по подсчету,Ошибки в формулах ставим знак «+» B2-B8. можно, не только, делать, если необходимоСУММ Excel. К примеру,Michael_S до 30тыс строк4. ДВССЫЛ(«A1:A»&СЧЁТЗ(‘si-2012m30’!A:A)) –

: как использовать сумму видимые отфильтрованные данныеТо есть, прощеIgor64zzz

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

planetaexcel.ru

Суммирование видимых ячеек по условию (Формулы/Formulas)

: Спасибо, Михаил! чтобы при включенииkrokodil_X, 04.06.2014 в вторым аргументом: способов чувствительны к «СУММЕСЛИМН» по Сергеевой.. строкам, но и а условие проверять не включает в значения в трехAurifex,а обьясните пожалуйст ячейки А1:Аффф; случае я вообще: тему http://www.excelworld.ru/forum/2-3434-1#36901 смотрел не только промежуточнуюЭто то что
текстового фильтра (например, 13:17, в сообщенииkrokodil_X этим факторам, некоторые Получается, мы сложилиКогда мы поставили выбирать для сложения

 по-другому? расчет: ячейках. Мы, конечнобез вашего файла логику формулы в

5. СТРОКА(ДВССЫЛ(«A1:A»&СЧЁТЗ(‘si-2012m30’!A:A))) – не представляю
функции предварительный.итог тоже

 сумму (в колонке нужно. только значения «а»

 № 6200?’200px’:»+(this.scrollHeight+5)+’px’);»>почему в: Добрый день! Помогите – нет. Это функции отдельно по курсор в эту

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

 3), но иlanalina, суммировались только
формуле СТРОКА(D$1:D$45) берется пожалуйста с такой нужно учитывать при
 каждому менеджеру. строку, автоматически появилась по разным условиям.СУММЕСЛИ попытаться сложить оператором обычным оператором суммирования: Сергея сказать нечего.reborn
1;2;3;…;ффф; я запользную, но
нет обработки фукнции посчитать
: Добрый день! Поднимаю отфильтрованные (видимые) значения именно этот диапазон?У

 проблемой: имеется таблица выполнении вычислений:Внимание! новая строка дляКак установить простоеприпасла третий необязательный суммирования, то формулаНо мы также можем
Silenser: пробую растянуть формулу6. СТРОКА(ДВССЫЛ(«A1:A»&СЧЁТЗ(‘si-2012m30’!A:A)))-1 –

 разве при фильтрации «счетесли»промежуточную разницу между

excelworld.ru

Выборочное суммирование ячеек

 тему с вашего диапазона С3:С24.
нас диапазон со в которой требуетсяСУММ (SUM)В условиях этой условий. Если условий условие для подсчета аргумент, который отвечает вернет ошибку:

 воспользоваться функцией: А он и
до -13 и дает массив 0;1;2;,,,;ффф; данных автофильтром, скрытыеа как считать показаниями счетчика в
позволения.Буду очень признателен,

 строки 3, так? просуммировать только видимые– тупо суммирует функции «СУММЕСЛИМН» количество
 много, то появляется данных, смотрите в
за диапазон, которыйФункцияСУММ так скрытые считать
добавить несколько строк,7. СМЕЩ(‘si-2012m30’!$A$1;СТРОКА(ДВССЫЛ(«A1:A»&СЧЁТЗ(‘si-2012m30’!A:A)))-1;) – строки не будут с помощью других конце и вДобрый день. Прошу если сможете помочь.
Можно было написать ячейки («длина участка»)

 все в выделенном ячеек диапазона суммирования полоса прокрутки, с статье «Функция «ЕСЛИ»
необходимо просуммировать. Т.е.СУММи записать формулу

 не будет. В выдает неправильный счет последовательно дает нам считаться? я пока не начале месяца для помочь с решением
Z
СТРОКА(D$3:D$47)-2 в столбце ( диапазоне без разбора, и количество ячеек
помощью которой, переходим в Excel». по первому аргументудостаточно универсальна и следующим образом: смысле, если просто для статической таблицы значения ячеек, начинаяу меня проблема
додумался отфильторованных строк. следующей задачи. Имеется: Как же этоЭто то жескрытые не учитывать

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

 в следующие окнаЗдесь рассмотрим функция проверяет условие, позволяет использовать вПоскольку функция выделить ячейки и (первая формула) и с А1 и не в томSerge_007Как это можно

 список точек кофеавтоматов тяжко: почитать справку,

 самое, что СТРОКА(D$1:D$45)) с определенным условием
строки в том быть одинаковое. Например,

 условий.функцию «СУММЕСЛИМН» в а третий подлежит качестве своих аргументов
СУММ посмотреть сумму в н.д. для динамической смещаясь вниз на что я не
: http://www.excelworld.ru/forum/2-241-1#2669 сделать? (колонка 1) взять да проверить…Суть в том, типа СУММЕСЛИ, где
числе. Если хотя все диапазоны сВ строке «Условие1»Excel суммированию.
не только ссылкиподдерживает работу не строке статуса. Аreborn числа из п.6
могу посчитать, а
http://www.excelworld.ru/forum/2-2346-1#25996Заранее спасибо !по которым агентыСм. вложение, список/таблица, что нам нужно в качестве критерия
бы в одной 3 по 8 пишем фамилию менеджера., которая суммирует числа,В следующем примере мы на ячейки и
только с отдельными вот если в
: Serge_007, так подойдет? (то есть – в том эксельhttp://www.excelworld.ru/forum/2-5007-1#51429vikttur
(колонка 2) развозят однако, с пром получить массив 1;2;3;4;…;44;45 будет соседний столбец ячейке есть любая ячейки. Если будет
Мы написали – удовлетворяющие нескольким условиям, просуммируем общую стоимость диапазоны, но и
ячейками, но и формуле, то будет.как то жестко А1; А2;…;Аффф); и невидимые строкиSkyPro
: 1. Создайте отдельную кофе и какао. итогами…DarkScreamerDex («диаметр»), в котором
ошибка – перестает диапазон суммирования с
Васечкин.

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

planetaexcel.ru

Как суммировать только отфильтрованные значения

 все тут у8. ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;СМЕЩ(‘si-2012m30’!$A$1;СТРОКА(ДВССЫЛ(«A1:A»&СЧЁТЗ(‘si-2012m30’!A:A)))-1;)) –
считает, а мне: тему. Они развозят товарIgor64zzz: к чему эти значение должно соответствовать считать и тоже 3 по 10В строке «Диапазон для сложения по Для этого воспользуемся
и даже другие
вышеприведенную формулу можно

: Необходимо в формуле вас дает массив из надо чтоб считал200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(—((‘si-2012m30’!$R$2:$R$18=G4)*(‘si-2012m30’!$H$2:$H$18>$B$2)))2. Не нужно
по точкам, а
: Наверное выше я

CyberForum.ru

Как обработать только видимые ячейки (Формулы)

 выкрутасы… можно поставить определенному значению (пример, выдает ошибку на ячейки. А диапазон условия2» пишем диапазон нескольким условиям. Можно следующей формулой: функции Excel: модифицировать: между аргументами функцииreborn
нулей и единиц. только видимые отфильтрованные
Подсчет ячеек по писать через строку. потом ежемесячно не совсем корректно

 пару списков для диаметр равен 40).
выходе. условия с 3 столбца покупателей – указать до 127
НажавЕсли интересно узнать, какИстинная мощь функции установить точку с

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

AnatolyInTheAssсдают отчет, в объяснил, поэтому то
фильтрации, убрать фильтр Пример прилагаю.ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS)

 по 8, то А2:А8. условий.Enter
посчитать накопительную суммуСУММ запятой например: =(В1;В5), вам спасибо. а) в ячейках_Boroda_
добавить свои.: Добрый день, котором указывают количество что Вы прислали и считать с
Для всех ячеекс кодом 9 формула посчитает неВ строке «Условие2»Есть еще функция
получим результат: в Excel, обратитесьраскрывается, когда необходимов этом случаеМожете, если не

 из п.7 не: Так нужно?rebornСкажите, пожалуйста, как реализованных порций (колонка

 — не то помощью СУММЕСЛИ или без условия скрытые в первом аргументе верно. Это нужно
написали фамилию покупателя «СУММЕСЛИ», но в

Если одного условия Вам к этому уроку. сложить большое количество В2, В3, В4 сложно, обьяснить логику
число – уДля статической таблицы: Вообще не понял заставить excel считать 3). одновременно с что я хочу.
СУММЕСЛИМН через списки или видимые формула – суммирует все учитывать при установлении – Иванов. ней можно указать не достаточно, тоФункция ячеек в Excel. в подсчете не

 работы формулы? Общую нас это только
(всегда 18 строк) причем тут ячейка
сумму только тех
этимЕще раз задача:темболее что в
простая, но вот
видимые после фильтра
функции «СУММЕСЛИМН».
Двигаем поле условий
одно условие. Смотрите Вы всегда можете
СУММЕСЛИ
В примере ниже
участвуют. хотя бы. шапка таблицы, а200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;СМЕЩ(‘si-2012m30’!$A$1;СТРОКА(‘si-2012m30’!A$1:A$18)-1;))*(‘si-2012m30’!$R$1:$R$18=G4)) $B$2 ячеек, которые видныснимаются показания счетчикаСейчас в ячейке
формуле нули пробивают для видимых я ячейки. Игнорирует другиеЕсть еще одна
полосой прокрутки, пишем статью «Функция «СУММЕСЛИ» воспользоваться функциейпозволяет подсчитать условную требуется просуммировать 12Сергей каханскийAurifex она нам недля динамической таблицыв текущем раскладе в конкретный момент
(колонка 4), установленного С2 суммируются значения при фильтрации
не знаю как подобные функции, которые функция в Excel,
следующее условие. в Excel» тут.
СУММЕСЛИМН сумму в Excel, значений. Функция: если данные скрываются: Добрый день! Подскажите нужна для расчетов (строки можно дописывать) примера исходя из
времени. Проблема заключается на кажждом автомате, диапазона С3:С24 сkrokodil_X написать. могут считать внутренние которая считает выборочноВ строке «ДиапазонИтак, у нас, которая позволяет осуществлять т.е. сумму ячеек,СУММ с помощью фильтр, решение следующего вопроса: (про первый аргументНе верно данных первого листа в том, что и сравниваются с шагом 5 (i=5).: Спасибо тебе хорошийDarkScreamerDex подитоги в исходном по условию. В условий3» пишем диапазон такая таблица.
условное суммирование в которые удовлетворяют определенномупозволяет сделать это можно воспользоваться функциейимеется таблица, из в ПРОМЕЖУТОЧНЫЕ.ИТОГИ см.Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;СМЕЩ(‘si-2012m30’!$A$1;СТРОКА(ДВССЫЛ(«A1:A»&СЧЁТЗ(‘si-2012m30’!A:A)))-1;))*(‘si-2012m30’!$R$1:$R$18=G4)) значения строки 5 формула считает сумму даннымиНеобходимо сделать так, человек. Формула заработала.
: приходит в голову диапазоне. ней можно указывать

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

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

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) разной длины диапазоны,
В строке «Условие3» проданных яблок, которые нескольких критериев.

СУММЕСЛИ если же использоватьесли же скрываются
значения строк с кстати, в нашуКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;СМЕЩ(‘si-2012m30’!$A$1;СТРОКА(ДВССЫЛ(«A1:A»&СЧЁТЗ(‘si-2012m30’!A:A)))-1;))*(СМЕЩ(‘si-2012m30’!$R$1;;;СЧЁТЗ(‘si-2012m30’!A:A))=G4)) быть следующими:

excelworld.ru

суммирование значений видимых ячеек отсортированной таблицы (суммирование значений ячеек отсортированной таблицы)

 столбце, незавиисмо отПоказания счетчика растут текстового фильтра (например,
: Добрый день! списком, список можнос кодом 109 но условие можно пишем – «яблоки». продал менеджер ВасечкинСуммирование – это одноможет содержать только оператор сложения, то строки, я тут заданным критерием. Я формулу можно поставить

Файл перевложилдля 5й строки того скрыты они в течение месяца. только значения «а»

Подскажите пожалуйста, есть отдельно создать в первом аргументе указать только одно.Нажимаем «ОК». Получился такой покупателю Иванову.

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

На 30000 может 3 1 1

 фильтрами или нет. Скажем, в начале, суммировались ли возможность в_Boroda_ – суммирует все

excelworld.ru

Excel как посчитать сумму ячеек в столбце, если между ними есть скрытые, как сделать чтоб эти скрытые он не считал?

 Подробнее о применении результат.В ячейку F5 которое пользователь выполняетНапример, следующая формула суммируемВ следующем примере функцияhttp://files.mail.ru/TXYJNO Но данное решение 3) и б) тормозить.

 0 0 0Спасибо! месяца счетсик показывалс заданным ранее шагом excel 2007 суммировать
: Так нужно? видимые после фильтра функции «СУММЕСЛИ», оФормула в ячейке А5

 устанавливаем формулу. в Microsoft Excel. только положительные числаСУММвроде работает
суммирует все строки, ячейка (вернее, всяОбъяснялка формулы.
0 0 0
Анатолий.

1000 порций, а

Источник

17 авг. 2022 г.
читать 2 мин

Самый простой способ получить сумму отфильтрованного диапазона в Excel — использовать следующий синтаксис:

SUBTOTAL( 109 , A1:A10 )

Обратите внимание, что значение 109 — это сокращение для получения суммы отфильтрованного диапазона строк.

В следующем примере показано, как использовать эту функцию на практике.

Пример: суммирование отфильтрованных строк в Excel

Предположим, у нас есть следующий набор данных, содержащий информацию о различных баскетбольных командах:

Далее давайте отфильтруем данные, чтобы показывать только игроков на Mavs или Warriors.

Для этого выделите диапазон ячеек A1:B10.Затем щелкните вкладку « Данные » на верхней ленте и нажмите кнопку « Фильтр ».

Затем щелкните стрелку раскрывающегося списка рядом с « Команда» , снимите флажок рядом с «Селтикс» и нажмите « ОК »:

Данные будут автоматически отфильтрованы, чтобы удалить строки с «Селтикс» в качестве команды:

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

Вместо этого мы можем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() :

Эта функция принимает сумму только видимых строк.

Мы можем вручную проверить это, взяв сумму видимых строк:

Сумма очков в видимых рядах: 99 + 94 + 93 + 104 + 109 + 84 = 583 .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

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

Написано

Замечательно! Вы успешно подписались.

Добро пожаловать обратно! Вы успешно вошли

Вы успешно подписались на кодкамп.

Срок действия вашей ссылки истек.

Ура! Проверьте свою электронную почту на наличие волшебной ссылки для входа.

Успех! Ваша платежная информация обновлена.

Ваша платежная информация не была обновлена.

Источник

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

Суммировать только отфильтрованные или видимые значения ячеек с помощью формулы
Суммируйте только отфильтрованные или видимые значения ячеек с помощью функции, определяемой пользователем
Сумма/счет/усреднение только отфильтрованных или видимых ячеек с Kutools for Excel

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

С этой SUBTOTAL функция, которая игнорирует строки, которые были исключены фильтром, вы можете легко добавить только видимые ячейки. Вы можете сделать так:

Предположим, у вас есть диапазон данных, и они были отфильтрованы по мере необходимости, см. Снимок экрана:

1. В пустой ячейке, например, C13, введите следующую формулу: = Итого (109; C2: C12) (109 указывает на то, что при суммировании чисел скрытые значения игнорируются; C2: C12 — диапазон, который вы суммируете, игнорируя отфильтрованные строки.), и нажмите Enter .

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

Сумма / количество / среднее значение видимых ячеек только в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек / строк / столбцов

Обычно функция SUM/Count/Average подсчитывает все ячейки в указанном диапазоне независимо от того, скрыты/отфильтрованы ячейки или нет. В то время как функция промежуточного итога может только суммировать/подсчитывать/усреднять, игнорируя скрытые строки. Однако, Kutools for Excel СУЩЕСТВЕННЫЙ / СОВМЕСТНЫЙ / СРЕДНЯЯВИДИМАЯ функции легко вычисляют указанный диапазон, игнорируя любые скрытые ячейки, строки или столбцы.

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

Если вас интересует следующий код, он также может помочь вам суммировать только видимые ячейки.

1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
        total = total + rng.Value
    End If
Next
SumVisible = total
End Function

3. Сохраните этот код и введите формулу = SumVisible (C2: C12) в пустую ячейку. Смотрите скриншот:

4, Затем нажмите Enter key и вы получите желаемый результат.

Сумма/счет/усреднение только отфильтрованных или видимых ячеек с Kutools for Excel

Если у вас есть Kutools for Excel установлен, вы можете быстро вычислить сумму / количество / среднее только видимые или отфильтрованные ячейки в Excel легко.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now

Например, вы хотите суммировать только видимые ячейки, выберите ячейку, в которую вы поместите результат суммирования, введите формулу = СУММВИДИМ (C3: C12) (C3: C13 — это диапазон, в котором вы суммируете только видимые ячейки) в него и нажмите Enter .

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

Для подсчета только видимых ячеек примените эту формулу = СОВМЕСТНЫЙ (C3: C12); Для усреднения только видимых ячеек примените эту формулу = СРЕДНЕВИДИМОЕ (C3: C12).

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

1. Выберите ячейку, в которую вы поместите результат суммирования, и нажмите Кутулс > функции > Статистические и математические > СУЩЕСТВЕННЫЙ (или СРЕДНИЙ ВИДИМОЙ, СОВМЕСТНЫЙ как вам нужно). Смотрите скриншот:

2. В открывшемся диалоговом окне «Аргументы функций» укажите диапазон, в котором вы будете суммировать, игнорируя скрытые ячейки, и нажмите кнопку OK кнопка. Смотрите скриншот:

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

Демонстрация: сумма / подсчет / среднее только отфильтрованные или видимые ячейки

Легко суммировать / подсчитывать только отфильтрованные / видимые ячейки, удаляя скрытые строки в Excel

При суммировании/подсчете отфильтрованных ячеек в Excel функция СУММ или функция подсчета не будут игнорировать скрытые ячейки. Если скрытые/отфильтрованные строки удалены, мы можем легко суммировать или подсчитывать только видимые ячейки. Можешь попробовать Kutools for Excel’s Удалить скрытые (видимые) строки и столбцы утилита для ее решения.

Статьи по теме:

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

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

Решение: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ вместо СУММ. Формула будет немного отличаться, в зависимости от того, как вы спрятали строки. Если вы выделили строки, кликнули правой кнопкой мыши, и в контекстном меню выбрали скрыть, можно использовать: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; диапазон) (рис. 1). Весьма необычно использовать для этих целей ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Как правило, эта функция нужна, чтобы Excel игнорировал другие подитоги внутри диапазона.

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

Скачать заметку в формате Word или pdf, примеры в формате Excel

ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнить 11 операций. Первый аргумент функции указывает ей на следующие операции: (1) СРЗНАЧ, (2) СЧЁТ, (3) СЧЁТЗ, (4) МАКС, (5) МИН, (6) ПРОИЗВЕД, (7) СТАНДОТКЛОН, (8) СТАНДОТКЛОНП, (9) СУММ, (10) ДИСП, (11) ДИСПР. При добавлении сотни выполняются те же операции, но только над видимыми ячейкам. Например, 104 найдет максимум среди видимых ячеек. Под видимыми имеется ввиду, не видимые на экране (например, 120 строк не уместятся на экране), а не скрытые, командой Скрыть.

В ячейке Е566 (см. рис. 1) используется формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;E2:E564). Excel возвращает сумму только видимых (не скрытых) ячеек в диапазоне, а именно – Е2;Е30;Е72;Е78;Е564.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к вертикальным наборам данных. Она не предназначена для горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы номер_функции от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;С2:F2) рис. 2), скрытие столбца не повлияет на результат.

Рис. 2. Формула не игнорирует ячейки в скрытых столбцах

Дополнительные сведения: существует необычное исключение в поведении функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Когда строки были скрыты по какой-либо из команд фильтра (расширенный фильтр, автофильтр или фильтр), Excel суммирует только видимые строки даже в варианте ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон). Нет необходимости использовать версию 109 (рис. 3). Здесь фильтр используется для поиска записей Chevron.

Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра

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

Выбрать любую ячейку в вашем наборе данных.
Пройдите по меню ДАННЫЕ –>Фильтр (или нажмите Alt + Ы, а затем не отпуская Alt, нажмите Ф; или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) для всех заголовков столбцов.
Откройте одно из выпадающих меню, например, Customer. Снимите флажок Выделить все, а затем выберите одного клиента. В нашем примере – Chevron.
Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере –ячейки Е565:H565.
Нажмите клавиши Alt+= или щелкните значок Автосумма (меню ГЛАВНАЯ). Вместо того, чтобы использовать СУММ, Excel применит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон), которая просуммирует только строки, выбранные фильтром (см. рис. 3).

В Excel 2010 появилась еще одна подобная функция – АГРЕГАТ (подробнее см. Сравнение массивов и выборки по одному или нескольким условиям; раздел Функция АГРЕГАТ). Она имеет больше функций в своем «репертуаре» и больше опций, какие строки исключать, а какие обрабатывать. Основное ее достоинство – обработка ошибочных значений (например, #ДЕЛ/0!). К сожалению, эта функция также не применима к суммированию видимых столбцов.

Резюме: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы игнорировать скрытые строки.

Как в excel суммировать только видимые ячейки

Перминов Игорь

Сообщений: 1582
Откуда: Красная Орловка

Евгений Банщиков

Сообщений: 211
Откуда: Kurgan

DmGr
Автор

Нарисовал такую функцию в VBA

Function VisibleSum(InputRange As Range)
For Each OneCell In InputRange
If OneCell.Height > 0 Then
RangeSum = RangeSum + OneCell.Value
End If
Next
VisibleSum = RangeSum
Exit Function
End Function

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

Как бы побороть?

Владимир Максимов

Сообщений: 13846
Откуда: Москва

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

Суммирует строки второго столбца с учетом автофильтра, наложенного на первую строку. Ничего в FoxPro рассчитывать не надо.

AleksM

Автофильтр накладывается на ~~столбец~~ ниже лежащие ячейки. Даже если ты такую ф-цию напишешь, то не факт что ты увидешь результат её работы. Результат нужно будет или в другой столбец выводить или выше суммируемых ячеек.
Если пользователь будет скрывать какие то строки, то оформи свою функцию в виде макроса, например:

Все бы тут хорошо, но вот почему то автокалк не отрабатывает при скрытии строк. Приходится на ячейке с суммированием F2+enter делать.

2 Владимир Максимов
С автофильтром ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает, но со скрытими строками — нет.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel для промежуточных расчетов

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel используется для расчета промежуточных итогов в таблицах (в том числе и базах данных) и возвращает искомое числовое значение (в зависимости от номера требуемой операции, указанного в качестве первого аргумента данной функции, например, 1 – среднее арифметическое диапазона значений, 9 – суммарное значение и т. д.). Чаще всего рассматриваемую функцию применяют для модификации списков с промежуточными итогами, созданных с использованием специальной встроенной команды в Excel.

Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ целесообразно использовать в случаях, когда таблица содержит большое количество данных, которые могут быть отфильтрованы по одному или нескольким критериям. При этом в результате применения фильтров будет отображена только часть таблицы, данные в которой соответствуют установленному критерию. Однако операции с использованием обычных функций, таких как СУММ, СРЗНАЧ и др. будут производиться над всей изначальной таблицей (то есть с учетом скрытых строк). Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает только с отфильтрованными данными.

Суммирование только видимых ячеек в фильтре Excel

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

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

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

Нажмем на раскрывающийся список в столбце B («Марка товара») и установим флажок только напротив названия «Ibanez»:

После нажатия на кнопку «ОК» таблица примет следующий вид:

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

Вместо этого в ячейке C24 будем использовать следующую функцию:

9 – числовое значение, соответствующее использованию функции СУММ для получения промежуточных итогов;
C4:C20 – диапазон ячеек, содержащих данные о стоимости гитар (при этом все другие гитары, кроме марки Ibanez, в расчете не учитываются).

Аналогично выполним расчет для количества проданных гитар и общей выручки («Сумма). В результате получим:

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

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

Выборочное суммирование ячеек таблицы в Excel

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

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

После нажатия на кнопку «ОК» исходная таблица примет следующий вид:

Полученная таблица имеет инструменты, позволяющие скрывать/отображать части данных и отобразить при необходимости только общий итог. Если выделить любую ячейку, в которой отображаются промежуточные итоги, можно увидеть, что они были рассчитаны с использованием функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

Примеры формул для расчетов промежуточных итогов в таблице Excel

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

Отфильтруем данные с использованием критериев «джинсы» и указанная в условии дата:

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ не содержит встроенных функций для расчета моды и среднего отклонения. Для расчета моды используем следующую формулу (формула массива CTRL+SHIFT+ENTER):

В данном случае функция ПРОМЕЖУТОЧНЫЕ.ИТОГЫ возвращает ссылку на диапазон ячеек, из которого исключены строки, которые не отображаются в связи с использованием фильтров. Функция ЕСЛИ возвращает массив, содержащий числовые значения для отображаемых строк и пустые значения «» для строк, которые не отображены. Функция МОДА игнорирует их при расчете. В результате выполнения формулы получим:

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

Как правило, функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ используют для несложных вычислений. 11 функций, предложенных в рамках ее синтаксиса, как правило вполне достаточно для составления отчетов с промежуточными итогами.

Особенности использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Рассматриваемая функция имеет следующую синтаксическую запись:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ( номер_функции;ссылка1; [ссылка2];…])

номер_функции – обязательный для заполнения аргумент, принимающий числовые значения из диапазонов от 1 до 11 и от 101 до 111, характеризующие номер используемой функции для расчета промежуточных итогов: СРЗНАЧ, СЧЁТ, СЧЁТЗ, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНП, СУММ, ДИСП и ДИСПР соответственно. При этом функции, обозначенные числами от 1 до 11, используются тогда, когда в расчет требуется также включить строки, которые были скрыты вручную. Функции, обозначенные числами от 101 до 111, игнорируют скрытые вручную строки при расчетах. Строки, которые были скрыты в связи с применением фильтров, в расчетах не учитываются в любом случае;
ссылка1 – обязательный аргумент, принимающий ссылку на диапазон ячеек с числовыми данными, для которых требуется выполнить расчет промежуточных итогов;
[ссылка2];…] – вторая и последующие ссылки на диапазоны ячеек, для значений которых выполняется расчет промежуточных итогов. Максимальное количество аргументов – 254.

Если в качестве аргументов ссылка1, [ссылка2];…] были переданы диапазоны ячеек, в которые включены ячейки, содержащие промежуточные итоги, полученные с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, они учтены не будут чтобы не повлиять на итоговый результат.
В отфильтрованной таблице отображаются только те строки, содержащиеся значения в которых удовлетворяют поставленным условиям (используемым фильтрам). Некоторые строки могут быть скрыты вручную с использованием опции «Скрыть строки». Такие строки также могут быть исключены из результата, возвращаемого функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, если в качестве ее первого аргумента было указано число из диапазона от 101 до 111.
Основное свойство рассматриваемой функции (выполнение операций только над отфильтрованными данными) применимо только для таблиц, данные в которых фильтруются по строкам, а не по столбцам. Например, при расчете промежуточных итогов в горизонтальной таблице, в которой в результате применения фильтра были скрыты несколько столбцов, функция =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A1:F1) вернет среднее значение для всех величин, находящихся в диапазоне A1:F1, несмотря на то, что некоторые столбцы являются скрытыми.
Если в качестве аргументов ссылка1, [ссылка2];…] были переданы ссылки на диапазоны ячеек, находящиеся на другом листе или в другой книге Excel (такие ссылки называются трехмерными), функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ вернет код ошибки #ЗНАЧ!.

Суммирование только видимых ячеек

Включены фильтры
Скрыты некоторые строки
Свернуты сгруппированные строки
Промежуточные итоги внутри таблицы
Ошибки в формулах

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

Сумма по фильтру в Эксель (Excel) — как найти?

Как в программе Эксель (Excel) найти сумму ячеек, которые были отфильтрованы?

Например, если в столбце таблицы 100 ячеек, а после применения фильтра осталось 10, то как можно легко и быстро посчитать сумму этих 10 ячеек?

Сумма отфильтрованных ячеек в Excel

При работе в Excel иногда бывает необходимо не только отфильтровать данные в таблице, но и найти сумму по одному или нескольким столбцам, к которым был применён фильтр.

Расскажу как это можно сделать на конкретном примере.

Пусть исходная таблица будет такая:

Нужно оставить только фрукты и посчитать их суммарную стоимость.

I. Сначала устанавливаем фильтр: на панели инструментов Excel «Главная» нажимаем на «Сортировка и фильтр» -> «Фильтр», а затем щёлкаем на появившемся треугольнике в столбце «Фрукты / овощи».

Снимаем галочку с «Овощи» и нажимаем на «ОК».

Останутся только фрукты.

II. Вообще, сумма при фильтре отображается в строке состояния Excel — достаточно просто выделить нужный столбец.

То есть все фрукты стоят суммарно 430 рублей.

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

Поэтому можно сделать так:

1) Поставить курсор в нижестоящую ячейку и на панели инструментов «Главная» нажать на «Автосумма».

2) Появится формула с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ

3) Нажимаем на клавишу Enter и получаем нужную нам сумму.

Это значение можно использовать в формулах и копировать куда угодно.

Если же вы поставите в фильтре другое условие, то сумма автоматически пересчитается.

В частности, в рассматриваемом примере можно поменять условие с «Фрукты» на «Овощи»:

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

У этой функции 3 аргумента:

1) Диапазон — это диапазон ячеек, которые нужно проверить (удовлетворяют ли они какому-либо условию или нет).

В нашем случае это столбец «Фрукты / овощи» (B).

2) Критерий — указывается нужное условие.

В нашем случае это «Фрукты» (в самом мастере формул пишется без кавычек).

3) Диапазон_суммировани я — столбец со значениями, которые нужно суммировать.

Источник

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Формула не игнорирует ячейки в скрытых столбцах

Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра

Выбрать любую ячейку в вашем наборе данных.
Пройдите по меню ДАННЫЕ –> Фильтр (или нажмите Alt + Ы, а затем не отпуская Alt, нажмите Ф; или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) для всех заголовков столбцов.
Откройте одно из выпадающих меню, например, Customer. Снимите флажок Выделить все, а затем выберите одного клиента. В нашем примере – Chevron.
Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере –ячейки Е565:H565.
Нажмите клавиши Alt+= или щелкните значок Автосумма (меню ГЛАВНАЯ). Вместо того, чтобы использовать СУММ, Excel применит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон), которая просуммирует только строки, выбранные фильтром (см. рис. 3).

Резюме: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы игнорировать скрытые строки.

Источник

Автосумма в Excel

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

Наиболее распространенная функция, которую используют большинство пользователей Microsoft Excel, это функция СУММ. Неудивительно, что Microsoft Excel решили добавить специальную кнопку на ленту Excel, которая автоматически вставляет функцию СУММ.

По сути, Автосумма в Excel автоматически вводит формулу для суммирования чисел на вашем листе. В данной статье мы рассмотрим следующую информацию:

Где находится Автосумма в Excel

Кнопку Автосумма можно найти в двух местах на ленте Excel.

Вкладка « ГЛАВНАЯ » —> группа « Редактирование » —> «Автосумма»:

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

Вкладка « ФОРМУЛЫ » —> группа « Библиотека функций » —> «Автосумма»:

Автосумма в Excel – Кнопка Автосумма во вкладке Формулы

Теперь где Автосумма в Excel вы знаете.

Как сделать автосумму в Excel

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

Чтобы использовать Автосумму в Excel, выполните следующие 3 простых действия:

Выберите ячейку рядом с цифрами, которые вы хотите просуммировать:

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

Автосумма в Excel – Выбор ячейки, после который вы хотите посчитать сумму

Нажмите кнопку Автосумма на вкладке « ГЛАВНАЯ » или « ФОРМУЛЫ ».

В выбранной ячейке появляется формула суммы, а диапазон суммируемых ячеек выделяется (B2:B6 в этом примере):

Автосумма в Excel – Кликните на кнопке Автосумма на вкладке Главная и Формулы (в данном случае на вкладке Формула)

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

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

Нажмите клавишу Enter , чтобы завершить формулу.

Теперь вы можете увидеть вычисленную сумму в ячейке и формулу СУММ в строке формул:

Автосумма в Excel – Кликните клавишу Enter, чтобы завершить формулу СУММ

Разберем причины, по которым автосумма в Excel выдает 0.

Автосумма в Excel не считает

Бывают случаи, когда Автосумма в Excel не работает, то есть автосумма в Excel выдает 0. Самая распространенная причина почему не работает Автосумма в Excel — это числа, отформатированные как текст. На первый взгляд, эти значения могут выглядеть как обычные числа, но Excel рассматривает их как текстовые строки и не учитывает при расчетах.

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

Автосумма в Excel – Преобразование числа в текстовом формате, в числовой

Числа могут иметь текстовый формат по различным причинам, например, они могут быть импортированы из внешнего источника. Теперь вы знаете причину, почему Автосумма в Excel не считает ваши данные и решение этого вопроса.

Автосумма в Excel горячие клавиши

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

Автосумма в Excel – Автосумма в Excel горячие клавиши

При удерживании клавиши Alt , и нажатии знака равенства (=), в выбранной ячейке (ячейках) вставляется формула СУММ точно так же, как при нажатии кнопки Автосумма на ленте, и затем вы нажимаете клавишу Enter для ввода формулы.

Как использовать Автосумму с другими функциями

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

СРЕДНЕЕ — возвращает среднее (среднее арифметическое) чисел.

СЧЕТЧИК — подсчитывает количество ячеек с числами.

МАКСИМУМ — возвращает наибольшее значение.

МИНИМУМ — возвращает наименьшее значение.

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

Например, именно так вы можете получить наибольшее число в столбце B:

Автосумма в Excel – Использование кнопки Автосумма для вставки других формул (в данном случае Максимум)

Если вы выберете «Другие функции» из раскрывающегося списка «Автосумма», Microsoft Excel откроет диалоговое окно «Вставить функцию», где вы можете найти необходимую формулу в списке или воспользоваться поиском.

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

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

Если ваши данные организованы в таблице Excel (что можно легко сделать, нажав сочетание клавиш Ctrl+T), и вы отфильтровали свои данные, применив один из параметров фильтрации, нажатие кнопки Автосуммы вставляет формулу ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а не СУММ, как показано на следующем изображении:

Автосумма в Excel – Использование Автосуммы для видимых ячеек

В исходной таблице были скрыты 3, 6, 9 и 12 строка. В данном случае, использование Автосуммы и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, были просуммированы только видимые ячейки.

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

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

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

Например, вы можете выбрать диапазон ячеек B7:D7, щелкнуть Автосумма, и как показано на следующем изображении, значения в каждом из трех столбцов суммируются по отдельности:

Автосумма в Excel – Автосумма нескольких столбцов одновременно

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

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

Автосумма в Excel – Сумма выбранных ячеек вертикально по столбцам

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

Автосумма в Excel – Сумма выбранных ячеек горизонтально по строкам

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

Автосумма в Excel – Суммирование выбранных ячеек столбцам и строкам

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

Функция СУММ

В этом курсе:

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

=СУММ(A2:A10) Суммирует значения в ячейках A2:10.

=СУММ(A2:A10;C2:C10) Суммирует значения в ячейках A2:10, а также ячейки C2: C10.

Первое число для сложения. Это может быть число 4, ссылка на ячейку, например B6, или диапазон ячеек, например B2:B8.

Это второе число для сложения. Можно указать до 255 чисел.

Этот раздел содержит некоторые рекомендации по работе с функцией СУММ. Многие из этих рекомендаций можно применить и к другим функциям.

Метод =1+2 или =A+B. Вы можете ввести =1+2+3 или =A1+B1+C2 и получить абсолютно точные результаты, однако этот метод ненадежен по ряду причин.

Опечатки. Допустим, вы пытаетесь ввести много больших значений такого вида:

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

Ошибки #ЗНАЧ!, если ячейки по ссылкам содержат текст вместо чисел

Допустим, вы используете формулу такого вида:

Если ячейки, на которые указывают ссылки, содержат нечисловые (текстовые) значения, формула может вернуть ошибку #ЗНАЧ!. Функция СУММ пропускает текстовые значения и выдает сумму только числовых значений.

Ошибка #ССЫЛКА! при удалении строк или столбцов

При удалении строки или столбца формулы не обновляются: из них не исключаются удаленные значения, поэтому возвращается ошибка #ССЫЛКА!. Функция СУММ, в свою очередь, обновляется автоматически.

Формулы не обновляют ссылки при вставке строк или столбцов

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

Функция СУММ — отдельные ячейки или диапазоны.

Используя формулу такого вида:

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

Такая формула будет обновляться при добавлении и удалении строк.

Мне нужно добавить, вычесть, умножить или поделить числа. Просмотрите серию учебных видео: Основные математические операции в Excel или Использование Microsoft Excel в качестве калькулятора.

Как уменьшить или увеличить число отображаемых десятичных знаков? Можно изменить числовой формат. Выберите соответствующую ячейку или соответствующий диапазон и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, затем щелкните вкладку Число и выберите нужный формат, указав при этом нужное количество десятичных знаков.

Как добавить или вычесть значения времени? Есть несколько способов добавить или вычесть значения времени. Например, чтобы получить разницу между 8:00 и 12:00 для вычисления заработной платы, можно воспользоваться формулой =(«12:00»-«8:00»)*24, т. е. отнять время начала от времени окончания. Обратите внимание, что Excel вычисляет значения времени как часть дня, поэтому чтобы получить суммарное количество часов, необходимо умножить результат на 24. В первом примере используется формула =((B2-A2)+(D2-C2))*24 для вычисления количества часов от начала до окончания работы с учетом обеденного перерыва (всего 8,5 часов).

Если вам нужно просто добавить часы и минуты, вы можете просто вычислить сумму, не умножая ее на 24. Во втором примере используется формула =СУММ(A6:C6), так как здесь нужно просто посчитать общее количество часов и минут, затраченных на задания (5:36, т. е. 5 часов 36 минут).

Как получить разницу между датами? Как и значения времени, значения дат можно добавить или вычесть. Вот распространенный пример вычисления количества дней между датами. Для этого используется простая формула =B2-A2. При работе со значениями дат и времени важно помнить, что дата или время начала вычитается из даты или времени окончания.

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

Как вычислить сумму только видимых ячеек? Иногда когда вы вручную скрываете строки или используете автофильтр, чтобы отображались только определенные данные, может понадобиться вычислить сумму только видимых ячеек. Для этого можно воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Если вы используете строку итогов в таблице Excel, любая функция, выбранная из раскрывающегося списка «Итог», автоматически вводится как промежуточный итог. Дополнительные сведения см. в статье Данные итогов в таблице Excel.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Формула не игнорирует ячейки в скрытых столбцах

Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра

Выбрать любую ячейку в вашем наборе данных.
Пройдите по меню ДАННЫЕ –>Фильтр (или нажмите Alt + Ы, а затем не отпуская Alt, нажмите Ф; или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) для всех заголовков столбцов.
Откройте одно из выпадающих меню, например, Customer. Снимите флажок Выделить все, а затем выберите одного клиента. В нашем примере – Chevron.
Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере –ячейки Е565:H565.
Нажмите клавиши Alt+= или щелкните значок Автосумма (меню ГЛАВНАЯ). Вместо того, чтобы использовать СУММ, Excel применит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон), которая просуммирует только строки, выбранные фильтром (см. рис. 3).

Резюме: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы игнорировать скрытые строки.

Как посчитать сумму ячеек в экселе несколькими классными способами?

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

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

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

Простое сложение

Начем с легенько разминки. Если вам нужно посчитать сyмму каких-то чиcел, то самое простое — это сделать обычный пример. Для этого встаньте на любую ячейку и напишите знак равенства (=), после чего начинайте складывать нужные цифры (=15+6+94+3-10+2). Последним штрихом вам нужно будет жмахнуть клавишу Enter, тогда весь этот математический пример молниеносно преобразуется в решенный ответ.

Сложение каждой ячейки

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

Откройте документ excel, где у вас записаны какие-либо значения.
Теперь выберите какое-нибудь свободное место и поставьте туда знак «Равно» (=). В этот момент у вас активировалась функция решения примеров, и неважно каких, сложения или вычитания.
Теперь нажмите на ячeйку, где у вас записано первое число, которое вы хотите использовать для сложения. В том месте, где мы ставили знак «=», должна появится координата этой цифры, т.е. C6 или D2, и т.д. Отлично. Это то, что нам нужно.
Теперь поставьте знак «+» и нажмите на следующую ячейкy с данными, которое вы хотите использовать для сложения. Видите, как меняется значение в том месте, где мы ставили «=»? Да. Теперь там стоят уже две координаты, объединенные плюсом.
Таким же образом продолжайте искать оставшиеся цифры и когда они закончатся, вам останется нажать Enter и значения, которые вы выбрали суммируются, а сама сумма покажется в ячейке, в которой мы изначально ставили знак равенства.

Суммирование столбца

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

Выделите столбeц или его часть, после чего переходите во вкладку «Формулы» и выбирайте там пункт «Автосумма». После этой несложной манипуляции вы увидите сумму всех выделенных вами чисел. Она автоматически встанет в ближайшую свободную ячейку в вашем столбце.

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

Использование формулы с помощью параметра «СУММ»

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

Поставьте курсор в то место, где вы хотите, чтобы отображался ваш результат и поставьте туда уже знакомый нам знак равенства.
Теперь нам нужно выполнить функцию «СУММ». Ее вы можете выбрать в левом верхнем углу листа, нажав на него. Либо вы можете увидеть ее во вкладке «Формулы» , а затем выбрать «Математические» , и уже там, среди множества других функций, искать заветную СУММ. В общем жмахайте.
Вам откроется окно с аргументами функции. Здесь вы должны будете выбрать диапазон чисел, которые будете складывать. Например я хочу сложить столбик от А1 до А7. Тогда поставьте курсор в графу «Число 1» , а затем выделите нужные ячейки.
Теперь нажмите Enter и вы увидите, что всё у вас отлично посчиталось.

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

Суммирование с разных листов

Откройте (создайте) новую таблицу, где разные значения будут находиться на разных листах. Сделали: Молодцы. И тогда сразу же поставьте в любую ячейку на любом лиcте знакомый значок «=» и снова выберите функцию «СУММ», как я рассказывал выше.
Теперь, когда у вас открылось окно «Аргументы функции» , поставьте курсор в строку «Число 1» , после чего выделите нужные значения на первом листe.
Теперь ставьте курсор в строку «Число 2» , переходите на другой лист, и выделяйте все числа там. После этого нажмите ОК.

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

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

Ну на этом у меня вроде бы всё. Если остались какие-либо непонятки, то спрашивайте. С удовольствием отвечу на интересующие вас вопросы. Так же рекомендую вам подписаться на обновления моего блога, чтобы всегда быть в курсе обо всем новом и интересном. Увидимся с вами в других статьях. Пока-пока!

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.

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

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

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

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

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

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

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

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

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

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма затрат»;$M$2;»Дата»;1;»Статья»;»Статья 1″)

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Суммирование только видимых ячеек

Ссылки по теме

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

Функция СУММ – простое суммирование ячеек в Excel

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

Суммирование ячеек в Excel по условию.

Суммирование только видимых ячеек

Суммирование видимых ячеек по условию (Формулы/Formulas)

Выборочное суммирование ячеек

Как суммировать только отфильтрованные значения

Как обработать только видимые ячейки (Формулы)

суммирование значений видимых ячеек отсортированной таблицы (суммирование значений ячеек отсортированной таблицы)

Excel как посчитать сумму ячеек в столбце, если между ними есть скрытые, как сделать чтоб эти скрытые он не считал?

Пример: суммирование отфильтрованных строк в Excel

Дополнительные ресурсы

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

Сумма / количество / среднее значение видимых ячеек только в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек / строк / столбцов

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

Сумма/счет/усреднение только отфильтрованных или видимых ячеек с Kutools for Excel

Демонстрация: сумма / подсчет / среднее только отфильтрованные или видимые ячейки

Легко суммировать / подсчитывать только отфильтрованные / видимые ячейки, удаляя скрытые строки в Excel

Статьи по теме:

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Как в excel суммировать только видимые ячейки

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel для промежуточных расчетов

Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Суммирование только видимых ячеек в фильтре Excel

Выборочное суммирование ячеек таблицы в Excel

Примеры формул для расчетов промежуточных итогов в таблице Excel

Особенности использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Суммирование только видимых ячеек

Сумма по фильтру в Эксель (Excel) — как найти?

Автосумма в Excel

Где находится Автосумма в Excel

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

Автосумма в Excel – Кнопка Автосумма во вкладке Формулы

Как сделать автосумму в Excel

Автосумма в Excel – Выбор ячейки, после который вы хотите посчитать сумму

Автосумма в Excel – Кликните на кнопке Автосумма на вкладке Главная и Формулы (в данном случае на вкладке Формула)

Автосумма в Excel – Кликните клавишу Enter, чтобы завершить формулу СУММ

Автосумма в Excel не считает

Автосумма в Excel – Преобразование числа в текстовом формате, в числовой

Автосумма в Excel горячие клавиши

Автосумма в Excel – Автосумма в Excel горячие клавиши

Как использовать Автосумму с другими функциями

Автосумма в Excel – Использование кнопки Автосумма для вставки других формул (в данном случае Максимум)

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

Автосумма в Excel – Использование Автосуммы для видимых ячеек

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

Автосумма в Excel – Автосумма нескольких столбцов одновременно

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

Автосумма в Excel – Сумма выбранных ячеек вертикально по столбцам

Автосумма в Excel – Сумма выбранных ячеек горизонтально по строкам

Автосумма в Excel – Суммирование выбранных ячеек столбцам и строкам

Функция СУММ

В этом курсе:

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

Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Как посчитать сумму ячеек в экселе несколькими классными способами?

Простое сложение

Сложение каждой ячейки

Суммирование столбца

Использование формулы с помощью параметра «СУММ»

Суммирование с разных листов

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

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

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

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

А вот еще интересные статьи: