Tel overeenkomende numerieke matrices op met + om bijbehorende voorraadgegevens in Excel voor Mac te combineren. Als de activaregels in een andere volgorde staan, zoek je eerst elk activum op en tel je daarna de gevonden hoeveelheden bij elkaar op. Zo blijven de resultaten voor elke maat afzonderlijk behouden.

In deze handleiding gebruiken we synthetische kunstobjecten met twee maten: afdrukken en kleurstaalkaarten. De taak is om de hoeveelheden van twee opslaglocaties te combineren en elk activum en elke maat herkenbaar te houden. Alle hoeveelheden zijn ter illustratie.

Kies de formule op basis van de overeenkomst tussen de activa

Bepaal voordat je een formule schrijft waarop je twee waarden met elkaar vergelijkt. Posities komen alleen overeen als beide invoerblokken dezelfde volgorde van activa en maten hebben. Als je activalabels vergelijkt, mogen de bronrijen in een andere volgorde staan.

Voorwaarde voor de invoer Ondersteunde methode Resultaat
Dezelfde activa, dezelfde rijvolgorde en dezelfde maatkolommen Tel even grote numerieke bereiken op Eén resultaat per activum en maat
Unieke activalabels, andere rijvolgorde Tel twee resultaten van XLOOKUP met meerdere kolommen op De maten van één activum in afzonderlijke cellen
Herhaalde activalabels die moeten worden samengevoegd Gebruik voor elke maat een afzonderlijke berekening met SUMIF Eén resultaat per gevraagd activum en maat

De voorbeelden hieronder leiden deze methoden af uit de door Microsoft gedocumenteerde matrixberekeningen, zoekresultaten met meerdere items en voorwaardelijke sommatie. Kies de methode die bij je invoerstructuur past voordat je een formule kopieert.

Controleer welke editie van Excel je hebt

Het zoekvoorbeeld werkt in Excel voor Microsoft 365 voor Mac, Excel 2024 voor Mac en Excel 2021 voor Mac. Microsoft vermeldt uitdrukkelijk dat XLOOKUP niet beschikbaar is in Excel 2016 en Excel 2019, hoewel die edities wel voorkomen in de algemene lijst met ondersteunde versies op de documentatiepagina. De XLOOKUP-documentatie van Microsoft.

Op een Mac vind je in het applicatiemenu van een Office-app het dialoogvenster Over, waarin de versie en het licentietype staan. Kies in Excel Excel → Over Excel. Controleer zowel de producteditie als het versienummer voordat je een formule kiest. Over Office: welke versie van Office gebruik ik?.

De optionele formule HSTACK verderop in dit artikel vereist Excel voor Microsoft 365 voor Mac of Excel 2024 voor Mac; Microsoft vermeldt Excel 2021 voor Mac niet voor deze functie. Voor de hoofdberekeningen is deze functie niet nodig. De HSTACK-documentatie van Microsoft.

Stel twee kleine inventarissen van kunstobjecten op

Gebruik een leeg werkblad met gewone celbereiken. Voer de voorraad van de eerste locatie in A1:C4 in:

Activum Afdrukken Kleurstaalkaarten
Tile-A 8 3
Tile-B 5 7
Tile-C 6 2

Voer de voorraad van de tweede locatie in E1:G4 in en laat kolom D leeg:

Activum Afdrukken Kleurstaalkaarten
Tile-A 4 2
Tile-B 9 1
Tile-C 3 5

Beide numerieke blokken zijn drie rijen hoog en twee kolommen breed. In de eerste numerieke kolom staat het aantal afdrukken; in de tweede het aantal kleurstaalkaarten. De labels staan buiten de rekenbereiken.

Maak in J1:L4 de onderstaande uitvoerruimte. Voer voorlopig alleen de kopteksten en activalabels in; laat de cellen met hoeveelheden leeg.

Activum Totaal aantal afdrukken Totaal aantal kleurstaalkaarten
Tile-A
Tile-B
Tile-C

In deze voorbeelden is elke hoeveelheid een getal en komt elk activum in elke bron één keer voor. Een ontbrekende registratie heeft een andere betekenis dan een geregistreerde nul: gebruik nul als bekend is dat de hoeveelheid nul is, en controleer een ontbrekende registratie voordat je die als nul behandelt.

Tel overeenkomende matrices op om elk bijbehorend resultaat te behouden

Voer met de bronrijen in de getoonde volgorde deze volledige werkbladformule in K2 in:

=B2:C4+F2:G4

Bevestig de formule met Return. In Excel met dynamische matrices beslaat de uitvoer K2:L4: drie activarijen en twee maatkolommen. Microsoft documenteert dat matrixformules meerdere resultaten kunnen opleveren en dat dynamische resultaten naar aangrenzende cellen uitvloeien. Richtlijnen en voorbeelden van matrixformules.

De verwachte uitvoer is:

Activum Totaal aantal afdrukken Totaal aantal kleurstaalkaarten
Tile-A 12 5
Tile-B 14 8
Tile-C 9 7

De berekening volgt de posities: B2+F2 levert 12 op, terwijl C2+G2 5 oplevert. Voor elke resterende positie geldt dezelfde regel. De twee bronblokken vormen samen één resultaatblok met dezelfde afmetingen.

Voor een voorraad met één kolom zou de equivalente formule =B2:B4+F2:F4 zijn. Die levert drie hoeveelheden op, één per rij. De versie met twee kolommen behoudt naast de eerste ook een tweede maat.

Waarom een buitenste SOM de verkeerde vorm oplevert

Deze formules berekenen een eindtotaal:

=SUM(B2:C4,F2:G4)

=SUM(B2:C4+F2:G4)

Beide leveren voor dit voorbeeld 55 op. Dat getal combineert alle drie de activa en beide maten. Microsoft documenteert dat SUM de opgegeven waarden als getallen, verwijzingen of bereiken optelt. Als je de resulterende matrix in SUM verpakt, wordt die daarom tot één waarde teruggebracht. De SUM-documentatie van Microsoft.

Gebruik de bovenstaande uitvoer van zes cellen als controle. Eén cel met daarin 55 beantwoordt een andere vraag, ook al klopt de berekening.

Een versie-onafhankelijke indeling voor uitgelijnde rijen

Als je liever afzonderlijke celformules gebruikt, voer je =B2+F2 in K2 in. Sleep de vulgreep naar rechts tot L2 en vul vervolgens K2:L2 omlaag tot en met rij 4. Relatieve verwijzingen worden aangepast om elk overeenkomend paar te berekenen. Microsoft documenteert dit gedrag van de vulgreep voor Excel voor Mac. Excel gebruiken als rekenmachine in Excel voor Mac.

Zo krijg je dezelfde zes hoeveelheden zonder dat resultaten naar aangrenzende cellen uitvloeien. De bronrijen moeten hiervoor nog steeds in dezelfde volgorde staan.

Vergelijk activalabels als de bronrijen in een andere volgorde staan

Als je niet op rijposities kunt vertrouwen, wis je eerst het vorige resultaat uit K2. Laat J2:J4 staan als de lijst met gewenste activa. Voer de volgende formule in K2 in:

=XLOOKUP($J2,$A$2:$A$4,$B$2:$C$4)+XLOOKUP($J2,$E$2:$E$4,$F$2:$G$4)

Elke zoekopdracht vindt het activum in J2 en retourneert de twee kolommen met hoeveelheden. Door de geretourneerde rijen op te tellen, krijg je 12 in K2 en 5 in L2. Microsoft documenteert zowel dat er standaard exact wordt gezocht als dat er meerdere items kunnen worden geretourneerd uit een retourbereik met meerdere kolommen. De XLOOKUP-documentatie van Microsoft.

Kopieer de formule van K2 naar K3:K4 en laat L2:L4 leeg voor de horizontale resultaten. De dollartekens houden de bronbereiken vast; het gewenste activum verandert met elke uitvoerrij.

Verplaats als diagnostische controle de volledige records van de tweede bron, zodat Tile-C vooraan komt te staan. Verplaats elk label samen met beide hoeveelheden. De uitvoer op basis van zoekopdrachten hoort de verwachte waarden te behouden, terwijl de optelling op basis van posities dan verschillende activa aan elkaar zou koppelen.

Een ontbrekend label levert #N/A op, omdat de formule geen optionele vervangende waarde voor een niet-overeenkomende zoekopdracht bevat. Bij een herhaald label wordt de eerste overeenkomst geretourneerd in plaats van de records samen te voegen. Dit zijn gedocumenteerde beperkingen van de zoekfunctie. De XLOOKUP-documentatie van Microsoft.

De uitvoer bevat alleen de activa die in J2:J4 staan. Controleer voordat je eigen gegevens gebruikt of deze lijst alle activa bevat die je van een van beide locaties wilt rapporteren.

Combineer herhaalde activaregistraties per maat

Als meerdere rijen opzettelijk bij hetzelfde activum horen, gebruik je voorwaardelijke sommatie. Voer met de oorspronkelijke voorbeeldbereiken deze volledige formule in K2 in:

=SUMIF($A$2:$A$4,$J2,B$2:B$4)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Kopieer de formule naar rechts, naar L2, en kopieer vervolgens K2:L2 omlaag tot en met rij 4. Als je de formule naar rechts verplaatst, veranderen de hoeveelheidsbereiken in de kleurstaalkolommen, terwijl de labelbereiken vast blijven staan. Elke uitvoercel telt alleen het bijbehorende activum en de bijbehorende maat op. Microsoft documenteert dat je het ene bereik voor criteria gebruikt en het andere voor de bijbehorende hoeveelheden. De SUMIF-documentatie van Microsoft.

Voeg Tile-A, 2 en 1 toe in A5:C5 om herhaalde registraties te demonstreren. Breid voordat je opnieuw berekent de criterium- en hoeveelheidsbereiken van de eerste bron uit tot en met rij 5:

=SUMIF($A$2:$A$5,$J2,B$2:B$5)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Kopieer de aangepaste formule zoals eerder naar rechts en omlaag. De illustratieve uitvoer voor Tile-A wordt dan 14 afdrukken en 6 kleurstaalkaarten. Een vast bereik dat eindigt bij rij 4 zou de toegevoegde registratie overslaan. Bij deze methode moeten de bereiken dus worden bijgewerkt.

Houd elk criteriumbereik even groot en met dezelfde vorm als het bijbehorende hoeveelheidsbereik. Houd er ook rekening mee dat SUMIF * en ? als jokertekens behandelt; de synthetische labels hier bevatten die tekens niet. De SUMIF-documentatie van Microsoft.

Los matrixfouten op en controleer de voorraad

Een formule waarvan de resultaten naar aangrenzende cellen uitvloeien, heeft ruimte nodig voor de volledige uitvoer. Plaats de formule buiten een Excel-tabel en bewerk of vervang de formule via de cel linksboven. Microsoft documenteert beide beperkingen. Dynamische matrixformules en het gedrag van uitvloeiende matrixresultaten.

Als #SPILL! verschijnt, controleer dan of er al inhoud staat in het beoogde uitvoergebied en of er samengevoegde cellen zijn. Verplaats blokkerende inhoud of kies een leeg uitvoergebied. Microsoft noemt geblokkeerde bestemmingen en samengevoegde cellen als oorzaken van spill-fouten. Een #SPILL!-fout oplossen.

Controleer op tekstwaarden bij de hoeveelheden als optellen #VALUE! oplevert. Microsoft vermeldt dat directe optelling kan mislukken bij niet-numerieke tekst, terwijl SUM tekst negeert. Als je de formule vervangt door SUM, veranderen daarom zowel de vorm van het resultaat als de manier waarop tekst wordt verwerkt. De SUM-documentatie van Microsoft.

Gebruik voor een optioneel gelabeld resultaatblok in Microsoft 365 voor Mac of Excel 2024 voor Mac het oorspronkelijke uitgelijnde voorbeeld in een nieuw, leeg gebied:

=HSTACK(A2:A4,B2:C4+F2:G4)

Zo komen de activalabels naast de berekende maten te staan. HSTACK voegt kolommen toe; de + in de formule voert de optelling uit. De formule vergelijkt geen labels en corrigeert geen verschillen in bronrijvolgorde. De HSTACK-documentatie van Microsoft.

Controleer tot slot drie dingen: de uitvoer bevat elk gewenst activum, elke maat staat in een eigen kolom en één handmatig gecontroleerde rij komt overeen met de bronhoeveelheden. Tel bereiken op voor uitgelijnde invoer. Tel zoekresultaten op voor unieke labels waarvan de rijvolgorde kan veranderen. Tel bij herhaalde registraties de maten van elk activum afzonderlijk op. De uiteindelijke voorraad moet voldoende details bevatten om te zien wat elke hoeveelheid telt.

Bronnen