в какую область сводной таблицы добавляется вычисляемое поле
Вычисляемое поле сводной таблицы «Excel». ( формулы в сводной таблице )
По умолчанию сводная таблица отображает поля, которые вычисляют сумму или количество значений из источника данных.
Как правило, этого хватает, чтобы обработать данные и провести простую аналитику полученной информации.
Иногда случается так, что суммы и количества из полей сводной таблицы недостаточно. В таких случаях на помощь приходят вычисляемые поля.
в этих полях можно прописать формулу, которая будет обрабатывать информацию сводной таблицы так, как Вам требуется.
О том, как записать формулу в вычисляемые поля сводной таблицы «Эксель» расскажу далее.
На основании таблицы, в которой изображены продажи товаров за несколько лет,
Таблица на листе в Excel
построена сводная таблица.
Сводная таблица в Эксель
В сводную таблицу нужно добавить вычисляемые ячейки с формулами.
Рассмотрим, как это можно сделать.
Выделяем сводную таблицу кликом левой кнопки мыши.
Перейти во вкладку «Параметры»
Кликнуть по ярлыку «Поля, элементы, наборы»
Вставить вычесляемое поле
Выбрать пункт «Вычисляемое поле»
В открывшемся окне указать имя нового поля.
Имя поля
Вставка формулы в поле сводной таблицы
Если в формуле следует использовать поля сводной таблицы, их можно внести в формулу кнопкой добавить.
Сводные таблицы Excel
Перед созданием вычисляемого поля необходимо открыть или сгенерировать сводную таблицу. В нашем примере используется сводная таблица, показанная на рис. 5.6.
Теперь, когда в нашем распоряжении имеется сводная таблица, наступило время для создания, вычисляемого поля. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля (Insert Calculated Field).
Рис. 5.6. Создайте сводную таблицу, в которую будет добавляться вычисляемое поле
Чтобы открыть его, на контекстной вкладке ленты Параметры (Options), выбранной в группе контекстных вкладок Работа со сводными таблицами (PivotTable Tools), перейдите в группу Вычисления (Calculations), щелкните на кнопке Поля, элементы и наборы (Fields, Items & Sets) и выберите в раскрывающемся меню команду Вычисляемое поле (Calculated Field), как показано на рис. 5.7.
Рис. 5.7. Создание вычисляемого поля начинается с этого раскрывающегося меню
На экране появится диалоговое окно Вставка вычисляемого поля (Insert Calculation Field), как показано на рис. 5.8.
Рис. 5.8. Диалоговое окно Вставка вычисляемого поля поможет в создании вычисляемого поля для сводной таблицы
В верхней части диалогового окна имеются два текстовых поля: Имя (Name) и Формула (Formula). В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. На рис. 5.9 показано, что вычисляемому полю присвоено описательное имя Средняя выручка за час. Это имя должно точно характеризовать тип выполняемой математической операции.
Рис. 5.9. Выбор для вычисляемого поля описательного имени
Далее перейдите к списку Поля (Fields) и дважды щелкните на поле Объем продаж. Введите символ косой черты /, чтобы указать программе Excel, что вы будете делить значение поля Объем продаж на другой элемент.
По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом собственной формулы следует удалить нуль.
С этого момента диалоговое окно должно выглядеть так, как показано на рис. 5.10.
Рис. 5.10. Начните ввод формулы со значения = ‘Объем продаж’/
Закончите ввод формулы, дважды щелкнув на поле Период продаж (в часах). Готовая формула показана на рис. 5.11.
Рис 5.11. Полная формула = ‘Объем продаж’/’Период продаж (в часах)’ отображает требуемое вычисляемое поле
Щелкните на кнопке Добавить (Add), а затем — на кнопке ОК для активизации нового вычисляемого поля. Как видно на рис. 5.12, в результате вы получите вычисляемое поле внутри сводной таблицы.
Рис. 5.12. Вы успешно добавили вычисляемое поле в сводную таблицу. Теперь можно изменять настройки этого нового поля точно так же, как и параметры любого другого поля (например, имя поля, числовой формат или цвет)
Обратите внимание на то, что после создания нового поля Средняя выручка за час оно добавляется в список полей.
Результат, полученный с помощью вычисляемого поля, не форматируется. При желании его можно отформатировать с помощью методики, описанной в разделе Создание базовой сводной таблицы.
Означает ли это, что вы добавили столбец в источник данных? Нет. Вычисляемые поля похожи на элементы Промежуточная сумма (Subtotal) и Общая сумма (Grand Total) сводной таблицы, задаваемые по умолчанию, так как все они являются математическими функциями, которые выполняют перерасчет данных при изменении или обновлении сводной таблицы. Вычисляемые поля просто имитируют строго заданные поля в источнике данных. Можете перетаскивать их, изменять настройки полей, а также использовать вместе с другими вычисляемыми полями.
Взгляните внимательно на рис. 5.11. Обратите внимание на то, что формат введенной вами формулы похож на используемый в стандартной строке формул Excel. Очевидное различие состоит в том, что вместо использования строгих ссылок на ячейки или числовых значений вы ссылаетесь на поля данных сводной таблицы для определения аргументов, применяемых в расчетах. Если вы уже работали с формулами в Excel, то быстро усвоите концепции создания вычисляемых полей.
Вычисляемое поле в Сводных таблицах в MS Excel
history 2 марта 2019 г.
Научимся добавлять и редактировать Вычисляемое поле в Сводной таблице MS EXCEL 2010.
Нашей задачей будет:
В итоге у нас должна получиться вот такая сводная таблица.
Исходная таблица
Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).
Сводная таблица
Для создания сводной таблицы выделите любую ее ячейку и в меню Вставка/ Таблицы нажмите кнопку Сводная таблица. В результате появится диалоговое окно.
Нажав ОК, сводная таблица автоматически создастся на новом листе.
Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.
Создаем вычисляемое поле
Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’
Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :
Появится диалоговое окно:
Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:
После проведенных манипуляций в списке поле Сводной таблицы появится еще одно поле. Завершите формирование Сводной таблицы как показано на рисунке ниже, разместив Вычисляемое поле в область Значения.
После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).
Обратите внимание, что Сводная таблица содержит Общий итог как по столбцам, так и по строкам.
Теперь разберемся, что Вычисляемое поле нам насчитало.
Вычисляемое поле. Алгоритм расчета
Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?
Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).
Аналогично расчет ведется и для итогов по столбцам: находится сумма продаж и плана по годам, затем вычисляется их отношение.
Если бы для каждого месяца в исходной таблице было бы несколько сумм продаж и плановых значений, то расчет был бы аналогичен подсчету итоговых значений.
В итоговом столбце теперь будет отображаться средний процент выполнения плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тоже диалоговое окно, которое мы использовали для создания Вычисляемого поля. В выпадающем списке выберите нужное поле. Появится его формула, которую можно отредактировать, также как и название этого Вычисляемого поля.
Там же можно удалить это поле.
Еще одно ограничение
Еще одно ограничение Вычисляемого поля проявляется при попытке использовать его в качестве названия Строк или Столбцов Сводной таблицы. Этого сделать нельзя. Покажем это на нашем примере.
Изначально в исходной таблице номер месяца и года вычислялись в отдельных столбцах. Попробуем сделать эти вычисления в Вычисляемом поле.
Однако, перенести его в качестве строк сводной таблицы не получается.
Сводные таблицы в Excel
history 10 апреля 2013 г.
Сводные таблицы ( Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:
Подготовка исходной таблицы
Начнем с требований к исходной таблице.
Вместо того, чтобы плодить повторяющиеся столбцы ( регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).
Создание таблицы в формате EXCEL 2007 добавляет новые возможности:
В качестве исходной будем использовать таблицу в формате EXCEL 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте.
В таблице имеются столбцы:
Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».
Создание Сводной таблицы
Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.
На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы).
Структура Сводной таблицы в общем виде может быть представлена так:
Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже ).
Теперь поставим галочку в Списке полей у поля Продажи.
Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.
Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью Отбор уникальных значений с суммированием по соседнему столбцу ). Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.
Детализация данных Сводной таблицы
Обновление Сводной таблицы
Удаление Сводной таблицы
Изменение функции итогов
Изменение порядка сортировки
Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).
Затем, нажав левую клавишу мыши, перетащите ячейку на самую верхнюю позицию в списке прямо под заголовок столбца.
После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.
Изменение формата числовых значений
Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат …
Добавление новых полей
Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.
Добавление столбцов
Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.
Сводная таблица примет следующий вид.
Меняем столбцы местами
Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место.
Удаление полей
Любое поле можно удалить из Сводной таблицы. Для этого нужно навести на него курсор мыши в Списке полей (в областях Фильтр отчета, Названия отчета, Названия столбцов, Значения), нажать левую клавишу мыши и перетащить удаляемое поле за границу Списка полей.
Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).
Добавление фильтра
Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:
Вид получившейся Сводной таблицы должен быть таким:
Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.
После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.
Обратите внимание, что в Списке полей Сводной таблицы напротив поля Прибыль появился значок фильтра. Удалить фильтр можно сняв галочку в Списке полей.
Также стандартный механизм фильтрации данных доступен через выпадающий список в заголовках строк и столбцов Сводной таблицы.
Несколько итогов для одного поля
Предположим, что требуется подсчитать количество проданных партий и суммарные продажи каждого Товара. Для этого:
Отключаем строки итогов
Группируем числа и Даты
Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:
Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:
Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.
Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:
Теперь Сводная таблица показывает, сколько партий Товара поставлялось каждый месяц.
Условное форматирование ячеек Сводной таблицы
К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:
Изменение итоговой функции или настраиваемого вычисления для поля в отчете сводной таблицы
Данные в поле значений обобщают данные источников (а не значения, которые отображается) в отчете сводной таблицы следующим образом: для числовых значений используется функция СУММ, а для текстовых — функция СЧЁТ. Однако функция сведения данных может быть изменена. Можно также создать настраиваемое вычисление.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Анализ в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Доступные функции сведения данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Количество числовых значений. Действует аналогично функции СЧЁТ.
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые значения.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые значения.
Число разных элементов
Число уникальных значений. Эта функция сведения работает только при использовании модели данных в Excel.
Примечание: Для некоторых типов исходных данных, таких как данные OLAP, вычисляемых полей и полей с вычисляемыми элементами функцию сведения изменить нельзя.
При необходимости можно использовать настраиваемое вычисление следующим способом.
Откройте вкладку Дополнительные вычисления.
В поле Дополнительные вычисления выберите нужный тип вычисления.
Выключение настраиваемого вычисления.
Отображение значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Отображение доли в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Вычисление (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Вычисление (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Вычисление (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отображение значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Отображение значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Отображение значения в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Отображение значения в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Вычисление значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Выберите базовое поле и базовый элемент, если они доступны для нужных вычислений.
Примечание: Базовое поле не должно совпадать с полем, выбранным на шаге 1.
Для изменения форматирования чисел нажмите кнопку Числовой формат, а затем на вкладке Число диалогового окна Формат ячеек выберите числовой формат и нажмите кнопку OK.
Если в отчете содержится несколько полей значений, повторите предыдущие шаги для каждого такого поля, которое нужно изменить.
Примечание: Чтобы использовать для одного поля несколько функций сведения, снова добавьте это поле из списка полей сводной таблицы и повторите шаги, выбрав другую функцию.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Количество числовых значений. Действует аналогично функции СЧЁТ.
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые значения.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые значения.
Примечание: Для некоторых типов исходных данных, таких как данные OLAP, вычисляемых полей и полей с вычисляемыми элементами функцию сведения изменить нельзя.
При необходимости можно использовать настраиваемое вычисление следующим способом.
Откройте вкладку Дополнительные вычисления.
В поле Дополнительные вычисления выберите нужный тип вычисления.
Выключение настраиваемого вычисления.
Отображение значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Отображение доли в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Вычисление (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Вычисление (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Вычисление (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отображение значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Отображение значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Отображение значения в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Отображение значения в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Вычисление значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Выберите базовое поле и базовый элемент, если они доступны для нужных вычислений.
Примечание: Базовое поле не должно совпадать с полем, выбранным на шаге 1.
Для изменения форматирования чисел нажмите кнопку Числовой формат, а затем на вкладке Число диалогового окна Формат ячеек выберите числовой формат и нажмите кнопку OK.
Если в отчете содержится несколько полей значений, повторите предыдущие шаги для каждого такого поля, которое нужно изменить.
Примечание: Чтобы использовать для одного поля несколько функций сведения, снова добавьте это поле из списка полей сводной таблицы и повторите шаги, выбрав другую функцию.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Доступные функции сведения данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.