Monsieur Excel
Pour tout savoir faire sur Excel !

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.

10 janvier 2017

Le 1er lundi de la 3ème semaine

Dans l’article publié le 30 décembre, Une formule dure à comprendre, nous montrions comment interpréter une formule assez complexe dont l’objectif était d’afficher la date du lundi de la 3ème semaine d’une année donnée.

Il s’agissait presque d’une énigme, tant la formule proposée état rébarbative. Notre lecteur Benji a, dans un commentaire, montré comment alléger un peu cette formule, en gardant son esprit, puis comment la remplacer par une formule bien plus simple. Nous vous la commentons ci-dessous, car de nombreux lecteurs ne lisent pas les commentaires.


La formule, reproduite en C6, est =("1/"&B1)-MOD(("1/"&B1)-2;7)+B2*7.

("1/"&B1) donne "1/2017" : cela donne le texte entre guillemets si on le garde tout seul. Mais si on lui ajoute ou ôte une valeur, c’est converti dans la valeur de date du 1er janvier 2017.

MOD(("1/"&B1)-2;7) enlève 2, car c’est la valeur du lundi, comme on peut le vérifier avec joursem().

Remarque – Ce qui est intéressant, c’est que les trois formules donnaient bien le même résultat avec l’année 2017 mais, comme on le voit dans la copie d’écran, donnent un résultat différent en 2018 !

Après vérification, c'est la formule originale qui avait raison. Il ne vous reste plus qu'à voir comment corriger les deux formules de Benji...

PS – Veuillez m’excuser d’avoir fait une pause plus longue que d’habitude. Je suis à San Francisco depuis samedi dernier, et j’étais trop occupé. En outre, j’ai eu la « chance » d’avoir 5 jours de pluie d’affilée (sur 5) ce qui est tout à fait incroyable là où il n’y a en général que 15 jours de pluie par an ! Mais au moins les Californiens ne sont pas mécontents de voir les réserves d'eau se reconstituer...

03 janvier 2017

Mes formations en janvier 2017

Vous trouverez ci-dessous la liste des formations que je propose en janvier 2017.
Chaque formation est limitée à huit participants.

Modélisation avec Excel (2 jours) :vendredi 20 et mercredi 25 janvier.
Création detableau de bord sous Excel : le mardi 31 janvier.

Mes autres séminaires habituels ne sont pas proposés en inter lors de cette session.

Avec la formation « Modélisation avec Excel », un utilisateur moyen d’Excel passe en deux journées au niveau des 5% des meilleurs utilisateurs Excel. J’anime cette formation depuis des années et aucun participant n’a jamais contesté cela. Cette formation aura lieu dans les locaux d’Ubisoft à Montreuil.

La formation « Création de tableau de bord sous Excel » vous permet, en une journée, de 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 au 9 avenue de l’Opéra à Paris.

Toutes mes formations 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 manquez 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.

Pour recevoir un programme détaillé, écrivez-moi à thiriez@hec.fr.

30 décembre 2016

Une formule dure à comprendre…

La formule ci-dessous serait due à Laurent Longre, qui avait entre autres créé dans le temps une DLL appelée MoreFunc.DLL.

L’objectif est d’identifier le premier lundi de la semaine indiquée en B2 pour l’année en B1.

Voici la formule placée en B4 :
=somme(mod(("1/"&B1)-{0;6};{1E+99;7})*{1;-1})+B2*7-4

Pouvez-vous comprendre comment cette formule s’y prend pour obtenir le bon résultat ?


Les explications sérieuses suivantes proviennent du MVP Craig Hatmaker, qui a fourni une analyse détaillée du processus utilisé. Il a dû tirer cela de son chapeau… Les explications plus farfelues sont de votre serviteur.

La première date normale dans Excel est 1, pour le dimanche 1er janvier 1900.

La première date possible dans Excel est 0, pour le samedi précédent. Toute valeur négative est refusée comme date.

On voit bien là que Bill Gates est un peu un extraterrestre puisqu’il fait commencer Excel à une date qui n’existe pas. Steve Jobs, qui n’était pas un extraterrestre, a fait débuter Excel Mac sur le 1er janvier 1904, ce qui fait qu’il y avait 4 ans et 1 jour d’écart entre les deux calendriers. Vous pouvez le vérifier en passant par les options d’Excel, dans la section « Options avancées », bloc « Lors du calcul de ce classeur ».

"1/"&B1 donne le 1er janvier de l’année en B1.

("1/"&B1)-{0;6} donne le 1er janvier de l’année et 6 jours plus tôt.

1E+99 est un nombre si grand que n’importe quelle date divisée par ce nombre a un reste égal à la date. Si nous remplaçons ce grand nombre par 1000000, nous obtenons le même résultat…

{1er janvier;Reste} * {1 ;-1} donne {1er janvier;-Reste}

Somme({1er janvier;-Reste}) donne le vendredi précédant le lundi cherché

Quand la semaine cherchée est 1, alors 7*semaine – 4 = 3 : on cherche donc le troisième jour après le vendredi cité ci-dessus, ce qui est bien un lundi !

En général, dans ce blog, j’essaye de vous apprendre à faire simple. 

Aujourd’hui, pour changer, c’était l’inverse. Mais un peu de stimulation intellectuelle pour disperser les vapeurs de l’alcool de fin d'année, c’est peut-être bien utile…

J’espère que cela ne vous empêchera pas de bien profiter de votre réveillon…


Bonne année !

24 décembre 2016

Booléens vs. SI() imbriqués

Une particularité d’Excel est sa gestion des variables booléennes. Une variable booléenne peut prendre les valeurs VRAI ou FAUX. Quand ces valeurs sont utilisées dans des calculs, VRAI est équivalent à 1 et FAUX à 0.

Prenons un exemple tout simple où un commercial reçoit une prime de 75 € pour une commande de 1.000 € ou plus, de 125 € pour une commande de 2.000 € ou plus, et enfin de 250 € pour une commande supérieure à 5.000 €.

Une solution simple (colonne G) consiste à créer une table de recherche en B1:C4 dans laquelle on ira chercher le résultat grâce à la fonction recherche().

Supposons maintenant que l’on ne souhaite pas créer cette table de recherche. On peut alors utiliser (colonne H) trois fonctions si() imbriquées l’une dans l’autre.

Mais une troisième solution (colonne I) revient à remplacer cela par une formule avec des conditions résultant dans des valeurs booléennes.


Remarque – L’utilisation de valeurs booléennes permet d’éviter à l’utilisateur d’imbriquer des fonctions si(), ce qui n’est jamais souhaitable pour la lisibilité des modèles.

En tant que consultant, quand je construis des modèles pour mes clients, je suis souvent confronté à ce genre de dilemme. La solution booléenne est plus simple et logique que la solution avec les si(), mais elle est plus difficile à comprendre pour les utilisateurs. Quand j’ai ainsi le choix entre une solution plus directe et/ou compacte, mais moins compréhensible, je choisis en général pour mes clients la solution la plus compréhensible et auditable.

Pour la même raison, je ne mets du VBA dans mes modèles que lorsque c’est indispensable. Les clients ont en effet plus de mal à s’approprier un modèle s’il contient du code VBA…

18 décembre 2016

Création d’un « waffle chart » (c)

Pour terminer la série sur les waffle charts, voici enfin un waffle chart à quatre catégories.

Pour ceux qui prennent le train en route, sachez que nous avons simplement entré une puce « ● » dans chaque cellule de B5 à P14, en Calibri taille 36, et que c’est la macro ci-dessous, liée à la feuille, qui met tout cela en couleur.

Ce sont les couleurs des cellules B5:B8 qui sont prises comme modèles et reproduites dans le « graphe ».


Voici le code VBA, une fois encore grâce au MVP américain Matt Mickle :

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CatVal1 As Integer: Dim CatVal2 As Integer
    Dim ColLp As Integer: Dim RowLp As Integer: Dim Cnt As Integer
    Dim Cat1Color  As Long: Dim Cat2Color As Long: Dim Cat3Color As Long
     
    If Intersect(Target, Range("B5:C7")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
   
        On Error Resume Next
        CatVal1 = 100 * Range("D5")
        CatVal2 = 100 * Range("D6") + CatVal1
        Cat1Color = Range("B5").Font.Color
        Cat2Color = Range("B6").Font.Color
        Cat3Color = Range("B7").Font.Color
   
        For RowLp = 5 To 14
            For ColLp = 7 To 16
                Cnt = Cnt + 1
                    If Cnt <= CatVal1 Then
                        Cells(RowLp, ColLp).Font.Color = Cat1Color
                    ElseIf Cnt <= CatVal2 Then
                        Cells(RowLp, ColLp).Font.Color = Cat2Color
                    Else
                        Cells(RowLp, ColLp).Font.Color = Cat3Color
                    End If
            Next ColLp
        Next RowLp
       
        'Change Font Color of TextBoxes
        With Sheets("3CategoryWaffleChart")
            .TextBoxes("TextBox 2").Font.Color = Cat1Color
            .TextBoxes("TextBox 6").Font.Color = Cat1Color
            .TextBoxes("TextBox 3").Font.Color = Cat2Color
            .TextBoxes("TextBox 7").Font.Color = Cat2Color
            .TextBoxes("TextBox 4").Font.Color = Cat3Color
            .TextBoxes("TextBox 8").Font.Color = Cat3Color
        End With
        On Error GoTo 0

End Sub

13 décembre 2016

Création d’un « waffle chart » (b)

Dans le dernier post, nous avons vu comment fonctionnait le waffle chart avec deux catégories.

Voici aujourd’hui un exemple de waffle chart à trois catégories, ainsi que le code VBA associé.

Le code original est dû au MVP américain Matt Mickle. Je l’ai seulement un peu simplifié et compacté.


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CatVal1 As Integer: Dim CatVal2 As Integer
    Dim ColLp As Integer: Dim RowLp As Integer: Dim Cnt As Integer
    Dim Cat1Color  As Long: Dim Cat2Color As Long: Dim Cat3Color As Long
     
    If Intersect(Target, Range("B5:C7")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
   
        On Error Resume Next
        CatVal1 = 100 * Range("D5")
        CatVal2 = 100 * Range("D6") + CatVal1
        Cat1Color = Range("B5").Font.Color
        Cat2Color = Range("B6").Font.Color
        Cat3Color = Range("B7").Font.Color
   
        For RowLp = 5 To 14
            For ColLp = 7 To 16
            
                Cnt = Cnt + 1
                    If Cnt <= CatVal1 Then
                        Cells(RowLp, ColLp).Font.Color = Cat1Color
                    ElseIf Cnt <= CatVal2 Then
                        Cells(RowLp, ColLp).Font.Color = Cat2Color
                    Else
                        Cells(RowLp, ColLp).Font.Color = Cat3Color
                    End If
                   
            Next ColLp
        Next RowLp
       
        'Change Font Color of TextBoxes
        With Sheets("3CategoryWaffleChart")
            .TextBoxes("TextBox 2").Font.Color = Cat1Color
            .TextBoxes("TextBox 6").Font.Color = Cat1Color
            .TextBoxes("TextBox 3").Font.Color = Cat2Color
            .TextBoxes("TextBox 7").Font.Color = Cat2Color
            .TextBoxes("TextBox 4").Font.Color = Cat3Color
            .TextBoxes("TextBox 8").Font.Color = Cat3Color
        End With
       
        On Error GoTo 0

End Sub