Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 1

Обычно в таких случаях говорят: «Всего» или «Итого». А знаете ли вы, в чём разница между всего и итого? Если нет, пора позвать на помощь Яндекса или Гугла. Пусть поищут. Не мы первые такой вопрос задаём.

Задание. Выясните, в чём разница между ВСЕГО и ИТОГО.

Итак, заголовки. Мы как авторы знаем, где что лежит. А для читателя это сказать открытым текстом, без намёков и действий по умолчанию. Совсем не факт, что он «сам догадается».

Так что лучше прямо и честно сказать, где у нас города и где у нас товары (рис.6.9).

Рис. 6.9. Оформление таблицы

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

Далее в том же диалоговом окне мы видим закладку

Summarize Values By

Вычислять итоги с помощью.

Под ней имеется раздел

Summarize value field by.

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

Перед нами более десятка вариантов:

— Sum(Сумма числовых значений);

— Count(Количество заполненных ячеек);

— Average(Среднее значение);

— Max(Максимальное значение);

— Min(Минимальное значение);

— Product(Произведение значений);

— Count numbers(Количество ячеек, в которых записаны числа);

— StdDev(Выборочное стандартное отклонение);

— StdDevp(Генеральное стандартное отклонение);

— Var(Выборочная дисперсия);

— Varp(Генеральная дисперсия).

Задание. Рассмотрите список способов вычисления итогов и сравните с учебником по теории статистики. Выясните, какие статистические показатели не включили сюда разработчики.

В списке показателей третьим пунктом идёт «Среднее значение». Скорее всего, это то, что в статистике называется «средняя арифметическая простая». Вряд ли программисты знают остальные ДЕСЯТЬ способов расчёта среднего. Здесь всё по-простому. По рабоче-крестьянски. Мы, как говорится, «академиев не кончали».

Познакомимся со средним значением. Сделаем новую сводную таблицу. Пусть теперь она вычисляет среднее. Ну и, конечно, оформим её как следует.

Чтобы облегчить свою задачу, просто скопируем весь лист и поместим его в конец списка. Нажимаем правой кнопкой мыши на вкладку рабочего листа (внизу окна Excel) и выбираем в контекстном меню Move or Copy. Затем Move to Endи Create a Copy.

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

Pivot Table Fields — Values — Value Field Settings — Summarize value field by — Average — OK.

Переименуем заголовок.

Теперь формат вывода. Числа длинные. Супер-точные. Нам достаточно два знака после запятой. Щёлкаем по любой ячейке внутри сводной таблицы, нажимаем правую кнопку мыши и обнаруживаем, что вместо пункта Format Cellsу нас теперь появился знакомый пункт Value Field Settings. Нажимаем кнопку Number Format.

Наконец-то мы добрались до Format Cells. Устанавливаем числовой формат и два знака после запятой (рис. 6.10). Нажимаем ОК. Теперь выбранный формат применится не только к выбранной ячейке, но и ко всем ячейкам с нашим итоговым показателем.

Рис. 6.10. Средние значения

Таблица готова (рис. 6.11). Числа выводятся на экран короткие, приятные для глаза. Но это не округление. Внутри ячеек по-прежнему точные значения. Их всегда можно посмотреть в строке формул.

На вкладках тоже пусть будет написано что-то осмысленное. Потом, в конце работы всё переименуем в числа — для оглавления.

Рис. 6.11. Средние значения

Задание. Сделайте сводную таблицу с расчётом среднего.

Мы рассмотрели подсчёт итогов по количеству товара в килограммах. Теперь поиграем с рублями. Сделаем новые сводные таблицы. Пусть теперь у нас программа подведёт итоги по стоимости товара в рублях. Пусть это будут итоги в виде суммы и в виде среднего. Пока сделаем это в двух отдельных таблицах.

Задание. Создайте две новые сводные таблицы с расчётом суммы и среднего для стоимости товара.

Попробуем построить сводную таблицу, в которой будут две меры. Грубо говоря, сумма килограммов и сумма рублей. Как и в предыдущих примерах, настройки такие:

— города — по столбцам;

— категории — товаров по строкам.

В раздел Valuesперетаскиваем «Количество товара кг» и «Стоимость товара руб» (рис. 6.12).

Рис. 6.12. Две меры

Задание. Постройте сводную таблицу с двумя мерами.

Получаем довольно громоздкую таблицу.