Viitenumeron laskeminen on sinäänsä ihan yksinkertainen juttu:
- Tarkistettavan viitteen numerot kerrotaan oikealta vasemmalle
painoarvoilla 7,3,1,7,3,1,...
- Kertotulot lasketaan yhteen ja summa vähennetään seuraavasta täydestä
kymmenestä.
- Erotus on tarkiste, joka tulostetaan viitteen viimeiseksi numeroksi.
...mutta kuinka tuon voisi tehdä sujuvasti excelillä?
Etukäteen kiitellen
Jani Korkiakangas
Voisi hoitua vaikka niin, että ensin poimit Mid-funktiolla (tai muulla
sopivalla tekstifunktiolla) aina halutun numeron kertomista varten ja
summaat saadut luvut.
Sitten jakojäännöksen avulla lasket tarkisteen.
Lopuksi Concatenatella ("Ketjuta") yhdistät saadut luvut merkkijonoksi.
Jos haluat mammuttitautisen funktion soluun, upotat noita funktioita
sopivasti sisäkkäin (ulommaisin taitaisi olla se Concatenate, jossa
parametreina sitten noita muita).
MR
Vautsi ja kiitos!
Mutta ensinnäkin allekirjoittaneen täytyy tunnustaa olevansa ns. "melkoinen
käsi" mitä tulee exceliin
ja toisekseen ei näin kuumalla jaksa harmaa massa korvien välissä raksuttaa
eli vähän semmosta
valmista koodinpätkää etsiskelen.
(poistuu nolona jääkaapille jäätelö noutamaan)
'muuttujien alustus
origSarja = numerosarja
sarjanPituus = Len(numerosarja)
Laskuri = 0
sarjanSumma = 0
tarkisteNumero = 0
kertoimet(0) = 7: kertoimet(1) = 3: kertoimet(2) = 1
'tarkistetaan annetun numerosarjan pituus ja sisältö
If (sarjanPituus < 1 Or sarjanPituus > 19) Or Not IsNumeric(numerosarja)
Then GoTo Viite_Error
'käydään numerosarja lävitse
Do While Laskuri < sarjanPituus
sarjanSumma = sarjanSumma + (Mid(numerosarja, sarjanPituus -
Laskuri, 1) * kertoimet(Laskuri Mod 3))
Laskuri = Laskuri + 1 'laskurin inkrementointi
Loop
'lasketaan tarkistenumero (sarjan summasta seuraava täysi kymmen -
sarjan summa)
tarkisteNumero = (10 - (sarjanSumma Mod 10)) Mod 10
'palautetaan kutsuvaan aliohjelmaan alkuperäinen numerosarja ja
tarkisteNumero
LaskeViite = origSarja & tarkisteNumero
refnumber = origSarja & tarkisteNumero
strlen = Len(origSarja & tarkisteNumero)
' / Järjestetään 5 merkin ryhmiin
If strlen > 5 Then
j = 0
For i = strlen To 1 Step -1
j = j + 1
If j = 6 Then
outstr = outstr & " "
j = 1
End If
outstr = outstr & Mid(refnumber, i, 1)
Next
UusiViite = ""
For i = Len(outstr) To 1 Step -1
UusiViite = UusiViite & Mid(outstr, i, 1)
Next i
LaskeViite = UusiViite
End If
Exit Function
Viite_Error:
On Error GoTo 0
Err.Raise vbObjectError + 1001, , "Viitenumeroksi laskettava numerosarja
virheellinen"
LaskeViite = ""
Exit Function
End Function
'-----------------------------
Kaj Bredenberg
"Jani Korkiakangas" <jako...@NOSPAM.kase.fi> kirjoitti viestissä
news:AI%M8.155$wG4....@read2.inet.fi...
Tämä viitenumero on ollut vaikka miten monesti, ja itsekin olen
tainnut vuosien mittaan pariinkin kertaan vastailla.
Mutta laitetaan taas, ja toivotaan että joku vaikka joskus löytäisi
Googlellakin tämän vastauksen jälkeenpäin...
Tämä on sikäli helppo ratkaisu Exceliin, että ei tarvitse ymmärtää
mitään Excelin makroista tai VBA:sta. Riittää kunhan osaa tar-
kalleen kopioida alla olevan funktion Exceliin.
#=IF( ISNUMBER(A2);(10000000*G9+10*A2+RIGHT(10-RIGHT(7*MID(10000000000+1000000*G9+A2;2;1)#
#+1*MID(10000000000+1000000*G9+A2;3;1)+3*MID(10000000000+1000000*G9+A2;4;1)#
#+(7*MID(10000000000+1000000*G9+A2;5;1))+(1*MID(10000000000+1000000*G9+A2;6;1))#
#+(3*MID(10000000000+1000000*G9+A2;7;1))+(7*MID(10000000000+1000000*G9+A2;8;1))#
#+(1*MID(10000000000+1000000*G9+A2;9;1))+(3*MID(10000000000+1000000*G9+A2;10;1))#
#+(7*MID(10000000000+1000000*G9+A2;11;1));1);1));"")#
-Yllä oleva 448 merkin pituinen stringi on pankkiviitteen laskeva Excelin funktio.
Sen toimivuus on testattu ainakin englanninkielisellä Excel 4.0 versiolla, sekä
suomenkielisellä Excel-97 versiolla.
-Jokaisen rivin alussa ja lopussa olevat ylimääräiset #-merkit on laitettu
vain selvennyksen vuoksi, rivinvaihtopaikkojen löytämiseksi. Ne pitää poistaa,
ja yhdistää stringi yhdeksi pitkäksi stringiksi, joka siis sijoitetan Excelissä
siihen soluun, johon pankin viitenumero halutaan.
-Näppäilyteknisesti, vähimmillä virheillä (Exceli ei siedä merkinkään puut-
tumista) noiden stringien yhdistäminen käynee niin, että yhdistää ensin
stringit vaikka Wordissa, tai jollakin muulla editorilla yhdeksi pitkäksi
stringiksi, mutta pitää tässä vaiheessa vielä risuaidat paikallaan.
Kun stringi on yhdistetty, käytetään editorin Replace -komentoa, ja sillä
korvataan jokainen #-merkki tyhjällä, *ei* siis välilyöntimerkillä, vaan
olemattomalla merkillä.
-Kyseinen valmis pitkä stringi kopioidaan Windowsin leikepöydän kautta
Excelin soluun.
-Laskentakaavassa on viittauksia soluun A2, jossa solussa on numero,
jolle viitenumeroa ollaan laskemassa. Joten testausvaiheessa tämä kaava
kannattaa sijoittaa soluun A1, ja numero jolle viitettä lasketaan, tulee
soluun A2.
-A1-solun tyyppinä voi olla Yleinen tai Number. Jos haluaa että Excel
lisäksi jaottelee viitenumeron 5:n numeron ryhmiksi, kuten Pankkiyh-
distyksen standardi määrittelee, niin solun kuvausformaatiksi pitää
laittaa: '##\ #####' (ilman lainausmerkkejä, tietenkin).
-Aseta kokeeksi soluun A2 vaikka numero: 12345678. A1:een pitäisi il-
maantua pankkiviitteen tarkistusnumero, eli kokonaisuudessaan: 1234 56780
-Kun toimivuus on testattu, voikin alkaa muokkailla funktiota mie-
leisekseen.
Lähinnä A2-soluviittaus varmaan halutaan muuttaa joksikin omaa laskenta-
alustaa vastaavaksi muuksi numeroksi.
Tämä ei kannata yrittää tehdä käsipelillä, sillä melko varmasti te-
kee virheen 447 merkin stringissä.
Excelin Korvaa/Replace -toiminto sen sijaan tekee vaihdoksen vaivatta.
MNe
Kiitokset myös KajBrelle hienosta koodinpätkästä joka aikaansai merkonomissa
merkillistä kasvojen vääntelehtymistä ja suurta kunnioitusta "nörttejä"
kohtaan 8^)
J.K.
Viidellä numerolla onnistunee vaikka näin:
=A1&ROUNDUP(MID(A1;1;1)*7+MID(A1;2;1)*3+MID(A1;3;1)+MID(A1;4;1)*7+MID
(A1;5;1)*3;-1)-(MID(A1;1;1)*7+MID(A1;2;1)*3+MID(A1;3;1)+MID(A1;4;1)*7+MID
(A1;5;1)*3)
Solussa A1 siis viisinumeroinen luku. Kaavaa jatkamalla saa sitten lisää
numeroita.
--
Sami