Gebruik REDUCE met VSTACK om opzoekresultaten met een variabele grootte in Excel voor Mac te combineren tot één rechthoekige matrix. In deze uitleg maak je een lijst met art-assets, behoud je de volgorde van de aanvragen en worden niet-overeenkomende groepen weergegeven in plaats van ze stilzwijgend weg te laten.

De taak is eenvoudig: elke aangevraagde assetgroep kan overeenkomen met meerdere bronrijen, één rij of geen enkele rij. Je wilt al die resultaten in één uitbreidend werkbladbereik, waarbij assetlabels en aantallen bij elkaar blijven.

Welke versies van Excel voor Mac ondersteunen deze aanpak?

Gebruik Excel voor Microsoft 365 voor Mac voor de hoofdformule hieronder. Microsoft vermeldt deze editie expliciet op de documentatiepagina van de functie REDUCE. Op de huidige pagina wordt Excel 2024 voor Mac niet vermeld, dus de aanwezigheid van andere moderne matrixfuncties bewijst op zichzelf niet dat de volledige formule beschikbaar is.

Functie Mac-edities vermeld in de relevante Microsoft-documentatie Rol hier
BYROW, MAP Microsoft 365 voor Mac; Excel 2024 voor Mac De oorspronkelijke aanpak en de beperkingen ervan toelichten
REDUCE Microsoft 365 voor Mac Het groeiende resultaat tussen aanvragen bijhouden
VSTACK, HSTACK Microsoft 365 voor Mac; Excel 2024 voor Mac Resultaatblokken samenvoegen en een terugvalrij maken
FILTER Microsoft 365 voor Mac; Excel 2024 voor Mac; Excel 2021 voor Mac Alle overeenkomende assetrijen retourneren

Deze editieoverzichten zijn afkomstig van de referentiepagina's van Microsoft voor BYROW, MAP, VSTACK, HSTACK en FILTER.

Open het menu Excel en kies Over Excel om je installatie te controleren. In de Mac-instructies van Microsoft staat dat het dialoogvenster Over van een Office-app de versie en het licentietype toont. Controleer beide wanneer je bepaalt welke formule je kunt gebruiken. Zie Over Office: welke versie van Office gebruik ik?.

Een nieuwere preview in de Mac Beta verandert de grens voor geneste matrices

In de aankondiging van 24 september 2026 introduceert Microsoft geneste matrices en FLATTEN als previewfuncties voor het Beta Channel. Hiervoor is Mac-versie 16.114 Build 26092111 of hoger vereist; beschikbaarheid is afhankelijk van de uitrol. Voor de meeste berekeningen met geneste matrices is werkmapcompatibiliteitsversie 3 nodig, die je selecteert via Formule > Berekeningsopties. Deze voorwaarden zijn van belang: alleen Microsoft 365 hebben bewijst niet dat de preview is ingeschakeld. Zie Meerdere waarden in één cel plaatsen met lijsten en matrices in Excel.

De uitgewerkte oplossing hieronder bouwt een gewone rechthoekige matrix zonder van die preview afhankelijk te zijn. Zo hangt de basistaak van de werkmap, het maken van een assetlijst, ook niet af van instellingen voor geneste matrices.

Waarom geeft het retourneren van matrices uit BYROW of MAP problemen?

Microsoft documenteert dat BYROW één resultaat per invoerrij oplevert. In de referentie staat expliciet dat een resultaat van de interne LAMBDA dat uit meer dan één waarde bestaat, een fout #CALC! oplevert. Een zoekactie die meerdere records retourneert, valt buiten deze gedocumenteerde beperking. Zie BYROW-functie.

Na het invoeren van de voorbeeldgegevens in het volgende gedeelte is dit bijvoorbeeld een diagnostisch voorbeeld van de problematische aanpak volgens het gedocumenteerde gedrag met één waarde:

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

De eerste aanvraag retourneert drie rijen en drie kolommen. Als je BYROW vraagt dat blok op één resultaatpositie te plaatsen, ontstaat een probleem met geneste matrices. De buitenste functie vervangen door MAP biedt volgens het gebruikelijke berekeningsgedrag geen algemene manier om resultaten te stapelen: het interne resultaat bevat nog steeds meerdere cellen. In de documentatie van MAP beschrijft Microsoft hoe invoerwaarden worden omgezet in nieuwe waarden; in de richtlijnen voor #CALC! wordt de beperking voor geneste matrices beschreven.

De mislukte berekening in VSTACK wikkelen kan het interne resultaat niet herstellen. De nuttige wijziging is om elk opzoekblok toe te voegen terwijl de aanvragen worden verwerkt, zodat elk tussenresultaat al één rechthoekige matrix vormt.

Een volledige assetlijst opbouwen met REDUCE en VSTACK

1. De synthetische brongegevens invoeren

Begin met een leeg werkblad. Plak dit door tabs gescheiden blok in A1. De aantallen zijn ter illustratie van assetaantallen.

Groep\tAsset\tAantal
Mozaïek\tAmberkleurige tegel\t2
Baan\tZilveren ring\t3
Mozaïek\Blauwe tegel\t4
Prisma\tHelder paneel\t5
Mozaïek\tKoraalkleurige tegel\t1
Prisma\tBerijpt paneel\t2

Plak de volgende lijst met aanvragen in E1. Behoud de weergegeven volgorde, zodat je het verwachte resultaat kunt vergelijken.

Aangevraagde groep
Mozaïek
Baan
Kiezel

Dit voorbeeld bevat bewust drie uitkomsten: Mozaïek heeft drie overeenkomende records, Baan één en Kiezel geen. De records van Prisma moeten buiten de uitvoer blijven, omdat die groep niet is aangevraagd.

2. Eén zoekactie afzonderlijk controleren

Voer deze formule in een ongebruikt gebied in, bijvoorbeeld L1:

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

De formule moet de drie records van Mozaïek retourneren. FILTER gebruikt de groepsvergelijking om volledige rijen uit de bron te selecteren. De matrix met insluitingswaarden moet een compatibele hoogte of breedte hebben; hier omvatten zowel de bron als de voorwaarde de rijen 2:7. Zie FILTER-functie.

Met deze kleine controle is de zoekactie gescheiden van de stapellogica. Als de rijen niet kloppen, corrigeer dan de bronlabels of verwijzingen voordat je de buitenste berekening toevoegt.

3. De volledige stapelformule invoeren

Laat ruimte in de kolommen G:I en voer deze formule vervolgens in G1 in:

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

Druk op Return. Voer de formule één keer in de cel linksboven van het uitvoergebied in; Excel past de grootte van het overloopbereik automatisch aan. Plaats de formule buiten een Excel-tabel, omdat matrixformules die overlopen niet in tabellen worden ondersteund. Microsoft licht beide gedragingen toe in Dynamische matrixformules en gedrag van overlopende matrices.

4. Het verwachte resultaat vergelijken

Met de opgegeven gegevens beslaat de uitvoer G1:I6:

Groep Asset Aantal
Mozaïek Amberkleurige tegel 2
Mozaïek Blauwe tegel 4
Mozaïek Koraalkleurige tegel 1
Baan Zilveren ring 3
Kiezel Geen overeenkomst

De verwachte afmetingen volgen rechtstreeks uit het voorbeeld: één koprij, drie rijen voor Mozaïek, één rij voor Baan en één rij voor de niet-overeenkomende aanvraag. De vier overeenkomende records hebben samen een aantal van 10. Het lege aantal naast Kiezel maakt duidelijk dat het zoekresultaat ontbreekt en niet dat het assetaantal nul is.

Hoe de formule het resultaat samenstelt

REDUCE begint met A1:C1 als accumulator. Voor elke waarde in E2:E4 bevat key de huidige aangevraagde groep en acc het tot dan toe samengestelde resultaat. Microsoft documenteert dit patroon met beginwaarde, accumulator en huidige waarde in REDUCE-functie.

In deze formule maakt FILTER het volgende overeenkomende blok en voegt VSTACK het onder acc toe. Door de daadwerkelijke koprij als beginwaarde te gebruiken, heeft het resultaat meteen drie kolommen en hoef je geen tijdelijke lege startrij toe te voegen. VSTACK voegt matrices achtereenvolgens verticaal samen, zoals beschreven in VSTACK-functie.

Als er geen overeenkomsten zijn, levert het derde argument van FILTER de waarde HSTACK(key,"No match",""). Zo ontstaat een terugvalrij met drie kolommen: aangevraagde groep, verklarend label en leeg aantal. Microsoft documenteert het argument voor lege resultaten in FILTER-functie en het horizontaal samenvoegen in HSTACK-functie.

Het is van belang dat deze terugvalrij drie kolommen breed is. Microsoft documenteert dat VSTACK ontbrekende kolommen opvult met #N/A als de invoermatrices verschillende breedtes hebben. Een terugvalwaarde in één cel zou daarom ongewenste opvulling ernaast opleveren. Zie VSTACK-functie.

Kies het uitvoergedrag voordat je het werkblad uitbreidt

De formule volgt een specifiek beleid: verwerk elke aanvraag op volgorde, behoud alle overeenkomende bronrijen en toon één markeringsrij voor een niet-overeenkomende groep. Als een groep meerdere keren in de aanvraaglijst voorkomt, wordt het bijbehorende blok dus herhaald. Ook dubbele bronrecords blijven behouden; deze formule verwijdert geen dubbele waarden.

Gebruik deze besliswijzer om het voorbeeld aan te passen:

Gewenst resultaat Geschikte aanpak Voorwaarde
Eén telling per groep MAP met COUNTIF Elke aanvraag levert één waarde op
Alle assetrecords in aanvraagvolgorde De volledige REDUCE-formule hierboven Vereist de gedocumenteerde Microsoft 365 voor Mac-functies
Enkele expliciet vermelde zoekblokken Rechtstreeks VSTACK van afzonderlijke aanroepen van FILTER Voeg voor elke aanvraag een formuleargument toe
Geneste matrices in cellen behouden Microsoft-preview voor geneste matrices Vereist de gedocumenteerde beschikbaarheid in Beta en werkmapinstellingen

Voer voor een beknopte telling deze volledige formule in K6 in en laat drie cellen eronder vrij:

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

De verwachte tellingen zijn 3, 1 en 0. Dit is een geschikt gebruik van MAP, omdat elke aanvraag één getal oplevert. COUNTIF telt cellen die aan een criterium voldoen; het voorbeeld gebruikt eenvoudige synthetische labels. Zie MAP-functie en De functie AANTAL.ALS gebruiken in Microsoft Excel.

Een alternatief met vaste aanvragen voor Excel 2024 voor Mac

Als je editie FILTER, HSTACK en VSTACK biedt, maar niet REDUCE, gebruik dan deze volledige alternatieve formule in G1 ter vervanging van de vorige formule. Voor de drie opgegeven aanvragen levert deze hetzelfde verwachte resultaat op:

=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 vermeldt Excel 2024 voor Mac in de documentatie van alle drie de vereiste functies: FILTER, HSTACK en VSTACK. Het nadeel is het onderhoud: voor een extra aangevraagde groep is nog een argument voor FILTER nodig.

Fouten opsporen en het resultaat volledig houden

Selecteer bij een fout #SPILL! de formulecel en controleer het beoogde uitvoergebied. Verplaats bestaande waarden uit dat gebied en controleer op samengevoegde cellen. Microsoft noemt ook formules in tabellen als mogelijke oorzaak; plaats de uitvoer in het gewone werkbladraster. Zie Een fout #SPILL! corrigeren.

Bepaal bij #CALC! eerst welke berekening de fout heeft veroorzaakt. Een formule met BYROW die een blok met meerdere cellen retourneert, bereikt de gedocumenteerde beperking tot één waarde. Een op zichzelf staande FILTER zonder overeenkomsten en zonder terugvalwaarde kan daarentegen een fout voor een lege matrix opleveren. Dit zijn afzonderlijke oorzaken die in de richtlijnen voor #CALC! van Microsoft worden beschreven.

Wanneer je bronrecords toevoegt, breid dan zowel $A$2:$C$7 als $A$2:$A$7 uit tot dezelfde laatste rij. Breid bij de versie met REDUCE ook $E$2:$E$4 uit tot de laatste bedoelde aanvraag. Neem geen ongebruikte aanvraagcellen op: de formule verwerkt elke cel in dat bereik.

Overweeg bij een bronlijst die regelmatig groeit de bron in een Excel-tabel op te slaan en de vaste bronverwijzingen te vervangen door gestructureerde verwijzingen. Microsoft documenteert dat gestructureerde verwijzingen worden aangepast wanneer tabelrijen worden toegevoegd of verwijderd. Houd het overlopende resultaat buiten de tabel. Zie Dynamische matrixformules en gedrag van overlopende matrices.

Voordat je de samengestelde assetlijst gebruikt, vergelijk je de groepsblokken met de aanvraaglijst en controleer je de markeringen voor niet-overeenkomende resultaten. De concrete controle in dit voorbeeld is eenvoudig: drie records van Mozaïek, één record van Baan, één zichtbare markering voor Kiezel en geen records van Prisma. Zo bevestig je dat de selectie en volgorde voor de opgegeven gegevens kloppen.

Bronnen