Функция индекс в программе microsoft excel

4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).

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

Sub Poisk()

‘ ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Искомое слово» ‘присвоить переменной искомое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.

Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:

После этого Вы можете использовать обычную функцию ВПР, чтобы найти нужный заказ. Например:

• Находим 2-й товар, заказанный покупателем Dan Brown:

=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE) =ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)

Находим 3-й товар, заказанный покупателем Dan Brown:

=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE) =ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)

На самом деле, Вы можете ввести ссылку на ячейку в качестве искомого значения вместо текста, как представлено на следующем рисунке:

Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:

$F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);

$B$ – столбец Customer Name;

Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);

$C16 – конечная ячейка Вашей таблицы или диапазона.

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

Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в Excel есть функция INDEX (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

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

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel – ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Надеюсь, что данный урок Вам пригодился. Оставайтесь с нами и успехов в изучении Excel.

Поиск значений по вертикали в списке неизвестного размера с точным соответствием

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

Match («апельсины»; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales.

Функция ИНДЕКС() в MS EXCEL

​, т.е. пересчитывается каждый​ИНДЕКС​ объем продаж. В​Функция ИНДЕКС() часто используется​ ИНДЕКС() возвращает значение​02.05.12​Счет​​ строки 3, находящейся​​ которое меньше или​ с функциями подстановки,​ функции поиска.​

Синтаксис функции

​=MATCH(40,B2:B10,-1)​​ работает неправильно данное.​

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

​) и столбца (нам​​ раз при изменении​и двух функций​ файле примера, выбранные​ в связке с​ ошибки #ССЫЛКА! Например,​3474​

​Город​​ в том же​ равняется 1 и​ прежде чем применять​Функции ВПР и ГПР​Аргумент​Если функция​

​ формула =ИНДЕКС(A2:A13;22) вернет​Челябинск​Дата выставления счета​ столбце (столбец B).​ составляет 0,946, а​ их к собственным​ вместе с функций​тип_сопоставления​ПОИСКПОЗ​ а не функцию​​приблизительное совпадение​​ ИНДЕКС и ПОИСКПОЗ​

Значение из заданной строки диапазона

​ второй столбец).​​ИНДЕКС​

​можно легко реализовать​ выделены цветом с​ возвращает позицию (строку)​ ошибку, т.к. в​04.05.12​

​Самая ранняя счет по​7​ затем возвращает значение​​ данным, то некоторые​​ индекс и ПОИСКПОЗ,описаны​в синтаксисе присваивается​не находит искомое​ ВПР.​

Значение из заданной строки и столбца таблицы

​, а большинство людей​ с несколькими условиями​​Функция ИНДЕКС предназначена для​

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

Использование функции в формулах массива

​=ГПР(«П»;A1:C4;3;ИСТИНА)​ из столбца C​ образцы данных. Некоторые​ некоторые из наиболее​ значение -1, это​ значение в массиве​В данном примере представлен​ приблизительное совпадение не​ Excel.​ выборки значений из​ тонко и запускает​Если таблица не одна,​Бывает у вас такое:​ Это позволяет создать​

​А2:А13​​Челябинск​​3115​Поиск буквы «П» в​ в той же​​ пользователи Excel, такие​​ полезных функций в​ означает, что порядок​​ подстановки, возвращается ошибка​​ небольшой список, в​ устраивает.​Внимание! Для функции ИНДЕКС​​ таблиц Excel по​​ пересчет только при​

​ а их несколько,​ смотришь на человека​ формулу, аналогичную функции​​только 12 строк.​​05.05.12​Казань​ строке 1 и​

​ строке.​ как с помощью​ Microsoft Excel.​ значений в B2:​​ # н/д.​​ котором искомое значение​Чтобы убедиться в том,​ номера строк и​​ их координатам. Ее​​ изменении своих аргументов,​ то функция​

​ и думаешь «что​ ВПР().​Пусть имеется одностолбцовый диапазон​

​3503​07.04.12​

ПОИСКПОЗ() + ИНДЕКС()

​ возврат значения из​100​ функции ВПР и​Примечание:​ B10 должен быть​Если вы считаете, что​ (Воронеж) не находится​ что использование приблизительного​

​ столбцов определяют высоту​ особенно удобно использовать​ что ощутимо ускоряет​ИНДЕКС​ за @#$%)(*?» А​​Формула =ВПР(«яблоки»;A35:B38;2;0) аналогична формуле​

​А6:А9.​Челябинск​=»Казань = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),1)& «,​ строки 3, находящейся​=ВПР(0,7,A2:C10,3,ЛОЖЬ)​ ГПР; другие пользователи​ Функция мастер подстановок больше​ в порядке убывания​ данные не содержится​ в крайнем левом​ совпадения может иметь​ и ширину таблицы,​ при работе с​ расчет в тяжелых​может извлечь данные​

Ссылочная форма

​ потом при близком​ =ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0)) которая извлекает​Выведем значение из 2-й​08.05.12​

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

​ базами данных. Данная​

​ книгах по сравнению​ из нужной строки​ знакомстве оказывается, что​ цену товара Яблоки​ строки диапазона, т.е.​3151​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),3),»m/d/yy»)​ столбце. Так как​

​ ищет в столбце​ функций индекс и​ Microsoft Excel.​ Но значения в​ таблице, но не​ не можем использовать​ что ищется цена​ первом аргументе. Они​ функция имеет несколько​

​ со​ и столбца именно​

​ он знает пять​

​ из таблицы, размещенную​ значение Груши. Это​

​Нижний Новгород​3137​ «П» найти не​

​ A значение 0,7.​ ПОИСКПОЗ вместе. Попробуйте​Вот пример того, как​ порядке возрастания, и,​ удалось найти его​

​ функцию ВПР. Для​ детали с идентификатором​ никак не связаны​ аналогов, такие как:​​СМЕЩ​​ заданной таблицы. В​ языков, прыгает с​ в диапазоне​ можно сделать с​09.04.12​Казань​ удалось, возвращается ближайшее​ Поскольку точного соответствия​ каждый из методов​

excel2.ru>

Справочная форма

Описание

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

Синтаксис

ИНДЕКС(ссылка; номер_строки; ; )

Ссылочная форма функции индекс имеет следующие аргументы:

справки Обязательный. Ссылка на один или несколько диапазонов ячеек.

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

Если каждая область в ссылке содержит только одну строку или один столбец, аргумент “номер_строки” или “номер_столбца” соответственно является обязательным. Например, для ссылки на единственную строку нужно использовать формулу ИНДЕКС(ссылка,,номер_столбца).

Номер_строки — обязательный аргумент. Номер строки в диапазоне, заданном аргументом “ссылка”, из которого требуется возвратить ссылку.

Номер_столбца — необязательный аргумент. Номер столбца в диапазоне, заданном аргументом “ссылка”, из которого требуется возвратить ссылку.

номер_области — необязательный аргумент. Выделяет диапазон в ссылке, из которого нужно вернуть пересечение номер_строки и номер_столбца. Первая выделенная или введенная область имеет номер 1, второй — 2 и т. д. Если аргумент номер_области опущен, то функция индекс использует область 1. Все перечисленные здесь области должны располагаться на одном листе. Если вы указали области, которые не находятся на одном листе, это вызовет #VALUE! ошибка #ЧИСЛО!. Если необходимо использовать диапазоны, расположенные на разных листах, рекомендуется использовать форму массива функции индекс и использовать другую функцию для вычисления диапазона, который составляет массив. Например, чтобы вычислить диапазон, который будет использоваться, можно использовать функцию выбор.

Например, если в ссылке описаны ячейки (a1: B4, D1: E4, G1: H4), то функция номер_области 1 — это диапазон a1: B4, номер_области 2 — диапазон D1: E4, а номер_области 3 — диапазон G1: H4.

Замечания

После того, как ссылка и номер_области выделены определенный диапазон, выбери ячейку и номер_столбца, выделив одну из них: номер_строки 1 — это первая строка диапазона, номер_столбца 1 — первый столбец, и т. д. Ссылка, возвращаемая функцией индекс, — это пересечение с номер_строки и номер_столбца.

Если для аргумента “номер_строки” или “номер_столбца” установлено значение 0 (ноль), функция индекс возвращает ссылку на весь столбец или строку соответственно.

номер_строки, номер_столбца и номер_области должны указывать на ячейку в ссылке. в противном случае функция индекс возвращает значение #REF! ошибка #ЧИСЛО!. Если аргумент “номер_строки” и “номер_столбца” опущен, функция индекс возвращает область в ссылке, указанную с помощью номер_области.

Результатом вычисления функции ИНДЕКС является ссылка, которая интерпретируется в качестве таковой другими функциями. В зависимости от формулы значение, возвращаемое функцией ИНДЕКС, может использоваться как ссылка или как значение. Например, формула ЯЧЕЙКА(“ширина”;ИНДЕКС(A1:B2;1;2)) эквивалентна формуле ЯЧЕЙКА(“ширина”;B1). Функция ЯЧЕЙКА использует значение, возвращаемое функцией ИНДЕКС, как ссылку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2;1;2), преобразует значение, возвращаемое функцией ИНДЕКС, в число в ячейке B1.

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

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

Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

Примечание: Надстройка “Мастер подстановок” прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

Если команда подстановка недоступна, необходимо загрузить мастер подстановок надстройка программу.

Загрузка программы-надстройки “Мастер подстановок”

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

Функция ПОИСКПОЗ

​ измените ширину столбцов,​​ равно значению аргумента​​Функция​​ и имеет более​ Затем одна таблица​​ результата при использовании​​ мощной альтернативы ВПР​ решить эту задачу.​ должна выполнятся в​установите флажок рядом​Продажи​​ функций ИНДЕКС и​​(ПОИСКПОЗ) отлично работает​(ПОИСКПОЗ) в виде​​Использовать вместе с​​ не можем использовать​​Чтобы убедиться в том,​​ чтобы видеть все​

Синтаксис

​искомое_значение​

​ПОИСКПОЗ​ или менее следующий​

• ​ умножается на другую.​​ совершенно разных функций.​ я уже подробно​Это самый очевидный и​​ массиве. Если все​​ с пунктом​.​ ПОИСКПОЗ.​ в сочетании с​ массива констант. Если​CHOOSE​ функцию ВПР. Для​​ что использование приблизительного​​ данные.​​.​выполняет поиск указанного​ вид:​ Выглядит это более​Итак, у нас есть​

• ​ описывал (с видео).​​ простой (хотя и​ сделано правильно в​Мастер подстановок​

• ​К началу страницы​​Дополнительные сведения см. в​ функцией​ в ячейке D5​​(ВЫБОР), чтобы перевести​​ поиска значения «Воронеж»​ совпадения может иметь​​Продукт​​Просматриваемый_массив​ элемента в диапазоне​​1*НЕ(ЕОШИБКА(ПОИСКПОЗ(F5;A2:A9;0)))​​ или менее так,​ таблица, и мы​ В нашем же​​ не самый удобный)​ строке формул появятся​и нажмите кнопку​Для выполнения этой задачи​ разделе, посвященном функции​​INDEX​​ ввести более поздний​

​ успеваемость учащихся в​	​ в диапазоне B1:B11​
​ серьезные последствия, предположим,​	​Количество​​должен быть упорядочен​​ ячеек и возвращает​Изменяются только выделенные фрагменты.​ как показано на​​ бы хотели, чтобы​​ случае, можно применить​​ способ. Поскольку штатная​​ фигурные скобки.​ОК​ используется функция ГПР.​ ВПР.​(ИНДЕКС), которую мы​
​ месяц, например,​	​ буквенную систему оценок.​​ будет использоваться функция​​ что ищется цена​Бананы​​ по возрастанию: …,​​ относительную позицию этого​​ Для каждого столбца​​ рисунке ниже (пример​ формула Excel отвечала​
​ их для поиска​	​ функция​​Как видно функция самостоятельно​​.​ См. пример ниже.​Что означает:​​ рассмотрим более пристально​​Oct​​Использовать вместе с​​ ПОИСКПОЗ. Оно найдено​ детали с идентификатором​25​ -2, -1, 0,​ элемента в диапазоне.​ с помощью функции​

• ​ для искомого выражения​​ на вопрос, в​​ по нескольким столбцам​ВПР (VLOOKUP)​ справилась с решением​​Следуйте инструкциям мастера.​​Функция ГПР выполняет поиск​​=ИНДЕКС(нужно вернуть значение из​​ чуть позже в​(октябрь), то результатом​VLOOKUP​

• ​ в строке 4.​​ 2345768, но вы​​Апельсины​ 1, 2, …,​

• ​ Например, если диапазон​​ ПОИСКПОЗ, мы проверяем​​ «Прогулка в парке»).​ каком столбце (или​ в виде формулы​

• ​умеет искать только​​ поставленной задачи.​​К началу страницы​​ по столбцу​​ C2:C10, которое будет​​ рамках данного марафона.​​ функции будет​(ВПР) для гибкого​​ Затем функция ИНДЕКС​​ перепутали две цифры​38​​ A-Z, ЛОЖЬ, ИСТИНА.​​ A1:A3 содержит значения​ удалось ли найти​В результате мы получаем​ строке, однако в​ массива. Для этого:​ по одному столбцу,​​Во многих поисковых формулах​Продажи​​ соответствовать ПОИСКПОЗ(первое значение​​ В этом примере​

Пример

​#N/A​ выбора столбца.​ использует это значение​ и ввели их​Яблоки​0​ 5, 25 и​ в столбце искомое​ массив с нулями​ нашем примере я​Выделите пустую зеленую ячейку,​ а не по​Чтобы функция ПОИСКПОЗ работала​

​ очень часто приходится​	​и возвращает значение​
​ «Капуста» в массиве​	​ функция​
​(#Н/Д).​	​Использовать вместе с​
​ в качестве аргумента​	​ в формулу следующим​
​40​	​Функция​
​ 38, то формула​	​ выражение. Если нет,​	​ везде, где значения​
​ использую столбец), находится​	​ где должен быть​ нескольким, то нам​ с таблицей с​ использовать функцию ПОИСКПОЗ​ из строки 5 в​	​ B2:B10))​
​MATCH​	​=MATCH(D5,{«Jan»,»Feb»,»Mar»},0)​INDEX​	​ поиска и находит​
​ образом:​	​Груши​ПОИСКПОЗ​=ПОИСКПОЗ(25;A1:A3;0)​ то функция возвращает​	​ в нашей таблице​

support.office.com>

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Совет: Попробуйте использовать новые функции кслукуп и ксматч с улучшенными версиями функций, описанными в этой статье. Эти новые функции работают в любом направлении и возвращают точное совпадение по умолчанию, упрощая и удобнее в использовании, чем их предшественники.

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

Функции ВПР и ГПР вместе с функцией индекс и ПОИСКПОЗявляются наиболее полезными функциями в Excel.

Примечание: Функция «Мастер подстановок» больше не доступна в Excel.

Ниже приведен пример использования функции ВПР.

В этом примере ячейка B2 является первым аргументом— элементом данных, для работы которого требуется функция. Для функции ВПР первым аргументом является значение, которое нужно найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «Иванов» или 21 000. Второй аргумент — это диапазон ячеек (C2-: E7, в котором нужно найти искомое значение. Третьим аргументом является столбец в диапазоне ячеек, который содержит искомое значение.

Четвертый аргумент необязателен. Введите значение истина или ложь. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение ложь, функция будет соответствовать значению, представленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым — или ввести значение истина, вы получаете более гибкие возможности.

В этом примере показано, как работает функция. Когда вы вводите значение в ячейке B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приближенное соответствие из третьего столбца в диапазоне, столбец E (Третий аргумент).

Четвертый аргумент пуст, поэтому функция возвращает приближенное соответствие. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.

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

Использование функций индекс и MATCH вместо функции ВПР

Существуют определенные ограничения с использованием функции ВПР — функция ВПР может искать значение слева направо. Это означает, что столбец с искомым значением всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Если электронная таблица не создана таким образом, не используйте функцию ВПР. Вместо этого используйте сочетание функций индекс и ПОИСКПОЗ.

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования функции индекс и MATCH вместо функции ВПР можно найти в статье https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Джилена, Microsoft MVP.

Попробуйте попрактиковаться

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

Пример функции ВПР на работе

Скопируйте указанные ниже данные в пустой лист.

Совет: Перед вставкой данных в Excel задайте ширину столбцов для столбцов A — 250 пикселей и нажмите кнопку Перенос текста (вкладка Главная , группа Выравнивание ).

Примеры использования

Теперь рассмотрим несколько примеров использования ПОИСКПОЗ. Ниже представлены основные сценарии, когда может пригодиться команда.

Поиск по одному критерию

Начнем с простого варианта – это поиск с одним искомым значением. Работает по строкам и по столбцам. Также можно использовать массив в виде таблицы. Для нахождения позиции значения делаем следующее:

1. Попробуем найти позицию значения 4. Для этого вписываем команду ПОИСКПОЗ и в скобках указываем адрес ячейки с цифрой. Вы можете вписать само значение, но тогда придется менять формулу каждый раз. А если указать ссылку, то пользователь сможет автоматизировать процесс.

1. Ставим точку с запятой и выделяем весь массив. Он может состоять как из одного столбца, как в примере, так и из полноценной таблицы.

1. Теперь указываем тип сопоставления. Если хотим найти точное совпадение, вписываем 0.

1. Жмем клавишу Enter и видим готовый результат.

1. Если поменять значение в ячейке, ссылка на которую указана в начале формулы, то итоговое значение сменится.

Подобным образом функция работает и для текстовых значений. Видим таблицу с месяцами года. Допустим, хотим узнать расположение августа относительно данного массива. Так же указываем ссылку на ячейку с названием месяца, затем выделяем массив и добавляем тип сопоставления. Если речь идет о текстовых значениях, то необходимо ставить 0.

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

Поиск по двум критериям

Этот пример включает в себя указание нескольких условий. Применяется для массивов с несколькими значениями. Для примера рассмотрим простую таблицу с автомобилями и годами выпуска. Мы видим, что несколько марок авто повторяются, но даты напротив них разные. Попробуем найти позицию автомобиля с конкретной датой выпуска.

Для последующей автоматизации поиска можно создать простую таблицу. В дальнейшем в ней можно менять только название машины и год выпуска.

Поскольку поиск будет осуществляться по двум признакам, придется использовать правила при работе с массивами. Начинаем вводить формулы с привычного ПОИСКПОЗ и указываем ссылку на ячейку с названием марки. После этого ставим знак & и указываем ссылку на ячейку с нужной датой выпуска.

Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.

Для вычисления позиции необходимо воспользоваться комбинацией Ctrl+Shift+Enter. Так вы запустите выполнение функции в массиве.