Frage von Holger4211, 81

Hilfe bei Exel Tabelle Formeln?

Hallo User,

Ich habe heute mal 3 Fragen gleichzeitig. Folgendes habe ich vor. Ich möchte eine Exel Tabelle erstellen für Ersthelfer. Diese beinhaltet Name:Vorname,Bereich wo er arbeitet und wann seine letzte Auffrischung zum Lehrgang war. Als erstes möchte ich folgendes:

  1. Die Tabelle soll immer das aktuelle Datum haben

  2. eine Bearbeitung der Liste soll angezeigt werden-oder dokumentiert werden am besten im Hintergrund

und

  1. möchte ich einen ständigen Abgleich haben zwischen dem aktuellen Datum und em Datum der letzten Auffrischung auf 2 Jahre und farblich Hinterlegt.

Da bei uns nach 2 jahren immer eine Auffrischung des Lehrganges erfolgt. Ich hoffe es kann mir wer helfen. Vielen Dank im vor raus. Holger4211

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Iamiam, Community-Experte für Excel, 40

1._ aktuelles Datum: =heute(), das ändert sich aber , morgen ist heute dann der nächste Tag. Den Tag der Bearbeitung festhalten kann man ganz schnell mit Strg+Punkt (das geht übrigens auch als Schnelleingabe innerhalb von Formeln, -allerdings muss man dann vorher ein Gänsefüßchen setzen und danach wieder- und mit +Leer+"Strg+:" kann man noch die aktuelle Uhrzeit dranhängen!). Strg+Punkt formatiert gleichzeitig die Zelle auf Datum, allerdings im Format 02.02.2020.

2._ Dokumentation: Schau mal in der xl-Hilfe nach mit Stichwort

Änderungen nachverfolgen

Da wars bei mir gleich der erste Eintrag. (hab das selbst aber noch nie gemacht).

3._ Ablauf dere Gültigkeit der Schulung: Da würde ich noch nicht mal die bedingte Formatierung verwenden (die ist eh noch nicht ausgereift), sondern:

in A1 das Kursdatum (zB 5.2.14)

in B1 die Formel: =EDATUM(A1;24)-HEUTE() und das dann darstellen in

diesem Benutzerdefin. Format: #.##0;[Rot]-#.##0;[Farbe7]!;[schwarz]@

stellst Du dann Schrift Arial Rounded MT Bold, Fett und grün ein,

wird das Format richtig tricky:

die noch gültige Dauer des Kurses wird in Tagen grün angezeigt.

Am Ablauftag erscheint ein magentafarbenes Ausrufezeichen

Abgelaufene Gültigkeiten in Tagen erscheinen rot mit - davor (~überzogen!)

und Texteinträge sind schwarz: alles bestens unterscheidbar, selbst bei flüchtigem Hinsehen! (wählbar bleibt dann aber keine Farbe mehr, aber es gibt ja auch noch Füllfarben, Muster, Rahmen, Schriften, dextreme Sonderzeichen wie ►◄▓ oder♚♛♜♝♞♟➜ oder ✓✔✕✖✗‼ ≋﹏♒⊗ ✇ ⊡ ⊠ ❶ ❷ ❸ ❹ ❺ ❻ ❼ ❽ ❾ ❿ usw, der Phantasie sind da keine Grenzen gesetzt

Du kannst das ! und rot natürlich auch vorverlegen: EDatum(A1;23) ist einen Monat früher zu Ende, EDatum(A1;24)-7 warnt eine Woche vor Ablauf (ich würde sogar schon 2 Monate vorher warnen, das Ganze braucht ja Vorlaufzeit). (auch mit nochmals einem anderen Format ließe sich der Farbwechsel früher einstellen, aber das führt zu weit hier)

Kommentar von Iamiam ,

weils mich selbst nochmal gereizt hat, das hinzukriegen: das Bendef. Format

[grün][>60]#.##0;[Rot][<0]-#.##0;[Farbe7]#.##0;[Schwarz]@

liefert grün für alle Zahlen >60 und -rot für alles <0. 

Farbe7 ist für den Rest, also <=60 und >=0, schwarz für Text

Kommentar von Holger4211 ,

Hallo.

Danke für deine Antwort. Punkt 1 und 2 habe ich hinbekommen.

Mit Punkt 3 habe ich so meine Schwierigkeiten. Aber vielleicht habe ich mich auch nur verkehrt ausgedrückt.

In meiner Tabelle ist in der Spalte E das datum eingetragen wann jeder Mitarbeiter seine letzte Schulung hatte. sind so rund 90 MA.

Da ja alle 2 Jahre die Schulung wiederholt werden soll möchte ich das die MA die in dem Jahr dran sind mit Schulung mir angezeigt werden. Da wir danach unsere Schulungen planen.

Und wenn es dann noch tricky aussieht ist es ja auch gut.

Wenn es möglich ist möchte ich auch das mir die ganze Zeile farblich angezeigt wird mit Name und alles. Muss aber nicht unbedingt.

Und das dann immer fortlaufend wenn ich das Datum beim MA ändere wenn er eine Schulung hatte. Aber das müsste ja mit der Formel problemlos gehen wenn man sie nicht verändert oder löscht.

Kommentar von Ninombre ,

Die Anforderung ist auch nicht ganz klar:

1. Soll gem. der Lösung von Iamiam ermittelt und angezeigt werden, ob die Schulung noch gültig ist? Das ist ja schon ausgiebig als kreative Lösung beschrieben

2. Soll einfach die ganze Zeile farblich markiert werden wenn die Frist von 2 Jahren überschritten ist?

Das wäre eine bedingte Formatierung (nicht Zellenformat!). Den Bereich markieren, der ggf. gefärbt werden soll (A1-E...) und dann neue Regel anlegen (Formel zur Ermittlung... verwenden):
=EDATUM($E1;24)<=HEUTE()
Wenn E1 + 24 Monate kleiner / gleich dem Tagesdatum ist, ist die Schulung fällig und die Zeile wird markiert. 

3. Oder willst Du für die Mitarbeiter, bei denen die Schulung fällig ist, eine eigene Liste erstellen?

Ist per Formel etwas umständlich, aber auch möglich:

In Spalte F brauchst Du die Formel von Iamiam (egal, ob mit oder ohne die spezielle Formatierung), also =EDATUM(E1;24)-HEUTE()

An irgendeine andere Stelle kannst Du dann die Tabelle erstellen, in der nur die Mitarbeiter enthalten sind, die zur Schulung anstehen. Die Formel ist etwas länger...

=WENN(ZÄHLENWENN($F$1:$F$1000;"<0")<ZEILE()-1;"";INDEX($C$1:$C$1000;KKLEINSTE(WENN($F$1:$F$1000<0;1;9999)*ZEILE($F$1:$F$1000);ZEILE()-1);1))

http://www.herber.de/forum/archiv/844to848/847390_Auszug_Liste_aus_einer_Tabelle...

Die Formel fügst Du z.B. in Spalte H2 ein. Beim Verlassen der Zelle noch mit STRG+SHIFT+RETURN bestätigen, da es eine Matrixformel ist. Durch die Tastenkombination entstehen geschweifte Klammern um die Formel (die {} nicht direkt eingeben, sondern wie beschrieben). Dann kannst Du die Formel in die Zeilen darunter kopieren

Nach diesem Schema wird der Wert aus Spalte C übernommen, wenn das Datum um 2 Jahre überschritten ist. Um noch mehr Spalten zu übernehmen, die Formel kopieren und die Stellen mit C entsprechend ersetzen.

Kommentar von Iamiam ,

auf die Schnelle (komm erst So wieder dazu): Wo stehen Deine Mitarbeiter, wo ihre letzte Schulung, kannst Du ggf eine Spalte einfügen und vllt hast Du auch schon i'wo (wo?) die möglichen Schulungstermine stehen (nicht zwingend, könnte man nur vllt verwerten), Du weißt ja, xl braucht genaue Bezüge, auch wenn sich die Anzahl der MA mal ändert. Ggf auch Blattnamen angeben (wenn mehrere involviert sind), damit ichs gleich passgenau formulieren kann.

Kannst ja zB eine Zeile mit einem Dummy-Namen möglichst weit oben in der Liste einfügen, auf den ich mich dann beziehen kann (auch mit Adresse!).

Kommentar von Iamiam ,

Übrigens: obiges Format kann noch eine weitere Stufe differenziert
werden (auch wenn Du jetzt gerade nichts damit anfangen kannst, aber damits bei der AW steht):

anstatt
;[Rot][<0]-#.##0; kannst Du

;[Rot][<30]#.##0; schreiben (also auch ohne -), dann wirds schon ab 30 Tagen (sozusagen dringendst) rot und -(minus) wird bei negativen Zahlen trotzdem dargestellt.

Brauchst du die Vorausschau übrigens für die Etatplanung oder für frühzeitige Anschreiben? Und nicht für 365 Tage, sondern für "in diesem Jahr"? Dann ok.

Ansonsten würde ich Dir trotzdem empfehlen, sich die Formatierung samt Formel näher anzusehen und vllt selbst kreativ zu werden.

Kommentar von Iamiam ,

danke für den *, demnach hats geklappt?

Expertenantwort
von Ninombre, Community-Experte für Excel, 33

1. =heute()

2. Mit Formeln leider nicht zu machen. Dafür muss ein Makro her. Dazu wäre es notwendig, dass Du näher beschreibst, was dokumentiert werden soll.

3. Bedingte Formatierung: Den Bereich markieren, in dem das Daum der letzten Auffrischung steht. Dann neue Regel anlegen > Formel zur Ermittlung...

Wenn der Bereich bspw. in B3 beginnt, wäre die Formel für die bed. Formatierung
=HEUTE()-730>B3

730 = 2*365 Tage. Ich denke, dass ist genau genug, selbst wenn es mal ein Schaltjahr gibt.

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten