zanyatieExcel_3

9.Выберите в списке в качестве условия отбора Студент и нажмите кнопку

OK.

Врезультате в окне базы данных останется информация, касающаяся только студентов (Рисунок 12).

Рисунок 12. Отфильтрованной фрагмент базы данных.

При фильтрации нумерация строк не меняется, и номера отфильтрованных записей выделяются другим цветом. Кнопка у названия столбца в Excel 97-2003 также изменяет свой цвет (в Excel 2007 цвет кнопки не меняется, но на ней

появляется значок воронки с фильтрующим элементом ) показывая, что она используется в качестве условия для фильтра.

Чтобы отменить применение фильтра нужно выбрать пункт Все в выпадающем списке.

Программа позволяет создавать довольно сложные фильтры, используя пункт условие. Появляющийся при этом диалоговое окно Пользовательский автофильтр, Рисунок 13, содержит несколько выпадающих списков-окошек:

•Левые два окошка содержат логические операции: равно, больше,

меньше, не равно, больше или равно, меньше или равно и указания для поиска данных в строке: содержит, не содержит, начинается с, не начинается с, заканчивается на, не заканчивается на,

Рисунок 14.

Рисунок 13. Диалоговое окно Пользовательский автофильтр

•Поля для операций объединяются по правилу И или ИЛИ. (Устанавливается переключателем).

•Правые два окошка содержат значения возможных условий из выбранного списка (столбца) фильтрации.

Рисунок 14. Фрагмент диалогового окна Пользовательский автофильтр

10.Щелкните по кнопке списка справа от поля Род занятий. и выберите позицию Условие (в Excel 2007 раскройте вложенный список Текстовые фильтры и выберите Настраиваемый фильтр)

Открывается диалоговое окно Пользовательский автофильтр.

11.В открывшемся диалоговом окне Пользовательский автофильтр (Рисунок 13), введите в качестве условия «равно», а в качестве значения

«студент».

12.Нажмите кнопку OK.

Результат будет такой же, как и на Рисунок 12

Упражнение «Расширенный фильтр».

Более сложные условия можно фильтрации можно реализовать командой Расширенный

фильтр.

1.Отмените результаты выполнения предыдущего упражнения.

2.Подготовьте область для хранения условий отбора. Каждое условие записывается в две ячейки: в верхнюю – имя поля, в нижнюю – знак отношения (>,<,>=,<=,< >) и значение.

Использование знака равенства для ввода текста и значений

Поскольку при вводе текста или значения в ячейку знак равенства (=) используется для обозначения формулы, то Excel вычисляет введенную формулу; однако это может привести к неверным результатам отбора. Чтобы указать оператор сравнения для текста или значения, введите условие в виде строкового выражения в соответствующую ячейку диапазона условий отбора:

=''=ввод'',

где ввод — искомый текст или значение.

Чтобы отобрать запись, одновременно удовлетворяющую двум условиям,

•Род занятий - студент

•Дата рождения - 19.02.1992:

3.В ячейки К1 и L1 введите названия полей

Дата рождения и Род занятий

соответственно.

4. В ячейку K2 введите

19.02.1992

в ячейку L2

студент

Область для хранения условий отбора подготовлена.

В данном случае два условия соединены логическим действием "И" – значения условий располагаются в одной и той же строке области для хранения условий отбора:

Рисунок 15. Область для хранения условий отбора

5. В пункте меню Данные (в Excel 2007 вкладка Данные) щелкните на команде и позиции списка Расширенный фильтр (в Excel 2007 команда

Дополнительно группы Сортировка и фильтр).

Открывается диалоговое окно Расширенный фильтр.

Рисунок 16. Диалоговое окно Расширенный фильтр

Обратите внимание, что исходный диапазон данных отображается автоматически как первая и последняя заполненная ячейка списка.

6.Введите в окошко Диапазон условий первую и последнюю ячейки подготовленной области для хранения условий отбора:

•установите курсор в это окошко;

•выделите мышкой диапазон ячеек K1:L2

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

Рисунок 17. Диалогов окно с запоенным диапазоном условий отбора

7. Щелкните кнопку ОК.

Результаты фильтрации приведены ниже:

Рисунок 18. Результаты фильтрации расширенным фильтром.

Измените действие, соединяющее два условия отбора на "ИЛИ", т.е. отберите записи, содержащиеся или дату рождения 19.02.1992, или род занятий студент

8.Для объединения с помощью "ИЛИ" пропустите между именем второго поля и вторым условием строчку (Рисунок 19).

Рисунок 19. Область для хранения условий отбора, соединенных при помощи логического "ИЛИ"

9.Введите в окошко Диапазон условий первую и последнюю ячейки новой

области для хранения условий отбора и щелкните кнопку ОК.

Результаты фильтрации приведены ниже:

Рисунок 20. Результаты фильтрации расширенным фильтром при соединении условий логическим ИЛИ.

Диалоговое окно Расширенный фильтр позволяет скопировать отфильтрованные данные в другое место того же листа. Подготовьте область листа для копирования отфильтрованных данных:

1.Выделите названия полей базы данных, т.е. ячейки A1:G1, и скопируйте их в ячейки A41:G41.

2.Откройте диалоговое окно Расширенный фильтр.

3.Введите в окошко Диапазон условий первую и последнюю ячейки новой

области для хранения условий отбора.

4.Установите переключатель Обработка в положение Скопировать результат в другое место.

5.Введите в окошко Поместить результат в диапазон диапазон ячеек A41:G41 и нажмите кнопку ОК.

Результаты фильтрации приведены ниже:

Рисунок 21. Результаты фильтрации, скопированные в другое место листа.

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

6.Переместите результаты фильтрации рисунка 21 на лист3 (если листа3 нет, создайте его) книги Учебная база1, переименуйте лист3 в Отфильтрсписок и сохраните книгу.

ФункцииВПРиГПР

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

Синтаксис

ВПР(искомое значение; таблица; номер столбца; интервальный просмотр)

•Искомое значение — значение, которое должно быть найдено в первом столбце таблицы. Этот аргумент может быть значением или ссылкой. Если искомое значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР показывает ошибку #Н/Д.

•Таблица — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое значение». Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.

•Номер столбца — номер столбца в аргументе «таблица», из которого берется соответствующее значение. Если номер столбца = 1, то значение берется из первого столбца таблицы; если номер столбца = 2 — берется значение из второго столбца таблицы и т. д. Если значение аргумента «номер столбца»:

-меньше 1, функция ВПР показывает ошибку #ЗНАЧ!;

-больше, чем число столбцов в таблице, функция ВПР показывает ошибку

#ССЫЛ!.

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

-Если этот аргумент имеет значение ИСТИНА или опущен, то берется точное или приблизительное значение. В этом случае значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке, иначе функция ВПР может дать неправильный результат

-Если точное соответствие не найдено, то берется наибольшее значение, которое меньше, чем искомое значение.

-Если данный аргумент имеет значение ЛОЖЬ, функция ВПР ищет только точное соответствие. В этом случае сортировка значений в первом столбце аргумента «таблица» не обязательна. Если в этом первом столбце имеется два или более значений, соответствующих аргументу «искомое значение», используется первое найденное значение. Если точное соответствие не найдено, показывается значение ошибки #Н/Д.

•При поиске текстовых значений в первом столбце аргумента «таблица» данные в этом столбце не должны содержать начальных пробелов, конечных пробелов, используемых не по правилам прямых (' или ") и фигурных (‘ или “) кавычек или

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

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

•Если значение аргумента «интервальный просмотр» — ЛОЖЬ, а аргумент «искомое значение» представляет собой текст, то в аргументе «искомое значение» допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому знаку; звездочка — любой последовательности знаков. Чтобы найти сами эти знаки, следует поставить перед ними знак тильды (~).

5. В ячейку B43 введите формулу =ВПР(A2;A2:H36;3;ЛОЖЬ).

В ней отобразится отчество Сергеевна

6. В ячейку B44 введите формулу =ВПР(A2;A2:H36;4;ЛОЖЬ).

В ней отобразится адрес ул. Галкина, 1-23

7. В ячейку B45 введите формулу =ВПР(A2;A2:H36;5;ЛОЖЬ).

В ней отобразится телефон 12-34-56

8. В ячейку B46 введите формулу =ВПР(A2;A2:H36;6;ЛОЖЬ).

В ней отобразится дата рождения 09.09.1932

9. В ячейку B47 введите формулу =ВПР(A2;A2:H36;7;ЛОЖЬ).

В ней отобразится род занятий пенсионер

10. В ячейку B48 введите формулу =ВПР(A2;A2:H36;8;ЛОЖЬ).

В ней отобразится дата приема.

Лист примет вид:

Рисунок 22. Результаты выполнения функции ВПР.