Анализ и оптимизация

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

Консолидация данных

Выполняется в том случае, если необходимо подытожить данные, расположенные в разных областях таблицы.

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

После вызова Данные®Консолидация в диалоговом окне нужно выбрать необходимую функцию, затем перейти в поле Ссылка и указать координаты первого диапазона ячеек. После этого щелкнуть на кнопке Добавить, вследствие чего ссылка появится в поле Список диапазонов. Адреса остальных диапазонов вводятся аналогично. После ввода всех адресов надо установить опцию значение левого столбца в области Использовать в качестве имен, затем нажать ОК. При этом значения в строках с одинаковыми метками будут просуммированы. Чтобы установить связь между исходными и консолидируемыми данными надо включить опцию Создавать связи с исходными данными в окне Консолидация. Достоинство консолидации с созданием связей состоит в том, что при выполнении этой операции происходит и структурирование таблицы. Консолидированные значения вычисляются на основе данных второго уровня структуры. Если исходные данные и результаты консолидации находятся в одной книге, то обновление происходит автоматически, если в разных, то его придется выполнять с помощью команды Связи меню Правка.

В поле Функцияможно выбрать одну из доступных обобщающих функций. Чаще всего это функция Сумма.Затем в разделе Ссылкаследует задать блок, для которого выполняется анализ -A1:D11. Диапазон может быть задан с клавиатуры "руками", а может быть и выделен мышью непосредственно на листе (выделяемый блок обрамляется бегущим пунктиром). После этого следует нажать кнопку [Добавить]- координаты выделенного блока переместятся в окошко Список диапазонов.При необходимости объединить несколько таблиц эти действия следует повторить столько раз, сколько имеется таблиц. В случае потребности изъять какие-то блоки используется кнопка |Удалить].

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

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

Сводная таблица

Сводными называются вспомогательные таблицы, которые содержат часть данных анализируемой таблицы, отобранных так, чтобы зависимости между ними отображались наилучшим образом. Сводные таблицы создаются на основе области таблицы, целой таблицы или нескольких таблиц. Создание и обработка сводных таблиц осуществляется с помощью специального мастера Данные®Сводная таблица. После ее вызова открывается первое диалоговое окно мастера сводных таблиц и диаграмм – Мастер сводных таблиц и диаграмм – шаг 1 из 3.

1 шаг - Указывается источник данных для сводной таблицы. Далее.

2 шаг – Определяется диапазон ячеек, данные из которого будут включены в сводную таблицу. Если данные для сводной таблицы находятся в другой книге, следует нажать на кнопку Обзор, чтобы попасть в окно выбора файла и загрузить нужную книгу.

3 шаг – указать местоположение сводной таблицы (на отдельном или на этом листе). При размещении на текущем листе необходимо указать положение ее левого верхнего угла.

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

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

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

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

Сводная таблица- открывает меню управления сводной таблицей.

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

Мастер диаграмм- построитель диаграмм для сводной таблицы. В отличие от обычного мастера диаграмм, здесь имеются средства управления расположением и наполнением ее данными (полями).

Мастер сводных таблиц- вызывает экран третьего завершающего шага построения сводных таблиц для модификации сводной таблицы.

Показать детали предъявляет окно с перечнем полей, где можно выбрать одно, для которого показать все данные. Кнопка Скрыть деталисворачивает детали.

Обновить данные - обновляет содержимое сводной таблицы, если были изменены данные в исходной таблице.

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

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

Excel располагает развитым аппаратом численного анализа данных, в основном, доступным через меню Сервис.

Подбор параметра

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

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

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