Analyse mit MMULT()

[Rabe]

Hi,

ich habe diverse Listen:

1. Mitarbeiter
   
2. Arbeitsplätze
   
3. Gefährdungen
   
4. Maßnahmen
   

Nun will ich im Endeffekt eine Matrix haben, in der ersichtlich ist, für welchen Mitarbeiter welche Maßnahmen zu planen sind.

Dazu lege ich manuell (0 oder 1) auszufüllende 2-dimensionale Matrix (Matrizen) an:

1. eine, in der die Mitarbeiter den Arbeitsplätzen zugewiesen werden ("Zuordnung Mitarbeiter")
   
2. eine zweite Matrix, welche Arbeitsplätze welche Gefährdungen beinhalten ("Gefährdungszuordnung")
   
3. eine dritte Matrix, welche Gefährdungen welche Maßnahmen erfordern ("Maßnahmenzuordnung")
   

Dann verknüpfe ich in Matrix "Ergebnistabelle 1" mit MMULT() die "Gefährdungszuordnung" mit der "Maßnahmenzuordnung" und danach in Matrix "Ergebnistabelle 2" die Matrix "Zuordnung Mitarbeiter" mit der "Ergebnistabelle 1".
Jetzt kommt auch was raus!

Nun habe ich Fragen zu der MMULT()-Funktion:

1. ist das richtig, wenn ich so zwei Matrizen miteinander verknüpfe?
   
2. müssen immer gleich viele Zeilen und Spalten in der Ergebnismatrix stehen (z.B. 100 Arbeitsplätze und 100 Maßnahmen) oder kann eines davon auch weniger sein?
   
3. was ist überhaupt die maximale Zahl?
   
4. was ist mit den 5461 Zellen gemeint?
   

Hier mal eine Beispieldatei zum Ausprobieren:

  Gefährdungsanalyse.xlsb (Größe: 160,99 KB / Downloads: 3)

Kann diese Auswertung auch noch einfacher und resourcenschonender erzeugt werden als mit den ganzen Matrix-Formeln?

Bei Office-Lösung habe ich folgendes Makro gefunden, daß die MMULT() ersetzt, kann mir das die Auswertung auch lösen?

Code:

Public Sub ppp2()
 
   Dim Zeilen  As Long
   Dim i As Long
   Dim j As Long
   Dim matrix As Variant
   Dim myarray() As Double
   Dim b() As Variant
 
   'Zeilen = Sheets("Tabelle1").Range("A1").Value
   Zeilen = 10000

   matrix = Sheets("Tabelle2").Range("B45:O58")
   
   ReDim myarray(1 To Zeilen, 1 To 14)
   For i = 1 To UBound(myarray)
     For j = 1 To UBound(myarray, 2)
        myarray(i, j) = Val(i & j) 'Application.WorksheetFunction.NormInv(Rnd() + 0.0000000001, 0, 1)
     Next j
   Next i

   'b = Application.WorksheetFunction.MMult(myarray(), matrix)
   b = MMULT_X(myarray(), matrix)
 
   Sheets("Tabelle3").Range("A:N").Clear
   'Sheets("Tabelle3").UsedRange.Clear
   Sheets("Tabelle3").Range("A1").Resize(UBound(b), UBound(b, 2)) = b
 
End Sub

Function MMULT_X(ar1, ar2)
  Dim i As Long
  Dim k As Long
  Dim n As Long
 
  Dim arErg As Variant
  ReDim arErg(LBound(ar1) To UBound(ar1), LBound(ar2, 2) To UBound(ar2, 2))
 
 
  For i = LBound(arErg) To UBound(arErg)
     For k = LBound(arErg, 2) To UBound(arErg, 2)
        For n = LBound(ar1, 2) To UBound(ar1, 2)
           arErg(i, k) = arErg(i, k) + ar1(i, n) * ar2(n, k)
        Next
     Next
  Next
 
  MMULT_X = arErg
End Function

[lupo1]

Das Kind heißt MMULT()

Und sind die Daten gesund, freuen sich Pivot oder Power-Query.

[Glausius]

Hallo Ralf,

mein Excel sagt:

MMULT() gibt das Produkt von zwei Arrays zurück, ein Array mit der gleichen Anzahl von Zeilen wie Array1 und Spalten wie Array2.

daraus folgt für mich, dass die zu berechnendn Arrays unterschiedlich groß sein können.

[Rabe]

Hi Lupo,

Und sind die Daten gesund, freuen sich Pivot oder Power-Query.

wie könnte ich das denn mit Pivot oder Power Query einfacher lösen?
Mir fällt gerade nicht ein, wie ich aus 4 Tabellen dann die Matrix Ergebnistabelle 2 herausbekomme.

Vielleicht schaut sich auch Günther die Datei nochmal an für die Power Query Lösung?

  Gefährdungsanalyse V2.1.xlsb (Größe: 162,41 KB / Downloads: 2)

[Rabe]

Hi,
hier mal eine Erklärung:
ich habe die Datei von meinem Chef übernommen, mit der Bitte:
"ich will in der Ergebnistabelle 2 einen Button haben, der mir alle Maßnahmen des selektierten Mitarbeiters in ein Formblatt füllt und dann ablegt, kannst Du das mal machen?"

Die Dummy-Zeilen und Spalten stehen nur drin, weil der Chef hörte, für MMULT() benötigt man gleich viel Spalten wie Zeilen und weil er es für größere Zahlen erweiterbar haben wollte.
Die Umwandlung in intelligente Tabellen habe ich gemacht. Damit ich schneller springen konnte und schraffierte Zeilen habe und weil ich inzwischen bei Listen immer mit intelligenten Tabellen arbeite.

Alle Zuordnungen (MA, AP, Gefahr, Maßnahme) sind in Handarbeit (mit 1 oder 0) gemacht.
Es sollen später nicht nur Gefahren, sondern möglicherweise auch Themen/Projekte/Aufgaben, Arbeitsplatzausstattung usw. enthalten sein.

Die "Ergebnistabelle 1" ist nur die Vorstufe/Vorbereitung für die Ergebnistabelle 2, die kann auch wegfallen.

Wenn es eine andere Lösung gibt, wie die Maßnahmen den Mitarbeitern zugeordnet werden können, bin ich dafür offen. Ich fürchte, die Blätter außer den Ergebnistabellen benötigen wir schon?

[shift-del]

Hallo Ralf

Hier mal ein Ansatz mit PQ.
Aufgrund der Daten ist die zweite Ergebnistabelle leer.

[Rabe]

Hi Detlef,

ich habe durch Eingabe von ein paar mehr Daten auch in Ergebnis 2 nun Daten drin.

Was ist aber, wenn ein Mitarbeiter nicht nur einen Arbeitsplatz hat, oder ein Arbeitsplatz mehrere Gefährdungen? Dann wird es doch unübersichtlich in den Ergebnistabellen?
Kann das nach rechts in weiteren Spalten eingegeben oder angezeigt werden?

Wie kann ich aus der Ergebnis 2-Liste am Ende dann die Vorlage (in der Beispielmappe ausgeblendet) füllen, in der für den einzelnen Kollegen seine Maßnahmen aufgelistet werden?

So mache ich es seither:

Option Explicit

Sub MitarbeiterBlatt_anlegen()
  ' 
  ' Protokoll_anlegen Makro 
  ' 
  'Definition der Variablen und Konstanten im mdl_Variablen 
  ' 
  'Konstanten 
  loMatrixStart = 2
  loMatrixEnde = 103
  loMaßnahmeStart = 12
  ' 
  
  With Application
     .ScreenUpdating = False
     .EnableEvents = False
  End With
  
  If ActiveCell.Column <> 1 Then Exit Sub
  strName = ActiveCell.Value
  
  Sheets(strVorlage).Visible = True
  Sheets(strVorlage).Select
  Sheets(strVorlage).Copy Before:=Sheets(11)
  
  Range("B4").Value = strName
  loZeile = Application.Match(strName, Sheets(strErgebnis).Columns(1), 0)
  
  Range("B4").Value = Range("B4").Value & ", " & Sheets(strMitarbeiter).Range("B" & loZeile).Value
  ActiveSheet.Name = Range("B4").Value
  Range("F4").Value = Sheets(strMitarbeiter).Range("C" & loZeile).Value
  Range("B6").Value = Sheets(strMitarbeiter).Range("D" & loZeile).Value
  
  'Sheets("Vorlage").Visible = False 
  
  'Nun werden die Maßnahmen (Spaltenbezeichnungen aus Zeile 3) ohne Lücken in das Mitarbeiter-Blatt eingetragen! 
  With Sheets(strErgebnis)
     'loLetzte = .Cells(Rostr.Count, 1).End(xlUp).Row           ' letzte belegte in Spalte A (1) 
     j = loMaßnahmeStart
     For i = loMatrixStart To loMatrixEnde
        If .Cells(loZeile, i).Value > 0 Then
           ActiveSheet.Range("B" & j) = .Cells(3, i)
           j = j + 1
        End If
     Next i
  End With
  
  Call Daten_Export
  
  With Application
     .ScreenUpdating = True
     .EnableEvents = True
  End With
  
End Sub

[shift-del]

Was ist aber, wenn ein Mitarbeiter nicht nur einen Arbeitsplatz hat, oder ein Arbeitsplatz mehrere Gefährdungen? Dann wird es doch unübersichtlich in den Ergebnistabellen?
Kann das nach rechts in weiteren Spalten eingegeben oder angezeigt werden?

Inwiefern wird das unübersichtlich?
Die Ergebnislisten sind klassische Datensatzlisten. Können gefiltert werden. Können sortiert werden. Können pivotiert werden.
Mit einer Pivot-Tabelle kommst du vermutlich deiner Vorstellung näher. Aber ich würde keine Kreuztabelle daraus machen (Too much whitespace; Platzverschwendung) sondern die Felder in den Zeilenbereich ziehen.