Заполнение списков с помощью формы данных

Лабораторная работа № 9. Работа со списками в Excel.

Требования к содержанию, оформлению и порядку выполнения

Перед выполнением лабораторной работы необходимо создать папку «Ваша фамилия Lab9» (например: «Ivanov Lab9»). В эту папку в ходе выполнения работы необходимо сохранять требуемые материалы.

Лабораторные работы необходимо выполнять согласно своему варианту. Варианты лабораторной работы распределяются согласно номеру монитора

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

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

После выполнения лабораторной работы ответьте на контрольные вопросы.

Теоретическая часть

Программа Microsoft Excelрасполагает средствами для работы с простыми базами данных, которые могут быть реализованы с помощью обычгной (плоской, двумерной) таблицы. База данных в Microsoft Excel – таблица, состоящая из однотипных записей (строк). Столбцы таблицы являются полями записи в базе данных. Под имена полей выделяется первая строка базы данных. Например, если базой данных считать телефонный справочник, то полями записи будут фамилии, номера телефонов и адреса абонентов.

Электронная таблица, все строки которой содержат однородную информацию, рассматривается как список или база данных. Термины база данных и список в Excel используются как синонимы. При этом нужно иметь в виду следующее:

· каждая строка списка рассматривается как запись базы данных;

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

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

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

· все строки таблицы должны содержать однородную информацию – во всех строках в одинаковых столбцах должны находиться однотипные данные;

· заголовки столбцов должны находиться в первой строке списка;

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

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

· Если формула, содержащаяся в списке, ссылается на ячейку вне списка, необходимо, чтобы ссылка была абсолютной.

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

· добавлять, изменять и удалять записи;

· находить записи;

· сортировать записи;

· осуществлять фильтрацию (и выборку) данных с помощью Автофильтра и Расширенного фильтра;

· подводить общие и промежуточные итоги и т. д.

Заполнение списков с помощью формы данных.

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

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

Рис. 4.1. Пример формы

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

Рассмотрим назначение других клавиш:

Удалить – удаляет выведенную запись, другие записи базы данных сдвигаются. Удаленные записи не могут быть восстановлены.

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

Критерии – очищает поля перед вводом критериев сравнения с операторами сравнения для поиска необходимого подмножества записей.

Закрыть – закрывает форму данных.

Сортировка данных

Для использования некоторых операций автоматической обработки данных часто бывает необходимо заранее расположить данные в таблице (списке) в строго определенной последовательности – выполнить сортировку исходных данных. В электронной таблице можно сортировать как строки, так и столбцы. Строки можно отсортировать по значениям ячеек одного столбца или нескольких. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем порядке (от 1 до 9, от А до Я) или в убывающем порядке (от 9 до 1, от Я до А). По умолчанию Excel сортирует данные в алфавитном порядке. Сортировка данных в электронных таблицах производится с помощью команд меню Данные/Сортировка или с помощью кнопок на Стандартной панели инструментов.

После выбора команды Данные/Сортировка открывается диалоговое окно (рис. 4.2), в котором в поле списка Сортировать по необходимо выбрать, по которому будут отсортированы данные, и тип сортировки (по возрастанию или по убыванию).

Рис. 4.2. Диалоговое окно Сортировка диапазона.

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

Фильтрация данных

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

Автофильтр

Команда Данные/Фильтр/Автофильтрустанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов . С их помощью можно указать требуемые значения или выбрать строку «условие» и задать критерии выборки в диалоговом окне Пользовательский автофильтр (рис. 4.3). Условия для отбора записей в определенном столбце могут состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ. Каждая часть условия может включать:

· значение, которое может выбираться из списка или содержать шаблонные символы подстановки. В качестве символов подстановки используются звездочка * - для указания произвольного количества символов или вопросительный знак ? – для замены одного символа;

· оператор отношения (сравнения). При задании критериев выборки могут использоваться следующие операторы сравнения: Равно, Не равно, Меньше, Меньше или равно, Больше, Больше или равно.

Рис. 4.3. Диалоговое окно Пользовательского автофильтра.

Для восстановления всех строк исходной таблицы нужно щелкнуть мышью по кнопке со стрелкой (синего цвета) и в раскрывшемся списке выбрать строку «все» или выполнить команду Данные/Фильтр/Отобразить все. Для отмены режима фильтрации нужно установить курсор внутри таблицы и снова ввести команду меню Данные/Фильтр/Автофильтр(убрать переключатель).