Interne Leistungsverrechnung kommunaler Bauhof

[volkmerch]

Hallo liebe Excelgemeinde.

ich habe dieses Thema bereits in einem anderen Forum gepostet wurde aber dafür gesperrt. Ich kann mir nicht erklären warum.

Ich habe ein größeres Projekt vor.

Ich arbeite in einem städtischen Bauhof und wir haben keinerlei Software um unsere Leistungen verrechnen zu können. Stundenlange Recherche im Internet und bei anderen Städten brachten bisher nix.

Ich habe einen Tätigkeitsnachweis erarbeitet, wo meine Stadtarbeiter eintragen wo und was sie für arbeiten ausgeführt haben, ebenfalls wie lange diese Arbeiten gemacht wurden und mit welchem Gerät oder Material.

Die Tätigkeitsnachweise möchte ich erfassen, wenn sie abgegeben werden und die Tabelle soll in einem anderen Blatt die Arbeiten und Stundenanteile Objektmäßig zusammenfassen und auflisten. sodass eine Druckbare Tabelle entsteht, welche ich dann zur Internen Leistungsverrechnung an unsere Hauptverwaltung geben kann.

Die Maschinenverrechnungssätze sind noch nicht ausgereift und sollten anpassbar bleiben, sowie auch die Stundensätze der Stadtarbeiter, da auch hier Veränderungen oder Neueinstellungen möglich sind.

Nach Möglichkeit sollte ich die Leistungen mit einem Auswahlfeld anwählen können, sowie auch die Maschinensätze. Das wird dann zusammengerechnet und bei Auswahl des Objektes dann in dem ersten Tabellenblatt kommuliert.

Hat jemand eine Idee, wie das umzusetzen wäre?

Ich habe mal einen Ansatz von Tabelle im Anhang, aber keinen Ansatz, wie ich die Auswahllisten kommulieren lassen kann in Tabelle 1.

Bin für alle Vorschläge, Hilfe und Kritik dankbar!!!

Ich möchte nicht den Eindruck erwecken, dass ihr mir eine fertige Tabelle bauen sollt. Ich suche nur nach Denkanstößen und Möglichkeiten das so effizient wie möglich umzusetzen.

Danke im Voraus!

LG Christian

[Fennek]

Hallo,

nachdem ich mir die Datei angesehen habe, möchte ich nur eine kleine Anregung geben.

In der beigefügten Mappe ist ein Vorschlag für die Organisation der Daten, die (hoffentlich) die Auswertung vergleichsweise einfach macht.

Viele Felder werden per Auswahl aus einer Liste gefüllt, das verringert Eingabefehler und erleichtert die Auswertung. Die Liste müßten an einer Stelle ("Daten") gepflegt werden.

mfg

(eigentlich wäre das ein Auftrag für einen Dienstleister)

[Rabe]

Hi Christian,

Hat jemand eine Idee, wie das umzusetzen wäre?

hier mal ein Ansatz aufgrund Deiner bisherigen Tabellen für die Blätter Bezüge und Mitarbeiter, es fehlt jetzt noch die Kummulierung (das hat nix mit der Kommune zu tun):

Arbeitsblatt mit dem Namen 'Mitarbeiter A'
	A	B	C	D	E	F	G
1
2	Mitarbeiter A		Fortführung dieser Tabelle bis zum 31.12.
3							=SUMME(D5+(B5*Stundensatz von $A$2))
4	Tag	Anzahl Stunden	Art	Bemerkung	Maschine	Maschinensatz	Gesamtkosten
5	01.01.2016	4,00	Friedhof Ort 1- Heckenschnitt	freier Text	Transporter A	100,00 €	200,00 €
6	01.01.2016	3,50	Friedhof Ort 1- mäharbeiten	freier Text	Transporter A	100,00 €	187,50 €
7	02.01.2016	2,00	Transportarbeiten		LKW A	150,00 €	200,00 €
8	02.01.2016	6,00	Baumpflege/kontrolle		Transporter A	100,00 €	250,00 €

Zelle	Format	Wert
A5	TT.MM.JJJJ	01.01.2016
B5	0,00	4
F5	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
G5	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	200
A6	TT.MM.JJJJ	01.01.2016
B6	0,00	3,5
F6	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
G6	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	187,5
A7	TT.MM.JJJJ	02.01.2016
B7	0,00	2
F7	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	150
G7	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	200
A8	TT.MM.JJJJ	02.01.2016
B8	0,00	6
F8	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
G8	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	250

Name	Bezug
Maschinen	=Tabelle2[Maschine]
Maschinensatz	=Tabelle2[Maschinensatz]
Mitarbeiter	=Tabelle1[Mitarbeiter]
Objekte	=Tabelle3[Objekte]
Stundensatz	=Tabelle1[Stundensatz]

Zelle	Formel
F5	=SVERWEIS(E5;Tabelle2;2;0)
G5	=F5+(B5*SVERWEIS($A$2;Tabelle1;2;0))
F6	=SVERWEIS(E6;Tabelle2;2;0)
G6	=F6+(B6*SVERWEIS($A$2;Tabelle1;2;0))
F7	=SVERWEIS(E7;Tabelle2;2;0)
G7	=F7+(B7*SVERWEIS($A$2;Tabelle1;2;0))
F8	=SVERWEIS(E8;Tabelle2;2;0)
G8	=F8+(B8*SVERWEIS($A$2;Tabelle1;2;0))

Zelle	Gültigkeitstyp	Operator	Wert1	Wert2
A2	Liste		=Mitarbeiter
C5	Liste		=Objekte
E5	Liste		=Maschinen
C6	Liste		=Objekte
E6	Liste		=Maschinen
C7	Liste		=Objekte
E7	Liste		=Maschinen
C8	Liste		=Objekte
E8	Liste		=Maschinen

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

Arbeitsblatt mit dem Namen 'Bezüge'
	A	B	C	D	E	F	G
1	Berechnungsblatt
2
3
4	Mitarbeiter	Stundensatz		Maschine	Maschinensatz		Objekte
5	Mitarbeiter A	25,00 €		Transporter A	100,00 €		Friedhof Ort 1- mäharbeiten
6	Mitarbeiter B	15,00 €		LKW A	150,00 €		Friedhof Ort 1- Heckenschnitt
7	Hans Otto	10,00 €		ca. 40 weitere			Verwaltung - Reinigung
8	Lars Weber	10,00 €					Transportarbeiten
9	Susanne Volker	25,00 €					Sitzungsdienst
10							Kontrollen
11							Baumpflege/kontrolle
12							Grünflächen
13							Winterdienst
14							Sonstiges

Zelle	Format	Wert
B5	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	25
E5	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
B6	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	15
E6	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	150
B7	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	10
E7	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-
B8	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	10
E8	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-
B9	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	25

Name	Bezug
Maschinen	=Tabelle2[Maschine]
Maschinensatz	=Tabelle2[Maschinensatz]
Mitarbeiter	=Tabelle1[Mitarbeiter]
Objekte	=Tabelle3[Objekte]
Stundensatz	=Tabelle1[Stundensatz]

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

Als weitere Anregung würde ich nur eine Datenbank-Liste machen, in der für alle Mitarbeiter und Tage die Daten einfach chronologisch oder durcheinander untereinander aufgeschrieben werden. Diese Datenbank kann dann ganz einfach durch eine PivotTabelle ausgewertet werden:

Arbeitsblatt mit dem Namen 'Datenbank'
	A	B	C	D	E	F	G	H
1	Stundenübersicht
2			Fortführung dieser Tabelle bis zum 31.12.
3								=SUMME(E5+(B5*Stundensatz von $A$2))
4	Tag	Mitarbeiter	Anzahl Stunden	Art	Bemerkung	Maschine	Maschinensatz	Gesamtkosten
5	01.01.2016	Mitarbeiter A	4,0	Friedhof Ort 1- Heckenschnitt	freier Text	Transporter A	100,00 €	200,00 €
6	01.01.2016	Mitarbeiter A	3,5	Friedhof Ort 1- mäharbeiten	freier Text	Transporter A	100,00 €	187,50 €
7	01.01.2016	Mitarbeiter B	4,0	Friedhof Ort 1- Heckenschnitt	freier Text	Transporter A	100,00 €	160,00 €
8	01.01.2016	Mitarbeiter B	3,5	Friedhof Ort 1- mäharbeiten	freier Text	Transporter A	100,00 €	152,50 €
9	02.01.2016	Mitarbeiter A	2,0	Transportarbeiten	freier Text	LKW A	150,00 €	200,00 €
10	02.01.2016	Mitarbeiter A	6,0	Baumpflege/kontrolle	freier Text	Transporter A	100,00 €	250,00 €

Zelle	Format	Wert
H3	@	=SUMME(E5+(B5*Stundensatz von $A$2))
A5	TT.MM.JJJJ	01.01.2016
C5	0,0	4
G5	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
H5	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	200
A6	TT.MM.JJJJ	01.01.2016
C6	0,0	3,5
G6	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
H6	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	187,5
A7	TT.MM.JJJJ	01.01.2016
C7	0,0	4
G7	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
H7	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	160
A8	TT.MM.JJJJ	01.01.2016
B8	TT.MM.JJJJ	Mitarbeiter B
C8	0,0	3,5
G8	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
H8	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	152,5
A9	TT.MM.JJJJ	02.01.2016
B9	TT.MM.JJJJ	Mitarbeiter A
C9	0,0	2
G9	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	150
H9	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	200
A10	TT.MM.JJJJ	02.01.2016
B10	TT.MM.JJJJ	Mitarbeiter A
C10	0,0	6
G10	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	100
H10	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	250

Name	Bezug
Maschinen	=Tabelle2[Maschine]
Maschinensatz	=Tabelle2[Maschinensatz]
Mitarbeiter	=Tabelle1[Mitarbeiter]
Objekte	=Tabelle3[Objekte]
Stundensatz	=Tabelle1[Stundensatz]

Zelle	Formel
G5	=SVERWEIS(F5;Tabelle2;2;0)
H5	=G5+(C5*SVERWEIS(B5;Tabelle1;2;0))
G6	=SVERWEIS(F6;Tabelle2;2;0)
H6	=G6+(C6*SVERWEIS(B6;Tabelle1;2;0))
G7	=SVERWEIS(F7;Tabelle2;2;0)
H7	=G7+(C7*SVERWEIS(B7;Tabelle1;2;0))
G8	=SVERWEIS(F8;Tabelle2;2;0)
H8	=G8+(C8*SVERWEIS(B8;Tabelle1;2;0))
H9	=G9+(C9*SVERWEIS(B9;Tabelle1;2;0))
H10	=G10+(C10*SVERWEIS(B10;Tabelle1;2;0))

Zelle	Gültigkeitstyp	Operator	Wert1	Wert2
B5	Liste		=Mitarbeiter
D5	Liste		=Objekte
F5	Liste		=Maschinen
B6	Liste		=Mitarbeiter
D6	Liste		=Objekte
F6	Liste		=Maschinen
B7	Liste		=Mitarbeiter
D7	Liste		=Objekte
F7	Liste		=Maschinen
B8	Liste		=Mitarbeiter
D8	Liste		=Objekte
F8	Liste		=Maschinen
B9	Liste		=Mitarbeiter
D9	Liste		=Objekte
F9	Liste		=Maschinen
B10	Liste		=Mitarbeiter
D10	Liste		=Objekte
F10	Liste		=Maschinen

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

Arbeitsblatt mit dem Namen 'Auswertung'
	A	B	C
1	Arbeitsleistungen des Bauhofes für die Einrichtungen der
2	Stadtverwaltung für 2016
3
4	Zeilenbeschriftungen	Summe von Anzahl Stunden	Summe von Gesamtkosten
5	Friedhof Ort 1- Heckenschnitt	8	360,00 €
6	Friedhof Ort 1- mäharbeiten	7	340,00 €
7	Transportarbeiten	2	200,00 €
8	Baumpflege/kontrolle	6	250,00 €
9	Gesamtergebnis	23	1.150,00 €

Zelle	Format	Wert
C5	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	360
C6	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	340
C7	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	200
C8	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	250
C9	_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-	1150

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

In der Beispieldatei sind beide Versionen enthalten:

  Innere Verrechnung neu - Rabe.xlsx (Größe: 28,29 KB / Downloads: 16)

[volkmerch]

oha Ralf, das sieht soweit schon gut aus. aber das übersteigt meine Excel-Fähigkeiten wahrscheinlich doch etwas...

PivotTabelle hab ich noch nie gehört.

[Rabe]

Hi Christian,

PivotTabelle hab ich noch nie gehört.

das kriegst Du hin!

Ich habe überall intelligente Tabellen drin, die beliebig erweitert werden können. Es kann nach Erweiterung der Tabellen in "Bezüge" in den Mitarbeiter-Blättern und natürlich der "Datenbank" aus den erweiterten Tabellen ausgewählt werden.

Schaue Dir mal die letzten beiden Blätter "Datenbank" und "Auswertung" (= Pivot) an und spiele damit rum, bzw. gib in die "Datenbank" neue Stunden ein und danach im Blatt "Auswertung" Rechtsklick in der Tabelle - aktualisieren.

[shift-del]

Hallo Christian

Ich arbeite in einem städtischen Bauhof und wir haben keinerlei Software um unsere Leistungen verrechnen zu können. Stundenlange Recherche im Internet und bei anderen Städten brachten bisher nix.

Wirklich?
[Etwas später]
https://www.ekom21.de/Produkte/Bauwesen/...fault.aspx
http://www.infoma.de/de/
https://www.albstadt.de/includes/pdf/anl...mowski.pdf
http://www.o-s-k.de/phpfusion/viewpage.php?page_id=15

[volkmerch]

Hallo Ralf,

ich hab mir die Tabelle heute mal angesehen. Die ist total genial. So hab ich mir das vorgestellt. Dann werde ich jetzt die Anpassungsarbeiten vornehmen und anständig arbeiten können.

Vielen Dank !!!!!! Du bist ein Engel

@ shift-del: Ich kenn e natürlich auch einige dieser Programme und habe auch schon live damit probieren können, jedoch gehen hier einige Tausend Euro Steuergelder rein, bevor es hier richtig eingebunden ist und das will unsere Politik nicht dafür ausgeben.

Ich kann mir also nur mit Excel und co. weiterhelfen.

Wenn man dann hier von Euch solche Hilfe bekommt, ist das echt super.

Vielen Dank nochmal!

LG Christian

[Rabe]

Hi Christian,

ich hab mir die Tabelle heute mal angesehen. Die ist total genial. So hab ich mir das vorgestellt. Dann werde ich jetzt die Anpassungsarbeiten vornehmen und anständig arbeiten können.

Vielen Dank !!!!!! Du bist ein Engel

die Aktualisierung der Auswertung kann auch noch automatisiert werden durch zwei kleine Makros.
Dieses in das VBA-Editor-Fenster, das mit Rechtsklick auf den Tabellenreiter "Auswertung" und "Code anzeigen" erscheint:
Auswertung

Option Explicit 

Private Sub Worksheet_Activate() 
  Call Pivot_aktualisieren 
End Sub 

Dieses in ein allgemeines Modul (Rechtsklick im Projekt-Explorer (linke Spalte) und "Einfügen" - "Modul"):

Option Explicit

Sub Pivot_aktualisieren()
  ' 
  ' Pivot_aktualisieren: alle Pivot einer einzelnen Tabelle 
  ' © 21.09.2011, Ralf 
  ' Aufgerufen mit Worksheet_Activate() im betreffenden Tabellenblatt 
  ' 
  Dim pt As PivotTable
  ' 

     'Alle Pivot-Tabellen eines Tabellenblattes durchlaufen 
     For Each pt In ActiveSheet.PivotTables
        
        'Den PivotCache der PT aktualisieren 
        pt.PivotCache.Refresh
     Next pt
 
End Sub

Außerdem im VBA-Editor bei "Extras" - "Optionen" den Haken setzen bei "Variablendeklaration erforderlich".
   

[volkmerch]

ohh, das klingt gut.

Bei Ausprobieren sagt er mir :

"Fehler beim Kompilieren:

Sub oder Function nicht definiert"

und dann markert er die Zeile

"Private Sub Worksheet_Activate()" gelb an

[Rabe]

Hi Christian,

"Fehler beim Kompilieren:

Hast Du dieses Worksheet_Activate Makro in ein allgemeines Modul oder im Modul des "Auswertung"s-Tabellenblatt eingefügt?
Da das Makro Worksheet_Activate heißt, gehören es zum Arbeitsblatt, in dem es wirken soll.