Excel Freitag – Excel-Tipps bei Hanser

Wie fit sind Sie in Excel? Wollen Sie die Grundlagen von Excel kennenlernen? Oder suchen Sie nach Tipps und Tricks, um Ihre Aufgaben in Excel effizienter zu gestalten?

In beiden Fällen sind Sie hier genau richtig! Hier finden Sie hilfreiche Antworten zu Ihren Fragen rund um die Arbeit mit Excel.

Lernen Sie Formeln und Diagramme zu erstellen, Berechnungen durchzuführen und Tabellen sinnvoll zu bearbeiten. Ob es darum geht relative von absoluten Bezügen zu unterscheiden, nützliche Shortcuts zu entdecken oder zeitsparende Techniken zu nutzen – Hanser ist die richtige Adresse für Sie.

Wenn Sie Anleitungen für Ihre Aufgaben in Excel suchen finden Sie hier alles, was Sie wissen müssen, um häufige Fehler zu vermeiden und selbst Excel-Profi zu werden.

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 #41: Power Pivot in Excel – die ersten Schritte

Excel Freitag #40: Was ist Power BI?

Excel Freitag #39: 5 Fragen zu Excel

Excel Freitag #38: Was ist eigentlich VBA?

Excel Freitag #37: Neu in Office 365 - Eindeutige Werte

Excel Freitag #36: Neu in Office 365 - Dynamische Arrays

Excel Freitag #35: F2 - die Tricktaste

Excel Freitag #34: Datenüberprüfungsliste für Monatsnamen

Excel Freitag #33: Datumseingaben auf gültige Werte beschränken

Excel Freitag #32: Richtig filtern

Excel Freitag #31: Wochentag aus Datum generieren

Excel Freitag #30: Erste Zeile fixieren

Excel Freitag #29: Durchstreichen mit Excel

Excel Freitag #28: PivotTable-Einstellungen sichern

Excel Freitag #27: Negative Zeiten eintragen

Excel Freitag #26: Teilergebnisse ermitteln

Excel Freitag #25: Standardabweichung berechnen mit Excel

Excel Freitag #24: Zellen richtig verschieben

Excel Freitag #23: Arbeitszeit berechnen

Excel Freitag #22: Fußball-Auswertung mit Business Intelligence

Excel Freitag #21: Der absolute Bezug

Excel Freitag #20: Diagramme erstellen leicht gemacht

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 #41: Power Pivot in Excel – die ersten Schritte

  • Legen Sie drei Tabellen in einer Arbeitsmappe an:
    tbl_Artikel: Artikelnummer, Bezeichnung, Einkaufspreis und Verkaufspreis
    tbl_Bestellungen: Artikelnr, Menge
  • Holen Sie beide über Power Pivot / Tabellen in das Datenmodell.
  • Verknüpfen Sie die Artikelnummern in der Datenansicht.
  • Legen Sie zwei neue Spalten in der Datenansicht an. Schreiben Sie diese Formeln:
    Kosten: =tbl_Bestellungen[Menge]*RELATED (tbl_Artikel[Einkaufspreis])
    Umsatz: =tbl_Bestellungen[Menge]*RELATED (tbl_Artikel[Verkaufspreis])
  • Erstellen Sie eine PivotTable aus dem Datenmodell.

PowerPivot in Excel

Excel Freitag Linie

Excel Freitag #40: Was ist Power BI?

Power BI ist eine BI-Dienst von Microsoft, bestehend aus Power BI Desktop (Download kostenlos unter powerbi.com), dem Power BI-Dienst in der Cloud und mobilen Apps A (alles kostenlos in der Basisversion). Excel-Arbeitsmappen oder CSV-Dateien sind potenzielle Quellen für die BI-Analyse mit Power BI. Power Query und Power Pivot sind sowohl in Excel (ab 2016) als auch in Power BI Desktop verfügbar.

Mein Tipp: Erste Datenmodelle mit Excel aufbauen, in Power BI Desktop visualisieren und Dashboards in der Cloud erstellen. Macht Spaß!

Excel Freitag Linie

Excel Freitag #39: 5 Fragen zu Excel



Im Rahmen unserer Excel-Umfrage konnten die Teilnehmer das neue Buch von Ignatz Schels Business Intelligence mit Excel gewinnen. Dafür sollten Sie eine nicht ganz ernst gemeinte Begründung schreiben, warum Sie das Buch unbedingt benötigen. Die besten Sprüche haben wir für Sie hier zusammengestellt:


Die beiden Lieblingssprüche vom Excel-Team gibt es hier zum Download:

Postkarten hier herunterladen

Excel Freitag Linie

Excel Freitag #38: Was ist eigentlich VBA?

VBA steht für “Visual Basic for applications”, und das ist eine Programmiersprache. Mit VBA schreiben Sie „Makros“, ablauffähige Prozeduren, die Aktionen in Excel durchführen. Oder Funktionen, die ähnlich wie die internen Funktionen (SUMME(), ZÄHLENWENN() …). Berechnungen durchführen.

Für Ihre ersten Makros nutzen Sie am besten den Makrorecorder, er zeichnet alle Aktionen auf, und Sie können diese anschließend einfach wieder abspielen. Makros können aber auch mit Programmiersprachenelementen wie Variablen, Schleifen, Bedingungen und Array bestückt werden, und die Krönung des Ganzen ist die Programmierung von UserForms. Das sind Dialogboxen mit Formularelementen (Auswahllisten, Ankreuzkästchen, Eingabefelder u.a.).

Eine kleine Anleitung für ein ersten Makro mit dem Makrorecorder finden Sie hier:

Wählen Sie Datei/Optionen

1. Für VBA brauchen Sie die Entwicklertools. Wählen Sie Datei/Optionen.


2. Schalten Sie um auf Menüband anpassen und kreuzen Sie in der Liste der Hauptregisterkarten die Entwicklertools an.

Schalten Sie um auf Menüband anpassen und kreuzen Sie in der Liste der Hauptregisterkarten die Entwicklertools an.

3. Jetzt finden Sie das neue Register im Menüband.

Jetzt finden Sie das neue Register im Menüband

Für Ihr erstes Makro klicken Sie auf Code/Makro aufzeichnen

4. Für Ihr erstes Makro klicken Sie auf Code/Makro aufzeichnen.


Geben Sie einen Makronamen (ohne Leer- oder Sonderzeichen) ein und tragen Sie eine kurze Beschreibung ein. Klicken Sie auf OK, um den Makrorecorder zu starten.

5. Geben Sie einen Makronamen (ohne Leer- oder Sonderzeichen) ein und tragen Sie eine kurze Beschreibung ein. Klicken Sie auf OK, um den Makrorecorder zu starten.


6. Jetzt heißt die Option Aufzeichnung beenden, und links unten in der Statusleiste finden Sie ebenfalls ein kleines Symbol, um den Makrorecorder zu beenden.

Jetzt heißt die Option Aufzeichnung beenden, und links unten in der Statusleiste finden Sie ebenfalls ein kleines Symbol, um den Makrorecorder zu beenden.

7. Starten Sie mit der ersten Aktion: Fügen Sie ein neues Tabellenblatt ein.


Tragen Sie das Tagesdatum ein

8. Schreiben Sie in die Zelle A1 des neuen Blattes die Formel für das Tagesdatum:
=HEUTE()


Stoppen Sie den Makrorecorder

9. Klicken Sie unter Entwicklertools/Code auf Aufzeichnung beenden, um den Makrorecorder zu stoppen.


10. Mit einem Klick auf Code/Makro erhalten Sie die Liste aller aktiven Makros. Markieren Sie Ihr Makro und klicken Sie auf Ausführen. Das Makro wird damit gestartet, es fügt wieder ein neues Blatt ein und trägt das Tagesdatum ein.

Wählen Sie Ihr Makro und klicken Sie auf Ausführen.

11. Um das Makro zu bearbeiten, wählen Sie Code/Makro, markieren das Makro und wählen Bearbeiten.

Bearbeiten Sie Ihr Makro

Der Visual Basic-Editor wird aktiv

12. Jetzt wird der Visual Basic-Editor aktiv, er präsentiert am linken Rand den Projekt-Explorer (falls nicht, unter Ansicht bitte einschalten).
Das Makro befindet sich im Modul Modul1, klicken Sie es doppelt an, falls es nicht offen ist.


Hier sehen Sie den Makrocode mit den aufgezeichneten VBA-Befehlen

13. Im Arbeitsbereich sehen Sie den Makrocode mit den aufgezeichneten VBA-Befehlen. Das Makro beginnt mit

Sub <Makroname>

und endet mit

End Sub.


14. Schreiben Sie noch eine MsBox-Anweisung vor das Makroende.

Schreiben Sie eine MsBox-Anweisung vor das Makroende.

Vor dem Abschluss des Makros erhalten Sie eine Bildschirmnachricht.

15. Damit erhalten Sie eine kleine Bildschirmnachricht vor dem Abschluss Ihres Makros.


Speichern Sie das Makro als Excel Arbeitsmappe mit Makros mit der Dateiendung XLSM

16. Achten Sie darauf, dass die Arbeitsmappe mit dem Makro unter dem Dateityp Excel-Arbeitsmappe mit Makros und der Dateieendung XLSM gespeichert werden muss, damit die Makros erhalten bleiben.

Excel Freitag Linie

Excel Freitag #37: Neu in Office 365 - Eindeutige Werte

Darauf haben wir schon lange gewartet: Office 365 bietet jetzt endlich eine Funktion an, die alle eindeutigen Werte aus einer Liste oder Tabelle ausliest. Beispiel:

Schreiben Sie die Wochentage (Montag – Sonntag) mehrfach in Spalte A. Tragen Sie in Zelle C2 die Formel für einen dynamischen Array mit allen Wochentagen (einmal) ein:

C2: =EINDEUTIG(A1:A8)

Aber Vorsicht: Der Bereich ab C3 muss frei bleiben, sonst gibt es einen #Überlauf! -Fehler!

Excel Eindeutige Werte

Excel Freitag Linie

Excel Freitag #36: Neu in Office 365 - Dynamische Arrays

Mit dieser neuen Technik wird sich Ihre Arbeit mit Matrizen (Arrays) grundlegend ändern. Für dynamische Arrays brauchen Sie nur eine Formel:

Schreiben Sie die Monatsnamen Januar bis Dezember in Spalte A.
Holen Sie das erste Quartal mit dieser Formel in eine Zelle:

C2: =A1:A3

Oder nutzen Sie INDIREKT(), um eine über eine Zelle bestimmte Anzahl Monate abzuholen:

D1: 6
D2. =INDIREKT(“A1:A”&$1)

Aber Vorsicht: Wenn der Bereich für den dynamischen Array nicht leer ist,
gibt es einen #ÜBERLAUF! -Fehler!

Excel Dynamische Arrays

Excel Freitag Linie

Excel Freitag #35: F2 - die Tricktaste

Die Funktionstaste F2 ist meine Tricktaste für besonders lästige und zeitraubende Aktionen in Excel:

Drücken Sie F2, um die aktive Zelle zu öffnen. Der Cursor blinkt am Ende des Zellinhaltes oder der Formel, und Sie können gleich weiterschreiben.

Wenn Sie eine Formel schreiben und mit dem Cursor nach rechts oder links wandern, trägt Excel den Zellbezug ein. Das ist besonders lästig beim Reparieren von Namensbezügen. Drücken Sie F2, schaltet Excel in den „Eingeben“-Modus, und die Schreibmarke lässt sich normal bewegen. Mit F2 geht´s wieder zurück. Links unten in der Statusleiste sehen Sie übrigens den aktuellen Modus.

Excel F2 Tricktaste

Excel Freitag Linie

Excel Freitag #34: Datenüberprüfungsliste für Monatsnamen

Ein einfaches, aber effektives Formularwerkzeug ist die Datenüberprüfungsliste. Geben Sie Monatsnamen, Kostenarten oder andere Daten einfach als Vorschlagsliste in die Zelle ein:

Tragen Sie die Vorschlagsliste in einen freien Bereich ein, geben Sie ihr am besten einen Bereichsnamen (z. B. „Monate“).

Markieren Sie die Zelle(n) und wählen Sie Daten/Datentools/Datenüberprüfung/Einstellungen.

Zulassen: Liste

Quelle: =monate

In den Registerkarten „Eingabemeldung“ und „Fehlermeldung“ können Sie noch Texte für eine Eingabehilfe und für die Fehlermeldung eintragen, die erscheint, wenn die Zelle anschließend etwas anderes bekommt als einen Monatsnamen.

Excel Datenüberprüfungsliste

Excel Freitag Linie

Excel Freitag #33: Datumseingaben auf gültige Werte beschränken

Gebe ich ein falsches Datum ein, liegt das meist nicht an meinem begrenzten Sachverstand – die meisten Fehler passieren einfach durch Unaufmerksamkeit. Mit der Datenüberprüfung sichern Sie Falscheingaben ab und geben noch einen netten Hinweis darauf, wie es richtig sein sollte. Hier am Beispiel „Geburtsdatum“:

Markieren Sie die Zelle(n) für die Eingabe und wählen Sie Daten/Datentools/Datenüberprüfung.
Wählen Sie:

Einstellungen/Zulassen: Datum
Daten: Zwischen
Anfangsdatum: 01.01.1900
Enddatum: =HEUTE()

Unter dem Register „Eingabe“ können Sie einen Hinweis für die Eingabe hinterlassen, und unter „Fehlermeldung“ schreiben Sie den Text, der auf eine Falscheingabe hinweist.

Excel Datumseingaben auf gültige Werte beschränken

Excel Freitag Linie

Excel Freitag #32: Richtig filtern

Filtern will gelernt sein. Wenn Sie eine Liste filtern wollen, markieren Sie diese vorher. Setzen Sie den Zellzeiger in die Liste und drücken Sie Strg + Umschalt + *. Schalten Sie dann den Filter ein (Daten/Sortieren und Filtern/Filtern). Auf keinen Fall nur die Kopfzeile markieren.

Sollte Ihre Liste Leerzeilen enthalten, setzen Sie den Zellzeiger in die erste Zelle und drücken
Strg + Umschalt + Ende. Das markiert den „benutzten Bereich“, und damit können Sie sichergehen, dass alle Daten im Tabellenblatt enthalten sind.

Ganz sichern gehen Sie natürlich wieder mit Tabellen, da funktioniert der Filter immer korrekt.

Excel Filtern

Excel Freitag Linie

Excel Freitag #31: Wochentag aus Datum generieren

Der Wochentag ist bereits in einem Datum enthalten, auch wenn das auf den ersten Blick nicht ersichtlich ist. Schreiben Sie ein Datum in eine Zelle, wird nämlich nur der numerische Wert ausgegeben. Mit dem passenden Zahlenformat holen Sie den Wochentag, drücken Sie Strg + 1 für das Zahlenformat. Am besten aber mit Platzhaltern (Benutzerdefiniert):

Mit dem passenden Zahlenformat holen Sie den Wochentag.

Eine Funktion WOCHENTAG() gibt es auch, die berechnet aber die Wochentagszahl. Geben Sie im zweiten Argument an, wo die Zählung beginnen soll (1 für Sonntag bis Samstag oder 2 für Montag bis Sonntag):

=WOCHENTAG(Datumszelle;1)

oder

=WOCHENTAG(Datumszelle;2)

Excel Wochentag aus Datum generieren

Excel Freitag Linie

Excel Freitag #30: Erste Zeile fixieren

In großen Listen verschwindet die Kopfzeile im Nirwana, wenn nach unten geblättert oder gerollt wird. Muss aber nicht sein.

Wählen Sie „Ansicht/Fenster/Fenster fixieren/Oberste Zeile fixieren“. Soll die erste Spalte beim Blättern nach rechts stehen bleiben, nehmen Sie „Erste Spalte fixieren“. Und für beide, Zeile und Spalte, setzen Sie den Zellzeiger in Zelle B2 und wählen „Fenster fixieren“.

Besser geht´s mit Tabellen: Strg + t macht aus der Liste eine Tabelle. Rollen Sie diese nach unten, springt die Kopfzeile in den Spaltenkopf.

Excel Erste Zeile fixieren

Excel Freitag Linie

Excel Freitag #29: Durchstreichen mit Excel

Text in Zellen unterstreichen, das geht. Aber Durchstreichen? Vielleicht sogar nur einzelne Wörter oder Sätze? Geht auch, und zwar so:

Zelle markieren, Start-Register, Klick auf das Dialogfeldkästchen der Gruppe Schriftart (rechts unten). Das ist der „alte“ Excel-Dialog, und hier finden Sie tatsächlich noch verschiedene Unterstreichungen und die Effekte „Durchgestrichen“ und „Hoch/Tiefgestellt“. Einzelne Wörter oder Sätze markieren Sie vorher in der Bearbeitungsleiste. Und Farbe geht natürlich auch …

Excel Druchstreichen in der Zelle

Excel Freitag Linie

Excel Freitag #28: PivotTable-Einstellungen sichern

Eine besonders nützliche Neuerung beschert uns Excel 2019 bzw. Office 365: Die Einstellungen für PivotTables lassen sich endlich als Vorlage speichern. Bisher musste jede PivotTable von Neuem formatiert und mit dem passenden Layout versehen werden.

Wählen Sie in Excel 2019 Datei/Optionen. In der neuen Kategorie „Daten“ sehen Sie die Datenoptionen. Klicken Sie auf „Standardlayout bearbeiten“ und „Importieren“, um die Einstellungen der aktiven PivotTable für alle weiteren PivotTables zu übernehmen. Mit den anderen Optionen lassen sich weitere Einstellungen vornehmen, zum Beispiel das Tabellenformat im Berichtslayout oder die PivotTable-Optionen (mit „Aktualisieren beim Öffnen der Datei“).

Excel PivotTable Einstellungen sichern

Excel Freitag Linie

Excel Freitag #27: Negative Zeiten eintragen

Negative Zeiten kann Excel nicht anzeigen. Im Tipp Arbeitszeit berechnen hatten Sie einen Trick kennengelernt, um negative Zeiten zu umgehen. Es gibt noch eine Möglichkeit, aber die ist nicht besonders gut: Unter Datei/Optionen/Erweitert gibt es die Option „1904-Datumswerte verwenden“. Damit beginnt der Excel-Kalender erst am 1.1.1904 (wie beim Apple OS), was aber zu falschen Berechnungen führen kann. Hier ein Tipp, wie Sie negative Zeiten auch mit dem Standardkalender berechnen können:

A1: Sollzeit, B1: Istzeit

A2: 8:00, B2: 6:00

C2: =WECHSELN*24;“0,00”);”,”;”:”)

Die Funktion wandelt die Differenz mit *24 in eine Dezimalzahl um, konvertiert sie mit der TEXT-Funktion in einen Text und tauscht in diesem das Komma durch einen Doppelpunkt.

Excel Tipp: Negative Zeiten eintragen

Excel Freitag Linie

Excel Freitag #26: Teilergebnisse ermitteln

Eine der „alten“ Excel-Techniken, die aber noch häufig genutzt wird: Die Funktion =TEILERGEBNIS() ist ein „Container“ für andere Funktionen wie SUMME(), ANZAHL() oder MITTELWERT(). So ziehen Sie Teilergebnisse in eine Liste ein:

Sortieren Sie die Liste nach der Spalte, nach der Sie gruppieren wollen.

Wählen Sie Daten/Gliederung/Teilergebnis. „Gruppieren nach“ enthält die Spalte, die gruppiert wird.
„Unter Verwendung von“ bietet die Formeln an (Summe, Anzahl etc.). Kreuzen Sie in der Liste die Spalte(n) für das Teilergebnis an.
Bestätigen Sie mit OK, enthält die Liste Zwischenergebnisse und Gruppierungen am linken Rand.
Aktivieren Sie die Teilergebnisse noch einmal, können Sie mit „Alle entfernen“ alle Zwischensummen löschen.

Übrigens: In Tabellen gibt es keine Teilergebnisse. Und wenn Sie etwas „moderne“ Auswertungen brauchen, nehmen Sie die PivotTable.

Excel Teilergebnis ermitteln

Excel Freitag Linie

Excel Freitag #25: Standardabweichung berechnen mit Excel

Lust auf ein wenig Statistik? Mittelwert, Varianz und Standardabweichung gehören zur Grundausstattung des Statistikers.

Ein Beispiel: Wenn Golfstar Tiger Woods auf einem Par 3 abschlägt, liegt der Ball nicht weit von der Fahne weg. Eine Werteliste „Messungen“ enthält diese Messungen (so viel wie möglich).

Der Mittelwert liefert den Durchschnittswert:

=MITTELWERT (Messungen)

Die Varianz ist das Streuungsmaß für die Verteilung der Werte um den Mittelwert:

=VAR.P(Messungen) berechnet die Varianz, wenn die Liste die Grundgesamtheit bildet.
=VAR.S(Messungen) schätzt die Varianz ausgehend von einer Stichprobe.

Die Standardabweichung berechnet, wie weit Werte um ihren Mittelwert streuen:

=STABW.N(Messungen) berechnet die Standardabweichung für die Grundgesamtheit.
=STANBW.S(Messungen) berechnet die Standardabweichung, wenn eine Stichprobe vorliegt.

Wenn die Werteliste Texte und logische Werte enthält, können Sie diese mit den Funktionen VARIANZA und VARIANZENA bzw. STABWA und STABWNA mit einbeziehen.

Noch ein Tipp: Wer gut putten kann, dem kann die statistische Abweichung egal sein …

Standardabweichung berechnen

Excel Freitag Linie

Excel Freitag #24: Zellen richtig verschieben

Verschoben wird in Excel nicht mit „leere Spalte einfügen, Zellen reinkopieren und die alten Zellen löschen“. So viel Zeit haben wir nicht …

So geht´s: Spalte mit [Strg] + [Leertaste] markieren, Zeile mit [Umschalt] + [Leertaste]. Jetzt [Umschalt]-Taste gedrückt halten, Mauszeiger an den Rand setzen und mit gedrückter Maustaste ziehen. An der Einfügeposition zuerst die Maustaste loslassen und fertig. Funktioniert natürlich auch mit Zellbereichen.

Probieren Sie es auch mal Strg + Umschalt …

Zeile richtig verschieben

Excel Freitag Linie

Excel Freitag #23: Arbeitszeit berechnen

Mit der Differenz Arbeitsende minus Arbeitsbeginn ist es nicht getan – was machen Sie mit Schichtzeiten? Da ist die erste Zeit größer als die zweite, und Excel präsentiert eine Endloskette.
So berechnen Sie Arbeitszeiten:

A1: Beginn, B1: Ende, C1: Arbeitszeit
A2: 8:30, B2: 16:00
A3: 21:00, B3: 6:00
C2: =WENN

Formel per Doppelklick auf das Füllkästchen nach unten kopieren und der Spalte Zahlenformat h:mm zuweisen. Und mit diesem Zahlenformat zeigt die Stundensumme alle Stunden an:

[h]:mm

Da macht die Arbeit doch gleich wieder Spaß …

Arbeitszeit berechnen

Excel Freitag Linie

Excel Freitag #22: Fußball-Auswertung mit Business Intelligence

Auswertung aller WM-Spiele von 1938 bis 2018 über Business Intelligence

Die etwas andere Fußball-WM-Auswertung mit Business Intelligence

Die schönste Nebensache der Welt wird noch um einiges schöner, wenn 20 Millionen Bundestrainer vor oder nach dem Spiel heiß diskutieren über Mannschaft, Trainer, Spieler und Ergebnisse. Sicher schätzen Ihre Freunde und Kollegen Ihre Expertenmeinung, was das runde Leder betrifft.

Wie wär es, wenn Sie mal richtig mit Fachwissen glänzen könnten?
Wenn Sie Ihre Ratschläge mit Fakten untermauern könnten, die nicht in der Zeitung mit den großen Buchstaben stehen?

Business Intelligence (kurz: BI) heißt das Zauberwort, und der Begriff steht für Datenanalyse und Reporting unter Einbeziehung aller verfügbaren Quellen.
Excel bietet mit Power Query und Power Pivot zwei interessante Werkzeuge, die ab Version 2010 verfügbar sind.

Testen Sie die neue Form der Datenauswertung an unserem vergnüglichen Beispiel:

Holen Sie Daten über die WM-Spiele mit deutscher Beteiligung aus dem Internet, bereiten Sie diese mit Power Query auf und erstellen Sie Dashboards mit Visualisierungen mit Pivot Tabellen oder mit Power BI.

Wir zeigen Ihnen wie es geht!


1. Datenübernahme in Excel

Ganz einfach externe Quellen für Excel nutzen

Im Internet-Portal der Sportzeitschrift Kicker finden Sie Daten zu allen Sportarten und Sportereignissen.

2. Datenübernahme in Excel

Starten Sie Ihren Browser und geben Sie diese Adresse in die URL-Zeile ein.

3. Datenübernahme in Excel

Sie führt zu einer Übersicht über alle Spiele bei Weltmeisterschaften mit deutscher Beteiligung.

4. Datenübernahme in Excel

Kopieren Sie die URL in die Zwischenablage.


5. Daten mit Power Query abrufen

Power Query in Excel 2013 richtig anwenden

Starten Sie Excel und legen Sie eine neue Arbeitsmappe an. Wählen Sie Daten/Daten abrufen und transformieren/Daten abrufen/aus anderen Quellen/Aus dem Web.

Hinweis: In Excel 2010 heißt das Add-In noch Power Query, ab Excel 2013 ist Power Query in der Registergruppe Abrufen und Transformieren integriert.

6. Daten mit Power Query abrufen

Holen Sie die URL mit Strg + v in die URL-Zeile und bestätigen Sie mit OK.

7. Daten mit Power Query abrufen

Die Webseite wird geladen, der Power Query-Editor ebenfalls, er präsentiert alle Tabellen, die auf der Webseite zu finden sind. Kreuzen Sie die Option Mehrere Elemente auswählen an.

8. Daten mit Power Query abrufen

Wählen Sie eine beliebige Tabelle, zum Beispiel Tabelle 17 (WM 2014).

Klicken Sie auf Daten transformieren (Bearbeiten in älteren Versionen).

9. Daten mit Power Query abrufen

Der Power Query-Editor präsentiert jetzt den Tabelleninhalt, Sie können die Tabelle bearbeiten.


10. Abfrageeinstellungen anpassen

Geben Sie der Tabelle in den Eigenschaften gleich einen passenden Namen.

Der Schritte-Editor zeigt die ersten Schritte an. Klicken Sie auf das Löschsymbol, um einen Schritt wieder zu löschen oder auf das Zahnrad, um einen Schritt zu bearbeiten

11. Abfrageeinstellungen anpassen

Fügen Sie mit Spalte hinzufügen eine benutzerdefinierte Spalte hinzu.

12. Abfrageeinstellungen anpassen

Geben Sie der Spalte den Spaltennamen Jahr und tragen Sie die Formel ein, mit der jeder Datensatz die Jahreszahl bekommt: =2014

13. Abfrageeinstellungen anpassen

Mit der rechten Maustaste öffnen Sie das Kontextmenü der Spalte, ändern Sie den Datentyp der Spalte in Text.

14. Abfrageeinstellungen anpassen

Fügen Sie eine weitere benutzerdefinierte Spalte ein, nennen Sie diese Datum und tragen Sie diese Formel ein: =Text.Start([Anstoß2],6)&[Jahr]
Damit kombinieren Sie das Datum aus der Spalte Anstoß2 mit dem eingetragenen Jahr.
Hinweis: Achten Sie darauf, dass Power Query bei Formeln exakt zwischen Groß- und Kleinschreibung unterscheidet.

15. Abfrageeinstellungen anpassen

Jetzt können Sie den Datentyp der Spalte auf Datum umstellen, und die die Spalte präsentiert das Datum der Begegnungen.

16. Abfrageeinstellungen anpassen

Bearbeiten Sie die Spalte Ergebnis. Sie enthält nämlich nicht nur reine Ergebnisse.
Entfernen Sie die Texte „n.V.“ (nach Verlängerung“ und „i.E.“ (im Elfmeterschießen).

Wählen Sie Werte ersetzen… im Kontextmenü.


17. Werte ersetzen

Suchen Sie nach „n.V.“ und einem geschützten Leerzeichen. Das Sonderzeichen dafür holen Sie aus der Liste am unteren Rand. Lassen Sie das Feld Ersetzen durch frei und klicken Sie auf OK.

18. Werte ersetzen

Löschen Sie so auch den Text „i.E.“, ersetzen Sie ihn durch nichts.


19. Nach dem Trennzeichen teilen

Jetzt können Sie die Spalte teilen. Wählen Sie den Befehl im Kontextmenü.

20. Nach dem Trennzeichen teilen

Das Trennzeichen ist wieder das geschützte Leerzeichen. Wählen Sie für die Aufteilung Beim äußersten linken Trennzeichen.

21. Nach dem Trennzeichen teilen

Markieren Sie mit gedrückter Strg-Taste alle Spalten, die Sie entfernen wollen.

22. Nach dem Trennzeichen teilen

Wählen Sie im Kontextmenü Spalten entfernen.

23. Nach dem Trennzeichen teilen

Ziehen Sie die Datumsspalte nach vorne an die erste Position.


24. Den Gewinner ermitteln

Fügen Sie noch eine benutzerdefinierte Spalte für den Gewinner ein.

25. Den Gewinner ermitteln

Mit dieser Formel ermitteln Sie den Gewinner der einzelnen Begegnungen: = if Text. Before Delimiter ([Ergebnis],”:”) >Text.AfterDelimiter([Ergebnis],”:”)then [Mannschaft I] else if Text.BeforeDelimiter([Ergebnis],”:”)<Text.AfterDelimiter([Ergebnis],”:”) then [Mannschaft II] else “Unentschieden”/


26. Der M-Code

Damit ist die erste Tabelle fertig bearbeitet. Schalten Sie unter Ansicht um auf den Erweiterten Editor.

27. Der M-Code

Hier finden Sie alle Abfrageschritte im M-Code (M ist die Abfragesprache von Power Query). Achten Sie auf die dritte und vierte Zeile, hier finden Sie die einzigen Unterscheidungen zwischen den Tabellen: Data0 = Quelle{}[Data] Jahr = Table.AddColumn(Data0, “Jahr”, each ). Diesen mit Strg + c kopieren und Erweiterten Editor schließen.


28. Nächste Abfrage beginnt

Holen Sie für die nächste Abfrage mit Start/Neue Abfrage/Zuletzt verwendete Quelle die Webabfrage wieder.

29. Nächste Abfrage beginnt

Wählen Sie eine weitere Tabelle und schalten Sie gleich in den Erweiterten Editor.

30. Nächste Abfrage beginnt

Achten Sie auf die Tabellennummer. Markieren Sie den alten Code und entfernen Sie ihn.

31. Nächste Abfrage beginnt

Holen Sie mit Strg + v den neuen Code aus der Zwischenablage und ändern Sie die beiden Zeilen. Fügen Sie den Tabellennamen und das Jahr ein. Schließen Sie den Editor mit OK und benennen Sie die Abfrage gleich mit der Jahreszahl.

32. Nächste Abfrage beginnt

Auf diese Weise können Sie schnell und risikolos alle WM-Jahre in den Power Query-Editor holen.

33. Nächste Abfrage beginnt

Wählen Sie Start/Kombinieren/Abfragen anfügen/Abfragen als neu anfügen.

34. Nächste Abfrage beginnt

Schalten Sie um auf die zweite Option (Drei oder mehr Tabellen) und holen Sie mit Hinzufügen alle Tabellen in die rechte Liste.

Die verfügbaren Tabellen können Sie mit der Umschalt-Taste markieren.

35. Nächste Abfrage beginnt

Die neue Abfrage bekommt den Abfragenamen Alle.


36. Anzahl der Gegentore und Tore pro Partie

Berechnen Sie noch die Anzahl der Tore und der Gegentore pro Partie. Fügen Sie dazu je eine neue Spalte ein und schreiben Sie diese Formeln: Text.BeforeDelimiter([Ergebnis],”:”) Text.AfterDelimiter([Ergebnis],”:”)

37. Anzahl der Gegentore und Tore pro Partie

Formatieren Sie die beiden neuen Spalten mit dem Datentyp ganze Zahl.

38. Anzahl der Gegentore und Tore pro Partie

Schließen und Laden Sie alle Abfragen mit dem Befehl im Start-Menü.


39. Power Query erstellt eine Tabelle

Power Query erstellt für jede Abfrage eine eigene Tabelle. Klicken Sie die Abfrage in der Abfrageliste an und wählen Sie im Kontextmenü Laden in.

40. Power Query erstellt eine Tabelle

Mit der Option Nur Verbindung erstellen entfernen Sie die Tabellen und laden diese nur als Verbindung.
Bestätigen Sie die Warnmeldung und löschen Sie die zuvor angelegten Tabellenblätter. Die einzige Tabelle, die übrigbleibt, ist die aus der Abfrage Alle.


41. Alle Ergebnisse mit deutscher Beteiligung

Und das ist das Ergebnis: Die Tabelle enthält alle Begegnungen mit deutscher Beteiligung in allen WM-Turnieren von 1938 bis 2018.

42. PivotCharts

Die Diagramme erstellen Sie über PivotCharts.

43. Gruppieren nach Datum

Hier gruppieren Sie das Datum nach Jahren und filtern die Daten (Gewinner = Deutschland). Berechnen Sie die Anzahl der Gewinner.

44. Das TreeMap

Das TreeMap lässt sich nicht aus PivotTables erstellen, kopieren Sie die Daten zuvor in eine Liste.

45. Das Kreisdiagramm

Das Kreisdiagramm lässt sich auch mit diesem Typ anlegen, der die Daten einer Gruppe (hier die Mannschaften, gegen die einmal verloren wurde) in einer Liste zusammenfasst (die Liste wurde hier als Screenshot einkopiert).

46. Das Balkendiagramm

Hier ein Balkendiagramm mit der Gegenüberstellung von Toren und Gegentoren. Die Pokalgrafik wird einfach über die Zwischenablage auf den Datenpunkt des WM-Siegerjahres kopiert.



Und wenn Sie fleißig Mitglied unserer WhatsApp Gruppe sind, erfahren Sie bald auch, wie man ein Spannweitendiagramm erstellt.

So eines brauchen Sie nämlich, um dem Bundestrainer mitzuteilen, an welchen Wochentagen seine Jungs am häufigsten gewinnen …


Sie sehen, mit den richtigen Werkzeugen sind der Datenanalyse keine Grenzen gesetzt.

Business Intelligence - Abschließend

Business Intelligence mit Excel Ihr Leitfaden in die Welt der professionellen Datenanalyse

Wir haben Ihnen einen kleinen Ausschnitt gezeigt, was mit Business Intelligence möglich ist.

Das Buch vermittelt Ihnen ein Grundlagenwissen zum Thema und hilft Ihnen schnell und praxisnah mit BI zu arbeiten.

Nutzen Sie die Möglichkeiten der Datenanalyse, Datenaufbereitung und Visualisierung (Pivot Tabellen, Power BI) beruflich und privat.

Business Intelligence mit Excel von Ignatz Schels


Excel Freitag Linie

Excel Freitag #21: Der absolute Bezug

Mit F4 legen Sie einen absoluten Bezug fest.

… unterscheidet sich vom relativen Bezug durch das $-Zeichen:

A1: 19%
A5: Betrag netto
A6: 100
A7: 200
B5: MwSt
B6: Schreiben Sie =A6*
Klicken Sie auf die Zelle A1.
Drücken Sie F4, um den Bezug absolut zu setzen (bei Notebooks evtl. die Fn-Taste gedrückt halten).
Drücken Sie weiter F4, werden alle Varianten durchgespielt (Zeile absolut,
Spalte absolut, alles wieder relativ).
Wird die Formel nach unten kopiert, bleibt der Bezug auf $1.
B6: =A6*$1
B7:=A7*$1
In großen Listen wird der Dollarbezug häufig durch Tabellen und strukturierte Verweise abgelöst. Aber dazu gibt es sicher einen weiteren Tipp …

Excel Freitag Linie

Excel Freitag #20: Diagramme erstellen leicht gemacht

Erstellen Sie eine Tabelle um daraus ein Diagramm zu generieren.

Für ein Diagramm brauchen Sie eine Liste oder besser eine Tabelle:
A1: Monat, B1: Nord, C1: West, D1: Süd, E1: Ost
A2: Januar (mit Füllkästchen bis A5 – April – füllen)
Tragen Sie Werte in B2:E5 ein, drücken Sie Strg + t für eine Tabelle.
Wählen Sie mit dem Zellzeiger in der Tabelle Einfügen/Diagramme. Suchen Sie den passenden Diagrammtyp.
In Säulen, Balken und Linien bildet die erste Spalte die X-Werte, die erste Zeile die Legende. Das Kreisdiagramm kann nur die erste Reihe abbilden, im Punktediagramm brauchen Sie Zahlen in der ersten Spalte. Formatiert wird über das Register Diagrammtools.
Und noch ein Tipp zur Gestaltung: KISS (keep it simple and stupid).

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