Моор С.М., Моор П.К., Моор А.П. - Информационные технологии управления (1092190), страница 18

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 18)

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

В результате получим список с промежуточными итогами, изображенный на рис. 8.9.

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

Для удаления из списка промежуточных итогов необходимо вызвать команду Данные|Итоги и нажать кнопку «Убрать все». При удалении промежуточных итогов из списка, удаляется структура и все разрывы страниц, которые были вставлены в список при подведении итогов.

Рис. 8.9. Список с промежуточными итогами

8.2.8. Сводные таблицы

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

Приведем пример сводной таблицы, в которой отображены количество заказанных товаров в разрезе фирм и наименований (рис. 8.10).

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

Сводные таблицы удобны при анализе данных по нескольким причинам:

• позволяют создавать обобщающие таблицы, которые предоставляют возможность группировки однотипных данных, подведения итогов, нахождения статических характеристик записей;

• легко преобразуются;

• на основе сводных таблиц строятся диаграммы, которые динамически перестраиваются вместе с изменением сводной таблицы.

Рассмотрим пошаговое описание процесса создания сводной таблицы, изображенной на рис. 8.10, на примере таблицы «Поставка товаров». Для этого необходимо:

1. Выбрать команду Данные|Сводная таблица. На экране появится окно Мастера сводных таблиц, шаг 1 (рис. 8.11). Заметим, что количество шагов в мастере зависит от версии. Мы будем рассматривать технологию работы в Excel 2000. В Excel 2003

2. В первом окне Мастера сводных таблиц под заголовком «Создать таблицу на основе данных, находящихся:» надо указать источник данных для создания сводной таблицы. Возможны четыре источника данных, которые выбираются при помощи переключателей. Мы будем рассматривать только первый вариант: построение сводной таблицы на основании списка расположенного на рабочем листе.

Кроме того, при помощи переключателей под заголовком «Вид создаваемого отчета:» можно задать вид сводной таблицы: просто сводная таблица или сводная диаграмма со сводной таблицей. В нашем случае выберем «сводная таблица» и нажмем кнопку «Далее».

3. На экране появится второе окно Мастера сводных таблиц. На этом шаге необходимо указать диапазон, содержащий данные, по которым будет строиться сводная таблица. Если источник данных находится в другой рабочей книге, то необходимо воспользоваться кнопкой Обзор.

Рис. 8.11. Мастер сводных таблиц, шаг 1

Если перед выбором команды Данные|Сводная текущая ячейка находилась в списке и список оформлен в соответствии с правилами, перечисленными выше, Excel выделит весь список и сам сформирует диапазон ячеек. В противном случае необходимо его задать самостоятельно.

Итак, после ввода ссылки на диапазон данных следует нажать кнопку «Далее». На экране появится третье окно Мастера сводных таблиц (рис. 8.12).

Рис. 8.12. Мастер сводных таблиц, шаг 3

4. Первоначально нажмем кнопку «Макет…» для создания структуры сводной таблицы. На экране отобразится окно Мастер сводных таблиц и диаграмм - макет (рис. 8.13).

Рис. 2.13. Мастер сводных таблиц и диаграмм - макет

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

Для того чтобы поместить сводной таблице в строках (столбцах) значения поля, необходимо перетащите кнопку выбранного поля в соответствующую область макета. В нашем случае по строкам будут размещаться сведения о товарах, а по столбцам – о фирмах. Поэтому отбуксирует поле «Товар» в область заголовков строк, а поле «Фирма» - столбцов.

Далее необходимо выбрать поле, содержащее данные, по которым нужно подвести итоги, и отбуксировать соответствующую кнопку в область Данные. В нашем случае, будем подводить итоги по полю «Количество». Excel самостоятельно выбирает функцию подведения итогов – «Сумма».

Для того чтобы изменить функцию подведения итогов, необходимо дважды щелкнуть по кнопке «Сумма по полю Количество», расположенной в области Данные. На экране отобразится окно Вычисление поля сводной таблицы (рис. 8.14). В списке Операция перечислены допустимые операции: Сумма, Кол-во значений, Среднее, и т.д. Кнопка «Дополнительно» позволяет расширить множество допустимых операций, а кнопка «Формат…» – выбрать формат отображения данных. После выбора функции следует нажать кнопку «ОК». Произойдет возврат в окно «Мастер сводных таблиц и диаграмм - макет.

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

Размещение поля в области «Страница» позволяет работать со сводной таблицей, как со стопкой листов.

Итак, в окне «Мастер сводных таблиц и диаграмм – макет» создана структура будущей сводной таблицы (рис. 8.13). Нажатие на кнопку «ОК» вернет нас к третьему окну Мастера сводных таблиц.

Параметры сводной таблицы можно изменить с помощью кнопки «Параметры» панели инструментов (рис. 8.15).

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

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

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

В результате наших действий будет построена сводная таблица, приведенная на рис. 8.10.

Отметим, что в Excel 2003 имеется возможность построения сводной таблицы непосредственно на рабочем листе: на третьем шаге можно не обращаться к кнопке «Макет…». В этом случае мы получим возможность формирования элементов сводной таблицы прямо на рабочем листе с помощью панели «Сводные таблицы» (рис. 8.16).

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

Рис. 8.16. Создание сводной таблицы в Excel 2003

Способы редактирования в разных режимах сводится к:

• перетаскиванию названия поля в соответствующую область;

• вызову команды Данные главного меню;

• обращению к контекстно-зависимому меню поля;

• двойному щелчку на имени поля;

• щелчку на кнопке панели инструментов «Сводные таблицы».

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

Например, буксировкой в область данных поля «Сумма» или в область страницы поля «Фирма» таблица, приведенная на рис. 8.10 может быть трансформирована в сводные таблицы, приведенные на рис. 8.17.

Рис. 8.17. Модификация сводной таблицы

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

Такая сводная таблица, как правило, не представляет особого интереса, так как при большом числе данных, равномерно распределенных по датам, она окажется очень большой. Однако эта таблица может быть модифицирована: данные по строкам могут быть сгруппированы по выбранному критерию.

Для выполнения такого преобразования необходимо разместить текущую ячейку в области заголовков строк (например, в ячейке А6) и выполнить команду Данные|Группа и структура|Группировать….

Рис. 8.18. Сводная таблица без группировки

В открывшемся окне Группирование можно установить нужные параметры группировки данных. На рис. 8.19 показана группировка по кварталам и годам. Данные в области заголовков строк и столбцов могут быть сгруппированы, если в них размещены числовые данные (в том числе даты и время).

Рис. 8.19. Окно Группирование

В результате выполненных действий Excel преобразует сводную таблицу 2 (рис. 8.18) в таблицу, изображенную на рис. 8.20.

Рис. 8.20. Сводная таблица с группировкой данных

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

8.3. Решение оптимизационных задач в Microsoft Excel

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

Несмотря на все многообразие этих задач MS Excel предлагает единый, мощный инструмент их решения - Поиск решения. От пользователя требуется сформулировать в MS Excel задачу (разработать формулы для расчета оптимизируемого значения), а оптимальное решение он найдет сам. Данный раздел на примерах демонстрирует возможности решения оптимизационных задач.

8.3.1. Задача об оптимальном производстве

В данном разделе будет продемонстрировано, как при помощи средства Поиск решения решаются линейные оптимизационные задачи на примере задачи о планирование производства.

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

Рассмотрим следующую задачу планирования производства. Пусть фирма производит два вида продукции: столы и стулья. Для изготовления одного стула требуется 15 кг. древесины, для одного стола – 35 кг. На изготовление одного стула затрачивается 2 час. рабочего времени, на изготовление стола – 8 час. Каждый стул приносит прибыль 1000 руб., а стол – 3000 руб. Кроме того, фирма ежедневно может располагать 2400 кг. древесины и 400 час. рабочего времени. Ставится задача нахождения плана выпуска продукции, который обеспечит максимальную прибыль.

Характеристики

Список файлов книги