Evolcom.ru

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

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

Excel works!

menu

Если у вас есть таблица данных, вы можете вычислить сумму столбца с помощью обычной функции =SUM(). Возможно, было замечено, что эта функция не работает должным образом, когда определенные значения выбраны фильтром. Каким образом вычисляется сумма только выбранных значений? Для этого можно использовать промежуточные итоги в Excel.

Промежуточные итоги в Excel

Функция промежуточные итоги в Excel имеет 2 аргумента:

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

Промежуточные итоги в Excel

2. ссылка. Это диапазон, в котором должен быть найден итог.

Пример. В столбце «Сумма» добавьте сумму за все месяцы, в которых было продано более 20 единиц товара.

Как вы видите на первой картинке, мы отобрали фильтром все значение по столбцу «Продано единиц» большие 20. В ячейке С15 записываем формулу вида

Значение функции будет меняться при изменении условий фильтра.

Помните, что то же самое действие можно выполнить с помощью =SUMMESLY().

Промежуточные итоги в Excel, сумма только видимых ячеек

Как считать только те ячейки, которые не были скрыты?

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

Промежуточные итоги в Excel. Создать таблицу на отдельном листе

Т.е. с промежуточными итогами для каждой группы строк, например, по полугодиям в нашем примере.

Промежуточные итоги в Excel

Что нужно сделать

1. убедитесь, что данные отсортированы хотя бы по одной колонке.

2. выбрать диапазон

3. На панели инструментов перейдите в меню Данные в разделе Структура и выберите пункт Промежуточные итоги.

Читайте так же:
Как перенести ссылку при автоматическом заполнении вниз / вправо в Excel?

Промежуточные итоги в Excel

В открывшемся меню выполните настройки, выберите столбец для сортировки (Семестр) и что делать (Сделка), а также столбец для вычисления:

Промежуточные итоги в Excel

Она должна выглядеть, как на рисунке выше.

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

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

— если используете возможность Таблица (Вставка -Таблица), в эту интерактивную таблицу при расчете суммы автоматом добавляется «Строка итогов», которая во многом заменяет промежуточные итоги. В открывшейся вкладке Работа с таблицами – Конструктор вы можете настроить интерактивную таблицу почти под любые условия. Я пока почти не использую эту возможность, т.к. такая таблица даже на 10 тысяч строк может значительно грузить систему. Хотя в Excel я работаю до 500 тыс. строк без проблем, даже без PowerPivot.

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

Использование функции СУММЕСЛИ в Microsoft Excel. В этой статье описывается синтаксис и использование этой формулы.

Описание

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

Синтаксис

Аргументы функции СУММЕСЛИ описаны ниже.

Аргументом является номер функции. Число от 1 до 11 или от 101 до 111, обозначающее функцию, используемую для вычисления промежуточных итогов. Функции с 1 по 11 учитывают скрытые вручную строки, а функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.

Читайте так же:
Как преобразовать 1-12 в название месяца в Excel?

Function_num
(включая скрытые строки)

Function_num
(игнорирует скрытые строки)

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

Ссылка2;. Необязательно. Имеются именованные интервалы или ссылки 2-254, для которых должны быть рассчитаны промежуточные итоги.

Примечания

Если внутри формулы «ref1;ref2;. » Во избежание двойного суммирования, (вложенные итоги) будут проигнорированы.

Для констант «номер функции» от 1 до 11 функция TOTAL.TOTAL учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или показать) в группе Ячейки главного приложения Excel. Эти константы используются для создания промежуточных итогов, учитывающих скрытые и не скрытые номера в списке. Для констант «номер функции» 101 — 111 функция TOTALS.TOTALS удаляет скрытые значения строк с помощью команды Hide Rows. Эти константы используются для получения промежуточных итогов, которые учитывают только не скрытые номера в списке.

Функция TOTALS.TOTALS исключает все строки, не включенные в результат фильтра, независимо от значения используемой константы «функция_число».

И ТОГИ. Колонки данных или вертикальные наборы данных можно суммировать с помощью TOTALS. Поддержка строк данных или горизонтальных наборов данных отсутствует. Вы можете определить промежуточные итоги для горизонтальных наборов данных, используя константу номера функции 101 или выше (например, TOTALS. Если скрыть столбец (TOTALS(109;B2:G2)), результат останется неизменным. Если в вертикальном наборе данных суммируются промежуточные итоги, то скрытая строка будет влиять на суммирование.

T OTALS, если среди ссылок есть трехмерные ссылки. Функция TOTALS возвращает значение ошибки #ЗОНА!

Пример

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

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