4.3.10 Работа со списками (базами данных)

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

4.3.11 Создание списка (базы данных)

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

Для создания списка с помощью формы (маски ввода):

1. Сформируйте заглавную строку списка. В каждом столбце этой строки введите название соответствующего поля записи.

2. Щелкните на любой из ячеек заглавной строки и выберите команду Данные / Форма.

3. В отрывшемся диалоговом окне, содержащем поля, название и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. Ширина полей соответствует самому широкому столбцу заголовка. Для перехода между полями можно пользоваться указателем мыши, либо клавишами <Tab> – для перехода вниз и <Shift><Tab> – для перехода вверх.

4. Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись.

Для завершения процесса ввода данных нажмите кнопку Закрыть.

5.3.12 Поиск записей в списке

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

1. Установите курсор в любую ячейку списка и выберите команду Данные/ Форма.

2. Нажмите кнопку Критерии.

3. Введите критерии поиска а одно или несколько полей записи. Так, для поиска всех фамилий, начинающихся на букву А, достаточно в поле фамилии набрать А. для поиска записей с величиной оклада, большей 500000, в поле оклада следует ввести >500000.

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

Для заданного нового критерия поиска нажмите кнопку Очистить.

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

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

• Автофильтр, включая фильтр по выделенному, для простых условий отбора;

• Расширенный фильтр для более сложных условий отбора.

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

Пример. Выбор данных из списка, используя Расширенный фильтр, по Критерию сравнения и по Вычисляемому критерию

Этап 1. Формирование диапазона условий по типу Критерий сравнения

1.Скопируйте все имена полей списка (рис.5.5) в другую область на том же листе, например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеекJ1:O1 – имена полей области критерия,J2:O5 – область значений критерия.

Рис. 5.5

2.Сормируйте в области условий отбора Критерий сравнения– о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

в столбец Номер группы- точное значение 133;

в столбец Код предмета– точное значение п1;

в столбец Оценка – условие - >3.

Этап 2. Фильтрация записей расширенным фильтром.

Произведите фильтрацию записей на том же листе:

• установите курсор в область списка (базы данных);

• выполните команду Данные, Фильтр, Расширенный фильтр;

• в диалоговом окне <<Расширенный фильтр>> с помощью мыши задайте параметры, например:

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

Исходный диапазон: А1:G17

Диапазон условия: J1:O5

Поместить результат в диапазон:J6

• Нажмите кнопку ОК.

3.Придумайте собственные критерии отбора по типу Критерий сравненияи проведите фильтрацию на том же листе.

Этап 1. Формирование диапазона условий по типу Вычисляемый критерий

Сформируйте в области условий отбора вычисляемый критерий – для каждого преподавателя выберите сведения о сдаче студентами экзамена на оценку выше средней, вид занятий – л; результат отбора поместите на новый рабочий лист. Для этого:

• В столбец Вид занятиявведите точное значение – букву л;

• Переименуйте в области критерия столбец Оценка, например, на имяОценка1;

• В столбец Оценка 1введите вычисляемый критерий, например, вида

=G2>CPЗНАЧ($G$2:$G$17),

где G2 – адрес первой клетки с оценкой в исходном списке,

$G$2:$G$17- блок ячеек с оценками,

CPЗНАЧ – функция вычисления среднего значения.

Этап 2. Фильтрация записей с расширенным фильтром.

Произведите фильтрацию записей на новом листе:

• Установите курсор в область списка (базы данных);

• Выполните команду Данные, Фильтр, Расширенный фильтр;

• В диалоговом окне Расширенный фильтрс помощью мыши задайте параметры, например:

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

Исходный диапазон: А1:G17

Диапазон условия:J1:O5

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

• Нажмите кнопку ОК.

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