Что позволяет сделать диалоговое окно наложение условий по списку

Обновлено: 16.05.2024

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

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

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

Кроме того, для выбора данных можно использовать возможности поиска данных, формы и некоторые функции.

Использование автофильтра

Установка и снятие автофильтра

Для установки автофильтра необходимо выделить любую ячейку таблицы и выполнить команду Данные/Фильтр/Автофильтр. После этого команда Автофильтр в подчиненном меню Данные/Фильтр будет отмечена галочкой. Это означает, что фильтр включен и работает.

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

После установки автофильтра в названиях столбцов таблицы появятся значки раскрывающихся списков (ячейки A1: G1 в таблице на рис. 21.10).

Следует иметь в виду, что фильтры могут быть установлены только для одного списка на листе.

Для удаления фильтров необходимо выделить любую ячейку таблицы и выполнить команду Данные/Фильтр/Автофильтр.

Фильтры автоматически устанавливаются при оформлении списка и автоматически удаляются при преобразовании списка в обычный диапазон.

Работа с автофильтром

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

Заголовки строк листа, выбранных из таблицы с помощью фильтра, отображаются синим цветом. Синим цветом отображаются стрелки значков раскрывающихся списков в названиях столбцов, по которым была произведена выборка. В строке состояния окна Microsoft Excel в течение некоторого времени отображается текст с указанием количества найденных записей и общего количества записей в таблице.

Количество столбцов, по которым производится выборка, не ограничено.

Если данные уже отфильтрованы по одному из столбцов, при использовании фильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке.

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

Простая выборка

Для выборки данных, удовлетворяющих одному значению, следует щелкнуть по значку раскрывающегося списка соответствующего столбца и выбрать искомое значение. Например, в таблице на рис. 21.10 произведена выборка по столбцу "Модель" - ВАЗ 21093, по столбцу "Год" - 1995, по столбцу "Цвет" - "черный". В результате в выборке оказалось четыре записи ( рис. 21.11).

Выборка по условию

Можно производить выборку не только по конкретному значению, но и по условию. Например, в таблице на рис. 21.10 необходимо выбрать все автомобили с годом выпуска не ранее "1997".

Для применения условия следует щелкнуть по значку раскрывающегося списка соответствующего столбца и выбрать параметр (Условие. ). В диалоговом окне Пользовательский автофильтр ( рис. 21.12) в раскрывающемся списке с названием столбца, по которому производится отбор данных, следует выбрать вариант условия отбора, а в раскрывающемся списке справа - выбрать из списка или ввести с клавиатуры значение условия.

Всего существует 12 вариантов условий.

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

Для текстовых значений можно использовать следующие варианты условий: равно, не равно, содержит, не содержит, начинается с, не начинается с, заканчивается на, не заканчивается на.

Например, для отбора всех моделей автомобиля ВАЗ в столбце "Модель" таблицы на рис. 21.10 следует выбрать вариант условия начинается на и ввести значение ВАЗ. Для выбора всех моделей автомобилей, за исключением моделей ВАЗ, следует выбрать вариант условия не начинается на и ввести значение ВАЗ. Для выбора всех моделей автомобиля ВАЗ 2109, включая разновидности, можно выбрать вариант условия содержит и ввести значение ВАЗ 2109.

Одновременно можно применять два условия отбора, объединяя их союзом И, если требуется, чтобы данные удовлетворяли обоим условиям, или союзом ИЛИ, если требуется, чтобы данные удовлетворяли хотя бы одному из них.

Например, в столбце "Год" таблицы на рис. 21.10 с использованием двух условий можно отобрать автомобили с годом выпуска не ранее 1997 (первое условие - больше или равно 1997 ) и не позднее 2000 (второе условие - меньше или равно 2000 ). В этом случае следует использовать союз И. Если же в столбце "Цвет" таблицы на рис. 21.10 требуется отобрать автомобили только белого и черного цвета, то следует использовать союз ИЛИ: первое условие - равно белый, второе условие - равно черный.

Отбор наибольших и наименьших значений

Для отбора наибольших и наименьших значений следует щелкнуть по значку раскрывающегося списка соответствующего столбца и выбрать параметр (Первые 10. ). В диалоговом окне Наложение условия по списку ( рис. 21.13) в счетчике можно установить количество (или процент от общего числа) отбираемых элементов, в раскрывающемся списке следует выбрать принцип отбора (наибольшие или наименьшие значения). В последнем раскрывающемся списке следует выбрать режим отбора: отображение заданного числа строк с наибольшими или наименьшими значениями в данном столбце ( элементов списка ) или отображение заданного процента строк с наибольшими или наименьшими значениями в процентном отношении ( % от количества элементов ).

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

Под фильтрацией списков понимается выделение из всего списка только тех записей, значения полей которых удовлетворяют заданным условиями.
MS Excel позволяет выполнять фильтрацию с помощью трех инструментов, которые имеют различные функциональные возможности.

В Excel для фильтрации списков есть три инструмента - Автофильтр , который применяется в случае простых условий отбора, Пользовательский автофильтр и Расширенный фильтр для усложненных условий отбора.

Применение для фильтрации Автофильтра

Для вызова Автофильтра нужно выделить любую ячейку списка и на ленте Данные в группе Сортировка и фильтр кликнуть на пиктографической кнопке Фильтр .

После вызова Автофильтра на листе таблицы рядом с каждым заголовком столбца появятся кнопки в виде стрелок (раскрывающиеся списки) (рис.1).

Если кликнуть на любой стрелке, то раскроется список значений, допустимых для задания условий фильтрации по этому столбцу (рис. 2).

Если требуется, например, просмотреть все операции только по счету № 1 , нужно кликнуть по стрелке рядом со столбцом № счета и в раскрывшемся списке установить пометку (галочку) только для счета 1. Результат фильтрации представлен на рис. 3.

Для отмены результатов фильтрации нужно кликнуть на пиктограмме Фильтр на ленте Данные .

Наложение условий фильтрации по списку
Для наложения условий по списку:
- нужно кликнуть на стрелке рядом с наименованием атрибута списка;
- в раскрывшемся списке выбрать пункт Числовые фильтры (заметим что название этого пункта может меняться в зависимости от типа данных, содержащихся в столбце списка, например, для столбца Дата операции он будет иметь наименование Фильтры по дате );
- в выпашем списке ( рис. 4) выбрать пункт Первые 10 - откроется диалоговое окно Наложение условия по списку (рис. 5);

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

Пользовательский автофильтр

Применение пользовательского автофильтра позволяет задать более сложное условие для фильтрации. Чтобы включить пользовательский автофильтр нужно:
- в списке (рис. 2) выбрать пункт Числовые фильтры ;
- в раскрывшемся списке (рис. 4.) выбрать один из пунктов Равно . Между или Настраиваемый фильтр, откроется диалоговое окно Пользовательский автофильтр (рис. 6) ;
- в соответствующих полях этого диалогового окна установить значения, как показано на рисунке (используйте раскрывающиеся списки).

Например, решается задача отбора всех операций, проведенных в период с 5.01 по 24.01 включительно. Условия для фильтрации приведены на рис. 6.

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

Применение для фильтрации Расширенного фильтра

Расширенный фильтр, в отличие от «Автофильтра» позволяет:
- Задавать условия, соединенные одним или несколькими логическими операторами.
- Задать несколько условий для одного столбца.
- Задать вычисляемые условия.
- Скопировать результаты фильтрации в другое место.

Другим отличием от Автофильтра является то, что условия для фильтрации записываются в диапазоне ячеек свободного участка рабочего листа.

Задание диапазона условий

Рассмотрим технологию задания диапазона условий на примере отбора сведений об операциях по номерам счетов с 5 по 8 включительно. Для решения задачи выполним действия:

1. В свободных ячейках рабочего листа определим диапазон, в котором будет записано условие для фильтрации (например, C2:D3).
2. Запишем в первой строке этого диапазона наименования столбцов списка, на которые будут наложены условия фильтрации (это лучше сделать копированием), а во второй выражение условия (рис.7).

Заметим, что при записи выражений для условий на одной строке условия как бы соединяются логическим оператором «И». Если нужно применить логический оператор «ИЛИ», то условия нужно записать на разных строках одно под другим.

3. Установим курсор в область списка.
4. На ленте Данные в группе Сортировка и фильтр кликнем на кнопе пиктографического меню Дополнительно -откроется диалоговое окно Расширенный фильтр (рис. 8) .

5. В поле Исходный диапазон укажем адрес диапазона, в котором размещен список (в примере - $A$2:$E$124), а в поле Диапазон условий - адрес диапазона, содержащего условия фильтрации (в примере - $C$2:$D$3).
6. В поле Поместить результат в диапазон укажем адрес начальной ячейки диапазона, куда следует вывести результат (A6).
После клика на кнопке «ОК» получим выборку по заданным условиям(рис 9).

В диапазоне условий можно ввести любое количество условий. Возможно одновременное применение «И» и «ИЛИ».

Применение условий с вычисляемыми значениями

Вычисляемые условия позволяют использовать в условиях выборки значения, возвращаемые формулой. При их применении необходимо соблюдать правила:
- Заголовок над вычисляемым условием должен отличаться от заголовков столбцов в списке. Это условие противоположно требованиям для формирования обычных условий.
- Ссылки на ячейки, находящиеся вне списка, должны быть абсолютными.
- Ссылки на ячейки в списке должны быть относительными.

Пример.

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

- В любой ячейке вне списка запишем формулу вычисления среднего значения (например, в ячейке H2) (рис. 10).
- В ячейку H4 запишем слово «Выборка» (или что-либо другое), а в ячейке H5 - выражение вычисляемого условия =D3>$G$2. Если условия фильтрации в ячейках записаны правильно, то в них появится запись ИСТИНА или ЛОЖНО.
- Включим расширенный фильтр.
- В открывшемся диалоговом окне Расширенный фильтр введем адрес исходного диапазона, адрес диапазона условий, одну из опций обработки и адрес вывода результата (рис. 10).
- Кликнем на кнопке ОК.

КомандаДанные/Фильтр/ Автофильтр для каждого столбца строит список значений, и который используется для задания условий фильтрации (рис. 1). В каждом столбце появляется кнопка списка, нажав которую можно ознакомиться со списком возможных критериев выбора.

Рис. 1. Список с автофильтром

По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:

· все — выбираются все записи без ограничений;

· первые 10 — данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис.2) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;

· значения — будут выбраны только те записи, которые в данном столбце содержат указанное значение;

Рис. 2. Диалоговое окно «Наложение условия по списку»

· условие — выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис.3).

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

Условие для отбора записей по конкретным значениям в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ.

Каждая часть условия включает:

оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше),
значение, которое может выбираться из списка или содержать шаблонные символы *.

Пример. Для Кода предмета можно сформировать условия:

>=п* — отобрать все записи, которые содержат код предмета, начинающийся с буквып;

<>п1 — отобрать все записи, которые не содержат кода предметап1.

Можно задать условия отбора для нескольких столбцов независимо друг от друга, .фильтрация записей выполняется по всем условиям одновременно. Все записи, не прошедшие через фильтр, будут скрыты. Отфильтрованные записи можно выделить и скопировать в другое место, удалить. Отмена результата фильтрации и возврат к исходному состоянию списка производятся повторным вводом команды Данные/Автофильтр.

Фильтрация данных в списке ≈ это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные, Фильтр. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командой Данные, Форма.

Автофильтрация

Команда Данные, Фильтр, Автофильтр для каждого столбца строит список значений, и который используется для задания условий фильтрации (рис. 3.36). В каждом столбце появляется кнопка списка, нажав которую можно ознакомиться со списком возможных критериев выбора.

Рис. 3.36. Список с автофильтром

По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:

все ≈ выбираются все записи без ограничений;
первые 10 ≈ данный пункт позволяет во вновь появляющемся диалоговом окне ╚Наложение условия по списку╩ (рис.3.37) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;
значения ≈ будут выбраны только те записи, которые в данном столбце содержат указанное значение;

Рис. 3.37. Диалоговое окно ╚Наложение условия по списку╩

условие ≈ выбираются записи по формируемому пользователем условию в диалоговом окне ╚Пользовательский фильтр╩ (рис.3.3 8).

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

Каждая часть условия включает:

оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше),
значение, которое может выбираться из списка или содержать шаблонные символы *.

Пример. Для Кода предмета можно сформировать условия:

>=п* ≈ отобрать все записи, которые содержат код предмета, начинающийся с буквы п;

<>п1 ≈ отобрать все записи, которые не содержат кода предмета п1.

Расширенный фильтр

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

критерий сравнения;
вычисляемый критерий.

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

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

Технология использования расширенного фильтра состоит из двух этапов:

этап 1 ≈ формирование области критериев поиска;

этап 2 ≈ фильтрация записей списка.

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

Критерий сравнения формируется при соблюдении следующих требований:

состав столбцов области критериев определяется столбцами, по которым задаются условия фильтрации записей;
имена столбцов области критериев должны точно совпадать с именами столбцов исходного списка;
ниже имен столбцов располагаются критерии сравнения типа:
точного значения;
значения, формируемого с помощью операторов отношения;
шаблона значения, включающего символы * и (или) ?.

Правила формирования множественного критерия:

1. Если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И .

2. Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Пример. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами: 1-й способ. Множественный критерий сравнения ≈ все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка≈ оператор сравнения со значением константы.

2'й способ. Множественный критерий сравнения ≈ все условия (точи значения полей) находятся в одной строке, столбец Оценка используется дважды, связка И.

3-й способ. Множественный критерий сравнения ≈ условия (точные значения полей) записаны в двух строках, связка ИЛИ.

Вычисляемый критерий представляет собой формулу, записанную в строке области╩ условий, которая возвращает логическое значение ИСТИНА или ЛОЖЬ. Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (числе текст, дата, логическая константа), операторов отношения.

Внимание! Имя столбца, содержащего формулу вычисляемого критерия, должно отличаться от имени столбца в списке.

Пример. Выбрать записи о сдаче экзаменов студентами группы 133 с оценкой ниже общего среднего балла или записи с оценкой 5:

В области критериев присутствуют столбцы с заголовками: Номер группы.Оценка1.

Последовательность действий по созданию данного вычисляемого критерия:

присвоить отличное от имен полей списка новое имя столбцу, куда будет введен вычисляемый критерий;
установить курсор в ячейку ввода;
вызвать Мастер функций ≈ команда Вставка, Функция, выбор категории ≈Логические и выбор функции ≈ ИЛИ;
ввод параметров функции ИЛИ:

(при вводе формулы использовать курсорный указатель на ячейки таблицы, вызов встроенной функции СРЗНАЧ, указание на абсолютные ссылки с помощью клавиши )

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ ≈ результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому:

либо в комбинированном виде:

Э т а п 2 .Фильтрация записей расширенным фильтром. После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 3.39).

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

Исходный диапазон и диапазон условий включают все строки, в том числе и строку наименования столбцов. Если предполагается копирование результата в другое место, указывается левая верхняя ячейка области. Переключатель Только уникальные записи позволяет исключить дублирование записей.

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

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

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

Фильтрация с помощью формы данных

ППП Excel 97 позволяет работать с отдельными записями списка с помощью экранной формы (рис.3,40). Основные операции обработки записей списка: последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка,

При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей ≈ названия столбцов списка.

Для просмотра записей используется полоса прокрутки либо кнопки или , выводится индикатор номера записи. При просмотре записей возможно их редактирование. Поля, не содержащие формул, доступны для редактирования, вычисляемые или защищенные поля не редактируются. Корректировку текущей записи с помощью кнопки можно отменить.

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

С помощью экранной формы задаются критерии сравнения. Для этого нажимаете╩ кнопка , форма очищается для т да условий поиска в полях формы с помои кнопки , а название кнопки заменяется на название . По ввода критериев сравнения нажимаются кнопки или для просмотра отфильтрованных записей в нужном направлении. 1И просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка , для выхода из формы ≈ кнопка .

Рис.3.40. Экранная форма для работы со списком записей

ЗАДАНИЕ 1

Выберите данные из списка по критерию отбора, используя Автофильтр.

Проведите подготовительную работу ≈ переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных (см рис, 3.35).

Выберите из списка данные, используя критерий:

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

Отмените результат автофильтрации.

Выберите из списка данные, используя критерий: для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4.

Отмените результат автофильтрации.

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

ТЕХНОЛОГИЯ РАБОТЫ

Проведите подготовительную работу:

переименуйте ЛистЗ ≈Автофильтр
выделите блок ячеек исходного списка, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Автофильтр.

Выберите из списка данные, используя критерий ≈ для преподавателя ≈ а1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий ≈ л. Для этого:

установите курсор в область списка и выполните команду Данные, Фильтр, Автофильтр; в каждом столбце появятся кнопки списка;
сформируйте условия отбора записей:
в столбце Таб. № препод. нажмите кнопку , из списка условий отбора выберите а1;
в столбце Оценка нажмите кнопку, из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;
в столбце Вид занятия нажмите кнопку , из списка условий отбора выберите л.

Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные, Фильтр, Автофильтр.

4. Выберите из списка данные, используя критерий ≈ для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4. Для этого воспользуйтесь аналогичной п. 3 технологией фильтрации

5.Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные╩ Фильтр, Автофильтр.

6.Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

ЗАДАНИЕ 2

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

1.Проведите подготовительную работу ≈ переименуйте новый лист на Расширенный фильтр и скопируйте на него исходную базу данных рис.3.35).

2. Скопируйте имена полей списка в другую область на том же листе.

3. Сформируйте в области условий отбора Критерий сравнения ≈ о сдаче экзаменов! студентами группы 133 по предмету п1 на оценки 4 или 5.

4.Произведите фильтрацию записей на том же листе.

5.Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.

6.Сформируйте в области условий отбора Вычисляемый критерий ≈ для каждого пре╜подавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий ≈ л; результат отбора поместите на новый рабочий лист.

7.Произведите фильтрацию записей на новом листе.

8.Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.

ТЕХНОЛОГИЯ РАБОТЫ

1. Проведите подготовительную работу:

переименуйте Лист4 ≈ Расширенный фильтр

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

Этап 1. Формирование диапазона условий по типу Критерий сравнения

2. Скопируйте все имена полей списка (см. рис. 3.35) в другую область на том же листе например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 ≈ имена полей области критерия, J2:О5 ≈ область значений критерия.

3. Сформируйте в области условий отбора Критерий сравнения ≈ о сдаче экзаменов студентами группы 133 по предметуп1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

в столбец Номер группы ≈ точное значение ≈ 133;

в столбец Код предмета ≈ точное значения ≈ п1;

в столбец Оценка≈условие≈>3

Этап 2. Фильтрация записей расширенным фильтром.

4. Произведите фильтрацию записей на том же листе:

установите курсор в область списка (базы данных);

выполните команду Данные, Фильтр, Расширенный фильтр;

в диалоговом окне ╚Расширенный фильтр╩ с помощью мыши задайте параметры, например;

Скопировать результат в другое место: установите флажок

Исходный диапазон: A1:G17

Диапазон условия: J1:O5

Поместить результат в диапазон: J6

5. Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе, соблюдая технологию п.З и п.4.

Этап 1. Формирование диапазона условий по типу Вычисляемый критерий.

6. Сформируйте в области условий отбора Вычисляемый критерий ≈ для каждого пре╜подавателя выберите сведения о сдаче студентами экзамена на оценку выше средней, вид занятий ≈ л; результат отбора поместите на новый рабочий лист. Для этого:

в столбец Вид занятия введите точное значения ≈ букву л;

переименуйте в области критерия столбец Оценка, например, на имя Оценка 2:

в столбец Оценка1 введитевычисляемый критерий, например, вида

где G2 ≈ адрес первой клетки с оценкой в исходном списке,

$G$2 : $G$I7 ≈ блок ячеек с оценками,

СРЗНАЧ ≈ функция вычисления среднего значения.

Этап 2. Фильтрация записей расширенным фильтром.

7. Произведите фильтрацию записей на новом листе;

установите курсор в область списка (базы данных);

выполните команду Данные, Фильтр, Расширенный фильтр;

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

Скопировать результат в другое место: установите флажок

Исходный диапазон: A1:G17

Диапазон условия: Л:05

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

8. Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе, соблюдая технологию п.6 и п.7.

ЗАДАНИЕ 3

Используя Форму, выберите данные из списка.

1. Проведите подготовительную работу ≈ переименуйте новый лист на Форма и скопируйте на него исходную базу данных (см. рис.3,35).

2. Просмотрите записи списка с помощью формы данных, добавьте новые.

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

4. Просмотрите отобранные записи.

5. Сформируйте собственные условия отбора записей и просмотрите их,

ТЕХНОЛОГИЯ РАБОТЫ

1. Проведите подготовительную работу:

переименуйте Лист5 ≈ Форма;

выделите блок ячеек исходного списка, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Форма;

установите курсор в область списка и выполните команду Данные, Форма,

2. Просмотрите записи списка и внесите необходимые изменения с помощью кнопки и . С помощью кнопки добавьте новые записи.

3. Сформируйте условие отбора ≈ для преподавателя ≈ а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий ≈ л. Для этого:

нажмите кнопку , название которой поменяется на ;

в пустых строках имен полей списка введите критерии:

в строку Таб № препод. введите а1

в строку Вид занятия введите л;

в строку Оценка введите условие > 2

4. Просмотрите отобранные записи, нажимая на кнопку или .

5. Аналогично сформируйте собственные условия отбора записей и просмотрите их.

Начнем изучение Условного форматирования с проверки числовых значений на больше /меньше /равно /между в сравнении с числовыми константами.

Эти правила используются довольно часто, поэтому в EXCEL 2007 они вынесены в отдельное меню Правила выделения ячеек .

Эти правила также же доступны через меню Главная/ Стили/ Условное форматирование/ Создать правило, Форматировать только ячейки, которые содержат .

Рассмотрим несколько задач:

СРАВНЕНИЕ С ПОСТОЯННЫМ ЗНАЧЕНИЕМ (КОНСТАНТОЙ)

Задача1 . Сравним значения из диапазона A1:D1 с числом 4.

введем в диапазон A1:D1 значения 1, 3, 5, 7

выделим этот диапазон;

применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше );

в левом поле появившегося окна введем 4 – сразу же увидим результат применения Условного форматирования .

Нажмем ОК.

Результат можно увидеть в файле примера на листе Задача1 .

СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)

Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4.

Задача2 . Сравним значения из диапазона A1:D1 с числом из ячейки А2 .

введем в ячейку А2 число 4;

выделим диапазон A1:D1 ;

применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше );

в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует абсолютную ссылку $А$2 ).

В результате, все значения из выделенного диапазона A 1: D 1 будут сравниваться с одной ячейкой $А$2 . Те значения из A 1: D 1 , которые меньше A 2 будут выделены заливкой фона ячейки.

Результат можно увидеть в файле примера на листе Задача2 .

Чтобы увидеть как настроено правило форматирования, которое Вы только что создали, нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ; затем дважды кликните на правиле или нажмите кнопку Изменить правило . В результате увидите диалоговое окно, показанное ниже.

ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)

Теперь будем производить попарное сравнение значений в строках 1 и 2.

Задача3 . Сравнить значения ячеек диапазона A 1: D 1 со значениями из ячеек диапазона A 2: D 2 . Для этого будем использовать относительную ссылку.

введем в ячейки диапазона A2:D2 числовые значения (можно считать их критериями);

выделим диапазон A1:D1 ;

применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше )

в левом поле появившегося окна введем относительную ссылку на ячейку A2 (т.е. просто А2 или смешанную ссылку А$2 ). Убедитесь, что знак $ отсутствует перед названием столбца А.

Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.

Результат можно увидеть в файле примера на листе Задача3 .

Внимание! В случае использования относительных ссылок в правилах Условного форматирования необходимо следить, какая ячейка является активной в момент вызова инструмента Условное форматирование .

Примечание-отступление : О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками

При создании относительных ссылок в правилах Условного форматирования , они «привязываются» к ячейке, которая является активной в момент вызова инструмента Условное форматирование.

СОВЕТ : Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от Строки формул ). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в поле Имя , там будет отображен адрес активной ячейки A1 или D 1 . Почему возможно 2 вырианта и в чем разница для правил условного форматирования?

Посмотрим внимательно на второй шаг решения предыдущей задачи3 - выделение диапазона A 1: D 1 . Указанный диапазон можно выделить двумя способами: выделить ячейку А1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1 ; либо, выделить ячейку D1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1 . Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1 , а во втором D 1 !

Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой.

Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2 , мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2 . Т.к. правило распространяется на диапазон A 1: D 1 , то B 1 будет сравниваться с В2 и т.д. Задача будет корректно решена.

Если при создании правила Условного форматирования активной была ячейка D1 , то именно ее значение будет сравниваться со значением ячейки А2 . А значение из A 1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A 2 , EXCEL выберет самую последнюю ячейку XFD для С1 , затем предпоследнюю для B 1 и, наконец XFB2 для А1 ). Убедиться в этом можно, посмотрев созданное правило:

выделите ячейку A1 ;

нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ;

теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки 6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон. На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта). Подробнее можно ]]>прочитать здесь ]]> .

Если к диапазону ячеек применимо правило форматирования, то оно обладает приоритетом над форматированием вручную. Форматирование вручную можно выполнить при помощи команды Формат из группы Ячейки на вкладке Главная . При удалении правила условного форматирования форматирование вручную остается.

УСЛОВНОЕ ФОРМАТИРОВАНИЕ и ФОРМАТ ЯЧЕЕК

Условное форматирование не изменяет примененный к данной ячейке Формат (вкладка Главная группа Шрифт, или нажать CTRL+SHIFT+F ). Например, если в Формате ячейки установлена красная заливка ячейки, и сработало правило Условного форматирования, согласно которого заливкая этой ячейки должна быть желтой, то заливка Условного форматирования "победит" - ячейка будет выделены желтым. Хотя заливка Условного форматирования наносится поверх заливки Формата ячейки, она не изменяет (не отменяет ее), а ее просто не видно.

Через Формат ячеек можно задать пользовательский формат ячейки , который достаточно гибок и иногда даже удобнее, чем Условное форматирование. Подробнее см. статью Пользовательский ЧИСЛОвой формат в MS EXCEL (через Формат ячеек) .

ОТЛАДКА ПРАВИЛ УСЛОВНОГО ФОРМАТИРОВАНИЯ

Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено.

Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1

В тех столбцах, где результат формулы равен ИСТИНА, условное форматирование будет применено, а где ЛОЖЬ - нет.

ИСПОЛЬЗОВАНИЕ В ПРАВИЛАХ ССЫЛОК НА ДРУГИЕ ЛИСТЫ

До MS Excel 2010 для правил Условного форматирования нельзя было напрямую использовать ссылки на другие листы или книги. Обойти это ограничение можно было с помощью использования имен . Если в Условном форматирования нужно сделать, например, ссылку на ячейку А2 другого листа, то нужно сначала определить имя для этой ячейки, а затем сослаться на это имя в правиле Условного форматирования . Как это реализовано См. файл примера на листе Ссылка с другого листа .

ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ

на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найтии выделить ,

выберите в списке пункт Условное форматирование .

Будут выделены все ячейки для которых заданы правила Условного форматирования.

ДРУГИЕ ПРЕДОПРЕДЕЛЕННЫЕ ПРАВИЛА

В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.

Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее.

Текст содержит… Приведем пример. Пусть в ячейке имеется слово Дрель . Выделим ячейку и применим правило Текст содержит …Если в качестве критерия запишем ре (выделить слова, в которых содержится слог ре ), то слово Дрель будет выделено.

Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами.

Как видно из рисунка выше, Условное форматирование можно настроить выделять не только ячейки, содержащие определенный текст, но и не содержащие , начинающиеся с и заканчивающиеся на определенный текст. Кроме того, в случае условий содержит и не содержит возможно применение подстановочных знаков ? и * .

Пусть снова в ячейке имеется слово Дрель . Выделим ячейку и применим правило Текст содержит … Если в качестве критерия запишем р?, то слово Дрель будет выделено. Критерий означает: выделить слова, в которых содержатся слога ре, ра, ре и т.д. Надо понимать, что также будут выделены слова с фразами р2, рм, рQ , т.к. знак ? означает любой символ. Если в качестве критерия запишем . (выделить слова, в которых не менее 6 букв), то, соответственно, слово Дрель не будет выделено. Можно, конечно подобного результата добиться с помощью формул с функциями ПСТР() , ЛЕВСИМВ() , ДЛСТР() , но этот подход, согласитесь, быстрее.

Повторяющиеся значения… Это правило позволяет быстро настроить Условное форматирование для отображения уникальных и повторяющихся значений. Под уникальным значением Условное форматирование подразумевает неповторяющееся значение, т.е. значение которое встречается единственный раз в диапазоне, к которому применено правило. Чтобы выделить уникальные значения (т.е. все значения без их повторов), то см. эту статью .

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

Значение ячейки. Это правило доступно через меню Главная/ Стили/ Условное форматирование/ Создать правило . В появившемся окне выбрать пункт форматировать ячейки, которые содержат. Выбор опций позволит выполнить большинство задач, связанных с выделением числовых значений.

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

Задача4 . Пусть имеется 21 значение, для удобства отсортированных по возрастанию . Применим правило Последние 10 элементов и установим, чтобы было выделено 3 значения (элемента). См. файл примера , лист Задача4 .

Слова "Последние 3 значения" означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они.

Соответственно, правила, примененные к нашему списку: "Последнее 1 значение", "Последние 2 значения", . "Последние 6 значений" будут приводить к одинаковому результату - выделению 6 значений равных 10.

К сожалению, в правило нельзя ввести ссылку на ячейку, содержащую количество значений, можно ввести только значение от 1 до 1000.

Применение правила "Последние 7 значений" приведет к выделению дополнительно всех значений равных 11, .т.к. 7-м минимальным значением является первое сверху значение 11.

Аналогично можно создать правило для выделения нужно количества наибольших значений, применив правило Первые 10 элементов .

Рассмотрим другое родственное правило Последние 10% .

Обратите внимание, что на картинке выше не установлена галочка "% от выделенного диапазона". Эта галочка устанавливается либо в ручную или при применении правила Последние 10% .

В этом правиле задается процент наименьших значений от общего количества значений в списке. Например, задав 20% последних, будет выделено 20% наименьших значений.

Попробуем задать 20% последних в нашем списке из 21 значения: будет выделено шесть значений 10 (См. файл примера , лист Задача4) . 10 - минимальное значение в списке, поэтому в любом случае будут выделены все его повторы.

Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.

ПРАВИЛА С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ

Создание правил форматирования на основе формул ограничено только фантазией пользователя. Здесь рассмотрим только один пример, остальные примеры использования Условного форматирования можно найти в этих статьях: Условное форматирование Дат ; Условное форматирование Чисел ; Условное форматирование Текстовых значений ; другие задачи .

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

Выделите ячейки, к которым нужно применить Условное форматирование (пусть это ячейка А1 ).

Вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )

Выберите Использовать формулу для определения форматируемых ячеек

Того же результата можно добиться по другому:

Вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )

Выделите пункт Форматировать только ячейки, которые содержат ;

В разделе Форматировать только ячейки, для которых выполняется следующее условие: в самом левом выпадающем списке выбрать Ошибки.

СОВЕТ : Отметить все ячейки, содержащие ошибочные значения можно также с помощью инструмента Выделение группы ячеек .

Читайте также:


Самодельные маяки для бетона

Какую печь выбрать для бани кирпичную или металлическую

Какой ламинат лучше или классический

Укладка террасной доски заокский

Дом без трубы на крыше психология