Mit Excel günstigsten Preis und günstigsten Anbieter finden

Wenn in einem Excel-Tabellenblatt für mehrere Produkte die Preise diverser Anbieter hinterlegt sind, ist es interessant zu wissen, wer ein bestimmtes Produkt am günstigsten anbietet. Durch eine Kombination der MIN-Funktion mit diversen Matrixfunktionen können Sie diese Informationen schnell ermitteln.

In der folgenden Tabelle sind für 10 Produkte die Preise von 6 Anbietern aufgelistet. Zur Kontrolle haben die Zellen, die für jedes Produkt den jeweils günstigsten Preis enthalten, durch die Anwendung der bedingten Formatierung eine grüne Hintergrundfarbe.

Bild1

Um den günstigsten Preis für Produkt 1 zu ermitteln, gehen Sie bitte folgendermaßen vor:

  1. Geben Sie dem Zellbereich A2:A11 den Namen Produkte.

  2. Geben Sie dem Zellbereich B1:G1 den Namen Anbieter.

  3. Schreiben Sie in Zelle A15 den Namen des gewünschten Produkts.

  4. In Zelle B15 soll der günstigste Preis erscheinen. Erfassen Sie dazu bitte die folgende Formel: =MIN(BEREICH.VERSCHIEBEN(Anbieter;VERGLEICH(A15;Produkte;0);0))

Im ersten Schritt wird die Zeile ermittelt, in der sich das ausgewählte Produkt befindet. Die Funktion VERGLEICH(A15;Produkte;0) sucht in dem Zellbereich Produkte nach dem Produkt 1. Das Ergebnis der Funktion ist 1, da sich das Produkt an der ersten Stelle befindet. Danach wird der auszuwertende Zellbereich Anbieter (B1:G1) um 1 Zeile nach unten verschoben. Die Funktion BEREICH.VERSCHIEBEN(ANBIETER;1;0) liefert den Zellbereich B2:G2 zurück. Der dritte Parameter in der Funktion mit dem Wert 0 bedeutet, dass keine Spaltenverschiebung stattfindet. Im letzten Schritt wird mit der Funktion MIN(B2:G2) der kleinste Wert bzw. der günstigste Preis für Produkt 1 ermittelt.

Wenn in der Zelle C15 der günstigste Anbieter ermitteln werden soll, wird die Funktion etwas umfangreicher: Sie lautet:

=INDEX(Anbieter;1;VERGLEICH(B15;BEREICH.VERSCHIEBEN(Anbieter;VERGLEICH(A15;Produkte;0);0);0))

Der folgende Teil der obigen Formel wird zuerst ausgewertet und wurde bereits bei der Ermittlung des günstigsten Preises verwendet. Er dient der Ermittlung des Zellbereichs, in dem sich die Preise des Produkts 1 befinden.

BEREICH.VERSCHIEBEN(Anbieter;VERGLEICH(A15;Produkte;0);0)

Als Ergebnis wird der Zellbereich B2:G2 zurückgegeben:

Danach wird die folgende Funktion ausgewertet:

VERGLEICH(B15;B2:G2)

Mit dieser Funktion können Sie ermitteln, an welcher Stelle sich im Zellbereich B2:G2 der günstigste Preis, der in Zelle B15 steht, befindet. Als Ergebnis wird 2 zurückgegeben, weil er sich an der zweiten Stelle befindet. Zuletzt wird der folgende Ausdruck ausgewertet:

INDEX(Anbieter;1;2)

Mit der INDEX-Funktion wird ein bestimmter Wert aus einem Zellbezug gesucht.

Mit dieser Funktion wird der Inhalt der 1. Zeile und der 2. Spalte im Zellbereich Anbieter (B1:G1) zurückgegeben. Der günstigste Anbieter ist demnach Anbieter 2.

Bild2

Hinweise

  • Wenn es für ein Produkt mehr als einen Anbieter mit dem gleichen, günstigsten Preis gibt, wird der erste von links gefunden.

  • Wenn ein Anbieter ein Produkt nicht anbietet, sollte die Zelle leer bleiben. Sie sollten die Funktion =NV() als Kennzeichnung dafür, dass ein Wert nicht vorhanden ist, nicht einsetzen, da die oben angegebenen Formeln sonst nicht funktionieren.

  • Um in Zelle A15 nur Produkte auswählen zu können, die es in der Liste gibt, sollten Sie die folgende Gültigkeitsregel einrichten:

    Bild3

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

10 Kommentare

  1. Hallo,

    ich würde gerne den höchsten und niedrigsten Preis eines Produkts durch Excel ausfindig machen.

    Leider hat es mit den angegebenen Formeln nicht funktioniert, was mach ich da falsch?

    Antworten
    • Hallo Frau Schwindt,

      möglicherweise liegt es an der Vergabe der Zellbereichsnamen, warum die Formel bei Ihnen nicht funktioniert.
      Wenn Sie den höchsten Preis eines Produkts ermitteln wollen, ersetzen Sie in der folgenden Formel die Funktion MIN durch MAX.
      =MIN(BEREICH.VERSCHIEBEN(Anbieter;VERGLEICH(A15;Produkte;0);0))

      Antworten
  2. Ich würde gerne Markierungen automatisch letzten lassen, exakt wie im Bild oben.

    Also jeweils für ein Produkt den Preis beim günstigsten Anbieter grün markieren.

    Ohne das eine Auswahl in einer bestimmten Zelle (hier B15) notwendig ist.

    Gibt es dafür eine Lösung

    Antworten
  3. Hallo,
    ich komme mit dem vorgestellten nicht hin. Ich habe Zeiten zu addiere, eigendlich ganz einfach (im Kopf) aber mit der Dauer ;_) (Jahre) wird das mühsam, also Z.B
    10:20
    10:15
    11:09
    10:20
    9:12 sind addiert 50 Std und 76 Minuten
    51:16
    Wie kann ich mit Hilfe von Excel nun dieses addieren und in einer Dezimalzahl darstellen?
    Heinrich

    Antworten
    • Hallo Herr Pleßner,

      Sie finden die Tipps zur Berechnung von Uhrzeiten hier.
      Rechnen mit Uhrzeiten

      Antworten
  4. Danke, perfekt.

    Antworten
  5. mir hat das leider nicht geholfen da das nicht mein Anforderungs bergreich entsprach.

    Antworten
  6. Supper!

    Antworten
  7. Trotz korrekter Formeleingabe immer wieder Fehlermeldung

    Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Pin It on Pinterest

Share This