Базы данных и их использование

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

Для проведения отбора по определенным условиям используют кнопки на панели инструментов (рис.1.1):

Рис.1.1 Команды "Фильтр по выделенному", "Изменить фильтр", "Применить фильтр" (слева направо) на панели инструментов

Чтобы применить заданные условия отбора, выполняют команду "Применить фильтр". Соответствующая кнопка при этом как бы "залипает". Повторное выполнение этой команды отображает все записи таблицы. Применение фильтра для данных формы или таблицы позволяет отобразить только те записи, которые удовлетворяют определенному условию отбора. Для создания фильтров используются следующие команды подменю Записи > Фильтр:

Фильтр по выделенному и Исключить выделенное - создают фильтры, критерий отбора которых формируется на основе выбранного пользователем фрагмента текста или значения. Команда "Фильтр по выделенному" позволяет отобрать те записи, у которых значение этого поля совпадает со значением поля, в котором находился курсор в момент нахождения курсора. Эту же команду можно применить для отбора не только по одному полю, а и по выделенному фрагменту.

Изменить фильтр - открывает пустую форму или таблицу, предназначенную для ввода значений, задающих критерий фильтрации. "Изменить фильтр" скрывает все записи и дает возможность ввести для нескольких полей необходимые условия отбора. Можно вводить и сложные условия. Например, если необходимо выбрать из таблицы "Страны" все страны с населением от 10 до 20 млн. населения, то следует ввести в поле "Население" условие:

> 10000 and <20000

Если нужно выбрать все страны с денежными единицами "динар" или "фунт", вводим следующее условие отбора в поле "Денежная единица": фунт or динар

При этом Access автоматически распознает текстовые значения и заключает их в двойные кавычки. Значения типа "Дата/время" автоматически окружаются знаком "#" (решетка).

Расширенный фильтр - открывает окно, позволяющее определить сложные условия фильтрации и сортировки.

Фильтр Фильтр по выделенному создать очень просто. Для того чтобы отобрать записи о книгах серии "Для профессионалов", найдите в поле Серия это название и щелкните на нем мышью. Затем щелкните на кнопке Фильтр по выделенному. На экране будут представлены лишь те записи, в которых присутствует это название серии.

Чтобы удалить фильтр, используйте кнопку Удалить фильтр . Для его повторного применения снова щелкните на указанной кнопке (однако теперь она будет называться Применение фильтра). Если для создания фильтра нужно использовать не все содержимое поля, а лишь его часть, выделите необходимый фрагмент данных поля. Фильтры можно применять к уже отфильтрованным данным, что позволяет уточнить критерий отбора записей.

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

Рис.1.2 Сокрытие записей посредством команды Исключить выделенное

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

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

Рис.1.3 Таблица с набором полей, аналогичным исходной таблице

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

Если нужно отобразить записи, удовлетворяющие хотя бы одному из нескольких критериев, создайте первый критерий описанным выше способом, а затем щелкните на вкладке ИЛИ и определите в новой таблице второй критерий. После этого появится еще одна вкладка ИЛИ, на которой можно задать следующий критерий. Определив все необходимые критерии, щелкните на кнопке Применение фильтра.

Более сложные способы отбора данных возможны при использовании запросов.

Задание 2. Microsoft Excel. Вариант 8

ПРОДАЖА ОФИСНОГО ОБОРУДОВАНИЯ

Наименование	Сентябрь	Октябрь	Ноябрь	Цена за штуку, руб.	Количество, шт.	Стоимость, руб.	Налоги, руб.	Прибыль, руб.
Стул офисный	810	930	650	45000 р.	?	?	?	?
Стол офисный	960	780	450	80000 р.	?	?	?	?
Принтер	20	31	24	21000 р.	?	?	?	?
Модем	32	21	14	65000 р.	?	?	?	?
Всего:	?	?	?			?	?	?

Символ "?" означает расчет в данной графе.

Налоги считаются по следующей формуле: если Стоимость > 100000, то Налоги равны 30% от стоимости, иначе 10% от стоимости.

Прибыль = Стоимость - Налоги

Для полей Цена за штуку, Стоимость, Прибыль задайте денежный формат.

Постройте диаграмму, отражающую прибыль по каждому наименованию.

Создание и форматирование таблицы

Запускаем Microsoft Excel. На чистом рабочем листе в диапазон ячеек А1: I1 вводим названия граф, остальные в соответствии с заданием заполняем исходными данными (рис.2.1).

Рис.2.1 Исходные данные на рабочем листе

Выполним изменение ориентации текста в ячейках. Для этого выделяем все ячейки, в которых текст должен быть написан вертикально снизу вверх. При этом для выделения ячеек, не составляющих один прямоугольный диапазон, удерживаем нажатой клавишу "Ctrl". Затем выполняем команду "Формат - Ячейки" и в открывшемся диалоговом окне на вкладке "Выравнивание" (рис.2.2) устанавливаем в пункте "Ориентация" значение 90 градусов.

Рис.2.2 Изменение ориентации текста

Выполняем обрамление таблицы. Для этого выделяем диапазон А1: I8 и в кнопке-меню "Границы" выбираем пункт "Все границы" (рис.2.3).

Рис.2.3 Установка обрамления рабочей таблицы

Ввод формул и печать таблицы
Выполним расчет количества, стоимости, налогов и прибыли для первого наименования офисного оборудования (2-я строка - "Стул офисный").

Для расчета количества вводим в ячейку F2 формулу "=СУММ (B2: D2)" (рис.2.4), для расчета стоимости - в ячейку G2 формулу "=E2*F2", для расчета налогов - в ячейку H2 формулу "=ЕСЛИ (G2>100000; G2*30%; G2*10%)", для расчета прибыли - в ячейку I2 - "=G2-H2".

Рис.2.4 Суммирование начислений для первого работника

Выделяем ячейки F2: I2, подводим указатель мыши к правому нижнему углу выделенной области и делаем двойной щелчок левой кнопкой мыши. Формулы из F2: I2 автоматически распространяются в диапазон нижестоящих ячеек F3: I5 (рис.2.5).

Рис.2.5 Копирование формул способом автораспространения

Для расчета необходимых сумм в строке "Всего: " (строка 6 рабочего листа) выделяем ячейки B6: D6, G6: I6 и нажимаем кнопку "Автосумма" на Стандартной панели инструментов (рис.2.6).

Рис.2.6 Автоматическое суммирование выбранных граф

Выводим полученную таблицу на печать. Для распечатки в режиме отображения формул предварительно выполняем команду "Сервис - Параметры" и на вкладке "Вид" в разделе "Параметры окна" устанавливаем флажок в пункте "формулы".

В режиме формул принято отображать заголовки столбцов и строк, поэтому выполняем команду "Файл - Параметры страницы" и на вкладке "Лист" устанавливаем соответствующий флажок (рис.2.7). Распечатки таблицы с результатами в обычном виде и в режиме отображения формул приводим в Приложении А.

Рис.2.7 Установка печати заголовков столбцов и строк

Построение диаграммы
Выделяем диапазон I3: I7 м нажимаем кнопку "Мастер диаграмм" на панели инструментов "Стандартная". На первом шаге Мастера диаграмм выбираем тип "Гистограмма" и вид "Объемный вариант обычной диаграммы" (рис.2.8), нажимаем кнопку "Далее".

Рис.2.8 Выбор типа диаграммы

На втором шаге переходим на вкладку "Ряд" и в пункте "Имя" устанавливаем значение "=Лист1! $I$1" (имя ряда), а в пункте "Подписи оси Х" - значение "=Лист1! $A$2: $A$5" (рис.2.9). Нажимаем кнопку "Далее".

Рис.2.9 Шаг второй Мастера диаграмм

На третьем шаге на вкладке "Заголовки" вводим названия оси категорий и оси значений (рис.2.10), на вкладке "Легенда" отказываемся от отображения легенды, т.к в ней нет смысла при формировании диаграммы, состоящей из одного ряда, на вкладке "Подписи данных" устанавливаем флажок в пункте "Включить в подписи … значения" (рис.2.11).

Рис.2.10 Ввод заголовков осей

Рис.2.11 Установка отображения значений

На последнем шаге указываем поместить диаграмму на отдельном листе (рис.2.12).

Рис.2.12. Последний шаг Мастера диаграмм

Диаграмму распечатываем и приводим в Приложении А.

Microsoft Access
Задана база данных "Учет выпускаемой продукции", состоящая из шести таблиц:

Таблица:

1. Список выпускаемых изделий

* Код изделия

Наименование изделия

Код единицы измерения

Цена

3. Справочник единиц измерения

* Код единицы измерения

Наименование единицы

4. План выпуска изделий

2. Список выпускающих цехов

* Номер выпускающего цеха

Наименование цеха

5. Цеховая накладная сдачи продукции на склад

Номер выпускающего цеха

Месяц выпуска

Код изделия

Количество по плану

* Номер цеховой накладной

Номер цеха

Код изделия

Месяц сдачи

Количество сдано

6. Товарно-транспортная накладная

* Номер накладной

Код изделия

Количество отгружено

* - ключевое поле.

Пояснения по выполнению задания:

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

каждое изделие имеет только одну единицу измерения;

один цех может выпускать несколько наименований изделий;

на одном складе хранится несколько наименований готовых изделий;

количество изделий измеряется целым числом;

выпуск цехом готовой продукции планируется помесячно;

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

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

Количество цехов должно быть не менее 4 и не более 6, изделий - не менее 7 и не более 9.

1. Используя таблицу "План выпуска изделий" и "Список выпускаемых изделий", вынесите в запрос "Номер выпускающего цеха", "Месяц выпуска", "Код изделия", "Наименование изделия", "Цена", "Количество по плану".

2. На основе запроса № 1 создайте форму, используя все поля, имеющиеся в запросе.

3. На основе запроса № 1 создайте запрос с вычисляемым полем, в котором будет определяться стоимость изделий по плану.

4. По запросу №3 составьте отчет.

Создание и связывание таблиц

Запускаем Microsoft Access. Для создания таблиц указанной структуры используем команду "Конструктор" на панели инструментов базы данных (3.1).

Рис.3.1 Запуск конструктора таблиц

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

Во всех таблицах, в которых это необходимо, устанавливаем ключевые поля. Так, например, в таблице "Список выпускаемых изделий" выделяем поле "Код изделия" и выполняем команду его контекстного меню "Ключевое поле" (рис.3.2).

Рис.3.2 Создание макета таблицы

Вводим в созданные таблицы произвольные данные. Затем выполняем создание схемы данных. Для этого выполняем команду меню "Сервис - Схема данных". В появившемся окне поочередно выбираем все таблицы и нажимаем кнопку "Добавить" (рис.3.3).

Рис.3.3 Включение таблиц в схему данных

Нажав кнопку "Закрыть", переходим к созданию связей между таблицами. Чтобы связать, например, таблицы "Список выпускаемых изделий" и "Справочник единиц измерения" по одноименным полям "Код единицы измерения", "перетаскиваем" поле из одной таблицы в другую. В открывшемся окне "Изменение связей" (рис.3.4) устанавливаем флажок в поле "Обеспечение целостности данных" и видим, что создан тип отношения "один-ко-многим".

Рис.3.4 Связывание таблиц

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

Создание запросов

Для создания первого запроса воспользуемся командой окна базы данных "Создание запроса с помощью мастера". На первом шаге мастера в окне "Создание простых запросов" в выпадающем списке "Таблицы и запросы" выбираем таблицу "План выпуска изделий". Затем из списка "Доступные поля" перемещаем кнопкой поля "Номер выпускающего цеха", "Месяц выпуска", "Код изделия" и "Количество по плану" в список "Выбранные поля" (рис.3.5).

Затем выбираем таблицу "Список выпускаемых изделий" и перемещаем в список выбранных поле "Наименование изделия".

Рис.3.5 Первый шаг мастера запросов

На втором шаге выбираем подробный отчет (рис.3.6).

Рис.3.6. Второй шаг Мастера простых запросов

На третьем шаге задаем имя запроса "Запрос 1" (рис.3.7).

Рис.3.7. Третий шаг Мастера запросов

В окне базы данных переключаемся к группе "Формы" и выполняем команду "Создание формы с помощью мастера". Выбираем запрос "Запрос 1" и включаем в список выбранных полей все его поля. На втором шаге Мастера форм выбираем вид представления данных "Список выпускаемых изделий" - "Подчиненные формы" (рис.3.8).

Рис.3.8. Выбор представления данных

Внешний вид на третьем шаге устанавливаем "табличный", стиль на четвертом - "Рисовая бумага", на пятом шаге соглашаемся с предложенными названиями главной и подчиненной форм: "Список выпускаемых изделий" и "План выпуска изделий подчиненная форма" соответственно. Получаем форму, внешний вид которой изображен на рис.3.9.

Рис.3.9. Форма по запросу №1

Для создания второго запроса используем конструктор запросов, создавая новое поле "Стоимость по плану: [Цена] * [Количество по плану] " с помощью построителя выражений (рис.3.10).

Рис.3.10. Построитель запросов

Создание отчета
Создание отчета непосредственно по указанному в задании запросу невозможно, т.к запрос на обновление не может быть источником строк, поэтому используем таблицу "Выпускаемые изделия", измененную запросом. На первом шаге Мастера выбираем в поле "Таблицы и запросы" необходимый запрос и все его поля перемещаем в список выбранных (рис.3.11).

Рис.3.11 Шаг первый мастера отчетов

На втором шаге отказываемся от группировки, на третьем отказываемся от сортировки содержимого запроса, на четвертом выбираем макет "блок", на пятом - стиль "Деловой" (рис.3.12), на последнем вводим имя отчета "Выпускаемые изделия" и завершаем создание отчета.

Рис.3.12. Выбор макета и стиля отчета

Затем кнопкой "Предварительный просмотр" переходим в режим просмотра отчета и отправляем его на печать.

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

Список использованных источников

1. Берлинер Э.М., Глазырина И.Б., Глазырин Б.Э. Office XP. Самоучитель. - 2-е изд. - М.: ООО "Бином-Пресс", 2003. - 432 с.: ил.

2. Симонович С.В., Евсеев Г.А., Алексеев А.Г. Специальная информатика: Учебное пособие. - М.: АСТ-ПРЕСС: Инфорком-Пресс, 2001. - 480 с.

3. Буза М.К., Певзнер Л.В. Windows-приложения: от операции к реализации: Учеб. пособие. - Мн.; Выш. шк., 1998. - 490 с.

4. Леонтьев В.П. Новейшая энциклопедия персонального компьютера 2001. - М.: ОЛМА-ПРЕСС, 2001. - 847 с.: ил.

5. Информатика: Учеб. пособие для 10-го кл. с углубл. изучением информатики общеобразоват. шк. с рус. яз. обучения / А.И. Павловский, А.Е. Пупцев, П.Л. Гращенко. - Мн.: Нар. асвета, 2000. - 223 с.

6. Основы экономической информатики: Учебное пособие / А.Н. Морозевич, Н.Н. Говядинова, Б.А. Желязко и др.; Под общ. ред.А.Н. Морозевича. - Мн.: БГЭУ, 1998. - 438 с.

7. Пасько В.П. Самоучитель работы на персональном компьютере.6-е изд., переработанное. - Спб.: Питер; Киев: Издательская группа BHV, 2004. - 655 с.