Dag allemaal,
Voor het consolideren van gegevens, ben ik op zoek naar een praktische en dynamische oplossing zonder gebruik van VBA. Op dit moment ben ik redelijk handig geworden met het gebruik van Dynamische matrix formules en wil ik dit naar een volgend niveau brengen, maar ik weet niet of dit reeds mogelijk is en dat ik een onjuiste syntax gebruik of dat dit simpelweg nog niet mogelijk is.
Beschrijving casus
Op het moment dat ik een cellenbereik heb van bijvoorbeeld cel A1
5 en ik geef dit een naam, dan wordt dit ook automatisch gezien als een matrix. Stel ik noem deze matrix "Matrix1" en ik zou in cel E1 typen:
dan wordt het bereik van cel A15 uitgesmeerd over de cellen E1:H5. Stel dat dit bereik van Matrix1 in Blad1 staat en ik noem hetzelfde bereik in Blad2 "Matrix2".
Wens
Wat ik eigenlijk zou willen doen, is die 2 matrices samenvoegen tot 1 matrix, maar welke Syntax gebruik ik dan? En is het mogelijk om Matrices samen te voegen die wel dezelfde breedte, maar niet dezelfde hoogte hebben? Logischerwijs zou ik willen zien dat als ik dit intyp in cel E1:
dat over het celbereik E1:H10, eerst de gegeven van Matrix1 worden neergezet dus van E1:H5 en dan van E6:H10 moeten de gegevens van Matrix2 komen te staan.
Toepassing achtergrond vraag
Ik heb van meerdere entiteiten saldibalansen en die hebben dezelfde lay-out. Het probleem is dat deze nog wel eens willen veranderen, bijvoorbeeld als er een rekening wordt toegevoegd of als een waarde verandert. De breedte verandert niet, maar de hoogte zou kunnen veranderen en is niet in elke entiteit hetzelfde.
Wat ik uiteindelijk wil doen, is in 1 tabblad een tabel opnemen met op de horizontale as ofwel de kolommen, de namen van de entiteiten. Op de verticale as, wil ik in kolom A, de grootboekrekeningen en in kolom B, de Grootboekomschrijvingen opnemen. Het kan zo zijn dat in Entiteit 1 en in Entiteit 2, dezelfde Grootboekrekening dezelfde omschrijving heeft, maar het kan ook zo zijn, dat een Grootboekrekening een andere omschrijving heeft. De unieke zoeksleutel moet de combinatie van Grootboekrekening+Grootboekomschrijving worden, zonder deze in 1 kolom samengevoegd te hebben.
Ik wil deze tabel zo bouwen, dat deze dynamisch gevormd wordt, zodat ik geen extra werkzaamheden heb, als ik de brontabel van een entiteit vernieuw. Als er een nieuwe grootboekrekening is, moet dit ook automatisch in de geconsolideerde tabel verschijnen, nadat ik mijn nieuwe saldibalans over de oude heen heb geplakt.
Mijn stappenplan + koppeling aan wens
Mijn specifieke vraag / verzoek voor dit forum
Hopelijk heb ik mijn probleemstelling juist geformuleerd en is het probleem helder en verwacht ik niet dat een voorbeeldbestand noodzakelijk is (ik zou ook niet weten wat ik dan zou moeten opnemen). Maar ik zou jullie graag willen vragen of jullie weten of dat mijn Wens mogelijk is en wat daarvoor de juiste syntax is. Mocht dit niet mogelijk zijn, dan ben ik wel benieuwd of er een alternatieve manier is waarop ik het beoogde eindproduct kan realiseren, waarbij het dus Dynamisch moet zijn. Ik heb ook al gekeken naar gebruik van PowerPivot, maar daar kom ik ook niet uit.
Waarom Matrices samenvoegen?
Ik kan natuurlijk ook voor stap 2 en 3, bijvoorbeeld in cel A1, Matrix1 oproepen en dan in de eerste cel aangrenzend onder Matrix1, Matrix2 oproepen. Probleem is echter, als in het brontabblad de hoogte wijzigt, er verdwijnt een grootboekrekening, of er komt een grootboekrekening bij, dan zal in het eerste geval, een lege regels ontstaan tussen Matrix1 en Matrix2, waardoor bij stap 3 bijvoorbeeld niet alle regels worden beoordeeld, omdat de UNIEK formule verwijst naar die gecombineerde matrix, waarvan de hoogte bepaald wordt, door het aantal gevulde regels. Als er tussen Matrix 1 en 2 een gat zit, dan worden de laatste regels van de laatste matrix ter grootte van het gat niet meegenomen.
In het omgekeerde geval, dus er komen regels bij in bijvoorbeeld Matrix1, dan ontstaat er een #Spill error, want in de uitgangssituatie was Matrix1 5 regels en begon Matrix2 op regel 6. Op moment dat er bij Matrix1 2 regels bijkomen, zou Matrix2 op regel 8 moeten beginnen, maar deze begon op regel 6, dus geeft Matrix1, een #spill error, omdat ie gegevens wil tonen over de eerste 7 regels, maar op regel 6 een overloop bestaat.
Waarom geen VBA?
Met VBA zou dit eenvoudig op te lossen zijn, maar dit is tijdrovend en heeft als gevolg dat ik of mijn medegebruikers moet gaan opvoeden, of dat ik het bestand tot op de nok moet beveiligen, dat mijn medegebruikers het niet stuk kunnen maken. Plus dat ik nu specifiek voor 1 case het voordeel van matrices combineren opnoem, maar er zijn veel meer toepassingen te bedenken en als dit werkt, scheelt dit een hoop tijd aan VBA bestandjes maken.
Tot slot
Kom alsjeblieft niet alsnog met een VBA oplossing, want dat zou ik zelf ook kunnen. Het meedenken wordt gewaardeerd, maar bespaar jezelf dan de moeite. Ik ben eigenlijk alleen op zoek naar antwoorden in de geest van: wat jij wil kan niet, of dit doe je met de volgende syntax of een mooie link naar een powerpivot oplossing o.i.d.
In ieder geval, mijn dank is groot aan degene die met de oplossing komt. In ieder geval alvast bedankt voor het meedenken.
Voor het consolideren van gegevens, ben ik op zoek naar een praktische en dynamische oplossing zonder gebruik van VBA. Op dit moment ben ik redelijk handig geworden met het gebruik van Dynamische matrix formules en wil ik dit naar een volgend niveau brengen, maar ik weet niet of dit reeds mogelijk is en dat ik een onjuiste syntax gebruik of dat dit simpelweg nog niet mogelijk is.
Beschrijving casus
Op het moment dat ik een cellenbereik heb van bijvoorbeeld cel A1
5 en ik geef dit een naam, dan wordt dit ook automatisch gezien als een matrix. Stel ik noem deze matrix "Matrix1" en ik zou in cel E1 typen:
Code:
=Matrix1Wens
Wat ik eigenlijk zou willen doen, is die 2 matrices samenvoegen tot 1 matrix, maar welke Syntax gebruik ik dan? En is het mogelijk om Matrices samen te voegen die wel dezelfde breedte, maar niet dezelfde hoogte hebben? Logischerwijs zou ik willen zien dat als ik dit intyp in cel E1:
Code:
=Matrix1;Matrix2Toepassing achtergrond vraag
Ik heb van meerdere entiteiten saldibalansen en die hebben dezelfde lay-out. Het probleem is dat deze nog wel eens willen veranderen, bijvoorbeeld als er een rekening wordt toegevoegd of als een waarde verandert. De breedte verandert niet, maar de hoogte zou kunnen veranderen en is niet in elke entiteit hetzelfde.
Wat ik uiteindelijk wil doen, is in 1 tabblad een tabel opnemen met op de horizontale as ofwel de kolommen, de namen van de entiteiten. Op de verticale as, wil ik in kolom A, de grootboekrekeningen en in kolom B, de Grootboekomschrijvingen opnemen. Het kan zo zijn dat in Entiteit 1 en in Entiteit 2, dezelfde Grootboekrekening dezelfde omschrijving heeft, maar het kan ook zo zijn, dat een Grootboekrekening een andere omschrijving heeft. De unieke zoeksleutel moet de combinatie van Grootboekrekening+Grootboekomschrijving worden, zonder deze in 1 kolom samengevoegd te hebben.
Ik wil deze tabel zo bouwen, dat deze dynamisch gevormd wordt, zodat ik geen extra werkzaamheden heb, als ik de brontabel van een entiteit vernieuw. Als er een nieuwe grootboekrekening is, moet dit ook automatisch in de geconsolideerde tabel verschijnen, nadat ik mijn nieuwe saldibalans over de oude heen heb geplakt.
Mijn stappenplan + koppeling aan wens
- Eerst definieer ik voor elke tabel een matrix, waarbij ik gebruikmaak van de functie VERSCHUIVING of in het Engels OFFSET. Deze heeft een vaste beginpositie en een vaste breedte, maar de hoogte wordt bepaald door het aantal gevulde cellen in kolom A met behulp van de functie AANTALARG of in het Engels COUNTA;
- In een nieuw tabblad, zet ik deze matrices onder elkaar door ze op te roepen (dit is dus het stukje waar mijn wens komt kijken);
- Vervolgens ga ik in een ander tabblad met behulp van de formule UNIEK gelet op de combinatie Grootboekrekening en Grootboekomschrijving een zogenaamd geconsolideerd rekeningschema maken;
- Vervolgens ga ik op de horizontale as, de bijbehorende waarden van de verschillende entiteiten opzoeken, doordat ik de kolomnamen gelijkzet aan de namen van de achterliggende tabbladen. Met de functie INDIRECT, kan ik dan vervolgens met 1 formule met INDEX + VERGELIJKEN de juiste waarde ophalen en wordt het hierdoor mogelijk om waarden tussen verschillende entiteiten met elkaar te vergelijken en bij elkaar op te tellen.
Mijn specifieke vraag / verzoek voor dit forum
Hopelijk heb ik mijn probleemstelling juist geformuleerd en is het probleem helder en verwacht ik niet dat een voorbeeldbestand noodzakelijk is (ik zou ook niet weten wat ik dan zou moeten opnemen). Maar ik zou jullie graag willen vragen of jullie weten of dat mijn Wens mogelijk is en wat daarvoor de juiste syntax is. Mocht dit niet mogelijk zijn, dan ben ik wel benieuwd of er een alternatieve manier is waarop ik het beoogde eindproduct kan realiseren, waarbij het dus Dynamisch moet zijn. Ik heb ook al gekeken naar gebruik van PowerPivot, maar daar kom ik ook niet uit.
Waarom Matrices samenvoegen?
Ik kan natuurlijk ook voor stap 2 en 3, bijvoorbeeld in cel A1, Matrix1 oproepen en dan in de eerste cel aangrenzend onder Matrix1, Matrix2 oproepen. Probleem is echter, als in het brontabblad de hoogte wijzigt, er verdwijnt een grootboekrekening, of er komt een grootboekrekening bij, dan zal in het eerste geval, een lege regels ontstaan tussen Matrix1 en Matrix2, waardoor bij stap 3 bijvoorbeeld niet alle regels worden beoordeeld, omdat de UNIEK formule verwijst naar die gecombineerde matrix, waarvan de hoogte bepaald wordt, door het aantal gevulde regels. Als er tussen Matrix 1 en 2 een gat zit, dan worden de laatste regels van de laatste matrix ter grootte van het gat niet meegenomen.
In het omgekeerde geval, dus er komen regels bij in bijvoorbeeld Matrix1, dan ontstaat er een #Spill error, want in de uitgangssituatie was Matrix1 5 regels en begon Matrix2 op regel 6. Op moment dat er bij Matrix1 2 regels bijkomen, zou Matrix2 op regel 8 moeten beginnen, maar deze begon op regel 6, dus geeft Matrix1, een #spill error, omdat ie gegevens wil tonen over de eerste 7 regels, maar op regel 6 een overloop bestaat.
Waarom geen VBA?
Met VBA zou dit eenvoudig op te lossen zijn, maar dit is tijdrovend en heeft als gevolg dat ik of mijn medegebruikers moet gaan opvoeden, of dat ik het bestand tot op de nok moet beveiligen, dat mijn medegebruikers het niet stuk kunnen maken. Plus dat ik nu specifiek voor 1 case het voordeel van matrices combineren opnoem, maar er zijn veel meer toepassingen te bedenken en als dit werkt, scheelt dit een hoop tijd aan VBA bestandjes maken.
Tot slot
Kom alsjeblieft niet alsnog met een VBA oplossing, want dat zou ik zelf ook kunnen. Het meedenken wordt gewaardeerd, maar bespaar jezelf dan de moeite. Ik ben eigenlijk alleen op zoek naar antwoorden in de geest van: wat jij wil kan niet, of dit doe je met de volgende syntax of een mooie link naar een powerpivot oplossing o.i.d.
In ieder geval, mijn dank is groot aan degene die met de oplossing komt. In ieder geval alvast bedankt voor het meedenken.
