Archive for octobre, 2012

Calculs sur les dates et les heures, format d’affichage et format interne

Les formules Excel, c’est relativement simple… Des additions, des soustractions on clique sur les cellules, des plus et des moins, et le tour est joué. Mais les calculs sur les dates, ce n’est pas si facile…

Avant toute chose, il est nécessaire de bien comprendre une chose dans Excel : chaque cellule dans laquelle on voit un nombre possède une valeur d’affichage et une valeur interne.

Par exemple, quand Excel affiche 4%, 4% est la valeur affichée, mais la valeur interne dans Excel est 0,04. Quand Excel affiche 4 €, la valeur interne est 4 (si on additionne 4 € et 8 € on obtiendra 12 €).

En ce qui concerne les dates et heures/minutes/secondes/dixièmes, cela se complique sérieusement…

LES DATES

Cherchons dans l’aide en ligne Excel (2003) et nous lirons :

Date la plus ancienne autorisée pour les calculs : 1er janvier 1900 (1er janvier 1904 en cas d’utilisation du système de date basé sur 1904).

Donc pour Excel, le premier jour de l’univers est le 01/01/1900. Saisissons cette date dans une cellule Excel et faisons clic-droit, « format de cellule – nombre », numérique à zéro décimale, et on trouve le beau « 1 ».

Donc, toute date postérieure au 01/01/1900 est stockée en interne par un simple nombre entier, à partir de 1. Aujourd’hui, le 04/09/2009, on est le 40060ème jour après le 01/01/1900…

Donc, pour connaitre le nombre de jours entre 2 dates, rien de plus simple, on fait la différence :

01/01/2009 – 01/01/2008 = 31/12/1900 pour Excel… Pensez donc bien à changer le format d’affichage là encore, car cela fait bien 366 et on obtiendra 366 en affichage… Mais comme vous avez travaillé sur des dates, Excel a gardé une date dans l’affichage du résultat…

Après, on a une flopée de fonction sur les dates. Parfois, lorsqu’on importe un fichier texte dans Excel par exemple, il se peut qu’Excel ne reconnaisse pas une date stockée sous la forme 09-4-25 (pour 25/04/2009), Excel va reconnaitre 09/04/2025… Donc importez cette date en format texte et non en format date, et il faut alors appliquer la formule suivante :

En A1, texte ’09-4-25′

En A2, =DATE(« 20″&GAUCHE(A1;2);STXT(A1;CHERCHE(« -« ;A1;1)+1;CHERCHE(« -« ;A1;4)-CHERCHE(« -« ;A1;1)-1);DROITE(A1;2))

Autant dire, assez illisible… Cette formule fonctionne, avec le texte 11-12-13, Excel renvoie bien le 13/11/2011 ! Mais cela ne marche pas pour une date inférieure au 01/01/2000, il faudrait encore faire plus compliqué. Bref, les formules imbriquées, c’est bien pratique, mais force est de reconnaitre que c’est assez illisible et non maintenable. Il faut dire que rien n’aide celui qui écrit la formule, puisque qu’Excel n’intègre pas d’éditeur de formule.

LES JOURS HEURES MINUTES SECONDES DIXIÈMES

Comment est-ce stocké en interne ? A bien y réfléchir c’est assez normal…

1 jour = 24 heures

1 heure = 60 minutes

1 minutes = 60 secondes

1 secondes = 10 dixièmes.

On divise par 24, 60, 10, 100 pour faire des calculs sur le temps. Ce n’est pas du calcul en base 10…

Mais Excel est simple (et les ordinateurs en général) : un jour est divisé en secondes. Il y a 3600 secondes par heures, soit 86400 secondes par jour. On a vu que un jour = 1, et bien une seconde, ça fait 1/86400 pour Excel. Du reste, la fonction TEMPS(heure;minute;seconde) d’excel nous donne les résultats suivant :

TEMPS(0;0;1) = 0,0000115740740740741 (affiché au format nombre avec 15 décimales). Et 1/86400 donne le même résultat !

Comprendre le stockage interne est une chose, arriver à saisir des temps et faire des calculs en est une autre… Pour cela, il faut saisir comme dans les exemples ci-dessous :

Bref, toujours saisir avec les 2 points, les virgules, etc…Pour faire les calculs, ne pas hésiter non plus à travailler avec la formule TEMPS(), et là encore, une flopée de fonction sur le temps vous attendra dans l’assistant formule, catégorie DATE et HEURE.

Top

Eliminer les doublons avec Excel 2007

Cette action est beaucoup plus simple avec Excel 2007 qu’avec 2003. Reprenons notre fichier nom avec 3 doublons exacts :

Dans le ruban (ou onglet) « Données », la nouvelle version 2007 apporte un bouton bien pratique, « Supprimer les doublons ». Si nous cliquons sur ce bouton, il sera possible de choisir les colonnes pour la détection de doublons :

Nous pouvons détecter les doublons finement, par colonne. Une validation avec « OK » nous donne correctement le nombre de doublon, et la feuille sans les doublons s’affiche alors.

Top

Attention aux macros VBA, code dangereux !

Un exemple plutôt « avancé » de macro :

http://www.geocities.jp/nchikada/pac/

Oui, c’est un jeu Pacman, parfaitement fonctionnel et cloné.

C’est plus que de la macro, non ? Parlons d’un vrai programme Excel VBA… Si on peut faire un Pacman en Excel, on peut aussi construire un fichier Excel contenant un code qui s’exécute à l’ouverture (si on nomme une procédure VBA AUTO_OPEN elle s’exécute à l’ouverture du fichier, ce qui peut être très pratique, par contre, si on est mal intentionné…). On peut également interroger la base de registre, scanner les répertoires d’entreprise à la recherche de documentation.

Avant d’accepter les yeux fermés l’exécution d’un code de macro (VBA), avant d’ouvrir un fichier Excel inconnu, tournez 7 fois la molette de votre souris dans votre main…

Top

Forcer l’affichage d’une boite de dialogue Excel en VBA

Lorsqu’on développe un programme VBA, il peut être utile d’imprimer le résultat de longs et fastidieux calculs faits par le code VBA. Essayez donc d’imprimer avec la méthode Printout :

ActiveWindow.SelectedSheets.PrintOut

Et vous verrez rapidement qu’il est malheureusement impossible de demander à l’utilisateur de choisir l’imprimante. Dans ce cas, il faut appeler directement la boite de dialogue d’impression Excel, avec la méthode Show de l’objet Dialog. Toute la difficulté réside dans le choix de l’index de la collection Dialogs, lequel choisir ?

L’aide en ligne Excel VBA fournit la liste des boite de dialogue d’Excel (2003 : chercher DIALOGS, collection d’objets et en bas, Liste des Arguments). Voici les 1eres lignes :

xlDialogActivate window_text, pane_num
xlDialogActiveCellFont font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count
xlDialogAddChartAutoformat name_text, desc_text
xlDialogAddinManager operation_num, addinname_text, copy_logical
xlDialogAlignment horiz_align, wrap, vert_align, orientation, add_indent
xlDialogApplyNames name_array, ignore, use_rowcol, omit_col, omit_row, order_num, append_last
xlDialogApplyStyle style_text

Dans notre cas, pour forcer l’ouverture de la boite de dialogue « Imprimer » avec toutes les options habituelles (choix de l’imprimante…), il faut écrire :

Application.Dialogs(xlDialogPrint).Show

Top

Eliminer les valeurs en double avec Excel 2003

Très utile encore, supprimer les doublons d’une liste. Voici une liste dans laquelle « Audibert » a 2 doublons exacts, sur toute la ligne (lignes 10 et 12).

Sélectionner les colonnes A à E, puis faire « Menu – Données – Filtrer – Filtre élaboré » et remplir les options de la manière suivante :

Attention, car la zone « Plage » est assez capricieuse. Pour la remplir, on clique dans la zone de texte à droite de « Plages : » puis on sélectionne la plage dans la feuille Excel.

On valide et voilà le résultat :

On peut alors supprimer les colonnes A:E pour faire plus propre (on aurait pu également choisir un autre onglet dans l’option « Copier dans » du filtre élaboré).

Si on veut faire l’inverse, c’est-à-dire déterminer les doublons (sur 50.000 lignes, ça peut être utile), on utilise un tri, et on teste si la valeur de la ligne précédente est égale à celle en cours. Pour cela, créer une clé de tri (en première colonne par exemple) qui est la concaténation de toutes les colonnes :

Enfin, on tri sur la clé ainsi créée, et on ajoute une colonne B dans laquelle on teste l’égalité avec la clé précédente, et on fait glisser la formule jusqu’à la dernière valeur.

Top

Excel 2003 : Colorier ou surligner une ligne en fonction d’une valeur

Il s’agit d’utiliser la mise en forme conditionnelle, afin de colorer une ligne entière si une valeur de la ligne a une valeur spécifique.

La mise en forme conditionnelle est dans le menu « Format – Mise en forme conditionnelle ». Nous ne développerons pas l’utilisation classique de cette fonctionnalité car elle est très intuitive. Si une cellule vaut « Oui », alors on peut la colorer en vert, si elle vaut « Non », on la colore en rouge, dans tous les autres cas, on la laisse telle quelle.

La mise en forme conditionnelle est très pratique, elle rend un tableau plus « dynamique » car on peut voir immédiatement des valeurs importantes (on change une formule, et la couleur de plusieurs cellules change).

Par contre, beaucoup moins intuitif, pour colorer une ligne entière en fonction d’une des valeurs de la ligne…

Prenons pour exemple un fichier Excel de personnes, avec nom, prénom, sexe, date naissance, age… Nous voulons surligner en rouge toutes les lignes des personnes de plus de 60 ans, et en bleu les personnes de moins de 35 ans.

1/ Sélectionner la ligne 2 entière, puis

EXCEL 2003 : menu « Format – Mise en forme conditionnelle »

EXCEL 2007 : ruban (ou menu) « Acceuil – Mise en forme conditionnelle – Règle de mise en surbrillance des celulles – Autres Règles ».

2/ Dans la condition 1, faire un test sur la formule et non sur la valeur, saisir la formule ci-dessous (voir copie ci-dessous) et appliquer la mise en forme souhaitée à l’aide du bouton « Format… « 

Remarque : si la valeur 60 est paramétrée, on écrit cette valeur en références absolues Excel, c’est-à-dire $M$1 si le 60 est stocké dans la cellule M1. Malheureusement, si le 60 est stocké dans une autre feuille, Excel 2003 n’accepte pas une formule de mise en forme conditionnelle faisant référence à une autre feuille… A nous donc de stocker les « paramètres » de mise en forme conditionnelles dans la même feuille, et de faire en sorte que ces paramètres soient une copie d’une autre feuille. Par exemple, en M1, la formule fera référence à une autre feuille paramètre : =parametre!A1 (pour un paramètre stocké en A1 de la feuille paramètre)

3/ Nous visualisons immédiatement le résultat souhaité uniquement pour la ligne 2 :

4/ Pour appliquer la mise en forme conditionnelle sur l’ensemble du tableau, faire clic-droit sur la ligne 2 sélectionnée, copier, puis sélectionner la plage sur laquelle sur laquelle nous souhaitons appliquer la mise en forme conditionnelle (pour une sélection rapide alors que la ligne 2 est déjà sélectionnée et copiée, faire CTRL + shift + flèche bas) : il faut faire sur la sélection clic-droit, collage spécial, collage du format, et nous avons le résultat suivant :

5/ On peut implémenter de cette façon 3 conditions en tout. Voici la 2ème :

6/ Pour le résultat final suivant :

Top
1 2 3 Page 1 of 3

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes