Вычисляемые условия отличаются от обычных условий сравнения тем, что позволяют использовать сравнения со значениями, которые вычисляются формулой. Для правильного использования такой фильтрации необходимо выполнять следующие правила:
Ø Заголовок над вычисляемым условием должен обязательно отличаться от любого заголовка столбца в таблице. Заголовок условия может быть пустым или содержать произвольный текст. Внимание! Это ограничение прямо противоположно требованию для обычных условий.
Ø Ссылки на ячейки, которые находятся вне таблицы (списка), должны быть абсолютными (при адресации используется знак доллара).
Ø Ссылки на ячейки в таблице должны быть относительными.
Упражнение. Необходимо вывести на экран данные о товарах закупленных магазинами по цене выше среднего значения, и поместить результат фильтрации в другую часть текущего листа, для этого:
· В ячейку N1 ввести: «Среднее значение».
· В ячейку N2 ввести формулу среднего значения цены =СРЗНАЧ(F7:F22).
|
|
· В ячейку N5 ввести заголовок для диапазона условий: «Выше среднего».
· В ячейку N6 ввести формулу сравнения: =F7>$N$2.
Формула условия сравнивает со средним значением ячейку F7, т.к. ячейка F7 является первой ячейкой в столбце «Цена» неотфильтрованного списка, и автоматически со средним значением будут сравниваться все нижерасположенные ячейки этого столбца.
· Скопировать шапку таблицы в ячейку А25 для того, чтобы поместить результат фильтрации в другую часть рабочего листа (копировать можно не всю шапку таблицы, а отдельные названия граф в соответствии с требованиями пользователя).
· Установить курсор в область таблицы.
· На вкладке Данные в группе Сортировка и фильтр нажать кнопку Дополнительно. При этом появилось диалоговое окно Расширенный фильтр и вся таблица выделилась подвижной рамкой.
· В области Обработка выбрать Скопировать результат в другое место.
· Проверить, что в окне Исходный диапазон выделен блок ячеек, соответствующий всей выделенной таблице.
· Установить курсор в окно Диапазон условий и выделить с помощью мыши на рабочем листе диапазон ячеек N5:N6.
· В окне Поместить результат в диапазон установить курсор, а затем на рабочем листе указать с помощью мыши диапазон ячеек А25:I25.
· Щелкнуть по кнопке ОК.
· Проанализировать результаты фильтрации и убрать фильтр.
Того же результата фильтрации можно добиться и другим способом. Можно не рассчитывать отдельно вне таблицы среднее значение столбца «Цена», а в диапазоне условий под заголовком «Выше среднего» в ячейке N6 сразу ввести формулу сравнения: =F7>СРЗНАЧ($F$7:$F$22).
· Выполнить фильтрацию таким способом самостоятельно и предъявить работу преподавателю.