Формула индекс и поискпоз с несколькими условиями поиска в excel

Функция ПОИСКПОЗ в Excel – синтаксис и использование

Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.

Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:

Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ, основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:

=ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

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

• Функция ПОИСКПОЗ возвращает относительное положение значения поиска в массиве, а не самого значения.
• ПОИСКПОЗ не зависит от регистра , что означает, что она не различает строчные и прописные символы при работе с текстовыми значениями.
• Если массив поиска содержит несколько вхождений искомого значения, возвращается позиция первого значения.
• Если искомое значение не найдено в просматриваемом массиве, возвращается ошибка #Н/Д.

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )

• Что_ищем — это значение, которое надо найти
• Где_ищем — это одномерный диапазон или массив (строка или столбец), где производится поиск
• Режим_поиска — как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий — поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

Поиск первой или последней текстовой ячейки

Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:

Числа и пустые ячейки в этом случае игнорируются.

Поиск ближайшего числа или даты

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

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

Связка функций ПОИСКПОЗ и ИНДЕКС

Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией — ИНДЕКС (INDEX) , которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, «левый ВПР».

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:

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

Пример формулы с ВПР и ПОИСКПОЗ

Пример таблицы табель премии изображен ниже на рисунке:

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

Например, нам нужно чтобы программа автоматически определила какая возможная минимальная премия для продавца из 3-тего магазина, выручка которого преодолела уровень в 370 000.

Для этого:

1. В ячейку B14 введите размер выручки: 370 000.
2. В ячейке B15 укажите номер магазина: 3.
3. В ячейке B16 введите следующую формулу:

В результате определена нижняя граница премии для магазина №3 при выручке больше >370 000, но меньше <400 000.

Поиск ближайшего значения Excel формулой ВПР и ПОИСКПОЗ:

В первом аргументе функции ВПР указываем ссылку на ячейку с критерием поискового запроса (исходная сумма выручки), который содержится в ячейке B14. Область поиска в просматриваемом диапазоне A5:K11 указывается во втором аргументе функции ВПР. А в третьем аргументе должен быть указан номер столбца, но он пока неизвестен. Из второго критерия поискового запроса известно только что исходный номер столбца таблицы относится к 3-тьему магазину (ячейка B15).

Чтобы определить номер столбца, который содержит заголовок «Магазин 3» следует использовать функцию ПОИСКПОЗ. Как само название функции говорит о том, что ее задачей является поиск позиции где находится значение внутри определенного диапазона ячеек. В нашем случаи мы ищем значение: «Магазин 3», которое следует еще определить используя конструкцию сложения амперсандом текстовой строки «Магазин » и критерий из ячейки B15. Поэтому в первому аргументе функции указываем «Магазин »&B15. Во втором аргументе функции ПОИСКПОЗ указывается ссылка на просматриваемый диапазон A3:J3 где нужно искать исходное значение (указанное в первом аргументе). Третий аргумент содержит значение 0 – это значит, что функция возвратит результат, как только найдет первое совпадение значений. В нашем примере значение «Магазин 3» находится на позиции номер 6 в диапазоне A3:J3, а значит функция ПОИСКПОЗ возвращает число 6 которое будет использовано в качестве значения для третьего критерия функции ВПР. Есть еще и четвертый аргумент в функции ВПР который определяет точность совпадения найденного значения с критерием (0-точное совпадение; 1 или пусто – приближенное совпадение), но в формуле он опущен по следующей причине. Получив все аргументы функция ВПР не находит значения 370 000 и так как не указан последний аргумент выполняет поиск ближайшего значения в Excel – 350 000.

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

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

Полезные советы для формул с функциями ВПР, ИНДЕКС и ПОИСКПОЗ:

Чтобы пошагово проанализировать формулу Excel любой сложности, рационально воспользоваться встроенными инструментами в разделе: «ФОРМУЛЫ»-«Зависимости формул». Например, особенно полезный инструмент для пошагового анализа вычислительного цикла – это «Вычислить формулу».

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

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

Эти функции могут быть доступны не на всех языках.

Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

Искомый_текст Обязательный. Текст, который требуется найти.

Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.

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

Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Если значение аргумента искомый_текст не найдено, #VALUE! возвращено значение ошибки.

Если аргумент начальная_позиция опущен, то он полагается равным 1.

Если Нач_позиция не больше 0 или больше, чем длина аргумента просматриваемый_текст , #VALUE! возвращено значение ошибки.

Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

​ в массиве записать​​ с небольшим числом​​яблоки​ получили оценку «неудовлетворительно»,​ выглядит следующим образом:​. Для наглядности вычислим,​ в этом векторе.​ столбце диапазона B1:I1.​ значение FordМаркетинговый. По​ с одним столбцом​В ячейке B12 введите​​ нажмите клавишу F2,​​ 1, 2, …,​ПОИСКПОЗ​ третьего столбца в​

​ ячейку, должен быть​​5​​ соответствующие функции «ПОИСКПОЗ»,​​ элементов. Например, у​перец​​ то в соответствующую​​ ИНДЕКС (массив, №​ что же возвращает​ При этом третий​Из приведенных примеров видно,​ этой причине первый​​ или с одной​​ номер необходимого отдела,​ а затем — клавишу​​ A-Z, ЛОЖЬ, ИСТИНА.​​следует пользоваться вместо​​ диапазоне — столбца​​ крайним левым столбцом​

Синтаксис

​3​

​ выдающие эти номера.​ вас есть отчет​

​5​​ ячейку следует ввести​ строки, № столбца).​ нам данная формула:​​ аргумент указывать необязательно.​​ что первым аргументом​ Ford из отдела​ строкой. Поэтому сразу​ который потом выступит​ ВВОД

При необходимости​0​ одной из функций​​ E (третий аргумент).​​ в диапазоне.​​1​ Обратите внимание, что​ об успеваемости нескольких​перцы​ выражение: ИНДЕКС (С2:С5,​

​Данная функция может работать​​Третьим аргументом функции​Например, следующая формула возвращает​ функции​

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

​ПОИСКПОЗ​	​, когда требуется найти​
​ аргумент оставлен пустым,​	​ столбец в диапазоне​​«уд»​​ «уд», расположенное в​ их оценки. Предположим,​имбирь​​A​​ строкой или с​​является номер столбца.​​ диапазона A1:A12 (вертикальный​является искомое значение.​ функции два форда​ проиллюстрируем как применять​ Например, 3.​
​ данные.​	​находит первое значение,​​ позицию элемента в​​ поэтому функция возвращает​ поиска ячеек, содержащий​​10​​ ячейке G2 и​​ вы хотите, чтобы​​6​B​
​ единственным столбцом. В​	​ Этот номер мы​​ вектор):​​ Вторым аргументом выступает​ – это разные​ функцию ПОИСКПОЗ для​​В ячейке B13 введите​​Продукт​​ равное аргументу​​ диапазоне, а не​ приблизительное совпадение.​ значение, которое нужно​12​ «гр. 2» из​ в ячейке H2​

• ​моркоь​​C​​ таком случае после​ получаем с помощью​Данная формула возвращает третье​​ диапазон, который содержит​​ значения (FordПродажи и​​ таблицы с двумя​​ номер статьи. Например,​Количество​искомое_значение​

• ​ сам элемент. Например,​​Разобравшись с функцией ВПР,​​ найти.​3​

• ​ H2. Кроме того,​​ появилось число студентов,​​бананы​D​ указания одномерного массива​

• ​ функции​​ значение из диапазона​​ искомое значение. Также​​ FordМаркетинговый). Просматриваемый диапазон​​ столбцами и более.​​ 7.​​Бананы​.​​ функцию​​ несложно будет освоить​Хотя четвертый аргумент не​​«уд»​​ нам нужны точные​ получивших оценку «неуд».​корица​E​ выставляется одно число.​ПОИСКПОЗ(C16;B1:E1;0)​ A1:L1(горизонтальный вектор):​ функция имеет еще​ теперь распространяется на​​Для примера возьмем список​​В ячейку B14 введите​

Пример

​25​Просматриваемый_массив​ПОИСКПОЗ​ и функцию ГПР.​ является обязательным, большинство​14​ совпадения, поэтому в​A​Диапазон значений в этом​1​ Оно обозначает номер​. Для наглядности вычислим​Если Вы уже работали​

​ и третий аргумент,​	​ 2 столбца, так​
​ автомобилей из автопарка​	​ следующую формулу:​
​Апельсины​	​может быть не​
​можно использовать для​	​ Функция ГПР использует​
​ пользователей вводят аргумент​	​10​
​ качестве последнего, третьего,​	​B​	​ случае В3:В6.​
​N/N​	​ строки, если массив​ и это значение:​ с функциями​ который задает тип​ же благодаря оператору​	​ средней фирмы, как​
​В результате получаем значение​	​38​ упорядочен.​	​ передачи значения аргумента​
​ те же аргументы,​	​ ЛОЖЬ (или 0).​14​ аргумента в обоих​C​	​Выбираем ячейку в другой​

support.office.com>

Excel MATCH функция

Microsoft Excel Функция ПОИСКПОЗ выполняет поиск определенного значения в диапазоне ячеек и возвращает относительное положение этого значения.

аргументы

Lookup_value (необходимые): Конкретное значение, которое вы хотите сопоставить в массиве look_up; Этот аргумент может быть числом, текстом, логическим значением или ссылкой ячейки на значение (число, текст или логическое значение).

Lookup_array (необходимые): Диапазон ячеек, содержащий искомое значение.

Match_type (необязательный): Тип соответствия, которое будет выполнять функция. Он состоит из 3-х видов:

• — Находит первое значение, точно соответствующее lookup_value
• 1 — или опущено. Если точное значение соответствия не может быть найдено, Match найдет наибольшее значение, которое меньше или равно значению look_up.Значения в аргументе массива look_up должны быть в порядке возрастания..
• -1 — Находит наименьшее значение, которое больше или равно значению look_up.Значения в аргументе массива look_up должны быть в порядке убывания..

Примечания к функциям

1. Функция ПОИСКПОЗ не чувствительна к регистру. 2. Функция Match вернет значение ошибки # N / A, если совпадение не будет найдено. 3. Функция ПОИСКПОЗ позволяет использовать символы подстановки в аргументе lookup_value для приблизительного совпадения.

Примеры

Пример 1: функция ПОИСКПОЗ для точного совпадения

Пожалуйста, сделайте следующее, чтобы вернуть позицию Чарльза Монагана в диапазоне B3: B7.

Выберите пустую ячейку, введите в нее формулу ниже и нажмите клавишу Enter, чтобы получить результат.=MATCH(D4,B3:B7,0)

Внимание: В формуле D4 содержит поисковое значение; B3: B7 это диапазон ячеек, содержащий искомое значение; количество означает, что вы ищете точное значение соответствия

Пример 2: функция ПОИСКПОЗ для приблизительного совпадения

В этом разделе рассказывается об использовании функции ПОИСКПОЗ для поиска приблизительного совпадения в Excel.

Как показано на скриншоте ниже, вы хотите найти позицию числа 2500 в диапазоне B3: B7, но в списке нет 2500, здесь я покажу вам, как вернуть позицию самого большого значения, которое меньше 2500 в списке.

Выберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите клавишу Enter, чтобы получить результат.=MATCH(D4,B3:B7,1)

Затем он вернет позицию числа 2000, которое является наибольшим значением, меньшим 2500 в списке.

Примечание. В этом случае все значения в списке B3: B7 должны быть отсортированы в порядке возрастания, в противном случае будет возвращена ошибка # N / A. Чтобы вернуть позицию наименьшего значения (говорит 3000), которое больше 2500 в списке, примените эту формулу:=MATCH(D4,B3:B7,-1)

Внимание: Все значения в списке B3: B7 должны быть отсортированы по убыванию в случае возврата ошибки # N / A

Пример 3: функция ПОИСКПОЗ для соответствия подстановочным знакам в функции ПОИСКПОЗ

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

Как показано на скриншоте ниже, для получения позиции значения, которое начинается с «привет», сделайте следующее.

Выберите пустую ячейку, введите в нее формулу ниже (вы можете заменить D4 непосредственно на «привет *») и нажмите клавишу Enter. Он вернет позицию первого значения совпадения.=MATCH(D4,B3:B7,0)

Чаевые: Функция ПОИСКПОЗ не чувствительна к регистру.

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

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

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

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

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

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

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

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения):

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10). Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; ):

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!