Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1360to1364
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

dynamischer Stellenplan

dynamischer Stellenplan
06.05.2014 16:25:15
Kevin
Hallo habe hier eine Beispieldatei (s.u.)
Dort ist zunächst ein Stellenplan hinterlegt. Dieser wird sich dann mit Zu- und Abgängen verändern. Ich möchte nun, dass Excel mir den Stellenplan (die Namen) in Spalte E schreibt der an dem Datum in F1 gültig ist. Also dass er alle Namen, die einem Abgang haben löscht und alle Namen mit einem Zugang hinzufügt.
Habe leider keine Ahnung von VBA. Könnt ihr mir helfen, oder wisst ihr vielleicht sogar wie das ohne VBA möglich ist?
A B C D E F
1 Stellenplan 20.08.2014
2 Name Status Datum Name
3 Müller erfasst 01.08.2014
4 Maier erfasst 01.08.2014
5 Schulze erfasst 01.08.2014
6 Peters erfasst 01.08.2014
7 Krug erfasst 01.08.2014
8 Heimann erfasst 01.08.2014
9 Zusig Zugang 02.08.2014
10 Schmidt Zugang 04.08.2014
11 Maier Abgang 05.08.2014
12 Utog Zugang 06.08.2014
13 Krug Abgang 13.08.2014
https://www.herber.de/bbs/user/90520.xlsx
Gruß,
Kevin

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
mit einer INDEX()-Matrixformel ...
06.05.2014 16:53:01
der
Hallo Kevin,
... nachfolgende Formel nach unten kopieren:
 BCDEF
1Stellenplan   20.08.2014
2NameStatusDatumName 
3Müllererfasst01.08.2014Müller 
4Maiererfasst01.08.2014Maier 
5Schulzeerfasst01.08.2014Schulze 
6Peterserfasst01.08.2014Peters 
7Krugerfasst01.08.2014Krug 
8Heimannerfasst01.08.2014Heimann 
9ZusigZugang02.08.2014Zusig 
10SchmidtZugang04.08.2014Schmidt 
11MaierAbgang05.08.2014Utog 
12UtogZugang06.08.2014  
13KrugAbgang13.08.2014  
14     

Formeln der Tabelle
ZelleFormel
E3{=WENNFEHLER(INDEX(B:B;KKLEINSTE(WENN((C$3:C$99<>"Abgang")*(D$3:D$99<F$1)*(B$3:B$99>0); ZEILE($3:$99)); ZEILE(A1))); "")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
ist doch wohl falsch
06.05.2014 17:16:24
WF
Hi Werner,
ohne jetzt Deine Formel zu analysieren:
In der Ergebnisspalte E stehen jetzt alle Namen (ohne Doppler) unabhängig davon, ob ein Abgang erfolgte.
WF

da hast Du Recht, ....
06.05.2014 17:38:12
der
Hallo WF,
... das hatte ich vorhin nicht beachtet :-(
Mir ist jetzt auf die Schnelle (mein Chef wollte auch parallel noch etwas) nur eine Hilfsspaltenlösung eingefallen.
Nachfolgende Formeln nach unten kopieren (Spalte H kann ausgeblendet werden).
 ABCDEFGH
1 Stellenplan   20.08.2014 Maier
2 NameStatusDatumName  Krug
3 Müllererfasst01.08.2014Müller   
4 Maiererfasst01.08.2014Schulze   
5 Schulzeerfasst01.08.2014Peters   
6 Peterserfasst01.08.2014Heimann   
7 Krugerfasst01.08.2014Zusig   
8 Heimannerfasst01.08.2014Schmidt   
9 ZusigZugang02.08.2014Utog   
10 SchmidtZugang04.08.2014    
11 MaierAbgang05.08.2014    
12 UtogZugang06.08.2014    
13 KrugAbgang13.08.2014    
14        

Formeln der Tabelle
ZelleFormel
H1{=WENNFEHLER(INDEX(B:B;KKLEINSTE(WENN(C$3:C$19="Abgang";ZEILE($3:$19)); ZEILE(A1))); "")}
E3{=WENNFEHLER(INDEX(B:B;KKLEINSTE(WENN(ISTNV(VERGLEICH(B$3:B$19;H:H;))*(D$3:D$19<F$1)*(B$3:B$19>0); ZEILE($3:$19)); ZEILE(A1))); "")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
müsste man aber zu Ende denken
06.05.2014 17:50:45
WF
Maier kann ja am 10.08. ausscheiden und am 16.08. wieder eintreten.
WF

sicherlich, doch ...
06.05.2014 18:22:00
der
Hallo WF,
... da könnte/müsste man evtl.noch anderes beachten.
Hierzu warten jetzt aber erst einmal ab, was der Fragesteller dazu noch einzubringen hat.
Allerdings ich mach für Heute Feierabend, einen schönen Abend Dir noch.
Gruß Werner
.. , - ...

Aufbau nicht optimal
06.05.2014 17:28:59
Christian
Hallo Kevin,
mit diesem Aufbau wirst Du Probleme bekommen!!
Vergib Personalnummern und vergib nur zwei Kategorien im Status, meinetwegen seit und bis..
Da ist die Auswertung einfacher!!
MfG Christian

Anzeige
Vorschlag mit VBA
06.05.2014 18:10:02
Erich
Hi Kevin,
probier mal Option Explicit Sub AnfZuAbPer() Dim oDic As Object, datP As Date, arQ, zz As Long Set oDic = CreateObject("Scripting.Dictionary") With Sheets("Tabelle1") datP = .Cells(1, 6) arQ = .Cells(3, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 2, 3) For zz = 1 To UBound(arQ) If arQ(zz, 3)

Vorschlag mit VBA - next Version
06.05.2014 18:54:24
Erich
Hi Kevin,
die vorige "Kurzfassung" war noch sehr abhängiog davon, dass die Quelldaten richtig sortiert sind.
Dieser Ccode ist wohl unabhängig von der Sortierung:

Option Explicit
Sub AnfZuAbPer2()
Dim oDic As Object, eDic As Object, datP As Date, arQ, zz As Long, arT
Set oDic = CreateObject("Scripting.Dictionary")
Set eDic = CreateObject("Scripting.Dictionary")
With Sheets("Tabelle1")
datP = .Cells(1, 6)
arQ = .Cells(3, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 2, 3)
ReDim arT(1 To 2)
For zz = 1 To UBound(arQ)
If arQ(zz, 3) 
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
Vorschlag mit VBA - 3. Version
07.05.2014 07:23:03
Erich
Hi Kevin,
es geht auch noch etwas einfacher - auch für unsortierte Daten:

Sub AnfZuAbPerZ()
Dim oDic As Object, eDic As Object, datP As Date, arQ, zz As Long, intP As Integer
Dim blnY As Boolean
Set oDic = CreateObject("Scripting.Dictionary")
Set eDic = CreateObject("Scripting.Dictionary")
With Sheets("Tabelle1")
datP = .Cells(1, 6)        ' per-Datum
arQ = .Cells(3, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 2, 3)
ReDim arT(1 To 2)
For zz = 1 To UBound(arQ)
If arQ(zz, 3)  0 Then eDic.Add arT(zz), ""
Next zz
.Columns(4).ClearContents
.Cells(3, 4).Resize(eDic.Count) = Application.Transpose(eDic.Keys)
End With
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Vorschlag mit VBA - 3. Version
07.05.2014 15:53:52
Kevin
Hallo,
ersteinmal vielen Dank für die vielen Antworten. Leider funktioniert es noch nicht so wie ich es mir erhofft hatte.
Wie WF schon gesagt hat gibt es auch Personen die abgehen und später wieder zugehen (z.B. bei Saissonarbeitern). Außerdem sollen neben den Namen auch noch andere Spalten übernommen werden (wie z.B. Abteilung). Es kann passieren dass Hr. Schmidt am 07.08. als Zugang in die Produktion kommt, am 10.08. in die Arbeitsvorbereitung versetzt wird, am 20.08. als Abgang das Unternehmen verlässt und am 25.08. als Zugang wieder in die Produktion zurückkehrt. (Ist natürlich ein Extrembeispiel).
Es müsste also möglich sein den zuletzt genannten Datensatz für eine Person auszugeben, es sei denn es ist ein Abgang, dann diese ganz zu entfernen.
Erich vielen Dank auch dir für deine Mühe, leider kommt bei allen 3 Versionen die selbe Fehlermeldung:
Laufzeitfehler 13: Typen unverträglich
beim debuggen markiert er dann die Zeile:
datP = .Cells(1, 6)
Habe leider keine Ahnung von VBA, um den Fehler selbst zu beheben.
Grüße,
Kevin

Anzeige
Datum in F1
07.05.2014 16:44:47
Erich
Hi Kevin,
Cells(1, 6) ist die Zelle F1 (1. Zeile, 6. Spalte). Darin sollte ein gültiges Datum - das Per-Datum - stehen.
Was steht bei dir in F1?
Vielleicht hast du ja einen Text in F1 stehen, der nur wie ein Datum aussieht.
Schreib mal in irgend eine Zelle die Formel
=ISTTEXT(F1)
Was kommt da raus?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

VBA - 4. Version
07.05.2014 18:06:40
Erich
Hi Kevin,
hier werden jetzt alle Daten (incl. einer "Abteilung") übertragen. Das Datum ist jetzt in K1.
Probier mal:

Option Explicit
Sub AnfZuAbPerZe()
Dim oDic As Object, eDic As Object, datP As Date, arQ, zz As Long
Dim arT, arE(), cc As Long, nn As Long
Set oDic = CreateObject("Scripting.Dictionary")
With Sheets("Tabelle2")
datP = .Cells(1, 11)        ' per-Datum in Zelle K1
arQ = .Cells(3, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 2, 4)
For zz = 1 To UBound(arQ)
If arQ(zz, 4)  "Abgang" Then
nn = nn + 1
For cc = 1 To 4
arE(nn, cc) = arQ(arT(zz), cc)
Next cc
End If
Next zz
.Columns("F:I").ClearContents
.Cells(2, 6).Resize(, 4) = .Cells(2, 1).Resize(, 4).Value2
.Cells(3, 6).Resize(oDic.Count, 4) = arE
End With
End Sub
Und hier eine BeiSpielMappe: https://www.herber.de/bbs/user/90546.xlsm
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: VBA - 4. Version
08.05.2014 09:07:07
Kevin
Sehr gut, das 4. Makro funktioniert genauso wie ich es mir wünschte.
Ich werd jetzt versuchen das auf die Originaldatei anzuwenden. Dort wird auch mit eindeutigen Personalnummern gearbeitet, diese hab ich in der Beispieldatei nur für die bessere Lesbarkeit durch Namen ersetzt.
Tut mir leid, falls ich mich zwischendurch etwas unverständlich ausgedrückt habe, war mein erster Forumseintrag.
Ich melde mich wieder, wenn es auch in der Originaldatei funktioniert.
Noch einmal vielen Dank an alle.
Grüße, Kevin

AW: dynamischer Stellenplan
07.05.2014 13:24:53
Kevin
Hallo,
ersteinmal vielen Dank für die vielen Antworten. Leider funktioniert es noch nicht so wie ich es mir erhofft hatte.
Wie WF schon gesagt hat gibt es auch Personen die abgehen und später wieder zugehen (z.B. bei Saissonarbeitern). Außerdem sollen neben den Namen auch noch andere Spalten übernommen werden (wie z.B. Abteilung). Es kann passieren dass Hr. Schmidt am 07.08. als Zugang in die Produktion kommt, am 10.08. in die Arbeitsvorbereitung versetzt wird, am 20.08. als Abgang das Unternehmen verlässt und am 25.08. als Zugang wieder in die Produktion zurückkehrt. (Ist natürlich ein Extrembeispiel).
Es müsste also möglich sein den zuletzt genannten Datensatz für eine Person auszugeben, es sei denn es ist ein Abgang, dann diese ganz zu entfernen.
Erich vielen Dank auch dir für deine Mühe, leider kommt bei allen 3 Versionen die selbe Fehlermeldung:
Laufzeitfehler 13: Typen unverträglich
beim debuggen markiert er dann die Zeile:
datP = .Cells(1, 6)
Habe leider keine Ahnung von VBA, um den Fehler selbst zu beheben.
Grüße,
Kevin

Anzeige
eindeutige Angaben Deinerseits sind notwendig ...
07.05.2014 16:17:15
der
Hallo Kevin,
... und diese am besten in einer kleinen Excelarbeitsmappe (können ja wieder Dummy-Daten sein). Dein Extrembeispiel "hinkt" bzgl. Deiner Eingangsaussagen, denn danach war das Stichdatum der 20.08.2014 also war er da noch ausgeschieden ... aber ich weiß jetzt was Du meinst.
Allerdings bei Namen wie Schmidt wird mind. zusätzlich ein Vorname notwendig, besser aber gleich mit einer eindeutigen ID (Personalnummer) arbeiten. denn es kann ja auch zwei verschiedene "Kevin Schmidt" geben.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige