ZusammenfassungUm Textwerte in Zahlen umzuwandeln und das Ergebnis zu summieren, können Sie eine INDEX- und MATCH-Formel sowie die SUM-Funktion verwenden. Im gezeigten Beispiel lautet die Formel in H5:
{= SUM ( INDEX (value, N ( IF (1, MATCH (C5:G5,code,0)))))}
wo 'code' ist benannter Bereich K5:K9 und 'Wert' ist der benannte Bereich L5:L9.
Hinweis: Dies ist ein Array-Formel , und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.
Erläuterung
Das Herzstück dieser Formel ist eine grundlegende INDEX- und MATCH-Formel, die verwendet wird, um Textwerte in Zahlen zu übersetzen, die in einer Nachschlagetabelle definiert sind. Um beispielsweise 'EX' in die entsprechende Zahl zu übersetzen, würden wir Folgendes verwenden:
= INDEX (value, MATCH ('EX',code,0))
was würde 4 zurückgeben.
Die Wendung an diesem Problem besteht jedoch darin, dass wir a . übersetzen und summieren wollen Bereich von Textwerten in den Spalten C bis G zu Zahlen. Das bedeutet, dass wir mehr als einen Lookup-Wert bereitstellen 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 zu SUM zurückgeben sollte. Wenn Sie dies jedoch versuchen, gibt die INDEX-Funktion nur ein Ergebnis SUM zurück. Damit INDEX mehrere Ergebnisse zurückgibt, müssen wir eher verwenden obskurer Trick , und wickeln Sie MATCH wie folgt in N und IF ein:
N ( IF (1, MATCH (C5:G5,code,0)))
Dies zwingt INDEX effektiv dazu, mehr als einen Wert für die SUM-Funktion bereitzustellen. Nachdem INDEX ausgeführt wurde, haben wir:
Formel zur Berechnung der Varianz in Excel
= SUM ({3,3,2,3,-1})
Und die SUM-Funktion gibt die Summe der Elemente im Array zurück, 10. Eine gute Beschreibung dieses Verhaltens finden Sie unter dieser interessante Artikel auf der EXCELXOR-Website .
Autor Dave Bruns
