Расширенный фильтр с использованием вычисляемых значений

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

Ø Заголовок над вычисляемым условием должен обязательно отличаться от любого заголовка столбца в таблице. Заголовок условия может быть пустым или содержать произвольный текст. Внимание! Это ограничение прямо противоположно требованию для обычных условий.

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

Ø Ссылки на ячейки в таблице должны быть относительными.

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

· В ячейку N1 ввести: «Среднее значение».

· В ячейку N2 ввести формулу среднего значения цены =СРЗНАЧ(F7: F22).

· В ячейку N5 ввести заголовок для диапазона условий: «Выше среднего».

· В ячейку N6 ввести формулу сравнения: =F7> $N$2.

Формула условия сравнивает со средним значением ячейку F7, т.к. ячейка F7 является первой ячейкой в столбце «Цена» неотфильтрованного списка, и автоматически со средним значением будут сравниваться все нижерасположенные ячейки этого столбца.

· Скопировать шапку таблицы в ячейку А25 для того, чтобы поместить результат фильтрации в другую часть рабочего листа (копировать можно не всю шапку таблицы, а отдельные названия граф в соответствии с требованиями пользователя).

· Установить курсор в область таблицы.

· На вкладке Данные в группе Сортировка и фильтр нажать кнопку Дополнительно. При этом появилось диалоговое окно Расширенный фильтр и вся таблица выделилась подвижной рамкой.

· В области Обработка выбрать Скопировать результат в другое место.

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

· Установить курсор в окно Диапазон условий и выделить с помощью мыши на рабочем листе диапазон ячеек N5: N6.

· В окне Поместить результат в диапазон установить курсор, а затем на рабочем листе указать с помощью мыши диапазон ячеек А25: I25.

· Щелкнуть по кнопке ОК.

· Проанализировать результаты фильтрации и убрать фильтр.

Того же результата фильтрации можно добиться и другим способом. Можно не рассчитывать отдельно вне таблицы среднее значение столбца «Цена», а в диапазоне условий под заголовком «Выше среднего» в ячейке N6 сразу ввести формулу сравнения: =F7> СРЗНАЧ($F$7: $F$22).

· Выполнить фильтрацию таким способом самостоятельно и предъявить работу преподавателю.