Hitta kvinnor och män via personnummer

Ladda ner exempelfilen: Udda eller jämnt.xlsx

I detta exempel ska du ta reda på om personer är kvinnor eller män och dessutom räkna hur många de är.

Du kanske vill ansöka om kommunala eller statliga bidrag för en förening och behöver ange dessa uppgifter. Även om du skulle ha tillgång till förnamn på personerna så framgår det inte alltid om det är en man eller kvinna. Vissa namn bärs av både kvinnor och män.

I svenska personnummer går det att se könstillhörighet på den näst sista siffran. Pojkar får ojämna siffror och flickor jämna siffror.

För att Excel ska kunna räkna ut detta behöver du skriva en formel som använder flera funktioner.

Extrahera värde

Först behöver Excel hitta den näst sista siffran. Till det går det att använda funktionen EXTEXT [MID] för att extrahera värden ur celler.

=EXTEXT(cell;startposition;antal tecken)

=MID(cell;startposition;antal tecken)

Funktionen behöver tre värden; vilken cell som värdet ska hämtas från, hur många positioner in i cellen något ska hämtas och hur många tecken som ska hämtas.

I detta exempel är A2 cellen där personnumret står, näst sista siffran står tio positioner in i cellen och det är endast ett tecken som ska hämtas.

=EXTEXT(A2;10;1)

=MID(A2;10;1)

Jämnt eller ojämnt tal

Nästa steg är att ta reda på om den hämtade siffran är ett jämnt eller ojämnt tal. Till detta går det att använda två funktioner:

=ÄRUDDA(värde) eller =ÄRJÄMN(värde)

=ISEVEN(värde) eller =ISODD(värde)

I detta exempel spelar det ingen roll vilken av funktionerna som används, runt om den första funktionen, resultatet kommer bli SANT eller FALSKT. Formeln blir:

=ÄRUDDA(EXTEXT(A2;10;1))

=ISODD(MID(A2;10;1))

Villkorstext

Excel vet inte att pojkar har ojämna och flickor jämna siffror i personnumret. Genom att lägga till en funktion som hanterar villkor går det att skriva ut vilka som är kvinnor och män. Funktionen OM [IF] löser det problemet.

=OM(villkor; värde om sant; värde om falskt)

=IF(villkor; värde om sant; värde om falskt)

Eftersom den tidigare formeln resulterade i SANT eller FALSKT går det att skriva så här:

=OM(ÄRUDDA(EXTEXT(A2;10;1));”Man”;”Kvinna”)

=IF(ISODD(MID(A2;10;1));”Man”;”Kvinna”)

Tips 1

Texten som ska bli resultatet i cellen omges med ”citattecken” i formeln, så Excel förstår att det är en text och inte ett område i Excel som avses. Det finns ju till exempel en kolumn som heter MAN.

Tips 2

Formeln kan även skrivas =OM(ÄRUDDA(EXTEXT(A2;10;1))=SANT;”Man”;”Kvinna”) men då blir formeln längre.

Tips 3

Ibland skrivs personnummer med 12 siffror. Då behöver du justera från vilken position värdet ska hämtas. I detta exempel från position tio till position tolv.

Det går också att använda funktionen ÄRJÄMN [ISEVEN] och vända på villkoret. Då går det att skriva formeln så här =OM(ÄRJÄMN(EXTEXT(A15;12;1))<>SANT;”Man”;”Kvinna”).

Tips 4

Är antalet siffror i personnummer ibland inmatade med tio och ibland tolv siffror och eventuellt med eller utan bindestreck, kan Excel räkna antalet tecken i cellen med personnummer.

Funktionen LÄNGD [LEN] kan räkna antalet tecken i en cell och svaret går sedan att minska med ett tecken, eftersom det är det näst sista tecknet som ska hämtas. Då blir formeln: =OM(ÄRUDDA(EXTEXT(A28;LÄNGD(A28)-1;1));”Man”;”Kvinna”)

Tips 5

Från version 2021 av Excel och om du har en Microsoft 365 prenumeration behöver du inte längre kopiera ner formeln till övriga rader.

I stället för att endast ange att formeln ska hämta näst sista tecknet från cellen A2, kan du ange att näst sista tecknet ska hämtas från alla celler i området A2:A11.

Då kommer formeln SPILLAS till ett motsvarande område nedanför formeln. I detta exempel finns bara formeln i cellen B2, men SPILLER ner till de övriga raderna.

Beräkna antal

För att beräkna hur många kvinnor och män det finns i listan går det att använda funktionen ANTAL.OM [COIUNTIF]. Funktionen behöver två argument; vilket område som ska undersökas och villkoret som ska vara uppfyllt.

Det går att skriva in villkoret i formeln förhand men det är bättre att hämta villkoret från en cell, för då går det att kopiera formeln till fler ställen.

=ANTAL.OM(B2:B11;E2)

= COIUNTIF (B2:B11;E2)

Tips 6

Även i formeln för att räkna antalet går det att använda den nya SPILL-funktionen i Excel.

Det går att hänvisa till ett SPILL-område med tecknet #.

Cellen B2 i detta exempel innehåller en formel som genererar ett SPILL-område. För att hänvisa till det området används brädgårdstecknet # efter cellens namn.

Brädgårdstecknet # representerar ett område med ett än så länge okänt antal rader (och eventuellt kolumner). SPILL-området kan ju ändra storlek beroende på antalet personer i listan, så det visas ett värdefel på den raden i funktionsguiden.

Om mer än en cell anges som villkorsområde kommer även denna formel SPILLA till ett område med motsvarande storlek.