Файл: Практикум по дисциплине Технологии и продукты цифровой экономики Методические указания к лабораторным работам для студентов.pdf

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания

Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;

Дата поставки – Дата поставки Товара Поставщиком;

Регион продажи – Регион, в котором была реализована партия Товара;

Продажи – Стоимость, по которой удалось реализовать партию Товара;

Сбыт – срок фактической реализации Товара в Регионе (в днях);

Прибыль – отметка о том, была ли получена прибыль от реализованной партии
Товара.
Через Диспетчер имен (
Формулы/ Определенные имена/ Диспетчер имен
) откорректируем
Имя таблицы на «Исходная_таблица».
Создание Сводной таблицы
Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
Имея исходную таблицу в формате EXCEL 2007
, для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню
Работа с таблицами/
Конструктор/ Сервис
выбрать пункт Сводная таблица.
В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы).
Структура Сводной таблицы в общем виде может быть представлена так:

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Заполним сначала раздел Названия строк. Т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия
Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец - синонимы).
Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже
).
Теперь поставим галочку в Списке полей у поля Продажи.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.
Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью
Отбор уникальных значений с суммированием по соседнему столбцу
).
Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Детализация данных Сводной таблицы
Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Обновление Сводной таблицы
Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи
(строки), то эти данные не будут автоматически учтены в Сводной таблице. Чтобы обновить
Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню
Работа со
сводными таблицами/ Параметры/ Данные/ Обновить
. Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы, вызовите правой клавишей мыши контекстное меню и выберите пункт Обновить.
Удаление Сводной таблицы
Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ - удалить только саму Сводную таблицу: выделите любую ячейку
Сводной таблицы, нажмите

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому
Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы. Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт
Итоги по/ Количество
Изменение порядка сортировки
Теперь немного модифицируем наш Сводный отчет. Сначала изменим порядок сортировки названий Товаров: отсортируем их в обратном порядке от Я до А. Для этого через выпадающий список у заголовка столбца, содержащего наименования Товаров, войдем в меню и выберем Сортировка от Я до А.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Затем, нажав левую клавишу мыши, перетащите ячейку на самую верхнюю позицию в списке прямо под заголовок столбца.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.
Изменение формата числовых значений
Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи).
Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат…

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
В появившемся окне выберите числовой формат и поставьте галочку флажка
Разделитель групп разрядов.

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

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Выделив любое название Товара и нажав пункт меню
Работа со сводными
таблицами/ Параметры/ Активное поле/ Свернуть все поле
, можно свернуть Сводную
таблицу, чтобы отобразить только продажи по Регионам.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Добавление столбцов
Добавление поля Регион продажи в область строк привело к тому, что Сводная
таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Сводная таблица примет следующий вид.

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

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).
Добавление фильтра
Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню
Работа со сводными таблицами/ Параметры/ Действия/ Очистить/
Очистить все
;

Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;

Переносим поле Прибыль из области Названия строк Списка полей в область
Фильтр отчета;
Вид получившейся Сводной таблицы должен быть таким:

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле
Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
После нажатия кнопки ОК будут выведены значения Продаж только прибыльных
Партий.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Обратите внимание, что в Списке полей Сводной таблицы напротив поля Прибыль появился значок фильтра. Удалить фильтр можно сняв галочку в Списке полей.
Очистить фильтр можно через меню
Работа со сводными таблицами/ Параметры/
Действия/ Очистить/ Очистить фильтры
Также стандартный механизм фильтрации данных доступен через выпадающий список в заголовках строк и столбцов Сводной таблицы.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Несколько итогов для одного поля
Предположим, что требуется подсчитать количество проданных партий и суммарные продажи каждого Товара. Для этого:

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню
Работа со сводными таблицами/ Параметры/ Действия/ Очистить/
Очистить все
;

Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей.
Поле Продажи будет автоматически помещено в область Значения;

Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В
Сводной таблице появится 2 столбца подсчитывающими суммы продаж;

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
 в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт
Итоги по/ Количество
. Задача решена.

Министерство науки и высшего образования РФ
Ульяновский государственный университет
Форма
Ф-Методические указания
Отключаем строки итогов
Строку итогов можно отключить через меню:
Работа со сводными таблицами/
Конструктор/ Макет/ Общие итоги
. Не забудьте предварительно выделить любую ячейку
Сводной таблицы.
Группируем числа и Даты
Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню
Работа со сводными таблицами/ Параметры/ Действия/ Очистить/
Очистить все
;

Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область
Значения;