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

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

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

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

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

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-Анимацию.

Комментарии  

# Якупов Айрат 24.11.2016 10:34
Спасибо очень помогло
Ответить | Ответить с цитатой | Цитировать
# ЮрийS 30.06.2016 19:28
Большое спасибо!
Еще вопрос:
Если в полях значений (в Вашем примере, например - Сумма) - стоит СУММА, то можно ли в Итоговой строке иметь, например, среднее значение ?
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 01.07.2016 08:46
К сожалению, нет. Можно попробовать "закинуть" "сумму" еще раз в значения, чтобы было два поля и у второго установить метод агрегации "среднее". Получиться два поля, одно будет показывать сумму второе - среднее.
Ответить | Ответить с цитатой | Цитировать
# Юрий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
Рад помочь...
Ответить | Ответить с цитатой | Цитировать

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

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