Kombinera uppslagsresultat med varierande storlek i Excel för Mac genom att använda REDUCE med VSTACK för att skapa en rektangulär matris. Den här genomgången skapar en lista över konsttillgångar, bevarar ordningen på begärandena och visar grupper som saknar träffar i stället för att utelämna dem utan förvarning.

Uppgiften är enkel: varje begärd tillgångsgrupp kan motsvara flera källrader, en rad eller ingen rad. Du vill samla alla dessa resultat i ett enda expanderande kalkylbladsområde, med tillgångsetiketter och antal intakta.

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

Använd Excel för Microsoft 365 för Mac för huvudformeln nedan. Microsoft anger uttryckligen den utgåvan på sin dokumentationssida för funktionen REDUCE. Den aktuella sidan nämner inte Excel 2024 för Mac, så det faktum att andra moderna matrisfunktioner finns tillgängliga visar inte i sig att hela formeln går att använda.

Funktion Mac-utgåvor som anges i den relevanta Microsoft-dokumentationen Roll här
BYROW, MAP Microsoft 365 för Mac; Excel 2024 för Mac Förklara det ursprungliga tillvägagångssättet och dess begränsning
REDUCE Microsoft 365 för Mac Hålla det växande resultatet mellan begäranden
VSTACK, HSTACK Microsoft 365 för Mac; Excel 2024 för Mac Sammanfoga resultatblock och skapa en reservrad
FILTER Microsoft 365 för Mac; Excel 2024 för Mac; Excel 2021 för Mac Returnera alla matchande tillgångsrader

Dessa uppgifter om utgåvor kommer från Microsofts referenssidor för BYROW, MAP, VSTACK, HSTACK och FILTER.

Öppna menyn Excel och välj Om Excel för att kontrollera installationen. I Microsofts instruktioner för Mac förklaras att dialogrutan Om i ett Office-program visar dess version och licenstyp. Kontrollera båda när du väljer vilken formel du ska använda. Se Om Office: Vilken version av Office använder jag?.

En nyare förhandsversion av Mac Beta ändrar gränsen för nästlade matriser

Microsofts tillkännagivande från den 24 september 2026 introducerar nästlade matriser och FLATTEN som förhandsfunktioner för betakanalen. Det anger Mac Version 16.114 Build 26092111 eller senare, med tillgänglighet beroende på utrullningen. De flesta beräkningar med nästlade matriser kräver arbetsbokens kompatibilitetsversion 3, som väljs via Formel > Beräkningsalternativ. Dessa villkor spelar roll: det räcker inte att ha Microsoft 365 för att förhandsfunktionen ska vara aktiverad. Se Lägg in flera värden i en cell med listor och matriser i Excel.

Den genomgångna lösningen nedan skapar ett vanligt rektangulärt resultat utan att förlita sig på förhandsfunktionen. Det innebär också att arbetsbokens grundläggande uppgift att skapa en tillgångslista inte är beroende av inställningar för nästlade matriser.

Varför blir det problem när BYROW eller MAP returnerar matriser?

Microsoft beskriver BYROW som en funktion som ger ett resultat per indatarad. I referensen anges uttryckligen att ett annat resultat än ett enda värde från den inre LAMBDA ger felet #CALC!. Ett uppslag som returnerar flera poster överskrider den dokumenterade gränsen. Se Funktionen BYROW.

När du till exempel har angett exempeldata i nästa avsnitt är detta ett diagnostiskt exempel på det problematiska tillvägagångssättet enligt det dokumenterade beteendet med ett enda värde:

=BYROW(E2:E4,LAMBDA(key,FILTER(A2:C7,A2:A7=key,"No match")))

Den första begäran returnerar tre rader och tre kolumner. Om du ber BYROW att placera det blocket på en enda resultatposition uppstår ett problem med nästlade matriser. Att ändra den yttre funktionen till MAP ger inte någon generell staplingsfunktion med det konventionella beräkningsbeteendet: det inre resultatet innehåller fortfarande flera celler. Microsofts dokumentation om MAP beskriver hur indata mappas till nya värden, medan vägledningen om #CALC! beskriver begränsningen för nästlade matriser.

Att omsluta den misslyckade beräkningen med VSTACK kan inte åtgärda det inre resultatet. Den användbara ändringen är att lägga till varje uppslagsblock medan begärandena behandlas, så att varje mellanresultat redan är en rektangulär matris.

Skapa ett komplett tillgångsuppslag med REDUCE och VSTACK

1. Ange syntetiska källdata

Börja med ett tomt kalkylblad. Klistra in det här tabbavgränsade blocket i A1. Antalen är exempel på antal konsttillgångar.

Grupp\tTillgång\tAntal
Mosaic\tTile Amber\t2
Orbit\tRing Silver\t3
Mosaic\tTile Blue\t4
Prism\tPanel Clear\t5
Mosaic\tTile Coral\t1
Prism\tPanel Frost\t2

Klistra in följande begärandelista i E1. Behåll ordningen som visas så att du kan jämföra med det förväntade resultatet.

Begärd grupp
Mosaic
Orbit
Pebble

Exemplet innehåller avsiktligt tre utfall: Mosaic har tre matchande poster, Orbit har en och Pebble har ingen. Prism-posterna ska inte ingå i resultatet eftersom gruppen inte begärdes.

2. Kontrollera ett uppslag separat

Ange den här formeln i ett oanvänt område, till exempel L1:

=FILTER($A$2:$C$7,$A$2:$A$7=$E$2,"No match")

Den bör returnera de tre Mosaic-posterna. FILTER använder gruppjämförelsen för att välja hela rader från källan. Dess inkluderingsmatris måste ha kompatibel höjd eller bredd; här omfattar både källan och villkoret raderna 2:7. Se Funktionen FILTER.

Den här lilla kontrollen isolerar uppslaget från staplingslogiken. Om raderna är fel korrigerar du källans etiketter eller referenser innan du lägger till den yttre beräkningen.

3. Ange den fullständiga staplingsformeln

Lämna plats i kolumnerna G:I och ange sedan den här formeln i G1:

=REDUCE(
  $A$1:$C$1,
  $E$2:$E$4,
  LAMBDA(acc,key,
    VSTACK(
      acc,
      FILTER(
        $A$2:$C$7,
        $A$2:$A$7=key,
        HSTACK(key,"No match","")
      )
    )
  )
)

Tryck på Retur. Ange formeln en gång i den översta vänstra resultatcellen; Excel anpassar spillområdet automatiskt. Placera den utanför en Excel-tabell, eftersom spillande matrisformler inte stöds i tabeller. Microsoft förklarar båda beteendena i Dynamiska matrisformler och spillbeteende.

4. Jämför med det förväntade resultatet

Med de angivna data fyller resultatet området G1:I6:

Grupp Tillgång Antal
Mosaic Tile Amber 2
Mosaic Tile Blue 4
Mosaic Tile Coral 1
Orbit Ring Silver 3
Pebble Ingen träff

De förväntade måtten följer direkt av exemplet: en rubrikrad, tre Mosaic-rader, en Orbit-rad och en rad för den obegärda gruppen utan träffar. De fyra matchande posterna har antal som totalt blir 10. Det tomma antalet bredvid Pebble skiljer ett saknat uppslagsresultat från ett tillgångsantal på noll.

Så sätter formeln ihop resultatet

REDUCE börjar med A1:C1 som ackumulator. För varje värde i E2:E4 innehåller key den aktuella begärda gruppen och acc resultatet som hittills har satts ihop. Microsoft beskriver mönstret med startvärde, ackumulator och aktuellt värde i Funktionen REDUCE.

I den här formeln skapar FILTER nästa matchande block och VSTACK lägger det under acc. Genom att använda den faktiska rubriken som startvärde får resultatet tre kolumner direkt, utan en överflödig inledande tom rad. VSTACK staplar matriser vertikalt i följd, vilket beskrivs i Funktionen VSTACK.

När det inte finns några träffar ger det tredje argumentet i FILTER värdet HSTACK(key,"No match",""). Det skapar en reservrad med tre kolumner: begärd grupp, förklarande etikett och tomt antal. Microsoft beskriver argumentet för tomma resultat i Funktionen FILTER och horisontell sammanfogning i Funktionen HSTACK.

Det är viktigt att reservraden har tre kolumner. Microsoft dokumenterar att VSTACK fyller ut saknade kolumner med #N/A när indatamatriserna har olika bredd. En reservrad med en enda cell skulle därför få oönskade utfyllnadsceller bredvid sig. Se Funktionen VSTACK.

Välj hur resultatet ska se ut innan du utökar kalkylbladet

Formeln följer en bestämd princip: behandla alla begäranden i ordning, behåll alla matchande källrader och visa en markörrad för grupper utan träffar. Om en grupp upprepas i begärandelistan upprepas alltså dess matchande block. Om en källpost upprepas behålls även den; formeln tar inte bort dubbletter.

Använd den här beslutshjälpen när du anpassar exemplet:

Önskat resultat Lämplig metod Villkor
Ett antal för varje grupp MAP med COUNTIF Varje begäran ger ett enda värde
Alla tillgångsposter i begärandenas ordning Den fullständiga formeln med REDUCE ovan Kräver de dokumenterade funktionerna i Microsoft 365 för Mac
Några få uttryckligen angivna uppslagsblock Direkt VSTACK av separata FILTER-anrop Lägg till ett formelargument för varje begäran
Nästlade matriser kvar i celler Microsofts förhandsfunktion för nästlade matriser Kräver den dokumenterade tillgängligheten i Beta och arbetsboksinställningarna

Ange den här fullständiga formeln i K6 för en kompakt kontroll av antalen. Lämna tre celler under den lediga:

=MAP($E$2:$E$4,LAMBDA(key,COUNTIF($A$2:$A$7,key)))

De förväntade antalen är 3, 1 och 0. Det här är ett lämpligt användningsområde för MAP eftersom varje begäran ger ett tal. COUNTIF räknar celler som uppfyller ett villkor; exemplet använder enkla syntetiska etiketter. Se Funktionen MAP och Använd funktionen COUNTIF i Microsoft Excel.

Ett alternativ med fasta begäranden för Excel 2024 för Mac

Om din utgåva har FILTER, HSTACK och VSTACK, men saknar REDUCE, använder du det här fullständiga alternativet i G1 och ersätter den föregående formeln. Det ger samma förväntade resultat för de tre angivna begärandena:

=VSTACK(
  $A$1:$C$1,
  FILTER($A$2:$C$7,$A$2:$A$7=$E$2,HSTACK($E$2,"No match","")),
  FILTER($A$2:$C$7,$A$2:$A$7=$E$3,HSTACK($E$3,"No match","")),
  FILTER($A$2:$C$7,$A$2:$A$7=$E$4,HSTACK($E$4,"No match",""))
)

Microsoft anger Excel 2024 för Mac i dokumentationen för alla tre nödvändiga funktionerna: FILTER, HSTACK och VSTACK. Kompromissen gäller underhåll: varje ny begärd grupp kräver ytterligare ett argument till FILTER.

Felsök fel och se till att resultatet är komplett

Vid felet #SPILL! markerar du formelcellen och granskar det avsedda utdataområdet. Flytta befintliga poster därifrån och kontrollera om det finns sammanfogade celler. Microsoft anger också formler i tabeller som en orsak; placera utdata i det vanliga kalkylbladsområdet. Se Så här åtgärdar du ett #SPILL!-fel.

Vid #CALC! börjar du med att ta reda på vilken beräkning som gav felet. En formel med BYROW som returnerar ett block med flera celler når den dokumenterade gränsen för ett enda värde. En fristående FILTER utan träffar och utan reservvärde kan i stället ge ett fel för tom matris. Det är skilda orsaker som beskrivs i Microsofts vägledning om #CALC!.

När du lägger till källposter utökar du både $A$2:$C$7 och $A$2:$A$7 till samma slutrad. När du lägger till begäranden i versionen med REDUCE utökar du $E$2:$E$4 till den sista begäran som ska ingå. Ta inte med oanvända begärandeceller: formeln behandlar varje cell i området.

Om källistan växer regelbundet kan du lagra källan i en Excel-tabell och ersätta de fasta källreferenserna med strukturerade referenser. Microsoft dokumenterar att strukturerade referenser justeras när tabellrader läggs till eller tas bort. Behåll spillresultatet utanför tabellen. Se Dynamiska matrisformler och spillbeteende.

Innan du använder den sammanställda tillgångslistan jämför du gruppblocken med begärandelistan och kontrollerar markörerna för grupper utan träffar. I det här exemplet är den konkreta kontrollen enkel: tre Mosaic-poster, en Orbit-post, en synlig markör för Pebble och inga Prism-poster. Det bekräftar att urvalet och ordningen är rätt för de angivna data.

Källor