Sažetak
Da biste pretvorili trošak u jednoj vremenskoj jedinici (tj. Dnevnoj, tjednoj, mjesečnoj itd.) U druge vremenske jedinice, možete upotrijebiti dvosmjernu formulu INDEX i MATCH. U prikazanom primjeru formula u E5 (kopirana preko i dolje) je:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
gdje su podaci (O5: S9), vunits (N5: N9) i lovi (O4: S4) imenovani rasponi, kako je objašnjeno u nastavku.
Obrazloženje
Da biste pretvorili trošak u jednoj vremenskoj jedinici (tj. Dnevnoj, tjednoj, mjesečnoj itd.) U druge vremenske jedinice, možete upotrijebiti dvosmjernu formulu INDEX i MATCH. U prikazanom primjeru formula u E5 (kopirana preko i dolje) je:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Ova formula koristi tablicu pretraživanja s imenovanim rasponima kao što je prikazano dolje:
Imenovani rasponi: podaci (O5: S9), vunits (N5: N9) i lovi (O4: S4).
Uvod
Cilj je pretvoriti trošak u jednoj vremenskoj jedinici, u ekvivalentni trošak u drugim vremenskim jedinicama. Primjerice, ako imamo mjesečni trošak od 30 USD, želimo izračunati godišnji trošak od 360 USD, tjedni od 7,50 USD itd.
Poput toliko izazova u Excelu, mnogo ovisi o tome kako pristupite problemu. Prvo biste mogli doći u napast da razmotrite lanac ugniježđenih IF formula. To se može učiniti, ali na kraju ćete dobiti dugu i složenu formulu.
Jednostavniji je pristup izradi tablice pretraživanja koja sadrži faktore pretvorbe za sve moguće pretvorbe, a zatim pomoću dvosmjerne formule INDEX i MATCH dohvatite potrebnu vrijednost za datu pretvorbu. Nakon što dobijete vrijednost, možete je jednostavno pomnožiti s izvornim iznosom.
Tablica pretvorbe
Tablica pretvorbe ima iste vrijednosti i za okomite i za vodoravne oznake: dnevno, tjedno, dvotjednik, mjesečno i godišnje. Jedinice "od" navedene su okomito, a jedinice "do" vodoravno. Za potrebe ovog primjera želimo prvo podudarati redak, a zatim stupac. Dakle, ako želimo pretvoriti mjesečni trošak u godišnji, podudaramo redak "mjesečni" i stupac "godišnji" i vraćamo 12.
Za popunjavanje same tablice koristimo kombinaciju jednostavnih formula i konstanti:
Napomena: Prilagodite vrijednosti pretvorbe u skladu s vašim specifičnim potrebama. Unos vrijednosti kao = 1/7 jednostavan je način da se izbjegne unos dugih decimalnih vrijednosti.
Formula pretraživanja
Budući da moramo pronaći vrijednost pretvorbe na temelju dva ulaza, vremenske jedinice "iz" i vremenske jedinice "u", trebamo dvosmjernu formulu pretraživanja. INDEX i MATCH pružaju lijepo rješenje. U prikazanom primjeru formula u E5 je:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Radeći iznutra prema van, prva funkcija MATCH locira ispravan redak:
MATCH($D5,vunits,0) // find row, returns 4
Izvornu vremensku jedinicu "iz" izvlačimo iz stupca D, pomoću koje pronalazimo pravi red u imenovanim vunicama raspona (N5: N9). Napomena $ D5 mješovita je referenca s zaključanim stupcem, tako da se formula može kopirati.
Druga funkcija MATCH locira stupac:
MATCH(F$4,hunits,0) // find column, returns 5
Ovdje dobivamo vrijednost pretraživanja iz zaglavlja stupca u retku 4 i pomoću njega pronalazimo pravi stupac "do" u imenovanom lovu na raspon (O4: S4). Opet, napomena F $ 4 mješovita je referenca s zaključanim retkom, tako da se formula može kopirati.
Nakon što obje MATCH formule vrate rezultate u INDEX, imamo:
=$C5*INDEX(data,4,5)
Niz dostavljen INDEX-u su imenovani podaci o rasponu , (O5: S9). S retkom 4 i stupcem 5, INDEX vraća 12, tako da dobivamo konačni rezultat od 12000 ovako:
=$C5*INDEX(data,4,5) =1000*12 =12000
