V tomto článku se naučíme Jak získat poslední položku po měsíci v Excelu.
Scénář:
Při práci s datovými sadami někdy potřebujeme extrahovat některé výsledky na základě různých kritérií. Zde je kritériem problému, že hodnota musí být posledním záznamem odpovídajícím danému měsíci. Problém lze interpretovat dvěma způsoby.
- Poslední záznam ve sloupci podle měsíce
- Poslední hodnota data měsíce ve sloupci
Určitě si říkáte, jaký je mezi nimi rozdíl. Rozdíl je v tom, že první problém extrahuje poslední položku ze sloupce, který odpovídá názvu měsíce, zatímco druhý problém extrahuje nejbližší hodnotu data z hodnoty data konce měsíce. Budeme rozumět oběma situacím jedna po druhé. Nejprve budeme zvažovat, jak získat poslední položku ve sloupci po měsíci.
Jak získat poslední položku ve sloupci po měsíci v aplikaci Excel?
K tomu použijeme funkci TEXT a LOOKUP. Zde používáme atribut vyhledávání funkce LOOKUP pro poslední hodnotu. Funkce LOOKUP přebírá 3 vyhledávací hodnoty, vyhledávací pole a výsledkové pole. Argument vyhledávací pole tedy použijeme jako 1/vyhledávací pole. Další podrobnosti viz níže uvedená syntaxe vzorce:
syntaxe vzorce:
| = LOOKUP (2,1/(TEXT (data, "mmyyyy") = TEXT (měsíc, "mmyyyy")), hodnoty) |
data: pole dat v datech
hodnoty: pole výsledků v datech
měsíc: kritéria měsíce
Příklad:
Pochopit to všechno může být matoucí. Pojďme pochopit, jak tento vzorec použít v příkladu. Zde máme data s hodnotami data a ceny. K tomu potřebujeme nejnovější záznam z měsíce ledna 2019, který bude posledním záznamem s datem ledna 2019. Zde jsme pro pole data a pole cen použili pojmenované rozsahy.
Použijte vzorec:
| = LOOKUP (2,1/(TEXT (data, "mmyyyy") = TEXT (F9, "mmyyyy")), cena) |
Vysvětlení:
- TEXT (data, „mmyyyy“) vrátí pole hodnot ve formátu „mmyyyy“ po jeho převodu na textové hodnoty, které jsou
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEXT (F9, „mmyyyy“) vrátí hodnotu data (v buňce F9) ve formátu „mmyyyy“ po převodu hodnoty data vyhledávání, která je „012019“, a tato hodnota bude spárována s výše uvedeným polem.
- Rozdělení s 1 převede pole pravdivých hodnot na 1 s a nepravdivých hodnot na chybu #DIV/0. Vrácené pole tedy získáme jako.
{1; 1; 1; 1; #DIV/0!; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Nyní funkce LOOKUP najde v poli hodnotu 2, která nebude nalezena. Vrátí tedy záznam odpovídající poslední 1 hodnotě.
Použité pojmenované rozsahy
data: C3: C15
Cena: D3: D15
Poslední cena je 78,48, což odpovídá 31-01-2019. Zkopírujte vzorec do jiných buněk pomocí Ctrl + D nebo přetažením dolů z pravého dolního rohu buňky.
Jak vidíte, vzorec vrací všechny požadované hodnoty. Funkce vrací chybu #N/A, pokud hodnota měsíce vyhledávání neodpovídá žádnému zadání data. Ve výše uvedeném příkladu jsou záznamy data seřazené. Poslední záznam měsíce se tedy shoduje s posledním datovým záznamem měsíce. Pokud nyní nejsou data seřazena, nejprve seřadíme hodnoty data a použijeme výše uvedený vzorec.
Jak získat položku Poslední datum podle měsíce ve sloupci v aplikaci Excel?
K tomu použijeme funkci VLOOKUP a EOMONTH. Funkce EOMONTH vezme hodnotu data v měsíci a vrátí poslední datum požadovaného měsíce. Funkce VLOOKUP najde poslední datum měsíce nebo nejbližší předchozí datum a vrátí hodnotu odpovídající této hodnotě.
Použijte vzorec:
| = VLOOKUP (EOMONTH (F3,0), tabulka, 2) |
Vysvětlení :
- EOMONTH (F3,0) vrací poslední datum stejného měsíce. K vrácení data ze stejného měsíce byl použit argument 0.
- Nyní se z vyhledávací hodnoty stane poslední datum daného měsíce.
- Nyní bude hodnota prohledána v prvním sloupci tabulky a vyhledá se poslední datum, pokud nalezeno vrátí hodnotu odpovídající hodnotě a pokud není nalezeno, vrátí poslední odpovídající výsledek, nejblíže k hodnotě vyhledávání a vrátí jeho odpovídající výsledek.
- Funkce vrací hodnotu z 2. sloupce tabulky, protože 3. argument je 2.
Zde vzorec vrátí 78,48 jako výsledek. Nyní zkopírujte vzorec do jiných buněk pomocí zkratky Ctrl + D nebo tažením dolů z pravého dolního rohu buňky.
Jak vidíte, vzorec funguje dobře. Je tu jen jeden problém. Když vyhledáme poslední datum odpovídající dubnovému měsíci, funkce vrátí hodnotu odpovídající březnu, protože v tabulce není žádné datum dubna. Tyto výsledky nezapomeňte zachytit.
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- Použijte vzorec odpovídající problému uvedenému v článku.
- Funkce VLOOKUP a LOOKUP jsou funkce, pokud je vyhledávací hodnota číslo a není přítomna ve vyhledávacím poli, vrací hodnotu odpovídající pouze nejbližšímu číslu menší než vyhledávací hodnota.
- Excel ukládá hodnoty data jako čísla.
- Výše uvedené dvě funkce vracejí pouze jednu hodnotu.
- Použijte 4. argument ve funkci VLOOKUP jako 0, abyste získali přesnou shodu vyhledávací hodnoty v tabulce.
Doufám, že tento článek o tom, jak získat poslední položku po měsíci v aplikaci Excel, je vysvětlující. Zde najdete další články o extrahování hodnot z tabulky pomocí vzorců. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail
Najděte poslední řádek se smíšenými daty v Excelu : práce s čísly, textem nebo prázdnou buňkou ve stejném poli. Extrahujte poslední řádek s prázdnou buňkou pomocí funkce MATCH v aplikaci Excel.
Hledání posledního dne daného měsíce : Funkce EOMONTH vrací poslední datum v měsíci dané hodnoty data.
Jak získat poslední hodnotu ve sloupci : Poslední záznam ve velkých datech, kombinace funkcí CELL a INDEX vrací poslední hodnotu ve sloupci nebo seznamu dat.
Rozdíl s poslední prázdnou buňkou : převezme alternativní rozdíl od poslední hodnoty v seznamu s čísly pomocí funkce IF a LOOKUP v aplikaci Excel.
Najděte poslední řádek dat s textovými hodnotami v Excelu : V poli textových hodnot a prázdných buněk vraťte poslední hodnotu v poli pomocí funkce MATCH a REPT v Excelu.
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po převzetí součinu odpovídajících hodnot ve více polích v Excelu.
Jak používat zástupné znaky v aplikaci Excel : Vyhledávání, počítání, součet, průměr a další matematické operace na buňkách odpovídajících frázím pomocí zástupných znaků v Excelu.
Oblíbené články:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.