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 #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 #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