Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1204to1208
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Tabellenblätter einer Matrix zuordnen

Tabellenblätter einer Matrix zuordnen
Maris
Hallo Leute,
ich suche eine Formel mit der ich ein ziemlich diffizieles Problem lösen kann. Ich möchte wie inder Beispielmappe die angehängt habe Tabellenblättern Kategorien zuordnen und aufsummieren in der Kennzahl1 Kennzahl2 und Kennzahl3 immer davon abhänig ob die Kategorien zusammengehören. Das habe ich durch die Spalten Tabname 1,2,3,4,5 zusammengefasst.
Also die Tabellenblätter gehören zusammen und sollen nach 2 Kriterien Week und Kennzahl aufsummiert werden. Ich hoffe die Erklärung ist nicht zu abgehoben, deswegen das Beispiel angehängt.
https://www.herber.de/bbs/user/74168.xls
Ja ie ist fast berechtigt, so diffizil ist dein...
29.03.2011 02:39:58
Luc:-?
…Problem bzw eher grenzwertig, Maris,
denn für eine elegante Lösung muss man schon recht gute xlKenntnisse haben, weshalb dein VBA-Level hier gar nichts nutzt, denn du hast ja auch nicht danach verlangt… ;->
Im Prinzip wäre eine Datenbereitstellungsfkt über mehrere, variabel anzugebende Blätter erforderl. So etwas bietet Xl aber normalerweise nicht, denn mit Tensoren (auch Vektoren u. Matrizen über mehrere Blätter betrachtet Xl so!) hat Xl meist nichts am Hut. Es gibt nur wenige xlFktt, die damit umgehen können (bspw SUMME). Hier hängt die Summe aber von mehreren Bedingungen ab, und da dürfte es auch für excelformeln eng wdn (jedenfalls ohne Hilfszellen). Ansonsten bieten sich dafür natürl spezielle udFktt an (→ habe ich auch schon geschrieben), habe mich hier aber für eine Kombi aus benannter Fml auf der Basis der XLM-Fkt AUSWERTEN und Verbundzellen aus 6 Einzelzellen, die in der Kopfzeile alle den Text haben, der auch in der sichtbaren Zelle zu sehen ist (wahrscheinl würde Text in den jeweils 1.beiden reichen!). In den Folgezeilen steht in der sichtbaren Zelle jeweils die Summenfml, während die verborgenen Zellen eine Matrixfml enthalten, die quasi nur aus dem Namen der benannten Fml besteht. Ist alles in der hier zu findenden Datei dokumentiert. Unten finden sich noch Muster für die Formatierung dieser Zellen, denn das kann nur mit dem Formatpinsel erfolgen.
Übrigens hättest du ruhig Blatt A-D einstellen können, damit auch mal ein Ergebnis zu sehen ist und nicht nur F-Werte!
Das schien mir hier jedenfalls eine annehmbar einfache und relativ elegante Lösung zu sein. Eine echte Alternative könnte neben VBA wohl höchstens noch DBSUMME bieten.
Viel Erfolg beim Nachvollziehen! ;-)
Gruß Luc :-?
Anzeige
AW: Ja ie ist fast berechtigt, so diffizil ist dein...
29.03.2011 09:59:51
Maris
WOW!!! Also deine Lösung bringt mich über meinen Horizont des Verständnisse hinaus (noch im Verständnisprozess ;-)). Echt wahnsinn! Eine Frage dennoch... macht es was aus wenn die Namen der Tabellenblätter Leerzeichen enthalten... Also statt "A" "Autos und Motorräder", weil wenn ich deine Lösung aus meiner Beispieltabelle übertrage, habe komischerweise immer Routen vorneweg stehen.
Gruß,
Maris
...Eigentl nicht, denn die dann erforderl...
29.03.2011 13:22:18
Luc:-?
…Apostrophs („Hochkommas”) sind ja wohl in die Fml integriert, Maris,
und deine „Routen” (wohl Pfade?!) dürften da gar nicht erscheinen, weil das ja auf einer xlinternen Ebene gemacht wird (Ausnahme: direkt angesprochene Blattnamen, allerdings ohne Pfade bei gleicher Mappe — ist in benannten Fmln so).
Vermute daher etwas Anderes. Nehme an, die übertragenen Fmln verweisen immer noch auf die BspMappe. Das kannst du durch Direkteintrag in die Zelle/Arbeitszeile (nicht ganze Zellen aufkopieren!) vermeiden.
Allerdings dürfte deine Kategorie-Bildung mit Autos und Motorräder usw statt Anicht mehr so elegant aussehen und vor allem mehr Platz benötigen. Evtl könnte da eine extra „Übersetzungstabelle” helfen. Ob nun in der auszuwertenden Fml # direkt durch A aus D2 oder durch einen weiteren Verweis auf eine Liste aus Kategorie-Symbol und -Bezeichnung in der Art …WECHSELN("…";"#";SVERWEIS($D2:$H2;'KatListe'!$A$2:$B$27;2;0))… ersetzt wird, ist letztlich schnurz.
Übrigens ist das 1.Argument von WECHSELN, also die eigentl Fml, hier Text, der erst von AUSWERTEN als Fml interpretiert und dann entsprechend ausgewertet wird. Durch den vorherigen Austausch des Platzhalters # mit WECHSELN gg die Werte einer Matrix aus den maximal mögl Kategoriezellen wdn alle von den Kategorien bezeichneten Blätter einzeln nach der Textfml ausgewertet. Dadurch wdn aber auch genausoviel Ergebniszellen benötigt, sonst fktt das nicht. Ob diese Hilfszellen nun irgendwo oder in einer Verbundzelle versteckt wdn, ist eigentl auch egal. Finde aber, dass das so eleganter ist, weil die Summanden gleich „unter” der Summenfml liegen. Die Matrixfml für die Summanden ist auch denkbar einfach, so dass deren Sichtbarkeit absolut unnötig ist. Die HptArbeit leistet ohnehin die mit KatVektor benannte Fml. Alles andere dient nur Darstellung und Zusammenfassung, könnte aber für (uneingeweihte) Kollegen/Nachfolger/Chef-Nachkontroll-Freaks zum Problem wdn, weil die kaum erkennen wdn, wo die Daten für die Summen herkommen… ;-)
Ach so, noch was! Schreibe die Echtnamen der Blätter ganz normal ohne ' oder ", das ist alles schon in der Fml. Gilt auch für alle anderen in der HptFml verwendeten VglsTexte, denn schon da musste berücksichtigt wdn, ob eine Zelle Text oder Zahl enthält. Die Adressen der zu vgld Bedingungswerte (Suchbegriffe) gehen nämlich nicht in die Textfml-Auswertung ein, sondern nur der Wert, der da steht. Sonst wäre keine automatische Adressanpassung möglich und es würden stets dieselben Werte geliefert. Deshalb können auf diese Weise auch nur Einzelzell-, keine Zellbereichsadressen verwendet wdn, es sei denn, man kann das Ergebnis zu einem Listentext in Matrixkonstanten-Form zusammenfassen. Aber das geht in Xl nur mühselig, weil man keine Zellbereiche in sich VERKETTEN kann, gut aber mit VBA.
Viel Erfolg! Gruß Luc :-?
Anzeige
AW: ...Eigentl nicht, denn die dann erforderl...
29.03.2011 15:25:29
Maris
Hi Luc,
ich habe nicht Routen sondern Rauten gemeint ;-) Diese verschwinden wenn ich die Arbeitsmappe einmal öffne und dann wieder schließe... also kein Problem mehr... komisch ist nur das er nur bei Kennzahl3 mir jetzt den Wert #NV ausgibt... Versuche gerade herauszufinden an was das liegt... solangsam versteh ich deine Funktion einigermaßen... aber das NV ergibt jetzt für mich keinen Sinn.
Gruß,
Maris
Aha! Lag wohl am anfängl Zeitbedarf,...
29.03.2011 15:53:45
Luc:-?
…Maris,
benannte Fmln wdn stets zuerst berechnet und ggf deshalb hat xl beim Neuanlegen erst mal keine darstellbaren Werte zV gehabt…
Das #NV-Problem kann ich aus der Ferne schlecht nachvollziehen — evtl ist was verrutscht…
Weiter viel Erfolg, Luc :-?
PS: Hast du vor, meinen Vorschlag bzgl KatListe aufzugreifen?
Anzeige
AW: Aha! Lag wohl am anfängl Zeitbedarf,...
29.03.2011 16:10:27
Maris
KatListe brauche ich garnicht funktioniert auch so wunderbar nur #NV Problem besteht weiterhin. ER erkennt die TabNamen nicht in der Zuordnungstabelle und das nur bei Kennzahl3 woanders funktioniert es :-( komisch?
Heute abend werde ich mal das Bsp entsprd...
29.03.2011 18:45:13
Luc:-?
…umbauen, Maris,
viell ergibt sich was daraus. Manche xlVss (besonders ab xl12) machen bei bestimmten Textanfängen in bestimmter Umgebung Probleme → bspw C#, R#, S# u. Z# (#=Ziffer) und wahrscheinl noch mehr. Auch die Länge des Textes könnte ggf eine Rolle spielen, ist ab xl12 aber eher unwahrscheinl.
Kann allerdings nur mit xl12 testen. Hatte da auch gestern ein Problem m.deiner BspDatei beim Testen, das Windows völlig durcheinander gebracht hat, so dass ich neu starten musste. Viell steckt da also irgendwo der Wurm drin. Notfalls erstmal alle VZellen wieder aufheben bis es fktt und erst dann m.d.Pinsel wiederherstellen.
Gruß Luc :-?
Anzeige
Eigentl wär's ja eher TabListe, aber nach...
30.03.2011 00:52:11
Luc:-?
…Überprüfung mit Autos u.Motorräder statt A gab's keine Fehler in der BspMappe, Maris!
Viell ist in deinem Original einer der verlangten Werte nicht vorhanden, ein Leerstring, unsichtbares Alfazeichen oder Textzahl (keine Zahl) bzw nichtdruckbares Zeichen. Aber wahrscheinl liegt's an einem Fehler bzw eher einer Unterlassung in der HptFml…
Wähle bitte die VZelle I2:N2 aus und ändere dann im Namensmanager die Fml von KatVektor wie folgt:
=1^JETZT()*AUSWERTEN(WENN(Zuordnungstabelle!$D2:$H2="";"0"; WECHSELN("wverweis("&Zuordnungstabelle!$B2&";'#'!B2:BA6;vergleich("""&Zuordnungstabelle!I$1&"""; '#'!A2:A6;0);0)";"#";Zuordnungstabelle!$D2:$H2)))
Erläuterung:
1. Zuordnungstabelle!H$1 wie bei dir verweist auf die falsche, vorhergehende Zelle. Das wirkt sich allerdings nicht auf die Ergebnisse aus, weil diese Angabe immer erst ab der nächsten Zelle gebraucht wird und da wird dann trotzdem das Richtige ausgelesen.
2. 1^JETZT() liefert zwar immer 1, ist aber notwendig, damit sich die Fml-Ergebnisse bei Änderungen selbsttätig aktualisieren*. Das passiert sonst nie, nachdem das 1x berechnet wurde (Ergebnis wird wohl von Xl separat gemerkt und dann immer wieder verwendet — hatte ich vergessen. Das würde auch das #NV erklären, das immer dann auftritt, wenn 1 verlangtes Blatt fehlt. Ist also wohl eher zufällig festgeschrieben worden. Wenn alle verlangten Blätter fehlen, erscheint dagg #BEZUG!.
* JETZT ist eine sog volatile resp unbestimmte Fkt, die im Fml-Assi als Ergebnis nur veränderlich anzeigt.
3. Da du offensichtl Xl14 verwendest (mein Upload war wohl versehentl als .xls angelegt, falls du das nicht schon so gemacht hast), muss die Datei wg der XLM-Fktsverwendung als .xlsm oder .xlsb gespeichert wdn; aber das hast du sicher bereits bemerkt.
Übrigens, schreibe ich Fktsnamen in auszuwertenden Fmln gern klein, damit man gleich sieht, was FmlText und was sofort wirksam ist.
Hoffe, das jetzt alles klappt!
Gruß, Luc :-?
Anzeige
Noch 'ne Variante mit nur jeweils 2 Zellen...
30.03.2011 23:18:34
Luc:-?
…pro VZelle und nur über Auswahl zentrierten Überschriften findest du hier, Christian!
Erläuterung:
Im Prinzip reicht auch das Verbinden von 2 Zellen, damit Xl in bestimmten, mehrere Felder liefernden Matrixfmln alle Felder verwendet. In 1zelligen Matrixfmln macht es das nämlich nicht immer! So kann die relevante FmlAnzahl auf 2 reduziert wdn. In der VZelle steht jetzt nur 1 Fml als Matrixfml. Deren 2. Zelle ist unter dem Verbund versteckt. Das wäre dann auch die eleganteste Lösung.
Alle anderen, zuvor von mir genannten Korrekturen wurden hier bereits eingefügt.
Gruß Luc :-?
PS: Hoffe, lese noch mal was von dir…!
Anzeige
Die Legende zu korrigieren hatte ich zT vergessen!
31.03.2011 21:44:55
Luc:-?
:-?
AW: Die Legende zu korrigieren hatte ich zT vergessen!
31.03.2011 21:56:46
Maris
Hi Luc,
erstmal vielen Dank für deine fantastische Arbeit. Ist schon wahnsinn was du da auf die Beine gestellt hast. Hab jetzt gerade erst meine Emails abgerufen und deine deine 2te Variante getestet.... das #N/V in der ersten Variante hatte ich eleiminiert... mußte natürlich deine Formel anpassen... Werde jetzt deine letzte Varaiante ausprobieren und wenn alles hinhaut in meine Mappe einbauen. Ist eine super Sache deine Matrixzuordnung, ich hoffe Sie wird vielen Usern helfen bei ähnlichen Problemstellungen helfen.
Von meiner Seite aus mindestens tausendmal dankeschön... ich werde dies in vielen Anwendungen für viele Zuordnungen anwenden können und je ausgereifter die Logik ist desto besser :)))
Vg,
Maris
Anzeige
Ja, danke, Maris, glaube, das haben sie...
01.04.2011 15:36:23
Luc:-?
…bei excelformeln.de vergessen, die Matrixfmln zu typisieren…
1. 1zellig/1stufig, 2. mehrzellig/1stufig, 3. 1zellig/mehrstufig, 4. mehrzellig/mehrstufig
Typ3 macht Probleme und gilt deshalb gemeinhin als nicht lösbar, aber, wenn man weiß wie's geht… Und die hier benötigte Matrixformel ist natürl von diesem Typ, denn es soll nur 1 Ergebnis rauskommen, die Berechnungsbasis ist aber ein mehrstufig komplex ermitteltes Datenfeld!
Wenn sie das bei excelformeln berücksichtigt hätten, wären sie evtl doch nicht so vehement gegen VerbundZellen… ;-)
Viel Erfolg! Gruß+schöWE, Luc :-?
Anzeige
Ach so, noch etwas! In deinem Fall hätte...
01.04.2011 15:51:41
Luc:-?
…auch mit einer jeweils 2., ausgeblendeten Spalte gearbeitet wdn können, Christian,
aber das bietet sich nicht immer an → eine ausgeblendete Spalte mitten in der Tabelle, und ich hasse so etwas (auf Grund schlechter Erfahrungen). Ausgeblendete Spalten/Zeilen (für Hilfszellen) am Blattende (was hier nach korrigierter Variante1 dann jeweils 5 sein müssten) sind da eher OK.
Luc :-?

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige