Une calculette dans Excel...

Aussi surprenant que cela puisse paraître pour un tableur aussi puissant qu’Excel, vous pouvez quand même enrichir votre tableur favori en lui ajoutant une calculette.

Comment ajouter une calculette ?

Faites un clic droit dans la bande grisée des barres d’outils – c’est plus rapide que de faire « Affichage - Barres d’outils » – puis sélectionnez la dernière commande : « Personnaliser… ». Cela vous met en mode de personnalisation des barres d’outils.

Sélectionnez alors l’onglet « Commandes », la catégorie « Outils », puis la commande « Personnaliser » avec une calculette à gauche (la seconde dans la partie droite de l’écran ci-dessous) :

Il ne reste plus qu’à tirer cette calculette dans la barre d’outils de votre choix, à la position qui vous convient, et à fermer la personnalisation. Vous avez maintenant une calculette en accès direct :

A quoi peut servir une calculette dans Excel ?

Est-il ridicule d’utiliser une calculette alors que le tableur vous offre des possibilités tellement plus riches ?

Mais non ! Il y a une situation où cela peut vous rendre des services : quand un dialogue Excel est ouvert à l’écran, vous pouvez ouvrir, utiliser puis fermer la calculette, sans devoir pour autant fermer le dialogue ouvert à l’écran. CQFD !

Recherche H ou V avec « faux »

Un problème se présente cependant avec le modèle du 24 mars. Qu’arrive-t-il si l’on entre en F14 un prénom erroné, par exemple « Jules » ?

Eh bien, tout logiquement, nous récupérerons les résultats pour « Jacques Lerose », puisque « Jacques » est le dernier prénom inférieur ou égal à
« Jules ».

Là nous constatons qu’il y a un problème. En effet, avec la table de tarifs postaux, les valeurs intermédiaires sont acceptables : tout envoi entre 100 gr. et 250 gr. coûte effectivement 1,22 €. En revanche, un prénom entre
« Jacques » et « Léon » ne correspond pas à « Jacques ».

Il est donc souhaitable de pouvoir exclure – dans un tel cas – les valeurs intermédiaires. C’est une fonctionnalité qui n’était pas présente dans la première version de RechercheH() et RechercheV(), mais qui a été ajoutée par la suite. L’astuce revient à ajouter un quatrième argument avec la valeur FAUX, pour indiquer à Excel que l’on refuse les valeurs intermédiaires.

Nouvelle formule de F15 : =recherchev(F14;E3:F11;2;faux)

Si le prénom n’est pas présent dans la liste, la valeur « #NA » apparaît alors…

Remarque 1

Quand il n’y a pas de quatrième argument, cela se passe comme si ce quatrième argument était VRAI : les valeurs intermédiaires sont alors acceptées.

Remarque 2

Microsoft ne vous le dit pas dans l’aide mais, quand il y a FAUX en quatrième argument, l’algorithme utilisé est différent. Dans ce cas, Excel descend la colonne de recherche jusqu’à ce qu’il trouve la valeur cherchée et – dès qu’il l’a trouvée – s’arrête et renvoie ce qui est dans cette ligne et dans la colonne demandée.

Corollaires de la remarque 2

Etant donné l’algorithme utilisé quand il y a FAUX en dernier argument, il est évident que, dans ce cas, il n’y a plus aucune nécessité pour que la colonne de recherche soit triée en ordre croissant.

S’il y a plusieurs « Gilles » dans la liste, c’est maintenant le premier qui sera retenu…

Remarque 3

Ce dernier argument avec FAUX fonctionne avec RechercheV() et avec RechercheH(), mais pas avec Recherche(). Pour obtenir le même résultat avec Recherche(), il faut écrire : =si(recherche(valeur);vecteur_recherche)=
valeur;recherche(valeur;matrice_recherche);na())

Les fonctions Recherche H et V

Dans les messages du 16 et du 18 mars, nous avons vu comment fonctionnait la fonction Recherche(), avec ses deux syntaxes.

Une des difficultés de la fonction Recherche(), pour certains utilisateurs, tient à ce qu’elle fonctionne en colonne ou en ligne selon la forme de la matrice.

Pour résoudre ce problème, après la création de la fonction Recherche(), qui était d’ailleurs présente dans le premier Visicalc sur Apple II en 1979, Microsoft a créé deux nouvelles fonctions, RechercheV() et RechercheH() qui – comme leur nom l’indique – pratiquent exclusivement des recherches verticales et horizontales.

Il a été décidé, par la même occasion, de résoudre une autre difficulté pour certains utilisateurs, le fait que la recherche verticale ramenait le contenu de la cellule la plus à droite dans la matrice pour la ligne sélectionnée.

C’est pourquoi la syntaxe de la fonction est : =recherchev(valeur;matrice;n°col) où n°col est le numéro de la colonne dont on doit ramener la valeur, sachant que ce numéro de colonne est relatif à la matrice.

Pour la recherche horizontale, il suffit de remplacer n°col par n°lig…

Dans l’exemple ci-dessus, nous cherchons à récupérer le nom de famille et le salaire d’une personne à partir de son prénom. Les formules utilisées sont :

En F15 : =recherchev(F14;E3:F11;2)

En F16 : =recherchev(F14;E3:G11;3)

Remarque 1 – Une seule matrice

Puisque nous indiquons le numéro de la colonne à ramener, nous aurions pu utiliser le même second argument E3:G11 (ou, mieux encore, le nom de ce bloc) dans les deux formules.

Remarque 2 – Et Gilles, alors ?

Vous avez probablement noté la présence de deux Gilles. Lequel sera retenu par nos deux formules ? Le dernier, évidemment, si vous avez bien compris l’algorithme présenté le 18 mars : on descend jusqu’à trouver le premier prénom plus grand, Hervé, puis l’on remonte d’une ligne, ce qui nous fait immanquablement arriver au dernier Gilles.

Remarque 3 – Une erreur possible

Une grosse erreur aurait été d’utiliser la formule =recherchev(F15;F3:G11;2) en cherchant le salaire à partir du nom. En effet, les noms ne sont pas en ordre alphabétique et vous obtenez alors n’importe un résultat souvent – mais pas toujours – faux, vu la façon dont marche l’algorithme de recherche.

Abonnez-vous à ce blog !

Dans ce blog, je vous prodigue régulièrement, depuis octobre 2005, des conseils sur Excel, sans pour autant fournir les fichiers Excel illustrant la mise en œuvre de ces conseils.

A la demande de plusieurs d’entre vous, j’ai décidé de proposer un service qui s’appellera « La lettre d’Excel », selon lequel vous recevrez par mail, tous les deux mois – cela passera à tous les mois dès que j’aurai 100 abonnés – un fichier Acrobat regroupant tous les conseils du mois, ainsi qu’un classeur Excel illustrant la mise en œuvre des conseils et la complétant par d’autres exemples. La première feuille de cet onglet servira de gare d’aiguillage et permettra de retrouver rapidement tel ou tel conseil.

De plus, le texte et la mise en page des messages sont légèrement modifiés dans la version Acrobat, de façon à tirer le meilleur parti possible du format A4.

Enfin, d’autres « goodies » bien choisis seront fournis : modèles Excel, macros, applications…, en freeware ou shareware.

L’abonnement annuel est de 150 euros TTC (TVA de 19,6%) et peut être payé directement à la société Logma, par chèque ou par carte bleue. Dans ce dernier cas, indiquez votre numéro de carte bleue et sa date limite de validité. Les abonnements débutent à la date de création du blog.

Logma SA
12, rue d’Anjou
78000 Versailles

Vous pouvez aussi commander, pour vous faire une idée, la première livraison, celle du dernier trimestre de l’année 2005, au prix de 35 euros TTC.

Pour les entreprises, en cas de prises d’abonnement simultanées, le second abonnement est à 100 euros TTC, le troisième à 75 euros TTC et les suivants à 50 euros TTC. Un abonnement « entreprise » peut être négocié, auquel cas l’entreprise acquiert le droit de reproduire en interne et/ou de mettre sur son Intranet les fichiers Acrobat et les fichiers Excel.

Pour la négociation de votre contrat « entreprise », contactez-moi directement à thiriez@hec.fr.

Plus sur la fonction Recherche()

Il y a deux jours, nous avons introduit la fonction Recherche(), mais nous n’avons pas tout dit sur ce sujet !

Nous avons présenté l’algorithme de la fonction recherche() en disant qu’Excel descendait au fur et à mesure le long de la première colonne, jusqu’au premier seuil supérieur à la valeur cherchée, puis remontait d’un cran et ramenait la valeur la plus à droite dans la même ligne.

En faut, c’est ce qui se passe quand la matrice de recherche est « verticale », c’est-à-dire possède plus de lignes que de colonnes. Quand la matrice est
« horizontale », Excel se déplace de gauche à droite le long de la première ligne, jusqu’au premier seuil supérieur à la valeur cherchée, puis revient à gauche d’un cran et ramène la valeur la plus basse dans cette colonne.

Il y a deux inconvénients à cela. Premièrement, si la table de recherche est verticale, puis que l’on insère plusieurs colonnes entre sa première et sa dernière colonne, elle peut devenir horizontale, auquel cas la formule de recherche ne donnera plus le résultat espéré. Ensuite, il est difficile de faire comprendre à certains utilisateurs que la même fonction peut, selon le cas, fonctionner en vertical ou en horizontal.

Pour éviter ces deux problèmes, Microsoft a décidé un beau jour d’ajouter une seconde syntaxe à la fonction recherche, la syntaxe =recherche(valeur;vecteur de recherche;vecteur de résultat).

Dans ce cas, Excel identifie dans le premier vecteur la position du dernier seuil inférieur ou égal à la valeur cherchée, puis ramène – depuis le second vecteur – la valeur qui se trouve dans la même position.

Pour la formule de C13, nous avons donc le choix entre les formules suivantes :
=recherche(C12;B3:C10) … dans la matrice verticale
=recherche(C12;C15:J16) … dans la matrice horizontale
=recherche(C12;B3:B10;C3:C10) ... avec les vecteurs verticaux
=recherche(C12;C15:J15;C16:J16) ... avec les vecteurs horizontaux

Mais rien ne vous empêche – quoique je ne le conseille pas, pour la lisibilité de votre modèle – d’être plus créatif avec une formule telle que =recherche(C12;B3:B10;C16:J16) qui cherche dans un vecteur vertical pour ramener le résultat depuis un vecteur horizontal !

Intro à la fonction Recherche()

Prenons l’exemple du tarif postal ci-contre : nous voulons obtenir en C13 les frais d’expédition d’un colis dont le poids est indiqué en C12.

Nous pourrions entrer la formule :
=si(C12<B4;C3;si(C12<B5;C4;
si(C12<B6;C5;si(C12<B7;C6...

Une telle formule est tout à fait légale mais présente deux inconvénients majeurs.

Tout d’abord, elle est totalement indigeste, sans compter qu’il y a une limite au nombre de fonctions que l’on peut imbriquer.

Mais surtout, elle n’est pas flexible du tout : si l’on insère une nouvelle catégorie pour 750 gr., la formule est à reprendre.

La bonne formule est ici : =recherche(C12;B3:C10), selon la syntaxe =recherche(valeur;matrice).

Pour bien utiliser la fonction Recherche(), il importe de connaître l’algorithme qui est à la base de son fonctionnement. En fait, Excel compare le poids visé (ici, 2.641 g.) à 0 gr. Si le poids visé est supérieur ou égal, Excel compare au poids suivant, 50 gr. Puis au poids suivant... jusqu’à ce qu’il trouve un poids supérieur au poids visé.

Quand il le trouve enfin, Excel remonte d’une ligne et ramène le résultat le plus à droite de cette ligne dans la table sélectionnée, ici 4,83.

S’il ne trouve pas de poids supérieur, la valeur de la cellule en bas et à droite de la table est retenue.

Conséquence n°1 : il faut que la première colonne soit triée en ordre croissant pour que notre objectif puisse être atteint.

Conséquence n°2 : si le poids visé est inférieur au premier poids de la table, on obtient une erreur : en effet, Excel sort alors de la table en essayant de remonter d’une ligne.

Deux réglages généraux d'Excel

Dans la commande Outils – Options – Général, il y a deux réglages par défaut de Microsoft que je trouve particulièrement ineptes ; trop souvent hélas, les utilisateurs d’Excel ne pensent pas à les corriger. Ce sont les réglages concernant la liste des derniers fichiers utilisés et le nombre de feuilles de calcul par nouveau classeur.

Les réglages que je vous conseille en la matière sont ceux de l’écran ci-dessous :

Liste des derniers fichiers utilisés

Excel n’affiche par défaut que les quatre derniers fichiers utilisés, ce qui est ridicule. Autant en afficher le maximum autorisé, c’est-à-dire neuf !

Vous pouvez d’ailleurs en profiter pour effectuer le même réglage sur Word et Powerpoint…

Nombre de feuilles de calcul par nouveau classeur

Là encore, on ne voit pas bien l’intérêt d’afficher trois feuilles par défaut alors qu’il est facile d’ajouter une feuille, soit en utilisant la commande Insertion – Feuille, soit par le raccourci [Maj]-[F11].

Vous avez donc intérêt à rapidement modifier ces deux réglages par défaut d'Excel !

Affichage d’indicateurs graphiques

Quand on compare des ventes réalisées (colonne B) par rapport à des objectifs (colonne C), on souhaite souvent disposer d’indicateurs graphiques permettant d’identifier d’un coup d’œil la qualité des résultats.

Rien de plus simple ! Il suffit pour cela de créer une table – ici, en G2:H7 – dans laquelle on entre les différents seuils et indicateurs.

La formule de E2, recopiée ensuite vers le bas, est: =recherche(D2;$G$2:$H$7).

Vous n’avez plus qu’à rechercher dans les polices graphiques (Symbol, Windings, ZapfDingbats ou autres) les symboles qui vous intéressent...

Somme.Si() : des trucs en plus

Pour en terminer avec les fonctions somme.si() et nb.si(), il faut savoir que la condition doit toujours être une condition unique. Si ce n’est pas le cas, il ne vous reste plus qu’à utiliser soit une formule matricielle (cf. notre article du 13 janvier), soit la fonction SommeProd(), dont je vais vous parler dans un avenir proche.

En revanche, il est tout à fait possible d’exprimer une grande variété de conditions simples. Nous vous en proposons deux exemples ci-dessous :

En C16, nous avons ainsi calculé le chiffre d’affaires réalisé avec d’autres commerciaux que A, grâce à la formule : =somme.si(D2:D10;"<>"&C12;E2:E10).

En C18, nous avons calculé la somme des chiffres d’affaires supérieurs à la moyenne, avec : =somme.si(E2:E10;">"&moyenne(E2:E10);E2:E10).

Les colonnes F et G sont totalement superflues pour ces calculs. Nous les avons mises là pour que vous puissiez vérifier que les résultats des cellules C16 et C18 sont exacts.

Enfin, si l’envie vous vient un jour d’évaluer une « moyenne si », il suffit tout benoîtement de diviser pour cela une somme.si() par un nb.si() !