Excel, einige Kurztipps für den Umgang mit Excel

(SPALTE = VON OBEN NACH UNTEN) (ZEILEN = VON LINKS NACH RECHTS)

Postleitzahlen mit Länderkennzeichen verketten:

• Neue Spalte bilden (Spalte muss als Standard deklariert sein)
• Über Menü Einfügen/Funktionen (ab 2007: Formel/Funktion einfügen)
• In Zeile von neuer Spalte bleiben
• Text/Verketten
• Feld 1 klicken

Postleitzahlen mit Ort in separate Felder aufteilen

• rechts neben der Spalte PLZ mit Ort mind. eine neue leere Spalte erstellen
• Spalte PLZ mit Ort markieren
• Im Menü „Daten“, „Text in Spalten“, „Getrennt“ auswählen, „Weiter“ anklicken, „Tabstopp“ deaktivieren, „Leerzeichen“ aktivieren, „Weiter“ anklicken
• Die neue Spalte mit der PLZ als Text Spalte formatieren (Datenformat der Spalte) da sonst bei PLZ mit führender „0“ diese weggenommen würde. Anschließend auf „Fertigstellen“ klicken.

4-stellige Postleitzahlen aufnullen:

• Spalte markieren
• Format/Zellen
• Benutzerdefiniert
• 5 Nullen eintragen

Zahlungsbedingung kopieren:

• Feld kopieren (Ctrl C)
• weiteren Bereich markieren
• Einfügen (Ctrl V)

Mappennummer auffüllen:

• 1. Zeile mit 001 bestücken
• 2. Zeile mit 002 bestücken
• Zwei Zeilen mit der Maus herunterziehen

Hilfreiche Tastenkürzel:

• Strg + Pos 1 = ganz an den Anfang springen
• Strg + Ende = ganz an das Ende springen
• Ende + Enter = Ende der Zeile
• Strg + ↓ = Ende Spalte

Summenbildung:

• Feld anklicken, wo Summe stehen soll
• Eintippen: =Summe( und mit den Pfeiltasten erstes Feld ansteuern
• Eintippen: : und mit der Pfeiltaste letztes Feld ansteuern
• Enter

Überschriften und Zeilen fixieren

• In die Zellen klicken, die unter Überschrift und rechts von der zu fixierenden Spalte ist.
• Über Menü „Fenster“ „Fenster fixieren“ auswählen
• Wenn Sie jetzt nach rechts oder nach unten in der Tabelle gehen, bleiben die Überschriften stehen.

Spalten in Zeilen wandeln – oder umgekehrt

• Zeile markieren
• Im Menü „Bearbeiten“, „Kopieren“ wählen
• In freie Zelle klicken
• Über Menü „Bearbeiten“, „Inhalte einfügen“ und in der Folgemaske “Transponieren“ auswählen.

Feld kürzen auf 20 Stellen

• Neue Spalte
• Menü "Einfügen/Funktionen"
• links "Text" anklicken
• rechts "links" anklicken
• in nächster Maske "Quellspalte" anklicken (Hintergrund)
• Anzahl Zeichen (hier 20) eingeben
• Feld kopieren wie oben bei Zahlungsbedingungen
• dann wieder neue Spalte
• neue Spalte markieren
• rechte Maus rein + kopieren
• rechte Maus auf Kopfzeile von neuer leerer Spalte
• Inhalte einfügen
• Nächste Maske "Werte" anklicken

Feld "04.95" bereinigen um "04."

• Neue Spalte anlegen
• Ins Feld 1 der neuen Spalte klicken
• Menü "Einfügen/Funktionen/Text/Rechts"
• Quellzeile und Anzahl Zeichen eingeben
• Funktion kopieren

Länge der Zellen einer Spalte ermitteln

• Neue Spalte bilden
• oben eingeben „=Länge(Z1)“
• in das erste Feld der Spalte einklicken und Doppelklick auf fetten Rahmen rechts unten

Doppelte Emailadressen feststellen (nur sinnvoll bei nicht personalisierten E-Mails)

• Spalte „Emailadresse“ sortieren
• Zwei neue Spalten hinter Spalte Emailadresse einfügen
• In Zelle der ersten neuen Spalte unter der Überschrift klicken und folgende Funktion eingeben: =WENN(R3 = R2;“Doppelte Emailadresse“;““)
• Doppelklick auf Zelle unten rechts, Spalte wird gefüllt
• Spalte markieren und kopieren
• Mit der rechten Maustaste in das freie Feld Überschrift der neuen zweiten Spalte klicken
• "Inhalte einfügen" und in der Folgemaske "Werte" bestätigen
• Jetzt die letzte Spalte von "Z nach A" sortieren
• Alle doppelten E-Mail-Adressen stehen oben

Lange Felder trennen

• Lange Felder können mit einem Tool von CDH getrennt werden, das Programm CDHSplitColumn.exe würde z. B. eine Artikel-Bezeichnung in folgende neue Felder trennen:
  

Feld 1: Länge 30 Zeichen

Feld 2: Länge 30 Zeichen

Feld 3: Länge 900 Zeichen

Damit können auch Langtexte in zwei Kurzbezeichnungen und in einen neuen Langtext getrennt werden, die Trennung erfolgt sinnvoll nach Wörtern.

OLAP-Statistik über Excel ausdrucken mit Überschriften

Wenn eine OLAP-Statistik nach Excel exportiert wird, gibt es die Möglichkeit, einen Ausdruck so zu erzeugen, dass auf jeder Seite eine Überschrift steht. Dies funktioniert wie folgt:

• In der Ansicht im Infosystem mit der rechten Maustaste in die Liste klicken und „Export nach Excel“ auswählen
• In Excel die Registerkarte „Seitenlayout“ auswählen und anschließend auf „Drucktitel“ klicken
• Im Feld „Wiederholungszeile oben:“ Folgendes eingeben: $1:$3
• Anschließend auf „OK“ klicken

Jetzt werden die ersten drei Zeilen auf allen Seiten gedruckt

Die Spalte N enthält sowohl PLZ und ORT aber in unterschiedlichen Formatierungen

Variante 1:

Eine neue Spalte neben der vorhanden einfügen und dann diese Formel verwenden. Dadurch wird das 1.Leerzeichen durch ein „;“ ersetzt und kann durch die Funktion „Text in Spalten“ getrennt werden =ERSETZEN(N9;FINDEN(" ";N9;1);1;";")

Ergebnis

Variante 2:

Man kann die Originalspalte durch eine Formel auch direkt in 2 Spalten aufteilen. Dazu müssen 2 neue Spalten eingefügt werden.

In der 1. Spalte wird folgende Formel eingetragen =LINKS(N9;FINDEN(" ";N9;1)-1)

In der 2. Spalte =RECHTS(N9;LÄNGE(N9)-FINDEN(" ";N9;1))

Ergebnis

Führende Nullen bei Telefonnummern und Postleitzahlen beibehalten, wenn CSV-Datei in Excel importiert wird

• Leere Excel Mappe öffnen
• „Datei öffnen“
• Entsprechende Datei auswählen
• Schritt 1 von 3: „Ursprünglicher Dateityp“ auf „getrennt“ setzten
• Schritt 2 von 3: Trennzeichen auswählen
• Schritt 3 von 3: Telefonnummern Spalten und PLZ Spalte auswählen und als Text deklarieren.

Die Datei darf nur einmal abgespeichert werden, da sonst die Spalten wieder umformatiert werden.

Führende Nullen in einer xls-Datei beibehalten

Bei Excel werden Zahlenfelder mit führender Null verändert. Die führende Null wird einfach weggelöscht (z. B. bei PLZ aus Ostdeutschland).

Tipp: Wenn man in einer Excel-Tabelle solch ein Feld hat, kann man vorne einfach ein Hochkomma vorsetzen, dann löscht Excel die führende Null nicht.
Es kann jedoch Probleme geben, wenn man solche Felder als CSV-Datei speichert.

Zahlenformatierung beim Verbinden von Zellen beibehalten

Formel: =VERKETTEN("Gesamtumsatz €: ";TEXT(A4;"#.##0,00"))

Bestimmten Text den Feldinhalten einer Spalte voranstellen/anhängen (Funktion Verketten)

• Drei neue Spalten links neben der zu bearbeitende Spalte z.B. „Name1“ bilden: Spalte markieren - rechte Maustaste - Zeilen einfügen (3x)
• Drei Überschriften eintragen Bei Import in CDH (8,25) darf keine Spalte genauso wie eine andere heißen
• In erste neue Spalte den anzufügenden Text z.B. „ZZZ“ eintragen und in alle Zeilen kopieren
• In zweite neue Spalte in zweite Zeile klicken und Funktion „Verketten“ einfügen über Menü Formeln - Funktion einfügen – Kategorie „Text“ – „Verketten“ auswählen – OK
• In Text1 klicken und dann in die 2. Zeile der ersten neuen Spalte klicken
• In Text2 klicken und dann in die 2. Zeile der zu verändernden Spalte klicken
• Dann wird der Text vorangestellt. Nimmt man statt „Text1“ das Feld „Text2“ wird angehangen
• OK
• Text erscheint verkettet im Feld
• Formel/Funktion kopieren in alle Zeilen
• 2. neue Spalte markieren und kopieren
• In 3. neuer Spalte nur die „Werte“ einfügen, nicht die Formel
• 3. neue Spalte Überschrift genauso wie die zu bearbeitende Spalte benennen
• Alte zu ändernde Spalte ebenso umbenennen in z.B. „Name1Orig“ oder ganz entfernen
• Hilfsspalten ggf. entfernen