Формулы в сводной таблице в excel
Вычисляемое поле в Сводных таблицах в MS Excel
Научимся добавлять и редактировать Вычисляемое поле в Сводной таблице MS EXCEL 2010.
Простые Сводные таблицы мы научились строить в статье Сводные таблицы в 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). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.
В итоговом столбце теперь будет отображаться средний процент выполнения плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тоже диалоговое окно, которое мы использовали для создания Вычисляемого поля. В выпадающем списке выберите нужное поле. Появится его формула, которую можно отредактировать, также как и название этого Вычисляемого поля.
Там же можно удалить это поле.
Еще одно ограничение
Еще одно ограничение Вычисляемого поля проявляется при попытке использовать его в качестве названия Строк или Столбцов Сводной таблицы. Этого сделать нельзя. Покажем это на нашем примере.
Изначально в исходной таблице номер месяца и года вычислялись в отдельных столбцах. Попробуем сделать эти вычисления в Вычисляемом поле.
Создать само Вычисляемое поле для номера месяца – не проблема:
Однако, перенести его в качестве строк сводной таблицы не получается.
Источник: excel2.ru
Вычисляемое поле сводной таблицы «Excel». ( формулы в сводной таблице )
По умолчанию сводная таблица отображает поля, которые вычисляют сумму или количество значений из источника данных.
Как правило, этого хватает, чтобы обработать данные и провести простую аналитику полученной информации.
Иногда случается так, что суммы и количества из полей сводной таблицы недостаточно. В таких случаях на помощь приходят вычисляемые поля.
в этих полях можно прописать формулу, которая будет обрабатывать информацию сводной таблицы так, как Вам требуется.
О том, как записать формулу в вычисляемые поля сводной таблицы «Эксель» расскажу далее.
На основании таблицы, в которой изображены продажи товаров за несколько лет,
Таблица на листе в Excel
построена сводная таблица.
Сводная таблица в Эксель
В сводную таблицу нужно добавить вычисляемые ячейки с формулами.
Рассмотрим, как это можно сделать.
Выделяем сводную таблицу кликом левой кнопки мыши.
Перейти во вкладку «Параметры»
Кликнуть по ярлыку «Поля, элементы, наборы»
Вставить вычесляемое поле
Выбрать пункт «Вычисляемое поле»
В открывшемся окне указать имя нового поля.
Имя поля
Вставка формулы в поле сводной таблицы
Если в формуле следует использовать поля сводной таблицы, их можно внести в формулу кнопкой добавить.
Источник: ruexcel.ru
Управление сводными таблицами в Excel
Сводная таблица – мощный инструмент Microsoft Excel. С ее помощью пользователь анализирует большие по объему диапазоны, подводит итоги всего в несколько кликов, выводит на экран только нужную в данный момент информацию.
Фильтр в сводной таблице Excel
В сводную таблицу можно преобразовать практически любой диапазон данных: итоги финансовых операций, сведения о поставщиках и покупателях, каталог домашней библиотеки и т.д.
Для примера возьмем следующую таблицу:
Создадим сводную таблицу: «Вставка» – «Сводная таблица». Поместим ее на новый лист.
Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.
Напомним, как выглядит диалоговое окно сводного отчета:
Перетаскивая заголовки, мы задаем программе инструкции для формирования сводного отчета. Если случайно допустим ошибку, из нижней области можно удалить заголовок, заменить его другим.
По данным, которые помещены в поле «Значения», подводятся итоги. В автоматическом режиме – сумма. Но можно задать «среднее», «максимум» и т.д. Если сделать это нужно для значений всего поля, то щелкаем по названию столбца и меняем способ представления итогов:
Например, среднее количество заказов по каждому поставщику:
Итоги можно менять не во всем столбце, а только в отдельной ячейке. Тогда щелкаем правой кнопкой мыши именно по этой ячейке.
Установим фильтр в сводном отчете:
- В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
- Перетащим это поле в область «Фильтр отчета».
- Таблица стала трехмерной – признак «Склад» оказался вверху.
Теперь мы можем отфильтровать значения в отчете по номеру склада. Нажимаем на стрелочку в правом углу ячейки и выбираем интересующие нас позиции:
В отчете отображается информация только по первому складу. Вверху видим значение и значок фильтра.
Отфильтровать отчет можно также по значениям в первом столбце.
Сортировка в сводной таблице Excel
Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».
Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:
После нажатия ОК сводная таблица приобретает следующий вид:
Отсортируем данные в отчете по значению столбца «Стоимость». Кликнем правой кнопкой мыши по любой ячейке или названию столбца. Выбираем «Сортировка» и способ сортировки.
Значения в сводном отчете поменяются в соответствии с отсортированными данными:
Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:
Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».
Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):
Формулы в сводных таблицах Excel
Сначала составим сводный отчет, где итоги будут представлены не только суммой. Начнем работу с нуля, с пустой таблицы. За одно узнаем как в сводной таблице добавить столбец.
- Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» – в сводную таблицу добавятся три одинаковых столбца.
- Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
- Поменяем местами значения столбцов и значения строк. «Поставщик» – в названия столбцов. «Σ значения» – в названия строк.
Сводный отчет стал более удобным для восприятия:
Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» – «Вычисляемое поле».
Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.
Получаем добавленный дополнительный столбец с результатом вычислений по формуле.
Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.
Источник: exceltable.com
Excel works!
Excel работает за вас
Excel works!
Thanks for Visiting
Формулы в сводной таблице. Как сделать?
Кто не любит сводные таблицы? Есть такие? Срочно восполняем пробел в этих статьях 1 и 2 . Сегодня постараюсь подробно рассказать, как сделать формулы в сводной таблице без дополнительных в соседних ячейках. Запись формулы в сводной таблице подойдет, если вам необходимо держать все расчеты в одном месте и для сохранения формул при добавлении новых параметров в анализ. Удобно, если вы ищете лучший вариант сводной таблицы или анализируете данные!
Хочется отметить, что формулы в сводной таблице можно создавать довольно примитивные, но это не уменьшает их пользу. *Я рассматриваю случай пока только для Excel 2007 и выше.
Как записать формулы в сводной таблице?
Все, как обычно, проще, чем кажется. Предположим, у нас есть список показателей выручка и затраты по городам. Нам необходимо посчитать прибыль. Создаем сводную таблицу:
Теперь необходимо задать формулу прибыли, можно конечно это сделать рядом, но если, например, вы добавите детализацию по товару, то формулу придется дополнять — удобнее сделать внутри сводной.
Нажав курсором на сводную таблицу, появится раздел Работа со сводными таблицами в панели инструментов
Выбираем Параметры — раздел Сервис — Формулы
Нажимаем формулы — Вычисляемое поле, в открывшемся окне вводим нужную формулу. Создать формулу можно кликая на реквизиты в таблице внизу, знаки действий ставим с клавиатуры. Вводим нужное имя вместо Поле1, например Прибыль.
Ссылка на сводную таблицу
Если вам все-таки нужно сослаться на сводную таблицу, то ссылка в большинстве случае будет иметь вид = ПОЛУЧИТЬ.ДАННЫЕ(…
Чтобы записать обычную формулу, самый простой способ такой, как показано ниже
Если есть вопросы, пишите нам в комментарии!
Источник: excelworks.ru
Формулы в сводной таблице в excel
Сводные таблицы способны проводить вычисления, гораздо более сложные, чем простые суммирования данных. В данной статье я расскажу, как можно заставить Сводную таблицу “считать”.
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
| Пример | 109 Кб | 2498 |
Допустим, мы имеем такую сводную таблицу:
Нам необходимо посчитать долю каждого менеджера в разрезе месяцев. Для этого в правом окне “Поля сводной таблицы”, наводим курсор мышки на поле “Сумма”, нажимаем левую клавишу и удерживая клавишу мышки тянем поле “Сумма” в окно “Значения”:
Как включить видео?
Чтобы увидеть видео включите в браузере GIF-Анимацию.
Затем наводим курсор на это поле в окне “Значения” и нажимаем на него левой клавишей мышки, в выпавшем меню выбираем пункт “Параметры полей значений. “:
Как включить видео?
Чтобы увидеть видео включите в браузере GIF-Анимацию.
В открывшемся диалоговом окне “Параметры поля значений”, меняем название поля, например, на “Доля”:
Переходим во вкладку “Дополнительные вычисления”, в поле со списком (там, где написано “Без вычислений”) выбираем пункт “% от суммы по столбцу” и нажимаем кнопку “ОК”:
После чего ваша Сводная таблица должна будет приобрести следующий вид:
Поэкспериментируйте с другими вариантами вычислений.
Еще один пример вычислений, имеем такую Сводную таблицу:
В одном столбце у нас “Сумма продаж”, в другой “Прибыль”, нам необходимо получить рентабельность каждой категории товаров. Для этого устанавливаем курсор на сводную таблицу, в главном меню в разделе “Работа со сводными таблицами”, во вкладке “Анализ” (в 2007 и 2010 “Параметры”), в разделе “Вычисления”, нажимаем кнопку “Поля, элементы и наборы”, в выпавшем списке выбираем пункт “Вычисляемое поле. ” (в 2007 “Сервис” -> “Формулы”):
В открывшемся диалоговом окне меняем название поля, например, на “Рентабельность”:
В окне “Поля:” встаем на поле “Прибыль” и нажимаем кнопку “Добавить поле” так чтобы оно появилось в поле “Формула:”, затем нажимаем на клавиатуре “/” – Знак деления, встаем на поле “Сумма” и снова нажимаем кнопку “Добавить поле”. В итоге всех манипуляций в поле “Формула:” у вас должно получиться “=Прибыль/Сумма”:
Источник: e-xcel.ru
Настройка вычислений в сводных таблицах
Допустим, у нас есть построенная сводная таблица с результатами анализа продаж по месяцам для разных городов (если необходимо, то почитайте эту статью, чтобы понять, как их вообще создавать или освежить память):
Нам хочется слегка изменить ее внешний вид, чтобы она отображала нужные вам данные более наглядно, а не просто вываливала кучу чисел на экран. Что для этого можно сделать?
Другие функции расчета вместо банальной суммы
Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (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 к этому набору добавились несколько новых функций:
-
% от суммы по родительской строке (столбцу) – позволяет посчитать долю относительно промежуточного итога по строке или столбцу:
В прошлых версиях можно было вычислять долю только относительно общего итога.
% от суммы нарастающим итогом – работает аналогично функции суммирования нарастающим итогом, но отображает результат в виде доли, т.е. в процентах. Удобно считать, например, процент выполнения плана или исполнения бюджета: 
Источник: www.planetaexcel.ru
