[Excel] Het grote Excel vragen topic #32

quote:

Op donderdag 17 mei 2012 13:46 schreef jakees het volgende:
hallo ik op basis van een formule bepalen uit welke cel ik een waarde wil ophalen. Even versimpeld wil op basis van een rekensom in b1 bepalen in de cel b2 de waarde hebben uit a1 of a2 of a3 etc. moet halen. Iets als:
=a(b1) waarbij b1 dan de waarde 1 tm 10000 kan bevatten. Ik wil niet gebruik maken van verticaal zoeken. Voor dit voorbeeld zou het wel werken maar ik wil dit gaan gebruiken voor een rangbepaling die ik meerdere keren binnen een kolom wil toepassen. Ik hoop dat het een beetje duidelijk is.

Heb je de ALS()-functie al geprobeerd?

-edit- Faal

[ Bericht 6% gewijzigd door qu63 op 17-05-2012 14:38:20 ]

quote:

Op donderdag 17 mei 2012 13:46 schreef jakees het volgende:
hallo ik op basis van een formule bepalen uit welke cel ik een waarde wil ophalen. Even versimpeld wil op basis van een rekensom in b1 bepalen in de cel b2 de waarde hebben uit a1 of a2 of a3 etc. moet halen. Iets als:
=a(b1) waarbij b1 dan de waarde 1 tm 10000 kan bevatten. Ik wil niet gebruik maken van verticaal zoeken. Voor dit voorbeeld zou het wel werken maar ik wil dit gaan gebruiken voor een rangbepaling die ik meerdere keren binnen een kolom wil toepassen. Ik hoop dat het een beetje duidelijk is.

Het antwoord op je vraag is =INDIRECT("a"&b1) waarbij je dus de kolom hebt staan op A en de waarde in B1 geeft het regelnummer.

quote:

Op donderdag 17 mei 2012 14:11 schreef snabbi het volgende:

[..]

Het antwoord op je vraag is =INDIRECT("a"&b1) waarbij je dus de kolom hebt staan op A en de waarde in B1 geeft het regelnummer.

thx is bijna wat ik zoek. Even wat verduidelijking. Ik heb 40 rijen. in kolom A staat de naam van een persoon en in kolom B de het jaartal en kolom 3 de score. Per jaar staan er 10 rijen. Wat ik wil is dat ik een rang functie maakt die als het ware dynamisch werkt. Dus bij de eerste 10 rijen staat er ==RANG(C2;C$2:C$11;0) in de 2e 10 rijen staat =RANG(C12;C$12:C$21;0). Ik wil proberen dit dynamisch te maken. Met indirect lukt dat denk ik niet. Dan haal je echt de waarde op terwijl ik de range dynamisch wil houden.

ADRES(INDIRECT(

quote:

Op donderdag 17 mei 2012 14:37 schreef DaFan het volgende:
ADRES(INDIRECT(

thx dat is hem!!!!!

quote:

Op donderdag 17 mei 2012 14:46 schreef jakees het volgende:

[..]

thx dat is hem!!!!!

Mooizo

quote:

Op donderdag 17 mei 2012 14:58 schreef DaFan het volgende:

[..]

Mooizo

Oef iets te vroeg gejuicht. Zie https://docs.google.com/s(...)lodXhmb0E&output=xls . Het lukt me wel om met de adres functie de juiste waarde op te halen. Maar ik krijg hem niet in de rang functie. Zien jullie wat ik verkeerd doe?

quote:

Op donderdag 17 mei 2012 15:49 schreef jakees het volgende:

[..]

Oef iets te vroeg gejuicht. Zie https://docs.google.com/s(...)lodXhmb0E&output=xls . Het lukt me wel om met de adres functie de juiste waarde op te halen. Maar ik krijg hem niet in de rang functie. Zien jullie wat ik verkeerd doe?

Dit gaat niet goed met de dubbele punt:
ADRES(RIJ(I3)-A3;3):ADRES(RIJ(I3)-A3+10;3)

Ik zie niet helemaal wat je doet maar je moet het volgende doen:
INDIRECT(ADRES1&":"&ADRES2)

Dan maak je een string met ADRES, dan de dubbele punt, dan het laatste ADRES.
Dus bv $C$3:$C$4. Als daar dan INDIRECT omheen staat kan hij het lezen als range.

Succes.

quote:

Op donderdag 17 mei 2012 15:54 schreef DaFan het volgende:

[..]

Dit gaat niet goed met de dubbele punt:
ADRES(RIJ(I3)-A3;3):ADRES(RIJ(I3)-A3+10;3)

Ik zie niet helemaal wat je doet maar je moet het volgende doen:
INDIRECT(ADRES1&":"&ADRES2)

Dan maak je een string met ADRES, dan de dubbele punt, dan het laatste ADRES.
Dus bv $C$3:$C$4. Als daar dan INDIRECT omheen staat kan hij het lezen als range.

Succes.

THX! nu eindelijk gelukt. De formule is nu:
=RANG(D2;INDIRECT(ADRES(RIJ(A2)-A2;4)&":"&ADRES(RIJ(A2)-A2+10;4))) is ook niet eenvoudig moet ik zeggen maar ja wel weer 3 uur verspeeld die ik aan mijn studie had moeten besteden. grrr

Een hele andere oplossing voor je, de formule die je hier nodig hebt is
=SOM((C2=$C$2:$C$31)*(D2<=$D$2:$D$31))

Het betreft een array formule. Je moet dus je ctrl en shift toets ingedrukt houden terwijl je op enter drukt.
http://www.mijnbestand.nl/Bestand-CCOH38ISWCOP.xlsx

Hoe het werkt:
Wat excel hier doet is het volgende. Er wordt binnen de cel waar je dit plaatst een nieuwe matrix aangemaakt. Deze is 1 kolom met 30 waarden (door je C2:C30).

Elke regel in deze cel krijgt de waarde 1 of 0. Deze waarde wordt bepaald aan de hand van de formule: a*b
hierbij is a gelijk aan c2=$c$2:$c$31
hierbij is b gelijk aan d2<=$D$2:$D$31

wanneer de waarde in c2 (bijvoorbeeld 2008) gelijk is aan de waarde in de reeks C2:C31 wordt op de corresponderende regel in je matrix een 1 geplaatst en wanneer het niet waar is een 0. Door de formule a*b krijg je 0*1 of 0*0 wanneer je een ander jaartal hebt dan degene die op die ene regel staat. Doordat je met 0 vermenigvuldigt, is de uitkomst ook nul.

Het tweede deel kijkt simpelweg of er getallen kleiner zijn dat het getal waar je nu naar kijkt. Als dat zo is, is dit dus niet het grootste getal en moet je er in je ranking er 1 optellen. Dit gebeurt ook met de 1*1 uitkomst. De som van je 30 getallen in je matrix, zorgt dat je de rank van die betreffende regel hebt.

[ Bericht 31% gewijzigd door snabbi op 17-05-2012 18:16:58 ]

quote:

Op donderdag 17 mei 2012 18:04 schreef snabbi het volgende:
Een hele andere oplossing voor je, de formule die je hier nodig hebt is
=SOM((C2=$C$2:$C$31)*(D2<=$D$2:$D$31))

Het betreft een array formule. Je moet dus je ctrl en shift toets ingedrukt houden terwijl je op enter drukt.
http://www.mijnbestand.nl/Bestand-CCOH38ISWCOP.xlsx

Hoe het werkt:
Wat excel hier doet is het volgende. Er wordt binnen de cel waar je dit plaatst een nieuwe matrix aangemaakt. Deze is 1 kolom met 30 waarden (door je C2:C30).

Elke regel in deze cel krijgt de waarde 1 of 0. Deze waarde wordt bepaald aan de hand van de formule: a*b
hierbij is a gelijk aan c2=$c$2:$c$31
hierbij is b gelijk aan d2<=$D$2:$D$31

wanneer de waarde in c2 (bijvoorbeeld 2008) gelijk is aan de waarde in de reeks C2:C31 wordt op de corresponderende regel in je matrix een 1 geplaatst en wanneer het niet waar is een 0. Door de formule a*b krijg je 0*1 of 0*0 wanneer je een ander jaartal hebt dan degene die op die ene regel staat. Doordat je met 0 vermenigvuldigt, is de uitkomst ook nul.

Het tweede deel kijkt simpelweg of er getallen kleiner zijn dat het getal waar je nu naar kijkt. Als dat zo is, is dit dus niet het grootste getal en moet je er in je ranking er 1 optellen. Dit gebeurt ook met de 1*1 uitkomst. De som van je 30 getallen in je matrix, zorgt dat je de rank van die betreffende regel hebt.

RESPECT. Daar had ik nooit opgekomen. Ik snap het een beetje maar ziet er goed uit. Wordt dit vaak gebruikt om de rang te bepalen?

quote:

Op donderdag 17 mei 2012 18:36 schreef jakees het volgende:

[..]

RESPECT. Daar had ik nooit opgekomen. Ik snap het een beetje maar ziet er goed uit. Wordt dit vaak gebruikt om de rang te bepalen?

Dit wordt vooral gebruikt wanneer je meerdere criteria hebt op basis waarvan je de rangschikking moet bepalen. Je kan dit namelijk met zoveel kolommen uitbreiden als je zelf wilt. Het is dus ook een goede tip voor alle voetbalpoules die nu worden opgezet

[ Bericht 0% gewijzigd door snabbi op 18-05-2012 11:27:37 ]

quote:

Op donderdag 17 mei 2012 18:44 schreef snabbi het volgende:

[..]

Dit wordt vooral gebruikt wanneer je meerdere criteria hebt op basis waarvan je de rangschikking moet betalen. Je kan dit namelijk met zoveel kolommen uitbreiden als je zelf wilt. Het is dus ook een goede tip voor alle voetbalpoules die nu worden opgezet

Ok top. Ik snap hem nu en is iets eenvoudiger qua opzet dan die ander. Thx snabbi en DaFan

quote:

Op woensdag 16 mei 2012 09:10 schreef Deetch het volgende:
Volgens mij gaat het met de DIM regel fout omdat dat geen zegt dat objol een outlook applicatie is maar je moet zeggen dat het een object is en dan later zeggen wat voor object. Zie ook het voorbeeld uit de VBA help van excel 2010. Of de site van ron de bruin: http://www.rondebruin.nl/sendmail.htm
[ code verwijderd ]


bovenstaande code werkt in ieder geval.

Mijn god, wat faal ik hard. Zelfs 1 op 1 kopie krijg ik niet werkend.

Ik ga jullie tijd niet verder verdoen, en me eerst zelf eens verder verdiepen in heel dit gebeuren Toch bedankt voor enthausiaste, snelle en goede hulp
Je ziet me weer terug als ik wat meer kennis heb opgedaan

quote:

Op vrijdag 18 mei 2012 07:25 schreef Prof_Hoax het volgende:

[..]

Mijn god, wat faal ik hard. Zelfs 1 op 1 kopie krijg ik niet werkend.

Ik ga jullie tijd niet verder verdoen, en me eerst zelf eens verder verdiepen in heel dit gebeuren Toch bedankt voor enthausiaste, snelle en goede hulp
Je ziet me weer terug als ik wat meer kennis heb opgedaan

Als ik je vraag goed heb begrepen, wil je gewoon een draft e-mail klaarzetten. Dit hoeft niet direct verstuurt te worden. In dat geval heb je in mijn ogen helemaal geen Macro nodig en kan je volstaan met een hyperlink. De mailto functie in je hyperlink zorgt ervoor dat je standaard e-mail programma wordt opgestart en je kan zelf de concept tekst helemaal klaar zetten.

Voor een voorbeeld: http://www.mijnbestand.nl/Bestand-LAUV6NWBKJHZ.xlsx

quote:

Op vrijdag 18 mei 2012 10:45 schreef snabbi het volgende:

[..]

Als ik je vraag goed heb begrepen, wil je gewoon een draft e-mail klaarzetten. Dit hoeft niet direct verstuurt te worden. In dat geval heb je in mijn ogen helemaal geen Macro nodig en kan je volstaan met een hyperlink. De mailto functie in je hyperlink zorgt ervoor dat je standaard e-mail programma wordt opgestart en je kan zelf de concept tekst helemaal klaar zetten.

Voor een voorbeeld: http://www.mijnbestand.nl/Bestand-LAUV6NWBKJHZ.xlsx

Na 't weekeind eens naar kijken. Dankjewel

Dacht dat ik wel aardig met excel overweg kon, maar kom er nu even niet uit.

Ik was een simpel EK poule in elkaar aan het zetten en nu de volgende sitautie:

E36 = Rusland
F36 = aantal doelpunten dat Rusland heeft gemaakt
H36 = aantal doelpunten dat Denemarken heeft gemaakt
I36 = Denemarken
K36 = Rusland of Denemarken (diegene die na penalty's wint, bij gelijke stand na 120 minuten, zie cel F36 en H36).

Nu wil ik dat de winnaar van deze wedstrijd automatisch wordt ingevuld in de halve finale, maar als de uitslag nog niet (volledig) is ingevuld, dan moet er nog niets komen te staan. Niet volledig = F36, H36 of eventueel K36.

Ik kom er niet uit, iemand die mij kan helpen?

Alvast bedankt!!

edit: het is al gelukt, ik zat met de > en < te kloten

dit was hem dus: =ALS(F36="";"";ALS(H36="";"";ALS(F36>H36;E36;ALS(F36<H36;I36;ALS(F36=H36;ALS(K36="";"";K36))))))

[ Bericht 17% gewijzigd door Roberto258 op 20-05-2012 17:20:15 ]

Hallo ik ben nieuw hier. Ik heb een berg Excelbestanden (~1000) waarin ik de tekstterugloop in
alle cellen van kolom B ongedaan wil maken. Is er een makkelijke manier om dit in bulk/batch te doen? Ik doe dit liever niet handmatig... . Het lukt me om een macro op te nemen om het binnen één bestand te doen, maar ik weet niet hoe ik dit automatisch over meerdere bestanden kan uitvoeren.

Iemand een goed idee? Alvast bedankt!

Ik heb een macro voor je gemaakt die ieder bestand in een bepaalde folder voor je opent. Ik heb hem niet monkey-proof gemaakt, dus hij checkt verder niet of het wel een Excel bestand is en dergelijke.

Open een Excel bestand en plaats de code er in. Dit kan je doen via ALT F11 om de Visual Basic editor te openen en vervolgens een module toe te voegen (rechts muistoets -> Invoegen -> Module).

Voer de opdracht uit via ALT F8 om en je macro te starten. In het voorbeeld heb ik de map c:\excels als voorbeeld genomen en natuurlijk alleen de B kolom.


EDIT: *woordje folder toegevoegd in regel 17

[ Bericht 1% gewijzigd door snabbi op 20-05-2012 22:36:50 ]

Fantastisch snabbi Dit is meer hulp dan ik op had gehoopt!

Ik krijg echter de volgende melding bij het uitvoeren van de macro:

quote:

Run time error '1004'
'bestand1.xlsx' could not be founds. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open a file from your list of most recent used files, make sure that the file has not be renamed, moved or deleted.

Als de locatie niet correct is, hoe kan hij dan de naam van het bestand weten? Zie ik iets over het hoofd of gaat er iets anders mis?

Wijzig de code in regel 17 naar (zoals ik ook hierboven al heb gedaan):
Workbooks.Open Filename:=folder & bestand

Dan ben je immers volledig in de naamgeving. Bij mijn testje werkte dit wel omdat hij dan gebruik maakt van je recent geopende bestanden. Even vergeten Let er wel op dat je folder dus ook eindigt op een \ anders loopt dat weer verkeerd

Wat je ook kan doen met je macro is met je cursor in de code gaan staan en via F8 vervolgens regel voor regel doorlopen. Uiteraard kan je via het play knopje doorgaan wanneer je voldoende gezien hebt.

Overigens ben ik er ook 'blind' vanuit gegaan dat je alleen in de actieve sheet kolom B moet wijzigen. Indien je over meerdere sheets de handeling wilt verrichten moet je dat column(kolom) deel wijzigen naar iets in de trant van:


[ Bericht 19% gewijzigd door snabbi op 20-05-2012 22:59:03 ]

De bewerking is inderdaad alleen in het actieve sheet nodig dus je laatste aanpassing is niet nodig voor mij

Het werkt nu in ieder geval perfect! Heel erg bedankt!!

quote:

Op vrijdag 18 mei 2012 10:45 schreef snabbi het volgende:

[..]

Als ik je vraag goed heb begrepen, wil je gewoon een draft e-mail klaarzetten. Dit hoeft niet direct verstuurt te worden. In dat geval heb je in mijn ogen helemaal geen Macro nodig en kan je volstaan met een hyperlink. De mailto functie in je hyperlink zorgt ervoor dat je standaard e-mail programma wordt opgestart en je kan zelf de concept tekst helemaal klaar zetten.

Voor een voorbeeld: http://www.mijnbestand.nl/Bestand-LAUV6NWBKJHZ.xlsx

Ziet er goed uit. En inderdaad je had de vraag goed begrepen, maar het is iets uitgebreider:

SPOILER

Om spoilers te kunnen lezen moet je zijn ingelogd. Je moet je daarvoor eerst gratis Registreren. Ook kun je spoilers niet lezen als je een ban hebt.

edit: zie post hieronder.

[ Bericht 5% gewijzigd door Prof_Hoax op 21-05-2012 11:37:46 ]

Disregard above


Heb iets geprobeerd. De cel waar de ontvanger in gedefineerd wordt heb ik aangegeven als zijnde een email adres. Dit omdat de PDL die ik wilde gebruiken begint met -admin-blabla-blabla-blabla.
Het streepje zorgt ervoor dat hij het ziet als een functie, en begon dus te mekkeren.

Daarbij heb ik het interne e-mail adres voor een PDL ook gevonden (nml pdl@exch.onsbedrijf.com)

Nu verder spelen.

Okay, status:
Ik krijg een draft.
Ik krijg hem met adres (pdl+ cc ) die ik wil.
Ik krijg hem met een aantal regels die ik als draft wil, echter ik mag maar maximaal dit invullen :

alles wat ik meer invul, krijg ik een #value (ongeacht of het een stuk tekst is of een extra witregel) Haal ik dit terug weg, doet hij het weer gewoon.

Oh, en ik gebruik %0a omdat ik geen enter/shift+enter kon gebruiken in B9 (gele vakje) van het test sheet...


[ Bericht 23% gewijzigd door Prof_Hoax op 21-05-2012 13:29:54 ]

Dit zegt toch : vervang, uit cel B9, character 10 (ik denk enter?) door "%0a" (dus een enter in de mail?)

Tevens, kan ik opmaak aan de tekst meegeven (bold/cursief)