Office: (Office 365) SUMMEWENN mit verschiedenen Kriterien

Hallo,

ein neuer Versuch.


In der folgenden Tabelle habe Daten die mit SUMMEWENN verschieden WGR – Warengruppen zusammenzähle. Die Hardware WGR wird immer gezählt, die Spalte Zubehör nur wenn sie mit einer passenden WAGR vom Zubehör zusammen die gleiche Zahl in der Spalte BON haben.


Das Ergebnis soll ergeben zu viel Hardware jeweils auf dem gleichen BON ein Zubehör verkauft wurde.


Wenn ich in der Tabelle die Zubehör WGR zähle, wie die Hardware kommen die Zahlen so raus wie in der Spalte K3 – K4, ich benötige aber das Ergebnis wie in der Spalte K10 – K11, weil nur diese drei Zubehör zusammen mit einer Hardware die Gleiche BON Nummer haben.


Zwei Zubehör WGR auf einem Bon ergeben keinen Zähler und daher keine Rückmeldung.


Die Spalte B ARTNO tut hier nichts zur Sache, die Spalte Menge erhöht oder reduziert den Abverkauf

Vielen Dank für Eure Unterstützung.

 

Hallo,

da du Excel365 hast, habe ich dafür eine Formel gebastelt.
1) im Zellbereich J3:J6 etwaige Formeln oder Zahlen löschen
2) in Zelle J3 folgende Formel eingeben/kopieren:
=LET(wgr; $A$2:$A$56; bon; $D$2:$D$56; menge; $C$2:$C$56; hwWGR; $H$3:$H$6; zbWGR; $J$3:$J$6; hw; WENNFEHLER(VERGLEICH(wgr; hwWGR; 0); 0); fhwBON; FILTER(bon; hw > 0; "nix"); zb; WENNFEHLER(VERGLEICH(wgr; zbWGR; 0); 0); fzbBON; FILTER(bon; zb > 0; "nix"); hwBonZl; WENNFEHLER(VERGLEICH(fzbBON; fhwBON; 0); 0); fhwBonZl; FILTER(hwBonZl; hwBonZl > 0; "nix"); hwzbBON; EINDEUTIG(INDEX(fhwBON; fhwBonZl; 1)); suHwZb; SUMMEWENNS(menge; wgr; zbWGR; bon; hwzbBON); suHwZb)

In strukturierter Form ist sie etwas leichter verständlich/übersichtlicher:

Sie ermittelt die Zubehörmengen aus der Zubehörliste J3:J6, sofern bei einem bestimmten Bon auch Hardware aus der Hardwareliste H3:H6 enthalten ist.
Sie müsste somit genau die von dir gewünschten Mengen ermitteln.

 

Hallo,

beiliegend noch die modifizierte Excel-Datei. In dieser habe ich den Datenbereich A1:D56 in eine strukturierte Tabelle namens "tbBONS" umgewandelt.

Achtung: Ein Fehler ist im vorigen Posting #2 enthalten:
Die Formel gehört natürlich nicht in Zelle J3 eingegeben, sondern in Zelle K3

 

Super, vielen Dank und Hut ab.
Das sieht auf den ersten Blick sehr komplex aus. Ich teste das die Tage noch und gebe dir dann noch eine Rückmeldung.

Vielen herzlichen Dank

 

anbei die erwähnte Datei aus der Unterhaltung.

hier funktioniert die Formel leider nicht und ich verstehen den Grund nicht ....

 

Hallo,

ich habe die Formel jetzt nochmals mit deinen neuen Daten getestet und festgestellt, dass sie damit nicht funktioniert - mein Fehler.

Ich habe dir in den Namensmanager eine kleine benutzerdefinierte Funktion namens "VergleichFilter" eingefügt:

Dadurch konnte ich die bisherige LET-Funktion übersichtlicher gestalten. Der eigentliche Fehler lag in der letzten Zeile der LET-Funktion in der SUMMEWENNS-Funktion. Dadurch, dass ich diese in eine REDUCE-Funktion gekapselt habe, konnte ich den Fehler beseitigen. In Zelle BJ5 ist jetzt folgende geänderte LET-Funktion enthalten:

Ferner habe ich die LET-Funktion in Zelle BH5 effizienter Excel365-like gestaltet.
Geänderte Datei liegt bei.

 

Super, vielen Dank. Ich bau das die Tage ein.

 

Es tut mir echt leid, ich bekommen die Formel nicht adaptiert ...
die ganze Datei besteht aus mehreren solchen Tabellen, ich hab jetzt noch eine weitere hinzugefügt und bekomme wieder einen #WERT Fehler

 

Hallo,

da es in der Tabelle "Artikel" keine BONs gibt, die gleichzeitig die angegebenen Hardware-WGR und Zubehör-WGR enthalten, muss bei allen Zubehör-WGR ein 0-Resultat die Folge sein.
Da ich damit nicht rechnete bei meiner Formelerstellung, habe ich jetzt die im Namensmanager enthaltene Funktion "VergleichFilter" geringfügig modifiziert, indem ich die beiden Wörter "nix" durch eine 0 ersetzt habe.

Die LET-Formel habe ich nicht geändert. Sie liefert in einem solchen Fall lauter 0 als Menge bei den Zubehör-WGR.
Modifizierte Datei liegt bei.

 

Hallo,

ich kann dir nicht sagen was hier genau passiert, wenn ich (auch bei deiner Tabelle) nur eine Zahl ändere springt die Auswertung komplett auf #Wert um und ich kann es nur zurückstellen wenn ich schließe ohne speichern und neu öffne.

gibt es nicht noch eine andere Möglichkeit, gerne auch etwas umständlicher ?
Tut mir echt leid, das ich solche Umstände mache ...

 

Hallo,

in der beiliegenden Datei habe ich dir im Arbeitsblatt "Attach" in den Spalten/Zellen BR5:CA5 exakt die 10 Formeln eingefügt, die auch in der LET()-Funktion der Zelle BO5 enthalten sind. Auf diese Weise kannst du mitverfolgen, was sich da tut.

1) In den ersten 3 Spalten (BR,BS,BT) sind einfach die Daten der Tabelle "Artikel" (Spalte WGR, BON, Menge) enthalten.

2) In den nächsten beiden Spalten (BU,BV) mit Kopf "hwWGR" und "zbWGR" sind die Hardware-WGR und Zubehör-WGR deines jeweiligen Abfragemodells enthalten (zB. aus 2.Modell die Zellbereiche BL5:BL8 und BN5:BN20 - oder aus 1.Modell die Zellbereiche BG5:BG13 und BI5:BI62).

3) In den beiden folgenden Spalten (BW,BX) mit Kopf "fhwBON" und "fzbBON" sind die Bonnummer-Listen, die Hardware-WGR bzw. Zubehör-WGR enthalten, aufgeführt.

4) Dann folgt die Spalte BY mit Kopf "fhwBonZl". Hier sind die Zeilennummern aus der Hardware-Bon-Liste "fhwBON", die Zubehör-WGR enthalten, aufgelistet. Dabei beziehen sich die Nummern nicht auf die Zeilennummern des Arbeitsblattes, sondern auf die Zeilennummer innerhalb der Liste: Nr.1 ist Zeilennr.5 des Arbeitsblattes = Zelle BW5, Nr.2 = Zelle BW6, usw.).
Da es bei deinem 2.Modell keine Bonnummern gibt, die eine Kombination aus aufgelisteter Hardware-WGR und Zubehör-WGR enthalten, können dort auch keine Zeilennummern erscheinen - stattdessen wird eine 0 angezeigt.

5) In der Spalte BZ mit Kopf "hwzbBON" sind die Bonnummern, die zu den Zeilennummern aus Spalte BY gehören, aufgelistet. Da in deinem 2.Modell eine Zeilennr.=0 in Zelle BY5 enthalten ist, wird auch in BZ5 nur eine 0 angezeigt. Hier habe ich eine kleine Verbesserung in der LET-Funktion eingefügt, sodass im 0-Fall keine überflüssigen Bonnummern angezeigt werden, was aber auf das Resultat aus Punkt 6) keinen Einfluss hat.

6) Schlussendlich werden in der Spalte CA mit Kopf "suHwZb" die Mengen aufsummiert von Bons mit Hardware-WGR, die ebenfalls eine entsprechende aufgelistete Zubehör-WGR enthalten. Diese Liste muss genauso lang sein, wie die Liste der Zubehör-WGR in Spalte BV (Kopf "zbWGR"). Da in deinem 2.Modell keine entsprechenden Bonnummern gefunden wurden, werden in der Spalte CA natürlich auch nur 0-Mengen aufgelistet.

Diese Instabilität der Excel-Datei kann nichts mit meinen Funktionen zu tun haben. Es kann höchstens der Fall eintreten, dass bei zu viel an Daten in der Tabelle "Artikel" der benötigte Arbeitsspeicher im Hintergrund der Datei zu klein wird.

Modifizierte Datei liegt beil.