Как изменить обрабатывающую функцию для полей значений сводной таблицы
Данные в поле значений обобщают данные источников (а не значения, которые отображается) в отчете сводной таблицы следующим образом: для числовых значений используется функция СУММ, а для текстовых — функция СЧЁТ. Однако функция сведения данных может быть изменена. Можно также создать настраиваемое вычисление.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Анализ в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Доступные функции сведения данных
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Количество числовых значений. Действует аналогично функции СЧЁТ.
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые значения.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые значения.
Число разных элементов
Число уникальных значений. Эта функция сведения работает только при использовании модели данных в Excel.
Примечание: Для некоторых типов исходных данных, таких как данные OLAP, вычисляемых полей и полей с вычисляемыми элементами функцию сведения изменить нельзя.
При необходимости можно использовать настраиваемое вычисление следующим способом.
Откройте вкладку Дополнительные вычисления.
В поле Дополнительные вычисления выберите нужный тип вычисления.
Выключение настраиваемого вычисления.
Отображение значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Отображение доли в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Вычисление (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Вычисление (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Вычисление (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отображение значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Отображение значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Отображение значения в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Отображение значения в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Вычисление значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Выберите базовое поле и базовый элемент, если они доступны для нужных вычислений.
Примечание: Базовое поле не должно совпадать с полем, выбранным на шаге 1.
Для изменения форматирования чисел нажмите кнопку Числовой формат, а затем на вкладке Число диалогового окна Формат ячеек выберите числовой формат и нажмите кнопку OK.
Если в отчете содержится несколько полей значений, повторите предыдущие шаги для каждого такого поля, которое нужно изменить.
Примечание: Чтобы использовать для одного поля несколько функций сведения, снова добавьте это поле из списка полей сводной таблицы и повторите шаги, выбрав другую функцию.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Количество числовых значений. Действует аналогично функции СЧЁТ.
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые значения.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые значения.
Примечание: Для некоторых типов исходных данных, таких как данные OLAP, вычисляемых полей и полей с вычисляемыми элементами функцию сведения изменить нельзя.
При необходимости можно использовать настраиваемое вычисление следующим способом.
Откройте вкладку Дополнительные вычисления.
В поле Дополнительные вычисления выберите нужный тип вычисления.
Выключение настраиваемого вычисления.
Отображение значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Отображение доли в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Вычисление (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Вычисление (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Вычисление (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отображение значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Отображение значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Отображение значения в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Отображение значения в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Вычисление значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Выберите базовое поле и базовый элемент, если они доступны для нужных вычислений.
Примечание: Базовое поле не должно совпадать с полем, выбранным на шаге 1.
Для изменения форматирования чисел нажмите кнопку Числовой формат, а затем на вкладке Число диалогового окна Формат ячеек выберите числовой формат и нажмите кнопку OK.
Если в отчете содержится несколько полей значений, повторите предыдущие шаги для каждого такого поля, которое нужно изменить.
Примечание: Чтобы использовать для одного поля несколько функций сведения, снова добавьте это поле из списка полей сводной таблицы и повторите шаги, выбрав другую функцию.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Доступные функции сведения данных
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Как изменить обрабатывающую функцию для полей значений сводной таблицы
Когда в сводной таблице есть расчетные поля , может возникнуть необходимость внести исправления в сам расчет, либо удалить данное поле.
Для того, чтобы это сделать, необходимо воспользоваться модулем настройки вычисляемых полей в EXCEL.
В примере вычисляемым поле является поле с валовым доходом с НДС .
Предположим, нам необходимо изменить расчет в поле и перевести в валовый доход БЕЗ НДС.
Для упрощения, определим ставку НДС=18% для всех групп товаров в таблице.
Для этого:
1. Кликнем на любом элементе сводной таблицы и в группе меню «Параметры» на ленте.
2. Кликнем на «Формулы» (или «Поля, элементы, наборы») и выберем «Вычисляемое поле» .
3. В открывшемся окне, необходимо нажать на выпадающий список справа от поля «Имя» . По умолчанию, имена вычисляемых полей сводной таблицы в данном модуле не отображаются.
4. Выберем поле, по которому необходимо изменить расчет и откорректируем формулу в поле «Формула» .
В примере мы переведем Валовый доход в значения без НДС.
=’Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’ 
=(‘Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’)/1,18
5. Переименуем поле и нажмем кнопку «Изменить» . Закроем модуль.
Итогом будет пересчитанный, уже по новой формуле, столбец.
Для удаления поля, необходимо выбрать имя поля в модуле и нажать «Удалить».
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Изменение настраиваемого вычисления для поля в отчете сводных таблиц
Сводные таблицы предоставляют широкие возможности обработки данных. О некоторых возможностях пользователи иногда даже не подозревают. �� Рассмотрим следующий пример [1].
Исходные данные представляют собой отчет об отгрузках (в штуках) за первые пять месяцев 2011 г.:
Рис. 1. Исходные данные (источник). Отчет об отгрузках.
Скачать заметку в формате Word, примеры в формате Excel
При формировании сводной таблицы по умолчанию данные в поле значений обрабатывают данные источника следующим образом (рис. 2):
- для числовых значений используется функция СУММ;
- для текстовых – функция СЧЁТ.
Видно (см. рис. 2), что для поля исходных данных «Отгружено, шт.» в сводной таблице применяется суммирование, а для поля исходных данных «Месяц» – подсчет числа ячеек с данными. При этом если используется функция суммирования, можно создать настраиваемое вычисление. В настоящей статье речь именно об этих возможностях.
Рис. 2. Суммирование или подсчет количества ячеек в зависимости от типа исходных данных
Для настройки вычислений в области значений выделите поле, для которого требуется изменить итоговую функцию отчета сводной таблицы. На вкладке Параметры в группе Активное поле нажмите кнопку Параметры поля (или воспользуйтесь контекстным меню, которое вызывается правой кнопкой мыши). Перейдите на вкладку «Дополнительные вычисления».
Доступны следующие варианты настройки вычислений сводной таблицы:
- Отличие. Отображает значения в виде разницы по отношению к значению выбранного элемента в поле.
Компании часто сравнивают результаты своей деятельности за текущий месяц с результатами, полученными в предыдущем месяце.
Чтобы получить такой отчет, на вкладке Дополнительные вычисления выберите опцию Отличие. Поскольку нужно сравнить один месяц с другим, в списке Поле выберите Месяц. В области Элемент содержится несколько значений. Если вы хотите сравнить каждый месяц с предыдущим, выберите опцию (назад). Если вы хотите сравнить данные с каким-то конкретным месяцем, то выберете этот месяц из списка (рис. 3). Видно, что в колонке Е показывается относительное изменение объема продаж в штуках… относительно предыдущего месяца.
2. Доля. Отображает значения в виде относительной разницы (в процентах) по отношению к значению выбранного элемента в поле. В нашем примере – по отношению к предыдущему месяцу.
3. Приведенное отличие. Отображает значения в виде разницы в процентах по отношению к значению выбранного элемента в поле.
Рис. 5. Приведенное отличие
Относительное изменение можно совместить с обычным суммированием в одной сводной таблице, что позволит построить весьма наглядный график (см. также лист «Приведенное отличие» в файле Excel):
Как строить такие диаграммы можно почитать в заметке «Как добавить линию на график».
4. С нарастающим итогом в поле. Отображает значения в виде нарастающего итога для последовательных элементов. Может быть использовано для АВС-анализа (он же анализ Парето)
Рис. 6. С нарастающим итогом в поле.
5. Доля от суммы по строке. Отображает значения в каждой строке в процентах от итогового значения по этой строке.
Указывает процентное соотношение в каждой строке и отдельно для каждой строки. Этот тип отчетов часть применяется при анализе сезонности в деятельности компании. Видно (см. рис. 7), что по артикулу 1002 в январе было продано 11,84% от суммарных продаж артикула 1002 за пять месяцев.
Рис. 7. Доля от суммы по строке.
6. Доля от суммы по столбцу. Отображает значения в каждом столбце в процентах от итогового значения по этому столбцу.
Этот тип отчетов может применяться при анализе вклада отдельных направлений (артикулов) в деятельность компании. Видно (см. рис. 8), что наибольший вклад в 5-месячный объем продаж дает артикул 1027 – 4,92% (после установки «доля от суммы по столбцу» я отсортировал артикулы по убывающей, чтобы наиболее «жирные» артикулы расположились вверху сводной таблицы).
Рис. 8. Доля от суммы по столбцу.
7. Доля от общей суммы. Отображает значения в процентах от общей суммы всех значений в отчете.
Для удобства восприятия (см. рис. 9) я ограничил число артикулов десятью самыми «жирными» – дающими наибольший вклад в 5-месячные продажи. Для этого я использовал фильтр по названию строк – «Первые 10…». Например, в феврале у артикула 1027 значение 4,66%. Что означает это число? Если сложить продажи за пять месяцев указанных 10 артикулов, то 4,66% – вклад в полученную сумму артикула 1027 в феврале.
Рис. 9. Доля от общей суммы
8. Индекс. Отображает относительную важность элемента.
Напомню, что значение индекса вычисляется по формуле:
(Значение в ячейке x Общий итог) / (Итог строки x Итог столбца)
К сожалению, физический смысл этой формуле мне не вполне понятен, поэтому в своей практике я ни разу не использовал такое представление данных… �� В нашем примере (см. рис. 10) наибольший индекс получился у артикула 1073 в январе. Почему это поле в соответствии с индексом, получило столь выдающееся значение, мне не понятно…
Настраиваемое вычисление создают дополнительные возможности для представления данных непосредственно в самих сводных таблицах.
Изменение функций полей сводной таблицы
В Excel имеется возможность поменять функцию поля(Правая кнопка мыши на поле-Параметры полей значений: Сумма, Количество, Среднее и т.д.). Но почему-то отсутствует возможность изменения функции сразу нескольких полей сводной таблицы. Иногда это может слегка разочаровать, особенно если надо изменить сразу полей 30. Именно этот недостаток исправляет данная команда — с её помощью Вы сможете поменять сразу несколько полей в одной таблице, выбранных таблицах или во всех сводных таблицах листа.
Функции поля — полностью дублируются все функции, применимые к полям значений сводной таблицы. Здесь выбирается та функция, которую необходимо назначить всем указанным полям сводной таблицы.
Область применения:
- Применить только к выделенным полям — функции полей значений будут заменены лишь в тех полях, которые выделены. При этом выделены могут быть разные(не смежные) поля разных сводных таблиц.
- Применить ко всем полям выделенных сводных — функции полей значений будут заменены во всех полях тех сводных, хотя бы одна ячейка которых выделена.
- Применить ко всем полям всех сводных на листе — функции полей значений будут заменены во всех сводных на листе.
Дополнительно: вверху таблицы указывается к каким конкретно сводным таблицам будет применена команда.