Excel van matrix naar kolommen met 12 maanden herhaling

[BiancaSiz]

Wat fijn als jullie mij kunnen helpen. Ben al dagen aan het puzzelen.

Mijn vraag
Ik wil de begroting in gaan lezen 12 maanden, waarbij alle combinaties (vanuit de matrix) 12x moeten voorkomen. Liefst met formule en als er geen data staat dat deze regel wordt overgeslagen. Cellen in rood zo krijg ik het aangeleverd, Groen de gewenste output.

Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb (dat is licht blauw) in het voorbeeld
=INDIRECT(ADRES(REST((RIJ());$F$2)+$F$3;INTEGER((RIJ())/$F$2)+($F$4+6)))

welke formules moeten ik gebruiken in cel A20, B20,D20 en evt D20


Output

[dix-neuf]

Je gebruikt in je formule kolom F, maar die is niet te zien.
Hetzelfde geldt voor de periodenummers: het is niet duidelijk waar je die vandaan haalt.

[superduper]

Kan je de boel niet beter met een pivottable organiseren?

[g0tanks]

superduper schreef op woensdag 8 juni 2022 @ 17:14:
Kan je de boel niet beter met een pivottable organiseren?

Volgens mij wil je hier de andere kant op. Het is al in de stijl van een draaitabel en TS wil er meer een platte lijst van maken.

[BiancaSiz]

Dank je wel voor jullie reactie 's. Ik wil graag van rood naar groen.
In blauw staat wat ik tot nu toe heb. A1 en C1 zetten de matrix om naar 1 kolom maar ik mis nog het gedeelte dat het herhaald wordt. Als ik 1 maand heb staan moet de selectie wordt herhaald. (alle 12 maanden dezelfde data alleen de periode is anders
.

[Patrick_6369]

Geen idee hoe je dit in Excel zelf doet.

Via PowerQuery is dit met de functie UnPivot wel te realiseren. Als je nog nooiit met PowerQuery hebt gewerkt stel ik voor dat je even de tijd neemt om een aantal introductiemanual-filmpjes op YOutube te bekijken en daarna een enkele over de Unpivotfunctie. Dan kun je aan de slag!

[dix-neuf]

@Bianca,
Welk resultaat wil je nu eigenlijk? Zoals in de groene tabel in je eerste bericht, of zoals in de blauwe tabel in je laatste? En als het de laatste (blauwe) is: waar haal je die nullen in kolom B vandaan? Die zie ik niet staan in de rode tabel waaruit je de gegevens haalt.

[dix-neuf]

Als ik het goed begrepen heb, dan wil je voor 12 maanden de gegevens weergeven zoals in de afbeelding hieronder voor maand 1 is gedaan. Als dat zo is, dan kun je daarvoor onderstaande macro gebruiken. Er is daarbij vanuitgegaan dat de oorspronkelijke gegevens in het blad staan met de naam "Blad1" in de kolommen H:P . Het resultaat komt dan in het blad met de naam "Blad2".

code:

Sub macro1()
Dim k As Integer, lr1 As Integer, lr2 As Integer, p As Integer, r As Integer
Set sh1 = Sheets("Blad1"): Set sh2 = Sheets("Blad2")
a = 0: p = 1: r = 1: k = 13: lr2 = 1
Application.ScreenUpdating = False
sh2.Columns("a:d").ClearContents
With sh1
.Range("h1:p1").Font.Bold = True
lr1 = .Cells(Rows.Count, 8).End(xlUp).Row
Do Until lr2 > 16
.Range(.Cells(r, k), .Cells(r + 3, k)).Copy sh2.Cells(lr2, 1)
.Range(.Cells(r, 8), .Cells(r + 3, 8)).Copy sh2.Cells(lr2, 2)
.Range(.Cells(r, k - 4), .Cells(r + 3, k - 4)).Copy sh2.Cells(lr2, 3)
sh2.Range(sh2.Cells(lr2 + 1, 4), sh2.Cells(lr2 + 3, 4)) = p
k = k + 1
lr2 = sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row + 1
Loop
a = a + 1: k = 13: p = p + 1: r = r + 4: lr2 = lr2 + 1
Do Until r > lr1
Do While a < 5
Select Case a
Case 1
Set myrange = sh2.Range("a1:d1")
Case 2
Set myrange = sh2.Range("a5:d5")
Case 3
Set myrange = sh2.Range("a9:d9")
Case 4
Set myrange = sh2.Range("a13:d13")
Case Else
End Select
myrange.Copy sh2.Cells(lr2, 1): a = a + 1: lr2 = lr2 + 1
.Range(.Cells(r, k), .Cells(r + 2, k)).Copy sh2.Cells(lr2, 1)
.Range(.Cells(r, 8), .Cells(r + 2, 8)).Copy sh2.Cells(lr2, 2)
.Range(.Cells(r, k - 4), .Cells(r + 2, k - 4)).Copy sh2.Cells(lr2, 3)
sh2.Range(sh2.Cells(lr2, 4), sh2.Cells(lr2 + 2, 4)) = p
k = k + 1
lr2 = sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row + 1
Loop
a = 1: k = 13: lr2 = lr2 + 1: p = p + 1: r = r + 3
Loop
End With
With sh2.Columns("a:d")
.AutoFit
.HorizontalAlignment = xlCenter
End With
Application.ScreenUpdating = True
End Sub

[ Voor 4% gewijzigd door dix-neuf op 16-06-2022 18:32 ]

[BiancaSiz]

Beste dix-neuf,

Ik ben nog niet bekend met marco's . Hoe moet ik dat aanpakken?

[dix-neuf]

1. Selecteer de code uit mijn vorig bericht, klik op de rechtermuisknop en kies: Kopiëren (je ziet dan nog niets gebeuren).
2. Open je Excelfile met de gegevens waarover je het in je eerste bericht had.
3. Klik met de rechtermuisknop onderaan op de naam van een willekeurig tabblad en kies in het verschijnende menu: Programmacode weergeven. Je komt dan in de vba-editor.
4. Kies in het menu bovenaan: Invoegen -> Module.
5. Klik in het rechtergedeelte op de rechtermuisknop en klik op Plakken. De onder punt 1 gekopieerde macro zie je daar dan verschijnen.
6. In de tweede regel van de macro zie je staan: Set sh1 = Sheets("Blad1"): Set sh2 = Sheets("Blad2"). Dat betekent dat ik ervan uitgegaan ben dat het blad met gegevens Blad1 heet en het blad waarin het resultaat moet komen Blad2. Hebben de bladen in jouw bestand andere namen, dan moet je ofwel in die tweede regel de bladnamen wijzigen (maar de aanhalingstekens laten staan), ofwel je moet in jouw Excelbestand de bladnamen wijzigen in Blad1 en Blad2 .
7. Klik op het kruisje rechtsboven, je verlaat dan de vba-editor en hebt een werkblad voor je.
8. Kies in het menu: Bestand - Opties, en klik in de linkerkolom op Werkbalk snelle toegang.
9. Kies in de tabel rechts van het menu, bovenaan onder "Kies opdrachten uit:", met het pijltje: Macro's. Klik dan 1 keer op Macro1 en klik tussen de twee tabellen op Toevoegen. Klik daarna rechts-onderaan in het venster op Ok. Je komt dan terug in een werkblad en je ziet in de Werkbalk snelle toegang (die - naar keuze - boven of onder het lint kan staan) een icoon met 3 gele rechthoekjes waarmee de macro in werking kan worden gesteld.
10. Klik 1 keer op dat icoon en de macro wordt uitgevoerd, dit kan enkele seconden duren.
Opm. 1: In de macro is ervan uitgegaan dat je gegevens in de kolommen H:P staan; is dat niet het geval, dan zal de macro niet werken! Een macro past zich nl. nooit automatisch aan.
Opm. 2: Als je de macro al eerder gekopieerd hebt, doe dat dan opnieuw, want ik heb een kleine wijziging er in aangebracht.
Succes ermee, laat a.u.b. even weten of het is gelukt.

[Teun_2]

Patrick_6369 schreef op vrijdag 10 juni 2022 @ 15:46:
Geen idee hoe je dit in Excel zelf doet.

Via PowerQuery is dit met de functie UnPivot wel te realiseren. Als je nog nooiit met PowerQuery hebt gewerkt stel ik voor dat je even de tijd neemt om een aantal introductiemanual-filmpjes op YOutube te bekijken en daarna een enkele over de Unpivotfunctie. Dan kun je aan de slag!

Dit is echt helemaal het juiste antwoord.
Macro kan ook, maar is wat meer gedoe.
Met powerquery kan je dit met een tiental clicks in orde hebben.
Als je het toch per sé met formules wil, kan dat met index match en array-formules, maar powerquery is eccht tien keer gemakkelijker. (en een skill die je echt wil hebben): https://exceljet.net/form...ch-with-multiple-criteria

[ Voor 17% gewijzigd door Teun_2 op 21-06-2022 09:09 ]