U ovom ćemo članku naučiti kako do zadnjeg unosa po mjesecima u Excelu.
Scenarij:
Prilikom rada sa skupovima podataka ponekad moramo izdvojiti neke rezultate na temelju različitih kriterija. Ovdje je kriterij problema da vrijednost mora biti posljednji unos koji odgovara danom mjesecu. Problem se može tumačiti na dva načina.
- Posljednji unos u stupac po mjesecima
- Vrijednost posljednjeg datuma mjeseca u stupcu
Sigurno razmišljate koja je razlika između to dvoje. Razlika je u tome što prvi problem izdvaja zadnji unos iz stupca koji odgovara nazivu mjeseca, dok drugi problem izdvaja vrijednost najbližeg datuma iz datuma datuma na kraju mjeseca. Razumjet ćemo obje situacije jednu po jednu. Prvo ćemo razmotriti Kako do zadnjeg unosa u stupac po mjesecima.
Kako dobiti zadnji unos u stupac po mjesecima u Excelu?
Za to ćemo koristiti funkciju TEXT i LOOKUP. Ovdje koristimo atribut pretraživanja funkcije LOOKUP za zadnju vrijednost. LOOKUP funkcija uzima 3 argumenta za traženje vrijednosti, niz za pretraživanje i niz rezultata. Stoga koristimo argument polja za pretraživanje kao 1/niz za pretraživanje. Za više detalja pogledajte sintaksu formule u nastavku:
sintaksa formule:
| = LOOKUP (2,1/(TEXT (datumi, "mmyyyy") = TEXT (mjesec, "mmyyyy")), vrijednosti) |
dates: niz datuma u podacima
vrijednosti: niz rezultata u podacima
mjesec: kriterij mjeseca
Primjer :
Sve ovo može biti zbunjujuće za razumijevanje. Shvatimo kako koristiti ovu formulu u primjeru. Ovdje imamo podatke s vrijednostima datuma i cijene. Za to nam je potreban najnoviji unos u mjesecu siječnju 2019. koji će biti posljednji unos s datumom siječanj 2019. Ovdje smo za niz datuma i niz cijena koristili imenovane raspone.
Koristite formulu:
| = PREGLED (2,1/(TEXT (datumi, "mmyyyy") = TEXT (F9, "mmyyyy")), Cijena) |
Obrazloženje:
- TEXT (dates, "mmyyyy") vratit će niz vrijednosti u "mmyyyy" formatu nakon što ga pretvori u tekstualne vrijednosti
{ "012019" ; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEKST (F9, "mmyyyy") vratit će vrijednost datuma (u ćeliji F9) u "mmyyyy" formatu nakon pretvaranja vrijednosti datuma pretraživanja koja je "012019", a ta će se vrijednost uskladiti s gore navedenim nizom.
- Podjela s 1 pretvorit će niz True vrijednosti u 1s i False vrijednosti u #DIV/0 pogrešku. Tako ćemo dobiti vraćeni niz kao.
{1; 1; 1; 1; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Sada funkcija LOOKUP pronalazi vrijednost 2 u nizu koja se neće pronaći. Dakle, vraća unos koji odgovara posljednjoj 1 vrijednosti.
Korišteni imenovani rasponi
datumi: C3: C15
Cijena: D3: D15
Zadnja cijena iznosi 78,48, što odgovara 31.01.2019. Kopirajte formulu u druge ćelije pomoću Ctrl + D ili povlačenjem prema dolje iz donjeg desnog kuta ćelije.
Kao što vidite, formula vraća sve potrebne vrijednosti. Funkcija vraća grešku #N/A, ako se vrijednost mjeseca pretraživanja ne podudara s bilo kojim unosom datuma. U gornjem primjeru unosi datuma su sortirani. Dakle, zadnji unos u mjesecu odgovara zadnjem unosu datuma u mjesecu. Ako unosi datuma nisu sortirani, prvo ćemo sortirati vrijednosti datuma i upotrijebiti gornju formulu.
Kako unijeti posljednji datum po mjesecu u stupac u Excelu?
Za to ćemo koristiti funkciju VLOOKUP i EOMONTH. Funkcija EOMONTH uzima datumsku vrijednost mjeseca i vraća zadnji datum potrebnog mjeseca. Funkcija VLOOKUP pronalazi posljednji datum u mjesecu ili najbliži prethodni datum i vraća vrijednost koja odgovara toj vrijednosti.
Upotrijebite formulu:
| = VLOOKUP (EOMONTH (F3,0), tablica, 2) |
Objašnjenje:
- EOMONTH (F3,0) vraća zadnji datum istog mjeseca. 0 argument koji se koristi za vraćanje datuma iz istog mjeseca.
- Sada vrijednost pretraživanja postaje posljednji datum u danom mjesecu.
- Sada će se vrijednost pretraživati u prvom stupcu tablice i tražiti posljednji datum, ako je pronađena vraća vrijednost koja odgovara vrijednosti, a ako nije pronađena, onda vraća posljednji odgovarajući rezultat, najbliži vrijednosti traženja i vraća odgovarajući rezultat.
- Funkcija vraća vrijednost iz drugog stupca tablice jer je 3. argument 2.
Ovdje formula vraća 78.48 kao rezultat. Sada kopirajte formulu u druge ćelije pomoću prečaca Ctrl + D ili povlačenjem prema dolje iz desnog donjeg kuta ćelije.
Kao što vidite, formula dobro radi. Postoji samo jedan problem. Dok tražimo posljednji datum koji odgovara mjesecu travnju, funkcija vraća vrijednost koja odgovara ožujku, jer u tablici nema unosa datuma travnja. Uhvatite ove rezultate.
Ovdje su sve napomene o primjeni formule.
Bilješke:
- Koristite formulu koja odgovara problemu navedenom u članku.
- VLOOKUP i LOOKUP funkcija su funkcije kada je vrijednost pretraživanja broj i nije prisutna u nizu za pretraživanje, vraća vrijednost koja odgovara samo najbližem broju manjem od vrijednosti traženja.
- Excel pohranjuje vrijednosti datuma kao brojeve.
- Gore navedene dvije funkcije vraćaju samo jednu vrijednost.
- Koristite 4. argument u funkciji VLOOKUP kao 0 da biste dobili točno podudaranje vrijednosti traženja u tablici.
Nadam se da je ovaj članak o tome kako dobiti posljednji unos po mjesecu u Excelu objašnjen. Ovdje pronađite još članaka o izdvajanju vrijednosti iz tablice pomoću formula. Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte
Pronađite zadnji redak s pomiješanim podacima u Excelu : rad s brojevima, tekstom ili praznom ćelijom u istom nizu. Izdvojite zadnji redak bez prazne ćelije pomoću funkcije MATCH u Excelu.
Pronalaženje posljednjeg dana određenog mjeseca : EOMONTH funkcija vraća zadnji datum u mjesecu, zadane vrijednosti datuma.
Kako dobiti zadnju vrijednost u stupcu : Posljednji unos velikih podataka, Kombinacija CELL i INDEX funkcije vraća posljednju vrijednost u stupcu ili popisu podataka.
Razlika s posljednjom ćelijom koja nije prazna : uzima alternativnu razliku od posljednje vrijednosti na popisu s brojevima pomoću funkcija IF i LOOKUP u Excelu.
Pronađite zadnji redak podataka s tekstualnim vrijednostima u Excelu : U nizu tekstualnih vrijednosti i praznih ćelija vratite posljednju vrijednost u nizu pomoću funkcije MATCH i REPT u Excelu.
Kako koristiti funkciju SUMPRODUCT u Excelu: Vraća ZBOR nakon uzimanja proizvoda odgovarajućih vrijednosti u više niza u Excelu.
Kako koristiti zamjenske znakove u Excelu : Pretraživanje, brojanje, zbroj, prosjek i više matematičkih operacija na ćelijama koje se podudaraju s izrazima pomoću zamjenskih znakova u Excelu.
Popularni članci:
Kako koristiti IF funkciju u Excelu : IF izraz u Excelu provjerava uvjet i vraća određenu vrijednost ako je uvjet TRUE ili vraća drugu određenu vrijednost ako je FALSE.
Kako koristiti funkciju VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.