BetalingsID udregning i Excel
Jens
Findes der en kvik Excel bruger, som kan lave en modulus beregning i en
formel til mig ? (Har minus forstand på Excel).
Jeg skal bruge det til at udskrive fakturaer fra Excel (midlertidigt), da
endeligt ERP system endnu ikke er oppe at køre. (Det har funktionaliteten
indbygget).
Se evt. her hvordan det beregnes:
http://www.dgprintservice.dk/uploads/FI-kort_manual_KA71(2).pdf
Jeg har følgende celler, hvor tallene skal tages fra:
Kundenr. (Består af kundens tlf.nr.)
Fakturanr. (Fortløbende fakturanr. f.eks. 1001)
Betalings id er så en sammensætning af Fakturanr. + kundenr. + modulus10
beregnet kontrolciffer og skal fylde 15 cifre.
Resultatet skulle så gerne give dette: +71<betalingsid+kreditornr.< (Jeg har
mit eget kreditornr.)
Er der nogen der kan hjælpe ?
På forhånd tak!
:-) Jens
Martin
En lille søgning på "betalingsident excel" på google giver følgende
<http://www.eksperten.dk/spm/597402>
og lidt videre giver dette
<http://www.tbdl.dk/excel/betalingsident.xls>
Jan Kronsell
"Jens" <jens.joensson@_FJERNDETTE_gmail.com> skrev i en meddelelse
news:474d59c8$0$90273$1472...@news.sunsite.dk...
>Jweg vil anbefale en VBA løsning i stedet. Men den bliver heller ikke kort
>:-). Problemt er at beregningen skal udføres i flere omgange, fordi
>tværsummer over 10 skal reduceres. Åbn det ark, du skal foretage dine
>beregninger i. Tryk Alt+F11. Vælg Insert - Module. Kopier nedenstående
>kode til dit modul:
Function Mod10(tl As String) As Byte
'Erklær nødvendige variable
Dim c(13) As Integer
Dim er As Integer
Dim md As Byte
' Læg hvert ciffer ind i variabel og gang med 1 eller 2
c(13) = Mid(tl, 14, 1) * 2
c(12) = Mid(tl, 13, 1)
c(11) = Mid(tl, 12, 1) * 2
c(10) = Mid(tl, 11, 1)
c(9) = Mid(tl, 10, 1) * 2
c(8) = Mid(tl, 9, 1)
c(7) = Mid(tl, 8, 1) * 2
c(6) = Mid(tl, 7, 1)
c(5) = Mid(tl, 6, 1) * 2
c(4) = Mid(tl, 5, 1)
c(3) = Mid(tl, 4, 1) * 2
c(2) = Mid(tl, 3, 1)
c(1) = Mid(tl, 2, 1) * 2
c(0) = Mid(tl, 1, 1)
' Find tværsummen af variabler, større end 10
For i = 0 To 13
If c(i) > 9 Then
c(i) = CInt(Left(c(i), 1)) + CInt(Right(c(i), 1))
End If
Next
' Find tværsummen af alle variable
er = 0
For i = 0 To 13
er = er + c(i)
Next
' Beregn 10 minus resten af tværsummen delt med 10
' Og skriv resultatet i regnearket
Mod10 = 10 - er Mod 10
End Function
Du kan nu beregne kontrolcifferet ved at skrive =Mod10(a1), hvor A1 skal
være den celle, hvor du har de første 14 cifre. Husk, at hvis de 14 cifre
starter med 0, skal de indtastes med foranstillet ' (enkelt anførselestegn)
for at koden virker.
Jan
Hans Terkelsen
"Jens" <jens.joensson@_FJERNDETTE_gmail.com> wrote in message news:474d59c8$0$90273$1472...@news.sunsite.dk...
Hej Jens.
En regnearksløsning til at finde kontrolcifret, modulus10, ud fra de 14 andre cifre i A1.
=REST(-SUMPRODUKT(REST(HELTAL(MIDT(TEKST(A1;GENTAG(0;14));RÆKKE($1:$14);1)*(1+REST(RÆKKE($2:$15);2))*1,1);10));10)
Foranstillede nuller er ikke nødvendige.
Der var ingen fejl blandt 10000 tilfældige, men ... :-)
Hilsen Hans.
Flemming
> andre cifre i A1.
>
> =REST(-SUMPRODUKT(REST(HELTAL(MIDT(TEKST(A1;GENTAG(0;14));RÆKKE($1:$14);1)*(1+REST(RÆKKE($2:$15);2))*1,1);10));10)
>
Det er elegant med en regnearksløsning a la denne her, synes jeg. For
eksempel med et betalingsid på 1 giver formlen desværre 8, mens det rigtige
er 2. Har ikke helt gennemskuet hvad problemet er, men tror det er noget med
at REST(-x;10) ikke altid giver tværsummen.
Jeg blev imidlertid inspireret, og lavede denne her:
=1+REST(SUM(VÆRDI(MIDT($A$1;RÆKKE($1:$14);1))*
TRANSPONER({1;2;1;2;1;2;1;2;1;2;1;2;1;2}))-1;9)
Matrix-formel, dvs. afsluttes med Shift-Ctrl, så den færdige formel omgives
med {} parenteser.
A1 skal indeholde betalingsid som tekst, og der skal være 14 cifre. Virker
ikke på 00000000000000 (giver 9 - skal give 0 - kan klares med HVIS(...),
hvis det er relevant), men bør virke på alle andre kombinationer.
For dem der måtte være interesserede: Princippet er at
VÆRDI(MIDT($A$1;RÆKKE($1:$14);1))
giver de 14 cifre i en matrix. Derefter udregnes sumproduktet (dvs.
ciffer1x1 + cifferx2 + ciffer3x1 + ...) af
SUM( <de 14 cifre> * TRANSPONER({1;2;1;2;1;2;1;2;1;2;1;2;1;2}))
Endelig beregnes tværsummen med
1+REST(sumprodukt-1;9). Når formlen ikke virker på 00000000000000 er det
fordi denne tværsumsberegning ikke virker på 0 (giver 9), men på alle højere
tal.
Mvh
Flemming
Jens
"Jens" <jens.joensson@_FJERNDETTE_gmail.com> skrev i meddelelsen
news:474d59c8$0$90273$1472...@news.sunsite.dk...
> Hej!
>
> Findes der en kvik Excel bruger, som kan lave en modulus beregning i en
> formel til mig ? (Har minus forstand på Excel).
>
> Kundenr. (Består af kundens tlf.nr.)
> Fakturanr. (Fortløbende fakturanr. f.eks. 1001)
>
> Betalings id er så en sammensætning af Fakturanr. + kundenr. + modulus10
> beregnet kontrolciffer og skal fylde 15 cifre.
> Resultatet skulle så gerne give dette: +71<betalingsid+kreditornr.< (Jeg
> har mit eget kreditornr.)
>
> Er der nogen der kan hjælpe ?
Tusind tak for alle de forskellige bidrag. Jeg tror dog at jeg er blevet
misforstået.
Jeg har som sagt ikke meget forstand på Excel og kan ikke sådan lige finde
ud af at "indlejre" excel regnearket
<http://www.tbdl.dk/excel/betalingsident.xls> i mit faktura layout eller
andre af de givne løsninger.
Hvis jeg kort skal beskrive mit Excel kendskab til formler så må det være
=SUM(D1:D10) osv...
Det vil derfor være en stor hjælp, hvis nogen kan fortælle mig hvordan jeg
ved at få cellen med kundenr. og cellen med fakturanr. lagt sammen
automatisk får udregnet (og udskrevet) betalingsid. kodelinien a la:
+71<betalingsid+mitkreditornr<
Nogen der kan hjælpe ?
:-) Jens
Flemming
> misforstået.
> Jeg har som sagt ikke meget forstand på Excel og kan ikke sådan lige finde
> ud af at "indlejre" excel regnearket
> <http://www.tbdl.dk/excel/betalingsident.xls> i mit faktura layout eller
> andre af de givne løsninger.
> Hvis jeg kort skal beskrive mit Excel kendskab til formler så må det være
> =SUM(D1:D10) osv...
>
> Det vil derfor være en stor hjælp, hvis nogen kan fortælle mig hvordan jeg
> ved at få cellen med kundenr. og cellen med fakturanr. lagt sammen
> automatisk får udregnet (og udskrevet) betalingsid. kodelinien a la:
>
> +71<betalingsid+mitkreditornr<
>
Hej Jens,
Jeg tror egentlig ikke, at du er blevet misforstået, for uanset at du måtte
finde beregningen af kontrolcifferet indviklet, så skal du vel lave den
alligevel?
Lad mig prøve at forklare hvordan du kan få det, du spørger om:
Kreditornummer som tekst i B1.
Kundenummer=telefonnumer - skal være 8 cifre - i B2.
Fakturanummer som tal i B3.
Hvis du i B4 indsætter denne formel får du betalingsid:
=$B$2&TEKST($B$3;"000000")&1+REST(SUM(VÆRDI(MIDT($B$2&TEKST($B$3;"000000");RÆKKE($1:$14);1))*TRANSPONER({1;2;1;2;1;2;1;2;1;2;1;2;1;2}))-1;9)
Formlen er en matrix-formel. Det betyder, at du efter indtastning skal holde
Shift og Ctrl nede samtidig og så trykke Enter. Derefter skulle formlen så
således ud:
{=$B$2&TEKST($B$3;"000000")&1+REST(SUM(VÆRDI(MIDT($B$2&TEKST($B$3;"000000");RÆKKE($1:$14);1))*TRANSPONER({1;2;1;2;1;2;1;2;1;2;1;2;1;2}))-1;9)}
Tuborg-parenteserne indikerer, at det er en matrix-formel. Hver gang du
retter i den, skal du afslutte på samme måde med Ctrl+Shift Enter.
Teksten til dit girokort kan du så få med følgende formel i B5:
="+71<"&$B$4&"+"&B1&"<"
En lille tester:
Kreditornummer: 123456
Kundenummer: 86878889
Fakturanummer 1001
giver
Betalingsid i B4:
868788890010015
Tekst til girokort i B5:
+71<868788890010015+1234567<
Var det bedre? Ellers spørg igen.
Mvh
Flemming
Hans Terkelsen
"Flemming" <flem...@mail.dk> wrote in message news:474e956b$0$2092$edfa...@dtext02.news.tele.dk...
Hej Flemming!
Har jeg nu lavet en fejl igen??
Det er godt med korrekturlæsning.
Men jeg tror mere vi er uenige om fortolkningen af opskriften
på proceduren for kontrolcifferberegningen.
Den er ikke særlig koncis.
Jan, Tommy Bak og jeg får samme resultater, næsten.
Jan, hvis du kigger her, så synes jeg der mangler et modulo 10
på resultatet af Mod10 funktionen.
Ellers kan den sommetider give 10, som skulle være 0.
Flemming, jeg synes osse godt om regnearksløsningerne.
Jeg kan ikke anbefale Leo Heusers løsninger nok, hvis du kigger tilbage.
Leos sidste bidrag, om CPR numre, fra 17/8/07, ligner denne opgave meget.
Jens, Flemming har vist klaret ærterne med sammenbygning af betalingsid,
bortset fra at vi er lidt uenige om det kontrolciffer.
Hilsen Hans.
Flemming
news:474f0471$0$21925$157c...@dreader1.cybercity.dk...
Hans,
Du har ret - på 2 måder - for det første er korrekturlæsning godt og for det
andet har du ret forstået således, at det var mig, der ikke læste artiklen
om beregning af kontrolcifferet ordentligt. Uheldigvis gav min forkerte
formel det samme som den skulle på lige præcis det eksempel, der var vist.
Det gav så lidt yderligere hovedbrud for at finde en rigtig formel. Den er
postet som korrektion til min første fomel.
Mvh
Flemming
Flemming
>
> =$B$2&TEKST($B$3;"000000")&1+REST(SUM(VÆRDI(MIDT($B$2&TEKST($B$3;"000000");RÆKKE($1:$14);1))*TRANSPONER({1;2;1;2;1;2;1;2;1;2;1;2;1;2}))-1;9)
>
Beklager.
Der er som Hans har påpeget en fejl i denne formel. En rigtig udgave er:
=10-REST(SUM(MIDT($A$1;RÆKKE($1:$14);1)*TRANSPONER({1;2;1;2;1;2;1;2;1;2;1;2;1;2}))-
SUM(HVIS(VÆRDI(MIDT($A$1;RÆKKE($1:$14);1))*TRANSPONER({1;2;1;2;1;2;1;2;1;2;1;2;1;2})>9;9;0));10)
Ikke køn. Stadig matrix-formel.
Mvh
Flemming
Hans Terkelsen
"Flemming" <flem...@mail.dk> wrote in message news:474f30a3$0$2088$edfa...@dtext02.news.tele.dk...
.....
> om beregning af kontrolcifferet ordentligt. Uheldigvis gav min forkerte
> formel det samme som den skulle på lige præcis det eksempel, der var vist.
>
> Det gav så lidt yderligere hovedbrud for at finde en rigtig formel. Den er
> postet som korrektion til min første fomel.
>
> Mvh
>
> Flemming
Flemming, kun respekt.
For mit eget vedkommende:
Alternativet til fejl, er ikke at lave noget som helst :-)
Hilsen Hans.