В этом видео мы используем MATCH, чтобы найти позицию элемента в таблице, и мы используем INDEX, чтобы получить значение в этой позиции. Мы работаем поэтапно, чтобы вы могли увидеть, как работают обе функции.
В этом видео мы рассмотрим, как настроить классический двусторонний поиск с помощью INDEX и Match.
Здесь у нас есть список продавцов с ежемесячными данными о продажах.
Что мы хотим сделать, так это добавить формулу в Q6, которая ищет и извлекает номер продаж на основе имени и месяца, указанных выше.
Для этого воспользуемся функциями ИНДЕКС и ПОИСКПОЗ.
Сначала я назову несколько диапазонов, чтобы формулы было легче читать. Я назову всю таблицу «данными», а затем буду использовать «имена» для списка продавцов. Обратите внимание, что я включаю первую пустую ячейку в оба имени. Это потому, что проще использовать одно и то же происхождение как для данных, так и для меток.
Наконец, я назову месяцы. Я снова включу первую ячейку. Теперь у нас есть 3 диапазона.
Затем давайте создадим формулу для проверки концепции, которая использует ИНДЕКС для получения значения на основе жестко заданных номеров строк и столбцов. Массив - это данные, и я буду использовать 2 для номера строки и столбца.
ИНДЕКС возвращает 11 882, что находится на пересечении второй строки и второго столбца.
Технически ИНДЕКС возвращает ссылку на ячейку C5, но это тема для другого дня.
Итак, теперь мы знаем, что INDEX выполнит эту работу, нам просто нужно выяснить, как использовать MATCH, чтобы получить правильные номера строк и столбцов.
Чтобы решить эту проблему, я собираюсь ввести формулы ПОИСКПОЗ отдельно, а затем соединить их вместе с ИНДЕКСОМ в конце. Сначала я введу имя и месяц, чтобы у нас было с чем сравнивать.
Чтобы сопоставить имя, нам нужен Q4 для значения сопоставления и «имена» для массива поиска. Тип соответствия равен нулю, потому что нам нужны только точные совпадения.
Чтобы соответствовать месяцу, нам нужен Q5 для значения соответствия и «месяцы» для массива поиска. Тип совпадения снова нулевой.
С Голубем и Яном мы получаем строку 8 и столбец 2. И если мы проверим таблицу, это правильно.
Подводя итог, мне просто нужно заменить жестко запрограммированные значения в формуле ИНДЕКС созданными нами функциями ПОИСКПОЗ. Самый простой способ сделать это - просто скопировать формулы и вставить их обратно в функцию ИНДЕКС в нужном месте.
Формула сопоставления имени соответствует номеру строки, а формула сопоставления месяца - столбцу.
Теперь формула завершена и будет искать правильный номер продаж, используя имя и месяц.
Это хороший подход, когда вы впервые разрабатываете более сложную формулу. Сначала создайте формулу проверки правильности концепции, а затем создайте необходимые вспомогательные формулы и убедитесь, что все работает правильно. Наконец, объедините вспомогательные функции с формулой доказательства концепции.
Курс
Основная формулаСвязанные ярлыки
Выбрать текущую область Ctrl + A ⌘ + A Расширить выделение до последней ячейки вниз Ctrl + Shift + ↓ ⌃ + ⇧ + ↓ Расширить выделение до последней ячейки вправо Ctrl + Shift + → ⌃ + ⇧ + → Перейти к верхнему краю области данных Ctrl + ↑ ⌃ + ↑ Копировать выбранные ячейки Ctrl + C ⌘ + C Вставить содержимое из буфера обмена Ctrl + V ⌘ + V
