Tabelle aus vielen Tabellenblättern

Computer: Software & Programmierung Office Software
#1

Ich habe drei Fragen zur Erstellung einer Excel Tabelle (Excel 2003)
Vorhaben: Ich erstelle Kalkulationen in Excel (ca. 250 bis 300 Stk.) Aus denen werden dann bestimmte Werte in einer Tabelle, nach Kunden und Auftragsnummern sortiert, aufgeführt. Je Spalte ein Auftrag.
Meine Idee: Erstes Tabellenblatt wird die Aufstellung. Die Kalkulationen füge ich als zusätzliche Tabellenblätter ein. Die Daten der Aufstellung (Menge, Auftragsvolumen, etc.) übernehme ich über die Summenfunktion aus der entsprechenden Zelle der jeweiligen Kalkulation. Die Kalkulationen wiederum greifen auf den Dollarkurs zu, den ich auf der Aufstellung (erstes Blatt) in einer Zelle eingetragen habe.

Frage 1: In jeder Spalte der Aufstellung greife ich auf die gleichen Zellen der Kalkulationen zu. Halt nur auf einem anderen Tabellenblatt. Kann ich die Summenfunktionen irgendwie kopieren, dass automatisch auf das nächste Tabellenblatt zugegriffen wird?

Frage 2: Die Aufstellung muss ich zwischendurch immer neu nach Kundenname und Auftragsnummer sortieren. Wenn man 10-20 Tabellenblätter anhängen hat, auf die über Formeln zugegriffen wird, funktioniert das. Klappt das auch noch mit 250-300 Tabellenblättern, oder ist Excel damit überfordert?

Frage 3: Jedes Tabellenblatt bekommt einen Namen. Kann ich auch noch diese Tabellenblätter nach Namen sortieren? Ansonsten finde ich bei der Menge keine Kalkulationen wieder. Wenn ja, haut es mir dann auch nicht meine Summenfunktionen durcheinander?

Ich hoffe, ich habe mein Anliegen verständlich erklärt und danke Euch schon jetzt vielmals für Eure Hilfe. Hab leider nicht so viel Ahnung davon.

LGConny

#2

Schwierig …
Moin Conny,

Frage 1: …Kann ich die Summenfunktionen irgendwie kopieren, dass automatisch auf das nächste Tabellenblatt zugegriffen wird?

Soweit ich weiß geht das nicht, nein. Aber man kann das per Suchen/Ersetzen erledigen, indem man die Tabellennamen in der Formel jeweils ersetzt.

Frage 2: … sortieren. … Klappt das auch noch mit 250-300 Tabellenblättern, oder ist Excel damit überfordert?

Ich bin nicht sicher. Klingt nicht nach entspannter Arbeit für Excel. Allein schon die pure Anzahl der Blätter … abgesehen von der hohen Anzahl meine ich gerade irgendwo hier gelesen zu haben, dass man maximal 255 Blätter anlegen kann in einer Datei. Und wie gesagt - bei der Menge wird sich Excel möglicherweise ´n Wolf sortieren.

Frage 3: … Tabellenblätter nach Namen sortieren?

Du kannst sie alle mit der Hand so verschieben, dass sie in der richtigen Reihenfolge stehen. Dadurch passiert natürlich mit den Formeln nichts, weil diese ja die TabellenNAMEN referenzieren. Und das Verschieben der Blätter kann man prinzipiell einfach per Makro erledigen.

Ich hoffe, ich habe mein Anliegen verständlich erklärt …

Bis auf die Sache mit den eigentlichen Berechnungen undso, die aus der Entfernung oft schwer zu erfassen sind, ja :wink: Allerdings glaube ich, dass Excel in Deinem Falle wegen der großen Zahl der benötigten Blätter kein geeignetes Instrument ist. Den Verweis auf Access spare ich mir jetzt mal, weil das die Sache nur noch komplizierter macht, auch wenn es an anderen Stellen die entscheidenden Vorteile bieten müsste.

Vielleicht fällt Dir ja noch eine andere Strukturierungsmöglichkeit für die Daten ein, die nicht darauf basiert, dass jede Kalkulation auf ein eigenes Blatt kommt. Dann kann es immernoch passieren, dass die Datei zu „fett“ wird, aber Du hast nicht die 255er Grenze. Vielleicht kannst Du die ganze Sache auch auf 10 Dateien aufteilen irgendwie. Dann könntest Du immernoch eine Zusammenfssung erstellen, die auf diese Dateien verweist. Nicht sonderlich schön, aber möglich.

Viele Grüße,
Kristian

#3

Hallo Conny,

Frage 3: Jedes Tabellenblatt bekommt einen Namen. Kann ich
auch noch diese Tabellenblätter nach Namen sortieren?
Ansonsten finde ich bei der Menge keine Kalkulationen wieder.

wenn du in Google excel blätter sortieren eingibst, erscheint gleich oben eine Fundstelle
http://www.wintotal.de/Artikel/tabellenblaetter/tabe…
in der du einen Macro-Code von Matthias Köhler runterladen kannst, der
folgendes kann: „Blätter einer Mappe sortieren und Verzeichnisblatt führen“ .

Gruß Finus

#4

Hallo Conny
Ich bin sicher, dass das alles mit Excel 2003 machbar ist. Dazu brauchst du ein ausgewachsenes Makro, wobei dir hier sicher weiter geholfen werden kann.

Hast du Makro-Kenntnisse? (Wo müssen wir beginnen?)

In einer Excel2003-Mappe kannst du „unendlich“ viele Blätter haben, wie weit man die auch sortieren kann, weiss ich nicht auswendig, werde es aber testen und hier mitteilen.
Damit man sinnvoll sortieren kann, müssen die Blätter einen entsprechenden Namen haben; der Name sollte nicht zu lange sein, da du sonst nicht viel sehen kannst auf der scroll-Leiste.

Bis bald
Erich

#5

Vielleicht kannst
Du die ganze Sache auch auf 10 Dateien aufteilen irgendwie.
Dann könntest Du immernoch eine Zusammenfssung erstellen, die
auf diese Dateien verweist. Nicht sonderlich schön, aber
möglich.

Bisher war jedes Kalkulationsblatt eine eigene Datei. Ich dachte nur, wenn ich dieses Jahr alle Aufträge in eine Datei packe, würde das das Zusammenspiel mit der Liste erleichtern.

Da sich der Durschnittskurs (Dollar) im Laufe des Jahres öfters ändert, hatte ich mit der alten Methode alle Kalkulationen von Hand ändern müssen. Wahrscheinlich nur aus Unwissenheit.

So, wie sich das jetzt anhört, ist es wohl besser, wenn ich das alte System beibehalte. Ich muss dann nur noch herausfinden, wie ich die Daten der einzelnen Dateien in meine Tabelle bekomme.

#6

Hast du Makro-Kenntnisse? (Wo müssen wir beginnen?)

Bisher keine, werde aber mal schnell einen Microsoft Online Kurs machen. Es gibt bestimmt einen zu diesem Thema.

#7

Hast du Makro-Kenntnisse? (Wo müssen wir beginnen?)

Bisher keine, werde aber mal schnell einen Microsoft Online
Kurs machen. Es gibt bestimmt einen zu diesem Thema.

„Mal schnell“ ist nicht das richtige Stichwort für VBA :wink: Also vergiss das lieber erstmal. Trotzdem wäre es nützlich, sich damit zu beschäftigen, zumal es wirklich nicht sehr schwer ist.

Wenn Excel 2003 also doch beliebig viele Blätter beinhalten kann … vielleicht wäre das eine Möglichkeit. Aber das bestehende System aufzubohren könnte man zuerst probieren. Technisch kein größeres Problem. Ich sammel jeden Monat Daten von Kollegen ein, die mir jeweils eine Datei zur Verfügung stellen. Diese lese ich dann alle per Makro automatisch ein und kopiere die relevanten Daten in ein Sammelblatt in der Master-Datei. Keine sehr große Sache. Makrofrei würde es ggf. auch gehen - müsste man probieren. Du wolltest ja keine Makros haben.

Kristian

#8

Grüezi Conny

Meine Idee: Erstes Tabellenblatt wird die Aufstellung. Die
Kalkulationen füge ich als zusätzliche Tabellenblätter ein.

OK, das ist IMO gut machbar - Du kannst mittels einer benannten Formel und Funktionen im Tabellenblatt ein ‚immerwährendes‘ Inhaltsverzeichnis deiner Mappe anlegen und dies auch gleichmit einem Hyperlink verknüpfen, dann kannst Du vom ersten Blatt sehr schnell auf die weiteren gelangen. Wenn dort noch ein weiterer Hyperlink für den Rücksprung eingepflegt wird, ist das Ganze dann sehr komfortabel.

Schau dir dazu das folgende Beispiel an, damit kommst Du sicher bereits einiges weiter; die Formeln kannst Du einfach nach unten ziehen.

Tabellenblatt 'Inhalt':

 │ A │ B │ C │ 
──┼──────────┼───────────────┼────────────────┤
1 │ │ Springen zu: │ Wert aus C1:C5 │ 
2 │ Inhalt │ Inhalt │ │ 
3 │ Tabelle2 │ Tabelle2 │ 30 │ 
4 │ Tabelle3 │ Tabelle3 │ 45 │ 
5 │ │ │ │ 
6 │ │ │ │ 
7 │ │ │ │ 
──┴──────────┴───────────────┴────────────────┘

Benutzte Formeln:
A2: =WENN(ZEILEN($1:1)\>ANZAHL2(x);"";TEIL(INDEX(x;ZEILEN($1:1));FINDEN("]";INDEX(x;ZEILEN($1:1)))+1;31))
B2: =HYPERLINK("#'"&A2&"'!A1";A2)
C3: =SUMME(INDIREKT("'"&A3&"'!C1:C5"))

Benutzte Namen:
x: =ARBEITSMAPPE.ZUORDNEN(1+0\*JETZT())

Die weiteren Blätter enthalten dann folgende Inhalte:

Tabellenblatt 'Tabelle2'

 │ A │ B │ C │ 
──┼──────────────────┼─────┼────┤
1 │ zurück zu Inhalt │ │ 2 │ 
2 │ │ │ 4 │ 
3 │ │ │ 6 │ 
4 │ │ │ 8 │ 
5 │ │ │ 10 │ 
──┴──────────────────┴─────┴────┘

Benutzte Formeln:
A1: =HYPERLINK("#'"&TEIL(INDEX(x;ZEILEN($1:1));FINDEN("]";INDEX(x;ZEILEN($1:1)))+1;31)&"'!A1";"zurück zu "&TEIL(INDEX(x;ZEILEN($1:1));FINDEN("]";INDEX(x;ZEILEN($1:1)))+1;31))

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Die Daten der Aufstellung (Menge, Auftragsvolumen, etc.)
übernehme ich über die Summenfunktion aus der entsprechenden
Zelle der jeweiligen Kalkulation. Die Kalkulationen wiederum
greifen auf den Dollarkurs zu, den ich auf der Aufstellung
(erstes Blatt) in einer Zelle eingetragen habe.

Frage 1: In jeder Spalte der Aufstellung greife ich auf die
gleichen Zellen der Kalkulationen zu. Halt nur auf einem
anderen Tabellenblatt. Kann ich die Summenfunktionen irgendwie
kopieren, dass automatisch auf das nächste Tabellenblatt
zugegriffen wird?

Ja, wenn dein ‚Inhaltsverzeichnis‘ entsprechend aufgebaut ist, dann klappt das.
Du benötigst eine Auflistung der Namen der einzelnen Tabellenblätter auf die Du dich dann mit INDIREKT() beziehen kannst um die individuellen Zugriffe zu steuern.

Frage 2: Die Aufstellung muss ich zwischendurch immer neu nach
Kundenname und Auftragsnummer sortieren. Wenn man 10-20
Tabellenblätter anhängen hat, auf die über Formeln zugegriffen
wird, funktioniert das. Klappt das auch noch mit 250-300
Tabellenblättern, oder ist Excel damit überfordert?

Ja, das müssste IMO nach wie vor klappen.

Frage 3: Jedes Tabellenblatt bekommt einen Namen. Kann ich
auch noch diese Tabellenblätter nach Namen sortieren?
Ansonsten finde ich bei der Menge keine Kalkulationen wieder.
Wenn ja, haut es mir dann auch nicht meine Summenfunktionen
durcheinander?

Wenn alles sauber aufgebaut ist klappt das bestens - dann kannst Du auch das Sortiern im ersten Blatt ‚vergessen‘. Dieses zeigt dann immer alle enthaltenen Tabellenblätter in der vorliegenden Reihenfolge an.
Wenn du also eine neue Kalkulation hinzugefügt hast, dann benenne das Tabellenblatt entsprechend und sortiere (das muss mit VBA geschehen) die Tabellenblätter auf- oder absteigend.

Ich hoffe, ich habe mein Anliegen verständlich erklärt und
danke Euch schon jetzt vielmals für Eure Hilfe. Hab leider
nicht so viel Ahnung davon.

Das kommt schon noch - sei getrost :smile:

–
Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#9

Hallo Thomas,

bei Dir sieht das so einfach aus. Leider fühle ich mich auf den ersten Blick von Deinen Formeln etwas überfordert. Ich werde aber mal versuchen, Dein Beispiel umzusetzen. Vielleicht verstehe ich es dann.

Vielen Dank
Conny

#10

Grüezi Conny

bei Dir sieht das so einfach aus. Leider fühle ich mich auf
den ersten Blick von Deinen Formeln etwas überfordert. Ich
werde aber mal versuchen, Dein Beispiel umzusetzen. Vielleicht
verstehe ich es dann.

Lass dich von den etwas längeren Formeln nicht abschrecken :smile:

Wenn Du magst lade ich die Demo-Mappe auf einen Webspace hoch und poste hier dann den Link.

–
Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#11

Hi Kristian,

„Mal schnell“ ist nicht das richtige Stichwort für VBA :wink: Also
vergiss das lieber erstmal. Trotzdem wäre es nützlich, sich
damit zu beschäftigen, zumal es wirklich nicht sehr schwer
ist.

Das habe ich auch gemerkt. Vorallem muss ich dazu ins Büro fahren. Dort habe ich Excel2003 und hier zuhause 2007. Mite dem Thema werde ich mich aber auf jeden Fall mal beschäftigen. Scheint sehr hilfreich zu sein. Danke für den Tipp.

Gruß
Conny

#12

Hi Thomas

Lass dich von den etwas längeren Formeln nicht abschrecken :smile:

Ich versuche es :wink:)

Wenn Du magst lade ich die Demo-Mappe auf einen Webspace hoch
und poste hier dann den Link.

Das wäre ja Luxus pur. Wäre super, wenn Du das machen könntest.

Vielen Dank
Conny

#13

Grüezi Conny

Wenn Du magst lade ich die Demo-Mappe auf einen Webspace hoch
und poste hier dann den Link.

Das wäre ja Luxus pur. Wäre super, wenn Du das machen
könntest.

OK, dann schau dir die Mappe hier mal an:

http://users.quick-line.ch/ramel/Demo-Daten/Inhalt-T…

Und bei Fragen einfach fragen, gell! :smile:

–
Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#14

Bitte um Verzeihung!
Hi Conny etc

In einer Excel2003-Mappe kannst du „unendlich“ viele Blätter
haben

Das ist nicht wahr, ein Versuch zeigte klar die Grenze von 256 Blättern!!!

, wie weit man die auch sortieren kann, weiss ich nicht

auswendig, werde es aber testen und hier mitteilen.
Damit man sinnvoll sortieren kann, müssen die Blätter einen
entsprechenden Namen haben

Es gibt im Internet Sortiermakros, z.B:
http://www.ozgrid.com/VBA/sort-sheets.htm
Der läuft prima mit den 256 Blättern!
Dennoch allen ein sonniges Wochenende
Erich

#15

Grüezi Erich & all

In einer Excel2003-Mappe kannst du „unendlich“ viele Blätter
haben

Das ist nicht wahr, ein Versuch zeigte klar die Grenze von 256
Blättern!!!

Wie hat Du den Versuch denn durchgeführt?

Die folgenden Zeilen habe ich nach ca 2000 Durchläufen abgebrochen - das Limit der Anzahl Tabellenlbätter hängt nur vom zur Verfügung stehenden Speicher und der Komplexität der einzelnen blätter ab.

Sub Makro1()
 Do
 Worksheets.Add
 Loop
End Sub

–
Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#16

In einer Excel2003-Mappe kannst du „unendlich“ viele Blätter
haben

Das ist nicht wahr, ein Versuch zeigte klar die Grenze von 256
Blättern!!!

Hallo Erich,

nein, das ist nur durch verfügbaren Speicher begrenzt und von den Systemressourcen.

Zitat aus xlam.ch:
…
Die maximale Anzahl Arbeitsblätter in einer Arbeitsmappe ist nur durch den verfügbaren Speicher begrenzt.
Wenn Sie eine neue, leere Arbeitsmappe anlegen, kann diese maximal 255 Arbeitsblätter enthalten. Beim Versuch, im Feld „Blätter in neuer Arbeitsmappe“ des Dialogfensters „Optionen“ eine grössere Zahl als 255 einzugeben, erscheint diese Fehlermeldung:
…
Danach werden noch Einschränkungen auf 98 Blätter bei XL97 genannt bei einer freigegebenen Mappe.

Schau halt selbst:

http://www.xlam.ch/xlimits/xllimit13.htm

Und zum Sortieren, wenn man die Blätter mit Worksheets.Add anlegt kann man doch gleich einsortieren.

worksheets.Add after:=worksheets(„Dezember2007“)
activesheet.name=„Januar2008“

oder s.ä.

Gruß
Reinhard