Une astuce très productive : iii pour =INDEX(ColRésultat;EQUIV(ValCherchée;ColRecherche;0))

Ca faisait un moment que je n'avais pas vu une astuce qui fasse gagner autant de temps, donc je suis ravi de la partager !

Vous connaissez déjà les nombreuses discussions qui existent sur le sujet du Recherchev vs. Index/match, sinon, je vous invite à revoir ces deux résumés de la situation :
http://www.xlerateur.com/divers/2016/12/31/index-equiv-vs-recherchev-5477/
http://www.xlerateur.com/divers/2017/01/02/6-bonnes-raisons-dabandonner-recherchev-meme-si-vous-vous-appelez-flash-5514/

Donc, un des arguments pour l'utilisation de Recherchev est que c'est plus rapide à taper. Voici donc une astuce qui va vous permettre de résoudre cet inconvénient.

Dans Fichier - Options - Vérification - Options de correction automatique - Correction automatique,
et vous ajouter uen nouvelle option, comme par exemple : iii pour
=INDEX(ColRésultat;EQUIV(ValCherchée;ColRecherche;0))

Il ne vous reste plus qu'à taper iii, puis espace, puis à double cliquer sur les arguments pour compléter la formule.

C'est tout !

Source : https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/14049132-make-a-simple-safer-version-of-vlookup-and-index-m?page=4&per_page=20

A bientôt.

Gaëtan

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

Créer un « Tout » dans une formule sommeprod

Suite à la question d'un client, voici comment créer une SOMMEPROD qui permettent de prendre en compte un critère "(Tout)", un peu à la manière d'un tableau croisé dynamique.

Pour cela, on va utiliser la formule SI et ensuite créer une série de {VRAI;VRAI;VRAI}, ce qui va donc nous donner ceci :

=SOMMEPROD((Tableau1[Montant])*(SI(D1="(Tout)";Tableau1[Ville];D1)=Tableau1[Ville]))

Donc, si D1 est égal à "Tout"; alors on tester [Ville]=[Ville] et donc : {VRAI;VRAI;VRAI;VRAI}

Voici un petit fichier qui illustre cette question.

A bientôt.
Gaetan

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

Chercher une référence circulaire

Bonjour à tous,

Voici comment traquer une référence circulaire ?

Une référence circulaire, c'est lorsque vous faites référence à la même cellule (directement ou indirectement), et de fait le calcul devient circulaire, donc insoluble d'une certaine manière.
Par exemple ici :

Tout d'abord, et c'est contre-intuitif, l'erreur ne se trouve pas forcément ici :

Mais vous aurez plus de chance de la trouver là :

Vous pouvez ensuite analyser les cellules trouvées pour comprendre le problème.

Parfois, si la situation est assez complexe, SUR UNE COPIE DE SAUVEGARDE, supprimer les formules des références circulaires jusqu'à ce que celle-ci devienne grisé. Ca vous permet de remonter à la source.

Espérant que cela vous aide !

A bientôt.

Gaetan

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

[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 !