Функции для работы со списками
ПРАКТИЧЕСКОЕ ЗАНЯТИЕ
Теоретический материал
Список – определенным образом сформированный на рабочем листе массив данных со столбцами и строками. Список может использоваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов (полей).
Каждая запись должна содержать полное описание конкретного элемента. Количество полей в каждой записи – одинаково. Каждое поле в записи может являться объектом поиска или сортировки.
Создание списка (базы данных)
Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.
o сформируйте заглавную строку списка: в каждом столбце этой строки введите название соответствующего поля записи;
o щелкнув на любой из ячеек заглавной строки, выберите команду Данные→Форма;
o в диалоговом окне введите данные в каждое поле. Для перехода между полями используются клавиши <TAB> (вниз) или <SHIFT>+<TAB> (вверх);
o нажать на кнопку Добавить для помещения значений данной записи в список;
o для завершения ввода данных нажать кнопку Закрыть.
Поиск записей в списке
1.Установите курсор в любую ячейку списка и выберите команду Данные→Форма.
2.Нажмите кнопку Критерии.
3.Введите критерии поиска в одно или несколько полей записи.
4.Нажимайте кнопки Назад и Далее для поиска записей, отвечающих установленному критерию.
Для задания нового критерия поиска нажмите кнопку Очистить.
Фильтрация списка
1.Установить курсор в любую ячейку списка, задать команду Данные→Фильтр, а затем выбрать пункт Автофильтр.
2.Нажать на кнопку со стрелкой в том столбце, по которому надо фильтровать данные.
3.Выбрать любой элемент из списка.
4.При использовании пункта Условие можно задавать до двух критериев фильтрации одного столбца, выбирая из списка операторов сравнения и списка значений данного поля те значения, которые необходимы для задаваемого критерия.
Для восстановления всех записей списка необходимо задать команду Данные→Фильтр→Показать все или же в раскрывающемся списке автофильтра выбрать пункт Все. Для отмены фильтрации необходимо повторно выбрать команду Данные→Фильтр→Автофильтр.
Расширенный фильтр
Для фильтрации данных можно использовать команду Данные→Фильтр→Расширенный фильтр. Эта команда позволяет применять сложные критерии для выборки данных или когда нужно поместить результат фильтрации в отдельную область рабочего листа.
Область критериев (поле Диапазон критериев команды Расширенный фильтр) представляет собой минимум 2 строки, в первой из которых содержатся названия полей из заглавной строки списка, а в остальных строках указываются критерии поиска.
o Для установки нескольких критериев для одного поля в интервал критериев должно быть включено несколько столбцов с названием этого поля (логическое И).
o Если на экран надо вывести записи, удовлетворяющие одному из критериев, то ввод условий производится в разные строки одного столбца (логическое ИЛИ).
Для восстановления списка следует выбрать команду Данные→Фильтр→Показать все.
Функции для работы со списками
| Функции и ее синтаксис | Назначение функции |
| БДСУММ(Список; Поле; Критерий поиска) | Суммирует числа в поле (столбце) записей базы данных, удовлетворяющих условию |
| БСЧЕТ(Список; Поле; Критерий поиска) | Подсчитывает количество ячеек в столбцах списка или базы данных, содержащих числа, удовлетворяющих критерию |
| ДСРЗНАЧ(Список; Поле; Критерий поиска) | Вычисляет среднее всех значений поля (столбца) списка, удовлетворяющих заданным условиям |
ЗАДАНИЕ1.
1.Создайте в Excel таблицу следующего содержания:
2.Используя форму данных, добавьте в список еще две записи с реквизитами:
3.Используя форму данных, найдите информацию о Голубкиной и измените размер ее оклада на 12300.
4.Используя форму данных, просмотрите все данные списка о швеях, чьи оклады превышают 9500 руб.
5.Используя Автофильтр, отобразите все данные списка по лицам, имеющим высшее образование.
6.Используя Автофильтр, отобразите все данные списка по мотористам, имеющим среднее образование.
7.Присвойте имя (например, Ателье) диапазону ячеек, содержащему все данные списка (включая заглавную строку).
8.Вставьте перед диапазоном со списком девять пустых строк.
9.В ячейки А1:H1 скопируйте шапку таблицы.
10.С помощью расширенного фильтра отобразите все данные лиц, имеющих высшее образование, используя для области критериев ячейки A1:H2. Изменив область критериев, отобразите все данные списка по электрикам.
11.Внесите изменение в область критериев, чтобы отобразить лишь записи списка, в которых размер оклада составляет от 9600 до 12500 руб.
12.Используя Расширенный фильтр, поместите в любую свободную (пустую) область рабочего листа все данные списка по лицам, зачисленным в 2004 г., предварительно изменив область критериев, либо создав новую.
13.Измените область критериев, добавив в качестве критерия одну должность – швея.
14.Для заданного критерия отбора, т.е. критерия швея, вычислите общую сумму:
o в ячейку В6 введите формулу расчета суммы БДСУММ, используя Мастер функций. Для указания диапазона базы данных выберите имя соответствующего диапазона (Ателье), для задания поля укажите ячейку с названием поля Оклад, для задания диапазона критериев также воспользуйтесь мышью;
o в ячейку В5 введите текст “Сумма по критерию швея”.
15.Используя функцию БСЧЕТ, подсчитайте в ячейке С6 количество окладов. Имя поля, указанного в окне Мастера функций, оставьте прежним. В ячейку С5 введите текст “Количество окладов”.
16.Используя функцию ДСРЗНАЧ, определите в ячейке Е6 средний оклад швеи. Введите в ячейку Е5 текст “Средний оклад швеи”.
17.Подсчитайте сумму окладов и количество записей по сотрудникам, имеющим высшее образование, изменив область критериев.