Excel Schulung als Crashkurs

für kaufmännische Anwender

Joern Steinz / Skillday.de

Inhalte

2 SD SkillDay.de

DatenanalyseFunktionen & Berechnungen

Daten-visualsierung

1

Inhalte einfügen Optionen (1/2)

3 SD SkillDay.de

Tabellen drehen (transponieren): Nützlich wenn Sie die Spaltenund Zeilen einer Tabelle tauschen möchten.

1. Markieren Sie Ihre Tabelle und kopieren Sie sie (STRG + C)2. Klicken Sie die rechte Maustaste und wählen Sie Inhalte

einfügen (alternativ STRG + ALT+ V)3. Wählen Sie im sich öffnenden Dialogfeld “transponieren”

Markt Großbritannien Russland USA Deutschland Spanien Japan China Australien Kanada

Veränderung in % -3,60 -1,20 0,50 0,80 1,20 -0,60 -1,60 2,00 4,20

Umsatz in Mio (€) 25.054.256 15.870.145 666.548.145 105.875.879 33.658.587 15.782.145 458.471.458 12.500.145 15.870.145

2

3

MarktVeränderung in % Umsatz in Mio (€)

Großbritannien -3,60 25.054.256

Russland -1,20 15.870.145

USA 0,50 666.548.145

Deutschland 0,80 105.875.879

Spanien 1,20 33.658.587

Japan -0,60 15.782.145

China -1,60 458.471.458

1. Schreiben Sie die Zahl, die Sie auf die Spalte anwenden möchten in eineleere Zelle und kopieren Sie sie (STRG + C, oder rechte Maustastekopieren)

2. Markieren Sie die Werte in der Spalte, auf die Sie die Berechnunganwenden möchten und klicken Sie die rechte Maustaste

3. Wählen Sie im Menü “Inhalte einfügen” und dann dividieren4. Klicken Sie die rechte Maustaste & ändern Sie das Format in %

1

Inhalte einfügen Optionen (2/2)

SD SkillDay.de

Alle Werte einer Spalte auf einmal einer Berechnung unterziehen, z.B. durch 100 teilen um Werte in % umzuwandeln.

2

3

4

1

Vorgehen Tabellenformatierung

5 SD SkillDay.de

2

Erhalten Sie ein Arbeitsblatt, das klar, visuell ansprechend und interaktiv ist. Machen Sie das mit jedem Datenexport in Excel!

• Klicken Sie in eine Zelle Ihrer Daten > wählen Sie dann in Ihrer Menuleiste unterStart/Tabellen => Als Tabelle formatieren => wählen Sie Tabelle mit Überschrifteneinfügen (wenn Ihre Tabelle Überschriften hat)

• Spalten, die Sie nicht benötigen können Sie ausblenden oder löschen (rechter Klick auf die entsprechenden Spalten), die optimale Spaltenbreite erhalten Sie durch einen Klick auf die Trennlinie, Gitternetzlinien lassen sich unter dem Menüpunkt Ansichtdekativieren, Überschriften Zeile fixieren: Ebenfalls unterAnsicht => Fenster fixieren

Sortieren in Excel (1/3)

6 SD SkillDay.de

Excel bietet Ihnen drei Möglichkeiten, um Ihre Daten zu sortieren.

• Klicken Sie auf das Dreieck in der Überschrift der Spalte, die Sie sortieren möchten und wählen Siedann aus den folgenden Sortierungsoptionen:

1. Vom Kleinsten zum Größten (oder vice versa)

2. Nach Farbe (hierzu müssen Sie zunächsteine bedingte Formatierung vergeben)

3. Benutzerdefiniert (dient dazu nach mehr alseinem Kriterium zu sortieren; bedarf der vorherigen Vergabe einer Farbe

7 SD SkillDay.de

Nach Farbe sortieren:

• Markieren Sie die Spalte, die Siesortieren möchten

• Vergeben Sie eine Farbe über Start > Bedingte Formatierung > obere / untere Regeln

• Klicken Sie auf das Dreieck in der Spaltenüberschrift Ihrer Tabelle und wählen Sie nach Farbe sortieren

Sortieren in Excel (2/3)

8 SD SkillDay.de

Benutzerdefiniertes Sortieren ermöglicht Ihnen Ihre Tabelle nach mehr als einem Wert zu sortieren (im Menü Sortieren nach Farbe).

1

2

3

4

Sortieren in Excel (3/3)

Filtern in Excel

9 SD SkillDay.de

Filteroptionen blenden Zellen aus, die nicht Ihrem Filter entsprechen.

• Zum Filtern, klicken Sieebenfalls auf das Dreickeck in Ihrer Spaltenüberschrift

• Ein besonders nützlicher Filter ist der Textfilter. Er ermöglichtIhnen alle Phrasen aus einerSpalte herauszufiltern, die einbestimmtes Wort enthalten.

• Filter können wiederaufgehoben werden, indem SieFilter löschen im Filtermeüwählen.

1

2 3

Text in Spalten (1/2)

10 SD SkillDay.de

Nützliche Funktion um Inhalte aus einer Spalte auf weitere Spaltenaufzuteilen und z.B. um PDF Tabellen in Excel zu bearbeiten.

• Markieren und kopieren Sie die Daten aus der PDF Tabelle, die Sie benötigen

• Fügen Sie die Daten in Excel ein => alle Datenstehen nun in einer Spalte

• Markieren Sie die Daten in der Spalte> (STRG + Shift + Pfeiltaste unten) > wählen Sie die Registerkarte Daten > klicken Sie auf Text in Spalten

• Wählen Sie Kriterien, nachdem Ihr Text aus der Spalte getrennt werden soll (z.B. Leerzeichen, Tab)

Quelle: http://www.bmi.bund.de/SharedDocs/Downloads/DE/Nachrichten/Pressemitteilungen/2014/06/PKS2013.pdf;jsessionid=C631643A0195F60CDA85223034B0B418.2_cid295?__blob=publicationFile

Text in Spalten (2/2)

11 SD SkillDay.de

1

2

3

4

Farbskalen

12 SD SkillDay.de

Mächtige Funktion, um Intensitäten in Excel Tabellen anzuzeigen.

• Markieren Sie die Daten in Ihrer Tabelle• Gehen Sie zu Start > Bedingte Formatierung• Wählen Sie Farbskalen• Ändern Sie unter Regel bearbeiten gegebenenfalls die Farbe für

den Tiefstwert und den HöchswertQuelle: http://www.bmi.bund.de/SharedDocs/Downloads/DE/Nachrichten/Pressemitteilungen/2014/06/PKS2013.pdf;jsessionid=C631643A0195F60CDA85223034B0B418.2_cid295?__blob=publicationFile

Sparklines (Linien einfügen)

13 SD SkillDay.de

Sparklines sind praktisch, um schnell Entwicklungen und Trends zu visualisieren.

1 • Markieren Sie die Zellen, in der die Datenstehen, für die Sie eine Sparkline erstellenmöchten

• Klicken Sie in die Zelle, in der die Sparkline eingefügt werden soll

• Wählen Sie unter Diagramme Sparklines / Linien• Ändern Sie die Stärke der Linie• Fügen Sie Hochpunkte und Tiefpunkte ein• Ändern Sie die Farbe der Linie und der Punkte

nach Ihrem Bedarf• Ziehen Sie die Zelle mit Ihrer Sparkline nach

unten, um die übrigen Zeilen Ihrer Tabelle mitSparklines zu erweitern

3

2

Bedingte Formatierung (1/6)

14 SD SkillDay.de

Effektives Tool, um Zellen nach Ihren Kriterien hervorzuheben

Bedingte Formatierung (2/6)

15 SD SkillDay.de

Übung 1: Zeigen Sie mit einer Ampel, welche Suchbegriffe auf den Seiten 1,2 und 3 der Googlesuchergebnisse angezeigt werden.

1

2

3

4

16 SD SkillDay.de

1

23

Bedingte Formatierung (3/6) Übung 1: Zeigen Sie mit einer Ampel, welche Suchbegriffe auf den Seiten 1,2 und 3 der Googlesuchergebnisse angezeigt werden.

17 SD SkillDay.de

Übung 1: Sortieren Sie die Ergebnisse nach Icons

1

2

3

4 5 6 7

Bedingte Formatierung (4/6)

18 SD SkillDay.de

Übung 2: Heben Sie Zellen mit Pfeilen hervor, die eine Klickratevon mehr als 20% und kleiner / gleich 5% aufweisen.

1

2 3

Bedingte Formatierung (5/6)

19 SD SkillDay.de

Übung 3: Markieren Sie Zellen die mehr als 300 Impressionenaufweisen mit einer Farbe.

1 2

3

4

Bedingte Formatierung (6/6)

Pivot Tabellen

20 SD SkillDay.de

Mächtiges Instrument, um große Datenmengen schnellübersichtlich einzuteilen und zu analysieren.

1

3

4

2

3. Was Sieauswertenmöchten

5. Daten in Zeilen

4. Daten in Spalten

7. Gruppierung: rechter Mausklick

auf Tage

Quellle: https://www.annielytics.com/blog/excel-tips/how-to-create-and-customize-pivot-tables-in-excel/

6. Einzelfilter

8. Gruppierungnach Monaten

SVerweis

21 SD SkillDay.de

Findet einen Wert in der x-ten Spalte von links einer Tabelle auf Basis eines definierten Kriteriums

1. Hilfstabelleanlegen

2. Klicken Sie in die Zelle für das

Ergebnis

3. ÖffnenSie den

Funktions-assistenten

4. Suchen Sie die SverweisFunktion

5. Suchkriteriumaus 1.

6. Wählen SieIhe gesamteTabelle aus

7. Spalte in der das Ergebnis zu

finden ist

8. Geben Sieimmer “0” ein,

für exakteErgebnisse

Zählenwenn

22 SD SkillDay.de

Funktion zählt, wie häufig ein definiertes Kriterium auftritt.Übung: Auswertung einer Umfrage

1. Klicken Sie in die Zelle, in der das

Ergebnis stehen soll

2. Öffnen Sie den Funktions-assistenten

3. Suchen Sie nachder ZählenWenn

Funktion

4. Markieren Sie den Datenbereich in demnach dem Kriteriumgesucht werden soll5. Klicken Sie in die

Zelle, in der Kriterium steht, das gezählt werden soll

=ZÄHLENWENN($A$2:$A$89;M4)

6. Fixieren Sie den Datenbereich mit $ und

ziehen Sie Ihre Formel auf die übrigen Werte

Häufigkeit

23 SD SkillDay.de

Zählt die Häufigkeit eines Wertes bis zu einem Limit / Schwelle.Übung: Wie viele Begriffe ranken auf den Positionen 1-3, 4-7, 8-10?

1. Limits definieren2. Zelle in der

das Ergbnisstehen soll

3. Häufigkeitsfunktion

im Funktions-assisten auswählen

4. Markieren Siedie Daten in der Spalte, die das

Ergebnis enthält

5. Geben Sie die Zelle ein, die das

Limit enthält

6. Berechnen Sieeinzelnen Bereiche, z.B. “Bereich 4-7” =

131 (180-49)

Zielwertsuche (1/2)

24 SD SkillDay.de

Berechnet den benötigten Wert, um ein Ziel zu erreichen.Übung: Berechnung Menge März, um 10K Umsatz in Q1 zu erzielen.

1. Berechnen Sie das Summenprodukt ausPreisen und Mengen

2. Gehen Sie zum Reiter “Daten” und wählen sie

“Was-wäre-wenn”

3. Wählen SieZielwertsuche

Zielwertsuche (2/2)

25 SD SkillDay.de

4. Die Zelle, die das Summenprodukt enthält

6. Die Zelle, deren Wert Sie suchen

5. Zielwerteingeben

Berechnet den benötigten Wert, um ein Ziel zu erreichen.Übung: Berechnung Menge März, um 10K Umsatz in Q1 zu erzielen.

Datentabelle (Break-Even-Analyse) (1/2)

26 SD SkillDay.de

Analyse zeigt Ergebnisse für verschiedene Kombinationen.Übung: Break Even Punkte für unterschiedliche Preise / Mengen

1. Gewinngleichung

2. Erstellen Sieeine Tabelle mitallen relevanten

Menge / PreisKombinationen

3. Geben Sie die Gewinngleichungnochmal ein und markieren Sie die gesamte Tabelle

4. Gehen Siezu Daten > was-wäre-

wenn > Datentabelle

5. Geben Sie die Zelle ein, die dieVariable für den

Preis enthält(Zeile)

6. Geben Sie die Zelle ein, die die Variable für die Menge enthält

(Spalte)

27 SD SkillDay.de

BedingteFormatierung >

Farbskalen

Datentabelle (Break-Even-Analyse) (2/2)

Analyse zeigt Ergebnisse für verschiedene Kombinationen.Übung: Break Even Punkte für unterschiedliche Preise / Mengen

Effektive Datenvisualsierung

28 SD SkillDay.de

Setzen Sie Farbe ein, um Ihre Aussage zu unterstützen und entfernen Sie alle unnötigen Elemente.

Source: Data from the blog “Storytelling with data”, Cole Nussbaumer

Standard Excel Chart

Excel Chart, das eine Aussage unmittelbar verdeutlicht

Wasserfall Diagramm

29 SD SkillDay.de

Gutes Chart, um die Bestandteile einer Veränderung zu zeigen.Übung: Visualisierung der Personalentwicklung eines Standortes.

Source: Data from the blog “Storytelling with data”, Cole Nussbaumer

1. Erstellen Sieeine Tabelle mit

den Daten für eingestapeltes

Säulendiagramm

2. Dataten für die unteren Säulen

3. Daten für die oberen Säulen (die

Veränderung, die Siezeigen möchten)

4. Fügen Sie eingestapeltes

Säulendiagrammein und änderen

Sie die Füllung auf 100% transparent

Danke und viel Erfolg!

Für Fragen melden Sie sich gerne

unter:

joern@skillday.de

Mehr Exceltipps finden Sie auf

unserem Excelblog unter

http://excellytics.de/blog/