Файл: Практикум Для студентов вузов Кемерово 2013 4 удк 004 (076) ббк 32. 81я7 И74.pdf
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 02.12.2023
Просмотров: 559
Скачиваний: 8
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
3. Электронные таблицы Excel
161
Рис. 3.33. Настройка расширенного фильтра
26. Переключатель установить в позицию «Скопировать результат в другое место». В этом случае результатом фильтра- ции станет новая таблица, содержащая отобранные записи, и ее можно будет сохранить.
27. Если ТД была активизирована перед запуском коман- ды фильтрации, то в окне ввода «Исходный диапазон» уже бу- дет содержаться ссылка на диапазон ТД. В противном случае надо ввести в окно такую ссылку – установить в окно курсор, затем мышью обвести диапазон ТД, включая заголовки полей.
28. В окно «Диапазон условий» аналогичным образом ввести ссылку на диапазон С27:Е28.
29. В окне «Поместить результат в диапазон» достаточно указать ссылку на ту ячейку, начиная с которой на листе будет размещен результат фильтрации, например А31.
30. Щелкнуть [ОК]. На листе появится результат фильтра- ции: таблица, содержащая строку заголовков и строки с вы- бранными записями.
Эта таблица отражает результат фильтрации для текущего состояния ТД. При сохранении книги она также сохраняется, ее можно отредактировать, скопировать в буфер, перенести в дру- гое место и т.п. При внесении изменений в ТД таблица резуль- татов фильтрации не изменяется, и для получения результатов фильтрации в новых условиях надо заново настроить расширен- ный фильтр (и создать новую, отдельную, таблицу результатов).
3. Электронные таблицы Excel
162
Если ТД фильтруется на месте, окно «Поместить резуль- тат в диапазон» недоступно. Результат фильтрации в этом слу- чае похож на результат автофильтрации и его можно отключить
(вернуть таблице исходный вид), щелкнув «Данные» – «Сорти- ровка и фильтр» – [Фильтр].
Анализ ТД с помощью диаграмм
Для наглядного анализа данных из ТД можно использо- вать диаграммы. Рассмотрим пример: требуется отобразить на круговой диаграмме распределение сумм выплат по отделам фирмы. Соответствующие значения были вычислены в качестве промежуточных итогов после сортировки (лист ТД-2).
Последовательность действий:
31. Перейти на лист ТД-2. Скопировать лист целиком в буфер обмена. Создать лист ТД-6 и вставить в него содержимое буфера.
32. Скрыть в группах строки записей, оставив только строки итогов (см. пункт 18 описания данной работы).
33. Скрыть все столбцы (см. § 3.1), кроме столбцов, со- держащих поля «Отдел» и «Сумма к выдаче».
Таблица примет вид, показанный на рис. 3.34.
34. Выделить диапазон В8:L32 (т.е. таблицу без заголов- ков и общего итога).
35. Выполнить команду «Вставка» – «Диаграммы» – [Кру- говая] – «Круговая». На листе появится круговая диаграмма, сектора которой будут обозначены в легенде именами, взятыми из ячеек диапазона В8:В32.
36. Правым щелчком по кругу диаграммы вызвать контек- стное меню и выбрать «Добавить подписи данных». В секторах появятся значения итоговых сумм по отделам.
37. Правым щелчком по любой из подписей данных вы- звать контекстное меню и выбрать «Формат подписей данных».
В одноименном окне диалога установить флажки «Включить в подписи»: «Имена категорий», «Значения», «Доли». Изменить с помощью переключателя положение подписей на «У вершины, снаружи». Выбрать из списка «Разделитель» – «Новая строка».
Закрыть окно.
3. Электронные таблицы Excel
163
Рис. 3.34. Отражение промежуточных итогов на круговой диаграмме
38. На диаграмме выделить щелчком и удалить легенду
(она не нужна, если имена категорий, т.е. секторов, включены в подписи данных). Отрегулировать мышью размеры области диаграммы и области построения так, чтобы элементы диаграм- мы не наползали друг на друга.
Варианты заданий
Таблица 3.9
Варианты заданий по использованию функций Excel
№
Определяемые характеристики
1
максимальный стаж работы
минимальная тарифная ставка
средняя величина налога
2
максимальный налог
минимальная премия
средняя величина стажа работы (с одним десятич- ным знаком)
3
максимальная надбавка за стаж
минимальная сумма к выдаче
средняя величина начисления
4
максимальная величина начисления
минимальное количество отработанных часов
средняя величина суммы к выдаче
3. Электронные таблицы Excel
164
Окончание табл. 3.9 5
максимальный тариф
минимальная надбавка за стаж
средняя величина премии
6
максимальный заработок по тарифу
минимальный налог
средняя величина количества отработанных часов
7
максимальный стаж работы
минимальная премия
средняя величина суммы к выдаче
8
максимальный тариф
минимальная доплата за стаж
средняя величина стажа
9
максимальное количество отработанных часов
минимальная величина общего начисления
средняя величина тарифа
10
максимальный заработок по тарифу
минимальный налог
средняя величина отработанных часов
11
максимальная премия
минимальная сумма к выдаче
средняя величина заработка по тарифу
12
максимальная надбавка за стаж
минимальная величина стажа
средняя величина премии
13
максимальное общее начисление
минимальный тариф
средняя величина надбавки за стаж
14
максимальный налог
минимальная величина отработанных часов
средняя величина общего начисления
15
максимальная сумма к выдаче
минимальная величина заработка по тарифу
средняя величина налога
1 ... 7 8 9 10 11 12 13 14 ... 19
3. Электронные таблицы Excel
165
Таблица 3.10
Варианты заданий по сортировке и подведению итогов
(Группа для подведения итогов определяется полем первого уровня сортировки)
№
Уровни и порядок сортировки
Итог подводится по полю
Вид итога
1 1 – Отдел, возрастание
2 – Должность, убывание
3 – Ф.И.О., возрастание
Стаж
Среднее
2 1 – Должность, возрастание
2 – Отдел, убывание
3 – Стаж, убывание
Тариф
Минимум
3 1 – Отдел, убывание
2 – Должность, возрастание
3 – Отработал часов, воз- растание
Заработок по та- рифу
Максимум
4 1 – Должность, возрастание
2 – Отдел, убывание
3 – Заработок по тарифу, убывание
Отработал часов
Среднее
5 1 – Отдел, возрастание
2 – Должность, убывание
3 – Премия, возрастание
За стаж
Максимум
6 1 – Должность, возраста- ние
2 – Отдел, убывание
3 – За стаж, убывание
Премия
Сумма
7 1 – Отдел, убывание
2 – Должность, возрастание
3 – Начислено всего, воз- растание
Налог
Максимум
8 1 – Должность, возрастание
2 – Отдел, убывание
3 – Налог, убывание
Начислено всего
Среднее
9 1 – Отдел, возрастание
2 – Должность, убывание
3 – Сумма к выдаче, воз- растание
Сумма к выдаче
Сумма
10 1 – Должность, возрастание
2 – Отдел, убывание
3 – Тариф, убывание
Отработал часов
Сумма
3. Электронные таблицы Excel
166
Окончание табл. 3.10 11 1 – Отдел, убывание
2 – Должность, возрастание
3 – Отработал часов, убы- вание
Заработок по тарифу
Среднее
12 1 – Должность, возрастание
2 – Отдел, убывание
3 – Заработок по тарифу, возрастание
Премия
Минимум
13 1 – Отдел, возрастание
2 – Должность, убывание
3 – Стаж, возрастание
Начислено всего
Сумма
14 1 – Должность, возрастание
2 – Отдел, убывание
3 – За стаж, возрастание
Стаж
Максимум
15 1 – Отдел, убывание
2 – Должность, возрастание
3 – Сумма к выдаче, убы- вание
Отработал часов
Минимум
Таблица 3.11
Варианты заданий по использованию автофильтра
№
Поле и значение критерия отбора записей (лист ТД-3)
Поле и условие отбора записей (лист ТД-4)
1
«Отдел» – Отдел кадров
Стаж больше 15 лет
2
«Должность» – Инженер
Тариф меньше 500 р.
3
«Стаж» – 15
Отработал меньше 30 часов
4
«Тариф» – 850
Заработок по тарифу меньше
25000 р.
5
«Отработал часов» – 36
Премия меньше 3000 р.
6
«Налог» – 0 р.
Надбавка за стаж меньше
5000 р.
7
«Ф.И.О.» – «Петров»
Фамилии, начинающиеся на «Б»
8
«Отдел» – Отдел ИТ
Должности – инженер или про- граммист
9
«Должность» – Секретарь
Сотрудники с фамилиями, на- чинающимися на «К» или «Л»
10
«Тариф» – 750
Отдел – административный или бухгалтерия
3. Электронные таблицы Excel
167
Окончание табл. 3.11 11
«Отработал часов» – 24
Заработок по тарифу от 20000 до 40000 р.
12
«Ф.И.О.» – Сидоров
Сумма к выдаче меньше
20000 р. или больше 40000 р.
13
«Отдел» – Бухгалтерия
Отработал от 20 до 40 часов
14
«Должность» – Начальник отдела
Заплатил налог больше 5000 р.
15
«Тариф» – 450
Надбавка за стаж больше
5000 р.
Таблица 3.12
Варианты заданий по использованию расширенного фильтра
№
Поля и условия отбора записей
1
Сотрудники бухгалтерии с фамилиями на «И»
2
Грузчики со стажем больше 5 лет
3
Сотрудники, отработавшие от 10 до 20 часов
4
Начальники отделов с отработкой менее 36 часов в неделю
5
Сотрудники с фамилиями на «Л», «О» или «С»
6
Инженеры производственного отдела и отдела кадров
7
Сотрудники отдела кадров, не имеющие надбавки за стаж
8
Сотрудники, платящие налог свыше 5000 р. при стаже работы менее 15 лет
9
Сотрудники с фамилиями, начинающимися на «К», «Л», «М»
10
Инженеры со стажем работы свыше 10 лет
11
Инженеры с отработкой более 40 часов в неделю
12
Начальники отделов со стажем больше 12 лет
13
Сотрудники отдела кадров со стажем работы меньше 10 лет
14
Сотрудники отдела ИТ, отработавшие меньше 30 часов
15
Сотрудники со стажем работы менее 10 лет и отработкой меньше 36 часов в неделю
4. Система управления базами данных Access
168
4. СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ
ДАННЫХ ACCESS
4.1. Общие сведения о реляционных базах данных
Базой данных (БД) называется упорядоченный набор све- дений о каком-то множестве объектов (людей, книг, организа- ций и т.п.). Наиболее распространены реляционные БД с таб- личной организацией данных. Для работы с БД применяются специальные программы – системы управления базами данных
(СУБД). В данной работе рассматривается СУБД Access 2007.
Основой БД в Access являются связанные между собой таблицы, которые служат хранилищами данных
8
. Каждая строка таблицы содержит одну запись – набор сведений об одном объ- екте. Записи обо всех объектах в таблице имеют одинаковую структуру и состоят из отдельных полей, содержащих для раз- ных объектов значения одного и того же атрибута (признака).
Значения одного поля для разных объектов хранятся в одном столбце таблицы.
Структура таблиц БД должна подчиняться определенным правилам, определяющим нормальные формы таблиц.
Первая нормальная форма (1НФ) требует, чтобы значения полей в записях были атомарными (неделимыми). Например, фамилия, имя и отчество человека должны рассматриваться как три отдельных атрибута, т.е. помещаться в отдельных полях.
Вторая нормальная форма (2НФ) требует, чтобы:
1) таблица находилась в 1НФ;
2) в таблице имелся первичный ключ – поле, значения ко- торого однозначно определяли бы любую запись.
Каждой записи в таблице должно соответствовать какое- то значение ключа (ключевое поле не может быть пустым), кро- ме того, разные записи не могут иметь одно и то же значение ключа. Если известно значение ключа, то по нему можно безоши- бочно найти в таблице нужную запись и определить значения всех
_______________________
8
Многие понятия и действия в Access аналогичны действиям по анали- зу таблиц данных (ТД) в Excel, поскольку ТД Excel является прообразом про- стейшей реляционной БД.
4. Система управления базами данных Access
169 неключевых полей этой записи (говорят, что неключевые поля связаны функциональной зависимостью с ключевым).
Третья нормальная форма (3НФ) требует, чтобы:
1) таблица находилась в 2НФ;
2) никакие два неключевых поля не были связаны друг с другом функциональной зависимостью (значение одного не- ключевого поля не должно влиять на значение другого).
Анализ требований остальных НФ выходит за рамки дан- ной работы. Требования НФ носят рекомендательный характер.
При их нарушении БД может функционировать, но работа с ней усложняется и возрастает вероятность появления ошибок, вы- званных влиянием человеческого фактора.
Стремление к соблюдению требований 3НФ приводит к тому, что все данные оказывается невозможно собрать в одну таблицу – в нее могут попасть функционально связанные не- ключевые атрибуты. Поэтому данные приходится размещать в нескольких таблицах, связь между которыми устанавливается с помощью ключевых полей.
Если в двух таблицах имеется одинаковое ключевое поле, то каждой записи одной таблицы соответствует одна запись другой таблицы, имеющая такое же значение ключа. Такая связь таблиц называется «один к одному».
Другой тип соединения получается, если какое-то поле является ключевым в одной таблице, и неключевым в другой.
Тогда во второй таблице может существовать несколько записей с одинаковым значением этого поля, и все они будут связаны с одной и той же записью первой таблицы. Эта связь называется
«один ко многим». Неключевое поле второй таблицы, посредст- вом которого осуществляется связь, называется ее внешним
ключом.
Общая структура всех таблиц БД и их связей называется
схемой данных базы.
4.2. Документы базы данных
При работе с БД могут создаваться три типа документов:
1. Форма служит для ввода, просмотра, корректировки и удаления записей. В отличие от выполнения тех же операций непосредственно в таблице, форма обычно показывает пользо-
4. Система управления базами данных Access
170 вателю только одну запись. Представление записи может иметь вид готового бланка какого-то документа (анкеты, ведомости и т.п.) или содержать стандартные управляющие элементы интер- фейса приложений Windows (окна ввода, кнопки, флажки и т.п.).
Форма может также выборочно показывать пользователю толь- ко отдельные поля записи, подлежащие исправлению.
2. Запрос – это инструмент, который служит для выбора и изменения данных. В СУБД Access существуют различные виды запросов, например: а) запрос на выборку позволяет выбрать из таблицы или их совокупности отдельные записи или их фрагменты по како- му-то признаку; б) запрос на изменение служит для автоматического изме- нения содержимого отдельных полей в выбранных записях; в) запрос на создание таблицы позволяет сохранить вы- бранные записи или их фрагменты в отдельной таблице.
Запросы служат основой для создания других типов доку- ментов – форм и отчетов.
3. Отчет – представление данных, полученных на основе запроса в виде оформленного и подготовленного для печати до- кумента (справки, ведомости, приказы и др.).
Access предлагает два режима создания документов: ре- жим Мастера и режим Конструктора. При создании документов в режиме Мастера значительную часть работы по созданию до- кумента берет на себя компьютер, предлагая пользователю го- товые варианты. В режиме Конструктора определение струк- туры и оформление документа осуществляются пользователем.
При создании таблиц данных используются режим табли- цы и режим Конструктора.
Заполнение таблицы может производиться:
1) вручную (в режиме таблицы или с помощью форм).
2) путем импорта данных из другой БД.
3) путем импорта данных из таблицы Excel.
База данных хранится на диске в виде единого файла, имеющего расширение .accdb. Число записей в таблицах огра- ничивается только емкостью диска. Максимальное число полей отдельной таблицы может достигать 255.