Damit eine Excel-Inventarsuche die spezifischste passende Regel auswählt, ermitteln Sie zuerst die zulässigen Zeilen und zählen anschließend deren ausgefüllte Felder. Geben Sie die Menge der eindeutig höchstbewerteten Zeile zurück und kennzeichnen Sie Gleichstände. Diese Anleitung zeigt, wie Sie dieses Ergebnis für synthetische Kunstobjekte in Excel für Mac ermitteln.

Welche Excel-Versionen für Mac unterstützen diesen Ansatz?

Die folgende Formel verwendet LET, XLOOKUP und dynamische Arrayberechnungen. Microsoft dokumentiert diese Funktionen für Excel für Microsoft 365 für Mac, Excel 2024 für Mac und Excel 2021 für Mac. Der Funktionsleitfaden für Excel 2021 für Mac führt ausdrücklich LET, XLOOKUP und dynamische Arrays auf. Neuerungen in Excel 2021 für Mac, LET-Funktion.

Excel 2016 und Excel 2019 unterstützen XLOOKUP laut dem ausdrücklichen Kompatibilitätshinweis von Microsoft nicht. Verwenden Sie für genau diese Formel eine der unterstützten Mac-Editionen. Eine andere Eingabeweise stellt keine fehlende Funktion bereit. Die optionale Prüfformel weiter unten in diesem Artikel verwendet FILTER, das ebenfalls für die drei oben genannten unterstützten Mac-Editionen dokumentiert ist. XVERWEIS-Funktion, FILTER-Funktion.

Was bedeutet „am spezifischsten“ in Ihrem Inventar?

In diesem Beispiel suchen Sie anhand von drei Attributen nach einer einem Kunstobjekt zugewiesenen Menge: Objektbezeichnung, Oberfläche und Größe. Ein leeres Regelfeld bedeutet, dass die Regel jeden Wert für dieses Attribut zulässt. Ein ausgefülltes Feld muss mit der entsprechenden Eingabe übereinstimmen. Von den zulässigen Regeln gewinnt die Regel mit den meisten ausgefüllten Feldern.

Dies ist eine ausdrücklich festgelegte Arbeitsmappenrichtlinie für dieses Beispiel. Excel leitet diese Richtlinie nicht aus Ihren Beschreibungen ab. Hier haben alle Attribute das gleiche Gewicht: Eine Regel, die Objekt und Oberfläche angibt, ist genauso spezifisch wie eine Regel, die Objekt und Größe angibt. Wenn beide passen und dieselbe Höchstpunktzahl erreichen, meldet die Formel einen Gleichstand.

Ein einfacher XLOOKUP kann diese Rangfolge nicht eigenständig bestimmen. Microsoft dokumentiert, dass standardmäßig das erste passende Element zurückgegeben wird. Wenn Sie die Suchrichtung umkehren, ändert sich lediglich, an welchem Ende die Suche beginnt; die Regeln werden dadurch nicht nach Spezifität sortiert. Die Formel berechnet daher zunächst die Gewinnerzeile und fragt anschließend mit XLOOKUP deren Menge ab. XVERWEIS-Funktion.

Richten Sie die synthetischen Regeln für Kunstobjekte ein

Geben Sie auf einem leeren Arbeitsblatt diese Überschriften und Werte in A1:D6 ein. Leere Zellen in der folgenden Tabelle müssen in Excel leer bleiben. Alle Bezeichnungen und Mengen dienen nur der Veranschaulichung.

Arbeitsblattzeile A: Objekt B: Oberfläche C: Größe D: Menge
2 30
3 Fliese 18
4 Fliese Matt 12
5 Fliese Matt Klein 7
6 Scheibe Groß 10

Die Regel in Zeile 2 ist ein bewusst gewählter Standard: Sie gibt keine Attribute an und hat Spezifität null. Zeile 3 gibt ein Attribut an, Zeile 4 zwei und Zeile 5 drei. Diese Mengen sind alternative zugewiesene Mengen, daher gibt die Suche einen einzelnen Wert zurück. Sie addiert nicht die Standardmenge und die Ausnahmemengen.

Geben Sie die Überschriften Objekt, Oberfläche, Größe und Ergebnis in H1:K1 ein. Geben Sie dann Fliese in H2, Matt in I2 und Klein in J2 ein. Füllen Sie alle drei Eingabeattribute aus. Bei diesem Aufbau gelten Leerfelder nur in der Regeltabelle als uneingeschränkte Bedingungen; bei unvollständigen Eingaben wird eine Meldung angezeigt.

Verwenden Sie numerische Mengen, einschließlich der Zahl null, wenn diese zutrifft. Beschränken Sie die Bereiche auf die tatsächlichen Regelzeilen. Würden Sie sie auf ungenutzte leere Zeilen erweitern, entstünden gemäß dieser Abgleichrichtlinie zusätzliche Regeln, bei denen alle Felder leer sind.

Geben Sie die Formel ein, die die Gewinnerregel auswählt

Wählen Sie K2 aus, geben Sie diese vollständige Formel ein und drücken Sie Return. Sie verwendet die oben definierten Regeltabellen- und Eingabezellen. Dynamische Arrayberechnungen werden innerhalb der Formel ausgewertet; das Endergebnis belegt eine Zelle. Laut der Dokumentation von Microsoft werden Formeln mit dynamischen Arrays normal eingegeben, ohne die Tastenkombinationen für die Eingabe älterer Arrayformeln. Formeln mit dynamischen Arrays und Verhalten überlaufender Arrays.

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

Bei den Beispieleingaben sollte K2 den Wert 7 zurückgeben. Vier Regeln sind zulässig, aber nur in Zeile 5 sind alle drei Attribute angegeben.

So funktionieren Zulässigkeit und Spezifität

LET weist Bereichen und Zwischenergebnissen lokale Namen zu, damit sich der Auswahlprozess leichter prüfen lässt. Microsoft dokumentiert, dass diese Namen innerhalb des Gültigkeitsbereichs der Formel existieren. LET-Funktion.

Für jedes Attribut prüft der Zulässigkeitsausdruck, ob die Regelzelle leer ist oder mit der Eingabe übereinstimmt. Durch Addition werden diese beiden Möglichkeiten verknüpft; der Vergleich wandelt das Ergebnis in eine logische Prüfung um. Durch die Multiplikation der drei Attributprüfungen müssen alle Bedingungen erfüllt sein. Microsoft verwendet in den Beispielen zu FILTER dasselbe Multiplikationsmuster, um Arraykriterien mit einer UND-Logik zu verknüpfen. FILTER-Funktion.

specificity zählt die ausgefüllten Regelfelder. Die doppelten unären Operatoren wandeln jede Prüfung auf Nichtleerheit in eine Zahl um und erzeugen so Werte von null bis drei. IF weist unzulässigen Zeilen für die Rangfolgeberechnung den Wert minus eins zu; MAX ermittelt anschließend die höchste Punktzahl der zulässigen Zeilen. Minus eins hält unzulässige Zeilen unter dem Wert null des uneingeschränkten Standards. WENN-Funktion, MAX-Funktion.

winner kennzeichnet nur zulässige Zeilen mit dieser Höchstpunktzahl. Die Formel prüft, ob es keinen Treffer oder mehrere Gewinner gibt, bevor sie eine Menge zurückgibt. Die abschließende Zahlenprüfung weist bewusst eine leere Menge oder eine als Text gespeicherte Zahl zurück. Microsoft dokumentiert, dass ISNUMBER Textzahlen nicht in Zahlenwerte umwandelt. Eine numerische Null bleibt ein gültiges Ergebnis. IS-Funktionen.

Prüfen Sie die Ergebnisse, bevor Sie das Arbeitsblatt erweitern

Ändern Sie H2:J2 nacheinander in die unten aufgeführten Kombinationen. Dies sind aus den Beispielregeln abgeleitete erwartete Ergebnisse, keine gemessenen Inventarwerte.

Objekt Oberfläche Größe Erwartetes Ergebnis Bedingung für den Gewinn
Fliese Matt Klein 7 Drei ausgefüllte Felder
Fliese Matt Groß 12 Objekt und Oberfläche
Fliese Glänzend Klein 18 Nur das Objekt
Scheibe Glänzend Groß 10 Objekt und Größe
Bogen Matt Klein 30 Uneingeschränkter Standard

Ändern Sie als Nächstes vorübergehend in Zeile 6 das Objekt zu Fliese, die Oberfläche zu Matt, die Größe zu Klein und die Menge zu 9. Wenn Sie die ursprünglichen Eingaben wiederherstellen, sollte das Ergebnis nun Gleichstand: Regeln überprüfen lauten: Zwei Regeln geben jetzt alle drei passenden Attribute an. Selbst identische Mengen würden weiterhin zu einem Gleichstand führen, weil die Regelauswahl mehrdeutig ist. Stellen Sie danach die ursprüngliche Zeile 6 wieder her.

Um den Zweig für „kein Treffer“ zu prüfen, ändern Sie vorübergehend die vier Regelbereiche in der Formel so, dass sie erst ab Zeile 3 beginnen und der Standard ausgeschlossen wird. Die Eingabe Bogen, Matt, Klein sollte dann Keine passende Regel zurückgeben. Stellen Sie anschließend die Regelbereiche so wieder her, dass sie ab Zeile 2 beginnen. Um die Mengenprüfung zu testen, leeren Sie bei den ursprünglichen Eingaben D5; erwarten Sie Menge der Regel prüfen und stellen Sie danach 7 wieder her.

Überlappende Regeln prüfen und Gleichstände auflösen

Um alle für die aktuellen Eingaben zulässigen Regeln anzuzeigen, geben Sie diese separate Formel in M2 ein. Lassen Sie zuvor M2:P6 leer. Die Ausgabe zeigt die passenden Regelzeilen in ihrer ursprünglichen Reihenfolge; sie sortiert sie nicht nach Rang.

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER gibt die von seinem booleschen Einschlussarray ausgewählten Zeilen zurück; das letzte Argument liefert eine Meldung, wenn keine Zeilen infrage kommen. Leere Quellzellen können in dieser Prüfausgabe als Nullen angezeigt werden. Behandeln Sie die leeren Zellen in der ursprünglichen Regeltabelle weiterhin als uneingeschränkte Bedingungen. FILTER-Funktion.

Die Prüfformel läuft in benachbarte Zellen über. Platzieren Sie sie außerhalb einer Excel-Tabelle und halten Sie den Ausgabebereich frei. Laut der Dokumentation von Microsoft werden Formeln mit überlaufenden Arrays in Tabellen nicht unterstützt; belegte Ausgabefelder können #SPILL! verursachen. Formeln mit dynamischen Arrays und Verhalten überlaufender Arrays.

Lösen Sie einen Gleichstand, indem Sie ein Duplikat korrigieren, eine überlappende Regel einschränken oder eine zusätzliche Rangfolgeregel festlegen. Beispielsweise erzielen Regeln mit Objekt plus Oberfläche und Objekt plus Größe hier beide zwei Punkte. Die Arbeitsmappe kann nicht entscheiden, welches Attribut wichtiger ist, solange Sie diese Präferenz nicht festlegen. Eine andere Zeilenreihenfolge sollte die Antwort gemäß der in diesem Artikel verwendeten Richtlinie nicht ändern.

Erweitern Sie die Suche, ohne ihre Bedeutung zu ändern

Geben Sie für ein weiteres Objekt seine drei Attribute in H3:J3 ein und kopieren Sie K2 nach K3. Die Eingabebezüge verschieben sich mit der Formel, während die Dollarzeichen die Regelbereiche fixieren. Wenn Sie nach Zeile 6 weitere Regeln hinzufügen, erweitern Sie alle vier Bereiche in der Hauptformel sowie beide Dimensionen der Prüfformel auf einheitliche Weise.

Führen Sie jedes Attribut in einer eigenen Spalte und verwenden Sie einheitliche Bezeichnungen ohne versehentliche Leerzeichen. Diese Formel vergleicht vollständige Feldwerte; sie sucht nicht nach Teilzeichenfolgen in einer kombinierten Objektbeschreibung. Ihre Mengenprüfung stellt sicher, dass der ausgewählte Wert numerisch ist. Zusätzliche Inventaranforderungen, etwa Mengen als nichtnegative ganze Zahlen, müssen Sie auf die Quelldaten anwenden.

Die Suche folgt damit einer klaren Entscheidungsabfolge: vollständige Eingaben verlangen, zulässige Regeln ermitteln, die höchste Spezifität finden, Gleichstände zurückweisen und die ausgewählte numerische Menge zurückgeben. So lassen sich allgemeine Standards und detaillierte Ausnahmen gemeinsam verwenden, während ungeklärte Entscheidungen sichtbar bleiben.

Quellen