[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:

• Datum und Uhrzeit: Format
  
  
• Datum
  
  • Kalenderwoche
    
  • Datum fixieren
    
    
• Uhrzeit
  
  • Zeitumrechnung - Winkelumrechnung
    
  • Zeiten addieren und subtrahieren
    
  • Stundenberechnung
    
  • Stunden / Zeiten
    
  • Subtrahierte Uhrzeiten
    
  • Dezimale Uhrzeiten
    
    
    
• Exkurs: Duplikate finden
  
  • Duplikate zählen
    
  • Filter
    

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:

• # = Platzhalter für Stellen, z. B. ###,## = 3 Stellen vor, zwei hinter dem Komma ( nur # = keine Anzeige bei Null! )
  
  
• 0 = Platzhalter für Ziffern, z. B. 000,00 = 3 Stellen vor, zwei hinter dem Komma, mit führenden Nullen!
  
  
• Textbestandteile von Zahlen immer "in Anführungszeichen"; hier sind Leerstellen möglich, z. B.: 0,00 " kWh"
  
  
  ( Neben dem so allgemein gültigen benutzerdefinierten Zellenformat kann man auch Zahlenfelder über eine Formel oder individuell mit Text formatieren )
  
  
  
• Zahlen als Text interpretieren lassen: Apostroph (') davorstellen.
  
  
• Formeln als Text interpretieren lassen: Leerzeichen davorstellen: (  = )
  
  
• Zahlen in Klammern: "("0")"
  
  
• oder Worte und Zahlen kombinieren:
  
  =("auf " & B1 & " km") oder =("auf " & B1 & " " & D1)    [ D1 = Dimension, variabel ]
  
  
• Worte und Zahlen kombiniert in Uhrzeiten:
  
  ="Die addierte Zeit beträgt " & TEXT (SUMME(A1:A5); "[h]:mm") & " Stunden"

- 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.

• Das Datum wird als Ganzzahl gespeichert mit dem Startwert 1. 1. 1900 = 1 ( in Microsoft- Systemen )
  
  
• Im Format Zahl reicht die Eingabe z. B.: "01.12.99", um das Datumsformat zu erzeugen.
  
  
• Jahre berechnen: X/365,25 ( ! )
  
  wegen der Schaltjahre
  
  
  
  Excel 97 - Tabelle: Datum und Uhrzeit - Formate
  

^ 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  + "."  und die Zeit mit   +   + "."  ( entspricht  + ":"  )

  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öglichkeit, 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 )

• Im Format Zahl reicht die Eingabe z. B.: "01:01", um das Uhrzeitformat zu erzeugen: Stunde:Minuten
  
  
• Excel kann beispielsweise die Zeiten "01:10" ( Anfangszeit ) und "02:00" ( Endzeit ) voneinander abziehen.
  Ergebnis: "0:50" ( = 50 Minuten )
  
  
• Die Uhrzeit wird im Format Zahl als serielle Zahl zwischen 0 und 1 ( 1=24; 12:00=0,5 ) gespeichert;
  Excel kann deshalb so nicht über 24 rechnen: Fehler!
  
  Um berechnete Uhrzeiten über 24 h korrekt darzustellen, muß in Excel die Zelle besonders formatiert werden:
  
  Format / Zellen / Zahlen / Uhrzeit: Benutzerdefiniert:   [hh]:mm:ss   [ bzw. [h]:mm ]
  
  oder z. B. 26:30:55
  
  { 1=24 [hh]=1440 [mm] ( =60*24 )}
  
  oder z. B. mit Text
  
  
  
• Mit diesem Zellenformat lassen sich in Excel auch Winkelberechnung durchführen bzw. anzeigen, da es so mehr als 60 Grad anzeigen kann:
  
  Format / Zellen / Zahlen / Uhrzeit: Benutzerdefiniert:   [hh]:mm:ss
  
  oder z. B. mit Text:     [HH] "° " MM "'" SS " ' ' "
  
  
  60,58333 Grad + 63 Grad = 123,508333 Grad oder 123 ° 30 ' 30 ' '
  
  
  
• Zurückrechnen läßt sich das Format wie eine Uhrzeit:   = *D18*24
  
  [ 123 ° 30 ' 30 ' ' ] *24 = 123,508333 Grad
  
  
  Siehe auch hier (Fremde Seite) sowie hier.
  
  
  

^  Zeiten addieren und subtrahieren:

• (Zeit_1 +/- Zeit_2)*24 = Zeit, dezimal. Damit läßt sich weiterrechnen.
  
  
• Das Ergebnis muß natürlich dann durch 24 geteilt werden, bevor es wieder im Uhrzeitformat  angezeigt werden soll.
  
  
  Bei Zeitdifferenzen, die über 24:00 hinausgehen und damit negativ werden ( oder über 12:00 im 2 x 12 Stunden - Format ) zur Berechnung das Ergebnis um + 1 = 24 Stunden ( 1 Tag ) bzw. + 0,5 = 12 Stunden ( 0,5 Tage ) erhöhen:
  
  =WENN((B15-B13)>=0;(B15-B13);(B15-B13)+1)
  
  
• Minuten (dezimal) addieren und das Ergebnis als Zeit ausgeben lassen ( Stunden und Minuten: Format: HH:MM:SS )
  
  =SUMME(A4:C4)/1440
  
  60,5 min + 63 min = 2:03:30
  
  

Excel 97 - Tabelle: Zeitrechnung 1

Excel 97 - Tabelle: Arbeitszeit 1

^ Stundenberechnung:

• Das Datum wird automatisch mitgespeichert, aber nicht abgerufen; durch die gemeinsame Formatierung als Datum + Uhrzeit ist jedoch möglich, um über 24 h zu rechnen! Dazu reicht dann auch einfaches Umformatieren - nicht umrechnen. Nachteil: komplizierte Werteeingabe.  
  
  Entsprechend wird beim Datum auch gleichzeitig die Uhrzeit als 00:00 abgespeichert.
  
  
• Funktionen: TAG(AX) STUNDE(AX) MINUTE(AX) ( Ganzzahl )
  
  
• Mit der TAG(AX) - Funktion lassen sich berechnete Werte über 24 Stunden wieder in Stunden auflösen:
  
  =(TAG(ZeitSumme)*24+STUNDE(ZeitSumme))
  
  - Zieht aus ( beispielsweise ) 26:30:55 den Stundenwert von 26; sonst wäre er 2 ( Fehler! ).
  
  
  

^ 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:

• Subtrahierte Uhrzeiten lassen sich in Dezimalzahlen umformatieren ( zum multiplizieren ).
  
  Dieses Microsoft- Normalformat hat aber den Nachteil, daß berechnete negative Uhrzeiten nicht dargestellt werden können!
  
  
• In dem Fall VORHER das Blatt auf das Mac- Format 1904- Datumswerte mit dem Startwert 1. 1. 1904 = 0  umstellen, wo dieses möglich ist: Extras / Optionen / Berechnen ( auch bei Konvertierungen Mac / MS ). Negative Zahlenwerte müssen danach beim Eingeben in Anführungszeichen gesetzt werden: -"12:30"
  
  Diese Datumswerte gelten immer für das ganze Blatt, also VORHER machen! Sonst werden Datumsanzeigen verfälscht: 14. 01. 99 – > 15. 01. 03  ( =  + 1462 Tage – abziehen lassen! )
  
  Siehe auch:
  
  
  Excel 97 - Tabelle: Stundenabrechnung
  
  
  Excel 97 - Tabelle: Stundennachweis

^ 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.
   
   
   • In der Spalte "Nr." die erste Zelle unter der Überschrift mit "1" belegen und mit dem und dem  - Zeichen unten rechts an der Zelle so weit wie nötig herunterziehen. Nicht mit einer Formel automatisieren!
     
     Diese Nummer dient dazu, nach mehrfacher Neusortierung die ursprüngliche Ordnung wieder herzustellen, und damit die ursprüngliche Reihenfolge der Eintragungen. WENN sie denn mitsortiert wurde.
     
     
   • Die Spalte "LfdNr" wird genauso erstellt, aber nach jeder Bereinigung der Datensätze neu, um aus der Differenz Ort und Zahl der gelöschten Datensätze feststellen zu können.
     
     
   • Vor dem Sortieren ( und Filtern ) immer auf A1 gehen ( mit    +    ) bzw. die ganze Tabelle markieren - sonst läuft man große Gefahr, die gesamten Tabelle unwiderruflich zu ruinieren!
     
     
   • In die Spalte "Datum" entweder mit  + "."  das Datum als unveränderliche Zahl eingeben - oder nach einer oben beschriebenen Methode.
     
     
   • Dies Spalte so formatieren, daß die Jahresangabe vorne liegt, gefolgt von Monat und Tag ( amerikanische Notierung ). Damit kann ggf. nach dem Datum sortiert werden.
     
     

  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 und dem 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 D ( Nachnamen )



oder


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


  Ergibt die Anzahl der gleichen Eintragungen in Spalte D ( Nachnamen ) und die Anzahl der gleichen Eintragungen in Spalte E ( Vornamen ) 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 und dem  - Zeichen ( ohne  ) 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

• wirft sie auch Leerzeilen als "OK" aus 
  
  - und -
  
• läßt das Ergebnis durch unsichtbare Leerzeichen in den Eintragungen verfälscht werden
  
  

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:

• Die Hilfsspalte ggf. eindeutig farbig absetzen und so schmal machen, daß sie nicht mehr sichtbar ist ( Format / Spalte / Breite: Spaltenbreite = 0 ); dann wird sie bei der Bewegung in der Tabelle mit der Tabulator - Taste übersprungen ( angezeigt wird das dadurch, daß im Spaltenkopf die Spalte "H" nun fehlt; wieder sichtbar gemacht wird sie, indem man die beiden rechts und links benachbarten Spalten markiert und in Format / Spalte / Breite: Spaltenbreite = optimal setzt ).
  
  
• Allerdings: Beim Hineinkopieren von Inhalten über mehrere Zellen hinweg ist die so unsichtbar gemachte Hilfsspalte weiterhin aktiv; sie wird deshalb ggf. überschrieben, die Spaltenzuordnung stimmt nicht mehr, und die Tabelle wird unbrauchbar. Abhilfe: Die Hilfsspalte außerhalb des aktiven Tabellen - Bereichs verlegen.
  
  

^ Filter

^  ) Die zweite Methode

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


Spezialfilter anwenden:

• Die Tabelle öffnen und die Spalten mit den Kriterien ( hier beispielsweise Vorname und Nachname ) markieren - eigentlich den entsprechenden Tabellenbereich ( falls Excel das nicht automatisch vornimmt ).
  
  
• Unter Daten auf Filter / Spezialfilter klicken.
  
  
• "Liste an gleicher Stelle Filtern" und "Keine Duplikate" aktivieren.
  
  
• Das Resultat ( mit blauen Zeilenköpfen ) enthält nun keine eindeutigen Duplikate mehr.
  
  Der bereinigte Datensatz kann nun kopiert (!) und in eine andere Arbeitsmappe eingefügt werden.
  
  Achtung: Es handelt sich dabei um eine reine Werte - Kopie; die Formeln werden nicht mitkopiert. Spätestens an dieser Stelle könnte man also eine automatisiert erstelltes Datum fixieren …
  
  
• Daten / Filter / Alle anzeigen beendet den Filtermodus wieder.
  
  Allerdings: Auch diese Methode ist gegen eine falsche Schreibweise der Kriterien nicht gesichert; es können inhaltlich identische Datensätze auf Grund unterschiedlicher Schreibweisen als unterschiedlich erkannt und damit übersehen werden. Dazu genügt ein ( unsichtbares ) Leerzeichen.
  
  

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:

• Die Tabelle mit der Alternative 3 präparieren und sichern.
  
  
• Die Tabelle markieren und nach dem Hauptkriterium ( hier der Nachname ) sortieren lassen ( mit Überschriften ).
  
  Achtung: Vor dem Sortieren immer auf erste Feld "A1" ( mit    +    ) gehen, ohne es zu markieren - oder die ganze Tabelle markieren.
  
  Sonst können die Daten schnell durcheinander geraten.
  
  
• Die Spalte mit dem aus der Formel resultierenden Ergebnis ( Schlüssel ) markieren.
  
• Unter Daten auf Filter / Autofilter klicken.
  
  Es erscheint ein Pfeil für ein Dropdown - Feld, unter dem sich eine Liste aller in dieser Spalte gefundenen Einträge findet - jeweils genau ein mal.
  
  
• Hier wären das also:
  
  "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 
  
  

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

• Wenn vorher sortiert wurde, erscheinen nun alle Datensätze mit dem gleichen oder nahezu gleichen Kriterien untereinander und können abgeglichen und / oder - bei erwiesener Identität oder Unbrauchbarkeit - gelöscht werden.
  
  
• Achtung: Die gelöschten Datensätze hinterlassen Leerzeilen und damit Lücken in der laufenden Nummerierung der Spalte "Nr." ( falls diese eingerichtet wurde ).
  
  
• Daten / Filter / Alle anzeigen zeigt wieder die gesamte Tabelle an.
  
  
• Das Deaktivieren des Autofilter unter Daten / Filter beendet den Filtermodus.
  
  

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

ZURÜCK
© 1999 Jürgen Hinrichs

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

Stichwortsuche:   Alle diese Worte (='UND') Eines dieser Worte (='ODER') in den Hilfe-Seiten in der Script-Sammlung  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