• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Formules aan de hand van draaitabel

Status
Niet open voor verdere reacties.
J

JMatrix

Gebruiker
Lid geworden
25 nov 2008
Berichten
87
Hallo,

Ik ben momenteel aan het stoeien met mijn draaitabel en ik heb hierover een vraag. Hopelijk kunnen jullie mij hierbij helpen.

Als ik mijn draaitabel bekijk lopen de betreffende kolommen daarvan bijvoorbeeld van 'kolom A' tot en met 'kolom KK'. Nu begin ik in de volgende kolom (dus kolom KL) met een handmatige formule gebaseerd op de waardes zoals deze in de draaitabel staan. Echter, wanneer ik de rapportfilter toepas zal het aantal kolommen gaan afnemen en/of toenemen waardoor mijn formule niet meer zal functioneren; hierbij kan Excel de vraag-melding geven of velden overschreven moeten worden.

Is er een manier hoe dit valt de omzeilen?

Alvast bedankt!
 
Het plaatsen van een voorbeeld zal inderdaad het een en ander verduidelijken, mijn excuses voor het ontbreken ervan.

Als bijlage heb ik een (ingekorte) versie van mijn model weergegeven. In werkelijkheid is het aantal toegepaste bedrijven vele malen groter en is het aantal kolommen eveneens veel groter kwa omgang.

Het voorbeeldbestand heeft twee tabbladen: een tabblad is gevuld met de basisdata, het tweede tabblad heeft de feitelijke draaitabel. In het draaitabel zijn de 'kolomlabels' gevuld met (segment)coderingen, welke gegroepeerd zijn op basis van hun eerste twee voorloopcijfers. De 'rijlabels' zijn de jaren en de 'waarden' de omzetcijfers, afgesplitst per jaar en per segmentcode.

In kolom 'T' heb ik een voorbeeld matrix-berekening toegevoegd. Dit betreft een simpele berekening die met de huidige, volledige positie van de draaitabel geen problemen oplevert. Echter, wanneer ik de rapportfilter ga toepassen en besluit om de gegevens van slechts 1 bedrijf op te vragen, dan gaat het mis. Stel, we besluiten enkel op bedrijf B te filteren (CONAME). De draaitabel geeft dan vier segmenten op hetgeen correct is. Echter, de berekening in kolom 'T' klopt dan niet meer.

Ik heb gestoeid met het werken met de Excel-functie 'draaitabel.ophalen', alleen kom ik hier niet verder mee.

Iemand die aan de hand van deze toelichting mij op weg kan helpen?

Alvast bedankt!
 

Bijlagen

Hallo JMatrix,

Zet in de formule in T7 de volgende formule
=SOM(ALS(ISGETAL(B7:O7);(B7:O7);0)/INDEX(B7:S7;VERGELIJKEN("Eindtotaal";$B$5:$S$5;0))^2)
als matrix formule.
 
Laatst bewerkt:
Hallo JMatrix,

Ik kom tot de ontdekking dat ik nog een kleinigheidje over het hoofd heb gezien.
Je moet devolgende formule als matixformule opnemen:
=SOM(ALS(ISGETAL(B7:S7);(B7:S7);0)/INDEX(B7:S7;VERGELIJKEN("Eindtotaal";$B$5:$S$5;0))^2)/2
Als je de zaak bekijkt zie je dat ik de letter O heb vervangen door de letter S en het totaal nog een keer deel door 2.
Dit wordt veroorzaakt doordat je bij een gefilterde lijst de eindtotaal kolom zou meetellen, waardoor de berekende waarde het dubbele zou zijn van wat de werkelijkheid is. Dat is nu gecorrigeerd.
 
Bedankt voor de reactie. Ik ga hier eens meer verder werken. Naast door door mijn voorgestelde matrix-formule heb ik nog meerdere (matrix)formules welke ik wil toepassen. Ik ga kijken of ik hier zelf uit ga komen, anders laat ik het hier even weten.
 
Hallo Jmatrix,

Vandaag, op de terugweg van een artsenbezoek, realiseerde ik mij dat ik mogelijk in mijn laatste formule toch nog een foutje heb staan. De factor /2 zou -1 moeten zijn.
Controleer dit even met een voorbeeldje.
 
Hallo Thoralf,

Waar je niet meer bezig bent na een dokterbezoek ;). Maar ik heb het even gecontroleerd en volgens mij klopt het gewoon. Ik heb zowel mijn formule als de jouwe even uitgeprobeerd en dit geeft dezelfde uitkomst.
 
Ja als je een uur onderweg bent en het is niet al te druk onderweg dan willen je gedachten wel eens verschillende kanten op gaan.
Bedankt overigens voor je antwoord.
Mocht je verdere vragen hebben: kom er gerust mee, eventueel kan dat ook rechtstreeks.
 
Hallo JMatrix,

Jouw werkmap liet mij toch nog niet los. Ik heb jouw formule eens goed bekeken en vergeleken met de definitie zoals die in Wikipedia te vinden is. Ik constateer dan dat jij in jouw formule een fout maakt: je kwadrateert wel de noemer, maar niet de teller van de breuk die het relatieve aandeel weergeeft. In bijgevoegde werkmap heb ik naast jouw formule een kolom opgenomen zoals volgens mij jouw formule had moeten zijn en daarnaast de formule van mij in het geval je filtert.
 

Bijlagen

Hallo Thoralf,

Bedankt voor je reactie! Ik heb het even nagekeken en volgens mij heb je gelijk; ik heb ergens een foutje gemaakt. Bedankt voor de oplettendheid. Ik vond het al vreemd gezien de vreemde uitkomsten welke ik kreeg.

Het betreft overigens de zogenaamde Herfindahl-formule, ik weet niet of dit je wat zegt?
 
Ik kwam de kop tegen in jouw werkmap en ik vroeg mij af wat dat precies voor index was en heb dus even op Google gekeken en daar kwam Wikipedia bovendrijven. Geeft een aardige beschrijving.
Succes met je sheet.
 
Hallo Thoralf en/of anderen,

Ik heb mijn model (zie nieuwe bijlage) aangepast naar een nieuwe berekeningsmethode. Hierbij een tweetal vragen.

Zoals te zien gaan de berekeningen in kolom AB vanaf cel AB14 fout en verschijnt er een '#naam?'-foutmelding. Aangezien de betreffende formule is doorgetrokken vanaf cel AB7 is het mij niet duidelijk waarom deze melding verschijnt vanaf cel AB14.

Tweede punt is gelijk aan de vraag hierboven. Hoe kan ik de drie formules in de kolommen AA, AB en AC aanpassen zodat deze toepasbaar blijven als ik met rapportfilters ga werken?

Alvast bedankt.
 

Bijlagen

De foutmelding komt omdat in de formule ET staat en het geen verwijzing is naar een cel.

Code: 
=SOM(ALS(B14:D14>1;(B14:D14/E14)*LN(1/(B14:D14/E14))))*(E14/Y14)+SOM(ALS(F14:G14>1;(F14:G14/H14)*LN(1/(F14:G14/H14))))*(H14/Y14)+SOM(ALS(I14>1;(I14/J14)*LN(1/(I14/J14))))*(J14/Y14)+SOM(ALS(K14>1;(K14/L14)*LN(1/(K14/L14))))*(L14/Y14)+SOM(ALS(M14>1;(M14/N14)*LN(1/(M14/N14))))*(N14/Y14)+SOM(ALS(O14:Q14>1;(O14:Q14/R14)*LN(1/(O14:Q14/R14))))*(R14/Y14)+SOM(ALS(S14>1;(S14/T14)*LN(1/(S14/[B][COLOR="Red"]ET[/COLOR][/B]))))*(T14/Y14)+SOM(ALS(U14>1;(U14/V14)*LN(1/(U14/V14))))*(V14/Y14)+SOM(ALS(W14>1;(W14/X14)*LN(1/(W14/X14))))*(X14/Y14)

Met vriendelijke groet,


Roncancio
 
Bedankt! Dat was het inderdaad. Blijkbaar kwam de fout pas vanaf rij 14 naar voren omdat toen kolom T zijn waarden kreeg.

Hopelijk kan iemand mij nog verder helpen met het tweede punt.
 
Hallo JMatrix,

Als ik jouw formules bekijk dan valt mij iets op:
Totaal is gelijk aan Related plus Unrelated, dit lijkt nog niet zo vreemd, maar wel verbaast het mij dat de formules bij Totaal en Related matrix-formules zijn en bij Unrelated niet. Daarnaast verbaast het mij dat je niet een som-formule gebruikt voor totaal: som Related plus Unrelated of Related of Unrelated uitrekenen als verschil van Totaal en de andere.
Op deze manier ben je iets efficiënter en is het geheel iets overzichtelijker.
Related als verschil van Totaal en Unrelated lijkt mij dan de meest voor de hand liggende oplossing.

Voor de concrete oplossing van jouw tweede vraag heb ik nog zo snel geen oplossing. Mijn eerste gedachten gaan uit naar het gebruik van een aantal hulpkolommen: Per groep 2 van de 3 waardes bepalen (Totaal en Unrelated) en tenslotte optellen en de derde waarde bepalen. Ik heb nog even geen tijd om dit idee wat verder uit te werken om zodoende de realiteitszin ervan te kunnen beoordelen. Pas volgende week heb ik daar weer de tijd voor. Mogelijk heb jij hier iets aan of kan iemand anders die dit ook zo ziet zitten er wat verder mee gaan.
 
Hallo Thoralf,

Bedankt voor je reactie. De formules zoals ze nu worden gebruikt zijn het resultaat van een eerder topic wat hier is behandeld. De reden om in een (1) geval juist geen gebruik te maken van een matrix-formule werd toen aangedragen.

Natuurlijk is het makkelijker om Unrelated of Related als verschil van Totaal te berekenen, maar door de huidige opzet creeer ik voor mezelf een bepaalde controle, welke ik anders op zou geven denk ik.

Ik heb wel een idee waar je heen wilt gaan, het toepassen van berekende velden. Klopt dat? Ik zal er eens mee gaan stoeien, maar ik kijk ook graag uit naar jouw methode.

Alvast bedankt.
 
Eerste probleem waar ik nu op stuit is dat het me niet lukt om per groep een berekend veld aan te maken, dit gebeurt nu voor iedere kolom. Iemand een aanwijzing hoe dit te op te lossen?

Overigens post ik even een aanvullend bestand. Hierin wordt al gebruik gemaakt van een extra kolom om de berekeningen met een tussenstap te verrichten. Het gaat dan om het bovenste voorbeeld; het onderste voorbeeld is de huidige situatie, dus zonder tussenstap.
 

Bijlagen

Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan