class: title-slide center middle <style>.xe__progress-bar__container { bottom:0; opacity: 1; position:absolute; right:0; left: 0; } .xe__progress-bar { height: 0.25em; background-color: #004c93; width: calc(var(--slide-current) / var(--slide-total) * 100%); } .remark-visible .xe__progress-bar { animation: xe__progress-bar__wipe 200ms forwards; animation-timing-function: cubic-bezier(.86,0,.07,1); } @keyframes xe__progress-bar__wipe { 0% { width: calc(var(--slide-previous) / var(--slide-total) * 100%); } 100% { width: calc(var(--slide-current) / var(--slide-total) * 100%); } }</style> # Grundlagen II ## Excel-Schulung Studierendenwerk Bielefeld ### Jens Klenke, M.Sc. --- class: top, left ## Funktionen — Übersicht - Grundlegende Berechnungen - Suchen & Vereinigen - Logische Funktionen - Bedingte Berechnungen - Text-Funktionen - Datum & Zeit --- class: inverse middle center # Grundlegende Berechnungen --- ### `MIN()` **Syntax:** .blockquote.exercise[ `=MIN(Zahl1; Zahl2; ...)` `=MIN(Feld1; Feld2; ...)` `=MIN(Feld1:Feld2)`] **Argumente:** - `Zahl1` — erster Wert oder Zellbereich - `;` — um Zahlen/ Felder zu seperiern - `:` — inkludiert alle Felder dazwischen -- **Beispiel:** ``` =MIN(A1:A10) =MIN(A1:A10; C1:C10) ``` --- ### `MAX()` **Syntax:** .blockquote.exercise[ `=MAX(Zahl1; Zahl2; ...)` `=MAX(Feld1; Feld2; ...)` `=MAX(Feld1:Feld2)`] **Argumente:** - `Zahl1` — erster Wert oder Zellbereich - `;` — um Zahlen/ Felder zu seperiern - `:` — inkludiert alle Felder dazwischen -- **Beispiel:** ``` =MAX(A1:A10) =MAX(A1:A10; C1:C10) ``` --- ### `PRODUKT()` **Syntax:** .blockquote.exercise[ `=PRODUKT(Zahl1; Zahl2; ...)` `=PRODUKT(Feld1; Feld2; ...)` `=PRODUKT(Feld1:Feld2)`] **Argumente:** - `Zahl1` — erster Wert oder Zellbereich - `;` — um Zahlen/ Felder zu seperiern - `:` — inkludiert alle Felder dazwischen -- **Beispiel:** ``` =PRODUKT(A1:A10) =PRODUKT(A1:A10; C1:C10) ``` --- ### `SUMME()` **Syntax:** .blockquote.exercise[ `=SUMME(Zahl1; Zahl2; ...)` `=SUMME(Feld1; Feld2; ...)` `=SUMME(Feld1:Feld2)`] **Argumente:** - `Zahl1` — erster Wert oder Zellbereich - `;` — um Zahlen/ Felder zu seperiern - `:` — inkludiert alle Felder daziwschen -- **Beispiel:** ``` =SUMME(A1:A10) =SUMME(A1:A10; C1:C10) ``` --- ### `SUMMENPRODUKT()` **Syntax:** .blockquote.exercise[ `=SUMMENPRODUKT(Feld1; Feld2; ...)`] **Argumente:** - `Feld1` — erster Zellbereich (gleiche Dimension wie Feld2!) - `Feld2` — zweiter Zellbereich, wird elementweise mit Feld1 multipliziert - `;` — um weitere Felder zu ergänzen (optional) -- **Beispiel:** ``` =SUMMENPRODUKT(A1:A10; B1:B10) =SUMMENPRODUKT(A1:A10; B1:B10; C1:C10) ``` *Multipliziert die Werte zeilenweise und summiert anschließend alle Produkte* --- ### `RUNDEN()`, `ABRUNDEN()` & `AUFRUNDEN()` **Syntax:** .blockquote.exercise[ `=RUNDEN(Zahl; Anzahl_Stellen)`] **Argumente:** - `Zahl` — Wert oder Zellbezug, der gerundet werden soll - `Anzahl_Stellen` — Anzahl der Nachkommastellen (positiv = Nachkommastellen, negativ = vor dem Komma, 0 = ganze Zahl) -- **Beispiel:** ``` =RUNDEN(3,14159; 2) =RUNDEN(1250; -2) ``` *Ergebnis: 3,14 bzw. 1300* --- ### `MEDIAN()` **Syntax:** .blockquote.exercise[ `=MEDIAN(Zahl1; Zahl2; ...)` `=MEDIAN(Feld1; Feld2; ...)` `=MEDIAN(Feld1:Feld2)`] **Argumente:** - `Zahl1` — erster Wert oder Zellbereich - `;` — um Zahlen/ Felder zu seperiern - `:` — inkludiert alle Felder dazwischen -- **Beispiel:** ``` =MEDIAN(A1:A10) =MEDIAN(A1:A10; C1:C10) ``` *Gibt den mittleren Wert zurück (robuster gegen Ausreißer als MITTELWERT)* --- ### `MITTELWERT()` **Syntax:** .blockquote.exercise[ `=MITTELWERT(Zahl1; Zahl2; ...)` `=MITTELWERT(Feld1; Feld2; ...)` `=MITTELWERT(Feld1:Feld2)`] **Argumente:** - `Zahl1` — erster Wert oder Zellbereich - `;` — um Zahlen/ Felder zu seperiern - `:` — inkludiert alle Felder dazwischen -- **Beispiel:** ``` =MITTELWERT(A1:A10) =MITTELWERT(A1:A10; C1:C10) ``` --- ### `ANZAHL()` **Syntax:** .blockquote.exercise[ `=ANZAHL(Wert1; Wert2; ...)` `=ANZAHL(Feld1; Feld2; ...)` `=ANZAHL(Feld1:Feld2)`] **Argumente:** - `Wert1` — erster Wert oder Zellbereich - `;` — um Zahlen/ Felder zu seperiern - `:` — inkludiert alle Felder dazwischen -- **Beispiel:** ``` =ANZAHL(A1:A10) =ANZAHL(A1:A10; C1:C10) ``` *Zählt nur Zellen mit **Zahlen** (Text/leere Zellen werden ignoriert)* --- ### `ANZAHL2()` **Syntax:** .blockquote.exercise[ `=ANZAHL2(Wert1; Wert2; ...)` `=ANZAHL2(Feld1; Feld2; ...)` `=ANZAHL2(Feld1:Feld2)`] **Argumente:** - `Wert1` — erster Wert oder Zellbereich - `;` — um Zahlen/ Felder zu seperiern - `:` — inkludiert alle Felder dazwischen -- **Beispiel:** ``` =ANZAHL2(A1:A10) =ANZAHL2(A1:A10; C1:C10) ``` *Zählt alle __nicht-leeren__ Zellen (auch Text, Datum, etc.)* --- ### `ANZAHLLEEREZELLEN()` **Syntax:** .blockquote.exercise[ `=ANZAHLLEEREZELLEN(Bereich)`] **Argumente:** - `Bereich` — Zellbereich, der auf leere Zellen geprüft wird -- **Beispiel:** ``` =ANZAHLLEEREZELLEN(A1:A10) ``` *Zählt alle **leeren** Zellen im angegebenen Bereich (nützlich zur Datenqualitätsprüfung)* --- class: inverse middle center # Suchen & Vereinigen --- ## SVERWEIS() **Syntax:** .blockquote.exercise[`=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])`] .font80.pull-left[ **Argumente:** | Argument | Beschreibung | |----------|--------------| | `Suchkriterium` | Wert, nach dem gesucht wird | | `Matrix` | Tabellenbereich (Suchspalte muss links sein) | | `Spaltenindex` | Nummer der Spalte mit Ergebnis | | `Bereich_Verweis` | FALSCH = exakt, WAHR = ungefähr | ] -- .font80.pull-right[ **Beispiel:** | | A | B | C | |---|---|---|---| | 1 | **ID** | **Name** | **Preis** | | 2 | 101 | Apfel | 0,50€ | | 3 | 102 | Banane | 0,30€ | | 4 | 103 | Kirsche | 2,00€ | ] -- <br> **Formel:** `=SVERWEIS(102; A2:C4; 3; FALSCH)` → Ergebnis: **0,30€** .center[ *Sucht 102 in Spalte A, gibt Wert aus Spalte C (3. Spalte) zurück* ] --- ## WVERWEIS() **Syntax:** .blockquote.exercise[`=WVERWEIS(Suchkriterium; Matrix; Zeilenindex; [Bereich_Verweis])`] .font80.pull-left[ **Argumente:** | Argument | Beschreibung | |----------|--------------| | `Suchkriterium` | Wert, nach dem gesucht wird | | `Matrix` | Tabellenbereich (Suchzeile muss oben sein) | | `Zeilenindex` | Nummer der Zeile mit Ergebnis | | `Bereich_Verweis` | FALSCH = exakt, WAHR = ungefähr | ] -- .font80.pull-right[ **Beispiel:** | | A | B | C | |---|---|---|---| | 1 | **ID** | 101 | 102 | | 2 | **Name** | Apfel | Banane | | 3 | **Preis** | 0,50€ | 0,30€ | ] <br> **Formel:** `=WVERWEIS(102; A1:C3; 3; FALSCH)` → Ergebnis: **0,30€** .center[*Sucht 102 in Zeile 1, gibt Wert aus Zeile 3 (3. Zeile) zurück*] --- ## XVERWEIS() **Syntax:** .font80.blockquote.exercise[`=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nv]; [Vergleichsmodus]; [Suchmodus])`] .font70.pull-left[ **Argumente:** | Argument | Beschreibung | |----------|--------------| | `Suchkriterium` | Wert, nach dem gesucht wird | | `Suchmatrix` | Bereich, in dem gesucht wird | | `Rückgabematrix` | Bereich, aus dem das Ergebnis stammt | | `[wenn_nv]` | Rückgabewert, falls nichts gefunden wird (optional) | | `[Vergleichsmodus]` | 0 = exakt (Standard), -1/1 = nächstkleinerer/-größerer Wert, 2 = Platzhalter | | `[Suchmodus]` | 1 = von vorne (Standard), -1 = von hinten, 2/-2 = binäre Suche | ] -- .font80.pull-right[ **Beispiel:** | | A | B | C | |---|---|---|---| | 1 | **ID** | **Name** | **Preis** | | 2 | 101 | Apfel | 0,50€ | | 3 | 102 | Banane | 0,30€ | | 4 | 103 | Kirsche | 2,00€ | ] -- .font80[ <br> **Formel:** `=XVERWEIS(102; A2:A4; C2:C4)`→ Ergebnis: **0,30€** <br> .center[*Sucht 102 in Spalte A, gibt zugehörigen Wert aus Spalte C zurück*] ] --- # Vergleich: SVERWEIS, WVERWEIS & XVERWEIS .font70[ | | **SVERWEIS** | **WVERWEIS** | **XVERWEIS** | |---|---|---|---| | **Suchrichtung** | Vertikal (Spalten) | Horizontal (Zeilen) | Beide Richtungen | | **Suchkriterium** | Wert zum Suchen | Wert zum Suchen | Wert zum Suchen | | **Matrix/Bereich** | Ganzer Bereich | Ganzer Bereich | Getrennt: Such- & Rückgabebereich | | **Rückgabe-Position** | Spaltenindex (Zahl) | Zeilenindex (Zahl) | Eigene Matrix (flexibel) | | **Suchspalte muss links sein?** | ✅ Ja | ✅ Ja (oben) | ❌ Nein | | **Fehlerbehandlung** | Separates WENNFEHLER nötig | Separates WENNFEHLER nötig | Integriert (`wenn_nv`) | | **Exakte/Ungefähre Suche** | `FALSCH`/`WAHR` | `FALSCH`/`WAHR` | `Vergleichsmodus` (0, -1, 1, 2) | | **Verfügbarkeit** | Alle Versionen | Alle Versionen | Nur Excel 365/2021+ | ] --- ## INDEX() & VERGLEICH() **VERGLEICH Syntax:** .blockquote.exercise[`=VERGLEICH(Suchkriterium; Suchmatrix; [Vergleichstyp])`] - Gibt den Index des ersten Treffers zurück -- **INDEX Syntax:** .blockquote.exercise[`=INDEX(Matrix; Zeile; [Spalte])`] - Den Wert aus einer Spalte anhand des Indexes raussuchen -- **Kombiniert:** .blockquote.exercise[`=INDEX(B:B; VERGLEICH("Suchwert"; A:A; 0))`] --- class: inverse middle center # Logische Funktionen: --- ## WENN() **Syntax:** .blockquote.exercise[`=WENN(Prüfung; [Dann_Wert]; [Sonst_Wert])`] **Argumente:** - `Prüfung` — logischer Test (z.B. `A1>10`) - `Dann_Wert` — Ergebnis wenn WAHR (optional) - `Sonst_Wert` — Ergebnis wenn FALSCH (optional) -- <br> **Beispiel:** ``` =WENN(A1>=50; "Bestanden"; "Nicht bestanden") ``` --- ## UND() **Syntax:** .blockquote.exercise[`=UND(Wahrheitswert1; [Wahrheitswert2]; ...)`] **Argumente:** - `Wahrheitswert1` — erste logische Bedingung - `[Wahrheitswert2]` — weitere Bedingungen -- <br> **Beispiel:** ``` =UND(A1>=50; A1<=100) ``` -- <br> *Ergebnis ist nur WAHR, wenn __alle__ Bedingungen erfüllt sind* --- ## ODER() **Syntax:** .blockquote.exercise[`=ODER(Wahrheitswert1; [Wahrheitswert2]; ...)`] **Argumente:** - `Wahrheitswert1` — erste logische Bedingung (erforderlich) - `[Wahrheitswert2]` — weitere Bedingungen (optional, bis zu 255) -- <br> **Beispiel:** ``` =ODER(A1="Nord"; A1="Süd") ``` -- <br> *Ergebnis ist bereits WAHR, wenn __mindestens eine__ Bedingung erfüllt ist* --- ## WENNFEHLER() **Syntax:** .blockquote.exercise[`=WENNFEHLER(Wert; Wert_falls_Fehler)`] **Argumente:** - `Wert` — die zu prüfende Formel - `Wert_falls_Fehler` — Rückgabewert bei Fehler -- **Beispiel:** ``` =WENNFEHLER(SVERWEIS(A1;B:C;2;0); "Nicht gefunden") ``` -- <br> .center[Nicht nötig bei **`XVERWEIS`**] --- class: inverse middle center # Bedingte Berechnungen --- ## SUMMEWENN() **Syntax:** .blockquote.exercise[`=SUMMEWENN(Bereich; Kriterium; [Summe_Bereich])`] **Argumente:** - `Bereich` — zu prüfender Bereich - `Kriterium` — Bedingung (Zahl, Text oder Ausdruck) - `Summe_Bereich` — zu summierender Bereich (optional) -- <br> **Beispiel:** ``` =SUMMEWENN(A:A; "Nord"; B:B) ``` --- ## SUMMEWENNS() **Syntax:** .blockquote.exercise[`=SUMMEWENNS(Summe_Bereich; Kriterien_Bereich1; Kriterium1; ...)`] **Argumente:** - `Summe_Bereich` — zu summierender Bereich - `Kriterien_Bereich1/2/...` — Prüfbereiche - `Kriterium1/2/...` — zugehörige Bedingungen -- <br> **Beispiel:** ``` =SUMMEWENNS(C:C; A:A; "Nord"; B:B; ">100") ``` -- <br> .center[Numerischer Vergleich muss in **Anführungsanzeichen** angegeben werden] --- class: inverse middle center # Text-Funktionen --- ## LINKS(), RECHTS() & TEIL() **LINKS:** .blockquote.exercise[`=LINKS(Text; Anzahl_Zeichen)`] **RECHTS:** .blockquote.exercise[`=RECHTS(Text; Anzahl_Zeichen)`] **TEIL:** .blockquote.exercise[`=TEIL(Text; Start; Anzahl_Zeichen)`] **Argumente:** - `Text` — Ausgangstext oder Zellbezug - `Anzahl_Zeichen` — wie viele Zeichen extrahiert werden - `Start` — Startposition (nur bei TEIL) --- ## TEXTVERKETTEN() **Syntax:** `=TEXTVERKETTEN(Trennzeichen; Ignorieren_leer; Text1; ...)` **Argumente:** - `Trennzeichen` – Zeichen zwischen Texten (z.B. ", ") - `Ignorieren_leer` – WAHR/FALSCH für leere Zellen - `Text1, ...` – zu verbindende Texte/Bereiche **Beispiel:** ``` =TEXTVERKETTEN(", "; WAHR; A1:A5) ``` --- class: inverse middle center # Datum & Zeit --- ## DATUM() **DATUM:** `=DATUM(Jahr; Monat; Tag)` **Argumente:** - `Jahr` — Jahreszahl - `Monat` — Monat - `Tag` — Tag -- <br> **Beispiel:** ``` =DATUM(2026; 9; 21) ``` --- ## Datumskomponenten Extrahieren **JAHR:** .blockquote.exercise[`=JAHR(ZAHL)`] **MONAT:** .blockquote.exercise[`=MONAT(ZAHL)`] **TAG:** .blockquote.exercise[`=TAG(ZAHL)`] **Argumente:** - `Zahl` — Angabe des Datums -- <br> **Beispiel:** ``` =JAHR(DATUM(2026; 9; 21)) =MONAT(DATUM(2026; 9; 21)) =TAG(DATUM(2026; 9; 21)) ``` --- ## Wochentag (Zahl) **Syntax:** .blockquote.exercise[`=WOCHENTAG(ZAHL, [TYP])`] **Argumente:** - `Zahl` — Angabe des Datums - `[TYP]` — Zählweise -- <br> **Beispiel:** ``` =WOCHENTAG(DATUM(2026; 9; 21); 1) ``` --- ## Datumskomponenten in Textformat angeben **Syntax:** .blockquote.exercise[`=TEXT(Wert, Textformat)`] **Argumente:** - `Wert` — Datum oder Zahl - `Textformat` — Ausgabeformat (Jahr = "J", Monat = "M", Tag = "T") - Von `\(1\)` bis `\(4\)` Buchstaben um kürzere oder längere Formate zu bekommen -- <br> **Beispiel:** ``` =TEXT(DATUM(2026; 9; 21); "T") =TEXT(DATUM(2026; 9; 21); "TT") =TEXT(DATUM(2026; 9; 21); "TTT") =TEXT(DATUM(2026; 9; 21); "TTTT") ``` ??? Wofür braucht man Text bei dem Jahr? t, j, m --- ## Zeitspannen berechnen (Tage) **TAGE:** .blockquote.exercise[`=TAGE(Zieldatum; Ausgangsdatum)`] **Argumente:** - `Zieldatum` — Angabe des Enddatums - `Ausgangsdatum` — Angabe des Startdatums -- <br> **Beispiel:** ``` =TAGE(DATUM(2026; 9; 22); DATUM(2026; 9; 21)) =TAGE(A1; A2) ``` --- ## Datum verschieben & Differenzen berechnen **EDATUM:** .blockquote.exercise[`=EDATUM(Ausgangsdatum; Monate)`] **DATEDIF:** .blockquote.exercise[`=DATEDIF(Ausgangsdatum; Enddatum; Einheit)`] - DATEDIF gibt es nur in der englischen Variante **Argumente:** - `Ausgangsdatum` — Startdatum - `Monate` — Anzahl Monate (positiv = vorwärts, negativ = rückwärts) - `Einheit` — "Y" (Jahre), "M" (Monate), "D" (Tage) -- <br> **Beispiel:** ``` =EDATUM(DATUM(2026;1;15); 3) =DATEDIF(DATUM(2020;5;1); HEUTE(); "Y") ``` --- ## Zeitkomponenten Extrahieren **STUNDE:** .blockquote.exercise[`=STUNDE(ZAHL)`] **MINUTE:** .blockquote.exercise[`=MINUTE(ZAHL)`] **SEKUNDE:** .blockquote.exercise[`=SEKUNDE(ZAHL)`] **Argumente:** - `Zahl` — Angabe der Uhrzeit -- <br> **Beispiel:** ``` =STUNDE(ZEIT(14; 30; 45)) =MINUTE(ZEIT(14; 30; 45)) =SEKUNDE(ZEIT(14; 30; 45)) ``` --- ## ZEIT() **Syntax:** .blockquote.exercise[`=ZEIT(Stunde; Minute; Sekunde)`] **Argumente:** - `Stunde` — Stundenwert (0-23) - `Minute` — Minutenwert (0-59) - `Sekunde` — Sekundenwert (0-59) -- <br> **Beispiel:** ``` =ZEIT(14; 30; 45) ``` ??? *Ergebnis: 14:30:45 Uhr* *Intern wird die Zeit als Dezimalzahl gespeichert (z.B. 0,604... für 14:30:45, da ein ganzer Tag = 1 entspricht)* --- ## Aktuelles Datum und Zeit **Syntax:** **Aktuelles Datum:** .blockquote.exercise[`=HEUTE()`] **Genauer:** .blockquote.exercise[`=JETZT()`] **Argumente:** - *(keine Argumente erforderlich)* -- <br> **Beispiel:** ``` =HEUTE() =JETZT() =TEXT(JETZT(); "hh:mm:ss") ``` --- ## Zeitdifferenzen berechnen **Differenz:** .font90.blockquote.exercise[ `=Endzeit - Startzeit` `=(Endzeit - Startzeit) * 24` ] .font90[ **Argumente:** - `Startzeit` — Beginn (z.B. 08:00) - `Endzeit` — Ende (z.B. 16:30) ] -- .font90[ **Beispiel:** ``` =ZEIT(16;30;0) - ZEIT(8;0;0) =(ZEIT(16;30;0) - ZEIT(8;0;0)) * 24 ``` ] -- .font90[ - *Ergebnis: 08:30 Uhr (Zeitformat) bzw. 8,5 (Dezimalzahl für Weiterverrechnung)* - Ohne `*24` bleibt das Ergebnis eine Zeit, keine "rechenbare" Einheit - Formattierung der Zelle entscheidend ]