EXCEL: Verweise und Summen in verschiedenen Tabellenblättern

...komplette Frage anzeigen

3 Antworten

Da bin ich wieder ... hat ne Weile gedauert.

Beispieldatei:
http://www.workupload.com/file/jCZNiC1a

Mein Lösungsvorschlag erfordert Vorarbeit, aber das denke ich ist die beste Lösung, falls die Anzahl der Aufträge groß wird bzw. die Anzahl der Kombinationen sehr groß ist. Sonst gibt das einen riesen Rechenaufwand für Excel und die Tabelle stürzt schlimmstenfalls ab.

Alle Aufträge in dieselbe Arbeitsmappe kopieren. Je Auftrag ein Tabellenblatt. Die Tabellenblätter müssen gleich heißen und fortlaufend nummeriert sein (siehe Erläuterung unten).

Auf Tabellenblatt 2 muss als Hilfstabelle zuerst händisch die komplette Liste aller Artikel und deren Kombinationsmöglichkeiten eingegeben werden (ist ja aber zum Glück nur einmalig nötig).
Hilfstabelle also so in etwa wie
Sessel XL grün
Sessel XL blau
...
Sessel M grün
Sessel M blau
...
Tisch XL ....

Anhand dieser Liste werden die nachfolgenden Arbeitsblätter (3 ff.) durchsucht.

Die Formel dafür ist

=SUMMENPRODUKT(
SUMMEWENNS(
INDIREKT("Tabelle"&ZEILE($3:$10)&"!D:D");
INDIREKT("Tabelle"&ZEILE($3:$10)&"!A:A");
A2;
INDIREKT("Tabelle"&ZEILE($3:$10)&"!B:B");
B2;
INDIREKT("Tabelle"&ZEILE($3:$10)&"!C:C");
C2
)
)

(Zeilenumbrüche entfernen, sie dienen folgender Erläuterung)
Sind zu wenig zu durchsuchende Tabellenblätter vorhanden oder heißen sie anders als angegeben (auch wenn die Zahl nicht fortlaufend ist sondern eine Lücke hat), gibt es einen Fehler
Die Tabellenblätter mit den Aufträgen heißen bei mir Tabelle3, Tabelle4, etc. und werden bis zum 10. durchsucht. Solltest du sie anders nennen wollen, musst du jedesmal den Text bei der INDIREKT-Funktion anpassen. Wichtig ist auch, dass sie durchnummeriert sind, sonst klappt das nicht. Wenn du die Tabellenblätter bpsw Aufrag1, Auftrag2, etc. nennen willst, wird aus
INDIREKT("Tabelle"&ZEILE($3:$10)
dann ein
INDIREKT("Auftrag"&ZEILE($1:$10)
Wenn die Anzahl der Tabellenblältter weiter als bis 10 geht, erhöhe die letzte Zahl bei ZEILE().

Zeile 3 der Formel beinhaltet den Summenbereich (hier Spalte D auf allen Tabelenblättern, wo das drin steht was summiert werden soll bei Zutreffen der Kriterien)
Zeile 4 ist der Suchbereich (Spalte A aller Tabellenblätter), der das erste Suchkriterium enthält
Zeile 5 ist der Bezug auf das erste Suchkriterium, welches in A2 steht.
Zeile 6 zweiter Suchbereich (Spalte B)
Zeile 7 zweites Kriterium
Zeile 8 dritter Bereich
Zeile 9 drittes Kriterium.

Das kannst du auch noch entspr. mit weiteren Kriterien erweitern bei Bedarf.

Ich beginne mit Zeile 2 bei den Kriterien, weil ich in Zeile 1 eine Spaltenbeschriftung habe (siehe Beispiel)


Auf dem ersten Tabellenblätt, welches die finale und um Leerzeilen (0 Treffer) bereinigte Übersicht enthält, kommt folgende Matrixformel (Eingabe mit STRG+Shift+Return) zum Einsatz:

{=WENNFEHLER(INDEX(Hilfstabelle!A$2:A$100;KKLEINSTE(WENN(Hilfstabelle!$D$2:$D$100>0;ZEILE(Hilfstabelle!$A$2:$A$100);FALSCH);ZEILE($A1))-1);"")}

Geschweifte Klammern werden nicht mit eingegeben (Eingabe mit STRG+Shift+Return abschließen!)
Werte beginnen in Zeile 2 bei der Hifstabelle. Wenn die Werte der Hilfstabelle mit Zeile 1 beginnen, dann die -1 bei ZEILE($A1))-1) entfernen. Wenn sie mit Zeile 3 beginnt, dann ZEILE($A1))-2). Wenn mit Zeile 4 dann ZEILE($A1))-3) etc.

Formel nach rechts und nach unten aufziehen/kopieren, um alle Spalten und Zeilen angezeigt zu bekommen.


Ich hoffe, es klappt.

Ortogonn 11.09.2013, 15:48

Nachtrag:
Bei Sonder-/Leerzeichen in den Tabellenblattnamen, muss in den Formeln der Name in ' ' gefasst werden.

=SUMMENPRODUKT(
SUMMEWENNS(
INDIREKT("'Tabelle"&ZEILE($3:$10)&"'!D:D");
INDIREKT("'Tabelle"&ZEILE($3:$10)&"'!A:A");
A2;
INDIREKT("'Tabelle"&ZEILE($3:$10)&"'!B:B");
B2;
INDIREKT("'Tabelle"&ZEILE($3:$10)&"'!C:C");
C2
)
)

und

{=WENNFEHLER(INDEX('Hilfstabelle'!A$2:A$100;KKLEINSTE(WENN('Hilfstabelle'!$D$2:$D$100>0;ZEILE('Hilfstabelle'!$A$2:$A$100);FALSCH);ZEILE($A1))-1);"")}
0
Ortogonn 17.09.2013, 12:40
@Ortogonn

... und mal wieder keine Reaktion :/

Ich hab das Gefühl, ich zieh das grad bei komplexen Fragen irgendwie an...

0

Sind alle Tabellenblätter/Auträge in einem Exceldokument oder ist jeder Auftrag ein eigenes Dokument?

Soll die zusammenfassende Tabelle im selben Dokument stehen, wie die Aufträge 8wenn diese alle zusammen in 1 Dokument stehen) ?

Verstehe ich das richtig, dass du in der Zusammenfassung ohne Berücksichtigung der Aufträge die Gesamtanzahl aller bspw. grünen Ohrensessel in Größe XL aus allen Aufträgen als 1 Wert angezeigt haben möchtest? Resp. alle grünen Ohrensessel in M, alle roten in XL, ...

jo13071990 04.09.2013, 13:13

Hey,

eigentlich ist jeder Auftrag ein eigenes Dokument, ist aber kein Problem sie alle in ein Dokument zu machen, wenn das leichter ist.

Das wäre egal, hauptsache die zusammenfassende Tabelle steht irgendwo :-)

Ja genau das ist richtig. Bspw.: erste Zeile, Anzahl aller grünen Ohrensessel in S aus allen Aufträgen, zweite Zeile alle grünen Ohrensessel in M, dritte Zeile alle roten Ohrensessen in S und so weiter ...

0
Ortogonn 04.09.2013, 13:24
@jo13071990

Ist machbar.
Wenn die Aufträge in versch Dokumenten stehen und das Ganze ohne VBA gelöst wird, müssen alle Dokumente geöffnet sein. Sonst kann Excel keinen Bezug herstellen.
Eine VBA-Lösung kann ich leider nicht anbieten.

Ich grübel grad auch noch wegen der Vorauswahl. Eine zusammenfassende Liste mit allen Artikeln und nur bei den verwendeten mit Anzahl ist sicherlich nicht gewünscht.
Also müssen auch die Suchbegriffe "ermittelt" werden, was das Ganze noch etwas aufbläht.

Da ich hier auf Arbeit grad nicht die Zeit hab, müsstest du bis heut Abend warten. Dann schau ich mir das mal genauer an.

Um eine eigene Idee vom Umfang zu bekommen, kannst du ja gern mal hier rein schauen:
w w w.excelformeln.de/formeln.html?welcher=342
w w w.excelformeln.de/formeln.html?welcher=235
w w w.excelformeln.de/formeln.html?welcher=234

1
jo13071990 04.09.2013, 17:21
@Ortogonn

Ach du je, ich probiere schon mal ein wenig selber durchzublicken, vielleicht führt das ja schon zu etwas.

0
Ortogonn 05.09.2013, 16:22
@jo13071990

hmm .. interessanter Weise hab ich grad ein ähnliches Problem zu lösen. Allerdings 1 Tabelle mit ~30.000 Zeilen.

Beim Filtern per Formel (denn der Spezialfilter ist so flexibel wie ein Grundofen und müsste jedes Mal neu ausgeführt werden) hat sich Excel nach mehreren Minuten Volllast (alle Kerne i7 2000er Serie) verabschiedet :D

Ich glaub, ich muss noch an der Formel feilen.

0

Klar, Du musst bei den Werten halt mit angeben, auf welchem Tabellenblatt sie sind, also z.B.

='Auftrag1'!F6

Der Name des Tabellenblatts steht in Hochkommata. Damit holst Du Dir die Summen aus den einzelnen Tabellenblättern und addierst sie.

Ortogonn 04.09.2013, 12:57

Die Hochkommata brauchst doch nur, wenn die Daten in einem anderen Exceldokument stehen. Oder seh ich das falsch?

0
germanils 04.09.2013, 12:59
@Ortogonn

Nein, in einem anderen Tabellenblatt im selben Dokument.

0
augsburgchris 04.09.2013, 14:37
@germanils

und wenn der Blattname Sonderzeichen enthält. In diesem Fall reicht auch =Auftrag1!F6 Aber beim Erstellen des Bezuges durch mausklick entscheidet Excel das selbst was notwendig ist.

1
Ortogonn 10.09.2013, 12:59
@augsburgchris

oder bei Leerzeichen im Blattnamen, wie mir aufgefallen ist. Scheint auch als Sonderzeichen zu gelten.

1
jo13071990 04.09.2013, 13:00

Hey,

danke erstmal für die schnelle Hilfe.

Ich glaube das löst leider noch nicht ganz das Problem. Ich probier es mal besser zu erklären:

Auf dem Auftrag (1) steht in Spalte A immer die Farbe (z.B. blau) in Spalte B: die Anzahl (z.B. 2) in Spalte C: die Größe (z.B. M) ind Spalte D: die Artikelnummer. (z.B. 123). Jetzt gibt es den Artikel 123 aber auch noch in anderen Farben und auch in anderen Größen.

In der zusammenfassenden Tabelle müsste also erstmal erkannt werden um welchen Artikel es sich in welcher Größe und welcher Farbe handelt und wie oft dieser dann auf allen Aufträgen bestellt wurde. Und auch wie oft dieser dann in einer anderen Größe bestellt wurde usw.

0

Was möchtest Du wissen?