Les fonctions Somme.Si et Nb.Si

Dans l’article du 13 janvier, nous avons vu comment utiliser une formule matricielle. Aujourd’hui, nous découvrirons les fonctions Somme.Si() et
Nb.Si(), qui permettent de faire certaines des choses que l’on peut faire avec des formules matricielles.

Reprenons le même exemple mais cette fois-ci en calculant en colonne E le chiffre d’affaires relatif à chaque vente :

En C13, nous calculons le chiffre d’affaires pour la famille sélectionnée dans la cellule C12. En C14, nous calculons le nombre de factures émises pour cette famille. Les formules sont les suivantes :

En C13 : =somme.si(D2:D10;C12;E2:E10)

En C14 : =nb.si(D2:D10;C12)

Les syntaxes de ces deux fonctions sont donc :
=somme.si(matrice à tester;condition;matrice de résultat)
=nb.si (matrice à tester;condition)

Notez que si la condition n’est pas une valeur ou une référence à une cellule, elle doit être entrée sous forme de texte. Ainsi la formule de C14 aurait pu être =nb.si(D2:D10; "="&C12).

Remarquons enfin que, même si les syntaxes nous permettent de faire référence à des matrices – et cela marche ! –, il est plus courant d’utiliser des vecteurs à ce niveau.

« Outils Options », poubelle d’Excel

Dans mon message du 18 février, je vous ai parlé de la commande « Outils Options », qui est hélas devenue, surtout ces dernières années, la poubelle d’Excel…

Originellement, cette commande était censée regrouper toutes les commandes générales d’Excel, celles qui ne concernaient pas le classeur en cours, et étaient censées rester mémorisés quand on quittait Excel. C’est toujours le cas par exemple pour les réglages de l’onglet « Général ».

Le problème, c’est que les développeurs de Microsoft – chaque fois qu’ils ne savent pas où loger une commande – vont la cacher dans « Outils Options », ce qui revient tout bonnement à mettre la poussière sous le tapis.

Nous avons ainsi observé une inflation regrettable de cette commande entre la version 2000 et la version XP. Pas moins de cinq onglets supplémentaires !

Il y maintenant tant de choses cachées dans cette commande que je vous conseille d’aller y faire un petit tour pour voir si vous n’y trouverez pas quelques pépites cachées, comme le réglage pour les graphes dont je vous ai parlé dans le dernier message.

Ceci dit, je déplore cette fâcheuse politique de Microsoft. De nombreux réglages qui s’y trouvent n’actuellement n’ont absolument rien à faire dans la commande « Outils Options ». Cela ne peut que brouiller les pistes et créer de la confusion chez les pauvres utilisateurs d’Excel.

Chaque fois que Microsoft fait ainsi de mauvais choix pour Excel, je peste en tant qu’utilisateur mais j’apprécie – en tant que consultant – ces occasions que Microsoft me donne de mieux aider mes clients.

Un peu de schizophrénie ne peut pas faire de mal, comme le disait mon alter ego…

Liste des fichiers du répertoire

Il est parfois intéressant de connaître la liste des fichiers d’un répertoire, ne serait-ce que pour savoir si un fichier que l’on cherche s’y trouve.

Contrairement à ce que pensent presque tous les utilisateurs d’Excel, on peut atteindre cet objectif sans passer par une macro VBA.

Il suffit pour cela d’exploiter une particularité d’Excel dont je vous ai parlé le 8 février dernier, la possibilité de définir un nom par une macro Excel 4.

Définissez donc le nom « Fichiers » comme suit, en utilisant la commande macro =fichiers() d’Excel 4.

Pour avoir la liste des fichiers du répertoire actif dans la colonne A de votre feuille, il suffit d’entrer en A1 la formule suivante, puis de la recopier vers le bas, assez loin pour que tous les fichiers du répertoire puissent être listés : =si(ligne()<=nbval(fichiers);index(fichiers;ligne());"")

Le si() nous permet d’éviter le message d’erreur qui apparaît autrement quand il n’y a plus de fichier dans le répertoire.

La fonction ligne() possède une particularité rare dans Excel, elle peut au choix avoir un argument – pour indiquer la ligne où se trouve la cellule référencée – ou aucun argument, pour indiquer le numéro de la ligne active.

Un graphe toujours visible

L’écran ci-dessous représente une situation fréquente. Vous avez un tableau comportant des résultats de ventes de plusieurs produits sur plusieurs mois. Et vous avez décidé, pour des raisons bien à vous, de masquer les colonnes de février et mars.

Catastrophe ! La partie du graphe correspondant à ces données tombe dans le triangle des Bermudes :

Heureusement, il y a une parade, très habilement cachée par Microsoft dans la poubelle d’Excel, la commande « Outils – Options ». Il suffit de cliquer préalablement dans le graphe, puis de faire « Outils – Options – Graphique » et enfin de décocher « Tracer les cellules visibles seulement ».

Cette commande est placée à un endroit totalement ridicule. De plus, elle est logée dans la commande « Outils – Options » qui normalement devait regrouper les commandes générales d’Excel et ne s’intéresser en aucun cas à quelque chose d’aussi particulier qu’un graphe actif.

Si je pouvais établir le cahier des charges d’un nouvel Excel, il y aurait des changements notables !

Définir un nom relatif

Nous avons vu dans notre rubrique du 8 février qu'un nom est par défaut absolu. Nous allons voir aujourd'hui comment le rendre relatif.

Dans le modèle ci-dessous, sélectionnons la ligne 6, puis utilisons la commande Insertion – Nom – Définir (oui, je sais, ce n’est pas la façon la plus rapide de définir un nom, mais ici elle est appropriée), puis entrons le nom « préc ». L’écran ci-dessous illustre la situation actuelle.

Sélectionnons le bloc du bas (Fait référence à) et modifions la partie droite de la formule de façon à obtenir « =Feuil1!5:5 ». Et enfin validons par « OK ».

Nous venons de créer un nom relatif faisant référence à la ligne précédente. Cliquons par exemple dans la cellule C8 puis déroulons la zone Nom – à gauche de la barre de formule – et sélectionnons « préc ». Aussitôt, la ligne 7 est sélectionnée ! Re-sélectionnons « préc » dans la zone Nom, et c’est maintenant la ligne 6 qui s’active. Le nom « préc » fait donc bien référence à la ligne précédente.

Sachant cela et sachant aussi – cf. la dernière rubrique – qu’un espace, dans Excel, représente l’intersection, nous avons maintenant de quoi créer une super-formule pour la colonne C. Supposons que nous ayons attribué le nom « Ventes » à la colonne B.

La formule de C3, reproduite ensuite vers le bas, est tout simplement :
= Ventes/Ventes préc–1

Voilà une formule explicite, facile à auditer !

La définition des noms

La méthode traditionnelle pour définir un nom dans Excel revient à sélectionner la cellule (ou les cellules) devant correspondre à ce nom, puis à passer par la commande Insertion – Noms – Définir, et enfin à taper le nom et à valider. Dans le cas ci-dessous, nous avons sélectionné, en maintenant la touche [Ctrl] enfoncée, le bloc A1:B4, puis le bloc C7:D8 :

Un nom peut ainsi correspondre à une cellule, à une matrice, à plusieurs cellules, à plusieurs matrices... Un nom ne peut pas inclure d’espace, car celui-ci est pour Excel un opérateur représentant l’intersection (au sens de la théorie des ensembles).

Par défaut, un nom est absolu (les « $ » viennent automatiquement) et lié à une feuille de calcul précise, celle qui est active lors de sa définition. Nous verrons dans d’autres rubriques que des noms peuvent être relatifs (sans
« $ »), et peuvent aussi être liés à la feuille courante.

En fait, un nom peut aussi contenir une formule, ou même une commande macro Excel 4, ce dont nous parlerons certainement un jour ou l’autre.

La méthode la plus rapide pour définir un nom revient à sélectionner tout d’abord la zone à nommer et, au lieu de passer par la commande Insertion – Noms – Définir, à cliquer dans la zone Nom – celle qui est placée à gauche de la barre de formules – à taper le nom, puis à valider par [Entrée].

Notons enfin que l’on peut définir des noms en rafale avec la commande Insertion – Noms – Créer.

Mes formations en avril 2006

Avez-vous envie de progresser ?

Voilà quelques détails complémentaires sur le contenu de mes prochaines formations…

La modélisation du risque (Crystal Ball), le mercredi 5 avril 2006
Pour découvrir la modélisation du risque à l’aide d’Excel et de l’add-in Crystal Ball. Vous pouvez acquérir des connaissances similaires avec mon livre « La modélisation du risque », chez Economica. En savoir plus


Utilisation de Crystal Ball Pro, le jeudi 6 avril 2006
Apprenez à utiliser les modules d’optimisation, d’analyse de séries chronologiques et de prévision, et de programmation, de la version Crystal Ball Pro. En savoir plus


Modélisation avec Excel, lundi 3 et 10 avril 2006
En deux jours, passez du niveau d'utilisateur lambda (ou même évolué) d'Excel à celui d'expert Excel (hors programmation VBA). J’anime aussi,
à la demande, des formations d’initiation au VBA en deux journées consécutives. En savoir plus


Powerpoint et Word efficaces, mercredi 12 avril 2006
Si vous avez envie d’utiliser Powerpoint et Word de façon aussi astucieuse que ce que je vous montre avec Excel dans ce blog, c’est l’occasion ou jamais ! Avec ces logiciels aussi, il y a de nombreuses astuces à connaître pour travailler de façon efficace. En savoir plus


Remarques générales
Ces formations ont toutes lieu chez Eurosites – 55, rue de Rivoli – 75001 Paris.
Pour chacune de ces formations, je m'engage à ce qu'il y ait un ordinateur par personne et au maximum 8 participants.

Figez la police de vos graphes

Il y a deux façons de loger un graphe : sur une feuille de calcul ou sur une feuille graphique. Pour ma part, j’utilise exclusivement la seconde, pour des raisons que je commenterai lors d’un prochain message.

Vous créez donc un graphe dans votre feuille de calcul, puis vous le déplacez exactement là où vous souhaitez le voir. Enfin, vous le dimensionnez juste comme cela vous convient.

Et patatras ! Vous constatez qu’Excel ajuste automatiquement la taille de la police du graphe à la taille redimensionnée du graphe.

Le résultat est horrible, surtout si vous avez plusieurs graphes sur la même feuille. En effet, vos graphes auront alors des tailles de police différentes, ce qui est au mieux inesthétique, au pire franchement déplaisant.

Comment désactiver ce réglage par défaut d’Excel que je trouve vraiment mal venu ?

Faites un clic droit sur le graphe pour faire apparaître son menu contextuel, puis sélectionnez la commande « Format de la zone de graphique », puis l’onglet « Police » :

Et vous trouvez le fautif en bas et à gauche de la fenêtre, l’affreux réglage
« Echelle automatique » que vous allez vous empresser de désactiver...

Maintenant, vous êtes tranquille, vous pouvez enfin redimensionner tous vos graphes sans souci pour la taille des polices.