Bonjour à tous,
Je suis en train de lire une excellente synthèse de John Peltier sur les formules dynamiques matricielles et je suis tombé sur une explication assez intéressante du moteur de calcul sous-jacent à excel. En voici une partie, illustrée avec un exemple.
IMPORTANT : l’exemple ci-dessous a été créé avec Excel 365 canal mensuel.
Pour d’autres versions, il peut être nécessaire de valider les formules avec CTRL+MAJ+Entrée.
Pour des versions encore antérieures, la fonction SI.NON.DISP n’existe 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 deuxième formule,
et
=SOMME(SI(ESTNA(B6:B8C6:C10);3;B6:B8C6:C10)) pour la troisième formule.
Cliquez ici pour télécharger le fichier.
Voici les résultats des différentes formules. On va les analyser dans le détail.
Dans l’exemple ci-dessous, la fonction =SOMME(B6:B8*C6:C9) retourne #N/A, car les deux plages sont de taille différentes. Excel remplace les paires non trouvées par un #N/A. Le terme technique de cette fonctionnalité s’appelle broadcasting en Anglais.
De fait, histoire de tester, j’ai ajouté le test du NA pour voir ce qu’il en est. Et ça semble fonctionner, puisque la formule renvoie 14 = 1*1+2*2+3*3+0.
Si on pousse un peu l’exemple en ajoutant une nouvelle 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 aussi un cas particulier qui demande une explication. La formule suivante =SOMME(B6*C6:C8) va renvoyer 6. En effet, dans le cas où il y a une seule cellule, excel va supposer que la valeur de cette cellule peut être utilisée dans la formule. D’où en complétant la plage pour matcher la plage la plus grande, on a : =1*1+2*1+3*1 = 6
Honnêtement, je ne vois vraiment pas à quoi cela peut servir, mais c’est intéressant de lever un peu le voile sur le fonctionnement interne des formules. Si cela vous donne des idées, n’hésitez-pas à partager !