04 Fortgeschrittene Funktionen in Excel

Ausgangsdaten

Erstellen Sie eine Tabelle mit den folgenden Daten:

Schritt 1: Datenbereich auswählen

1. Öffnen Sie Excel und geben Sie die obigen Daten in ein neues Arbeitsblatt ein.
2. Markieren Sie den gesamten Datenbereich (A1:D7).

Schritt 2: Pivot-Tabelle erstellen

1. Gehen Sie zur Registerkarte „Einfügen“.
2. Klicken Sie auf „PivotTable“ in der Gruppe „Tabellen“.
3. Im Dialogfeld „PivotTable erstellen“:
   • Stellen Sie sicher, dass der markierte Bereich korrekt ist.
   • Wählen Sie „Neues Arbeitsblatt“, um die Pivot-Tabelle in einem neuen Arbeitsblatt zu erstellen.
   • Klicken Sie auf „OK“.

Schritt 3: Felder in die Pivot-Tabelle ziehen

1. Im rechten Bereich des neuen Arbeitsblatts sehen Sie den „PivotTable-Felder“ Bereich.
2. Ziehen Sie die Felder wie folgt:
   • „Monat“ in den Bereich „Zeilen“.
   • „Region“ in den Bereich „Spalten“.
   • „Umsatz“ in den Bereich „Werte“.

Analyse der Pivot-Tabelle

1. Die Pivot-Tabelle zeigt jetzt eine Zusammenfassung der Umsätze nach Monat und Region.
2. Sie können die Daten weiter analysieren, indem Sie zusätzliche Felder hinzufügen oder entfernen, oder die Darstellung ändern.

Schritt 4: Pivot-Tabelle formatieren

1. Klicken Sie irgendwo in die Pivot-Tabelle, um die „PivotTable-Tools“ anzuzeigen.
2. Gehen Sie zur Registerkarte „Entwurf“.
3. Wählen Sie einen PivotTable-Stil aus der Galerie „PivotTable-Designs“ aus, um das Layout und die Farben anzupassen.

Erweiterte Optionen

Pivot-Tabellenfilter anwenden

1. Ziehen Sie das Feld „Produkt“ in den Bereich „Filter“.
2. Ein Dropdown-Menü erscheint über der Pivot-Tabelle.
3. Wählen Sie ein Produkt aus dem Dropdown-Menü aus, um die Daten für dieses Produkt zu filtern.

Werte anzeigen als

1. Klicken Sie in die Pivot-Tabelle auf einen der Umsatzwerte.
2. Klicken Sie mit der rechten Maustaste und wählen Sie „Wertfeldeinstellungen“.
3. Gehen Sie zur Registerkarte „Werte anzeigen als“.
4. Wählen Sie „% des Spaltensummes“, um die Umsatzwerte als Prozentsatz der Gesamtsumme der Spalte anzuzeigen.
5. Klicken Sie auf „OK“.

Beispiel Pivot-Tabelle nach Abschluss der obigen Schritte:

Schritt 5: Pivot-Tabelle aktualisieren

1. Wenn sich Ihre Ausgangsdaten ändern, können Sie die Pivot-Tabelle aktualisieren, um die neuesten Daten anzuzeigen:
   • Klicken Sie in die Pivot-Tabelle.
   • Gehen Sie zur Registerkarte „PivotTable-Analyse“ (früher „Analysieren“ genannt).
   • Klicken Sie auf „Aktualisieren“ in der Gruppe „Daten“.

Zusammenfassung

Die Verwendung von Pivot-Tabellen in Excel ermöglicht es, komplexe Datensätze effizient zu analysieren und zusammenzufassen. Durch das Ziehen von Feldern in verschiedene Bereiche der Pivot-Tabelle können Sie Ihre Daten auf unterschiedliche Weise betrachten und tiefere Einblicke gewinnen.

Um in Excel positive Werte grün und negative Werte rot zu hinterlegen, können Sie die bedingte Formatierung verwenden. Diese Funktion ermöglicht es, die Formatierung von Zellen basierend auf deren Inhalt automatisch anzupassen. Hier ist eine Schritt-für-Schritt-Anleitung, wie Sie dies erreichen können:

Schritt 1: Daten eingeben

1. Öffnen Sie ein neues Excel-Arbeitsblatt.
2. Geben Sie einige Werte in eine Spalte ein. Zum Beispiel:
   • In Zelle A1: 10
   • In Zelle A2: -5
   • In Zelle A3: 20
   • In Zelle A4: -15
   • In Zelle A5: 0

Schritt 2: Bereich auswählen

1. Markieren Sie den Bereich, den Sie formatieren möchten.
   • Zum Beispiel den Bereich A1:A5.

Schritt 3: Bedingte Formatierung anwenden

1. Gehen Sie zur Registerkarte „Start“.
2. Klicken Sie auf „Bedingte Formatierung“ in der Gruppe „Formatvorlagen“.
3. Wählen Sie „Regel verwalten...“.

Schritt 4: Regel für positive Werte erstellen

1. Klicken Sie auf „Neue Regel...“.
2. Wählen Sie „Nur Zellen formatieren, die enthalten“.
3. Stellen Sie die Regel wie folgt ein:
   • Im ersten Dropdown-Menü wählen Sie „Zellwert“.
   • Im zweiten Dropdown-Menü wählen Sie „größer als“.
   • Im Feld geben Sie 0 ein.
4. Klicken Sie auf „Formatieren...“.
5. Wählen Sie die Registerkarte „Ausfüllen“ und wählen Sie eine grüne Hintergrundfarbe.
6. Klicken Sie auf „OK“, um die Formatierung zu bestätigen.
7. Klicken Sie erneut auf „OK“, um die Regel zu erstellen.

Schritt 5: Regel für negative Werte erstellen

1. Klicken Sie auf „Neue Regel...“.
2. Wählen Sie „Nur Zellen formatieren, die enthalten“.
3. Stellen Sie die Regel wie folgt ein:
   • Im ersten Dropdown-Menü wählen Sie „Zellwert“.
   • Im zweiten Dropdown-Menü wählen Sie „kleiner als“.
   • Im Feld geben Sie 0 ein.
4. Klicken Sie auf „Formatieren...“.
5. Wählen Sie die Registerkarte „Ausfüllen“ und wählen Sie eine rote Hintergrundfarbe.
6. Klicken Sie auf „OK“, um die Formatierung zu bestätigen.
7. Klicken Sie erneut auf „OK“, um die Regel zu erstellen.

Schritt 6: Regeln anwenden und überprüfen

1. Stellen Sie sicher, dass beide Regeln im „Manager für Regeln zur bedingten Formatierung“ aufgeführt sind.
2. Klicken Sie auf „Übernehmen“ und dann auf „OK“.
3. Überprüfen Sie, ob die positiven Werte grün und die negativen Werte rot hinterlegt sind.

Beispiel:

Nach der Anwendung der bedingten Formatierung sollten Ihre Zellen wie folgt aussehen:

• Zelle A1 (10) sollte grün hinterlegt sein.
• Zelle A2 (-5) sollte rot hinterlegt sein.
• Zelle A3 (20) sollte grün hinterlegt sein.
• Zelle A4 (-15) sollte rot hinterlegt sein.
• Zelle A5 (0) sollte keine spezielle Formatierung haben, da sie weder positiv noch negativ ist.

Durch die bedingte Formatierung können Sie Ihre Daten visuell ansprechend und leicht verständlich gestalten. Diese Technik ist besonders nützlich für Finanzberichte, Bestandslisten oder jede andere Art von numerischen Daten, bei denen die Unterscheidung zwischen positiven und negativen Werten wichtig ist.

Ziel:

Dieses Arbeitsblatt führt Sie durch die Verwendung der bedingten Formatierung, um Wochenenden in einer Liste von Datumsangaben hervorzuheben. Diese Technik ist nützlich, um wichtige Daten visuell zu unterscheiden und die Analyse zu erleichtern.

Einführung

Bedingte Formatierung in Excel ermöglicht es, Zellen basierend auf bestimmten Kriterien automatisch zu formatieren. Dies kann verwendet werden, um bestimmte Tage, wie Wochenenden, hervorzuheben.

Abschnitt 1: Bedingte Formatierung für Wochenenden

Übung 1: Einfache Datumsreihe erstellen

1. Öffnen Sie ein neues Excel-Arbeitsblatt.
2. Erstellen Sie eine Liste von Datumsangaben:
   • Geben Sie in Zelle A1 das Startdatum ein, z.B. 01.05.2024.
   • Ziehen Sie den Füllziehpunkt (kleines Quadrat in der unteren rechten Ecke der Zelle) nach unten, um eine fortlaufende Reihe von Datumsangaben zu erstellen, z.B. bis zum 31.05.2024.

Übung 2: Bedingte Formatierung anwenden

1. Markieren Sie den Bereich der Datumsangaben:
   • Markieren Sie den Bereich A1:A31.
2. Bedingte Formatierung öffnen:
   • Gehen Sie zur Registerkarte „Start“.
   • Klicken Sie auf „Bedingte Formatierung“ in der Gruppe „Formatvorlagen“.
   • Wählen Sie „Neue Regel...“.
3. Regel zur Hervorhebung von Wochenenden erstellen:
   • Wählen Sie „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.

   • Geben Sie die folgende Formel ein:

     =WOCHENTAG(A1;2)>5
     

     • WOCHENTAG(A1;2): Funktion, die den Wochentag des Datums in Zelle A1 zurückgibt, wobei Montag als 1 und Sonntag als 7 gezählt wird.
     • > 5: Bedingung, die prüft, ob der Tag ein Samstag (6) oder Sonntag (7) ist.
4. Format auswählen:
   • Klicken Sie auf „Formatieren...“.
   • Wählen Sie auf der Registerkarte „Ausfüllen“ eine Hintergrundfarbe aus, um die Wochenenden hervorzuheben (z.B. Hellrot).
   • Klicken Sie auf „OK“.
5. Regel anwenden:
   • Klicken Sie erneut auf „OK“, um die Regel anzuwenden.
   • Die Wochenenden in der Liste sollten nun mit der ausgewählten Hintergrundfarbe hervorgehoben sein.

Übung 3: Bedingte Formatierung auf erweiterten Bereich anwenden

1. Dateneingabe erweitern:
   • Erweitern Sie die Liste der Datumsangaben bis zum 31.12.2024.
2. Bedingte Formatierung kopieren:
   • Markieren Sie den Bereich der neuen Datumsangaben (z.B. A32:A365).
   • Gehen Sie zur Registerkarte „Start“.
   • Klicken Sie auf „Bedingte Formatierung“.
   • Wählen Sie „Regeln verwalten...“.
   • Wählen Sie die zuvor erstellte Regel aus und klicken Sie auf „Regel bearbeiten...“.
   • Ändern Sie den Bereich, auf den die Regel angewendet wird, auf den gesamten Bereich der Datumsangaben (z.B. A$365).
   • Klicken Sie auf „OK“.
3. Ergebnis überprüfen:
   • Stellen Sie sicher, dass alle Wochenenden im erweiterten Bereich entsprechend hervorgehoben sind.

Ausgangsdaten

Erstellen Sie eine Tabelle mit den folgenden Daten:

1. WENN-DANN-Logik (IF-Logik)

Beschreibung:

Die WENN-Funktion führt eine logische Prüfung durch und gibt einen Wert zurück, wenn die Bedingung wahr ist, und einen anderen Wert, wenn sie falsch ist.

Syntax:

=WENN(Prüfung; Dann_Wert; Sonst_Wert)

Übung

1. Geben Sie in Zelle E1 die Überschrift „Bonus“ ein.

2. Geben Sie in Zelle E2 die folgende Formel ein:

   =WENN(C2>50000; "Ja"; "Nein")
   

   • Diese Formel prüft, ob das Gehalt in Zelle C2 größer als 50000 ist. Wenn ja, wird „Ja“ zurückgegeben, ansonsten „Nein“.
3. Verwenden Sie die Autofill-Funktion, um die Formel auf die Zellen E3 bis E7 zu kopieren.

2. SUMMEWENN (SUMIF)

Beschreibung:

Die SUMMEWENN-Funktion summiert die Werte in einem Bereich, die einem angegebenen Kriterium entsprechen.

Syntax:

=SUMMEWENN(Bereich; Kriterium; [Summe_Bereich])

Übung

1. Geben Sie in Zelle G1 die Überschrift „Summe Vertrieb“ ein.

2. Geben Sie in Zelle G2 die folgende Formel ein:

   =SUMMEWENN(B2:B7; "Vertrieb"; C2:C7)
   

   • Diese Formel summiert die Gehälter in Spalte C, wenn der Wert in Spalte B „Vertrieb“ ist.

3. SVERWEIS (VLOOKUP)

Beschreibung:

Die SVERWEIS-Funktion durchsucht die erste Spalte einer Tabelle und gibt einen Wert in derselben Zeile aus einer anderen Spalte zurück.

Syntax:

=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])

• Suchkriterium: Der Wert, nach dem gesucht werden soll.
• Matrix: Der Bereich, der durchsucht werden soll.
• Spaltenindex: Die Spaltennummer in der Matrix, aus der der Wert zurückgegeben werden soll.
• Bereich_Verweis:
  • WAHR (TRUE): Sucht nach der nächstkleineren Übereinstimmung und setzt voraus, dass die erste Spalte der Matrix sortiert ist.
  • FALSCH (FALSE): Sucht nach einer exakten Übereinstimmung und gibt den Fehlerwert #NV zurück, wenn keine exakte Übereinstimmung gefunden wird.

Übung 1: Exakte Übereinstimmung

Angenommen, wir haben folgende Tabelle:

Ziel: Finden Sie die Abteilung von Clara.

Formel:

=SVERWEIS("Clara"; A2:D7; 2; FALSCH)

• Suchkriterium: "Clara"
• Matrix: A2:D7
• Spaltenindex: 2 (die Spalte "Abteilung")
• Bereich_Verweis: FALSCH (exakte Übereinstimmung)

Ergebnis: "Vertrieb"

Übung 2: Ungefähre Übereinstimmung

Angenommen, wir haben folgende Tabelle, in der die Gehälter aufsteigend sortiert sind:

Ziel: Finden Sie den Namen der Person, deren Gehalt am nächsten zu 53000 liegt.

Formel:

=SVERWEIS(53000; A2:B7; 2; WAHR)

• Suchkriterium: 53000
• Matrix: A2:B7
• Spaltenindex: 2 (die Spalte "Name")
• Bereich_Verweis: WAHR (ungefähre Übereinstimmung)

Ergebnis: "Felix"

Erläuterung: Da 53000 nicht exakt in der Tabelle vorhanden ist und der Parameter WAHR verwendet wird, sucht die Funktion nach der nächstkleineren Zahl, die 53000 am nächsten kommt, was 52000 ist, und gibt den entsprechenden Namen "Felix" zurück.

4. INDEX und VERGLEICH

Beschreibung:
Die INDEX-Funktion gibt den Wert einer bestimmten Zelle in einem Bereich zurück, basierend auf den angegebenen Zeilen- und Spaltennummern. Die VERGLEICH-Funktion sucht nach einem Wert in einem Bereich und gibt die relative Position dieses Wertes zurück.

Syntax:

=INDEX(Matrix; Zeile; [Spalte])
=VERGLEICH(Suchkriterium; Suchmatrix; [Vergleichstyp])

A) Finden Sie den Namen basierend auf dem Gehalt

Verwenden der Funktion VERGLEICH

Die VERGLEICH-Funktion wird verwendet, um die Position eines Wertes in einem Bereich zu finden. In diesem Beispiel suchen wir die Position des Gehalts in der Gehaltsspalte.

1. Geben Sie in Zelle F1 die Überschrift „Suchgehalt“ ein.
2. Geben Sie in Zelle F2 das Gehalt ein, nach dem Sie suchen möchten, z.B. 55000.
3. Geben Sie in Zelle G1 die Überschrift „Position“ ein.

4. Geben Sie in Zelle G2 die folgende Formel ein:

   =VERGLEICH(F2; C2:C7; 0)
   

   • Diese Formel gibt die Position des Wertes in F2 (55000) in der Spalte C zurück.

Verwenden der Funktion INDEX

Die INDEX-Funktion gibt den Wert einer Zelle in einem bestimmten Bereich zurück, basierend auf der Zeilen- und Spaltennummer.

1. Geben Sie in Zelle H1 die Überschrift „Name“ ein.

2. Geben Sie in Zelle H2 die folgende Formel ein:

   =INDEX(A2:A7; G2)
   

   • Diese Formel verwendet die Position aus Zelle G2, um den entsprechenden Namen in der Spalte A zu finden.

Zusammenführen von INDEX und VERGLEICH

Um die Suche effizienter zu gestalten, können Sie die Funktionen INDEX und VERGLEICH in einer einzigen Formel kombinieren.

1. Geben Sie in Zelle J1 die Überschrift „Name für Suchgehalt“ ein.

2. Geben Sie in Zelle J2 die folgende Formel ein:

   =INDEX(A2:A7; VERGLEICH(F2; C2:C7; 0))
   

   • Diese Formel sucht nach dem Gehalt in Zelle F2 in der Spalte C, findet die Position und verwendet diese Position, um den Namen in Spalte A zu finden.

B) Finden Sie die Abteilung basierend auf dem Eintrittsdatum

1. Geben Sie in Zelle K1 die Überschrift „Suchdatum“ ein.
2. Geben Sie in Zelle K2 das Eintrittsdatum ein, nach dem Sie suchen möchten, z.B. 01.07.2020.
3. Geben Sie in Zelle L1 die Überschrift „Abteilung“ ein.

4. Geben Sie in Zelle L2 die folgende Formel ein:

   =INDEX(B2:B7; VERGLEICH(K2; D2:D7; 0))
   

   • Diese Formel sucht nach dem Datum in Zelle K2 in der Spalte D, findet die Position und verwendet diese Position, um die Abteilung in Spalte B zu finden.

Zusammenfassung

Die Kombination der Funktionen INDEX und VERGLEICH bietet eine flexible Methode, um Werte in Excel zu suchen und zurückzugeben, unabhängig davon, in welcher Spalte die Suchkriterien sich befinden. Dies ermöglicht komplexere und dynamischere Datenabfragen, die mit SVERWEIS allein nicht möglich wären.

5. TEXT-Funktionen (VERKETTEN, TEXT)

Beschreibung:
Die VERKETTEN-Funktion verbindet mehrere Textstrings zu einem einzigen Textstring. Die TEXT-Funktion formatiert eine Zahl und gibt sie als Text zurück.

Syntax:

=VERKETTEN(Text1; Text2; ...)
=TEXT(Wert; Textformat)

Beispiel:

1. Geben Sie in Zelle M1 die Überschrift „Name und Abteilung“ ein.

2. Geben Sie in Zelle M2 die folgende Formel ein:

   =VERKETTEN(A2; " - "; B2)
   

   • Diese Formel verbindet den Namen und die Abteilung in Spalte A und B.

Übung 5: Namen und Abteilung verbinden

• Geben Sie die Formel in Zelle M2 ein.
• Verwenden Sie Autofill, um die Formel auf die Zellen M3 bis M7 zu kopieren.

Zusammenfassung

Dieses Arbeitsblatt bietet eine umfassende Anleitung zur Verwendung fortgeschrittener Formeln und Funktionen in Excel, einschließlich der WENN-Funktion, SUMMEWENN, SVERWEIS, INDEX und VERGLEICH sowie Textfunktionen. Durch die praktischen Übungen werden Sie in der Lage sein, diese Konzepte effektiv anzuwenden, um komplexe Berechnungen und Datenanalysen durchzuführen.

Ausgangsdaten

Erstellen Sie eine Tabelle mit den folgenden Daten:

Schritt 1: Datenbereich auswählen

1. Öffnen Sie Excel und geben Sie die obigen Daten in ein neues Arbeitsblatt ein.
2. Markieren Sie den gesamten Datenbereich (A1:D7).

Daten sortieren

Schritt 2: Einfache Sortierung

1. Markieren Sie den gesamten Datenbereich, den Sie sortieren möchten.
   • Zum Beispiel den Bereich A1:D7.
2. Gehen Sie zur Registerkarte „Daten“.
3. Klicken Sie auf das Symbol „Sortieren“ in der Gruppe „Sortieren und Filtern“.

Schritt 3: Sortierung nach einer Spalte

1. Ein Dialogfeld „Sortieren“ öffnet sich.
2. Wählen Sie die Spalte aus, nach der sortiert werden soll.
   • Beispiel: Wählen Sie „Gehalt“.
3. Wählen Sie die Sortierfolge:
   • „Von klein nach groß“ oder „Von groß nach klein“.
4. Klicken Sie auf „OK“.
5. Das Ergebnis sollte die Tabelle nach dem Gehalt in aufsteigender oder absteigender Reihenfolge sortieren.

Übung 1: Sortieren nach Gehalt

• Markieren Sie den Datenbereich (A1:D7).
• Gehen Sie zur Registerkarte „Daten“.
• Klicken Sie auf „Sortieren“.
• Wählen Sie „Gehalt“ und „Von groß nach klein“.
• Klicken Sie auf „OK“.

Schritt 4: Mehrstufige Sortierung

1. Wählen Sie den gesamten Datenbereich aus (A1:D7).
2. Gehen Sie zur Registerkarte „Daten“.
3. Klicken Sie auf „Sortieren“.
4. Im Dialogfeld „Sortieren“:
   • Klicken Sie auf „Ebene hinzufügen“.
   • Wählen Sie die erste Sortierebene: „Abteilung“, „Werte“, „Von A bis Z“.
   • Wählen Sie die zweite Sortierebene: „Gehalt“, „Werte“, „Von groß nach klein“.
   • Klicken Sie auf „OK“.
5. Das Ergebnis sollte die Tabelle zuerst nach Abteilung und dann nach Gehalt innerhalb jeder Abteilung sortieren.

Übung 2: Mehrstufiges Sortieren

• Markieren Sie den Datenbereich (A1:D7).
• Gehen Sie zur Registerkarte „Daten“.
• Klicken Sie auf „Sortieren“.
• Wählen Sie die erste Sortierebene: „Abteilung“, „Werte“, „Von A bis Z“.
• Fügen Sie eine weitere Ebene hinzu: „Gehalt“, „Werte“, „Von groß nach klein“.
• Klicken Sie auf „OK“.

Daten filtern

Schritt 5: Filter anwenden

1. Wählen Sie den gesamten Datenbereich aus (A1:D7).
2. Gehen Sie zur Registerkarte „Daten“.
3. Klicken Sie auf das Symbol „Filtern“ in der Gruppe „Sortieren und Filtern“.
   • Über jeder Spaltenüberschrift erscheinen Dropdown-Pfeile.

Schritt 6: Filterkriterien festlegen

1. Klicken Sie auf den Dropdown-Pfeil in der Spaltenüberschrift „Abteilung“.
2. Deaktivieren Sie „Alle auswählen“.
3. Aktivieren Sie nur „HR“.
4. Klicken Sie auf „OK“.
   • Das Ergebnis zeigt nur die Zeilen, in denen die Abteilung „HR“ ist.

Übung 3: Filtern nach Abteilung „HR“

• Markieren Sie den Datenbereich (A1:D7).
• Gehen Sie zur Registerkarte „Daten“.
• Klicken Sie auf „Filtern“.
• Klicken Sie auf den Dropdown-Pfeil in der Spaltenüberschrift „Abteilung“.
• Deaktivieren Sie „Alle auswählen“.
• Aktivieren Sie „HR“.
• Klicken Sie auf „OK“.

Schritt 7: Filter für mehrere Kriterien

1. Klicken Sie auf den Dropdown-Pfeil in der Spaltenüberschrift „Gehalt“.
2. Wählen Sie „Zahlenfilter“ > „Größer als“.
3. Geben Sie im Feld „ist größer als“ den Wert „46000“ ein.
4. Klicken Sie auf „OK“.
   • Das Ergebnis zeigt nur die Zeilen, in denen das Gehalt größer als 46000 ist.

Übung 4: Filtern nach Gehalt größer als 46000

• Markieren Sie den Datenbereich (A1:D7).
• Gehen Sie zur Registerkarte „Daten“.
• Klicken Sie auf „Filtern“.
• Klicken Sie auf den Dropdown-Pfeil in der Spaltenüberschrift „Gehalt“.
• Wählen Sie „Zahlenfilter“ > „Größer als“.
• Geben Sie „46000“ ein.
• Klicken Sie auf „OK“.