I Microsoft 365 för Mac eller Excel 2024 för Mac kan du kombinera CHOOSECOLS, TOCOL, HSTACK och FILTER för att omvandla återkommande etiketter och kvantiteter för konsttillgångar till lagerrader. Formeln nedan bevarar parens inbördes ordning, nollkvantiteter och ofullständiga poster.

Den här genomgången är avsedd för dig som använder Excel på en Mac och har ett kalkylblad där en attributkolumn följs av en kvantitetskolumn som upprepas. Här är varje attribut en etikett för en konsttillgång, till exempel Circle Tile. Målet är att skapa en inventering med två kolumner och sedan slå upp kvantiteter utan att skilja dem från etiketterna.

Vilka versioner av Excel för Mac stöder den här formeln?

Microsoft anger uttryckligen att Excel för Microsoft 365 för Mac och Excel 2024 för Mac stöder alla tre omformningsfunktionerna: CHOOSECOLS, TOCOL och HSTACK. Använd någon av dessa utgåvor för huvudformeln.

Excel 2021 för Mac har dynamiska matriser, FILTER, LET och XLOOKUP, men det innebär inte att versionen stöder de nyare omformningsfunktionerna. Microsofts funktionsdokumentation för Excel 2021 för Mac beskriver vilka matris- och uppslagsfunktioner som finns; de tre funktionssidorna ovan listar inte Excel 2021.

Kontrollera utgåva och version i programmets dialogruta Om. Microsofts versionsguide för Office förklarar att dialogrutan på Mac visar både version och licenstyp. Om din utgåva saknar omformningsfunktionerna använder du Microsoft 365 för Mac eller Excel 2024 för Mac för den automatiska metoden, eller följer den manuella metoden för att stapla data som beskrivs nedan.

Skapa ett fullständigt syntetiskt exempel

Skriv in följande data i A1:F4 på ett tomt kalkylblad. Lämna de två tomma cellerna mitt på rad 3 helt tomma. Alla etiketter och kvantiteter är illustrativa.

Attribut 1 Kvantitet 1 Attribut 2 Kvantitet 2 Attribut 3 Kvantitet 3
Circle Tile 4 Line Texture 2 Dot Pattern 0
Arc Motif 3 Grid Texture 5
Circle Tile 7 Wave Pattern 6 Square Tile 1

Varje intilliggande par motsvarar en inventariepost. Det avsedda resultatet läser från vänster till höger på varje källrad och fortsätter sedan nedåt. Circle Tile förekommer två gånger eftersom det rör sig om separata poster; att slå ihop upprepade etiketter är en separat uppgift.

Metoden förutsätter att varje etikett omedelbart följs av dess kvantitet och att alla markerade kolumner ingår i det upprepade mönstret. Om kalkylbladet också innehåller ett samlingsnamn eller något annat fält som ska följa med varje post, behålls det inte i resultatet med två kolumner. Bestäm vilka fält inventeringen behöver innan du använder formeln.

Omvandla de upprepade paren till inventarierader

Skriv Tillgång i H1 och Kvantitet i I1. Låt utdataområdet under rubrikerna vara tomt. Klistra in hela formeln i H2 och tryck sedan på Retur. Formler med dynamiska matriser anges en gång och expanderar till intilliggande celler, vilket beskrivs i Microsofts guide till matrisformler.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

Det förväntade resultatet fyller H2:I9:

Tillgång Kvantitet
Circle Tile 4
Line Texture 2
Dot Pattern 0
Arc Motif 3
Grid Texture 5
Circle Tile 7
Wave Pattern 6
Square Tile 1

Parordningen i exemplet, de åtta utdataposterna, bevarandet av nollan och den totala kvantiteten 28 kontrollerades med en liten deterministisk Python-beräkning. Excel-formeln granskades mot den syntax Microsoft dokumenterar för funktionerna, men kördes inte i Excel.

Så håller formeln paren i rätt ordning

LET ger mellanresultat namn så att formeln kan återanvända dem. Namnen source, clean, labels, quantities och keep finns bara i den här formeln; du behöver inte skapa namngivna områden. Microsoft beskriver detta i referensen för LET.

CHOOSECOLS väljer etikettkolumnerna 1, 3 och 5 och kvantitetskolumnerna 2, 4 och 6. Dessa tal anger positioner inom det markerade källområdet. Microsofts dokumentation för CHOOSECOLS bekräftar att funktionen returnerar de angivna kolumnerna från en matris.

Båda anropen av TOCOL använder 0 för att behålla varje position och FALSE för att gå igenom raderna. Eftersom de går igenom motsvarande positioner i samma ordning förblir varje etikett kopplad till sin kvantitet. Microsoft beskriver båda argumenten i referensen för TOCOL.

HSTACK placerar de två resulterande kolumnerna bredvid varandra. Båda innehåller nio positioner före filtreringen, så längderna stämmer överens. Microsoft förklarar att HSTACK lägger till matriser vågrätt och fyller ut kortare matriser med felvärden om deras radantal skiljer sig åt.

Slutligen väljer keep positioner som innehåller en etikett, en kvantitet eller båda. FILTER tar endast bort helt tomma par. Det sista argumentet anger ett meddelande om inga poster återstår, enligt syntaxen Microsoft dokumenterar för FILTER.

Bevara tomma värden och nollor utan att dölja ofullständiga poster

En tom kvantitet och den numeriska nollan förmedlar olika information. Noll är ett angivet antal; en tom cell behöver fortfarande tolkas. Microsofts exempel på TOCOL visar att tomma celler kan returnera noll om de inte hanteras särskilt. Därför använder steget clean IF för att returnera tom text för tomma källceller innan omformningen. Microsoft beskriver villkorliga returvärden i referensen för IF.

Formeln behåller avsiktligt ofullständiga par så att de syns. Kontrollera följande innan du förlitar dig på resultatet:

Källpar Resultat Åtgärd
Etikett och numerisk kvantitet Båda behålls Kontrollera att kvantiteten är avsedd
Etikett och nolla Nollan behålls Behåll den om noll är ett avsiktligt antal
Båda cellerna tomma Paret tas bort Ingen korrigering behövs
Etikett med tom kvantitet Etiketten och den tomma kvantiteten behålls Fyll i kvantiteten
Tom etikett med kvantitet Den tomma etiketten och kvantiteten behålls Fyll i etiketten

Undvik att ta bort tomma värden oberoende från etiketternas och kvantiteternas matriser. Om en etikett saknas på en position och en kvantitet saknas på en annan kan separat borttagning av tomma värden förskjuta matriserna och skapa felaktiga par. Att behålla positionerna tills matriserna har sammanfogats är den viktiga designprincipen i den här formeln.

En cell som verkar tom men innehåller blanksteg behandlas inte som tom av den här formeln. Rätta sådana poster i källdata. Formeln bevarar också kvantiteter som har angetts som text i stället för att automatiskt omvandla dem. Granska dessa värden innan du beräknar summor.

Slå upp en post eller returnera alla matchande poster

Skriv Circle Tile i K2. Om du vill hämta den första kvantiteten anger du den här formeln i L2:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2# syftar på hela det spillda resultatet som börjar i H2, så uppslaget följer ändringar i resultatets storlek. Microsoft beskriver den här notationen i dokumentationen om operatorn för spillområden.

Resultatet är 4. Det uttryckliga matchningsläget 0 begär en exakt matchning, och standardsökningen returnerar den första matchande posten. Microsofts referens för XLOOKUP beskriver båda funktionerna. Den bekräftar också att XLOOKUP inte är tillgänglig i Excel 2016 och Excel 2019.

Om upprepade etiketter är betydelsefulla räcker det inte med ett uppslag som bara returnerar den första träffen. Om du vill returnera alla poster för Circle Tile placerar du den här alternativa formeln i N2 och lämnar plats i kolumnerna N:O:

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

Den returnerar två rader: Circle Tile med 4 och Circle Tile med 7. FILTER returnerar rader som uppfyller det angivna villkoret, vilket beskrivs i Microsofts referens för FILTER. Välj formeln för första träffen när en matchande post besvarar frågan; välj det filtrerade resultatet när du behöver granska alla förekomster.

Kontrollera resultatet och anpassa källområdet

För exemplet bekräftar du att det finns åtta utdata­rader och att Dot Pattern fortfarande har kvantiteten noll. Jämför sedan den totala kvantiteten i källan med den totala kvantiteten efter omformningen. Skriv in följande formler i två lediga celler:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Båda ska returnera 28. Microsofts dokumentation för SUM visar att funktionen kan summera flera områden och förklarar att textvärden ignoreras. Samma summa bevisar därför inte att varje kvantitet är numerisk och kan inte upptäcka kvantiteter som bytts mellan etiketter. Kontrollera även etiketterna och kvantiteterna parvis.

För fler källrader ändrar du A2:F4 i huvudformeln till det faktiska området, till exempel A2:F100. Rader utanför referensen tas inte med. För fyra upprepade par ändrar du källan till åtta kolumner, till exempel A2:H100, och uppdaterar etikettpositionerna till 1,3,5,7 och kvantitetspositionerna till 2,4,6,8.

När du utökar till åtta källkolumner flyttar du utdata till ett tomt område, till exempel J2, och uppdaterar efterföljande spillreferenser till J2#. Håll källa och utdata åtskilda. En ogiltig kolumnposition ger ett fel, vilket dokumenteras under fel för CHOOSECOLS.

Åtgärda blockerade utdata eller använd den manuella metoden för att stapla data

Om huvudformeln returnerar #SPILL! markerar du formelcellen och granskar det avsedda utdataområdet. Flytta befintliga poster från området och kontrollera om det finns sammanfogade celler. Placera formeln utanför en Excel-tabell: Microsoft dokumenterar i sin felsökningsguide för #SPILL! att matrisformler med spill inte kan expandera i tabeller eller till sammanfogade celler.

Om källdata innehåller ett fel rättar du den posten innan du använder inventeringen. Formeln döljer inte fel; Microsoft påpekar att ett fel i inkluderingsmatrisen för FILTER gör att funktionen returnerar ett fel. Om hela omvandlingen omsluts av felhantering kan en post som behöver rättas döljas.

För ett litet kalkylblad i Excel 2021 för Mac kan du manuellt skapa samma struktur med två kolumner: kopiera A2:B4 till H2:I4, kopiera C2:D4 till H5:I7 och kopiera E2:F4 till H8:I10. Ta endast bort rader där båda cellerna är tomma. Detta staplar poster per parblock i stället för i källradordningen som huvudformeln använder, och arbetet måste upprepas efter ändringar i källdata.

Efter den manuella konfigurationen kan Excel 2021 för Mac använda sin dokumenterade XLOOKUP-funktion med fasta områden:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

En användbar färdig inventering bevarar alla betydelsefulla par, visar ofullständiga poster och använder ett uppslag som passar för upprepade etiketter. Bekräfta dessa egenskaper med exemplet först och utöka sedan källområdet så att det passar kalkylbladet.

Källor