[2016 et +] Lister les lignes des cellules vides dans une seule cellule

Téléchargez le fichier ici
Une réponse possible pour 2016+, à valider avec CTRL+MAJ+Entrée :
=CONCAT(SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester])&" ";""))
ou encore :
=JOINDRE.TEXTE("-";VRAI;SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester]);""))


En cherchant un moyen de renvoyer toutes les cellules vides d'une plage, je suis tombé sur cette formule qui permet de renvoyer la première ligne :

A valider par CTRL+MAJ+Entrée
=MIN(SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester]);""))
Et si on décompose :
=MIN(SI({VRAI;FAUX;VRAI;FAUX;VRAI;FAUX};{2;3;4;5;6;7});""))
D'où :
=MIN({2;"";4;"";6;""})
et donc :
2

De là, me dis-je, on devrait pouvoir arriver à concaténer l'ensemble de :
SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester]))
Ce qui nous donnerait la solution.

Malheureusement, CONCATENER ne semble pas fonctionner comme formule matricielle, MAIS, la nouvelle fonction CONCAT ou JOINDRE.TEXT peut le faire 🙂

D'où
A valider avec CTRL+MAJ+Entrée
=CONCAT(SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester])))
qui renvoit donc :
246

Et si on veut séparer les lignes avec un espace (CTRL+MAJ+Entrée) :
=CONCAT(SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester])&" ";""))

Ou encore avec JOINDRE.TEXTE
=JOINDRE.TEXTE("-";VRAI;SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester]);""))

Et si on veut récupérer l'adresse exacte (CTRL+MAJ+Entrée)
=CONCAT(SI(Tableau1[Plage à tester]="";ADRESSE(LIGNE(Tableau1[Plage à tester]);COLONNE(Tableau1[Plage à tester]))&" ";""))
qui renvoit :
$A$2 $A$4 $A$6

En espérant que cela vous ouvre pleins d'horizons !

Je n'ai pas trop cherché, mais si vous connaissez la solution pour les versions antérieures à 2016, n'hésitez-pas.

A bientôt

Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Gérer un mini-mini budget dans une cellule

Bonjour à tous,

Entre l'enregistrement de deux vidéos sur les tableaux croisés dynamiques, j'avais besoin de faire travailler un peu les neurones du VBA 🙂

Donc, suite à une discussion avec Dominique qui me rappelait l'utilisation de la formule N() pour laisser un commentaire dans une formule, m'est venu l'idée de gérer tout cela via un formulaire.
Par exemple, vous pouvez entrer une formule comme ceci :

=N("Solde Départ")+1000+N("Train")-90+N("Nouveau")-98

qui va renvoyer 812 (=1000-90-98).

Cette formule permet donc de conserver la signification des valeurs entrées.

En VBA, ca nous donne donc ce formulaire :

Par exemple, cela vous permet de gérer un mini-budget dans une cellule pour savoir où vous en êtes d'un prévisionnel.

Vous pouvez télécharger le fichier ici et faire un double-clic sur n'importe quelle cellule.

Pour l'insérer dans vos propres fichiers. Voici la procédure en vidéo :

Je vais certainement en faire une capsule vidéo VBA si l'explication détaillée du code vous intéresse.

Si vous voyez d'autres utilisations possibles de ce type de formules/formulaires, n'hésitez-pas à commenter.

A bientôt.

Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Lobbying Microsoft

J'en avais déjà parlé précedemment, mais vous pouvez faire entendre votre voix sur les nouvelles fonctionnalités, bugs à corriger et autres nouvelles fonctions que vous voudriez voir apparaître dans Excel.

Ca se passe ici :
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/filters/top

Et forcément, ma favorite :
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/14049132-make-a-simple-safer-version-of-vlookup-and-index-m

A bientôt

Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

A propos d’Excel et de l’intelligence artificielle (en Anglais)…

Pas tout à fait de l'Excel, mais quand même très approprié quand on peut pressentir un avenir/présent (?) où nos feuilles de calculs seront créées automatiquement par de l'intelligence artificielle...D'ailleurs, si vous avez des références à ce sujet, cela m'intéresse grandement 🙂

Quelques questions qui me taraudent suite au visionnage de ce TED:
- Comment va-t-on "combiner nos forces", les forces de l'humain et celles de l'intelligence artificielle dans un contexte Excellien?
- Comment et est-ce-que nos compétences à "coacher" nos machines intelligentes vont (ou pas) rendre le processus global plus efficace/intelligent/productif/utile (rayer les mentions inutiles !) comparé à du pur humain (sans IA) ou à de la pure IA sans coach?

From Gasparov:
"And I reached this formulation. A weak human player plus a machine plus a better process is superior to a very powerful machine alone, but more remarkably, is superior to a strong human player plus machine and an inferior process. This convinced me that we would need better interfaces to help us coach our machines towards more useful intelligence."

Traduction libre aidée par une intelligence artificielle googlienne !
"Et j'ai atteint cette formulation. Un joueur humain faible, plus une machine, plus un meilleur processus, est supérieur à une machine très puissante seule, mais plus remarquable, est supérieure à un joueur humain fort, plus une machine et un processus inférieur. Cela m'a convaincu que nous aurons besoin de meilleures interfaces pour nous aider à "coacher" nos machines vers une intelligence plus utile."

Et plus spécifiquement sur Excel, quelques thèmes...
- Utilisation de l'IA pour vérifier la cohérence interne des classeurs déjà existants.
- Utilisation de l'IA pour proposer des nouvelles analyses, de nouveaux modèles ou de nouvelles structures dans le cadre d'un tableur.
- Abandon complet d'Excel au profit des seuls résultats livrés par l'IA hors-Excel.
- Utilisation d'API d'intelligence artificielle dans Excel ou en utilisant une combinaison des deux, un peu sous la forme de macros complémentaires.

Évidemment, il y a aussi l'utilisation d'Excel pour construire des IA, ou tout au moins pour en comprendre les fondements. De nombreuses références existent déjà sur le net.

N'hésitez pas à partager vos idées et commentaires.

Gaëtan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Mise en forme conditionelle si zéro, mais pas quand la cellule est vide

Bonjour à tous,

Voici une astuce intéressante (merci Fanny), lorsque vous voulez mettre en rouge une cellule qui contient un zéro, l'approche classique va aussi mettre en rouge les cellules vides :

Pour mettre uniquement la cellule contenant un zéro en rouge, on peut donc utiliser la formule suivante :
=ET(B1<>"";B1=0)

Si vous connaissez une autre méthode, n'hésitez-pas !

A bientôt.

Gaëtan Mourmant

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !