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

 
  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.

Skapa intervallserier
Här demonstreras en teknik för t ex vid uppbyggande av mallar mm skapa intervallserier.

Antag att vi ska skapa en serie om 1-4 och där denna serie ska förekomma flera gånger efter varandra. Det kan lösa på flera sätt men här visas hur vi enkelt kan lösa det mha av en formel.

Exemplet i sin helhet:

Formeln skapas i första cellen och kopieras därefter nedåt i kolumnen:

  • =OM(REST(RAD();4)=0;4;REST(RAD();4))

Delbart med x och y
I detta tips belyses hur vi kan styra vilket tal som ska visas i en resultatcell. Utgångspunkten är att XL ska visa:

De tal som matas in i inmatningscellen som är delbart med 4 och 6, som returnerar ett heltal. Anges t ex talet 20 så är det delbart med 4 (=5) och ska följaktligen visas.

Om inte delbart med dessa tal ska XL visa det närmaste delbara talet, uppåt eller nedåt i talordningen. T ex 7 så ska XL returnera 8.

Följande formel löser problemet ifråga:

  • =MIN(A2+REST(4-REST(A2;4);4);A2+REST(6-REST(A2;6);6))

Summa x rader eller kolumner
När vi ska summera rader eller kolumner och där inte varje rad/kolumn ska räknas in så kanske vi bygger upp t e x =SUMMA(A1;A3;A5.....) eller också =A1+A3+5.....Har vi dessutom några hundra rader/kolumner så tar det tid.

Här visas hur vi, relativt enkelt, kan skapa formler vilka summerar rader/kolumner i ett visst intervall.

Exemplet i sin helhet visas i följande bild:

För att summera var 2:a kolumn i rad 1 används följande formel:

  • =PRODUKTSUMMA((REST(KOLUMN(A1:F1);2)=
    REST(KOLUMN(A1);2))*A1:F1)

För att summera var 3:e rad i kolumn A används följande formel:

  • =PRODUKTSUMMA((REST(RAD(A1:A10);3)=
    REST(RAD(A1);3))*A1:A10)

Delsummeringar i flera steg
I detta tips demonstreras hur vi med hjälp av XL:s inbyggda verktyg för s k delsummeringar kan skapa ett flertal avancerade beräknad sammanställningar. Verktyget lämpar sig i synnerhet väl för stora datamängder.

Utgångspunkten för exemplet är följande lista:

I det första steget vill vi att summering ska ske per "Avdelning" och kommandot Data | Delsummering ger oss följande dialogrutan, där vi gör våra val:

Resultatet av denna instruktion visas i bilden nedan. Noterbart är också att vi dessutom har fått tillgång till ett ytterligare kraftig verktyg till vänster i bilden - Dispositionsverktyget.

Utifrån de beräkningarna som skapas kan vi också med dispositionsverktyget skapa olika vyer av materialet.

I nästa steg ska vi instruera XL att skapa ytterligare en beräkning och med bibehållande av tidigare beräkningar - Summering per Ordernr. Dialogrutan nedan visar de valda inställningarna.

Resultatet av instruktionen visas i följande bild:

Följande sammanfattar verktyget Delsummering:

  • Det kan utföras i flera steg för en och samma lista.
     
  • Utöver summeringar kan ett flertal andra beräkningsalternativ utföras.
     
  • Genom att vi får också tillgång till Dispositionsverktyget kan vi snabbt komprimera och dekomprimera information.

Räkna med sökord
I detta tips demonstreras en rättfram och flexibel teknik för att göra sammanställningar av listor utifrån sökbegrepp.

Sökorden kan bestå av hela ord eller delar av ett ord och den funktion som möjliggör det är SÖK-funktionen. När vi använder oss av denna funktion måste vi vara medvetna om att den är skifteskänslig, dvs att den gör åtskillnad mellan "A" och "a".

Bilden nedan visar exemplet i sin helhet:

För att få reda på antal kunder som uppfyller sökkriterierna används följande formel:

  • =PRODUKTSUMMA(ICKE(ÄRFEL(SÖK($F$2;$A$2:$A$6)))*
    ICKE(ÄRFEL(SÖK($G$2;$B$2:$B$6))))

För att erhålla den totala budgetsumman för dessa kunder används följande formel:

  • =PRODUKTSUMMA((ICKE(ÄRFEL(SÖK($F$2;$A$2:$A$6)))*
    ICKE(ÄRFEL(SÖK($G$2;$B$2:$B$6)))*C2:C6))

Om ett eller båda sökkriterierna inte finns i listan returneras värdet 0.

Rad med största / minsta värdet
Här visas hur vi relativt enkelt kan erhålla information om vilken rad som håller det största alternativt minsta värdet i en lista.

Bilden nedan visas exemplet i sin helhet:

För att erhålla radnumret med det största värdet används formeln:

  • =PASSA(MAX(A2:A8);A2:A8;0)+RAD(A2)-1

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

  • =PASSA(MIN(A2:A8);A2:A8;0)+RAD(A2)-1

Har vi serien med värden i en rad och vill identifiera kolumnen som håller det största respektive det minsta värdet används följande formler:

  • =PASSA(MAX(B11:H11);B11:H11;0)+KOLUMN(B11)-1
     
  • =PASSA(MIN(B11:H11);B11:H11;0)+KOLUMN(B11)-1

Intervallberäkningar
Här visas några alternativ för att beräkna intervall. Istället för att beräkna mellan värden så ska vi här beräkna nedre och övre "intervallen", mao utanför den traditionella intervallen.

Exemplet visas i sin helhet i följande bild:

Alternativ 1 har följande underliggande formler:

  • Antal: =ANTAL.OM(A2:A10;"<"&B2)+ANTAL.OM(A2:A10;">"&C2)
     
  • Summa: =SUMMA.OM(A2:A10;"<"&B2)+SUMMA.OM(A2:A10;">"&C2)
     
  • Medel: =E3/E2

Alternativ 2 har följande underliggande formler:

  • Antal: =PRODUKTSUMMA(N(A2:A10<B2)+N(A2:A10>C2))
     
  • Summa: =PRODUKTSUMMA((A2:A10<B2)*A2:A10)+
    PRODUKTSUMMA((A2:A10>C2)*A2:A10)
     
  • Medel: =F3/F2

Alternativ 3 har följande underliggande formler:

  • Antal: =PRODUKTSUMMA(ANTAL.OM(A2:A10;{"<200";">500"}))
     
  • Summa: =PRODUKTSUMMA(SUMMA.OM(A2:A10;{"<200";">500"}))
     
  • Medel: =G3/G2

Poängberäkning med faktor
Antag att vi gör en resultatsammanställning. I den anges poäng för varje deltagare och första gången deltagarna får poäng ska poängen reduceras med en känd faktor (även det omvända förhållandet kan gälla).

Problemet är att poängreduceringen endast ska ske första gången därefter räknas den faktiska poängen.

Bilden nedan visar exemplet och utfallet för problemet ifråga:

I den första cellen behövs ingen hänsyn tas varför följande formel kan användas:

  • =OM(ÄRTOM(A2);"";B2*$D$2)

I nästa cell (C3) skapas lösningsformeln, vilken därefter kopieras nedåt i kolumnen:

  • =OM(OCH(ÄRTOM(A3)=FALSKT;PRODUKTSUMMA(N(A3=$A$2:A2))
    =0);B3*$D$2;"")

För att i nästa cell få följande cellområden att beakta:

  • =OM(OCH(ÄRTOM(A4)=FALSKT;PRODUKTSUMMA(N(A4=$A$2:A3))
    =0);B4*$D$2;"")

Städa bort skräptecken
Att hämta data från ett annat program är vanligt förekommande, inte minst från stordatorsystem. Ett problem i detta sammanhang är att med datat följer det oftast med s k skräptecken, dvs icke-utskrivningsbara tecken.

Problemet uppstår när vi ska använda oss av datat såsom vid beräkningar o d.

Exemplet visas i sin helhet i följande uppställning:

Cellen A2 har ett skräptecken (skapat här med ALT+129) samt talet vi vill använda oss av.

I den första lösningen används funktionen RENSA och då denna funktion endast kan hantera blanksteg så kvarstår skräptecknet:

  • =RENSA(A2)

I den andra lösningen används funktionen STÄDA och den tar bort skräptecken på ett effektivt sätt:

  • =STÄDA(A2)

Den bästa lösningen är att kombinera dessa två funktioner samt omvandla värdet till ett numeriskt tal genom multiplikation med 1:

  • =RENSA(STÄDA(A2))*1

Löpande beräkningar
I detta tips visas några tekniker för att utföra beräkningar på data som successivt utökas.

Utgångspunkten för exemplet är att den sist inmatade posten inte ska räknas med utan endast de tidigare. När ny post läggs till så utökas beräkningsområdet till att innefatta även den föregående postens data.

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

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

  • =SUMMA(B2:B100;-INDEX(B2:B100;ANTAL(B2:B100)))

För att erhålla det största värdet ersätts SUMMA med MAX.

Denna grundformel modifieras för att beräkna medel:

  • =MEDEL(B2:B100;INDEX(B2:B100;ANTAL(B2:B100)))

För att erhålla det minsta värdet ersätts MEDEL med MIN.

I den andra lösningen används följande formel för att summera:

  • =SUMMA(FÖRSKJUTNING(B2;-1;0;ANTAL(B:B);1))

För beräkning av övriga mått ersätts SUMMA-funktionen med respektive funktionsnamn. Denna formel är den mest generella av de redovisade i det att antal poster som inte ska medräknas är enkelt att ändra på.

I den tredje lösningen används följande formel för att summera:

  • =SUMMA(INDIREKT("B2:B"&ANTAL(B2:B100)))

För beräkning av övriga mått ersätts SUMMA-funktionen med respektive funktionsnamn.

Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin