Est-ce bien utile? A lire si vous avez envie de perdre un peu de temps :-)

Bon­jour à tous,

Je suis en train de lire une excel­lente syn­thèse de John Pelti­er sur les for­mules dynamiques matricielles et je suis tombé sur une expli­ca­tion assez intéres­sante du moteur de cal­cul sous-jacent à excel. En voici une par­tie, illus­trée avec un exem­ple.

IMPORTANT : l’ex­em­ple ci-dessous a été créé avec Excel 365 canal men­su­el.
Pour d’autres ver­sions, il peut être néces­saire de valid­er les for­mules avec CTRL+MAJ+Entrée.
Pour des ver­sions encore antérieures, la fonc­tion SI.NON.DISP n’ex­iste pas, et il faut faire un test SI et ESTNA, ce qui donne avec CTRL+MAJ+Entrée :
=SOMME(SI(ESTNA(B6:B8C6:C9);0;B6:B8C6:C9)) pour la deux­ième for­mule,
et
=SOMME(SI(ESTNA(B6:B8C6:C10);3;B6:B8C6:C10)) pour la troisième for­mule.

Cliquez ici pour télécharg­er le fichi­er.

Voici les résul­tats des dif­férentes for­mules. On va les analyser dans le détail.

Dans l’ex­em­ple ci-dessous, la fonc­tion =SOMME(B6:B8*C6:C9) retourne #N/A, car les deux plages sont de taille dif­férentes. Excel rem­place les paires non trou­vées par un #N/A. Le terme tech­nique de cette fonc­tion­nal­ité s’ap­pelle broad­cast­ing en Anglais.

De fait, his­toire de tester, j’ai ajouté le test du NA pour voir ce qu’il en est. Et ça sem­ble fonc­tion­ner, puisque la for­mule ren­voie 14 = 1*1+2*2+3*3+0.

Si on pousse un peu l’ex­em­ple en ajoutant une nou­velle ligne et en changeant le 0 en 3, on obtient ceci, à savoir : 20 = 1*1+2*2+3*3+3 + 3 = 14+6

Il y a aus­si un cas par­ti­c­uli­er qui demande une expli­ca­tion. La for­mule suiv­ante =SOMME(B6*C6:C8) va ren­voy­er 6. En effet, dans le cas où il y a une seule cel­lule, excel va sup­pos­er que la valeur de cette cel­lule peut être util­isée dans la for­mule. D’où en com­plé­tant la plage pour match­er la plage la plus grande, on a : =1*1+2*1+3*1 = 6

Hon­nête­ment, je ne vois vrai­ment pas à quoi cela peut servir, mais c’est intéres­sant de lever un peu le voile sur le fonc­tion­nement interne des for­mules. Si cela vous donne des idées, n’hésitez-pas à partager !

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel
Pour marque-pages : permalien.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.