Als je een matrix met aantallen art-assets wilt omzetten naar één werkbladrij per item, vermeld je eerst elke combinatie van asset en formaat met een cumulatief aantal. Gebruik vervolgens SEQUENCE en XLOOKUP om de labels het vereiste aantal keren te herhalen. In deze handleiding worden fictieve labels voor illustraties gebruikt.

De taak is eenvoudig: zet een compact planningsraster om in een itemlijst. Een aantal van twee levert twee rijen met dezelfde asset en hetzelfde formaat op; nul levert niets op. In het uitgewerkte voorbeeld hieronder blijven de tussentijdse berekeningen zichtbaar, zodat je het resultaat kunt controleren voordat je het aanpast.

Welke Excel-versies voor Mac ondersteunen deze methode?

Gebruik Excel voor Microsoft 365 voor Mac, Excel 2024 voor Mac of Excel 2021 voor Mac. In de documentatie van Microsoft worden deze edities vermeld voor zowel SEQUENCE als XLOOKUP. Ook wordt in de functiegids voor Excel 2021 voor Mac expliciet melding gemaakt van dynamische matrices. SEQUENCE-functie, XLOOKUP-functie, Nieuw in Excel 2021 voor Mac.

De beschikbaarheid van functies is van belang wanneer je formules uit andere handleidingen kopieert. Microsoft vermeldt bijvoorbeeld VSTACK voor Microsoft 365 en Excel 2024 op de Mac, maar niet voor Excel 2021. Deze methode is niet afhankelijk van die functie. Microsoft vermeldt ook expliciet dat XLOOKUP niet beschikbaar is in Excel 2016 en Excel 2019. Die edities kunnen de onderstaande formules dus niet ongewijzigd uitvoeren. VSTACK-functie, XLOOKUP-functie.

De matrix met aantallen art-assets instellen

Voer de volgende fictieve gegevens in A1:C4 in op een leeg werkblad. De eerste kolom bevat assetlabels, de koptekstrij bevat formaten en de overige cellen bevatten aantallen.

Asset Klein Groot
Arc Tile 2 0
Dot Tile 1 0
Wave Tile 2 1

In dit voorbeeld moet elk aantal een numeriek geheel getal van nul of hoger zijn. Voer expliciet een nul in als er geen item nodig is. Los lege cellen, aantallen als tekst, negatieve waarden, breuken en cel fouten op voordat je doorgaat. Deze formules gaan uit van geldige aantallen; ze valideren of ronden die niet af.

De verwachte uitvoer bevat zes itemrijen: twee kleine Arc Tiles, één kleine Dot Tile, twee kleine Wave Tiles en één grote Wave Tile. De labels worden bewust herhaald, omdat elke rij één item voorstelt. De formules maken werkbladrecords; ze maken of dupliceren geen bestanden met illustraties.

De matrix omzetten naar een controleerbare hulplijst

Laat kolom D leeg. Voer in E1:I1 de kopteksten Positie, Asset, Formaat, Aantal en Einditem in. Voer de getallen één tot en met zes in E2:E7 in, op volgorde. Er zijn zes hulprijen, omdat drie assets vermenigvuldigd met twee formaten zes combinaties oplevert.

De hulplijst leest elke bronrij van links naar rechts voordat deze verdergaat naar de volgende rij: Arc Tile Small, Arc Tile Large, vervolgens de twee combinaties voor Dot Tile en daarna de twee combinaties voor Wave Tile. Het is essentieel dat deze volgorde consistent blijft.

De labels en het aantal voor elke combinatie ophalen

Voer deze formule in F2 in:

=INDEX($A$2:$A$4,INT((E2-1)/2)+1)

Voer deze formule in G2 in:

=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)

Voer deze formule in H2 in:

=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)

Kopieer F2:H2 omlaag tot en met rij zeven. Dit zijn gewone celformules, met één resultaat per cel. Met INDEX wordt een waarde op een opgegeven rij en kolom opgehaald. Hier zorgt INT ervoor dat de bronrij na elke twee posities opschuift, terwijl MOD de bronkolom afwisselend op de twee formaten instelt. INDEX-functie, INT-functie, MOD-functie.

Het laatste itemnummer voor elke combinatie berekenen

Voer de volgende formule in I2 in en kopieer deze omlaag tot en met I7:

=SUM($H$2:H2)

De vaste beginverwijzing en de veranderende eindverwijzing vormen een lopend totaal. Met SUM worden de aantallen opgeteld vanaf de eerste hulprij tot en met de huidige rij. SOM-functie.

De voltooide hulplijst ziet er als volgt uit:

Positie Asset Formaat Aantal Einditem
1 Arc Tile Klein 2 2
2 Arc Tile Groot 0 2
3 Dot Tile Klein 1 3
4 Dot Tile Groot 0 3
5 Wave Tile Klein 2 5
6 Wave Tile Groot 1 6

Zie Einditem als een grens. De eerste combinatie beslaat itemnummers één en twee. De derde combinatie beslaat itemnummer drie. De vijfde beslaat vier en vijf, en de zesde beslaat zes. Een aantal van nul verschuift de grens niet, dus krijgt die combinatie geen itemnummers.

Eén uitvoerrij per item genereren

Voer in K1 Asset in en in L1 Formaat. Laat de cellen onder beide kopteksten leeg. Plaats de uitvoer in gewone werkbladcellen buiten een Excel-tabel: Microsoft vermeldt dat matrixformules die resultaten laten doorlopen, niet worden ondersteund in tabellen. Dynamische matrixformules en het gedrag van doorlopende matrixresultaten.

Voer deze volledige formule in K2 in:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))

Voer deze volledige formule in L2 in:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))

Bevestig elke formule met Return. Kopieer deze twee formules niet omlaag: de matrixresultaten vullen de aangrenzende rijen automatisch. Microsoft beschrijft deze automatische uitbreiding als doorlopen. Dynamische matrixformules en het gedrag van doorlopende matrixresultaten.

Met SEQUENCE worden de itemnummers gegenereerd van één tot en met het totale aantal. Voor elk nummer zoekt XLOOKUP in de cumulatieve grenzen. De laatste twee argumenten geven aan dat er exact of naar de eerstvolgende grotere waarde moet worden gezocht, van de eerste naar de laatste waarde. Door die zoekrichting blijft de eerste overeenkomende grens behouden wanneer nullen herhaalde totalen opleveren. SEQUENCE-functie, XLOOKUP-functie.

De buitenste IF geeft lege tekst als het totaal nul is. In dat geval lijken de twee anker cellen leeg en zijn er geen itemrecords om te gebruiken. Dit is een bewuste afhandeling van een totaal van nul, waarbij IF tussen twee resultaten kiest. ALS-functie.

Voor de opgegeven matrix moet K1:L7 het volgende bevatten:

Asset Formaat
Arc Tile Klein
Arc Tile Klein
Dot Tile Klein
Wave Tile Klein
Wave Tile Klein
Wave Tile Groot

Beide uitvoerkolommen gebruiken dezelfde gegenereerde itemnummers en grensreeks. Alleen het retourbereik verandert: kolom F levert assetlabels en kolom G levert formaten. Houd deze opzoekgegevens gelijk wanneer je de formules bewerkt.

Het resultaat controleren voordat je de bron uitbreidt

Voer met de voorbeeldgegevens drie controles uit:

  1. Vergelijk de totalen: =SUM(B2:C4) en =SUM(H2:H7) moeten allebei zes opleveren.
  2. Vergelijk de combinaties: de uitvoer moet de vier combinaties met een niet-nul aantal bevatten, met het opgegeven aantal herhalingen.
  3. Wijzig B2 van twee naar drie: de verwachte uitvoer wordt dan zeven rijen, met drie vermeldingen van Arc Tile Small. Zet de waarde daarna weer terug op twee.

Met de eerste controle spoor je aantallen op die bij het omzetten naar de hulplijst zijn weggelaten of dubbel geteld. Met de tweede spoor je labels op die niet overeenkomen, iets wat je niet aan alleen het totaal kunt zien. Met de derde controleer je of wijzigingen via de hulplijst in de uitvoer terechtkomen.

Meer assets of formaten op een doordachte manier toevoegen

Als je aantallen binnen B2:C4 wijzigt, blijft de bestaande opzet werken. Als je de afmetingen van de matrix vergroot, moet je de verwijzingen en de lengte van de hulplijst aanpassen.

Breid voor een volledig voorbeeld de bron uit naar vier assets en drie formaten in A1:D5. Verplaats het hulpgebied of laat het op E:I staan, voer posities één tot en met twaalf in E2:E13 in en vervang de formules in F2:H2 door:

=INDEX($A$2:$A$5,INT((E2-1)/3)+1)

=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)

=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)

Kopieer deze formules tot en met rij dertien en breid de formule voor het lopende totaal in kolom I uit tot dezelfde rij. Vervang in beide uitvoerformules elk hulpbereik dat eindigt op rij zeven door een bereik dat eindigt op rij dertien. De deler en de basis voor de rest zijn nu drie, omdat elke asset drie formaten heeft. Controleer na deze wijzigingen het brontotaal en de afzonderlijke combinaties opnieuw.

Onvolledige of geblokkeerde uitvoer oplossen

Als Excel #SPILL! weergeeft, controleer dan of het beoogde uitvoergebied bestaande invoer of samengevoegde cellen bevat en bevestig dat de formule buiten een Excel-tabel staat. Een resultaat dat buiten de rand van het werkblad valt, kan ook niet doorlopen. Microsoft documenteert elk van deze situaties. Een #SPILL!-fout oplossen.

Als de uitvoer te weinig rijen bevat, controleer dan eerst de aantallen voordat je de opzoeklogica wijzigt. Microsoft vermeldt dat SUM tekst in de opgegeven bereiken negeert. Een aantal dat als tekst is opgeslagen, kan daardoor buiten het totaal blijven. Corrigeer de bronwaarde en voer de totaalcontrole opnieuw uit. SOM-functie.

Als labels onjuist zijn terwijl het totaal klopt, vergelijk de hulplijst dan cel voor cel met de matrix. Houd de asset, het formaat, het aantal en de cumulatieve grens van elke hulprij bij elkaar. Rangschik hulpkolommen niet afzonderlijk opnieuw.

Zodra de totalen en combinaties overeenkomen, gebruik je de twee uitvoerkolommen als itemlijst. Houd de matrix en de hulpberekeningen beschikbaar, zodat latere wijzigingen in aantallen te volgen zijn in de herhaalde rijen.

Bronnen