souhrn
Chcete-li převést výdaj v jedné časové jednotce (tj. Denní, týdenní, měsíční atd.) Na jiné časové jednotky, můžete použít oboustranný vzorec INDEX a MATCH. V zobrazeném příkladu je vzorec v E5 (kopírovaný napříč a dolů):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
kde data (O5: S9), vunits (N5: N9) a lovy (O4: S4) jsou pojmenovány rozsahy, jak je vysvětleno níže.
Vysvětlení
Chcete-li převést výdaj v jedné časové jednotce (tj. Denní, týdenní, měsíční atd.) Na jiné časové jednotky, můžete použít oboustranný vzorec INDEX a MATCH. V zobrazeném příkladu je vzorec v E5 (kopírovaný napříč a dolů):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Tento vzorec používá vyhledávací tabulku s pojmenovanými rozsahy, jak je uvedeno níže:
Pojmenované rozsahy: data (O5: S9), vunits (N5: N9) a lovy (O4: S4).
Úvod
Cílem je převést výdaj v jedné časové jednotce na ekvivalentní výdaj v jiných časových jednotkách. Například pokud máme měsíční výdaje 30 $, chceme vypočítat roční výdaje 360 $, týdenní výdaje 7,50 $ atd.
Stejně jako tolik výzev v aplikaci Excel, hodně záleží na tom, jak se k problému postavíte. Nejprve byste mohli být v pokušení zvážit řetězec vnořených vzorců IF. To se dá udělat, ale skončíte s dlouhým a komplikovaným vzorcem.
Čistší přístup je sestavit vyhledávací tabulku, která obsahuje převodní faktory pro všechny možné převody, a pak pomocí dvousměrného vzorce INDEX a MATCH získat požadovanou hodnotu pro danou konverzi. Jakmile máte hodnotu, můžete ji jednoduše vynásobit původní částkou.
Převodní tabulka
Konverzní tabulka má stejné hodnoty pro svislé i vodorovné štítky: denní, týdenní, čtrnáctidenní, měsíční a roční. Jednotky „od“ jsou uvedeny svisle a jednotky „od“ jsou uvedeny vodorovně. Pro účely tohoto příkladu chceme nejprve porovnat řádek, poté sloupec. Pokud tedy chceme převést měsíční výdaje na roční výdaje, porovnáme řádek „měsíční“ a sloupec „roční“ a vrátíme 12.
K naplnění samotné tabulky použijeme kombinaci jednoduchých vzorců a konstant:
Poznámka: Upravte hodnoty převodu tak, aby vyhovovaly vašim konkrétním potřebám. Zadání hodnoty jako = 1/7 je snadný způsob, jak se vyhnout zadávání dlouhých desetinných hodnot.
Vyhledávací vzorec
Protože potřebujeme najít hodnotu převodu na základě dvou vstupů, „od“ časové jednotky a „do“ časové jednotky, potřebujeme vzorec pro obousměrné vyhledávání. INDEX a MATCH poskytuje pěkné řešení. V zobrazeném příkladu je vzorec v E5:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Při práci zevnitř ven první funkce MATCH vyhledá správný řádek:
MATCH($D5,vunits,0) // find row, returns 4
Ze sloupce D vytáhneme původní časovou jednotku „od“, kterou použijeme k nalezení pravého řádku v pojmenovaných rozsahových jednotkách (N5: N9). Poznámka: $ D5 je smíšený odkaz se zamčeným sloupcem, takže vzorec lze kopírovat.
Druhá funkce MATCH vyhledá sloupec:
MATCH(F$4,hunits,0) // find column, returns 5
Zde získáme vyhledávací hodnotu ze záhlaví sloupce v řádku 4 a použijeme ji k vyhledání správného sloupce „do“ v pojmenovaných lovech rozsahu (O4: S4). Opět platí, že poznámka F $ 4 je smíšená reference s uzamčeným řádkem, takže vzorec lze zkopírovat dolů.
Poté, co oba MATCH vzorce vrátí výsledky na INDEX, máme:
=$C5*INDEX(data,4,5)
Pole poskytované pro INDEX je pojmenovaná data rozsahu (O5: S9). S řádkem 4 a sloupcem 5 vrátí INDEX 12, takže dostaneme konečný výsledek 12000 takto:
=$C5*INDEX(data,4,5) =1000*12 =12000
