UNIQUE à partir de colonnes non adjacentes - Astuces Excel

L'autre jour, j'étais sur le point de créer une combinaison unique de deux colonnes non adjacentes dans Excel. Je fais généralement cela avec Supprimer les doublons ou avec Filtre avancé, mais j'ai pensé que j'essaierais de le faire avec la nouvelle fonction UNIQUE à venir dans Office 365 en 2019. J'ai essayé plusieurs idées et aucune ne fonctionnerait. Alors, je suis allé voir le maître des tableaux dynamiques, Joe McDaid, pour obtenir de l'aide. La réponse est plutôt cool, et je suis sûr que je l'oublierai, alors je la documente pour vous et pour moi. Je suis sûr que, dans deux ans, je vais chercher sur Google comment faire cela et réaliser "Oh, regardez! C'est moi qui ai écrit l'article à ce sujet!"

Avant d'accéder à la fonction UNIQUE, jetez un œil à ce que j'essaie de faire. Je veux chaque combinaison unique de représentant commercial de la colonne B et de produit de la colonne C. Normalement, je suivrais ces étapes:

Copiez les en-têtes de B1 et D1 dans une section vierge de la feuille de calcul
À partir de B1, choisissez Data, Filter, Advanced
Dans la boîte de dialogue Filtre avancé, choisissez Copier vers un nouvel emplacement
Spécifiez les en-têtes de l'étape 1 comme plage de sortie
Cochez la case Valeurs uniques uniquement
Cliquez sur OK

Copiez les deux en-têtes dans une section vide qui devient la plage de sortie

Le résultat est chaque combinaison unique des deux champs. Notez que le filtre avancé ne trie pas les éléments - ils apparaissent dans l'ordre d'origine.

Obtenir une liste unique est l'une de mes utilisations préférées du filtre avancé

Ce processus est devenu plus facile dans Excel 2010 grâce à la commande Supprimer les doublons dans l'onglet Données du ruban. Suivez ces étapes:

Sélectionnez B1: D227 et Ctrl + C pour copier
Coller dans une section vide de la feuille de calcul.

Faites une copie des données car Supprimer les doublons est destructif
Choisissez Données, Supprimer les doublons
Dans la boîte de dialogue Supprimer les doublons, désélectionnez Date. Cela indique à Excel de ne regarder que le représentant et le produit.
Cliquez sur OK

Dites à Supprimer les doublons de ne considérer que le représentant et la date

Les résultats sont presque parfaits - il vous suffit de supprimer la colonne Date.

Supprimer la colonne supplémentaire

La question: existe-t-il un moyen pour que la fonction UNIQUE ne regarde que les colonnes B et D? (Si vous n'avez pas encore vu la nouvelle fonction UNIQUE, lisez: Fonction UNIQUE dans Excel.)

Demander =UNIQUE(B2:D227)vous obtiendrait chaque combinaison unique de représentant, de date et de produit, ce qui n'est pas ce que nous recherchons.

Comment pouvons-nous passer deux colonnes non adjacentes à la fonction UNIQUE?

Lorsque les tableaux dynamiques ont été introduits en septembre, j'ai dit que nous n'aurions plus jamais à nous soucier de la complexité des formules Ctrl + Maj + Entrée. Mais pour résoudre ce problème, vous allez utiliser un concept appelé Lifting. J'espère que maintenant, vous avez téléchargé mon livre électronique Excel Dynamic Arrays Straight To The Point. Reportez-vous aux pages 31-33 pour une explication complète du levage.

Voir mon livre pour une explication complète du levage (et plus tard, lorsque vous allez trier les résultats, du levage par paires)

Prenez une fonction Excel qui attend une valeur unique. Par exemple, =CHOOSE(Z1,"Apple","Banana")renvoie Apple ou Banana selon que Z1 contient 1 (pour Apple) ou 2 (pour Banana). La fonction CHOOSE attend un scalaire comme premier argument.

Mais au lieu de cela, vous allez passer une constante de tableau de (1,2) comme premier argument à CHOOSE. Excel effectuera l'opération de levage et calculera CHOOSE deux fois. Pour la valeur 1, vous voulez les commerciaux en B2: B227. Pour la valeur de 2, vous voulez les produits en D2: D227.

Dites CHOOSE pour renvoyer deux réponses

Normalement, dans l'ancien Excel, l'intersection implicite aurait foiré les résultats. Mais maintenant qu'Excel peut répandre les résultats sur de nombreuses cellules, la formule ci-dessus renvoie avec succès un tableau de toutes les réponses en B et D:

Succès! Tout est en descente d'ici

J'ai l'impression que j'insulterais votre intelligence pour écrire le reste de l'article, car à partir de là, c'est super simple.

Enveloppez la formule de la capture d'écran précédente dans UNIQUE et vous obtenez uniquement les combinaisons uniques de représentant commercial et de produit à l'aide =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Toujours pas trié

Pour vérifier votre compréhension, essayez de modifier la formule ci-dessus pour renvoyer toutes les combinaisons uniques de trois colonnes: représentant commercial, produit, couleur.

Tout d'abord, changez la constante du tableau pour qu'elle fasse référence à (1, 2, 3).

Ensuite, ajoutez un quatrième argument à choisir de revenir couleur E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Renvoie la combinaison unique de trois colonnes

Ce serait bien de trier ces résultats, nous nous tournons donc vers Trier avec une formule utilisant TRIER et TRIER.

Normalement, la fonction à trier par la première colonne par ordre croissant serait =SORT(Array)ou =SORT(Array,1,1).

Afin de trier par trois colonnes, vous devez effectuer un levage par paires avec =SORT(Array,(1,2,3),(1,1,1)). Dans cette formule, lorsque vous arrivez au deuxième argument de TRIER, Excel veut savoir par quelle colonne trier. Au lieu d'une seule valeur, envoyez trois colonnes à l'intérieur d'une constante de tableau: (1,2,3). Lorsque vous arrivez au troisième argument où spécifiez 1 pour Croissant ou -1 pour Décroissant, envoyez une constante de tableau avec trois 1 pour indiquer Ascendant, Croissant, Croissant. La capture d'écran suivante montre =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Pour plus d'informations sur le levage par paires, reportez-vous à la page 34 des tableaux dynamiques Excel directement au point.

Au moins jusqu'à la fin de 2018, vous pouvez télécharger gratuitement le livre Excel Dynamic Arrays en utilisant le lien au bas de cette page.

Je suis encouragé de constater que la réponse à la question d'aujourd'hui est un peu compliquée. Lorsque Dynamic Arrays est sorti, j'ai instantanément pensé à toutes les formules étonnantes publiées sur le babillard électronique par Aladin Akyurek et d'autres et à la façon dont ces formules deviendraient beaucoup plus simples dans le nouvel Excel. Mais l'exemple d'aujourd'hui montre que les génies des formules auront toujours besoin de créer de nouvelles façons d'utiliser les tableaux dynamiques.