Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1624to1628
1624to1628
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Prüfen auf mehrere Kombinationen aus 2 Spalten

Forumthread: Prüfen auf mehrere Kombinationen aus 2 Spalten

Prüfen auf mehrere Kombinationen aus 2 Spalten
24.05.2018 19:42:07
Kisska
Hallo zusammen,
ich stehe auf dem Schlauch und brauche eure Hilfe.
Ausgangssituation:
In einer Tabelle habe ich der Spalte A Namen stehen und in der Spalte B die Nummern dazu.
Name---Nummer
A---111
B---222
B---222
C---333
C---333
C---444
Ziel:
1 zu 1 Beziehungen (einmal vorkommende Kombination) zwischen Name (Spalte A) und Nummer (Spalte B) findne.
Mit Pivot ist das Ergebnis:
A---111
B---222
C---333
C---444
Ich wünsche mir, dass Pivot mir nur 1 zu 1 Beziehungen anzeigt, d.h. :
A---111
B---222
Gerne würde ich bereits in meiner Tabelle eine Hilfsspalte C anlegen, in der nur 0 oder 1 ausgewiesen wird.
1 = Kombination kommt nur einmal vor (egal ob doppelt oder einfach)
0 = es gibt mehr als eine Kombination (wie im Beispiel: C---333 und C---4)
Ich habe mit der Funktion SUMMENPRODUKT probiert, aber es kommt nur Unsinn raus.
Über eure Tipps würde ich mich sehr freuen!
VG
Kisska
Anzeige

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Solisten von Solisten
24.05.2018 22:02:19
Solisten
Hi,
3 Schritte
in C1 steht:
=A1&B1
in C2 steht die Arrayformel:
{=WENNFEHLER(INDEX(A$1:A$99&B$1:B$99;VERGLEICH(1;(ZÄHLENWENN(C$1:C1;A$1:A$99&B$1:B$99)=0) *(A$1:A$99"");0));"") }
runterkopieren
in D1 steht:
=LINKS(C1)
runterkopieren
in E1 steht die Arrayformel:
{=WENNFEHLER(INDEX(C:C;KKLEINSTE(WENN(ZÄHLENWENN(D$1:D$99;D$1:D$99)=1;ZEILE(X$1:X$99));ZEILE(X1))); "") }
runterkopieren
Salut WF
Anzeige
ZEILE(X1) ?
24.05.2018 23:20:58
Kisska
Hallo WS,
danke zunächst für deinen Lösungsvorschlag!
Was bedeutet ZEILE(X$1:X$99));ZEILE(X1))); "")? Ich bekomme in der Spalte E damit nichts angezeigt.
Hier meine Datei mit der Aufgabe und deiner Lösung:
https://www.herber.de/bbs/user/121806.xlsx
(Hinweis: Die Datei ist mit Excel 2013 erstellt, die Lösung wird jedoch für Excel 2010 benötigt)
VG
Kisska
Anzeige
ZEILE(X1) ist nur ein Zähler 1 - 2 - 3 -
25.05.2018 07:33:00
WF
Hi,
ZEILE(X$1:X$99) sind die Zahlen 1,2,3,4,5,6, ...98,99
ZEILE(X1) runterkopiert wird ZEILE(X1) ZEILE(X1) ... also 1 2 3 ...
statt X kannst Du jeden anderen Buchstaben nehmen: ZEILE(A1) oder ZEILE(K1) oder ...
Diese "intelligenten" Tabellen benutze ich nicht - kenne mich also nicht aus.
Schreibe in E2 die normale Arrayformel
{=WENNFEHLER(INDEX(C:C;KKLEINSTE(WENN(ZÄHLENWENN(D$1:D$99;D$1:D$99)=1;ZEILE(X$1:X$99));ZEILE(X1))); "") }
und Du hast Dein Ergebnis
(Das kann hier sicher jemand anpassen)
oben schreibst Du "die Lösung wird jedoch für Excel 2010 benötigt"
Meines Wissens gab's diese Tabellenart da noch nicht ?
WF
Anzeige
ZEILE(X1) runterkopiert wird natürlich
25.05.2018 07:39:54
WF
ZEILE(X2) ZEILE(X3) ZEILE(X4)... also 2 3 4 ...
Intelligent Tabellen ...
25.05.2018 09:05:52
Matthias
Hallo WF
Meines Wissens gab's diese Tabellenart da noch nicht ?
Zur Info: Intelligente Tabellen gab es schon unter XL2007
Gruß Matthias
Danke! => Hilfsspalten reduzieren?
25.05.2018 10:51:27
Kisska
Hallo WF,
danke für die Erklärung!
Ich habe deine Formel angepasst und es scheint zu funktionieren :-)
Den Ausdruck ZEILE(X$1:X$99));ZEILE(X1))) habe ich ersetzt durch 


ZEILE(X1));ZEILE(X1)))
. Ich weißt nicht, ob ich es machen durfte, aber zumindest bleibt das Ergebnis gleich.
Hier mein Ergebnis:
https://www.herber.de/bbs/user/121817.xlsx
In der Spalte F habe ich ausgegeben, ob es eine 1:1-Zuordnung gibt => so wie in der Aufgabestellung gewünscht.
An dieser Stelle herzlichen Dank für die prompte Hilfe! :-))
Für die gewünschte Auswertung (Spalte F) wurden ja drei Hilfsspalten benötigt. Ist es möglich, nur zwei oder sogar nur eine Hilfsspalte zu verwenden?
VG
Kisska
Anzeige
stimmt aber nicht !
25.05.2018 11:30:59
WF
Hi,
als Ergebnis wolltest Du in Spalte E:
A111 und B222
So hatte ich das auch gefummelt.
Bei Dir taucht jetzt zusätzlich C333 auf obwohl C*** in Spalte C mehrfach vorkommt.
WF
AW: stimmt aber nicht !
25.05.2018 16:08:53
Kisska
Hey WF,
ja, ich wollte tatsächlich das in der PIVOT-Auswertung nur A111 und B222 ausgewertet werden und die n:1-Zuordnungen im Filter abgewählt werden können.
Dazu hatte ich als Idee gehabt in der Originaltabelle eine C-Spalte einzubauen, die durch eine geschickte Formel entweder eine 1 oder 0 ausgibt (1 = das betrachtete Paar aus Spalte A und B ergibt eine 1:1-Zuordnung; 0 = es liegt eine n:1-Zuordnung).
In der Pivot-Tabelle hätte ich dann im Filter die "0" abgewählt und hätte somit nur 1:1-Beziehungen.
Deshalb habe ich bei deiner Lösung noch eine Spalte F hinzugefügt.
Aber jetzt merke ich, die jetzige Spalte E brauche ich ja gar nicht.
Dann bleibt nur die Frage, kann man die Spalten C und D zusammenführen, sodass es nur die Hilfsspalte C bleibt? Bei C2 kann man ja die Funktion Links ja direkt einbauen, aber bei den anderen Zellen klappt es nicht, wenn ich als Außen-Formel für deine Formel Links nehme :/
Dass mit C333 unter der Spalte C - ich dachte, dass es von dir so beabsichtigt war - eine Auflistung aller möglichen Kombinationen untereinander, dann in der Spalte daneben die Reduzierung nach "Namen" und zwar so häufig, wie es verschiedene Kombinationen gibt.
Jetzt habe ich so viel Text geschrieben, ich hoffe, ich habe mich halbwegs verständlich ausgedrückt. Danke für deine wertvolle Zeit, die du in mich investierst!
VG
Kisska
Anzeige
Hilfsspalten reduzieren sicher möglich aber...
25.05.2018 11:37:38
Josef
Hallo Kisska
....vorher sollte noch folgendes geklärt sein.
Was ist mit C, da ergibt deine Auswertung eins.
Hier gibt es aber nach meinem Verständnis mehrere Zuordnungen. Müsste dort nicht Null stehen?
Gruss Sepp
nein alles gut
25.05.2018 16:13:46
Kisska
Hallo Sepp,
danke für deinen Beitrag.
Meine Auswertung in der Spalte F bezieht sich auf die Spalte A - A2, A3 und A4 haben eine 1 erhalten, der Rest eine 0. Die Spalte C dient nur als Hilfsspalte - zumindest mit dem Lösungsvorschlag von WF.
Aber vielleicht gibt es einfachere Lösungen, um zu der Spalte F zu kommen...
VG
Kisska
Anzeige
In diesem Fall ganz ohne Hilfsspalte
25.05.2018 17:39:43
Josef
Hallo Kisska
In C2:
=(SUMMENPRODUKT(([Name]=[@Name])*([Nummer]=[@Nummer]))=ZÄHLENWENN([Name];[@Name]))*1

Gruss Sepp
PERFEKT! DANKE!
25.05.2018 18:29:43
Kisska
Wow, Sepp! Du bist klasse! Vielen vielen für diese elegante Lösung!
You made my day ! :))
Schönes Wochenende!
Anzeige
AW: ohne Pivot, mit nur 1ner Formel z. Ergebn. ...
25.05.2018 20:23:05
neopa
Hallo Kiska,
... und das ganze auch ohne {}-Formel.
Die Daten können in der "Als Tabelle formatierten" Liste (in meinem Beispiel: A2:C17) auch unsortiert sein und Leerzeilen beinhalten. Die Formel in C2 kopiert sich automatisch nach unten und passt sich ebenso jeglichen Datensatzänderungen an.
 ABC
1NameNrErgebnis
2A111B222
3B222D111
4B222E111
5C333 
6C333 
7C444 
8B222 
9A222 
10D111 
11B222 
12   
13D111 
14E111 
15A222 
16D111 
17C333 

Formeln der Tabelle
ZelleFormel
C2=WENNFEHLER(INDEX([Name]&[Nr];AGGREGAT(15;6;(ZEILE([Name])-1)/(ZÄHLENWENN([Name];[Name])=ZÄHLENWENNS([Name];[Name];[Nr];[Nr]))/(VERGLEICH([Name];[Name];0)+1=ZEILE([Name])); ZEILE(A1))); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
Danke!
26.05.2018 13:27:54
Kisska
Hallo Werner,
herzlichen Dank für diese tolle Lösung!
Die für mich perfekte Lösung bleibt die von Sepp. Ich habe mich wohl in meinem ersten Post unglücklich ausgedrückt, indem ich "Kombination" statt "Zuordnung" gesagt habe.
Deine Lösung werde ich aber definitiv noch zukünftig gebrauchen :-)
An dieser Stelle nochmal besten Dank an euch - WF, Sepp und Werner!
Anzeige
AW: bitte, gerne doch und ...
27.05.2018 11:02:43
neopa
Hallo Kiska,
... ich hatte Deine spätere Präzierung Deiner Zielstellung schon gelesen und verstanden. Dafür ist die Lösung von Sepp natürlich genau die von Dir angestrebte.
Ich wollte hier nur noch ergänzend eine alternative "einfachere" Formellösung zu dem zuerst hier im thread eingestellten "3 Schritte-Lösungsansatz" aufzeigen.
Gruß Werner
.. , - ...
Anzeige
Ohne Tabellenformatierung geht's auch so, ...
26.05.2018 17:26:16
Luc:-?
…Werner (&Kisska):
 ABCDEFGHIJ
20
NameNrMehrfach alle einmaligen Kombis nur 1. einmalige Kombis A1110 A111 A111 C3331       B2220 B222 B222 C4442       B2220 B222    C3331       A21:C26 - BF-Regel1: =$C21=0E21:F26 - BF-Regel1: nur eindeutige WerteC21:C26: {=SUMME(INDEX((INDEX(A21:A26;ZEILE(1:6))=A21:A26)-(INDEX(A21:A26&B21:B26;ZEILE(1:6))=A21:A26&B21:B26);;;1^ZEILE(1:6)))}E21:F26: {=WENN(SUMME(INDEX((INDEX(A21:A26;ZEILE(1:6))=A21:A26)-(INDEX(A21:A26&B21:B26;ZEILE(1:6))=A21:A26&B21:B26);;;1^ZEILE(1:6)))=0;INDEX((A21:A26;B21:B26);;;SPALTE(A:B));"")}H21:I21[;H22:I26]: {=WENN(UND(SUMME((A21=A$21:A$26)-(A21&B21=A$21:A$26&B$21:B$26))=0;ISTFEHLER(VERGLEICH(A21&B21;H$20:H20&I$20:I20;0)));INDEX((A21:A26;B21:B26);;;SPALTE(A:B));"")}
21
22
23
24
25
26
27
28
29
30
Wie Du sicher erkennst, verwendet die SummenBildung per pluraler MatrixFml das gleiche Prinzip wie ich es in der Tensor-Diskussion vorgestellt hatte. Außerdem ging ich davon aus, dass die beiden Spalten nicht zu nur einer mit KombiWert zusammengefügt wdn sollten.
Gruß & schöWE, Luc :-?
Anzeige
AW: allerdings ...
27.05.2018 10:52:18
neopa
Hallo Luc,
... würde wohl außer Dir, wohl kaum keiner einer eine derartige Lösung anstreben.
Für auszuwertende Daten ohne Tabellenformatierung ist eine Ergebnisdatenlisting auch ohne Leerzellen erzeugbar und dies sowohl mit singulärer Matrixformel als auch mit pluraler Matrixformel.
Mit singuläre Matrixformel in H21 z.B. so:
{=WENNFEHLER(INDEX(A:A;KKLEINSTE(WENN((ZÄHLENWENN(A$21:A$99;A$21:A$99)-
ZÄHLENWENNS(A$21:A$99;A$21:A$99;B$21:B$99;B$21:B$99)*VERGLEICH(A$21:A$99&"";A$21:A$99&"";0)=
ZEILE(A$21:A$99)-ZEILE(A$20))=0)*(A$21:A$99"");ZEILE(A$21:A$99));ZEILE()-ZEILE(A$20)));"")}
Analog mit pluraler Matrixformel in H21:H### mit geringügiger Erweiterung am Formelende so:
{=WENNFEHLER(INDEX(A:A;KKLEINSTE(WENN((ZÄHLENWENN(A$21:A$99;A$21:A$99)-ZÄHLENWENNS(A$21:A$99; A$21:A$99;
B$21:B$99;B$21:B$99)*(VERGLEICH(A$21:A$99&"";A$21:A$99&"";0)=ZEILE(A$21:A$99)-ZEILE(A$20))=0)*
(A$21:A$99"");ZEILE(A$21:A$99));ZEILE(A21:A99)-ZEILE($A$20)));"")}
Die zugeordente Nr der Daten, kann man einfach mit folgender Formel
=WENN(H21="";"";SVERWEIS(H21;A:B;2;0)) ermitteln.
Gruß Werner
.. , - ...
Anzeige
Nun ist es allerdings so, ...
27.05.2018 23:43:12
Luc:-?
…Werner,
dass, wenn ich Liste lese, ich sofort an Rohdaten, ggf aus DB-Import, denke. Eine Tabelle (erst recht eine sog „intelligente“) impliziert für mich stets ein mehr oder weniger komplex auswertendes Endprodukt, was aber hier nicht der Fall sein dürfte. Und auch das Ergebnis wird ein Arbeits(hilfs)mittel sein.
Meine 1.Lösung war das hier Vorgestellte auch nicht — mit UDFs kam ich auch auf eine Lösung ohne Leerzeilen. Aber Josefs ist ohnehin kürzer und hier wollte ich mal UDF-frei bleiben und den Meisten unbekannte Xl-Möglichkeiten einsetzen (Du erkennst sicher diese Absicht ;-]) → hier die Bildung einer Schar von Teilsummen per pluraler MxFml. Mit dieser Hilfsspalte könnte sogar Bedingt­Formatierung zwecks Kennzeichnung im Original eingesetzt wdn.
Natürlich wäre auf der Basis der Hilfsspalte C auch eine sehr kurze, zusammenfassende Fml möglich, wie bspw mit UDFs diese plurale MxFml: {=DataSet(NoErrRange(A21:A26;;C21:C26=0);NoErrRange(B21:B26;;C21:C26=0);;1)}
Die UDF NoErrRange erzeugt hier diskontinuierliche Bereiche und die UDF DataSet kann sie auch ver­ar­bei­ten und lässt dabei doppelte Kombinationen weg, so dass sich letztlich das Fol­gende ergäbe: {"A".111;"B".222}
Bei langen Datenlisten können Hilfszellen durchaus sinnvoll sein (übersichtlich, ggf vielseitig verwendbar), besonders, wenn sie direkt und sinnvoll in die Darstellung eingebunden wdn können und nicht irgendwo versteckt wdn müssen.
SchöWo, Luc :-?
Anzeige
AW: dem war aber hier nicht so, denn ...
28.05.2018 20:47:23
neopa
Hallo Luc,
... Kisska hatte in seinen 2. Beitrag seine Aufgabenstellung in einer Datei vorgestellt, in der die auszuwertenden Daten in einer "formatierten Tabelle" bereitgestellt waren. Für die von ihm dafür angestrebte Lösung, hatte Sepp bereits eine optimale Lösungsformel eingestellt.
Die Intention für meinen dazu später eingestellten Beitrag hatte ich hier im threead bereits erklärt und auch (m)eine Alternative zu Deinem darauf bezogen Beitrag eingestellt.
Deinen zuletzt Anmerkungen zu Deiner Formel in Spalte C teile ich nicht. Denn abgesehen davon, dass diese noch erweitert werden müsste, um das von Kisska gewünschte Ergebnis zu erzielen, halte ich eine derartige plurale Matrixformel als Hilfsspaltenlösung für eine analoge Auswertung zu meiner hier für wenig günstig. Dies vor allem auch dann, nicht wenn eine veränderliche Datensatzanzahl auszuwerten sind.
Und wenn sehr, sehr viele Datensätze auszuwerten sein sollten, sollte man wohl ganz auf eine reine Formellösung verzichten und z.B. auf eine PQ-Lösung zurückgreifen. Obwohl ich bzgl. deren Kenntnis noch absolut in den Kinderschuhen stecke, war es mir heute gelungen, damit eine äquivalente Lösung z.B. zu meiner Formellösung herzustellen. Der Nachteil ein solcher Lösung ist natürlich, dass man diese bei Datenänderung mit einem Mausklick aktualisieren muss.
Gruß Werner
.. , - ...
Anzeige
Einverstanden, u.PQ ist halt 1 parametrierbares …
29.05.2018 01:09:22
Luc:-?
…Komplett-Pgm, Werner,
und steht ebenso wie Diagramme, Pivot, PPivot, StatAnalysen und eigene SubProzeduren auf bzw neben Xl - als ZusatzAngebot -, während eine „intelligente“ Tabelle (IntTab) deutlich enger eingebunden ist.
Natürlich ist eine Hilfsspalte auf pluraler MxFml-Basis ungünstig bei immer wieder hinzukommenden Daten, weniger bei genereller Neuanlage von Importen.
Diese TabellenFormatierung, auf die ihr dann fast alle umgeschwenkt seid, hatte Kisska ursprüngl auch nicht erwähnt, weshalb ich annahm, dass sie seinen verunglückten Lösungsversuchen entsprang.
Auch hatte er in seiner letzten AW an WF erwähnt, dass er mittels einer Hilfsspalte C (Werte allerdings andersherum, was letztlich egal ist) nach 1:1-Zuordnungen filtern wollte, um nur diese in eine Pivot-Auswertung eingehen zu lassen. Genau das erreicht schon der 1.Block meiner Lösung, ganz ohne IntTabFormat. ;-)
Luc :-?
Anzeige

Forumthreads zu verwandten Themen

Spalten aus über 400 excel files auslesen
marky

Hallo Excel Freaks !
Habe folgendes Problem.
Ich möchter über ein makro auf einen Ordner Zugreifen indem sich über 400 excel files befinden. Die excel files beinhalten nur ein Sheet wobei dieses den Namen des files hat (Also nicht Tabelle 1 oder Sheet 1 oder so).
Ich möchte dass das...
03.09.2008 21:44:41
Thread-ID: 1007240
5 Beiträge
Bereich auswählen auf Basis der Spaltennummern
Eberhard

Hallo zusammen :-)
Vielleicht kann mir jemand helfen.
Ein Anwender soll in einer Tabelle einen Eintrag auswählen, in dem er in irgendeiner Zeile der Tabelle eine Zelle auswählt.
Das Makro soll dann spaltenorientiert Zellen aus dieser Zeile markieren, aber zum Beispiel nur die Spalte...
10.09.2008 18:06:00
Thread-ID: 1008890
6 Beiträge
Info aus mehreren Spaltenüberschriften entnehmen
mopitz

Hallo,
Da mein Problem etwas komplex ist, füge ich eine Datei zur Veranschaulichung an.:
https://www.herber.de/bbs/user/56058.xls
Ich möchte in der ersten Tabelle zu den Spalte "Kunde"(kommt nur einmal vor) die Technologie reinschreiben lassen. Diese Info erhalte ich aus der "Tabell...
16.10.2008 13:34:19
Thread-ID: 1016847
7 Beiträge
Werte aus 2 Spalten vergleichen??
stefan

Hallo,
ich habe in 2 Spalten Werte. Wie kann ich prüfen, ob die werte in der einen spalte enthalten sind. WEnn sie enthalten sind, soll in einer 3.spalte ein text ausgegeben werden.
wie mach ich das am besten?
danke euch?
13.03.2009 16:57:19
Thread-ID: 1059944
4 Beiträge
Array Splitten Transponse Ausgabe in 2 Spalten
chris

Hallo VBA experten,
ich habe eine frage und bitte um eure Hilfe.
Ich komme alleine nicht weiter:(
Ich habe eine Tabelle1. In Spalte 1 Stehen z.b Werte untereinander.
27>27
17>17
6>6
17>1
usw...
diese möchte ich gerne in ein Array übernehmen und da...
23.04.2009 10:44:32
Thread-ID: 1070604
10 Beiträge
Spezialfilter mit ausgewählten Spalten
Michi

Liebe Gemeinde,
eine Tabelle mit ca. 50 Spalten soll per Spezialfilter ausgelsen werden. Das ganze wird per Makro und Schalfläche ausgeführt.
Soweit ich das verstehe ist beim Spezilafilter aber nur die filterung von Splaten möglich, die direkt nebeneinander liegen. Ich hingegen möchte...
13.05.2009 17:14:31
Thread-ID: 1074880
7 Beiträge
Spalten zählen und ausblenden
Sophie

Hallo liebe Forum-Leser!
Ich versuche in Excel sowas ähnliches wie MS Project darzustellen (weil das so gewünscht wird, fragt aber nicht warum).
Durch Datumseingabe (für Anfang und Ende des Projekts) will ich nur den Zeitstrahl (in Kalendeswochen) darstellen, so dass man die einzelne Sc...
15.06.2009 19:17:19
Thread-ID: 1081675
9 Beiträge
kombinationen aus spalten bilden
gh

hallo zusammen,
gibt es bei excel eine möglichkeit aus spalte 1 und 2 oder 1, 2 und 3 kombinationen zu bilden?
vielen dank im voraus!
lg
25.09.2009 12:52:27
Thread-ID: 1105553
11 Beiträge
Wert aus Textdatei mit mehreren Spalten lesen
SteffenS

Hallo Zusammen,
ich haben folgenden Code
Sub test() 'Textdatei öffnen Dim afile As String, astring As String afile = ThisWorkbook.Path & "\system\" & "aplz" Open afile For Input As #1 ' Datei öffnen. Do While Not EOF(1) Line Input #1, astring Loop Close #1 End Sub Aufbau Tex...
12.08.2010 23:31:30
Thread-ID: 1172054
3 Beiträge
Datum aus 2 spalten filtern
Gregor

Hallo
Ich habe in Spalte M ein Datum "Ausführung Beginn" und in Spalte N ein Datum "Ausführung Ende". Wie kann ich für einen bestimmten Zeitraum alle Daten zwischen Ausrührung Beginn und Ausführung Ende filtern. Zb. alle Arbeiten die in der Zeit vom 1.11.10 und 31.11.10 ausgeführt werden bz...
18.11.2010 20:16:31
Thread-ID: 1187609
3 Beiträge
alle Kombinationen aus zwei Spalten ermitteln
Jockel

Hallo,
ich habe eine Tabelle mit zwei Spalten:
A_____________B______________
Beleg_________Code__________
125___________60____________
125___________40____________
122___________30____________
122___________30____________
usw.
Ich möchte nun per Makro ermitte...
25.01.2011 09:28:18
Thread-ID: 1197160
4 Beiträge
+/- Ausgabe aufgrund Folge von 2 Spalteneinträge
2

Hallo Excelianer,
habt Ihr eine Idee, wie ich die +/- Einträge automatisiert auf Basis der 2 Regelen darstellen kann.
Die Spalten F,G,H, sind lediglich Hilfsspalten.
Ich scheitere immer daran, wenn ich auf einen vorherigen Block schauen muss. In Regel 1 muss ich ja im selben Bereic...
13.05.2011 16:01:26
Thread-ID: 1213743
2 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Überprüfung auf 1:1-Zuordnungen in Excel mit Hilfsspalten

Schritt-für-Schritt-Anleitung

Um in Excel 1:1-Zuordnungen zwischen zwei Spalten zu überprüfen, kannst Du folgende Schritte befolgen:

  1. Tabellenstruktur anlegen:

    • Erstelle eine Tabelle mit den Spalten A (Namen) und B (Nummern).
    • Beispiel: 
      | Name | Nummer |
|------|--------|
| A    | 111    |
| B    | 222    |
| B    | 222    |
| C    | 333    |
| C    | 333    |
| C    | 444    |

  2. Hilfsspalte C einfügen:

    • Füge eine Hilfsspalte C hinzu, um die 1:1-Zuordnungen zu kennzeichnen.
    • In C2 kannst Du folgende Formel verwenden: 
      =WENN(SUMMENPRODUKT((A$2:A$100=A2)*(B$2:B$100=B2))=1; 1; 0)
    • Diese Formel gibt 1 zurück, wenn die Kombination aus Spalte A und B eindeutig ist, und 0, wenn sie mehrfach vorkommt.

  3. Formel nach unten kopieren:

    • Ziehe die Ecke der Zelle C2 nach unten, um die Formel auf die restlichen Zellen in Spalte C anzuwenden.

  4. Pivot-Tabelle erstellen:

    • Erstelle eine Pivot-Tabelle, um nur die 1:1-Zuordnungen anzuzeigen. Filtere die Hilfsspalte C nach dem Wert 1.

Häufige Fehler und Lösungen

  • Problem: Die Formel in Spalte C zeigt unerwartete Ergebnisse.

    • Lösung: Überprüfe, ob die Zellreferenzen in der Formel korrekt sind. Achte darauf, dass Du den richtigen Bereich verwendest (z.B. A$2:A$100).

  • Problem: Es erscheinen doppelte Kombinationen in der Pivot-Tabelle.

    • Lösung: Stelle sicher, dass Du die Hilfsspalte C nach dem Wert 1 filterst. Nur so erhältst Du die eindeutigen Kombinationen.

Alternative Methoden

  1. Verwendung von Array-Formeln:

    • Statt einer Hilfsspalte kannst Du auch Array-Formeln verwenden, um die eindeutigen Kombinationen direkt zu ermitteln.
    • Beispiel für eine Array-Formel in D2: 
      {=WENNFEHLER(INDEX(A$2:A$100;KKLEINSTE(WENN(ZÄHLENWENN(C$2:C$100;A$2:A$100)=0;ZEILE(A$2:A$100)-MIN(ZEILE(A$2:A$100))+1);ZEILE(1:1));"")}
    • Diese Formel gibt die eindeutigen Werte zurück, auch wenn in mehreren Spalten nach Kombinationen gesucht wird.

  2. Power Query:

    • Nutze Power Query, um die Daten zu transformieren und nur die eindeutigen Kombinationen anzuzeigen.
    • Lade die Tabelle in Power Query und verwende die Funktion "Entfernen von Duplikaten".

Praktische Beispiele

Hier ist ein praktisches Beispiel zur Veranschaulichung:

Angenommen, Du hast folgende Daten:

Name Nummer
A 111
B 222
B 222
C 333
C 333
C 444

  1. Hilfsspalte C anwenden:

    • Die Formel in C2 gibt für "A, 111" und "B, 222" den Wert 1 zurück, während sie für "C, 333" und "C, 444" 0 zurückgibt.

  2. Ergebnisse in Pivot-Tabelle:

    • Erstelle eine Pivot-Tabelle mit den Namen und Nummern und filtere nach Spalte C, um nur die eindeutigen Kombinationen zu sehen.

Tipps für Profis

  • Nutze die Funktion SUMMENPRODUKT, um auch mehrere Spalten zu prüfen. So kannst Du prüfen, ob der Wert in zwei Spalten vorkommt.
  • Wenn Du mit vielen Datensätzen arbeitest, kann Power Query viel Zeit sparen, indem es alle Kombinationen effizient analysiert.
  • Bei sehr großen Datenmengen könntest Du in Betracht ziehen, VBA zu verwenden, um die Leistung zu steigern.

FAQ: Häufige Fragen

1. Wie kann ich prüfen, ob ein Wert in zwei Spalten vorkommt? Du kannst die Funktion ZÄHLENWENN in Kombination mit SUMMENPRODUKT verwenden, um zu überprüfen, ob ein Wert in beiden Spalten vorhanden ist.

2. Ist es möglich, alle Kombinationen aus mehreren Spalten anzuzeigen? Ja, Du kannst INDEX und VERGLEICH verwenden, um alle Kombinationen aus mehreren Spalten zu extrahieren. Alternativ kannst Du Power Query verwenden, um alle Kombinationen zu generieren.

3. Funktioniert das auch in Excel 365? Ja, die beschriebenen Methoden funktionieren in Excel 365 sowie in früheren Versionen wie Excel 2010 oder 2013.

Beliebteste Forumthreads (12 Monate)

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