Kontrollera om kontrollsiffran i personnummer stämmer

[MissyMalinto]

Hejsan! Jag ska göra ett namnregister i Excel för min arbetsplats. En av kolumnerna ska innehålla personernas personnummer. Min fråga till er är om det finns något sätt att kontrollera att personnumret jag skrivit in är korrekt, dvs att sista kontrollsiffran stämmer. Det är ju så lätt att man råkar skriva in en siffra fel. Mycket tacksam för en noggrann instruktion då jag inte är så värst hemma i Excel. Vänliga hälsningar

Linda

[Pejo]

Hejsan! Jag ska göra ett namnregister i Excel för min arbetsplats. En av kolumnerna ska innehålla personernas personnummer. Min fråga till er är om det finns något sätt att kontrollera att personnumret jag skrivit in är korrekt, dvs att sista kontrollsiffran stämmer. Det är ju så lätt att man råkar skriva in en siffra fel. Mycket tacksam för en noggrann instruktion då jag inte är så värst hemma i Excel. Vänliga hälsningar

Linda

 

Hej,

 

Om jag har fattat Skatteverkets beräkningsmodell riktigt borde följande formel ge kontrollsiffran för ett personnummer på formen ÅÅMMDD-XXXX i cell A1.

=10 -REST(

HELTAL(EXTEXT($A1;1;1)*2/10)+REST(EXTEXT($A1;1;1)*2;10)+

HELTAL(EXTEXT($A1;2;1)*1/10)+REST(EXTEXT($A1;2;1)*1;10)+

HELTAL(EXTEXT($A1;3;1)*2/10)+REST(EXTEXT($A1;3;1)*2;10)+

HELTAL(EXTEXT($A1;4;1)*1/10)+REST(EXTEXT($A1;4;1)*1;10)+

HELTAL(EXTEXT($A1;5;1)*2/10)+REST(EXTEXT($A1;5;1)*2;10)+

HELTAL(EXTEXT($A1;6;1)*1/10)+REST(EXTEXT($A1;6;1)*1;10)+

HELTAL(EXTEXT($A1;8;1)*2/10)+REST(EXTEXT($A1;8;1)*2;10)+

HELTAL(EXTEXT($A1;9;1)*1/10)+REST(EXTEXT($A1;9;1)*1;10)+

HELTAL(EXTEXT($A1;10;1)*2/10)+REST(EXTEXT($A1;10;1)*2;10);10)

Eftersom varje siffra måste behandlas var för sig får de en rad var. Om formeln känns klumpig går det att skapa en helt egen funktion som gör samma beräkning i VBA, men det kräver lite mer arbete.

 

Hälsningar

/Johan

[MissyMalinto]

Hej,

 

Om jag har fattat Skatteverkets beräkningsmodell riktigt borde följande formel ge kontrollsiffran för ett personnummer på formen ÅÅMMDD-XXXX i cell A1.

=10 -REST(

HELTAL(EXTEXT($A1;1;1)*2/10)+REST(EXTEXT($A1;1;1)*2;10)+

HELTAL(EXTEXT($A1;2;1)*1/10)+REST(EXTEXT($A1;2;1)*1;10)+

HELTAL(EXTEXT($A1;3;1)*2/10)+REST(EXTEXT($A1;3;1)*2;10)+

HELTAL(EXTEXT($A1;4;1)*1/10)+REST(EXTEXT($A1;4;1)*1;10)+

HELTAL(EXTEXT($A1;5;1)*2/10)+REST(EXTEXT($A1;5;1)*2;10)+

HELTAL(EXTEXT($A1;6;1)*1/10)+REST(EXTEXT($A1;6;1)*1;10)+

HELTAL(EXTEXT($A1;8;1)*2/10)+REST(EXTEXT($A1;8;1)*2;10)+

HELTAL(EXTEXT($A1;9;1)*1/10)+REST(EXTEXT($A1;9;1)*1;10)+

HELTAL(EXTEXT($A1;10;1)*2/10)+REST(EXTEXT($A1;10;1)*2;10);10)

Eftersom varje siffra måste behandlas var för sig får de en rad var. Om formeln känns klumpig går det att skapa en helt egen funktion som gör samma beräkning i VBA, men det kräver lite mer arbete.

 

Hälsningar

/Johan

 

 

Hej, Johan (Pejo) och tack snälla för att du försöker hjälpa mig!

 

Problemet är bara att jag är helt rudis på Excel så jag förstår inte riktigt hur jag ska använda formeln du skrev ner. Var ska jag skriva in den???

 

Linda

[Mr Andersson]

Hejsan! Jag ska göra ett namnregister i Excel för min arbetsplats. En av kolumnerna ska innehålla personernas personnummer. Min fråga till er är om det finns något sätt att kontrollera att personnumret jag skrivit in är korrekt, dvs att sista kontrollsiffran stämmer. Det är ju så lätt att man råkar skriva in en siffra fel. Mycket tacksam för en noggrann instruktion då jag inte är så värst hemma i Excel. Vänliga hälsningar

Linda

 

Principen för att kontrollera personnummer är ganska enkel. Tekniker heter modulo-2. Låt oss säga att man har personnmmret 711301-131x:

 

7 1 1 3 0 1 1 3 1

2 1 2 1 2 1 2 1 2

 

Varje siffra multipliceras med 2 och 1 om vart annat, med start på 2. Siffrorna i resultaten adderas:

 

(1+4) + 1 + 2 + 3 + 0 + 1 + 2 + 3 + 2 = 19

 

Sedan tar man 10 - (sista siffran i summan) = 1

 

Detta är kontrollsiffran.

 

Men, detta bevisar inte på något sätt att personnumret existerar, det bevisar bara att personnumret skulle kunna existrera.

[MissyMalinto]

Principen för att kontrollera personnummer är ganska enkel. Tekniker heter modulo-2. Låt oss säga att man har personnmmret 711301-131x:

 

7 1 1 3 0 1 1 3 1

2 1 2 1 2 1 2 1 2

 

Varje siffra multipliceras med 2 och 1 om vart annat, med start på 2. Siffrorna i resultaten adderas:

 

(1+4) + 1 + 2 + 3 + 0 + 1 + 2 + 3 + 2 = 19

 

Sedan tar man 10 - (sista siffran i summan) = 1

 

Detta är kontrollsiffran.

 

Men, detta bevisar inte på något sätt att personnumret existerar, det bevisar bara att personnumret skulle kunna existrera.

 

 

Tack, Mr Andersson! Gulliga ni är som försöker hjälpa mej.

 

Jag tror jag har uttryckt mig lite slarvigt i min fråga. Det jag efterlyser är alltså nån slags formel eller dylikt som jag kan lägga in i de celler i mitt register där jag har personnummer, så att jag kan se direkt om numren jag matar in är korrekta eller inte. Det är fråga om sisådär en sex-sjutusen personnummer som ska knappas in av undertecknad (kul jobb jag har just nu, va) så det är ju rätt stor risk att det blir fel någonstans.

 

Jag tror att det möjligen var en sådan formel som Pejo/Johan hade hittat (se ovan). Bara det att jag inte begriper var jag ska göra av den.

 

Linda

[Monshi]

Jag tror jag har uttryckt mig lite slarvigt i min fråga. Det jag efterlyser är alltså nån slags formel eller dylikt som jag kan lägga in i de celler i mitt register där jag har personnummer, så att jag kan se direkt om numren jag matar in är korrekta eller inte. Det är fråga om sisådär en sex-sjutusen personnummer som ska knappas in av undertecknad

 

Ah, okej.

 

Det finns tre vägar att gå, två har samma formler och en har ingen formel men ett krav.

Vi börjar med formel

Säg att du ska mata in personnumret i cell G2, att du i A1:A100 på blad1 har dina existerande personnummer.

 

Vi börjar med att i en cell bredvid G2 skriva

=PASSA(G2;Blad1!$A$1:$A$100;0)

denna ger ett felvärde om värdet i G2 ej finns i sökområdet.

Nu kan du antingen använda variant av denna formel i cellen på bladet, skriva ut ett anpassat felmeddelande:

=OM(ÄRFEL(PASSA(...));"Felaktigt personnummer";"")

 

eller så kan vi använda villkorstyrda formateringen och färga cellen när fel. Markera cellen, leta reda på i menyerna Villkorsstryrd formatering och sätt formeln till

=ÄRFEL(PASSA($G$2;Blad....))

och ge en lämplig färg.

 

Till alternativet utan formler. Jag antar att du har dina personnummer på ett annat blad, då måste vi namnge detta område för att detta alternativ ska fungera. Enklaste sättet är att

1: markera alla personnummer, bara den kolumnen med personnummer.

2: Klicka i addressfältet till vänster om formelfältet och ge ett bara namn, pNummer exempelvis.

 

Sedan går vi till cell G2 igen.

Där klickar vi i menyn Data->Validering och väljer där lista och som källa skriver vi

=pNummer

samt bockar bort "lista i cell".

Nu kommer excel varna när du skriver fel.

[MissyMalinto]

Ah, okej.

 

Det finns tre vägar att gå, två har samma formler och en har ingen formel men ett krav.

Vi börjar med formel

Säg att du ska mata in personnumret i cell G2, att du i A1:A100 på blad1 har dina existerande personnummer.

 

Vi börjar med att i en cell bredvid G2 skriva

=PASSA(G2;Blad1!$A$1:$A$100;0)

denna ger ett felvärde om värdet i G2 ej finns i sökområdet.

Nu kan du antingen använda variant av denna formel i cellen på bladet, skriva ut ett anpassat felmeddelande:

=OM(ÄRFEL(PASSA(...));"Felaktigt personnummer";"")

 

eller så kan vi använda villkorstyrda formateringen och färga cellen när fel. Markera cellen, leta reda på i menyerna Villkorsstryrd formatering och sätt formeln till

=ÄRFEL(PASSA($G$2;Blad....))

och ge en lämplig färg.

 

Till alternativet utan formler. Jag antar att du har dina personnummer på ett annat blad, då måste vi namnge detta område för att detta alternativ ska fungera. Enklaste sättet är att

1: markera alla personnummer, bara den kolumnen med personnummer.

2: Klicka i addressfältet till vänster om formelfältet och ge ett bara namn, pNummer exempelvis.

 

Sedan går vi till cell G2 igen.

Där klickar vi i menyn Data->Validering och väljer där lista och som källa skriver vi

=pNummer

samt bockar bort "lista i cell".

Nu kommer excel varna när du skriver fel.

 

Tack snälla du och alla andra som försöker hjälpa mej! Hmmm...vågar jag säga detta nu när du har lagt ner så mycket jobb på att beskriva hur jag ska göra...? Saken är den att jag har inga existerande personnummer på något annat blad.

 

Nej, jag tror nog den här idioten (=undertecknad) får så lov att tala om vad det är hon ska göra från början till slut, så inga fler vänliga, hjälpsamma människor lägger ner tid och kraft på henne i onödan. Så här ligger det till:

 

Jag jobbar för en privat vårdcentral som ska starta i slutet av maj. Vid mina fötter befinner sig just nu en pappkasse som är knökfull med handskrivna intresseanmälningar från folk som vill "lista sig" på denna vårdcentral.

 

Mitt arbete just nu är att utifrån dessa papper upprätta ett register i Excel. (Registret ska bl a användas till att skapa självhäftande etiketter med personernas namn för brevutskick.)

 

Registret ska innehålla 6 kolumner med förnamn, efternamn, adress, postnummer, postadress och personnummer på alla dessa tusentals människor. Jag ska alltså sitta och föra in alla dessa uppgifter manuellt och nånting säger mig att när jag knappat in alla sex-/sjutusen personers personnummer så kommer möjligtvis något enstaka (positiv ska man alltid vara ) vara felaktigt infört.

 

Nu vet jag ju att i alla personnummer är den sista siffran en s k kontrollsiffra som finns där för att man ska veta huruvida man har knappat in födelsedatum och de tre näst sista siffrorna korrekt. När jag t ex sitter på jobbet och i vårt datorjournalsystem ska plocka fram en persons journal genom att knappa in personnumret, så får jag ett felmeddelande om jag tryckt in en felaktig siffra.

 

Min tanke är nu: skulle inte samma sorts formel (eller-vad-det-nu-heter) som finns i vårt journalsystem kunna läggas in i de celler i mitt Excelregister där jag ska skriva in personnummer? På så sätt skulle jag få ett felmeddelande om jag skrev in ett personnummer i dessa celler där kontrollsiffran inte stämmer.

 

Jag är uppenbarligen inte någon höjdare på att förklara, men förstår ni hur jag menar???

 

Ditt sätt, Monshi, skulle troligen funkat perfekt om jag hade haft alla korrekta personnummer uppskrivna i t ex Excel redan, men nu har jag ju tyvärr inte det... Så jag ber väl allra ödmjukast om hjälp en gång till och hoppas att någon vänlig själ kan förbarma sig över mej...

 

Linda

[Monshi]

Fasiken, där blev man lurad :/

 

Men ingen fara Bara tiden det tog att skriva det som är förlorad för mig.

 

Du har fått formel ovan som ska visa på när kontrollsiffran inte stämmer och här får du en till av mig, lite kortare:

=10-HÖGER((EXTEXT(A1;1;1)+EXTEXT(A1;3;1)+EXTEXT(A1;5;1)+EXTEXT(A1;8;1)+EXTEXT(A1;10;1))+EXTEXT(A1;2;1)+EXTEXT(A1;4;1)+EXTEXT(A1;6;1)+EXTEXT(A1;9;1);1)

givet att du matat in personnummer i A1 i formatet ååmmdd-xxxx

denna former ger alltså kontrollsiffran.

Säg att denna formel står i cell B1. I C1 kan vi då skriva

=OM((HÖGER(A1;1)*1)=B1;"Korrekt";"Felaktigt")

 

ja, lite snyggare:

=om(längd(A1)=11;OM((HÖGER(A1;1)*1)=B1;"Korrekt";"Felaktigt");"")

dvs bara bry sig om det står ett helt personnummer i cellen.

Det går att nyttja denna formel som bas för villkorstyrd formatering eller datavalidering om man så vill.

Redigerad 25 april, 2010 av Monshi

[MissyMalinto]

Fasiken, där blev man lurad :/

 

Men ingen fara Bara tiden det tog att skriva det som är förlorad för mig.

 

Du har fått formel ovan som ska visa på när kontrollsiffran inte stämmer och här får du en till av mig, lite kortare:

=10-HÖGER((EXTEXT(A1;1;1)+EXTEXT(A1;3;1)+EXTEXT(A1;5;1)+EXTEXT(A1;8;1)+EXTEXT(A1;10;1))+EXTEXT(A1;2;1)+EXTEXT(A1;4;1)+EXTEXT(A1;6;1)+EXTEXT(A1;9;1);1)

givet att du matat in personnummer i A1 i formatet ååmmdd-xxxx

denna former ger alltså kontrollsiffran.

Säg att denna formel står i cell B1. I C1 kan vi då skriva

=OM((HÖGER(A1;1)*1)=B1;"Korrekt";"Felaktigt")

 

ja, lite snyggare:

=om(längd(A1)=11;OM((HÖGER(A1;1)*1)=B1;"Korrekt";"Felaktigt");"")

dvs bara bry sig om det står ett helt personnummer i cellen.

Det går att nyttja denna formel som bas för villkorstyrd formatering eller datavalidering om man så vill.

 

Nej, nu har jag snart slitit bort allt hår jag har på skallen. Här har man levt hela sitt liv tryggt i förvissningen att man iallafall inte är korkad och så - ett tu tre - avslöjar det förbenade Excel att allt var lögn! Man är uppenbarligen totalt obildbar!!!

 

Så här har jag gjort :

1) I cell A1 har jag skrivit in ett personnummer i formatet 740105-xxxx

 

2) I cell B1 har jag klistrat in formeln jag fick:

=10-HÖGER((EXTEXT(A1;1;1)+EXTEXT(A1;3;1)+EXTEXT(A1;5;1)+EXTEXT(A1;8;1)+EXTEXT(A1;10;1))+EXTEXT(A1;2;1)+EXTEXT(A1;4;1)+EXTEXT(A1;6;1)+EXTEXT(A1;9;1);1)

 

3) I cell C1 har jag klistrat in den andra formeln:

=om(längd(A1)=11;OM((HÖGER(A1;1)*1)=B1;"Korrekt";"Felaktigt");"")

 

4) Sedan har jag kopierat respektive formel till resten av tabell B och C.

Det här blev resultatet:

Ibland får jag resultatet "Korrekt", ibland "Felaktigt", trots att alla personnummer jag skrivit in ÄR korrekta (har testat med släktens)!

 

Felet beror inte på att jag kopierat formlerna fel till resten av kolumnen, för jag har provat både med mitt eget personnummer och t ex farsans i samma cell (A1). Mitt eget och brorsans funkar varje gång men inte farsans och morsans. Aaaarrrrghhh!

 

 

Och för att göra saken än värre behöver jag dessutom ha personnumren i formatet 19740105-xxxx, 20080305-xxxx osv.

 

Vad tycker ni? Ska jag ge upp? Tror inte mitt hår tål fler ryck nu. Känns ju inte alltför festligt om jag skulle behöva gå och se ut som ett krusbär i huvet i sommar...

 

Linda

[Pejo]

Nej, nu har jag snart slitit bort allt hår jag har på skallen. Här har man levt hela sitt liv tryggt i förvissningen att man iallafall inte är korkad och så - ett tu tre - avslöjar det förbenade Excel att allt var lögn! Man är uppenbarligen totalt obildbar!!!

 

Så här har jag gjort :

1) I cell A1 har jag skrivit in ett personnummer i formatet 740105-xxxx

 

2) I cell B1 har jag klistrat in formeln jag fick:

=10-HÖGER((EXTEXT(A1;1;1)+EXTEXT(A1;3;1)+EXTEXT(A1;5;1)+EXTEXT(A1;8;1)+EXTEXT(A1;10;1))+EXTEXT(A1;2;1)+EXTEXT(A1;4;1)+EXTEXT(A1;6;1)+EXTEXT(A1;9;1);1)

 

3) I cell C1 har jag klistrat in den andra formeln:

=om(längd(A1)=11;OM((HÖGER(A1;1)*1)=B1;"Korrekt";"Felaktigt");"")

 

4) Sedan har jag kopierat respektive formel till resten av tabell B och C.

Det här blev resultatet:

Ibland får jag resultatet "Korrekt", ibland "Felaktigt", trots att alla personnummer jag skrivit in ÄR korrekta (har testat med släktens)!

 

Felet beror inte på att jag kopierat formlerna fel till resten av kolumnen, för jag har provat både med mitt eget personnummer och t ex farsans i samma cell (A1). Mitt eget och brorsans funkar varje gång men inte farsans och morsans. Aaaarrrrghhh!

 

 

Och för att göra saken än värre behöver jag dessutom ha personnumren i formatet 19740105-xxxx, 20080305-xxxx osv.

 

Vad tycker ni? Ska jag ge upp? Tror inte mitt hår tål fler ryck nu. Känns ju inte alltför festligt om jag skulle behöva gå och se ut som ett krusbär i huvet i sommar...

 

Linda

 

Hej igen,

 

Inte ge upp, det här går att lösa! (Även om arbetsuppgiften låter en smula enahanda oavsett, får du åtminstone lära dig mer om Excel.)

 

Jag får inte heller Monshis formel att ge rätt resultat (åtminstone inte på mitt personnummer), men här är en exempelfil med min formel i en extra kolumn. Formeln ger SANT/FALSKT beroende på om kontrollsiffran stämmer och kolumnen får mha villorsstyrd formatering även röd text när formeln är FALSK.

Personnummer.xls

 

Fyll i tabellen och kopiera med formelkolumnen för att kontrollera inmatningen.

 

Hälsningar

/Johan

[MissyMalinto]

Hej igen,

 

Inte ge upp, det här går att lösa! (Även om arbetsuppgiften låter en smula enahanda oavsett, får du åtminstone lära dig mer om Excel.)

 

Jag får inte heller Monshis formel att ge rätt resultat (åtminstone inte på mitt personnummer), men här är en exempelfil med min formel i en extra kolumn. Formeln ger SANT/FALSKT beroende på om kontrollsiffran stämmer och kolumnen får mha villorsstyrd formatering även röd text när formeln är FALSK.

Personnummer.xls

 

Fyll i tabellen och kopiera med formelkolumnen för att kontrollera inmatningen.

 

Hälsningar

/Johan

 

 

Johan - du är värd din vikt i guld! Tack, underbara mänska!

Tack också alla ni andra som ansträngt er för att hjälpa mej!

 

Kram

Linda

[MissyMalinto]

Johan - du är värd din vikt i guld! Tack, underbara mänska!

Tack också alla ni andra som ansträngt er för att hjälpa mej!

 

Kram

Linda

 

Nej! Neeeeejjjj! Nu provade jag med pappsens personnummer också i det kalkylblad jag fick av dej - och det står FALSKT!

[Pejo]

Nej! Neeeeejjjj! Nu provade jag med pappsens personnummer också i det kalkylblad jag fick av dej - och det står FALSKT!

 

Hmm, jag har visserligen bara testat den på ett par tre stycken personnummer, men där fungerade den. Har du dubbelkollat så att referenserna i formeln är riktiga?

 

Hälsningar

/Johan

[Monshi]

Jag får inte heller Monshis formel att ge rätt resultat (åtminstone inte på mitt personnummer)...

vafan... här var man säker på sin sak, ja, jag följde den mall Mr Andersson gett...

 

Men, bu, han for med osanning!

 

 

1. Siffrorna i födelsetiden och födelsenumret multipliceras växelvis med 2 och 1.

 

6 4 0 8 2 3 – 3 2 3

2 1 2 1 2 1 2 1 2

12,4,0,8,4,3, 6,2,6

 

2. Lägg ihop siffrorna i produkterna. Obs! 12 räknas som 1+2

1+2+4+0+8+4+3+6+2+6=36

 

3. Entalssiffran (6) i siffersumman dras från talet 10. 10-6=4.

 

Restsiffran (4) blir kontrollsiffra vilket gör att personnumret i exemplet blir 640823-3234. Är restsiffran 10, blir kontrollsiffran 0.

 

Inte lika smidigt att fixa det med en formel då men Pejos verkar fungera. Detta kontrollera jag mot denna kod:

 

Function KontrolleraKontrollsiffra(rn As Range) As Boolean
Dim sNummer As String
Dim i As Integer
Dim iStart As Integer
Dim summa As Integer
sNummer = Trim(rn)
sNummer = Replace(sNummer, "-", "")

iStart = 0
If Len(sNummer) = 12 Then iStart = 3
If Len(sNummer) = 10 Then iStart = 1
If iStart = 0 Then
	Err.Raise (1)
	Exit Function
End If
Dim temp As Integer
For i = iStart To Len(sNummer) Step 2
	temp = Mid(sNummer, i, 1)
	temp = temp * 2
	If (temp > 9) Then
	summa = summa + Int(temp / 10) + temp - 10
	Else
	summa = summa + temp
	End If
Next i
For i = iStart + 1 To Len(sNummer) - 1 Step 2

	temp = Mid(sNummer, i, 1)
	summa = summa + temp
Next i
temp = Right(sNummer, 1)
KontrolleraKontrollsiffra = ((10 - (summa - Int(summa / 10) * 10)) = temp)
End Function

 

som kan hantera personnummer givna som åååådddmm-xxxx som ååddmmxxxx och allt däremellan.

 

 

Kopiera, klistra in i en modul i VBA-editor (öppna med Alt+F11) och använd som en vanlig funktion.

 

Fast som jag brukar säga, när det går att lösa med formel bör man lösa det med en formel. Det ovan kan mer ses som ett facit till Pejos lösning men visst kan den duga

 

Vill man få ut kontrollsiffran så finns den i utrycket

(10 - (summa - Int(summa / 10) * 10))

Bara gör som funktion till att returnera Integer och se till att returnera den raden.

 

Redigerad 26 april, 2010 av Monshi

[Pejo]

Hmm, jag har visserligen bara testat den på ett par tre stycken personnummer, men där fungerade den. Har du dubbelkollat så att referenserna i formeln är riktiga?

 

Hälsningar

/Johan

 

Missade följande på Skatteverkets sida ...

Restsiffran (4) blir kontrollsiffra vilket gör att personnumret i exemplet blir 640823-3234. Är restsiffran 10, blir kontrollsiffran 0.

Formeln för kontrollsiffran får uppdateras enligt:

=REST(10 -REST(

HELTAL(EXTEXT($A1;1;1)*2/10)+REST(EXTEXT($A1;1;1)*2;10)+

HELTAL(EXTEXT($A1;2;1)*1/10)+REST(EXTEXT($A1;2;1)*1;10)+

HELTAL(EXTEXT($A1;3;1)*2/10)+REST(EXTEXT($A1;3;1)*2;10)+

HELTAL(EXTEXT($A1;4;1)*1/10)+REST(EXTEXT($A1;4;1)*1;10)+

HELTAL(EXTEXT($A1;5;1)*2/10)+REST(EXTEXT($A1;5;1)*2;10)+

HELTAL(EXTEXT($A1;6;1)*1/10)+REST(EXTEXT($A1;6;1)*1;10)+

HELTAL(EXTEXT($A1;8;1)*2/10)+REST(EXTEXT($A1;8;1)*2;10)+

HELTAL(EXTEXT($A1;9;1)*1/10)+REST(EXTEXT($A1;9;1)*1;10)+

HELTAL(EXTEXT($A1;10;1)*2/10)+REST(EXTEXT($A1;10;1)*2;10);10);10)

 

Uppdaterat ark:Personnummer.xls

 

Hälsningar

/Johan

[MissyMalinto]

Missade följande på Skatteverkets sida ...

 

Formeln för kontrollsiffran får uppdateras enligt:

=REST(10 -REST(

HELTAL(EXTEXT($A1;1;1)*2/10)+REST(EXTEXT($A1;1;1)*2;10)+

HELTAL(EXTEXT($A1;2;1)*1/10)+REST(EXTEXT($A1;2;1)*1;10)+

HELTAL(EXTEXT($A1;3;1)*2/10)+REST(EXTEXT($A1;3;1)*2;10)+

HELTAL(EXTEXT($A1;4;1)*1/10)+REST(EXTEXT($A1;4;1)*1;10)+

HELTAL(EXTEXT($A1;5;1)*2/10)+REST(EXTEXT($A1;5;1)*2;10)+

HELTAL(EXTEXT($A1;6;1)*1/10)+REST(EXTEXT($A1;6;1)*1;10)+

HELTAL(EXTEXT($A1;8;1)*2/10)+REST(EXTEXT($A1;8;1)*2;10)+

HELTAL(EXTEXT($A1;9;1)*1/10)+REST(EXTEXT($A1;9;1)*1;10)+

HELTAL(EXTEXT($A1;10;1)*2/10)+REST(EXTEXT($A1;10;1)*2;10);10);10)

 

Uppdaterat ark:Personnummer.xls

 

Hälsningar

/Johan

 

Johan, hade jag haft nån hatt skulle jag ha tagit av den för dig nu!

Briljant - nu funkar det! Tusen tack och kram på dig!

 

Linda

[Guraknugen]

Johan, hade jag haft nån hatt skulle jag ha tagit av den för dig nu!

Briljant - nu funkar det! Tusen tack och kram på dig!

 

Linda

 

Ett litet tips bara om man vill kunna jobba lite snabbare:

Strunta i att mata in ”-” i personnumret. Formatera istället cellerna enligt följande:

000000-0000

Då behöver du bara mata in exempelvis 8001019994 (påhittat personnummer) och strecket kommer ut automatiskt, så att säga.

 

Dock måste du ändra formeln lite för att det ska fungera:

=REST(10 -REST(

HELTAL(EXTEXT($A1;1;1)*2/10)+REST(EXTEXT($A1;1;1)*2;10)+

HELTAL(EXTEXT($A1;2;1)*1/10)+REST(EXTEXT($A1;2;1)*1;10)+

HELTAL(EXTEXT($A1;3;1)*2/10)+REST(EXTEXT($A1;3;1)*2;10)+

HELTAL(EXTEXT($A1;4;1)*1/10)+REST(EXTEXT($A1;4;1)*1;10)+

HELTAL(EXTEXT($A1;5;1)*2/10)+REST(EXTEXT($A1;5;1)*2;10)+

HELTAL(EXTEXT($A1;6;1)*1/10)+REST(EXTEXT($A1;6;1)*1;10)+

HELTAL(EXTEXT($A1;7;1)*2/10)+REST(EXTEXT($A1;7;1)*2;10)+

HELTAL(EXTEXT($A1;8;1)*1/10)+REST(EXTEXT($A1;8;1)*1;10)+

HELTAL(EXTEXT($A1;9;1)*2/10)+REST(EXTEXT($A1;9;1)*2;10);10);10)

 

Det kan tyckas som en liten tidsbesparing att slippa mata in ett streck, men jag tror man kommer att uppleva det som positivt att bara behöva mata in siffrorna.

 

Har inga möjligheter att testa i Excel (när kommer Linuxversionen av Excel…? ;D ), men det funkade i OpenOffice.org Calc i alla fall, så det borde funka även i Excel.

[Tom Düring]

I Pejos Execel-fil från 2010 får man "FALSK" även om ett personnummer stämmer, om kontrollsiffran är 0 (Noll).

Hur kan man kringgå detta ?

[Pejo]

I Pejos Execel-fil från 2010 får man "FALSK" även om ett personnummer stämmer, om kontrollsiffran är 0 (Noll).

Hur kan man kringgå detta ?

Oj, här var det gamla synder ...

Men du har testat den uppdaterade formeln från inlägg #15?

 

Hälsningar

/Johan

[Tom Düring]

Tack! Nu har det löst sig. Hade uppenbarligen tagit formeln från fel tråd!

//Tom

[Guraknugen]

Hmmm, varför göra det enkelt?

 

Underskatta aldrig den uttråkade hjärnans förmåga att krångla till det

 

=REST(10-REST(SUMMA(REST(EXTEXT(A1;{1;2;3;4;5;6;7;8;9};1)*{2;1;2;1;2;1;2;1;2};10);AVKORTA(EXTEXT(A1;{1;7;9};1)*{2;2;2}/10;0));10);10)

 

Eller så här om man vill slippa bry sig om streck

=REST(10-REST(SUMMA(REST(EXTEXT(BYT.UT(A1;"-";"");{1;2;3;4;5;6;7;8;9};1)*{2;1;2;1;2;1;2;1;2};10);AVKORTA(EXTEXT(BYT.UT(A1;"-";"");{1;7;9};1)*{2;2;2}/10;0));10);10)

 

Gav svaret 7 för mitt personnummer. Ska vara 6. Kan vara så att LibreOffice inte stöder den syntaxen (men jag fick inget felmeddelande). Excel kan jag inte köra då det saknar för många direkt avgörande finesser, exempelvis att kunna installeras i många olika operativsystem (eller åtminstone i de operativsystem jag har tillgång till).

Ska kolla på om det är en bugg i LibreOffice eller om det är något som de inte hunnit införa än. Kan jag låna en av ovanstående formler för ändamålet? Och är de testkörda för alla tio olika slutsiffror, så att vi vet att det ska bli rätt?

 

Bonus till den som listar ut varför det blir {1;7;9} i avkorta-delen

 

 

 

PS Summa måste eventuellt bytas mot produktsumma i äldre versioner av excel och då får man nog köra alla tal

 

 

PPS

Såg att ursprungs frågaren ville ha sant/falskt. Då blir det så här:

=HÖGER(A1;1)*1=REST(10-REST(SUMMA(REST(EXTEXT(BYT.UT(A1;"-";"");{1;2;3;4;5;6;7;8;9};1)*{2;1;2;1;2;1;2;1;2};10);AVKORTA(EXTEXT(BYT.UT(A1;"-";"");{1;7;9};1)*{2;2;2}/10;0));10);10)*1

[Guraknugen]

Hej Johnny. Jo, den är testad på ett gäng personnummer jag hade liggande

 

Prövar i Libre office nu och det är flera saker som inte fungerar.

Egentligen är det inte Libre/open som buggar utan excel 2010 som tillåter ganska ostrikta formler.

 

 

Dels accepterade excel att använda summa för att skapa en "pasiv" matrisformel. Det skall egentligen inte gå utan bör kräva produktsumma(). Tror inte att en äldre excel hade svalt den formeln heller.

 

Den andra saken är att Excel tillåter "slarv" och översätter en siffra skriven som text till tal. Det är ju bra i det här fallet men kan vara illa i andra fall. Libre är lite strängare och kräver att man omvandlar text till siffror, Dvs använder Textnum

 

Det tråkiga är ju att min korta formel blir nästan lika lång som de andra

 

Det här borde fungera för dig

=REST(10-REST(PRODUKTSUMMA(REST(TEXTNUM(EXTEXT(A1;{1;2;3;4;5;6;7;8;9};1))*{2;1;2;1;2;1;2;1;2};10))+PRODUKTSUMMA(AVKORTA(TEXTNUM(EXTEXT(A1;{1;7;9};1))*{2;2;2}/10;0));10);10)

Eller så här om man bara vill ha en produktsumma

 

=REST(10-REST(PRODUKTSUMMA(REST(TEXTNUM(EXTEXT(A1;{1;2;3;4;5;6;7;8;9};1))*{2;1;2;1;2;1;2;1;2};10)+AVKORTA(TEXTNUM(EXTEXT(A1;{1;2;3;4;5;6;7;8;9};1))*{2;1;2;1;2;1;2;1;2}/10;0));10);10)

 

 

Sant/falskt

=TEXTNUM(HÖGER(A1;1))=REST(10-REST(PRODUKTSUMMA(REST(TEXTNUM(EXTEXT(A1;{1;2;3;4;5;6;7;8;9};1))*{2;1;2;1;2;1;2;1;2};10))+PRODUKTSUMMA(AVKORTA(TEXTNUM(EXTEXT(A1;{1;7;9};1))*{2;2;2}/10;0));10);10)

 

 

PS

Den bygger på att man INTE har med sekelsiffror. Man kan naturligtvis fixa till så att den fungerar med sekelsiffor också, men det är överkurs.

 

Okej, testade den mellersta formeln på några personnummer och det funkade. Är inte så bekant med syntaxen och använder inte PRODUKTSUMMA så ofta, även om det händer (dessutom har jag numera LibreOffice Calc inställd på att visa formler på engelska för att det ska bli lättare att få hjälp eller hjälpa till i engelskspråkiga mailinglistor och liknande). Antar att jag får läsa på lite när det gäller ”passiva matriser” och produktsummor…

[Guraknugen]

Kom förresten på att min variant också var lite onödigt lång och går att förkorta avsevärt bara genom att ta bort allt som räknas ut i onödan (multiplicering med 1 och liknande):

=REST(10-REST(HELTAL(EXTEXT($A1;1;1)/5)+REST(EXTEXT($A1;1;1)*2;10)+
				REST(EXTEXT($A1;2;1);10)+
      HELTAL(EXTEXT($A1;3;1)/5)+REST(EXTEXT($A1;3;1)*2;10)+
				REST(EXTEXT($A1;4;1);10)+
      HELTAL(EXTEXT($A1;5;1)/5)+REST(EXTEXT($A1;5;1)*2;10)+
				REST(EXTEXT($A1;6;1);10)+
      HELTAL(EXTEXT($A1;7;1)/5)+REST(EXTEXT($A1;7;1)*2;10)+
				REST(EXTEXT($A1;8;1);10)+
      HELTAL(EXTEXT($A1;9;1)/5)+REST(EXTEXT($A1;9;1)*2;10);10);10)

Blir dock fortfarande längre än den kortare varianten med ”passiva matriser”, som säkert också går att förkorta på motsvarande sätt även om inte jag lyckades så bra med det. Fick fel resultat och orkade inte felsöka (är magsjuk för tillfället och har inte så mycket ork…).

[Guraknugen]

Kom förresten på att min variant också var lite onödigt lång och går att förkorta avsevärt bara genom att ta bort allt som räknas ut i onödan (multiplicering med 1 och liknande):

=REST(10-REST(HELTAL(EXTEXT($A1;1;1)/5)+REST(EXTEXT($A1;1;1)*2;10)+
				REST(EXTEXT($A1;2;1);10)+
      HELTAL(EXTEXT($A1;3;1)/5)+REST(EXTEXT($A1;3;1)*2;10)+
				REST(EXTEXT($A1;4;1);10)+
      HELTAL(EXTEXT($A1;5;1)/5)+REST(EXTEXT($A1;5;1)*2;10)+
				REST(EXTEXT($A1;6;1);10)+
      HELTAL(EXTEXT($A1;7;1)/5)+REST(EXTEXT($A1;7;1)*2;10)+
				REST(EXTEXT($A1;8;1);10)+
      HELTAL(EXTEXT($A1;9;1)/5)+REST(EXTEXT($A1;9;1)*2;10);10);10)

Blir dock fortfarande längre än den kortare varianten med ”passiva matriser”, som säkert också går att förkorta på motsvarande sätt även om inte jag lyckades så bra med det. Fick fel resultat och orkade inte felsöka (är magsjuk för tillfället och har inte så mycket ork…).

 

Fortfarande har jag med saker som alltid blir noll… Så här blir det ännu kortare, med samma resultat:

=REST(10-REST(HELTAL(EXTEXT($A1;1;1)/5)+REST(EXTEXT($A1;1;1)*2;10)+
				REST(EXTEXT($A1;2;1);10)+
				REST(EXTEXT($A1;3;1)*2;10)+
				REST(EXTEXT($A1;4;1);10)+
				REST(EXTEXT($A1;5;1)*2;10)+
				REST(EXTEXT($A1;6;1);10)+
      HELTAL(EXTEXT($A1;7;1)/5)+REST(EXTEXT($A1;7;1)*2;10)+
				REST(EXTEXT($A1;8;1);10)+
      HELTAL(EXTEXT($A1;9;1)/5)+REST(EXTEXT($A1;9;1)*2;10);10);10)

 

Eller som den ser ut i formelraden:

=REST(10-REST(HELTAL(EXTEXT($A1;1;1)/5)+REST(EXTEXT($A1;1;1)*2;10)+REST(EXTEXT($A1;2;1);10)+REST(EXTEXT($A1;3;1)*2;10)+REST(EXTEXT($A1;4;1);10)+REST(EXTEXT($A1;5;1)*2;10)+REST(EXTEXT($A1;6;1);10)+HELTAL(EXTEXT($A1;7;1)/5)+REST(EXTEXT($A1;7;1)*2;10)+REST(EXTEXT($A1;8;1);10)+HELTAL(EXTEXT($A1;9;1)/5)+REST(EXTEXT($A1;9;1)*2;10);10);10)

Fortfarande ganska långt, men troligen snabbare eftersom färre saker behöver räknas ut.

 

Om man läser #23 så ser man dessutom att varianten där med två produktsummor gör samma sak som den jag nyss skrev. Hade jag studerat den lite närmare med en gång hade jag sluppit att skriva väldigt mycket av det jag skrivit nyligen. Så kan det gå…

Men det var kul att komma fram till det själv, även om det tog tid för min långsamma hjärna… ha ha ha…

 

Min fundering nu är vilken som går snabbast. Oväsentligt när man bara använder uttrycket i en cell, men ska man fylla en kolumn med några hundra rader kan det kanske ha betydelse. Har dock ingen bra idé hur man testar detta på ett vetenskapligt sätt. Makron är enkla att ta tid på, men cellformler? Kanske lika enkelt, men jag har aldrig försökt…

Redigerad 9 januari, 2013 av Guraknugen

[Guraknugen]

Min gissning är att det tar ungefär lika lång tid. Matrisformler anses vara långsamma, men det är när man slår upp värden i långa cellintervall. Nu gör ju formlerna exakt samma sak så de borde vara jämnsnabba, möjligtvis en viss fördel till dina "manuella".

 

med 65 000 personnummer så skulle du möjligtvis tjäna någon 1/10 sekund på att slänga in "TEXTNUM" runt varje EXTEXT. Formeln blir längre men programmet slipper gissa datatyp. Misslyckande för "min" formel berodde ju på att typkonverteringen var svår att hantera för programmet.

 

Den enda gången jag testar formelhastighet brukar vara ofrivilligt. T.ex när man behöver skapa en ny variabel i en lång tabell. Då märks det tydligt att matrisformler har det jobbigt, men då brukar formeln titta på hela kolumnen, det är klart att det blir jobbigt när formeln upprepas på varje rad

 

Såg att det gick att göra din ännu lite kortare i alla fall, om än kanske inte snabbare: Byt ut AVKORTA mot HELTAL… Inte nog med att HELTAL har färre bokstäver, du slipper ett argument också. Har ingen som helst praktisk betydelse, men nu har jag sagt det i alla fall.

 

En sak som jag ibland gjort som visat sig gå fruktansvärt långsamt, i alla fall i LibreOffice, är när man har villkorlig formatering på ett stort område, där man i villkoren har med saker som MAX() eller MIN(), eller värre, där argumenten för dessa är en fast referens till ett stort område. Om området är 10 000 rader, 10 kolumner och om MAX() exempelvis inkluderar en kolumn på 10 000 rader, så kommer alltså varje cell i det området att gås igenom 10 000 · 10 000 · 10 gånger… per villkor i värsta fall! Alltså en miljard gånger…

Där kan man vinna mycket på att räkna ut MAX()- och/eller MIN()-funktionerna en gång för alla i var sin cell istället (om referenserna är fasta, alltså typ MAX($A$1:$A$10000), så behöver de bara räknas ut en enda gång var. Egna tester tyder på att detta är fruktansvärt väsentligt för snabbheten, i alla fall i LibreOffice. Har som sagt ingen möjlighet att testa i Excel.

Redigerad 9 januari, 2013 av Guraknugen