Combineer in Microsoft 365 voor Mac of Excel 2024 voor Mac CHOOSECOLS, TOCOL, HSTACK en FILTER om herhaalde labels van illustratie-assets en hoeveelheden om te zetten in inventarisrijen. De uitgewerkte formule hieronder behoudt de uitlijning van paren, hoeveelheden van nul en onvolledige invoer.

Deze handleiding is bedoeld voor een Mac-desktopgebruiker wiens werkblad afwisselend een kenmerkkolom en de bijbehorende hoeveelheidkolom bevat. Hier is elk kenmerk een label voor een illustratie-asset, zoals Circle Tile. De taak is om een inventaris met twee kolommen te maken en vervolgens hoeveelheden op te zoeken zonder ze van hun labels te scheiden.

Welke versies van Excel voor Mac ondersteunen deze formule?

Microsoft vermeldt expliciet Excel voor Microsoft 365 voor Mac en Excel 2024 voor Mac voor alle drie de vormgevingsfuncties: CHOOSECOLS, TOCOL en HSTACK. Gebruik een van die edities voor de hoofdformule.

Excel 2021 voor Mac bevat dynamische matrices, FILTER, LET en XLOOKUP, maar dat bevestigt niet dat de nieuwere vormgevingsfuncties worden ondersteund. In Microsofts documentatie over functies in Excel 2021 voor Mac staan de beschikbare matrix- en zoekmogelijkheden; de drie functiepagina's hierboven vermelden Excel 2021 niet.

Controleer je editie en versie in het dialoogvenster Over. In Microsofts handleiding voor Office-versies staat dat het Mac-dialoogvenster zowel de versie als het licentietype weergeeft. Als je editie de vormgevingsfuncties niet bevat, gebruik dan Microsoft 365 voor Mac of Excel 2024 voor Mac voor de automatische methode, of gebruik de hieronder beschreven handmatige stapelmethode.

Een volledig synthetisch voorbeeld instellen

Voer deze gegevens in een leeg werkblad in A1:F4 in. Laat de twee lege cellen in het midden van rij 3 echt leeg. Alle labels en hoeveelheden zijn ter illustratie.

Kenmerk 1 Hoeveelheid 1 Kenmerk 2 Hoeveelheid 2 Kenmerk 3 Hoeveelheid 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

Elk aangrenzend paar vormt één inventarisitem. De bedoelde uitvoer loopt per bronrij van links naar rechts en gaat daarna naar beneden. Circle Tile komt twee keer voor omdat het om afzonderlijke items gaat; herhaalde labels samenvoegen is een aparte taak.

Deze methode gaat ervan uit dat elk label direct wordt gevolgd door de bijbehorende hoeveelheid en dat alle geselecteerde kolommen deel uitmaken van dit herhalende patroon. Als je werkblad ook een collectienaam of een ander veld bevat dat bij elk item moet blijven staan, behoudt deze uitvoer met twee kolommen dat veld niet. Bepaal welke velden de inventaris nodig heeft voordat je de formule toepast.

De herhaalde paren omzetten naar inventarisrijen

Voer Asset in H1 en Quantity in I1 in. Houd het uitvoergebied onder deze koppen leeg. Plak deze volledige formule in H2 en druk op Return. Dynamische matrixformules voer je één keer in; ze worden uitgebreid naar aangrenzende cellen, zoals beschreven in Microsofts handleiding voor matrixformules.

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

Het verwachte resultaat beslaat H2:I9:

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

De volgorde van de paren in het voorbeeld, de acht uitvoeritems, het behoud van nul en de totale hoeveelheid van 28 zijn gecontroleerd met een kleine deterministische Python-berekening. De Excel-formule is vergeleken met de door Microsoft gedocumenteerde functiesyntaxis; de formule is niet in Excel uitgevoerd.

Hoe de formule de paren uitgelijnd houdt

LET geeft tussenresultaten namen, zodat de formule ze opnieuw kan gebruiken. De namen source, clean, labels, quantities en keep bestaan binnen deze formule; je hoeft geen benoemde bereiken te maken. Microsoft documenteert dit gedrag in de LET-verwijzing.

CHOOSECOLS selecteert de labelkolommen 1, 3 en 5 en de hoeveelheidkolommen 2, 4 en 6. Deze getallen zijn posities binnen het geselecteerde bronbereik. In Microsofts CHOOSECOLS-documentatie staat dat de functie de opgegeven kolommen uit een matrix retourneert.

Beide aanroepen van TOCOL gebruiken 0 om elke positie te behouden en FALSE om rijen van links naar rechts te doorlopen. Omdat ze dezelfde posities in dezelfde volgorde doorlopen, blijft elk label uitgelijnd met de bijbehorende hoeveelheid. Microsoft documenteert beide argumenten in de TOCOL-verwijzing.

HSTACK plaatst de twee resulterende kolommen naast elkaar. Beide bevatten negen posities vóór het filteren, dus hun lengtes komen overeen. Microsoft legt uit dat HSTACK matrices horizontaal samenvoegt en kortere matrices aanvult met fouten als hun aantal rijen verschilt.

Ten slotte selecteert keep posities met een label, een hoeveelheid of beide. FILTER verwijdert alleen volledig lege paren. Het laatste argument geeft een bericht op als er geen items overblijven, volgens de door Microsoft gedocumenteerde syntaxis van FILTER.

Lege waarden en nullen behouden zonder onvolledige items te verbergen

Een lege hoeveelheid en de numerieke waarde nul bevatten verschillende informatie. Nul is een ingevoerd aantal; een lege cel moet nog worden geïnterpreteerd. In Microsofts voorbeelden van TOCOL staat dat lege cellen zonder verdere verwerking nul kunnen opleveren. Daarom gebruikt de stap clean IF om lege tekst terug te geven voor lege broncellen voordat ze opnieuw worden vormgegeven. Microsoft documenteert voorwaardelijke retourwaarden in de IF-verwijzing.

De formule houdt onvolledige paren bewust zichtbaar. Voer deze controles uit voordat je op het resultaat vertrouwt:

Bronpaar Gedrag van de uitvoer Actie
Label en numerieke hoeveelheid Behoudt beide Controleer of de hoeveelheid klopt
Label en nul Behoudt nul Behoud nul als dit een bewust ingevoerd aantal is
Beide cellen leeg Verwijdert het paar Correctie niet nodig
Label met lege hoeveelheid Behoudt het label en de lege hoeveelheid Vul de hoeveelheid in
Leeg label met hoeveelheid Behoudt het lege label en de hoeveelheid Vul het label in

Verwijder niet afzonderlijk lege waarden uit de label- en hoeveelheidmatrices. Als op de ene positie een label ontbreekt en elders een hoeveelheid, kunnen de matrices door het afzonderlijk verwijderen van lege waarden verschuiven en onjuiste combinaties opleveren. Posities behouden tot na het samenvoegen is de belangrijkste ontwerpkeuze in deze formule.

Een cel die leeg lijkt maar spaties bevat, wordt door deze formule niet als leeg behandeld. Corrigeer zulke waarden in de bron. De formule behoudt ook hoeveelheden die als tekst zijn ingevoerd, zonder ze automatisch om te zetten. Controleer deze waarden voordat je totalen berekent.

Eén item opzoeken of alle overeenkomende items retourneren

Voer Circle Tile in K2 in. Voer deze formule in L2 in om de eerste hoeveelheid op te halen:

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

H2# verwijst naar het volledige overlopende resultaat dat begint bij H2, zodat de verwijzing de wijzigingen in de omvang ervan volgt. Microsoft legt deze notatie uit in de documentatie over de operator voor overlopende bereiken.

Het resultaat is 4. De expliciete overeenkomstmodus 0 vraagt om een exacte overeenkomst; de standaardzoekrichting retourneert de eerste overeenkomst. In Microsofts XLOOKUP-verwijzing worden beide gedragingen beschreven. Daarin staat ook dat XLOOKUP niet beschikbaar is in Excel 2016 en Excel 2019.

Als herhaalde labels betekenisvol zijn, volstaat het niet om alleen de eerste overeenkomst op te zoeken. Plaats deze volledige alternatieve formule in N2 om alle Circle Tile-items te retourneren; laat ruimte in de kolommen N:O:

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

Dit retourneert twee rijen: Circle Tile met 4 en Circle Tile met 7. FILTER retourneert rijen die voldoen aan de opgegeven voorwaarde, zoals beschreven in Microsofts FILTER-verwijzing. Gebruik de formule voor de eerste overeenkomst als één overeenkomend item antwoord geeft op je vraag; gebruik het gefilterde resultaat als je elke keer dat het item voorkomt wilt bekijken.

Het resultaat controleren en het bronbereik aanpassen

Controleer voor het voorbeeld of er acht uitvoerrijen zijn en of Dot Pattern nog steeds hoeveelheid nul heeft. Vergelijk vervolgens het totaal van de bronhoeveelheden met het totaal van de omgezette hoeveelheden. Voer deze formules in twee vrije cellen in:

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

=SUM(CHOOSECOLS(H2#,2))

Beide moeten 28 retourneren. In Microsofts SUM-documentatie staat dat meerdere bereiken kunnen worden opgeteld en dat tekstwaarden worden genegeerd. Overeenkomende totalen bewijzen daarom niet dat elke hoeveelheid numeriek is en kunnen niet detecteren of hoeveelheden aan de verkeerde labels zijn gekoppeld. Controleer ook de paren van labels en hoeveelheden.

Wijzig voor meer bronrijen A2:F4 in de hoofdformule in het werkelijke bereik, bijvoorbeeld A2:F100. Rijen buiten dat bereik worden niet meegenomen. Wijzig voor vier herhaalde paren de bron in acht kolommen, bijvoorbeeld A2:H100, en pas de labelposities aan naar 1,3,5,7 en de hoeveelheidposities naar 2,4,6,8.

Verplaats bij uitbreiding naar acht bronkolommen de uitvoer naar een lege locatie, bijvoorbeeld J2, en pas vervolgverwijzingen naar overlopende bereiken aan naar J2#. Houd bron en uitvoer gescheiden. Een ongeldige kolompositie veroorzaakt een fout, zoals beschreven bij CHOOSECOLS-fouten.

Geblokkeerde uitvoer oplossen of de handmatige stapelmethode gebruiken

Als de hoofdformule #SPILL! retourneert, selecteer dan de formulecel en inspecteer het beoogde uitvoergebied. Verplaats bestaande inhoud uit dat gebied en controleer op samengevoegde cellen. Plaats de formule buiten elke Excel-tabel: Microsoft documenteert in de handleiding voor het oplossen van #SPILL!-fouten dat overlopende matrixformules niet binnen tabellen of in samengevoegde cellen kunnen overlopen.

Als de bron een fout bevat, corrigeer dan die bronvermelding voordat je de inventaris gebruikt. De formule onderdrukt geen fouten; Microsoft merkt op dat een fout in de opneemmatrix van FILTER ervoor zorgt dat de functie een fout retourneert. De volledige omzetting met foutonderdrukking omhullen kan een item verbergen dat moet worden gecorrigeerd.

Voor een klein werkblad in Excel 2021 voor Mac kun je dezelfde structuur met twee kolommen handmatig maken: kopieer A2:B4 naar H2:I4, kopieer C2:D4 naar H5:I7 en kopieer E2:F4 naar H8:I10. Verwijder alleen rijen waarin beide cellen leeg zijn. Hiermee stapel je items per blok met paren in plaats van in de bronrijvolgorde van de hoofdformule, en moet je het werk herhalen nadat de bron is gewijzigd.

Na deze handmatige instelling kan Excel 2021 voor Mac de gedocumenteerde functie XLOOKUP gebruiken met vaste bereiken:

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

Een goede afgeronde inventaris behoudt elk betekenisvol paar, maakt onvolledige items zichtbaar en gebruikt een zoekformule die geschikt is voor herhaalde labels. Controleer deze eigenschappen eerst met het voorbeeld en breid daarna het bronbereik uit zodat het op je werkblad aansluit.

Bronnen