Un Excel à ta main, des données qui se retrouvent

Un expert ne subit pas Excel : il le règle. Tout passe par Fichier › Options, une fenêtre à douze rubriques. Trois d'entre elles changent vraiment ta façon de travailler :

Rubrique	Ce qu'on y règle	Pourquoi un expert y va
Général	Police et taille par défaut, nombre de feuilles d'un nouveau classeur, thème Office (Coloré, Gris foncé, Noir, Blanc), nom d'utilisateur, écran de démarrage	Retrouver la même police dans tous ses classeurs ; passer en Noir pour les yeux
Options avancées	Sens du déplacement après Entrée, séparateur décimal, affichage des zéros, des onglets de feuilles, des barres de défilement, des en-têtes de lignes et de colonnes	Masquer le quadrillage et les en-têtes d'une feuille « tableau de bord »
Personnaliser le ruban	Afficher ou masquer un onglet (Développeur), créer son propre onglet et ses groupes, renommer, réinitialiser, importer et exporter ses réglages	Regrouper ses dix commandes de tous les jours dans un onglet à son nom

Deux autres réglages se font hors des Options : la barre d'état (clic droit dessus : Moyenne, Nombre, Somme, Mode de calcul, Zoom…) et les options d'affichage du ruban (bouton ⌄ à droite du ruban : Ruban simplifié, Afficher les onglets uniquement, Plein écran).

En troisième année, RECHERCHEV t'a rendu service, avec ses deux limites : elle ne cherche que dans la première colonne de la table, et elle fige un numéro de colonne qui devient faux dès qu'on insère une colonne au milieu. Le couple INDEX + EQUIV n'a aucune de ces limites.

Fonction	Ce qu'elle fait	Écriture
EQUIV	Donne la position d'une valeur dans une plage (7 pour la 7e ligne)	=EQUIV(valeur_cherchée;plage;0)
INDEX	Donne le contenu de la ne cellule d'une plage	=INDEX(plage;n)
Les deux	Cherche la référence dans la colonne A, rapporte le prix de la colonne E	=INDEX(Produits!$E$2:$E$47;EQUIV(A2;Produits!$A$2:$A$47;0))

Le 0 d'EQUIV

Le troisième argument d'EQUIV joue le rôle du FAUX de RECHERCHEV : 0 exige une correspondance exacte. Avec 1 (ou rien), Excel suppose la plage triée en ordre croissant et renvoie la position de la plus grande valeur inférieure ou égale ; avec -1, l'inverse sur une plage décroissante. Pour une référence produit, c'est toujours 0.

Pourquoi l'expert la préfère

• On peut chercher à droite et rapporter à gauche : la colonne de recherche n'a pas à être la première.
• Les deux plages sont des références : une colonne insérée dans le catalogue les décale toutes seules.
• INDEX à deux dimensions : =INDEX(table;EQUIV(ligne;…;0);EQUIV(colonne;…;0)) retrouve une valeur au croisement d'une ligne et d'une colonne, comme une grille tarifaire.

Une mise en forme automatique est une mise en forme nommée, qu'Excel applique d'un clic et qu'on peut modifier partout à la fois. Excel en a trois familles :

Famille	Où	Ce que ça apporte
Styles de cellules	Accueil › Styles › Styles de cellules	Titre, En-tête 1, Entrée, Calcul, Satisfaisant, Monétaire… Un style modifié (clic droit › Modifier) change toutes les cellules qui le portent
Styles de tableau	Accueil › Mettre sous forme de tableau	Une galerie Clair / Moyen / Foncé ; en bas, Nouveau style de tableau pour créer le sien (lignes à bandes, en-tête, ligne de total…)
Thèmes	Mise en page › Thèmes (vu en BA3)	Les couleurs et polices que tous les styles utilisent

Créer son style

1. Accueil › Styles de cellules › Nouveau style de cellule… : un nom, puis Format pour régler nombre, police, bordure, remplissage.
2. Pour un tableau : Mettre sous forme de tableau › Nouveau style de tableau… : chaque élément (Ligne d'en-tête, Première colonne, Ligne à bandes 1…) reçoit sa mise en forme.
3. Coche Définir comme style par défaut pour que tous les prochains Tableaux du classeur le prennent.

Ctrl+L crée un Tableau : tu le sais depuis la deuxième année. L'expert s'en sert comme d'une base de données : nommée, auto-extensible, source des formules, des tableaux croisés, des segments et des graphiques.

Les références structurées

Dans un Tableau nommé Commandes, Excel n'écrit plus G2 mais [@Total_HT] : « la cellule de la colonne Total_HT, sur cette ligne ». Une formule tapée dans une cellule de la colonne se propage à toute la colonne (colonne calculée), nouvelles lignes comprises.

Référence	Désigne
Commandes	Toutes les données, sans l'en-tête
Commandes[Total_HT]	Une colonne entière (sans l'en-tête)
[@Total_HT]	La cellule de cette colonne sur la ligne courante (dans le Tableau)
Commandes[#En-têtes], Commandes[#Totaux]	La ligne d'en-tête, la ligne de total
Commandes[[#Tout];[Canal]]	La colonne Canal avec son en-tête et son total

La ligne de total

Création de tableau › Ligne Total ajoute une dernière ligne où chaque cellule propose une liste : Somme, Moyenne, Nombre, Max, Min… Excel y écrit =SOUS.TOTAL(109;[Total_HT]) : le 109 ignore les lignes filtrées et les lignes masquées, le total suit donc le filtre.

Par défaut, Excel cache un onglet entier : Développeur. C'est là que vivent les macros, l'éditeur Visual Basic, les contrôles de formulaire et les compléments. Pour l'afficher : Fichier › Options › Personnaliser le ruban, puis, dans la liste de droite, coche Développeur. Le réglage est fait une fois pour toutes, pour tous les classeurs.

Groupe	Commandes	Tu t'en sers pour
Code	Visual Basic (Alt+F11), Macros (Alt+F8), Enregistrer une macro, Utiliser les références relatives, Sécurité des macros	Enregistrer, lire, modifier, lancer une macro (séances 2 et 3)
Compléments	Compléments, Compléments Excel, Compléments COM	Charger le Solveur ou l'Utilitaire d'analyse
Contrôles	Insérer (contrôles de formulaire et ActiveX), Mode Création, Propriétés, Visualiser le code	Poser une case à cocher, une liste déroulante, un bouton (séance 5)
XML	Source, Propriétés du mappage, Importer, Exporter	Échanger des données avec un autre système

Tu connais Rechercher et remplacer (Ctrl+H), qui modifie les cellules une fois pour toutes. La fonction REMPLACER fait la même chose par formule, mais elle raisonne par position : « à partir du caractère n°, sur tant de caractères, mets ceci ».

Formule	Résultat	Lecture
=REMPLACER("CMD-2025-0113";5;4;"2026")	CMD-2026-0113	À partir du 5e caractère, 4 caractères remplacés par 2026
=REMPLACER(A2;1;3;"REF")	REF-2025-0113	Les trois premiers caractères deviennent REF
=REMPLACER(A2;4;0;"X")	CMDX-2025-0113	0 caractère remplacé : insertion d'un X à la 4e position
=SUBSTITUE(A2;"2025";"2026")	CMD-2026-0113	Remplace un texte, où qu'il soit, toutes les occurrences

REMPLACERB, et les fonctions en B

REMPLACERB, GAUCHEB, NBCARB… comptent en octets et non en caractères : elles existent pour les langues à caractères sur deux octets (japonais, chinois, coréen) quand une langue de ce type est la langue d'édition par défaut. En français, REMPLACERB donne exactement le même résultat que REMPLACER. Le référentiel la cite ; tu sais maintenant ce qu'elle est, et que tu ne t'en serviras pas.

Mise en page › Mise en page › Arrière-plan pose une image derrière toutes les cellules de la feuille active : logo en filigrane, texture, photo. L'image se répète en mosaïque pour couvrir la feuille, et le bouton devient Supprimer l'arrière-plan.

Ce qu'il faut savoir	Conséquence
L'arrière-plan ne s'imprime pas	Pour un filigrane imprimé (« BROUILLON », logo), il faut passer par l'en-tête (Insertion › En-tête et pied de page › Image) ou par une forme
Il vaut pour une feuille	Chaque feuille a le sien ; grouper les feuilles n'applique pas l'arrière-plan aux autres
Le quadrillage reste visible	Décoche Affichage › Quadrillage pour un rendu propre
Un fond de cellule le recouvre	Les cellules colorées cachent l'image : utile pour ne montrer le filigrane que dans les marges

La poignée de recopie fait 90 % du travail. Le bouton Accueil › Édition › Recopier (l'icône de flèche bleue vers le bas, que l'aide appelle aussi « Remplissage ») fait les 10 % restants, ceux qui demandent de la précision :

Commande	Ce qu'elle fait	Raccourci
En bas, À droite…	Recopie la première cellule de la sélection sur toute la sélection	Ctrl+B (en bas), Ctrl+D (à droite)
Vers les feuilles de travail	Copie la sélection aux mêmes cellules de toutes les feuilles groupées (contenu, formats, ou les deux)	—
Série	Une série réglée : linéaire (pas), géométrique (×), chronologique (jour, jour de semaine, mois, année), dernière valeur	—
Justifier	Répartit un texte long sur plusieurs lignes de cellules, à la largeur de la colonne	—
Remplissage instantané	Ce que fait Ctrl+E (vu en BA3)	Ctrl+E

La boîte Série de données

Elle vaut le détour pour deux cas que la poignée ne sait pas faire : une série qui s'arrête à une dernière valeur sans qu'on sache combien de cellules sélectionner (tape 1 en A1, Série › Colonnes › Pas 1 › Dernière valeur 500), et une série de dates en jours de semaine (l'option s'appelle ainsi), qui saute les week-ends.

Trois magasins, trois feuilles de même structure, et la direction veut le total. La formule 3D (BA3) marche si les feuilles sont strictement identiques. Données › Outils de données › Consolider va plus loin : il additionne (ou moyenne, compte, maximise…) des plages qui n'ont pas la même taille ni le même ordre de lignes, en se fiant aux étiquettes.

1. Place-toi sur la feuille de synthèse, cellule du coin supérieur gauche du résultat.
2. Données › Consolider, choisis la Fonction (Somme).
3. Dans Référence, sélectionne la plage du premier magasin, étiquettes comprises, puis Ajouter ; recommence pour chaque source.
4. Coche Étiquettes dans : Ligne du haut et Colonne de gauche : Excel aligne les produits par leur nom, même si l'ordre diffère.
5. Lier aux données source : coché, le résultat est fait de formules et d'un plan (boutons 1 et 2 à gauche) qui suit les sources ; décoché, ce sont des valeurs figées.

Situation	Outil
Feuilles identiques, cellule à cellule	Formule 3D : =SOMME(Lille:Lyon!B5)
Mêmes rubriques, ordre ou nombre de lignes différents	Consolider, avec les étiquettes
Données brutes à additionner par catégorie	Tableau croisé dynamique

Ces six questions reprennent toute la séance, au format et au niveau de la certification TOSA. Réponds sans revenir en arrière.