Pour joindre des libellés d’actifs répétés sans multiplier les lignes, numérotez les occurrences de chaque libellé dans les deux listes, puis recherchez la paire libellé-occurrence. Ce guide pour Excel pour Mac conserve chaque ligne de gauche et signale les correspondances manquantes.

Que signifie une correspondance par occurrence ?

Supposons que deux listes d’actifs artistiques contiennent plusieurs lignes intitulées Arc Tile. Votre tâche consiste à associer la première quantité source à la première ligne Arc Tile de gauche, la deuxième quantité source à la deuxième, et ainsi de suite. D’autres libellés peuvent apparaître entre ces lignes ; le décompte des occurrences est propre à chaque libellé.

Une relation fondée uniquement sur le libellé est ambiguë. Si une liste contient trois lignes Arc Tile et l’autre en contient deux, associer toutes les combinaisons correspondantes produirait six combinaisons pour ce libellé. Une recherche simple présente une autre limite : Microsoft indique que XLOOKUP renvoie la première correspondance trouvée. Répéter une recherche basée uniquement sur le libellé ne permet donc pas de passer aux occurrences suivantes. Fonction XLOOKUP

La solution consiste à rendre la clé de recherche plus précise : Arc Tile|1, Arc Tile|2 et Arc Tile|3. Chaque clé numérotée identifie une occurrence. La méthode ci-dessous est un exemple détaillé utilisant des fonctions Excel documentées ; la documentation de Microsoft décrit le comportement de ces fonctions.

Utilisez cette approche lorsque l’ordre des lignes définit les associations. Si les lignes doivent plutôt être associées à l’aide d’un identifiant d’actif distinct ou d’un champ de révision, utilisez ces informations. Les numéros d’occurrence ne permettent pas de déterminer quels enregistrements associer lorsque l’ordre n’a pas de signification.

Quelles versions d’Excel pour Mac prennent en charge cette méthode ?

Les formules principales utilisent COUNTIF, la concaténation avec & et XLOOKUP. Microsoft répertorie Excel pour Microsoft 365 pour Mac, Excel 2024 pour Mac et Excel 2021 pour Mac sur les pages consacrées aux fonctions concernées. Sa documentation sur XLOOKUP précise explicitement que cette fonction n’est pas disponible dans Excel 2016 et Excel 2019. Fonction XLOOKUP, Utiliser la fonction COUNTIF dans Microsoft Excel

Commencez par la formule à recopier ci-dessous. Une variante présentée plus loin renvoie les mêmes sept résultats avec une seule formule matricielle à déversement. Microsoft indique que les formules matricielles à déversement doivent être placées dans la grille de la feuille de calcul, en dehors des tableaux Excel. Formules matricielles dynamiques et comportement des déversements

Configurer deux listes d’actifs fictives

Utilisez une feuille de calcul vierge dans un classeur. Saisissez la liste de gauche suivante dans A1:B8. Ces libellés et quantités sont donnés à titre d’exemple ; la quantité de gauche reste partie intégrante de la liste d’origine et n’est pas utilisée pour déterminer la correspondance.

Libellé de l’actif Quantité de gauche
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

Saisissez la liste source dans G1:H7. Conservez l’ordre des lignes indiqué pour que vos résultats correspondent à ceux du guide.

Libellé de l’actif Quantité source
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

Ajoutez les en-têtes Occurrence, Clé de jointure et Quantité source correspondante dans C1, D1 et E1. Ajoutez Occurrence, Clé de jointure et Utilisée par la liste de gauche dans I1, J1 et K1. Laissez la colonne F vide pour séparer visuellement les listes.

Pour cet exemple, utilisez des libellés textuels non vides de moins de 255 caractères, avec une casse et des espaces cohérents, et sans caractères génériques tels que *, ? ou ~. Saisissez une quantité source numérique pour chaque ligne source, y compris zéro si nécessaire. Ces conditions préalables permettent d’interpréter sans ambiguïté les formules simples.

Numéroter les occurrences et créer les clés de recherche

Compter chaque libellé depuis le début de sa liste

Saisissez cette formule dans C2, appuyez sur Return, puis recopiez-la jusqu’à C8 :

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

La première référence, $A$2, reste fixe. La deuxième s’étend à mesure que vous recopiez la formule vers le bas. À chaque ligne, la formule compte le nombre d’apparitions du libellé de cette ligne jusque-là. COUNTIF compte les cellules qui répondent à un critère donné. Utiliser la fonction COUNTIF dans Microsoft Excel

Les numéros d’occurrence de gauche devraient être 1, 1, 2, 1, 2, 3, 1. En particulier, le troisième Arc Tile reçoit 3, même si d’autres libellés séparent ses lignes.

Saisissez la formule source correspondante dans I2, puis recopiez-la jusqu’à I7 :

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

Les numéros d’occurrence source devraient être 1, 1, 1, 2, 2, 1. Comptez les deux listes indépendamment : le numéro d’occurrence d’une ligne correspond à sa position parmi les libellés identiques dans sa propre liste.

Combiner le libellé et l’occurrence

Saisissez cette formule dans D2 et recopiez-la jusqu’à D8 :

=A2&"|"&C2

Saisissez cette formule dans J2 et recopiez-la jusqu’à J7 :

=G2&"|"&I2

La liste de gauche contient maintenant Arc Tile|1, Arc Tile|2 et Arc Tile|3. La source ne contient que les deux premières de ces clés. Cela fait apparaître la troisième occurrence manquante avant même de renvoyer des quantités.

Renvoyer une quantité source pour chaque ligne de gauche

Saisissez la formule suivante dans E2, puis recopiez-la jusqu’à E8 :

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

La formule recherche les clés de jointure source, renvoie la quantité source correspondante et affiche Unmatched lorsque cette clé numérotée est absente. Le dernier argument 0 demande explicitement une correspondance exacte, qui est également le comportement par défaut de XLOOKUP. Fonction XLOOKUP

Le résultat final de gauche devrait être :

Libellé de l’actif Quantité de gauche Occurrence Clé de jointure Quantité source correspondante
Arc Tile 4 1 Arc Tile|1 8
Dot Panel 2 1 Dot Panel|1 12
Arc Tile 6 2 Arc Tile|2 9
Line Print 3 1 Line Print|1 7
Dot Panel 5 2 Dot Panel|2 0
Arc Tile 1 3 Arc Tile|3 Unmatched
Ring Card 2 1 Ring Card|1 Unmatched

Il y a toujours sept lignes de gauche. Le deuxième Arc Tile reçoit 9 au lieu de répéter 8, qui correspond à la première occurrence. Le troisième n’a pas de partenaire source. Ring Card n’en a pas non plus, tandis que le zéro correspondant au deuxième Dot Panel reste visible.

Ne confondez pas Unmatched avec zéro. Dans cet exemple, zéro est une quantité source réelle ; utiliser zéro pour signaler une correspondance absente masquerait cette différence. De même, résolvez les quantités sources vides avant d’interpréter le résultat afin de ne pas confondre une cellule source vide avec une quantité enregistrée.

Facultatif : renvoyer les résultats sous forme d’une seule matrice

Après avoir vérifié les résultats recopiés vers le bas, effacez uniquement E2:E8 et saisissez cette autre formule dans E2 :

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

Cette formule fournit les sept clés de jointure de gauche comme entrée de recherche. Avec les données de l’exemple, le résultat occupe E2:E8, dans l’ordre de la liste de gauche. Appuyez sur Return ; les cellules de sortie adjacentes doivent être vides. Microsoft décrit ce placement automatique des résultats matriciels comme un déversement. Formules matricielles dynamiques et comportement des déversements

Choisissez la version recopiée vers le bas si vous préférez pouvoir inspecter indépendamment la formule de chaque ligne. Choisissez la version à déversement si vous préférez qu’une seule formule contrôle la colonne de résultats. Les deux reposent sur les mêmes clés d’occurrence.

Vérifier les correspondances manquantes et les lignes source inutilisées

Conserver le nombre de lignes de gauche ne signifie pas que chaque ligne source a été utilisée. Ajoutez un contrôle inverse pour que les occurrences source supplémentaires restent visibles. Dans K2, saisissez la formule suivante et recopiez-la jusqu’à K7 :

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

Pour cet exemple, les cinq premières lignes source renvoient 1, et Grid Sheet|1 renvoie 0. Ce zéro indique une ligne source sans partenaire dans la liste de gauche. Une valeur supérieure à 1 signifierait qu’une clé source est référencée par plusieurs clés de gauche et nécessiterait une vérification.

Utilisez ces vérifications ensemble :

  • Confirmez que le résultat occupe exactement les sept lignes de gauche d’origine.
  • Confirmez la séquence de quantités : 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • Confirmez que le troisième Arc Tile et Ring Card restent clairement sans correspondance.
  • Confirmez que Grid Sheet|1 est indiqué comme inutilisé dans le contrôle de la source.

La séquence attendue et le résultat de la vérification des sources inutilisées ont été contrôlés à l’aide d’un petit calcul déterministe de la logique d’association par occurrence. Les formules Excel ont été vérifiées par rapport à la syntaxe documentée par Microsoft ; elles n’ont pas été exécutées dans Excel pour Mac.

Résoudre les problèmes avant d’étendre le classeur

Les associations changent après un tri

Les formules d’occurrence utilisent l’ordre actuel des lignes. Trier l’une ou l’autre liste et recalculer peut modifier la ligne répétée qui reçoit l’occurrence 1 ou 2. Déterminez l’ordre souhaité avant la numérotation. Si une association permanente est nécessaire, conservez un identifiant stable qui l’enregistre au lieu de vous fier uniquement à une position recalculée.

Des libellés semblent identiques, mais les correspondances sont inattendues

Microsoft indique que COUNTIF ne tient pas compte de la casse, reconnaît les critères génériques et peut produire des résultats inattendus en présence d’espaces de début ou de fin ou d’autres caractères indésirables. La documentation signale également des limites lors de la correspondance de chaînes de plus de 255 caractères. Uniformisez les libellés des deux listes avant de les numéroter ; les libellés courts et formatés de manière cohérente ci-dessus évitent ces problèmes. Utiliser la fonction COUNTIF dans Microsoft Excel

La formule matricielle affiche #SPILL!

Vérifiez s’il existe du contenu dans E3:E8, y compris les formules recopiées précédemment vers le bas. Déplacez les éléments que vous souhaitez conserver, puis supprimez l’obstacle. Si la formule se trouve dans un tableau Excel, utilisez l’approche avec recopie vers le bas ou déplacez le résultat à déversement en dehors du tableau. Ces restrictions de déversement sont documentées. Formules matricielles dynamiques et comportement des déversements

Vous avez besoin d’une recherche sans XLOOKUP

Les mêmes clés auxiliaires peuvent servir à une recherche INDEX/MATCH. À la place de l’une ou l’autre formule ci-dessus, saisissez celle-ci dans E2 et recopiez-la jusqu’à E8 :

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

Ici, MATCH avec 0 localise la clé de jointure exacte, et INDEX renvoie la quantité à cette position. Les clés manquantes restent volontairement visibles sous la forme #N/A. Microsoft documente ces deux fonctions pour les éditions Mac actuelles. Cette formule scalaire ne nécessite aucun résultat à déversement. Fonction MATCH, Fonction INDEX

Les nouvelles lignes ne figurent pas dans les résultats

Le guide utilise des plages fixes. Lorsque vous ajoutez des lignes, étendez ensemble les formules d’occurrence et de clé de jointure, les plages de recherche et de renvoi de la source, ainsi que la plage de contrôle inverse. Pour la version à déversement, étendez également l’entrée de recherche de gauche au-delà de D8. L’ajout de données sous ces plages ne les inclut pas à lui seul dans le calcul.

Une fois que la séquence de résultats et le contrôle inverse correspondent à l’ordre souhaité, vous disposez d’une jointure par occurrence opérationnelle : chaque ligne d’actif de gauche conserve sa place, reçoit au plus une quantité source et indique clairement lorsque l’occurrence correspondante est absente.

Sources