In dit artikel zullen we leren hoe u de laatste invoer per maand in Excel kunt krijgen.
Scenario:
Wanneer we met datasets werken, moeten we soms enkele resultaten extraheren op basis van verschillende criteria. Hier is het probleemcriterium dat de waarde de laatste invoer moet zijn die overeenkomt met een bepaalde maand. Het probleem kan op twee manieren worden geïnterpreteerd.
- Laatste invoer in kolom per maand
- Laatste datumwaarde van maand in kolom
Je moet denken wat is het verschil tussen de twee. Het verschil is dat de eerste opgave de laatste invoer uit de kolom haalt die overeenkomt met de naam van de maand, terwijl de tweede opgave de dichtstbijzijnde datumwaarde haalt uit de einddatum van de maand. We zullen beide situaties één voor één begrijpen. Ten eerste zullen we overwegen Hoe u de laatste invoer in kolom per maand kunt krijgen.
Hoe krijg ik het laatste item in kolom per maand in Excel?
Hiervoor gebruiken we de TEKST-functie en de LOOKUP-functie. Hier gebruiken we een attribuut van de functie LOOKUP zoeken voor de laatste waarde. De functie LOOKUP heeft 3 argumenten voor de opzoekwaarde, de opzoekarray en de resultaatarray. Dus we gebruiken het argument opzoekarray als 1/opzoekarray. Zie de onderstaande formulesyntaxis voor meer details:
formule syntaxis:
| =LOOKUP(2,1/(TEKST(datums,"mmjjj")=TEKST(maand,"mmjjj")),waarden) |
datums: data-array in data
waarden: resultaatarray in gegevens
maand: maandcriteria
Voorbeeld :
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we eens kijken hoe we deze formule in een voorbeeld kunnen gebruiken. Hier hebben we gegevens met datum- en prijswaarden. Hiervoor hebben we de laatste invoer van de maand januari 2019 nodig, die de laatste invoer zal zijn met de datum van januari 2019. Hier hebben we voor de datumreeks en Prijsreeks benoemde bereiken gebruikt.
Gebruik de formule:
| =LOOKUP(2,1/(TEXT(dates,"mmyyyy")=TEXT(F9,"mmyyyy")),Prijs) |
Uitleg:
- TEXT(dates,"mmyyyy") retourneert de reeks waarden in "mmyyyy"-indeling nadat deze is geconverteerd naar tekstwaarden die
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEXT(F9,"mmyyyy") retourneert de datumwaarde (in F9-cel) in "mmyyyy"-formaat na het converteren van de opzoekdatumwaarde die "012019" is en deze waarde zal overeenkomen met de bovengenoemde array.
- Als u de deling met 1 neemt, wordt de reeks True-waarden omgezet in 1s en False-waarden in #DIV/0-fout. Dus we krijgen de geretourneerde array als.
{ 1 ; 1 ; 1 ; 1 ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Nu vindt de functie LOOKUP de waarde 2 in de array die niet zal worden gevonden. Dus het retourneert het item dat overeenkomt met de laatste 1 waarde.
Gebruikte benoemde bereiken
data: C3:C15
Prijs: D3:D15
De laatste prijs is 78,48 wat overeenkomt met 31-01-2019. Kopieer de formule naar andere cellen met behulp van Ctrl + D of sleep naar beneden vanuit de rechterbenedenhoek van de cel.
Zoals u kunt zien, retourneert de formule alle vereiste waarden. De functie retourneert de fout #N/B als de waarde van de opzoekmaand niet overeenkomt met een datuminvoer. In het bovenstaande voorbeeld zijn de datuminvoeren gesorteerd. Dus de laatste invoer van de maand komt overeen met de laatste datuminvoer van de maand. Als de datuminvoer niet is gesorteerd, sorteren we eerst de datumwaarden en gebruiken we de bovenstaande formule.
Hoe krijg ik de laatste datuminvoer per maand in kolom in Excel?
Hiervoor gebruiken we de functies VERT.ZOEKEN en EOMONTH. De functie EOMONTH neemt de datumwaarde van de maand en retourneert de laatste datum van de gewenste maand. De functie VERT.ZOEKEN vindt de laatste datum van de maand of de dichtstbijzijnde vorige datum en retourneert een waarde die overeenkomt met die waarde.
Gebruik de formule:
| = VERT.ZOEKEN ( EOMONTH (F3,0), tabel, 2) |
Uitleg :
- EOMONTH (F3,0) retourneert de laatste datum van dezelfde maand. 0-argument gebruikt om de datum van dezelfde maand te retourneren.
- Nu wordt de opzoekwaarde de laatste datum van de opgegeven maand.
- Nu wordt de waarde doorzocht in de eerste kolom van de tabel en wordt gezocht naar de laatste datum. Indien gevonden, wordt de waarde geretourneerd die overeenkomt met de waarde en indien niet gevonden, wordt het laatste overeenkomende resultaat geretourneerd, het dichtst bij de opzoekwaarde en wordt het bijbehorende resultaat geretourneerd.
- De functie retourneert de waarde uit de 2e kolom van de tabel aangezien het 3e argument 2 is.
Hier retourneert de formule de 78,48 als resultaat. Kopieer nu de formule naar andere cellen met behulp van de sneltoets Ctrl + D of sleep naar beneden vanuit de rechterbenedenhoek van de cel.
Zoals je kunt zien, werkt de formule prima. Er is alleen een probleem. Als we de laatste datum opzoeken die overeenkomt met de maand april, retourneert de functie de waarde die overeenkomt met maart, omdat er geen datum in april is ingevoerd in de tabel. Zorg ervoor dat u deze resultaten opvangt.
Hier zijn alle observatienotities met betrekking tot het gebruik van de formule.
Opmerkingen:
- Gebruik de formule die overeenkomt met het probleem dat in het artikel wordt vermeld.
- De functies VERT.ZOEKEN en ZOEKEN zijn functies wanneer de opzoekwaarde een getal is en niet aanwezig is in de opzoekarray, retourneert de waarde die overeenkomt met alleen het dichtstbijzijnde getal dat kleiner is dan de opzoekwaarde.
- Excel slaat datumwaarden op als getallen.
- De twee bovengenoemde functies retourneren slechts één waarde.
- Gebruik het 4e argument in de VERT.ZOEKEN-functie als 0 om de exacte overeenkomst van de opzoekwaarde in de tabel te krijgen.
Ik hoop dat dit artikel over Hoe u de laatste invoer per maand in Excel kunt krijgen, verklarend is. Vind hier meer artikelen over het extraheren van waarden uit de tabel met formules. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite
Vind de laatste rij met gemengde gegevens in Excel : werken met getallen, tekst of lege cellen in dezelfde array. Extraheer de laatste rij met een niet-lege cel met behulp van de MATCH-functie in Excel.
De laatste dag van een bepaalde maand vinden : EOMONTH-functie retourneert de laatste datum van de maand, van de opgegeven datumwaarde.
Hoe de laatste waarde in kolom te krijgen : Laatste invoer in een groot aantal gegevens. Een combinatie van de functie CEL en INDEX retourneert de laatste waarde in een kolom of lijst met gegevens.
Verschil met de laatste niet-lege cel : neemt het alternatieve verschil van de laatste waarde in de lijst met getallen met behulp van de ALS- en ZOEKEN-functie in Excel.
Zoek de laatste rij gegevens met tekstwaarden in Excel : Retourneer in een array met tekstwaarden en lege cellen de laatste waarde in de array met behulp van de MATCH- en REPT-functie in Excel.
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na het nemen van het product van de corresponderende waarden in meerdere matrices in Excel.
Jokertekens gebruiken in Excel : Opzoeken, tellen, optellen, gemiddelde en meer wiskundige bewerkingen op cellen die overeenkomen met zinnen met behulp van de jokertekens in Excel.
Populaire artikels:
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.