rezumat
Pentru a converti o cheltuială dintr-o singură unitate de timp (adică zilnic, săptămânal, lunar etc.) în alte unități de timp, puteți utiliza o formulă INDICE și MATCH cu două căi. În exemplul prezentat, formula din E5 (copiată în sus și în jos) este:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
unde datele (O5: S9), vuniturile (N5: N9) și huniturile (O4: S4) sunt denumite intervale, după cum se explică mai jos.
Explicaţie
Pentru a converti o cheltuială dintr-o singură unitate de timp (adică zilnic, săptămânal, lunar etc.) în alte unități de timp, puteți utiliza o formulă INDICE și MATCH cu două căi. În exemplul prezentat, formula din E5 (copiată în sus și în jos) este:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Această formulă utilizează un tabel de căutare cu intervale denumite așa cum se arată mai jos:
Intervalele denumite: date (O5: S9), vunits (N5: N9) și hunits (O4: S4).
Introducere
Scopul este de a converti o cheltuială într-o unitate de timp, la o cheltuială echivalentă în alte unități de timp. De exemplu, dacă avem o cheltuială lunară de 30 USD, dorim să calculăm o cheltuială anuală de 360 USD, o cheltuială săptămânală de 7,50 USD etc.
La fel ca atâtea provocări din Excel, multe depind de modul în care abordați problema. S-ar putea să fiți mai întâi tentați să luați în considerare un lanț de formule IF imbricate. Acest lucru se poate face, dar veți ajunge cu o formulă lungă și complicată.
O abordare mai curată este de a construi un tabel de căutare care conține factori de conversie pentru toate conversiile posibile, apoi să utilizați o formulă bidirecțională INDEX și MATCH pentru a recupera valoarea necesară pentru o conversie dată. Odată ce ați obținut valoarea, puteți înmulți pur și simplu cu suma inițială.
Tabelul de conversie
Tabelul de conversie are aceleași valori atât pentru etichetele verticale, cât și pentru cele orizontale: zilnic, săptămânal, bisăptămânal, lunar și anual. Unitățile „de la” sunt listate vertical, iar unitățile „la” sunt listate pe orizontală. În scopul acestui exemplu, vrem să potrivim mai întâi rândul, apoi coloana. Deci, dacă dorim să transformăm o cheltuială lunară într-o cheltuială anuală, potrivim rândul „lunar” și coloana „anuală” și returnăm 12.
Pentru a completa tabelul în sine, folosim un amestec de formule simple și constante:
Notă: personalizați valorile conversiei pentru a vă satisface nevoile specifice. Introducerea unei valori ca = 1/7 este o modalitate ușoară de a evita introducerea valorilor zecimale lungi.
Formula de căutare
Deoarece trebuie să localizăm o valoare de conversie bazată pe două intrări, o unitate de timp „de la” și o unitate de timp „la”, avem nevoie de o formulă de căutare în două direcții. INDEX și MATCH oferă o soluție plăcută. În exemplul prezentat, formula din E5 este:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Lucrând din interior spre exterior, prima funcție MATCH localizează rândul corect:
MATCH($D5,vunits,0) // find row, returns 4
Tragem unitatea de timp „din” originală din coloana D, pe care o folosim pentru a găsi rândul potrivit în vunitele de interval numite (N5: N9). Notă $ D5 este o referință mixtă cu coloana blocată, astfel încât formula să poată fi copiată.
A doua funcție MATCH localizează coloana:
MATCH(F$4,hunits,0) // find column, returns 5
Aici, obținem valoarea de căutare din antetul coloanei din rândul 4 și o folosim pentru a găsi coloana „la” dreapta în intervalul numit hunits (O4: S4). Din nou, nota F $ 4 este o referință mixtă cu rândul blocat, astfel încât formula poate fi copiată.
După ce ambele formule MATCH returnează rezultatele la INDEX, avem:
=$C5*INDEX(data,4,5)
Matricea furnizată INDEX este numită gama de date , (O5: S9). Cu un rând de 4 și coloana de 5, INDEX returnează 12, deci obținem un rezultat final de 12000 astfel:
=$C5*INDEX(data,4,5) =1000*12 =12000
