Если последнее значение в строке excel

Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР

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

Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера — 41 и т.д.

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

Универсальным решением будет использование функции ПРОСМОТР (LOOKUP):

У этой функции хитрая логика:

• Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
• Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
• Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.

Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10¹¹ или сто миллиардов).

Если в таблице не числа, а текст, то идея остается той же, но «очень большое число» нужно заменить на «очень большой текст»:

Применительно к тексту, понятие «большой» означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:

1. латиница прописные (A-Z)
2. латиница строчные (a-z)
3. кириллица прописные (А-Я)
4. кириллица строчные (а-я)

Поэтому строчная «я» оказывается буквой с наибольшим кодом и слово из нескольких подряд «яяяяя» будет, условно, «очень большим словом» — заведомо «большим», чем любое текстовое значение из нашей таблицы.

Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже «на ура».

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

• Поиск и подстановка по нескольким условиям (ВПР по 2 и более критериям)
• Поиск ближайшего похожего текста (max совпадений символов)
• Двумерный поиск в таблице (ВПР 2D)

Последняя заполненная ячейка в MS EXCEL

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

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

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

В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой:
=СЧЁТЗ(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<>«»))

После ввода формулы массива нужно нажать CTRL + SHIFT + ENTER. Предполагается, что значения вводятся в диапазон A1:A20. Лучше задать фиксированный диапазон для поиска, т.к. использование в формулах массива ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.

Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() :
=ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20<>«»)))

Как обычно, после ввода формулы массива нужно нажать CTRL + SHIFT + ENTER вместо ENTER.

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

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

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

Как найти последнюю заполненную строку в столбце таблицы Excel

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

Функция ИНДЕКС использована с одним столбцом требует лишь указать один аргумент с номером строки. Третий необязательный для заполнения аргумент в данной ситуации не используется. Функция СЧЁТЗ используется с целью подсчитывания непустых ячеек в столбце B. Ее итоговый результат вычисления следует увеличить на число +1, так как в первой строке пустая ячейка. Функция ИНДЕКС в данном примере возвращает 12-ую строку в столбце B.

Функция СЧЁТЗ подсчитывает ячейки содержащие значения: числа, текстовые строки, даты и все любые другие значения за исключением пустых ячеек. Если ваши данные содержат пустые ячейки, которые разрывают целостность списков, тогда эта формула не будет возвращать правильных результатов вычисления.

Поиск последнего числа в столбце с пустыми ячейками Excel

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

Искомое значение в данной формуле (единица с 308 знаками) – это самое большое число доступное в программе Excel. Так как функция ПРОСМОТР не имела шансов найти большего значения чем искомое, она прекратила свое вычисление на последнем найденном числовом значении, которую и вернула в результат.

Что значит число с буквой E в Excel?

Число такое как, например, 9,99E+307 записано в экспоненциальном формате. Число перед буквой E имеет одну цифру 0-9 и только две цифры после запятой. Число после буквы E значит количество разрядов, на которые следует сместить запятую (307 в данном примере), чтобы получить числовое значение, записанное традиционным способом. Плюс значит, что запятую следует смещать в право, а минус – влево. Например, запись 4,32E-02 означает число, записанное в десятичной дроби 0,0432.

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

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

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

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

В этой статье

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

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

Примеры использования функции ВПР

Дополнительные сведения можно найти в разделе функция ВПР.

Примеры ИНДЕКСов и СОВПАДЕНИй

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

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

Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ.

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

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

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

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

Дополнительные сведения можно найти в разделе функция ВПР.

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

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

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

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

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

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

Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Для выполнения этой задачи используется функция ГПР.

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

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

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

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

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

Загрузка программы-надстройки «Мастер подстановок»

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

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

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

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

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

В столбце с нашими будущими результатами вводим =ПРОСМОТР( и нажимаем fx.

В предложенных режимах функции выбираем первый – «искомое_значение;просматриваемый_вектор;вектор_результатов» и нажимаем ОК.

Далее заполняем аргументы. Функция ПРОСМОТР имеет очень интересную особенность. Если она не находит требуемые искомые значения, то она возвращает последнее справа в указанном диапазоне. Диапазон для поиска находится в просматриваемом векторе. Его мы преобразуем таким образом, чтобы результатами были либо значения, либо ошибки.
Вводя конструкцию 1/(ДИАПАЗОН<>””) (диапазон, не равный пустым ячейкам), мы получим последовательность <1; #ДЕЛ/0…..>. Это даст нам возможность исключить из поиска пустые ячейки. Т.к. в просматриваемом векторе теперь заведомо будут отсутствовать любые искомые значения (кроме «1», ее вводить нельзя), то в искомое значение вводим любое число – например «1111».

=ПРОСМОТР(1111111111;1/(B3:F3<>«»);B3:F3), ее можно скопировать в строку формул и перенастроить под первую строку вашей таблицы, изменяя диапазон B3:F3 в векторах.

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

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

Если Вам необходимо в таблицах, которые имеют неодинаковое количество ячеек в строках и/или столбцах, например таких:

находить последние заполненные ячейки и извлекать из них значения, то в Excel Вы, к сожалению, не найдёте функции типа ВЕРНУТЬ.ПОСЛЕДНЮЮ.ЯЧЕЙКУ()

Вот как это сделать имеющейся в стандартном наборе функций функцией ПРОСМОТР().

1. Для текстовых значений:

В английской версии:

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

Пояснение: Почему именно «яяя«? Во-первых, потому что функция сравнивает при поиске текст посимвольно, а символ «я» в русском языке последний и все предыдущие при сравнении отбрасываются, во-вторых, потому что в русском языке нет такого слова.

Примечание: Вообще-то достаточно использовать и «яя«, но тогда возникает мизерная возможность попасть на таблицу, в которой будет такое слово. Так называются город и река в Кемеровской области. В детстве я был в этом городе и даже купался в этой реке 🙂

2. Для числовых значений:

В английской версии:

Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число «9E+307» и не найдя его, останавливается на последней ячейке в которой есть хоть какое-то число. Так как мы не указали третий аргумент этой функции «Вектор_результатов», то функция возвращает значение из второго аргумента «Вектор_просмотра».

Пояснение: Почему именно «9E+307«? Потому что это максимально возможное число в Excel. Поэтому функция найти его может только в каком-то невероятном случае, в реальной жизни пользователь такими числами просто не оперирует.

3. Для смешанных (текстово-числовых) значений:

В английской версии:

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

Пояснение: Почему именно «1«? Да просто так 🙂 С таким же успехом можно использовать число 2 или 3 или 100500, например. Главное что бы первый аргумент функции был не менее делимого в выражении 1/Диапазон. Вот пример применения другого числа в первом аргументе, при делимом отличном от единицы:

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

​Смотрите также​- это одномерный​ предыдущей, в противном​ возвращаем это значение​в русском языке​ЕСЛИОШИБКА​ – это результат​(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​ Мы хотим знать​Если указаны оба аргумента,​В нескольких недавних статьях​ этом примере значение​ и автоматически проверять​

1. ​Давайте рассмотрим несколько полезных​

2. ​: Разобралась с УФ​​ в сторону макроса,​​», но тогда возникает​​ИНДЕКС​​и сравниваем его​​ПОИСКПОЗ для строки​​ поэтому мы используем​

3. ​INDEX​​Если Вы работаете​​ если удалить или​(ИНДЕКС), должно соответствовать​

   ​ значение этого элемента!​

4. ​ то функция​​ мы приложили все​ ​ возвращается из столбца​ их правильность. Значения,​​ вариантов ее применения​​ самостоятельно, всем огромное​​ или уточнить логику​​ мизерная возможность попасть​

5. ​/​​ со всеми именами​​– мы ищем​​ тип сопоставления​​(ИНДЕКС), из которого​​ с небольшими таблицами,​​ добавить столбец в​

6. ​ значениям аргументов​​Позвольте напомнить, что относительное​​ИНДЕКС​ усилия, чтобы разъяснить​​ D​​ возвращенные поиском, можно​​ на практике.​​ спасибо!​

7. ​ заливки (если она​

​ на таблицу, в​

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​​ для продвинутых пользователей.​​ См. пример ниже.​​ данных и отображения​

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

​ списке:​​вот такой монстр​​ наоборот ставить знак​ был в этом​ выдаст ошибку.​1​A1: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).​ всегда использовать абсолютные​

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

• ​(ВПР)? Есть ли​​INDEX​A1: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)​gling​Code =ПРОСМОТР(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:E11​​INDEX(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+Enter​Lookup table​ этот шаблон на​в комбинации с​ВПР​ значения в 255​ главные преимущества использования​ «Japan» в столбце​, хотите ли Вы​и​Microsoft Office​ значение 5 и возвращает​Так, в предыдущем примере​

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

​.​

office-guru.ru

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

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

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

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

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

​вместо​

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

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

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

​0​​INDEX(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

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

Получить первое не пустое значение в списке

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

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

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

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

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

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

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

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

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

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

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

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

Получить первое текстовое значение в списке

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

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

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

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

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

Получить первое текстовое значение с ГПР

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

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

= ГПР ( «*»; С5: Е5; 1; 0 )

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

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

{ = МАКС( ЕСЛИ ( Величины = знач ; СТРОКА(величина) — СТРОКА(ИНДЕКС( Величины; 1 ; 1 )) + 1 )) }

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

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

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

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

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

= ПРОСМОТР( 2 ; 1 / ПОИСК ( вещи ; А1 ); вещи )

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

В примере формула в С5:

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

Получить n-е совпадение

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

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

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

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

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

Получить n-ое совпадение с ИНДЕКС/ПОИСКПОЗ

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

Чтобы получить n-ое совпадение, используя ИНДЕКС и ПОИСКПОЗ, вы можете использовать формулу массива с функциями ЕСЛИ и НАИМЕНЬШИЙ, чтобы выяснить номер строки совпадения.

Получить n-ое совпадение с ВПР

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

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

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

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

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

Если ячейка содержит одну из многих вещей

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

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

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

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

Поиск первой ошибки

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

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

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

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

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

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

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

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

Поиск следующего наибольшего значения

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

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

=ИНДЕКС(C5:C9;ПОИСКПОЗ(F4;B5:B9)+1)

Несколько совпадений в списке, разделенных запятой

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

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

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

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

Частичное совпадение чисел с шаблоном

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

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

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

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

Решение

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

Другой вариант

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

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

Частичное совпадение с ВПР

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

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

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

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

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

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

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

В примере формула в Е7:

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

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

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

Скачать пример рабочей книги

Загрузите образец книги

Из этого туториала Вы узнаете, как найти последнее значение в столбце или строке в Excel.

Последнее значение в столбце

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

Давайте пройдемся по этой формуле.

Часть формулы B: B ”” возвращает массив, содержащий значения True и False: {FALSE, TRUE, TRUE,…}, при тестировании каждая ячейка в столбце B пуста (FALSE).

Эти логические значения преобразуются в 0 или 1 и используются для деления 1.

Это lookup_vector для функции LOOKUP. В нашем случае lookup_value равно 2, но наибольшее значение в lookup_vector равно 1, поэтому функция LOOKUP будет соответствовать последней 1 в массиве и вернет соответствующее значение в result_vector.

Если вы уверены, что в столбце есть только числовые значения, данные начинаются с строки 1, а диапазон данных непрерывен, вы можете использовать несколько более простую формулу с функциями ИНДЕКС и СЧЁТ.

Функция COUNT возвращает количество ячеек, заполненных данными в непрерывном диапазоне (4), а функция INDEX, таким образом, дает значение ячейки в этой соответствующей строке (4-я).

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

Функция ISBLANK возвращает массив, содержащий значения True и False, соответствующие единицам и нулям. Функция NOT изменяет True (т.е. 1) на False и False (т.е. 0) на True. Если мы инвертируем этот результирующий массив (при делении 1 на этот массив), мы получим результирующий массив, содержащий снова # DIV / 0! ошибок и единиц, которые можно использовать как поисковый массив (lookup_vector) в нашей функции LOOKUP. Функциональные возможности функции LOOKUP будут такими же, как и в нашем первом примере: она возвращает значение вектора результатов в позиции последней единицы в поисковом массиве.

Когда вам нужно вернуть номер строки с последней записью, вы можете изменить формулу, используемую в нашем первом примере, вместе с функцией ROW в вашем result_vector.

Последнее значение в строке

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

Посмотрим, как работает эта формула.

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

Мы используем функцию MATCH, чтобы «подсчитать», сколько значений ячеек меньше 1 + максимум всех значений в строке 2, начиная с B2.

Lookup_value функции MATCH является максимальным из всех значений в строке2 + 1. Поскольку это значение, очевидно, не существует в строке2, а match_type установлено в 1 (меньше или равно lookup_value), функция MATCH вернет Позиция последней «проверенной» ячейки в массиве, то есть количество ячеек, заполненных данными в диапазоне B2: XFD2 (XFD — самый последний столбец в новых версиях Excel).

СМЕЩЕНИЕ Функция

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

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

Поиск последней ячейки — все о данных

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

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

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

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

# 1 — Метод Range.End

Range.End () очень похож на сочетание клавиш Ctrl + Arrow. В VBA вы можете использовать этот метод, чтобы найти последнюю непустую ячейку в той же строке или столбце.

Диапазон: конец примера кода VBA

Sub Last_Cell () ‘Найти последнюю непустую ячейку в той же строке или столбце Dim lRow As Long Dim lCol As Long’ Найти последнюю непустую ячейку в столбце A (1) lRow = Cells (Rows.Count, 1) . End (xlUp) .Row ‘Найти последнюю непустую ячейку в строке 1 lCol = Cells (1, Columns.Count) .End (xlToLeft) .Column MsgBox «Последняя строка:» & lRow & vbNewLine & _ «Последний столбец : «& lCol Fine Sub

Чтобы найти последнюю использованную строку в столбце, этот метод начинается с последней ячейки в столбце и идет вверх (xlUp), пока не найдет первую непустую ячейку.

Оператор Rows.Count возвращает количество всех строк на листе. Поэтому на практике мы указываем последнюю ячейку в столбце A листа (ячейка A1048567) и идем вверх, пока не найдем первую непустую ячейку.

То же самое и с поиском в последнем столбце. Начните с последнего столбца строки, затем двигайтесь влево, пока не будет найдена последняя непустая ячейка в столбце. Columns.Count возвращает общее количество столбцов на листе. Итак, начнем с последнего столбца и будем двигаться влево.

Аргумент метода End указывает, в каком направлении двигаться. Возможные варианты: xlDown, xlUp, xlToLeft, xlToRight.

Pro of Range.End

• Range.End прост в использовании и понимании, поскольку он работает так же, как сочетания клавиш Ctrl + Arrow.
• Его можно использовать для поиска первой пустой ячейки или последней непустой ячейки в той же строке или столбце.

Минусы Range.End

• Range.End работает только с одной строкой или столбцом. Если у вас есть диапазон данных, содержащий пробелы в последней строке или столбце, может быть сложно определить, в какой строке или столбце запустить метод.
• Если вы хотите найти последнюю использованную ячейку, вам нужно оценить как минимум два оператора. Один для поиска последней строки и один для поиска последнего столбца. Затем их можно объединить для ссылки на последнюю ячейку.

Вот справочные статьи для Range.End

• Страница справки MSDN для Range.End
• Руководство MSDN для перечислений xlDirection

# 2 — Метод Range.Find

Метод Range.Find: я предпочитаю этот метод для поиска последней строки, столбца или ячейки. Он самый универсальный, но и самый устрашающий.

У Range.Find много аргументов, но пусть это вас не пугает. Как только вы узнаете, что они делают, вы можете использовать Range.Find для многих вещей в VBA.

Range.Find — это в основном способ программирования меню поиска в Excel. Он делает то же самое, и большинство аргументов Range.Find — это пункты меню «Найти.

Гамма — найти образец кода

Ниже приведен код для поиска последней непустой строки.            

Sub Last_Cell_Find () ‘Находит последнюю непустую ячейку в листе / диапазоне. Dim lRow As Long Dim lCol As Long lRow = Cells.Find (What: = «*», _ After: = Range («A1»), _ LookAt: = xlPart, _ LookIn: = xlFormulas, _ SearchOrder: = xlByRows, _ SearchDirection: = xlPrevious, _ MatchCase: = False) .Row MsgBox «Последняя строка:» & lRow End Sub

Range.Find метод. Пояснения

Метод Find ищет первую непустую ячейку («*»). Звездочка — это подстановочный знак, который выполняет поиск любого текста или числа в ячейке.

Начиная с ячейки A1, вернитесь назад (xlPrevious) и фактически начните искать в последней ячейке рабочего листа. Затем он перемещается справа налево (xlByRows) и прокручивает каждую строку, пока не найдет непустую ячейку. Если обнаружено непустое значение, он останавливается и возвращает номер строки.

Вот подробное объяснение каждой темы.

• Что: = ”*” — звездочка — это подстановочный знак, который соответствует любому тексту или числу в ячейке. По сути, это то же самое, что найти непустую ячейку.
• After: = Range («A1») — запускает поиск после ячейки A1, первой ячейки листа. Это означает, что A1 не будет искать. Поиск начнется после A1, и следующая ячейка, которую он ищет, зависит от SearchOrder и SearchDirection. Этот аргумент можно изменить, чтобы он начинался в другой ячейке, просто помните, что поиск фактически начинается в ячейке, следующей за указанной.
• LookAt: = xlPart — проверяет любую часть текста в ячейке. Другой вариант — xlWhole, который попытается сопоставить все содержимое ячейки.
• LookIn: = xlFormulas — заставляет Find искать формулы, что является важным аргументом. Другой вариант — xlValues, который будет искать только значения. Если у вас есть формулы, которые возвращают пробелы (= IF (A2> 5, «Ok», «»), вы можете рассматривать это как непустую ячейку. Если вы укажете LookIn как xlFormulas, эта формула будет считаться непустой даже если возвращаемое значение пустое.
• SearchOrder: = xlByRows — указывает Find выполнять поиск в каждой строке целиком перед переходом к следующей. Направление поиска слева направо или справа налево зависит от аргумента SearchDirection. Другой вариант — xlByColumns, который используется при поиске последнего столбца.
• SearchDirection: = xlPrevious — указывает направление поиска, XlPrevious означает, что поиск будет выполняться справа налево или снизу вверх. Другой вариант — xlNext, который движется в противоположном направлении.
• MatchCase: = False — указывает Find не учитывать прописные или строчные буквы. Если установлено значение True, это помогает. Этот аргумент не требуется для этого сценария.

Да, я знаю, что это много, но я надеюсь, что вы лучше понимаете, как использовать эти аргументы, чтобы найти что-то на листе.

Pro of Range.Find

• Поиск выполняет поиск во всем диапазоне последней непустой строки или столбца. Он НЕ ограничен одной строкой или столбцом.
• Последняя строка в наборе данных может содержать пробелы, и Range.Find все равно найдет последнюю строку.
• Аргументы можно использовать для поиска в разных направлениях и определенных значений, а не только пустых ячеек.

Минусы Range.Find

• это ужасно. Метод содержит 9 аргументов. Хотя требуется только один из этих аргументов (What), вы должны привыкнуть использовать по крайней мере первые 7 аргументов. В противном случае метод Range.Find по умолчанию будет использовать последние использованные настройки в поле поиска. Это важно. Если вы не предоставите необязательные аргументы для LookAt, LookIn и SearchOrder, метод Find будет использовать последние использованные параметры в поле поиска Excel.
• Для поиска последней ячейки требуются две инструкции. Один для поиска последней строки и один для поиска последнего столбца. Поэтому вам нужно объединить их, чтобы найти последнюю ячейку.

Macro Recorder поможет!

Range.Find по-прежнему остается моим любимым методом поиска последней ячейки из-за его универсальности. Но есть что написать и вспомнить. К счастью, вам это не нужно.

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

1. Запустить средство записи макросов
2. Нажмите Ctrl + F
3. Затем нажмите кнопку «Найти далее»

Код для метода Find со всеми аргументами будет сгенерирован регистратором макросов.

Используйте настраиваемую функцию для метода поиска

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

# 3 — Range.SpecialCells (xlCellTypeLastCell)

Метод SpecialCells делает то же самое, что и нажатие сочетания клавиш Ctrl + End, и выбирает последнюю использованную ячейку на листе.

Пример кода SpecialCells (xlCellTypeLastCell)

Sub Range_SpecialCells_Method () Диапазон MsgBox («A1»). SpecialCells (xlCellTypeLastCell) .Address End Sub

На самом деле это самый простой способ найти последнюю использованную ячейку. Однако этот метод находит последнюю использованную ячейку, которая может отличаться от последней непустой ячейки.

Часто нажимайте Ctrl + End на клавиатуре, и вы попадете в ячейку в нижней части листа, которая определенно не используется. Это может произойти по разным причинам. Распространенной причиной является изменение свойств форматирования этой ячейки. Простое изменение размера шрифта или цвета заливки ячейки помечает ее как используемую ячейку.

Профи диапазона

• Вы можете использовать этот метод, чтобы найти «используемые» строки и столбцы в конце листа и удалить их. Сравнение результата Range.SpecialCells с результатом Range.Find для непробелов может помочь вам быстро определить, есть ли на вашем листе неиспользуемые строки или столбцы.
• Удаление неиспользуемых строк / столбцов может уменьшить размер файла и увеличить полосу прокрутки.

Минусы Range.SpecialCells

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

Другие способы найти последнюю ячейку

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

Оставьте комментарий ниже, если у вас есть какие-либо вопросы или вы все еще не можете найти последнюю ячейку, я буду рад помочь!

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