Använd en exakt matchning för att lägga till tillgångsmetadata på varje lagerrad, även när tillgångs-ID:n upprepas. I Excel för Mac kan du skapa ett enda utspillt resultat med HSTACK och XLOOKUP, eller använda separata uppslagskolumner i Excel 2021.

Den här genomgången är avsedd för en Mac-användare med många poster i ett konstinventarium och en metadatarad per tillgång. Uppgiften är att koppla varje tillgångs titel, format och färgpalett till rätt post, samtidigt som inventariets ursprungliga ordning, antal och upprepade poster bevaras. Alla exempeldata nedan är syntetiska.

Vilken version av Excel för Mac stöder den här metoden?

Kontrollera din installation via Excel → Om Microsoft Excel. I Microsofts instruktioner för Mac står det att programmets dialogruta Om visar versionsnummer och licenstyp. Kontrollera båda när du väljer formel. Om Office: Vilken version av Office använder jag?

Mac-version Metod i den här artikeln
Excel för Microsoft 365 för Mac Ett samlat resultat med HSTACK och XLOOKUP
Excel 2024 för Mac Ett samlat resultat med HSTACK och XLOOKUP
Excel 2021 för Mac Tre separata spillkolumner med XLOOKUP
Excel 2019 eller 2016 De här formlerna kräver en nyare version

Microsoft listar Microsoft 365 för Mac och Excel 2024 för Mac i dokumentationen för HSTACK. Dokumentationen för funktioner i Excel 2021 för Mac bekräftar att XLOOKUP och dynamiska matriser finns. Dokumentationen för XLOOKUP anger uttryckligen att Excel 2016 och 2019 inte stöds.

Om du har Excel 2021 använder du metoden med separata kolumner nedan. Den ger samma resultat utan att kräva HSTACK.

Förbered lager- och metadatainformation med ett gemensamt tillgångs-ID

Skapa två kalkylblad i samma arbetsbok med namnen Inventory och Metadata. Ange följande lagerdata i Inventory!A1:B6. Låt cellerna vara ett vanligt kalkylbladsområde i det här exemplet.

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

Ange metadata i 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

De upprepade ID:na i lagret är avsiktliga: två poster kan avse separata antal av samma tillgång. Metadataarket innehåller den gemensamma beskrivningen en gång. Resultatet ska därför innehålla fem datarader, trots att metadataarket bara innehåller tre tillgångar.

Använd ett tillgångs-ID som identifierar samma tillgång på ett konsekvent sätt i båda arken. I det här exemplet är alla ID:n och metadatafält ifyllda. I din egen arbetsbok bör du bestämma hur du uttryckligen vill ange ett okänt fält, till exempel med texten Unknown, så att det går att skilja från en tillgång som saknar matchning.

Kontrollera att varje lager-ID har exakt en matchning i metadata

Skriv MetadataMatches i C1 i Inventory. Skriv den här formeln i C2 och kopiera den sedan till C6:

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

Tolka resultatet innan du skapar utdata:

  • 0: tillgångs-ID:t saknas i metadataarket.
  • 1: tillgången har den enda metadatarad som förväntas i det här arbetsflödet.
  • Ett tal större än 1: det finns dubbletter av ID:t i metadata som behöver åtgärdas.

COUNTIF räknar celler som uppfyller ett villkor. För de angivna data returnerar varje kontroll 1. Använd enkla ID:n som de som visas här: COUNTIF tolkar asterisker och frågetecken som jokertecken, så ID:n som innehåller sådana tecken kräver ytterligare hantering. Använd funktionen COUNTIF i Microsoft Excel

Lös problemet med dubbletter av metadata-ID:n innan du fortsätter. XLOOKUP returnerar som standard den första matchningen, så ett ifyllt resultat bevisar inte i sig att metadatanyckeln är unik. Funktionen XLOOKUP

Skapa en berikad matris i Microsoft 365 eller Excel 2024

Skapa ett kalkylblad med namnet Enriched. Skriv de här fem rubrikerna i A1:E1: AssetID, Quantity, Title, Format, Palette. Lämna A2:E6 tomt.

Markera Enriched!A2, skriv hela formeln nedan och tryck på Retur:

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

Formeln kombinerar de två ursprungliga lagerkolumnerna med tre kolumner med uppslagsresultat. Varje uppslag använder samma fem lager-ID:n, så de tillagda fälten hålls i linje med de ursprungliga posterna. HSTACK lägger matriserna horisontellt i den angivna ordningen. Funktionen HSTACK

Det sista 0 i varje uppslag begär en exakt matchning. Texten MISSING METADATA visas när ett ID saknar matchning. Om den är synlig kan du hitta ofullständiga matchningar i stället för att lämna en tom cell utan förklaring. Funktionen XLOOKUP

Det förväntade resultatet visas i 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

Lägg märke till att båda posterna för ARC-01 finns kvar, med antalen 3 och 2. Båda får värdena Arc Study, Vector och Teal. Metadata har berikat posterna utan att slå ihop dem.

Formeln gör avsiktligt ett uppslag för ett metadatafält i taget innan resultaten sammanfogas. Då blir resultatets form tydlig: fem lagerrader, två ursprungliga kolumner och tre tillagda kolumner.

Använd tre uppslagskolumner i Excel 2021 för Mac

I Excel 2021 arbetar du direkt bredvid det ursprungliga lagret. Behåll kontrollen av antalet matchningar i kolumn C och skriv sedan Title, Format och Palette i D1:F1. Lämna D2:F6 tomt.

Skriv den här formeln i Inventory!D2:

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

Skriv den här formeln i Inventory!E2:

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

Skriv den här formeln i Inventory!F2:

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

Tryck på Retur efter varje formel. Varje resultat fyller fem rader under startcellen. Tillgångs-ID:n och antal finns kvar i kolumnerna A:B, diagnosräkningen finns kvar i C och den tillagda metadatan visas i D:F. Excel 2021 för Mac innehåller den funktion för dynamiska matriser som behövs för den här metoden. Nyheter i Excel 2021 för Mac

Anpassa arbetsboken när lagret får fler rader

Exemplen använder fasta områden så att varje referens är synlig. När lagret växer utökar du alla lagerreferenser till samma sista rad. Om lagret till exempel slutar på rad 501 behöver den samlade formeln referenserna Inventory!A2:B501 och tre förekomster av Inventory!A2:A501. Utöka kontrollen av antalet matchningar till samma rad.

När metadataarket växer utökar du alla metadatareferenser samtidigt. Om den sista raden blir 101 använder du Metadata!A2:A101 för nycklarna och motsvarande returintervall B2:B101, C2:C101 och D2:D101. Ta med faktiska poster i stället för att lägga till många oanvända rader.

Om arbetsboken växer regelbundet kan du överväga att konvertera källområdena till Excel-tabeller och använda strukturerade referenser. Microsoft dokumenterar att referenserna justeras när källrader läggs till eller tas bort. Låt det utspillda resultatet ligga i kalkylbladets rutnät utanför tabellerna. Dynamiska matrisformler och beteendet för utspillda matriser

Om du behöver berika data i en Excel-tabell använder du en separat formel som ger ett enda resultat för varje metadatakolumn och lagerpost. Tabeller stöder beräknade kolumner som tillämpar en formel genom hela kolumnen; en matris som spills över flera celler ska ligga utanför tabellen. Översikt över Excel-tabeller

Felsök problem och verifiera det färdiga resultatet

Om Excel visar #SPILL! kontrollerar du de avsedda målcellerna. Flytta innehåll som du behöver behålla och kontrollera att formeln ligger utanför en Excel-tabell. Det går bara att redigera den övre vänstra cellen i ett utspillt resultat; ändra formeln där. Microsoft dokumenterar dessa spillgränser och fel som uppstår när spillområdet blockeras. Dynamiska matrisformler och beteendet för utspillda matriser

Om en rad visar MISSING METADATA går du tillbaka till kontrollen av antalet matchningar. Noll betyder att du behöver lägga till den saknade metadataposten eller rätta till tillgångs-ID:t. Kontrollera om det finns oavsiktliga inledande eller avslutande blanksteg när etiketter som ser identiska ut inte fungerar som väntat; Microsoft anger att sådana blanksteg kan ge oväntade resultat med COUNTIF. Använd funktionen COUNTIF i Microsoft Excel

Behåll de två källkalkylbladen i samma arbetsbok i den här konfigurationen. Om du senare flyttar källan till en annan arbetsbok bör du ta hänsyn till Microsofts dokumenterade begränsning: länkade dynamiska matriser kräver att båda arbetsböckerna är öppna, och en uppdatering medan källarbetsboken är stängd kan ge #REF!. Dynamiska matrisformler och beteendet för utspillda matriser

Verifiera exemplet med syntetiska data genom tre konkreta kontroller:

  1. Det finns fem utdatarader i den ursprungliga lagerordningen.
  2. Antalen är fortfarande 3, 5, 2, 4, 1, totalt 15.
  3. Upprepade ID:n får identisk metadata och varje kontroll av antalet matchningar visar 1.

Dessa kontroller bekräftar det avsedda resultatet: varje lagerpost behåller sin plats och sitt antal samtidigt som den får beskrivningen som hör till dess tillgångs-ID. Upprepa samma kontroller i en större arbetsbok när du har utökat områdena eller lagt till metadataposter.

Källor