Excel Freitag – Excel-Tipps bei Hanser

Ab 3.9.2018 schicken wir Ihnen per WhatsApp jeden Freitag 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 #3: So berechnen Sie Spritkosten

Excel Freitag #2: So rechnen Sie richtig mit Prozenten

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

SVERWEIS() ganz einfach

Excel Freitag Linie

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

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 einen 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)


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