startsida < företaget < organisera < datorer < tips
 
Kalkylering V

 
  Kalkylering
Kalkylering I
Kalkylering II
Kalkylering III
Kalkylering IV
Kalkylering V
Kalkylering VI
Kalkylering VII
Villkorsstyrd
kalkylering
Villkorsstyrd
formatering I
Villkorsstyrd
formatering II
Felhanterings-
kalkylering
Matriskalkylering I
Matriskalkylering II
Matriskalkylering III
Matriskalkylering IV
Matriskalkylering V
Datumkalkylering I
Datumkalkylering II
Datumkalkylering III
Datavalidering I
Datavalidering II
Cirkelreferenser
   
  Innehåll
expowera

 

Materialet publicerat med tillstånd från Dennis Wallentin.

Räkna de senaste värdena
Det förekommer en hel del frågor kring "hur kan jag räkna med de xx senaste värdena i en lista?" och detta tips har därför tillkommit för att bringa klarhet kring denna fråga.

Detta förfarande passar alldeles utmärkt i de situationer där data löpande läggs till listan.

Exemplet har sin utgångspunkt i följande uppställning:

Den generella formeln är:

  • =FUNKTION(FÖRSKJUTNING(StartCell;ANTALV(StartCell:SlutCell)-
    ANTAL CELLER;0;ANTAL CELLER;1))

I exemplet vill vi räkna med de senaste 3 angivna värdena såsom

SUMMA: =SUMMA(FÖRSKJUTNING(A2;ANTALV(A2:A65536)-3;0;3;1))

MEDEL: =MEDEL(FÖRSKJUTNING(A2;ANTALV(A2:A65536)-3;0;3;1))

Vill vi t ex räkna med de senaste 20 värdena ersätts således värdet 3 i formeln med 20.

Bevara referenserna
När vi t ex tar bort celler och vi refererar till det berörda cellområdet justerar XL automatiskt cellreferenserna till det nya cellområdet.

Här demonstreras en teknik för att bibehålla referenserna till det ursprungliga cellområdet.

Antag att vi använder oss av följande formel:

  • =LETARAD(A2;$D$2:$E$10;2;0)

Antag vidare att vi tar bort cellområdet D8:E10 då ändrar XL referensen till att endast omfatta cellområdet $D$2:$E$7 och formeln ser ut såsom:

  • =LETARAD(A2;$D$2:$E$7;2;0)

För att förhindra att XL justerar cellområdet måste vi använda oss av INDIREKT-funktionen:

  • =LETARAD(A2;INDIREKT("$D$2:$E$10");2;0)

Därmed får vi en formellösning som inte påverkas av borttag eller insättning av celler, rader eller kolumner.

Visa radnummer för duplikat
Här visas en teknik för att erhålla radnumrerna för de värden i en lista som förekommer flera gånger i listan.

Exemplet har sin utgångspunkt i följande uppställning:

Följande formel löser det upplevda problemet och skapas i cell B2 och kopieras till rad 16 (ej den sista raden) i B-kolumnen.

  • =OM(ÄRSAKNAD(PASSA(A2;$A3:$A$17;0));"";PASSA
    (A2;$A3:$A$17;0)+RAD())

Komplicerat medelvärde
Här demonstreras en lösning för att erhålla medelvärdet då följande förhållanden finns:

  • Räkna endast de poster i Namn-kolumnen som har värden.
     
  • Räkna endast de poster i Antal-kolumnen som har värden.
     
  • Beräkna medelvärdet utifrån dessa förhållanden.

Nedanstående bild visar exemplet i sin helhet:

Formeln som ger oss medelvärdet ser ut på följande sätt:

  • =PRODUKTSUMMA((LÄNGD(A2:A8)>0)*(ÄRTAL(B2:B8))*B2:B8)/
    MAX(1;PRODUKTSUMMA((LÄNGD(A2:A8)>0)*(ÄRTAL(B2:B8))))

Löpande beräkning av medelvärde
I detta tips ska vi titta närmare på en avancerad problemställning för uträkning av ett medelvärde.

Bilden nedan visar en lista som ifylls löpande i A-kolumnen:


Problemet som ska lösas är att beräkna medelvärdet för de senaste 5 inmatade värdena med avdrag för de senaste 10 inmatade värdenas medelvärde.

Medelvärdet i exemplet (vilket ej framgår av bilden ovan) för de senaste 5 inmatade värdena är:

  • (26+27+28+29+30=140)/5=28

Medelvärdet för de senaste 10 inmatade värdena är:

  • (21+22+23+24+25+26+27+28+29+30=255)/10=25,5

Medelvärdet utifrån problemuppställningen är 28 - 25,5 = 2,5

Den tekniska lösningen består av följande tre formler:

  • Vilken rad som håller det sista inmatade värdet: {=MAX(RAD(A1:A1000)*(A1:A1000<>""))}
     
  • Summa av alla inmatade värden: =SUMMA(A:A)
     
  • Beräkningen av medelvärdet:
    =((C2-SUMMA(A2:INDEX(A:A;C1-5)))/5)-((C2-SUMMA(A2:INDEX
    (A:A;C1-10)))/10)

Sammanställa var x:e rads data
Här demonstreras hur vi kan hämta värden ur en lista där värdena hämtas från var 3:e rad. Från vilken rad vi hämtas data kan enkelt justeras i formeln nedan.

Bilden nedan visar exemplet i sin helhet:

Formeln som ger de önskade värden skapas i exemplet i C2 och kopieras så långt det är önskvärt nedåt i C-kolumnen:

  • =INDEX(A:A;RAD()*3;0)

Räkna ej med
I detta tips demonstreras hur vi kan räkna förekomsten av värden vi ej vill ha med för att erhålla antalet av de värden vi vill ha.

Bilden nedan visar exemplet i sin helhet:

I exemplet vill vi ej räkna med värdena "AAA", "CDA" och "EEG".

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

  • =PRODUKTSUMMA(ICKE(ÄRTAL(PASSA(A2:A18;
    {"AAA";"CDA";"EEG"};0)))+0)

I den andra lösningen används nedanstående formel:

  • =ANTALV(A2:A20)-ANTAL.OM(A2:A20;"=AAA")-ANTAL.OM(A2:A20;"=
    CDA")-ANTAL.OM(A2:A20;"=EEG")

Båda formlerna har sina fördelar varför den faktiska situationen får styra formelvalet.

Undvik de x lägsta värdena
Här är ett tips på den frekventa frågan "hur kan jag undvika att ta med de x lägsta värdena i en lista?".

Istället för den traditionella uppställningen i en kolumn utgår exemplet från en rad med värden, såsom följande bild visar:

Formeln för att ge summan, exklusive de 3 lägsta värden, i exemplet är:

  • =SUMMA(A1:G1)-SUMMA(MINSTA(A1:G1;{1;2;3}))

Formeln som ger oss medelvärdet, exklusive de 3 lägsta värdena, i exemplet är:

  • =(SUMMA(A1:G1)-SUMMA(MINSTA(A1:G1;{1;2;3})))/(ANTAL(A1:G1)-3)

Vill vi eliminera t ex de 3 högsta värdena ersätts funktionen MINSTA med STÖRSTA-funktionen.

Förekomst
I detta tips visas hur vi relativt enkelt kan få fram det mest frekventa värdet och hur många gånger det förekommer i en lista.

Tipset har sin utgångspunkt och lösning i följande bild:

För att erhålla värdet används följande formel:

  • =INDEX(A2:A10;TYPVÄRDE(PASSA(A2:A10;A2:A10;0)))

För att erhålla antal gånger värdet förekommer används följande formel:

  • =ANTAL.OM(A1:A10;INDEX(A1:A10;TYPVÄRDE(PASSA
    (A1:A10;A1:A10;0))))

Skapa uppslagsvärden mha cellfärg
Här visas hur vi med olika tekniker och mha av den egenutvecklade funktionen CELLFARGER kan skapa en lösning där olika färger styr utfallet.

Exemplet i sin helhet visas i följande bild:

Värdet erhålls med följande formel:

  • =INDEX(A1:D5;PASSA(G2;A1:A5);PASSA(G1;A1:D1))

Cellreferens erhålls med följande formel:

  • =CELL("adress";INDEX(A1:D5;PASSA(G2;A1:A5);PASSA(G1;A1:D1)))

Slutligen används följande formel för att erhålla nivån:

  • =LETARAD(CELLFARG(INDIREKT(G4));I2:J4;2;0)
Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin