Monsieur Excel
Pour tout savoir faire sur Excel !

19 janvier 2019

Un problème d’arrondis (c)

Maintenant que tout est en place, nous pouvons passer enfin à l’optimisation de notre problème, afin de trouver les valeurs rouges à déterminer de façon à optimiser la cellule H11, c’est-à-dire à rendre justes tous les totaux par ligne et par colonne, tout en minimisant la somme des valeurs absolues des écarts entre les valeurs rouges initiales et finales.

Le réglage du solveur

Nous formulons dans le solveur la fonction économique à optimiser, la valeur de la cellule H1.

Nous indiquons ensuite que les variables sont les cellules de B3 à G7, et que les valeurs de ces cellules doivent se situer entre leur minimum, en R3:W7, et leur maximum, Y3:AD7.

Enfin, nous sélectionnons la méthode de résolution Evolutionnaire. Comme l’indique le solveur, c’est la méthode appropriée pour résoudre les problèmes complexes.


La solution « optimale »

Nous lançons le solveur et, en moins d’une minute, celui-ci nous annonce avoir trouvé la solution optimale. Cette solution est reproduite à la fin de cet article.

Remarque – Le seul problème, c’est que, si nous lançons 10 fois de suite cette optimisation, nous trouvons 10 solutions « optimales » différentes avec les valeurs suivantes : 0,51 - 0,75 - 0,61 - 0,39 - 0,35 - 1,36 - 0,57 - 1,42 - 0,84 - 0,71.

C’est tout à fait normal car le domaine des solutions possibles n’est pas continu, avec tous les arrondis. Ce qui est positif quand même, c’est que chacune de ces 10 solutions consécutives résout le problème principal, c’est-à-dire que tous les totaux par ligne et par colonne sont enfin justes.

En revanche, cela ne marchera pas forcément si nous complexifions le problème. Dans le cas de mon client, il y avait deux tableaux au lieu d’un, plus d’autres contraintes encore. Là, le solveur ne parvenait plus à trouver une solution dans laquelle tous les totaux horizontaux et verticaux étaient justes !



13 janvier 2019

Un problème d’arrondis (b)

Nous reprenons le modèle d’arrondis présenté dans l’article précédent.
Aujourd’hui, nous nous concentrons sur les formules utilisées dans le modèle.

La formule de H3, reproduite vers le bas, est :
=arrondi(somme(B3:G3);0)

La formule de I3, reproduite vers le bas, est :
=arrondi(B3;0)+arrondi(C3;0)+ arrondi(D3;0)+arrondi (E3;0)+arrondi(F3;0)+arrondi(G3;0)

La formule de B8, reproduite vers la droite, est :
= arrondi(somme(B2:B7);0)

La formule de B9, reproduite vers la droite, est :
=arrondi(B2;0)+arrondi(B3;0)+arrondi(B4;0)+arrondi(B5;0)+arrondi(B6;0)+arrondi(B7;0)

C11 calcule la somme des valeurs absolues des écarts entre la ligne 8 et la ligne 9 :
=abs(B8-B9)+abs(C8-C9)+abs(D8-D9)+abs(E8-E9)+abs(F8-F9)+abs(G8-G9)+abs(H8-H9)

E11 calcule la somme des valeurs absolues des écarts entre la colonne H et la colonne I :
=abs(H2-I2)+abs(H3-I3)+abs(H4-I4)+abs(H5-I5)+abs(H6-I6)+abs(H7-I7)+abs(H8-I8)

Les bornes et les écarts

Les blocs R3:W7 et Y3:AD7 représentent les bornes inférieures et supérieures des valeurs rouge, dont la valeur initiale est en K3:P7.

Remarque – Comme nous l’avions expliqué dans l’article précédent, il y a un écart maximal de +/-1 par rapport à la valeur initiale, et les valeurs initiales à 0 ne bougent pas…

La formule de Q3, reproduite vers le bas, est :
=abs(B3-K3)+abs(C3-L3)+abs(D3-M3)+abs(E3-N3)+abs(F3-O3)+abs(G3-P3)


La cellule à optimiser

H11, enfin, représente ce que nous allons tenter de minimiser, soit 100 fois la somme des écarts cumulés sur les totaux plus la somme des valeurs absolues des écarts entre les valeurs rouges originales et les valeurs rouges finales :
=100*(B11+E11)+Q8

Voilà, nous avons mis en place tout ce qu’il fallait pour pouvoir, dans le prochain article, voir quelle sont les valeurs à entrer en B3:G7 pour atteindre notre objectif : que tous les totaux par ligne et par colonne soient cohérents, tout en minimisant la somme des écarts en valeur absolue des valeurs rouges finales par rapport aux valeurs originales.



08 janvier 2019

Un problème d’arrondis (a)

Les premières semaines de 2019 vont être une période de lourde charge pour tous ceux qui doivent préparer le compte-rendu de l’activité de leur entreprise en 2018. Dans certaines entreprises, pour la préparation du rapport annuel, ils sont confrontés à un problème vieux comme le monde : le problème des arrondis.

En effet, dans un souci de simplicité, on n’utilise pas de décimale dans la plupart de ces rapports finaux. Du coup, on est confronté à un problème impossible à résoudre : il n’y a aucune raison que la somme des arrondis (en ligne comme en colonne) soit égale à l’arrondi de la somme des valeurs originales. C’est ainsi que, chaque année, des cadres passent du temps à retoucher les valeurs individuelles de façon à rendre cohérents tous les totaux par ligne et par colonne.

Bien entendu, il est souhaitable d’augmenter ou de diminuer chaque valeur modifiée de +/-1 au maximum. Tout en évitant quand même d’aboutir à un nombre négatif pour une quantité qui doit être positive ou nulle. Il est aussi conseillé de ne pas modifier une valeur à 0, car ce 0 est probablement significatif. Enfin, idéalement, il faudrait que la somme des valeurs absolues des écarts entre l’ancienne et la nouvelle valeur soit la plus faible possible.


Remarque – J’ai utilisé un format conditionnel (fond jaune) pour mettre en relief chaque somme d’arrondis qui n’est pas égale à l’arrondi de la somme. Dans notre exemple, il y a trois lignes et trois colonnes où les totaux ne sont pas les mêmes…

Dans certaines entreprises, on se dédouane du problème en déclarant sous le tableau que, puisque la somme des arrondis n’est pas forcément égale à l’arrondi de la somme, il ne faut pas être surpris si les totaux ne tombent pas juste. Dans d’autres entreprises, des cadres passent un certain temps à modifier – le plus légèrement possible – les valeurs affichées (en rouge dans notre exemple) jusqu’à ce que tous les totaux par ligne et par colonne tombent juste.

Le tableau ci-dessus, dans les colonnes A à H, représente ce problème classique en gestion. Les nombres représentés en rouge ont été arrondis à l’entier le plus proche. Le bloc K3:P7 montre les véritables valeurs des cellules B3:G7.

J’ai récemment été contacté par une entreprise du CAC40 qui souhaitait résoudre ce problème mieux que par les tâtonnements habituels. J’ai résolu ce problème et ce client est entièrement satisfait. Je suis disponible à présent pour résoudre ce problème pour d’autres entreprises.

Dans les articles suivants, je vous montrerai comment résoudre ce problème, quand il est simple (un nombre modéré de totaux concernés) et quand il n’y a pas d’autre objectif à atteindre que de seulement équilibrer les totaux horizontaux et verticaux. La méthode que j’ai mise au point pour mon client permet d’aller bien plus loin, mais il faut bien que je préserve un avantage pour mes clients…

01 janvier 2019

Faites parler Excel en 2019 !


Je souhaite à toutes mes lectrices et à tous mes lecteurs une
Excellente Année 2019 !

En guise de cadeau – pour ceux d’entre vous qui ne le sauraient pas encore…  – je vais vous montrer comment faire parler Excel !

En fait, c’est tout simple !

La macro ci-dessous lit le texte de toutes les cellules sélectionnées !

Sub Cell_talk()
    For Each cell In Selection
        Application.Speech.Speak (cell.Value)
    Next
End Sub

Je n’ai hélas pas trouvé le moyen de modifier la voix qui, sur mon ordinateur, est par défaut une voix française masculine. Si l'un(e) d'entre vous peut m’indiquer comment modifier le sexe ou la langue de l’orateur, je suis preneur !

Pour le moment, c’est à vous qu’il revient d’adapter le texte à lire pour corriger cela. 

J’ai ainsi trouvé que, pour approcher « Happy New Year ! », il était préférable d’entrer dans la cellule « hhapi gniou yeure »…

28 décembre 2018

Réorganisation du TCD

Dans le dernier article, je vous ai laissé avec une question. Que fallait-il faire au TCD pour que le tableau de résultat (âge moyen et salaire moyen en fonction du sexe) ressemble à celui obtenu dans l’article du 17 décembre à l’aide de la fonction bdmoyenne() ?

En fait, je vous propose deux solutions, que vous retrouverez dans la copie d’écran en bas de cet article.

La solution la plus efficace – mais certes pas la plus naturelle ! – revient à déplacer le S Valeurs qui se trouve au départ dans le bloc Colonnes vers le bloc Lignes. Ce n’est pas intuitif, mais cela fonctionne parfaitement et atteint le but que nous nous étions fixé au départ.

La seconde solution est plus naturelle : elle consiste à tout simplement tirer le bloc Sexe – soit depuis la liste du haut, soit en le retirant du bloc Colonnes – pour le loger dans le bloc Lignes. Cette solution est plus naturelle, mais la présentation n’est plus la même que dans notre tableau Excel initial…


Remarque – Dans un cas comme dans l’autre, nous sommes à présent dans un TCD. Si les données de la base de départ sont modifiées, notre tableau de synthèse ne sera actualisé que si nous n’oublions pas d’actualiser le TCD.   

23 décembre 2018

Un premier TCD sur la base

Reprenons la base sur laquelle nous travaillons depuis le 8 novembre. Cette fois-ci, nous plaçons le curseur n’importe où dans la base (je vous rappelle qu’Excel fait alors un [Ctrl]-* pour sélectionner tout ce qu’il y a autour). Puis nous utilisons la commande InsertionTableau croisé dynamique et validons aussitôt, ce qui va créer un TCD dans une nouvelle feuille insérée juste avant la feuille de la base.

Nous tirons alors le champ Sexe dans le bloc Colonnes et les champs Salaire et Age dans le champ S valeurs.

Puis nous sélectionnons chacun des deux champs « Somme de… » pour effectuer, via la commande Paramètres des champs de valeurs, deux opérations : remplacer la somme par la moyenne puis, à l’aide du bouton Format de nombre, affecter à ce champ un format approprié.

Pour obtenir la présentation ci-dessous, j’ai ensuite ôté le quadrillage, ajouté un encadrement, modifié les formats de cellules en centré vertical et horizontal, en enfin redimensionné les colonnes pour obtenir un tableau agréable à lire. 

Voici le résultat :


Vous remarquez bien entendu que la présentation n’est pas aussi jolie que celle que nous avions obtenue dans l’article précédent. Il faudrait idéalement avoir une ligne pour le salaire et une autre pour l’âge.

Essayez donc de résoudre ce problème. En cadeau de Noël, je vous donnerai la solution !

17 décembre 2018

Salaire et Age moyens par Sexe

Supposez à présent que nous souhaitions créer une table avec le salaire moyen et l’âge moyen en fonction du sexe.

Nous créons donc une table à cet effet de C43 à F47.

Vous connaissez ma propension à n’utiliser qu’une formule pour un tableau entier chaque fois que c’est possible. En effet, avec une seule formule au lieu de trois (selon le sexe), nous allons à la fois trois fois plus vite et trois fois plus « safe » : une formule représente un risque d’erreur, trois formules trois risques…

La formule magique, entrée en D46 et validée (avec la touche [Ctrl] enfoncée) dans le bloc D46:F47 est : =bdmoyenne($A$5:$F$30;$C46;D$44:D$45).

Remarque 1 – Notez bien qu’il ne faut pas se tromper avec la touche : la base est entièrement figée, le nom du champ est bloqué en colonne et celui de la zone de critères bloqué en ligne.

Remarque 2 – Il y a 12 fonctions BDxxx(). Cette famille de fonctions possède une particularité originale : c’est la seule dont toutes les fonctions ont exactement les mêmes arguments. Quand vous en maîtrisez une, vous les maîtrisez toutes !


Impact des fonctions BDxxx()

Par rapport aux extractions dont nous avons parlé dans les articles précédents, les fonctions BDxxx() ont une grande différence : ce sont des fonctions, ce qui signifie qu’elles se recalculent en permanence. Si par exemple vous entrez m en F45, ce sont les valeurs pour les hommes qui apparaîtront dans les deux lignes en dessous !

Un problème à résoudre…

Une question pour les lecteurs qui connaissent bien les tableaux croisés dynamiques : « Comment faut-il procéder pour créer un TCD qui nous affiche le même tableau qu’en C44:F47 ?

Vous aurez la réponse dans notre prochain article, ce qui vous donne le temps de vous y entraîner…