Microsoft Excel

Herbers Excel/VBA-Archiv

Anzahl Unterschiedliche


Betrifft: Anzahl Unterschiedliche von: UweD
Geschrieben am: 21.11.2017 14:22:41

Hallo Leute

Heute brauche ich mal wieder eure Hilfe.

Meine Tabelle (natürlich viel länger) sieht wie folgt aus

Lager

 ABCDEFGHIJ
1LAGERORTARTIKELNUMMERBEZ ARTIKELDATUMMENGEENHVERBRAUCHT FÜRAnzahlSummeModelle (Wunsch)
2A001100222333VERSTÄRUNGSPLATTE 28.08.201785ST Golf31202
3A001100222333VERSTÄRUNGSPLATTE 28.08.201716ST Polo   
4A001100222333VERSTÄRUNGSPLATTE 29.08.201719ST Polo   
5A0013001245789SCHRAUBE 4*10 28.08.2017200ST Golf1310423
6A0013001245789SCHRAUBE 4*10 31.08.201772ST Golf   
7A0013001245789SCHRAUBE 4*10 04.09.201742ST Polo   
8A0013001245789SCHRAUBE 4*10 07.09.2017136ST Turan   
9A0013001245789SCHRAUBE 4*10 11.09.201772ST Turan   
10A0013001245789SCHRAUBE 4*10 14.09.20178ST Turan   
11A0013001245789SCHRAUBE 4*10 15.09.201732ST Turan   
12A0013001245789SCHRAUBE 4*10 18.09.201716ST Polo   
13A0013001245789SCHRAUBE 4*10 19.09.201780ST Turan   
14A0013001245789SCHRAUBE 4*10 21.09.2017112ST Turan   
15A0013001245789SCHRAUBE 4*10 22.09.201796ST Turan   
16A0013001245789SCHRAUBE 4*10 14.10.201796ST Turan   
17A0013001245789SCHRAUBE 4*10 16.10.201780ST Turan   
18A0014.711ANSCHL.-KABEL31.08.201724ST Tuareg61561
19A0014.711ANSCHL.-KABEL11.09.201712ST Tuareg   
20A0014.711ANSCHL.-KABEL19.09.201728ST Tuareg   
21A0014.711ANSCHL.-KABEL21.09.201732ST Tuareg   
22A0014.711ANSCHL.-KABEL22.09.201740ST Tuareg   
23A0014.711ANSCHL.-KABEL29.09.201720ST Tuareg   

verwendete Formeln
Zelle Formel Bereich N/A
H2:H23=WENN(ZÄHLENWENN($B$2:B2;B2)=1;ZÄHLENWENN($B:$B;B2);"")  
I2:I23=WENN(H2<>"";SUMMEWENN(B:B;B2;E:E);"")  
http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://Hajo-Excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 21.10 einschl. 64 Bit



Die Formeln für H und I funktionieren.

Für Spalte J hab ich keine Idee.
- Dabei sollen je Artikelnummer die Anzahl der unterschiedlichen Verursacher gezählt werden.

Wer kann mir helfen?

LG UweD


  

Betrifft: AW: Anzahl Unterschiedliche von: UweD
Geschrieben am: 21.11.2017 16:55:20

Hier noch die Datei

https://www.herber.de/bbs/user/117817.xlsx


  

Betrifft: {=SUMME(1/ZÄHLENWENN(Bereich;Bereich))} von: Matthias L
Geschrieben am: 21.11.2017 16:55:58

Hallo

Mal als kleinen Vorschlag

Tabelle2

 ABCDEFGHIJKL
11LAGERORTARTIKELNUMMERBEZ ARTIKELDATUMMENGEENHVERBRAUCHT FÜRAnzahlSummeWunschVorschlag
22A001100222333VERSTÄRUNGSPLATTE28.08.201785STGolf312022
33A001100222333VERSTÄRUNGSPLATTE28.08.201716STPolo    
44A001100222333VERSTÄRUNGSPLATTE29.08.201719STPolo    
55A0013001245789SCHRAUBE 4*1028.08.2017200STGolf13104233
66A0013001245789SCHRAUBE 4*1031.08.201772STGolf    
77A0013001245789SCHRAUBE 4*1004.09.201742STPolo    
88A0013001245789SCHRAUBE 4*1007.09.2017136STTuran    
99A0013001245789SCHRAUBE 4*1011.09.201772STTuran    
1010A0013001245789SCHRAUBE 4*1014.09.20178STTuran    
1111A0013001245789SCHRAUBE 4*1015.09.201732STTuran    
1212A0013001245789SCHRAUBE 4*1018.09.201716STPolo    
1313A0013001245789SCHRAUBE 4*1019.09.201780STTuran    
1414A0013001245789SCHRAUBE 4*1021.09.2017112STTuran    
1515A0013001245789SCHRAUBE 4*1022.09.201796STTuran    
1616A0013001245789SCHRAUBE 4*1014.10.201796STTuran    
1717A0013001245789SCHRAUBE 4*1016.10.201780STTuran    
1818A0014.711ANSCHL.-KABEL31.08.201724STTuareg615611
1919A0014.711ANSCHL.-KABEL11.09.201712STTuareg    
2020A0014.711ANSCHL.-KABEL19.09.201728STTuareg    
2121A0014.711ANSCHL.-KABEL21.09.201732STTuareg    
2222A0014.711ANSCHL.-KABEL22.09.201740STTuareg    
2323A0014.711ANSCHL.-KABEL29.09.201720STTuareg    

Formeln der Tabelle
ZelleFormel
L2{=SUMME(1/ZÄHLENWENN(H2:H4;H2:H4))}
L5{=SUMME(1/ZÄHLENWENN(H5:H17;H5:H17))}
L18{=SUMME(1/ZÄHLENWENN(H18:H23;H18:H23))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Matthias


  

Betrifft: AW: {=SUMME(1/ZÄHLENWENN(Bereich;Bereich))} von: UweD
Geschrieben am: 22.11.2017 16:42:44

Hallo Matthias

Ich kam eben erst dazu das zu testen. Die Grenzen will ich aber nicht von Hand eingeben.


das sollte dynamisch gehen.

Trotzdem Dank

LG UweD


  

Betrifft: am eifachsten mit Hilfsspalte von: WF
Geschrieben am: 21.11.2017 17:53:11

Hi,

in K2 steht:
=B2&G2
runterkopieren

in J2 dann:
=WENN(H2<>"";SUMMENPRODUKT((B$2:B$99=B2)/ZÄHLENWENN(K$2:K$99;K$2:K$99&""));"")
runterkopieren

WF


  

Betrifft: AW: am eifachsten mit Hilfsspalte von: UweD
Geschrieben am: 22.11.2017 17:19:04

Hallo Walter


deine Version klappt auch.

Scheint aber am rechenintensivsten zu sein.

Wie bei den Anderen bereits geschrieben. leider 18.000 Datensätze


Besten Dank


LG UweD


  

Betrifft: AW: mit nur 1ner Matrixfunktion(alität)sformel... von: ... neopa C
Geschrieben am: 21.11.2017 18:07:13

Hallo Uwe,

... ganz ohne klassische Matrixformel und ohne Hilfsspalte, kannst Du Dein Wunschergebnis auch ermitteln. Formel nach unten kopierst,

 BDGHI
1ARTIKELNUMMERDATUMFÜRAnzahlSumme
2100.222.33328.08.2017Golf2120
3100.222.33328.08.2017Polo  
4100.222.33399.08.2017Polo  
53.001.245.78928.08.2017Golf31042
63.001.245.78931.08.2017Golf  
73.001.245.78904.09.2017Polo  
83.001.245.78907.09.2017Turan  
93.001.245.78911.09.2017Turan  
103.001.245.78914.09.2017Turan  
113.001.245.78915.09.2017Turan  
123.001.245.78918.09.2017Polo  
133.001.245.78919.09.2017Turan  
143.001.245.78921.09.2017Turan  
153.001.245.78922.09.2017Turan  
163.001.245.78914.10.2017Turan  
173.001.245.78916.10.2017Turan  
184.71131.08.2017Tuareg1156
194.71111.09.2017Tuareg  
204.71119.09.2017Tuareg  
214.71121.09.2017Tuareg  
224.71122.09.2017Tuareg  
234.71129.09.2017Tuareg  
24     

Formeln der Tabelle
ZelleFormel
H2=WENN((B2="")+(B2=B1); "";SUMME(INDEX((VERGLEICH((B$1:B$99=B2)&G$1:G$99;(B$1:B$99=B2)&G$1:G$99;0)=ZEILE(G$1:G$99))*(B$1:B$99=B2); )))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4



Gruß Werner
.. , - ...


  

Betrifft: gut, aber Du musst Dich auf Spalte H beziehen von: WF
Geschrieben am: 21.11.2017 18:35:46

=WENN(H2=""; "";SUMME(INDEX((VERGLEICH((B$1:B$99=B2)&G$1:G$99;(B$1:B$99=B2)&G$1:G$99;0) =ZEILE(G$1:G$99))*(B$1:B$99=B2); )))
sonst geht's bei Leerzeilen daneben

WF


  

Betrifft: AW: muss ich nicht ... von: ... neopa C
Geschrieben am: 21.11.2017 19:48:52

Hallo WF,

... denn:
1.) wer macht denn schon so was? Und
2.) würde ich eine derartige Auswertung eh nicht über eine ganze Ergebnisspalte ziehen, wofür
3.) die Datentabelle offensichtlich schon nach Artikelnummern sortiert sein muss und allein dadurch auch keine Zwischenleerzeilen anfallen und
4.) wenn wirklich Zwischenleerzeilen vorhanden sein sollten, dann nutze ich eben einfach eine andere Vorbedingung in der Formel. Damit kann man sogar aus eine unsortierte Datentabelle die spez. gewünschten Ergebnis-Spalten-Liste ermittelt werden.

Einfach anstelle =WENN((B2="")+(B2=B1);... =WENN((B2="")+(ZÄHLENWENN(B$1:B2;B2)<>1);...

Aber wie zu 1.) bereits geschrieben, würde ich sowie in einer separaten Liste mit INDEX() und AGGREGAT() die Artikelnummern ermitteln und dieser dann meine Auswertungsformel pur zuweisen.

Gruß Werner
.. , - ...


  

Betrifft: das ist die Praxis von: WF
Geschrieben am: 21.11.2017 20:15:08

Bei langen Listen gibt es immer Leerzeilen und/oder Zwischenüberschriften.

WF


  

Betrifft: AW: wie bereits dargelegt ... von: ... neopa C
Geschrieben am: 21.11.2017 20:35:00

Hallo,

... meine mit ... +(ZÄHLENWENN(B$1:B2;B2)<>1) ergänzte Formel H2 hätte damit auch kein Problem.
Doch wer will bei längeren Listen die Ergebniswerte wirklich erst durch mühsames Scollen sehen? ich nicht. Da ist mein Vorschlag, den ich unter 1.) angezeigt hatte, schon viel eher geeignet.

Deshalb stell ich den nun doch noch ergänzend ein. Da Uwe´s Artikelnummern nur spez. formatierte Zahlenwerte sind, braucht man für das Duplikat freie listen der Artikelnummern dazu noch nicht mal mehr INDEX().

 JK
1Art_Nr.Typ_Anz
24.7111
3100.222.3332
43.001.245.7893
5  

Formeln der Tabelle
ZelleFormel
J2=WENNFEHLER(AGGREGAT(15;6;B$2:B$100/(B$2:B$100<>"")/(ZÄHLENWENN(J$1:J1;B$2:B$100)=0); 1); "")
K2=WENN(J2="";"";SUMME(INDEX((VERGLEICH((B$1:B$100=J2)&G$1:G$100;(B$1:B$100=J2)&G$1:G$100;0)=ZEILE(G$1:G$100))*(B$1:B$100=J2); )))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: mit nur 1ner Matrixfunktion(alität)sformel... von: UweD
Geschrieben am: 22.11.2017 16:56:42

Hallo Werner

kam nicht früher dazu, das zu testen.

Klappt, wobei bei 18,000 Zeilen schon 30 min gerechnet werden.
Muss ich ja aber nur 1x machen, da es eine statische Auswertung ist.

Dann in Werte umwandeln.


Besten Dank

LG UweD


  

Betrifft: AW: nachgefragt ... von: ... neopa C
Geschrieben am: 22.11.2017 17:13:02

Hallo Uwe,

... für welche Lösungsmethode hast Du denn Dich entschieden? Offensichtlich die, auf die Du hier geantwortet hast? Dann teste bei Gelegenheit nochmal meine alternative Lösung, die ich gestern Abend als letztes eingestellt habe?

Obwohl generell festzustellen ist, dass für Massendatenauswertung keine der beiden Lösungsvorschläge konzipiert waren und dafür auch nur bedingt (z.B. so wie Du es jetzt genutzt hast) sinnvoll sind

Gruß Werner
.. , - ...


  

Betrifft: Danke von: UweD
Geschrieben am: 24.11.2017 09:21:19

Hallo Werner


es war eine Datei mit 12 Tabellenblättern.

In Blatt 1 hatte ich Deine Erste Formel benutzt. Hatte aber den Bereich recht grosszügig gewählt.
Im nächsten Blatt vorher die Anzahl Datensätze ermittelt und dann die Formel genauer angepasset.
Ging schon schneller.

Irgendwann dann mal WF's Formel. Das dauerte länger.

Also in den restlichen Blättern wieder Deine.




Die zweite Lösung von dir habe ich wegen der Vielzahl der Blätter nicht mehr benutzt.



Wie bereits geschrieben: Es war eine (hoffentlich) einmalige Auswertung.



Nochmals Danke an euch alle

LG UweD


  

Betrifft: AW: und Dir auch danke für Deine Info owT von: ... neopa C
Geschrieben am: 24.11.2017 10:04:58

Gruß Werner
.. , - ...


Beiträge aus den Excel-Beispielen zum Thema "Anzahl Unterschiedliche"