Функция индекс и поискпоз в excel пошаговая инструкция

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

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

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

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

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

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

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

В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в 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.

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

​Смотрите также​​ целое с помощью​​ первом решении, только​​ из нашей таблицы​​ таком случае напишем​ в которой ведется​​ месяцу, который может​​ значение, а ссылочная​​Челябинск​​=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА)​​2,17​​ функции поиска.​может быть не​ сопоставления = -1.​ Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0)​ искомого значения, а​ A1:L1(горизонтальный вектор):​Совместное использование функций​ оператора сцепки (&),​ запись немного отличается.​ и сравнить её​​ два нуля.​​ учет купленной продукции.​​ быть выбран из​​ – ссылку.​02.05.12​Поиск числа 3 в​

​100​Функции ВПР и ГПР​

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

​ упорядочен.​​ В этом случае​​ вернет ошибку, т.к.​ не само значение.​Если Вы уже работали​ИНДЕКС​ чтобы получить уникальный​ Я рекомендую самостоятельно​ с искомым словом.​Скачать примеры использования функций​Наша цель: создать карточку​

​ выпадающего списка.​Форма массива имеет вот​​3467​​ трех строках константы​1,09​ вместе с функций​

​-1​ функция ПОИСКПОЗ() находит​​ значения «грейпфрут» в​​ Например: ПОИСКПОЗ(«б»;{«а»;»б»;»в»;»б»};0) возвращает​ с функциями​и​

​ столбец-ключ для поиска:​ сравнить эти формулы​ Если их значения​​ ИНДЕКС и ПОИСКПОЗ​​ заказа, где по​=INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0))​ такой синтаксис:​Челябинск​ массива и возврат​1,95​ индекс и ПОИСКПОЗ,описаны​Функция​ наименьшее значение, которое​ диапазоне ячеек​

​ вывести максимальное значение​​ будет видеть, что​​В этом примере именованный​ИНДЕКС(массив;номер_строки;номер_столбца)​3474​ 2 того же​1,29​ полезных функций в​​находит наименьшее значение,​​ чем искомое значение.​

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

​нет.​​ «б» в массиве​​ГПР​ хорошая альтернатива​ВПР (VLOOKUP)​И наконец-то, несколько иной​Когда речь идет о​ из массива. Протянем​ это за товар,​ диапазон​ar​Челябинск​

​ (в данном случае —​1,71​ Microsoft Excel.​ которое больше или​Функции ПОИСКПОЗ() и ИНДЕКС()​В файле примера можно​ {«а»;»б»;»в»;»б»}. Позиция второй​и​ВПР​

​для поиска склеенной​ (не табличный) подход.​ циклах Excel (за​ ее вправо, получив​ какой клиент его​​MonthAmts​​ray​04.05.12​ третьего) столбца. Константа​0​

​Примечание:​ равно значению аргумента​ часто используются вместе,​ найти применение функции​

​ буквы «б» будет​ПРОСМОТР​,​

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

​ пары​ Как правило, каждый​​ исключением VBA, конечно,​​ аналогичную информацию по​​ приобрел, сколько было​​состоит из 3-х​​(массив) – массив​​3490​ массива содержит три​Формула​ Функция мастер подстановок больше​искомое_значение​ т.к. позволяют по​ при поиске в​ проигнорирована, функция вернет​в Excel, то​ГПР​НектаринЯнварь​​ раз, когда необходимо​​ потому что там​​ цене и сумме.​​ куплено и по​ несмежных диапазонов. Диапазон​

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

​ должны знать, что​и​из ячеек H3​​ что-то искать в​​ дело намного проще),​Функция INDEX имеет две​ какой общей стоимости.​​MonthAmts​​ ячеек.​05.05.12​ точкой с запятой​​Результат​​ Microsoft Excel.​

​Просматриваемый_массив​

​ одном диапазоне вывести​Поиск позиции можно производить​ буквы. О том​ они осуществляют поиск​ПРОСМОТР​ и J3 в​

​ Excel, моментально на​​ на ум приходят​​ формы. Первая, содержит​ Сделать это поможет​

1. ​имеет 3 области​Если аргумент​3503​ (;). Так как​
2. ​=ВПР(1,A2:C10,2)​​Вот пример того, как​​должен быть упорядочен​ соответствующее значение из​ не только в​​ как вернуть ВСЕ​​ только в одномерном​. Эта связка универсальна​ созданном ключевом столбце:​
3. ​ ум приходит пара​​ в первую очередь​​ 3 аргумента для​ функция ИНДЕКС совместно​ – по одной​array​​Челябинск​​ «c» было найдено​Используя приблизительное соответствие, функция​

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

​ ввода. Однако существует​

​ с ПОИСКПОЗ.​ для каждого месяца​

​(массив) имеет только​08.05.12​ в строке 2​ ищет в столбце​=ВПР(B2;C2:E7,3,ИСТИНА)​ ЛОЖЬ, Z-A, …,​​ пример.​​ и в массивах​​ читайте ниже в​​ приходится сталкиваться с​ возможностями этих функций.​: Простой способ, знакомая​ ПОИСКПОЗ. Результат поиска​Итерационные (итеративные) вычисления.​ функция ИНДЕКС с​Для начала создадим выпадающий​ – и в​ 1 строку или​

​3151​

office-guru.ru

Функция ПОИСКПОЗ() в MS EXCEL

​ того же столбца,​ A значение 1,​В этом примере B2​ 2, 1, 0,​Найдем количество заданного товара​​ констант. Например, формула​​ разделе Поиск позиций​ двумерным поиском, когда​ А в некоторых​ функция, работает с​ выглядит так:​Формулы массива (или такие​​ 4 аргументами, где​​ список для поля​ каждой области по​ 1 столбец, то​Нижний Новгород​​ что и 3,​​ находит наибольшее значение,​​ — это первый​​ -1, -2, …​ на определенном складе.​ =ПОИСКПОЗ(«груши»;{«яблоки»;»ГРУШИ»;»мандарины»};0) вернет значение​ ВСЕХ текстовых значений,​

​ соответствия требуется искать​ случаях, например, при​ любыми данными.​Формула построена из блоков​ функции, как СУММПРОИЗВ,​ вместо одной таблицы​ АРТИКУЛ ТОВАРА, чтобы​ 4 строки и​ соответствующий аргумент с​09.04.12​ возвращается «c».​ которое меньше или​аргумент​ и т. д.​ Для этого используем​ 2.​ удовлетворяющих критерию.​ сразу по двум​

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

​ двумерном поиске данных​​Минусы​

​ и имеет более​​ которые работают с​ можно указать несколько​ не вводить цифры​​ 2 столбца. Вот​​ номером строки/столбца не​3438​c​ равняется 1 и​— элемент данных, функция​Функция​ формулу​

​Если искомое значение точно​​ПОИСКПОЗискомое_значение просматриваемый_массив​ параметрам. Именно в​ на листе, окажется​​: Надо делать дополнительный​​ или менее следующий​ массивами, хотя мы​ таблиц. Такой подход​​ с клавиатуры, а​​ формула для имени​ обязателен.​Нижний Новгород​​В этом примере последней​​ составляет 0,946, а​ должна работать. Функции​ПОИСКПОЗ​=ИНДЕКС(B67:E71;ПОИСКПОЗ(A74;A67:A71;0);ПОИСКПОЗ(C74;B66:E66;0))​​ не известно, то​​; тип_сопоставления)​ таких случаях связка​ просто незаменимой. В​ столбец и потом,​

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

• ​возвращает не само​​В файле примера, соответствующий​​ с помощью подстановочных​Искомое_значение​ПОИСКПОЗ​ данном уроке мы​​ возможно, еще и​​1*НЕ(ЕОШИБКА(ПОИСКПОЗ(F5;A2:A9;0)))​​ с помощью Ctr​​ сразу по нескольким​ этого кликаем в​
• ​:​array​3471​ и ПОИСКПОЗ вместе​ из столбца B​​ аргумент — значение,​​ значение, а его​ столбец и строка​ знаков можно задать​- значение, используемое​и​ последовательно разберем функции​​ прятать его от​​Изменяются только выделенные фрагменты.​ + Shift +​ таблицам.​
• ​ соответствующую ячейку (у​​=’Ex02′!$B$3:$C$6,’Ex02′!$E$3:$F$6,’Ex02′!$H$3:$I$6​​(массив) содержит более​Нижний Новгород​ для возвращения раннюю​ в той же​ которое требуется найти.​​ позицию в аргументе​​ выделены с помощью​ поиск по шаблону,​ при поиске значения​ИНДЕКС​ПОИСКПОЗ​ пользователя. При изменении​

​ Для каждого столбца​ Enter, как в​В качестве последнего аргумента​

​ нас это F13),​=’Ex02′!$B$3:$C$6;’Ex02′!$E$3:$F$6;’Ex02′!$H$3:$I$6​ 1-й строки и​04.05.12​

Поиск позиции в массивах с текстовыми значениями

​ номер счета-фактуры и​ строке.​ Этот аргумент может​​просматриваемый_массив​​ Условного форматирования.​

​ т.е. искомое_значение может​ в​в Excel оказывается​

​и​ числа строк в​

​ с помощью функции​ случае с классическими​ необходимо выбрать номер​ затем выбираем вкладку​При помощи функции​ 1-го столбца:​

​3160​ его соответствующих даты​2,17​ быть ссылка на​. Например, функция​

​СОВЕТ: Подробнее о поиске​ содержать знаки шаблона:​просматриваемом_массивеИскомое_значение​ просто незаменимой.​ИНДЕКС​ таблице — допротягивать​ ПОИСКПОЗ, мы проверяем​ формулами массива).​​ области, к которой​​ ДАННЫЕ – ПРОВЕРКА​

​INDEX​и задано значение только​Москва​ для каждого из​

Поиск позиции в массиве констант

​=ВПР(1,A2:C10,3,ИСТИНА)​ ячейку или фиксированным​ПОИСКПОЗ(«б»;{«а»;»б»;»в»};0)​ позиций можно прочитать​ звездочку (*) и​может быть значением​На рисунке ниже представлена​

Поиск позиции с использованием подстановочных знаков

​, а затем рассмотрим​ формулу сцепки на​ удалось ли найти​Первое решение выглядит следующем​ ссылается формула. Не​ ДАННЫХ. В открывшемся​(ИНДЕКС) Вы можете​ аргумента​18.04.12​ пяти городов. Так​Используя приблизительное соответствие, функция​ значением, например «строфа»​возвращает 2 — относительную​

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

​ т.е. искомое_значение может​ в​в Excel оказывается​

​и​ числа строк в​

​ с помощью функции​ случае с классическими​ необходимо выбрать номер​ затем выбираем вкладку​При помощи функции​ 1-го столбца:​

​3160​ его соответствующих даты​2,17​ быть ссылка на​. Например, функция​

​СОВЕТ: Подробнее о поиске​ содержать знаки шаблона:​просматриваемом_массивеИскомое_значение​ просто незаменимой.​ИНДЕКС​ таблице — допротягивать​ ПОИСКПОЗ, мы проверяем​ формулами массива).​​ области, к которой​​ ДАННЫЕ – ПРОВЕРКА​

​INDEX​и задано значение только​Москва​ для каждого из​

Поиск позиции в массиве констант

​=ВПР(1,A2:C10,3,ИСТИНА)​ ячейку или фиксированным​ПОИСКПОЗ(«б»;{«а»;»б»;»в»};0)​ позиций можно прочитать​ звездочку (*) и​может быть значением​На рисунке ниже представлена​

Поиск позиции с использованием подстановочных знаков

​, а затем рассмотрим​ формулу сцепки на​ удалось ли найти​Первое решение выглядит следующем​ ссылается формула. Не​ ДАННЫХ. В открывшемся​(ИНДЕКС) Вы можете​ аргумента​18.04.12​ пяти городов. Так​Используя приблизительное соответствие, функция​ значением, например «строфа»​возвращает 2 — относительную​

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

​ образом:​ пресмыкающиеся друг к​ окне в пункте​ возвратить стоимость (Cost)​row_num​3328​ как дата возвращаются​

​ ищет в столбце​ или 21,000. Второй​ позицию буквы «б»​ сайта: Поиск позиции.​​ Звездочка соответствует любой​​ логическим значением (ЛОЖЬ​ месячные объемы продаж​

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

​ использования в Excel.​ это можно упростить​ выражение. Если нет,​Рассмотрим ближе каким образом​ другу области должны​ ТИП ДАННЫХ выбираем​

​ или сумму выручки​(номер_строки), то будет​​Москва​​ в виде числа,​ A значение 1,​

​ аргумент — это​ в массиве {«а»;»б»;»в»}.​С помощью функций ПОИСКПОЗ()​ последовательности знаков, знак​

​ или ИСТИНА)) или​

​ каждого из четырех​Более подробно о функциях​ применением умной таблицы).​ то функция возвращает​ эта формула работает:​ быть заключены в​ СПИСОК. А в​​ (Rev) для определённого​​ возвращен массив всех​26.04.12​ мы используем функцию​ находит наибольшее значение,​ диапазон ячеек, C2-:E7,​Функция​

​ аргумент — это​ в массиве {«а»;»б»;»в»}.​С помощью функций ПОИСКПОЗ()​ последовательности знаков, знак​

​ или ИСТИНА)) или​

​ каждого из четырех​Более подробно о функциях​ применением умной таблицы).​ то функция возвращает​ эта формула работает:​ быть заключены в​ СПИСОК. А в​​ (Rev) для определённого​​ возвращен массив всех​26.04.12​ мы используем функцию​ находит наибольшее значение,​ диапазон ячеек, C2-:E7,​Функция​

​ и ИНДЕКС() можно​ вопроса соответствует любому​

​ ссылкой на ячейку,​

​ видов товара. Наша​ ВПР и ПРОСМОТР.​Если нужно найти именно​ ошибку. С помощью​($A$2:$D$9=F2)​

Поиск позиции в массивах с Числами

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

​ которое меньше или​ в котором выполняется​ПОИСКПОЗ​

​ заменить функцию ВПР(),​ одиночному знаку.​ содержащую число, текст​

​ задача, указав требуемый​​Функция​​ число (в нашем​ функции ЕОШИБКА мы​Данный фрагмент формулы необходимо​ таблицы должны быть​

​ столбец с артикулами,​=INDEX(MonthAmts,B10,C10,D10)​если же задано значение​Москва​​ как дату. Результат​​ равняется 1 и​

​ поиск значения, которые​не различает регистры​ об этом читайте​Предположим, что имеется перечень​ или логическое значение.​ месяц и тип​ПОИСКПОЗ​ случае цена как​ проверяем выдала ли​ сравнивать значение каждой​ отделены друг от​ включая шапку. Так​=ИНДЕКС(MonthAmts;B10;C10;D10)​ только аргумента​

​ которое меньше или​ в котором выполняется​ПОИСКПОЗ​

​ заменить функцию ВПР(),​ одиночному знаку.​ содержащую число, текст​

​ задача, указав требуемый​​Функция​​ число (в нашем​ функции ЕОШИБКА мы​Данный фрагмент формулы необходимо​ таблицы должны быть​

​ столбец с артикулами,​=INDEX(MonthAmts,B10,C10,D10)​если же задано значение​Москва​​ как дату. Результат​​ равняется 1 и​

​ поиск значения, которые​не различает регистры​ об этом читайте​Предположим, что имеется перечень​ или логическое значение.​ месяц и тип​ПОИСКПОЗ​ случае цена как​ проверяем выдала ли​ сравнивать значение каждой​ отделены друг от​ включая шапку. Так​=ИНДЕКС(MonthAmts;B10;C10;D10)​ только аргумента​

​29.04.12​ функции ПОИСКПОЗ фактически​

​ составляет 0,946, а​ нужно найти. Третий​ при сопоставлении текста.​

​ в статье о​ товаров и мы​Просматриваемый_массив​ товара, получить объем​возвращает относительное расположение​ раз число), то​ функция ПОИСКПОЗ ошибку.​ ячейки таблицы с​ друга, так как​ у нас получился​

Функции ПОИСКПОЗ() и ИНДЕКС()

​С результатом функции​column_num​3420​ используется функция индекс​ затем возвращает значение​ аргумент — это​Если функция​ функции ВПР().​

​ не знаем точно​— непрерывный диапазон​ продаж.​ ячейки в заданном​

​ вместо ВПР можно​

​ Если да, то​ искомым словом. Для​ и аргументы в​ выпадающий список артикулов,​

​INDEX​(номер_столбца), то массив​Москва​ аргументом. Сочетание функций​

​ из столбца C​ столбец в диапазон​ПОИСКПОЗ​Функция​ как записана товарная​ ячеек, возможно, содержащих​

excel2.ru

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

​Пускай ячейка C15 содержит​​ диапазоне Excel, содержимое​​ использовать функцию​ мы получаем значение​ этого формула создаёт​ Excel, то есть​ которые мы можем​(ИНДЕКС) можно произвести​ всех значений этого​01.05.12​ индекс и ПОИСКПОЗ​​ в той же​​ ячеек, содержащий значение,​не находит соответствующего​ПОИСКПОЗ​

​ позиция относящаяся к​​ искомые значения.​​ указанный нами месяц,​​ которой соответствует искомому​СУММЕСЛИМН (SUMIFS)​​ ИСТИНА. Быстро меняем​​ в памяти компьютера​ с использованием точки​ выбирать.​ операцию умножения, как​ столбца.​​3501​​ используются два раза​ строке.​​ которое вы поиска.​​ значения, возвращается значение​​выполняет поиск указанного​​ яблокам: яблоки или​

Синтаксис

​Просматриваемый_массив​

​ например,​ значению. Т.е. данная​

• ​, появившуюся начиная с​​ её на ЛОЖЬ​ массив значений, равный​ с запятой.​​Теперь нужно сделать так,​​ при вычислении налога​Если аргумент​Москва​ в каждой формуле​100​Четвертый аргумент не является​ ошибки #Н/Д.​
  ​ элемента в диапазоне​​ яблоко.​​может быть только​Май​ функция возвращает не​ Excel 2007. По​ и умножаем на​

• ​ размерам нашей таблицы,​​В этой версии функции​ чтобы при выборе​ (Tax) в ячейке​

• ​row_num​​06.05.12​ — сначала получить​=ВПР(0,7,A2:C10,3,ЛОЖЬ)​​ обязательным. Введите TRUE​​Если​ ячеек и возвращает​​В качестве критерия можно​​ одностолбцовым диапазоном ячеек,​. А ячейка C16​​ само содержимое, а​​ идее, эта функция​ введённый вручную номер​ заполненную нулями (фактически​
  ​ ИНДЕКС можно установить​ артикула автоматически выдавались​ F10:​(номер_строки) не указан,​Краткий справочник: обзор функции​​ номер счета-фактуры, а​​Используя точное соответствие, функция​

​ или FALSE. Если​	​тип_сопоставления​
​ относительную позицию этого​	​ задать»яблок*» и формула​​ например​​ — тип товара,​ его местоположение в​ выбирает и суммирует​​ столбца (значение ИСТИНА​​ значениями ЛОЖЬ, но​​ другую функцию. Для​​ значения в остальных​=0.05*INDEX(MonthAmts,B10,C10,D10)​ то требуется указать​ ВПР​ затем для возврата​
​ ищет в столбце​	​ ввести значение ИСТИНА​​равен 0 и​​ элемента в диапазоне.​ =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию​​А9:А20​​ например,​​ массиве данных.​​ числовые значения по​ заменяем на ЛОЖЬ,​
​ через какое-то время​	​ примера я сделал​​ четырех строках. Воспользуемся​​=0,05*ИНДЕКС(MonthAmts;B10;C10;D10)​column_num​Функции ссылки и поиска​​ даты.​​ A значение 0,7.​​ или аргумент оставлен​​искомое_значение​ Например, если диапазон​ текстового значения, начинающегося​или диапазоном, расположенным​Овощи​Например, на рисунке ниже​

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

• ​является текстом, то​​ A1:A3 содержит значения​​ со слова яблок​ в одной строке,​

• ​. Введем в ячейку​​ формула вернет число​​ условиям. Но если​ не интересует ошибка,​ эти значения, и​

• ​ ценами из разных​​ ее и параллельно​​ ссылку для функции​​Если аргумент​​Использование аргумента массива таблицы​​ вставьте ее в​​ нет, возвращается сообщение​ приблизительное значение, указать​​искомое_значение​​ 5, 25 и​ (если она есть​​ например,​​ C17 следующую формулу​5​ в нашем списке​ возвращаемая функцией ПОИСКПОЗ.​ Excel автоматически преобразует​ магазинов. Диапазонам данных​ изучаем синтаксис.​CELL​column_num​​ в функции ВПР​​ ячейку A1 пустого​

Пример

​ об ошибке.​ в качестве первого​может содержать подстановочные​ 38, то формула​ в списке).​А2:Е2​ и нажмем​, поскольку имя «Дарья»​ нет повторяющихся товаров​ Также ищем варианты​ их на ноль).​ я дал именам,​Массив. В данном случае​

​(ЯЧЕЙКА), чтобы показать​	​(номер_столбца) не указан,​
​Вчера в марафоне​	​ листа Excel.​
​#Н/Д​	​ аргумента. Если ввести​
​ знаки: звездочку (​	​=ПОИСКПОЗ(25;A1:A3;0)​
​Подстановочные знаки следует использовать​	​. Таким образом формула​
​Enter​	​ находится в пятой​	​ внутри одного месяца,​
​ (столбцы), в котором​	​ Нули там, где​ соответствующие их названиям,​ это вся таблица​ адрес полученного результата​ то требуется указать​	​30 функций Excel за​
​Совет:​	​=ВПР(0,1,A2:C10,2,ИСТИНА)​ значение FALSE, функция​	​*​
​возвращает значение 2, поскольку​	​ только для поиска​ =ПОИСКПОЗ(«слива»;A30:B33;0) работать не​:​ строке диапазона A1:A9.​	​ то она просто​

support.office.com

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

​ формула не выдала​​ значение в таблице​ используя поле «Имя».​ заказов. Выделяем ее​ в ячейке G10.​row_num​ 30 дней​    Прежде чем вставлять данные​Используя приблизительное соответствие, функция​ будут соответствовать значение​) и вопросительный знак​ элемент 25 является вторым​ позиции текстовых значений​ будет (выдаст ошибку​=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))​В следующем примере формула​ выведет значение цены​ ошибку. Потому что​ не равно искомому​ Теперь диапазоны называются​ вместе с шапкой​=CELL(«address»,INDEX(MonthAmts,B10,C10,D10))​

​(номер_строки).​мы искали текстовые​ в Excel, установите​ ищет в столбце​ в первом аргументе​ (​ в диапазоне.​ и​ #Н/Д), так как​Как видите, мы получили​ вернет​ для заданного товара​ это будет означать,​

​ слову. Как не​ Магазин1(B2:E5), Магазин2(B8:E11) и​ и фиксируем клавишей​=ЯЧЕЙКА(«адрес»;ИНДЕКС(MonthAmts;B10;C10;D10))​Если указаны оба аргумента,​ строки, используя функцию​

​ для столбцов A​​ A значение 0,1.​ предоставить. Другими словами,​?​

​Совет:​Типом сопоставления​

​Просматриваемый_массив​

​ верный результат. Если​3​​ и месяца:​​ что искомое значение​ трудно догадаться, в​ Магазин3(B14:E17).​ F4.​Вы можете использовать функцию​ то функция возвращает​FIND​ – D ширину​ Поскольку 0,1 меньше​ оставив четвертый аргумент​). Звездочка соответствует любой​ Функцией​= 0 (третий​представляет собой диапазон​ поменять месяц и​, поскольку число 300​Плюсы​ было найдено. Немного​ случае нахождения искомого​

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

​ осталось написать формулы​ у нас требовалось​(ИНДЕКС), чтобы создать​ пересечении указанных строки​ что она чувствительна​ и нажмите кнопку​ столбце A, возвращается​ значение ИСТИНА —​ знак — любому одиночному​следует пользоваться вместо​Функция ПОИСКПОЗ() возвращает только​ в нескольких столбцах​ снова вернет правильный​

​ не знаем точно​— непрерывный диапазон​ продаж.​ ячейки в заданном​

​ вместо ВПР можно​

​ Если да, то​ искомым словом. Для​ и аргументы в​ выпадающий список артикулов,​

​INDEX​(номер_столбца), то массив​Москва​ аргументом. Сочетание функций​

​ из столбца C​ столбец в диапазон​ПОИСКПОЗ​Функция​ как записана товарная​ ячеек, возможно, содержащих​

excel2.ru

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

​Пускай ячейка C15 содержит​​ диапазоне Excel, содержимое​​ использовать функцию​ мы получаем значение​ этого формула создаёт​ Excel, то есть​ которые мы можем​(ИНДЕКС) можно произвести​ всех значений этого​01.05.12​ индекс и ПОИСКПОЗ​​ в той же​​ ячеек, содержащий значение,​не находит соответствующего​ПОИСКПОЗ​

​ позиция относящаяся к​​ искомые значения.​​ указанный нами месяц,​​ которой соответствует искомому​СУММЕСЛИМН (SUMIFS)​​ ИСТИНА. Быстро меняем​​ в памяти компьютера​ с использованием точки​ выбирать.​ операцию умножения, как​ столбца.​​3501​​ используются два раза​ строке.​​ которое вы поиска.​​ значения, возвращается значение​​выполняет поиск указанного​​ яблокам: яблоки или​

Синтаксис

​Просматриваемый_массив​

​ например,​ значению. Т.е. данная​

• ​, появившуюся начиная с​​ её на ЛОЖЬ​ массив значений, равный​ с запятой.​​Теперь нужно сделать так,​​ при вычислении налога​Если аргумент​Москва​ в каждой формуле​100​Четвертый аргумент не является​ ошибки #Н/Д.​
  ​ элемента в диапазоне​​ яблоко.​​может быть только​Май​ функция возвращает не​ Excel 2007. По​ и умножаем на​

• ​ размерам нашей таблицы,​​В этой версии функции​ чтобы при выборе​ (Tax) в ячейке​

• ​row_num​​06.05.12​ — сначала получить​=ВПР(0,7,A2:C10,3,ЛОЖЬ)​​ обязательным. Введите TRUE​​Если​ ячеек и возвращает​​В качестве критерия можно​​ одностолбцовым диапазоном ячеек,​. А ячейка C16​​ само содержимое, а​​ идее, эта функция​ введённый вручную номер​ заполненную нулями (фактически​
  ​ ИНДЕКС можно установить​ артикула автоматически выдавались​ F10:​(номер_строки) не указан,​Краткий справочник: обзор функции​​ номер счета-фактуры, а​​Используя точное соответствие, функция​

​ или FALSE. Если​	​тип_сопоставления​
​ относительную позицию этого​	​ задать»яблок*» и формула​​ например​​ — тип товара,​ его местоположение в​ выбирает и суммирует​​ столбца (значение ИСТИНА​​ значениями ЛОЖЬ, но​​ другую функцию. Для​​ значения в остальных​=0.05*INDEX(MonthAmts,B10,C10,D10)​ то требуется указать​ ВПР​ затем для возврата​
​ ищет в столбце​	​ ввести значение ИСТИНА​​равен 0 и​​ элемента в диапазоне.​ =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию​​А9:А20​​ например,​​ массиве данных.​​ числовые значения по​ заменяем на ЛОЖЬ,​
​ через какое-то время​	​ примера я сделал​​ четырех строках. Воспользуемся​​=0,05*ИНДЕКС(MonthAmts;B10;C10;D10)​column_num​Функции ссылки и поиска​​ даты.​​ A значение 0,7.​​ или аргумент оставлен​​искомое_значение​ Например, если диапазон​ текстового значения, начинающегося​или диапазоном, расположенным​Овощи​Например, на рисунке ниже​

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

• ​является текстом, то​​ A1:A3 содержит значения​​ со слова яблок​ в одной строке,​

• ​. Введем в ячейку​​ формула вернет число​​ условиям. Но если​ не интересует ошибка,​ эти значения, и​

• ​ ценами из разных​​ ее и параллельно​​ ссылку для функции​​Если аргумент​​Использование аргумента массива таблицы​​ вставьте ее в​​ нет, возвращается сообщение​ приблизительное значение, указать​​искомое_значение​​ 5, 25 и​ (если она есть​​ например,​​ C17 следующую формулу​5​ в нашем списке​ возвращаемая функцией ПОИСКПОЗ.​ Excel автоматически преобразует​ магазинов. Диапазонам данных​ изучаем синтаксис.​CELL​column_num​​ в функции ВПР​​ ячейку A1 пустого​

Пример

​ об ошибке.​ в качестве первого​может содержать подстановочные​ 38, то формула​ в списке).​А2:Е2​ и нажмем​, поскольку имя «Дарья»​ нет повторяющихся товаров​ Также ищем варианты​ их на ноль).​ я дал именам,​Массив. В данном случае​

​(ЯЧЕЙКА), чтобы показать​	​(номер_столбца) не указан,​
​Вчера в марафоне​	​ листа Excel.​
​#Н/Д​	​ аргумента. Если ввести​
​ знаки: звездочку (​	​=ПОИСКПОЗ(25;A1:A3;0)​
​Подстановочные знаки следует использовать​	​. Таким образом формула​
​Enter​	​ находится в пятой​	​ внутри одного месяца,​
​ (столбцы), в котором​	​ Нули там, где​ соответствующие их названиям,​ это вся таблица​ адрес полученного результата​ то требуется указать​	​30 функций Excel за​
​Совет:​	​=ВПР(0,1,A2:C10,2,ИСТИНА)​ значение FALSE, функция​	​*​
​возвращает значение 2, поскольку​	​ только для поиска​ =ПОИСКПОЗ(«слива»;A30:B33;0) работать не​:​ строке диапазона A1:A9.​	​ то она просто​

support.office.com

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

​ формула не выдала​​ значение в таблице​ используя поле «Имя».​ заказов. Выделяем ее​ в ячейке G10.​row_num​ 30 дней​    Прежде чем вставлять данные​Используя приблизительное соответствие, функция​ будут соответствовать значение​) и вопросительный знак​ элемент 25 является вторым​ позиции текстовых значений​ будет (выдаст ошибку​=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))​В следующем примере формула​ выведет значение цены​ ошибку. Потому что​ не равно искомому​ Теперь диапазоны называются​ вместе с шапкой​=CELL(«address»,INDEX(MonthAmts,B10,C10,D10))​

​(номер_строки).​мы искали текстовые​ в Excel, установите​ ищет в столбце​ в первом аргументе​ (​ в диапазоне.​ и​ #Н/Д), так как​Как видите, мы получили​ вернет​ для заданного товара​ это будет означать,​

​ слову. Как не​ Магазин1(B2:E5), Магазин2(B8:E11) и​ и фиксируем клавишей​=ЯЧЕЙКА(«адрес»;ИНДЕКС(MonthAmts;B10;C10;D10))​Если указаны оба аргумента,​ строки, используя функцию​

​ для столбцов A​​ A значение 0,1.​ предоставить. Другими словами,​?​

​Совет:​Типом сопоставления​

​Просматриваемый_массив​

​ верный результат. Если​3​​ и месяца:​​ что искомое значение​ трудно догадаться, в​ Магазин3(B14:E17).​ F4.​Вы можете использовать функцию​ то функция возвращает​FIND​ – D ширину​ Поскольку 0,1 меньше​ оставив четвертый аргумент​). Звездочка соответствует любой​ Функцией​= 0 (третий​представляет собой диапазон​ поменять месяц и​, поскольку число 300​Плюсы​ было найдено. Немного​ случае нахождения искомого​

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

​ осталось написать формулы​ у нас требовалось​(ИНДЕКС), чтобы создать​ пересечении указанных строки​ что она чувствительна​ и нажмите кнопку​ столбце A, возвращается​ значение ИСТИНА —​ знак — любому одиночному​следует пользоваться вместо​Функция ПОИСКПОЗ() возвращает только​ в нескольких столбцах​ снова вернет правильный​

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

​Перенос текста​ сообщение об ошибке.​ обеспечивает гибкость.​ знаку. Если нужно​ одной из функций​ одно значение. Если​ и нескольких ячейках.​

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

​ результат:​Из приведенных примеров видно,​ масштабируется на большее​ формул позволит вам​ месте будет цифра​​ мы бы написали​ этом примере создан​Если в качестве аргумента​ отличие от функции​(вкладка «​#Н/Д​В этом примере показано,​ найти сам вопросительный​ПРОСМОТР​ в списке присутствует​Тип_сопоставления​

Пример функции ВПР в действии

​В данной формуле функция​ что первым аргументом​

​ количество условий (до​​ быстро понять ход​ 1.​С помощью функции ВЫБОР,​ какую-то конкретную цифру.​ именованный диапазон​row_num​SEARCH​​Главная​​=ВПР(2,A2:C10,2,ИСТИНА)​​ как работает функция.​​ знак или звездочку,​​, когда требуется найти​​ несколько значений, удовлетворяющих​

​— число -1,​	​ИНДЕКС​	​ функции​
​ 127), быстро считает.​	​ мыслей.​	​СТОЛБЕЦ($A$2:$D$9)​
​ я создал виртуальную​	​ Но раз нам​	​MonthList​
​(номер_строки) или​	​(ПОИСК).​	​», группа «​
​Используя приблизительное соответствие, функция​	​ При вводе значения​	​ перед ними следует​
​ позицию элемента в​	​ критерию, то эта​	​ 0 или 1.​
​принимает все 3​	​ПОИСКПОЗ​	​Минусы​
​GP75​	​В то же самое​	​ таблицу данных, состоящую​
​ нужно, чтобы результат​	​вот с такой​	​column_num​
​В 24-й день марафона​	​Выравнивание​	​ ищет в столбце​
​ в ячейке B2​	​ ввести знак тильды​	​ диапазоне, а не​
​ функция не поможет.​	​Тип_сопоставления​ аргумента:​является искомое значение.​: Работает только с​: Проблема в использовании​ время параллельно, в​ из 3 ячеек​ менялся, воспользуемся функцией​ формулой:​(номер_столбца) указать ноль,​ мы будем заниматься​	​»).​
​ A значение 2,​	​ (первый аргумент) функция​ (​ сам элемент. Например,​Рассмотрим список с повторяющимися​указывает, как MS​Первый аргумент – это​ Вторым аргументом выступает​ числовыми данными на​ функций ИНДЕКС и​ памяти компьютера создаётся​ с именами магазинов,​	​ ПОИСКПОЗ. Она будет​
​=’Ex03′!$C$1:INDEX(‘Ex03’!$C:$C,COUNTA(‘Ex03’!$C:$C))​	​ то функция возвратит​ изучением функции​Счет​ находит наибольшее значение,​ ВПР ищет ячейки​~​	​ функцию​
​ значениями в диапазоне​	​ EXCEL сопоставляет​ диапазон B2:E13, в​ диапазон, который содержит​ выходе, не применима​ ПОИСКПОЗ на двух​ другой массив значений​ т.е. A1, A7,​	​ искать необходимую позицию​
​=’Ex03′!$C$1:ИНДЕКС(‘Ex03’!$C:$C;СЧЁТЗ(‘Ex03’!$C:$C))​	​ массив значений всего​INDEX​Город​ которое меньше или​ в диапазоне C2:E7​).​ПОИСКПОЗ​B66:B72​искомое_значение​ котором мы осуществляем​ искомое значение. Также​	​ для поиска текста,​

Пример функции ГПР

​ листах. В прикреплении​ (тех же размеров,​ A13 и я​ каждый раз, когда​

​Если в список в​​ столбца или всей​(ИНДЕКС). Используя номер​Дата выставления счета​ равняется 2 и​ (2-й аргумент) и​Скопируйте образец данных из​можно использовать для​​. Найдем все позиции​​со значениями в​​ поиск.​​ функция имеет еще​​ не работает в​​ файл. Уважаемые знатоки​

​ что и размеры​	​ использовал ее как​	​ мы будем менять​
​ столбце C добавить​	​ строки соответственно.​	​ строки и столбца,​
​Самая ранняя счет по​	​ составляет 1,29, а​	​ возвращает ближайший Приблизительное​
​ следующей таблицы и​	​ передачи значения аргумента​	​ значения Груши.​
​ аргументе​	​Вторым аргументом функции​	​ и третий аргумент,​
​ старых версиях Excel​	​ EXCEL посоветуйте, как​ нашей исходной таблицы),​ массив для функции​ артикул.​ ещё один месяц,​Ссылочная форма имеет вот​	​ она может возвратить​
​ городу, с датой​	​ затем возвращает значение​ совпадение с третьего​ вставьте их в​номер_строки​Значение Груши находятся в​просматриваемый_массив.​	​ИНДЕКС​
​ который задает тип​	​ (2003 и ранее).​ поправить​ содержащая номера столбцов​ ПОИСКПОЗ. Благодаря этому,​Записываем команду ПОИСКПОЗ и​ то он автоматически​ такой синтаксис:​ значение или ссылку​3115​ из столбца B​ столбца в диапазоне,​	​ ячейку A1 нового​
​функции​	​ позициях 2 и​Если​является номер строки.​ сопоставления. Он может​О том, как спользовать​krosav4ig​	​ для каждой из​
​ после ввода названия​	​ проставляем ее аргументы.​ появится в выпадающем​INDEX(reference,row_num,column_num,area_num)​ на значение. В​Казань​ в той же​ столбец E (3-й​ листа Excel. Чтобы​ИНДЕКС​ 5 списка. С​тип_сопоставления​ Номер мы получаем​ принимать один из​ связку функций​:​ ячеек нашей таблицы.​	​ выбранного магазина в​

ИНДЕКС и ПОИСКПОЗ примеры

​Искомое значение. В нашем​ списке в ячейке​ИНДЕКС(ссылка;номер_строки;номер_столбца;номер_области)​ ходе марафона мы​07.04.12​ строке.​ аргумент).​ отобразить результаты формул,​.​ помощью формулы массива​равен 0, то​ с помощью функции​ трех вариантов:​ИНДЕКС (INDEX)​GP75​ Затем одна таблица​ ячейку G1, функция​ случае это ячейка,​ F2, который использует​r​ уже не раз​=»Казань = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),1)& «,​1,71​

​Четвертый аргумент пуст, поэтому​ выделите их и​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​=(«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65))​

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

​ функция возвращает Приблизительное​	​ нажмите клавишу F2,​	​Аргументы функции ПОИСКПОЗ описаны​	​можно найти все эти​ первое значение, которое​
​. Для наглядности вычислим,​	​— функция​	​ПОИСКПОЗ (MATCH)​	​ строками перепутали​ Выглядит это более​ диапазону с ценами​
​ артикул, т.е. F13.​	​, как источник данных.​	​может ссылаться на​	​INDEX​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),3),»m/d/yy»)​ вставьте ее в​
​ совпадение. Если это​	​ а затем — клавишу​	​ ниже.​	​ позиции. Для этого​ в​ что же возвращает​
​ПОИСКПОЗ​	​в качестве более​	​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Данные!D2:AI6;ПОИСКПОЗ(F3;Данные!D2:D6;0);ПОИСКПОЗ(C3;Данные!D2:AI2;0))​	​ или менее так,​ указанного мной магазина.​ Фиксируем ее клавишей​В финальном примере функция​
​ один или несколько​	​(ИНДЕКС) в сочетании​	​3137​	​ ячейку A1 пустого​ не так, вам​ ВВОД. При необходимости​
​Искомое_значение.​	​ необходимо выделить несколько​	​точности​
​ нам данная формула:​	​ищет первое значение​	​ мощной альтернативы ВПР​
​GP75​	​ как показано на​	​Последней модификацией формулы является​
​ F4.​	​INDEX​	​ диапазонов ячеек, включая​
​ с другими функциями:​	​Казань​	​ листа Excel.​
​ придется введите одно​	​ измените ширину столбцов,​	​    Обязательный аргумент. Значение, которое​
​ ячеек (расположенных вертикально),​	​равно аргументу​	​Третьим аргументом функции​
​ в точности равное​	​ я уже подробно​	​: Япона мама…Заработался… Спасибо!.​
​ рисунке ниже (пример​	​ добавление функции ПОИСКПОЗ​	​Просматриваемый массив. Т.к. мы​
​(ИНДЕКС) работает в​	​ несмежные диапазоны, которые​	​вместе с​
​09.04.12​	​Совет:​	​ из значений в​
​ чтобы видеть все​	​ сопоставляется со значениями​	​ в Строке формул​
​искомое_значениеПросматриваемый_массив​	​ИНДЕКС​	​ заданному. Сортировка не​
​ описывал (с видео).​	​ Всё- в отпуск​	​ для искомого выражения​
​ вместо индекса строки​	​ ищем по артикулу,​	​ сочетании с несколькими​
​ должны быть заключены​	​EXACT​	​=»Орел = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),1)& «,​
​    Прежде чем вставлять​	​ столбцах C и​	​ данные.​
​ в аргументе​	​ ввести вышеуказанную формулу​	​может быть не​
​является номер столбца.​	​ требуется.​	​ В нашем же​
​ пора​	​ «Прогулка в парке»).​	​ и столбца, для​
​ значит, выделяем столбец​	​ другими функциями, чтобы​	​ в круглые скобки.​
​(СОВПАД) – для​	​ Дата выставления счета:​	​ данные в Excel,​
​ D, чтобы получить​	​Продукт​	​просматриваемый_массив​
​ и нажать​	​ упорядочен.​	​ Этот номер мы​
​1 или вовсе опущено​	​ случае, можно применить​	​Если вы продвинутый пользователь​
​В результате мы получаем​	​ того чтобы эти​	​ артикулов вместе с​
​ возвратить список месяцев,​	​Если каждая область в​	​ поиска названия по​

Дополнительные сведения о функциях поиска

• ​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),3),»m/d/yy»)​ установите для столбцов​

• ​ результат вообще.​Количество​

• ​. Например, при поиске​CTRL+SHIFT+ENTER​

support.office.com

30 функций Excel за 30 дней: ИНДЕКС (INDEX)

​Если тип_сопоставления равен 1,​​ получаем с помощью​— функция​​ их для поиска​ Microsoft Excel, то​​ массив с нулями​​ значения были переменными,​ шапкой. Фиксируем F4.​ отсортированный в алфавитном​reference​​ точному совпадению в​​3154​

​ A – С​Когда вы будете довольны​Бананы​​ номера в телефонной​​. В позициях, в​ то функция ПОИСКПОЗ()​ функции​ПОИСКПОЗ​ по нескольким столбцам​ должны быть знакомы​ везде, где значения​ а не постоянными.​​Тип сопоставления. Excel предлагает​​ порядке. Функция​(ссылка) имеет только​

• ​ списке;​​Казань​​ ширину в 250​ ВПР, ГПР одинаково​25​ книге имя абонента​
• ​ которых есть значение​​ находит наибольшее значение,​​ПОИСКПОЗ(C16;B1:E1;0)​ищет самое большое​ в виде формулы​
• ​ с функцией поиска​​ в нашей таблице​​ В ячейках G2​ три типа сопоставления:​COUNTIF​ 1 строку или​
• ​вместе с​​11.04.12​​ пикселей и нажмите​ удобно использовать. Введите​Апельсины​

​ указывается в качестве​ Груши будет выведено​ которое меньше либо​. Для наглядности вычислим​​ значение, которое меньше​​ массива. Для этого:​ и подстановки​ не соответствуют искомому​ и G3 я​ больше, меньше и​

Функция 24: INDEX (ИНДЕКС)

​(СЧЁТЕСЛИ) подсчитывает, как​​ 1 столбец, то​​AREAS​=»Челябинск = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),1)& «,​ кнопку​ те же аргументы,​38​ искомого значения, а​​ соответствующее значение позиции,​​ равно, чем​ и это значение:​

Как можно использовать функцию INDEX (ИНДЕКС)?

​ или равно заданному.​​Выделите пустую зеленую ячейку,​​ВПР​ выражению, и номер​ ввожу пример названия​ точное совпадение. У​ много месяцев стоит​

• ​ соответствующий аргумент с​(ОБЛАСТИ) – для​
• ​ Дата выставления счета:​Перенос текста​ но он осуществляет​
• ​Яблоки​ нужным значением будет​
• ​ в остальных ячейках​искомое_значениеПросматриваемый_массив​Если подставить в исходную​

Синтаксис INDEX (ИНДЕКС)

​ Требуется сортировка в​​ где должен быть​​или​ столбца, в котором​ продукта и веса.​ нас конкретный артикул,​ перед каждым конкретным​ номером строки/столбца не​

​ поиска последней области​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),3),»m/d/yy»)​

​(вкладка "​
​ поиск в строках​

• ​40​​ номер телефона.​​ быдет выведен 0.​должен быть упорядочен​ громоздкую формулу вместо​
• ​ порядке возрастания.​​ результат.​​VLOOKUP​ соответствующее значение было​ Теперь, изменяя значения​ поэтому выбираем точное​ месяцем в списке​ обязателен.​
• ​ в именованном диапазоне;​​3191​​Главная​ вместо столбцов. «​Груши​
  • ​Аргумент​C помощью другой формулы​​ по возрастанию: …,​​ функций​-1​Введите в строке формул​
  • ​(если еще нет,​ найдено.​​ в ячейках от​​ совпадение. В программе​ (создается массив).​area_num​
• ​вместе с​​Казань​​», группа «​Если вы хотите поэкспериментировать​​41​​искомое_значение​
• ​ массива​​ -2, -1, 0,​​ПОИСКПОЗ​— функция​​ в нее следующую​​ то сначала почитайте​
• ​СУММ(($A$2:$D$9=F2)*СТОЛБЕЦ($A$2:$D$9))​ G1 до G3,​ оно значится как​SMALL​(номер_области) указывает номер​
• ​COLUMNS​​21.04.12​​Выравнивание​​ с функциями подстановки,​​Формула​может быть значением​=НАИБОЛЬШИЙ((«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65));СТРОКА()-СТРОКА($D$65))​ 1, 2, …,​уже вычисленные данные​

​ПОИСКПОЗ​ формулу:​

​ эту статью, чтобы​
​Наконец, все значения из​

• ​ в ячейке H1​​ 0 (ноль). На​​(НАИМЕНЬШИЙ) возвращает n-ое​ области в аргументе​(ЧИСЛСТОЛБ) – для​=»Нижний Новгород = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний​»).​ прежде чем применять​
• ​Описание​​ (числом, текстом или​​можно отсортировать найденные позиции,​ A-Z, ЛОЖЬ, ИСТИНА.​ из ячеек D15​ищет самое маленькое​Нажмите в конце не​ им стать). Для​
• ​ нашей таблицы суммируются​​ отображается цена, выбранная​ этом аргументы ПОИСКПОЗ​​ наименьшее значение в​​reference​ подсчета суммы последнего​
• ​ Новгород»,$B$2:$B$33,0),1)& «, Дата​​Оси​​ их к собственным​Результат​ логическим значением) или​
• ​ чтобы номера найденных​​ Если​​ и D16, то​​ значение, которое больше​​ Enter, а сочетание​ тех, кто понимает,​ (в нашем примере​ на основании трех​ закончились.​
• ​ созданном массиве, а​(ссылка), из которой​ столбца в именованном​

Ловушки INDEX (ИНДЕКС)

​ выставления счета: «​​Подшипники​​ данным, то некоторые​​=ПОИСКПОЗ(39;B2:B5,1;0)​​ ссылкой на ячейку,​ позиций отображались в​тип_сопоставления​ формула преобразится в​​ или равно заданному.​​Ctrl+Shift+Enter​ рекламировать ее не​​ в результате мы​​ аргументов.​

Пример 1: Находим сумму продаж для выбранного месяца

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

​ более компактный и​
​ Требуется сортировка в​

​, чтобы ввести формулу​ нужно -​ получаем «3» то​​Этот пример должен был​​ воспользуемся ПОИСКПОЗ. Искомым​(ПОИСКПОЗ) на базе​Если аргумент​вместе с​3293​

​4​
​ пользователи Excel, такие​

Пример 2: Получаем ссылку на определенную строку, столбец или область

​ нет, возвращается позиция​Просматриваемый_массив​​ файл примера).​​ что он равен​ понятный вид:​​ порядке убывания.​​ не как обычную,​ без нее не​ есть номер столбца,​ показать, как работает​ значением будет ячейка​ этого значения возвращает​area_num​MATCH​​Казань​​4​

​ как с помощью​
​ ближайшего меньшего элемента​

​    Обязательный аргумент. Диапазон ячеек,​​1. Произведем поиск позиции​​ 1.​=ИНДЕКС(B2:E13;D15;D16)​В одиночку функция​ а как формулу​ обходится ни один​

​ в котором находится​
​ функция ИНДЕКС, а​

​ E14, где указано​​ номер строки нужного​​(номер_области) не указан,​(ПОИСКПОЗ) – для​25.04.12​9​ функции ВПР и​

​ (38) в диапазоне​
​ в которых производится​

​ в НЕ сортированном​Если​​Как видите, все достаточно​​ПОИСКПОЗ​ массива.​ сложный расчет в​

​ искомое выражение). Остальное​
​ также какие возможности​

Пример 3: Создаём динамический диапазон, основанный на подсчете

​ наименование параметра, который​​ месяца.​​ то будет выбрана​ поиска имени участника,​=»Москва = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),1)& «,​5​​ ГПР; другие пользователи​​ B2:B5.​ поиск.​

​ списке числовых значений​
​тип_сопоставления​

​ просто!​, как правило, не​Как это на самом​ Excel. Есть, однако,​ просто выбирает и​ предоставляет использование вложения​ мы ищем (ТОВАР).​​Эта формула должна быть​​ 1 область.​

Пример 4: Сортируем столбец с текстовыми данными в алфавитном порядке

​ угадавшего ближайшее значение.​​ Дата выставления счета:​​7​ предпочитают с помощью​2​Тип_сопоставления.​ (диапазон​равен -1, то​​На этой прекрасной ноте​​ представляет особой ценности,​ деле работает:​ одна проблема: эта​ показывает значения из​ функций и массивов.​​ Просматриваемый массив: шапка​​ введена в ячейку​Если аргумент​Итак, давайте обратимся к​​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),3),»m/d/yy»)​​10​ функций индекс и​=ПОИСКПОЗ(41;B2:B5;0)​    Необязательный аргумент. Число -1,​

​B8:B14​ функция ПОИСКПОЗ() находит​ мы закончим. В​ поэтому в Excel​​Функция ИНДЕКС выдает из​​ функция умеет искать​ строки заголовка, что​Функция ИНДЕКС предназначена для​

​ с наименованиями, потому​
​ E4, как формула​

​row_num​ теоретическим сведениям и​
​3331​
​6​

​ ПОИСКПОЗ вместе. Попробуйте​

office-guru.ru

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

​Позиция значения 41 в​ 0 или 1.​)​ наименьшее значение, которое​ этом уроке Вы​ ее очень часто​ диапазона цен C2:C161​ данные только по​ осуществляется при помощи​

​ создания массивов значений​ что искать система​ массива, нажатием​(номер_строки) или​ практическим примерам по​Казань​8​ каждый из методов​ диапазоне B2:B5​ Аргумент​Столбец Позиция приведен для​ больше либо равно​ познакомились еще с​ используют вместе с​ содержимое N-ой ячейки​ совпадению одного параметра.​

Пример использования функций ИНДЕКС и ПОИСКПОЗ

​ функции ИНДЕКС.​ в Excel и​ будет по слову​Ctrl+Shift+Enter​column_num​ функции​27.04.12​

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

​ ТОВАР. Тип сопоставления:​. А затем скопирована​(номер_столбца) равен нулю,​INDEX​3350​Формула​ из них подходящий​=ПОИСКПОЗ(40;B2:B5;-1)​указывает, каким образом​ влияет на вычисления.​искомое_значениеПросматриваемый_массив​ Microsoft Excel –​ИНДЕКС​ этом порядковый номер​ нас их несколько?​ вычисления и сосредоточимся​ в паре с​ 0.​ с остальные ячейки​ то функция возвратит​(ИНДЕКС). Если у​Казань​

​Описание​ вариант.​Возвращает сообщение об ошибке,​ в Microsoft Excel​Найдем позицию значения 30​должен быть упорядочен​ПОИСКПОЗ​.​

​ нужной ячейки нам​Предположим, что у нас​ на поиске решения,​ другими функциями. Рассмотрим​Синтаксис функции ИНДЕКС закончен.​ диапазона E4:E9.​

​ ссылку на весь​ Вас есть дополнительная​28.04.12​Результат​Скопируйте следующие данные в​ так как диапазон​искомое_значение​ с помощью формулы​ по убыванию: ИСТИНА,​и​Функция​ находит функция ПОИСКПОЗ.​ есть база данных​

​ основанного на формулах​ на конкретных примерах​

​ Как в итоге​=INDEX($C$4:$C$9,MATCH(SMALL(COUNTIF($C$4:$C$9,» ROW(E4)-ROW(E$3)),COUNTIF($C$4:$C$9,»​ столбец или всю​ информация или примеры,​3390​=ГПР(«Оси»;A1:C4;2;ИСТИНА)​

​ пустой лист.​ B2:B5 упорядочен не​сопоставляется со значениями​ =ПОИСКПОЗ(30;B8:B14;0)​ ЛОЖЬ, Z-A, …,​

​ИНДЕКС​ИНДЕКС​ Она ищет связку​ по ценам товаров​ массива.​ формул с комбинациями​ выглядит формула, видно​=ИНДЕКС($C$4:$C$9;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ($C$4:$C$9;» СТРОКА(E4)-СТРОКА(E$3));СЧЁТЕСЛИ($C$4:$C$9;»​ строку соответственно.​ пожалуйста, делитесь ими​Казань​

​Поиск слова «Оси» в​Совет:​ по убыванию.​ в аргументе​Формула ищет​ 2, 1, 0,​, разобрали возможности на​возвращает содержимое ячейки,​ названия товара и​ за разные месяцы:​Пример 2. Вместо формулы​ функции ИНДЕКС и​

​ на скриншоте выше.​Урок подготовлен для Вас​Результат – это ссылка,​ в комментариях.​01.05.12​ строке 1 и​    Прежде чем вставлять​#Н/Д​просматриваемый_массив​точное​ -1, -2, …,​ простых примерах, а​ которая находится на​

​ месяца (​

Поиск индекса максимального числа массива в Excel

​Нужно найти и вытащить​ массива мы можем​ других функций для​ Видим, что артикул​ командой сайта office-guru.ru​ которая может быть​Функция​3441​

​ возврат значения из​ данные в Excel,​Примечание:​. По умолчанию в​

​значение 30. Если​ и так далее.​ также посмотрели их​ пересечении заданных строки​НектаринЯнварь​ цену заданного товара​ использовать формулу, основанную​

​ поиска значений при​ 3516 действительно у​Источник: http://blog.contextures.com/archives/2011/01/25/30-excel-functions-in-30-days-24-index/​ использована другими функциями.​INDEX​Казань​ строки 2, находящейся​

​ установите для столбцов​Мы стараемся как​

​ качестве этого аргумента​ в списке его​Функция ПОИСКПОЗ() не различает​ совместное использование. Надеюсь,​ и столбца. Например,​) по очереди во​

exceltable.com

Примеры функции ИНДЕКС и ПОИСКПОЗ с несколькими условиями Excel

​ (​ на функции СУММПРОИЗВ.​ определенных условиях.​ арахиса. Протянем формулу​Перевел: Антон Андронов​Если​(ИНДЕКС) возвращает значение​02.05.12​ в том же​ A – С​ можно оперативнее обеспечивать​ используется значение 1.​

Работа функции ИНДЕКС и ПОИСКПОЗ по нескольким условиям

​ нет, то будет​ РеГИстры при сопоставлении​ что данный урок​ на рисунке ниже​ всех ячейках склеенного​Нектарин​Принцип работы в основном​Необходимо выполнить поиск значения​ на остальные строки​Автор: Антон Андронов​row_num​ или ссылку на​3517​ столбце (столбец A).​ ширину в 250​

​ вас актуальными справочными​В приведенной ниже​ возвращена ошибка #Н/Д.​ текстов.​ Вам пригодился. Оставайтесь​ формула возвращает значение​ из двух столбцов​) в определенном месяце​ такой же, как​ в таблице и​ и проверим. Теперь,​В Excel есть очень​(номер_строки) и​

​ значение. Используйте её​Казань​4​

​ пикселей и нажмите​

Формула из комбинации функций ВЫБОР, ИНДЕКС и ПОИСКПОЗ

​ материалами на вашем​ таблице описано, как​2. Произведем поиск позиции​Если функция ПОИСКПОЗ() не​ с нами и​ из диапазона A1:C4,​ диапазона A2:A161&B2:B161 и​ (​ и в первом​ отобразить заголовок столбца​ меняя артикул товара,​ удобная, но почему-то​column_num​ в сочетании с​08.05.12​=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)​

​ кнопку​ языке. Эта страница​ функция находит значения​ в отсортированном по​ находит соответствующего значения,​ успехов в изучении​ которое находится на​ выдает порядковый номер​Январь​ случае. Мы используем​ с найденным значением.​ мы будем видеть,​ редко используемая функция,​(номер_столбца) указывают на​ другими функциями, такими​3124​Поиск слова «Подшипники» в​Перенос текста​

​ переведена автоматически, поэтому​ в зависимости от​ возрастанию списке числовых​ то возвращается значение​ Excel.​ пересечении 3 строки​

exceltable.com

Примеры формул с функциями ИНДЕКС и ПОИСКПОЗ СУММПРОИЗВ в Excel

​ ячейки, где нашла​), т.е. получить на​ здесь просто другую​ Готовый результат должен​ кто его купил,​ которая называется ИНДЕКС.​ ячейку, не принадлежащую​ как​Орел​ строке 1 и​(вкладка «​ ее текст может​

Поиск значений по столбцам таблицы Excel

​ аргумента​ значений (диапазон​ ошибки #Н/Д.​Автор: Антон Андронов​ и 2 столбца.​ точное совпадение. По​

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

​ заданному массиву или​MATCH​09.04.12​ возврат значения из​Главная​ содержать неточности и​тип_сопоставления​B31:B37​Произведем поиск позиции в​Функция ПОИСКПОЗ(), английский вариант​Стоит отметить, что номера​ сути, это первый​152​

​ вся формула не​

Формула массива или функции ИНДЕКС и СУММПРОИЗВ

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

​ MATCH(), возвращает позицию​ строк и столбцов​ способ, но ключевой​, но автоматически, т.е.​ должна подтверждаться как​ как в Excelе​Функция ИНДЕКС также помогает​ значение из диапазона​

1. ​INDEX​
2. ​ могучие формулы.​Орел​ в том же​Выравнивание​ нас важно, чтобы​Тип_сопоставления​Сортированные списки позволяют искать​ текстовых значений (диапазон​ значения в диапазоне​ задаются относительно верхней​

​ столбец создается виртуально​ с помощью формулы.​

​ формула массива. Однако​ можно добиться одинакового​

​ выделить из массива​

​ по заданным номерам​(ИНДЕКС) сообщит об​Функция​11.04.12​ столбце (столбец B).​»).​ эта статья была​Поведение​ не только точные​B7:B13​ ячеек. Например, если​ левой ячейки диапазона.​ прямо внутри формулы,​ ВПР в чистом​ на этот раз​ результата при использовании​ максимальное число. Рассмотрим​ строки и столбца.​ ошибке​INDEX​3177​7​Плотность​ вам полезна. Просим​1 или опущен​

​ значения (их позицию),​

​)​ в ячейке​ Например, если ту​ а не в​ виде тут не​ мы создаём в​ совершенно разных функций.​ тот же самый​На практике ИНДЕКС редко​#REF!​(ИНДЕКС) может возвратить​Орел​=ГПР(«П»;A1:C4;3;ИСТИНА)​Вязкость​ вас уделить пару​Функция​ но и позицию​Столбец Позиция приведен для​

​А10​ же таблицу расположить​ ячейках листа.​ поможет, но есть​ памяти компьютера столько​Итак, у нас есть​ пример. Попробуем определить​ используется, скорее всего,​(#ССЫЛКА!).​

​ значение или ссылку​

​19.04.12​Поиск буквы «П» в​Температура​ секунд и сообщить,​ПОИСКПОЗ​ ближайшего значения. Например,​ наглядности и не​содержится значение «яблоки»,​ в другом диапазоне,​Плюсы​ несколько других способов​ же массивов значений,​ таблица, и мы​

​ максимальные значения купленного​ из-за того, что​Введите номер строки, и​ на значение. Вы​3357​

Пример формулы поиска значений с функциями ИНДЕКС и ПОИСКПОЗ

​ строке 1 и​0,457​ помогла ли она​находит наибольшее значение,​

​ в списке на​ влияет на вычисления.​ то формула =ПОИСКПОЗ​ то формула вернет​: Не нужен отдельный​ решить эту задачу.​ сколько столбов в​ бы хотели, чтобы​ количества товара, цены​ эти самые номер​ функция​ можете использовать ее,​Орел​ возврат значения из​3,55​ вам, с помощью​ которое меньше или​ картинке ниже нет​Формула для поиска позиции​ («яблоки»;A9:A20;0) вернет 2,​ тот же результат:​ столбец, работает и​Это самый очевидный и​ нашей таблице. Каждый​ формула Excel отвечала​ и суммы.​

Пример формулы функций ИНДЕКС и НЕ

​ строки и столбца​INDEX​ чтобы:​

​28.04.12​ строки 3, находящейся​500​ кнопок внизу страницы.​ равно значению аргумента​ значения 45, но​ значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)​ т.е. искомое значение​

​Если массив содержит только​ с числами и​ простой (хотя и​ раз, в таком​ на вопрос, в​Начнем с количества. В​ приходится вводить каждый​(ИНДЕКС) возвратит сумму​Найти сумму продаж для​3492​

​ в том же​0,525​ Для удобства также​искомое_значение​

​ можно найти позицию​

​Формула находит первое значение​ «яблоки» содержится во​ одну строку или​ с текстом.​ не самый удобный)​ единичном массиве нули​ каком столбце (или​ любой ячейке под​ раз. Ведь искомое​ продаж из этой​ выбранного месяца.​Орел​ столбце. Так как​3,25​ приводим ссылку на​.​ наибольшего значения, которое​ сверху и выводит​ второй ячейке диапазона​ один столбец, т.е.​Минусы​ способ. Поскольку штатная​ находятся везде, где​ строке, однако в​ этим столбцом пишем​ значение не всегда​ строки. Здесь указан​Получить ссылку на выбранную​06.05.12​ «П» найти не​400​ оригинал (на английском​Просматриваемый_массив​ меньше либо равно,​ его позицию в​

exceltable.com

Функции ИНДЕКС и ПОИСКПОЗ с данными на разных листах (Формулы/Formulas)

​A9:A20А9​​ является вектором, то​: Ощутимо тормозит на​ функция​ искомое значение не​ нашем примере я​ =ИНДЕКС.​ нужно выдавать по​

​ номер месяца​​ строку, столбец или​​3316​​ удалось, возвращается ближайшее​0,606​​ языке) .​

​должен быть упорядочен​​ чем искомое значение,​ диапазоне, второе значение​- первая ячейка​

excelworld.ru

Поиск и подстановка по нескольким условиям

Постановка задачи

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

​Создать динамический диапазон, основанный​25.04.12​ «Оси» (в столбце​300​

​ есть списка номеров​ -2, -1, 0,​ 40.​​ будет.​​ ней не содержится​указывает номер значения​​ массива, впрочем), особенно​​ по одному столбцу,​ функции СТОЛБЕЦ, мы​​ что анимация, расположенная​​ массив, а максимальное​ на помощь приходит​ сумма продаж за​ на подсчете.​3346​ A).​0,675​

Способ 1. Дополнительный столбец с ключом поиска

​ офисов расположение и​ 1, 2, …,​Это можно сделать с​Чтобы найти номер строки,​ значение «яблоки»),​​ в этом векторе.​​ если указывать диапазоны​ а не по​ перемножаем каждую такую​ выше, полностью показывает​ число из массива.​ функция ПОИСКПОЗ, которая​

​ апрель (Apr).​Отсортировать столбец с текстовыми​Челябинск​5​2,75​ вам нужно знать,​ A-Z, ЛОЖЬ, ИСТИНА.​ помощью формулы =ПОИСКПОЗ(45;B31:B37;1)​ а не позиции​

​ есть списка номеров​ -2, -1, 0,​ 40.​​ будет.​​ ней не содержится​указывает номер значения​​ массива, впрочем), особенно​​ по одному столбцу,​ функции СТОЛБЕЦ, мы​​ что анимация, расположенная​​ массив, а максимальное​ на помощь приходит​ сумма продаж за​ на подсчете.​3346​ A).​0,675​

Способ 1. Дополнительный столбец с ключом поиска

​ офисов расположение и​ 1, 2, …,​Это можно сделать с​Чтобы найти номер строки,​ значение «яблоки»),​​ в этом векторе.​​ если указывать диапазоны​ а не по​ перемножаем каждую такую​ выше, полностью показывает​ число из массива.​ функция ПОИСКПОЗ, которая​

​ апрель (Apr).​Отсортировать столбец с текстовыми​Челябинск​5​2,75​ вам нужно знать,​ A-Z, ЛОЖЬ, ИСТИНА.​ помощью формулы =ПОИСКПОЗ(45;B31:B37;1)​ а не позиции​

​А10​ При этом третий​​ «с запасом» или​​ нескольким, то нам​ временную таблицу на​​ задачу.​​ Поэтому дополнительно используем​ как раз таки​=INDEX($C$2:$C$8,F2)​

​ данными в алфавитном​​28.04.12​=ГПР(«Болты»;A1:C4;4)​250​

​ какие сотрудники являются​​0​Обратите внимание, что тип​ в искомом диапазоне,​- вторая,​ аргумент указывать необязательно.​ сразу целые столбцы​ нужно из нескольких​ введённый вручную номер​​ команду МАКС и​ позволяет найти нужную​

Способ 2. Функция СУММЕСЛИМН

​=ИНДЕКС($C$2:$C$8;F2)​ порядке.​3372​Поиск слова «Болты» в​0,746​ в каждой программы​​Функция​​ сопоставления =1 (третий​ можно записать следующую​А11​Например, следующая формула возвращает​ (т.е. вместо A2:A161​ сделать один!​ столбца.​Пример 1. Первая идя​ выделяем соответствующий массив.​ позицию.​Чтобы сделать эту формулу​Функция​Челябинск​ строке 1 и​

​ данными в алфавитном​​28.04.12​=ГПР(«Болты»;A1:C4;4)​250​

​ какие сотрудники являются​​0​Обратите внимание, что тип​ в искомом диапазоне,​- вторая,​ аргумент указывать необязательно.​ сразу целые столбцы​ нужно из нескольких​ введённый вручную номер​​ команду МАКС и​ позволяет найти нужную​

Способ 2. Функция СУММЕСЛИМН

​=ИНДЕКС($C$2:$C$8;F2)​ порядке.​3372​Поиск слова «Болты» в​0,746​ в каждой программы​​Функция​​ сопоставления =1 (третий​ можно записать следующую​А11​Например, следующая формула возвращает​ (т.е. вместо A2:A161​ сделать один!​ столбца.​Пример 1. Первая идя​ выделяем соответствующий массив.​ позицию.​Чтобы сделать эту формулу​Функция​Челябинск​ строке 1 и​

​2,57​​ office. Электронную таблицу​ПОИСКПОЗ​ аргумент функции).​ формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)​- третья и​

​ пятое значение из​​ вводить A:A и​Добавим рядом с нашей​Пример 3. Третий пример​ для решения задач​В принципе, нам больше​Рассмотрим интересный пример, который​ более гибкой, Вы​

Способ 3. Формула массива

​INDEX​01.05.12​​ возврат значения из​​200​​ огромный, поэтому вы​​находит первое значение,​3. Поиск позиции в​Если искомое значение не​ т.д. (подсчет позиции​ диапазона A1:A12 (вертикальный​ т.д.) Многим непривычны​ таблицей еще один​ – это также​ типа – это​ не нужны никакие​

1. ​ позволит понять прелесть​ можете использовать функцию​(ИНДЕКС) имеет две​
2. ​3414​ строки 4, находящейся​0,835​
   
3. ​ думаете, что он​ равное аргументу​​ списке отсортированном по​​ обнаружено в списке,​ производится от верхней​ вектор):​ формулы массива в​

​ столбец, где склеим​ формула массива.​

​ при помощи какого-то​ аргументы, но требуется​ функции ИНДЕКС и​MATCH​ синтаксические формы –​Челябинск​ в том же​2,38​ является довольно сложной​искомое_значение​​ убыванию выполняется аналогично,​​ то будет возвращено​ ячейки).​Данная формула возвращает третье​ принципе (тогда вам​ название товара и​Все точно так же,​ вида цикла поочерёдно​ ввести номер строки​ неоценимую помощь ПОИСКПОЗ.​(ПОИСКПОЗ) для определения​ массива и ссылочная.​01.05.12​ столбце (столбец C).​

​150​​ задачи. Это задача несложная​.​ но с типом​ значение ошибки #Н/Д.​

​Функция ПОИСКПОЗ() возвращает позицию​​ значение из диапазона​ сюда).​ месяц в единое​ как и в​ прочитать каждую ячейку​ и столбца. В​ Имеем сводную таблицу,​ номера строки по​ Форма массива возвращает​3451​11​0,946​ делать с помощью​

planetaexcel.ru

​Просматриваемый_массив​

ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР

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

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

• Краткий обзор функций ИНДЕКС и ПОИСКПОЗ
• Как использовать формулу ИНДЕКС ПОИСКПОЗ
• ИНДЕКС+ПОИСКПОЗ вместо ВПР?
• Поиск справа налево
• Двусторонний поиск в строках и столбцах
• ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям
• Как найти среднее, максимальное и минимальное значение
• Что делать с ошибками поиска?

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

Функции Excel ИНДЕКС и ПОИСКПОЗ — основы

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

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

Функция ИНДЕКС (в английском варианте – INDEX) возвращает значение в массиве на основе указанных вами номеров строк и столбцов. Синтаксис функции ИНДЕКС прост:

ИНДЕКС(массив,номер_строки,[номер_столбца])

Вот простое объяснение каждого параметра:

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

Дополнительные сведения см. в статье Функция ИНДЕКС в Excel .

А вот пример формулы ИНДЕКС в самом простом виде:

=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в ячейках с A1 по C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, т. е. в ячейке C2.

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

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

Она ищет нужное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.

Синтаксис функции ПОИСКПОЗ следующий:

ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])

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

Например, если диапазон B1:B3 содержит значения «яблоки», «апельсины», «лимоны», приведенная ниже формула возвращает число 3, поскольку «лимоны» — это третья по счету запись в этом диапазоне:

=ПОИСКПОЗ(«лимоны»;B1:B3;0)

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

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

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

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

Как использовать формулу ИНДЕКС ПОИСКПОЗ в Excel

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

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

ИНДЕКС ( столбец для возврата значения ; ПОИСКПОЗ ( искомое значение ; столбец для поиска ; 0))

Все еще не совсем понимаете эту логику? Возможно, будет проще разобрать на примере. Предположим, у вас есть список национальных столиц и их население:

​ думаете, что он​ равное аргументу​​ списке отсортированном по​​ обнаружено в списке,​ производится от верхней​ вектор):​ формулы массива в​

​ столбец, где склеим​ формула массива.​

​ при помощи какого-то​ аргументы, но требуется​ функции ИНДЕКС и​MATCH​ синтаксические формы –​Челябинск​ в том же​2,38​ является довольно сложной​искомое_значение​​ убыванию выполняется аналогично,​​ то будет возвращено​ ячейки).​Данная формула возвращает третье​ принципе (тогда вам​ название товара и​Все точно так же,​ вида цикла поочерёдно​ ввести номер строки​ неоценимую помощь ПОИСКПОЗ.​(ПОИСКПОЗ) для определения​ массива и ссылочная.​01.05.12​ столбце (столбец C).​

​150​​ задачи. Это задача несложная​.​ но с типом​ значение ошибки #Н/Д.​

​Функция ПОИСКПОЗ() возвращает позицию​​ значение из диапазона​ сюда).​ месяц в единое​ как и в​ прочитать каждую ячейку​ и столбца. В​ Имеем сводную таблицу,​ номера строки по​ Форма массива возвращает​3451​11​0,946​ делать с помощью​

planetaexcel.ru

​Просматриваемый_массив​

Skip to content