Если вы какое-то время читали советы по Excel, вы неизменно находили кого-то, кто говорит об использовании функций Excel ИНДЕКС () и ПОИСКПОЗ () вместо Excel ВПР. Что касается меня, то всегда было слишком сложно попробовать освоить ДВЕ новые функции одновременно. Но это крутой трюк. Дайте мне пять минут, и я постараюсь объяснить это простым английским языком.
30-секундный обзор VLOOKUP
Скажем, у вас есть таблица записей сотрудников. Первый столбец - это номер сотрудника, а остальные столбцы - различные данные о сотруднике. Каждый раз, когда у вас есть номер сотрудника на листе, вы можете использовать ВПР, чтобы получить конкретную информацию о сотруднике. Синтаксис: ВПР (значение, диапазон данных, номер столбца, ЛОЖЬ). Он говорит Excel: «Перейдите к диапазону данных. Найдите строку, которая имеет (значение) в первом столбце диапазона данных. Верните (номер столбца) -ое значение из этой строки. Как только вы его освоите, это очень просто и мощно.
Эта проблема
Однажды у вас возникнет ситуация, когда у вас есть имя сотрудника, но вам нужен номер сотрудника. На следующем изображении у вас есть имя в A10, и вам нужно найти номер сотрудника в B10.
Когда ключевое поле находится справа от данных, которые вы хотите получить, функция ВПР не работает. Если бы только функция ВПР приняла в качестве номера столбца -1, проблем не было бы. Но это не так. Одно из распространенных решений - временно вставить новый столбец A, скопировать столбец с именами в новый столбец A, заполнить VLOOKUP, вставить специальные значения, а затем удалить временный столбец A. Профессионалы Excel, вероятно, могут сделать это во сне.
Я предлагаю вам принять вызов и попробовать использовать этот пошаговый метод. Да, вам придется наклеить формулу на стену в течение нескольких недель, но вы уже давно сделали это с помощью VLOOKUP, не так ли?
Я думаю, что причина такой сложности в том, что вы используете две функции, которые, вероятно, никогда раньше не использовали. Итак, позвольте мне разбить его на две части.
Во-первых, есть функция ИНДЕКС (). Это функция с ужасным названием. Когда кто-то говорит «index», в моем уме не возникает ничего похожего на то, что делает эта функция. Индекс требует трех аргументов.
=INDEX(data range, row number, column number)
На английском языке Excel переходит к диапазону данных и возвращает значение на пересечении (номер строки) -й строки и (номер столбца) -го столбца. Эй, подумай об этом - это довольно просто, правда? =INDEX($A$2:$C$6,4,2)даст вам значение в B5.
Применяя INDEX () к нашей проблеме, вы можете понять , что вернуть номер сотрудника из диапазона, вы бы использовать это: =INDEX($A$2:$A$6,?,1). На самом деле эта часть кажется настолько тривиальной, что кажется бесполезной. Но когда вы заменяете вопросительный знак функцией ПОИСКПОЗ (), у вас есть решение.
Вот синтаксис:
=MATCH(Value, Single-column data range, FALSE)
Он сообщает Excel: «Найдите диапазон данных и сообщите мне относительный номер строки, в которой вы найдете совпадение для (данных). Итак, чтобы найти, в какой строке находится сотрудник в A10, вы должны использовать =MATCH(A10,$B$2:$B$6,FALSE). Да, это сложнее, чем Индекс , но это должно быть прямо на пути профессионалов ВПР. Если A10 содержит "Miller, Bob", то это ПОИСКПОЗ вернет, что он находится в 3-й строке диапазона B2: B6.
Вот и все - функция ПОИСКПОЗ () сообщает функции индекса, в какой строке искать - все готово. Возьмите функцию индекса, замените наш вопросительный знак функцией ПОИСКПОЗ, и теперь вы можете выполнять эквивалент ВПР, когда ключевое поле не находится в левом столбце. Вот функция, которую нужно использовать:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Клейкая записка на моей стене показывает это в виде двух линий. Сначала я написал объяснение для MATCH (). Ниже я написал объяснение для INDEX (). Затем я нарисовал между ними форму воронки, чтобы указать, что функция ПОИСКПОЗ () переходит ко второму аргументу функции ИНДЕКС ().
Первые несколько раз, когда мне приходилось делать одно из этих действий, я испытывал искушение просто вставить туда новую временную колонку A, но вместо этого прошел через боль, сделав это таким образом. Это быстрее и требует меньше манипуляций. Итак, в следующий раз, когда вы захотите ввести отрицательное число в функцию ВПР, попробуйте эту странную комбинацию ИНДЕКС и ПОИСКПОЗ, чтобы решить ваши проблемы.
