I den här artikeln lär vi oss hur du får den sista posten per månad i Excel.
Scenario:
När vi arbetar med datamängder behöver vi ibland extrahera några resultat baserat på olika kriterier. Här är problemkriterierna värdet måste vara den sista posten som motsvarar en given månad. Problemet kan tolkas på två sätt.
- Sista posten i kolumn efter månad
- Månadens sista datumvärde i kolumnen
Du måste tänka på vad som är skillnaden mellan de två. Skillnaden är att det första problemet extraherar den sista posten från kolumnen som matchar månadsnamnet medan det andra problemet extraherar det närmaste datumvärdet från datumvärdet vid slutet av månaden. Vi kommer att förstå båda situationerna en efter en. Först kommer vi att överväga hur man får den sista posten i kolumn för månad.
Hur får jag den sista posten i kolumnen efter månad i Excel?
För detta kommer vi att använda TEXT -funktionen och LOOKUP -funktionen. Här använder vi ett attribut för LOOKUP -funktionssökning för det sista värdet. LOOKUP -funktionen tar tre argument, uppslagsvärde, uppslagsmatris och resultatmatris. Så vi använder uppslagarrayargumentet som 1/uppslagningsarray. För mer information, se nedanstående formelsyntax:
formelsyntax:
| = LOOKUP (2,1/(TEXT (datum, "mmyyyy") = TEXT (månad, "mmyyyy")), värden) |
dates: dates array in data
värden: resultatmatris i data
månad: månadskriterier
Exempel:
Allt detta kan vara förvirrande att förstå. Låt oss förstå hur man använder den här formeln i ett exempel. Här har vi data med datum och prisvärden. För detta behöver vi den senaste posten i januari 2019 månad som kommer att vara den sista posten med datumet Jan 2019. Här för datummatrisen och prismatrisen använde vi namngivna intervall.
Använd formeln:
| = LOOKUP (2,1/(TEXT (datum, "mmyyyy") = TEXT (F9, "mmyyyy")), Pris) |
Förklaring:
- TEXT (datum, "mmyyyy") returnerar värdena i "mmyyyy" -format efter att det har konverterats till textvärden som är
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEXT (F9, "mmyyyy") returnerar datumvärdet (i F9 cell) i "mmyyyy" -format efter konvertering av uppslagsdatumvärdet som är "012019" och detta värde kommer att matchas med ovan angivna array.
- Om du tar divisionen med 1 omvandlas matrisen True -värden till 1s och False -värden till #DIV/0 -fel. Så vi får den returnerade matrisen som.
{1; 1; 1; 1; #DIV/0!; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Nu hittar LOOKUP -funktionen värdet 2 i matrisen som inte kommer att hittas. Så det returnerar posten som motsvarar det sista värdet 1.
Benämnda intervall används
datum: C3: C15
Pris: D3: D15
Det sista priset är 78,48 motsvarande 31-01-2019. Kopiera formeln till andra celler med Ctrl + D eller dra nedåt från cellens nedre högra hörn.
Som du kan se formeln returnerar alla värden som krävs. Funktionen returnerar #N/A -fel, om värdet för uppslagsmånad inte matchar någon datumpost. I exemplet ovan är datumposterna sorterade. Så månadens sista post matchar månadens sista datumpost. Om datumposterna inte sorteras kommer vi först att sortera datumvärdena och använda ovanstående formel.
Hur får jag posten Sista datum per månad i kolumn i Excel?
För detta kommer vi att använda funktionen VLOOKUP och EOMONTH. EOMONTH -funktionen tar datumvärdet för månad och returnerar det sista datumet för den önskade månaden. VLOOKUP -funktion hittar det sista datumet i månaden eller närmaste föregående datum och returnerar ett värde som motsvarar det värdet.
Använd formeln:
| = VISNING (EOMONTH (F3,0), tabell, 2) |
Förklaring :
- EOMONTH (F3,0) returnerar sista datumet för samma månad. 0 -argument som används för att returnera datumet från samma månad.
- Nu blir uppslagsvärdet det sista datumet för den angivna månaden.
- Nu kommer värdet att sökas i den första kolumnen i tabellen och leta efter det sista datumet, om hittat returnerar värdet som motsvarar värdet och om det inte hittas returnerar det det sista matchningsresultatet, närmast uppslagsvärdet och returnerar motsvarande resultat.
- Funktionen returnerar värdet från den andra kolumnen i tabellen eftersom det tredje argumentet är 2.
Här returnerar formeln 78,48 som resultat. Kopiera nu formeln till andra celler med genvägen Ctrl + D eller dra ner från det nedre högra hörnet av cellen.
Som ni ser fungerar formeln bra. Det finns bara ett problem. När vi letar upp det sista datumet som motsvarar aprilmånaden, returnerar funktionen det värde som motsvarar mars, eftersom det inte finns någon post för aprildatum i tabellen. Se till att fånga dessa resultat.
Här är alla observationsanteckningar om användning av formeln.
Anmärkningar:
- Använd formeln som motsvarar problemet som anges i artikeln.
- VLOOKUP och LOOKUP -funktion är funktioner när uppslagsvärde är tal och inte finns i uppslagsmatrisen, returnerar värdet som motsvarar bara det närmaste talet som är mindre än uppslagsvärdet.
- Excel lagrar datumvärden som siffror.
- Ovanstående två angivna funktioner returnerar endast ett värde.
- Använd det fjärde argumentet i VLOOKUP -funktionen som 0 för att få exakt matchning av uppslagsvärdet i tabellen.
Hoppas att den här artikeln om hur du får den sista posten per månad i Excel är förklarande. Hitta fler artiklar om att extrahera värden från tabellen med hjälp av formler här. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen
Hitta den sista raden med blandad data i Excel : arbetar med siffror, text eller tom cell i samma array. Extrahera den sista raden med icke -tom cell med funktionen MATCH i Excel.
Hitta den sista dagen i en given månad : EOMONTH -funktionen returnerar det sista datumet i månaden, av det angivna datumvärdet.
Hur man får det sista värdet i kolumnen : Senaste inmatningen i en stor data, En kombination av CELL- och INDEX -funktionen returnerar det sista värdet i en kolumn eller lista med data.
Skillnad med den sista icke tomma cellen : tar den alternativa skillnaden från det sista värdet i listan med siffror med hjälp av IF och LOOKUP -funktionen i Excel.
Hitta den sista raden med data med textvärden i Excel : I en rad textvärden och tomma celler, returnera det sista värdet i matrisen med hjälp av MATCH och REPT -funktionen i excel.
Så här använder du SUMPRODUCT -funktionen i Excel: Returnerar summan efter att ha tagit produkten av motsvarande värden i flera matriser i excel.
Hur man använder jokertecken i excel : Sök, räkna, summa, genomsnittlig och mer matematisk operation på celler som matchar fraser med jokertecken i excel.
Populära artiklar:
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
Så här använder du VLOOKUP -funktionen i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.