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

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

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

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

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

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

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

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

В новеньком открывшемся окне необходимо избрать функцию суммы.

В итоге, можно будет узреть суммированные итоги каждой группы продуктов. Также покажутся новейшие способности для данной таблицы. В левой части программки высветятся иконки «-» в развернутом виде и «+» в свернутом. Зависимо от действий будут видны те либо остальные результаты работы функции расчета промежных итогов.

Также можно свернуть информацию на сто процентов и показать только общий показатель.

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

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

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

В окне аргументов следует вписать номер_функции. Для примера используем 9-ю операцию – суммирование. Также есть и остальные номер функций:

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

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

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

Расчет через сводную таблицу редактора

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

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

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

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

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

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

Как удалить промежуточные итоги

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

В открывшемся окне просто надавить на кнопку «убрать все». Опосля этого таблица воспримет собственный начальный вид.

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

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

• Повторное внедрение чего же угодно: Добавляйте более нередко применяемые либо сложные формулы, диаграммы и все другое в избранное и стремительно используйте их в будущем.
• Наиболее 20 текстовых функций: Извлечь число из текстовой строчки; Извлечь либо удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты слияния : Несколько книжек и листов в одну; Соединить несколько ячеек / строк / столбцов без утраты данных; Соедините повторяющиеся строчки и сумму.
• Поделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книжки в несколько файлов Excel, PDF либо CSV; От 1-го столбца к нескольким столбцам.
• Вставить пропуск Сокрытые / отфильтрованные строчки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электрические письма нескольким получателям массово.
• Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к хоть каким листам; Сортировать по неделькам, денькам, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
• Наиболее 300 массивных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Обычное развертывание на вашем предприятии либо в организации.

Показать несколько общих итогов в сводной таблице

Умопомрачительный! Внедрение действенных вкладок в Excel, таковых как Chrome, Firefox и Safari!

Сберегайте 50% собственного времени и сокращайте тыщи щелчков мышью любой денек!

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

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

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

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

4. Опосля нажатия пробела расположите курсор в другое пространство, а потом вернитесь, чтоб опять избрать эту новейшую ячейку метки поля, щелкните ее правой клавишей мыши и изберите Итого «Общая сумма», а потом будут отображены общие итоги для промежных итогов, см. снимки экрана:

5. Потом вы должны показать промежуточные итоги в нижней части сводной таблицы, щелкните всякую ячейку в сводной таблице и потом перейдите к Дизайн вкладку и щелкните подытоги > Показать все промежуточные итоги понизу группы, смотрите снимки экрана:

6. Опосля отображения промежных итогов понизу сводной таблицы вы сможете избрать несколько функций для собственных промежных итогов. Щелкните правой клавишей мыши Всего обозначить ячейку и избрать Опции полей из контекстного меню см. скриншот:

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

8. Потом нажмите OK чтоб закрыть это диалоговое окно, и несколько функций были отображены понизу в сводной таблице, как и общие итоги, см. скриншот:

9. В конце концов, для вас необходимо скрыть либо удалить начальную общую сумму. Щелкните правой клавишей мыши Общий результат подпишите ячейку понизу сводной таблицы, изберите Удалить общую сумму из меню, см. скриншот:

Промежный результат в Excel

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

В статье будет рассматриваться таблица реализации продуктов в месяц, потому что почаще всего конкретно эту функцию употребляют для представленной операции. Смотреться она будет в виде 3-х столбцов: наименование продукта, дата и сумма выручки. Используя в «Эксель» промежуточные итоги, есть возможность высчитать каждодневный заработок той либо другой продукции. Кроме этого, в конце можно подвести сумму реализации продуктов. На данный момент мы тщательно разглядим, как делаются промежуточные итоги в «Эксель».

Условия использования функции

Стоит сходу уточнить, что для использования представленной операции таблица обязана соответствовать требованиям. Всего стоит соблюдать три пт:

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

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

Создание промежного итога. Внедрение специального инструмента

Как сделать промежный результат в «Эксель»? 1-ый метод будет продемонстрирован при использовании обычного одноименного инструмента в программке. Итак, приступим.

Шаг 1: Открытие инструмента

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

Шаг 2: Настройка отображения

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

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

• минимум;
• количество;
• максимум;
• произведение.

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

Также в этом окне еще есть несколько переменных: «Поменять текущие итоги», «Конец странички меж группами» и «Итоги под данными». Из принципиальных можно отметить только 1-ый пункт, а другие устанавливайте по желанию.

Шаг 3: Конец

Опосля проведения всех опций для вас остается только надавить на клавишу «ОК». Сейчас в программке вы сможете следить промежуточные итоги по датам. Необходимо отметить, что при помощи знака «минус» в левой части окна вы сможете сворачивать и разворачивать группы. В самом низу будет подведен общий результат.

Создание промежного итога. Внедрение специальной формулы

Кроме выше представленного метода, как можно еще высчитать в «Эксель» промежуточные итоги? Формула — вот что нам поможет.

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

Шаг 1: Открытие мастера функций

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

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

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

Шаг 2: Ввод аргументов функции

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

В нашем случае нужно вычислить сумму, потому необходимо вписать число «9».

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

Опять покажется знакомое нам окно, в котором можно указать вторую ссылку (всего их четыре). Если созодать этого для вас не нужно, то жмите на клавишу «ОК» и результаты выведутся в таблице.

Как подвести промежуточные итоги в Гугл Таблицах (обычное управление)

SUBTOTAL — это функция в Гугл Таблицах, которая быть может неописуемо всепригодной. С правильными советами и инструментами она быть может оживленной и сильной, позволяя экспоненциально наращивать функциональность ваших листов.

При использовании функции SUBTOTAL в Гугл Таблицах вы отыщите разные методы ее использования и несколько приложений с отчетами.

Три главных метода её использования:

• Применение разных главных функций (к примеру, Sum, Count, Average) к списку данных.
• Создание селектора отчетов для просмотра различной статистики из 1-го набора данных.
• Расчет данных со сокрытыми строчками либо без их.

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

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

Гугл Таблицы — Функция SUBTOTAL

Синтаксис

Мы начнем с синтаксиса SUBTOTAL в Гугл Таблицах.

Функция SUBTOTAL просит 2-ух либо наиболее аргументов.

• Код функции.
• По последней мере, один спектр для выполнения данной функции.

Коды функций изображены ниже и доступны в Гугл Таблицах в хоть какое время. Для этого для вас поначалу необходимо начать вводить функцию = SUBTOTAL.

Потом в разделе справки по формулам в левом нижнем углу вы увидите клавишу «Подробнее». Изберите это.

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

Код функции

Опосля того, как вы нажали клавишу «Выяснить больше», покажется правая боковая панель; в ней вы увидите подробное разъяснение всякого кода функции. Это может понадобиться, если вы забудете.

Коды функций последующие:

1 = AVERAGE
2 = COUNT
3 = COUNTA
4 = MAX
5 = MIN
6 = PRODUCT
7 = STDEV
8 = STDEVP
9 = SUM
10 = VAR
11 = VARP

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

101 = AVERAGE
102 = COUNT
103 = COUNTA
104 = MAX
105 = MIN
106 = PRODUCT
107 = STDEV
108 = STDEVP
109 = SUM
110 = VAR
111 = VARP

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

Как ПОЛУЧИТЬ ПРОМЕЖУТОЧНЫЙ ИТОГ в Гугл Таблицах

Кто-то может спросить, для чего применять функцию SUBTOTAL для выполнения функции SUM? Не было бы проще применять функцию SUM? Эти вопросцы полностью понятны и логичны. Но бывают ситуации, когда функция SUBTOTAL быть может наиболее ценной и действенной.

Представим, у вас есть последующий набор данных, и вы решили применять функцию SUM заместо функции SUBTOTAL :

На приведенной ниже диаграмме вы увидите, что функция SUM находится в каждой итоговой ячейке, а общая итоговая ячейка — в нижней части таблицы.

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

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

Гугл Таблицы ПРОМЕЖУТОЧНЫЙ ИТОГ для отфильтрованных либо укрытых данных

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

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

Вы увидите подобающую таблицу понизу листа.

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

Для этого избираем раскрывающийся перечень зеленоватого фильтра в высшей части столбца «Месяц». Изберите месяц, о котором речь идет, и нажмите ОК.

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

Вы также заметите, что ячейки SUM в таблице ниже не поменялись.

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

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

Как применять функцию SUBTOTAL для сотворения селектора функций динамического отчета

Самый всепригодный способ использования функции SUBTOTAL в Гугл Таблицах — это создание селектора функции отчета. Дозволяет стремительно изменять характеристики, отображаемые в отчете, и быть может выполнено методом объединения разных функций и операций с функцией SUBTOTAL . Это относительно просто, и шаги описаны ниже.

• Первым шагом в этом является создание таблицы функций для доступных функций SUBTOTAL .
  • Мы будем применять этот перечень для проверки данных (раскрывающийся перечень) и функции VLOOKUP на последующих шагах.
  • Потом вы создадите проверку данных, которая ссылается на этот перечень в ячейке, в которой вы желаете создать собственный селектор раскрывающимся.
    • Для этого щелкните правой клавишей мыши подходящую ячейку, найдите Проверка данных и щелкните ее.
    • Сейчас вы увидите окно проверки данных. Изберите перечень из спектра и введите спектр столбца агрегации из перечня, который мы сделали на шаге 1.
    • Сейчас, когда вы сделали раскрывающийся перечень проверки данных, вы должны узреть стрелку в ячейке, которая дозволяет два раза щелкнуть и избрать подходящий вариант.

    

    • Другие шаги соединены с написанием функций для получения подходящих нам результатов.
      • Мы будем вкладывать функцию VLOOKUP в нашу функцию SUBTOTAL . Вложенность — это одновременное внедрение нескольких функций для получения хотимого результата. Для этого вы напишите в ячейке последующее.
      • VLOOKUP просматривает таблицу, сделанную нами на шаге 1, в спектре AH4: AI14. Она отыскивает содержимое в раскрывающейся ячейке, которую мы сделали в E54. Обнаружив правильную строчку, она отыскивает итог во 2-м столбце спектра. Потом она вставляет это число в функцию VLOOKUP для кода функции.
      • Сейчас сделайте ячейку для функции «Игнорировать сокрытые строчки» и введите ниже. Это то же самое, что и выше, кроме того, что мы добавили «100+» в раздел Vlookup, что принудит его применять код функции игнорирования укрытых ячеек.
      • Ваш селектор сейчас работает и готов к использованию. Но, если вы желаете создать его наиболее продвинутым, вы сможете сделать флаг для управления функцией игнорирования укрытых ячеек.
      • Потом в ячейке результатов для вас необходимо будет ввести обозначенную ниже функцию. Вы используете те же функции, что и выше, но управляете оператором IF, который инспектирует флаг и потом решает, какие результаты показывать. Дозволяет юзеру переключаться меж отображением либо игнорированием этих ячеек.

      Заключение

      Таблицы Гугл имеют огромное количество нужных функций. Одна из их — функция SUBTOTAL . Она имеет весьма оживленный нрав и может принести пользу хоть какой системе отчетности в Гугл Таблицах.