[Excel] Het grote Excel vragen topic #39

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

quote:

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

[..]


De volgende stap was CTRL ALT DEL

Haha, doet me denken aan de oude dagen in chatrooms
"Ik heb zin, wil je privé met me? Druk dan op Alt + F4"

*Poof*
Halve chatroom leeg

Of snel bij een collega CTRL SHIFT en "pijltjestoetsen" indrukken (en dan snel weglopen).

Beste allemaal,

Ik ben momenteel bezig op mijn werk een overzicht te maken van spullen welke we daarvan de cerficaten moeten bijhouden en monitoren wanneer deze komen te verlopen. Ik heb op het internet gevonden hoe je een dropdownmenu kunt maken en hoe je een kleur kan geven wanneer iets over de datum heen is. Hoe het momenteel is opgebouwd:

Bovenaan de pagina heb ik een datum =TODAY() staan die dus de datum van vandaag altijd aangeeft. Dan heb ik een lijst met spullen die je kan kiezen uit een dropdown menu en daarbij moet je wel handmatig de datums invullen. Nu wil ik het zo hebben, dat wanneer de datum (die achter de itmens staan vermeld) op de certificaten verlopen zijn dit rood opkleurt. Dit is mij gelukt met voorwaardelijke opmaak, echter nu wil ik ook nog dat deze een maand voor tijd of 3 maanden voor tijd geel opkleurt, zodat je bij elke keer dat je de excelfile opent in één oogopslag kunt zien, wanneer er een hercertificering zit aan te komen.

Voorwaardelijke opmaak

Cel Value <= D$2$ (de today cel) met stop if true aangevinkt

Momenteel als ik dan geen datum in de cel zit kleurt hij dan rood op, terwijl dit blank zou moeten zijn.

Dus nog even voor de duidelijkheid:

- Ik heb een datum bovenaan de pagina staan die de dag vandaag aangeeft.
- Met een dropdownmenu selecteer je het item
- Handmatig dan de datum invoeren van het item
- Wanneer je niks invoert moet de cel waar je de datum van het certificaat van het item invult blank blijven
- Hij moet een maand voor tijd geel worden
- Hij moet, wanneer het certificaat verlopen is rood kleuren (wat momenteel al werkt), dit heb ik getest

Ik heb ook nog een vraag over een email aanmaken / versturen, maar begreep dat dit wel redelijk ingewikkeld is. Dus bij het verlopen van certificaat automatisch een email verzenden / outlook openen met betreffende email. Dit komt later wel

Er wordt vaak op een antwoord gegeven voorwaardelijke opmaak, echter ben hier al uren mee aan het prutsen geweest en kom er niet uit.

Alvast bedankt,

Foxbuster

Werkt niet? Geel staat boven rood, stop if true staat niet aangevinkt en wat moet ik dan bij de formule invullen?

Geel -> Cell value smaller then or equal too D$2$+30 (de maand) (stop if true niet aangevinkt)
Rood -> Cell value smaller then or equal too D$2$ (stop if true aangevinkt)

Alles kleurt nu geel

En hoe krijg ik blank wanneer ik geen datum invul?

hey allen!

Ik wil in mijn excelbestand met 1 druk op de knop wat tekst in meerdere cellen kunnen laten verdwijnen en weer terug laten komen...is dit mogelijk?

Ik zal mijn bestand even uploaden, toelichting staat in de file zelf. Hoop dat jullie mij verder kunnen helpen

http://www.mijnbestand.nl/Bestand-XKRTTWBHZAZ8.xlsx

Toelichting

Wanneer de vuldatum bereikt wordt of groter dan rood kleuren -> dit werkt
Wanneer de vuldatum een maand daarvoor bereikt wordt tot de vuldatum moet deze geel kleuren

Dus blank -> maand ervoor geel -> daarna rood totdat de vuldatum opnieuw wordt aangepast

Als D2 "vandaag" is, dan moet je voor "geel" vergelijken met D2-30 (<= D2-30).

Voor zowel rood als geel nog even not(isblank(jecel)) opnemen in een AND-formule zodat alleen de rijen met ingevulde datum worden ingekleurd.

Moet niet zo moeilijk zijn: ik heb 2 kolommen, A en B. B is langer dan A. Ik wil van kolom B enkel de waarden overhouden die niet in kolom A zichtbaar zijn. Howto?

edit: dit maar gebruikt:
En welke klapjosti heeft in godsnaam bedacht om de formules te vertalen? Tenenkrommend.

[ Bericht 54% gewijzigd door MichielPH op 26-02-2014 13:12:19 ]

quote:

Op woensdag 26 februari 2014 13:03 schreef MichielPH het volgende:
Moet niet zo moeilijk zijn: ik heb 2 kolommen, A en B. B is langer dan A. Ik wil van kolom B enkel de waarden overhouden die niet in kolom A zichtbaar zijn. Howto?

edit: dit maar gebruikt:
[ code verwijderd ]

En welke klapjosti heeft in godsnaam bedacht om de formules te vertalen? Tenenkrommend.

irritant is dat he. Gelukkig kun je dat aanpassen in de meeste office pakketten.

Ik heb een vraag over voorwaardelijke opmaak.
Ik heb voorwaardelijke opmaak gebruikt om een rij gegevens te ontdubbelen. Maar als ik er nu weer wat in kopieer geeft hij weer die kleur aan als het dubbel voorkomt. Hoe verwijder ik dat uit die kolom?

quote:

Op woensdag 26 februari 2014 13:29 schreef Stansfield het volgende:
Ik heb een vraag over voorwaardelijke opmaak.
Ik heb voorwaardelijke opmaak gebruikt om een rij gegevens te ontdubbelen. Maar als ik er nu weer wat in kopieer geeft hij weer die kleur aan als het dubbel voorkomt. Hoe verwijder ik dat uit die kolom?

Voorwaardelijke opmaak > regels wissen > regels uit geselecteerde cellen wissen.

Hoi, ik heb een vraagje en ik hoop dat jullie OF een antwoord hebben, of kunnen bevestigen dat ik pech heb en het niet anders kan .

Situatie: we gebruiken Excel om offertes op te stellen. Je hebt dan een product met inkoopprijs, hier komt een percentage bij voor verkoop en vervolgens een totaalprijs. Echter, omdat de formule met het niet afgeronde bedrag werkt, krijg je soms afwijkingen (zowel positief als negatief). Voorbeeld:
Koffie arrangement ¤ 1,74. Uplift is 12%, maakt op papier ¤ 1,95. Echter is dit stiekem ¤ 1,9488 (hier rekent Excel op de achtergrond mee), waardoor je voor 8x koffie ineens ¤ 15,59 betaald i.p.v. ¤ 15,60.

Is dit te voorkomen? Of te corrigeren? In het voorbeeld gaat het om een zeer minimale afwijking, maar ten eerste staat het soms raar op de offerte (bijvoorbeeld op een bedrag van 1,34 > 1,50 * 8 = 12, wordt weergegeven als 12,01) en ten tweede maakt het op grote bedragen natuurlijk wel meer uit.

De productprijs afronden met ROUND, is dat een optie?

quote:

Op maandag 3 maart 2014 13:50 schreef sylvesterrr het volgende:
De productprijs afronden met ROUND, is dat een optie?

Kan ik dat hieraan toevoegen? =+C2*1,12 want dat berekent de verkoopprijs. C2 is dus de inkoopprijs.

Nevermind, als ik er een kolom bij maak, eerst de verkoopprijs per stuk laat berekenen en die in de 2e kolom laat rounden, dan gaat het goed! Thanks!