Сводная таблица – это таблица, которая позволяет быстро подвести итоги, объединить большие объемы данных и, меняя местами строки и столбцы, получать итоговые данные в различных разрезах.
Сводную таблицу можно создавать на основе данных, находящихся в списке, нескольких листах рабочей книги, во внешней базе данных, а также в другой сводной таблице.
В качестве исходных данных рассмотрим таблицу, представленную на рис. 67.
Рис. 67. Таблица исходных данных
Для создания сводной таблицы курсор устанавливается в то место, где будут отражены результаты (свод) и вызывается Мастер сводных таблиц (рис. 68). На шаге 1 необходимо указать, где находятся исходные данные, и что будет создаваться (сводная таблица или сводная диаграмма).
Рис. 68. Выбор источника данных и типа создаваемого отчета
На шаге 2 указываем диапазон, содержащий исходные данные (рис. 69).
Рис. 69. Указание диапазона исходных данных
На шаге 3 указываем лист и адрес левого верхнего угла сводной таблицы (рис. 70).
Рис. 70. Указание листа и адреса левого верхнего угла сводной таблицы
На этом шаге мы должны создать макет сводной таблицы. В правой части макета представлены метки полей из исходной таблицы. Перетащим поля Год и Продавец в область Строкаа поле Район в область Столбец. Получим вариант макета сводной таблицы, приведенной на рис. 72.
Рис. 72. Вариант макета сводной таблицы
Нажав кнопку ОК а затем кнопку Готово, получим сводную таблицу, приведенную на рис. 73.
Рис. 73. Разметка макета сводной таблицы
Нажимая на кнопки, расположенные рядом с полями Год, Продавец, Район, мы можем убрать из таблицы или добавить в нее отдельные значения этих полей. Так, убрав из перечня районов значения Северный и Южный, получим следующую сводную таблицу (рис. 74)
Рис. 74. Результирующая сводная таблица
Использование математических формул в электронных таблицах позволяет представить взаимосвязь между различными параметрами некоторой реальной системы. Основное свойство электронных таблиц — мгновенный пересчет формул при изменении значений входящих в них операндов. Благодаря этому свойству, таблица представляет собой удобный инструмент для организации численного эксперимента и решения задач «что-если». При этом используются такие встроенные в Microsoft Excel, как подбор параметра, ведение сценариев, поиск решения.
1.7.1 Решение задач «что-если» путем Подбора параметра
Зачастую Вы знаете тот результат, который нужно получить с помощью вычисления по формуле, однако входное значение, необходимое для получения этого результата, Вам неизвестно. Чтобы решить эту задачу, можно воспользоваться подбора параметра. С помощью подбора параметра Excel варьирует значение в заданной ячейке до тех пор, пока вычисление по формуле, зависящей от этой ячейки, не даст нужный результат. Подбор параметра позволит избежать проведения анализа методом проб и ошибок, на который уходит слишком много времени. Рассмотрим пример расчета выручки от продаж, представленный на рис. 74.
Рис. 74. Расчет выручки от продаж
Пусть требуется определить такую цену изделия 3, при которой доля выручка этого изделия составит 30 % от общей стоимости продаж. Для решения этой задачи применим инструмент «Подбор параметра».
1. Выберите команду Подбор параметра в меню Сервис. На экране появится диалоговое окно Подбор параметра (рис. 75.)
Рис. 75. Диалоговое окно Подбор параметра
2. В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу (адрес доли выручки от продаж - $E$8).
3. Введите искомый результат в поле Значение (30).
4. В поле Изменяя значение ячейки введите ссылку на ячейку, содержащую подбираемое значение (Цена изделия 3 - $В$8).
После выполнения расчета цена изделия 3 будет 3374.99 руб. (рис. 76).
Рис. 76. Результаты подбора параметра
1.7.2 Ведение сценариев «что-если».
Средства Excel позволяют создавать и сохранять в виде сценариев наборы входных значений, приводящих к различным результатам. Сценарий – это множество входных значений, называемых изменяемыми ячейками, которое сохраняется под указанным Вами именем. Каждому набору изменяемых ячеек соответствует набор предположений “что-если”, который Вы применяете к расчетной модели, чтобы проследить, как значения изменяемых ячеек влияют на другие значения модели. Для каждого сценария можно определить до 32 изменяемых ячеек.
Диспетчер сценариев Microsoft Excel позволяет выполнять следующее:
· создавать составные сценарии с множествами наборов изменяемых ячеек;
· просматривать результаты применения каждого сценария на рабочем листе;
· создавать итоговый отчет по всем входным значениям и результатам;
· объединять сценарии из одной группы в единую модель сценариев;
· защищать сценарии от модификации и скрывать их;
· отслеживать модификации с помощью автоматического ведения истории сценария.
Для примера сделаем таблицу расчета ипотечной ссуды (рис. 77).
Рис. 77. Таблица расчета ипотечной ссуды
Расчетные формулы в ячейках С10:С13 приведены на рис. 78.
Для наглядности итогового отчета присвоим изменяемым ячейкам и ячейкам результатов имена. Для этого в меню Вставкавыберем подменю Имя и команду Присвоить. На экране появится окно диалога Присвоение имени (рис. 79).
Рис. 79. Окно диалога Присвоение имени
В качестве имен Excel предлагает слова, стоящие в столбце слева от этих ячеек. Эти имена нас устраивают. Так, ячейке С6 присваиваем имя Срок_погашения_месяцы, С7 – Процентная_ставка, С11 – Месячная_плата, С12 – Общая_сумма, С13 – Сумма_комиссионных.
Для рассмотрения различных вариантов погашения ссуды создадим сценарии, где при изменении срока погашения ссуды и процентной ставки, будут пересчитываться месячная плата на погашение ссуды, общая сумма выплат и сумма комиссионных банка.
1. Выберите команду Сценарии в меню Сервис. Появится окно диалога Диспетчера сценариев (рис.80).
Рис. 80. Окно диалога Диспетчер сценариев
2. Нажмите кнопку Добавить.
На экране появится диалоговое окно Изменение сценария (рис. 81).
Рис. 81. Диалоговое окно Изменение сценария
3. Введите необходимое имя в поле Название сценария.
4. Введите ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки (рис. 81).
Нажмите кнопку OK.
На экране появится диалоговое окно Значение ячеек сценария (рис. 82).
Рис. 82. Диалоговое окно Значения ячеек сценария
6. Введите необходимые значения в диалоговом окне Значения ячеек сценария.
7. Чтобы создать сценарий, нажмите кнопку OK.
Для создания дополнительных сценариев нажмите кнопку Добавить, а затем повторите шаги с 3 по 7. После завершения создания сценариев нажмите кнопку OK, а затем – кнопку Закрыть в диалоговом окне Диспетчер сценариев.
После ввода всех сценариев можно провести расчеты и создать итоговый отчет. Для этого:
1. Выберите команду Сценарии в меню Сервис.
2. Нажмите кнопку Отчет.
На экране появится диалоговое окно Отчет по сценарию (рис. 83).
Рис. 83. Диалоговое окно Отчет по сценарию
3. Установите переключатель в положение Структура.
4. В поле Ячейки результата введите ссылки на ячейки, значения которых были изменены с помощью сценариев.
