Excel und Diagramme (Expertenfrage)

[Psycon]

Moin!
Ich versuche mal mein Problem richtig rüber zu bringen:

Ich habe eine Excel Datei erstellt, in die man Arbeitszeiten, Pausen und Dienstbesprechungen eintragen kann und dann die noch zu leistenden Stunden sehen kann. Es funktioniert alles so weit prima. (1.Seite)

Nun habe ich für jeden einzelnen Tag (1.-31.) ein Gantt-Diagram erstellt, damit man die Mitarbeiter Überschneidungen sehen kann. Diese platziere ich in 5er Gruppen (Werktage) auf den nachfolgenden Seiten. So komme ich für den Oktober auf 6 Seiten zum ausdrucken. Klappt auch so weit gut.

Da ich aber eine universal Tabelle haben möchte, d.h. für alle Monate anwendbar, habe ich nun ein Problem bei den Diagrammen. Jetzt im Oktober ist quasi das Paradebeispiel. 1.Oktober = Montag. Somit fängt der 1. im Monat mit dem ersten Diagramm an und somit passt es perfekt mit den Werktagen.
Bei meiner jetzigen Tabelle habe ich aber nun folgendes Problem. Wäre der 1. Oktober ein Samstag gewesen, würden die ersten 3 Diagramme leer sein. Ich müsste also erstmal alle Diagramme neu verschieben bzw. neu erstellen damit sie für den Ausdruck später in den richtigen Wochen sind. Bei meinem Beispiel müsste das erste Diagramm den 3. (trotz Feiertag) anzeigen und nicht ein leeres Diagramm für den 1. Im Monat.

Ich suche nun folgendes:

Ich platziere in meiner Formatierung (5 Diagramme pro Seite) die maximale Anzahl an leeren Diagrammen und durch einen Schalter bzw Drop/Down Menü kann ich für den jeweiligen Tag einen Datensatz aussuchen, den das jeweilige Diagramm dann anzeigt...


Ich kann es leider nicht wirklich besser ausführen. Wenn ich dazu komme füttere ich diesen Thread auch noch mit Screenshots um das vielleicht besser verdeutlichen zu können.

Ich danke aber schon mal für die Antworten.

Ach ja noch eine Frage: Gibt es eine maximale Anzahl an Diagrammen pro Tabelle bzw. Datei?

 

[Snooty]

Kannst du nicht einfach nach Tagen arbeiten, also eben 1, 2, 3, anstatt die Wochentage zu verwenden?

Deine Diagramme greifen doch auf irgendwelche Tabellen zu; und in diesen ist doch die Reihe immer dieselbe von 1 bis 28/30/31.

Wenn ich dazu komme füttere ich diesen Thread auch noch mit Screenshots

Eine Beispiel-Tabelle wäre besser.

 

[Psycon]

Hier mal eine kommentierte Tabelle ohne Mitarbeiternamen.

Ich hoffe es ist jetzt verständlicher

 

[Scientist]

Was ich jetzt verstanden habe:
Ziel: 5 Diagramme pro Blatt (1 KW), beginn mit Montag
aktuelles Problem: Diagrammquelle ist Monatstag bezogen mit festen Bezugbereich?

Man koennte mit dem Namensmanager variable Bezuege fuer die Diagramme erzeugen.
Waere aber mit sehr hohen manuellen Aufwand verbunden, weil fuer jeden Bezug eine Variable erstellt werden muesste.
Und man muesste die Bezugsquelle anders aufbauen (alle Tage in eine Spalte oder Reihe).

Ich persoenlich wuerde mir irgendetwas in VBA basteln ...

 

[Psycon]

Leider habe ich keine Ahnung wie man sich etwas mit VBA bastelt...

Ich habe gehofft, dass es eine leichtere Möglichkeit für so was gibt...

 

[Scientist]

Sry, ich hab mich zu sehr auf die Diagramme konzentriert ...
Man kann auch den Monatstag neben den Diagrammen variable gestalten, so dass der erste Eintrag immer dem ersten Montag im Monat entspricht (dazu bspw. neben dem Datum im Ausgangsformular die Wochentage ergaenzen -> Sverweis/Index -> 1. Montag bestimmen).
Die notwendigen Werte fuer die Diagramme dann nachdem selben Schema bestimmen.

Geht also doch recht einfach

 

[Psycon]

Recht einfach......

Naja ich habe mich gerade mittels Youtube Videos in die Welt Index/Vergleich gestürzt, was dem auch ziemlich entgegenkommt was ich suche (inkl. Drop Down Menü). Die ersten Tests sind ganz gut gelaufen. Nur habe ich immer folgendes Problem:

Wert: Datum1 | Datum2| Datum3|...
................. 01.01.18| 02.01.18 | 03.01.18|...
..................04.01.18| 05.01.18|...

Wenn die Überschrift (z.B. Datum1) nur eine Spalte zur Abfrage hat, bekomme ich das hin, indem ich mit dem Namensmanager die Bereiche markiere. Aber ich habe pro Überschrift 2 Abfragespalten für das spätere Gantt-Diagramm.

Wert: Datum1 |....................| Datum2|.....................| Datum3|...
..................01.01.18| 02.01.18 | 03.01.18| xx.xx.xx |xx.xx.xx
..................04.01.18| 05.01.18 | xx.xx.xx | xx.xx.xx |xx.xx.xx

Da nimmt er dann nämlich immer nur eine Spalte, was ein falsches Diagramm dann ergibt.

Das ist die Formel, die ich bisher so abgeguckt habe.

=INDEX(Original!$AV$4:$BG$11;0;VERGLEICH(Original!$C$38;Original!$AV$3:$BG$3;0))

Per Index lege ich ja den Datenbreich fest. Ich habe aber bisher nicht ganz verstanden wie das mit der Auswahl der Zeilen und Spalten geht. Die 0 sagt ja aus, dass in der Spalte alle Zeilen genommen werden sollen. Kann man aber auch irgendwie sagen, das er alle Zeilen von Spalte 1 und 2 für ein Datum nehmen soll?

Man kann auch den Monatstag neben den Diagrammen variable gestalten, so dass der erste Eintrag immer dem ersten Montag im Monat entspricht (dazu bspw. neben dem Datum im Ausgangsformular die Wochentage ergaenzen -> Sverweis/Index -> 1. Montag bestimmen).
Die notwendigen Werte fuer die Diagramme dann nachdem selben Schema bestimmen.

SVERWEIS ist schon zu lange her, dass ich das noch was vernünftiges zusammen bekomme... Müsste ich mich auch erst wieder einlesen

Und 100%ig wäre das dann glaub ich auch nicht. Wenn der 1. Arbeitstag im Monat auf einen z.B. Dienstag fällt, würde nach meinem Verständis Das Diagramm ab dem kommenden Montag anfangen.

 

[Scientist]

Und 100%ig wäre das dann glaub ich auch nicht. Wenn der 1. Arbeitstag im Monat auf einen z.B. Dienstag fällt, würde nach meinem Verständis Das Diagramm ab dem kommenden Montag anfangen.

Das ist jetzt Frage der Definition.
Was soll ab wann im neuen Monat dargestellt werden bzw. bis was im alten Monat.

Wenn die Überschrift (z.B. Datum1) nur eine Spalte zur Abfrage hat, bekomme ich das hin, indem ich mit dem Namensmanager die Bereiche markiere. Aber ich habe pro Überschrift 2 Abfragespalten für das spätere Gantt-Diagramm.

Ich bin mir unsicher, was du meinst ...
Meine Idee war jedenfalls, den Starttag im Monat zu berechnet (die rote 1).
Die einfache Variante waere es dazu irgendwo, in einer Matrix, Datum und Wochentag zu verknuepfen und dann ueber SVerweis oder Index/Vergleich den gewuenschten Starttag zu ermitteln.
Bspw. koenntest du in Spalte A die Tageszahlen in ein Datum umwandeln und ueber die Formatierung nur den Tag darstellen lassen.
-> Datum("Jahr"; "Monat"; "Tag") -> Datum(Jahr(Heute());10;1) -> Ausgabe: "43374" bzw. "01.10.2018"
Das Datum muesste dann natuerlich im Monat und Jahr variabl sein (bspw. ueber Jahr(Heute())), damit das Datum immer richtig ist.

Neben dem Datum koennte man eine neue Spalte einfuegen (falls unerwuenscht ausblenden) in der mit Text() auf Spalte A verwiesen wird.
-> Text("Wert"; "Formatierung") -> Text(43374; "TTT") -> Ausgabe: "Mo"

Anschließend kann der Starttag als Basis fuer einen SVerweis genutzt werden, um die Werte fuer die Diagramme zu ermitteln (die gruenen Werte).

Dazu suchen wir den Tag und lassen uns die dazugehoerigen Werte des Mitarbeiters ausgeben.
Jeder notwendiger Wert muss separat gesucht.

SVERWEIS ist schon zu lange her, dass ich das noch was vernünftiges zusammen bekomme... Müsste ich mich auch erst wieder einlesen

Index()/Vegleich():
Vergleich() vergleicht einen Wert mit einer Matrix und gibt bei einem Treffer den Index in der Matrix wieder.
Anhand des Indexes koennen wir mit Index() den Wert in einer anderen, vergleichbar aufgebauten Matrix, wiedergeben.
Dabei ist es egal wo bzw. wo zueinander die Matrizen sich befinden, solang der Aufbau gleich ist.

SVerweis():
Hier wird ein Wert in der 1. Spalte einer Matrix gesucht und bei einem Treffer dann der Wert in der gleichen Zeile aus Spalte X wiedergegeben.
-> SVerweis("Suchkriterium"; "Matrix"; "Spaltenindex"; "WAHR/FALSCH")
-> SVerweis(1; A4:AG34; 30; "FALSCH") -> Ausgabe: 7
Gesucht wurde die Zahl 1 in der 1. Spalte der Matrix "A4:AG34", also in "A4:A34"
Wenn vorhanden, soll der Wert in der 30. Spalte dieser Matrix ausgegeben werden (A = 1; ...; AD= 30).
In Zelle A4 wurde die 1 gefunden und ausgegeben wird der Wert in Zelle AD4 (7).
SVerweis() ist kann also per Definition nur Werte rechts vom gesuchten Wert ausgegeben, fuer alles andere brauch man also eine Alternative (bspw. Index()/Vergleich()).
WVerweis() funktioniert genauso, nur halt von "oben nach unten".

Wahr: ungenaue Suche, der "naechstpassende Wert" wird genommen
Falsch: genaue Suche, nur der exakte Suchbegriff wird genommen

 

[Psycon]

So nach viel rumprobieren habe ich es geschafft. Wenn ich fertig bin lade ich die letzte Version nochmal hoch.

War auf jeden Fall ein sehr lehrreiches Projekt

 

[Psycon]

So hab. Nun die fertige Tabelle. Ich habe die Lösung mittels "Index+Vergleich" hinbekommen. War aber ganz schön frickelich, hat am Ende aber gut geklappt.

 

[Scientist]

Okay ...
Du hast dich doch fuer den harten Weg ueber den Namensmanager entschieden.
Ja, das war sicherlich nervig.

Ansonsten kleiner Hinweis:

Blende nichts ueber weiße Schrift oder die Formatierung aus.
Wenn du nicht willst, dass die Hilfszellen jemand sieht,
blend die Zeilen/Spalten aus oder schieb besser die Hilfszellen auf ein anderen Blatt.
So koennen Zellen einfach mal ueberschrieben werden, weil sie "leer" aussehen.

Und vielleicht noch interessant:

Es gibt eine Funktion namens Nettoarbeitstage(), damit sparst du dir das Zaehlen der Werktage im Monat.

Die Wochenendeinfaerbung kannst du auch ueber die Bedingte Formatierung erledigen.
Vorrausgesetzt in Spalte A sind die korrekten Datumswerte hinterlegt.
Feiertage ebenso, wenn diese irgendwo aufgelistet sind.
Ostern und Co. lassen sich auch berechnen (einfach mal googlen).

Isokalenderwoche()