Computer help - file server


[ Blog ] - [ File Server ] - [ Удаленная компьютерная помощь ]

Те из вас, кто хорошо разбирается в Excel, скорее всего, хорошо знакомы с функцией ВПР. Функция VLOOKUP используется для поиска значения в другой ячейке на основе некоторого совпадающего текста в той же строке.

Если вы все еще плохо знакомы с функцией ВПР, вы можете ознакомиться с моей предыдущей публикацией о том, как использовать ВПР в Excel.



Какой бы мощной ни была функция ВПР, она имеет ограничение на то, как должна быть структурирована соответствующая справочная таблица, чтобы формула работать.

Эта статья покажет вам ограничение, при котором нельзя использовать ВПР, и познакомит с другой функцией в Excel под названием ИНДЕКС-ПОИСКПОЗ, которая может решить эту проблему. INDEX MATCH Пример Excel

Используя следующий пример электронной таблицы Excel, у нас есть список имен владельцев и автомобилей. В этом примере мы попытаемся получить идентификатор автомобиля на основе модели автомобиля, указанной для нескольких владельцев, как показано ниже:



На отдельном листе под названием CarType у нас есть простая база данных автомобилей с идентификатором, моделью автомобиля и цветом.



При такой настройке таблицы функция ВПР может работать только в том случае, если данные, которые мы хотим получить, находятся в столбце для справа от того, что мы пытаемся сопоставить (поле Модель автомобиля).

Другими словами, с этой структурой таблицы, поскольку мы пытаемся сопоставить ее на основе модели автомобиля, единственная информация, которую мы можем получить, - это цвет (не идентификатор, поскольку столбец идентификатора расположен слева столбца "Модель автомобиля".)

Это связано с тем, что при использовании функции ВПР значение поиска должно отображаться в первом столбце, а столбцы поиска должны располагаться справа.В нашем примере ни одно из этих условий не выполняется.

Хорошая новость в том, что INDEX-MATCH может помочь нам в этом. На практике это фактически объединение двух функций Excel, которые могут работать индивидуально: функции ИНДЕКС и функции ПОИСКПОЗ.



Однако для целей этой статьи мы будем говорить только о комбинации этих двух с целью воспроизведения функция ВПР.



Поначалу формула может показаться немного длинной и пугающей.Однако, использовав его несколько раз, вы выучите синтаксис наизусть.

Это полная формула в нашем примере: = ИНДЕКС (CarType! $ A $ 2: $ A $ 5, MATCH (B4, CarType! $ B $ 2: $ B $ 5,0))

Вот разбивка по каждому разделу

= ИНДЕКС (- «=» обозначает начало формулы в ячейке, а ИНДЕКС - это первая часть функции Excel, которую мы используем.

CarType! $ A $ 2: $ A $ 5 - столбцы на листе CarType, в которых содержатся данные, которые мы хотели бы получить. В этом примере это идентификатор каждой модели автомобиля.

MATCH (- вторая часть функции Excel, которую мы используем.

B4 - ячейка, содержащая поисковый текст, который мы используем (модель автомобиля).

CarType! $ B $ 2: $ B $ 5 - столбцы на листе CarType с данными, которые мы будем использовать для сопоставления с поисковым текстом.

0)) - чтобы указать, что поисковый текст имеет для точного совпадения с текстом в соответствующем столбце (например, CarType! $ B $ 2: $ B $ 5). Если точное совпадение не найдено, формула возвращает # N/A.

Примечание: запомните двойную закрывающую скобку в конце этой функции «))» и запятые между аргументами.

Лично я отошел от ВПР и теперь использую ИНДЕКС-ПОИСКПОЗ, поскольку он может делать больше, чем ВПР.

Функции ИНДЕКС-ПОИСКПОЗ также имеют другие преимущества по сравнению с ВПР: более быстрые вычисления

Когда мы работаем с большими наборами данных, когда само вычисление может занять много времени из-за большого количества ВПР функций, вы обнаружите, что после замены всех этих формул на INDEX-MATCH общий расчет будет выполняться быстрее. Нет необходимости подсчитывать относительные столбцы

Если в нашей справочной таблице есть ключевой текст, который мы хотим искать в столбце C, а данные, которые нам нужно получить, находятся в столбце AQ, нам нужно знать/подсчитывать сколько столбцов находится между столбцом C и столбцом AQ при использовании ВПР.

С помощью функций ИНДЕКС-ПОИСКПОЗ мы можем напрямую выбрать столбец индекса (например, столбец AQ), в котором нам нужно получить данные, и выбрать столбец для сопоставления (например, столбец C). Это выглядит более сложным

ВПР довольно распространено в наши дни, но не многие знают об использовании функций ИНДЕКС-ПОИСКПОЗ вместе.

Более длинная строка в функции ИНДЕКС-ПОИСКПОЗ помогает вам выглядеть экспертом в работе со сложными и расширенными функциями Excel. Наслаждайтесь!

.
SETUP UA COMPUTER BLOG