Вычисляемое поле в Сводных таблицах в MS Excel
В качестве исходной таблицы возьмем таблицу продаж товара по месяцам. В этой таблице также содержится план продаж. Подробности можно посмотреть в файле примера .
Нашей задачей будет:
- вычислить % выполнения плана
- представить полученные данные по годам для каждого месяца (каждый год — отдельный столбец)
В итоге у нас должна получиться вот такая сводная таблица.
Исходная таблица
Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).
На основе даты продажи в столбце А, в таблице рассчитываются 2 столбца: Номер месяца =МЕСЯЦ() и Год =ГОД() . Для форматирования ячеек столбца А в виде окт11 использован пользовательский формат Даты [$-419]МММГГ;@.
Столбец План представляет собой линейный тренд (это не важно для целей данной статьи), столбец Продано — фактический объем продаж.
Сводная таблица
Для создания сводной таблицы выделите любую ее ячейку и в меню Вставка/ Таблицы нажмите кнопку Сводная таблица. В результате появится диалоговое окно.
Нажав ОК, сводная таблица автоматически создастся на новом листе.
В окне Список полей будут отражены названия всех столбцов исходной таблицы. Таким образом, поле — это просто столбец. Вычисляемое поле — это, по сути, вычисляемый столбец.
Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.
Создаем вычисляемое поле
Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’
Это можно сделать непосредственно в Сводной таблице , создав Вычисляемое поле ПроцентВыполнения.
Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :
Появится диалоговое окно:
Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:
- Вместо Поле1 введите название Вычисляемого поля, например, ПроцентВыполнения
- В списке полей выделите поле Продано, руб. и нажмите кнопку Добавить поле или дважды кликните на него. Название поля будет введено в поле Формула
- Введите символ деления / в поле Формула
- В списке полей выделите поле План, руб. и нажмите кнопку Добавить поле
- Нажмите ОК
После проведенных манипуляций в списке поле Сводной таблицы появится еще одно поле. Завершите формирование Сводной таблицы как показано на рисунке ниже, разместив Вычисляемое поле в область Значения.
После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).
Обратите внимание, что Сводная таблица содержит Общий итог как по столбцам, так и по строкам.
Теперь разберемся, что Вычисляемое поле нам насчитало.
Вычисляемое поле. Алгоритм расчета
Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года — это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).
Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?
Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).
В этом состоит одно из ограничений Вычисляемого поля — итоговые значения вычисляются только на основании суммирования.
Аналогично расчет ведется и для итогов по столбцам: находится сумма продаж и плана по годам, затем вычисляется их отношение.
Если бы для каждого месяца в исходной таблице было бы несколько сумм продаж и плановых значений, то расчет был бы аналогичен подсчету итоговых значений.
Чтобы обойти данное ограничение и вычислить, например, средний % выполнения плана для всех январских месяцев, придется отказаться от Вычисляемого поля. Создайте в исходной таблице новый столбец — отношение продажи к плану для каждого месяца (см. лист Исходная таблица2). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.
В итоговом столбце теперь будет отображаться средний процент выполнения плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тоже диалоговое окно, которое мы использовали для создания Вычисляемого поля. В выпадающем списке выберите нужное поле. Появится его формула, которую можно отредактировать, также как и название этого Вычисляемого поля.
Там же можно удалить это поле.
Еще одно ограничение
Еще одно ограничение Вычисляемого поля проявляется при попытке использовать его в качестве названия Строк или Столбцов Сводной таблицы. Этого сделать нельзя. Покажем это на нашем примере.
Изначально в исходной таблице номер месяца и года вычислялись в отдельных столбцах. Попробуем сделать эти вычисления в Вычисляемом поле.
Создать само Вычисляемое поле для номера месяца — не проблема:
Однако, перенести его в качестве строк сводной таблицы не получается.
Как в сводной таблице добавить столбец с формулой
Сводные таблицы способны проводить вычисления, гораздо более сложные, чем простые суммирования данных. В данной статье я расскажу, как можно заставить Сводную таблицу «считать».
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
| Пример | 109 Кб | 4516 |
Допустим, мы имеем такую сводную таблицу:
Нам необходимо посчитать долю каждого менеджера в разрезе месяцев. Для этого в правом окне «Поля сводной таблицы», наводим курсор мышки на поле «Сумма», нажимаем левую клавишу и удерживая клавишу мышки тянем поле «Сумма» в окно «Значения»:
SWF
Как включить видео?
GIF
Чтобы увидеть видео включите в браузере GIF-Анимацию.
Затем наводим курсор на это поле в окне «Значения» и нажимаем на него левой клавишей мышки, в выпавшем меню выбираем пункт «Параметры полей значений. «:
SWF
Как включить видео?
GIF
Чтобы увидеть видео включите в браузере GIF-Анимацию.
В открывшемся диалоговом окне «Параметры поля значений», меняем название поля, например, на «Доля»:
Переходим во вкладку «Дополнительные вычисления», в поле со списком (там, где написано «Без вычислений») выбираем пункт «% от суммы по столбцу» и нажимаем кнопку «ОК»:
После чего ваша Сводная таблица должна будет приобрести следующий вид:
Поэкспериментируйте с другими вариантами вычислений.
Еще один пример вычислений, имеем такую Сводную таблицу:
В одном столбце у нас «Сумма продаж», в другой «Прибыль», нам необходимо получить рентабельность каждой категории товаров. Для этого устанавливаем курсор на сводную таблицу, в главном меню в разделе «Работа со сводными таблицами», во вкладке «Анализ» (в 2007 и 2010 «Параметры»), в разделе «Вычисления», нажимаем кнопку «Поля, элементы и наборы», в выпавшем списке выбираем пункт «Вычисляемое поле. » (в 2007 «Сервис» -> «Формулы»):
В открывшемся диалоговом окне меняем название поля, например, на «Рентабельность»:
В окне «Поля:» встаем на поле «Прибыль» и нажимаем кнопку «Добавить поле» так чтобы оно появилось в поле «Формула:», затем нажимаем на клавиатуре «/» — Знак деления, встаем на поле «Сумма» и снова нажимаем кнопку «Добавить поле». В итоге всех манипуляций в поле «Формула:» у вас должно получиться «=Прибыль/Сумма»:
покупка
Когда вы создали сводную таблицу на основе исходных данных, а иногда вы все еще надеетесь, что данные сводной таблицы могут выполнять дальнейшие вычисления для получения дополнительной информации. В этом случае Расчетное поле Функция может помочь вам добавить настраиваемое вычисляемое поле по своему усмотрению и не будет предупреждать исходные данные. Чтобы узнать больше об этой функции, прочтите эту статью.
Добавить вычисляемое поле в сводную таблицу в Excel
Предположим, у меня есть следующая сводная таблица, и теперь я хочу рассчитать среднюю продажу на человека в каждой компании. В этом примере я покажу вам, как применить функцию вычисляемого поля для решения этой задачи.
1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Инструменты сводной таблицы Вкладки.
2, Затем нажмите Опции > Поля, предметы и наборы > Расчетное поле, см. снимок экрана:
3. В Вставить вычисляемое поле В диалоговом окне введите нужное имя поля в текстовое поле Имя, см. снимок экрана :.
4. Затем в Формула текстовое поле, удалите исходную формулу и введите = только и нажмите Цена продажи, затем нажмите Вставить поле кнопку, чтобы вставить ваше поле в Формула box, см. снимок экрана :.
5. После вставки Цена продажи поле и введите знак деления / , и продолжайте щелкать Число людей поле, а затем щелкните Вставить поле кнопку, вы получите полностью настраиваемое вычисляемое поле. Смотрите скриншот :.
6. Затем нажмите Добавить в Вставить вычисляемое поле диалоговое окно, затем щелкните OK , чтобы закрыть диалоговое окно, и вы получите новый столбец поля средней продажи за исходными данными в сводной таблице.
Внимание: В этом примере я представляю простую формулу, если вам нужны сложные вычисления, вам просто нужно вставить формулу в Формула текстовое поле, как вам нужно ..
Статьи по теме:
Управление сводными таблицами в Excel
Сводная таблица – мощный инструмент Microsoft Excel. С ее помощью пользователь анализирует большие по объему диапазоны, подводит итоги всего в несколько кликов, выводит на экран только нужную в данный момент информацию.
Фильтр в сводной таблице Excel
В сводную таблицу можно преобразовать практически любой диапазон данных: итоги финансовых операций, сведения о поставщиках и покупателях, каталог домашней библиотеки и т.д.
Для примера возьмем следующую таблицу:
Создадим сводную таблицу: «Вставка» — «Сводная таблица». Поместим ее на новый лист.
Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.
Напомним, как выглядит диалоговое окно сводного отчета:
Перетаскивая заголовки, мы задаем программе инструкции для формирования сводного отчета. Если случайно допустим ошибку, из нижней области можно удалить заголовок, заменить его другим.
По данным, которые помещены в поле «Значения», подводятся итоги. В автоматическом режиме – сумма. Но можно задать «среднее», «максимум» и т.д. Если сделать это нужно для значений всего поля, то щелкаем по названию столбца и меняем способ представления итогов:
Например, среднее количество заказов по каждому поставщику:
Итоги можно менять не во всем столбце, а только в отдельной ячейке. Тогда щелкаем правой кнопкой мыши именно по этой ячейке.
Установим фильтр в сводном отчете:
- В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
- Перетащим это поле в область «Фильтр отчета».
- Таблица стала трехмерной – признак «Склад» оказался вверху.
Теперь мы можем отфильтровать значения в отчете по номеру склада. Нажимаем на стрелочку в правом углу ячейки и выбираем интересующие нас позиции:
В отчете отображается информация только по первому складу. Вверху видим значение и значок фильтра.
Отфильтровать отчет можно также по значениям в первом столбце.
Сортировка в сводной таблице Excel
Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».
Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:
После нажатия ОК сводная таблица приобретает следующий вид:
Отсортируем данные в отчете по значению столбца «Стоимость». Кликнем правой кнопкой мыши по любой ячейке или названию столбца. Выбираем «Сортировка» и способ сортировки.
Значения в сводном отчете поменяются в соответствии с отсортированными данными:
Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:
Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».
Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):
Формулы в сводных таблицах Excel
Сначала составим сводный отчет, где итоги будут представлены не только суммой. Начнем работу с нуля, с пустой таблицы. За одно узнаем как в сводной таблице добавить столбец.
- Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» — в сводную таблицу добавятся три одинаковых столбца.
- Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
- Поменяем местами значения столбцов и значения строк. «Поставщик» — в названия столбцов. «Σ значения» — в названия строк.
Сводный отчет стал более удобным для восприятия:
Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».
Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.
Получаем добавленный дополнительный столбец с результатом вычислений по формуле.
Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры