Задание. 1. Использовать список студентов из работы 3.1. Использовать список студентов из работы 3. 2. Отфильтровать список, выбрав только отличников по всем дисциплинам. Скопировать результат во второй лист. 3. Отфильтровать список, выбрав тех, кто сдал сессию, т.е. получил положительные оценки. Скопировать результат в третий лист. 4. Отфильтровать список, выбрав тех, кто получил только оценки 3 или 4. Скопировать результат в четвертый лист. 5. С помощью расширенного фильтра установить список студентов, получивших хотя бы одну двойку. Скопировать результат в пятый лист. 6. Найти в списке результаты сессии для студентов, фамилии которых начинаются на букву В. Скопировать результат в шестой лист. 7. Все листы поименовать по смыслу. 8. Показать результаты преподавателю.
Лабораторная работа 6. Средства выборки из списков с агрегированием данных
В Microsoft Excel имеются специальные функции, используемые для анализа данных из таблиц, или баз данных. Каждая из этих функций, которые из соображений совместимости имеют обобщенное название БДФункция, использует три аргумента: база_данных, поле и критерий. Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются данной функцией. Обращение к БД-функциям выполняется через линейку Формулы, опцию Вставить функцию, категорию Работа с базой данных. При этом синтаксис функции имеет вид: БДФункция(база_данных;поле;критерий), где база_данных - это интервал ячеек, формирующих базу данных. Верхняя строка интервала содержит названия каждого столбца. База данных может быть задана как диапазон ячеек либо как имя, соответствующее диапазону; поле определяет столбец, используемый функцией. Поля данных должны содержать идентифицирующее имя в первой строке. Аргумент «поле» может быть задан как текст с названием столбца в двойных кавычках, например "Алгебра" или "Информатика" в приведенном ниже примере базы данных, или как адрес любой ячейки этого столбца, входящей в состав базы данных; критерий - это ссылка на интервал ячеек, задающих условия для функции. Функция возвращает данные из базы данных, которые удовлетворяют условиям, определенным диапазоном критериев. Диапазон критериев включает копию названия столбца, для которого выполняется обработка, в базе данных. Ссылка на критерий может быть введена как интервал ячеек, например A1:F2 в приведенном ниже примере базы данных, или как имя интервала, например "Критерии". При этом следует иметь в виду следующее: Любой интервал, который содержит, по крайней мере, одно название столбца и, по крайней мере, одну ячейку под названием столбца с условием, может быть использован как аргумент критерий БДФункции. Например, если интервал G1:G2 содержит название столбца «Программирование» в ячейке G1 и величину 10 000 в ячейке G2, то можно определить интервал СоответствуетПрибыли и использовать это имя как аргумент критерий в функции баз данных. Диапазон критериев не должен перекрываться с базой данных. Чтобы выполнить операцию над целым столбцом базы данных, нужно ввести пустую ячейку под названием столбца в интервале критерия. Рассмотрим некоторые функции базы данных. Функция ДСРЗНАЧ(база_данных;поле;критерий) - возвращает среднее значение выбранных фрагментов базы данных. Функция БСЧЁТ(база_данных;поле;критерий) - подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных указанном поле всех записей, удовлетворяющих заданным условиям. Аргумент поле не является обязательным. Если аргумент поле опущен, то функция БСЧЁТ подсчитывает количество записей в базе данных, отвечающих критериям. Функция БСЧЁТА(база_данных;поле;критерий) - подсчитывает все непустые ячейки в столбце списка или базы данных, которые удовлетворяют заданным условиям. Функция БИЗВЛЕЧЬ(база_данных;поле;критерий) - извлекает из базы данных одну запись, удовлетворяющую заданному условию. Если ни одна из записей не удовлетворяет критерию, то функция БИЗВЛЕЧЬ возвращает значение ошибки #ЗНАЧ!. Если более чем одна запись удовлетворяет критерию, то функция БИЗВЛЕЧЬ возвращает значение ошибки #ЧИСЛО!. Функция ДМАКС(база_данных;поле;критерий) - возвращает максимальное значение среди выделенных записей базы данных. Функция ДМИН(база_данных;поле;критерий) - возвращает минимальное значение среди выделенных записей базы данных. Функция БДПРОИЗВЕД(база_данных;поле;критерий) - перемножает значения определенных полей записей базы данных, удовлетворяющих условию. Функция БДСУММ(база_данных;поле;критерий) - с уммирует числа в поле столбца записей базы данных, удовлетворяющих условию. Например, пусть есть база данных по результатам сессии (данные в диапазоне А5:Е11):
Диапазон A5:E11 имеет название БазаДанных, а диапазон A1:F3 - Критерий. Тогда различные функции дадут следующие результаты: БСЧЁТ(БазаДанных;"Алгебра";A1:F2) равняется 3. Эта функция просматривает записи о студентах, которые сдали историю на 3 и 4 и подсчитывает количество числовых полей Алгебра в этих записях. БСЧЁТА(БазаДанных;"Программирование";A1:F2) равняется 3. Эта функция просматривает записи о студентах, которые сдали историю на 3 и 4 и подсчитывает количество непустых полей Программирование в этих записях. ДМАКС(БазаДанных;"Программирование";A1:A3) равняется 5. Здесь определяется лучшая оценка по программированию, полученная студентами в сессию. ДМИН(БазаДанных;"Программирование";A1:B2) равняется 3. Это низшая оценка по программированию, полученная студентами, которые сдали историю. БДСУММ(БазаДанных;"Программирование";A1:A2) равняется 22. Это сумма всех оценок по программированию. БДСУММ(БазаДанных;"Программирование";A1:F2) равняется 10. Это сумма оценок по программированию, полученных студентами, которые сдали историю на 3 и 4. БДПРОИЗВЕД(БазаДанных;"Информатика";A1:B2) равняется 900.Это произведение оценок по информатике, полученных студентами, которые сдали историю. ДСРЗНАЧ(БазаДанных;"Информатика";A1:B2) равняется 4. Это средняя оценка по информатике для студентов, сдавших историю. ДСРЗНАЧ(БазаДанных;3;БазаДанных) равняется 3.666667. Это средняя оценка по алгебре. БИЗВЛЕЧЬ(БазаДанных;"Информатика";Критерий) возвращает значение ошибки #ЧИСЛО!, потому что критерию удовлетворяет более чем одна запись.
|
