[Excel] Het grote Excelvragentopic #48

In vond dit een nogal verhelderend filmpje betreffende het optimaliseren van code.

Heb op Youtube al wat uurtjes achter de rug qua tutorials. Echter zijn dit dingen die op dit moment nog niet echt van toepassing op het gene waar ik het op wil toepassen. Ik steek er natuurlijk wel iets van op.

Op dit moment update ik wekelijks handmatig een lijst met data. Dit zijn simpele handelingen zoals kolommen verwijderen die ik niet nodig heb, filteren en kleuren toepassen om het overzichtelijk te maken. Via Vlookup haal ik opmerkingen/notities uit de lijst van de week er voor.

Uiteindelijk wil ik de "verse" data in het huidige bestand laden en de lijst updaten met één druk op de knop. Als dit goed werkt wil ik om het half uur de lijst automatisch bij laten werken zodat ik altijd actuele informatie heb.

Even een vraagje over de opzet van een formule.

Met behulp van het www heb ik de volgende code gemaakt.

quote:

=ALS(E3>G3;"3";ALS(E3<G3;"0";ALS(E3=G3;"1")))

Zoals jullie kunnen zien vul je een waarde in cel E3 en G3 en afhankelijk of deze kleiner/groter of gelijk is veranderd deze in de actieve cel naar 0,1 of 3. (even oefenen met voetbal uitslagen😉)

Nu heb ik elders een formule gevonden die anders is opgebouwd maar wel hetzelfde resultaat geeft.

quote:

=ALS(J6="";"";ALS(J6>L6;3;ALS(J6=L6;1;0)))

Ik probeer te begrijpen waarom dit zo is. Ik mis bij deze bijvoorbeeld het < teken. Wat bepaalt bijvoorbeeld de uitkomst als J6 kleiner is dan L6?

De als functie werkt als volgt: als voorwaarde dan waarde, anders waarde.
Je ziet een '>' en een '=' in die formule. Als niet 'J6>L6;3' en als niet 'J6=L6' dan 0.

quote:

Op vrijdag 15 november 2019 15:18 schreef Harveo het volgende:

Ik probeer te begrijpen waarom dit zo is. Ik mis bij deze bijvoorbeeld het < teken. Wat bepaalt bijvoorbeeld de uitkomst als J6 kleiner is dan L6?

Hoewel de post boven mij technisch klopt, weet ik niet of je het snapt wanneer je het leest. Daarom nu met iets meer tekst:
De formule als werkt als volgt: ALS(CONDITIE;"Wat indien conditie waar is";"Wat indien de conditie NIET waar is") in het kort ALS(CONDITIE;WAAR;ONWAAR)

We delen de probleemstelling op:
=ALS(J6="";"";ALS(J6>L6;3;ALS(J6=L6;1;0)))
oftewel:
=ALS(J6=""; wordt "" bij WAAR; iets moeilijks bij ONWAAR) -> met andere woorden dit doet niets anders dan dat het resultaat "" is wanneer J6 leeg is.

Wanneer J6 ongelijk is aan "" gaan we het tweede gedeelte van de formule in. Dat tweede gedeelte is:
ALS(J6>L6;3;ALS(J6=L6;1;0))
Als J6 groter is dan L6, dan is het resultaat 3.
Als J6 niet groter is dan L6 krijg je het volgende deel van de formule

Als J6 dus ongelijk is aan "", maar ook niet groter is dan L6, krijgen we de volgende formule:
ALS(J6=L6;1;0)
Als J6 gelijk is aan L6 is het resultaat 1
Als J6 niet gelijk is aan L6 is het resultaat 0.

Wanneer je dit in normale taal uitschrijft krijg je:
Indien J6 is "", dan antwoord ""
Indien J6>L6; dan antwoord 3
Indien J6=L6, dan antwoord 1
In alle andere gevallen (wat niet anders kan zijn dan J6<L6), dan antwoord 0

quote:

Op vrijdag 15 november 2019 15:49 schreef snabbi het volgende:

[..]

Hoewel de post boven mij technisch klopt, weet ik niet of je het snapt wanneer je het leest.

Mee eens

Onderstaande geeft een foutmelding (VBA).


Het lukt me bijvoorbeeld wel om =A1 & " " & B1 in een cel te plakken. Maar bovenstaande niet. Als ik de '=' weghaal, gaat het wel goed overigens. Kan het zijn dat de VERT.ZOEKEN een #N/B geeft en daarom de macro niet verder wil?

quote:

Op vrijdag 22 november 2019 14:39 schreef Z het volgende:
Onderstaande geeft een foutmelding (VBA).
[ code verwijderd ]

Het lukt me bijvoorbeeld wel om =A1 & " " & B1 in een cel te plakken. Maar bovenstaande niet. Als ik de '=' weghaal, gaat het wel goed overigens. Kan het zijn dat de VERT.ZOEKEN een #N/B geeft en daarom de macro niet verder wil?

vba gebruikt de engelse notatie
myFormula = "=VLOOKUP(H" & i + 1 & ",[varvallab.xlsx]Blad1!A$1:B$20000,2,FALSE)"

quote:

Op vrijdag 22 november 2019 15:36 schreef snabbi het volgende:

[..]

vba gebruikt de engelse notatie
myFormula = "=VLOOKUP(H" & i + 1 & ",[varvallab.xlsx]Blad1!A$1:B$20000,2,FALSE)"

Ook als je het als string in een cel plakt?

Ik ben zelf ook wat verder. De fout komt waarschijnlijk toch doordat lookup niks vindt.

quote:

Op vrijdag 22 november 2019 15:43 schreef Z het volgende:

[..]

Ook als je het als string in een cel plakt?

Ik ben zelf ook wat verder. De fout komt waarschijnlijk toch doordat lookup niks vindt.

Dat een lookup niets vindt, leidt tot de waarde #N/B in de betreffende cel. Dit leidt niet tot een fout in de vba verwerking.
VBA hanteert altijd de Amerikaanse notatie zowel voor de , als scheidingsteken alsmede de amerikaanse formulenaam.

De enige wijze om daar onder uit te komen is door zelf een functie te schrijven die de nederlandse naam heeft.

Overigens kan je ook gewoon even je marco opname tool gebruiken. Zelf de formule in de cel typen en daarna de opname beeindigen. Dan heb je altijd de juiste notatie. Daarna kan je je bewerking doen met de i +1 enzo.

quote:

Op vrijdag 22 november 2019 15:47 schreef snabbi het volgende:

[..]

Dat een lookup niets vindt, leidt tot de waarde #N/B in de betreffende cel. Dit leidt niet tot een fout in de vba verwerking.
VBA hanteert altijd de Amerikaanse notatie zowel voor de , als scheidingsteken alsmede de amerikaanse formulenaam.

De enige wijze om daar onder uit te komen is door zelf een functie te schrijven die de nederlandse naam heeft.

Overigens kan je ook gewoon even je marco opname tool gebruiken. Zelf de formule in de cel typen en daarna de opname beeindigen. Dan heb je altijd de juiste notatie. Daarna kan je je bewerking doen met de i +1 enzo.

Opgelost (dit werkt):
myFormula = "=VLOOKUP(A" & i + 1 & ",[varvallab_2.xlsm]Blad1!A$1:B$17983,2,FALSE)"

Ik weet dat macros met engelse termen werken. Maar omdat ik nu in wezen een string paste in een cel, snap ik nog steeds niet helemaal niet dat de macro dit niet wil doen.

Ik heb een textbox met een vaste waarde (' dit is een waarde' ) bijvoorbeeld, die wil ik linken aan de waarde van de dyamische cel. Zonder tekst is dat geen probleem, ik verwijs in de tekstbox naar de cel, maar hoe combineer ik die? Is dat zonder moeilijke fratsen te doen?

quote:

Op maandag 25 november 2019 13:11 schreef Z het volgende:

[..]

Opgelost (dit werkt):
myFormula = "=VLOOKUP(A" & i + 1 & ",[varvallab_2.xlsm]Blad1!A$1:B$17983,2,FALSE)"

Ik weet dat macros met engelse termen werken. Maar omdat ik nu in wezen een string paste in een cel, snap ik nog steeds niet helemaal niet dat de macro dit niet wil doen.

Excel heeft in werkelijkheid de amerikaanse notatie onderwater, waarbij het slechts een gebruikersinstelling heeft om die informatie aan jou te presenteren.

Het antwoord is eigenlijk zo simpel, dat je wel een string in de cel zet. Deze waarde zet je alleen op een andere laag dan jij verwacht. De macro zit niet op de gebruikersinterface kant waar de taal en toetsenbordinstellingen invloed hebben op de werking van de macro. Immers zou je macro dan niet meer werken wanneer je het bestandje naar mij stuurt en ik de engelse versie draai waar jij de nederlandse draait.

quote:

Op maandag 25 november 2019 22:44 schreef static het volgende:
Ik heb een textbox met een vaste waarde (' dit is een waarde' ) bijvoorbeeld, die wil ik linken aan de waarde van de dyamische cel. Zonder tekst is dat geen probleem, ik verwijs in de tekstbox naar de cel, maar hoe combineer ik die? Is dat zonder moeilijke fratsen te doen?

Ik zit even te zoeken welke je gebruikt. Ik vermoed dat het gaat over een formulierbesturingselement. Daar had ik zelf nog even zo snel niet een tekstvak met een link naar celinhoud gevonden. Die links ken ik zelf voornamelijk vanuit keuze lijsten.

De enige optie die doet die jij beschrijft is om zelf een Tekstvak te maken en door middel van een macro dmv Change iets uit te voeren.

Kan je iets meer precies aangeven wat je precies gebruikt?

Ik heb een databestand wat er als volgt uitziet:



Dus een 'kop' en daaronder rijen met of een getallen (bijvoorbeeld 11,021...) of een percentage (bijvoorbeeld 16,985...%).

Nu wil ik die percentages 'converteren naar getal'. Maar dat lukt me niet.

Wat werkt is 'F2 + Enter' maar dat in een macro voor een matrix van 100 bij 25000 is dat niet "het meest optimaal".

Iemand een idee?

Heeft iemand een andere suggestie?

quote:

Op vrijdag 6 december 2019 14:26 schreef Z het volgende:
Ik heb een databestand wat er als volgt uitziet:

[ afbeelding ]

Dus een 'kop' en daaronder rijen met of een getallen (bijvoorbeeld 11,021...) of een percentage (bijvoorbeeld 16,985...%).

Nu wil ik die percentages 'converteren naar getal'. Maar dat lukt me niet.

Wat werkt is 'F2 + Enter' maar dat in een macro voor een matrix van 100 bij 25000 is dat niet "het meest optimaal".

Iemand een idee?

Heeft iemand een andere suggestie?

Kolom selecteren -> Gegevens (bovenaan) -> Tekst naar kolommen -> Voltooien

Wil wel eens helpen.

quote:

Op vrijdag 6 december 2019 14:29 schreef KennyPowers het volgende:

[..]

Kolom selecteren -> Gegevens (bovenaan) -> Tekst naar kolommen -> Voltooien

Wil wel eens helpen.

Werkt! Veel dank.

Overal 0 bij optellen wil ook nog wel eens werken.

quote:

Op vrijdag 6 december 2019 14:56 schreef Janneke141 het volgende:
Overal 0 bij optellen wil ook nog wel eens werken.

Of *1

Power BI:
Links en rechts hoor ik dat Power BI ongekende mogelijkheden biedt.
Ik heb wat bekeken hier en daar, maar volgens mij zijn de mogelijkheden eigenljk allemaal wel te vinden binnen Excel zelf.
Het gaat om het selecteren van data en het uitvoeren van queries, etc.
Als je je hebt verdiept in filters, draaitabellen en enigszins handig bent met VBA lijkt met Power BI nauwelijks meerwaarde hebben.
Wat is jullie mening?

quote:

Op zaterdag 7 december 2019 14:56 schreef The_vice het volgende:

[..]

Ik sla het over. Doe mijn query's liever in Access. Vele malen sneller met data.
Heb het volgens mij 10 minuten geprobeerd met Power Bi toen maar met tranen in de ogen dichtgedaan.

Grafieken maken in Access is wel nog erg 1997. Maar daarvoor duwik data dan naar excel, en maak daar mijn grafieken.

Tweede vraag:
Heeft Access meerwaarde boven Excel?
Zoals ik al zei kunnen query's, grafieken e.d. volgens mij in Excel ook worden verricht.

quote:

Op zaterdag 7 december 2019 15:06 schreef Twentsche_Ros het volgende:

[..]

Tweede vraag:
Heeft Access meerwaarde boven Excel?
Zoals ik al zei kunnen query's, grafieken e.d. volgens mij in Excel ook worden verricht.

Access heeft wel degelijk meerwaarde, o.a. omdat het een relationele databse is. Je kan hetzelfde doel wel behalen met Excel, maar Access is er beter in.

Zie ook het door MS slecht vertaalde verhaal op hun eigen site: https://support.office.co(...)6f-9312-e825227fcaea

Een challenge waar jullie me misschien mee kunnen helpen.
Ik wil van alle getallen tussen de 111111111 en 999999999 de getallen hebben waarbij de getallen 1 tm 9 precies éénmalig voorkomen.
Voorbeeld:
123456789
213456789
976854321
Maar dus niet: 223456789 of 333344445

Volgens mijn berekening zijn er 9 x 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 = 362.880 mogelijkheden
(Oftewel 9! --> 9 faculteit)

Hoe zou je in Excel vba de code moeten fabriceren om deze 362.880 mogelijkheden uit te spugen?

(de eerste met de juiste oplossing krijgt een virtuele schouderklop en uiteraard eeuwige roem)