Kalkylering VII

Välj tips & tricks ur denna lista Räkna med filter Erhålla det närmast högre värdet i en lista Senaste inmatade radvärdet PRODUKTSUMMA med flera villkor för en och samma kolumn Kontroll av personnummer Hantering av felvärden vid beräkningar Hitta rad- och kolumnetiketter Autofilter och summera med villkor Maxresultat Radstyrd summering Räkna antal rader och kolumner mellan två värden Multiplicera två listor Summera var x:e rad över flera kolumner Räkna antal förekomster i flera blad Vänd på listan! Avancerad summering Räkna de senaste värdena Bevara referenserna Visa radnummer för duplikat Komplicerat medelvärde Löpande beräkning av medelvärde Sammanställa var x:e rads data Räkna ej med Undvik de x lägsta värdena Förekomst Skapa uppslagsvärden mha av cellfärg Skapa intervallserier Delbart med x och y Summa x rader eller kolumner Delsummering i flera steg Räkna med sökbegrepp Rad med största / minsta värdet Intervallberäkningar Poängberäkning med faktor Städa bort skräptecken Löpande beräkning Cirkumflexberäkning Kalkylbladsberäkningar Kolumn- & radberäkningar Fasta decimaler Manuell beräkning Snabb uppdatering Namnge konstanter XL som miniräknare Funktionen PRODUKT Funktionen PRODUKTSUMMA Avrunda små tal Avrunda stora tal Avrunda till heltal Avrunda till önskat antal siffror Flexibel kalkylering Räkna tomma / ej tomma celler Beräkna dynamiska dataserier Konvertera text till tal Räkna med ekvationer! Räkna antal i en diskontinuerlig markering Summera flera kalkylblad Hitta det närmaste värdet Medelvärde Högsta/Lägsta notering Stegvis Värdeökning Löpande summering I vilken kolumn? Slumpa fram namn Summera värden i cellintervall Räkna med merkostnader Sortering av alfanumeriska värden Summera utestående fordringar Räkna förekomsten av flera värden Omvandla rader till kolumner Transponera importerad data

 

Räkna med filter

Antag att vi har en lista med avdelningar och utfall. Antag vidare att vi gör ett urval av avdelningar och att vi då vill erhålla antal poster som förekommer i ett givet intervall bland de synliga cellerna.

Intervallet som antal poster ska räknas inom är >=10 och <=20

Här är listan innan filtrering sker:

 

Efter det att listan har blivit filtrerad utifrån Avd (=AA) så ser utfallet ut så här:

 

Så här ser formeln ut i cell D3:

• =PRODUKTSUMMA(DELSUMMA(3;FÖRSKJUTNING(B2:B10;RAD(B2:B10 )-MIN(RAD(B2:B10 ));;1))*(B2:B10>=10)*(B2:B10<=20))

 

Erhålla det närmast högre värdet i en lista

I detta lilla tips demonstreras hur vi kan hämta värden ur listor som antingen är lika med det sökta värdet eller om det inte finns det närmast högre värdet.

Exemplet i sin helhet visas i följande bild:

 

För att erhålla värdet i A-listan används följande matrsformel:

• {=INDEX(A2:A5;PASSA(SANT;(D1<=A2:A5);0))}

 

Värdet från B-listan hämtas på basis av det erhållna värdet i A-listan varför följande formel används:

• =INDEX(B2:B5;PASSA(MINSTA(A2:A5;ANTAL.OM(A2:A5;"<"&D1)+1);A2:A5;0))

 

Senaste inmatade värdet i raden

Här i detta tips belyses en formelteknik för att alltid erhålla det senaste inmatade värdet i en rad. Lösningen förutsätter att inga tomma celler förekommer mellan värdena.

Bilden nedan visar exemplet i sin helhet:

 

Följande formel används:

• =FÖRSKJUTNING(B2;0;ANTALV(2:2)-1)

 

PRODUKTSUMMA med flera villkor för en och samma kolumn

Tipset demonstrerar hur vi kan ställa flera villkor för en kolumn för att summera poster.

Förutsättningarna och dess lösning framgår i följande bild:

 

Villkoren är att summering ska ske för de poster vars produktnamn

• Börjar på AZ och
• Slutar på 1

 

Följande formel används:

• =PRODUKTSUMMA((VÄNSTER(B2:B8;2)="AZ")*(HÖGER(B2:B8;1)="1")*C2:C8)

 

Personnummerkontroll

Här visas ett sätt att kontrollera kontrollera personnummer mha av den s k modulus-metoden med vikterna 2 och 1 där beräkningen resulterar i den sista talet i personnumret, den s k kontrollsiffran.

Beräkningsgrunden är följande:

1. Varannan siffran multipliceras med 2 och de övriga med 1 för de första 9 siffrorna såsom:
   2, 1,2,1,2,1,2,1,2
2. De framräknade siffrorna adderas ihop med det tillägget att tal överstigande 10 delas upp i två tal som summeras med varandra såsom 14 = 1 + 4 = 5.
3. Entalssiffran i det framräknade värdet dras ifrån värdet 10.
4. Restsiffran utgörs av korrekt kontrollsiffra.

 

Följande bild visar exemplet i sin helhet:

 

Följande formler används:

• Cell B6: =OM(LÄNGD(VÄNSTER($A$2;1)*2)=1;VÄNSTER($A$2;1)*2;
  VÄNSTER(VÄNSTER($A$2;1)*2;1)+HÖGER(VÄNSTER($A$2;1)*2;1))
• Cell B3 och nedåt:
  =OM(LÄNGD(EXTEXT($A$2;RAD()-1;1)*OM(REST(RAD()-1;2)=0;1;2))=1;EXTEXT($A$2;RAD()-1;1)*OM(REST(RAD()-1;2)=0;1;2);VÄNSTER(EXTEXT($A$2;RAD()-1;1)*OM(REST(RAD()-1;2)=0;1;2);1)+HÖGER(EXTEXT($A$2;RAD()-1;1)*OM(REST(RAD()-1;2)=0;1;2);1))
• Cell C2: =10-HÖGER(SUMMA(B2:B10);1)

 

Tilläggas bör att det går också bra att ange personummer i sin helhet då ovanstående formler inte beaktar den 10:e siffran.

I en färdig lösning kan B-kolumnen i sin helhet döljas och en kontroll kan läggas till formeln i C2 om huruvida inmatningscellen är tom eller inte.