Kalkylering VI

Kalkylering VI

Hantering av felvärden vid beräkningar

Att slippa felvärden till följd av att underliggande formler ger t ex #Division/0! och få t ex en summa beräknasd är många gånger önskvärt. I detta tips visas några exempel på hur felvärden kan hanteras.

Bilden nedan visar exemplet i sin helhet.

Följande formler används:

1:a =SUMMA(A2:A7)

2:a =SUMMA.OM(A2:A7;"<>#Division/0!")

3:e =SUMMA.OM(A2:A7;">0")

4:e {=SUMMA(OM(ÄRFEL(A2:A7);0;A2:A7))}

Den konkreta situationen får styra val av lösning.

Hitta rad- och kolumnetiketter

I detta tips demonstreras hur vi kan identifiera vilken rad- och kolumnetikett ett värde återfinns under. Lösningen kräver att uppslagsvärdena finns att tillgå i tabellen.

Om ett och samma värde förekommer flera gånger erhålls rad- och kolumnetikett för den sista positionen värdet förekommer i tabellen.

Bilden nedan visar exemplet i sin helhet:

För att få fram verksamhetsåret (radetiketten) används följande matrisformel:

{=INDEX(A1:A3;MAX((B1:D3=G1)*(RAD(B1:D3))))}

För att erhålla avdelning (kolumnetiketten) används följande matrisformel:

{=INDEX(A1:D1;MAX((B1:D3=G1)*(KOLUMN(B1:D3))))}

Se även följande tips:

I vilken kolumn?

Hitta ifyllda / tomma celladresser

Hitta sista raden & kolumnen

Rad & celladress med maxvärdet?

Autofilter och summera med villkor

Den inbyggda SUMMA.OM-funktionen går inte att applicera på data som filtreras mha Autofilter. Här demonstreras en lösning för att kringgå detta problem.
Exemplet visas i sin helhet i följande uppställning:

Följande formel används här:

=PRODUKTSUMMA((B3:B12>=E1)*DELSUMMA(3;FÖRSKJUTNING(A3;RAD(A3:A12)-RAD(A3);))*B3:B12)

Formeln möjliggör också att vi använder oss av flera villkor.

Maxresultat

Här visas en teknik för att utifrån ett namn erhållas personens högsta värde i en serie.
Exemplet i sin helhet visas i följande bild:

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

=MAX(INDEX((A2:A5=D1)*(B2:B5);0;1))

Radstyrd summering

I detta tips demonstreras hur vi kan styra vilka rader som ska summeras i t ex en lista. Det är två tekniker som demonstreras och där utfallet skiljer sig åt.
Exemplet utgår från följande bild:

I den första lösningen sker summering fr o m rad 2 t om rad 4 (3 rader) mha följande formel:

=SUMMA(INDIREKT("A2:A"&D1))

I den andra lösningen sker summering fr om rad 2 to m rad 5 (4 rader) mha följande formel:

=SUMMA(FÖRSKJUTNING(A2:A7;;;D1;))

Givetvis kan även andra beräkningar utföras efter samma principer som ovan.
Räkna antal rader och kolumner mellan två värden

Här visas hur vi på ett relativt enkelt sätt kan erhålla endera antalet rader mellan två värden i en lista eller antal kolumnen.
Följande bild visar exemplet i sin helhet:

För att erhålla antal rader används följande formel:

=ABS(PASSA(D1;A:A;0)-PASSA(D2;A:A;0))-1

För att erhålla antal kolumner mellan två värden används följande formel:

=ABS(PASSA(D1;7:7;0)-PASSA(D2;7:7;0))

Noterbart är att vi var två skilda synsätt på hur antalet ska erhållas mellan formlerna för antal rader respektive antal kolumner. Den faktiska situationen får styra vårt förfarande.
Multiplicera två listor

Ibland kan vi ha behov av att multiplicera två listor där den ena listan är uppställd vertikalt och den andra horisontellt.
Här demonstrerar en relativ enkel formelteknik för att lösa det. Bilden nedan visar exemplet i sin helhet:

Följande formel används i exemplet:

=PRODUKTSUMMA(A2:A5*D1:F1)

Summera var x:e rad över flera kolumner

I detta tips demonstreras en teknik för att summera var 5:e rad över flera kolumner. Exemplet visas i sin helhet i följande bild:

Formeln för att erhålla summan är:

=PRODUKTSUMMA((REST(RAD($A$2:$A$13)-CELL("Rad";$A$2:$A$13)+0;5)=0)*($A$2:$C$13))

(Där talet 5 anger radfrekvensen.)

Räkna antal förekomster i flera blad

Här visas hur vi mha formler kan räkna antal förekomster av värden inom flera intervall och i fler arbetsblad.
En förutsättning är dock att arbetsbladen har serienamn såsom Test1, Test2 osv.
Nedanstående bild visar ett exempel på detta:

För det första intervallet, 0 - 20, används följande formel:

=PRODUKTSUMMA(ANTAL.OM(INDIREKT("Test"&RAD($1:$3)&"!C5");"<="&C4))

För de övriga intervallen används nedanstående formel, vilken kopieras nedåt i kolumnen:

=PRODUKTSUMMA(ANTAL.OM(INDIREKT("Test"&RAD($1:$3)&"!C5");">="&B5)-ANTAL.OM(INDIREKT("Test"&RAD($1:$3)&"!C5");">"&C5))

Se också

Summer flera kalkylblad

Vänd på listan!

Exemplet belyser hur vi mha en formel kan vända på listor utan att de sorteras stigande eller fallande.
Bilden nedan visar exemplet i sin helhet:

Följande formel skapas i cellen B2 och kopieras nedåt i kolumnen:

=FÖRSKJUTNING($A$2;ANTAL(A2:$A$6)-1;;1)

Avancerad summering

I detta exempel utgår vi från mera komplexa förutsättningar än vanligt.

Utgångspunkten är att vi har värden i en rad och där vi ska:

Hitta celladressen för det första värdet som är större än 0 i raden

Med start i denna celladress addera värdet i cellen med de fyra efterkommande radcellernas värdena, dvs totalt ska 5 cellers värden summeras.

Slutligen ska summan divideras med 5.

Bilden nedan visar radlistan samt utfallet:

Följande matrisformel identifierar startcellens adress:

{=ADRESS(7;PASSA(SANT;A7:N7>0;0))}

Följande matrisformel identifieras slutcellens adress:

{=ADRESS(7;KOLUMN(INDIREKT(ADRESS(7;PASSA(SANT;A7:N7>0;0))))+4)}

Följande formel summeras ihop cellvärderna:

=SUMMA(INDIREKT(C2&":"&C3))

Avslutningsvis utförs divisionen med följande enkla formel:

=C4/5

Vill vi inte beräkna sluresultatet enligt den stegvisa modellen ovan kan vi imponera med följande matrisformel:

{=SUMMA(INDIREKT(ADRESS(7;PASSA(SANT;A7:N7>0;0))&":"&
ADRESS(7;KOLUMN(INDIREKT(ADRESS(7;PASSA(SANT;A7:N7>0;0))))+4)))/5}