Excel Beibehaltung des Alters & automatische Berechnung des Alters?
Hallo, ich habe in der Excel folgende Funktion drin :=DATEDIF(B6;HEUTE();"y") (damit sich das Alter automatisch berechnet). Wenn ich jetzt nächstes Jahr die Excel Tabelle öffne ändert sich das alter von der Person automatisch in allen Tabellen. Jedoch möchte ich, dass das Alter zunächst mal fix bleibt und ich in der neuen Tabelle automatisch das neue Alter habe. Ist das Verständlich? Kann mir dabei jemand weiterhelfen?
Liebe Grüße
5 Antworten
Hallo, hier eine weitere Antwort von mir, da man in Kommetare keine Bilder einfügen kann und ich meine erste Antwort nicht mehr verändern/ergänzen kann...
Ich habe hier mal auf die Schnelle ein Beispiel gebaut:
Die rote Hinterlegung wandert für jede Person immer in den entsprechenden Bereich der Tabelle, weil da in allen drei Spalten jeweils eine bedingte Formatierung hinterlegt ist, die das aktuelle Jahr mit dem "Von - Bis" Jahreszahlen vergleicht.
Ich hoffe, das hilft Dir von der Idee her weiter.
Das aktuelle Datum wird anhand einer Formel immer aktuell sein, die Geburtsdaten werden zusammen mit den Personendaten erfasst. Der Rest ist anhand von Formeln berechnet. Du musst also nur den Namen und das Geburtsdatum eingeben so wie die Gehälter in den jeweiligen Altersstufen (wenn die anhand einer Formel berechenbar sind, kannst Du da die zusätzlichen Stufen ab 50 und ab 60 ja auch noch berechnen lassen).
Ich habe mir bei der Altersberechnung jetzt erspart da Formeln zu bauen, die das tagesgenau ausrechnen (anhand des exakten Geburtsdatums) und habe mich einfach auf das Jahr beschränkt um das Prinzip zu zeigen.
Danke das ist sehr sehr ausführlich und gut erklärt. Vielen lieben Dank für deine Bemühungen so mache ich das vermutlich auch! Liebe Grüße :)
Ich habe ja auch schon eine Nachfrage gestellt.
Deine Formel rechnet immer das aktuelle Alter aus. Wenn Du also heute, in einem Jahr, in zehn Jahren die Excel-Datei öffnest, dann wird dort immer das zu dem Zeitpunkt gerade aktuelle Alter angezeigt.
Jetzt vermute ich mal, dass Du mehrere Dateien für eine Person anlegen möchtest (z.B. eine Datei pro Jahr) und dass Du möchtest, das dort immer das zu dem Zeitpunkt gerade aktuelle Alter der Person in der Datei erhalten bleibt? Also so, dass dort immer die gleich Zahl steht, egal wann die Datei danach geöffnet wird (heute, in einem Jahr, in zehn Jahren)?
In dem Fall würde ich tatsächlich eine manuelle Eingabe des Alters verwenden, ist das Einfachste.
Man kann ja trotzdem irgendwo das errechnete Feld mit dem aktuellen Alter einblenden aber das für dieses Formular relevante Alter, das sich nicht ändern soll muss dann eben per Hand eingegeben werden. So bleibt sie fest als Zahl in dem jeweiligen Feld.
OK, ich denke schon.
Ich würde dann wie folgt vorgehen:
- Irgendwo ein Eingabefeld für das Geburtsdatum der Person.
- Ein Feld, dass das aktuelle Datum anzeigt mit "=heute()".
- Entsprechende Spalten in Excel, wo dann berechnet wird ab welchen Jahr die Zuschüsse anfallen (Geburtsjahr + 50 oder so) und die betreffenden Zuschläge berechnet werden. Das kann man ja für alle Zuschlagsstufen machen, unabhängig davon, ob sie bereits für die Person gelten.
- Um zu sehen, welcher Bereich (Spalte) für die Person gerade gültig ist (aufgrund des aktuellen Alters) mit einer bedingten Formatierung (anhand einer Formel, die das aktuelle Datum mit dem Geburtsdatum vergleicht) die richtige Spalte farblich hervorheben.
Wenn du etwas fixieren willst, kannst du die betreffenden Zellen auf sich selbst kopieren und beim Einfügen die Option wählen, nur die Werte einzufügen.
Entweder berechnet sich etwas anhand einer Formel oder der Zellinhalt ist fix. Eine Halbautomation gibt es für Zellen nicht.
Okey, wenn du viel Zeit hast, könntest du dich in VBA einarbeiten.
Das Variable in deiner Formel ist HEUTE. Du könntest etwas mehr Transparenz einbringen, indem du HEUTE() in eine eigene Zelle auslagerst. Da, wo du das Alter fixieren möchtest, markierst du die Zelle und drückst Strg+.
Da, wo das Alter dynamisch errechnet werden soll, lässt du HEUTE() in der Zelle stehen.
Noch ein Hinweis: Du kannst die automatische Anwendung von Formeln deaktivieren. Vielleicht ist das die Lösung für dich.
Formeln → Berechnung → Berechnungsoptionen → Manuell
Damit ich ein Bild einfügen kann eine weitere Antwort von mir.
Mit diesem VBA Makro kannst du die Formeln einer Spalte entfernen und nur den Wert stehen lassen:
Sub DatumFixieren()
Dim zeile
Dim spalte
spalte = 1 ' 1 Steht für A
'das i zählt die Zeilen bis zum Ende der Spalte
For zeile = 1 To Cells(Rows.Count, spalte).End(xlUp).Row
'Falls die Zelle eine Formel hat
If Cells(zeile, spalte).HasFormula = True Then
'wird sie enrsetzt durch den Wert
Cells(zeile, spalte).Value = Cells(zeile, spalte).Value
End If
Next
End Sub
Einfügen kannst du den Quelltext im VBA Editor den man unter Entwicklertools findet oder ALT+F11 drückt. Unter DieseArbeitsmappe in das Textfeld (Allgemein) den Code einfügen, gegebenenfalls den Quelltext anpassen auf die richtige Spalte.
Weiter gehts mit einer Schaltfläche, die Findest du auch unter Entwicklertools im Ribbon Einfügen (Kleine graue Fläche) und dann so wie im Bild die Schaltfläche mit dem Makro verbinden.
Aber ACHTUNG! Das entfernen der Formel führt auch dazu, dass das Alter nicht mehr automatisch berechnet wird und ist nicht umkehrbar (schon aber etwas zu kompliziert).
Woow, vielen Dank ! Das ist jetzt echt viel. Ich muss das mit meiner Chefin nochmals abklären, ob Sie das auch wirklich möchte oder nicht. Sonst würde ich vielleicht auch einfach eine extra Spalte anlegen und dort das aktuelle Alter immer berechnen lassen. Aber vielen lieben Dank. Ich greife drauf zurück, falls ich es anders nicht hinbekomme. Liebe Grüße & einen schönen Tag noch :)
Kleine Sicherheitsabfrage kann nicht schaden. Ergänzung:
Sub DatumFixieren()
If MsgBox("ACHTUNG! Datum wird fixiert", vbOKCancel, "Datum fixieren") = vbCancel Then
Exit Sub
End If
Dim zeile
Dim spalte
spalte = 1 ' 1 Steht für A
'das i zählt die Zeilen bis zum Ende der Spalte
For zeile = 1 To Cells(Rows.Count, spalte).End(xlUp).Row
'Falls die Zelle eine Formel hat
If Cells(zeile, spalte).HasFormula = True Then
'wird sie enrsetzt durch den Wert
Cells(zeile, spalte).Value = Cells(zeile, spalte).Value
End If
Next
End Sub
Super!
Vor allem faszinierend, dass folgende Zeile eine Änderung bewirkt 😅
Cells(zeile, spalte).Value = Cells(zeile, spalte).Value
Stimmt eigentlich sollte man Value in eine Zwischenvariable schreiben, dann die Formel löschen und danach den Wert setzen aber MS hat sich das anders ausgedacht.
Das was im Vergleich zu normalen Variablen ungewohnt ist, ist dass beim Schreiben in das value mehr passiert, als nur in das value zu schreiben, nämlich auch das Löschen der Formel.
zunächst mal fix bleibt und ich in der neuen Tabelle automatisch das neue Alter
Wie lange soll es fix bleiben? Den zweiten Teil des Zitates verstehe ich nicht, sozusagen in der alten Tabelle und was ist dann die neue?
Die Heute() Funktion wird sich jeden Tag ändern. Was ich mir vorstellen kann ist ein Makro das du dann ausführst, wenn du es möchtest.
Es gibt 3-6 Tabellen mit unterschiedlichen Engeltsummen, da sich das jedes Jahr verändert. Es geht darum, dass in einer Tabelle mit 50 Jahren Zuschüsse berechnet werden müssen und dann in 2023 oder so mit dem neuen alter neue Zuschüsse berechnet werden. Es ist so, dass die Funktion für die Berechnung des Alters schon drin war. Das Alter der Person ist momentan 50, aber sobald man in 2 Jahren in die Tabelle reinschaut wird sich das Alter nochmals aktualisieren und somit hat man dann die Berechnungen von vor 2 Jahren nicht mehr. Das Alter in der alten Tabelle aktualisiert sich dann auch. Verständlich?
Mit Makro kenn ich mich leider gar nicht aus. Darüber müsste ich mich informieren. Vielen lieben Dank für deine Antwort :)
Es gibt 3-6 Tabellen mit unterschiedlichen Engeltsummen, da sich das jedes Jahr verändert. Es geht darum, dass in einer Tabelle mit 50 Jahren Zuschüsse berechnet werden müssen und dann in 2023 oder so mit dem neuen alter neue Zuschüsse berechnet werden. Es ist so, dass die Funktion für die Berechnung des Alters schon drin war. Das Alter der Person ist momentan 50, aber sobald man in 2 Jahren in die Tabelle reinschaut wird sich das Alter nochmals aktualisieren und somit hat man dann die Berechnungen von vor 2 Jahren nicht mehr. Das Alter in der alten Tabelle aktualisiert sich dann auch. Verständlich?