Erstellung Besuchsstatistik Außendienst

[Freezer11]

Hallo, 

ich habe aktuell ein Problem, das ich leider nicht lösen kann und zwar geht es dabei um folgenden Sachverhalt:

- Erstellung eines Besuchsplans für den Außendienst, der Kundendaten und die Soll Anzahl der Besuche pro Jahr enthält. (z.B. 8 Besuche pro Kunde pro Jahr geplant sind, daraus folgt ein Besuchsrhythmus von 6,5 Besuchen (52 KWs geteilt durch 8 Soll Besuche pro Jahr). Dieser Kunde müsste also alle 6,5 Wochen besucht werden. 

- Daneben gelegt ist ein Kalender, der auf Kalenderwochen plant. Jede Spalte stellt dabei eine Kalenderwoche dar. 

- Nun soll diese Excel Datei folgendes können:

- Der Außendienst Mitarbeiter gibt beispielsweise ein "X" in seinen Kalender. Mal angenommen, er plant den Kunden in der KW 6 besuchen zu wollen. Er setzt hier also ein "X" in KW 6. Durch welche Formel kann dieser Prozess nun automatisiert werden, sodass das "X" immer um 6,5 Wochen weiter nach rechts geschoben wird (also, sodass das nächste "X" für einen geplanten Besuch in KW 12 oder 13 gesetzt wird). 


Hat jemand für den dargestellten Sachverhalt einen Lösungsansatz parat? Vielen Dank!

[GMG-CC]

Moin,
offensichtlich sind das alles Freiberufler im Außendienst, denn Selbstständige leisten sich meistens keinen Urlaub ... 
Ach ja, Wochenenden und Feiertage gelten natürlich auch nicht; durch die halben Wochen könnte das ja recht oft zutreffen.

Und ich muss sagen, dass sich offensichtlich im Bereich des Außendienstes sehr viel gewandelt hat. Nicht nur dass ich jahrelang keine solchen "Krücken" brauchte, ich konnte sogar durch mein Gespür selber festlegen, wann ei Kunden ein rasches Nachfassen braucht oder vielleicht eine längere "Überlegungsphase" (sprich einfach Ruhe vor mir) haben möchte.

[Rabe]

Hi,

wie Günther schon geschrieben hat, sind 6,5 Wochen Mumpitz, denn erstens verrutscht der Termin dann oft auf das WE (ist also in 2 KW einzutragen) und zweitens hat der Kunde nicht immer dann Zeit, wenn Deine 6,5 Wochen rum sind.

Hier mal ganz primitiv mit einer Formel (Namen in Spalte B, KW ab Spalte C):
I3: =WENN(C3="X";"X";"")

  Besuchstermine.xlsb (Größe: 11,48 KB / Downloads: 4)

[RPP63]

Moin!
Wenn man den Quatsch mit dem x lässt, kann Excel dies durchaus errechnen.
Der Erstbesuch in A2 wird manuell eingegeben:
Tabelle3

	A	B	C	D
1	Besuche 2017	Anzahl Arbeitstage	Feiertage
2	04.01.2017	250	01.01.2017	Neujahrstag
3	16.02.2017	Anzahl Besuche	09.04.2017	Palmsonntag
4	31.03.2017	8	14.04.2017	Karfreitag
5	18.05.2017		16.04.2017	Ostersonntag
6	05.07.2017		17.04.2017	Ostermontag
7	17.08.2017		01.05.2017	Maifeiertag
8	29.09.2017		25.05.2017	Christi Himmelfahrt
9	15.11.2017		04.06.2017	Pfingstsonntag
10			05.06.2017	Pfingstmontag
11			15.06.2017	Fronleichnam
12			03.10.2017	Tag der Deutschen Einheit
13			01.11.2017	Allerheiligen
14			19.11.2017	Volkstrauertag
15			26.11.2017	Totensonntag
16			03.12.2017	1. Advent
17			10.12.2017	2. Advent
18			17.12.2017	3. Advent
19			24.12.2017	4. Advent
20			25.12.2017	1. Weihnachtstag
21			26.12.2017	2. Weihnachtstag

Formeln der Tabelle

Zelle Formel B2 =NETTOARBEITSTAGE("1.1.17";"31.12.17";Feiertage) A3 =ARBEITSTAG(A2;B$2/B$4;Feiertage)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Für die Außendienstler mit einer Sechs-Tage-Woche böten sich die .Intl-Versionen der Funktionen an.

Gruß Ralf

[Rabe]

Hi,

Der Erstbesuch in A2 wird manuell eingegeben

und hier nach Deinem Beispiel für mehr Kunden:

Tabelle3

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Kunde 1	Kunde 2	Kunde 3	Kunde 4	Kunde 5	Kunde 6		Anzahl Arbeitstage		Feiertage			01.01.2017
2	04.01.2017	07.02.2017	01.02.2017	12.01.2017	18.01.2017	03.03.2017		250		01.01.2017	Neujahrstag		31.12.2017
3	16.02.2017	22.03.2017	16.03.2017	24.02.2017	02.03.2017	19.04.2017		Anzahl Besuche		09.04.2017	Palmsonntag
4	31.03.2017	09.05.2017	03.05.2017	10.04.2017	18.04.2017	06.06.2017		8		14.04.2017	Karfreitag
5	18.05.2017	26.06.2017	20.06.2017	29.05.2017	02.06.2017	20.07.2017				16.04.2017	Ostersonntag
6	05.07.2017	08.08.2017	02.08.2017	13.07.2017	19.07.2017	01.09.2017				17.04.2017	Ostermontag
7	17.08.2017	20.09.2017	14.09.2017	25.08.2017	31.08.2017	17.10.2017				01.05.2017	Maifeiertag
8	29.09.2017	06.11.2017	30.10.2017	10.10.2017	16.10.2017	30.11.2017				25.05.2017	Christi Himmelfahrt
9	15.11.2017	19.12.2017	13.12.2017	23.11.2017	29.11.2017	16.01.2018				04.06.2017	Pfingstsonntag
10										05.06.2017	Pfingstmontag
11										15.06.2017	Fronleichnam
12										03.10.2017	Tag der Deutschen Einheit
13										01.11.2017	Allerheiligen
14										19.11.2017	Volkstrauertag
15										26.11.2017	Totensonntag
16										03.12.2017	1. Advent
17										10.12.2017	2. Advent
18										17.12.2017	3. Advent
19										24.12.2017	4. Advent
20										25.12.2017	1. Weihnachtstag
21										26.12.2017	2. Weihnachtstag

verwendete Formeln
Zelle	Formel	Bereich	N/A
H2	=NETTOARBEITSTAGE(M1;M2;Feiertage)
A3:F9	=ARBEITSTAG(A2;$H$2/$H$4;Feiertage)

definierte Namen
Name	Bezieht sich auf	Tabelle	Z1S1-Formel
Feiertage	=Tabelle3!$J$2:$J$21		=Tabelle3!R2C10:R21C10

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

[schauan]

Hallöchen,

nur mal ein paar Gedanken am Rande.

Was der Außendienstler nun genau bei seinen Besuchen macht, steht hier nicht. Ich kann mir durchaus Tätigkeiten vorstellen, wo ein regelmäßiger Turnus einzuhalten ist.
Ob man dann bei Bedarf auch zwischendrin noch was tut und das Einfluss auf die turnusmäßigen Besuche hat, sei mal dahingestellt.
Es muss ja nicht alles wie bei einem selbst funktionieren ....

Bei mir im Outlook habe ich z.B. die Arbeitswoche zur Ansicht eingestellt. Da fehlen die Wochenenden. Kann durchaus sein, dass das beim Fragesteller auch so ist. Könnte man also in die Betrachtung einbeziehen. Was nun mit den Feiertagen passieren soll, sei mal dahingestellt. Ein Pastor oder die Hebamme kommt bestimmt auch an denen ...

Die Aufgabenstellung ist da ja noch einfacher. Es gibt nur eine Wochenübersicht. Da sind Feiertage und Wochenenden egal. Bei Feiertagen muss die Arbeit dann eben auf die anderen Tage verteilt werden. Wie, war ja zum Glück nicht gefragt

Bei uns wird die Grüne Tonne Donnerstags abgeholt. Also regelmäßig jede Woche und nicht nur bei Bedarf. Der Nachteil war nun Himmelfahrt. Da ist die Abholung ausgefallen. Ich habe allerdings heute Nachmittag noch nicht nachgeschaut ...

Die 6,5 Wochen wären übrigens nur dann genau einzuhalten, wenn man die Uhrzeiten mitnimmt. Schließlich hat eine Woche ja eine ungerade Anzahl Tage. Nach einem Termin Montags 14:00 Uhr wäre der Nächste dann an einem Donnerstag früh 02:00 Uhr Ich gehe hier von den 5 Arbeitstagen aus, also hat eine halbe Woche 2,5 Tage. In der Aufgabe stand ja durchschnittlich - ist also auch nicht so eng zu sehen.

So, jetzt mach ich für heute Schluss. Mein Lösungsansatz kommt voraussichtlich Donnerstag, sollte dann auf der in der Aufgabenstellung beschriebenen Form aufsetzen... Ich schaue aber vorher noch in Rabes Lösungsansatz - habe ich jetzt noch nicht getan. Wäre dann auch ein Ansatz mit dem "Quatsch mit dem x" wobei ich den Quatsch daran nicht erkenne ...

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E
1		KW1	KW2	KW3	KW4
2	Meier	x

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

[RPP63]

Moin!

Ich schaue aber vorher noch in Rabes Lösungsansatz

*hüstel*

Wenn täglicher Besuch möglich sein sollte, dann werden die Formeln ja (noch) einfacher:
Ich bleibe mal bei den Nicht-Pfarrern ohne Urlaub. ;)
In B3: =RUNDEN(B1/B2;0)


Tabelle1

	A	B	C	D	E	F	G	H	I	J
1	Anzahl AT	250	250	250		Feiertage		Erstbesuch
2	Anzahl Besuche	8	15	10		01.01.2017	Neujahrstag	04. Jan	18. Jan	02. Feb
3	Durchschnitt	31	17	25		14.04.2017	Karfreitag	KWs
4	Kunde	Müller	Meier	Schulze		17.04.2017	Ostermontag	Müller	Meier	Schulze
5	Erstbesuch	04. Jan	18. Jan	02. Feb		01.05.2017	Maifeiertag	1	3	5
6		16. Feb	10. Feb	09. Mrz		25.05.2017	Christi Himmelfahrt	7	6	10
7		31. Mrz	07. Mrz	13. Apr		05.06.2017	Pfingstmontag	13	10	15
8		18. Mai	30. Mrz	23. Mai		15.06.2017	Fronleichnam	20	13	21
9		05. Jul	26. Apr	30. Jun		03.10.2017	Tag der Deutschen Einheit	27	17	26
10		17. Aug	22. Mai	04. Aug		01.11.2017	Allerheiligen	33	21	31
11		29. Sep	19. Jun	08. Sep		25.12.2017	1. Weihnachtstag	39	25	36
12		15. Nov	12. Jul	16. Okt		26.12.2017	2. Weihnachtstag	46	28	42
13			04. Aug	21. Nov					31	47
14			29. Aug	28. Dez					35	52
15			21. Sep						38
16			17. Okt						42
17			10. Nov						45
18			05. Dez						49

Formeln der Tabelle

Zelle Formel D5 =J2 H5 =KALENDERWOCHE(B5;21) D6 =ARBEITSTAG(D5;D$3;Feiertage) H6 =KALENDERWOCHE(B6;21)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Die Spalten A:G können ausgeblendet werden.
Wem dies zu unübersichtlich ist, kann ja immer noch den "Quatsch" mit den x machen! ;)

Tabelle2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

1

KW

01

02

03

04

05

06

07

08

09

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

2

Müller

x

x

x

x

x

3

Meier

x

x

x

x

x

x

x

4

Schulze

x

x

x

x

x

Formeln der Tabelle

Zelle Formel B2 =WENNFEHLER(WENN(VERGLEICH(B$1;Tabelle1!$H:$H;0);"x");"") B3 =WENNFEHLER(WENN(VERGLEICH(B$1;Tabelle1!$I:$I;0);"x");"") B4 =WENNFEHLER(WENN(VERGLEICH(B$1;Tabelle1!$J:$J;0);"x");"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf

[RPP63]

… und wenn es nur eine Formel in Tabelle2 sein soll:
In B2: =WENNFEHLER(WENN(VERGLEICH(B$1;INDEX(Tabelle1!$H:$J;;ZEILE(A1));0);"x");"")
Formel nach rechts und unten ziehen.

Gruß Ralf

[schauan]

Hallöchen,

hier mal die Lösung nach meinem Ansatz. Eine Zusatztabelle ist nicht nötig.
A1 ist für die Berechnungen hier nicht relevant. In A2 habe ich mir mal erlaubt, die 365/8 in Tagen zu berechnen. Das nutze ich dann bei der Abstandsberechnung.
In B1 und C1 habe ich die Montage der ersten beiden KW eingetragen. In B2 und C2 habe ich per Formel die KW dazu ermittelt und alles so weit wie nötig nach rechts gezogen..
In C3 ! habe ich dann meine Formel für die "Abstandsberechnung" platziert. Die Formel habe ich dann ebenfalls nach rechts gezogen und auch eine Zeile tiefer. In B3 und B4 ist noch nix nötig und eigentlich könnte man generell erst ein paar Wochen später mit den Formeln beginnen .... Da für die Berechnungen der Montag relevant ist - eine Differenzierung des Wochentages war ja nicht vorgesehen -, beginnen die Abstände immer mit der kürzeren Variante (6 Wochen) und anschließend die längere (7 Wochen). Da die 365/8 keine exakt halbe Woche ergibt, gibt es irgendwo weiter hinten dann mal eine Abweichung vom Rhythmus kurz-lang. Durch die Berechnung anhand der "Jahrestage" 365/8 könnte man auch mal ein Schaltjahr berücksichtigen. Aber so genau ... kein Hund Oder einen anderen Besuchsrhythmus ...

Den Beginn einer Besuchsreihe markiert man einfach mit dem x. Daraufhin werden alle folgenden Besuche berechnet. Da durch das x die Formel ersetzt wird, muss man ggf. bei Änderungen die Formel aus einer NAchbarzelle wieder reinkopieren.

Links neben dem erstem Besuch entstehen #NV. Das kann man vermeiden, indem man =WENNFEHLER(...) drum herum bastelt und dann auch in allen Formeln über den gesamten Bereich drin lässt. Könnte ja sein, das irgendwer erst im Dezember anfängt.

Arbeitsblatt mit dem Namen 'Tabelle1 (2)'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2017

02.01.2017

09.01.2017

16.01.2017

23.01.2017

30.01.2017

06.02.2017

13.02.2017

20.02.2017

27.02.2017

06.03.2017

13.03.2017

20.03.2017

27.03.2017

03.04.2017

2

45,625

KW 01

KW 02

KW 03

KW 04

KW 05

KW 06

KW 07

KW 08

KW 09

KW 10

KW 11

KW 12

KW 13

KW 14

3

Meier

x

x

x

4

Müller

#NV

x

x

Zelle	Formel
A2	=365/8
B2	=KALENDERWOCHE(B1;21)
C2	=KALENDERWOCHE(C1;21)
C3	=WENN(D$1<(INDEX($B$1:B$1;;VERGLEICH("x";INDEX($B3:B3;1;0);0);1)+$A$2*ZÄHLENWENN($B3:B3;"x"));"";"x")
C4	=WENN(D$1<(INDEX($B$1:B$1;;VERGLEICH("x";INDEX($B4:B4;1;0);0);1)+$A$2*ZÄHLENWENN($B4:B4;"x"));"";"x")

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

[Freezer11]

Vielen Dank für die ganzen hilfreichen Antworten! 

@schauan: Wie würde die Formel denn aussehen, um das #NV zu vermeiden? 


Im Allgemeinen konnte ich das gut auf mein Beispiel übertragen. Problematisch wird es nur, wenn die Xe dynamisch angelegt werden sollen: 

- Ich habe meine einzelnen Kunden initial mit der Formel von schauan mit einzelnen "X"versehen. Wie aber bekomme ich es jetzt hin, dass wenn beispielsweise der dritte Termin nicht in der KW 15 stattfinden soll, sondern in KW16, die Formel automatisch die nachfolgenden Termine nach der KW 16 auch weiter verschiebt und den fixen Besuchsrhythmus  beibehält? 

Danke Euch und beste Grüße!