SVerweis mit komplexer Suche? Never! Excel 365 – Ever!

Alexander Achenbach/ Datenanalyse, Excel

Eine unlösbare Aufgabe? Die Antwort lautete und lautet: Ja. Die aus den 90ern stammende SVerweis-Funktion, aber auch die neue XVerweis-Funktion ist für genau ein einfaches Suchkriterium gemacht. In Excel 365 gibt es aber einen neuen Lösungsansatz, der ohne Umbau einer Tabelle oder wüster Hilfsberechnungen auskommt. Einfach, straight, forward!

Wir beginnen mit einer übersichtlichen, aber bisher unlösbaren Problemstellung:

Zeitbasierte Preisliste (Preise)

Wir haben drei Artikel, die in bestimmten Zeiträumen unterschiedliche Preise haben. Der Artikel A1 ist der einfachste, denn er kostet immer 3,99 €. Der Artikel A2 hat in vier Zeiträumen unterschiedliche Preise. Der Artikel A3 hat drei unterschiedliche Zeiträume und Preise.

Nun sollen diese Preise in einem Kassenbuch zugeordnet werden:

Bon-Liste zur Zuordnung

Einfach mal eben so? Ja! Das Geheimnis liegt darin, nicht um die Ecke zu denken sondern einfach nur das Wesentliche zu sehen; was wird zugeordnet?

  1. Wir benötigen Preise[Preis].
  2. Der Artikel muss übereinstimmen.
  3. Das Datum muss >= Preise[Von] sein.
  4. Das Datum muss <= Preise[Bis] sein.

Wir gehen natürlich davon aus, dass es genau einen Preis zu den genannten Bedingungen gibt, aber das war es. Und wie? Mit der FILTER-Funktion von Excel 365.

FILTER ( Matrix ; einschliessen ; wenn_leer )

Matrix: Ein Bereich von Zellen, die gefiltert und zurück geliefert werden sollen.
einschliessen: Vergleichskriterien (vgl1) * (und vgl2) + (oder vgl3)
wenn_leer: Ersatzwert, falls nichts gefunden.

Es ist zu beachten, dass bei mehreren Kriterien jeder einzelne Vergleich in Klammern () zu setzen ist und diese Vergleiche mit * (für und) oder + (für oder) verbunden werden.

Matrix ist die Spalte Preis unserer Preisliste (s. 1). Einschliessen ist die Kombination aus Artikel (s, 2) und Von (s. 3) und Bis (s. 4). Wir benötigen also drei Vergleiche (Artikel)*(Von)*(Bis), die mit einem logischen UND (*) verknüpft werden müssen.

=FILTER(Preise[Preis];([@Artikel]=Preise[Artikel])*([@Datum]>=Preise[Von])*([@Datum]<=Preise[Bis]);0)

Das kann etwas strukturierter vielleicht besser verstanden werden:

=FILTER(
   Preise[Preis] ;
     ([@Artikel]=Preise[Artikel])*
     ([@Datum]>=Preise[Von])*
     ([@Datum]<=Preise[Bis]) ;
       0
)

=FILTER mir Daten (
liefere mir einen Wert aus den Preisen ,
für den folgendes gilt:
die Artikelnummer muss in der Spalte Artikel gleich sein UND (*)
das Datum muss größer oder gleich der Spalte Von sein UND (*)
das Datum muss kleiner oder gleich der Spalte Bis sein.
falls keine Daten gefunden werden, gib eine 0 zurück.
)

Zugeordnete Preise mit Hilfe der Filter-Funktion.

Das war es schon. Das Geheimnis liegt hierbei darin, dass unsere Filter-Funktion immer nur genau einen Wert liefern kann, denn einerseits geben wir ihr ja nur die Preise und andererseits kann immer nur ein Wert die drei Suchkriterien erfüllen.

Ein bisschen „verdrehen“ wir hier die Filter-Funktion also schon, denn sie kann natürlich noch viel mehr.

Share this Post