Office: Soll/Ist Werte - Auswertung für einen bestimmten Bereich/Zeitraum

Guten Abend,

ich bin gerade dabei für meine Arbeit im sozialen Bereich eine Soll/Ist Tabelle für die Auswertung der Betreuungszeiten zu überarbeiten. In meiner bisherigen Tabelle funktioniert die Bereichsauswahl bisher über die Eingabe von Buchstaben (der Spalten), diese werden dann a la

Code:

=SUMME(INDIREKT(C34&"4"&":"&E34&"4"))

mit der Zeilenzahl kombiniert.

Jetzt habe ich auf der Seite http://www.it-service-ruhr.de/tipp/excel-variable-zeitraumvergleiche eine etwas schönere und praktischere Variante gefunden, da man direkt über die Monatsnamen die Auswahl treffen kann. Statt 2011 und 2012 wie in dem Beispiel möchte ich genau so Soll und Ist ausrechnen lassen.

Im Idealfall kann ich dies in meine bisherige Tabelle direkt einbauen, diese listet aber die Werte für jeden Klienten in der Horizontalen auf. Wie kann ich

Code:

SUMME(BEREICH.VERSCHIEBEN($C$2;VERGLEICH($G$1;Monate;0)-1;0):BEREICH.VERSCHIEBEN($C$2;VERGLEICH($G$2;Monate;0)-1;0))

entsprechend anpassen. Je nach Monatsname soll die entsprechende Spalte zu einer fest definierten Zelle herausgesucht werden.



So soll es dann mal ausschauen... die Zeile 2 hilft nur bei der Erstellung der Monatsnamen in Zeile 2 und wird ausgeblendet. Verglichen werden sollen in dem Beispiel die Zeilen 5+7.

Ich freue mich über jeden Tipp und jede Hilfestellung.

Grüße

 

Hallo,

es gibt Seiten im Inder-nett, welche schon lange mal überarbeitet hätten werden müssen. So auch die von Dir gepostete.
Egal...
Was mir jedoch auffällt ist, dass die Datenbasis des geposteten Links sehr gut für die Pivottabelle geeignet ist.
Bitte poste doch mal ne anonymisierte Bsp.-Datei, vielleicht ist diese dann auch geeignet für die Pivottabelle!

 

Hallo,

danke für die Antwort und die erste Info.

Ich habe meine aktuelle Tabelle ohne Namen angehangen.

Zu jedem Namen werden Soll und Ist Zahlen eingetragen. Da diese in verschiedenen Formaten vorliegen (Soll in ganzen Minuten und Ist in hh:mm), rechnet die Tabelle beides in das Dezimalsystem (Bei Soll noch mit einem Faktor von 4,33, da dieser für die Umrechnung von Wochen auf Monatszeiten festgelegt ist) um. Mein Ziel ist es, auf möglichst einfachen und praktischen Weg in der Anwendung die Berechnung für bestimmte Zeiträume auswerten zu lassen. Wie schon eingangs geschrieben passiert das aktuell über die Eingabe der Buchstaben der Spaltenbezeichnung. Ideal wäre es, wie in dem Link und in dem geposteten Bild zu sehen, wenn ich über ein Dropdown Start- und Endmonat auswählen könnte und die Tabelle für diesen Zeitraum die Differenz in hh:mm und % ausspuckt.

Die Monatsnamen sollten automatisch erstellt werden nach Festlegung des Startjahrs. Das habe ich in einer Testtabelle (siehe Bild oben) schon hinbekommen, ebenso die Festlegung eines Namens für den Bereich aller Monate, damit diese dann ausgewählt werden können.

Falls es auf einere andere Art und Weise eleganter und praktischer zu lösen ist, freue ich mich sehr auf eine Hilfestellung und neue Anregungen. :)

Den Anhang Stundenübersicht-Vorlage NEU 2.xlsx betrachten

 

Hallo,

Dein Datenaufbau ist sicher gut, wenn dieser Plan an einer Wand hängt und man diesen per Stift ausfüllen soll.
Jedoch ist er völlig ungeeignet für eine Datenauswertung per Excel, geschweige denn per Pivottabelle.
Kann Dein Erfassungsplan auch anders aussehen oder anders aufgebaut sein?

 

Ja, dass habe ich "befürchtet". ;)

Ich klammere aber keineswegs an dieser Struktur. Wie sähe denn eine sinnvollere Struktur aus? Die Eingabeformate für die Soll und Ist Zeiten, ebenso die Umrechnung müssen so bestehen bleiben, wichtig wäre auch noch eine übersichtliche Auswertung. So das man schnell ersehen kann, dass die Soll-Ist-Zeit bei Klient 1 von Monat A bis Monat X die Differenz Y in hh:mm oder x,xx% hat. Wenn man auch das Startjahr flexibel festlegen und die Start/End-Monate per Dropdown auswählen kann, wäre es das i-Tüpfelchen. :)

Freue mich auf einen excel-konformeren Vorschlag.

 

Hallo,

bei Deinen Wünschen...
welche Ansicht hättest Du gerne?
Die, wie sie unter "Auswertung" steht oder genügt die "Kurzfassung" wie in Spalte A unterhalb der Namen?

 

Guten Abend,

die untere Auswertung besteht seit der ersten Version der Tabelle. Ich finde sie zu unübersichtlich und unpraktisch, da man erst runterscrollen muss. Daher habe ich, als ich auch die Spalte A und die Monatsnamen fixiert habe, die nach oben erstmal "umgeleitet". Das gefällt mir um einiges besser, so hat man immer die Monatsnamen und die Klientennamen beim Rumcrollen in der Tabelle im Auge, ebenso die Auswertung. Ideal fände ich also

Name
Startmonat
Endmonat
Differenz hh:mm
Differenz %

Grüße

 

Hallo,

na da hatte ich gestern doch schon richtig begonnen...:D
Anbei, wie man mit Deiner Datenstruktur dies erstellen "könnte".
Allerdings gibt es in Deiner Datei einen "Fehler", welcher zwar bei negativer Zeit sehr hilfreich ist, jedoch auch zu ungeahnten Ansichten führen kann. Denn Du hast in den Optionen "1904" Datumswerte eingestellt.
Manchmal kommt es vor, wenn eine Datei ohne diesen Wert zuerst geöffnet wurde und dann noch die mit 1904 Werten, dass beim speichern auch die 1904 Datei wieder auf "normal" umgestellt wird und Du dann ganz komische Datumswerte in Deiner Tabelle hast.
Also Vorsicht mit dieser Datumshilfe!

Die Datei ist noch nicht angehübscht, jedoch kommt sie ihrer Aufgabe erstmal nach.
Die neuen Spalten B und C werden nur für die Funktionalität benötig. C nur, da wenn Du nach rechts scrollst, nicht mehr den Auswahlpfeil hättest!:)

Schau mal, ob es so passt.
Ach so...falls Du die Zeiten umgedreht haben willst...muss dies nur in der Formel umgedreht werden.
Und bei Fragen, fragen!

Den Anhang Stundenübersicht-Vorlage NEU 2.xlsx betrachten

 

Hallo,

super, vielen Dank, dass schaut schon mal richtig gut aus... so habe ich mir das vorgestellt. Ich habe jetzt nochmal die erste Zeile gelöscht und die Monatsnamen oben fixiert, damit die beim Scrollen auch immer sichtbar sind.

Was mir aufgefallen ist, das Feld mit der Prozentangabe ist laut Formatierung auf 0,00% und linksbündig eingestellt, aber so wird es nicht angezeigt, was ist die Ursache? Kann man das ändern?

Die Zeit in hh:mm wird nur im positiven Bereich angezeigt, ansonsten bleibt das Feld leer. Minusstunden wären aber schon sehr wichtig. Liegt es bei an der genannten 1904 Einstellung? In meiner alten Tabelle hatte ich bisher keine Probleme mit negativer Zeit und [hh]:mm, so dass auch Zeit über 24h angezeigt wird. Kann man das wieder so einfügen?

Grüße und nochmals vielen Dank für deine bisherigen Mühen... :-)

 

Und irgendwie ist Soll (oben) und Ist (unten) vertauscht... was muss dazu umgestellt werden?

 

Hallo,

Sorry, aber bei mir (XL07) wird es so angezeigt, wie gewollt.

Ja es liegt an der 1904 Einstellung. Nimm doch mal den Haken raus in den Optionen und staune selber!;-)
Die Zelle brauchst Du nur unter "Zelle formatieren" auf "Benutzerdefiniert" umstellen und dort dann [hh:mm] eingeben und so werden auch über 24 Stunden richtig angezeigt.

Und wegen Ist und Soll:
Du musst nur die Formel verdrehen:
=(SUMME(INDEX($A$1:$AM$41;ZEILE()-1;VERGLEICH(A4;$1:$1;0)):INDEX($A$1:$AM$41;ZEILE()-1;VERGLEICH(B4;$1:$1;0)))-SUMME(INDEX($A$1:$AM$41;ZEILE()+1;VERGLEICH(A4;$1:$1;0)):INDEX($A$1:$AM$41;ZEILE()+1;VERGLEICH(B4;$1:$1;0))))/24
in
=(SUMME(INDEX($A$1:$AM$41;ZEILE()+1;VERGLEICH(A4;$1:$1;0)):INDEX($A$1:$AM$41;ZEILE()+1;VERGLEICH(B4;$1:$1;0)))-SUMME(INDEX($A$1:$AM$41;ZEILE()-1;VERGLEICH(A4;$1:$1;0)):INDEX($A$1:$AM$41;ZEILE()-1;VERGLEICH(B4;$1:$1;0))))/24

und bei der anderen ebenso:
=SUMME(INDEX($A$1:$AM$41;ZEILE();VERGLEICH(A4;$1:$1;0)):INDEX($A$1:$AM$41;ZEILE();VERGLEICH(B4;$1:$1;0)))/SUMME(INDEX($A$1:$AM$41;ZEILE()-2;VERGLEICH(A4;$1:$1;0)):INDEX($A$1:$AM$41;ZEILE()-2;VERGLEICH(B4;$1:$1;0)))
in
=SUMME(INDEX($A$1:$AM$41;ZEILE()-2;VERGLEICH(A4;$1:$1;0)):INDEX($A$1:$AM$41;ZEILE()-2;VERGLEICH(B4;$1:$1;0)))/SUMME(INDEX($A$1:$AM$41;ZEILE();VERGLEICH(A4;$1:$1;0)):INDEX($A$1:$AM$41;ZEILE();VERGLEICH(B4;$1:$1;0)))

 

Guten Abend,

vielen Dank für die weiteren Infos. Formeln umdrehen hat geklappt (% war schon richtig rum), ebenso die Zeit auf [hh]:mm umzustellen, dass hatte ich vorher auch schon drin. Die Monatsnamen habe ich umgestellt auf =Datum(), so dass ich einem extra Feld das Startjahr festlegen kann und sich dann alle Monatsnamen entsprechend anpassen. 1904 habe ich auch wieder rausgenommen.

Die Angabe der % klappt auch, sofern ich keine bedingte Formatierung einsetze. Ich wollte ja gerne, die Angabe in % nach unserem System farblich hinterlegen lassen. Aber sobald ich das einbaue, springt die Anzeige wieder auf diese x,xxxxxxxxx Bruchzahl um. Hast du ne Idee, woran das liegen kann?

 

Hallo,

da hilft nur, nochmals ne Bsp.-Datei zu posten!;-)

 

Hallo,

hier meine aktuelle Tabelle samt den genannten Anpassungen. Für die bedingte Formatierung habe ich folgendes Ziel

< 85,00% rot
85,00%-115,00% grün
> 115,00% rot

In meiner ursprünglichen Tabelle hat es so geklappt. Vielleicht findest du die ja die Ursache, die jetzt zu dem anderen Verhalten führt.

Grüße

 

Hallo,

ich hab hier nur XL03 und da funktioniert alles wie gewollt.

Name 1
Jan 2013	Dez 2015
Differenz in hh:mm	04:28
Differenz in %	111,19%
Name 2
Jan 2013	Dez 2015
Differenz in hh:mm	02:45
Differenz in %	117,01%
Name 3
Jan 2013	Dez 2015
Differenz in hh:mm	########
Differenz in %	27,71%
Name 4
Jan 2013	Dez 2015
Differenz in hh:mm	00:04
Differenz in %	100,69%

Zelle	bedingte Formatierung...	Format
B6	1: Zellwert ist kleiner 0,85	abc
B6	2: Zellwert ist kleiner gleich 1,15	abc
B6	3: Zellwert ist größer 1,15	abc
B11	1: Zellwert ist kleiner 0,85	abc
B11	2: Zellwert ist kleiner gleich 1,15	abc
B11	3: Zellwert ist größer 1,15	abc
B16	1: Zellwert ist kleiner 0,85	abc
B16	2: Zellwert ist kleiner gleich 1,15	abc
B16	3: Zellwert ist größer 1,15	abc
B21	1: Zellwert ist kleiner 0,85	abc
B21	2: Zellwert ist kleiner gleich 1,15	abc
B21	3: Zellwert ist größer 1,15	abc

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

Ich kann es heute Abend erst mit XL07 testen!