Selles artiklis õpime, kuidas Excelis viimast kirjet kuude kaupa saada.
Stsenaarium:
Andmekogumitega töötades peame mõnikord välja võtma mõned tulemused, mis põhinevad erinevatel kriteeriumidel. Siin on probleemikriteeriumiks see, et väärtus peab olema antud kuu viimane kirje. Probleemi saab tõlgendada kahel viisil.
- Viimane kirje veerus kuude kaupa
- Kuu viimane kuupäevaväärtus veerus
Ilmselt mõtlete, mis neil kahel vahet on. Erinevus seisneb selles, et esimene ülesanne eraldab kuu nimele vastava veeru viimase kirje, teine ülesanne aga kuu lõpu kuupäeva väärtusest lähima kuupäeva väärtuse. Mõistame mõlemat olukorda ükshaaval. Esiteks kaalume, kuidas saada viimast kirjet veergude kaupa.
Kuidas saada Excelis veergude viimane kirje?
Selleks kasutame funktsiooni TEXT ja LOOKUP. Siin kasutame viimase väärtuse funktsiooni OTSING atribuuti. Funktsioon LOOKUP võtab 3 argumendi otsinguväärtust, otsingumassiivi ja tulemimassiivi. Nii et me kasutame otsimassiivi argumenti 1/otsimassiivina. Lisateabe saamiseks vaadake allolevat valemisüntaksi:
valemi süntaks:
| = LOOKUP (2,1/(TEXT (kuupäevad, "mmyyyy") = TEXT (kuu, "mmyyyy")), väärtused) |
kuupäevad: kuupäevade massiiv andmetes
väärtused: tulemuste massiiv andmetes
kuu: kuu kriteeriumid
Näide:
Kõiki neid võib segadusse ajada. Mõistame, kuidas seda valemit näites kasutada. Siin on meil andmed kuupäeva ja hinna väärtustega. Selleks vajame jaanuari 2019 kuu viimast kirjet, mis on viimane kirje koos kuupäevaga jaanuar 2019. Siin kasutasime kuupäevamassiivi ja hinnamassiivi jaoks nimega vahemikke.
Kasutage valemit:
| = OTSING (2,1/(TEKST (kuupäevad, "mmyyyy") = TEXT (F9, "mmyyyy")), Hind) |
Selgitus:
- TEXT (kuupäevad, "mmyyyy") tagastab väärtuste massiivi vormingus "mmyyyy" pärast selle teisendamist tekstiväärtusteks, mis on
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEXT (F9, "mmyyyy") tagastab kuupäeva väärtuse (lahtris F9) vormingus "mmyyyy" pärast otsingupäeva väärtuse teisendamist, mis on "012019", ja see väärtus sobitatakse ülaltoodud massiiviga.
- Jaotise 1 võtmine teisendab tõeväärtuste massiivi väärtuseks 1s ja vale väärtuseks vea #DIV/0. Nii saame tagastatud massiivi kui.
{1; 1; 1; 1; #DIV/0!; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Funktsioon LOOKUP leiab massiivist väärtuse 2, mida ei leita. Seega tagastab see viimasele 1 väärtusele vastava kirje.
Kasutatakse nimega vahemikke
kuupäevad: C3: C15
Hind: D3: D15
Viimane hind on 78.48, mis vastab 31.01.2019. Kopeerige valem teistesse lahtritesse, kasutades Ctrl + D või lohistades lahtri paremas alanurgas allapoole.
Nagu näete, tagastab valem kõik vajalikud väärtused. Funktsioon tagastab vea #N/A, kui otsingukuu väärtus ei vasta ühelegi kuupäevakirjele. Ülaltoodud näites on kuupäevakirjed sorteeritud. Seega vastab kuu viimane kirje kuu viimase kuupäeva sisestusele. Kui kuupäevakirjeid ei sorteerita, sorteerime esmalt kuupäeva väärtused ja kasutame ülaltoodud valemit.
Kuidas saada Exceli veerus viimase kuupäeva kirje kuude kaupa?
Selleks kasutame funktsiooni VLOOKUP ja EOMONTH. Funktsioon EOMONTH võtab kuu kuupäeva väärtuse ja tagastab vajaliku kuu viimase kuupäeva. Funktsioon VLOOKUP leiab kuu viimase kuupäeva või lähima eelmise kuupäeva ja tagastab sellele väärtusele vastava väärtuse.
Kasutage valemit:
| = VLOOKUP (EOMONTH (F3,0), tabel, 2) |
Selgitus:
- EOMONTH (F3,0) tagastab sama kuu viimase kuupäeva. 0 argumenti kasutatakse sama kuu kuupäeva tagastamiseks.
- Nüüd muutub otsingu väärtus antud kuu viimaseks kuupäevaks.
- Nüüd otsitakse väärtust tabeli esimesest veerust ja otsitakse viimast kuupäeva. Kui leitud väärtus tagastab väärtusele vastava väärtuse ja kui seda ei leitud, tagastab see viimase vastava tulemuse, mis on otsinguväärtusele kõige lähemal, ja tagastab vastava tulemuse.
- Funktsioon tagastab tabeli 2. veeru väärtuse, kuna kolmas argument on 2.
Siin annab valem tulemuseks 78.48. Nüüd kopeerige valem teistesse lahtritesse, kasutades kiirklahvi Ctrl + D või lohistades lahtri paremas alanurgas alla.
Nagu näete, töötab valem hästi. On ainult üks probleem. Aprillikuule vastava viimase kuupäeva otsimisel tagastab funktsioon märtsi väärtuse, kuna tabelis pole aprilli kuupäeva. Pange need tulemused kindlasti tähele.
Siin on kõik tähelepanekud valemi kasutamise kohta.
Märkused:
- Kasutage artiklis toodud probleemile vastavat valemit.
- Funktsioon VLOOKUP ja OTSING on funktsioonid, kui otsinguväärtus on number ja seda ei leidu otsingumassiivis, tagastab väärtuse, mis vastab vaid lähimale arvule, mis on väiksem kui otsinguväärtus.
- Excel salvestab kuupäeva väärtused numbritena.
- Ülaltoodud kaks funktsiooni tagastavad ainult ühe väärtuse.
- Tabeli otsingu väärtuse täpse vaste saamiseks kasutage funktsiooni VLOOKUP neljandat argumenti kui 0.
Loodetavasti on see artikkel, kuidas Excelis viimast kannet kuude kaupa saada, selgitav. Lisateavet väärtuste tabelist väljavõtmise kohta leiate siit valemite abil. Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile
Leidke Excelis viimane rida segatud andmetega : töötamine numbrite, teksti või tühja lahtriga samas massiivis. Ekstraktige viimane rida tühja lahtriga, kasutades Exceli funktsiooni MATCH.
Konkreetse kuu viimase päeva leidmine : Funktsioon EOMONTH tagastab antud kuupäeva väärtuse kuu viimase kuupäeva.
Kuidas saada viimast väärtust veerus : Viimane kirje suurtes andmetes. Funktsiooni CELL ja INDEX kombinatsioon tagastab veeru või andmete loendi viimase väärtuse.
Erinevus viimase tühja lahtriga : võtab alternatiivse erinevuse loendi viimasest väärtusest numbritega, kasutades Exceli funktsiooni IF ja LOOKUP.
Leidke Excelis tekstiväärtustega viimane andmerida : Tagastage tekstiväärtuste ja tühjade lahtrite massiivis massiivi viimane väärtus, kasutades Exceli funktsiooni MATCH ja REPT.
Funktsiooni SUMPRODUCT kasutamine Excelis: Tagastab SUM -i pärast vastavate väärtuste korrutamist mitmes Excelis asuvas massiivis.
Kuidas kasutada Excelis metamärke : Otsing, loendamine, summa, keskmine ja matemaatilisem toiming fraasidele, mis vastavad fraasidele, kasutades Excelis metamärke.
Populaarsed artiklid:
Funktsiooni IF kasutamine Excelis : Exceli IF -lause kontrollib tingimust ja tagastab konkreetse väärtuse, kui tingimus on TRUE, või tagastab mõne muu väärtuse, kui FALSE.
Funktsiooni VLOOKUP kasutamine Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.