Werte für qualifizierende Einträge summieren, ohne sie doppelt zu zählen in Excel

Werte für qualifizierende Einträge summieren, ohne sie doppelt zu zählen in Excel

Ich habe eine Sammlung von Textzeichenfolgen, denen jeweils ein numerischer Wert zugeordnet ist. Ich muss die zugehörigen numerischen Werte für die Einträge summieren, die „qualifiziert“ sind. Ein Texteintrag ist qualifiziert, wenn er eine oder mehrere bestimmte Zielzeichenfolgen enthält. Ein Eintrag kann möglicherweise mehrere der Zielzeichenfolgen oder eine Zielzeichenfolge mehr als einmal enthalten. Ich möchte den zugehörigen Wert für den Eintrag jedoch nur einmal summieren, wenn der Eintrag qualifiziert ist, weil er eine Übereinstimmung mit einem der Ziele oder einer Zielkombination enthält.

Angenommen, die Zellen A1:A3 enthalten jeweils apple, banana, pear, und B1:B3 enthalten jeweils die Zahl 1. Meine Suchziele sind aund p. Alle drei Texteinträge erfüllen die Voraussetzungen, da sie jeweils mindestens eine Instanz von mindestens einem der Ziele enthalten. Die Summierung der zugehörigen Werte in Spalte B sollte ein Ergebnis von zurückgeben 3.

Ich habe dies mithilfe von SUMIF und Platzhalterzielen versucht. Meine Formel für dieses Beispiel lautet:

=SUM(SUMIF(A:A, {"*a*", "*p*"}, B:B))

Allerdings werden Einträge, die mehr als einem Ziel entsprechen, doppelt gezählt. In diesem Fall enthalten alle drei aund zwei enthalten auch p, sodass die Summe aus ergibt 5.

Wie kann ich dies erreichen, ohne Einträge doppelt zu zählen?

Antwort1

Es ist flexibler, wenn Ihre Kriterien in tatsächlichen Zellen im Arbeitsblatt vorhanden sind, anstatt sie fest in der Formel zu codieren.

Wenn Sie einVertikale, zusammenhängender Zellbereich (zB H1:H2) zu diesem Zweck und unter der Annahme, dassReichweiteist einVertikaleBereich, können Sie diese verwendenArray-Formel**:

=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))

Wenn Sie darauf bestehen, die Kriterien in der Formel zu haben, dann:

=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))

Grüße

Antwort2

Hier ist eine relativ einfache Lösung. Mit den verknüpften Werten all 1wird das gewünschte Ergebnis erzielt 3, aber ich habe unterschiedliche Werte zugewiesen, um zu zeigen, dass die richtigen Werte ausgewählt sind, und sicherheitshalber einen nicht übereinstimmenden Eintrag eingefügt.

Bildbeschreibung hier eingeben

Die Liste der Einträge steht in Spalte C und die dazugehörigen Werte in Spalte D. Das Ergebnis finden Sie in E1.

Nur bestimmte Funktionen können Platzhalter verwenden. Hier wird also SEARCH für die Zielzeichenfolge verwendet.

Die typische Methode zum Umgang mit mehreren ODER-Kriterien besteht darin, die Ergebnisse jedes Tests zu addieren. Dies zählt jedoch doppelt, wenn die Elemente mehrere Kriterien erfüllen können. Um dieses Problem zu lösen, werden die aggregierten Kriterientests überprüft, um festzustellen, ob die Summe größer als Null ist, und dies wird mit dem zugehörigen Wert verwendet.

Die Handhabung der Zielsuchzeichenfolgen als Array wird kompliziert, da die für diese Art von Formeln verwendeten allgemeinen Funktionen ein Ergebnis für das gesamte Array berechnen, bevor sie es auf den nächsten Begriff anwenden. Daher habe ich jedes Kriterium separat behandelt. Für weitere Kriterien fügen Sie einfach ISNUMBER(SEARCH("target",range))für jedes Kriterium ein weiteres in den Klammern vor dem >0Test hinzu.

SUMPRODUCT führt die Array-Stil-Berechnungen mit einer normalen, nicht Array-Formel durch.

Die Formel in E1 lautet:

=SUMPRODUCT(((ISNUMBER(SEARCH("p",C1:C4))+ISNUMBER(SEARCH("a",C1:C4)))>0)*D1:D4)

Antwort3

Ich dachte wirklich, das wäre eine SUMPRODUCT(--Formel, aber ich kann sie nicht zum Laufen bringen. Das hier sollte aber funktionieren -

=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))

Wo {"d","g"}sich Ihr Array mit Suchzeichenfolgen befindet.

Es handelt sich um eine Array-Formel. Wenn Sie sie also eingegeben haben, müssen Sie ctrl+ shft+ drücken entr. In der Formelleiste sollten dann geschweifte Klammern um die gesamte Funktion angezeigt werden.

Beachten Sie, dass es nur funktioniert, wenn Sie eine einzelne Spalte durchsuchen.

Möglicherweise funktioniert das nicht so gut, wenn man Platzhalter berücksichtigt. Vielleicht brauchen Sie Regex?

verwandte Informationen