V tomto článku sa naučíme Ako získať posledný záznam podľa mesiacov v programe Excel.
Scenár:
Pri práci s množinami údajov niekedy potrebujeme extrahovať niektoré výsledky na základe rôznych kritérií. Problémovým kritériom je, že hodnota musí byť posledným záznamom zodpovedajúcim danému mesiacu. Problém je možné interpretovať dvoma spôsobmi.
- Posledný záznam v stĺpci podľa mesiaca
- Hodnota posledného dátumu v mesiaci v stĺpci
Určite sa zamýšľate nad tým, aký je medzi nimi rozdiel. Rozdiel je v tom, že prvý problém extrahuje posledný záznam zo stĺpca, ktorý zodpovedá názvu mesiaca, zatiaľ čo druhý problém extrahuje najbližšiu hodnotu dátumu z hodnoty dátumu konca mesiaca. Obidve situácie budeme chápať jednu po druhej. Najprv zvážime, ako získať posledný záznam v stĺpci podľa mesiaca.
Ako v programe Excel získať posledný záznam v stĺpci podľa mesiaca?
Na tento účel použijeme funkciu TEXT a LOOKUP. Tu používame atribút vyhľadávania funkcie LOOKUP pre poslednú hodnotu. Funkcia LOOKUP má 3 argumenty, vyhľadávaciu hodnotu, vyhľadávacie pole a pole výsledkov. Argument vyhľadávacie pole teda použijeme ako 1/vyhľadávacie pole. Ďalšie podrobnosti nájdete v nižšie uvedenej syntaxi vzorca:
syntax vzorca:
| = LOOKUP (2,1/(TEXT (dátumy, "mmyyyy") = TEXT (mesiac, "mmyyyy")), hodnoty) |
dátumy: pole dátumov v dátach
hodnoty: pole výsledkov v dátach
mesiac: kritériá mesiaca
Príklad:
Toto všetko môže byť mätúce na pochopenie. Poďme pochopiť, ako použiť tento vzorec v príklade. Tu máme údaje s hodnotami dátumu a ceny. Na to potrebujeme najnovší záznam z mesiaca január 2019, ktorý bude posledným záznamom s dátumom januára 2019. Tu sme pre pole dátumov a pole ceny použili pomenované rozsahy.
Použite vzorec:
| = LOOKUP (2,1/(TEXT (dátumy, "mmyyyy") = TEXT (F9, "mmyyyy")), cena) |
Vysvetlenie:
- TEXT (dátumy, „mmyyyy“) vráti pole hodnôt vo formáte „mmyyyy“ po jeho konverzii na textové hodnoty, ktoré sú
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEXT (F9, „mmyyyy“) vráti hodnotu dátumu (v bunke F9) vo formáte „mmyyyy“ po konverzii hodnoty dátumu vyhľadávania, ktorá je „012019“, a táto hodnota sa zhoduje s vyššie uvedeným poľom.
- Rozdelením na 1 sa pole skutočných hodnôt prevedie na 1 s a falošných na chybu #DIV/0. Vrátené pole teda dostaneme ako.
{1; 1; 1; 1; #DIV/0!; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Teraz funkcia LOOKUP nájde hodnotu 2 v poli, ktoré sa nenájde. Vráti teda záznam zodpovedajúci poslednej 1 hodnote.
Použité pomenované rozsahy
dátumy: C3: C15
Cena: D3: D15
Posledná cena je 78,48 zodpovedajúca 31. 1. 2019. Skopírujte vzorec do iných buniek pomocou klávesov Ctrl + D alebo ťahaním nadol z pravého dolného rohu bunky.
Ako vidíte, vzorec vracia všetky požadované hodnoty. Ak hodnota vyhľadávacieho mesiaca nezodpovedá zadaniu dátumu, funkcia vráti chybu #N/A. Vo vyššie uvedenom príklade sú záznamy dátumu zoradené. Posledný záznam v mesiaci sa teda zhoduje s posledným zadaním dátumu v mesiaci. Ak teraz nie sú položky dátumu zoradené, najskôr zoradíme hodnoty dátumu a použijeme vyššie uvedený vzorec.
Ako získať záznam posledného dátumu podľa mesiaca v stĺpci v programe Excel?
Na tento účel použijeme funkciu VLOOKUP a EOMONTH. Funkcia EOMONTH preberá hodnotu dátumu v mesiaci a vracia posledný dátum požadovaného mesiaca. Funkcia VLOOKUP nájde posledný dátum v mesiaci alebo najbližší predchádzajúci dátum a vráti hodnotu zodpovedajúcu tejto hodnote.
Použite vzorec:
| = VLOOKUP (EOMONTH (F3,0), tabuľka, 2) |
Vysvetlenie:
- EOMONTH (F3,0) vráti posledný dátum toho istého mesiaca. Na vrátenie dátumu z rovnakého mesiaca sa používa argument 0.
- Teraz sa hodnota vyhľadávania stane posledným dátumom daného mesiaca.
- Teraz sa hodnota vyhľadá v prvom stĺpci tabuľky a vyhľadá sa posledný dátum. Ak nájdený vráti hodnotu zodpovedajúcu hodnote a ak sa nenájde, vráti posledný zodpovedajúci výsledok najbližšie k vyhľadávanej hodnote a vráti príslušný výsledok.
- Funkcia vráti hodnotu z druhého stĺpca tabuľky, pretože tretí argument je 2.
Tu vzorec vráti 78,48 ako výsledok. Teraz skopírujte vzorec do iných buniek pomocou skratky Ctrl + D alebo ťahaním nadol z pravého dolného rohu bunky.
Ako vidíte, vzorec funguje dobre. Je tu len jeden problém. Keď vyhľadáme posledný dátum zodpovedajúci aprílovému mesiacu, funkcia vráti hodnotu zodpovedajúcu marcu, pretože v tabuľke nie je žiadny dátum apríla. Uistite sa, že ste zachytili tieto výsledky.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Použite vzorec zodpovedajúci problému uvedenému v článku.
- Funkcie VLOOKUP a LOOKUP sú funkcie, keď je hodnota vyhľadávania číslo a nie je vo vyhľadávacom poli, vráti hodnotu zodpovedajúcu iba najbližšiemu číslu menšiemu, ako je hodnota vyhľadávania.
- Excel ukladá hodnoty dátumu ako čísla.
- Vyššie uvedené dve funkcie vracajú iba jednu hodnotu.
- Použite 4. argument vo funkcii VLOOKUP ako 0, aby ste získali presnú zhodu vyhľadávanej hodnoty v tabuľke.
Dúfam, že tento článok o tom, ako získať posledný záznam podľa mesiacov v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o extrahovaní hodnôt z tabuľky pomocou vzorcov. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Nájdite posledný riadok so zmiešanými údajmi v Exceli : práca s číslami, textom alebo prázdnou bunkou v rovnakom poli. Extrahujte posledný riadok s prázdnou bunkou pomocou funkcie MATCH v programe Excel.
Nájdenie posledného dňa v danom mesiaci : Funkcia EOMONTH vráti posledný dátum v mesiaci danej hodnoty dátumu.
Ako získať poslednú hodnotu v stĺpci : Posledný záznam vo veľkých údajoch, Kombinácia funkcií CELL a INDEX vráti poslednú hodnotu v stĺpci alebo zozname údajov.
Rozdiel oproti poslednej neprázdnej bunke : preberie alternatívny rozdiel od poslednej hodnoty v zozname s číslami pomocou funkcie IF a LOOKUP v programe Excel.
Nájdite posledný riadok údajov s textovými hodnotami v programe Excel : V poli textových hodnôt a prázdnych buniek vráťte poslednú hodnotu v poli pomocou funkcie MATCH a REPT v programe Excel.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SÚčet po prevzatí súčinu zodpovedajúcich hodnôt vo viacerých poliach v programe Excel.
Ako používať zástupné znaky v programe Excel : Vyhľadávanie, počítanie, súčet, priemer a ďalšie matematické operácie na bunkách zodpovedajúcich frázam pomocou zástupných znakov v programe Excel.
Populárne články:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.