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

Оформим нашу таблицу, чтобы всё стало совсем понятно. Что нас интересует?

Общий заголовок «Сводная таблица» мало что сообщает читателю.

Остальные названия ещё лучше:

— Column Labels;

— Row Labels;

— Grand Total.

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

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

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

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

Так что лучше прямо и честно сказать, где у нас города и где у нас товары (рис.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. Две меры

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

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

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

Сократим заголовки, как мы сделали в предыдущих примерах.

Удаляем итоги по строкам: Remove Grand Total.

Получаем приемлемый вид таблицы (рис. 6.13). По каждому городу имеем две колонки с показателями.

Рис. 6.13. Два показателя

Задание. Настройте оформление сводной таблицы.

Построим ещё один вариант сводной таблицы. Теперь вычислим два разных показателя — сумму и среднее по одному и тому же полю. Перетаскиваем поле «Количество товара кг» в раздел Values(Значения) два раза. Первоначально оба раза автоматически выбирается сумма. Изменим второй показатель на среднее (рис. 6.14).

Кстати говоря, в разделе Columnsкроме названия города появились заголовки — Values. То есть наши два показателя.

Рис. 6.14. Два показателя по одному полю

Настроим оформление таблицы.

Устанавливаем формат вывода:

— сумма — с точностью до целых;

— среднее — с точностью до сотых.

Делаем короткие заголовки (рис. 6.15).

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

Задание. Настройте оформление таблицы.

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

Создаём новую сводную таблицу. Перетаскиваем поле «Количество товара» в раздел Valuesдва раза. Получаем две суммы (рис. 6.16).

Рис. 6.16. Две суммы

Теперь настроим вычисление процентов. Пусть это будут проценты по строке. В нашем случае это процент количества товара в каждой категории. Процент в общем количестве данной категории товара по всем городам торговой сети.

Щёлкаем правой кнопкой по любой ячейке во второй колонке сумм. Выбираем пункт Value Field Settings. В диалоговом окне переходим на вкладку Show Values As. Здесь у нас есть целый список самых разных процентов. Вот примеры: