Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender Navigationstipps
Anzeige
Inhaltsverzeichnis

Sortierung über mehrere Tabellenblätter

Sortierung über mehrere Tabellenblätter
23.09.2015 22:06:54
Guffels
Hallo zusammen,
ich bin gerade dabei einen "Dienstplan" zu erstellen.
Dafür habe ich ein Blatt "Mitarbeiter", in dem die persönlichen Daten der Mitarbeiter eingetragen werden, neue Mitarbeiter eingefügt werden können und die Sortierreihenfolge bestimmt werden kann (diese benötige ich für den Abgleich mit anderen Listen, Datenbanken).
Desweiteren gibt es 12 Blätter für die einzelnen Monate, in denen dann die Dienste, Krank- und Urlaubstage eingetragen werden können.
Im Blatt "Mitarbeiter" gibt es neben den perönlichen Daten einen Auswertebereich, der die Dienste aller Monate, etc. zählt.
Es kann nun sein, dass während des Jahres ein neuer Mitarbeiter hinzukommt oder die Sortierreihenfolge sich ändert. Dann möchte ich natürlich gerne die Mitarbeiter neu sortieren, aber eben mit ihren Diensten, Krank- und Urlaubstagen der einzelnen Monate.
Mehrere Versuche in VBA führten nicht zu dem gewünschten Ergebnis. Ich habe mir schon überlegt, sämtliche Eintragungen der Monate in eine weitere Tabelle zu kopieren, dort zu sortieren, die alten Daten zu löschen und die neuen aus der weiteren Tabelle zurück zu schreiben. Gibt es noch eine einfachere Lösung? Oder evtl. einen grundsätzlich anderen Ansatz beim Tabellen(-blatt)aufbau?
Für Vorschläge wäre ich dankbar.
Ich habe zum besseren Verständis eine Beispieldatei hochgeladen.
https://www.herber.de/bbs/user/100344.xlsm
Gruß Guffels

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sortierung über mehrere Tabellenblätter
24.09.2015 15:08:21
matthias
Hallo Guffels,
um einen neuen Mitarbeiter anzulegen, kannst du bei den Monats-Blättern eine Abkürzung nehmen:
Januar auswählen, Shift gedrückt halten und den Dezember anklicken. Damit sind alle 12 Blätter markiert, wenn du jetzt die Zeile des letzten Mitarbeiter nach unten ziehst und einen neuen Namen vergibst, so geschiet das bei allen Blättern gleichzeitig.
Das Blatt "Mitarbeiter" musst du dann trotzdem noch anpassen.
Wenn du so deine Blätter alle auf dem gleichen Stand hältst, gibt es auch mit deinen Formeln keine Probleme. Dh. ein Mitarbeiter der zwar erst im August hinzukommt, wird trotzdem in allen Monaten angelegt. Dann ergibt das Sortieren auch immer das gleiche Ergebnis, sodass deine Formeln im Blatt "Mitarbeiter" jederzeit korrekt sind.
Deine Datenüberprüfung ob D,K, oder U eingegeben sind, würde ich nicht auf Zellen beziehen, die stören unterhalb der Tabelle nur beim Anlegen von MA. Als Quelle lieber "D;U;K" angeben.
Das Sortieren lässt sich leider nicht durch Gruppieren der Arbeitsblätter abkürzen. Ich habe mir erlaubt, dazu beispielhaft ein Makro anzufertigen.
Für jede Sortieroption die du haben möchtest, legst du zunächst einen Button an (hier Formular-Steuerlement!) und weist ihnen das Makro zu. Jeder Schaltfläche werden am Anfang des Makros Sortierspalten angegeben. SpalteMA gilt für das Blatt Mitarbeiter, Spalte für alle anderen Blätter.
Im Beispiel gibt es die Schaltflächen 1-4 die jeweils Name, D-Tage, K-Tage bzw. U-Tage sortieren.
Ein zweiter Klick auf die Schalfläche ändert die Sortierreihenfolge (ab-/aufsteigend).
Ich bin zuversichtlich, dass du dir den Rest anpassen kannst.
Dim bYesNo As Boolean Sub Sortieren() Dim wks As Worksheet Dim sButtonText As String Dim SpalteMA As Byte, Spalte As Byte bYesNo = Not bYesNo 'auf oder absteigend 'Sortierspalte festlegen If IsError(Application.Caller) = False Then sButtonText = Application.Caller Select Case sButtonText Case "": SpalteMA = 2: Spalte = 1 'Wenn aus VBA-Umgebung heraus aufgerufen Case "Schaltfläche 1": SpalteMA = 2: Spalte = 1 'Name Case "Schaltfläche 2": SpalteMA = 6: Spalte = 33 'D Case "Schaltfläche 3": SpalteMA = 7: Spalte = 34 'U Case "Schaltfläche 4": SpalteMA = 8: Spalte = 35 'K End Select 'Sortieren für jedes TB For Each wks In ThisWorkbook.Worksheets With wks If .Name "Mitarbeiter" Then If bYesNo Then .Range("A:AI").Sort .Columns(Spalte), Order1:=xlAscending, Header:=xlYes Else .Range("A:AI").Sort .Columns(Spalte), Order1:=xlDescending, Header:=xlYes End If Else If bYesNo Then .Range("A:H").Sort .Columns(SpalteMA), Order1:=xlAscending, Header:=xlYes Else .Range("A:H").Sort .Columns(SpalteMA), Order1:=xlDescending, Header:=xlYes End If End If End With Next wks End Sub lg Matthias

Anzeige
AW: Sortierung über mehrere Tabellenblätter
24.09.2015 17:37:03
Matthias
Hallo
Na, ob Deine Zählungen für U,K und D denn stimmen werden, wenn man mal "aus Versehen"
den 29.2 oder den 30.2 oder den 31.2 mit einem der 3 Statuseinträge füllt?
Diese Daten sind ja bei Dir vorhanden und sollten zwingend verändert werden!
Februar

 ADAEAFAGAHAI
1293031Dienst-TageU-TageK-Tage
2DDD300
3UUU030
4KKK003

Formeln der Tabelle
ZelleFormel
AG2=ZÄHLENWENN($B2:$AF2;"D")
AH2=ZÄHLENWENN($B2:$AF2;"U")
AI2=ZÄHLENWENN($B2:$AF2;"K")
AG3=ZÄHLENWENN($B3:$AF3;"D")
AH3=ZÄHLENWENN($B3:$AF3;"U")
AI3=ZÄHLENWENN($B3:$AF3;"K")
AG4=ZÄHLENWENN($B4:$AF4;"D")
AH4=ZÄHLENWENN($B4:$AF4;"U")
AI4=ZÄHLENWENN($B4:$AF4;"K")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Du schreibst, für Vorschläge wäre ich dankbar.
Ok, dann hier ein Vorschlag zum Layout und zu den Formeln:
Feb

 ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1Feb.201512345678910111213141516171819202122232425262728   Dienst-TageU-TageK-Tage
2Mitarbeiter_a DDDDD  KKKKK  UUUUU  DDDDD DUK1055

Formeln der Tabelle
ZelleFormel
A1=DATWERT("1."&TEIL(ZELLE("dateiname";A1); FINDEN("]";ZELLE("dateiname";A1))+1;255)&Mitarbeiter!$B$1)
B1=$A$1+SPALTE()-2
C1=$A$1+SPALTE()-2
D1=$A$1+SPALTE()-2
E1=$A$1+SPALTE()-2
F1=$A$1+SPALTE()-2
G1=$A$1+SPALTE()-2
H1=$A$1+SPALTE()-2
I1=$A$1+SPALTE()-2
J1=$A$1+SPALTE()-2
K1=$A$1+SPALTE()-2
L1=$A$1+SPALTE()-2
M1=$A$1+SPALTE()-2
N1=$A$1+SPALTE()-2
O1=$A$1+SPALTE()-2
P1=$A$1+SPALTE()-2
Q1=$A$1+SPALTE()-2
R1=$A$1+SPALTE()-2
S1=$A$1+SPALTE()-2
T1=$A$1+SPALTE()-2
U1=$A$1+SPALTE()-2
V1=$A$1+SPALTE()-2
W1=$A$1+SPALTE()-2
X1=$A$1+SPALTE()-2
Y1=$A$1+SPALTE()-2
Z1=$A$1+SPALTE()-2
AA1=$A$1+SPALTE()-2
AB1=$A$1+SPALTE()-2
AC1=$A$1+SPALTE()-2
AD1=WENNFEHLER(WENN(MONAT(AC1+1)=MONAT(AC1); $A$1+SPALTE()-2;""); "")
AE1=WENNFEHLER(WENN(MONAT(AD1+1)=MONAT(AD1); $A$1+SPALTE()-2;""); "")
AF1=WENNFEHLER(WENN(MONAT(AE1+1)=MONAT(AE1); $A$1+SPALTE()-2;""); "")
A2=Mitarbeiter!B3
AG2=SUMMENPRODUKT(($B$1:$AF$1<>"")*($B2:$AF2=LINKS(AG$1)))
AH2=SUMMENPRODUKT(($B$1:$AF$1<>"")*($B2:$AF2=LINKS(AH$1)))
AI2=SUMMENPRODUKT(($B$1:$AF$1<>"")*($B2:$AF2=LINKS(AI$1)))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Datei erst speichern und dann neu Öffnen!
Zwischen Jan und Dez dürfen keine anderen Blätter eingefügt werden!
wg. der Formeln
Mitarbeiter

 ABCDEFGH
1Jahr2015   DienstUrlaubKrank
2SortierungName   DUK
326Mitarbeiter_aMitarbeitera1452121

Formeln der Tabelle
ZelleFormel
F3=SUMME(Jan:Dez!AG2)
G3=SUMME(Jan:Dez!AH2)
H3=SUMME(Jan:Dez!AI2)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Hier die Datei zum Experimentieren
https://www.herber.de/bbs/user/100380.xlsm
Gruß Matthias L

Anzeige
AW: Sortierung über mehrere Tabellenblätter
24.09.2015 22:35:17
Guffels
Vielen Dank an die beiden Matthias!
Den Sortiercode habe ich angepasst und er funtktioniert auch.
Ich bin immer wieder überrascht, wie elegant man doch VBA-Codes schreiben kann, wenn man die nötigen Befehle, Eigenschaften kennt...
Ebenso sind auch die Formeln eleganter als meine. Ich wollte zwar im Februar und den Monaten mit 30 Tagen die überflüssigen Zellen ausblenden, aber so ist es natürlich schöner. Auf die Formel mit dem Namen des Arbeitsblattes für den Monatsanfang wäre ich auch niemals gekommen.
Vielen Dank noch mal !!!

372 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige