Сводные таблицы способны проводить вычисления, гораздо более сложные, чем простые суммирования данных. В данной статье я расскажу, как можно заставить Сводную таблицу "считать".

Файлы для скачивания:
ФайлОписаниеРазмер файла:Скачивания
Скачать этот файл (P_STab_01.zip)Пример 109 Кб1377

Допустим, мы имеем такую сводную таблицу:

Как проводить вычисления в сводной таблице?

Нам необходимо посчитать долю каждого менеджера в разрезе месяцев. Для этого в правом окне "Поля сводной таблицы", наводим курсор мышки на поле "Сумма", нажимаем левую клавишу и удерживая клавишу мышки тянем поле "Сумма" в окно "Значения":

SWF

Как включить видео?

GIF

kak-provodit-vychisleniya-v-svodnoj-tablitse_10.gif

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Затем наводим курсор на это поле в окне "Значения" и нажимаем на него левой клавишей мышки, в выпавшем меню выбираем пункт "Параметры полей значений...":

SWF

Как включить видео?

GIF

kak-provodit-vychisleniya-v-svodnoj-tablitse_12.gif

Чтобы увидеть видео включите в браузере GIF-Анимацию.

В открывшемся диалоговом окне "Параметры поля значений", меняем название поля, например, на "Доля":

kak-provodit-vychisleniya-v-svodnoj-tablitse_2.png

Переходим во вкладку "Дополнительные вычисления", в поле со списком (там, где написано "Без вычислений") выбираем пункт "% от суммы по столбцу" и нажимаем кнопку "ОК":

kak-provodit-vychisleniya-v-svodnoj-tablitse_3.png

После чего ваша Сводная таблица должна будет приобрести следующий вид:

kak-provodit-vychisleniya-v-svodnoj-tablitse_4.png

Поэкспериментируйте с другими вариантами вычислений.

Еще один пример вычислений, имеем такую Сводную таблицу:

kak-provodit-vychisleniya-v-svodnoj-tablitse_5.png

В одном столбце у нас "Сумма продаж", в другой "Прибыль", нам необходимо получить рентабельность каждой категории товаров. Для этого устанавливаем курсор на сводную таблицу, в главном меню в разделе "Работа со сводными таблицами", во вкладке "Анализ" (в 2007 и 2010 "Параметры"), в разделе "Вычисления", нажимаем кнопку "Поля, элементы и наборы", в выпавшем списке выбираем пункт "Вычисляемое поле..." (в 2007 "Сервис" -> "Формулы"):

kak-provodit-vychisleniya-v-svodnoj-tablitse_6.png

В открывшемся диалоговом окне меняем название поля, например, на "Рентабельность":

kak-provodit-vychisleniya-v-svodnoj-tablitse_7.png

В окне "Поля:" встаем на поле "Прибыль" и нажимаем кнопку "Добавить поле" так чтобы оно появилось в поле "Формула:", затем нажимаем на клавиатуре "/" - Знак деления, встаем на поле "Сумма" и снова нажимаем кнопку "Добавить поле". В итоге всех манипуляций в поле "Формула:" у вас должно получиться "=Прибыль/Сумма":

SWF

Как включить видео?

GIF

kak-provodit-vychisleniya-v-svodnoj-tablitse_14.gif

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Если все получилось, смело, нажимаем кнопку "ОК":

kak-provodit-vychisleniya-v-svodnoj-tablitse_8.png

В итоге ваша Сводная таблица должна будет принять следующий вид:

kak-provodit-vychisleniya-v-svodnoj-tablitse_9.png

Присвоим столбцу "Рентабельность" формат "%", уберем из заголовка лишние слова и получим такую красоту:

kak-provodit-vychisleniya-v-svodnoj-tablitse_16.png

В чем, собственно говоря, прелесть таких вычислений? В том, что если вы теперь добавите поле "SKU" в "СТРОКИ", то вы получите "Рентабельность" по каждому SKU, не вбивая никаких дополнительных формул:

SWF

Как включить видео?

GIF

kak-provodit-vychisleniya-v-svodnoj-tablitse_17.gif

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Комментарии  

# анатолий1977 01.09.2017 09:59
доброе утро, почему у меня получается "Доля" на месте поля "Сумма", а не в отдельном столбце, и как сделать что-бы "Доля" была в отдельном столбце и видна "Сумма"? Заранее спасибо за ответ.
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 01.09.2017 10:07
Вы в поле значения закиньте сумму два раза. Одно поле будет показывать сумму, второе настройте на долю.
Ответить | Ответить с цитатой | Цитировать
# анатолий1977 01.09.2017 10:23
в поле значений закинуть сумму два раза, это через Работа со сводными таблицами - поля,элементы и наборы - вычисляемое поле - вставка вычисляемого поля, и выбираем поле что нужно вставить, или я что-то путаю?? Потом-что делаю так и в этом поле только "нули". ...
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 01.09.2017 10:39
Нет. Вы мышкой перетаскивание поле "Сумма" из полей в "Значения", потом это же поле перетаскивание туда ещё раз. В итоге у вас получается два поля сумма в значениях. Потом в одном из этих полей в свойствах выбираете "дополнительные вычисления" и устанавливаете "доля".
Ответить | Ответить с цитатой | Цитировать
# анатолий1977 01.09.2017 17:51
Большущее спасибо за совет.))))
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 01.09.2017 17:55
Да не за что, пишите если что.
Ответить | Ответить с цитатой | Цитировать
# Якупов Айрат 24.11.2016 10:34
Спасибо очень помогло
Ответить | Ответить с цитатой | Цитировать
# ЮрийS 30.06.2016 19:28
Большое спасибо!
Еще вопрос:
Если в полях значений (в Вашем примере, например - Сумма) - стоит СУММА, то можно ли в Итоговой строке иметь, например, среднее значение ?
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 01.07.2016 08:46
К сожалению, нет. Можно попробовать "закинуть" "сумму" еще раз в значения, чтобы было два поля и у второго установить метод агрегации "среднее". Получиться два поля, одно будет показывать сумму второе - среднее.
Ответить | Ответить с цитатой | Цитировать
# Guest 10.02.2017 17:22
Цитирую Хвостов Сергей:
К сожалению, нет. Можно попробовать "закинуть" "сумму" еще раз в значения, чтобы было два поля и у второго установить метод агрегации "среднее". Получиться два поля, одно будет показывать сумму второе - среднее.

Сергей, добры день
подскажите, пожалуйста, как это сделать? "попробовать "закинуть" "сумму" еще раз в значения", что то у меня не получается...
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 10.02.2017 18:17
Ну, например, вы перенесли сумму в "Значения" - у Вас считается сумма. Вы берете сумму еще раз и отправляете ее в значения снова, чтобы у вас в "Значениях" - было два поля "Сумма". У второго поля меняете метод агрегации. Правда такой метод работает только в таблицах построенных традиционным способом, не через модель данных. Если вы мне напишите на почту, я в ответ вышлю файл пример.
Ответить | Ответить с цитатой | Цитировать
# ЮрийS 29.06.2016 18:25
Спасибо,
А как изменить формулу?
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 29.06.2016 18:33
Снова нажмите "Вычисляемое поле" в меню, в открывшемся диалоговом окне в поле "Имя" - выпадающий список, выберите в нем свое поле и измените формулу.
Ответить | Ответить с цитатой | Цитировать
# Александра В. 17.09.2015 08:43
Сергей,подскажи те пожалуйста, а можно создать ли формулу которая будет ссылаться на листы в книге?
Ответить | Ответить с цитатой | Цитировать
# Сергей Хвостов 17.09.2015 11:16
Александр, добрый день!
Ссылки, имена и массивы, к сожалению, нельзя использовать в формулах сводных таблиц.
Ответить | Ответить с цитатой | Цитировать
# Елена Семчук 16.10.2014 18:44
Не пойму почему - не получается сделать вычисление по сводной таблице, построенной из нескольких листов.
Можете помочь советом, пожалуйста?
Ответить | Ответить с цитатой | Цитировать
# Сергей Хвостов 17.10.2014 11:33
Елена кидайте файл мне на электронку, адрес в разделе "Контакты".
Ответить | Ответить с цитатой | Цитировать
# Дмитрий 06.08.2014 12:26
Спасибо. Это очень круто. Я три дня себе мозг выносил... а эта статья в минуту поставила все на места))
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 06.08.2014 13:25
Рад помочь...
Ответить | Ответить с цитатой | Цитировать

Добавить комментарий

Защитный код
Обновить