Bien que n'ayant pas encore utilisé cette fonction dans un vrai cas de figure, je voudrais apporter quelques éléments de réflexion qu'il me semble capital de maitriser.

Dans la très grande majorité des cas, utiliser des tableaux structurés est beaucoup plus avantageux et lisible (le nom du tableau et de la colonne sont automatiquement dans la formule).

RECHERCHEX et INDEX/EQUIV

Donc, SUPPR.PLAGE et .:. réduisent la plage de travail, mais si on travaille sur deux colonnes devant avoir le même nombre de lignes, on prend un gros risque.

Par exemple...

Pour la première formule, la valeur qu'on aurait attendu serait 0 (ou vide avec & "").
Pour la deuxième formule, la valeur retournée est #VALEUR, ce qui est logique (mais pas forcément attendu) puisque les deux plages dans recherchex n'ont pas le même nombre de lignes.
Les troisièmes et quatrièmes formules illustrent le même problème, mais avec la combinaison INDEX/EQUIV. On voit d'ailleurs comment la dernière formule renvoie une valeur carrément fausse.

Les cellules de fin ou début de plage contenant un espace ne sont pas "supprimées" avec Suppr.Plage.

Par exemple, j'ai ici ajouté un espace en B5 et remis le 1 en A1.
Regardez l'impact sur les formules.

Ou bien encore cet espace ajouté malencontreusement en B9 qui engendre quelques subtilités.

Subtilité 1 : les cellules de fin ou début de plage contenant un espace ne sont pas "supprimées" avec Suppr.Plage.

Subtilité 2 : Lorsqu'une référence dynamique rencontre des cellules vides, elle va les transformer en zéro. Bien ou pas bien, je ne sais pas, mais il faut juste en être conscient. On a un exemple en C1, qui va retourner les cellules vides avec des zéros.

Subtilité 3 : Ensuite, vous pouvez vous amuser avec les fonctions NB et NBVAL pour comprendre les impacts et les utiliser à bon escient en fonction de ce que vous voulez retourner !

La fonction NBVAL compte le nombre de cellules qui ne sont pas vides dans une plage. Donc 5 (A,B,C,D,Espace) pour NBVAL(A.:.A) et 9 (on ajoute les 4 zéros) pour NBVAL(C1#).

La fonction NB() retourne les valeurs numériques.

NB(A.:.A) retourne donc 0 valeurs numériques. NB(C1#) retourne 4 pour la plage dynamique propagée réduite de A:A avec un remplacement des cellules vides par un zéro - donc du numérique. Argh !

Mini bonus : =LIGNES(A.:.A) renvoit bien 9. Ouf !

En conclusion, j'aime beaucoup avoir cette nouvelle fonction pour des cas tordus, mais pour le moment rien ne remplace un bon tableau structuré en raison de ces effets de bord !

L'occasion de relire ce post

Les tableaux structurés dans Excel

Il y a quelques mois, la fonction SUPPR.PLAGE est apparue. On peut utiliser l'équivalent avec .:. (oui, oui, point, deux points, point).

Je ne pense pas beaucoup utiliser cette fonction, car je construis mes fichiers pratiquement uniquement avec des tableaux structurés (Accueil, Mettre sous forme de tableau), mais si vous n'aimez pas cette option, alors cette approche peut être très utile !

Voici un fichier avec quelques exemples.

Cette fonction va donc supprimer les zones complètement vides autour d'une plage.

Donc, si on sélectionne toute la feuille, ca va renvoyer l'équivalent de la plage utilisée. Il faut faire appel à cette fonction à partir d'une autre feuille pour éviter de gérer une référence circulaire.

Soit la feuille 1 :

Sur une deuxième feuille, voici donc le résultat de l'utilisation de .:. et de SUPPR.PLAGE, qui renvoie donc la plage utilisée dans la feuil1.

On peut ensuite utiliser plusieurs fonctions pour retourner la plage sous forme de texte.

=ADRESSE( MIN(LIGNE(Feuil1!1.:.1048576)); MIN(COLONNE(Feuil1!1.:.1048576))) &":"& ADRESSE(MAX(LIGNE(Feuil1!1.:.1048576)); MAX(COLONNE(Feuil1!1.:.1048576)))

ou plus "simplement" en Lambda

=LAMBDA(_a;ADRESSE(MIN(LIGNE(_a));MIN(COLONNE(_a)))&":"&ADRESSE(MAX(LIGNE(_a));MAX(COLONNE(_a))))(Feuil1!1.:.1048576)

D'où une nouvelle fonction sans VBA pour retourner l'adresse de la plage utilisée dans un fichier

A quoi ça peut servir?

Jusqu'à présent, je n'en ai jamais eu besoin, mais je peux envisager quelques exemples :

• Partout où la fonction VBA usedrange est utilisée, on peut la remplacer par .:.. Cela pourrait permettre d'éviter de devoir utiliser du VBA.
• Tester si des plages ont besoin d'être supprimées. Par exemple, on peut tester si la dernière ligne d'une feuille a été remplie, ce qui en général peut indiquer une erreur.
• Tester si des données ont été saisies en dessous ou à droite d'un tableau structuré. En général, ca n'est pas un gros problème, mais si vous n'utilisez pas des formules structurées, un somme(A:A) peut générer une erreur.

On peut aussi ajouter le nom de la feuille:

="'"&TEXTE.APRES(CELLULE("nomfichier";'Feuil 1'!A1);"]") & "'!" & LAMBDA(_a;ADRESSE(MIN(LIGNE(_a));MIN(COLONNE(_a)))&":"&ADRESSE(MAX(LIGNE(_a));MAX(COLONNE(_a))))('Feuil 1'!1.:.1048576)

On notera l'utilisation du ' en première position et devant le ! pour gérer le cas de la présence d'espaces dans le nom de la feuille.

Finalement, on peut vérifier que tout fonctionne bien avec la fonction INDIRECT.

Et vous, avez-vous des idées sur comment utiliser cette nouvelle fonction?

Allez, on sort la tête l'IA et on revisite quelques fondamentaux !

L'idée !

L'astuce est de passer par une mise en forme conditionnelle en barre de données

Processus pour des cas simples

Par exemple, on saisit 1 dans la cellule B5, puis on applique sur cette cellule une mise en forme conditionnelle avec des barres et ensuite on saisit 0 et 1 pour les limites. On notera aussi que "Afficher la barre uniquement" a été cochée pour ne pas afficher la valeur 1 de la cellule (demande initiale du client).

On obtient ceci :

Évidemment, dans la vraie vie, on veut avoir une valeur spécifique dans cette cellule, issue d'un calcul ou bien tapée à la main.

Donc, on peut modifier la solution pour prendre en compte cette valeur et soustraire 1 (par exemple) et ajouter 1 pour les calculs de limites. J'ai aussi décoché "Afficher la barre uniquement"

Il reste une petite subtilité à gérer, le cas des valeurs négatives.

Voici ce qui est retourné par défaut avec la valeur -20

Donc, on va donc cliquer sur le bouton "Valeur négative et axe..."

Et ensuite, on choisir les options suivantes :

Et qui ne va donc pas changer si on change la valeur de J5

Aller plus loin avec des cases à cocher.

On va compliquer un peu les choses en contrôlant avec des cases à cocher.

L'idée est toute simple, c'est de reprendre la même solution, mais en testant la valeur de la case à cocher avec une formule SI.

On a donc :

Et pour ne pas afficher le texte et gérer un contenu dynamique, on peut utiliser cette formule dans la mise en forme conditionnelle :

=SI($J$6;$K$6-1;NA())

et pour la limite haute

=SI($J$6;$K$6+1;NA())

N'hésitez pas à partager vos cas d'usage et vos expérimentations !

A très bientôt.