Translate

Dienstag, 11. November 2014

Exaktes Alter anhand von einem Geburtsdatum ausrechnen (Access/Excel)


Wie ist es möglich die exakte Anzahl von Jahren zwischen 2 Datumsangaben zu errechnen?

Eine Datumsangabe entspricht eine Zahl.
Tag 1 ist der 01.01.1900. Ab da an wird jeder Tag durchgezählt, somit entspricht der 01.02.1900 der 32. Tag, daher ist der Wert von diesem Datum der Wert 32.

Die Berechnung 12.02.2014 - 16.08.1974 ergibt den Wert 14425

Warum? Weil tatsächlich folgendes berechnet wird:
41682 - 27257 = 14425

Was bedeutet nun der Wert 14425?
Es handelt sich um die Anzahl der Tage die zwischen den 2 Datumsangaben liegen.

Und wie viel Jahre liegen nun dazwischen?
Eine Möglichkeit ist, den Wert durch die Anzahl der Tag im Jahr zu teilen, also:
14425 / 365 = 39,52

Unsere Person mit Geburtsdatum 16.08.1974 ist also am 12.02.2014 ca. 39 und halb Jahre alt. Am 01.09.2014 ist die Person 40 Jahre alt, bzw. über 40 Jahre alt.

Leider ist die Teilung durch 365 etwas ungenau, da nicht jedes Jahr 365 Tage hat. 

Eine häufig angewandte Lösung ist das subtrahieren der Jahreswerte:
Jahr(01.09.2014) - Jahr(16.08.1974) = 40
Am 01.09.2014 ist die Person also 40 Jahre alt.

Die gleiche Berechnung vor dem 16.08.1974 ergibt auch 40:
Jahr(01.05.2014) - Jahr(16.08.1974) = 40
Jedoch ist die Person am 01.05.2014 nicht 40, sondern 39 Jahre alt.
Manche Menschen sind dann sehr beleidigt ;-)

Also das subtrahieren der Jahreswerte errechnet nicht das tatsächliche Alter in Jahren, sondern das Alter welches die Person in einem gewissen Jahr wird oder bereits geworden ist.

Um das exakte Alter in Jahren auszurechnen, müssten wir ggf. den Wert 1 von unserer Berechnung abziehen, sollte der Geburtstagstag der Person im aktuellen noch nicht erreicht sein. Dies könnte wiederum mit einer Wenn-Funktion realisiert werden.

Aber warum kompliziert, wenn es doch einfach und effizient geht:

Jahr(12.02.2014 - 16.08.1974)-1900 = 39

Jahr(01.09.2014 - 16.08.1974)-1900 = 40

Dieser Rechnenweg ist allgemein gültig und kann somit sowohl in Excel, Access, Word usw. eingesetzt werden.


Alter in Jahren in einer Access-Abfrage berechnen:
SELECT Year(Date-[GebDatum])-1900 AS [AlterExakt] FROM ...
Dabei ist zu beachten, das das Feld [GebDatum] ein Datumsfeld ist und das Geburtsdatum der Person enthält.

Alter in Jahren mit Excel berechnen:
=Jahr(Heute()-A1)-1900
Dabei ist zu beachten, das in Zelle A1 das Geburtsdatum steht und die Formel-Zelle mit dem  Zahlenformat "Standard" (bzw. kein Datumsformat) formatiert ist.

Übrigens, in Excel findet man oft die Lösung mit der Funktion DATEDIF
DATEDIF(16.08.1974;12.02.2014;"y") liefert tatsächlich 39 und DATEDIF(16.08.1974;01.09.2014;"y") den Wert 40.

Es funktioniert, der Hacken an dieser Funktion ist, das sie undokumentiert ist und somit ist nicht sichergestellt ob diese auch noch in zukünftigen Excel-Version noch existiert.

Also warum auf eine „unsicher“ Funktion zurückgreifen, wenn es dafür einen einfachen Lösungsweg gibt?!

Freitag, 19. September 2014

MS ACCESS: Datenpflege von m:n Datensätze über Kontrollkästchen



Aufgabenstellung:
Zuordnung von Gruppen (Geschäftlich, Freunde, Familie usw...) an Kontaktdaten

Dies wir üblicherweise in MS Access über Haupt- und Unterformular umgesetzt.
Geht es allerdings darum, nur Datensätze ohne weitere Angaben einen Hauptdatensatz zu zuordnen, dann sind Unterformulare nicht wirklich optimal.
Daher stelle ich in diesen Blog-Eintrag eine Lösung mit dynamischen Kontrollkästchen vor.

Klassisch Lösung mit Unterformular:
  • Nur die bereits zugeordneten Einträge werden in der Liste dargestellt
  • VBA-Code wird nicht benötigt
  • Bedienung umständlich und fehleranfällig
  • Jede Zuordnungsliste benötigt ein Unterformular
  • Fazit: Geeignet für Listen mit vielen Einträgen


 Alternative Lösung mit dynamischen Kontrollkästchen:
  • Sämtliche Einträge werden in der Liste übersichtlicht dargestellt
  • Bedienung einfach und eindeutig
  • Standardisiertes Design
  • Nur ein Unterformular
  • Kein externes ActiveX (OCX) wird benötigt
  • VBA-Code wird benötigt (ohne zusätzlichen Verweis)
  • Fazit: Geeignet für Listen mit wenig Einträgen


Das Formular mit Beispiel kann unter folgenden Link heruntergeladen werden:

Voraussetzung:
MS Access 2010, 2007, 2003, 2002, 2000
Es werden 2 Stammdaten Tabellen und eine Zuordnungstabelle benötigt.
Die Stammdaten Tabellen benötigen ein Feld mit eindeutigen Daten (z.B. Primarykey auf ein Feld).
Einer der zwei Stammdaten Tabellen wird in ein Formular in der Formularansicht dargestellt. Zusätzliche Verweise in VBA werden nicht benötigt.

Implementierung:
1.      Das Formular frmACB im aktuellen Projekt importieren
2.      Dieses Formular als Unterformular in das Hauptformular einfügen
3.     Im Hauptformular beim Laden (Form_Load) folgenden Code hinterlegen:

Option Compare Database
Option Explicit
   
DIM frmAktion As Form_frmACB


Private Sub Form_Load()
    Set frmAktion = Me.frmZuordnungAktion.Form
    With frmAktion
        .eLinkMasterFeld = Me.ID 'Erforderlich.

        .eZuordnungstabelle = "tblZuordnungKontaktAktion" 'Erforderlich.
        .eZuordnungsFeld1 = "KontaktID" 'Erforderlich.
        .eZuordnungsFeld2 = "AktionID" 'Erforderlich.
        .eStammtabelle = "tblAktion" 'Erforderlich.
        .eStammFeldID = "ID" 'Erforderlich.
        .eStammFeldBezeichnung = "Bezeichnung" 'Erforderlich.
       
        .eSpaltenBeschriftung = "Aktionen" 'Optional.

        .eStammFormName = "frmAktion" 'Optional.
        .eStammFormSchaltflächenbeschriftung = "Aktionen verwalten..." 'Optional.
        .eAuswahlAlleEinblenden = True 'Optional.
        .eSortierung = NachBezeichnung 'Optional.
        .eSortierrichtung = Aufsteigend 'Optional.
    End With 
End Sub

Hintergrund der Technik:
Das Formular frmACB ist ein Endlosformular und wird als Unterformular in einem Hauptformular eingefügt. Es kann beliebig oft in einem Hauptformular eingefügt werden. Denn welche Datensätze abgebildet werden soll ist nicht in dem Unterformular hinterlegt, sondern wird über das Hauptformular einmalig definiert (Siehe Code oberhalb)

Nach jedem Datensatzwechsel im Hauptformular, wird die entsprechende Datenherkunft im Unterformular frmACB gesetzt.
Die Prozedur RequeryRecords erzeugt eine sql-Abfrage die alle Datensätze der Stammdatentabelle (tblAktion) anzeigt.
Über einen LEFT JOIN wird festgestellt, ob der Datensatz einen zugeordneten Datensatz in der Zuordnungstabelle (tblZuordnungKontaktAktion) hat. Dies muss allerdings nur für den Datensatz geschehen, der im Hauptformular gerade vorhanden ist. Dies wird über eine integrierte Unterabfrage realisiert. Das Feld SELECTED enthält also den Wert True, wenn eine Zuordnung vorhanden ist, ansonsten False.

Die Änderung der Zuordnung kann allerdings nicht über das Kontrollkästchen SELECTED erfolgen, da dies ja ein berechneter Ausdruck ist, also nur zur Anzeige dient. Hier wird ein kleiner optischer Trick verwendet, über das Kontrollkästchen wird ein ungebundenes Kontrollkästchen chkChanger gelegt, welches die Änderungen erfasst.
Die Prozedur SetAssignment führt die Änderung tatsächlich durch.

In der Beispiel-Datei sind die Eigenschaften im Code genauer beschrieben.


Hinweis: Dies ist die neue Version 1.5  mit Ereignis-Übergabe mit folgenden Vorteilen:
  • Die Eigenschaften eZuordnungstabelle und eStammtabelle können jetzt alternativ mit einen SQL-Code befüllt werden, anstatt mit einen Tabellen-/Abfragename
  • Noch einfachere Implementierung (nur eine Prozedur)
  • Das  Formular frmACB liefert Events auf die im Hauptformular reagiert werden kann

Samstag, 22. Februar 2014

MS Excel: Umwandeln von Text in Zahlen per VBA


Wenn Sie in Excel eine Zahl eingeben, können Sie diese auch als Text definieren. Damit Excel aber korrekte Berechnungen vornehmen kann, müssen Zahlen entsprechend als solche deklariert sein. Dies erreichen Sie, in dem Sie den Zellen das Zahlenformat Standard oder Zahl vergeben.

Dies klappt jedoch nicht immer, somit können keine Berechnungen mit diesen Zellen durchgeführt werden, obwohl diese das Zahlenformat Standard zugewiesen wurden.

Z.B. nach dem Import von Daten aus anderen Quellen passiert es häufigen, dass Texte, die aus Ziffern bestehen, wie Zahlen erscheinen aber Excel erkennt sie nicht als Zahl, sondern als Text. 

Eine Möglichkeiten diese wieder in effektive Zahlen umzuwandeln ist das verwenden der integrierten Fehlerüberprüfung (insofern aktiviert).

Gehen Sie dazu wie folgt vor:
1.   Markieren Sie die zu konvertierenden Zellen
2.   Klicken Sie auf das Ausrufezeichen rechts von der Zelle, insofern es erscheint ;-)
3.   Wählen Sie nun den Unterpunkt
"In eine Zahl umwandeln"

 

Leider wird die Konvertierung mit dem Ausrufezeichen oft nicht von Excel angeboten, wo dann andere Lösungswege angewandt werden müssen, wie z.B. alle Zellen mit 1 zu multiplizieren.
Diese Lösungsansätze sind jedoch umständlich und kosten Zeit, vor allem dann, wenn dieser Vorgang sehr häufig gemacht werden muss.
Was liegt also näher als uns dafür eine kleine VBA-Routine zu schreiben, die genau diese Anforderung dauerhaft und zuverlässig löst:

Public Sub TextcellToNumber(Optional TargetRange As Range _
                            , Optional NumberFormat As String = "General")
    Dim rg As Range, tCursor As Long, tStatusBar As String
   
    On Error GoTo ExitProc
    tCursor = Application.Cursor
    Application.Cursor = xlWait
    If Not Application.StatusBar = False Then tStatusBar = Application.StatusBar
    Application.StatusBar = "Please wait until the conversion is completed..."
    Application.ScreenUpdating = False
   
    If TargetRange Is Nothing Then Set TargetRange = Application.Selection
    If TargetRange.CountLarge = 1 Then
        If IsEmpty(TargetRange) Or TargetRange.HasFormula _
           Or Not (TargetRange.Formula = TargetRange) Then GoTo ExitProc
    Else
        Set TargetRange = TargetRange.SpecialCells(xlCellTypeConstants, xlTextValues)
    End If
    On Error GoTo NextRange
    For Each rg In TargetRange
        If IsDate(rg.Value) Then
            rg.NumberFormat = "m/d/yyyy"
            rg = CDate(rg.Value)
            rg.NumberFormat = NumberFormat
        ElseIf IsNumeric(rg.Value) Then
            rg.NumberFormat = NumberFormat
            rg = CDbl(rg.Value)
        End If
NextRange:
    Next

ExitProc:
    Application.StatusBar = tStatusBar
    Application.ScreenUpdating = True
    Application.Cursor = tCursor
End Sub

Nun zur Anwendung dieser Prozedur 
Um in VBA z.B. alle Zellen von der Spalte A der Tabelle1 in Zahlen zu konvertieren, rufen Sie die Prozedur in VBA wie folgt auf:
TextcellToNumber Tabelle1.Range("A:A")
Möchten Sie dabei gleich ein besonderes Zahlenformat (z.B. Tausendertrennzeichen mit 2 Dezimalstellen) umgesetzt haben, dann lautet der Befehl so:
TextcellToNumber Tabelle1.Range("A:A"), "#,##.00"
Möchten Sie alle aktuell markierten Zellen konvertiert haben, dann lautet der Befehl ganz einfach so:
TextcellToNumber Application.Selection

Selbstverständliche läßt sich die Prozedur auch aus dem Applikationsfenster von Excel aufrufen, in dem Sie das Makro auf ein Symbol in der Symbolleiste für den Schnellzugriff legen.
Die Vorgehensweise dazu ist hier erklärt.

Achtung! Damit die Prozedur auch dauerhaft in Ihrem Excel verfügbar ist, sollten Sie die Prozedur in einem Modul in der Datei Personal.xlsb (Version bis 2003 Personl.xls) hinterlegen.

Und noch eine kleine letzte Hürde:
Prozeduren mit Paramater erscheinen leider nicht in der Makro-Befehlsauswahliste.
Dazu tricksen wir das System etwas aus, in dem wir die Parameter kurzfristig entfernen:
  1. Ändern Sie den Kopf der Prozedur wie folgt:
    Public Sub TextcellToNumber()
  2. Nun können Sie das Makro in die Schnellzugriffsleiste hinzufügen.
  3. Anschließend wiederherstellen Sie den Prozedurkopf wie gehabt.
Jetzt sind Sie in der Lage mit einem Mausklick den aktuellen markierten Bereich von Text auf Zahl zu konvertieren.

Sollte Ihnen das alles zu aufwendig sein, dann installieren Sie doch einfach den Shortcut Menu Manager für Microsoft® Excel hier.
Die Funktion steht Ihnen dann direkt auf der Rechten Maustaste zur Verfügung: