Linken naar een lijst

[Mulderalexander]

Hi All,

Ik hoop dat iemand me met het volgende probleem kan helpen.
Helaas kan ik geen voorbeeld hier posten omdat er teveel vertrouwelijke informatie in staat...

Ik heb een sheet dat onderverdeeld kan worden in verschillende groepen door middel van een lijst. Ik heb x aantal kantoren die verdeeld zijn over verschillende landen en als ik dan bijvoorbeeld in de lijst Nederland aan klik dan krijg ik een verkleind overzicht van onze kantoren in Nederland met verschillende scores. Onze klanten geven een score van hoe tevreden ze met onze hulp waren. Hetzelfde geldt voor al onze kantoren in Belgie, Frankrijk, Duitsland, etc.

Deze scores worden opgeteld en komen in een tabel te staan. Deze score die in het tabel staat wordt naar een ander workbook gelinked en daar wordt dan een grafiek mee gemaakt. Het is nu echter zo dat ik een grafiek per land maak en als ik de tabel in het tweede workbook link aan de tabel in de eerste workbook dan verandert de score als ik in de lijst van land wissel en dit wil ik dus net niet.

Hoe kan ik er voor zorgen dat de scoren land gebonden blijven en niet meeveranderen?

Hoop dat het duidelijk is. Zo niet laat het dan even weten.

En kan iemand me vertellen wat deze formule is? Ik snap 'em namelijk niet helemaal....

=ROUND(SUMPRODUCT(B$13:B$263,F$13:F$263)/SUM(F$13:F$263),2)



Gr

 

[MauriceSmit]

Ben ik weer :love:

de laatste lijkt mij een berekening voor een gewogen gemiddelde.. Die round eromheen snap ik niet, want die kun je afvangen met je celeigenschappen.

Die eerste vraag met een voorbeeldje kom ik een heel eind

 

[Roncancio]

Bij celeigenschappen zijn de cijfers achter het afgeronde decimaal nog in de cel aanwezig.
Met ROUND (in het Nederlands Afronden) wordt een getal afgerond op het aantal aangegeven decimalen.
De getallen achter de decimaal worden verwijderd.

Voorbeeld:

=Afronden(4,5666; 1)
Resultaat 4,5

Vermenigvuldig je dit resultaat met bijvoorbeeld 2 dan is de uitkomst 9.
Zou je kijken wat er achter de komma bij 9 staat dan zijn dat 0'en.

Met vriendelijke groet,


Roncancio

 

[sickofitall]

Hi All,

Ik hoop dat iemand me met het volgende probleem kan helpen.
Helaas kan ik geen voorbeeld hier posten omdat er teveel vertrouwelijke informatie in staat...

Ik heb een sheet dat onderverdeeld kan worden in verschillende groepen door middel van een lijst. Ik heb x aantal kantoren die verdeeld zijn over verschillende landen en als ik dan bijvoorbeeld in de lijst Nederland aan klik dan krijg ik een verkleind overzicht van onze kantoren in Nederland met verschillende scores. Onze klanten geven een score van hoe tevreden ze met onze hulp waren. Hetzelfde geldt voor al onze kantoren in Belgie, Frankrijk, Duitsland, etc.

Deze scores worden opgeteld en komen in een tabel te staan. Deze score die in het tabel staat wordt naar een ander workbook gelinked en daar wordt dan een grafiek mee gemaakt. Het is nu echter zo dat ik een grafiek per land maak en als ik de tabel in het tweede workbook link aan de tabel in de eerste workbook dan verandert de score als ik in de lijst van land wissel en dit wil ik dus net niet.

Hoe kan ik er voor zorgen dat de scoren land gebonden blijven en niet meeveranderen?

Hoop dat het duidelijk is. Zo niet laat het dan even weten.

En kan iemand me vertellen wat deze formule is? Ik snap 'em namelijk niet helemaal....

=ROUND(SUMPRODUCT(B$13:B$263,F$13:F$263)/SUM(F$13:F$263),2)

Gr

Met een voorbeeldbestand zal je sneller antwoord krijgen:
- als je namen hebt van kantoren, verander die in "kantoor nr1", "kantoor nr 2", enz...
- als er in je bestand dingen staan zoals 'Omzet', 'Kosten'... Verander deze dan in "Parameter nr1", "Parameter nr2" enz...

Op die manier kunnen wij je vraag oplossen met een, in ons ogen, nutteloos bestand, aangezien we amper weten over wie of wat het gaat.

 

[Mulderalexander]

Hey Maurice,

Ik heb een pivot table bijgevoegd waar ik een beetje problemen mee heb. Ik moet deze pivot table maken om het probleem uit te leggen maar krijg het niet voor elkaar om de data erin te krijgen die ik wil.

Als je in Sheet 1 kijkt zie je alle data. Deze probeer ik dus in Sheet 4 in de Pivot table te krijgen. Ik heb alle dingen erin behalve de data zelf dus, haha. Ik wil in de COLUMN lijst bijvoorbeeld OSAT MTD en OSAT MTD LY en ARRIVAL COMPOSITE en SERVICE COMPOSITE etc etc en daaronder per kantoor de verschillende scores, maar ik krijg nu alleen of de SUM of de COUNT en krijg het niet voor elkaar om de precieze data erin te krijgen. Als je me hier eerst mee kan helpen dan kan ik mn probleem wat duidelijker uitleggen (mn eerste originele vraag).

Hopelijk kan je me vandaag WEER uit de brand helpen!

Gr

 

[MauriceSmit]

Wat voor resultaten wou je precies in de pivot hebben staan? Je 'aggregatie' niveau is inderdaad op dit moment "Kantoor", maar je hebt voor bijvoorbeeld kantoorA een tweetal scores. Wil je het gemiddelde als resultaat of iets anders?

 

[sickofitall]

als je de som gebruikt, krijg je de scores, maar dan opgeteld, dus kantoor A krijgt de score van German en Italy opgeteld. Als je de waarden wil zien van je sheet 1, zul je niet enkel per kantoor, maar ook per land moeten werken.

zie bijlage...

 

[Mulderalexander]

Wat voor resultaten wou je precies in de pivot hebben staan? Je 'aggregatie' niveau is inderdaad op dit moment "Kantoor", maar je hebt voor bijvoorbeeld kantoorA een tweetal scores. Wil je het gemiddelde als resultaat of iets anders?

Sorry inderdaad een klein foutje van mijn kant. Ik heb het nu verandert. Dus nu als je alles op ALL houdt dan krijg je alle verschillende kantoren en dan wil ik daar achter dus de scores per kantoor per categorie. En dan als ik in mn pivotlijst onder BRAND een ander brand kies dat ik dan alleen de scores per kantoor van die brand te zien krijg....

 

[MauriceSmit]

Ik ben nog wel een beetje zoekende, want je had de oplossing al voor 99% geupload, volgens mij

 

[Mulderalexander]

Ik ben nog wel een beetje zoekende, want je had de oplossing al voor 99% geupload, volgens mij

Ja 99% maar niet 100%

Hoe heb je die DATA tabel gekregen en OSAT scores daarin? Als je me dat meteen kan vertellen zou dat super zijn :thumb:

Maar dan terug naar mn oorspronkelijke vraag

Ik heb nu in SHEET 4 dus de Pivot table in nu links boven in een tabel die de gemiddelde score laat zien van een kantoor afhankelijk van de keuze die je boven in maakt i.e. Brand, Land, Region, Country, etc.

In SHEET 2 wil ik een overzicht maken van de scores per brand, maar ik wil dat de scores automatisch mee veranderen als ik de data in SHEET 1 verander. Wat er nu gebeurt is als ik in SHEET 2 cel B3 link aan cel E6 in SHEET 4 en ik verander de keuze van bijvoorbeeld BRAND van FPbS naar STR dan verandert mn data in SHEET 2 in de tabel van FPbS (waar ik het aan gelinkt had)ook, maar dit wil ik dus niet... Ik wil dat de data van FPbS hetzelfde blijft maar ik wil niet alleen copy paste doen omdat ik anders elke maand alles moet copy-pasten.

Ik hoop dat je snapt wat ik bedoel...

Alvast bedankt en AL bedankt voor al jullie hulp

 

[MauriceSmit]

hmm, ik krijg ongeveer door wat je bedoeling is. Omtrent het toevoegen van de DATA gegevens: met welke versie van Excel werk je? Het vinden van deze gegevens is namelijk verschillend tussen Excel versies.

Daarnaast even een belangrijk punt; pivot tabellen zijn over het algemeen vrij statisch. Als de bron data wijzigt, veranderd de pivot niet (automatisch) mee. Dit is wel af te vangen met macros.

Ik heb in de bijlage de Pivot weten te omzeilen, en de berekeningen rechstreeks uit je brondata gehaald. Zo kun je de pivot gebruiken om allerlei doorsnedes te bekijken, zonder dat het andere sheet daardoor veranderd.

 

[Mulderalexander]

Maurice ziet er al echt al zo goed uit! Ik was al bang dat ik het niet met de Pivot table zou kunnen doen vanwege de beperkingen ervan. Brondata is dus de enige manier... Maar ziet er zo wel onwijs goed uit.

Ik gebruik trouwens (ik denk dat je nu een traantje gaat laten) versie 2003....

Ik denk dat ik het op deze manier ga doen nu, maar hoe krijg ik het voor elkaar om alle data die ik in de Pivot Table wil hebben, en in SHEET 2, erin?

Ik wil namelijk in mn PIVOT table de volgende info hebben staan (onder data):

LOY MTD
LOY MTD LY
LOY yoy var
SERV MTD
SERV MTD LY
SERV yoy var
PHYS MTD
PHYS MTD LY
PHYS yoy var
F&B MTD
F&B MTD LY
F&B yoy var
ARR MTD
ARR MTD LY
ARR yoy var
COUNT MTD
COUNT MTD LY

Voor de uitleg yoy var is MTD - MTD LY

En dan in SHEET 2 per BRAND in de linker Column onder OSAT LOY, PHYS, F&B en ARR en dan weer MTD, MTD LY en VAR. Nu hoef je dit echt niet te doen voor me want het is volgens mij best wat werk (alhoewel ik je natuurlijk niet tegen hou ) maar als je me zou kunnen uitleggen hoe je die data zo in de PIVOT hebt gekregen dat je de data zelf krijgt en niet de COUNT of SUM zou ik dat al ontzettend op prijs stellen.

Ik ga je niet om die hele mooie formule van SHEET 2 vragen want ik ben bang dat mn hoofd dan ontploft

Weet niet hoe ik je beter kan bedanken dan mijn oprechte dank geven hier, maar je bent echt een LIFE SAVER!

Groetjes Alexander

 

[MauriceSmit]

1. Het toevoegen van de gegevens aan 'data' zijn inderdaad de 'SUM' waarden. Maar als het aantal rows iedere mogelijkheid laat zien, zal het resultaat de SOM van 1 waarde zijn. Misschien beter om dus voor de zekerheid hier geen 'som' maar 'average' of 'gemiddelde' te zetten. Stel dat 1 kantoor toch meerdere gegevens bevat, krijg je in ieder geval geen optelling van de uitkomsten. Om extra gegevens toe te voegen, klik je op de pivot, en in de pop-up tabel "PivotData Field List" selecteer de waarde welke je wilt toevoegen. Geef echter onderin het pop-upje dat het gegeven moet worden toegevoegd aan "Data Area". Er is dan een extra kolom, die (bij default) op Count staat. Klik met de rechtermuisknop op deze nieuwe kolom, en ga naar 'field setting'. Zet de optie nu op 'average', in plaats van 'count'.

Om een kolom toe te voegen, waarbij er twee waarden van elkaar worden afgetrokken, klik je weer op je pivot tabel, en op je werkbalk "Pivottable" ga je naar het puldown menutje genaamd: "PivotTable".
Uit de drop down, kies je nu voor "Formulas" en vervolgens op "Calculated Field".
Vul de naam van de nieuwe kolom in (waarschijnlijk dus "LOY yoy var"), en je formule:
='OSAT MTD LY'- 'OSAT MTD'. In de popup "PivotData Field List" is nu (helemaal onderaan) je nieuwe veld toegevoegd. Dit kun je dus op dezelfde manier toevoegen als de reguliere kolommen.

Om toch even te kijken of je hoofd ontploft, de formule op sheet2.
=SUMPRODUCT(("OSAT " &B2=ratings)*(brands=$A2)*ratings_values)/COUNTIF(brands;$A2)

Alleerst de ranges:
'ratings' = uit je brongegevens (sheet1) de range: H1:AR1 --> kolomtitels alle waarden
'brands' = uit je brongegevens (sheet1) de range: D234 --> alle brands
'ratings_values' = uit je brongegevens (sheet1) de range: H2:AR34 --> alle waarden

voor het eerste stukje van de formule in cell B3 op sheet2 ("OSAT " &B2=ratings) staat er het volgende: "OSAT " & B2 = OSAT MTD --> Dit is een van ratings-titels, die dus overeenkomt met een van de waarden uit de range 'ratings'. Excel zal dan de volgende serie gegevens in kolomvorm onthouden:
False False False True False False False etc

Bij het tweede stukje van de formule: (brands=$A2) gebeurt hetzelfde en het resultaat is dan:
False
True
False
False
Etc.

Hier worden vervolgens de "ratings" tegen aangehouden (deze heeft evenveel rijen en kolommen als 'ratings' en 'brands') en zal als uitkomst een optelling zijn van waarden waarvoor zowel de kolom als de rij "True" is. Dit kunnen er meer dan 1 zijn, als je brands meer dan 1 rating hebben. Vandaar dat ik op het einde van de formule, de uitkomst deel door het aantal brands. Er ontstaat dus een gemiddelde rating per brand.

 

[Mulderalexander]

EDIT: Lees hieronder

 

[Mulderalexander]

Maurice of iemand anders,

Mn laatste vraag was niet zo duidelijk lees ik net. Dit komt omdat ik de formule niet helemaal begreep maar nu gelukkig iets beter...

Ik zie dat de score in SHEET2 gelinked is naar de waardes van SHEET2, maar ik moet dus dat ie de scores uit SHEET1 haalt. De originele data. Hoe link ik dus deze sheet naar de originele data sheet? En dan nog inderdaad hoe ik de formule verander om bijvoorbeeld hetzelfde te krijgen voor LOY, PHYS, ARR, F&B, SERV?

Ik wil dus in SHEET2 de tabellen, gelinked aan de data in SHEET1 en dan dus per BRAND een tabel met LOY, PHYS, ARR, F&B en SERV.

Hopelijk kan je het uitleggen..

Dank je!