Zählen Sie, wie viele eindeutige Werte in einer Spalte vorhanden sind, während eine andere Spaltenanforderung erfüllt wird

492
Fam

Hallo, ich habe Daten in einer Tabelle in der Form:

Order# Origin Status Date 90028 BG00 30 1/2/2018 90021 BG01 50 2/2/2018 90021 BG01 40 3/2/2018 90028 BG01 20 2/2/2018

Und ich möchte zählen, wie viele eindeutige Bestellnummern vorhanden sind (zählen ohne Duplikat).

Origin from BG00 hat einen Status 30 oder 40 hat ein heutiges Datum

Hilfe wird so schnell wie möglich geschätzt.

0
Schauen Sie sich [diese Erklärung] (https://exceljet.net/formula/count-unique-text-values-with-criteria) an, um zu sehen, ob es hilft. PeterT vor 6 Jahren 0

1 Antwort auf die Frage

0
Rajesh S

enter image description here

Schreiben Sie diese Matrixformel in F76 und beenden Sie die Eingabe mit Strg + Umschalt + Eingabetaste .

{=SUM(IF(("BG00"=$B$76:$B$79)*($C$76:$C$79>=30)*($C$76:$C$79<=40)*(E74=$D$76:$D$79), 1/COUNTIFS($B$76:$B79, "BG00", $A$76:$A$79, $A$76:$A$79, $C$76:$C$79, ">="&30, $C$76:$C$79, "<="&40,$D$76:$D$79,E74)),0)}

Bearbeitet:

Wie es funktioniert:

Der erste Teil der Formel gibt 90028von A76:A79da Bestell - ID in Zeile 1 nur paßt zu allen drei conditons .

{=SUM(IF(("BG00"=$B$76:$B$79)*($C$76:$C$79>=30)*($C$76:$C$79<=40)*(E74=$D$76:$D$79),A76:A79,0))}

Anmerkung: Die obige Formel wurde absichtlich hinzugefügt A76:A79, um den Wert von Range zu erhalten. Andernfalls wird der zweite Teil Countifsals True- Bedingungen der IF eingegeben und es wird 1 zurückgegeben (nur One Match).

{=COUNTIFS($B$76:$B79, "BG00", $A$76:$A$79, $A$76:$A$79, $C$76:$C$79, ">="&30, $C$76:$C$79, "<="&40,$D$76:$D$79,E74)),0)}

Wenn Auftrag Id 90028in row 4würde alle drei Bedingungen entsprechen Orgin, Status & Datedann Countifswürde Rückkehr haben 2.

SUM& 1/Countifswurde der Formel als "aufgegebene Vorsichtsmaßnahme" hinzugefügt, andernfalls funktioniert diese Formel auch ohne sie.

{=IF(("BG00"=$B$76:$B$79)*($C$76:$C$79>=30)*($C$76:$C$79<=40)*(E74=$D$76:$D$79), COUNTIFS($B$76:$B79, "BG00", $A$76:$A$79, $A$76:$A$79, $C$76:$C$79, ">="&30, $C$76:$C$79, "<="&40,$D$76:$D$79,E74),0)}

SUMist sehr nützlich, wenn Werte aus dem Bereich A76:A79zu den Kriterien hinzugefügt werden müssen.

Hinweis,

  • Passen Sie die Zellverweise in der Formel nach Bedarf an.
  • Wenn Sie das heutige Datum in E74 eingeben, müssen Sie die Spalte "Datum" mit wenigen ähnlichen Datumswerten füllen , mit denen übereinstimmen soll.
Die Formel ist komplex und nicht unkompliziert, und das OP muss sie an ihre eigene Tabelle anpassen. Können Sie eine Erklärung hinzufügen, wie und warum es funktioniert? fixer1234 vor 6 Jahren 0
@ fixer1234, es war die einzige Möglichkeit, da die Formel 3 Ursprung, Status und Datum testen muss, um den eindeutigen Wert zu ermitteln. Rajesh S vor 6 Jahren 0
@ fixer1234 was meinst du damit "das OP muss es an seine eigene Tabelle anpassen?" Fam vor 6 Jahren 0
@Fam, die Website ist eine Wissensdatenbank mit Lösungen, die jedem helfen kann, der ähnliche Probleme hat. Sie oder ein anderer Leser haben keine Tabelle, die genau diesem Beispiel entspricht. Damit eine Lösung nützlich ist, muss jeder Leser verstehen, wie er funktioniert, um ihn an die spezifischen Bedürfnisse seiner Bedürfnisse anzupassen. Diese Lösung ist nicht trivial oder naheliegend, weshalb ich Rajesh vorschlug, einige Erklärungen zu seiner Funktionsweise hinzuzufügen, um die Antwort nützlicher zu machen. fixer1234 vor 6 Jahren 0
@ fixer1234, bald werde ich das bearbeiten und erklären, wie es funktioniert, danke. Rajesh S vor 6 Jahren 0
@ fixer1234, ich habe die Antwort bearbeitet und versuche am besten zu erklären, wie beide Teile der Formel funktionieren und sich koordinieren. Auch die modifizierte Version der Formel ohne 'SUM' & '1 / COUNTIS' wird OP und Lesern helfen, sie mit ihren Arbeitsblättern anzupassen. :-) Rajesh S vor 6 Jahren 0
Serviervorschläge zur Verdeutlichung: 1)> = 30 & <= 40 bedeutet, dass jeder Wert dazwischen die Kriterien erfüllt (z. B. 35). Frage definiert Anforderung als 30 oder 40. 2) Es ist nicht offensichtlich, was 1 / countifs tut. wie es funktioniert, warum es da ist oder wie es eine korrekte Anzahl zurückgibt, wenn Anzahl> 1. 3) Innerhalb der Countifs ist es nicht offensichtlich, wie bei $ A $ 76: $ A $ 79, $ A $ 76: $ A $ 79 mehrere potenzielle Aufträge mit jedem Wert verglichen werden, ohne ein Vielfaches der Doppelzählung. Gleiches Problem mit 30 oder 40. 4) Sieht so aus, als wären die schließenden Klammern nicht an der richtigen Stelle. fixer1234 vor 6 Jahren 0
@ fixer1234, `> = 30 & <= 40` umfasst alle Zahlen zwischen '30 & 40', einschließlich '30 & 40'. COUNTIFs ist beendet, die Ergebnisse werden als Divisor mit 1 als Zähler verwendet. Werte, die in Daten einmal angezeigt werden, werden im Array als 1 angezeigt, Werte, die mehrmals angezeigt werden, werden als Bruchwerte angezeigt, die dem Mehrfachen entsprechen. (dh ein Wert, der fünfmal im Datenbereich erscheint, generiert 5 Elemente im Array mit einem Wert von 1/5 = .2). Ja, die "Countifs" könnten auch ohne "A76: A79" funktionieren. Sie wurde aus Sicherheitsgründen hinzugefügt, da ein größerer Datenbereich als sicherere Seite für ein mehrfaches Auftreten betrachtet wird. Rajesh S vor 6 Jahren 0
So für einen einzelnen Datenbereich `= SUMPRODUCT (1 / COUNTIF (A76: A79, A76: A79))` mit derselben Methode. Eine andere einfache Formel, die auch für OP funktioniert, sollte `= SUMPRODUCT sein (COUNTIFS ($ B $ 76: $ B79," BG00 "), $ C $ 76: $ C $ 79,"> = "& 30, $ C $ 76: $ C $ 79," <= "& 40, $ D $ 76: $ D $ 79, E74))` gibt 1 zurück. * Die angezeigte Matrixformel ist jedoch angesichts des größeren Datenbereichs auch besser und sicherer. * Rajesh S vor 6 Jahren 0
Die Zahlen zwischen 30 und 40 unterscheiden sich von der Anforderung von 30 oder 40. Außerdem sieht es so aus, als seien die schließenden Klammern an der falschen Stelle. Die Erklärung in den Kommentaren erklärt nicht wirklich "warum" die Formel das angeforderte Ergebnis liefern sollte oder in allgemeinen Fällen außerhalb dieses speziellen Beispiels funktionieren sollte. Fügen Sie in Ihrem Beispiel ein Duplikat der Daten der Zeile 76 hinzu und prüfen Sie, ob Sie immer noch dieselbe Antwort erhalten. fixer1234 vor 6 Jahren 0
Lassen Sie uns [diese Diskussion im Chat fortsetzen] (https://chat.stackexchange.com/rooms/80599/discussion-between-rajesh-s-and-fixer1234). Rajesh S vor 6 Jahren 0

Verwandte Probleme