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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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