Collage spécial avec transposition

Vous disposez d’un beau tableau en A1:F7, joliment encadré, et dont le seul inconvénient – à vos yeux – est sa disposition avec les produits en colonne et les dates en ligne.

Vous souhaitez en fait refaire ce tableau à partir de la cellule A9, avec cette fois-ci les produits en ligne et les dates en colonne :

Pour cela, copiez le bloc A1:F7, placez le curseur en A9 et utilisez la commande Edition – Collage spécial, en cochant l’option « Transposé ». Et voici le résultat, où vous constatez que tout est parfait, à part l’encadrement qui semble être un peu n’importe quoi mais correspond tout de même à une certaine rationalité :


Il ne vous reste plus qu’à refaire l’encadrement original ou, pour vous simplifier la vie, à utiliser l’outil d’encadrement pour obtenir rapidement un encadrement plus sobre... Notez d’ailleurs que l’option « Tout sauf la bordure » nous aurait évité ce fâcheux résultat au niveau de l’encadrement !

Remarque 1 – Si les cellules du tableau avaient contenu des formules, le tableau aurait vraiment donné n’importe quoi car il n’y a aucune raison que ces formules, une fois transposées, aient encore le moindre sens. Dans un tel cas, il vous faudrait aussi cocher « Valeurs » pour remplacer les formules par leurs valeurs.

Remarque 2 – Quand vous faites ainsi un collage spécial des valeurs, vous perdez tous les formats. Pour récupérer les formats (y compris avec les problèmes d’encadrement vus ci-dessus), il faut ensuite faire un second collage spécial, des formats seuls.

Remarque 3 – On peut regretter que Microsoft ait choisi de mettre des boutons radio dans ce dialogue plutôt que des cases à cocher. Si cela avait été le cas, on aurait pu – en une seule opération au lieu de deux – faire un collage spécial des valeurs et des formats.

Formatage Excel pour code-barres

Un lecteur m'a demandé il y a quelque temps comment formater de façon personnalisée de grands nombres. Il s'agissait de formater des nombres de 13 chiffres pour les codes-barres répondant aux normes GS1 (GTIN-13). Ainsi un nombre tel que "1234567890123" devait être formaté en 1234567.89012.3.

Cela ne pose aucun problème en effet si l'on maîtrise les formats personnalisés d'Excel. Nous avons entré ce nombre dans la cellule A1 (cf. ci-dessous), puis nous l'avons reproduit, avec la formule " =A1 " dans les deux cellules suivantes.

Par défaut, un nombre de cette taille est représenté par Excel en format scientifique, donc sous la forme 1,23457E+12 qui signifie en fait 1,23456 * 10^12.

Pour le représenter "en entier", nous avons créé le format personnalisé 0000000000000, l'avant-dernier format dans l'écran ci-dessous.

Pour enfin le représenter conformément à la représentation requise pour le code-barres, il nous a suffi de créer le format 0000000"."00000"."0.

Les formats personnalisés d'Excel offrent de multiples possibilités, comme nous l'avons déjà montré il y a quelque temps...

Macro de tri pour le pense-bête

Il y a quatre jours, je vous ai présenté mon modèle de pense-bête. Il ne restait plus qu’à lister la macro qui effectue le tri de la liste en ordre de priorité décroissante. Voici cette macro :

Sub Tri()
'
' Macro de tri créée par Hervé Thiriez
'
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Select
End Sub

Et voici le résultat :

Remarque 1 – Sélection de la feuille

La première commande sélectionne la feuille entière, ce qui ne pose pas de problème ici puisqu’il n’y a rien d’autre dans cette feuille que le pense-bête.

Remarque 2 – L’option « Header »

Quand vous enregistrez une macro de ce genre, Excel enregistre de façon automatique l’option « Header:=xlGuess », ce qui pose souvent des problèmes quand on utilise la macro par la suite.

En effet, selon le contenu de la première ligne, Excel peut la considérer ou non comme un titre. Il est donc essentiel, quand on retouche une telle commande pour finaliser la macro, de remplacer cette option, selon le cas, par « Header:=xlYes » ou par « Header:=xlNo ».

Programmation d’un pense-bête

Il m’arrive souvent d’avoir tant de choses à faire que j’ai absolument besoin d’un « pense-bête » pour ne pas oublier telle ou telle tâche urgente.

En général, il me suffit pour cela d’une liste en trois colonnes, avec dans la première colonne l’urgence de la tâche – codée conventionnellement de 1 à 10 – puis le texte associé à la tâche, et enfin un numéro de téléphone (éventuel).

Voici ce que cela peut donner :

Notez que les messages de priorité 10 sont automatiquement codés en rouge et ceux de priorité 9 en bleu. Cette tâche est assurée grâce à un format conditionnel dont voici la définition dans la cellule A1 :

Nous verrons dans le prochain message à quoi sert le bouton « Trier », mais je pense que vous le savez déjà :)

LireDonneesTabCroisDyn (et fin !)

Il y a un bug dans la fonction LireDonneesTabCroisDynamique() avec Excel 2000 !

En effet, quand vous utilisez cette fonction, cela peut provoquer un plantage d’Excel quand les deux conditions suivantes s’appliquent :
- la fonction se trouve dans une feuille différente de celle où se trouve le tableau croisé dynamique ;
- le tableau croisé dynamique contient un champ avec des dates.

La raison du plantage

Pour comparer les valeurs du champ de date avec celle utilisée comme argument par la fonction LireDonneesTabCroisDynamique(), Excel doit formater le champ de date selon le format utilisé dans le TCD pour ce champ. La fonction qui récupère ce format a un argument indiquant dans quelle feuille elle doit le chercher. Dans ce cas, l’argument en question est NULL, ce qui est interprété par Excel comme étant la feuille où se trouve la formule. Si donc cette feuille n’est pas celle du TCD, il y a plantage...

Le remède à appliquer

Pour résoudre ce problème, vous devez disposer de la dernière mise à jour d'Office 2000. Pour avoir plus d’information, sélectionnez l’article suivant dans la basse de connaissance Microsoft :

276367 (http://support.microsoft.com/kb/276367/EN-US/) OFF2000: How to Obtain the Latest Office 2000 Service Pack

La version anglaise de ce patch devrait avoir les attributs suivants, ou des dates plus récentes encore :

Date Time Version Size File name
-----------------------------------------------------
01-Jul-2002 09:42 1,865,364 Excel.msp
01-Jul-2002 10:58 4,470,636 Excel_admin.msp

Eh oui, la micro-informatique n’est pas une science exacte…

LireDonneesTabCroisDyn... (suite)

Avec Excel 2002 et les versions suivantes, quand vous êtes dans une cellule, que vous tapez un signe « = », puis que vous cliquez dans la cellule C7, Excel affiche automatiquement la formule = LireDonneesTabCroisDynamique("Salaire";$A$3;"Pays";"Austria";"Age";26).


Une nouvelle syntaxe depuis Excel 2002

Notons que la syntaxe n’est alors pas la même que celle présentée il y a trois jours. Autrement dit, il y a une seconde syntaxe pour cette fonction depuis la version 2002. Il n’est plus nécessaire de séparer les éléments par un espace : un point-virgule suffit, ce qui est plus naturel.

Ouverture dans un Excel antérieur

Quand vous utilisez cette syntaxe dans un classeur envoyé à un utilisateur d’une version antérieure, cette personne pourra lire dans la cellule le résultat que vous y aviez obtenu, mais il lui sera en revanche tout à fait impossible de modifier cette formule.

En effet, dès que l’on modifie quoi que ce soit dans cette formule – sauf à rétablir les arguments « à l’ancienne » –, on obtient le fatidique message :
« La formule que vous avez tapée contient une erreur » !

LireDonneesTabCroisDynamique()

La fonction LireDonneesTabCroisDynamique() est la fonction d’Excel dont le nom est le plus horrible. Il aurait été tellement plus simple de l’appeler LireDonneesTCD()... Mais pourquoi faire simple quand on peut faire compliqué ?

Le message de ce jour s’adresse en priorité à ceux d’entre vous qui ont une version d’Excel antérieure à la version 2002. En effet, depuis cette version, quand vous faites référence – en cliquant dessus lors de la création d’une formule – Excel incorpore cette fonction de façon automatique.

Prenons un TCD classique :

Vous souhaitez récupérer, de façon dynamique, un renseignement particulier, par exemple le salaire moyen des belges, ou des personnes de 27 ans, ou même de tout le monde.

Pour cela, vous allez utiliser la fonction LireDonneesTabCroisDynamique(). Son premier argument doit être une cellule quelconque du tableau croisé : tant qu’à faire, prenons A3. Le second argument doit être une chaîne de caractères avec – séparés par un espace – les identifiants des critères et les valeurs de ces critères.

Vous pouvez voir dans le tableau suivant les résultats obtenus avec en colonne O la copie des formules entrées en colonne N.

Remarque – Vous aurez noté, par exemple en comparant les formules de N2 et N5, ou N3 et N6, ou encore N8 et N9, qu’il n’est pas nécessaire d’indiquer l’identifiant du critère quand sa valeur ne peut pas être confondue avec les valeurs des autres critères.

Formule matricielle de conso

Nous avons dans les colonnes A à E des codes de répartition, la famille de produit étant identifiée par les deux chiffres à gauche. Dans les colonnes de G à K, nous avons des quantités à consolider :


Enfin, dans le bloc de M à Q, nous souhaitons voir apparaître la somme des quantités appartenant à la même famille que le produit courant. Ainsi, nous avons en O2 et en Q2 la valeur 125 qui est le total du 69 en I2 et du 56 en K2 qui correspondent tous les deux à la famille « 96 », comme le montrent les cellules C2 et E2.

Pour obtenir ce résultat, nous avons entré en M2 la formule matricielle – donc validée par la combinaison [Ctrl]- [Alt]-[Entrée] – suivante :
=somme(si(ent(A2/100)=ent($A2:$E2/100);$G2:$K2;0))

Ensuite, nous avons recopié cette cellule dans tout le bloc M2:Q6.

Remarque – Notez que cela suppose que les codes soient numériques et aient été formatés en « 0000 » pour expliquer l’affichage de A4, E4 et A6... Si ces codes avaient été entrés sous la forme de texte, il aurait fallu remplacer les blocs ent(…/100) par gauche(A2;2) !