[Excel] Het grote Excel vragen topic #39

quote:

Op woensdag 19 februari 2014 21:48 schreef qu63 het volgende:

[..]

Waar loop je precies in/op vast dan?

Ah, dat was precies handig te vermelden. Ik weet niet exact hoe ik moet beginnen en hoe ik het moet aanpakken.

quote:

Op dinsdag 18 februari 2014 23:20 schreef qu63 het volgende:

[..]

Ik ben bang dat dit een lesje te ver is, gezien de eerste 3 opdrachten (en Comic Sans ).

En als ik t goed begrijp gaat t alleen om t antwoord, de manierop boeit niet..

Tja, ik had niet verzonnen dat de solver gebruikt moest worden. Je kan ook heel simpel gewoon de combinaties uitschrijven, Wheat = 1, 2, 3, 4 etc.
Zeker in dit geval is dat nog sneller dan die solver dingen inrichten.

Hebben jullie ervaring met gewogen data in Excel? Ik heb een databestand wat ik omzet in percentages of gemiddelden (bijvoorbeeld aantal mannen en aantal vrouwen of de gemiddelde beoordeling). Met gemiddelden is het meenemen van het gewicht makkelijk (somproduct) maar hoe moet ik dat doen bij geslacht. Stel een vrouw heeft een weegfactor van 3, dan kan ik niet 3 x 2 (1 = man, 2 = vrouw) doen. De vrouw moet drie keer meetellen en niet haar antwoord vermenigvuldigd met drie hebben.

Antwoord x3 + Aantal x3. Je zou een hulpkolommen kunnen toevoegen (aantal + antwoord). Of je zou het wellicht met een array-formule kunnen oplossen, maar daar heb ik weinig ervaring mee.

Ik snap je oplossing niet geloof ik ...? Bij geslacht gaat het niet op een gemiddelde maar een gewogen verhouding man vrouw.

Ik snap het nu wel geloof ik.

Gewoon som.als.

Mooie oplossing gevonden:
Some unlikely uses for sumproduct

quote:

Op donderdag 20 februari 2014 22:33 schreef Z het volgende:
Mooie oplossing gevonden:
Some unlikely uses for sumproduct

De laatste paar toepassingen van SumProduct zijn wel interessant, die wist ik nog niet.
Verder snap ik nog steeds niet wat je probleem was, maar mooi dat het opgelost is

quote:

Op donderdag 20 februari 2014 22:49 schreef PAAC het volgende:

[..]

De laatste paar toepassingen van SumProduct zijn wel interessant, die wist ik nog niet.
Verder snap ik nog steeds niet wat je probleem was, maar mooi dat het opgelost is

m 2
m 3
m 5
v 3
v 5

(m = man, v = vrouw)

Het gemiddelde hiervan zou zijn (2+3+5+3+5)/5 = 3,6
Het antwoord van de vrouwen moet echter zwaarder meetellen (3x), dus wordt he: (2+3+5+3x3+3x5)/9 = 3,77

Denk ik...

quote:

Op vrijdag 21 februari 2014 08:10 schreef sylvesterrr het volgende:

[..]

m 2
m 3
m 5
v 3
v 5

(m = man, v = vrouw)

Het gemiddelde hiervan zou zijn (2+3+5+3+5)/5 = 3,6
Het antwoord van de vrouwen moet echter zwaarder meetellen (3x), dus wordt he: (2+3+5+3x3+3x5)/9 = 3,77

Denk ik...

Als dat het is doe je dat volgens mij het snelste door een hulpkolommetje te maken met =ALS(cel="M";1;3) en dan alsnog te somproducten. Ook lekker makkelijk als je de weging nog ooit wil veranderen.

Om een databestand een representatieve afspiegeling te laten zijn van een bepaalde populatie kan je achteraf de data wegen. Stel dat de verhouding man/vrouw 60% om 40% is dan kan je een weegfactor toevoegen, alle mannen krijgen een weegfactor van ongeveer 0,85 en alle vrouwen van ongeveer 1,15. Alle antwoorden van een man tellen 0,85 keer mee, alle antwoorden van een vrouw 1,15. In mijn geval zijn er 8 variabelen meegenomen in de weging.

Dus als je een grafiek man/vrouw wilt, reken je met de weegfactoren ipv de antwoorden. Alle weegfactoren van de man opgeteld en alle weegfactoren van de vrouwen. In mijn geval gold er een dubbele voorwaarde: doet boodschappen bij de AH + vrouw en doet boodschappen bij de AH + man.

Dat wordt dan zoiets:

quote:

Op vrijdag 21 februari 2014 08:36 schreef Janneke141 het volgende:

[..]

Als dat het is doe je dat volgens mij het snelste door een hulpkolommetje te maken met =ALS(cel="M";1;3) en dan alsnog te somproducten. Ook lekker makkelijk als je de weging nog ooit wil veranderen.

Dat is waar maar dat zouden dan heel veel hulpkolommen worden in dit geval. Het liefst werk ik hulpkolomloos.

quote:

Op vrijdag 21 februari 2014 08:38 schreef Z het volgende:

[..]

Dat is waar maar dat zouden dan heel veel hulpkolommen worden in dit geval. Het liefst werk ik hulpkolomloos.

Dat kan ook, maar dan worden je formules gewoon langer. Wat er in die hulpkolom zou staan kan je inbouwen in je uiteindelijke formule, maar het is gewoon niet zo lekker voor het overzicht als je nog eens wat moet aanpassen. Of: iemand anders dat moet doen.

quote:

Op vrijdag 21 februari 2014 08:40 schreef Janneke141 het volgende:

[..]

Dat kan ook, maar dan worden je formules gewoon langer. Wat er in die hulpkolom zou staan kan je inbouwen in je uiteindelijke formule, maar het is gewoon niet zo lekker voor het overzicht als je nog eens wat moet aanpassen. Of: iemand anders dat moet doen.

Of wanneer iemand vraagt of je ff naar zijn sheet wilt kijken omdat iets niet werkt
"Broncellen aanwijzen" aangezet, kon daarna zo het pad volgen van de berekening en zien waar de fout zat

Eens kijken of ik dit probleem simpel kan uitleggen:



Zie hierboven allereerst een tabel met 5 kolommen. Het gaat hier om valuta-combinaties met willekeurige waarden.

val1/ccy1 is gelinkted met val2/ccy2. dus 5 EUR -3 USD is een bij elkaar horende regel.
En zo zijn er dus nog 11 regels, maar het aantal regels kan potentieel groter of kleiner zijn.
Ook kunnen er andere val/ccy combiaties zijn met valuta die niet zijn genoemd.

De bedoeling is dat combinaties die hetzelfde zijn bij elkaar opgeteld worden, MAAR hier het moeilijke: EUR-USD is hetzelfde als USD-EUR en moeten dus bijvoorbeeld bij elkaar opgeteld worden.

Ik heb de uitkomst onder de tabel handmatig uitgerekend, en zoals jullie zien tel ik EUR-USD op bij USD-EUR en kom uit op een totaal EUR + toaal USD voor deze valutacombinatie.

Eigenlijk wil ik uit de tabel alleen maar de gele regels als uitkomst hebben, en dan maakt het voor mij niet uit of de uitkomst -4EUR 1USD is of 1USD -4 EUR, zolang er voor deze combinatie maar 1 totaal is.

Ik zit me suf te piekeren hoe ik dit voor elkaar kan krijgen.

quote:

Op vrijdag 21 februari 2014 21:59 schreef elkravo het volgende:
Eens kijken of ik dit probleem simpel kan uitleggen:

[ afbeelding ]

Zie hierboven allereerst een tabel met 5 kolommen. Het gaat hier om valuta-combinaties met willekeurige waarden.

val1/ccy1 is gelinkted met val2/ccy2. dus 5 EUR -3 USD is een bij elkaar horende regel.
En zo zijn er dus nog 11 regels, maar het aantal regels kan potentieel groter of kleiner zijn.
Ook kunnen er andere val/ccy combiaties zijn met valuta die niet zijn genoemd.

De bedoeling is dat combinaties die hetzelfde zijn bij elkaar opgeteld worden, MAAR hier het moeilijke: EUR-USD is hetzelfde als USD-EUR en moeten dus bijvoorbeeld bij elkaar opgeteld worden.

Ik heb de uitkomst onder de tabel handmatig uitgerekend, en zoals jullie zien tel ik EUR-USD op bij USD-EUR en kom uit op een totaal EUR + toaal USD voor deze valutacombinatie.

Eigenlijk wil ik uit de tabel alleen maar de gele regels als uitkomst hebben, en dan maakt het voor mij niet uit of de uitkomst -4EUR 1USD is of 1USD -4 EUR, zolang er voor deze combinatie maar 1 totaal is.

Ik zit me suf te piekeren hoe ik dit voor elkaar kan krijgen.

Weet niet of ik je probleem 100% goed snap, maar voor het optellen van combinaties van valuta zou je misschien het volgende kunnen doen.
Misschien kun je de valuta indelen met waarden 2^i.
EUR = 2^0 = 1
USD = 2^1 = 2
CZK = 2^2 = 4

Dan volgt voor combinaties van valuta
EUR + EUR = 2
EUR + USD = 3
USD + USD = 4
EUR + CZK = 5
USD + CZK = 6

Dan met hulp kolommetje en som-als kun je de combinaties bij elkaar optellen.

quote:

Op vrijdag 21 februari 2014 21:59 schreef elkravo het volgende:
Eens kijken of ik dit probleem simpel kan uitleggen:

Niet moeilijker maken dan het is. Gewoon je bereik optellen onder bepaalde voorwaarden.

=SOMMEN.ALS(val_1;ccy_1;A15;ccy_2;B15)+SOMMEN.ALS(val_2;ccy_2;A15;ccy_1;B15)
(waar A15 is valuta 1 zoals EUR en B15 is valuta 2 zoals USD. Je krijgt via deze formule de EURs terug.)

zie: www.snirc.nl/extra/currency.xlsx

[ Bericht 25% gewijzigd door snabbi op 21-02-2014 22:33:51 ]

quote:

Op vrijdag 21 februari 2014 22:24 schreef snabbi het volgende:

[..]

Niet moeilijker maken dan het is. Gewoon je bereik optellen onder bepaalde voorwaarden.

=SOMMEN.ALS(val_1;ccy_1;A15;ccy_2;B15)+SOMMEN.ALS(val_2;ccy_2;A15;ccy_1;B15)

zie: www.snirc.nl/extra/currency.xlsx

Maandag op mijn werk die sheet wat meer uitpluizen (heb thuis Excel 2003, dus sommen.als doet het niet...)
Die groepen zijn misschien wel handig... (wist wel dat je ze kon maken, maar had nog nooit echt het nut ervan gezien )

quote:

Op vrijdag 21 februari 2014 22:34 schreef PAAC het volgende:

[..]

Maandag op mijn werk die sheet wat meer uitpluizen (heb thuis Excel 2003, dus sommen.als doet het niet...)
Die groepen zijn misschien wel handig... (wist wel dat je ze kon maken, maar had nog nooit echt het nut ervan gezien )

Ik gebruik de namen vooral bij langere formules. Dan raak je het overzicht kwijt. Als je de formule nu leest is het eigenlijk direct logisch.

Met Excel 2003 kan het ook, alleen dan moet je meer moeite doen. Toen moest je het nog met een Array-formule oplossen. Ik heb het bestandje voor je aangepast. De array formule is iets lastiger uit te leggen. Dit is wat hij doet:

In cel G17 staat de volgende formule {=SOMPRODUCT((val_1)*(ccy_1=A17)*(ccy_2=B17))+SOMPRODUCT((val_2)*(ccy_2=A17)*(ccy_1=B17))}

De {} is belangrijk en krijg je via CTRL SHIFT ENTER.

Ik maak een vergelijking door per regel de waarde van de ccy van die regel te vergelijken met de waarde in A17. Wanneer dit WAAR is, dan ziet excel het als 1. Als het ONWAAR is als 0. Door gebruik te maken van vermenigvuldigen doe je in je somproduct een vermenigvuldiging met 0 wanneer het niet over A17 gaat. Met andere woorden, je telt alleen de A17s. Zo ook voor het tweede deel met de B17s.

Nadat Excel per regel heeft uitrekent of hij voldoet aan zowel de voorwaarde in A17 als in B17, telt hij de uitkomst van alle regels bij elkaar op.

[ Bericht 15% gewijzigd door snabbi op 21-02-2014 23:05:13 ]

quote:

Op vrijdag 21 februari 2014 22:57 schreef snabbi het volgende:

[..]

Ik gebruik de namen vooral bij langere formules. Dan raak je het overzicht kwijt. Als je de formule nu leest is het eigenlijk direct logisch.

Met Excel 2003 kan het ook, alleen dan moet je meer moeite doen. Toen moest je het nog met een Array-formule oplossen. Ik heb het bestandje voor je aangepast. De array formule is iets lastiger uit te leggen. Dit is wat hij doet:

In cel G17 staat de volgende formule {=SOMPRODUCT((val_1)*(ccy_1=A17)*(ccy_2=B17))+SOMPRODUCT((val_2)*(ccy_2=A17)*(ccy_1=B17))}

De {} is belangrijk en krijg je via CTRL SHIFT ENTER.

Ik zal zo even de eerder gemaakte uitleg hiervan aanhalen.. even zoeken.

Ik krijg nu zo nu en dan het antwoord "0" (geen flauw idee of dat klopt).

En rekenen met "{}" in Excel heb ik aardig mee lopen te rommelen ooit.
Vooral met de volgorde van wat je doet...
Maar uiteindelijk was dat gelukt

quote:

Op vrijdag 21 februari 2014 23:04 schreef PAAC het volgende:

[..]

Ik krijg nu zo nu en dan het antwoord "0" (geen flauw idee of dat klopt).

En rekenen met "{}" in Excel heb ik aardig mee lopen te rommelen ooit.
Vooral met de volgorde van wat je doet...
Maar uiteindelijk was dat gelukt

Ik heb ondertussen wat uitleg toegevoegd. Het invoeren moet dus zonder de {} en door op CTRL ALT ENTER te drukken krijg je die optie. Het bestand in de bovenstaande post heeft ook een volledige uitwerking.

De CTRL ALT ENTER, oftewel de array formule zorgt ervoor dat Excel met die meerdere regels in 1 cel gaat rekenen. Voor grote sheets wordt dit wel enorm traag. Daarom is SOMMEN.ALS veel fijner.

quote:

Op vrijdag 21 februari 2014 23:07 schreef snabbi het volgende:

[..]

Ik heb ondertussen wat uitleg toegevoegd. Het invoeren moet dus zonder de {} en door op CTRL ALT ENTER te drukken krijg je die optie. Het bestand in de bovenstaande post heeft ook een volledige uitwerking.

De CTRL ALT ENTER, oftewel de array formule zorgt ervoor dat Excel met die meerdere regels in 1 cel gaat rekenen. Voor grote sheets wordt dit wel enorm traag. Daarom is SOMMEN.ALS veel fijner.

quote:

Op vrijdag 21 februari 2014 23:07 schreef snabbi het volgende:

[..]

Ik heb ondertussen wat uitleg toegevoegd. Het invoeren moet dus zonder de {} en door op CTRL ALT ENTER te drukken krijg je die optie. Het bestand in de bovenstaande post heeft ook een volledige uitwerking.

De CTRL ALT ENTER, oftewel de array formule zorgt ervoor dat Excel met die meerdere regels in 1 cel gaat rekenen. Voor grote sheets wordt dit wel enorm traag. Daarom is SOMMEN.ALS veel fijner.

Nu haal je SHIFT en ALT door elkaar
Verder snap ik nu wat er gebeurt met de functie

Matrix-rekenen was ik al bekend mee, heb ooit eens Betacoefficienten van een Partial Least Squares algoritme nagerekend mbv die functies(kostte me meer tijd om uit te vissen hoe dat rekenen werkte qua invoeren, dan daadwerkelijk alles na te rekenen )

quote:

Op vrijdag 21 februari 2014 23:13 schreef PAAC het volgende:

[..]

[..]

Nu haal je SHIFT en ALT door elkaar
Verder snap ik nu wat er gebeurt met de functie

De volgende stap was CTRL ALT DEL