Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 10:30:26
bedingte Formatierung lange Zahlen
18.04.2024 09:22:48
VBA Filter in intelligenter Tabelle funktioniert nicht
18.04.2024 08:54:52
automatisches übertragen von Daten
18.04.2024 08:16:13
Dynamische Positionsnummerierung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1340to1344
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1340to1344
1340to1344
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zähnenwenns ohne Doppeltzählungen

Zähnenwenns ohne Doppeltzählungen
27.11.2013 10:23:38
Kina
Hallo zusammen,
Möchte folgendes machen:
Habe in Spalte C Bundesländer stehen und in Spalte B Produktenummern. Was ich möchte ist wissen in welchen Bundesländern wie viele verschiedene Produkte bestellt wurden.
Mein Ansatz:
=ZÄHLENWENNS(C4:C200;"NRW";4:B200;"ohne Doppelzzählung")
10 NWR
11 NWR
12 NWR
10 NWR
Resultat müsste 3 sein.
Weiss bloss nicht wie ich "ohne Doppeltzählung" umsetzen soll...
Jemand eine Idee?

27
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 10:31:26
Klaus
Hallo Kina,
das würd ich ganz unkompliziert mit zwei Hilfsspalten lösen:
https://www.herber.de/bbs/user/88277.xlsx
Eine Formellösung ohne Hilfsspalten wäre bestimmt auch möglich, aber ungleich komplexer.
Grüße,
Klaus M.vdT.

AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 10:48:25
Kina
Vielen Dank! Ich habe jedoch alle Bundesländer und wenn ich da alles über Hilfsspalen lösen muss wird das alleine ein riesen Excel...
Kennt vielleicht doch jemand eine Formellösung?

AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 11:27:06
Kina
hmm..

AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 12:25:44
Christian
Hallo Kina,
siehe:
http://www.excelformeln.de/formeln.html?welcher=44
 ABCDEF
1 ProduktNrBundesland   
2 10NWR BundesLandNWR
3 11NWR Nummern:3
4 12NWR   
5 10NWR   
6 10SH   
7 11SH   
8 12SH   
9 10SH   

Formeln der Tabelle
ZelleFormel
F3=SUMMENPRODUKT((VERGLEICH(B2:B101&C2:C101;B2:B101&C2:C101;0)=ZEILE(1:100))*(B2:B101<>"")*(C2:C101=F2))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
MfG Christian

Anzeige
AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 13:20:11
Kina
Danke Christian!
Diese Formel hatte ich auch schon versucht, aber angewendet auf meine Daten habe ich immer O als Resultat:-(
=SUMMENPRODUKT((VERGLEICH(File!B4:B200&File!C4:C200;File!B4:B200&File!C4:C200;0)=ZEILE(File!3:199)) *(File!B4:B200"")*(File!C4:C200=A6))
Was übersehe ich?

AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 13:20:11
Kina
Danke Christian!
Diese Formel hatte ich auch schon versucht, aber angewendet auf meine Daten habe ich immer O als Resultat:-(
=SUMMENPRODUKT((VERGLEICH(File!B4:B200&File!C4:C200;File!B4:B200&File!C4:C200;0)=ZEILE(File!3:199)) *(File!B4:B200"")*(File!C4:C200=A6))
Was übersehe ich?

Anzeige
AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 13:33:25
Christian
Hallo Kina,
Du musst ZEILE() korrekt anpassen!
=SUMMENPRODUKT((VERGLEICH(File!B4:B200&File!C4:C200;File!B4:B200&File!C4:C200;0)=ZEILE(File!1:197)) *(File!B4:B200<>"")*(File!C4:C200=A6))
MfG Christian

AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 14:18:59
Kina
Super!!!!
Danke Christian

AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 13:34:53
Firmus
Hi Kina,
schau Dir mal diesen etwas anderen Ansatz an.
https://www.herber.de/bbs/user/88280.xlsx
Vllt. zeigt dies das gewünschte Ergebnis.
Gruß
Firmus

AW: Zähnenwenns ohne Doppeltzählungen
27.11.2013 14:16:41
Kina
Habe jetzt herausgefunden, dass die Formel versagt, sobald ich an den Zellbereichen etwas verändere... Ich möchte gerne die Bereiche wie im vorhergehenden Post, was muss ich dann bei den Zeilen für einen Bereich auswählen und wie lautet die Regel, damit es in Zukunft ohne Hilfe klappt?
Lg Kina

Anzeige
AW: Zähnenwenns ohne Doppeltzählungen (? an wen?)
27.11.2013 15:26:38
Firmus
Hi Kina,
sprichst Du mich oder Christian an?
Gruß
Firmus

mit HÄUFIGKEIT() ...
27.11.2013 18:46:46
neopa
Hallo Kina,
... wenn Deine Produktnummern wirklich Zahlenwerte sind, dann einfach so: =SUMME(N(HÄUFIGKEIT(File!B4:B999*(File!C4:C999=A6);File!B4:B999)>0)) Gruß Werner
.. , - ...

Recht so, Werner, endl Schluss m.d.Zähnen! ;-)
27.11.2013 19:01:33
Luc:-?
Gruß Luc :-?

das Du das bemerkrst!!
27.11.2013 23:24:40
Christian
Hallo Luc,
so oft w Du Abkzn bntz, dcht ich Dr flt das nt auf...
MfG Christian

Das war ja eben KEINE, aber dafür ständig ...
28.11.2013 03:03:52
Luc:-?
…gedankenlos wiederholt, Christian;
da wundert eher, dass das bisher Keinem aufgefallen zu sein scheint bzw Keinen gestört hat.
Was Abkk betrifft, hatten wir vor Jahren mal 'ne Diskussion (evtl im nicht mehr existenten OffTopic-Forum) zu TextVerstümmelungen (ala Hajo), in der eine Studie zu TextVerstümmelungen bei NachrichtenÜbertragung (Fernschreiben &Co) angeführt wurde, die untersucht hatte, bis zu wieviel % Verstümmelung Texte noch verstanden wdn. Aber das hat natürlich mit besonders in der FachLiteratur regulär benutzten Abkk weniger zu tun. Da wir hier ein sog FachForum sind, bemühe ich mich, viell auch im Sinne der von St.Lem in seinen Vorworten (zu Büchern des 21.Jhdts ) postulierten MetaSprache, nicht so oft in Belletristik, in der Abkk verpönt sind, zu verfallen. ;-]
Gruß Luc :-?

Anzeige
um de Zeene unn de Ochen ...
28.11.2013 18:30:25
neopa
Hallo Luc,
... ist es bei mir schlecht bestellt. Ich hab den Schreibfehler auch nicht wirklich bemerkt. Sicherlich auch deshalb, weil ähnliche Schreibfehler von mir sicherlich auch häufig fabriziert werden :-( und ich in solchen Fällen auch Gnade bzw. wenigstens Nachsicht erhoffe.
Gruß Werner
.. , - ...

die "Teufligkeit" mit der Häufigkeit
28.11.2013 20:02:09
Josef
Hallo Werner und @
Die Funktion Häufigkeit ist nicht so einfach zu handhaben, wenn man diese an Bedingungen knüpft.
In diesem Fall muss man die Bedingung in eine Wenn-Abfrage packen. Andernfalls kann es durch die "produzierten" Nullen zu Ergebnissen kommen die um 1 zu hoch sind.
Die erweiterte Formel muss aber bei der Eingabe als Matrixformel abgeschlossen werden.
Gruss Sepp
file

 ABC
1 ProduktNrBundesland
2   
3   
4 11NWR
5 11NWR
6NWR11NWR
7317NWR
8210SH
9 11SH
10 12SH
11 10SH

Formeln der Tabelle
ZelleFormel
A7=SUMME(N(HÄUFIGKEIT(file!B4:B999*(file!C4:C999=A6); file!B4:B999)>0))
A8{=SUMME(N(HÄUFIGKEIT(WENN((file!C4:C999=A6); file!B4:B999); file!B4:B999)>0))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Anzeige
sapperlot .... obwohl ich das wusste, ...
29.11.2013 16:28:22
neopa
Hallo Sepp,
... hatte ich aber mal wieder nicht aufgepasst :-(
Ich danke Dir, dass aber Du wieder einmal gut aufgepasst hast. In solchen Fällen definiere ich _ normalerweise den Auswertungsbereich immer so groß, dass immer mindestens eine Leerzelle sich drin befindet und schreibe, wenn ich denn fit bin, =HÄUFIGKEIT(...)-1. Aber um sicherzugehen (dass wirklich Leerzellen vorhanden sind) ist natürlich WENN() die saubere Lösung oder was ich auch schon getan habe, ermittele dies, wenn keine {} gewünscht sind z.B. mit 

=SUMME(HÄUFIGKEIT(...))-(ANZAHLLEEREZELLEN(Bereich)>0)

Ich wünsche Dir nun noch einen schönen Abend sowie auch schönes 1. Adventswochenende.
Gruß Werner
.. , - ...

Anzeige
leider nein Werner...
29.11.2013 19:24:16
Josef
Hallo Werner
...die Sache ist noch teuflischer
Du kannst eben nicht einfach minus 1 rechnen, wenn Leerzellen da sind. Ich habe diese Variante auch schon öfters gesehen, aber sicher ist auch diese nicht. Wenn du mein Beispiel nimmst, und in B7 eine 9 hinschreibst, ist mit deiner Kurzformel schon wieder alles richtig. Wie die Daten vorliegen hat also auch noch einen Einfluss, ob richtig oder falsch gerechnet wird.
Ich wünsche auch dir ein schönes Wochenende
Gruss Sepp

...und dann gibt's ja auch immer noch die Sache...
29.11.2013 22:52:40
Luc:-?
…mit einem Wert plus, Jungs,
was ja uU auch noch Einfluss haben könnte…
Dito schöAdWE! Gruß Luc :-?

Anzeige
hmm ...
02.12.2013 08:47:16
neopa
Hallo Luc,
... nachdem mir Dank Sepp gerade ein Lichtlein aufgegangen war, nun Deine Aussage. Diese verstehe ich im Moment nicht wirklich. Aber es ist auch ja erst Montag Morgen, vielleicht ja später oder Du hilfst mir in die Potten?
Gruß Werner
.. , - ...

Ich meine, dass HÄUFIGKEIT grdsätzl ...
02.12.2013 15:24:01
Luc:-?
…immer einen Wert mehr ausgibt als die Klasse in Argument2 Werte hat, Werner,
was auch in der Hilfe steht. Falls mal Werte in Argument1 anfallen, die von der Klasse nicht gedeckt sind, wdn die dann dort gezählt, quasi als Sonstige. Das könnte ggf, je nach FmlAufbau, auch zu einem Fehler führen.
Gruß Luc :-?

Anzeige
dass ist mir bekannt, ...
02.12.2013 16:09:00
neopa
Hallo Luc,
... aber wie Du bereits geschrieben hast, dass kann, muss aber nicht zwangsläufig zu einem Fehler führen. Das Problem was Sepp offengelegt hat, war und ist dagegen für mich völlig neu.
Gruß Werner
.. , - ...

Das hängt aber mit dem Anderen zusammen, ...
03.12.2013 04:38:04
Luc:-?
…Werner;
heute Nachmittag/Abend mehr! Nur soviel, die „Kurzfassung“ ist kreuzgefährlich; da kann noch mehr schiefgehen, was bei dieser speziellen Anwendung auch schon mal nicht auffallen könnte!
Morrn, Luc :-?

So könnte man ja auch die 10 in den relevanten ...
03.12.2013 18:24:44
Luc:-?
…Bereich einbeziehen oder gleich SH (alles lt Josefs Bsp) wählen, Werner (& Josef);
was meinst du wohl, was dann passiert?! Bei Letzterem hat die 10 plötzlich 6 Treffer*, was ja nie und nimmer stimmen kann. Hier wdn die 0en hinzugezählt. Dadurch liefert die KurzFml hier auch ein richtiges Ergebnis, da ja nur einfach gezählt wird, was >0 ist. Auch ein ErsatzKonstrukt (in Kurzform), was im NWR-NormalFall (auch bei Hinzunahme 1er 10) fktioniert, liefert hier als einzige meiner TestFmln – ich habe hier mal mit auf das Bsp eingeschränktem Bereich getestet – ein falsches Ergebnis → =SUMME(N(HÄUFIGKEIT(B4:B11;B4:B11*(C4:C11=A6))>0))
Mit negativierten Argumenten (zumindest bei primär nur positiven Werten) wird auch der Zusammenhang mit dem „ResteSammelWert“ Sonstige erkennbar → alle 0en wdn so brav dort, also an letzter Position, gesammelt: =HÄUFIGKEIT(-B4:B11*(C4:C11=A8);-B4:B11)
(Das klappt dann allerdings auch negativiert mit =SUMME(N(HÄUFIGKEIT(-B4:B11;-B4:B11*(C4:C11=A6))>0)), nur liefert die HÄUFIGKEIT falsche Werte, aber an den richtigen Stellen und der MüllZähler ist =0!)
Auf diese Weise ließe sich der Wert ggf aus der Zählung ausschließen, diese Vorgehensweise bedürfte aber einer unnötig langen Fml; da ist es schon besser, mit einigen wenigen Zeichen mehr, das von vornherein zu verhindern (wie es Josef gezeigt hat).
* Möglicherweise ein alter Bug dieser Fkt, weil der Pgmierer weder redundante Klassen- noch zusätzliche 0en enthaltene HauptArgumente berücksichtigt hat bzw das Auftreten von 0en hierbei grdsätzl problembehaftet und nicht unter allen Umständen adäquat gelöst ist. Aber auch mit redundanzfreiem KlassenArgument landen die 0en bei 10. Nur bei negativierten Argumenten unter Sonstige.
Gruß Luc :-?

Anzeige
Übrigens wäre wohl mit AGGREGAT unter ...
03.12.2013 20:35:14
Luc:-?
…Ersetzen von FWerten auch eine Kurzform mit folgd Arg1 möglich, Werner:
B4:B11/(C4:C11=A6)
Meine neue, ähnlich, aber auf anderer Basis fktionierende UDF VectAr erlaubt das nämlich auch. Mit =MTRANS(VectAr(B4:B11/(C4:C11=A6);;"-frequency";;B4:B11)) ergibt sich nämlich der „saubere“ ErgebnisVektor {3;0;0;1;0;0;0;0;0} ({1;0;0;0;2;0;1;0;0} für SH). Den(/die) dürfte ein entsprd AGGREGAT-Konstrukt auch liefern.
Luc :-?

Du hast mir eben Adventsstimmung verschafft ...
02.12.2013 08:42:11
neopa
Hallo Sepp,
... ein Lichtlein ist mir aufgegangen ... und das einen Tag nach dem ersten Advent :-)
Das was Du festgestellt hast, ist völlig richtig und wirklich irgend wie auch "teuflisch". Ich hab nun bestimmt schon einige Male eine derartige Lösungsformelformel angeboten und noch nie hat jemand mich auf diese "Falle" aufmerksam gemacht.
Da muss ich wohl wieder auf das von mir ungeliebte WENN() ausweichen. jedenfalls ist mir auf die Schnelle keine vernünftige Alternative eingefallen.
Ich danke Dir für Deine Hinweise und wünsche Dir noch eine schöne 1 Adventswoche.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige