Office: (Office 2016) Matrixfomel - kein Zugriff auf gespeicherte Daten

Hallo Zusammen,

ich habe beim Programmieren einer Matrixformel das Problem, dass ich nicht mehr auf die Inhalte der Berechnung zugreifen kann.

Folgendes Szenario (Die Matrixformel besteht aus mehreren Schritten):

1. Abfrage ob Daten (hier eine Spieler ID Einzelspieler) in einem aktuellen Spiel (4 Spieler mit je einer eigenen ID Team) vorkommen:

{=PRODUKT(INDEX(WENN($CB$6:$CE$6=$DB$6:$DB$33;0;1);$CY$6:$CY$33))}

WENN : Abfrage ob der gefragte Spieler im 4er Team parallel im Einzelspiel vorkommt
--> bei ja = 0 --> darf nicht spielen
--> bei nein = 1 --> darf spielen

da eine jeweils das 4er Team überprüft wird resultiert hieraus eine Matrix X:4 (x ist abhängig von der Anzahl der Teams)

Danach wird mit Index und Produkt jede Zeile multipliziert, um zu schauen, ob der Spieler nicht Teil des gesamten Teams (4er Team) ist.

Bsp. Aktuelles Teamspiel (Spieler) = 4,5,6,7 --> Kontrolle ob Spieler 5 nicht spielt --> Ergebnis: 1, 0, 1, 1 --> dies multipliziert = 0 --> Spieler 5 spielt also in dem aktuellen Teamspiel.

In der nächsten Zeile erfolgt die Abfrage für das nächste Teamspiel. Ziel ist herauszufinden, welches Einzelspiel gestartet werden kann.

--> Ergebnis ist ein Array mit einer Spalte (0 oder 1) welche besagt ob der Spieler spielen darf oder nicht.

Bis hierhin konnte ich die Formel erstellen. Wenn ich jetzt versuche die Zeile herauszufinden, bei der die 1 vorkommt, geht was schief:

2. Herausfinden der "1"

=VERGLEICH(1;PRODUKT(INDEX(WENN($CB$6:$CE$6=$DB$6:$DB$33;0;1);$CY$6:$CY$33));0)

hier scheint die Funktion Vergleich nicht alle Ergebnisse von oben mit einzubeziehen.
Es wird jedes mal nur ein Wert verglichen.

Somit ist das Ergebnis natürlich #NV, da die Formel fehlerhaft ist:
- laut Excel: VERGLEICH(1;0;0) --> #NV
- erwartet: VERGLEICH(1;(0;0;0;0;1;1;1;0;1... je nach Anzahl der möglichen Spiele);0)

Wie bekomme ich hier Excel dazu alle Werte des Arrays zu betrachten und nicht zeilenweise? Ergbeniss soll die Zeile einer 1 sein (kann eine beliebige sein).

Hab mit kgrößer, vergleich, index und summe herumprobiert und somit darauf gestoßen, dass nie das ganze Array betratet wird.

Könnt ihr mir hierbei vill helfen? Zerbreche mir schon seit Tagen den Kopf darüber...

Besten Dank
Jan

 

Nein. Es ist ein Einzelwert und kein Array.

Da es kein Array gibt scheitert VERGLEICH().

 

Moin Detlef,

danke für dein Antwort.

Dann muss der Fehler weiter vorne liegen, denn wenn ich die Formel 1 ausführe, füllte diese 28 Zellen einer Spalte mit den richtigen Werten... Werden diese dann einzelnd berechnet und gar nicht gespeichert?

Könnet man diesen dann in einer Formel in den Vergleich bekommen?

Will umgehen, dass ich über 200 Spalten füllen muss.... deswegen alles in einer Formel

Besten Dank
Jan

 

Hier ein Array: =NACHZEILE(ZEILE(6:33);LAMBDA(x;PRODUKT(WENN({2.6.7.9}=x;x))))

Kannst Du ja in Deinem Sinne abwandeln, wenn XL365/Web. Habe nicht verstanden, was Du möchtest.

 

Das würde mich wundern.
Allerdings ist es schwierig dir zu helfen weil weder die Daten noch das Wunschergebnis bekannt sind.

 

Moin lupo1, moin Detlef,

so ich habe versucht das Ganze einmal etwas verständlicher darzustellen.
Anbei die verkürzte Datei mit der Frage. Ziel ist die Komprimierung der Formel auf eine Zelle, damit diese für jedes 2 vs 2 direkt ausgeführt werden kann.

Besten Dank für eure Hilfe!

VG Jan

 

Das Problem habe ich nicht verstanden, aber ich habe versucht das Wunschergebnis zu erzielen.

Code:

=VERGLEICH(WAHR;MMULT(--($B$6:$E$6<>$J$6:$J$33);ZEILE(1:4)^0)=4;0)

 

Moin Detlef,

holy.... so kurz und tut was es soll. Ich versuche die mir mal zu erklären :D
Funktioniert auf jeden Fall top.

Besten Dank !!!

Viele Grüße
Jan

 

Hallo Jan,

nachgefragt. Warum beginnt Deine "Zählung" in A6 mit 0? Nach Deiner Ergebniszielvorgabe zu urteilen, sollte diese mE doch mit 1 beginnen, oder?

Hier noch eine Alternative zur Formel von Detlef für Deine "Zählweise" in Spalte A

in Zeile6:
=AGGREGAT(15;6;$A$7:$A$215/(MMULT((J$6:J$215<>$B6:$E6)+0;{1;1;1;1})=4);1)

und nach unten und rechts kopierbar. Sollte jedoch die "Game ID" gesucht werden, dann in der Formel die 7 durch 6 ersetzen. Aber verständlich ist mir das, was da für was ermittelt wird, auch nicht.

 

Hallo shift-del und neopa, hier etwas zu MMULT: W-Regel (Excel)

Man muss immer davon ausgehen, dass ein Fragender tatsächlich mal auf die Idee kommt, seine 100.000 Datensätze ohne gesonderten Hinweis auf 100 Beispieldaten zu verkleinern, so, wie wir es ihm immer nahelegen. Dann müssen wir aber auch davon ausgehen, dass er die ihm gebotene Lösung auch auf seine fetten Ursprungsdaten anwenden möchte. Dort kacken MMULT (und AGGREGAT) ab.

Grenzen und Performance von dynamischen Formeln behandelt neben dem Thema des Threads auch inhaltlich ein Vermeiden von (fast) jeglichem unnötigen Datenvergleich. Es beschränkt sich auf das, was Excel am schnellsten kann: Zugreifen (INDEX) und Sortieren. Damit komme ich linear auf ca. 1 Sekunde pro 100.000 Datensätzen. Einen Nachteil hat dies auch: Es wird Speicherbedarf gegen Zeitbedarf getauscht. Beides gering halten tut nur VBA.

Hoffentlich lesen wir bald keine AGGREGAT-Lösungen mehr. Bernd Plumhoff sagt außerdem: Excel-Tabellen mit AGGREGAT können nicht nach den Richtlinien von Prüfungsorganisationen geprüft werden, wenn sie Fehler unterdrücken.

 

Hallo lupo1,

zunächst zu:

Dies sehe ich prinzipiell nicht so. Kann man tun, muss es aber als Helfer in Foren nicht. Anders wäre es, wenn man eine bezahlte Dienstleistung erbringt/erbringen soll oder will. In der Pflicht ist diesbzgl. immer zunächst der Fragesteller.

Ich setze Formeln bei Auswertungen von unter 1.000 Datensätzen (meist sind es eh höchstens 100) ein und dies auch nur dann, wenn ich keine Pivotauswertung vornehmen kann oder eine solche mir nicht gerade einfällt bzw. diese nicht gewünscht ist. Bei mehr auszuwertenden Datensätzen (wird nach meinen Erfahrungen von über 90% der Nachfragen in Foren auch nicht benötigt) halte ich ich mich entweder aus Antworten außen vor, nutze bzw. verweise auf PQ. Ich bezweifele nicht, dass VBA auch bei der Auswertung von weniger Datensätzen die effektivere Lösung sein kann (aber auch nicht muss). Ich interessiere mich jedoch nicht für VBA-Lösungen und muss es auch nicht.

Ich bin auch kein Theoretiker sondern eher Praktiker. Als solcher käme ich nie auf die Idee derartige Formel-"Überlegungen" wie sie in W-Regel (Excel) ab der 2. Formel in Betracht gezogen wurden. Da ich auch nur Excel 2010 und XL2016 im Einsatz habe, kann ich außerdem die letzten 3 Formeln noch nicht mal ohne Fehlermeldung einsetzen (auch wenn ich verstehe, was damit in etwa wie bewirkt werden soll). Die Nichtmatrixformel die ich neben der 1. dort aufgezeigten eingesetzt hätte, wäre einfach =SUMME(A$1:A1) gewesen, die nach unten kopiert wird.

Zu Deinen Untersuchungen hier: Grenzen und Performance von dynamischen Formeln kann ich mich sowieso nicht äußern, da ich wie bereits geschrieben bis dato nur XL2016 einsetze.

Und nun noch zu:

Ich kenne keine derartigen Richtlinien. Wo sind diese festgehalten? Möglicherweise gelten solche ja für professionelle Excel-Dienstleistungen, weiß ich aber nicht. Als mehr Hobby-Excelianer muss ich diese mE aber zumindest nicht zwingend kennen. Entscheidend für mich ist, wie offensichtlich für viele andere Helfer und vor allem Fragesteller auch, dass eine Lösung gemäß vorliegenden Angaben des/der Fragesteller erstellt werden kann. Dies ist mit der in XL2010 eingeführten Funktion AGGREGAT() sehr wohl in sehr vielen Fällen gut (und mE auch einfach) möglich. Zudem muss zumindest ich für die Definition derartigen Formeln mit dieser Funktion im Vergleich zur gleichwertigen "klassischen Matrixformeln" mit anderen Funktionen meist weniger Zeitaufwand investieren. Außerdem werten nach meinen bisherigen Erfahrungen AGGREGAT()-Formeln zumindest oft schneller aus als "klassische Matrixformeln".

Etwas verwunderlich ist auch, dass immer die AGGREGAT()-Funktion (dessen leider gegebene Unvollkommenheit ich übrigens in deutschsprachigen Foren - im damaligen Online Excel - zumindest mit als einer der Ersten bemängelt hatte, aber für dessen restlichen gegebenen Nutzwert ich trotzdem bin) regelrecht verpönt wird. Warum dann nicht auch Funktionen wie z.B. VERWEIS(), ANZAHL() ... ? Letztgenannte Funktionen unterdrücken intern auch Fehler und dies wird dementsprechend so auch in mancher Formeln genutzt.

 

Vertiefe Dich vielleicht doch noch einmal in die Artikel von Bernd.

In Grenzen und Performance von dynamischen Formeln habe ich ja geschrieben, dass ein linear steigender Zeitbedarf (statt eines exponenziellen) für wachsende Datenmengen nur ohne breitflächige Verwendung von AGGREGAT, ZÄHLENWENN und vielen VERGLEICH/VERWEISen erzielt werden kann. Also über SORTIEREN. Oder VBA. Oder Pivot.

Ich war selbst WP-Assistent. Das ist lange her (Umbruch von Papier zum PC in unserer Prüfungsarbeit); daher weiß ich nicht, wo das heute geschrieben steht. Aber prüfbar ist AGGREGAT so tatsächlich nicht. Ich würde die Daten des Mandanten also stets mit meinem eigenen Ansatz prüfen, also den separat erstellen (müssen). Misslich ist es, wenn es beim Mandanten um mehr, als nur Statistik geht - und auch schon dort, weil Statistiken Grundlage für Entscheidungen sind. Da der Wirtschaftsprüfer in seinem Bericht letztlich auch eine Einschätzung für die Kreditwürdigkeit abgeben muss oder zumindest mit Einwilligung des Mandanten von der Bank danach gefragt werden könnte, kann er nach Treu und Glauben nur antworten: "Die Firma trifft Entscheidungen via fehlerträchtige Excelstatistiken ohne Gegenprüfungen auf anderem Wege, so dass wir hier keine ungefährdete Kreditwürdigkeit sehen." Tut er das nicht, schreibt er seinem Mandanten einen Blankoscheck zulasten seiner eigenen Berufshaftung (und -ethik) aus.

Bernd verweist zu Recht auf Apologeten wie Charles Williams, der die Verwendung von Hilfszellen predigt.

Was Du (und auch ich und andere) hier in den Foren veranstalten, ist letztlich selbst beweihräuchernde Gehirnakrobatik, wofür uns niemand bestraft. Besser wären einfachere Formeln mit Zwischenschritten.

Du sagt, Du kämst schon von Dir aus nie auf Lösungen ab der 2. Formel in dem Link. Sinngemäß tun das aber alle Deine AGGREGATe. Und sowas wie ZÄHLENWENN(A$1:A1;A1) herunterkopiert hast Du sicherlich auch schon mal. Das ist alles das Gleiche.

Und: Auch Du musst manchmal außerhalb des CD-Archivierers oder Kleintierzüchters oder Fotoalben-Organisierers antworten.

 

Hallo lupo1,

zu:

Geprüft werden muss bzw. sollte jedes Ergebnis, welches mit Hilfsmitteln egal welcher Art auch immer erstellt wurden ist. Wie das geschieht ist die Angelegenheit dessen, der ein Ergebnis ermittelt bzw. welcher es zu prüfen hat. Prüfbar ist jedenfalls alles.

Ich gehöre zu einer Generation die in den ersten Jahren des Berufslebens nicht gerade einfache Rechnungen noch mit Hilfe des Rechenschiebers aufgestellt hat. Natürlich hab ich da auch immer eine Plausibilitätsprüfung der Ergebniswerte vorgenommen. Solches habe ich beibehalten, als der Taschenrechner aufkam und später dann auch ich in PC-Programme nutzen konnte. Natürlich kann ich mich sowohl mit meinen Ermittlungen mit Hilfe von Excelfunktionalitäten und mit meinen Plausibilitätsprüfungen vertun. Ich bin nicht fehlerfrei.

Aber wenn Du schreibst "ist nicht prüfbar", dann werden mE nicht geeignet Prüfmethoden eingesetzt. Für mich immer noch in erster Linie entscheidend ist, ob ein Ergebnis korrekt ist oder falsch. Wenn sie falsch sind, wurden/werden ungeeignete oder fehlerhafte Ermittlungsmethoden eingesetzt. Wenn also mit AGGREGAT() falsche Ergebnisse ermittelt werden sollten, dann zeige sie auf. Dann hab ich oder ein anderer der vielen User die AGGREGAT() einsetzen, mal ein fehlerhafte Definition vorgenommen und würde es dann entsprechend abändern Alles andere interessiert mich zunächst nur in zweiter Linie.

 

AGGREGAT ist nur ein besonders schlimmes Beispiel, aufgrund der Fehlerunterdrückbarkeit. Es gibt von Fragern hochgeladene Dateien, deren Formel-Analyse so zeitaufwändig wäre, dass man sie besser durch einen Neuaufbau anhand der Daten plausibilisiert. Für mich sind sie außerdem oft pures "Augenfeuer", da immer wieder Daten mit Ausgaben gemischt sind. Ich breche dann regelmäßig nach 10 bis 60 Sekunden den Beantwortungsversuch ab.

Gerade bei abgeleiteten Kennziffern kann man "erstellt durch Excel" so ohne weiteres nicht trauen, wenn man nicht selbst hinter dem Formelwerk steckt. Die Finanzverwaltung erlaubt aus anderem Grund (den jeder nachvollziehen kann) ja auch keine Fahrtenbücher in Excel. Eine bloße Summe kann man natürlich relativ schnell verproben und damit prüfen.

 

Hallo lupo1,

Deine Ausführungen begründen sich also auf der Meinung einer deutschen Behörde, die nach Deinen Aussagen überspitzt gesagt am liebsten nur Datenwerte vorgelegt bekommen möchte, deren Ergebnisse durch Summierung zu prüfen sind. Wenn in der Wirtschaft so gearbeitet bzw. gedacht würde, hätte die Behörde weniger zu prüfen/verwalten.

Persönlich kann ich mich übrigens an keine Problemstellung erinnern, wo ich AGGREGAT() eingesetzt hätte, die für die Finanzwirtschaft und -verwaltung von Relevanz wäre.

Außerdem stellt sich zumindest mir nach Deinen Aussagen die Frage, wie denn VBA- oder PQ-Lösungen oder Formellösungen mit Einsatz von u.a. LN() ... oder gar den neuen leistungsfähigen Funktionen LAMBDA() und ....zu bewerten sind.