Curieuse formule d'extraction de valeurs sans doublons

Article basé sur une demande d'un utilisateur.

La solution proposée est assez "exotique" et m'a semblé être intéressante à présenter.

Pour les anciennes versions d'Excel non "Tableau dynamique" (voir Formules de tableaux dynamiques, une nouvelle logique de conception - Bienvenue à 1forme.fr ), valider par CTRL + MAJ +ENTER.

L'objectif

Extraire en colonne les valeurs d'une plage sans doublons.

Tableau de départ

Résultat attendu

La solution avec des fonctions "modernes"

=UNIQUE(DANSCOL(A2:D5))

Remarque :

Étonnamment, on a parfois un doublon : 0 (zéro).

Si le tableau contient à la fois des cellules vides et des cellules à 0 (zéro), on aura 2 fois 0 (les cellules vides étant évaluées à 0).

On peut ajouter une 2ème fonction UNIQUE pour résoudre ce problème.

Le défi

Les fonctions DANSCOL et UNIQUE ne sont pas disponibles dans la version de l'utilisateur (2019).

Il va donc falloir trouver d'autres solutions pour :

• Remplacer UNIQUE pour supprimer les doublons.
• Trouver comment travailler sur une plage avec plusieurs lignes et colonnes. Beaucoup de fonctions d'Excel ne fonctionnent qu'avec une matrice "2D" (une matrice d'une ligne ou d'une colonne) et non "3D" (comme UNIQUE) d'où l'utilisation de la fonction DANSCOL pour passer la plage/matrice en "2D".

Une solution avec des fonctions "anciennes"

=INDIRECT(TEXTE(
    MIN(SI(
           ($A$2:$D$5<>"")*(NB.SI.ENS($F$8:F8;$A$2:$D$5)=0) ;
           LIGNE($2:$5)*1000+COLONNE($A:$D) ;
           10^9+1
         ))
  ; "L0C000" ) ; ) & ""

Formule en cellule F9 qui devra être recopier vers le bas (cf plage "élastique" $F$8:F8 du NB.SI.ENS).

Plusieurs éléments de syntaxe mystérieux :

• Dans NB.SI.ENS
  • La plage élastique ici $F$8:F8
  • Le critère est constitué d'une plage.
• Le * 1000
• Le 10^9+1
• Le "L0C000"
• Le dernier ; avec rien après
• Le & "" à la fin

Décortiquons son fonctionnement.

Principes de fonctionnement

1. On calcul des "codes coordonnés" des cellules restant encore à extraire.
2. On prend le plus "petit" code.
3. On récupère cette valeur.

Décomposition de la formule

Rôles de INDIRECT et TEXTE

Je commence par la "fin" de la formule !

=INDIRECT(TEXTE( MIN(...) ; "L0C000" ) ; ) & ""

La fonction MIN va coder une valeur "coordonnés" de la cellule à récupérer sous cette forme : N°LigneN°colonne (le N° de colonne est codé sur 3 caractères ici).

Exemple : 0004002 soit la ligne 4 et la colonne 2.

La formule est donc limitée à 999 colonnes avec cette syntaxe (on peut facilement augmenter cette limite).

On a donc :

=INDIRECT(TEXTE( 0004002 ; "L0C000" ) ; ) & ""

La fonction TEXTE renvoie la valeur "L0004C002" en lui appliquant le format "L0C000".

On arrive à :

=INDIRECT( "L0004C002" ; ) & ""

La fonction INDIRECT renvoie le contenue de la cellule en colonne 2 et en ligne 4 soit B4.

L'astuce ici vient du point-virgule.

= INDIRECT( "L0004C002" ; ) est la forme abrégée de = INDIRECT( "L0004C002" ; FAUX ).

Avoir FAUX au 2eme argument indique l'utilisation de l'ancienne notation des références de cellule d'Excel en mode L1C1 ou les colonnes sont référencées par des nombres.

On obtient :

= B4 & ""

Quand il n'y a plus de valeurs à extraire, le formule renvoie la cellule A1000000 (cellule vide, on l'espère 😁).

Pourquoi & ""

Étonnement

= 0 & "" => 0

Mais

= cellule vide & "" => ""

Donc ainsi la formule n'affiche plus des 0 quand toutes les valeurs ont été extraites mais une cellule vide.

La génération des coordonnées par la fonction MIN

Dans l'exemple précédemment utilisé, comment obtient-on le code 0004002 ?

...MIN(SI(
           ($A$2:$D$5<>"")*(NB.SI.ENS($F$8:F8;$A$2:$D$5)=0) ;
           LIGNE($2:$5)*1000+COLONNE($A:$D) ;
           10^9+1
         ))

La fonction NB.SI.ENS renvoie une "table de vérité", de la même taille que la plage de départ ou 0 indique NON TROUVE (je vais y revenir plus loin).

Le NB.SI.ENS(...) = 0 permet d'inverser la table. Les 0 (=non trouvé) deviennent VRAI (soit 1) et les autres valeurs FAUX (soit 0).

Malheureusement, les cellules vides sont prises en compte d'où le ($A$2:$D$5<>"") * pour les passer à 0.

Exemple de table de vérité (plage H2:K5) générée par la formule en F15 pour la 7eme valeurs (B4).

Les cellules en oranges correspondent aux cas qui ont déjà été "traitées" et aux cas des valeurs vides générant la valeur 0 dans la "table de vérité".

La fonction SI renvoie la table des codes de coordonnées à extraire.

1E+09 c'est 10^9+1 soit 1 000 000 001

Rappel :

0 c'est équivalent à FAUX pour Excel et 1 (ou un autre nombre) c'est VRAI.

Donc

SI(Table de vérité; LIGNE($2:$5)*1000+COLONNE($A:$D) ; 10^9+1 )

renvoie :

• le code de coordonné si Table de vérité = 0
• 1E+09 si Table de vérité = 1

La fonction MIN "écrase" la table et ne récupère que la plus petite valeurs (ici 4002).

C'est là où le 10^9+1 prend son sens. Ainsi MIN ignorera les valeurs déjà trouvées. Elle récupérera cette valeur en dernier, une fois que toutes les extractions auront été faites (+1 pour ne pas avoir une erreur dû à un n° colonne à 0).

La fonction NB.SI.ENS

NB.SI.ENS($F$8:F8;$A$2:$D$5)

Ici, ce n'est pas une syntaxe courante, l'argument Critère est une matrice, dans ce cas NB.SI.ENS renvoie une matrice de résultats de même taille que la matrice Critère.

Chaque élément de la matrice de résultats correspond au résultat de la fonction NB.SI.ENS avec l'élément correspondant de la matrice Critère.

Ici on compte le nombre de fois où l'on trouve les valeurs dans les résultats (pas l'inverse !). Si la valeur n'a pas déjà été extraite, NB.SI.ENS renvoie 0, permettant ainsi les repérer.

Autres syntaxes pour cette formule

Version ne convertissant pas les valeurs en texte

=SIERREUR(
    INDIRECT(TEXTE(
        MIN(SI(
             ($A$2:$D$5<>"")*(NB.SI.ENS($F$8:F8;$A$2:$D$5)=0) ;
             LIGNE($2:$5)*1000+COLONNE($A:$D) ;
             10^9
           ))
      ; "L0C000" ) ; )
  ;"")

• Plus de +1 après 10^9 afin de déclencher une erreur quand on arrive en fin d'extraction.
• Plus de & "" à la fin forçant la conversion.

Version sans fonction SI et sans conversion

=SIERREUR(
    INDIRECT(TEXTE(
        MAX(($A$2:$D$5<>"")*(NB.SI.ENS($H$8:H8;$A$2:$D$5)=0)*(LIGNE($2:$5)*1000+COLONNE($A:$D)))
    ;"L0C000");)
;"")

Ici on profite des 0 de la table de vérité pour annuler les coordonnées à ne pas traiter (plus de fonction SI ni de 10^9+1).

La table de vérité est "inversée" (0 c'est déjà trouvé ou vide) donc on utilise une fonction MAX au lieu de la fonction MIN.

Après la dernière extraction la fonction INDIRECTE va chercher des cellules L0C0 d'où une erreur et d'où la fonction SIERREUR pour l'intercepter.

Remarques

Ne profitant pas des tableaux dynamiques (comme avec la fonction UNIQUE), c'est à l'utilisateur d'anticiper le nombre de résultats en recopiant la formule sur une plage suffisamment grande (les cellules vides/non utilisées utiliseront ainsi de la puissance de calcul inutilement (mais c'était courant comme situation).

Merci pour votre attention bienveillante.