Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Viitenumeron laskeminen Excelillä?

6,018 views
Skip to first unread message

Jani Korkiakangas

unread,
Jun 10, 2002, 7:04:00 AM6/10/02
to
Allekirjoittaneella on pitkä listä jäsennumeroita (5 lukuinen jäsennumero)
joista pitäisi kätevästi saada muodostettua maksuviitteeksi soveltuva
viitenumero.

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


Mikail Ruutu

unread,
Jun 10, 2002, 8:01:06 AM6/10/02
to

"Jani Korkiakangas" <jako...@NOSPAM.kase.fi> kirjoitti
viestissä:AI%M8.155$wG4....@read2.inet.fi...

> Allekirjoittaneella on pitkä listä jäsennumeroita (5 lukuinen jäsennumero)
> joista pitäisi kätevästi saada muodostettua maksuviitteeksi soveltuva
> viitenumero.
>
> 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.

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


Jani Korkiakangas

unread,
Jun 10, 2002, 8:19:56 AM6/10/02
to

"Mikail Ruutu" <mikr...@netscape.net.invalid> wrote in message
news:6y0N8.183$wG4....@read2.inet.fi...

> 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).
>

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)


KajBre

unread,
Jun 10, 2002, 10:23:53 AM6/10/02
to
Samasta aiheesta oli juttua muutama viikko sitten newseissa. Tässä olisi
VBA-funktio, jota voi käyttää viitenumeron laskemiseen, jonka postasin
silloin viimeksikin.
Funktion parametrina on siis viitenumero ilman tarkistusnumeroa, ja funktio
palauttaa sitten viitteen tarkistusnumeron kanssa. Lopputulos järjesteteään
vielä viiden numeron ryhmiin, kuten se tavallisesti maksukuiteissa
esitetään.
Moni varmaan koodaa tämän tyylikkäämminkin, mutta tämän koodin pitäisi kyllä
toimia.
'-------------------------------------------------------------
Function LaskeViite(ByVal numerosarja As String) As String
'Muuttujien esittely
Dim origSarja As String ' alkuperäinen numerosarja
Dim Laskuri As Byte ' laskuri
Dim sarjanPituus As Byte ' numerosarjan pituus
Dim tarkisteNumero As Byte ' tarkistenumero
Dim sarjanSumma As String ' numerosarjan summa
Dim kertoimet(2) As Byte ' kertoimet
Dim i As Integer
Dim j As Integer
Dim refnumber As String 'Viitenumero ilman jarjestelyä
Dim outstr As String
Dim UusiViite As String 'Viitenumero 5 mrk. ryhmissä

'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...

Markku Nevalainen

unread,
Jun 10, 2002, 11:29:51 AM6/10/02
to
Jani Korkiakangas wrote:
>
> Viitenumeron laskeminen on sinäänsä ihan yksinkertainen juttu:
>
> ...mutta kuinka tuon voisi tehdä sujuvasti excelillä?
>

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

Jani Korkiakangas

unread,
Jun 11, 2002, 6:38:36 AM6/11/02
to
Valtiovelan kokoiset kiitokset Nevalaisen Markulle ohjeista joiden avulla
jopa
keskivertomarkkinointimerkonomi kykeni tuottamaan hetkessä 1440
viitenumeroa.

Kiitokset myös KajBrelle hienosta koodinpätkästä joka aikaansai merkonomissa
merkillistä kasvojen vääntelehtymistä ja suurta kunnioitusta "nörttejä"
kohtaan 8^)

J.K.


Sami Hilden

unread,
Jun 12, 2002, 2:52:29 PM6/12/02
to
"Jani Korkiakangas" <jako...@NOSPAM.kase.fi> wrote in
news:AI%M8.155$wG4....@read2.inet.fi:
>
> ...mutta kuinka tuon voisi tehdä sujuvasti excelillä?

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

Juri

unread,
Jan 1, 2015, 4:09:38 PM1/1/15
to
Markku,

Minä löysin viestisi Googlella (12,5 vuotta viestisi jättämisen jälkeen) ja sain tarvitsemani avun. Googlella ei olisi arvoa ilman sinun kaltaisia avuliaita ihmisiä, jotka jakavat osaamisensa muiden käyttöön. Matematiikka ja kaavat eivät ole minun vahvuuksiani, joten suuri kumarrus sinulle!

eetu....@gmail.com

unread,
Sep 23, 2015, 2:41:36 AM9/23/15
to
maanantai 10. kesäkuuta 2002 18.29.51 UTC+3 Markku Nevalainen kirjoitti:
> Mutta laitetaan taas, ja toivotaan että joku vaikka joskus löytäisi
> Googlellakin tämän vastauksen jälkeenpäin...

Löytyi ja auttoi 13v päästäkin. Kiitos.

Tässä ajassa taulukkolaskennat tosin tekeytyivät ymmärtämättömiksi kieliversioiden osalta. Ts. suomalainen ei ymmärrä englantia ja toisin päin. Libreoffice calc 4.1.x.x käännökset asian kanssa painiville:

IF : JOS
ISNUMBER : ONLUKU
RIGHT : OIKEA
MID : POIMI.TEKSTI

Eetu

anne.m....@gmail.com

unread,
Sep 28, 2016, 8:09:01 AM9/28/16
to

>
> Mutta laitetaan taas, ja toivotaan että joku vaikka joskus löytäisi
> Googlellakin tämän vastauksen jälkeenpäin..
>

Ja auttoihan tämä 14v jälkeenkin.
Liitän tähän suomennetun koodin, koska eipä tosiaan osannut englantia enää suomalainen excel.

#=JOS(ONLUKU(A2);(10000000*G9+10*A2+OIKEA(10-OIKEA# #(7*POIMI.TEKSTI(10000000000+1000000*G9+A2;2;1)+1*POIMI.TEKSTI#
#(10000000000+1000000*G9+A2;3;1)+3*POIMI.TEKSTI(10000000000+1000000*G9+A2;4;1)+#
#(7*POIMI.TEKSTI(10000000000+1000000*G9+A2;5;1))+#
#(1*POIMI.TEKSTI(10000000000+1000000*G9+A2;6;1))+#
#(3*POIMI.TEKSTI(10000000000+1000000*G9+A2;7;1))+#
#(7*POIMI.TEKSTI(10000000000+1000000*G9+A2;8;1))+#
#(1*POIMI.TEKSTI(10000000000+1000000*G9+A2;9;1))+#
#(3*POIMI.TEKSTI(10000000000+1000000*G9+A2;10;1))+#
#(7*POIMI.TEKSTI(10000000000+1000000*G9+A2;11;1));1);1));"")#

Kiitos Markku :)


-Anne-

toim...@mommilanjarvensoutelu.fi

unread,
May 22, 2018, 6:18:34 AM5/22/18
to
Kiitos kaavoista! Kiroilin ja taistelin aikani, mutta eihän tuo suomenkielinen excel tosiaan ymmärtänyt englanninkielistä kaavaa (tähän sellanen piruhymiö).

Terveisin, Soutelusihteeri

toim...@mommilanjarvensoutelu.fi

unread,
May 22, 2018, 6:57:36 AM5/22/18
to
Nojuu - sitten kävi niin, että laskut 2018001-2018098 saivat viitenumerot.
2018099-2018100 laskuille tuli #ARVO!
Sitten huomasin, että tarvitsen mainoslaskutusta varten lisää laskunumeroita ja lisäsin 2018101-2018130 laskunumerot. 2018101-2018104:lle antoi tuon saman #ARVO! ja loput toimivat.
Missä vika?
0 new messages