Archief - voor iemand die veel van excel kent

Het archief is een bevroren moment uit een vorige versie van dit forum, met andere regels en andere bazen. Deze posts weerspiegelen op geen enkele manier onze huidige ideeën, waarden of wereldbeelden en zijn op sommige plaatsen gecensureerd wegens ontoelaatbaar. Veel zijn in een andere tijdsgeest gemaakt, al dan niet ironisch - zoals in het ironische subforum Off-Topic - en zouden op dit moment niet meer gepost (mogen) worden. Toch bieden we dit archief nog graag aan als informatiedatabank en naslagwerk. Lees er hier meer over of start een gesprek met anderen.
m

maxdevis

Legacy Member
ik zie de logica er niet van in?
in kolom 1 staan letters, en in kolom 2 staan cijfers die op geen enkele manier in verband staan met de cijfers van andere, gelijke letters?

of ben ik mis?
J

Jezeke

Legacy Member
tis moeilijk om uit te leggen, maar ik zal het proberen me een voorbeeld:

in kolom 1 staat de nummer van een muizenkoppel: vb A wil zeggen :het koppel muis 1 en muis 2, B wil zeggen: muis 3 en muis 4,...

als nu in week1 er in nest A 5 jongen geboren worden, zet ik in kolom 2 een nummering van 1 tot 5.
Als in week 2 er in nest B 3 jongen geboren worden, zet ik in kolom 2 achter B een nummering van 1 tot 3.
Als nu in week 3 het koppel A weer vb 2 jongen krijgt, moet ik in kolom 2 de nummering van de vorige jongen van koppel A verderzetten: dus in kolom 2 moet ik 6 en 7 invullen. In week 4 krijgt A weer jongen, in week 5 krijgt B jongen, ....

kheb die file veranderd, nu is het zoals het voorbeeld da ik juist gegeven heb
d

daVinci

Legacy Member
Is het niet het gemakkelijkste om die nummering gewoon zelf bij te houden ipv met een functie? Dan kan je wel in een andere cell bijhouden vanaf welke nummering per koppel je moet beginnen ingeven, dit laatste enkel om het makkelijker te maken.
J

Jezeke

Legacy Member
ik had daar ook al aan gedacht, maar ik moe zoveel ingeven dat da nog altij veel werk is. Het zou veel gemakkelijker gaan als ik da me een functie kon doen
d

daVinci

Legacy Member
Mag het systeem van invoeren een beetje veranderen?

Code: 
A 1
A 2
A 3
B 1
B 2
A 4
A 5

ipv

A 1
  2
  3
B 1
  2 
A 4
  5
S

Suicide Monkey

Legacy Member
Kan je niet gewoon met meedere sheets werken? 1 sheet per koppel en dan kan je gewoon met autoaanvullen het aantal jongen verder aanvullen en de week ernaast zetten :)
J

Jezeke

Legacy Member
nee, ik mag daar niks aan veranderen aan die structuur van die files,
davinci, ik mag het zo ook nie doen, het moet zo zijn zoals ik in begin gevraagd heb
m

maxdevis

Legacy Member
volgens mij kan je in vb wel iets maken waardoor als je in kolom B bv letter A schrijft, dat dan in het vak ernaast (kolom C) het cijfer van A van de vorige week plus 1 komt, maar de rest zal je dan toch nog handmatig moeten invoeren aangezien het aantal dat er bij komt per week niet regelmatig is.
Dus ik zie niet goed waarom je dat dan eigenlijk zou schrijven.

het is dus niet mogelijk wat jij vraagt.
of ik ben nog steeds niet mee, dat kan ook.
d

daVinci

Legacy Member
Ik heb in een excel een functie geschreven die (volgens mij) doet wat jij vraagt. Het is een functie die in de rapte geschreven is, dus niet altijd volgens de regels van de kunst, maar hij werkt wel.

Het is een functie die je in de B kolomplaats (dus waar de getallekes komen) en bevat 3 argumenten.
1. De inputcell, dit is de cell waar mogelijk een letter staat (dus de cell ernaast, via een relatieve cellverwijzing)
2. De startcell, dit is de cell in kolom A vanwaar men moet beginnen kijken naar A's en B's enz. Een absolute cellverwijzing best.
3. De stopcell, dit is de cell in kolom A tot waar men moet kijken naar de A's en de B's. Best een absolute cellverwijzing.

Dit is de code die je nog in een VBA module moet plaatsen:

Code: 
Function Fncttellen(inputcell As Range, startcell As Range, stopcell As Range)
Application.Volatile
rowinput = inputcell.Row
colinput = inputcell.Column
rowstart = startcell.Row
rowstop = stopcell.Row
If rowinput >= rowstart And rowinput <= rowstop Then
    If Cells(rowinput, colinput).Value = "" Then
        Fncttellen = Cells(rowinput - 1, colinput + 1) + 1
    Else
        maximumwaarde = 0
        For i = rowstart To rowinput - 1
            If Cells(i, colinput).Value = inputcell.Value Then
                j = i + 1
                Do Until Cells(j, colinput).Value <> ""
                    j = j + 1
                Loop
                maximumwaarde = Cells(j - 1, colinput + 1)
            End If
        Next
        
        If maximumwaarde = 0 Then
            Fncttellen = 1
        Else
            Fncttellen = maximumwaarde + 1
        End If
    End If
    

Else

Fncttellen = ""

End If

If inputcell.Column <> startcell.Column Or inputcell.Column <> stopcell.Column Then
Fncttellen = "Verschillende kolommen is niet toegestaan"
End If

End Function

Die application.volatile is nodig bij herberekeningen, maar kan bij grote sheets de boel wel een beetje vertragen.

Voorbeeld:
in kolom A staan de A's, de B's enz. in kolom B (=altijd kolom (van A's, B's) +1) komt dan bv. volgende formule.

B1=Fncttellen(A1;$A$1;$A$100)
B2=Fncttellen(A2;$A$1;$A$100)
B3=Fncttellen(A3;$A$1;$A$100)
....

Ik hoop dat je er iets mee bent.
J

Jezeke

Legacy Member
werkt da ook als er meer dan 2 letterkes, dus a, b, c, d ...... zijn? ik snap niks van uwe post, maar ik zal is zien :D, toch bedankt voor alle moeite da ge gedaan hebt :)
J

Jezeke

Legacy Member
ah, kheb eindelijk gevonden hoe ik die code van u moet gebruiken, ik ken niks van excel, van macro's en zo, kwist nog nie eens wa vba was :), maar het werkt, thx he :applause:
J

Jezeke

Legacy Member
wa moet ik in die code veranderen als de kolom met de input cellen nu nie links naast de kolom met de volgnummers sta? als die kolom met de inputcellen nu 2 kolommen verder sta of zo
d

daVinci

Legacy Member
Is de afstand tussen de A's, B's-kolom en de 1,2,3 kolom altijd een vaste waarde, of kan dit verschillen van bestand tot bestand? Indien dit altijd gelijk is, kunnen we gewoon de code aanpassen, anders nemen we een extra argument in de functie dat de afstand tussen de 2 kolommen bepaald.

Volgende regels moeten we dan wijzigen:
regel 9:
huidige code:Fncttellen = Cells(rowinput - 1, colinput + 1) + 1
nieuwe code:Fncttellen = Cells(rowinput - 1, colinput + 2) + 1

regel 18:

huidige code:maximumwaarde = Cells(j - 1, colinput + 1)
nieuwe code:maximumwaarde = Cells(j - 1, colinput + 2)

die 1 (huidig) of 2 (nieuw) geeft de afstand tussen de 2 kolommen aan. Dus die waarde moet je veranderen naargelang je behoefte. Zoals gezegd, moest dit telkens verschillen (bv. tussen verschillende sheets, dit mag NIET verschillen binnen 1 lijst!), dan veranderen we dat in volgende code:

regel1:
huidige code:Function Fncttellen(inputcell As Range, startcell As Range, stopcell As Range)
nieuwe code:Function Fncttellen(inputcell As Range, startcell As Range, stopcell As Range,kolomverschil as integer)

regel 9:
huidige code:Fncttellen = Cells(rowinput - 1, colinput + 1) + 1
nieuwe code:Fncttellen = Cells(rowinput - 1, colinput + kolomverschil) + 1

regel 18:

huidige code:maximumwaarde = Cells(j - 1, colinput + 1)
nieuwe code:maximumwaarde = Cells(j - 1, colinput + kolomverschil)


De oproep van de functie wijzigt dan ook:
nu: B1=Fncttellen(A1;$A$1;$A$100)
nieuw: B1=Fncttellen(A1;$A$1;$A$100;5)

waarbij de 5 het aantal kolommen verschil aangeeft.

Ik hoop dat het duidelijk is, en uw porbleem oplost

PS. moest de 1,2,3 kolom links liggen van de A,B kolom ipv rechts, moet je een minteken ipv een plusteken gebruiken in de aangegeven lijnen.
d

daVinci

Legacy Member
Vergeet mijn vorige post, en verander/voeg toe de rode tekst van onderstaande code:

Code: 
Function Fncttellen(inputcell As Range, startcell As Range, stopcell As Range)
Application.Volatile
rowinput = inputcell.Row
colinput = inputcell.Column
rowstart = startcell.Row
rowstop = stopcell.Row
[COLOR="Red"]kolom = Application.Caller.Column[/COLOR]
If rowinput >= rowstart And rowinput <= rowstop Then
    If Cells(rowinput, colinput).Value = "" Then
        Fncttellen = Cells(rowinput - 1, [COLOR="red"]kolom[/COLOR]) + 1
    Else
        maximumwaarde = 0
        For i = rowstart To rowinput - 1
            If Cells(i, colinput).Value = inputcell.Value Then
                j = i + 1
                Do Until Cells(j, colinput).Value <> ""
                    j = j + 1
                Loop
                maximumwaarde = Cells(j - 1, [COLOR="red"]kolom[/COLOR])
            End If
        Next
        
        If maximumwaarde = 0 Then
            Fncttellen = 1
        Else
            Fncttellen = maximumwaarde + 1
        End If
    End If
    

Else

Fncttellen = ""

End If

If inputcell.Column <> startcell.Column Or inputcell.Column <> stopcell.Column Then
Fncttellen = "Verschillende kolommen is niet toegestaan"
End If

End Function

Laat je iets weten of het werkt volgens jouw eisen?
J

Jezeke

Legacy Member
het werkt precies :), kheb nog wel een vraag: als ik die code in een macro wil invoeren, staat er, als ik een nieuwe macro aanmaak, altijd: Sub End Sub, wa betekent da? ik ken niks van visual basic :)
en ook, kan ik die macro ergens opslaan? want ik heb veel excelfiles die ik moet invullen, en het zou gemakkelijk zijn als ik een nieuwe file open, da ik die functie gewoon in mijn functielijst terug vind
Van excel ken ik ook al niks

hmm da is precies wel zwaar voor de pc, moet die die nummers elke keer opnieuw berekenen als ik die file terug opnieuw open? want hier op het werk hebben sommigen ne trage pc, en als die die file zouden openen, gaan die vastlopen denk ik
J

Jezeke

Legacy Member
ik heb nog een vraag, ik zou in een derde kolom moeten krijgen welk koppel A, B, C, .... juist is, in een 2e sheet sta gegeven welke letters juist welke koppels zijn, kunde mij daar ook bij helpen? al bedankt voor wa ge al gedaan hebt he :niceone: kolom 3 moet alleen ingevuld worden in de rijen waar er in kolom 1 een letter sta, waar er geen letter sta, moet er in kolom 3 niks ingevuld worden

Code: 
      sheet1                                    sheet2    
  kolom1    kolom2    kolom3              kolom1               kolom2  
     A           1                            A        mannetje1 * vrouwtje1
                 2                            B        mannetje2 * vrouwtje2
                 3                            C        mannetje3 * vrouwtje3
     B           1                            D        mannetje4 * vrouwtje4
                 2                            E        mannetje5 * vrouwtje5
d

daVinci

Legacy Member
Vraag 1 - Sub en end sub: eigenlijk moet je die daar niet zetten. Je moet die in een module zetten. Sub en End sub geeft het begin en einde van een procdure aan. Maar wij hebben hier een functie, vandaar function en end function. Het verschil is (in het kort) dat een procedure bij oproep geen uitkomst doorgeeft, en een functie wel. Ik zal straks een voorbeeld posten waar je die wel moet zetten.

Vraag 2 - hergebruik: dit is mogelijk, maar dan moeten we die code exporeteren naar een *.xla bestand en dat via invoegtoepassingen inladen in excel. Ik heb het zelf nog nooit moeten doen, maar zal er eens achter kijken hoe dit juist moest (weet het uit mijn hoofd maar ongeveer).

Vraag 3 - performance kwestie: Heb in een vorige post al aangegeven dat dit kan leiden tot prestatie verlies. Waarom? Standaard laat Excel de functies herberekenen als er iets aan de argumenten wijzigt. Maar aangezien we hier soms een wijziging hebben in cellen die niet tot de inputcellen behoort, maar wel het resultaat van de functie beïnvloedt, moeten we die application.volatile gebruiken, willen we zonder manuele interventie steeds een juist resultaat bekomen. Ik kan dit wijzigen, maar dan gaat ge bij wijzigingen in eerdere waardes die formules moeten laten herberekenen. Ik zal daar straks ook eens concreet naar kijken.

Vraag 4 - ophalen gegevens: dit is perfect mogelijk via een vlookup of match functie. Wederom zal ik straks een voorbeeld posten hoe dit kan gedaan worden.

Grtz,

daVinci

edit: misschien is het makkelijker om via msn een paar zaken door te nemen. msn: davinci2k3 AT hotmail DOT com
d

daVinci

Legacy Member
Nog een paar vraagjes:

-is de naam van die functie goed? Want die kan ik veranderen in een andere naam en dit zonder problemen.
-ivm die constante herberekening. Dit kan afgezet worden, maar dan moet je regelmatig om alles up to date te houden eens ctrl-alt-F9 drukken. Ge ziet dat er een afwegening gemaakt moet worden tussen performance en gebruiksgemak.

Voor het ophalen van die omschrijving kan je volgende formule gebruiken:

C1=ALS(ISFOUT(VERT.ZOEKEN(A1;Blad2!$A$1:$B$4;2;ONWAAR));"";VERT.ZOEKEN(A1;Blad2!$A$1:$B$4;2;ONWAAR))

Hier moet je uiteraard nog wel de juiste bereiken invullen zodanig dat hij de gegevens op de juiste plaats gaat zoeken. Moest je hiermee problemen hebben laat het dan maar weten.

*Het xla-bestand, dat zorgt voor het ter beschikking stellen van in alle excel bestanden: http://users.pandora.be/da_vinci/fncttellen.xla

Deze file zet je in een bepaalde folder (zorg dat excel niet actief is), start dan excel op (blank sheet, dus geen bestand openen) en ga naar menu Extra -> Invoegtoepassingen. Kies daar bladeren, en verwijs naar de file in de folder die je juist hebt aangemaakt. Daarna komt dit voor in de lijst van invoegtoepassingen, vink deze dan ook aan en klik OK. Nu moet deze functie beschikbaar zijn in alle excel bestanden.


*een voorbeeld (reeds met opzoeken van gegevens op een ander blad):
http://users.pandora.be/da_vinci/TNGtestnummeringA123B1A4XLAVERSION.xls

Moest er nog iets niet duidelijk zijn, of foutief laat dan maar iets weten.
Het archief is een bevroren moment uit een vorige versie van dit forum, met andere regels en andere bazen. Deze posts weerspiegelen op geen enkele manier onze huidige ideeën, waarden of wereldbeelden en zijn op sommige plaatsen gecensureerd wegens ontoelaatbaar. Veel zijn in een andere tijdsgeest gemaakt, al dan niet ironisch - zoals in het ironische subforum Off-Topic - en zouden op dit moment niet meer gepost (mogen) worden. Toch bieden we dit archief nog graag aan als informatiedatabank en naslagwerk. Lees er hier meer over of start een gesprek met anderen.
Terug
Bovenaan