Clever-Excel-Forum

Clever-Excel-Forum > Office-Anwendungen > Excel > Interne Leistungsverrechnung kommunaler Bauhof
Normale Version: Interne Leistungsverrechnung kommunaler Bauhof
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2

volkmerch

11.05.2016, 13:04
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

11.05.2016, 13:51
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

11.05.2016, 14:06
Hi Christian,

(11.05.2016, 13:04)volkmerch schrieb: [ -> ]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'
 ABCDEFG
1       
2Mitarbeiter A Fortführung dieser Tabelle bis zum 31.12.    
3      =SUMME(D5+(B5*Stundensatz von $A$2))
4TagAnzahl StundenArtBemerkungMaschineMaschinensatzGesamtkosten
501.01.20164,00Friedhof Ort 1- Heckenschnittfreier TextTransporter A100,00 €200,00 €
601.01.20163,50Friedhof Ort 1- mäharbeitenfreier TextTransporter A100,00 €187,50 €
702.01.20162,00Transportarbeiten LKW A150,00 €200,00 €
802.01.20166,00Baumpflege/kontrolle Transporter A100,00 €250,00 €

ZelleFormatWert
A5TT.MM.JJJJ01.01.2016
B50,004
F5_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-100
G5_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-200
A6TT.MM.JJJJ01.01.2016
B60,003,5
F6_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-100
G6_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-187,5
A7TT.MM.JJJJ02.01.2016
B70,002
F7_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-150
G7_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-200
A8TT.MM.JJJJ02.01.2016
B80,006
F8_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-100
G8_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-250

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

ZelleFormel
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))

ZelleGültigkeitstypOperatorWert1Wert2
A2Liste =Mitarbeiter 
C5Liste =Objekte 
E5Liste =Maschinen 
C6Liste =Objekte 
E6Liste =Maschinen 
C7Liste =Objekte 
E7Liste =Maschinen 
C8Liste =Objekte 
E8Liste =Maschinen 
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Arbeitsblatt mit dem Namen 'Bezüge'
 ABCDEFG
1Berechnungsblatt      
2       
3       
4MitarbeiterStundensatz MaschineMaschinensatz Objekte
5Mitarbeiter A25,00 € Transporter A100,00 € Friedhof Ort 1- mäharbeiten
6Mitarbeiter B15,00 € LKW A150,00 € Friedhof Ort 1- Heckenschnitt
7Hans Otto10,00 € ca. 40 weitere  Verwaltung - Reinigung
8Lars Weber10,00 €    Transportarbeiten
9Susanne Volker25,00 €    Sitzungsdienst
10      Kontrollen
11      Baumpflege/kontrolle
12      Grünflächen
13      Winterdienst
14      Sonstiges

ZelleFormatWert
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

NameBezug
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'
 ABCDEFGH
1Stundenübersicht       
2  Fortführung dieser Tabelle bis zum 31.12.     
3       =SUMME(E5+(B5*Stundensatz von $A$2))
4TagMitarbeiterAnzahl StundenArtBemerkungMaschineMaschinensatzGesamtkosten
501.01.2016Mitarbeiter A4,0Friedhof Ort 1- Heckenschnittfreier TextTransporter A100,00 €200,00 €
601.01.2016Mitarbeiter A3,5Friedhof Ort 1- mäharbeitenfreier TextTransporter A100,00 €187,50 €
701.01.2016Mitarbeiter B4,0Friedhof Ort 1- Heckenschnittfreier TextTransporter A100,00 €160,00 €
801.01.2016Mitarbeiter B3,5Friedhof Ort 1- mäharbeitenfreier TextTransporter A100,00 €152,50 €
902.01.2016Mitarbeiter A2,0Transportarbeitenfreier TextLKW A150,00 €200,00 €
1002.01.2016Mitarbeiter A6,0Baumpflege/kontrollefreier TextTransporter A100,00 €250,00 €

ZelleFormatWert
H3@=SUMME(E5+(B5*Stundensatz von $A$2))
A5TT.MM.JJJJ01.01.2016
C50,04
G5_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-100
H5_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-200
A6TT.MM.JJJJ01.01.2016
C60,03,5
G6_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-100
H6_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-187,5
A7TT.MM.JJJJ01.01.2016
C70,04
G7_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-100
H7_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-160
A8TT.MM.JJJJ01.01.2016
B8TT.MM.JJJJMitarbeiter B
C80,03,5
G8_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-100
H8_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-152,5
A9TT.MM.JJJJ02.01.2016
B9TT.MM.JJJJMitarbeiter A
C90,02
G9_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-150
H9_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-200
A10TT.MM.JJJJ02.01.2016
B10TT.MM.JJJJMitarbeiter A
C100,06
G10_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-100
H10_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* "-"?? €_-;_-@_-250

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

ZelleFormel
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))

ZelleGültigkeitstypOperatorWert1Wert2
B5Liste =Mitarbeiter 
D5Liste =Objekte 
F5Liste =Maschinen 
B6Liste =Mitarbeiter 
D6Liste =Objekte 
F6Liste =Maschinen 
B7Liste =Mitarbeiter 
D7Liste =Objekte 
F7Liste =Maschinen 
B8Liste =Mitarbeiter 
D8Liste =Objekte 
F8Liste =Maschinen 
B9Liste =Mitarbeiter 
D9Liste =Objekte 
F9Liste =Maschinen 
B10Liste =Mitarbeiter 
D10Liste =Objekte 
F10Liste =Maschinen 
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Arbeitsblatt mit dem Namen 'Auswertung'
 ABC
1Arbeitsleistungen des Bauhofes für die Einrichtungen der  
2Stadtverwaltung für 2016  
3   
4ZeilenbeschriftungenSumme von Anzahl StundenSumme von Gesamtkosten
5Friedhof Ort 1- Heckenschnitt8360,00 €
6Friedhof Ort 1- mäharbeiten7340,00 €
7Transportarbeiten2200,00 €
8Baumpflege/kontrolle6250,00 €
9Gesamtergebnis231.150,00 €

ZelleFormatWert
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:
[attachment=5236]

volkmerch

11.05.2016, 14:19
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. Sad

Rabe

11.05.2016, 14:47
Hi Christian,

(11.05.2016, 14:19)volkmerch schrieb: [ -> ]PivotTabelle hab ich noch nie gehört. Sad

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

11.05.2016, 17:31
Hallo Christian

(11.05.2016, 13:04)volkmerch schrieb: [ -> ]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

12.05.2016, 06:46
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 Wink

@ 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

12.05.2016, 07:21
Hi Christian,

(12.05.2016, 06:46)volkmerch schrieb: [ -> ]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 Wink

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".
[attachment=5245]

volkmerch

12.05.2016, 07:52
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

12.05.2016, 08:00
Hi Christian,

(12.05.2016, 07:52)volkmerch schrieb: [ -> ]"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.
Seiten: 1 2
Clever-Excel-Forum > Office-Anwendungen > Excel > Interne Leistungsverrechnung kommunaler Bauhof