Как убрать итоги в сводной таблице excel
Данные в поле значений обобщают данные источников (а не значения, которые отображается) в отчете сводной таблицы следующим образом: для числовых значений используется функция СУММ, а для текстовых — функция СЧЁТ. Однако функция сведения данных может быть изменена. Можно также создать настраиваемое вычисление.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Анализ в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Доступные функции сведения данных
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Количество числовых значений. Действует аналогично функции СЧЁТ.
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые значения.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые значения.
Число разных элементов
Число уникальных значений. Эта функция сведения работает только при использовании модели данных в Excel.
Примечание: Для некоторых типов исходных данных, таких как данные OLAP, вычисляемых полей и полей с вычисляемыми элементами функцию сведения изменить нельзя.
При необходимости можно использовать настраиваемое вычисление следующим способом.
Откройте вкладку Дополнительные вычисления.
В поле Дополнительные вычисления выберите нужный тип вычисления.
Выключение настраиваемого вычисления.
Отображение значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Отображение доли в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Вычисление (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Вычисление (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Вычисление (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отображение значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Отображение значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Отображение значения в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Отображение значения в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Вычисление значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Выберите базовое поле и базовый элемент, если они доступны для нужных вычислений.
Примечание: Базовое поле не должно совпадать с полем, выбранным на шаге 1.
Для изменения форматирования чисел нажмите кнопку Числовой формат, а затем на вкладке Число диалогового окна Формат ячеек выберите числовой формат и нажмите кнопку OK.
Если в отчете содержится несколько полей значений, повторите предыдущие шаги для каждого такого поля, которое нужно изменить.
Примечание: Чтобы использовать для одного поля несколько функций сведения, снова добавьте это поле из списка полей сводной таблицы и повторите шаги, выбрав другую функцию.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Количество числовых значений. Действует аналогично функции СЧЁТ.
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые значения.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые значения.
Примечание: Для некоторых типов исходных данных, таких как данные OLAP, вычисляемых полей и полей с вычисляемыми элементами функцию сведения изменить нельзя.
При необходимости можно использовать настраиваемое вычисление следующим способом.
Откройте вкладку Дополнительные вычисления.
В поле Дополнительные вычисления выберите нужный тип вычисления.
Выключение настраиваемого вычисления.
Отображение значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Отображение доли в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Вычисление (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Вычисление (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Вычисление (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отображение значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Отображение значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Отображение значения в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Отображение значения в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Вычисление значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Выберите базовое поле и базовый элемент, если они доступны для нужных вычислений.
Примечание: Базовое поле не должно совпадать с полем, выбранным на шаге 1.
Для изменения форматирования чисел нажмите кнопку Числовой формат, а затем на вкладке Число диалогового окна Формат ячеек выберите числовой формат и нажмите кнопку OK.
Если в отчете содержится несколько полей значений, повторите предыдущие шаги для каждого такого поля, которое нужно изменить.
Примечание: Чтобы использовать для одного поля несколько функций сведения, снова добавьте это поле из списка полей сводной таблицы и повторите шаги, выбрав другую функцию.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Доступные функции сведения данных
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Промежуточные итоги являются одной из самых важных возможностей в отчетах сводных таблиц. Иногда промежуточные суммы и другие элементы не требуется отображать, особенно в случае вывода более подробной информации, чем простые промежуточные итоги по полям.
Сокрытие промежуточных итогов при наличии множества полей строк
Если в отчете содержится много полей строк, вывод промежуточных итогов может сбить вас с толку. Например, как показано на рис. 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).
Дополнительные настройки сводных таблиц применяются для управления самыми разными ее элементами. Например, промежуточные итоги могут располагаться под или над группой исходных данных или вообще могут быть исключены из исходной таблицы.
Как уже упоминалось ранее, перемещение промежуточных сумм в верхнюю часть группы данных позволяет уменьшить общий размер сводной таблицы на несколько строк. Тем не менее сами промежуточные итоги выводятся только в случае предстаатения сводной таблицы в форме структуры или в сжатой форме. Настраиваются промежуточные суммы с помощью меню Промежуточные итоги (Subtotals) контекстной вкладки Конструктор (Design). На рис. 3.16 показано, как выглядят промежуточные итоги, добавленные над группами исходных данных.
Рис. 3.16. Промежуточные итоги над группами данных и пустые строки между ними добавляются с помощью раскрывающихся меню группы Макет контекстной вкладки Конструктор
Обшие итоги выводятся в нижней части каждого столбца и/или каждой строки; их также можно легко скрыть в сводной таблице. Настраиваются общие итоги с помощью раскрывающегося меню Общие итоги контекстной вкладки Конструктор. Некоторые команды этого раскрывающегося меню выглядят несколько странно.
Чтобы добавить общие итоги в правой части сводной таблицы, выберите команду Включить только для строк (On for Rows). Данная команда может также применяться к строке общих итогов, вычисляемых для всех столбцов. Подобным образом команда Включить только для столбцов (On for Columns) требует добавлять обшие итоги только под сводной таблицей. Все построчные общие итоги также подлежат суммированию в последнем столбце сводной таблицы.
Раскрывающееся меню Пустые строки (Blank Rows) позволяет добавлять пустые строки между группами данных. На рис. 3.17 пустая строка 13 добавлена с помощью команды Вставить пустую строку после каждого элемента (Insert Blank Line After Each Item) данного меню.
Рис. 3.17. В Excel 2010 заголовки полей часто мешают правильно проанализировать имеющиеся данные
После изучения сводной таблицы, показанной на рис. 3.16. вам может показаться странным, что столбец В шире столбцов С, D и Е. Причина заключается в том, что в ячейке ВЗ находится текст Названия столбцов. Надпись Названия строк, находящаяся в ячейке А4, приводит к чрезмерному расширению столбца А. Эти надписи появились в версии Excel 2007, и несмотря на то что разрабатывались они в надежде повысить удобство управления сводной таблицей, на практике они только мешают быстро и, главное, правильно анализировать данные.
Чтобы удалить мешающие записи, перейдите на контекстную вкладку ленты Параметры (Options) и в группе Показать или скрыть (Show/Hide) щелкните на кнопке Заголовки полей (Field Headers). В этой группе находятся кнопки для сокрытия или отображения кнопок свертывания (минус) и развертывания (плюс) полей, а также кнопка, с помощью которой можно скрыть список полей сводной таблицы. На рис. 3.17 показана описанная выше группа ленты и часть сводной таблицы со всеми тремя скрытыми элементами.
До настоящего момента использовались 11 функций вычислений промежуточных итогов, такие как Сумма, Среднее и Количество. Помимо этих функций, при вычислении итогов используется ряд дополнительных функций, таких как С нарастающим итогом в поле (Running Total In), % от обшей суммы (% of Total) и т.д.
В Excel 2010 количество дополнительных функций, применяемых для вычисления итогов, выросло с 9 до 15, включая такие экзотические функции, как % от суммы с нарастающим итогом в поле,% от суммы по родительской строке/столбцу и т.д.
Обратите внимание на сводную таблицу (рис. 3.30), где столбец Доход повторяется 7 раз. В столбце D отображается поле Сумма по полю Доход, в котором не выполняются дополнительные вычисления. В столбцах Е:1 выполняются другие функции вычисления итогов, отображенные в списке Дополнительные вычисления (Show Values As).
Рис. 3.30. Для отображения промежуточных итогов можно воспользоваться рядом дополнительных функций
Для построения отчета, показанного на рис. 3.30, семь раз перетащите поле Доход в область Значения списка полей сводной таблицы. Для изменения применяемой для вычисления итогов функции изменяйте соответствующим образом настройки поля для каждого столбца Доход.
Как и в предыдущих версиях Excel, многие функции вычисления итогов не требуют дополнительных данных. Для пяти таких функций нужно указать базовое поле. Для трех функций требуется определить базовое поле и базовый элемент. Функции вычисления итогов, выбираемые в раскрывающемся списке Дополнительные вычисления, приведены в табл. 3.1.
Таблица 3.1. Функции вычисления итогов, выбираемые в списке Дополнительные вычисления
| Название функции | Изменение в Excel 2010 по сравнению с предыдущими версиями | Дополнительная информация | Описание |
|---|---|---|---|
| Без вычислений (No calculation) | Ранее называлась Нет (Normal) | Не требуется | |
| % от общей суммы (% of Grand Total) | Ранее называлась Доля от общей суммы (% of Total) | Не требуется | Отображение значений в процентах от обшей суммы всех значений или точек данных в отчете |
| % от суммы по столбцу (% of Column Total) | Ранее называлась Доля от суммы по столбцу (% of Column) | Не требуется | Отображение всех значений в каждом столбце или каждой строке в процентах от итогового значения по этому столбцу или этой строке |
| % от суммы по строке (% of Row Total) | Ранее называлась Доля от суммы по строке (% of Row) | Не требуется | Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории |
| % от суммы по родительской строке (% of Parent Row Total) | Появилась в Excel 2010 | Не требуется | Отображения значений в нескольких полях строк в виде процентов от общего итога по родительской строке |
| % от суммы по родительскому столбцу (% of Parent Column Total) | Появилась в Excel 2010 | Не требуется | Отображения значений в нескольких полях столбцов в виде процентов от обшего итога по родительскому столбцу |
| Индекс (Index) | Название не изменилось | Не требуется | Отображение относительной важности элемента |
| % от родительской суммы (% of Parent Total) | Появилась в Excel 2010 | Только базовое поле | На основе значений в нескольких полях строк и/или столбцов вычисляет долю ячейки по отношению к общему итогу от родительских элементов |
| С нарастающим итогом в поле (Running Total In) | Название не изменилось | Только базовое поле | Отображение значений в виде нарастающего итога для последовательных элементов |
| % от суммы с нарастающим итогом в поле (% Running Total In) | Появилась в Excel 2010 | Только базовое поле | Отображение значений в виде процента от нарастающего итога |
| Сортировка от минимального к максимальному (Rank Smallest to Large) | Появилась в Excel 2010 | Отображение ряда элементов, отсортированного от минимального (1) до максимального | |
| Сортировка от максимального к минимальному (Rank Largest to Smallest) | Появилась в Excel 2010 | Только базовое поле | Отображение ряда элементов, отсортированного от максимального (1) до минимального |
| Доля (% of) | Название не изменилось | Базовый элемент и базовое поле | Отображение значений в процентах к значению элемента в поле |
| Отличие (Difference From) | Название не изменилось | Базовый элемент и базовое поле | Отображение значений в виде разницы по отношению к значению элемента в поле |
| Приведенное отличие (% Difference From) | Название не изменилось | Базовый элемент и базовое поле | Отображение значений в виде разницы н процентах по отношению к значению элемента в поле |
В устаревших версиях Excel перечисленные выше функции находились на вкладке Дополнительные вычисления (Show Values As) диалогового окна Параметры поля значений (Value Field Settings). Несмотря на то что некоторые параметры на этой вкладке изначально скрыты, после выбора функции вычисления промежуточного итога на вкладке Дополнительные вычисления отображаются все параметры, используемые этой функцией. Также отображаются пункты (активные либо неактивные) в списках Поле (Base Field) или Элемент (Base Element), рис. 3.31. Можно также переименовать поле в диалоговом окне Параметры поля значений.
Рис. 3.31. Функции вычисления промежуточных итогов доступны на второй вкладке диалогового окна Параметры поля значений
После выбора требуемого параметра в раскрывающемся списке Дополнительные вычисления название соответствующего столбца сводной таблицы не изменяется. Задача по изменению этого заголовка возлагается на пользователя.
В Excel 2010 функции вычисления промежуточных итогов доступны в раскрывающемся списке Дополнительные вычисления (Show Values As), находящемся на контекстной вкладке Параметры (Options). Этот список более нагляден, чем вкладка Дополнительные вычисления диалогового окна Параметры поля значений, хотя в списке Дополнительные вычисления отсутствуют элементы Пользовательское имя, Поле и Элемент (рис. 3.32).
Рис. 3.32. Функцию вычисления итогов можно выбрать в раскрывающемся списке, появившемся в версии Excel 2010
Если выбранная функция использует базовое поле и/или базовый элемент, Excel 2010 отображает новое диалоговое окно, в котором можно выбрать соответствующее поле либо элемент. Целесообразнее изменять название столбца в соответствии с типом выбранной функции вычисления итогов. Для переименования столбца выберите контекстную вкладку Параметры (Options), щелкните на кнопке Активное поле (Active Field) и переименуйте поле Параметры поля значений (Field Settings). Можно также ввести новое имя столбца в ячейку заголовка.
Возможность создания собственных расчетов представляет собой еще один пример уникальной гибкости отчетов сводных таблиц. С помощью опций Дополнительные вычисления можно изменять расчеты для отдельного поля данных на основе других ячеек области данных.
Читайте также: