Usa FILTER per isolare una categoria esatta di risorse grafiche, quindi XLOOKUP per restituire il livello corrispondente alla soglia di quantità più alta che non supera il conteggio. Questa guida illustra un esempio completo in Excel per Mac con etichette sintetiche e test dei valori limite.

Quali versioni di Excel per Mac supportano questa formula?

La formula seguente usa LET, FILTER e XLOOKUP. La documentazione Microsoft elenca tutte e tre per Excel per Microsoft 365 per Mac, Excel 2024 per Mac ed Excel 2021 per Mac. Microsoft identifica inoltre queste funzioni nella panoramica di novità di Excel 2021 per Mac.

Excel 2016 ed Excel 2019 non possono valutare XLOOKUP; Microsoft documenta esplicitamente questa limitazione. Per usare questa formula specifica, scegli una delle edizioni per Mac elencate sopra. L'apertura di una cartella di lavoro contenente la funzione in un'edizione precedente non aggiunge il supporto per la funzione. Consulta la documentazione di XLOOKUP di Microsoft.

Se non sai quale edizione è installata, controlla la versione e il tipo di licenza nella finestra Informazioni sull'applicazione. Microsoft spiega come identificare questi dettagli in Informazioni su Office: quale versione di Office sto usando?.

Configurare quantità minime specifiche per categoria

Supponiamo che tu tenga traccia delle quantità di risorse grafiche riutilizzabili e voglia assegnare a ogni categoria un livello di inventario descrittivo. L'attività segue due regole: la categoria deve corrispondere e la quantità deve raggiungere il minimo previsto per un livello. Tutte le etichette e le quantità riportate sotto sono esemplificative.

In un foglio di lavoro vuoto, inserisci questa tabella nell'intervallo A1:C7. Le intestazioni occupano la riga 1; le sei righe di dati occupano le righe da 2 a 7. Inserisci le quantità minime come numeri e compila tutte le celle delle categorie e dei livelli.

Categoria Quantità minima Etichetta del livello
Texture Tile 5 Compatto
Texture Tile 20 Esteso
Texture Tile 50 Ampio
Shape Stamp 5 Compatto
Shape Stamp 30 Esteso
Shape Stamp 80 Ampio

Poi inserisci Categoria da controllare in E1, Quantità in F1 e Livello di inventario in G1. Inserisci Texture Tile in E2 e il numero 34 in F2. Lascia vuota la colonna D per separare i dati di riferimento dai valori di input.

Considera le quantità minime come limiti inferiori inclusivi. Per Texture Tile, Compatto inizia da 5, Esteso da 20 e Ampio da 50. Di conseguenza, un conteggio di 34 corrisponde a Esteso. Per Shape Stamp, le stesse etichette iniziano a quantità diverse: un conteggio di 20 corrisponde ancora a Compatto.

Prima di scrivere la formula, decidi se i tuoi limiti rappresentano minimi o massimi. Questo esempio usa sempre i minimi. Inoltre, lascia fuori da tutti i livelli le quantità inferiori a 5 e consente all'ultimo livello di proseguire oltre la sua quantità iniziale. Sono scelte esplicite per questo esempio; modifica i dati di riferimento se le regole che intendi applicare sono diverse.

Inserire la formula completa per categoria e quantità

Seleziona G2, incolla la formula completa seguente nella barra della formula e premi Return. La formula usa esattamente la struttura del foglio di lavoro descritta sopra; non servono intervalli denominati né colonne di supporto.

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

Per Texture Tile e 34, il risultato previsto è Esteso. La formula restituisce una sola etichetta, anche se i calcoli intermedi usano matrici. Le moderne formule a matrice dinamica si inseriscono normalmente; Microsoft spiega come confermarle con Invio in Formule a matrice dinamica e comportamento di espansione.

In che modo il filtro per categoria mantiene allineate le matrici

L'espressione categories=category crea la matrice di inclusione denominata keep. Per questo valore di input, seleziona le tre righe Texture Tile. Entrambe le chiamate a FILTER usano la stessa matrice, producendo i minimi 5, 20 e 50 insieme alle etichette corrispondenti Compatto, Esteso e Ampio.

Microsoft descrive FILTER come una funzione che seleziona i dati mediante una matrice di inclusione booleana con dimensioni corrispondenti all'intervallo di origine. Usare la stessa matrice di inclusione per entrambe le colonne ne mantiene la corrispondenza. Consulta la funzione FILTER.

La funzione esterna LET assegna nomi leggibili agli intervalli di origine, ai valori di input e alla matrice di inclusione. Questi nomi esistono solo all'interno della formula; non occorre crearli separatamente nella cartella di lavoro. Microsoft descrive questo comportamento nella documentazione della funzione LET.

In che modo la quantità determina il livello

Il quinto argomento di XLOOKUP, -1, richiede una corrispondenza esatta della quantità oppure il valore successivo più piccolo. Tra le righe filtrate di Texture Tile, 34 seleziona il minimo di 20 e restituisce Esteso. Una quantità esatta di 50 seleziona Ampio. Microsoft definisce queste modalità nella documentazione della funzione XLOOKUP.

La formula distingue anche tra una categoria sconosciuta e un conteggio inferiore al primo livello. SUM(--keep) conta le righe della categoria corrispondente. Se non ce n'è nessuna, la formula restituisce Categoria non trovata prima di eseguire la ricerca filtrata. Se la categoria esiste ma tutti i suoi valori minimi superano il conteggio, la ricerca restituisce Sotto il primo livello.

Verificare i limiti e l'isolamento delle categorie

Sostituisci E2 e F2 con le coppie seguenti, una alla volta. Questi controlli sono più utili di una verifica con un solo valore intermedio: mettono alla prova la prima soglia, i limiti esatti, le differenze tra categorie e le categorie mancanti.

Categoria in E2 Quantità in F2 Risultato previsto
Texture Tile 0 Sotto il primo livello
Texture Tile 5 Compatto
Texture Tile 19 Compatto
Texture Tile 20 Esteso
Texture Tile 34 Esteso
Texture Tile 50 Ampio
Shape Stamp 20 Compatto
Shape Stamp 34 Esteso
Shape Stamp 100 Ampio
Color Swatch 34 Categoria non trovata

I due test con conteggio pari a 20 sono particolarmente significativi. Texture Tile deve restituire Esteso, mentre Shape Stamp deve restituire Compatto. Se entrambi restituiscono Esteso, controlla che la ricerca della quantità utilizzi le righe filtrate per categoria.

Questi dieci risultati attesi sono stati verificati con un breve calcolo Python che applica la regola delle soglie minime descritta. La formula di Excel è stata confrontata con la sintassi documentata da Microsoft, ma non è stata eseguita in Excel.

Controlla anche i messaggi relativi ai valori di input: una categoria vuota o una quantità inserita come testo dovrebbe restituire Inserisci una categoria e una quantità numerica; una quantità numerica negativa dovrebbe restituire Usa zero o un numero maggiore. Usa valori interi per i conteggi delle risorse. La formula verifica che le quantità siano numeriche e non negative, ma non impone che siano intere.

Diagnosticare risultati imprevisti ed estendere il foglio di lavoro

Esegui questi controlli nell'ordine indicato, così un problema di formattazione non porterà a modificare le regole dei livelli.

  1. Controlla il testo della categoria. Confronta il valore di input con le etichette di riferimento, spazi inclusi. Microsoft indica i caratteri imprevisti e gli spazi nascosti tra le cause comuni dei problemi di ricerca nella guida alla risoluzione dei problemi di ricerca.
  2. Controlla le celle numeriche. I valori di input delle quantità e i minimi devono contenere numeri, non testo che sembra numerico. La stessa guida Microsoft indica i tipi di dati incoerenti come possibile problema di ricerca.
  3. Controlla l'allineamento degli intervalli. Gli intervalli di categorie, minimi ed etichette devono comprendere le stesse righe di dati. In questo esempio, ogni intervallo inizia alla riga 2 e termina alla riga 7.
  4. Controlla le regole duplicate. Assegna un'unica etichetta di livello a ogni coppia categoria e quantità minima. Se due righe assegnano etichette diverse allo stesso limite, risolvi l'ambiguità nei dati di riferimento.
  5. Controlla la convenzione dei limiti. Un risultato che sembra di un livello superiore o inferiore può indicare che i valori di riferimento sono massimi, mentre questa formula si aspetta minimi.

Per un controllo visivo, inserisci la seguente formula diagnostica in una cella inutilizzata, per esempio I2. Con Texture Tile in E2, dovrebbe mostrare le tre righe complete di Texture Tile. Questa è una formula diagnostica distinta, non sostituisce G2.

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

Lascia spazio affinché il risultato diagnostico si espanda su tre colonne e tre righe. Il risultato di una matrice autonoma si espande nelle celle adiacenti, e le celle occupate possono causare #SPILL!. Microsoft spiega questo comportamento in Formule a matrice dinamica e comportamento di espansione.

Quando aggiungi altre righe di riferimento, estendi insieme tutti e tre gli intervalli di origine nella formula principale. Per esempio, se l'ultima riga di dati diventa la 10, modifica le definizioni in categories,$A$2:$A$10, minimums,$B$2:$B$10 e labels,$C$2:$C$10. Poi ripeti i test dei limiti per la categoria aggiunta.

Per elaborare più righe di input, inserisci altre coppie categoria e quantità sotto E2:F2 e copia G2 verso il basso. Gli intervalli di origine restano fissi perché i riferimenti contengono il simbolo del dollaro; i numeri di riga dei valori di input avanzano. Mantieni espliciti i limiti specifici per categoria e usa i test con coppie di categorie per verificare che ogni etichetta restituita segua la regola di inventario prevista.

Fonti