Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 12:46:38
Nutzereingaben Speichern und Laden
26.04.2024 11:49:48
VAB und Bedingte Formatierung nimmt die Farbe im Makro nicht
26.04.2024 11:38:46
Acrobat PDF Formularfelder mit Excel ausfüllen
26.04.2024 11:23:04
zählen wenn - aber nur die selektierten Zeilen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1728to1732
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
1728to1732
1728to1732
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Doppelte zählen ohne Hilfsspalte

Doppelte zählen ohne Hilfsspalte
21.12.2019 14:11:58
Klaus
Hallo Leute,
gegeben seien zwei Tabellen
Tabelle1
Hund - Haustier
Katze - Haustier
Esel - Nutztier
Pferd - Nutztier
Igel - Wildtier
Tabelle2
Hund - Haustier
Spinne - Anarchroide
Esel - Nutztier
Reh - Wildtier
Schwein - Nutztier
Ich möchte wissen: Wie viele Nutztiere stehen in beiden Tabellen? Im Beispiel wäre die Antwort "1", weil nur der Esel in beiden Tabellen vorkommt.
Mit VBA kann ich es. Mit Hilfsspalten auch, easy. Aber bekommt jemand eine 1-Formel-Lösung hin?
LG,
Klaus M.

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Doppelte zählen ohne Hilfsspalte
21.12.2019 14:13:45
Hajo_Zi
Hallo Klaus,
sehe ich es falsch oder ist Hund auch in beiden Tabellen?

Richtig erkannt Hajo
21.12.2019 14:31:40
Daniel
Hund steht in beiden Tabellen.
Nur ist Hund ein Haus- und kein Nutztier und nur diese sind gesucht.
Gruß Danier
AW: Richtig erkannt Hajo
22.12.2019 12:26:01
Klaus
Genau, Daniel hat es richtig erkannt!
Der Hund steht in beiden Tabellen, soll aber nicht gezählt werden, da er kein Nutztier ist.
Das Schwein ist ein Nutzier, soll aber nicht gezählt werden da es nur in einer Tabelle steht.
Nur der Esel erfüllt beide Bedingungen - Nutztier und doppeltes vorkommen - und soll daher einmal gezählt werden.
Die echten Tabellen haben - natürlich - mehrere hundert bis mehrere tausend Einträge. Ich importiere sie per VBA aus zwei verschiedenen Quellen (eine aus einer Lotus-Notes Datenbank, eine aus einer Access-Datenbank) die zu allem übel natürlich völlig unterschiedlich formatiert und sortiert sind. Ich finde es nicht elegant, importierte Daten nachträglich zu verändern, darum möchte ich auf Hilfsspalten verzichten. Zum zählen nehme ich bisher eine VBA-Lösung, zwecks besserer Übersicht hätte ich aber das VBA-Script gern auf reines importieren beschränkt und die Auswertung in einer Excelformel.
LG und schönen 4ten Advent,
Klaus M.
Anzeige
voll reingefallen!
22.12.2019 12:46:31
Klaus
Hallo Günther!
Jetzt bin ich voll drauf reingefallen. Hab aus Neugierde (man will ja doch wissen, was PQ ist) die Datei geöffnet, und dann ganz lange nach "Daten - Abrufen und transformieren gesucht ... um dann festzustellen, privat bin ich ja noch auf Excel 2007.
Danke, schaue ich mir Montag am Firmenrechner mit Excel 2016 bestimmt nochmal an!
Immerhin warst du heute der einzige im Thread, der mich dazu gebraucht hat auch Excel zu öffnen :-)
LG,
Klaus M.
Anzeige
AW: Doppelte zählen ohne Hilfsspalte
21.12.2019 16:48:50
Günther
Moin Klaus,
das wäre gewiss eine gute Gelegenheit für dich, sich ein wenig mit Power Query (Daten | Abrufen und transformieren) auseinander zu setzen... :-)
Gruß
Günther
immer wieder Power Query .... :-)
22.12.2019 12:31:47
Klaus
Ach Günter,
du hast ja Recht. Ich komme mir auch uralt vor - immer wenn ich Power Query höre, muss ich den Reflex unterdrücken zu sagen:
"FRÜHER, zu meiner Zeit, haben wir das noch mit Formel lösen müssen!"
um mich dann wieder meinem Karteikasten und meinem Abakus zuzuwenden.
Jüngst habe ich mich aber gegen PQ und für SQL entschieden - dies kann gern kontrovers diskutiert werden, aber bitte nicht in diesem Thread :-) Die Frage ist sicherlich auch eine Musterfrage für eine SQL-Bedingung .... Pseudo-SQL Select * from Tabelle1 where Tier is in Tabelle2 and Tier in Tabelle2 is Nutztier .... aber SQL und Access fragen versuche ich hier bei herber.de zu vermeiden, da Themenfremd.
LG und schönen vierten Advent,
Klaus M.
Anzeige
AW: immer wieder Power Query .... :-)
22.12.2019 12:44:23
Günther
Warum sollte SQL denn nicht bei herber.de hineinpassen? SQL ist sehr schön in VBA eingegliedert und passt insofern sehr gut. Ansonsten ist SQL gewiss eine hervorragende Lösung, wenn du IT-affin bist und programmieren kannst. Und das funktioniert sogar unter Excel 2003 ohne Murren (mit der gleichen, feinen Ironie gemeint wie dein erster Absatz ;-) )
Gruß
Günther
AW: Doppelte zählen ohne Hilfsspalte
21.12.2019 14:37:13
Daniel
Mal vorausgesetzt, die Listen sind eindeutig und konsistent:
=Summenprodukt((Tabelle1!B1:B5="Nutztier")*ZählenWenn(Tabelle2!A1:A5;Tabelle1!A1:A5))
Ggf als Matrixformel einzugeben.
Ich habe aber nicht getestet.
Gruß Daniel
Anzeige
AW: Doppelte zählen ohne Hilfsspalte
22.12.2019 12:37:06
Klaus
Ja das sieht gut aus, Daniel.
Die Tabellen sind insofern konsistent, dass der Esel immer ein Nutztier und die Spinne niemals ein Haustier sein wird. Allerdings sind die Tabellen unterschiedlich lang. Müsste trotzdem gehen, schlimmstenfalls vergleicht Summenprodukt halt ein paar leere Zellen.
Ich habe heute am Sonntag keinen Bock Excel zu öffnen, aber ich versuche es am Montag im Büro an den richtigen Daten. Bis dahin auch dir einen schönen 4ten Advent, nochmal!
LG,
Klaus M.
AW: Doppelte zählen ohne Hilfsspalte
23.12.2019 12:29:45
Daniel
Hi
Die eigentliche Matrix ist nur Tabelle1, Spalte A und B.
Diese Zellbereiche müssen gleich groß sein.
Tabelle2 ist davon unabhängig und wird eigenständig durch das ZählenWenn ausgewertet.
Daher können die Tabellen unterschiedlich lang sein.
Gruß Daniel
Anzeige
kleine Arrayformel
21.12.2019 14:45:54
WF
Hi,
{=ANZAHL(VERGLEICH(WENN(B1:B9="Nutztier";A1:A9);Tabelle2!A1:A9;0))}
WF
AW: kleine Arrayformel
22.12.2019 12:41:47
Klaus
Schaut gut aus! Das ist die gleiche Lösung wie Daniel vorgeschlagen hat, nur ohne Summenprodukt davor.
Ebenfalls: Probiere ich Montag an den Echt-Daten aus und melde mich dann.
Bis dahin: Schönen 4ten Advent!
LG,
Klaus M.
Stimmt nicht, ...
23.12.2019 02:48:30
Luc:-?
…Klaus;
WFs Lösung folgt einem komplett anderen Ansatz (der erweiterbar ist, s.u.).
Morhn, Luc :-?
Auch WFs Fml setzt wie Daniels konsistente ...
21.12.2019 23:57:11
Luc:-?
…Tabellen voraus, Matthias;
ist diese Konsistenz (in Form gleicher Kategorisierung) per sé nicht unbedingt gewährleistet, könnte durch eine Erweiterung von WFs Fml das (im Vgl beider Ergebnisse) sichtbar wdn (Zeile 1 für Titel reserviert; in E1 steht der Vglsbegriff, hier Nutztiere):
{=ANZAHL(VERGLEICH(WENN(B2:B7=E1;A2:A7;"");WENN(Tabelle2!B2:B6=E1;Tabelle2!A2:A6);0))}
Es wäre aber auch ein anderer Ansatz als die bisher gezeigten möglich — mit UDFs, aber ebenfalls per singularer MatrixFml, wobei nur die jeweils relevanten Werte verwendet wdn und deren Schnittmenge gebildet wird:
{=ANZAHL2(DataSet(NoErrRange(A2:A7;;B2:B7=E1); NoErrRange(Tabelle2!A2:A6;;Tabelle2!B2:B6=E1);1)) }
Übrigens, Spinnen haben idR nichts mit Anarchisten zu tun, weshalb sie auch eher Arachnoiden wären. ;-]
Gruß, Luc :-?
„Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder.“ Stapps ironisches Paradoxon
Nichtsdestotrotz Durchblick verbessern mit …
Anzeige
Ersetze Matthias durch Klaus! owT
21.12.2019 23:59:36
Luc:-?
:-?
AW: Ersetze Matthias durch Klaus! owT
22.12.2019 12:40:24
Klaus
Hallo Luc,
schön mal wieder von dir zu lesen nach all den Jahren!
Die Tabellen sind konsistent. Deine UDF habe ich einmal angeschaut und gleich wieder zugemacht - viel zu aufwendig für diese Fragestellung. Und ... Wenn ich schon eine UDF benutze, kann ich es ja gleich in VBA lösen wie bisher!
Aber danke für den äußerst wichtigen Hinweis auf den Rechtschreibfehler :-)
Auch dir einen schönen 4ten Advent,
LG,
Klaus M.

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige