Monsieur Excel
Pour tout savoir faire sur Excel !

22 juillet 2014

Fonctions date & heure d’Excel (a)

Cet article fait suite aux trois articles récents relatifs aux dates dans Excel. Si vous ne les avez pas encore lus, nous vous conseillons de commencer par les étudier avant de lire cet article.

« Saisie de dates dans Excel », le 30 juin 2014
« La grand-mère de Bill Gates », le 5 juillet 2014
« Bascule entre les bases de dates », le 11 juillet 2014

Il y a deux familles de fonctions de date et heure dans Excel : les fonctions historiques, présentes dans Excel depuis très longtemps, et les fonctions de « l’Utilitaire d’analyse » , qui est – à l’instar du Solveur – un add-in livré avec Excel mais non installé par défaut.

Or, à présent – je ne sais plus depuis quelle version –, les fonctions de date et heure qui étaient présentes dans « l’Utilitaire d’analyse » ont été intégrées à Excel et sont reconnues même quand cet utilitaire n’est pas installé.


Le tableau ci-dessus illustre l'utilisation des fonctions de date et heure historiques d’Excel, c’est-à-dire en ignorant celles ajoutées au départ par l’utilitaire d’analyse et intégrées depuis. Nous verrons ces fonctions dans le prochain article.

La colonne B affiche les formules utilisées en colonne A. Nous avons aussi mis en commentaire de la cellule A9 la véritable valeur de cette cellule, le contenu actuel de la cellule étant dû au format jjj jj/mm/aaaa hh:mm:ss.

Remarque 1 – On note en A4 que dateval() ignore l’heure en argument, et en A14 que tempsval() ignore la date en argument.

Remarque 2 – La fonction jours360() possède un troisième argument facultatif dont vous pourrez voir le mode d'emploi dans l'aide d'Excel.


16 juillet 2014

Un énorme bug des tables…

Nous avons les tables depuis Excel 2007. Et pourtant, c’est tout récemment qu’un membre bien connu de la communauté des MVPs Excel, Zack Barresse, y a découvert un bug important. Il a été testé sur Excel 2010 et 2013, mais je n’ai plus de 2007 sous la main pour vérifier s’il y était déjà présent. Ceci dit, a priori, on ne voit pas pourquoi il ne serait apparu qu’avec la version 2010…

Nous avons en A1:B6 (table 1) et en F1:G6 (table 2) deux tables. Le bloc en F8:G13 (bloc 3) possède la même structure, mais n’est pas défini comme table. Dans les trois cas, seules les valeurs de la seconde colonne diffèrent.

En outre, en D2, nous avons une formule faisant référence à la table 1.


Copie des valeurs du bloc vers la table 1

Commençons par copier le bloc F8:G13, à sélectionner la table 1 et à y coller les valeurs. Tout se passe bien et la cellule en D2 actualise son résultat.

Annulons à présent cette première opération avec [Ctrl]-z.

Copie des valeurs de la table 2 vers la table 1

A présent – mais nous aurions aussi bien pu faire cela en premier – copions la table 2, en F1:G6, sélectionnons la table 1 et collons-y les valeurs. La structure de table de la table 1 disparaît aussitôt et la cellule D2 affiche le message d’erreur #REF !

Autrement dit, le collage des valeurs d’une table vers une autre table tue la table réceptrice ! C’est un bug énorme…

Remarque – Dans la première rédaction de cet article, j'avais dit que l’annulation avec [Ctrl]-z ne marchait pas avec les opérations sur les tables. En fait, cela était dû à un conflit avec un add-in que j'ai depuis désactivé...


11 juillet 2014

Bascule entre les bases de dates

En référence avec l’article précédent, j’ai écrit une toute petite macro pour basculer entre les dates à la Bill Gates et les dates à la Steve Jobs. Un simple clic sur le bouton permet de basculer entre les deux modes. Voici la macro :

Sub Change_base_date()
    ActiveWorkbook.Date1904 = _
        Not (ActiveWorkbook.Date1904)
End Sub

Notez l’utilisation astucieuse de la fonction Not() qui permet d’éviter un bien plus lourd :

Sub Change_base_date()
    If ActiveWorkbook.Date1904 = True Then
          ActiveWorkbook.Date1904 = False
     Else
          ActiveWorkbook.Date1904 = True
     End If
End Sub

Nous avons entré en D1:E4 quelques dates célèbres. Nous voyons que les dates en E1:E2, antérieures à la première date possible (0 janvier 1900 ou 1 janvier 1904, selon la grand-mère) sont prises sous la forme de texte et donc cadrées à gauche par défaut.

Du coup, elles ne bougent pas quand on passe à la base 1904, contrairement aux deux dates en E3:E4.

 










Deux lectures pour mieux comprendre et gérer les dates non reconnues comme telles par Excel…

How to Work with Dates Before 1900 in Excel :

Pre-1900 Dates in Excel :



05 juillet 2014

La grand-mère de Bill Gates

La gestion des dates dans Excel est un peu particulière. Il faut savoir que – pour Excel – un jour représente une unité et une partie de jour une fraction de cette unité.

Dans le tableau ci-dessus, nous avons entré dans la colonne A, successivement, les formules que voici :
En A2 : =2/3
En A3 : 1,25
En A4 : -1
En A5 : =aujourdhui()
En A6 : =maintenant()


Pour les formules de la colonne B, c’est tout simple. Celle de B2 est « =A2 » et elle a été recopiée jusqu’en B5.

Notez enfin que nous avons attribué aux cellules A4:A5 le format « # ##0,00 ».

Les cinq cellules de la colonne B ont ensuite reçu le format personnalisé indiqué en commentaire.

La première date d’Excel est celle de la date de naissance de la grand-mère de Bill Gates. On savait déjà qu’il était un peu extra-terrestre ; il n’est donc pas surprenant que sa grand-mère soit née un 0 janvier, date originale s’il en est.

On n’a pas le droit de remonter plus haut dans la généalogie de Bill, puisque (cf. A3) , toute date négative affiche une erreur non « typée » : en effet, elle n'est pas sous la forme #VALEUR !, qui aurait été nettement plus logique en la circonstance.

Notez que c’est le seul cas où vous récolterez une série de « # » sans que la largeur de la colonne ne soit en cause.

Nous allons maintenant découvrir que la grand-mère de Steve Jobs, elle, est née le 1er janvier 1904 (lui n’est donc pas extra-terrestre). Pour cela, nous passons par la séquence FichierOptionsOptions avancées et, vers la fin de cet onglet,  nous cochons l’option « Utiliser le calendrier depuis 1904 », qui nous met en mode « Excel Macintosh ».

Voici ce que devient alors notre modèle :


On constate aussitôt une autre étrangeté. Avec ce réglage, les dates négatives aboutissent à une date normale précédée du signe « - ». Allez donc savoir pourquoi !

On remarque en revanche que les dates des lignes 5 et 6 sont toujours bonnes : en effet, ce n'étaient pas des constantes mais des fonctions...

PS – Si vous voulez surprendre un collègue, mettez discrètement son Excel dans le mode «Utiliser le calendrier depuis 1904 » : il ne comprendra plus rien aux dates affichées dans ses modèles…

Remarque – J’ai repris en partie un article publié au début de ce blog, le 14 décembre 2005.


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…