Šajā rakstā mēs uzzināsim, kā iegūt pēdējo ierakstu pa mēnešiem programmā Excel.
Scenārijs:
Strādājot ar datu kopām, dažreiz mums ir jāiegūst daži rezultāti, pamatojoties uz dažādiem kritērijiem. Šeit problēmas kritērijs ir, ka vērtībai jābūt pēdējam ierakstam, kas atbilst konkrētajam mēnesim. Problēmu var interpretēt divējādi.
- Pēdējais ieraksts slejā pa mēnešiem
- Mēneša pēdējā datuma vērtība kolonnā
Jūs noteikti domājat, kāda ir atšķirība starp abiem. Atšķirība ir tā, ka pirmā problēma izraksta pēdējo ierakstu no slejas, kas atbilst mēneša nosaukumam, bet otrā problēma iegūst tuvāko datuma vērtību no mēneša beigu datuma vērtības. Mēs sapratīsim abas situācijas pa vienai. Pirmkārt, mēs apsvērsim, kā iegūt pēdējo ierakstu slejā pa mēnešiem.
Kā iegūt pēdējo ierakstu slejā pa mēnešiem programmā Excel?
Šim nolūkam mēs izmantosim funkciju TEXT un LOOKUP. Šeit mēs izmantojam funkcijas LOOKUP meklēšanas atribūtu pēdējai vērtībai. Funkcija MEKLĒŠANA aizņem 3 argumentu uzmeklēšanas vērtību, uzmeklēšanas masīvu un rezultātu masīvu. Tātad mēs izmantojam uzmeklēšanas masīva argumentu kā 1/uzmeklēšanas masīvu. Lai iegūtu sīkāku informāciju, skatiet zemāk esošo formulas sintaksi:
formulas sintakse:
| = LOOKUP (2,1/(TEXT (datumi, "mmyyyy") = TEXT (mēnesis, "mmyyyy")), vērtības) |
datumi: datumu masīvs datos
vērtības: rezultātu masīvs datos
mēnesis: mēneša kritēriji
Piemērs :
To visu var būt mulsinoši saprast. Let's saprast, kā izmantot šo formulu piemērā. Šeit mums ir dati ar datuma un cenas vērtībām. Šim nolūkam mums ir nepieciešams jaunākais 2019. gada janvāra ieraksts, kas būs pēdējais ieraksts ar datumu 2019. gada janvāris. Šeit datumu masīvam un cenu masīvam mēs izmantojām nosauktos diapazonus.
Izmantojiet formulu:
| = MEKLĒT (2,1/(TEKSTS (datumi, "mmyyyy") = TEXT (F9, "mmyyyy")), Cena) |
Paskaidrojums:
- TEXT (datumi, "mmyyyy") atgriezīs vērtību masīvu formātā "mmyyyy" pēc tā konvertēšanas teksta vērtībās, kas ir
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEKSTS (F9, "mmyyyy") pēc uzmeklēšanas datuma vērtības konvertēšanas, kas ir "012019", atgriezīs datuma vērtību (F9 šūnā) formātā "mmyyyy", un šī vērtība tiks saskaņota ar iepriekš norādīto masīvu.
- Ja dalīsit skaitli ar 1, patieso vērtību masīvs tiks pārvērsts par 1s un kļūdainu vērtību par kļūdu #DIV/0. Tātad mēs saņemsim atgriezto masīvu kā.
{1; 1; 1; 1; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Tagad funkcija LOOKUP atrod masīvā vērtību 2, kas netiks atrasta. Tātad tas atgriež ierakstu, kas atbilst pēdējai 1 vērtībai.
Izmantoti nosaukti diapazoni
datumi: C3: C15
Cena: D3: D15
Pēdējā cena ir 78.48, kas atbilst 31.01.2019. Kopējiet formulu uz citām šūnām, izmantojot Ctrl + D vai velkot uz leju no šūnas apakšējā labā stūra.
Kā redzat, formula atgriež visas nepieciešamās vērtības. Funkcija atgriež kļūdu #N/A, ja uzmeklēšanas mēneša vērtība neatbilst nevienam datuma ierakstam. Iepriekš minētajā piemērā datuma ieraksti ir sakārtoti. Tātad mēneša pēdējais ieraksts sakrīt ar mēneša pēdējo datumu. Tagad, ja datuma ieraksti nav sakārtoti, vispirms sakārtosim datuma vērtības un izmantosim iepriekš minēto formulu.
Kā iegūt sleju Excel pēdējā datumā pa mēnešiem?
Šim nolūkam mēs izmantosim funkciju VLOOKUP un EOMONTH. Funkcija EOMONTH ņem mēneša datuma vērtību un atgriež vajadzīgā mēneša pēdējo datumu. Funkcija VLOOKUP atrod mēneša pēdējo datumu vai tuvāko iepriekšējo datumu un atgriež šai vērtībai atbilstošu vērtību.
Izmantojiet formulu:
| = VLOOKUP (EOMONTH (F3,0), tabula, 2) |
Paskaidrojums:
- EOMONTH (F3,0) atgriež tā paša mēneša pēdējo datumu. 0 argumentu izmanto, lai atgrieztu datumu no tā paša mēneša.
- Tagad uzmeklēšanas vērtība kļūst par konkrētā mēneša pēdējo datumu.
- Tagad vērtība tiks meklēta tabulas pirmajā kolonnā un meklēs pēdējo datumu, ja atrasts atgriež vērtību, kas atbilst vērtībai, un, ja tā nav atrasta, tad atgriež pēdējo atbilstošo rezultātu, kas ir vistuvāk uzmeklēšanas vērtībai, un atgriež atbilstošo rezultātu.
- Funkcija atgriež vērtību no tabulas 2. kolonnas, jo trešais arguments ir 2.
Šeit formula atgriež 78.48. Tagad kopējiet formulu uz citām šūnām, izmantojot īsinājumtaustiņu Ctrl + D vai velkot uz leju no šūnas labā apakšējā stūra.
Kā redzat, formula darbojas labi. Ir tikai viena problēma. Meklējot pēdējo datumu, kas atbilst aprīļa mēnesim, funkcija atgriež marta vērtību, jo tabulā nav ieraksta aprīļa datums. Noteikti uztveriet šos rezultātus.
Šeit ir visas novērošanas piezīmes par formulas izmantošanu.
Piezīmes:
- Izmantojiet formulu, kas atbilst rakstā norādītajai problēmai.
- Funkcija VLOOKUP un LOOKUP ir funkcijas, kad uzmeklēšanas vērtība ir skaitlis un nav uzmeklēšanas masīvā, atgriež vērtību, kas atbilst tikai tuvākajam skaitlim, kas ir mazāks par uzmeklēšanas vērtību.
- Programmā Excel datuma vērtības tiek saglabātas kā skaitļi.
- Iepriekš minētās divas funkcijas atgriež tikai vienu vērtību.
- Izmantojiet 4. argumentu funkcijā VLOOKUP kā 0, lai tabulā iegūtu precīzu uzmeklēšanas vērtības atbilstību.
Ceru, ka šis raksts par to, kā programmā Excel iegūt pēdējo ierakstu pa mēnešiem, ir paskaidrojošs. Šeit atrodiet citus rakstus par vērtību iegūšanu no tabulas, izmantojot formulas šeit. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē
Atrodiet pēdējo rindu ar jauktajiem datiem programmā Excel : darbs ar cipariem, tekstu vai tukšu šūnu tajā pašā masīvā. Izvelciet pēdējo rindu ar šūnu, kas nav tukša, izmantojot funkciju MATCH programmā Excel.
Konkrētā mēneša pēdējās dienas atrašana : Funkcija EOMONTH atgriež dotā datuma vērtības mēneša pēdējo datumu.
Kā iegūt pēdējo vērtību kolonnā : Pēdējais ieraksts lielos datos. Funkcijas CELL un INDEX kombinācija atgriež pēdējo vērtību kolonnā vai datu sarakstā.
Atšķirība no pēdējās šūnas, kas nav tukša : ņem alternatīvo atšķirību no pēdējās vērtības sarakstā ar skaitļiem, izmantojot funkciju IF un LOOKUP programmā Excel.
Atrodiet pēdējo datu rindu ar teksta vērtībām programmā Excel : Teksta vērtību un tukšu šūnu masīvā atgrieziet masīvā pēdējo vērtību, izmantojot funkciju MATCH un REPT programmā Excel.
Funkcijas SUMPRODUCT izmantošana programmā Excel: Atgriež SUM pēc tam, kad ir iegūta atbilstošo vērtību reizināšana vairākos Excel masīvos.
Kā programmā Excel izmantot aizstājējzīmes : Uzmeklēšana, skaitīšana, summa, vidējā un matemātiskākā darbība šūnās, kas atbilst frāzēm, izmantojot Excel aizstājējzīmes.
Populāri raksti:
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.