Excel: Viele Blattbezüge

Computer: Software & Programmierung Office Software
#1

Hallo Ihr Profis,

ich habe in einer Excel-Mappe viele Matrix-formeln, die mit SUMMEWENN auf andere Blätter derselben Mappe Bezug nehmen. Die Formeln müssen immer mal wieder angepaßt werden, und damit ich dann nicht jedesmal den Namen der verschiedenen Tabellenblätter in den Formeln anpassen muß, habe ich die Blattnamen in eine Spalte dynamisch angezeigt und greife in den Spalten rechts daneben mit INDIREKT und dem Blattnamen darauf zu.

Die Struktur des Resultat-Blattes sieht derzeit etwa so aus:

 A B
1 | BMW {=SUMMEWENN(INDIREKT("'"&A1&"'!$D$2:blush:D$10000")\>20;1)}
2 | Mercedes {=SUMMEWENN(INDIREKT("'"&A2&"'!$D$2:blush:D$10000")\>20;1)}
3 | Audi {=SUMMEWENN(INDIREKT("'"&A3&"'!$D$2:blush:D$10000")\>20;1)}
4 | Jaguar {=SUMMEWENN(INDIREKT("'"&A4&"'!$D$2:blush:D$10000")\>20;1)}

Tatsächlich werden die Tabellenblattnamen („BMW“, „Mercedes“ etc.) in der Spalte A sogar über eine INDEX-Formel ermittelt.

Jaja, ich weiß: das böse INDIREKT. Ich habe nämlich nun das Problem, daß ich die Mappe wegen mangelndern Ressourcen nicht mehr speichern kann (ohne übrigens, daß ich an ihrem Inhalt schon was verändert habe). Darum meine Frage:

Wie kann ich Bezüge zu anderen Tabellenblättern herstellen (pro Blatt eine Zeile), ohne INDIREKT und ohne den Namen des Blattes in die Formel einzufügen? Die Formeln sollen wie gesagt kopierbar bleiben.

Technisches: Excel 2010, WinXP SP3

Vielen Dank für Eure Hilfe
Hanno

#2

Grüezi Hanno

ich habe in einer Excel-Mappe viele Matrix-formeln, die mit
SUMMEWENN auf andere Blätter derselben Mappe Bezug nehmen.

Autsch!

Jaja, ich weiß: das böse INDIREKT. Ich habe nämlich nun das
Problem, daß ich die Mappe wegen mangelndern Ressourcen nicht
mehr speichern kann (ohne übrigens, daß ich an ihrem Inhalt
schon was verändert habe). Darum meine Frage:

Wie kann ich Bezüge zu anderen Tabellenblättern herstellen
(pro Blatt eine Zeile), ohne INDIREKT und ohne den Namen des
Blattes in die Formel einzufügen? Die Formeln sollen wie
gesagt kopierbar bleiben.

IMO geht das das so gar nicht - lege deine Datenbasis sauber als ‚flache Tabelle‘ an in nur einem Tabellenblatt, dann kannst Du z.B. mit einer Pivot-Tabelle wunderbar auswerten und läufts nicht mehr in Performance-Probleme.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#3

Hallo Hanno,

Jaja, ich weiß: das böse INDIREKT. Ich habe nämlich nun das
Problem, daß ich die Mappe wegen mangelndern Ressourcen nicht
mehr speichern kann (ohne übrigens, daß ich an ihrem Inhalt
schon was verändert habe).

ich gehe mal davon aus, dass die Matrixfunktionen ohne Indirekt halbwegs performant funktionieren. (Wenn das schon 5 Minuten dauert, würde ich mir mal gedanken machen, ob Excel das richtige Tool ist.)

Bei den Matrixformeln mit Indirekt macht er da überhaupt eine Berechnung wenn du F9 drückst, oder kollabiert das System dann schon?
Ist die Formelberechnung schon auf manuell geändert und „vor dem Speichern neu berechnen“ deaktiviert? Falls nicht, würde damit mal experimentieren.

Wie kann ich Bezüge zu anderen Tabellenblättern herstellen
(pro Blatt eine Zeile), ohne INDIREKT und ohne den Namen des
Blattes in die Formel einzufügen? Die Formeln sollen wie
gesagt kopierbar bleiben.

Ich wüßte nicht, dass es eine Alternative zu Indirekt gibt, was aber nicht heißen soll, dass es wirklich keine gibt. Wenn die Formel auf dem Übersichtsblatt kopierbar sein muss, würde ich generell zwei verschiedene Alternativen ins Auge fassen:

  1. Die Matrixformeln werden auf den jeweiligen Sheets ausgeführt und du holst dir das Ergebnis mit einer anderen Formel, in welcher dann Indirekt benutzt wird, auf das Übersichtsblatt. (Diese Methode wäre mein Favorit und führt sicher am schnellsten zum Erfolg.)

  2. Du programmierst in VBA eine benutzerdefinierte Funktion und verwendest diese als Ersatz für die Matrixfunktion. (Diese Methode führt sicher zum Erfolg, ist aber aufwendiger zu programmieren. Außerdem möchte auch nicht ich wissen, wie lange die Berechnung dann dauert.)

PS: Wenn die Formel auf dem Übersichtsblatt nicht kopierbar sein müsste, würde ich die Matrix-Formeln ohne Indirekt einfach per VBA in die Zellen schreiben.

MfG
Stephan

#4

INDIREKT-weg hilft nicht
Hallo Thomas,

IMO geht das das so gar nicht - lege deine Datenbasis sauber
als ‚flache Tabelle‘ an in nur einem Tabellenblatt, dann
kannst Du z.B. mit einer Pivot-Tabelle wunderbar auswerten und
läufts nicht mehr in Performance-Probleme.

An sich habe ich ja gar keine Performance-Probleme (daß die Berechnung der Tabelle jedesmal ca. 20 Sekunden dauert, macht nix). Blöd ist nur, daß ich diese Tabelle, obwohl am selben Rechner erstellt, jetzt nicht mal mehr direkt nach dem Öffnen speichern kann, also ganz ohne daß ich irgendeine Änderung vorgenommen habe.

Aber anyway: Ich habe sämtliche INDIREKTs durch direkte Bezüge ersetzt, und immer noch kann ich die Tabelle nicht speichern. Selbst wenn ich das ganze Resultat-Blatt lösche: „nicht genügend Ressourcen“.

Hilfe :frowning:

Viele Grüße
Hanno

#5

Hallo Stephan,

ich gehe mal davon aus, dass die Matrixfunktionen ohne
Indirekt halbwegs performant funktionieren. (Wenn das schon 5
Minuten dauert, würde ich mir mal gedanken machen, ob Excel
das richtige Tool ist.)

Wie ich gerade Thomas schrieb, kann ich auch ohne INDIREKT-Formeln nicht speichern. Selbst wenn ich das ganze Blatt lösche - „nicht genügend Ressourcen“.

Bei den Matrixformeln mit Indirekt macht er da überhaupt eine
Berechnung wenn du F9 drückst, oder kollabiert das System dann
schon?

Nein, das Berechnen, Neuberechnen etc. ist kein Problem. Das geht in weniger als 20 Sekunden.

Ist die Formelberechnung schon auf manuell geändert und „vor
dem Speichern neu berechnen“ deaktiviert? Falls nicht, würde
damit mal experimentieren.

Das bewirkt leider keine Besserung.

  1. Die Matrixformeln werden auf den jeweiligen Sheets
    ausgeführt und du holst dir das Ergebnis mit einer anderen
    Formel, in welcher dann Indirekt benutzt wird, auf das
    Übersichtsblatt. (Diese Methode wäre mein Favorit und führt
    sicher am schnellsten zum Erfolg.)

  2. Du programmierst in VBA eine benutzerdefinierte Funktion
    und verwendest diese als Ersatz für die Matrixfunktion. (Diese
    Methode führt sicher zum Erfolg, ist aber aufwendiger zu
    programmieren. Außerdem möchte auch nicht ich wissen, wie
    lange die Berechnung dann dauert.)

Da ich das Speicherproblem nun auch ohne die INDIREKT-Formeln habe, stelle ich diese Lösungsvorschläge erst mal hintenan. Aber vielen Dank für Deine Überlegungen. Vielleicht hast Du ja noch eine Idee, jetzt wo klar ist, daß die INDIREKTs nicht dafür verantwortlich sind.

PS: Wenn die Formel auf dem Übersichtsblatt nicht kopierbar
sein müsste, würde ich die Matrix-Formeln ohne Indirekt
einfach per VBA in die Zellen schreiben.

Klar, aber mit VBA ist das Formelschreiben wieder eine andere Herausforderung, und die Formeln sind vor allem wenn man darin nicht geübt ist, schwer zu prüfen und nachzuvollziehen.

Viele Grüße
Hanno

#6

Hallo Hanno,

Vielleicht hast Du ja
noch eine Idee, jetzt wo klar ist, daß die INDIREKTs nicht
dafür verantwortlich sind.

bei Google findet man dies hier: http://support.microsoft.com/kb/271513/de

Überflieg den Artikel mal! Vielleicht findest du dort einen Lösungsansatz.

Ich tippe mal darauf, dass du irgendein Limit von Excel überschreitest. Sind in der Datei Formeln, die z.B. die Summe von zig tausenden Zellen einer anderen Excel-Tabelle berechnen? Falls ja, zähl mal durch! Ich hatte vor zig Jahren schon mal ein Problem mit einer Datei meines Vorgängers. Alles hatte wunderbar funktioniert bis eines Tages die Quelldatei so groß wurde, dass ein Limit überschritten wurde. Nix ging mehr und ich musste umprogrammieren.

Irgendwann gehts mit Excel einfach nicht mehr, und spätestens dann sollte man soviel viel möglich in eine Datenbank auslagern. Die von Thomas angesprochenen Pivot-Tabellen sind übrigens unschlagbar, wenn man mal eben schnell große Datenmengen in Excel auswerten möchte.

MfG
Stephan

#7

Grüezi Hanno

Aber anyway: Ich habe sämtliche INDIREKTs durch direkte Bezüge
ersetzt, und immer noch kann ich die Tabelle nicht speichern.
Selbst wenn ich das ganze Resultat-Blatt lösche: „nicht
genügend Ressourcen“.

Hilfe :frowning:

Tja, Mappe 'putt würde ich da sagen.

Übertrage die Daten sauber in eine neue Mappe (nicht die ganzen Tabelllenblätter sondern nur die Daten) und fasse sie dabei gleich in einem gemeinsamen Tabellenblatt zusammen (vielleicht benötigst du dann noch eine weitere Spalte die den bisherigen Unterschied in den Tabellenblättern kennzeichnet).

Anschliessend wertest Du das Ganze dann mit einer Pivot-Tabelle aus.

Damit steigt einerseits die Performance und andererseits dürfte auch dein Speichern-Problem vom Tisch sein.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -