Beispiel SVerweis - MS Excel Kurs

Im Büroalltag muss man häufig Werte aus verschiedenen Tabellen entnehmen, sei es die anfallenden Kreditzinsen für eine Investition, der Zollsatz für eine bestimmte Warengruppe, die Lohnsteuer oder den Sozialversicherungsbetrag für ein bestimmtes Gehalt.

Mit der Funktion SVERWEIS sind diese Aufgaben sehr einfach lösbar.

Beispiel: Betriebskostenabrechnung mit SVerweis

Je nach Größe und Lage einer Wohnung werden unterschiedliche Betriebskosten verrechnet und unsere Aufgabe ist es, ein Formular zu erstellen, das für die unterschiedlichen Wohnungen automatisch die monatlichen Gesamtkosten errechnet.

Angaben für die Kostenarten:
Monaltiche Heizkosten für eine Wohnung mit

bis 49 m2 ... 49.- €
ab 55 m2 ... 67.- €
ab 75 m2 ... 96.-€
ab 95 m2 ... 127.- €
ab 125 m2 ... 159.-€.

Liftgebühren abhängig vom Stockwerk. Befindet sich die Wohnung

ab dem 1. Stock ... 12.- €
ab dem 3. Stock ... 24.- €
ab dem 5. Stock ... 39.- €
ab dem 7. Stock ... 59.- €.

Nachfolgend haben wir einen Betriebsabrechnungsbogen (Tabelle) für alle Wohnungen erstellt, wo ebenfalls in zwei Tabellen (Spalte F + G) die Kostenarten aufgelistet wurden. Mithilfe des SVerweises wollen wir nun die Heizkosten und die Liftgebühr je Wohnung ermitteln. Danach bilden wir noch die Summe der beiden Kostenarten.

Beispiel SVerweis - Bild 1

Nachfolgend verwenden wir die Funktion SVerweis um aus der Tabelle "Heizkostenbeitrag" (C41:D46) den Wert "Heizkosten" für das Feld (F29) zu ermitteln.
Die Funktion lautet: =SVERWEIS(D29;$C$42:$D$46;2)

Beispiel SVerweis - Bild 2

Was bedeutet diese Formel?

=SVERWEIS(D29;$C$42:$D$46;2)

Mithilfe der Funktion SVERWEIS soll ein Wert aus einer vorgegebenen Tabelle ermittelt/gesucht weden. SVerweis steht für Suchverweis.

Das ERSTE KRITERIUM D29 steht für das Suchkriterium. Nach welchem Wert soll in der Tabelle (C42:D46) gesucht werden. In unserem Fall nach dem Wert 56(m²).

Das ZWEITE KRITERIUM zeigt an wo sich die Tabelle/Matrix befindet, in der gesucht werden soll. In unserem Fall eben in ($C$42:$D$46). !!! Achtung nicht vergessen: ABSOLUT setzen, damit die Formel beim Kopieren immer im gleichen Bereich sucht!!!

Das DRITTE KRITERIUM gibt an, dass aus unserem Matrix/Datenbereich ($C$41:$D$46) der Wert aus der 2. Spalte zurückgegeben werden soll. In unserem Fall wird der Wert 67 ermittelt, da ja das Suchkriterium 56m² war. Die Funktion SVerweis erkennt in der Matrix den Wert 55 und zählt einfach weiter. Der nächste Wert wäre erst wieder 75, somit wird der Wert 67 ermittelt.

Jetzt wird die Formel von F30 bis F38 kopiert und Excel "findet" für jede Wohnfläche den richtigen Wert.

Beispiel SVerweis - Bild 3

Zur Ermittlung der jeweiligen Liftgebühr gehen wir genau gleich vor. Wir verwenden die Funktion SVwerweis mit folgenden Werten. =SVerweis(E29;$F42:$G$45$;2). Danach brauchen wir diese Formel nur noch in das Feld G30:G38 kopieren und die Formel sucht die jeweilige Liftgebühr.

Für die Ermittlung der Gesamtkosten im Feld H29 verwenden wir eine einfache Grundrechnung (=F29+G29). Diese Formel kopieren wir dann in die Felder H30:H38.

Viel Spass beim Nachmachen und Lernen!!!