Практическая часть

Постановка задачи

Исходные данные приведены в табл. 1.

Требуется создать консолидирующие таблицы по продавцам, номенклатуре товара, видам товаров; сводную таблицу; таблицу расшифровки оборота товара за день; таблицы подстановки с одной и двумя переменными.

Таблица

Исходные данные задачи

ФИО	Товар	Выручка	Дата
Иванов П.И.	Стул	100,00р.	01.01.2005
Петров И.В.	Стол	150,00р.	03.01.2005
Короблев А.Л.	Пуфик	500,00р.	10.01.2005
Иванов П.И.	Стул	600,00р.	15.01.2005
Петров И.В.	Пуфик	1 500,00р.	15.01.2005
Короблев А.Л.	Стул	250,00р.	15.01.2005
Иванов П.И.	Стул	50,00р.	20.02.2005
Петров И.В.	Стол	100,00р.	20.02.2005
Короблев А.Л.	Стул	180,00р.	25.02.2005
Иванов П.И.	Стол	450,00р.	03.02.2005
Петров И.В.	Стол	1 600,00р.	03.02.2005
Короблев А.Л.	Пуфик	560,00р.	10.03.2005
Иванов П.И.	Стул	150,00р.	10.03.2005
Петров И.В.	Стол	400,00р.	20.03.2005
Короблев А.Л.	Стул	100,00р.	21.03.2005

Порядок выполнения работы

1. Занесем исходные данные в таблицу. Затем следует установить курсор на свободное место, так чтобы снизу и справа было свободное пространство для формирования консолидирующей таблицы.

2. На рис. 1 изображено окно Консолидация (вкладка Данные). В поле Функция можно выбрать одну из доступных обобщающих функций. Чаще всего это функция Сумма. Затем в разделе Ссылка нужно задать блок, для которого выполняется анализ A1:D16. После этого следует нажать кнопку Добавить – координаты выделенного блока переместятся в окошко Список диапазонов. В случае потребности изъять блоки используется кнопка Удалить. В разделе Использовать в качестве имен флаг Подписи верхней строки и значений ключевого поля и флаг Значения левого столбца определяют, указывать или нет в консолидирующей таблине названия колонок. Флаг Создавать связи с исходными данными используется для установления связи с таблицами-источниками, находящимися на других листах/книгах.

Рис. 1. Окно «Консолидация»

Рис. 2. Итоги консолидации по продавцам

Результат консолидации приведен на рис. 2. Видим, что каждый сотрудник представлен одной строкой, где в колонке Выручка отображена сумма всех его продаж. Колонка Товар не содержит никаких данных, т.к. это текстовое поле, а колонка Дата содержит некоторые бессмысленные даты. Это сумма всех дат продаж в формате Дата, ведь даты в Excel – это числа, и они могут суммироваться. Колонки Товар и Дата впоследствии нужно удалить.

3. Если нас интересует выручка не по продавцам, а по номенклатуре товаров, следует создать копию исходного листа и сделать так, чтобы самой левой ключевой колонкой была колонка Товар. Попутно не будем включать колонку Дата. Таким образом, в разделе Список диапазонов окна Консолидация укажем диапазон В1:С16. Результат показан на рис. 3.

4. Выполним консолидацию нескольких (трех) таблиц по видам товаров. Исходный материал представлен на рис. 4. Здесь и поле Ссылка окна Консолидация необходимо последовательно указать и Добавить три консолидируемых диапазона В1:С6, B8:C13, В15:С20.

Результат будет выглядеть аналогично рис. 3.

Рис. 3. Итоги консолидации по номенклатуре товара

Рис. 4. Исходные данные для консолидации нескольких таблиц

Рис. 5. Сводная таблица

1. Создадим сводную таблицу на основе исходных данных, используя Мастер сводных таблиц (Вставка à Таблицы) (рис. 5).

1. Создадим таблицу расшифровки оборота товара за день. Щелкаем по значку Развернуть все поле (Работа со сводными таблицамиàПараметры àАктивное поле) . В результате на экране будет открыто диалоговое окно (рис. 6), в котором необходимо выделить для показа детали. Выберем Товар.

Рис. 6. Окно «Детали»

Рис. 7. Таблица расшифровки обработка товара за день Рис. 8. Панель управления сводными таблицами

2. Далее рассмотрим панель управления сводными таблицами (рис. 8, табл. 2).

Таблица 2

Функции кнопок панели управления сводными таблицами

Кнопка	Функция
Сводная таблица	Открывает меню управления сводной таблицей
Формат отчёта	Открывает доступ к имеющимся в Excel шаблонам таблиц, из которых можно выбрать желаемых для создания отчёта.
Мастер диаграмм	Позволяет построить диаграммы для сводных таблицы
Мастер сводных таблиц	Вызывает экран третьего завершенного шага построения сводных таблиц для модификации сводной таблицы
Показать детали	Предъявить окно с перечнем полей, в котором можно выбрать одно и показать для него все данные
Скрыть детали	Сворачивает детали
Обновить данные	Обновляет содержимое сводной таблицы, если были изменены данные в исходной таблице
Параметры поля	Открываем окно Вычисление поля свободной таблицы, где можно указать виды итогов, которые желают получить пользователь, а также уточнить внешний вид сводной таблицы
Скрыть / отобразить поля	Скрывает / отобразить для пользователя нижнюю часть панели, содержащую перечень кнопок-полей, которые, при желании, можно отбуксировать в сводную таблицу, изменив тем самым ее структуру

3. Если нужно увидеть сводные данные, не расписанные по конкретным датам, а сгруппированные по месяцам, нажмем правую кнопку мыши на любой клетке, содержащей дату. В появившемся меню выберем пункт Группа и структура, а в меню следующего уровня – пункт Группировать. Он вызовет окно диалога (рис. 9). В разделе Авто в исходном состоянии оба флага будут установлены и справа от них указаны минимальное и максимальное значения дат, найденных в сводной таблице. Если требуется ограничить диапазон сводимых дат, следует сбросить флаги и ввести новые начальную и/или конечную даты. Список выбора (раздел с шагом) допускает отбор сразу нескольких значений. Если, например, выбрать пункты Годы, Кварталы и Месяцы, то Excel построит структуру, где все продажи будут последовательно сгруппированы по этим категориям. Мы укажем Месяцы (рис. 10). Переключатель Количество дней позволяет установить группировку по желаемому числу дней, например по пятидневкам. Эта возможность открывается только в случае выбора пункта Д ни.

Рис. 9. Окно «Группирование»

Рис. 10. Результаты группировки по месяцам

Рис. 11. Окно «Вычисление поля сводной таблицы»

4. Если дважды щелкнуть на кнопке-поле или выбрать из контекстного меню пункт Поле, будет предъявлено окно Вычисление поля сводной таблицы (рис. 11). Здесь выбрано поле Товар. В разделе Итоги пользователь может выбрать сводную функцию. Кнопка Удалить удаляет соответствующее поле из сводной таблицы. Кнопка Далее откроет доступ к интерфейсу сортировки. Кнопка Макет (если она доступна) откроет средства дальнейшего управления отображением структуры таблицы. В зависимости от того, какое поле было выбрано, вид окна Вычисление поля сводной таблицы может быть и иным.

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

Рис. 12. Окно «Параметры сводной таблицы»

Прежде всего, здесь можно задать собственное имя сводной таблице (по умолчанию она получает имя СводнаяТаблица1). В разделе Формат указываются параметры, определяющие внешний вид таблицы (табл. 3).

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

Таблица 3

Значения клавиш окна Параметры сводной таблицы

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

6. Используем сводные таблицы для консолидации. Построим сводную таблицу. Вставим в книгу рабочий лист «Сводка». Выделим ячейку А1 и вызовем Мастер сводных таблиц. Установим переключатель В нескольких диапазонах консолидации. Установим второй переключатель Создавать одно ноле страницы. Далее укажем диапазоны консолидации по полной аналогии с диалоговым окном Консолидация. Сконструируем макет сводной таблицы.

В диалоговом окне отсутствуют поля сводной таблицы, а вместо них имеются Строка, Столбец, Значение, Страница (рис. 13). В нашем случае настройку макета можно не делать, но посмотрим, как его настроить. Сделаем двойной щелчок мышью по кнопке Столбец (расположенной в макете, а не сбоку). Получим диалоговое окно Вычисление поля сводной таблицы. Здесь можно указать, вычислять ли промежуточные итоги. В последнем диалоговом окне нажмем кнопку Готово. На текущий рабочий лист будет выведена сводная таблица (рис. 14).

Рис. 13. Окно «Мастер сводных таблиц – макет»

Рис. 14. Сводная таблица консолидации

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

7. Создадим таблицу подстановки с одной переменной. Пользуясь исходной таблицей начисления процентов, определим с помощью функции создания таблицы подстановки суммы выплат для разных значений процентной ставки и срока вклада при заданной сумме вклада. Для этого следует создать список значений процентных ставок (рис. 15). Введем в ячейки формулы: G5=(1+C8)^C6, H5=C10*C4. Формулы идентичны содержащимся в ячейках С10 и С4

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

Для построения таблицы подстановки выделим диапазон ячеек F2:H16. После выберем в меню Данные–Анализ «что-если»-Таблица данных (рис. 15). Поместим курсор ввода в поле Подставлять значения по строкам в диалоговом окне Таблица подстановки. Выделим ячейку В3 в поле ввода и нажмем кнопку ОК. Для построения таблицы подстановки была использована следующая формула: {ТАБЛИЦА(;В3)}. Вычисленный результат представлен в таблице подстановки (рис. 16).

8. Создадим таблицу подстановки с двумя переменными. В качестве переменных используем процентную ставку и срок вклада, а в качестве результата будет выступать сумма выплат для различных сроков при различных процентных ставках. Необходимо создать копию и изменить нашу таблицу. Затем удалим содержимое ячеек G5:H19 и введем в ячейки следующие данные: F2=((1+С8)^С6)*С4, C2=5, H2=10, I2=15, J2=20. В ячейке F2 представлена формула для вычисления коэффициента наращения и суммы выплат. В остальных ячейках введены значения различных сроков вклада, которые будут использоваться в качестве значений второй переменной.

Выделим диапазон ячеек F2:I17 и выберем команду Таблица данных из меню Данные. Для значений процентных ставок отводится ячейка С8, адрес которой необходимо указать в поле Подставлять значения по строкам в. В поле Подставлять значения по столбцам в следует выделить адрес ячейки С6 с указанием срока вклада (рис. 17).

Рис. 16. Таблица подстановки с одной переменной (вычисленные результаты)

Рис. 17. Построение таблицы подстановки с двумя переменными

Рис. 18. Таблица подстановки с двумя переменными (вычисленные результаты)

После активизации кнопки ОК в рабочем листе появится таблица подстановки с вычисленными результатами (рис. 18).

При этом диапазон ячеек G5:I17 будет содержать формулу =ТАБЛИЦА(С6;С8). В этом случае в формуле должны быть указаны два аргумента. Первый аргумент представляет собой ссылку на ячейку, в которую подставляются значения из первой строки выделенного диапазона ячеек. Вторым аргументом служит ячейка, в которую будут подставлены значения из левого столбца этого диапазона ячеек.

Задания для самостоятельной работы

Вариант 1

Создать исходную таблицу со следующими столбцами: страна (РФ, Украина, Белоруссия), город, плательщик (ФИО), вид платежа (наличные, банковский перевод), сумма платежа, дата оплаты. В таблице должно быть не менее 12 строк. Сделать консолидацию по суммам платежей стран, по средним суммам банковских и наличных платежей, разбить на несколько таблиц и выполнить консолидацию по платежам городов. Создать сводную таблицу и осуществить группировку данных по месяцам, попробовать сделать другие операции со сводными таблицами, выполнить консолидацию всей таблицы с использованием сводной таблицы. Изменив в исходных данных (см. рис. 15) сумму вклада, срок вклада, процентную ставку, создать таблицы подстановки с одной и двумя переменными.

Вариант 2

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

Вариант 3

Создать исходную таблицу со следующими столбцами: экспедитор (фамилия), автомобиль (ГАЗ, КамАЗ, ЗИЛ), вид товара, расфасовка (контейнеры, бочки, канистры), стоимость перевозки, дата перевозки. В таблице должно быть не менее 12 строк. Сделать консолидацию по суммам перевозки товара, по средним суммам перевозок автомобилей, разбить на несколько таблиц и выполнить консолидацию по суммам в зависимости от расфасовки товара. Создать сводную таблицу и осуществить группировку данных по месяцам, попробовать сделать другие операции со сводными таблицами, выполнить консолидацию всей таблицы с использованием сводной таблицы. Изменив в исходных данных (см. рис. 15) сумму вклада, срок вклада, процентную ставку, создать таблицы подстановки с одной и двумя переменными.

Вариант 4

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

Вариант 5

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