Stel een Excel-inventariszoekopdracht zo in dat deze de meest specifieke overeenkomende regel kiest: bepaal eerst welke rijen in aanmerking komen en tel daarna de ingevulde velden. Geef de hoeveelheid uit de enige rij met de hoogste score terug en markeer gelijke scores. In deze handleiding bouw je dit resultaat voor synthetische kunstassets in Excel voor Mac.

Welke Excel voor Mac-versies ondersteunen deze aanpak?

De onderstaande formule gebruikt LET, XLOOKUP en dynamische matrixberekeningen. Microsoft documenteert deze mogelijkheden voor Excel voor Microsoft 365 voor Mac, Excel 2024 voor Mac en Excel 2021 voor Mac. In de functiegids voor Excel 2021 voor Mac worden LET, XLOOKUP en dynamische matrices expliciet genoemd. Wat is er nieuw in Excel 2021 voor Mac, LET-functie.

Excel 2016 en Excel 2019 ondersteunen XLOOKUP niet, volgens Microsofts expliciete compatibiliteitsopmerking. Gebruik een van de ondersteunde Mac-edities voor deze exacte formule; een andere manier om de formule in te voeren voegt geen ontbrekende functie toe. De optionele inspectieformule verderop in dit artikel gebruikt FILTER, dat ook is gedocumenteerd voor de drie hierboven genoemde ondersteunde Mac-edities. XLOOKUP-functie, FILTER-functie.

Wat betekent ‘meest specifiek’ in je inventaris?

In dit voorbeeld zoek je een hoeveelheid op die aan een kunstasset is toegewezen, aan de hand van drie kenmerken: assetlabel, afwerking en formaat. Een leeg regelveld betekent dat de regel elke waarde voor dat kenmerk accepteert. Een ingevuld veld moet gelijk zijn aan de bijbehorende invoer. Van de regels die in aanmerking komen, wint de regel met de meeste ingevulde velden.

Dit is een expliciet werkmapbeleid dat voor dit voorbeeld is opgesteld. Excel leidt dit beleid niet af uit je beschrijvingen. Hier wegen alle kenmerken even zwaar: een regel die asset en afwerking opgeeft, heeft dezelfde specificiteit als een regel die asset en formaat opgeeft. Als beide overeenkomen en dezelfde hoogste score hebben, meldt de formule een gelijke score.

Een gewone XLOOKUP kan deze voorrang niet zelfstandig bepalen. Microsoft documenteert dat deze standaard het eerste overeenkomende item retourneert. Door de zoekrichting om te keren, verander je aan welk uiteinde de zoekopdracht begint; regels worden er niet op specificiteit mee gerangschikt. De formule berekent daarom eerst de winnende rij en vraagt daarna met XLOOKUP de hoeveelheid op. XLOOKUP-functie.

Stel de regels voor synthetische kunstassets in

Voer deze kopteksten en waarden in een leeg werkblad in, in A1:D6. Lege cellen in de onderstaande tabel moeten in Excel leeg blijven. Alle labels en hoeveelheden zijn ter illustratie.

Werkbladrij A: Asset B: Afwerking C: Formaat D: Hoeveelheid
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

De regel in rij 2 is een opzettelijke standaardregel: er zijn geen kenmerken opgegeven en de specificiteit is nul. In rij 3 is één kenmerk opgegeven, in rij 4 twee en in rij 5 drie. Deze hoeveelheden zijn alternatieve toegewezen hoeveelheden, dus de zoekopdracht retourneert één waarde. De standaardhoeveelheid en de hoeveelheden van uitzonderingen worden niet bij elkaar opgeteld.

Voer de kopteksten Asset, Finish, Size en Result in H1:K1 in. Voer vervolgens Tile in H2, Matte in I2 en Small in J2. Vul alle drie invoerkenmerken in. In dit ontwerp gelden lege velden alleen in de regeltabel als onbeperkte voorwaarden; bij onvolledige invoer wordt een melding weergegeven.

Gebruik numerieke hoeveelheden, inclusief numerieke nul wanneer dat van toepassing is. Beperk de bereiken tot de daadwerkelijke regels. Als je ze uitbreidt tot ongebruikte lege rijen, ontstaan er volgens dit overeenkomingsbeleid extra regels waarin alle velden leeg zijn.

Voer de formule in die de winnende regel selecteert

Selecteer K2, voer deze volledige formule in en druk op Return. De formule gebruikt de hierboven gedefinieerde regeltabel en invoercellen. Dynamische matrixberekeningen worden binnen de formule uitgevoerd; het eindresultaat verschijnt in één cel. Volgens Microsofts documentatie voer je dynamische matrixformules op de gebruikelijke manier in, zonder de oude toetsencombinatie voor matrixformules. Dynamische matrixformules en het gedrag van overlopende matrices.

=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"
        )
      )
    )
  )
)

Met de voorbeeldinvoer hoort K2 7 te retourneren. Vier regels komen in aanmerking, maar alleen de regel in rij 5 bevat alle drie kenmerken.

Hoe geschiktheid en specificiteit werken

LET geeft lokale namen aan de bereiken en tussenresultaten, zodat het selectieproces gemakkelijker te controleren is. Microsoft documenteert dat deze namen alleen binnen het bereik van de formule bestaan. LET-functie.

Voor elk kenmerk controleert de geschiktheidsuitdrukking of de regelcel leeg is of overeenkomt met de invoer. Optellen combineert die twee mogelijkheden en de vergelijking zet het resultaat om in een logische test. Door de drie kenmerkentests met elkaar te vermenigvuldigen, moeten alle voorwaarden kloppen. Microsoft gebruikt hetzelfde vermenigvuldigingspatroon om matrixcriteria met EN-logica te combineren in voorbeelden van FILTER. FILTER-functie.

specificity telt de ingevulde regelvelden. De dubbele unaire operatoren zetten elke test op niet-leeg om in een getal, wat scores van nul tot en met drie oplevert. IF geeft niet-toelaatbare rijen voor de rangschikking een score van min één; MAX vindt vervolgens de hoogste score van de rijen die in aanmerking komen. Min één houdt een niet-toelaatbare rij onder zelfs de score nul van de onbeperkte standaardregel. IF-functie, MAX-functie.

winner markeert alleen de rijen die in aanmerking komen en die hoogste score hebben. De formule controleert op geen overeenkomst en meerdere winnaars voordat deze een hoeveelheid retourneert. De laatste numerieke controle wijst bewust een lege hoeveelheid of een als tekst opgeslagen getal af. Microsoft documenteert dat ISNUMBER tekstgetallen niet omzet in numerieke waarden. Een numerieke nul blijft een geldig resultaat. IS-functies.

Controleer de resultaten voordat je het werkblad uitbreidt

Wijzig H2:J2 in elk van de onderstaande combinaties. Dit zijn verwachte resultaten op basis van de voorbeeldregels, geen gemeten inventarisgegevens.

Asset Afwerking Formaat Verwacht resultaat Winnende voorwaarde
Tile Matte Small 7 Drie ingevulde velden
Tile Matte Large 12 Asset en afwerking
Tile Gloss Small 18 Alleen asset
Disc Gloss Large 10 Asset en formaat
Arc Matte Small 30 Onbeperkte standaardregel

Wijzig vervolgens tijdelijk in rij 6 de asset in Tile, de afwerking in Matte, het formaat in Small en de hoeveelheid in 9. Nadat je de oorspronkelijke invoer hebt hersteld, hoort het resultaat Tie: review rules te worden: twee regels bevatten nu alle drie overeenkomende kenmerken. Zelfs bij identieke hoeveelheden blijft er een gelijke score, omdat de regelkeuze dubbelzinnig is. Herstel daarna de oorspronkelijke waarden van rij 6.

Om de tak voor geen overeenkomst te testen, wijzig je tijdelijk de vier regelbereiken in de formule zodat ze bij rij 3 beginnen en de standaardregel uitsluiten. De invoer Arc, Matte, Small hoort dan No matching rule te retourneren. Stel de bereiken daarna weer in op rij 2. Om de hoeveelheidscontrole te testen, wis je D5 met de oorspronkelijke invoer; verwacht Check rule quantity en herstel daarna 7.

Bekijk overlappende regels en los gelijke scores op

Voer deze aparte formule in M2 in om alle regels te bekijken die voor de huidige invoer in aanmerking komen. Laat M2:P6 vooraf leeg. De uitvoer toont de overeenkomende regelrijen in hun oorspronkelijke volgorde; de regels worden niet gerangschikt.

=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 retourneert de rijen die door de Booleaanse opnamematrix zijn geselecteerd. Het laatste argument geeft een melding als geen enkele rij in aanmerking komt. Lege broncellen kunnen in deze inspectie-uitvoer als nullen worden weergegeven; blijf de lege cellen in de oorspronkelijke regeltabel behandelen als onbeperkte voorwaarden. FILTER-functie.

De inspectieformule loopt over in aangrenzende cellen. Plaats deze buiten een Excel-tabel en houd het uitvoergebied leeg. Microsoft documenteert dat overlopende matrixformules niet in tabellen worden ondersteund en dat bezette uitvoercellen #SPILL! kunnen veroorzaken. Dynamische matrixformules en het gedrag van overlopende matrices.

Los een gelijke score op door een duplicaat te corrigeren, een overlappende regel te beperken of een aanvullende voorrangsregel vast te leggen. De regels voor asset plus afwerking en asset plus formaat krijgen hier bijvoorbeeld allebei score twee. De werkmap kan niet bepalen welk kenmerk zwaarder weegt tenzij je die voorkeur vastlegt. Volgens het beleid in dit artikel hoort de volgorde van de rijen het antwoord niet te bepalen.

Breid de zoekopdracht uit zonder de betekenis te veranderen

Voer voor een andere asset de drie kenmerken in H3:J3 in en kopieer K2 naar K3. De invoerverwijzingen verschuiven mee met de formule, terwijl de dollartekens de regelbereiken vastzetten. Als je na rij 6 regels toevoegt, breid je alle vier bereiken in de hoofdformule en beide dimensies van de inspectieformule op dezelfde manier uit.

Houd elk kenmerk in een eigen kolom en gebruik consistente labels zonder onbedoelde spaties. Deze formule vergelijkt volledige veldwaarden; hij zoekt niet naar fragmenten in een gecombineerde assetbeschrijving. De hoeveelheidscontrole stelt vast dat de gekozen waarde numeriek is. Pas daarom eventuele aanvullende inventarisvereisten, zoals niet-negatieve gehele hoeveelheden, toe op de brongegevens.

De resulterende zoekopdracht volgt een duidelijke beslisvolgorde: vereis volledige invoer, bepaal welke regels in aanmerking komen, vind de hoogste specificiteit, wijs gelijke scores af en retourneer de geselecteerde numerieke hoeveelheid. Zo kun je algemene standaardregels en gedetailleerde uitzonderingen samen gebruiken en blijven onopgeloste keuzes zichtbaar.

Bronnen