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.
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.SPOILER
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19Sub MOA()
'Macro optimalisatie aan
'Roep deze routine aan bij aanvang van jouw code
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
End Sub
Sub MOU()
'macro optimalisatie uit
'Roep deze routine aan na afloop van jouw code
'zorg er ook voor dat eventuele foutvangers ook verwijzen naar deze routine
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.StatusBar = ""
End Sub
Password kwijt?SPOILER
1
2
3
4
5
6***Pseudo-code***
for i = 1 to EndOfRoutine
Application.StatusBar = "Bezig met uitvoeren FOK! macro_1 " _
& 100 - cInt((EndOfRoutine - i)/ EndOfRoutine * 100) & " %"
'FOK!_macro_1 code
next
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
- de minimale waarde wordt berekend over de afgelopen xx dagen (bijvoorbeeld 5 dagen, dus van de periode 5-9 mei)
- de minimale waarde mag geen nul zijn
Het gewenste resultaat zou in dit geval 2 moeten zijn.
In de kolom van de waarden staan geen negatieve getallen, wel lege cellen:
Hoe krijg ik dit het best voor elkaar? Ik krijg het zelf niet goed voor elkaar
En dan naar beneden slepen?
In C1 wil ik graag één keer het minimum berekenen, dit minimum verandert dus elke dag, omdat het bereik (5 dagen terug vanaf nu) elke dag verandert.quote:Op maandag 9 mei 2022 13:39 schreef Tegan het volgende:
In C1: MIN.ALS.VOORWAARDEN(B1:B5;B1:B5;">0")
En dan naar beneden slepen?
Je kan dit doen met VERSCHUIVING en door een negatief aantal rijen mee te geven van -4.quote:Op maandag 9 mei 2022 11:42 schreef kaiserhenk het volgende:
Vraag: ik wil de minimale waarde retourneren op basis van twee argumenten:
- de minimale waarde wordt berekend over de afgelopen xx dagen (bijvoorbeeld 5 dagen, dus van de periode 5-9 mei)
- de minimale waarde mag geen nul zijn
Het gewenste resultaat zou in dit geval 2 moeten zijn.
In de kolom van de waarden staan geen negatieve getallen, wel lege cellen:
[ afbeelding ]
Hoe krijg ik dit het best voor elkaar? Ik krijg het zelf niet goed voor elkaar
=MIN.ALS.VOORWAARDEN(VERSCHUIVING(C1;-4;-1):VERSCHUIVING(C1;0;-1);VERSCHUIVING(C1;-4;-1):VERSCHUIVING(C1;0;-1);">0")
Probleem hiermee is dat je voor de bovenste vier rijen buiten het werkblad uitkomt en hierdoor een #VERW! fout krijgt. Dit kun je weer oplossen door met een ALS te controleren of het rijnummer een vier of kleiner is en dan het rijnummer in de formule te gebruiken.
=ALS(RIJ(A1)<5;MIN.ALS.VOORWAARDEN(VERSCHUIVING(C1;1-RIJ(A1);-1):VERSCHUIVING(C1;0;-1);VERSCHUIVING(C1;1-RIJ(A1);-1):VERSCHUIVING(C1;0;-1);">0");MIN.ALS.VOORWAARDEN(VERSCHUIVING(C1;-4;-1):VERSCHUIVING(C1;0;-1);VERSCHUIVING(C1;-4;-1):VERSCHUIVING(C1;0;-1);">0"))
Ik kan dit nu niet testen maar zoiets: MIN.ALS.VOORWAARDEN(B:B;B:B;">0";A:A;"<=(VANDAAG()-1)";A:A;">=(VANDAAG()-5)")quote:
[..]
In C1 wil ik graag één keer het minimum berekenen, dit minimum verandert dus elke dag, omdat het bereik (5 dagen terug vanaf nu) elke dag verandert.
Dat hoeft ook nietquote:
Niet perse mee eens, mijn oplossing is slechts één formule.
Ik geef alleen aan wat ik zou doen. Kan de vraagsteller kiezen wat hem het handigste lijkt voor zijn eigen situatie.Bedankt allen!
Ik heb dit net getest en zo moest het zijn:quote:
[..]
Ik kan dit nu niet testen maar zoiets: MIN.ALS.VOORWAARDEN(B:B;B:B;">0";A:A;"<=(VANDAAG()-1)";A:A;">=(VANDAAG()-5)")
MIN.ALS.VOORWAARDEN(B:B;B:B;">0";A:A;"<="&(VANDAAG()-1);A:A;">="&(VANDAAG()-5))
Een vraag:
Uit een data warehouse staat in geel gearceerde cellen TO of een andere 2 letters lange afkorting. Deze wil ik eruit halen met een formule. Echter lukt niks omdat de letters er niet lijken te zijn als ik op de cel sta.
Wat kan ik doen om deze letters er uit te halen?
En als je de opmaak van de cel veranderd?quote:Op maandag 23 mei 2022 15:33 schreef phpmystyle het volgende:
hoi hoi,
Een vraag:
Uit een data warehouse staat in geel gearceerde cellen TO of een andere 2 letters lange afkorting. Deze wil ik eruit halen met een formule. Echter lukt niks omdat de letters er niet lijken te zijn als ik op de cel sta.
Wat kan ik doen om deze letters er uit te halen?
[ afbeelding ]
In wat dan?quote:
[..]
En als je de opmaak van de cel veranderd?
Maakt niet zoveel uit, vraag is vooral of je dan de letters wel te zien krijgt in de formulebalk.quote:
Nee, niet zichtbaar in de formule balk. Heb standaard geprobeerd, getal, etc etc.quote:
[..]
Maakt niet zoveel uit, vraag is vooral of je dan de letters wel te zien krijgt in de formulebalk.
En met 'tekst naar kolommen' ziet ie het dan wel?quote:
[..]
Nee, niet zichtbaar in de formule balk. Heb standaard geprobeerd, getal, etc etc.
Herkent de letters ook niet in tekst naar kolommen.quote:
[..]
En met 'tekst naar kolommen' ziet ie het dan wel?
Zijn het geen afbeeldingen die er staan?quote:
[..]
Herkent de letters ook niet in tekst naar kolommen.
Nope ook dat niet.quote:
[..]
Zijn het geen afbeeldingen die er staan?
Ik gebruik alleen de Engelse Excel dus je zult even zelf moeten kijken wat het in het NLs is maar....quote:
Vraag: ik wil de minimale waarde retourneren op basis van twee argumenten:
- de minimale waarde wordt berekend over de afgelopen xx dagen (bijvoorbeeld 5 dagen, dus van de periode 5-9 mei)
- de minimale waarde mag geen nul zijn
Het gewenste resultaat zou in dit geval 2 moeten zijn.
In de kolom van de waarden staan geen negatieve getallen, wel lege cellen:
[ afbeelding ]
Hoe krijg ik dit het best voor elkaar? Ik krijg het zelf niet goed voor elkaar
=SMALL(B:B,COUNTIF(B:B,0)+1)
Dan kun je kolom B net zo lang maken als je wil, nullen en lege vakken worden genegeerd.
Met de countif kun je criteria toevoegen die je wil negeren, in dit geval dus de nul.
Excel versie 2205 - NL
Ik heb een beste ALS-formule gemaakt.
Ik probeer de formule van L2 en daaronder aan te passen:
| 1 | =ALS(K2=GROOTSTE($K2:$K30;1);"1st";ALS(K2=GROOTSTE($K2:$K30;2);"2e";ALS(K2=GROOTSTE($K2:$K30;3);"3e"))) |
Het lukt me niet om de ONWAAR weg te laten. Het leek me logisch door achteraan
| 1 | ;"" |
Als ik die toevoeging één haakje naar voren haal, krijg ik nog steeds die melding.
Iemand een idee hoe ik het werkend kan krijgen?
-edit-
Oh, toch gelukt. Het moest zelfs voor het laatste haakje. Dom van me.
| 1 | =ALS(K2=GROOTSTE($K$2:$K$30;1);"1st";ALS(K2=GROOTSTE($K$2:$K$30;2);"2e";ALS(K2=GROOTSTE($K$2:$K$30;3);"3e";""))) |
Ik denk dat je de , "" niet op de juiste plek zette.
[ Bericht 96% gewijzigd door onlogisch op 04-07-2022 14:00:12 ]
Ik heb in Blad2 twee kolommen gemaakt met A t/m Q en daarnaast de mediaan van de margecode. Dus 2,5;7,5;12,5 etc.
Kan ik een formule instellen dat er in Blad1 in kolom H de margecode letter met corresponderende mediaan wordt vervangen? Ik zou zeggen verticaal zoeken. Maar kom er niet uit om deze werkend te krijgen in Blad1 waarbij ik iedere keer nieuwe gegevens wil plakken.
Als je in kolom A die letters hebt staan, kan Vert.zoeken er in kolom B de gewenste percentages bij zetten. Kun je in kolom C marges of weet ik wat uitrekenen.
En de letters in A kun je net zo vaak veranderen als je wil. De grap van die formules is dat ze daarna weer alles aanpassen.
Ja, dat was denk ik het probleem, een soort van loop aan veranderingen. Ik heb er een kolom naast gezet waar de verwijzing naartoe gaat en vanaf die kolom gaat de rekensom beginnen. Thanks,quote:
Verticaal zoeken vervangt niets hè, die zet er wat naast.
Als je in kolom A die letters hebt staan, kan Vert.zoeken er in kolom B de gewenste percentages bij zetten. Kun je in kolom C marges of weet ik wat uitrekenen.
En de letters in A kun je net zo vaak veranderen als je wil. De grap van die formules is dat ze daarna weer alles aanpassen.
Ik gebruik deze formule al zeker 20 jaar en heb dit (gelukkig) nog nooit eerder mee gemaakt.
Ik heb een Excel bestand met (o.m.) een tabblad ("Kosten") dat data uit de (FireBird) database ophaalt / weergeeft (d.m.v. een query) en een tabblad met uit het ERP-programma gekopieerde ("handmatig": kopiëren/plakken) data,
Uit het eerst genoemde tabblad wil ik de gegevens (waarden) uit kolom W weergeven in het tweede ("ERP-tab").
Beide tabbladen hebben een "gemene deler" -de eerste drie tekens-, zij het dat er in het ERP-programma een aantal spaties voor staan. Daarom heb ik in de "ERP-tab" een "hulpkolom" -S- gemaakt (TRIM / SPATIES.WISSEN)
Mijn (nl) formule :
=INDEX(Kosten!W:W;VERGELIJKEN(LINKS(S2;3);LINKS(Tabel_Query[OMSCHRIJVING];3);0))
Waar zit de fout ?
[ Bericht 1% gewijzigd door GwaddYr op 15-07-2022 21:52:12 ]
In T2 wordt de waarde uit cel W97 van tabblad "Kosten" weergegeven, terwijl deze uit W98 moet komen.
Zo wordt in T3 de waarde uit W101 weergegeven, wat de waarde uit W102 zou moeten zijn.
Het blijkt fout te gaan bij Tabel_Query[OMSCHRIJVING].
Dit moet veroorzaakt worden door de combinatie met LINKS.
Wanneer ik dit vervang door Kosten!F:F - de kolom OMSCHRIJVING op tabblad Kosten - wordt wél de juiste waarde weergegeven
=INDEX(Kosten!W:W;VERGELIJKEN(LINKS(S2;3);LINKS(Kosten!F:F;3);0))
Het vervangen van Tabel_Query[OMSCHRIJVING] door Kosten!F:F zie ik echter wel als een lapmiddel. Het heeft normaal ook zeker niet mijn voorkeur.
Mijn probleem heb ik weliswaar voor nu opgelost, maar blijft knagen waarom de oorspronkelijke formule fout gaat.
Ik wil graag het hoe en waarom van deze verschuiving weten / begrijpen..
=INDEX(Table[naam_header],1,1) geeft de eerste inhoudelijke resultaat zonder de header.
Waarom maak je van je Kosten!W:W niet ook een named reference? Dan zit je 2x op dezelfde naming convention en hoef je niet een workaround te verzinnen om in je index zelf +1 of -1 te doen om de verschillen tussen met of zonder header op te lossen.
Anders moet je gaan werken met Kosten!W2:W1048576 ook niet elegant
.Opgelost
Kzou verwachten van wel maar heb na tijdje googlen niks kunnen vinden.
Ik heb een competitieschema met 20 teams waarbij ik het team in kolom A een extra taak geef tov het team in kolom B. Ik zou dan graag willen tellen hoevaak elk team in kolom A en hoevaak elk team in kolom B voorkomt zodat ik het uiteindelijk eerlijk kan verdelen.
Nee, die formule bestaat niet. Je kunt er wel op filteren en dan tellen hoeveel er over zijn.quote:
Bestaat er een formule om cellen te tellen die cursief of onderstreept zijn?
Kzou verwachten van wel maar heb na tijdje googlen niks kunnen vinden.
Ik heb een competitieschema met 20 teams waarbij ik het team in kolom A een extra taak geef tov het team in kolom B. Ik zou dan graag willen tellen hoevaak elk team in kolom A en hoevaak elk team in kolom B voorkomt zodat ik het uiteindelijk eerlijk kan verdelen.
Maar persoonlijk zou ik extra informatie in een extra kolom zetten. Werken met opmaak voor dit soort dingen is meestal een recept voor ellende.
Hoe zou je dat dan doen qua kolom? Tzijn 20 teams, 500+ regelsquote:
[..]
Nee, die formule bestaat niet. Je kunt er wel op filteren en dan tellen hoeveel er over zijn.
Maar persoonlijk zou ik extra informatie in een extra kolom zetten. Werken met opmaak voor dit soort dingen is meestal een recept voor ellende.
(Ik bedenk me dat ik natuurlijk wel aantallen.als kan doen op kolom A en daarna op kolom B, maar dat vind ik minder mooi)
Dan heb ik iets scherper nodig wat de bedoeling is.quote:
[..]
Hoe zou je dat dan doen qua kolom? Tzijn 20 teams, 500+ regels
(Ik bedenk me dat ik natuurlijk wel aantallen.als kan doen op kolom A en daarna op kolom B, maar dat vind ik minder mooi)
Ik neem aan dat het een competitieschema is met 20*19 regels of iets dergelijks. Waarbij teams moeten scheidsrechteren ofzo? Dan maak je een scheidsrechterkolom waarbij je de naam van het scheidsende team zet (of een nummer of een afkorting, wat je leuk vindt) en dan ga je die tellen met AANTAL.ALS. Dan weet je of het gelijk verdeeld is.
Je kunt wel tellen op achtergrondkleur, met behulp van een hulpkolom. Maar dan kan je beter in die hulpkolom zetten wat de extra taak is, zoals Janneke zegtquote:
Bestaat er een formule om cellen te tellen die cursief of onderstreept zijn?
Kzou verwachten van wel maar heb na tijdje googlen niks kunnen vinden.
Ik heb een competitieschema met 20 teams waarbij ik het team in kolom A een extra taak geef tov het team in kolom B. Ik zou dan graag willen tellen hoevaak elk team in kolom A en hoevaak elk team in kolom B voorkomt zodat ik het uiteindelijk eerlijk kan verdelen.
Het zijn 3 kolommen met totaal 500+ regelsquote:
[..]
Dan heb ik iets scherper nodig wat de bedoeling is.
Ik neem aan dat het een competitieschema is met 20*19 regels of iets dergelijks. Waarbij teams moeten scheidsrechteren ofzo? Dan maak je een scheidsrechterkolom waarbij je de naam van het scheidsende team zet (of een nummer of een afkorting, wat je leuk vindt) en dan ga je die tellen met AANTAL.ALS. Dan weet je of het gelijk verdeeld is.
Team a - team b
Team c- team d
Team e- team f
Team a- team d
Team f - team b
Etc
En dan zou ik idd een aantal als kunnen doen op eerst de thuiskolom en daarna de uitkolom
Edit: ik heb dit inmiddels gedaan en het kostte me ongeveer 4 seconden (omdat ik al een kolom had met alle teamnamen). Deze hele 4 posts van mij kostten meer tijd dan de meest logische oplossing
[ Bericht 12% gewijzigd door kalinhos op 31-08-2022 19:15:16 ]
Niks mis met hulpkolommen. Beter dan zo'n ellenlange formule waar je niet meer wijs uit wordt.quote:
Met de hulpkolommen is het gelukt.
Bedankt allen!
Sommige mensen houden van zo'n kerstboom van verschillende opmaak, kleuren, kris kras over het veld. Met ook nog eens cellen samengevoegd.quote:
[..]
Nee, die formule bestaat niet. Je kunt er wel op filteren en dan tellen hoeveel er over zijn.
Maar persoonlijk zou ik extra informatie in een extra kolom zetten. Werken met opmaak voor dit soort dingen is meestal een recept voor ellende.
Een drama.
Ik ben altijd voor een databasevorm. Eén kopregel en alle cellen gevuld. Dat opent de poort tot filteren, som.als kwesties, en last but not least de draaitabellen.
1) Er zijn in de eerste playlist o.a. deze twee titels met é's, waarbij ik die é in een gewone e wil veranderen, omdat dat bij importeren vaak beter blijkt te werken.
Maar om de een of andere reden lukt vervangen bij de eerste titel wel, en bij de tweede niet. De é blijft bij de tweede titel een é, of verandert (als er andere manieren gebruikt worden) in e'. Vanwaar dat verschil?
Titel 1: New York Stroll -- Mino Cinélu & Nils Petter Molvær
Titel 2: L'espoir fée vivre -- Fée C
---------------------
2: In de tweede playlist staan titels met (feat. erachter, en titels die dat niet hebben. Bij de titels met (feat. erachter wil ik alle tekst vanaf dat woord weghalen, aangezien dat bij importeren foutmeldingen kan geven.
Daarvoor is deze formule gebruikt:
=LINKS(A2; VIND.ALLES("#"; SUBSTITUEREN(A2; "(feat."; "#"; 1)) -1)
Dat geeft dit beeld:
Het is dus de bedoeling dat kolom C gewoon vol titels komt te staan ipv deels met #WAARDE. De titels waar van origine (feat. achter stond zien er goed uit, de rest dus niet. Hoe komt er gewoon een complete lijst in kolom C?
Als er feat in zit dan doe je substitutieregel en anders niet.
Vraag 1 vind ik lastig te beantwoorden zonder formule. Probeer anders even de ascii code terug te vinden van de betreffende letters om te zien of er een verschil is. Zelf gebruik ik ook een dergelijke formule als hieronder:quote:
Weer een paar vragen over playlists die ik in Spotify wil importeren:
1) Er zijn in de eerste playlist o.a. deze twee titels met é's, waarbij ik die é in een gewone e wil veranderen, omdat dat bij importeren vaak beter blijkt te werken.
Maar om de een of andere reden lukt vervangen bij de eerste titel wel, en bij de tweede niet. De é blijft bij de tweede titel een é, of verandert (als er andere manieren gebruikt worden) in e'. Vanwaar dat verschil?
Titel 1: New York Stroll -- Mino Cinélu & Nils Petter Molvær
Titel 2: L'espoir fée vivre -- Fée C
---------------------
2: In de tweede playlist staan titels met (feat. erachter, en titels die dat niet hebben. Bij de titels met (feat. erachter wil ik alle tekst vanaf dat woord weghalen, aangezien dat bij importeren foutmeldingen kan geven.
Daarvoor is deze formule gebruikt:
=LINKS(A2; VIND.ALLES("#"; SUBSTITUEREN(A2; "(feat."; "#"; 1)) -1)
Dat geeft dit beeld:
[ link | afbeelding ]
Het is dus de bedoeling dat kolom C gewoon vol titels komt te staan ipv deels met #WAARDE. De titels waar van origine (feat. achter stond zien er goed uit, de rest dus niet. Hoe komt er gewoon een complete lijst in kolom C?
=SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(H4;".";"");"é";"e");"ë";"e");"è";"e");"í";"i");"ö";"o");"ä";"a");"ü";"u");"'";"");",";"");",";"")
Vraag 2:
Je zal echt bijvoorbeeld een ALS(ISFOUT( moeten gebruiken of een andere slimme combinatie voor de gevallen waar er geen feat in staat.
Ja, dezelfde formule heb ik ook ergens tevoorschijn gegoogled, maar de é bleef in de tweede titel alsnog gewoon staan.quote:
[..]
Vraag 1 vind ik lastig te beantwoorden zonder formule. Probeer anders even de ascii code terug te vinden van de betreffende letters om te zien of er een verschil is. Zelf gebruik ik ook een dergelijke formule als hieronder:
=SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(H4;".";"");"é";"e");"ë";"e");"è";"e");"í";"i");"ö";"o");"ä";"a");"ü";"u");"'";"");",";"");",";"")
Die é blijkt inderdaad een codeverschil. 101 en 233.
[ Bericht 2% gewijzigd door Eightyone op 25-09-2022 15:25:12 ]
101 is de normale e, 233 is de équote:
[..]
Ja, dezelfde formule heb ik ook ergens tevoorschijn gegoogled, maar de é bleef in de tweede titel alsnog gewoon staan.
Die é blijkt inderdaad een codeverschil. 101 en 233.
Heb zelf ook gezocht via Google, maar het is totaal geen ingewikkelde macro. Lijkt me sterk dat dit aan mijn computer ligt, aangezien de andere macro's feilloos werken. Toch?
Daar kunnen we weinig over zeggen zonder die macro te kennen.quote:
Ik hou zelf de zaken goed bij, en heb een raar "probleem" dat ik nog nooit gehad heb. Twee macro's zijn extreem langzaam, terwijl vergelijkbare dat niet zijn. Hoe kan dit? Er zit echt een flinke delay op, denk tegen een halve minuut aan wel.
Heb zelf ook gezocht via Google, maar het is totaal geen ingewikkelde macro. Lijkt me sterk dat dit aan mijn computer ligt, aangezien de andere macro's feilloos werken. Toch?
Wat doet deze macro?
Wat voor functies en bronnen gebruikt hij?
Worden er dingen dubbel gedaan, bijvoorbeeld in geneste loops?
Ga eens met de debugger kijken waar die vertraging precies zit.
Als vergelijkbare macro's wel snel werken dan zoek de verschillen.
De excelversie op het werk is eehhh... geen idee gewoon 2003 enterprise denk ik. Staat op engels. Meeste dingen kom ik wel uit maar hier heb ik even een hint van een van jullie nodig.
Mijn probleem:
Ik heb een werkorder en ik heb een monteur.
Ik kan een raw dump maken van een complete werkorder, maar die staat vol kolommen en extra dingen waar de monteur helemaal niks van hoeft te weten.
Raw dump ziet er ongeveer zo uit, voor de monteur is alleen belangrijk: Materials en Build step.
Dus mijn simpele plan: ik raw dump de data in sheet 1, van sheet 2 maak ik een 'voorgefilterde' sheet en dan print ik sheet 2 efkes.
Maar...
Niet elke werkorder is het zelfde. En als ik 'voorgefilterde' sheet 2 maak en ik zet op sheet 1 nieuwe info, filtert niet niet correct.
Wat ik daarmee bedoel is:
Kijk naar kolom Phantom. Daar staat nu 'yes' op D4, als ik regel 4 wegfilter (Want ik heb geen phantoms nodig) dan blijft bij een volgende dump regel 4 altijd 'weggefilterd' ook al is het die keer geen phantom.
Suggesties?
Als een cel alleen meegenomen hoeft te worden als er iets in staat kom je een heel eind met Als(Isleeg()). Dan moet je wel kunnen controleren dat de cel ook echt leeg is en er niet een spatie in staat of de karakters een voor een met backspace zijn verwijderd.quote:
Ok gasten... volgens mij heb ik een simpele vraag en zie ik het gewoon niet....
De excelversie op het werk is eehhh... geen idee gewoon 2003 enterprise denk ik. Staat op engels. Meeste dingen kom ik wel uit maar hier heb ik even een hint van een van jullie nodig.
Mijn probleem:
Ik heb een werkorder en ik heb een monteur.
Ik kan een raw dump maken van een complete werkorder, maar die staat vol kolommen en extra dingen waar de monteur helemaal niks van hoeft te weten.
Raw dump ziet er ongeveer zo uit, voor de monteur is alleen belangrijk: Materials en Build step.
[ afbeelding ]
Dus mijn simpele plan: ik raw dump de data in sheet 1, van sheet 2 maak ik een 'voorgefilterde' sheet en dan print ik sheet 2 efkes.
Maar...
Niet elke werkorder is het zelfde. En als ik 'voorgefilterde' sheet 2 maak en ik zet op sheet 1 nieuwe info, filtert niet niet correct.
Wat ik daarmee bedoel is:
Kijk naar kolom Phantom. Daar staat nu 'yes' op D4, als ik regel 4 wegfilter (Want ik heb geen phantoms nodig) dan blijft bij een volgende dump regel 4 altijd 'weggefilterd' ook al is het die keer geen phantom.
Suggesties?
Je kan ook Janneke's optie gebruiken, eventueel aangevuld met een beperking op de opties die in een bepaalde kolom ingevuld kunnen worden via Gegevensvalidatie (zodat mensen wel yes kunnen invullen maar geen ja bijvoorbeeld)
quote:
Aan je dumpsheet een kolom toevoegen die berekent of een regel opgenomen moet worden (iets als =ALS(D6="yes";0;1) of wat dan ook je voorwaarden zijn). Die berekende kolom neem je mee naar sheet 2 en daarna haal je met een simpel dropdownfiltertje de niet-gewenste regels eruit (door de 0en niet te selecteren en de 1en wel).
Ahhhhhh ja tuurlijk.quote:
[..]
Als een cel alleen meegenomen hoeft te worden als er iets in staat kom je een heel eind met Als(Isleeg()). Dan moet je wel kunnen controleren dat de cel ook echt leeg is en er niet een spatie in staat of de karakters een voor een met backspace zijn verwijderd.
Je kan ook Janneke's optie gebruiken, eventueel aangevuld met een beperking op de opties die in een bepaalde kolom ingevuld kunnen worden via Gegevensvalidatie (zodat mensen wel yes kunnen invullen maar geen ja bijvoorbeeld)
Ik ga em met een fprmule voor-filteren komt goed
Thnx !
quote:
Aan je dumpsheet een kolom toevoegen die berekent of een regel opgenomen moet worden (iets als =ALS(D6="yes";0;1) of wat dan ook je voorwaarden zijn). Die berekende kolom neem je mee naar sheet 2 en daarna haal je met een simpel dropdownfiltertje de niet-gewenste regels eruit (door de 0en niet te selecteren en de 1en wel).
Blijkt dat er gewoon een filter formule in Excel zit tegenweurdig (na excel 365 iig....quote:
[..]
Als een cel alleen meegenomen hoeft te worden als er iets in staat kom je een heel eind met Als(Isleeg()). Dan moet je wel kunnen controleren dat de cel ook echt leeg is en er niet een spatie in staat of de karakters een voor een met backspace zijn verwijderd.
Je kan ook Janneke's optie gebruiken, eventueel aangevuld met een beperking op de opties die in een bepaalde kolom ingevuld kunnen worden via Gegevensvalidatie (zodat mensen wel yes kunnen invullen maar geen ja bijvoorbeeld)
Dus ik tief alles mooi in 1 formule met meerdere conditions en dan haalt ie de blanco's er nog uit ook (dat zou mijn 2e vraag worden
)for future reference:
=filter(A2:E8,D2:D8="",C2:C8>0)
Bedankt Janneke en H_T !
Kolom A Voornaam
Kolom B Achternaam
Kolom C Contracturen
Kolom D Kostprijsuurloon
Kolom E Afdeling
Kolom F Rekensom C*D
Elke rij is dan een andere medewerker (+- 100 stuks)
Nu wil ik in tabblad 2 een overzicht hebben van elke medewerker van Afdeling 1 met alle medewerkers die daarbij horen onder elkaar. met de zes kolommen, zodat ik dan gemakkelijk een -subtotaal- kan maken per afdeling. Dit dan voor elke afdeling.
In de hele lijst zelf wordt het te rommelig.
Kan dit wel gemakkelijk en daardoor automatisch zodat ik enkel het eerste tabblad hoef te updaten?
Zo kan ik iedere mail beoordelen en via en extra kolom rubriceren om daarna de onzinmails op te ruimen en de anderen in mappen te organiseren.
Ik moet eerst weten wat het is, en welke rubricering ik het best kan hanteren.
Is er ook zoiets voor de Gmail?
Ik zag wel wat, maar dat vond ik omslachtig omdat je eerst wat moest importeren.
Enfin wie weet dit?
Ik denk dat dat met X.Zoeken wel moet kunnenquote:
Ik heb een personeelsbestand die ik standaard kan opvragen.
Kolom A Voornaam
Kolom B Achternaam
Kolom C Contracturen
Kolom D Kostprijsuurloon
Kolom E Afdeling
Kolom F Rekensom C*D
Elke rij is dan een andere medewerker (+- 100 stuks)
Nu wil ik in tabblad 2 een overzicht hebben van elke medewerker van Afdeling 1 met alle medewerkers die daarbij horen onder elkaar. met de zes kolommen, zodat ik dan gemakkelijk een -subtotaal- kan maken per afdeling. Dit dan voor elke afdeling.
In de hele lijst zelf wordt het te rommelig.
Kan dit wel gemakkelijk en daardoor automatisch zodat ik enkel het eerste tabblad hoef te updaten?
En dan met dollartekens vastzetten waar precies gezocht moet worden, met flink wat extra ruimte aan de onderkant zodat je nieuwe namen er gewoon onder kan knallen zonder dat je alle formules moet aanpassen.In het verlengde daarvan...
Ik zelf heb al heel wat jaren met Excel gewerkt.
Een algemene vraag:
Wat is voor jou nu een eigenschap waar je niet veel van hoort maar die echt het verschil maakt in jou manier van werken.
Bij mij is dat dus:
Databasen maken die orde scheppen in de chaos.
Stel je wordt ergens gedumpt als administrateur en je voorganger is definitief weg en de rest van het personeel weet van toeten noch blazen.
Allereerst dus bv de mailbox copiëren en als database in Excel plaatsen en dan de mailjes stuk voor stuk doornemen en rubriceren. Met een extra kolom in een andere kleur genaamd actieplan.
Dan kun je de urgente dingen eruit halen etc.
Met een dergelijk Excel-bestand heb je ook na een weekend nog snel beeld van je bevindingen.
Verder:
De bestanden op de server in een database-bestand zetten, dankzij:
De command-prompt
dir /s >lijst
Hiermee wordt een txt-bestand gemaakt van alle bestanden inclusief submappen.
Met een aantal truuks in Excel met "mid" etc. heb je een database per bestand:
Het totale pad vanaf de root
Het aantal bytes
De datum met tijd (belangrijk).
En natuurlijk de naam.
Dan kun je sorteren op datum/tijd zodat je weet waar jouw voorganger het laatst mee bezig was.
Verder:
Ik heb ooit een administratie overgenomen waarbij iedere inkoopfactuur in 2 systemen (boekhoudprogramma en projectadministratie) werd ingevoerd.
In het boekhoudpakket kon je niet zien welk project bij een inkoopfactuur betrof.
Inkoopfacturen hadden totaal verschillende nummers.
Oplossing:
Een unieke entiteit maken adhv iedere inkoopfactuur.
Crediteurnummer/datum/bedrag
En dan 2 kolommen maken
Daarmee kun je ongeveer 80% matchen.
De rest heeft een kleine afwijking in het bedrag, of er zijn per ongeluk 2 verschillende crediteurnummers gemaakt, of een vergissing met de datum.
Dankzij vert.zoeken en draaitabellen e.d.
Zo heb ik wel eens 4000 facturen gematched voordat de accountant met de handen in het haar zat.
Dan zijn iig de totalen in de boekhouding en projectadministratie gematched.
Ik ben benieuwd naar andere ervaringen...
[ Bericht 1% gewijzigd door Twentsche_Ros op 05-11-2022 10:17:43 ]
Het enige wat er gebeurt is echter dat het geluidsvolume van de laptop gewijzigd wordt; iets wat ook op de toets te zien is.
Hoe is F2 dan wel te gebruiken in Excel? FN samen met F2 indrukken leverde iig niets op.
Het is een Windows 11-laptop van Lenovo, mocht dit ertoe doen.
Werken de andere F toetsen wel, evt met de fn toets?
Kopier de lijst met klanten, plak die ergens, haal de dubbele waarden eruit.quote:
Ik heb een bestand waarin in 1 kolom de naam van de klant staat en in de andere kolom zijn omzet. Sommige klanten staan er meerdere keren in en ik zou graag willen dat deze regels samengevoegd worden en de omzet opgeteld. Kan ik dat op een makkelijke manier doen?
Daarna tel je de omzet met som.als()
Of maak een draaitabel.
Kan ik niet gewoon 2 regels selecteren en ergens eenvoudig op een knop drukken? Zodat Excel die 2 regels dan samenvoegt?quote:
[..]
Kopier de lijst met klanten, plak die ergens, haal de dubbele waarden eruit.
Daarna tel je de omzet met som.als()
Of maak een draaitabel.
Als jij dat wil kan dat natuurlijk. Gewoon doenquote:
[..]
Kan ik niet gewoon 2 regels selecteren en ergens eenvoudig op een knop drukken? Zodat Excel die 2 regels dan samenvoegt?
Dat was dus een draaitabelquote:
[..]
Kan ik niet gewoon 2 regels selecteren en ergens eenvoudig op een knop drukken? Zodat Excel die 2 regels dan samenvoegt?
ik heb in de tabel in de eerste kolom iets verkeerd gedaan en nu krijg ik enkel een cijfer ipv de datum.
Heb al bij celeigenschappen op datum gezet maar het blijft vaststaan
Dat komt dat excel 'onthoudt'. Dus als je in de cel waarvan je net de voorwaardelijke opmaak naar een datumnotatie hebt gezet, staat, druk op F2 (daarmee kan je de inhoud aanpassen) en druk meteen op enter. Dan wordt het getal wel als datum getoond.quote:
[ afbeelding ]
ik heb in de tabel in de eerste kolom iets verkeerd gedaan en nu krijg ik enkel een cijfer ipv de datum.
Heb al bij celeigenschappen op datum gezet maar het blijft vaststaan
Wat je volgens mij voor de hele rij kan doen. Pas de voorwaardelijke opmaak bij alle cellen aan naar een datum. Kopieer daarna alle cellen en plak ze direct als waarden. Dat zou zeg ik uit mijn hoofd moeten werken. Of plak ze in een andere hulpkolom en werk daarna met een datum functie
Als ik F2 doe dan komt de datum wel tevoorschijn maar als ik dan enter of ervan afspring heb ik weer gewoon een getal van 5 cijfers, een nieuwe rij met data kan maar ik wil ook graag weten wat er nu gebeurdequote:
[..]
Dat komt dat excel 'onthoudt'. Dus als je in de cel waarvan je net de voorwaardelijke opmaak naar een datumnotatie hebt gezet, staat, druk op F2 (daarmee kan je de inhoud aanpassen) en druk meteen op enter. Dan wordt het getal wel als datum getoond.
Wat je volgens mij voor de hele rij kan doen. Pas de voorwaardelijke opmaak bij alle cellen aan naar een datum. Kopieer daarna alle cellen en plak ze direct als waarden. Dat zou zeg ik uit mijn hoofd moeten werken. Of plak ze in een andere hulpkolom en werk daarna met een datum functie
ik moet per ongeluk ctrl-t hebben gedaan, fx{formules weergeven
[ Bericht 81% gewijzigd door mrPanerai op 18-01-2023 10:10:50 ]
Ik heb een string van getallen in A2 staan. Ik wil dat excel eerst het 5e getal checkt, vervolgens zijn er een tientalkeuzes, en wil ik per keuze een waarde weergeven.
Voorbeeld:
20000812310, wanneer 8 de uitkomst is van het getal dan geef je waarde A aan.
Iets met =deel en =voorwaarden.als?
| 1 | =LINKS(RECHTS(A2;LENGTE(A2)-5);1) |
En die kan je nesten:
| 1 | =ALS(LINKS(RECHTS(A2;LENGTE(A2)-5);1)=8;"A";ALS(LINKS(RECHTS(A2;LENGTE(A2)-5);1)=9;"B";ALS(... |
Simpelste oplossing:quote:Op dinsdag 24 januari 2023 11:52 schreef onlogisch het volgende:
Ik loop een beetje te stoeien met een formule waar ik niet helemaal uitkom. Ik hoop dat jullie me kunnen helpen.
Ik heb een string van getallen in A2 staan. Ik wil dat excel eerst het 5e getal checkt, vervolgens zijn er een tientalkeuzes, en wil ik per keuze een waarde weergeven.
Voorbeeld:
20000812310, wanneer 8 de uitkomst is van het getal dan geef je waarde A aan.
Iets met =deel en =voorwaarden.als?
| 1 | = DEEL("ABCDEFGHIJ";DEEL(A1;5;1)+1;1) |
A=0
B=1
C=2
etc.
dus naar wens aanpassen
Onder het tabblad 'invoegen' staan o.a. de knoppen 'slicer' en 'tijdlijn'. Alleen geeft het klikken op een van deze knoppen iets heel anders dan waar ze voor bedoeld zijn: 'bestaande verbindingen'. Dat is bij beide knoppen zo, dat je dat te zien krijgt.
Hebben andere mensen dat ook?
Lijkt me wel logisch aangezien je naar ik aanneem op dat moment geen tabel hebt geselecteerdquote:
Er klopt iets niet aan mijn Excel (nieuwste versie, is al nagekeken).
Onder het tabblad 'invoegen' staan o.a. de knoppen 'slicer' en 'tijdlijn'. Alleen geeft het klikken op een van deze knoppen iets heel anders dan waar ze voor bedoeld zijn: 'bestaande verbindingen'. Dat is bij beide knoppen zo, dat je dat te zien krijgt.
Hebben andere mensen dat ook?
Het is maar een hele beperkte omschrijving, dus ik weet niet precies wat je er zelf aan doet om het (tijdelijk) weg te halen.quote:
Mijn excel gaat steeds naar verwijzingstype R1K1 Ik weet wel hoe ik dat terug kan zetten. Maar hoe zorg ik dat dit altijd uit is?
Via File->Options->Formulas heb je een instelling met of zonder vinkje voor je R1K1 verwijzing.
Mocht dit juist de vorm zijn die je elke keer opnieuw moet instellen, dan gebruik je wellicht een template document om instellingen mee te nemen naar nieuwe Excel bestanden. Kan je daar iets over zeggen?
Dit is inderdaad waar ik het elke keer wijzig. Want de oude manier vind ik toch wat fijner werken. Kan ook een stukje gewenning zijn natuurlijk. Maar hou het liever zonder R1K1.quote:
[..]
Het is maar een hele beperkte omschrijving, dus ik weet niet precies wat je er zelf aan doet om het (tijdelijk) weg te halen.
Via File->Options->Formulas heb je een instelling met of zonder vinkje voor je R1K1 verwijzing.
Mocht dit juist de vorm zijn die je elke keer opnieuw moet instellen, dan gebruik je wellicht een template document om instellingen mee te nemen naar nieuwe Excel bestanden. Kan je daar iets over zeggen?
Het is de excel op mijn werk. Dus dan ka het zijn dat dit bij 1 excelbestand ingesteld staat en elke keer wanneer ik die open wordt R1K1 mijn standaard instelling?
https://www.excel-easy.com/examples/default-templates.html
Is er een formule waarmee hier kolommen van gemaakt kunnen worden, waarbij de woorden met hoofdletters apart gehouden worden van de woorden erna? De standaard kolommengereedschappen in Excel/Kutools boden de optie niet.
(Om het even te illustreren, zo ziet die lijst er ongeveer uit:
DIT ZIJN HOOFDLETTERS Dit is een normale zin
OOK DIT ZIJN HOOFDLETTERS Ook dit is een normale zin)
Hoe lang zijn die teksten? Wat is bij benadering de hoogste positie waar je de scheiding tegen gaat komen?quote:
Ik heb een lijst teksten waarbij de eerste woorden steeds getikt zijn met hoofdletters, en de rest 'normaal', cq beginnend met een hoofdletter en voor de rest kleine letters. Er staan geen scheidingstekens tussen, alleen spaties.
Is er een formule waarmee hier kolommen van gemaakt kunnen worden, waarbij de woorden met hoofdletters apart gehouden worden van de woorden erna? De standaard kolommengereedschappen in Excel/Kutools boden de optie niet.
(Om het even te illustreren, zo ziet die lijst er ongeveer uit:
DIT ZIJN HOOFDLETTERS Dit is een normale zin
OOK DIT ZIJN HOOFDLETTERS Ook dit is een normale zin)
Ik weet wel een oplossing, maar die vraagt wel 26 hulpkolommen
En als het alleen heel ingewikkeld kan, dan gaat het hem niet worden.
En als je het "voordoet" en dan flash fill gebruikt (CTRL+E)?quote:
Het zijn (opnieuw) liedjeslijsten. De artiesten in hoofdletters, de titels 'normaal'. Helaas zijn de mensen die ze oorspronkelijk online zetten niet zo netjes als het gaat om het vormgeven ervan.
En als het alleen heel ingewikkeld kan, dan gaat het hem niet worden.
Dit kan nog wel eens werken als je zelf de formule niet weet.
Het kan, maar het is omslachtig. Als je het één keer voor een hele peut nummers wil doen dan gaat het wel.quote:Op donderdag 2 maart 2023 11:16 schreef Z het volgende:
Ik zie dit niet lukken met formules. Met een macro kan het iig wel.
Zoek in de teksten met VIND.SPEC op a, b, c etc t/m z. Het minimum van de waardes die je terugkrijgt is de beginpositie van de titel.
I Am the Best 2NE1
Dancing Queen ABBA
You Shook Me All Night Long AC/DC
Sure, here's how you can split the data into two columns:
Title Artist
I Am the Best | 2NE1
Dancing Queen | ABBA
You Shook Me All Night Long | AC/DC
For the "Title" column, use the following formulas:
To extract the title in cell A1: =LEFT(A1,FIND(" ",A1)-1)
To extract the title in cell A2: =LEFT(A2,FIND(" ",A2)-1)
To extract the title in cell A3: =LEFT(A3,FIND(" ",A3)-1)
For the "Artist" column, use the following formulas:
To extract the artist in cell A1: =RIGHT(A1,LEN(A1)-FIND(" ",A1))
To extract the artist in cell A2: =RIGHT(A2,LEN(A2)-FIND(" ",A2))
To extract the artist in cell A3: =RIGHT(A3,LEN(A3)-FIND(" ",A3))
Of gebruik ChatGPT, die had er weinig moeite mee en gaf me gelijk 2 kolommen
https://learn.microsoft.c(...)percase-to-lowercase
quote:Op donderdag 2 maart 2023 11:27 schreef _I het volgende:
Of gebruik ChatGPT, die had er weinig moeite mee en gaf me gelijk 2 kolommen
Tsja, nood breekt wet, toch!?quote:
Zolang het gaat om liedjes scheiden van de artiest. Het is niet alsof er een schoolwerkstuk in elkaar wordt gezet.
Hij gaf eerst twee kolommen, die je zo zou kunnen kopieren en plakken. En kwam daarna met een fomule voorstel. Laatste zou je ook weg kunnen laten idd. (Zou niet meer zonder willen werken!)quote:
Je kan ChatGPT waarschijnlijk ook vragen het gewoon te doen (zonder Excel). Ik heb wat lopen te stoeien met GPT en Googledocs. Met dingen als: haal de postcode uit de tekst. Werkte best goed. Vooral als je quick en dirty resultaten wil.
Oh, ik heb er ook zeker geen probleem mee. Ik was er gewoon niet opgekomen.quote:
[..]
Tsja, nood breekt wet, toch!?
Zolang het gaat om liedjes scheiden van de artiest. Het is niet alsof er een schoolwerkstuk in elkaar wordt gezet.
Ik moet daar echt eens wat mee gaan experimenteren. Ik bedoel, als leerlingen er schoolopdrachten mee kunnen maken dan zou ik het dus ook moeten kunnen
Ik vind het briljant. Schrijf geen email meer zonder, gebruik het voor presentaties, doorlezen van tekst en samenvatting eruit halen. Laat het alle teksten lezen van onze website en brochures en het maakt allerlei social media posts. Voer mijn notities in en laat het structureren en maakt actielijsten voor me. Ben 10 sneller en efficiënter, en schrijf tegenwoordig foutloos in 7 talen.quote:Op donderdag 2 maart 2023 11:46 schreef Janneke141 het volgende:
[..]
Oh, ik heb er ook zeker geen probleem mee. Ik was er gewoon niet opgekomen.
Ik moet daar echt eens wat mee gaan experimenteren. Ik bedoel, als leerlingen er schoolopdrachten mee kunnen maken dan zou ik het dus ook moeten kunnen
Zou het je echt aanraden. Denk dat er nog veel meer mogelijk is dan wat ik er mee doe, maar schrik nu soms al wat het kan.=LINKS(A2;VERGELIJKEN(ONWAAR;GELIJK(LINKS(A2;VIND.ALLES(" ";A2 & " ";
RIJ(INDIRECT("1:60"))));HOOFDLETTERS(LINKS(A2;VIND.ALLES(" ";
A2 & " ";RIJ(INDIRECT("1:60"))))));0)-1)
Een versie vanaf de rechterkant van de cel, voor de woorden met kleine letters, werkt echter niet.
En dan nu nog eens voor bandnamen bestaande uit 2 of meer woordenquote:
can you give me a table with two columns and split the three lines;
I Am the Best 2NE1
Dancing Queen ABBA
You Shook Me All Night Long AC/DC
Sure, here's how you can split the data into two columns:
Title Artist
I Am the Best | 2NE1
Dancing Queen | ABBA
You Shook Me All Night Long | AC/DC
For the "Title" column, use the following formulas:
To extract the title in cell A1: =LEFT(A1,FIND(" ",A1)-1)
To extract the title in cell A2: =LEFT(A2,FIND(" ",A2)-1)
To extract the title in cell A3: =LEFT(A3,FIND(" ",A3)-1)
For the "Artist" column, use the following formulas:
To extract the artist in cell A1: =RIGHT(A1,LEN(A1)-FIND(" ",A1))
To extract the artist in cell A2: =RIGHT(A2,LEN(A2)-FIND(" ",A2))
To extract the artist in cell A3: =RIGHT(A3,LEN(A3)-FIND(" ",A3))
Of gebruik ChatGPT, die had er weinig moeite mee en gaf me gelijk 2 kolommen
Draaitabel met x aantal kolommen met getallen. Hoe voeg ik daar een subtotaal aan toe van een aantal van die kolommen (dus geen eindtotaal)? Iemand de gouden tip hoe je zo'n berekend veld maakt? Lukte me maar niet.
Weet zeker dat ChatGPT dat aan kan. Van Excel ben ik nog niet zeker. Die kan alleen aangeven of een hele cel in hoofdletters is geschreven of niet.quote:
[..]
En dan nu nog eens voor bandnamen bestaande uit 2 of meer woorden
Zeker. Als je in je draaitabel gaat staan Start - invoegen - berekend veld invoegenquote:
Ik had vandaag iets waar ik niet uitkwam, waarvan ik wel een werkend voorbeeld zag.
Draaitabel met x aantal kolommen met getallen. Hoe voeg ik daar een subtotaal aan toe van een aantal van die kolommen (dus geen eindtotaal)? Iemand de gouden tip hoe je zo'n berekend veld maakt? Lukte me maar niet.
Dan komt er een pop-up en kan je een naam aan je nieuwe veld geven en daaronder komt een formule blokje. Daar type je = "veld a" + "veld b" + "veld k" (die je kan kiezen uit je keuze mogelijkheden eronder)=> OK en je hebt een nieuw veld ingevoegd met een "custom" subtotaal.
[ Bericht 1% gewijzigd door _I op 02-03-2023 19:24:29 ]
.Maar als je de linkerkant hebt, dan kun je met behulp van de lengte en RECHTS toch de rest eruit halen?quote:
Wat betreft m'n eerdere vraag: Vanaf de linkerkant van de cel weet ik het nu, als ik alleen de woorden met hoofdletters wil hebben - waar de regels dus mee beginnen.
=LINKS(A2;VERGELIJKEN(ONWAAR;GELIJK(LINKS(A2;VIND.ALLES(" ";A2 & " ";
RIJ(INDIRECT("1:60"))));HOOFDLETTERS(LINKS(A2;VIND.ALLES(" ";
A2 & " ";RIJ(INDIRECT("1:60"))))));0)-1)
Een versie vanaf de rechterkant van de cel, voor de woorden met kleine letters, werkt echter niet.
Weet niet zeker of ik je goed begrijp. Je kan rijen en kolommen groeperen en dan ontstaat daar automatisch een subtotaal. Selecteer de regels, druk rechtermuisknop en kies voor groeperenquote:
Ik had vandaag iets waar ik niet uitkwam, waarvan ik wel een werkend voorbeeld zag.
Draaitabel met x aantal kolommen met getallen. Hoe voeg ik daar een subtotaal aan toe van een aantal van die kolommen (dus geen eindtotaal)? Iemand de gouden tip hoe je zo'n berekend veld maakt? Lukte me maar niet.
Of zoals al gezegd. Voeg een berekend veld toe. Zie tabblad 'draaitafel analyseren' en dan 'belden items en sets'
Ik wil uiteindelijk een draaitabel met alleen een aantal subtotalen, dus groeperen is geen optie. En bij berekend veld kreeg ik de error die ik hierboven beschrijf.quote:
[..]
Weet niet zeker of ik je goed begrijp. Je kan rijen en kolommen groeperen en dan ontstaat daar automatisch een subtotaal. Selecteer de regels, druk rechtermuisknop en kies voor groeperen
Of zoals al gezegd. Voeg een berekend veld toe. Zie tabblad 'draaitafel analyseren' en dan 'belden items en sets'
Ik ga morgen een nieuwe poging doen.
Edit: Ik heb het nu werkend, heel simpel eigenlijk. Het probleem dat ik had lag aan mijn Excel-omgeving/BI-tool. Bedankt allen.
[ Bericht 6% gewijzigd door Tegan op 03-03-2023 13:08:09 ]
Dat blijkt in de praktijk niet zo te werken, als ik 'links' door 'rechts' vervang, en 'hoofdletters' door 'kleine.letters'. Er missen dan wat letters. Ik had dat wat jij zegt al geprobeerd.quote:
[..]
Maar als je de linkerkant hebt, dan kun je met behulp van de lengte en RECHTS toch de rest eruit halen?
Dat bedoel ik niet. Als de lengte van je originele tekst 50 karakters is, en met jouw formule weet je dat het linkerdeel 21 karakters heeft, dan vormen de rechtse 29 karakters toch het rechterdeel? (Of eigenlijk 28, want anders begint het met een spatie).quote:
[..]
Dat blijkt in de praktijk niet zo te werken, als ik 'links' door 'rechts' vervang, en 'hoofdletters' door 'kleine.letters'. Er missen dan wat letters. Ik had dat wat jij zegt al geprobeerd.
. Kwam daar een keer achter bij een heel ambitieuze geneste formule toen ik nog niet zo kundig was.Oh vast wel, maar staar je niet blind op het antwoord van de AIquote:
[..]
Weet zeker dat ChatGPT dat aan kan. Van Excel ben ik nog niet zeker. Die kan alleen aangeven of een hele cel in hoofdletters is geschreven of niet.
Kolom A: Standplaatsen
Kolom B..D: niet-relevante info
Kolom E: Begindatum
Kolom F: Einddatum.
Het is een vrij lange lijst, maar als standplaats 651 er bijvoorbeeld drie keer instaat met
651 15-7-1986 3-10-2005
651 1-1-2000 31-7-2015
651 12-5-2001 15-8-2023
Dan ben ik op zoek naar de totalen per jaar, peildatum 31 december. In 1989 dus bijvoorbeeld de waarde 1, in 2000 de waarde 2 en in 2003 de waarde 3.
Ideaal gezien wil ik een draaitabel met verticaal de verschillende standplaatsen en horizontaal de jaartallen - met dus de totalen per jaar op 31 december. De data liggen tussen pakweg 1980 en nu.
Is daar een eenvoudige manier voor?
Als er alleen een ingewikkelde manier is, bespaar je dan de moeite om het uit te typen. Daar ga ik waarschijnlijk toch niet aan beginnen. De vraag is of je in een draaitabel op deze manier gegevens kan groeperen
Geen idee of het met draaitabellen kan, maar quick&dirty:quote:
Ideaal gezien wil ik een draaitabel met verticaal de verschillende standplaatsen en horizontaal de jaartallen - met dus de totalen per jaar op 31 december. De data liggen tussen pakweg 1980 en nu.
- in G1: 31-12-1980, in H2: 31-12-1981 etc
- in G2: =ALS(EN($E2<=G$1;$F2>=G$1);1;0)
etc.
| 1 2 3 4 | Begindatum Einddatum 31-12-1989 31-12-2000 31-12-2001 15-7-1986 3-10-2005 1 1 1 1-1-2000 31-7-2015 0 1 1 12-5-2001 15-8-2023 0 0 1 |
Ja, die had ik ook wel bedacht, maar levert een gruwelijke hoeveelheid hulpkolommen en formules op. Vandaar mijn idee voor een draaitabel. Maar het zou inderdaad wel werken. Dank voor het meedenken in elk geval.quote:
[..]
Geen idee of het met draaitabellen kan, maar quick&dirty:
- in G1: 31-12-1980, in H2: 31-12-1981 etc
- in G2: =ALS(EN($E2<=G$1;$F2>=G$1);1;0)
etc.
[ code verwijderd ]
Desnoods maak je een handmatige draaitabel, door kolommen G t/m XXX zelf op te tellen per jaar met wat SOM.ALS formules...
Draaitabel is lastig doordat je de data van 2 velden moet combineren.quote:
Ik heb een werkblad met de volgende kolommen:
Kolom A: Standplaatsen
Kolom B..D: niet-relevante info
Kolom E: Begindatum
Kolom F: Einddatum.
Het is een vrij lange lijst, maar als standplaats 651 er bijvoorbeeld drie keer instaat met
651 15-7-1986 3-10-2005
651 1-1-2000 31-7-2015
651 12-5-2001 15-8-2023
Dan ben ik op zoek naar de totalen per jaar, peildatum 31 december. In 1989 dus bijvoorbeeld de waarde 1, in 2000 de waarde 2 en in 2003 de waarde 3.
Ideaal gezien wil ik een draaitabel met verticaal de verschillende standplaatsen en horizontaal de jaartallen - met dus de totalen per jaar op 31 december. De data liggen tussen pakweg 1980 en nu.
Is daar een eenvoudige manier voor?
Als er alleen een ingewikkelde manier is, bespaar je dan de moeite om het uit te typen. Daar ga ik waarschijnlijk toch niet aan beginnen. De vraag is of je in een draaitabel op deze manier gegevens kan groeperen
Formules kunnen natuurlijk wel. Dus zet bijvoorbeeld in K2 de datum 31/12/1989 en dan krijg je een formule zoals:
=COUNTIFS(A:A,651,E:E,"<"&K2,F:F,">"&K2)
Als je het echt mooi wilt doen btw dan maak je van je huidige tabel een echte Table
Stap 1: ga in je data staan van je tabel en druk CTRL T - geef je tabel een naam ik gebruik nu gewoon even TABEL
Zorg overigens dat je netjes aangeeft dat je tabel headers heeft.
Stap 2: Zet nu bijvoorbeeld in K2 de volgende formule:
=UNIQUE(TABEL[Standplaatsen])
Wat dit doet is alle unieke waarden in je tabel onder elkaar zetten zodat je alle standplaatsnummers netjes 1x hebt staan.
Stap 3: Zet bijvoorbeeld in L1 de datum 31/12/1989 en doe vervolgens de automatische doortrekken naar rechts zodat je in M1 31/12/1990 hebt staan e.d.
Stap 4: Je formule is:
=COUNTIFS(TABEL[Standplaatsen],$K2,TABEL[Begindatum],"<"&L$1,TABEL[Einddatum],">"&L$1)
Deze formule kan je zowel naar onderen doortrekken als naar rechts trekken zodat je voor elk jaar voor elke standplaats je resultaat hebt. En ook wanneer je nieuwe standplaatsnummers krijgt, dan werkt die UNIQUE formule je resultaten gewoon bij zonder dat je opnieuw moet klooien.
-edit-
had een foutje in UNIQUE staan
[ Bericht 2% gewijzigd door snabbi op 08-03-2023 20:29:41 ]
Dat is ook zo, maar ik ga heel even een poging wagen. Het is nog vrij snel te genereren (in elk geval sneller dan alle hulpkolommen die ik zelf in gedachten had) en als het een klein beetje werkbaar blijft dan is het nog wel een uurtje prutsen waard.quote:
Ik wilde ook AANTALLEN.ALS voorstellen maar dacht halverwege het typen dat je aantal standplaatsen keer aantal jaartallen teveel formules vond.
De echte oplossing ligt dan ook in PowerQuery waarin je stiekem die data-set op de achtergrond kan bouwen, maar dat leek mij te ingewikkeld om te plaatsen. Zie dit youtube filmpje hoe je dat kan oplossen.. maar ik weet zeker dat je deze oplossing niet echt gaat gebruiken
[ Bericht 4% gewijzigd door snabbi op 09-03-2023 00:29:22 ]
Je kan per jaartal een berekend veld (in de draaitabel dus) maken met in de formule daarvan dus direct 1987 i.p.v. een verwijzing, en die vergelijken met de twee datums wat wel verwijzingen zijn. Zo is een draaitabel mogelijk.quote:
Nee, in mijn ogen is het probleem met een draaitabel dat je daarvoor eigenlijk voor die eerste regel die van 1986 gaat tot 2005, dat je geen regels hebt waar ook 1987 in staat. Je moet dus eigenlijk een soort lijst bouwen in de regel die je hebt en hij moet naar die lijst kijken.
De echte oplossing ligt dan ook in PowerQuery waarin je stiekem die data-set op de achtergrond kan bouwen, maar dat leek mij te ingewikkeld om te plaatsen. Zie dit youtube filmpje hoe je dat kan oplossen.. maar ik weet zeker dat je deze oplossing niet echt gaat gebruiken
Het tonen van alle jaartallen op de horizontale of verticale as - dat is iets wat ik makkelijk kan aandragen, maar om daarbij voor die standplaatsen een true/false uitkomst te krijgen op basis van die start en einddatum.. dat zou ik niet kunnen. Dus mocht jij die formule hebben van dat berekende veld, dan zouden we de oplossing hebben.quote:
[..]
Je kan per jaartal een berekend veld (in de draaitabel dus) maken met in de formule daarvan dus direct 1987 i.p.v. een verwijzing, en die vergelijken met de twee datums wat wel verwijzingen zijn. Zo is een draaitabel mogelijk.
Ik kreeg dit werkend met ALS(EN(JAAR(VeldDatum1)<=1987;JAAR(VeldDatum2)>=1987);1;0) en dan herhalen voor ieder jaartal.quote:
[..]
Het tonen van alle jaartallen op de horizontale of verticale as - dat is iets wat ik makkelijk kan aandragen, maar om daarbij voor die standplaatsen een true/false uitkomst te krijgen op basis van die start en einddatum.. dat zou ik niet kunnen. Dus mocht jij die formule hebben van dat berekende veld, dan zouden we de oplossing hebben.
En het geposte filmpje ga ik nog wel een keer kijken als ik tijd heb. Altijd interessant.
Stappenplan:quote:
[..]
Ik kreeg dit werkend met ALS(EN(JAAR(VeldDatum1)<=1987;JAAR(VeldDatum2)>=1987);1;0) en dan herhalen voor ieder jaartal.
1) Maak een Pivot Table via de Ribbon Insert -> PivotTable. In het popup scherm vink je de onderste optie aan "Add this data to the Data Model".
2) Ga via de Ribbon Data naar de optie: Manage Data Model (is een groen icoontje).
3) Klik op de Ribbon Design en vervolgens op Date Table -> New.
4) Je ziet een aantal definities met dagen in het jaar. Zorg dat je ergens in de half zichtbare kolom met een formule toevoegt: =Day(date)
Zodra je op enter drukt dan zie je ook dat ie netjes de dagen van de maand toont 1,2,3 etc.
5) Rechtermuistoets op de kolom header -> Rename Column, bijvoorbeeld naar Eigen_dag
6) Onder de Ribbon Design klik je op Create Relationships. Zorg ervoor dat je bij de bovenste Calendar de kolom selecteert met Date, daaronder kies je voor Range en dan Startdatum.
7) klik de power pivot weg... eventueel moet je nu in het PivotTable Fields menu klikken op More tables om hem te updaten.
8) Wat je nu ziet is dat je zowel een tabel Calendar hebt, en ook een tabel Range (waar je je normale velden in hebt staan).
9) Onder Calendar is er een optie "More Fields" waardoor je Year op de horizontale of verticale as kunt plaatsen. Eigenlijk moet je denk ik Date hebben om de exacte match te kunnen maken op 31-12, maar ik wilde in ieder geval die dag in de maand en de maand zelf als eventuele Filter opties in de Pivot table hebben zodat je lekker kunt klooien ermee.
Nu zou je dus die berekende velden moeten toevoegen zoals jij dat zegt (eigenlijk kan dit ook in de Power Pivot, maar daar krijg ik het niet werkend).
in plaats van 1987 zou je nu gewoon Calendar[Year] moeten kunnen gebruiken
Klinkt goed, wellicht binnenkort keertje mee spelen.quote:
[..]
Stappenplan:
1) Maak een Pivot Table via de Ribbon Insert -> PivotTable. In het popup scherm vink je de onderste optie aan "Add this data to the Data Model".
2) Ga via de Ribbon Data naar de optie: Manage Data Model (is een groen icoontje).
3) Klik op de Ribbon Design en vervolgens op Date Table -> New.
4) Je ziet een aantal definities met dagen in het jaar. Zorg dat je ergens in de half zichtbare kolom met een formule toevoegt: =Day(date)
Zodra je op enter drukt dan zie je ook dat ie netjes de dagen van de maand toont 1,2,3 etc.
5) Rechtermuistoets op de kolom header -> Rename Column, bijvoorbeeld naar Eigen_dag
6) Onder de Ribbon Design klik je op Create Relationships. Zorg ervoor dat je bij de bovenste Calendar de kolom selecteert met Date, daaronder kies je voor Range en dan Startdatum.
7) klik de power pivot weg... eventueel moet je nu in het PivotTable Fields menu klikken op More tables om hem te updaten.
8) Wat je nu ziet is dat je zowel een tabel Calendar hebt, en ook een tabel Range (waar je je normale velden in hebt staan).
9) Onder Calendar is er een optie "More Fields" waardoor je Year op de horizontale of verticale as kunt plaatsen. Eigenlijk moet je denk ik Date hebben om de exacte match te kunnen maken op 31-12, maar ik wilde in ieder geval die dag in de maand en de maand zelf als eventuele Filter opties in de Pivot table hebben zodat je lekker kunt klooien ermee.
Nu zou je dus die berekende velden moeten toevoegen zoals jij dat zegt (eigenlijk kan dit ook in de Power Pivot, maar daar krijg ik het niet werkend).
in plaats van 1987 zou je nu gewoon Calendar[Year] moeten kunnen gebruiken
Datum 31-12-jaartal kun je zelf genereren met (uit mijn hoofd) DATUM(jaartal;12;31) waarbij 12 en 31 dus geen verwijzing zijn
.Hoe krijg ik dat teringding uit? Ik wil dat niet
Het soort me mateloos in mijn werk.
Dus weet iemand toevallig hoe dat uit te zetten is?
Daar heb ik me ook mateloos aan gestoord, ja. Heeft mij ook even gekost voordat ik gevonden had waar ik die flauwekul uit kon zetten. Dansende paperclip, iemand?quote:
In excel (365) zit tegenwoordig een 'feature' dat je een menu krijgt bij de ctrl toets oid.
Hoe krijg ik dat teringding uit? Ik wil dat niet
Het soort me mateloos in mijn werk.
Dus weet iemand toevallig hoe dat uit te zetten is?
In elk geval,
Het is één van deze twee dacht ik.
Zit ook al hard te zoeken
Ik weet vrijwel zeker dat het een van die twee moet zijn. Je moet het echt uit kunnen zetten, want dat is mij namelijk ook gelukt
Opties, Geavanceerd, Knippen kopiëren en plakken en dan het bovenste vinkje.quote:
Dat moet hem denk ik zijn dan.
Yes, die is het,quote:
[..]
Opties, Geavanceerd, Knippen kopiëren en plakken en dan het bovenste vinkje.
Dat moet hem denk ik zijn dan.
dank je wel
Nu had ik ook een methode bedacht om een soort van administratie hierop te doen en te berekenen hoeveel unieke nummers er in gebruik waren en met welk bedrag. Nu werkt dat niet meer omdat er is besloten dat een id-nummer meerdere keren op een dag maar ook meerdere dagen gebruikt kan worden.
Nu ben ik dus op zoek naar een manier uit een tabel (zie screen print) het aantal unieke id-nummers te berekenen, maar alleen als ze een waarde hebben hoger dan 0. Idealiter ook met de waarde per uniek id-nummer.
In mijn huidige formule berekent hij alleen de unieke argumenten, ongeacht de waarde (=AANTALARG(UNIEK(B2:B19))-ALS(AANTAL.LEGE.CELLEN(B2:B19)>0;1;0)).
Iemand hier die de gouden tip heeft? De tabel die ik er eigenlijk een soort van tussen geplakt heb is geen must, dus een functie direct over de onderliggende tabjes is ook zeker een optie! Hiernaast heb ik nog een tabel waarbij men als ze het unieke id-nummer invoeren het bedrag moet krijgen wat er aan gekoppeld hoort te zijn.
Waarom zoek je het antwoord '6'? Omdat er 6 sealbags zijn met een gestort bedrag? Want dan zal er toch ergens een verwijzing naar kolom E in je formule moeten zitten.
[ Bericht 61% gewijzigd door Janneke141 op 20-03-2023 15:30:44 ]
Nee andersom juist, ik wil alleen de sealbags tonen die nog niet afgestort zijn.quote:
Ik zou dit denk ik even met een hulpkolommetje oplossen.
Waarom zoek je het antwoord '6'? Omdat er 6 sealbags zijn met een gestort bedrag?
Die is er nu ook, maar das een simpele vlookup die ook niet meer werkt (dat moet iets met sommen.als gaan worden).quote:Want dan zal er toch ergens een verwijzing naar kolom E in je formule moeten zitten.
Daar zitten dubbelen in zo te zien.quote:
Hulpkolommetje maken met =ALS(Bedrag=0;Sealbagnummer;"") en dan in die kolom de unieke waarden tellen. Dat zou het moeten doen volgens mij.
AANTALARG(UNIEK(X.ZOEKEN(1;(B2:B99999>0)*(F2:F99999>0);B2:B99999)))
Die kwam gek genoeg op 1 uit.quote:
Zoiets dan?
AANTALARG(UNIEK(X.ZOEKEN(1;(B2:B99999>0)*(F2:F99999>0);B2:B99999)))
Deze lijkt het hem te doen inderdaad. Ik dacht veel te moeilijk.. Thanks!quote:
[..]
Daar zitten dubbelen in zo te zien.
Hulpkolommetje maken met =ALS(Bedrag=0;Sealbagnummer;"") en dan in die kolom de unieke waarden tellen. Dat zou het moeten doen volgens mij.
Dat was een beetje dom van mij. Maar ik vond het toch een leuk probleem dus heb ik in een verloren kwartiertje nog even zitten spelen en hierbij een oplossing die werkt.quote:
AANTAL(UNIEK(ALS.VOORWAARDEN(F1:F99999>0;B1:B99999)))
Op het werk hebben we een lijst met verkoopcijfers en we maken een Excelbestand per maand. Nu zou ik daar graag een jaaroverzicht van maken omdat we bepaalde zaken moeten rapporteren.
Ik moet bv. het aantal keer weten dat een bepaald iets verkocht wordt maar ook wat de som is.
Dat lukt prima zolang het 1 tabel en 1 bestand is.
Maar ik moet dat nu maken voor alle maanden.
Hoe zouden jullie te werk gaan?
Alles in 1 bestand plakken (van verkoopcijfers) of kan het ook nog op een andere manier?
Je kunt met functies als SOM(...) celbereiken uit andere bestanden halen, maar je kunt ook via het lint Gegevens en dan Gegevens ophalen kiezen.
Alle data overkopieren vind ik zelf het prettigst werken, maar dat kan weer veel ellende geven qua performance of wanneer er door de tijd heen toch nog een kleine aanpassing in de data gemaakt wordt.
Kort gezet wil ik met een dropdown menu werken in Excel, maar naargelang de keuze daarin moeten gegevens in een bepaalde cel ingevuld worden.
Bv.
Audi --> Kolom B
Mercedes --> Kolom C
Audi --> Kolom D
BYD --> Kolom E
Tesla --> Kolom F
Hoe maak je zoiets?
@Z hierboven heeft het vermoedelijk over een Formulier. Om deze te maken moet je zorgen dat je de Ribbon Developer kan zien. Ga hiervoor in File -> Options -> naar "Customize Ribbon". In het rechter menu moet je een vinkje zetten bij Developer.
Vervolgens kan je daar bijvoorbeeld via Developer -> Insert -> een Combo Box maken. Wanneer je dit aanklikt moet je zelf via je muis klik aangeven hoe groot die Combo box moet zijn. Die zweeft dan eigenlijk bovenop je Excel werkblad.
Via rechtermuistoets op de Combo Box en dan Format Control krijg je een "input range" voor de waarden die getoond moeten worden en een "cel link" waar een getal komt met de zoveelste optie die gekozen wordt door de gebruiker.
Een andere manier is om gebruik te maken in de cel zelf van Validatie.
Klik hiervoor in de Ribbon Data op Data Validation. Onder de popup kies je bij Allow voor "list" en daarna kan je bij Source de waarden weergeven die getoond moeten worden (via een cel referentie).
De gebruiker kan alleen de mogelijke waarden kiezen en krijgt anders een foutmelding te zien als deze zelf probeert te typen.
Het voordeel is dat het qua opmaak makkelijker in je Excel past, het nadeel is dat de dropdown teken pas zichtbaar wordt wanneer de cel geselecteerd wordt.
Het zou natuurlijk met een als formule kunnen. =als(A2="merk";dan waarde;anders leeg)quote:
Mijn nieuwe bericht is blijkbaar weg.
Kort gezet wil ik met een dropdown menu werken in Excel, maar naargelang de keuze daarin moeten gegevens in een bepaalde cel ingevuld worden.
Bv.
Audi --> Kolom B
Mercedes --> Kolom C
Audi --> Kolom D
BYD --> Kolom E
Tesla --> Kolom F
Hoe maak je zoiets?
En dat zet je in de goede kolommen neer.
Nu excel 365 NL kan ik het niet meer vinden. Weet iemand wat het nu geworden is?
=ASELECT()quote:
Met vorige versies van excel kon ik met =rand() een willekeurig waarde neerzetten bij een lijst argumenten.
Nu excel 365 NL kan ik het niet meer vinden. Weet iemand wat het nu geworden is?
Dank, die zocht ik ja.quote:
Doet uiteindelijk weer niet helemaal wat ik wil.
wat wil je dan?quote:
[..]
Dank, die zocht ik ja.
Doet uiteindelijk weer niet helemaal wat ik wil.
Met =aselect() krijg je dubbelingen zag ik net.
Daar heb je gelijk in. aselect() levert een op zichzelf staand random getal en dat kan zomaar hetzelfde getal zijn als eerder.quote:
Ik heb een rij met gebeurtenissen. Daar wil ik dat excel er willekeurig een getal aan hangt. Min 1 max het aantal gebeurtenissen (zeg 40).
Met =aselect() krijg je dubbelingen zag ik net.
De beste oplossing die ik ken gaat als volgt:
=sorteren.op(reeks(40);aselect.matrix(40)) [in het nederlands met puntkomma's]
=sortby(sequence(40),randarray(40)) [in het engels met komma's]
Nu heb ik zelf niet de beschikking over een Nederlandse Excel, dus ik heb de Nederlandse formulenamen er bij gezocht en vermoed dat deze goed zijn.
De reden waarom deze formule wel goed gaat is omdat je een reeks maakt van unieke getallen die je vervolgens random door elkaar heen husselt. Zo voorkom je dat het maken van getallen een dubbeling kan opleveren.
Als ik zoiets nodig heb dan los ik het altijd op met ASELECT en RANG.quote:
Ik heb een rij met gebeurtenissen. Daar wil ik dat excel er willekeurig een getal aan hangt. Min 1 max het aantal gebeurtenissen (zeg 40).
Met =aselect() krijg je dubbelingen zag ik net.
Dus in tabblad 1 staat in kolom A: AGB-1234 en in tabblad 2 staat in kolom D ook AGB-1234. Dan wil ik een formule in tabblad 1 in kolom B invullen dat er een ja komt te staan. En als er in rij 2 van kolom A iets staat wat niet in tabblad B voorkomt moet er een nee komen te staan. Iemand tips?
Wij zitten met een stock, die stock bevat 2 producten.
Bv.:
- FOK membercards voor 1 maand
- FOK membercards voor 3 maanden
Maar beide producten zijn ook nog eens opgesplitst in 3 à 4 pakketjes, want die zogenaamde FOK membercards hebben een volgnummer. Dat volgnummer is voorgedrukt door de leverancier en is altijd anders.
- FOK membercards voor 1 maand
• 2000-2999
• 6000-6999
• 8000 - 8999
- FOK membercards voor 3 maanden
• 3000-3999
• 5000-5999
• 9000-9999
De medewerkers bij ons verkopen die kaarten in numerieke volgorde. Nu wil ik dat zeer overzichtelijk in een Excelbestand gieten.
Waarbij de medewerker na elke werkdag zijn verkoop invult.
Bestaat voor zoiets een kant-en-klaar template?
Bestaat er een mogelijkheid dat Excel weet uit welke pakketje er verkocht werd?
Zoals:
11/05/2023 --> Einzeinz --> Beginnummer --> Eindnummer en Excel moet vervolgens het aantal berekenen.
Maar ik zit dus met 3 (tot 4) stapeltjes. Nu loopt het dus fout want ik moet die stapeltjes zeker gescheiden houden.
Ik vraag me af of Excel ook kun zien van welke stapel hij die kaarten moet nemen?
huur een student in?
als je zelf wil leren ga dan voor een query - via youtube of udemy leer je het best snel
[ Bericht 24% gewijzigd door opgebaarde op 12-05-2023 00:48:53 ]
kolom 1 --> kaartnummer
kolom 2 --> de range waar dat nummer in valt
kolom 3 --> verkoopdatum
kolom 4 --> verkoper
kolom 5 --> aantal (dat zal dan telkens 1 zijn)
Ergens de beginaantallen registreren en dan is er mbv draaitabellen toch een actueel overzicht te maken met beschikbare aantallen? Ofzo?
Ik heb dus als het ware 2 producten in mijn assortiment. Kaarten van 1 maand en kaarten van 3 maanden (dat zijn fysieke kaarten).
Die kaarten zijn afkomstig van de leverancier en bevatten een volgnummer. Maar die verschillende paketten zijn met volgnummers die elkaar niet opvolgen. Dat zorgt er voor dat je meerdere pakketjes hebt.
Wat ik nu wil maken is het volgende:
Elke dag moet de medewerker een zeer klein Excelbestandje invullen maar dat moet zo kort mogelijk zijn.
- Datum van de verkoop
- Naam van de medewerker/verkoper
- Product (dus kaart 1 maand of 3 maanden)
- Beginnummer
- Eindnummer
Maar zoals je ziet in bovenstaand voorbeeld moet ik bepaalde kolommen gaan herhalen (omdat ik nl. met meerdere pakketjes zit)
Ik zou liever een overzichtelijke verkoop zien en dat in die kadertjes dan de huidige stand van de volgnummers opgehaald wordt en dat de medewerker ook ziet hoeveel kaarten nog aanwezig zijn.
Maar ik geraak er zelf totaal niet aan uit.
Ik snap overigens niet waarom je verkoopgegevens handmatig bijhoudt...
Ik zie de noodzakelijke waarde om kolommen G tm I en J tm L toe t voegen niet. Gewoon lekker onder elkaar.
Je kan in de eerste rijen zoals je nu hebt een staatjeper medewerker (of per product, per dag etc) maken met een een =sommen.als toevoegen die berekent hoeveel van de maand en van de 3maand kaarten verkocht zijn
Nog ergens een =min en een =max als controle op stocknummers
Dit soort schaduwadministraties zijn een bron voor ellende. Je hebt vast ergens de daadwerkelijke uitleveringen geregistreerd staan en dat kopieren naar een verantwoordingsdocument lijkt mij veel betrouwbaarder.quote:
Ik snap overigens niet waarom je verkoopgegevens handmatig bijhoudt...
En dan heb je een mini foutgevoelig ERP-systeem gebouwd.
Liefst van al heb ik dat inderdaad het liefst gewoon onder elkaar en dat de medewerker gewoon invult wat ze moeten invullen.quote:
Je hebt de som van de dag in kolom F (en I en L)
Ik zie de noodzakelijke waarde om kolommen G tm I en J tm L toe t voegen niet. Gewoon lekker onder elkaar.
Je kan in de eerste rijen zoals je nu hebt een staatjeper medewerker (of per product, per dag etc) maken met een een =sommen.als toevoegen die berekent hoeveel van de maand en van de 3maand kaarten verkocht zijn
Nog ergens een =min en een =max als controle op stocknummers
Het probleem is dat er meerdere stapels zijn, ik moet natuurlijk wel het verkocht aantal en resterend aantal per pakket weten.
Uit SAP kunnen wij een rapport trekken met hoeveel er besteld werken en er is ook een factuur, die factuur wordt vervolgens integraal betaald zonder dat daar controle op is.quote:
[..]
Dit soort schaduwadministraties zijn een bron voor ellende. Je hebt vast ergens de daadwerkelijke uitleveringen geregistreerd staan en dat kopieren naar een verantwoordingsdocument lijkt mij veel betrouwbaarder.
Het probleem is dat er de laatste maanden heel veel kaarten verdwenen/gestolen zijn door medewerkers maar we hebben geen spoor van wie of wanneer dat gebeurd is.
Om concreet te zijn. In het laatste pakket (die wij in februari hebben ontvangen) zaten 500 kaarten en daarvan zijn er 479 verkocht. Er ontbreken dus 21 kaarten. Maar bij wie zitten die kaarten en wanneer zijn ze verdwenen?
We willen nu korter op de bal spelen en de medewerkers laten noteren wie, wanneer, hoeveel er verkoopt. Het rapport van hen zullen we naast onze SAP cijfers leggen.
Op zich is het wel te doen om dat te maken maar het probleem is dat de volgnummers niet doorlopen.
En ik zit flink in de knoop met die volgnummers. Vandaag 8 uur bezig geweest aan die Excel, zonder resultaat. Het lukt me gewoon NIET om een overzichtelijk bestand te maken.
Ik dacht dat een voorraadlijst in Excel iets zeer eenvoudig was maar heb me er flink aan mispakt.
Heb er nu 14u op zitten zoeken en het lukt me niet
Ik heb een bestand met 3 tabbladen van max 2400 regels maar gek genoeg is t bestand nu al 40mb (!) terwijl hetzelfde bestand over voorgaande jaren meer regels had maar nooit over 2,5mb kwam. Ik vermoed daarom dat er ergens (veels te veel) data is mee gekopieerd…maar hoe vind ik dat?
Met (lengte) heb ik wel cellen gevonden met 30k tekens erin maar die bevonden zich ook in bestanden van voorgaande jaren.
En dan is 40MB nog steeds heel veel. Zitten er niet per ongeluk plaatjes in geplakt, of andere gekkigheid? Of eindeloze reeksen overbodige formules?
In verreweg de meeste gevallen wordt zoiets veroorzaakt door pivot tables, maar er is wel een trucje wat je kan toepassen. Let op dit werkt alleen met Office 365 in de browser-versie (ik hou zelf altijd van de lokale app, maar daar heb ik het knopje niet gevonden).quote:
Is er een manier te vinden welk tabblad of welke cel of regel of kolom het meest ruimte inneemt in termen van kb/mb?
Ik heb een bestand met 3 tabbladen van max 2400 regels maar gek genoeg is t bestand nu al 40mb (!) terwijl hetzelfde bestand over voorgaande jaren meer regels had maar nooit over 2,5mb kwam. Ik vermoed daarom dat er ergens (veels te veel) data is mee gekopieerd…maar hoe vind ik dat?
Met (lengte) heb ik wel cellen gevonden met 30k tekens erin maar die bevonden zich ook in bestanden van voorgaande jaren.
In de browser-versie heb je de Ribbon (tabblad) Review. Het 3e icoontje heet "Check Performance". Met deze feature kan je zien hoeveel van de cellen daadwerkelijk gebruikt worden versus welke cellen wel in het geheugen worden opgenomen. Je kan per sheet aangeven dat je de performance wil optimaliseren.
Aangezien je de online versie gebruikt, mochten de aanpassingen je niet aanstaan kan je via de versie-historie terug naar de vorige versie.
Eigenlijk nog een betere optie gevonden. 2 manieren om hetzelfde te bereiken.quote:
Is er een manier te vinden welk tabblad of welke cel of regel of kolom het meest ruimte inneemt in termen van kb/mb?
Ik heb een bestand met 3 tabbladen van max 2400 regels maar gek genoeg is t bestand nu al 40mb (!) terwijl hetzelfde bestand over voorgaande jaren meer regels had maar nooit over 2,5mb kwam. Ik vermoed daarom dat er ergens (veels te veel) data is mee gekopieerd…maar hoe vind ik dat?
Met (lengte) heb ik wel cellen gevonden met 30k tekens erin maar die bevonden zich ook in bestanden van voorgaande jaren.
Maak een kopie van je excel bestand en pas via Windows Verkenner de extensie van je bestand aan. Dus in plaats van .xlsx maak je er nu een .zip van.
Vervolgens open je via je verkenner de .zip bestand en dan klik je vervolgens op de submap xl en vervolgens Worksheets. In deze map zie je aan het geval van de worksheet de hoeveelste worksheet de betreffende ruimte inneemt.
Mocht het niet gewoon de data van de worksheet zijn, maar bijvoorbeeld plaatjes, dan kan je deze plaatjes vinden via "xl" en vervolgens "media". Als je geen media folder hebt, dan houdt dat in dat er geen plaatjes zijn toegevoegd.
Mocht je niet bekend zijn met de wijze waarop je de naam van de extensie van een bestand aanpast, dan kan je hetzelfde bereiken door het bestand via het menu van "Opslaan als" en dan vervolgens het bestandtype daar aan te passen aan Webpagina htm.
In de folder waar je hem opslaat zie je dezelfde type structuur en dan dus ook echt per werkblad de grootte van dat werkblad.
Het ziet er nu als volgt uit voor een particulier (een van de opties):
| 1 2 3 4 | A B C D Item Prijs (incl. BTW) BTW (L/H) BTW-bedrag Bezorgkosten 4,95 L 0,41 Bezorgkosten 4,95 H 0,86 |
Voor zakelijke klanten ziet het er als volgt uit (een van de opties):
| 1 2 3 4 | A B C D Item Prijs (excl. BTW) BTW (L/H) BTW-bedrag Bezorgkosten 4,54 L 0,41 Bezorgkosten 4,09 H 0,86 |
In cel F1 (buiten beeld) staat aangegeven of een factuur Incl. BTW of Excl. BTW moet worden opgemaakt. Deze keuze wordt gebruikt om de juiste waarde in kolom B (Prijs) te bepalen. Vervolgens wordt in kolom D het BTW-bedrag berekend, wat mede afhankelijk van de handmatig ingevoerde waarde in kolom C (L of H).
Welke oplossing kan ik het beste gebruiken om in kolom D de uitkomst van het BTW-tarief te berekenen?
Ik wil één formule/oplossing gebruiken die rekening houdt met de volgende scenario's:
• ALS (F1 = Incl. BTW) en ALS (C3 = L) => (BTW-bedrag = (Prijs - (Prijs/1,09))
• ALS (F1 = Incl. BTW) en ALS (C3 = H) => (BTW-bedrag = (Prijs - (Prijs/1,21))
• ALS (F1 = Excl. BTW) en ALS (C3 = L) => (BTW-bedrag = (Prijs * 0,09))
• ALS (F1 = Excl. BTW) en ALS (C3 = H) => (BTW-bedrag = (Prijs * 0,21))
Volgens mij moet het kunnen met geneste IF's, maar ik zie door de bomen het bos niet meer. Heb ook geen idee of ik het logisch uitleg.
ALS(EN(F$1="Incl. BTW";C3="L");B3-(B3/1,09);ALS(EN(F$1="Incl. BTW";C3="H");B3-(B3/1,21);ALS(EN(F$1="Excl. BTW";C3="L");B3*0,09;ALS(EN(F$1="Excl. BTW";C3="H");B3*0,21;""))))quote:
Ik heb een relatief eenvoudige facturatietool in Excel (=Google Sheets) voor levering van goederen. Voor onderscheid tussen particuliere en zakelijke klanten wil ik nu een berekening maken van de verzendkosten inclusief en exclusief BTW. Dat lukt nog, maar afhankelijk van welke goederen verstuurd worden, zijn de verzendkosten hoog of laag BTW-tarief (21% of 9%).
Het ziet er nu als volgt uit voor een particulier (een van de opties):
[ code verwijderd ]
Voor zakelijke klanten ziet het er als volgt uit (een van de opties):
[ code verwijderd ]
In cel F1 (buiten beeld) staat aangegeven of een factuur Incl. BTW of Excl. BTW moet worden opgemaakt. Deze keuze wordt gebruikt om de juiste waarde in kolom B (Prijs) te bepalen. Vervolgens wordt in kolom D het BTW-bedrag berekend, wat mede afhankelijk van de handmatig ingevoerde waarde in kolom C (L of H).
Welke oplossing kan ik het beste gebruiken om in kolom D de uitkomst van het BTW-tarief te berekenen?
Ik wil één formule/oplossing gebruiken die rekening houdt met de volgende scenario's:
• ALS (F1 = Incl. BTW) en ALS (C3 = L) => (BTW-bedrag = (Prijs - (Prijs/1,09))
• ALS (F1 = Incl. BTW) en ALS (C3 = H) => (BTW-bedrag = (Prijs - (Prijs/1,21))
• ALS (F1 = Excl. BTW) en ALS (C3 = L) => (BTW-bedrag = (Prijs * 0,09))
• ALS (F1 = Excl. BTW) en ALS (C3 = H) => (BTW-bedrag = (Prijs * 0,21))
Volgens mij moet het kunnen met geneste IF's, maar ik zie door de bomen het bos niet meer. Heb ook geen idee of ik het logisch uitleg.
Ik kon niet slapen
.Moet het nog even uitgebreider testen, maar het lijkt de oplossing. Dankjewelquote:
[..]
ALS(EN(F$1="Incl. BTW";C3="L");B3-(B3/1,09);ALS(EN(F$1="Incl. BTW";C3="H");B3-(B3/1,21);ALS(EN(F$1="Excl. BTW";C3="L");B3*0,09;ALS(EN(F$1="Excl. BTW";C3="H");B3*0,21;""))))
Ik kon niet slapen
Ik hoop dat je inmiddels de slaap hebt kunnen vatten
opgelost.
SPOILER: Dit was de vraagHoi. Ik had een bepaald format in excel wat ik wilde kopieren over 9 verschillende bestanden. Voor iedere maand vanaf september '23 tot en met juni '24. September was de basis en daarvan moet gecloned worden.
Met behulp van chat gpt kwam ik aan een vba code die dit snel en eenvoudig voor elkaar zou moeten krijgen. (ik ben een 100% leek dus alles komt van chat gpt.).
Echter ik loop vast omdat ik waarschijnlijk niet de juiste vragen stel.
Dit is de code :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29Sub CreateMonthlyWorkbooksWithTabs()
Dim sourceWs As Worksheet
Dim newWorkbook As Workbook
Dim ws As Worksheet
Dim i As Integer, j As Integer
' Definieer het bronwerkblad (het eerste tabblad)
Set sourceWs = ThisWorkbook.Sheets("vr-1-9-23")
' Loop door de maanden van september 2023 tot juni 2024
For i = 9 To 6 Step -1
Dim newFileName As String
newFileName = Format(DateSerial(2023, i, 1), "mmmm yyyy") & ".xlsx"
' Maak een nieuw werkboek en voeg tabbladen toe
Set newWorkbook = Workbooks.Add
For j = 1 To 31 ' Maak 31 tabbladen
Set ws = newWorkbook.Sheets.Add
ws.Name = "Day " & j
sourceWs.Cells.Copy Destination:=ws.Cells
Next j
' Hernoem het tabblad en sla het werkboek op met de gewenste naam
ws.Name = Format(DateSerial(2023, i, 1), "mmmm yyyy")
newWorkbook.SaveAs newFileName
' Sluit het nieuwe werkboek zonder wijzigingen op te slaan
newWorkbook.Close SaveChanges:=False
Next i
En opzich werkt die code goed. Maar hij maakt niet zoals gevraagd bestanden oktober 2023 t/m juni 2024, maar hij gaat terugtellen van september 2023 naar augstus 203 naar juli 2023 en naar juni 2023.
Ik eindig daarmee dus met 4 bestanden i.p.v. 9. Welllicht dat Chat gpt iets met het jaar niet lekker voor elkaar krijgt. Maar Excel-experts, kijkend naar deze code, wat zou er gewijzigd kunnen worden zodat ik wel 9 kopiën kan krijgen van mijn bronbestand?
Gokje, daar van 6 1 maken?quote:For i = 9 To 6 Step -1
Nogmaals aan Chat gpt gevraagd (ik had al 4 verschillende codes van hem gehad) of het verspringen van het jaartal een oorzaak kon zijn. Dat werd bevestigd.quote:
Toen kreeg ik dit terug.
En dat werktSPOILER
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37Sub CreateMonthlyWorkbooksWithTabs()
Dim sourceWs As Worksheet
Dim newWorkbook As Workbook
Dim ws As Worksheet
Dim currentDate As Date
Dim i As Integer, j As Integer
' Definieer het bronwerkblad (het eerste tabblad)
Set sourceWs = ThisWorkbook.Sheets("vr-1-9-23")
' Stel de startdatum in als 1 september 2023
currentDate = DateSerial(2023, 9, 1)
' Loop door de maanden van september 2023 tot juni 2024
For i = 1 To 10
Dim newFileName As String
newFileName = Format(currentDate, "mmmm yyyy") & ".xlsx"
' Maak een nieuw werkboek en voeg tabbladen toe
Set newWorkbook = Workbooks.Add
For j = 1 To 31 ' Maak 31 tabbladen
Set ws = newWorkbook.Sheets.Add
ws.Name = "Day " & j
sourceWs.Cells.Copy Destination:=ws.Cells
Next j
' Hernoem het tabblad en sla het werkboek op met de gewenste naam
ws.Name = Format(currentDate, "mmmm yyyy")
newWorkbook.SaveAs newFileName
' Sluit het nieuwe werkboek zonder wijzigingen op te slaan
newWorkbook.Close SaveChanges:=False
' Ga naar de volgende maand
currentDate = DateAdd("m", 1, currentDate)
Next i
End Sub
Ach. Chat gpt is ook pas een jaartje oud. Geef het wat tijdquote:
Alleen Chat GPT zou iets verzinnen met een for loop en stapjes van -1
Het is een magisch knap ding verder, alleen ik kan me niet voorstellen dat dit het resultaat is van trainen op miljoenen voorbeelden. Geen mens zou beginnen met stapjes van -1quote:
[..]
Ach. Chat gpt is ook pas een jaartje oud. Geef het wat tijd
Ligt aan je vraagstelling natuurlijk, als je uitgaat van je doel dan moet je terugrekenen. Als je begint bij 'nu' dan moet je optellenquote:
Alleen Chat GPT zou iets verzinnen met een for loop en stapjes van -1
Eerst functie:
=ALS(D4="";"";LINKS(D4;VIND.SPEC(" ";D4;1)))
Hier zoekt Excel op voornaam (alles voor de spatie)
Tweede functie:
=VERT.ZOEKEN(E4;Medewerkers!A:D;3;ONWAAR)
Vervolgens moet die voornaam omgezet worden naar een personeelsnummer.
Alleen kan Excel dat niet omzetten omdat kolom E4 een formule is. Die functie moet dus genest worden.
Je VIND.SPEC-functie geeft de positie van de spatie. Daarna maakt de LINKS-Functie van "Jan Hendriks" dus "Jan " (met de spatie erachter). En die kan ie dan weer niet vinden in je namen/personeelsnummerlijst want daar staan ongetwijfeld namen zonder spaties erachter.
=ALS(D4="";"";LINKS(D4;VIND.SPEC(" ";D4;1)-1))
Moet het volgens mij oplossen.
Klopt wat je zegt, bedanktquote:
Ik gok dat ik weet waar het probleem zit, en dat is niet omdat je het zou moeten nesten.
Je VIND.SPEC-functie geeft de positie van de spatie. Daarna maakt de LINKS-Functie van "Jan Hendriks" dus "Jan " (met de spatie erachter). En die kan ie dan weer niet vinden in je namen/personeelsnummerlijst want daar staan ongetwijfeld namen zonder spaties erachter.
=ALS(D4="";"";LINKS(D4;VIND.SPEC(" ";D4;1)-1))
Moet het volgens mij oplossen.
Plaatje uploaden lukt me ook niet meer, gaat lekker....
quote:
Ik doe iets fout, maar zie niet wat helaas, jullie misschien? Thanks!
[ afbeelding ]
Plaatje uploaden lukt me ook niet meer, gaat lekker....
Geen idee, bij mij komt er 3 uit.
Bedankt, maar ik moet nog even verder klooien zo te zien, kom erop terug.quote:Op vrijdag 13 oktober 2023 13:17 schreef Evangelion het volgende:
[..]
[ afbeelding ]
Geen idee, bij mij komt er 3 uit.
Dit is mijn huidige formule:
In principe bestaat die formulie uit de onderstaande, maar dan een aantal keer gekopieerd en achter elkaar geplakt met andere parameters:SPOILER=IF(G12="en_US";VLOOKUP(N12;Phrase_export!A:AF;3;FALSE);"")&IF(G12="cs_CZ";VLOOKUP(N12;Phrase_export!A:AF;4;FALSE);"")&IF(G12="da_DK";VLOOKUP(N12;Phrase_export!A:AF;5;FALSE);"")&IF(G12="de_DE";VLOOKUP(N12;Phrase_export!A:AF;6;FALSE);"")&IF(G12="de_AT";VLOOKUP(N12;Phrase_export!A:AF;7;FALSE);"")&IF(G12="el_GR";VLOOKUP(N12;Phrase_export!A:AF;8;FALSE);"")&IF(G12="en_AU";VLOOKUP(N12;Phrase_export!A:AF;9;FALSE);"")&IF(G12="en_GB";VLOOKUP(N12;Phrase_export!A:AF;10;FALSE);"")&IF(G12="en_NZ";VLOOKUP(N12;Phrase_export!A:AF;11;FALSE);"")&IF(G12="es_ES";VLOOKUP(N12;Phrase_export!A:AF;12;FALSE);"")&IF(G12="et_EE";VLOOKUP(N12;Phrase_export!A:AF;13;FALSE);"")&IF(G12="fi_FI";VLOOKUP(N12;Phrase_export!A:AF;14;FALSE);"")&IF(G12="fr_FR";VLOOKUP(N12;Phrase_export!A:AF;15;FALSE);"")&IF(G12="hr_HR";VLOOKUP(N12;Phrase_export!A:AF;16;FALSE);"")&IF(G12="hu_HU";VLOOKUP(N12;Phrase_export!A:AF;17;FALSE);"")&IF(G12="it_IT";VLOOKUP(N12;Phrase_export!A:AF;18;FALSE);"")&IF(G12="ja_JA";VLOOKUP(N12;Phrase_export!A:AF;19;FALSE);"")&IF(G12="ko_KO";VLOOKUP(N12;Phrase_export!A:AF;20;FALSE);"")&IF(G12="lt_LT";VLOOKUP(N12;Phrase_export!A:AF;21;FALSE);"")&IF(G12="lv_LV";VLOOKUP(N12;Phrase_export!A:AF;22;FALSE);"")&IF(G12="no_NO";VLOOKUP(N12;Phrase_export!A:AF;23;FALSE);"")&IF(G12="nl_NL";VLOOKUP(N12;Phrase_export!A:AF;24;FALSE);"")&IF(G12="pl_PL";VLOOKUP(N12;Phrase_export!A:AF;25;FALSE);"")&IF(G12="pt_PT";VLOOKUP(N12;Phrase_export!A:AF;26;FALSE);"")&IF(G12="pt_BR";VLOOKUP(N12;Phrase_export!A:AF;27;FALSE);"")&IF(G12="ro_RO";VLOOKUP(N12;Phrase_export!A:AF;28;FALSE);"")&IF(G12="sl_SI";VLOOKUP(N12;Phrase_export!A:AF;29;FALSE);"")&IF(G12="sv_SE";VLOOKUP(N12;Phrase_export!A:AF;30;FALSE);"")
=IF(G12="en_US";VLOOKUP(N12;Phrase_export!A:AF;3;FALSE);"")
Wat ik hiermee wil bereiken is dat als de G12 cel en_US bevat, de VLOOKUP functie een bepaalde waarde uit de 3e kolom van Phrase_export moet halen.
Verderop in mijn grote formule heb ik dezelfde VLOOKUP maar dan voor als G12 (of andere cellen) niet en_US maar en_GB heeft.
en_US is onze fallback, dus als er in en_GB niks staat, moet de functie de waarde uit en_US nemen.
Weet iemand hoe ik dit doe?
Lastige vraagstelling, nu ik het teruglees.quote:
Iemand een idee welke functie ik moet gebruiken voor volgende situatie.
Dit is mijn huidige formule:In principe bestaat die formulie uit de onderstaande, maar dan een aantal keer gekopieerd en achter elkaar geplakt met andere parameters:SPOILER=IF(G12="en_US";VLOOKUP(N12;Phrase_export!A:AF;3;FALSE);"")&IF(G12="cs_CZ";VLOOKUP(N12;Phrase_export!A:AF;4;FALSE);"")&IF(G12="da_DK";VLOOKUP(N12;Phrase_export!A:AF;5;FALSE);"")&IF(G12="de_DE";VLOOKUP(N12;Phrase_export!A:AF;6;FALSE);"")&IF(G12="de_AT";VLOOKUP(N12;Phrase_export!A:AF;7;FALSE);"")&IF(G12="el_GR";VLOOKUP(N12;Phrase_export!A:AF;8;FALSE);"")&IF(G12="en_AU";VLOOKUP(N12;Phrase_export!A:AF;9;FALSE);"")&IF(G12="en_GB";VLOOKUP(N12;Phrase_export!A:AF;10;FALSE);"")&IF(G12="en_NZ";VLOOKUP(N12;Phrase_export!A:AF;11;FALSE);"")&IF(G12="es_ES";VLOOKUP(N12;Phrase_export!A:AF;12;FALSE);"")&IF(G12="et_EE";VLOOKUP(N12;Phrase_export!A:AF;13;FALSE);"")&IF(G12="fi_FI";VLOOKUP(N12;Phrase_export!A:AF;14;FALSE);"")&IF(G12="fr_FR";VLOOKUP(N12;Phrase_export!A:AF;15;FALSE);"")&IF(G12="hr_HR";VLOOKUP(N12;Phrase_export!A:AF;16;FALSE);"")&IF(G12="hu_HU";VLOOKUP(N12;Phrase_export!A:AF;17;FALSE);"")&IF(G12="it_IT";VLOOKUP(N12;Phrase_export!A:AF;18;FALSE);"")&IF(G12="ja_JA";VLOOKUP(N12;Phrase_export!A:AF;19;FALSE);"")&IF(G12="ko_KO";VLOOKUP(N12;Phrase_export!A:AF;20;FALSE);"")&IF(G12="lt_LT";VLOOKUP(N12;Phrase_export!A:AF;21;FALSE);"")&IF(G12="lv_LV";VLOOKUP(N12;Phrase_export!A:AF;22;FALSE);"")&IF(G12="no_NO";VLOOKUP(N12;Phrase_export!A:AF;23;FALSE);"")&IF(G12="nl_NL";VLOOKUP(N12;Phrase_export!A:AF;24;FALSE);"")&IF(G12="pl_PL";VLOOKUP(N12;Phrase_export!A:AF;25;FALSE);"")&IF(G12="pt_PT";VLOOKUP(N12;Phrase_export!A:AF;26;FALSE);"")&IF(G12="pt_BR";VLOOKUP(N12;Phrase_export!A:AF;27;FALSE);"")&IF(G12="ro_RO";VLOOKUP(N12;Phrase_export!A:AF;28;FALSE);"")&IF(G12="sl_SI";VLOOKUP(N12;Phrase_export!A:AF;29;FALSE);"")&IF(G12="sv_SE";VLOOKUP(N12;Phrase_export!A:AF;30;FALSE);"")
=IF(G12="en_US";VLOOKUP(N12;Phrase_export!A:AF;3;FALSE);"")
Wat ik hiermee wil bereiken is dat als de G12 cel en_US bevat, de VLOOKUP functie een bepaalde waarde uit de 3e kolom van Phrase_export moet halen.
Verderop in mijn grote formule heb ik dezelfde VLOOKUP maar dan voor als G12 (of andere cellen) niet en_US maar en_GB heeft.
en_US is onze fallback, dus als er in en_GB niks staat, moet de functie de waarde uit en_US nemen.
Weet iemand hoe ik dit doe?
In principe wil ik zoiets als:
Als cel A1 = en_GB dan VLOOKUP in XYZ en als in XYZ niks staat, dan diezelfde VLOOKUP in ABC.
Ja, dankjewel! Zoiets speelde ChatGPT ook terug. Heb de hele middag zitten kloten, YouTube tutorials en zo... Maar nee hoor, helemaal niks.quote:
Als er niks in XYZ staat, krijg je een fout. Toch?. Dan kan je "isfout" in je formule verwerken.
Nu heb ik even een dickmove gespeeld en het mijn collega gegeven. Ga morgen op vakantie en dit lukte gewoon niet meer.
Zoiets zou al voldoende moeten zijn in plaats van zoveel nesten.
[ Bericht 11% gewijzigd door Tegan op 15-11-2023 18:41:21 ]
Ik heb een stamtabel met daarin contract nummer, datum waarin een prijs actief is, en de prijs.
Ik wil zoeken op datum én contractnummer. Dit doen we met twee zoeksleutels mijn inziens, voor de datum en contractnummer. Datum (en die formule enkelzijdig werkt gewoon) gebruik ik met -1 om de prijs te vinden, maar als ik met tweede zoeksleutel voor het vinden van het contractnummer dan krijg ik een NB
=INDEX(J23:L30;VERGELIJKEN(O24;J23:J30;0)&VERGELIJKEN(P24;K23:K30;-1);3)
Ik zit even niet achter een computer, maar je eerste vergelijken levert in je voorbeeld een 2 op en de tweede ook, dus krijg je door je & toch een index met 22? Dat bereik heeft je matrix niet.quote:
Vraag:
Ik heb een stamtabel met daarin contract nummer, datum waarin een prijs actief is, en de prijs.
Ik wil zoeken op datum én contractnummer. Dit doen we met twee zoeksleutels mijn inziens, voor de datum en contractnummer. Datum (en die formule enkelzijdig werkt gewoon) gebruik ik met -1 om de prijs te vinden, maar als ik met tweede zoeksleutel voor het vinden van het contractnummer dan krijg ik een NB
=INDEX(J23:L30;VERGELIJKEN(O24;J23:J30;0)&VERGELIJKEN(P24;K23:K30;-1);3)
[ afbeelding ]
Volgens mij moet je je in MATCH verdiepen, of als je een recente versie van excel gebruikt filter. Match met meerdere criteria moet met array formules en het gebruik van * ipv &
[ Bericht 2% gewijzigd door snabbi op 16-11-2023 18:43:44 ]
Ik zou dit met een hulpkolom oplossen (met daarin je contractnummer * 100000 + de datum), maar je kunt ook met INDIRECT gaan klooien om de 2e vergelijker te laten beginnen op de eerste regel van het juiste contractnummer. Maar daarvoor moet de reeks wel juist gesorteerd zijn, anders lukt dat ook niet.quote:
Vraag:
Ik heb een stamtabel met daarin contract nummer, datum waarin een prijs actief is, en de prijs.
Ik wil zoeken op datum én contractnummer. Dit doen we met twee zoeksleutels mijn inziens, voor de datum en contractnummer. Datum (en die formule enkelzijdig werkt gewoon) gebruik ik met -1 om de prijs te vinden, maar als ik met tweede zoeksleutel voor het vinden van het contractnummer dan krijg ik een NB
=INDEX(J23:L30;VERGELIJKEN(O24;J23:J30;0)&VERGELIJKEN(P24;K23:K30;-1);3)
[ afbeelding ]
Oef, das best ingewikkeld, mijn inziens gaat de formule bij mij fout of er mist een operator..quote:
[..]
Ik zit even niet achter een computer, maar je eerste vergelijken levert in je voorbeeld een 2 op en de tweede ook, dus krijg je door je & toch een index met 22? Dat bereik heeft je matrix niet.
Volgens mij moet je je in MATCH verdiepen, of als je een recente versie van excel gebruikt filter. Match met meerdere criteria moet met array formules en het gebruik van * ipv &
Thanks!quote:
[..]
Ik zou dit met een hulpkolom oplossen (met daarin je contractnummer * 100000 + de datum), maar je kunt ook met INDIRECT gaan klooien om de 2e vergelijker te laten beginnen op de eerste regel van het juiste contractnummer. Maar daarvoor moet de reeks wel juist gesorteerd zijn, anders lukt dat ook niet.
Werkt in database bestand contractnummer (is al eineloos lang nummer) + datum (moet van . naar - gaan maar swa), en dan heb je uniek nummer, en idem dito in stamtabel.
Zo simpel, zo goed!
Maar sta altijd open als iemand de formule wel zou weten als je het helemaal nice wil doen
ik gebruik voor een 20 tal lijnen onderstaande sortering (wk 48)
dan wil ik een lijn vrijlaten en dezelfde sortering maken voor wk 49
is er een optie om die sortering te kopieren?
thx
Een echte oplossing heb ik niet (buiten het programmeren met macro's om).quote:
hoi,
ik gebruik voor een 20 tal lijnen onderstaande sortering (wk 48)
[ afbeelding ]
dan wil ik een lijn vrijlaten en dezelfde sortering maken voor wk 49
is er een optie om die sortering te kopieren?
thx
Wel heb ik een workaround die je kan gebruiken. De workaround gaat als volgt:
stap 1: maak van je huidige wk 48 een echte tabel (ipv de losse data invoer die je wellicht normaal doet). Het maken van een tabel doe je via CTRL T. Vul gewoon netjes de kolommen/rijen in van je wk48 (en of je tabel ook headers heeft of niet).
Je ziet of het gelukt is wanneer je cellen er ineens gekleud uit zien.
Stap 2: voer in je tabel de sorteer opties in van je eigen voorkeur.
Stap 3: kopieer de tabel naar je cellen aan de rechterkant. [eventueel kan je je echte data van wk 49 daarna er gewoon overheen plakken]
Stap 4: uit je sort & filter dingetje kan je nu direct de reapply knop gebruiken.
Helpt helaas nietquote:
Er valt omheen te werken als ik een ander bestand verbouw, maar het leek me handiger deze Excelroute te volgen. Als ik de aanhalingstekens onder controle krijg ben ik erquote:
Eigenlijk vraag ik me ook af in hoeverre het uberhaupt slim is. Door je line-break gaat je input applicatie toch ook de cellen verkeerd inlezen?
Het is niet de meest elegante oplossing, dat geef ik gelijk toequote:
Ik heb het geprobeerd en kreeg het niet voor elkaar. Alleen wanneer je een spatie-gescheiden format neemt, maar dan ben je natuurlijk de scheiding tussen je cellen ook kwijt.
Eigenlijk vraag ik me ook af in hoeverre het uberhaupt slim is. Door je line-break gaat je input applicatie toch ook de cellen verkeerd inlezen?
Over verkeerd inlezen maak ik me niet zo druk. Ik wil hiermee informatie doorpompen naar Photoshop en dan is het met line breaks prima te doen, want die hoeft het alleen maar in beeld te zetten. Ik klooi nog even door Ik heb nu alle nodige cellen naast elkaar gezet, in plaats van alles via een formule met tussenkomma's in een enkele cel te proppen. Sla je dat als csv op dan krijg je ; als scheidingsteken, wat Photoshop weer niet tof vindt. Maar... txt met tabs pakt ie wel
. Bedankt voor de hulp Hoe doe ik dat het beste?
=SOM(INDIRECT("'"&A2&"'!B:B"))quote:
Een vraag: ik heb een excelbestand met veel verschillende tabbladen. Ik wil per regel een som uitvoeren van steeds hetzelfde bereik (B:B) maar wel steeds op een verschillend blad. Ik wil de formule in kolom B2 kunnen doortrekken zodat deze steeds de som van B:B neemt op het tabblad met de naam die in A1, A2, A3, etc. staat.
Hoe doe ik dat het beste?
[ afbeelding ]
De formule INDIRECT maakt van een geschreven tekst een verwijzing naar de reeks cellen.
In feite is je directe verwijzing naar het andere tabblad: '2'!B:B
Hierbij is die 2 je variabele die je in de A kolom opslaat. Omdat je zowel normale tekst voor als na die A2 krijgt, krijg je die niet zo goed leesbare combinatie van "'"
PS
Als je gewoon de som wil over al die tabbladen zonder dit in een tabel weer te geven, dan werkt dit ook gewoon:
=SOM('1:4'!B:B)
De enige voorwaarde is wel dat het meest linker tabblad de 1 is en de meest rechter de 4. Alles wat tussen die 2 tabbladen zit, neemt ie dan mee.
Ik wil op blad 1 gewoon normaal getallen typen zoals we dat hier in Nederland doen, maar op blad 2 wil ik het een soort van presenteren voor een stel buitenlanders die het blijkbaar andersom doen.
Ik weet wel hoe je in Excel instellingen die zooi kan aanpassen, maar ik wil het het liefst via celopmaak voor specifieke cellen doen.
Nee, want ik wil in één blad werken en in het 'presenteer-blad' verwijzen naar de zooi waar ik mee werk, alleen dan op de manier met komma's en punten andersom.quote:
Een van de twee als tekst opbouwen/weergeven is zeker geen optie?
SUBSTITUEREN(Blad1!A1;",";".")quote:
[..]
Nee, want ik wil in één blad werken en in het 'presenteer-blad' verwijzen naar de zooi waar ik mee werk, alleen dan op de manier met komma's en punten andersom.
Als het alleen maar een weergave-blad hoeft te zijn dan kun je dat met SUBSTITUEREN wel oplossen, maar dan kun je niet meer rekenen met wat je overhoudt. Als dat geen probleem is, zie de post van Teganquote:
[..]
Nee, want ik wil in één blad werken en in het 'presenteer-blad' verwijzen naar de zooi waar ik mee werk, alleen dan op de manier met komma's en punten andersom.
Ja, maar dan is het geen getal meer. Ik wil eigenlijk in dat presentatieblad ook nog kunnen optellen.quote:
Alhoewel ik dat zou kunnen omzeilen door alles met die code over te brengen en alleen op te tellen op m'n werkblad
Ik ga er even over nadenken thnx
Je kan ook weer terug SUBSTITUEREN met nog een WAARDE() er omheen bijvoorbeeldquote:
[..]
Ja, maar dan is het geen getal meer. Ik wil eigenlijk in dat presentatieblad ook nog kunnen optellen.
Alhoewel ik dat zou kunnen omzeilen door alles met die code over te brengen en alleen op te tellen op m'n werkblad
Ik ga er even over nadenken thnx
.Ja, maar je krijgt het getal 3,75 nooit als 3.75 in beeld zodat Excel nog steeds denkt dat het een getal is.quote:
[..]
Je kan ook weer terug SUBSTITUEREN met nog een WAARDE() er omheen bijvoorbeeld
Nee precies. Maar hiermee kan je wel rekenen op het tweede tabblad en nog een keer SUBSTITUEREN voor het weergeven.quote:
[..]
Ja, maar je krijgt het getal 3,75 nooit als 3.75 in beeld zodat Excel nog steeds denkt dat het een getal is.
Ja, dat kan. Maar triviale formules als SOM en GEMIDDELDE worden al ronduit ellendig als je die moet nesten met SUBSTITUEREN om met die nepgetallen te kunnen werken. Moet je echt niet willen wmb.quote:
[..]
Nee precies. Maar hiermee kan je wel rekenen op het tweede tabblad en nog een keer SUBSTITUEREN voor het weergeven.
- Dus wanneer de personen met het andere getallenstelsel met je bestand moeten werken, dan gaat dit gewoon goed op hun eigen systemen.
- Wanneer je iets moet presenteren, dan heb je de calculatieoptie niet nodig en kan je gewoon het format kiezen wat je dan goed uitkomt.
En als je in een presentatie met beide partijen moet werken, dan werkt het wisselen van notatievorm alleen maar verwarrend voor iedereen. En wil je dat echt perse doen dan zou ik lekker je onedrive/sharepoint web-based versie op amerikaaans zetten terwijl je excel zelf op nederlands zet. Via alt tab met 2 verschillende views hetzelfde bestand tonen.
Het probleem is dat ik het irritant vind om zelf te werken met ./, en ik werk in dit bestand, niemand anders. De rest ziet het presentatiegedeelte wat in dat format moet.quote:
Om heel eerlijk te zijn begrijp ik het probleem niet erg. De instellingen voor het scheidingsteken van decimalen en duizendtallen is iets wat je op het niveau van de computer (windows) of op het niveau van excel vaststelt voor de gebruiker.
- Dus wanneer de personen met het andere getallenstelsel met je bestand moeten werken, dan gaat dit gewoon goed op hun eigen systemen.
- Wanneer je iets moet presenteren, dan heb je de calculatieoptie niet nodig en kan je gewoon het format kiezen wat je dan goed uitkomt.
En als je in een presentatie met beide partijen moet werken, dan werkt het wisselen van notatievorm alleen maar verwarrend voor iedereen. En wil je dat echt perse doen dan zou ik lekker je onedrive/sharepoint web-based versie op amerikaaans zetten terwijl je excel zelf op nederlands zet. Via alt tab met 2 verschillende views hetzelfde bestand tonen.
Tuurlijk is het elke keer als ik iets presenteer wel om te zetten en te checken, maar ik was opzoek naar een structurele oplossing.
Nu weet ik wel een manier met DEEL en CODE enzo, maar die is behoorlijk omslachtig. Is er een snellere manier om in deze lijst alle 'foute' entries bovenaan te krijgen, of via voorwaardelijke opmaak te signaleren?
Een blonde en te simpele manier is het via zoeken en selecteren -> vervangen te doen.quote:
Ik heb een (hele) lange lijst van lettercombinaties van exact vijf letters. Er staan hoofd- en kleine letters in en dat moeten allemaal kleine letters worden, maar dat is nog niet zo moeilijk. Helaas zitten er in een klein aantal van die combinaties ook een letter met een diakritisch teken (á, ë etc.) en die moeten eruit. Helaas, vanwege het doel van die strings, is het niet zo makkelijk om alle ë's te vervangen door e's, of zoiets, dat moet handmatig. Dat vervangen zelf is nog wel te doen want het gaat om nog geen 1% van de totale lijst, maar het vinden is een crime. We willen geen lijst van 10.000 regels handmatig doorzoeken.
Nu weet ik wel een manier met DEEL en CODE enzo, maar die is behoorlijk omslachtig. Is er een snellere manier om in deze lijst alle 'foute' entries bovenaan te krijgen, of via voorwaardelijke opmaak te signaleren?
Vervang ë door e.
Dat gaat dus niet. Niet alle ë's moeten namelijk een e worden, maar sommige ook iets anders. Niet handig, weet ik, maar binnen de context is er niks aan te doen.quote:
[..]
Een blonde en te simpele manier is het via zoeken en selecteren -> vervangen te doen.
Vervang ë door e.
Kan zo geen functie vinden die dit met array's kan.
Oh, dit zou nog wel eens wat op kunnen leveren. Hier ga ik even mee prutsen.quote:
Alle combi's een keer uittypen en nesten in een paar SUBSTITUEREN()?
Kan zo geen functie vinden die dit met array's kan.
Kan je op een andere manier een onderscheid maken in welke ë's wat moeten worden?quote:
[..]
Dat gaat dus niet. Niet alle ë's moeten namelijk een e worden, maar sommige ook iets anders. Niet handig, weet ik, maar binnen de context is er niks aan te doen.
Ja, het heeft te maken met wat ervoor en erachter staat. Dus vandaar dat de tip van Tegan nog wel eens wat op kan leveren.quote:
[..]
Kan je op een andere manier een onderscheid maken in welke ë's wat moeten worden?
Blijf ik met het probleem zitten dat ik straks nog steeds niet zeker weet of ik alles gehad heb, maar dat zien we dan wel weer..
Ja, dat is dus een beetje het probleem, dat weten we niet precies. We hebben de eerste 200 regels gescand en daar kwamen we 2 hits in tegen. Er zijn best wat verschillende tekens mogelijk, maar welke we wel en niet gaan tegenkomen weten we niet, alleen hoe we ze moeten vervangen als ze bestaan.quote:
Met zoeken en vervangen kun je ook zeg aë vervangen met ae. Afhankelijk van hoeveel combi's er zijn is ofwel zoeken en vervangen ofwel een paar keer SUBSTITUEREN() nesten sneller.
Dus ik wacht nog even of er nog iemand met een briljant idee op de proppen komt, en anders dan ga ik 'm toch maar even teken voor teken checken via CODE. Weet ik in elk geval zeker dat we niks gemist hebben.
Maar begrijp ik nu goed dat het echte probleem is, dat je niet zeker weet welke bijzondere karakters je tegen komt? Want toen je zei dat niet alle ë's een e worden, dan wordt het toch wel wat moeilijker.quote:
[..]
Ja, dat is dus een beetje het probleem, dat weten we niet precies. We hebben de eerste 200 regels gescand en daar kwamen we 2 hits in tegen. Er zijn best wat verschillende tekens mogelijk, maar welke we wel en niet gaan tegenkomen weten we niet, alleen hoe we ze moeten vervangen als ze bestaan.
Dus ik wacht nog even of er nog iemand met een briljant idee op de proppen komt, en anders dan ga ik 'm toch maar even teken voor teken checken via CODE. Weet ik in elk geval zeker dat we niks gemist hebben.
Dus gaat het nu om alle mogelijke bijzondere karakters op te sporen of gaat het om gewoon alles om te zetten?
In VBA heb je de volgende code nodig. Mocht je daar minder ervaring mee hebben, druk op ALT F11. Vervolgens in het menu aan de linkerkant met je rechtermuistoets klikken om een Module toe te voegen.
Voor deze code alle eer naar de mensen van de volgende website waarvan ik de code heb gejat: https://www.ablebits.com/office-addins-blog/excel-regex-formulas/SPOILERPublic Function RegExpMatch(input_range As Range, pattern As String, Optional match_case As Boolean = True) As Variant
Dim arRes() As Variant 'array to store the results
Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long 'index of the current row in the source range, index of the current column in the source range, count of rows, count of columns
On Error GoTo ErrHandl
RegExpMatch = arRes
Set regex = CreateObject("VBScript.RegExp")
regex.pattern = pattern
regex.Global = True
regex.MultiLine = True
If True = match_case Then
regex.ignorecase = False
Else
regex.ignorecase = True
End If
cntInputRows = input_range.Rows.Count
cntInputCols = input_range.Columns.Count
ReDim arRes(1 To cntInputRows, 1 To cntInputCols)
For iInputCurRow = 1 To cntInputRows
For iInputCurCol = 1 To cntInputCols
arRes(iInputCurRow, iInputCurCol) = regex.Test(input_range.Cells(iInputCurRow, iInputCurCol).Value)
Next
Next
RegExpMatch = arRes
Exit Function
ErrHandl:
RegExpMatch = CVErr(xlErrValue)
End Function
Zodra je deze code in je Macro's hebt toegevoegd heb je een nieuwe Excel functie tot je beschikking. Als je de tekst in de A kolom hebt staan, moet je vervolgens de volgende formule in de B kolom plaatsen om vast te stellen of de betreffende tekst in de A kolom alleen uit normale letters bestaat.
=RegExpMatch(A1;"^[a-z]*$";ONWAAR)
uitleg in de spoiler
De uitkomst van de formule is WAAR wanneer deze alleen uit letters bestaat en ONWAAR wanneer er ook andere tekens in staan. Dus mocht er een ç in je tekst staan, dan komt er nu ONWAAR te staan. En door je B kolom op die ONWAAR te filteren krijg je dus alle regels met een raar teken.SPOILERHet eerste argument is gewoon de verwijzing naar de cel die je wilt controleren.
Het tweede argument is de reguliere expressie. Hierbij hanteer ik de volgende dingen:
^ verwijst naar het begin van de tekst en de $ verwijst naar het laatste teken in de tekst. Ik moet die 2 dingen gebruiken om te zorgen dat de hele tekst wordt beoordeeld. Als je de ^ of de $ weg laat, dan zijn namelijk substrings ook goed.
De [a-z] verwijst naar alle letters tussen a en z in de ascii tabel.
De * geeft aan 0 of meerdere matches. Als je de * weg laat kan hij dus alleen maar 1 letter beoordelen.
Door deze code te gebruiken beoordeel je dus of je tekst alleen maar uit normale letters bestaat. Zowel cijfers alsmede leestekens in de letters voldoen niet aan de voorwaarden. En dan krijg je ONWAAR als uitkomst.
Mijn derde argument met ONWAAR zorgt ervoor dat hij ongevoelig is voor hoofd- en kleine letters.
Omdat ik niet zeker weet wat voor tekens je wel wilt accepteren, geef ik hieronder nog wat opties:
=RegExpMatch(A2;"^([a-z]|\s)*$";ONWAAR) -- wanneer je letters en spaties wilt goedkeuren
=RegExpMatch(A2;"^([a-z]|\d|\s)*$";ONWAAR) -- wanneer je letters, getallen en spaties wil goedkeuren.
Heldquote:
In jouw geval Janneke141, aangezien ik weet dat je geen starter bent met Excel, zou ik je een Reguliere Expressie via VBA adviseren.
Als ik het goed lees, dan is dit echt precies wat ik zoek. Ik ben inderdaad zeker geen starter met Excel, maar wel een redelijke leek in VBA. Maar ik ga hier eens in duiken, want ik wil dit soort dingen zelf ook wel snappen.
Met voorwaardelijke opmaak kan je via Tekst met bijvoorbeeld alle cellen met een ë een kleurtje geven en met een andere regel alles met een ä, maar dat worden erg veel regels als je veel verschillende opties hebt.quote:
Ik heb een (hele) lange lijst van lettercombinaties van exact vijf letters. Er staan hoofd- en kleine letters in en dat moeten allemaal kleine letters worden, maar dat is nog niet zo moeilijk. Helaas zitten er in een klein aantal van die combinaties ook een letter met een diakritisch teken (á, ë etc.) en die moeten eruit. Helaas, vanwege het doel van die strings, is het niet zo makkelijk om alle ë's te vervangen door e's, of zoiets, dat moet handmatig. Dat vervangen zelf is nog wel te doen want het gaat om nog geen 1% van de totale lijst, maar het vinden is een crime. We willen geen lijst van 10.000 regels handmatig doorzoeken.
Nu weet ik wel een manier met DEEL en CODE enzo, maar die is behoorlijk omslachtig. Is er een snellere manier om in deze lijst alle 'foute' entries bovenaan te krijgen, of via voorwaardelijke opmaak te signaleren?
Wat mogelijk ook kan: exporteren naar een antiek formaat dat niet lekker overweg kan met letters mer accenten en kijken waar er fouten optreden
Met VBA lijkt dit ook een goeie optie: https://www.tutorialspoin(...)-characters-in-excelquote:
[..]
Ik ben inderdaad zeker geen starter met Excel, maar wel een redelijke leek in VBA. Maar ik ga hier eens in duiken, want ik wil dit soort dingen zelf ook wel snappen.
Nou, de grap is dus: die hele lijst gaat over productspecificaties uit het jaar kruik, door een of andere hobbyist bedacht maar wel altijd operationeel gebleven uit gewenning. Maar het bakelieten apparaat dat ermee overweg kon gaat eindelijk met pensioen, en de variant die het overneemt kan het nietquote:
Wat mogelijk ook kan: exporteren naar een antiek formaat dat niet lekker overweg kan met letters mer accenten en kijken waar er fouten optreden
Moeilijk verhaal, maar i.v.m. herkenbaarheid ga ik het niet beter uitleggen dan dit.Dit lijkt mij ook wel een goede, al is het maar om de lijst aan bijzondere letters te hebben. Dan zou je het zelfs zonder macro's via SUBSTITUEREN kunnen oplossen, al wordt dan wel een enge formule dan.quote:
[..]
Met VBA lijkt dit ook een goeie optie: https://www.tutorialspoin(...)-characters-in-excel
[ afbeelding ]
Mooi lijstje:
Const AccChars= "¦´¨¸¾ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüý "
Const RegChars= "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
al is die natuurlijk korter als je eerst alles naar lowercase brengt
Ik heb een data dump gemaakt van strava en nu dus een lijst met alle fietsritten per datum met de afstand erachter.
Ik heb al een kolom toegevoegd met de cumulatieve afstand per jaar. En een kolom met het betreffende jaar.
Ook heb ik een kolom gemaakt met alle maanden van het jaar.
Die maanden wil ik dan op de horizontale as. En dan per jaar een lijn omhoog hoe mijn jaarafstand ontwikkeld over de loop van het jaar. En dan dan dus meerdere lijnen omdat ik meerdere jaren met elkaar wil vergelijken.
Maar op één of andere reden krijg ik het niet voor elkaar. Iemand tips hoe ik dit aanpak?
Ik krijg het wel voor elkaar als ik een simpel tabelletje maak met 12 maanden en per maand een oplopend getal. Maar het probleem is dus dat ik verschillende punten heb per maand die niet gelijk lopen. De ene maand is het 3x fietsen en de andere maand 12x.
[ Bericht 16% gewijzigd door Stansfield op 15-02-2024 20:20:44 ]
Dit is hoe het er nu uit ziet. Rechts alle data die ik heb en in een grafiek wil zetten en het liefst ook per punt een steiging. en rechts hoe het wel lukt met een simpel voorbeeld.quote:
Draaitabel maken dat je per jaar een kolom aan data hebt, en dan een lijngrafiek daarvan.
Als ik het met een draaitabel doe dan krijg ik de stand per maand. Het is dan niet te doen om dit wat meer te laten fluctueren per punt die ik heb. Of krijg je dan een horizontale as die veel te breed is?
Met het voorlopige eindresultaat:
Nu kan ik in de linker kolom mijn afstand aanvullen per maand en wijzigt de rest.
[ Bericht 66% gewijzigd door Stansfield op 15-02-2024 21:04:39 ]
Excel wordt ook overal gebruikt
Gelukkig hebben wij wel een controle dus was het er 2 dagen later weer uit, lang voordat er iets met de cijfers gedaan zou worden.
Ook bij F1 Teamsquote:
(vanaf 3:00 wordt Excel genoemd)
Haha, dat ja!quote:
Horror Stories - EuSPRIG Horror Stories https://eusprig.org/research-info/horror-stories/quote:
staan nog meer van dat soort gevallen op.
Edit: type spreiding?
.
Dit moet dan handmatig aangepast.
Ik ga dit denk ik oplossen met R.
Als ik het zo doe en de waarden aanpas (ze hebben Aselect() in de cel staan) veranderen daarna de punten op de grafiek gezellig meequote:
Dat werk inderdaad. Dat wist ik. Maar dat bedoel ik niet. Ik heb het niet helemaal goed uitgelegd. Als je ook tekstlabels bij de punten wil. Dan beginnen de problemen. Want die kolom kan je niet meenemen in de selectie. Dan moet je aanpassingen doen.
[ afbeelding ]
Dit moet dan handmatig aangepast.
[ afbeelding ]
Ik ga dit denk ik oplossen met R.
En met labelopmaak als volgt kan ik de namen aanpassen in kolom A en gaat de grafiek ook mee
Is dit wat je bedoelde?
Nee. Plaatje 1 plaatst de punten zogezegd. Dan rechtsklik je op 1 punt en met de informatie uit plaatje 2 kan je het zo instellen dat hij de namen van alle punten opzoekt in een kolom. Pas je de naam in de cel aan, dan verandert de grafiek mee.quote:
Jij moet dus ook voor elk punt de naam en x&y 'bij elkaar klikken' toch?
Edit: ik duik er toch even opnieuw in want de plaatsing van de punten lijkt niet te kloppen
Edit 2: gevonden. De XY-positionering doet het wel goed als ik alleen de cellen meeneem waar ook echt wat zinnigs in staat en niet de hele kolom pak. Dat maakt voor het labelen niet uit, maar toch fijn dat het nu klopt
[ Bericht 11% gewijzigd door H_T op 29-03-2024 15:54:56 ]
Jij wil dus 'Waarde uit cellen', 'X-waarde' en 'Y-waarde' selecteren.
[ Bericht 25% gewijzigd door Tegan op 29-03-2024 16:53:58 ]
Dus wat ik eigenlijk wil is een:quote:
[..]
Als ik het zo doe en de waarden aanpas (ze hebben Aselect() in de cel staan) veranderen daarna de punten op de grafiek gezellig mee
[ afbeelding ]
En met labelopmaak als volgt kan ik de namen aanpassen in kolom A en gaat de grafiek ook mee
[ afbeelding ]
Is dit wat je bedoelde?
- bereik selecteren
- een spreidingsgrafiek selecteren
- en dan in zo min mogelijk stappen labels bij de coördinaten weergeven
Nu moet ik voor elke coördinaat een aantal stappen doen om dit voor elkaar te krijgen.
@H_T met welke versie van Excel werk jij? Wellicht maakt dat een verschil.
Stap 2:
Stap 3:
Stap 4:
Ook 'X-waarde' en 'Y-waarde' selecteren.
Dit is super! Wist ik niet. Veel sneller. Dank.quote:
Stap 1:
[ afbeelding ]
[ afbeelding ]
Stap 2:
[ afbeelding ]
Stap 3:
[ afbeelding ]
Stap 4:
Ook 'X-waarde' en 'Y-waarde' selecteren.
Ik wil in cel A3 het verschil van A1-A2; maar als in cel A2 leegis wil ik dat in cel A3 ook leeg is
=ALS(A2="";"";A1-A2)quote:
Snelle vraag:
Ik wil in cel A3 het verschil van A1-A2; maar als in cel A2 leegis wil ik dat in cel A3 ook leeg is
@onlogisch
thx maar dat is 'm nietquote:
Ik wil dus in kolom d de formule naar beneden doortrekken maar niet dat er nullen komen
de kolom moet alleen ingevuld worden als ik in kolom B een aantal ingeef
dus enkel de rij waar ingave is mag het resultaat tonen
Jawel, zat weer in de verkeerde rij.quote:
bedankt voor de hulp, opgelost