Controlegetal berekening

[Rik van Dorst]

Voor onze leden hebben wij een aansluitingsnummer inclusief controlegetal ingevoerd. De bedoeling van het controlegetal is dat een verkeerde invoer van het nummer voorkomen wordt. Voor het aansluitingsnummer zijn 9 posities gereserveerd en ziet er bijvoorbeeld als volgt uit: 0 9 8 7 6 5 4 3 8
Het aansluitingsnummer is als volgt samengesteld: als a0, a1,..a8 ;
a0 - a1: de regiocode, mogelijke waarden : 00 t/m 14 en 25;
a2 - a7: het nummer ;
a8 : het controlegetal volgens de berekening:
(3 x a0)+(7 x a1)+(1 x a2)+(3 x a3)+(7 x a4)+(1 x a5)+(3 x a6)+(7 x a7). Deel de uitkomst door 10.
Wanneer de rest van deze deling 0 is, moet het controlegetal 0 zijn. Wanneer de rest van deze deling ongelijk aan 0 is, moet het controlegetal gelijk zijn aan 10 minus de rest van de deling.
Voorbeeld:
Aansluitingsnummer is 098765438
(3 x 0) + (7 x 9) + (1 x 8) + (3 x 7) + (7 x 6) + (1 x 5) + (3 x 4) + (7 x 3) = 172 : 10 = 17
Rest = 2. Controlegetal is 10 – 2 = 8

Nu wordt er in Excel het aansluitingsnummer handmatig ingevoerd waarop een controle losgelaten moet worden. Een collega van mij heeft de volgende formule gemaakt:

=ALS(ISLEEG(A2);"";ALS(LENGTE(A2)=9;ALS(RECHTS((10-RECHTS((DEEL(A2;1;1)*3+DEEL(A2;2;1)*7+DEEL(A2;3;1)*1+DEEL(A2;4;1)*3+DEEL(A2;5;1)*7+DEEL(A2;6;1)*1+DEEL(A2;7;1)*3+DEEL(A2;8;1)*7);1));1)=DEEL(A2;9;1);"JUIST";"ONJUIST");ALS(LENGTE(A2)=8;ALS(RECHTS((10-RECHTS((DEEL(A2;1;1)*7+DEEL(A2;2;1)*1+DEEL(A2;3;1)*3+DEEL(A2;4;1)*7+DEEL(A2;5;1)*1+DEEL(A2;6;1)*3+DEEL(A2;7;1)*7);1));1)=DEEL(A2;8;1);"JUIST";"ONJUIST");ALS(LENGTE(A2)=7;ALS(RECHTS((10-RECHTS((DEEL(A2;1;1)*1+DEEL(A2;2;1)*3+DEEL(A2;3;1)*7+DEEL(A2;4;1)*1+DEEL(A2;5;1)*3+DEEL(A2;6;1)*7);1));1)=DEEL(A2;7;1);"JUIST";"ONJUIST");"ONJUIST"))))

Mijn vraag is kan bovenstaande formule korter gemaakt worden? Voor de zekerheid heb ik een voorbeeld toegevoegd van de controleberekening.

 

[Ginger]

Rik van Dorst, Die formule zal je niet snel korter krijgen... Óf je moet er natuurlijk een UDF voor schrijven! Laat ik dat nou ff voor je gedaan hebben....

Function Aansluitnr(sAnr As String) As Boolean
Dim iTotaal As Integer
Dim x As Integer

    sAnr = Replace(sAnr, "-", "")
    If Len(sAnr) <> 9 And Left(sAnr, 1) <> 0 Then sAnr = 0 & sAnr
    x = 1
    
    For i = 1 To 8
        iTotaal = iTotaal + Mid(sAnr, i, 1) * Choose(x, 3, 7, 1)
        x = x + 1: If x = 4 Then x = 1
    Next i
    
    Aansluitnr = (Right(10 - (iTotaal Mod 10), 1) = Right(sAnr, 1))

End Function

De syntax in je sheet wordt dan =aansluitnr([celadres of directe ingave]). De bovenstaande functie plaats je in een standaard module van je workbook. De functie kan jouw notatie aan waarbij je het nummer ziet zoals je het ziet door de celopmaak. Of dat je echt het nummer zo (01-123456-8) moet opgeven in de sheet.

Ga d'r maar 'ns mee testen en kijk of je er iets mee kan...

Groet, Leo

P.s. Ik heb deze functie ff vlug geschreven, dus er zouden nog foutjes in kunnen zitten!

 

[Rik van Dorst]

Rik van Dorst, Die formule zal je niet snel korter krijgen... Óf je moet er natuurlijk een UDF voor schrijven! Laat ik dat nou ff voor je gedaan hebben....

Function Aansluitnr(sAnr As String) As Boolean
Dim iTotaal As Integer
Dim x As Integer

    sAnr = Replace(sAnr, "-", "")
    If Len(sAnr) <> 9 And Left(sAnr, 1) <> 0 Then sAnr = 0 & sAnr
    x = 1
    
    For i = 1 To 8
        iTotaal = iTotaal + Mid(sAnr, i, 1) * Choose(x, 3, 7, 1)
        x = x + 1: If x = 4 Then x = 1
    Next i
    
    Aansluitnr = (Right(10 - (iTotaal Mod 10), 1) = Right(sAnr, 1))

End Function

De syntax in je sheet wordt dan =aansluitnr([celadres of directe ingave]). De bovenstaande functie plaats je in een standaard module van je workbook. De functie kan jouw notatie aan waarbij je het nummer ziet zoals je het ziet door de celopmaak. Of dat je echt het nummer zo (01-123456-8) moet opgeven in de sheet.

Ga d'r maar 'ns mee testen en kijk of je er iets mee kan...

Groet, Leo

P.s. Ik heb deze functie ff vlug geschreven, dus er zouden nog foutjes in kunnen zitten!

Beste Leo,

Ziet er goed uit, trouwens zeer snel gemaakt, alvast bedankt. Voor het testen van de functie heb ik wat extra aansluitingsnummers in de sheet gezet en dan gaat het bij sommige aansluitingsnummers niet goed. Als je nog eens de gelegenheid heb kan je dan naar bijgaande sheet kijken.

Groet, Rik

 

[Ginger]

...en voor de aardigheid zelf nog wat 'debugging' gedaan en daarbij nog een functie geschreven die het controlegetal kan berekenen. Zie bijlage.

Groet, Leo

 

[Ginger]

Ghi... Ik zie dat we ongeveer tegelijk een bericht hebben geplaatst. Doe mij een plezier en test zelf ff met de net geplaatste bijlage. Ik hoor dan wel als er nog problemen zijn of dat die met deze bijlage direct zijn opgelost.

Groet, Leo

 

[Rik van Dorst]

Hallo Leo,

Ik heb een en ander getest bij de meeste aansluitingsnummer werkt het fantastisch. Echter als het aansluitingsnummer met 00- begint dan gaat de controle niet goed. Als basis van de test heb ik 2e temp_ControleAansluitingsnummer.xls gebruikt en deze heb ik voor de test hernoemd. De sheet heb ik bijgevoegd. Ik zat trouwens een tijd naar de code te staren en ik heb daarna moeten vaststellen dat ik een leek ben. Dus als je nog in de gelegenheid ben...

Alvast bedankt,
Rik

 

[Thoralf]

Hallo Rik,

Als je een aansluitnummer volledig intypt, dus met de koppelstreepjes en de voorloopnullen zoals bijv 00-800087-9, dan levert de volgende formule bij een juist aansluitnummer de waarde 0 op, bij een onjuist aansluitnummer komt er een waarde ongelijk aan 0 te staan.
=REST(SOMPRODUCT(WAARDE((DEEL(SUBSTITUEREN(A1;"-";"");{1;2;3;4;5;6;7;8;9};1))*{3;7;1;3;7;1;3;7;1}));10)
Er is hier uitgegaan van A1 bevat het aansluitnummer.

En mocht je er toe besluiten de aansluitnummers niet volledig, dus zonder de koppeltekens op te geven, dus als getal, dan voldoet de volgende formule:
=REST(SOMPRODUCT(WAARDE((DEEL(TEKST(A1;"000000000");{1;2;3;4;5;6;7;8;9};1))*{3;7;1;3;7;1;3;7;1}));10)

 

[Thoralf]

Hallo Rik,

Doordat de site gisteravond een database error gaf kon ik een aanvulling niet meer plaatsen, daarom die aanvulling dus nu maar.
Als je het aansluitnummer als getal wilt invullen, dus 00-800087-9 als het getal 80000879,
dan volstaat de formule
=REST(SOMPRODUCT((DEEL(TEKST(A1;"000000000");{1;2;3;4;5;6;7;8;9};1))*{3;7;1;3;7;1;3;7;1});10)

Overigens de formule in mijn voige bericht kan nog net even korter, de WAARDE-functie kan eruit, zodat hij dan wordt:
=REST(SOMPRODUCT((DEEL(SUBSTITUEREN(A1;"-";"");{1;2;3;4;5;6;7;8;9};1))*{3;7;1;3;7;1;3;7;1});10)

 

[Ginger]

Rik van Dorst, Het probleem zit 'm eigenlijk niet zozeer in de code. Ik denk dat je moet afstappen van jouw methode van invoer. Het 'aanvullen' van een waarde dmv een format is database-technisch volkomen onjuist! Zet je cellformat op text om de voorloop nul vast te kunnen houden en daarbij via Data > Validation > Text Length de verplichte invoer van 9 tekens. Eventueel zou er dan nog via het Worksheet_Change-event wat geschreven kunnen worden om de invoer om te zetten naar invoer met streepjes. Ik ben er namelijk bang voor dat je 'nummer' (= het 2e deel in de string) óók met nullen kan beginnen. Dan loopt mijn functie namelijk wéér vast. Hier is dan bijna niets meer op te verzinnen...

Hierbij in ieder geval de code om de 1e 2 nullen (via jouw 'format methode') wel aan te kunnen.

Function Aansluitnr(sAnr As String) As Boolean
Dim iTotaal As Integer
Dim x As Integer

    sAnr = Replace(sAnr, "-", "")
    [COLOR="Blue"]If Len(sAnr) = 7 And Left(sAnr, 1) <> 0 Then sAnr = "00" & sAnr[/COLOR]
    If Len(sAnr) = 8 And Left(sAnr, 1) <> 0 Then sAnr = 0 & sAnr
    x = 1
    
    For i = 1 To 8
        iTotaal = iTotaal + Mid(sAnr, i, 1) * Choose(x, 3, 7, 1)
        x = x + 1: If x = 4 Then x = 1
    Next i
    
    Aansluitnr = (Right(10 - (iTotaal Mod 10), 1) = Right(sAnr, 1))

End Function

Groet, Leo

@Thoralf, Zéér fraaie formule oplossing!!! :thumb:

 

[Rik van Dorst]

Heren,

Heel hartelijk dank voor de aangedragen oplossingen.

Groet,
Rik

 

[Thoralf]

Leo,

@Thoralf, Zéér fraaie formule oplossing!!!

Dank voor compliment.
De betrokken formules laten zich eenvoudig aanpassen voor ander controlegetal berekeningen. Ik denk dat iemand met wat ervaring daarin zijn weg wel zal weten te vinden.

 

[bandito bob]

Hallo Rik,

Als je een aansluitnummer volledig intypt, dus met de koppelstreepjes en de voorloopnullen zoals bijv 00-800087-9, dan levert de volgende formule bij een juist aansluitnummer de waarde 0 op, bij een onjuist aansluitnummer komt er een waarde ongelijk aan 0 te staan.
=REST(SOMPRODUCT(WAARDE((DEEL(SUBSTITUEREN(A1;"-";"");{1;2;3;4;5;6;7;8;9};1))*{3;7;1;3;7;1;3;7;1}));10)
Er is hier uitgegaan van A1 bevat het aansluitnummer.

En mocht je er toe besluiten de aansluitnummers niet volledig, dus zonder de koppeltekens op te geven, dus als getal, dan voldoet de volgende formule:
=REST(SOMPRODUCT(WAARDE((DEEL(TEKST(A1;"000000000");{1;2;3;4;5;6;7;8;9};1))*{3;7;1;3;7;1;3;7;1}));10)

Zeker goed gevonden:thumb:

Hij kan wel iets korter, de functie WAARDE() kan er tussenuit. Dit wordt door de berekening automatisch gedaan.

 

[snb]

of

Function Aansluitnr(sAnr As String)
  For j = 1 To 8
    Aansluitnr = Aansluitnr + Mid(Format(sAnr, "000000000"), j, 1) * Choose(j, 3, 7, 1, 3, 7, 1, 3, 7)
  Next
  Aansluitnr = Right(sAnr, 1) Mod (Aansluitnr Mod 10)
End Function