V tem članku se bomo naučili, kako v Excelu dobiti zadnji vnos po mesecih.
Scenarij:
Pri delu z nabori podatkov moramo včasih izvleči nekaj rezultatov na podlagi različnih meril. Tukaj je merilo problema, da mora biti vrednost zadnji vnos, ki ustreza danemu mesecu. Problem je mogoče razlagati na dva načina.
- Zadnji vnos v stolpec po mesecih
- Vrednost zadnjega datuma meseca v stolpcu
Gotovo si mislite, kakšna je razlika med tema dvema. Razlika je v tem, da prva težava izvleče zadnji vnos iz stolpca, ki se ujema z imenom meseca, medtem ko druga težava izvleče vrednost najbližjega datuma iz vrednosti datuma konca meseca. Obe situaciji bomo razumeli eno za drugo. Najprej bomo razmislili, kako do zadnjega vnosa v stolpec po mesecih.
Kako v Excelu dobiti zadnji vnos v stolpec po mesecih?
Za to bomo uporabili funkcijo TEXT in LOOKUP. Tukaj uporabljamo atribut iskanja funkcije LOOKUP za zadnjo vrednost. Funkcija LOOKUP vključuje iskanje vrednosti treh argumentov, iskalno polje in matriko rezultatov. Zato uporabimo argument lookup array kot 1/lookup array. Za več podrobnosti glejte spodnjo sintakso formule:
sintaksa formule:
| = LOOKUP (2,1/(BESEDILO (datumi, "mmyyyy") = TEXT (mesec, "mmyyyy")), vrednosti) |
dates: datumski niz v podatkih
vrednosti: matrika rezultatov v podatkih
mesec: merila meseca
Primer:
Vse to je lahko zmedeno za razumevanje. Poglejmo, kako uporabiti to formulo v primeru. Tu imamo podatke z vrednostmi datuma in cene. Za to potrebujemo najnovejši vnos januarja 2019, ki bo zadnji vnos z datumom januar 2019. Tu smo za matriko datumov in niz cen uporabili imenovana območja.
Uporabite formulo:
| = POGLED (2,1/(BESEDILO (datumi, "mmyyyy") = BESEDILO (F9, "mmyyyy")), cena) |
Pojasnilo:
- BESEDILO (datumi, "mmyyyy") bo po pretvorbi v besedilne vrednosti vrnilo matriko vrednosti v obliki "mmyyyy"
{ "012019" ; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- BESEDILO (F9, "mmyyyy") bo vrnilo vrednost datuma (v celici F9) v obliki "mmyyyy" po pretvorbi vrednosti datuma iskanja, ki je "012019", in ta vrednost se bo ujemala z zgoraj navedenim nizom.
- Če delite z 1, se matrika True vrednosti pretvori v 1s in False v napako #DIV/0. Tako bomo dobili vrnjeni niz kot.
{1; 1; 1; 1; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Zdaj funkcija LOOKUP najde vrednost 2 v matriki, ki je ne bo mogoče najti. Tako vrne vnos, ki ustreza zadnji 1 vrednosti.
Uporabljeni imenovani obsegi
datumi: C3: C15
Cena: D3: D15
Zadnja cena je 78,48, kar ustreza 31. 1. 2019. Kopirajte formulo v druge celice s kombinacijo tipk Ctrl + D ali povlecite navzdol iz spodnjega desnega kota celice.
Kot lahko vidite, formula vrne vse zahtevane vrednosti. Funkcija vrne napako #N/A, če se vrednost iskalnega meseca ne ujema z vnosom datuma. V zgornjem primeru so datumski vnosi razvrščeni. Tako se zadnji vnos v mesecu ujema z zadnjim vnosom datuma v mesecu. Če datumski vnosi niso razvrščeni, bomo najprej razvrstili vrednosti datumov in uporabili zgornjo formulo.
Kako v Excel v stolpec vnesti zadnji datum po mesecih?
Za to bomo uporabili funkcijo VLOOKUP in EOMONTH. Funkcija EOMONTH vzame vrednost datuma meseca in vrne zadnji datum zahtevanega meseca. Funkcija VLOOKUP najde zadnji datum v mesecu ali najbližji prejšnji datum in vrne vrednost, ki ustreza tej vrednosti.
Uporabite formulo:
| = VLOOKUP (EOMONTH (F3,0), miza, 2) |
Pojasnilo:
- EOMONTH (F3,0) vrne zadnji datum istega meseca. 0 argument, ki se uporablja za vrnitev datuma iz istega meseca.
- Zdaj vrednost iskanja postane zadnji datum v danem mesecu.
- Zdaj bo vrednost iskana v prvem stolpcu tabele in poiskala zadnji datum, če najdeno vrne vrednost, ki ustreza vrednosti, in če ni najdena, vrne zadnji ujemajoči se rezultat, najbližji iskalni vrednosti, in vrne ustrezen rezultat.
- Funkcija vrne vrednost iz drugega stolpca tabele, saj je 3. argument 2.
Tukaj formula kot rezultat vrne 78,48. Sedaj prekopirajte formulo v druge celice s bližnjico Ctrl + D ali povlecite navzdol iz desnega spodnjega kota celice.
Kot lahko vidite, formula deluje dobro. Obstaja le en problem. Ko iščemo zadnji datum, ki ustreza mesecu aprilu, funkcija vrne vrednost, ki ustreza marcu, saj v tabeli ni vnosa datuma aprila. Preverite te rezultate.
Tu so vse opombe glede uporabe formule.
Opombe:
- Uporabite formulo, ki ustreza problemu, navedenemu v članku.
- VLOOKUP in LOOKUP funkcija sta funkciji, ko je iskalna vrednost številka in ni prisotna v iskalnem nizu, vrne vrednost, ki ustreza le najbližjemu številu, manjšemu od iskalne vrednosti.
- Excel shranjuje datumske vrednosti kot številke.
- Zgornji dve navedeni funkciji vračata samo eno vrednost.
- Uporabite 4. argument v funkciji VLOOKUP kot 0, da dobite natančno ujemanje iskalne vrednosti v tabeli.
Upam, da je ta članek o tem, kako do zadnjega vnosa po mesecih v Excel, razložljiv. Več člankov o pridobivanju vrednosti iz tabele s formulami najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte
Poiščite zadnjo vrstico z mešanimi podatki v Excelu : delo s številkami, besedilom ali prazno celico v istem nizu. Izvlecite zadnjo vrstico s prazno celico s funkcijo MATCH v Excelu.
Iskanje zadnjega dne v danem mesecu : Funkcija EOMONTH vrne zadnji datum v mesecu z datumsko vrednostjo.
Kako do zadnje vrednosti v stolpcu : Zadnji vnos v velike podatke, Kombinacija funkcije CELL in INDEX vrne zadnjo vrednost v stolpcu ali seznamu podatkov.
Razlika z zadnjo celico, ki ni prazna : vzame nadomestno razliko od zadnje vrednosti na seznamu s številkami s funkcijo IF in LOOKUP v Excelu.
Poiščite zadnjo vrstico podatkov z besedilnimi vrednostmi v Excelu : V matriki besedilnih vrednosti in praznih celic vrnite zadnjo vrednost v matriki s funkcijo MATCH in REPT v Excelu.
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne znesek SUM, potem ko je vzel zmnožek ustreznih vrednosti v več matrikah v Excelu.
Kako uporabljati nadomestne znake v Excelu : Iskanje, štetje, vsota, povprečje in več matematičnih operacij na celicah, ki se ujemajo s frazami, z uporabo nadomestnih znakov v Excelu.
Priljubljeni članki:
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.