Monsieur Excel
Pour tout savoir faire sur Excel !

29 novembre 2013

La syntaxe exacte d’agregat()

Dans notre article du 13 novembre, nous n’avions présenté pour simplifier que la première syntaxe de la fonction agregat(). En fait, comme nous le voyons dans la copie d’écran ci-dessous montrant trois extraits de l'aide de Microsoft, cette fonction possède deux syntaxes.

 
Comme c’est très souvent le cas dans l’aide de Microsoft, la syntaxe proposée est foireuse… 
     
En effet, si vous utilisez la première syntaxe proposée par Microsoft avec par exemple =agregat(14;6;$A$1:$A$10;) en omettant donc le [k] censé être facultatif mais en gardant le « ; » qui le précède, vous obtiendriez une erreur !

Si l'on voulait être précis, il faudrait en fait écrire :
=agregat(n° fonction;n° option;zone[;arg]) pour la première syntaxe
=agregat(n° fonction;n° option;zone[;zone2[;zone3…]]) pour la seconde syntaxe

La seconde syntaxe est réservée aux fonctions dont les numéros vont de 1 à 13, les fonctions qui n’ont pas besoin d’un argument additionnel. Dans ce cas, les arguments à partir du troisième peuvent identifier plusieurs zones. 

Ainsi,  la formule =agregat(1;6;$A$1:$A$10) est-elle équivalente à =agregat(1;6;$A$1:$A$6;$A$7:$A$10).

La première syntaxe sert à passer un argument supplémentaire à la fonction concernée, ce qui concerne uniquement les fonctions de 14 à 19.

En conclusion, si le numéro de fonction (le premier argument) est supérieur ou égal à 14, c’est la première syntaxe qui est utilisée par Excel. S’il est inférieur ou égal à 13, c’est la seconde syntaxe qui s’applique.

Contre-vérités trouvées sur d’autres sites Excel

Sur certains sites que j’ai consultés, il est affirmé que le troisième argument ne peut pas être une zone en trois dimensions, c’est-à-dire un ensemble de matrices. C’est faux !

Faites-en donc l’expérience… Sélectionnez trois matrices (en maintenant la touche [Ctrl] enfoncée), donnez le nom « Jules » à cet ensemble de matrices – donc à une zone en trois dimensions – puis utilisez la formule = agregat(1; 6;jules). Cela fonctionne parfaitement bien !

Remarque – Vous pouvez à ce sujet consulter l’article « Tutorial sur la fonction index() » du 9 mars 2011, qui illustre une utilisation d’une zone à trois dimensions…

Sur certains sites aussi, il est affirmé que, pour les six dernières fonctions – qui requièrent un argument supplémentaire –, les valeurs analysées doivent obligatoirement être dans une plage continue. C’est faux aussi : il suffit pour cela d’utiliser un nom, comme je l’ai fait ci-dessus avec Jules. Dans ce cas, on a accès non seulement aux plages à trois dimensions, mais aussi aux plages discontinues à une ou à deux dimensions.

24 novembre 2013

Un peu plus sur agregat()...

Le vide est-il nul ?

Eh oui, pour une fois, nous nous posons une question métaphysique ! 

Pour faire suite aux commentaires de l’article précédent, nous voyons aujourd’hui ce que donne la formule matricielle – donc validée avec [Ctrl]-[Maj]-[Entrée] – utilisée dans les colonnes E et F pour émuler le travail de la fonction agregat().

La formule en E2 est, comme dans l'article précédent : =moyenne(si(esterreur($A$1:$A$9);"";$A$1:$A$9))
En F2, nous avons entré : =moyenne(si(esterreur($A$1:$A$10);"";$A$1:$A$10))

La colonne E nous donne les mêmes résultats que la fonction agregat() en colonne D. Pour la colonne F, les résultats sont différents car la formule matricielle compte la cellule vide en A10 comme un « 0 », alors qu’elle est ignorée par la fonction agregat().


En conclusion, le vide est un « 0 » pour la formule matricielle, mais il n’est pas nul pour agregat()…

Pourquoi utiliser la fonction agregat() ?

Une utilisation notable de la fonction agregat() peut être trouvée dans la définition de formats conditionnels. 

En effet, quand un format de ce type dépend de valeurs constatées sur un ensemble de cellules, il peut être utile que ce format ne « disparaisse » pas dès que l’une des cellules concernées affiche un message d’erreur ou ne puisse être faussé si l’une des cellules concernées est vide.

Le très mauvais choix de Microsoft

J’ai été très déçu par la façon totalement idiote dont Microsoft a défini l’argument « option » de la fonction agregat() – cf. article du 13 novembre. Ce choix d’option est un véritable fouillis.

Il aurait été bien plus intelligent d’utiliser, à l’instar de la fonction InputBox du VBA, un argument additif à partir de puissances de 2 avec par exemple :
  • 1 pour ignorer les fonctions Sous.Total et Agregat imbriquées
  • 2 pour ignorer les lignes masquées
  • 4 pour ignorer les valeurs d’erreur
Un « 6 », donc 2 + 4, indiquerait alors que l’on ne veut ignorer que les lignes masquées et les valeurs d’erreur.

Cela aurait été tellement plus clair !

19 novembre 2013

Le quatrième argument d’agregat()

Dans la syntaxe de la fonction agregat() que nous avons présentée dans le dernier article, il n’y avait que trois arguments. En fait, la syntaxe complète, si l’on utilise des crochets pour représenter les arguments facultatifs, est la suivante :
=agregat(n° fonction;n° option;zone[;arg])

On peut dont ajouter un argument qui, de fait, est l'argument supplémentaire requis par la fonction à laquelle on fait référence.

Nous avons simplifié le modèle précédent pour ne garder que les valeurs de départ – qui ne sont pas les mêmes que la dernière fois, car je les tire au hasard – et les colonnes C, D et E.


En D2, recopiée vers le bas jusqu’en D14, j’ai utilisé la formule :
=agregat(ligne()-1;6;$A$1:$A$10)
.


En E2:E20, j’ai les formules matricielles permettant d’obtenir le même résultat. Pour la cellule E2, c’est : =moyenne(si(esterreur($A$1:$A$9);"";$A$1:$A$9))

En ligne 14, il n'y a pas de réponse car toutes les valeurs de A1:A9 sont uniques. Il en faudrait au moins une doublée par obtenir une réponse numérique.

Pour la ligne 20, je n'ai pas trouvé la valeur d'argument qui me donne une réponse valable, mais je ne sais pas à quoi cette fonction correspond exactement...
 
Remarque – Notez que nous faisons référence à la ligne 9 dans les formules matricielles, et non plus la ligne 10 : en effet, si nous ne le faisions pas, nous aurions des erreurs dans la plupart des lignes à cause de la prise en compte de la cellule A10 !

A partir de la ligne 15, nous devons modifier la formule qui venait de D2 pour intégrer le paramètre supplémentaire requis par la fonction utilisée. Ainsi, en D15, nous avons =agregat(ligne()-1;6;$A$1:$A$10;3) car nous voulons la troisième valeur la plus grande du bloc A1:A10. 

Ces derniers arguments sont, sur les lignes 15 à 20, respectivement : 3 - 3 - 0,9 - 0,9 - 0,8 - 0,8.     

13 novembre 2013

Découvrez la fonction agregat()

La fonction agregat() est apparue avec la version 2010 d’Excel. Elle permet de résoudre des problèmes que l’on aurait beaucoup de difficulté à résoudre sans son assistance.

Nous découvrons aujourd’hui la première de ses deux syntaxes :
=agregat(n° fonction;n° option;zone)

Vous pouvez utiliser, pour le premier argument, 19 numéros de fonctions dont la liste est reproduite à droite de la copie d’écran ci-dessous.

Le numéro d’option, le second argument, peut pour sa part prendre les valeurs de 0 à 7, comme on le voit en bas de la copie d’écran.

Le troisième argument, « zone », identifie un groupe de cellules, à une (vecteur), deux (matrice) ou trois (ensemble de matrices) dimensions.


Remarque 1 – Vous pouvez même taper « agrégat » au lieu de « agregat » : cela ne troublera pas Excel, qui transforme cela en majuscules non accentuées. C’est aussi le cas pour d’autres fonctions aux lettres accentuées telles que « écartype ».

Le tableau en C1:F8 illustre l’utilisation de cette nouvelle fonction. En colonne D, nous avons utilisé les fonctions directes, comme =moyenne($A$1:$A$10) en B2. La formule de la cellule E2 – qui a été ensuite reproduite vers le bas – est : =agregat(E2;6;$A$1:$A$10).

Remarque 2 – Nous avons pris la valeur « 6 » comme second argument, afin de sauter les cellules contenant des valeurs d’erreur.

Avant la fonction agregat(), pour obtenir le résultat de la cellule F2, il fallait utiliser la formule matricielle =moyenne(si(esterreur(A1:A10);"";A1:A10)). C’est plus simpleet surtout non matriciel !avec cette nouvelle fonction !


08 novembre 2013

Tendance et intervalle de confiance

On peut encore améliorer sensiblement le look de notre courbe de tendance en la remplaçant par un intervalle de confiance.

Commençons par supprimer la courbe de tendance dans le graphe.

Nous entrons alors en C17 la formule : =erreur.type.xy(B2:B12;A2:A12).
Puis en D2, reproduite vers le bas, la formule =C2-$C$17.
Et enfin, en E2, reproduite vers le bas, la formule =2*$C$17.

Sélectionnons D2:D12, copions ce bloc et collons le comme une nouvelle série dans le graphe. Nous modifions alors le type de graphe de cette série en sélectionnant la surface cumulée (le format Aires 2). Puis sélectionnons pour cette surface le format « Sans remplissage ».

Sélectionnons E2:E12, copions ce bloc et collons le comme une nouvelle série dans le graphe. Nous souhaitons alors lui donner le format Aires 2, mais il est hélas impossible de sélectionner la série à cet effet. Une astuce : entrez la valeur 200 en E2 ! Cela vous permet de voir la série et de lui donner le bon format. Il vous suffit ensuite de recopier en E2 la formule de E3 :)

A cette nouvelle série, j’ai attribué un remplissage bleu ciel (celui du milieu de la colonne) avec un taux de transparence de 40%. Le tout sans bordure.

Pour l’esthétique, j’ai aussi augmenté l’épaisseur du trait de la série originale.

Et voici le résultat ! Cela a de la gueule, n’est-ce pas ? 
 
 

Voici la référence du post de Charley Kid où j’ai trouvé l’idée de cet article :

03 novembre 2013

Utiliser la droite de régression

Cet article fait suite à l’article précédent, publié le 29 octobre.

La pente de la droite de régression et sa constante peuvent être récupérées par des formules, comme nous l’avons fait en ligne 16. Pour cela, nous avons sélectionné le bloc A16:B16, puis entré la formule =droitereg(B2:B12;A2:A12), que nous avons validée en formule matricielle avec la combinaison [Ctrl]- [Maj]-[Entrée].

En colonne C, nous avons entré les valeurs « prévues » par la droite de régression, en appliquant cette fois-ci la formule =$A$16*$A2+$B$16 en C2.

Remarque 1 – Nous notons que les valeurs sont légèrement différentes. En effet, il faut tenir compte des décimales qui n’étaient pas visibles dans la formule de la droite de régression inclue dans le graphe.

Si l’on réutilise la commande « Ajouter une courbe de tendance », on constate qu’on peut en ajouter une autre, par exemple exponentielle, qui ne sera d’ailleurs pas forcément meilleure. Si l’on souhaite apprécier la qualité des ajustements, il suffit de cocher aussi la case relative à l’affichage du R2.

En revanche, il n’y a pas de commande visible permettant de désactiver (ou détruire) la courbe de tendance actuelle.

 
Remarque 2 – Pour enlever une courbe de tendance, il faut la sélectionner, en cliquant dessus (un petit cercle de sélection apparaît alors à chaque bout de la droite, cf. copie d’écran) puis en appuyant sur la touche [Suppr].

Dans le prochain article, nous verrons comment encadrer la courbe de tendance par un intervalle de confiance.