Om du vill att en lagersökning i Excel ska välja den mest specifika matchande regeln identifierar du först de rader som kan komma i fråga och räknar sedan deras angivna fält. Returnera kvantiteten från den enda raden med högst poäng och markera eventuella likalägen. Den här genomgången visar hur du får fram resultatet för syntetiska konstmaterial i Excel för Mac.

Vilka versioner av Excel för Mac stöder den här metoden?

Formeln nedan använder LET, XLOOKUP och beräkningar med dynamiska matriser. Microsoft dokumenterar stöd för de här funktionerna i Excel för Microsoft 365 för Mac, Excel 2024 för Mac och Excel 2021 för Mac. Funktionsguiden för Excel 2021 för Mac tar uttryckligen upp LET, XLOOKUP och dynamiska matriser. Nyheter i Excel 2021 för Mac, LET-funktionen.

Excel 2016 och Excel 2019 stöder inte XLOOKUP, enligt Microsofts uttryckliga kompatibilitetsinformation. Använd någon av de Mac-versioner som stöds för just den här formeln. Att ändra hur du anger formeln tillför inte en funktion som saknas. Den valfria granskningsformeln längre fram i artikeln använder FILTER, som också dokumenteras för de tre Mac-versionerna ovan. XLOOKUP-funktionen, FILTER-funktionen.

Vad ska ”mest specifik” betyda i ditt lager?

I det här exemplet söker du efter en kvantitet som tilldelats en konstresurs utifrån tre attribut: resursens beteckning, ytbehandling och storlek. Ett tomt regelfält innebär att regeln godtar vilket värde som helst för det attributet. Ett ifyllt fält måste matcha motsvarande indatavärde. Bland reglerna som kan komma i fråga vinner regeln med flest ifyllda fält.

Det här är en uttrycklig arbetsbokspolicy som tagits fram för exemplet. Excel härleder inte policyn från dina beskrivningar. Här har alla attribut samma vikt: en regel som anger resurs och ytbehandling har samma specificitet som en regel som anger resurs och storlek. Om båda matchar och delar högsta poäng rapporterar formeln ett likaläge.

En vanlig XLOOKUP kan inte avgöra den här prioriteringen på egen hand. Microsoft dokumenterar att den som standard returnerar det första matchande objektet. Om du vänder på sökningen ändrar du vilken ände den börjar från, men du rangordnar inte reglerna efter specificitet. Formeln beräknar därför den vinnande raden innan den hämtar kvantiteten med XLOOKUP. XLOOKUP-funktionen.

Ställ in reglerna för syntetiska konstresurser

Ange dessa rubriker och värden i A1:D6 på ett tomt kalkylblad. Tomma celler i tabellen nedan måste förbli tomma i Excel. Alla beteckningar och kvantiteter är exempelvärden.

Kalkylbladsrad A: Resurs B: Ytbehandling C: Storlek D: Kvantitet
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

Regeln på rad 2 är ett avsiktligt standardvärde: den anger inga attribut och har specificitetsvärdet noll. Rad 3 anger ett attribut, rad 4 anger två och rad 5 anger tre. Kvantiteterna är alternativa tilldelade värden, så sökningen returnerar ett värde. Den summerar inte standardvärdet och undantagsvärdena.

Ange rubrikerna Asset, Finish, Size och Result i H1:K1. Ange sedan Tile i H2, Matte i I2 och Small i J2. Se till att alla tre indatattributen är ifyllda. I den här utformningen fungerar tomma celler som obegränsade villkor endast i regelområdet. Ofullständiga indata ger ett meddelande.

Använd numeriska kvantiteter, inklusive numeriskt noll när det är lämpligt. Begränsa områdena till de faktiska regelraderna. Om du utökar dem till oanvända tomma rader tillkommer fler regler där alla fält är tomma enligt den här matchningspolicyn.

Ange formeln som väljer den vinnande regeln

Markera K2, ange hela formeln nedan och tryck på Return. Den använder regelområdet och indatacellerna som definierats ovan. Beräkningar med dynamiska matriser utförs i formeln och slutresultatet visas i en cell. I Microsofts dokumentation står att formler med dynamiska matriser anges som vanligt, utan äldre tangentkombinationer för matrisinmatning. Formler med dynamiska matriser och hur spillområden fungerar.

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

Med exempelindata ska K2 returnera 7. Fyra regler kan komma i fråga, men bara regeln på rad 5 anger alla tre attributen.

Så fungerar behörighet och specificitet

LET tilldelar lokala namn till områdena och mellanresultaten, vilket gör urvalsprocessen enklare att granska. Microsoft dokumenterar att de här namnen gäller inom formelns omfång. LET-funktionen.

För varje attribut kontrollerar behörighetsuttrycket om regelfältet är tomt eller matchar indatavärdet. Addition kombinerar de här två möjligheterna och jämförelsen omvandlar resultatet till ett logiskt test. Multiplikation av de tre attributtesterna kräver att alla villkor uppfylls. I sina exempel med FILTER använder Microsoft samma multiplikationsmönster för att kombinera matriskriterier med OCH-logik. FILTER-funktionen.

specificity räknar ifyllda regelfält. De dubbla unära operatorerna omvandlar varje test av om ett fält är tomt till ett tal och ger poäng från noll till tre. IF tilldelar rader som inte kan komma i fråga poängen minus ett i rankningsberäkningen. Därefter hittar MAX den högsta poängen bland de behöriga raderna. Minus ett gör att en rad som inte kan komma i fråga hamnar under även det obegränsade standardvärdets poäng noll. IF-funktionen, MAX-funktionen.

winner markerar endast behöriga rader med den högsta poängen. Formeln kontrollerar om det saknas en matchning eller finns flera vinnare innan den returnerar en kvantitet. Den slutliga numeriska kontrollen avvisar avsiktligt en tom kvantitet eller ett tal som lagrats som text. Microsoft dokumenterar att ISNUMBER inte omvandlar texttal till numeriska värden. Numeriskt noll är fortfarande ett giltigt resultat. IS-funktioner.

Kontrollera resultaten innan du utökar kalkylbladet

Ändra H2:J2 till varje kombination nedan. Det här är förväntade resultat som beräknats utifrån exempelreglerna, inte uppmätta lagerobservationer.

Resurs Ytbehandling Storlek Förväntat resultat Villkor för vinnande regel
Tile Matte Small 7 Tre angivna fält
Tile Matte Large 12 Resurs och ytbehandling
Tile Gloss Small 18 Endast resurs
Disc Gloss Large 10 Resurs och storlek
Arc Matte Small 30 Obegränsat standardvärde

Ändra sedan tillfälligt rad 6 till resursen Tile, ytbehandlingen Matte, storleken Small och kvantiteten 9. När du återställer de ursprungliga indata bör resultatet bli Tie: review rules: nu anger två regler alla tre matchande attributen. Även om kvantiteterna vore identiska skulle resultatet fortfarande bli ett likaläge, eftersom regelvalet är tvetydigt. Återställ den ursprungliga rad 6 efteråt.

Om du vill testa grenen för utebliven matchning ändrar du tillfälligt de fyra regelområdena i formeln så att de börjar på rad 3 och utesluter standardvärdet. Indatan Arc, Matte, Small ska då returnera No matching rule. Återställ områdena så att de börjar på rad 2 när du är klar. Om du vill testa kvantitetskontrollen tömmer du D5 medan du använder de ursprungliga indata. Förvänta dig Check rule quantity och återställ sedan 7.

Granska överlappande regler och lös likalägen

Om du vill se alla regler som kan komma i fråga för den aktuella indatan anger du den här separata formeln i M2. Se först till att M2:P6 är tomt. Resultatet visar matchande regelrader i sin ursprungliga ordning. Det rangordnar dem inte.

=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 returnerar de rader som valts av den booleska inklusionsmatrisen, och det sista argumentet anger ett meddelande om ingen rad uppfyller villkoren. Tomma källceller kan visas som nollor i granskningsresultatet. Fortsätt att behandla de tomma cellerna i den ursprungliga regelraden som obegränsade villkor. FILTER-funktionen.

Granskningsformeln spiller över i intilliggande celler. Placera den utanför en Excel-tabell och håll resultatområdet tomt. Microsoft dokumenterar att formler med spillande matriser inte stöds i tabeller och att upptagna resultatceller kan orsaka #SPILL!. Formler med dynamiska matriser och hur spillområden fungerar.

Lös ett likaläge genom att rätta till en dubblett, begränsa en överlappande regel eller definiera ytterligare en prioriteringspolicy. Regler för resurs plus ytbehandling och resurs plus storlek får till exempel båda poängen två här. Arbetsboken kan inte avgöra vilket attribut som väger tyngre om du inte kodar den prioriteringen. Radernas ordning ska inte avgöra resultatet enligt policyn i den här artikeln.

Utöka sökningen utan att ändra dess innebörd

Om du vill söka efter en annan resurs anger du dess tre attribut i H3:J3 och kopierar K2 till K3. Indatareferenserna flyttas med formeln, medan dollartecknen håller regelområdena fasta. När du lägger till regler efter rad 6 utökar du alla fyra områdena i huvudformeln och båda dimensionerna i granskningsformeln på samma sätt.

Ha varje attribut i en egen kolumn och använd konsekventa beteckningar utan oavsiktliga blanksteg. Formeln jämför hela fältvärden; den söker inte efter delar av texten i en sammanslagen resursbeskrivning. Kvantitetskontrollen säkerställer att det valda värdet är numeriskt. Tillämpa eventuella ytterligare lagerkrav, till exempel att kvantiteter ska vara positiva heltal, på källdata.

Resultatet blir en sökning med en tydlig beslutsordning: kräv fullständiga indata, identifiera behöriga regler, hitta högsta specificitet, avvisa likalägen och returnera den valda numeriska kvantiteten. Då kan generella standardvärden och detaljerade undantag användas tillsammans, samtidigt som oavgjorda val förblir synliga.

Källor