Ergebnis 1 bis 1 von 1
  1. #1
    Office-Hilfe.com - Auskenner
    Registriert seit
    30.12.2016
    Beiträge
    136

    Alle Kombinationen mehrerer Einträge pro Spalte listen


    Einleitung


    __|_____A_____|_______B_______|__C__|CODETab von lupo1
    1|Wasserpumpe|Ovalflansch |10ccm|
    2|Ölpumpe |Rundflansch |20ccm|
    3| |Vertikalflansch| |
    soll - alles mit jedem kombiniert - werden zu:

    __|_____A_____|_______B_______|__C__|CODETab von lupo1
    8|Wasserpumpe|Ovalflansch |10ccm|
    9|Wasserpumpe|Ovalflansch |20ccm|
    10|Wasserpumpe|Rundflansch |10ccm|
    11|Wasserpumpe|Rundflansch |20ccm|
    12|Wasserpumpe|Vertikalflansch|10ccm|
    13|Wasserpumpe|Vertikalflansch|20ccm|
    14|Ölpumpe |Ovalflansch |10ccm|
    15|Ölpumpe |Ovalflansch |20ccm|
    16|Ölpumpe |Rundflansch |10ccm|
    17|Ölpumpe |Rundflansch |20ccm|
    18|Ölpumpe |Vertikalflansch|10ccm|
    19|Ölpumpe |Vertikalflansch|20ccm|

    Lösung

    Mit

    A6[:D6]: =MAX(1;PRODUKT(ANZAHL2(A1:A5);B6))

    ermitteln wir die Anzahlen, ab welcher sich die jeweilige Spalte wiederholt. Die letzte Spalte (C) dreht sich dabei am häufigsten, ist also nur 2 Einträge lang. Da die Spalte B der Spalte C gegenüber 3 "übergeordnete" Einträge hat (das ist jetzt einfach so behauptet), benötigt sie 2x3=6 Einträge, bis sie wieder von vorn beginnt. Und Spalte A ist die im Beispiel gewählte "Tophierarchie" mit 2x3x2=12 Einträgen. Das Ganze gelingt mit Hilfe von PRODUKT. Eine künstliche Spalte D mit dem künstlichen Wert 1 benötigen wir hierfür jedoch noch als Anker:

    __|_____A_____|_______B_______|__C__|__D_|CODETab von lupo1
    6| 12,00| 6,00| 2,00|1,00|
    A8[:C19]: =INDEX(A$1:A$5;REST((ZEILE(A1)-1)/B$6;A$6/B$6)+1)

    sorgt nun für die 12zeilige Verwertung der Kopfzeilen 1:5 (die im Beispiel aber nur bis Zeile 2 oder 3 gefüllt sind). In der Einleitung steht schon das gewünschte Ergebnis, welches hier nicht wiederholt wird.


    Baue Dir die Datei selbst

    Führe dafür einfach folgendes Makro aus:

    Sub EintraegeVonSpaltenKombinieren()
    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


    Anmerkungen:
    • Die definierten Namen im Erstellungsmakro bewirken, dass in der erstellten Tabelle Spalten und Zeilen beliebig eingefügt, gelöscht und verschoben werden dürfen, was mit normalen Formeln nicht geht. Nur die letzte Spalte (hier: D) muss als jeweils letzte Spalte (also auch nach Einfügen oder Löschen) erhalten bleiben, da der Wert 1 in Zeile (hier) 8 benötigt wird.
    • Die Zuweisung definierter Namen als Zellformeln funktioniert in VBA auch ohne .Formula bzw. .FormulaR1C1
    Geändert von lupo1 (25.09.2017 um 16:31 Uhr)

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  
Excel Ticker - News, Tipps und Tricks zu Microsoft Excel | SMS kostenlos versenden | Forenuser - Die Foren Findmaschine