Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1796to1800
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
1796to1800
1796to1800
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kombinationen finden

Kombinationen finden
29.11.2020 14:02:26
erichm
Hallo,
ich habe 2 Tabellen:
Tabelle Auswertung: hier sind in den Spalten GT, GU und GV jeweils ab Zeile 2 immer vierstellige Ziffern die unterschiedlich oft vorkommen. Dabei ist es immer so, dass die jeweils ersten beiden Ziffern in den Spalten aufsteigend sind, z.B. 0108 0509 1115
Es kann also nicht vorkommen: 0509 0108 1115
Tabelle Daten: hier sind in den Spalten HI und HJ ab Zeile 44 jeweils 2 vierstellige Ziffern (= eine Kombination), also z.B. 0101 0108. Dabei ist es immer so, dass pro Zeile die beiden ersten Ziffern immer gleich sind. Es kommt also nicht vor, z.B. 0101 0509
Jetzt muss ich in der Tabelle Auswertung, immer betreffend 7 Zeilen untereinander, also
Zeilen 2 bis 8
Zeilen 3 bis 9
Zeilen 4 bis 10
usw….
herausfinden, ob sich darin eine Kombination aus der Tabelle Daten, Spalten HI und HJ befindet. Dabei ist es egal ob in der gleichen Zeile oder innerhalb der 7 Zeilen, also z.B. Zeilen 2 bis 8.
Diese Auswertung kann ab Spalte GW erfolgen, ggfls. auch in den Spalten GW GX und GY.
Hier die Musterdatei (Microsoft365):
https://www.herber.de/bbs/user/141926.xlsx
Vielen Dank für eine Hilfe.
Mfg

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Schön wäre der Eintrag einer Musterlösung ...
29.11.2020 14:13:51
lupo1
... in der Datei (händisch). Mit begründendem Text dahinter.
Dann so:
29.11.2020 16:22:25
lupo1
GZ8:
=MIN(
WENNFEHLER(VERGLEICH(GT2:GT8&MTRANS(GU2:GU8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GT2:GT8&MTRANS(GV2:GV8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GU2:GU8&MTRANS(GV2:GV8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GU2:GU8&MTRANS(GT2:GT8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GV2:GV8&MTRANS(GT2:GT8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GV2:GV8&MTRANS(GU2:GU8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4))
Abweichend von Deiner Tabelle bekomme ich ab nicht schon in GZ11 ein NEIN (bei mir: "10000"), sondern erst in GZ30. Bei allen dazwischenliegenden gibt es also Treffer.
Deine anderen drei Ergebnisspalten sind überflüssig.
Das Ergebnis bei Treffer ist immer das erstmögliche bei mehreren (MIN).
Du musst in älteren Excel-Versionen die Formel als {} eingeben.
Anzeige
Dann werden aus den 6 Zeilen wie angekündigt 9:
29.11.2020 19:45:46
lupo1
Quaranta-y-tres!
GZ8:
=MIN(
WENNFEHLER(VERGLEICH(GT2:GT8&MTRANS(GU2:GU8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GT2:GT8&MTRANS(GV2:GV8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GU2:GU8&MTRANS(GV2:GV8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GU2:GU8&MTRANS(GT2:GT8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GV2:GV8&MTRANS(GT2:GT8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GV2:GV8&MTRANS(GU2:GU8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GT2:GT8&MTRANS(GT2:GT8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GU2:GU8&MTRANS(GU2:GU8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4);
WENNFEHLER(VERGLEICH(GV2:GV8&MTRANS(GV2:GV8);Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10^4))
Anzeige
Formel eingekürzt um strukturell gleiche Teile
29.11.2020 21:05:10
lupo1
GZ8:
=MIN(WENNFEHLER(VERGLEICH(
INDEX(GT2:GV8;ZEILE($7:$55)/7;SPALTE($C:$K)/3)&
INDEX(GT2:GV8;REST(ZEILE($7:$55);7)+1;REST(SPALTE($C:$K);3)+1);
Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10000))
oder in 365-Schreibweise:
=MIN(WENNFEHLER(VERGLEICH(
INDEX(GT2:GV8;SEQUENZ(49;;;1/7);SEQUENZ(;9;;1/3))&
INDEX(GT2:GV8;REST(SEQUENZ(49;;0);7)+1;REST(SEQUENZ(;9;0);3)+1);
Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;10000))
Schneller wird sie dadurch allerdings nicht.
... und jetzt mit Fundortliste statt nur 1. Hit
29.11.2020 21:19:30
lupo1
GZ8:
=TEXTVERKETTEN(";";;WENNFEHLER(VERGLEICH(
INDEX(GT2:GV8;ZEILE($7:$55)/7;SPALTE($C:$K)/3)&
INDEX(GT2:GV8;REST(ZEILE($7:$55);7)+1;REST(SPALTE($C:$K);3)+1);
Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;""))
(müsste ab xl2016 oder xl2019 klappen)
Anzeige
Und diese nun noch sortiert/ohne Duplikate:
29.11.2020 21:32:17
lupo1
GZ8:
=TEXTVERKETTEN(";";;SORTIEREN(EINDEUTIG(
GLÄTTEN(TEIL(WECHSELN(TEXTVERKETTEN(";";;WENNFEHLER(VERGLEICH(
INDEX(GT2:GV8;ZEILE($7:$55)/7;SPALTE($C:$K)/3)&
INDEX(GT2:GV8;REST(ZEILE($7:$55);7)+1;REST(SPALTE($C:$K);3)+1);
Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;""));
";";WIEDERHOLEN(" ";299));ZEILE($1:$99)*299-298;299)))))
Noch falsch sortiert, daher hier Korrektur:
29.11.2020 22:03:47
lupo1
GZ8:
=TEXTVERKETTEN(";";;SORTIEREN(EINDEUTIG(
GLÄTTEN(TEIL(WECHSELN(TEXTVERKETTEN(";";;WENNFEHLER(TEXT(VERGLEICH(
INDEX(GT2:GV8;ZEILE($7:$55)/7;SPALTE($C:$K)/3)&
INDEX(GT2:GV8;REST(ZEILE($7:$55);7)+1;REST(SPALTE($C:$K);3)+1);
Daten!HI$44:HI$923&Daten!HJ$44:HJ$923;)+43;"000");""));";";
WIEDERHOLEN(" ";299));ZEILE($1:$99)*299-298;299)))))
... aber Besserwisser's Strings sind halt kürzer :-)
Anzeige
AW: ... und jetzt mit Fundortliste statt nur 1. Hit
29.11.2020 21:36:52
Besserwisser
Hallo Zusammen,
hier noch eine kürzere Alternative:
=TEXTVERKETTEN(";";;MODUS.VIELF(WENNFEHLER(ZEILE(Daten!$HI$44:$HI$923) /ISTZAHL(FINDEN(Daten!$HI$44:$HJ$923;TEXTVERKETTEN("*";;GT3:GV9);1));"")))
Gruß
Christian
Klasse!
29.11.2020 21:44:38
lupo1
... und gleich sortiert/eindeutig ohne die dynamischen Funktionen von XL365.
AW: schließe mich der Aussage von lupo an ......
30.11.2020 09:54:51
lupo
Hallo Christian,
... leider kann ich Deine Formel momentan in meinen Excelversionen (2010 und 2016) nicht testen, weil es da TEXTVERKETTEN() noch nicht gibt. Dies hole ich aber vielleicht in nicht zu ferner Zeit noch nach.
Nun noch nachgefragt.
Von lupos Formeln kann ich auch nur die mit den MTRANS() testen. Mit dieser werden 718 Ergebniswerte ermittelt. Da mit meinen Formeln wesentlich weniger ermittelt werden, geh ich davon aus, dass meine Interpretation nicht richtig war, wenn Du die gleichen Ergebniswerte ermittelt hast wie lupo. Oder?
Gruß Werner
.. , - ...
Anzeige
Besserwisser hat gleiche Ergebnisse wie ich, ...
30.11.2020 11:38:14
lupo1
... wobei er gleich von vornherein keine Zeilen-Duplikate ausweist und richtig sortiert.
=TEXTVERKETTEN macht eben vieles möglich und ist von den Neuerungen seit xl2007 für mich so ziemlich auf Platz 1. Die neuen dynamischen Formeln natürlich auch, aber dann gleich als Paket eines Paradigmenwechsels "Wegfall der Beschränkung einer Formel auf eine Zelle".
AW: ... und jetzt mit Fundortliste statt nur 1. Hit
01.12.2020 13:04:34
erichm
Hallo Besserwisser,
wirklich eine klasse Lösung. Das verschafft mir die Option einer weiteren Optimierung im Projekt:
Zuordnung für jede gefundene Zeile zu der betreffenden Kombination.
Folgende Zeilen wurden z.B. gefunden:
57;58
293;392
51;481;719
Grundsätzlich könnte ich durch den Assistenten "Text in Spalten" die Werte ab Spalte GX aufteilen. Da ich die Spaltenanzahl begrenzen muss wollte ich mir eine Formel für die Aufteilung erstellen. Irgendwie scheitere ich leider daran, dass das Semilikon ja immer an unterschiedlichen Stellen vorkommt.
Besten Dank für eine weitere Hilfe.
mfg
Anzeige
...doch noch eine Lösung gefunden
03.12.2020 07:29:02
erichm
wenn Zelle A1 ausgewertet wird:
51;481;719
folgende Matrixformel:
=WENN(SPALTE(A$1)>(LÄNGE($A1)-LÄNGE(WECHSELN($A1;";";""))+1);"";LINKS(TEIL(";"&$A1&";"; KKLEINSTE(WENN(TEIL(";"&$A1&";";SPALTE($1:$1);1)=";";SPALTE($1:$1));SPALTE(A1))+1;55);FINDEN(";"; TEIL(";"&$A1&";";KKLEINSTE(WENN(TEIL(";"&$A1&";";SPALTE($1:$1);1)=";";SPALTE($1:$1));SPALTE(A1))+1; 55))-1))
AW: mE noch nicht eindeutig ...
29.11.2020 17:59:48
neopa
Hallo Erich,
... denn wie Deine Angaben interpretiere erhalte ich viele andere Ergebniswerte als lupo. Wohl nur Du kannst entscheiden, was für Dich zutreffend ist.
Meine Ermittlung der zutreffenden duplikatfreien Zeilen erfolgte mit nachfolgenden Formel:
In GW2:
=WENNFEHLER(AGGREGAT(15;6;ZEILE($E$44:E923)/(ZÄHLENWENN(GT2:GV8;Daten!HI$44:HI$923)+ZÄHLENWENN(GT2:GV8;Daten!HJ$44:HJ$923)=2)/(ZÄHLENWENN(GW$1:GW1;ZEILE(E$44:E$923))=0);1);"")
und diese nach unten kopiert (was meinen PC wie auch bei der Formel von lupo nach Luft schnappen lies).
Mit folgender einfachen Formel hab ich dann die so ermittelten Zeilennummern in einer 2. Spalte sortiert gelistet:
=WENNFEHLER(KKLEINSTE(GW:GW;ZEILE(E1));"")
Damit erhalte ich für Deine Datei 515 Ergebniszeilen. Wenn ich die Ergebniswerte mit lupos Formelvorschlag duplikatfrei auswerte, komme ich dagegen auf 756.
Gruß Werner
.. , - ...
Anzeige
Bei Dir ist eine Kombi i derselben Spalte zulässig
29.11.2020 18:07:28
lupo1
... ich hatte es so verstanden, dass es die Paare
GT GU
GT GV
GU GV
sein müssen (und umgekehrt), also 6 statt 9.
Aber die Aufgabe ist nicht zweifelsfrei formuliert.
AW: Bei Dir ist eine Kombi i derselben Spalte zulässig
29.11.2020 19:40:58
erichm
Hallo lupo1 und Werner,
stimmt, die Aufgabe ist nicht eindeutig genau formuliert.
Richtig interpretiert wurde sie von Werner, d.h. auch in derselben Spalte soll eine Kombination geprüft / gefunden werden.
Bei der Formellösung von Werner komme ich jedoch noch zu anderen Ergebnissen bei manueller Suche:
In GW2 wird auf die Zeile 124 von Daten verwiesen, das ist die Kombination 0501 0508 - diese Kombi finde ich aber nicht in den Zeilen 2 bis 8, auch keine andere Kombi. Für diesen Bereich ist das Ergebnis von lupo1 richtig.
Interessant ist der Formelvergleich für die Zeilen 23 bis 29:
lupo1: findet die Kombination, Zeile 127 = 0501 0515
Werner: keine Kombi gefunden (ist bei Werner die Auswertungszeile 23)
Also wenn ich jetzt beide Formeln richtig verstanden habe....
Die richtige Lösung wäre also, wenn bei lupo1 die Kombi in der gleichen Spalte noch geprüft wird
bzw.
wenn bei Werner's Lösung noch eine Anpassung erfolgt.
Vielen Dank jedenfalls - unabhängig davon sind beide Lösungsvarianten wieder hochinteressant!!
mfg
Anzeige
AW: sehe ich noch etwas anders ...
29.11.2020 20:11:06
neopa
Hallo Erich,
... die Kombi-Zeile 127 wurde bei mir gefunden und zwar in der noch unsortierten Liste in Zeile 21.
Die Zeile 124 wird bei nur gefunden, wenn ich nur in einer Spalte suche.
Ich würde meine Formel zu unsortierten aber duplikatfreien Ermittlung der "Kombi"-Zeilen jetzt nur wie folgt in GW2 leicht ergänzen:

=WENNFEHLER(AGGREGAT(15;6;ZEILE($E$44:E923)/((ZÄHLENWENN(GT2:GV8;Daten!HI$44:HI$923)&gt0) +(ZÄHLENWENN(GT2:GV8;Daten!HJ$44:HJ$923)&gt0)=2)/(ZÄHLENWENN(GW$1:GW1;ZEILE(E$44:E$923))=0);1);"")
Damit werden dann aber noch weniger zutreffende Kombinationen gefunden. Die Zeile 127 wird nun in der Auswertungszeile 18 gefunden.
Gruß Werner
.. , - ...
Anzeige
AW: sehe ich noch etwas anders ...
01.12.2020 12:44:06
erichm
Hallo Werner,
danke für die Ergänzung. Die Ergebnisse führen noch nicht zum Ziel - ist aber grundsätzlich kein Problem mehr, da die beiden Formeln von lupo1 und Besserwisser zu den gleichen und auch von mir gewünschten Ergebnissen führen. Die Anzahl der Zeilen, die "ohne Kombinationen" vorkommen sind in beiden Fällen 530.
mfg

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige