Podsumowanie
Aby przeliczyć wydatek w jednej jednostce czasu (tj. Dzienny, tygodniowy, miesięczny itp.) Na inne jednostki czasu, można użyć dwukierunkowej formuły INDEKS i PODAJ. W przedstawionym przykładzie formuła w E5 (skopiowana w poprzek i w dół) to:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
gdzie dane (O5: S9), vunits (N5: N9) i hunits (O4: S4) to nazwane zakresy, jak wyjaśniono poniżej.
Wyjaśnienie
Aby przeliczyć wydatek w jednej jednostce czasu (tj. Dzienny, tygodniowy, miesięczny itp.) Na inne jednostki czasu, można użyć dwukierunkowej formuły INDEKS i PODAJ. W przedstawionym przykładzie formuła w E5 (skopiowana w poprzek i w dół) to:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Ta formuła używa tabeli przeglądowej z nazwanymi zakresami, jak pokazano poniżej:
Nazwane zakresy: dane (O5: S9), vunits (N5: N9) i hunits (O4: S4).
Wprowadzenie
Celem jest konwersja wydatku w jednej jednostce czasu na równoważny wydatek w innych jednostkach czasu. Na przykład, jeśli miesięczny wydatek wynosi 30 USD, chcemy obliczyć roczny wydatek w wysokości 360 USD, tygodniowy koszt w wysokości 7,50 USD itd.
Podobnie jak w przypadku wielu wyzwań w programie Excel, wiele zależy od tego, jak podejdziesz do problemu. Najpierw możesz ulec pokusie, aby rozważyć łańcuch zagnieżdżonych formuł JEŻELI. Można to zrobić, ale otrzymasz długą i skomplikowaną formułę.
Bardziej przejrzystym podejściem jest utworzenie tabeli odnośników, która zawiera współczynniki konwersji dla wszystkich możliwych konwersji, a następnie użycie dwukierunkowej formuły INDEKS i PODAJ.POZYCJĘ w celu pobrania wymaganej wartości dla danej konwersji. Gdy już masz wartość, możesz po prostu pomnożyć przez pierwotną kwotę.
Tabela konwersji
Tabela konwersji ma te same wartości zarówno dla etykiet pionowych, jak i poziomych: dzienne, tygodniowe, dwutygodniowe, miesięczne i roczne. Jednostki „od” są wymienione pionowo, a jednostki „do” - poziomo. Na potrzeby tego przykładu chcemy najpierw dopasować wiersz, a następnie kolumnę. Tak więc, jeśli chcemy przeliczyć wydatek miesięczny na wydatek roczny, dopasowujemy wiersz „miesięczny” i kolumnę „roczna” i zwracamy 12.
Aby wypełnić samą tabelę, używamy kombinacji prostych formuł i stałych:
Uwaga: dostosuj wartości konwersji do swoich konkretnych potrzeb. Wprowadzenie wartości jako = 1/7 to łatwy sposób na uniknięcie wprowadzania długich wartości dziesiętnych.
Formuła wyszukiwania
Ponieważ musimy zlokalizować wartość konwersji na podstawie dwóch danych wejściowych, jednostki czasu „od” i jednostki czasu „do”, potrzebna jest formuła wyszukiwania dwukierunkowego. INDEX i MATCH to fajne rozwiązanie. W pokazanym przykładzie formuła w E5 to:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Pracując od wewnątrz, pierwsza funkcja MATCH lokalizuje właściwy wiersz:
MATCH($D5,vunits,0) // find row, returns 4
Pobieramy oryginalną jednostkę czasu „z” z kolumny D, której używamy do znalezienia odpowiedniego wiersza w nazwanym zakresie vunits (N5: N9). Uwaga $ D5 to odwołanie mieszane z zablokowaną kolumną, więc formułę można skopiować w poprzek.
Druga funkcja MATCH lokalizuje kolumnę:
MATCH(F$4,hunits,0) // find column, returns 5
Tutaj otrzymujemy wartość wyszukiwania z nagłówka kolumny w wierszu 4 i używamy jej do znalezienia właściwej kolumny „do” w nazwanych polach zakresów (O4: S4). Ponownie należy zauważyć, że F $ 4 jest odniesieniem mieszanym z zablokowanym wierszem, więc wzór można skopiować w dół.
Po tym, jak obie formuły MATCH zwracają wyniki do INDEX, mamy:
=$C5*INDEX(data,4,5)
Tablica dostarczona do INDEX to nazwane dane zakresu (O5: S9). Z wierszem 4 i kolumną 5, INDEKS zwraca 12, więc otrzymujemy wynik końcowy 12000 w następujący sposób:
=$C5*INDEX(data,4,5) =1000*12 =12000
