База данных. бд. Базой данных называется организованная структура, предназначенная для хранения данных и информации
 Скачать 38.81 Kb.
|
|
Базой данных называется организованная структура, предназначенная для хранения данных и информации. Система управления базами данных Excel и MS Access предназначены для создания и поддержания в рабочем состоянии баз данных, для получения из них необходимой информации в требуемой форме, а также для создания приложений, автоматизирующих работу с базами данных. На сегодняшний день базы данных используются практически в каждой сфере общественной жизни: на производственных предприятиях, в банках, фирмах и т. д. Поэтому, необходимость умения работы с ними велика для современного человека. Целью данной курсовой работы является закрепление навыков работы в программах Excel и MS Access в процессе проектирования баз данных. Данная курсовая работа помогает приобрести навыки работы с базами данных при помощи создания следующих объектов: В Excel — подсчет данных по формулам, сортировка, фильтрация, структурирование в трех уровнях, подведение итогов, консолидация, сводные таблицы и построение диаграммы. В MS Access — таблица с сортировкой и фильтрацией, запросы различных видов, отчет с группированием, форма и кнопочная форма. 1. Задание I. Применение приложения Excel как базы данных 1.1 Подсчет данных по формулам Исходя из задания известно, что в жилищно-коммунальном хозяйстве производится оплата различных коммунальных услуг. База данных салона должна содержать информацию о квартиросъемщиках, их адресах, льготах, всей стоимости услуг с учетом льгот, также информацию о долгах и пени. Время изготовления — 14 дней. Произвольно нужно заполнить следующие поля -«Кваритиросъемщик», «Адрес», «Количество членов семьи», «Льготы», «Вид услуги», «Отчетный период», «Цена», «Долго за предыдущий период», «Пени». А остальные поля — «Стоимость с учетом льгот», «Оплатить», «Фамилия квартиросъемщика, не имеющего задолженность», «Льготы, имеющие максимальную цену», «Вид услуги, оказываемой квартиросъемщику, имеющему и более членов семьи», «Адрес квартиры, имеющей льготы Ветеран» и «Отчетный период, имеющий максимальную пени» — являются вычисляемыми, они заполняются при помощи формул. Данные поля заполняются следующим образом: Для определения наименования «Стоимость с учетом льгот», «Фамилия квартиросъемщика, не имеющего задолженность», «Льготы, имеющие максимальную цену», «Вид услуги, оказываемой квартиросъемщику, имеющему и более членов семьи», «Адрес квартиры, имеющей льготы Ветеран» и «Отчетный период, имеющий максимальную пени», выбираем функцию ЕСЛИ. Данная функция является логической. И она позволяет строить выражения, вырабатывающие различные результаты в зависимости от некоторых условий. «Стоимость с учетом льгот» рассчитывается следующим образом: Для начала построим выражение: ЕСЛИ «Квартиросъемщик» имеет «Льготу» Ветеран, то из стоимости будет вычитаться 100 рублей, ЕСЛИ «Льгота» — Инвалид, то 50 рублей, и ЕСЛИ «Льгота» — Герой труда, то 40 рублей. Следовательно, «Стоимость с учетом льгот» =G3-(ЕСЛИ (D3="Ветеран" ;100;0)+ЕСЛИ (D3="Инвалид" ;50;0)+ЕСЛИ (D3="Герой труда" ;40;0)) «Фамилия квартиросъемщика, не имеющего задолженность» рассчитывается следующим образом: ЕСЛИ в ячейке «Долг за предыдущий период» будет число, большее нуля, то в ячейке «Фамилия квартиросъемщика, не имеющего задолженность» будет стоять значение ячейки «Квартиросъемщик», а если значение равно нулю, то прочерк. Следовательно, «Фамилия квартиросъемщика, не имеющего задолженность» =ЕСЛИ (I3>0;A3;" -") «Льготы, имеющие максимальную цену» рассчитываются следующим образом: ЕСЛИ в ячейке «Цена» будет максимальное число, по сравнению с остальными числами в столбце G3=МАКС ($G$ 3;$G$ 22), то в ячейке «Льготы имеющие максимальную цену» будет стоить значение ячейки «Льготы», а иначе прочерк. =ЕСЛИ (G3=МАКС ($G$ 3;$G$ 22);D3;" -") «Вид услуги, оказываемой квартиросъемщику, имеющему 5 и более членов семьи» рассчитывается следующим образом: ЕСЛИ в ячейке «Количество членов семьи» будет число равное или больше 5, то в ячейке «Вид услуги, оказываемой квартиросъемщику, имеющему и более членов семьи» будет стоять значение ячейки «Вид услуги», а иначе прочерк. =ЕСЛИ (C3>=5;E3;" -") «Адрес квартиры, имеющей льготы Ветеран» рассчитывается следующим образом: ЕСЛИ в ячейке «Льготы» будет стоять льгота «Ветеран», то в ячейке «Адрес квартиры, имеющей льготы Ветеран», будет стоять значение ячейки «Адрес», а иначе прочерк. =ЕСЛИ (D3="ветеран" ;B3;" -") «Отчетный период, имеющий максимальную пени» рассчитывается следующим образом: ЕСЛИ в ячейке «Пени» будет максимальное число, по сравнению с остальными числами в столбце J3=МАКС ($J$ 3:$J$ 22), то в ячейке «Отчетный период, имеющий максимальную пени» будет стоить значение ячейки «Отчетный период», а иначе прочерк. =ЕСЛИ (J3=МАКС ($J$ 3:$J$ 22);F3;" -") Для расчета поля «Текущая дата» нужно выбрать функцию : =ТДАТА (). Дата будет определяться автоматически. Полученные данные представлены на рисунках 1.1 и 1.2 — «Подсчет данных по формулам». Они представлены с готовыми данными Таблица 1 и с данными в формулах Таблица 2. 1.2 Сортировка Сортировка — это упорядочение данных по возрастанию или убыванию. Самая простая сортировка может быть выполнена с помощью двух пиктограмм — кнопок сортировки (по возрастанию и убыванию). Для этого надо зафиксировать курсор на колонке, по которой производится сортировка, и щелкнуть по одной из пиктограмм. Также выполнение сортировки может производиться с помощью команды «Данные» — «Сортировка» с установкой необходимых параметров. В курсовой работе на листе «Сортировка» выполняются одноуровневая, двухуровневая и трехуровневая сортировки. 1.2.1 Одноуровневая сортировка На листе «Сортировка» была произведена одноуровневая сортировка. Для ее выполнения необходимо произвести следующие операции: необходимо установить курсор в поле списка и ввести команду «Данные» — «Сортировка». При этом должна выделиться вся область списка. Если этого не произошло, то предварительно необходимо выделить весь список, а затем ввести указанную команду. Затем в диалоговом окне «Сортировка диапазона» необходимо установить: Сортировать по: поле «Цена», по возрастанию. Затем по: (не сортировать). В последнюю очередь: (не сортировать). После этого необходимо поставить флажок в строке «Идентифицировать поля по подписям», чтобы исключить из области, подлежащей сортировке заголовков столбцов. Или отсортировать данные при помощи пиктограммы — кнопки сортировки. таблица сортировка фильтрация данные 1.2.2 Двухуровневая сортировка На том же листе Сортировка была произведена двухуровневая сортировка. Сначала были отсортированы данные первого уровня, а затем одинаковые записи первого столбца сортируются по столбцу второго уровня. Для двухуровневой сортировки необходимо установить курсор в поле списка и ввести команду «Данные» — «Сортировка». В диалоговом окне «Сортировка диапазона» необходимо установить: Сортировать по: поле «Цена», по возрастанию. Затем по: поле «Количество членов семьи», по убыванию. В последнюю очередь: (не сортировать). После этого необходимо поставить флажок в строке «Идентифицировать поля по подписям», чтобы исключить из области, подлежащей сортировке заголовков столбцов. 1.2.3 Трехуровневая сортировка Также на листе «Сортировка» была произведена трехуровневая сортировка. Данные были отсортированы сначала по полю «Цена», затем по полю «Количество членов семьи», в последнюю очередь по полю «Отчетный период». Для проведения трехуровневая сортировки необходимо установить курсор в поле списка и ввести команду «Данные» — «Сортировка» и ввести команду «Данные» — «Сортировка». В диалоговом окне «Сортировка диапазона» необходимо установить: Сортировать по: поле «Цена», по возрастанию. Затем по: поле «Количество членов семьи», по убыванию. В последнюю очередь: поле «Отчетный период», по месяцам (Январь, Февраль, Март, Апрель и т. д.) После этого необходимо поставить флажок в строке «Идентифицировать поля по подписям». Отсортированные данные приведены на рисунке Таблица 3 «Трехуровневая сортировка». Они выделены цветом. 1.3 Фильтрация данных в списке Фильтрация данных в списке — это выбор данных по заданному критерию, т. е. это операция, которая позволяет выделить нужные данные среди имеющихся. С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью простой команды — «Автофильтрация». Более сложные запросы к базе данных можно реализовать при помощи команды «Расширенный фильтр». 1.3.1 Автофильтрация Для того, чтобы произвести автофильтрацию, необходимо изначально скопировать на новый лист «Автофильтрация» исходную базу данных с листа «Подсчет данных по формулам». Затем установим курсор в область списка и выполним команду «Данные» — «Фильтр» — «Автофильтр». По этой команде Excel помещает раскрывающиеся списки непосредственно в имена столбцов списка. Щелкнув по стрелке, можно ознакомиться со списком возможных критериев выбора. Если кнопка была использована для назначения фильтра, то стрелка окрашивается в синий цвет. Существуют следующие варианты списка критериев: · «Все» — выбираются все записи; · «Первые 10» — в диалоговом окне «Наложение условия по списку» выбрать определенное кол-во наименьших или наибольших элементов списка, которое необходимо отобразить; · «Значения» — будут выбраны только те записи, которые в данном столбце создают указанное значение; · «Условие» — выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский автофильтр»; · «Пустые» — предъявляются строки, не содержащие данные в колонке; · «Непустые» — предъявляются только те записи, которые содержат непустые строки в колонке. В данном случае необходимо сформировать следующие условия для проведения операции «Автофильтрация»: для поля «Льготы» нужно задать значение «Ветеран или Инвалид», а для поля «Количество членов семьи» нужно задать условие — «Больше или равно 3″». В соответствии с тем, что одновременно установлены фильтры в двух столбцах, то фильтрация записей будет выполнена по двум условиям одновременно, то есть в итоге будут отобрана льготы Ветеран и Инвалид, количество членов семьи которых больше или равно 3. В итоге были найдены квартиросъемщики, которые удовлетворяют вышеизложенным условиям. Данный результат представлен на рисунке Таблица 4 «Автофильтрация». 1.3.2 Расширенный фильтр Фильтрация с помощью расширенного фильтра осуществляется с помощью команды: «Данные» — «Фильтр» — «Расширенный фильтр». Для использования команды «Расширенный фильтр», надо сначала создать таблицу критериев, которую далее разместим на том же рабочем листе «Расширенный фильтр», что и исходная таблица «Подсчет данных по формулам», но так, чтобы не скрывать лист во время фильтрации. В «Расширенном фильтре» также как и в «Автофильтре» существует несколько вариантов видов критерия, такие как: Критерий сравнения включает операции следующего типа: · точного значения; · значения, формируемого с помощью операторов отношения; · шаблона значения, включающего символы или Множественный критерий — критерий, формируемый в нескольких столбцах. · Если критерии указываются в каждом столбце на одной строке, то они считаются связанными условием И. · Если критерии записаны в нескольких строках, то они считаются связанными условием ИЛИ. Вычисляемый критерий — представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение «ИСТИНА» или «ЛОЖЬ». 1.3.2.1 Расширенный фильтр «Критерий сравнения» На листе «Расширенный фильтр» сначала представлен отбор записей по «Критерию сравнения». Для проведения данного отбора, необходимо скопировать все имена полей списка в другую область на том же листе, установив курсор в ячейку A30. Данная область — это такая область, где будут формироваться условия отбора записей. Далее в области условий отбора задается «Критерий сравнения». В данном случае следует найти квартиросъемщиков с льготой «Ветеран» платящие за услуги «За газ» количество членов семьи которых больше 2. Для этого в первую строку после имен полей необходимо ввести: в столбец «Льгота» — точное значение «Ветеран», а в столбец «Вид услуги» — «За газ»; в столбец «Количество членов семьи» — условие — > 2. Затем производится фильтрация записей на том же листе. Для этого необходимо установить курсор в область списка; выполнить команду «Данные» — «Фильтр» — «Расширенный фильтр»; в диалоговом окне «Расширенный фильтр» при помощи мыши нужно задать следующие параметры: «Скопировать результат в другое место» — нужно установить флажок; «Исходный диапазон» — A3: P23. «Диапазон условия» — A30: P31. «Поместить результат в диапазон» — A33 и нажать кнопку «OK». В результате такого отбора был найден квартиросъемщик Петров с льготой «Ветеран», платящий за услуги «За газ», количество членов семьи которого равно 4 и квартиросъемщик Огнев с льготой «Ветеран», платящий за услуги «За газ», количество членов семьи которого равно 4. Результат фильтрации представлен на рисунке «Расширенный фильтр „Критерий сравнения“». 1.3.2.2 Расширенный фильтр «Вычисляемый критерий» На листе «Расширенный фильтр» также представлен отбор записей по «Вычисляемому критерию». Для того чтобы произвести этот отбор, необходимо сформировать в области условий отбора «Вычисляемый критерий» — нужно найти квартиросъемщиков, количество членов семьи которых равно 2 и цена за услуги которых больше среднего значения всех цен. Для того чтобы это произвести, необходимо скопировать имена полей на этот же лист и далее после имен полей в первую строку необходимо ввести: в столбец «Количество членов семьи» — точное значение 2. Также необходимо переименовать в области критерия столбец «Цена» на «Цена1», а затем в данный столбец «Цена1» ввести вычисляемый критерий =G4>СРЗНАЧ ($G$ 4:$G$ 23) где «G4» — адрес первой ячейки цены в исходном списке, «$G$ 4:$G$ 23» — блок ячеек с ценой. Для вычисления необходимо применить функцию СРЗНАЧ (). После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ — результат применения сформированного вычисляемого критерия по отношению к первой записи списка. Формулу критерия можно просмотреть лишь в строке формул. Затем производится фильтрация записей на этом же листе «Расширенный фильтр», для этого устанавливаем курсор в область списка, выполняя команду «Данные» — «Фильтр» — «Расширенный фильтр» и аналогично отбору записей по «Критерию сравнения» задаем параметры. В результате отбора были найдены квартиросъемщики Иванов, Кораблев и Токарев. Полученные данные представлены на рисунке. Таблица «Расширенный фильтр „Вычисляемый критерий“» 1.4 Структурирование Структурирование — группирование строк и столбцов, открытие или закрытие этих групп. Прежде чем структурировать таблицу необходимо провести сортировку записей. Затем уже можно выбрать один из способов структурирования. Их два — автоматический и ручной. В курсовой работе применяется ручной способ структурирования. 1.4.1 Ручной способ структурирования Данный способ структурирования используется в том случае, когда невозможно применить автоструктурирование или нужно произвести структурирование определенного вида. В этом случае, нужно самостоятельно определять какие строки или столбцы необходимо структурировать. А затем выполнить команду: «Данные» — «Группа и Структура» — «Группировать». Но чтобы произвести структурирование, для начала нужно отсортировать данные. В курсовой работе была произведена сортировка по следующим полям — по полю «Льготы» (по возрастанию), по полю «Вид услуги» (по возрастанию). Затем вставлены пустые строки между различными льготами, чтобы группировать их по видам. Потом необходимо создать структурные части таблицы, их в данном случае будет три. Создание первого уровня — для начала, мы выделяем всю таблицу и выполняем следующую «Данные» — «Группа и Структура» — «Группировать». В появившемся окне установите флажок «Строки». Далее создаем второй уровень структурирования — выделяем блоки ячеек с одинаковыми наименованиями льгот и выполняем команду «Данные» «Группа и Структура» — «Группировать». В данном случае группируются наименования льгот — «Ветеран», «Герой труда», «Инвалид». Третий уровень структуры создается по полю «Вид услуги» в структуре по наименованию вил услуг «За газ». Выделяем блок ячеек и затем выполняем команду «Данные» — «Группа и Структура» — «Группировать». В появившемся окне нужно установить флажок «Строки» и нажимаем кнопку «ОК». Закрыть и открыть созданные структурные части таблицы, нажимая на кнопки «Минус» или «Плюс». Трехуровневое структурирование представлено на рисунке. Таблица 6 «Ручной способ структурирования» 1.5 Подведение итогов Данная функция позволяет выполнить детальный анализ сводных показателей для групп данных, объединенных каким-либо общим признаком. Для формирования итогов, в первую очередь, необходимо отсортировать исходные данные по желаемому признаку. В данном случае была произведена сортировка по полю «Льготы» (по возрастанию), а затем по полю «Стоимость с учетом льгот» (по возрастанию). Затем был создан первый уровень итогов — сумма по полю «Стоимость с учетом льгот» по каждому наименованию льготы. Для этого необходимо было в произвольную ячейку списка записей установить курсор и выполнить команду «Данные» — «Итоги»; в диалоговом окне «Промежуточные итоги» необходимо указать: В строке «При каждом изменении в» — «Льготы»; В строке «Операция»: «Сумма»; В строке «Добавить итоги по»: «Стоимость с учетом льгот»; В строке «Заменять текущие итоги в» — «нет»; В строке «Конец строки» — «нет»; В строке «Итоги под данными» — «да». Полученные данные представлены на рисунке. Таблица 7 «Подведение итогов» 1.6 Консолидация данных Консолидация — это объединение данных, представленных в исходных областях — источниках. Данный инструмент позволяет объединять таблицы, находящиеся в разных местах и даже в разных книгах. Для этого все таблицы должны иметь абсолютно идентичную структуру. Существует несколько вариантов консолидации данных: · с помощью формул, где используются ссылки на источники таблицы; · по расположению данных для одинаково организованных областей-источников; · по категориям для различающихся по своей структуре областей данных; · консолидация внешних данных; · консолидация сводной таблицы. Операция Консолидация осуществляется при помощи следующей команды: «Данные» — «Консолидация». Консолидированная таблица в данном случае используется для нахождения сумм значений по таким полям, как: · Цена; · Стоимость с учетом льгот; · Долг за предыдущий период; · Пени; · Оплатить. Для создания консолидированной таблицы необходимо установить курсор в ячейку А2. Затем нужно выполнить команды «Данные» — «Консолидация», в диалоговом окне «Консолидация» выбрать из списка функцию «Сумма» и установить флажки в строках «подписи верхней строки» и «значения левого столбца». После этого необходимо установить курсор в окне «Ссылка» и ввести диапазон, в котором находится таблица с данными, в данном случае — «'Исходная таблица 1'!$A$ 2:$H$ 22» и «'Исходная таблица 2'!$A$ 2:$H$ 22». Затем нужно нажать кнопку «Добавить», в окне «Список диапазонов» появится ссылка на выделенный диапазон. В результате появится консолидированная таблица, представленная на рисунке. Таблица 8 «Консолидация данных» 1.7 Построение сводных таблиц Инструмент «Сводная таблица» является удобным средством анализа и трансформации данных. С его помощью можно сделать практически любой разрез таблицы и получить итоги по любым данным. Сводная таблица во многом объединяет возможности других обобщающих инструментов, таких как «Итоги», «Фильтр», «Консолидация» (11, "https://referat.bookap.info"). С помощью сводной таблицы можно произвольным образом изменить структуру исходных данных, располагая заголовки строк и столбцов удобным образом, а также получить все необходимые промежуточные итоги и сортировки. Сводная таблица обслуживается более мощным инструментом обработки данных, который называется «Мастером сводных таблиц», который запускается вводом команд «Данные» — «Сводная таблица». Построение сводной таблицы осуществляется в 4 этапа следующим образом: Этап 1. Прежде всего, сначала следует указать вид источника данных. В данном случае в качестве источника используется текущая таблица. Поэтому в строке «Создать таблицу на основе данных, находящихся», выбираем кнопку «в списке или базе данных Excel», а затем щелкаем по кнопке «Далее». Этап 2. На данном этапе следует указать адреса исходных данных (список должен обязательно содержать имена полей (столбцов)). Полное имя диапазона ячеек записывается в виде «[имя_книги]имя_листа!диапазон ячеек». Запрос появляется при нажатии кнопки «Далее». В данном случае в качестве данных используется исходная таблица «Подсчет данных по формулам» предварительно перенесенная на лист, где проводится создание сводной таблицы, это лист под названием «Сводная таблица». Этап 3. На данном этапе осуществляется построение структуры сводной таблицы. На этом этапе необходимо построить макет сводной таблицы. Структура сводной таблицы состоит из следующих областей: · «Страница» — размещаются поля, значения которых обеспечивают отбор записей на 1-ом уровне. · «Столбец» — размещаются слева на право поля, обеспечивая группировку данных сводной таблицы по иерархии полей. · «Строка» — поля размещаются сверху вниз, обеспечивая группировку данных сводной таблицы по иерархии полей. · «Данные» — поля, по которым подводятся итоги, согласно выбранной функции. В поле «Столбец» данной таблицы размещается поле «Количество членов семьи», а в поле «Строка» данной таблицы размещаются поля «Льгота» и «Вид услуги». В поле «Данные» содержатся поля сумма по полю «Цена». Именно по этому полю осуществляется подсчет данных по каждому виду услуги, которую оплачивает определенный льготник. Этап 4. Данный этап является последним при создании сводной таблицы. Он заключается в указании местоположения готовой сводной таблицы. В данном случае существует два возможных варианта: · Новый лист; · Существующий лист. В курсовой работе сводная таблица помещается на существующий лист. Сводная таблица представлена на рисунке. Таблица 9 «Сводная таблица» 1.8 Диаграмма Диаграмма является графическим представление данных. Диаграммы используются для анализа и сравнения данных, представления их в наглядном виде. Диаграмма создается при помощи «Мастера диаграмм». Для запуска «Мастера диаграмм», необходимо осуществить команды «Вставка» — «Диаграмма». Построение диаграммы при помощи «Мастера диаграмм» в интерактивном режиме осуществляется при помощи 4 этапов. Но это только для выделенного блока ячеек — диапазона данных построения диаграммы. Цель построения диаграммы в курсовой работе — наглядно продемонстрировать стоимость с учетом льгот для каждого квартиросъемщика. Этап 1. На первом этапе осуществляется выбор типа диаграммы. «Мастер диаграмм» позволяет строить диаграммы 14 стандартных типов плоскостного и объемного представления и 22 нестандартных. Выбираем стандартный тип диаграммы — «Гистограмма», а вид гистограммы — «Обычная гистограмма». Этап 2. Данный этап заключается в указании источника данных. В данном случае источником данных является таблица, расположенная на этом же листе. Диапазоном данных в данном случае являются столбец «Квартиросъемщик», а также столбец «Стоимость с учетом льгот» с данными цен на услуги. Этап 3. На данном этапе указываем параметры диаграммы. Итак, название оси X — «Фамилия квартиросъемщика», название оси Y — «Стоимость с учетом льгот, руб.», название диаграммы — «Стоимость с учетом льгот для каждого квартиросъемщика». Этап 4. На данном этапе происходит выбор места размещения диаграммы. В данном случае, диаграмма была размещена на листе «Диаграмма». Полученные данные представлены на рисунке. Таблица 10 «Диаграмма» 1.9 Гиперссылка Гиперссылка создается на листе «Указатели». Теперь обоснуем как, к примеру, создавался указатель «Подсчет данных по формулам»: ставим курсор в ячейке А1 и печатаем текст «Подсчет данных по формулам». Далее нажимаем правой кнопкой мыши и в появившемся окне выбираем «Гиперссылка». В строке «Связать с»: выбираем «Местом в документе»; В строке «Текст»: пишем название указателя или оно там же написано; В строке «Введите адрес ячейки»: ставим адрес ячейки, в которой будет располагаться адрес указателя. В нашем случае — «А1»; В строке «Или выберите место в документе»: выбираем «Подсчет данных по формулам». И щелкаем «ОК». Указатель готов. Аналогично создаем указатели и для других листов. Полученные данные представлены на рисунке «Указатели». 2. Задание II. Применение приложения Access для решения задач в различных предметных областях Прежде чем приступить к работе с Microsoft Access, необходимо перенести ранее созданную в Microsoft Excel таблицу «Подсчет данных по формулам». Для этого необходимо импортировать таблицу из Microsoft Excel в Microsoft Access. Это осуществляется следующим образом: в окне Базы данных необходимо выбрать вкладку «Таблицы», затем нажать правую кнопку мыши, выбрать «Импорт». Затем появляется окно «Импорт», где необходимо выбрать нужный документ, т. е. документ Excel «Курсовая работа» и нажать кнопку «Импорт». После этого в окне «Импорт электронной таблицы» выбираем нужную нам таблицу и импортируем ее. Но нам нужны не все столбцы данной таблицы, а лишь только те, которые являются первичными, а это — «Квартиросъемщик», «Адрес», «Количество членов семьи», «Льготы», «Вид услуги», «Отчетный период», «Цена», «Долго за предыдущий период», «Пени». Следовательно, в Excel необходимо создать таблицу с невычисляемыми полями и выбрать эту таблицу. Далее можно начинать работу непосредственно в самом редакторе Microsoft Access. 2.1 Создание структуры таблицы Для начала необходимо создать таблицу: в таблице создать структуру, присвоить имя полям, при необходимости изменить размер и формат поля. В окне «Таблица для БД» необходимо выбрать пункт «Конструктор» или выполнить команду «Вид» — «Конструктор» и щелкнуть «ОК». В результате проделанных операций открывается окно таблицы в режиме конструктора, в котором следует определить поля таблицы. Для поля «Квартиросъемщик» установить тип данных — текстовый и размер поля — 10, для поля «Адрес» — текстовый и размер поля равен 25, для поля «Количество членов семьи» — числовой и целое, для поля «Льготы» — тип данных текстовый и 15, для поля «Вид услуги» — текстовый и 15, для поля «Отчетный период» — текстовый и 20, для полей «Цена», «Долг за предыдущий период» и «Пени» типом данных является денежный. Также изменяем ширину каждого поля таблицы в соответствии с шириной данных, для этого необходимо выполнить команду «Формат» — «Ширина столбца», а затем в появившемся окне щелкаем по кнопке «По ширине данных». В результате ширина поля изменится. Затем необходимо перейти в режим «Таблица», воспользовавшись командой «Вид» — «Режим таблицы». На вопрос о сохранении таблицы необходимо щелкнуть по кнопке «Да». Полученная таблица представлена на рисунке 1. «ЖКХ». 2.2 Сортировка Сортировка данных в MS Access производится аналогично сортировке в Excel. Для этого ставим курсор в нужную ячейку и сортируем данные при помощи команды «Записи» — «Сортировка» — «По возрастанию». В курсовой работе поле «Количество членов семьи» сортируется по возрастанию. 2.3 Фильтрация данных Для фильтрации данных ставим курсор в ячейку, в которой находится значение, по которому нужно отфильтровать данные. Далее выполняем команду «Записи» — «Фильтр» — «Фильтр по выделенному». И получаем отфильтрованные данные. В курсовой работе данные были отфильтрованы по полю «Льготы». Полученные данные представлены на рисунке 2. «Сортировка и фильтрация». 2.4 Создание формы. Ввод и просмотр данных посредством формы Форма — настраиваемые диалоговые окна, сохраняемые в базах данных в виде объектов специального типа. Формы содержат элементы управления, с помощью которых осуществляется доступ к данным таблицы. Для начала необходимо открыть вкладку «Формы» в окне базы данных, затем щелкнуть по кнопке «Создать». В появившемся окне выбрать пункт «Мастер форм», затем щелкнуть по значку списка в нижней части окна, выбрать из появившегося списка таблицу «Исходная таблица», щелкнуть по кнопке «ОК». В появившемся окне выбрать поля, которые будут присутствовать в форме. В создаваемой форме должны присутствовать все поля, поэтому необходимо щелкнуть по кнопке «. Затем нужно щелкнуть по кнопке «Далее». В появившемся окне необходимо выбрать вид «В столбец», затем нажать на кнопку «Далее». В появившемся окне необходимо выбрать стиль оформления, в данном случае «Яркий», после этого щелкнуть по кнопке «Далее». В появившемся окне задать имя формы, набрав на клавиатуре параметр «Форма», затем щелкнуть по кнопке «Готово». В результате откроется форма. Для перемещения по записям необходимо щелкать по стрелкам, находящимся в нижней части окна параметра «Запись». Также создаем вычисляемые поля, которые нужны для подсчета данных, в нашем случае — «Итоговая цена» и «Текущая дата». Для их создания необходимо для начала перейти в режим «Конструктор», выполнить команду «Вид» — «Панель элементов», на этой панели щелкнуть по кнопке «ab» — элемент управления «Поле», создать прямоугольник для надписи, имени поля, ввести надпись «Итоговая цена», и затем в область данных необходимо ввести формулу для подсчета итоговой цены. Для этого необходимо нажать правую кнопку мыши и выбрать «Свойства». В окне «Свойства» выбрать вкладку «Данные», затем в строке «Данные» необходимо нажать кнопку …, после чего появится окно Построитель выражений, где необходимо ввести формулу для подсчета стоимости с учетом льгот =[Цена]-(IIf ([Льготы]="Ветеран" ;100;IIf ([Льготы]="Инвалид" ;50;IIf ([Льготы]="Герой труда" ;40;" 0″)))) Затем нажать кнопку «ОК». После этого перейти в режим таблицы, где уже посчитана стоимость с учетом льгот. Для создания вычисляемого поля «Текущая дата» действуем точно также, но только выбираем функцию: =Date (). Она также как и в Excel будет выводиться автоматически. Полученные данные представлены на рисунке 3. «Форма». 2.5 Формирование запросов Запрос — инструкция на отбор записей. Запросы используются для просмотра, изменения и анализа данных. Запросы можно создавать самостоятельно в режиме «Конструктора» и с помощью «Мастеров запроса». «Мастера запросов» автоматически выполняют основные действия в зависимости от ответов пользователя на поставленные вопросы. В Ассеss можно создавать следующие типы запросов: · «запрос на выборку» — предназначенный для отбора данных, хранящихся в таблицах, и не изменяющий эти данные; · «запрос с параметрами» — позволяющий определить одно или несколько условий отбора во время выполнения запроса; · «перекрестный запрос»; · «запрос на изменение» — запрос на удаление, обновление и добавление записей, на создание таблицы. На основе запроса можно построить отчет или форму. 2.5.1 Простой запрос Данный запрос используется наиболее часто и является самым простым. При его выполнении данные, удовлетворяющие условиям отбора, выбираются из одной или из нескольких таблиц и выводятся в определенном порядке. Простые запросы на выборку практически не отличаются от фильтров. Более того, фильтры можно сохранять как запросы. Для создания запроса на выборку, в котором должны отражаться поля «Квартиросъемщик», «Вид услуги», «Цена», «Долг за предыдущий период» и «Пени», необходимо для начала в окне базы данных открыть вкладку «Запросы». В открывшемся окне затем необходимо щелкнуть по кнопке «Создать», из появившихся пунктов окна «Новый запрос» выбрать «Простой запрос» и щелкнуть по кнопке «ОК», в появившемся окне в строке «Таблицы/Запросы» выбрать «Таблица для БД». В окне «Доступные поля» выбрать «Квартиросъемщик», «Вид услуги», «Цена», «Долг за предыдущий период» и «Пени», и затем щелкнуть по кнопке «Далее», в строке параметра «Задайте имя запроса» ввести новое имя «Данные по оплате услуг квартиросъемщиков (Простой запрос)», затем щелкнуть по кнопке «Готово». В результате на экране появится таблица с результатами запроса. Далее необходимо сохранить запрос, для этого выполним команду «Файл» — «Сохранить». Полученные данные представлены на рисунке 4. «Данные по оплате услуг квартиросъемщиков (Простой запрос)». 2.5.2 Запрос с параметром Запрос с параметром — это запрос, при выполнении которого в его диалоговом окне пользователю выдается приглашение ввести данные, на основе которых будет выполняться запрос. При создании запроса с параметрами действуем так же, как и при создании простого запроса. После создания запроса выполняем команду «Вид» — «Конструктор», в строке параметра «Условия отбора» для поля «Квартиросъемщик» вводим фразу: [Введите фамилию квартиросъемщика], затем выполним команду «Запрос» — «Запуск», в появившемся окне введем фамилию квартиросъемщика и нажмем кнопку «ОК». На экране появится таблица, в которой представлены данные о квартиросъемщике, фамилию которого ввели. После этого необходимо сохранить запрос. Полученные данные представлены на рисунке 5. «Данные по квартиросъемщикам (Запрос на выборку с параметром)». 2.5.3. Перекрестный запрос Перекрестный запрос — запрос, в котором отображаются результаты статистических расчетов, (например, подсчитывается сумма, среднее, число значений), выполненных по данным из одного поля таблицы. Наиболее часто запросы используются для того, чтобы соединить в одной таблице на экране данные, которые реально размещаются в разных таблицах. Для этого таблицы необходимо соединить специальным образом. Такие таблицы должны иметь поля с совпадающими значениями. Для того, чтобы создать «Перекрестный запрос», итоги в котором будут определяться по полю «Цена» по каждому заказчику, нужно выбрать объект «Запросы» и нажать клавишу «Создать». В появившемся окне выбрать вид запроса — «Перекрестный запрос». Выберем за основу для создания запроса таблицу «Исходная таблица» и нажмем «Далее». Далее выберем из доступных полей следующее — «Квартиросъемщик», и нажмем кнопку «Далее». Далее в открывшемся окне из доступных полей выберем поле «Вид услуги» и «Далее». В появившемся окне выберем поле «Цена»" и функцию — «Сумма» и «Далее». Далее задаем имя запросу «Цена за услуги (Перекрестный запрос)» и нажимаем кнопку «Готово». Запрос готов. Полученные данные представлены на рисунке 6. «Цена за услуги (Перекрестный запрос)». 2.6 Создание отчета с группированием Отчеты позволяют выбрать из базы данных требуемую пользователем информацию и оформить ее в виде документов, которые можно просмотреть и отпечатать. Источником данных для отчета может быть таблица или запрос. Кроме данных, полученных из таблиц, в отчете могут отображаться вычисленные по исходным данным значения, например, итоговые суммы. Отчеты в отличие от форм не предназначены для ввода и правки данных в таблицах. Они позволяют лишь просматривать и печатать данные Можно разработать отчет самостоятельно или создать его с помощью «Мастера». После вызова «Мастера» выводятся диалоговые окна с предложением ввести необходимые данные, и отчет создается на основании ответов пользователя. После этого можно переключиться в режим «Конструктора» и внести изменения в стандартный макет. Для создания отчета с группированием по фамилии заказчика необходимо открыть вкладку «Отчеты» и щелкнуть по кнопке «Создать». В открывшемся окне выбрать пункт «Мастер отчетов». Затем необходимо щелкнуть по значку раскрывающегося списка в нижней части окна и выбрать из появившегося списка таблицу «Исходная таблица». В появившемся окне выбрать поля, которые будут присутствовать в форме. В данном случае такими полями являются «Квартиросъемщик», «Адрес», «Количество членов семьи», «Льготы», «Вид услуги», «Отчетный период», «Цена», «Долго за предыдущий период», «Пени». Затем нужно щелкнуть «Далее», в появившемся окне присутствует перечень полей, необходимо перевести выделение на поле «Льготы», тем самым будет задана группировка данных по льготам квартиросъемщиков. Microsoft Access предлагает несколько стилей оформления отчета. В данном случае был выбран «Яркий». Отчету было присвоено имя «Отчет с группированием по льготам». Полученные данные представлены на рисунке 7. «Отчет с группированием по льготам». 2.7 Создание кнопочной формы Согласно заданию необходимо разработать «Кнопочную форму» — меню для работы с базами данных, в которой должны быть созданные формы и отчеты. Но для того, чтобы создать «Кнопочную форму», для начала нужно создать «Макросы». «Макрос» — набор макрокоманд, который создается для автоматизации часто выполняемых задач. Группа макросов позволяет выполнить несколько задач одновременно. Создадим четыре макроса — «Данные ЖКХ», «Личные данные квартиросъемщиков» «Оплата услуг», «Цена за услуги». Для их создания в окне базы данных выбираем объект «Макросы», далее «Создать». Появляется окно с макрокомандами и примечаниями. В поле Макрокомандах из списка выбираем создаваемые макросы. Теперь для быстрой и эффективной работы пользователя с базой данных создадим «Кнопочную форму». Для этого в главном меню выбираем «Сервис — «Служебные программы» — «Диспетчер кнопочных форм». Так как кнопочной формы пока в базе данных нет, то диспетчер выдает запрос на создание кнопочной формы, на который следует ответить утвердительно. После того, как Access откроет диалоговое окно операций над кнопочной формой, причем активной (и единственной) является Главная кнопочная форма (по умолчанию). Для создания иерархической кнопочной формы в диалоговом окне создадим 7 элементов главной кнопочной формы «Данные ЖКХ», «Личные данные квартиросъемщиков» «Оплата услуг», «Цена за услуги», «Льготы», «Квартиросъемщики» и «Выход». Также вставим рисунок в «Кнопочную форму». Откроем режим «Конструктора» и вставим картинку. Полученные данные представлены на рисунке 8. «Кнопочная форма». Заключение В данной курсовой работе был рассмотрен процесс проектирования базы данных. Так как современные информационные системы основаны на концепции и интеграции данных, характеризующихся сложной организацией, необходимостью удовлетворять разнообразные требования многочисленных пользователей и т. д., то для управления этими данными и обеспечения эффективности доступа к ним были созданы системы управления данными. Можно отметить, что были достигнуты раннее поставленные задачи по проектированию базы данных. Также была выполнена цель курсовой работы, а именно, мы смогли применить полученные теоретические знания в реальной ситуации, и получили необходимые навыки по работе с MS Access и Excel. |