Создание информационной системы средствами МС Excel и VBA База данных Сборка изделий (стр. 1 из 3)

Цель работы: Разработка информационной системы некоторой предметной области с использованием табличного процессора Excel объективно ориентированного языка VBA.

Программа Microsoft Excel позволяет:

· сформировать данные в виде таблиц;

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

· представить данные из таблиц в графическом виде;

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

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

Существуют ограничения, накладываемые на структуру базы данных:

· первый ряд базы данных должен содержать неповторяющиеся имена полей;

· остальные ряды базы данных должны содержать записи, которые не являются пустыми рядами;

· информация по полям (столбцам) должна быть однородной, т.е. только цифры или только текст;

Основная работа любой базы данных заключается в поиске информации по определенным критериям. С увеличением количества записи поиск информации затрудняется.

Если данные представлены в таком виде, программа Excel позволяет упростить этот процесс путем сортировки и фильтрации данных.

Сортировка - это упорядочение данных по возрастанию или по убыванию. Проще всего произвести такую сортировку, выбрав одну из ячеек и щелкнув на кнопке Сортировка по возрастанию или Сортировка по убыванию на панели инструментов.

Параметры сортировки задают командой Данные > Сортировка. При этом открывается диалоговое окно Сортировка диапазона. В нем можно выбрать от одного до трех полей сортировки, а также задать порядок сортировки по каждому полю.

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

При фильтрации базы отображаются только записи, обладающие нужными свойствами. Простейшее средство фильтрации - автофильтр. Он запускается командой Данные > Фильтр > Автофильтр.

По команде Автофильтр в ячейках, содержащих заголовки полей появляются раскрывающие кнопки. Щелчок на такой кнопке открывает доступ к списку вариантов фильтрации. Варианты фильтрации записываются обычно с помощью простых или составных условий. Составные условия формируются с помощью логических выражений и логических операции И или ИЛИ. Записи, не удовлетворяющие условию фильтрации, не отображаются.

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

Задание

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

Каждая запись содержит поля:

· фамилия сборщика,

· количество изделий, собранных им ежедневно в течение шестидневной недели, т.е. раздельно – в понедельник, вторник и т.д.

Количество записей произвольное.

Выдать следующую информацию:

- фамилию сборщика и общее количество деталей, собранное им за неделю;

- данные по разряду, Ф.И.О., понедельнику, среде, субботе сборщиков;

- данные, сортированные по разряду в особом порядке: 4, 6, 3,5;

- сборщиков 3 и 4 разряда, количество деталей которых больше 1114 и меньше или равно 1633;

- сборщиков раздела 3 с количеством изделий более 1300;

- максимальное количество деталей сотрудников каждого разряда;

- максимальное значение изделий сотрудников 3 и 5 разрядов, собранных в понедельник;

- количество сотрудников разряда 3;

- среднее значение изделий сотрудников разряда 3 и 5;

- количество изделий сотрудников разряда 3 и 5;

- количество по полю Разряд.

Упорядочить информацию. Построить диаграмму.

1. Создание базы данных

В Microsoft Excel в качестве базы данных можно использовать список.

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

Если таблицу считают базой данных, то:

- столбцы списков становятся полями базы данных;

- заголовки столбцов становятся именами полей базы данных;

- каждая строка списка преобразуется в запись данных.

Все действия со списками (базой данных) выполняет команда главного меню ДАННЫЕ.

На листе не следует помещать более одного списка. Некоторые функции обработки списков, например фильтры, не позволяют обрабатывать несколько списков одновременно.

Между списком и другими данными листа необходимо оставить по меньшей мере одну пустую строку и один пустой столбец. Это позволяет Microsoft Excel быстрее обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений.

В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

Важные данные не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.

Форма — это способ представления данных из таблицы, когда на экране представлено содержимое только одной записи.

С помощью формы можно:

- заносить данные в таблицу;

- просматривать или корректировать данные;

- удалять данные;

- отбирать записи по критерию.

Вставка записей с помощью формы

1. Укажите ячейку списка, начиная с которой следует добавлять записи.

2. Выберите команду Форма в меню Данные.

3. Нажмите кнопку Добавить.

4. Введите поля новой записи, используя клавишу TAB для перемещения к следующему полю. Для перемещения к предыдущему полю используйте сочетание клавиш SHIFT+TAB.

Чтобы добавить запись в список, нажмите клавишу ENTER. По завершении набора последней записи нажмите кнопку Закрыть, чтобы добавить набранную запись и выйти из формы.

Если поле списка содержит формулу, то в форме выводится ее результат. Изменять это поле в форме нельзя. При добавлении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши ENTER или кнопки Закрыть. Чтобы отменить добавление записи, нажмите кнопку Вернуть перед нажатием клавиши ENTER или кнопки Закрыть. Microsoft Excel автоматически добавляет запись при переходе к другой записи или закрытии формы.

Поиск записей в списке с помощью формы

Для перемещения на одну запись нажмите на стрелки полосы прокрутки в диалоговом окне. Чтобы переместиться на 10 записей, нажмите полосу прокрутки между стрелками.

Нажмите кнопку Далее для перехода к следующей записи списка и Назад — для перехода к предыдущей.

Чтобы задать условия поиска или условия сравнения, нажмите кнопку Критерии. Введите критерии в форме. Чтобы найти совпадающие с критериями записи, нажмите кнопки Далее или Назад. Чтобы вернуться к правке формы, нажмите кнопку Правка.

1. В первой строке нового рабочего листа наберите головку таблицы со следующими названиями граф:

· фамилию сборщика,

· количество изделий, собранных ежедневно каждым сборщиком,

2. Через команду Данные – Форма занести Ф.И.О. сборщиков и количество собранных деталей (Рис.1).

Рис.1. База данных по сборщикам

2. Сортировка данных

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

Рис.2. Сортировка сборщиков по разряду

3. Фильтрация данных

Команда “Данные-Фильтр” является удобным инструментом для создания запросов по одному или нескольким критериям. Особенно удобным и наглядным является подрежим “Автофильтр”. При включении данного режима справа от названий полей списка появится раскрывающаяся кнопка со стрелкой, которая содержит перечень всех значений для данного поля. При выборе значения из данного списка на экране остаются только записи, удовлетворяющие данному критерию поиска. Остальные записи скрываются. С результатом запроса можно работать как с обычной таблицей – распечатать, сохранить в отдельном файле, перенести на другой рабочий лист и т.д. Чтобы вернуться к первоначальному виду таблицы, в списке справа от названия поля выбираем вариант “все”.