Excel Freitag – Excel-Tipps bei Hanser

Jeden Freitag schicken wir Ihnen per WhatsApp einen Excel-Tipp auf Ihr Smartphone.
Natürlich völlig kostenlos für Sie.

Zur Anmeldung

Alle Tipps können Sie anschließend hier nachlesen.
Sie wünschen sich Tipps zu einem bestimmten Excel-Thema?
Schreiben Sie uns an
(Ihre E-Mail-Adresse wird nicht von uns gespeichert und nicht zu Werbezwecken verwendet)


Diese Excel-Tricks finden Sie hier:

Excel Freitag #19: Da soll keiner ran - Excel-Zellen schützen lernen

Excel Freitag #18: Doppelte Werte

Excel Freitag #17: Tastenkombinationen für Excel

Excel Freitag #16: 5 häufige Excel-Fehler und wie sie vermieden werden

Excel Freitag #15: Fröhliche Weihnachten

Excel Freitag #14: Subtrahieren mit Excel

Excel Freitag #13: Druckbereich festlegen

Excel Freitag #12: Multiplizieren mit Excel

Excel Freitag #11: Endlich doppelte Daten aufspüren und entfernen

Excel Freitag #10: Urlaubsplanung mit Excel

Excel Freitag #9: Summe berechnen mit SUMMEWENN

Excel Freitag #8: Durchschnitt

Excel Freitag #7: Alltagstipps

Excel Freitag #6: Tipps zur AutoSumme

Excel Freitag #5: WENN() verstehen

Excel Freitag #4: SVERWEIS() ganz einfach

Excel Freitag #3: So berechnen Sie Spritkosten

Excel Freitag #2: So rechnen Sie richtig mit Prozenten

Excel Freitag #1: So schnell können Sie Spalten einblenden

Excel Freitag Linie

Excel Freitag #19: Da soll keiner ran - Excel-Zellen schützen lernen

Hier lernen Sie, Zellen in Excel zu schützen.

Alle Zellen im Tabellenblatt sind standardmäßig geschützt. Erst wenn der Blattschutz aktiviert wird, greift der Schutz.
Markieren Sie einen Bereich, drücken Sie [Strg] + [1]. Im Register Schutz
sehen Sie den Status (Gesperrt).
Entfernen Sie den Schutz und sperren Sie mit Überprüfen/Blatt schützen
das ganze Blatt.
Zum Aufheben einfach wieder auf „Blattschutz aufheben“ klicken.

Excel Freitag Linie

Excel Freitag #18: Doppelte Werte

Sie können Duplikate markieren oder auch entfernen.

Hier der Freitagstipp „Doppelte Werte“:

So finden Sie doppelte Werte in Spalten:
Für die Einträge in Spalte A schreiben Sie in Zelle B1 diese Formel:
=ZÄHLENWENN($A:$A;A1)
Kopieren Sie die Formel per Doppelklick auf das Füllkästchen nach unten. Aktivieren Sie den Filter und filtern Sie mit dem Zahlenfilter alles, was größer als 1 ist.
Um doppelte Werte zu entfernen, markieren Sie die Spalte(n), wählen Sie Daten/Datentools/Duplikate entfernen. Geben Sie die Spalte mit den doppelten Werten an und löschen Sie diese mit OK.

Excel Freitag Linie

Excel Freitag #17: Tastenkombinationen für Excel

… gibt es viele, mehrere Hundert. Sie müssen nicht alle kennen, sondern nur die wichtigsten 10. Aber die müssen Sie kennen, dann bekommen Sie den richtigen Schwung in die Kalkulation!

1. Spalte mit dem Zellzeiger markieren
[Strg] + [Leertaste]
Zeile mit dem Zellzeiger markieren
[Umschalt] + [Leertaste]
Alle Zellen markieren
[Strg] + [Umschalt] + [Leertaste]

2. Ab dem Zellzeiger bis zum Ende einer Liste, bis zur letzten beschrifteten Zelle oder alle leeren Zellen nach unten markieren. Das gilt für in alle Richtungen.
[Strg] + [Pfeiltaste]

3. Die Liste bis zur ersten Leerzeile und Leerspalte markieren
[Strg] + [*]
Dabei den Zellzeiger stehen lassen
[Strg] + [a]

4. Die letzte benutzte Zelle ansteuern
[Strg] + [Ende]
Markierung ab dem Zellzeiger
[Strg] + [Ende] + [Umschalt]

5. Kopieren
[Strg] + [C]
Ausschneiden
[Strg] + [x]
Einfügen
[Strg] + [v]

6. Die letzte Aktion zurücknehmen
[Strg] + [z] oder [Alt] + [Backspace]
Die letzte Aktion wiederholen
[Strg] + [y]

7. Einen Zeilenumbruch an der Cursorposition einfügen
[Alt] + [Eingabe]

8. Zum nächsten Blatt umschalten
[Strg] + [Bild ab]
Zum vorherigen Blatt umschalten
[Strg] + [Bild auf]

9. Zur nächsten offenen Mappe umschalten
[Strg] + [F6]
Zur vorherigen Mappe umschalten
[Strg] + [Umschalt] + [F6]

10. Eine neue Mappe anlegen
[Strg] + [n]
Eine Mappe speichern
[F12]
Eine Mappe schließen
[Strg] + [w] oder [Alt] + [F4]

Zum Schluss noch eine Windows-Tastenkombination, die auch für Excel-User wichtig ist:
Mit [Alt] + [Umschalt] schalten Sie (meist versehentlich) auf die englische Tastaturbelegung um. Mit der gleichen Kombination geht´s wieder zurück.

Excel Freitag Linie

Excel Freitag #16: 5 häufige Excel-Fehler und wie sie vermieden werden

1. Relative und absolute Bezüge

Falsche Bezüge sind die häufigste Fehlerquelle in der Kalkulation. Bezüge sind entweder relativ oder absolut, und welche Form gebraucht wird, entscheidet die Aufgabe. Hier ein Beispiel:


Rechnung mit Artikel, Menge und Verkaufspreis

Die Rechnung enthält neben dem Artikel die Menge und den Verkaufspreis. In der Spalte Betrag wird das Produkt aus Menge und Preis berechnen.


Relative Bezüge benutzen

Dazu verwendet die Formel zwei relative Bezüge (relativ, weil der Bezug auf die Zellen in der gleichen Zeile verweisen):
=B2*C2


Die Formel auf die anderen Rechnungsbeträge kopieren

Kopieren Sie die Formel mit dem Füllkästchen nach unten auf die übrigen Rechnungsbeträge, …


Die relativen Bezüge passen sich an

… passen sich die beiden relativen Bezüge an. Aus B2 wird B3 und B4, aus C2 wird C3 und C4.


Rabattabzug in einer weiteren Spalte berechnen

Berechnen Sie in einer weiteren Spalte einen Rabattabzug, schreiben Sie den Rabatt dazu in die Zelle H2.


Formel mit relativen Bezügen schreiben

Schreiben Sie die Formel (Betrag x Rabatt) mit relativen Bezügen, rechnet sie genau einmal richtig.


Beim kopieren passen sich alle relativen Bezüge an, was nicht immer richtig ist

Kopieren Sie die Formel nach unten, passen sich alle relativen Bezüge an, was bei D2 zwar richtig ist aber nicht bei H2.


Den Bezug der sich nicht ändern darf absolut setzen.

Abhilfe schaffen Sie, indem Sie den Bezug, der sich nicht ändern darf, absolut setzen. Schreiben Sie ein $-Zeichen vor die Zeilen- und die Spaltennummer:
= D 2 * $ H $ 2


Der absolute Bezug ändert sich beim Kopieren nicht.

Jetzt können Sie die Formel nach unten kopieren, und die Rechnung stimmt. Der absolute Bezug wird sich beim Kopieren nicht verändern.


Die Formel für die endgültige Rechnungssumme bekommt zwei relative Bezüge.

Die Formel für die endgültige Rechnungssumme (Betrag minus Rabattabzug) bekommt natürlich wieder zwei relative Bezüge, so kann sie auf alle Positionen kopiert werden:
=D2-E2


2. Zirkelbezüge

Warnt Sie Excel beim Abschluss einer Formel vor einem Zirkelbezug, haben Sie wahrscheinlich in einem Teil der Formel einen Bezug auf die eigene Zelle verwendet, also die Zelle, in der die Formel steht.


Schreiben Sie eine Summenformel

Schreiben Sie beispielsweise eine Summenformel, in der die Zelle mit der Formel in den Summenbereich einbezogen wird:


Sie erhalten eine Fehlermeldung

Jetzt erhalten Sie eine Fehlermeldung:


Sie erhalten einen Warnhinweis mit der fehlerhaften Adresse

In der Statusleiste sehen Sie einen Warnhinweis und die Adresse der fehlerhaften Zelle mit dem Zirkelbezug:


Die Formel reparieren und der Warnhinweis verschwindet.

Reparieren Sie die Formel, dann verschwindet die Zirkelbezugswarnung aus der Statuszeile und die Summe ist korrekt:


3. Die Rautenkette

Lästig, aber einfach zu vermeiden ist die Rautenkette. Sie taucht auf, wenn eine Zahl oder ein Datum nicht mehr in die Spalte passt:


Das Datum wird mit einem Zahlenformat formatiert.

Das Datum in der Zelle A1 wird mit einem Zahlenformat formatiert, das den Wochentag anzeigt und den Monat ausschreibt.


Die Rautenkette erscheint, weil die Spalte zu klein ist.

Damit wird die Spalte aber zu klein für die Anzeige, die Rautenkette erscheint.


Vergrößern Sie die Spalte, bis das Datum hineinpasst.

Abhilfe schaffen Sie, indem Sie die Spalte so weit vergrößern, bis das Datum hineinpasst. Klicken Sie dazu einfach doppelt auf die rechte Spaltenlinie im Spaltenkopf (optimale Spaltenbreite).


Zellen markieren und Verbinden und Zentrieren anwenden

Markieren Sie die Zelle und die angrenzende Zelle rechts und verwenden Sie Verbinden und Zentrieren unter Start/Ausrichtung.Markieren Sie die Zelle und die angrenzende Zelle rechts und verwenden Sie Verbinden und Zentrieren unter Start/Ausrichtung.


Sie können die Spalte wieder verkleinern und das Datum wird über beide Zellen geschrieben.

Jetzt können Sie die Spalte wieder kleiner machen, das Datum wird über beide Zellen weggeschrieben.


4. Diagramme passen sich nicht dynamisch an

Diagramme verwenden absolute Bezüge, und die passen sich leider nicht an, wenn der Liste neue Zahlen angefügt werden. Abhilfe schaffen Sie, indem Sie die Liste einfach zur Tabelle erklären.


Eine Liste mit Regionen und Umsätzen

Hier eine Liste mit Regionen in der ersten Spalte und Umsätzen in Spalte B. Markieren Sie die Liste inklusive der Überschriften, …


Erstellen Sie über Einfügen/Diagramme ein 2D Säulendiagramm

… und erstellen Sie über Einfügen/Diagramme ein 2D-Säulendiagramm.


Fügen Sie eine Zeile an, ändert sich das Diagramm nicht.

Das Ergebnis ist zwar richtig, das Diagramm bildet alle Regionsumsätze als Balken ab. Fügen Sie aber eine weitere Zeile an, ändert sich das Diagramm nicht.


Grund ist, dass die Funktion Datenreihe absolute Bezüge benutzt

Grund dafür ist die Funktion DATENREIHE, die Sie angezeigt bekommen, wenn Sie auf die Balken klicken. Sie verwendet absolute Bezüge für die Rubrikenachse und die Größenachse.


Markieren Sie die Liste.

Markieren Sie die Liste, setzen Sie dazu einfach den Zellzeiger in eine beliebige Zelle (die Liste darf aber keine Leerzeilen enthalten).


Wählen Sie Einfügen - Tabelle - Tabelle und bestätigen Sie mit OK.

Wählen Sie Einfügen/Tabelle/Tabelle. Der Bereich wird vorgeschlagen, bestätigen Sie die Meldung mit OK.


Zeichnen Sie ein Diagramm mit Tabelle als Datenbasis.

Zeichnen Sie wieder ein Diagramm, dieses Mal aber mit der Tabelle als Datenbasis.


Eine neue Zeile in der Tabelle wird direkt in das Diagramm übernommen.

Fügen Sie eine weitere Zeile an die Tabelle an, wird diese automatisch als neuer Datenpunkt in das Diagramm übernommen.


5. Bezugsfehler vermeiden

Bezugsfehler sind Fehler, die durch Einfüge- oder Löschaktionen im Tabellenblatt entstehen. Im Unterschied zu Formelfehlern, die mit #NAME! oder #WERT! etc. angezeigt werden, lässt sich ein Bezugsfehler nicht durch Änderungen in Zellen rückgängig machen. Aber – es gibt Mittel und Wege, um diese Fehler zu vermeiden. Einen besonders schönen Trick sehen Sie hier:


Liste mit Startnummern und Namen.

Die Liste enthält Startnummern und Namen. Die Nummern werden in der Regel so erstellt: Sie schreiben die erste Nummer, …


Erstellen Sie die Startnummer mit einer Formel.

… und erstellen eine Formel, mit der die Startnummer um einen Zähler erweitert wird. Diese Formel kopieren Sie nach unten bis zum gewünschten Endwert.


Löschen Sie einen Eintrag.

Wenn Sie aber einen Eintrag löschen müssen, wie hier zum Beispiel die Zeile 4 mit der Startnummer 3, …


Für die übrigen Zellen wird ein Bezugsfehler angezeigt, da kein passender Zellbezug mehr vorhanden ist.

… erhalten Sie Bezugsfehler für die übrigen Zeilen, denn die Formel findet keinen passenden Zellbezug mehr, den sie hochzählen kann.


Den Startwert mit der Funktion Zeile berechnen.

Abhilfe schaffen Sie, indem Sie den Startwert einfach mit der Funktion ZEILE berechnen. Damit verwenden Sie die Zeilennummer des Tabellenblattes – ziehen Sie eine1 ab, um mit dem Startwert 1 zu beginnen.


Kopieren Sie die Formel nach unten.

Kopieren Sie die Formel nach unten, sie wird in jeder neuen Zeile eine fortlaufende Nummer berechnen.


Löschen Sie einen Eintrag aus der Nummernfolge

Löschen Sie aus dieser Nummernfolge einen Eintrag, …


Die Nummern werden automatisch nach oben geschoben und die Formel berechnet die nächste Zeilenzahl.

… werden die übrigen Nummern einfach nach oben geschoben, und die Formel berechnet einfach wieder die nächste Zeilenzahl bzw. die fortlaufende Startnummer.

Excel Freitag Linie

Excel Freitag #15: Fröhliche Weihnachten

Fröhliche Weihnachten

Fröhliche Weihnachten!
Was gibt es Schöneres zum Jahresende als einen Weihnachtsbaum mit vielen bunt blinkenden Kugeln und einem rotierenden Stern? Der Spaß ist garantiert und Sie haben sicher auch wieder etwas über Excel gelernt.


Hier markieren Sie alle Zellen

Markieren Sie im leeren Tabellenblatt alle Zellen, klicken Sie dazu links oben in das Feld, in dem sich Zeilennummern und Spaltenbuchstaben treffen.


Die Spaltenbreite auf 25 Pixel setzen

Die Spaltenbreite setzen Sie einheitlich auf 25 Pixel, ziehen Sie dazu die Spaltenzwischenlinie nach links.


Zeilenhöhe auf 40 Pixel setzen

Die Zeilenhöhe setzen Sie für alle Zeilen auf 40 Pixel, ziehen Sie dazu die untere Linie der Zeilennummer nach oben.


Einen Stamm zeichnen

Zeichnen Sie anschließend einen Weihnachtsbaum. Markieren Sie die ersten Zeilen für den Stamm, …


Zellen für den Baum markieren

… drücken Sie die Strg-Taste und markieren Sie weitere Zellen, …


Die restlichen Zellen des Baums markieren

… bis der komplette Bereich für den Baum markiert ist.


Dem Baum die grüne Farbe zuweisen

Jetzt können Sie ihm seine grüne Farbe zuweisen.


Baum in das Namensfeld der aktiven Zelladresse schreiben

Wenn Sie mit der aktiven Zellmarkierung das Wort „Baum“ in das Namensfeld schreiben, in dem die aktive Zelladresse angezeigt wird, finden Sie den Bereich später leichter wieder. Vergessen Sie nicht, mit der Eingabe-Taste abzuschließen.


Färben Sie den Stamm mit einer passenden Farbe ein

Der Baum braucht natürlich noch einen Stamm, markieren Sie dazu sechs Zellen und weisen Sie diesen eine passende Farbe zu.


Markieren Sie alle Blätter mit dem Namen aus dem Namensfeld

Markieren Sie dann wieder alle „Blätter“, holen Sie dazu einfach den Namen aus dem Namensfeld.


Schreiben Sie diese Formel: =ZUFALLSBEREICH(1;20)

Schreiben Sie diese Formel:
=ZUFALLSBEREICH(1;20)


Zum Abschluss die Tasten Strg und Eingabe drücken

Drücken Sie zum Abschluss die Tasten Strg und Eingabe. Damit wird die Formel auf alle markierten Zellen verteilt.


Eine Beleuchtung anlegen

Jetzt brauchen wir noch eine Beleuchtung. Dazu wählen Sie Start/Formatvorlagen/Bedingte Formatierung.


Den Eintrag Symbolsätze suchen und die erste Symbolreihe unter Formen markieren.

Suchen Sie den Eintrag Symbolsätze und markieren Sie die erste Symbolreihe unter Formen.


 Unter Start, Formatvorlagen und Bedingte Formatierung Regeln verwalten auswählen.

Wählen Sie gleich im Anschluss Start/Formatvorlagen/Bedingte Formatierung/Regeln verwalten.


Neue Bedingungsformatregel festlegen

Mit Regeln bearbeiten wird die neue Bedingungsformatregel bearbeitet.


Nur Symbol anzeigen ankreuzen

Kreuzen Sie die Option „Nur Symbol anzeigen“ an und schließen Sie Verwaltung wieder mit OK.


F9 drücken um die Lichterfarben zu variieren

Die Beleuchtung ist angebracht, drücken Sie mehrmals F9, um die Farben der Lichter zu variieren.


Unter Einfügen - Illustrationen einen Stern zeichnen

Zeichnen Sie unter Einfügen/Illustrationen noch einen Stern, …


Den Stern auf die Baumspitze setzen

… setzen Sie diesen auf die Baumspitze.


Dem Stern noch eine Farbe geben

Die passende Farbe finden Sie in den Designformatvorlagen für das gezeichnete Objekt.


Neuberechnung automatisieren

Für eine automatische Neuberechnung brauchen wir ein VBA-Makro. Zeichnen Sie dazu unter Entwicklertools/Formularelemente/Einfügen eine Schaltfläche.


Ziehen Sie ein Rechteck auf

Ziehen Sie mit gedrückter Maustaste ein Rechteck auf, …


Dem Makro einen Namen geben und anlegen

… geben Sie einen Namen für das neue Makro ein (hier: Start) und klicken Sie auf Neu, um das Makro anzulegen.


Die Makroanweisung schreiben

Schreiben Sie diese Makroanweisungen zwischen Sub Start() und End Sub:

LichtAn: Calculate Application.Wait Now + TimeValue("0:00:01") GoTo LichtAn


Beschriftung für die Schaltfläche erstellen

Schließen Sie den VBA-Editor wieder und tragen Sie eine Beschriftung für die Schaltfläche ein. Sollte sie nicht mehr aktiv sein, klicken Sie das Objekt mit der rechten Maustaste an.


Fröhliche Weihnachten

Ihr Weihnachtsbaum ist fertig, mit Klick auf die Schaltfläche blinkt er in vielen schönen Farben. Drücken Sie Strg + Pause, um das Makro zu beenden.


Durch eine Schleige im Makro den Stern rotieren lassen

Fügen Sie noch eine kleine Schleife in das Makro ein, dann rotiert auch der Stern auf der Baumspitze:

Sub Start()

Dim i As Integer

LichtAn:

For i = 1 To 180 Step 10

ActiveSheet.Shapes(1).ThreeD.IncrementRotationX i

Calculate

Application.Wait Now +
TimeValue(“0:00:01”)

Next i

GoTo LichtAn

End Sub

Excel Freitag Linie

Excel Freitag #14: Subtrahieren mit Excel

Mit Excel können Sie sich den Taschenrechner sparen

So subtrahieren Sie zwei Zahlenwerte:
A1: 200
A2: 120
A3: =A1-A2
Ergebnis: 80

Bei negativen Werten greift natürlich die arithmetische Regel (Minus und Minus = Plus):
A1: 200
A2: -120
A3: =A1-A2
Ergebnis: 320

Mehrere Zellen fassen wir am besten in einer Summe zusammen und ziehen diese dann von der Basiszahl ab:
A1: 200
B1: 20
B2: 30
B3: 50
A3: =A1-SUMME
Ergebnis: 100

Negative Werte werden in der Summe natürlich subtrahiert:
B2: -30
Ergebnis in A3: 160

Mit der Funktion ABS schalten Sie das Minuszeichen ab und verwenden nur den absoluten Wert:
=A1-(SUMME(ABS(B1:B3)))
Ergebnis in A3: 100

Excel Freitag Linie

Excel Freitag #13: Schluss mit der Papierverschwendung - Druckbereich festlegen

zerknülltes Papier: keine Papierverschwendung mehr dank Excel

So einfach können Sie in Excel den Druckbereich festlegen:

Excel druckt standardmäßig immer das ganze Tabellenblatt.
Um nur einen Teil zu drucken, legen Sie einen Druckbereich an.
Markieren Sie die Daten und wählen Sie Seitenlayout/Seite einrichten/Druckbereich festlegen.
Der Druckbereich ist ein Bereichsname, Sie finden ihn mit dem zugewiesenen Bereich (z.B.$1:$20) im Namens-Manager (mit Strg+F3 aufrufen).
Hier können Sie ihn auch neu definieren oder wieder löschen.

Excel Freitag Linie

Excel Freitag #12: Multiplizieren mit Excel

Mit Excel multiplizieren

Multipliziert wird mit dem *.
Berechnen Sie die MwSt aus drei Rechnungsbeträgen:

A1: 200
A2: 500
A3: 300
B1: =A1*19% Ergebnis: 38

Die Formel mit Doppelklick auf das Füllkästchen nach unten kopieren.
Steht der Faktor in einer eigenen Zelle, muss er mit $ fixiert werden:

D1: 19%
B1:=A1*$1

Um mehrere Zellen zu multiplizieren, können Sie eine Matrixformel verwenden. Das Ergebnis muss aber in eine Summe, da die Matrix nicht in einer Zelle angezeigt werden kann. Drücken Sie STRG+UMSCHALT+EINGABE zum Abschluss:

=SUMME(A1:A3*19%) Mit PRODUKT() multiplizieren Sie einzelne Werte oder ganze Reihen:
=PRODUKT(A1;19%)
=PRODUKT(A1:A3)

Excel Freitag Linie

Excel Freitag #11: Endlich doppelte Daten aufspüren und entfernen

Doppelte Daten (z.B. zweimal Radieschen in der Liste) in Excel vermeiden

Der Bereich A1:A4 enthält doppelte Daten:

A1: Produkt
A2: Tomaten
A3: Gurken
A4: Tomaten
A5: Radieschen

Schreiben Sie in der zweiten Spalte diese Formel, die zählt, wie oft jeder Eintrag vorkommt:

B1: Filter
B2: =ZÄHLENWENN($2:$5;A2)

Schalten Sie im Daten-Register den Filter ein und filtern Sie in Spalte 2 alle Einträge, die größer als 1 sind.

Um doppelte Datensätze zu entfernen, markieren Sie die alle Spalten und wählen Datentools/Duplikate entfernen.
Kreuzen Sie die Spalte mit den doppelten Einträgen und bestätigen Sie mit OK.

Excel Freitag Linie

Excel Freitag #10: Urlaubsplanung mit Excel

Bald ist es wieder soweit: Das Neue Jahr beginnt, und die Urlaubs- und Abwesenheitsplanung muss neu aufgesetzt werden. Mit Excel haben Sie ein fantastisches Werkzeug für die Erstellung eines solchen Planes.

Diese Werkzeuge und Funktionen sollten Sie kennen:
⯈ Eine Liste mit Mitarbeiterdaten (Name, Abteilung, Kostenstelle, Urlaubsanspruch)
⯈ Eine Liste mit den Feiertagen des aktuellen Jahres
⯈ Ein Kalender, der sich mit der Eingabe des Jahres automatisch berechnet
⯈ Bedingte Formatierungen für die Kennzeichnung der Wochenend- und Feiertage im Kalender
⯈ Links (Hyperlinks) zur Ansteuerung der einzelnen Monate im Kalender
⯈ Die Gliederungsfunktion, um Zeilen oder Spalten zu gruppieren
⯈ Die Fensterfixierung, um den Datenbereich vom Kalender zu trennen
⯈ Die Datenüberprüfung für komfortable Eingaben der Abwesenheit
⯈ Funktionen zur Berechnung der Abwesenheitstage und des Resturlaubs

Der Urlaubsplaner als Vorlage zum Download

So sieht das Ergebnis aus:


Ergebnis: Urlaubsplanung mit Excel

Auswertungsjahr in Excel-Zelle  A1

Schreiben Sie das Auswertungsjahr in Zelle A1.


Excel-Zelle mit Bereichsname Jahr

Geben Sie der Zelle den Bereichsnamen „Jahr“, tragen Sie ihn einfach in das Namensfeld ein ([Eingabe] nicht vergessen).


Richtiges Excel-Zahlenformat eingeben

Mit diesem Zahlenformat setzen Sie den Text vor die Zahl:
“Urlaubsplanung“0
Verbinden Sie die Zellen A1:B1.


Ab Excel-Zeile 6 Mitarbeiterdaten eintragen

Die Mitarbeiterdaten tragen Sie ab Zeile 6 ein.


Mitarbeiterdaten in Excel einfügen

Geben Sie die Daten ein oder holen Sie die Liste als Kopie oder als Verknüpfung aus den Personaldaten.


Excel-Spalten markieren

Markieren Sie die Spalten A:E, …


In Excel Daten/Gliederung/Gruppieren wählen

… und wählen Sie Daten/Gliederung/Gruppieren. Setzen Sie die Spalten in die zweite Ebene.


In Excel Spalten gruppieren

Gruppieren Sie auch die Spalten G:K und tragen Sie die Überschriften für die Auswertung ein.


Legende für Urlaubsarten mit Excel-Zeichenwerkzeugen erstellen

Die Legende für die Urlaubsarten zeichnen Sie mit den Zeichenwerkzeugen aus der Formenbibliothek.


Texte in Excel-Textfelder gruppieren

Schreiben Sie die Texte in Textfelder und gruppieren Sie die Objekte.
Weisen Sie den Objekten die Eigenschaft „Von Zellposition und -größe unabhängig“ zu.


Excel-Zelle mit Berechnung der Datumsschiene

Die Zelle M5 bekommt die Formel zur Berechnung der Datumsschiene aus dem aktuellen Jahr:
=DATUM(Jahr;1;Spalte().12)


Excel-Zahlenformat TT

Das Zahlenformat TT sorgt dafür, dass nur der Tag angezeigt wird.


Excel-Zelle M6 bekommt Verknüpfung auf M5

Die Zelle M6 bekommt eine Verknüpfung auf M5 und das Zahlenformat TTT für den abgekürzten Wochentag.


Excel-Formel schreibt Monatsnamen in Zelle

In M4 schreiben Sie eine Formel, die in jeder Spalte, in der ein neuer Monat beginnt, den Monatsnamen in die Zelle schreibt:
=WENN(TAG(M5)=1;TEXT(M5;"MMMM");"")


Excel-Formeln und Füllkästchen kopieren

Kopieren Sie die drei Formeln von M4:M6 mit dem Füllkästchen bis NN4:NN6.


Excel-Bereich mit Datumswerten markieren

Markieren Sie den Bereich mit den Datumswerten bis zum letzten Mitarbeiter.


Bedingte Formatierung wählen

Wählen Sie Start/Formatvorlagen/Bedingte Formatierung.


Bedingte Formatierung mit unterschiedlichen Füllfarben

Legen Sie eine bedingte Formatierung für die Samstage an:
=WOCHENTAG(M$5)=7
… und für die Sonntage:
=WOCHENTAG(M$5)=1
Weisen Sie den Regeln unterschiedliche Füllfarben zu.


Tabelle in neuem Tabellenblatt erstellen

Legen Sie in einem neuen Tabellenblatt eine Feiertagstabelle an (mit [Strg]+t zur Tabelle erklären).


Geben Sie der Tabelle den Namen tbl_Feiertage.


Ostersonntag mit Excel-Formel berechnen

Der Ostersonntag ist variabel, er wird mit dieser Formel berechnet:
=RUNDEN((TAG(MINUTE(Jahr/38)/2+55)&".4."&Jahr)/7;)*7-6+1


Übrige feiertage mit Excel berechnen

Berechnen Sie die übrigen Feiertage.


Bereichsnamen angeben

Legen Sie einen Bereichsnamen Feiertage an, geben Sie ihm die erste Spalte der Tabelle als Bezug:

=tbl_Feiertage[Datum]


Excel-Formel für neue Regel

Mit dieser Formel legen Sie eine neue Regel für den gesamten Kalenderbereich an, mit der die Feiertage farbig markiert werden:

=VERGLEICH(Urlaubsplanung!M$5;Feiertage;0">NICHT))

Innebereich des Excel-Kalenders

Der Innenbereich des Kalenders bekommt eine Datenüberprüfungsliste.

In Excel Zulassen:Liste wählen

Wählen Sie unter
Zulassen: Liste
und geben Sie die Abwesenheiten als Quelle an:
U;U/2;K;D

Für jede Abwesenheitsart eine bedingte Formatierung anlegen

Legen Sie für jede Abwesenheitsart eine bedingte Formatierung an und weisen Sie die in der Legende fixierten Farben zu.

Excel Fehlermeldung festlegen

Legen Sie eine Fehlermeldung für den Fall fest, dass die Datenüberprüfung Fehler bringt.

Abwesenheiten eintragen oder aus Liste holen

Jetzt können Sie die Abwesenheiten eintragen oder aus der Liste holen.

Funktion ZÄHLENWENN

Mit der Funktion ZÄHLENWENN() berechnen Sie im Auswertungsbereich die Anzahl der Abwesenheiten:
=ZÄHLENWENN(INDEX(UTab;ZEILE()-6;);"U")

Excel-Formel, um Resturlaub zu berechnen

Den Resturlaub berechnen Sie mit dieser Formel:
=E7-G7-H7/2

Link in Excel-Zellen einfügen

Fügen Sie in die Zellen C1:E4 noch je einen Link auf die Monatsnamen im Kalender ein. Ein Klick darauf, und der Zellzeiger springt auf den Monat.

Angezeigten Text in die Zelle schreiben

Den angezeigten Text schreiben Sie einfach in die Zelle.

Fensterfixierung in Zelle M einfügen

Fügen Sie noch in der Zelle M eine Fensterfixierung ein. Damit können Sie im Kalender blättern, ohne die Mitarbeiterdaten auszublenden.

Damit ist die Urlaubs- und Abwesenheitsplanung fertig.

Fertige Urlaubs- und Abwesenheitsplanung in Excel
Nach oben
Excel Freitag Linie

Excel Freitag #9:Schnell die Summe berechnen mit der Excel-Funktion SUMMEWENN

So geht SUMMEWENN() und SUMMEWENNS():
SUMMEWENN() liefert die Summe aller Werte, die bei einer Bedingung mit WAHR abschneiden: =SUMMEWENN(Bereich;Suchkriterium;Summe_Bereich)

Mit SUMMEWENNS() können Sie mehrere Bedingungen angeben: =SUMMEWENNS(Summe_Bereich;Kriterienbereich1;Kriterium1;Kriterienbereich2;Kriterium2 …)

Hier eine Einnahmen/Ausgabeliste:
A1: September, B1: 100
A2: Oktober, B2: 200
A3: September, B3: -50
A4: Oktober, B4: -100

Die Summe im September: =SUMMEWENN(A1:A4;"September";B1:B4) Ergebnis: 50

Nur die Einnahmen im September: =SUMMEWENNS(B1:B4;A1:A4;"September";B1:B4;">0")
Ergebnis: 100

Beispiel für die Excel-Funktion SUMMEWENN

Beispiel für die Anwendung von SUMMEWENN in Excel

Excel Freitag Linie

Excel Freitag #8: Mittelwert und Median berechnen mit Excel

Für die Berechnung des arithmetischen Mittels einer Zahlenreihe verwenden Sie MITTELWERT() oder MITTELWERTA() . Erstere bildet das Mittel nur aus Zahlen, MITTELWERTA() bezieht auch Texte mit ein.

A1: 100
A2: Test
A3: 500
C1: =MITTELWERT(A1:A3) Ergebnis: 300 – entspricht: =SUMME(A1:A3)/ANZAHL(A1:A3)
C2: =MITTELWERTA(A1:A3) Ergebnis: 200 – entspricht: =SUMME(A1:A3)/ANZAHL2(A1:A3) Mit MEDIAN() erhalten Sie den Wert in der Mitte einer Zahlenreihe. =MEDIAN(2;3;4;5;6) liefert als Ergebnis 4.

Excel: die Begriffe Mittelwert und Median
Excel Freitag Linie

Excel Freitag #7: Tipps für Formeln in Excel

Für den Alltag: Excel Tipps

Formeln beginnen immer mit =. Sie können auch mit + (Plus) oder – (Minus) starten, das =-Zeichen wird automatisch vorangestellt. Mit [Esc] brechen Sie eine Formel an, mit [Eingabe] wird sie geschrieben.

[F2] öffnet die Zelle zum Editieren der Formel. [Strg]+[Eingabe] verteilt die Formel auf alle markierten Zellen.
Markieren Sie einen Teil in der Bearbeitungsleiste, können Sie diesen mit [F9] berechnen.

Excel Freitag Linie

Excel Freitag #6: Tipps zur AutoSumme

Excel Funktion Autosumme

Die wohl bekannteste und meist eingesetzte Funktion. Um die Summe zum Einsatz zu bringen, bietet Excel ein Symbol an. Das AutoSummen-Symbol ist im Menüband zweimal vertreten, es steht im Register Start rechts außen in der Gruppe Bearbeiten und in der Funktionsbibliothek der Registerkarte Formeln.Für eine automatische Summe markieren Sie die Zahlen zusammen mit den (leeren) Ergebniszellen.

Beispiel1:
C5: 20
C6: 30
C7: 45
Markieren Sie C5:C8.
Klicken Sie unter Start/Bearbeiten auf AutoSumme.

Oder drücken Sie ALT + UMSCHALT + =.

Die Formel in C8: =SUMME(C5:C7)
Das Ergebnis: 95

Beispiel 2:
C5: 20 D5: 21
C6: 30 D6: 31
C7: 45 D7: 46

Markieren Sie C5:E8.
Die AutoSumme zieht alle Zeilen- und Spaltensummen.

Formel in E8: =SUMME(C8:D8)
Ergebnis: 193

Excel Freitag Linie

Excel Freitag #5: WENN() verstehen

Die WENN()-Funktion zählt neben der SUMME() zu den häufigsten Funktionen, die zum Einsatz kommen. WENN() wird immer dann genommen, wenn das Ergebnis von mehr als einer möglichen Bedingung abhängt. Die WENN()-Funktion braucht drei Argumente, a, b und c:
=WENN(a;b;c)

Für a formulieren Sie einen logischen Ausdruck mit Zeichen wie = (gleich), > (größer) oder < (kleiner). In b geben Sie das Formelergebnis für a = WAHR an, in c das Formelergebnis für a = FALSCH.

Beispiel: Berechnen Sie in C21 5% Rabatt für mehr als 100 EUR in C20, sonst 3%:

C21: =WENN(C20>100;C20*5%;C20*3%)
C20: 120
C21: 3
C20: 85,90
C21: 2,46

Hier ist die Wenn()-Funktion im Detail erklärt:


Excel Freitag Linie

Excel Freitag #4: SVERWEIS() ganz einfach

Eine der wichtigsten Funktionen aus der Excel-Funktionsbibliothek ist sicher der SVERWEIS(). In diesem Tipp zeigen wir Ihnen, wie Sie die Funktion und ihre Argumente in den Griff bekommen.
SVERWEIS() sucht einen Wert in der ersten Spalte einer Referenzliste und liefert als Ergebnis den Wert aus einer beliebigen Spalte dieser Liste, der sich in der gefundenen Zeile befindet.
Die Funktion hat maximal 5 Argumente, vier davon müssen Sie besetzen. Das fünfte Argument, der Bereichsverweis ist aber das wichtigste Argument, es bestimmt nämlich, ob der exakte Wert gesucht werden muss oder ob auch der nächstkleine Wert akzeptiert wird. Die Syntax der Formel lautet:

*=SVERWEIS(Suche_nach;Suche_in;Ergebnisspalte;Bereichs_Verweis)*

Beispiel 1: Verkäuferprovision suchen

SVERWEIS Excel-Tipp

Im ersten Beispiel liegt eine Liste mit Verkäuferumsätzen vor:
Sie haben die Aufgabe, zu berechnen, wieviel Provision jeder Verkäufer abhängig von der Höhe seines Umsatzes bekommt:

von 0 bis 10.000 €: 0%
von 10.001 € bis 30.000 €: 3%
von 30.001 € bis 50.000 €: 5%
ab 50.001 € 7%

Excel-Tipps: SVERWEIS

Legen Sie eine Liste mit den Umsatzgrenzen in Spalte 1 und den Provisionssätzen in Spalte 2 an (hier ab Zelle K1):

Geben Sie in Zelle C1 neben der Umsatzliste Provision ein und berechnen Sie in C2 den Provisionssatz für den ersten Verkäufer.

Excel lernen SVERWEIS

Setzen Sie den Zellzeiger in die Zelle C2.

SVERWEIS fx-Symbol

Starten Sie mit Klick auf das fx-Symbol in der Bearbeitungsleiste den Funktions-Assistenten …

Kategorie auswählen

… und wählen Sie die Funktion SVERWEIS aus der Kategorie Nachschlagen und Verweisen (Excel 2016) bzw. Matrix (Excel 2010).

SVERWEIS Suchkriterium

Klicken Sie in das erste Feld Suchkriterium und anschließend in die Zelle B2.

SVERWEIS Matrix

Klicken Sie in das Feld Matrix und markieren Sie den Bereich K1:L5.

SVERWEIS Bezug absolut

Drücken Sie die Funktionstaste F4, um den Bezug absolut zu setzen ($-Zeichen für Zeilen und Spalten).

SVERWEIS Spaltenindex

Geben Sie in das Feld Spaltenindex die Zahl 2 ein.

SVERWEIS ok

Schließen Sie die Formel mit Klick auf OK ab.

SVERWEIS Füllkästchen

Klicken Sie doppelt auf das Füllkästchen rechts unten an der Zelle C2, um die Formel für alle weiteren Verkäufer zu kopieren.

SVERWEIS Provision berechnen

Damit sind die Provisionen für alle Verkäufer berechnet.

Beachten Sie:


  • Die Suchspalte muss immer die erste Spalte in der Referenzliste sein.
  • Bei diesem SVERWEIS muss das letzte Argument Bereich_Verweis nicht besetzt sein oder den Wert WAHR haben. Damit stellen Sie sicher, dass die Funktion den gesuchten Wert oder den nächstkleineren Wert findet.
  • Die erste Spalte in der Referenztabelle (im Beispiel Spalte K) muss aufsteigend sortiert sein.


Beispiel 2: Artikelpreis finden

SVERWEIS Artikelpreis

In diesem Beispiel liegt eine Rechnung für gekaufte Artikeln vor, sie enthält aber nur die Artikelnummer und die Menge.

SVERWEIS Suchen

Die Bezeichnung der einzelnen Artikel und den Einzelpreis finden Sie in einer zweiten Liste, die wieder in Spalte K beginnt. Die erste Spalte enthält die Artikelnummer, in der Spalte daneben steht die Bezeichnung und eine Spalte weiter der Preis.

Suchen Sie mit einem SVERWEIS() die Bezeichnung und den Preis der gekauften Artikel und berechnen Sie den Gesamtpreis (Menge mal Preis).

SVERWEIS zelle C1

Schreiben Sie Bezeichnung in die Zelle C1 und setzen Sie den Zellzeiger in die Zelle C2.

SVERWEIS Funktions-Assistent

Starten Sie den Funktions-Assistenten per Klick auf das fx-Symbol in der Bearbeitungsleiste, …

SVERWEIS Nachschlagen und Verweisen

… und wählen Sie die Funktion SVERWEIS aus der Kategorie Nachschlagen und Verweisen (Excel 2016) bzw. Matrix (Excel 2010).

SVERWEIS Suchkriterium

Tragen Sie in das Feld Suchkriterium die Zelle mit der ersten Artikelnummer (A2) ein.

SVERWEIS Bezug absolut

Setzen Sie den Cursor in das Feld Matrix und markieren Sie die Liste im Bereich K1:M5. Drücken Sie F4, um den Bezug absolut zu setzen.

SVERWEIS Funktionsargumente

Geben Sie für den Spaltenindex die Zahl 2 ein, um den Wert aus der 2. Spalte der Referenzliste zu holen.

SVERWEIS Bereich_Verweis

Geben Sie in das Feld Bereich_Verweis das Wort „Falsch“ ein.

SVERWEIS ok

Bestätigen Sie die Funktion mit Klick auf OK.

Formel kopieren

Kopieren Sie die Formel per Doppelklick auf das Füllkästchen in Zelle C2 nach unten für alle weiteren Artikel.

SVERWEIS Preis berechnen

Berechnen Sie in einer weiteren Spalte den Preis des Artikels. Suchen Sie dazu wieder nach A2 in der Liste im Bereich $1:$5. Geben Sie die Spalte 3 als Ergebnisspalte ein und vergessen Sie nicht, den Bereichsverweis auf Falsch zu setzen.

SVERWEIS Gesamtbetrag

Berechnen Sie in einer weiteren Spalte Betrag den Gesamtbetrag (Menge mal Preis).

Beachten Sie:


  • Gesucht wird immer in der ersten Spalte der Referenzliste.
  • Die Referenzliste ($1:$5) kann beliebig sortiert sein.
  • An Stelle von FALSCH kann im letzten Argument Bereich_Verweis auch eine 0 eingetragen werden.
  • Der SVERWEIS() liefert einen #NV-Fehler, wenn die Artikelnummer nicht in der Referenzliste zu finden ist.

SVERWEIS NV-Fehler

Profi-Tipp:
Erklären Sie die Referenzliste zur Tabelle. Tabellen sind dynamisch, d.h. der Name der Tabelle wird automatisch angepasst, wenn neue Datensätze hinzukommen. Damit laufen Sie nicht Gefahr, im SVERWEIS() einen falschen Bereich zu benutzen.


  1. Setzen Sie den Zellzeiger in die Liste und wählen Sie Einfügen / Tabellen / Tabelle.
  2. Bestätigen Sie den Dialog (Bereich mit Überschrift).
  3. Geben Sie der Tabelle über Tabellentools / Entwurf / Eigenschaften einen Namen (z. B. tbl_Artikel).
  4. Verwenden Sie diesen Namen im Argument Matrix:
    =SVERWEIS(A2;tbl_Artikel;2;FALSCH)

Nach oben
Excel Freitag Linie

Excel Freitag #3: So berechnen Sie Ihre Spritkosten

Legen Sie eine Liste für die Tankbelege an: A2: Datum, B2: km-Stand, C2:km gefahren, D2: Liter, E2: Betrag, F2: Literpreis, G2: Liter/100 km. Geben Sie das erste Datum in A2 ein, die getankten Liter in D2 und den ersten Betrag in E2. Berechnen Sie in F2 den Literpreis: =E2/D2. Ab dem zweiten Eintrag können Sie auch die gefahrenen km und den Verbrauch berechnen: C3: = B3-B2, G3: =D3*100/C3

Hier können Sie die Spritkosten-Tabelle herunterladen

Excel Freitag Linie

Excel Freitag #2: So rechnen Sie richtig mit Prozenten

Steht in A1: 10 und in A2: 120, liefert die Formel =A1/A2% den prozentualen Anteil, also 8,33333 ….

Das Prozentzeichen in der Formel dividiert das Ergebnis durch 100.
Drücken Sie Strg + 1 und schalten Sie um auf die Kategorie „Benutzerdefiniert“.
Jetzt können Sie das Zahlenformat mit Platzhaltern festlegen: 0,00% ergibt 8,33%.

Um eine „normale“ Zahl (z. B. 8,33) als Prozentzahl zu formatieren, setzen Sie das Prozentzeichen in Anführungszeichen: 0,00”%”

Excel Freitag Linie

Excel Freitag #1: So schnell können Sie Spalten einblenden

Zeigen Sie einfach auf die Linie zwischen den beiden Spaltenbuchstaben links und rechts der ausgeblendeten Spalte. Der Doppelpfeil am Zellzeiger hat eine weiße Zwischenlinie, sobald er auf die ausgeblendete Spalte zeigt. Ziehen Sie die Spaltenlinie mit gedrückter Maustaste nach rechts, und die ausgeblendete Spalte wird wieder sichtbar.

Excel Freitag Linie

Verlagsbereiche

FachbuchFachzeitschriftenTagungen und MessenLiteraturKinderbuch

Fachportale

FORM + WerkzeugHANSER automotiveKunststoffeKunststoffe.tvKunststoffe internationalQZ Qualität und ZuverlässigkeitWB Werkstatt + Betrieb

Wissenschaftsportale

HTM Journal of Heat Treatment and MaterialsInternational Journal of Materials ResearchInternational Polymer ProcessingKerntechnikMP Materials TestingPractical MetallographyTenside Surfactants DetergentsZeitschrift KunststofftechnikZWF Zeitschrift für wirtschaftlichen Fabrikbetrieb

Partnerportale

automationbbr Bänder Bleche Rohreenergiespektrum:K Kompetenz für Konstrukteureg+h Gebäudetechnik und Handwerklogistik journalmaschine + werkzeug

Carl Hanser Verlag GmbH & Co. KG · Kolbergerstraße 22 · 81679 München
Telefon: +49 89 99830-0 · Fax: +49 89 984809 · E-Mail