Excel - Daten über Blätter automatisch verteilen

Computer: Software & Programmierung Office Software
#1

Hallo,

ich suche eine Möglichkeit, um Text aufgrund bestimmter Bedingungen auf verschiedene Datenblätter kopieren zu lassen.
Zum Beispiel: Namensliste (Spalte A) mit in regelmäßigen Intervallen hinzukommenden „Zusätzen“ (diese kommen dann in Spalte B, C, D - max. 10 Spalten).

Etwa: Der (unkreative) Weihnachtsmann verwaltet Geschenke. Über die Jahre hat er auf dem ersten Datenblatt (komplette Namensliste, dahinter Detailinfo) eine Dokumentation, was die einzelnen Personen erhalten haben. Gleichzeitig hat er (da unkreativ…) ca. 5 weitere Datenblätter, auf denen die Personen gesammelt werden, die in dem betreffenden Jahr die gleichen Geschenke erhalten.

Blatt 1:

Spalte A Spalte B
Anton Buch
Berta Buch
Carl Puppe
Denise Auto
Emil Puppe

Wie kann unser „Beispielweihnachtsmann“ auf dem Datenblatt „Buch“ automatisch eine Liste aller Buchempfänger (auf der Grundlage von Spalte B) erhalten (entsprechend auch für Auto & Puppe).
Dass auf dem Blatt „Buch“ die Namen der Empfänger dann alphabetisch sortiert sein sollen, versteht sich sicher von selbst.

Ich weiß, dass so etwas mit einer Datenbanksoftware sicher sinnvoller umzusetzen ist - nur wäre das dann (aufgrund des Datenimports, der Benutzer der Liste etc.) in der Anwendung / Weiterverwendung komplizierter. Und da Excel die Bedingte Formatierung ermöglicht, sollte das obige Problem doch damit zu lösen sein (oder?).

(Ich würde gern die „Krücke“ - alphabetische Sortierung der Spalte B & dann manuelles Kopieren auf die anderen Datenblätter - vermeiden & suche ein „voll- oder halbautomatisches“ Verfahren…)

#2

Hallo Benuna,

die bedingte Formatierung ist nicht für solch eine Verteilung von Daten geeignet. Sie dient nur zur optischen Hervorhebung der Daten innerhalb eines Tabellenblattes.

Formellösungen sind hier auch nicht so prickelnd, da sie sehr komplex und rechendaufwendig werden.

Ein Werkzeug zur Auswertung von Listen sind Pivottabellenberichte. Allerdings kann man mit Pivottabs keine kompletten Tabellenzeilen übertragen. Leider neigen kreative/unwissende Weihnachtsmänner dazu ihre Dateneingaben so aufzubauen, dass sie für den menschlichen Bertrachter/Bearbeiter gut geeignet sind, aber nicht optimal oder ungeeignet sind für Verwendung dieses Werkzeugs in Excel.

Die Übertragung kompletter Tabellenzeilen in andere Tabellen geht mit dem Spezialfilter (dieser ist aber ggf. kompliziert in der Anwendung) oder per VBA-Makros.
Für dein Problem gibt es prinzipielle Lösungswege per Makro, aber diese müssen immer maßgeschneidert sein auf den Tabellenaufbau. Da kann man ohne genaue Vorgabe keine Lösung vorschlagen. Dass ist dann auch eher was für eine Auftragsprogrammierung oder eine sher intensive Suche hier im Archiv oder anderen Excel-Foren. Für ähnliche Aufgabenstellungen gibt es mit Sicherheit Lösungsansätze, die du verwenden kannst (VBA-Grndkenntnisse vorausgesetzt).

Bei Makros gibt es ggf. das Problem, dass gem. Regelungen in der Firma nicht jeder Anwender Makros programmieren/anwenden darf sondern dass diese aus Gründen der Datensicherheit nur über die IT-Abteilung eingerichtet werden dürfen oder sogar grundsätzlich verboten sind.

Gruß
Franz

#3

Etwa: Der (unkreative) Weihnachtsmann verwaltet Geschenke.
Über die Jahre hat er auf dem ersten Datenblatt (komplette
Namensliste, dahinter Detailinfo) eine Dokumentation, was die
einzelnen Personen erhalten haben. Gleichzeitig hat er (da
unkreativ…) ca. 5 weitere Datenblätter, auf denen die
Personen gesammelt werden, die in dem betreffenden Jahr die
gleichen Geschenke erhalten.

Blatt 1:

Spalte A Spalte B
Anton Buch
Berta Buch
Carl Puppe
Denise Auto
Emil Puppe

Wie kann unser „Beispielweihnachtsmann“ auf dem Datenblatt
„Buch“ automatisch eine Liste aller Buchempfänger (auf der
Grundlage von Spalte B) erhalten (entsprechend auch für Auto &
Puppe).

Hallo Benuna,

in E1 gibst du den Begriff ein, A, B, D, E sind Hilfsspalten, in C
ist das Ergebnis:

Tabellenblatt: [Mappe1]!Tabelle2
 │ A │ B │ C │ D │ E │
--┼------┼------┼------┼---┼-------┼
1 │ Emil │ Emil │ Carl │ 1 │ Puppe │
--┼------┼------┼------┼---┼-------┼
2 │ Carl │ │ Emil │ │ │
--┼------┼------┼------┼---┼-------┼
3 │ │ Carl │ │ 2 │ │
--┼------┼------┼------┼---┼-------┼
4 │ │ │ │ │ │
--┼------┼------┼------┼---┼-------┼
5 │ │ │ │ │ │
--┼------┼------┼------┼---┼-------┼
6 │ │ │ │ │ │
--┴------┴------┴------┴---┴-------┴
Benutzte Formeln:
B1: =WENN(Tabelle1!$B2=$E$1;Tabelle1!$A2;"")
B2: =WENN(Tabelle1!$B3=$E$1;Tabelle1!$A3;"")
B3: =WENN(Tabelle1!$B4=$E$1;Tabelle1!$A4;"")
usw. in B
D1: =WENN(B1"";1;"")
D2: =WENN(B2"";SUMME(D$1:smiley:1)+1;"")
D3: =WENN(B3"";SUMME(D$1:smiley:2)+1;"")
usw. in D
Benutzte Matrixformeln:
A1: {=WENN(ZEILE(A1)\>MAX(D:smiley:);"";INDEX(B:B;
KKLEINSTE(WENN(B$1:B$990"";ZEILE($1:blush:990));ZEILE(A1))))}
A2: {=WENN(ZEILE(A2)\>MAX(D:smiley:);"";INDEX(B:B;
KKLEINSTE(WENN(B$1:B$990"";ZEILE($1:blush:990));ZEILE(A2))))}
A3: {=WENN(ZEILE(A3)\>MAX(D:smiley:);"";INDEX(B:B;
KKLEINSTE(WENN(B$1:B$990"";ZEILE($1:blush:990));ZEILE(A3))))}
usw. in A
C1: {=INDEX(A:A;VERGLEICH(KGRÖSSTE(ZÄHLENWENN(A$1:A$90;"\>="&
A$1:A$90)+99\*ISTZAHL(A$1:A$90);ZEILEN($1:1));ZÄHLENWENN(A$1:A$90;"\>="&
A$1:A$90)+99\*ISTZAHL(A$1:A$90);0))&""}
C2: {=INDEX(A:A;VERGLEICH(KGRÖSSTE(ZÄHLENWENN(A$1:A$90;"\>="&
A$1:A$90)+99\*ISTZAHL(A$1:A$90);ZEILEN($1:2));ZÄHLENWENN(A$1:A$90;"\>="&
A$1:A$90)+99\*ISTZAHL(A$1:A$90);0))&""}
C3: {=INDEX(A:A;VERGLEICH(KGRÖSSTE(ZÄHLENWENN(A$1:A$90;"\>="&
A$1:A$90)+99\*ISTZAHL(A$1:A$90);ZEILEN($1:3));ZÄHLENWENN(A$1:A$90;"\>="&
A$1:A$90)+99\*ISTZAHL(A$1:A$90);0))&""}
usw. in C

(Matrixformeln nicht mit "Enter" sondern mit "Strg+Shift+Enter" eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von Excel erzeugt.)
A1:E6
haben das Zahlenformat: Standard

Gruß
Reinhard

#4

Grüezi Benuna

Wie kann unser „Beispielweihnachtsmann“ auf dem Datenblatt
„Buch“ automatisch eine Liste aller Buchempfänger (auf der
Grundlage von Spalte B) erhalten (entsprechend auch für Auto &
Puppe).
Dass auf dem Blatt „Buch“ die Namen der Empfänger dann
alphabetisch sortiert sein sollen, versteht sich sicher von
selbst.

Sortiere die Quelldaten jeweils im ersten Tabellenblatt

Dann kannst Du die folgende Muster-Datei 1:1 verwenden.

http://users.quick-line.ch/ramel/Files/spezialfilter…

Füge ins erste Tabellenblatt deine Daten ein wie es in den Hinweisen in der Mappe beschrieben wird.

Lösche dann alle Blätter bis auf die ersten beiden.

Füge im Blatt 2 in Zeile 1 deine Spaltenüberschriften aus Blatt 1 1:1 ein, leere den Inhalt der Zeile 2 und gib in der entsprechenden Spalte dein Filter-Kriterium ein.

Kopiere dann Blatt 2 so oft wie benötigt und passe jeweils den Namen des Kriteriums an.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#5

Hallo,
Du willst an der (Daten-)quelel schreiben wo es hinsoll.
Excel will es andersrum.
Am Datenziel steht, wo die Quelle ist,
ggf mit Bedingungen oder Formeln.
Gruss Helmut

#6

Danke an alle!
Ein herzliches Dankeschön für die vielen prompten Anworten.
Ich werd’ denn mal Basteln & Ausprobieren gehen…

#7

Nochmals danke - funktioniert prima.

Vielleicht hast du noch einen Tipp: Einzelne Felder sollen (wenn ein bestimmter Textinhalt, z.B. „Buch“ vorhanden ist), farbig (freundlich grün) markiert werden. Die Bedingte Formatierung übernimmt das (also auf x, y, kombi) dann, wenn man die Formatierung neu „anlegt“ - klickt man raus & wieder rein, verschwindet sie.

(Ich versuche gerade, mir damit zu helfen, auf dem 1. Datenblatt die Felder automatisch markieren zu lassen & dann zu „übertünchen“ - aber so schön ist das nicht…)

#8

Grüezi Benuna

Vielleicht hast du noch einen Tipp: Einzelne Felder sollen
(wenn ein bestimmter Textinhalt, z.B. „Buch“ vorhanden ist),
farbig (freundlich grün) markiert werden. Die Bedingte
Formatierung übernimmt das (also auf x, y, kombi) dann, wenn
man die Formatierung neu „anlegt“ - klickt man raus & wieder
rein, verschwindet sie.

(Ich versuche gerade, mir damit zu helfen, auf dem 1.
Datenblatt die Felder automatisch markieren zu lassen & dann
zu „übertünchen“ - aber so schön ist das nicht…)

Hmmmm wie ist es wenn Du die Bedingte Formatierung im Gesamt-Datenblatt anlegst?
Diese sollte dann eigentlich mit übertragen werden.

Formatierungen in den einzelnen Unter-Blättern passen nicht, da sie beim erneuten Filtern überschrieben werden (mit den Daten aus dem ersten Tabellenblatt).

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#9

Hallo nochmal,

Hmmmm wie ist es wenn Du die Bedingte Formatierung im
Gesamt-Datenblatt anlegst?
Diese sollte dann eigentlich mit übertragen werden.

Nein, so hatte ich es von Anfang an, das funktioniert leider nicht.

Formatierungen in den einzelnen Unter-Blättern passen nicht,
da sie beim erneuten Filtern überschrieben werden (mit den
Daten aus dem ersten Tabellenblatt).

Das dachte ich mir schon - ich hatte nur gehofft, dass einen „Workaround“ gäbe (also irgendwie die Anweisung, dass die Regeln der Bedingten Formatierung NACH Überschreiben angewendet werden sollen).

Nun ja, da die Daten nur einmal angelegt werden & dann einige Jahre recht stabil sind, lässt sich das auch per Hand erledigen.
Vielen Dank nochmal!
Beste Grüsse
Benuna

#10

Grüezi Benuna

Hmmmm wie ist es wenn Du die Bedingte Formatierung im
Gesamt-Datenblatt anlegst?
Diese sollte dann eigentlich mit übertragen werden.

Nein, so hatte ich es von Anfang an, das funktioniert leider
nicht.

Ja, das habe ich inzwischen getestet - die Bedingte Formatierung wird nicht mit übernommen; sie gilt nur in dem Tabellenblatt in dem sie angelegt worden ist (was ja eigentlich auch logisch ist).

Formatierungen in den einzelnen Unter-Blättern passen nicht,
da sie beim erneuten Filtern überschrieben werden (mit den
Daten aus dem ersten Tabellenblatt).

Das dachte ich mir schon - ich hatte nur gehofft, dass einen
„Workaround“ gäbe (also irgendwie die Anweisung, dass die
Regeln der Bedingten Formatierung NACH Überschreiben
angewendet werden sollen).

Nun ja, da die Daten nur einmal angelegt werden & dann einige
Jahre recht stabil sind, lässt sich das auch per Hand
erledigen.

Ggf. könntest Du das Ganze auch als Makro aufzeichnen und dann jeweils nach dem Filtern noch einbauen oder von Hand starten lassen.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#11

Die Bedingte
Formatierung übernimmt das (also auf x, y, kombi) dann, wenn
man die Formatierung neu „anlegt“ - klickt man raus & wieder
rein, verschwindet sie.

Hallo Benuna,

erstze mal die beiden Clear im Code gegen ClearContents

Gruß
Reinhard

#12

Hallo Reinhard,

erstze mal die beiden Clear im Code gegen ClearContents

Leider keine Änderung…

…aber danke für’s drüber Grübeln.
(Hierfür nicht nur ein Sternchen, sondern gleich ein ganzes Sterne-„Blümchen“ :smile:

***
**** ****
***** ******
***** ********
***** ******* ****
** ***** **** ********
****** *** ** *********
******* **** ****
********** „„ ** **** ***
************* „„„„ ******** **
** ******** „„„„ **************
**** **** ** „„ ***********
***** **** ********
********* ** *** ******
******** **** ***** **
**** ******* *****
******** *****
****** *****
„„ **** ****
„„ „„ *** „
„„„ „„„ „„„ „ „
„„„ „„„ „„„ „„„ „„
„„„„ „„„ „„„ „„„ „„„
„„„„ „„„ „„„ „„„„ „„
„„„„„ „„„„„ „„„ „„„„„ „„
„„„„„„ „„„„„„„„„„„„ „„„
„„„„„„„ „„„„„„„„„ „„„
„„„„„„„ „„„„„ „„„„
„„„„„„„„ „„„„„
„„„„„„„„„
„„„