Методика и порядок проведения работы. Создание и применение баз данных в электронных
Лаб. Раб. №13
Создание и применение баз данных в электронных
таблицах MS Excel
1. Цель и содержание:знакомство с основными приемами и способами создания баз данных, редактирование, сортировка и выборка в базах данных, подведение итогов и создание сводных таблиц в электронных таблицах Excel.
Теоретическое обоснование
2.1. Построение базы данных.
Общее понятие о списке. Список является аналогом таблицы реляционной базы данных, строки списка соответствуют записям базы данных, столбцы списка – полям записей. Условно список называется базой данных Excel.
Список – прямоугольная область ячеек, в которой строки электронной таблицы имеют фиксированную регулярную структуру заполнения. Во всех строках списка одинаковое использование столбцов – назначение и формат данных, все ячейки в столбце содержат один и тот же тип данных. Верхняя строка списка содержит имена (названия) столбцов. Причем это название может состоять из нескольких слов, обязательно размещенных в одной ячейке.
По отношению к списку записей выполняются различные операции обработки. Для определения списка перед выполнением операций обработки командой ДАННЫЕ достаточно установить курсор в любую ячейку списка. Когда список сформирован неверно или нужно работать с частью области списка, требуется выделение области списка с помощью мыши.
2.1.1. Сортировка данных в списке
Команда ДАННЫЕ>Сортировка позволяет упорядочить строки в списке в соответствии с содержимым определенных столбцов. Для этого нужно выделить сортируемые строки в списке или его части, определить требуемый порядок сортировки. Можно создать и применить пользовательский порядок сортировки.
Сортировка осуществляется в выделенной области, порядок сортировки данных указывается по значениям одного, двух или трех столбцов по возрастанию или убыванию. Причем для результата сортировки существенен порядок выбора столбцов. При сортировке по возрастанию упорядочение записей по значению указанного столбца идет следующим образом – от меньшего к большему, по алфавиту или в хронологическом порядке дат, но при этом имеет место приоритет: числа, текст, логические значения, значения ошибок, пустые ячейки. Сортировка по убыванию использует обратный порядок (исключение пустые ячейки, расположенные в конце списка).
Кроме того, вы можете использовать пользовательский порядок сортировки. При этом программа использует порядок, заданный в созданном пользовательском списке. Пользовательский список, задающий порядок сортировки можно выбрать в окне диалога Параметры сортировкив поле Порядок сортировки по первому ключу. Пользовательский порядок сортировки можно применять только по основному критерию. Задания пользовательского порядка сортировки для поля, указанного в группе Затем по, невозможно.
Фильтрация записей списка
Фильтрация данных в списке позволяет отобразить только те записи, которые соответствуют заданным условиям. Команда ДАННЫЕ>Фильтр обеспечивает выборку подмножества данных из списка. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр.
Автофильтр. При применении автофильтра имена полей будут дополнены раскрывающимися списками, из которых можно выбрать нужное значение поля или задать пользовательский критерий. Применяя пользовательский фильтр, вы можете соединить с помощью логических операторов отдельные условия отбора записей.
Команда ДАННЫЕ>Фильтр>Автофильтр для каждого столбца строит список значений, который используется для заданияусловий фильтрации. По отдельному столбцу можно указать: значение – будут выбраны только записи, которые в данном столбце содержат указанное значение; все – выбираются все записи без ограничений; первые 10 – выбираются первые десять записей; условие – выбирается, если условие фильтрации для значений в столбце более сложное, в результате выбора этого поля появляется окно Пользовательский автофильтр, в котором задаётся условие отбора данных (рис. 1).
Рисунок 1. Окно задания условий фильтрации
Условие для отбора записей по значению в определенном столбце может состоять из одной или из двух самостоятельных частей, соединенных логической связкой И/ИЛИ. Каждая часть условия включает операторы отношения: равно, неравно, больше, больше или равно, меньше, меньше или равно.
Расширенный фильтр. После создания диапазона критериев можно запустить расширенный фильтр и подвергнуть данные списка фильтрации (рис. 2). Вы можете применить в рабочем листе только один расширенный фильтр. Если в результате применения расширенного фильтра не должны быть отображены повторяющиеся записи, в диалоговом окне Расширенный фильтр установите флажок параметра Только уникальные записи.
Рисунок 2. Диалоговое окно Расширенный фильтр
Чтобы после фильтрации задать отображение в рабочем листе всех записей списка, следует выполнить команду ДАННЫЕ>Фильтр>Отобразить всё.
Форма данных.Excel позволяет работать с отдельными записями списка с помощью простой экранной формы для работы со списком записей, основные операции обработки записей списка (последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка).
При установке курсора в область списка и выполнении команды ДАННЫЕ>Формана экран выводится форма, в составе которой имена полей – названия столбцов списка.
Для просмотра записей используется полоса прокрутки либо кнопкиНазад, Далее и выводится индикатор номера записи. При просмотре записей, возможно их редактирование. Поля, не содержащие формул, доступны для редактирования, вычисляемые или защищенные поля не редактируются. Корректировку текущей записи можно отменить с помощью кнопки Вернуть.
Для создания новой записи нажимается кнопка Добавить, выполняется заполнение пустых полей экранной формы; для перехода между полями формы используются курсор мыши либо клавиша Tab. При повторном нажатии кнопки Добавить сформированная запись добавляется в конец списка. Для удаления текущей записи нажимается кнопка Удалить. Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.
С помощью экранной формы задаются критерии сравнения. Для этого нажимается кнопка Критерии. Для ввода условий поиска в полях форма очищается с помощью копки Очистить,а название кнопки Критерии заменяется на название Правка. После вывода критериев сравнения нажимаются кнопки Назад и Далее для просмотра отфильтрованных записей в нужном направлении. При просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка Правка, для выхода из формы – кнопка Закрыть.
Автоматическое подведение итогов. Команда ДАННЫЕ>Итоги>Промежуточные итоги вставляет строки промежуточных и общих итогов для выбранных столбцов в соответствии с заданной итоговой функцией (рис.3).
Рисунок 3. Окно подведения промежуточных итогов
С помощью списка При каждом изменении в: выбирается поле, по которому поле разбивается на группы записей. При первом открытии окна диалога автоматически выбрано имя первого поля. В списке Операция указывается функция, которая должна использоваться при определении промежуточных итогов. В поле Добавить итоги по: указывается поле, по которому должны быть определены промежуточные итоги. При первом открытии окна диалога по умолчанию выбирается последнее поле.
Команда ДАННЫЕ>Итоги может выполняться для одного и того же списка записей многократно, ранее созданные промежуточные итоги могут как заменяться новыми, так и оставаться неизменными посредством установки или снятия флажка параметра Заменить текущие итоги. Таким образом, имеется возможность подведения итогов различных уровней вложенности.
Для того чтобы отдельные группы записей были расположены и выведены на печать на различных страницах, устанавливается флажок параметра Конец страницы между группами.
По умолчанию промежуточные итоги будут отображены под отдельными группами записей, а общий итог – в конце списка. Для того, чтобы отобразить строки с итогами над детальными данными необходимо сбросить флажок параметра Итоги под данными.
При нажатии кнопки ОК автоматически создаются промежуточные итоги. Слева от таблицы на служебном поле появляются символы структуры: кнопка «+» – показ деталей; кнопка «-» – скрытие деталей; номера уровней 1, 2, 3 и т.д. – обозначенияуровней структуры.
Для открытия (закрытия) определенного уровня иерархии необходимо щелкнуть на номер уровня – кнопки с номерами 1, 2, 3 и т.д. Для открытия (закрытия) определенной иерархической ветки нажимаются кнопки +, - определенного элемента структуры, которые находятся слева от таблицы.
Создание сводных таблиц. КомандаДАННЫЕ>Сводная таблицавызываетМастера сводных таблиц для построения сводов – итогов определенных видов на основании данных списков, других свободных таблиц, внешних баз данных, нескольких разрозненных областей данных электронной таблицы Excel. Сводная таблица обеспечивает различные способы агрегирования информации.
Мастер сводных таблиц осуществляет построение сводной таблицы в несколько шагов:
Шаг 1. Указание вида источника сводной таблицы: использование списка или базы данных Excel; использование внешнего источника данных; использование данных из другой сводной таблицы. В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Рассмотрим случай использования списков при построении сводных таблиц.
Шаг 2.Указание интервала ячеек для построенияодной таблицы. Список (или база данных Excel) должен обязательно содержать имена столбцов (полей).
Если предварительно установить курсор в список, для которого строится сводная таблица, интервал ячеек будет автоматически указан. Для ссылки на закрытый интервал другой рабочей книги нажимается кнопка Обзор, в одноименном диалоговом окне выбирается диск, каталог или файл закрытой рабочей книги, вводятся имя рабочего листа и интервал ячеек либо имя блока ячеек.
Шаг 3. Построение макета сводной таблицы.
Структура сводной таблицы состоит из следующих областей, которые вы определяете в макете: страница – на ней размещаются поля, значение которых обеспечивают фильтрацию записей; на странице может быть размещено несколько полей, между которыми устанавливается иерархия связи – сверху вниз; страницу определять необязательно; столбец – поля размещаются слева направо, обеспечивая группирование данных сводной таблицы по иерархии полей; при условии существования области страницы или строки определять столбец необязательно; строка – поля размещаются сверху вниз, обеспечивая группирование данных таблицы по иерархии полей; при условии существования области страницы или столбцов определять строку необязательно; данные – поля, по которым подводятся итоги, связанные с определенным полем столбца и строки, согласно выбранной функции; обязательно определяемая область.
Кроме этого в рабочей области листа появляется окно Список полей сводной таблицы, которое содержит список полей базы данных.
Размещение полей выполняется путем их перетаскивания из окна Список полей сводной таблицы при нажатой левой кнопке мыши в определенную область макета. Каждое поле размещается только один раз в одной из областей. По этим полям можно формировать группы и получать итоговые значения в области данных, так называемые группировочные поля. В области данных могут находиться поля произвольных типов, одно и то же поле может многократно размещаться в области данных. Для каждого такого поля задается вид итоговой функции и выполняется необходимая настройка. Для изменения структуры сводной таблицы выполняется перемещение полей из одной области в другую (добавление новых, удаление существующих полей, изменение местонахождение поля). Для сводных таблиц существенен порядок следования полей (слева направо, сверху вниз), изменяется порядок следования полей также путем их перетаскивания.
В макете сводной таблицы можно выполнить настройку параметров полей. Для этого следует установить курсор на настраиваемое поле и дважды нажать левую кнопку мыши для вызова диалогового окна Вычисление поля сводной таблицы(рис. 4).
Рисунок 4. Окно вычислений в сводной таблице
В поле Имя: можно задать новое имя поля; в поле Операция: можно указать действие, которое необходимо выполнить в поле данных; удалить поле можно с помощью кнопки Удалить; с помощью кнопки Формат можно изменить формат поля базы данных; с помощью кнопки Дополнительно>> при необходимости можно выполнить Дополнительные вычисления.
Для удобной работы со сводными таблицами программа предоставляет в распоряжение пользователя специальную панель инструментов – Сводные таблицы.
Другие настройки сводной таблицы можно установить с помощью команд контекстно-зависимого меню. Аналогичный список кнопок располагается на панели инструментов Сводная таблица.
Методика и порядок проведения работы
3.1 Задание 1. Создать базу данных в Excel (рис.4.5), сделать сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета.
3.1.1. Рекомендации по выполнению: Откройте программу MS Excel.
- 1. Переименуйте первый лист рабочей книги Лист 1 – СПИСОК.
| A | B | C | D | E | F | G | |
| Номер группы | Номер зачетной книжки | Код предмета | Табельный номер преподавателя | Вид занятий | Дата | Оценка | |
| п1 | а1 | л | 12.05.2005 | ||||
| п2 | а2 | пр | 25.05.2005 | ||||
| п1 | а1 | л | 12.06.2005 | ||||
| п2 | а2 | пр | 20.05.2005 | ||||
| п1 | а1 | л | 12.06.2005 | ||||
| п2 | а3 | пр | 25.05.2005 | ||||
| п1 | а1 | л | 12.06.2005 | ||||
| п1 | а3 | пр | 25.05.2005 | ||||
| п1 | а2 | л | 07.06.2005 | ||||
| п2 | а1 | пр | 25.05.2005 | ||||
| п1 | а2 | л | 07.06.2005 | ||||
| п2 | а1 | пр | 20.05.2005 | ||||
| п1 | а2 | л | 07.06.2005 | ||||
| п2 | а3 | пр | 25.05.2005 | ||||
| п1 | а2 | л | 07.06.2005 | ||||
| п2 | а3 | пр | 20.05.2005 |
Рисунок 5. Пример списка (базы данных) в Excel
Для этого: установите курсор на Лист 1 и вызовите контекстное меню;
выберите опцию Переименовать и введите новое имя– СПИСОК.
2. Сформируйте название столбцов списка. Рассмотрим эту технологию на примере формирования названия первого столбца Номер группы:
- установите указатель мыши в ячейку А1 и введите название столбца Номер группы;
- вызовите контекстное меню и, воспользовавшись командой ФОРМАТ>Ячейки>Выравнивание, установите параметры: по горизонтали: по центру; по вертикали: по нижнему краю; отображение - установите флажок переносить по словам.
3. Введите данные для всех столбцов и заполните таблицу.
4. Выделите весь список и скопируйте его на Лист 2. Переименуйте Лист 2 – СОРТИРОВКА.
5. На Листе 2 выполните сортировку по возрастанию. Для этого:
- установите курсор в поле списка и введите команду ДАННЫЕ>Сортировка.
- в диалоговом окне Сортировка установите в строке:
Сортировать по – Табельный номер преподавателя;
Затем по – Номер группы;
В последнюю очередь, по – Код предмета;
3.2. Задание 2. Выбрать из списка данные, используя следующие критерии:
а) для указанного преподавателя – а1выбрать сведения о сдаче экзамена на положительную оценку, вид занятия – л;
б) для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4.
3.2.1. Рекомендации по выполнению:
1. Скопируйте СПИСОК на Лист 3. Переименуйте Лист 3 – АВТОФИЛЬТР.
2. Установите курсор в области списка и выполните команду ДАННЫЕ>Фильтр>Автофильтр.
3. Сформируйте условия отбора для преподавателя а1. Для этого:
- в столбце Табельный номер преподавателя нажмите кнопку 
из списка условий отбора выберите а1;
- в столбце Оценка из списка условий отбора выберите Условие и в диалоговом окне Пользовательский автофильтрсформируйте условия отбора - больше 2;
- в столбце Вид занятия из списка условий отбора выберите л.
4. Отмените результат автофильтрации, установив указатель мыши в список и выполните команду ДАННЫЕ>Фильтр>Автофильтр(убрать галочку).
5. По аналогии выполните задание б).
3.3. Задание 3. Сформировать условия отбора с помощью формы данных:
а) для указанного преподавателя – а1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий – л;
б) для группы 133 получить сведения о неудовлетворительной сдаче и неявках на экзамен по предмету п1. Просмотреть отфильтрованные записи. Создать новые записи и поместить их в список..
3.3.1 Рекомендации по выполнению:
1. Скопируйте СПИСОК на Лист 4. Для того, чтобы создать новый лист, воспользуйтесь командой ВСТАВКА>Лист или выполните команду контекстного меню, щелкнув правой кнопкой мыши на названии листа. Переименуйте Лист 4 – ФОРМА ДАННЫХ.
2. Установите курсор в область списка и выполните команду ДАННЫЕ>Форма.
3. Просмотрите записи списка с помощью кнопок Далее или Назад.
4. С помощью кнопки Добавить создайте новые записи.
5. Сформируйте первое условие отбора, приведенное в записи. Для этого нажмите кнопку Критерии, название которой поменяется на Правка. В пустых строках имен полей списка введите критерии:
- Табельный номер преподавателя введите а1;
- Вид занятия введите л;
- Оценка введите >2.
6. Просмотрите отобранные записи, нажатием на кнопок Далее или Назад.
7. Аналогично сформируйте остальные указанные условия отбора записей.
3.4. Задание 4.Получить промежуточные итоги:
1-го уровня – средний балл по каждой учебной группе;
2-го уровня – средний балл по каждому предмету по всем учебным группам;
3-го уровня – средний балл по каждому виду занятий определенных предметов по всем учебным группам
3.4.1. Рекомендации по выполнению:
1. Скопируйте СПИСОК на Лист 5. Переименуйте Лист 5 – ИТОГИ.
2. Создайте 1-й уровень итогов по группам. Для этого:
- установите курсор в произвольную ячейку списка записей и выполните команды ДАННЫЕ>Итоги.
- В диалоговом окне Промежуточные итогиукажите:
При каждом изменении в: Номер группы;
Операции: Среднее;
Добавить итоги по: Оценка.
Установите значения переключателей:
Заменять текущие итоги: Нет.
Конец страницы между группами: Нет.
Итоги под данными: Да.
- В результате будет рассчитан средний балл по каждой группе.
3. Создайте 2-й уровень итогов – по кодам предметов внутри учебных групп. Для этого: в диалоговом окне Промежуточные итогиукажите: При каждом изменении в: Код предмета; Операции: Среднее; Добавить итоги по: Оценка; - значения переключателей аналогичны первому уровню.
В результате будет рассчитан средний балл по каждому предмету по каждой учебной группе.
4. Создайте 3-й уровень итогов по видам занятий внутри предметов для каждой группы. Для этого:
- в диалоговом окне Промежуточные итогиукажите: При каждом изменении в: Вид занятий; Операции: Среднее; Добавить итоги по: Оценка;
- значения переключателей аналогичны.
В результате будет рассчитан средний балл по каждому виду занятий определенного предмета в учебной группе.
3.5. Задание 5.Рассчитать средний балл по каждому виду занятий определенного предмета в учебной группе.
3.5.1. Рекомендации по выполнению:
На новом листе создайте промежуточные итоги:
на 1-м уровне – по коду предмета;
на 2-м уровне – по виду занятий;
на 3-м уровне – номеру группы.
3.6. Задание 6.Построить сводную таблицу по учебным группам и подвести итоги по виду занятий, вычисляя средний балл.
3.6.1. Рекомендации по выполнению:
1. Скопируйте СПИСОК на новый рабочий лист и переименуйте его – СВОДНАЯ ТАБЛИЦА.
2. Установите курсор в область списка, выполните команду ДАННЫЕ>Сводная таблица для запуска мастера сводных таблиц.
3. Заполните диалоговые окна мастера:
Шаг 1. Установите переключатель в строке В списке или базе данных MS Excel.
Шаг 2. Укажите диапазон, содержащий исходные данные - всю область СПИСКА.
Шаг 3. Поместить таблицу в – новый лист.
4. Постройте макет сводной таблицы:
· из списка поле сводной таблицы перетащите в область столбцов – Номер группы;
· в область строк – Вид занятий;
· в область данных – Оценка;
· с помощью команды контекстного меню Параметры поля вызовите диалоговое окно Вычисление поля сводной таблицы;
· в диалоговом окне измените имя Исходного поля на Средний балл и выберите операцию – среднее.
3.7. Задание 7.На отдельном листе построить сводную таблицу, подводящую итоги в разрезе предметов и номеров учебных групп по каждому преподавателю: количество оценок, максимальные и минимальные оценки.
4 Аппаратура и материалы:IBM PC, табличный процессор MS Excel.
5. Содержание отчета и его форма
1. Форма отчёта письменная.
2. Тема, цель лабораторной работы.
3. Краткое теоретичеcкое описание работы.
4. Описание выполнения работы.
5. Продемонстрировать электронный вариант таблиц .
6 Контрольные вопросы:
1. Базы данных в Excel.
2. Основной элемент базы данных.
3. Чему соответствуют строки и столбцы списка?
4. Основные технологические операции по сортировке данных.
5. Фильтрация данных.
6. Перечислите критерии, которые указывают при автофильтрации.
7. Возможности расширенного фильтра.
8. Какие возможности предоставляет экранная форма данных?
9. Автоматическое подведение итогов.
10. Какой командой вставляют строки промежуточных и общих итогов?
11. Алгоритм создание сводных таблиц.