[Excel] Formule om controlegetal te bepalen via mod10

[Kamikazi]

Ik moet voor een import via EDI zogenoemde EAN codes (vergelijkbaar met de getallen van barcodes).

Nu heb ik een lijst in Excel met deze getallen:

290000000001
290000000002
290000000003

etc etc.

Via een MOD10 (of modulus 10 of luhn check als ik het goed heb gezien in Excel) het controlegetal hierbij krijgen. Het resultaat moet dus in dit geval worden:

2900000000018
2900000000025
2900000000032

Ik heb echter geen flauw idee hoe ik dit in Excel voor elkaar moet gaan krijgen. De LUHN check werkt zo maar hoe krijg ik dat in Excel voor elkaar?

[Anoniem: 9449]

Wat is EDI ?? (heeft zeker iets met E en/of G te maken)

[Kamikazi]

EDI kan je zien als een soort voorloper van XML. Is een manier om gegevens te kunnen uitwisselen tussen verschillende systemen (bijv. bestellingen, facturen etc etc)

[__fred__]

http://www.barcodeisland.com/ean13.phtml

EDI is Electronic Data Interchange. Het uitwisselen van gegevens tussen meerdere automatische systemen. Lekker algemeen dus.

MOD10 is een simpele manier om een controlegetal toe te voegen aan een gewoon getal waardoor er een kans van 90% ontstaat dat een eventueel fout getal herkend kan worden. Alleen juist het omwisselen van digits wordt hier niet herkend. (en dat is een vaak gemaakte menselijke fout).
LUHN-10 is een aangepaste gewogen versie van het modulo algoritme dat hier geen last van heeft.

EAN-13 gebruikt een aangepaste versie van LUNH-10 om zijn 13e controlegetal te berekenen.

voorbeeldsheet: http://www.spuij.nl/files/ean.xls

In cel A1 het 12 cijferige EAN getal invoeren, in B5 komt het 13e controlegetal.

[ Voor 8% gewijzigd door __fred__ op 31-07-2004 13:07 ]

[Anoniem: 112359]

Het lijkt mij het gemakkelijkste om een zelfgeschreven VBA functie te bouwen en die vanuit Excel aan te roepen. Je moet met niet vragen hoe de precieze code zou moeten zijn, maar de volgende dingen die je nodig hebt zijn zeker mogelijk met VBA:

* Lezen van de inhoud in b.v. Cel A1
* Het uitrekenen van het controlegetal (door de ingelezen string in karakters op te splitsen (do while loopje???) en te sommeren na vermenigvuldiging)
* Het berekende controlegetal weergeven in de cel waaruit de functie werd aangeroepen

Je kunt met VBA ook controleren of de ingevoerde waarde in een cel voldoet aan de eisen (lengte, bepaald begingetal enz) en eventueel een foutmelding retourneren.

Het lijkt mij al met al redelijk standaard VBA stuff en dus niet al te moeilijk. Door uiteindelijk je VBA module te exporteren naar een .xla bestand en dit in te laden tijdens het opstarten van Excel kun je de functie direct vanuit elk willekeurig werkblad aanroepen.

Succes ermee

[__fred__]

Anoniem: 112359 schreef op 31 juli 2004 @ 13:57:
Het lijkt mij het gemakkelijkste om een zelfgeschreven VBA functie te bouwen en die vanuit Excel aan te roepen. Je moet met niet vragen hoe de precieze code zou moeten zijn, maar de volgende dingen die je nodig hebt zijn zeker mogelijk met VBA:

* Lezen van de inhoud in b.v. Cel A1
* Het uitrekenen van het controlegetal (door de ingelezen string in karakters op te splitsen (do while loopje???) en te sommeren na vermenigvuldiging)
* Het berekende controlegetal weergeven in de cel waaruit de functie werd aangeroepen

Je kunt met VBA ook controleren of de ingevoerde waarde in een cel voldoet aan de eisen (lengte, bepaald begingetal enz) en eventueel een foutmelding retourneren.

Het lijkt mij al met al redelijk standaard VBA stuff en dus niet al te moeilijk. Door uiteindelijk je VBA module te exporteren naar een .xla bestand en dit in te laden tijdens het opstarten van Excel kun je de functie direct vanuit elk willekeurig werkblad aanroepen.

Succes ermee

toon volledige bericht

zoiets: ;-)

code:

Function EANControle(EAN12 As Range)

Dim tEAN12 As String
Dim d1, d2, d3, d4, d5, d6, d7, d8, d9, d10, d11, d12 As Integer
Dim EANSom, EANModulo As Integer

tEAN12 = EAN12(1, 1)
tEAN12 = String(12 - Len(tEAN12), "0") + tEAN12

d1 = Mid(tEAN12, 1, 1)
d2 = Mid(tEAN12, 2, 1)
d3 = Mid(tEAN12, 3, 1)
d4 = Mid(tEAN12, 4, 1)
d5 = Mid(tEAN12, 5, 1)
d6 = Mid(tEAN12, 6, 1)
d7 = Mid(tEAN12, 7, 1)
d8 = Mid(tEAN12, 8, 1)
d9 = Mid(tEAN12, 9, 1)
d10 = Mid(tEAN12, 10, 1)
d11 = Mid(tEAN12, 11, 1)
d12 = Mid(tEAN12, 12, 1)

EANSom = d1 + 3 * d2 + d3 + 3 * d4 + d5 + 3 * d6 + d7 + 3 * d8 + d9 + 3 * d10 + d11 + 3 * d12 - 1
EANModulo = 9 - EANSom Mod 10

EANControle = EAN12(1, 1) * 10 + EANModulo

End Function

[Anoniem: 30225]

Kamikazi schreef op 31 juli 2004 @ 11:57:
Ik moet voor een import via EDI zogenoemde EAN codes (vergelijkbaar met de getallen van barcodes).

Nu heb ik een lijst in Excel met deze getallen:

290000000001
290000000002
290000000003

etc etc.

Via een MOD10 (of modulus 10 of luhn check als ik het goed heb gezien in Excel) het controlegetal hierbij krijgen. Het resultaat moet dus in dit geval worden:

2900000000018
2900000000025
2900000000032

Ik heb echter geen flauw idee hoe ik dit in Excel voor elkaar moet gaan krijgen. De LUHN check werkt zo maar hoe krijg ik dat in Excel voor elkaar?

toon volledige bericht

Ik weet niet wat je wilt hoor, maar het voorbeeld wat je geeft klopt niet.
Bij de "modulus 10"-checksum, maakt de checksum ook deel uit van het getal. Dit is bij het "LUHN-10" algoritme niet het geval, omdat de som altijd deelbaar moet zijn door 10.
Dit is wat ik op maak uit de link van het artikel.

Simpele Modulus 10

code:

290000000001  --> 2+9+1 = 12 --> controlegetal = 2
290000000002  --> 2+9+2 = 13 --> controlegetal = 3
290000000003  --> 2+9+3 = 14 --> controlegetal = 4

LUHN-10

code:

290000000001
2*2   9*1   0*2   0*1   0*2   0*1   0*2   0*1   0*2   0*1   0*2   1*1
 4  + 1+0 +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  1  = 6

6 is niet deelbaar door 10. De code is dus niet geldig


290000000002
2*2   9*1   0*2   0*1   0*2   0*1   0*2   0*1   0*2   0*1   0*2   1*2
 4  + 1+0 +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  2  = 7

7 is niet deelbaar door 10. De code is dus niet geldig


290000000001
2*2   9*1   0*2   0*1   0*2   0*1   0*2   0*1   0*2   0*1   0*2   1*3
 4  + 1+0 +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  0  +  3  = 8

8 is niet deelbaar door 10. De code is dus niet geldig

EAN-13

code:

290000000001
SomEven = 9+0+0+0+0+1 = 10
10*3 = 30
SomOnEven = 2+0+0+0+0+0 = 2
Totaal = 30+2 = 32
32+X=40 (eerste veelvoud van 10)  --> controlegetal = 8


290000000002
SomEven = 9+0+0+0+0+2 = 11
11*3 = 33
SomOnEven = 2+0+0+0+0+0 = 2
Totaal = 33+2 = 35
35+X=40 (eerste veelvoud van 10)  --> controlegetal = 5


290000000003
SomEven = 9+0+0+0+0+3 = 12
12*3 = 36
SomOnEven = 2+0+0+0+0+0 = 2
Totaal = 36+2 = 38
38+X=40 (eerste veelvoud van 10)  --> controlegetal = 2

Wat jij dus nodig hebt is EAN-13.

Visual Basic:

    Function Append_EAN_Checksum (RawString as String)
    Dim    Position as Integer
    Dim    CheckSum as Integer

    CheckSum = 0
    For Position = 2 to 12 step 2
          Checksum = Checksum + Val(Mid$(RawString, Position, 1))
    Next Position
    CheckSum = CheckSum * 3
    For Position = 1 to 11 Step 2
         CheckSum = CheckSum + Val(Mid$(RawString, Position, 1))
    Next Position
    CheckSum = CheckSum Mod 10
    CheckSum = 10 - CheckSum
    If CheckSum = 10 Then
         CheckSum = 0
    End If
    Append_Ean_Checksum = RawString & Format$(CheckSum, "0")
    End Function

__fred__ schreef op 31 juli 2004 @ 12:53:
MOD10 is een simpele manier om een controlegetal toe te voegen aan een gewoon getal waardoor er een kans van 90% ontstaat dat een eventueel fout getal herkend kan worden. Alleen juist het omwisselen van digits wordt hier niet herkend. (en dat is een vaak gemaakte menselijke fout).
LUHN-10 is een aangepaste gewogen versie van het modulo algoritme dat hier geen last van heeft.

Dit klopt niet wat je hier zegt.

[ Voor 11% gewijzigd door Anoniem: 30225 op 31-07-2004 19:29 . Reden: Ean-13 vb code toegevoegd. ]

[__fred__]

Anoniem: 30225 schreef op 31 juli 2004 @ 19:28:

Dit klopt niet wat je hier zegt.

Behalve dat het wat kort door de bocht was, wat klopt er niet?

[Anoniem: 30225]

__fred__ schreef op 31 juli 2004 @ 12:53:
MOD10 is een simpele manier om een controlegetal toe te voegen aan een gewoon getal waardoor er een kans van 90% ontstaat dat een eventueel fout getal herkend kan worden. Alleen juist het omwisselen van digits wordt hier niet herkend. (en dat is een vaak gemaakte menselijke fout).
LUHN-10 is een aangepaste gewogen versie van het modulo algoritme dat hier geen last van heeft.

__fred__ schreef op 01 augustus 2004 @ 16:47:
[...]
Behalve dat het wat kort door de bocht was, wat klopt er niet?

MOD10
De kans is niet 90% dat een eventueel fout getal herkend wordt.
Je kunt getal x met 1 verhogen en getal y met 1 verlagen, waardoor dit niet opgemerkt wordt.
Er is niet na te gaan welk getal er fout is.

LUHN-10
97.8% kans dat twee omgekeerde getallen naast elkaar gedetecteerd kan worden.
90% kans dat een willekeurige fout gedetecteerd wordt.

[__fred__]

Anoniem: 30225 schreef op 02 augustus 2004 @ 19:26:

MOD10
De kans is niet 90% dat een eventueel fout getal herkend wordt.
Je kunt getal x met 1 verhogen en getal y met 1 verlagen, waardoor dit niet opgemerkt wordt.
Er is niet na te gaan welk getal er fout is.

Als je 10000x een willekeurig getal van 12 cijfers voor het EAN getal en een willekeurig getal voor het controle getal trekt, dan zul je zien dat MOD10 in 90% van de gevallen de fout "ontdekt". Dat wil ik ook nog wel wiskundig bewijzen voor je, als je het niet direct gelooft.
Ik heb juist gezegd dat het in specifieke gevallen slecht presteert, zoals het verwisselen van twee getallen (0 procent) of het ophogen van één getal en het verlagen van een ander getal (0 procent).

Anoniem: 30225 schreef op 02 augustus 2004 @ 19:26:
LUHN-10
97.8% kans dat twee omgekeerde getallen naast elkaar gedetecteerd kan worden.
90% kans dat een willekeurige fout gedetecteerd wordt.

Dit klopt en heb ik volgens mij ook nooit tegengesproken.

[Anoniem: 30225]

__fred__ schreef op 03 augustus 2004 @ 09:00:
[...]

Als je 10000x een willekeurig getal van 12 cijfers voor het EAN getal en een willekeurig getal voor het controle getal trekt, dan zul je zien dat MOD10 in 90% van de gevallen de fout "ontdekt". Dat wil ik ook nog wel wiskundig bewijzen voor je, als je het niet direct gelooft.

Oke, kun je dat met bewijsregels aantonen?