Usa una ricerca con corrispondenza esatta per aggiungere i metadati delle risorse a ogni riga dell’inventario, inclusi gli ID risorsa ripetuti. In Excel per Mac, puoi creare un unico risultato espanso con HSTACK e XLOOKUP, oppure usare colonne di ricerca separate in Excel 2021.

Questa guida è rivolta a chi usa Excel su un Mac desktop e ha molte voci nell’inventario delle risorse artistiche, con una riga di metadati per ogni risorsa. L’obiettivo è associare a ogni risorsa il titolo, il formato e la palette, mantenendo l’ordine originale, le quantità e le voci ripetute dell’inventario. Tutti i dati di esempio riportati di seguito sono sintetici.

Quale versione di Excel per Mac supporta questo metodo?

Controlla la tua installazione da Excel → Informazioni su Microsoft Excel. Le istruzioni Microsoft per Mac spiegano che la finestra Informazioni mostra il numero di versione e il tipo di licenza dell’applicazione. Controllali entrambi quando scegli una formula. Informazioni su Office: quale versione di Office sto usando?

Edizione per Mac Metodo descritto in questo articolo
Excel per Microsoft 365 per Mac Un unico risultato combinato con HSTACK e XLOOKUP
Excel 2024 per Mac Un unico risultato combinato con HSTACK e XLOOKUP
Excel 2021 per Mac Tre colonne espanse separate con XLOOKUP
Excel 2019 o 2016 Queste formule richiedono un’edizione più recente

Microsoft elenca Microsoft 365 per Mac ed Excel 2024 per Mac nella documentazione di HSTACK. La documentazione delle funzionalità di Excel 2021 per Mac conferma XLOOKUP e le matrici dinamiche. La documentazione di XLOOKUP esclude esplicitamente Excel 2016 e 2019.

Se hai Excel 2021, usa il metodo con colonne separate descritto di seguito. Consente di ottenere lo stesso arricchimento senza richiedere HSTACK.

Impostare l’inventario e i metadati con un ID risorsa condiviso

Crea due fogli di lavoro nella stessa cartella di lavoro, denominati Inventory e Metadata. Inserisci i dati seguenti dell’inventario in Inventory!A1:B6. Per questo esempio, mantieni queste celle come un normale intervallo del foglio di lavoro.

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

Inserisci i metadati in Metadata!A1:D4:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

Gli ID ripetuti nell’inventario sono intenzionali: due voci possono indicare quantità separate della stessa risorsa. Il foglio dei metadati fornisce una sola volta la descrizione condivisa. L’output dovrà quindi contenere cinque righe di dati, anche se i metadati includono solo tre risorse.

Usa un ID risorsa che identifichi in modo coerente la stessa risorsa in entrambi i fogli. In questo esempio, tutti gli ID e i campi dei metadati sono compilati. Nella tua cartella di lavoro, decidi come rappresentare esplicitamente un campo sconosciuto, ad esempio con il testo Unknown, in modo da distinguerlo da una risorsa senza corrispondenza.

Verificare che ogni ID dell’inventario abbia esattamente una corrispondenza nei metadati

In Inventory, inserisci MetadataMatches in C1. Inserisci questa formula in C2, poi copiala fino a C6:

=COUNTIF(Metadata!$A$2:$A$4,A2)

Interpreta il risultato prima di creare l’output:

  • 0: l’ID risorsa non è presente nel foglio dei metadati.
  • 1: la risorsa ha l’unica riga di metadati prevista da questo flusso di lavoro.
  • Qualsiasi numero maggiore di 1: i metadati contengono ID duplicati che vanno risolti.

COUNTIF conta le celle che soddisfano un criterio. Con i dati forniti, ogni controllo restituisce 1. Usa ID semplici come quelli mostrati qui: COUNTIF considera gli asterischi e i punti interrogativi come caratteri jolly, quindi gli ID che contengono tali caratteri richiedono una gestione aggiuntiva. Usare la funzione CONTA.SE in Microsoft Excel

Risolvi gli ID duplicati nei metadati prima di continuare. XLOOKUP restituisce per impostazione predefinita la prima corrispondenza, quindi un risultato compilato da solo non dimostra che la chiave dei metadati sia univoca. Funzione XLOOKUP

Creare una matrice arricchita in Microsoft 365 o Excel 2024

Crea un foglio di lavoro denominato Enriched. Inserisci queste cinque intestazioni in A1:E1: AssetID, Quantity, Title, Format, Palette. Lascia vuoto l’intervallo A2:E6.

Seleziona Enriched!A2, inserisci la formula completa riportata di seguito e premi Return:

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

Questa formula combina le due colonne originali dell’inventario con tre colonne di risultati delle ricerche. Ogni ricerca usa gli stessi cinque ID dell’inventario, mantenendo i campi aggiunti allineati alle voci originali. HSTACK aggiunge le matrici orizzontalmente nell’ordine specificato. Funzione HSTACK

L’ultimo argomento 0 di ogni ricerca richiede una corrispondenza esatta. Per un ID senza corrispondenza viene restituito il testo MISSING METADATA. Mantenerlo visibile aiuta a individuare le corrispondenze incomplete, invece di lasciare una cella vuota senza spiegazione. Funzione XLOOKUP

L’output previsto occupa Enriched!A2:E6:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

Nota che entrambe le voci ARC-01 restano presenti, con quantità 3 e 2. Entrambe ricevono Arc Study, Vector e Teal. I metadati hanno arricchito queste voci senza unirle.

La formula cerca intenzionalmente un campo di metadati alla volta prima di unire i risultati. In questo modo, la struttura dell’output è esplicita: cinque righe di inventario, due colonne originali e tre colonne aggiunte.

Usare tre colonne di ricerca in Excel 2021 per Mac

Per Excel 2021, lavora direttamente accanto all’inventario originale. Mantieni il controllo del numero di corrispondenze nella colonna C, poi inserisci Title, Format e Palette in D1:F1. Lascia vuoto l’intervallo D2:F6.

Inserisci questa formula in Inventory!D2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

Inserisci questa formula in Inventory!E2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

Inserisci questa formula in Inventory!F2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

Premi Return dopo ogni formula. Ogni risultato riempie cinque righe sotto la cella iniziale. Gli ID risorsa e le quantità restano nelle colonne A:B, il conteggio diagnostico resta in C e i metadati aggiunti compaiono in D:F. Excel 2021 per Mac include il comportamento delle matrici dinamiche necessario per questo metodo. Novità di Excel 2021 per Mac

Adattare la cartella di lavoro a più righe di inventario

Gli esempi usano intervalli fissi per rendere visibili tutti i riferimenti. Quando l’inventario cresce, estendi ogni riferimento all’inventario fino alla stessa ultima riga. Ad esempio, se l’inventario termina alla riga 501, la formula combinata deve includere Inventory!A2:B501 e tre istanze di Inventory!A2:A501. Estendi fino alla stessa riga anche il controllo del numero di corrispondenze.

Quando aumentano i metadati, estendi insieme tutti i relativi riferimenti. Se l’ultima riga diventa 101, usa Metadata!A2:A101 per le chiavi e i corrispondenti intervalli di restituzione B2:B101, C2:C101 e D2:D101. Includi i record effettivi invece di aggiungere un grande blocco di righe inutilizzate.

Per una cartella di lavoro che cresce regolarmente, valuta la conversione degli intervalli di origine in tabelle Excel e l’uso di riferimenti strutturati. Microsoft documenta che questi riferimenti si aggiornano quando vengono aggiunte o rimosse righe di origine. Mantieni l’output espanso nella griglia del foglio di lavoro, fuori da qualsiasi tabella. Formule di matrice dinamica e comportamento delle matrici espanse

Se devi arricchire i dati all’interno di una tabella Excel, usa una formula con risultato singolo separata per ogni colonna di metadati e riga dell’inventario. Le tabelle supportano colonne calcolate che applicano una formula all’intera colonna; una matrice che si espande su più celle deve trovarsi fuori dalla tabella. Panoramica delle tabelle di Excel

Diagnosticare i problemi e verificare il risultato finale

Se Excel visualizza #SPILL!, controlla le celle di destinazione previste. Sposta gli eventuali contenuti esistenti che vuoi conservare e assicurati che la formula sia fuori da una tabella Excel. È possibile modificare solo la cella in alto a sinistra di un risultato espanso; modifica la formula in quella cella. Microsoft documenta questi limiti di espansione e gli errori dovuti a ostruzioni. Formule di matrice dinamica e comportamento delle matrici espanse

Se una riga mostra MISSING METADATA, controlla il relativo conteggio delle corrispondenze. Un valore pari a zero indica che devi aggiungere il record di metadati mancante o correggere l’ID risorsa. Se etichette visivamente identiche non si comportano come previsto, verifica che non ci siano spazi iniziali o finali involontari: Microsoft li indica come possibile causa di risultati COUNTIF inattesi. Usare la funzione CONTA.SE in Microsoft Excel

Per questa configurazione, mantieni i due fogli di origine nella stessa cartella di lavoro. Se in seguito sposti l’origine in un’altra cartella di lavoro, tieni conto del limite documentato da Microsoft: le matrici dinamiche collegate richiedono che entrambe le cartelle di lavoro siano aperte e l’aggiornamento con l’origine chiusa può produrre #REF!. Formule di matrice dinamica e comportamento delle matrici espanse

Verifica l’esempio sintetico con tre controlli concreti:

  1. Ci sono cinque righe di dati nell’output, nello stesso ordine dell’inventario originale.
  2. Le quantità restano 3, 5, 2, 4, 1, per un totale di 15.
  3. Gli ID ripetuti ricevono metadati identici e ogni controllo del numero di corrispondenze restituisce 1.

Questi controlli confermano il risultato previsto: ogni voce dell’inventario mantiene la propria posizione e quantità e riceve la descrizione associata al suo ID risorsa. Per una cartella di lavoro più grande, ripeti gli stessi controlli dopo aver esteso gli intervalli o aggiunto record di metadati.

Fonti