Учебно-методический комплекс дисциплины «информационные технологии управления»
Скачать 4.15 Mb.
|
| Тема 4.2. Решение управленческих задач в Microsoft Excel Концепция электронных таблиц Табличные процессоры представляют собой мощную систему создания и использования электронных таблиц. Однако реальные возможности этих систем гораздо шире, чем просто вычислительный потенциал электронной таблицы. Кроме вычислений можно выделить следующие группы возможностей:
Весь перечисленный здесь набор возможностей реализован в табличном процессоре Microsoft Excel. Изучение основ применения электронных таблиц для обработки информации (в том числе экономической) рассматривается в дисциплине «Информатика». В данном пособии рассматривается применение электронных таблиц для обработки списков и решения задач оптимизации. 4.2.1 Списки Обработка списков в Microsoft Excel При обработке данных большего объёма однотипной информации об объектах обычно используются базы данных. Большинство баз данных для хранения своей информации использует таблицы. Каждая таблица состоит из строк и столбцов. Основным назначением базы данных является быстрый поиск содержащейся в ней информации и выборка информации по заданному критерию. В качестве простой базы данных в MS Excel можно использовать список. Excel располагает обширным набором программ команд, которые позволяют легко обращаться с базами данных. Чтобы воспользоваться возможностями Excel для обработки данных, нужно создать таблицу на рабочем листе и выбрать команды Данные. Excel автоматически определяет и обрабатывает весь список при правильной его организации. При выполнении операций со списком:
В MS Excel имеется набор команд и функций, облегчающих обработку и анализ данных в списке. Чтобы использовать жит функции, при организации списка следует придерживаться следующих правил:
Для отделения заголовков от распложенных ниже данных не следует использовать пустые строки, список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные. 4.2.2. Описание демонстрационного примера Для демонстрационных основных возможностей и приёмов работы со списками в MS Excel рассмотрим следующий пример. Пусть для некоторой оптовой фирмы, торгующего средствами вычислительной техники, требуется автоматизировать учёт поставок товаров другим фирмам. Такой учёт может быть организован в таблице MS Excel (табл. 4.2) Таблица 4.2 Пример списка «Поставка товаров»
В реальных задачах учёта число параметров для каждой поставки гораздо больше, то есть в таблице будет больше столбцов, но для демонстрации возможностей средств обработки списков это не существенен. Отметим, что среди параметров часть является исходными данными, а другие – вычисляемыми (например, столбец «Сумма» - вычисляемый параметр то есть в столбце находиться формула). Для некоторых поставок «Дата выполнения» не указана, это означает, что эта поставка ещё не выполнена. Следует иметь в виду, что число строк в этой таблице может быть очень большим, поэтому обычные средства работы с табличными данными могут оказаться недостаточными. 4.2.3 Формы Форма – это удобное средство для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Форма данных представляет собой окно диалога, используемое для просмотра, изменения, добавления и удаления записей базы данных, или для поиска конкретных записей на основе определяемых критериев (4.1). Заголовки столбцов используются для создания полей формы. Одновременно в форме выводится до 32 полей списка.  Рис. 4.1. Пример формы Форма позволяет выполнить наиболее часто используемые операции работы данных: - переход между записями; - редактирование данных; - вставку и удаление записей; - отбор записей в соответствии с некоторыми критериями. В диалоговом окне формы данных можно использовать следующими кнопками:
Вставка записей с помощью формы Для добавления записи в конец списка необходимо нажать кнопку «Добавить», по завершении набора последней записи нажмите кнопку «Закрыть», чтобы добавить набранную запись и выйти из формы. Если поле списка содержит формулу, то в форме выводится её результат (например, поля «Сумма» и «Срок исполнения»). Изменять значения этого поля в форме нельзя. При добавлении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши ENTER или кнопки Закрыть. Чтобы отменить добавление записи, следует нажать кнопку Вернуть перед нажатием клавиши ENTER или кнопки Закрыть. MS Excel автоматически добавляет запись при переходе к другой записи или закрытии формы. Изменение записей с помощью формы Для перемещение следующему полю формы используется клавиша TAB, для перемещения к предыдущему полю – сочетание клавиш SHIFT+TAB. По завершению изменения записи следует нажать кнопку Закрыть для обновления текущей записи и выхода из формы. Поиск записей в списке с помощью формы Для перемещение на одну запись следует нажать на кнопки Далее и Назад или стрелки полосы прокрутки в диалоговом окне. Чтобы переместиться на 10 записей, можно нажать на полосу прокрутки между стрелками. Чтобы задать в форме условия отбора записей для отображения, следует нажать кнопки Критерии и ввести критерии в полях формы. Чтобы найти совпадающее с критериями записи, следует нажать кнопки Далее или Назад. Например, на рис. 4.2 изображено задание критерия отбора: Фирма = «Мастер» и Количество >3. После нажатия на кнопку Далее и Назад в форме будут отображаться записи, удовлетворяющие этим условиям.  Рис. 4.2. Задание в форме критерия отбора записей 4.2.4 Сортировка Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Максимальное число столбцов сортировки – три. Строки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем или убывающем порядке.  Рис. 4.3. Окно диалога для задания порядка сортировки По умолчанию списки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки, который можно выбрать с помощью кнопки Параметры. 4.2.5 Фильтрация Фильтр – это специальное средство, позволяющее отображать в списке записи, удовлетворяющие некоторым условиям. Фильтр может быть установлен только для одного списка на листе. Существует две разновидности фильтра: Автофильтр и Расширенный фильтр. Наиболее простым по использованию является Автофильтр. Для его установки необходимо: Сделать текущей какую-либо ячейку в фильтруемом списке; Выбрать команду Далее| Фильтр| Автофильтр; Чтобы отфильтровывать строки, содержащие определённое значение, нажмите кнопку со стрелкой в столбце, в котором содержаться искомые данные. Выбрать значение в списке. Повторить шаги 3 и 4 чтобы ввести дополнительные ограничения для значений в других столбцах. На рис. 4.4 приведён пример отображения фрагмента списка с Автофилтром по двум полям: Фирма = «Арсенал» и Товар = «Pentium 4».  Рис. 4.4. Список с Автофильтром При изображении списка, для которого установлен Автофильтри заданы условия фильтрации, номера строк и кнопки в заголовке списка отображаются с синим цветом. Если данные уже отфильтрованы по одному из столбцов, то при использовании фильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке. Чтобы отфильтровать список по двум значениям в одном столбце или применить отличные от равенства операторы сравнения, следует нажать кнопку со стрелкой, а затем выбрать пункт Условие и задать условие фильтрации (рис. 4.5).  Рис. 4.5. Окно диалога Пользовательский фильтр Чтобы отобразить строки, удовлетворяющие одновременно двум условиям отбора, введите оператор и значение сравнения в первой группе полей, нажмите кнопку И, а затем введите второй оператор и значение сравнения во второй группе полей. Чтобы отобразить строки, удовлетворяющие одному из двух условий отбора, необходимо использовать для их объединения логическую операцию ИЛИ. С помощью команды Автофильтр на столбец можно наложить до двух условий. Если требуется наложить большее число условий, следует использовать расширенный фильтр. Чтобы удалить фильтр для одного столбца списка, нажмите на кнопку со стрелкой, а затем выберите из развернувшегося списка пункт Всё. Чтобы удалить фильтры для всех столбцов списка, выберите в меню Данные пункт Фильтр, а затем - команду Показать всё. Чтобы удалить автофильтр, необходимо выбрать в меня Данные пункт Фильтр, а затем – выключить команды Автофильтр. 4.2.6 Расширенный фильтр Чтобы отфильтровать список с помощью расширенного фильтра, столбы списка должны уметь заголовки. На листе также должно быть не менее трёх пустых строк выше списка. Эти строки буду использованы в качестве диапазона условий отбора. Для задания условий расширенного фильтра необходимо выполнять следующие действия:
 Рис. 4.6. Окно Диалога Расширенный фильтр Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон. Примеры условий отбора расширенного фильтра. В условии отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение. Чтобы задать для отдельного столбца три или более условий отбора, введите условия в ячейке, расположенные в смежных строках. Чтобы наложить условия отбора нескольких столбцов одновременно, необходимо ввести условия в ячейке, расположенные в одной строке диапазона условий. Чтобы выбрать строки, удовлетворяющие одному из нескольких условий, наложенных на разные столбцы, необходимо ввести условия в ячейки, расположенные в разных строках диапазона условий. Виды условий отбора. При вводе условий отбора в диапазоне условий расширенного фильтра можно использовать перечисленные ниже элементы условий. При использовании текстовой команды будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности символов. Чтобы получить точное соответствие отобранных значений заданному образцу, например текст, следует ввести условие: =″=текст″ Знаки подстановки. Чтобы отобрать строки с ячейками, содержащими последовательность символов, в некоторых позициях которой могут стоять произвольные символы, следует использовать знаки подстановки. Знак подстановки эквивалентен одному символу или произвольной последовательности символов.
Значения сравнения. Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать оператор сравнения. Условие отбора с оператором сравнения следует ввести в ячейку ниже заголовка столбца в диапазоне условий. Например, чтобы отобрать строки, умеющие значения ячеек большие или равные 1000, введите условие отбора >=1000 ниже заголовка «Количество». 4.2.7. Промежуточные итоги В MS Excel имеется средство, позволяющее получить обобщённые данные, если требуется объединить данные в отдельные группы, например, сгруппировать подставки по фирмам или товарам. Использование команды Итоги позволит, не составлять формул и не преобразуя в таблицу, найти промежуточные и общие итоги, структурировать данные и вставить в таблицу строки с промежуточными и общими итогами. Рассмотрим процесс создания промежуточных итогов на демонстрационном примере «Поставка товаров» (табл. 4.1) Прежде чем подводить промежуточные итоги, необходимо отсортировать записи по тем столбцам, по которым будет выполняться группировка данных. Например, рассмотрим задачу вычисления общего количества и сумму поставок каждой фирмы. Для вставки в список промежуточных итогов необходимо выполнить следующие действия:
 Рис. 4.7. Окно "Промежуточные итоги"
 Рис. 4.8. Список с промежуточными итогами Если надо к полученным итогам добавить дополнительные (вложенные итоги), то следует повторить пункты 2-8, при этом флажок Заменить текущие итоги должен быть сброшен. Например, добавим в промежуточные итоги информацию о количестве заказов, выполненных фирмой. С этой целью вновь обратимся к команде Данные/ Итоги и в окне Промежуточные итоги в списке Операция укажем «Количество значений» и сбросим флажок Заменить текущие итоги. В результате получим список с промежуточными итогами, изображенный на рис. 4.9.  Рис. 4.9. Список с промежуточными итогами Для расчета итогового значения по строкам списка в соответствии с заданным условием можно воспользоваться командой Автофильтр, чтобы сначала вывести строки, удовлетворяющие этому условию, а затем рассчитать промежуточные итоги только по этим строкам. Для удаления из списка промежуточных итогов необходимо вызвать команду Данные / И тоги и нажать кнопку «Убрать все». При удалении промежуточных итогов из списка, удаляется структура и все разрывы страниц, которые были вставлены в список при подведении итогов. 4.2.8. Сводные таблицы Сводные таблицы являются одним из наиболее мощных средств MS Excel по анализу баз данных, помещенных в таблицы или списки. Сводная таблица не просто группирует и обобщает данные, но и даст возможность провести глубокий анализ имеющейся информации. Создавая сводную таблицу, пользователь задает имена полей, которые размещаются в ее строках и столбцах. Допускается также задание поля страницы, которое позволяет работать со сводной таблицей, как со стопкой листов. Приведем пример сводной таблицы, в которой отображены количество заказанных товаров в разрезе фирм и наименований (рис. 4.10)  Рис. 4.10. Сводная таблица Сводные таблицы удобны при анализе данных по нескольким причинам: — позволяют создавать обобщающие таблицы, которые предоставляют возможность группировки однотипных данных, подведения итогов, нахождения статических характеристик записей; — легко преобразуются: — на основе сводных таблиц строятся диаграммы, которые динамически перестраиваются вместе с изменением сводной таблицы. Рассмотрим пошаговое описание процесса создания сводной таблицы, изображенной на рис 4.10, на примере таблицы «Поставка товаров». Для этого необходимо: 1. Выбрать команду Данные/ Сводная таблица. На экране появится окно Мастера сводных таблиц, шаг 1 (рис. 4.1.1). Заметим, что количество шагов в мастере зависит от версии. Мы будем рассматривать технологию работы в Excel. 2.В первом окне Мастера сводных таблиц под заголовком «Создать таблицу на основе данных, находящихся:» надо указать источник данных для создания сводной таблицы. Возможны четыре источника данных, которые выбираются при помощи переключателей. Мы будем рассматривать только первый вариант: построение сводной таблицы на основании списка расположенного на рабочем листе. Кроме того, при помощи переключателей под заголовком «Вид создаваемого отчета:» можно задать вид сводной таблицы: просто сводная таблица или сводная диаграмма со сводной таблицей. В нашем случае выберем «сводная таблица» и нажмем кнопку «Далее».  Рис. 4.11. Мастер сводных таблиц шаг 1 3. На экране появится второе окно Мастера сводных таблиц. На этом шаге необходимо указать диапазон, содержащий данные, по которым будет строиться сводная таблица. Если источник данных находится в другой рабочей книге, то необходимо воспользоваться кнопкой орбз** Если перед выбором команды Данные/Сводная текущая ячейка находилась в списке и список оформлен в соответствии с правилами, перечисленными выше, Excel выделит весь список и сам сформирует диапазон ячеек. В противном случае необходимо его задать самостоятельно. Итак, после ввода ссылки на диапазон данных следует нажать кнопку «Далее*. На экране появится третье окно Мастера сводных таблиц(рис.4.12). 4. Первоначально нажмем кнопку «Макет...» для создания структуры сводной таблицы. На экране отобразится окно Мастер сводных таблиц и диаграмм - макет (рис.4.13). Прежде чем создавать макет сводной таблицы, надо определиться, какая информация будет вводиться в области строк, столбцов, данных и страниц. Для того чтобы поместить сводной таблице в строках (столбцах) значения поля, необходимо перетащите кнопку выбранного поля в соответствующую область макета. В нашем случае по строкам будут размещаться сведения о товарах, а по столбцам — о фирмах. Поэтому отбуксирует поле <• Товар* в область заголовков строк, а поле «Фирма» — столбцов.  Рис. 4.12. Мастер сводных таблиц, шаг 3  Рис. 4.13. Мастер сводных таблиц и диаграмм - макет Далее необходимо выбрать поле, содержащее данные, по которым нужно подвести итоги, и отбуксировать соответствующую кнопку в область Данные. В нашем случае, будем подводить итоги по полю «Количество» . Excel самостоятельно выбирает функцию подведения итогов — «Сумма». Для того чтобы изменить функцию подведения итогов, необходимо дважды щелкнуть по кнопке «Сумма по полю Количество», расположенной в области Данные. На экране отобразится окно Вычисление поля сводной таблицы (рис.4.14). В списке Операция перечислены допустимые операции: Сумма, Количество значений, Среднее и т. д. Кнопка «Дополнительно» позволяет расширить множество допустимых операций, а кнопка «Формат…» - выбрать формат отображения данных. После выбора функции следует нажать кнопку «ОК». Произойдет возврат в окно «Мастер сводных таблиц и диаграмм - макет».  Рис. 4.14. Окно вычисления сводной таблицы Размещение поля в области «Страница» позволяет работать со сводной таблицей, как со стопкой листов. Итак, в окне «Мастер сводных таблиц и диаграмм - макет» создана структура будущей сводной таблицы(рис.8.13). Нажатие на кнопку «ОК» вернет вас к третьему окну Мастера сводных таблиц. Параметры сводной таблицы можно изменить с помощью кнопки «Параметры» панели инструментов (рис.4.15). В окне, под полем Имя, в которое вводится имя сводной таблицы, находятся две группы параметров Формат и Данные. Из группы Формат отметим только флажки общая сумма по строкам и общая сумма по столбцам, которые определяют, надо ли подводить итоги по строкам и столбцам. Вернемся вновь к третьему шагу Мастера сводных таблиц (кнопка «ОК»). В группе «Поместить таблицу в:» имеется два переключателя: «новый лист» и «существующий лист», при помощи которых мы можем управлять размещение сводной таблицы.  Рис. 4.15. Параметры сводной таблицы В результате наших действий будет построена сводная таблица, приведенная на рис. 4.10. Отметим, что в Excel имеется возможность построения сводной таблицы непосредственно на рабочем листе: на третьем шаге можно не обращаться к кнопке «Макет…». В этом случае мы получим возможность формирования элементов сводной таблицы прямо на рабочем листе с помощью панели «Сводные таблицы» (Рис.4.16).  Рис.4.16. Создание сводной таблицы в Excel 2003 Для формирования элементов сводной таблицы можно отбуксировать перечисленные поля в соответствующие области на рабочем листе. Редактировать сводные таблицы можно, как в режиме просмотра, так и вернувшись, в режим создания таблицы. Изменению о таблице могут подвергаться структура (добавление новых полей, удаление существующих, изменение местонахождения поля), тип используемой функции и дополнительные вычисления.
Панель инструментов «Сводные таблицы» появляется на экране автоматически после построения сводной таблицы или вызывается командой главного меню Вид/ Панели инструментов. С помощью кнопок этой панели инструментов можно быстро переместиться в диалоговое окно 3 из 4 Мастера сводных таблиц (т. е. для изменения структуры таблицы), открыть диалоговое окно «Поле сводной таблицы», отобразить все страницы текущей сводной таблицы на отдельные листы и т. п. Например, буксировкой в область данных поля «Сумма» или в область страницы поля «Фирма» таблица, приведенная на рис. 4.10 может быть трансформирована а сводные таблицы, приведенные на рисунке 4.17. Отметим еще одну возможность, имеющуюся при создании сводных таблиц. Построим сводную таблицу, разместив в области заголовков строк даты заказа товара, а в области заголовков столбцов — наименования фирм. В области данных будем вычислять суммы продаж. В результате этих действий будет получена сводная таблица, показанная на рис. 4.18. Такая сводная таблица, как правило, не представляет особого интереса, так как при большом числе данных, равномерно распределенных по датам, она окажется очень большой. Однако эта таблица может быть модифицирована: данные по строкам могут быть сгруппированы по выбранному критерию. Для выполнения такого преобразования необходимо разместить текущую ячейку в области заголовков строк (например, в ячейке А6) и выполнить команду Данные / Группа и структура / Группировать...  Рис. 4.17. модификация сводной таблицы  Рис. 4.18. Сводная таблица без группировки В открывшемся окне Группирование можно установить нужные параметры группировки данных. На рисунке 4.19 показана группировка по кварталам и годам. Данные в области заголовков строк и столбцов могут быть сгруппированы, если в них размещены числовые данные (в том числе даты и время).  Рис. 4.19. Окно Группирование В результате выполненных действии Ехсеl преобразует сводную таблицу 2 (рис. 4.18) в таблицу, изображенную на рис. 4.20.  Рис. 4.20. Сводная таблица с группировкой данных По данной сводной таблице уже может быть проведен анализ данных и построена диаграмма, отражающая поставки товаров фирмами по кварталам. Для этого достаточно нажать на соответствующую кнопку на панели инструментов «Сводная таблица* (при этом текущая ячейка должна находиться в сводной таблице). |
Похожие:
 | Учебно-методический комплекс дисциплины составлен в соответствии с требованиями государственного образовательного стандарта высшего... | | Учебно-методический комплекс составлен в соответствии с требованиями государственного образовательного стандарта высшего профессионального... |
| Учебно-методический комплекс составлен в соответствии с требованиями государственного образовательного стандарта высшего профессионального... | | Учебно-методический комплекс составлен в соответствии с требованиями государственного образовательного стандарта высшего профессионального... |
 | Профиль подготовки информационно-измерительная техника и технологии информационные системы и технологии | | Учебно-методический комплекс составлен в соответствии с требованиями федерального государственного образовательного стандарта высшего... |
| Рабочей программы учебной дисциплины «информационные технологии в профессиональной деятельности» 4 | | Учебно-методический комплекс дисциплины обсуждена на заседании кафедры Информационные системы управления «29» июня 2011 г |
| При разработке учебно – методического комплекса учебной дисциплины в основу положены | | Учебно-методический комплекс составлен в соответствии с требованиями государственного образовательного стандарта высшего профессионального... |