Примеры условий отбора расширенного фильтра

В условия отбора расширенного фильтра может входить несколько условий:

а) накладываемых на один столбец. Чтобы задать для отдельного столбца три или более условий отбора, они вводятся в ячейки, расположенные в смежных строках. Например, для следующего диапазона условий будут отобраны строки, содержащие либо «Белов», «Батурин» или «Сушкин» в столбце «Студент».

б) накладываемых одновременно на несколько столбцов. Для этого условия вводятся в ячейки, расположенные в одной строке диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах группы 98ИС, сдавших экзамен по физике на 4 или 5 и по культурологии – на 5.

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

г) сложное условие отбора, накладываемое на несколько столбцов. Для этого вводят его составные части в отдельные строки диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах групп 98ИС и 98ВТ1, сдавших экзамен по физике на 4 и 5.

Виды условий отбора

При настройке автофильтра или вводе условий отбора в диапазоне условий расширенного фильтра можно использовать перечисленные ниже элементы условий:

1. Последовательности символов. При использовании текстовой константы в качестве условия отбора будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности символов. Например, при вводе условия «Пет» будут отобраны строки с ячейками, содержащими фамилии Петров, Петеров и Петухов. Чтобы получить точное соответствие отобранных значений заданному образцу, например, текст, следует ввести условие: =''=текст''.

2. Знаки подстановки. Используются для того, чтобы отобрать строки с ячейками, содержащими последовательность символов, в некоторых позициях которой могут стоять произвольные символы. Знак подстановки эквивалентен одному символу или произвольной последовательности символов. Например, знак ? заменяет любой символ в той же позиции, что и сам знак (так, д?м задает поиск строк «дом« и «дым»); знак * задает любую последовательность символов в той же позиции, что и сам знак (так, *ино задает поиск строк «Люблино» и «Выхино»)

3. Значения сравнения. Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать оператор сравнения. Условие отбора с оператором сравнения следует ввести в ячейку ниже заголовка столбца в диапазоне условий. Например, чтобы отобрать строки, имеющие значения ячеек большие или равные 1000, введите условие отбора >=1000 ниже заголовка «Количество». Отметим, что строчные и прописные буквы при фильтрации данных не различаются.

Формы

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

Вставка записейвыполняется с помощью кнопки Добавить.Вводятся поля новой записи, используя клавишу TAB для перемещения к следующему полю (для перемещения к предыдущему полю используйте сочетание клавиш SHIFT+TAB). Чтобы добавить запись в список, нажмите клавишу ENTER. Чтобы отменить добавление записи, нажмите кнопку Вернуть перед нажатием клавиши ENTER или кнопки Закрыть.

Изменение записейтребует предварительного размещения курсора в нужной ячейке. После изменения данных нажмите клавишу ENTER для обновления текущей записи и перехода к следующей записи. Чтобы отменить изменения записи, нажмите кнопку Вернуть перед нажатием клавиши ENTER или кнопки Закрыть, пока эта запись активна в форме.

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

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

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

Задание к работе

1. Использовать список студентов из работы 3.

2. С помощью Формы:

· Добавить в список несколько записей в разные его места.

· Модифицировать часть записей, найдя их через Критерий в соответствии с фамилией.

· Удалить несколько записей, найдя их через Критерий в соответствии с фамилией.

3. Отфильтровать список, выбрав только отличников по всем дисциплинам. Скопировать результат во второй лист.

4. Отфильтровать список, выбрав тех, кто сдал сессию, т.е. получил положительные оценки. Скопировать результат в третий лист.

5. Отфильтровать список, выбрав тех, кто получил только оценки 3 или 4. Скопировать результат в четвертый лист.

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

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

8. Все листы поименовать по смыслу.

9. Показать результаты преподавателю.

10.

Лабораторная работа 7

Средства выборки из списков с агрегированием данных

Цель работы – изучение функций работы с базами данных.

В Microsoft Excel имеется 12 функций рабочего листа, используемых для анализа данных из списков или баз данных. Каждая из этих функций, которые из соображений совместимости имеют обобщенное название БДФункция, использует три аргумента: база_данных, поле и критерий. Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются данной функцией. Обращение к БД-функциям выполняется через мастера функций (категория Работа с базой данных) или через ввод названия функции и ее параметров с клавиатуры. При этом синтаксис функции имеет вид:

БДФункция(база_данных;поле;критерий),

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

поле определяет столбец, используемый функцией. Поля данных в списке должны содержать идентифицирующее имя в первой строке. Аргумент «поле» может быть задан как текст с названием столбца в двойных кавычках, например "Алгебра" или "Информатика" в приведенном ниже примере базы данных, или как число, задающее положение столбца в списке: 1 - для первого поля (Студент), 2 - для второго поля (История), и так далее;

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

При этом следует иметь в виду следующее:

· Любой интервал, который содержит, по крайней мере, одно название столбца и по крайней мере одну ячейку под названием столбца с условием, может быть использован как аргумент критерий БДФункции. Например, если интервал G1:G2 содержит название столбца Программирование в ячейке G1 и величину 10 000 в ячейке G2, то можно определить интервал СоответствуетПрибыли и использовать это имя как аргумент критерий в функции баз данных.

· Несмотря на то, что диапазон критериев может располагаться в любом месте таблицы, не стоит помещать его внизу списка. Это связано с тем, что данные, добавляемые в список командой Форма меню Данные, вставляются с первой строки после списка. Если эта строка не пустая, Microsoft Excel не сможет добавить данные в список.

· Диапазон критериев не должен перекрываться со списком.

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

Рассмотрим некоторые функции базы данных.

Функция ДСРЗНАЧ(база_данных;поле;критерий) -возвращает среднее значение выбранных фрагментов базы данных.

Функция БСЧЁТ(база_данных;поле;критерий) -подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных указанном поле всех записей, удовлетворяющих заданным условиям. Аргумент поле не является обязательным. Если аргумент поле опущен, то функция БСЧЁТ подсчитывает количество записей в базе данных, отвечающих критериям.

Функция БСЧЁТА(база_данных;поле;критерий) -подсчитывает все непустые ячейки в столбце списка или базы данных, которые удовлетворяют заданным условиям.

Функция БИЗВЛЕЧЬ(база_данных;поле;критерий) -извлекает из базы данных одну запись, удовлетворяющую заданному условию. Если ни одна из записей не удовлетворяет критерию, то функция БИЗВЛЕЧЬ возвращает значение ошибки #ЗНАЧ!. Если более чем одна запись удовлетворяет критерию, то функция БИЗВЛЕЧЬ возвращает значение ошибки #ЧИСЛО!.

Функция ДМАКС(база_данных;поле;критерий) -возвращает максимальное значение среди выделенных записей базы данных.

Функция ДМИН(база_данных;поле;критерий) -возвращает минимальное значение среди выделенных записей базы данных.

Функция БДПРОИЗВЕД(база_данных;поле;критерий) -перемножает значения определенных полей записей базы данных, удовлетворяющих условию.

Функция БДСУММ(база_данных;поле;критерий) - суммирует числа в поле столбца записей базы данных, удовлетворяющих условию.

Например, пусть есть база данных по результатам сессии (рис.1). Каждая запись содержит информацию об одном студенте. Диапазон A5:E11 имеет название БазаДанных, а диапазон A1:F3 - Критерий.

Рис. 1. Пример базы данных

Тогда различные функции дадут следующие результаты:

БСЧЁТ(БазаДанных;"Алгебра";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. Это средняя оценка по алгебре.

БИЗВЛЕЧЬ(БазаДанных;"Информатика";Критерий) возвращает значение ошибки #ЧИСЛО!, потому что критерию удовлетворяет более чем одна запись.

Задание к работе

1. Загрузить таблицу из третьей работы.

2. С помощью функций базы данных определить:

- среднее значение стипендии у студентов, сдавших информатику на 4;

- число студентов, получивших хотя бы одну тройку;

- найти студента, получившего по информатике 2, и вывести на экран полную информацию по нему;

- найти максимальную стипендию среди студентов, получивших по информатике 4;

- найти сумму стипендий студентов, получивших по информатике 4 и по физике 5.

3. Результаты решения задач из п.2 разместить на том же листе, что и исходная таблица, и снабдить каждый результат примечанием с указанием того, какая задача решалась.

4. Показать результат преподавателю.

Лабораторная работа 8

Сводные таблицы

Цель работы – изучение средств создания и модификации сводных таблиц.

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

Поиск по сайту: