Создание сводной таблицы на основе списка или базы данных Microsoft Excel

Размещено на http://www.allbest.ru/

РЕКОМЕНДАЦИИ ДЛЯ ВЫПОЛНЕНИЯ

КОНТРОЛЬНОЙ И КУРСОВОЙ РАБОТЫ

Оглавление

Глава 1. Информатика

1.1 Электронные таблицы

1.2 Создание презентаций с помощью MS Power Point

Глава 2. Информационные технологии

2.1 Базы данных СУБД ACCESS

Рекомендуемая литература

Приложение

Глава 1. Информатика

1.1 Электронные таблицы

l Электронные таблицы и принципы их использования. Форматы электронных таблиц. Вычисления в электронных таблицах. Графическое представление табличных данных, изготовление диаграмм (деловая графика). Электронная таблица Excel. Обмен данными.

l Работа со списком, фильтрация данных. Создание сводной таблицы на основе списка или базы данных Microsoft Excel. Консолидация данных из нескольких интервалов в сводной таблице.

l Решение задач путем анализа данных. Решение задач «что - если». Поиск значения параметра, удовлетворяющего формулам. Проектирование вычислений с помощью таблицы данных.

1.2 Создание презентаций с помощью MS Power Point

l Создание и сохранение презентации. Мастер презентаций. Шаблоны дизайна.

l Добавление слайдов. Общие операции со слайдами. Форматирование и образцы. Вставка в слайд объектов (картинки, таблицы, диаграммы). Режим слайдов. Сортировщик слайдов.

l Анимация и звук. Задание эффекта при переходе слайдов. Задание порядка и эффекта анимации. Вставка анимированного изображения. Создание звукозаписи.

l Действия. Вставка элементов управления. Создание разделов. Вставка гиперссылки. Установка параметров воспроизведения презентации.

Контрольные задания

Задания 1 - 3 выполняются средствами процессора MS Excel. Для выполнения данных заданий необходимы начальные знания, умения и навыки по работе в MS Excel (ввод данных в таблицы, форматирование и редактирование, правила составления формул, работа со стандартными функциями, технология построения диаграмм, работа со справочной системой).

Цель данного задания - из набора встроенных функций MS Excel выбрать те, которые бы обеспечивали оптимальное решение задачи. Как правило, функцию нужно искать в той категории, задачу раздела которой вы решаете. Например, финансовые вычисления можно произвести достаточно просто и быстро, применяя встроенные функции MS Excel из категории «Финансовые». Для поиска нужной функции и работы с ней рекомендуется использовать Справочную систему MS Excel.

Некоторые из необходимых функций не входят в ядро Excel и становятся доступны только при загрузке дополнительного пакета анализа из меню Сервис-Надстройки.

В контрольном задании вы должны решить 3 задачи на применение функций в MS Excel ( по одной задачи из каждого раздела). Все 3 задачи решить в одном файле, но на разных листах.

Используя таблицу, выберете свой вариант задания (В1,В2, и т. д.). Например, если последняя цифра в номере вашей зачетной книжки 9, то в вашем варианте вы должны решить задачи 1.5; 2.3; 3.3.

Последняя цифра в номере зачетной книжки

Разделы	В1	В2	В3	В4	В5	В6	В7	В8	В9	В0
Задание 1	1.1	1.2	1.3	1.4	1.5	1.3	1.4	1.2	1.5	1.1
Задание 2	2.1	2.2	2.3	2.4	2.2	2.4	2.1	2.2	2.3	2.1
Задание 3	3.1	3.2	3.3	3.4	3.5	3.6	3.1	3.2	3.3	3.4

Задание 1. Логические функции

1.1. Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000, то 2 %; если объем от 3000 до 10 000 (включая 3000), то 3,5 %; если свыше 10 000 (включая 10 000), то 5 %.

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

1.2. В блоке А1:А10 дана последовательность чисел. Вычислите в блоке В1:В9 разности соседних элементов (из второго элемента вычесть первый, из третьего - второй и т. д.). Если все они положительные, вывести сообщение «Последовательность возрастающая», если все отрицательные - «Последовательность убывающая», если встречаются и положительные, и отрицательные, то «Последовательность неопределенная».

1.3. Введите в таблицу данные:

№ п/п	фио	цех	начисления	Кол-во детей	алименты
1	Иванов	10	1 300 000	2

l Введите 20 записей;

l Рассчитайте алименты, используя функцию «если», если кол-во детей меньше двух, - 25 % в противном случае - 33 %;

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

l С помощью функции фильтр отберите работников, алименты которых составляют от 600 000 до 1 000 000.

1.4. Экзаменатор проверяет письменную работу, состоящую из пяти задач. За каждую задачу он проставляет оценку - целое число от 0 до 4. Создать таблицу, в которой автоматически высчитывается суммарная оценка для каждого студента, а если преподаватель ошибочно вводит нецелое число, появляется предупреждение об ошибке.

1.5. Даны три стороны треугольника a, b, c. Требуется вычислить его площадь по формуле Герона, полупериметр, радиус вписанной и описанной окружностей. Если подкоренное выражение меньше 0, то такие стороны не могут образовать треугольник, поэтому необходимо вывести сообщение «Это не треугольник».

Задание 2. Табличные формулы

2.1. Рассчитать величину начисленных денег и величину выдаваемой премии для каждого сотрудника отдела по формулам для 20 записей.

Начислено=Отработано часов*Тарифная ставка.

Премия=Начислено*Процент премии.

Величина Процент премии - 10 % - хранится в отдельной ячейке Е1. Если сотрудник, отработал больше 20 часов, то назначить премиальный коэффициент равный, 20 %, в противном случае - 10 %. Предположим, что значение 10 % хранится в ячейке Е1, а 20 % хранится в ячейке Е2.

2.2. Требуется спроектировать таблицу. На одном рабочем листе список физических лиц, их совокупный доход за 1998 г. и сумма налога. Ставка подоходного налога определяется следующим образом:

2.3. В блоке А1:А10 расположены числа. Вычислить количество чисел блока:

l являющихся нечетными числами;

l кратных 3 и не кратных 5;

l являющихся квадратами четных чисел.

2.4. Создать таблицу и ввести 20 записей.



Задание 3. Финансовые вычисления

3.1.Рассматриваются две схемы вложения денег ни три года: в начале каждого года под 24 % годовых или в конце года под 36 %. Ежегодно вносится по 4000. Какая схема выгоднее?

3.2. Рассматриваются два варианта покупки недвижимости: заплатить сразу 70 000 рублей или платить ежемесячно по 800 рублей в течение 12 лет при ставке 9 % годовых. Какой вариант наиболее выгоден?

3.3. За какой срок в годах сумма, равна 75 000 $, достигнет 200 000 $ при начислении процентов по сложной ставке 15 % раз в год и поквартально?

3.4. Контракт между фирмой и банком предусматривает, что банк фирме предоставляет в течение 3-х лет кредит ежегодными платежами в размере 1 млн $ в начале каждого года под ставку 10 % годовых. Фирма возвращает долг, выплачивая 1 млн, 2 млн, и 1 млн $ последовательно в конце 3-го, 4-го и 5-го периодов. Выгодна ли эта операция банку?

3.5. Как выгоднее оформить вклад: под 200 % годовых или под 35 %, начисляемых ежеквартально? Учтите, что «внутри» года в первом случае и «внутри» квартала во втором банк начисляет простые проценты. Начиная с какого по счету дня хранить деньги во втором варианте выгоднее?

3.6. В банк помещен вклад в размере 3900 рублей под 50 % годовых. В конце каждого из первых 4-х лет хранения после начисления процентов вкладчик дополнительно вносил на счет одну и ту же фиксированную сумму. К концу пятого года после начисления процентов оказалось, что размер вклада увеличился по сравнению с первоначальным на 725 %. Какую сумму вкладчик ежегодно добавлял к вкладу?

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

В Excel создайте базу данных продаж канцелярских товаров с полями «Фамилия продавца», «Фирма», «Дата продажи», «Покупатель», «Цена», «Количество», «Выручка». База данных должна содержать 50 записей за период с 1991 по 2000 гг. Таблица задает дополнительные задания по вариантам.

Задание 5. Создание презентации

С помощью программы PowerPoint создать презентацию на любую тему:

1. Цветочный магазин;

2. Гастроль артиста;

3. Сеть аптек;

4. Кинофестивали;

5. Бюро путешествий;

6. Автомагазины;

7. Аукцион;

8. Памятники архитектуры;

9. Салон красоты;

10.Магазин велозапчастей.

Требования к презентации:

1. 5-7 слайдов;

2. Поименование каждого слайда;

3. Встроенная анимация;

4. Использование гиперссылок;

5. При демонстрации смена слайдов по времени;

6. Циклическое повторение презентации (автоматическое).

Глава 2. Информационные технологии

2.1 Базы данных СУБД ACCESS

Общие положения

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

Принципы выполнения курсовых работ заключаются в следующем:

· Тщательное и объемное изучение общей и специальной литературы по теме работы с обязательным привлечением современных источников информации;

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

· Использование практического и экспериментального материала, а также проведение сравнительного анализа теории и практики;

· Оформление курсовой работы в соответствии с установленными требованиями.

Курсовые работы представляются на бумажных носителях. Допускается материалы курсовых работ представлять на электронных носителях.

Задания на курсовой проект оформляется по форме, представленной в прил. Б настоящих методических указаний, а также в прил. В в [1].

Основные требования и этапы выполнения курсовой работы

Пояснительная записка выполняется на формах 5 и 5а по ГОСТ 2.106 [4], размещаемых на одной или обеих сторонах листа белой бумаги формата А4 (210х297). Включаемые в пояснительную записку в качестве иллюстраций чертежи, схемы и таблицы допускается выполнять на листах формата А3, складываемых до размера формата А4.

Текст пояснительной записки должен быть выполнен следующим способом: на электронных носителях данных (ГОСТ 28388); шрифт Times New Roman, размер шрифта 14, междустрочный интервал «одинарный». Расстояние от рамки формы до границ текста в начале и конце строк ? не менее 3 мм, вверху и внизу ? не менее 10 мм.

Титульный лист выполняется по форме, предусмотренной ГОСТ 2.105 (пример заполнения приводится в прил. Б, В

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

Все расчеты должны быть выполнены в единицах СИ или других допущенных к применению ГОСТ 8.417.

Дополнительные требования к курсовому проекту

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

2. Курсовая работа должна носить творческий характер и демонстрировать умение студента делать собственные выводы на основе изученной литературы.

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

4. Теоретические положения необходимо подкреплять конкретными примерами и фактами.

5. Недопустимо дословное заимствование текста из используемых литературных и иных источников.

6. Работа должна быть написана простым и ясным языком с четкими формулировками ее основных положений.

7. При оформлении работы необходимо руководствоваться настоящими методическими рекомендациями, а также требованиями ГОСТ [2-4] 2.105, 2.106, 7.32 - 2001, а также СТО ИрГТУ.005 - 2015 [1].

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

Основные этапы выполнения курсовой работы

1. Подготовительный этап, на котором выполняются следующие шаги:

· ознакомление с методическими рекомендациями по выполнению курсовой работы;

· подбор литературы и составление библиографии по выбранному заданию (не менее 10 - 15 источников, преимущественно не старше 5 - 7 лет);

· составление плана курсовой работы;

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

2. Основной этап, включающий в себя:

· сбор фактического, статистического и иллюстративного материала и его оформление (в виде рисунков, схем, диаграмм);

· литературное изложение изученного и проанализированного материала в соответствии с планом курсовой работы.

3. Итоговый этап, завершающий выполнение работы:

· оформление курсовой работы и сдача ее для проверки преподавателем;

· ознакомление с результатами проверки и подготовка к защите;

· защита курсовой работы.

Структура и объем курсовой работы

Пояснительная записка должна включать в себя следующие структурные части в указанной последовательности:

· титульный лист;

· задание на курсовую работу;

· аннотацию (с указанием текстового редактора и программного продукта, использованного при выполнении работы);

· оглавление;

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

· основную часть;

· теоретический материал по теме курсовой работы;

· практическую часть, содержащую решение поставленной задачи (задач);

· заключение;

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

Объем курсовой работы должен составлять 30-40 страниц печатного текста.

Во введении к курсовой работе должны быть отражены следующие основные вопросы:

· общая формулировка проблемы или постановка задачи;

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

· характеристика предмета, объекта, целей, задач и выбранных методов исследования;

· краткая аннотация последующих разделов работы.

Объем введения обычно не превышает двух-трех страниц.

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

Основную часть рекомендуется разделить на теоретическую часть, включающую обзор материала по теме курсовой работы, и практическую часть, содержащую решение поставленной задачи (задач).

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

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

Защита курсового проекта

В ходе защиты курсовой работы студенту необходимо показать преподавателю владение материалом темы при ответе на вопросы.

Курсовая работа не допускается к защите, если:

· она не носит самостоятельного характера и списана из литературных источников без анализа и выработки собственной точки зрения;

· основные вопросы не раскрыты, изложены схематично и фрагментарно;

· введение и заключение не отражают результатов работы;

· в тексте имеют место содержательные или логические ошибки;

· текст оформлен с грубыми отступлениями от требований Госстандарта;

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

Критерии оценки курсовой работы

Оценка за выполнение курсовой работы выставляется по результатам ее проверки и защиты в соответствии со следующими критериями:

· знание содержания курсовой работы;

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

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

· правильность и полнота ответов на вопросы;

· соответствие оформления работы требованиям Госстандарта

Основные понятия, работа с СУБД Access

1. Основные понятия. Принципы организации базы данных и их использования. Типы баз данных.

2. СУБД MS Access. Объекты в MS Access.

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

4. Работа с формами. Создание форм с помощью мастера и в режиме Конструктора.

5. Выборка записей из БД. Типы запросов. Работа с запросами.

6. Отчеты. Создание отчетов с помощью мастера и в режиме Конструктора. Дизайн форм и отчетов.

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

Создание базы данных с помощью СУБД ACCESS

В основе информационных систем (ИС), предназначенных для хранения и обработки информации, лежит концепция использования баз данных (БД).

База данных - это особым образом организованное хранилище информации, снабженное специальными программами. Эти программы позволяют вводить данные в БД, корректировать их, хранить. Кроме того, они реализуют информационные запросы пользователей, позволяя выбирать (группировать, фильтровать) нужную им информацию. Примером информационной системы может служить справочная правовая система «Консультант Плюс».

Создаются ИС (т. е. базы данных и программы их обслуживания) с помощью специальных инструментальных средств - систем управления базами данных (СУБД). Существует множество различных СУБД. Среди них получили признание так называемые визуализированные СУБД, которые позволяют наглядно, в интерактивном режиме строить нужные пользователю базы данных. К таким можно отнести СУБД ACCESS, входящую в состав Microsoft Office.

В терминологии этой СУБД база данных включает в свой состав различные объекты. К основным из них относятся таблицы, формы, запросы и отчеты.

Таблицы являются основой базы данных, они предназначены для хранения информации. Таблицы состоят из однотипных записей (строки). Записи содержат сведения о единицах хранения. Записи состоят из полей (столбцы). В качестве полей выступают признаки (характеристики), описывающие единицы хранения. Поле определяется именем и типом информации. Приведем примеры некоторых типов информации:

Тип	Назначение, пример
Текстовый	Любые символы: фамилии, адреса, номера телефонов и т. д.
Числовой	Числа, над которыми можно выполнять арифметические операции: цены, суммы, стоимость, количество и т. п.
Денежный	С данными этого типа можно работать, как с числом, но он имеет специфическую внешнюю форму представления
Дата/Время	Представление даты и времени в разных форматах, позволяет выполнять некоторые числовые операции с датами, с предварительным преобразованием их
Логический	Применяется при необходимости односложного ответа на вопрос: Да или Нет
Счетчик	Для подсчета записей в таблице базы данных при создании новой записи значение счетчика автоматически увеличивается на 1
Memo	Используется для записи в базу данных небольших текстов

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

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

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

Задание. Создание базы данных учета сотрудников в отделе кадров

Создадим базу данных (СОТРУДНИКИ) для учета сотрудников некоторой фирмы. Данные о сотрудниках фирмы представлены в прил. (см. табл. 1 и 2). Реквизитов в табл. может быть больше или меньше, в зависимости от потребностей фирмы. Но подход к созданию БД не меняется.

Прежде чем приступить к созданию БД на компьютере, Вы должны хорошенько продумать, какие данные и в какой последовательности Вы хотели бы хранить в базе. Чем лучше продумана структура базы данных изначально, тем меньше придется тратить усилий и времени на ее изменение в процессе работы.

Данные о сотрудниках фирмы можно классифицировать на служебные и личные. Создадим две таблицы, представленные на рисунке 1 и 2 соответственно прил.

При создании БД с помощью СУБД ACCESS можно выделить следующие этапы:

· загрузка ACCESS;

· создание таблиц БД с указанием ключевых полей;

· организация связей между таблицами;

· создание форм для редактирования информации в таблицах;

· создание информационных запросов;

· создание отчетов.

1. Загрузка ACCESS, создание новой базы данных

· Загрузите ACCESS (Пуск\Программы\Microsoft Office\Microsoft ACCESS).

· В появившемся окне справа выберите команду - Создать файл. В новом окне выберите команду Новая база данных.

· В диалоговом окне Файл новой базы данных введите имя файла Сотрудники. Найдите свою папку, создайте в ней папку База.

· Выберите Тип файла База данных Microsoft ACCESS и нажмите кнопку Создать. Появляется диалоговое окно Сотрудники: база данных (формат ACCESS).

2. Создание структуры таблиц БД, установка ключей

· В открывшемся окне выберите кнопку Таблицы в левой части окна. Откроется список режимов создания таблиц.

· Для создания первой таблицы с именем Сотрудники_Служебн базы данных Сотрудники выберем режим Конструктор (ОК). Появляется окно создаваемой таблицы. В режиме Конструктор оно содержит 3 столбца: Имя поля, Тип данных, Описание.

· Задайте теперь структуру этой таблицы. Для этого введите в соответствующие столбцы имена полей и типы данных из вспомогательной табл. 1 прил. Для перехода в следующий столбец пользуйтесь клавишей Tab (рис. 1).

Рис. 1. Диалоговое окно «Сотрудн_Служебн»

Внесение изменений в структуру таблиц

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

Если Вы пропустили какое-то поле, установите курсор на строку, перед которой собираетесь вставить новое поле, и выполните команду Вставка/Строки. Появится новая строка для ввода характеристик поля.

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

Если аналогичные операции необходимо выполнить для группы полей, выделите их предварительно, «протянув» указатель мыши по области маркировки этих полей.

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

· Если Вы не определили первичный ключ, то при сохранении таблицы Access напомнит о его отсутствии и предложит создать его. В таком случае нажмите кнопку Да.

· Сохраните полученную таблицу (Файл/Сохранить).

В соответствующем диалоговом окне присвойте таблице имя Сотрудники_Служебн (ОК). На экране Вы увидите, что в окне Сотрудники: база данных… в режиме Таблицы появился объект Сотрудники_Служебн. Пока эта таблица пустая, но уже определена структура записи, т. е. последовательность полей и типы входной информации.

· Все готово для ввода данных в таблицу. Ввод данных можно сделать в режиме Таблица. Для этого просто щелкните по созданному Вами объекту Сотрудники_Служебн. Откроется окно с таблицей и одной пустой строкой.

· Перенесите данные первых двух записей прил. табл. 1 в таблицу Сотрудники_Служебн.

· Можно улучшить внешний вид таблицы, например, изменив ширину столбцов. Сделайте это аналогично тому, как Вы это делали в EXCEL, или воспользуйтесь командой Формат/Ширина столбца, предварительно выделив нужный столбец.

· Теперь сохраните Вашу таблицу с данными, щелкнув на пиктограмме Сохранить (рис. 2).

Рис. 2 Диалоговое окно «Сотрудн_Служебн»

· Самостоятельно создайте структуру таблицы Сотрудники_Личн и введите данные в соответствии с прил. табл. 2. Сохраните эту таблицу. В режиме Таблицы окна базы данных Сотрудники. Вы должны увидеть оба этих объекта.

Организация связей между таблицами

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

Рассмотрим самый простой вид связи, когда данные из-за объема разделены на две таблицы, несмотря на то, что они описывают одни и те же объекты и имеют одинаковые ключи. Это связь один к одному. Установим такой вид связи между таблицами Сотрудники_Служебн и Сотрудники_Личн.

· Выберите команду Сервис /Схема данных. Откроется окно диалога.

· Откройте контекстное меню и воспользуйтесь командой Добавить таблицу. В соответствующем окне выберите таблицу Сотрудники_Служебн (Добавить), затем аналогично добавьте таблицу Сотрудники_Личн. Закройте это окно.

· В окне диалога Схема данных Вы увидите структуру своих таблиц.

· Теперь, чтобы установить связь между двумя таблицами, нажмите и удерживайте кнопку мыши на поле Код сотрудника таблицы Сотрудники_Служебн. Перенесите это поле в список таблицы Сотрудники_Личн выше поля Код сотрудника. Когда появится маленький прямоугольник, отпустите кнопку мыши. Откроется диалоговое окно: Изменение связей. Левый столбец показывает главную таблицу, правый - подчиненную. Под именами таблиц представлены ключевые поля. В нижней части окна диалога показан тип связи. В данном случае один к одному (рис. 3).

Рис. 3. Схема данных

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

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

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

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

Однако для этой функции разработчики базы стараются предоставить пользователю большие удобства, поэтому они создают такой объект ACCESS, как Форма. Она может использоваться и для ввода данных.

· В диалоговом окне базы данных Сотрудники нажмите кнопку Форма.

· Из предложенных режимов создания форм выберите Создание формы с помощью мастера (так как структура записи в нашей базе не очень сложная нам достаточно возможностей Мастера). Здесь же укажите в качестве источника данных таблицу Сотрудники_Служебн (ОК). Мастер форм открывает окно, в левой части которого указаны поля таблицы.

· Можно создавать форму, позволяющую редактировать все поля таблицы или несколько из них. Давайте создадим форму, которая позволит редактировать все поля всех таблиц сразу. Поэтому воспользуемся значком >> для выбора всех полей таблицы Сотрудники_Служебн. Они будут перенесены в правое окно (рис. 4).



Рис. 4. Создание форм

· Установите теперь в этом же окне (сверху) в качестве источника данных таблицу Сотрудники_Личн и также перенесите все ее поля, кроме Код сотрудника (он уже в форме присутствует), в правую часть окна (кнопка Далее).

· В последующих окнах выберите внешний вид формы - в столбец (Далее). Затем выберите стиль (любой) (Далее). Затем присвойте этой форме имя, например (название Служебн_Личн говорит о том, что этой формой можно пользоваться для редактирования полей обеих таблиц) (Готово). Перед Вами раскроется форма для ввода и редактирования данных в обеих таблицах (рис. 5).

Рис. 5. Диалоговое окно Служебн_Личн

· Выполните команду Файл/Сохранить и закройте окно. Вы снова попадаете в основное окно Сотрудники: база данных, через которое и идет вся работа по созданию базы данных.

· Теперь можно попробовать, как будет действовать созданная форма. Нажмите кнопку Форма и щелкните по созданной форме Служебн_Личн.

· В появившейся форме каждой записи таблицы отведена своя страница, поля расположены в столбец, как мы и заказывали. Очевидно, чтобы переместиться на одну страницу вниз, надо нажать клавишу PgDn. Чтобы вернуться на предыдущую запись - нажать клавишу PgUp. Кроме того, для перехода к определенной записи можно воспользоваться кнопками перемещения в самом конце страницы. Для перемещения по полям записи в форме можно использовать клавиши Tab, Shift + Tab, ,, Home, End.

· Далее переместитесь на первую пустую страницу формы Служебн _ Личн (PgDn). Пользуясь формой, введите 1-2 записи в таблицы Сотрудники_Служебн и Сотрудники_Личн.

Организация подстановок при вводе данных. Использование таблиц-справочников

Если Вы хорошо проанализируете данные в таблицах приложения, Вы заметите, что содержимое некоторых реквизитов повторяется. Это приводит к необходимости неоднократно вводить эти данные при заполнении следующих полей:

· в таблице Сотрудники_Служебн: поля - «Подразделение» и «Должность»;

· в таблице Сотрудники_Личн: поля - «Семейное положение», «Отношение в/о».

Это так называемая условно-постоянная информация. Она изменяется редко. Очевидно, что эту информацию можно ввести один раз, оформив в виде самостоятельных таблиц, и затем использовать в режиме подстановки при заполнении таблиц Сотрудники_Служебн и Сотрудники_Личн.

Такие таблицы называют справочниками. С точки зрения ACCESS, они ничем не отличаются от таких таблиц, как Сотрудники_Служебн и Сотрудники_Личн (их иногда называют основными), различие только в особенностях их использования. Справочники содержат повторяющуюся информацию условно-постоянного характера и облегчают заполнение основных таблиц. Можно сказать, что справочники - это источники условно-постоянной информации, а основные таблицы - ее приемники.

С учетом вышесказанного создадим новую таблицу-справочник, содержащий наименования подразделений, используя при этом информацию прил. А табл. 2. Сделаем это аналогично тому, как создавали таблицу Сотрудники_Служебн и Сотрудники_Личн. Поскольку полей в справочнике будет немного, записывать их во вспомогательные таблицы необязательно. презентация редактирование интерактивный запрос

· В окне Сотрудники: база данных нажмите кнопку Таблицы (при этом уже имеющиеся таблицы не должны быть выделены). В списке режимов создания таблиц выберите режим Конструктор. Появляется окно создаваемой таблицы с тремя столбцами.

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

· Поскольку связь таблицы Сотрудники_Служебн и справочника осуществляется по полю Подразделение, присвойте этому полю признак - Ключевое поле с помощью соответствующей команды контекстного меню.

· Сохраните новую таблицу-справочник с именем Справ_Подразд (Файл/Сохранить). В режиме Таблицы должен появиться новый объект.

· Теперь заполните справочник информацией в соответствии с прил. табл. 3. Она понадобится нам для проверки работы базы данных (отладки). Откройте справочник (двойной щелчок или клавиша Открыть) наименования подразделений. Сохраните файл и закройте окно ввода (рис. 6).



Рис. 6. Справ_Подразд

Для того чтобы при заполнении таблицы Сотрудники_Служебн можно было воспользоваться справочником, эти таблицы должны быть связаны между собой. В структуре основной таблицы нужно указать, что теперь значение поля Подразделение будет не вводиться с клавиатуры, а подставляться из справочника (в дальнейшем такое поле будет называться полем подстановки, или полем со списком). Это можно сделать разными способами, например, таким:

· В режиме Таблицы выделите таблицу Сотрудники_Служебн, вызовите Конструктор нажатием кнопки на панели инструментов окна. В открывшемся окне установите указатель мыши на ячейку структуры таблицы, содержащую тип информации поля Подразделение. Смените этот тип. Щелкните кнопкой мыши, с помощью появившейся кнопки откройте список Тип данных и из предложенных типов выберите Мастер подстановок (он последний в списке) (рис. 7).

Рис. 7. Служебная табица

Работа с Мастером подстановок осуществляется в несколько шагов.

· Первый шаг. Открывается диалоговое окно подстановок. Укажите режим - Столбец подстановки использует значения из таблицы или запросов (клавиша Далее).

· Второй шаг. Укажите таблицу - источник данных, в нашем случае - Справ_Подразд (Далее).

· Третий шаг. Отметьте поле Подразделение в левом окне Доступные поля и с помощью стрелки перенесите его в правое окно. Таким образом, Вы указали поле, которое будет использоваться для подстановки (Далее).

· Четвертый шаг. Отрегулируйте ширину столбца, если это необходимо (Далее).

· Пятый шаг. Можно ничего не менять, если Вы собираетесь использовать прежнее имя поля подстановки. Можно поменять его на новое имя из справочника (Готово). Появляется сообщение о необходимости сохранить таблицу, прежде чем создавать связь. Ответьте - Да.

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

Итак, структура таблицы, при заполнении которой мы хотим пользоваться режимом подстановки, скорректирована. Опробуем теперь этот режим, введем новую запись о следующем сотруднике. Так как запись таблицы Сотрудники_Служебн связана с записью таблицы Сотрудники_Личн, то также надо ввести данные в таблицу Сотрудники_Личн (или заполнить хотя бы только ключевое поле Код сотрудника).

· Итак, откройте таблицу Сотрудники_Личн. Введите данные по одному из сотрудников.

· Затем откройте таблицу Сотрудники_Служебн. Продолжите ввод данных по этому делу. Перейдя в поле Подразделение, щелкните по нему и с помощью появившейся кнопки откройте список подразделений. Он «высвечивается» из соответствующего справочника. Выберите нужное подразделение, содержимое поля справочника будет подставлено в таблицу Сотрудники_Служебн.

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

· Обратите внимание на то, что режим подстановки не появился и в форме Служебн _ Личн, которую Вы сделали ранее. Нажмите кнопку режима Форма, откройте форму Служебн _ Личн и убедитесь в этом.

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

· Удалите существующую форму Служебн_Личн и создайте ее после организации всех подстановок.

Анализ структуры и связей таблиц базы данных

· Теперь рассмотрим состав и структуру создаваемой базы данных в схематическом виде (Сервис/Схема данных).

· В открывшемся окне Вы увидите структуру таблиц и связи между ними (если видны не все таблицы, воспользуйтесь командой контекстного меню, Отобразить все). Вы можете улучшить графическое отображение базы данных, например, изменить размеры таблиц так, чтобы были видны все поля; переместить таблицы так, чтобы связи не накладывались друг на друга. Эти действия выполняются приемами работы в Windows(рис. 8).

Рис. 8. Схема данных

· Сохраните новую схему (Файл/Сохранить). Закройте окно схемы данных.

Самостоятельное задание

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

Заполните справочник информацией (прил.). Для полей Семейное положение и Отношение к в/о сделать Подстановку с использованием Фиксированного списка. После всех действий создайте форму Служебн_Личн, как было описано, посредством Мастера форм.

Информационные запросы и поиск информации в БД. Под Запросом понимается операция отбора информации из таблиц базы данных. Чаще всего запросы создаются для вывода нужной информации на экран (информационный запрос). Создавая запрос, пользователь определяет критерии отбора и форму представления информации.

Создание простого запроса

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

· В окне Сотрудники: база данных. Нажмите кнопку Запросы, выберите режим Создать. Выберите режим Простой запрос (ОК). Укажите последовательно обе таблицы и выберите все поля, которые нужно вывести на экран (операции аналогичны действиям работы с Мастером форм). Дайте имя запросу - Все.

· После нажатия клавиши Готово на экране появляется окно этого запроса, содержащее всю информацию, хранящуюся в базе данных. Проверьте правильность выдачи информации и закройте окно запроса. В режиме Запросы окна Сотрудники: база данных… появляется новый объект - информационный запрос, позволяющий отобразить на экране всю информацию основных таблиц.

· Активизировать запрос можно в любой момент двойным щелчком кнопки мыши. Удаляются запросы аналогично другим объектам базы данных ( команда Удалить пункта меню Правка или контекстного меню) (рис. 9).

Рис. 9. Запрос на выборку

Создание запроса с условиями

Как правило, информация, выводимая по запросу пользователя, отбирается с учетом каких-то условий. Такие запросы в ACCESS можно создавать с помощью Конструктора запросов. Например, мы хотим получить сведения по тем сотрудникам, у которых оклад >12 000 руб., записи должны быть по фамилиям в алфавитном порядке.

При этом записи должны содержать информацию только следующих полей: «Код сотрудника», «Фамилия», «Оклад», «Дата рождения».

· Находясь в режиме Запросы, нажмите кнопку Создать в режиме Конструктора, затем выберите в появившемся окне диалога: Добавление таблицы, выберите таблицу Сотрудники_Служебн и нажмите кнопку Добавить (аналогично добавьте таблицу Сотрудники_Личн). Появится окно Конструктора запросов: Запрос 1: запрос на выборку с выбранными таблицами и связями между ними (кнопка Закрыть).

· Рассмотрим окно Конструктора запроса. В главном меню, помимо уже знакомых вам пунктов, появился пункт Запросы. Панель инструментов также несколько изменилась, появились новые пиктограммы. Обратите внимание на нижнюю часть окна, там расположен Бланк запроса. Каждая строка в этом бланке имеет свое название: Поле, Таблица, Сортировка и т. д. и выполняет определенную функцию. Их мы рассмотрим в процессе создания запроса.

· Укажите поля, которые Вы хотите видеть на экране, в Бланке запроса. Щелкните кнопкой мыши в первой ячейке строки Поле и, открыв список полей, выберите нужное поле соответствующей таблицы. Это будет Код сотрудника. Аналогично укажите последующие поля запроса. Во второй строке Бланка запроса появляется имя таблицы, а в четвертой строке установится значок вывода на экран .

· Теперь в строке Сортировка в поле Фамилия установите опцию (условие) - по возрастанию.

· В строке Условие отбора в поле Оклад занесите условие >12 000.

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

· Закройте окно Конструктора, ответив согласием на предложение Сохранить. Дайте имя созданному запросу - По величине оклада, (клавиша ОК). Во вкладыше Запросы окна Сотрудники: база данных появляется новый информационный запрос, имя которого указывает на критерий отбора данных.

· Проверьте, как работает созданный запрос (двойной щелчок по его пиктограмме). На экране должны появиться записи о сотрудниках, соответствующие заданным критериям отбора (рис. 10).

Рис.10. Запрос на выборку

Создание интерактивного запроса с условием

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

· В качестве примера создадим запрос для выбора сотрудников с высшим образованием (поле Образование). Для упрощения задачи допустим, что будет достаточно получить данные только таких полей, как «Код сотрудника», «Фамилия», «Образование».

· Выполните действия аналогично запросу с условием. С помощью Конструктора добавьте нужную таблицу. Укажите нужные поля в той последовательности, которая, на Ваш взгляд, удобна для пользователя. Укажите условие сортировки записей по любому выбранному Вами полю.

· Новым будет следующее действие - в строке Условие отбора в поле Образование введите следующую фразу в квадратных скобках [Введите вид образования]. Эти слова и будут выведены на экран как приглашение для ввода вида образования. Закройте окно, сохранив запрос с именем - По виду образования.

· Проверьте работу запроса, активизируя его двойным щелчком. В диалоге введите вид образования. Учтите, что название должно быть введено без ошибок, иначе отбор записей не произойдет. Если запрос создан правильно, на экране должны появиться записи, удовлетворяющие этому условию (рис. 11).

Рис. 11. Запрос по образованию

О разделе имущ О признании зав; О признании нед О взыскании авт. О возмещении ч О признании пра О взыскании мат О принудительнс

Создание отчетов для наглядного отображения данных при печати

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

Создание простого отчета

Итак, если мы хотим распечатать сведения по всем делам, хранящимся в базе данных, нам надо оформить уже имеющийся запрос с именем Сотрудники_Служебн в виде отчета. Сделаем это сначала простым способом, пользуясь режимом Автоотчет. Нажмите кнопку Отчеты окна Сотрудники: база данных, нажмите кнопку Создать.

· В появившемся окне Новый отчет выберите режим Автоотчет: в столбец, укажите, что в качестве источника данных будет использоваться запрос Сотрудники_Служебн (ОК). ACCESS автоматически строит отчет, где информация каждой записи располагается в столбец. Рассмотрите этот отчет, но не сохраняйте его. Попробуйте режим Автоотчет: в линейку. Таким образом, вы можете выбрать ту форму отчета, которая вас устраивает. Эти отчеты можно не сохранять.

Создание сложного отчета

Давайте создадим и теперь уже сохраним более сложный отчет с группированием и сортировкой записей также на основе запроса Сотрудники_Служебн. Нажмите кнопку Создать с помощью Мастера отчетов.

· В появившемся окне Новый отчет выберите Мастер отчетов, в качестве источника данных укажите запрос Сотрудники_Служебн. Откроется диалог Создание отчетов (5 шагов) (рис. 12).

Рис. 12. Создание отчета

· Первый шаг этого диалога. Выберите все поля, которые должны присутствовать в отчете (они должны переместиться в правое окно) (кнопка Далее).

· Второй шаг. Укажите поле, по которому записи будут группироваться. При этом можно заказать несколько уровней группировки. Для упрощения задачи считаем, что в нашем отчете достаточно представить данные с одним уровнем группировки по одному из полей. Это может быть поле «Фио» (если Вам нужен отчет по фамилиям), поле «Оклад» (если необходима отчетность по окладам) и т. д. (кнопка Далее) (рис.13).