[Excel] automatische output genereren

[C4M1]

Ik heb een paar vragen mbt tot excel waar ik zelf niet direct uit kom. Ik hoop dat iemand me hier bij kan helpen. Ik maak gebruik van Excel 2002 Engelstalig.

Allereerst wil ik een automatische pass/fail conclusion genereren aan de hand van in te voeren velden. Dit moet er als volgt uit zien:

Min Max Actual Fail/pass
1 2 4 Fail
1 2 1.5 Pass
1 2 please fill in actual value

De velden "min" en "max" worden van te voren ingevuld. "Actual" wordt later ingevuld en hieruit rolt vanzelf een "fail/pass" generation. Dit is te maken mbv de volgende code: =IF(B2<A2,"Fail",IF(C2>B2,"Fail","Pass"))
Ik wil graag hieraan nog toevoegen een optie "please fill in actual value" als er in "actual" niets is ingevuld. Hoe is dit te doen?

Een tweede vraag die ik heb is een pass/fail generation gebaseerd op een dropdown menu. Maw: je hebt een dropdown menu waar je kunt selecteren: optie a, optie b, optie c. Worden optie a of optie b gekozen dan wordt in een ander veld automatisch "pass" gegenereert. Wordt optie c gekozen dan wordt er "fail" gegenereert. Wederom, als er niks is geselecteerd, komt er een melding "please choose". Ik heb het dropdown menu momenteel via "data --> validation --> list opgezet". Een probleem is hiermee dat als ik een range selecteer om in het dropdown menu weer te geven, en 1 van de cellen uit de range uit een gemergede cell bestaat in het dropdown menu deze cell ook een aantal regels wit verschijnen. Is dit te voorkomen?

[BtM909]

Het eerste kan je doen via Data | Validation...

[C4M1]

BtM909 schreef op maandag 26 februari 2007 @ 09:28:
Het eerste kan je doen via Data | Validation...

Als ik je goed begrijp doel jij op Data | Validation | Settings | Allow: decimal etc.
Als je nu een waarde opgeeft die niet binnen de opgegeven range valt krijg je een error. Het is echter de bedoeling dat de en de pass/fail melding in aparte cellen staan. Daarbovenop moet de pass/fail melding gewoon in de cell te lezen zijn, en niet in de vorm van een error naar voren komen.

[onkl]

Gewoon, je If nog een keer extra nesten:
=IF(C2="","please fill in actual value",IF(B2<A2,"Fail",IF(C2>B2,"Fail","Pass")))
Ik denk trouwens dat je
=IF(C2="","please fill in actual value",IF(C2<A2,"Fail",IF(C2>B2,"Fail","Pass")))
bedoelt.

Tweede vraag: Kijk eens of je ergens een "ignore blank" vinkje ziet staan. Die moet je hebben.

[C4M1]

onkl schreef op maandag 26 februari 2007 @ 10:52:
Gewoon, je If nog een keer extra nesten:
=IF(C2="","please fill in actual value",IF(B2<A2,"Fail",IF(C2>B2,"Fail","Pass")))
Ik denk trouwens dat je
=IF(C2="","please fill in actual value",IF(C2<A2,"Fail",IF(C2>B2,"Fail","Pass")))
bedoelt.

Tweede vraag: Kijk eens of je ergens een "ignore blank" vinkje ziet staan. Die moet je hebben.

Thank you! En je hebt ook gelijk met je punt dat ik eigenlijk iets anders bedoel. Dat vinkje bekijk ik later even. Time for dinner hier in Shanghai

[C4M1]

Nu wil ik toch een stapje lastiger, ik ben benieuwd of het volgende mogelijk is:

Tot zoverre hebben we het als volgt uitgewerkt, en werkend gekregen. (ik vraag me alleen nog af hoe je "fail"in rode letters, of met rode achtergrond kleur cel, en hetzelfde voor "pass" in het groen kunt krijgen)

Min...Max...Actual...Conclusion
1.........2.........0...........Fail

Ik wil hier nu 2 kolommen aan toevoegen

Min...Max...Testamount...Max Fail...Actual...Conlusion
1........2............20...............3......................Pass/Fail

Er is nu een test aantal, en een max aantal defecten mee te rekenen. In de cel "actual" moeten nu dan ook het aantal testwaardes worden ingevuld dat in de cel "test-amount" staat aangegeven. Worden er minder dan het benodigde aantal testen uitgevoerd, dan moet er een melding in de cel conclusion komen: "please complete tests". Als alle tests zijn uitgevoerd moet berekend worden of alle waardes tussen het toegestane minimum en maximum zitten. Of in elk geval, of er niet meer dan het max. aantal items deze eis overschrijden. Ben benieuwd of dit haalbaar is
Mijn voorkeur gaat er trouwens zeer sterk naar uit om de meetwaardes (actual) een beetje compact te houden, liefst in 1 cel. Dus niet een hele lange lijst onder elkaar maken.

[ Voor 5% gewijzigd door C4M1 op 28-02-2007 04:37 ]

[onkl]

Ik zal het even in brokjes doen:

C4M1 schreef op woensdag 28 februari 2007 @ 04:04:
Nu wil ik toch een stapje lastiger, ik ben benieuwd of het volgende mogelijk is:

Tot zoverre hebben we het als volgt uitgewerkt, en werkend gekregen. (ik vraag me alleen nog af hoe je "fail"in rode letters, of met rode achtergrond kleur cel, en hetzelfde voor "pass" in het groen kunt krijgen)

Kijk een bij conditional formatting, in het format menu.

Min...Max...Actual...Conclusion
1.........2.........0...........Fail

Ik wil hier nu 2 kolommen aan toevoegen

Min...Max...Testamount...Max Fail...Actual...Conlusion
1........2............20...............3......................Pass/Fail

Er is nu een test aantal, en een max aantal defecten mee te rekenen. In de cel "actual" moeten nu dan ook het aantal testwaardes worden ingevuld dat in de cel "test-amount" staat aangegeven. Worden er minder dan het benodigde aantal testen uitgevoerd, dan moet er een melding in de cel conclusion komen: "please complete tests". Als alle tests zijn uitgevoerd moet berekend worden of alle waardes tussen het toegestane minimum en maximum zitten. Of in elk geval, of er niet meer dan het max. aantal items deze eis overschrijden. Ben benieuwd of dit haalbaar is

Ja. Gewoon nog meer if's in je formule.

Mijn voorkeur gaat er trouwens zeer sterk naar uit om de meetwaardes (actual) een beetje compact te houden, liefst in 1 cel. Dus niet een hele lange lijst onder elkaar maken.

Nee, dat gaat niet lukken zonder VBA te gebruiken. Het is an sich niet heel moeilijk om een functie te schrijven die het doet, maar met het normale functieaanbod gaat het hem niet worden.

[C4M1]

Ik ben de afgelopen dagen een heel eind verder gekomen. Bedankt voor de hulp tot zoverre.

Conditional formatting werkt perfect. 1 klein nadeel: voor zover ik kan zien kan je per cel maar 3 verschillende 'opties' opgeven. En het is mij ook niet gelukt 1 cel te formatten gebaseerd op een conditie van een andere cel. (VB: als cel A1 = 1, cel A2 heeft grijze fill. Het lukt mij alleen: als cel A1=1, dan krijgt A1 een grijze fill)

De extra kolommen en IF's zijn toegevoegd. Ik heb het uiteindelijk niet in 1 cel gedaan omdat VBA me te ingewikkeld was, maar via een aparte sheet waar de waardes ingevoerd kunnen worden. Dit even met hyperlinkjes gekoppeld zodat het makkelijk terug te vinden is. Ik probeerde de aparte sheet te 'hiden', alleen dan werken de hyperlinkjes niet meer. Dat is erg jammer!

Verder vroeg ik me het volgende af: Hoe kan je een aantal cellen automatisch opvullen?
Stel: cel A1 heeft een input waarde. Hier vul je bijvoorbeeld in: 20
Nu moeten in de onderstaande cellen automatisch de range getallen van 1 tm 20 ingevuld worden. (Dus A2 = 1, A3 = 2, A4 = 3 etc t/m 20)
Het is mij wel gelukt met een stukje 'ranzig' programmeren. Stukje voorbeeldcode voor cel A8: =IF(A7=$A$1,"",IF(A7="","",A7+1)) . Deze code heb ik dan gewoon over de hele A kolom doorgecopieerd, maar echt fraai is het niet. Volgens mij moet het met een counter oid ook kunnen zodat je niet de hele code door je hele sheet hoeft te copieren, maar ik heb geen idee hoe.

Tot slot vraag ik me af of het mogelijk is woorden te tellen. Dus in een kolom heb ik onder elkaar staan 'Pass', 'Fail', 'Fail', 'Fail', 'Pass', 'Pass' etc. Nu wil ik weten hoeveel 'Pass' en hoeveel 'Fail' dit zijn. Ik weet dat je dit met draaitabellen makkelijk kunt doen. Het probleem hiermee is dat als ik de draaitabel een eigen sheet geef, deze draaitabel niet automatisch update. Je moet hiervoor de pagina refreshen. Als iemand weet hoe je dit automatisch kunt refreshen / updaten is dat perfect. Het is me wel gelukt het handmatig te programmeren door wat Pass en Fail naar losse kolommen te copieren en de waarde 1 te geven, zodat je ze simpel met een SUM kunt optellen. Als je de kolommen vervolgens HIDE is het probleem opgelost. Alleen zou het fraai zijn als het ook zonder dit soort trucs en extra kolommen kan. De excel file moet uiteindelijk door veel mensen gebruikt gaan worden, dus hoe meer verborgen trucs erin zitten hoe groter de kans wordt dat er mensen over gaan struikelen.

Hoop dat het verhaal duidelijk is en dan iemand tips heeft. Op verzoek kan ik ook zeker het documentje toesturen waar ik mee aan het werk ben.

[onkl]

C4M1 schreef op donderdag 08 maart 2007 @ 04:53:
Ik ben de afgelopen dagen een heel eind verder gekomen. Bedankt voor de hulp tot zoverre.

Conditional formatting werkt perfect. 1 klein nadeel: voor zover ik kan zien kan je per cel maar 3 verschillende 'opties' opgeven. En het is mij ook niet gelukt 1 cel te formatten gebaseerd op een conditie van een andere cel. (VB: als cel A1 = 1, cel A2 heeft grijze fill. Het lukt mij alleen: als cel A1=1, dan krijgt A1 een grijze fill)

Dan moet je met formules gaan werken in de conditional formatting. Meest voorkomend probleem dat ik ken, is dat Excel random aanhalingstekens verzint bij het opslaan van de formule, dus opnieuw openen en aanpassen. De output van de formule moet "TRUE" of "FALSE" zijn, iets als =IF(A2=1;"TRUE";"FALSE") kan je mee gaan prutsen.

De extra kolommen en IF's zijn toegevoegd. Ik heb het uiteindelijk niet in 1 cel gedaan omdat VBA me te ingewikkeld was, maar via een aparte sheet waar de waardes ingevoerd kunnen worden. Dit even met hyperlinkjes gekoppeld zodat het makkelijk terug te vinden is. Ik probeerde de aparte sheet te 'hiden', alleen dan werken de hyperlinkjes niet meer. Dat is erg jammer!

Verder vroeg ik me het volgende af: Hoe kan je een aantal cellen automatisch opvullen?
Stel: cel A1 heeft een input waarde. Hier vul je bijvoorbeeld in: 20
Nu moeten in de onderstaande cellen automatisch de range getallen van 1 tm 20 ingevuld worden. (Dus A2 = 1, A3 = 2, A4 = 3 etc t/m 20)
Het is mij wel gelukt met een stukje 'ranzig' programmeren. Stukje voorbeeldcode voor cel A8: =IF(A7=$A$1,"",IF(A7="","",A7+1)) . Deze code heb ik dan gewoon over de hele A kolom doorgecopieerd, maar echt fraai is het niet. Volgens mij moet het met een counter oid ook kunnen zodat je niet de hele code door je hele sheet hoeft te copieren, maar ik heb geen idee hoe.

Iets is pas ranzig als het niet werkt. Ik doe het ook altijd zo. Zal inderdaad vast netter kunnen.

Tot slot vraag ik me af of het mogelijk is woorden te tellen. Dus in een kolom heb ik onder elkaar staan 'Pass', 'Fail', 'Fail', 'Fail', 'Pass', 'Pass' etc. Nu wil ik weten hoeveel 'Pass' en hoeveel 'Fail' dit zijn. Ik weet dat je dit met draaitabellen makkelijk kunt doen. Het probleem hiermee is dat als ik de draaitabel een eigen sheet geef, deze draaitabel niet automatisch update. Je moet hiervoor de pagina refreshen. Als iemand weet hoe je dit automatisch kunt refreshen / updaten is dat perfect. Het is me wel gelukt het handmatig te programmeren door wat Pass en Fail naar losse kolommen te copieren en de waarde 1 te geven, zodat je ze simpel met een SUM kunt optellen. Als je de kolommen vervolgens HIDE is het probleem opgelost. Alleen zou het fraai zijn als het ook zonder dit soort trucs en extra kolommen kan. De excel file moet uiteindelijk door veel mensen gebruikt gaan worden, dus hoe meer verborgen trucs erin zitten hoe groter de kans wordt dat er mensen over gaan struikelen.

Hoop dat het verhaal duidelijk is en dan iemand tips heeft. Op verzoek kan ik ook zeker het documentje toesturen waar ik mee aan het werk ben.

Kijk eens naar de COUNTIF functie. Die kan dat.

[C4M1]

Het functiegebruik onder voorwaardelijke opmaak werkt briljant.
De countif ziet er na wat google zoekwerk zo simpel nog niet uit. Omdat ik van deze nog niet direct inzie hoe het de sheet een stuk 'netter' kan maken, moet ik hier nog even over nadenken. Een gehide kolom is wellicht niet zo heel veel slechter. Zou eigenlijk veel liever willen weten of het mogelijk is zo'n aparte sheet met pivot table automatisch te laten refreshen. Komende dagen verder klussen! Bedankt voor de tips weer in elk geval!

[C4M1]

Ik ben naar schatting op 80% van de file. Het gaat de goede kant op. Maar er is nog 1 punt waar ik tegenaan loop. Het gaat om het volgende. Er is een input waarde. En er zijn ranges van getallen. De input waarde valt altijd binnen een van de ranges van deze getallen. Door binnen een bepaalde range te vallen valt de input waarde vanzelf in een bepaalde categorie. Hoe is het makkelijk te programmeren dat voor de input waarde snel berekend wordt in welke range deze valt, zodat de ouput categorie zichtbaar wordt?

Voorbeeld:

Input: 2000

Ranges:

Min Max Output (categorie)

2 8 A
9 15 A
16 25 B
26 50 C
51 90 C
91 150 D
151 280 E
281 500 F
501 1200 G
1201 3200 H
3201 10000 J
10001 35000 K
35001 150000 L
150001 500000 M
500001 9999999999 N

[C4M1]

Ik heb het dit keer zelf opgelost. Twee extra kolommen toegevoegd.
De eerste om van de dubbele voorwaarde een enkele te maken. Maw: eerst moets een getal in een range vallen. Mbv: =SUM(IF($B$25>$B3,IF($B$25<$A5,1,0))) kun je dit omzetten in 1 (true) of 0 (not true). Nu in de kolom daarnaast een simpele IF vergelijking , bv: =IF($J4=1,$D4,""). En dan die kolom verticaal optellen om de juiste waarde eruit te halen, voor degene die dit uberhaupt nog leest of volgt.
Hoe dan ook, het werkt. Bedankt voor de support.

[onkl]

Het kan charmanter, waardoor je geen hulpkolommen nodig hebt (en dus meerdere waardes tegelijk kan berekenen. De functie VLOOKUP geeft, als je het vierde argument "true" laat zijn, geen error als de zoekwaarde niet gevonden is, maar de hoogste waarde die kleiner is dan de zoekwaarde.
=VLOOKUP(inputcel,min-max-outputdeel,3,TRUE) geeft dus het gezochte antwoord.

[C4M1]

dat is inderdaad een mooie functie. Bedankt voor de tip!
offtopic: irritant om engelse functies (vlookup) in het Nederlands (vert.zoeken) terug te vinden, als ik thuis aan het klussen sla.

[Verwijderd]

C4M1 schreef op woensdag 14 maart 2007 @ 11:33:offtopic: irritant om engelse functies (vlookup) in het Nederlands (vert.zoeken) terug te vinden, als ik thuis aan het klussen sla.

kijk daarvoor eens naar
http://members.chello.nl/jvolk/keepitcool/download.html
http://www.applewood.nl/m...x.asp?excelvertaal.asp&14
of
http://cherbe.free.fr/traduc_fonctions_xl97.html

[C4M1]

top, bedankt!

[onkl]

Verwijderd schreef op woensdag 14 maart 2007 @ 11:45:
[...]
kijk daarvoor eens naar
http://members.chello.nl/jvolk/keepitcool/download.html
http://www.applewood.nl/m...x.asp?excelvertaal.asp&14
of
http://cherbe.free.fr/traduc_fonctions_xl97.html

Ach, ja. Die zouden ooit nog in de FAQ landen. Zal even een mod DM'en.