Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция index match в excel

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

Здравствуйте друзья!

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

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

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

Синтаксис, который имеет функция ИНДЕКС в Excel, следующий:

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

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

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

=ИНДЕКС(C1:G12;6; 2)

вернет значение ячейки B4, то есть, значение с четвертой строки во втором столбике; В более сложном исполнении функции ИНДЕКС мы вернем значение целого массива:

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

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

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

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

До новых встреч на страницах сайта!

«Они нуждаются, обладая богатством, — а это самый тяжелый вид нищеты.
»
Л.А. Сенека

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

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

Также в этой статье рассматривается комбинация формул ИНДЕКС ПОИСКПОЗ в Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формула ПОИСКПОЗ Excel с подстановочными знаками

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

  • Вопросительный знак (?) — заменяет любой символ
  • Звездочка (*) — заменяет любую последовательность символов

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

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

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

Предполагая, что названия компаний находятся в диапазоне A2:A8, и вы ищете название, начинающееся с «восток», формула выглядит следующим образом:

=ПОИСКПОЗ(«восток*»; A2:A8; 0)

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

Читать еще:  Работа в excel для чайников

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

Как показано на изображении ниже, формула возвращает 2 позицию, которая соответствует компании « Восток-авто »:

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

Чтобы заменить только один символ в значении поиска, используйте подстановочный оператор «?», например:

Вышеуказанная формула будет соответствовать названию « Бейкер » и вернет его относительное положение, которое равно 5.

Формула ПОИСКПОЗ с учетом регистра

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

Чувствительность к регистру формулы для сопоставления данных в Excel заключается в следующем:

Логика этой формулы такова:

  • Функция СОВПАД сравнивает значение поиска с каждым элементом просматриваемого массива. Если сравниваемые ячейки в точности равны, функция возвращает ИСТИНУ, или ЛОЖЬ в противном случае.
  • Далее функция ПОИСКПОЗ сравнивает ИСТИНУ (которая является ее искомым_значением) с каждым значением в массиве, возвращаемом СОВПАД, и возвращает позицию первого совпадения

Обратите внимание , что это формула массива, которая требует нажатия Ctrl+Shift+Enter для правильной обработки.

Пусть значение поиска находится в ячейке E1, а массив поиска — A2:A8, тогда формула выглядит следующим образом:

=ПОИСКПОЗ(ИСТИНА; СОВПАД(A2:A8; E1);0)

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

Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с учетом регистра

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

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

=ЕСЛИ(ЕНД(ПОИСКПОЗ (1-е значение в Списке 1; Список 2; 0)); «Нет в Списке 1»; «»)

Для любого значения из Списка 2, отсутствующего в Списке 1, формула возвращает «Нет в Списке 1». И вот как это происходит:

  • Функция ПОИСКПОЗ выполняет поиск значения из Списка 1 в Списке 2. Если значение найдено, она возвращает относительное положение, в противном случае ошибку #Н/Д.
  • Функция ЕНД в Excel выполняет только одно: проверяет наличие ошибок #Н/Д (что означает «недоступно»). Если заданное значение является ошибкой #Н/Д, функция возвращает ИСТИНА, в противном случае – ЛОЖЬ. В нашем случае ИСТИНА означает, что значение из Списка 1 не найдено в Списке 2 (ПОИСКПОЗ возвращает ошибку #Н/Д).
  • Поскольку для других пользователей будет сложно интерпретировать результаты в формате ИСТИНА/ЛОЖЬ, то мы будем использовать функцию ЕСЛИ для отображения текста «Нет в Списке 1» для значений, которых нет в Списке 1.

Предположим, что нам необходимо сравнить значения в столбце B со значениями в столбце A, формула примет следующую форму (где B2 — самая верхняя ячейка):

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0));»Нет в Списке 1″; «»)

Как вы помните, функция ПОИСКПОЗ Excel сама по себе не учитывает регистр. Чтобы заставить ее различать регистр, вставьте функцию СОВПАД в аргумент просматриваемый_массив и не забудьте нажать Ctrl+Shift+Enter , чтобы завершить эту формулу массива :

=ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A;B2); 0));»Нет в Списке 1″; «»)

На следующем изображении демонстрируются обе формулы в действии:

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

ИНДЕКС и ПОИСКПОЗ в Excel

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

Синтаксис и использование функции ИНДЕКС

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

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

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

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

Если используются оба параметра номер_строки и номер_столбца, функция ИНДЕКС возвращает значение в ячейке на пересечении указанной строки и столбца.

Вот простейший пример формулы ИНДЕКС:

Формула ищет в ячейках от A1 до C10 и возвращает значение ячейки во второй строке и третьем столбце, то есть в ячейке C2.

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

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

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

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

Комбинацию ИНДЕКС ПОИСКПОЗ в Excel можно представить таким образом:

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

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

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

Найдем численность населения, например, России, используя следующую формулу ИНДЕКС ПОИСКПОЗ:

Теперь давайте разберем, что на самом деле выполняет каждый компонент этой формулы:

  • Функция ПОИСКПОЗ выполняет поиск значения «Россия» в столбце B, точнее в ячейках B2:B11 и возвращает номер 7, потому что «Россия» находится на седьмом месте в списке.
  • Функция ИНДЕКС принимает «7» во втором параметре (номер_строки), который указывает, из какой строки вы хотите вернуть значение, и превращается в простую формулу =ИНДЕКС($C$2:$C$11, 7). То есть формула производит поиск в ячейках C2-C11 и возвращает значение ячейки в седьмой строке, то есть ячейке C8, потому что мы начинаем отсчет со второй строки.

И вот результат, который мы получаем в Excel:

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

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

Excel. Функции ИНДЕКС и ПОИСПОЗ

Для извлечения данных вместо ВПР можно использовать связку функций ИНДЕКС и ПОИСКПОЗ. Формулы с ними работают быстрее и намного гибче. Лично я использую ВПР 90% времени, но иногда переключаюсь на ИНДЕКС + ПОИСКПОЗ, например, когда «ключевой» столбец не является первым слева в таблице.

Для меня функция ИНДЕКС совершенно непонятна. Проблема: я недавно прочитал справку Excel по этой функции. Кто в здравом уме будет использовать =ИНДЕКС(В4:G22;2;4), чтобы указать на ячейку F6 (рис. 5.1)?

Рис. 5.1. Функция ИНДЕКС кажется бесполезной

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

На самом деле, вы уже знакомы с тем, как работает функция ПОИСКПОЗ. Действительно, если вы используете ВПР, вы уже знаете кое-что и о ПОИСКПОЗ. Давайте сравним эти функции (рис. 5.2):

  • Первым параметром является поисковое значение, как ВПР.
  • Таблица подстановки включает один столбец, а не прямоугольный диапазон.
  • Вам не нужно указывать номер столбца, поэтому отсутствует третий аргумент.
  • Последний аргумент – ЛОЖЬ, как и в ВПР (также можно использовать ноль).

Пока всё очень похоже.

А вот и отличие… ПОИСКПОЗ не возвращает значение из таблицы. ПОИСКПОЗ расскажет вам, какая строка в таблице содержит искомое значение. Какая… по номеру. В нашем примере – вторая.

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

Рис. 5.2. ПОИСКПОЗ – замаскированная ВПР

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

Далее будут приведены примеры использования связки ИНДЕКС + ПОИСКПОЗ для решения проблем.

Поиск слева от ключевой колонки. Проблема: таблица подстановки ведется в другом отделе. Колонка с ценой у них расположена слева от столбца с номенклатурой. Могу ли я указать «–1» (минус один) в качестве третьего аргумента функции ВПР?

Рис. 5.3. Поиск значение слева от номенклатуры

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

Читать еще:  Вставить выбор из списка в excel

Итак, артикулы расположены в столбце G. Они могут быть не отсортированы, но не должно быть дублей. Посмотрите на формулу в С6 (рис. 5.4) =ПОИСКПОЗ(A6;$G$2:$G$29;0). Она говорит Excel найти СR50-3 в диапазон G2:G29. Третий аргумент – ноль – указывает на то, что вы ищете точное совпадение.

Рис. 5.4. ПОИСПОЗ находит CR50-3 во второй строке таблицы подстановки

ПОИСКПОЗ возвращает значение 2, т.е. говорит, что CR50-3 находится во второй строке диапазона. И на самом деле CR50-3 находится в G3. ПОИСКПОЗ возвращает относительное положение элемента в пределах диапазона поиска. Ответ «2» говорит, что CR50-3 находится во второй ячейке G2:G29.

Теперь, когда вы знаете позицию элемента в таблице поиска, вы можете использовать функцию ИНДЕКС, чтобы извлечь цену из таблицы. Укажите диапазон цен в качестве первого аргумента функции ИНДЕКС (рис. 5.5). Второй аргумент задает строку в таблице подстановки. И этим вторым аргументом является функции ПОИСКПОЗ. Поскольку у вас только один столбец в таблице подстановки, вам не нужен третий аргумент функции ИНДЕКС. В ячейке С6 формула =ИНДЕКС($F$2:$F$29;ПОИСКПОЗ(A6;$G$2:$G$29;0)).

Рис. 5.5. По существу, вы применили «левую» ВПР

Быстрый поиск по множеству столбцов. Проблема: мне нужно сделать двенадцать столбцов поиска. Таблица подстановки очень большая. Набор данных также весьма велик. Значения постоянно меняются и формулы пересчитываются.

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

Стратегия: добавить столбец «Где?», который вскоре будет скрыт. Использовать в нем формулу ПОИСКПОЗ, возвращающую номер строки в таблице подстановки, где находится артикул (рис. 5.6).

Рис. 5.6. Продукт A308 находится в 208-й строке таблицы подстановки

Теперь можно использовать функцию ИНДЕКС в 12 столбцах основной таблицы для извлечения значений из соответствующего столбца таблицы подстановки (рис. 5.7). Важно спланировать размещение знаков доллара в формуле:

  • В ячейку C3 введите =ИНДЕКС(R$3:R$226;$B3;0). Вы используете $ перед 3 и 226, чтобы ссылка не менялась при копировании формулы вдоль столбца С. Однако, вы не используете знаки доллара перед буквой R. При копировании этой части формулы вправо в ячейку D3 ссылка изменится на S$3:S$226. Будет использован столбец S таблицы подстановки, относящийся к февралю. Второй аргумент функции ИНДЕКС использует один знак доллара перед обозначением столбца B. При копировании формулы вправо она по-прежнему будет ссылаться на столбец В.
  • Скопируйте С3 в диапазон D3:Н3.
  • Выберите С3:Н3. Скопируйте этот диапазон вниз на все строки, дважды щелкнув маркер заполнения в правом нижнем углу ячейки Н3.
  • Скройте столбец В.

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

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

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

ВПР позволяет найти следующий меньшего размера, но не следующий большего размера (см. главу 6). На рис. 5.8 показан «правильный» продукт для покрытия 131 кв. м. Это продукт с артикулом N2557, который позволяет покрыть до 144 кв. м.

Рис. 5.8. Используя в качестве последнего аргумента функции ПОИСКПОЗ «–1» можно начти следующее большее значение

Стратегия: используйте ПОИСКПОЗ с третьим аргументом равным –1 (минус 1). Если использование в качестве третьего аргумента нуля, позволяет найти точное совпадение, то при «–1» ПОИСКПОЗ ищет значение, равное или ближайшее большее, чем искомое значение. Например, ПОИСКПОЗ(B2;$H$2:$H$9;-1) вернет 2, поскольку 144 кв. м – ближайшее большее искомых 131 кв. м. А =ИНДЕКС($I$2:$I$9;2;-1) вернет N2557.

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

Чтобы закончить анализ, добавьте в ячейку D2 формулу =ИНДЕКС($K$2:$K$9;ПОИСКПОЗ(B2;$H$2:$H$9;-1)) или =ВПР(C2;$I$2:$K$9;3;ЛОЖЬ).

Использование ПОИСКПОЗ для определения, присутствует ли элемент в списке. Проблема: у меня есть список элементов (товаров), по которым сегодня поступил заказ. Мне нужно сравнить этот список со списком позиций, которые есть в наличии на складе. Это же вопрос был задан в начале главы 4. Однако, сейчас с ПОИСКПОЗ ответ может быть немного проще.

Рис. 5.9. Какие элементы из столбца С есть в наличии, т.е. присутствуют в столбце D?

Стратегия: введите в В2 формулу =ПОИСКПОЗ(A2;$D$3:$D$16;0). Если элемент найден в списке Товары на складе, ПОИСКПОЗ вернет число – номер элемента в списке. Если товара нет в наличии, ПОИСКПОЗ вернет ошибку #Н/Д. Воспользовавшись рекомендациями главы 4, можно с помощью функций ЕСЛИ и ЕОШИБКА модифицировать формулу, чтобы она возвращала что-то более человеческое, например, =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$D$3:$D$16;0)); » нет на складе » ; » в наличии » ).

Рис. 5.10. Использование ПОИСКПОЗ для определения, присутствует ли элемент в списке

5 комментариев для “Excel. Функции ИНДЕКС и ПОИСПОЗ”

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

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

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

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

Массив — ссылка на диапазон ячеек.

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

Номер_столбца — номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.

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

Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС() возвращает значение ошибки #ССЫЛКА! Например, формула =ИНДЕКС(A2:A13;22) вернет ошибку, т.к. в диапазоне А2:А13 только 12 строк.

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

Пусть имеется одностолбцовый диапазон А6:А9.

Выведем значение из 2-й строки диапазона, т.е. значение Груши . Это можно сделать с помощью формулы =ИНДЕКС(A6:A9;2)

Если диапазон горизонтальный (расположен в одной строке, например, А6:D6 ), то формула для вывода значения из 2-го столбца будет выглядеть так =ИНДЕКС(A6:D6;;2)

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

Пусть имеется таблица в диапазоне А6:B9.

Выведем значение, расположенное в 3-й строке и 2-м столбце таблицы, т.е. значение 200 . Это можно сделать с помощью формулы =ИНДЕКС(A6:B9;3;2)

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

Если задать для аргумента «номер_строки» или «номер_столбца» значение 0, функция ИНДЕКС() возвратит массив значений для целого столбца или, соответственно, целой строки (не всего столбца/строки листа, а только столбца/строки входящего в массив). Чтобы использовать массив значений, введите функцию ИНДЕКС() как формулу массива .

Пусть имеется одностолбцовый диапазон А6:А9. Выведем 3 первых значения из этого диапазона, т.е. на А6 , А7 , А8 . Для этого выделите 3 ячейки ( А21 , А22 , А23 ), в Строку формул введите формулу =ИНДЕКС(A6:A9;0) , затем нажмите CTRL+SHIFT+ENTER .

Зачем это нужно? Теперь удалить по отдельности значения из ячеек А21 , А22 , А23 не удастся, мы получим предупреждение «нельзя изменять часть массива».

Хотя можно просто ввести в этих 3-х ячейках ссылки на диапазон А6:А8. Выделите 3 ячейки и введите формулу =A6:A8. Затем нажмите CTRL+SHIFT+ENTER и получим тот же результат.

Использование массива констант

Вместо ссылки на диапазон можно использовать массив констант :

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

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

Читать еще:  Не могу найти видео в телефоне

Формула =ВПР(«яблоки»;A35:B38;2;0) аналогична формуле =ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0)) которая извлекает цену товара Яблоки из таблицы, размещенную в диапазоне A35:B38

Связка ПОИСКПОЗ() + ИНДЕКС() даже гибче, чем функция ВПР() , т.к. с помощью ее можно, например, определить товар с заданной ценой (обратная задача, так называемый «левый ВПР()»). Формула =ИНДЕКС(A35:A38;ПОИСКПОЗ(200;B35:B38;0)) определяет товар с ценой 200. Если товаров с такой ценой несколько, то будет выведен первый сверху.

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

Функция ИНДЕКС() позволяет использовать так называемую ссылочную форму. Поясним на примере.

Пусть имеется диапазон с числами ( А2:А10 ) Необходимо найти сумму первых 2-х, 3-х, . 9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений).

Использование функции ИНДЕКС() в этом примере принципиально отличается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адрес ячейки) на значение. Вышеуказанная формула =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)

Аналогичный результат можно получить используя функцию СМЕЩ()

Теперь более сложный пример, с областями.

Пусть имеется таблица продаж нескольких товаров по полугодиям.

Задав Товар , год и полугодие , можно вывести соответствующий объем продаж с помощью формулы =ИНДЕКС((B9:C12;D9:E12;F9:G12);B15;A19;B17)

Вся таблица как бы разбита на 3 подтаблицы (области), соответствующие отдельным годам: B9:C12 ; D9:E12 ; F9:G12 . Задавая номер строки, столбца (в подтаблице) и номер области, можно вывести соответствующий объем продаж. В файле примера , выбранные строка и столбец выделены цветом с помощью Условного форматирования .

Функция ИНДЕКС (англ. INDEX) в Excel с примерами

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

Давайте вспомним как работает эта функция, а после этого рассмотрим работу данной функции совместно с функцией ПОИСКПОЗ (англ. MATCH)

Посмотрите на вот этот пример

Есть таблица с продажами различных фруктов в разных магазинах — это область A2:F10

Напомню синтаксис функции ИНДЕКС:

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

где массив — это наша таблица A2:F10

номер_строки — как следует из описания — это номер строки нашего массива (таблицы) , Обратите внимание! Номер строки необходимо считать именно по нашему указанному массиву , а не вообще с первой строки. Наша таблица начинается со второй строки.

номер_столбца — это номер столбца указанного массива. В нашем случае первый столбец совпадает с первым столбцом нашего массива.

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

=ИНДЕКС( A2:F10 ; 5 ; 3 ) — смотрите рисунок выше

Еще раз обратите внимание, слива находится в 6-й строке на данном листе, но если рассматривать именно наш массив A2:F10 , то видно, что Слива расположена на 5-й строчке данной таблицы. Как видите, все очень просто, но в таком виде формула не имеет применения, так как номер строки и номер столбца мы считали устно и без формулы. Если нам необходимо найти данные по другим товарам нам так же придется все считать устно и указывать номера строк и номера столбцов. Поэтому, в большинстве случаях функцию ИНДЕКС используют совместно с другими функциями, часто с функцией ПОИСКПОЗ, которая позволяет найти номер строки и столбца автоматически.

Функция ИНДЕКС в Excel с функцией ПОИСКПОЗ

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

И вот из этого отчета нам необходимо вытащить определенные данные. Например, продажи только Груш, Слив и Киви в магазине Перекресток и Лента — правая таблица с желтыми ячейками.

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

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

Итак, давайте для наглядности, чтобы вы видели последовательность действий, сначала пропишем функцию ИНДЕКС в чистом виде. В ячейке L4 нам необходимо найти из таблицы A2:F10 продажи Груш в Перекрестке. Пропишем формулу

=ИНДЕКС( A2:F10 ; 3 ; 3 ) — груша находится в третьей строке таблицы A2:F10, а Перекресток в третьем столбце . Отлично, а теперь пропишем формулу, чтобы номер строки и номер столбца считался автоматически.

Чтобы найти номер строки используем функцию ПОИСКПОЗ — поиск позиции. Синтаксис функции:

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

искомое_значение — нашем случае, в ячейке L4 мы ищем груши, поэтому искомое значение у нас будет K4

просматриваемый массив — нашем примере нам необходимо найти груши с столбце с фруктами — это диапазон A2:A10

тип_сопоставления — указываем 0, так как мы ищем полное совпадение.

Формула будет иметь следующий вид:

=ПОИСКПОЗ(K4;A2:A10;0) — итогом данной формулы будет позиция 3 в диапазоне A2:A10

аналогично, только в горизонтальном виде находим номер столбца.

искомое_значение — магазин перекресток или ячейка L3

просматриваемый массив — магазин мы находим в строке с магазинами — это диапазон A2:F2

тип_сопоставления — указываем 0, так как мы ищем точное совпадение.

в итоге получаем формулу:

=ПОИСКПОЗ(L3;A2:F2;0) — итогом данной формулы будет позиция 3 в диапазоне A2:F2

Теперь в нашу формулу =ИНДЕКС( A2:F10 ; 3 , 3 ) вместо номера строки и номера столбца пропишем раноценные значения, но в виде формул:

=ИНДЕКС( A2:F10 ; ПОИСКПОЗ(K4;A2:A10;0) ; ПОИСКПОЗ(L3;A2:F2;0) )

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

наш массив A2:F10 имеет относительный адрес, поэтому при протягивании формулы вниз и право диапазон так же будет сдвигаться, а он у нас постоянный, поэтому пропишем преобразуем его в абсолютный адрес, для этого пропишем знаки долларов перед столбцами и строки (можно выделить данный диапазон в формуле и нажать клавишу F4).

Далее идет номер строки с формулой ПОИСКПОЗ(K4;A2:A10 ;0) , при протягивании вниз у нас автоматически K4 (Груши) поменяется на K5 (Сливы), что нам и требуется, но диапазон, А2:A10 у нас постоянный, поэтому пропишем его в абсолютном виде А2:A10 → $А$2:$A$10

Все отлично, но когда мы будем протягивать формулу вправо, то K4 (Груши) автоматически поменяется на L4, нам же необходимо, чтобы при протягивании право K4 не менялось. Но мы помним, что в то же время нам необходимо, чтобы K4 менялось при протягивании вниз. Поэтому нам необходимо закрепить только столбец (K), а строка должна меняться. Пропишем знак доллар только перед столбцом К4 → $ K4

В итоге формула поиска номера строки будет выглядеть ПОИСКПОЗ($K4;$A$2:$A$10 ;0)

Аналогично с номером столбца, диапазон должен быть полностью закреплен, при протягивании вправо столбец должен меняться, а при протягивании вниз номер строки (строка с магазинами) не должен меняться. Для этого пропишем знак доллара только перед номером строки L3 → L$3

В итоге формула поиска номера столбца будет выглядеть ПОИСКПОЗ(L$3;$A$2:$F$2;0)

Итоговая формула, которую вы можете протянуть вниз и вправо

=ИНДЕКС( $ A$2:$F$10 ; ПОИСКПОЗ($K4;$A$2:$A$10;0) ; ПОИСКПОЗ(L$3;$A$2:$F$2;0) )

=INDEX($A$2:$F$10;MATCH($K4;$A$2:$A$10;0);MATCH(L$3;$A$2:$F$2;0)) — англ. версии

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

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

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

Ссылка на основную публикацию
Adblock
detector