Villkorsstyrd summering
Funktionen SUMMA.OM kan vara mycket effektiv att använda i vissa sammanhang.
Utifrån ett villkor summeras cellinnehållen.
Givet att man förstår hur funktionen fungerar så kan man ha stor nytta av den. Nedan
demonstreras ett flertal tekniska lösningar:
Att sammanställa större datamängder är ett område där funktionen kan spara mycket tid. I exemplet nedan visas en dylik sammanställning.
Summering sker av värdena i "Belopp"-kolumnen utifrån veckotillhörighet. Villkoret
här är "Veckonr". Noterbart är att kolumnreferenserna, A :A och B:B, inte beaktar
textvärdena, dvs XL genererar inga felmeddelanden.
Vill man t ex summera olika intervall lämpar sig funktionen väl till det. I
nedanstående tabell visas ett exempel på detta.
Funktionen summerar de celler vars värden faller inom respektive intervall. Det
första argumentet utgör sökområdet medan det andra argumentet utgör själva
villkoret för att summering ska ske.
I nedanstående exempel ska belopp summeras per månad. Villkoret är här mer
komplext och det har tillkommit ett ytterligare argument - summaområdet.
Funktionen söker i området A2:A5, där villkoret är att summering ska ske om
datumet börjar på t ex 98xx. Summering sker för området B2:B5. Att använda
absoluta referenser underlättar kalkylarbetet. Cellreferens kan också ersättas med
namn, vilket förtydligar funktionen för andra.
Skulle datumen "krångla" så kan man även lösa det mha följande matrisformel:
-
{=SUMMA(OM(ÄRFEL(SÖK("9802**";A2:A5));0;B2:B5))}
Om man vill erhålla den totala summa för det mest frekventa värdet kan man lösa det på följande sätt:
-
SUMMA.OM(A2:A6;TYPVÄRDE(A2:A6))
Se också:
Istället för att bygga upp en större kalkyl med ett flertal formler mm så kan man m
h a villkorsstyrd summering 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
bestämt alternativ sker en viss beräkning, såsom "Ord" ger summan för de enheter
som ingår i "Ordergruppen". Här används sammanfogningstecknet, "&", i kombination med stjärntecknet, "*", för att hitta de enheter som matchar villkoret
"Ord". Ett alternativt beräkningssätt är att använda
VÄLJ-funktionen.
Istället för att använda sig av SUMMA.OM-funktionen kan man nyttja en av de
s k databasfunktionerna,
DSUMMA. Den är mer komplex men kan å andra sidan kan databasfunktionen hantera mer komplexa villkor. Se också
Intervallsummering.
Villkorsstyrd beräkning av antal
Funktionen ANTAL.OM kan spara in mycket arbetstid. Istället för att manuellt eller
via flera kalkylsteg räkna fram förekomsten kan man m h a denna funktion snabbt
få fram önskat resultat. Utifrån ett villkor räknas antal förekomster. I ovanstående
grundläggande uppställning kan SUMMA.OM ersättas av ANTAL.OM och man får antal förekomster. Dock kan inte ANTAL.OM-funktionen hantera separata
villkorsområden och målområden.
I nedanstående tabell visas ett exempel avseende statistik för
tidredovisning.
Funktionen söker i intervallet B2:B6 och där antal förekomster beräknas utifrån
villkoret, t ex "=Sj". Resultatet visas som heltal.
Händer det att du måste räkna förekomsten av t ex namn i en lista som också
innehåller tomma celler? M h a TECKNET-funktionen erhålls en "cool" lösning på
problemet - Se "Antal 1-exemplet" nedan!
Om en eller flera celler innehåller länkar kan antalet förekomster bli fel."Antal 2-exemplet" i tabellen ovan ger då en bättre lösning. "?*" i formeln innebär minst ett tecken.
Om man vill räkna antal tal mellan 25 och 75 blir den formeltekniska lösningen följande:
-
=ANTAL(A2:A5)-ANTAL.OM(A1:A5;"<25")-ANTAL.OM(A2:A5;">75") eller
-
i kortare uttryck: =ANTAL.OM(A2:A5;">=25")-ANTAL.OM(A2:A5;">75")
-
eller mha av en matrisformel, där värdena 25 och 75 återfinns i C1 respektive D1 :
{=ANTAL(OM((A2:A5>=C1)*(A1:A5<=D1);A1:A5))} eller -
=SUMMA(ANTAL.OM(A1:A5;{">=25";"<=75";"<>"})*{1;1;-1})
Antag att man vill erhålla antal förekomster av det mest frekventa värdet i en tabell. Det problemet kan lösas enligt följande:
-
=ANTAL.OM(A2:A6;TYPVÄRDE(A1:A4))
Matrisfunktionen FREKVENS beräknar också antal förekomster men är mer komplex att använda. Även här finns en databasfunktion att tillgå: DANTAL.
Flera villkor
Här demonstreras några tekniker där vi kan använda oss av flera villkor för funktionerna SUMMA.OM och ANTAL.OM
Bilden nedan visar exemplet i sin helhet:
Utifrån listan så ska XL beräkna antalet värden som uppfyller de två villkoren:
- Villkor 1 Tal = 3
- Villkor 2 Tal = 5
Vid den första beräkningen av antal används följande matrisformel:
- =SUMMA(ANTAL.OM(A2:A7;B2:C2))
Vid den andra beräkningen av antal används följande formel:
- =PRODUKTSUMMA(ANTAL.OM(A2:A7;B2:C2))
Vid den första beräkningen av summa antal tal som uppfyller villkoren används matrisformeln:
- =SUMMA(SUMMA.OM(A2:A7;B2:C2))
Vid den andra beräkningen av summa antal tal används följande formel:
- =PRODUKTSUMMA(SUMMA.OM(A2:A7;B2:C2))