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
[ Bericht 2% gewijzigd door ChevyCaprice op 14-05-2020 19:30:20 ]
Die sheets wil ik importeren in een applicatie en die kan maar 1000 regels per keer inlezen.quote:Op zondag 30 juni 2019 14:10 schreef The_vice het volgende:
[..]
Vraagje, wat is het doel hiervan?
Normaal zou ik alle gegevens (die over hetzelfde onderwerp gaan) juist op één sheet willen hebben.
Maar zoals Janneke141 zegt met een macro.
Ik zou hier eens mee beginnen:
[ code verwijderd ]
Dank je voor de macro, maar hoe gebruik ik dit? Ik heb nog nooit met macro gewerkt.
Je moet eerst de macro's accepteren.quote:
[..]
Die sheets wil ik importeren in een applicatie en die kan maar 1000 regels per keer inlezen.
Dank je voor de macro, maar hoe gebruik ik dit? Ik heb nog nooit met macro gewerkt.
Zie de topic starpost hierboven. Daar mee beginnen, en je hier weer melden als je vast looptquote:
[..]
Die sheets wil ik importeren in een applicatie en die kan maar 1000 regels per keer inlezen.
Dank je voor de macro, maar hoe gebruik ik dit? Ik heb nog nooit met macro gewerkt.
. Ook moet je in de code nog wat handmatig aanpassen (aantal sheets en regel nummers)=SOMPRODUCT(A1:A3;B1:B3)
De waarden A1:A3 --> g - 0 - 1
De waarden B1:B3 --> 1 - 2 - 3
Waarom geeft de somproduct-formule (bij mij) een oplossing en geen '#WAARDE!' ?
Omdat hij gewoon vermenigvuldigt en optelt wat een getal is, en de rest negeert.quote:Op woensdag 3 juli 2019 10:01 schreef Z het volgende:
Ik heb een simpele somproduct-formule:
=SOMPRODUCT(A1:A3;B1:B3)
De waarden A1:A3 --> g - 0 - 1
De waarden B1:B3 --> 1 - 2 - 3
Waarom geeft de somproduct-formule (bij mij) een oplossing en geen '#WAARDE!' ?
de =SOM van de cellen 1,2,3,4,Q is ook gewoon 10.
Thnx. Wiskundig lijkt me het niet helemaal juist. Excel gaat er kennelijk iets anders mee om.quote:
[..]
Omdat hij gewoon vermenigvuldigt en optelt wat een getal is, en de rest negeert.
de =SOM van de cellen 1,2,3,4,Q is ook gewoon 10.
Nou ja, het was vroeger anders. In eerdere versies was 1+2+3+4+Q wel degelijk #WAARDE?, maar voor de gemiddelde gebruiker die gewoon niet zo heel erg veel van Excel óf van wiskunde snapt is '10' een wat prettiger uitkomst dan '#WAARDE?'.quote:
[..]
Thnx. Wiskundig lijkt me het niet helemaal juist. Excel gaat er kennelijk iets anders mee om.
Het is vooral lastig wanneer je zo'n optelling maakt en er wordt dan iets niet meegeteld omdat een of andere digibeet '7.40' in een vakje heeft gepropt in plaats van '7,40'. Als je werkt met gegevens van anderen moet je daar eigenlijk wel een check op zetten.
Nu moet ik dat elke keer handmatig aanpassen, is ergens in te stellen dat Excel default (alsin vooraf bepaalde) kleuren gebruikt?
Ja het is er wel, maar zelf vind ik het niet geweldig werken.quote:
Ik maak nogal wat grafieken (verschillende soorten) met als gemeenschappelijke eigenschap dat bepaalde kleuren gebruikt worden voor bepaalde termen (bijvoorbeeld critical: rood, major: amber, etc.)
Nu moet ik dat elke keer handmatig aanpassen, is ergens in te stellen dat Excel default (alsin vooraf bepaalde) kleuren gebruikt?
Wanneer je je eerste grafiek hebt opgemaakt op de wijze waarop je wilt, kies je in de Ribbon "Ontwerpen" voor het tweede icoontje "Opslaan als sjabloon". De ribbon zie je alleen wanneer je je grafiek hebt aangeklikt.
Plaats je sjabloon in de standaard map, die wordt aanbevolen en ga niet zelf een andere map zoeken.
Bij het maken van je nieuwe grafiek kan je via de bovenste optie Sjabloon je eigen sjabloon terugvinden. Het werkt vooral op de volgorde van de kolommen voor de kleuren van je grafieken.
---
Wat ook werkt is om je eerste grafiek te selecteren en te kopieren. Vervolgens je tweede grafiek aanklikken en via het menu van plakken speciaal de optie opmaak te kiezen.
Je zal het vrij snel in VBA macro's moeten zoeken.quote:
Ik maak nogal wat grafieken (verschillende soorten) met als gemeenschappelijke eigenschap dat bepaalde kleuren gebruikt worden voor bepaalde termen (bijvoorbeeld critical: rood, major: amber, etc.)
Nu moet ik dat elke keer handmatig aanpassen, is ergens in te stellen dat Excel default (alsin vooraf bepaalde) kleuren gebruikt?
Ik laat bijvoorbeeld naar de serie naam kijken, en dan op basis daarvan een kleur instellen.
Hier wat voorbeelden van Peltier Tech, "Format According to Performance"
Vraagje met betrekking tot een vlookup (of iemand moet een ander briljant idee hebben.1) Ik heb een bestand met 6 tabs, 1 'hoofdtab'' waar de informatie in moet komen en de andere 5 tabs zijn content tabs. Waar de informatie uit komt. In alle tabs staat in principe dezelfde informatie, alleen met andere 'relatienummers'. Het doel is om in mijn hoofdtab de relatienummers van alle tabs terug te krijgen, per kolom. Maar als 1 kolom werkt, dan krijg ik de rest ook prima werkend.
2) In iedere tab heb ik uit samengevoegde cellen een unieke key gemaakt, die zou dus in iedere tab exact hetzelfde moeten zijn voor de specifieke klant. Op basis daarvan doe ik in tab 1 (hoofdtab) , waar ik dan de informatie van een relatie terug moet krijgen.
---
In de hoofdtab heb ik daarom een vlookup gemaakt die als volgt is:
| 1 | =VERT.ZOEKEN(F2;TIL!$A$2:$P$115524;3;ONWAAR) |
Nu krijg ik voor sommige informatie wel een nummer terug, die ook klopt, maar voor sommige ook niet. Terwijl als ik zelf een control-f'je doe op de specifieke tab, ik het dan wel vindt. Iemand enig idee wat hiervan het probleem is, of is er misschien een betere oplossing?
Gracias!
Gebruik je heel misschien een Office-versie van voor 2007?quote:
Dag vrienden!
1) Ik heb een bestand met 6 tabs, 1 'hoofdtab'' waar de informatie in moet komen en de andere 5 tabs zijn content tabs. Waar de informatie uit komt. In alle tabs staat in principe dezelfde informatie, alleen met andere 'relatienummers'. Het doel is om in mijn hoofdtab de relatienummers van alle tabs terug te krijgen, per kolom. Maar als 1 kolom werkt, dan krijg ik de rest ook prima werkend.
2) In iedere tab heb ik uit samengevoegde cellen een unieke key gemaakt, die zou dus in iedere tab exact hetzelfde moeten zijn voor de specifieke klant. Op basis daarvan doe ik in tab 1 (hoofdtab) , waar ik dan de informatie van een relatie terug moet krijgen.
---
In de hoofdtab heb ik daarom een vlookup gemaakt die als volgt is:
[ code verwijderd ]
Nu krijg ik voor sommige informatie wel een nummer terug, die ook klopt, maar voor sommige ook niet. Terwijl als ik zelf een control-f'je doe op de specifieke tab, ik het dan wel vindt. Iemand enig idee wat hiervan het probleem is, of is er misschien een betere oplossing?
Gracias!
En hoe zijn je sheets verder opgebouwd? Kan je wellicht wat dummy data uploaden?
Het is inderdaad office 2006. Is dat een ramp?quote:
[..]
Gebruik je heel misschien een Office-versie van voor 2007?
En hoe zijn je sheets verder opgebouwd? Kan je wellicht wat dummy data uploaden?
Dummy data kan, maar kost wat tijd.
Wat je nu hebt in je vijf tabs zijn overzichten (presentaties) waar je in je hoofdtab "data" van wilt maken.quote:
....
1) Ik heb een bestand met 6 tabs, 1 'hoofdtab'' waar de informatie in moet komen en de andere 5 tabs zijn content tabs. Waar de informatie uit komt. In alle tabs staat in principe dezelfde informatie, alleen met andere 'relatienummers'. Het doel is om in mijn hoofdtab de relatienummers van alle tabs terug te krijgen, per kolom. Maar als 1 kolom werkt, dan krijg ik de rest ook prima werkend
....
Als alle 6 de tabs gelijk zijn, dan is het beter om het allemaal samen te voegen naar één tabblad, met een kolom "klant" ervoor voor de verschillende klanten.
Dan wordt je hoofdtab de "data", en en kan je van daaruit per klant de overzichten maken.
Meestal kan je dan je overzichten (presentaties) via een pivot table/draai tabel in elkaar klussen. Dat scheelt een koop gedoe met zoek, match en indexeer functies.
En als je per klant een overzicht wilt (of eigen tab) dan maak je een pivot table/draaitabel met als filter "klant"
[ Bericht 4% gewijzigd door The_vice op 17-08-2019 12:14:44 ]
Dat is een kleine ramp, want pre-2007 kan maar 65k rijen aan, jij zoekt nu in 115kquote:
[..]
Het is inderdaad office 2006. Is dat een ramp?
Dummy data kan, maar kost wat tijd.
https://docs.microsoft.co(...)office-2010/ff700514(v=office.14)#excel-2007-performance-improvements
Is er nog een manier om dit te omzeilen? Er is geen mogelijkheid tot het schrappen van regels in ieder geval.quote:
[..]
Dat is een kleine ramp, want pre-2007 kan maar 65k rijen aan, jij zoekt nu in 115k
https://docs.microsoft.co(...)office-2010/ff700514(v=office.14)#excel-2007-performance-improvements
Ik zat zelf te denken aan een IF en dan daarin 2 vlooksup verwerkt.
Upgraden naar een nieuwere versiequote:
[..]
Is er nog een manier om dit te omzeilen? Er is geen mogelijkheid tot het schrappen van regels in ieder geval.
Ik zat zelf te denken aan een IF en dan daarin 2 vlooksup verwerkt.
Als je zeker weet dat er een match is zou je idd ze kunnen combineren
Zal het aan de baas doorgevenquote:
[..]
Upgraden naar een nieuwere versie
Als je zeker weet dat er een match is zou je idd ze kunnen combineren
Altijd lastig als een hyperlink niet "werkt" omdat ie afkapt.quote:
[..]
Dat is een kleine ramp, want pre-2007 kan maar 65k rijen aan, jij zoekt nu in 115k
https://docs.microsoft.co(...)office-2010/ff700514(v=office.14)#excel-2007-performance-improvements
de link is:
https://docs.microsoft.com/en-us/previous-versions/
office/developer/office-2010/ff700514(v=office.14)#excel-2007-performance-improvements
(zelf maar even aan elkaar koppelen...)
Of zo: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ff700514(v=office.14)#excel-2007-performance-improvementsquote:
[..]
Altijd lastig als een hyperlink niet "werkt" omdat ie afkapt.
de link is:
https://docs.microsoft.com/en-us/previous-versions/
office/developer/office-2010/ff700514(v=office.14)#excel-2007-performance-improvements
(zelf maar even aan elkaar koppelen...)
Er is een oplossing, maar ik zal gelijk toegeven dat deze extreem ingewikkeld is en ik me afvraag of je het werkend gaat krijgen voor je eigen bestand.quote:
Dag vrienden!
*KNIP*
Nu krijg ik voor sommige informatie wel een nummer terug, die ook klopt, maar voor sommige ook niet. Terwijl als ik zelf een control-f'je doe op de specifieke tab, ik het dan wel vindt. Iemand enig idee wat hiervan het probleem is, of is er misschien een betere oplossing?
Gracias!
Het probleem wat je beschrijft is namelijk eigenlijk veel meer een database type probleem. De oplossing die ik ga uitleggen zit dan ook met het gebruik van een Query en met een Macro. Ik ben hier zelf eigenlijk geen voorstander van, omdat ik denk dat je het zelf niet kunt onderhouden.
Voor ik heel veel tijd in de uitleg ga steken, hierbij een voorbeeldbestand van wat er mogelijk is. In dit bestand zie je 3 tabbladen. De inhoud van Blad2 en de inhoud van Blad3 worden samengevat weergegeven op Blad1. Dit is gedaan alleen voor de zoekwaarde die je in cel B1 invult. Als je de zoekwaarde wijzigt (bijvoorbeeld tussen 1 en 11) dan wijzigt de tabel met de weergegeven data op Blad1 mee.
Ook kan je natuurlijk de data van blad2 en blad3 wijzigen om te zien dat wanneer je bij die 3 kolommen blijft je ook echt de gegevens dynamisch mee krijgt.
Is dit de moeite waard om meer specifiek op jouw situatie te maken?
Let wel op dat het je zelf veel tijd gaat kosten om het werkend voor jouw bestand te gaan maken ofwel dat je het bestand moet delen zodat wij dat voor je kunnen doen (maar dat je dan wel vast blijft zitten aan de wijze waarop je nu werkt).
voorbeeldbestand
Thanks! Fijn dat je meedenkt. Ik denk niet dat dit de moeite waard is, het is niet de bedoeling/de wens om het bestand continu te gaan onderhouden. Voor een interne software wijziging is het nodig om alle klantnummers even aan elkaar te koppelen. De oude software is gewoon een puinzooi, waardoor we dit dus krijgen.quote:
[..]
Er is een oplossing, maar ik zal gelijk toegeven dat deze extreem ingewikkeld is en ik me afvraag of je het werkend gaat krijgen voor je eigen bestand.
Het probleem wat je beschrijft is namelijk eigenlijk veel meer een database type probleem. De oplossing die ik ga uitleggen zit dan ook met het gebruik van een Query en met een Macro. Ik ben hier zelf eigenlijk geen voorstander van, omdat ik denk dat je het zelf niet kunt onderhouden.
Voor ik heel veel tijd in de uitleg ga steken, hierbij een voorbeeldbestand van wat er mogelijk is. In dit bestand zie je 3 tabbladen. De inhoud van Blad2 en de inhoud van Blad3 worden samengevat weergegeven op Blad1. Dit is gedaan alleen voor de zoekwaarde die je in cel B1 invult. Als je de zoekwaarde wijzigt (bijvoorbeeld tussen 1 en 11) dan wijzigt de tabel met de weergegeven data op Blad1 mee.
Ook kan je natuurlijk de data van blad2 en blad3 wijzigen om te zien dat wanneer je bij die 3 kolommen blijft je ook echt de gegevens dynamisch mee krijgt.
Is dit de moeite waard om meer specifiek op jouw situatie te maken?
Let wel op dat het je zelf veel tijd gaat kosten om het werkend voor jouw bestand te gaan maken ofwel dat je het bestand moet delen zodat wij dat voor je kunnen doen (maar dat je dan wel vast blijft zitten aan de wijze waarop je nu werkt).
voorbeeldbestand
Net even geprobeerd of het met een INDEX & VERGELIJKEN werkend te krijgen is, maar neen. Hij kapt de boel vaak automatisch af en als het dan handmatig wordt aangepast krijg je een error.
Bijvoorbaald in de cel:
Amsterdam;#Rotterdam;#Maastricht;#Bangkok
=VIND.SPEC vindt bepaalde tekens in een tekenreeks. Daarmee kun je telkens afkappen via =LINKS() of DEEL().quote:
Ik heb een cel die verschillende (tekstuele) waardes heeft, niet allemaal gelijke lengte, met altijd een ';#' ertussen. Hoe kan ik daar een lijstje van maken die te gebruiken is in een picot-table?
Bijvoorbaald in de cel:
Amsterdam;#Rotterdam;#Maastricht;#Bangkok
Ik kopieer willekeurige content (paar rijen/kolommen) van een Excel sheet dat ik gebruik. Plak dit in een nieuwe mail in Outlook, paste met source formatting. Ziet er in eerste instantie goed uit.
Ik verstuur het, en bij de ontvanger komen een aantal van die cellen vervolgens leeg binnen.
Bekijk ik die mail vanuit Sent Items, dan zijn die gegevens daar ook verdwenen.
Verder nog iets raars gezien: een cel die begint met "21-7: random text", kopiëren naar een nieuwe mail, en daar maakt ie van ":7-21 random text"
Dus een deel van de celinhoud wordt omgedraaid. De originele cel is van type General. Nog nooit gezien, en echt geen idee wat hier aan de hand kan zijn.
Dat laatste zal wel te maken hebben met datumherkenning en het omzetten in de Amerikaanse notatie. Verder geen idee.quote:
Heb een raar probleem, wellicht niet helemaal specifiek Excel specifiek, maar de oorzaak is niet duidelijk nog.
Ik kopieer willekeurige content (paar rijen/kolommen) van een Excel sheet dat ik gebruik. Plak dit in een nieuwe mail in Outlook, paste met source formatting. Ziet er in eerste instantie goed uit.
Ik verstuur het, en bij de ontvanger komen een aantal van die cellen vervolgens leeg binnen.
Bekijk ik die mail vanuit Sent Items, dan zijn die gegevens daar ook verdwenen.
Verder nog iets raars gezien: een cel die begint met "21-7: random text", kopiëren naar een nieuwe mail, en daar maakt ie van ":7-21 random text"
Nog nooit gezien, en echt geen idee wat hier aan de hand kan zijn.
"random text (random lang nummer)"
dat resulteerde na plakken in
"random text (random(langnummer"
Zeg het maar.
Ik ben met taalinstellingen (toetsenbord, staat standaard op ENG, ook tijdens de voorbeelden) bezig geweest, kan nog niet helemaal uitsluiten dat dat er iets mee te maken heeft.
Nee die redenering moet andersom. Alleen wanneer je celeigenschap op Tekst staat zou Excel het letterlijk als een tekst moeten interpreteren. Alle andere varianten is slechts een weergave van wat Excel onderwater doet.quote:
Dat zou je ergens denken maar dan zou de originele cel toch echt een datumveld moeten zijn, en dat is het niet. En hij doet iets vergelijkbaars met een cel waarin staat
"random text (random lang nummer)"
dat resulteerde na plakken in
"random text (random(langnummer"
Zeg het maar.
Ik ben met taalinstellingen (toetsenbord, staat standaard op ENG, ook tijdens de voorbeelden) bezig geweest, kan nog niet helemaal uitsluiten dat dat er iets mee te maken heeft.
Het andere probleem rondom de weergave kan vermoedelijk liggen aan een verschil in de geaccepteerde opmaak-stijl in het mail programma. Eventuele instellingen rondom RTF/MIME/HTML opmaak en zelfs dat de ontvanger de tekst bijv in een ander lettertype laat weergeven als standaard instelling kan voor heel veel problemen zorgen.
Als ik hetzelfde stuk in Word plak in een verder totaal standaard leeg document, heb ik het issue nog niet kunnen reproduceren.
Ik heb een jaar aan datums naast elkaar staan. Bij iedere datum wil ik een waarde terug krijgen uit een tabel. Vervolgens wil ik mijn code slepen maar dan krijg ik overal hetzelfde resultaat terug, namelijk die van 9-1-2017.
Hoe kan ik zorgen dat er bij de tekst tussen quotjes bij het slepen telkens +1 wordt gedaan?
| 1 | =COUNTIF(Sheet1!$F2:$F37931;"9-1-2017") |
quote:Op donderdag 19 september 2019 13:32 schreef onlogisch het volgende:
Hoi,
Ik heb een jaar aan datums naast elkaar staan. Bij iedere datum wil ik een waarde terug krijgen uit een tabel. Vervolgens wil ik mijn code slepen maar dan krijg ik overal hetzelfde resultaat terug, namelijk die van 9-1-2017.
Hoe kan ik zorgen dat er bij de tekst tussen quotjes bij het slepen telkens +1 wordt gedaan?
[ code verwijderd ]
“9-1-2017” vervangen door een verwijzing naar de cel met die datum in je rij met datums?quote:
Hoi,
Ik heb een jaar aan datums naast elkaar staan. Bij iedere datum wil ik een waarde terug krijgen uit een tabel. Vervolgens wil ik mijn code slepen maar dan krijg ik overal hetzelfde resultaat terug, namelijk die van 9-1-2017.
Hoe kan ik zorgen dat er bij de tekst tussen quotjes bij het slepen telkens +1 wordt gedaan?
[ code verwijderd ]
Ligt er een beetje aan hoe je de data gestructureerd hebt
Ja, en dat lukte me niet, tot nuquote:
[..]
[..]
“9-1-2017” vervangen door een verwijzing naar de cel met die datum in je rij met datums?
Ligt er een beetje aan hoe je de data gestructureerd hebt
iets met ""&f1& ""Voor de mensen die kennis hebben van excel
Edit:quote:
[..]
Ja, en dat lukte me niet, tot nu
Las niet goed. Mooi dat het gelukt is.
[ Bericht 4% gewijzigd door #ANONIEM op 19-09-2019 13:54:03 ]
Ik heb weer een excel vraag :
Ik heb een waarde in g2 t/m g21. Deze waarde wil ik maal a2 t/m a21 doen (g2 x a2, g3 x a3 etc.) de uitkomst van al die waardes wil ik bij elkaar optellen en die wil ik weer projecteren in g22.
Hoe kan ik dat het beste realiseren?
Geen mention gehad..quote:
Mannen broeders :
Ik heb weer een excel vraag :
Ik heb een waarde in g2 t/m g21. Deze waarde wil ik maal a2 t/m a21 doen (g2 x a2, g3 x a3 etc.) de uitkomst van al die waardes wil ik bij elkaar optellen en die wil ik weer projecteren in g22.
Hoe kan ik dat het beste realiseren?
Zo werkt het wel lijkt het, maar is dat niet te vereenvoudigen?
Knal er gewoon een kolom naast en doe vervolgens de som daaropquote:
=G3*a3+G4*a4
Zo werkt het wel lijkt het, maar is dat niet te vereenvoudigen?
@onlogischquote:
Dit zocht je toch?
Dit lijkt me nog wel het beste inderdaad, een derde kolom met "=A2*G2" en dan onderaan die kolom =SUM(hele kolom met vermenigvuldigde getallen)quote:
[..]
Knal er gewoon een kolom naast en doe vervolgens de som daarop
1,"01-01-2019","XXXXXXXX","18200","-2.34","","","1121212152"
De waarden tussen de komma's wil ik apart in elk kolom scheiden.
Dit lukt via gegevens > 'tekst naar kolommen' en 'uit tekst/CSV', maar het getal met twee cijfers achter de decimalen wordt niet goed weergegeven.
Ik krijg dan ofwel '=2.34 of '2' afhankelijk met welke functie ik de gegevens importeer.
Bij eentje wordt een =-teken toegevoegd en het minteken verdwijnt en bij het ander wordt er afgerond.
Ik dacht dat het aan de taalinstellingen lag, maar dat is het ook niet. Herkent iemand dit probleem?
1,"01-01-2019","XXXXXXXX","18200","-2.34","","","1121212152"quote:
Ik heb een conversie-probleem met CSV-bestand in Excel.
1,"01-01-2019","XXXXXXXX","18200","-2.34","","","1121212152"
De waarden tussen de komma's wil ik apart in elk kolom scheiden.
Dit lukt via gegevens > 'tekst naar kolommen' en 'uit tekst/CSV', maar het getal met twee cijfers achter de decimalen wordt niet goed weergegeven.
Ik krijg dan ofwel '=2.34 of '2' afhankelijk met welke functie ik de gegevens importeer.
Bij eentje wordt een =-teken toegevoegd en het minteken verdwijnt en bij het ander wordt er afgerond.
Ik dacht dat het aan de taalinstellingen lag, maar dat is het ook niet. Herkent iemand dit probleem?
Zet in Sheet1 in cel A1:
1,"01-01-2019","XXXXXXXX","18200","-2.34","","","1121212152"
Zet in Sheet2
In Cel a1:
=FIND(",";Sheet1!A1)
In Cel b1:
=FIND(",";Sheet1!A1;A1+1)
In Cel c1:
=FIND(",";Sheet1!A1;B1+1)
In Cel d1:
=FIND(",";Sheet1!A1;C1+1)
In Cel e1:
=FIND(",";Sheet1!A1;D1+1)
In Cel f1:
=FIND(",";Sheet1!A1;E1+1)
In Cel g1:
=FIND(",";Sheet1!A1;F1+1)
In Cel h1:
=LEN(Sheet1!A1)
Zet in Sheet 3:
In Cel a1:
=LEFT(Sheet1!A1;Sheet2!A1)*1
In Cel b1:
=MID(Sheet1!A1;Sheet2!A1+2;Sheet2!B1-Sheet2!A1-3)*1
Selecteer kolom B en ga via "Format Cells" naar "Date" en kies je favoriete datumnotatie
In Cel c1:
=MID(Sheet1!A1;Sheet2!B1+2;Sheet2!C1-Sheet2!B1-3)
In Cel d1:
=MID(Sheet1!A1;Sheet2!C1+2;Sheet2!D1-Sheet2!C1-3)*1
Via "*1" wordt het een getal ipv tekst. Ik neem aan dat dat de bedoeling is.
In Cel e1:
=SUBSTITUTE(+MID(Sheet1!A1;Sheet2!D1+2;Sheet2!E1-Sheet2!D1-3);".";",")*1
Via "Substitute" wordt de punt vervangen door een komma, en via "*1" wordt het een getal ipv tekst
Als er ook nog sprake is van komma's bij
In Cel f1:
=MID(Sheet1!A1;Sheet2!E1+2;Sheet2!F1-Sheet2!E1-3)
In Cel g1:
=MID(Sheet1!A1;Sheet2!F1+2;Sheet2!G1-Sheet2!F1-3)
In Cel h1:
=MID(Sheet1!A1;Sheet2!G1+2;Sheet2!H1-Sheet2!G1-2)*1
Via "*1" wordt het een getal ipv tekst. Ik neem aan dat dat de bedoeling is.
Is dit, buiten dat de code niet is geoptimaliseerd, de meeste efficiënte manier?
De code werkt, maar ik ben wat aan het klooien met Excel en probeer te leren om handelingen te automatiseren en te optimaliseren. Heb verder nog zeer weinig kennis maar moet ergens beginnen natuurlijk.
Inderdaad, macro's opnemen zijn een prima startpunt. De code is niet optimaal, maar je kan er wel van leren (of lui achterkomen) hoe bepaalde zaken werken, zoals een cell een kleur geven, een tekst of formule toe te passen, een workbook op te slaan, etc.quote:Op woensdag 6 november 2019 23:11 schreef Harveo het volgende:
In mijn Excel bestand heb ik in een kolom verschillende waardes staan als zijnde platte tekst. Deze waardes heb ik een kleur gegeven met behulp van voorwaardelijk opmaak en vervolgens dit proces opgenomen om het als macro te laten draaien.
Is dit, buiten dat de code niet is geoptimaliseerd, de meeste efficiënte manier?
De code werkt, maar ik ben wat aan het klooien met Excel en probeer te leren om handelingen te automatiseren en te optimaliseren. Heb verder nog zeer weinig kennis maar moet ergens beginnen natuurlijk.
De code die eruitkomt is zeker niet optimaal, en al helemaal niet flexibel
optimaal:
als je in een opgenomen macro 5 keer iets typt dan zal het 5 keer verschijnen in een macro:
A1 doe dit
A2 doe dit
A3 doe dit etc..
optimaler is het om dat 5 keer met dezelfde code te herhalen, middels een for to loop:
for i = 0 to 4
cells(1,i) doe dit (numeriek naar cellen verwijzen in plaats van A1 etc)
next i
flexibel
flexibeler wordt het als je bijv je macro je sheet laat opslaan met bijv de datum van vandaag via een macro. Dan doe je dingen als
| 1 | ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Format(Date, ddmmyyyy) & ".xlsm" |
Weet niet precies waar je nu precies opzoek naar bent, maar met (excel) programmeren is het goed als je een project/workbook hebt waar je mee wilt automatiseren, want dat is een goede motivatie om dingen te leren.
Verder is een goed boek met VBA voor Excel absoluut niet verkeerd, daar staan veel dingen clip en klaar in, al zal je er mee moeten pielen om het in jou ideeen toe te passen, maar je leert er ook van door het na te bouwen.
En zoeken via google "excel VBA" + wat je wilt doen.
sites als mrexcel, ron de bruin, en zeker stackoverflow zijn goed om uit de google resultaten te pakken.
Later is het goed om functions te leren, arrays toe te passen, leren om classes te maken etc. zeker als je door grotere hoeveelheden data gaat ploegen.
en als je er niet meer uitkomt zorgen dat je het ergens kan vragen, bijvoorbeeld hier.
[ Bericht 100% gewijzigd door The_vice op 06-11-2019 23:58:38 ]
Op dit moment update ik wekelijks handmatig een lijst met data. Dit zijn simpele handelingen zoals kolommen verwijderen die ik niet nodig heb, filteren en kleuren toepassen om het overzichtelijk te maken. Via Vlookup haal ik opmerkingen/notities uit de lijst van de week er voor.
Uiteindelijk wil ik de "verse" data in het huidige bestand laden en de lijst updaten met één druk op de knop. Als dit goed werkt wil ik om het half uur de lijst automatisch bij laten werken zodat ik altijd actuele informatie heb.
Met behulp van het www heb ik de volgende code gemaakt.
Zoals jullie kunnen zien vul je een waarde in cel E3 en G3 en afhankelijk of deze kleiner/groter of gelijk is veranderd deze in de actieve cel naar 0,1 of 3. (even oefenen met voetbal uitslagen😉)quote:=ALS(E3>G3;"3";ALS(E3<G3;"0";ALS(E3=G3;"1")))
Nu heb ik elders een formule gevonden die anders is opgebouwd maar wel hetzelfde resultaat geeft.
Ik probeer te begrijpen waarom dit zo is. Ik mis bij deze bijvoorbeeld het < teken. Wat bepaalt bijvoorbeeld de uitkomst als J6 kleiner is dan L6?quote:=ALS(J6="";"";ALS(J6>L6;3;ALS(J6=L6;1;0)))
Je ziet een '>' en een '=' in die formule. Als niet 'J6>L6;3' en als niet 'J6=L6' dan 0.
Hoewel de post boven mij technisch klopt, weet ik niet of je het snapt wanneer je het leest. Daarom nu met iets meer tekst:quote:
Ik probeer te begrijpen waarom dit zo is. Ik mis bij deze bijvoorbeeld het < teken. Wat bepaalt bijvoorbeeld de uitkomst als J6 kleiner is dan L6?
De formule als werkt als volgt: ALS(CONDITIE;"Wat indien conditie waar is";"Wat indien de conditie NIET waar is") in het kort ALS(CONDITIE;WAAR;ONWAAR)
We delen de probleemstelling op:
=ALS(J6="";"";ALS(J6>L6;3;ALS(J6=L6;1;0)))
oftewel:
=ALS(J6=""; wordt "" bij WAAR; iets moeilijks bij ONWAAR) -> met andere woorden dit doet niets anders dan dat het resultaat "" is wanneer J6 leeg is.
Wanneer J6 ongelijk is aan "" gaan we het tweede gedeelte van de formule in. Dat tweede gedeelte is:
ALS(J6>L6;3;ALS(J6=L6;1;0))
Als J6 groter is dan L6, dan is het resultaat 3.
Als J6 niet groter is dan L6 krijg je het volgende deel van de formule
Als J6 dus ongelijk is aan "", maar ook niet groter is dan L6, krijgen we de volgende formule:
ALS(J6=L6;1;0)
Als J6 gelijk is aan L6 is het resultaat 1
Als J6 niet gelijk is aan L6 is het resultaat 0.
Wanneer je dit in normale taal uitschrijft krijg je:
Indien J6 is "", dan antwoord ""
Indien J6>L6; dan antwoord 3
Indien J6=L6, dan antwoord 1
In alle andere gevallen (wat niet anders kan zijn dan J6<L6), dan antwoord 0
Mee eensquote:
[..]
Hoewel de post boven mij technisch klopt, weet ik niet of je het snapt wanneer je het leest.
| 1 2 | myFormula = "=VERT.ZOEKEN(H" & i + 1 & ";[varvallab.xlsx]Blad1!A$1:B$20000;2;ONWAAR)" ActiveCell.Value = myFormula |
Het lukt me bijvoorbeeld wel om =A1 & " " & B1 in een cel te plakken. Maar bovenstaande niet. Als ik de '=' weghaal, gaat het wel goed overigens. Kan het zijn dat de VERT.ZOEKEN een #N/B geeft en daarom de macro niet verder wil?
vba gebruikt de engelse notatiequote:
Onderstaande geeft een foutmelding (VBA).
[ code verwijderd ]
Het lukt me bijvoorbeeld wel om =A1 & " " & B1 in een cel te plakken. Maar bovenstaande niet. Als ik de '=' weghaal, gaat het wel goed overigens. Kan het zijn dat de VERT.ZOEKEN een #N/B geeft en daarom de macro niet verder wil?
myFormula = "=VLOOKUP(H" & i + 1 & ",[varvallab.xlsx]Blad1!A$1:B$20000,2,FALSE)"
Ook als je het als string in een cel plakt?quote:
[..]
vba gebruikt de engelse notatie
myFormula = "=VLOOKUP(H" & i + 1 & ",[varvallab.xlsx]Blad1!A$1:B$20000,2,FALSE)"
Ik ben zelf ook wat verder. De fout komt waarschijnlijk toch doordat lookup niks vindt.
Dat een lookup niets vindt, leidt tot de waarde #N/B in de betreffende cel. Dit leidt niet tot een fout in de vba verwerking.quote:
[..]
Ook als je het als string in een cel plakt?
Ik ben zelf ook wat verder. De fout komt waarschijnlijk toch doordat lookup niks vindt.
VBA hanteert altijd de Amerikaanse notatie zowel voor de , als scheidingsteken alsmede de amerikaanse formulenaam.
De enige wijze om daar onder uit te komen is door zelf een functie te schrijven die de nederlandse naam heeft.
Overigens kan je ook gewoon even je marco opname tool gebruiken. Zelf de formule in de cel typen en daarna de opname beeindigen. Dan heb je altijd de juiste notatie. Daarna kan je je bewerking doen met de i +1 enzo.
Opgelost (dit werkt):quote:
[..]
Dat een lookup niets vindt, leidt tot de waarde #N/B in de betreffende cel. Dit leidt niet tot een fout in de vba verwerking.
VBA hanteert altijd de Amerikaanse notatie zowel voor de , als scheidingsteken alsmede de amerikaanse formulenaam.
De enige wijze om daar onder uit te komen is door zelf een functie te schrijven die de nederlandse naam heeft.
Overigens kan je ook gewoon even je marco opname tool gebruiken. Zelf de formule in de cel typen en daarna de opname beeindigen. Dan heb je altijd de juiste notatie. Daarna kan je je bewerking doen met de i +1 enzo.
myFormula = "=VLOOKUP(A" & i + 1 & ",[varvallab_2.xlsm]Blad1!A$1:B$17983,2,FALSE)"
Ik weet dat macros met engelse termen werken. Maar omdat ik nu in wezen een string paste in een cel, snap ik nog steeds niet helemaal niet dat de macro dit niet wil doen.
Excel heeft in werkelijkheid de amerikaanse notatie onderwater, waarbij het slechts een gebruikersinstelling heeft om die informatie aan jou te presenteren.quote:Op maandag 25 november 2019 13:11 schreef Z het volgende:
[..]
Opgelost (dit werkt):
myFormula = "=VLOOKUP(A" & i + 1 & ",[varvallab_2.xlsm]Blad1!A$1:B$17983,2,FALSE)"
Ik weet dat macros met engelse termen werken. Maar omdat ik nu in wezen een string paste in een cel, snap ik nog steeds niet helemaal niet dat de macro dit niet wil doen.
Het antwoord is eigenlijk zo simpel, dat je wel een string in de cel zet. Deze waarde zet je alleen op een andere laag dan jij verwacht. De macro zit niet op de gebruikersinterface kant waar de taal en toetsenbordinstellingen invloed hebben op de werking van de macro. Immers zou je macro dan niet meer werken wanneer je het bestandje naar mij stuurt en ik de engelse versie draai waar jij de nederlandse draait.
Ik zit even te zoeken welke je gebruikt. Ik vermoed dat het gaat over een formulierbesturingselement. Daar had ik zelf nog even zo snel niet een tekstvak met een link naar celinhoud gevonden. Die links ken ik zelf voornamelijk vanuit keuze lijsten.quote:
Ik heb een textbox met een vaste waarde (' dit is een waarde' ) bijvoorbeeld, die wil ik linken aan de waarde van de dyamische cel. Zonder tekst is dat geen probleem, ik verwijs in de tekstbox naar de cel, maar hoe combineer ik die? Is dat zonder moeilijke fratsen te doen?
De enige optie die doet die jij beschrijft is om zelf een Tekstvak te maken en door middel van een macro dmv Change iets uit te voeren.
Kan je iets meer precies aangeven wat je precies gebruikt?
Dus een 'kop' en daaronder rijen met of een getallen (bijvoorbeeld 11,021...) of een percentage (bijvoorbeeld 16,985...%).
Nu wil ik die percentages 'converteren naar getal'. Maar dat lukt me niet.
Wat werkt is 'F2 + Enter' maar dat in een macro voor een matrix van 100 bij 25000 is dat niet "het meest optimaal".
Iemand een idee?
Heeft iemand een andere suggestie?
Kolom selecteren -> Gegevens (bovenaan) -> Tekst naar kolommen -> Voltooienquote:
Ik heb een databestand wat er als volgt uitziet:
[ afbeelding ]
Dus een 'kop' en daaronder rijen met of een getallen (bijvoorbeeld 11,021...) of een percentage (bijvoorbeeld 16,985...%).
Nu wil ik die percentages 'converteren naar getal'. Maar dat lukt me niet.
Wat werkt is 'F2 + Enter' maar dat in een macro voor een matrix van 100 bij 25000 is dat niet "het meest optimaal".
Iemand een idee?
Heeft iemand een andere suggestie?
Wil wel eens helpen.
Werkt! Veel dank.quote:
[..]
Kolom selecteren -> Gegevens (bovenaan) -> Tekst naar kolommen -> Voltooien
Wil wel eens helpen.
Of *1quote:
Overal 0 bij optellen wil ook nog wel eens werken.
Links en rechts hoor ik dat Power BI ongekende mogelijkheden biedt.
Ik heb wat bekeken hier en daar, maar volgens mij zijn de mogelijkheden eigenljk allemaal wel te vinden binnen Excel zelf.
Het gaat om het selecteren van data en het uitvoeren van queries, etc.
Als je je hebt verdiept in filters, draaitabellen en enigszins handig bent met VBA lijkt met Power BI nauwelijks meerwaarde hebben.
Wat is jullie mening?
Ik sla het over. Doe mijn query's liever in Access. Vele malen sneller met data.quote:
Power BI:
Links en rechts hoor ik dat Power BI ongekende mogelijkheden biedt.
Ik heb wat bekeken hier en daar, maar volgens mij zijn de mogelijkheden eigenljk allemaal wel te vinden binnen Excel zelf.
Het gaat om het selecteren van data en het uitvoeren van queries, etc.
Als je je hebt verdiept in filters, draaitabellen en enigszins handig bent met VBA lijkt met Power BI nauwelijks meerwaarde hebben.
Wat is jullie mening?
Heb het volgens mij 10 minuten geprobeerd met Power Bi toen maar met tranen in de ogen dichtgedaan.
Grafieken maken in Access is wel nog erg 1997. Maar daarvoor duwik data dan naar excel, en maak daar mijn grafieken.
Tweede vraag:quote:
[..]
Ik sla het over. Doe mijn query's liever in Access. Vele malen sneller met data.
Heb het volgens mij 10 minuten geprobeerd met Power Bi toen maar met tranen in de ogen dichtgedaan.
Grafieken maken in Access is wel nog erg 1997. Maar daarvoor duwik data dan naar excel, en maak daar mijn grafieken.
Heeft Access meerwaarde boven Excel?
Zoals ik al zei kunnen query's, grafieken e.d. volgens mij in Excel ook worden verricht.
Access heeft wel degelijk meerwaarde, o.a. omdat het een relationele databse is. Je kan hetzelfde doel wel behalen met Excel, maar Access is er beter in.quote:
[..]
Tweede vraag:
Heeft Access meerwaarde boven Excel?
Zoals ik al zei kunnen query's, grafieken e.d. volgens mij in Excel ook worden verricht.
Zie ook het door MS slecht vertaalde verhaal op hun eigen site: https://support.office.co(...)6f-9312-e825227fcaea
Ik wil van alle getallen tussen de 111111111 en 999999999 de getallen hebben waarbij de getallen 1 tm 9 precies éénmalig voorkomen.
Voorbeeld:
123456789
213456789
976854321
Maar dus niet: 223456789 of 333344445
Volgens mijn berekening zijn er 9 x 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 = 362.880 mogelijkheden
(Oftewel 9! --> 9 faculteit)
Hoe zou je in Excel vba de code moeten fabriceren om deze 362.880 mogelijkheden uit te spugen?
(de eerste met de juiste oplossing krijgt een virtuele schouderklop en uiteraard eeuwige roem)
=VIND.SPEC(1;A4;1)+VIND.SPEC(2;A4;1)+VIND.SPEC(3;A4;1)+VIND.SPEC(4;A4;1)+VIND.SPEC(5;A4;1)+VIND.SPEC(6;A4;1)+VIND.SPEC(7;A4;1)+VIND.SPEC(8;A4;1)+VIND.SPEC(9;A4;1)quote:
Een challenge waar jullie me misschien mee kunnen helpen.
Ik wil van alle getallen tussen de 111111111 en 999999999 de getallen hebben waarbij de getallen 1 tm 9 precies éénmalig voorkomen.
Voorbeeld:
123456789
213456789
976854321
Maar dus niet: 223456789 of 333344445
Volgens mijn berekening zijn er 9 x 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 = 362.880 mogelijkheden
(Oftewel 9! --> 9 faculteit)
Hoe zou je in Excel vba de code moeten fabriceren om deze 362.880 mogelijkheden uit te spugen?
(de eerste met de juiste oplossing krijgt een virtuele schouderklop en uiteraard eeuwige roem)
En dan alles met als antwoord 45!?
Maar dan moet je een miljard getallen langs. Dat zijn er best veel.quote:Op maandag 9 december 2019 21:40 schreef _I het volgende:
[..]
=VIND.SPEC(1;A4;1)+VIND.SPEC(2;A4;1)+VIND.SPEC(3;A4;1)+VIND.SPEC(4;A4;1)+VIND.SPEC(5;A4;1)+VIND.SPEC(6;A4;1)+VIND.SPEC(7;A4;1)+VIND.SPEC(8;A4;1)+VIND.SPEC(9;A4;1)
En dan alles met als antwoord 45!?
Het is wel een leuk vraagstuk. Als je het antwoord hebt kun je excel volgens mij ook sudoku's laten oplossen.
Ja das ook weer zo 😅quote:
[..]
Maar dan moet je een miljard getallen langs. Dat zijn er best veel.
Het is wel een leuk vraagstuk. Als je het antwoord hebt kun je excel volgens mij ook sudoku's laten oplossen.
*slingert laptop weer aan..
Jep: https://www.microsoft.com(...)calculation-part-12/ (site is al zo oud dat de plaatjes het niet eens meer doenquote:
[..]
Maar dan moet je een miljard getallen langs. Dat zijn er best veel.
Het is wel een leuk vraagstuk. Als je het antwoord hebt kun je excel volgens mij ook sudoku's laten oplossen.
)Helaas, 45 gaat niet op. Vb; 555555555 of 999932211. Bezitten beide dubbelen en gesommeerd 45..quote:
[..]
=VIND.SPEC(1;A4;1)+VIND.SPEC(2;A4;1)+VIND.SPEC(3;A4;1)+VIND.SPEC(4;A4;1)+VIND.SPEC(5;A4;1)+VIND.SPEC(6;A4;1)+VIND.SPEC(7;A4;1)+VIND.SPEC(8;A4;1)+VIND.SPEC(9;A4;1)
En dan alles met als antwoord 45!?
Voor databewerking is het niet geschikt. Veel te traag en onhandig. Echt alleen voor dashboards gebruiken.quote:
Power BI:
Links en rechts hoor ik dat Power BI ongekende mogelijkheden biedt.
Ik heb wat bekeken hier en daar, maar volgens mij zijn de mogelijkheden eigenljk allemaal wel te vinden binnen Excel zelf.
Het gaat om het selecteren van data en het uitvoeren van queries, etc.
Als je je hebt verdiept in filters, draaitabellen en enigszins handig bent met VBA lijkt met Power BI nauwelijks meerwaarde hebben.
Wat is jullie mening?
Voordelen zou ik zeggen:
1. Gebruiksvriendelijk (voor de gebruiker van het dashboard, niet per se voor de programmeur). Jij kan misschien goed overweg met geavanceerde filters/queries maar deel je die bestanden ook, en zo ja kunnen die mensen er ook goed mee overweg? In PowerBI kan iedereen het.
2. Gelikter. Excel heeft geen goede presenteermodus of iets dergelijks.
3. Dashboards delen via de cloud.
Maar ik ben geen Excel/VBA expert. Gebruik meestal Python voor scripting/databewerking en dan eventueel het resultaat exporteren naar csv en in PowerBI stoppen.
Voor de programmeur is PowerBI ook niet altijd gebruiksvriendelijk. Het is ook nog steeds in ontwikkeling dus veel functionaliteiten die je zou willen zijn er nog niet of veranderen steeds. Ik was ooit uren op zoek naar een box plot. Bleek alleen via een R-extensie te kunnen.
Hoe PowerBI is ten opzichte van andere dashboard-programma's weet ik niet.
Nope, beide een errorquote:
[..]
Helaas, 45 gaat niet op. Vb; 555555555 of 999932211. Bezitten beide dubbelen en gesommeerd 45..
Jij telt de getallen bij elkaar op, maar de formule werkt alleen als alle getallen er 1 x in voorkomen.
[ Bericht 9% gewijzigd door _I op 10-12-2019 06:22:32 ]
Ah had niet door dat hij alleen o unieke triggerde, nice!quote:
[..]
Nope, beide een error
Jij telt de getallen bij elkaar op, maar de formule werkt alleen als alle getallen er 1 x in voorkomen.
Uiteindelijk de volgende code gevonden, spuugt alle mogelijkheden uit;quote:
Een challenge waar jullie me misschien mee kunnen helpen.
Ik wil van alle getallen tussen de 111111111 en 999999999 de getallen hebben waarbij de getallen 1 tm 9 precies éénmalig voorkomen.
Voorbeeld:
123456789
213456789
976854321
Maar dus niet: 223456789 of 333344445
Volgens mijn berekening zijn er 9 x 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 = 362.880 mogelijkheden
(Oftewel 9! --> 9 faculteit)
Hoe zou je in Excel vba de code moeten fabriceren om deze 362.880 mogelijkheden uit te spugen?
(de eerste met de juiste oplossing krijgt een virtuele schouderklop en uiteraard eeuwige roem)
| 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 | Private CurrentRow As Long Sub GetString() Dim InString As String InString = InputBox("Enter text to permute:") If Len(InString) < 2 Then Exit Sub If Len(InString) > 9 Then MsgBox "Too many permutations!" Exit Sub Else ActiveSheet.Columns(1).Clear CurrentRow = 1 Call GetPermutation("", InString) End If End Sub Sub GetPermutation(x As String, y As String) Dim i As Integer, j As Integer j = Len(y) If j < 2 Then Cells(CurrentRow, 1) = x & y CurrentRow = CurrentRow + 1 Else For i = 1 To j Call GetPermutation(x + Mid(y, i, 1), _ Left(y, i - 1) + Right(y, j - i)) Next End If End Sub |
Ik krijg dit wel voor elkaar. Maar ik begin niet met een formule in VBA. Dat duurt veel te lang. Copieer eerst eens allemaal eentjes in de cellen A1 t/m a40320.quote:
Een challenge waar jullie me misschien mee kunnen helpen.
Ik wil van alle getallen tussen de 111111111 en 999999999 de getallen hebben waarbij de getallen 1 tm 9 precies éénmalig voorkomen.
Voorbeeld:
123456789
213456789
976854321
Maar dus niet: 223456789 of 333344445
Volgens mijn berekening zijn er 9 x 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 = 362.880 mogelijkheden
(Oftewel 9! --> 9 faculteit)
Hoe zou je in Excel vba de code moeten fabriceren om deze 362.880 mogelijkheden uit te spugen?
(de eerste met de juiste oplossing krijgt een virtuele schouderklop en uiteraard eeuwige roem)
Dan: a40321 vul je met de formule a1+1. copieer die cel naar A40322 t/m A362880
Daarna copieer je kolom A en doe je "plakken speciaal" "waarden".
Zo, dan heb je het eerste cijfer al gefixed. De rest is een beetje logisch nadenken. copy pasten special values. Enzo….
van 111111111 en 999999999quote:
[..]
Uiteindelijk de volgende code gevonden, spuugt alle mogelijkheden uit;
[ code verwijderd ]
hoef je volgens mij maar de helft te doen. na 555555555 kan je de resultaten spiegelen
No shit!quote:
[..]
van 111111111 en 999999999
hoef je volgens mij maar de helft te doen. na 555555555 kan je de resultaten spiegelen
Dat scheelt een hoop werk....
Ik heb een vraag over excel.
Ik heb de urls van afbeeldingen in excel staan, verdeeld over 3 kolommen.
http://website/ 1 /300/300.png
http://website/ 2 /300/300.png
http://website/ 3 /300/300.png
Elke keer is de middelste kolom anders (bedoel dus gewoon increment) en dat zorgt voor een nieuwe afbeelding.
Wat ik nu graag wil is het volgende:
http://website/ 1 /300/300.png
https://eenstandaardfoto.png
https://eenstandaardfoto.png
https://eenstandaardfoto.png
http://website/ 2 /300/300.png
https://eenstandaardfoto.png
https://eenstandaardfoto.png
https://eenstandaardfoto.png
http://website/ 3 /300/300.png
https://eenstandaardfoto.png
etc.
Tegenover elk persoon wil ik 3 a 4 keer een standaardfoto presenteren omdat niet iedereen een foto upload.
Kan iemand mij vertellen hoe een dergelijke excel formule eruit zou zien?
Alvast erg bedankt
Lijkt namelijk wat suf als ik eerst van 1.000 personen de foto toon en dan van 3.000 een standaardafbeelding. Heb het liever wat gelijkmatig verdeeld. Nogmaals ontzettend bedankt alvast!In E1 zet je de formule: =A1+1
Cel E1 kopieer je door naar rechts zover je wilt.
Je hebt nu 4x hetzelfde getal, met een increment.
In cel A2 zet je de formule: =hyperlink("http://website/"&a1&"/300/300.png")
Je B2,C2 en D2 je andere link.
Selecteer de cellen A2 t/m D2. Kopieer door naar rechts zo ver je wilt.
Ik print regelmatig wat uit uit Excel en onderaan de pagina die uitgeprint wordt staat een tabelletje in de voettekst, als afbeelding in voettekst. In dat tabelletje vul ik dan met een pen de datum van vandaag en die van morgen in en nog wat dingetjes. Wat ik eigenlijk wil is die tabel-afbeelding vervangen door een excel-tabel zodat ik er met =vandaag() automatisch de datum kan vullen. Ik heb vandaag wat zitten klooien met de voettekst maar het lijkt alsof de gehele voettekst maar in 1 cel kan....
Kan wat ik wil en zo ja hoe?
Kan, maar met wat VBA, met een BeforePrint event (Zie Allen Wyatt):quote:
| 1 2 3 4 | Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.CenterHeader = Format(Date - 1, "mmmm d, yyyy") & " " & Format(Time, "mm:hh:ss") ActiveSheet.PageSetup.CenterFooter = Format(Date + 5, "mmmm d, yyyy") End Sub |
Klik in het menu van Pagina-instelling - Koptekst/voettekst op Aangepaste voettekst.quote:
Oke, ik wil iets maar ik twijfel of deze mogelijkheid in Excel bestaat (ik vermoed van wel)
Ik print regelmatig wat uit uit Excel en onderaan de pagina die uitgeprint wordt staat een tabelletje in de voettekst, als afbeelding in voettekst. In dat tabelletje vul ik dan met een pen de datum van vandaag en die van morgen in en nog wat dingetjes. Wat ik eigenlijk wil is die tabel-afbeelding vervangen door een excel-tabel zodat ik er met =vandaag() automatisch de datum kan vullen. Ik heb vandaag wat zitten klooien met de voettekst maar het lijkt alsof de gehele voettekst maar in 1 cel kan....
Kan wat ik wil en zo ja hoe?
Daar heb je ook buttons die je helpen, maar wat jij zoekt is bijvoorbeeld &[Datum]
Het is dan wel in het format van 14-1-2020. Je hebt wel een macro nodig als je het format anders wil hebben.
Had ik dit niet al zo beantwoord?quote:
[..]
Klik in het menu van Pagina-instelling - Koptekst/voettekst op Aangepaste voettekst.
Daar heb je ook buttons die je helpen, maar wat jij zoekt is bijvoorbeeld &[Datum]
Het is dan wel in het format van 14-1-2020. Je hebt wel een macro nodig als je het format anders wil hebben.
Helder, en thanks aan The_vice ook.quote:
[..]
Klik in het menu van Pagina-instelling - Koptekst/voettekst op Aangepaste voettekst.
Daar heb je ook buttons die je helpen, maar wat jij zoekt is bijvoorbeeld &[Datum]
Het is dan wel in het format van 14-1-2020. Je hebt wel een macro nodig als je het format anders wil hebben.
Het punt is niet dat ik 1 cel wil, dat lukt nog wel, maar een blokje van 4 rijen/2 kolommen.
Je post was prima. Ik zag in jouw post vooral de VBA code. Ik vul dat aan met wat buiten VBA kan.quote:
[..]
Had ik dit niet al zo beantwoord?
Dat is inderdaad iets meer gekunstel. Sowieso is het makkelijker wanneer het in de header staat (dan herhaal je gewoon de eerste rijen op elke pagina).quote:
[..]
Helder, en thanks aan The_vice ook.
Het punt is niet dat ik 1 cel wil, dat lukt nog wel, maar een blokje van 4 rijen/2 kolommen.
De snelste ideeën die in mijn hoofd opkomen, is de printfunctie via Word te gebruiken. Dus eventueel via VBA naar Word en dan naar print.
Maar hoe doe je dan plus of min één dag? Daar ging je dan niet naar aanleiding van de originele vraag niet op in.quote:
[..]
Je post was prima. Ik zag in jouw post vooral de VBA code. Ik vul dat aan met wat buiten VBA kan.
Volgens mij kan dat niet met de [&Datum] methode, maar ik laat me graag verrassen.
Hoe zou jij dat doen?
Dat kan idd niet. Wat wel jammer is, want in Word een formule in de footer plakken is mega handig maar in Excel kan je alleen de opmaak (blod, cursief, lettertype, kleur, etc) aanpassen..quote:
[..]
Maar hoe doe je dan plus of min één dag? Daar ging je dan niet naar aanleiding van de originele vraag niet op in.
Volgens mij kan dat niet met de [&Datum] methode, maar ik laat me graag verrassen.
Hoe zou jij dat doen?
In de snelheid had ik het stuk van morgen uit de vraag niet opgepikt. Net zo goed dat de vba code geen invulling geeft aan de gevraagde tabel-vorm en dus ook geen oplossing is..quote:
[..]
Maar hoe doe je dan plus of min één dag? Daar ging je dan niet naar aanleiding van de originele vraag niet op in.
Volgens mij kan dat niet met de [&Datum] methode, maar ik laat me graag verrassen.
Hoe zou jij dat doen?
Terug naar het probleem:
- Ik zie alleen mogelijkheden als een excel importeren in een programma dat het wel kan, zoals word.
- zelf prutsen met de marges van het papier en de normale excel cellen.
- gebruik te maken van de header ipv footer. Ik zat zelfs nog te denken of je dat misschien ondersteboven kan printen, zodat je header een footer wordt. Dat idee is nog niet omgezet in iets wat werkt.
- tot slot kan je zelf prutsen met wat lijntjes tekenen met --- en |. Nadeel is wel dat je maar 255 karakters kan gebruiken.
Klopt,quote:
[..]
- tot slot kan je zelf prutsen met wat lijntjes tekenen met --- en |. Nadeel is wel dat je maar 255 karakters kan gebruiken.
wat wel nog kan is een herhaal n rijen als header misbruiken en daar wat gegevens in stoppen als tabel.
Maar ja Excel is ook geen opmaak programma. Hoewel veel mensen dat máár blijven proberen.
Ik ken voorbeelden waar men elke pagina op een apart sheet zet, met een leuke header in de pagina uitgetekend op het sheet. En dan 40 sheets aanmaken vol met gemergde cellen.
brrrrr...
hier krijg ik bijvoorbeeld nachtmerries van (wel uitzoomen)
[ Bericht 32% gewijzigd door dimmak op 16-01-2020 12:06:38 ]
Bij 1 bestand lukt me dat helaas niet. Er zijn allerlei query's in gemaakt. Ik heb de linkjes ververst met de nieuwe bestanden en het leek goed te gaan. De eerste nieuwe data van 2020 staat er. Maar nu gebeurt er niets meer. Hij updated niet meer automatisch en ook als ik alles probeer te refreshen, gebeurt er niets.
Ik heb het originele bestand niet gemaakt en diegene die het wel heeft gedaan, werkt hier niet meer.
Hebben jullie tips/ideeën/de oplossing?
Zijn de queries daarin opgenomen?
Ja. Daar kan ik wel vanalles terugvinden, maar kan dus niet vinden waar t misgaat.quote:
In de Ribbon (tabblad) Data (gegevens) heb je een knopje Connections (verbindingen).
Zijn de queries daarin opgenomen?
Ik wil op een formulier graag dat Excel een woord neerzet als het binnen een bepaalde getallenreeks valt.
Het lukt me om dit te doen met één woord, alleen niet met de rest.
Kan iemand mij hier mee helpen?
excellent 209 of hoger
goed 132 - 208
matig 110 - 131
onvoldoende 0 - 109
Dit is het idee.
Ik heb dus een hokje (I113) waar een bepaald getal in wordt berekend, aan de hand van dat getal wil ik in een ander hokje (C115) automatisch krijgen dat het excellent, goed, matig of onvoldoende is.
Ik gebruik Excel 2016 Nederlands
Alvast bedankt!
Ik heb nu dus dit erin staan:quote:
Hallo,
Ik wil op een formulier graag dat Excel een woord neerzet als het binnen een bepaalde getallenreeks valt.
Het lukt me om dit te doen met één woord, alleen niet met de rest.
Kan iemand mij hier mee helpen?
excellent 209 of hoger
goed 132 - 208
matig 110 - 131
onvoldoende 0 - 109
Dit is het idee.
Ik heb dus een hokje (I113) waar een bepaald getal in wordt berekend, aan de hand van dat getal wil ik in een ander hokje (C115) automatisch krijgen dat het excellent, goed, matig of onvoldoende is.
Ik gebruik Excel 2016 Nederlands
Alvast bedankt!
=ALS(I113<110;"Onvoldoende")
Verder lukt het me niet...
Enzovoorts
Maar 108 is dan toch ook kleiner dan 132?quote:
ALS(I113<110;"Onvoldoende";ALS(I113 < 132;“Matig”))
Enzovoorts
Ja, maar excel werkt van voren af aan. Dus hij komt eerst de voorwaarde '<110' tegen, en voor 108 geldt dat, dus dan geeft ie de waarde Onvoldoende terug. Met alles wat er nog achteraankomt doet hij niks meer, de voorwaarde is immers al gevuld.quote:
[..]
Maar 108 is dan toch ook kleiner dan 132?
De syntax is =ALS(voorwaarde;waarde-indien-waar;waarde-indien-onwaar). Dat betekent dat alles vanaf de tweede ALS pas gelezen wordt als die eerste voorwaarde onwaar is.
Die tweede ALS voert hij alleen uit als de eerste ALS False is.quote:
[..]
Maar 108 is dan toch ook kleiner dan 132?
Kijk even naar de definitie van ALS. Je hebt drie argumenten.
Let even op de puntkomma's en hoe het ALS-commando werkt:quote:
[..]
Maar 108 is dan toch ook kleiner dan 132?
=ALS(conditie;indien resultaat waar;indien resultaat niet waar)
Drie argumenten, het derde argument is de 'ga hier verder als de conditie niet waar is'. Hier kun je dus ook een nieuw ALS-commando in nesten, en dat kun je best ver doorvoeren.
Met dank!
Het is best lastig om op deze manier het probleem te vinden, maar ik doe nog een poging.quote:
[..]
Ja. Daar kan ik wel vanalles terugvinden, maar kan dus niet vinden waar t misgaat.
Wanneer je in het menu zit van de verbindingen, klik op één van de queries. Vervolgens op Eigenschappen.
Naast de instellingen over hoe er vernieuwd moet worden, is er op het tweede tabblad een definitie te vinden. Afhankelijk van het type kan je hier een verbindingsbestand vinden, maar in ieder geval ook de verbindingsreeks.
De reeks heeft allerlei informatie over hoe de connectie moet plaatsvinden.
Kan je mij deze reeks sturen? Of kan je daaruit zelf al herkennen dat het wellicht naar een bestand verwijst dat niet meer bestaat (bijv door naamwijzigingen).
Ik importeer een lijst met daarin o.a. een kolom:
Campaign reference
GLOBAL-2020-01-P-NewYears-en
GLOBAL-2020-02-P-NewYears-es
GLOBAL-2020-02-P-NewYears-fr
RU-2020-03-P-WomensDay-ru
BR-2020-01-B-CallcenterPromo-en
BR-2020-01-B-CallcenterPromo-br
etc
etc
Nu wil ik voor de import naar een werkblad de taalparameter deleten. Dus dat bijvoorbeeld -en, -fr, -es wordt verwijderd.
In een werkblad doe ik dat met
=LEFT(A2; (FIND("-en";A2; 1)-1)) voor Engels of =LEFT(A2; (FIND("-fr";A2; 1)-1)) voor Frans.
Nu vond ik dat de LEFT functie in queries wordt vervangen door
=Text.Starts([Campaign reference])
Maar ik weet niet hoe ik het zo voor elkaar krijg, dat alles blijft behalve -en/-fr/-es.
Iemand een idee?
Met welke excel functie haal je dit binnen en uit wat voor een bron?quote:
Ik heb een vraag m.b.t. een query.
Ik importeer een lijst met daarin o.a. een kolom:
...
Iemand een idee?
text bestand, excel, access etc?
zoiezo zou ik de functie als volgt schrijven:
| 1 | =LEFT([@Remark];LEN([@Remark])-3) |
[ Bericht 24% gewijzigd door The_vice op 03-03-2020 16:25:00 ]
Dat weet ik ook niet, maar ik weet wel dat je het FIND-gedeelte uit je formule kan vervangen door LEN(A2)-3, mits in alle cellen de taal de 3 laatste karakters beslaat. In lange lijsten scheelt dat een hoop rekentijd.quote:
Ik heb een vraag m.b.t. een query.
Ik importeer een lijst met daarin o.a. een kolom:
Campaign reference
GLOBAL-2020-01-P-NewYears-en
GLOBAL-2020-02-P-NewYears-es
GLOBAL-2020-02-P-NewYears-fr
RU-2020-03-P-WomensDay-ru
BR-2020-01-B-CallcenterPromo-en
BR-2020-01-B-CallcenterPromo-br
etc
etc
Nu wil ik voor de import naar een werkblad de taalparameter deleten. Dus dat bijvoorbeeld -en, -fr, -es wordt verwijderd.
In een werkblad doe ik dat met
=LEFT(A2; (FIND("-en";A2; 1)-1)) voor Engels of =LEFT(A2; (FIND("-fr";A2; 1)-1)) voor Frans.
Nu vond ik dat de LEFT functie in queries wordt vervangen door
=Text.Starts([Campaign reference])
Maar ik weet niet hoe ik het zo voor elkaar krijg, dat alles blijft behalve -en/-fr/-es.
Iemand een idee?
Het is een Excel lijst die ik dus importeer naar een query. De bron van deze Excel lijst is onze interne database. Vroege spuugde deze in .csv uit maar sinds kort in .xlsquote:
[..]
Met welke excel functie haal je dit binnen en uit wat voor een bron?
text bestand, excel, access etc?
Hm ja, dat is wat ik eerst deed. Maar helaas zijn oudere data nog wat onrein, dus dan mist de taalparamenter of is er nog een paramater achter geplaatst. Dus deze functie gaat op voor recente campagnes, maar niet voor oudere.quote:
[..]
Dat weet ik ook niet, maar ik weet wel dat je het FIND-gedeelte uit je formule kan vervangen door LEN(A2)-3, mits in alle cellen de taal de 3 laatste karakters beslaat. In lange lijsten scheelt dat een hoop rekentijd.
Ah lijkt erop dat je het nu over twee dingen hebt?quote:
[..]
Het is een Excel lijst die ik dus importeer naar een query. De bron van deze Excel lijst is onze interne database. Vroege spuugde deze in .csv uit maar sinds kort in .xls
is een power query, of een andere excel functie?
in powerquery lijkt "Text.Start("Hello, World", 5)" de eerste 5 karakters op te halen, als je de 5 meegeeft. In je voorbeeld stond er geen aantal achter.
Verder, met je latere post, als je twee typen data in één kolom importeert, test dan in een volgende kolom of bijvoorbeeld het op 3 na laatste karakter een "-" is.
| 1 | =RIGHT(LEFT(A1;LEN(A1)-2);1)="-" |
dan in de volgende kolom (C)
| 1 | =IF(B1;LEFT(A1;LEN(A1)-3);A1) |
Oké moment, even verduidelijken:quote:
[..]
Ah lijkt erop dat je het nu over twee dingen hebt?
is een power query, of een andere excel functie?
in powerquery lijkt "Text.Start("Hello, World", 5)" de eerste 5 karakters op te halen, als je de 5 meegeeft. In je voorbeeld stond er geen aantal achter.
Verder, met je latere post, als je twee typen data in één kolom importeert, test dan in een volgende kolom of bijvoorbeeld het op 3 na laatste karakter een "-" is.
[ code verwijderd ]
(met in kolom A de data in dit voorbeeld, en in B deze formule)
dan in de volgende kolom (C)
[ code verwijderd ]
dan laat je afhankelijk van het resultaat wel of niet (True/False of Waar/Onwaar in nl versie) de tekst opschonen
Ik heb het inderdaad over power query. En het bestand dat ik daar importeer is toch een .csv, ik was even in de war met een ander bestand.
Na het importeren van deze lijst ziet het er ongeveer zo uit:
En na het opschonen en bruikbaar maken ziet het er zo uit:SPOILER
Zoals je ziet is de hele Campaign reference één kolom, dus helaas niet samengesteld verschillende kolommen. Dat zou handig zijn want dan zou ik gewoon de kolom met taalparameter verwijderen.SPOILER
Uiteindelijk wil ik dit weer in een Excelwerkblad weergeven, waarbij dus alleen GLOBAL-2020-01-P-DiscoverEurope te zien is.
Als ik de lijst uit het tweede plaatje van mijn vorige post in Excel zet, en dan de =LEFT functie toepas, dan werkt het wel:
https://ibb.co/tYtn8Rb
Maar ik wil dit dus direct in Power Query doen.
Simpeler dan ik dacht. Moet nu alleen wel één keer manueel alle mogelijke taal- of andere parameters toevoegen om te verwijderen.
Wellicht kan je de twee letter versies snel bouwen vanaf deze lijst, of importeren middels power query en er dan dingen mee doen.quote:
Nadeel van de replace is denk ik wel dat alle items die lijken op -en, -fr etc. vervangen worden, of bijvoorbeeld alleen de eerste verschijning ervan.
Neem "las-vegas", dat kan gestript worden tot "lasgas" als je -ve (Venezuela) in de lijst opneemt.
Maar dat zal je met deze methode proefondervindelijk moeten uitvinden.
Dankje voor de tip.quote:
[..]
Wellicht kan je de twee letter versies snel bouwen vanaf deze lijst, of importeren middels power query en er dan dingen mee doen.
Nadeel van de replace is denk ik wel dat alle items die lijken op -en, -fr etc. vervangen worden, of bijvoorbeeld alleen de eerste verschijning ervan.
Neem "las-vegas", dat kan gestript worden tot "lasgas" als je -ve (Venezuela) in de lijst opneemt.
Maar dat zal je met deze methode proefondervindelijk moeten uitvinden.
En inderdaad, daar dacht ik ook aan. Maar zie op dit moment geen andere oplossing. Moet er bij de naamgeving van campagnes dan maar rekening mee houden of zo.
Je had deze ook kunnen gebruiken.quote:
Ik heb een vraag m.b.t. een query.
Ik importeer een lijst met daarin o.a. een kolom:
Campaign reference
GLOBAL-2020-01-P-NewYears-en
GLOBAL-2020-02-P-NewYears-es
GLOBAL-2020-02-P-NewYears-fr
RU-2020-03-P-WomensDay-ru
BR-2020-01-B-CallcenterPromo-en
BR-2020-01-B-CallcenterPromo-br
etc
etc
Nu wil ik voor de import naar een werkblad de taalparameter deleten. Dus dat bijvoorbeeld -en, -fr, -es wordt verwijderd.
In een werkblad doe ik dat met
=LEFT(A2; (FIND("-en";A2; 1)-1)) voor Engels of =LEFT(A2; (FIND("-fr";A2; 1)-1)) voor Frans.
Nu vond ik dat de LEFT functie in queries wordt vervangen door
=Text.Starts([Campaign reference])
Maar ik weet niet hoe ik het zo voor elkaar krijg, dat alles blijft behalve -en/-fr/-es.
Iemand een idee?
Oh, nice! Waar vind ik dat? Net gezocht maar niet gevonden (misschien vanwege NL --> EN omzetting)quote:
[..]
Je had deze ook kunnen gebruiken.
[ afbeelding ]
In het Engels onder het tabje Transform > Extract > Text Before Delimiterquote:
[..]
Oh, nice! Waar vind ik dat? Net gezocht maar niet gevonden (misschien vanwege NL --> EN omzetting)
Vanuit een query heb ik 100 datums:
1-11-62
1-8-53
1-12-59
Etc. Dit gaat om toekomstige data dus 2062, 2053, etc.
Als ik uit die query kopieer naar Excel, leest Excel het als:
1-11-1962
1-8-1953
1-12-1959
Is hier wat aan te doen?
Normaal werkt kopiëren naar kladblok en dan als tekst plakken redelijk, maar in dit geval ook niet.
Overal 100 jaar bij optellen?quote:
Een datumvraag.
Vanuit een query heb ik 100 datums:
1-11-62
1-8-53
1-12-59
Etc. Dit gaat om toekomstige data dus 2062, 2053, etc.
Als ik uit die query kopieer naar Excel, leest Excel het als:
1-11-1962
1-8-1953
1-12-1959
Is hier wat aan te doen?
Normaal werkt kopiëren naar kladblok en dan als tekst plakken redelijk, maar in dit geval ook niet.
=DATUM(JAAR(A1)+100;MAAND(A1);DAG(A1))
Of, als het niet bij allemaal is
=ALS(A1<36526;DATUM(JAAR(A1)+100;MAAND(A1);DAG(A1));A1)
Hmm die +100 jaar had wel een uitkomst geweest ja. Uiteindelijk heb ik m'n query dusdanig kunnen aanpassen dattie van 1-4-45 1-4-2045 maakte en dan ben ik er wel.quote:
[..]
Overal 100 jaar bij optellen?
=DATUM(JAAR(A1)+100;MAAND(A1);DAG(A1))
Of, als het niet bij allemaal is
=ALS(A1<36526;DATUM(JAAR(A1)+100;MAAND(A1);DAG(A1));A1)
Het kan een Windows-setting zijn: https://answers.microsoft(...)98-b3ed-5ac31669214fquote:
Een datumvraag.
Vanuit een query heb ik 100 datums:
1-11-62
1-8-53
1-12-59
Etc. Dit gaat om toekomstige data dus 2062, 2053, etc.
Als ik uit die query kopieer naar Excel, leest Excel het als:
1-11-1962
1-8-1953
1-12-1959
Is hier wat aan te doen?
Normaal werkt kopiëren naar kladblok en dan als tekst plakken redelijk, maar in dit geval ook niet.
Dus in tabblad 1 staat dit:
Jan
Piet
Kees
In tabblad 2 wil ik dit dan kunnen kopieren dat hij die namen over neemt maar verder niks. En als er een week erna in tabblad 1 een naam bij komt dat deze ook automatisch gevuld wordt.
Bedankt alvast.
Edit: gevonden: =ALS(Blad1!A2;Kopieerblad!A2;"")
[ Bericht 5% gewijzigd door Stansfield op 21-04-2020 16:24:47 ]
Ik heb in één cel twee formules staan, de formules werken naar behoren als ik die beide in een cel zet. Als ik de formule in één cel zet dan werkt het tweede gedeelte van de formuler niet (de som.als)
Wie o wie?SPOILER=SOM(VERSCHUIVING('Automatisering Invul'!A10;0;1;1;$F$1))+SOM.ALS('Automatisering Invul'!$O$5:$Z$5;">"&$F$1;'Automatisering Invul'!O10:Z10)
Gokje, omdat SOM.ALS alleen verticaal werkt en niet horizontaal?quote:Op maandag 27 april 2020 21:34 schreef phpmystyle het volgende:
Beste FOK!kers,
Ik heb in één cel twee formules staan, de formules werken naar behoren als ik die beide in een cel zet. Als ik de formule in één cel zet dan werkt het tweede gedeelte van de formuler niet (de som.als)Wie o wie?SPOILER=SOM(VERSCHUIVING('Automatisering Invul'!A10;0;1;1;$F$1))+SOM.ALS('Automatisering Invul'!$O$5:$Z$5;">"&$F$1;'Automatisering Invul'!O10:Z10)
Dat is het niet, want de formules werken wel als ik die twee afzonderlijk in een cel zet.quote:
[..]
Gokje, omdat SOM.ALS alleen verticaal werkt en niet horizontaal?
misschien wat extra haakjes erbij?quote:
[..]
Dat is het niet, want de formules werken wel als ik die twee afzonderlijk in een cel zet.
[ Bericht 64% gewijzigd door phpmystyle op 28-04-2020 00:00:53 ]
https://imgur.com/a/jLWNuBQ
Door simpelweg de "som van Bestedingen / Gemiddelde van budget" te doen. Kan iemand mij helpen deze formule toe te passen?
Voeg in het bronbestand een kolom toe met de berekening.quote:Op dinsdag 12 mei 2020 10:38 schreef hello_moto1992 het volgende:
Ik wil graag in een draaitabel een extra "berekend veld" toevoegen:
https://imgur.com/a/jLWNuBQ
Door simpelweg de "som van Bestedingen / Gemiddelde van budget" te doen. Kan iemand mij helpen deze formule toe te passen?
Of gebruik de optie die Excel heeft: https://support.office.co(...)5c-a5c6-b0185e59da77
Corona-dit: 12052020:_file1_ qty 54 amnt_2
Corona-deze: 12052020:_file4,_ g qty 83 amnt_92
Corona-dit: 05052020:_file9_; f qty 51 amnt_2
Het gaat mij om een aantal gegevens uit het onderwerp die ik in aparte kolommen wil hebben. De meeste krijg ik er wel uit via DEEL en VINDSPEC omdat die elke keer hetzelfde aantal tekens na een bepaald woord komen (bv 54 komt precies 2 tekens na qty en na amnt_ volgt altijd een getal wat ik ook nodig heb)
Mijn probleem zit in de datum. Die zit aan t begin in de zin, maar niet in elke regel na hetzelfde aantal tekens. Soms pas na 12 tekens en soms na 11 al. Ook lastig is, is dat 5 mei 5052020 wordt en 12 mei 12052020.
Hoe kan ik zo'n formule maken die exact de datum eruit trekt?
De vaste variabele is dat elke datum sowieso 2020 bevat en 2 cijfers van de maand ervoor. Dus wellicht kan ik t vinden door vanaf rechts te zoeken vanaf waar 2020 begint en dan tot er een spatie is?
En als ik dat dan doe...hoe zorg ik dat Excel t ook herkent als datum? Ik zou t via LINKS, DEEL en RECHTS wel in aparte kolommen kunnen krijgen, en daarna via &"-"& ook wel als 12-05-2020 maar dat ziet Excel niet als datum...
[ Bericht 2% gewijzigd door kalinhos op 12-05-2020 23:01:10 ]
Met =DATUM(j;m;d) kun je drie getallen tot een datum maken. Via zoeken op de eerste en tweede dubbele punt zul je wel kunnen achterhalen of de datum uit 7 of 8 karakters bestaat, denk ik.quote:
Ik wil een bepaald gedeelte uit een zin trekken en in aparte kolommen zetten. Vanuit Outlook kopieer ik 30 mails en die zet ik in excel. Ik krijg dan zoiets te zien als onderwerp (30 regels):
Corona-dit: 12052020:_file1_ qty 54 amnt_2
Corona-deze: 12052020:_file4,_ g qty 83 amnt_92
Corona-dit: 05052020:_file9_; f qty 51 amnt_2
Het gaat mij om een aantal gegevens uit het onderwerp die ik in aparte kolommen wil hebben. De meeste krijg ik er wel uit via DEEL en VINDSPEC omdat die elke keer hetzelfde aantal tekens na een bepaald woord komen (bv 54 komt precies 2 tekens na qty en na amnt_ volgt altijd een getal wat ik ook nodig heb)
Mijn probleem zit in de datum. Die zit aan t begin in de zin, maar niet in elke regel na hetzelfde aantal tekens. Soms pas na 12 tekens en soms na 11 al. Ook lastig is, is dat 5 mei 5052020 wordt en 12 mei 12052020.
Hoe kan ik zo'n formule maken die exact de datum eruit trekt?
En als ik dat dan doe...hoe zorg ik dat Excel t ook herkent als datum? Ik zou t via LINKS, DEEL en RECHTS wel in aparte kolommen kunnen krijgen, en daarna via &"-"& ook wel als 12-05-2020 maar dat ziet Excel niet als datum...
Gebruik Power Query.quote:
Ik wil een bepaald gedeelte uit een zin trekken en in aparte kolommen zetten. Vanuit Outlook kopieer ik 30 mails en die zet ik in excel. Ik krijg dan zoiets te zien als onderwerp (30 regels):
Corona-dit: 12052020:_file1_ qty 54 amnt_2
Corona-deze: 12052020:_file4,_ g qty 83 amnt_92
Corona-dit: 05052020:_file9_; f qty 51 amnt_2
Het gaat mij om een aantal gegevens uit het onderwerp die ik in aparte kolommen wil hebben. De meeste krijg ik er wel uit via DEEL en VINDSPEC omdat die elke keer hetzelfde aantal tekens na een bepaald woord komen (bv 54 komt precies 2 tekens na qty en na amnt_ volgt altijd een getal wat ik ook nodig heb)
Mijn probleem zit in de datum. Die zit aan t begin in de zin, maar niet in elke regel na hetzelfde aantal tekens. Soms pas na 12 tekens en soms na 11 al. Ook lastig is, is dat 5 mei 5052020 wordt en 12 mei 12052020.
Hoe kan ik zo'n formule maken die exact de datum eruit trekt?
De vaste variabele is dat elke datum sowieso 2020 bevat en 2 cijfers van de maand ervoor. Dus wellicht kan ik t vinden door vanaf rechts te zoeken vanaf waar 2020 begint en dan tot er een spatie is?
En als ik dat dan doe...hoe zorg ik dat Excel t ook herkent als datum? Ik zou t via LINKS, DEEL en RECHTS wel in aparte kolommen kunnen krijgen, en daarna via &"-"& ook wel als 12-05-2020 maar dat ziet Excel niet als datum...
Ik heb nu een tabel met facturen en creditnota's per klant. Die heb ik in een draaitabel gezet zodat de omzet per klant te zien is maar ik wil nu de negatieve getallen (de creditnota's dus) eruit halen. Ik heb al geprobeerd met waardefilter groter dan 0 maar dat heeft geen succes. Iemand enig idee?
1x in het veld voor de "waarde" en 1x in het veld voor de rapportage filter. In je rapportage filter kan je makkelijk de waarden selecteren die je niet wilt tonen.
Het kan overigens ook wel met 1x, maar via de rapportage filter is het wel zo netjes.
Wat niet werkt is filteren in de originele lijst waar de data in staat waaroverheen de draaitabel gemaakt is.
In het menu van het filteren zit de optie "Waardefilter". Via het waardefilter kan je invullen groter dan 0quote:
Dat had ik ook geprobeerd. Maar als je dan wilt filteren in de rapportage filter kan dat alleen met het uit en aanvinken van de waardes toch? En dat zijn er dan weer net teveel. Dan wil ik eigenlijk alles onder 0 eruit filteren.
Een andere makkelijke oplossing is dan om in de originele tabel een extra hulpkolom te maken. Bijvoorbeeld via de formule =ALS(Bedrag<0;"negatief";"positief")
Vervolgens rechter muistoets de draaitabel bijwerken zodat je die extra kolom ook kan gebruiken om te filteren.
Waar vind ik precies die optie waardefilter? Want dat zoek ik dan.quote:
[..]
In het menu van het filteren zit de optie "Waardefilter". Via het waardefilter kan je invullen groter dan 0
Een andere makkelijke oplossing is dan om in de originele tabel een extra hulpkolom te maken. Bijvoorbeeld via de formule =ALS(Bedrag<0;"negatief";"positief")
Vervolgens rechter muistoets de draaitabel bijwerken zodat je die extra kolom ook kan gebruiken om te filteren.
Ja in de tabel kan ik ook gaan wijzigen. Maar het is niet voor mezelf maar voor een collega die weer minder van excel weet dus wil ik dat zo makkelijk mogelijk klaar zetten voor hem.
quote:
Waar vind ik precies die optie waardefilter? Want dat zoek ik dan.
Maar dat gaat niet werken omdat die alleen filtert op het resultaat van de draaitabel en niet op de onderliggende waarden. Met een meting in Power Pivot kun je dat wel doen.
Ik gebruik de Nederlandstalige versie van Excel (Office) 2013 en ben op zoek naar een functie / formule om een probleem op te lossen.
Wat ik probeer af te vangen is de afschrijving van een auto. Over afschrijving is op zich voldoende te vinden, maar dat heeft voor zover ik kan zien allemaal een ander uitgangspunt dan ik wil gebruiken.
Waar het voor mij om gaat is dat ik de afschrijving in een maand per kilometer kan berekenen. Het knelpunt daarbij is dat het bedrag per kilometer lager is naarmate het aantal gemaakte kilometers hoger is.
Voorbeeld:
Bij 2300 km in een maand is de afschrijving ongeveer ¤0,06 per km, ¤138 totaal.
Bij 200 km in een maand is de afschrijving ¤0,45 per km, ¤90 totaal.
Ik kan dit gedeeltelijk opvangen door een vast en variabel deel per maand te gebruiken, maar het knelpunt blijft bestaan, wordt alleen wat gedempt hiermee.
Is er een functie of formule die ik hiervoor kan gebruiken / misbruiken? De enige optie die ik zelf tot nog toe heb kunnen bedenken is een gelaagde ALS functie of een tabel. Beiden hebben hetzelfde effect, maar ik hoop dat iemand een mooiere oplossing weet.
Leasemaatschappijen maken hier hele tabellen voor, in combinatie met kilometers en looptijd/leeftijd. Schat dus in hoeveel KM je per jaar (maand) maakt, welke kosten wanneer komen (distrubutieriem na 90k, banden na 40k, etc) en schat wat de restwaarde is na x jaar en y kilometers.quote:
Goedemorgen
Ik gebruik de Nederlandstalige versie van Excel (Office) 2013 en ben op zoek naar een functie / formule om een probleem op te lossen.
Wat ik probeer af te vangen is de afschrijving van een auto. Over afschrijving is op zich voldoende te vinden, maar dat heeft voor zover ik kan zien allemaal een ander uitgangspunt dan ik wil gebruiken.
Waar het voor mij om gaat is dat ik de afschrijving in een maand per kilometer kan berekenen. Het knelpunt daarbij is dat het bedrag per kilometer lager is naarmate het aantal gemaakte kilometers hoger is.
Voorbeeld:
Bij 2300 km in een maand is de afschrijving ongeveer ¤0,06 per km, ¤138 totaal.
Bij 200 km in een maand is de afschrijving ¤0,45 per km, ¤90 totaal.
Ik kan dit gedeeltelijk opvangen door een vast en variabel deel per maand te gebruiken, maar het knelpunt blijft bestaan, wordt alleen wat gedempt hiermee.
Is er een functie of formule die ik hiervoor kan gebruiken / misbruiken? De enige optie die ik zelf tot nog toe heb kunnen bedenken is een gelaagde ALS functie of een tabel. Beiden hebben hetzelfde effect, maar ik hoop dat iemand een mooiere oplossing weet.
En doe dit dan voor de verschillende combinaties van looptijd en kilometers...
Dat zit allemaal al in mijn overzicht. Maar die afschrijving wil ik exacter benaderen, op maandbasis. En de maandkilometers fluctueren nogal, zeker nu in Coronatijd.quote:
[..]
Leasemaatschappijen maken hier hele tabellen voor, in combinatie met kilometers en looptijd/leeftijd. Schat dus in hoeveel KM je per jaar (maand) maakt, welke kosten wanneer komen (distrubutieriem na 90k, banden na 40k, etc) en schat wat de restwaarde is na x jaar en y kilometers.
En doe dit dan voor de verschillende combinaties van looptijd en kilometers...
Op jaarbasis is het iets makkelijker, maar dan schiet ik mijn doel voorbij.
Heb het voor nu even opgelost door het vaste bedrag per maand vrij hoog te zetten, daarmee benader ik de realiteit aardig. Maar een oplossing is nog steeds welkom.
Of heb je vaste kosten en een variabel deel per km?
Ik weet niet helemaal zeker of ik je vraag goed begrijp.quote:
Zijn de kosten per kilometer anders per km of zitten ze in ranges?
Of heb je vaste kosten en een variabel deel per km?
De afschrijvingskosten per kilometer zijn anders wanneer het aantal gereden kilometers per maand anders is. Hoe meer kilometers, hoe lager de afschrijving per kilometer. Ranges is een mogelijkheid (en relatief makkelijk af te vangen), maar ik wilde eigenlijk een berekening / formule.
Er is een vast deel per maand en een variabel deel per kilometer inderdaad. Wanneer de auto een maand stilstaat dan schrijft deze toch af.
Ja, maar door het variabel per maand of zelfs per week te maken dan schiet je niets op. Straks is een bezoekje aan de supermarkt om de hoek 'duurder' dan een beoek aan de schoonouders, puur omdat de afschrijving per maand hoog is. Zo krijg je imho geen eerlijke kosten in je overzicht.quote:
[..]
Dat zit allemaal al in mijn overzicht. Maar die afschrijving wil ik exacter benaderen, op maandbasis. En de maandkilometers fluctueren nogal, zeker nu in Coronatijd.
Op jaarbasis is het iets makkelijker, maar dan schiet ik mijn doel voorbij.
Heb het voor nu even opgelost door het vaste bedrag per maand vrij hoog te zetten, daarmee benader ik de realiteit aardig. Maar een oplossing is nog steeds welkom.
Ik zou het op jaarbasis uitrekenen met verschillende kilometers en 'looptijd', en dat terugrekenen per maand. En dan uitgaan van degene met de hoogste kosten, zo hou je onbewust geld over.
Vaste kosten ¤ xxxx,xx
Variabel per km ¤ xx,xx
Aantal km's
Vast delen door km's , variabel maal km's, en je hebt je bedrag.
Dat lees je weer in in je berekening voor de maand.
Dat is wat ik nu gebruik, ongeveer. Het variabele deel is het "probleem", dat is per km lager naarmate het aantal kilometers hoger is.quote:
Kan je niet een tweede tabblad aanmaken.
Vaste kosten ¤ xxxx,xx
Variabel per km ¤ xx,xx
Aantal km's
Vast delen door km's , variabel maal km's, en je hebt je bedrag.
Dat lees je weer in in je berekening voor de maand.
Ik probeer het overzicht zo zuiver mogelijk te krijgen. Doe het al een aantal jaar en dat werkt best goed. Aan het eind van elke maand is alles conform de realiteit, voor zover dat mogelijk is natuurlijk. Ook de afschrijving is dan vrij goed vast te stellen. Maar dat is achteruit kijken en ik wil ook (beter) vooruit kijken en calculeren.quote:
[..]
Ja, maar door het variabel per maand of zelfs per week te maken dan schiet je niets op. Straks is een bezoekje aan de supermarkt om de hoek 'duurder' dan een beoek aan de schoonouders, puur omdat de afschrijving per maand hoog is. Zo krijg je imho geen eerlijke kosten in je overzicht.
Ik zou het op jaarbasis uitrekenen met verschillende kilometers en 'looptijd', en dat terugrekenen per maand. En dan uitgaan van degene met de hoogste kosten, zo hou je onbewust geld over.
De jaarvariant, daar probeer ik nu juist vanaf te komen. Die is relatief makkelijk uit te rekenen, maar het nadeel daarvan is dat afschrijving lager is naarmate je een auto langer hebt. Ik ken mezelf, neem me voor 6 jaar met een auto te doen en na 1,5 jaar komt er toch weer een andere. Met de lange termijn afschrijving kloppen de bedragen dan bepaald niet meer.
In mijn calculatie probeer ik daarom - vooruitkijkend - maandelijks te bepalen wat de actuele waarde aan het eind van elke maand is.
Wanneer je bijvoorbeeld kijkt op de ANWB site, dan krijg je een aardig beeld van de kostenprojectie op de lange termijn, zeker wanneer je verschillende kilometrages invoert. De opstelling die ze geven is verder vrij matig, maar dat deel is wel interessant. Daar zal een tabel achter zitten, of misschien een logaritme. Dat laatste wil ik ook graag kunnen.
Onafhankelijk van het aantal kilometers, schrijft een auto het eerste jaar (relatief) harder af dan in jaar vijf. Gooi je daar dan ook nog de variabele afschrijving per kilometer bij, vraag ik mij af hoe je dit zo zuiver mogelijk wilt krijgen. En dan hebben we het nog niet over de (dagelijks) veranderende markt(prijzen) bij inruil/verkoop.quote:
[..]
Ik probeer het overzicht zo zuiver mogelijk te krijgen. Doe het al een aantal jaar en dat werkt best goed. Aan het eind van elke maand is alles conform de realiteit, voor zover dat mogelijk is natuurlijk. Ook de afschrijving is dan vrij goed vast te stellen. Maar dat is achteruit kijken en ik wil ook (beter) vooruit kijken en calculeren.
Ik neem aan dat je in je variabele kilometerprijs ook hebt opgenomen of de kilometers op snelwegen, 80 km/h wegen of in de stad gereden worden. Dit heeft natuurlijk ook een grote invloed op de variabele brandstof.
Wat je wilt kan natuurlijk gebouwd worden maar dan moet je wel alle variabelen registreren en meenemen.
Samengevat: wil je niet teveel in detail? Zet het echt zoden aan de dijk al je naar het totaal van de kosten kijkt?
Kan je iets meer vertellen over die datapunten die je noemt. Zijn dat grenswaarden zodat 2290km nog steeds met ¤0,45 gaat, of is dat volledig vloeiend en probeer je de achterliggende formule te ontdekken?quote:
Goedemorgen
Ik gebruik de Nederlandstalige versie van Excel (Office) 2013 en ben op zoek naar een functie / formule om een probleem op te lossen.
Wat ik probeer af te vangen is de afschrijving van een auto. Over afschrijving is op zich voldoende te vinden, maar dat heeft voor zover ik kan zien allemaal een ander uitgangspunt dan ik wil gebruiken.
Waar het voor mij om gaat is dat ik de afschrijving in een maand per kilometer kan berekenen. Het knelpunt daarbij is dat het bedrag per kilometer lager is naarmate het aantal gemaakte kilometers hoger is.
Voorbeeld:
Bij 2300 km in een maand is de afschrijving ongeveer ¤0,06 per km, ¤138 totaal.
Bij 200 km in een maand is de afschrijving ¤0,45 per km, ¤90 totaal.
Ik kan dit gedeeltelijk opvangen door een vast en variabel deel per maand te gebruiken, maar het knelpunt blijft bestaan, wordt alleen wat gedempt hiermee.
Is er een functie of formule die ik hiervoor kan gebruiken / misbruiken? De enige optie die ik zelf tot nog toe heb kunnen bedenken is een gelaagde ALS functie of een tabel. Beiden hebben hetzelfde effect, maar ik hoop dat iemand een mooiere oplossing weet.
Als je er een mooie vloeiende functie van wil maken, hebben we meer datapunten nodig. Als het werkt op basis van grenswaarden is een tabel de beste optie inderdaad.
2290×0.45= 1000 euro afschrijven in die maand, en 10 kilometer meer rijden opeens nog maar 138.quote:
Als je er een mooie vloeiende functie van wil maken, hebben we meer datapunten nodig. Als het werkt op basis van grenswaarden is een tabel de beste optie inderdaad.
Met die 2 data punten kun je er toch wel een lineare functie van maken?
Klopt allemaal. De auto is niet nieuw gekocht, dat scheelt. Maar het effect blijft. Heb dat een tijdje opgelost door een percentage van de waarde van de voorgaande maand te nemen. Dat helpt maar is niet dé oplossing.quote:
[..]
Onafhankelijk van het aantal kilometers, schrijft een auto het eerste jaar (relatief) harder af dan in jaar vijf. Gooi je daar dan ook nog de variabele afschrijving per kilometer bij, vraag ik mij af hoe je dit zo zuiver mogelijk wilt krijgen. En dan hebben we het nog niet over de (dagelijks) veranderende markt(prijzen) bij inruil/verkoop.
Echt zuiver zal me nooit lukken, maar ik probeer het zo dicht mogelijk te benaderen.
Het verbruik hou ik uiteraard bij en dat is vrij stabiel. Ik reken met het gemiddelde en stel dat na elke tankbeurt bij. Na de eerste paar maanden worden de veranderingen erg klein.quote:Ik neem aan dat je in je variabele kilometerprijs ook hebt opgenomen of de kilometers op snelwegen, 80 km/h wegen of in de stad gereden worden. Dit heeft natuurlijk ook een grote invloed op de variabele brandstof.
Natuurlijk wil ik teveel in detail. Ik heb hier lol in en probeer steeds dichter bij de realiteit te komen. De onderliggende reden is deels ook gewoon serieus; ik heb de afgelopen jaren veel te veel geld in het kopen van auto's gestopt en dat hou ik hiermee onder controle.quote:Wat je wilt kan natuurlijk gebouwd worden maar dan moet je wel alle variabelen registreren en meenemen.
Samengevat: wil je niet teveel in detail? Zet het echt zoden aan de dijk al je naar het totaal van de kosten kijkt?
Precies mijn punt waarom ik het vraag met een idioot voorbeeld.quote:
[..]
2290×0.45= 1000 euro afschrijven in die maand, en 10 kilometer meer rijden opeens nog maar 138.
Ja, ook een logaritmische lukt me prima. Met 1 punt wordt het nog makkelijker.quote:Met die 2 data punten kun je er toch wel een lineare functie van maken?
quote:
[..]
Kan je iets meer vertellen over die datapunten die je noemt. Zijn dat grenswaarden zodat 2290km nog steeds met ¤0,45 gaat, of is dat volledig vloeiend en probeer je de achterliggende formule te ontdekken?
Als je er een mooie vloeiende functie van wil maken, hebben we meer datapunten nodig. Als het werkt op basis van grenswaarden is een tabel de beste optie inderdaad.
Hier zit denk ik wel de essentie van wat ik zoek. De 2 datapunten die ik noem zijn echt, maar in de tussenliggende maanden heb ik helaas niet de waarde opgevraagd. Het is niet lineair maar een kromme.quote:
[..]
2290×0.45= 1000 euro afschrijven in die maand, en 10 kilometer meer rijden opeens nog maar 138.
Met die 2 data punten kun je er toch wel een lineare functie van maken?
Hoe maak ik daar - wanneer ik meer datapunten heb - een formule van?
De waarde opgevraagd? Waar en hoe, waarom denk je dat die waarde altijd helemaal realistisch is als je deze opvraagt?quote:
[..]
[..]
Hier zit denk ik wel de essentie van wat ik zoek. De 2 datapunten die ik noem zijn echt, maar in de tussenliggende maanden heb ik helaas niet de waarde opgevraagd. Het is niet lineair maar een kromme.
Hoe maak ik daar - wanneer ik meer datapunten heb - een formule van?
Ik gebruik de site van Autotelex daarvoor. Het blijven schattingen, maar in mijn ervaring zitten zij er dichtbij. Bij de vorige 2 auto's die ik ingeruild heb klopte de inruilwaarde binnen een marge van 150¤.quote:
[..]
De waarde opgevraagd? Waar en hoe, waarom denk je dat die waarde altijd helemaal realistisch is als je deze opvraagt?
Wat Basp1 al zegt, je kan er heel makkelijk een lineaire functie van kan maken. Wat ManAtWork al terecht opmerkt is dat er veel meer variabelen zullen zijn en die formule niet simpel lineair zal zijn.quote:
[..]
[..]
Hier zit denk ik wel de essentie van wat ik zoek. De 2 datapunten die ik noem zijn echt, maar in de tussenliggende maanden heb ik helaas niet de waarde opgevraagd. Het is niet lineair maar een kromme.
Hoe maak ik daar - wanneer ik meer datapunten heb - een formule van?
Stel het zou een lineaire functie zijn. Dan zou de formule zijn:
y = ax + b
a = delta y / delta x = 48 / 2100 = 0,022857
1 datapunt invullen
b = 90 - 0,022857 * 200 = 85,42857
Dus je simpele formule zou zijn:
afschrijving = 0,022857 * aantal km + 85,42857
Alleen iedereen weet dat het BS is, want het is geen rechte lijn. Zonder dat je meer datapunten hebt kan je de werkelijke voorloop van die lijn niet voorspellen.
Maar in die schattingen op dit moment zitten natuurlijk ook de economische omstandigheden verdisconteerd, als jij deze op voorhand kan voorspellen zou je je met andere dingen als de afschrijving van je auto moeten gaan bezig houden.quote:
[..]
Ik gebruik de site van Autotelex daarvoor. Het blijven schattingen, maar in mijn ervaring zitten zij er dichtbij. Bij de vorige 2 auto's die ik ingeruild heb klopte de inruilwaarde binnen een marge van 150¤.
Precies, een grote tabel met kilometers en leeftijd en die kruist precies met wat jij invult. De berekeningen die achter die waardes in de tabel zitten zijn gebaseerd op o.a. verkoopcijfers nieuw van vergelijkbare voertuigen in dezelfde periode als de jouwe, het onderhoudsboekje van de fabrikant, bekende prijzen van onderhoud (met of zonder kortingen), veranderingen in de markt voor verkoop van gebruikte voertuigen (groot effect op je restwaarde), etc, etc.quote:
Daar zal een tabel achter zitten, of misschien een logaritme. Dat laatste wil ik ook graag kunnen.
Een shitload aan datapunten dus, die jou uiteindelijk de kosten per km geven.
Als jij in een maand alleen maar 1x naar de supermarkt zou rijden dan is dat ritje in jouw berekening extreem duur per km (verzekering, wegenbelasting, afschrijving, en een beetje brandstof en banden), dan kan je beter met 110km/u naar Zuid-Frankrijk rijden en tanken langs de snelweg.. Door het per jaar te berekenen kom je juist op realistisere waardes, over de lange termijn.
Ik gebruik op dit moment ¤85 voor het vaste deel en ¤0,022 voor het variabele deel en dat zijn inderdaad waarden die waarschijnlijk alleen nu kloppen.quote:
[..]
Wat Basp1 al zegt, je kan er heel makkelijk een lineaire functie van kan maken. Wat ManAtWork al terecht opmerkt is dat er veel meer variabelen zullen zijn en die formule niet simpel lineair zal zijn.
Stel het zou een lineaire functie zijn. Dan zou de formule zijn:
y = ax + b
a = delta y / delta x = 48 / 2100 = 0,022857
1 datapunt invullen
b = 90 - 0,022857 * 200 = 85,42857
Dus je simpele formule zou zijn:
afschrijving = 0,022857 * aantal km + 85,42857
Alleen iedereen weet dat het BS is, want het is geen rechte lijn. Zonder dat je meer datapunten hebt kan je de werkelijke voorloop van die lijn niet voorspellen.
Maar hoe reken je het uit wanneer er meer data is? Kan je daar ook zo'n opstelling voor maken? Daarmee kom ik steeds dichterbij mijn doel.
Nee, dat kan ik niet. De bijstellingen die ze daar soms doen die kan ik onmogelijk voorspellen. Maar dat hoeft ook niet.quote:
[..]
Maar in die schattingen op dit moment zitten natuurlijk ook de economische omstandigheden verdisconteerd, als jij deze op voorhand kan voorspellen zou je je met andere dingen als de afschrijving van je auto moeten gaan bezig houden.
Ik wil de schattingen graag kunnen benaderen met de huidige informatie. Dat dat af en toe bijgesteld moet worden hoort daarbij.
Eén van mijn voorgaande auto's werd ineens "meer" waard (de inruilwaarde in april was hoger dan die in maart). Dat betrof een hybride en die lagen steeds beter in de markt waardoor de waarde omhoog bijgesteld werd. Interessante materie.
Ik moet echt een heel verhaal gaan schrijven om uit te leggen wat ik doe, jij hebt daar zo te lezen een ander beeld bij.quote:
[..]
Precies, een grote tabel met kilometers en leeftijd en die kruist precies met wat jij invult. De berekeningen die achter die waardes in de tabel zitten zijn gebaseerd op o.a. verkoopcijfers nieuw van vergelijkbare voertuigen in dezelfde periode als de jouwe, het onderhoudsboekje van de fabrikant, bekende prijzen van onderhoud (met of zonder kortingen), veranderingen in de markt voor verkoop van gebruikte voertuigen (groot effect op je restwaarde), etc, etc.
Een shitload aan datapunten dus, die jou uiteindelijk de kosten per km geven.
Als jij in een maand alleen maar 1x naar de supermarkt zou rijden dan is dat ritje in jouw berekening extreem duur per km (verzekering, wegenbelasting, afschrijving, en een beetje brandstof en banden), dan kan je beter met 110km/u naar Zuid-Frankrijk rijden en tanken langs de snelweg.. Door het per jaar te berekenen kom je juist op realistisere waardes, over de lange termijn.
In het kort: het ís een meerjarenplanning, maar ik probeer het effect van de maandelijkse kilometers in kaart te brengen. Voor onderhoud / reparatie / brandstof is dat prima te doen, voor afschrijving is het lastiger.
Voorheen was dit geen punt, ik maakte elke maand best veel kilometers. In een rustige maand 2000 en in een drukke tot 3500. De effecten daarvan zijn veel makkelijker te voorspellen dan de lage waarden van het afgelopen half jaar.
Gokken.quote:
[..]
Ik gebruik op dit moment ¤85 voor het vaste deel en ¤0,022 voor het variabele deel en dat zijn inderdaad waarden die waarschijnlijk alleen nu kloppen.
Maar hoe reken je het uit wanneer er meer data is? Kan je daar ook zo'n opstelling voor maken? Daarmee kom ik steeds dichterbij mijn doel.
Met meer datapunten zou ik een grafiek maken om te zien hoe de lijn loopt. Op basis van ervaring weet ik welk type formule welk type lijn oplevert. Vervolgens dat type uitrekenen en dan die schatting gebruiken.
Voor mijn gevoel proberen een aantal mensen aan te geven dat de werkelijkheid complex is. Dat snap je zelf ook wel. Een iets beter onderbouwde schatting kan je prima doorvoeren op de gestelde manier.
Even puntjes op de i zetten wiskundig gezien is een lijn altijd een rechte tussen 2 punten dus liniear, het zal hoe dan ook een curve zijn waar deze vraagsteller naar toe op zoek is.quote:
[..]
Gokken.
Met meer datapunten zou ik een grafiek maken om te zien hoe de lijn loopt. Op basis van ervaring weet ik welk type formule welk type lijn oplevert. Vervolgens dat type uitrekenen en dan die schatting gebruiken.
Voor mijn gevoel proberen een aantal mensen aan te geven dat de werkelijkheid complex is. Dat snap je zelf ook wel. Een iets beter onderbouwde schatting kan je prima doorvoeren op de gestelde manier.
Kan de vraagsteller vanuit autotelex geen curve genereren door meer data punten te creëren met verschillende kilometers per maand in te vullen?
Nou ja, dat was wat ik hier probeerde te vinden.quote:
[..]
Gokken.
Met meer datapunten zou ik een grafiek maken om te zien hoe de lijn loopt. Op basis van ervaring weet ik welk type formule welk type lijn oplevert. Vervolgens dat type uitrekenen en dan die schatting gebruiken.
Voor mijn gevoel proberen een aantal mensen aan te geven dat de werkelijkheid complex is. Dat snap je zelf ook wel. Een iets beter onderbouwde schatting kan je prima doorvoeren op de gestelde manier.
Een kromme / curve zoek ik inderdaad.quote:
[..]
Even puntjes op de i zetten wiskundig gezien is een lijn altijd een rechte tussen 2 punten dus liniear, het zal hoe dan ook een curve zijn waar deze vraagsteller naar toe op zoek is.
Kan de vraagsteller vanuit autotelex geen curve genereren door meer data punten te creëren met verschillende kilometers per maand in te vullen?
Dat met die datapunten heb ik geprobeerd, maar dat vindt Autotelex niet zo leuk. Na een aantal pogingen word je geblokkeerd.
Zou het natuurlijk wel over een aantal maanden kunnen proberen. Dat heb ik al eerder gedaan, maar die data heb ik niet bewaard (was bij een voorgaande auto). Probleem blijft daarbij dat ik er dan in formulevorm iets mee moet en ik niet weet hoe.
daar zal dan vast wel iemand je mee willen helpen hier las je wat meer data punten hebt.quote:
Probleem blijft daarbij dat ik er dan in formulevorm iets mee moet en ik niet weet hoe.
Kun je autotelex met je browser ook niet op anonieme tabbladen openen en daarmee de waardes die je wilt hebben vinden?
[img]hoe kan ik een voorwaardelijke opmaak instellen dat een cel een bepaalde kleur krijgt als het groter of kleiner is dan een andere celwaarde
voorbeeld: ik wil graag dat cel B2 groen wordt als ze kleiner is dan B1 en rood als ze groter is dan B1[/img]
opgelost. maar als ik dit naar beneden sleep wordt de voorwaardelijke op de oorspronkelijk cel gekopieerd en niet naar de cel ernaast
[ Bericht 21% gewijzigd door mrPanerai op 19-05-2020 11:15:01 ]
Kiezen voor "een formule gebruiken" als je de voorwaardelijke opmaak instelt.quote:
Snel vraagje
hoe kan ik een voorwaardelijke opmaak instellen dat een cel een bepaalde kleur krijgt als het groter of kleiner is dan een andere celwaarde
voorbeeld: ik wil graag dat cel B2 groen wordt als ze kleiner is dan B1 en rood als ze groter is dan B1
In tabblad 1 heb ik een naam staan, Harrie.
In tabblad 2 wil ik de naam van Harrie opzoeken tussen allerlei andere namen.
De naam van Harrie komt 3x voor op onderstaande data. Harrie heeft dus 75 euro uitgegeven in januari.
Harrie 15 januari , 10 euro
Harrie 20 januari . 15 euro
Harrie 25 januari, 50 euro
hoe kan ik op tabblad 1 weergeven hoeveel geld Harrie totaal heeft uitgegeven?
Sommen.alsquote:
Ik worstel met een excel vraagstuk. Ik hoop dat jullie me kunnen helpen.
In tabblad 1 heb ik een naam staan, Harrie.
In tabblad 2 wil ik de naam van Harrie opzoeken tussen allerlei andere namen.
De naam van Harrie komt 3x voor op onderstaande data. Harrie heeft dus 75 euro uitgegeven in januari.
Harrie 15 januari , 10 euro
Harrie 20 januari . 15 euro
Harrie 25 januari, 50 euro
hoe kan ik op tabblad 1 weergeven hoeveel geld Harrie totaal heeft uitgegeven?
Thanks makkerquote:
Staat er een dollarteken op de verkeerde plaats in je formule?quote:
Snel vraagje
[ [url=hoe kan ik een voorwaardelijke opmaak instellen dat een cel een bepaalde kleur krijgt als het groter of kleiner is dan een andere celwaarde
voorbeeld: ik wil graag dat cel B2 groen wordt als ze kleiner is dan B1 en rood als ze groter is dan B1]afbeelding[/url] ]
opgelost. maar als ik dit naar beneden sleep wordt de voorwaardelijke op de oorspronkelijk cel gekopieerd en niet naar de cel ernaast
dat weet ik niet, maar de doelcel blijft hetzelfde terwijl die mee naar beneden moet schuivenquote:
[..]
Staat er een dollarteken op de verkeerde plaats in je formule?
de tweede colom moet groen worden als het kleiner is dan de eerste kolom en rood indien groter dan de eerste kolom.
Als ik ze naar beneden sleep blijft de voorwaardelijke info in takt maar verwijst ze naar de startcel, in dit geval dus 112 terwijl ik wil dat ze vergelijkt met 32 bijvoorbeeld.
duidelijk?
Klinkt alsof er inderdaad een $ teveel in je fomrule staat. Volgens mij heb je 2 regels met bijbehorende opmaak nodig:quote:
[..]
dat weet ik niet, maar de doelcel blijft hetzelfde terwijl die mee naar beneden moet schuiven
[ afbeelding ]
de tweede colom moet groen worden als het kleiner is dan de eerste kolom en rood indien groter dan de eerste kolom.
Als ik ze naar beneden sleep blijft de voorwaardelijke info in takt maar verwijst ze naar de startcel, in dit geval dus 112 terwijl ik wil dat ze vergelijkt met 32 bijvoorbeeld.
duidelijk?
=$B1<$A1
=$B1>$A1
Zou het kunnen dat jij een $ hebt staan voor de rijnummers?
Heb je in het hoofdscherm van Voorwaardelijke opmaak wellicht de selectie staan op Huidige selectie ipv Dit werkblad?quote:
klopt maar als ik die aanpas dan zet hij ze zelf weer terug als ik op toepassen klik
Waarom als je veel berekening moet doen met formules is Excel toch veel makkelijker als in een database als acces te moeten gaan klooien.quote:
Zou het voor veel mensen die lopen te schutteren met excel niet veel beter zijn als ze zouden overstappen op MS Access!?
Omdat veel geklooi met formules eigenlijk het nabootsen van een database is.quote:
[..]
Waarom als je veel berekening moet doen met formules is Excel toch veel makkelijker als in een database als acces te moeten gaan klooien.
Zat mensen die van excel een offerte programma willen maken, of een financiele maandelijkse rapportage willen doen. En dan met moeilijke lookups data willen zoeken, wat eigenlijk een SQL query is.
Allemaal veel makkelijker in Access (en ook daar kan je formules toepassen). Ja, de drempel is wat hoger, maar met een goed boek kom je een heel eind. Als je dan ook nog iemand in de buurt hebt die je op weg kan helpen scheelt dat ook. Zat voorbeeld Access database te vinden waar je mee kan pielen en van kan leren.
Ik zit een beetje te prutsen in Excel en ik hoop dat jullie kunnen helpen: Ik wil graga een bestand waarin ik afgesproken betalingsregeling zet die op een bepaalde datum de SOLL positie van die regeling weergeeft. Als ik dan een openstaande postenlijst er achter plak moet die op klantniveau via vertikaal zoeken de IST er bij zoeken en vergelijken. Is het verschil nul, dan houd de klant zich aan de regeling en is het ok. Is er een negatief verschil dan moet ik actie ondernemen. Ik heb het volgende reeds bedacht:
In het baluw staan de gegevens van de vordering en de afgesproken regeling. In de groene kolom staan de verstreken maanden (peildatum minus startdatum regeling). De SOLL kolom is verstreken maanden * termijn bedrag.
Waar ik nu tegen aan loop is het SOLL bedrag. Er zijn voor deze regeling 5 maanden afgesproken en de SOLL zou dus 0 moeten zijn (open bedrag minus 5*termijn bedrag). Echter maak ik gebruik van de verstreken maanden en dat zijn er elf en de SOLL wordt negatief.
Mijn vraag is: Hoe zorg ik dat SOLL niet negatief wordt maar stopt bij nul als volgens de regeling alles betaald zou moeten zijn?
Merci
Hoe zou je die er in zetten dan, ik heb zelf namelijk gene idee hoe dat te doen.quote:
Extra voorwaarde (als functie dus) in de kolom maand die hem afkapt op het max aantal termijnen zou ik voor gaan.
Zoiets? : =ALS(g7-k7)=<0;"";G7-K7
waar:
G7 = aantal afgesproken betaaltermijn = 5
K7 = verstreken maanden sinds startdatum = 11
Dus zoiets als aantal verstreken maanden kleiner dan 0 is, pak dan 0?
Ik zou geen 0 gebruiken, je wil wel blijven zien hoeveel maanden er verstreken zijn, tot aan het maximale toe.
dat is waar, ik ben aan het prutsen, thanksquote:
als aantal verstreken maanden groter dan maanden betalingstermijn, dan maanden betalingstermijn, anders aantal verstreken maanden.
Ik zou geen 0 gebruiken, je wil wel blijven zien hoeveel maanden er verstreken zijn, tot aan het maximale toe.
Hoe zet ik dat in een formule, ik ben zeer zwak in als functies, al die haakjes, komma's etcquote:
als aantal verstreken maanden groter dan maanden betalingstermijn, dan maanden betalingstermijn, anders aantal verstreken maanden.
Ik zou geen 0 gebruiken, je wil wel blijven zien hoeveel maanden er verstreken zijn, tot aan het maximale toe.
Ik kom tot: =Als(DATUMVERSCHIL(H7;$B$3>G7);"G7"; DATUMVERSCHIL(H7;$B$3))
H7 = Startdatum regeling
B3 = Peildatum
G7 = Afgesproken termijnen
(lang leve excel) kan hem dus niet namaken zo. Maar ziet er redelijk logisch uit.Eerst reken ik het aantal verstreken maanden uit en vervolgen sin een kolom er na de als formule: als groter dan afgepsproken dan aantal afgepsproken. Met deze laatste reken ik nu.
Ooit ga ik ze misschien samenvoegen, voor nu is dit voldoende, dank @marcb1974
Gewoon die cellen van elkaar aftrekken, eventueel met absolute waarde er bij. Je kan met datums rekenen net als met getallen: dus + -quote:
Ik mijn excel versie bestaat datumverschil niet meer
Makkelijkste optie is de opmaak aanpassen en 'hr' weghalen, maar als je echt '3' als antwoord wilt hebben dan moet je in A2 de volgende fornule doen: A1*24, voor de minuten doe je A1*24*60 (en seconden A1*24*60*60, etc)quote:
Waarschijnlijk bijzonder simpel op te lossen; ik heb een kolom waarde uren in staan, maar in het formaat '3 hr'. Hoe converteer ik die naar een daadwerklijk nummer, dus 3 uur of 180 minuten? Kolommen hebben meestal een andere waarde,
quote:
Kan je niet gewoon de celopmaak aanpassen?
Het is een combinatie van letters en cijfers, dus ik denk dat het makkelijkste is om die 'H' eruit te kunnen slopen.quote:
[..]
maar als je echt '3' als antwoord wilt hebben dan moet je in A2 de volgende fornule doen: A1*24, voor de minuten doe je A1*24*60 (en seconden A1*24*60*60, etc)
Bedoel je handmatig?quote:Makkelijkste optie is de opmaak aanpassen en 'hr' weghalen,
Dan doe je dat. in je lint bij Start heb je Zoeken en selecteren.quote:
[..]
[..]
Het is een combinatie van letters en cijfers, dus ik denk dat het makkelijkste is om die 'H' eruit te kunnen slopen.
Daar neem je vervangen.
Je vervangt [spatie]H door niets
en dan heb je alleen het getal over.
Voor de veiligheid zou ik de kolom eerst een keer kopieren, dan kan het nog een keer fout gaan ofzo
Nee, natuurlijk niet handmatig. Je kunt die hele hr er in 1x uitslopen. Maar afhankelijk van alle andere tekst die erin staat wil je dat of wil je dat niet. Als je het eruit haalt, dan staat er dus alleen 3. Is dat wat je wil?quote:
[..]
[..]
Het is een combinatie van letters en cijfers, dus ik denk dat het makkelijkste is om die 'H' eruit te kunnen slopen.
[..]
Bedoel je handmatig?
Dan is deel() vaak makkelijker.quote:
Je kunt ook nog moeilijk doen met links(), rechts() en lengte().
Ook dan moet je vermoedelijk de andere drie formules ook gebruiken.quote:
Ik heb een excel sheet waarin ik gegevens uit een website ophaal, nou staat er ergens op die website een tekst "July 2020". Ik wil daarmee rekenen om de eerste zaterdag van die maand er uit te krijgen. Tot zover nog geen probleem (als ik mijn taalinstelling op Engels zou zetten). Nu is het probleem dat deze sheet door mijn internationale vriendengroep gebruikt gaat worden (die hun taalinstellingen in Windows allemaal anders hebben staan) .
Ik zoek dus eigenlijk een manier ok de tekst July 2020 om te zetten naar een datum, die dan weer weergegeven wordt in de taalinstelling van de gebruiker.
Ik wist dat het zo simpel zou zijn en over het hoofd zou zien.quote:
[..]
Dan doe je dat. in je lint bij Start heb je Zoeken en selecteren.
Daar neem je vervangen.
Je vervangt [spatie]H door niets
en dan heb je alleen het getal over.
Voor de veiligheid zou ik de kolom eerst een keer kopieren, dan kan het nog een keer fout gaan ofzo
Ik zat weer eens te moeilijk te denken...... Ik zet de tekst July met een if-statement om naar het maandnummer, en gebruik dat om de datum mee samen te stellenquote:
Ik heb een vrij specifieke vraag.....
Ik heb een excel sheet waarin ik gegevens uit een website ophaal, nou staat er ergens op die website een tekst "July 2020". Ik wil daarmee rekenen om de eerste zaterdag van die maand er uit te krijgen. Tot zover nog geen probleem (als ik mijn taalinstelling op Engels zou zetten). Nu is het probleem dat deze sheet door mijn internationale vriendengroep gebruikt gaat worden (die hun taalinstellingen in Windows allemaal anders hebben staan) .
Ik zoek dus eigenlijk een manier ok de tekst July 2020 om te zetten naar een datum, die dan weer weergegeven wordt in de taalinstelling van de gebruiker.
Als je de opmaak van de bron weet zou je het nog kunnen doen met een vertaaltabel en vert.zoeken()quote:
[..]
Ik zat weer eens te moeilijk te denken...... Ik zet de tekst July met een if-statement om naar het maandnummer, en gebruik dat om de datum mee samen te stellen
Probleem: Ik heb een lijst met 120.000 rijen, bestaande uit 4 kolommen. 1e kolom een url, 2e kolom een title, 3e kolom een url en 4e kolom een text.
Nu wil ik een zoek actie doen naar "Argentinië" en alle volledige rijen waar het woord Argentinie in voorkomt selecteren (dus met url, titel, url en text) en kopieeren naar een nieuw tabblad. Ehm... hoe
Duh,,,, FILTER jonguh
[ Bericht 7% gewijzigd door flipsen op 15-07-2020 16:34:31 ]
Als je alle rijen wilt overhouden waar in 1 of méér van de kolommen het zoekwoord voorkomt, werkt het standaard filter niet.quote:
Hm, dacht toch dat ik intussen wel aardig bedreven was in Excel, maar kom nu iets tegen wat ik zo 123 niet kan vinden/oplossen. Welke experts hier wel?
Probleem: Ik heb een lijst met 120.000 rijen, bestaande uit 4 kolommen. 1e kolom een url, 2e kolom een title, 3e kolom een url en 4e kolom een text.
Nu wil ik een zoek actie doen naar "Argentinië" en alle volledige rijen waar het woord Argentinie in voorkomt selecteren (dus met url, titel, url en text) en kopieeren naar een nieuw tabblad. Ehm... hoe
Duh,,,, FILTER jonguh
ik zit wat te prutsen met verschillende versies van Excel. In Excel365 krijg ik onderstaande goed voor elkaar. Ik wil de maximale waarde uit kolom B vinden op basis van de juiste maand en jaar.
Heb de formule nu als volgt:
| 1 | =MAX.ALS.VOORWAARDEN(B:B;A:A;">="&D2;A:A;"<="&LAATSTE.DAG(D2;0)) |
Ik zoek een formule die hetzelfde kan als bovenstaand, maar dan zonder de functie MAX.ALS.VOORWAARDEN, waarschijnlijk iets met SOMPRODUCT (om zonder matrixformules te werken) of MAX.ALS (met matrixformule).
Zonder matrixformule heeft de voorkeur.
Wie weet raad?
max.als gaat sowieso werken, maar werkt dus wel met arrays: https://exceljet.net/formula/max-value-in-given-monthquote:
Goedemiddag,
ik zit wat te prutsen met verschillende versies van Excel. In Excel365 krijg ik onderstaande goed voor elkaar. Ik wil de maximale waarde uit kolom B vinden op basis van de juiste maand en jaar.
Heb de formule nu als volgt:
[ code verwijderd ]
Er wordt echter ook gebruik gemaakt van Excel 2016 en Excel 2019, daar zit de functie MAX.ALS.VOORWAARDEN niet in
Ik zoek een formule die hetzelfde kan als bovenstaand, maar dan zonder de functie MAX.ALS.VOORWAARDEN, waarschijnlijk iets met SOMPRODUCT (om zonder matrixformules te werken) of MAX.ALS (met matrixformule).
Zonder matrixformule heeft de voorkeur.
Wie weet raad?
[ afbeelding ]
somproduct kan ook, maar dan krijg je hulpkolommen voor maand (en jaar) om alles goed te kunnen matchen: https://excelchamps.com/blog/max-if-formula/
Eerst de makkelijkste. De matrix oplossingquote:
Goedemiddag,
ik zit wat te prutsen met verschillende versies van Excel. In Excel365 krijg ik onderstaande goed voor elkaar. Ik wil de maximale waarde uit kolom B vinden op basis van de juiste maand en jaar.
Heb de formule nu als volgt:
[ code verwijderd ]
Er wordt echter ook gebruik gemaakt van Excel 2016 en Excel 2019, daar zit de functie MAX.ALS.VOORWAARDEN niet in
Ik zoek een formule die hetzelfde kan als bovenstaand, maar dan zonder de functie MAX.ALS.VOORWAARDEN, waarschijnlijk iets met SOMPRODUCT (om zonder matrixformules te werken) of MAX.ALS (met matrixformule).
Zonder matrixformule heeft de voorkeur.
Wie weet raad?
[ afbeelding ]
=MAX(ALS(A2:A9>=D2;ALS(A2:A9<=LAATSTE.DAG(D2;0);B2:B9)))
CTRL SHIFT ENTER gebruiken bij het invoeren van de formule.
Zonder matrix kan ik zo snel niet bedenken.
-edit-
Zonder matrix:
=SOMPRODUCT(MAX((A2:A9>=D2)*(A2:A9<=LAATSTE.DAG(D2;0))*(B2:B9)))
[ Bericht 1% gewijzigd door snabbi op 17-07-2020 21:13:45 ]
(Hoewel, gebruik liever INDEX MATCH
)Versie is Excel365, taal staat op Engels.
Casus: ik heb een sheet met 2 kolommen; in A staat een lijst met user id's, in B een lijst met codes.
User id's komen vaak meerdere keren voor, maar wel altijd met unieke codes (dezelfde combinatie komt dus nooit voor).
Dit idee:
| 1 2 3 4 5 6 7 | A B 12345 ABC123 12345 DEF456 54321 ABC123 54321 GHI789 98765 DEF456 98765 GHI789 |
Nu wil ik per code (kolom B) weten welke codes nog meer voorkomen bij die betreffende users.
Dus bijv. voor code 'ABC123' geldt dat die voorkomt bij 2 users en wil ik dus op kunnen halen dat die users ook codes 'DEF456' en 'GHI789' hebben.
Het lijkt me dat dit vrij eenvoudig moet kunnen, maar ik kom er niet uit hoe
Ik zat al te denken aan een kruistabel, waarbij ik alle codes tegen elkaar uitzet en per code na ga óf en zo ja hoe vaak die voorkomt in combinatie met elke andere code, maar daar kom ik ook niet echt uit
Hoop dat dit enigszins duidelijk is (maar kan me zomaar voorstellen van niet
)Ik heb een kolom waar staat jun, juli, aug,okt.
Deze wil ik allemaal tellen, dus dan gebruik je aantallen.als, maar ik wil geen 4 keer aantallen.als gebruiken terwijl je naar mijn idee ook zou moeten kunnen gebruiken =aantallen.als(bereik;EN("jun";"juli";"aug";"okt"))
Weten jullie waarom dit niet werkt?
Mijn oplossing verdient niet de schoonheidsprijs, maar ik denk dat je een eind komt als je in kolom B alle unieke waarden wegfilterd en/of dubbele waarden filters. Dan houd je links alle user id's over waar dan dubbele wachtwoorden staan.quote:
Oké, ik ben dus zo iemand als in de OP die zich heel wat voelt na VLOOKUP gebruikt te hebben
(Hoewel, gebruik liever INDEX MATCH
Versie is Excel365, taal staat op Engels.
Casus: ik heb een sheet met 2 kolommen; in A staat een lijst met user id's, in B een lijst met codes.
User id's komen vaak meerdere keren voor, maar wel altijd met unieke codes (dezelfde combinatie komt dus nooit voor).
Dit idee:
[ code verwijderd ]
Nu wil ik per code (kolom B) weten welke codes nog meer voorkomen bij die betreffende users.
Dus bijv. voor code 'ABC123' geldt dat die voorkomt bij 2 users en wil ik dus op kunnen halen dat die users ook codes 'DEF456' en 'GHI789' hebben.
Het lijkt me dat dit vrij eenvoudig moet kunnen, maar ik kom er niet uit hoe
Ik zat al te denken aan een kruistabel, waarbij ik alle codes tegen elkaar uitzet en per code na ga óf en zo ja hoe vaak die voorkomt in combinatie met elke andere code, maar daar kom ik ook niet echt uit
Hoop dat dit enigszins duidelijk is (maar kan me zomaar voorstellen van niet
SPOILERwaarom wil je dit weten
Oke, ik gaf dus expres niet te veel details, omdat ik dacht dat dat af zou leiden van de vraag. Maar blijkbaar word je dan meteen gezien als hackerquote:
[..]
Mijn oplossing verdient niet de schoonheidsprijs, maar ik denk dat je een eind komt als je in kolom B alle unieke waarden wegfilterd en/of dubbele waarden filters. Dan houd je links alle user id's over waar dan dubbele wachtwoorden staan.SPOILERwaarom wil je dit weten
Die 'codes' zijn vakcodes; het betreft een roosterprobleem
Ik wil weten hoe vaak combinaties van vakken voorkomen, zodat de roosteraar ervoor kan zorgen dat die vakken niet (uitsluitend) tegelijk geroosterd worden.
Maar ik denk dat ik zelf al iets bedacht heb!
Haha, was een mogelijkheid toch obv jouw vraagquote:
[..]
Oke, ik gaf dus expres niet te veel details, omdat ik dacht dat dat af zou leiden van de vraag. Maar blijkbaar word je dan meteen gezien als hacker
Die 'codes' zijn vakcodes; het betreft een roosterprobleem
Ik wil weten hoe vaak combinaties van vakken voorkomen, zodat de roosteraar ervoor kan zorgen dat die vakken niet (uitsluitend) tegelijk geroosterd worden.
Maar ik denk dat ik zelf al iets bedacht heb!
Maar ik snap je hoor, je kunt met excelvraagstukken beter recht-toe-recht-aan voorbeelden gebruiken dan een abstract verhaal uitleggen.
Wat is de oplossing?
Je hoeft toch maar een keer aantallen.als() te gebruiken?quote:
Ik heb een vraag:
Ik heb een kolom waar staat jun, juli, aug,okt.
Deze wil ik allemaal tellen, dus dan gebruik je aantallen.als, maar ik wil geen 4 keer aantallen.als gebruiken terwijl je naar mijn idee ook zou moeten kunnen gebruiken =aantallen.als(bereik;EN("jun";"juli";"aug";"okt"))
Weten jullie waarom dit niet werkt?
aantallen.als(bereik1;criterium1;bereik2;criterium2; etc …)
Waarbij bereik1 toevallig gelijk is aan bereik2, bereik3 en bereik4.
Edit: Nee dus, dan telt alleen als aan alle vier voorwaarden is voldaan, excuus.
[ Bericht 4% gewijzigd door Tegan op 22-07-2020 22:08:07 ]
EN gebruik je om meerdere voorwaarden te kunnen toetsen. Dus EN(dier=aap;naam=bokito). Jij wil het hier meer gebruiken als een soort bevindt zich in de volgende array.quote:
Ik heb een vraag:
Ik heb een kolom waar staat jun, juli, aug,okt.
Deze wil ik allemaal tellen, dus dan gebruik je aantallen.als, maar ik wil geen 4 keer aantallen.als gebruiken terwijl je naar mijn idee ook zou moeten kunnen gebruiken =aantallen.als(bereik;EN("jun";"juli";"aug";"okt"))
Weten jullie waarom dit niet werkt?
Dit kan je zelf bereiken door bijvoorbeeld zoiets te doen: =SOMPRODUCT(AANTAL.ALS(A:A;D:D))
Waarbij je dan in kolom D die jun, jul etc onder elkaar zet en ziet hoe vaak het voorkomt in kolom A
[ Bericht 1% gewijzigd door snabbi op 22-07-2020 22:21:16 ]
Mooi dat je zelf al een oplossing had.quote:
Oké, ik ben dus zo iemand als in de OP die zich heel wat voelt na VLOOKUP gebruikt te hebben
(Hoewel, gebruik liever INDEX MATCH
Versie is Excel365, taal staat op Engels.
Casus: ik heb een sheet met 2 kolommen; in A staat een lijst met user id's, in B een lijst met codes.
User id's komen vaak meerdere keren voor, maar wel altijd met unieke codes (dezelfde combinatie komt dus nooit voor).
Dit idee:
[ code verwijderd ]
Nu wil ik per code (kolom B) weten welke codes nog meer voorkomen bij die betreffende users.
Dus bijv. voor code 'ABC123' geldt dat die voorkomt bij 2 users en wil ik dus op kunnen halen dat die users ook codes 'DEF456' en 'GHI789' hebben.
Het lijkt me dat dit vrij eenvoudig moet kunnen, maar ik kom er niet uit hoe
Ik zat al te denken aan een kruistabel, waarbij ik alle codes tegen elkaar uitzet en per code na ga óf en zo ja hoe vaak die voorkomt in combinatie met elke andere code, maar daar kom ik ook niet echt uit
Hoop dat dit enigszins duidelijk is (maar kan me zomaar voorstellen van niet
De meest luie oplossing voor mij is altijd om er een draaitabel van te maken. Gooi je die user id in de kolom. De code in de value. Overal waar de waarde groter dan 1 is, heb je dus een userID met meerdere codes.
Als je snel wilt zien welke codes, dan gooi je die code ook in de draaitabel in de kolom. Dan krijg je namelijk per userID de bijbehorende codes.
Als je wilt kijken naar dubbele codes dan draai je de volgorde van de 2 om, dan zie je codes die aan meerdere usersIDs zijn toegekend.
som(aantallen.als(bereik;{criterium1;criterium2}))
Ik kan het nu even alleen in de Engelse Excel testen en daar werkt het.
Thanks, dit werkt wel!quote:
[..]
EN gebruik je om meerdere voorwaarden te kunnen toetsen. Dus EN(dier=aap;naam=bokito). Jij wil het hier meer gebruiken als een soort bevindt zich in de volgende array.
Dit kan je zelf bereiken door bijvoorbeeld zoiets te doen: =SOMPRODUCT(AANTAL.ALS(A:A;D:D))
Waarbij je dan in kolom D die jun, jul etc onder elkaar zet en ziet hoe vaak het voorkomt in kolom A
Vind'm alleen niet zo logisch, had echt verwacht dat met een geneste aantallen.als OF het zou moeten lukken.
[ Bericht 100% gewijzigd door phpmystyle op 22-07-2020 23:44:46 ]
Dit krijg ik niet werkend. Kan je een voorbeeld geven wat je hebt gebruikt (Engelse termen zijn ook prima).quote:
Google zegt dat dit ook kan:
som(aantallen.als(bereik;{criterium1;criterium2}))
Ik kan het nu even alleen in de Engelse Excel testen en daar werkt het.
-edit 1-
Ik zie het al. Je hebt de invoer in Excel als een Matrix formule gedaan via CTRL SHIFT ENTER.
Hierdoor stel je de CEL in staat een matrix als input te nemen.
Het is een beetje een educated guess aan mijn kant. Ik liet de techniek wat weg omdat het zeker niet voor iedereen geschikt is. Volgens mij ligt het aan de combinatie van wat er in 1 cel van Excel getoond kan worden (namelijk alleen één waarde) en de functie die je gebruikt. De functie moet in staat zijn de Matrix om te toveren naar een waarde. Een functie heeft zodoende een INPUT en een RESULTAAT.quote:
[..]
Thanks, dit werkt wel!
Vind'm alleen niet zo logisch, had echt verwacht dat met een geneste aantallen.als OF het zou moeten lukken.
Functies zoals SOMPRODUCT accepteren een Matrix als inputwaarde. Functies zoals SOM en AANTAL niet. Dit kan je ook zien wanneer je de functie invoert. Excel geeft aan welke inputwaarde deze verwacht. Bij SOMPRODUCT is dat "matrix1;matrix2" en bij de andere genoemde functie 'bereik'. Het bereik is slechts een 1 dimensionale vector. Dit zegt niets meer dan dat SOMPRODUCT in staat is om een matrix als 1 waarde als resultaat terug te geven, zodat die waarde in je cel getoond kan worden.
Nu zijn de functies onderliggend in excel 'slim' geprogrammeerd. Iedere functie geeft een 'Object' terug en niet een 'String', 'Integer', 'Array', of 'Array[][]' - waarbij de laatste 'Array[][]' of vierkantsarray dus eigenlijk de matrix is. Het feit dat ze dat ongedefineerde object teruggeven zorgt ervoor dat het probleem van het vertalen van dat 'Object' naar die verwachte inputwaarde wordt overgelaten aan de bovenliggende functie. Hierdoor doen zich de volgende 2 scenario's zich voor.
Scenario 1: Je plaatst in de CEL de formule AANTAL.ALS(bereik;bereik)
De combinatie van bereik;bereik is in feite een matrix. Deze matrix ziet er als volgt uit:
De functie verwerkt de matrix en de functie is niet in staat het resultaat terug te geven als een enkel getal. Daarom geeft de functie het resultaat van het Object terug.SPOILERArgument1,Argument2
A1,D1
A1,D2
A1,D3
..
A2,D1
A2,D2
A2,D3
...
Bij de verwerking van het resultaat van de functie als input op CEL niveau is de CEL niet in staat om het Object te verwerken naar een enkele waarde. , dus het resultaat van AANTAL.ALS wordt een matrix. Het resultaat wordt daardoor oneigenlijk getoond als 0.
Scenario 2: Je plaatst in de CEL de formule SOMPRODUCT -> met als subformule de aantal.als.
Wederom levert de subformule het resultaat van een Object dat gelijk is aan een Matrix aan de bovenliggende laag - namelijk de formule. De formule SOMPRODUCT kan wel een matrix als input verwerken en is in staat de berekening uit te voeren met die matrix. Hierbij kent SOMPRUDCT de waarde 1 toe aan een argument in de matrix wanneer A1 gelijk is aan D1, en de waarde 0 wanneer ze ongelijk zijn. De functie kan de SOM over deze hele array nemen en teruggeven als enkel getal aan de CEL. Nu wordt het getal getoond.
Helaas gaat het mij niet veel beter lukken om dit uit te leggen, omdat het deels aan de grenzen ligt van wat ik weet hoe het werkt.
Wel kan ik zeggen dat elke andere formule die je een input mogelijkheid geeft Matrix een zelfde type oplossing gaat geven.
-edit 2-
Overigens zie ik dat mijn bovenstaande verhaal niet 100% klopt. De formule KOLOMMEN(AANTAL.ALS(A:A;D:D)) geeft 1 als resultaat. Dit houdt eigenlijk in dat er niet een matrix onderliggend is met 2 argumenten, maar dat er een vector is met 1 argument... waarbij dat ene argument wéér een eigen vector is.
[ Bericht 3% gewijzigd door snabbi op 23-07-2020 07:45:39 ]
Had gekund jaquote:
[..]
Haha, was een mogelijkheid toch obv jouw vraag
Maar ik snap je hoor, je kunt met excelvraagstukken beter recht-toe-recht-aan voorbeelden gebruiken dan een abstract verhaal uitleggen.
Wat is de oplossing?
Ik heb nu twee tabellen aangemaakt. Eentje met de 'ruwe' data; user ID in kolom A en alle codes in de kolommen erachter, met 1/0 per vakcode (zodat je ook eenvoudig kunt filteren op combinaties).
En op basis daarvan een kruistabel, waarin je afleest hoeveel users een specifieke combinatie van codes hebben.
Dat leek al voldoende hulp te bieden voor degene die hier daadwerkelijk mee aan de slag gaat
Hoe "schrijf" ik een bestand vanuit een rij binaire codes.
Deze rij binaire codes kunnen in een excelblad onder elkaar staan, maar ook in een apart text-bestand.
Dat maakt op zich niet zoveel uit.
Iig er een bestand geschreven worden dat qua grootte dus ook precies evenveel bytes heeft dan het aantal getallen dat onder elkaar staat.
quote:
In VBA:
Hoe "schrijf" ik een bestand vanuit een rij binaire codes.
Deze rij binaire codes kunnen in een excelblad onder elkaar staan, maar ook in een apart text-bestand.
Dat maakt op zich niet zoveel uit.
Iig er een bestand geschreven worden dat qua grootte dus ook precies evenveel bytes heeft dan het aantal getallen dat onder elkaar staat.
Ja, ja, even een andere taal leren....quote:
Oh oh al dat gestuntel hier met Excel. Zodra VBA in het spel komt kun je in veel gevallen beter switchen naar R of Python.
Dat gaat 'm niet worden.
Ik kan redelijk mijn ding doen in VBA, en ik ben helemaal vergroeid met Excel.
Even voor de duidelijkheid:
Dit is het omgekeerde, nl een bestand presenteren als een rij binaire codes:
Sub z()
Dim k As Byte
Dim i As Long
Open "d:/meer.abc" For Binary As #8
Open "d:/meer.txt" For Output As #9
For i = 1 To 1000000
Get #8, , k
Print #9, k
Next i
Close #8
Close #9
End Sub
Een bestand, "d:/meer.abc" bestaat uit een aantal bytes.
En in meer.txt krijg je een lijst van nummers (van 0 t/m 255) van de binaire codes.
Het aantal bytes uit meer.abc is gelijk aan het aantal nummers in de rij van meer.txt.
Maar:
Bovenstaand programma'tje dus andersom...
Met PUTquote:
In VBA:
Hoe "schrijf" ik een bestand vanuit een rij binaire codes.
Doe net zo'n Open for Binary als je in je eigen voorbeeld gebruikt en via Put schrijf je de bytes.
Sub z()quote:Deze rij binaire codes kunnen in een excelblad onder elkaar staan, maar ook in een apart text-bestand.
Dat maakt op zich niet zoveel uit.
Iig er een bestand geschreven worden dat qua grootte dus ook precies evenveel bytes heeft dan het aantal getallen dat onder elkaar staat.
Dim k As Byte
Dim j As Integer
Range("A1").Select
j = Selection.End(xlDown).Row
Open "d:/meer.abc" For Binary As #8
For i = 1 To j
Put #8, , k
Next j
Close #8
End Sub
Bendankt voor het op weg helpen.quote:
[..]
Met PUT
Doe net zo'n Open for Binary als je in je eigen voorbeeld gebruikt en via Put schrijf je de bytes.
[..]
Sub z()
Dim k As Byte
Dim j As Integer
Range("A1").Select
j = Selection.End(xlDown).Row
Open "d:/meer.abc" For Binary As #8
For i = 1 To j
Put #8, , k
Next j
Close #8
End Sub
Maar je had als fout "Next j", dat moet natuurlijk Next i zijn.
Verder heb ik 'm als volgt gemaakt:
Sub za()
Dim k As Byte
Open "d:/meerxxy.abc" For Binary As #8
For i = 1 To 519534
k=cells(i,1)
Put #8, , k
Next i
Close #8
End Sub
Eventueel kan ik nog in cel b1 de formule zetten:
+1048576-COUNTBLANK(A:A)
Zodat ik 519534 of een andere lengte kan vervangen door Cells(1,2)
Dan klopt het programma altijd.
Belangrijkste is dat je het idee had hoe het werkt en dat jij het voor je eigen oplossing werkend hebt.
Precies, bedankt nogmaals!quote:
ah ja, last minute wijziging van variabele namen en dan 1 vergeten.
Belangrijkste is dat je het idee had hoe het werkt en dat jij het voor je eigen oplossing werkend hebt.
Toen ik gisteren bij mijn werk weg ging mooie grafiek opgezet bestaande uit gestapelde kolommen en 2 reeksen aan lijnen.
Ik open het bestand vanmiddag weer is een van de reeksen weg, alle reeksnamen zijn verdwenen en complete layout is vernaggeld.
Gelukkig heb ik versiebeheer (via onedrive) maar ik moest meerdere versies terug voordat ik mijn oude layout terug had. En ik weet zeker dat het klopte toen ik weg ging want ik heb een plaatje ervan in een powerpoint gezet en kon zo het origineel zien.
Vraagje waar de excelgoeroes vast mee kunnen helpen.
Ik heb 2 cellen waar een datum ingevuld wordt en een derde cel die dan telt hoeveel verschil er tussen die datums zit.
Nu wil ik die 2de invuldatum rood laten kleuren zodra tijdens invullen blijkt dat er meer dan x dagen tussen de 1ste en de 2de datum zit.
Met die 3de cel lukt dat wel, maar met die datumcel lukt me dat niet.
Iemand enig idee hoe ik die 2de datumcel een kleurtje geef zodra die 3de cel meer dan x dagen betreft?
Kolom B = tweede datum
Voorwaardelijke opmaak met deze regel =B1-A1>2
en bereik =$B:$B
Waarbij 2 het maximale aantal dagen is dat niet moet kleuren.
Edit:
Als je een kleiner bereik wil dan moet de formule ook aangepast worden naar de eerste vergelijking in het bereik. M.a.w. =B4-A4>2 werkt bij bereik =$B$4:$B$50 .
[ Bericht 20% gewijzigd door Tegan op 26-08-2020 17:32:15 ]
Waarbij het soms makkelijk kan zijn om die "2" ook uit een cel te halen zodat je users die niet thuis zijn in excel ook deze periode kan laten veranderen.quote:
Kolom A = eerste datum
Kolom B = tweede datum
Voorwaardelijke opmaak met deze regel =B1-A1>2
en bereik =$B:$B
Waarbij 2 het maximale aantal dagen is dat niet moet kleuren.
Nevermind, gelukt, thanks
[ Bericht 82% gewijzigd door vosss op 27-08-2020 09:04:15 ]
Cel is bijvoorbeeld 'Hallo'', daar wil ik 'allemaal' aan toevoegen zodat de cel 'Hallo allemaal' bevat. En dan voor alle rijen. De tekst in de cel is dus variabel, wat ik wil toevoegen is altijd hetzelfde.
Iemand een suggestie?
met je eerste tekst in kolom A
[ Bericht 53% gewijzigd door Symbo op 01-09-2020 10:14:12 (probleem opgelost) ]
Ik heb een waarde van 10 karakters in veld A4 staan. Deze waarde wil ik in mijn tabblad 'filter' opzoeken. Deze staat ook in tabblad 'filter' namelijk op record A91985. Hiervan wil ik de waarde terug geven die in kolom B staat.
De code die ik gebruik is
| 1 | =VERT.ZOEKEN(TEKST(A4;"0000000000");filter!A2:A130000;2;ONWAAR) |
Toch krijg ik een #VERW! terug. Iemand een suggestie?
Je waarde van tekst aanpassen of de waardes opsplitsenquote:Op maandag 7 september 2020 15:35 schreef onlogisch het volgende:
Op de één of andere manier heb ik altijd struggles met vlookup waarbij de celindeling 'standaard' is, of 'tekst'.
Ik heb een waarde van 10 karakters in veld A4 staan. Deze waarde wil ik in mijn tabblad 'filter' opzoeken. Deze staat ook in tabblad 'filter' namelijk op record A91985. Hiervan wil ik de waarde terug geven die in kolom B staat.
De code die ik gebruik is
[ code verwijderd ]
Toch krijg ik een #VERW! terug. Iemand een suggestie?
Meestal hebben dan de beide waarden die je vergelijkt (zoekt) een andere opmaak.quote:
Op de één of andere manier heb ik altijd struggles met vlookup waarbij de celindeling 'standaard' is, of 'tekst'.
Ik heb een waarde van 10 karakters in veld A4 staan. Deze waarde wil ik in mijn tabblad 'filter' opzoeken. Deze staat ook in tabblad 'filter' namelijk op record A91985. Hiervan wil ik de waarde terug geven die in kolom B staat.
De code die ik gebruik is
[ code verwijderd ]
Toch krijg ik een #VERW! terug. Iemand een suggestie?
Juist, en met de zoekwaarde moet dat euvel toch verholpen zijn? Dus met de (TEKST(A4;"0000000000")quote:
[..]
Meestal hebben dan de beide waarden die je vergelijkt (zoekt) een andere opmaak.
Nee. Alleen ik kon erbij.quote:
Was de file niet nog door een collega geopend in de tussentijd en per ongeluk een paar keer gesaved?
Heb dat nog nooit eerder meegemaakt. Zag ook dat eerdere versies aangetast waren. Terwijl ik prints heb van die versies dat ze wel gewoon kloppen.
Die A13... aanpassen naar B13...?quote:
Op de één of andere manier heb ik altijd struggles met vlookup waarbij de celindeling 'standaard' is, of 'tekst'.
Ik heb een waarde van 10 karakters in veld A4 staan. Deze waarde wil ik in mijn tabblad 'filter' opzoeken. Deze staat ook in tabblad 'filter' namelijk op record A91985. Hiervan wil ik de waarde terug geven die in kolom B staat.
De code die ik gebruik is
[ code verwijderd ]
Toch krijg ik een #VERW! terug. Iemand een suggestie?
Edit: Er is geen tweede kolom in bereik A4:A13..., daarom de foutmelding.
[ Bericht 1% gewijzigd door Tegan op 08-09-2020 12:53:30 ]
Mijn collega wees me daar inmiddels ook opquote:
[..]
Die A13... aanpassen naar B13...?
Er is geen tweede kolom in bereik A4:A13..., daarom de foutmelding.
Door de bomen (getrut iedere keer met 'tekst' en 'standaard') zag ik het bos niet meer.
Opgelost in ieder geval
quote:
[..]
Mijn collega wees me daar inmiddels ook op
Door de bomen (getrut iedere keer met 'tekst' en 'standaard') zag ik het bos niet meer.
Opgelost in ieder geval
.Zaten er macros in en was per ongeluk het lijstscheidingsteken en decimaaltekem aangepast door van toetsenbord indeling te veranderen?quote:
[..]
Nee. Alleen ik kon erbij.
Heb dat nog nooit eerder meegemaakt. Zag ook dat eerdere versies aangetast waren. Terwijl ik prints heb van die versies dat ze wel gewoon kloppen.
Nope. Via Onedrive/sharepoint kan ik tot wel 15 versies terug zetten. Ook versies waarvan ik weet dat ze goed waren.quote:
[..]
Zaten er macros in en was per ongeluk het lijstscheidingsteken en decimaaltekem aangepast door van toetsenbord indeling te veranderen?
Ik heb 2 grote lijsten waarvan ik een waarde wil vergelijken via vlookup.
Echter, één van die lijsten daar begint de waarde met '
Welke code dien ik te gebruiken dat hij bij vlookup teken ' negeert?
Ik ken daar geen code voor.quote:
Hoi,
Ik heb 2 grote lijsten waarvan ik een waarde wil vergelijken via vlookup.
Echter, één van die lijsten daar begint de waarde met '
Welke code dien ik te gebruiken dat hij bij vlookup teken ' negeert?
Wel werkt het om gewoon op TEKST te zoeken en niet op GETALLEN:
=vlookup(""&100,A:A,1,false)
Door de "" zorg je ervoor dat hij naar TEKST zoekt in plaats van naar getallen. Zoek je alleen op het getal 100 zal je vlookup nog steeds geen resultaat tonen. De 100 kan je natuurlijk ook gewoon vervangen voor een celverwijzing.
De andere vraag of je de ' niet kan verwijderen. Dat is een erg lastige, omdat zelfs CTRL H (zoeken en vervangen) met een ' geen resultaten zal geven. Je kan eventueel met een hulpkolom wel de ' weg krijgen, maar dan nog zit je met een TEKST. Dus in de kolom B gewoon de formule =A1 zal er al voor zorgen dat de waarde in B1 niet de ' laat zien.
je zou t met index(match))/index(vergelijken)) kunnen proberen. Met match/vergelijken kan je ook wildcards gebruiken bij het zoeken en zodoende dus je kolom zonder ' vergelijken met de kolom met.quote:
Hoi,
Ik heb 2 grote lijsten waarvan ik een waarde wil vergelijken via vlookup.
Echter, één van die lijsten daar begint de waarde met '
Welke code dien ik te gebruiken dat hij bij vlookup teken ' negeert?
Het zijn 127.000 recordsquote:
Kan je die ' niet gewoon weghalen?
Dan werkt mijn code niet meer. Hij ziet er nu zo uit.quote:
Je kunt verticaak zoeken op rechts(A1;lengte(A1)-1) ?
| 1 | =VERT.ZOEKEN(TEKST(A40200;"0000000000");Blad1!$A$1:$Z$130000;9;ONWAAR) |
Als ik de ' handmatig weghaal, krijg ik het resultaat wat ik wil zien. Als ik de ' niet weghaal krijg ik #N/B
Zijn het getallen of teksten die je gaat zoeken?quote:
| 1 | =DEEL(A1;2;10) |
Hier verschijnt dan wat ik wil zien en gebruik die kolom voor mijn code.
Hier ben ik weer met mijn dagelijkse excelvraagjes. Het is mij wederom een compleet raadsel.
Ik wil een macro activeren. Ik krijg deze melding :
Ik sluit de werkmap en open hem opnieuw. Ik zou niet weten waar ik 'de macro's in moet schakelen'.
Ik klik dus maar op 'Help'.
Deze leidt mij naar de volgende site toe :
https://support.microsoft(...)ad=nl#__toc311698309
Hierin staat :
Een gedeelte over een gele balk met 'inhoud inschakelen'. Dat krijg ik al niet te zien als ik mijn bestand open.
Het volgende gedeelte gaat over :
Macro's inschakelen in de weergave Backstage
Geen idee wat dat in vredesnaam zou moeten zijn. Maar ik probeer het dan maar op te volgen.
Ook hier loop ik vast.
Stap 1 : Klik op het tabblad Bestand. <- Hey, dat werkt
Stap 2 : 'Klik in het gedeelte Beveiligingswaarschuwing op Inhoud inschakelen.' <-- Hey, dat zie ik helemaal niet
Dat zie ik dus helemaal niet. Ik zie dit :
Iemand dus enig idee hoe ik wél een macro in kan schakelen zonder allerlei rare meldingen en verwijzingen die ik helemaal niet zie?
Onderste optie, alle macro's inschakelen (niet aanbevolen etc.) aangevinkt. Probleem blijft bestaan.quote:
Tabblad Ontwikkelaars en dan Macrobeveiliging?
Of
Bron: https://support.microsoft(...)b8-b19f-e8621e8fe373quote:Ik kan mijn beveiligingsinstellingen voor macro's niet wijzigen.
Sommige gebruikers kunnen instellingen voor Vertrouwenscentrum mogelijk niet wijzigen als gevolg van beveiligingsbeleid voor groepen binnen de organisatie. In dergelijke gevallen moet u contact opnemen met de IT-beheerder van uw organisatie.
Anders zou ik het ook niet weten.
Eigen PC, of die van je werk die deels beheerd wordt door je werkgever?quote:
[..]
Onderste optie, alle macro's inschakelen (niet aanbevolen etc.) aangevinkt. Probleem blijft bestaan.
PC van de zaak ja. Zou het daarmee te maken kunnen hebben?quote:
[..]
Eigen PC, of die van je werk die deels beheerd wordt door je werkgever?
Ja, dat kan. Die kunnen dergelijke toepassingen wel geblokkeerd hebben. Ik zou jou bijvoorbeeld een exceldocument kunnen mailen wat zichzelf met behulp van een macro kan vermenigvuldigen, of gaan doormailen naar de schoonmoeder van je CEO. Dat zal je systeembeheerder toch willen voorkomenquote:
[..]
PC van de zaak ja. Zou het daarmee te maken kunnen hebben?
Ik zou even contact opnemen met de IT-helpdesk van je bedrijf. Is het een macro die je zelf gemaakt hebt, of heb je hem per mail ontvangen ofzo?
Zelf gemaakt. Zal IT morgen even benaderen.quote:
[..]
Ja, dat kan. Die kunnen dergelijke toepassingen wel geblokkeerd hebben. Ik zou jou bijvoorbeeld een exceldocument kunnen mailen wat zichzelf met behulp van een macro kan vermenigvuldigen, of gaan doormailen naar de schoonmoeder van je CEO. Dat zal je systeembeheerder toch willen voorkomen
Ik zou even contact opnemen met de IT-helpdesk van je bedrijf. Is het een macro die je zelf gemaakt hebt, of heb je hem per mail ontvangen ofzo?
Dank je wel
Ik heb een macro gemaakt op een bepaalde locatie :g/docY, deze xlsm heb ik verplaatst naar G/doc X. Nu krijg ik een foutmelding. Als ik het document weer teruggeplaats naar de initiële locatie dan werkt de macro weer.
Knows anyone>?
Zegt de foutmelding nog iets cryptisch waar we wat uit zouden kunnen afleiden?quote:
Zijn er hier FOK!kers met verstand van macro's?
Ik heb een macro gemaakt op een bepaalde locatie :g/docY, deze xlsm heb ik verplaatst naar G/doc X. Nu krijg ik een foutmelding. Als ik het document weer teruggeplaats naar de initiële locatie dan werkt de macro weer.
Knows anyone>?
Staan er in de macro code harde verwijzingen naar de oude locatie?
De oplossing zou zijn bij de Macro eerst netjes via ActiveWorkbook.Name de naam te verkrijgen ipv hard coded er vanuit gaan dat het ABC is.
Dus als voorbeeld dat
Dorpstraat 1 10euro
Dorpstraat 1 3euro
Dorpstraat 3 2 euro
Dorpstraat 5 1 euro
Dorpstraat 5 1 euro
Dorpstraat 7 6 euro
Dorpstraat 9 5 euro
Dorpstraat 10 6 euro
Dorpstraat 10 6 euro
Moet dan worden
Dorpstraat 1 13 euro
Dorpstraat 3 2 euro
Dorpstraat 5 2 euro
Dorpstraat 7 6 euro
Dorpstraat 9 5 euro
Dorpstraat 10 12 euro
Is dit te doen zonder draaitabel? Want dan mis ik wat gegevens uit mijn echte tabel. In het voorbeeld zou dit dan 2 dorpstraten uit 2 dorpen zijn. In een draaitabel is dit niet in elke cel zichtbaar. Terwijl ik dat weer wil linken. Dus heb ik per regel Dorp 1 dorpstraat 1 13 euro nodig.
Weet iemand hoe ik dit voor elkaar krijg?
Dan laat je de draaitabel toch eerst groeperen op de plaatsnaam?
Of op de postcode.
Maak van de straten in een aparte kolom unieke waarden.quote:
Hoe kan ik een tabel zo orderen dat wanneer gegevens van 1 bepaalde rij samengevoegd worden.
Dus als voorbeeld dat
Dorpstraat 1 10euro
Dorpstraat 1 3euro
Dorpstraat 3 2 euro
Dorpstraat 5 1 euro
Dorpstraat 5 1 euro
Dorpstraat 7 6 euro
Dorpstraat 9 5 euro
Dorpstraat 10 6 euro
Dorpstraat 10 6 euro
Moet dan worden
Dorpstraat 1 13 euro
Dorpstraat 3 2 euro
Dorpstraat 5 2 euro
Dorpstraat 7 6 euro
Dorpstraat 9 5 euro
Dorpstraat 10 12 euro
Daarna met =som.als() kan je optellen.
Alvast bedankt!
Gemiddelde ----> Gewenste waarde
9,2156 -----------------------> 2
11,0000 ---------------------> 0
13,0015 ---------------------> 0
quote:
Ik wil het gemiddelde van een aantal getallen berekenen met 1 decimaal achter de komma (dus bijv. 9,2156 of 11,0). Hiervan wil ik alleen het gedeelte na de komma retourneren, dus bijv. 2 of 0. Zie onderstaand voorbeeld. Hoe kan ik dit het beste doen met een formule? Liefst geen VBA. Ik heb zelf al wat zitten frutten maar kom niet verder dan een geretourneerde waarde van 0,2 i.p.v. 2.
Alvast bedankt!
Gemiddelde ----> Gewenste waarde
9,2156 -----------------------> 2
11,0000 ---------------------> 0
13,0015 ---------------------> 0
| 1 | =AFRONDEN(ABS(A1-GEHEEL(A1;0));1)*10 |
Dat werkt denk ik niet als het een heel getal is (want dan heb je geen decimaal)...quote:
Met de functie afronden op 1 decimaal en daarna in een cel de functie rechts gebruiken waar je alleen het eerste getal pakt? Dus rechts(A1;1)
Nicequote:
[..]
Dat werkt denk ik niet als het een heel getal is (want dan heb je geen decimaal)...
Yes, werkt als een zonnetje. Bedankt voor de snelle reactie!quote:
[..]
[ code verwijderd ]
Lijkt goed te werken,
quote:
https://www.google.com/search?q=excel+return+only+decimal+valuequote:
[..]
Yes, werkt als een zonnetje. Bedankt voor de snelle reactie!
Eerste hit
Alleen de *10 heb ik zelf toegevoegd Is gelukt met een som als formule idd.quote:
[..]
Maak van de straten in een aparte kolom unieke waarden.
Daarna met =som.als() kan je optellen.
Ik heb een template met een aantal kolommen met dropdown menu's. Voor het makkelijke:
In kolom B heeft het dropdown menu 4 keuzes: 1, 2, 3 of 4
In kolom E heeft het dropdown menu 5 keuzes: a,b,c,d of e
Totaal zijn er 6 tabbladen en elk tabblad heeft minstens 1 kolom met dropdown.
In de template plak ik data die ik uit Oracle krijg. Dit zijn bladen met 20K regels. Vervolgens is het opmaak kopiëren/plakken en dan staat alles goed.
Maar met dropdown's is het zo dat als de keuze 1,2,3 of 4 is, dat je een foutmelding krijgt als je '5' typt. Maar als je '5' erin plakt, krijg je geen foutmelding.
Kortom, als ik de 20k regels in de template plak en sommigen van deze kolommen hebben een waarde die niet in de dropdown voorkomen, krijg ik daar geen foutmelding van.
Is dit te verhelpen? Of kan dit alleen met een bepaalde macro.
Enige wat me nu te binnen schiet (als controle) is dat ik de data uit de dropdown kolommen naar een leeg tabblad kopieer en dan alle dubbele waarden eruit haal. Dan zie ik iig per kolom wat er in kan staan en kan ik dat vergelijken met de dropdown-opties, maar das best een intensief werk.
Hm ja het probleem alleen is dat de dropdown-opties allemaal namen zijn van 50 tekens en das wat lastiger zoeken.quote:
Je kan achteraf op die kolommen zoeken naar de waarde 5? Eventueel zoeken en vervangen?
Je data op orde krijgen is geen optie?quote:
Is dit te verhelpen? Of kan dit alleen met een bepaalde macro.
Ik wil dat als ik de data uit mn database haal (met oracle sql) en ze in de excel template plak, dattie in (bijvoorbeeld) kolom C alleen de waarden 1,2,3 en 4 kan weergeven in de 20k rijen. Ik wil dus eigenlijk dattie een waarschuwing geeft, of rood wordt ofzo, als ik vanuit mn data een waarde in de template plak die daar niet zou mogen horen.quote:
Kan je wat specifieker uitleggen watvoor waarden je in die kolom tegenkomt, en wat je ermee wil? Wil je die hele rijen verwijderen?
Rood maken kan met voorwaardelijke opmaak die in je template zit. Daarna moet je alsnog de verkeerde waarden handmatig corrigeren.quote:
[..]
Ik wil dat als ik de data uit mn database haal (met oracle sql) en ze in de excel template plak, dattie in (bijvoorbeeld) kolom C alleen de waarden 1,2,3 en 4 kan weergeven in de 20k rijen. Ik wil dus eigenlijk dattie een waarschuwing geeft, of rood wordt ofzo, als ik vanuit mn data een waarde in de template plak die daar niet zou mogen horen.
De andere twee oplossingen zijn al gegeven: zoeken en vervangen of een filter erop.
Ik ben het helemaal eens met Piles. Je moet of je data in Oracle op orde krijgen (of wanneer jij een andere weergave wilt van de juiste data, moet je niet de data zelf aanpassen maar gebruik maken van een view op die data). Het zou zelfs nog beter zijn om dit gewoon in je select query van je oracle database te hebben.
De tabel is een grid van 20x20 unieke nummers,
Links onder 001, rechts boven 400.
Nu heb ik in 1 kolom een lijst met een aantal van deze nummers,
001
050
200
333
Hoe kan ik deze lijst vergelijken met het grid, en dan het bijbehorende nummer in het grid kleuren?
Voorwaardelijke opmaak met de formule AANTAL.ALS() ?quote:
graag zou ik een conditional format hebben op een tabel.
De tabel is een grid van 20x20 unieke nummers,
Links onder 001, rechts boven 400.
Nu heb ik in 1 kolom een lijst met een aantal van deze nummers,
001
050
200
333
Hoe kan ik deze lijst vergelijken met het grid, en dan het bijbehorende nummer in het grid kleuren?