В тази статия ще научим как да получите последния запис по месец в Excel.
Сценарий:
Когато работим с набори от данни, понякога трябва да извлечем някои резултати въз основа на различни критерии. Тук критерият на проблема е, че стойността трябва да е последният запис, съответстващ на даден месец. Проблемът може да се тълкува по два начина.
- Последен запис в колона по месец
- Стойност на последната дата на месец в колона
Сигурно си мислите каква е разликата между двете. Разликата е, че първият проблем извлича последния запис от колоната, който съвпада с името на месеца, докато вторият проблем извлича най -близката стойност на датата от стойността на датата в края на месеца. Ще разбираме и двете ситуации една по една. Първо ще обмислим Как да получим последния запис в колона по месеци.
Как да получите последния запис в колона по месец в Excel?
За това ще използваме функцията TEXT и LOOKUP. Тук използваме атрибут на търсене на функция LOOKUP за последната стойност. Функцията LOOKUP приема 3 аргумента стойност за търсене, масив за търсене и масив от резултати. Затова използваме аргумента за масив за търсене като 1/масив за търсене. За повече подробности вижте синтаксиса на формулата по -долу:
формула синтаксис:
| = LOOKUP (2,1/(TEXT (дати, "mmyyyy") = TEXT (месец, "mmyyyy")), стойности) |
дати: масив от дати в данни
стойности: масив от резултати в данни
месец: критерии за месец
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме тази формула в пример. Тук имаме данни със стойности на дата и цена. За това се нуждаем от последния запис от месец януари 2019 г., който ще бъде последният запис с дата януари 2019 г. Тук за масив с дати и ценови масив използвахме именовани диапазони.
Използвайте формулата:
| = LOOKUP (2,1/(TEXT (дати, "mmyyyy") = TEXT (F9, "mmyyyy")), Цена) |
Обяснение:
- TEXT (дати, "mmyyyy") ще върне масива от стойности във формат "mmyyyy" след преобразуването му в текстови стойности, което е
{ "012019" ; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- ТЕКСТ (F9, "mmyyyy") ще върне стойността на датата (в клетка F9) във формат "mmyyyy" след преобразуване на стойността на датата за търсене, която е "012019" и тази стойност ще бъде съпоставена с гореспоменатия масив.
- Приемането на делението с 1 ще преобразува масива от True стойности в 1s и 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! }
- Сега функцията LOOKUP намира стойността 2 в масива, която няма да бъде намерена. Така че връща записа, съответстващ на последната 1 стойност.
Използвани наименувани диапазони
дати: C3: C15
Цена: D3: D15
Последната цена е 78.48, съответстваща на 31-01-2019. Копирайте формулата в други клетки, като използвате Ctrl + D или плъзнете надолу от долния десен ъгъл на клетката.
Както можете да видите, формулата връща всички необходими стойности. Функцията връща грешка #N/A, ако стойността на месеца за търсене не съвпада с никаква дата. В горния пример записите за дата са подредени. Така че последното въвеждане на месеца съвпада с последното въвеждане на дата в месеца. Сега, ако записите за дата не са сортирани, първо ще сортираме стойностите на датите и ще използваме горната формула.
Как да получите последната дата по месец в колона в Excel?
За това ще използваме функцията VLOOKUP и EOMONTH. Функцията EOMONTH приема стойността на датата на месеца и връща последната дата на необходимия месец. Функцията VLOOKUP намира последната дата от месеца или най -близката предишна дата и връща стойност, съответстваща на тази стойност.
Използвайте формулата:
| = VLOOKUP (EOMONTH (F3,0), таблица, 2) |
Обяснение:
- EOMONTH (F3,0) връща последната дата на същия месец. 0 аргумент, използван за връщане на датата от същия месец.
- Сега стойността за търсене става последната дата за дадения месец.
- Сега стойността ще се търси в първата колона на таблицата и ще се търси последната дата, ако е намерена връща стойността, съответстваща на стойността, и ако не е намерена, тя връща последния съвпадащ резултат, най -близък до стойността на търсене и връща съответния резултат.
- Функцията връща стойността от втората колона на таблицата като третият аргумент е 2.
Тук формулата връща 78.48 като резултат. Сега копирайте формулата в други клетки, като използвате прекия път Ctrl + D или плъзнете надолу от долния десен ъгъл на клетката.
Както можете да видите, формулата работи добре. Има само един проблем. Докато търсим последната дата, съответстваща на априлския месец, функцията връща стойността, съответстваща на март, тъй като няма въвеждане на априлска дата в таблицата. Не забравяйте да хванете тези резултати.
Ето всички забележки от наблюденията относно използването на формулата.
Бележки:
- Използвайте формулата, съответстваща на проблема, посочен в статията.
- Функциите VLOOKUP и LOOKUP са функции, когато стойността за търсене е число и не присъства в масива за търсене, връща стойността, съответстваща само на най -близкото число, по -малко от стойността за търсене.
- Excel съхранява стойностите на датите като числа.
- Горепосочените две функции връщат само една стойност.
- Използвайте четвъртия аргумент във функцията VLOOKUP като 0, за да получите точното съвпадение на стойността за търсене в таблицата.
Надявам се тази статия за Как да получите последния запис по месец в Excel е обяснителна. Намерете още статии за извличане на стойности от таблицата с помощта на формули тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Намерете последния ред със смесени данни в Excel : работа с числа, текст или празна клетка в същия масив. Извлечете последния ред с непразна клетка, като използвате функцията MATCH в Excel.
Намиране на последния ден от даден месец : Функцията EOMONTH връща последната дата от месеца, на дадената стойност на датата.
Как да получите последната стойност в колоната : Последен запис в големи данни, Комбинация от функция CELL и INDEX връща последната стойност в колона или списък с данни.
Разлика с последната непразна клетка : взема алтернативната разлика от последната стойност в списъка с числа, използвайки функцията IF и LOOKUP в Excel.
Намерете последния ред данни с текстови стойности в Excel : В масив от текстови стойности и празни клетки върнете последната стойност в масива, като използвате функцията MATCH и REPT в Excel.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след вземане на произведението на съответните стойности в множество масиви в Excel.
Как да използвате заместващи символи в Excel : Търсене, преброяване, сума, средна и повече математически операции върху клетки, съответстващи на фрази, използвайки заместващи знаци в Excel.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.