book

Microsoft Excel Hacks - Formeln und Funktionen effektiv einsetzen. 250 praktische Lösungen für konkrete Aufgabenstellungen

Name: Microsoft Excel Hacks - Formeln und Funktionen effektiv einsetzen. 250 praktische Lösungen für konkrete Aufgabenstellungen
Author: Franz Boehm
ISBN: 9783747506127

by Franz Boehm

February 2023

Intermediate to advanced

352 pages

7h 44m

German

mitp Verlag

Read now

Unlock full access

Excel Hacks
Impressum
Einleitung
Kapitel 1: Datum und Uhrzeit
1.1 Zeitberechnungen1.1.1 Eine aktualisierbare Uhrzeit‌ einfügen1.1.2 Zahlen in Minuten umwandeln1.1.3 Wie errechnen Sie aus Minuten die Stunden und Minuten als Dezimalzahl?1.1.4 Uhrzeiten runden1.1.5 Mit Uhrzeiten rechnen (Stundenlohn – Tageslohn‌ – Durchschnittsgeschwindigkeit)1.1.6 Positive und negative Zeitdifferenzen darstellen1.1.7 Berechnung der Arbeitszeit mit Pausenzeit nach sechs Stunden1.1.8 In Industrieeinheiten (60 min = 100 Einheiten) erfasste Arbeitszeit in Stunden und Minuten umwandeln1.2 Tagesberechnungen1.2.1 Gestern – Heute – Morgen1.2.2 Welches Datum hat der nächste Freitag?1.2.3 Welches Datum‌ hat der nächste »Freitag, der 13.«?1.2.4 Der wievielte Freitag im Monat ist der 12.08.2022?1.2.5 Den wievielten Tag des Jahres haben wir heute?1.2.6 Wie viele Tage sind es bis zum Jahresende?1.2.7 Individuelle Netto-Arbeitstage1.2.8 Welches Datum hat man nach 10 Arbeitstagen?1.3 Wochen- und Monatsberechnungen1.3.1 Das Datum in einer Woche oder in einem Monat1.3.2 Mit welchem Datum‌ beginnt eine vorgegebene Kalenderwoche‌?1.3.3 Wochenbeginn und Wochenende zu einem bestimmten Datum1.3.4 Zu welcher Kalenderwoche gehört das Datum?1.3.5 Der Wochentag ohne Zahlenformat1.3.6 Der erste Tag eines Monats als kopierbare Spaltenüberschrift oder kopierbare Zeilenbeschriftung1.3.7 Der letzte Tag eines Monats1.3.8 Der erste oder letzte Arbeitstag eines Monats1.3.9 Die Anzahl der Monate berechnen1.3.10 Monate in Jahre und Monate aufteilen1.3.11 Monate mit weniger als fünf Buchstaben werden ausgeschrieben, die restlichen abgekürzt1.4 Quartals-, Halbjahres- und Jahresberechnungen1.4.1 In welches Quartal fällt das Datum?1.4.2 Quartalsanfang – Quartalsende, Grundlage zur Berechnung des letzten Kündigungstermins‌1.4.3 Halbjahresanfang – Halbjahresende1.4.4 Beginn von Sommer- oder Winterzeit berechnen1.4.5 Wann haben wir ein Schaltjahr?1.4.6 Ein immerwährender Jahreskalender1.5 Geburtstage und Feiertage1.5.1 Wie alt ist Tante Anna?1.5.2 Wie viele Tage sind es bis zum nächsten Geburtstag?1.5.3 Eine Geburtstagsliste jahresunabhängig sortieren1.5.4 Eine Geburtstagsliste so sortieren, dass immer der nächste Geburtstag angezeigt wird1.5.5 Bevorstehende Geburtstage der nächsten 7 Tage hervorheben1.5.6 Geburtstage des aktuellen Monats farbig hervorheben1.5.7 Runde Geburtstage anzeigen1.5.8 An welchem Tag ist Muttertag?1.5.9 Auf welches Datum fällt der Ostersonntag und welche Feiertage hängen von Ostern ab?1.5.10 Welche Feiertage hängen vom ersten Weihnachtsfeiertag ab und wie werden sie errechnet?1.6 Zeitspannen berechnen1.6.1 Anzahl der Tage eines Zeitraums‌ mit SUMMENPRODUKT()1.6.2 Anzahl der Tage‌ mit SUMME() und WENN()1.6.3 Zeitraum aus einem Datumsbereich ermitteln und anzeigen1.6.4 Datumsdifferenz‌ in Jahren, Monaten und Tagen1.6.5 Jahresdifferenz‌ mit Nachkommastellen1.6.6 Mindesthaltbarkeitsdatum durch Datumsvergleich‌ überprüfen und anzeigen1.6.7 Wann läuft der Garantiezeitraum ab?1.6.8 Wann läuft die Kündigungsfrist‌ ab, wann müssen Sie kündigen?1.7 Datumsteile und Darstellung1.7.1 Ein Datum in Einzelteile zerlegen‌1.7.2 Datum und Text kombinieren‌
Kapitel 2: Finanzfunktionen
2.1 Kapital und Anlagen2.1.1 Wie lange müssen Sie sparen, um sich ein Auto für 40.000 € kaufen zu können?2.1.2 Wie viele Jahre müssen Sie Ihr Geld anlegen, um ein bestimmtes Kapital‌ zu erreichen?2.1.3 Wie viele Zinsen erhalten Sie für Ihr angelegtes Kapital?2.1.4 Wie hoch ist der Auszahlungsbetrag eines Festgeldes?2.1.5 Wie hoch ist der Auszahlungsbetrag eines Wertpapiers mit festem Zinssatz‌ zu einem gegebenen Zeitpunkt?2.1.6 Ein Zinsangebot mit Ablaufdatum2.2 Kredite und Zinsen2.2.1 Wie hoch ist die monatliche Tilgungsrate‌ eines Bankkredits?2.2.2 Welchen Kredit können Sie sich leisten?2.2.3 Wie hoch ist der Zinssatz?2.2.4 Wie viele Zinstage‌ sind es bis zum Jahresende?2.2.5 Wie hoch ist die Zinsbelastung‌ eines Darlehens im dritten Kalenderjahr?2.2.6 Wie hoch ist der Tilgungsbetrag‌ des Darlehens im dritten Kalenderjahr?2.2.7 Wann haben Sie Ihr Bausparkassendarlehen getilgt, wie hoch ist der aktuelle Schuldsaldo‌?2.3 Abschreibung2.3.1 Wie berechnen Sie die lineare Abschreibung‌ für Ihr Auto?2.3.2 Wie berechnen Sie die arithmetisch-degressive Abschreibung‌ für eine Maschine?2.3.3 Wechsel von der degressiven zur linearen Abschreibung
Kapitel 3: Informationsfunktionen
3.1 Zellinhalte prüfen3.1.1 Ist eine Zahl eingetragen oder nicht?3.1.2 Enthält die Zelle einen Textwert?3.1.3 Enthält die Zelle ein Datum?3.1.4 Ist die Zelle leer?3.1.5 Ist die Spalte richtig sortiert?3.1.6 Fehlerwerte aufspüren und Fehlermeldungen vermeiden3.2 Zellposition und Name3.2.1 In welcher Zeile oder in welcher Spalte befindet sich die Zelle?3.2.2 Dateinamen ermitteln und in eine Zelle einfügen
Kapitel 4: Mathematik – Statistik
4.1 Summieren4.1.1 Summe der sieben größten / kleinsten Zahlen eines Bereichs4.1.2 Summe eines Bereichs mit einer Bedingung4.1.3 Werte addieren, die bestimmte Kriterien erfüllen4.1.4 Summe jeder x-ten Zeile oder Spalte4.1.5 Summe der letzten Werte verschieden langer Spalten4.1.6 Zeilensumme‌ bis zu einer bestimmten Spalte4.1.7 Die Werte einer bestimmten Kalenderwoche addieren‌4.1.8 Laufende Summen berechnen4.1.9 Summieren‌ von gerundeten Werten‌4.1.10 Den Bezug der Summen-Formel fixieren‌4.1.11 Summe in Abhängigkeit vom Anfangsbuchstaben4.1.12 Summierung‌ eines Bereichs mit Fehlerwerten4.1.13 Summieren‌ mit der Funktion AGGREGAT() – Fehlerwerte ignorieren‌4.2 Umsatz- und Gewinnermittlung4.2.1 Den Tagesumsatz‌ errechnen4.2.2 Die Summe der Umsätze‌ von jedem Montag eines Zeitraums4.2.3 Den Monatsumsatz‌ berechnen4.2.4 Der Umsatz‌ eines bestimmten Quartals‌4.2.5 Der Umsatz vom 1. Januar bis zu einem bestimmten Tag4.2.6 Wie errechnen Sie den Umsatz eines bestimmten Zeitraums in einer chronologisch unsortierten Liste?4.2.7 Einnahmen und Ausgaben getrennt aufsummieren4.2.8 Alle Zahlungseingänge eines bestimmten Kunden finden4.2.9 Umsatz eines Mitarbeiters aus einer Jahrestabelle filtern4.2.10 Der Tag mit dem höchsten Umsatz!4.2.11 Wie entwickelt sich der Umsatz im nächsten Halbjahr?4.2.12 Die Gewinnentwicklung‌ zweier Jahre vergleichen4.2.13 Die Umsatzprognose laufend‌ aktualisieren4.3 Zellen und Werte zählen4.3.1 Alle Zellen zählen‌, die eine bestimmte Bedingung erfüllen4.3.2 Anzahl bestimmter Werte‌ auf Grundlage mehrerer Kriterien4.3.3 Wie viele Zellen enthalten Werte‌, wie viele sind leer‌ – wie viele Athleten erreichten das Ziel?4.3.4 Leere Zellen in Abhängigkeit anderer Zellen farbig anzeigen und zählen4.3.5 Wie viele Werte‌ zwischen 25 und 30 Grad Celsius enthält die Spalte?4.3.6 Anzahl unterschiedlicher Einträge in einem Tabellenbereich‌4.3.7 Anzahl unterschiedlicher Einträge in einer gefilterten Liste4.3.8 Duplikate‌ finden4.4 Rangfolgen ermitteln4.4.1 Zelle mit der kleinsten bzw. größten Zahl ermitteln – »relative« und »absolute« Zelladressierung4.4.2 Die Rangfolge in der Reihe ermitteln4.4.3 Rangfolge‌ in einem nicht zusammenhängenden Bereich4.4.4 Eine Siegerliste mit SVERWEIS() und WENNFEHLER() – Zelleninhalte in unterschiedlichen Bereichen suchen4.4.5 Fortlaufende Rangfolge auch bei identischen Ergebnissen4.4.6 Eine eindeutige Reihenfolge trotz gleichem Rang4.4.7 Eine dynamische »TOP 10«-Liste4.4.8 Siegerliste mit Rang‌ und Namen durch die Funktionen INDEX() und VERGLEICH()4.5 Runden4.5.1 Zahlen AUFRUNDEN() oder ABRUNDEN()4.5.2 Das Ergebnis auf eine bestimmte Dezimalzahl runden4.5.3 Alle Beträge mit VRUNDEN() auf ein Vielfaches runden‌4.5.4 Rechnungsbeträge mit OBERGRENZE() auf 5 Cent auf- oder abrunden4.5.5 Zahlen auf glatte 100 oder 1000 Einheiten runden4.5.6 Auf GERADE() bzw. UNGERADE() Zahlen runden‌4.5.7 Als Ergebnis einer Berechnung nur die Zahl links vom Komma anzeigen4.6 Mittelwertberechnung4.6.1 Welcher Zahlenwert liegt in der Mitte der Zahlenreihe?4.6.2 Wie erhalten Sie den Mittelwert einer Datenreihe ohne »Ausreißerwerte‌«?4.6.3 Das gewogene Mittel berechnen4.6.4 Einen Mittelwert ohne Einbeziehung von »0«-Werten bilden4.7 Weitere Rechenfunktionen4.7.1 Maßeinheiten UMWANDELN()4.7.2 Währungen umrechnen und darstellen4.7.3 Das erste und das letzte Auftreten einer Zahl erfassen4.7.4 Ermittlung des x-größten Werts4.7.5 Differenz‌ zwischen der aktuellen Zeile und der Zeile mit dem letzten Eintrag ermitteln4.7.6 Wie errechnen Sie den Sinus eines bestimmten Winkels?4.7.7 Potenzieren‌ und Wurzel ziehen‌4.8 Rechenfunktionen in der Praxis4.8.1 Ein Haushaltsbuch‌ mit der Funktion‌ SUMMENPRODUKT()4.8.2 Kfz-Kosten-Verwaltung‌ mit den Funktionen SUMME() und WENN()4.8.3 Kilometergeld nach Entfernungspauschalen‌ berechnen4.8.4 Die Altersgruppen‌ eines Vereins ermitteln4.8.5 Das häufigste Ergebnis einer Versuchsreihe4.8.6 Jahresdurchschnittsnote‌ berechnen4.8.7 Wie hoch sind die Gewinnchancen, wie viele mögliche KOMBINATIONEN() gibt es?
Kapitel 5: Tabelle und Datenbank
5.1 Zeilen und Spalten5.1.1 x-letzter Eintrag‌ einer Zeile und dessen Zellbezug5.1.2 x-letzter Spaltenwert‌ bei lückenhaften Einträgen‌5.1.3 Summe‌ der Zahlen einer bestimmten Zeile‌5.1.4 Wert jeder x-ten Zelle‌ einer Spalte ausgeben5.1.5 Eine Zeilennummerierung erstellen5.1.6 Eine laufende Nummerierung mit ANZAHL2()5.1.7 Eine laufende Nummerierung mit KGRÖSSTE()5.1.8 Jede 5. Zeile nummerieren5.1.9 ZEILEN() und SPALTEN() zählen5.2 Zellinhalte suchen und anzeigen5.2.1 Steht ein bestimmter Wert im Zellbereich?5.2.2 Texte‌ mit SVERWEIS() suchen und zusammenführen5.2.3 SVERWEIS() nach links5.2.4 Mit INDEX() den Inhalt bestimmter Zellen‌ anzeigen5.2.5 Den aktuellen Saldo in der ersten Zeile anzeigen5.2.6 Wert am Schnittpunkt‌ von Zeile und Spalte einer Matrix5.2.7 Wertesuche‌ mit Rückgabe von Zeilen und Spaltenreferenz5.2.8 Eine Tabelle drehen5.3 Zellbereiche auswerten5.3.1 Den günstigsten Preis‌ eines Artikels finden5.3.2 Summierung‌ des Datenbankbereichs mit mehreren Suchkriterien5.3.3 Die Summe‌ eines bestimmten Zeitraums‌ berechnen5.3.4 Summe‌ eines beliebigen Zeitraums‌ mit SUMMEWENNS() berechnen5.3.5 Summe‌ einer gefilterten Liste‌ mit TEILERGEBNIS()5.3.6 Summierung bis zur nächsten Leerzelle5.3.7 Datenauswertung‌ mit benannten Formeln und der Funktion BEREICH.VERSCHIEBEN()5.3.8 Kfz-Kosten-Berechnung‌ in einem »dynamischen Bereich‌«5.3.9 Anzahl der Besucher an einem bestimmten Tag – Datenauswertung mit der Funktion ERSTERWERT() oder WAHL()5.3.10 Noten‌ anhand einer Punktetabelle‌ vergeben5.4 Zufallszahlen5.4.1 Zufallszahlen erzeugen‌5.4.2 Zufallszahlen‌ ohne Duplikate erstellen5.4.3 Zufälligen Eintrag‌ aus einer Liste auswählen5.4.4 Zelleninhalt‌ aus einem Tabellenbereich‌ zufällig auswählen5.4.5 Listeneinträge mischen
Kapitel 6: Textfunktionen
6.1 Texte verbinden oder trennen6.1.1 Inhalt mehrerer Zellen in einer anderen Zelle anzeigen6.1.2 Textbereiche verbinden‌6.1.3 Den Familiennamen‌ mit einer förmlichen Anrede verbinden6.1.4 Ein Zeilenumbruch‌ mit der Funktion ZEICHEN()6.1.5 Straßennamen und Hausnummer trennen6.1.6 Bankleitzahl aus einer IBAN‌ extrahieren6.2 Texte und Zeichenfolgen suchen6.2.1 Texte SUCHEN() und FINDEN()6.2.2 Die ersten fünf Zeichen von LINKS() oder RECHTS()6.2.3 Die Länge einer Zeichenkette ermitteln6.2.4 Position der ersten Ziffer‌ in einer Zeichenfolge finden6.2.5 Das erste Wort einer Zeichenkette‌6.2.6 Das letzte Wort einer Zeichenkette‌6.2.7 Das zweite Wort einer Zeichenkette‌6.2.8 Ist der Name in der Liste?6.2.9 Texte und Zeichenfolgen suchen und vergleichen6.2.10 Die Häufigkeit eines Zeichens‌ ermitteln6.2.11 Anzahl der Wörter‌ in einer Tabellenzelle ermitteln6.3 Zeichenketten bearbeiten6.3.1 Text in Großbuchstaben oder Kleinbuchstaben umwandeln6.3.2 Zeichenfolgen wiederholen‌6.3.3 Leerzeichen zwischen Text und Zahl einfügen6.3.4 Wörter umdrehen und Namen und Vornamen trennen6.3.5 Das Minuszeichen von rechts nach links setzen6.3.6 Zeichenfolgen‌ ERSETZEN()6.3.7 Nummerierungen sortieren‌6.3.8 Nicht druckbare Zeichen‌ und überflüssige Leerzeichen mit SÄUBERN() und GLÄTTEN() entfernen6.3.9 Aus einem Text alle Leerzeichen entfernen‌, Zeichenfolgen ersetzen‌ oder auswechseln6.3.10 Einen leeren Zellbereich‌ mit dem Währungsstrich — durchstreichen (entwerten)6.3.11 Alle Umlaute entfernen oder Umlaute einfügen6.3.12 Ein Häkchen‌‌‌ in einer Zelle erscheinen lassen, um etwas als »erledigt« zu markieren6.3.13 Versteckte Kommentare‌6.4 Textwerte und Zeichencodes6.4.1 Zahlen mit einer festen Nachkommazahl‌‌ als Textwert ausgeben6.4.2 Mit Textwerten rechnen6.4.3 Zeichencodes ermitteln‌6.4.4 RÖMISCH() – ARABISCH()
Kapitel 7: Formatvorlagen und weitere nützliche Funktionen
7.1 Bedingte Formatierungen7.1.1 Alle Werte über 5.000 € hervorheben‌7.1.2 Werte über 5.000 € gelb und Werte über 7.000 € grün hervorheben‌7.1.3 Den niedrigsten und den höchsten Wert‌ einer Reihe markieren7.1.4 Alle Zellen mit dem Wert 0‌ grün einfärben7.1.5 Leere Zellen hervorheben‌7.1.6 Textzellen aufspüren‌7.1.7 Zellen mit Formeln suchen‌7.1.8 Ungeschützte Formelzellen‌ einfärben7.1.9 Leerzeichen suchen‌7.1.10 Wochenenden farbig einfärben7.1.11 Drei Tage vor und nach dem aktuellen Datum markieren7.1.12 Bedingte Formatierung‌ als Suchfunktion7.1.13 Nicht identische Zellen‌ mithilfe einer bedingten Formatierung markieren7.1.14 Text als Suchbedingung‌‌7.1.15 Duplikate‌ mit einer bedingten Formatierung‌‌ aufspüren7.1.16 Suchen mit zwei Bedingungen‌ (Vor- und Nachname müssen stimmen)7.1.17 Zwischensummen farbig hervorheben‌7.1.18 Ampelfunktion‌ für optische Wertanzeige‌7.2 Zeilen und Spalten einfärben7.2.1 Jede zweite Zeile einfärben‌7.2.2 Jede zweite Spalte einfärben‌7.2.3 Jede zweite Zeile erst dann einfärben, wenn sie ausgefüllt wird7.2.4 Jede zweite Zeile‌ auch beim Filtern‌ einfärben7.2.5 Mehrere zusammenhängende Zellen‌ einfärben, wenn in einer der Zellen ein Eintrag erfolgt7.2.6 Drei unterschiedliche Zeilenfarben‌7.2.7 Zeilen‌- und Spaltenfarbe‌ als Lesehilfe‌7.2.8 Fehlerhafte Einträge‌‌ farbig kennzeichnen7.3 Zahlenformate7.3.1 Zahlen im Tausend-Euro-Format oder in Millionen darstellen7.3.2 Nur bestimmte Zahlen anzeigen7.3.3 Ein bedingtes Zahlenformat‌ mit Text und Farbe7.3.4 Ein Zahlenformat‌ zur Entwertung des Leerraums‌ einer Betragszelle mit einem beliebigen Zeichen7.4 Eigene Konstanten, Funktionen und Datenprüfung7.4.1 Eigene Konstanten erstellen‌ – benannte Formeln7.4.2 Eigene Funktionen erstellen‌7.4.3 Doppelte Einträge‌ in einer Spalte oder in einer Zeile verhindern7.4.4 Dynamische Dropdownlisten‌ in Tabellen

Kapitel 8: Beispielanwendungen zum Download
8.1 Haushaltsbuch‌8.2 Quittungsformular‌8.3 Urlaubskalender‌8.4 Jahreskalender‌8.5 Sternzeichenberechnung‌

Content preview from Microsoft Excel Hacks - Formeln und Funktionen effektiv einsetzen. 250 praktische Lösungen für konkrete Aufgabenstellungen

Kapitel 1: Datum und Uhrzeit

1.1 Zeitberechnungen

1.1.1 Eine aktualisierbare Uhrzeit‌ einfügen

Problem

In einem Formular soll die aktuelle Zeit angezeigt werden. Die Zeit soll sich beim Öffnen der Datei oder beim Drücken der Taste F9 automatisch aktualisieren.

[Bild]

Lösung

Schreiben Sie die Funktion = JETZT() in die Zelle, in der die aktuelle Uhrzeit angezeigt werden soll.

Hintergrund

Die Formel wird bei jeder Neuberechnung der Arbeitsmappe aktualisiert.

Wählen Sie zusätzlich Start|Format|Zellen, Registerkarte Zahlen, Kategorie Uhrzeit und dann das gewünschte Zeitformat.

Diese Funktion kann auch in Text eingebettet werden, wie die Formel in folgendem ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft Excel: Formeln & Funktionen - Das Maxibuch, 3., aktualisierte und erweiterte Auflage

Egbert Jeschke, Eckehard Pfeifer, Helmut Reinke, Sara Unverhau, Bodo Fienitz

Effektives Arbeiten mit MS Teams, OneNote, Outlook & Co. -- Zusammenarbeit und Selbstorganisation mit Microsoft 365/Office 365

Gräfen, Helmut

LibreOffice 24 - Praxiswissen für Ein- und Umsteiger

Wilfried Seimert

Microsoft Office 2021 – Das Handbuch

Rainer G. Haselier, Klaus Fahnenstich

Publisher Resources

ISBN: 9783747506127

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft Excel Hacks - Formeln und Funktionen effektiv einsetzen. 250 praktische Lösungen für konkrete Aufgabenstellungen

by Franz Boehm

Kapitel 1: Datum und Uhrzeit

1.1 Zeitberechnungen