[Excel 97- Menü aufrufen]

Duplikate, Datum und Uhrzeit in Excel



Weiterführend:


 Tips & Tricks für Excel (Hier Erweitert )


 Zurück zu den GRUNDLAGEN



Inhalt:




Wie Datum und Uhrzeit in Excel verarbeitet werden, hängt entscheidend vom Zellenformat ab



^ Zellenformate


Es gibt prinzipiell Text, Uhrzeit, Zahl, sowie Währung, Prozent, und benutzerdefinierte Unterformatierungen über

Format / Zellen / Zahlen / Benutzerdefiniert ( am Ende der Liste ),

wo per Hand beliebige eigene Formate kodiert werden können; auch für Datumsformat, auch ohne Punkt, mit Leerzeichen, Kommata, etc. etc... Textvorgabe dabei in Anführungszeichen setzen.


Es gilt für benutzerdefinierte Formate:



- allerdings kann Excel mit diesem Wert nicht weiter rechnen; das geht nur bei global benutzerdefinierten Zellenformaten.









^ Datum:


Datum mit Jahresangabe:


Achtung: "Jahr" bis 29 gilt als 2029,

ab 30 als 1930;

Fehler werden als Text interpretiert.






^ Exkurs: Kalenderwoche


Normalerweise sollte =KALENDERWOCHE(HEUTE()) die aktuelle Kalenderwoche auswerfen,
oder =KALENDERWOCHE(A1) - wenn die Zelle A1 mit =HEUTE() das aktuelle Datum anzeigt.

Das Ergebnis entspricht jedoch nicht immer der DIN 1355, in der die erste Woche des Jahres ist definiert als die Woche, in die mindestens 4 Tage fallen.


Die Formel dazu lautet:

=KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)&". KW"

(Gefunden bei Herber)





^ Exkurs: Datum fixieren


In Excel wird das aktuelle Datum normalerweise mit

=HEUTE()

generiert, mit Uhrzeit als

=JETZT()

 -> Allerdings ist dieses immer das aktuelle Datum und die aktuelle Zeit, d. h. die Einträge werden bei jeder Öffnung der Datei ( oder mit F9 ) neu aktualisiert. Damit geht jede Aussage über das Datum eines Eintrages verloren.

 -> Unveränderlich werden das Datum manuell über die Tastatur mit [ Strg ] + ". und die Zeit mit  [ Strg ] + [ Umschalt ]  + ". ( entspricht [ Strg ] + ": )

 -> Ein festes Datum einzugeben wäre also möglich, indem man in einer dafür vorgesehenen Spalte auf diese Art das Datum einträgt, während man einen neuen Datensatz manuell eingibt - und es nicht vergißt ( dagegen hilft eine Voreinstellung mit dem Wort "DATUM!" in rot in jeder dazu vorgesehehen Zelle ).

Will man ein unveränderliches Datum automatisiert eingeben lassen, gibt es zwar eine MöglichkeitSie verlassen diese Seiten, aber sie erfordert das Blockieren der automatischen Aktualisierung durch eine eigentlich verbotenen Selbstbezug in der Formel


=WENN(A1="";"";WENN(B1="";JETZT();B1))


Das wiederum erfordert das Abschalten der automatischen Iteration.


 -> Dazu in Extras / Optionen: Berechnen das Häkchen vor "Iteration" setzen ( dieses wird in der Excel - Mappe gespeichert; es ist jedoch nur dann wirksam, wenn die betreffende Mappe als erste oder einzige geöffnet wurde ).

 -> Fehlt diese Einstellung, wird eine Fehlermeldung mit einer Warnung auf eine Zirkelschluß ( Zirkelverweis ) ausgegeben - und der Zelleninhalt ( das Datum ) nicht angezeigt.

Auf welche Zahl die "Maximale Iterationszahl"gesetzt wird, ist nicht so wichtig."1" geht in den meisten Fällen, insbesondere wenn es sich um so etwas simples wie Adressenlisten handelt ( man kann es aber auch bei den voreingestellten "100" belassen ).   


 -> Je nach Zellenformatierung in Format / Zellen… wird damit in der Zelle B1 das Datum oder die Uhrzeit ausgegeben, sobald und so lange in der Zelle A1 ein Eintrag erfolgt ( ist ). Damit ist das Datum der letzte relevanten Änderung fixiert.

Bei geschickter Formatierung kann auch nach diesem Datum sortiert werden.








^ Uhrzeit


^ Zeitumrechnung / Zeiten / Winkel addieren ( Stunden / Minuten  / Sekunden zusammenzählen )


^  Zeiten addieren und subtrahieren:




Excel 97 - Tabelle: Zeitrechnung 1

Excel 97 - Tabelle: Arbeitszeit 1




^ Stundenberechnung:


^ Wenn Stunden / ZEITEN in die Rechnungen einfließen:



· =(C18/B8)*D18*24 oder
· /24 bei Geschwindigkeit z. B. : =AX/BY/24
· Entfernung/Zeit/24 oder
· Entfernung:Zeit:24 oder
· =(STUNDE(A31/B31)+MINUTE(A31/B31)/60)*AX oder
· gleich mit Datum + Uhrzeitformat eingeben!

Excel 97 - Tabelle: Arbeitszeit 2



^ Subtrahierte Uhrzeiten:







^ Dezimale Angaben in Uhrzeiten umwandeln:



Um aus Minuten-Angaben ( bspw. 450 Minuten in A9 ) zu Stunden zu machen, deren Zahl durch 60 teilen:

B5=A9/60

Die Uhrzeit liegt nun im Feld B5 im dezimalen Format 7,5 h vor:

dann

=GANZZAHL(B5)&" Stunden " &RUNDEN(REST(B5*60;60);2)& " Minuten "


Ergibt 7,5 h = 7 Stunden 30 Minuten


oder


=GANZZAHL((A9/60))&" Stunden " &RUNDEN(REST(A9;60);2)& " Minuten "



Ergibt 450 Minuten = 7 Stunden 30 Minuten


- direkt aus den Minutenzahl gerechnet.


Wichtig ist in beiden Fällen die Division durch 60, die den Rest liefert; deshalb muß im ersten Fall erst mit 60 multipliziert, dann wieder durch 60 geteilt werden.




Weiter Tips zu Uhrzeit -, Datum - und Winkelberechnung hier.







^ Exkurs:Duplikate finden


Mehrfache Einträge in Datentabellen ( Adressenlisten ) von Excel finden und anzeigen lassen - oder gleich unterbinden



  1. Grundsätzlich solche Datentabellen allein in einem Tabellenblatt führen, nicht mit Berechnungen, Kommentaren und anderen Dingen vermischen. Das macht es leichter Fehler zu vermeiden.

  2. Die fragliche Tabelle sichern und eine Kopie erstellen. Mit der Kopie arbeiten.

  3. In der oberste Zeile Überschriften ( wie beispielsweise "Namen, Vornamen, Telefonnummer" etc. ) eintragen, wo noch nicht vorhanden - am besten gleich fixieren. Das ist später beim Sortieren, Filtern und Bearbeiten der Daten von erheblicher Bedeutung.

  4. Es ist in Excel ein Kreuz, ein wirklich festes Datum automatisch einzufügen zu lassen. Deshalb ist es Ratsam, vor einen Datensatz ( mit beispielsweise Adressen ) einige Spalten namens "Nr.", "LfdNr" und "Datum" einzurichten.



 -> Es gibt nun zwei sich ergänzende Möglichkeiten, doppelte Einträge in einer Tabelle von Excel finden und anzeigen zu lassen, wenn man sie nicht von vornherein unterbinden will.



^ A ) Die erste Methode

arbeitet schon bei der Eingabe der Daten und ist auch für die anschließende Filterung von Nutzen.

Angenommen, es geht darum, in einer Adressenliste Nachname und Vorname auf duplikate Paare abgleichen zu lassen.


Dazu:

  1. Eine neue Spalte vor die fragliche Tabelle einsetzen, in die oberste Zelle eine eindeutige Überschrift wie "Schlüssel" eingeben ( ggf. auf Schrift = rot formatieren ), in die erste Zeile mit Daten die folgende Formel einsetzen und

  2. Diese mit [ Strg ] und dem [ Strg ] und dem [ + ] - Zeichen unten rechts an der Zelle so weit wie nötig herunterziehen.

Angenommen, die erste Zeile mit Daten wäre die Zeile 2, und
die Nachnamen wären in Spalte D, die Vornamen in Spalte E aufgeführt;


dann gibt es für die anzuwendende Formel einige Alternativen: 


Alternative 1:


=ZÄHLENWENN(D:D;D2)


 -> Ergibt die Anzahl der gleichen Eintragungen in Spalte DNachnamen )



oder


=ZÄHLENWENN(D:D;D2)*10+ZÄHLENWENN(E:E;E2)


 -> Ergibt die Anzahl der gleichen Eintragungen in Spalte DNachnamen ) und die Anzahl der gleichen Eintragungen in Spalte EVornamen ) als zusammengesetzte Zahl


für diese gilt hier:

  0   Leerzeile ( Keine Daten )
  1   Prüfen; Kriterium 1 fehlt
10   Prüfen; Kriterium 2 fehlt
11   Alles in Ordnung; Kriterium 1 und 2 kommen jeweils 1 mal vor.
1y -   In Ordnung; Kriterium 1 kommt 1 mal, Kriterium 2 kommt Y mal vor
2y -   Prüfen; Kriterium 1 kommt 2 mal, Kriterium 2 kommt Y mal vor
yy -   Dublette; Kriterium 1 und 2 kommen Y mal vor

… und darüber

Letztendlich ist diese Tabelle nur dann sauber, wenn vor jeder Zeile 1Y steht. Allerdings nur bis zu einer bestimmten Schwelle. Ab einer größeren Zahl von Datensätzen steigt die Wahrscheinlichkeit, daß beispielsweise lediglich das "Kriterium 2" 1Y mal vorkommt. Darüber hinaus können Kriterium 1 und Kriterium 2 unabhängig voneinander mehrfach vorkommen.



Alternative 2:


=WENN(ZÄHLENWENN(D:D;D2)>1;"Duplikat";"OK")

 -> Diese Formel mit dem [ Strg ] und dem [ + ] - Zeichen ( ohne [ Strg ] ) unten rechts über die ganze Länge der Tabelle ziehen; dabei darauf achten, daß der Zeilenbezug immer stimmt.

Achtung: Das Verschieben von einzelnen Datensätzen, egal wo, verändert den Bezug und das Resultat stimmt nicht mehr!

NB: Das "OK" ( oder ein anderer Ausdruck ) dient hier und anderswo nur als Platzhalter und als Signal dafür, daß die Formel in der Zelle tatsächlich vorhanden ist; es kann auch durch "" oder " / " ersetzt werden, um Irritationen zu vermeiden.


 -> Diese Formel gleicht hier nur den Nachnamen ab und wirft in der fraglichen Spalte 'Duplikat' aus, wenn der jeweilige Nachname doppelt erscheint; sonst nichts. Sie führt ALLE doppelten ( mehrfachen ) Eintragungen auf.

Diese Methode ist ganz nützlich, um schon beim Eintragen auf Duplikate hinzuweisen, und so ggf. Fehleintragungen zu vermeiden, beispielsweise bei Adressen und Telefonnummern. Mehrere Felder können gemeinsam überprüft werden, indem verschiedene Spalten durch "ODER "verbunden werden:

=WENN(ODER((ZÄHLENWENN(D:D;D2)>1);(ZÄHLENWENN(E:E;E2)>1));"Duplikat";"OK")


Dagegen führt

=WENN(ZÄHLENWENN(D$2:D2;D2)>1;"Duplikat";"OK")

 -> alle doppelten ( mehrfachen ) Eintragungen auf - bis auf die Erste.


Will man sowohl Vor - wie Nachnamen einzeln abgleichen, kann man diese Formel benutzen ( Eine Ausweitung auf andere Spalten ist nach diesem Schema natürlich auch möglich ):

=WENN(UND((ZÄHLENWENN(D:D;D2)>1);(ZÄHLENWENN(E:E;E2)>1));"Identisch";WENN(ZÄHLENWENN(D:D;D2)>1;"Doppelt";"OK"))

Dabei signalisiert:

"OK":              Vor - und Nachname kommen jeweils nur 1 mal vor
"Doppelt":        Der Nachname kommt mehrfach vor
"Identisch":      Vor - und Nachname kommen mehrfach vor


 -> Es werden ALLE mehrfachen Eintragungen aufgeführt.



=WENN(UND((ZÄHLENWENN(D$2:D2;D2)>1);(ZÄHLENWENN(E$2:E2;E2)>1));"Identisch";WENN(ZÄHLENWENN(D$2:D2;D2)>1;"Doppelt";"OK"))

 -> führt dagegen alle mehrfachen Eintragungen auf - bis auf die erste.

Allerdings ist auch diese Formel nicht eindeutig, da sie die Kriterien einzeln zählt und nicht deren Kombination; bei genügend Meiers, Müllers, Herthas und Karls werden falsch identische Datensätze ausgeworfen.

Darüber hinaus

Daher nun die


Alternative 3:


Eine Hilfsspalte einrichten ( hier beispielsweise H ) und durchgehend mit der Formel

=WECHSELN(D2&E2;" ";"")

belegen. Die Hilfsspalte enthält nun beide Kriterien ( hier Vor - und Nachnamen ) zu einer eindeutigen Zeichenkette ohne Leerzeichen zusammengesetzt ( eine Ausweitung auf andere Spalten ist nach diesem Schema natürlich auch hier möglich ). Diese eindeutige Zeichenkette wird nun mit der folgenden Formel auf Mehrfachvorkommen untersucht:


=WENN((H2="");"Leer";WENN(ZÄHLENWENN(H:H;H2)>1;ZÄHLENWENN(H:H;H2)&"mal";WENN(ODER((WECHSELN(D2;" ";"")="");(WECHSELN(E2;" ";"")=""));"Unvollst";"OK")))


Sie ergibt:

"OK":               Vor - und Nachname kommen jeweils nur 1 mal vor 
"Y mal":           Vor - und Nachname kommen Y mal vor 
"Unvollst":       Vor - oder Nachname fehlt
"Leer":             Vor - und Nachnamen fehlen 



 -> Es werden ALLE mehrfachen Eintragungen aufgeführt. Das ist günstiger, weil nicht immer eindeutig ist, welcher der mehrfachen Eintragungen ggf. fehlerhaft ist.

 -> Da diese Untersuchung gegen eine falsche Schreibweise der Kriterien nicht gesichert ist, sollte man zusätzlich mit einer zweiten, unabhängigen Untersuchung nach Alternative 1, 2 oder 3 weitere eindeutige Merkmale wie Straße und Hausnummer und / oder Telefonnummer abgleichen


Hinweis: Verkompliziert wird die Sache dadurch, daß die Funktion "ZÄHLENWENN" auch einer leeren Zelle einen Wert zumißt und diese mitzählt. Leerzeichen in einer Tabellenzelle lassen sich finden mit:

WENN(ZÄHLENWENN(D2:D2;"* *")>0;"Leerzeichen!";


Tip:








^ Filter



^  ) Die zweite Methode

wird nach Eingabe der Daten angewandt und besteht darin, den Spezialfilter bzw. AutofilterDaten / Filter / Spezialfilter bzw. Autofilter ) von Excel anzuwenden.


Spezialfilter anwenden:



Die folgende Methode kombiniert beide Möglichkeiten, das Zählen eindeutiger Zeichenketten mit der automatischen Filterung.

Sie hat den Vorteil, fragliche Datensätze zusätzlich per Hand abgleichen zu können.


Autofilter
:



 -> Ein Klick auf einen dieser Einträge zeigt nun alle zugehörigen Datensätze an ( mit blauen Zeilenköpfen ).

Nun die Datentabelle, wenn gewünscht, wieder nach der laufenden Numerierung der Spalte "Nr." in die ursprüngliche Reihenfolge sortieren lassen. Allerdings ist diese Nummerierung nun lückenhaft; sie kann falls nötig neu erstellt werden.


Excel 97 - Tabelle: Duplikate erkennen









 Zurück zu GRUNDLAGEN

  

Alle hier aufgeführten Excel 97 - Tabellen: gepackt

 




Letzte Aktualisierung: 06 / 10


ZU1 SeiteRÜCK
© 1999 Jürgen Hinrichs

http://erleuchtet.kilu.de/hilfe/index-g.htm



  Stichwortsuche:  

 Wie suche ich richtig mit FreeFind?

Suchbegriff(e) bitte EXAKT eingeben; ggf. mit Platzhalter abkürzen:
formatieren findet nur formatieren; format* findet auch Formate, Formatierung, etc.

     Von FreeFind
     Inhaltsverzeichnis