Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Zählen ohne Doppelte mit Kriterum: Bereich
#1
Hallo zusammen,

dies ist mein erster Beitrag, nachdem ihr mir schon viele Male dank der SuFu helfen könnte.
Aber jetzt komme ich einfach nicht weiter und benötige aktive Hilfe.

Habe folgende Tabelle (siehe auch Anhang):


[
Bild bitte so als Datei hochladen: Klick mich!
]

Ich möchte nun die Anzahl der Auftragsnummern zählen ohne Doppelte nach einem Kriterium, das Datum.
In meinem Beispiel möchte ich wissen, wie viele unterschiedlich Auftragsnummern ich am 29.05. hatte. Antwort ist "3" und kann mit der Matrix-Formel
{=SUMME((VERGLEICH(A1:A102&"#"&B1:B102;A1:A102&"#"&B1:B102;0)=ZEILE(1:102)*(B1:B102=E3))*1)}
berechnet werden. Soweit, so gut

Jetzt ist es so, dass meine Tabelle "in echt" wesentlich länger als 102 Zeilen (aktueller Bereich in der Matrix-Formel, siehe oben) sein kann, gerne auch mal 50.000 Zeilen. Um die Rechenleistung zu begrenzen, möchte ich den Bereich in der Formel nicht pauschal auf Zeile 50.000 setzen, weil Excel dann viele Sekunden/Minuten berechnet.

Deswegen habe ich überlegt mit dynamischen Bereichen zu arbeiten. Ich habe im Namensmanager für meine Bereiche Spalte A, Spalte B und den "ZEILE"-Bereich aus der Formel also mit "BEREICH.VERSCHIEBEN" und zählen von aktuellen Zeilen verformelt.

z.B. BEREICH.VERSCHIEBEN(Tabelle1!$A$1;;;ANZAHL2(Tabelle1!$A:$A);)
Anstatt also "A1:A102" einzugeben, nutze ich einfach meinen dynamischen Bereich "Auftragsnummer"

Allerdings scheint mit dynamischen Bereichen die Matrix-Formel nicht mehr zu funktionen. Zumindest bekomme ich kein Ergebnis wenn ich das so machen. Desweiteren fällt mir auch keine kluge Formel für den Bereich der ZEILE ein, siehe Einstellung im Namensmanager.
Aber egal, selbst wenn ich das mit der Zeile hinbekomme: Wie kann ich meine Berechnung möglichst performance-arm gestalten? Ich will nicht dass die Matrix-Formel jedes mall 50.000 Zeilen durchsucht, schon weil das auch nicht meine einzige Formel dieser Art ist. Dynamischer Bereich ist da doch das beste!?

Bin für jeden Hinweis dankbar.

Lg,
Chris


Angehängte Dateien
.xlsx   zählen ohne doppelte.xlsx (Größe: 10,36 KB / Downloads: 12)
Antworten Top
#2
Hi Chris,

füge eine Hilfsspalte, die du einfach ausblenden kannst, ein:

Arbeitsblatt mit dem Namen 'Tabelle1 (2)'
ABCDE
1AuftragsnummerDatumHS
2A29.05.20191
3A29.05.20192Kriterium29.05.2019
4A29.05.20193
5B29.05.20191
6C29.05.20191Anzahl4
7D30.05.20191
8E29.05.20191
9D30.05.201924
10X30.05.20191
11X30.05.20192
12C30.05.20192
13X30.05.20193

ZelleFormel
C2=ZÄHLENWENN($A$2:$A2;A2)
E6{=SUMME((VERGLEICH(A1:A102&"#"&B1:B102;A1:A102&"#"&B1:B102;0)=ZEILE(1:102)*(B1:B102=E3))*1)}
E9=SUMMENPRODUKT(($B$2:$B$13=$E$3)*($C$2:$C$13=1))
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#3
Boah, unfassbar. Wie einfach und schlank (Rechenleistung).
Vielen lieben Dank. Tut, bin sehr zurieden!

Nach anfänglicher Euphorie: Funktioniert nur so halb.

Wenn die Liste unsortiert ist oder eine Auftragsnummer mit zwei Daten auftaucht, dann passt es nicht mehr.
Aber ich habe Glück. In meinem Fall kann eine Auftragsnummer nur ein Datum haben und die Liste ist auch sortiert nach Auftragsnummer.
Antworten Top
#4
Hier noch eine Variante (zwar etwas lang, aber dafür ohne Hilfsspalte):
ABCDE
1AuftragsnummerDatum
2A29.05.2019
3A29.05.2019Kriterium29.05.2019
4A29.05.2019
5B29.05.2019
6C29.05.2019Anzahl3
7D30.05.2019
8D30.05.2019
9D30.05.2019
10X30.05.2019
11X30.05.2019
12X30.05.2019

ZelleFormel
E6{=SUMME(WENN(B2:INDEX(A:A;VERWEIS(2;1/(A2:A65000<>"");ZEILE(A2:A65000)))=E3;1/ZÄHLENWENNS(A2:INDEX(A:A;VERWEIS(2;1/(A2:A65000<>"");ZEILE(A2:A65000)));A2:INDEX(A:A;VERWEIS(2;1/(A2:A65000<>"");ZEILE(A2:A65000)));B2:INDEX(B:B;VERWEIS(2;1/(A2:A65000<>"");ZEILE(A2:A65000)));E3)))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
Die Formel geht jetzt maximal bis Zeile 65000 (kann man aber durch Anpassen der VERWEIS-Formel beliebig anpassen).

Etwas kürzer und etwas schneller allerdings mit einer Hilfszelle:
ABCDE
1AuftragsnummerDatum
2A29.05.2019
3A29.05.2019Kriterium29.05.2019
4A29.05.2019
5B29.05.2019Letzte Zeile:12
6C29.05.2019Anzahl3
7D30.05.2019
8D30.05.2019
9D30.05.2019
10X30.05.2019
11X30.05.2019
12X30.05.2019

ZelleFormel
E5=VERWEIS(2;1/(A2:A650000<>"");ZEILE(A2:A650000))
E6{=SUMME(WENN(B2:INDEX(A:A;E5)=E3;1/ZÄHLENWENNS(A2:INDEX(A:A;E5);A2:INDEX(A:A;E5);B2:INDEX(B:B;E5);E3)))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
Gruß
Michael
Antworten Top
#5
Als Übung für Power Query bestens geeignet... Stichwort "Berechnete diskrete Anzahl"...
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Antworten Top
#6
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$E$4" Then
    Cells(1, 10).CurrentRegion.ClearContents
    Range("E1:E2") = Application.Transpose(Array("", "=B2=" & Clng([E4])))
    Cells(1).CurrentRegion.AdvancedFilter 2, Range("E1:E2"), Cells(1, 10), -1
  End If
End Sub

In E6:


PHP-Code:
=COUNTA(J1:J30
Zum übersetzen von Excel Formeln:

http://dolf.trieschnigg.nl/excel/index.p...gids=en+de
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste