Risiko in Risikomatrix ermitteln

[Excelnfaenger]

Hallo allerseits,

Im Anhang ist eine Tabelle mit einer Risikomatrix.
  Risikomatrix_mit_Formeln.xlsx (Größe: 10,7 KB / Downloads: 16)
 
Folgende Parameter werden festgelegt, um das Risiko zu ermitteln.
In den Zellen M7 bis Q11 werden die Parameter festgelegt und das das Risiko R (Spalte R) ermittelt.
Parameter 1: In Spalte N die Schwere des Schadens (S in Zelle N6 als Kopfspalte) festgelegt.
Parameter 2: In Spalte O die Aufenthaltsdauer im Gefahrenbereich (A in Zelle O6 als Kopfspalte) festgelegt.
Parameter 3: In Spalte P die Eintrittswahrscheinlichkeit (E in Zelle P6 als Kopfspalte) festgelegt.
Parameter 4: In Spalte Q die Möglichkeit zur Vermeidung oder Begrenzung des Schadens (M in Zelle Q6 als Kopfspalte) festgelegt.
 
Die Risikomatrix funktioniert wie folgt.
- Erster zu ermittelnder Faktor ist S (Schwere des Schadens). Zu suchen in Spalte B zwischen den Zellen B6 und B16.
- Für den zweiten Faktor geht es waagerecht rüber in Spalte C und es wird der Wert für Aufenthaltsdauer A gesucht. Dieser liegt zwischen 1 und 3. Nur wenn du Schwere des Schadens 1 ist, dann kann dieser Schritt übersprungen werden.
- Der dritte Faktor ist die Eintrittswahrscheinlichkeit E, in Zeile 2 zu suchen.
- Der vierte Faktor: Je nachdem welcher Wert für E ermittelt wurde geht es in der entsprechenden Spalte eine Zeile nach unten (Zeile 3). Dort wird der Faktor M (Möglichkeit zur Vermeidung oder Begrenzung des Schadens) gesucht. Dieser kann nur 1 oder 2 sein.

Aus diesen 4 ermittelten Werten kann dann in der Risikomatrix der Wert R ermittelt werden.

Um es besser zu verstehen, ein paar Beispiele:
#1 Gefährdung Nr1 (M7), S = 3 , A = 2 , E = 3 , M = 1 ergibt ein Risiko von 8 laut Risikomatrix.
#2 Gefährdung Nr2 (M8), S = 2 , A = 2 , E = 4 , M = 2 ergibt ein Risiko von 10 laut Risikomatrix.
#3 Gefährdung Nr3 (M9), S = 1 , A = entfällt , E = 3 , M = 2 ergibt ein Risiko von 6 laut Risikomatrix.
#4 Gefährdung Nr3 (M9), S = 4 , A = 2 , E = 3 , M = 2 ergibt ein Risiko von 14 laut Risikomatrix.

Kurzfassung:
Meine Eingaben stehen in den Zellen M7:Q11
R7 bis R11 sind Ergebnisse, die durch Formeln ermittelt werden sollen. Ich habe hier die Werte aus der Tabelle herausgelesen, aber ich will, daß dies durch eine Formel gelöst wird.
Die eigentliche Risikomatrix steht links daneben in den Zellen B6 bis K18.

Mein Lösungsansatz
Zuerst wollte ich S und E finden. Da habe ich in den Zellen M17 und O17 getan. Nun komme ich nicht weiter, weil ich in Abhängigkeit von S bzw. E den nächsten Wert (A und M) ermitteln muß. Wie kann ich A und M ermitteln?

Oder gibt es eine allgemeingültige Formel ohne diese Hilfsschritte, um aus den 4 Faktoren mit Hilfe der Risikomatrix das Risiko zu ermitteln?

[BigJane]

Hi,

es geht sicher mit noch komplexerer Formel aber um es möglichst einfach und verständlich zu halten würde ich eine Hilfsspalte und -zeile einfügen.

Dafür macht es auch Sinn Felder möglichst gleich zu halten, daher der "-" Eintrag in C7

Bei allen anderen Formellösungen sollte man den Tabellenaufbau überdenken, Leerfelder die gedanklich einen Wert von oben drüber haben oder verbundene Zellen erschweren Formellösungen unnötig.

Anbei ein Beispiel. 

VG Jane

[neopa]

Hallo,

eine Möglichkeit ohne Hilfsspalte wäre z.B. folgende Formel in R7 und kopiere diese nach unten: 

PHP-Code:

=INDEX(A:K;VERWEIS(9;1/(B$6:B$18=N7);ZEILE(B$6:B$18))+O7-(O7>0);VERWEIS(9;1/(D$2:K$2=P7);SPALTE(D2:K2))+Q7-1) 


um bei unvollständigen Parametern keine fehlerhaften Ergebnisse ausgegeben werden, klammere die Formel mit =WENN(M6=";"";[Formel]) und überprüfe in Spalte M die Parameter auf ihre Vollständigkeit. 

Die Formel setzt voraus, dass das "-" in O9 gelöscht wird.  Wenn da aber ein "-" stehen soll, dann formatiere O7:O## mit dem benutzerdefinierten Zahlenformat "0;;"-"  und schreibe anstelle des "-" in O9 eine 0 ein.

[Andreas Killer]

Machen wir es ganz einfach:

Das "S" und das "E" muss gesucht werden, "A" und "M" sind regelmäßige Unterbereiche die man einfach dazu addieren kann, da die Zählung im Unterbereich mit 1 beginnt muss man -1 rechnen damit die Position passt.

R7:  =INDEX($D$6:$K$18;VERGLEICH(N7;$B$6:$B$18;0)+O7-1;VERGLEICH(P7;$D$2:$K$2;0)+Q7-1)
runterziehen.

A darf Werte von 1 bis 3 haben, M Werte von 1 bis 2.

Damit bei S=1 der korrekte Wert rauskommt muss O9  1 sein, ein "-" ist nicht erlaubt. Kann man jedoch auch "verformeln" wenn man will.

R7:  =INDEX($D$6:$K$18;VERGLEICH(N7;$B$6:$B$18;0)+WENN(N7=1;0;O7-1);VERGLEICH(P7;$D$2:$K$2;0)+Q7-1)

Alles klar?

Andreas.

[BoskoBiati]

Hi,

mein Vorschlag wäre, die Tabelle etwas umzubauen und mit einer INDEX-Formel  die Werte zu ermitteln. Das Ergebnis in Deiner Nr. 3 ist wohl falsch.


  Risikomatrix_mit_Formeln.xlsx (Größe: 11,51 KB / Downloads: 3)

[neopa]

Hallo Edgar,

also wenn schon die Quelldaten "umbauen",  dann würde ich dieses konsequent vornehmen. D.h. so, daß diese im Layout dem der Vorlage weitestgehend entspricht. und die Formelauswertung vereinfacht wird. 

Allerdings beim "umbauen" ist  zu beachten, dass in den verbunden Zellen die Datenwerte in allen Zellen wie in allen verbunden Zellen enthalten sind.
So ist gewährleistet, dass die Auswertungsformeln korrekt ermitteln.

  Risikomatrix_mit_Formeln_pa.xlsx (Größe: 12,13 KB / Downloads: 8)

[Sulprobil]

Hallo,

Mein Vorschlag (ohne jegliche Gewährleistung):

   


  CEF_Risikomatrix_mit_Formeln.xlsx (Größe: 12,44 KB / Downloads: 7)

Viele Grüße und macht es gut,
Sulprobil

[snb]

Ohne Formeln, doch mit dynamische Tabelle und Autofilter

[slowboarder]

Hi

verwende diese Formel in R8.

=INDEX($D$6:$K$18;VERGLEICH(N7;$B$6:$B$18;0)+WENN(N7=1;0;O7-1);(P7-1)*2+Q7)

sie funktioniert mit deiner Datei ohne dass du an der Tabelle was ändern musst.
Gruß Daniel