[Excel] Het grote Excelvragentopic #42

Index

» digital corner

actieve topics nieuwe topics
abonnement Unibet Coolblue
pi_145767316


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.

SPOILER
Om spoilers te kunnen lezen moet je zijn ingelogd. Je moet je daarvoor eerst gratis Registreren. Ook kun je spoilers niet lezen als je een ban hebt.
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
Om spoilers te kunnen lezen moet je zijn ingelogd. Je moet je daarvoor eerst gratis Registreren. Ook kun je spoilers niet lezen als je een ban hebt.
Password kwijt?
Soms heb je een worksheeet beveiligd met een password maar ben je die kwijt. Gelukkig is dat geen groot probleem want de interne beveiliging van Excel stelt niet heel veel voor.
Hier vind je meer info en een downloadbare macro die alle interne passwords verwijdert.

DISCLAIMER: Denk erom dat je met het breken van de beveiliging van een excelsheet mogelijk wet- of regelgeving overtreedt. Als de spreadsheet van jezelf is zou het geen probleem moeten zijn.
In andere gevallen: vraag toestemming aan de originele schrijver.
Bij twijfelgevallen: NIET DOEN


Mooi, grappig en cool
Een uitgebreide site met nuttige en minder nuttige excelsheetjes. Van kekke grafieken om je manager of leraar te betoveren via oscillogrammen en simulatoren tot pong voor als het weer een typische vrijdagmiddag is: Excelunusual

Verder
Alle vorige topics zijn hier terug te vinden.

Nieuw topic? Haal de OP uit de wiki
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
pi_145767450
42! :7
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
pi_146026697
Kleine kick inclusief vraag :)

Ik heb een Word bestand die ik wil mergen met data uit Excel. De data in Excel staat echter niet in een tabel zoals gebruikelijk, maar verspreid over honderden sheets. Is het mogelijk om een mergebestand te maken die data uit dezelfde range haalt, alleen verschillende sheets??
pi_146027445
Zijn de sheets de tabbladen in één bestand of bedoel je honderden bestanden?

Je zou dus een verzamelspreadsheet kunnen maken. Hiervoor gebruik je bijvoorbeeld de functie INDIRECT. Deze functie maakt van een tekstregel een verwijzing.

bijvoorbeeld INDIRECT("'[bestandsnaam.xlsx]Sheet1'!$G$8")

LET OP de dubbele aanhalingstekens " voor het enkele aanhalingsteken '

Door nu een lijst te maken met alle bestandsnamen in bijvoorbeeld kolom A en het samenvoegen van tekststring (met & ) kun je uit meerdere bestanden en sheets gegevens halen. Nadeel is wel dat je eenmalig al die bestanden open moet hebben staan.

Bijvoorbeeld in A2 staat "bestandsnaam.xlsx"

=INDIRECT("'["&A2&"Sheet1'!$G$8") geeft hetzelfde resultaat als hierboven.

Als je het verzamelbestand hebt kun je net als normaal mergen.
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
pi_146027630
Onderstaande code is om alle bestanden uit één directory in een lijstje te zetten.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42		Sub ListAllFile()
'makes list of files in selected folder
    Dim objFso As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sItem As String
    Dim fldr As FileDialog
    
    Set objFso = CreateObject("Scripting.FileSystemObject")
    'Set ws = ActiveCell
     
    'Get the folder object associated with the directory
    
    'select folder dialogue
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
'MsgBox sItem

    Set objFolder = objFso.GetFolder(sItem)
    ActiveCell.Value = "The files found in " & sItem & " are:"
     
     'Loop through the Files collection
    i = 1
    For Each objFile In objFolder.Files
        ActiveCell.Offset(i, 0).Value = objFile.Name
        i = i + 1
    Next
     
     'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFso = Nothing
     
End Sub
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
pi_146029149
Het gaat om ~100 excel bestanden met elk 5-75 sheets Alle bestanden in 1 directory samenvoegen in een grote tabel zou geweldig zijn, maar dat trekt Word niet (minimaal 500- 7500 brieven :()

Heb ondertussen zelf wat code geschreven die een merge sheet maakt in het bestand die je open hebt.

Voor de geïnteresseerde:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36		Option Explicit
Sub makeMergeSheets(control As IRibbonControl)

Dim ws As Worksheet
Dim x As Integer
Dim r As Integer

On Error Resume Next
    Application.DisplayAlerts = False
        Sheets("Merge").Delete
    Application.DisplayAlerts = True
On Error GoTo 0

Set ws = Worksheets.Add(before:=Sheets("Index"))

ws.Name = "Merge"

r = 5 'beginregel
For x = 3 To Worksheets.Count - 2

If Sheets(x).Range("H30").Value > 0 Then

With ws
    .Range("a" & r).Value = Sheets(x).Range("D4").Value
    .Range("b" & r).Value = Sheets(x).Range("D5").Value
    .Range("c" & r).Value = Sheets(x).Range("D6").Value
    .Range("d" & r).Value = UCase(Sheets(x).Range("D7").Value)
    .Range("e" & r).Value = Sheets(x).Range("i3").Value
    r = r + 1
End With
End If

Next
Set ws = Nothing

End Sub

Jouw voorbeeld is overigens handig. Daar ga ik binnenkort even mee spelen, wellicht kan ik het ergens anders voor gebruiken })
  woensdag 29 oktober 2014 @ 13:55:51 #7
350577 YourCaptor
A billion dollar
pi_146072668
Dit zal vast niet de moeilijkste vraag zijn, maar ik kom er maar niet uit. :?
Ik wil getallen sorteren van groot naar klein. Door het scheidingsteken voor duizendtallen (een punt in dit geval) sorter hij echter niet goed:



Hij zou de 2.059 na de 1.936 moeten zetten en voor 10.065. Enzovoorts.
Ik wil gewoon dat 2.059 wordt weergegeven als 2059.
Iemand?
We know this. The Chinese know that we know. But we make-believe that we don't know and the Chinese make-believe that they believe that we don't know, but know that we know. Everybody knows.
pi_146073594
quote:
0s.gif Op woensdag 29 oktober 2014 13:55 schreef CklokC het volgende:
Dit zal vast niet de moeilijkste vraag zijn, maar ik kom er maar niet uit. :?
Ik wil getallen sorteren van groot naar klein. Door het scheidingsteken voor duizendtallen (een punt in dit geval) sorter hij echter niet goed:

[ afbeelding ]

Hij zou de 2.059 na de 1.936 moeten zetten en voor 10.065. Enzovoorts.
Ik wil gewoon dat 2.059 wordt weergegeven als 2059.
Iemand?
Waarschijnlijk zijn het geen getallen maar tekst. En waarom is 1640 zonder punt?
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
  woensdag 29 oktober 2014 @ 15:01:06 #9
350577 YourCaptor
A billion dollar
pi_146075125
quote:
3s.gif Op woensdag 29 oktober 2014 14:18 schreef Deetch het volgende:

[..]

Waarschijnlijk zijn het geen getallen maar tekst. En waarom is 1640 zonder punt?
Zou goed kunnen. Deze getallen komen uit SAP.
1640 is zonder punt omdat ik die handmatig zo had gezet, om te testen of ie dat wel pakte. ;)
We know this. The Chinese know that we know. But we make-believe that we don't know and the Chinese make-believe that they believe that we don't know, but know that we know. Everybody knows.
pi_146075353
quote:
0s.gif Op woensdag 29 oktober 2014 15:01 schreef CklokC het volgende:

[..]

Zou goed kunnen. Deze getallen komen uit SAP.
1640 is zonder punt omdat ik die handmatig zo had gezet, om te testen of ie dat wel pakte. ;)
Kolom selecteren
CTRL+H
. vervangen door niks
replace all
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
  woensdag 29 oktober 2014 @ 16:34:33 #11
350577 YourCaptor
A billion dollar
pi_146078231
quote:
3s.gif Op woensdag 29 oktober 2014 15:07 schreef Deetch het volgende:

[..]

Kolom selecteren
CTRL+H
. vervangen door niks
replace all
Soms is het zo makkelijk.
Bedankt! _O_
We know this. The Chinese know that we know. But we make-believe that we don't know and the Chinese make-believe that they believe that we don't know, but know that we know. Everybody knows.
pi_146078271
quote:
0s.gif Op woensdag 29 oktober 2014 16:34 schreef CklokC het volgende:

[..]

Soms is het zo makkelijk.
Bedankt! _O_
Helaas vaak genoeg geclasht met SAP :D

graag gedaan
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
pi_146078277
quote:
3s.gif Op woensdag 29 oktober 2014 15:07 schreef Deetch het volgende:

[..]

Kolom selecteren
CTRL+H
. vervangen door niks
replace all
:D

Ik zat al moeilijk een oplossing uit te denken met text to columns _O-
Please consider the environment before printing this post.
Op zaterdag 27 mei 2017 00:36 schreef d4v1d het volgende:
Kabuf is af en toe best een prima kerel.
pi_146078516
quote:
2s.gif Op woensdag 29 oktober 2014 16:36 schreef KaBuf het volgende:

[..]

:D

Ik zat al moeilijk een oplossing uit te denken met text to columns _O-
Lol, gebeurt mij ook nog wel eens. heb ik een hele macro gemaakt om iets te doen en zegt een collega: Oh zo? klik klik klaar }:|
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
pi_146081329
quote:
3s.gif Op woensdag 29 oktober 2014 16:42 schreef Deetch het volgende:

[..]

Lol, gebeurt mij ook nog wel eens. heb ik een hele macro gemaakt om iets te doen en zegt een collega: Oh zo? klik klik klaar }:|
Dat deed ik een keer bij een collega die al jaren met formules en filters duplicaten verwijderde, terwijl Data>Remove duplicate hetzelfde doet. _O-
  woensdag 29 oktober 2014 @ 19:50:31 #16
350577 YourCaptor
A billion dollar
pi_146086189
quote:
3s.gif Op woensdag 29 oktober 2014 16:35 schreef Deetch het volgende:

[..]

Helaas vaak genoeg geclasht met SAP :D

graag gedaan
Dit is ook meteen een makkelijke manier om datums uit SAP goed te zetten. Vanwege de datum notatie 29.10.2014 sorteert hij dan ook zo irritant. Nu deed ik via tekst naar kolom (o.i.d. weet het even niet exact uit m'n hoofd) de datum overzetten van 29.10.2014 naar 29-10-2014. :)
We know this. The Chinese know that we know. But we make-believe that we don't know and the Chinese make-believe that they believe that we don't know, but know that we know. Everybody knows.
pi_146103853
quote:
0s.gif Op woensdag 29 oktober 2014 19:50 schreef CklokC het volgende:

[..]

Dit is ook meteen een makkelijke manier om datums uit SAP goed te zetten. Vanwege de datum notatie 29.10.2014 sorteert hij dan ook zo irritant. Nu deed ik via tekst naar kolom (o.i.d. weet het even niet exact uit m'n hoofd) de datum overzetten van 29.10.2014 naar 29-10-2014. :)
Tja SAP is dan ook een duits programma. de afkorting staat voor "Scheiß Arsch Programm" of "Sanduhr Abwarten Programm" :D

OT:
Het kan idd ook via text to columns, dan moet je bij de datum kolom even aangeven dat het een datum notatie is.
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
  donderdag 30 oktober 2014 @ 10:30:38 #18
350577 YourCaptor
A billion dollar
pi_146106313
quote:
3s.gif Op donderdag 30 oktober 2014 08:17 schreef Deetch het volgende:

[..]

Tja SAP is dan ook een duits programma. de afkorting staat voor "Scheiß Arsch Programm" of "Sanduhr Abwarten Programm" :D

OT:
Het kan idd ook via text to columns, dan moet je bij de datum kolom even aangeven dat het een datum notatie is.
Hahaha, dat verklaart alles. Deze zegt voor mij genoeg:


Yep met tekst naar kolom doe ik het nu, alleen is dat veel meer werk omdat het per kolom moet. En ik heb een kolom of 6 die omgezet moeten worden steeds.
We know this. The Chinese know that we know. But we make-believe that we don't know and the Chinese make-believe that they believe that we don't know, but know that we know. Everybody knows.
pi_146110448
quote:
0s.gif Op woensdag 29 oktober 2014 19:50 schreef CklokC het volgende:

[..]

Dit is ook meteen een makkelijke manier om datums uit SAP goed te zetten. Vanwege de datum notatie 29.10.2014 sorteert hij dan ook zo irritant. Nu deed ik via tekst naar kolom (o.i.d. weet het even niet exact uit m'n hoofd) de datum overzetten van 29.10.2014 naar 29-10-2014. :)
Als je nog meer geluk hebt krijg je er ook nog 01.01.2014 uit en excel vind in datum notities de trailing 0 vaak niet zo leuk.
pi_146245471
Ik zou graag in de cel waar de vraagtekens een functie hebben welke afhankelijk van het getal wat in de cel erboven staat het juiste percentage weergeeft.

Dus indien er in cel B2 een waarde staat van ¤7000 moet hij bijvoorbeeld weergeven K2
Staat er in cel b2 een waarde van ¤21500 moet hij weergeven K4, enzovoorts. Nu zou dit volgens mij moeten met een Als functie maar ik heb geen flauw idee hoe. En ik zou ook echt niet weten hoe, wie kan mij helpen?

Alvast dank,

  maandag 3 november 2014 @ 10:36:26 #21
346939 Janneke141
Green, green grass of home
pi_146245734
quote:
0s.gif Op maandag 3 november 2014 10:28 schreef Legwieh het volgende:
Ik zou graag in de cel waar de vraagtekens een functie hebben welke afhankelijk van het getal wat in de cel erboven staat het juiste percentage weergeeft.

Dus indien er in cel B2 een waarde staat van ¤7000 moet hij bijvoorbeeld weergeven K2
Staat er in cel b2 een waarde van ¤21500 moet hij weergeven K4, enzovoorts. Nu zou dit volgens mij moeten met een Als functie maar ik heb geen flauw idee hoe. En ik zou ook echt niet weten hoe, wie kan mij helpen?

Alvast dank,

[ afbeelding ]
=VERT,ZOEKEN(B2;I2:K8;3;WAAR)
Opinion is the medium between knowledge and ignorance (Plato)
pi_146247200
Mijn dank is groot!
pi_146251934
Hoe selecteer je een aantal opeenvolgende kollomen behalve één?

Heb nu dit: =COUNTIF(B4;B5;B7;B8;B9;B10;B11; ">0") maar dan krijg ik dus dit omdat ik elke kolom apart opgeef.



Ik wil dus B4, B5 en B7 t/m B11 selecteren.. en B6 niet.
  maandag 3 november 2014 @ 13:49:48 #24
346939 Janneke141
Green, green grass of home
pi_146251990
quote:
11s.gif Op maandag 3 november 2014 13:48 schreef d4v1d het volgende:
Hoe selecteer je een aantal opeenvolgende kollomen behalve één?

Heb nu dit: =COUNTIF(B4;B5;B7;B8;B9;B10;B11; ">0") maar dan krijg ik dus dit omdat ik elke kolom apart opgeef.

[ afbeelding ]

Ik wil dus B4, B5 en B7 t/m B11 selecteren.. en B6 niet.
Named ranges moet wel kunnen denk ik.

Andere optie: =COUNTIF(B4:B11)-COUNTIF(B6)
Opinion is the medium between knowledge and ignorance (Plato)
pi_146252247
quote:
0s.gif Op maandag 3 november 2014 13:49 schreef Janneke141 het volgende:

[..]

Named ranges moet wel kunnen denk ik.

Andere optie: =COUNTIF(B4:B11)-COUNTIF(B6)
Named ranges werkt ook niet met count if, je andere optie is dan eigenlijk de enige oplossing.

Beter past de vragensteller zijn dataset aan want het lijkt op een onlogische opbouw.
Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten
actieve topics nieuwe topics
abonnement Unibet Coolblue
[Excel] Het grote Excelvragentopic #42
Index

» digital corner

Forum Opties
Forumhop:
Hop naar:
(afkorting, bv 'KLB')