Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Sortierung - Formelveränderung





Frage

Wünsche einen wunderschönen guten Morgen/Rosenmontag! Bin auf der Suche nach Trick17 betreffend dem Thema Sortierung und Formeln. Beim Sortieren soll der Formelbezug unberücksichtigt bleiben. Jedoch bin ich mit meiner Weisheit am Ende. Vielleicht kann mir ja jemand weiterhelfen. Zu meiner Problemstellung habe ich eine Musterdatei samt Beschreibung unter [url]http://www.netupload.de/detail.php?img=01553dcd76b56647b4cf931d61b072a3.xls[/url] abgelegt. Ciao Thomas

Antwort 1 von M.O.

Hallo Thomas,

hier ein kleiner Denkanstoß von mir:

Probier doch mal in Tabelle3 statt der Summenformel =SUMME(Tabelle1:Tabelle2!E2)
folgende Formel:
=SUMMEWENN(Tabelle1!$A$2:$E$11;A2;Tabelle1!$E$2:$E$11)+SUMMEWENN(Tabelle2!$A$2:$E$11;A2;Tabelle2!$E$2:$E$11)

Die Bereiche musst du natürlich entsprechend anpassen. Die Formel
kannst du dann nach unten ziehen.

Gruß

M.O.

Antwort 2 von Tomschi

Hi M.O.!

Danke, dass Du Dir mein Problem zu Herzen genommen und mir einen tollen Ansatz geliefert hast!

Leider ist es nicht so einfach wie in meiner Mustertabelle dargestellt.
=SUMME(Tabelle1:Tabelle2!E2) sollte nur als Beispiel dienen.
In der Realität sind davon ca. 20 Blätter betroffen.
Das sind die Werte + ... wohl ein wenig umständlich.
Ausserdem ist in der Echtdatei nicht nur diese eine Spalte davon betroffen.

Vielleicht könnte man es mit INDIREKT umsetzen.
Habe vor einiger Zeit schon mal unter https://supportnet.de/threads/1393952 ein paar Tipps dazu gekommen. Leider schaffe ich es aber nicht wenn mehrere Tabellen davon betroffen sind.
=summe(indirekt("Tabelle1!E"&zeile()) = okay
Aber wie kann ich =SUMME(Tabelle1:Tabelle2!E2) in indirekt einbauen?

Vielleicht denk ich auch kompliziert und es gibt eine viel einfacher Lösung.

Ciao

Tom

Antwort 3 von Saarbauer

Hallo,

hast du es mal mit Adresse() in der Summenformel versucht?

Ich könnte mir denken, das kommt deinem Wunsch am nächsten.

Gruß

Helmut

Antwort 4 von M.O.

Hallo Thomas,

das habe ich mir fast gedacht :-).

Das mit Indirekt über mehrere Tabellenblätter ist nicht so einfach.
Die Lösung zu diesem Problem findest du hier: Link1 und Link2

Danach muss die Formel für deine Beispieltabelle so aussehen:
=SUMME(SUMME(SUMMEWENN(INDIREKT("Tabelle"&ZEILE(1:2)&"!E"&ZEILE());"<1e+99")))
Achtung: Matrixformel - mit STRG+SHIFT+RETURN abschließen!

Für deine richtige Tabelle musst du sie entsprechend anpassen.
Also wenn deine Blätter Tabelle1 bis Tabelle20 heißen:
=SUMME(SUMME(SUMMEWENN(INDIREKT("Tabelle"&ZEILE(1:20)&"!E"&ZEILE());"<1e+99")))

Gruß

M.O.

Antwort 5 von rainberg

Hallo Thomas,

während M.O. gepostet hat war ich am Probieren und will Dir das Ergebnis nicht vorenthalten.
Der Ursprung der Formel liegt hier http://www.excelformeln.de/formeln.html?welcher=342.

Und so sieht sie aus

=SUMME((T(INDIREKT("Tabelle"&SPALTE(A:E)&"!A"&ZEILE($2:$100)))=A2)*(N(INDIREKT("Tabelle"&SPALTE(A:E)&"!E"&ZEILE($2:$100)))))

Sie berechnet die Summen nicht einfach nach den Zellbezügen sondern nimmt als Kriterium den Text aus Spalte A.

Bedingung ist, dass die Tabellennamen ein Indize haben, also Tabelle1 bis Tabellexxx oder Monat1 bis Monatxxx oder ein anderer Text vor dem Indize.

Ich hab Dir mal ein Beispiel mit 5 Tabellen angehängt, was Du problemlos auf 26 Tabellen erweitern kannst, aber immer nur soviel Tabellen in der Formel ansprechen, wie tatsächlich vorhanden sind.

Die beiden fettgeduckten Passagen in der Formel müssten geändert werden
SPALTE(A:E) bedeutet soviel wie Tabelle1(A)-Tabelle5(E).
SPALTE(A:Z) würde demzufolge Tabelle1 - Tabelle26 bedeuten
Also der Wert der Buchstaben entspricht die Reihenfolge derer in Alphabet.

Die beiden Passagen in der Formel ZEILE($2:$100) bedeuten die Länge der Liste von Zeile 2 bis 100, das kannst Du ebenfalls anpassen.

Schau es Dir einfach mal an und frage bei Unklarheiten.

http://www.netupload.de/detail.php?img=ec6de54a1ca05e2c7ffb117f7b27...

Gruß
Rainer

Antwort 6 von Tomschi

Hi!

Danke für die Mithilfe!
Um ehrlich zu sein bin ich mit den Angaben im Moment überfordert. Werde mir die Lösungsansätze und die geposteten Link mal in Ruhe ansehen.
Vor dem Wochenende schaffe ich dies aber bestimmt nicht.
Daher bitte etwas Geduld haben.

Eine Frage vorweg:
...Bedingung ist, dass die Tabellennamen ein Indize haben, also Tabelle1 bis Tabellexxx oder Monat1 bis Monatxxx oder ein anderer Text vor dem Indize.
...
Heisst das, dass der Blattname immer mit einer Zahl enden muss?
Z. B. "Jänner" bis "Dezember" bzw. "Jänner - Dezember" funktionieren nicht?

Thanx!

Tom

Antwort 7 von M.O.

Hallo Thomas,

schau dir mal den Link von Rainer an zum Ursprung der Formel an. Dort wird auch erklärt, wie die Formel mit nicht indizierbaren Blattnamen funktioniert.

Die Lösung von Rainer ist für deine Zwecke natürlich die Beste.

Gruß

M.O.

Antwort 8 von rainberg

Hallo Thomas,

es geht natürlich auch mit beliebigen Blattnamen, nur müssen diese dann irgendwo in der Auswertungstabelle hinterlegt sein.
Im Beispiel wäre das der Bereich O2:O6, welcher entsprechend angepasst werden muss/kann.

Die Formel lautet dann so:

{=SUMME((T(INDIREKT("'"&MTRANS(O$2:O$6)&"'!A"&ZEILE($1:$100)))=A2)*(N(INDIREKT("'"&MTRANS(O$2:O$6)&"'!E"&ZEILE($1:$100)))))}

Ich hatte die andere Variante gewählt, weil ich sie für dynamischer halte, ist aber Geschmacksache.

http://www.netupload.de/detail.php?img=683ea7815bf66937b08761e3de37...

Gruß
Rainer

Antwort 9 von Tomschi

Hi!

Ich bin Euch soooo dankbar!

Bye

Tom

Antwort 10 von Tomschi

Hallo Rainer!

Ich habe am Wochenende versucht Deine Formel aus Antwort 8 in meine Exceldatei einzubauen. Funktioniert auch (mehr oder weniger) einwandfrei.

Leider habe ich beim Testen jedoch festgestellt, dass sich der "Spalten-Buchstabe" (--> in Deinem Beispiel E) beim Löschen bzw. Einfügen von Spalte nicht mitändert.
--> daher auch das "mehr oder weniger"

Ansonsten bin ich zu 100% mit Deinem Lösungsansatz glücklich.
Daher bekomst Du von mir auch ein "Diese Antwort hat mein Problem gelöst".
Zwar verstehe nicht wirklich was genau Deine Formel macht, aber sie funktioniert und das ist die Hauptsache.
Was genau z. B. ist der Unterschied einer "normalen Formel" zu einer Matrixformel?
Warum das * zwischen ... A2)*(N(indirekt(...?

@Hemut:
Danke für den Tipp mit "Adresse()". Vielleicht kann ich dies ja anderswo mal einsetzen.

Zuletzt DANKE an M.O.!

Ciao - bis demnächst

Tom

Antwort 11 von rainberg

Hallo Tom,

mit dem händischen Ändern des Spaltenbuchstaben beim Einfügen/Löschen von Spalten musst Du leben.

Innerhalb der Funktion INDIREKT() werden nur Formeln und Bezüge selbsttätig geändert, nicht aber in Anführungszeichen stehende Textpassagen, was eigentlich auch logisch ist.

Was eine Matrixformel ist, ist hier gut beschrieben

http://www.online-excel.de/excel/singsel.php?f=26

Zitat:
Warum das * zwischen ... A2)*(N(indirekt(...?


In Matrixformeln werden Argumente mit "*" oder mit "+" oder mit beiden verknüpft.

"*" entspricht der Tabellenfunktion UND()
"+" entspricht der Tabellenfunktion ODER()

Sind die Argumente mit "*" verknüpft müssen alle verknüpften Argumente WAHR sein um die entsprechende Berechnung auszuführen.
Bei der "+"-Verknüpfung hingegen reicht es, wenn nur eine Verknüpfung den Wert WAHR ergibt.

Entsprechend der geforderten Logik kannst Du diese Verknüpfungsarten nun einzeln oder gemischt einsetzen.

Gruss
Rainer

Antwort 12 von Tomschi

Guten Morgen!

Danke für den Link und die kurze Erklärung.
Ich werde mir die Beschreibung mal in Ruhe ansehen.

Bye

Tom