Gebruik FILTER om een exacte categorie grafische assets te isoleren en vervolgens XLOOKUP om het niveau te retourneren bij de grootste hoeveelheidsdrempel die niet hoger is dan uw aantal. Deze handleiding bouwt een volledig voorbeeld voor Excel voor Mac op met fictieve labels en grenstests.

Welke versies van Excel voor Mac ondersteunen deze formule?

De onderstaande formule gebruikt LET, FILTER en XLOOKUP. In de documentatie van Microsoft worden alle drie vermeld voor Excel voor Microsoft 365 voor Mac, Excel 2024 voor Mac en Excel 2021 voor Mac. Microsoft noemt deze functies ook in het overzicht van nieuw in Excel 2021 voor Mac.

Excel 2016 en Excel 2019 kunnen XLOOKUP niet evalueren; Microsoft documenteert deze beperking expliciet. Gebruik voor deze exacte formule een van de hierboven vermelde Mac-edities. Als u een werkmap met deze functie opent in een oudere editie, wordt de functie daardoor niet ondersteund. Zie de XLOOKUP-documentatie van Microsoft.

Als u niet zeker weet welke editie is geïnstalleerd, controleert u de versie en het licentietype in het dialoogvenster Info van de app. Microsoft legt uit hoe u deze gegevens kunt achterhalen in Over Office: welke versie van Office gebruik ik?.

Stel minimumhoeveelheden per categorie in

Stel dat u aantallen bijhoudt van herbruikbare grafische assets en voor elke categorie een beschrijvend inventarisniveau wilt. Hiervoor gelden twee regels: de categorie moet overeenkomen en de hoeveelheid moet minstens aan het minimum van een niveau voldoen. Alle onderstaande labels en hoeveelheden dienen ter illustratie.

Voer deze tabel in A1:C7 in op een leeg werkblad. De kopteksten staan in rij 1; de zes gegevensrijen staan in de rijen 2–7. Voer de minimumhoeveelheden in als getallen en vul elke categorie- en niveaucel in.

Categorie Minimumhoeveelheid Niveauaanduiding
Texture Tile 5 Compact
Texture Tile 20 Expanded
Texture Tile 50 Extensive
Shape Stamp 5 Compact
Shape Stamp 30 Expanded
Shape Stamp 80 Extensive

Voer vervolgens Categorie controleren in E1, Hoeveelheid in F1 en Inventarisniveau in G1 in. Plaats Texture Tile in E2 en het getal 34 in F2. Laat kolom D leeg om de referentiegegevens van de invoer te scheiden.

Behandel de minimumhoeveelheden als inclusieve ondergrenzen. Voor Texture Tile begint Compact bij 5, Expanded bij 20 en Extensive bij 50. Een aantal van 34 hoort daarom bij Expanded. Voor Shape Stamp beginnen dezelfde labels bij andere hoeveelheden: een aantal van 20 hoort nog bij Compact.

Bepaal voordat u de formule schrijft of uw eigen grenzen minimumwaarden of maximumwaarden voorstellen. In dit voorbeeld worden overal minimumwaarden gebruikt. Hoeveelheden onder 5 vallen buiten alle niveaus en het laatste niveau loopt door tot boven de beginhoeveelheid. Dat zijn expliciete keuzes voor dit voorbeeld; pas de referentiegegevens aan als uw beoogde regels hiervan afwijken.

Voer de volledige formule voor categorie en hoeveelheid in

Selecteer G2, plak de volgende volledige formule in de formulebalk en druk op Return. Deze formule gebruikt de exacte werkbladindeling hierboven; benoemde bereiken of hulpkolommen zijn niet nodig.

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

Voor Texture Tile en 34 is het verwachte resultaat Expanded. De formule retourneert één label, hoewel de tussentijdse berekeningen matrices gebruiken. Moderne dynamische matrixformules voert u op de gebruikelijke manier in; Microsoft documenteert dat u deze met Enter bevestigt in Dynamische matrixformules en gedrag van overlopende matrices.

Hoe het categoriefilter de matrices uitgelijnd houdt

De expressie categories=category maakt de insluitingsmatrix met de naam keep. Voor deze voorbeeldinvoer selecteert die de drie rijen voor Texture Tile. Beide aanroepen van FILTER gebruiken dezelfde matrix, waardoor de minimumwaarden 5, 20 en 50 worden gekoppeld aan de overeenkomstige labels Compact, Expanded en Extensive.

Microsoft beschrijft FILTER als een functie die gegevens selecteert met een Booleaanse insluitingsmatrix waarvan de afmetingen overeenkomen met het bronbereik. Als voor beide kolommen dezelfde insluitingsmatrix wordt gebruikt, blijven de waarden bij elkaar horen. Zie de FILTER-functie.

De buitenste LET kent leesbare namen toe aan de bronbereiken, invoerwaarden en insluitingsmatrix. Deze namen bestaan alleen binnen deze formule; u hoeft ze niet afzonderlijk in de werkmap aan te maken. Microsoft beschrijft dit gedrag in de documentatie over de LET-functie.

Hoe de hoeveelheid het niveau bepaalt

Het vijfde argument van XLOOKUP, -1, vraagt om een exacte hoeveelheidsovereenkomst of de eerstvolgende kleinere waarde. Binnen de gefilterde rijen voor Texture Tile selecteert 34 de minimumwaarde 20 en retourneert de formule Expanded. Bij een exacte hoeveelheid van 50 wordt Extensive geselecteerd. Microsoft definieert deze modi in de documentatie over de XLOOKUP-functie.

De formule maakt ook onderscheid tussen een onbekende categorie en een aantal onder het eerste niveau. SUM(--keep) telt de rijen voor de overeenkomende categorie. Als er geen zijn, retourneert de formule Categorie niet gevonden voordat de gefilterde zoekopdracht wordt uitgevoerd. Als de categorie wel bestaat, maar alle minimumwaarden hoger zijn dan het aantal, retourneert de zoekopdracht Onder het eerste niveau.

Test grenzen en scheiding per categorie

Vervang E2 en F2 één voor één door de volgende paren. Deze controles zijn nuttiger dan alleen een waarde uit het midden testen: ze toetsen de eerste drempel, exacte grenzen, verschillen tussen categorieën en ontbrekende categorieën.

Categorie in E2 Hoeveelheid in F2 Verwacht resultaat
Texture Tile 0 Onder het eerste niveau
Texture Tile 5 Compact
Texture Tile 19 Compact
Texture Tile 20 Expanded
Texture Tile 34 Expanded
Texture Tile 50 Extensive
Shape Stamp 20 Compact
Shape Stamp 34 Expanded
Shape Stamp 100 Extensive
Color Swatch 34 Categorie niet gevonden

De twee tests met een aantal van 20 zijn bijzonder verhelderend. Texture Tile moet Expanded retourneren, terwijl Shape Stamp Compact moet retourneren. Als beide Expanded retourneren, controleer dan of de zoekopdracht naar de hoeveelheid de rijen van de gefilterde categorie gebruikt.

Deze tien verwachte uitkomsten zijn gecontroleerd met een kleine Python-berekening die de beschreven regel met minimumdrempels toepast. De Excel-formule zelf is gecontroleerd aan de hand van de gedocumenteerde syntaxis van Microsoft; de formule is niet in Excel uitgevoerd.

Controleer ook de invoermeldingen: bij een lege categorie of een hoeveelheid die als tekst is ingevoerd, moet Voer categorie en numerieke hoeveelheid in verschijnen; bij een negatieve numerieke hoeveelheid moet Gebruik nul of meer verschijnen. Gebruik gehele getallen voor assetaantallen. De formule controleert of hoeveelheden numeriek en niet-negatief zijn, maar dwingt geen gehele getallen af.

Diagnoseer onverwachte resultaten en breid het werkblad uit

Voer deze controles in de aangegeven volgorde uit, zodat een opmaakprobleem niet leidt tot een wijziging van de niveauregels.

  1. Controleer de categorietekst. Vergelijk de invoer met de referentielabels, inclusief spaties. Microsoft noemt onverwachte tekens en verborgen spaties als veelvoorkomende problemen bij zoekopdrachten in de richtlijnen voor het oplossen van zoekproblemen.
  2. Controleer numerieke cellen. De hoeveelheidsinvoer en minimumwaarden moeten getallen bevatten, geen tekst die op een getal lijkt. In dezelfde richtlijnen van Microsoft worden inconsistente gegevenstypen genoemd als probleem bij zoekopdrachten.
  3. Controleer of de bereiken op elkaar aansluiten. De bereiken voor categorie, minimumwaarde en label moeten dezelfde gegevensrijen omvatten. In dit voorbeeld begint elk bereik bij rij 2 en eindigt het bij rij 7.
  4. Controleer op dubbele regels. Wijs aan elk paar van categorie en minimumhoeveelheid één niveauaanduiding toe. Als twee rijen verschillende labels aan dezelfde grens toewijzen, los die onduidelijkheid dan op in de referentiegegevens.
  5. Controleer de grensconventie. Een resultaat dat één niveau te hoog of te laag lijkt, kan erop wijzen dat uw referentiewaarden maximumwaarden zijn, terwijl deze formule minimumwaarden verwacht.

Voer de volgende diagnoseformule in een ongebruikte cel in, zoals I2, om de gegevens visueel te controleren. Met Texture Tile in E2 moet de formule de drie volledige rijen voor Texture Tile weergeven. Dit is een afzonderlijke diagnoseformule, geen vervanging voor G2.

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

Zorg dat er ruimte is zodat het resultaat van de diagnoseformule over drie kolommen en drie rijen kan worden uitgevouwen. Een zelfstandig matrixresultaat loopt over naar aangrenzende cellen; bezette cellen kunnen #SPILL! veroorzaken. Microsoft legt dit gedrag uit in Dynamische matrixformules en gedrag van overlopende matrices.

Wanneer u meer referentierijen toevoegt, breidt u alle drie de bronbereiken in de hoofdformule tegelijk uit. Als de laatste gegevensrij bijvoorbeeld rij 10 wordt, wijzigt u de definities in categories,$A$2:$A$10, minimums,$B$2:$B$10 en labels,$C$2:$C$10. Voer vervolgens de grenstests opnieuw uit voor de toegevoegde categorie.

Als u meerdere invoerrijen wilt verwerken, voert u meer categorie- en hoeveelheidsparen in onder E2:F2 en kopieert u G2 omlaag. De bronbereiken blijven vast omdat hun verwijzingen dollartekens bevatten; de rijnummers van de invoer lopen door. Houd de grenzen per categorie expliciet en gebruik de gekoppelde categorietests om te controleren of elk geretourneerd label de beoogde inventarisregel volgt.

Bronnen