Excel находит последнее совпадение в столбце

Обновлено: 21.11.2024

Недавно я работал над составлением повестки дня собрания.

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

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

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

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

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

Если вы хорошо разбираетесь в функциях Excel, вы должны знать, что ни одна функция Excel не может сделать это.

Но вы находитесь в разделе Formula Hack, и здесь мы делаем волшебство.

В этом уроке я покажу вам три способа сделать это.

Это руководство охватывает:

Найти последнее вхождение — используя функцию MAX

Благодарность этому методу принадлежит статье Чарли Кида, признанного экспертом в области Excel.

Вот формула Excel, которая вернет последнее значение из списка:

Вот как работает эта формула:

Функция MAX используется для поиска номера строки последнего совпадающего имени. Например, если имя — Глен, будет возвращено 11, так как оно находится в 11-й строке. Поскольку наш список начинается со второй строки и далее, из него вычитается 1. Таким образом, позиция последнего появления слова Глен — 10 в нашем списке. используется, чтобы гарантировать, что вам не нужно использовать Control + Shift + Enter, поскольку СУММПРОИЗВ может обрабатывать формулы массива. теперь используется для поиска даты последнего совпадающего имени.

Найти последнее вхождение — с помощью функции ПРОСМОТР

Вот еще одна формула для выполнения той же задачи:

Вот как работает эта формула:

Искомое значение равно 2 (вы поймете, почему... продолжайте читать)
Диапазон поиска: 1/($A$2:$A$14=$D$3). Возвращает 1, когда находит совпадающее имя, и ошибку, если не находит. Таким образом, вы получите массив. Например, если искомое значение — Глен, массив будет .
Третий аргумент ([result_vector]) – это диапазон, из которого выдается результат, в данном случае это даты.

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

В этом случае искомое значение равно 2, и в нашем массиве мы получим только 1 или ошибки. Таким образом, он сканирует весь массив и возвращает позицию последней единицы, которая является последним совпадающим значением имени.

Найти последнее вхождение — с помощью пользовательской функции (VBA)

Позвольте мне также показать вам другой способ сделать это.

Мы можем создать пользовательскую функцию (также называемую пользовательской функцией) с помощью VBA.

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

Я создал простую формулу (во многом похожую на формулу ВПР).

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

Вот формула, которая даст вам результат:

Формула принимает три аргумента:

Искомое значение (это будет имя в ячейке D3)
Диапазон поиска (это будет диапазон, в котором есть имена и даты — A2:B14)
Номер столбца (это столбец, из которого мы хотим получить результат)

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

Вот код формулы:

Вот шаги для размещения этого кода в редакторе VB:

Теперь формула будет доступна на всех листах рабочей книги.

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

Если функция ВПР находит более одного совпадения, вы получите первое или последнее совпадение? Это вопрос с подвохом. Это зависит от обстоятельств :) В этой статье подробно объясняется эта запутанная тема с множеством примеров.

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

Например, предположим, что мы используем ВПР для поиска цены на "зеленый" в приведенных ниже данных. Какую цену мы получим?

Прочитайте ответ и другие интересные примеры.

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

Точное совпадение = первое

Выполняя точное соответствие, вы всегда получаете первое совпадение, и точка. Неважно, отсортированы данные или нет. На приведенном ниже экране значение поиска в E5 выделено красным цветом. Функция ВПР в режиме точного совпадения возвращает цену первого совпадения:

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

Приблизительное соответствие = последнее

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

На приведенном ниже экране функция ВПР настроена на режим приблизительного соответствия, а цвета отсортированы. ВПР возвращает цену последней «зелени»:

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

Приблизительное совпадение + несортированные данные = опасность

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

Чтобы проиллюстрировать эту проблему, см. пример ниже. Данные не отсортированы, и функция ВПР без указания четвертого аргумента по умолчанию дает приблизительное совпадение. Обратите внимание, что нет "красного" с ценой 17,00 долларов США, но функция ВПР с радостью возвращает этот неверный результат:

По этой причине я рекомендую всегда явно задавать последний аргумент функции ВПР: ИСТИНА = приблизительное совпадение, ЛОЖЬ = точное совпадение. Аргумент является необязательным, но указание значения заставляет вас задуматься об этом и обеспечивает визуальное напоминание в будущем.

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

"Нормальное" приблизительное совпадение

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

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

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

Практическое применение

Как мы можем использовать поведение, описанное выше, в практической ситуации? Что ж, один из распространенных сценариев — это поиск «последней» или «последней» записи для элемента. Например, ниже мы используем функцию ВПР в режиме приблизительного соответствия, чтобы найти последнюю цену на сандалии. Данные уведомлений сортируются по товарам, а затем по дате, поэтому последняя цена для данного товара отображается последней:

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

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

Функция ПРОСМОТР

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

Последнее совпадение с несортированными данными

Что делать, если вам нужно последнее совпадение, но данные не отсортированы по значению поиска? Другими словами, вы хотите применить критерии для поиска соответствия, и вам просто нужен последний элемент данных, соответствующий вашим критериям? На самом деле это тот случай, когда функция ПРОСМОТР сияет, потому что ПРОСМОТР может обрабатывать операции с массивами изначально, без управления + сдвиг + ввод. Это означает, что мы можем динамически создавать поисковый массив для поиска нужных данных с помощью простых логических выражений.

Например, взгляните на приведенную ниже формулу:

Эта формула находит последние цены на сандалии в несортированных данных.

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

В результате получается массив значений ИСТИНА и ЛОЖЬ, где ИСТИНА соответствует элементам, которые являются "сандалиями", а ЛОЖЬ соответствует всем остальным значениям:

Далее мы делим число 1 на этот массив. Во время деления ИСТИНА становится 1, а ЛОЖЬ становится нулем, поэтому вы должны визуализировать операцию следующим образом:

Не волнуйтесь, у этого безумия есть метод :)

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

Функция ПРОСМОТР автоматически игнорирует ошибки, поэтому единственное, что остается для сравнения, это единицы. Он будет сканировать 1 в поисках 2, которые никогда не будут найдены. Когда он достигает конца массива, он «отступает» к последнему допустимому значению — последнему 1 — что соответствует последнему совпадению на основе предоставленных критериев.

Версия массива ИНДЕКС и ПОИСКПОЗ

Преимущество функции ПРОСМОТР заключается в том, что она может обрабатывать операции с массивами, описанные выше, без необходимости ввода в виде формулы массива с помощью клавиш Ctrl + Shift + Enter. Однако вы, безусловно, можете использовать формулу массива, если хотите. Вот эквивалентная формула ИНДЕКС и ПОИСКПОЗ, которую нужно вводить с помощью клавиш Ctrl + Shift + Enter:

Последняя непустая ячейка

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

Поиск n-го совпадения? Все совпадения?

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

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

Что дальше?

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

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

Оглавление

1. Найдите последнее совпадающее значение

Формула в ячейке F3 выполняет поиск и находит последний элемент, возвращая соответствующее значение из столбца C той же строки.

Например, элемент "SV" находится в ячейках B3, B6 и B10. Последнее совпадающее значение находится в ячейке B10, а соответствующее значение в столбце C равно "40".

Формула в ячейке F3:

Я рекомендую функцию XLOOKUP, если вы используете Excel 365.

1.1 Объяснение формулы в ячейке F3

Шаг 1. Сравните значения с поисковым значением

Знак равенства сравнивает значение со значением. Вы также можете сравнивать одно значение с несколькими значениями одновременно. Вот что здесь происходит.

Шаг 2. Разделите 1 на результат

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

Шаг 3. Поиск последнего совпадающего значения

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

2. Найти последнее совпадающее значение — два условия И — логика

2.1 Вопрос

DonW спрашивает:
Хорошо, вы показали это для обычных диапазонов. как насчет таблиц. У меня есть таблица, похожая на: ID Имя Дата
1001 Джо Смит 01.05.2017
1002 Джон Доу 02.05.2017
1001 Джо Смит 17.05.2017 2017
1003 Джейн Доу 18.05.2017
1001 Джо Смит 20.05.2017

2.2 Формула

Приведенная ниже формула позволяет выполнять поиск по критериям и возвращать последнюю соответствующую запись в таблице.

Например, 1001 и Джо Смит находятся в строках 3, 5 и 7. Запись в строке 7 является последней записью в таблице, поэтому формула возвращает дату (2017-05-17) из строки 7 в ячейке. G4.

Формула в ячейке G4:

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

2.3 Объяснение формулы в ячейке G4

Шаг 1. Создание первого логического выражения (ID)

Мы хотим найти все, чтобы найти значения в столбце B, равные 1001. Знак равенства позволяет сравнивать значение со значениями без учета регистра.

Шаг 2. Создание второго логического выражения (ИМЯ)

Второе логическое выражение проверяет, равны ли значения в диапазоне ячеек C3:C12 "Джо Смиту"

Шаг 3. Умножение массивов

Оба условия должны быть выполнены, поэтому мы должны умножить (*) массивы. Если бы мы хотели, чтобы выполнялось хотя бы одно из двух условий, мы бы добавили массивы (+)

Шаг 4. Деление на ноль возвращает ошибку

Шаг 5. Найдите последнее значение в массиве

Функция ПРОСМОТР находит последнее значение в массиве и возвращает соответствующее значение в той же строке в диапазоне ячеек D3:D12.

и возвращает 2017-05-17 (42872) в ячейке G4.

Находит значение в отсортированном диапазоне ячеек и возвращает значение в той же строке.

3. Найти последнее совпадающее значение — два условия ИЛИ — логика

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

Например, первое условие указано в ячейке G2, а второе — в ячейке G3. Формула начинается с последней строки (12) и идет вверх. «Джейн Доу» — это первое совпадение в ячейке C11, формула возвращает соответствующее значение «5/5/2017» из столбца D в той же строке (11).

Формула в ячейке G4:

3.1 Объяснение формулы в ячейке G4

Шаг 1. Сравните первое искомое значение со значениями

Знак равенства — это логический оператор, означающий, что он позволяет сравнивать значение со значениями без учета регистра. Результатом является логическое значение или логическое значение, в данном случае несколько значений.

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

Сегодня я покажу вам, как получить последнее совпадающее значение, изображение выше демонстрирует эту формулу в ячейке E6. Он ищет значение BB, и последнее совпадающее значение находится в строке 12, соответствующее значение в столбце C равно 10, и это значение возвращается в ячейке E6.

Формула массива в ячейке E6:

Чтобы ввести формулу массива, введите формулу в ячейку, затем одновременно нажмите и удерживайте клавиши CTRL + SHIFT, а затем один раз нажмите Enter. Отпустите все ключи.

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

Объяснение формулы в ячейке E6

Шаг 1. Проверьте, равны ли значения искомому значению

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

Шаг 2. Разделите 1 на массив

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

Шаг 3. Функция ПОИСКПОЗ

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

Шаг 4. Функция ИНДЕКС

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

Получить файл Excel *.xlsx

Если вы не хотите использовать формулу массива, прочитайте эту статью:

Еженедельный блог EMAIL

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

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

Статьи по теме

В этой статье показано, как получить значение из набора данных на основе нескольких условий в нескольких столбцах. С.Бабу спрашивает: […]

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

Читайте также: