Kombinationen / Varianten hochzählen

06.04.2019 15:05:54

erichm

Hallo,
ich habe mir 2 Tabellen gebastelt mit denen ich die Inhalte von Zellen zu Kombinationen verbinde.
2fach:
D.h. es gibt 2 Spalten mit einer unterschiedlichen Anzahl von Zeilen. Es wird jede Zelle (ZEILE) der Spalte 1 mit jeder Zelle (ZEILE) der Spalte 2 kombiniert.
3fach:
Wie 2fach, erweitert um eine dritte Spalte, wiederum werden aus jeder Spalte die Zellen mit jeder Zelle der anderen Spalten kombiniert.
So weit so gut.
Meine Versuche "dieses System" auf 4-fach auszuweiten sind leider gescheitert. Infosern suche ich die Lösung für die Tabelle 4fach in der beiliegenden Musterdatei.
https://www.herber.de/bbs/user/128977.xlsx
Anhand der Tabellen 2fach und 3fach kann "das System" denke ich gut nachvollzogen werden.
Besten Dank für eine Hilfe.
mfg

AW: als reine Formellösung kaum zu empfehlen, ...

06.04.2019 18:38:45

neopa

Hallo Erich,
... weil es in der "4-fach" Variante im Extremfall über 455 Tausend Formeln werden könnten (in der Beispielvariante würden es allerdings "lediglich" ca 5.000), wenn ich Deine Angaben richtig deute, wonach je Teil bis zu 15 Varianten möglich sein können.
Und meine Hochrechnung für den Extremfall setzt schon voraus, dass durch eine andere Formeldefinition in Q:T die Formeln in A:E evtl. eingespart werden könnten. Ohne dies kämen nochmals max. über 250.000 Formeln hinzu.
Gruß Werner
.. , - ...

AW: als reine Formellösung kaum zu empfehlen, ...

06.04.2019 18:54:17

erichm

Hallo Werner,
danke für die Rückmeldung. Bei der 4fach-Variante werden sich die Anzahl der betroffenen Zeilen "zwangsläufig" in Grenzen halten müssen. 4fach-Kombinationen werden nur für einzelne Projektabschnitte benötigt die mengenmäßig gut eingegrenzt werden können. Deswegen auch das gewählte "kleine Beispiel".
Aber manuell mit kopieren o.ä. aufzulisten, ist dann doch zu aufwändig. Darum die Anfrage hier.
Falls es eine VBA-Lösung gäbe, könnten wir diese ebenfalls integrieren.
mfg

AW: wenn dem so sein soll, dann ...

07.04.2019 16:27:08

neopa

Hallo Erich,
... würde ich folgende Auswertung vornehmen. Nachfolgende Formel H3 nach rechts und die Formeln A4:E4 weit genug nach unten ziehend kopieren. Diese Formeln sollten auch schneller auswerten, als die SVERWEIS()-Formeln.
Die Auswertung für 2- und 3-fach entsprechend dem hier aufgezeigten entsprechend anpassen.

Arbeitsblatt mit dem Namen '4fach'
	A	B	C	D	E	F	G	H	I	J	K
1								3	4	5	6
2
3	Teil1	Teil2	Teil3	Teil4	Ausw			Teil1	Teil2	Teil3	Teil4
4	A	E	I	P	i.O.			A	E	I	P
5	A	E	I	Q	i.O.			B	F	J	Q
6	A	E	I	R	i.O.			C	G	K	R
7	A	E	I	S	i.O.				H	L	S
8	A	E	I	T	i.O.					M	T
9	A	E	I	U	i.O.						U
10	A	E	J	P	i.O.
11	A	E	J	Q	i.O.
12	A	E	J	R	i.O.
13	A	E	J	S	i.O.
14	A	E	J	T	i.O.
15	A	E	J	U	i.O.
16	A	E	K	P	i.O.

Zelle	Formel
H1	=ANZAHL2(H4:H15)
A4	=INDEX(H:H;KÜRZEN((ZEILE(G1)-1)/I$1/J$1/K$1;0)+4)&""
B4	=WENN(A4="";"";INDEX(I:I;KÜRZEN(REST(ZEILE(H1)-1;PRODUKT(I$1:K$1))/K$1/J$1;0)+4))
C4	=WENN(B4="";"";INDEX(J:J;KÜRZEN(REST(ZEILE(I1)-1;J$1*K$1)/K$1;0)+4))
D4	=WENN(C4="";"";INDEX(K:K;REST(ZEILE(J1)-1;K$1)+4))
E4	=WENN(SUMMENPRODUKT(ZÄHLENWENN(A4:D4;A4:D4))=4;"i.O.";"n.b.")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...

Superschnelle Lösung Werner - vielen Dank! o.w.T.

09.04.2019 14:46:42

erichm

...

AW: bitteschön owT

09.04.2019 14:55:42

neopa

Gruß Werner
.. , - ...

Das geht auch mit nur 2 verschiedenen Formeln:

10.04.2019 09:35:19

lupo1

http://www.office-hilfe.com/support/showthread.php/22312
Die benannten Formeln (müssen aber nicht benannt sein) heißen:
(rostbraun) =KombinationenAbHier
(giftgrün) =Kombinationsfeld

Ein Link kann ja mal gegenstandslos werden; daher:

10.04.2019 10:08:09

lupo1


Sub EintraegeVonSpaltenKombinieren() 'baut eine exemplarische Datei
Workbooks.Add xlWorksheet
ActiveWorkbook.Names.Add Name:="KombinationenAbHier", _
RefersToR1C1:="=MAX(1,PRODUCT(COUNTA(R[-5]C:R[-1]C),RC[1]))"
ActiveWorkbook.Names.Add Name:="Kombinationsfeld", _
RefersToR1C1:="=INDEX(R1C:R5C,MOD((ROW(R[-7]C)-1)/R6C[1],R6C/R6C[1])+1)"
[A1:C1] = Split("Wasserpumpe Ovalflansch 10ccm")
[A2:C2] = Split("Ölpumpe Rundflansch 20ccm")
[A3:C3] = Split(" Vertikalflansch ")
[A6:D6] = "=KombinationenAbHier"
[A8:C19] = "=Kombinationsfeld"
[A1:D5].Interior.Color = 44444
[A6:D6].Interior.Color = 22222
[A8:C19].Interior.Color = 55555
End Sub

AW: Ein Link kann ja mal gegenstandslos werden; daher:

12.04.2019 10:10:59

erichm

Hallo lupo1,
DANKE für den tollen Service.
Für eine Optimierung meines Einsatzzwecks ist es mir gelungen, folgende Anpassungen vorzunehmen:
1. neue Tabelle im aktiven Workbook erstellen (statt neue Datei)
2. flexible Anzahl der auszuwertenden Zeilen
hier das Beispiel für 9 statt 5 Zeilen:

Sub EintraegeVonSpaltenKombinieren10() 'baut eine exemplarische Tabelle
ThisWorkbook.Worksheets.Add.Name = "Kombi10"
ActiveWorkbook.Names.Add Name:="KombinationenAbHier10", _
RefersToR1C1:="=MAX(1,PRODUCT(COUNTA(R[-9]C:R[-1]C),RC[1]))"
ActiveWorkbook.Names.Add Name:="Kombinationsfeld10", _
RefersToR1C1:="=INDEX(R1C:R9C,MOD((ROW(R[-11]C)-1)/R10C[1],R10C/R10C[1])+1)"
[A1:C1] = Split("Wasserpumpe Ovalflansch 10ccm")
[A2:C2] = Split("Ölpumpe Rundflansch 20ccm")
[A3:C3] = Split(" Vertikalflansch ")
[A10:D10] = "=KombinationenAbHier10"
[A12:C30] = "=Kombinationsfeld10"
'[A1:D5].Interior.Color = 44444
[A10:D10].Interior.Color = 22222
'[A8:C19].Interior.Color = 55555
End Sub

An folgender weiterer Optimierung scheitere ich aber:
Formelanpassung für weitere Spalten die auszuwerten sind. Also derzeit sind es 3 Spalten; diese möchte ich auf 4 / 5 / 6 anpassen.
Vielen Dank für eine Überprüfung bzw. Vorschlag für diese Erweiterungen.
Hilfreich wäre noch, wenn in der Datei überprüft wird, ob die neu zu erstellende Tabelle bereits vorhanden ist und wenn ja, diese dann zu löschen bevor der Code ausgeführt wird.
mfg

doch noch eine Lösung gefunden

12.04.2019 20:22:10

erichm

für 9 Zeilen und 4 Spalten:

Sub EintraegeVonSpaltenKombinieren104() 'baut eine exemplarische Tabelle
Blatt_Loeschen104
ThisWorkbook.Worksheets.Add.Name = "Kombi104"
ActiveWorkbook.Names.Add Name:="KombinationenAbHier104", _
RefersToR1C1:="=MAX(1,PRODUCT(COUNTA(R[-9]C:R[-1]C),RC[1]))"
ActiveWorkbook.Names.Add Name:="Kombinationsfeld104", _
RefersToR1C1:="=INDEX(R1C:R9C,MOD((ROW(R[-11]C)-1)/R10C[1],R10C/R10C[1])+1)"
[A1:C1] = Split("Wasserpumpe Ovalflansch 10ccm")
[A2:C2] = Split("Ölpumpe Rundflansch 20ccm")
[A3:C3] = Split(" Vertikalflansch ")
[A10:E10] = "=KombinationenAbHier104"
[A12:D30] = "=Kombinationsfeld104"
'[A1:D5].Interior.Color = 44444
[A10:E10].Interior.Color = 22222
'[A8:C19].Interior.Color = 55555
End Sub

Sub Blatt_Loeschen104()
Dim x As Long
For x = 1 To Worksheets.Count
If Worksheets(x).Name = "Kombi104" Then
Application.DisplayAlerts = False
Worksheets(x).Delete
Application.DisplayAlerts = True
Exit For
End If
Next
End Sub

für 9 Zeilen und 5 Spalten

Sub EintraegeVonSpaltenKombinieren105() 'baut eine exemplarische Tabelle
Blatt_Loeschen105
ThisWorkbook.Worksheets.Add.Name = "Kombi105"
ActiveWorkbook.Names.Add Name:="KombinationenAbHier105", _
RefersToR1C1:="=MAX(1,PRODUCT(COUNTA(R[-9]C:R[-1]C),RC[1]))"
ActiveWorkbook.Names.Add Name:="Kombinationsfeld105", _
RefersToR1C1:="=INDEX(R1C:R9C,MOD((ROW(R[-11]C)-1)/R10C[1],R10C/R10C[1])+1)"
[A1:C1] = Split("Wasserpumpe Ovalflansch 10ccm")
[A2:C2] = Split("Ölpumpe Rundflansch 20ccm")
[A3:C3] = Split(" Vertikalflansch ")
[A10:F10] = "=KombinationenAbHier105"
[A12:E30] = "=Kombinationsfeld105"
'[A1:D5].Interior.Color = 44444
[A10:F10].Interior.Color = 22222
'[A8:C19].Interior.Color = 55555
End Sub

Sub Blatt_Loeschen105()
Dim x As Long
For x = 1 To Worksheets.Count
If Worksheets(x).Name = "Kombi105" Then
Application.DisplayAlerts = False
Worksheets(x).Delete
Application.DisplayAlerts = True
Exit For
End If
Next
End Sub

mfg

Kombinationen / Varianten hochzählen

Forumthread: Kombinationen / Varianten hochzählen

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Kombinationen / Varianten hochzählen

Forumthread: Kombinationen / Varianten hochzählen

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread