Vvmebel.com

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

Индекс поиск позиции в excel

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

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

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

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

. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

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

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

Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

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

Вариант 4. Ссылка на столбец / строку

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

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

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

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

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

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

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

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

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

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

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

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

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

Чувствительность к регистру формулы для сопоставления данных в 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

В данной статье рассмотрены некоторые функции по работе со ссылками и массивами:

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

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

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

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

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

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

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

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;<2;»Два»:1;»Один»>;2;ЛОЖЬ) – результат выполнения строка «Два».

Функция ГПР

Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

Читать еще:  Excel фильтр содержит

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
  • стиль_ссылки – необязательный аргумент. Логическое значение:
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».
  • имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

=АДРЕС(1;1) – возвращает $A$1.
=АДРЕС(1;1;4) – возвращает A1.
=АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

  • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9».
  • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
    • ИСТИНА – стиль A1. Является значением по умолчанию;
    • ЛОЖЬ – стиль R1C1.

=ДВССЫЛ(«a3») – возвращает ссылку на ячейку A3.
=ДВССЫЛ(«r3c3») – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(«r3c3»; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:

Функция ЧСТРОК

Возвращает число строк в указанном диапазоне или массиве.

Синтаксис: =ЧСТРОК(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.

Пример использования:
=ЧСТРОК(D1:D8) – функция возвращает результат 8.
=ЧСТРОК(<1:2:3:4:5>) – функция определят, что в массиве 5 строк.

Функция ЧИСЛСТОЛБ

Возвращает число столбцов в указанном диапазоне или массиве.
Синтаксис: =ЧИСЛСТОЛБ(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.
Пример использования:
=ЧИСЛСТОЛБ(A5:D5) – результат функции 4.
=ЧИСЛСТОЛБ(<1;2;3;4;5>) – функция определят, что в массиве 5 столбцов.

Функция СМЕЩ

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

Синтаксис: =СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]), где

    ссылка – обязательный аргумент.

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

  • смещ_по_строкам – обязательный аргумент. Отрицательное либо положительное число, указывающее, на какое количество строк необходимо сместить диапазон.
  • смещ_по_столбцам – обязательный аргумент. Отрицательное либо положительное число, указывающее, на какое количество столбцов необходимо сместить диапазон.
  • высота – необязательный аргумент. Натуральное число (кроме нуля), определяющее количество строк в новом (смещенном) диапазоне.
  • ширина – необязательный аргумент. Натуральное число (кроме нуля), определяющее количество столбцов в новом диапазоне.
  • Если в результате смещения диапазон стал выходить на пределы листа, то функция возвращает ошибку #ССЫЛКА!.

    Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.
    Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.

    Теперь сместим диапазон на один столбец влево, т.е. на -1.

    Снова изменим ссылку, а именно расширим до 4 столбцов. После этого возвращаемая ссылка станет A3:D12. Результат на изображении.

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

    Возвращает позицию элемента, заданного по значению, в диапазоне либо массиве.

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

    • искомое_значение – обязательный аргумент. Значение элемента, который необходимо найти в массиве.
    • Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
    • тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее способ поиска элемента:
      • 1 – значение по умолчанию. Если совпадений не найдено, то возвращается позиция ближайшего меньшего по значению к искомому элементу. Массив или диапазон должен быть отсортирован от меньшего к большему или от А до Я.
      • 0 – функция ищет точное совпадение. Если не найдено, то возвращается ошибка #Н/Д.
      • -1 – Если совпадений не найдено, то возвращается позиция ближайшего большего по значению к искомому элементу. Массив или диапазон должен быть отсортирован по убыванию.

    Пример использования:
    =ПОИСКПОЗ(«Г»; <«а»;»б»;»в»;»г»;»д»>) – функция возвращает результат 4.

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

    При этом регистр не учитывается.
    =ПОИСКПОЗ(«е»; <«а»;»б»;»в»;»г»;»д»>; 1) – результат 5, т.к. элемента не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.
    =ПОИСКПОЗ(«е»; <«а»;»б»;»в»;»г»;»д»>; 0) – возвращается ошибка, т.к. элемент не найден, а тип сопоставления указан на точное совпадение.
    =ПОИСКПОЗ(«в»; <«д»;»г»;»в»;»б»;»а»>; -1) – результат 3.
    =ПОИСКПОЗ(«д»; <«а»;»б»;»в»;»г»;»д»>; -1) – элемент не найден, хотя присутствует в массиве. Функция возвращает неверный результат, так как последний аргумент принимает значение -1, а элементы НЕ расположены по убыванию.

    Для текстовых значений функция допускает использование подстановочных символов «*» и «?».

    Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

    У Вас недостаточно прав для комментирования.

    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. И после этого применить ВПР. Мы исходим из того, что такие манипуляции вам недоступны. Вы можете использовать ПОИСКПОЗ, чтобы выяснить, какую цену извлечь из таблицы для того или иного артикула.

    Итак, артикулы расположены в столбце 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)).

    Читать еще:  Excel 2020 перенос строки в ячейке

    Рис. 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 на простых примерах

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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