624

СИБИРСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ

П.М. ОСИПОВА, Н.М. ТКАЧУК

АВТОМАТИЗАЦИЯ РАБОЧЕГО МЕСТА ИНЖЕНЕРА ПО ОБРАБОТКЕ БАЗ ДАННЫХ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL С ПРИМЕНЕНИЕМ VBA

Учебное пособие

НОВОСИБИРСК 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–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