Лабораторные по Excel / MS_Office_97_2000_Система_электронных_таблиц_Excel
.pdf6.2. Диспетчер сценариев
Сценарий – это набор входных значений, называемых изменяемыми ячейками, который порождает различные результаты в формулах, зависящих от этих ячеек. Можно определить до 32 изменяемых ячеек на один сценарий. Каждый сценарий имеет имя.
Диспетчер сценариев (Scenario Manager) вызывается командой Сцена- рии (Scenarious) из меню Сервис (Tools). После нажатия кнопки Добавить (Add) создается новый сценарий, для которого определяется следующее:
Название сценария (Scenario Name) – имя длиной до 255 символов;
Изменяемые ячейки (Changing Cells) – ссылки на изменяемые ячей- ки (от которых зависят результаты формул);
Примечание (Comment) – текст длиной до 255 символов, поясняю- щий сценарий (по умолчанию выводится дата создания или послед- него редактирования сценария и имя пользователя, создавшего или редактировавшего сценарий);
Защита (Protection) – обеспечение следующих видов защиты:
Запретить изменения (Prevent Changes) – запрет другим поль-
зователям вносить изменения в сценарий;
Скрыть (Hide) – скрытие имени сценария в окне диспетчера сце-
нариев.
После определения указанных параметров для каждой изменяемой ячейки задаются конкретные значения. Кнопка Вывести (Show) диспетчера сценариев позволяет просмотреть на рабочем листе результат влияния значе- ний, заданных в изменяемых ячейках, на все зависимые от них ячейки.
Диспетчер сценариев имеет кнопку Отчет (Summary), по которой можно создать сводный отчет на отдельном листе активной рабочей книги, поместив переключатель в позицию структура (Structure), или сводную таблицу сце- нария на отдельном рабочем листе, поместив переключатель в позицию свод-
ная таблица (Pivot Table) сценария. В поле Ячейки результата (Result Cells)
вводятся ссылки на ячейки с формулами, которые зависят от изменяемых ячеек. При вводе нескольких ссылок их надо разделять точкой с запятой (;).
Сводный отчет появляется на специальном листе отчета, в котором представлены значения изменяемых ячеек и ячеек, зависимых от них, для всех сценариев данного рабочего листа. Слева и сверху в отчете возникают символы структуры, позволяющие скрывать и показывать детальные данные.
51
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Сценарии доступны только для активного рабочего листа. Если требует- ся воспользоваться сценариями другого рабочего листа, то используется кнопка Объединить (Merge) диспетчера сценариев.
6.3. Поиск решения
Excel позволяет решать задачи сформулированные в рамках линейного программирования, с помощью инструмента, называемого поиск решения.
Для применения этого инструмента необходимо определить следующее:
целевую ячейку – ячейку, значение которой надо максимизировать, минимизировать или сделать равным конкретной величине;
изменяемые ячейки – ячейки, значения которых Excel подбирает, пока не будет найдено искомое решение, обеспечивающее заданное значе- ние целевой ячейки (можно определить до 200 изменяемых ячеек);
ограничения – ограничения к изменяемым ячейкам, к целевой ячейке или к другим ячейкам, прямо или косвенно связанным с задачей (для изменяемых ячеек можно указать по два ограничения: верхнюю и нижнюю границы). Ограничения могут применяться не более, чем к 1000 ячейкам в одной задаче.
Целевая ячейка и изменяемые ячейки должны находиться на активном рабочем листе. Целевая ячейка должна содержать формулу, прямо или кос- венно зависящую от изменяемых ячеек. Изменяемые ячейки должны содер- жать числовые значения.
Для задания указанных параметров задачи надо выбрать команду Поиск решения (Solver) из меню Сервис (Tools) и определить следующее:
Установить целевую ячейку (Set Target Cell) – ввод ссылки на це-
левую ячейку;
равной (Equal to) – задание максимального, минимального или кон- кретного значения, которое должно быть достигнуто в целевой ячей- ке в результате решения задачи;
Изменяя ячейки (By Changing Cells) – ввод ссылок на изменяемые ячейки, отделяемые друг от друга точкой с запятой (;) (кнопка Пред- положить (Guess) отыскивает все ячейки, прямо или косвенно зави- сящие от целевой ячейки);
Ограничения (Subject to the Constraints) – добавление (Добавить
(Add)), изменение (Изменить (Change)) или удаление (Удалить (Delete)) ограничений решаемой задачи.
52
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Помимо указанных параметров задачи можно задать ряд дополнитель- ных параметров процесса поиска решения, определяемых по кнопке Пара- метры (Options), например таких, как:
Максимальное время (Max Time) – ограничение времени, требую- щегося для процесса поиска решения (до 32 767);
Предельное число итераций (Iterations) – ограничение времени,
требующегося для процесса поиска решения, путем ограничения числа промежуточных вычислений (до 32 767);
Относительная погрешность (Precision) – задание точности реше-
ния (дробное число от 0 до 1);
Допустимое отклонение (Tolerance) – процент допустимого откло- нения от оптимального решения при целочисленных ограничениях для всех элементов задачи (этот параметр не влияет, если не введены
целочисленные ограничения).
Каждый из дополнительных параметров имеет значение по умолчанию, подходящее для большинства задач.
Решение поставленной задачи осуществляется после нажатия кнопки Выполнить (Solve). Excel сообщает о том, найдено решение или нет, выво- дит результаты последнего вычисления, используя значения ячеек, наиболее близкие к нужному решению, и предлагает следующие варианты действий:
Сохранить найденное решение (Keep Solver Solution) – найденное решение сохраняется в изменяемых ячейках на рабочем листе;
Восстановить исходные значения (Restore Original Values) – в из-
меняемых ячейках восстанавливаются исходные значения;
Сохранить сценарий (Save Scenario) – найденные значения изме- няемых ячеек сохраняются как сценарий в диспетчере сценариев
(Scenario Manager);
Тип отчета (Reports) – создает отчет, появляющийся на отдельном листе рабочей книги, одного из следующих типов:
Результаты (Answer) – выводит исходные и конечные значения для целевой и изменяемых ячеек, а также информацию об ограничениях;
Устойчивость (Sensitivity) – выводит информацию о том, на- сколько чувствительно решение к малым изменениям в целевой ячейке или ограничениях;
Пределы (Limits) – выводит различные предельные значения це-
левой и изменяемых ячеек.
53
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
6.4. Консолидация данных
Консолидация данных – это объединение данных из одной или не- скольких областей-источников и вывод объединенных данных в область назначения.
Области-источники – это интервалы ячеек, из которых при консолида- ции берутся данные и которые могут находиться на одном рабочем листе, на нескольких рабочих листах или даже в разных рабочих книгах.
Область назначения – это область, выделенная для итоговых (консоли- дируемых) данных, которая может находиться на том же рабочем листе или на любом другом (даже в другой рабочей книге).
Excel позволяет консолидировать данные по расположению или по ка- тегориям.
При консолидации по расположению Excel собирает информацию из одинаково расположенных ячеек нескольких интервалов (например, таблицы с финансовой информацией, расположенные на разных листах, но имеющие одинаковую структуру).
При консолидации по категориям Excel в качестве основы для объеди- нения использует заголовки столбцов или строк консолидируемых данных, что позволяет обобщать таблицы с произвольным размещением строк и столбцов.
Для консолидации данных сначала надо определить область назначения, поместив курсор в ее левую верхнюю ячейку, а затем выбрать команду Кон- солидация (Consolidate) из меню Данные (Data). В этой команде необходи- мо определить следующие параметры консолидации:
Функция (Function) – определение функции, используемой для кон- солидации данных (по умолчанию используется функция Сумм
(Sum));
Ссылка (Reference) – определение области-источника для добавле- ния к консолидируемым данным; все области-источники, выбранные для консолидации, представлены в списке Все ссылки (All References) (можно определить до 255 областей-источников);
Использовать в качестве имен (Use Labels in) – указание на необ-
ходимость использования подписей верхней строки и/или значений левого столбца областей-источников при консолидации (указание столбца (и/или строки), содержащего повторяющиеся данные);
54
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Создавать связи с исходными данными (Create Links to Source Data) – указание на необходимость выполнения связанной консоли- дации посредством создания связи, обеспечивающей автоматическое обновление области назначения при изменениях в области-источнике (при этом Excel создает формулы связи для ячеек области назначения
с соответствующими ячейками областей-источников).
Кнопки Добавить (Add) и Удалить (Delete) позволяют редактировать перечень всех областей-источников для выполняемой консолидации данных.
При выполнении связанной консолидации данных необходимо, чтобы область назначения находилась не на одном рабочем листе с областями- источниками. Лучше область назначения располагать на отдельном листе, не содержащем никаких данных. Это вызвано тем, что связанная консолидация применяет структурирование, и при выводе результатов консолидации слева от таблицы возникают символы структуры, позволяющие скрывать и пока- зывать детальные данные.
6.5. Сводные таблицы
Сводная таблица – это интерактивная таблица на рабочем листе, кото- рую можно использовать для того, чтобы подытожить и проанализировать данные уже существующих списков и таблиц. Сводную таблицу можно об- новлять в соответствии с изменениями в исходных данных, на которых она построена.
Например, пусть существует такая таблица.
№ п.п. |
A |
B |
C |
D |
E |
|
|
|
|
|
|
1 |
Продукт |
Год |
Сбыт |
Продавец |
Регион |
|
|
|
|
|
|
2 |
Молоко |
1992 |
7686 |
Иванов |
Север |
|
|
|
|
|
|
3 |
Консервы |
1993 |
2956 |
Петров |
Запад |
|
|
|
|
|
|
4 |
Консервы |
1992 |
8165 |
Петров |
Юг |
|
|
|
|
|
|
5 |
Молоко |
1993 |
4448 |
Петров |
Север |
|
|
|
|
|
|
6 |
Молоко |
1993 |
75 |
Петров |
Восток |
|
|
|
|
|
|
7 |
Консервы |
1993 |
4923 |
Иванов |
Юг |
|
|
|
|
|
|
8 |
… |
… |
… |
… |
… |
Сводная таблица должна подводить итоги продаж – по регионам, по продуктам, по годам и по торговым агентам.
55
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Поле страницы |
|
|
|
Сводная таблица |
|
|
|
||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
Регион |
|
Восток |
|
|
|
|
|
|
|
|
Поле столбца |
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Сумма: |
|
|
|
Про- |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
Поле строки |
|
Сбыт |
|
|
|
дукт |
|
|
|
|
|
||||
|
|
|
|
Год |
|
Прода- |
|
Мо- |
Кон- |
|
Общий |
|
|
||
|
|
|
|
|
|
|
|
|
|||||||
|
|
|
|
|
|
вец |
|
локо |
сервы |
|
итог |
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1992 |
|
Петров |
15164 |
|
8476 |
|
23640 |
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Иванов |
7016 |
|
|
5720 |
|
12736 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1992 |
|
|
22180 |
|
14196 |
|
36376 |
|
|
||
|
|
|
|
Всего |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1993 |
|
Петров |
1722 |
|
|
6955 |
|
8677 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Иванов |
15061 |
|
4588 |
|
19649 |
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1993 |
|
|
16783 |
|
11543 |
|
28326 |
|
|
||
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
Всего |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Общий |
|
|
38963 |
|
25739 |
|
64702 |
|
|
||
|
|
|
|
итог |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Элемент |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
Поле строки – поле исходной таблицы, значения которого в сводной таблице становятся метками строк.
Поле столбца – поле исходной таблицы, значения которого в сводной таблице становятся метками столбцов.
Поле страницы – поле исходной таблицы, значения которого в сводной таблице выводятся по одному и становятся метками страниц.
Элемент – значение поля строки, столбца или страницы.
Область данных – часть сводной таблицы, содержащая сводные данные. Значения в каждой ячейке области данных представляют сводные итоги по данным из строк источника данных.
Создание сводной таблицы выполняется с помощью Мастера сводных таблиц (Pivot Table Wizard), который вызывается командой Сводная таб-
лица (Pivot Table) из меню Данные (Data).
В первом диалоговом окне мастера сводных таблиц задается источник данных для создания сводной таблицы, находящийся:
в списке или базе данных MS Excel (Microsoft Excel List or Database) – источником является интервал ячеек рабочего листа, состоя- щий из 2 или более строк и 2 или более столбцов (первая строка этого
интервала должна содержать метки для каждого столбца);
56
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
во внешнем источнике данных (External Data Source) – источ-
ником являются данные другого приложения или внешней СУБД (в этом случае запускается MS Query для того, чтобы сделать за- прос на получение информации из внешнего источника данных);
в нескольких диапазонах консолидации (Multiple Consolidation Ranges) – источником является несколько интервалов рабочих листов;
в другой сводной таблице или сводной диаграмме (Another Pivot Table or Diagram) – источником является другая сводная таблица, находящаяся в этой же рабочей книге.
Взависимости от выбора источника данных второе диалоговое окно мастера сводных таблиц отличается, но в любом случае оно предназначено для ввода области размещения данных, используемых для построения свод- ной таблицы. Если источником данных является список и одна из ячеек спи- ска активна, то Excel автоматически выделяет необходимый диапазон ячеек.
Третье диалоговое окно мастера сводных таблиц представляет собой конструктор, с помощью которого задается макет будущей таблицы. В пра- вой части показаны заголовки всех столбцов списка-источника. С помощью мыши эти заголовки можно перетащить в область полей строк сводной таб- лицы, область полей столбцов, область полей страниц или область данных. Двойной щелчок по полю позволяет выполнить настройку его параметров. Двойной щелчок мыши по полю в области данных приведет к возможности выбора функции, определяющей тип вычислений при объединении исходных данных в сводной таблице.
Четвертое диалоговое окно мастера сводных таблиц позволяет:
Диапазон исходных данных (Pivot Table Starting Cell) – указать местоположение сводной таблицы (ссылка на левую верхнюю ячейку сводной таблицы);
Имя сводной таблицы (Pivot Table Name) – задать заголовок свод- ной таблицы;
Параметры сводной таблицы (Pivot Table Options) – задать пара-
метры и сведения для форматирования данных:
общая сумма по столбцам (Grand Totals For Columns) – указать на необходимость вывода общих итогов по столбцам;
общая сумма по строкам (Grand Totals For Rows) – указать на
необходимость вывода общих итогов по строкам;
57
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
сохранить данные вместе с таблицей (Save Data With Table Layout) – указать на необходимость сохранения копии исходных данных (сохранение данных источника в рабочей книге Excel мо- жет привести к большому размеру файла этой книги, если исполь- зовать таблицы из внешнего источника; отказавшись от их сохра- нения, можно уменьшить размер файла, но при перестраивании сводной таблицы Excel будет заново обращаться к источнику, что может занять значительное время);
автоформат (AutoFormat) – указать на необходимость использо- вания автоформатирования для сводной таблицы (дает возмож- ность применения команды Автоформат (AutoFormat) из меню
Формат (Format) к существующей сводной таблице).
Если сводная таблица основана на списке Excel, в который вставлены дополнительные строки или столбцы, то для обновления сводной таблицы с целью включения новых данных надо снова выбрать команду Сводная таб- лица (Pivot Table) из меню Данные (Data) и указать мастеру сводных таб- лиц новый интервал исходных данных.
Если были изменены только исходные данные (без добавления строк или столбцов), то обновить информацию в текущей сводной таблице можно командой Обновить данные (Refresh Data) из меню Данные (Data) или из контекстного меню.
Отличительным свойством сводных таблиц является возможность их непосредственного видоизменения на рабочем листе без обращения к масте- ру сводных таблиц.
Общие и промежуточные итоги в сводной таблице автоматически пере- считываются при скрытии или показе элементов (это не влияет на исходные данные). Для этого выбирается поле, элементы которого надо скрыть (пока- зать), а затем в команде Поле сводной таблицы (Pivot Table Field) из меню Данные (Data) или из контекстного меню выбираются/очищаются требуе- мые элементы в окне Скрыть элементы (Hide Items).
Непосредственно в сводной таблице с помощью мыши можно менять ориентацию полей строк, столбцов, страниц и данных (например, переместив поле строки в область столбцов). Элементы также можно перемещать, но в пределах соответствующего поля.
При такой реорганизации сводной таблицы происходит автоматическое пересчитывание данных.
58
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Имена элементов и полей сводной таблицы могут быть изменены путем ввода нового имени через строку формул. Новое имя сохраняется и при об- новлении данных. Но нельзя изменять имена итогов и общих итогов.
Содержание работы
1.На рабочем листе Лист 1 (Sheet 1) выполните следующие действия:
удалите имеющиеся итоги, полученные с помощью автоматиче- ского подведения итогов для списков в предыдущей лабораторной работе;
в ячейку H5 введите текст «Среднее отклонение», а в ячейку H6 – формулу, подсчитывающую среднее отклонение среднего балла аттестатов абитуриентов от проходного балла;
определите с помощью средства подбор параметра такую величи- ну проходного балла, которая бы обеспечила нулевое среднее от- клонение среднего балла аттестатов абитуриентов от проходного балла.
2.На рабочем листе Лист 7 (Sheet 7) выполните следующие действия:
в ячейке A1 создайте заголовок «Распределение по группам» с та- ким же форматированием, как заголовки других рабочих листов;
создайте таблицу с заголовками граф «ФИО» и «С учетом аттеста- та» заполнив ее данными из соответствующих граф рабочего лис- та Лист 2 (Sheet 2) и обеспечив связь с этими данными (измене- ния данных рабочего листа Лист 2 (Sheet 2) должны автоматиче- ски отслеживаться в создаваемой таблице);
к созданной таблице добавьте графу «№ группы»;
справа от созданной таблицы введите следующие данные:
Критерии распределения по группам
№ группы |
Балл |
7321 |
17 |
7322 |
16 |
7323 |
15 |
где «Балл» – |
минимальный проходной балл в соответствующую |
учебную группу;
для ячеек графы «№ группы» создайте формулу, определяющую зачисление абитуриента в конкретную учебную группу на осно- ве его общей суммы баллов с учетом аттестата и заданных кри-
59
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
териев распределения по группам (если общая сумма баллов с учетом аттестата абитуриента меньше минимального проходного балла в любую учебную группу, то абитуриент не зачисляется в институт);
создайте несколько сценариев для подбора проходных баллов в учебные группы с целью более равномерного распределения аби- туриентов по группам;
создайте сводный отчет по проведенным сценариям.
3.На рабочем листе Лист 3 (Sheet 3) в графу «Набрано» введите фор- мулы, подсчитывающие число абитуриентов, зачисленных в каждую из групп на основе данных распределения по группам рабочего листа Лист 7 (Sheet 7) (используйте функцию СЧЁТЕСЛИ (COUNTIF)).
4.На рабочем листе Лист 7 (Sheet 7) выполните следующие действия:
справа от критериев распределения по группам в одну ячейку вве- дите текст «Незачисленные», а в ячейку, находящуюся под ней, формулу, подсчитывающую число незачисленных абитуриентов на основе данных распределения по группам (использовать функ- цию СЧЁТЕСЛИ (COUNTIF));
с помощью инструмента поиск решения определите, каковы должны быть проходные баллы в учебные группы, чтобы все аби- туриенты были зачислены;
создайте отчет по результатам поиска решения, не изменяя дан- ных на рабочем листе.
5.На рабочем листе Лист 8 (Sheet 8) выполните следующие действия:
в ячейке A1 создайте заголовок «Анализ результатов экзаменов по школам» с таким же форматированием, как заголовки других ра- бочих листов;
создайте таблицу с заголовками граф «ФИО» и «№ группы», за- полнив ее данными из соответствующих граф рабочего листа Лист 7 (Sheet 7) и обеспечив связь с этими данными (изменения данных рабочего листа Лист 7 (Sheet 7) должны автоматически отслеживаться в создаваемой таблице);
к созданной таблице прибавьте графу с заголовком «Школа», за- полнив ее данными из аналогичной графы рабочего листа Лист 1 (Sheet 1) и обеспечив связь с этими данными;
60
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com