Zusammenfassung
Um eine Ausgabe in einer Zeiteinheit (dh täglich, wöchentlich, monatlich usw.) in andere Zeiteinheiten umzurechnen, können Sie eine Zwei-Wege-INDEX- und MATCH-Formel verwenden. In dem gezeigten Beispiel lautet die Formel in E5 (quer und runter kopiert):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Dabei werden Daten (O5: S9), Einheiten (N5: N9) und Einheiten (O4: S4) als Bereiche bezeichnet, wie nachstehend erläutert.
Erläuterung
Um eine Ausgabe in einer Zeiteinheit (dh täglich, wöchentlich, monatlich usw.) in andere Zeiteinheiten umzurechnen, können Sie eine Zwei-Wege-INDEX- und MATCH-Formel verwenden. In dem gezeigten Beispiel lautet die Formel in E5 (quer und runter kopiert):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Diese Formel verwendet eine Nachschlagetabelle mit benannten Bereichen, wie unten gezeigt:
Benannte Bereiche: Daten (O5: S9), Einheiten (N5: N9) und Einheiten (O4: S4).
Einführung
Ziel ist es, einen Aufwand in einer Zeiteinheit in einen entsprechenden Aufwand in anderen Zeiteinheiten umzuwandeln. Wenn wir beispielsweise eine monatliche Ausgabe von 30 USD haben, möchten wir eine jährliche Ausgabe von 360 USD, eine wöchentliche Ausgabe von 7,50 USD usw. berechnen.
Wie so viele Herausforderungen in Excel hängt vieles davon ab, wie Sie das Problem angehen. Sie könnten zuerst versucht sein, eine Kette verschachtelter IF-Formeln in Betracht zu ziehen. Dies kann getan werden, aber Sie erhalten eine lange und komplizierte Formel.
Ein sauberer Ansatz besteht darin, eine Nachschlagetabelle zu erstellen, die Konvertierungsfaktoren für alle möglichen Konvertierungen enthält, und dann eine Zwei-Wege-INDEX- und MATCH-Formel zu verwenden, um den erforderlichen Wert für eine bestimmte Konvertierung abzurufen. Sobald Sie den Wert haben, können Sie einfach mit dem ursprünglichen Betrag multiplizieren.
Die Umrechnungstabelle
Die Umrechnungstabelle enthält dieselben Werte für vertikale und horizontale Beschriftungen: täglich, wöchentlich, zweiwöchentlich, monatlich und jährlich. Die "von" -Einheiten werden vertikal aufgelistet und die "bis" -Einheiten werden horizontal aufgelistet. In diesem Beispiel möchten wir zuerst die Zeile und dann die Spalte abgleichen. Wenn wir also eine monatliche Ausgabe in eine jährliche Ausgabe umwandeln möchten, stimmen wir mit der Zeile "monatlich" und der Spalte "jährlich" überein und geben 12 zurück.
Um die Tabelle selbst zu füllen, verwenden wir eine Mischung aus einfachen Formeln und Konstanten:
Hinweis: Passen Sie die Conversion-Werte an Ihre spezifischen Anforderungen an. Die Eingabe eines Wertes als = 1/7 ist eine einfache Möglichkeit, die Eingabe langer Dezimalwerte zu vermeiden.
Die Suchformel
Da wir einen Konvertierungswert basierend auf zwei Eingaben suchen müssen, einer "von" -Zeiteinheit und einer "nach" -Zeiteinheit, benötigen wir eine Zwei-Wege-Suchformel. INDEX und MATCH bieten eine gute Lösung. In dem gezeigten Beispiel lautet die Formel in E5:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Die erste MATCH-Funktion arbeitet von innen nach außen und findet die richtige Zeile:
MATCH($D5,vunits,0) // find row, returns 4
Wir ziehen die ursprüngliche "von" -Zeiteinheit aus Spalte D, mit der wir die richtige Zeile in den benannten Bereichseinheiten (N5: N9) finden. Hinweis $ D5 ist eine gemischte Referenz mit gesperrter Spalte, sodass die Formel kopiert werden kann.
Die zweite MATCH-Funktion sucht die Spalte:
MATCH(F$4,hunits,0) // find column, returns 5
Hier erhalten wir den Lookup - Wert aus dem Spaltenkopf in Zeile 4, und verwenden Sie diese das Recht „auf“ Spalte in dem genannten Bereich zu finden hunits (O4: S4). Auch hier ist Anmerkung F $ 4 eine gemischte Referenz mit gesperrter Zeile, sodass die Formel nach unten kopiert werden kann.
Nachdem beide MATCH-Formeln die Ergebnisse an INDEX zurückgegeben haben, haben wir:
=$C5*INDEX(data,4,5)
Die Array Index versehen sind , die benannte Bereich Daten , (O5: S9). Mit einer Zeile von 4 und einer Spalte von 5 gibt INDEX 12 zurück, sodass wir ein Endergebnis von 12000 wie folgt erhalten:
=$C5*INDEX(data,4,5) =1000*12 =12000
