Povzetek
Če želite pretvoriti strošek v eni časovni enoti (tj. Dnevni, tedenski, mesečni itd.) V druge časovne enote, lahko uporabite dvosmerno formulo INDEX in MATCH. V prikazanem primeru je formula v E5 (kopirana navzgor in navzdol):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
kjer so podatki (O5: S9), vunits (N5: N9) in lovi (O4: S4) imenovani obsegi, kot je razloženo spodaj.
Pojasnilo
Če želite pretvoriti strošek v eni časovni enoti (tj. Dnevni, tedenski, mesečni itd.) V druge časovne enote, lahko uporabite dvosmerno formulo INDEX in MATCH. V prikazanem primeru je formula v E5 (kopirana navzgor in navzdol):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Ta formula uporablja iskalno tabelo z imenovanimi obsegi, kot je prikazano spodaj:
Imenovani obsegi: podatki (O5: S9), vunits (N5: N9) in lovi (O4: S4).
Uvod
Cilj je pretvoriti strošek v eni časovni enoti v enakovreden strošek v drugih časovnih enotah. Če imamo na primer mesečne stroške 30 USD, želimo izračunati letne stroške 360 USD, tedenske 7,50 USD itd.
Tako kot toliko izzivov v Excelu je tudi tukaj veliko odvisno od tega, kako se lotevate težave. Najprej vas bo morda zamikalo razmisliti o verigi ugnezdenih formul IF. To je mogoče storiti, vendar boste na koncu dobili dolgo in zapleteno formulo.
Čistejši pristop je ustvariti iskalno tabelo, ki vsebuje pretvorbene faktorje za vse možne pretvorbe, nato pa z dvosmerno formulo INDEX in MATCH pridobiti zahtevano vrednost za dano pretvorbo. Ko dobite vrednost, jo lahko preprosto pomnožite s prvotnim zneskom.
Pretvorbena tabela
Tabela pretvorb ima enake vrednosti za navpične in vodoravne oznake: dnevno, tedensko, dvotedensko, mesečno in letno. Enote »od« so navedene navpično, enote »do« pa vodoravno. Za namene tega primera želimo najprej ujemati vrstico in nato stolpec. Torej, če želimo pretvoriti mesečne stroške v letne, ujemamo vrstico "mesečno" in stolpec "letno" ter vrnemo 12.
Za zapolnitev same tabele uporabimo kombinacijo preprostih formul in konstant:
Opomba: Vrednosti pretvorbe prilagodite svojim potrebam. Vnos vrednosti kot = 1/7 je preprost način, da se izognete vnosu dolgih decimalnih vrednosti.
Formula iskanja
Ker moramo najti vrednost pretvorbe na podlagi dveh vhodov, časovne enote "od" in časovne enote "do", potrebujemo dvosmerno formulo iskanja. INDEX in MATCH zagotavljata lepo rešitev. V prikazanem primeru je formula v E5:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Deluje od znotraj navzven, prva funkcija MATCH poišče pravilno vrstico:
MATCH($D5,vunits,0) // find row, returns 4
Iz stolpca D potegnemo izvirno časovno enoto "od", s katero poiščemo pravo vrstico v imenovanih enotah obsega (N5: N9). Opomba $ D5 je mešana referenca z zaklenjenim stolpcem, zato je formulo mogoče kopirati.
Druga funkcija MATCH najde stolpec:
MATCH(F$4,hunits,0) // find column, returns 5
Tu dobimo vrednost iskanja iz glave stolpca v vrstici 4 in s to pomočjo poiščemo pravi stolpec "do" v imenovanem lovu na obseg (O4: S4). Opomba F $ 4 je mešana referenca z zaklenjeno vrstico, zato lahko formulo kopirate navzdol.
Potem ko obe formuli MATCH vrneta rezultate v INDEX, imamo:
=$C5*INDEX(data,4,5)
Matrika, posredovana INDEX, so poimenovani podatki obsega (O5: S9). Z vrstico 4 in stolpcem 5 INDEX vrne 12, tako da dobimo končni rezultat 12000, kot je ta:
=$C5*INDEX(data,4,5) =1000*12 =12000
