☰

Was genau macht diese SUMPRODUCT- und MID-Formel?

tag-icon • tag-icon microsoft-excel worksheet-function
Was genau macht diese SUMPRODUCT- und MID-Formel?

In einer Excel-Tabelle habe ich Texteinträge H23, w67, P45, usw. und ich möchte die numerischen Teile dieser summieren, d. h. 23, 67, 45.

Ich habe die Formel

=SUMPRODUCT((0&MID($H2:$S2,1+ISERROR(LEFT($H2:$S2)+0),10))+0)

das funktioniert, aber ich bin mir nicht sicher, was es tut, und ich möchte es nicht verwenden, ohne es vollständig zu verstehen. Kann jemand die Formel erklären?

Antwort1

Beginnen Sie mit einer einzelnen Zellformel, um Verwirrung zu vermeiden.

=SUMPRODUCT((0&MID($H2,1+ISERROR(LEFT($H2)+0),10))+0)
  1. Ihre Beispieldaten beginnen alle mit einem Buchstaben, aber das ISERROR(LEFT($H2:$S2)+0)System sucht nach einer Zahl oder einem Buchstaben als erstem Zeichen und fügt1wenn es ein Brief ist. DieLEFT-Funktionist standardmäßig ein einzelnes Zeichen ganz links und TRUE wird berücksichtigt1und FALSE ist0. Wenn das Zeichen ganz links alphabetisch ist, wird bei der Verwendung in einer mathematischen Operation ein Fehler generiert (z. B.<Zeichen>+ 0) also1wird hinzugefügt zu1imStartnummerParameter derMID-Funktionund es beginnt um2. Wenn kein Fehler generiert wird, dann0wird hinzugefügt zu1imStartnummerParameter.
  2. Der10ist die maximale Anzahl von Zeichen, die die MID-Funktion abzurufen versucht. Es können auch weniger verfügbar sein, diese werden dann abgerufen.
  3. Wenn überhaupt keine Zahl vorhanden ist,SUMPRODUCT-Funktionwird beim Versuch, eine Zeichenfolge mit der Länge Null (im Wesentlichen leer) in eine Zahl umzuwandeln, ersticken. Um dies zu verhindern,0wird dem von der MID-Operation zurückgegebenen Ergebnis vorangestellt.
  4. Das Ergebnis der MID-Operation (und die angehängten0) ist ein String, keine Zahl. Wenn alles gut gegangen ist, sollte das Ergebnis nur aus Ziffern bestehen. Ein String, der nur aus Ziffern besteht, kann in eine echte Zahl umgewandelt werden, ohne dass sich sein numerischer Wert ändert, indem man mit multipliziert1oder Hinzufügen0. zB Wenn Sie hinzufügen0Zu"123"Sie erhalten eine tatsächliche Anzahl von123.

Das sind alle internen Operationen. Sie müssen dies nur auf mehrere Spalten erweitern und die Array-Verarbeitung mit demSUMPRODUCT-Funktionals Wrapper.

=SUMPRODUCT((0&MID($H2:$S2,1+ISERROR(LEFT($H2:$S2)+0),10))+0)

Während die Spalten als absolut gesperrt sind, die Zeilen jedoch relativ bleiben, füllen Sie die nachfolgenden Zeilen nach unten aus.

verwandte Informationen