Ladda ner exempelfilen här: Beräkningar med villkor.xlsx

Beräkningar med villkor

Det kan finnas behov att göra beräkningar om vissa villkor är uppfyllda.

I detta exempel ska utfall, budget- och prognosvärden fyllas i och beräknas per månad och även för hela året. Budgetvärdena är lika för alla månaderna.

Allt eftersom utfallsvärdena fylls i ska prognosvärdena för varje månad och totalerna uppdateras. Alla utfallsvärden ska också summeras.

Problemet är att celler med budgetvärden endast ska summeras om cellerna på raden ovanför (där utfallet per månad fylls i) är tomma.

Summera hela rader

Att summera hela raden med utfallsvärden på rad 3 och prognosvärdena på rad 5 i kolumn N är enkelt. Det går att göra genom att:

• Markera cellerna.
• Klicka sedan på knappen Autosumma (AutoSum) på menyfiken Start [Home].

Men vi vill inte behöva tömma cellerna på rad 4 allteftersom utfallsvärdena fylls i på rad 3 för att kalkylen ska räkna fram rätt total på budgetvärdena.

Excel kan i stället kontrollera om cellerna är tomma åt oss!

Funktionen ÄRTOM

Vi börjar med att skapa formeln för att hämta rätt prognosvärden för varje månad.

Om cellen med utfallet är ifylld så ska värdet på rad 3 visas på rad 5. I annat fall så ska budgetvärdet hämtas från rad 4 för den aktuella månaden.

Vi använder funktionen OM [IF] för att hämta rätt värde och funktionen ÄRTOM [ISBLANK] för att testa om något är ifyllt eller inte i cellerna på rad 3.

Formeln vi ska använda ser ut så här:

=OM(villkor;värde-om-sant;värde-om-falskt)

Formeln blir:

=OM(ÄRTOM(B3);B4;B3)

=IF(ISBLANK(B3);B4;B3)

Först kommer formeln testa om innehållet i cellen B3 är tomt eller inte. Om det är sant hämtas budgetvärde i cellen B4, men om det är falskt hämtas utfallsvärdet i cellen B3.

När formeln för första cellen är klar går det att kopiera den till övriga månader.

Funktionen PRODUKTSUMMA

I stället för att använda den vanliga funktionen SUMMA [SUM] för att beräkna totalen för budgetvärdena i cellen N4 ska vi utnyttja funktionen PRODUKTSUMMA [SUMPRODUCT].

Varför?

Därför att det med funktionen PRODUKTSUMMA [SUMPRODUCT] går att lägga till villkor för vilka celler som ska summeras.

Textsträng

Genom att använda funktionen ÄRTOM [ISBLANK] går det att kontrollera om celler är tomma eller inte.

Det går att testa om en cell och även om flera celler är tomma. Om ett område med celler anges i funktionen ÄRTOM [ISBLANK] skapas det en textsträng som innehåller orden SANT [TRUE] eller FALSKT [FALSE] för varje ingående cell.

=ÄRTOM(B3:M3)

= ISBLANK(B3:M3)

De celler i det markerade området som inte är tomma får värdet FALSKT [FALSE] och tomma celler får värdet SANT [TRUE] i textsträngen. Så resultatet blir:

FALSKT\FALSKT\FALSKT\SANT\SANT\SANT\SANT\SANT\SANT\SANT\SANT\SANT

FALSE\FALSE\FALSE\TRUE\TRUE\TRUE\TRUE\TRUE\TRUE\TRUE\TRUE\TRUE

Ordet FALSKT [FALSE] representerar matematiskt talet 0 och SANT [TRUE] är lika med talet 1.

Så textsträngen:

FALSKT\FALSKT\FALSKT\SANT\SANT\SANT\SANT\SANT\SANT\SANT\SANT\SANT

kan översättas till siffrorna:

0\0\0\1\1\1\1\1\1\1\1\1

som Excel kan använda i beräkningar.

Denna textsträng går att använda som villkor för att ange vilka celler som ska summeras eller inte.

PRODUKTSUMMA med villkor

Genom att multiplicera textsträngen med FALSKT/SANT [FALSE/TRUE] för utfallsvärdena (som representerar 0 och 1) med cellområdet med budgetvärden så kommer endast de celler som matchas med värdet SANT [TRUE] = 1 ingå i summan.

Eftersom allt som multipliceras med 0 blir 0.

Så formeln som bara kommer att summera de orangea cellerna i bilden ovan blir:

=PRODUKTSUMMA(B4:M4*ÄRTOM(B3:M3))

=SUMPRODUCT(B4:M4*ISBLANK(B3:M3))

Allteftersom fler utfallsvärden fylls i kommer textsträngen med FALSKT/SANT [FALSE/TRUE] ändras och rätt budget celler summeras.

Nu behöver inte budgetvärden tas bort för hand, utan Excel kan hitta de celler som ska summeras åt oss.