Specialformler & Formeltricks

© 2000 - 2006 All rights Aladin Akyurek Translated and published by XL-Dennis with permission

XL-Dennis är glad och stolt över att presentera formler och tricks skapade av Aladin Akyurek. Aladin har ett stort  kunnande och en bred kompetens inom området kalkylbladsfunktioner och formler.

Största talet

En av Aladins större bidrag är att mha av det största talet Excel kan hantera bygga upp effektiva och ändå enkla formler.

Det största talet Excel kan hantera är: 9,99999999999999E+307 !

För att slippa ange detta tal varje gång vi vill använda oss av det är det enklast att definiera ett namn och tilldela namnet detta värde.

I alla exempel här används namnet "STal".

Finessen med detta tal är att vi alltid kan hitta det närmaste talet till detta högsta tal

Hitta sista cellen med ett talvärde

I det första tipset demonstreras hur vi kan identifiera den sista cellen i en kolumn med ett talvärde i sig.

Följande bild visar exemplet i sin helhet:

 

Följande formel ger oss adressen till den sista cellen:

• =ADRESS(PASSA(STal;A:A);1)

 

Följande formel ger oss värdet som sista cellen håller:

• =LETAUPP(STal;A:A)

 

Vill vi t ex erhålla radnumret för det senast inmatade värdet kan följande formel användas:

• =PASSA(STal;A:A)

 

Löpande summering

Här visas hur vi kan användas oss av STal för att summera allteftersom värden matas in i en kolumn.

Exemplet belyses i följande bild:

Följande formel skapar förutsättningen för löpande summering:

• =SUMMA(A2:INDIREKT(ADRESS(PASSA(STal;A:A);1)))

 

Saknade nummer i sekventiella serier

Här visas ytterligare ett exempel på hur vi mha sTal kan lösa mer komplexa situationer.

Exemplet i sin helhet visas i följande bild:

 

Följande formel ger oss antalet saknade nummer:

• =(MAX(A:A)-MIN(A:A)+1)-PASSA(STal;A:A)-(CELL("Rad";A1)-1)

 

För att få färgmarkering används villkorsstyrd formatering där villkoret för cellområdet utgörs av:

• =ICKE(A2=A1+1)

 

Sista värdet i raden

Här demonstreras en formelteknisk lösning som kan användas för både numeriska som alfanumeriska värden.

Exemplet i sin helhet visas i nedanstående bild:

 

Formel som ger oss lösningen är:

• =INDEX(A2:F2;PASSA(SANT;INDEX(A2:F2<>"";1;0)))

 

Se också: Hitta sista värdet i kolumnen/raden

Omvandla alfanumeriska tal till numeriska tal

I detta exempel demonstreras en annorlunda teknik för att omvandla alfanumeriska tal till numeriska tal. Traditionellt sker det med uttrycket 1* eller +0.

 

Samma logik som rangordnar beräkningsordningen I Excel för de fyra beräkningssätten (division/multiplikation och addition/subtraktion) är applicerbar även i detta sammanhang:

Excel rangordnar formlerna enligt följande uppställning:

• 1 - =--HÖGER(A2;3)
• 2 - =HÖGER(A2;3)+0
• 3 - =HÖGER(A2;3)*1

 

Hitta värdet till höger om maxvärdet

Här visas en teknik för att hitta värdet till höger om maxvärdet i en lista.

Exemplet utgår från följande uppställning:

 

Formeln som ger oss talet i Lista 2 är följande:

• =INDEX($B$2:$B$5;PASSA(MAX($A$2:$A$5);$A$2:$A$5;0))

 

Räkna antal förekomster av textvärden

Att räkna förekomsten av text i en lista kan ske på ett flertal sätt men de tekniker som demonstreras här tillhör onekligen kategorin finurliga lösningar.

Exemplet i sin helhet visas i följande bild:

 

I den första lösningen används följande formel:

• =PRODUKTSUMMA((A2:A8={"AA";"EE"})+0)

I den andra används följande formel:

• =PRODUKTSUMMA(--(A2:A8={"AA";"EE"}))

 

Extrahera text

I detta exempel visas hur vi kan plocka ut text ur en kombination av text- och talvärden i önskade celler.

Exemplet och lösningen visas i följande bild:

 

För de två första cellerna används följande matrisformel:

• {=BYT.UT(A1;HÖGER(A1;PRODUKTSUMMA((LÄNGD(A1)-LÄNGD(BYT.UT(A1;{0;1;2;3;4;5;6;7;8;9};"")))));"")}

 

För de två sista cellerna används följande matrisformel:

• {=BYT.UT(A3;VÄNSTER(A3;PRODUKTSUMMA((LÄNGD(A3)-LÄNGD(BYT.UT(A3;{0;1;2;3;4;5;6;7;8;9};"")))));"")}

 

Sista textvärdet i kolumnen

Här visas ett relativt enkelt sätt för att erhålla det sista textvärdet i en kolumn.

Tipset visas i sin helhet i bilden nedan:

 

Följande formel används i cell C1:

• =LETAUPP(REP("z";255);A:A)

 

Se också: Hitta sista värdet i kolumnen/raden