Лабораторная работа Списки и базы данных Excel по дисциплине Информационные технологии в профессиональной деятельности

Лабораторная работа №16
Списки и базы данных Excel
Цель: научиться работать со списками в программе Excel:
работа с экранной формой,
сортировка списков,
фильтрация списков (автофильтр, расширенный фильтр).
Теоретическая часть
MS Excel позволяет работать с реляционными структурами, которые носят название списка или базы данных Excel. Список – таблица прямоугольной конфигурации, столбцы таблицы считаются полями, строки – записями базы данных реляционного типа. Список должен отвечать ряду требований:
список целиком помещается на одном рабочем листе;
список должен отделяться от других данных рабочего листа хотя бы одним незаполненным столбцом и/или строкой;
имена столбцов списка располагаются в первой строке таблицы, имена могут быть многострочными с переносами слов;
ячейки одного столбца списка содержат однородную информацию.
Традиционные операции со списками:
Ввод и редактирование данных списка.
Сортировка – упорядочивание записей (строк/столбцов таблицы) списка.
Фильтрация (отбор) записей списка по условиям.
Агрегирование информации списка, вычисление статистических оценок (сводные таблицы, промежуточные итоги).
Структурирование данных (формирование новых группировок строк и столбцов).
Консолидация (объединение данных нескольких списков с формированием общих итогов).
Форма для работы со списками
Список MSExcel(база данных) может создаваться и редактироваться с помощью экранной формы, которая содержит имена и значения полей записи списка. Диалоговое окно формы имеет имя, соответствующее названию листа списка, с помощью текстовых кнопок форма обеспечивает работу с каждой отдельной записью списка (рис. 1).

Рис. 1. Форма для ввода данных в список
Добавить – ввод новой записи в список.
Назад, Далее – последовательный переход к записям списка.
Критерии – поиск записей, удовлетворяющим условиям отбора.
Удалить – удаление записи.
Закрыть – выход из формы.
Для удобства вызова формы, можно добавить кнопку Форма на Панель быстрого доступа, для этого необходимо воспользоваться кнопкой Office и активировать кнопку Параметра Excel, в появившемся диалоговом окне Параметра Excel пошагово выполнить следующую последовательность действий: Настройка – Выбрать команды из: Все команды – Форма – Добавить – ОК.Список может содержать вычисляемые поля, пример – поле Тариф (см. рис. 1). Весь список, включая имена полей и записи, может рассматриваться как единый блок ячеек, для которого создаётся общее имя. Если блок списка имеет стандартное имя БазаДаных, при вводе новых записей через форму ввода блок автоматически расширяется, в вычисляемые поля новых записей копируются формулы.
Основным недостатком применения экранных форм для работы со списками MS Excel является низкий уровень достоверности вводимых данных в связи с отсутствием программных методов контроля, невозможностью использования элемента управления Поле со списком.
Сортировка списков
Технология сортировки для всех таблиц прямоугольной конфигурации, включая списки MSExcel, единая. Особенностью списков является ото, что они сортируются построчно, другие же таблицы могут сортироваться как по строкам, так и по столбцам. Для сортировки записей таблицы выделяется сортируемая область, поэтому допускается сортировка части таблицы, при этом правильность операции сортировки должна контролироваться пользователем.
Результат сортировки списка всегда остается на том же месте. Для сортировки можно задать несколько уровней с указанием направлений сортировки – по возрастанию или убыванию для каждого уровня в отдельности. В этом случае необходимо воспользоваться командой Настраиваемая сортировка и в появившемся диалоговом окне Сортировка указать столько уровней сортировки сколько необходимо.

Рис. 2. Вызов команды Настраиваемая сортировка
При сортировке по возрастанию упорядочение идёт от меньшего к большему, по алфавиту или в хронологическом порядке дат. Сортировка по убыванию использует обратный порядок, исключение – пустые ячейки, которые располагаются в конце списка.
Чтобы отсортировать список нужно воспользоваться кнопкой на вкладке Главная или кнопкой на вкладке Данные.
Фильтрация списков
Различают два способа фильтрации (отбора) записей: Автофильтр и Расширенный фильтр. Автофильтр создаёт для каждого поля список значений, используемый для отбора записей и устанавливается при помощи кнопки на вкладке Главная или кнопки на вкладке Данные. Для отказа от Автофильтра повторно выполняется команда её выбора.
Расширенный фильтр предполагает формирование специального диапазона ячеек в произвольном месте. Этот диапазон является критерием для расширенного фильтра списка. Диапазон условий состоит из имён полей списка и строк условий.
Кнопка на вкладке Данные выводит диалоговое окно для указания диапазона ячеек: исходного диапазона – списка MS Excel, диапазона условий, диапазона для вывода результата фильтрации (рис. 3).

Рис. 3. Диалоговое окно Расширенного фильтра
Результат расширенного фильтра можно скопировать в новое место, он может включать все отобранные или только уникальные записи. Исходный диапазон – список MS Excel, диапазон условий или копия отфильтрованных записей могут находиться на одном и том же листе, на разных листах одной книги и даже в различных открытых книгах.
Практическая часть
Задание 1. Ознакомьтесь с теоретической частью лабораторной работы и основные моменты законспектируйте в тетрадь.
Задание 2. Создать список Картотека и заполнить его с помощью экранной формы.
Порядок выполнения
Откройтесвою рабочую книгу с именем «Лабораторные».
Переименуйте свободный лист Вашей рабочей книги на Тариф, создайте и отформатируйте на своё усмотрение таблицу1, начиная с ячейки А1 (рис.4).
Создайте именованные блоки ячеек для столбцов Разряд, Коэффициент, Ставка (см. предыдущую лабораторную работу).
Рассчитайте столбец ставка (рис. 5).
Свободный лист переименуйте на Картотека, и, начиная с ячейки А1, создайте и отформатируйте на своё усмотрение таблицу2 (см. рис.6). Перед заполнением ячеек задайте текстовый формат для ячеек столбца Табельный №, для этого выделите столбец В и выполните команду меню Формат - Ячейки, на вкладке Число выберите формат Текстовый.
Таблица 1

Рис. 4

Рис. 5
Таблица 2

Рис. 6
.Создайте именованные блоки ячеек для столбцов ФИО, Табельный №, Профессия, Разряд работающего, Тариф, Льготы.
Проверьте определение блоков всеми способами:
Выбрать блоки в списке имён ячеек (слева от строки формул).
Нажать клавишу F5.
На листе Картотека в ячейку Е2 введите формулу тарифа: = ПРОСМОТР (D2; Разряд; Ставка) (рис. 7).
Скопируйте формулу ячейки E2 в ячейки столбца Тариф (для заполненных строк списка).

Рис. 7
Установите курсор в область списка и активируйте кнопку Форма на Панели быстрого доступа (см. теоретическую часть). Введите новые записи в список (см. таблица 3) в количестве 5-ти человек с помощью кнопки Добавить.
Таблица 3. Списки работающихФИО Табельный № Профессия Разряд работающегоТариф Льготы
Котов А.А. 01234 Весовщик 5 2
Павлов Ф.Ф. 02345 Весовщик 4 2
Соловьев А.Н. 12980 Упаковщик 3 1
Крылов А.Н. 00127 Грузчик 5 1
Ильин А.Е. 13980 Упаковщик 5 1
Пересоздайте именованные блоки ячеек для столбцов ФИО, Табельный №, Профессия, Разряд работающего, Тариф, Льготы (включая новые записи таблицы).
Выполните отбор записей по условию: профессия Грузчик, разряд работающего выше 2, для этого нажмите кнопку Критерии экранной формы и заполните поля:
поле Профессия – Грузчик.
поле Разряд работающего – >2.
Нажмите кнопку Далее или Назад и просмотрите записи, удовлетворяющие запросу.
Нажмите кнопку Очистить и самостоятельно выполните отбор записей по условию: разряд работающего до 4 и льготы больше 2.
Задание 3. Отобрать сведения о сотрудниках, для которых число льгот по подоходному налогу находится в интервале от 2 до 3 и разряд не ниже 3, а также всех сотрудников по профессии Грузчик. Условия отбора поместить на новом листе. Результат фильтрации записей списка поместить на новом листе.
Порядок выполнения
На листе Картотека создайте именованный блок БазаДанных для списка Картотека.
Вставьте (если необходимо) новый лист и переименуйте его на Критерии.
Подготовьте диапазон условий для расширенного фильтра на листе Критерии (рис. 8).

Рис. 8 Условия для Расширенного фильтра
Перейдите на новый лист (добавьте лист в рабочую книгу, если необходимо), который переименуйте на Результат.
Установите курсор в ячейку А1 листа Результат и откройте диалоговое окно Расширенный фильтр с помощью кнопки Дополнительно на вкладке Данные.
В диалоговом окне Расширенный фильтр укажите:

Задание 4. Отчитайтесь по проделанной работе преподавателю и оформите отчёт по данной лабораторной работе, ответив письменно на контрольные вопросы.
Контрольные вопросы:
Что такое список? Перечислите ряд требований, которому должен отвечать список.
Назовите основной недостаток применения экранных форм для работы со списками.
С помощью какой команды можно выполнить сортировку списка?
Сколько способов фильтрации существует? Чем они отличаются друг от друга?
Как отменить автофильтр для списка?