Создание списка (базы данных)
На листе не следует помещать более одного списка. Некоторые функции обработки списков, например фильтры, не позволяют обрабатывать несколько списков одновременно. В списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка. Списков должен быть организован так, чтобы во всех строках в одинаковых столбцах находиться однотипные данные. Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.
Для создания списка с помощью формы (маски ввода):
1. Сформируйте заглавную строку списка. В каждом столбце этой строки введите название соответствующего поля записи.
2. Щелкните на любой из ячеек заглавной строки и выберите команду Данные / Форма.
3. В отрывшемся диалоговом окне, содержащем поля, название и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. Ширина полей соответствует самому широкому столбцу заголовка. Для перехода между полями можно пользоваться указателем мыши, либо клавишами <Tab> – для перехода вниз и <Shift><Tab> – для перехода вверх.
4. Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись.
Для завершения процесса ввода данных нажмите кнопку Закрыть.
Поиск записей в списке
Для того чтобы в большой таблице найти записи, удовлетворяющие некоторому условию:
1. Установите курсор в любую ячейку списка и выберите команду Данные/ Форма.
2. Нажмите кнопку Критерии.
3. Введите критерии поиска а одно или несколько полей записи. Так, для поиска всех фамилий, начинающихся на букву А, достаточно в поле фамилии набрать А. для поиска записей с величиной оклада, большей 500000, в поле оклада следует ввести >500000.
4. Нажимайте кнопки Назад и Далее, либо кнопки полосы прокрутки для поиска записей, отвечающих установленному критерию.
Для заданного нового критерия поиска нажмите кнопку Очистить.
Фильтрация данных
Фильтрация- это быстрый и легкий способ поиска подмножества данных и работы с ними в списке. В отфильтрованном списке отображаются только строки, отвечающие условиям отбора, заданным для столбца. Microsoft Excel предоставляет две команды для фильтрации списков:
· Автофильтр, включая фильтр по выделенному, для простых условий отбора;
· Расширенный фильтр для более сложных условий отбора.
В отличие от сортировки, при фильтрации порядок записей в списке не изменяется. При фильтрации временно скрываются строки, которые не требуется отображать. Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их.
Пример. Выбор данных из списка, используя Расширенный фильтр, по Критерию сравнения и по Вычисляемому критерию
Этап 1. Формирование диапазона условий по типу Критерий сравнения
1.Скопируйте все имена полей списка (рис.4.5) в другую область на том же листе, например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 – имена полей области критерия, J2:O5 – область значений критерия.
Рис. 4.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
Поместить результат в диапазон: перейдите на новый лист и щелкните мышью в любой ячейке
· Нажмите кнопку ОК.
Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.
Сводные таблицы
Сводные таблицы применяются для обработки больших списков данных. С ее помощью можно быстро извлечь из больших баз данных необходимую информацию, благодаря ее возможности одновременно выполнять различные операции.
Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц. Перед построением необходимо убрать все ранее созданные промежуточные итоги и наложенные фильтры.
1. Установите курсор в любую ячейку списка и выберите команду Данные/Сводная таблица.
2. В открывшемся диалоговом окне Мастер сводных таблиц отметьте опцию в списке или базе данных MS Excel.
3. Далее определите диапазон, с которым будет работать Мастер сводных таблиц. Щелкните на кнопке Далее.
4. В следующем окне определите, значения каких полей списка будут использоваться в качестве заголовок строк (зона Строка), каких – в качестве заголовков столбцов (зона Столбец) и каких - в качестве данных (зона Данные), по которым следует подвести необходимые итоги. В зону Страница помещается кнопка поля, по которому предполагается фильтровать данные. В каждой зоне может быть несколько кнопок.
5. Далее определяется место, в которое будет помещена разработанная сводная таблица.
6. После нажатия на кнопку Готово в указанном месте появляется таблица со сводными данными.