624
.pdfСИБИРСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ
П.М. ОСИПОВА, Н.М. ТКАЧУК
АВТОМАТИЗАЦИЯ РАБОЧЕГО МЕСТА ИНЖЕНЕРА ПО ОБРАБОТКЕ БАЗ ДАННЫХ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL С ПРИМЕНЕНИЕМ VBA
Учебное пособие
НОВОСИБИРСК 2008
УДК 338.364:681.5
О-741
О с и п о в а П.М., Тк ачу к Н.М.Автоматизациярабочего
места инженера по обработке баз данных в табличном процессоре MS Excel с применением VBA: Учеб. пособие.—
Новосибирск: Изд-во СГУПСа, 2008. — 99 с. ISBN 5-93461-329-4
Рассматриваются вопросы, связанные с расчетами в электронных таблицах. Представлены средства автоматизации работы и повышения эффективности использования Excel при обработке списков, создании форм и диаграмм средствами Visual Basic for Application. Приводится взятый из профессиональной сферы деятельности инженера пример, на котором излагаются основные принципы выполнения расчетно-графической работы.
Пособие предназначено для студентов специальности 190701 (240100) «Организация движения и управление на транспорте (железнодорожном)». Может быть полезно для студентов других специальностей, изучающих информатику.
Утвержденоредакционно-издательскимсоветомуниверситета в качестве учебного пособия.
Ответственный редактор канд. техн. наук, доц. кафедры «Информационные
технологии транспорта» Е.В. Редьков
Р е ц е н з е н т ы:
кафедра «Программные системы и базы данных» Новосибирского государственного технического университета (завкафедрой д-р техн. наук А.А. Попов)
канд. техн. наук, доц. кафедры «Полупроводниковые приборы и микроэлектроника» Новосибрского государственного технического университета П.П. Люмаров
ISBN 5-93461-329-4
Осипова П.М., Ткачук Н.М., 2008
Сибирский государственный университет путей сообщения, 2008
ПРЕДИСЛОВИЕ
Учебное пособие предназначено для выполнения студентами расчетно-графическойработы подисциплине «Информатика». Навыки, полученные при выполнении расчетно-графи- ческой работы, могут быть использованы на старших курсах в другихдисциплинахприавтоматизациирабочегоместапользователя компьютера для обработки наборов данных.
Работа выполняется средствами табличного процессора MS Excel и VBA и состоит из двух разделов:
1.Создание и ведение базы данных.
2.Создание отчетов.
Каждый раздел выполняется в несколько этапов. В начале каждого этапа излагается теоретический материал, затем приводится четкая последовательность действий, выполнение которых обеспечивает получение предусмотренных данным этапом результатов.
Принципы выполнения расчетно-графической работы рассматриваются на реальной модели базы данных. Получив знания и умения работы с базой данных на конкретном примере, студент должен выполнить индивидуальное задание. Варианты заданий и общие указания к ним приводятся в разделах 3 и 4.
В своей работе инженеру приходится выполнять много расчетов, и в этом ему может помочь использование электронных таблиц. Однако для повышения эффективности работы полезноизучитьнекоторыедополнительныеособенностиExcel. Использование списков, выполнение анализа данных, построение диаграмм и создание фильтров могут оказаться полезными при работе инженера с электронными таблицами. Следует
3
отметить, что почти все работники сферы управления сталкиваются в своей деятельности с компьютерами, но далеко не все имеют представление о широчайших возможностях использования компьютерных технологий. Необходимо, чтобы студенты научились, используя средства Excel и VBA, создавать микроофисы для рациональной и эффективной организации работы с большими объемами информации.
Теоретический материал пособия может быть полезен для всех, изучающих в курсе «Информатика» способы обработки данных средствами табличного процессора MS Excel с элементами программирования на VBA.
ВВЕДЕНИЕ
Совокупность сведений о каких-либо объектах, процессах, событиях или явлениях чаще всего представляется двухмерной таблицей. Практически, любой набор данных может быть представлен в виде плоских таблиц. Каждая таблица обладает следующими свойствами:
•все элементы столбца имеют одинаковый тип данных;
•столбцам присвоены уникальные имена;
•в таблице нет двух одинаковых строк;
•порядок расположения строк и столбцов в таблице не имеет значения.
Списки в MS Excel можно рассматривать как простейший вариант базы данных (БД).
Современные электронные базы данных чаще всего организованы в виде таблицы, и в настоящее время, как правило, используются реляционные базы данных, представляющие собой несколько взаимосвязанных таблиц. В понятие базы данных обязательным элементом входит описание правил этой взаимосвязи. Независимо от того, сколько таблиц входит в базу данных, каждая строка любой таблицы содержит данные ободномобъекте(человеке, техническом устройстве, документе и т.д.), а столбцы содержат различные характеристики этих объектов (названия, адреса, даты и т. д.). Строки таблицы принято называть записями, а столбцы — полями записей. В полях записей содержатся атрибуты объектов записей. Все записи имеют одинаковые поля, содержащие разные значения
4
атрибутов. Каждое поле записи имеет строго определенный тип данных — текст, число, дата и т.п.
Задачи хранения, получения, анализа, визуализации данных принято называть управлением данными, а программы для решения подобных задач — системами управления базами данных (СУБД). Существуют различные пакеты для работы с данными — dBASE, FoxPro, Oracle и др. Наиболее распространенной является входящая в пакет MS Office —
СУБД MS Access.
Несложные базы данных, как правило, состоящие из одной таблицы, можно создавать и в Microsoft Excel. Тем более что это тоже компонент пакета MS Office, и в дальнейшем при необходимости их легко импортировать в СУБД MS Access. С электронными таблицами удобно работать, если число записей невелико (не более 500–1000). При увеличении числа записей работать становится неудобно, главным образом из-за плохой структурированности данных.
Всвязи с тем, что наборы данных, с которыми нам придется работать, невелики, и в то же время желая подчеркнуть их идентичность базам данных, будем называть списки базами данных, обработка которых позволит в дальнейшем облегчить понимание принципов работы в СУБД.
Врасчетно-графической работе студент должен сформировать базу данных, состоящую из двух взаимосвязанных таблиц, по данным этих таблиц создать отчеты и построить диаграммы (см. нижеприведенный образец задания расчетнографической работы).
5
Задание на расчетно-графическую работу по информатике (специальности У, БТП)
Вариант 0
Работа выполняется в табличном процессоре Microsoft Excel с элементами программирования в VBA. Управление проектом должно осуществляться через кнопочные меню на рабочих листах.
1.Создайте справочник. Корректировка справочника должна выполняться с использованием стандартного диалогового окна (команда Фор-
ма… из меню Данные).
2.Создайте на рабочем листе форму для заполнения базы данных, в которой должны находиться только исходные данные для расчетной таблицы «Сведения о состоянии пути ПЧ».
На форме расположить кнопки для добавления, удаления, корректировки записей таблицы.
Сведения о состоянии пути ПЧ
|
|
|
|
Число |
|
|
|
|
|
|
|
Загрязнен |
Потреб- |
|
Номер |
Длина |
|
Длина |
дефект- |
Тип |
Негодные |
|
|
Общее |
Число |
% |
|||
Тип |
Тип |
Материал |
ность |
ность в |
||||||||||
километ |
километ |
рельса |
ных |
скреп |
скрепления, |
число |
негодных |
негодных |
||||||
рельса |
шпал |
шпал |
балласта, |
среднем |
||||||||||
ра |
ра, м |
|
, м |
рельсов |
ления |
% |
|
|
шпал |
шпал |
шпал |
% |
ремонте |
|
|
|
|
|
в км |
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
||
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
|
3345 |
1000 |
Р75 |
Б |
22 |
ЖБР |
25 |
Ш2 |
железобетон |
1840 |
57 |
3,1 |
28 |
нет |
|
3346 |
1000 |
Р75 |
Б |
18 |
ДО |
5 |
Тип1 |
дерево |
1840 |
100 |
5,4 |
17 |
нет |
|
4024 |
875 |
Р65 |
25,0 |
6 |
ДО |
15 |
Тип1 |
дерево |
1610 |
250 |
15,5 |
13 |
нет |
|
4027 |
1000 |
Р75 |
25,0 |
22 |
ДО |
8 |
Тип1 |
дерево |
1840 |
150 |
8,2 |
34 |
нет |
|
4031 |
1000 |
Р75 |
25,0 |
18 |
ДО |
9 |
Тип1 |
дерево |
1840 |
280 |
15,2 |
23 |
нет |
|
3035 |
950 |
Р75 |
12,5 |
6 |
КБ |
13 |
Ш1 |
железобетон |
1748 |
41 |
2,3 |
45 |
да |
|
3031 |
1000 |
Р65 |
12,5 |
22 |
КБ |
12 |
Ш1 |
железобетон |
1840 |
29 |
1,6 |
22 |
нет |
|
3450 |
1000 |
Р65 |
25,0 |
18 |
КБ |
7 |
Ш1 |
железобетон |
1840 |
12 |
0,7 |
16 |
нет |
|
3452 |
975 |
Р75 |
25,0 |
6 |
ДО |
2 |
Тип1 |
дерево |
1794 |
200 |
11,1 |
39 |
да |
|
3453 |
1000 |
Р75 |
25,0 |
22 |
ДО |
14 |
Тип1 |
дерево |
1840 |
230 |
12,5 |
35 |
да |
|
3460 |
1000 |
Р75 |
25,0 |
22 |
ДО |
6 |
Тип1 |
дерево |
1840 |
107 |
5,8 |
31 |
нет |
|
Итого |
|
|
|
182 |
|
|
|
|
|
1456 |
|
|
|
|
В среднем |
|
|
16,5 |
|
10,5 |
|
|
|
132 |
7,4 |
27,5 |
|
Исходные данные — гр. 1–7, 11, 13. Данные из справочников — гр. 8, 9. Результаты — гр. 10, 12, 14.
гр. 10 = 1,84•гр. 2.
гр. 12 = гр. 11/гр. 10•100.
гр. 14 = «да», если гр. 13 > 30 % и (гр. 7 > 12 % или гр. 12 > 10 %). 3. Создайте отчеты:
а) таблицу с рассчитанными данными, средними значениями и итогами; б) таблицу с данными, отсортированными по возрастанию значений гр. 12; в) максимальное и минимальное значения гр. 6; г) три вида диаграмм:
—гистограмму,
—круговую,
—кольцевую;
д) таблицы с данными, отфильтрованными по условиям:
—процент использования грузоподъемности превышает среднее значение;
—грузоподъемность попадает в указанный диапазон (границы диапазона задать самостоятельно);
—отбор по двум столбцам (условие сформулировать самостоятельно).
6
1. СОЗДАНИЕ И ВЕДЕНИЕ БАЗЫ ДАННЫХ
Одной из типичных задач, выполняемых с помощью электронных таблиц, является ведение списков —списков номеров телефонов, клиентов, торговых операций, материальных ценностей и т. д. Microsoft Excel имеет самый богатый набор средств для работы со списками по сравнению с любой другой программой электронных таблиц, что позволяет легко создавать, вести и анализировать такого рода информацию.
Спискипозволяют эффективно работать с большимиупорядоченными наборами данных, имеющими одинаковую структуру. Например, списком является телефонный справочник, в котором в большом количестве строк приведены фамилии абонентов и номера их телефонов. Каждый элемент списка занимает одну строку, в которой данные распределяются по нескольким полям (столбцам). В табличном процессоре имеются операции для их обработки (сортировка, фильтрация и т. д.).
В первой строке рабочего листа обычно помещаются названия отдельных полей списка. Эта строка используется в качестве строки заголовков списка. Начиная со следующей строки вводятся данные.
Чтобы достичь максимальной эффективности при работе со списками, следует соблюдать следующие правила:
•Каждый столбец должен содержать информацию одного типа. В списке сотрудников, например, можно отвести один столбец для фамилии, второй для имени, третий для отчества, четвертый для даты приема на работу и т. д.
•Одна или две верхние строки списка должны содержать заголовки, каждый из которых описывает содержимое расположенного ниже столбца.
•В списке не должно быть пустых строк и столбцов.
•Лучшевсегоотводить для спискаотдельный лист.Еслиэто невозможно, то список должен быть отделен от других данных рабочего листа по крайней мере одной пустой строкой и одним пустым столбцом.
•Нежелательноразмещать данныеслева исправаот списка, так как они могут быть скрыты в процессе фильтрации списка.
7
1.1. Создание и корректировка справочника
Новую информацию всегда можно внести в список, перейдя к первой пустой строке внизу списка и введя ее с клавиатуры. Но еще проще и быстрее это сделать с помощью команды Форма из меню Данные, которая откроет диалоговое окно для ввода данных. Перед ее использованием необходимо выделить любую ячейку в списке. В противном случае Excel не сможет правильно представить в форме заголовки столбцов.
В верхней части формы Excel выводит имя листа (а не книги), содержащего список, для которого предназначена форма. Непосредственно ниже этой строки заголовка находятся все заголовки столбцов списка. Если в список уже введено несколько строк, то справа от заголовков столбцов будут видны значения для первой строки списка. В верхнем правом углу формы выводится информация об общем количестве строк в списке и номере строки, отображаемой в форме в данный момент. Следует заметить, что строка заголовков исключается из этого количества. В правой части формы находятся несколько кнопок команд, предназначенных для работы со списком. Рядом с каждым заголовком столбца располагается поле ввода, если столбец не содержит значений, вычисляемых с помощью формул.
Чтобы вставить в список новую строку, нужно нажать кнопку Добавить. Excel выведет пустую форму, в которой можно ввести значения для новой строки. После заполнения данными всех полей ввода и нажатия командной кнопки Добавить можно начать ввод следующей строки и т. д., пока не будут введены все строки списка. В заключение следует закрыть окно щелчком по командной кнопке Закрыть.
Цели работы:
•познакомиться с понятием “справочник» в электронной таблице;
•научиться изменять данные справочника, используя ко-
манду Данные / Форма… .
Справочник — это электронная таблица, представляющая собой список однотипных данных, которые используются для автоматического заполнения других таблиц.
8
В пе рво м ст олбц е справочник должен содержать уникальные данные.Например, табельный номер всписке сотрудников или номенклатурный номер в прайс-листе. Для заполнения базы данныхв рассматриваемом варианте расчетно-графи- ческой работы предлагается использовать справочник, представленный на рис. 1.
Рис. 1. Справочная таблица
Для редактирования справочников (добавления, удаления, исправления записей) в MS Excel можно использовать диалоговое окно команды Форма… из меню Данные (рис. 2). Так как управление проектом должно осуществляться через кнопочные меню на рабочих листах, то и корректировка справочника должна выполняться с помощью макроса. Создадим макрос методом автозаписи.
Рис. 2. Диалоговое окно команды «Форма»
9
Ход работы Задание 1. Создайте новую рабочую книгу и сохраните ее
насвоем сетевом диске подименем «Сведения осостоянии пути ПЧ».
По ря д ок р або т ы:
1.Откройте программу MS Excel. Для этого в Главном меню(кнопкаПуск)выберитепунктВсепрограммы/Microsoft Office/Microsoft Office Excel2003. При открытии програм-
мы открывается новая рабочая книга.
2.В меню Файл выберите команду Сохранить, в диалоге этой команды в списке Папка выберитесвой сетевойдиск (К:),
встроке Имя файла введите Сведения о состоянии пути ПЧ
и щелкните кнопку Сохранить.
Задание 2. Переименуйте Лист1 в «Главное меню», а Лист2
в«Справочник»; на листе «Справочник» создайте заголовок справочника.
По ря д ок р або т ы:
1.Активизируйте Лист1.
2.Выберите команду Формат / Лист / Переименовать,
введите новое имя листа Главное меню и нажмите клавишу
Enter.
3.Активизируйте Лист2.
4.Выберите команду Формат / Лист / Переименовать,
введите новое имя листа Справочник и нажмите клавишу
Enter.
5.Введите текст:
Тип скрепления — в ячейку А1.
Тип шпал — в ячейку В1.
Материал шпал — в ячейку С1.
6.Выделите блок ячеек А1 : С1.
7.Выберите команду Формат / Ячейки и на вкладке Выравнивание установите параметры:
Выравнивание: по горизонтали — по центру. Выравнивание: по вертикали — по центру.
Переносить по словам .
8. Выделите блок ячеек А1 : С2.
1 0