La fonction UNIQUE d'Excel ne peut pas ignorer les colonnes, sauf si vous utilisez cette astuce

La fonction UNIQUE d'Excel change la donne en matière de nettoyage des données, mais elle présente une limitation frustrante : elle ne fonctionne qu'avec des colonnes adjacentes. Mais en y imbriquant des fonctions supplémentaires, vous pouvez créer une liste dynamique et personnalisée qui ignore les colonnes que vous ne souhaitez pas inclure.

Les bases : comment fonctionne habituellement UNIQUE

La fonction UNIQUE d'Excel, disponible pour ceux qui utilisent les versions autonomes d'Excel publiées en 2021 ou version ultérieure, Excel pour Microsoft 365, Excel pour le Web et les applications mobiles et tablettes les plus récentes, utilise la syntaxe suivante :

=UNIQUE(array,(by_col),(exactly_once))

où:

• tableau (requis): Il s'agit de la plage ou de la table dont vous souhaitez extraire des valeurs uniques.
• par_col (facultatif): Indique à Excel de comparer les colonnes (si la valeur est TRUE) au lieu des lignes (la valeur par défaut est FALSE).
• exactement_une fois (facultatif): Renvoie les éléments qui apparaissent une seule fois dans la source (si défini sur TRUE), plutôt qu'une liste de chaque valeur distincte (par défaut sur FALSE).

La fonction UNIQUE fait partie de la famille des tableaux dynamiques, ce qui signifie que même si vous ne tapez la formule que dans une seule cellule, les résultats se répercutent automatiquement dans les cellules voisines. Vous remarquerez une fine bordure bleue autour des résultats (la plage de déversement), et si quelque chose vous gêne, vous verrez le #SPILL ! erreur.

Extraire les colonnes adjacentes

Imaginez que vous suivez les dépenses de votre ménage dans un tableau Excel nommé T_Expenses et que vous souhaitez générer une liste de chaque combinaison unique de catégorie de paiement et de magasin.

Étant donné qu'il s'agit de colonnes adjacentes dans votre tableau, vous pouvez les relier entre elles à l'aide de deux-points dans le champ tableau argument:

=UNIQUE(T_Expenses((Category):(Store)))

Le problème avec l'extraction de colonnes non adjacentes

Supposons maintenant que vous souhaitiez générer une liste de chaque combinaison unique de catégorie et de mode de paiement, qui sont des colonnes non adjacentes de votre tableau.

Si vous essayez de sélectionner uniquement ces colonnes spécifiques comme arguments distincts dans votre formule UNIQUE, vous verrez la #VALEUR ! erreur. Cela se produit car UNIQUE nécessite un seul tableau contigu. Lorsque vous sélectionnez deux colonnes distinctes, séparées par une virgule, Excel essaie d'utiliser la deuxième colonne comme par_col argument, mais comme cela nécessite un VRAI ou un FAUX, la formule plante.

Voici deux façons de résoudre ce problème.

La solution rapide : imbriquer CHOOSECOLS dans UNIQUE

Pour ignorer des colonnes, vous devez fournir à la fonction UNIQUE une table virtuelle qui contient uniquement les données souhaitées. La fonction CHOOSECOLS est conçue pour faire exactement cela : elle vous permet d'extraire des colonnes spécifiques d'une plage ou d'une table plus grande par leur numéro d'index.

Dans votre table T_Expenses, Catégorie est la deuxième colonne et Méthode est la quatrième colonne. Ainsi, pour obtenir cette liste unique, vous imbriquez la fonction CHOOSECOLS comme ceci :

=UNIQUE(CHOOSECOLS(T_Expenses,2,4))

CHOOSECOLS examine l'intégralité de la table T_Expenses et ignore tout sauf les colonnes 2 et 4. Il transmet ensuite cette nouvelle table virtuelle à deux colonnes à la fonction UNIQUE, qui effectue sa vérification et renverse les résultats.

Bien que la méthode CHOOSECOLS fonctionne parfaitement, elle présente un défaut : elle s'appuie sur des numéros d'index codés en dur. Si vous insérez une nouvelle colonne dans votre table T_Expenses, la colonne 4 ne sera plus la colonne Méthode, donc votre formule affichera les mauvaises données.

Pour rendre votre formule indestructible, vous pouvez utiliser la fonction MATCH pour rechercher les colonnes par leur nom :

=UNIQUE(
CHOOSECOLS(
T_Expenses,
MATCH(G1,T_Expenses(#Headers),0),
MATCH(H1,T_Expenses(#Headers),0)
))

Appuyez sur Alt+Entrée lorsque vous tapez votre formule dans la barre de formule pour créer un saut de ligne. Cela rend la formule plus facile à construire et à lire.

Ici, plutôt que de taper 2 et 4 manuellement, les fonctions MATCH font le travail à votre place. Le premier MATCH examine le texte de la cellule G1 (« Catégorie ») et le recherche dans la ligne d'en-tête de votre tableau. Le 0 à la fin indique à Excel de trouver une correspondance exacte. Il renvoie ensuite un nombre relatif à la plage sélectionnée en mémoire : si Catégorie est la deuxième colonne, le résultat est 2. Le deuxième MATCH fait ensuite de même pour le texte de la cellule H1 (« Méthode »), renvoyant 4.

Il s’agit d’une solution à toute épreuve pour les raisons suivantes :

• Liberté structurelle : Vous pouvez déplacer la colonne Catégorie à la fin du tableau ou insérer cinq nouvelles colonnes au milieu. Étant donné que MATCH recherche toujours le nom dans l'en-tête plutôt que de s'appuyer sur des numéros d'index codés en dur, la formule se met automatiquement à jour.
• Prévention des erreurs : Cette méthode élimine le risque d’erreur de comptage dans les grands tableaux comportant des dizaines de colonnes.
• Sélection dynamique : Puisque la formule est liée aux cellules G1 et H1, vous pouvez générer de nouvelles listes uniques en modifiant simplement les noms de colonnes dans ces cellules.

Pour rendre votre appairage encore plus intuitif, vous pouvez transformer les cellules G1 et H1 en menus déroulants. Cela évite que la formule ne se brise à cause d'une simple faute de frappe.

Cependant, il existe un obstacle technique : l'outil de validation des données d'Excel ne prend pas en charge nativement les références structurées (comme T_Expenses(#Headers)). Vous pouvez contourner ce problème en utilisant la fonction INDIRECT, qui convertit une chaîne de texte en une référence valide que Data Validation peut comprendre.

Tout d'abord, sélectionnez les cellules dans lesquelles iront vos sélections d'en-tête dynamique (dans ce cas, G1 et H1), et dans l'onglet Données, cliquez sur l'icône « Validation des données ».

=INDIRECT("T_Expenses(#Headers)")

Lorsque vous cliquez sur « OK », la boîte de dialogue se ferme et les cellules G1 et H1 contiennent une flèche cliquable affichant chaque en-tête de votre tableau T_Expenses.

Au moment où vous choisissez un nouveau nom de colonne dans la liste, la fonction MATCH trouve sa nouvelle position, CHOOSECOLS récupère les données et UNIQUE met à jour votre liste instantanément. De plus, comme la fonction INDIRECT examine spécifiquement les en-têtes du tableau, toute nouvelle colonne que vous ajoutez au tableau T_Expenses apparaîtra automatiquement dans vos menus déroulants sans que vous ayez à mettre à jour les paramètres de validation des données.

---

Étant donné que la fonction UNIQUE repose sur des tableaux dynamiques, il s'agit d'un moyen beaucoup plus sûr et efficace de gérer vos données que l'outil de suppression des doublons à l'ancienne, qui écrase définitivement vos informations. Avec cette configuration, votre liste principale reste intacte et votre résumé unique reste précis, quelle que soit la réorganisation de votre feuille de calcul.