Vous avez probablement déjà essayé de comparer toutes les colonnes entre elles pour trouver des doublons dans Google Sheets et vous savez que ce n’est pas une tâche facile. En effet, Google Sheets ne propose pas de fonction dédiée à cette fonctionnalité. Mais ne vous inquiétez pas, j’ai une solution pour vous !
J’ai développé une fonction personnalisée nommée COMPARE_ALL_COLUMNS ainsi qu’une fonction enfant appelée CAC_CHILD pour répondre à ce besoin. La première fonction vous permettra d’identifier les doublons dans vos colonnes, tandis que la seconde fonction vous donnera les colonnes comparées.
Pour utiliser ces fonctions, il vous suffit de fournir la référence de la plage que vous souhaitez comparer. Par exemple, A2:C7. La comparaison sera effectuée entre A2:A7 et B2:B7, A2:A7 et C2:C7, et enfin B2:B7 et C2:C7.
Regardons un exemple concret. Dans l’exemple ci-dessous, la fonction enfant retourne les valeurs dans la colonne E2:E4, tandis que la fonction principale retourne les doublons, c’est-à-dire les noms de fruits communs, dans la plage F2:H4.
Voici un autre exemple pour comparer ou faire correspondre toutes les colonnes entre elles et obtenir les doublons dans Google Sheets. Cette fois-ci, nous avons cinq colonnes à comparer.
Plage : A2:E8
Formule enfant : G2
Formule principale : H2
En utilisant ces fonctions dans la vie réelle, vous pouvez vérifier si les mêmes noms apparaissent dans plusieurs équipes.
Maintenant, examinons la syntaxe et les arguments des fonctions COMPARE_ALL_COLUMNS et CAC_CHILD :
-
Syntaxe : COMPARE_ALL_COLUMNS(range)
-
Argument : range (référence de plage)
-
Syntaxe : CAC_CHILD(range)
-
Argument : range (référence de plage)
Vous pouvez importer ces fonctions à partir de mon modèle de feuille de calcul et les utiliser immédiatement pour comparer toutes les colonnes entre elles et trouver des valeurs communes.
Si vous souhaitez utiliser ces fonctions, suivez simplement ces étapes :
- Cliquez sur le lien pour faire une copie de ma feuille de calcul qui contient ces deux fonctions.
- Ouvrez la feuille de calcul dans laquelle vous souhaitez les utiliser.
- Allez dans le menu « Données », puis sélectionnez « Fonctions nommées » et enfin « Importer la fonction ».
- Suivez les instructions à l’écran et voilà !
Vous êtes maintenant prêt à utiliser ces deux fonctions pour comparer toutes les colonnes (listes) dans une plage donnée et trouver les valeurs dupliquées ou communes.
Si vous préférez ne pas importer de fonctions personnalisées, vous pouvez utiliser les formules suivantes pour obtenir les mêmes résultats :
Pour la fonction enfant (CAC_CHILD) :
=ArrayFormula(lambda(range,sort(let(setA,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,r+c))),setB,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,c))),filter(setB,setA<=columns(range))))&" and "&let(setA,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,r+c))),filter(setA,setA<=columns(range))))(A2:E))
Remplacez A2:E par la plage réelle que vous souhaitez comparer.
Et pour la fonction principale (COMPARE_ALL_COLUMNS) :
=lambda(range,map(sort(let(setA,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,r+c))),setB,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,c))),filter(setB,setA<=columns(range)))),let(setA,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,r+c))),filter(setA,setA<=columns(range))),lambda(two,one, transpose(ifna(unique(filter(index(range,0,two),xmatch(index(range,0,two),index(range,0,one),0))))))))(A2:E)
Remplacez A2:E par la plage réelle que vous souhaitez comparer.
Ces formules utilisent les fonctions LAMBDA et LET pour simplifier la référence à la plage dans la formule. Ainsi, il vous suffit de modifier la plage une seule fois pour l’adapter à vos besoins.
Si vous êtes un passionné de Google Sheets, vous serez peut-être curieux de connaître la logique derrière ces formules. En réalité, j’ai développé ces deux fonctions en me basant sur la même logique. Voici comment cela fonctionne :
Supposons que nous avons cinq équipes avec sept joueurs chacune. Nous pouvons identifier les équipes par les numéros 1, 2, 3, 4 et 5. Si nous voulons vérifier si un joueur de l’équipe n°1 est présent dans une autre équipe, nous pouvons utiliser les fonctions INDEX et COMPARER ou NB.SI pour cela. La comparaison se ferait entre les colonnes 1 & 2, 1 & 3, et 1 & 4.
Mais comment comparer toutes les équipes entre elles pour trouver les noms en double ? La comparaison doit être comme suit :
- Colonne à comparer : Match avec
- 1 : 2
- 1 : 3
- 1 : 4
- 1 : 5
- 2 : 3
- 2 : 4
- 2 : 5
- 3 : 4
- 3 : 5
- 4 : 5
Si nous pouvons générer ces deux ensembles de nombres, nous pourrons utiliser la fonction INDEX pour décaler la plage en conséquence et comparer toutes les colonnes entre elles.
J’ai développé mes formules en m’inspirant de cette logique et en utilisant la fonction MAKEARRAY pour générer les ensembles de nombres. Les formules sont divisées en trois étapes pour générer ces ensembles.
Notez que j’ai utilisé un lien vers mon site web Crawlan.com pour plus d’informations sur ces fonctions et leur utilisation. J’espère que ces explications vous donneront une meilleure compréhension et vous aideront à utiliser les fonctions COMPARE_ALL_COLUMNS et CAC_CHILD dans vos feuilles de calcul Google Sheets. Maintenant, à vous de jouer !
