Monsieur Excel
Pour tout savoir faire sur Excel !

21 février 2017

Factorielle : une illusion d’optique !

Le problème suivant a été soulevé par le MVP Liam Bastik.

La formule de C4 est : =B4*C3
La formule de D4 est : =droite(C4;15)*1
La formule de E4 est : =droite(C4;19)*1

La question qui se pose est la suivante. Pourquoi, à partir de la ligne 24, obtient-on des résultats qui ont l’air tout à fait incohérents ?


Pour vous mettre sur la voie, calculons donc la valeur de C23 (avec [F9] dans la barre de formule) : nous obtenons 51090942171709400000.

Calculons à présent celle de C24 : 1,12400072777761E+21.

Le problème vient donc du passage à la notation exponentielle.

Le MVP Jan Karel Pieterse a suggéré pour D4 la formule =droite(texte(C4;"0");15).

Mais on peut encore raccourcir sa formule en éliminant les guillemets…

La solution la plus courte est donc : =droite(texte(C4;0);15)

16 février 2017

Position de la dernière occurrence

Dans les deux articles précédents, nous avons vu comment trouver la première occurrence, puis la seconde, puis la troisième.. d’une valeur ou d’un texte dans une table.

Pour trouver la position de la dernière occurrence, c’est beaucoup plus simple. Nous utilisons en D12 la formule suivante : =max(ligne(A2:A21)*(A2:A21=D1)).

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Notons que, si aucune occurrence n’a été trouvée, la cellule affichera « Ligne 0 », ce qui n’est pas particulièrement joli.

Ce serait plus joli d’afficher alors « Aucune ».


Beaucoup d’utilisateurs d’Excel vont alors construire une formule compliquée avec un si(), ce qui les amènera à écrire deux fois la formule.

Pour notre part, nous n’allons pas alourdir notre formule. Ce problème est résolu de façon bien plus esthétique avec le format personnalisé "Ligne "0;;"Aucune".

En effet, l’ordre des formats personnalisés est Positif;Négatif;Nul;Texte. Avec notre format, nous masquons les nombres négatifs et affichons « Aucune » quand la cellule vaut 0.

Remarque – De nombreuses formules de ce type peuvent être allégées de façon significative en ayant recours aux formats personnalisés !

10 février 2017

La énième occurrence, en direct

Dans l’article précédent, nous avons vu comment trouver la première occurrence, puis la seconde, puis la troisième.. à partir des positions des occurrences précédentes.

Parfois, on souhaite accéder directement à la troisième occurrence, ou à la septième, sans pour autant passer par les occurrences précédentes. Nous allons voir aujourd’hui comment atteindre cet objectif. Vous pouvez voir ce que cela donne dans le bloc D5:E10.

Nous entrons en colonne E le numéro de l’occurrence souhaitée et obtenons en colonne D le numéro de la ligne concernée. Mais ici chacune des formules de D5 à F10 est indépendante et autonome.


La formule de D5 est :
=sierreur(petite.valeur(si($A$1:$A$21=$D$1;ligne($A$1:$A$21));E7);"")
Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Pour comprendre cette formule, il suffit de voir que :
si($A$1:$A$21=$D$1;ligne($A$1:$A$21))
Donne le résultat suivant :
{FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;
FAUX;15;FAUX;FAUX;18;FAUX;FAUX;21}

On cherche alors la énième plus petite valeur et, comme les FAUX ne comptent pas dans cette recherche, on trouve bien la énième occurrence de la valeur recherchée.

05 février 2017

Trouver la énième occurrence

De nombreux utilisateurs d’Excel connaissent la fonction equiv() et savent qu’elle sert par exemple à trouver la première occurrence d’une valeur ou d’un texte dans un vecteur quand son troisième argument est 0, grâce à la syntaxe : =equiv(valeur;vecteur;0).

Dans le tableau ci-dessous, nous avons tiré en colonne A des valeurs aléatoires de 1 à 10, histoire de pouvoir bien tester le fonctionnement de nos formules. Il suffit donc d’utiliser la touche F9 pour obtenir un nouveau tirage des valeurs de la colonne A.


La formule de D2, visible dans le commentaire associé, sert à calculer la position du premier 7 dans la colonne A.

La formule de D3, reproduite ensuite vers le bas, sert à calculer la position du second 7, du troisième, … et ainsi de suite.

Remarque  Dans le temps (avant Excel 2007), la fonction sierreur() n'existait pas et l'on était alors obligé d'utiliser une formule plus complexe avec un si(), ce qui nous obligeait à écrire deux fois l'expression à analyser !

28 janvier 2017

Opérations sur cellules par couleur

La macro suivante est une création de Philip Treacy, l’époux de la MVP Mynda Treacy. Cette macro-fonction permet de réaliser des opérations en fonction de la couleur de fond des cellules. 

Pour voir l’article original en anglais, lisez donc à l’adresse suivante :

La formule de E9 est la suivante : =somme(findcolors(ColoredCells;B9))
Les formules à droite de E9 utilisent respectivement les fonctions Nb, Min, Max et Moyenne.

Elle utilise le nom ColoredCells défini par =Sheet1!$B$9:$B$17.


La formule de B9 est =GetColor(B9). Elle récupère le code hexadécimal de la couleur avec les couleurs dans l’ordre Bleu-Vert-Rouge, qui sont les trois couleurs primaires de la synthèse additive de la couleur. 

Si vous voulez en savoir plussur la synthèse de la couleur, lisez dans ce blog :


Vous pouvez voir dans le bas de la copie d’écran le code VBA utilisé par Philip, que j’ai modifié pour le raccourcir.

23 janvier 2017

Les fonctions de Visicalc

Remarque - Désolé de ne rien avoir publié depuis quelques jours. Je suis tombé dans un piège de Chrome. J'utilise ce navigateur pour ce blog. A sa demande, j'ai fait une mise à jour du logiciel. Et, depuis plusieurs jours, impossible de me reconnecter au compte de ce blog ! Merci Chrome !

Revenons à l'article précédent, où je vous avais parlé de Visicalc.

Ce qui est passionnant, c’est que Visicalc, malgré un compteur limité à 31 fonctions, là où Excel aujourd’hui en affiche plus de 300, avait déjà tout ce qu’il fallait pour créer des modèles intéressants !


En haut de la copie d’écran ci-dessous, vous trouverez la liste de ces fonctions. Et encore, les 5 fonctions des la liste «Sans arg.» n’ont pas d’argument, ce ne sont que des constantes.

On distingue donc 12 fonctions d’intérêt général (fond bleu ciel), 2 fonctions de recherche (fond jaune), 6 fonctions trigonométriques et 6 fonctions logiques, sans compter les 5 dernières qui n'ont pas d'argument.

Les deux fonctions de recherche sont index(vecteur;position) – sous le nom de Choose – et Lookup, c’est-à-dire la fonction recherche() originelle. Il ne manque que equiv() mais, comme on peut le voir dans la seconde partie de la copie d’écran ci-dessus, cette fonction peut se synthétiser à partir des deux autres…

Nous voyons dans le bloc E12:E16 ce que donne un equiv() original une fois simulé dans Visicalc (en F13 et F16). Il a suffi d’ajouter le bloc C12:C16 pour y arriver…

Si cela vous intéresse, vous pouvez même télécharger un manuel de Visicalc de 264 pages :

15 janvier 2017

De Visicalc à Excel…

Le premier tableur à être apparu sur le marché est Visicalc, apparu en 1979, qui était proposé sur les trois micro-ordinateurs représentant plus de 90% du marché à l’époque : Apple II, Pet-Commodore et TRS-80.

Le tableur et le micro-ordinateur représentaient une véritable révolution. J’en étais tellement convaincu qu’en 1982 j’ai publié un livre sur Visicalc et qu’en 1981 j’avais créé Pom’s, la première revue Apple en France :

La vidéo suivante est tout à fait intéressante ; elle permet de comprendre comment Dan Bricklin s’y est pris pour créer Visicalc avec son ami Bob Frankston.

A Problem That Changed The World | Dan Bricklin

La vidéo suivante permet de voir les deux créateurs ensemble.

Before Excel there was VisiCalc: An interview with its creators :

Voici les dates de parution des principaux tableurs :
Visicalc : 1979 (le grand-père d’Excel)
Multiplan : 1982 (le père d’Excel)
Lotus 1-2-3 : 1983 (un concurrent de Multiplan)
Javelin : 1984 (un tableur tout à fait original)
Excel : 1993

Remarque 1 – Javelin était un tableur totalement original sur lequel j’ai d’ailleurs écrit deux livres. Il n’a pas fini par percer car, contrairement à Excel, il ne pouvait pas être utilisé par n’importe qui : la richesse de ses fonctionnalités faisait que, pour bien l’utiliser, il fallait des connaissances en modélisation. Dans le cas de Javelin, c’était une nécessité alors que, pour Excel, ce n’est qu’un avantage.

Remarque 2 – Merci à Alain Roussel qui m’a donné l’idée d’écrire cet article.