Mise en forme et tableaux

Un tableau de données brutes, tout gris, est difficile à lire. La mise en forme ne sert pas qu'à faire joli : elle guide l'œil et rend une colonne compréhensible d'un coup. Mais l'apport principal de ce chapitre est ailleurs, dans une distinction que presque personne ne fait au début : ce qu'une cellule contient et ce qu'elle affiche sont deux choses différentes. Confondre les deux produit des erreurs de calcul silencieuses.

Le format ne change pas la valeur

Ce qui est affiché n'est pas ce qui est calculé

Une cellule qui contient 3,14159 et qu'on met au format « deux décimales » affiche 3,14. Elle contient toujours 3,14159, et c'est cette valeur-là qui entre dans les calculs.
Conséquence directe : une colonne de montants arrondis à l'affichage peut donner un total qui ne correspond pas à la somme de ce qu'on lit. Rien n'est faux, mais tout le monde s'en inquiète.
Pour arrondir vraiment, il faut la fonction ARRONDI, qui modifie la valeur. Le format, lui, ne fait que masquer des décimales.
La règle : le format pour la lisibilité, ARRONDI quand la valeur elle-même doit changer.

B5fx

	A	B	C
1	Montant	Format seul	Avec ARRONDI
2	10	10	10
3	10	10	10
4	10	10	10
5	Total	30,01	30
6

B5 contient la formule =SOMME(B2:B4), et affiche 30,01. Les cellules qu'elle utilise sont teintées.

Trois montants identiques. La colonne B les recopie, la colonne C les arrondit pour de bon. Les trois cases affichent 10, et les deux totaux ne sont pas les mêmes : celui de gauche vaut 30,012 et s'affiche 30,01, celui de droite vaut exactement 30. Le premier additionne les valeurs réelles, le second les valeurs arrondies. Sur une facture, l'écart se voit ; sur dix mille lignes, il devient un litige.

Les formats utiles

Format	Ce qu'il fait	Raccourci
Nombre	sépare les milliers, fixe les décimales	`Ctrl + Maj + !`
Monétaire	ajoute le symbole, aligne les montants	`Ctrl + Maj + $`
Pourcentage	multiplie l'affichage par 100 et ajoute `%`	`Ctrl + Maj + %`
Date	affiche un numéro de série comme une date	`Ctrl + Maj + #`
Texte	empêche toute interprétation	par le ruban

Le format pourcentage multiplie l'affichage par cent

Une cellule qui contient 0,2 mise au format pourcentage affiche 20 %. La valeur reste 0,2, et c'est bien elle qu'il faut employer dans les calculs : =A1*0,2, ou mieux =A1*$B$1.
L'erreur classique est de taper 20 dans une cellule déjà au format pourcentage. Le tableur affiche alors 2 000 %, et toutes les formules qui l'emploient sont fausses d'un facteur cent.
Le contrôle : cliquer sur la cellule et lire la barre de formule, qui montre la valeur réelle et non son habillage.

Le tableau structuré

Tableau structuré

Sélectionner une plage et appuyer sur Ctrl + L la transforme en tableau structuré : un objet nommé, que le tableur reconnaît comme un ensemble cohérent.

Quatre choses qu'un tableau structuré apporte gratuitement

Les filtres apparaissent sur chaque en-tête, sans rien faire.
Les formules se propagent. Écrire une formule dans une cellule de la première ligne la recopie automatiquement sur toute la colonne, et sur les lignes ajoutées ensuite.
Les plages s'étendent toutes seules. Un graphique ou un tableau croisé fondé sur ce tableau intègre les nouvelles lignes sans qu'on y touche.
Les formules deviennent lisibles. =SOMME(Ventes[Montant]) se comprend sans ouvrir le fichier, là où =SOMME(D2:D847) ne dit rien et devient faux dès qu'une ligne est ajoutée.

Trier et filtrer

Le tri sur une seule colonne détruit les données

C'est l'accident le plus courant et le plus définitif du tableur.
Sélectionner une seule colonne et la trier réordonne cette colonne seule. Les noms ne suivent plus les notes, les prix ne suivent plus les articles, et rien ne le signale : le tableau reste parfaitement présentable, il est simplement faux.
Les parades sont au nombre de deux, et il suffit d'en appliquer une. Convertir en tableau structuré, qui rend l'accident impossible. Ou cliquer dans une seule cellule avant de trier, sans rien sélectionner : le tableur prend alors le bloc entier.
Si l'accident est arrivé et qu'on l'a enregistré, il n'y a plus de retour en arrière.

Le tableau ci-dessous se trie en cliquant sur les en-têtes, et met en évidence ce que le chapitre 2 décrivait.

n°	Article↕	Catégorie↕	Prix↕	Stock↕	Fournisseur↕
1	Clavier	informatique	25,90	12	Alpha
2	Souris	informatique	12,50	40	Alpha
3	Écran 24	informatique	149,00	5	Beta
4	Chaise	mobilier	89,00	8	Gamma
5	Bureau	Mobilier	249,00	3	Gamma
6	Lampe	mobilier	·	22	Beta
7	Casque	informatique	79,90	15	Alpha
8	Tapis	informatique	9,90	40	Alpha
9	Étagère	mobilier	119,00	40	Gamma
10	Souris	informatique	12,50	40	Alpha
11	Webcam	informatique	45,00	·	Beta
12	Fauteuil	Mobilier	320,00	2	Gamma

valeur manquante2libellé incohérent2relevé tronqué4ligne en double1

12 lignes affichées sur 12, 8 portant au moins un défaut. Cliquer sur un en-tête pour trier : c'est le geste qui fait remonter les anomalies ensemble.

Douze articles. Trier sur « Catégorie » fait apparaître les deux « Mobilier » avec une majuscule, qui compteront comme une catégorie distincte. Trier sur « Stock » regroupe les quatre valeurs à 40, qui sont un plafond suspect. Et la dernière ligne « Souris » est une saisie en double.

Ce que le tri révèle et qu'aucune moyenne ne montre

Trier une colonne est le geste de contrôle le plus rentable qui existe, parce qu'il rassemble les anomalies.

Les cases vides se retrouvent toutes ensemble, en haut ou en bas selon le sens.

Les libellés incohérents se placent côte à côte : « mobilier » et « Mobilier » deviennent voisins et sautent aux yeux.

Les valeurs impossibles se retrouvent aux extrémités : un âge négatif, un prix à sept chiffres, une date en 1899.

Les accumulations suspectes forment un bloc : quatre stocks exactement à 40 sur douze articles ne doivent rien au hasard.

Exercices type

Une colonne de montants affiche `10` sur trois lignes, et le total affiche `30,01`. Le fichier est-il faux ?

Non, et c'est même le comportement normal.

Les cellules contiennent 10,004 et sont affichées arrondies. L'affichage masque la troisième décimale, mais c'est bien 10,004 qui entre dans la somme : trois fois cela fait 30,012, affiché 30,01.

Rien n'est faux, mais personne ne le croira sur parole. Deux réponses possibles.

Laisser tel quel et expliquer, si la précision compte.

Arrondir vraiment avec =ARRONDI(A2;2), qui modifie la valeur et non son habillage. Le total tombe alors sur 30,00, au prix d'une précision perdue.

Sur une facture, la seconde s'impose. Sur des mesures, la première.

Quelle différence entre le format « deux décimales » et la fonction `ARRONDI` ?

Le format change l'affichage, ARRONDI change la valeur.

Une cellule au format deux décimales contenant 3,14159 affiche 3,14 et calcule avec 3,14159.

=ARRONDI(A1;2) produit une nouvelle valeur qui vaut exactement 3,14, et c'est elle qui entre ensuite dans les calculs.

Le choix se fait sur une question simple : la précision perdue doit-elle disparaître pour de bon ? Sur un montant à facturer, oui. Sur une mesure ou un résultat intermédiaire, non, parce que les arrondis successifs s'accumulent.

Une cellule au format pourcentage affiche `2 000 %`. Qu'a-t-on tapé ?

On a tapé 20 dans une cellule déjà au format pourcentage.

Le format pourcentage multiplie l'affichage par cent. Une valeur de 0,2 s'affiche 20 % ; une valeur de 20 s'affiche 2 000 %.

Toutes les formules qui emploient cette cellule sont donc fausses d'un facteur cent, sans qu'aucun message ne le signale.

La correction : retaper 20% avec le signe, ou 0,2 sans lui. Et le contrôle général, valable partout : cliquer sur la cellule et lire la barre de formule, qui montre la valeur réelle.

Pourquoi ne jamais trier une seule colonne ?

Parce que les autres colonnes ne suivent pas, et que le tableau devient définitivement faux sans que rien ne le signale.

Trier la colonne des notes en ayant sélectionné elle seule réordonne les notes ; les noms restent à leur place. Chaque élève se retrouve avec la note d'un autre. Le tableau paraît impeccable.

Deux parades, et une seule suffit. Convertir en tableau structuré avec Ctrl + L, ce qui rend l'accident impossible. Ou cliquer dans une seule cellule avant de trier, sans rien sélectionner : le tableur prend alors le bloc entier.

Une fois l'accident enregistré, il n'y a pas de retour en arrière.

Qu'apporte un tableau structuré qu'une simple plage n'apporte pas ?

Quatre choses, sans rien coder.

Les filtres apparaissent tout seuls sur chaque en-tête.

Les formules se propagent : écrite une fois, une formule descend sur toute la colonne, y compris sur les lignes ajoutées plus tard.

Les plages s'étendent : les graphiques et tableaux croisés qui s'appuient dessus intègrent les nouvelles lignes automatiquement.

Les formules deviennent lisibles : =SOMME(Ventes[Montant]) se comprend sans ouvrir le fichier, et reste juste quand des lignes s'ajoutent, là où =SOMME(D2:D847) devient faux.

Le raccourci est Ctrl + L, et il n'y a pratiquement aucune raison de s'en priver.

Comment repérer rapidement les défauts d'une colonne fraîchement reçue ?

En la triant, dans les deux sens.

Le tri rassemble les anomalies au lieu de les disperser. Les cases vides se groupent à une extrémité. Les libellés incohérents comme « mobilier » et « Mobilier » deviennent voisins. Les valeurs impossibles apparaissent aux extrêmes. Les accumulations suspectes, comme quatre stocks exactement à 40, forment un bloc visible.

Aucun résumé statistique ne montre cela : une moyenne ne dit rien d'une majuscule mal placée, et un écart-type ne voit pas un doublon.

C'est le geste de contrôle le plus rentable du tableur, et il coûte un clic.

Vérification rapideon peut se reprendre

1.Une cellule contient 3,14159 et l'affiche comme 3,14. Que vaut-elle dans un calcul ?

2.Une cellule au format pourcentage affiche 2 000 %. On a probablement tapé…

3.Trier une seule colonne sélectionnée…

4.Que fait Ctrl + L sur une plage de données ?

5.Quel est le geste le plus rentable pour contrôler une colonne reçue ?

La méthode

Avant toute mise en forme, cliquer sur une cellule et lire la barre de formule : elle montre la valeur réelle, sous l'habillage.
Choisir le format d'après la nature de la donnée : Ctrl + 1 ouvre la boîte, monétaire pour un montant, pourcentage pour un taux, date pour une date.
Saisir un taux avec son signe (20%) ou sous sa forme décimale (0,2), jamais 20 dans une cellule déjà au format pourcentage.
Employer ARRONDI quand c'est la valeur qui doit changer, et laisser le format s'occuper du seul affichage.
Convertir la plage en tableau structuré avec Ctrl + L avant tout tri, filtre ou graphique.
Ne jamais sélectionner une seule colonne avant de trier. Cliquer dans une cellule quelconque du bloc suffit à ce que le tableur prenne le bloc entier.
Trier chaque colonne reçue dans les deux sens : vides, doublons, libellés incohérents et valeurs impossibles se rassemblent aux extrémités.
Enregistrer avant de trier un fichier qui n'est pas en tableau structuré. Un tri désolidarisé ne se défait plus une fois le fichier refermé.

Synthèse

Le format change l'affichage, jamais la valeur ; seul ARRONDI change la valeur.
Un total qui ne correspond pas à la somme des montants affichés n'est pas faux : il est arrondi ailleurs.
Le format pourcentage multiplie l'affichage par cent ; taper 20 au lieu de 0,2 fausse tout d'un facteur cent.
La barre de formule montre toujours la valeur réelle, sous l'habillage.
Le format se choisit selon la nature de la donnée : nombre, monétaire, pourcentage, date, texte ; Ctrl + 1 les donne tous.
Un tableau structuré (Ctrl + L) apporte filtres, propagation des formules, plages extensibles et formules lisibles.
Trier une seule colonne désolidarise les lignes, sans avertissement et sans retour possible.
Cliquer dans une seule cellule avant de trier suffit à ce que le bloc entier suive.
Trier une colonne est le contrôle le plus rentable : il rassemble vides, doublons, libellés incohérents et valeurs impossibles.

Ce que ce chapitre apporte