gefilterte Daten aus einer Tabelle in andere übernehmen

[Ilonka]

Hallo,

ich habe eine größere Excel-Tabelle, in welcher sämtliche Materialien aller Kurse, die wir verkaufen aufgelistet sind. Eine Beispieldatei hänge ich an. Viele Materialien sind Bestandteil verschiedener Kurse, so ist das Schülermäppchen z.B. in allen Deutschkursen dabei, andere Materialien gehören nur in einen Kurs, z.B. Buntstifte Dicki ausschließlich im Vorschulkurs.

In der Tabelle habe ich hinter der Spalte Artikel die verschiedenen Kurse angelegt und zum jeweiligen Artikel die Anzahl der zum Kurs gehörenden Materialien eingetragen.


  Testdaten.xlsx (Größe: 45,36 KB / Downloads: 2)

Ich möchte nun auf einem neuen Blatt zu jedem Kurs eine automatische Auflistung aller zugehörigen Materialien.

z.B. für die Vorschule:
Wenn das Feld in Spalte C nicht leer ist, dann zeige die Felder ABC der entsprechenden Zeile in neuer Tabelle an.
Wenn das Feld leer ist, soll keine Anzeige erfolgen.

Ebenso will ich die Materiallisten für die anderen Kurse gestalten, Deutsch 1 entsprechen: 
Wenn das Feld in Spalte E nicht leer ist, dann zeige die Felder ABE der entsprechenden Zeile in neuer Tabelle an.

Kann ich das irgendwie über Formeln lösen? Mit VBA kenne ich mich leider gar nicht aus.

Probiert habe ich bislang folgendes über Pivot-Tabelle: 
Allerdings schaffe ich das nur über eine Hilfsspalte: Enthalten VS (Spalte J in der Beispieldatei), sodass ich darüber in der Pivot-Tabelle sortieren kann. Das erscheint mir allerdings sehr umständlich. Das sind bei 26 Kursen 26 zusätzliche Hilfsspalten.
[img]webkit-fake-url://29320bdd-1b66-4dab-82b8-51d70716f7e0/image.tiff[/img]

Wenn es eine bessere Lösung gibt, bin ich wirklich dankbar für jede Hilfe. 
Ich hoffe, ich habe alle notwendigen Infos gegeben.

Lieben Gruß,
Ilonka

[atilla]

Hallo Ilonka,

das sollte mit einer Pivottabelle ohne Hilfspalten möglich sein.

Hier mal zwei Ergebnisse mit zwei Pivottabelle ohne Hilfsspalte:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
2	VS	(Mehrere Elemente)			D1	(Mehrere Elemente)
3
4	Warennummer	Bezeichnung	Summe von VS		Warennummer	Bezeichnung	Summe von D1
5	21101	Schülermäppchen	1		21101	Schülermäppchen	1
6	21103	Bleistift HB Standard	2		21102	Schreiblernbleistift Triplus	2
7	21106	Bunstifte Dicki (10 St. Staedtler)	1		21103	Bleistift HB Standard	2
8	21109	Filzstifte (10 St. Staedtler)	1		21107	Buntstifte Jumbo Ergo Soft (12 St. Staedtler)	1
9	21110	Wachsmalstifte (8 St. Stockmar)	1		21110	Wachsmalstifte (8 St. Stockmar)	1
10	Gesamtergebnis		6		21112	Füller mit Ersatzfeder (Rechtshänder)	1
11					21113	Füller mit Ersatzfeder (Linkshänder)	1
12					21114	Patronen, blau (6 St.)	2
13					21117	Tintenkiller	2
14					Gesamtergebnis		13

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Und so habe ich es gemacht:

Warennummer und Bezeichnung in das Zeilenfeld gezogen
Bezeichnung für den Kurs einmal in den Berichtfilter und einmal Werte gezogen und die Werte als Summe eingestellt

[Ilonka]

Hallo Atilla, 

vielen Dank für die schnelle Antwort. Das sieht gut aus. Aber wie stellst du das ein, dass er die beiden Positionen unter Zeilen gleichwertig nebeneinander darstellt? Bei mir wird das immer verschachtelt angezeigt, d.h. die eine Position ist der anderen immer übergeordnet und kann mit Klick auf den Kreis vorne aus- bzw. eingeblendet werden (siehe Bildschirmfoto).

Darf ich hier noch eine weitere Frage anhängen? Kann ich die Pivot-Tabelle evtl. über bedingte Formatierung so einstellen, dass eine Position, die nicht in der nebenstehenden Liste auftaucht, farbig markiert wird?

In der nebenstehenden Liste sind die Packeinheiten angegeben, also in welche Tüte welches Material verpackt wird. Wenn dem Kurs nun in der ersten Tabelle ein Artikel zusätzlich zugeordnet wird, muss auch festgelegt werden, in welcher Tüte der mit verpackt werden soll. Falls es da Diskrepanzen gibt, möchte ich das möglichst schnell finden. Ich habe nochmal ein Bildschirmfoto angehängt.

Vielen Dank für die Hilfe!

Lieben Gruß,
Ilonka

[atilla]

Hallo Ilonka,

es ist für die Helfer einfacher, wenn Du statt Bildern immer einen Tabellenausschnitt einstellst, so wie ich es gemacht habe.
In Deinem Fall wäre aber eine Beispielmappe noch besser.

Die Darstellung wie bei mir bekommst Du so hin:
-eine Zelle in der Pivottabelle auswählen
 in der Ribbonleiste erscheinen zwei neue Menüs für die Pivot
-unter Entwurf links Berichtslayout -> in Tabellenformat anzeigen wählen

[
Bild bitte so als Datei hochladen: Klick mich!
]

[Ilonka]

Hallo Atilla,

danke, dass geht ja echt fix hier.


  Testdaten.xlsx (Größe: 47,57 KB / Downloads: 1)

Ich habe die Beispielsmappe mal angehängt. Die Formatierung habe ich jetzt soweit hinbekommen. Über Optionen musste ich noch den Haken bei "Schaltflächen zum Erweitern/Reduzieren anzeigen", rausnehmen. Jetzt sieht sie so aus wie sie soll.

Wenn du noch eine Idee zur bedingten Formatierung hast, um die Pivot-Tabelle mit einer nebenstehenden zu vergleichen, wäre das super. Da die rechte Tabelle auch nur Verweise auf die "Grundtabelle" enthält, muss die Anzahl der Artikel nicht überprüft werden, sondern nur, ob der Artikel in der Packliste überhaupt auftaucht.

Vielen Dank, 
Ilonka

[Ilonka]

Hallo Atilla, 

sorry, für das Dauerposting....  
Ich habe nun nach einigem Probieren und Googlen eine Lösung für die farbliche Markierung gefunden. Ich gehe über "bedingte Formatierung", neue Formatierungsregel, Formel verwenden und nutze folgende Formel: =ZÄHLENWENN($I$5:$I$12;A5)=0 Diese Regel wende ich dann für die gesamte Spalte Warennummer in der Pivot-Tabelle an. Das scheint ganz gut zu funktionieren. Mit Text klappt es leider nicht, d.h. die gleiche Regel in der Spalte Bezeichnung gibt nicht die gewünschten Ergebnisse.

Hier nochmal die Beispieldatei im Anhang mit der bedingten Formatierung.

  Testdaten.xlsx (Größe: 48,6 KB / Downloads: 1)

Lieben Dank für deine Hilfe!
Ilonka

[atilla]

Hallo Ilonka,

das Problem mit der Bedingten Formatierung in der Pivot ist, wenn Du zum Beispiel D1 raus nimmst und D4 rein nimmst, dann geht die Formatierung verloren.

Ich habe lediglich die Möglichkeit per VBA oder neben der Pivot in einer Hilfsspalte zu formatieren..
Dabei darf die Pivottabelle nie über die Hilfsspalte hinausgehen.

Hier die Lösung mit der Hilfsspalte (Deiner Lösung sehr ähnlich)

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
2	D1	(Mehrere Elemente)
3
4	Warennummer	Bezeichnung	Anzahl von D1
5	21101	Schülermäppchen	1
6	21102	Schreiblernbleistift Triplus	1
7	21103	Bleistift HB Standard	1
8	21107	Buntstifte Jumbo Ergo Soft (12 St. Staedtler)	1
9	21110	Wachsmalstifte (8 St. Stockmar)	1
10	21112	Füller mit Ersatzfeder (Rechtshänder)	1
11	21113	Füller mit Ersatzfeder (Linkshänder)	1
12	21114	Patronen, blau (6 St.)	1
13	21117	Tintenkiller	1

Zelle	bedingte Formatierung...	Format
D5	1: =UND(A5<>"";ZÄHLENWENN($I$5:$I$13;A5)=0)	abc

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Ilonka]

Danke Atilla, 

in meiner längeren Liste hatte ich mit meiner Formel doch wieder falsche Ergebnisse. Mit deiner Ergänzung funktioniert es bislang prima.

Vielen Dank für deine Mühe!

Lieben Gruß,
Ilonka

[atilla]

Hallo Ilonka,


ich glaube ich habe Dir das Leben unnötig erschwert.

Die Pivot geht übersichtlicher und einfacher so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
4	Warennummer	Bezeichnung	D1
5	21101	Schülermäppchen	1
6	21102	Schreiblernbleistift Triplus	2
7	21103	Bleistift HB Standard	2
8	21107	Buntstifte Jumbo Ergo Soft (12 St. Staedtler)	1
9	21110	Wachsmalstifte (8 St. Stockmar)	1
10	21112	Füller mit Ersatzfeder (Rechtshänder)	1
11	21113	Füller mit Ersatzfeder (Linkshänder)	1
12	21114	Patronen, blau (6 St.)	2
13	21117	Tintenkiller	2

Zelle	bedingte Formatierung...	Format
A5	1: =UND($A5<>"";ZÄHLENWENN($I$5:$I$13;$A5)=0)	abc

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Ich habe alle oben sichtbaren Spalten einfach in den Zeilenbereich gezogen. Das geht, weil Du ja nur eine Anzeige brauchst und keine Berechnung in der Pivot.
Da es nicht passieren wird, dass die Pivot ganz ohne Daten stehen wird, kannst Du auch die Bedingte Formatierung in die Pivot nehmen.

Ich habe dazu den Bereich A5:C100 markiert, also einen Bereich der über die Daten der Pivot geht. Nimm so viele Zeilen wie nötig sein könnte aber so wenige wie möglich.
Die Formel ist fast die gleiche geblieben. Einzig die Bezüge sind mal absolut mal relativ gesetzt ($ Zeichen). Unbedingt darauf achten.

Teste es bei Dir.

Deine zuletzt eingestellte Datei habe ich dahin gehend angepasst unten eingestellt.
In dieser habe ich noch ein kleines Makro eingebaut. Das Makro siehst du, wenn Du in dem Blatt mit der Pivottabelle einen Rechtsklick auf den Registernamen machst und Code anzeigen wählst.
Das Makro wird automatisch ausgeführt, wenn Du das Baltt mit der Pivot aktivierst. Die Aufgabe ist, die Pivot automatisch zu aktualisieren.

Außerdem habe ich einen benannten Namen in der Mappe. Der Name "pivotdaten" in der Ribbonleiste unter Menü "Formeln" "Namensmanager" zu finden liest dynamisch den Bereich der Datenquelle ein. So kann die Pivot ihre Daten auch dynamisch beziehen. Dazu muss die Quelle geändert werden Wenn Pivot ausgewählt unter Optionen Datenquelle ändern). Bei Tabelle oder Bereich auswählen folgendes eingeben:

=pivotdaten

Damit hast Du im Ganzen eine Flexible Lösung

Falls Du keine Makros einsetzen kannst oder möchtest, dann einfach die Mappe als Excel Mappe ohne Makros .xlsx speichern und die Warnmeldung mit "Ja" bestätigen.
Damit sind die Makros aus der Datei futsch



  Kopie von Testdaten-1.xlsm (Größe: 23,45 KB / Downloads: 0)