[Excel] Het grote Excel vragen topic #33

maandag 18 juni 2012 @ 13:10:11 #1

qu63

..de tijd drinkt..

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 meerder workbooks sheets kan selecteren voo afdrukken, een power replace die termen kan wijzigen in meerdere workbooks tegelijk en een formule vertaler, de formule in de actieve cell wordt in het engels weergegeven, je kan ook een engelse formule invoeren die dan in de actieve cell 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.

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.

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

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

maandag 18 juni 2012 @ 15:00:06 #2

Deetch

WTF?

quote:
Op maandag 18 juni 2012 12:42 schreef xyntarsus het volgende:
thx voor de hulp.

kan dit trouwens ook zonder dat ik het menu krijg (dat ie automatisch cel B2 pakt?)

quote:
Op maandag 18 juni 2012 13:09 schreef qu63 het volgende:

[..]

Dan krijg je
[code]
Sub fok()
If Range("B2").Value <> "" Then ActiveSheet.Name = Range("B2").Value
End Sub
[/code]

als ik t goed heb..

Of als je alle sheets van je workbook in 1 keer wilt doen:

1
2
3
4
5
6
7
8

Sub fok()

For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If Range("B2").Value <> "" Then ActiveSheet.Name = Range("B2").Value
Next sh

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

dinsdag 19 juni 2012 @ 10:46:03 #4

Deetch

WTF?

dat kan niet vanuit de ingevoegde trend.

Je kunt wel een nieuwe kolom aanmaken met daarin de vergelijking voor de trend.

stel:
A2:A20 = maanden
B2:B20 = getallen
nieuwe kolom C
in C2 zet je de formule =TREND($A$2:$A$20,$B$2;$B$20,A2)
deze formule trek je door naar beneden (tm C20)

grafiek maken met beide datasets erin en dan kun je de trendlijn wel aflezen.

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

dinsdag 19 juni 2012 @ 10:56:52 #5

ralfie

!Yvan eht nioj

hmm? je kunt toch gewoon rechtsklikken op de trendlijn en dan bij eigenschappen 'vergelijking in grafiek weergeven' aanvinken? Mocht je specifieke waarden nodig hebben gebruik je die formule om dat te berekenen

dinsdag 19 juni 2012 @ 12:10:44 #7

Deetch

WTF?

quote:
Op dinsdag 19 juni 2012 11:19 schreef andreas612 het volgende:

[..]

ja maar die formule klopt dus niet

waarschijjnlijk moet je de datum waarvan je een waarde wilt weten nog omrekenen naar de seriele datum. Excel slaat een datum nl op als zoveel dagen na 0-januari-1900.
http://www.cpearson.com/excel/datetime.htm

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 20 juni 2012 @ 10:32:05 #8

jakees

Ik probeer de eerste positie van een getal te vinden in een string. Ik gebruik office 2007 taal = NL. Nu heb ik de volgende functie gevonden
=als(deel(vind.alles({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))>LENgte(A1);0;deel(vind.alles({0;1;2;3;4;5;6;7;8;9};A1 &"0123456789")))

op 1 of andere manier werkt die formule niet. Kunnen jullie even kijken? Ik ben al een tijdje bezig en heb ook veel gezocht op internet, vind wel dingen maar die lukken op eoa. manier niet. Thx alvast!

woensdag 20 juni 2012 @ 10:58:02 #9

Deetch

WTF?

quote:
Op woensdag 20 juni 2012 10:32 schreef vogeltjesdans het volgende:
Het gaat om een excel document. Ik weet dat er een apart excel topic is, maar hier moet ook VBA code in dus ik post het even apart.

Waar het om gaat is het volgende. Ik heb een lijst met kolommen:
datum omschrijving behandelaar status
op deze kolommen pas ik een filter toe, waardoor ik makkelijk kan sorteren.

Wat ik wil is het volgende:
* selecteren op behandelaar
* afdrukbereik bepalen op behandelaar
* dit afdrukken

Dit heb ik in een macro gestopt (relatieve verwijzinng aangezet) met als volgend resultaat.
[ code zie onder ]

Dit werkt, maar de behandelaar heeft een telkens wisselend aantal rijen. Dus ik wil dat het afdrukbereik bepaald wordt op de rijen waar de betreffende behandelaar staat. Een soort =ALS functie, maar ik weet niet hoe ik dat in deze code moet invoeren.

Iemand?

Hier de code van vogeltjesdans

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Sub Sorteren()
ActiveCell.Offset(5, 4).Range("A1").Select
ActiveWorkbook.Worksheets("blad1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("blad1").AutoFilter.Sort.SortFields.Add Key:= _
ActiveCell.Offset(-3, 0).Range("A1"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("blad1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
ActiveCell.Offset(-1, -5).Range("A1:J17").Select
Selection.PrintOutCopies:=1, Collate:=True, IgnorePrintAreas:=False
End Sub

vervang Range("A1:J17").Select door
Range("A1", Range("J65536").End(xlUp)).Select en hij zou moeten werken

[ Bericht 13% gewijzigd door Deetch op 20-06-2012 11:36:47 (end sub niet gekopieerd) ]

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 20 juni 2012 @ 11:15:13 #10

ralfie

!Yvan eht nioj

quote:
Op woensdag 20 juni 2012 10:32 schreef jakees het volgende:
Ik probeer de eerste positie van een getal te vinden in een string. Ik gebruik office 2007 taal = NL. Nu heb ik de volgende functie gevonden
=als(deel(vind.alles({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))>LENgte(A1);0;deel(vind.alles({0;1;2;3;4;5;6;7;8;9};A1 &"0123456789")))

op 1 of andere manier werkt die formule niet. Kunnen jullie even kijken? Ik ben al een tijdje bezig en heb ook veel gezocht op internet, vind wel dingen maar die lukken op eoa. manier niet. Thx alvast!

Het eerste probleem wat ik zie is dat deel() meerdere argumenten nodig heeft. Het tweede probleem is dat vind.alles (bij mij althans) slechts één tekst zoekt, en dus niet 1 of 2 of 3 of ...

Ik denk niet dat wat je wilt met louter excel formules te doen is (ik kan het mis hebben). Met VBA is het twee seconden werk.

Wat wel kan (maar erg omslachtig is dit):

1
2
3

B1 = A1 & "1234567890"
C1 = MIN(VIND.SPEC(1;B1);VIND.SPEC(2;B1);VIND.SPEC(3;B1);VIND.SPEC(4;B1);VIND.SPEC(5;B1);VIND.SPEC(6;B1);VIND.SPEC(7;B1);VIND.SPEC(8;B1);VIND.SPEC(9;B1);VIND.SPEC(0;B1))
D1 = ALS(C1>LENGTE(A1);-1;C1)

Iets korters zie ik zogauw niet in excel

woensdag 20 juni 2012 @ 11:18:20 #11

Deetch

WTF?

quote:
Op woensdag 20 juni 2012 10:32 schreef jakees het volgende:
Ik probeer de eerste positie van een getal te vinden in een string. Ik gebruik office 2007 taal = NL. Nu heb ik de volgende functie gevonden
=als(deel(vind.alles({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))>LENgte(A1);0;deel(vind.alles({0;1;2;3;4;5;6;7;8;9};A1 &"0123456789")))

op 1 of andere manier werkt die formule niet. Kunnen jullie even kijken? Ik ben al een tijdje bezig en heb ook veel gezocht op internet, vind wel dingen maar die lukken op eoa. manier niet. Thx alvast!

Eerste positie van een getal (onderstaande functie bevestigen met CTRL+SHIFT+ENTER)
=MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)

Alle getallen uit een string (onderstaande functie bevestigen met CTRL+SHIFT+ENTER)
=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9})),SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))))

Let op dat je de CTRL+SHIFT+ENTER gebruikt ipv gewone enter anders werken bovenstaande functies niet

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.

[ Bericht 16% gewijzigd door Deetch op 20-06-2012 11:23:49 ]

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 20 juni 2012 @ 11:34:41 #12

vogeltjesdans

cry me a river

quote:
Op woensdag 20 juni 2012 10:58 schreef Deetch het volgende:

[..]

Hier de code van vogeltjesdans
[ code verwijderd ]

vervang Range("A1:J17").Select door
Range("A1", Range("J65536").End(xlUp)).Select en hij zou moeten werken

Thanks! Maar als ik hem aanpas en uitvoer geeft hij een fout op de onderste regel, deze wordt geel:
Selection.PrintOutCopies:=1, Collate:=True, IgnorePrintAreas:=FalseEnd Sub.

Op vrijdag 14 mei 2021 @ 23:33 schreef Joopklepzeiker: Zonder twijfel is @vogeltjesdans de grootste smaakmaker en intelligentste persoon van heel KLB.

woensdag 20 juni 2012 @ 11:36:18 #13

Deetch

WTF?

quote:
Op woensdag 20 juni 2012 11:34 schreef vogeltjesdans het volgende:

[..]

Thanks! Maar als ik hem aanpas en uitvoer geeft hij een fout op de onderste regel, deze wordt geel:
Selection.PrintOutCopies:=1, Collate:=True, IgnorePrintAreas:=FalseEnd Sub.

tussen False en End Sub moet een ENTER (foutje bij kopieren van jouw code)

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 20 juni 2012 @ 12:20:15 #14

jakees

quote:
Op woensdag 20 juni 2012 11:18 schreef Deetch het volgende:

[..]

Eerste positie van een getal (onderstaande functie bevestigen met CTRL+SHIFT+ENTER)
=MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)

Alle getallen uit een string (onderstaande functie bevestigen met CTRL+SHIFT+ENTER)
=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9})),SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))))

Let op dat je de CTRL+SHIFT+ENTER gebruikt ipv gewone enter anders werken bovenstaande functies niet

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.
[ afbeelding ]

Deetch, zit jij op Excel of zo. 1 minpuntje aan je post. Het is niet duidelijk dat je op CTRL-SHIFT-ENTER moet drukken maar dat vergeef ik je ;-)

Toch nog een paar vraagjes. De eerste heb ik vertaald naar:
=VERGELIJKEN(WAAR;ISGETAL(-DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1));0) ik snap niet zo goed wat die doet.

Die 2e retourneert bij alle getallen in de string. Hartstikke mooi maar ik ben op zoek naar de 1e positie van het getal. Is daar nog makkelijk achter te komen. Bijvoorbeeld in de string. Oranjenummer1inBrazilie2014 wil ik als resultaat 13 terugkrijgen omdat daar de eerste string staat. Thx alvast weer!

woensdag 20 juni 2012 @ 12:38:26 #15

Deetch

WTF?

Die eerste functie geeft aan op welke positie het eerste getal staat, precies wat je zocht.
Ik weet alleen niet zeker of ISGETAL wel een excel 2007 functie is of dat die nieuw is in 2010. Krijg je daar een foutmelding? _{ook als je wel ctrl+shift+enter doet}

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 20 juni 2012 @ 12:43:34 #16

qu63

..de tijd drinkt..

quote:
Op woensdag 20 juni 2012 12:38 schreef Deetch het volgende:
Die eerste functie geeft aan op welke positie het eerste getal staat, precies wat je zocht.
Ik weet alleen niet zeker of ISGETAL wel een excel 2007 functie is of dat die nieuw is in 2010. Krijg je daar een foutmelding? _{ook als je wel ctrl+shift+enter doet}

=ISGETAL() is ook een 2007-functie

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

woensdag 20 juni 2012 @ 12:43:41 #17

qu63

..de tijd drinkt..

-crap-

Quote ipv edit.. Zelfs na 9 jaar fok heb ik dat trucje niet door..

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

woensdag 20 juni 2012 @ 13:27:35 #18

vogeltjesdans

cry me a river

quote:
Op woensdag 20 juni 2012 11:36 schreef Deetch het volgende:

[..]

tussen False en End Sub moet een ENTER (foutje bij kopieren van jouw code)

Kut

edit: in de code staat hij goed. Help

Op vrijdag 14 mei 2021 @ 23:33 schreef Joopklepzeiker: Zonder twijfel is @vogeltjesdans de grootste smaakmaker en intelligentste persoon van heel KLB.

woensdag 20 juni 2012 @ 13:55:43 #19

znarch

ondertitel

Een hopelijk niet al te moeilijke vraag.

Ik heb in een sheet een formule:

1	=EXP(((-1VB1)VB2))*NORMSDIST(C24)

VB1 en VB2 zijn cellen met een naam en dus een vaste waarde. C24 wijzigt steeds.

Hoe kan ik nu (in VB) zelf een formule maken waarbij ik alleen als ik: =formule typ alleen C24 zelf hoef in te vullen?

"AAAAAHH ZENNE MOAT, WOARST VLEISCH"

woensdag 20 juni 2012 @ 14:16:01 #20

DaFan

Dat heet een User Defined Function. Ga je zelf proberen of heb je hulp nodig?

woensdag 20 juni 2012 @ 18:01:44 #21

znarch

ondertitel

quote:
Op woensdag 20 juni 2012 14:16 schreef DaFan het volgende:
Dat heet een User Defined Function. Ga je zelf proberen of heb je hulp nodig?

Ik faal, hard

"AAAAAHH ZENNE MOAT, WOARST VLEISCH"

woensdag 20 juni 2012 @ 21:07:26 #22

jakees

quote:
Op woensdag 20 juni 2012 12:38 schreef Deetch het volgende:
Die eerste functie geeft aan op welke positie het eerste getal staat, precies wat je zocht.
Ik weet alleen niet zeker of ISGETAL wel een excel 2007 functie is of dat die nieuw is in 2010. Krijg je daar een foutmelding? _{ook als je wel ctrl+shift+enter doet}

quote:
Op woensdag 20 juni 2012 12:20 schreef jakees het volgende:

[..]

Deetch, zit jij op Excel of zo. 1 minpuntje aan je post. Het is niet duidelijk dat je op CTRL-SHIFT-ENTER moet drukken maar dat vergeef ik je ;-)

Toch nog een paar vraagjes. De eerste heb ik vertaald naar:
=VERGELIJKEN(WAAR;ISGETAL(-DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1));0) ik snap niet zo goed wat die doet.

Die 2e retourneert bij alle getallen in de string. Hartstikke mooi maar ik ben op zoek naar de 1e positie van het getal. Is daar nog makkelijk achter te komen. Bijvoorbeeld in de string. Oranjenummer1inBrazilie2014 wil ik als resultaat 13 terugkrijgen omdat daar de eerste string staat. Thx alvast weer!

Ik heb het vertaald naar:
=VERGELIJKEN(WAAR;ISGETAL(-DEEL(A1+RIJ(INDIRECT("1:"&LENGTE(A1)));1));0) Ik snap totaal niet wat er gebeurt maar er staat ook dat ik een argument te weinig hebt bij de functie DEEL. Nog een tip over wat ik verkeerd doe?

woensdag 20 juni 2012 @ 21:33:59 #23

snabbi

=VERGELIJKEN(WAAR;ISGETAL(-DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1));0)
is juist (had Deetch gezegd CTRL SHIFT ENTER?)

Je hebt in jouw formule bij de A1 een + staan ipv een ;

Wat het eigenlijk doet is dat het in de cel zelf een nieuwe matrix aanmaakt (genaamd array). In deze matrix wordt op elke regel 1 karakter geplaatst. Vervolgens wordt van dit karakter bepaald of het een getal is of niet. Deze uitkomst (WAAR of ONWAAR) wordt opgeslagen op deze regel en daarna wordt de eerste regel met een WAAR teruggeven.

Het gebruik van de CTRL SHIFT ENTER zorgt ervoor dat je de array functie in de cel gebruikt. Dit kan je ook zien aan je formule doordat de formule tussen { } accolades worden weergegeven.

[ Bericht 30% gewijzigd door snabbi op 20-06-2012 21:40:09 ]

donderdag 21 juni 2012 @ 07:35:46 #24

jakees

quote:
Op woensdag 20 juni 2012 21:33 schreef snabbi het volgende:
=VERGELIJKEN(WAAR;ISGETAL(-DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1));0)
is juist (had Deetch gezegd CTRL SHIFT ENTER?)

Je hebt in jouw formule bij de A1 een + staan ipv een ;

Wat het eigenlijk doet is dat het in de cel zelf een nieuwe matrix aanmaakt (genaamd array). In deze matrix wordt op elke regel 1 karakter geplaatst. Vervolgens wordt van dit karakter bepaald of het een getal is of niet. Deze uitkomst (WAAR of ONWAAR) wordt opgeslagen op deze regel en daarna wordt de eerste regel met een WAAR teruggeven.

Het gebruik van de CTRL SHIFT ENTER zorgt ervoor dat je de array functie in de cel gebruikt. Dit kan je ook zien aan je formule doordat de formule tussen { } accolades worden weergegeven.

Thx!!!! Ik wist niet dat je CTRL SHIFT ENTER moest gebruiken. Had hij wel even kunnen zeggen ;-) Maar die + deed het hem. Ik denk ook wel dat ik de formule ongeveer snap (een klein beetje iig).

donderdag 21 juni 2012 @ 11:11:50 #25

xyntarsus

ex nihi omnia creata est

en ik ben er weer met een vraag

ik heb een userform gemaakt om een aantal cellen in te voeren.
bij 'ok' voert excel uit, bij 'cancel' annuleert hij en verwijdert automatisch het nieuw aangemaakte blad.

nu heb ik hierbij nog 3 dingen die ik graag wil toevoegen:

1. als ik cancel doe, wil ik graag de standaardmelding van excel (weet u zeker blablabla) overslaan
edit:

1
2
3
4
5
6
7
8
9

Private Sub CommandButton2_Click()

UserForm1.Hide
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("TOTAAL").Select

End Sub

is de oplossing die ik hiervoor gevonden heb

2. als ik op het kruisje klik ipv cancel, moet hij hetzelfde doen als met de cancelknop

3. via het form doe ik ook tabnaam aanpassen, maar ik wil graag een controle of de naam al bestaat
zo nee, mag hij doorgaan, zo ja moet er een pop-up met foutmelding komen

Is dit mogelijk in een VBA script?
(en zo ja, hoe natuurlijk

)

[ Bericht 25% gewijzigd door xyntarsus op 21-06-2012 12:58:44 (deeloplossing gevonden) ]

donderdag 21 juni 2012 @ 20:40:10 #26

snabbi

Ik ben geen held met user forms (en vind dit eigenlijk een oneigenlijk element in Excel)

Vraag 2)
Volgens mij moet je iets maken in de trant van:

quote:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
'xxx
End If
End Sub

CloseMode is een 1 wanneer je zelf gebruik maakt van Unload Me
CloseMode is een 0 wanneer je bijvoorbeeld het kruisje gebruikt.

Via deze wijze kan je hem leiden naar je routine die je gebruikt voor Cancel.

3)

quote:
Public Function BladBestaat(ByVal myName As String) As Boolean
On Error Resume Next
Bladbestaat = (Sheets(myName).Name <> "")
On Error GoTo 0
End Function

Nu kan je gewoon iets doen als:

quote:
Dim MyNewName As String
naamwerkblad:
MyNewName = InputBox("Hoe moet het tabblad heten")
If BladBestaat(MyNewName) Then
MsgBox ("Deze naam bestaat al")
GoTo naamwerkblad
End If
ActiveSheet.Name = MyNewName

donderdag 21 juni 2012 @ 22:01:49 #27

DaFan

quote:
Op woensdag 20 juni 2012 18:01 schreef znarch het volgende:

[..]

Ik faal, hard

Ok

Open VBA editor met Alt-F11.
Voeg een nieuwe Module toe door rechtermuisknop te klikken je filename (VBAProject (xxx) en te kiezen voor Insert -> Module

Plak deze code erin:

1
2
3
4
5

Public Function ExpNormSDist(x As Double, y As Double, z As Double) As Double

ExpNormSDist = Exp(((-1 * x) * y)) * Application.WorksheetFunction.NormSDist(z)

End Function

Dan sluit je de editor.
Nu kan je in Excel dmv

1	=ExpNormSDist(VB1;VB2;C24)

de uitslag bepalen.
Omdat VB1 en VB2 gelijk zijn kan je ook gebruik maken van:

1
2
3
4
5

Public Function ExpNormSDist(z As Double) As Double

ExpNormSDist = Exp(((-1 * Range("VB1").Value) * Range("VB2").Value)) * Application.WorksheetFunction.NormSDist(z)

End Function

En dan de uitkomst ophalen met

1	=ExpNormSDist(C24)

Succes!
Kijk even of je eruitkomt hoé het werkt, en niet óf het werkt

vrijdag 22 juni 2012 @ 08:28:24 #28

xyntarsus

ex nihi omnia creata est

thx voor de info wederom

ik heb voor de foutcontrole (bestaat de bladnaam al, zo ja geef een melding)
de volgende code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

Private Sub CommandButton1_Click()

Item = TextBox1
ActiveSheet.Range("b2").Value = Item

Item = TextBox2
ActiveSheet.Range("b3").Value = Item

Item = TextBox3
ActiveSheet.Range("b4").Value = Item

Item = TextBox4
ActiveSheet.Range("b5").Value = Item

Item = TextBox5
ActiveSheet.Range("f2").Value = Item

Item = TextBox6
ActiveSheet.Range("f3").Value = Item

On Error GoTo fout

ActiveSheet.Name = ActiveSheet.Range("B2")

fout:
     MsgBox ("het nummer bestaat al, voer een nieuw nummer in")
     UserForm1.Hide
     Application.DisplayAlerts = False
     ActiveSheet.Delete
     Application.DisplayAlerts = True
     Sheets("basis").Unprotect
     Sheets("basis").Copy Before:=Sheets("basis")
     Sheets("basis").Protect
     Sheets("basis (2)").Select
     UserForm1.Show

UserForm1.Hide

End Sub

Deze gaat dus lopen als ik op OK klik
Als ik nu een al bestaand project heb geeft hij ook de juiste foutmelding, en hij opent ook netjes
het userform opnieuw.
Hierna gaat het mis, alle nummers die ik invoer neemt hij als 'al bestaand' en ik kom er niet meer uit.

Als ik nu de functie beeindig en opnieuw opstart, blijft hij de fout loopen en kan ik geen nieuwe bladen maken.

Wie kan mij vertellen wat ik fout doe??

vrijdag 22 juni 2012 @ 22:35:42 #29

snabbi

quote:
Op vrijdag 22 juni 2012 08:28 schreef xyntarsus het volgende:
thx voor de info wederom

ik heb voor de foutcontrole (bestaat de bladnaam al, zo ja geef een melding)
de volgende code
[ code verwijderd ]

Deze gaat dus lopen als ik op OK klik
Als ik nu een al bestaand project heb geeft hij ook de juiste foutmelding, en hij opent ook netjes
het userform opnieuw.
Hierna gaat het mis, alle nummers die ik invoer neemt hij als 'al bestaand' en ik kom er niet meer uit.

Als ik nu de functie beeindig en opnieuw opstart, blijft hij de fout loopen en kan ik geen nieuwe bladen maken.

Wie kan mij vertellen wat ik fout doe??

Ok, laten we beginnen met een stukje voor de leesbaarheid van je code. Hiermee bedoel ik ook voorkomen dat je onnodig variabelen aanmaakt, danwel de code heel lang maakt

Een verwijziging naar Activesheet is niet nodig, ook is het eigenlijk niet nodig met de range te werken (al vinden veel mensen dit wel zo netjes. Je eerste deel kan je in feite beperken tot:

quote:
[b2] = textbox1
[b3] = textbox2
[b4] = textbox3
[b5] = textbox4
[f2] = textbox5
[f3] = textbox6

Nu naar je echte probleem. Hij loopt je code van boven tot beneden door. Dit houdt in dat hij je code van "fout" altijd uitvoert, ongeacht of het nu goed of fout gaat. Wat je dus moet doen is zorgen dat je code stopt, wanneer het niet fout gaat. In jouw geval krijg je dan zoiets:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Private Sub CommandButton1_Click()
On Error GoTo fout
[b2] = textbox1
[b3] = textbox2
[b4] = textbox3
[b5] = textbox4
[f2] = textbox5
[f3] = textbox6
ActiveSheet.Name = Range("B2")
'mag overigens ook zijn ActiveSheet.Name = [b2]
'wanneer het goed gaat moet hij nu stoppen, daarom zet ik hieronder End neer, je kan bijvoorbeeld ook een Goto Einde maken ofzo
On Error GoTo 0
'Wanneer je iets met error handling doet, is het goed om wanneer je klaar bent met het afvangen van je error weer terug te verwijzen naar goto 0 zodat weer de normale Excel error naar voren kan komen.

End
fout:
'en hier je fout handling zoals je dat wil
end sub

maandag 25 juni 2012 @ 13:39:54 #30

xyntarsus

ex nihi omnia creata est

Thx

ik heb het zo opgebouwd, en t lijkt zelfs soepeler te lopen.

nu voel ik me nog meer een amateur

Maar goed, op naar de volgende te bouwen onderdelen
(lijsten genereren, zoekfunctie bouwen en automatisch gegevens invoeren vanuit cellen)

donderdag 28 juni 2012 @ 16:31:18 #31

xyntarsus

ex nihi omnia creata est

en wederom ben ik er met een vraag.

Ik wil in het bestand dat ik aan het bouwen ben nog 2 extra dingen toevoegen.
1. een lijst van alle tabbladen (in mijn geval projectnummers)
waarbij dan een aantal gegevens uit het tabblad worden gezet in een lijst

2. een zoekfunctie waarbij ik een artikelnummer opgeef en die door meerdere tabbladen heen zoekt en kijkt of er in een bepaalde range cellen
(gedefinieerd door een artikelnummer aan het begin van de regel) niet lege cellen zijn.
deze regels met gegevens (dus de regels waarin in gegevens zijn ingevoerd) moet dan in een lijst
komen die weer begint met het projectnummer en dan de rest van de regel erachter zet
(en voor elk product in de zoekopdracht een lijst maakt)

Ik heb bij deze twee echter geen idee hoe ik moet beginnen

Wie helpt mij???

donderdag 28 juni 2012 @ 22:24:19 #32

snabbi

quote:
Op donderdag 28 juni 2012 16:31 schreef xyntarsus het volgende:
en wederom ben ik er met een vraag.

Ik wil in het bestand dat ik aan het bouwen ben nog 2 extra dingen toevoegen.
1. een lijst van alle tabbladen (in mijn geval projectnummers)
waarbij dan een aantal gegevens uit het tabblad worden gezet in een lijst

2. een zoekfunctie waarbij ik een artikelnummer opgeef en die door meerdere tabbladen heen zoekt en kijkt of er in een bepaalde range cellen
(gedefinieerd door een artikelnummer aan het begin van de regel) niet lege cellen zijn.
deze regels met gegevens (dus de regels waarin in gegevens zijn ingevoerd) moet dan in een lijst
komen die weer begint met het projectnummer en dan de rest van de regel erachter zet
(en voor elk product in de zoekopdracht een lijst maakt)

Ik heb bij deze twee echter geen idee hoe ik moet beginnen

Wie helpt mij???

Voor het eerste deel is er zover ik weet geen normale formule die je daar bij kunt helpen en moet je dus naar VBA en krijg je iets in de trant van:

1
2
3

For i = 1 To Sheets.Count
ActiveCell.Offset(i - 1, 0).Value = Sheets(i).Name
Next i

Het eerste deel is gewoon een loop maken van 1 tot het aantal sheets dat je hebt. Vervolgens ga je voor elke sheet vanaf je huidige cel de namen onder elkaar opslaan. (De offset zorgt ervoor dat je i-cellen naar beneden vanaf de huidige cel iets wegschrijft).

Het tweede deel van je vraag begrijp ik niet helemaal. Kan je niet beter alle activiteiten op 1 tabblad opslaan en wanneer je iets voor een specifiek project wilt zien daar op filteren of een view daarvoor maken. Het verspreiden over al die tabbladen lijkt me eigenlijk niet handig.

vrijdag 29 juni 2012 @ 10:00:44 #33

xyntarsus

ex nihi omnia creata est

dank je, dat eerste deel is al erg nuttig,
kan ik daar dan ook, als ik die lijst eenmaal gegenereerd heb, gegevens uit dat specifieke blad achter hangen op een geautomatiseerde manier

de code die ik nu heb:

1
2
3
4
5
6
7
8

Private Sub CommandButton4_Click()

  Sheets.Add Before:=Sheets("totaal")
  ActiveSheet.Name = "Projectenlijst"

For i = 4 To Sheets.Count -1
ActiveCell.Offset(i - 1, 1).Value = Sheets(i).Name
Next i

Wat ik ook graag wil is een aantal bladen uitsluiten in de lijst (totaalblad en 'basis'tabel)
Kan dat ook?
boerenlogica zegt: als bladnaam = totaal, dan niets doen en doorgaan met de volgende
edit: ik heb hem nu in het 4e blad laten beginnen en het laatste niet meenemen, dan krijg ik in ieder geval te zien wat ik wil. Weet alleen niet of dit de beste oplossing is

voor het tweede deel van mijn vraag:
omdat ik ook een lijst per project wil kunnen uitdraaien, en ik iedere keer dezelfde tabel heb
voor een project (in de rijen de producten en in de kolommen 52 weken) wordt het heel onoverzichtelijk om dat in 1 blad te doen.
vandaar de keuze om voor elk project (pakweg 50 tegelijk meestal) een apart tabblad te maken, mijn 1e blad is dan een totaalblad dat optelt door de bladen heen.

Ik heb laatst een vergelijkbare opzet gezien, maar helaas krijg ik daar de code niet van :S

[ Bericht 7% gewijzigd door xyntarsus op 29-06-2012 10:10:59 (trial and error werkt :P) ]

vrijdag 29 juni 2012 @ 11:56:50 #34

Deetch

WTF?

Zoiets? Je moet zelf nog even met de variabele j puzzelen om de lijst op de goeie plek te krijgen.
Waarom begin je trouwens pas vanaf sheet 4 te tellen?

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Sub test()

j = 0

For i = 4 To Sheets.Count -1
    If Sheets(i).Name = "totaalblad" Or Sheets(i).Name = "basistabel" Then
    j = j
    Else
    ActiveCell.Offset(j, 1).Value = Sheets(i).Name
    j = j + 1
    End If
Next i

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

vrijdag 29 juni 2012 @ 13:15:53 #35

xyntarsus

ex nihi omnia creata est

omdat de eerste drie bladen de bladen zijn die ik niet wil mee tellen, dus ik had het eigenlijk omzeild

dat stukje 'if' functie is denk ik de nette manier

zo lukt het in ieder geval wel

blijven de punten van het invullen van andere gegevens uit de betreffende bladen en de zoekfunctie over...
na wat zoek en stoeiwerk denk ik dat ik met vert.zoeken een eind moet kunnen komen,
maar dan moet ik dat over meerdere bladen trekken en vervolgens van de gevonden regels een kopie in een nieuw blad zetten (en dan alle gevonden regels in hetzelfde blad onder elkaar)

vrijdag 29 juni 2012 @ 13:33:22 #36

Deetch

WTF?

Maar als iemand dan je bladen husselt dan gaat dat dus wel mis.

Als je veel uitzonderingen hebt is het misschin mooier om met case select te werken in plaats van de If....then...else
Dus in plaats van het hele stuk code tusen IF en END IF doe je onderstaande code

1
2
3
4
5
6
7

Select Case Sheets(i).Name
Case "totaal", "test"
j = j
Case Else
ActiveCell.Offset(j, 1).Value = Sheets(i).Name
j = j + 1
End Select

Aangezien je een lijst wilt op basis van projectnummer en alle tabbladen projectnumemrs zijn zou je een vert.zoeken kunnen combineren met indirect?

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

vrijdag 29 juni 2012 @ 13:46:10 #37

xyntarsus

ex nihi omnia creata est

met indirect?
nu ben ik overvraagd....

vrijdag 29 juni 2012 @ 13:53:57 #38

Deetch

WTF?

Stel naam van tabblad is "project1"
Om nu een waarde uit dat tabblad te halen doe je ='project1'!C2

Echter als je nou de naam van het tabblad variabel wilt maken omdat je al een lijst hebt met alle tabbladnamen dan kun je met INDIRECT dat bereiken.

Voorbeeld:
zet de naam van het tabblad op een ander blad in cel A1 (bijvoorbeeld)
en zet in de cel eronder de volgende formule

=indirect("'"& A1 &"'!C2") (let op alle leestekens)

deze haalt de waarde uit cel C2 van het tabblad waarvan de naam in A1 staat.

met het & teken kun je tekststrings aan elkaar plakken. Je plakt hier dus een ' voor de naam van het tabblad (A1) en erachter '!C2 voor het afsluiten van de tabblad naam en de verwijzing naar de cel.

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

vrijdag 29 juni 2012 @ 14:15:30 #39

xyntarsus

ex nihi omnia creata est

ok thx, dit werkt, ik kan nu een projectenlijst maken en updaten

enige wat hij nu niet doet, is projecten die er niet meer zijn weghalen,
is er een mogelijkheid dat ik dta kan invoegen?

vrijdag 29 juni 2012 @ 14:48:22 #40

Deetch

WTF?

quote:
Op vrijdag 29 juni 2012 14:15 schreef xyntarsus het volgende:
ok thx, dit werkt, ik kan nu een projectenlijst maken en updaten

enige wat hij nu niet doet, is projecten die er niet meer zijn weghalen,
is er een mogelijkheid dat ik dta kan invoegen?

Ik ben er van uitgegaan dat de sheetnamenlijst in b1 en daaronder staat, anders in de activesheet regel b1 veranderen in de gewenste cel
De hele regel van de sheet die niet meer bestaat wordt verwijderd

1
2
3
4
5
6
7
8
9
10
11
12

Sub sheetbestaatnietmeer()

On Error Resume Next

ActiveSheet.Range("b1", ActiveSheet.Range("b1").End(xlDown)).Select

For Each c In Selection
If Sheets(c.Value).Name = "" Then c.EntireRow.Delete
Next c
On Error GoTo 0

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

vrijdag 29 juni 2012 @ 21:39:16 #41

snabbi

quote:
Op vrijdag 29 juni 2012 10:00 schreef xyntarsus het volgende:
voor het tweede deel van mijn vraag:
omdat ik ook een lijst per project wil kunnen uitdraaien, en ik iedere keer dezelfde tabel heb
voor een project (in de rijen de producten en in de kolommen 52 weken) wordt het heel onoverzichtelijk om dat in 1 blad te doen.
vandaar de keuze om voor elk project (pakweg 50 tegelijk meestal) een apart tabblad te maken, mijn 1e blad is dan een totaalblad dat optelt door de bladen heen.

Ik heb laatst een vergelijkbare opzet gezien, maar helaas krijg ik daar de code niet van :S

Tja eigenlijk blijf ik wel van mening dat je eigenlijk al je data in 1 tabblad moet zetten. Wanneer je verschillende dwarsdoorsnedes wilt maken van je gegevens set kan je dat veel makkelijker via een pivot tabel regelen. Dan kan je tenminste zeggen ik wil alles van 1 project zien, of 1 product over alle projecten heen.

Ook scheelt dit heel veel macro gedoe, wat in mijn ogen er alleen maar voor zorgt dat je programma onbeheersbaar wordt.

zaterdag 30 juni 2012 @ 12:54:01 #42

DaFan

Access

maandag 2 juli 2012 @ 08:20:23 #43

Deetch

WTF?

quote:
Op zaterdag 30 juni 2012 12:54 schreef DaFan het volgende:
Access

Dat is eigenlijk wel beter voor xyntarsus' gebruik. Maar ja, access is eng

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

maandag 2 juli 2012 @ 09:30:33 #44

DaFan

quote:
Op maandag 2 juli 2012 08:20 schreef Deetch het volgende:

[..]

Dat is eigenlijk wel beter voor xyntarsus' gebruik. Maar ja, access is eng

Faler

maandag 2 juli 2012 @ 10:43:22 #45

xyntarsus

ex nihi omnia creata est

access is niet eng hoor

maar acces is niet beschikbaar op het netwerk hier

moet het doen met word en excel....
en wat betreft de aparte tabbladen, ook dat is een soort van
'opgelegde eis' :S

pivot tables zouden inderdaad kunnen als alles in 1 blad stond.

maandag 2 juli 2012 @ 11:02:31 #46

Deetch

WTF?

quote:
Op maandag 2 juli 2012 10:43 schreef xyntarsus het volgende:
access is niet eng hoor

maar acces is niet beschikbaar op het netwerk hier
moet het doen met word en excel....
en wat betreft de aparte tabbladen, ook dat is een soort van
'opgelegde eis' :S

pivot tables zouden inderdaad kunnen als alles in 1 blad stond.

zucht, IT op de werkplek

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

maandag 2 juli 2012 @ 11:07:58 #47

xyntarsus

ex nihi omnia creata est

idd, maja, ik zal t moeten doen met wat we hebben

kan thuis wel een Access dbase bouwen in office 2010, maar daar gaan we in
excel 2003 weinig aan hebben

:P

zal dus wat meer moeite moeten doen, maar heb er wel vertrouwen is dat t gaat lukken...

(denk ik....)

maandag 2 juli 2012 @ 22:14:56 #48

snabbi

Mwa je kan vanuit Excel gewoon een koppeling maken naar je Access database.
Daarnaast kan je bij het dubbel-klikken in je pivot tabel je betreffende benodigde tabblad creëren. Het maakt in ieder geval die zoekfunctie makkelijker (query) ipv via vertikaal zoeken over alle tabbladen of een ctrl f functie via macro's.

dinsdag 3 juli 2012 @ 08:08:35 #49

xyntarsus

ex nihi omnia creata est

Dat klopt, maar blijf het probleem bestaan dat op t netwerk hier geen access beschikbaar is

Is die zoekfunctie overigens wel mogelijk??

[ Bericht 15% gewijzigd door xyntarsus op 03-07-2012 08:48:05 ]

dinsdag 3 juli 2012 @ 13:32:08 #50

ralfie

!Yvan eht nioj

Zoiets voor de zoekfunctie?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Sub zoek(ByRef Sheet As Worksheet, ByRef Targetcell As Range, ByRef qrySearch As String, ByRef numcolumns As Integer)
    Dim zoekrange As Range, lastrow As Integer, cell As Range
    Set zoekrange = Sheet.Range("A:A")'Waar gaan we zoeken?
    Set cell = Sheet.Cells(1, 1)'De eerste cell in de kolom
    lastrow = 0
    Do
        Set cell = zoekrange.Find(qrySearch, cell, xlValues, xlPart, xlByColumns, xlNext)
        If cell Is Nothing Then Exit Sub'zoekopdracht nergens gevonden
        If cell.Row < lastrow Then Exit Sub'voorbij einde van kolom
        lastrow = cell.Row
        Targetcell.Value = Sheet.Name
        If WorksheetFunction.CountA(Sheet.Rows(lastrow)) > 1 Then'Er zitten niet-lege cellen in deze rij
            Sheet.Range("A" & lastrow).Resize(ColumnSize:=numcolumns).Copy Targetcell.Offset(ColumnOffset:=1).Resize(ColumnSize:=numcolumns)
            Set Targetcell = Targetcell.Offset(Rowoffset:=1)
        End If
    Loop
End Sub

Je zoekt dan bijvoorbeeld:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Sub test()
dim rngDoelwit as Range
dim strQry as string
dim numCols as integer

'hoeveel kolommen moeten er gekopieerd worden
numCols = 10
'Je zoekopdracht
strQry = "1*"
'Waar moeten de resultaten komen
set rngDoelwit = ActiveCell

For i = 4 To Sheets.Count -1
    If Sheets(i).Name <> "totaalblad" And Sheets(i).Name <> "basistabel" Then
        zoek Sheets(i), rngDoelwit, strQry, numCols
    End if
Next i
End Sub

dinsdag 3 juli 2012 @ 16:29:51 #51

xyntarsus

ex nihi omnia creata est

ik denk dat dit een heel eind doet wat ik in gedachten had,
maar wat ik even niet kan zien in de gauwigheid is of hij een nieuw blad aanmaakt
waarin hij alle gevonden regels als kopie zet (met als naam: 'art "ingegeven nummer" '.
(dit laatste lijkt me de regel:

1 2	item = textbox activesheet.name = ' "art"' &item'

tenminste zo had ik het zelf verzonnen

)

Van de (artikel)nummers waarop ik ga zoeken weet ik overigens zeker dat ze bestaan en gevonden zullen worden.

vrijdag 6 juli 2012 @ 11:29:19 #52

Pablooo

konjo

Ik heb in excel een sheet met 2 lijsten naast elkaar

Lijst 1 heeft een aantal namen
Lijst 2 heeft getallen die bij de namen horen

In plaats van:
Banaan 2
Appel 3

Wil ik zo een lijst

Banaan
Banaan
Appel
Appel
Appel

Weet iemand hoe ik dit snel en makkelijk kan doen?

vrijdag 6 juli 2012 @ 13:13:21 #53

ralfie

!Yvan eht nioj

quote:
Op vrijdag 6 juli 2012 11:29 schreef Pablooo het volgende:
Ik heb in excel een sheet met 2 lijsten naast elkaar

Lijst 1 heeft een aantal namen
Lijst 2 heeft getallen die bij de namen horen

In plaats van:
Banaan 2
Appel 3

Wil ik zo een lijst

Banaan
Banaan
Appel
Appel
Appel

Weet iemand hoe ik dit snel en makkelijk kan doen?

snel en makkelijk via VBA, maar het kan ook in excel (zij het omslachtig

)

Ik ga even van de volgende indeling uit:

1
2
3
4

Fruit Aantal
Banaan 2
Appel 3
Peer 4

Waar 'fruit' in A1 staat, 'banaan' in A2 etc.

stap 1) maak een lege kolom vóór de fruitkolom.
In A2 vul je de rij in waarin je uitkomst kolom moet beginnen. (bijvoorbeeld '2', indien je rij in D2 begint)
in A3 vul je =SOM(C$2:C2)+A$2
deze formule trek je naar beneden tot één rij onder je laatste fruit.
Dan, ik de kolom waar je je fruit wil hebben, vul je de volgende formule in
=VERT.ZOEKEN(RIJ();A:B;2;WAAR)
Deze formule trek je naar beneden zover je wil.
Indien je de nullen lelijk vindt zet je een spatie in de cel onder het laatste fruit.
Klaar.

vrijdag 6 juli 2012 @ 14:34:06 #54

DaFan

Clever... Chapeau.

vrijdag 6 juli 2012 @ 15:23:59 #55

Deetch

WTF?

Om een nieuw werrkblad met een gewenste naam te maken gebruik je de volgende code:

1
2
3

sheetnaam = InputBox("Geef artikelnummer: ", "Artikelnummer")
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "art." & sheetnaam

Als de sheetnaam (oftewel het artikelnummer) ergens vandaan komt dan moet je in plaats van de inputbox naar die plek verwijzen. Of in een eerder stadium in je macro het gevonden artikelnummer aan de variabele sheetnaam hangen.

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

zondag 8 juli 2012 @ 20:23:26 #56

einschtein

Hai, ben even een Excel-bestandje aan het inelkaar flanzen alleen kom ik er niet helemaal goed uit.

Op blad 1, moet worden ingevuld JA of NEE. Op blad 2, staan de antwoorden, ook in JA of NEE vorm. En op blad 3, moet de uitslag neer worden gezet. Als het goed is, dan moet hetzelfde vakje als in blad 1 en 2, in het groen komen te staan met de tekst 'GOED'. Als het fout is, dan in het rood met de test 'FOUT'.

Kom er niet uit.

zondag 8 juli 2012 @ 20:59:44 #57

snabbi

=als(Blad1!A1=Blad2!A1;"GOED";"FOUT")

zondag 8 juli 2012 @ 21:02:42 #58

einschtein

quote:
Op zondag 8 juli 2012 20:59 schreef snabbi het volgende:
=als(Blad1!A1=Blad2!A1;"GOED";"FOUT")

Thanks!

Enig idee, trouwens. Ik heb bij de 'uitslag' pagina dus de uitslag staan, maar als ik niets invul bij de antwoorden zelf geeft hij 'fout' aan. Is er ook een manier om het zo in te stellen, dat hij pas 'goed' of 'fout' aangeeft als ik pas iets heb ingevuld?

[ Bericht 23% gewijzigd door einschtein op 08-07-2012 21:10:55 ]

zondag 8 juli 2012 @ 21:12:45 #59

DaFan

Lukt je dat niet zelf adhv de formule die je net hebt gekregen?

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.

zondag 8 juli 2012 @ 21:22:52 #60

einschtein

quote:
Op zondag 8 juli 2012 21:12 schreef DaFan het volgende:
Lukt je dat niet zelf adhv de formule die je net hebt gekregen?

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.

Ik had zelf inderdaad even verder moeten denken, maar heb er niet aangedacht om een ALS in een ALS te verwerken.

=ALS(Vragen!A1="";"Blanco";ALS(Vragen!A1=Antwoorden!A1;"Goed";"Fout"))

En het werkt.

zondag 8 juli 2012 @ 21:35:59 #61

DaFan

maandag 9 juli 2012 @ 21:29:52 #62

Holy_Goat

mhèèhèhè

/offtopic
Even een andere 'vraag'

Heeft iemand van jullie wel eens wat gemaakt (in excel om het ontopic te houden) waar je ZO trots op bent dat je er gewoon een tikkie blij van wordt als je er aan denkt? Ja, i know, quite nerdy enzo, maar vroeg het me af.

Want dat heb ik nu wel

Voor het eerst ook een echt gelikte GUI in elkaar geflanst die onwijs intuitief aanvoelt en gewoon vet is. (als het af is post ik wel filmpie

)
/offtopic

Maarja, het is geen showoff topic hier natuurlijk ^^

maandag 9 juli 2012 @ 22:50:45 #63

qu63

..de tijd drinkt..

quote:
Op maandag 9 juli 2012 21:29 schreef Holy_Goat het volgende:
/offtopic
Even een andere 'vraag'

Heeft iemand van jullie wel eens wat gemaakt (in excel om het ontopic te houden) waar je ZO trots op bent dat je er gewoon een tikkie blij van wordt als je er aan denkt? Ja, i know, quite nerdy enzo, maar vroeg het me af.

Want dat heb ik nu wel Voor het eerst ook een echt gelikte GUI in elkaar geflanst die onwijs intuitief aanvoelt en gewoon vet is. (als het af is post ik wel filmpie )
/offtopic

Maarja, het is geen showoff topic hier natuurlijk ^^

Jup, mijn mega-super-duper-planning voor m'n studie!

Helaas hield die er alleen geen rekening mee dat ik er langer dan 4 jaar over zou doen..

Alleen worden de successen vaak overschaduwt door de dingen die ik wel wil, maar niet voor elkaar krijg in Excel

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

dinsdag 10 juli 2012 @ 08:46:15 #64

Deetch

WTF?

Mijn afstudeerproject voor de HTS was één dikke macro waarmee ik een fysisch model toepaste op reeele experimentele data en nee ik deed toen geen IT opleiding.
Dat was wel in de tijd dat ik nog niet zoveel met arrays etc werkte dus na het invoeren van alle relevante data in een userform flitste het scherm een minuut of 5 en dan kwam er een vet sjieke grafiek uit.
Daar was ik toen heel trots op en ook mijn cijfer was ernaar

Als ik er nu over nadenk:

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

dinsdag 10 juli 2012 @ 16:17:26 #65

KomtTijd...

Ik heb excel 2007, en probeer een footer te maken.
Nou bestaat die uit 3 vakjes met nauwelijks opmaakmogelijkheden, maar ik wil graag een tabel van 4 vakjes als footer.

Kan dat?

zcflevo.nl

woensdag 11 juli 2012 @ 09:57:25 #66

Deetch

WTF?

Wat bedoel je met nauwelijks opmaak mogelijkheden? Je kunt er plaatsjes in invoegen, lettertypes, grootte en kleur aanpassen dus wat wil je nog meer.
Je zult je in ieder geval moeten behelpen met de 3 secties (links, center en rechts)

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 11 juli 2012 @ 10:46:45 #67

KomtTijd...

Ik heb een standaard footer met bedrijfsnaam en contactgegevens enzo, die in 4 kolommen verdeeld is.

Maar als ik het zo begrijp is de enige mogelijkheid dus om dat als afbeelding in te voegen?

zcflevo.nl

zondag 15 juli 2012 @ 23:27:31 #68

sitting_elfling

Milkbreak Man

Ik vroeg me af of Excel eigenlijk de mogelijkheid kent om een bootstrap te doen (uit een distrubtie trekken). Met andere woorden, het (tigmaal) samplen van een tijdreeks?

People once tried to make Chuck Norris toilet paper. He said no because Chuck Norris takes crap from NOBODY!!!!
Megan Fox makes my balls look like vannilla ice cream.

maandag 16 juli 2012 @ 09:36:40 #69

Deetch

WTF?

quote:
Op zondag 15 juli 2012 23:27 schreef sitting_elfling het volgende:
Ik vroeg me af of Excel eigenlijk de mogelijkheid kent om een bootstrap te doen (uit een distrubtie trekken). Met andere woorden, het (tigmaal) samplen van een tijdreeks?

Met andere woorden ik begrijp niet wat je precies wilt. Excel kan goed werken met getallen, bedoel je soms uit een hele reeks data "random" een x aantal getallen halen?
Wat heb je als in input en wat wil je als output?

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

maandag 16 juli 2012 @ 11:37:28 #70

ralfie

!Yvan eht nioj

quote:
Op zondag 15 juli 2012 23:27 schreef sitting_elfling het volgende:
Ik vroeg me af of Excel eigenlijk de mogelijkheid kent om een bootstrap te doen (uit een distrubtie trekken). Met andere woorden, het (tigmaal) samplen van een tijdreeks?

Ja, maar moet je zelf schrijven in visual basic.

EDIT: Het KAN strict genomen in excel ook maar is erg onpractisch.
één bootstrap wordt dan (als brondata, bijvoorbeeld kosten en effecten in A1:B100 staat)

C1 = 1+ASELECT()*99
D1 = Index(A1:B100;C1;1)
E1 = Index(A1:B100;C1;2)
Trek dit 200 cellen naar beneden en je hebt 200 bootstraps aan kosten (D) en effecten (E).

De reden dat je dit beter niet zo kunt doen is dat 1) een bootstrap van 10000x erg traag wordt en 2) excel random getallen keer op keer vernieuwd waardoor je lijst continue veranderd.

[ Bericht 30% gewijzigd door ralfie op 16-07-2012 12:15:20 ]

maandag 16 juli 2012 @ 18:35:26 #71

Maartel

Oké, 'help!'
Om één of andere reden is een Excelsheet van me kolossaal geworden met héél veel rijen en héél veel kolommen. Zoveel dat Excel het niet aan kan om ook maar één rij of kolom te verwijderen omdat dat 'te zwaar is', ookal is 99% ervan leeg.

Weet iemand of er een manier is om een Excel sheet te 'croppen'?

Don't let your shadow spoil the view of what's around you

maandag 16 juli 2012 @ 22:54:18 #72

snabbi

quote:
Op zondag 15 juli 2012 23:27 schreef sitting_elfling het volgende:
Ik vroeg me af of Excel eigenlijk de mogelijkheid kent om een bootstrap te doen (uit een distrubtie trekken). Met andere woorden, het (tigmaal) samplen van een tijdreeks?

Wellicht begrijp ik je verkeerd, ik lees dit even als dat je een scenario wilt doorberekenen aan de hand van een random input? Ja dat kan. Maak hiervoor gebruik van de gegevenstabel zoals dit te vinden is onder tab Gegevens -> Wat-als analyse.

Het is even prutsen om het de eerste keer werkend te krijgen maar feitelijk is dit bedoeld om een formule met verschillende scenario's door te rekenen. Voor jouw analyse zal je vervolgens het gemiddelde hiervan waarschijnlijk willen gebruiken (van een random input var).

maandag 16 juli 2012 @ 22:58:15 #73

snabbi

quote:
Op maandag 16 juli 2012 18:35 schreef Maartel het volgende:
Oké, 'help!'
Om één of andere reden is een Excelsheet van me kolossaal geworden met héél veel rijen en héél veel kolommen. Zoveel dat Excel het niet aan kan om ook maar één rij of kolom te verwijderen omdat dat 'te zwaar is', ookal is 99% ervan leeg.

Weet iemand of er een manier is om een Excel sheet te 'croppen'?

De makkelijkste manier is je relevante data naar een nieuw werkblad te brengen en de oude te verwijderen.

maandag 16 juli 2012 @ 23:11:42 #74

Maartel

quote:
Op maandag 16 juli 2012 22:58 schreef snabbi het volgende:

[..]

De makkelijkste manier is je relevante data naar een nieuw werkblad te brengen en de oude te verwijderen.

Pfff, dan gaat alleen de lay-out enigszins naar de kl*ten, maar dat gaan we dan toch maar even proberen want rijen verwijderen trekt ie kennelijk echt niet.
Ik heb overigens nog steeds geen flauw idee welke toetsencombinatie die rijen tevoorschijn heeft gehaald..

Don't let your shadow spoil the view of what's around you

maandag 16 juli 2012 @ 23:53:27 #75

snabbi

Zonder het document kunnen wij natuurlijk ook niet zeggen hoe het kotm. Wat ik wel kan doen is je even helpen het gemakkelijker maken de zaken te kopieren. Dit kan je doen door de berekening van de formules tijdelijk uit te schakelen.

Kijk hiervoor onder het tabblad Formules naar de optie Berekeningopties. Haal deze van automatisch af om te voorkomen dat je bij elke wijziging alle dingen opnieuw doorrekent. Nadat je de wijzigingen hebt doorgevoerd, kan je dit weer aanzetten.

dinsdag 17 juli 2012 @ 22:58:45 #76

PowerData

Ik heb een tabel in Excel waarmee ik graag mijn gemiddelde cijfers voor school ed. wil uitrekenen. Hierbij heb ik gewogen cijfers, dus de een 5,5 en een 8 kan alsnog een 6 gemiddeld zijn.
Nu wil ik mijn gemiddelde 'vast' zetten, als in: daar hoef ik niets aan te doen. Dat betekent dat als ik een nieuw cijfer in wil voeren, ik enkel het cijfer en de wegingsfactor invoer, waarna mijn gemiddelde wordt berekend.

Ik kan wel bedenken hoe ik mijn totale wegingsfactor uitreken (=SUM(Weging[#All]), maar ik kan mij niet bedenken hoe ik wanneer ik een nieuwe kolom maak, ik mijn cijfer maal mijn wegingsfactor kan doen (voor elk cijfer). Kan iemand mij helpen?

“I'm not about caring; I'm about winning.”
- Harvey Specter
“Take a step back, look at the bigger picture.”

dinsdag 17 juli 2012 @ 23:06:25 #77

snabbi

Voorbeeld
Je wegingsfactor in kolom A (bijvoorbeeld van A2 tot A10), je cijfer in kolom B.(bijvoorbeeld van B2 tot B10).
Je kan nu je totaal berekenen door middel van somproduct(A2:A10;B2:B10) [in het engels sumproduct] en je kunt het totaal van je wegingsfactor berekenen via som(A2:A10).

Je gewogen gemiddelde vormt dus: somproduct(A2:A10;B2:B10)/som(A2:A10)

dinsdag 17 juli 2012 @ 23:11:55 #78

PowerData

quote:
Op dinsdag 17 juli 2012 23:06 schreef snabbi het volgende:
Voorbeeld
Je wegingsfactor in kolom A (bijvoorbeeld van A2 tot A10), je cijfer in kolom B.(bijvoorbeeld van B2 tot B10).
Je kan nu je totaal berekenen door middel van somproduct(A2:A10;B2:B10) [in het engels sumproduct] en je kunt het totaal van je wegingsfactor berekenen via som(A2:A10).

Je gewogen gemiddelde vormt dus: somproduct(A2:A10;B2:B10)/som(A2:A10)

Ontzettend bedankt, ik kende die functie niet.

Nu kan ik weer verder.

“I'm not about caring; I'm about winning.”
- Harvey Specter
“Take a step back, look at the bigger picture.”

dinsdag 17 juli 2012 @ 23:45:50 #79

PowerData

quote:
Op dinsdag 17 juli 2012 23:06 schreef snabbi het volgende:
Voorbeeld
Je wegingsfactor in kolom A (bijvoorbeeld van A2 tot A10), je cijfer in kolom B.(bijvoorbeeld van B2 tot B10).
Je kan nu je totaal berekenen door middel van somproduct(A2:A10;B2:B10) [in het engels sumproduct] en je kunt het totaal van je wegingsfactor berekenen via som(A2:A10).

Je gewogen gemiddelde vormt dus: somproduct(A2:A10;B2:B10)/som(A2:A10)

Hmm, ik heb geprobeerd het totaal aantal punten (voor deling) te berekenen, echter voert Excel mijn formule überhaupt niet uit. Wat is er fout aan deze formule?

1	=SUMPRODUCT(Weging[[#All]];[@[Proefwerk H1]]:OFFSET(E6;;COUNTA(ProgressChartValues)))

Weging[#All] is het totaal van de wegingsfactoren (die zelf een tabel vormen). Bij [@[Proefwerk H1]] beginnen de cijfers. OFFSET(E6;;COUNTA(ProgressChartValues)) berekent de locatie van mijn laatste cijfer d.m.v. de wegingsfactor. Als ik bijvoorbeeld 3 wegingsfactoren heb ingevuld, is COUNTA(ProgressChartValues) 3, waardoor OFFSET(E6;;COUNTA(ProgressChartValues)) de waarde E9 wordt. ProgressChartValues is hierbij een array met alle wegingsfactoren.

//edit: Laat maar, de formule was wel correct. Ik heb een hele stomme fout gemaakt: ik had de cell op 'Text' gezet. Door 'm op 'General' te zetten, kwam alles weer goed.

[ Bericht 2% gewijzigd door PowerData op 18-07-2012 11:23:23 ]

“I'm not about caring; I'm about winning.”
- Harvey Specter
“Take a step back, look at the bigger picture.”

woensdag 18 juli 2012 @ 21:14:56 #80

PowerData

Ik heb nog een probleem, excuses.

Ik zou graag een gemiddelde (gewogen) over een aantal cijfers berekenen via een dynamische tabel, echter wil ik dat bijvoorbeeld wanneer er een 0 waarde staat hij niet wordt meegerekend. Ik vertel wat ik mijn cellen heb.

In de cellen A3 t/m A5 heb ik de namen van de leerlingen staan. In de cellen B3 t/m B5 staan hun gemiddeldes.
Cel B3:

1	=IFERROR(SUMPRODUCT(Weging[#All];[@[Proefwerk 1]]:OFFSET(B3;;COUNTA(ProgressChartValues)))/SUM(Weging[#All]);"")

In de cellen B4 e.d. is alleen de waarde B3 verandert in B4.

De werking is als volgt: via SUMPRODUCT worden de cijfers vermenigvuldigt met de wegingsfactoren (Weging[#All]). Vervolgens worden deze totalen gedeeld door de som van de wegingsfactoren (SUM(Weging[#All]).
De OFFSET berekent dynamisch, aan de hand van het aantal wegingsfactoren, hoeveel cijfers er in de tabel ingevoerd zijn.

In de cellen C3 t/m C5 heb ik de eerste cijfers ingevoerd (van 'proefwerk 1'), in de cellen D3 t/m D5 de cijfers van het tweede proefwerk en de cellen E3 t/m E5 die van het derde proefwerk.

Stel dat ik bijvoorbeeld bij leerling 2 het cijfer van het tweede proefwerk niet invul, hoe zorg ik er dan voor dat het gemiddelde geen rekening houdt met zijn tweede proefwerk, maar wel met dat van de anderen?

“I'm not about caring; I'm about winning.”
- Harvey Specter
“Take a step back, look at the bigger picture.”

woensdag 18 juli 2012 @ 22:02:35 #81

snabbi

quote:
Op woensdag 18 juli 2012 21:14 schreef PowerData het volgende:

Ik heb een voorbeeld voor je gemaakt hoe ik het zou doen. Dit voorbeeld moet je alleen gebruiken wanneer het aantal leerlingen niet heel groot wordt (dus zeg onder de 1000 ofzo).

Het voorbeeld kan je hier vinden: http://www.snirc.nl/extra/leerlingen.xlsx

Wat ik hier doe is het volgende:
Ik bepaal nog steeds de sumproduct op de gebruikelijke wijze, immers vormt een 0 * een wegingsfactor gewoon 0. De truc zit in het tweede deel: SUM((C3:E3>0)*(C1:E3))
Wat ik hier doe is gebruik maken van een array (of vector) binnen 1 cel. De vector voor het deel C3:E3 levert een 1 op wanneer dit groter is dan 1 en een 0 wanneer dit niet groter is dan 0. Deze 1 of 0 vermenigvuldig ik vervolgens met de betreffende wegingsfactor in C1:E1. Hierdoor tellen alleen de wegingsfactoren voor je betreffende leerling mee wanneer het cijfer >0 is.

Voor het gebruik van een array formule moet je bij het invoeren je linker CTRL en SHIFT toets ingedrukt houden terwijl je via enter de formule bevestigd. Gebruik je niet del CTRL + SHIFT dan kan Excel dit niet juist verwerken.

Het gebruik van een array binnen een cel zorgt ervoor dat excel extra berekenigen moet maken per cel. Wanneer je dus veel (echt veel) leerlingen en proefwerken hebt, kan dit trager werken.

Tot slot. Wanneer je geen cijfers invoert zal hij een deling door 0 maken. Dit kan je eventueel oplossen door kolom B te splitsen in 2 kolommen. In de ene kolom maak je de werkelijke berekening (en deze verberg je) en via de andere kolom zet je iets als: =if(iserror(B3):0;B3)
Nu wordt er 0 getoond wanneer je die foutmelding krijgt voor delen door 0.

woensdag 18 juli 2012 @ 23:15:11 #82

PowerData

quote:
Op woensdag 18 juli 2012 22:02 schreef snabbi het volgende:

[..]

Wat ik hier doe is het volgende:
Ik bepaal nog steeds de sumproduct op de gebruikelijke wijze, immers vormt een 0 * een wegingsfactor gewoon 0. De truc zit in het tweede deel: SUM((C3:E3>0)*(C1:E3))
Wat ik hier doe is gebruik maken van een array (of vector) binnen 1 cel. De vector voor het deel C3:E3 levert een 1 op wanneer dit groter is dan 1 en een 0 wanneer dit niet groter is dan 0. Deze 1 of 0 vermenigvuldig ik vervolgens met de betreffende wegingsfactor in C1:E1. Hierdoor tellen alleen de wegingsfactoren voor je betreffende leerling mee wanneer het cijfer >0 is.

Briljant.

Ik ben nieuw met arrays binnen Excel (al heb ik ze wel gebruikt, gezien dat handig is voor dynamische grafieken ed.), maar ik wist nog niet dat je ook een True, False, True array kon maken. Bedankt daarvoor.
Overigens is er iets wat ik nog niet helemaal snap: wat is nou het verschil tussen SUM((C3:E3>0)*(C1:E3)) en SUMPRODUCT(C3:E3>0;C1:E3). De SUMPRODUCT werkt niet, maar ik zou toch denken dat die juist zelfs zonder array zou werken?

In ieder geval, dit is nu het resultaat voor het gemiddelde (in B3), en het werkt.

1	{=IFERROR(SUMPRODUCT(Weging[#All];[@[Proefwerk H1]]:OFFSET(B3;;COUNTA(ProgressChartValues)))/SUM(([@[Proefwerk H1]]:OFFSET(B3;;COUNTA(ProgressChartValues))>0)*Weging[#All]);"")}

Bedankt.

“I'm not about caring; I'm about winning.”
- Harvey Specter
“Take a step back, look at the bigger picture.”

woensdag 18 juli 2012 @ 23:33:37 #83

snabbi

quote:
Op woensdag 18 juli 2012 23:15 schreef PowerData het volgende:
Overigens is er iets wat ik nog niet helemaal snap: wat is nou het verschil tussen SUM((C3:E3>0)*(C1:E3)) en SUMPRODUCT(C3:E3>0;C1:E3). De SUMPRODUCT werkt niet, maar ik zou toch denken dat die juist zelfs zonder array zou werken?

Waarom weet ik niet precies, maar SUMPRODUCT(C3:E3>0) blijft TRUE en FALSE gebruiken ipv 1 en 0. SUMPRODUCT((C3:E3>0)*1) maakt er wel al 1 en 0 van.

Het nadeel van dit toe te passen in je sumproduct deel is dat je het cijfer van je proefwerk kwijt raakt in het 1 en 0 geneuzel. Dit zou je weer moeten oplossen door er zoiets van te maken:
=SUMPRODUCT((C3:E3>0)*(C3:E3);$C$1:$E$1)

Op dit moment heb je nog steeds maar het totaal berekend en niet het deel wat je moet berekenen voor de wegingsfactor. Er is geen verschil tussen SUM en SUMPRODUCT wanneer je alles binnen 1 argument houdt. In mijn formule is het enige verschil dat het deel van de SUM bepaalt door hoeveel gedeeld moet worden.

quote:
Bedankt.

You're welcome

woensdag 18 juli 2012 @ 23:42:31 #84

PowerData

quote:
Op woensdag 18 juli 2012 23:33 schreef snabbi het volgende:

[..]

Waarom weet ik niet precies, maar SUMPRODUCT(C3:E3>0) blijft TRUE en FALSE gebruiken ipv 1 en 0. SUMPRODUCT((C3:E3>0)*1) maakt er wel al 1 en 0 van.

Het nadeel van dit toe te passen in je sumproduct deel is dat je het cijfer van je proefwerk kwijt raakt in het 1 en 0 geneuzel. Dit zou je weer moeten oplossen door er zoiets van te maken:
=SUMPRODUCT((C3:E3>0)*(C3:E3);$C$1:$E$1)

Zoals je al zei is het voor het totaal berekenen niet nodig om een vergelijking als > 0 in te voeren, gezien die automatisch al wegvalt.

Ik bedoelde eigenlijk voor de noemer.

Jij had voorgesteld als noemer SUM((C3:E3>0)*(C1:E3)) te nemen en er dan een array van te maken, wat dus overigens perfect werkt. Zou het niet ook werken om dan in plaats daarvan SUMPRODUCT(((C3:E3>0)*1);$C$1:$E$1) te gebruiken, waarbij je geen (extra) arrays nodig hebt?

//edit: ik heb het getest, en dit werkt ook!

Welke is dan slimmer om te gebruiken? De SUM waarbij je de array met CSE zelf toevoegt, of de SUMPRODUCT die uit zichzelf al met arrays werkt?

“I'm not about caring; I'm about winning.”
- Harvey Specter
“Take a step back, look at the bigger picture.”

woensdag 18 juli 2012 @ 23:55:31 #85

snabbi

Prima, dat zou inderdaad ook werken ja.
Wat is beter, tja ik zou vermoeden dat het beter is geen gebruik te maken van een array formule, omdat ik verwacht dat de interne formules van Excel efficienter zijn geprogrammeerd, maar volgens mij is het het belangrijkste dat het werkt

Zonder array is wel makkelijker invoeren en daardoor minder fout gevoelig.

donderdag 19 juli 2012 @ 00:01:05 #86

PowerData

Oke, ik kijk nog wel. In ieder geval bedankt voor het laten zien dat je met een vergelijking en een array dit probleem gemakkelijk kunt oplossen.

“I'm not about caring; I'm about winning.”
- Harvey Specter
“Take a step back, look at the bigger picture.”

donderdag 19 juli 2012 @ 09:25:12 #87

Deetch

WTF?

Sumproduct met een vergelijking erin geeft altijd true false, je vraagt namelijk of een cel wel of niet aan een bepaalde voorwaarde voldoet. Om van een true false array een 1, 0 array te maken kun je hem inderdaad vermenigvuldigen met 1. =SUMPRODUCT((C3:E3>0)*1) geeft een 1,0 array.

Ik gebruik zelf liever -- ervoor omdat het dan duidelijker is dat je die alleen gebruikt om van true false een 1 of 0 te maken ipv dat je een array daadwerkelijik vermenigvuldigd met een echt getal.
=SUMPRODUCT(--(C3:E3>0))

Mijn ervaring is dat een sumproduct sneller is dan een CSE functie. En verder ziet een sumproduct er natuurlijk vet uit.

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

vrijdag 20 juli 2012 @ 17:44:20 #88

_superboer_

Ik zit met de volgende uitdaging:

Ik heb een sheet met voor elke dag (kolommen) hoeveelste deel van een doos er ontvangen moet worden per product (rijen). Dit is bijvoorbeeld 1,3 of 2,5 of 3. Bijvoorbeeld:

1
2
3
4
5

           1 jan         2 jan         3 jan         4 jan
prod 1         1             2             0             1
prod 2       1.5           1.3             1           1.4
prod 3         2             0           1.2           1.9
prod 4         0             1           1.7           1.2

Nu moet ik kijken wanneer er een afgevulde vrachtwagen met bijvoorbeeld plaats voor 10 dozen kan komen rijden. Probleem hierbij is dat 1,5 doos de ruimte inneemt van 2 dozen. Maar dat prod 2, als de vrachtwagen op 1 jan rijdt en voor 2 dagen meeneemt, niet 4 (roundup(1.5)+roundup(1.3) dozen aan ruimte inneemt, maar 3 want roundup(1.5+1.3). Hoe kan ik dit het makkelijskte doen?

vrijdag 20 juli 2012 @ 18:02:58 #89

ralfie

!Yvan eht nioj

=SUM(ROUNDUP((A2:A5+B2:B5))

invullen en met ctr+shift+enter accepteren

vrijdag 20 juli 2012 @ 18:09:51 #90

_superboer_

quote:
Op vrijdag 20 juli 2012 18:02 schreef ralfie het volgende:
=SUM(ROUNDUP((A2:A5+B2:B5))

invullen en met ctr+shift+enter accepteren

Dank je, met een ,0 bij de roundup lukte het

Alleen hoe bepaal ik nu of ik de som van 1, 2, 3 of misschien wel meer kolommen moet hebben om de vrachtwagen te vullen? Het kan bijvoorbeeld voorkomen dat de vrachtwagen gevuld kan worden met de behoefte van bijvoorbeeld 20 dagen.

zaterdag 21 juli 2012 @ 15:29:46 #91

_superboer_

Niet meer nodig

[ Bericht 99% gewijzigd door _superboer_ op 21-07-2012 16:23:53 ]

maandag 23 juli 2012 @ 08:46:45 #92

Deetch

WTF?

quote:
Op zaterdag 21 juli 2012 15:29 schreef _superboer_ het volgende:
Niet meer nodig

had je het zelf opgelost? Misschien kon iemand anders er nog wat van leren.

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

maandag 23 juli 2012 @ 08:57:38 #93

_superboer_

quote:
Op maandag 23 juli 2012 08:46 schreef Deetch het volgende:

[..]

had je het zelf opgelost? Misschien kon iemand anders er nog wat van leren.

Had het zelf opgelost:
ActiveCell.FormulaR1C1 = _
"=ROUNDUP(SUM('4.1.1. tussenstap krat'!RC:RC[j - 1]),0)"

Moest zijn:

ActiveCell.FormulaR1C1 = _
"=ROUNDUP(SUM('4.1.1. tussenstap krat'!RC:RC[" & j - 1 & "]),0)"

maandag 23 juli 2012 @ 15:41:42 #94

_superboer_

Een nieuw vraagje: doordat mijn macro zichzelf weer start krijg ik na een tijdje een out of stack error. Is er een mogelijkheid om een macro vaak te laten herhalen zonder die error te krijgen?

maandag 23 juli 2012 @ 20:22:13 #95

snabbi

Stack errors worden vermoedelijk veroorzaakt door recursie of door gebruik te maken van een onchange event dat meerdere keren wordt aangeroepen. Recursie is uit te schrijven met behulp van for loops.

donderdag 26 juli 2012 @ 10:23:18 #96

Puala

Vraagje.. Ik heb 2 excel bestanden. Laat ik zeggen bestand A en bestand B.

In bestand A staat in kolom D data. Deze staat in willekeurige volgorde ook in bestand B, kolom A. Echter staat er in bestand B data in kolom B welke ik naar bestand A wil hebben. Maar alleen op de goede plaats iofwel achter dezelfde term als waar het in B ook staat. Hoe doe ik dit?

Dit in Excel 2007, Engels.

donderdag 26 juli 2012 @ 11:22:37 #97

Deetch

WTF?

Onderstaande formule zet in je bestand A, cel E1 en trek je door naar beneden.

=VLOOKUP(D1,[bestandB.xlsx]Sheet1!$A$1:$B$4,2,0)

Ik ben er gemakshalve even vanuit gegaan dat je tabel in bestandB niet verder gaat dan rij 4, anders moet je $B$4 even naar smaak aanpassen.

[ Bericht 72% gewijzigd door Deetch op 26-07-2012 11:58:53 ]

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

vrijdag 27 juli 2012 @ 21:22:52 #98

_superboer_

Als ik enkele van mijn excelbestanden probeer te openen (>15mb) loopt excel (64bit) vast. Als ik deze bestanden echter openen op een virtual machine met excel (32bit) kunnen de bestanden wel geopend worden. Iemand een idee waar dit aan kan liggen?

vrijdag 27 juli 2012 @ 21:37:24 #99

DaFan

quote:
Op vrijdag 27 juli 2012 21:22 schreef _superboer_ het volgende:
Als ik enkele van mijn excelbestanden probeer te openen (>15mb) loopt excel (64bit) vast. Als ik deze bestanden echter openen op een virtual machine met excel (32bit) kunnen de bestanden wel geopend worden. Iemand een idee waar dit aan kan liggen?

http://social.technet.mic(...)5-876e-9d1d46e7975f/
?

zaterdag 28 juli 2012 @ 19:54:56 #100

hendrik2009

Ik heb een macro, waarin ik de waarden wil sommere waarbij aan twee criteria wordt voldaan. Ik wil graag voorkomen dat ik combinaties moet maken van de twee kolommen.

Hoe doe ik onderstaande het meest efficiënt in VBA?
{=SOM((B2:B10="A")*(C2:C10="B")*D2:D10)}

Thanks!

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

» digital corner

» software

» digital corner

» software