Toevoegen berekend veld/item

[JMatrix]

Hallo,

Al een aantal keer heb ik hier mijn vragen omtrent mijn Excel-modellen neergezet, waarbij ik meerdere malen goede hulp heb gekregen. Helaas lukt het mij nog niet om mijn draaitabel goed in te richten, dus nogmaals een vraag. Als bijlage heb ik een voorbeeld bijgesloten om mijn probleem te verduidelijken.

In de draaitabel staan een drietal bedrijven vermeldt, gerangschikt per jaar en per gerapporteerd segment. Nu wil ik graag dat er voor ieder bedrijf, per jaar een berekening wordt uitgevoerd met een bepaalde formule afhankelijk van de volgende waarden: sale per segment en totale sale (per jaar). De totale sale is dan de individuele sales per segment op geteld. De formule is dan als volgt: {SOM((('Sales')/'TotSale')^2)} emn zou dan een matrix-vorm moeten aannemen om ervoor te zorgen dat alle sales worden meegenomen.

Als ik bijvoorbeeld kijk naar Bedrijf B, dan meldt deze een totale Sale van 3104.0 in 1984, verdeeld over twee segmenten. In de ideale situatie zou in de rij hierboven de berekening moeten plaatsvinden gebaseerd op deze twee waarden en dat dan voor ieder jaar.

Is er misschien iemand die mij hiermee op weg kan helpen? Ik heb al liggen te stoeien met berekende velden en berekende items, maar dit brengt tot dusver weinig succes.

Alvast bedankt!

 

[Thoralf]

Hallo JMatrix,

zou je even kunnen kijken of je het voorbeeldbestand wel goed hebt gekoppeld. Ik kan het bij mij niet bekijken/downloaden.
Eventueel kun je mij het bestand ook rechtstreeks sturen: thoralf at hetepost dot com

 

[Thoralf]

Hallo JMatrix,

Ik heb jouw bestand niet kunnen downloaden, maar uit jouw eerdere vragen meen ik het bestand te herkennen.
Als je een segment aanduidt met een 4-cijferige code in jouw draaitabel, dan had je de formule bijna voor het grijpen:
=SOM(ALS(((B7:Z7)*($B$6:$Z$6))>0;((B7:Z7)/((SOM(B7:Z7))/3))^2;0))
Inbrengen als matrix-formule.

Als je de ^2 vervangt door *LN(1/((B7:Z7)/((SOM(B7:Z7))/3))) dan zul je hem ongetwijfeld herkennen.

Aanvulling:

Mocht je een segment aanduiden met de 2-cijferige code, dan wordft de formule
=SOM(ALS((B7:Z7)>0;((B7:Z7)/((SOM(B7:Z7))/3))^2;0))-SOM(ALS(((B7:Z7)*($B$6:$Z$6))>0;((B7:Z7)/((SOM(B7:Z7))/3))^2;0))-1
Ook weer een matrix-formule.

Wil je de gegevens per bedrijf zien, dan selecteer je in de draaitabel simpel dat bedrijf. De formules werken ook voor een combinatie van bedrijven, de combinatie wordt als 1 bedrijf gezien.

 

[JMatrix]

Ik heb het voorbeeldbestand nogmaals gepost, hopelijk gaat het nu wel goed.

Het is dus de bedoeling dat de formule in het berekende veld (Formule1) wordt vermeldt. Dus bijvoorbeeld in de cellen A8 en A14.

Om de een of andere manier krijg ik dit niet voor elkaar.

Alvast bedankt.

 

[Thoralf]

Hallo JMatrix,

IK heb nog eens even gekeken naar Berekend veld en Berekend item en kom tot de conclusie dat je daar niet of misschien heel moeilijk mee uit de voeten kunt. Dit wordt veroorzaakt door het feit dat er in de formules geen verwijzingen kunnen staan. Dat betekent zo wie zo al dat je dus geen functies kunt gebruiken. Verder kun je in de formules enkel veldnamen gebruiken, getallen, haakjes en operatoren (+ - / * ^).

Om jouw gegevns toch te kunnen berekenen ben ik weer terug geaan naar je oorspronkelijke draaitabel, echter wel met een kleine aanpassing: Ik heb de CONAME als rijlabel opgenomen. Vervolgens ben ik weer horizontaal gaan werken.
Een en ander kun je terugvinden op het blad Draaitabel-2.
Deze indeling, een blad Draaitabel en een blad Draaitabel-2 geeft je als voordeel dat je op het blad Draaitabel de gegevens van een enkel bedrijf kunt zien, of een cluster van bedrijven als geheel en op het andere blad een groep van bedrijven in hun onderlinge verband.

Voorlopig zie ik zo snel geen andere oplossing voor het boven tafel halen van jouw getallen.

 

[JMatrix]

Hallo Thoralf,

Ik heb ook met jouw opbouw zitten te stoeien, alleen heb ik tot nu toen ervoor gekozen om de rijlabels andersom op te zetten, dus per jaar en daar binnen in per bedrijf.

De reden hiervoor is dat ik makkelijker jaargemiddelden kan uitrekenen naar mijn mening. Is dit echter aan de hand van jouw opzet ook makkelijk te bepalen? Dus met andere woorden dat je een formule aangeeft waarbij je de gemiddelen van 'Total' voor het jaar '1984' betreffende alle bedrijven bepaalt.

 

[Thoralf]

Hallo JMatrix,

Voor alle bedrijven (of cluster van bedrijven) kijk je op blad Draaitabel, voor de situatie per bedrijf kijk je op het blad Draaitabel-2.

 

[Thoralf]

Hallo RedEvil,

Prima getallen, maar dan wel de vraag hoe dit gerealiseerd? Ik zie geen brongegevens en evenmin kan ik de daarop gebaseerde draaitabel vinden die aan deze getallen ten grondslag dient te liggen.
De kracht van draaitabellen is de mogelijkheid on the fly een kruistabel te realiseren die gebaseerd is op door jou op dat moment te bepalen argumenten. Ik zie in jouw oplossing geen mogelijkheid de argumenten aan te passen en gebaseerd op die aanpassing de juiste getallen te genereren.

 

[Thoralf]

Hallo RedEvil,

Maar dan ben ik wel benieuwd hoe je dit via een draaitabel hebt gerealiseerd. Ik geef mijn antwoorden vanuit mijn beperkte kennis, maar deze directe oplossing had ik zo nog niet gezien. Ik leer ook graag nog wat, ondanks mijn leeftijd.

 

[Haije]

Lijkt dit er op?

allen,

ik weet niet of dit de bedoeling is, maar kijk eens in de bijlage

 

[JMatrix]

Hallo allemaal,

Excuses voor het uitblijven van een reactie; ik ben al aan de slag gegaan met het model wat ik met hulp van Thoralf tot mijn beschikking had.

Wat betreft het model van RedEvil ben ik wel benieuwd naar zijn originele draaitabel/berekening, omdat ik (tot dusver) concludeer dat het bedrijfstotalen per jaar niet overeenkomen met de som van de individuele segmenten. Ik denk echter dat ik een berekening over het hoofd zie, maar ik ben benieuwd naar de achterliggende berekeningen want het ziet er interessant uit.

Wat betreft Haije denk ik niet dat hetgeen wat hij voordraagt mijn oplossing is, maar toch bedankt hiervoor.

 

[Thoralf]

Hallo JMatrix,

De gehele discussie overziend ben ik tot de conclusie gekomen dat ik wel de onderliggende waardes voor de getallen in jouw regel Formule1 heb geproduceerd, maar net niet de laatste slag heb gemaakt de optelling.
In afwachting van de oplossing van RedEvil , direct met de draaitabel ben ik nog weer eens aan het stoeien gegaan en heb met twee hulpreeksen jouw voorbeeldbestand kunnen aanvullen met naar ik aanneem de correcte getallen.
De formules zijn knap ingewikkeld geworden om dat in één deel een matrix-constructie nodig was en in het andere deel dat niet kon, waardoor er een andere oplossing gezocht moest worden (vandaar de twee hulpreeksen). Anderzijds zijn de formules zo ingewikkeld omdat ze zich automatisch moeten richten op de veranderen layout van de draaitabel.
Wil je het sheet aanpassen aan een langere draaitabel, dan zul je de gele cellen moeten aanpassen en de groene cel naar beneden door trekken.
Let op: In de formule in B52 staat ongeveer in het midden het getal 20. Dit is het kolomnummer van kolom T. Als je dus kolom T verplaatst moet je dus ook dit getal overeenkomstig aanpassen.

 

[Thoralf]

Hallo Jmatrix,

Nog een keer terugkomend op mijn laatste bijdrage:
Als je enkel geinteresserd bent in de totalen (jouw Formule1-regel) per bedrijf, dan kan het overzicht duidelijk korter.
Op het blad Draaitabel heb ik dat in de regels 40 tot en met 44 gerealiseerd.
Regel 40 had ik graag achterwege gelaten (is het zelfde als regel 50), maar als ik de formule van bijv B40 opneem in de formule van B42 dan komt er een fout-melding, waarvan ik de oorzaak nog niet heb achterhaalt.

Tevens heb ik de formule van B52 wat aangepast, waardoor bij een kortere lijst van bedrijven (door het aan- en/of uitvinken van één of meer bedrijven in de draaitabel) er geen foutboodschappen meer ontstaan in de kolommen B en verder.

Ook hier weer de formules in de gele cellen aanpassen (de verwijzingen naar B26 en B40, als ook de verwijzing naar A26 en A42, idem voor B50 en B52).

Ik ben verder wel benieuwd of de getallen nu voldoen aan je verwachting.

 

[JMatrix]

Hallo Thoralf,

De nieuwe opzet, gebaseerd op totalen, is niet hetgeen wat ik direct zoek. Misschien herinner je je de andere, gerelateerde vragen betreffende 'Total', 'Related' en 'Unrelated' nog waarbij 'groepswaarden' dus van belang zijn.

De opzet/uitkomsten moeten dus gelijk zijn aan de methode welke Thoralf en ik eerder hebben besproken, alleen wilde ik de draaitabel hierbij een andere opzet geven.

Overigens ben ik al aan de slag gegaan met Thoralf's eerder voorgestelde methode en heb hierbij mijn eigen draai aan gegeven, welke misschien wat omslachtig is. Ik was namelijk tot de conclusie gekomen dat de 'Totaal'-lijst van alle bedrijven niet gebruikt mocht worden omdat dit een vertekend beeld geeft van alle onderlinge segmenten in mijn sample; ik kreeg hierbij onjuiste uitkomsten. De juiste toepassing bleek om voor ieder bedrijf indivueel ieder segment te bepalen, om vervolgens van alle segmenten (dus niet bedrijven) een gemiddelde te nemen.

Indien er interesse is kan ik mijn model wel even, aangepast, online zetten.

 

[Thoralf]

Hallo JMatrix,

Natuurlijk ben ik geinteresseerd in het model , en ik kijk er dan ook met belangstelling naar uit. Mogelijk is dan aan de hand ervan nog enige vereenvoudiging te realiseren dan wel nog wat vraagpunten op te lossen.

 

[Thoralf]

Hallo JMatrix,

Als ik je laatste bericht goed interpreteer dan zou het blad Draaitabel-2 in de bijlage een hele stap in de richting zijn.

 

[JMatrix]

Hoi Thoralf,

Ik wil graag een voorbeeld posten, maar ik word belemmerd in door de toegestane 100KB.

 

[Thoralf]

Hallo JMatrix,

Je zou het kunnen zippen. Dan wordt het in ieder geval een stuk kleiner en in het over grote deel van de gevallen is het dan passend.

 

[JMatrix]

Het (in mijn geval) rarren is gelukt, bedankt voor de tip. Hierbij het betreffende bestand. In werkelijkheid is de draaitabel vele malen groter, evenals mijn eigen aangemaakte tabel met de berekeningen welke met een aantal jaren doorloopt.

Met dit model heb ik de resultaten kunnen bepalen welke ik moest hebben.

 

[Thoralf]

Hallo JMatrix,

Als ik jouw model bekijk dan vermoed ik dat je een denkfout maakt, waardoor jouw getallen niet correct zijn. Dit geldt dan met name voor de getallen in de regel totals op rij 17.
De getallen op de rijen 7 tot en met 13 lijken mij theoretisch correct, maar je mag mijns inziens voor het totaal dit niet optellen. In de rijen 7 t/m 13 werk je per bedrijf en is de markt beperkt tot de markt van het bedrijf. In de totaal-regel is de markt echter het totaal van de 3 bedrijven en als je dan de entropie wilt berekenen dan moet je ook uitgaan van de totale markt. Dit betekent dat je een aanpassing moet doen: zie daartoe de som-delen in de formules op rij 22 t/m 24 in vergelijking tot die op rij 7 t/m 9.
Op deze wijze komt dan mijns inziens de onderlinge verhouding en interactie van de verschillende bedrijven in de totale markt tot uiting.
Ik vindt het dan ook niet verrassend dat de optelling in P26 gelijk is aan AE6 in Model2JMatrix (waar de entropy berekend wordt voor de groep van de drie bedrijven, zonder dat er per bedrijf een entropie wordt berekend, maar wel uitgaande van totalen van de 3 bedrijven per groep).

Met betrekking tot de vraagstelling in de lopende discussie zie ik even nog geen verbinding.