Microsoft Excel

Herbers Excel/VBA-Archiv

Vergleich zweier Bereiche mit Excel, kein VBA!


Betrifft: Vergleich zweier Bereiche mit Excel, kein VBA! von: Ingo
Geschrieben am: 17.11.2017 14:22:03

Hallo Excel-Experten,

ich versuche gerade mit Excel Bordmitteln eine Abfrage zu erstellen, die mir 2 Bereiche (auf unterschiedlichen Tabellenblättern) miteinander vergleicht und immer wenn 2 Werte in den Bereichen (an x-beliebiger Position) überein stimmen diese auf einem separaten Blatt ausgibt. Als Bereich meine ich jetzt nicht das gesamte Tabellenblatt, sondern jeweils nur eine definierte Spalte (Kundennummer). Ich kann/darf dafür leider kein VBA verwenden!
Bsp.
3 Tabellenblätter:
1) Tab_Vergleich
2) Tab_Abteilung A mit Kundendaten (u. a. Kundennr.)
3) Tab_Abteilung B mit Kundendaten (u. a. Kundennr.)

Beide Abteilungen (A und B) arbeiten u. U. mit den gleichen Kunden zusammen. Die Kunden werden über eine eindeutige Kundennummer identifiziert, die für alle Abteilungen gleich ist.
Um das zu konsolidieren möchte ich auf einem weiteren Tabellenblatt (Tab_Vergleich) nur die Kunden (Kundennr.) ausgegeben haben, die in beiden Abteilungen vorkommen.
Ist dies grundsätzlich mit Excel Bordmitteln (ggf. auch mit MS Query) möglich? Ich habe bereits verschiedene Möglichkeiten versucht (WENN, IDENTISCH, VERGLEICH), die aber alle daran scheitern, dass entweder ein bestimmter Vergleichswert vorhanden sein muss, keine Bereiche als Input angegeben werden dürfen oder das Ergebnis (im Falle Vergleich) auf einen Index verweist, der mir als Ergebnis auch nicht weiter hilft. Die Suche hier im Forum ergab verschiedene Lösungsansätze, die aber alle VBA basiert sind. Wie bereits gesagt, möchte ich dies OHNE VBA lösen.
Ich habe leider noch nie mit MS Query gearbeitet. Daher im Falle einer Lösung mit Query bitte auch den Lösungsweg für Dummies aufzeigen.

VG und besten Dank
Ingo

  

Betrifft: AW: Vergleich zweier Bereiche mit Excel, kein VBA! von: SF
Geschrieben am: 17.11.2017 14:23:50

Hola,

http://www.excelformeln.de/formeln.html?welcher=83

Gruß,
steve1da


  

Betrifft: AW: nachgefragt ... von: ... neopa C
Geschrieben am: 17.11.2017 14:31:26

Hallo Ingo,

... wie viele Datensätze max (ca.) gibt es in den beiden Tabellenblättern und sind die Kundennummern alphanumerisch oder Zahlenwerte. Wie auch immer es gibt verschiedene Möglichkeiten formeltechnisch Dein Wunschergebnis einfacher zu erzielen als in dem von SF aufgezeigten Link.

Gruß Werner
.. , - ...


  

Betrifft: AW: nachgefragt ... von: Ingo
Geschrieben am: 17.11.2017 14:35:36

Hallo Werner,
vielen Dank für die schnelle Reaktion.
Insgesamt werden in einer Spalte mehrere 100 Kundennummern in numerischer Form stehen.
VG
Ingo


  

Betrifft: AW: weiter nachgefragt ... von: ... neopa C
Geschrieben am: 17.11.2017 14:46:42

Hallo nochmal,

... in welchen Spalten stehen die abzugleichenden Nummern in den beiden Tabellenblättern und die wie benannt sind?

Gruß Werner
.. , - ...


  

Betrifft: AW: weiter nachgefragt ... von: Ingo
Geschrieben am: 17.11.2017 14:51:33

Hallo Werner,
in beiden Fällen ist die Kundennummer gleich als KdNr bezeichnet. Im Tabellenblatt "Abteilung A" steht sie in Spalte "C" und im Tabellenblatt "Abteilung B" steht sie in Spalte "F".

VG
Ingo


  

Betrifft: AW: dann z.B. folgende Möglichkeit ... von: ... neopa C
Geschrieben am: 17.11.2017 15:25:15

Hallo Ingo,

... mit lediglich einer Formel, die weit genug mach unten ziehend zu kopieren ist.

Diese Formel setzt momentan voraus, dass Deine Datentabellen als "intelligente" Tabellen formatiert sind. Wenn das noch nicht der Fall ist, solltest Du das noch tun. Dazu nutze die Funktion "Als Tabelle formatieren".

Dies hat den Vorteil, dass Du keine Änderung an der Ergebnisformel vornehmen musst, wenn in den Ausgangstabellen zu einem späteren Zeitpunkt weitere Datensätze hinzukommen.

Die Formel listet je Kundennummer auch nur einmal auf, falls sie mehrmals in einer Ausgangstabelle gelistet sein sollte und in der anderen 1x oder mehrmals vorhanden sein sollte. Wenn Duplikate (in einer Datentabelle) nicht vorkommen können, könnte ich die Formel noch etwas vereinfachen.

Wenn, wie Du schreibst, keine tausende Datensätze auszuwerten sind, ist nachfolgende Formellösung eine Formel-Lösungsariante, die zwar für Dich sicherlich nicht leicht nachvollziehbare ist. Du solltest diese deshalb auch erst mal mit ein paar Beispieldaten nachstellen, wo Du deren Ergebnis leicht selbst visuell überprüfen kannst.

indentisch

 B
1KdNr
21211
31237
41239
51242
62001
7 

Formeln der Tabelle
ZelleFormel
B2=WENNFEHLER(AGGREGAT(15;6;Tabellle1[KdNr]/(ZÄHLENWENN(Tabelle2[KdNr];Tabellle1[KdNr])>0)/(ZÄHLENWENN(B$1:B1;Tabellle1[KdNr])=0); 1); "")


Abteilung A

 C
1KdNr
21234
31242
41237
52001
61240
71241
81236
91239
101240
111211
121234
13 


Abteilung B

 F
1KdNr
22001
31237
41211
52001
61239
71242
81211
9 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: dann z.B. folgende Möglichkeit ... von: Ingo
Geschrieben am: 17.11.2017 16:51:16

Wow Werner,
das sieht bei Dir echt super aus. Vielen Dank schon mal dafür!
Ich habe das mal versucht, bei mir umzusetzen, aber Excel gibt mir einen Fehler aus: "Mit dieser Formel besteht ein Problem". Die Formel (oben in B2) steht auf einem separaten Tabellenblatt. Richtig? Das wäre bei mir das Tabellenblatt Vergleich.
Zu meinem Verständnis: Du hattest mich gefragt, in welcher Spalte die Kundennummer stehen. Das finde ich hier bei Dir nicht wieder (zumindest nicht auf den ersten Blick). Wofür war das wichtig? Könntest Du noch ein, zwei Erläuterungen zu den verwendeten Funktionen geben? Das wäre echt super!
Muss mir das noch einmal in aller Ruhe anschauen. Bin gleich weg zum Termin, schaue es mir am WE genauer an.
VG und ich gebe auf jeden Fall noch eine Rückmeldung.
Ingo


  

Betrifft: AW: mehr zu intelligenten Tabellen ... von: ... neopa C
Geschrieben am: 17.11.2017 17:18:59

Hallo Ingo,

... dazu sieh z.B. mal hier: https://www.youtube.com/watch?v=UobweK5TDA4

Danach ist Dir sicherlich verständlicher, warum in meiner Formel kein absoluter Spaltenbezug vorhanden ist und warum bei Dir "Mit dieser Formel besteht ein Problem" erscheint. Ich schrieb ja, das intelligente Tabellen Voraussetzung für diese ist.

Die intelligente Tabelle "Tabelle1" war bei mir im Beispiel wie folgt definiert:
='Abteilung A'!$C$2:$C$12 und analog Tabelle2 ='Abteilung B'!$F$2:$F$8

Wie das Ergebnistabellenblatt benannt ist egal.

Gruß Werner
.. , - ...


  

Betrifft: Problem gelöst! Vielen Dank an alle Unterstützer von: Ingo
Geschrieben am: 20.11.2017 08:05:40

Perfekt Werner!
Du hast recht. Den Hinweis mit der intelligenten Tabelle hatte ich nicht so recht kapiert. Deine Lösung ist genau das, was ich benötige - und ich habe auch nun die Lösung verstanden!
Ganz herzlichen Dank dafür und Dir einen angenehmen Start in die Woche :-)
VG aus Berlin
Ingo


  

Betrifft: AW: freut mich; auch Dir einen guten Start owT von: ... neopa C
Geschrieben am: 20.11.2017 13:10:45

Gruß Werner
.. , - ...


  

Betrifft: AW: Vergleich zweier Bereiche mit Excel, kein VBA! von: Ingo
Geschrieben am: 17.11.2017 14:54:03

Hallo Steve1da,
vielen Dank für Deinen Link.
Ich versuche mich da gerade mal durch zu denken, konnte aber noch keinen Erfolg in der praktischen Umsetzung verzeichnen. Bin dran und melde mich bei Erfolg (auch bei Misserfolg zwecks weiteren Support ;-) )
VG
Ingo


  

Betrifft: AW: Vergleich zweier Bereiche mit Excel, kein VBA! von: Ingo
Geschrieben am: 17.11.2017 15:14:21

Sorry steve1da, aber ich komme da nicht wirklich weiter. Die erste Zeile klappt mit der Formel. Bei allen anderen Zeilen (Formel runter kopiert wie beschrieben) bringt er mir einen "#Zahl!"-Fehler. An den Zellen ist aber nichts anders. Ich habe ein blüten weißes neues Excel Blatt geöffnet.
Hast Du eine Idee?

VG
Ingo


  

Betrifft: AW: Vergleich zweier Bereiche mit Excel, kein VBA! von: SF
Geschrieben am: 17.11.2017 15:41:28

Hola,

ich kann nur raten: du hast die Formel nicht mit Strg-Shift-Enter abgeschlossen.
Ansonsten wäre eine Beispieldatei mit der Formel hilfreich.

Gruß,
steve1da


  

Betrifft: AW: Vergleich zweier Bereiche mit Excel, kein VBA! von: Ingo
Geschrieben am: 17.11.2017 16:54:28

Hi Steve1da,
also definitiv habe ich die Formel nicht mit dem o. g. Befehl abgeschlossen. Ich schau mir das am WE mal genauer an. Muss jetzt leider weg zum Termin. Gebe auf jeden Fall noch Feedback.

Vielen Dank schon mal für Deine Unterstützung!

VG
Ingo


  

Betrifft: AW: Vergleich zweier Bereiche mit Excel, kein VBA! von: Günther Mumme
Geschrieben am: 17.11.2017 20:44:34

Also, mit Power Query aka Daten | Abrufen und transformieren sollte das ohne eine Formel möglich sein (genauer gesagt: ich bin davon überzeugt). Mit einer Muster-Datei liefere ich dir die Lösung (falls möglich).


Beiträge aus den Excel-Beispielen zum Thema "Vergleich zweier Bereiche mit Excel, kein VBA!"