Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1624to1628
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
Inhaltsverzeichnis

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

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
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
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
Anzeige
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!
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!
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 :-?
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
.. , - ...
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 :-?

147 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige