Здравствуйте, друзья. Одна из распространенных ситуаций – когда есть два списка, и нужно найти их пересечения. Сегодня я расскажу, как найти первое совпадение по одному списку в другом.
Пример. Есть таблица «События», где описано, что происходило в течение дня. Одна ячейка – одно событие, в порядке следования, с утра до вечера (см. изображение выше).
Вторая таблица – «Отслеживаемые события». Там всего два пункта. Мы хотим определить, какое из отслеживаемых событий наступило раньше, руководствуясь последовательностью из первой таблички. Иными словами, первое пересечение.
В этой статье применяется формула массива. Когда вводите её в ячейку, нажимайте Ctrl+Shift+Enter вместо Enter. Фигурные скобки писать не нужно, они появятся автоматически!
Конструируем формулу массива по шагам:
- С помощью функции СЧЁТЕСЛИ посчитаем, сколько раз каждое событие встречается в таблице отслеживаемых данных:
{=СЧЁТЕСЛИ(D3:D4;B3:B18)}
Формула вернёт виртуальный массив с количеством совпадений для каждой строчки списка. Для наглядности, выведу его на экран:
- Добавим в эту формулу условие, чтобы преобразовать нули и единицы в ИСТИНУ и ЛОЖЬ:
{=СЧЁТЕСЛИ(D3:D4;B3:B18)>0}
Первое сверху значение ИСТИНА – это и будет то событие, которое мы ищем.
- Осталось найти это первый элемент ИСТИНА с помощью функции ПОИСКПОЗ:
{=ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(D3:D4;B3:B18)>0;0)}
Получили индекс найденного элемента в списке.
- Выведем найденный элемент с помощью функции ИНДЕКС:
=ИНДЕКС(B3:B18;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(D3:D4;B3:B18)>0;0))
Мы получили ожидаемый результат. Можно еще предусмотреть случай, когда совпадений вообще нет. В этом случае формула вернет ошибку #Н/Д. Тогда обернем нашу формулу в функцию обработки ошибок:
=ЕСНД(ИНДЕКС(B3:B18;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(D3:D4;B3:B18)>0;0));»Совпадений нет»)
Можете сделать как у меня в примере: с помощью условного форматирования: текст становится красным, если внутри ячейки строка «Совпадений нет».
Надеюсь, вы разобрались, как это работает. Если нет – задавайте вопросы в комментариях!
Скачать пример