In diesem Artikel zeigen wir, wie Sie einen Angebotsvergleich in Excel durchführen können. Wir stellen dabei zwei Wege vor.
Hinweis
Um alle Schritte komplett nachvollziehen zu können, sollten Sie mit den grundlegenden Eigenschaften der WENN-, VERGLEICH- und INDEX-Funktion vertraut sein. Links zu den entsprechenden Artikeln im Microsoft-Support finden Sie am Ende des Artikels. Eine eingebettete Beispiel-Tabelle finden Sie ebenfalls am Ende des Artikels.
Um Ihnen die beiden Berechnungswege besser zeigen zu können, haben wir ein Beispiel für Sie vorbereitet. Dabei möchten wir drei Angebote vergleichen.
Excel soll uns in Zelle B18 den Namen des günstigsten Anbieters anzeigt und in Zelle B19 soll der dazugehörige günstigste Angebotspreis dargestellt werden.
Unsere Beispiele für einen Angebotsvergleich in Excel sind stark vereinfacht; das Prinzip sollte aber klar werden und Sie können die Basis-Funktionen auf Ihre Tabelle übertragen.
Angebotsvergleich Excel – “INDEX” & “VERGLEICH”
Als Erstes schauen wir uns an, wie Sie in Excel einen Angebotsvergleich mit Hilfe einer Kombination aus den Funktionen INDEX() und VERGLEICH() durchführen können.
Syntax
1. Syntax für den Angebotsvergleich in Excel:
Allgemein:
=INDEX(Matrix;VERGLEICH(MIN(Vergleichsbereich);Vergleichsbereich;0))
In unserem Beispiel:
=INDEX($B$5:$G$5;VERGLEICH(MIN(C16:G16);C16:G16;0))Erklärung zur Funktion:
- =INDEX(Matrix […] : Diese Excel-Funktion gibt den Wert aus einer Matrix (auch Array oder Datenreihe) zurück – welchen genau, das bestimmen Sie, indem Sie eine Zeilen- und optional auch noch eine Spaltennummer angeben. In unserem Beispiel umfasst die Matrix den Zellbereich von $B$5:$G$5 (vergessen Sie nicht einen absoluten Bezug herzustellen). Diese Datenreihe enthält nämlich die Namen der Anbieter.
- VERGLEICH(MIN(Vergleichsbereich);Vergleichsbereich;0): Diese Funktion vergleicht – wie der Name schon sagt – einen bestimmten Wert (in unserem Beispiel das Minimum aus C16 bis G16) mit dem Wert aus einer Datenreihe (in unserem Beispiel C16 bis G16) und gibt dann den Index des ersten Treffers zurück.
- MIN(): Die Minimum Funktion sucht nach dem kleinsten Wert in dem Bereich, welcher innerhalb der Klammer angegeben wird.
- 0: Durch die Null signalisieren wir Excel, dass wir eine exakte Übereinstimmung suchen.
- ): Optional kann man bei der INDEX-Funktion noch eine Spaltennummer angeben; da wir aber durch die MIN-Funktion automatisch die Spalte ermitteln (nämlich die mit dem kleinsten Wert), brauchen wir die Spalte nicht explizit angeben.
Rechenbeispiel
Folgen Sie der folgenden Anleitung, um einen Angebotsvergleich in Excel durch eine Kombination aus den Funktionen INDEX() und VERGLEICH() durchzuführen.
1. Schritt: Klicken Sie in die Zielzelle, in welcher Sie den Namen des Anbieter mit dem günstigsten Angebot anzeigen lassen möchten.
2. Schritt: Geben Sie die Funktion wie oben zu sehen ein, und ersetzen Sie dabei unsere Werte durch Ihre eigenen.
3. Schritt: Wenn Sie sich den dazugehörigen billigsten Angebotspreis auch anzeigen lassen möchten – wie hier in diesem Beispiel in Zelle B19 -, dann geben Sie die MIN()-Funktion ein und geben Sie in der Klammer den Bereich an, in welchem Excel nach dem kleinsten Wert suchen soll (in unserem Fall in Zeile 16, in welcher das Ergebnis der Kalkulation für den Einzelpreis steht).
Angebotsvergleich Excel – “WENN”
Alternativ können wir auch mit einer verschachtelten WENN-Funktion zum selben Ergebnis gelangen. Wir prüfen dabei drei Bedingungen und basierend darauf wird dann ein entsprechendes Ergebnis angezeigt.
Syntax
2. Syntax für den Angebotsvergleich in Excel:
Allgemein:
=WENN(Bedingung1;WENN(Bedingung2;Wert_wenn_wahr_1;Wert_wenn_falsch_1);WENN(Bedingung3;Wert_wenn_wahr_2;Wert_wenn_falsch_2))
In unserem Beispiel:
=WENN(C16<E16;WENN(C16<G16;$B$5;$F$5);WENN(E16<G16;$D$5;$F$5))Erklärung zur Funktion:
Die Formel beginnt mit der Überprüfung der Bedingung C16<E16 (erste Bedingung). Wenn diese Bedingung wahr ist, dann geht Excel zur Überprüfung der zweiten Bedingung (C16<G16) über. Wenn diese Bedingung ebenfalls wahr ist, gibt Excel den Wert in Zelle $B$5 zurück.
Sollte herauskommen, dass diese Bedingung falsch ist, dann gibt Excel den Wert $F$5 zurück.
Wenn die erste Bedingung (C16<E16) falsch ist, überprüft Excel schließlich die dritte Bedingung (E16<G16). Sollte diese Bedingung wahr sein, gibt Excel den Wert aus Zelle $D$5 zurück. Wenn diese Bedingung falsch ist, gibt Excel den Wert aus Zelle $F$5 zurück.
- WENN(): Die WENN-Funktion ist die Hauptfunktion in dieser verschachtelten Funktion. Die WENN-Funktion enthält in unserem Beispiel zwei weitere WENN-Funktionen. Dadurch können wir mehrere Bedingungen überprüfen. Ja nachdem, ob die Bedingung wahr oder falsch ist, überprüft Excel als nächstes eine andere Bedingung und gibt entsprechende Werte zurück, die wir zuvor festgelgt haben.
Rechenbeispiel
Folgen Sie der folgenden Schritt-für-Schritt Anleitung für einen Angebotsvergleich mit Hilfe der verschachtelten WENN() Funktion in Excel.
1. Schritt: Klicken Sie in die Zielzelle, in welcher Sie den Namen des Anbieters mit dem billigsten Angebot anzeigen lassen möchten.
2. Schritt: Geben Sie die Funktion wie oben beschrieben ein und ersetzen Sie dabei unsere Werte durch Ihre eigenen.
3. Schritt: Wenn Sie sich den dazugehörigen günstigsten Angebotspreis auch anzeigen lassen möchten – wie hier in diesem Beispiel in Zelle B19 -, dann geben Sie die MIN()-Funktion ein. Um nochmal nachzulesen wie das funktioniert, schauen Sie gerne weiter oben im Artikel.
Interaktives Beispiel: Angebotsvergleich Excel
Das Excel aus den Beispielen oben finden Sie hier unten als eingebettete Datei vor. Nach der Freigabe können Sie die Zellen darin doppelt anklicken und die Formeln einsehen. Wenn Sie diese auch bearbeiten oder weiterverwenden wollen, laden Sie die Datei bitte über die Schaltfläche am unteren Rand herunter (fünftes Symbol von rechts).
Links:
VERGLEICH-Funktion bei Microsoft
