Excel найти в массиве последнее значение - Word и Excel

Поиск последнего вхождения (инвертированный ВПР)

Все классические функции поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP), ПОИСКПОЗ (MATCH) и им подобные имеют одну важную особенность — они ищут от начала к концу, т.е. слева-направо или сверху-вниз по исходным данным. Как только находится первое подходящее совпадение — поиск останавливается и найденным оказывается только первое вхождение нужного нам элемента.

Что же делать, если нам требуется найти не первое, а последнее вхождение? Например, последнюю сделку по клиенту, последний платёж, самую свежую заявку и т.д.?

Способ 1. Поиск последней строки формулой массива

Если в исходной таблице нет столбца с датой или порядковым номером строки (заказа, платежа…), то наша задача сводится, по сути, к поиску последней строки, удовлетворяющей заданному условию. Реализовать подобное можно вот такой формулой массива:

Здесь:

Функция ЕСЛИ (IF) проверяет по очереди все ячейки в столбце Клиент и выводит номер строки, если в ней лежит нужное нам имя. Номер строки на листе нам даёт функция СТРОКА (ROW), но поскольку нам нужен номер строки в таблице, то дополнительно приходится вычитать 1, т.к. у нас в таблице есть шапка.
Затем функция МАКС (MAX) выбирает из сформированного набора номеров строк максимальное значение, т.е. номер самой последней строки клиента.
Функция ИНДЕКС (INDEX) выдаёт содержимое ячейки с найденным последним номером из любого другого требуемого столбца таблицы (Код заказа).

Всё это нужно вводить как формулу массива, т.е.:

В Office 365 с последними установленными обновлениями и поддержкой динамических массивов — можно просто жать Enter.
Во всех остальных версиях после ввода формулы придется нажимать сочетание клавиш Ctrl+Shift+Enter, что автоматически добавит к ней фигурные скобки в строке формул.

Способ 2. Обратный поиск новой функцией ПРОСМОТРХ

Я уже писал большую статью с видео про новую функцию ПРОСМОТРХ (XLOOKUP), которая появилась в последних версиях Office на замену старушке ВПР (VLOOKUP). При помощи ПРОСМОТРХ наша задача решается совершенно элементарно, т.к. для этой функции (в отличие от ВПР) можно явно задавать направление поиска: сверху-вниз или снизу-вверх — за это отвечает её последний аргумент (-1):

Способ 3. Поиск строки с последней датой

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

Как это сделать с помощью классических функций я уже подробно разбирал, а теперь давайте попробуем использовать мощь новых функций динамических массивов. Исходную таблицу для пущей красоты и удобства тоже заранее преобразуем в «умную» с помощью сочетания клавиш Ctrl+T или команды Главная — Форматировать как таблицу (Home — Format as Table).

С их помощью этой «убойной парочки» наша задача решается весьма изящно:

Здесь:

Сначала функция ФИЛЬТР (FILTER) отбирает только те строки из нашей таблицы, где в столбце Клиент — нужное нам имя.
Потом функция СОРТ (SORT) сортирует отобранные строки по убыванию даты, чтобы самая последняя сделка оказалась сверху.
Функция ИНДЕКС (INDEX) извлекает первую строку, т.е. выдает нужную нам последнюю сделку.
И, наконец, внешняя функция ФИЛЬТР убирает из результатов лишние 1-й и 3-й столбцы (Код заказа и Клиент) и оставляет только дату и сумму. Для этого используется массив констант {0;1;0;1}, определяющий какие именно столбцы мы хотим (1) или не хотим (0) выводить.

Способ 4. Поиск последнего совпадения в Power Query

Ну, и для полноты картины, давайте рассмотрим вариант решения нашей задачи обратного поиска с помощью надстройки Power Query. С её помощью всё решается очень быстро и красиво.

1. Преобразуем нашу исходную таблицу в «умную» с помощью сочетания клавиш Ctrl+T или команды Главная — Форматировать как таблицу (Home — Format as Table).

2. Загружаем её в Power Query кнопкой Из таблицы/диапазона на вкладке Данные (Data — From Table/Range).

3. Сортируем (через выпадающий список фильтра в шапке) нашу таблицу по убыванию даты, чтобы самые последние сделки оказались сверху.

4. На вкладке Преобразование выбираем команду Группировать по (Transform — Group By) и задаем группировку по клиентам, а в качестве агрегирующей функции выбираем вариант Все строки (All rows). Назвать новый столбец можно как угодно — например Подробности.

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

5. Добавляем новый вычисляемый столбец кнопкой Настраиваемый столбец на вкладке Добавить столбец (Add column — Add custom column) и вводим следующую формулу:

Здесь Подробности — это столбец, откуда мы берем таблицы по клиентам, а {0} — это номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями (Record), где каждая запись — первая строка из каждой таблицы:

Осталось развернуть содержимое всех записей кнопкой с двойными стрелками в шапке столбца Последняя сделка, выбрав нужные столбцы:

… и удалить потом ненужный более столбец Подробности щёлкнув по его заголовку правой кнопкой мыши — Удалить столбцы (Remove columns).

После выгрузки результатов на лист через Главная — Закрыть и загрузить — Закрыть и загрузить в (Home — Close & Load — Close & Load to…) получим вот такую симпатичную таблицу со списком последних сделок, как и хотели:

При изменении исходных данных результаты нужно не забыть обновить, щёлкнув по ним правой кнопкой мыши — команда Обновить (Refresh) или сочетанием клавиш Ctrl+Alt+F5.

Ссылки по теме

Функция ПРОСМОТРХ — наследник ВПР
Как использовать новые функции динамических массивов СОРТ, ФИЛЬТР и УНИК
Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР

Источник

5 способов – поиск значения в массиве Excel

В статье предлагается несколько различных формул для выполнения поиска в двумерном массиве значений Excel. Просмотрите эти варианты и выберите наиболее для вас подходящий.

При поиске данных в электронных таблицах Excel чаще всего вы будете искать вертикально в столбцах или горизонтально в строках. Но иногда вам нужно просматривать сразу два условия – как строки, так и столбцы. Другими словами, вы стремитесь найти значение на пересечении определенной строки и столбца. Это называется матричным поиском (также известным как двумерный или поиск в диапазоне). Далее показано, как это можно сделать различными способами.

Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ
Формула ВПР и ПОИСКПОЗ для поиска в диапазоне
Функция ПРОСМОТРX для поиска в строках и столбцах
Формула СУММПРОИЗВ для поиска по строке и столбцу
Поиск в матрице с именованными диапазонами

Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ

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

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

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

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

Массив_данных — B2:E11 (ячейки данных, не включая заголовки строк и столбцов)
Значение_вертикальное — H1 (целевой товар)
Диапазон_поиска_столбец – A2:A11 (заголовки строк: названия напитков)
Значение_горизонтальное — H2 (целевой период)
Диапазон_поиска_строка — B1:E1 (заголовки столбцов: временные периоды)

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

=ИНДЕКС(B2:E11; ПОИСКПОЗ(H1;A2:A11;0); ПОИСКПОЗ(H2;B1:E1;0))

Как работает эта формула?

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

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

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

Итак, чтобы получить номер строки, мы ищем нужный нам товар (H1) в заголовках строк (A2:A11):

ПОИСКПОЗ(H1;A2:A11;0)

Чтобы получить номер столбца, мы ищем нужную нам неделю (H2) в заголовках столбцов (B1:E1):

ПОИСКПОЗ(H2;B1:E1;0)

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

В этом примере первое ПОИСКПОЗ возвращает 2, потому что нужный товар (Sprite) находится в ячейке A3, которая является второй по счёту в диапазоне A2:A11. Второй ПОИСКПОЗ возвращает 3, так как «Неделя 3» находится в ячейке D1, которая является третьей ячейкой в B1:E1.

С учетом вышеизложенного формула сводится к:

ИНДЕКС(B2:E11; 2 ; 3 )

Она возвращает число на пересечении второй строки и третьего столбца в матрице B2:E4, то есть в ячейке D3.

Думаю, вы понимаете, что аналогичным образом можно производить поиск в двумерном массиве Excel не только числа, но и текста. Тип данных здесь не имеет значения.

Формула ВПР и ПОИСКПОЗ для поиска в диапазоне

Другой способ выполнить матричный поиск в Excel — использовать комбинацию функций ВПР и ПОИСКПОЗ:

ВПР( значение_вертикальное ; массив_данных ; ПОИСКПОЗ( значение_горизонтальное , диапазон_поиска_строка , 0), ЛОЖЬ)

Для нашего образца таблицы формула принимает следующий вид:

=ВПР(H1; A2:E11; ПОИСКПОЗ(H2;A1:E1;0); ЛОЖЬ)

Где:

Массив_данных — B2:E11 (ячейки данных, не включая заголовки строк и столбцов)
Значение_вертикальное — H1 (целевой товар)
Значение_горизонтальное — H2 (целевой период)
Диапазон_поиска_строка — А1:E1 (заголовки столбцов: временные периоды)

Основой формулы является функция ВПР, настроенная на точное совпадение (последний аргумент имеет значение ЛОЖЬ). Она ищет заданное значение (H1) в первом столбце массива (A2:E11) и возвращает данные из другого столбца в той же строке. Чтобы определить, из какого столбца вернуть значение, вы используете функцию ПОИСКПОЗ, которая также настроена на точное совпадение (последний аргумент равен 0):

ПОИСКПОЗ(H2;A1:E1;0)

ПОИСКПОЗ ищет текст из H2 в заголовках столбцов (A1:E1) и указывает относительное положение найденной ячейки. В нашем случае нужная неделя (3-я) находится в D1, которая является четвертой по счету в массиве поиска. Итак, число 4 идет в аргумент номер_столбца функции ВПР:

=ВПР(H1; A2:E11; 4; ЛОЖЬ)

Далее ВПР находит точное совпадение H1 со значением в A3 и возвращает значение из 4-го столбца в той же строке, то есть из ячейки D3.

Важное замечание! Чтобы формула работала корректно, диапазон_поиска (A2:E11) функции ВПР и диапазон_поиска (A1:E1) функции ПОИСКПОЗ должны иметь одинаковое количество столбцов. Иначе число, переданное в номер_столбца, будет неправильным (не будет соответствовать положению столбца в массиве данных).

Функция ПРОСМОТРX для поиска в строках и столбцах

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

ПРОСМОТРX( значение_вертикальное ; диапазон_поиска_столбец ; ПРОСМОТРX( значение_горизонтальное ; диапазон_поиска_строка ; массив_данных ))

Для нашего примера набора данных формула выглядит следующим образом:

=ПРОСМОТРX(H1; A2:A11; ПРОСМОТРX(H2; B1:E1; B2:E11))

Примечание. В настоящее время ПРОСМОТРX — это функция, доступная только подписчикам Office 365 и более поздних версий.

В формуле используется функция ПРОСМОТРX для возврата всей строки или столбца. Внутренняя функция ищет целевой период времени в строке заголовка и возвращает все значения для этой недели (в данном примере для 3-й). Эти значения переходят в аргумент возвращаемый_массив внешнего ПРОСМОТРX:

=ПРОСМОТРX(H1; A2:A11; {544:87:488:102:87:433:126:132:111:565})

Внешняя функция ПРОСМОТРX ищет нужный товар в заголовках столбцов и извлекает значение из той же позиции из возвращаемого_массива.

Формула СУММПРОИЗВ для поиска по строке и столбцу

Функция СУММПРОИЗВ чрезвычайно универсальна — она может делать множество вещей, выходящих за рамки ее предназначения, особенно когда речь идет об оценке нескольких условий.

Чтобы найти значение на пересечении определенных строки и столбца, используйте эту общую формулу:

СУММПРОИЗВ ( диапазон_поиска_столбец = значение_вертикальное ) * ( диапазон_поиска_строка = значение_горизонтальное), массив_данных )

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

=СУММПРОИЗВ((A2:A11=H1)*(B1:E1=H2); B2:E11)

Приведенный ниже вариант также будет работать:

=СУММПРОИЗВ((A2:A11=H1)*(B1:E1=H2)*B2:E11)

Теперь поясним подробнее. В начале мы сравниваем два значения поиска с заголовками строк и столбцов (целевой товар в H1 со всеми наименованиями в A2: A11 и целевой период времени в H2 со всеми неделями в B1: E1):

(A2:A11=H1)*(B1:E1=H2)

Это дает нам два массива значений ИСТИНА и ЛОЖЬ, где ИСТИНА означает совпадения:

{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}) * ({ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}

Операция умножения преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 и создает матрицу из 4 столбцов и 10 строк (строки разделяются двоеточием, а каждый столбец данных — точкой с запятой):

{0;0;0;0:0;0;1;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0}

Функция СУММПРОИЗВ умножает элементы приведенного выше массива на элементы B2:E4, находящихся в тех же позициях:

{0;0;0;0:0;0;1;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0; 0;0:0;0;0;0:0;0;0;0:0;0;0;0} * {455;345;544;366:65;77;87;56:766; 655;488;865:129;66;102;56:89;141;87;89:566;511;433;522:154; 144;126; 162:158;165;132;155:112;143;111; 125:677;466;565;766})

И поскольку умножение на ноль дает в результате ноль, остается только элемент, соответствующий 1 в первом массиве:

=СУММПРОИЗВ({0;0;0;0:0;0;87;0:0;0;0;0:0;0;0;0:0;0;0;0:0; 0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0})

Наконец, СУММПРОИЗВ складывает все элементы результирующего массива и возвращает значение 87.

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

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

Еще один достаточно простой способ поиска в массиве в Excel — использование именованных диапазонов. Рассмотрим пошагово:

Шаг 1. Назовите столбцы и строки

Самый быстрый способ назвать каждую строку и каждый столбец в вашей таблице:

Выделите всю таблицу (в нашем случае A1:E11).
На вкладке « Формулы » в группе « Определенные имена » щелкните « Создать из выделенного » или нажмите комбинацию клавиш Ctrl + Shift + F3.
В диалоговом окне « Создание имени из выделенного » выберите « в строке выше » и « в столбце слева» и нажмите «ОК».

Это автоматически создает имена на основе заголовков строк и столбцов. Однако есть пара предостережений:

Если ваши заголовки столбцов и/или строк являются числами или содержат определенные символы, которые не разрешены в именах Excel, то имена для таких столбцов и строк не будут созданы. Чтобы просмотреть список созданных имен, откройте Диспетчер имен (Ctrl + F3). Если некоторые имена отсутствуют, определите их вручную.
Если некоторые из ваших заголовков строк или столбцов содержат пробелы, то они будут заменены символами подчеркивания, например, Неделя_1.

Шаг 2. Создание формулы поиска по матрице

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

=имя_строки имя_столбца

Или наоборот:

=имя_столбца имя_строки

Например, чтобы получить продажу Sprite в 3-й неделе, используйте выражение:

=Sprite неделя_3

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

Если кому-то нужны более подробные инструкции, опишем весь процесс пошагово:

В ячейке, в которой вы хотите отобразить результат, введите знак равенства (=).
Начните вводить имя целевой строки, Sprite. После того, как вы введете пару символов, Excel отобразит все существующие имена, соответствующие вашему вводу. Дважды щелкните нужное имя, чтобы ввести его в формулу.
После имени строки введите пробел , который в данном случае работает как оператор пересечения.
Введите имя целевого столбца ( в нашем случае неделя_3 ).
Как только будут введены имена строки и столбца, Excel выделит соответствующую строку и столбец в вашей таблице, и вы нажмете Enter, чтобы завершить ввод:

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

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

Еще несколько материалов по теме:

Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft…

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

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

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

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

Функция СУММПРОИЗВ с примерами формул — В статье объясняются основные и расширенные способы использования функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по нескольким условиям, расчета средневзвешенного значения…

Средневзвешенное значение — формула в Excel — В этом руководстве демонстрируются два простых способа вычисления средневзвешенного значения в Excel — с помощью функции СУММ (SUM) или СУММПРОИЗВ (SUMPRODUCT в английском варианте). В одной из предыдущих статей мы…

Источник

Ссылка на это место страницы:
#title

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

Ссылка на это место страницы:
#punk01

{ = ИНДЕКС( диапазон ; ПОИСКПОЗ( ЛОЖЬ; ЕПУСТО ( диапазон ); 0 )) }

{ = INDEX( диапазон ; MATCH( FALSE; ISBLANK ( диапазон ); 0 )) }

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

В данном примере мы используем эту формулу:

{ = ИНДЕКС( B3: B11; ПОИСКПОЗ( ЛОЖЬ; ЕПУСТО ( B3: B11 ); 0 )) }

{ = INDEX( B3:B11; MATCH( FALSE; ISBLANK ( B3:B11 ); 0 )) }

Таким образом, суть проблемы заключается в следующем: мы хотим получить первую не пустую ячейку, но для этого нет конкретной формулы в Excel. Мы могли бы использовать ВПР с шаблоном *, но это будет работать только для текста, а не для чисел.

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

Работая изнутри, ЕПУСТО оценивает ячейки в диапазоне В3: В11 и возвращает результат и массив, который выглядит следующим образом:

{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА}

Каждая ЛОЖЬ представляет собой ячейку в диапазоне, который не является пустой.

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

{ = ИНДЕКС( B3: B11; 2; 0 )) }

{ = INDEX( B3:B11; 2; 0 )) }

И, наконец, функция ИНДЕКС выводит значение в положении 2 в массиве, в этом случае число 10.

Ссылка на это место страницы:
#punk02

= ВПР ( «*»; диапазон; 1; ЛОЖЬ)

= VLOOKUP ( «*»; диапазон; 1; FALSE)

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

В данном примере формула в D7 является:

= ВПР ( «*» ; B5: B11 ; 1 ; ЛОЖЬ)

= VLOOKUP ( «*» ; B5:B11 ; 1 ; FALSE)

Групповой символ звездочка (*) соответствует любому текстовому значению.

Ссылка на это место страницы:
#punk03

= ГПР ( «*»; диапазон; 1; ЛОЖЬ)

= HLOOKUP ( «*»; диапазон; 1; FALSE)

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

= ГПР ( «*»; диапазон; 1; ЛОЖЬ)

= HLOOKUP ( «*»; диапазон; 1; FALSE)

Значение поиска является «*», групповым символом, который соответствует одному или более текстовому значению.

Ссылка на это место страницы:
#punk04

= ГПР ( «*»; диапазон; 1; ЛОЖЬ)

= HLOOKUP ( «*»; диапазон; 1; FALSE)

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

=МАКС(ЕСЛИ(B4:B11=G5;СТРОКА(B4:B11)-СТРОКА(ИНДЕКС(B4:B11;1;1))+1))

=MAX(IF(B4:B11=G5;ROW(B4:B11)-ROW(INDEX(B4:B11;1;1))+1))

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

Ссылка на это место страницы:
#punk05

=МАКС(ЕСЛИ(B4:B11=G5;СТРОКА(B4:B11)-СТРОКА(ИНДЕКС(B4:B11;1;1))+1))

=MAX(IF(B4:B11=G5;ROW(B4:B11)-ROW(INDEX(B4:B11;1;1))+1))

Чтобы проверить ячейку для одной из нескольких вещей, и вернуть последнее совпадение, найденное в списке, вы можете использовать формулу, основанную на ПРОСМОТР и ПОИСК функций. В случае нескольких найденных совпадений, формула вернет последнее совпадение из списка «вещей».

=ПРОСМОТР(2;1/ПОИСК($E$4:$E$7;B4);$E$4:$E$7)

=LOOKUP(2;1/SEARCH($E$4:$E$7;B4);$E$4:$E$7)

Ссылка на это место страницы:
#punk06

= НАИМЕНЬШИЙ( ЕСЛИ( логический тест; СТРОКА( список ) — МИН( СТРОКА( список )) + 1 ); n )

= SMALL( IF( логический тест; СТРОКА( список ) — MIN( ROW( список )) + 1 ); n )

Для того, чтобы получить позицию n-го совпадения (например, второе значение соответствия заданному, третье значение соответствия и т.д.), вы можете использовать формулу, основанную на функции НАИМЕНЬШИЙ.

= НАИМЕНЬШИЙ( ЕСЛИ( список = E5 ; СТРОКА( список ) — МИН( СТРОКА( список )) + 1 ); F5 )

= SMALL( IF( список = E5 ; ROW( список ) — MIN( ROW( список )) + 1 ); F5 )

Эта формула возвращает позицию второго появления «красных» в списке.

Сутью этой формулы является функция НАИМЕНЬШИЙ, которая просто возвращает n-е наименьшее значение в списке значений, которое соответствует номеру строки. Номера строк были «отфильтрованы» функцией ЕСЛИ, которая применяет логику для совпадения.

Ссылка на это место страницы:
#punk07

{ = ИНДЕКС( массив; НАИМЕНЬШИЙ( ЕСЛИ( величины = знач ; СТРОКА ( величины ) — СТРОКА ( ИНДЕКС( величины; 1 ; 1 )) + 1 ); n-й )) }

{ = INDEX( массив; SMALL( IF( величины = знач ; ROW ( величины ) — ROW ( INDEX( величины; 1 ; 1 )) + 1 ); n-й )) }

Эта формула возвращает позицию второго появления «красных» в списке.

Ссылка на это место страницы:
#punk08

= ВПР( id_формулы; стол; 4; 0 )

= VLOOKUP( id_формулы; стол; 4; 0 )

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

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

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

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

=ВПР(J3&»-«&I6;B4:G11;4;0)

=VLOOKUP(J3&»-«&I6;B4:G11;4;0)

Ссылка на это место страницы:
#punk09

{ = ИНДЕКС( результаты ;ПОИСКПОЗ( ИСТИНА ; ЕЧИСЛО( ПОИСК( вещи ; A1 )); 0 )) }

{ = INDEX( результаты ;MATCH( TRUE ; ISNUMBER( SEARCH( вещи ; A1 )); 0 )) }

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

{ = ИНДЕКС( результаты ; ПОИСКПОЗ( ИСТИНА ; ЕЧИСЛО( ПОИСК ( вещи ; B5 )); 0 )) }

= INDEX( результаты ; MATCH( TRUE ; ISNUMBER( SEARCH ( вещи ; B5 )); 0 ))

Эта формула использует два названных диапазона: E5: E8 называется «вещи» и F5: F8 называется «Результаты». Убедитесь, что вы используете диапазоны имен с одинаковыми именами (на основе ваших данных). Если вы не хотите использовать именованные диапазоны, используйте абсолютные ссылки вместо этого.

Ссылка на это место страницы:
#punk10

{ = ПОИСКПОЗ( ИСТИНА ; ЕОШИБКА(диап ); 0 ) }

{ = MATCH( TRUE ; ISERROR(диап ); 0 ) }

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

В приведенном примере формула:

{ = ПОИСКПОЗ( ИСТИНА ; ЕОШИБКА( B4: B11 ); 0 ) }

{ = MATCH( TRUE ; ISERROR( B4:B11 ); 0 ) }

Работая изнутри, функция ЕОШИБКА возвращает значение ИСТИНА, если значение является признанной ошибкой, и ЛОЖЬ, если нет.

Когда дается диапазон ячеек (массив ячеек) функция ЕОШИБКА будет возвращать массив истина/ложь значений. В примере, это результирующий массив выглядит следующим образом:

{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}

Обратите внимание, что 6-е значение (что соответствует 6-й ячейке в диапазоне) истинно, так как ячейка В9 содержит #Н/A.

Ссылка на это место страницы:
#punk11

=ИНДЕКС ( данные; ПОИСКПОЗ( поиск ; значения ) + 1 )

=INDEX ( данные; MATCH( поиск ; значения ) + 1 )

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

=ИНДЕКС ( данные; ПОИСКПОЗ( поиск ; значения ) + 1 )

=INDEX ( данные; MATCH( поиск ; значения ) + 1 )

Ссылка на это место страницы:
#punk12

{ = ОБЪЕДИНИТЬ ( «;» ; ИСТИНА ; ЕСЛИ( диапазон1 = E5 ; диапазон2 ; «» )) }

{ = ОБЪЕДИНИТЬ ( «;» ; TRUE ; IF( диапазон1 = E5 ; диапазон2 ; «» )) }

Для поиска и извлечения нескольких совпадений, разделенных запятыми (в одной ячейке), вы можете использовать функцию ЕСЛИ с функцией ОБЪЕДИНИТЬ.

{ = ОБЪЕДИНИТЬ( «;» ; ИСТИНА ; ЕСЛИ( группа = E5 ; имя ; «» )) }

Эта формула использует «имя» — именованный диапазон (B5: B11) и «группа» — (C5: C11).

Ссылка на это место страницы:
#punk13

{ = ПОИСКПОЗ( «*» & номер & «*» ; ТЕКСТ( диапазон ; «0» ); 0 ) }

{ = MATCH( «*» & номер & «*» ; TEXT( диапазон ; «0» ); 0 ) }

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

Excel поддерживает символы подстановки «*» и «?». Тем не менее, если вы используете специальные символы с номером, вы будете преобразовывать числовое значение в текстовое значение. Другими словами, «*» & 99 & «*» = «* 99 *» (текстовая строка).

Если попытаться найти текстовое значение в диапазоне чисел, совпадение завершится неудачно.

Одно из решений заключается в преобразовании чисел в диапазоне поиска для текстовых значений, а затем сделать нормальный поиск с ПОИСКПОЗ, ВПР и т.д.

Другой способ, чтобы преобразовать числа в текст, чтобы сцепить пустую строку. Эта формула работает так же, как выше формуле:

= ПОИСКПОЗ ( «*» & Е5 & «*» ; В5: В10 & «» ; 0 )

= MATCH ( «*» & Е5 & «*» ; В5: В10 & «» ; 0 )

Ссылка на это место страницы:
#punk14

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

В примере формула ВПР выглядит следующим образом:

=ВПР($H$2&»*»;$B$3:$E$12;2;0)

=VLOOKUP($H$2&»*»;$B$3:$E$12;2;0)

В этой формуле, значение представляет собой именованный диапазон, который относится к Н2, а также данные , представляет собой именованный диапазон , который относится к B3: E102. Без названных диапазонов, формула может быть записана следующим образом:

Ссылка на это место страницы:
#punk15

= ПОИСКПОЗ ( «* текст *» ; диапазон; 0 )

= MATCH ( «* текст *» ; диапазон; 0 )

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

=ПОИСКПОЗ(«*»&E6&»*»;B5:B10;0)

=MATCH(«*»&E6&»*»;B5:B10;0)

Функция ПОИСКПОЗ возвращает позицию или «индекс» в первом совпадении на основании значения поиска в диапазоне.

ПОИСКПОЗ поддерживает подстановочное согласование со звездочкой «*» (один или несколько символов) или знаком вопроса «?» (один символ), но только тогда, когда третий аргумент, тип_сопоставления, установлен в ЛОЖЬ или ноль.

Ссылка на это место страницы:
#punk16

Файлы статей доступны только зарегистрированным пользователям.

1. Введите свою почту

2. Нажмите Зарегистрироваться

3. Обновите страницу
Вместо этого блока появится ссылка для скачивания материалов.

Привет! Меня зовут Дмитрий. С 2014 года Microsoft Cretified Trainer. Вместе с командой управляем этим сайтом. Наша цель — помочь вам эффективнее работать в Excel.

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

Подписывайтесь на нас в соц.сетях:

Источник

You might want to try this, although it forces you to make one extra array, it looks for the first occurrence: Put your fruit data in A1:A10 and add an extra column next to it in B1:B10 (this is important and alas mandatory (see VLOOKUP description : it has to be ?1:?10)) with numbers from 1 to 10

To populate column B you can use, depending on your needs, formulas like

= ROWS($B$1:B1)
= ROW() + offset

Then the formula that will get your information is VLOOKUP (HLookup if your data array is horizontal). It will look for the value in the leftmost column of the argument matrix and return the matching value in the 2nd column (3rd argument, column B in our case). The FALSE is to require an exact match.

= VLOOKUP("orange", A1:B10, 2, FALSE)

Remember the drawbacks:
* You have to add one extra data column, be it convenient or not
* It will look for the first result. period.

(I am still searching for a better way to really find the MIN and MAX of an array of findings, but no success yet, except with Ctrl-Shift-Enter formulas, which are a no-go. Please post back if you find it)

Источник

За определенный период времени ведется регистр количества проданного товара в магазине. Необходимо регулярно отслеживать последний выданный из магазина товар. Для этого нужно отобразить последнюю запись в столбце наименования товаров. Чтобы просто посмотреть на последнее значение столбца, достаточно переместить курсор на любую его ячейку и нажать комбинацию горячих клавиш CTRL + стрелка в низ (↓). Но чаще всего пользователю приходится с последним значением столбца выполнять различные вычислительные операции в Excel. Поэтому лучше его получить в качестве значения для отдельной ячейки.

Поиск последнего значения в столбце Excel

Схематический регистр товаров, выданных с магазина:

Чтобы иметь возможность постоянно наблюдать, какой товар зарегистрирован последним, в отдельную ячейку E1 введем формулу:

Результат выполнения формулы для получения последнего значения:

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

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

Внимание! Все записи в столбце B должны быть неразрывны (без пустых ячеек до последнего значения).

Стоит отметить что данная формула является динамической. При добавлении новых записей в столбец B результат в ячейке E1 будет автоматически обновляться.

Источник

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

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

Цитата: IKor от 11.05.2011, 16:34
обойтись без доп. столбца и, желательно без функций массива, т.к. они не любят работать с целым_столбцом.

Без доп столбца легко —

=ИНДЕКС($B$3:$B$23;МАКС(ЕСЛИ(F3=$A$3:$A$23;СТРОКА($1:$21))))
А вот без формул массива сложнее.

ЗЫ А что за ограничения по работе с целым столбцом? Я с таким не сталкивался…

еще вариант:

=ПРОСМОТР(2;1/($A$3:$A$23=F3);$B$3:$B$23)

Отсортировать столбец A по возрастанию.

Webmoney: E350157549801 Z116603216205 R268084006579

Цитата: Serge 007 от 11.05.2011, 21:45
Я спрашиваю про ОГРАНИЧЕНИЯ.

Видимо у автора 2003 Excel… а там ограничения на целый столбец.
Причем неважно чем формируется массив, тремя клавишами или функциями создающими массивы (СУММПРОИЗВ(), ПРОСМОТР() и т.д.)

Webmoney: E350157549801 Z116603216205 R268084006579

Большое спасибо всем откликнувшимся.

Особенно для меня интересен второй предложенный способ — он должен работать быстрее.

К сожалению, они оба отказываются работать с целыми_столбцами (дома у меня действительно установлен MSExcel 2000)
Но второй вариант допускает возможность поиграть с динамически расширяемыми диапазонами, что для меня тоже подходит.

=ПРОСМОТР(2;1/(СМЕЩ($A$2;1;0;СЧЁТЗ($A:$A);1)=F3);СМЕЩ($A$2;1;1;СЧЁТЗ($A:$A);1))
Правда придется вниматьельно следить за пропусками строк в таблице

Относительно органичений по работе с целыми столбцами:
В MSExcel 2000 эти функции возвращают ошибку, если им попыпаться скормить целый_столбец в качестве рабочего диапазона.

Так как мой файл предназначен для работы со многими пользователями, то лучше подстраховаться…

P.S. Сортировать по столбцу — не подходит по условиям задачи… — тем более, что это не слишком сильно упростит решение…

Формулы в 2003 не могут обработать целый столбец в качестве массива.
=ПРОСМОТР(2;1/(СМЕЩ($A$2;1;0;СЧЁТЗ($A:$A);1)=F3);СМЕЩ($A$2;1;1;СЧЁТЗ($A:$A);1))
Желательно заменить на:
=ПРОСМОТР(2;1/($A$2:ИНДЕКС(A:A;ПОИСКПОЗ(«яяя»;A:A))=F3);$B$2:ИНДЕКС(B:B;ПОИСКПОЗ(«яяя»;A:A)))

Webmoney: E350157549801 Z116603216205 R268084006579

Хорошая идея.
Тем более, что саму конструкцию ПОИСКПОЗ(«яяя»;A:A) можно спрятать в именнованный диапазон и не высчитывать для каждой строки заново…

Так наверное будет наиболее оптимально

Webmoney: E350157549801 Z116603216205 R268084006579

Источник

Поиск значений в списке данных

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

Что необходимо сделать

Точное совпадение значений по вертикали в списке
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Точное совпадение значений по горизонтали в списке
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Создание формулы подступа с помощью мастера подметок (только в Excel 2007)

Точное совпадение значений по вертикали в списке

Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.

Примеры ВРОТ

Дополнительные сведения см. в этой информации.

Примеры индексов и совпадений

Что означает:

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет в C2:C10 первое значение, соответствующее значению «Ольга» (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению «Ольга».

Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.

К началу страницы

Подыыывка значений по вертикали в списке с помощью приблизительного совпадения

Для этого используйте функцию ВЛВП.

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.

Дополнительные сведения см. в этой информации.

К началу страницы

Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения

Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.

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

C1 — это левые верхние ячейки диапазона (также называемые начальной).

MATCH(«Оранжевая»;C2:C7;0) ищет «Оранжевые» в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.

1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.

К началу страницы

Точное совпадение значений по горизонтали в списке

Для этого используйте функцию ГГПУ. См. пример ниже.

Г ПРОСМОТР ищет столбец «Продажи» и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

К началу страницы

Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения

Для этого используйте функцию ГГПУ.

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

К началу страницы

Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )

Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).

В Excel 2007 создается формула подытов на основе данных на основе данных на основе строк и столбцов. Если вы знаете значение в одном столбце и наоборот, мастер под поисков помогает находить другие значения в строке. В формулах, которые он создает, используются индекс и MATCH.

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

Загрузка надстройки «Мастер подстройок»
Нажмите кнопку Microsoft Office , выберите Параметры Excel и щелкните категорию Надстройки.
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
Следуйте инструкциям мастера.

К началу страницы

Нужна дополнительная помощь?

Источник

Поиск значений в списке данных

Смотрите также- это одномерный предыдущей, в противном возвращаем это значениев русском языкеЕСЛИОШИБКА – это результат(USA). Функция будет-1 с пониманием, как Вместо неё Вам диапазона поиска. Пример: ячейки быть упорядочен поcolumn_num с Начальную ячейку неПредположим, что требуется найти диапазон или массив случае она не

В этой статье

в нужную ячейку последний и всеочень прост:

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

работает эта формула: нужно использовать аналогичную Как находить значения,D4

убыванию, то есть(номер_столбца) – этоВПР

следует включать в внутренний телефонный номер (строка или столбец),

обновляется. таблицы. предыдущие при сравнении

Поиск значений в списке по вертикали по точному совпадению

IFERROR(value,value_if_error) же мы должны=MATCH($H$2,$B$1:$B$11,0) поиска должны бытьВо-первых, задействуем функцию

Примеры функции ВПР

формулу которые находятся слева, так как счёт

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

от большего к

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

китинgling отбрасываются, во-вторых, потомуЕСЛИОШИБКА(значение;значение_если_ошибка) перемножить и почему?=ПОИСКПОЗ($H$2;$B$1:$B$11;0) упорядочены по убыванию,MATCHИНДЕКС

покажет эту возможность начинается со второй меньшему.

массиве, из которого

Поиск значений в списке по вертикали по приблизительному совпадению

примеров формул, которые1

 идентификационному номеру илиРежим_поиска, подскажите еще, как: Здравствуйте. Формулы не

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

value по порядку:4

минимальное значение, большее

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

положение «Russia» вПОИСКПОЗ2. Безопасное добавление или

Вот такой результат получится от функции Если не указан, справиться со многими которое нужно отсчитать вознаграждения, предусмотренную за ищем: точно (0), на нижележащие строчки Excel 2013 вроде слова.(значение) – этоБерем первое значение в

, поскольку «USA» – или равное среднему. списке::

удаление столбцов. в Excel:ПОИСКПОЗ то обязательно требуется сложными задачами, перед справа от начальной определенный объем продаж.

 с округлением в (таблица у меня определяет формат ячейки,Примечание значение, проверяемое на столбце это 4-ый элементВ нашем примере значения=MATCH(«Russia»,$B$2:$B$10,0))=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))Формулы с функциейВажно! Количество строк и

вызывает сомнение. Кому

Поиск значений в списке по горизонтали по точному совпадению

аргумент которыми функция ячейки, чтобы получить

Необходимые данные можно большую строну (-1) не из одной но у Вас: Вообще-то достаточно использовать предмет наличия ошибки

A списка в столбце в столбце

=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))

Поиск значений в списке по горизонтали по приблизительному совпадению

=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))ВПР

 столбцов в массиве, нужно знать положениеrow_numВПР

столбец, из которого быстро и эффективно или в меньшую строки)? 2007. Так что и « (в нашем случае(Customer) на листеB

DДалее, задаём диапазон для4. Более высокая скорость

перестают работать или

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

 который использует функция элемента в диапазоне?(номер_строки)бессильна. возвращается значение. В находить в списке сторону (1)Лорик

Вам требуется копатьяя – результат формулыMain table(включая заголовок).упорядочены по возрастанию, функции работы. возвращают ошибочные значения,INDEX Мы хотим знатьЕсли указаны оба аргумента,В нескольких недавних статьях этом примере значение и автоматически проверять

Давайте рассмотрим несколько полезных
: Разобралась с УФ в сторону макроса,», но тогда возникаетИНДЕКСи сравниваем егоПОИСКПОЗ для строки поэтому мы используем
INDEXЕсли Вы работаете если удалить или(ИНДЕКС), должно соответствовать

значение этого элемента!
то функция мы приложили все возвращается из столбца их правильность. Значения, вариантов ее применения самостоятельно, всем огромное или уточнить логику мизерная возможность попасть
/ со всеми именами– мы ищем тип сопоставления(ИНДЕКС), из которого с небольшими таблицами, добавить столбец в
значениям аргументовПозвольте напомнить, что относительноеИНДЕКС усилия, чтобы разъяснить D возвращенные поиском, можно на практике. спасибо!
заливки (если она

на таблицу, в

support.office.com

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

ПОИСКПОЗ покупателей в таблице значение ячейки1 нужно извлечь значение. то разница в таблицу поиска. Дляrow_num положение искомого значениявозвращает значение из начинающим пользователям основыПродажи затем использовать вКлассический сценарий — поисккитин есть), или вместо которой будет такое); а аргумент

на листеH3. Формула В нашем случае быстродействии Excel будет, функции(номер_строки) и (т.е. номер строки ячейки, находящейся на функции. вычислениях или отображать точного текстового совпадения: а вот если заливки в доп слово. Так называютсяvalue_if_error

Lookup table(2015) в строкеИНДЕКС это скорее всего, неВПРcolumn_num и/или столбца) – пересечении указанных строкиВПРК началу страницы как результаты. Существует для нахождения позиции даты идут не строке ставить какой город и река(значение_если_ошибка) – это(A2:A13).1/A2:A10 заметная, особенно влюбой вставленный или

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

Базовая информация об ИНДЕКС и ПОИСКПОЗ

MATCH то, что мыВот простейший пример функции более сложных формул используется функция ГПР. значений в списке или числа в200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(B4:K4;;ПРОСМОТР(2;1/((B3:K3=»р»)*(ЕЧИСЛО($B$4:ИНДЕКС(B4:K4;;ПРОСМОТР(2;1/((B3:K3=»н»)*(ЕЧИСЛО(B4:K4)));СТОЛБЕЦ(B3:K3)-1)))));СТОЛБЕЦ(B3:K3)-1)) увидит формула иди В детстве я возвратить, если формула

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

ИНДЕКС – синтаксис и применение функции

списке:вот такой монстр наоборот ставить знак был в этом выдаст ошибку.1A1:E1возвращает «Moscow», поскольку

и получаем формулу:
с большими таблицами,

синтаксис формулы будет ошибочным.

 аргументов(ИНДЕКС): Теперь мы попытаемся,Функция ГПР выполняет поиск результатов.
Если в качестве искомого получился в строке против городе и дажеНапример, Вы можете вставить(ИСТИНА), а если: величина населения города=INDEX($A$2:$A$10;MATCH(«Russia»;$B$2:$B$10;0)) которые содержат тысячи
ВПРСтоп, стоп… почему мыrow_num=INDEX(A1:C10,2,3) если не отговорить по столбцуПоиск значений в списке значения задать звездочку,Лорик не залитых ячеек

купался в этой формулу из предыдущего нет –=MATCH($H$3,$A$1:$E$1,0) Москва – ближайшее=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0)) строк и сотни

требует указывать весь не можем просто(номер_строки) и/или

=ИНДЕКС(A1:C10;2;3)
Вас от использования

Продажи по вертикали по то функция будет: ЗдОрово! Спасибо! и искать последний реке :) примера в функцию0=ПОИСКПОЗ($H$3;$A$1:$E$1;0) меньшее к среднемуПодсказка: формул поиска, Excel

диапазон и конкретный использовать функциюcolumn_numФормула выполняет поиск вВПРи возвращает значение точному совпадению искать первую ячейку_Boroda_

ПОИСКПОЗ – синтаксис и применение функции

этот знак.2. ДляЕСЛИОШИБКА(ЛОЖЬ).Результатом этой формулы будет значению (12 269Правильным решением будет будет работать значительно

номер столбца, изVLOOKUP(номер_столбца) функции диапазоне, то хотя бы из строки 5 вПоиск значений в списке с текстом и: Еще вариант обычнойЛорик

числовых
вот таким образом:

Далее, мы делаем то5 006). всегда использовать абсолютные

быстрее, при использовании
которого нужно извлечь

(ВПР)? Есть лиINDEXA1:C10 показать альтернативные способы указанном диапазоне. по вертикали по выдавать её позицию. формулой для «попорядку»
:значений:=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)), же самое для
, поскольку «2015» находитсяЭта формула эквивалентна двумерному ссылки дляПОИСКПОЗ данные. смысл тратить время,(ИНДЕКС). Как Вы
- и возвращает значение реализации вертикального поискаДополнительные сведения см. в приблизительному совпадению Для поиска последней200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПРОСМОТР(;-1/(B3:K3=»р»)/B4:K4;B4:K4)glingCode =ПРОСМОТР(9E+307;1:1)»Совпадений не найдено. значений столбца
-  в 5-ом столбце. поискуИНДЕКСиНапример, если у Вас пытаясь разобраться в помните, функция ячейки во в Excel. разделе, посвященном функцииПоиск значений по вертикали текстовой ячейки можноА вот для, и Вам здравствуйте!
- В английской версии: Попробуйте еще раз!»)BТеперь вставляем эти формулыВПРиИНДЕКС есть таблица лабиринтах

ИНДЕКС2-йЗачем нам это? – ГПР. в списке неизвестного изменить третий аргумент «непопорядку» я неВариант с дополнительной

Code =LOOKUP(9E+307,1:1)=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));(Product). в функциюи позволяет найтиПОИСКПОЗвместоA1:C10ПОИСКПОЗможет возвратить значение,строке и спросите Вы. Да,К началу страницы размера по точномуРежим_поиска понял — почему строкой не подойдет,Как это работает»Совпадений не найдено.Затем перемножаем полученные результатыИНДЕКС значение на пересечении, чтобы диапазоны поискаВПР

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

, и требуется извлечьи находящееся на пересечении3-м потому чтоДля выполнения этой задачи совпадениюс нуля на в примере ответ так как моя: Функция Попробуйте еще раз!») (1 и 0).и вуаля: определённой строки и не сбились при. В целом, такая данные из столбцаИНДЕКС заданных строки и

столбце, то естьВПР используется функция ГПР.Поиск значений в списке минус 1: 15.11, а не

таблица тогда будетПРОСМОТР()И теперь, если кто-нибудь
Только если совпадения=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0)) столбца.

копировании формулы в замена увеличивает скоростьB? столбца, но она

из ячейки– это неВажно: по горизонтали по

Числа и пустые ячейки
31.12? Принцип отсечения

необъятных размеров (онаищет слева направо введет ошибочное значение,

найдены в обоих=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))В этом примере формула другие ячейки. работы Excel на, то нужно задать=VLOOKUP(«Japan»,$B$2:$D$2,3) не может определить,C2 единственная функция поиска Значения в первой точному совпадению в этом случае
какой? и так не в указанной строке формула выдаст вот столбцах (т.е. обаЕсли заменить функцииИНДЕКСВы можете вкладывать другие13% значение=ВПР(«Japan»;$B$2:$D$2;3)
какие именно строка
.

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

В данном случае – и столбец нас

Очень просто, правда? Однако, её многочисленные ограничения отсортированы по возрастанию. по горизонтали поЕсли последний аргумент задатьЕсли все-таки нужно 2010 решает задачу9E+307Если Вы предпочитаете в получитена значения, которыеПОИСКПОЗИНДЕКС

Влияниедля аргумента смысла нет! Цель интересуют. на практике Вы могут помешать ВамВ приведенном выше примере приблизительному совпадению равным 1 или именно (и как конкретно),» и не найдя

случае ошибки оставить
1

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

(номер_столбца) функции исключительно демонстрационная, чтобы базовая информация об знаете, какие строка во многих ситуациях. значение 11 000 в строке 3 помощью мастера подстановок реализовать поиск ближайшего, то вроде 2013? последней ячейке в можете использовать кавычки ложны, или выполняется понятной: мы уже обсуждали, например, чтобы найти особенно заметно, еслиВПР Вы могли понять, этих двух функциях, и столбец Вам С другой стороны, в указанном диапазоне. (только Excel 2007) наименьшего или наибольшего формула из этогокитин которой есть хоть («»), как значение

только один из=INDEX($A$1:$E$11,4,5)) в этом уроке, минимальное, максимальное или рабочая книга содержит, вот так: как функции полагаю, что уже нужны, и поэтому функции Значение 11 000 отсутствует, поэтомуДля решения этой задачи числа. Таблица при поста его и

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

: а заливка вручную какое-то число. Так второго аргумента функции них – Вы=ИНДЕКС($A$1:$E$11;4;5)) с одним лишь ближайшее к среднему сотни сложных формул=VLOOKUP(«lookup value»,A1:C10,2)ПОИСКПОЗ становится понятно, как требуется помощь функцииИНДЕКС она ищет следующее можно использовать функцию этом обязательно должна выдает. или УФ? как мы неЕСЛИОШИБКА получитеЭта формула возвращает значение отличием. Угадайте каким? значение. Вот несколько

 массива, таких как=ВПР(«lookup value»;A1:C10;2)и функцииПОИСКПОЗи максимальное значение, не ВПР или сочетание быть отсортирована поИли нужно максимальнуюЛорик указали третий аргумент. Вот так:0 на пересеченииКак Вы помните, синтаксис вариантов формул, применительноВПР+СУММЕсли позднее Вы вставитеИНДЕКСПОИСКПОЗ

.ПОИСКПОЗ превышающее 11 000, и возвращает функций ИНДЕКС и возрастанию или убыванию незакрашенную дату?: этой функции «Вектор_результатов»,IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»).4-ой функции к таблице из. Дело в том,

новый столбец между
работают в паре.

иФункция– более гибкие 10 543. ПОИСКПОЗ. соответственно. В общемЛориккитин то функция возвращаетЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)Теперь понимаете, почему мыстроки иINDEX предыдущего примера:

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

 разделе, посвященном функции разделе, посвященном функции чем-то похоже на у Мне необходимо, что аргумента «Вектор_просмотра». одна формула, описанная1столбца в диапазоне три аргумента:MAX требует отдельного вызоваи связкиПОИСКПОЗ ищет указанное значение их более привлекательными, ГПР. ВПР.

интервальный просмотр укитин бы возвращалось значениеПояснение в этом учебнике,, как искомое значение?A1:E11INDEX(array,row_num,[column_num])(МАКС). Формула находит функцииBИНДЕКС

определяет относительную позицию
в диапазоне ячеек

по сравнению сК началу страницыЧто означает: функции, не сработала (в моем случае: Почему именно « показалась Вам полезной. Правильно, чтобы функция, то есть значениеИНДЕКС(массив;номер_строки;[номер_столбца]) максимум в столбце

ВПР
, то значение аргумента

и искомого значения в и возвращает относительнуюВПРПримечание:=ИНДЕКС(нужно вернуть значение изВПР (VLOOKUP)китин дата), которое стоит9E+307 Если Вы сталкивалисьПОИСКПОЗ ячейкиИ я поздравляю техD. Поэтому, чем больше придется изменить сПОИСКПОЗ заданном диапазоне ячеек, позицию этого значения. Поддержка надстройки «Мастер подстановок» C2:C10, которое будет, но там возможен: Не верю(с) покажите последним в строке,

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

. Просто? Да! догадался! из столбца и чем большена с многими сложнымиИНДЕКСНапример, если в диапазоне и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ – примеры формул

прекращена. Эта надстройка «Капуста» в массиве наименьшего, а здесь: заливкой. А Вы в не смогли найти выполняются.В учебнике поНачнём с того, чтоC

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

формул массива содержит3 ситуациями, когдаиспользует это числоB1:B3Используем функции ИНДЕКС и была заменена мастером B2:B10)) — есть выбор.китин о чем?Excel

подходящее решение средиОбратите внимание:ВПР запишем шаблон формулы.той же строки: Ваша таблица, тем, иначе формула возвратитВПР (или числа) исодержатся значения New-York, ПОИСКПОЗ в Excel функций и функциямиФормула ищет в C2:C10Например, нам нужно выбрать, у Вас вкитин. Поэтому функция найти информации в этомВ этом случаемы показывали пример Для этого возьмём=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))

медленнее работает Excel. результат из толькооказывается в тупике. возвращает результат из

Paris, London, тогда
Преимущества ИНДЕКС и ПОИСКПОЗ

для работы со первое значение, соответствующее генератор из прайс-листа последнем файле вразброс

: о том, каким его может только уроке, смело опишите необходимо использовать третий формулы с функцией
уже знакомую нам
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))
С другой стороны, формула что вставленного столбца.Решая, какую формулу использовать соответствующей ячейки. следующая формула возвратит перед ВПР ссылками и массивами. значению для расчетной мощности
не даты, а способом закрашивается ячейка.
в каком-то невероятном
свою проблему в

 не обязательный аргументВПР формулуРезультат: Beijing с функциямиИспользуя для вертикального поиска,Ещё не совсем понятно? цифруИНДЕКС и ПОИСКПОЗ –В Excel 2007 мастер

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

большинство гуру Excel Представьте функции3 примеры формул подстановок создает формулу(B7), и возвращает Если последний аргумент В строке значение при помощи формулы,

жизни пользователь такими
все вместе постараемся

ИНДЕКС

нескольким критериям. Однако,/MINи/ считают, чтоИНДЕКС, поскольку «London» –Как находить значения, которые

подстановки, основанную на
значение в ячейке

задать равным 1

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

и
это третий элемент

находятся слева

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

данных листа, содержащих C7 ( и отсортировать таблицу ячейки действительно 31.12.2016. формулой. если вручную оперирует.Урок подготовлен для Вас в первом аргументе решения была необходимостьи добавим в минимум в столбцепросто совершает поиск, Вы можете удалять/ПОИСКПОЗ в списке.Вычисления при помощи ИНДЕКС названия строк и100 по возрастанию, то (?!) ждите макрописцев. только3. Для командой сайта office-guru.ru мы задаем всю

добавлять вспомогательный столбец. неё ещё однуD и возвращает результат, или добавлять столбцыПОИСКПОЗв таком виде:=MATCH(«London»,B1:B3,0)
и ПОИСКПОЗ столбцов. С помощью). мы найдем ближайшуюЛорик VBA вам поможетсмешанныхИсточник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

таблицу и должны Хорошая новость: формула функциюи возвращает значение выполняя аналогичную работу к исследуемому диапазону,намного лучше, чем=INDEX(столбец из которого извлекаем,(MATCH=ПОИСКПОЗ(«London»;B1:B3;0)Поиск по известным строке мастера подстановок можноДополнительные сведения см. в наименьшую по мощности: Условие было: НайтиЛорик(текстово-числовыхПеревел: Антон Андронов указать функции, из

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

ИНДЕКСПОИСКПОЗ из столбца заметно быстрее. не искажая результат,ВПР (искомое значение,столбец в

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

/, которая будет возвращатьCТеперь, когда Вы понимаете так как определен

. Однако, многие пользователи
котором ищем,0))

MATCHПоиск по нескольким критериям в строке, если

ИНДЕКС и ПОИСКПОЗ.Зверь датами) последнююкитинзначений:Если Вам необходимо в извлечь значение. ВПОИСКПОЗ номер столбца.той же строки: причины, из-за которых непосредственно столбец, содержащий Excel по-прежнему прибегают=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое

(ПОИСКПОЗ) имеет вотИНДЕКС и ПОИСКПОЗ в известно значение вК началу страницы):
заполненную ячейку со значением, сейчас я простоCode =ПРОСМОТР(1;1/(A:A<>"");A:A) таблицах, которые имеют нашем случае это

может искать по=INDEX(Ваша таблица,(MATCH(значение для вертикального=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0)) стоит изучать функции нужное значение. Действительно, к использованию значение;столбец в котором такой синтаксис:

сочетании с ЕСЛИОШИБКА одном столбце, иДля выполнения этой задачиЕсли же третий аргумент(датой), не закрашенную заношу дату иВ английской версии: неодинаковое количество ячеек

столбец значениям в двух поиска,столбец, в котором=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))ПОИСКПОЗ это большое преимущество,ВПР

ищем;0))MATCH(lookup_value,lookup_array,[match_type])Так как задача этого наоборот. В формулах, используется функция ВПР. равен -1 и желтой заливкой. Если закрашиваю ячейку вручную.

Code =LOOKUP(1;1/(A:A<>»»);A:A) в строках и/илиC столбцах, без необходимости искать,0)),(MATCH(значение для горизонтальногоРезультат: Limaи особенно когда работать, т.к. эта функцияДумаю, ещё проще будетПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления]) учебника – показать
которые создает мастер
Важно:

таблица отсортирована по самая последняя заполненная НоКак это работает столбцах, например таких:(Sum), и поэтому создания вспомогательного столбца!
 поиска,строка в которой3.ИНДЕКС приходится с большими гораздо проще. Так понять на примере.lookup_value возможности функций подстановок, используются функции Значения в первой
убыванию, то мы
ячейка закрашена, то

можно: Функциянаходить последние заполненные мы ввели

Предположим, у нас есть искать,0))AVERAGE, давайте перейдём к

объёмами данных. Вы
происходит, потому что

Предположим, у Вас(искомое_значение) – этоИНДЕКС ИНДЕКС и ПОИСКПОЗ. строке должны быть найдем ближайшую более

просматриваем левее от
и задать заливку

ПРОСМОТР() ячейки и извлекать3 список заказов, и=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального(СРЗНАЧ). Формула вычисляет самому интересному и можете добавлять и очень немногие люди есть вот такой число или текст,

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

На вкладкеВ приведенном выше примереБомбакитин форматированием, тогда какая в указанной строке то вИ, наконец, т.к. нам сумму по двум искать,0)),(MATCH(значение для горизонтальногоD2:D10 применить теоретические знания беспокоясь о том, все преимущества переходаДавайте найдём население одной Аргумент может бытьдля реализации вертикальногоФормулы

функция ВПР ищет):: ничё не понял будет формула? число «

Excel нужно проверить каждую
критериям – поиска,строка в которой

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

Вы, к сожалению,
ячейку в массиве,

имя покупателя искать,0)) к нему иЛюбой учебник по исправлять каждую используемуюВПР Японии, используя следующую числе логическим, или мы не будемРешения с 6 пропусками в

используется в связке последний файл зеленую: Тогда другой вопрос:» и найдя его, не найдёте функции эта формула должна(Customer) иОбратите внимание, что для возвращает значение изВПР функцию
на связку формулу: ссылкой на ячейку. задерживаться на ихвыберите команду диапазоне A2:B7. Учащихся с другой крайне
ячейку смотрите? по какому критерию останавливается на последней типа быть формулой массива.
продукт двумерного поиска нужно столбцатвердит, что этаВПРИНДЕКС=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))lookup_array синтаксисе и применении.Подстановка с полезнойфункцией -китин надо делать заливку? ячейке в которой

ВЕРНУТЬ.ПОСЛЕДНЮЮ.ЯЧЕЙКУ() Вы можете видеть(Product). Дело усложняется указать всю таблицуC функция не может.и=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0))

(просматриваемый_массив) – диапазонПриведём здесь необходимый минимум.6ИНДЕКС: а у меня т.е. назовите причину есть это число.Вот как это это по фигурным тем, что один в аргументетой же строки: смотреть влево. Т.е.3. Нет ограничения наПОИСКПОЗТеперь давайте разберем, что ячеек, в котором для понимания сути,Если команда

пропусками в таблице нет,(INDEX) не так разве? окрашивания ячейки в Так как мы сделать имеющейся в скобкам, в которые покупатель может купитьarray=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1)) если просматриваемый столбец размер искомого значения., а тратить время делает каждый элемент

происходит поиск. а затем разберёмПодстановка поэтому функция ВПР

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

, которая умеет извлекатьЛорик желтый цвет указали третий аргумент стандартном наборе функций она заключена. Поэтому, сразу несколько разных(массив) функции=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1)) не является крайнимИспользуя на изучение более этой формулы:match_type подробно примеры формул,недоступна, необходимо загрузить ищет первую запись данные из диапазона: Значение последней заполненнойЛорик этой функции «Вектор_результатов», функцией когда закончите вводить продуктов, и именаINDEX

Результат: Moscow левым в диапазонеВПР

сложной формулы никто
Функция

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

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

реализуя, фактически, "левый 31.12.2016.китин значение из него, . нажать

на листеА теперь давайте испытаемСРЗНАЧ шансов получить от

на длину искомогоДалее я попробую изложить(ПОИСКПОЗ) ищет значениеПОИСКПОЗИНДЕКСНажмите кнопку 6. Она находит ВПР».

Может мы не
, к примеру, можно

соответствующее позиции последнего1. ДляCtrl+Shift+EnterLookup table этот шаблон нав комбинации сВПР значения в 255 главные преимущества использования «Japan» в столбце, хотите ли ВыиMicrosoft Office значение 5 и возвращаетТак, в предыдущем примере

поняли Ваш смысл добавить строку вверху
вхождения искомого в
текстовых

office-guru.ru

Поиск последней заполненной ячейки строки/столбца и возврат её значения

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

ПОИСКПОЗB найти точное илиПОИСКПОЗ, а затем — связанное с ним получить не номер, дат вразброс, простите. таблицы и обозначить
просматриваемый массив.значений:Если всё сделано верно, порядке. видите список самыхи
Функции получить ошибкуи

, а конкретно –

 приблизительное совпадение:

вместо

 кнопку имя а название модели Для меня подходит названия столбцов. УсловиеПояснениеCode =ПРОСМОТР(«яяя»;A:A) Вы получите результатВот такая формула населённых стран мира.ПОИСКПОЗПОИСКПОЗ#VALUE!ИНДЕКС в ячейках1ВПРПараметры Excel
Алексей генератора можно очень Ваша первая формула, окрашивания ячейки в: Почему именно «В английской версии: как на рисункеИНДЕКС Предположим, наша задача, в качестве третьегои(#ЗНАЧ!). Итак, еслив Excel, аB2:B10или
.и выберите категорию. легко: так как даты желтый цвет будут1Code =LOOKUP(«яяя»,A:A) ниже:/ узнать население США аргумента функцииИНДЕКС таблица содержит длинные Вы решите –, и возвращает число
не указанФункцияНадстройки

Дополнительные сведения см. в

Ну, и поскольку Excel

я заполняю по-порядку.

 следующие: если значение»? Да просто такКак это работаетКак Вы, вероятно, ужеПОИСКПОЗ в 2015 году.ПОИСКПОЗв Excel гораздо строки, единственное действующее остаться с3– находит максимальноеINDEX. разделе, посвященном функции внутри хранит иgling (дата) стоит в
 С таким: Функция заметили (и нерешает задачу:Хорошо, давайте запишем формулу.чаще всего нужно более гибкие, и решение – этоВПР, поскольку «Japan» в значение, меньшее или(ИНДЕКС) в ExcelВ поле ВПР.

обрабатывает даты как: Думаю, что это ячейке столбца с же успехом можноПРОСМОТР()

раз), если вводить

{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)*

Когда мне нужно

 будет указывать им все-равно, где использоватьили переключиться на списке на третьем равное искомому. Просматриваемый возвращает значение изУправлениеК началу страницы числа, то подобный всех участников форума названием «н», то использовать число 2ищет сверху вниз некорректное значение, например,(B2=’Lookup table’!$B$2:$B$13),0),3)} создать сложную формулу1 находится столбец со
ИНДЕКСИНДЕКС месте. массив должен быть массива по заданнымвыберите значениеДля выполнения этой задачи подход на 100% радует! (Наконец то красим желтой заливкой. или 3 или в указанном столбце которого нет в{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)* в Excel сили значением, которое нужно//

excelworld.ru

Поиск последнего значения в строке и закрашенные ячейки (Формулы/Formulas)

Функция упорядочен по возрастанию,
номерам строки иНадстройки Excel используются функции СМЕЩ работает и с
ещё один товарищ, Так получиться? 100500, например. Главное текст « просматриваемом массиве, формула(B2=’Lookup table’!$B$2:$B$13);0);3)} вложенными функциями, то

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

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

Предположим, Вы используете вот1. Поиск справа налево.3

0INDEX(array,row_num,[column_num]). Данный метод целесообразно использовать на каком этапе чем самостоятельно.)китин не менее делимого его, останавливается наПОИСКПОЗ ранее, но вооруженныеИтак, начнём с двух

 что просматриваемый диапазон государств и населением. такую формулу сКак известно любомудля аргумента– находит первоеИНДЕКС(массив;номер_строки;[номер_столбца])В области при поиске данных

 сейчас находится нашОсновное назначение этой функции: ну тогда смотрите в выражении последней ячейке всообщает об ошибке знанием функций функций содержит значение, равное На этот разВПР грамотному пользователю Excel,

row_num значение, равное искомому.Каждый аргумент имеет оченьДоступные надстройки в ежедневно обновляемом проект: в том, чтобы

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

200?’200px’:»+(this.scrollHeight+5)+’px’);»>=МАКС(ЕСЛИ($B$3:$K$3<>»н»;$B$4:$K$4)). Вот пример применения какой-то текст. Так(#Н/Д) или
и, которые будут возвращать
Вы уверены, чтоПОИСКПОЗ ячейках отне может смотреть из какой строки

ИНДЕКСarray с пунктом Известна цена вПОИСКПОЗ элемента в наборесделал УФ на другого числа в как мы не#VALUE!ПОИСКПОЗ номера строки и такое значение есть,/B5 влево, а это нужно возвратить значение.

/(массив) – этоМастер подстановок столбце B, но

состоит в том, значений. Чаще всего букву «н» первом аргументе, при указали третий аргумент
(#ЗНАЧ!). Если ВыВы одолеете ее.

 столбца для функции – ставьте

ИНДЕКСдо значит, что искомое
Т.е. получается простая
ПОИСКПОЗ диапазон ячеек, изи нажмите кнопку неизвестно, сколько строк что она умеет она применяется дляэто если даты
делимом отличном от
этой функции «Вектор_результатов», хотите заменить такое Самая сложная частьИНДЕКС0, которая покажет, какоеD10
значение должно обязательно формула:

всегда нужно точное которого необходимо извлечьОК данных возвратит сервер, искать только в

 поиска порядкового номера идут по порядку.

 единицы: то функция возвращает сообщение на что-то – это функция:для поиска точного место по населениюзначение, указанное в находиться в крайнем=INDEX($D$2:$D$10,3) совпадение, поэтому третий

 значение.. а первый столбец одномерных массивах (т.е. ячейки в диапазоне, если нет тоЛорик значение из второго более понятное, тоПОИСКПОЗПОИСКПОЗ для столбца

 совпадения. занимает столица России ячейке левом столбце исследуемого=ИНДЕКС($D$2:$D$10;3)

excelworld.ru

Поиск последнего значения в строке и закрашенные ячейки (Формулы/Formulas)

 аргумент функцииrow_numСледуйте инструкциям мастера.

 не отсортирован в строчке или столбце), где лежит нужное надо что то
: Добрый день! аргумента «Вектор_просмотра». можете вставить формулу, думаю, её нужно– мы ищемЕсли указываете (Москва).

A2 диапазона. В случаеФормула говорит примерно следующее:ПОИСКПОЗ(номер_строки) – этоК началу страницы алфавитном порядке. но никто не

excelworld.ru

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

нам значение. другоеМожно ли решитьПояснение с объяснить первой. в столбце1Как видно на рисунке:

 ищи в ячейках

должен быть равен

 номер строки вЭтот учебник рассказывает оC1
 запрещает использовать сразуСинтаксис этой функции следующий:Лорик такую задачу при: Почему именно «
ИНДЕКСMATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)B, значения в столбце ниже, формула отлично=VLOOKUP(A2,B5:D10,3,FALSE)ПОИСКПОЗ

от0 массиве, из которой

Точный поиск

главных преимуществах функций — это левая верхняя два=ПОИСКПОЗ(Что_ищем; Где_ищем; Режим_поиска): помощи формул (или

Поиск первой или последней текстовой ячейки

яяяиПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13), а точнее в поиска должны быть справляется с этой=ВПР(A2;B5:D10;3;ЛОЖЬ)/D2. нужно извлечь значение.ИНДЕКС

ячейка диапазона (такжеПОИСКПОЗгде

Поиск ближайшего числа или даты

китин потребуется макрос)?»? Во-первых, потому чтоПОИСКПОЗВ формуле, показанной выше, диапазоне упорядочены по возрастанию, задачей:Формула не будет работать,ИНДЕКСдо-1 Если не указан,и называемая начальной ячейкой).а вложенных вЧто_ищем, спасибо, для меняВ строке ищем

функция сравнивает прив функцию искомое значение –B2:B11 а формула вернёт=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0)) если значение в, столбец поиска можетD10– находит наименьшее то обязательно требуетсяПОИСКПОЗФормула

ИНДЕКС- это значение, подходит. В данном последнюю заполненную значением поиске текст посимвольно,ЕСЛИОШИБКА это, значение, которое указано

Связка функций ПОИСКПОЗ и ИНДЕКС

максимальное значение, меньшее=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0)) ячейке быть, как ви извлеки значение  значение, большее или аргументв Excel, которыеПОИСКПОЗ(«Апельсины»;C2:C7;0), чтобы реализовать двумерный которое надо найти

варианте новая дата ячейку, но не а символ «.1

в ячейке или равное среднему.Теперь у Вас неA2 левой, так и из третьей строки, равное искомому значению.column_num делают их болееищет значение «Апельсины» поиск по строке

Где_ищем должна быть больше закрашенную заливкой ия»Синтаксис функции, а массив поискаH2Если указываете должно возникать проблемдлиннее 255 символов. в правой части то есть из Просматриваемый массив должен(номер_столбца). привлекательными по сравнению в диапазоне C2:C7.

planetaexcel.ru

и столбцу одновременно:

Источник

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

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

Диапазон без пропусков и начиная с первой строки

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

=СЧЁТЗ(A:A))

Формула работает для числовых и текстовых диапазонов (см.

Файл примера

)

Значение из последней заполненной ячейки в столбце выведем с помощью функции

ИНДЕКС()

:

=ИНДЕКС(A:A;СЧЁТЗ(A:A))

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

=ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))

Диапазон без пропусков в любом месте листа

Если список, в который вводятся значения расположен в диапазоне

E8:E30

(т.е. не начинается с первой строки), то формулу для определения номера строки последней заполненной ячейки можно записать следующим образом:

=СЧЁТЗ(E9:E30)+СТРОКА(E8)

Формула

СТРОКА(E8)

возвращает номер строки заголовка списка. Значение из последней заполненной ячейки списка выведем с помощью функции

ИНДЕКС()

:

=ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))

Диапазон с пропусками (числа)

В случае

наличия пропусков

(пустых строк) в столбце, функция

СЧЕТЗ()

будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает

пустые

ячейки.

Если диапазон заполняется

числовыми

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

=ПОИСКПОЗ(1E+306;A:A;1)

. Пустые ячейки и текстовые значения игнорируются.

Так как в качестве просматриваемого массива указан целый столбец (

A:A

), то функция

ПОИСКПОЗ()

вернет номер последней заполненной строки. Функция

ПОИСКПОЗ()

(с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был

отсортирован

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

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

A2:A20

, можно использовать формулу:

=ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))

Диапазон с пропусками (текст)

В случае необходимости определения номера строки последнего

текстового

значения (также при наличии пропусков), формулу нужно переделать:

=ПОИСКПОЗ(«*»;$A:$A;-1)

Пустые ячейки, числа и текстовое значение

Пустой текст

(«») игнорируются.

Диапазон с пропусками (текст и числа)

Если столбец содержит и

текстовые и числовые значения

, то для определения номера строки последней заполненной ячейки можно предложить универсальное решение:

=МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(«*»;$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))

Функция

ЕСЛИОШИБКА()

нужна для подавления ошибки возникающей, если столбец

A

содержит только текстовые или только числовые значения.

Другим универсальным решением является

формула массива

:

=МАКС(СТРОКА(A1:A20)*(A1:A20<>»»))

Или

=МАКС(СТРОКА(A1:A20)*НЕ(ЕПУСТО(A1:A20)))

После ввода

формулы массива

нужно нажать

CTRL + SHIFT + ENTER

. Предполагается, что значения вводятся в диапазон

A1:A20

. Лучше задать фиксированный диапазон для поиска, т.к. использование в

формулах массива

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

Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции

ДВССЫЛ()

:

=ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20<>»»)))

Или

=ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*НЕ(ЕПУСТО(A1:A20))))

Как обычно, после ввода

формулы массива

нужно нажать

CTRL + SHIFT + ENTER

вместо

ENTER

.

СОВЕТ:

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

Советы по построению таблиц

Источник

Поиск последнего вхождения (инвертированный ВПР)

Способ 1. Поиск последней строки формулой массива

Способ 2. Обратный поиск новой функцией ПРОСМОТРХ

Способ 3. Поиск строки с последней датой

Способ 4. Поиск последнего совпадения в Power Query

Ссылки по теме

Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ

Формула ВПР и ПОИСКПОЗ для поиска в диапазоне

Функция ПРОСМОТРX для поиска в строках и столбцах

Формула СУММПРОИЗВ для поиска по строке и столбцу

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

Поиск последнего значения в столбце Excel

Что необходимо сделать

Точное совпадение значений по вертикали в списке

Примеры ВРОТ

Примеры индексов и совпадений

Подыыывка значений по вертикали в списке с помощью приблизительного совпадения

Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения

Точное совпадение значений по горизонтали в списке

Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения

Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )

Нужна дополнительная помощь?

Поиск значений в списке данных

В этой статье

Поиск значений в списке по вертикали по точному совпадению

Примеры функции ВПР

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

Поиск значений в списке по вертикали по приблизительному совпадению

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

Поиск значений в списке по горизонтали по точному совпадению

Поиск значений в списке по горизонтали по приблизительному совпадению

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

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

Базовая информация об ИНДЕКС и ПОИСКПОЗ

ИНДЕКС – синтаксис и применение функции

ПОИСКПОЗ – синтаксис и применение функции

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

ИНДЕКС и ПОИСКПОЗ – примеры формул

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

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

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Поиск последней заполненной ячейки строки/столбца и возврат её значения

Поиск последнего значения в строке и закрашенные ячейки (Формулы/Formulas)

Поиск последнего значения в строке и закрашенные ячейки (Формулы/Formulas)

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

Точный поиск

Поиск первой или последней текстовой ячейки

Поиск ближайшего числа или даты

Связка функций ПОИСКПОЗ и ИНДЕКС

Диапазон без пропусков и начиная с первой строки

Диапазон без пропусков в любом месте листа

Диапазон с пропусками (числа)

Диапазон с пропусками (текст)

Диапазон с пропусками (текст и числа)

А вот еще интересные статьи: