Caching internet locally ...

1. Oktober 2016

Excel 101: Cheatsheet für Journalisten

In diesem Beitrag werden die wichtigsten Funktionen für Microsoft Excel auf Windows und Apple erklärt. Das zugehörige Datenset der Demo ist hier herunterzuladen

Überblick

Vor dem Start

Datenstruktur festlegen

Vor der Eingabe der Daten sollte überlegt werden, welche Felder benötigt werden. Natürlich könnte ihr Spalten im Nachhinein hinzufügen. In diesem Fall müsst ihr aber wahrscheinlich Daten, die ihr bereits eingegeben habt, in die bestehende Struktur einpflegen. Es ist immer leichter Zeilen oder Spalten im Nachhinein zusammenzufügen als sie zu spalten. Das gilt beispielsweise für Namen, die in einer Zelle zusammengefasst werden. Besser ist es, Vorname(n) und Nachname(n) zu trennen. Dann können sie auch sortiert (A-Z) oder gefiltert werden.

In Zeilen werden Daten geschrieben, die sich wiederholen. In Spalten werden Daten geschrieben, die eine Eigenschaft beschreiben.

Ebenso sollte eine Reihenfolge festgelegt werden, die den Workflow stützt. Beispielsweise sollten die Felder Adresse, Hausnummer, Postleitzahl, Stadt, Land immer nacheinander folgen.
Bessere Navigation
Shortcuts, wie ihr im Registerblatt schnell von A nach Z kommt:

  • Spring zur nächsten Zelle rechts: ↹ Tab (Mac, Windows)
  • Spring zum rechten Rand der gewählten Zeile
    • Mac: [⌘ cmd] + [→ Rechte Pfeiltaste]
    • Windows:[ctrl] + [→ rechte Pfeiltaste]
  • Spring zum linken Rand der gewählten Zeile
    • Mac: [⌘ cmd] + [← linke Pfeiltaste]
    • Windows: [ctrl] + [← linke Pfeiltaste]
  • Spring zur letzten Zelle der gewählten Spalte
    Mac: [⌘ cmd] + [↓ Pfeiltaste abwärts]
    Windows: [ctrl] + [↓ Pfeiltaste abwärts]
  • Spring zur erste Zelle der gewählten Spalte
    • Mac: [⌘ cmd] +  [↑ Pfeiltaste aufwärts]
    • Windows: [ctrl] + [↑ Pfeiltaste aufwärts]

Bessere Übersicht

Markiere die Spaltenüberschriften und hebe sie mit fetter Schrift hervor.

fetten

Fixiere die erste Zeile, um die Spaltenüberschriften auch dann im Sichtfeld zu haben, wenn du in großen Datensets nach unten scrollst.

oberstezeileeinfrieren

Spaltenbreite anpassen
Pass die Spaltenbreite so an, dass du alles sehen kannst. Wenn du über den Bereich zwischen zwei Spalten hoverst, taucht ein Zeichen mit zwei Pfeilen in die jeweils entgegengesetzte Richtung auf. Damit kann die Spaltenbreite je nach Bedarf angepasst werden. Der Screenshot zeigt das Vorgehen auf Mac. Auf Windows ist die Abfolge: Registerkarte Ansicht in der Gruppe Fenster auf den Pfeil unter Fenster einfrieren – Oberste Zeile einfrieren.

spaltenbreiteanpassen

Formatierungen entfernen

Üblicherweise kommen Excel-Dateien mit Formatierungen. Das kann zu Problemen bei späteren Berechnungen und Funktionen führen. Deshalb sollten alle Formatierungen entfernt werden. Auf Mac ist der Befehl unter Bearbeiten – Löschen – Formate zu finden. Der Bereich, der seine Formatierungen verlieren soll, ist vorher zu markieren.

formatierungentfernen

Auf Windows ist die Abfolge: Registerkarte Start in der Gruppe Bearbeiten auf den Button Löschen klicken und Formate löschen wählen.

Text in Spalten

Wird verwendet, wenn der Inhalt einer Zelle auf Basis eines bestimmten Zeichens getrennt werden soll. Das kann ein Beistrich, Semikolon, Tab oder vorher definiertes Zeichen sein. Häufig wird dieses Feature eingesetzt, um eine Datei mit der Endung .csv (comma-seperated values) in ein besser lesbares Format zu überführen.

Die Datenset zur Bevölkerungsprognose wird so zur Verfügung gestellt.

Berechnungen als Werte einfügen

Häufig liefern Berechnungen auch Fehlermeldungen, weil nicht mit Werten, sondern mit den Formeln dahinter gerechnet wird. Wenn der Großteil der Kalkulationen, die nicht aufeinander aufbauen, abgeschlossen ist, kann deshalb ein weiteres Arbeitsblatt eröffnet werden, in dem der bearbeitete Datensatz kopiert und als Wert eingefügt wird.

Hier wird beispielsweise die Geschlechterverteilung pro Zählbezirk berechnet.

Bildschirmfoto 2015-11-08 um 11.42.51

Es ist besser mit dem Quotienten als Wert weiter zu rechnen als mit der Formel, die zum Ergebnis geführt hat. Deshalb wird das Arbeitsblatt kopiert, im neuen Arbeitsblatt erfolgt in die erste Zelle in Rechtsklick und die Option Inhalte einfügen wird gewählt. Der Dialog sieht dann so aus.

alswerteinfuegen

 Funktionen

Diese Sammlung ist ein Überblick der wichtigsten Funktionen im datenjournalistischen Tagesgeschäft. Diese Liste ist nicht vollständig. Eine Abbild aller Möglichkeiten ist kaum möglich, weil Excel sehr sehr sehr viel anbietet. Google hilft im Normalfall weiter. Falls die Ergebnisse nicht zufriedenstellend waren, ist es ratsam, das jeweilige Problem auf Englisch zu googeln.

Es gibt immer jemanden, der bereits ein ähnliches Problem hatte.

Wurdest du fündig, kannst du die angegebenen Formel beispielsweise mit diesem Sheet zurück ins die deutsche Übersetzung wandeln.

Wenn-Funktion

Führt eine Berechnung durch oder liefert einen Wert zurück, wenn eine vorher definierte Bedingung erfüllt wird.  In unserem Datensatz können wir prüfen lassen, ob es in einem Zählbezirk mehr Männer oder Frauen gibt und das Ergebnis als Text zurück liefern.
Bildschirmfoto 2015-11-09 um 20.58.17

Aufpassen: Text muss im Unterschied zu Zahlen oder Berechnungen (z.B.: E2-F2 oder 5+5) in „Anführungszeichen“ gesetzt werden. Kommt vor allem zum Einsatz, wenn eine Kontrollspalte gemacht werden soll. Diese kann dann wiederum sortiert und gefiltert werden.

Filtern und Sortieren
Wenn Daten nach ihrem Wert absteigend oder aufsteigend gereiht oder gefiltert werden sollen. Auf Mac:

  1. In die erste Zeile mit den Überschriften klicken.
  2. Registerkarte Start in der Gruppe Sortieren und Filtern auf den Button Filtern klicken.
  3. Im Dialogfenster je nach Belieben Auf- oder Absteigend sortieren oder über das Suchfeld Werte filtern und An- oder Abwählen.

filternundosrtieren

Auf Windows: Registerkarte Start in der Gruppe Bearbeiten und Sortieren.

Achtung: Das Filtern nur einer Spalte kann dazu führen, dass die Werte ihre „Zugehörigkeit“ zu einem Datensatz verlieren. Das heißt ihre Verbindung zur ID, im verwendeten Beispiel-Datensatz könnte es der Zählbezirk sein. Für korrektes Filtern muss die Filtermöglichkeit bei allen Spalten aktiv sein. Fehlerpotenzial ist v.a. dann vorhanden, wenn es leere Spalten in einem Datensatz gibt, die nicht in eine Bildschirmansicht passen. Das passiert insbesondere dann gerne, wenn es viele Spalten in einem Datensatz gibt.

Wenn in einer Zelle nur ein bestimmter Teil interessant ist, dann können wir diesen mit Formeln extrahieren. In der Bevölkerungsprognose können wir zur besseren Ansicht des Prognosejahres nur das Jahr herausfiltern.

  • Das heißt es sind vier Zeichen gezählt von der linken Seite zu extrahieren. Demnach ist das Ziel mit =LINKS(M2;4) zu erreichen.
  • Gleiches Schema gilt für den Fall, dass wir den Monatstag extrahieren wollen. Das wäre mit =RECHTS(M2;2) zu erreichen.
  • Wenn wir den Monat extrahieren wollen, wäre das mit =TEIL(M2;5;2) zu erreichen. In diesem Fall setzen wir mit 5 den Punkt, wo Excel 2 Zeichen extrahieren soll.

Zählenwenn(s)

Diese Funktion kommt zum Einsatz, wenn wir auf der Suche nach der Anzahl der Zellen sind, die ein bestimmtes Kriterium erfüllen. Das können einzelne oder mehrere Kriterien sein. Geht es um ein einzelnes Kriterium, wird die Funktion =ZÄHLENWENN verwendet. Andernfalls wird =ZÄHLENWENNS einzusetzen. Beispielsweise können wir in unserem Datensatz die Anzahl der Zählbezirke berechnen, in denen es mehr Frauen als Männer gibt. Dafür sind zwei Bedingungen festzulegen. Einerseits, dass „mehr Frauen“ gezählt werden soll. Andererseits, dass das Jahr auf „2014“ gesetzt ist.

Zudem ist anzugeben, welcher Bereich gezählt oder geprüft werden soll. Das ist hier an erster bzw. dritter Stelle in der Formel zu sehen.

zaehlenwenns

Und woher kommen diese $-Zeichen? Wir müssen die zu prüfenden Bereiche fixieren, also absolut setzen. Aber warum? Mehr weiter unten im Text.

Die Berechnung ergibt, dass es im Jahr 2014 in 215 Zählbezirken mehr Frauen als Männer gibt. Für einen Vergleichswert wäre es jetzt interessant, diese Formel auch für „mehr Männer“ und für das Jahr 2024, das Ende des Prognosezeitraums, anzuwenden. Wir müssen die Formel dafür nicht neu eingeben, sondern können Sie mit dem schwarzen Kreuz, das bei Hover über die rechte untere Ecke erscheint, nach unten ziehen.

formelkopieren

Bildschirmfoto 2015-11-09 um 23.45.41

Weil die Kriterienbereiche absolut gesetzt sind und die Kriterien noch nicht verändert wurden bleibt das Ergebnis gleich. Deshalb sind die Kriterien entsprechend auf „Männer“ bzw. „2024“ zu verbessern.

Bildschirmfoto 2015-11-09 um 23.48.36

Es ist darauf zu achten, dass die jeweiligen Kriterien exakt gleich geschrieben sind wie im gewählten Bereich. Jedes Leerzeichen oder falsche Umlaut kann zu fehlerhaften Berechnungen führen.

Aber wie war das mit Formel absolut setzen nochmal?

Bereiche absolut setzen

Wir möchten Formeln möglichst effizient verwenden, sie also nicht für jeden Zählbezirk neu eingeben. Deshalb kann die Formel wie eben praktiziert mit dem schwarzen Pfeil nach unten gezogen werden. Der Kriterienbereich muss absolut gesetzt werden, weil er sonst nach unten mitwandern würde. Das ist manchmal hilfreich, oft nicht.

In unserem Fall soll der zu zählende Bereich gleich sein und nicht bis auf das Feld J4500 ausgeweitet werden. Was soll dort auch schon gezählt werden? Um dieses Prinzip zu verstehen, ist es am besten, den Fehler selbst zu machen.

Für das Hinzufügen der $-Zeichen gibt es einen Shortcut:

  • Mac: [cmd] + [T]
  • Windows: F4

Maximalen Wert finden

Mit der Formel =MAX(Bereich) können wir den höchsten Wert in einem Bereich finden. Möchten wir den Zählbezirk finden mit dem höchsten Frauenanteil wäre das =MAX(I2:I2740). Das Ergebnis ist 0,5599.

(*Im Datensatz gibt es einen Subdistrict, in dem keine Frau wohnt und ein Mann. Dieser Bereich muss entfernt werden, weil Divisionen durch null nicht möglich sind. Wir können nicht damit rechnen.)

Minimalen Wert finden

Mit der Formel =MIN(Bereich) können wir den kleinsten Wert in einem Bereich finden. Möchten wir den Zählbezirk finden mit dem geringsten Frauenanteil wäre das =MIN(I2:I2740). Das Ergebnis ist 0,33*.

Mittelwert, Median, Modalwert

  • Das arithmetische Mittel (Durchschnitt) ist mit =MITTELWERT(Bereich) zu berechnen.
  • Der Median ist mit =MEDIAN(Bereich) zu errechnen.
  • Der Modalwert, der häufigste Wert in einem Bereich, ist mit =MODALWERT(Bereich) zu errechnen.

Der Unterschied zwischen Median und Mittelwert wird hier einfach erklärt. 

SVERWEIS und WVERWEIS

Eine der praktischsten Funktionen im Repertoire. Sie wird verwendet, um Daten aus einem anderen Tabellenblatt basierend auf einem gemeinsamen Nenner zuzuweisen. Am Beispiel erklärt. Wir haben eine Spalte Subdistrict. Diese Zahl identifiziert einen Zählbezirk eindeutig. In einem anderen Arbeitsblatt gibt es die Namen der Wiener Zählbezirke und die jeweilige Subdistrict-Nummer. Das Prinzip kann auch auf Mitarbeiternummern, Artikelnummern, Gemeindekennziffern o.Ä. angewendet werden.

Die Zählbezirke der Bevölkerungsprognose sollen also Namen bekommen. Dafür nehmen wir diese Datei. 

Die Syntax ist =SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis]). 

Das Suchkriterium ist jener gemeinsame Nenner, der einen Eintrag aus einer andere Tabelle einen Eintrag zugewiesen bekommen soll.  Die Matrix ist jener Bereich, in dem der SVERWEIS in der ersten Spalte nach dem Suchkriterium sucht. Spaltenindex bestimmt, welche Spalte ausgegeben wird. In unserem Beispiel also der Name des Zählbezirks. Das ist in der Matrix die zweite Spalte.  [Bereich_Verweis] ist auf FALSCH zu setzen, damit eine exakte Übereinstimmung erreicht wird. Mit WAHR wird der nächstgelegene Wert zurückgegeben. Weil bevorzugt genau gearbeitet soll, ist die erste Variante zu wählen.

Der WVERWEIS kommt bei einem horizontalen Datenlayout in Frage. Die Matrix wird dann statt senkrecht nach unten horizontal durchsucht.

Glätten

Es kommt vor, dass in Datensets ein Leerzeichen vor- oder nachgestellt ist. Das kann zu Fehlern führen, wenn man den SVERWEIS einsetzt. Es kann keine exakte Übereinstimmung gefunden werden, deshalb liefert Excel ein #NV zurück. Deshalb müssen die Leerzeichen in der Tabelle mit der Matrix und dem gemeinsamen Nenner mit =GLÄTTEN(Zelle) entfernt werden.

Posted in Allgemein
Write a comment

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.