Archives de la catégorie : Divers

Voici un sujet qui est assez intéressant.

Comment combiner le résultat d'une plage dynamique dans une seule cellule et que cette cellule se recopie vers le bas?

La solution est assez simple, on va utiliser la fonction JOINDRE.TEXTE

Ca nous donne ceci :

=JOINDRE.TEXTE(CAR(10);;
         FILTRE(T_Ventes[ID Produits];[@[ID Client]]=T_Ventes[ID Client]))

où FILTRE renvoie la liste des ID produits, et joindre.texte permet de les combiner ensemble dans une seule cellule. J'utilise CAR(10) pour un retour à la ligne, mais on aurait aussi pu utiliser point-virgule. Si vous utilisez CAR(10), il faut ensuite formater avec "Renvoyer à la ligne automatiquement" dans le menu Accueil.
Ca nous donne ceci :

On peut aller plus loin en faisant l'inverse grâce à la fonction FRACTIONNER.TEXTE.

On a donc :

=JOINDRE.TEXTE(CAR(10);;
       RECHERCHEX(
          FRACTIONNER.TEXTE([@[Liste Produits]];;CAR(10))*1;
          T_Produits[ID Produit];
          T_Produits[Description]))

FRACTIONNER.TEXTE va permettre de scinder les ID sur plusieurs lignes, et ensuite RECEHERCHEX va permettre de faire la recherche pour chacun de ces IDs et ensuite retourner la description. On multiplie ici par 1, car les ID sont en numérique, mais FRACTIONNER.TEXTE renvoie du texte.

IMPORTANT, dans les deux cas de figure, il faut s'assurer que les séparateurs n'existent pas dans les données, sinon on va joindre ou séparer au mauvais endroit.

Voilà, j'espère que cela vous donnera des idées !

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?