Gebruik een zoekopdracht met exacte overeenkomst om assetmetadata toe te voegen aan elke inventarisrij, ook bij herhaalde asset-ID's. In Excel voor Mac kun je één overlopende uitkomst maken met HSTACK en XLOOKUP, of afzonderlijke zoekkolommen gebruiken in Excel 2021.

Deze handleiding is bedoeld voor een Mac-desktopgebruiker met veel inventarisvermeldingen van kunstassets en één metadatarij per asset. De taak is om de titel, indeling en het palet van elke asset toe te voegen, met behoud van de oorspronkelijke volgorde, aantallen en herhaalde vermeldingen in de inventaris. Alle voorbeeldgegevens hieronder zijn synthetisch.

Welke versie van Excel voor Mac ondersteunt deze aanpak?

Controleer je installatie via Excel → Over Microsoft Excel. In de Mac-instructies van Microsoft staat dat in het dialoogvenster Info over de versie en het licentietype van de toepassing worden weergegeven. Controleer beide wanneer je een formule kiest. Over Office: Welke versie van Office gebruik ik?

Mac-editie Aanpak in dit artikel
Excel voor Microsoft 365 voor Mac Eén gecombineerde uitkomst met HSTACK en XLOOKUP
Excel 2024 voor Mac Eén gecombineerde uitkomst met HSTACK en XLOOKUP
Excel 2021 voor Mac Drie afzonderlijke overlopende XLOOKUP-kolommen
Excel 2019 of 2016 Deze formules vereisen een nieuwere editie

Microsoft vermeldt Microsoft 365 voor Mac en Excel 2024 voor Mac in de documentatie over HSTACK. De documentatie over functies in Excel 2021 voor Mac bevestigt XLOOKUP en dynamische matrices. De documentatie over XLOOKUP sluit Excel 2016 en 2019 expliciet uit.

Als je Excel 2021 hebt, gebruik je de methode met afzonderlijke kolommen hieronder. Daarmee bereik je hetzelfde verrijkingsresultaat zonder HSTACK nodig te hebben.

Stel de inventaris en metadata in met een gedeelde asset-ID

Maak in dezelfde werkmap twee werkbladen met de namen Inventory en Metadata. Voer de volgende inventarisgegevens in Inventory!A1:B6 in. Gebruik voor dit voorbeeld een gewone werkbladreeks voor deze cellen.

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

Voer de metadata in Metadata!A1:D4 in:

AssetID Titel Indeling Palet
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

De herhaalde inventaris-ID's zijn bewust opgenomen: twee vermeldingen kunnen afzonderlijke aantallen van dezelfde asset beschrijven. Het metadatablad bevat de gedeelde beschrijving daarom maar één keer. De uitvoer moet dus vijf gegevensrijen bevatten, ook al bevat de metadata slechts drie assets.

Gebruik een asset-ID die in beide bladen consequent dezelfde asset aanduidt. In dit voorbeeld zijn alle ID's en metadatavelden ingevuld. Bepaal in je eigen werkmap hoe je een onbekend veld expliciet weergeeft, bijvoorbeeld met de tekst Unknown, zodat dit te onderscheiden is van een ontbrekende assetovereenkomst.

Controleer of elke inventaris-ID precies één metadataovereenkomst heeft

Voer op Inventory MetadataMatches in C1 in. Voer deze formule in C2 in en kopieer deze vervolgens tot en met C6:

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

Interpreteer het resultaat voordat je de uitvoer maakt:

  • 0: de asset-ID ontbreekt in het metadatablad.
  • 1: de asset heeft de enige metadatarij die deze workflow verwacht.
  • Elk getal groter dan 1: de metadata bevat dubbele ID's die moeten worden opgelost.

COUNTIF telt cellen die aan een criterium voldoen. Voor de opgegeven gegevens geeft elke controle 1 als resultaat. Gebruik eenvoudige ID's zoals hier: COUNTIF behandelt sterretjes en vraagtekens als jokertekens, dus voor ID's met die tekens is aanvullende verwerking nodig. De functie AANTAL.ALS gebruiken in Microsoft Excel

Los dubbele metadata-ID's op voordat je verdergaat. XLOOKUP retourneert standaard de eerste overeenkomst, dus een ingevuld resultaat bewijst op zichzelf niet dat de metadatasleutel uniek is. De functie XLOOKUP

Maak één verrijkte matrix in Microsoft 365 of Excel 2024

Maak een werkblad met de naam Enriched. Voer deze vijf kopteksten in A1:E1 in: AssetID, Quantity, Title, Format, Palette. Laat A2:E6 leeg.

Selecteer Enriched!A2, voer de volledige formule hieronder in en druk op 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)
)

Deze formule combineert de twee oorspronkelijke inventariskolommen met drie kolommen met zoekresultaten. Elke zoekopdracht gebruikt dezelfde vijf inventaris-ID's, zodat de toegevoegde velden bij de oorspronkelijke vermeldingen blijven horen. HSTACK voegt de matrices horizontaal samen, in de opgegeven volgorde. De functie HSTACK

De laatste 0 in elke zoekopdracht vraagt om een exacte overeenkomst. Voor een ID zonder overeenkomst verschijnt de tekst MISSING METADATA. Door die zichtbaar te houden, kun je onvolledige overeenkomsten vinden in plaats van een niet verklaarde lege cel over te houden. De functie XLOOKUP

De verwachte uitvoer beslaat Enriched!A2:E6:

AssetID Aantal Titel Indeling Palet
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

Let op dat beide vermeldingen van ARC-01 behouden blijven, met de aantallen 3 en 2. Beide krijgen Arc Study, Vector en Teal. De metadata heeft die vermeldingen verrijkt zonder ze samen te voegen.

De formule zoekt bewust elk metadataveld afzonderlijk op voordat de resultaten worden samengevoegd. Zo is de vorm van de uitvoer duidelijk: vijf inventarisrijen, twee oorspronkelijke kolommen en drie toegevoegde kolommen.

Gebruik drie zoekkolommen in Excel 2021 voor Mac

Werk in Excel 2021 direct naast de oorspronkelijke inventaris. Laat de controle van het aantal overeenkomsten in kolom C staan en voer Title, Format en Palette in D1:F1 in. Laat D2:F6 leeg.

Voer deze formule in Inventory!D2 in:

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

Voer deze formule in Inventory!E2 in:

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

Voer deze formule in Inventory!F2 in:

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

Druk na elke formule op Return. Elk resultaat vult vijf rijen onder de startcel. Je asset-ID's en aantallen blijven in de kolommen A:B staan, de diagnostische telling blijft in C en de toegevoegde metadata verschijnt in D:F. Excel 2021 voor Mac bevat de dynamische matrixfunctionaliteit die voor deze aanpak nodig is. Wat is nieuw in Excel 2021 voor Mac

Pas de werkmap aan voor meer inventarisrijen

De voorbeelden gebruiken vaste bereiken, zodat elke verwijzing zichtbaar is. Wanneer je inventaris groeit, breid je elke inventarisverwijzing uit tot dezelfde laatste rij. Als de inventaris bijvoorbeeld eindigt op rij 501, moet de gecombineerde formule Inventory!A2:B501 bevatten en drie keer Inventory!A2:A501. Breid de controle van het aantal overeenkomsten uit tot dezelfde rij.

Breid bij groeiende metadata alle metadataverwijzingen samen uit. Als de laatste rij daarvan 101 wordt, gebruik je Metadata!A2:A101 voor de sleutels en de bijbehorende bereiken B2:B101, C2:C101 en D2:D101 voor de retourwaarden. Neem daadwerkelijke records op in plaats van een groot blok ongebruikte rijen toe te voegen.

Overweeg voor een werkmap die regelmatig groeit de bronbereiken om te zetten in Excel-tabellen en gestructureerde verwijzingen te gebruiken. Microsoft documenteert dat deze verwijzingen worden aangepast wanneer bronrijen worden toegevoegd of verwijderd. Houd de overlopende uitvoer buiten elke tabel, in het raster van het werkblad. Dynamische matrixformules en gedrag van overlopende matrices

Gebruik afzonderlijke formules met één resultaat per metadatakolom en inventarisrij als je verrijking binnen een Excel-tabel nodig hebt. Tabellen ondersteunen berekende kolommen, waarin een formule voor de hele kolom wordt toegepast; een matrix die meerdere cellen vult, hoort buiten de tabel. Overzicht van Excel-tabellen

Problemen oplossen en het eindresultaat controleren

Als Excel #SPILL! weergeeft, controleer dan de beoogde doelcellen. Verplaats bestaande inhoud die je wilt behouden en zorg dat de formule buiten een Excel-tabel staat. Alleen de cel linksboven van een overlopend resultaat kan worden bewerkt; wijzig de formule daar. Microsoft documenteert deze grenzen voor overlopende matrices en fouten door blokkades. Dynamische matrixformules en gedrag van overlopende matrices

Als in een rij MISSING METADATA verschijnt, ga dan terug naar de controle van het aantal overeenkomsten. Een nul betekent dat je de ontbrekende metadatarij moet toevoegen of de asset-ID moet corrigeren. Controleer op onbedoelde spaties voor of na een label als visueel identieke labels zich niet gedragen zoals verwacht; Microsoft noemt deze als oorzaak van onverwachte resultaten met COUNTIF. De functie AANTAL.ALS gebruiken in Microsoft Excel

Houd voor deze installatie de twee bronwerkbladen in dezelfde werkmap. Als je de bron later naar een andere werkmap verplaatst, houd dan rekening met de door Microsoft gedocumenteerde beperking: gekoppelde dynamische matrices vereisen dat beide werkmappen geopend zijn, en vernieuwen terwijl de bron gesloten is kan #REF! opleveren. Dynamische matrixformules en gedrag van overlopende matrices

Controleer het synthetische voorbeeld aan de hand van drie concrete punten:

  1. Er zijn vijf gegevensrijen in de oorspronkelijke inventarisvolgorde.
  2. De aantallen blijven 3, 5, 2, 4, 1 en tellen op tot 15.
  3. Herhaalde ID's krijgen identieke metadata en elke controle van het aantal overeenkomsten geeft 1 als resultaat.

Met deze controles stel je vast dat het beoogde resultaat is bereikt: elke inventarisvermelding behoudt zijn plaats en aantal en krijgt tegelijk de beschrijving die bij de asset-ID hoort. Herhaal bij een grotere werkmap dezelfde controles nadat je bereiken hebt uitgebreid of metadatagegevens hebt toegevoegd.

Bronnen