Riskanalysmatris i Excel med INDEX och XMATCHNING

I följande exempel så skall vi skapa en riskanalysmatris. VI har en tabell där vi listar olika risker och bedömer dom utifrån Sannolikhet att risken infaller från Mycket hög till Mycket låg och Konsekvensen om den gör det ifrån Försumbar till Allvarlig. Både Sannolikheten och Konsekvensen graderas på en femgradig skala.

Du kan ladda ner exempelfilen mallen under inläggets rubrik.

Mallen ser ut som i bilden ovan där vi först har vår tabell till vänster där vi listar våra olika risker och bedömer riskerna efter Sannolikhet och Konsekvens med var sin dropdown lista. Baserat på de två värdena för Sannolikhet och Konsekvens så hämtar vi värdet på Risknivå ifrån vår riskanalysmatris till höger, se bilden nedan.

I kolumnen Risknivå i tabellen använder vi oss av en kombination av formlerna INDEX och XMATCHNING för att hämta värdet för Risknivå ifrån matrisen till höger om tabellen, se bilden nedan.

Formeln INDEX används för att hämta ett värde från en matris där man refererar till två tre argument

Matris: Den matris där våra värden finns i. Här refererar vi till matrisen ovan, endast cellerna med numeriska värden.

Rad: I vilken rad värdet finns i.

Kolumn: I vilken kolumn värdet finns i.

För att ta redan på vilken kolumn respektive rad värdet finns i så använder vi oss av formeln XMATCHNING. Den formeln söker efter ett värde i en vektor och returnerar dess position. Formeln XMATCHNING kräver minst två argument.

Letauppvärde: Det värde vars position vi skall leta upp

Letauppvektor: Den vektor, ett område som består av antingen en kolumn med flera rader eller flera kolumner fast på bara en rad.

Vi hämtar vårt letauppvärde i tabellen med olika risker, kolumn E eller F.

Vår letauppvektor finns i matrisen till höger. För rad är det Sannolikhet, K7:k11. För kolumn är det Konsekvens, L6:P6.

När vi kombinerar INDEX och XMATCHNING får vi formeln enligt nedan, där vi även sätter letauppvektorerna samt matrisen som absoluta värden för att kunna fylla tabellen nedåt:

=INDEX(

$L$7:$P$11;                                                                                 -> Matris

XMATCHNING(E4;$K$7:$K$11);                        -> Rad (Sannolikhet)

XMATCHNING(F4;$L$6:$P$6))                           -> Kolumn (Konsekvens)

I den grön-gula matrisen längst ner så räknas frekvensen ut av vad vilka risknivåer som förekommer mest, där absoluta referenser i dynamisk tabell används. Högst upp så räknas det genomsnittliga risknivån ut.