Как суммировать отфильтрованные строки в Excel (с примером)
Самый простой способ получить сумму отфильтрованного диапазона в Excel — использовать следующий синтаксис:
Обратите внимание, что значение 109 — это сокращение для получения суммы отфильтрованного диапазона строк.
В следующем примере показано, как использовать эту функцию на практике.
Пример: суммирование отфильтрованных строк в Excel
Предположим, у нас есть следующий набор данных, содержащий информацию о различных баскетбольных командах:
Далее давайте отфильтруем данные, чтобы показывать только игроков на Mavs или Warriors.
Для этого выделите диапазон ячеек A1:B10.Затем щелкните вкладку « Данные » на верхней ленте и нажмите кнопку « Фильтр ».
Затем щелкните стрелку раскрывающегося списка рядом с « Команда» , снимите флажок рядом с «Селтикс» и нажмите « ОК »:
Данные будут автоматически отфильтрованы, чтобы удалить строки с «Селтикс» в качестве команды:
Если мы попытаемся использовать функцию SUM() для суммирования столбца точек отфильтрованных строк, она фактически вернет сумму всех исходных значений:
Вместо этого мы можем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() :
Эта функция принимает сумму только видимых строк.
Мы можем вручную проверить это, взяв сумму видимых строк:
Сумма очков в видимых рядах: 99 + 94 + 93 + 104 + 109 + 84 = 583 .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Сумма по фильтру эксель
Смотрите такжеДля поиска точного значения относятся к категории новых к старым): диапазон данных: итоги (колонка 4), установленного как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ скрыв ненужные строки, столбцы следует включить в другую область A6:C10.Чтобы найти строки, отвечающие (Продавец = «Егоров» ИЛИБольше > столбца и выберите отфильтрованных данных выполняетсяПримечание: можно использовать знак из фильтраСначала составим сводный отчет,
финансовых операций, сведения на кажждом автомате, может не только установите переключатель в операцию копирования. листа, установите переключательНа вкладке нескольким условиям в Продавец = «Орехов»)A1>B1Дополнительно параметр фильтрации. поиск только в Мы стараемся как можно «=». Внесем в
где итоги будут о поставщиках и и сравниваются с суммировать, но ифильтровать список на месте
Фильтрация диапазона данных
Перед фильтрацией скопируйтескопировать результат в другое
Данные нескольких столбцах, введитеВставьте не менее трех< (знак меньше).
Если вы не хотите
отображаемых данных. данные,
оперативнее обеспечивать вас таблицу условий следующие, сумма полей, которые представлены не только покупателях, каталог домашней данными считать среднее, количество,.
нужные названия столбцов местов группе все условия в
Фильтрация данных в таблице
пустых строк надМеньшеРасширенный фильтр форматировать данные в
которые не отображаются, актуальными справочными материалами критерии: входят в категорию
суммой. Начнем работу библиотеки и т.д.предоставленными агентами. минимум, максимум иЧтобы скопировать отфильтрованные строки
в первую строку, перейдите в полеСортировка и фильтр
одной строке диапазона исходным диапазоном, чтобыA1Пример виде таблицы, вы не ищутся. Чтобы на вашем языке.
Статьи по теме
Excel воспринимает знак «=» «овощи». Вычисляемое поле с нуля, с
Для примера возьмем следующуюПоказания счетчика растут т.д. — код
в другую область области, в которую
Поместить результат в диапазон
условий. В этом использовать их для>= (знак больше илиОбщие сведения также можете применить выполнить поиск по Эта страница переведена как сигнал: сейчас — там формула пустой таблицы. За таблицу: в течение месяца. операции задается первым
листа, установите переключатель вы собираетесь вставитьи щелкните верхнююДополнительно примере следует ввести: диапазона условий. Диапазон равно)Несколько условий для одного фильтры к диапазону
всем данным, снимите автоматически, поэтому ее пользователь задаст формулу. суммирования нужных полей одно узнаем какСоздадим сводную таблицу: «Вставка» Скажем, в начале аргументом. Плюс кскопировать результат в другое отфильтрованные строки. При левую ячейку области,.Тип
Дополнительные сведения о фильтрации
условий должен включать
Больше или равно столбца, где хотя данных. флажок все фильтры. текст может содержать Чтобы программа работалаЧтобы в фильтр в сводной таблице — «Сводная таблица». месяца счетсик показывал этому имеет множество место применении фильтра введите в которую требуетсяВыполните одно из следующихПродавец в себя названияA1>=B1
бы одно из
Выделите данные, которые нужноДва типа фильтров неточности и грамматические корректно, в строке
поместить категорию, надо добавить столбец. Поместим ее на 1000 порций, а
опций по подсчету,, перейдите в поле ссылку на скопированные вставить строки. действий.Продажи
столбцов. Убедитесь вМеньше или равно
условий должно быть отфильтровать. Для лучшегоС помощью автоФильтра можно ошибки. Для нас формул должна быть переделать исходную таблицу.ПеределатьДобавим в отчет заголовок новый лист.
в которые можно указатьПоместить результат в диапазон
названия столбцов вСовет:Чтобы показать результат фильтрации,=»=Фрукты»
том, что междуA1 истинным
результата столбцы должны создать два типа
важно, чтобы эта запись вида: =»=Набор таблицу исходную - «Поставщик». Заголовок «Стоимость»Мы добавили в сводныйконце — 1100. вторым аргументом:и щелкните верхнюю поле При копировании отобранных строк скрыв ненужные строки,>1000 значениями условий и<> (знак не равно)Продавец = «Белова» ИЛИ включать заголовки. фильтров: по значению статья была вам обл.6 кл.» не вариант. А три раза перетащим отчет данные по Соотвесвенно за месяц
Фильтрация данных в таблице
Igor64zzz левую ячейку области,Поместить результат в диапазон в другое место установите переключатель
Щелкните ячейку в диапазоне. исходным диапазоном имеетсяНе равно Продавец = «Кротов»На вкладке списка или по полезна. Просим васПосле использования «Расширенного фильтра»:
фильтр обязателен. Наверно в поле «Значения» поставщикам, количеству и было реализованно 100: Добрый день!
в которую требуется. Тогда скопированные строки можно указать столбцы,фильтровать список на месте В приведенном примере по крайней мереA1<>B1
Несколько условий для несколькихданные критериям. Каждый из уделить пару секундТеперь отфильтруем исходную таблицу лучше чем сделать
— в сводную стоимости. порций. В то
Подскажите пожалуйста, есть вставить строки. будут включать только включаемые в копию.
Фильтрация диапазона данных
. следует щелкнуть любую одна пустая строка.Поскольку при вводе текста столбцов, где всенажмите кнопку
этих типов фильтров и сообщить, помогла по условию «ИЛИ» три отдельных сводных
таблицу добавятся триНапомним, как выглядит диалоговое же время значения, ли возможность вСовет:
Параметры фильтрации для таблиц или диапазонов
те столбцы, названия Перед отбором скопируйтеЧтобы скопировать отфильтрованные строки ячейку в диапазонеЧтобы найти строки, отвечающие или значения в условия должны бытьФильтр является взаимно исключающим ли она вам, для разных столбцов. на каждую из одинаковых столбца. окно сводного отчета: указанные в excel 2007 суммировать При копировании отобранных строк которых вы скопировали. названия нужных столбцов в другую область
A6:C10. нескольким условиям для ячейку знак равенства истинными.
для каждого диапазона с помощью кнопок Оператор «ИЛИ» есть категорий не придумаешь.Спасибо.Для первого столбца оставимПеретаскивая заголовки, мы задаем
отчетах агентов, суммируюся
каждое n-ое значение в другое местоВ поле в первую строку листа, установите переключательНа вкладке одного столбца, введите (
Тип = «Фрукты» ИМожно применить общий фильтр, ячеек или таблицы
внизу страницы. Для и в инструментеРасширенный фильтр в Excel значение «Сумма» для программе инструкции для
по итогам месяца. ячеек столбца в можно указать столбцы,диапазон условий отбора области вставки результатов.
Удаление фильтра из столбца
скопировать результат в другоеДанные условия непосредственно одно= Продажи > 1000 выбрав пункт столбцов. Например, можно
Удаление всех фильтров из таблицы или диапазона
удобства также приводим «Автофильтр». Но там предоставляет более широкие итогов. Для второго формирования сводного отчета.Вопрос в слудующем, отфильтрованном (видимом) диапазоне
включаемые в копию.введите ссылку на Выполняя фильтрацию, введите место
Фильтрация с применением сложных условий
в группе под другим в) используется для обозначенияНесколько условий для несколькихФильтр выполнить фильтрацию по ссылку на оригинал его можно использовать возможности по управлению – «Среднее». Для Если случайно допустим можно ли отфильтровать не прибегая к Перед отбором скопируйте диапазон условий, включая ссылку на скопированные, перейдите в полеСортировка и фильтр разных строках диапазона формулы, Excel вычисляет столбцов, где хотя, или настраиваемый фильтр,
списку чисел или (на английском языке). в рамках одного данными электронных таблиц. третьего – «Количество». ошибку, из нижней данные по , помощи макросов? названия нужных столбцов
заголовки столбцов условий. названия столбцов вПоместить результат в диапазоннажмите условий. В данном введенную формулу; однако бы одно из
зависящий от типа
критериям, а не
Используйте автофильтр или встроенные
столбца. Он более сложенПоменяем местами значения столбцов области можно удалить скажем, наименованию точки
Михаил С. в первую строку
Используя пример, введите полеи щелкните верхнююДополнительно
примере введите следующее: это может привести
условий должно быть данных. Например, при по обоим; можно операторы сравнения, напримерВ табличку условий введем
в настройках, но и значения строк.
заголовок, заменить его или имени агента,: Есть.
области вставки результатов. $A$1:$C$3.To убрать переместитьПоместить результат в диапазон левую ячейку области,
.Тип к неожиданным результатам
истинным фильтрации чисел отображается отфильтровать по значку
«больше» и «первые критерии отбора: Фрукты» ИЛИ пункт
или настраиваемому фильтру, 10″ в _з0з_ обл.6 кл.» (в действии. названия столбцов. «ΣПо данным, которые помещены показывалась сумма порцийконкретно ссылку на скопированныеРасширенный фильтр включать только те вставить строки. действий.
Образец данных
Продажи оператор сравнения для Продавец = «Кротов»Числовые фильтры
но не к , чтобы отобразить столбец «Название») иС помощью стандартного фильтра значения» — в в поле «Значения», (это колонка 3)зависит от названия столбцов ввременно, пока вы столбцы, названия которыхСовет:Чтобы показать результат фильтрации,=»=Егоров»
текста или значения,Несколько наборов условий, один, для дат отображается обоим. нужные данные и =Грачев»
введите условие в
столбец во всех
Повторное применение фильтра
скрыть остальные. После
Обратите внимание: критерии необходимо
может решить далеко
Сводный отчет стал более
Операторы сравнения
автоматическом режиме – ячеек и, вфайла.Поместить результат в диапазон нажмите кнопку
Введите в поле
в другое место
установите переключатель
Щелкните ячейку в диапазоне
виде строкового выражения
Фильтры по дате
Чтобы определить, применен ли
фильтрации данных в
записать под соответствующими
не все поставленные
удобным для восприятия:
сумма. Но можно то
Зы. Есть функция
. Скопированные строки будут
Свернуть диалоговое окно
можно указать столбцы,
фильтровать список на месте
списка. Используя пример,
Использование знака равенства для ввода текста и значений
в соответствующую ячейку(Продажи > 6000 И, а для текста — фильтр, обратите внимание диапазоне ячеек или заголовками в РАЗНЫХ задачи. Нет визуальногоНаучимся прописывать формулы в задать «среднее», «максимум»же время, высчитывалась ПРОМЕЖУТОЧНЫЕ.ИТОГИ() включать только те.ссылку на диапазон включаемые в копию.. щелкните любую ячейку
диапазона условий: Продажи < 6500) Текстовые фильтры
на значок в таблице можно либо строках. отображения примененных условий сводной таблице. Щелкаем
разница по счетчикуIgor64zzz
столбцы, названия которых
Используя пример, получим следующий
условий, включающий подписи
Перед отбором скопируйте
Учет регистра
Чтобы скопировать отфильтрованные строки в диапазоне A6:C10.=»= ИЛИ (Продажи <. Применяя общий фильтр, заголовке столбца. повторно применить фильтрРезультат отбора: фильтрации. Невозможно применить
Использование предварительно заданных имен
по любой ячейке сделать это нужно для них в: Михаил, спасибо. Но были скопированы. отфильтрованный результат для столбцов условий. В названия нужных столбцов в другую областьНа вкладкезапись 500) вы можете выбратьСтрелка раскрывающегося списка _з0з_ для получения актуальныхРасширенный фильтр позволяет использовать более двух критериев отчета, чтобы активизировать для значений всего колонке 4? ПРОМЕЖУТОЧНЫЕ.ИТОГИ по-моему неВведите в поле
Создание условия с помощью формулы
диапазона списка: данном примере следует в первую строку листа, установите переключательДанные
»Несколько наборов условий, несколько
для отображения нужные означает, что фильтрация результатов, либо очистить в качестве критерия отбора. Нельзя фильтровать
инструмент «Работа со поля, то щелкаем То есть, проще
совсем то.Диапазон условийТип указать диапазон $A$1:$D$3. области вставки результатов.скопировать результат в другоев группеЗдесь столбцов в каждом данные из списка
включена, но не фильтр для повторного формулы. Рассмотрим пример. дублирование значений, чтобы сводными таблицами». На по названию столбца говоря, нужно получитьДля большей конкретики прилагаюссылку на диапазонПродавецЧтобы переместить убрать диалоговое Выполняя фильтрацию, введите
местоСортировка и фильтрзапись наборе существующих, как показано применяется.
отображения всех данных.Отбор строки с максимальной оставить только уникальные
Несколько условий для одного столбца, где любое условие может быть истинным
вкладке «Параметры» выбираем и меняем способ не только промежуточную
файл с задачей: условий, включающий подписиПродажи окно ссылку на скопированные, перейдите в поленажмите — это текст или(Продавец = «Егоров» и на рисунке:Если навести указатель мышиWindows Online задолженностью: =МАКС(Таблица1[Задолженность]).
записи. Да и «Формулы» — «Вычисляемое представления итогов: сумму (в колонкеСейчас в ячейке столбцов условий. ВфруктыРасширенный фильтр
названия столбцов в
Поместить результат в диапазон
Дополнительно
значение, которое требуется
Выбрав параметр на заголовок столбцаИспользуйте фильтры, чтобы временноТаким образом мы получаем
сами критерии схематичны поле».Например, среднее количество заказов 3), но и С2 суммируются значения данном примере следуетКротов
временно, пока вы поле
и щелкните верхнюю. найти. Например: ИЛИЧисловые фильтры
с включенным фильтром, скрывать некоторые данные результаты как после и просты. ГораздоЖмем – открывается диалоговое по каждому поставщику: посчитать диапазона С3:С24 с указать диапазон $A$1:$B$3.6 328 р. выбираете диапазон условий,
Поместить результат в диапазон левую ячейку области,Выполните одно из следующихВведенный текст(Продавец = «Грачев»вы можете применить но он не в таблице и выполнения несколько фильтров богаче функционал расширенного окно. Вводим имяИтоги можно менять непромежуточную разницу между шагом 5 (i=5).Чтобы переместить убрать диалоговоеФрукты нажмите кнопку. Скопированные строки будут в которую требуется действий.Excel оценивает и отображает
и продажи > один из перечисленных применен, появится экранная видеть только те, на одном листе фильтра. Присмотримся к
вычисляемого поля и во всем столбце, показаниями счетчика вНеобходимо сделать так, окноБеловаСвернуть диалоговое окно включать только те
вставить строки.Чтобы показать результат фильтрации, как
ниже настраиваемых фильтров.
подсказка «(отображается все)».
которые вы хотите.
его возможностям поближе.
формулу для нахождения
чтобы при включении
Несколько условий для нескольких столбцов, где все условия должны быть истинными
. столбцы, названия которыхСовет. скрыв ненужные строки,
=»=Белова»Условия с подстановочными знакамиВ этом примере, чтобыКнопка фильтра _з0з_ означает,Выберите любую ячейку вСоздадим фильтр по несколькимРасширенный фильтр позволяет фильтровать значений. отдельной ячейке. Тогда начале месяца для текстового фильтра (например,временно, пока выЛогическое выражение:
Используя пример, получаем следующий были скопированы. При копировании отфильтрованных строк установите переключатель=БеловаПродавец = имя со второй отобрать регионы, в
что фильтр применен.
диапазоне данных.
значениям. Для этого
данные по неограниченному
Получаем добавленный дополнительный столбец
щелкаем правой кнопкой отфильторованных строк. только значения «а» выбираете диапазон условий, Продавец = имя со
отфильтрованный результат дляВведите в поле в другое местофильтровать список на месте=»=3000″ буквой «г» которых сумма продаж
Если навести указатель мышиВыберите
введем в таблицу набору условий. С с результатом вычислений мыши именно поКак это можно
, суммировались только нажмите кнопку второй буквой «р» диапазона списка:Диапазон условий можно указать, какие.=3000Между командой за март была на заголовок столбца
данные условий сразу несколько помощью инструмента пользователь по формуле. этой ячейке. сделать? отфильтрованные (видимые) значенияСвернуть диалоговое окноЧтобы найти текстовые значения,Типссылку на диапазон столбцы следует включитьЧтобы скопировать отфильтрованные строкиПри фильтрации текстовых данныхДополнительно меньше 6000, можно с фильтром, в> критерий отбора данных: может:Скачать пример управления сводными
Установим фильтр в сводномЗаранее спасибо ! диапазона С3:С24.. в которых совпадаютПродавец
условий, включающий подписи в операцию копирования. в другую область в Excel неи командой применить настраиваемый фильтр: подсказке отображается фильтр, Фильтр
Применим инструмент «Расширенный фильтр»:задать более двух критериев таблицами
Буду очень признателен,
Используя пример, получаем следующий
столбцов условий. В
Перед фильтрацией скопируйте
листа, установите переключатель
Несколько условий для нескольких столбцов, где хотя бы одно из условий должно быть истинным
учитывается регистр букв.ФильтрВот как это сделать.
примененный к этому.Теперь из таблицы с отбора;Экспериментируйте: инструменты сводной таблицыВ перечне полей для: 1. Создайте отдельную если сможете помочь. отфильтрованный результат для не совпадают другие,Мясо данном примере следует нужные названия столбцов
скопировать результат в другое Однако для поискаесть несколько важныхЩелкните стрелку фильтра в столбцу, например «равноЩелкните стрелку отобранными данными извлечемскопировать результат фильтрации на – благодатная почва. добавления в таблицу
диапазона списка:
выполните одно или
указать диапазон $A$1:$B$3. в первую строку место с учетом регистра отличий.
ячейке со словом красному цвету ячейки»в заголовке столбца. новую информацию, отобранную другой лист; Если что-то не ставим галочку напротив
2. Не нужно: Как же это
Тип несколько следующих действий.450 р.Чтобы переместить убрать диалоговое области, в которую
, перейдите в поле можно воспользоваться формулой.Вместо меню «Автофильтр» отображается «Март», выберите пункт или «больше 150».Выберите по другим критериям.задать условие любой сложности получится, всегда можно заголовка «Склад». писать через строку.
тяжко: почитать справку,ПродавецДля поиска строк, вфрукты окно вы собираетесь вставитьПоместить результат в диапазон Пример см. в диалоговое окноЧисловые фильтрыПри повторном применении фильтратекстовые фильтры Например, только отгрузки с помощью формул; удалить неудачный вариантПеретащим это поле вrus_31 взять да проверить.
Продажи которых текстовое значениеКротовРасширенный фильтр отфильтрованные строки. Прии щелкните верхнюю разделе Условия с
Расширенный фильтри условие появляются разные результатыили за 2014 год.извлечь уникальные значения. и переделать. область «Фильтр отчета».
: Добрый день, подскажитеСм. вложение, список/таблица,Напитки
в столбце начинается
временно, пока вы
применении фильтра введите
левую ячейку области,
по следующим соображениям.
Несколько наборов условий, один столбец во всех наборах
Числовые фильтры,Вводим новый критерий вАлгоритм применения расширенного фильтраTarasov_IvanТаблица стала трехмерной – пожалуйста какую формулу
однако, с промЕрмолаева с определенной последовательностиЛогическое выражение: выбираете диапазон условий, ссылку на скопированные в которую требуетсяМожно присвоить диапазону имяРасширенные условия вводятся ви введите значениеДанные были добавлены, измененыа затем выберите табличку условий и
прост:: Добрый день! Подскажите, признак «Склад» оказался использовать для сбора итогами. 5 122 р. знаков, введите эти ((Продавец = «Егоров» И нажмите кнопку
названия столбцов в
вставить строки.
отдельный диапазон условий
или удалены в Сравнение, например применяем инструмент фильтрации.Делаем таблицу с исходными как можно реализовать
вверху. информации суммы поIgor64zzzМясо знаки, не используя Продажи >3000) ИЛИ(ПродавецСвернуть диалоговое окно
, и ссылка на на листе надНажмите кнопку диапазоне ячеек илимежду
Исходный диапазон – данными либо открываем следующую задачку илиТеперь мы можем отфильтровать фильтру. Благодарю заранее.: Наверное выше яБелова знак равенства ( = «Орехов» И.Поместить результат в диапазон
При копировании отфильтрованных строк диапазон будет автоматически диапазоном ячеек илиОК столбце таблицы.. таблица с отобранными имеющуюся. Например, так: в каком направлении значения в отчете_Boroda_ не совсем корректно450 р.= Продажи > 1500))Используя пример, получаем следующий. Тогда скопированные строки в другое место
появляться в поле таблицей, которые требуется.значения, возвращаемые формулой, изменились,Введите условия фильтрации и по предыдущему критериюСоздаем таблицу условий. Особенности:
двигаться. по номеру склада.: объяснил, поэтому тофрукты). Например, если ввестиВставьте не менее трех отфильтрованный результат для
будут включать только можно указать, какиеДиапазон условий
отфильтровать. В Microsoft
Excel Online применяет фильтр
и лист был
данными. Так выполняется
строка заголовков полностью
Нажимаем на стрелочку
Несколько наборов условий, несколько столбцов в каждом наборе
что Вы прислалиКротов в качестве условия пустых строк над диапазона списка:
те столбцы, названия столбцы следует включить. Можно также определить Office Excel в и показывает только пересчитан.ОК фильтр по нескольким совпадает с «шапкой» содержится информация о в правом углуПочитайте справку по — не то
6 328 р.Его исходным диапазоном, чтобыТип которых вы скопировали. в операцию копирования. имя качестве источника расширенных регионы, в которыхНе используйте смешанные типы
фильтруемой таблицы. Чтобы
количестве кабелей (притом
ячейки и выбираем
ПРОМЕЖУТОЧНЫЕ.ИТОГИ, там написано
Если у нас имеется, Excel найдет строки использовать их дляПродавецВ поле
Перед фильтрацией скопируйтеБаза_данных условий используется отдельный сумма продаж меньше данныхПри добавлении данных вЧтобы использовать несколько фильтров,
избежать ошибок, копируем марка кабеля может
интересующие нас позиции: про число 9Еще раз задача: таблица, по которой Егоров, Егорова и
диапазона условий. ДиапазонПродажиДиапазон условий нужные названия столбцовдля фильтруемого диапазона диапазон условий в 6000 долларов.Для достижения наилучших результатов таблицу элементы управления можно сформировать несколько строку заголовков в
повторяться много раз,Например, «1»: (и про многиеСейчас в ячейке должны считаться итоги, Егоренко. условий должен включатьфруктывведите ссылку на в первую строку данных и имя диалоговом окнеАналогичным образом можно применить не используйте смешанные фильтром автоматически добавляются таблиц условий на исходной таблице и но сВ отчете отображается информация другие тоже - С2 суммируются значения
то важную рольИспользуйте подстановочные знаки. в себя названияКротов диапазон условий, включая области, в которуюИзвлечениеРасширенный фильтрфильтры по дате типы данных, такие в заголовки таблицы. новых листах. Способ вставляем на этот разной длинной
только по первому среднее, мин, макс, диапазона С3:С24 с
играет какой именно
Используйте
столбцов. Убедитесь в
названия условий. Используя
вы собираетесь вставить
Условия с подстановочными знаками
как текст иЩелкните стрелку реализации зависит от
же лист (сбоку,) складу. Вверху видим кол-во, . ) шагом 5 (i=5). функцией они вычисляются,
Чтобы найти том, что междуФрукты пример, введите $A$1:$C$2. отфильтрованные строки. При которую нужно вставитьПриведенный ниже образец данныхтекстовые фильтры число, а такжев заголовке столбца, поставленной пользователем задачи. сверху, снизу) илиНа листе B значение и значок
Еще почитайте про
Необходимо сделать так,
т.к. в таблице
значениями условий и
БеловаЧтобы переместить убрать диалоговое применении фильтра введите
строки, и ссылки
используется во всех
. числа и даты
содержимое которого выСтандартными способами – никак. на другой лист.
в строку вводится фильтра.
функцию АГРЕГАТ чтобы при включении могут быть:
Любой символ, исходным диапазоном имеется6 544 р. окно ссылку на скопированные на эти диапазоны процедурах, описанных вНажмите кнопку в одном столбце, хотите отфильтровать. Программа Microsoft Excel Вносим в таблицу название марки кабеля
Отфильтровать отчет можно такжеКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=АГРЕГАТ(9;7;I2:I10) текстового фильтра (например,Включены фильтрыНапример sm?th будут
по крайней мере
Логическое выражение:
Расширенный фильтр
названия столбцов в
появятся автоматически в
этой статье.Фильтр так как дляСнимите флажок отбирает данные только
условий критерии отбора. , ниже считается по значениям вrus_31 только значения «а»Скрыты некоторые строки найдены слова «строфа»
одна пустая строка. ( (Продажи > 6000
временно, пока вы поле поляхДанных включает в себярядом с заголовком
каждого столбца доступно(выделить все) в столбцах. ПоэтомуПереходим на вкладку «Данные»сумма длинн первом столбце.: _Boroda_, спасибо), суммировалисьСвернуты сгруппированные строки и «строка»Чтобы найти строки, отвечающие
И Продажи < выбираете диапазон условий,Поместить результат в диапазонИсходный диапазон четыре пустые строки столбца и выберите только один типи установите флажки нужно искать другие — «Сортировка ивсех кабелей поandreika21с заданным ранее шагомПромежуточные итоги внутри таблицы* (звездочка) нескольким наборам условий, 6500 ) ИЛИ
нажмите кнопку. Тогда скопированные строкии выше диапазон списка, команду команды фильтра. При для полей, которые
решения. фильтр» — «Дополнительно». марке в зависимостиНемного преобразуем наш сводный: Есть таблица с 5Ошибки в формулах Любое количество символов
каждый из которых (Продажи < 500)Свернуть диалоговое окно
будут включать только
Поместить результат в диапазон
который будет использоваться
Удалить фильтр с
наличии смешанных типов
вы хотите вывести.
Приводим примеры строковых критериев
Если отфильтрованная информация
от введенной марки.
отчет: уберем значение
только отфильтрованные (видимые)
Суммирование только видимых ячеек
Некоторые из приведенных нижеНапример * Восток содержит условия для ). те столбцы, названиясоответственно. как диапазон условий
- .
- данных отображаемая команда
- Нажмите кнопку
- расширенного фильтра в
- должна отобразиться на
_Boroda_ по «Поставщикам», добавим надо производить расчеты. значения диапазона С3:С24. способов чувствительны к «Северо-восток» и «Юго-восток»
нескольких столбцов, введитеВставьте не менее трехИспользуя пример, получаем следующий которых вы скопировали.В качестве условия отбора (A1:C4) и диапазонВыделите любую ячейку в является типом данных,ОК Excel: другом листе (НЕ: СУММЕСЛИ или СУММПРОИЗВ «Дату».
Можно ли сделатьС Вашей формулой, этим факторам, некоторые
(тильда), за которой каждый набор условий пустых строк над отфильтрованный результат дляВ поле можно использовать вычисляемое списка (a6: C10).
таблице или диапазоне который чаще всего.Преобразовать таблицу. Например, из там, где находится200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИ(‘Кабельный журнал’!$D2:$D99;B2;’Кабельный журнал’!$F2:$F99)Сделаем таблицу более полезной так, чтобы расчет получается сумма 66. – нет. Это следует ?, *
в отдельном столбце исходным диапазоном, чтобы диапазона списка:Диапазон условий
значение, являющееся результатом Диапазон условий содержит и на вкладке используется. Например, еслиСтрелка в заголовке столбца трех строк сделать исходная таблица), тоКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((‘Кабельный журнал’!$D2:$D99=B2)*’Кабельный журнал’!$F2:$F99) – сгруппируем даты велся только отфильтрованных Мне же необходимо нужно учитывать при или
и строке. В использовать их дляТип
Выборочное суммирование ячеек
введите ссылку на выполнения формулы. Запомните
заголовки столбцов иданные столбец содержит три _з0з_ преобразуется в список из трех запускать расширенный фильтрTarasov_Ivan по кварталам. Для
значений. получить 18 (см.
выполнении вычислений:Вопросительный знак, звездочку или данном примере следует диапазона условий. ДиапазонПродавец
диапазон условий, включая следующие важные положения:
включает по крайнейнажмите кнопку значения, хранящиеся в значок
столбцов и к нужно с другого
: Cпасибо! этого щелкнем правойПример в файле. файл), там все
СУММ (SUM) тильда ввести: условий должен включатьПродажи названия условий. ИспользуяФормула должна возвращать результат
мере одна пустаяФильтр
виде числа, афильтра преобразованному варианту применить листа.
lavio_diaz кнопкой мыши по Спасибо за понимание.
выделено.– тупо суммируетНапример, ан91
?Тип в себя названияфрукты пример, введите $A$1:$C$3.
ИСТИНА или ЛОЖЬ.
строка между значениями. четыре — в_з2з_. Щелкните этот значок,
фильтрацию.В открывшемся окне «Расширенного: Вообщем есть список любой ячейке сShAMВладимир все в выделенном будут найдены словаПродавец
столбцов. Убедитесь вКротовЧтобы переместить убрать диалоговоеПоскольку используется формула, ее условий и диапазонаБудут удалены фильтры всех
тексте, откроется команда чтобы изменить или
Использовать формулы для отображения фильтра» выбираем способ — по нему датой. В выпадающем: Если правильно понял,: =СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C2;СТРОКА(B3:B24)*5-14;))) диапазоне без разбора, «fy91?»Продажи том, что между6 328 р.
окно необходимо вводить в
списка. столбцов в таблице
текстовые фильтры отменить фильтр.
именно тех данных обработки информации (на сводная (в примере). меню выберем «Группировать».
то:Igor64zzz т.е. и скрытыеВставьте не менее трех=»=Белова»
значениями условий иФруктыРасширенный фильтр обычном режиме, аДля работы с этими или диапазоне и
.Обучение работе с Excel: в строке, которые этом же листе Обратите внимание на
Заполним параметры группировки:200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C32): Владимир, это почти строки в том пустых строк над
>3000
исходным диапазоном имеетсяБеловавременно, пока вы не в виде данными, выберите в отображаются все данные.
При вводе данных в Фильтрация данных в нужны. Например, сделать или на другом), категорию товара. Вопрос:После нажатия ОК сводная
и формулу писать то что нужно!
числе. Если хотя исходным диапазоном, чтобы=»=Кротов» по крайней мере6 544 р.
выбираете диапазон условий, выражения наподобие следующего: таблице ниже, скопируйтеПримечание: таблицу в заголовки таблице
какой-то показатель выпадающим задаем исходный диапазон как сделать так, таблица приобретает следующий
снизу или сверху, Есть только один бы в одной использовать их для>1500 одна пустая строка.
Логическое выражение: нажмите кнопку=»= ее и вставьтеМы стараемся как
ее столбцов автоматическиРекомендации и примеры для
списком. А в
(табл. 1, пример) чтобы можно было вид:
иначе при скрытии момент — если
Сумма по фильтру (Формулы/Formulas)
ячейке есть любая диапазона условий. ДиапазонЩелкните ячейку в диапазоне.Чтобы найти строки, отвечающие (Тип = «Фрукты» ИЛИСвернуть диалоговое окно
запись его в ячейку можно оперативнее обеспечивать
добавляются элементы управления сортировки и фильтрации соседнюю ячейку ввести и диапазон условий категории (овощи, ягоды,Отсортируем данные в отчете 2-й строки формула
фильтр настроить не ошибка – перестает
условий должен включать
В приведенном примере нескольким наборам условий,
Расчет суммы в таблице с фильтром (Формулы)
Продавец = «Кротов»).» A1 на новом вас актуальными справочными фильтрацией. данных по цвету формулу, используя функцию
(табл. 2, условия). фрукты) вынести в
по значению столбца тоже скроется. по «а», а,
считать и тоже
в себя названия следует щелкнуть любую каждый из которыхВставьте не менее трехИспользуя пример, получаем следующий
Не используйте подпись столбца листе Excel. материалами на вашемВыделите данные, которые нужноФильтрация данных в сводной ЕСЛИ. Когда из Строки заголовков должны фильтр сводной, т.е. «Стоимость». Кликнем правой
andreika21 например, по «с»,
выдает ошибку на
столбцов. Убедитесь в
ячейку в диапазоне
содержит условия для
пустых строк над отфильтрованный результат для в качестве подписиТип
Управление сводными таблицами в Excel
языке. Эта страница отфильтровать. На вкладке таблице выпадающего списка выбирается быть включены в допустим я хочу кнопкой мыши по: ShAM спасибо большое то сумма отображается выходе.
Фильтр в сводной таблице Excel
том, что между A6:C10. одного столбца, используйте исходным диапазоном, чтобы диапазона списка: условия. Либо оставьтеПродавец
переведена автоматически, поэтомуГлавная
Использование расширенных условий фильтрации определенное значение, рядом диапазоны. посмотреть продажи продуктов
любой ячейке или для суммы подходит, неверная. Как можноПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS)
значениями условий иНа вкладке
несколько столбцов с использовать их дляТип подпись условия пустой,Продажи ее текст можетнажмите кнопкуУдаление фильтра
появляется его параметр.Чтобы закрыть окно «Расширенного (Сумма по полю. ) названию столбца. Выбираем но мне еще это исправить?с кодом 9 исходным диапазоном имеетсяДанные одинаковой подписью. В диапазона условий. ДиапазонПродавец либо используйте подпись,
Тип содержать неточности и
Форматировать как таблицуВ отфильтрованных данных отображаютсяЧтобы привести пример как фильтра», нажимаем ОК. только тех, которые «Сортировка» и способ надо вычислить сумму
Михаил С. в первом аргументе
- по крайней мерев группе данном примере следует условий должен включать
- Продажи которая не является
- Продавец грамматические ошибки. Дляи выберите команду
только те строки, работает фильтр по Видим результат. относятся к категории сортировки. квадратов а в: так, что ли?
одна пустая строка.Сортировка и фильтр ввести: в себя названияМясо
подписью столбцов диапазонаПродажи нас важно, чтобы
Форматировать как таблицу
Сортировка в сводной таблице Excel
которые соответствуют указанному строкам в Excel,Верхняя таблица – результат овощи (картошка, лук,
Значения в сводном отчете SUBTOTAL нет такойIgor64zzz видимые после фильтраВ строках под названияминажмитеТип столбцов. Убедитесь вБелова
(в примерах нижеНапитки эта статья была
. _з0з_ и скрывают создадим табличку: фильтрации. Нижняя табличка морковь), остальное скрыть поменяются в соответствии функции, как обойти: Спасибо, Михаил!
ячейки. Игнорирует другие столбцов введите условия,Дополнительно
Продавец том, что между450 р. — «Среднее арифметическое»Ермолаева вам полезна. ПросимВ диалоговом окне строки, которые неДля списка товаров создадим с условиями дана через фильтр.
с отсортированными данными: эту засаду.Это то что подобные функции, которые которым должен соответствовать.
Продажи значениями условий ифрукты и «Точное соответствие»).5 122 р. вас уделить пару
Формулы в сводных таблицах Excel
Создание таблицы должны отображаться. После выпадающий список: для наглядности рядом.Буду благодарен ВамТеперь выполним сортировку данных_Boroda_ нужно. могут считать внутренние
- результат. Используя пример,Выполните одно из следующихПродажи исходным диапазоном имеетсяКротовЕсли в формуле вместоМясо
- секунд и сообщить,можно указать, есть фильтрации данных можноНад таблицей с исходными
- за помощь, автора по дате. Правая: Так нужно?lanalina подитоги в исходном введите:
по крайней мере6 328 р. относительной ссылки наБелова помогла ли она ли в таблице копировать, искать, изменять, данными вставим пустуюЧтобы отменить действие расширенного
решения готов немного кнопка мыши –Формула массива: Добрый день! Поднимаю диапазоне.
ТипЧтобы показать результат фильтрации,Щелкните ячейку в диапазоне
одна пустая строка.Фрукты
ячейку или имени450 р. вам, с помощью заголовки. форматировать, выносить и строку. В ячейки
Суммирование значений по фильтру (Макросы/Sub)
фильтра, поставим курсор финансово отблагодарить если «Сортировка». Можно выбрать200?’200px’:»+(this.scrollHeight+5)+’px’);»>=КОРЕНЬ(СУММКВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(D3;СТРОКА(D3:D33)-3;)))) тему с вашегоПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS)
Продавец скрыв ненужные строки, списка. Используя пример,Для поиска строк, отвечающихБелова диапазона используется названиефрукты кнопок внизу страницы.
Выберите выводить на печать введем формулу, которая в любом месте что. способ сортировки иили обычная формула позволения.
с кодом 109Продажи
установите переключатель
щелкните любую ячейку
нескольким условиям для6 544 р.
Фильтры сводной таблицы, категории товаров «Сумма по полю.»
столбца, в ячейкеКротов Для удобства такжеТаблица с заголовками подмножество отфильтрованных данных будет показывать, из таблицы и нажмемlavio_diaz на этом остановиться.Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=КОРЕНЬ(СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(D3;СТРОКА(D3:D33)-3;))^2))Добрый день. Прошу в первом аргументе=»=Мя*»фильтровать список на месте в диапазоне списка нескольких столбцов, когдаЛогическое выражение: с условием будет6 328 р.
приводим ссылку на, чтобы преобразовать верхнюю без переупорядочения или каких столбцов берется сочетание клавиш Ctrl
: Если сделать вычисляемые Но мы пойдемandreika21
помочь с решением – суммирует все=»=?г*». A6:C10.
истинным может быть (Тип = «Фрукты» И выведено значение ошибкиФрукты оригинал (на английском строку в заголовки перемещения. информация. ””;МАКС($A$1:A1)+1)’ > + Shift + поля? по другому пути.
: _Boroda_, урааааа работает! следующей задачи. Имеется видимые после фильтраЩелкните ячейку в диапазонеЧтобы скопировать отфильтрованные строкиНа вкладке
любое из условий, Продажи > 1 #ИМЯ? или #ЗНАЧ!Белова языке) . таблицы. Данные вКроме того, можно выполнитьРядом с выпадающим списком L или «Данные»lavio_diaz
Фильтрация данных в Excel с использованием расширенного фильтра
Нажмем «Дополнительные параметры Вы меня спасли, список точек кофеавтоматов и группировки (или списка. Используя пример, в другую областьДанные введите условия в
000) Эту ошибку можно6 544 р.Если требуется отфильтровать данные этой строке не фильтрацию по нескольким ячейку введем следующую — «Сортировка и: Стесняюсь спросить, а сортировки». Откроется окно спасибо большое. (колонка 1) скрытия) ячейки. Игнорирует щелкните любую ячейку листа, установите переключательв группе разные столбцы и
Как сделать расширенный фильтр в Excel?
Вставьте не менее трех не исправлять, посколькуОператоры сравнения используются для с помощью сложных будут фильтроваться.
- столбцам. Фильтры являются формулу:МАКС($B$1:$O$1);»»;ГПР(СТОЛБЕЦ(A:A);$B$1:$O$33;ПОИСКПОЗ($A$12;$A$4:$A$9;)+3))’ > Ее
- фильтр» — «Очистить». как вычисляется сумма
- вида:Сводная таблица – мощный
- по которым агенты
другие подобные функции, в диапазоне списка
- скопировать результат в другоеСортировка и фильтр строки диапазона условий.
- пустых строк над она не повлияет сравнения двух значений. условий (например, ТипНе устанавливайте флажок, если аддитивными, т. е. задача – выбиратьНайдем с помощью инструмента по полям овощиУстановим параметры сортировки: «Дата инструмент Microsoft Excel. (колонка 2) развозят которые могут считать
- A6:C10. местонажмите В данном примере исходным диапазоном, чтобы на результаты фильтрации. Результатом сравнения является = «Фрукты» ИЛИ вы хотите, чтобы каждый дополнительный фильтр из таблицы те
- «Расширенный фильтр» информацию и др.? по убыванию». Кликнем С ее помощью кофе и какао. внутренние подитоги вНа вкладке, перейдите в полеДополнительно следует ввести: использовать их дляВ формуле, которая применяется
- логическое значение: ИСТИНА либо Продавец = «Белова»), Excel Online добавил
задается на основе значения, которые соответствуют по значениям, которыеЗадача стоит как
по кнопке «Дополнительно».
Как пользоваться расширенным фильтром в Excel?
пользователь анализирует большие Они развозят товар исходном диапазоне.ДанныеПоместить результат в диапазон.Тип диапазона условий. Диапазон в качестве условия,
ЛОЖЬ. можно использовать диалоговое заполнители заголовков (которые текущего фильтра и
определенному товару содержат слово «Набор».
раз выбрать в Поставим галочку напротив по объему диапазоны, по точкам, аЕсли нужно не суммировать,
в группеи щелкните верхнююВыполните одно из следующихПродавец условий должен включать
для ссылки наОператор сравнения окно вы можете переименовать) далее сокращает подмножествоСкачать примеры расширенного фильтраВ таблицу условий внесем фильтре категорию «Овощи»
«Автоматической сортировки при
подводит итоги всего потом ежемесячно то можно использоватьСортировка и фильтр левую ячейку области, действий.Продажи в себя названия соответствующую ячейку в
ЗначениеРасширенный фильтр над данными. данных.Таким образом, с помощью
критерии. Например, такие: и в построении каждом обновлении отчета». в несколько кликов,
другие значения коданажмите в которую требуется
Чтобы показать результат фильтрации,=»=Фрукты»
столбцов. Убедитесь в первой строке необходимоПример.Нажмите кнопку
Как сделать несколько фильтров в Excel?
Примечание: инструмента «Выпадающий список»Программа в данном случае столбцов сумма поТеперь при появлении в
выводит на экран
котором указывают количество математической операции:Дополнительно вставить строки. скрыв ненужные строки,=»=Грачев»
том, что между использовать относительную ссылку.= (знак равенства)Чтобы открыть диалоговое окноОК При использовании диалогового окна и встроенных функций будет искать всю полю «картофель», сумма
сводной таблице новых только нужную в реализованных порций (колонкаАГРЕГАТ (AGGREGATE).Совет.
Как сделать фильтр в Excel по строкам?
установите переключательЩелкните ячейку в диапазоне значениями условий иВсе другие ссылки вРавноРасширенный фильтр
. « Excel отбирает данные
- информацию по товарам, по полю «лук» дат программа Excel данный момент информацию. 3). одновременно с– самая мощная
- Выполните одно из следующих При копировании отфильтрованных строкфильтровать список на месте списка. Используя пример, исходным диапазоном имеется формуле должны бытьA1=B1, выберите пунктыЧтобы применить фильтр, щелкнитеПоиск в строках по в названии которых
и др., т.е. будет сортировать ихВ сводную таблицу можно этим
функция, появившаяся в действий.
в другое место. щелкните любую ячейку по крайней мере абсолютными.> (знак больше)Данные
стрелку в заголовке» для поиска определенному критерию. есть слово «Набор». только те которые по убыванию (от преобразовать практически любой
снимаются показания счетчика
Office 2010. ТакжеЧтобы показать результат фильтрации, можно указать, какиеЧтобы скопировать отфильтрованные строки в диапазоне списка одна пустая строка.
Формула ПРОМЕЖУТОЧНЫЕ ИТОГИ — основные функции с примерами.
В предыдущей статье мы обсудили автоматический способ вставки промежуточных итогов в Excel с помощью инструмента Промежуточные итоги. Сегодня вы узнаете, как можно самостоятельно создавать формулы промежуточных итогов и какие преимущества это дает.
Синтаксис и использование.
Microsoft определяет ПРОМЕЖУТОЧНЫЕ.ИТОГИ как функцию, которая возвращает промежуточный итог в таблице данных. И это не просто суммирование чисел в определенном диапазоне ячеек.
В отличие от других функций Excel, которые предназначены только для одной конкретной задачи, она удивительно универсальна. Она может выполнять различные арифметические и логические операции, такие как подсчет количества значений, вычисление среднего, поиск минимального или максимального и многое другое.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (на английском — SUBTOTAL) доступна во всех версиях Excel 2019, 2016 и ниже.
Синтаксис ее следующий:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; ссылка1; [ссылка2];…)
| Номер функции |
Номер функции |
Функция | Описание |
| 1 | 101 | СРЗНАЧ | Возвращает среднее значение чисел. |
| 2 | 102 | СЧЁТ | Подсчитывает ячейки, содержащие числовые значения. |
| 3 | 103 | СЧЁТЗ | Считает непустые ячейки. |
| 4 | 104 | МАКС | Возвращает наибольшее значение. |
| 5 | 105 | МИН | Возвращает наименьшее значение. |
| 6 | 106 | ПРОИЗВЕД | Вычисляет произведение ячеек. |
| 7 | 107 | СТАНДОТКЛОН | Возвращает стандартное отклонение генеральной совокупности на основе выборки чисел. |
| 8 | 108 | СТАНДОТКЛОНП | Возвращает стандартное отклонение, основанное на генеральной совокупности чисел. |
| 9 | 109 | СУММ | Складывает числа. |
| 10 | 110 | ДИСП | Оценивает дисперсию генеральной совокупности на основе выборки чисел. |
| 11 | 111 | ДИСПР | Оценивает дисперсию генеральной совокупности на основе всей совокупности чисел. |
На самом деле нет необходимости запоминать все эти номера. Как только вы начнете вводить формулу промежуточного итога в ячейку или в строку формул, Microsoft Excel отобразит для вас список доступных функций с номерами. Останется только выбрать подходящую.
Например, вот как вы можете составить формулу с номером 9 для суммирования значений в ячейках с D2 по D10:
Чтобы добавить номер функции в формулу, дважды щелкните его в списке, затем поставьте точку с запятой, укажите диапазон, введите закрывающую скобку и нажмите Enter. Готовая формула будет выглядеть так:
Аналогичным образом вы можете написать формулу с 1 для получения среднего значения, с 2 для подсчета ячеек с числами, указать 3 для подсчета непустых значений и т. д. На скриншоте ниже показаны несколько других формул с различными функциями в действии:
Особенности формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ:
- Считает только по столбцам, для строк не подходит.
- Отфильтрованные значения не учитываются. Скрытые вручную значения учитываются функциями с номерами 1 – 11.
- Подсчитывает только исходные данные. Никакие другие промежуточные итоги не учитываются.
- Когда вы используете формулу промежуточных итогов с функцией суммирования, например СУММ или СРЗНАЧ, она вычисляет только ячейки с числами, игнорируя пустые и содержащие нечисловые значения.
- Применяется только к обычным диапазонам, с таблицами Excel не работает.
Существенное преимущество этой формулы в том, что ее можно использовать почти в любом месте вашего рабочего листа. То есть, таблица может не быть настолько жестко упорядоченной, как при использовании инструмента «Промежуточные итоги».
Теперь, когда вы знаете, как создать формулу промежуточных итогов в Excel, главный вопрос: зачем вообще тратить силы на ее изучение? Почему бы просто не использовать обычные функции, такие как СУММ, СЧЁТ, МАКС и т.д.? Вы найдете ответ ниже.
3 основных причины использовать ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel
По сравнению с традиционными функциями Excel, ПРОМЕЖУТОЧНЫЕ.ИТОГИ дает следующие важные преимущества.
1. Вычисляет значения с учетом применяемого фильтра.
Стандартная функция СУММ с подсчетом только нужных ячеек не справится. Придется либо копировать эти данные в другое место, чтобы все другие не мешали, либо применять функцию СУММЕСЛИ и постоянно корректировать ее в соответствии с нужными целями.
Примечание. Поскольку оба набора номеров функций (1-11 и 101-111) игнорируют отфильтрованные ячейки, в этом случае вы можете использовать формулу с функцией номер 9 или 109.
2. Вычисление только видимых ячеек.
Как вы помните, формулы промежуточных итогов с номером функции от 101 до 111 игнорируют все скрытые ячейки – отфильтрованные и скрытые вручную. Итак, когда вы используете функцию «Скрыть» в Excel для удаления из представления нерелевантных данных, используйте функцию номер 101-111, чтобы исключить значения в скрытых строках из подсчета итогов.
Номер функции Отфильтрованные значения Скрытые вручную 1-11 игнорируются учитываются 101-111 игнорируются игнорируются На рисунке ниже вы видите, что не следует использовать функцию 9, если вы руками скрывали какие-либо строки в вашей таблице. При этом функция 109 всегда возвращает результат исходя только из видимых на экране значений.
Следующий пример поможет вам лучше понять, как это работает: Промежуточный итог 9 против 109.
3. Игнорируются значения во вложенных формулах промежуточных итогов.
Если диапазон, указанный в вашей формуле промежуточных итогов Excel, содержит любые другие формулы промежуточных итогов, эти вложенные итоги будут проигнорированы, поэтому одни и те же числа не будут вычисляться дважды. Ведь нам не нужен двойной счёт. Классно, не правда ли?
На снимке экрана ниже формула
игнорирует результаты формул промежуточных итогов в ячейках G7 и G120, как если бы вы использовали функцию СУММ с двумя отдельными диапазонами: СУММ(G2:C6, G8:G11).
Использование промежуточных итогов в Excel — примеры формул
Когда вы впервые сталкиваетесь с промежуточными итогами, это может показаться сложным, запутанным и даже не слишком нужным. Но как только вы узнаете все подробности, то поймете, что овладеть ими не так уж и сложно, а пользы может быть много. Следующие примеры дадут вам несколько полезных советов и новых идей.
Пример 1. Промежуточный итог с функцией 9 или 109?
Как вы уже знаете, ПРОМЕЖУТОЧНЫЕ.ИТОГИ использует 2 набора номеров функций: 1-11 и 101-111. Все они игнорируют отфильтрованные строки, но номера 1–11 учитывают скрытые вручную строки, а номера 101–111 исключают их. Чтобы лучше понять разницу, давайте рассмотрим следующий пример.
Чтобы суммировать отфильтрованные строки, вы можете использовать формулу Промежуточный итог с функцией 9 или с 109, как показано на рисунке ниже:
Но если вы скрыли отдельные элементы вручную,
- используя команду «Скрыть строки» на вкладке «Главная»> «Ячейки»> «Формат» > «Скрыть и отобразить»,
- или щелкнув строки правой кнопкой мыши и выбрав «Скрыть»,
и теперь хотите суммировать значения только в видимых строках, использовать функцию 109 – это единственный вариант:
Остальные номера функций работают таким же образом. Например, для подсчета непустых отфильтрованных ячеек подойдет формула с функцией 3 или 103. Но только функция 103 может правильно подсчитывать видимые значения, если в диапазоне есть скрытые вручную строки.
Примечание. Функция Excel ПРОМЕЖУТОЧНЫЕ.ИТОГИ с номерами 101-111 игнорирует значения в скрытых строках, но не в скрытых столбцах. Например, если вы используете формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;D2:H10) для суммирования чисел в горизонтальном диапазоне (по строке), то скрытие столбца F не повлияет на результат.
Пример 2. Промежуточные итоги с условием.
Если вы создаете сводный отчет или информационную панель, где вам нужно отображать различные сводные данные, но у вас ограничено место, чтобы разместить множество вариантов итогов, то можно сделать так:
- В одной ячейке создайте раскрывающийся список, содержащий названия функций, такие как СУММ, МАКС, МИН и др.
- В ячейке рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточных итогов, соответствующими именам функций в раскрывающемся списке.
Например, если предположить, что значения для промежуточного итога находятся в ячейках C2: C16, а раскрывающийся список в A17 содержит элементы «Итого», «Среднее», «Максимум» и «Минимум», «динамическая» формула промежуточного итога будет выглядеть следующим образом:
=ЕСЛИ(A17=»Сумма»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C16); ЕСЛИ(A17=»Среднее»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;C2:C16);ЕСЛИ(A17=»Минимум»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(5;C2:C16); ЕСЛИ(A17=»Максимум»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4;C2:C16);»»))))
И теперь, в зависимости от того, какую функцию ваш пользователь выбирает из раскрывающегося списка, соответствующая функция будет вычислять значения в отфильтрованных строках:
Примечание. Если вдруг раскрывающийся список и ячейка формулы исчезнут с вашего рабочего листа, обязательно снова выберите их в списке фильтра – значит, вы их случайно отфильтровали.
Думаю, при помощи этого способа вы можете заменить этой универсальной функцией стандартные функции суммирования, нахождения максимума и минимума, среднего значения, подсчёта и т.д. Ведь в данном случае даже сортировка не нужна, так как мы не группируем данные, а просто делаем подсчёт. Главное, чтобы не было пустых строк.
Обратите внимание также на то, что если бы мы использовали для расчёта суммы продаж функцию СУММ, то в этой таблице мы не смогли бы подсчитать продажи по какому-то определённому покупателю или группе покупателей. Пришлось бы для каждого из них использовать функцию СУММЕСЛИ.
Вы можете достаточно просто посчитать промежуточные итоги с условием, если отфильтровать нужные строки по товарам, по покупателям либо по другим критериям. Результат будет показан только по значениям, оставшимся после фильтрации. Соответственно и расчет будет сделан только по ним.
К примеру, вот как можно подсчитать сумму продаж черного шоколада, исключив молочный при помощи фильтра:
Как видите, при помощи СУММ это сделать невозможно. А использовать СУММЕСЛИ — это каждый раз корректировать условие в формуле либо делать несколько формул «на все случаи жизни». А здесь мы просто отбираем нужное при помощи фильтра. Согласитесь, это гораздо проще и удобнее, нежели писать и затем корректировать громоздкие формулы.
Почему промежуточные итоги не работают? Распространенные ошибки.
Если формула промежуточного итога возвращает ошибку, вероятно, это связано с одной из следующих причин:
#ЗНАЧЕН!— аргумент номер_функции не является целым числом от 1 до 11 или от 101 до 111; или любой из аргументов ref содержит трехмерную ссылку сразу на несколько листов вашей рабочей книги.
#ДЕЛ/0! — возникает, если указанная функция должна выполнить деление на ноль (например, вычисление среднего значения или стандартного отклонения для диапазона ячеек, не содержащего ни одного числа).
#ИМЯ? — неправильно написано название функции — исправить ошибку проще простого:)
Совет. Если вы еще не чувствуете себя комфортно с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, вы можете использовать встроенный инструмент ПРОМЕЖУТОЧНЫЙ ИТОГ и автоматически вставлять формулы.
Функция СУММПРОИЗВ с примерами формул — В статье объясняются основные и расширенные способы использования функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по нескольким условиям, расчета средневзвешенного значения…
Сумма по цвету и подсчёт по цвету в Excel — В этой статье вы узнаете, как посчитать ячейки по цвету и получить сумму по цвету ячеек в Excel. Эти решения работают как для окрашенных вручную, так и с условным форматированием. Если…
Промежуточные итоги в Excel — В руководстве объясняется, как использовать инструмент промежуточных итогов Excel для автоматического суммирования, подсчета или усреднения различных групп ячеек. Вы также узнаете, как отображать или скрывать детали промежуточных итогов, копировать только строки…
Формула суммы в Excel — несколько полезных советов и примеров — Как вычислить сумму в таблице Excel быстро и просто? Попробуйте различные способы: взгляните на сумму выбранных ячеек в строке состояния, используйте автосумму для сложения всех или только нескольких отдельных ячеек,…
7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями — В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Хорошо знакомая нам функция ЕСЛИ позволяет производить вычисления в зависимости…
8 простых способов как посчитать в Excel сумму столбца — Как посчитать сумму в Excel быстро и просто? Чаще всего нас интересует итог по столбцу либо строке. Попробуйте различные способы найти сумму по столбцу, используйте функцию СУММ или же преобразуйте…
Как сохранить фильтр в Excel используя представления данных
Допустим каждый месяц нам нужно создать отчет о результатах в продажах нашего отдела. Для подготовки отчета используем статистические показатели по всех транзакциях в журнале истории взаиморасчетов с клиентами, выполненных за прошедший период. Чтобы экспонировать данные по конкретному клиенту, например, для сравнения, необходимо каждый раз фильтровать таблицу истории взаиморасчетов под разными критериями. Нет необходимости тратить время и усилия на многоразовое фильтрование тех самых данных. Можно существенно облегчить решение данной задачи сгруппировав данные и сохранив их как нестандартный вид представления. Благодаря этому одним кликом мышки можно детально экспонировать необходимые данные на данный момент.
Как быстро пользоваться фильтром в Excel
Допустим наш журнал истории взаиморасчетов с клиентами отображается также как на рисунке.
История продаж и взаиморасчетов по клиентам:
Нам необходимо отобразить разные варианты данных с разделением на группы: название фирм, городов и сумм. К последней группе еще хотим добавить итоговый показатель.
Начнем работу с фильтрования данных, а потом разберем как их сохранять в разных режимах фильтра. Каждая группа будет записана как пользовательский вид представления данных на рабочем листе Excel.
Допустим первая группа данных должна включать в себя данные касающиеся транзакций сумой выше 20 000 рублей реализованной фирмой ADEX Comp. Для этого:
- Выделите заголовок таблицы «Сумма» D1 и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр». Для исходной таблицы включиться режим автофильтра и в ее заголовках появятся кнопки выпадающего меню.
- Откройте выпадающее меню ячейки D1 и выберите опцию «Числовые фильтры»-«Настраиваемый фильтр». В результате появиться диалоговое окно «Пользовательский автофильтр».
- В первом выпадающем списке данного окна укажите на значение «больше», а в поле ввода напротив введите значение 20000. И нажмите ОК.
- Теперь раскройте выпадающее меню в ячейке A1 («Название фирмы») и выберите только фирму ADEX Comp.
Применено второе условие фильтрования данных.
Как посчитать сумму по фильтру в Excel
Отфильтрованная группа готова, осталось только добавить итоговое значение для столбца «Сумма». Для этого выберите первую пустую ячейку под этим столбцом и выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сумма» или нажмите комбинацию клавиш CTR+=. После нажатия на клавишу Enter суммируются только видимые значения в столбце. По завершению присвойте для этой ячейки денежный формат.
Обратите внимание! Вместо стандартной функции СУММ мы используем функцию: Из ходя из названия сложно догадаться что данная функция будет суммировать значения. Но если в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ находиться константа с кодом номер 9 – это значит, что мы подключаем встроенную функцию суммирования. Таким образом мы выполняем суммирование только промежуточных итогов, а не все значения в столбце. Стоит рассмотреть все встроенные функции в ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которые можно выполнять с отфильтрованными значениями в таблице:
- – среднее значение, альтернатива функции СРЕДЗНАЧ.
- – подсчет количества значений, альтернатива для СЧЁТ.
- – подсчет только непустых ячеек в диапазоне, как СЧЁТЗ.
- – возвращает максимальное значение (МАКС).
- – возвращает минимальное значение (МИН).
- – возвращает произведение аргументов (ПРОИЗВЕД).
- – функция стандартного отклонения как СТАНДОТКЛОН.
- – стандартное отклонение по генеральной совокупности (логические и текстовые значения – игнорируются) как в СТАНДОТКЛОНП.
- – суммирует значение как СУММ.
- – дисперсия по выборке как ДИСП.
- – дисперсия для генеральной совокупности ДИСПР.
Примечание. Номера констант могут быть с включением и с исключением значений в скрытых строках инструментом: «ГЛАВНАЯ»-«Ячейки»-«Формат»-«Скрыть или отобразить». Отличие заключаться в коде константы:
- 1-11 – с включением;
- 101-111 – с исключением.
Если мы хотим получить суммирование итогов для другой фирмы, достаточно только изменить критерий фильтра, а итоговый результат автоматически просчитается благодаря функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Сохранение фильтра в представление данных
Когда первый набор значений для группы готовый, можно его сохранить как один из видов таблицы журнала истории взаиморасчетов по клиентам. Благодаря этому мы получим молниеносный доступ к организации новой структуры данных в таблице. Для этого:
- Отфильтруйте таблицу по выше описанным критериям и выберите инструмент: «ВИД»-«Режимы просмотра книги»-«Представления».
- В появившемся диалоговом окне «Представления» нажмите на кнопку «Добавить». Появиться новое окно «Добавление представления».
- В поле «Имя:» введите название «ADEX Comp >20 тыс.руб.» для этого представления данных на рабочем листе и нажмите ОК.
- Создайте новое представление для нового отображения таблицы с другими критериями фильтрования и придумайте им новые названия.
Теперь достаточно выбрать любой из списка видов представления данных на листе, используя выше указанный инструмент: «ВИД»-«Режимы просмотра книги»-«Представления». При выборе любого из преставлений данные на листе будут автоматически преобразовываться и группироваться в ранее сохраненный их вид. Теперь нет необходимости каждый раз заново создавать сложные фильтры по множеству критериев для их определения. Достаточно переключиться между видами представлений. Указать на представление в списке и нажать на кнопку «Применить».
Чтобы удалить представление снова вызовите диалоговое окно: «ВИД»-«Режимы просмотра книги»-«Представления». Потом выделите в списке ненужный вид и нажмите на кнопку «Удалить».