Создание информационной системы средствами MS Excel и VBA (стр. 1 из 4)
Федеральное агентство по образованию
Государственное образовательное учреждение высшего профессионального образования
Камская государственная инженерно-экономическая академия
Кафедра ПИУ
Курсовая работа на тему:
"Создание информационной системы средствами MSExcel и VBA"
Предметная область: "Библиотека"
Выполнил студент: гр.2117
Масалимова Г.А.
Проверил: ст. преподаватель
Хасанова Ф.С.
Наб. Челны 2008
1. Задание к курсовой работе
Создать таблицу, содержащую сведения о книгах. Сведения о каждой книге это фамилия автора, название книги, год издания, издательство, тираж, цена. Самостоятельно придумать не мене пяти запросов. При решении данной работы применять сортировку, автофильтр, расширенный фильтр, функции БД, ВПР(). Построить минимум три вида диаграмм.
2. Исходная база данных
3. Список. Составляющие списка
В MSExcel можно работать со списками любого объема. В Excel БД-это просто список, состоящий из одного или более столбцов.
Выделяются следующие элементы списка (рис. 1):
· запись (отдельная строка);
· поле (отдельный столбец);
· имена полей (в первой строке списка);
· строка заголовков (первая строка списка)._
БД в Excel состоит из диапазона БД, диапазона критериев (условий), диапазона для извлечения:
· диапазон БД – область, где хранятся данные списка; связанные друг с другом данные записываются в отдельные строки, каждому столбцу соответствует свое поле списка с уникальным именем поля;
· диапазон критериев – область на рабочем листе, где задаются критерии поиска информации; здесь указываются имена полей и отводится область для записи условия отбора;
· диапазон для извлечения – это область, в которую копируют выбранные из списка данные.
 |
Рис.1. Элементы списка или БД в MSExcel
4. Ввод данных в список или БД
В рабочую книгу Excel может вводиться информация любого типа: числа, текст, даты, время, последовательные ряды данных и формулы. После того как данные введены, возникает вопрос о том, в каком виде они должны быть отображены на экране. Для представления данных в Excel существуют самые разнообразные форматы, в частности, числовой, текстовый, дата, время и т.д. Если данных много и их типы различны, то может возникнуть проблема организации их ввода, а после того как данные введены, может потребоваться организация их поиска и замены (например, для исправления ошибок).
Расположение данных в таблицах предполагает наличие у них заголовков. Сделать простые заголовки у таблиц не представляет трудностей, но если заголовки сложные, то требуется наличие определенных навыков в их построении с помощью средств Excel.
Мне необходимо ввести и обработать список с предметной областью Библиотека и со следующими именами полей: Фамилия автора, Наименование книги, Год издания, Издательство, Тираж, Цена.
В общей сложности фрагмент этого списка выглядит следующим образом:
При просмотре достаточно большого списка удобно, чтобы строка заголовков всегда оставалась видимой. Это можно осуществить следующим образом (рис.2):
· опустить маркер разделения окна по вертикали под строку заголовков,
· воспользоваться командой Окно/Закрепить области
Рис 2. Закрепление заголовка списка.
5. Поиск и замена данных
После ввода информации может возникнуть необходимость отыскать или внести изменения в ранее введенный текст. С этой целью используется команды Правка/Найти и Правка/Заменить, во многом схожие, в частности, данные команды имеют идентичные диалоговые окна, в которых задаются их параметры.
Действие команды Правка/Найти распространяется на выделенный фрагмент или на весь рабочий лист, если фрагмент не выделен. Данная команда просматривает также выделенную группу листов, исключая модули макросов.
Результатом работы команды Найти по поиску указанных символов является первая выделенная ячейка, содержащая заданные символы. Повторный поиск начинается с текущего положения и заканчивается на следующей ячейке, в которой имеются указанные символы. При отсутствии символов, заданных в команде Найти, выдается соответствующее сообщение.
Выбор команды Правка/Заменить предоставляет в распоряжение пользователя еще одно поле диалогового окна, в которое следует ввести информацию для замены найденного сочетания символов (рис.3)
Рис.3 Диалоговое окно команды Правка/Заменить.
Поиск можно осуществлять в формулах, значениях или примечаниях ячеек. В режиме Ячейка целиком, который доступен только для рабочих листов, поиск ведется по совпадению всего содержимого ячейки с заданным набором символов.
При нажатии кнопки Найти далее выполняется список следующего вхождения заданного набора символов. Для возврата к позиции предыдущего вхождения следует нажать клавишу Shift и, одновременно с этим, выбрать кнопку Найти далее.
Необходимо соблюдать осторожность при использовании команд Заменить и Заменить все . Перед использованием команды Заменить все можно порекомендовать создание резервной копии таблицы, с которой предстоит работать.
6. Применение функции ВПР()
При работе с электронными таблицами очень удобно, когда какой-либо столбец или строку можно заполнить, не копируя информацию из другой таблицы, а применяя определенную функцию для автоматического заполнения столбца или строки. Именно в этих случаях используется функция ВПР()
Функция ВПР() применяется в тех случаях, когда информация расположена в двух таблицах:
· справочная таблица;
· рабочая (основная) таблица.
Обе таблицы связаны друг с другом, они содержат общее поле, которое называется ключом.
Функция ВПР() открывается с помощью "Мастер функций" в группе "ссылки и массивы".
Применяя эту функцию, необходимо ответить на следующие вопросы:
1) что ищем (указываем образец);
2) где осуществлять поиск (диапазон справочной таблицы);
3) что является результатом поиска (№ столбца);
4) как точно нужно искать (ложь "конкретно" или истина "приближенно").
Составляющие функции ВПР() рис.4:
1) искомое значение - это значение, которое требуется найти в первой строке таблицы;
2) табличный массив - описание ячеек, содержащих просматриваемую таблицу, т.е. справочник;
3) № индекса столбца – номер столбца, содержащий информацию о том, в какой строке функция должна искать результат. Нумерация строки или столбца начинается с 1 относительно ключевого поля.
4) диапазон просмотра - это необязательный критерий (ложь - если необходим точный поиск, истина – если необходимо приближенное значение)
Рис.4 Диалоговое окно функции ВПР()
7. Сортировка данных
Сортировка по возрастанию/убыванию
Зачастую после ввода какой-либо информации в электронную таблицу ее необходимо отсортировать тем или иным образом. Например, по возрастанию содержимого одного из столбцов. Для этой цели в Excel имеется команда Данные/Сортировка, которую необходимо вызвать после выделения диапазона ячеек (таблицы), который необходимо отсортировать.
При выполнении этой команды отображается диалоговое окно Сортировка диапазона (рис.6), в котором имеются следующие основные разделы:
· Сортировать по – выбор столбца, по которому нужно сортировать в первую очередь;
· Затем по - определение столбца, по ячейкам которого следует выполнять сортировку в том случае, если равны соответствующие значения в столбце, указанном в поле Сортировка по;
· В последнюю очередь по – выбор столбца, по которому сортировка должна выполняться в случае равенства значений в ячейках каких-либо строк первых двух столбцов, которые выбраны в полях Сортировать по и Затем по.
При этом в каждом из приведенных разделов можно указать направление сортировки (по возрастанию или по убыванию).
Сортировка в особом порядке
В MSExcel данные можно сортировать в особом порядке. И причем, кроме заданных порядков, можно указывать собственный порядок сортировки. Для этого в диалоговом окне Сортировка диапазона нужно выбрать копку Параметры. В диалоговом окне Параметры сортировки в раскрывающемся списке Порядок сортировки по первому ключу выбрать подходящий вариант.
Например, мне необходимо сортировать БД по столбцу Издательство. Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать (рис.5):
1. с помощью команды Сервис/Параметры вызвать окно Параметры;
2. перейти на клавишу Списки;
3. в поле Элементы списка ввести элементы, разделяя их нажатием клавиши Enter;
4. после вода всех элементов нажать кнопку Добавить и закрыть окно Параметры.
Рис.5 Создание собственного порядка сортировки.
Сортировка по четырем и более полям
С помощью команды Данные/Сортировка (рис.5) можно упорядочить список по любому количеству полей. Это осуществляется путем последовательных сортировок. Для того чтобы предыдущие сортировки не терялись, следует начинать с ключей самого нижнего уровня.
