För att sammanfoga upprepade tillgångsetiketter utan att multiplicera rader numrerar du varje etiketts förekomster i båda listorna och slår sedan upp paret etikett och förekomst. Den här genomgången för Excel för Mac bevarar varje rad i vänsterlistan och markerar saknade träffar.

Vad innebär matchning efter förekomst?

Anta att två listor med konsttillgångar båda innehåller flera rader med etiketten Arc Tile. Din uppgift är att koppla den första källkvantiteten till den första Arc Tile-raden i vänsterlistan, den andra källkvantiteten till den andra och så vidare. Andra etiketter kan förekomma mellan dessa rader; förekomstnumret räknas separat för varje etikett.

En relation som bara baseras på etiketten är tvetydig. Om den ena listan innehåller tre rader med Arc Tile och den andra innehåller två, skulle varje möjlig kombination av matchande rader ge sex kombinationer för den etiketten. En vanlig uppslagning har en annan begränsning: Microsoft dokumenterar att XLOOKUP returnerar den första träffen som hittas. En upprepning av en uppslagning som bara baseras på etiketten går därför inte vidare till nästa förekomst. XLOOKUP-funktionen

Lösningen är att göra uppslagsnyckeln mer specifik: Arc Tile|1, Arc Tile|2 och Arc Tile|3. Varje numrerad nyckel identifierar en förekomst. Metoden nedan visar en konstruktion med dokumenterade Excel-funktioner; Microsofts dokumentation beskriver hur funktionerna fungerar.

Använd den här metoden när radordningen avgör kopplingen. Om raderna i stället ska matchas med ett separat tillgångs-ID eller revisionsfält använder du den informationen. Förekomstnummer kan inte avgöra vilka två poster som hör ihop om ordningen saknar betydelse.

Vilka Excel för Mac-versioner stöder den här metoden?

Huvudformlerna använder COUNTIF, sammanfogning med & och XLOOKUP. På de relevanta funktionssidorna listar Microsoft Excel för Microsoft 365 för Mac, Excel 2024 för Mac och Excel 2021 för Mac. I dokumentationen för XLOOKUP står det uttryckligen att funktionen inte är tillgänglig i Excel 2016 och Excel 2019. XLOOKUP-funktionen, Använd COUNTIF-funktionen i Microsoft Excel

Börja med uppslagsformeln som kopieras nedåt. En senare variant returnerar samma sju resultat med en enda matrisformel som spiller. Microsoft dokumenterar att spillande formler ska placeras i kalkylbladets cellområde utanför Excel-tabeller. Dynamiska matrisformler och hur spill fungerar

Konfigurera två fiktiva tillgångslistor

Använd ett tomt kalkylblad i en arbetsbok. Ange följande vänsterlista i A1:B8. Etiketterna och kvantiteterna är exempel; vänsterkvantiteten hör till ursprungslistan och används inte för att avgöra matchningen.

Tillgångsetikett Kvantitet vänster
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

Ange källistan i G1:H7. Behåll den visade radordningen så att resultaten stämmer med genomgången.

Tillgångsetikett Källkvantitet
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

Lägg till rubrikerna Occurrence, Join key och Matched source quantity i C1, D1 och E1. Lägg till Occurrence, Join key och Used by left list i I1, J1 och K1. Lämna kolumn F tom för att visuellt separera listorna.

I det här exemplet använder du icke-tomma textetiketter som är kortare än 255 tecken, med konsekvent användning av versaler, gemener och blanksteg och utan jokertecken som *, ? eller ~. Ange en numerisk källkvantitet för varje källrad, även noll där det är lämpligt. Dessa förutsättningar gör tolkningen av de enkla formlerna entydig.

Numrera förekomster och skapa uppslagsnycklarna

Räkna varje etikett från början av dess egen lista

Ange den här formeln i C2, tryck på Retur och kopiera den ned till C8:

=COUNTIF($A$2:A2,A2)

Den första ändpunkten, $A$2, förblir låst. Den andra ändpunkten utökas när du kopierar formeln nedåt. På varje rad räknar formeln hur många gånger radens etikett har förekommit hittills. COUNTIF räknar celler som uppfyller ett angivet villkor. Använd COUNTIF-funktionen i Microsoft Excel

Förekomstnumren i vänsterlistan ska nu vara 1, 1, 2, 1, 2, 3, 1. I synnerhet får den tredje Arc Tile värdet 3, även om andra etiketter förekommer mellan raderna.

Ange motsvarande källformel i I2 och kopiera den sedan till I7:

=COUNTIF($G$2:G2,G2)

Förekomstnumren i källistan ska vara 1, 1, 1, 2, 2, 1. Räkna de två listorna oberoende av varandra: en rads förekomstnummer anger dess position bland matchande etiketter i den egna listan.

Kombinera etiketten och förekomsten

Ange den här formeln i D2 och kopiera den till D8:

=A2&"|"&C2

Ange den här formeln i J2 och kopiera den till J7:

=G2&"|"&I2

Vänsterlistan innehåller nu Arc Tile|1, Arc Tile|2 och Arc Tile|3. Källistan innehåller bara de två första nycklarna. Därmed blir den saknade tredje förekomsten synlig innan några kvantiteter returneras.

Returnera en källkvantitet per rad i vänsterlistan

Ange följande formel i E2 och kopiera den sedan till E8:

=XLOOKUP(D2,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

Formeln söker igenom källnycklarna, returnerar motsvarande källkvantitet och visar Unmatched när den numrerade nyckeln saknas. Det sista argumentet 0 begär uttryckligen en exakt matchning, vilket också är standardinställningen för XLOOKUP. XLOOKUP-funktionen

Det färdiga resultatet för vänsterlistan ska vara:

Tillgångsetikett Kvantitet vänster Förekomst Sammanfogningsnyckel Matchad källkvantitet
Arc Tile 4 1 Arc Tile\ 1
Dot Panel 2 1 Dot Panel\ 1
Arc Tile 6 2 Arc Tile\ 2
Line Print 3 1 Line Print\ 1
Dot Panel 5 2 Dot Panel\ 2
Arc Tile 1 3 Arc Tile\ 3
Ring Card 2 1 Ring Card\ 1

Det finns fortfarande sju rader i vänsterlistan. Den andra Arc Tile får 9 i stället för att upprepa 8 från den första förekomsten. Den tredje saknar en motsvarande källrad. Ring Card saknar också en motsvarighet, medan den matchade nollan för den andra Dot Panel fortfarande är synlig.

Håll Unmatched åtskilt från noll. I det här exemplet är noll en verklig källkvantitet; om du använder noll för en saknad matchning döljer du den skillnaden. Lös även tomma källkvantiteter innan du tolkar resultatet, så att en tom källcell inte misstas för en registrerad kvantitet.

Valfritt: returnera resultaten som en enda matris

När du har kontrollerat resultaten från de nedkopierade formlerna rensar du endast E2:E8 och anger det här alternativet i E2:

=XLOOKUP(D2:D8,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

Detta skickar in alla sju uppslagsnycklarna från vänsterlistan som uppslag. Med exempeldata fyller resultatet E2:E8 i vänsterlistans ordning. Tryck på Retur; de intilliggande utdatacellerna måste vara tomma. Microsoft beskriver denna automatiska placering av matrisresultat som spill. Dynamiska matrisformler och hur spill fungerar

Välj den nedkopierade versionen om du vill kunna granska varje rads formel separat. Välj den spillande versionen om du föredrar en enda formel som styr resultatkolumnen. Båda använder samma förekomstnycklar.

Kontrollera saknade matchningar och oanvända källrader

Att bevara antalet rader i vänsterlistan betyder inte att varje källrad har använts. Lägg till en omvänd kontroll så att extra förekomster i källistan förblir synliga. Ange följande i K2 och kopiera det till K7:

=COUNTIF($D$2:$D$8,J2)

I det här exemplet returnerar de fem första källraderna 1, medan Grid Sheet|1 returnerar 0. Den nollan identifierar en källrad utan motsvarighet i vänsterlistan. Ett värde större än 1 skulle innebära att en källnyckel används av flera vänsternycklar och behöver undersökas.

Använd kontrollerna tillsammans:

  • Bekräfta att resultatet omfattar exakt de sju ursprungliga raderna i vänsterlistan.
  • Bekräfta kvantitetsföljden: 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • Bekräfta att den tredje Arc Tile och Ring Card fortfarande tydligt visas som omatchade.
  • Bekräfta att Grid Sheet|1 markeras som oanvänd i källgranskningen.

Den förväntade följden och resultatet för oanvänd källa kontrollerades med en liten deterministisk beräkning av logiken för matchning efter förekomst. Excel-formlerna granskades mot Microsofts dokumenterade syntax; de kördes inte i Excel för Mac.

Felsök resultatet innan du utökar arbetsboken

Kopplingarna ändras efter sortering

Förekomstformlerna använder den aktuella radordningen. Om du sorterar någon av listorna och räknar om kan det ändra vilken upprepad rad som får förekomstnumret 1 eller 2. Bestäm den avsedda ordningen innan du numrerar. Om en permanent koppling krävs bör du behålla en stabil identifierare som registrerar den, i stället för att enbart förlita dig på en omräknad position.

Etiketter ser likadana ut men matchar oväntat

Microsoft påpekar att COUNTIF inte tar hänsyn till skiftläge, känner igen jokerteckenvillkor och kan ge oväntade resultat med inledande eller avslutande blanksteg eller andra oönskade tecken. Dokumentationen beskriver också begränsningar vid matchning av strängar som är längre än 255 tecken. Standardisera etiketterna i båda listorna innan du numrerar dem; de korta, konsekvent formaterade etiketterna ovan undviker dessa förhållanden. Använd COUNTIF-funktionen i Microsoft Excel

Matrisformeln visar #SPILL!

Kontrollera om det finns befintligt innehåll i E3:E8, inklusive de tidigare nedkopierade formlerna. Flytta det du vill behålla och rensa sedan hindret. Om formeln finns i en Excel-tabell använder du den nedkopierade metoden eller flyttar spillresultatet utanför tabellen. Det här är dokumenterade begränsningar för spill. Dynamiska matrisformler och hur spill fungerar

Du behöver en uppslagning utan XLOOKUP

Samma hjälplistor med nycklar kan användas för en uppslagning med INDEX/MATCH. Som alternativ till någon av formlerna ovan anger du detta i E2 och kopierar det till E8:

=INDEX($H$2:$H$7,MATCH(D2,$J$2:$J$7,0))

Här hittar MATCH med 0 den exakta sammanfogningsnyckeln, och INDEX returnerar kvantiteten på den positionen. Saknade nycklar förblir synliga som #N/A. Microsoft dokumenterar båda funktionerna för aktuella Mac-utgåvor. Den här skalära formeln kräver inget spillområde. MATCH-funktionen, INDEX-funktionen

Nya rader saknas i resultaten

Genomgången använder fasta områden. När du lägger till rader utökar du formlerna för förekomster och sammanfogningsnycklar, källans uppslags- och returintervall samt det omvända granskningsintervallet tillsammans. För den spillande versionen utökar du även uppslagsindata från vänsterlistan utöver D8. Om du bara lägger till data nedanför dessa områden ingår de inte i beräkningen.

När resultatföljden och den omvända granskningen stämmer med den avsedda ordningen har du en användbar sammanfogning baserad på förekomster: varje tillgångsrad i vänsterlistan behåller sin plats, får högst en källkvantitet och visar tydligt när den matchande förekomsten saknas.

Källor