Microsoft Excel

Herbers Excel/VBA-Archiv

Matrixformel

Betrifft: Matrixformel von: Florian
Geschrieben am: 16.04.2014 14:23:05

Hallo,

hier meine Excel-Datei: https://www.herber.de/bbs/user/90207.xlsx

Die beigefügten Excel-Datei enthält 2 Arbeitsblätter!
Im ersten Arbeitsblatt "Schichten prüfen" brauche ich in den gelb markierten Zellen vermutlich eine Matrixformel, die folgenden Wert ermittelt:

Aus dem zweiten Arbeitsplatt "Query-Kostenverteilung" soll passend zur Personalnummer aus Spalte 1 und des Datums aus Spalte 3 des ersten Tabellenblatts die "Stamm-Kostenst." aus dem zweiten Arbeitsblatt der Spalte 5 ausgewertet werden, die innerhalb des Zeitraums zwischen Spalte 6 und 7 liegt.

So und jetzt wird's kompliziert:
Falls in Spalte 8 eine abweichende Kst. zu Spalte 5 vorliegt, dann ist diese am jeweiligen Tag (Spalte 9 und 10) vorrangig auszugeben.

Weil das so kompliziert ist und ich das nicht mit Hardcopys erklären kann, hier die Schritte, wie ich die Werte manuell (Spalte 6, erstes Arbeitsplatz) ermittelt habe (Bsp. für Zeile 10/Spalte 6 im ersten Arbeitsplatz):

1. Die Kostenstelle der Personalnummer 56461 am 03.04.2014 wird gesucht
2. Im zweiten Arbeitsblatt bei Personalnummer 56461 muss im grünen Block der Zeitraum gesucht werden, der das Datum 03.04.2014 enthält. Das ist der vorletzte Datensatz mit Stamm-Kostenstelle 2318!
3. Zuletzt muss geprüft werden, ob es im roten Block (Spalten 8 - 10) zum Datum 03.04.2014 eine abweichende Kostenstelle gibt. Dies ist hier der Fall mit (IT24-Kostenstelle) 6420!

Ergebnis: 6420


Es muss auch nicht unbedingt alles in einer Formel sein.
Das Ergebnis kann auch über mehrere Schritte ermittelt werden.
Ich habe mit Matrixformeln schon etwas gearbeitet, komme aber selber auf keinen grünen Zweig.

Bitte um eure Hilfe und Ideen.

LG
Florian

  

Betrifft: AW: Matrixformel von: Peter Müller
Geschrieben am: 16.04.2014 15:02:01

Hallo Florian,

vielleicht hilft dir das weiter:

http://excelformeln.de/formeln.html?welcher=30

Ich lasse die Frage trotzdem offen.

Gruß,
Peter


  

Betrifft: AW: Matrixformel von: Philipp
Geschrieben am: 16.04.2014 15:05:03

Hallo Florian,

die Formel ist nen bissl wild und je nach Daten könnte sie evtl. auch Schwierigkeiten haben, aber schau einfach mal, ob es so passt.

https://www.herber.de/bbs/user/90211.xlsx

Kurze Erklärung:
Da die Kostenstellen Zahlen sind, kann man die gut summieren, was ich hier mache und dann einen Durchschnitt bilde.
Sollte eine Kombination mehrfach vorkommen mit unterschiedlichen Kostenstellen, würde das einen falschen Durchschnitt ergeben. Aber andererseits würde nichts ein richtiges Ergebnis geben.

Wenn es zu der Kombination im roten Bereich keine Kostenstelle gibt, gibt er die Kostenstelle aus dem grünen Bereich aus.

Gruß

Philipp


  

Betrifft: AW: Matrixformel von: Florian
Geschrieben am: 16.04.2014 16:12:13

Hallo Philipp,

vielen Dank für deine Hilfe.
Ich habe die die Formel getestet und bei den Testdaten stimmen die Werte alle!
Ich hatte bei der manuellen Ermittlung einen Fehler, aber hier ist das richtige Ergebnis: https://www.herber.de/bbs/user/90212.xlsx

Dennoch brauche ich eine Formel, auf die ich mich 100% verlassen kann, da es um sehr große Datenmengen und Kosten geht!

Gibt es wirklich keine zuverlässigere Lösung?
Hast du mir vielleicht eine Konstellation, bei der es nicht funktioniert?
Ich dachte ursprünglich an eine Art SVERWEIS in einer Matrixformel oder ähnliches!

Hat jemand noch andere Ideen?

Gruß
Florian


  

Betrifft: AW: Matrixformel von: Philipp
Geschrieben am: 16.04.2014 21:00:50

Hallo Florian,

es kommt ganz auf deine Daten an. Wenn die so aufgebaut sind wie dein Beispiel, passt es.
Ansonsten kann ich da nicht für garantieren, da ich halt nicht weiß was da noch für Daten sein könnten.

Gruß

Philipp


 

Beiträge aus den Excel-Beispielen zum Thema "Matrixformel"