Иногда возникает задача определить, есть ли в диапазоне некое значение. Часто задача усложняется выявлением ячейки совпадения, или количеством совпадений. Рассмотрим несколько вариантов для версии Excel 365.
Функция ИЛИ
Одно из простейших решений связано с функцией ИЛИ. Эта функция сравнит каждый элемент диапазона с тестом, и вернет значение ИСТИНА, если хотя бы одно значение совпадет:
Рис. 1. Функция ИЛИ возвращает ИСТИНА при совпадении хотя бы одного элемента диапазона с тестом
Скачать заметку в формате Word или pdf, примеры в формате Excel
Я воспользовался новым функционалом Excel 365 – динамическими массивами, и в ячейке А1 написал формулу =СЛМАССИВ(5;5;1;99;ИСТИНА). Эта формула создает массив случайных целых чисел в диапазоне от 1 до 99 размером 5*5 ячеек.
Формула в ячейке Н3 сравнивает массив, разлитый в А1 со значением в Н1. При наличии хотя бы одного совпадения формула =ИЛИ(A1#=H1) возвращает значение ИСТИНА. При отсутствии совпадений – возвращает ЛОЖЬ. Для наглядности я подсветил ячейку совпадения с помощью условного форматирования. Решетка в элементе А1# подразумевает ссылку не на одну ячейку А1, а на весь динамический массив, разлитый из ячейки А1. Подробнее см. Функция СЛМАССИВ динамических массивов в Excel.
Функция СЛМАССИВ волатильна, т.е. изменяет значения при каждом новом действии в Excel. Поэтому, если вы откроете файл примеров, то увидите иные числа, чем на рис. 1. При этом совпадения могут отсутствовать, и подкрашенных ячеек не будет. Чтобы изменить набор чисел и найти совпадения понажимайте F9. Эта кнопка заставляет Excel пересчитывать все формулы, а для волатильных функций – генерить новые значения.
Если у вас для сравнения используется обычный диапазон (не динамический массив), просто укажите его в качестве элемента сравнения. Например, =ИЛИ(A1:Е5=H1).
Условное форматирование
Выделите диапазон А1:Е5, пройдите по меню Главная –> Условное форматирование –> Создать правило –> Использовать формулу для определения форматируемых ячеек. В открывшемся окне введите формулу, нажмите кнопку Формат, и выберите стиль выделения ячейки или текста.
Рис. 2. Выделение совпадающих значений условным форматированием; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Функция СОВПАД
После введения в Excel функций динамических массивов многие старые функции «научились» поддерживать динамические свойства. Выше мы увидели, как это делает функция ИЛИ, принимая массив в качестве аргумента, и возвращая одно значение. Функция СОВПАД() способна принимать массив в качестве аргумента и возвращать массив того же размера. В ячейке G6 я разместил формулу =СОВПАД(A1#;H1), которая при нажатии Enter разлилась в массив 5*5, состоящий из значений ИСТИНА и ЛОЖЬ, в зависимости от того совпадает или нет соответствующее значение массива А1:Е5 с ячейкой Н1:
Рис. 3. Функция СОВПАД возвращает массив исходного размера, отмечая совпадения значением ИСТИНА
Число совпадений
Чтобы исследовать число совпадений в ячейке А1 я разместил формулу =СЛМАССИВ(5;5;1;9;ИСТИНА). Случайные числа выбираются из меньшего диапазона 1…9, что дает больше совпадений. В ячейке Н3 формула =СУММ(- -(A1#=H1)) возвращает число совпадений.
Рис. 4. Определение числа совпадений
Чтобы понять, как работает формула, проследим за ходом вычислений. Встаньте на ячейку Н3 и пройдите по меню Формулы –> Зависимости формул –> Вычислить формулу. Откроется окно Вычисление формулы. Нажмите Вычислить.
Рис. 5. Вычисление формулы
Повторно нажимайте кнопку Вычислить, пока не появится финальный результат. Вот как выглядят шаги вычисления:
Рис. 6. Шаги вычисления
На шаге 1 массив A1# представляется его числовыми значениями. На шаге 2, этот массив поэлементно сравнивается со значением в ячейке Н1 равном 8. На шаге 3 в результате сравнения возвращается массив значений ИСТИНА и ЛОЖЬ. К сожалению Excel далее не показывает промежуточные вычисления. Использование двух подряд знаков минус превращает логические значения в числовые: ЛОЖЬ –> 0, ИСТИНА –> 1. Массив значений ЛОЖЬ и ИСТИНА, полученный на шаге 3, превращается в числовой массив {0;0;1;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0}. Сумма значений этого массива равна 3.
Адреса совпадающих ячеек
Для возврата адресов совпадающих ячеек можно воспользоваться формулой: =ЕСЛИОШИБКА(АДРЕС(ЕСЛИ(A1#=H1;СТРОКА(A1#));СТОЛБЕЦ(A1#);4);"")
Эта формула по полной использует свойства динамических диапазонов)) Сначала значения элементов массива А1# сравниваются со значением в ячейке Н1. Далее создается массив адресов по размеру равный исходному массиву А1#. Для совпадающих ячеек выводятся их адреса, для несовпадающих – ошибка #ЗНАЧ! И, наконец, функция ЕСЛИОШИБКА() возвращает адрес, где он есть или пустое значение "" – для ошибок. Третий аргумент функции АДРЕС позволяет выбрать тип ссылки. Я задал значение 4, что соответствует относительному типу ссылок.
Рис. 7. Вывод массива адресов для совпадающих ячеек