Excel формула поиск значения с наиболее ранней датой
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как "кузьмина" или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку "Перенос текста" (вкладка "Главная", группа "Выравнивание").
Как найти самые ранние и самые поздние даты в диапазоне в Excel?
В столбце дат нелегко быстро узнать самую раннюю и самую позднюю дату, если вы не можете отсортировать даты. На самом деле, есть несколько хитрых способов узнать самые ранние и самые поздние даты в Excel легко и удобно.
- Найдите самые ранние и самые свежие даты с функциями
- Найдите самые ранние и последние даты с Kutools for Excel
- Найдите самую раннюю и самую позднюю даты в каждой строке / столбце Excel
Найдите самые ранние и самые свежие даты с функциями
Предположим, вы хотите узнать самую раннюю дату и последнюю дату в диапазоне A1: D7, см. Следующий снимок экрана:
1. Функция Min для поиска самой ранней даты
В пустой ячейке введите формулу = МИН (A1: D7) , и нажмите Enter ключ. Он вернет самые ранние даты в активной ячейке как можно скорее.
2. Небольшая функция для поиска самой ранней даты
Вы также можете применить формулу = МАЛЕНЬКИЙ (A1: D7,1) чтобы быстро получить самые ранние даты.
3. Максимальная функция для поиска последней даты
Если вы хотите узнать последние даты в диапазоне, вы можете ввести формулу = МАКС (A1: D7) , и нажмите Enter ключ.
4. Большая функция для поиска последней даты
Эта формула = НАИБОЛЬШИЙ (A1: D7,1) поможет вам быстро узнать самые свежие даты.
Найдите самые ранние и последние даты с Kutools for Excel
Если у вас есть Kutools for Excel установлен, его Sвыбрать ячейки с максимальным или минимальным значением Инструмент может помочь вам быстро узнать самую раннюю дату и последние даты, не запоминая функции.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите диапазон, в котором вы выберете самую последнюю или самую раннюю форму даты. В этом случае выберите диапазон A1: D7 и нажмите Кутулс > Выберите > Выберите ячейки с максимальным или минимальным значением.
2. Задайте настройки, как показано на следующем снимке экрана:
(1) Если вы хотите узнать последнюю дату, пожалуйста, проверьте Максимальное значение вариант; или проверьте Минимальное значение вариант на самую раннюю дату;
(2) Проверьте Ячейка вариант и Только первая ячейка опцию.
3. Нажмите OK. Тогда сразу будет выделена ячейка с самой поздней или самой ранней датой в выделении.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Найдите самую раннюю или самую позднюю дату в каждой строке / столбце Excel
Этот Sвыбрать ячейки с максимальным или минимальным значением Утилита Kutools for Excel также поддерживает простой выбор максимального или минимального значения в каждом столбце или в каждой строке .:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
Выберите диапазон, из которого вы возьмете самую последнюю или самую раннюю дату, и нажмите Кутулс > Выберите > Выберите ячейки с максимальным или минимальным значением, а затем укажите параметры, как показано на скриншоте ниже:
Проверьте Весь ряд возможность найти последнюю дату в каждой строке:
Проверьте Вся колонка возможность найти последнюю дату в каждой строке:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демо: поиск самых ранних и последних дат в диапазоне или в каждой строке / столбце диапазона
Легко найти максимальное / минимальное значение на основе критериев в другом столбце (по группе) в Excel
Kutools для Excel Расширенные ряды комбинирования Утилита может помочь пользователям Excel быстро и легко определить максимальное значение минимального значения каждого элемента в другом столбце. Полнофункциональная бесплатная 30-дневная пробная версия!
Эта утилита Advanced Combine Rows также может объединять строки и разделять значения по указанным меткам или объединять строки и вычислять их суммы, количество вхождений, максимальные / минимальные значения, средние значения и т. Д.
Как найти самую раннюю или самую последнюю дату на основе критериев в Excel?
Скажем, у вас есть два столбца данных, в которых столбец A содержит продукты, столбец B включает дату, и теперь вы хотите найти самую раннюю или последнюю дату на основе критериев в столбце A, как показано на следующем снимке экрана. Как быстро решить эту проблему в Excel?
Найдите самую раннюю / последнюю дату на основе критериев с формулами
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
В Excel следующие формулы массива могут помочь вам вернуть самую старую или самую последнюю дату с критериями, пожалуйста, сделайте следующее:
Чтобы получить самую раннюю дату на основе критериев:
1. Введите эту формулу: = MIN (IF ($ A $ 2: $ A $ 16 = "Вкладка Office", $ B $ 2: $ B $ 16)) в пустую ячейку, в которой вы хотите получить результат, а затем нажмите Shift + Ctrl + Enter вместе, чтобы вернуть самую старую дату на основе критериев - вкладка Office в столбце A, и вы получите 5-значный номер, см. снимок экрана:
Советы: В этой формуле: A2: A16 содержит ли диапазон данных критерии, на которых вы основывались, "Office Tab»- это критерии, которые вы используете, и B2: B16 включает дату, которую вы хотите вернуть.
2. Затем вы должны преобразовать 5-значное число в формат даты, нажмите Главная, перейдите в генерироватьЯ раскрываюсь, чтобы выбрать Короткое свидание формат, и была отображена самая ранняя дата на основе вкладки Office, см. снимок экрана:
Примечание:
Чтобы получить самую последнюю дату с критериями, вы можете применить эту формулу массива: = MAX (IF ($ A $ 2: $ A $ 16 = «Вкладка Office», $ B $ 2: $ B $ 16)) , и не забудьте нажать Shift + Ctrl + Enter ключи вместе, чтобы получить правильный результат.
Найдите самую раннюю / последнюю дату на основе критериев с помощью Kutools for Excel
Kutools for ExcelАвтора Расширенные ряды комбинирования Функция может помочь вам получить самую раннюю или самую позднюю дату каждого элемента в столбце A без каких-либо формул.
Если вы установили Kutools for Excel, пожалуйста, сделайте следующее:
1. Если вы хотите сохранить исходные данные, вы можете скопировать и вставить данные в другой диапазон, а затем выбрать вставленный диапазон, который вы хотите использовать.
2. Затем нажмите Кутулс > Content > Расширенные ряды комбинирования, см. снимок экрана:
3. В Объединить строки на основе столбца В диалоговом окне щелкните имя столбца, на основе которого вы хотите получить дату, а затем щелкните Основной ключ вариант, см. снимок экрана:
4. Затем щелкните столбец даты, в котором вы хотите вернуть самую последнюю или самую раннюю дату, и выберите Рассчитать > Макс / Мин, ( Выбрать Максимум чтобы получить последнюю дату и Мин. чтобы вернуть самую старую дату), см. снимок экрана:
Советы: Если вы проверите У моих данных есть заголовки, ваш заголовок в диапазоне данных будет проигнорирован автоматически, чтобы получить правильный формат даты, снимите флажок Использовать форматированные значения опцию.
5. Затем нажмите Ok кнопка, самая последняя или самая старая база дат для каждого элемента была возвращена следующим образом:
Демо: найдите самую раннюю / последнюю дату на основе критериев с помощью Kutools for Excel
Kutools for Excel: с более чем 200 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 60 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Если у вас есть список дат на листе, и вы хотите найти самую последнюю или самую старую дату на основе определенного значения, как показано на следующем снимке экрана:
У вас есть идеи, как решить эту проблему в Excel?
Поиск последней или самой ранней даты с формулами в Excel
Чтобы получить относительную самую старую или самую новую дату определенного значения, вы можете применить следующие формулы для ее решения.
Чтобы получить самую старую дату, введите эту формулу: =MIN(IF(A2:A16=E1,B2:B16)) в указанную ячейку, а затем нажмите Shift + Ctrl + Enter вместе, чтобы вернуть правильный ответ, см. снимок экрана:
Чтобы получить самую новую дату, введите эту формулу: =MAX(IF(A2:A16=E1,B2:B16)) в указанную вами ячейку и не забудьте нажать Shift + Ctrl + Enter вместе, чтобы вернуть результат, см. снимок экрана:
Ноты:
1. В приведенных выше формулах:
- A2: A16 столбец, в котором вы хотите искать;
- E1 это значение поиска;
- B2: B16 - столбец, в который будет возвращен результат.
2. Если вы получаете числовой результат после нажатия Shift + Ctrl + Enter ключи, вам просто нужно преобразовать число в формат даты. Выберите числовую ячейку и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек, то в Формат ячеек диалоговое окно, нажмите Время под Число и выберите один формат даты в правом поле списка Тип.
Все классические функции поиска и подстановки типа ВПР (VLOOKUP) , ГПР (HLOOKUP) , ПОИСКПОЗ (MATCH) и им подобные имеют одну важную особенность - они ищут от начала к концу, т.е. слева-направо или сверху-вниз по исходным данным. Как только находится первое подходящее совпадение - поиск останавливается и найденным оказывается только первое вхождение нужного нам элемента.
Что же делать, если нам требуется найти не первое, а последнее вхождение? Например, последнюю сделку по клиенту, последний платёж, самую свежую заявку и т.д.?
Способ 1. Поиск последней строки формулой массива
Если в исходной таблице нет столбца с датой или порядковым номером строки (заказа, платежа. ), то наша задача сводится, по сути, к поиску последней строки, удовлетворяющей заданному условию. Реализовать подобное можно вот такой формулой массива:
- Функция ЕСЛИ(IF) проверяет по очереди все ячейки в столбце Клиент и выводит номер строки, если в ней лежит нужное нам имя. Номер строки на листе нам даёт функция СТРОКА(ROW) , но поскольку нам нужен номер строки в таблице, то дополнительно приходится вычитать 1, т.к. у нас в таблице есть шапка.
- Затем функция МАКС(MAX) выбирает из сформированного набора номеров строк максимальное значение, т.е. номер самой последней строки клиента.
- Функция ИНДЕКС(INDEX) выдаёт содержимое ячейки с найденным последним номером из любого другого требуемого столбца таблицы (Код заказа).
Всё это нужно вводить как формулу массива, т.е.:
- В Office 365 с последними установленными обновлениями и поддержкой динамических массивов - можно просто жать Enter .
- Во всех остальных версиях после ввода формулы придется нажимать сочетание клавиш Ctrl + Shift + Enter , что автоматически добавит к ней фигурные скобки в строке формул.
Способ 2. Обратный поиск новой функцией ПРОСМОТРХ
Я уже писал большую статью с видео про новую функцию ПРОСМОТРХ (XLOOKUP) , которая появилась в последних версиях Office на замену старушке ВПР (VLOOKUP) . При помощи ПРОСМОТРХ наша задача решается совершенно элементарно, т.к. для этой функции (в отличие от ВПР) можно явно задавать направление поиска: сверху-вниз или снизу-вверх - за это отвечает её последний аргумент (-1):
Способ 3. Поиск строки с последней датой
Если в исходных данных у нас есть столбец с порядковым номером или датой, играющей аналогичную роль, то задача видоизменяется - нам требуется найти уже не последнюю (самую нижнюю) строку с совпадением, а строку с самой поздней (максимальной) датой.
Как это сделать с помощью классических функций я уже подробно разбирал, а теперь давайте попробуем использовать мощь новых функций динамических массивов. Исходную таблицу для пущей красоты и удобства тоже заранее преобразуем в "умную" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) .
С их помощью этой "убойной парочки" наша задача решается весьма изящно:
- Сначала функция ФИЛЬТР(FILTER) отбирает только те строки из нашей таблицы, где в столбце Клиент - нужное нам имя.
- Потом функция СОРТ(SORT) сортирует отобранные строки по убыванию даты, чтобы самая последняя сделка оказалась сверху.
- Функция ИНДЕКС(INDEX) извлекает первую строку, т.е. выдает нужную нам последнюю сделку.
- И, наконец, внешняя функция ФИЛЬТР убирает из результатов лишние 1-й и 3-й столбцы (Код заказа и Клиент) и оставляет только дату и сумму. Для этого используется массив констант >, определяющий какие именно столбцы мы хотим (1) или не хотим (0) выводить.
Способ 4. Поиск последнего совпадения в Power Query
Ну, и для полноты картины, давайте рассмотрим вариант решения нашей задачи обратного поиска с помощью надстройки Power Query. С её помощью всё решается очень быстро и красиво.
1 . Преобразуем нашу исходную таблицу в "умную" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) .
2 . Загружаем её в Power Query кнопкой Из таблицы/диапазона на вкладке Данные (Data - From Table/Range) .
3 . Сортируем (через выпадающий список фильтра в шапке) нашу таблицу по убыванию даты, чтобы самые последние сделки оказались сверху.
4 . На вкладке Преобразование выбираем команду Группировать по (Transform - Group By) и задаем группировку по клиентам, а в качестве агрегирующей функции выбираем вариант Все строки (All rows) . Назвать новый столбец можно как угодно - например Подробности.
После группировки получим список уникальных имен наших клиентов и в столбце Подробности - таблицы со всеми сделками каждого из них, где первой строкой будет идти самая последняя сделка, которая нам и нужна:
5 . Добавляем новый вычисляемый столбец кнопкой Настраиваемый столбец на вкладке Добавить столбец (Add column - Add custom column) и вводим следующую формулу:
Здесь Подробности - это столбец, откуда мы берем таблицы по клиентам, а - это номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями ( Record ), где каждая запись - первая строка из каждой таблицы:
Осталось развернуть содержимое всех записей кнопкой с двойными стрелками в шапке столбца Последняя сделка, выбрав нужные столбцы:
. и удалить потом ненужный более столбец Подробности щёлкнув по его заголовку правой кнопкой мыши - Удалить столбцы (Remove columns) .
После выгрузки результатов на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to. ) получим вот такую симпатичную таблицу со списком последних сделок, как и хотели:
При изменении исходных данных результаты нужно не забыть обновить, щёлкнув по ним правой кнопкой мыши - команда Обновить (Refresh) или сочетанием клавиш Ctrl + Alt + F5 .
Читайте также: