image

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

Сценарий:

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

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

Как получить последнюю запись в столбце по месяцам в Excel?

Для этого мы будем использовать link: / excel-text-formulas-the-text-function-in-excel [TEXT function] и link: / lookup-formulas-excel-lookup-function [LOOKUP function] . Здесь мы используем атрибут функции LOOKUP для поиска последнего значения. Функция LOOKUP принимает значение поиска 3 аргументов, массив поиска и массив результатов. Поэтому мы используем аргумент массива поиска как 1 / массив поиска.

Подробнее см. Синтаксис формулы ниже:

синтаксис формулы:

=LOOKUP(2,1/(TEXT(dates,»mmyyyy»)=TEXT(month,»mmyyyy»)),values)

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

Пример:

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

Используйте формулу:

=LOOKUP(2,1/(TEXT(dates,»mmyyyy»)=TEXT(F9,»mmyyyy»)),Price)

Пояснение:

  • link: / excel-text-formulas-the-text-function-in-excel [TEXT] (date, «mmyyyy»)

вернет массив значений в формате «ммгггг» после преобразования его в текстовые значения, который равен \ {«012019»; «012019»; «012019»; «012019»; «022019»; «022019»; «022019»; «022019»; «032019»; «032019»; «032019»; «032019»; «032019»} * link: / excel-text-formulas-the-text-function-in-excel [TEXT] (F9, «mmyyyy»)

вернет значение даты (в ячейке F9) в формате «ммгггг» после преобразования значения даты поиска, равного «012019», и это значение будет сопоставлено с указанным выше массивом.

  • При делении на 1 массив значений True преобразуется в единицы, а значения False — в ошибку # DIV / 0. Итак, мы получим возвращенный массив как.

\ {1; 1; 1; 1; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! } * Теперь функция ПРОСМОТР находит в массиве значение 2, которое не будет найдено. Таким образом, он возвращает запись, соответствующую последнему 1 значению.

image

Именованные диапазоны используемых дат: C3: C15 Цена: D3: D15 Последняя цена 78,48, что соответствует 31-01-2019. Скопируйте формулу в другие ячейки, используя Ctrl + D или перетащив ее из нижнего правого угла ячейки.

image

Как видите, формула возвращает все необходимые значения. Функция возвращает ошибку # Н / Д, если значение месяца поиска не соответствует ни одной записи даты. В приведенном выше примере записи даты отсортированы. Таким образом, последняя запись месяца соответствует последней записи месяца.

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

Как получить последнюю дату записи по месяцам в столбце Excel?

Для этого мы будем использовать link: / формулы-и-функции-введение-vlookup-function [VLOOKUP]

и link: / excel-date-and-time-how-to-use-the-eomonth-function-in-excel [EOMONTH function]. Функция EOMONTH принимает значение даты месяца и возвращает последнюю дату требуемого месяца. Функция VLOOKUP находит последнюю дату месяца или ближайшую предыдущую дату и возвращает значение, соответствующее этому значению.

Используйте формулу:

=

VLOOKUP

(

EOMONTH

(F3,0), table, 2)

Пояснение:

  1. link: / excel-date-and-time-how-to-use-the-eomonth-function-in-excel [EOMONTH]

(F3,0) возвращает последнюю дату того же месяца. 0 аргумент, используемый для возврата даты того же месяца.

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

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

  3. Функция возвращает значение из 2-го столбца таблицы, поскольку 3-й аргумент равен 2.

image

Здесь формула возвращает 78,48 в качестве результата. Теперь скопируйте формулу в другие ячейки, используя сочетание клавиш Ctrl + D или перетащив ее из правого нижнего угла ячейки.

image

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

Вот все заметки по использованию формулы.

Примечания:

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

  2. Excel хранит значения дат в виде чисел.

  3. Две указанные выше функции возвращают только одно значение.

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

Надеюсь, эта статья о том, как получить последнюю запись по месяцам в Excel, будет пояснительной. Дополнительные статьи об извлечении значений из таблицы с помощью формул можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]

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

link: / lookup-formulas-find-the-last-row-with-mixed-data-in-excel [Найти последнюю строку со смешанными данными в Excel]: работа с числами, текстом или пустой ячейкой в ​​одном массиве . Извлеките последнюю строку с непустой ячейкой, используя функцию ПОИСКПОЗ в Excel.

link: / excel-date-time-formulas-find-the-last-day-of-a-given-month [Поиск последнего дня заданного месяца]: функция EOMONTH возвращает последнюю дату месяца, из заданное значение даты.

link: / tips-how-to-get-last-value-in-column [Как получить последнее значение в столбце]: Последняя запись в больших данных. Комбинация функций ЯЧЕЙКА и ИНДЕКС возвращает последнее значение в столбец или список данных.

link: / lookup-formulas-difference-with-the-last-non-blank-cell [Разница с последней непустой ячейкой]: берет альтернативное отличие от последнего значения в списке с числами, используя IF и LOOKUP функция в Excel.

link: / lookup-formulas-find-the-last-row-of-data-in-excel [Найти последнюю строку данных с текстовыми значениями в Excel]: * В массиве текстовых значений и пустых ячеек вернуть последнее значение в массиве с использованием функции ПОИСКПОЗ и ПОВТОР в excel.

link: / summing-excel-sumproduct-function [Как использовать функцию СУММПРОИЗВ в Excel]: возвращает СУММ после взятия произведения соответствующих значений в нескольких массивах в excel.

link: / tips-excel-wildcards [Как использовать подстановочные знаки в excel]: поиск, подсчет, сумма, среднее и другие математические операции над ячейками, совпадающими с фразами, с использованием подстановочных знаков в excel.

Популярные статьи:

link: / tips-if-condition-in-excel [Как использовать функцию IF в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если оно ложно .

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы. link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.