Om herhaalde assetlabels samen te voegen zonder rijen te vermenigvuldigen, nummert u de voorkomens van elk label in beide lijsten en zoekt u vervolgens op basis van het label en het voorkomnummer. In deze stapsgewijze uitleg voor Excel voor Mac blijft elke rij aan de linkerkant behouden en worden ontbrekende overeenkomsten gemarkeerd.

Wat betekent overeenkomsten zoeken op basis van voorkomen?

Stel dat twee lijsten met kunstobjecten meerdere rijen met het label Arc Tile bevatten. Het is de bedoeling dat u de eerste bronhoeveelheid koppelt aan de eerste Arc Tile aan de linkerkant, de tweede bronhoeveelheid aan de tweede, enzovoort. Tussen die rijen kunnen andere labels staan; het voorkomnummer wordt voor elk label afzonderlijk bijgehouden.

Een relatie op basis van alleen het label is niet eenduidig. Als de ene lijst drie rijen met Arc Tile bevat en de andere twee, levert het koppelen van elke combinatie van overeenkomende rijen zes combinaties voor dat label op. Een gewone zoekfunctie kent nog een beperking: volgens de documentatie van Microsoft retourneert XLOOKUP de eerste overeenkomst die wordt gevonden. Herhaald zoeken op alleen het label gaat dus niet verder naar volgende voorkomens. XLOOKUP-functie

De oplossing is om de zoeksleutel specifieker te maken: Arc Tile|1, Arc Tile|2 en Arc Tile|3. Elke genummerde sleutel duidt één voorkomen aan. Hieronder wordt de werkwijze uitgewerkt met gedocumenteerde Excel-functies; de documentatie van Microsoft beschrijft hoe de functies werken.

Gebruik deze aanpak wanneer de rijvolgorde bepaalt hoe de rijen worden gekoppeld. Als de rijen in plaats daarvan moeten overeenkomen op basis van een afzonderlijk asset-ID of revisieveld, gebruik dan die gegevens. Voorkomnummers kunnen niet bepalen welke twee records bij elkaar horen als de volgorde geen betekenis heeft.

Welke versies van Excel voor Mac ondersteunen deze methode?

De belangrijkste formules gebruiken COUNTIF, samenvoeging met & en XLOOKUP. Microsoft vermeldt Excel voor Microsoft 365 voor Mac, Excel 2024 voor Mac en Excel 2021 voor Mac op de relevante functiepagina’s. In de documentatie over XLOOKUP staat uitdrukkelijk dat deze functie niet beschikbaar is in Excel 2016 en Excel 2019. XLOOKUP-functie, De functie COUNTIF gebruiken in Microsoft Excel

Begin met de ingevulde zoekformule hieronder. Een latere variant retourneert dezelfde zeven resultaten met één matrixformule die de resultaten laat overlopen. Microsoft vermeldt dat overlopende formules in het werkbladraster moeten staan, buiten Excel-tabellen. Dynamische matrixformules en gedrag van overlopende matrices

Twee voorbeeldlijsten met assets instellen

Gebruik een leeg werkblad in één werkmap. Voer de volgende lijst aan de linkerkant in A1:B8 in. De labels en hoeveelheden zijn voorbeelden; de hoeveelheid aan de linkerkant blijft onderdeel van de oorspronkelijke lijst en wordt niet gebruikt om de overeenkomst te bepalen.

Assetlabel Hoeveelheid links
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

Voer de bronlijst in G1:H7 in. Houd de weergegeven rijvolgorde aan, zodat uw resultaten overeenkomen met de uitleg.

Assetlabel Bronhoeveelheid
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

Voeg de koppen Voorkomen, Sleutel voor samenvoegen en Overeenkomende bronhoeveelheid toe in C1, D1 en E1. Voeg Voorkomen, Sleutel voor samenvoegen en Gebruikt door linkerlijst toe in I1, J1 en K1. Laat kolom F leeg om de lijsten visueel van elkaar te scheiden.

Gebruik voor dit voorbeeld niet-lege tekstlabels van minder dan 255 tekens, met consistente hoofdletters en spaties en zonder jokertekens zoals *, ? of ~. Voer voor elke bronrij een numerieke bronhoeveelheid in, ook nul wanneer dat van toepassing is. Zo blijft de betekenis van de eenvoudige formules eenduidig.

Voorkomens nummeren en zoeksleutels samenstellen

Elk label tellen vanaf de bovenkant van de eigen lijst

Voer deze formule in C2 in, druk op Return en kopieer de formule omlaag tot en met C8:

=COUNTIF($A$2:A2,A2)

Het eerste eindpunt, $A$2, blijft vast. Het tweede eindpunt wordt groter wanneer u de formule omlaag kopieert. Op elke rij telt de formule hoe vaak het label op die rij tot dan toe is voorgekomen. COUNTIF telt cellen die voldoen aan een opgegeven criterium. De functie COUNTIF gebruiken in Microsoft Excel

De resulterende voorkomnummers aan de linkerkant moeten 1, 1, 2, 1, 2, 3, 1 zijn. Het derde Arc Tile krijgt in het bijzonder 3, ook al staan er andere labels tussen de rijen.

Voer de bijbehorende bronformule in I2 in en kopieer deze vervolgens omlaag tot en met I7:

=COUNTIF($G$2:G2,G2)

De bronvoorkomnummers moeten 1, 1, 1, 2, 2, 1 zijn. Tel de twee lijsten onafhankelijk van elkaar: het voorkomnummer van een rij is de positie ervan tussen de overeenkomende labels in de eigen lijst.

Label en voorkomen combineren

Voer deze formule in D2 in en kopieer deze omlaag tot en met D8:

=A2&"|"&C2

Voer deze formule in J2 in en kopieer deze omlaag tot en met J7:

=G2&"|"&I2

De linkerlijst bevat nu Arc Tile|1, Arc Tile|2 en Arc Tile|3. De bron bevat alleen de eerste twee van die sleutels. Zo wordt het ontbrekende derde voorkomen zichtbaar voordat er hoeveelheden worden geretourneerd.

Eén bronhoeveelheid per rij aan de linkerkant retourneren

Voer de volgende formule in E2 in en kopieer deze vervolgens omlaag tot en met E8:

=XLOOKUP(D2,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

De formule zoekt in de bronsleutels voor samenvoegen, retourneert de bijbehorende bronhoeveelheid en toont Unmatched wanneer die genummerde sleutel ontbreekt. Met het laatste argument 0 wordt expliciet om een exacte overeenkomst gevraagd; dat is ook de standaardinstelling van XLOOKUP. XLOOKUP-functie

Het voltooide resultaat aan de linkerkant moet er als volgt uitzien:

Assetlabel Hoeveelheid links Voorkomen Sleutel voor samenvoegen Overeenkomende bronhoeveelheid
Arc Tile 4 1 Arc Tile\ 1
Dot Panel 2 1 Dot Panel\ 1
Arc Tile 6 2 Arc Tile\ 2
Line Print 3 1 Line Print\ 1
Dot Panel 5 2 Dot Panel\ 2
Arc Tile 1 3 Arc Tile\ 3
Ring Card 2 1 Ring Card\ 1

Er zijn nog steeds zeven rijen aan de linkerkant. Het tweede Arc Tile krijgt 9 en niet opnieuw 8, het resultaat van het eerste voorkomen. Er is geen bronrij voor het derde. Ook heeft Ring Card geen overeenkomst, terwijl de overeenkomende nul voor de tweede Dot Panel zichtbaar blijft.

Houd Unmatched onderscheiden van nul. In dit voorbeeld is nul een echte bronhoeveelheid; nul gebruiken voor een ontbrekende overeenkomst zou dat verschil verbergen. Los ook lege bronhoeveelheden op voordat u de uitvoer interpreteert, zodat een lege broncel niet wordt aangezien voor een geregistreerde hoeveelheid.

Optioneel: de resultaten als één matrix retourneren

Wis, nadat u de ingevulde resultaten hebt gecontroleerd, alleen E2:E8 en voer deze alternatieve formule in E2 in:

=XLOOKUP(D2:D8,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

Hiermee worden alle zeven zoeksleutels van de linkerkant als invoer voor de zoekactie gebruikt. Met de voorbeeldgegevens vult het resultaat E2:E8 in de volgorde van de linkerlijst. Druk op Return; de aangrenzende uitvoercellen moeten leeg zijn. Microsoft beschrijft het automatisch plaatsen van matrixresultaten als overlopen. Dynamische matrixformules en gedrag van overlopende matrices

Kies de ingevulde versie als u op elke rij een formule wilt die u afzonderlijk kunt controleren. Kies de versie die resultaten laat overlopen als u liever één formule hebt die de resultaatkolom aanstuurt. Beide zijn gebaseerd op dezelfde voorkomensleutels.

Ontbrekende overeenkomsten en ongebruikte bronrijen controleren

Het behoud van het aantal rijen aan de linkerkant betekent niet dat elke bronrij is gebruikt. Voeg een omgekeerde controle toe, zodat extra bronvoorkomens zichtbaar blijven. Voer het volgende in K2 in en kopieer de formule omlaag tot en met K7:

=COUNTIF($D$2:$D$8,J2)

Voor dit voorbeeld retourneren de eerste vijf bronrijen 1 en retourneert Grid Sheet|1 0. Die nul duidt een bronrij aan waarvoor geen overeenkomst aan de linkerkant bestaat. Een waarde groter dan 1 zou betekenen dat naar een bronsleutel wordt verwezen door meerdere linkersleutels; dat moet worden onderzocht.

Gebruik deze controles samen:

  • Controleer of de uitvoer precies de zeven oorspronkelijke rijen aan de linkerkant bevat.
  • Controleer de reeks hoeveelheden: 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • Controleer of het derde Arc Tile en Ring Card zichtbaar zonder overeenkomst blijven.
  • Controleer of Grid Sheet|1 in de broncontrole als ongebruikt wordt gemarkeerd.

De verwachte reeks en het resultaat voor de ongebruikte bron zijn gecontroleerd met een kleine deterministische berekening van de logica voor het koppelen op basis van voorkomens. De Excel-formules zijn gecontroleerd aan de hand van de gedocumenteerde syntaxis van Microsoft, maar zijn niet uitgevoerd in Excel voor Mac.

Het resultaat oplossen voordat u de werkmap uitbreidt

Koppelingen veranderen na sorteren

De voorkomensformules gebruiken de huidige rijvolgorde. Als u een van beide lijsten sorteert en de formules opnieuw berekent, kan veranderen welke herhaalde rij voorkomen 1 of 2 krijgt. Bepaal de gewenste volgorde voordat u de nummers toekent. Als een permanente koppeling nodig is, gebruik dan een stabiele identificatie die deze vastlegt in plaats van alleen op een opnieuw berekende positie te vertrouwen.

Labels lijken hetzelfde, maar komen onverwacht overeen

Microsoft vermeldt dat COUNTIF geen onderscheid maakt tussen hoofdletters en kleine letters, jokertekens in criteria herkent en onverwachte resultaten kan geven bij voor- of achterloopspaties of andere ongewenste tekens. Ook worden beperkingen vermeld bij het overeenkomen van tekenreeksen van meer dan 255 tekens. Maak de labels in beide lijsten op dezelfde manier voordat u ze nummert; de korte, consistent opgemaakte labels hierboven vermijden deze omstandigheden. De functie COUNTIF gebruiken in Microsoft Excel

De matrixformule toont #SPILL!

Controleer of er al inhoud in E3:E8 staat, waaronder de eerder ingevulde formules. Verplaats wat u wilt behouden en wis daarna de blokkade. Als de formule zich in een Excel-tabel bevindt, gebruik dan de ingevulde aanpak of verplaats de overlopende uitvoer buiten de tabel. Dit zijn gedocumenteerde beperkingen voor overlopen. Dynamische matrixformules en gedrag van overlopende matrices

U hebt een zoekactie zonder XLOOKUP nodig

Dezelfde hulpsleutels kunnen worden gebruikt voor een zoekactie met INDEX/MATCH. Voer als alternatief voor de bovenstaande formules het volgende in E2 in en kopieer het omlaag tot en met E8:

=INDEX($H$2:$H$7,MATCH(D2,$J$2:$J$7,0))

Hiermee lokaliseert MATCH met 0 de exacte sleutel voor samenvoegen en retourneert INDEX de hoeveelheid op die positie. Ontbrekende sleutels blijven bewust zichtbaar als #N/A. Microsoft documenteert beide functies voor huidige Mac-edities. Deze scalaire formule vereist geen overlopende uitvoer. MATCH-functie, INDEX-functie

Nieuwe rijen ontbreken in de resultaten

In deze uitleg worden vaste bereiken gebruikt. Wanneer u rijen toevoegt, breidt u de formules voor voorkomens en samenvoegsleutels, de bronzoek- en retourbereiken en het bereik voor de omgekeerde controle gezamenlijk uit. Breid voor de versie die resultaten laat overlopen ook de invoer voor de zoekactie aan de linkerkant uit tot voorbij D8. Gegevens onder deze bereiken toevoegen neemt die gegevens op zichzelf niet mee in de berekening.

Zodra de uitvoerreeks en de omgekeerde controle overeenkomen met de gewenste volgorde, hebt u een bruikbare samenvoeging op basis van voorkomens: elke assetrij aan de linkerkant behoudt zijn plaats, krijgt maximaal één bronhoeveelheid en blijft zichtbaar gemarkeerd als het overeenkomende voorkomen ontbreekt.

Bronnen