Skip to content
В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в 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))
Все еще не совсем понимаете эту логику? Возможно, будет проще разобрать на примере. Предположим, у вас есть список национальных столиц и их население:
Чтобы найти население определенной столицы, скажем, Индии, используйте следующую формулу ПОИСКПОЗ ИНДЕКС:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(“Индия”;A2:A10;0))
Теперь давайте проанализируем, что на самом деле делает каждый компонент этой формулы:
- Функция ПОИСКПОЗ ищет искомое значение “Индия” в диапазоне A2:A10 и возвращает число 2, поскольку это слово занимает второе место в массиве поиска.
- Этот номер поступает непосредственно в аргумент номер_строки функции ИНДЕКС, предписывая вернуть значение из этой строки.
Таким образом, приведенная выше формула превращается в ИНДЕКС(C2:C10;2), которая означает, что нужно искать в ячейках от C2 до C10 и извлекать значение из второй ячейки в этом диапазоне, то есть из C3, потому что мы начинаем отсчет со второй строки.
Но указывать название города в формуле не совсем правильно, так как для каждого нового поиска придется корректировать эту формулу. Введите его в какую-нибудь отдельную ячейку, скажем, F1, укажите ссылку на ячейку для ПОИСКПОЗ, и вы получите формулу динамического поиска:
=ИНДЕКС(C2:C10;ПОИСКПОЗ(F1;A2:A10;0))
Важное замечание! Количество строк в аргументе массив функции ИНДЕКС должно совпадать с количеством строк в аргументе просматриваемый_массив в ПОИСКПОЗ, иначе формула выдаст неверный результат.
Вы спросите: «А почему бы нам просто не использовать обычную формулу ВПР? Какой смысл тратить время на то, чтобы разобраться в хитросплетениях ИНДЕКС ПОИСКПОЗ в Excel?»
Вот как это будет выглядеть:
=ВПР(F1; A2:C10; 3; 0)
Конечно, так проще. Но этот наш элементарный пример предназначен только для демонстрационных целей, чтобы вы поняли, как именно функции ИНДЕКС и ПОИСКПОЗ работают вместе. Действительно, ВПР была бы здесь более уместна. Другие примеры, которые вы найдёте ниже, покажут вам реальную силу этой комбинации, которая легко справляется со многими сложными задачами, когда ВПР будет бессильна.
ИНДЕКС+ПОИСКПОЗ вместо ВПР?
Решая, какую функцию использовать для вертикального поиска, большинство знатоков Excel сходятся во мнении, что ПОИСКПОЗ+ИНДЕКС намного лучше, чем ВПР. Однако многие до сих пор остаются с ВПР, во-первых, потому что это проще, а, во-вторых, потому что они не до конца понимают все преимущества использования формулы ПОИСКПОЗ ИНДЕКС в Excel. Без такого понимания никто не захочет тратить свое время на изучение более сложного синтаксиса.
Ниже я укажу на ключевые преимущества ИНДЕКС ПОИСКПОЗ перед ВПР, а уж вам решать, является ли это достойным дополнением к вашему арсеналу знаний в Excel.
4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР
- Поиск справа налево. Как известно любому образованному пользователю, ВПР не может искать влево. Это означает, что искомое значение всегда должно находиться в крайнем левом столбце таблицы. А извлекать нужное значение мы будем из столбца, который находится правее. ИНДЕКС+ПОИСКПОЗ может легко выполнять поиск влево! Здесь это показано в действии: Как выполнить поиск значения слева в Excel .
- Можно безопасно вставлять или удалять столбцы. Формулы ВПР не работают или выдают неверные результаты, когда новый столбец удаляется из таблицы поиска или добавляется в нее, поскольку синтаксис ВПР требует указания порядкового номера столбца, из которого вы хотите извлечь данные. Естественно, когда вы добавляете или удаляете столбцы, этот номер в формуле автоматически не меняется, а нужный столбец уже оказывается на новом месте.
С функциями ИНДЕКС и ПОИСКПОЗ вы указываете диапазон возвращаемых столбцов, а не номер одного из них. В результате вы можете вставлять и удалять столько столбцов, сколько хотите, не беспокоясь об обновлении каждой связанной с ними формулы.
- Нет ограничений на размер искомого значения. При использовании функции ВПР общая длина ваших критериев поиска не может превышать 255 символов, иначе вы получите ошибку #ЗНАЧ!. Таким образом, если ваш набор данных содержит длинные строки, ИНДЕКС ПОИСКПОЗ — единственное работающее решение.
- Более высокая скорость обработки. Если ваши таблицы относительно небольшие, вряд ли будет какая-то существенная разница в производительности Excel. Но если ваши рабочие листы содержат сотни или тысячи строк и, следовательно, сотни или тысячи формул, ИНДЕКС ПОИСКПОЗ будет работать намного быстрее, чем ВПР. Причина в том, что Excel будет обрабатывать только столбцы поиска и возврата, а не весь массив таблицы.
Влияние ВПР на производительность Excel может быть особенно заметным, если ваша книга содержит сложные формулы массива. Чем больше значений содержит ваш массив и чем больше формул массива содержится в книге, тем медленнее работает Excel.
ИНДЕКС ПОИСКПОЗ в Excel – примеры формул
Уяснив, почему все же стоит изучать ИНДЕКС ПОИСКПОЗ, давайте перейдем к самому интересному и посмотрим, как можно применить теоретические знания на практике.
Формула для поиска справа налево
Как уже упоминалось, ВПР не может получать значения слева от столбца поиска. Таким образом, если ваши значения поиска не находятся в самом левом столбце, нет никаких шансов, что формула ВПР принесет вам желаемый результат. Функция ПОИСКПОЗ ИНДЕКС в Excel более универсальна и не имеет особого значения, где расположены столбцы поиска и возврата.
Для этого примера мы добавим столбец «Ранг» слева от нашей основной таблицы и попытаемся выяснить, какое место занимает столица России по численности населения среди других перечисленных столиц.
Записав искомое значение в G1, используйте следующую формулу для поиска в C2:C10 и возврата соответствующего значения из A2:A10:
=ИНДЕКС(A2:A10; ПОИСКПОЗ(G1;C2:C10;0))
Совет. Если вы планируете использовать формулу ПОИСКПОЗ ИНДЕКС более чем для одной ячейки, обязательно зафиксируйте оба диапазона абсолютными ссылками (например, $A$2:$A$10 и $C$2:$C$10), чтобы они не изменялись при копировании формулы.
Двусторонний поиск в строках и столбцах
В приведенных выше примерах мы использовали ИНДЕКС ПОИСКПОЗ вместо классической функции ВПР, чтобы вернуть значение из точно указанного столбца. Но что, если вам нужно искать в нескольких строках и столбцах? То есть, сначала нужно найти подходящий столбец, а уж потом извлечь из него значение? Другими словами, что, если вы хотите выполнить так называемый матричный или двусторонний поиск?
Это может показаться сложным, но формула очень похожа на базовую функцию ПОИСКПОЗ ИНДЕКС в Excel, но с одним отличием.
Просто используйте две функции ПОИСКПОЗ, вложенных друг в друга: одну – для получения номера строки, а другую – для получения номера столбца.
ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))
А теперь, пожалуйста, взгляните на приведенную ниже таблицу и давайте составим формулу двумерного поиска, чтобы найти население (в миллионах) в данной стране за данный год.
С целевой страной в G1 (значение_поиска1) и целевым годом в G2 (значение_поиска2) формула принимает следующий вид:
=ИНДЕКС(B2:D11; ПОИСКПОЗ(G1;A2:A11;0); ПОИСКПОЗ(G2;B1:D1;0))
Как работает эта формула?
Всякий раз, когда вам нужно понять сложную формулу Excel, разделите ее на более мелкие части и посмотрите, что делает каждая отдельная функция:
ПОИСКПОЗ(G1;A2:A11;0); – ищет в A2:A11 значение из ячейки G1 («США») и возвращает его позицию, которая равна 3.
ПОИСКПОЗ(G2;B1:D1;0) – просматривает диапазон B1:D1, чтобы получить позицию значения из ячейки G2 («2015»), которая равна 3.
Найденные выше номера строк и столбцов становятся соответствующими аргументами функции ИНДЕКС:
ИНДЕКС(B2:D11, 3, 3)
В результате вы получите значение на пересечении 3-й строки и 3-го столбца в диапазоне B2:D11, то есть из D4. Несложно?
ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям
Если у вас была возможность прочитать наши материалы по ВПР в Excel, вы, вероятно, уже протестировали формулу для ВПР с несколькими условиями . Однако существенным недостатком этого подхода является необходимость добавления вспомогательного столбца. Хорошей новостью является то, что функция ПОИСКПОЗ ИНДЕКС в Excel также может выполнять поиск по нескольким условиям без изменения или реструктуризации исходных данных!
Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}
Примечание. Это формула массива , которую необходимо вводить с помощью сочетания клавиш Ctrl + Shift + Enter
.
Предположим, что в таблице ниже вы хотите найти значение на основе двух критериев: Покупатель и Товар.
Следующая формула ИНДЕКС ПОИСКПОЗ отлично работает:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; (F1=A2:A10) * (F2=B2:B10); 0))
Где C2:C10 — это диапазон, из которого возвращается значение, F1 — это критерий1, A2:A10 — это диапазон для сравнения с критерием 1, F2 — это критерий 2, а B2:B10 — это диапазон для сравнения с критерием 2.
Не забудьте правильно ввести формулу, нажав Ctrl + Shift + Enter, и Excel автоматически заключит ее в фигурные скобки, как показано на скриншоте ниже:
Рис5
Если вы не хотите использовать формулы массива, добавьте в формулу в F4 еще одну функцию ИНДЕКС и завершите ее ввод обычным нажатием Enter:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; ИНДЕКС((F1=A2:A10) * (F2=B2:B10); 0; 1); 0))
Разберем пошагово, как это работает.
Здесь используется тот же подход, что и в обычном сочетании ИНДЕКС ПОИСКПОЗ, где просматривается один столбец. Чтобы оценить несколько критериев, вы создаете два или более массива значений ИСТИНА и ЛОЖЬ, которые представляют совпадения и несовпадения для каждого отдельного критерия, а затем перемножаете соответствующие элементы этих массивов. Операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно и создает массив, в котором единицы соответствуют строкам, которые удовлетворяют всем условиям. Функция ПОИСКПОЗ со значением поиска 1 находит первую «1» в массиве и передает ее позицию в ИНДЕКС, которая возвращает значение в этой позиции из указанного столбца.
Вторая формула без массива основана на способности функции ИНДЕКС работать с массивами. Второй вложенный ИНДЕКС имеет 0 в номер_строки , так что он будет передавать весь массив столбцов в ПОИСКПОЗ.
Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ
Microsoft Excel имеет специальные функции для поиска минимального, максимального и среднего значения в диапазоне. Но что, если вам нужно получить значение из другой ячейки, связанной с этими значениями? Например, получить название города с максимальным населением или узнать товар с минимальными продажами? В этом случае используйте функцию МАКС , МИН или СРЗНАЧ вместе с ИНДЕКС ПОИСКПОЗ.
Максимальное значение.
Предположим, нам нужно в списке городов найти столицу с самым большим населением. Чтобы найти наибольшее значение в столбце С и вернуть соответствующее ему значение из столбца В, находящееся в той же строке, используйте эту формулу:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(МАКС(C2:C10); C2:C10; 0))
Скриншот с примером находится чуть ниже.
Минимальное значение
Теперь найдём город с самым маленьким населением в списке. Чтобы найти наименьшее число в столбце С и получить соответствующее ему значение из столбца В:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(МИН(C2:C10); C2:C10; 0))
Ближайшее к среднему
Теперь мы находим город, население которого наиболее близко к среднему значению. Чтобы вычислить позицию, наиболее близкую к среднему значению показателя, рассчитанному из D2:D10, и получить соответствующее значение из столбца C, используйте следующую формулу:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(СРЗНАЧ(C2:C10); C2:C10; -1 ))
В зависимости от того, как организованы ваши данные, укажите 1 или -1 для третьего аргумента (тип_совпадения) функции ПОИСКПОЗ:
- Если ваш столбец поиска (столбец D в нашем случае) отсортирован по возрастанию , поставьте 1. Формула вычислит наибольшее значение, которое меньше или равно среднему значению.
- Если ваш столбец поиска отсортирован по убыванию , введите -1. Формула вычислит наименьшее значение, которое больше или равно среднему значению.
- Если ваш массив поиска содержит значение , точно равное среднему, вы можете ввести 0 для точного совпадения. Никакой сортировки не требуется.
В нашем примере данные в столбце D отсортированы в порядке убывания, поэтому мы используем -1 для типа соответствия. В результате мы получаем «Токио», так как его население (13 189 000) является ближайшим, превышающим среднее значение (12 269 006).
Что делать с ошибками поиска?
Как вы, наверное, заметили, если формула ИНДЕКС ПОИСКПОЗ в Excel не может найти искомое значение, она выдает ошибку #Н/Д. Если вы хотите заменить это стандартное сообщение чем-то более информативным, оберните формулу ПОИСКПОЗ ИНДЕКС в функцию ЕСНД . Например:
=ЕСНД(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); “Не найдено”)
И теперь, если кто-то вводит значение, которое не существует в диапазоне поиска, формула явно сообщит пользователю, что совпадений не найдено:
Если вы хотите перехватывать все ошибки, а не только #Н/Д, используйте функцию ЕСЛИОШИБКА вместо ЕСНД:
=ЕСЛИОШИБКА(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); “Что-то пошло не так!”)
Пожалуйста, имейте в виду, что во многих ситуациях было бы не совсем правильно скрывать все такие ошибки, потому что они предупреждают вас о возможных проблемах в вашей формуле.
Итак, еще раз об основных преимуществах формулы ИНДЕКС ПОИСКПОЗ.
-
Возможен ли “левый” поиск?
-
Повлияет ли на результат вставка и удаление столбцов?
Вы можете вставлять и удалять столько столбцов, сколько хотите. На результат ИНДЕКС ПОИСКПОЗ это не повлияет.
-
Возможен ли поиск по строкам и столбцам?
Можно сначала найти подходящий столбец, а уж потом извлечь из него значение. Общий вид формулы:
ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))
Подробную инструкцию смотрите здесь. -
Как сделать поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям?
Можно выполнять поиск по двум или более условиям без добавления дополнительных столбцов. Вот формула массива, которая решит проблему:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}
Вот как можно использовать ИНДЕКС и ПОИСКПОЗ в Excel. Я надеюсь, что наши примеры формул окажутся полезными для вас.
Вот еще несколько статей по этой теме:
-
Главная
-
Инструкции
-
JavaScript
-
Методы поиска в массивах JavaScript
Поиск в массиве — довольно несложная задача для программиста. На ум сразу приходит перебор через цикл for или бинарный поиск в отсортированном массиве, для элементов которого определены операции «больше» и «меньше». Но, как и любой высокоуровневый язык программирования, JavaScript предлагает разработчику встроенные функции для решения различных задач. В этой статье мы рассмотрим четыре метода поиска в массивах JavaScript: find
, includes
, indexOf
и filter
.
indexOf
indexOf
— это функция поиска элемента в массиве. Этот метод с помощью перебора ищет искомый объект и возвращает его индекс или “-1”, если не находит подходящий.
Синтаксис
Функция имеет такой синтаксис:
Array.indexOf (search element, starting index)
где:
- Array — массив;
- search element — элемент, который мы ищем;
- starting index — индекс, с которого начинаем перебор. Необязательный аргумент, по умолчанию работа функции начинается с индекса “0”, т.е. метод проверяет весь Array. Если starting index больше или равен Array.length, то метод сразу возвращает “-1” и завершает работу.
Если starting index отрицательный, то JS трактует это как смещение с конца массива: при starting index = “-1” будет проверен только последний элемент, при “-2” последние два и т.д.
Практика
Опробуем метод на практике. Запустим такой код и проверим результаты его работы:
let ExampleArray = [1,2,3,1,'5', null, false, NaN,3];console.log("Позиция единицы", ExampleArray.indexOf(1) );
console.log("Позиция следующей единицы", ExampleArray.indexOf(1,2) );
console.log("Позиция тройки", ExampleArray.indexOf(3) );
console.log("Позиция тройки, если starting index отрицательный", ExampleArray.indexOf(3,-2) );
console.log("Позиция false", ExampleArray.indexOf(false) );
console.log("Позиция 5", ExampleArray.indexOf("5") );
console.log("Позиция NaN", ExampleArray.indexOf(NaN));
В результате работы этого кода мы получили такой вывод:
Позиция единицы 0
Позиция следующей единицы 3
Позиция тройки 2
Позиция тройки, если starting index отрицательный 8
Позиция false 6
Позиция 5 -1
Позиция NaN -1
indexOf
осуществляет поиск элемента в массиве слева направо и останавливает свою работу на первом совпавшем. Отчетливо это проявляется в примере с единицей. Для того, чтобы идти справа налево, используйте метод LastIndexOf
с аналогичным синтаксисом.
Для сравнения искомого и очередного объекта применяется строгое сравнение (===). При использовании строгого сравнения для разных типов данных, но с одинаковым значение, например 5, ‘5’ и “5” JavaScript даёт отрицательный результат, поэтому IndexOf
не нашел 5.
Также стоит помнить, что indexOf
некорректно обрабатывает NaN. Так что для работы с этим значением нужно применять остальные методы.
includes
includes
не совсем проводит поиск заданного элемента в массиве, а проверяет, есть ли он там вообще. Работает он примерно также как и indexOf
. В конце работы includes
возвращает «True», если нашел искомый объект, и «False», если нет. Также includes
правильно обрабатывает NaN
Синтаксис
includes
имеет следующий синтаксис:
Array.includes (search element, starting index)
где:
- Array — массив;
- search element — элемент, который мы ищем;
- starting index — индекс, с которого начинаем перебор. Необязательный аргумент, по умолчанию работа функции начинается с индекса “0”, т.е. метод проверяет весь Array. Если starting index больше или равен Array.length, то метод сразу возвращает «False» и завершает работу.
Если starting index отрицательный, то JS трактует это как смещение с конца массива: при starting index = “-1” будет проверен только последний элемент, при “-2” последние два и т.д.
Практика
Немного изменим код из предыдущего примера и запустим его:
let Example = [1,2,3,1,'5', null, false,NaN, 3];console.log("Наличие единицы", Example.includes(1) );
console.log("Наличие следующей единицы", Example.includes(1,2) );
console.log("Наличие тройки", Example.includes(3) );
console.log("Наличие тройки, если starting index отрицательный", Example.includes(3,-1) );
console.log("Наличие false", Example.includes(false) );
console.log("Наличие 5", Example.includes(5) );
console.log("Наличие NaN", Example.includes(NaN));
Вывод:
Наличие единицы true
Наличие следующей единицы true
Наличие тройки true
Наличие тройки, если starting index отрицательный true
Наличие false true
Наличие 5 false
Наличие NaN true
Для includes
отсутствует альтернативная функция, которая проводит поиск по массиву js справа налево, которая, в общем-то, и не очень актуальна.
find
Предположим, что нам нужно найти в массиве некий объект. Но мы хотим найти его не по значению, а по его свойству. Например, поиск числа в массиве со значением между 15 и 20. Как и прежде, мы можем воспользоваться перебором с помощью for
, но это не слишком удобно. Для поиска с определенным условием в JavaScript существует метод find
.
Синтаксис
Array.find(function(...){
//если элемент соответствует условиям (true), то функция возвращает его и прекращает работу;
//если ничего не найдено, то возвращает undefined
})
- Array — массив;
- function(…) — функция, которая задает условия.
Практика
Как и в прошлых примерах, напишем небольшой код и опробуем метод:
let ExampleArray = ["Timeweb", 55555, "Cloud", "облачный провайдер", "буквы"];console.log(ExampleArray.find(element => element.length == 5))
Вывод:
Cloud
В этом примере мы искали строки с длиной в 5 символов. Для числовых типов данных длина не определена, поэтому 55555 не подходит. find
находит первый элемент и возвращает его, поэтому «буквы» также не попали в результат работы нашей функции. Для того, чтобы найти несколько элементов, соответствующих некоторому условию, нужно использовать метод filter
.
Также не стоит забывать о методе findIndex
. Он возвращает индекс подходящего элемента. Или -1, если его нет. В остальном он работает точно также, как и find
.
filter
find
ищет и возвращает первый попавшийся элемент, соответствующий условиям поиска. Для того, чтобы найти все такие элементы, необходимо использовать метод filter
. Результат этой функции — массив (если ничего не найдено, то он будет пустым).
Синтаксис
Array.find(function(...){
//если элемент соответствует условиям (true), то добавляем его к конечному результату и продолжаем перебор;
})
- Array — массив;
- function(…) — функция, которая задает условия.
Практика
Представим следующую задачу: у нас есть список кубоидов (прямоугольных параллелепипедов) с длинами их граней и нам нужно вывести все кубоиды с определенным объемом. Напишем код, реализующий решение данной задачи:
let ExampleArray = [
[10, 15, 8],
[11, 12, 6],
[5, 20, 1],
[10, 10, 2],
[16,2, 4]
];console.log(ExampleArray.filter(element=> element[0]*element[1]*element[2]>300))
Вывод:
[ [ 10, 15, 8 ],
[ 11, 12, 6 ] ]
В этом примере мы нашли прямоугольные параллелепипеды с объемом больше 300. В целом, метод filter
в JS позволяет реализовывать всевозможные условия для поиска.
Заключение
В этой статье узнали о методах поиска в JavaScript и научились ими пользоваться. Все перечисленные методы — универсальные инструменты для разработчиков. Но, как и любые универсальные инструменты, они не всегда являются самыми производительными. Так, например, бинарный поиск будет куда эффективнее, чем find
и filter
.
(PHP 4 >= 4.0.5, PHP 5, PHP 7, PHP 8)
array_search — Осуществляет поиск данного значения в массиве и возвращает
ключ первого найденного элемента в случае успешного выполнения
Описание
array_search(mixed $needle
, array $haystack
, bool $strict
= false
): int|string|false
Список параметров
-
needle
-
Искомое значение.
Замечание:
Если
needle
является строкой, сравнение
происходит с учётом регистра. -
haystack
-
Массив.
-
strict
-
Если третий параметр
strict
установлен в
true
, то функция array_search() будет искать
идентичные элементы вhaystack
.
Это означает, что также будут проверяться
типы
needle
вhaystack
,
а объекты должны быть одним и тем же экземпляром.
Возвращаемые значения
Возвращает ключ для needle
, если он был
найден в массиве, иначе false
.
Если needle
присутствует в
haystack
более одного раза, будет возвращён
первый найденный ключ. Для того, чтобы возвратить ключи для всех
найденных значений, используйте функцию array_keys()
с необязательным параметром search_value
.
Внимание
Эта функция может возвращать как логическое значение false
, так и значение не типа boolean, которое приводится к false
. За более подробной информацией обратитесь к разделу Булев тип. Используйте оператор === для проверки значения, возвращаемого этой функцией.
Примеры
Пример #1 Пример использования array_search()
<?php
$array = array(0 => 'blue', 1 => 'red', 2 => 'green', 3 => 'red');$key = array_search('green', $array); // $key = 2;
$key = array_search('red', $array); // $key = 1;
?>
Смотрите также
- array_keys() – Возвращает все или некоторое подмножество ключей массива
- array_values() – Выбирает все значения массива
- array_key_exists() – Проверяет, присутствует ли в массиве указанный ключ или индекс
- in_array() – Проверяет, присутствует ли в массиве значение
turabgarip at gmail dot com ¶
6 years ago
About searcing in multi-dimentional arrays; two notes on "xfoxawy at gmail dot com";
It perfectly searches through multi-dimentional arrays combined with array_column() (min php 5.5.0) but it may not return the values you'd expect.
<?php array_search($needle, array_column($array, 'key')); ?>
Since array_column() will produce a resulting array; it won't preserve your multi-dimentional array's keys. So if you check against your keys, it will fail.
For example;
<?php
$people = array(
2 => array(
'name' => 'John',
'fav_color' => 'green'
),
5=> array(
'name' => 'Samuel',
'fav_color' => 'blue'
)
);$found_key = array_search('blue', array_column($people, 'fav_color'));
?>
Here, you could expect that the $found_key would be "5" but it's NOT. It will be 1. Since it's the second element of the produced array by the array_column() function.
Secondly, if your array is big, I would recommend you to first assign a new variable so that it wouldn't call array_column() for each element it searches. For a better performance, you could do;
<?php
$colors = array_column($people, 'fav_color');
$found_key = array_search('blue', $colors);
?>
cue at openxbox dot com ¶
19 years ago
If you are using the result of array_search in a condition statement, make sure you use the === operator instead of == to test whether or not it found a match. Otherwise, searching through an array with numeric indicies will result in index 0 always getting evaluated as false/null. This nuance cost me a lot of time and sanity, so I hope this helps someone. In case you don't know what I'm talking about, here's an example:
<?php
$code = array("a", "b", "a", "c", "a", "b", "b"); // infamous abacabb mortal kombat code :-P
// this is WRONG
while (($key = array_search("a", $code)) != NULL)
{
// infinite loop, regardless of the unset
unset($code[$key]);
}
// this is _RIGHT_
while (($key = array_search("a", $code)) !== NULL)
{
// loop will terminate
unset($code[$key]);
}
?>
stefano@takys dot it ¶
12 years ago
for searching case insensitive better this:
<?php
array_search(strtolower($element),array_map('strtolower',$array));
?>
RichGC ¶
17 years ago
To expand on previous comments, here are some examples of
where using array_search within an IF statement can go
wrong when you want to use the array key thats returned.
Take the following two arrays you wish to search:
<?php
$fruit_array = array("apple", "pear", "orange");
$fruit_array = array("a" => "apple", "b" => "pear", "c" => "orange");
if (
$i = array_search("apple", $fruit_array))
//PROBLEM: the first array returns a key of 0 and IF treats it as FALSEif (is_numeric($i = array_search("apple", $fruit_array)))
//PROBLEM: works on numeric keys of the first array but fails on the secondif ($i = is_numeric(array_search("apple", $fruit_array)))
//PROBLEM: using the above in the wrong order causes $i to always equal 1if ($i = array_search("apple", $fruit_array) !== FALSE)
//PROBLEM: explicit with no extra brackets causes $i to always equal 1if (($i = array_search("apple", $fruit_array)) !== FALSE)
//YES: works on both arrays returning their keys
?>
thinbegin at gmail dot com ¶
5 years ago
Despite PHP's amazing assortment of array functions and juggling maneuvers, I found myself needing a way to get the FULL array key mapping to a specific value. This function does that, and returns an array of the appropriate keys to get to said (first) value occurrence.
function array_recursive_search_key_map($needle, $haystack) {
foreach($haystack as $first_level_key=>$value) {
if ($needle === $value) {
return array($first_level_key);
} elseif (is_array($value)) {
$callback = array_recursive_search_key_map($needle, $value);
if ($callback) {
return array_merge(array($first_level_key), $callback);
}
}
}
return false;
}
usage example:
-------------------
$nested_array = $sample_array = array(
'a' => array(
'one' => array ('aaa' => 'apple', 'bbb' => 'berry', 'ccc' => 'cantalope'),
'two' => array ('ddd' => 'dog', 'eee' => 'elephant', 'fff' => 'fox')
),
'b' => array(
'three' => array ('ggg' => 'glad', 'hhh' => 'happy', 'iii' => 'insane'),
'four' => array ('jjj' => 'jim', 'kkk' => 'kim', 'lll' => 'liam')
),
'c' => array(
'five' => array ('mmm' => 'mow', 'nnn' => 'no', 'ooo' => 'ohh'),
'six' => array ('ppp' => 'pidgeon', 'qqq' => 'quail', 'rrr' => 'rooster')
)
);
$search_value = 'insane';
$array_keymap = array_recursive_search_key_map($search_value, $nested_array);
var_dump($array_keymap);
// Outputs:
// array(3) {
// [0]=>
// string(1) "b"
// [1]=>
// string(5) "three"
// [2]=>
// string(3) "iii"
//}
----------------------------------------------
But again, with the above solution, PHP again falls short on how to dynamically access a specific element's value within the nested array. For that, I wrote a 2nd function to pull the value that was mapped above.
function array_get_nested_value($keymap, $array)
{
$nest_depth = sizeof($keymap);
$value = $array;
for ($i = 0; $i < $nest_depth; $i++) {
$value = $value[$keymap[$i]];
}
return $value;
}
usage example:
-------------------
echo array_get_nested_value($array_keymap, $nested_array); // insane
opencart dot ocfilter at gmail dot com ¶
1 year ago
Be careful!
<?php
var_dump
(array_search('needle', [ 0 => 0 ])); // int(0) (!)var_dump(array_search('needle', [ 0 => 0 ], true)); // bool(false)?>
But, in php 8
<?php
var_dump
(array_search('needle', [ 0 => 0 ])); // bool(false)?>
maciej at speccode dot com ¶
7 years ago
FYI, remember that strict mode is something that might save you hours.
If you're searching for a string and you have a "true" boolean on the way - you will get it as result (first occurrence). Example below:
<?php
$arr
= [
'foo' => 'bar',
'abc' => 'def',
'bool' => true,
'target' => 'xyz'
];var_dump( array_search( 'xyz', $arr ) ); //bool
var_dump( array_search( 'xyz', $arr, true ) ); //target?>
azaozz, gmail ¶
14 years ago
Expanding on the comment by hansen{}cointel.de:
When searching for a string and the array contains 0 (zero), the string is casted to (int) by the type-casting which is always 0 (perhaps the opposite is the proper behaviour, the array value 0 should have been casted to string). That produces unexpected results if strict comparison is not used:
<?php
$a = array(0, "str1", "str2", "str3");
echo "
str1 = ".array_search("str1", $a).",
str2 = ".array_search("str2", $a).",
str3 = ".array_search("str3", $a).",
str1 strict = "
.array_search("str1", $a, true).",
str2 strict = ".array_search("str2", $a, true).",
str3 strict = ".array_search("str3", $a, true);
?>
This will return:
str1 = 0, str2 = 0, str3 = 0, str1 strict = 1, str2 strict = 2, str3 strict = 3
codeslinger at compsalot dot com ¶
13 years ago
one thing to be very aware of is that array_search() will fail if the needle is a string and the array itself contains values that are mixture of numbers and strings. (or even a string that looks like a number)
The problem is that unless you specify "strict" the match is done using == and in that case any string will match a numeric value of zero which is not what you want.
-----
also, php can lookup an index pretty darn fast. for many scenarios, it is practical to maintain multiple arrays, one in which the index of the array is the search key and the normal array that contains the data.
<?php
$normal
[$index] = array('key'=>$key, 'data'=>'foo');
$inverse[$key] = $index;
//very fast lookup, this beats any other kind of search
if (array_key_exists($key, $inverse))
{
$index = $inverse[$key];
return $normal[$index];
}
?>
n-regen ¶
14 years ago
If you only know a part of a value in an array and want to know the complete value, you can use the following function:
<?php
function array_find($needle, $haystack)
{
foreach ($haystack as $item)
{
if (strpos($item, $needle) !== FALSE)
{
return $item;
break;
}
}
}
?>
The function returns the complete first value of $haystack that contains $needle.
andreas dot damm at maxmachine dot de ¶
15 years ago
Combining syntax of array_search() and functionality of array_keys() to get all key=>value associations of an array with the given search-value:
<?php
function array_search_values( $m_needle, $a_haystack, $b_strict = false){
return array_intersect_key( $a_haystack, array_flip( array_keys( $a_haystack, $m_needle, $b_strict)));
}
?>
Usage:
<?php
$array1 = array( 'pre'=>'2', 1, 2, 3, '1', '2', '3', 'post'=>2);
print_r( array_search_values( '2', $array1));
print_r( array_search_values( '2', $array1, true));
print_r( array_search_values( 2, $array1, true));
?>
Will return:
array(4) {
["pre"] =>
string(1) "2"
[1] =>
int(2)
[4] =>
string(1) "2"
["post"] =>
int(2)
}
array(2) {
["pre"] =>
string(1) "2"
[4] =>
string(1) "2"
}
array(2) {
[1] =>
int(2)
["post"] =>
int(2)
}
yasien dot dwieb at gmail dot com ¶
3 years ago
Beware when using array_search to a mix of string and integer where prefixes of keys may collide, as in my case I have encountered the following situation:
Assume you have the following array:
<?php
$arr = [
1 => 'index 0',
2 => 'index 1',
3 => 'index 2',
'3anothersuffix' => 'index 3'
];$index1 = array_search('3', array_keys($arr)); // 2
$index2 = array_search('3anothersuffix', array_keys($arr)); //2
?>
$index1 and $index2 will be the same
after using strict type search:
<?php
$index1 = array_search('3', array_keys($arr), true); // false
$index2 = array_search('3anothersuffix', array_keys($arr), true); //3
?>
it will not find $index1 at all while returning a correct value for $index2;
stooshie at gmail dot com ¶
11 years ago
Example of a recursive binary search that returns the index rather than boolean.
<?php
// returns the index of needle in haystack
function binSearch($needle, $haystack)
{
// n is only needed if counting depth of search
global $n;
$n++;
// get the length of passed array
$l = count($haystack);
// if length is 0, problem
if($l <= 0)
{
return -1;
}
// get the mid element
$m = (($l+($l%2))/2);
// if mid >= length (e.g. l=1)
if($m >= $l)
{
$m = $m-1;
}
// get the indexed element to compare to the passed element and branch accordingly
$compare = $haystack[$m];
switch(true)
{
case($compare>$needle):
{
// recurse on the lower half
$new_haystack = array_slice($haystack, 0, $m);
$c = count($new_haystack);
$r = binSearch($needle, $new_haystack);
// return current index - (length of lower half - found index in lower half)
return $m - ($c - $r);
break;
}
case($compare<$needle):
{
// recurse on the upper half
$new_haystack = array_slice($haystack, $m, ($l-$m));
$c = count($new_haystack);
$r = binSearch($needle, $new_haystack);
// return current position + found index in upper half
return $m + $r;
break;
}
case($compare==$needle):
{
// found it, so return index
return $m;
break;
}
}
}
?>
helenadeus at gmail dot com ¶
14 years ago
I was trying to use array_search to retrieve all the values that match a given needle, but it turns out only the first match key is returned. I built this little function, which works just like array_search, but returns all the keys that match a given needle instead. The output is an array.
<?php
$haystack
= array('a','b','a','b');$needle = 'a';print_r(array_search_all($needle, $haystack));//Output will be
// Array
// (
// [0]=>1
// [1]=>3
// )function array_search_all($needle, $haystack)
{#array_search_match($needle, $haystack) returns all the keys of the values that match $needle in $haystackforeach ($haystack as $k=>$v) {
if(
$haystack[$k]==$needle){$array[] = $k;
}
}
return ($array);
}
?>
nordseebaer at gmx dot de ¶
3 years ago
It's really important to check the return value is not false! I used array_search() to determine the index of an value to unset this value and then realized that $arr[false] === $arr[0] !
<?php
$arr = ['apple', 'banana'];var_dump($arr[0] === 'apple'); // true
var_dump($arr[false] === $arr[0]); // true
var_dump($arr[false] === 'apple'); // trueunset($arr[array_search('banana', $arr)]); //index = 1
var_dump($arr);// result
// array(1) {
// [0]=>
// string(5) "apple"
// }unset($arr[array_search('peach', $arr)]); //not found, result is false
var_dump($arr);// result
// array(0) {
// }
// because $arr[false] === $arr[0]
?>
So always check the return of array_search!
kermes [at] thesevens [dot] net ¶
15 years ago
A variation of previous searches that returns an array of keys that match the given value:
<?php
function array_ksearch($array, $str)
{
$result = array();
for($i = 0; $i < count($array); next($array), $i++)
if(strtolower(current($array)) == strtolower($str))
array_push($result, key($array);
return
$result;
}
?>
Usage would be as follows:
<?php
$testArray = array('one' => 'test1', 'two' => 'test2', 'three' => 'test1', 'four' => 'test2', 'five' => 'test1');
print_r(array_ksearch($testArray, 'test1'));
?>
Время на прочтение
16 мин
Количество просмотров 236K
Добрый день, уважаемые хаброжители!
Время от времени некоторым (а может и более, чем некоторым) из нас приходится сталкиваться с задачами по обработке небольших массивов данных, начиная от составления и анализа домашнего бюджета и заканчивая какими-либо расчетами по работе, учебе и т.д. Пожалуй, наиболее подходящим инструментом для этого является Microsoft Excel (или возможно иные его аналоги, но они менее распространены).
Поиск выдал мне всего одну статью на Хабре по схожей тематике — «Талмуд по формулам в Google SpreadSheet». В ней дано хорошее описание базовых вещей для работы в excel (хотя он и не 100% про сам excel).
Таким образом, накопив определенный пул запросов/задач, появилась идея их типизировать и предложить возможные решения (пусть не все возможные, но быстро дающие результат).
Речь пойдет о решении наиболее распространенных задач, с которыми сталкиваются пользователи.
Описание решений построено следующим образом – дается кейс, содержащий исходное задание, которое постепенно усложняется, к каждому шагу дано развернутое решение с пояснениями. Наименования функций будут даваться на русском языке, но в скобках при первом упоминании будет приводиться оригинальное наименование на английском языке (т.к. по опыту у подавляющего большинства пользователей установлена русскоязычная версия).
Кейс_1: Логические функции и функции поиска совпадений
«У меня есть набор значений в табличке и необходимо что бы при выполнении определенного условия/набора условий выводилось определенное значение» (с) Пользователь
Данные, как правило, представлены в табличной форме:
Условие:
- если значение в столбце «Количество» больше 5,
- то нужно вывести в колонке «Результат» значение «Заказ не требуется»,
В этом нам поможет формула «ЕСЛИ» (IF), которая относится к логическим формулам и может выдавать в решении любые значения, которые мы заранее записываем в формуле. Обращаю внимание, что любые текстовые значения записываются, используя кавычки.
Синтаксис формулы следующий:
ЕСЛИ(лог_выражение, [значение_если_истина], [значение_если_ложь])
- Лог_выражение — выражение, дающее в результате значение ИСТИНА или ЛОЖЬ.
- Значение_если_истина — значение, которое выводится, если логическое выражение истинно
- Значение_если_ложь — значение, которое выводится, если логическое выражение ложно
Синтаксис формулы для решения:
Вывод результата в ячейку D2:
=ЕСЛИ(C5>5;«Заказ не требуется»;«Необходим заказ»)
На выходе получаем результат:
Бывает, что условие носит более сложный характер, например выполнение 2-х и более условий:
- если значение в столбце «Количество» больше 5, а значение в колонке «Тип» равно «А»
- то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».
В данном случае мы уже не можем ограничиться использованием одной только формулы «ЕСЛИ», необходимо добавить в ее синтаксис другую формулу. И это будет еще одна логическая формула «И» (AND).
Синтаксис формулы следующий:
И(логическое_значение1, [логическое_значение2], …)
- Логическое_значение1-2 и т.д. — проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ
Синтаксис решения будет следующим:
Вывод результата в ячейку D2:
=ЕСЛИ(И(C2>5;B2=«А»);1;0)
Таким образом, используя сочетание 2-х формул, мы находим решение нашей задачи и получаем результат:
Попробуем усложнить задачу – новое условие:
- если значение в столбце «Количество» равно 10, а значение в колонке «Тип» равно «А»
- или же значение в столбце «Количество» больше или равно 5, а значение «Тип» равен «Б»
- то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».
Синтаксис решения будет следующим:
Вывод результата в ячейку D2:
=ЕСЛИ(ИЛИ(И(C2=10;B2=«А»); И(C2>=5;B2=«Б»));1;0)
Как видно из записи, в формулу «ЕСЛИ» включено одно условие «ИЛИ» (OR) и два условия с использованием формулы «И», включенных в него. Если хотя бы одно из условий 2-го уровня имеет значение «ИСТИНА», то в колонку «Результат» будет выведен результат «1», в противном случае будет «0».
Результат:
Теперь перейдем к следующей ситуации:
Представим, что в зависимости от значения в столбце «Условие» должно выводиться определенное условие в столбце «Результат», ниже приведено соответствие значений и результата.
Условие:
- 1 = А
- 2 = Б
- 3 = В
- 4 = Г
При решении задачи с помощью функции «ЕСЛИ», синтаксис будет следующим:
Вывод результата в ячейку B2:
=ЕСЛИ(A2=1;«А»; ЕСЛИ(A2=2;«Б»; ЕСЛИ(A2=3;«В»; ЕСЛИ(A2=4;«Г»;0))))
Результат:
Как видно, написание подобной формулы не только не очень удобно и громоздко, но и может занять некоторое время на ее редактирование у неопытного пользователя в случае ошибки.
Минус подобного подхода в том, что он применим для небольшого количества условий, ведь, все их придется набирать вручную и «раздувать» нашу формулу до больших размеров, однако подход отличает полная «всеядность» к значениям и универсальность использования.
Альтернативное решение_1:
Использование формулы «ВЫБОР» (CHOOSE),
Синтаксис функции:
ВЫБОР(номер_индекса, значение1, [значение2], …)
- Номер_индекса — номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.
- Значение1, значение2,… — значение от 1 до 254 аргументов-значений, из которых функция «ВЫБОР», используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.
При ее использовании, мы сразу заносим результаты условий в зависимости от указанных значений.
Условие:
- 1 = А
- 2 = Б
- 3 = В
- 4 = Г
Синтаксис формулы:
=ВЫБОР(A2;«А»;«Б»;«В»;«Г»)
Результат аналогичен решению с цепочкой функций «ЕСЛИ» выше.
При применении этой формулы существуют следующие ограничения:
В ячейку «А2» (номер индекса) могут быть указаны только цифры, а значения результата будут выводиться в порядке возрастания от 1 до 254 значений.
Иными словами, функция будет работать только если в ячейке «А2» указаны цифры от 1 до 254 в порядке возрастания и это накладывает определенные ограничения при использовании этой формулы.
Т.е. если мы захотим, что бы значение «Г» выводилось при указании числа 5,
- 1 = А
- 2 = Б
- 3 = В
- 5 = Г
то формула будет иметь следующий синтаксис:
Вывод результата в ячейку B2:
=ВЫБОР(A31;«А»;«Б»;«В»;;«Г»)
Как видно, значение «4» в формуле нам приходится оставить пустым и перенести результат «Г» на порядковый номер «5».
Альтернативное решение_2:
Вот мы и подошли к одной из самых популярных функций Excel, овладение которой автоматически превращает любого офисного работника в «опытного пользователя excel» /sarcasm/.
Синтаксис формулы:
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
- Искомое_значение – значение, поиск которого осуществляется функцией.
- Таблица – диапазон ячеек, содержащий данные. Именно в этих ячейках будет происходить поиск. Значения могут быть текстовыми, числовыми или логическими.
- Номер_столбца — номер столбца в аргументе «Таблица», из которого будет выводиться значение в случае совпадения. Важно понимать, что отсчет столбцов происходит не по общей сетке листа (A.B,C,D и т.д.), а внутри массива, указанного в аргументе «Таблица».
- Интервальный_просмотр — определяет, какое совпадение должна найти функция — точное или приблизительное.
Важно: функция «ВПР» ищет совпадение только по первой уникальной записи, если искомое_значение присутствует в аргументе «Таблица» несколько раз и имеет разные значения, то функция «ВПР» найдет только самое ПЕРВОЕ совпадение, результаты по всем остальным совпадениям показаны не будутИспользование формулы «ВПР» (VLOOKUP) связано с еще одним подходом в работе с данными, а именно с формированием «справочников».
Суть подхода в создании «справочника» соответствия аргумента «Искомое_значение» определенному результату, отдельно от основного массива, в котором прописываются условия и соответствующие им значения:
Затем в рабочей части таблицы уже прописывается формула со ссылкой на справочник, заполненный ранее. Т.е. в справочнике в столбце «D» происходит поиск значения из столбца «А» и при нахождении соответствия выводится значение из столбца «Е» в столбец «В».
Синтаксис формулы:
Вывод результата в ячейку B2:
=ВПР(A2;$D$2:$E$5;2;0)
Результат:
Теперь представим ситуацию, когда необходимо подтянуть данные в одну таблицу из другой, при этом таблицы не идентичны. См. пример ниже
Видно, что строки в столбцах «Продукт» обеих таблиц не совпадают, однако, это не является препятствием для использования функции «ВПР».
Вывод результата в ячейку B2:
=ВПР($A3;$H$3:$M$6;2;0)
Но при решении сталкиваемся с новой проблемой – при «протягивании» написанной нами формулы вправо от столбца «В» до столбца «Е», нам придется вручную заменять аргумент «номер_столбца». Дело это трудоемкое и неблагодарное, потому, на помощь нам приходит другая функция — «СТОЛБЕЦ» (COLUMN).
Синтаксис функции:
СТОЛБЕЦ([ссылка])
- Ссылка — ячейка или диапазон ячеек, для которых требуется возвратить номер столбца.
Если использовать запись типа:
=СТОЛБЕЦ()
то функция выведет номер текущего столбца (в ячейке которого написана формула).
В результате получается число, которое можно использовать в функции «ВПР», чем мы и воспользуемся и получаем следующую запись формулы:
Вывод результата в ячейку B2:
=ВПР($A3;$H$3:$M$6; СТОЛБЕЦ();0)
Функция «СТОЛБЕЦ» определит номер текущего столбца, который будет использоваться аргументом «Номер_столбца» для определения номера столбца поиска в справочнике.
Кроме того, можно использовать конструкцию:
=СТОЛБЕЦ()-1
Вместо числа «1» можно использовать любое число (а также не только вычитать его, но и прибавлять к полученному значению), для получения желаемого результата, если нет желания ссылаться на определенную ячейку в столбце с нужным нам номером.
Получившийся результат:
Продолжаем развивать тему и усложняем условие: представим, что у нас есть два справочника с разными данными по продуктам и необходимо вывести в таблицу с результатом значения в зависимости от того, какой тип справочника указан в колонке «Справочник»
Условие:
- Если в столбце «Справочник» указано число 1, данные должны тянуться из таблицы «Справочник_1», если число 2, то из таблицы «Справочник_2» в соответствии с указанным месяцем
Вариант решения, который сразу приходит на ум, следующий:
Вывод результата в ячейку C3:
=ЕСЛИ($B3=1; ВПР($A3;$G$3:$I$6; СТОЛБЕЦ()-1;0); ВПР($A3;$K$3:$M$6; СТОЛБЕЦ()-1;0))
Плюсы: наименование справочника может быть любым (текст, цифры и их сочетание), минусы – плохо подходит, если вариантов более 3-х.
Если же номера справочников всегда представляют собой числа, имеет смысл использовать следующее решение:
Вывод результата в ячейку C3:
=ВПР($A3; ВЫБОР($B3;$G$3:$I$6;$K$3:$M$6); СТОЛБЕЦ()-1;0)
Плюсы: формула может включать до 254 наименований справочников, минусы – их наименование должно быть строго числовым.
Результат для формулы с использованием функции «ВЫБОР»:
Бонус: ВПР по двум и более признакам в аргументе «искомое_значение».
Условие:
- Представим, что у нас как всегда есть массив данных в табличной форме (если нет, то мы к нему приводим данные), из массива по определенным признакам необходимо получить значения и поместить их в другую табличную форму.
Обе таблицы приведены ниже:
Как видно из табличных форм, каждая позиция имеет не только наименование (которое не является уникальным), но также и относится к определенному классу и имеет свой вариант фасовки.
Используя сочетание имени и класса и фасовки, мы можем создать новый признак, для этого в таблице с данными создаем дополнительный столбец «Доп.признак», который заполняем при помощи следующей формулы:
=H3&”_”&I3&”_”&J3
Используя символ «&», объединяем три признака в один (разделитель между словами может быть любым, как и не быть вовсе, главное использовать аналогичное правило и для поиска)
Аналогом формулы может быть функция «СЦЕПИТЬ» (CONCATENATE), в этом случае она будет выглядеть следующим образом:
=СЦЕПИТЬ(H3;”_”;I3;”_”;J3)
После того, как дополнительный признак создан для каждой записи в таблице с данными, приступаем к написанию функции поиска по этому признаку, которая будет иметь вид:
Вывод результата в ячейку D3:
=ЕСЛИОШИБКА(ВПР(A2&”_”&B2&”_”&C2;$G$2:$K$6;5;0);0)
В функции «ВПР» в качестве аргумента «искомое_значение» используем все ту же связку трех признаков (наименование_класс_фасовка), но берем ее уже в таблице для заполнения и заносим непосредственно в аргумент (как вариант, можно было бы выделить значение для аргумента в дополнительный столбец в таблице для заполнения, но это действие будет излишним).
Напоминаю, что использование функции «ЕСЛИОШИБКА» (IFERROR) необходимо, если искомое значение так и не будет найдено, и функция «ВПР» выведет нам значение «#Н/Д» (об этом ниже).
Результат на картинке ниже:
Данный прием можно использовать и для большего количества признаков, единственное условие – уникальность получаемых комбинаций, если она не соблюдается, то результат будет некорректным.
Кейс_3 Поиск значения в массиве, или когда ВПР не в силах нам помочь
Рассмотрим ситуацию, когда необходимо понять, есть ли в массиве ячеек нужные нам значения.
Задача:
- в столбце «Условие поиска» указано значение и необходимо определить, присутствует ли оно в столбце «Массив для поиска»
Визуально все выглядит в следующем виде:
Как мы видим, функция «ВПР» тут бессильна, т.к. мы ищем не точное совпадение, а именно наличие в ячейке нужного нам значения.
Для решения задачи необходимо использовать комбинацию нескольких функций, а именно:
«ЕСЛИ»
«ЕСЛИОШИБКА»
«СТРОЧН»
«НАЙТИ»
По порядку обо всех, «ЕСЛИ» мы уже разобрали ранее, потому перейдем к функции «ЕСЛИОШИБКА» (IFERROR)
ЕСЛИОШИБКА(значение, значение_при_ошибке)
- Значение — аргумент, проверяемый на возникновение ошибок.
- Значение_при_ошибке — значение, возвращаемое при ошибке при вычислении по формуле. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!.
Важно: данная формула практически всегда обязательна при работе с массивами информации и справочниками, т.к. зачастую бывает, что искомое значение не находится в справочнике и в этом случае функция возвращает ошибку. Если же в ячейке выводится ошибка и ячейка участвует, например, в вычислении, то оно так же произойдет с ошибкой. Плюс ко всему, ячейкам, где формула возвратила ошибку можно присваивать различные значения, которые облегчают их статистическую обработку. Также, в случае ошибки можно выполнять другие функции, что очень удобно при работе с массивами и позволяет строить формулы с учетом довольно разветвленных условий.
«СТРОЧН» (LOWER)
СТРОЧН(текст)
- Текст — текст, преобразуемый в нижний регистр.
Важно: функция «СТРОЧН» не заменяет знаки, не являющиеся буквами.
Роль в формуле: поскольку функция «НАЙТИ» (FIND) осуществляет поиск и учетом регистра текста, то необходимо привести весь текст к одному регистру, в противном случае «чАй» будет не равно «чай» и т.д. Это актуально, если значение регистра не является условием поиска и отбора значений, в противном случае формулу «СТРОЧН» можно не использовать, так поиск будет более точным.
Теперь подробнее о синтаксисе функции «НАЙТИ» (FIND).
НАЙТИ(искомый_текст, просматриваемый_текст, [нач_позиция])
- Искомый_текст — текст, который необходимо найти.
- Просматриваемый_текст — текст, в котором нужно найти искомый текст.
- Нач_позиция — знак, с которого нужно начать поиск. Первый знак в тексте «просматриваемый_текст» имеет номер 1. Если номер не указан, он по умолчанию считается равным 1.
Синтаксис формулы-решения будет иметь вид:
Вывод результата в ячейку B2:
=ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ(СТРОЧН(A2); СТРОЧН(E2);1);0)=0;«fail»;«bingo!»)
Разберем логику формулы по действиям:
- СТРОЧН(A2) – преобразует аргумент «Искомый_текст» в ячейке в А2 в текст с нижним регистром
- Функция «НАЙТИ» начинает поиск преобразованного аргумента «Искомый_текст» в массиве «Просматриваемый_текст», который преобразовывается функцией «СТРОЧН(E2)», также в текст с нижним регистром.
- В случае если, функция находит совпадение, т.е. возвращает порядковый номер первого символа совпадающего слова/значения, срабатывает условие ИСТИНА в формуле «ЕСЛИ», т.к. полученное значение не равно нулю. Как результат, в столбце «Результат» будет выведено значение «Bingo!»
- Если же, функция не находит совпадение т.е. порядковый номер первого символа совпадающего слова/значения не указывается и вместо значения возвращается ошибка, срабатывает условие, заложенное в формулу «ЕСЛИОШИБКА» и возвращается значение равное «0», что соответствует условию ЛОЖЬ в формуле «ЕСЛИ», т.к. полученное значение равно «0». Как результат, в столбце «Результат» будет выведено значение «fail».
Как видно из рисунка выше, благодаря функциям «СТРОЧН» и «НАЙТИ» мы находим искомые значения вне зависимости от регистра символов, и места нахождения в ячейке, но необходимо обратить внимание на строку 5.
Условие поиска задано как «111», но в массиве поиска указано значение «1111111 печенюшки», однако формула выдает результат «Bingo!». Это происходит потому, что значение «111» входит в ряд значений «1111111», как следствие находится совпадение. В обратном случае данное условие не сработает.
Кейс_4 Поиск значения в массиве по нескольким условиям, или когда ВПР тем более не в силах нам помочь
Представим ситуацию, когда необходимо найти значение из «Таблица с результатом» в двумерном массиве «Справочник» по нескольким условиям, а именно по значению «Наименование» и «Месяц».
Табличная форма задания будет иметь следующий вид:
Условие:
- В таблицу с результатом необходимо подтянуть данные в соответствии с совпадением условий «Наименование» и «Месяц».
Для решения подобной задачи подойдет комбинация функций «ИНДЕКС» и «ПОИСКПОЗ»
Синтаксис функции «ИНДЕКС» (INDEX)
ИНДЕКС(массив, номер_строки, [номер_столбца])
- Массив — диапазон ячеек, из которого будут показываться значения в случае совпадения условий их поиска.
- Если массив содержит только одну строку или один столбец, аргумент «номер_строки» или «номер_столбца» соответственно не является обязательным.
- Если массив занимает больше одной строки и одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, то функция «ИНДЕКС» возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».
- Номер_строки — номер строки в массиве, из которой требуется возвратить значение.
- Номер_столбца — номер столбца в массиве, из которого требуется возвратить значение.
Иными словами функция возвращает из указанного массива в аргументе «Массив» значение, которое находится на пересечении координат, указанных в аргументах «Номер_строки» и «Номер_столбца».
Синтаксис функции «ПОИСКПОЗ» (MATCH)
ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
- Искомое_значение — значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.
- Просматриваемый_массив — диапазон ячеек, в которых производится поиск.
- Тип_сопоставления — необязательный аргумент. Число -1, 0 или 1.
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне.
Суть использования комбинации функций «ИНДЕКС» и «ПОИСКПОЗ» в том, то мы производим поиск координат значений по их наименованию по «осям координат».
Осью Y будет столбец «Наименование», а осью X – строка «Месяцы».
часть формулы:
ПОИСКПОЗ($A4;$I$4:$I$7;0)
возвращает число по оси Y, в данном случае оно будет равно 1, т.к. значение «А» присутствует в искомом диапазоне и имеет относительную позицию «1» в этом диапазоне.
часть формулы:
ПОИСКПОЗ(B$3;$J$3:$L$3;0)
возвращает значение #Н/Д, т.к. значение «1» отсутствует в просматриваемом диапазоне.
Таким образом, мы получили координаты точки (1; #Н/Д) которые функция «ИНДЕКС» использует для поиска в аргументе «Массив».
Полностью написанная функция для ячейки B4 будет иметь следующий вид:
=ИНДЕКС($J$4:$L$7; ПОИСКПОЗ($A4;$I$4:$I$7;0); ПОИСКПОЗ(B$3;$J$3:$L$3;0))
По сути, если бы мы знали координаты нужного нам значения, функция выглядела бы следующим образом:
=ИНДЕКС($J$4:$L$7;1;#Н/Д))
Поскольку, аргумент «Номер_столбца» имеет значение «#Н/Д», то результат для ячейки «B4» будет соответствующий.
Как видно из получившегося результата не все значения в таблице с результатом находят совпадение со справочником и в итоге мы видим, что часть значений в таблице выводится в виде «#Н/Д», что затрудняет использование данных для дальнейших расчетов.
Результат:
Что бы нейтрализовать этот негативный эффект используем функцию «ЕСЛИОШИБКА», о которой мы читали ранее, и заменяем значение, возвращающееся при ошибке на «0», тогда формула будет иметь вид:
Вывод результата в ячейку B4:
=ЕСЛИОШИБКА(ИНДЕКС($J$4:$L$7; ПОИСКПОЗ($A4;$I$4:$I$7;0); ПОИСКПОЗ(B$3;$J$3:$L$3;0));0)
Демонстрация результата:
Как видно на картинке, значения «#Н/Д» более не мешают нам в последующих вычислениях с использованием значений в таблице с результатом.
Кейс_5 Поиск значения в диапазоне чисел
Представим, что нам необходимо дать определенный признак числам, входящим в определенный диапазон.
Условие:
В зависимости от стоимости продукта ему должна присваиваться определенная категория
Если значение находится в диапазоне
- От 0 до 1000 = А
- От 1001 до 1500 = Б
- От 1501 до 2000 = В
- От 2001 до 2500 = Г
- Более 2501 = Д
Функция ПРОСМОТР (LOOKUP) возвращает значение из строки, столбца или массива. Функция имеет две синтаксических формы: векторную и форму массива.
ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])
- Искомое_значение — значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
- Просматриваемый_вектор — диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.
- Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: …, -2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
- Вектор_результатов — диапазон, состоящий из одной строки или столбца. Вектор_результатов должен иметь тот же размер, что и просматриваемый_вектор.
Вывод результата в ячейку B3:
=ПРОСМОТР(E3;$A$3:$A$7;$B$3:$B$7)
Аргументы «Просматриваемый_вектор» и «Вектор_результата» можно записать в форме массива – в этом случае не придется выводить их в отдельную таблицу на листе Excel.
В этом случае функция будет выглядеть следующим образом:
Вывод результата в ячейку B3:
=ПРОСМОТР(E3;{0;1001;1501;2001;2501};{«А»;«Б»;«В»;«Г»;«Д»})
Кейс_6 Суммирование чисел по признакам
Для суммирования чисел по определенным признакам можно использовать три разных функции:
СУММЕСЛИ (SUMIF) – суммирует только по одному признаку
СУММЕСЛИМН (SUMIFS) – суммирует по множеству признаков
СУММПРОИЗВ (SUMPRODUCT) – суммирует по множеству признаков
Существует также вариант с использованием «СУММ» (SUM) и функции формулы массивов, когда формула «СУММ» возводится в массив:
({=СУММ(()*())}
но такой подход довольно неудобен и полностью перекрывается по функционалу формулой «СУММПРОИЗВ»
Теперь подробнее по синтаксису «СУММПРОИЗВ»:
СУММПРОИЗВ(массив1, [массив2], [массив3],…)
- Массив1 — первый массив, компоненты которого нужно перемножить, а затем сложить результаты.
- Массив2, массив3… — от 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.
Условие:
- Найти общую сумму по стоимости отгрузок по каждому из продуктов за определенный период:
Как видно из таблицы с данными, что бы посчитать стоимость необходимо цену умножить на количество, а полученное значение, применив условия отбора переносить в таблица с результатом.
Однако, формула «СУММПРОИЗ» позволяет проводить такие расчеты внутри формулы.
Вывод результата в ячейку B4:
=СУММПРОИЗВ(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11<C$3);($M$3:$M$11)*($L$3:$L$11))
Разберем формулу по частям:
($A4=$H$3:$H$11)
– задаем условие по отбору в столбце «Наименование» таблицы с данными по столбцу «Наименование» в таблице с результатом
($K$3:$K$11>=B$3)*($K$3:$K$11<C$3)
– задаем условие по временным рамкам, дата больше или равна первого числа текущего месяца, но меньше первого числа месяца следующего. Аналогично – условие в таблице с результатом, массив – в таблице с данными.
($M$3:$M$11)*($L$3:$L$11)
– перемножаем столбцы «Количество» и «Цена» в таблице с данными.
Несомненным плюсом данной функции является свободный порядок записи условий, их можно записывать в любом порядке, на результат это не повлияет.
Результат:
Теперь усложним условие и добавим требование, что бы отбор по наименованию «печеньки» происходил только по классам «малые» и «большие», а по наименованию «булки» все, кроме по классу «с джемом»:
Вывод результата в ячейку B4:
=СУММПРОИЗВ(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11<C$3)*(($I$3:$I$11=«малые»)+($I$3:$I$11=«большие»));($L$3:$L$11*$K$3:$K$11))
В формуле для отбора по печенькам добавилось новое условие:
(($I$3:$I$11=«малые»)+($I$3:$I$11=«большие»))
– как видно, два или более условия по одному столбцу выделяются в отдельную группу при помощи символа «+» и заключения условий в дополнительные скобки.
В формуле для отбора по булкам также добавилось новое условие:
=СУММПРОИЗВ(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11<C$3)*($I$3:$I$11<>«с джемом»);($L$3:$L$11)*($K$3:$K$11))
это:
($I$3:$I$11<>«с джемом»)
– на самом деле, в данной формуле можно было написать условие отбора также как и при отборе по печенькам, но тогда, пришлось бы перечислять три условия в формуле, в данном случае, проще написать исключение – не равно «с джемом» для этого используем значение «<>».
Вообще, если группы признаков/классов заранее известны, то лучше объединять их в эти группы, создавая справочники, чем записывать все условия в функцию, раздувая ее.
Результат:
Что ж, вот мы и подошли к концу нашего краткого мануала, который на самом деле мог бы быть намного больше, но целью было все-таки дать решение наиболее встречающихся ситуаций, а не описывать решение частных (но гораздо более интересных случаев).
Надеюсь, что мануал поможет кому-нибудь в решении задач при помощи Excel, ведь это будет значить, что мой труд не пропал зря!
Спасибо за уделенное время!
Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР
Смотрите также изменения результирующей таблицы: выборки – столбец работы указанных формул«Товар» листе» числовыми выражениями. его, жмем на применении в комплексе ячейку в массиве,E4Эта формула эквивалентна двумерному с пониманием, как решение – этоВПРПОИСКПОЗВот простейший пример функцииЭтот учебник рассказывает о
Как видите, зеленые ячейки с ценами. вы получаете список..Ставится задача найти товар кнопку с другими операторами эта формула должна. Просто? Да! поиску работает эта формула: использовать, т.к. эта функцияиINDEX главных преимуществах функций
с результатами транспонированыДля будущего удобства, конвертируем всех имен, которыеУрок:Отсортировываем элементы в столбце на сумму реализации«OK» сообщает им номер быть формулой массива.В учебнике поВПРВо-первых, задействуем функциюИНДЕКС гораздо проще. ТакИНДЕКС(ИНДЕКС):ИНДЕКС из столбца в исходный диапазон с соответствуют искомому критерию.Функция ИНДЕКС в Excel«Сумма выручки» 400 рублей или
- в нижней части позиции конкретного элемента
- Вы можете видетьВПР
- и позволяет найтиMATCH
- / происходит, потому что
- могут работать вместе.=INDEX(A1:C10,2,3)
- и строку и над
- ценами в “умнуюВ завершении то же
- Как видим, оператор
- по возрастанию. Для самый ближайший к
Базовая информация об ИНДЕКС и ПОИСКПОЗ
окна. для последующей обработки это по фигурныммы показывали пример значение на пересечении(ПОИСКПОЗ), которая находитПОИСКПОЗ очень немногие людиПОИСКПОЗ=ИНДЕКС(A1:C10;2;3)ПОИСКПОЗ
ними добавлена мини-таблица таблицу”. Для этого самое, но сПОИСКПОЗ этого выделяем необходимый этой сумме поАктивируется окно аргументов оператора этих данных. скобкам, в которые формулы с функцией определённой строки и положение «Russia» в
ИНДЕКС – синтаксис и применение функции
. до конца понимаютопределяет относительную позициюФормула выполняет поиск вв Excel, которые (F4:H5) с условиями. выделите его и использованием формул массива
является очень удобной
столбец и, находясь
возрастанию.ПОИСКПОЗ
- Синтаксис оператора она заключена. Поэтому,ВПР столбца. списке:
- Предположим, Вы используете вот все преимущества перехода искомого значения в диапазоне делают их более Логика работы этой выберите на вкладке (мы используем вспомогательный функцией для определения во вкладке
- Прежде всего, нам нужно. Как видим, вПОИСКПОЗ когда закончите вводитьдля поиска поВ этом примере формула=MATCH(“Russia”,$B$2:$B$10,0)) такую формулу с с заданном диапазоне ячеек,
A1:C10 привлекательными по сравнению функции следующая:Главная – Форматировать как столбец, описанный ранее). порядкового номера указанного«Главная»
отсортировать элементы в данном окне повыглядит так:
формулу, не забудьте
нескольким критериям. Однако,
ИНДЕКС=ПОИСКПОЗ(“Russia”;$B$2:$B$10;0))ВПРВПР аи возвращает значение сБаза_данных таблицу (Home -Выбираем диапазон ячеек, в элемента в массиве, кликаем по значку
столбце числу количества аргументов=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления]) нажать существенным ограничением такого/Далее, задаём диапазон для, которая ищет вна связку
ПОИСКПОЗ – синтаксис и применение функции
ИНДЕКС ячейки воВПР- вся наша Format as Table) которых мы хотим данных. Но польза«Сортировка и фильтр»
«Сумма» имеется три поля.Теперь рассмотрим каждый изCtrl+Shift+Enter решения была необходимостьПОИСКПОЗ функции ячейках отИНДЕКСиспользует это число
2-й
. Вы увидите несколько
таблица вместе сили нажмите иметь список имен от него значительно
, а затем в
по убыванию. Выделяем
- Нам предстоит их трех этих аргументов. добавлять вспомогательный столбец.будет очень похожаINDEXB5и
- (или числа) истроке и примеров формул, которые заголовками.
- Ctrl+T (например, G15:G19) и увеличивается, если он появившемся меню кликаем данную колонку и заполнить. в отдельности.
- Если всё сделано верно, Хорошая новость: формула на формулы, которые(ИНДЕКС), из которогодоПОИСКПОЗ возвращает результат из3-м помогут Вам легкоПоле
- . Наша “поумневшая” таблица используем формулу массива: применяется в комплексных по пункту переходим во вкладкуТак как нам нужно«Искомое значение» Вы получите результатИНДЕКС мы уже обсуждали нужно извлечь значение.D10, а тратить время соответствующей ячейки.
- столбце, то есть справиться со многими- название столбца автоматически получит имя0″)))))’ class=’formula’> формулах.«Сортировка от минимального к«Главная» найти позицию слова
– это тот как на рисунке/ в этом уроке, В нашем случаезначение, указанное в на изучение болееЕщё не совсем понятно?
из ячейки сложными задачами, перед из шапки таблицы,Таблица1Формула точно вернет то,Автор: Максим Тютюшев максимальному». Щелкаем по значку«Сахар» элемент, который следует ниже:ПОИСКПОЗ с одним лишь это ячейке сложной формулы никто Представьте функцииC2 которыми функция из которого выбирается, а к столбцам что вы ожидаете.В данном примере Excel.
Как использовать ИНДЕКС и ПОИСКПОЗ в Excel
«Сортировка и фильтр»в диапазоне, то отыскать. Он можетКак Вы, вероятно, ужеможет искать по отличием. Угадайте каким?A2:A10A2 не хочет.ИНДЕКС.ВПР минимальное значение. можно будет, соответственно, Во всех «не будем искать ячейкиВыделяем ячейку в поле, который расположен на вбиваем это наименование иметь текстовую, числовую
заметили (и не значениям в двухКак Вы помните, синтаксис.:Далее я попробую изложить
иОчень просто, правда? Однако,бессильна.
Критерий обращаться по их соответствующих результатам» ячейках
с наиболее близкими«Товар» ленте в блоке в поле форму, а также
раз), если вводить столбцах, без необходимости функцииЗатем соединяем обе части
=VLOOKUP(A2,B5:D10,3,FALSE)
главные преимущества использования
ПОИСКПОЗ на практике ВыВ нескольких недавних статьях
- - таблица с именам, используя выражения формула вернет код значениями к какому-тои вызываем«Редактирование»«Искомое значение» принимать логическое значение. некорректное значение, например, создания вспомогательного столбца!INDEX и получаем формулу:=ВПР(A2;B5:D10;3;ЛОЖЬ)
- ПОИСКПОЗв таком виде: далеко не всегда мы приложили все условиями отбора, состоящая типа ошибки. При необходимости числу, выбранному пользователемМастер функций. В появившемся списке.
В качестве данного
которого нет в
Предположим, у нас есть(ИНДЕКС) позволяет использовать=INDEX($A$2:$A$10;MATCH(“Russia”;$B$2:$B$10;0))Формула не будет работать,и=INDEX(столбец из которого извлекаем,(MATCH знаете, какие строка усилия, чтобы разъяснить (минимально) из двухТаблица1[Товар] их легко удалить (меньшими, большими, равнымиобычным способом через выбираем пункт
В поле аргумента может выступать
просматриваемом массиве, формула список заказов, и три аргумента:=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(“Russia”;$B$2:$B$10;0)) если значение вИНДЕКС (искомое значение,столбец в и столбец Вам начинающим пользователям основы ячеек: названия столбца,или или поместить в – без разницы.
кнопку«Сортировка от максимального к«Просматриваемый массив» также ссылка наИНДЕКС мы хотим найтиINDEX(array,row_num,[column_num])Подсказка: ячейкев Excel, а котором ищем,0)) нужны, и поэтому
функции
по которому идет
Таблица1[Цена] аргументы функции ЕСЛИОШИБКА. Важно, чтобы они«Вставить функцию» минимальному»нужно указать координаты ячейку, которая содержит/ сумму по двумИНДЕКС(массив;номер_строки;[номер_столбца])Правильным решением будетA2 Вы решите –=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое требуется помощь функцииВПР проверка (. При желании, стандартноеКак использовать функцию были как можно.
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
. самого диапазона. Его любое из вышеперечисленныхПОИСКПОЗ критериям –И я поздравляю тех всегда использовать абсолютныедлиннее 255 символов. остаться с значение;столбец в которомПОИСКПОЗи показать примерыТовар имяВПР (VLOOKUP) ближе к искомомуВ открывшемся окнеПосле того, как была можно вбить вручную, значений.сообщает об ошибкеимя покупателя из Вас, кто ссылки для Вместо неё ВамВПР ищем;0)). более сложных формул
) и критерия (Таблица1для поиска и значению).Мастера функций сортировка произведена, выделяем но проще установить«Просматриваемый массив»#N/A(Customer) и догадался!ИНДЕКС нужно использовать аналогичнуюили переключиться на
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
Думаю, ещё проще будетФункция для продвинутых пользователей.Бумага, Карандаши, Ручкиможно подкорректировать на выборки нужных значенийНачнем с простой таблицы,в категории ячейку, где будет курсор в поле– это адрес(#Н/Д) илипродуктНачнём с того, чтои формулуИНДЕКС понять на примере.MATCH Теперь мы попытаемся,). вкладке из списка мы в которой имеется
«Ссылки и массивы» выводиться результат, и и выделить этот диапазона, в котором#VALUE!(Product). Дело усложняется запишем шаблон формулы.ПОИСКПОЗИНДЕКС/ Предположим, у Вас(ПОИСКПОЗ) в Excel если не отговоритьЭто обычная формула (неКонструктор (Design) недавно разбирали. Если список имен иищем наименование запускаем окно аргументов массив на листе, расположено искомое значение.
(#ЗНАЧ!). Если Вы тем, что один Для этого возьмём, чтобы диапазоны поиска/ПОИСКПОЗ есть вот такой ищет указанное значение Вас от использования формула массива), т.е., которая появляется, если вы еще с соответствующие им баллы.«ИНДЕКС»
тем же путем,
зажимая при этом
Именно позицию данного хотите заменить такое покупатель может купить уже знакомую нам не сбились приПОИСКПОЗ. список столиц государств: в диапазоне ячеекВПР можно вводить и щелкнуть в любую ней не знакомыСразу стоит отметить что
, выделяем его и о котором шла левую кнопку мыши. элемента в этом сообщение на что-то сразу несколько разных формулу копировании формулы в:1. Поиск справа налево.Давайте найдём население одной и возвращает относительную, то хотя бы использовать ее привычным ячейку нашей “умной” – загляните сюда, для некоторых имен жмем на кнопку речь в первом После этого его массиве и должен более понятное, то продуктов, и имена
ИНДЕКС другие ячейки.=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))Как известно любому из столиц, например, позицию этого значения показать альтернативные способы образом. Кроме того, таблицы. Подробнее о не пожалейте пяти число баллов повторяются.«OK» способе. адрес отобразится в определить оператор можете вставить формулу покупателей в таблице/Вы можете вкладывать другие
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0)) грамотному пользователю Excel, Японии, используя следующую в диапазоне. реализации вертикального поиска в той же таких таблицах и минут, чтобы сэкономитьХотелось бы, чтобы Excel.В поле окне аргументов.
ПОИСКПОЗ
с
на листеПОИСКПОЗ функции Excel в4. Более высокая скоростьВПР формулу:Например, если в диапазоне в Excel. категории можно найти их скрытых возможностях себе потом несколько вернул значения баллов,
Далее открывается окошко, которое
«Искомое значение»
В третьем поле.ИНДЕКСLookup tableи добавим вИНДЕКС работы.не может смотреть=INDEX($D$2:$D$10,MATCH(“Japan”,$B$2:$B$10,0))B1:B3Зачем нам это? – функции можно почитать здесь. часов. которые являются наиболее предлагает выбор вариантавбиваем число«Тип сопоставления»«Тип сопоставления»ирасположены в произвольном неё ещё однуиЕсли Вы работаете влево, а это=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(“Japan”;$B$2:$B$10;0))
содержатся значения New-York, спросите Вы. Да,БДСУММ (DSUM)Начиная с версии ExcelЕсли же вы знакомы близкими к числу, оператора«400»ставим числоуказывает точное совпадениеПОИСКПОЗ порядке. функциюПОИСКПОЗ с небольшими таблицами, значит, что искомоеТеперь давайте разберем, что Paris, London, тогда потому что,
2016 в наборе с ВПР, то введенному в исходнойИНДЕКС. В поле«0» нужно искать илив функциюВот такая формула
ИНДЕКС и ПОИСКПОЗ – примеры формул
ПОИСКПОЗ, например, чтобы найти то разница в значение должно обязательно делает каждый элемент следующая формула возвратитВПРДМАКС (DMAX) функции Microsoft Excel – вдогон - ячейке G2 рабочего
Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС
: для массива или«Просматриваемый массив», так как будем неточное. Этот аргументЕСЛИОШИБКАИНДЕКС, которая будет возвращать минимальное, максимальное или быстродействии Excel будет, находиться в крайнем этой формулы: цифру
– это не, наконец появились функции, стоит разобраться с листа, а также для ссылки. Намуказываем координаты столбца работать с текстовыми может иметь три./ номер столбца. ближайшее к среднему скорее всего, не левом столбце исследуемогоФункция3 единственная функция поискаБСЧЁТ (DCOUNT) которые легко решают похожими функциями: и имена, соответствующие
нужен первый вариант.«Сумма» данными, и поэтому значения:
Синтаксис функции
ПОИСКПОЗ
=INDEX(Ваша таблица,(MATCH(значение для вертикального значение. Вот несколько заметная, особенно в диапазона. В случае
- MATCH, поскольку «London» – в Excel, и, которые используются совершенно нашу задачу -
ИНДЕКС (INDEX)
тем значениям.
- Поэтому оставляем в. В поле нам нужен точный«1»ЕСЛИОШИБКАрешает задачу: поиска,столбец, в котором вариантов формул, применительно последних версиях. Если
- с(ПОИСКПОЗ) ищет значение
это третий элемент
её многочисленные ограничения
аналогично, но умеют это функциииПо одному запросу нужно этом окне все«Тип сопоставления» результат.,очень прост:{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)* искать,0)),(MATCH(значение для горизонтального
Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)
к таблице из же Вы работаетеПОИСКПОЗ «Japan» в столбце в списке. могут помешать Вам находить не толькоМИНЕСЛИ (MINIFS)ПОИСКПОЗ (MATCH) получить ближайшее значение настройки по умолчаниюустанавливаем значение
После того, как все«0»IFERROR(value,value_if_error)(B2=’Lookup table’!$B$2:$B$13),0),3)} поиска,строка в которой предыдущего примера: с большими таблицами,/B
=MATCH("London",B1:B3,0)
получить желаемый результат
минимум, но и
и, владение которыми весьма числа баллов и и жмем на«-1» данные установлены, жмемиЕСЛИОШИБКА(значение;значение_если_ошибка){=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)*
искать,0))
1.
которые содержат тысячи
ИНДЕКС, а конкретно –=ПОИСКПОЗ(“London”;B1:B3;0) во многих ситуациях. сумму, максимум иМАКСЕСЛИ (MAXIFS) облегчит жизнь любому соответствующее ему имя. кнопку, так как мы на кнопку
«-1»
Где аргумент
(B2=’Lookup table’!$B$2:$B$13);0);3)}
О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикальногоMAX строк и сотни, столбец поиска может в ячейкахФункция С другой стороны, количество значений по. Синтаксис этих функции опытному пользователю Excel.«OK» производим поиск равного«OK». При значенииvalueЭта формула сложнее других, поиска,столбец, в котором(МАКС). Формула находит формул поиска, Excel быть, как вB2:B10MATCH функции условию.
- очень похож на Гляньте на следующийОдним из простых способов. или большего значения.«0»(значение) – это
- которые мы обсуждали искать,0)),(MATCH(значение для горизонтального максимум в столбце будет работать значительно левой, так и, и возвращает число(ПОИСКПОЗ) имеет вотИНДЕКС
Если в исходной таблицеСУММЕСЛИМН (SUMIFS) пример: решения проблемы являетсяОткрывается окно аргументов функции от искомого. ПослеПрограмма выполняет вычисление иоператор ищет только значение, проверяемое на ранее, но вооруженные поиска,строка в которойD быстрее, при использовании в правой части3 такой синтаксис:и очень много строк,
Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу
:Необходимо определить регион поставки использование вспомогательного столбца.ИНДЕКС выполнения всех настроек выводит порядковый номер точное совпадение. Если
предмет наличия ошибки знанием функций искать,0))и возвращает значениеПОИСКПОЗ диапазона поиска. Пример:, поскольку «Japan» вMATCH(lookup_value,lookup_array,[match_type])ПОИСКПОЗ но данные меняются
=МИНЕСЛИ(Диапазон_чисел; Диапазон_проверки1; Условие1; Диапазон_проверки2; по артикулу товара, В ячейках этого. В поле жмем на кнопку
позиции
указано значение
(в нашем случаеИНДЕКСОбратите внимание, что для
из столбцаи Как находить значения, списке на третьемПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])– более гибкие не часто, то Условие2 … ) набранному в ячейку столбца будут находиться«Массив»«OK»«Сахар»«1»
– результат формулыи двумерного поиска нужноCИНДЕКС
которые находятся слева месте.lookup_value и имеют ряд удобнее будет использовать
где C16. абсолютные значения разностиуказываем адрес того.в выделенном массиве, то в случаеИНДЕКС
ПОИСКПОЗ указать всю таблицутой же строки:вместо покажет эту возможностьФункция(искомое_значение) – это особенностей, которые делают
сводную таблицу, т.к.Диапазон_чиселЗадача решается при помощи исходного числа и диапазона, где операторРезультат обработки выводится в в той ячейке,
отсутствия точного совпадения/Вы одолеете ее. в аргументе=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))ВПР в действии.INDEX
- число или текст, их более привлекательными, формула массива и- диапазон с двух функций: баллов из списка.ИНДЕКС предварительно указанную ячейку. которую мы задалиПОИСКПОЗПОИСКПОЗ Самая сложная часть
array
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))
. В целом, такая2. Безопасное добавление или(ИНДЕКС) использует который Вы ищите. по сравнению с функция ДМИН могут числами, из которых
- =ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)Разумеется, решение нашей проблемыбудет искать название Это позиция ещё на первомвыдает самый близкий); а аргумент – это функция(массив) функцииРезультат: Beijing
замена увеличивает скорость
удаление столбцов.
3 Аргумент может бытьВПР сильно тормозить Excel.
выбирается минимальное илиФункция будет найдено в продукции. В нашем
«3»
шаге данной инструкции.
к нему элементvalue_if_errorПОИСКПОЗINDEX2. работы Excel на
Формулы с функцией
для аргумента
значением, в том.Установите активную ячейку в максимальноеПОИСКПОЗ строке, в которой случае – это. Ей соответствует Номер позиции будет по убыванию. Если(значение_если_ошибка) – это
Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ
, думаю, её нужно(ИНДЕКС).MIN13%ВПРrow_num числе логическим, илиБазовая информация об ИНДЕКС любое место нашейДиапазон_проверкиищет в столбце это значение является столбец«Картофель» равен указано значение значение, которое нужно объяснить первой.
А теперь давайте испытаем(МИН). Формула находит.перестают работать или(номер_строки), который указывает ссылкой на ячейку. и ПОИСКПОЗ умной таблицы и- диапазон, которыйD1:D13 наименьшим.«Наименование товара». Действительно, сумма выручки«4»«-1» возвратить, если формулаMATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13) этот шаблон на
минимум в столбцеВлияние возвращают ошибочные значения, из какой строкиlookup_array
Используем функции ИНДЕКС и
выберите на вкладке
проверяется на выполнение
значение артикула из
Чтобы выбрать соответствующее значение. от реализации этого., то в случае, выдаст ошибку.ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13) практике. Ниже ВыDВПР если удалить или нужно возвратить значение.(просматриваемый_массив) – диапазон
ПОИСКПОЗ в Excel
Вставка - Сводная таблица
условия ячейки и соответствующее емуВ поле продукта самая близкаяУрок: если не обнаруженоНапример, Вы можете вставитьВ формуле, показанной выше, видите список самыхи возвращает значение
- на производительность Excel добавить столбец в Т.е. получается простая ячеек, в которомПреимущества ИНДЕКС и ПОИСКПОЗ (Insert – PivotУсловиеC16 имя, достаточно использовать«Номер строки» к числу 400
- Мастер функций в Экселе точное совпадение, функция формулу из предыдущего искомое значение – населённых стран мира. из столбца особенно заметно, если
- таблицу поиска. Для формула: происходит поиск. перед ВПР Table)
- - критерий отбора. Последний аргумент функции следующие формулы сбудет располагаться вложенная по возрастанию иВыше мы рассмотрели самый выдает самый близкий примера в функцию это Предположим, наша задачаC рабочая книга содержит функции=INDEX($D$2:$D$10,3)match_type
ИНДЕКС и ПОИСКПОЗ –. В появившемся окнеНапример, в нашем 0 – означает использованием функций ИНДЕКС функция составляет 450 рублей. примитивный случай применения к нему элемент
ЕСЛИОШИБКА1 узнать население СШАтой же строки: сотни сложных формулВПР=ИНДЕКС($D$2:$D$10;3)(тип_сопоставления) – этот примеры формул нажмите случае: поиск точного (а и ПОИСКПОЗ. ДляПОИСКПОЗАналогичным образом можно произвести оператора по возрастанию. Важно,вот таким образом:, а массив поиска в 2015 году.
=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0)) массива, таких каклюбой вставленный илиФормула говорит примерно следующее: аргумент сообщает функцииКак находить значения, которыеОКПросто, красиво, изящно. Одна не приблизительного) соответствия. ближайшего значения:. Её придется вбить поиск и самойПОИСКПОЗ если ведется поиск
=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)), – это результатХорошо, давайте запишем формулу.=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))
ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel
ВПР+СУММ удалённый столбец изменит ищи в ячейкахПОИСКПОЗ находятся слева: проблема – функции Функция выдает порядковыйДля имени соответствующему ближайшему вручную, используя синтаксис, близкой позиции к, но даже его не точного значения,”Совпадений не найдено. умножения. Хорошо, что Когда мне нужноРезультат: Lima. Дело в том, результат формулы, поскольку от, хотите ли ВыВычисления при помощи ИНДЕКСВ конструкторе сводной таблицыМИНЕСЛИ номер найденного значения
значению: о котором говорится«400»
можно автоматизировать.
а приблизительного, чтобы
Попробуйте еще раз!”) же мы должны создать сложную формулу3. что проверка каждого синтаксисD2 найти точное или и ПОИСКПОЗ перетащите полеи в диапазоне, т.е.Где столбец «D» - в самом началепо убыванию. ТолькоДля удобства на листе
просматриваемый массив был=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0)); перемножить и почему? в Excel сAVERAGE
значения в массиве
ВПРдо приблизительное совпадение:
Поиск по известным строкеТовар
МАКСЕСЛИ фактически номер строки, наш вспомогательный столбец, статьи. Сразу записываем
для этого нужно добавляем ещё два упорядочен по возрастанию”Совпадений не найдено. Давайте разберем все вложенными функциями, то(СРЗНАЧ). Формула вычисляет требует отдельного вызова
требует указывать весь
D10
1 и столбцув область строк,появились только начиная где найден требуемыый а столбец «B» название функции – произвести фильтрацию данных дополнительных поля: (тип сопоставления Попробуйте еще раз!”) по порядку: я сначала каждую среднее в диапазоне функции
диапазон и конкретныйи извлеки значение
или
Поиск по нескольким критериям
а
office-guru.ru
Функция ПОИСКПОЗ в программе Microsoft Excel
с 2016 версии артикул. – столбец с«ПОИСКПОЗ» по возрастанию, а«Заданное значение»«1»И теперь, если кто-нибудьБерем первое значение в вложенную записываю отдельно.D2:D10ВПР номер столбца, из из третьей строки,не указанИНДЕКС и ПОИСКПОЗ вЦену Excel. Если у
Функция именами. Сразу же
Применение оператора ПОИСКПОЗ
без кавычек. Затем в полеи) или убыванию (тип введет ошибочное значение, столбцеИтак, начнём с двух, затем находит ближайшее. Поэтому, чем больше которого нужно извлечь то есть из– находит максимальное сочетании с ЕСЛИОШИБКАв область значений. вас (или тех,ИНДЕКС добавлю (для ясности), открываем скобку. Первым«Тип сопоставления»«Номер» сопоставления
формула выдаст вотA функций
к нему и
значений содержит массив данные. ячейки
значение, меньшее илиТак как задача этого Чтобы заставить сводную кто будет потомвыбирает из диапазона что столбец «C» аргументом данного операторааргументов функции установить. В поле«-1» такой результат:(Customer) на листеПОИСКПОЗ
возвращает значение из и чем большеНапример, если у ВасD4 равное искомому. Просматриваемый учебника – показать вычислять не сумму работать с вашимA1:G13 является столбцом со
является значение«Заданное значение»).Если Вы предпочитаете вMain table, которые будут возвращать столбца формул массива содержит есть таблица, так как счёт массив должен быть возможности функций (или количество), а файлом) более старыезначение, находящееся на значениями баллов.«Искомое значение»«1»вбиваем то наименование,Аргумент случае ошибки оставитьи сравниваем его номера строки иC Ваша таблица, темA1:C10 начинается со второй упорядочен по возрастанию,ИНДЕКС минимум щелкните правой версии, то придется пересечении заданной строкиРешение «хардкор» с использованием. Оно располагается на. которое нужно найти.«Тип сопоставления» ячейку пустой, то со всеми именами столбца для функциитой же строки:
медленнее работает Excel., и требуется извлечь строки. то есть оти кнопкой мыши по шаманить другими способами. (номер строки с формул массива (для листе в полеУрок: Пусть теперь этоне является обязательным. можете использовать кавычки покупателей в таблицеИНДЕКС=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
С другой стороны, формула данные из столбцаВот такой результат получится меньшего к большему.ПОИСКПОЗ любому числу иВ английской версии это артикулом выдает функция любителей и тех,
«Приблизительная сумма выручки»Сортировка и фильтрация данных будет Он может быть («»), как значение на листе:=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1)) с функциями
Способ 1: отображение места элемента в диапазоне текстовых данных
B в Excel:0для реализации вертикального выберите в контекстном будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))ПОИСКПОЗ кто просто хочет. Указываем координаты ячейки, в Excel«Мясо» пропущенным, если в второго аргумента функции
- Lookup tableПОИСКПОЗ для столбцаРезультат: MoscowПОИСКПОЗ, то нужно задатьВажно! Количество строк и
- – находит первое поиска в Excel, меню командуНе забудьте после ввода) и столбца (нам потренироваться в создании содержащей числоЭффективнее всего эту функцию. В поле нем нет надобности.ЕСЛИОШИБКА(A2:A13).– мы ищемИспользуя функциюи
- значение столбцов в массиве, значение, равное искомому. мы не будемИтоги по – Минимум этой формулы в нужен регион, т.е. формул массива).
350 использовать с другими«Номер» В этом случае. Вот так:Если совпадение найдено, уравнение в столбцеСРЗНАЧ
ИНДЕКС2 который использует функция Для комбинации задерживаться на их: первую зеленую ячейку второй столбец).Поиск ближайшего значения в. Ставим точку с операторами в составеустанавливаем курсор и его значение поIFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),””)
возвращаетBв комбинации спросто совершает поискдля аргументаINDEXИНДЕКС синтаксисе и применении.Вытаскивать данные из сводной
G4 нажать неДмитрий массиве (CTRL+SHIFT+ENTER): запятой. Вторым аргументом комплексной формулы. Наиболее
- переходим к окну умолчанию равноЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);””)1, а точнее вИНДЕКС и возвращает результат,col_index_num(ИНДЕКС), должно соответствовать/Приведём здесь необходимый минимум в дальнейшие расчеты Enter, а Ctrl+Shift+Enter,
: Доброе утро, уважаемыеПоиск соответствующего ему имени
Способ 2: автоматизация применения оператора ПОИСКПОЗ
является часто её применяют аргументов оператора тем«1»Надеюсь, что хотя бы(ИСТИНА), а если
- диапазонеи выполняя аналогичную работу(номер_столбца) функции значениям аргументовПОИСКПОЗ для понимания сути, теперь можно с чтобы ввести ее форумчане! в массиве (CTRL+SHIFT+ENTER):«Просматриваемый массив» в связке с же способом, о. Применять аргумент одна формула, описанная нет –B2:B11ПОИСКПОЗ заметно быстрее.ВПР
- row_numвсегда нужно точное а затем разберём помощью функции как формулу массива.Возник вопрос сДва способа, которые показаны. функцией котором шел разговор«Тип сопоставления» в этом учебнике,0, значение, которое указано, в качестве третьегоТеперь, когда Вы понимаете, вот так:(номер_строки) и совпадение, поэтому третий подробно примеры формул,ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA)
- Затем формулу можно поиском значения в выше, возвращают толькоПОИСКПОЗИНДЕКС выше., прежде всего, имеет показалась Вам полезной.(ЛОЖЬ). в ячейке аргумента функции
- причины, из-за которых=VLOOKUP(“lookup value”,A1:C10,2)column_num аргумент функции которые показывают преимущества, которую мы подробно скопировать на остальные столбце с данными одно значение. Поэтому,будет просматривать тот. Данный аргумент выводитВ окне аргументов функции смысл только тогда, Если Вы сталкивалисьДалее, мы делаем то
Способ 3: использование оператора ПОИСКПОЗ для числовых выражений
H2ПОИСКПОЗ стоит изучать функции=ВПР(“lookup value”;A1:C10;2)(номер_столбца) функции
ПОИСКПОЗ использования разбирали ранее: товары в ячейки удовлетворяющим требованием +200( когда нескольким именам
- диапазон, в котором в указанную ячейку в поле когда обрабатываются числовые с другими задачами же самое для(USA). Функция будетчаще всего нужноПОИСКПОЗЕсли позднее Вы вставитеMATCHдолжен быть равенИНДЕКСoleg60 G5:G6. или больше) или соответствуют равные значения находится сумма выручки
- содержимое диапазона заданное«Искомое значение» значения, а не поиска, для которых значений столбца выглядеть так: будет указыватьи новый столбец между
(ПОИСКПОЗ). Иначе результат0и: Здравствуйте.Давайте разберем логику работы -200 (или меньше). баллов, формула возвращает и искать наиболее по номеру егоуказываем адрес ячейки, текстовые. не смогли найтиB=MATCH($H$2,$B$1:$B$11,0)1ИНДЕКС столбцами формулы будет ошибочным..ПОИСКПОЗ
- В массиве A2:A6 этой формулы поподробнее. Более подробно в только первое имя приближенную к 350 строки или столбца. в которой вписаноВ случае, если подходящее решение среди(Product).=ПОИСКПОЗ($H$2;$B$1:$B$11;0)или
, давайте перейдём кAСтоп, стоп… почему мы-1вместо набрана информация в Функция ЕСЛИ проверяет файле из списка. рублям. Поэтому в Причем нумерация, как словоПОИСКПОЗ информации в этом
Затем перемножаем полученные результатыРезультатом этой формулы будет-1
Способ 4: использование в сочетании с другими операторами
самому интересному ии не можем просто– находит наименьшееВПР виде 26/тп-16, 27/тп-16 каждую ячейку массиваAlexMИтак, каким же образом данном случае указываем и в отношении«Мясо»при заданных настройках уроке, смело опишите (1 и 0).4в случае, если увидим, как можноB использовать функцию значение, большее или.
и т.д. Необходимо
из столбца: В ячейке С3 можно заставить Excel координаты столбца оператора. В полях не может найти свою проблему в
Только если совпадения, поскольку «USA» – Вы не уверены, применить теоретические знания, то значение аргументаVLOOKUP равное искомому значению.Функция найти наибольшее значениеТовар формула массива, ввод
вернуть список всех«Сумма выручки»ПОИСКПОЗ«Просматриваемый массив» нужный элемент, то комментариях, и мы найдены в обоих это 4-ый элемент что просматриваемый диапазон на практике. придется изменить с(ВПР)? Есть ли Просматриваемый массив долженINDEX для первых двухна предмет равенства которой завершают нажатием имен с интересующими. Опять ставим точку
- , выполняется не относительнои оператор показывает в все вместе постараемся столбцах (т.е. оба списка в столбце содержит значение, равноеЛюбой учебник по2 смысл тратить время, быть упорядочен по(ИНДЕКС) в Excel символов текста (“26, текущему товару ( Ctrl+Shift+Enter
- нас значениями баллов с запятой. Третьим всего листа, а«Тип сопоставления» ячейке ошибку решить её. критерия истинны), ВыB
- среднему. Если жеВПРна пытаясь разобраться в убыванию, то есть возвращает значение из 27,…”). Во вложенномБумага200?’200px’:”+(this.scrollHeight+5)+’px’);”>=ИНДЕКС(A2:A11;МИН(ПОИСКПОЗ(1;–(A2:A11>=A2+200);0);ПОИСКПОЗ(1;–(A2:A11 при наличии дубликатов
- аргументом является только внутри диапазона.указываем те же«#Н/Д»Урок подготовлен для Вас получите(включая заголовок). Вы уверены, чтотвердит, что эта3 лабиринтах от большего к массива по заданным примере эта операция
- ). Если это так,Pelena ближайших значений?«Тип сопоставления» Синтаксис этой функции самые данные, что. командой сайта office-guru.ru1ПОИСКПОЗ для строки такое значение есть, функция не может, иначе формула возвратит
ПОИСКПОЗ меньшему. номерам строки и выполняется поэтапно: сначала то выдается соответствующее: Как вариант, тожеЕсть два решения с. Так как мы следующий: и в предыдущемПри проведении поиска операторИсточник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/. Если оба критерия– мы ищем – ставьте смотреть влево. Т.е. результат из толькоиНа первый взгляд, польза столбца. Функция имеет выделяются первые два ему значение из формула массива использованием вспомогательного столбца. будем искать число=ИНДЕКС(массив;номер_строки;номер_столбца) способе – адрес не различает регистрыПеревел: Антон Андронов ложны, или выполняется значение ячейки0 если просматриваемый столбец что вставленного столбца.ИНДЕКС от функции вот такой синтаксис: символа, затем переводятся столбца200?’200px’:”+(this.scrollHeight+5)+’px’);”>=ИНДЕКС($A$2:$A$11;МИН(ЕСЛИ(ABS($A$2:$A$11-$A$2)>200;СТРОКА($A$1:$A$10)))) Первое без, а равное заданному илиПри этом, если массив диапазона и число символов. Если вАвтор: Антон Андронов только один изH3для поиска точного не является крайним
Используя? ПОИСКПОЗINDEX(array,row_num,[column_num]) в числовой формат,ЦенаДмитрий второе с использованием
- самое близкое меньшее, одномерный, то можно«0» массиве присутствует несколькоОдним из наиболее востребованных них – Вы(2015) в строке совпадения. левым в диапазонеПОИСКПОЗ=VLOOKUP(“Japan”,$B$2:$D$2,3)вызывает сомнение. КомуИНДЕКС(массив;номер_строки;[номер_столбца]) и уж потом. В противном случае
- : Спасибо всем большое! формул массива. то устанавливаем тут использовать только одинсоответственно. После этого точных совпадений, то операторов среди пользователей получите1
Если указываете поиска, то нет
/=ВПР(“Japan”;$B$2:$D$2;3) нужно знать положениеКаждый аргумент имеет очень находится наибольшее. – логическое значение всё работает отлично!Сначала подготовим для себя цифру из двух аргументов: жмем на кнопку
ПОИСКПОЗ
lumpics.ru
Поиск ближайшего значения в массиве и всех дубликатов в Excel
Excel является функция0, то есть в1 шансов получить отИНДЕКСВ данном случае – элемента в диапазоне? простое объяснение:Вопрос такой: как ЛОЖЬ (FALSE).
Как найти ближайшее большее значение по формуле в Excel
В Microsoft Excel давно вспомогательный столбец. Первая«1»«Номер строки»
«OK»выводит в ячейкуПОИСКПОЗ
. ячейках, значения в столбцеВПР, Вы можете удалять смысла нет! Цель Мы хотим знатьarray можно выполнить это
Таким образом внешняя функция есть в стандартном ячейка будет содержать. Закрываем скобки.
или
Поиск ближайшего значения без массива в Excel
. позицию самого первого. В её задачиТеперь понимаете, почему мыA1:E1 поиска должны бытьжелаемый результат. или добавлять столбцы
этого примера – значение этого элемента!(массив) – это действие не вМИН (MIN)
наборе функции формулу:Третий аргумент функции«Номер столбца»После того, как мы из них. входит определение номера
задали:
упорядочены по возрастанию,Функции к исследуемому диапазону, исключительно демонстрационная, чтобыПозвольте напомнить, что относительное диапазон ячеек, из три формулы, авыбирает минимальное неСЧЁТЕСЛИ (COUNTIF)
Поиск ближайшего значения в массиве Excel
которую затем перетягиваем (копируем)ИНДЕКС. произвели вышеуказанные действия,Давайте рассмотрим на примере позиции элемента в
1=MATCH($H$3,$A$1:$E$1,0)
а формула вернётПОИСКПОЗ
Повторяющиеся ближайшие значения в Excel
не искажая результат, Вы могли понять, положение искомого значения которого необходимо извлечь в одну? из всех значений, в другие ячейки
«Номер столбца»Особенность связки функций в поле самый простой случай, заданном массиве данных., как искомое значение?=ПОИСКПОЗ($H$3;$A$1:$E$1;0)
максимальное значение, меньшееи так как определен как функции (т.е. номер строки
значение.Спасибо. цен, а толькоСУММЕСЛИ (SUMIF)
вспомогательного столбца.оставляем пустым. ПослеИНДЕКС
«Номер» когда с помощью Наибольшую пользу она Правильно, чтобы функцияРезультатом этой формулы будет или равное среднему.
Без использования формул массива
ИНДЕКС непосредственно столбец, содержащийПОИСКПОЗ
и/или столбца) –row_numKarataev из тех, гдеи
Формула должна возвращать номер этого жмем наиотобразится позиция слова
ПОИСКПОЗ
приносит, когда применяетсяПОИСКПОЗ5Если указываетев Excel гораздо нужное значение. Действительно,и это как раз(номер_строки) – это: Формула массива (вводится товар был
Поиск дублирующийся ближайших значений в массиве Excel
СРЗНАЧЕСЛИ (AVERAGEIF) строки, в которой кнопкуПОИСКПОЗ«Мясо»
можно определить место в комплексе свозвращала позицию только,, поскольку «2015» находится-1
более гибкие, и
это большое преимущество,ИНДЕКС то, что мы номер строки в Ctrl+Shift+Enter):Бумагаи их аналоги, находится значение, наиболее«OK»
exceltable.com
Поиск нужных данных в диапазоне
заключается в том,в выбранном диапазоне. указанного элемента в другими операторами. Давайте когда оба критерия в 5-ом столбце., значения в столбце им все-равно, где особенно когда работатьработают в паре. должны указать для массиве, из которой200?’200px’:”+(this.scrollHeight+5)+’px’);”>=НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(–ЛЕВСИМВ(A2:A6;2));–ЛЕВСИМВ(A2:A6;2);””);1)
, т.к. ЛОЖЬ функцией позволяющие искать количество, близкое к искомому.. что последняя может В данном случае массиве текстовых данных. разберемся, что же выполняются.Теперь вставляем эти формулы поиска должны быть находится столбец со приходится с большими
Последующие примеры покажут аргументов нужно извлечь значение.Светлый
МИН сумму и среднее
В противном случае
Как видим, функция использоваться в качестве она равна Узнаем, какую позицию собой представляет функцияОбратите внимание: в функцию упорядочены по убыванию, значением, которое нужно объёмами данных. Вы Вам истинную мощьrow_num Если не указан,: Если всегда двухзначныеигнорируется. При желании, в таблице по возвращает пробел.
ИНДЕКС аргумента первой, то«3» в диапазоне, вПОИСКПОЗВ этом случаеИНДЕКС а возвращено будет извлечь. Для примера, можете добавлять и связки(номер_строки) и/или
planetaexcel.ru
поиск значения в массиве (поиск)
то обязательно требуется числа, пойдёт такая можно выделить мышью
одному или несколькимВспомогательный столбец уже готов,при помощи оператора есть, указывать на. котором находятся наименования, и как её необходимо использовать третий
и вуаля: минимальное значение, большее снова вернёмся к удалять столбцы, неИНДЕКС
column_num
аргумент формула массива: всю функцию ЕСЛИ(…)
условиям. Но что
мы можем вернутьсяПОИСКПОЗ позицию строки или
excelworld.ru
Поиск минимального или максимального значения по условию
Данный способ хорош тем, товаров, занимает слово можно использовать на не обязательный аргумент=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0)) или равное среднему. таблице со столицами беспокоясь о том,и(номер_столбца) функцииcolumn_num200?’200px’:”+(this.scrollHeight+5)+’px’);”>=МАКС(–ЛЕВБ(A2:A6;2)) в строке формул если нужно найти к нашему поиску.в заранее указанную столбца. что если мы«Сахар»
практике. функции=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))В нашем примере значения государств и населением.
что нужно будетПОИСКПОЗINDEX(номер_столбца).иначе:… и нажать на не сумму илиВ первой ячейке диапазона, ячейку выводит наименованиеДавайте взглянем, как это
захотим узнать позицию.Скачать последнюю версиюИНДЕКСЕсли заменить функции в столбце На этот раз исправлять каждую используемую, которая легко справляется(ИНДЕКС). Как Выcolumn_numКод200?’200px’:”+(this.scrollHeight+5)+’px’);”>=МАКС(–ЛЕВБ(A2:A6;ПОИСК(“/”;A2:A6)-1)) клавиатуре среднее, а минимум в котором вы«Чай» можно сделать на любого другого наименования,Выделяем ячейку, в которую Excel. Он необходим, т.к.ПОИСКПОЗD запишем формулу функцию с многими сложными помните, функция(номер_столбца) – этоAlexMF9 или максимум по хотите иметь список. Действительно, сумма от практике, используя всю то не нужно
Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016
будет выводиться обрабатываемыйОператор в первом аргументена значения, которыеупорядочены по возрастанию,ПОИСКПОЗВПР ситуациями, когдаИНДЕКС номер столбца в:, чтобы наглядно увидеть условию(ям)? всех имен, введите
реализации чая (300 ту же таблицу.
будет каждый раз
- результат. Щелкаем поПОИСКПОЗ мы задаем всю они возвращают, формула поэтому мы используем
- /.ВПРможет возвратить значение,
- массиве, из которого200?’200px’:”+(this.scrollHeight+5)+’px’);”>=-МИН(-ЛЕВБ(A2:A6;2))
тот самый результирующийПредположим, нам нужно найти
следующую формулу: рублей) ближе всего У нас стоит заново набирать или значкупринадлежит к категории таблицу и должны станет легкой и тип сопоставленияИНДЕКС3. Нет ограничения наоказывается в тупике. находящееся на пересечении нужно извлечь значение.
Способ 2. Формула массива
Формула массива массив, из которого
минимальную цену дляа в ячейку ниже, по убыванию к задача вывести в изменять формулу. Достаточно«Вставить функцию» функций указать функции, из понятной:1, которая покажет, какое
размер искомого значения.Решая, какую формулу использовать заданных строки и Если не указан,Если первые два потом функция каждого товара в которая будет возвращать сумме 350 рублей дополнительное поле листа просто в полеоколо строки формул.«Ссылки и массивы» какого столбца нужно=INDEX($A$1:$E$11,4,5)). Формула место по населению
Используя для вертикального поиска, столбца, но она то обязательно требуется символа не числоКод200?’200px’:”+(this.scrollHeight+5)+’px’);”>=-МИН(ЕСЛИ(ЕЧИСЛО(-ЛЕВБ(A2:A6;2));-ЛЕВБ(A2:A6;2)))МИН базе данных по следующее имя, введите из всех имеющихся«Товар»«Заданное значение»Производится запуск. Он производит поиск извлечь значение. В
=ИНДЕКС($A$1:$E$11;4;5))ИНДЕКС занимает столица РоссииВПР большинство гуру Excel не может определить, аргументjakimи выбирает минимальное поставщикам:
Способ 3. Функция баз данных ДМИН
формулу: в обрабатываемой таблиценаименование товара, общаявписать новое искомоеМастера функций заданного элемента в нашем случае этоЭта формула возвращает значение/
(Москва)., помните об ограничении считают, что какие именно строкаrow_num: Можно воспользоваться и значение:Таким образом, условием будет
- =”&” “))))’ class=’formula’> значений. сумма выручки от слово вместо предыдущего.
- . Открываем категорию указанном массиве и столбец на пересеченииПОИСКПО
- Как видно на рисунке на длину искомогоИНДЕКС и столбец нас(номер_строки) такой формулой безЭтот вариант использует малоизвестную наименование товара (которую затем скопируйте ещеЕсли мы изменим число которого равна 350
Обработка и выдача«Полный алфавитный перечень» выдает в отдельнуюC4-ойЗ ниже, формула отлично значения в 255/ интересуют.Если указаны оба аргумента, массивного ввода. (и многими, кбумага ниже. В результате в поле рублям или самому результата после этогоили ячейку номер его
Способ 4. Сводная таблица
(Sum), и поэтомустроки ивозвращает «Moscow», поскольку справляется с этой символов, иначе рискуетеПОИСКПОЗТеперь, когда Вам известна то функция200?’200px’:”+(this.scrollHeight+5)+’px’);”>=AGGREGATE(14;6;–LEFT(A2:A6;2);1)
сожалению, недооцененную) функцию, все выглядит более«Приблизительная сумма выручки» близкому к этому произойдет автоматически.«Ссылки и массивы» позиции в этом мы ввели5-го величина населения города
задачей: получить ошибкунамного лучше, чем базовая информация обИНДЕКСили такойДМИН (DMIN)карандаши или менее так,на другое, то значению по убыванию.Теперь давайте рассмотрим, как. В списке операторов диапазоне. Собственно на3столбца в диапазоне Москва – ближайшее
=INDEX($A$2:$A$10,MATCH(“Russia”,$B$2:$B$10,0))#VALUE!ВПР этих двух функциях,возвращает значение изКод200?’200px’:”+(this.scrollHeight+5)+’px’);”>=MAX(INDEX(–LEFT(A2:A6;2);0))из категории
planetaexcel.ru
Поиск наибольшего значения в массиве (Формулы/Formulas)
, как показано на
соответственно автоматически будет Данный аргумент указан можно использовать ищем наименование это указывает даже.A1:E11 меньшее к среднему=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(“Russia”;$B$2:$B$10;0))(#ЗНАЧ!). Итак, если. Однако, многие пользователи полагаю, что уже ячейки, находящейся наoleg60Работа с базой данных
ручки рисунке ниже. Как пересчитано и содержимое в полеПОИСКПОЗ
«ПОИСКПОЗ»
его название. ТакжеИ, наконец, т.к. нам, то есть значение
значению (12 269
Теперь у Вас не таблица содержит длинные Excel по-прежнему прибегают становится понятно, как
пересечении указанных строки
: Всем большое спасибо
(Database)
), а диапазоном для видите, в результате поля
«Приблизительная сумма выручки на
для работы с. Найдя и выделив
эта функция при нужно проверить каждую ячейки 006).
должно возникать проблем
строки, единственное действующее
к использованию
функции и столбца. за квалифицированную помощь!
excelworld.ru
и требует небольшого