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

4.1. Создайте сводную таблицу, которая выводит суммарные данные по наименованию и денежному потоку, и соответствующую диаграмму.

Для создания сводной таблицы:

- выберите ленту Вставка, пункт «Сводная таблица»;

- переключатель (селектор) «Выбрать таблицу или диапазон» в разделе анализа оставить активным; переключатель размещения сводной таблицы также оставляем активным в значении «На новом листе» (см. рис. 8);

- исходный диапазон – выделяем таблицу от шапки (включительно) до итоговой части (не включая);

- нажмите на кнопку ОК.

Рис. 8 – Первый этап создания сводной таблицы

На втором этапе создания сводной таблицы необходимо заполнить макет сводной таблицы, выделяя флажки заданных полей и при необходимости перемещая поле меток между разделами макета Строки / Столбцы.

Раздел Данные - это та часть макета, которая должна быть заполнена обязательно, потому что именно в ней над данными выполняется итоговая операция сведения данных (по умолчанию это сумма). Другие три части макета - Строки, Столбцы, Фильтры - заполняются при необходимости и по выбору пользователя (хотя бы одно из них необходимо заполнить для информативности сводных данных).

Активизируйте флажки полей «Наименование товара» и «Денежный поток», согласно заданию (см. рис. 9). Сводная таблица готова.

Нажмите на кнопку «Сводная диаграмма» ленты Вставка.

Рис. 9 – Работа с макетом сводной таблицы

Переместите сводную диаграмму, растяните до оптимального размера (см. рис. 10). Проверьте работу фильтров сводной таблицы и диаграммы, синхронность изменения отображаемых данных с изменениями в фильтрах, проверьте обновление сводной таблицы и диаграммы при обновлении данных исходной таблицы.

Рис. 10 – Сводная таблица и диаграмма по наименованиям товаров и денежному потоку

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

Для создания второй сводной таблицы:

- вернитесь на лист исходной таблицы (активизируйте лист с помощью ярлыка);

- аналогичным образом вставьте сводную таблицу на основе исходной на новый лист;

- учитывая задание, активизируйте флажки полей «Наименование товара», «Дата операции» и «Денежный поток» в макете сводной таблицы. Поле «Дата операции» перетащите в макете в раздел «Столбцы», для получения оптимального вида сводной таблицы;

- кнопку поля «Дата операции» можно разместить как в области столбца, так и в области фильтра. Если разместить в области фильтра, будет создан на листе фильтр по полю дата. За счет этого такая таблица будет компактнее и удобной (позволяет отображать в таблице и соответственно диаграмме только значение за нужную дату). Но в данном примере нагляднее располагать поле «Дата операции» в разделе «Столбцы».

- для того, чтобы изменить итоговую операцию над данными (по умолчанию это функция суммы), вызовите пункт меню «Параметры полей значений» по кнопке поля в области данных «Сумма по полю Денежный поток». На экране появится диалоговое окно, в котором можно выбрать итоговую функцию из предложенных, выберите «Среднее» (см. рис. 11). В этом же диалоговом окне можно настроить числовой формат вычисляемых значений, выберите «Денежный» с отображениями отрицательных сумм красным цветом.

Рис. 11 – Сводная таблица по наименованию, дате операции и среднему денежному потоку

Нажмите на кнопку «Сводная диаграмма» ленты Вставка, разместите диаграмму оптимально. Проверьте работу фильтров.

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

5.1. Постройте круговую диаграмму:

- выделите диапазон для круговой диаграммы B3:E23 (данные трех столбцов - наименование товара, дата и вид операции - будут служить метками секторов, а данные столбца количество единиц товара будет задавать величины секторов);

- вставьте с помощью кнопки ленты Вставка объемную круговую диаграмму;

- на ленте конструктор нажмите на кнопку Переместить диаграмму (см. рис. 12);

Рис. 12 – Построение аналитической диаграммы по количеству товаров

- с помощью меню «Элементы диаграммы» + уберите легенду, выведите подписи значений данных, с долей, категорией и линиями выноски (см. рис. 13);

- измените название диаграммы на «Анализ количества товара по наименованиям, датам поступления и виду операции.

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

Рис. 13 – Настройка круговой диаграммы

5.2. На ленте Конструктор нажмите на кнопку Выбрать данные, выберите в исходной таблице значения и имя столбца Сумма (см. рис. 14).

Рис. 14 – добавление на диаграмму столбца Сумма

Нажмите на кнопку ОК.

На диаграмме столбец Сумма не отобразился, потому что круговая диаграмма может анализировать только один ряд данных. Нажмите на ленте Конструктор кнопку Изменить тип диаграммы. Выберите вид диаграммы Кольцевая, дополнительно настройте метки значений обоим кольцам: значения, выведите легенду на диаграмму (см. рис. 15).

Рис. 15 – Кольцевая сравнительная диаграмма количества и суммы

5.3. Постройте сравнительную диаграмму комбинированного типа:

- выделите диапазон для гистограммы B3:E23; Н3:Н23 (необходимо во время выделения не смежного столбца держать нажатой клавишу Ctrl);

- на ленте Вставка выберите комбинированную диаграмму (см. рис. 16);

- аналогично предыдущим алгоритмам построения диаграмм, переместите диаграмму на отдельный лист;

- внесите название диаграммы «Сравнительный анализ количества и суммы по наименованию товаров, дате и виду операции».

Рис. 16 – Создание комбинированной диаграммы

Пояснение: так как выбранные для сравнения данные имеют разный порядок (10² и 10⁵) и разный формат - числовой и денежный, они не могут быть корректно отображены в одной координатной плоскости стандартных диаграмм, поэтому необходима диаграмма комбинированного типа со вспомогательной осью.

В дополнительных параметрах горизонтальной оси настройте оптимальное отображение меток. Для этого отключите фладок Многоуровневые подписи категорий (см. рис. 17) и отключите расположение текста подписей вертикально в параметрах текста на ленте Главная. Диаграмма готова.

Рис. 17 – Настройка подписей горизонтальной оси