Sortierung über mehrere Tabellenblätter

Bild

Betrifft: Sortierung über mehrere Tabellenblätter
von: Guffels
Geschrieben am: 23.09.2015 22:06:54

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

Bild

Betrifft: AW: Sortierung über mehrere Tabellenblätter
von: matthias
Geschrieben am: 24.09.2015 15:08:21
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

Bild

Betrifft: AW: Sortierung über mehrere Tabellenblätter
von: Matthias L
Geschrieben am: 24.09.2015 17:37:03
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

Bild

Betrifft: AW: Sortierung über mehrere Tabellenblätter
von: Guffels
Geschrieben am: 24.09.2015 22:35:17
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 !!!

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Sortierung über mehrere Tabellenblätter"