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

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

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

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

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

  1. Отсутствие пустых ячеек, т.е. все строки и столбцы должны быть заполнены данными.
  2. В шапке таблицы нельзя использовать несколько строк. Она должна быть представлена лишь одной строкой. А также имеет значение ее расположение. Она должна находиться только на самой верхней строке и нигде больше.
  3. Формат таблицы обязательно должен быть представлен в виде обычной области ячеек.

Применение функции промежуточных итогов

Итак, теперь, когда мы определились с основными критериями “годности” таблиц, приступим к подсчету промежуточных итогов.

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

  1. Отмечаем любую ячейку таблицы, переключаемся во вкладку “Данные”, находим раздел “Структура”, щелкаем по нему и в раскрывшемся перечне нажимаем по варианту “Промежуточный итог”.
  2. В итоге появится окно, где мы осуществим дальнейшие настройки согласно нашей задаче.
  3. Итак, нам требуется произвести расчет ежедневных продаж всех наименований продукции. Информация о дате продажи размещается в одноименном столбце. Исходя из этого, заполняем требуемые поля настроек.
    • раскрываем список для строки “При каждом изменении в” и останавливаем выбор на “Дате”.
    • мы хотим посчитать общую сумму ежедневных продаж, поэтому для параметра “Операция” выбираем функцию “Сумма”.
    • если бы пред нами стояла другая задача, то можно было бы выбрать другую функцию из четырех предложенных программой: произведение (умножение), минимум, максимум, количество.
    • далее требуется указать место вывода полученных данных. У нас в таблице имеется столбец под названием “Продано, в руб.” Его и укажем для параметра «Добавить итоги по».
    • также следует обратить внимание на пункт “Заменить текущие итоги”. Если напротив него нет установленной галочки, нужно ее поставить. В противном случае возникнут проблемы при внесении каких-либо изменений и повторном пересчете итогов.
    • перейдем к надписи “Конец страницы между группами” и разберемся, стоит ли ставить напротив нее галочку. Если этот параметр будет отмечен галочкой, это повлияет на внешний вид документа при отправке на принтер. Все блоки таблицы с подведенными промежуточными итогами распечатаются на отдельных листах каждый.
    • и, наконец, параметр “Итоги под данными” определяет расположение результата относительно строк. Если убрать отметку напротив этого пункта, то результат будет выводиться над строками. Приемлемы оба варианта, но всё-таки привычнее и визуально понятнее расположение итогов под данными.
    • закончив с настройками, подтверждаем действие нажатием на OK.
  4. В результате проделанных действий в таблице будут отображены промежуточные итоги по группам (по датам). Напротив каждой группы можно увидеть значок минуса, при нажатии на который строки внутри нее сворачиваются.
  5. При желании можно убрать лишние данные из поля видимости, оставив только общий итог и промежуточные суммы. Нажатием кнопки “плюс” можно обратно развернуть строки внутри групп.

Примечание: После внесении каких-либо изменений и добавлении новых данных промежуточные итоги будут пересчитаны в автоматическом режиме.

Написание формулы промежуточных итогов вручную

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

  1. Для начала отмечаем ячейку, где должен быть выведен итог подсчета. Далее нажимаем на значок «Вставить функцию» (fx) рядом со строкой формул с левой стороны от нее.
  2. Откроется Мастер функций. Выбираем категорию “Полный алфавитный перечень”, находим из предложенного перечня функцию “ПРОМЕЖУТОЧНЫЕ.ИТОГИ”, ставим на нее курсор и нажимаем OK.
  3. Теперь нужно задать настройки функции. В поле «Номер_функции» указываем цифру, которой соответствует нужному варианту обработки информации. Всего опций одиннадцать:
    • цифра 1 – расчет среднего арифметического значения
    • цифра 2 – подсчет количества ячеек
    • цифра 3 – подсчет количества заполненных ячеек
    • цифра 4 – определение максимального значения в выбранном массиве данных
    • цифра 5 – определение минимального значения в выбранном массиве данных
    • цифра 6 – перемножение данных в ячейках
    • цифра 7 – выявление стандартного отклонения по выборке
    • цифра 8 – выявление стандартного отклонения по генеральной совокупности
    • цифра 9 – расчет суммы (ставим в нашем варианте согласно задаче)
    • цифра 10 – нахождение дисперсии по выборке
    • цифра 11 – нахождение дисперсии по генеральной совокупности
  4. В поле «Ссылка 1» указываем координаты диапазона, для которого требуется просчитать итоги. Всего можно указать до 255 диапазонов. После введения координат первой ссылки, появится строка для добавления следующей. Прописывать координаты вручную не совсем удобно, к тому же, велика вероятность ошибиться. Поэтому просто ставим курсор в поле для ввода информации и затем левой кнопкой мыши отмечаем нужную область данных. Аналогичным образом можно добавить следующие ссылки, если потребуется. По завершении подтверждаем настройки нажатием кнопки OK.
  5. В итоге в ячейке с формулой будет выведен результат подсчета промежуточных итогов.

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

Читать еще:  Сравнение таблиц excel

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер обработки данных;координаты ячеек)

Далее жмем клавишу Enter и получаем желаемый результат в заданной ячейке.

Заключение

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

Сводные таблицы Excel

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

Сокрытие промежуточных итогов при наличии множества полей строк

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

Рис. 3.26. Иногда не нужно выводить промежуточные суммы на каждом уровне таблицы

Чтобы удалить промежуточные суммы в поле Рынок сбыта, щелкните на названии этого поля в соответствующей области диалогового окна Список полей сводной таблицы (PivotTable Field List) и выберите команду Параметры поля (Field Settings). В диалоговом окне Параметры поля в разделе Итоги (Subtotals) установите переключатель нет (None), как показано на рис. 3.27.

Рис. 3.27. Установите переключатель нет, чтобы исключить вывод промежуточных итогов для поля Рынок сбыта

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

Посмотреть на Яндекс.ФоткахРис. 3.28. Отказ от вывода промежуточных итогов упрощает дальнейший анализ данных отчета

Чтобы отказаться от вывода промежуточных итогов для полей, включенных в область строк, перейдите на контекстную вкладку Конструктор (Design) и в группе Макет (Layout) щелкните на кнопке Промежуточные итоги (Subtotals). В отобразившемся списке выберите параметр Не показывать промежуточные суммы (Do Not Show Subtotals).

Настройка вычислений в сводных таблицах

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

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

Другие функции расчета вместо банальной суммы

Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings) , то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

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

По умолчанию, для числовых данных Excel всегда автоматически выбирает суммирование (Sum) , а для нечисловых (даже если из тысячи ячеек с числами попадется хотя бы одна пустая или с текстом или с числом в текстовом формате) – функцию подсчета количества значений (Count) .

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

…а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля (Field settings) , чтобы в итоге получить желаемое:

Долевые проценты

Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as) :


В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row) , Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total) , чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:

Динамика продаж

Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference) , а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):

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

А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом — то получим то же самое, но не в рублях, а в процентах:

В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще — щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By) :

Читать еще:  Таблицы для чайников эксель

. и Дополнительные вычисления (Show Data as) :

Также в версии Excel 2010 к этому набору добавились несколько новых функций:

    % от суммы по родительской строке (столбцу) — позволяет посчитать долю относительно промежуточного итога по строке или столбцу:

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

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

  • Сортировка от минимального к максимальному и наоборот — немного странное название для функции ранжирования (РАНГ), вычисляющей порядковый номер (позицию) элемента в общем списке значений. Например, с ее помощью удобно ранжировать менеджеров по их суммарной выручке, определяя кто на каком месте в общем зачете:
  • Поля промежуточных и общих итогов в сводной таблице

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

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

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

    На вкладке анализ в группе активное поле нажмите кнопку Параметры поля.

    Откроется диалоговое окно » Параметры поля «.

    В диалоговом окне Параметры поля в разделе Итоги выполните одно из указанных ниже действий.

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

    Для удаления промежуточных итогов выберите пункт Нет.

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

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

    Функции, доступные для вычисления промежуточных итогов

    Сумма значений. Функция по умолчанию для числовых данных.

    Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.

    Количество числовых значений. Действует аналогично функции СЧЁТ.

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

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

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

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

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

    Чтобы учитывать или игнорировать новые элементы при использовании фильтра, установите или снимите флажок Включить новые элементы в фильтр.

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

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

    На вкладке Конструктор в группе Макет нажмите кнопку Промежуточные итоги.

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

    Выберите пункт Не показывать промежуточные суммы.

    Выберите Показывать все промежуточные итоги в нижней части группы.

    Выберите Показывать все промежуточные итоги в заголовке группы.

    Отображение и скрытие общих итогов по всему отчету

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

    Отображение или скрытие общих итогов

    Щелкните в любом месте сводной таблицы.

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

    Изменение состояния общих итогов по умолчанию (отображение или скрытие)

    Щелкните сводную таблицу.

    На вкладке анализ в группе Сводная Таблица нажмите кнопку Параметры.

    В диалоговом окне Параметры сводной таблицы на вкладке Итоги и фильтры выполните одно из указанных ниже действий.

    Чтобы отобразить общие итоги, выберите либо Показать общие итоги для столбцов , либо Показать общие итоги для строкили и то, и другое.

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

    Вычисление промежуточных и общих итогов с отфильтрованными элементами и без них

    Щелкните в любом месте сводной таблицы.

    На вкладке анализ в группе Сводная Таблица нажмите кнопку Параметры.

    В диалоговом окне Параметры сводной таблицы на вкладке Итоги и фильтры выполните одно из указанных ниже действий.

    Если вы используете источник данных OLAP, выполните одно из указанных ниже действий.

    Установите или снимите флажок Промежуточные суммы по отобранным фильтром элементам страницы, чтобы включить отфильтрованные элементы в итоги или убрать из них.

    Примечание: Источник данных OLAP должен поддерживать синтаксис подзапросов выборки многомерного выражения.

    Установите или снимите флажок Помечать итоги *, чтобы отобразить или скрыть звездочку рядом с итогами. Звездочка означает, что при вычислении итогов в Excel использовались не только отображаемые значения.

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

    Читать еще:  Как закрепить заголовок таблицы в excel при печати

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

    Посмотреть видео

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

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

    На вкладке Параметры в группе Активное поле нажмите кнопку Параметры поля.

    Откроется диалоговое окно » Параметры поля «.

    В диалоговом окне Параметры поля в разделе Итоги выполните одно из указанных ниже действий.

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

    Для удаления промежуточных итогов выберите пункт Нет.

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

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

    Функции, доступные для вычисления промежуточных итогов

    Сумма значений. Функция по умолчанию для числовых данных.

    Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.

    Количество числовых значений. Действует аналогично функции СЧЁТ.

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

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

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

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

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

    Чтобы учитывать или игнорировать новые элементы при использовании фильтра, установите или снимите флажок Включить новые элементы в фильтр.

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

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

    На вкладке Конструктор в группе Макет нажмите кнопку Промежуточные итоги.

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

    Выберите пункт Не показывать промежуточные суммы.

    Выберите Показывать все промежуточные итоги в нижней части группы.

    Выберите Показывать все промежуточные итоги в заголовке группы.

    Отображение и скрытие общих итогов по всему отчету

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

    Отображение или скрытие общих итогов

    Щелкните в любом месте сводной таблицы.

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

    Изменение состояния общих итогов по умолчанию (отображение или скрытие)

    Щелкните сводную таблицу.

    На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

    В диалоговом окне Параметры сводной таблицы на вкладке Итоги и фильтры выполните одно из указанных ниже действий.

    Чтобы отобразить общие итоги, выберите либо Показать общие итоги для столбцов , либо Показать общие итоги для строкили и то, и другое.

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

    Вычисление промежуточных и общих итогов с отфильтрованными элементами и без них

    Щелкните в любом месте сводной таблицы.

    На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

    В диалоговом окне Параметры сводной таблицы на вкладке Итоги и фильтры выполните одно из указанных ниже действий.

    Если вы используете источник данных OLAP, выполните одно из указанных ниже действий.

    Установите или снимите флажок Промежуточные суммы по отобранным фильтром элементам страницы, чтобы включить отфильтрованные элементы в итоги или убрать из них.

    Примечание: Источник данных OLAP должен поддерживать синтаксис подзапросов выборки многомерного выражения.

    Установите или снимите флажок Помечать итоги *, чтобы отобразить или скрыть звездочку рядом с итогами. Звездочка означает, что при вычислении итогов в Excel использовались не только отображаемые значения.

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

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

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

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

    Похожие статьи

    Ссылка на основную публикацию
    Похожие публикации
    Adblock
    detector