РАЗРАБОТКА КОРПОРАТИВНОЙ ИНФОРМАЦИОННОЙ СИСТЕМЫС ПОМОЩЬЮ MS EXCEL
Методические указания к выполнению лабораторной работы
по дисциплине «Корпоративные информационные системы»
для студентов специальности 230201.65
дневной формы обучения
Одобрено
редакционно-издательским советом
Балаковского института техники,
технологии и управления
Балаково 2012
ЦЕЛЬ РАБОТЫ– изучить возможности MS Excel для создания корпоративных информационных систем: ведение баз данных, создание сводных таблиц и программирования c помощью VBA.
ОСНОВНЫЕ ПОНЯТИЯ
Использование компьютеров для хранения и обработки данных на предприятии в разы повышает эффективность управления. Это происходит благодаря увеличению скорости обработки и прохождения информации, структуризированному хранению данных и, конечно, умению всем этим пользоваться.
Информатизация в области управления корпоративными процессами предполагает, прежде всего, повышение производительности труда работников за счет снижения соотношения стоимость/производство, а также повышения квалификации и профессиональной грамотности занятых управленческой деятельностью специалистов. Одной из области применения информационных технологий в организациях является использование корпоративных информационных систем с базами данных.
Автоматизация деятельности организаций и предприятий с использованием корпоративных информационных систем (КИС) оптимально подходит в таких сферах, в которых наряду с новыми данными требуется помнить результаты предыдущей работы.
Списки
Список — прямоугольная область ячеек, в которой строки электронной таблицы имеют фиксированную регулярную структуру заполнения (все ячейки в столбце должны иметь один и тот же тип данных), (рис.1).
Список является аналогом таблицы реляционной базы данных, строки списка соответствуют Записям базы данных, столбцы списка – Полям записей, заголовки столбцов называются Именами полей.
Рис.1. Элементы списка в Excel: 1- Имена полей; 2 – Записи; 3 – Поля
При работе со списком необходимо выполнить следующие правила:
1. Каждый столбец должен содержать информацию одного типа.
2. Одна или две верхних строки списка должны содержать заголовки, описывающие содержимое расположенного ниже столбца.
3. Список не должен содержать пустые строки и столбцы.
4. Желательно использовать для списка отдельный лист. Если это невозможно, то список должен быть отделен от других данных рабочего листа пустой строкой или пустым столбцом.
5. Рекомендуется не размещать данные слева или справа от списка, т. к. они могут быть скрыты в процессе фильтрации списка.
Для работы со списком в Excel существуют несколько функций: Сортировка, фильтрация(Автофильтр и Расширенный фильтр) и Итоги. Эти функции в Excel находятся на вкладке Данные.
Фильтрация данных
Фильтрация обеспечивает выборку данных, удовлетворяющих заданным условиям отбора. При использовании фильтров скрываются строки, не удовлетворяющие условиям отбора. Фильтр также выводит номера отобранных строк синим цветом, а в Строке состояния показывает число отобранных строк и общее число записей в списке. Существуют две разновидности этой команды: Автофильтр - для простых условий отбора, и Расширенный фильтр - для более сложных вычисляемых критериев.
При выделении ячейки и выборе команды: вкладка Данные, группа интерфейсных элементов Сортировка и фильтр, кнопка Фильтр (рис.2) Автофильтр выводит кнопки со стрелками рядом с каждым заголовком столбца.
Щелчок на любой кнопке раскрывает список значений, которые можно использовать для задания условий отбора строк.
Для удаления всех автофильтров и их кнопок необходимо нажать кнопку Фильтр еще раз.
При нажатии на любую кнопку автофильтра появляется выпадающее меню, содержащее следующие опции.
ü Сортировка от максимума к минимуму.
ü Сортировка от минимума к максимуму.
ü Сортировка по цвету, Пользовательская сортировка - открывает окно Сортировка для числовых полей списка.
ü Числовые фильтры или Текстовые фильтры в зависимости от того, какие данные хранятся в столбце: числовые или текстовые.
| Задание условия по полю Предмет |
Рис.2. Список с выведенными кнопками автофильтра
Опция Числовые фильтры открывает выпадающее меню, позволяющее выбрать логическое условие (равно, не равно, больше, меньше и т.д.) для задания Пользовательского автофильтра; а также: вывести первые десять записей списка, записи выше или ниже среднего.
Опция Текстовые фильтры открывает выпадающее меню, позволяющее вызвать Пользовательский автофильтр с помощью логических условий (равно, не равно); а также отобрать записи, начинающиеся или заканчивающиеся определенным сочетанием символов (начинается с.., заканчивается на…); содержащие или не содержащие заданное количество символов (содержит…, не содержит..)
ü Фильтр по цвету позволяет отсортировать записи списка по цвету.
3. Сводная таблица
Сводная таблица – это интерактивная таблица, создаваемая пользователем на основе данных, содержащихся в списке, и отражающая обобщенные результаты обработки этих данных. Сводные таблицы используются для целенаправленного обобщения информации, представления взаимосвязей между категориями, изменения способа группировки данных, суммирования различных категорий данных и др.
Сводные таблицы создаются с помощью Мастера сводных таблиц (пошаговой инструкции по созданию сводных таблиц) по команде: вкладка Вставка, группа интерфейсных элементов Таблицы, кнопка Сводная таблица.
Первый шаг заключается в том, что пользователь должен указать источник данных для сводной таблицы и вид создаваемого отчета (рис.3). В качестве источника данных можно использовать как списки MS Excel, внешние источники данных (например, таблицы MS Access), диапазоны консолидации. Отчет может быть представлен в форме сводной таблицы или сводной диаграммы.
Затем необходимо указать программе диапазон, где размещены исходные данные. Если перед началом создания сводной таблицы курсор находился внутри списка, программа способна самостоятельно распознать этот диапазон.
В последнюю очередь пользователь должен указать место, где должна размещаться создаваемая сводная таблица.
Рис.3. Первый шаг Мастера сводных таблиц
При нажатии на кнопку ОК откроется второе окно Мастера сводных таблиц (рис.4), в котором, в зависимости от целей анализа, пользователь должен перетащить поля на области Фильтр отчета, Названия строк, Названия столбцов, Значения.
Область Фильтр отчета используется для отбора данных, создающих трехмерное представление данных.
Область Название строк – совокупность данных, размещаемых в строках сводной таблицы.
Область Названия столбцов – совокупность данных, размещаемая в столбцах сводной таблицы.
Область Значения – часть сводной таблицы, на которую размещаются поля, к которым применяются различные арифметические функции (среднее, максимум, минимум, количество и т.д.)
Рис.4. Второй шаг Мастера сводных таблиц –
Список полей сводной таблицы
Макросы
Макрос MS Excel - это программа, написанная на языке VBA (Visual Basic for Applications), и призванная автоматизировать какую-нибудь задачу. Это может быть выполнение рутинной операции по форматированию внешнего вида ячейки или одинаковых действий над большим количеством записей таблицы и т.п. По форме макрос - это записанный по определенным правилам текст программы, который хранится в тех же файлах, что и листы рабочей книги, только в особых областях, называемых модулями.
Открыть окно Макросы можно нажав соответствующую кнопку на панели Разработчик/Visual Basic, тогда перед нами откроется окно редактора Visual Basic с открытым в нем макросом (рис. 5)
Рис.5. Окно редактора Visual Basic
Окно редактора Visual Basic выглядит одинаково во всех приложениях Microsoft Office. Ниже рассмотрено главное меню этого окна.
- File (Файл) — служит для работы с файлами.
- File/Save — сохраняет файл.
- File/Import File — позволяет импортировать внешний файл в редактор. Например, таким образом можно добавить в свой проект модуль (то есть — программный код) или форму.
- File/Export File — экспортирует данные из редактора во внешний файл. Например, этой командой можно сохранить редактируемую форму и передать ее другому разработчику.
- File/Close and Return To Microsoft Word — закрывает VBA-редактор и возвращается в Microsoft Word (аналогичная команда есть и для MS Excel). Вы можете просто переключаться между редактором и основным приложением в Панели задач Windows, не закрывая редактор.
- Edit (Правка) — содержит команды для правки. Помимо стандартных команд отмены и возврата последнего действия (Undo, Redo), вырезания, копирования, вставки (Cut, Copy, Insert), поиска (Find), это меню содержит несколько особенных команд. В частности, это List Properties/Methods (Список Свойств/Методов) и другие.
- View (Вид) — cодержит команды для отображения различных окон редактора. Названия команд соответствуют названиям окон.
- Insert (Вставка) — служит для вставки в проект форм (Insert/User Form), модулей (Insert/Module), процедур (Insert/Procedure), файлов (Insert/File) и модулей класса (Insert/Class Module). Чаще всего вам придется вставлять в проект формы. Эти команды продублированы на панели инструментов редактора.
- Format (Форматирование) — служит для управления расположением элементов управления на формах.
- Debug (Отладка) — содержит команды, позволяющие отлаживать программы.
- Run (Запуск) — содержит команды для управления выполнением программ. В частности, команда Run/Sub/User Form (Запуск/Процедура/Форма) запускает на выполнение активную процедуру или форму (рядом с этой командой стоит характерный зеленый треугольник). Команда Run/Break (Запуск/Приостановить) - приостанавливает выполнение программы, команда Run/Reset (Запуск/Перезапуск) – останавливает выполнение программы. Эти команды продублированы на панели инструментов редактора в виде кнопок с соответствующими пиктограммами.
- Tools (Инструменты) — содержит средства для настройки свойств редактора, подключения дополнительных библиотек объектов.
- Add-Ins (Дополнения) — позволяет управлять дополнениями. По умолчанию это меню содержит лишь одну команду, запускающую менеджер дополнений.
- Window (Окно) — стандартные команды для работы с окнами.
- Help (Помощь) — помощь по VBA.
Окно редактора включает в себя несколько рабочих областей - окон, служащих для выполнения различных действий. На рис. 5 в окне редактора присутствуют три рабочих области:
· Code (Код) - это окно, в котором пишут тексты VBA-программ и редактируют макросы. Эта область расположена справа и занимает большую часть окна редактора.
· Project Explorer (Проводник Проекта) - это окно обычно открыто в левой верхней части окна редактора. Оно отображает информацию о компонентах проекта, позволяет быстро перемещаться между компонентами.
· Properties (Свойства) - отображает свойства выделенного объекта. Обычно располагается в левой нижней части рабочего окна.