Per fare in modo che una ricerca nell’inventario di Excel scelga la regola corrispondente più specifica, individua prima le righe idonee, poi conta i campi specificati. Restituisci la quantità della riga con il punteggio più alto, se è unica, e segnala i pareggi. Questa guida mostra come ottenere questo risultato per risorse artistiche sintetiche in Excel per Mac.

Quali versioni di Excel per Mac supportano questo approccio?

La formula qui sotto usa LET, XLOOKUP e calcoli con matrici dinamiche. Microsoft documenta queste funzionalità per Excel per Microsoft 365 per Mac, Excel 2024 per Mac ed Excel 2021 per Mac. La guida alle funzionalità di Excel 2021 per Mac include esplicitamente LET, XLOOKUP e le matrici dinamiche. Novità di Excel 2021 per Mac, Funzione LET.

Secondo l’esplicita nota di compatibilità di Microsoft, Excel 2016 ed Excel 2019 non supportano XLOOKUP. Per usare questa formula esatta, scegli una delle edizioni per Mac supportate: modificare il modo in cui inserisci la formula non aggiunge una funzione mancante. La formula di controllo facoltativa più avanti in questo articolo usa FILTER, documentata anch’essa per le tre edizioni per Mac supportate sopra. Funzione XLOOKUP, Funzione FILTER.

Cosa significa “più specifica” nel tuo inventario?

In questo esempio, cerchi una quantità assegnata a una risorsa artistica usando tre attributi: etichetta della risorsa, finitura e dimensione. Un campo vuoto nella regola significa che la regola accetta qualsiasi valore per quell’attributo. Un campo compilato deve corrispondere all’input pertinente. Tra le regole idonee, vince quella con il maggior numero di campi compilati.

Questa è una regola esplicita del foglio di lavoro, definita per questo esempio. Excel non la deduce dalle tue descrizioni. Qui tutti gli attributi hanno lo stesso peso: una regola che specifica risorsa e finitura ha la stessa specificità di una che specifica risorsa e dimensione. Se entrambe corrispondono e hanno il punteggio più alto, la formula segnala un pareggio.

Un semplice XLOOKUP non può stabilire questa precedenza da solo. Microsoft documenta che, per impostazione predefinita, restituisce il primo elemento corrispondente. Invertire la ricerca cambia da quale estremità inizia, ma non ordina le regole in base alla specificità. La formula calcola quindi la riga vincente prima di chiedere a XLOOKUP di restituirne la quantità. Funzione XLOOKUP.

Imposta le regole per le risorse artistiche sintetiche

In un foglio di lavoro vuoto, inserisci queste intestazioni e questi valori nell’intervallo A1:D6. In Excel, le celle vuote della tabella qui sotto devono rimanere vuote. Tutte le etichette e le quantità sono puramente illustrative.

Riga del foglio A: Risorsa B: Finitura C: Dimensione D: Quantità
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

La regola della riga 2 è un valore predefinito intenzionale: non specifica attributi e ha specificità zero. La riga 3 specifica un attributo, la riga 4 ne specifica due e la riga 5 ne specifica tre. Queste quantità sono valori assegnati alternativi, quindi la ricerca restituisce un solo valore. Non somma la quantità predefinita a quella delle eccezioni.

Inserisci le intestazioni Asset, Finish, Size e Result in H1:K1. Poi inserisci Tile in H2, Matte in I2 e Small in J2. Compila tutti e tre gli attributi di input. In questo schema, i campi vuoti fungono da condizioni senza restrizioni solo nella tabella delle regole; se l’input è incompleto, viene visualizzato un messaggio.

Usa quantità numeriche, incluso lo zero numerico quando appropriato. Limita gli intervalli alle righe delle regole effettive. Estenderli alle righe vuote inutilizzate aggiungerebbe altre regole interamente vuote secondo questa logica di corrispondenza.

Inserisci la formula che seleziona la regola vincente

Seleziona K2, inserisci questa formula completa e premi Return. Usa la tabella delle regole e le celle di input definite sopra. I calcoli con matrici dinamiche vengono eseguiti all’interno della formula; il risultato finale occupa una sola cella. La documentazione Microsoft spiega che le formule con matrici dinamiche si inseriscono normalmente, senza i tasti di scelta rapida usati per le matrici nelle versioni precedenti. Formule con matrici dinamiche e comportamento delle matrici espanse.

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

Con gli input di esempio, K2 dovrebbe restituire 7. Quattro regole sono idonee, ma solo quella della riga 5 specifica tutti e tre gli attributi.

Come funzionano l’idoneità e la specificità

LET assegna nomi locali agli intervalli e ai risultati intermedi, rendendo più facile esaminare il processo di selezione. Microsoft documenta che questi nomi sono validi nell’ambito della formula. Funzione LET.

Per ogni attributo, l’espressione di idoneità verifica se la cella della regola è vuota oppure corrisponde all’input. L’addizione combina queste due possibilità e il confronto converte il risultato in un test logico. Moltiplicare i tre test degli attributi richiede che tutte le condizioni siano soddisfatte. Negli esempi di FILTER, Microsoft usa lo stesso schema di moltiplicazione per combinare criteri di matrice con la logica AND. Funzione FILTER.

specificity conta i campi compilati della regola. I due operatori unari di negazione convertono ogni test di non vuoto in un numero, producendo punteggi da zero a tre. IF assegna alle righe non idonee un punteggio di meno uno per il calcolo della classifica; MAX trova quindi il punteggio idoneo più alto. Meno uno mantiene ogni riga non idonea al di sotto persino del punteggio zero della regola predefinita senza restrizioni. Funzione IF, Funzione MAX.

winner contrassegna solo le righe idonee con quel punteggio massimo. Prima di restituire una quantità, la formula verifica che non ci siano corrispondenze o che non ci siano più vincitori. Il controllo numerico finale esclude intenzionalmente una quantità vuota o un numero memorizzato come testo. Microsoft documenta che ISNUMBER non converte i numeri in formato testo in valori numerici. Lo zero numerico resta un risultato valido. Funzioni IS.

Controlla i risultati prima di estendere il foglio di lavoro

Modifica H2:J2 impostando ciascuna delle combinazioni seguenti. Sono risultati attesi in base alle regole di esempio, non osservazioni effettive dell’inventario.

Risorsa Finitura Dimensione Risultato atteso Condizione vincente
Tile Matte Small 7 Tre campi specificati
Tile Matte Large 12 Risorsa e finitura
Tile Gloss Small 18 Solo risorsa
Disc Gloss Large 10 Risorsa e dimensione
Arc Matte Small 30 Valore predefinito senza restrizioni

Poi modifica temporaneamente la riga 6 impostando la risorsa su Tile, la finitura su Matte, la dimensione su Small e la quantità su 9. Una volta ripristinati gli input originali, il risultato dovrebbe diventare Pareggio: controlla le regole: ora due regole specificano tutti e tre gli attributi corrispondenti. Si verifica comunque un pareggio anche se le quantità sono identiche, perché la selezione della regola è ambigua. Al termine ripristina la riga 6 originale.

Per provare il caso senza corrispondenze, modifica temporaneamente i quattro intervalli delle regole nella formula, in modo che inizino dalla riga 3 ed escludano il valore predefinito. L’input Arc, Matte, Small dovrebbe allora restituire Nessuna regola corrispondente. Al termine ripristina gli intervalli affinché inizino dalla riga 2. Per provare il controllo della quantità, cancella D5 usando gli input originali: il risultato atteso è Controlla la quantità della regola; poi ripristina 7.

Esamina le regole sovrapposte e risolvi i pareggi

Per visualizzare tutte le regole idonee per l’input corrente, inserisci questa formula separata in M2. Prima assicurati che M2:P6 sia vuoto. L’output mostra le righe delle regole corrispondenti nell’ordine originale; non le classifica.

=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 restituisce le righe selezionate dalla matrice booleana di inclusione e il suo ultimo argomento fornisce un messaggio se nessuna è idonea. In questo risultato di controllo, le celle vuote di origine possono essere visualizzate come zeri; continua a considerare le celle vuote nella tabella originale delle regole come condizioni senza restrizioni. Funzione FILTER.

La formula di controllo si espande nelle celle adiacenti. Inseriscila fuori da una tabella Excel e lascia libera l’area di output. Microsoft documenta che le formule con matrici espanse non sono supportate all’interno delle tabelle e che le celle occupate nell’area di output possono causare #SPILL!. Formule con matrici dinamiche e comportamento delle matrici espanse.

Per risolvere un pareggio, correggi un duplicato, restringi una regola sovrapposta oppure definisci un’ulteriore regola di priorità. In questo esempio, le regole che combinano risorsa e finitura e quelle che combinano risorsa e dimensione hanno entrambe punteggio due. Il foglio di lavoro non può decidere quale attributo conta di più, a meno che tu non codifichi questa preferenza. Secondo la regola usata in questo articolo, spostare le righe non dovrebbe determinare il risultato.

Estendi la ricerca senza modificarne la logica

Per un’altra risorsa, inserisci i suoi tre attributi in H3:J3 e copia K2 in K3. I riferimenti agli input si spostano insieme alla formula, mentre i simboli del dollaro mantengono fissi gli intervalli delle regole. Quando aggiungi regole oltre la riga 6, estendi in modo coerente tutti e quattro gli intervalli della formula principale e entrambe le dimensioni della formula di controllo.

Mantieni ogni attributo nella propria colonna e usa etichette coerenti, senza spazi accidentali. Questa formula confronta i valori completi dei campi; non cerca frammenti all’interno di una descrizione combinata della risorsa. Il controllo della quantità verifica che il valore selezionato sia numerico; applica quindi ai dati di origine eventuali requisiti aggiuntivi dell’inventario, come quantità intere non negative.

La ricerca risultante segue una sequenza decisionale chiara: richiede un input completo, individua le regole idonee, trova la specificità più alta, rifiuta i pareggi e restituisce la quantità numerica selezionata. In questo modo, valori predefiniti generali ed eccezioni dettagliate possono essere usati insieme, lasciando visibili le scelte ancora irrisolte.

Fonti