Arrêter de recopier des données à la main dans Excel

Si votre travail consiste régulièrement à copier des cellules d'un onglet vers un autre, le problème n'est pas votre vitesse de frappe. C'est la structure du classeur.

Le copier-coller entre onglets est un symptôme, et il annonce toujours la même suite : une erreur de décalage d'une ligne, découverte trois semaines plus tard, dans un fichier dont plus personne ne sait quelle version fait foi.

La règle qui change tout : une donnée, un endroit

Une information ne doit exister qu'à un seul endroit du classeur. Partout ailleurs, elle est référencée, jamais recopiée.

Cela suppose de distinguer trois natures d'onglets, et de ne jamais les mélanger :

La plupart des classeurs qui deviennent ingérables violent cette séparation : on saisit une valeur au milieu d'un tableau de calcul, et six mois plus tard personne ne sait plus laquelle des cellules est une donnée et laquelle est un résultat.

Le tableau structuré, avant tout le reste

Convertir une plage en tableau structuré est le geste le plus rentable d'Excel, et l'un des moins pratiqués.

Ce que cela apporte immédiatement :

• les formules s'étendent seules aux nouvelles lignes ;
• les références deviennent lisibles : Ventes[Montant] au lieu de D2:D5000 ;
• les plages s'ajustent quand les données grandissent, sans qu'un graphique ou un tableau croisé pointe soudain à côté ;
• les filtres et le tri sont posés d'office.

Le réflexe utile : dès qu'un tableau a un en-tête et plus de dix lignes, il devient un tableau structuré. Il faut aussi lui donner un nom explicite — Ventes, Clients — parce que Tableau1 ne vous dira rien dans six mois.

Rapprocher deux tableaux sans les fusionner

C'est la situation qui déclenche le plus de copier-coller : une liste de commandes d'un côté, une liste de clients de l'autre, et le besoin d'afficher le nom du client en face de chaque commande.

La mauvaise réponse consiste à coller les noms dans le tableau des commandes. À la première mise à jour de la liste clients, les deux divergent.

La bonne réponse est une fonction de recherche, qui va chercher la valeur au moment du calcul. Selon votre version d'Excel :

• RECHERCHEX (XLOOKUP) si vous en disposez : elle cherche dans n'importe quel sens, accepte une valeur par défaut si rien n'est trouvé, et ne casse pas quand une colonne est insérée ;
• INDEX + EQUIV (INDEX + MATCH) sinon : plus verbeux, mais tout aussi robuste et disponible partout.

Évitez RECHERCHEV dans les fichiers destinés à durer. Elle ne cherche que vers la droite et repose sur un numéro de colonne : insérez une colonne, et toutes les formules renvoient la mauvaise valeur — silencieusement, sans message d'erreur. C'est le pire des comportements, parce que rien ne signale la panne.

Un détail qui fait perdre des heures : une recherche qui échoue alors que les valeurs semblent identiques vient presque toujours d'un écart de type. Un identifiant stocké en texte d'un côté et en nombre de l'autre ne correspond pas, même si les deux s'affichent pareil.

Quand le classeur devient trop gros

Passé un certain volume, les fonctions de recherche ralentissent et les onglets se multiplient. Deux outils prennent le relais.

Power Query pour tout ce qui est importé et retraité de la même façon à chaque fois : nettoyer, filtrer, dépivoter, fusionner deux sources. Les étapes sont enregistrées et rejouées d'un clic sur les nouvelles données. C'est ce qui remplace véritablement la préparation manuelle.

Le modèle de données pour relier plusieurs tableaux par un identifiant commun, sans les fusionner en un tableau géant. Un tableau croisé dynamique peut alors puiser dans plusieurs sources à la fois. C'est un changement de raisonnement : on cesse de construire un grand tableau plat pour décrire des relations entre tableaux.

Les habitudes qui coûtent cher

• Les cellules fusionnées. Elles cassent le tri, les filtres et les tableaux croisés. Pour centrer un titre, utilisez « centrer sur plusieurs colonnes », qui produit le même effet visuel sans fusionner.
• Les valeurs en dur dans les formules. Un taux de TVA écrit directement dans vingt formules devra être corrigé vingt fois. Mettez-le dans une cellule nommée.
• Les lignes vides au milieu d'un tableau. Elles interrompent les plages et faussent les totaux.
• Plusieurs informations dans une même cellule. « Dupont Jean — Paris » ne se trie pas. Une information par colonne.
• Les dates saisies comme du texte. Elles ne se trient pas et ne se calculent pas.

Ce qu'il faut retenir

• Une donnée existe à un seul endroit, référencée partout ailleurs.
• Séparez données brutes, calculs et restitution.
• Convertissez en tableaux structurés et nommez-les.
• RECHERCHEX ou INDEX+EQUIV, plutôt que RECHERCHEV.
• Power Query pour les imports répétitifs, le modèle de données pour les relations.
• Pas de cellules fusionnées, pas de valeurs en dur, une information par colonne.

Pour aller plus loin

La formation Excel avancé traite les fonctions de recherche, les tableaux structurés et le modèle relationnel, avec des exercices corrigés.

Si les bases ne sont pas encore solides, Excel pour débutants couvre d'abord la saisie propre, les références et les formules courantes.

La ressource Formules Excel avancées réunit les formules utilisables directement, avec leur syntaxe et un exemple.