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

Щёлкаем по сводной таблице. В верхнем меню появляется раздел PivotTable Tools.Выбираем пункт меню:

PivotTable Tools — Analyze — Calculations — Fields, Items & Sets — Calculated Field.

На экране появляется диалоговое окно

Insert Calculated Field(рис. 6.65).

В строке Nameзадаём название нашему вычисляемому полю: «Прирост».

В строке Formulaудаляем ноль.

В списке полей Fieldвыделяем «Кол кг» и нажимаем кнопку Insert Field.

Рис. 6.65. Вычисляемое поле

Всё хорошо, но только в этом случае мы не сможем найти разность двух соседних колонок, если это один и тот же итоговый показатель. То есть мы не можем взять «Кол кг» за 2019 год и вычесть из него «Кол кг» за 2018 год.

Зато можно построить сводную таблицу с двумя итоговыми показателями-мерами. Например, общее количество и общая стоимость товаров. А вычисляемое поле будет находить среднюю цену товара (рис. 6.56).

Для упрощения таблицы удалим сумму по годам и итоги по федеральным округам. Щёлкаем правой кнопкой мыши по заголовку общих итогов по годам Grand Totalи выбираем в контекстном меню Remove Grand Total.Щёлкаем правой кнопкой по заголовку строки ФОи в контекстном меню снимаем выделение пункта Subtotal ФО.Настраиваем заголовки.

Рис. 6.66. Вычисляемое поле

Задание. Постройте описанную выше таблицу и создайте вычисляемый столбец средних цен.

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

Задание. Прочитайте в Википедии статью «Спарклайн» и выясните, для чего можно использовать спарклайны.

Приступим к работе с мини-графиками.

Выбираем в ленте пункт Insert. Рассмотрим раздел Sparklines. У нас есть выбор — три типа мини-графика (рис. 7.1):

— Line — График в виде ломаной линии;

— Column — Столбиковая диаграмма;

— Win/Loss — Столбиковая диаграмма положительных и отрицательных значений.

Рис. 7.1. Вставка спарклайна

Задание. Подведите курсор к кнопке вставки спарклайна и прочитайте всплывающую подсказку.

Начнём с первого типа мини-графика.

Нажимаем на кнопку Line.

На экране появляется диалоговое окно

Create Sparkline(рис. 7.2).

Указываем диапазон ячеек для исходных данных:

Choose the data that you want — Data Range.

В нашем примере это квартальные данные по продажам.

Выбираем адрес ячейки для вывода мини-графика:

Choose where you want the sparklines to be placed — Location Range.

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

Нажимаем ОК.

Рис. 7.2. Вставка спарклайна

Получаем мини-график в одной ячейке.

Мы выбрали для вывода графика ячейку с итоговым показателем. Поэтому линия накладывается на цифры. Это не очень удобно. Немного растянем ячейку в ширину. Теперь видно и график, и цифры (рис. 7.3).

Настраиваем спарклайны по остальным строкам.

Рис. 7.3. Один спарклайн

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

Мы построили «линейные» графики по строкам. Это была зависимость от времени. Поэтому здесь был смысл в том, чтобы соединить соседние точки линиями. Ведь это ДИНАМИКА — изменение значений показателя во времени.

Данные для наших миниатюрных графиков могут располагаться как по строкам, так и по столбцам. В нашей таблице столбцы — это разбивка по федеральным округам.

Для сравнения таких показателей по территориям, то есть в пространстве, лучше подойдёт второй тип спарклайна — график в виде столбиков.

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

Insert — Sparklines — Column.

Указываем диапазон исходных данных и ячейку для вывода графика.

Растягиваем график по высоте, чтобы лучше его рассмотреть.

По умолчанию построены столбики синего цвета. Они сливаются с цифрами. Настроим цвет диаграммы. Выбираем подходящий цвет в разделе Style.

Черные цифры на жёлтом фоне читаются нормально (рис. 7.4).

Рис. 7.4. Столбиковый спарклайн

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

Мы справились с одним столбцом. Если так работать с большим количеством столбцов, это будет слишком долго. Конечно, хочется как-то ускорить работу.

Мы можем одним махом построить целую группу графиков.

Вызываем вставку столбикового спарклайна.

Указываем в качестве исходных данных все столбцы.

А в качестве ячеек для вывода графика — все итоговые ячейки.

В результате программа вставляет сразу всю группу спарклайнов.

Нам осталось только настроить цвет столбиков — и всё готово (рис. 7.5).

Рис. 7.5. Вставка группы спарклайнов

Задание. Вставьте группу спарклайнов по всем колонкам.

Мы вставили спарклайны — вначале как одиночные графики, затем как группу. Здесь разница не только в скорости работы. Рассмотрим различия поподробнее.

Щёлкнем по мини-графику в строке, а затем в столбце (рис. 7.6). В первом случае выделяется одна ячейка, во втором — вся группа.

Рис. 7.6. Одиночный мини-график и группа

Задание. Щёлкните по спарклайнам и обратите внимание на разницу в выделении одиночного графика и группы.

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

Выделяем одиночные спарклайны, построенные по строкам.

В разделе Sparkline Toolsвыбираем пункт

Design — Group — Group(рис. 7.7).

Теперь щёлкаем по любому из этих спарклайнов и видим выделение всей группы.

Рис. 7.7. Группировка спарклайнов

Задание. Сгруппируйте спарклайны, построенные по строкам.

Точно так же мы можем разгруппировать спарклайны.

Щёлкаем по любому из мини-графиков, построенных по столбцам.

Нажимаем кнопку Ungroup.

Щёлкаем по любому другому графику из этой группы и наблюдаем результат (рис. 7.8).

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

Получается, что в результате разгруппировки из состава нашей группы вышел один график. А вот все остальные по-прежнему состоят в одной группе.

Рис. 7.8. Попытка разгруппировки

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