Monsieur Excel
Pour tout savoir faire sur Excel !

30 juin 2014

Saisie de dates dans Excel

Dans Excel, quand on saisit dans une cellule totalement vierge – par là, je veux dire même par formatée ! – un contenu numérique, Excel attribue automatiquement à cette cellule le format numérique le plus proche de ce qui a été saisi.

Quand on entre dans une cellule totalement vierge une valeur pouvant représenter une date, Excel attribue à cette cellule le format de date le plus proche. Pour vous donner une idée des divers formats de date, nous avons montré dans l’encadré rouge les formats de date d’Excel listés dans la rubrique des formats personnalisés.

Nous pouvons constater l’effet de ce formatage dans la copie d’écran ci-dessous où nous avons montré :
En colonne A, ce qui a été saisi en colonne B
En colonne B, le résultat ainsi obtenu
En colonne C – la formule de C2 est =B2 – l'on peut voir ce que cela donne avec le format numérique affiché en C1


Les trois premières dates donnent un résultat différent quand vous utilisez un Excel en anglais. Dans ce cas, en effet, on obtient le 7 janvier à la place du 1er juillet. Dans le cas de la cellule B4, par exemple, il faudrait taper « jul2014 » dans un Excel en anglais à la place de « jul14 » pour obtenir le même résultat qu’avec un Excel français.

Remarque – Notons aussi au passage qu’il existe deux raccourcis pour obtenir automatiquement la date ou l’heure actuelles dans la cellule active : [Ctrl]-: pour l’heure et [Ctrl]-; pour la date. Pour le premier, c’est naturel car le « » est le séparateur naturel de l’heure.


25 juin 2014

Rêvez sur la barre de formule…

La barre de formule d’Excel n’a pratiquement pas changé depuis 20 ans.

Si vous voulez rêver à ce qu’elle pourrait devenir, allez donc voir le site suivant :
http://www.spreadsheetstudio.com/labs

Vous pourrez y voir la maquette d’un add-in – toujours en cours de développement – dont le but est de donner les fonctionnalités suivantes à la barre de formule d’Excel.


  • Un « look and feel » naturel pour les utilisateurs d’Excel
  • Un comportement intuitif, tel qu’il ne soit pas nécessaire de lire un mode d’emploi pour se débrouiller
  • Une accessibilité facile, que ce soit pour l’utilisateur lambda ou le « power user »
  • Une grande interactivité pour donner de nouvelles et riches fonctionnalités à l’utilisateur.

Vous trouverez à l’adresse ci-dessus une vidéo de 6′22″ montrant en parallèle le comportement de la barre de formule traditionnelle en comparaison avec celui de la nouvelle barre de formule.

Remarque 1 – Le produit ne sera hélas disponible que pour Excel 2013…

Remarque 2 – La vidéo est en anglais, ce qui peut poser un problème à certains…

Ce qui est triste, c’est que la plupart des fonctionnalités de cette « nouvelle » barre de formule ont été suggérées par des MVPs et autres experts Excel aux développeurs de Microsoft depuis plus de 10 ans, hélas sans la moindre incidence sur l’évolution historique réelle de cette barre de formule…


19 juin 2014

Comment planter Excel en beauté…

En fait, quand j’ai développé le modèle de l’article précédent pour comparer les temps de calcul des cinq solutions proposées, je l’ai fait au départ sur une reproduction de la formule de C2 jusqu’à C1000001, avec donc un millions d’exemplaires de la formule.

Quand je lançais le calcul à l’aide de la macro, je plantais Excel à chaque fois. Et je le plantais bien !

En effet, même en passant avec [Ctrl]-[Alt]-[Del] par le Gestionnaire de tâches et en demandant la fermeture d’Excel, rien ne se passait. Pour arrêter vraiment Excel, il me fallait passer par l’onglet « Processus », sélectionner Excel puis activer le bouton « Arrêter le processus ».

En revanche, quand j’essayais de chronométrer à la main sans passer par la macro, il n’y avait aucun problème. La seule difficulté était de bien lancer le calcul avec [F9] en même temps que le chronomètre, puis de bien arrêter le chrono dès que je voyais le total dans la cellule F2 se mettre à jour.

Ce n’est pas la première fois que cela m’arrive… Quand on lance des calculs lourds par macro, on peut planter Excel alors que ces mêmes calculs, lancés à la main, ne posent aucun problème.

Quand j’ai constaté ce problème et vu que je plantais systématiquement Excel, j’ai détruit la moitié des lignes, ne gardant que 500.000 exemplaires de la formule, et la macro a – depuis cette modification – fonctionné parfaitement à chaque fois, ne plantant plus du tout Excel !

Si un lecteur ou une lectrice connaît la solution à ce problème, je suis preneur…



14 juin 2014

Temps de calcul des formules

Dans l’article précédent, nous vous avons proposé cinq formules différentes pour calculer le nombre d’absences pour cause de maladie.

Afin de comparer ces cinq solutions, nous avons successivement collé chacune des cinq formules en C2, puis recopié cette formule vers le bas jusqu’en C500001 et mesuré son temps de calcul. Pour éviter des temps de calcul trop longs, nous nous sommes mis en mode de calcul manuel, via les options de calcul de l’onglet « Formules ».

Nous avons ensuite développé la macro suivante, appelée par le bouton macro de la feuille :

Sub Temps_calcul()
Dim Time As Double
    Time = Now()
    Calculate
    Range("K2") = Range("Total").Value
    Range("Temps") = Now() - Time
End Sub

Nous avons reporté les temps de calcul observés en K3 dans le bloc G2:J6. La moyenne des temps par ligne est reprise en colonne L.


Remarque – Les couleurs des cellules en D2:D6 nous permettent d’éviter de mal interpréter les résultats de la colonne C.

Nous constatons que les formules utilisant sommeprod() et les formules matricielles donnent des résultats assez comparables. Cela prouve que sommeprod se comporte comme une formule matricielle et n’est donc pas plus performante.

La seule économie de temps de calcul notable correspond à l’utilisation de la fonction nb.si.ens() !



08 juin 2014

Absences pour cause de maladie…

Nous avons en B2:B102 des états où « » signifie absence pour cause de maladie.

Le but du jeu, si l’on peut dire, est de calculer le nombre de périodes d’absence : dans notre cas, nous comptons « 1 » pour l’absence du 24 avril, « 1 » pour celle du 26 au 28 avril,  « 1 » pour celle du 30 avril au 1er mai – on n’a pas dit qu’il s’agissait de jours de travail, ou alors il s’agit peut-être d’un hôpital ! –, « 1 » pour celle du 3 mai, et ainsi de suite…


Peter Bartolomew a proposé les deux premières formules. Nous verrons dans l’article suivant pourquoi nous utilisons deux formules si proches l’une de l’autre. 

Doug Bliss a proposé deux formules utilisant la fonction nb.si.ens() décrite dans l’article précédent. Là encore, la seconde formule est une légère variante de la première. 

Enfin, en D6, nous avons une formule matricielle atteignant le même objectif.

Une fois de plus, nous constatons la richesse d’Excel avec ces cinq solutions – et l’on pourrait en proposer d’autres encore ! – au même problème.


03 juin 2014

Somme.Si.Ens() et Nb.Si.Ens()

Avec Excel 2007, Microsoft nous a donné deux nouvelles fonctions de calcul, qui enrichissent les fonctions Somme.Si() et Nb.Si() que nous vous avons présentées le 25 février 2006 et le 2 mars 2006.

Il s’agit des fonctions Somme.Si.Ens() et Nb.Si.Ens() dont l’aide ne dit pas ce que signifie le « Ens » ainsi ajouté : Ensemble ?

L’exemple ci-dessous illustre leur utilisation avec à titre de comparaison le même résultat obtenu à chaque fois avec une fonction BD…().


Les formules utilisées sont les suivantes :

Personnes de 27 ans :
I5 : =BDSOMME(A1:F26;E1;H1:H2)
I6 : =SOMME.SI.ENS(E:E;D:D;27)
I11 : =BDNB(A1:F26;E1;H1:H2)
I12 : =NB.SI.ENS(D:D;27)
Hommes de 27 ans :
I8 : =BDSOMME(A1:F26;E1;H1:I2)
I9 : =SOMME.SI.ENS(E:E;D:D;27;A:A;"m")
I14 : =BDNB(A1:F26;E1;H1:I2)
I15 : =NB.SI.ENS(D:D;27;A:A;"m")

Remarque 1 – En fait, ces nouvelles fonctions ne font rien que l’on ne sache déjà faire avec une fonction BD…() mais elles évitent la création d’une zone de critères comme H1:H2 ou H1:I2, selon le cas. Ceci dit, cette simplification a un inconvénient car elle aboutit à une moindre lisibilité du modèle : il faut en effet voir la formule pour comprendre d’où vient le résultat.

Remarque 2 – Faites très attention, car le champ sur lequel on fait la somme est en première position dans Somme.Si.Ens() alors qu’il était en troisième position dans Somme.Si(). Microsoft a été obligé de changer sa place car il n’y a qu’un champ de sommation alors qu’il peut y avoir plusieurs doublons « colonne;valeur ».

Remarque 3 – Cet article a déjà été publié le 27 février 2009. Je le reproduis ici pour deux raisons : d'une part, j'ai constaté chez mes clients que ces deux fonctions sont encore peu connues à ce jour. D'autre part, elles vont nous servir dans le prochain article.