DIG Digital Corner
Alles wat je altijd al over computers, hardware, software, internet en elektronische gadgets had willen weten, maar niet op Tweakers.net durft te vragen.
Welkom bij “Het grote Excel vragentopic” waar je terecht kan met vragen over Excel.
In dit topic is het de bedoeling dat we gericht vragen stellen en antwoorden geven op de gestelde vragen. Om alles zo goed mogelijk te laten verlopen zijn er een aantal regels.
Regels
1. Als je een vraag stelt, zet er dan bij welke versie van Excel je hebt en op welke taal hij ingesteld staat.
2. Zet macrocode tussen [ code ] tags
3. Als het “pseudo-code” is, vermeldt dit dan duidelijk
4. Er bestaan geen domme vragen, wel domme antwoorden. Als jouw antwoord niet gaat bijdragen, geef het antwoord dan niet.
5. Dit topic is geen “code-FEBO” waar je half opgewarmde code uit de muur trekt, het is de bedoeling dat je er zelf iets van leert.
Definities
• Functie Een interne functie die bij Excel is geleverd.
• UDF Een “user definded function”, een zelfgebouwde functie
• Macro Een stuk code die opdrachten uitvoert, gebouwd in (meestal) de VBA editor
• Add-In Een Excel workbook met VBA-code (of C#, C) die als .xla (of .xll) is opgeslagen, deze is als vaste plug-in in Excel te plaatsen.
• Workbook Het excelbestand
• Sheet of Worksheet Het tabblad
• Form, Formulier, Userform Een Dialoogvenster in VBA gebouwd
Tips & Links
De help functie van excel of visual basic voor excel kan je een eind op weg helpen. (F1)
Het workbook met het FOK!-menu zoals in de banner weergegeven is hier te vinden. Dit menu is naar wens aan te passen. De icoontjes in het menu zijn hier allemaal weergegeven. In dit workbook zijn drie macro's aanwezig:
• Een printertool waarmee je uit meerdere workbooks sheets kan selecteren voor afdrukken
• Een power replace die termen kan wijzigen in meerdere workbooks tegelijk
• Een formule vertaler, de formule in de actieve cel wordt in het engels weergegeven. Je kan ook een engelse formule invoeren die dan in de actieve cel in de geldende taal wordt geplaatst.
De UDF is naar aanleiding van een vraag in topic #29. De functie kan worden aangeroepen door in een cel =FOK() te typen en dan op de Fx te klikken voor uitleg over de variabelen.
De VBA editor bereik je door ALT+F11 in te drukken. Vanaf Excel 2007 is de VBA editor lastiger te vinden. Op deze site wordt uitgelegd hoe je de editor kan activeren.
De interne functies zijn afhankelijk van welke taal jouw Excel draait. Op deze en deze site kun je de formules in verschillende talen vertalen.
Macro uitvoering optimaliseren
De meer uitgebreide macro's kunnen traag worden, ook kan het scherm flikkeren als gevolg van veel "select" acties. Deels kun je dit ondervangen door zo veel mogelijk select acties te vervangen door directe verwijzingen naar cells en deels door een macro-optimalisatie door te voeren. In de onderstaande code is een routine voor macro-optimalisatie en een routine om de optimalisatie weer uit te zetten weergegeven. In de voorbeeld sheet is een uitgebreide versie van MOA en MOU opgenomen.
SPOILEROm 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.Bij macro's die een bepaalde, merkbare, tijd nodig hebben om hun werk te doen is het van belang de gebruiker een idee te geven van wat er gebeurt, zeker met macro-optimalisatie aan kan het lijken alsof Excel is vastgelopen. Je kan bijvoorbeeld de statusbar een waarde geven, met eventueel een percentage van de reeds uitgevoerde code.SPOILEROm 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.Password kwijt?
Soms heb je een worksheeet beveiligd met een password maar ben je die kwijt. Gelukkig is dat geen groot probleem want de interne beveiliging van Excel stelt niet heel veel voor.
Hier vind je meer info en een downloadbare macro die alle interne passwords verwijdert.
DISCLAIMER: Denk erom dat je met het breken van de beveiliging van een excelsheet mogelijk wet- of regelgeving overtreedt. Als de spreadsheet van jezelf is zou het geen probleem moeten zijn.
In andere gevallen: vraag toestemming aan de originele schrijver.
Bij twijfelgevallen: NIET DOEN
Mooi, grappig en cool
Een uitgebreide site met nuttige en minder nuttige excelsheetjes. Van kekke grafieken om je manager of leraar te betoveren via oscillogrammen en simulatoren tot pong voor als het weer een typische vrijdagmiddag is: Excelunusual
Verder
Alle vorige topics zijn hier terug te vinden.
Nieuw topic? Haal de OP uit de wiki
[ Bericht 0% gewijzigd door Tegan op 13-11-2020 15:55:44 ]
Ik denk dat (als je de brondata ongemoeid wilt laten) je dit ook wel via Power Query voor elkaar kan krijgen. Je laat via de query dan je sheet vullen met de gewenste data, rechtstreeks vanuit de database of via een dump bestand waar je de query in laat kijken. Je kunt dan een rule op de betreffende kolom zetten dat hij de cel leeg laat of bijv. met null of een bepaalde tekst invult als de waarde niet overeenkomt met 1, 2, 3 of 4.quote:Op donderdag 22 oktober 2020 21:16 schreef kalinhos het volgende:
[..]
Ik wil dat als ik de data uit mn database haal (met oracle sql) en ze in de excel template plak, dattie in (bijvoorbeeld) kolom C alleen de waarden 1,2,3 en 4 kan weergeven in de 20k rijen. Ik wil dus eigenlijk dattie een waarschuwing geeft, of rood wordt ofzo, als ik vanuit mn data een waarde in de template plak die daar niet zou mogen horen.
Ik zat moeilijk te denken, voorwaardelijke opmaak op dubbele waarden werkt makkelijkerquote:Op vrijdag 13 november 2020 15:43 schreef Tegan het volgende:
[..]
Voorwaardelijke opmaak met de formule AANTAL.ALS() ?
.
Ik heb een tekst in 1 cel die luidt:
" Aa_bbb_1122020_prod_to_test_AB150 Notification: x83"
Dit is een onderwerp van een mail die ik vanaf Outlook kopieer naar een Excel
Via SPATIES.WISSEN, DEEL en/of VIND.SPEC kan ik zo in een aantal kolommen daarachter de nodige data halen. Ik kan AB150 eruit filteren, ik kan x83 eruit filteren en ik kan 01122020 eruit filteren
Het probleem is dat die dan 01122020 niet ziet als datum. Wat moet ik doen om dat wel voor elkaar te krijgen?
Ik wil dit namelijk met een andere datum vergelijken en die cellen/kolom herkent excel wel als datum.
" Aa_bbb_1122020_prod_to_test_AB150 Notification: x83"
Dit is een onderwerp van een mail die ik vanaf Outlook kopieer naar een Excel
Via SPATIES.WISSEN, DEEL en/of VIND.SPEC kan ik zo in een aantal kolommen daarachter de nodige data halen. Ik kan AB150 eruit filteren, ik kan x83 eruit filteren en ik kan 01122020 eruit filteren
Het probleem is dat die dan 01122020 niet ziet als datum. Wat moet ik doen om dat wel voor elkaar te krijgen?
Ik wil dit namelijk met een andere datum vergelijken en die cellen/kolom herkent excel wel als datum.
Het is...kiezen of delen, spelen winnen verliezen of vervelen en helemaal niets ondernemen
=DATUM(RECHTS(A1;4);DEEL(A1;3;2);LINKS(A1;2))quote:
Ik heb een tekst in 1 cel die luidt:
" Aa_bbb_1122020_prod_to_test_AB150 Notification: x83"
Dit is een onderwerp van een mail die ik vanaf Outlook kopieer naar een Excel
Via SPATIES.WISSEN, DEEL en/of VIND.SPEC kan ik zo in een aantal kolommen daarachter de nodige data halen. Ik kan AB150 eruit filteren, ik kan x83 eruit filteren en ik kan 01122020 eruit filteren
Het probleem is dat die dan 01122020 niet ziet als datum. Wat moet ik doen om dat wel voor elkaar te krijgen?
Ik wil dit namelijk met een andere datum vergelijken en die cellen/kolom herkent excel wel als datum.
Als de bewuste datum in A1 staan. Dit werkt wel alleen als de output-datum altijd 8 karakters heeft.
Opinion is the medium between knowledge and ignorance (Plato)
dank u! Altijd zo'n getob vind ik die datunms.quote:
[..]
=DATUM(RECHTS(A1;4);DEEL(A1;3;2);LINKS(A1;2))
Als de bewuste datum in A1 staan. Dit werkt wel alleen als de output-datum altijd 8 karakters heeft.
Edit: het blijft een getob. Als ik de datumcel via verticaal zoeken oproep herkent excel het weer niet als datum
[ Bericht 15% gewijzigd door kalinhos op 02-12-2020 17:06:30 ]
Het is...kiezen of delen, spelen winnen verliezen of vervelen en helemaal niets ondernemen
Ik heb zó vaak zitten kloten met dit soort data uit .csv-achtige bestanden dat ik dit bijna blind kanquote:
[..]
dank u! Altijd zo'n getob vind ik die datunms.
Opinion is the medium between knowledge and ignorance (Plato)
Watvoor fout krijg je dan? Waarop verticaal zoek je precies?quote:
[..]
dank u! Altijd zo'n getob vind ik die datunms.
Edit: het blijft een getob. Als ik de datumcel via verticaal zoeken oproep herkent excel het weer niet als datum
Hier werkt het zoals je zou verwachten.
Die datum uit jouw voorbeeld, was dat 1 december of 12 januari?quote:
[..]
dank u! Altijd zo'n getob vind ik die datunms.
Edit: het blijft een getob. Als ik de datumcel via verticaal zoeken oproep herkent excel het weer niet als datum
Opinion is the medium between knowledge and ignorance (Plato)
Ik heb de formule van Janneke in kolom P gezetquote:
[..]
Watvoor fout krijg je dan? Waarop verticaal zoek je precies?
Hier werkt het zoals je zou verwachten.
In een ander tabblad zoek ik op kolom L om de waarde uit die kolom P te krijgen. Maar dan krijg ik weer de 01122020 output
Maargoed: ik kan in dat tabblad natuurlijk ook weer Jannekes formule gebruiken en die in de kolom ernaast zetten en vervolgens de originele bron verbergen.
Het is...kiezen of delen, spelen winnen verliezen of vervelen en helemaal niets ondernemen
Heb je dan niet het kolomnummer in je VERT.ZOEKEN verkeerd?quote:
[..]
Ik heb de formule van Janneke in kolom P gezet
In een ander tabblad zoek ik op kolom L om de waarde uit die kolom P te krijgen. Maar dan krijg ik weer de 01122020 output
Maargoed: ik kan in dat tabblad natuurlijk ook weer Jannekes formule gebruiken en die in de kolom ernaast zetten en vervolgens de originele bron verbergen.
Opinion is the medium between knowledge and ignorance (Plato)
Nee dat is niet nodig. Dit klinkt eerder als dat je de verkeerde kolom opzoekt met vert.zoeken().quote:
[..]
Ik heb de formule van Janneke in kolom P gezet
In een ander tabblad zoek ik op kolom L om de waarde uit die kolom P te krijgen. Maar dan krijg ik weer de 01122020 output
Maargoed: ik kan in dat tabblad natuurlijk ook weer Jannekes formule gebruiken en die in de kolom ernaast zetten en vervolgens de originele bron verbergen.
Ohja ik zie het. Nja dank 
Het is...kiezen of delen, spelen winnen verliezen of vervelen en helemaal niets ondernemen
Met behulp van
op 1.23 wil ik mijn formule voorzien van als.fout
=VERT.ZOEKEN(D2;Blad1!A:H;1;ONWAAR) <---- waar zet ik de haakjes en de tekst die ik kwijt wil? Want ik krijg alleen maar foutmeldingen
[ Bericht 1% gewijzigd door onlogisch op 04-12-2020 08:35:26 ]
op 1.23 wil ik mijn formule voorzien van als.fout
=VERT.ZOEKEN(D2;Blad1!A:H;1;ONWAAR) <---- waar zet ik de haakjes en de tekst die ik kwijt wil? Want ik krijg alleen maar foutmeldingen
[ Bericht 1% gewijzigd door onlogisch op 04-12-2020 08:35:26 ]
=ALS.FOUT( <je formule>;"tekst")quote:
Met behulp van
op 1.23 wil ik mijn formule voorzien van als.fout
=VERT.ZOEKEN(D2;Blad1!A:H;1;ONWAAR) <---- waar zet ik de haakjes en de tekst die ik kwijt wil? Want ik krijg alleen maar foutmeldingen
dus
=ALS.FOUT(VERT.ZOEKEN(D2;Blad1!A:H;1;ONWAAR);"")
--edit--
Even iets meer uitleg erbij.
Wanneer je waarde D2 ongeldig is (door het delen door 0), dan kan Excel die vertikaal zoeken actie ook niet uitvoeren. Daardoor levert niet alleen die cel D2 een fout op, maar ook je vertikaal zoeken zelf levert een fout op. Door de ALS FOUT om de vertikaal zoeken heen te zetten, vang je 2 foute scenario's af:
Scenario 1: Er zit een fout in D2 waardoor een fout ontstaat
Scenario 2: Vertikaal zoeken kan het resultaat niet vinden in Blad1 kolom A
Wanneer je echt alleen wilt afvangen dat D2 ongeldig is en niet wilt afvangen scenario 2 dat vertikaal zoeken fout gaat, dan kom je in een complexere formule terecht. Zoiets als:
=ALS(ISFOUT(D2;"de waarde in D2 is fout";VERT.ZOEKEN(D2;Blad1!A:H;1;ONWAAR))
[ Bericht 23% gewijzigd door snabbi op 04-12-2020 08:50:47 ]
Onwaar gebruiken in de VLOOKUP. Noob.quote:
Met behulp van
op 1.23 wil ik mijn formule voorzien van als.fout
=VERT.ZOEKEN(D2;Blad1!A:H;1;ONWAAR) <---- waar zet ik de haakjes en de tekst die ik kwijt wil? Want ik krijg alleen maar foutmeldingen
Maar zoiets:
=ALS.FOUT(VERT.ZOEKEN(D2;Blad1!A:H;1;0);"Je dikke hoerenmoeder op een driewieler")
Dank je wel!quote:
[..]
=ALS.FOUT( <je formule>;"tekst")
dus
=ALS.FOUT(VERT.ZOEKEN(D2;Blad1!A:H;1;ONWAAR);"")
=(995-25)/(1-0,03)quote:
Even crosspost voor de rekenwonders onder ons: SES / Procentrekenen voor gevorderden (Excel?)
stupidity has become as common as common sense was before
Misschien kan iemand mij hier helpen:
Ik heb echt 0.0 verstand van excel, echter heb ik nu een bepaalde functie nodig in excel.
Het gaat om het volgende.
Ik heb in 357 cellen onder elkaar A, B, C of D staan.
Welke functie gebruik ik, zodat ik kan aangeven dat er in al die cellen totaal maar 4 verschillende letters voor komen?
Ik moet op werkblad 3 dus aan kunnen geven in cel B4=4
4 is dus het X aantal unike letters dan in die kolom voorkomt.
Ik heb echt 0.0 verstand van excel, echter heb ik nu een bepaalde functie nodig in excel.
Het gaat om het volgende.
Ik heb in 357 cellen onder elkaar A, B, C of D staan.
Welke functie gebruik ik, zodat ik kan aangeven dat er in al die cellen totaal maar 4 verschillende letters voor komen?
Ik moet op werkblad 3 dus aan kunnen geven in cel B4=4
4 is dus het X aantal unike letters dan in die kolom voorkomt.
The Older I Get, The Better I Was
Staan die letters telkens in 1 cel? Dus in A1 "A", in A2 "ACD", in A3 "B" etc. of staan ze in verschillende kolommen?quote:
Misschien kan iemand mij hier helpen:
Ik heb echt 0.0 verstand van excel, echter heb ik nu een bepaalde functie nodig in excel.
Het gaat om het volgende.
Ik heb in 357 cellen onder elkaar A, B, C of D staan.
Welke functie gebruik ik, zodat ik kan aangeven dat er in al die cellen totaal maar 4 verschillende letters voor komen?
Ik moet op werkblad 3 dus aan kunnen geven in cel B4=4
4 is dus het X aantal unike letters dan in die kolom voorkomt.
Zitten er dubbele letters in?
Als alles in 1 kolom staat en er staan geen dubbelen in, dan kun je met de functie =LENGTE() denk ik een heel eind komen.
Opinion is the medium between knowledge and ignorance (Plato)
De letters staan in Cel A2 tot en met A357quote:
[..]
Staan die letters telkens in 1 cel? Dus in A1 "A", in A2 "ACD", in A3 "B" etc. of staan ze in verschillende kolommen?
Zitten er dubbele letters in?
Als alles in 1 kolom staat en er staan geen dubbelen in, dan kun je met de functie =LENGTE() denk ik een heel eind komen.
Daar staan er dus in elke Cel 1 letter. En wil dus een nieuwe cel dat de hoeveelheid letters weergeeft.
The Older I Get, The Better I Was
Maar als je al weet dat er alleen A-B-C-D in staat dan is het antwoord toch gewoon '4', of begrijp ik nu iets helemaal verkeerd?quote:
[..]
De letters staan in Cel A2 tot en met A357
Daar staan er dus in elke Cel 1 letter. En wil dus een nieuwe cel dat de hoeveelheid letters weergeeft.
Opinion is the medium between knowledge and ignorance (Plato)
Kan je niet helemaal volgen.quote:
Misschien kan iemand mij hier helpen:
Ik heb echt 0.0 verstand van excel, echter heb ik nu een bepaalde functie nodig in excel.
Het gaat om het volgende.
Ik heb in 357 cellen onder elkaar A, B, C of D staan.
Welke functie gebruik ik, zodat ik kan aangeven dat er in al die cellen totaal maar 4 verschillende letters voor komen?
Ik moet op werkblad 3 dus aan kunnen geven in cel B4=4
4 is dus het X aantal unike letters dan in die kolom voorkomt.
Maar als ik je goed snapt is de functie die je wil er niet.
Gebruik autofilter om snel te tellen.
Som.als op a, b, c, d, en dit dan vergelijken met het totaal aantal waarden
Of de kolom kopieren en uniek maken.
stupidity has become as common as common sense was before
Als je hier met alle geweld formules tegenaan wil gooien dan kun je het volgende doen:
Gebruik B als hulpkolom, dus de kolom naast je letterkolom.
In B1 zet je
=AANTAL.ALS(A$1:A1;A1)
En die kopieer je naar beneden tot het einde van je kolom.
En de cel waar je het aantal verschillende letters wil hebben gebruik je
=AANTAL.ALS(B1:B357;1)
Die zou je antwoord op moeten leveren.
Gebruik B als hulpkolom, dus de kolom naast je letterkolom.
In B1 zet je
=AANTAL.ALS(A$1:A1;A1)
En die kopieer je naar beneden tot het einde van je kolom.
En de cel waar je het aantal verschillende letters wil hebben gebruik je
=AANTAL.ALS(B1:B357;1)
Die zou je antwoord op moeten leveren.
Opinion is the medium between knowledge and ignorance (Plato)
Ja dat is wel zo, maar ik wil kunnen aantonen hoe ik aan die 4 gekomen ben zeg maar.quote:
[..]
Maar als je al weet dat er alleen A-B-C-D in staat dan is het antwoord toch gewoon '4', of begrijp ik nu iets helemaal verkeerd?
The Older I Get, The Better I Was
Ik heb geen excel bij de hand nu. Maar misschien iets met tellen en subtotalen?
Hier staat een uitleg van wat ik bedoel:
https://support.microsoft(...)SOM%20en%20subtotaal.
Hier staat een uitleg van wat ik bedoel:
https://support.microsoft(...)SOM%20en%20subtotaal.
Misschien is het duidelijk als ik het zo uitleg:quote:
[..]
Kan je niet helemaal volgen.
Maar als ik je goed snapt is de functie die je wil er niet.
Gebruik autofilter om snel te tellen.
Som.als op a, b, c, d, en dit dan vergelijken met het totaal aantal waarden
Of de kolom kopieren en uniek maken.
Ik heb een enquête waaraan een x aantal scholen hebben meegedaan.
Die scholen zijn aangegeven met letters ipv namen.
Nu wil ik dus in een aparte cel aangeven hoeveel scholen hebben meegedaan.
Dus ipv dat ik alle 357 records die letters ga tellen. Wil ik gewoon een functie die mij gelijk aangeeft om hoeveel scholen het gaat.
Zodat als ik een school een andere letter geef, die in de cel ook aangepast wordt.
The Older I Get, The Better I Was
ooooohhhquote:
[..]
Misschien is het duidelijk als ik het zo uitleg:
Ik heb een enquête waaraan een x aantal scholen hebben meegedaan.
Die scholen zijn aangegeven met letters ipv namen.
Nu wil ik dus in een aparte cel aangeven hoeveel scholen hebben meegedaan.
Dus ipv dat ik alle 357 records die letters ga tellen. Wil ik gewoon een functie die mij gelijk aangeeft om hoeveel scholen het gaat.
Zodat als ik een school een andere letter geef, die in de cel ook aangepast wordt.
=AANTALARG(G:G)
"Fifty years ago the Leningrad street taught me a rule - if a fight is inevitable, you have to throw the first punch."
Vladimir Putin
“To forgive the terrorists is up to God, but to send them there is up to me.”
Vladimir Putin
Vladimir Putin
“To forgive the terrorists is up to God, but to send them there is up to me.”
Vladimir Putin
Dit geeft je het totaal aantal entries in die kolom. Levert in dit geval dus 357 op, en niet 4.quote:
Op
Opinion is the medium between knowledge and ignorance (Plato)
Dan kan ie beter even een screenshotje maken van zijn situatie op zijn spreadsheet. Kunnen we hem wat beter helpen.quote:
[..]
Dit geeft je het totaal aantal entries in die kolom. Levert in dit geval dus 357 op, en niet 4.
"Fifty years ago the Leningrad street taught me a rule - if a fight is inevitable, you have to throw the first punch."
Vladimir Putin
“To forgive the terrorists is up to God, but to send them there is up to me.”
Vladimir Putin
Vladimir Putin
“To forgive the terrorists is up to God, but to send them there is up to me.”
Vladimir Putin
Ik zou een tweede kolom maken met en daar met een aantal.als() een telling doen. Dan zie je vanzelf welke letters meedoen.quote:
[..]
Misschien is het duidelijk als ik het zo uitleg:
Ik heb een enquête waaraan een x aantal scholen hebben meegedaan.
Die scholen zijn aangegeven met letters ipv namen.
Nu wil ik dus in een aparte cel aangeven hoeveel scholen hebben meegedaan.
Dus ipv dat ik alle 357 records die letters ga tellen. Wil ik gewoon een functie die mij gelijk aangeeft om hoeveel scholen het gaat.
Zodat als ik een school een andere letter geef, die in de cel ook aangepast wordt.
Of misschien wel met vert.zoeekn(), dan geeft ie iedere keer de waarde weer als die voorkomt, en maar 1 keer.
stupidity has become as common as common sense was before
Er vanuit gaande dat je een recente versie van Excel hebt en de letters in kolom A staan met een kop er boven:
=AANTALARG(UNIEK(A:A))-2
Zonder kop =AANTALARG(UNIEK(A:A))-1
Min -1 of -2 omdat een lege cel ook als uniek wordt geteld.
=AANTALARG(UNIEK(A:A))-2
Zonder kop =AANTALARG(UNIEK(A:A))-1
Min -1 of -2 omdat een lege cel ook als uniek wordt geteld.
Jij bent een topper!! Dit was het dus.quote:
Er vanuit gaande dat je een recente versie van Excel hebt en de letters in kolom A staan met een kop er boven:
=AANTALARG(UNIEK(A:A))-2
Zonder kop =AANTALARG(UNIEK(A:A))-1
Min -1 of -2 omdat een lege cel ook als uniek wordt geteld.
The Older I Get, The Better I Was
Ik heb een kolom (kolom C) met datums.
Ik heb een kolom (kolom H) waar in ik met de hand Ja of Nee in typ.
Wat ik graag zou willen is:
Indien de waarde (datum) uit kolom C ouder is dan vandaag EN de tekst in kolom H is Nee, dan moet de de cel in kolom H een kleur krijgen.
Indien de waarde (datum) uit kolom C ouder is dan vandaag, maar de tekst in kolom H is Ja, dan moet er niks gebeuren.
Welke formule heb ik hiervoor nodig en hoe kan ik dit toepassen op alle cellen in kolom H?
Iets met:
$C<TODAY(), maar ik kom er niet helemaal uit
Ik heb een kolom (kolom H) waar in ik met de hand Ja of Nee in typ.
Wat ik graag zou willen is:
Indien de waarde (datum) uit kolom C ouder is dan vandaag EN de tekst in kolom H is Nee, dan moet de de cel in kolom H een kleur krijgen.
Indien de waarde (datum) uit kolom C ouder is dan vandaag, maar de tekst in kolom H is Ja, dan moet er niks gebeuren.
Welke formule heb ik hiervoor nodig en hoe kan ik dit toepassen op alle cellen in kolom H?
Iets met:
$C<TODAY(), maar ik kom er niet helemaal uit
Voorwaardelijke opmaak op H1:H9999 met formule EN(C1<VANDAAG();H1="Nee") .
Ik kan het nu niet testen, maar zoiets...
$ hoeft alleen als je meerdere kolommen wil kleuren.
Ik kan het nu niet testen, maar zoiets...
$ hoeft alleen als je meerdere kolommen wil kleuren.
Dus kolom H selecteren, Voorwaardelijke opmaak, nieuwe regel op basis van een formule. En dat moet zijn:quote:
Voorwaardelijke opmaak op H1:H9999 met formule EN(C1<VANDAAG();H1="Nee") .
Ik kan het nu niet testen, maar zoiets...
$ hoeft alleen als je meerdere kolommen wil kleuren.
EN(C1<VANDAAG();H1="Nee")
Geen error, maar doet ook niks.
Dat H1:H9999 is essentieel...quote:
[..]
Dus kolom H selecteren, Voorwaardelijke opmaak, nieuwe regel op basis van een formule. En dat moet zijn:
EN(C1<VANDAAG();H1="Nee")
Geen error, maar doet ook niks.
En de opmaak die toegepast moet worden moet ook nog ingevuld zijn.
$H$1:$H$9999 is juist. Ik bedoelde dat $H:$H onjuist was en dat $ in EN(C1<VANDAAG();H1="Nee") niet nodig is.
Klopt de formule nog wel, Excel kan nog eens vervelend doen als je het bereik wijzigt.
Klopt de formule nog wel, Excel kan nog eens vervelend doen als je het bereik wijzigt.
Excel maakt er dit van als Formule: ="EN(C1<VANDAAG();H1=""Nee"")"quote:
$H$1:$H$9999 is juist. Ik bedoelde dat $H:$H onjuist was en dat $ in EN(C1<VANDAAG();H1="Nee") niet nodig is.
Klopt de formule nog wel, Excel kan nog eens vervelend doen als je het bereik wijzigt.
En bij Van toepassing op: $H$1:$H$99999
de eerste regel met " bij de EN lijkt niet correct te zijn:quote:
[..]
Excel maakt er dit van als Formule: ="EN(C1<VANDAAG();H1=""Nee"")"
En bij Van toepassing op: $H$1:$H$99999
Computer opgestart en mijn testje werkt ook.
Heb je toevallig Engelse Excel? Dan werken de Nederlandse formules niet.
Heb je toevallig Engelse Excel? Dan werken de Nederlandse formules niet.
quote:
[..]
de eerste regel met " bij de EN lijkt niet correct te zijn:
[ afbeelding ]
/edit
Opgelost. Foutje. Sorry.
Formule moest zijn: =EN(C1<VANDAAG();H1="Nee")
Kennelijk ergens een copy/paste fout gemaakt.
Ik gebruikt Excel365 zowel Nederlands als Engels.
Voor mijn werk heb ik een tamelijk groot excel sheet gemaakt met een heel wat macro's. Hier reken ik ook met datums en weeknummers.
Tot mijn schrik denkt Excel blijkbaar dat het nu week 2 is terwijl mijn kalender echt week 1 aangeeft.
=WEEKNUMMER(NU())
=WEEKNUM(NOW())
Beide formules hebben 2 als resultaat.
Weet iemand of dit een bug is die waarschijnlijk snel opgelost gaat worden of heb ik heel 2021 een verkeerde weeknummer in mijn sheet?
Voor mijn werk heb ik een tamelijk groot excel sheet gemaakt met een heel wat macro's. Hier reken ik ook met datums en weeknummers.
Tot mijn schrik denkt Excel blijkbaar dat het nu week 2 is terwijl mijn kalender echt week 1 aangeeft.
=WEEKNUMMER(NU())
=WEEKNUM(NOW())
Beide formules hebben 2 als resultaat.
Weet iemand of dit een bug is die waarschijnlijk snel opgelost gaat worden of heb ik heel 2021 een verkeerde weeknummer in mijn sheet?
https://support.microsoft(...)6c-b411-b18c13c75340quote:
Weet iemand of dit een bug is die waarschijnlijk snel opgelost gaat worden of heb ik heel 2021 een verkeerde weeknummer in mijn sheet?
Even extra parameter toevoegen:
Systeem 1 De week met 1 januari is de eerste week van het jaar en wordt genummerd als week 1.
Systeem 2 De week met de eerste donderdag van het jaar is de eerste week van het jaar en wordt genummerd als week 1. Dit systeem is de methodologie die is vastgelegd in ISO 8601, dat algemeen bekend staat als Europees systeem voor weeknummering.
Waarschijnlijk wil je type_resultaat 21, zodat systeem 2 gebruikt wordt.
Er bestaan simpelweg twee manieren om de eerste week van het jaar te bepalen, en blijkbaar ben jij het nu niet eens met de manier die excel standaard kiest. Zie https://support.microsoft(...)6c-b411-b18c13c75340 voor hoe de WEEKNUMMER() functie werkt, daar staat ook uitgelegd hoe je kiest welk systeem je wilt gebruiken.quote:
Ik gebruikt Excel365 zowel Nederlands als Engels.
Voor mijn werk heb ik een tamelijk groot excel sheet gemaakt met een heel wat macro's. Hier reken ik ook met datums en weeknummers.
Tot mijn schrik denkt Excel blijkbaar dat het nu week 2 is terwijl mijn kalender echt week 1 aangeeft.
=WEEKNUMMER(NU())
=WEEKNUM(NOW())
Beide formules hebben 2 als resultaat.
Weet iemand of dit een bug is die waarschijnlijk snel opgelost gaat worden of heb ik heel 2021 een verkeerde weeknummer in mijn sheet?
--edit-- spuit 11, ik zie dat Piles me al voor was
Zou het ook nog met de land en toetsenbord instellingen geregeld kunnen worden zoals excel daar automagisch de scheidingstekens vandaan haalt.quote:
SHO / Verdrietig nieuws: static is onverwacht overleden
Toch wel iemand die hier regelmatig langs kwam
Toch wel iemand die hier regelmatig langs kwam
It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]
[i]What would life be like without rhethorical questions?[/i]
Inderdaad, ISO.WEEKNUMMER() geeft de Europese weeknummers aan, zonder de Amerikaanse.quote:
Ik gebruik altijd ISO.WEEKNUMMER() hierom.
Zelfde overigens met week die start op zondag ipv maandag als je het niet goed instelt.
Die kende ik niet...quote:
Ik gebruik altijd ISO.WEEKNUMMER() hierom.
Echt, die naamgeving af en toe. Waarom niet WEEKNUMMER.ISO, das toch veel handiger...
Ik erger me ook altijd aan dat de Engelse formules niet werken in Nederlandse Excel en dat het zelfs niet een exacte vertaling is.
Ik begin langzaamaan een beetje gek te worden. In Google Sheets heb ik de volgende formule: =SUM(C2:C4). In de cellen staan ook getallen, maar de uitkomst is 0. De totale som van de cellen zou dan toch moeten worden weergeven? Wat doe ik fout?
Uitvinder van de biersmiley.
Iets met de "celeigenschappen"?
Google zegt mij dat dat hier staat:
"Opmaak en dan Getal en dan Meer notaties.
Klik op Meer datum- en tijdnotaties."
Google zegt mij dat dat hier staat:
"Opmaak en dan Getal en dan Meer notaties.
Klik op Meer datum- en tijdnotaties."
Zou kunnen dat de getallen zijn opgemaakt als tekst. Dat kun je wijzigingen via celeigenschappen inderdaad.quote:
Ik begin langzaamaan een beetje gek te worden. In Google Sheets heb ik de volgende formule: =SUM(C2:C4). In de cellen staan ook getallen, maar de uitkomst is 0. De totale som van de cellen zou dan toch moeten worden weergeven? Wat doe ik fout?
Screenshot anders?
You're Fucking Out, I'm Fucking In
quote:
Iets met de "celeigenschappen"?
Google zegt mij dat dat hier staat:
"Opmaak en dan Getal en dan Meer notaties.
Klik op Meer datum- en tijdnotaties."
Thanks, dit was het inderdaad.quote:
[..]
Zou kunnen dat de getallen zijn opgemaakt als tekst. Dat kun je wijzigingen via celeigenschappen inderdaad.
Screenshot anders?
Uitvinder van de biersmiley.
Ik heb 3 kolommen D, E en F.
In kolom D staan de geboortejaren, maar er staat alleen 92 of 93 of 90
En kolom E staan de geboortemaanden 4 of 6 of 9 etc.
Kan iemand mij uitleggen hoe ik in Kolom F de actuele leeftijd aangeven?
In kolom D staan de geboortejaren, maar er staat alleen 92 of 93 of 90
En kolom E staan de geboortemaanden 4 of 6 of 9 etc.
Kan iemand mij uitleggen hoe ik in Kolom F de actuele leeftijd aangeven?
The Older I Get, The Better I Was
Je hebt geen geboortedag begrijp ik hieruit?quote:
Ik heb 3 kolommen D, E en F.
In kolom D staan de geboortejaren, maar er staat alleen 92 of 93 of 90
En kolom E staan de geboortemaanden 4 of 6 of 9 etc.
Kan iemand mij uitleggen hoe ik in Kolom F de actuele leeftijd aangeven?
Zijn die jaartallen allemaal van voor 2000, of zijn er ook cellen met bijvoorbeeld 01 erin voor 2001?
Met =VANDAAG() krijg je de datum van vandaag, en met DATUM(jaar;maand;dag) krijg je een datum op basis van losse getallen. Als je ze van elkaar afhaalt, deelt door 365,25 en dan afrondt op een manier die je bevalt heb je een leeftijd.
Opinion is the medium between knowledge and ignorance (Plato)
Ik heb inderdaad geen dagen. En zijn inderdaad alleen van rond begin 90.quote:
[..]
Je hebt geen geboortedag begrijp ik hieruit?
Zijn die jaartallen allemaal van voor 2000, of zijn er ook cellen met bijvoorbeeld 01 erin voor 2001?
Met =VANDAAG() krijg je de datum van vandaag, en met DATUM(jaar;maand;dag) krijg je een datum op basis van losse getallen. Als je ze van elkaar afhaalt, deelt door 365,25 en dan afrondt op een manier die je bevalt heb je een leeftijd.
Wat jij zegt was ik al achter, maar omdat ik geen dagen heb kom ik er niet uit.
The Older I Get, The Better I Was
Nou, dan maak je met =DATUM(D1+1900;E1;1) een datum die gewoon de eerste van de maand pakt. Je kunt ook de 15e pakken als je dat logischer vindt. De 30e zou ik je in elk geval afraden.quote:
[..]
Ik heb inderdaad geen dagen. En zijn inderdaad alleen van rond begin 90.
Wat jij zegt was ik al achter, maar omdat ik geen dagen heb kom ik er niet uit.
=AFRONDEN.NAAR.BENEDEN(VANDAAG()-DATUM(D1+1900;E1;1);0) geeft volgens mij wel wat je wil.
Opinion is the medium between knowledge and ignorance (Plato)
Volgens mij moet ik het dan nog ergens delen voor 365,25 toch?quote:
[..]
Nou, dan maak je met =DATUM(D1+1900;E1;1) een datum die gewoon de eerste van de maand pakt. Je kunt ook de 15e pakken als je dat logischer vindt. De 30e zou ik je in elk geval afraden.
=AFRONDEN.NAAR.BENEDEN(VANDAAG()-DATUM(D1+1900;E1;1);0) geeft volgens mij wel wat je wil.
The Older I Get, The Better I Was
Oh ja, heb je helemaal gelijk in. Het moet inderdaad (VANDAAG()-DATUM(blabla))/365,25 worden.quote:
[..]
Volgens mij moet ik het dan nog ergens delen voor 365,25 toch?
Opinion is the medium between knowledge and ignorance (Plato)
Thanks voor je hulp. Het werkt.quote:
[..]
Oh ja, heb je helemaal gelijk in. Het moet inderdaad (VANDAAG()-DATUM(blabla))/365,25 worden.
The Older I Get, The Better I Was
Hoe kun je in een tekst-cel bijvoorbeeld "de derde spatie" vinden?
Ik heb namelijk een bestand wat ik in een deugdelijke database wil plaatsen.
Hierbij moet ik "Rembrandtstraat 23" splitsen in een aparte kolom "Rembrandtstraat" en "23".
Dus: Straat en huisnummer.
Als alle straatnamen geen spaties zouden hebben, kan ik met =find(" ";a2) de plek traceren van de scheiding tussen straatnaam en huisnummer en dan met mid(a2;+b2;6) of zo nieuwe kolommen maken.
En dan met paste values en zo.
Enfin je hebt ook straatnamen als "Professor Huppelepuplaan" of "Hoog en droog". Kortom meer dan 1 spatie en ik moet de splitsing maken in de laatste spatie.
Nu kan ik via "substitute(a2;" ";"") er achter komen hoeveel spaties er in een cel zitten. Door de lengtes te vergelijken van de bewerkte tekst (via substitute) en de oorspronkelijke tekst.
De volgende uitdaging is dan dat je in één commando de plek van de laatste spatie kunt traceren.
Wie weet dit?
Ik heb namelijk een bestand wat ik in een deugdelijke database wil plaatsen.
Hierbij moet ik "Rembrandtstraat 23" splitsen in een aparte kolom "Rembrandtstraat" en "23".
Dus: Straat en huisnummer.
Als alle straatnamen geen spaties zouden hebben, kan ik met =find(" ";a2) de plek traceren van de scheiding tussen straatnaam en huisnummer en dan met mid(a2;+b2;6) of zo nieuwe kolommen maken.
En dan met paste values en zo.
Enfin je hebt ook straatnamen als "Professor Huppelepuplaan" of "Hoog en droog". Kortom meer dan 1 spatie en ik moet de splitsing maken in de laatste spatie.
Nu kan ik via "substitute(a2;" ";"") er achter komen hoeveel spaties er in een cel zitten. Door de lengtes te vergelijken van de bewerkte tekst (via substitute) en de oorspronkelijke tekst.
De volgende uitdaging is dan dat je in één commando de plek van de laatste spatie kunt traceren.
Wie weet dit?
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
Kun je dan niet beter een formule vinden voor het vinden van alle getallen in een string? Dan maakt de opbouw van de straatnaam niet meer uit. Moet je alleen even rekening houden met huisnummertoevoegingen (als die er zijn).quote:
Hoe kun je in een tekst-cel bijvoorbeeld "de derde spatie" vinden?
In Nederland kennen we straatnamen die "Plein 1945" heten, dan heb je al een probleem. Maar volgens mij moet het kunnen: Een soort "vind de derde spatie" Of vind de 20e "a". Het gaat me nu ook een beetje om het principe.quote:
[..]
Kun je dan niet beter een formule vinden voor het vinden van alle getallen in een string? Dan maakt de opbouw van de straatnaam niet meer uit. Moet je alleen even rekening houden met huisnummertoevoegingen (als die er zijn).
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
Misschien is dit een idee. Met de 1 in substitueren(a2;" ";"";1) kun je meegeven dat alleen de eerste spatie vervangen wordt, met substitueren(a2;" ";"";2) alleen de tweede, etc. Als je weet dat er drie spaties zijn kun je 1 en 2 vervangen (met een als() kun je het alle spaties behalve de laatste vervangen) om vervolgens een tekst over te houden met maar één spatie op de juiste plek. En dan kun je de rest gebruiken wat je al had.
Dan zoiets:quote:
[..]
In Nederland kennen we straatnamen die "Plein 1945" heten, dan heb je al een probleem. Maar volgens mij moet het kunnen: Een soort "vind de derde spatie" Of vind de 20e "a". Het gaat me nu ook een beetje om het principe.
=VIND.ALLES(TEKEN(1);SUBSTITUEREN(B5;" ";TEKEN(1);3))
Die 3 geeft aan dat het de derde spatie moet zijn
Geen garanties, ik google het ook maar bij elkaar
Ik doe het iets anders, maar dankzij jou ben ik er uit.quote:
[..]
Dan zoiets:
=VIND.ALLES(TEKEN(1);SUBSTITUEREN(B5;" ";TEKEN(1);3))
Die 3 geeft aan dat het de derde spatie moet zijn
Geen garanties, ik google het ook maar bij elkaar
Zie dit commando:
=SUBSTITUTE(G15;" ";"#";L15)
Er van uitgaande dat geen enkele straatnaam en geen enkel huisnummer een "#" (hekje) in zich herbergt.
L15 staat voor het aantal spaties dat ik in de cel heb gevonden.
G15 staat voor de tekst van de oorspronkelijke cel inclusief alle spaties.
En rechts naast heb substitute commando ga ik via "find.spec" op zoek naar de plek waar "#" staat.
Dank je wel!
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
Je kan ook nog met als() controleren of het laatste stukje een getal is. Als dat niet zo is moet je dus de twee na laatste spatie laten staan en de rest verwijderen.
Ik had jouw bijdrage over het hoofd gezien. Maar het komt volgens mij op hetzelfde neer.quote:
Misschien is dit een idee. Met de 1 in substitueren(a2;" ";"";1) kun je meegeven dat alleen de eerste spatie vervangen wordt, met substitueren(a2;" ";"";2) alleen de tweede, etc. Als je weet dat er drie spaties zijn kun je 1 en 2 vervangen (met een als() kun je het alle spaties behalve de laatste vervangen) om vervolgens een tekst over te houden met maar één spatie op de juiste plek. En dan kun je de rest gebruiken wat je al had.
Dank je.
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
Ja, maar je hebt ook 22a en 23b als huisnummer.quote:
Je kan ook nog met als() controleren of het laatste stukje een getal is. Als dat niet zo is moet je dus de twee na laatste spatie laten staan en de rest verwijderen.
Ik ben al geholpen met de bijdrages van jou en Piles. De uitzonderingen (mochten die er zijn) filter ik er wel uit.
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
| Forum Opties | |
|---|---|
| Forumhop: | |
| Hop naar: | |