O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Keine Angst vor Microsoft Excel! Formeln verstehen, selber aufbauen und erfolgreich einsetzen

Book Description

Dieses Buch erklärt anhand verständlicher Beispiele, wie Sie häufig einsetzbare Formeln Schritt für Schritt anlegen und flexibel anpassen können.

Table of Contents

  1. Keine Angst vor Microsoft Excel!: Formeln verstehen, selber aufbauen und erfolgreich einsetzen
  2. Vorwort
    1. Ein Buch, das Lust auf den Umgang mit Excel macht
    2. Für wen ist dieses Buch geschrieben?
    3. Wie ist das Buch aufgebaut?
    4. Gutes entsteht im Team oder Wer hat an diesem Buch mitgewirkt?
  3. 1. Besser als ein Taschenrechner: Formeln eingeben und FERTIG!
    1. Den Aufbau von Formeln verstehen
      1. Die Grundrechenarten in Excel
        1. Eine Formel zum Addieren aufstellen
        2. Eine Formel zum Subtrahieren eingeben
        3. Eine Formel zum Multiplizieren aufbauen
        4. Eine Formel zum Dividieren aufstellen
    2. Formeln flexibel machen mit Zellbezügen
      1. Für eine Kalkulation die Grundrechenarten nutzen
        1. Die Summe der festen Kosten in zwei Schritten ermitteln
        2. Die variablen Kosten pro Teilnehmer ermitteln
          1. Die Hotel- und Eintrittskosten per Multiplikation ermitteln
        3. Die Summe der variablen Kosten bilden
      2. Den Netto- und den Bruttopreis berechnen
        1. Notwendiger Zwischenschritt: Die Mehrwertsteuer ermitteln
        2. Den Bruttobetrag mithilfe der Funktion SUMME berechnen
      3. Die Kosten pro Teilnehmer und den zu zahlenden Teilnahmepreis kalkulieren
        1. Die Nettokosten pro Teilnehmer ermitteln
        2. Den Zuschuss berücksichtigen
        3. Den Brutto-Teilnahmepreis berechnen
      4. Zwischenfazit zur fertigen Lösung
    3. In Formeln häufig gebrauchte Elemente einfacher aufrufen
    4. Werte und Formeln zeitsparend auf andere Zellen übertragen
      1. Sich wiederholende Werte mühelos kopieren
      2. Werte schnell und einfach mit dem Ausfüllkästchen auf angrenzende Zellen übertragen
        1. Die AutoAusfüllen-Funktion für Spalte E und F nutzen
        2. Mit Turbo: AutoAusfüllen per Doppelklick für Spalte G
        3. Schnelle Eingabe in mehrere Zellen per Tastenkombination
      3. Netto berechnen und in der Spalte kopieren
        1. Die Formel zum Berechnen des Nettobetrags aufstellen
        2. Die Netto-Formel innerhalb der Spalte kopieren
      4. Mehrwertsteuer und Bruttobetrag berechnen
        1. Die Mehrwertsteuer per Multiplikation ermitteln
        2. Den Bruttobetrag mit der Summe-Funktion ermitteln
    5. Relative und absolute Bezüge verstehen und richtig nutzen
      1. Einen einfachen Bezug auf eine Zelle herstellen
        1. Zellbezüge in einer Formel nachvollziehen
      2. Zwischen relativen und absoluten Bezügen unterscheiden
        1. Zwischenfazit
      3. Einen absoluten Bezug für Spalte D herstellen
      4. Für weitere Spalten absolute Bezüge herstellen
      5. Den Nettopreis berechnen
      6. Relative und absolute Bezüge in einer Formel
      7. Schnelle Summenberechnung für die »Brutto«-Spalte
    6. Daten aus anderen Tabellenblättern per Formel übernehmen
      1. Die Anzahl der angemeldeten Teilnehmer summieren
      2. Daten aus dem Blatt »Basiskalkulation« nutzen
        1. Berechnung der festen Kosten pro Person
        2. Weitere Daten aus dem Blatt »Basiskalkulation« verwenden
  4. 2. Formeln aus dem Baukasten: Funktionen verstehen und nutzen
    1. Was ist eine Funktion im Unterschied zu einer Formel?
    2. Syntax: Der Aufbau von Funktionen
      1. Funktionen, die ohne Aufwand Ergebnisse liefern
      2. Was eine Funktion braucht und was unwichtig ist
      3. Halbjahresauswertung mit der Funktion SUMME
      4. Wissenswertes zu Argumenten
    3. Vier verschiedene Wege, eine Funktion einzugeben
      1. Ganz einfach: »AutoSumme« nutzen
      2. Echt komfortabel: Die Ergebniszeile verwenden
      3. Mit Autopilot: Den Funktions-Assistenten nutzen
      4. Ohne jede Hilfe: Funktionen manuell eingeben
    4. »AutoSumme« kann mehr als nur addieren
      1. Summe geteilt durch Anzahl oder einfach MITTELWERT
      2. Was ist der kleinste, was der größte Wert?
      3. Wie viele Einträge sind es eigentlich?
        1. Analyse der Resultate und warum die Werte abweichen
        2. Mit ANZAHL2 nicht nur Zahlen zählen
    5. Elf auf einen Streich: In »intelligenten Tabellen« die Ergebniszeile nutzen
      1. Die Vorteile einer »intelligenten« Tabelle
      2. Mit drei Mausklicks zur »intelligenten« Tabelle
      3. Die Ergebniszeile anzeigen lassen
      4. Formeln im Direktzugriff: Die Möglichkeiten der Ergebniszeile nutzen
        1. Den Spaltendurchschnitt in der Ergebniszeile anzeigen
        2. Die Spaltensummen in der Ergebniszeile anzeigen
      5. Die Funktionen der Ergebniszeile unter der Lupe
        1. Bedeutung der Zahlen in der Funktion TEILERGEBNIS
        2. Warum in der Ergebniszeile TEILERGEBNIS genutzt wird
        3. Flexibel erweiterbar: Neue Projekte werden in der Ergebniszeile sofort berücksichtigt
    6. Ganz einfach: Funktionen mithilfe des Assistenten Schritt für Schritt aufbauen
      1. Den Mittelwert für mehrere Bereiche ermitteln
        1. Excel denkt mit: Kontinuierliche Formelüberwachung
      2. Priorität festlegen mit WENN, UND und ISTLEER
    7. Alles selbst gemacht: Funktionen in der Bearbeitungsleiste aufbauen
      1. Die Formel in der Spalte kopieren
    8. Eine Übersicht: Welche Arten von Funktionen gibt es?
  5. 3. Zählen und Summieren: Werte nur nach bestimmten Kriterien berechnen
    1. Aktuelle Statusanzeige: Mit einfachen Funktionen für den Überblick sorgen
      1. Die Daten für den Auswertungsbereich definieren
        1. Die Datenliste vorbereiten: Einen Namen vergeben
        2. Die Basisinformationen für die Statusanzeige ermitteln
      2. Wieso ist Anzahl nicht gleich Anzahl?
        1. Mit ANZAHL2 nur die Zellen zählen, die Text enthalten
        2. Mit ANZAHL nur die Zellen zählen, die Zahlen enthalten
        3. Genial für offene Posten: ANZAHLLEEREZELLEN zählt unbezahlte Lieferungen
    2. Mehr Komfort: Das Bedienen der Statusanzeigen deutlich vereinfachen
      1. Dropdownlisten anlegen, die dynamisch sind
        1. Die Eingabe der Kriterien so richtig komfortabel machen
        2. Die Wahl der Kriterien per Datenüberprüfung erleichtern
          1. Flexible Wahl des Jahres per Mausklick
          2. Flexible Auswahl per Mausklick auch für »Lieferanten« und »Konten«
        3. Gut gerüstet: Testen Sie die Dynamik der Dropdownlisten
    3. Selektiv berechnen: Eine Auswertung an ein bestimmtes Kriterium knüpfen
      1. Zu Beginn: Ausgewählte Basisdaten ermitteln
      2. Zählen, aber nur wenn: Die Anzahl der Lieferungen eines bestimmten Lieferanten ermitteln
        1. Das leistet ZÄHLENWENN
      3. Zählen, aber erst ab einem bestimmten Betrag
      4. Ein Kriterium definieren, aber wie?
      5. Profitechnik: * und ? als Jokerzeichen nutzen und Zeichen beim Festlegen des Kriteriums ersetzen
        1. Beispiele für das Verwenden von Jokerzeichen
      6. Summenbildung, aber unter einer Bedingung ...
        1. Alle Lieferungen aus einem bestimmten Jahr addieren
        2. Alle Lieferungen eines bestimmten Lieferanten addieren
        3. Alle Lieferungen zu einem bestimmten Konto addieren
        4. Jokerzeichen auch in der SUMMEWENN-Funktion verwenden
    4. Noch selektiver: Eine Auswertung an mehrere Bedingungen knüpfen
      1. Summenbildung, wenn DIES und DAS erfüllt ist ...
        1. Die Rechnungssumme für bestimmte Artikel in einem ausgewählten Zeitraum ermitteln mit SUMMEWENNS
        2. Betrag der bezahlten Rechnungen für bestimmte Konten, Lieferanten und Jahre berechnen mit SUMMEWENNS
          1. Das Kriterium für bezahlte Rechnungen eingeben
        3. Betrag ausgewählter unbezahlter Rechnungen ermitteln
        4. Wie Excel bei SUMMEWENNS die leeren Zellen findet
    5. Aussagen auf den Punkt bringen: Informationen verketten
      1. Auskunft über bezahlte Rechnungen erhalten
      2. Auskunft über unbezahlte Rechnungen erhalten
    6. Keine verfälschten Resultate: Extrem- und Nullwerte nicht mitberechnen
      1. Realistische Durchschnittswerte durch diverse Einschränkungen
        1. Verschiedene Szenarien der Mittelwertberechnung testen
      2. Durchschnitt ohne Nullwerte berechnen
      3. Nullwerte auch ohne Matrixformel ausschließen
    7. Ziemlich genial: Wichtige Aussagen per Matrixformel gewinnen
  6. 4. Keine Angst vor Logik: Daten auswerten mit WENN & Co.
    1. Mit WENN Berechnungen in eine bestimmte Richtung leiten
      1. Syntax und Einsatzgebiete von WENN
      2. Mit WENN den richtigen Mehrwertsteuersatz finden
        1. Die Liste der Leistungen vorbereiten
        2. Korrekte Wahl der Leistungsart per Mausklick
        3. Die Mehrwertsteuersätze an einer zentralen Stelle festlegen
        4. Den passenden Mehrwertsteuersatz zuweisen
        5. Den Bruttobetrag mit dem passenden Mehrwertsteuersatz berechnen
    2. Fehlerquellen ausschließen mit WENN sowie WENNFEHLER
      1. Sinnvolle Kostenberechnungen durch Prüfung auf Vollständigkeit
        1. Ist die Differenz zwischen Soll- und Ist-Kosten negativ oder positiv?
        2. Soll- und Ist-Kosten prozentual im Vergleich
      2. Unschöne Fehlerwerte in Berechnungen ganz einfach vermeiden mit WENNFEHLER
    3. Statusinformationen liefern mit WENN
    4. Mehr als zwei mögliche Ergebnisse durch Verschachteln von WENN
    5. Noch mehr Bedingungen definieren mit UND, ODER und NICHT
      1. Positiv laufende Projekte herausstellen
      2. Diverse Kriterien zu einer Entscheidung zurate ziehen
      3. Kritische Projekte durch den Status »Achtung« kenntlich machen
      4. Ein weiteres Beispiel für die Kombination von WENN und UND
  7. 5. Sicher mit Datum und Zeit umgehen: Termine und Zeitauswertungen im Griff
    1. Wichtig fürs Verständnis: Wie Excel mit Datums- und Zeitangaben umgeht
      1. Wichtig beim Eingeben von Datum und Zeit
        1. Zwei oder vier Ziffern für die Angabe des Jahres?
        2. Aktuelles Datum oder aktuelle Zeit fast auf Knopfdruck
      2. Datums- und Zeitfunktionen ganz einfach nutzen
      3. Ein Datum in die zugehörige serielle Zahl umwandeln
      4. Die Konsequenzen der Eingabe von Datums- und Uhrzeitwerten auf das Zellformat kennen und meistern
        1. Sofort-Pannenhilfe: Störende Datumsformate beseitigen
    2. Die Anzeige von Datumsangaben mit Formaten steuern
      1. Die drei Platzhalter, die das Aussehen eines Datums beeinflussen
        1. Benutzerdefiniert: Ein Datum bedarfsgerecht formatieren
        2. Oft gebraucht: Zum Datum noch den Wochentag anzeigen
      2. Datumsformate, die Excel zur Auswahl stellt
        1. Formate komfortabel zuweisen mit zwei Mausklicks
        2. Deutlich mehr vorgefertigte Datums- und Zeitformate
    3. Lange Datumslisten ganz ohne Tipparbeit
      1. Terminliste ohne Wochenende oder nur mit den Monatsersten
      2. Datumsliste für ein komplettes Quartal erstellen
    4. Liefertermine steuern mit WOCHENTAG und KALENDERWOCHE
      1. Die Kalenderwoche zu einem Datum ermitteln
      2. Liefertermine am Wochenende vermeiden mit WENN und WOCHENTAG
        1. Die Kombination von WENN und WOCHENTAG aufbauen
        2. Die Ergebnisse in Form bringen und finalisieren
    5. Perfekte Geburtstagsliste: Das Alter berechnen und die Jubilare anzeigen
      1. Das Alter auf den Tag genau berechnen
        1. Gut versteckt und extrem nützlich: Die Funktion DATEDIF
        2. Auch auf den Tag genau: BRTEILJAHRE und GANZZAHL
        3. Fazit: Die drei Lösungswege im Vergleich
      2. Das aktuelle Alter und runde Geburtstage ermitteln
        1. Das aktuelle Alter berechnen mit DATEDIF und HEUTE
        2. Jubilare mit rundem Geburtstag sofort sehen
      3. Personen mit runden Geburtstagen hervorheben
      4. Den Zeitraum für angezeigte Jubiläen steuern
        1. Maximaler Bedienkomfort durch ein Drehfeld
        2. Die anzuzeigenden Jubilare um ein Kriterium erweitern
    6. Mit nur einer Formel: Jahreskalender mit hervorgehobenen Wochenenden
      1. Die Formel für den Jahreskalender aufbauen
        1. Die Formel noch verbessern
      2. Mittels bedingter Formatierung die Wochenenden farbig hervorheben
    7. Mit Uhrzeiten rechnen und dabei typische Excel-Probleme meistern
      1. Blick hinter die Kulissen: Wie Excel mit Stunden und Minuten rechnet
        1. Per Formel eine Uhrzeit in eine Dezimalzahl umwandeln
      2. Zeiterfassung ganz einfach: Die täglichen und die wöchentlichen Arbeitszeiten ermitteln
        1. Die Tageswerte zu einem Wochenergebnis summieren
        2. Die Lösung: Ein spezielles Zahlenformat für Uhrzeiten
      3. Lohnberechnung: Fehler beim Multiplizieren mit Arbeitsstunden vermeiden
      4. Nachtarbeit: Wenn über die Tagesgrenze hinweg gerechnet werden muss
      5. Wie Excel auch mit negativen Zeitwerten rechnet
        1. Excel hat zwei Datumssysteme: 1900 oder 1904
          1. Die Folgen beim Wechsel zwischen den beiden Datumssystemen
        2. Das Datumssystem für eine Arbeitsmappe ändern
  8. 6. Erfolgreiche Projektarbeit: Termine und Dauer flexibel berechnen und kontrollieren
    1. Die Dauer von Projekten in Kalendertagen und in Arbeitstagen berechnen
      1. Optimaler Blick auf die Termine durch Wochentagsanzeige
      2. Die Dauer in Kalendertagen berechnen
      3. Von den Kalendertagen automatisch die Wochenenden abziehen lassen
    2. Beim Berechnen der Arbeitstage auch die Feiertage berücksichtigen
      1. Formeleingabe erleichtern: Dem Zellbereich für Feiertage einen Namen geben
    3. Wenn die Arbeitswoche nicht fünf, sondern sechs oder nur vier Tage hat
      1. Für eine 6-Tage-Arbeitswoche die tatsächlich verfügbaren Arbeitstage berechnen
      2. Für eine 4-Tage-Arbeitswoche die tatsächlich verfügbaren Arbeitstage ermitteln
    4. Start- und Endtermine in Projekten sowie Kalenderwochen berechnen
      1. Das Enddatum eines Projekts berechnen
      2. Den Termin für den Projektstart berechnen
      3. Mit der 21 die richtige Kalenderwoche ermitteln
    5. Die Abfolge und Dauer von Projekten mit einem Gantt-Diagramm zeigen
  9. 7. Runden ja, aber ohne Fehler
    1. Zielgenau auf- und abrunden statt irritierender optischer Täuschung
    2. Nachkommastellen vermeiden mithilfe von RUNDEN
      1. Den Verbrauch erfassen und die Daten auswerten
      2. Die Funktion RUNDEN verwenden
    3. Runden von Zahlen auf volle Hundert oder Zehntausend
      1. Den Tagesdurchschnitt auf volle Hundert runden
      2. Den Jahresdurchschnitt auf volle Zehntausend runden
    4. Abläufe optimieren mit AUFRUNDEN, OBERGRENZE & Co.
      1. Bestelloptimierung mit einzelnen Rundungsfunktionen
      2. Variante 1: Runden der Werte nach Dezimalstellen
      3. Variante 2: Aufrunden der Werte mithilfe der Schrittweite in der Funktion OBERGRENZE
      4. Zwischenfazit: Was leistet RUNDEN und was nicht
    5. Bestelloptimierung durch Kombination verschiedener Rundungsfunktionen
      1. Preis optimieren durch Bestellung möglichst vieler großer Liefereinheiten, aber ohne Überbestände
      2. Die Bestellmenge optimieren
    6. Zeiten in bestimmten Intervallen runden mit AUFRUNDEN und VRUNDEN
      1. Jede angefangene Stunde zählt
      2. Projektzeitabrechnung im Viertelstundentakt
      3. Arbeitszeitabrechnung in vollen Minuten
    7. Geldbeträge auf volle Euro oder in 50-Cent-Schritten runden
      1. Bei Gebühren auf volle 10 oder 50 Euro abrunden
        1. Mit ABRUNDEN den unteren Zehnerwert berechnen
        2. Mit UNTERGRENZE noch flexibler abrunden
      2. Arbeitsentgelt auf 50 Cent runden
        1. Zunächst das tatsächliche Entgelt berechnen
        2. Das tatsächliche Entgelt »gerecht« auf- oder abrunden
    8. Zusammenfassung und Ausblick
      1. Welche Parameter stehen beim Runden zur Verfügung?
      2. Welche Funktion rundet in welche Richtung?
      3. Lust auf noch mehr Rundungsfunktionen?
  10. 8. Wichtige und abweichende Daten automatisch hervorheben
    1. Bedingte Formatierung: Das bringt sie und so funktioniert sie
      1. Anwendungsfälle für die bedingte Formatierung
      2. Nutzen bedingter Formatierung an einem Beispiel
      3. Wie funktioniert die bedingte Formatierung?
      4. Welche Formatierungsoptionen sind verfügbar?
      5. Wie werden bedingte Formate zugewiesen?
      6. Vorhandene Regeln nutzen und Zeit sparen
        1. Die Gruppe »Regeln zum Hervorheben von Zellen«
        2. Die Gruppe »Obere/untere Regeln«
        3. Attraktiv: Bedingte Formatierung mit grafischen Elementen
    2. Beispiel Produktionskontrolle: Grenzwerte, Fehlerquoten und Plan-Ist-Vergleich
      1. Werte außerhalb vordefinierter Grenzen hervorheben
        1. Das Unterschreiten einer Mindestmenge signalisieren
      2. Fehlerquoten im Blick: Das Einhalten von Grenzwerten überwachen
        1. Grünes Licht bei Nullfehlerproduktion
        2. Fehlerquoten über dem Durchschnitt bedeuten »Obacht«
        3. Grenzwertüberschreitung für Fehlerquoten herausstellen
        4. Warum die Reihenfolge der Regeln wichtig ist
      3. Unterschied Plan/Ist durch Datenbalken veranschaulichen
      4. Die einzelnen Produktionstage durch Trennlinien voneinander absetzen
    3. Beispiel Datumslisten: Farbige Kennzeichnungen und automatische Trennlinien
      1. Wochenenden farbig hinterlegen
      2. Fehlende Einträge optisch hervorheben
      3. Verschiedene Kalenderwochen voneinander absetzen
    4. Beispiel Bauplanung: Termin- und Kostenkontrolle auf einen Blick
      1. Differenzierte Darstellung der Leistungsarten mittels Schriftfarbe
      2. Den Grad der Fertigstellung mit Harvey Balls veranschaulichen
      3. Eine Kostenkontrolle mit Ampelsymbolen aufbauen
      4. Die aktuellen Aufgaben farblich kennzeichnen
  11. 9. Listen zusammenfassen: Von großen Tabellen zu informativen Übersichten
    1. Schneller Überblick statt langer Listen mit Teilergebnissen und Gliederungen
      1. Voraussetzungen für die Nutzung des Befehls »Teilergebnis«
      2. Informationen zusammenfassen und gliedern mit der Funktion »Teilergebnis«
        1. Eine Liste nach Ländern gruppieren und Mittelwerte zeigen
        2. Kleine Nachbesserungen an der Teilergebnisanzeige
        3. Mehr Details anzeigen mit einer Gruppierung nach Jahren
    2. Teilergebnisse informativer machen mit automatischen Hervorhebungen
      1. Vom Durchschnitt abweichende Werte für Länder und Monate kennzeichnen
      2. Die Niederschlagsverteilung übers Jahr auf einen Blick erfassen mithilfe von Datenbalken
    3. Flexiblere Übersicht mit Zusatzspalte und horizontaler Gruppierung
      1. Monatliche Auswertung pro Standort anzeigen
      2. Auch das geht: Eine horizontale Gruppierung anbringen
      3. Übersichten mit Teilergebnissen wieder in normale Datenlisten zurückverwandeln
    4. Mit Pivot-Tabelle und Datenschnitt ein kompaktes Info-Cockpit aufbauen
      1. Die Pivot-Tabelle aufbauen
      2. Die Pivot-Tabelle anpassen
      3. Pivot-Tabellen ganz einfach filtern per Datenschnitt
        1. Zwei Datenschnitte zur Pivot-Tabelle hinzufügen
        2. Die Datenschnitte flexibel einsetzen oder wieder löschen
  12. 10. Daten clever aufbereiten mit Textfunktionen
    1. Für Auswertungen zusammengesetzte Daten auf mehrere Zellen aufteilen
      1. Texte mit bekannten Längen auf mehrere Zellen verteilen
        1. Postleitzahl und Ort trennen mit LINKS, RECHTS, LÄNGE
        2. Mit GLÄTTEN überflüssige Leerzeichen entfernen
      2. Ist ein Assistent die Alternative zu den Formeln?
        1. Wenn der Ortsname aus mehr als einem Wort besteht
      3. Texte unbekannter Länge, aber mit festen Trennzeichen auf mehrere Zellen verteilen
      4. Unerlässlich für Serienbriefe: Nachname und Vorname in separaten Spalten
        1. Den Vornamen ermitteln mit LINKS und SUCHEN
        2. Den Nachnamen finden mit RECHTS, LÄNGE und SUCHEN
        3. Störende Leerzeichen bei der Ausrichtung von Texten entfernen
    2. Adresslisten bereinigen: Namen, Orte und Telefonnummern in Form bringen
      1. Perfekter Seriendruck durch klare Trennung von Vor- und Nachnamen
      2. Erleichtertes Filtern nach PLZ oder Ort durch Aufsplitten zusammengesetzter Ortsangaben
      3. Automatische Telefonnummernwahl durch Eliminieren von störenden Trennzeichen
    3. Importierte Geburtsdaten in ein lesbares Datumsformat bringen
    4. Vorhandene Daten zu informativen Aussagen zusammenfassen
      1. Texte und Zahlen zu informativen Aussagen zusammenstellen
      2. Aus Datumsangaben und Rechnungsbeträgen Zahlungsanweisungen generieren
  13. Stichwortverzeichnis
  14. Impressum