← Все курсы · Курс Excel

Двусторонний поиск: поиск значения одновременно по строке и столбцу

Майкл Носито · Обновлено в августе 2026 г. · Все числа на этой странице были обработаны до их публикации.

Регионы внизу слева, месяцы вверху, цифры посередине. Кому-то нужно значение для Юга в марте, и они хотят, чтобы оно продолжало работать при добавлении региона.

Что вы делаете: INDEX с двумя MATCHэс. Это та версия, которая выдерживает изменения в сетке, и именно ее стоит сохранить в мышечной памяти.

Краткая версия. Одно ПОИСКПОЗ для строки, одно для столбца, ИНДЕКС возвращает место их пересечения.

Сетка

Допустим, метки находятся в формате A2:A5, месяцы — в формате B1:E1, а числа — в формате B2:E5. Юг и Мар должны вернуть 7600 человек.

Три способа написать это

ИНДЕКС с двумя МАТЧАМИ, тот, который нужно выучить

=INDEX($B$2:$E$5,
        MATCH($G$1, $A$2:$A$5, 0),
        MATCH($G$2, $B$1:$E$1, 0))

G1 содержит регион, G2 — месяц. Первый MATCH возвращает 2 для юга, второй возвращает 3 для марта и INDEX возвращает значение в строке 2 столбца 3 сетки, равное 7600. Оба нуля означают точное совпадение, и оба они существенны.

Вставьте регион или измените порядок месяцев, и формула по-прежнему будет работать, потому что в ней ничего о позиции не написано.

Вложенный XLOOKUP, если он у вас есть.

=XLOOKUP($G$1, $A$2:$A$5,
         XLOOKUP($G$2, $B$1:$E$1, $B$2:$E$5))

Внутренний поиск возвращает весь мартовский столбец в виде массива, а внешний выбирает из него строку Юга. Коротко читать, и он работает только в Майкрософт 365 и Excel 2021 и более поздних версиях. Видеть XLOOKUP недоступен. прежде чем поместить его в общий файл.

SUMIFS, когда данные длинные

=SUMIFS(Data[Amount], Data[Region], $G$1, Data[Month], $G$2)

Это та версия, которая вам нужна. Ему нужны данные в длинной форме, по одной строке на регион и месяц, и взамен он обрабатывает дубликаты, суммируя их, возвращает 0, а не ошибку для отсутствующей комбинации, и позволяет добавлять третье или четвертое условие без изменения формы формулы.

Ловушки

Последний постоянно кусает заголовки месяцев. Заголовок, набранный как Mar — это текст, а ячейка, содержащая реальную дату в формате Mar это число. Они выглядят одинаково и никогда не совпадают. Тест с =ISTEXT(B1).

Создание настоящего интерфейса

1. Поместите два ключа в помеченные ячейки вверху, а не внутри формулы.
2. Дайте обеим ячейкам список проверки данных, взятый из заголовков, чтобы опечатка стала невозможной. Видеть Проверка данных.
3. Добавьте сообщение о ненайденном: =IFERROR(the formula, "no such combination"), как только вы узнаете, почему это потерпит неудачу.
4. Раскрасьте две входные ячейки иначе, чем все остальное. Входные и выходные данные, выглядящие одинаково, - это то, как набирается сообщаемое число.

Как применить это в своей работе

1. Найдите сетки в своих рабочих тетрадях и спросите, форму каких из них вам разрешено изменить. Измените их форму.
2. В остальном замените все жестко закодированные номера столбцов на MATCHи обратите внимание, сколько формул иммунизируется против вставленного столбца.
3. Проверьте каждую строку заголовка на наличие объединенных ячеек и несоответствий текста и даты.
4. Переместите ключи поиска в помеченные ячейки ввода со списками проверки.
5. Если возможны дубликаты, используйте SUMIFS а не поиск, поэтому две совпадающие строки создают сумму вместо молчаливого первого совпадения.

Единственная привычка, которую нужно сохранить

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

Сколько ваших формул содержат номера столбцов, введенные вручную?