Zusammenfassung
Um Textwerte in Zahlen zu übersetzen und das Ergebnis zu summieren, können Sie eine INDEX- und MATCH-Formel sowie die SUMME-Funktion verwenden. In dem gezeigten Beispiel lautet die Formel in H5:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
Dabei ist "Code" der benannte Bereich K5: K9 und "Wert" der benannte Bereich L5: L9.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Das Herzstück dieser Formel ist eine grundlegende INDEX- und MATCH-Formel, mit der Textwerte in Zahlen übersetzt werden, wie sie in einer Nachschlagetabelle definiert sind. Um beispielsweise "EX" in die entsprechende Zahl zu übersetzen, verwenden wir:
=INDEX(value,MATCH("EX",code,0))
das würde 4 zurückgeben.
Die Wendung bei diesem Problem besteht jedoch darin, dass wir einen Bereich von Textwerten in den Spalten C bis G in Zahlen übersetzen und summieren möchten. Dies bedeutet, dass wir mehr als einen Suchwert angeben müssen und INDEX mehr als ein Ergebnis zurückgeben muss. Der Standardansatz ist eine Formel wie diese:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Nachdem MATCH ausgeführt wurde, haben wir ein Array mit 5 Elementen:
=SUM(INDEX(value,(2,2,3,2,5)))
Es scheint also, dass INDEX 5 Ergebnisse an SUM zurückgeben sollte. Wenn Sie dies versuchen, gibt die INDEX-Funktion jedoch nur ein Ergebnis SUM zurück. Damit INDEX mehrere Ergebnisse zurückgibt, müssen wir einen ziemlich undurchsichtigen Trick verwenden und MATCH wie folgt in N und IF einschließen:
N(IF(1,MATCH(C5:G5,code,0)))
Dies zwingt INDEX effektiv dazu, der SUMME-Funktion mehr als einen Wert bereitzustellen. Nachdem INDEX ausgeführt wurde, haben wir:
=SUM((3,3,2,3,-1))
Die SUMME-Funktion gibt die Summe der Elemente im Array 10 zurück. Eine ausführliche Beschreibung dieses Verhaltens finden Sie in diesem interessanten Artikel auf der EXCELXOR-Website.
