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) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.
Источник
Предназначение и синтаксис метода Range.Find
Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.
Синтаксис метода Range.Find
| 1 | Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) |
Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.
В скобках перечислены параметры метода, среди них только What является обязательным.
Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.
Параметры метода Range.Find
| Наименование | Описание |
| Обязательный параметр | |
| What | Данные для поиска, которые могут быть представлены строкой или другим типом данных Excel. Тип данных параметра – Variant. |
| Необязательные параметры | |
| After | Ячейка, после которой следует начать поиск. |
| LookIn | Уточняет область поиска. Список констант xlFindLookIn:
|
| LookAt | Поиск частичного или полного совпадения. Список констант xlLookAt:
|
| SearchOrder | Определяет способ поиска. Список констант xlSearchOrder:
|
| SearchDirection | Определяет направление поиска. Список констант xlSearchDirection:
|
| MatchCase | Определяет учет регистра:
|
| MatchByte | Условия поиска при использовании двухбайтовых кодировок:
|
| SearchFormat | Формат поиска – используется вместе со свойством Application.FindFormat. |
* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes. ** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.
В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.
Особенности использования функции ПОИСКПОЗ в Excel
Функция имеет следующую синтаксическую запись:
=ПОИСКПОЗ( искомое_значение;просматриваемый_массив; )
- искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
- просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
- – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:
- -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
- 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
- 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.
- Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
- Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
- Если аргумент явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
- Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.
Метод WorksheetFunction.Match
WorksheetFunction.Match – это метод VBA Excel, который возвращает относительное положение элемента в массиве (диапазоне), соответствующее его порядковому номеру в массиве (диапазоне). Метод соответствует функции рабочего листа =ПОИСКПОЗ (поиск позиции).
Обратите внимание на то, что
- поиск позиции в массиве возможен, если он одномерный или двумерный, но с набором элементов только по одному измерению, например: myArray(8, 0) или myArray(1 To 1, 1 To 20);
- поиск позиции в диапазоне рабочего листа возможен, если он содержит только одну строку или один столбец;
- нумерация относительного положения элемента в массиве начинается с единицы, независимо от заданной индексации массива.
Синтаксис метода WorksheetFunction.Match в VBA Excel:
| 1 | WorksheetFunction.Match(Arg1,Arg2,Arg3) |
Параметры
Описание параметров метода WorksheetFunction.Match:
| Параметр | Описание |
|---|---|
| Arg1 | Обязательный параметр. Значение элемента массива, по которому будет произведен поиск относительного положения элемента в массиве. |
| Arg2 | Обязательный параметр. Непрерывный диапазон ячеек или массив, в котором будет произведен поиск позиции элемента, значение которого совпадет со значением параметра Arg1. |
| Arg3 | Необязательный параметр. Задает тип сопоставления значения Arg1 со значениями в массиве Arg2. |
В параметре Arg1 можно использовать знаки подстановки для шаблонов, те же, что и для методов Find и Replace.
Значения параметра Arg3, задающие тип сопоставления:
| Значение | Тип сопоставления |
|---|---|
| -1 | Метод WorksheetFunction.Match находит наименьшее значение элемента в Arg2, большее или равное Arg1. Значения элементов Arg2 должны быть расположены в убывающем порядке: , , и т. д. |
| Метод WorksheetFunction.Match находит в Arg2 первое значение, равное Arg1. Значения элементов Arg2 могут быть расположены в любом порядке. | |
| 1 | Значение по умолчанию. Метод WorksheetFunction.Match находит наибольшее значение элемента в Arg2, меньшее или равное Arg1. Значения элементов Arg2 должны быть расположены в возрастающем порядке: , , и т. д. |
При сопоставлении строк регистр не учитывается.
Поиск ТЕКСТовых значений в Excel. Бесплатные примеры и статьи.
Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL
Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.
Есть ли слово в списке MS EXCEL
Найдем слово в диапазоне ячеек, удовлетворяющее критерию: точное совпадение с критерием, совпадение с учетом регистра, совпадение лишь части символов из слова и т.д.
Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца
Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие функции. Рассмотрим задачу в случае текстовых значений.
Поиск позиции ТЕКСТа с учетом РЕгиСТра в MS EXCEL и выводом значения из соседнего столбца
Здесь развиваются идеи статьи Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца. Для нахождения позиции значения с учетом РЕгиСТра, с последующим выводом соответствующего значения из соседнего столбца, функция ВПР() применена быть не может, т.к. она не чувствительна к регистру. Задача может быть решена с помощи функций БИЗВЛЕЧЬ() , НАЙТИ() или СОВПАД() .
Поиск в MS EXCEL текстового значения первого или последнего по алфавиту
Найдем текстовое значение, которое при сортировке диапазона по возрастанию будет выведено первым, т.е. первое по алфавиту. Также найдем последнее значение по алфавиту.
Разнесение в MS EXCEL ТЕКСТовых значений и ЧИСЕЛ по разным спискам
Если исходный список, содержит и текст и числа, то с помощью формул массива можно в один список отобрать все текстовые значения, а в другой – числовые.
Поиск в MS EXCEL третьего (n-го) повтора в списках с ТЕКСТом
Рассмотрим поиск текстовых значений в списке с повторами. Задав в качестве критерия для поиска нужное текстовое значение и номер его повтора в списке, найдем номер строки, в которой содержится этот повтор, а также выделим его и другие повторы с помощью Условного форматирования.
О классификации задач Поиска в EXCEL
В этой статье изложены попытки классификации задач по поиску значений в MS EXCEL. Статья может быть интересна для продвинутых пользователей EXCEL.
Поиск нескольких значений с максимальной длиной строки в MS EXCEL
Найдем в исходном списке текстовые значения, которые имеют максимальную длину строки, и выведем их в отдельный диапазон.
Функция ИНДЕКС Google Таблиц
В то время как ПОИСКПОЗ показывает, где искать ваше значение (его положение в диапазоне), функция ИНДЕКС Google Таблиц извлекает само значение на основе его смещения строки и столбца:
=ИНДЕКС(ссылка, , )
- ссылка это диапазон для поиска. Требуется.
- строка это количество строк для смещения от самой первой ячейки вашего диапазона. Необязательный, 0, если опущен.
- столбецкак строка, — количество смещенных столбцов. Также необязательно, также 0, если опущено.
Если вы укажете оба необязательных аргумента (строку и столбец), Google Sheets INDEX вернет запись из целевой ячейки:
=ИНДЕКС(A1:C10, 7, 1)
Пропустите один из этих аргументов, и функция получит всю строку или столбец соответственно:
=ИНДЕКС(A1:C10, 7)
Лучшая альтернатива INDEX MATCH в Google Sheets — множественные совпадения с функцией VLOOKUP
Какую бы функцию поиска вы ни предпочли, ВПР или ПОИСКПОЗ ПО ИНДЕКСУ, есть лучшая альтернатива им обоим.
Несколько совпадений ВПР это специальное дополнение для Google Таблиц, предназначенное для:
- поиск без формул
- поиск по всем направлениям
- поиск по нескольким условиям для разных типов данных: текст, числа, даты, время, и т.п.
- получить несколько совпадений, столько, сколько вам нужно (конечно, при условии, что в вашей таблице их столько)
Интерфейс простой, так что сомневаться в том, что вы все делаете правильно, не придется:
- Выберите исходный диапазон.
- Установите количество совпадений и столбцов для возврата.
- Настройте условия с помощью предопределенных операторов (содержит, =, не пусто, междутак далее.).
Вы также сможете:
- предварительно просмотреть результат
- решить, где разместить
- и как: формулой или просто значениями
Не упустите возможность проверить дополнение. Иди вперед и установите его из Google Workspace Marketplace. Его учебная страница подробно объяснит каждый вариант.
Также мы подготовили специальное обучающее видео:
Как выбрать уникальные и повторяющиеся значения в Excel — пошаговая инструкция
Прежде всего, нужно понять, что такое чемпион. Это одно из важнейших статистических понятий, означающее набор параметров, выбранных по определенному критерию. Выборка может быть чем угодно: люди для эксперимента, одежда, предприятия, титулы и так далее.
Чтобы создать образец, вы должны сначала выбрать те результаты, которые соответствуют условиям, из большого списка, а затем отобразить эти значения в отдельном списке или в исходной таблице.
Подготовка содержания выпадающего списка
Чтобы сделать нашу работу сегодня более наглядной, возьмем историю соглашений с клиентами. Это будет так, как показано на картинке.
Здесь необходимо с помощью цвета выделить все операции, выполняемые по отношению к каждому конкретному аналогу. Для переключения между ними применим выпадающий список. Поэтому изначально это необходимо сделать, а для этого необходимо выбрать данные, которые будут его элементами. В нашем примере нам нужны все совпадающие имена, которые находятся в столбце A и не повторяются. Чтобы подготовить содержимое выпадающего списка, вам необходимо выполнить следующие инструкции:
- Выберем первый столбец нашей таблицы.
- Воспользуемся инструментом «Данные» — «Сортировка и фильтр» — «Дополнительно».
- Далее перед нами появится окно, в котором мы должны выбрать тип обработки «копировать результат в другое место», а также поставить галочку напротив пункта «Только уникальные записи». В нашем случае диапазон, который мы используем, будет ячейкой с адресом $ F $ 1. Знак доллара означает, что ссылка является абсолютной и не будет изменяться в зависимости от того, копирует ли человек содержимое ячейки, связанной с ней, или вставляет ее.
- После установки всех необходимых параметров нам необходимо нажать кнопку ОК и затем мы подтверждаем свои действия.
Теперь мы видим список ячеек с уникальными фамилиями, которые больше не повторяются. Это будет наш выбор для списка. 
Модификация исходной таблицы
После этого нам нужно внести некоторые изменения в нашу таблицу. Для этого выделите первые две строчки и нажмите комбинацию клавиш Ctrl + Shift + =. Поэтому мы вставили две дополнительные строки. Во вновь созданной ячейке A1 введите слово «Клиент».
Создание выпадающего списка
Далее нам нужно создать раскрывающийся список. Для этого выполните следующие действия:
- Щелкаем по ячейке B1. Переходим во вкладку «Данные» — «Работа с данными» — «Проверка данных».
- Появится диалоговое окно, в котором мы должны выбрать тип данных «Список» и выбрать наш список фамилий в качестве источника данных. Затем нажмите кнопку ОК.
Впоследствии ячейка B1 превращается в полный список имен клиентов. Если информация, которая служит источником для раскрывающегося списка, находится на другом листе, то в этом случае лучше всего назвать этот диапазон и обращаться к нему таким образом.
Выборка ячеек из таблицы по условию
Теперь попробуем создать выделение ячеек по условию. Для этого выберите таблицу, содержащую название контрагента, его код, номер транзакции и сумму транзакции, после чего мы откроем окно «Условное форматирование». Для его вызова нужно перейти на вкладку «Главная», найти там группу «Стили» и там будет кнопка «Условное форматирование».
Появится меню, в котором мы должны нажать на пункт «Создать правило», так как мы выбираем «Использовать формулу для определения форматированных ячеек». 
Затем введите формулу, показанную на снимке экрана, затем нажмите кнопку «Форматировать», чтобы все ячейки были того же цвета, что и фамилия. Например, зеленый. Далее подтверждаем все ранее выполненные действия, многократно нажимая «ОК» во всех окнах, которые будут открыты в этот момент. Впоследствии, когда мы выбираем фамилию нашего человека, все ячейки, которые ее включают, выделяются указанным нами цветом.
Как это работает? Когда мы выбираем значение в раскрывающемся списке, формула анализирует все доступные строки и, если находит совпадение, выделяет их цветом, указанным пользователем. Вы можете убедиться, что формула работает, выбрав другую фамилию. Впоследствии выбор изменится. Это значительно упрощает чтение таблицы.
Принцип работы следующий: проверяется значение в столбце A. Если оно совпадает с выбранным в списке в ячейке B1, эта формула возвращает значение ИСТИНА. После этого вся строка форматируется так, как вы хотите. В принципе, вы можете не только выделить эту строку отдельным цветом, но и произвольно настроить шрифт, границы и другие параметры. Но мелирование цветом — самый быстрый способ.
Как мы получили цвет всей строки, а не отдельной ячейки? Для этого мы применили ссылку на ячейку, где адрес столбца является абсолютным, а номер строки относительным.