Сравнение Значения с Диапазонами в Эксель
Если необходимо выполнить операцию, выходящую за рамки автофильтра, придется прибегнуть к помощи средств расширенной фильтрации. Расширенный фильтр гораздо более гибкий, чем автофильтр, однако при его использовании требуется выполнять больше подготовительных действий. Расширенный фильтр позволяет выполнить следующее:
Прежде чем использовать средство расширенной фильтрации, необходимо задать диапазон критериев. Диапазон критериев — это специально отведенная область рабочего листа, отвечающая определенным требованиям. Диапазон критериев используется Excel для фильтрации списка и должен отвечать следующим требованиям:
Чтобы выполнить фильтрацию, выберите команду Данные | Фильтр | Расширенный фильтр . Появится диалоговое окно Расширенный фильтр (рис. 2).
Определите диапазон списка и диапазон критериев и убедитесь, что установлен переключатель Фильтровать список на месте . Щелкните на кнопке ОК , и список будет отфильтрован по заданным критериям.
Если в диапазоне критериев используется несколько строк, критерии в каждой строке соединены оператором ИЛИ . На рисунке 3 показан диапазон критериев A1:D3 с двумя строками критериев.
Рис.3. В этом диапазоне критериев содержится два набора критериев
В этом примере строки отфильтрованного списка отвечают следующим требованиям:
Это пример фильтрации, которая не может быть выполнена с помощью автофильтра. Диапазон критериев может иметь любое количество строк, каждая из которых соединена одна с другой оператором ИЛИ.
Элементы, которые указываются в диапазоне критериев, можно разделить на следующие типы:
- Текстовые или числовые критерии. При фильтрации используется сравнение с числом или строковой (текстовой) константой с помощью операторов, таких как равно ( = ), больше чем ( > ), не равно ( ) и т.д.
- Вычисляемые критерии. Фильтрация, использующая вычисления любого рода.
В таблице 1 представлен список операторов сравнения, которые можно использовать в текстовых или числовых критериях.
В таблице 2 показаны примеры строковых (текстовых) критериев.
Вычисляемый критерий может сделать операцию фильтрации более гибкой. Вычисляемый критерий основывается на одном или нескольких вычислениях. На рисунке 4 показан пример простого списка, состоящего из кодов проектов, даты начала, даты окончания и количества привлеченных к проекту ресурсов.
Рис.4. Этот список будет отфильтрован с использованием вычисляемого критерия
Над списком в диапазоне А1:А2 помещен диапазон критериев. Однако обратите внимание на то, что в диапазоне критериев используются не названия полей списка, а название нового поля. Вычисляемый критерий, по существу, вычисляет для списка новое поле. Поэтому следует помещать название поля в первую строку диапазона критериев.
В ячейке А2 содержится следующая формула: = С5 — В5 + 1 >= 30
Если необходимо отфильтровать список так, чтобы были показаны только проекты, которые используют ресурсы больше среднего, примените следующую формулу: = D5 > СРЗНАЧ(D:D)
Отфильтрованный в результате список будет содержать только те строки, в которых значение в поле Ресурсы превосходит среднее значение этого же поля.
При использовании вычисляемых критериев рекомендуется помнить некоторые правила:
- Не используйте заголовки полей списка в диапазоне критериев. Создайте новый заголовок или просто оставьте пустую ячейку.
- Можно использовать любое количество вычисляемых критериев, а также сочетания вычисляемых критериев с невычисляемыми.
- Не обращайте внимание на значения, возвращаемые формулами в диапазоне критериев. Они ссылаются на первую строку списка.
- Если Ваша вычисляемая формула ссылается на значение вне списка, используйте абсолютные, а не относительные ссылки. Например, вместо C1 используйте ссылку $C$1 .
- При создании формул вычисляемых критериев используйте первую строку списка (не строку заголовков). Используйте относительные, а не абсолютные ссылки. Например вместо $C$5 используйте ссылку C5 .
В диалоговом окне Расширенный список представлены еще две опции:
Если Вы выберите переключатель Скопировать результат в другое место в диалоговом окне Расширенный фильтр , то отобранные строки будут скопированы в другое место активного рабочего листа или на другой лист. Место определяется в поле Поместить результат в диапазон . Обратите внимание, что при использовании этой опции сам список не фильтруется.
При выборе опции Только уникальные записи все одинаковые строки, отвечающие определенному критерию, будут скрыты. Если Вы не установили диапазон критериев, то в списке будут скрыты все одинаковые строки.
На следующем шаге мы рассмотрим использование в списках функций баз данных.
Выберите сводную таблицу результата Кликните по пиктограмме Экспресс-анализа Примените «Цветовую шкалу».
Как сравнить два столбца в Excel — методы сравнения данных Excel | Exceltip
Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.
Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.
Использование формулы подстановки ВПР
Принцип работы формулы аналогичен предыдущей методике, отличие заключается в использовании формулы ВПР, вместо ПОИСКПОЗ. Отличительной особенностью данного метода также является возможность сравнения двух горизонтальных массивов, используя формулу ГПР.
Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.
Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.
=АДРЕС(1;1;4) – возвращает A1.
=АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].
Сравнение Значения с Диапазонами в Эксель
В Microsoft Excel 2013 существует простой способ изменения или удаления имен диапазонов. Перейдите на вкладку ФОРМУЛЫ, выберите группу Определенные имена и откройте Диспетчер имен. Появится список имен всех диапазонов (рис. 1.9).
