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

 
  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.

Flexibel kalkylering
Istället för att bygga upp en större kalkyl med ett flertal formler mm så kan man m h a VÄLJ-funktionen få en flexibilitet som ger det underlag man just för stunden behöver. I tabellen nedan visas ett exempel. Genom att välja ett på förhand alternativ sker en viss uträkning, såsom alternativ 2 ger medelvärdet för datamängden.

Ett alternativt sätt att erhålla samma utfall återfinns under Villkorsstyrd summering.

Räkna antal tomma / ej tomma celler
Har man större kalkyler och man vill få reda på förekomsten av t ex tomma/ej tomma celler eller med viss innehåll kan man ha nytta av funktionen ANTAL. I tabellen nedan visas exempel på det.

Funktionerna DELSUMMA, DSUMMA och DANTAL kan vara en bättre lösning vid tabellberäkningar.

Beräkna dynamiska dataserier
Import av data innebär vanligtvis att mängden data varierar från gång till annan, dvs det är dynamisk data som importeras. Det finns skilda tekniker för att hantera datamängdens variation. I tabellen nedan visas ett förfaringssätt.

Definiera ett namn först, t ex "Cellsumma". Ange formeln enligt exemplet nedan för fältet "Refererar till" i dialogrutan. Applicera namnet i önskad formel och plats.

Formeln summerar numeriska värden med start från rad 1 i önskad kolumn. Den byggs upp med att först identifiera startcellen för intervallet som ska summeras. KOLUMN-funktionen ger värdet 1 i exemplet, dvs kolumn 1. ADRESS- funktionen genererar adressen $A$1. Slutcellen erhålls genom att formeln först identifierar antal rader (RAD-funktionen och inklusive rad för formeln!) reducerat med 1. KOLUMN-funktionen genererar värdet 1 och utifrån dessa uppgifter skapar ADRESS-funktionen i exemplet adressen $A$9. INDIREKT-funktionen hämtar värdena från intervallet A1 t o m A4.

En annan teknik är att använda sig av funktionerna FÖRSKJUTNING och ANTALV. Med samma antagande som ovan kan du ange följande formel i t ex cellen B2 i kalkylbladet.

  • =SUMMA(FÖRSKJUTNING($A$2;0;0;ANTALV($A:$A);1))

Denna formel kan också gömmas bakom ett namn, t ex Cellsumma. Se också Dynamiska diagramserier.

En ytterligare teknik är att använda sig av funktionerna INDIREKT och ANTAL. Med samma antaganden som ovan kan du ange följande formel i t ex cellen B2 i kalkylbladet.

  • =SUMMA(INDIREKT("A1:A"&ANTAL($A:$A)))

Denna formel utgår från att man endast har numeriska värden i kolumnen och att det inte förekommer tomma celler.

Förekommer det tomma rader kan man försöka lösa det med följande matrisformel:

  • {=SUMMA(INDIREKT("A1:A"&MAX(RAD($A$1:$A$10)*
    ($A$1:$A$10<>""))))}

Konvertera text till tal
Händer det att du importerar ekonomisk data från någon system till XL? Händer det då att du får värdena som text och med intäkts-tecknet bakom vissa tal ( - ) ? Om ja, så har du här lösningar på problemet!

Mha av ett flertal textfunktioner kan en lösning erhållas. Först sker en utvärdering huruvida cellvärdet är en intäkt eller inte. Om ja, så flyttas tecknet "-" till framför talet och därefter konverteras texten till tal. Annars konverteras texten direkt till ett tal.

Alternativ kan följande formel användas:

  • =OM(HÖGER(A1;1)="-";"-"&VÄNSTER(A1;LÄNGD(A1)-1);A1)*1

Om det förekommer blanksteg i den importerade textmassan kan det vara nödvändigt att rensa textvärdena innan konvertering. Det kan göras mha av RENSA-funktionen enligt följande:

  • =TEXTNUM(OM(HÖGER(RENSA(A1);1)="-";"-"&VÄNSTER
    (RENSA(A1);LÄNGD(RENSA(A1))-1);RENSA(A1)))

Räkna med ekvationer!
M h a problemlösaren kan man faktiskt lösa ekvationer i XL. Lösningen förutsätter att du har installerat tillägget problemlösaren. Anta att du vill lösa följande ekvation: 2x2 + 3x2 = 200. Följande steg krävs för att lösa ekvationen:

  • Skriv in följande formel i t ex B1=2*A1^2+3*A1^2
    (A1 utgör här den s k justerbara cellen).
     
  • Välj kommandot Verktyg / Problemlösare. I dialogrutan anges cellen B1 som målcell. Markera alternativet "Värdet av" och ange 200. Ange cellen A1 som justerbar cell och klicka därefter på knappen Lös - klart!

Räkna antal i diskontinuerliga markeringar
Har du behov att räkna antal celler som överstiger ett visst värde i en diskontinuerlig markering? Om ja, så får du här den tekniska lösningen på problemet - I exemplet nedan utgör värdet 4 villkoret, dvs antal celler i markeringen som har värden överstigande "4" ska summeras.

  • =INDEX(FREKVENS((C3;A5;B7);4);2)

Summera flera kalkylblad
Här presenteras några tekniker för att summera enstaka celler och områden i ett flertal kalkylblad. En viktigt förutsättning är att kalkylbladen "hänger" samman när det gäller namn på bladen, t ex 1,2,3 osv.

I tabellen nedan visas summering av enstaka celler i underliggande kalkylblad m h a en matrisformel. Namnen på underliggande blad är numeriska.

Formeln hämtar uppgifterna från cellerna A2 och A3, dvs värdena utgör
kalkylbladsintervallet som ska ingå i summeringen och där cellen A1 är det område som faktiskt ska summeras i varje blad.

För summering av enstaka celler men där namnen består av både alfanumeriska (text) som numeriska (tal) värden blir matrisformeln litet annorlunda - se exemplet nedan.

Men om vi vill summera cellområden och med villkor istället för enstaka celler, hur gör man då? Hm, även det går att lösa men m h a en annan matrisformel:

  • {=SUMMA(SUMMA.OM(FÖRSKJUTNING
    (INDIREKT("Vecka"&{1|2}&"!A1:a10");;);">1000"))}

Här sker en summering av områden, där värdena ska överstiga 1000.

Tips: Har du omfattande arbetsböcker och där summering ska ske för t ex kvartal och halvår m m, så använd s k rotationsrutor och koppla dessa till start- respektive slutcellen. Därmed erhålls god flexibilitet!

Hitta det närmaste värdet
Händer det att du försöker hitta det värde i en tabell som ligger närmast ett annan värde? Mha av en matrisformel kan man erhålla en lösning på detta lilla problem.

I exemplet nedan demonstreras formeln. ABS-funktionen ger de absoluta talen i listan och som reduceras med 50. Därmed erhålls differensen mellan 50 och de förekommande värdena i listan. MIN-funktionen ger den minsta differensen, i exemplet 8. Slutligen ger PASSA-funktionen den radposition som innehåller den minsta differensen, i exemplet är det rad 2. INDEX-funktionen ger värdet för den cell som återfinns i rad 2, dvs värdet i cellen A3.

Viktigt är att vara medveten om att formeln ger det första närmaste värde i en lista. Konkret innebär det att om t ex cellen A6 skulle innehålla värdet 42 så är differensen lika stor som i cellen A3, eller hur? Formeln visar då fortfarande 58 då cell A3 är den första cellen i listan vars värde är det närmaste värdet.

Två andra lösningar, vilka också är matrisformler, är:

  • {=MAX(OM(MIN(ABS(A2:A7-B2))=ABS(A2:A7-B2);A2:A7;""))}
     
  • {=FÖRSKJUTNING(A2;PASSA(MIN(ABS(B1-A2:A7));ABS
    (B1-A2:A7);0)-1;0)}

Medelvärde
Att låta XL räkna ut medelvärdet för en datamängd är i sig inte svårt men antag att du vill beräkna medelvärdet exklusive de lägsta/högsta värdena - hur erhåller man det?

Jo, i XL:s verktygslåda hittar vi funktionen TRIMMEDEL.

Antag att du ska beräkna medelvärdet per månad för hur många plockorders som verkställes per dag men i medelvärdet ska inte de 5 % lägsta respektive högsta värdena ingå i.

  • =TRIMMEDEL(Område;0,05)

Högsta/Lägsta notering
I vissa sammanhang kan det vara önskvärt att hålla reda på högsta/lägsta noteringen. Hur kan man få XL att hålla reda på detta?

Steg 1:
Välj kommandot Verktyg/Alternativ och under fliken "Beräkningar" bockas alternativet "Iterationer" i.

Därmed överlåter vi till XL att själv fastställa vilket värde av två som är högst respektive lägst.

Steg 2:
Antag att du i cell A2 har den dagliga noteringen och som följaktligen ändras kontinuerligt. Antag vidare att du anger formeln (se nedan) i cellen A4.

  • =MAX(A2;A4)

Rent tekniskt skapas här en cirkelreferens då formeln refererar till sig själv (A4) men XL genererar här inga felmeddelanden då iterationer är förvalt.

Vill man erhålla den lägsta noteringen ersätts MAX-funktionen av MIN- funktionen.

Stegvis värdeökning
Här presenteras en formelteknisk lösning på problemet att skapa intervall med stegvis värdeökning.

Ponera att du vill starta på värdet 10 och sluta med 20 där stegökningen är 2 mellan talen, dvs 10, 12 14 osv.

Bilden nedan visar förutsättningarna och utfallet.

För att erhålla denna lösning anges följande formel i cell A2 och kopieras till dess att texten "Utanför" visas.

  • =OM((Startvärde+(RAD()-RAD($A$2))*Stegvärde)
    <=Slutvärde;Startvärde+(RAD()-RAD($A$2))*Stegvärde;"Utanför")

Löpande summeringar
I det första exemplet demonstreras hur vi löpande summerar de senaste 12 månaderna.

Resultatet för de första 4 månaderna ser ut enligt tabellen nedan:

Formeln som hanterar såväl fler än 12 angivna värden som mindre än 12 är:

  • =SUMMA(FÖRSKJUTNING(B2;0;ANTALV(2:2)-2;1;MAX
    (-12;-ANTAL(2:2))))

Antag istället att det redan finns minst 12 värden inmatade och att data som ska summeras återfinns i en kolumn istället för rad. Givet detta scenario kan följande formel användas:

  • =SUMMA(FÖRSKJUTNING(B1;ANTALV(B:B)-1;0;-12;1))

Ett annat sätt att lösa löpande beräkning presenteras här i nästa exempel.

Antag att du i en lista kontinuerligt vill veta summan av de senaste 5 talen. Om du lägger till tal så ska det beaktas och om du drar ifrån tal ska formeln också ta hänsyn till det.

Formeln i cellen B2 i exemplet ovan utgörs av:

  • =SUMMA(INDIREKT(ADRESS(MAX(1;ANTAL(A:A)-4);1)&":"&ADRESS
    (ANTAL(A:A);1)))

Värdet 4 styr antal celler som ska ingå i beräkningsunderlaget, här innebär det de 5 senaste talen. En ökning av värdet innebär att fler celler ska ingå i underlaget.

Kolumnangivelsen A:A kan ersättas av ett mer specifikt angivande av cellområde, t ex A1:A500.

Vill man erhålla medeltalet för de senaste 5 inmatade värdena i listan ersätts SUMMA-funktionen med MEDEL-funktionen enligt följande:

  • =MEDEL(INDIREKT(ADRESS(MAX(1;ANTAL(A:A)-4);1)&":"&ADRESS
    (ANTAL(A:A);1)))

I vilken kolumn?
Här demonstreras en teknik för att identifiera max- och minimivärdet i en av flera kolumner.

Bilden nedan visar exemplet i sin helhet:

För att erhålla kolumnnumret som håller maxvärdet i används följande formel:

  • =KOLUMN(FÖRSKJUTNING(B2;0;PASSA(MIN(B2:E2);B2:E2;0)-1))

Kolumnnumret som erhålls är utifrån A-kolumnen. Här blir resultatet kolumn 4.

För att erhålla numret för den kolumn som håller den minsta värdet används följande formel:

  • =KOLUMN(FÖRSKJUTNING(B2;0;PASSA(MAX(B2:E2);B2:E2;0)-1))

Vill vi veta namnet på kolumnen som håller maxvärdet används följande formel:

  • =VÄNSTER(ADRESS(4;KOLUMN(B2)-1+PASSA(MIN(B2:E2)
    ;B2:E2;0);2);HITTA("$";ADRESS(4;KOLUMN(B2)-1+PASSA
    (MIN(B2:E2);B2:E2;0);2))-1)

För minsta värdet kan följande formel användas:

  • =VÄNSTER(ADRESS(4;KOLUMN(B2)-1+PASSA
    (MAX(B2:E2);B2:E2;0);2);HITTA("$";ADRESS(4;KOLUMN(B2)-1
    +PASSA(MAX(B2:E2);B2:E2;0);2))-1)

Har vi egna kolumnnamn, som i exemplet, så kan vi identifiera t ex vilken kolumn som håller det minsta värdet:

  • =INDEX(B1:E1;PASSA(MIN(B2:E2);B2:E2;0))

Vill vi erhålla kolumnnamnet för maxvärdet ersätts MIN-funktionen i ovanstående formel med MAX-funktionen.

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