Почему в сводной таблице вместо суммы количество
Перейти к содержимому

Почему в сводной таблице вместо суммы количество

Отображение различных вычислений в полях значений сводной таблицы

Вместо того чтобы писать собственные формулы в вычисляемых полях, можно использовать функцию « Показать значения как» для быстрого представления значений различными способами. Он также предоставляет несколько новых параметров вычислений, таких как % от родительского итога или %Running Total In.

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

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

    Примечание: Поле значения добавляется в сводную таблицу, а номер версии добавляется к имени поля. При необходимости можно изменить имя поля.

    Примечание: В Excel для Mac меню «Показать значения как» не отображаются все те же параметры, что и в Excel для Windows, но они доступны. Выберите пункт «Дополнительные параметры» в меню, если вы не видите нужный вариант в списке.

    Дополнительные вычисления

  2. Выберите параметр вычисления, который вы хотите использовать. Доступны следующие варианты вычисления:

Значение, введенное в данное поле.

Отображает значения в процентах от общего итога всех значений или точек данных в отчете.

% от суммы по столбцу

Отображает все значения в каждом столбце или ряду в процентах от общего значения столбца или ряда.

% от суммы по строке

Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.

Значения в процентах от значения базового элемента в соответствующем базовом поле.

% от суммы по родительской строке

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по строкам).

% от суммы по родительскому столбцу

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по столбцам).

% от родительской суммы

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента в выбранном базовом поле).

Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.

Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.

С нарастающим итогом в поле

Значение в виде нарастающего итога для последовательных элементов в базовом поле.

% от суммы с нарастающим итогом в поле

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

Сортировка от минимального к максимальному

Отображает ранг выбранных значений в определенном поле, перечисляя наименьший элемент в поле как 1 и каждое большее значение с более высоким значением ранга.

Сортировка от максимального к минимальному

Отображает ранг выбранных значений в определенном поле, перечисляя наибольший элемент в поле как 1 и каждое меньшее значение с более высоким значением ранга.

Рассчитывает значения следующим образом:

((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).

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

Повторяющиеся значения в области значений

В приведенном выше примере три поля значений были добавлены во второй раз в область значений. к их именам добавлено слово «_2». Ниже поле «Сумма 1/1/14» отображается дважды в отчете сводной таблицы, поэтому вы можете увидеть значение для каждого города и его родительского (восточная или западная область). Вы также увидите процент общего итога для каждого региона (в строках 6 и 9).

То же поле со значениями и процентными величинами

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Почему в сводной таблице вместо суммы количество

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

5 полезных свойств сводных таблиц для бизнес анализа

бизнес анализ

Из данной статьи вы узнаете о 5 полезных свойствах сводных таблиц, которые помогу вам быстро и детально проанализировать ситуацию (на примере анализа клиентской базы). Вы узнаете:

1. Как сгруппировать данные;
2. Какие показатели можно рассчитать при сведении данных;
3. Как одновременно рассчитать несколько показателей по одному параметру при сведении данных;
4. Какие дополнительные возможности расчета при сведении данных вы можете использовать?
5. О возможности сортировки.

И на основании этого анализа мы затронем мощнейшую технику планирования мероприятий по стимулированию сбыта на рынках FMCG.

Для начала сделаем сводную таблицу. Возьмем простую таблицу продажи клиентам по дням.

Установим курсор в левый верхний угол нашей таблицы, затем переходим в меню «Вставка» и нажимаем на кнопку «Сводная таблица»:

Сводная таблица

В диалоговом окне «Создание сводной таблицы» нажимаем «ОК»:

Получили сводную таблицу на новом листе:

svodnaya tablica

1-е полезное свойство сводной таблицы для бизнес анализа — группировка данных

Итак, у нас есть отгрузки клиентам по дням, мы хотим понять, в каком диапазоне отгрузок у нас максимальные объемы продаж. Для этого нам надо сгруппировать отгрузки в диапазоны.

Перетаскиваем поле «Отгрузка сумма» в область сводной таблицы «Название строк» (зажимаем поле «Отгрузка_сумма» левой кнопкой мыши и перетаскиваем в раздел сводной «Название строк»):

группировка в сводной таблице

Мы вывели все отгрузки в левый столбец сводной. Теперь устанавливаем курсор наши отгрузки (как на рисунке):

Группировка с помощью сводной

Заходим в меню Excel «Данные» и нажимаем на кнопку «Группировать»

бизнес анализ и сводные таблицы

В появившемся диалоговом окне устанавливаем шаг группировки «5000» (вы можете ввести любой) и нажимаем «ОК»

группировка в сводной

Получаем сгруппированные объемы продаж с заданным шагом:

Группировка данных

Для того, чтобы группировка выглядела красиво и воспринималось, нажимаем еще раз кнопку «Группировать» и руками ставим ровные значения, для значения «начиная с» — «-15 000» (ниже минимального значения, кратного 5000) «по» — «45 000» (больше максимальной группы, кратное 5000).

Выставляем в сводной таблицы группы

Получаем сгруппированные данные по сумме отгрузке:

задаем шаг для групп в сводной таблице

2-е полезное свойство сводных таблиц для бизнес анализа —
возможность рассчитать различные сводные параметры по полям из исходной таблицы

Итак, отгрузки сгруппировали, теперь посмотрим — какие объемы продаж приходятся на каждый диапазон отгрузок. Для этого просуммируем отгрузки в сводной.

Зажимаем левой кнопкой мыши поле «Отгрузка_сумма» и перетаскиваем его в поле сводной «Значения»:

суммируем данные в сводной таблице

Сводная по умолчанию рассчитала «Количество по полю Отгрузка_сумма», т.е. кол-во записей в нашей исходной таблице на листе «Данные». Т.к. таблица у нас содержит информацию по продажам клиентам по дням, то наш показатель «Количество по полю Отгрузка_сумма» — это количество отгрузок клиентам.

В итоге в сводной таблице мы видим количество отгрузок клиентам в разных диапазонах отгрузок:

группировка объемов продаж

Как нам из количества отгрузок получить сумму отгрузок?

Нажимаем левой кнопкой мыши на поле «Количество по полю Отгрузка сумма» в области сводной таблицы «Значения», и в раскрывшемся меню выбираем «Параметры полей значений…»

параметры сводной таблицы

В раскрывшемся окне выбираем интересующую нас операцию сведения данных (Сумма, количество, среднее, максимум, минимум. ). Выберем нужную нам операцию «сумма» и нажимаем «ОК».

суммирование в сводной таблице

Получаем суммарный объём продаж для каждого диапазона отгрузок:

summa otgruzki itog

Т.е. мы видим, какой объем продаж приходится на отгрузки в диапазоне от 0 до 5000 руб, от 5000 до 10 000 руб. и т.д. И видно, что максимальный объём отгрузок приходится на диапазон от нуля до 5000 руб.

3 свойство – возможность для одного поля рассчитывать различные операции сведения данных

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

В область сводной таблицы «Значения» перетаскиваем еще 2 раза поле «Отгрузка_сумма» и в параметрах поля значений для второго выбираем «количество» и для третьего поля выбираем «среднее».

Получаем для каждого диапазона отгрузок объем продаж, количество отгрузок и среднюю отгрузку:

группировка данных в сводной таблицы для бизнес анализа

Теперь мы видим, в каком диапазоне отгрузок максимальный объем продаж и максимальное количество отгрузок. В нашем примере это для диапазона от 0 до 5000 руб. и объём продаж и количество отгрузок максимально.

4-е свойство сводных таблиц — возможность проводить дополнительные расчеты

Для наглядности анализа данных добавим еще 2 параметра — «Долю по объёма продаж по каждой группе»и «Долю количества отгрузок для каждой группы».

Для этого в поле сводной таблицы «Значения» перетащим еще 2 раза поле «Отгрузка сумма»

4 dop raszety

Причем для одного параметра в меню «Параметры поля значений» (как это сделать см. выше) мы выберем операцию «сумма», а для второго операцию «количество» .

Получаем таблицу следующего вида:

сведение и группировка дынных в сводной таблице

Теперь еще раз заходим в «Параметры полей значений» и входим во вкладку «Дополнительные вычисления»:

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

Выбираем в поле «Дополнительные вычисления» пункт «Доля от общей суммы»

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

Получаем таблицу, в которой для каждого диапазона отгрузок клиентам мы видим объём продаж, количество отгрузок, среднюю отгрузку, долю объема продаж по каждой группе и долю количества отгрузок по каждой группе:

4 dolya obschey summy itog

5 полезное свойство — сортировка

Теперь для наглядности от максимальной к минимальной группе по объёму продаж сделаем сортировку. Для этого установим курсор в поле с объёмом продаж по группам и нажмем на кнопку «сортировка от максимального к минимальному»:

бизнес анализ в сводной таблице

сортировка в сводной тиблице

Видно, что максимальная группа по объёму продаж и количеству отгрузок — это группа «от 0 до 5000 руб.» средние продажи в этой группе составляют 1971 руб.

Обратите внимание! Средняя отгрузка по всем клиентам значительно отличается от 86% отгрузок. Причем отличается значительно

  • по всем группам средняя отгрузка равна 2 803 руб. (в строке общий итог).
  • А по 86% отгрузок 1 971 руб.

Это серьезная разница, и если мы будем стимулировать продажи опираясь на 86% отгрузок и среднюю по ним — 1 971 руб., то наши действия будут точней, а эффект гораздо выше, т.к. мы сможем заинтересовать максимальное количество клиентов.

Данный пример показывает мощнейшую технику планирования мероприятий по стимулированию сбыта на рынках массового сбыта и прогнозирования объёма продаж, которая может помочь вам значительно влиять на ситуацию и изменять её.

Если есть вопросы, обращайтесь.

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Дополнительные вычисления в сводных таблицах

Интересный факт: часто встречаю пользователей, которые хорошо владеют инструментом сводных таблиц, но при этом не знают о такой их возможности, как дополнительные вычисления в сводных таблицах. Такие вычисления доступны в Excel 2010–2016, а в Excel 2007 дополнительные вычисления «спрятаны» в параметрах поля и их гораздо меньше.

Например, у нас есть простая таблица Excel по продажам вот с такими данными:

пример сводной таблицы

Предположим, нам нужно построить несколько отчетов:

  1. Процентная структура продаж.
  2. Продажи нарастающим итогом.
  3. Продажи с темпами роста.

Разберем, как создать такие отчеты с помощью дополнительных вычислений в сводных таблицах.

1. Процентная структура продаж

Чтобы с помощью сводных таблиц определить процентную структуру продаж, нужно сделать несколько простых действий.

Шаг 1. Постройте сводную таблицу, где в области строк Города и Товары, а в области сумм — Доходы (если вы не знаете, как создать сводную таблицу, посмотрите статью «Как построить сводную таблицу в Excel»).

Сводная таблица

Шаг 2. Щелкаем правой кнопкой мыши по любому числу в сводной таблице и выбираем раздел:
Дополнительные вычисления → % от общей суммы. В появившемся меню доступно несколько способов вычисления процентов:

а) % от общей суммы – рассчитывается к итоговой сумме, от «угла».

вычисления в сводных таблицах

Если переместить данные по Городам в область строк, а Товары в столбцы, мы увидим, что общий процент считается как по строкам, так и по колонкам, и сумма процентов равна 100%.

б) % от суммы по столбцу или по строке.
Если требуется рассчитать структуру продаж, например, только по Городам, выбираем % от суммы по столбцу. Если только по товарам, соответственно – по строке.

в) А если нужно видеть структуру продаж и по товарам, и по городам? Не проблема! Нужно выбрать % от суммы по родительской строке.
Тогда процент рассчитается от суммы группы, а не от общего итога. А сумма процентов внутри группы будет равна 100%.

вычисления в сводной таблице

Шаг 3. Все, конечно замечательно, НО хотелось бы рядом с процентами видеть суммы. И это тоже не проблема! Открою маленький секрет: в область значений сводной таблицы мы можем несколько раз перетащить один и тот столбец. Для этого просто захватываем мышкой нужное поле и несколько раз перетаскиваем его в область сумм.

несколько одинаковых столбцов в сводной

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

2. Продажи нарастающим итогом

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

Шаг 1. Постройте сводную таблицу. В строки поместите Города, в столбцы — Месяцы.

Шаг 2. Правой кнопкой мыши по любому числу, выберите Дополнительные вычисления → С нарастающим итогом в поле.

нарастающий итог в сводной таблице

Шаг 3. В открывшемся окне выбираем, что нарастание нужно по Месяцам и все готово!
Можно выбрать, относительно какого поля будет идти нарастание – строк и столбцов, городов или месяцев. В нашем случае выбран вариант нарастающего итога по месяцам. Кстати, столбец Общий итог пустой, потому что нарастающий итог рассчитан в декабре.

3. Темпы роста

Настроим отчет, в котором будут темпы роста, рассчитанные в сводной таблице.

Шаг 1. В новую сводную таблицу добавляем в строки Города, в столбцы Месяцы. В область значений – два одинаковых столбца Доходы.
Когда в области Значений появляется более двух полей, в столбцах появляется «виртуальное» поле «∑ Значения», которое определяет размещение данных в сводной таблице – по строкам или столбцам. Переместите «∑ Значения» в область строк.

Несколько одинаковых полей в сводной

Шаг 2. Щелкаем правой кнопкой мышки по числам одного из полей сводной таблицы и выбираем Дополнительные вычисления → Приведенное отличие. Указываем Базовое поле «месяцы», элемент – «назад».

Приведенное отличие в сводной таблице

Январь будет пустым, потому что перед ним нет других данных. Это место можно занять спарклайнами. Чтобы их добавить, перейдите в меню Вставка → Спарклайны → График.

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *