Monsieur Excel
Pour tout savoir faire sur Excel !

29 mars 2015

Somme.Si.Ens() & Nb.Si.Ens()

Avec Excel 2007, Microsoft nous a donné deux nouvelles fonctions de calcul, qui enrichissent les fonctions Somme.Si() et Nb.Si() qui ont fait l’objet de nos articles récents du 17 et du 23 mars.

Il s’agit des fonctions Somme.Si.Ens() et Nb.Si.Ens() dont l’aide ne dit pas ce que signifie le « Ens » ainsi ajouté : Ensemble ?

L’exemple ci-dessous illustre leur utilisation avec à titre de comparaison le même résultat obtenu à chaque fois avec une fonction BD…().


Les formules utilisées sont les suivantes :

Personnes de 27 ans :
I5 : =BDSOMME(A1:F26;E1;H1:H2)
I6 : =SOMME.SI.ENS(E:E;D:D;27)
I11 : =BDNB(A1:F26;E1;H1:H2)
I12 : =NB.SI.ENS(D:D;27)

Hommes de 27 ans :
I8 : =BDSOMME(A1:F26;E1;H1:I2)
I9 : =SOMME.SI.ENS(E:E;D:D;27;A:A;"m")
I14 : =BDNB(A1:F26;E1;H1:I2)
I15 : =NB.SI.ENS(D:D;27;A:A;"m")

Remarque 1 – En fait, ces nouvelles fonctions ne font rien que l’on ne sache déjà faire avec une fonction BD…() mais elles évitent la création d’une zone de critères comme H1:H2 ou H1:I2, selon le cas. Ceci dit, cette simplification a un inconvénient car elle aboutit à une moindre lisibilité du modèle : il faut en effet voir la formule pour comprendre d’où vient le résultat.

Remarque 2 – Faites très attention, car le champ sur lequel on fait la somme est en première position dans Somme.Si.Ens() alors qu’il était en troisième position dans Somme.Si(). Microsoft a été obligé de changer sa place car il n’y a qu’un champ de sommation alors qu’il peut y avoir plusieurs doublons « colonne;valeur ».

23 mars 2015

Somme.Si() : des variantes

Pour en terminer avec les fonctions somme.si() et nb.si(), il faut savoir que la condition exprimée dans le second argument doit toujours être une condition unique. Si ce n’est pas le cas, il ne vous reste plus qu’à utiliser soit une formule matricielle – dont nous avons vu de multiples exemples dans ce blog –, soit la fonction SommeProd(), dont nous avons aussi parlé à de multiples reprises.

Même si somme.si() et nb.si() nous limitent à une condition unique, il est tout à fait possible d’exprimer, à travers une telle condition, une grande variété de conditions simples. Nous vous en proposons deux exemples ci-dessous, en C16 et en C18 :


En C16, nous avons calculé le chiffre d’affaires réalisé avec les commerciaux autres 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, grâce à la formule : =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 » et si vous n'avez pas Excel 2013 qui a introduit cette fonction, il suffit tout simplement pour cela de diviser pour cela une somme.si() par un nb.si() !

17 mars 2015

Somme.Si() et Nb.Si()

Dans de nombreux articles de ce blog, nous utilisons des formules matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée]. Ces formules sont très puissantes, mais leur utilisation n’est pas à la portée du premier venu.

Aujourd’hui, nous découvrons les fonctions Somme.Si() et Nb.Si(), qui permettent de faire – avec des formules non matricielles, donc plus simplement – certaines des choses que l’on peut faire avec des formules matricielles.

Dans le tableau ci-dessous, nous cherchons à calculer le chiffre d'affaires et le nombre de factures associés à une famille de produits sélectionnée en C12 via un menu déroulant, une validation de cellule par liste avec la liste A;B;C.


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)

Si la condition n’est pas une valeur ou une référence à une cellule, elle doit être entrée sous forme de texte.

Remarque 1 – La formule de C14 aurait aussi pu être =nb.si(D2:D10;"="&C12).

Remarque 2 – Même si les syntaxes nous permettent de faire référence à des matrices – et cela fonctionne ! –, il est plus courant d’utiliser des vecteurs à ce niveau.

11 mars 2015

Les valeurs en double, en mieux

Dans l’article précédent, nous avons vu comment définir rapidement un format conditionnel qui met en relief soit toutes les valeurs présentes dans au moins deux cellules de la sélection, soit toutes les valeurs uniques.

Je concluais l’article en disant que je déconseillais de définir des formats conditionnels sans passer par la commande « Gérer les règles ». Nous verrons donc ajourd’hui comment, en utilisant cette commande, nous pouvons obtenir le même résultat et – pourquoi pas ? – faire encore mieux au passage…

Voici la solution que je propose pour définir un format conditionnel dépendant d’une formule – ce qui permet de passer par « Gérer les règles… » :

Du coup, j’en ai profité pour mettre en E1 le nombre de répétitions que l’on veut mettre en relief. Avec « 1 », on sélectionne les valeurs uniques ; avec « 2 » celles qui sont exactement doublées, et ainsi de suite…


J’ai ajouté un curseur, officiellement une « toupie », en contrôle de formulaire qui permet de faire varier les valeurs de E1 de 0 à 3.

Quand E1 est à « 0 », les cellules B7 et C7 affichent le format conditionnel, alors qu’elles sont toutes les deux identiques… Mais on sait que la notion de vide dans Excel est complexe.

L’article « La dernière non vide ou la dernière non nulle ? » du 13 octobre 2012 abordait d’ailleurs à ce sujet un thème connexe…



05 mars 2015

Les valeurs en double, rapidement

Dans le tableau ci-dessous, nous avons listé les étudiants ayant rendu tel ou tel devoir. Nous souhaitons mettre en relief rapidement tous ceux ayant rendu au moins deux devoirs.

Cela peut se faire en quelques secondes à l’aide de la commande de format conditionnel représentée en bas de la copie d’écran. Cette commande permet de mettre en relief soit les valeurs uniques, soit les valeurs présentes au moins à deux reprises.


Avantage

L’avantage principal de cette technique est son extrême rapidité : il ne faut que quelques secondes pour obtenir le résultat désiré.

Inconvénient

Pour ma part, je déconseille en général de définir un format conditionnel sans passer par la commande « Gérer les règles ». 

En effet, cette commande est la seule commande de format conditionnel qui permet, lors de la définition d’une règle, de vérifier si l’une ou plusieurs des cellules concernées contient déjà d’autres formats conditionnels. Et cela me semble très important !

28 février 2015

Recherche sur >255 caractères

Les fonctions de recherche, aussi bien recherche() – et ses cousins rechercheH() et rechercheV() – que la fonction equiv() – sont tout à fait incapables de trouver un résultat quand la chaîne recherchée contient plus de 255 caractères.

Pour illustrer cela, nous avons mis en scène l’exemple suivant.


En A1, un texte contenant l’alphabet.
En A2, recopiée vers le bas, la formule =A1&$A$1.
En B1, recopiée vers le bas, la formule =nbcar(A1).

Formule de C1 : =equiv(A1;A:A;0)
Formule de D1 : =recherchev(A1;$A$1:$B$14;2;faux)

Manifestement, quand on copie ces deux formules vers le bas, on constate qu’elles ne fonctionnent plus quand le texte recherché comporte plus de 255 caractères !

Formule de E1, trouvée dans ExcelWays :
=sommeprod(($B$1:$B$14=B1)*ligne($B$1:$B$14))

Formule de F1, inspirée d’une formule proposée par Shane Devenshire :
=equiv(vrai;$A$1:$A$14=A1;0)
Cette dernière formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Et voilà ! Vous avez désormais deux façons de faire des recherches sur des chaînes de plus de 255 caractères !

22 février 2015

Mes formations en mars 2015

Vous trouverez ci-dessous la liste des formations que je propose pour le mois de mars 2015. Chaque formation est limitée à huit participants.

·         Découverte de Visual Basic (2 jours) : lundi 23 et mardi 24 mars.
·         Création de tableau de bord sous Excel : le jeudi 26 mars.

Les autres séminaires habituels ne sont pas proposés en inter lors de cette session. Toutes les formations proposées sont organisées en association avec la société EuroDécision.

Avec la formation « Découverte de Visual Basic », vous ne saurez pas tout sur le VBA, ce qui n'est pas possible en si peu de temps. Mais vous en saurez assez pour développer vos propres macros et savoir où trouver ce que vous ne savez pas encore. Cette formation aura lieu dans les locaux de Logma, 12 rue d’Anjou, 78000 Versailles. A 600 m. de la gare Rive Gauche et à 1.500 m. de la gare des Chantiers.

La formation « Création de tableau de bord sous Excel » est inédite. Elle vous permettra, en une seule journée, de totalement maîtriser la création d’un tableau de bord personnalisé, comme vous pouvez le voir dans l’article « Notre premier tableau de bord » du 22 mai 2013. Cette formation aura lieu dans les locaux de Finance 3.1, au 9 avenue de l’Opéra à Paris.

Toutes mes formations (cf. catalogue sur le site) peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Ne loupez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en plus de 10 pays.

Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.