Se préparer aux tests Excel et bureautiques

Les notions incontournables

Les références relatives et absolues

C’est la notion la plus testée au niveau intermédiaire. Lorsqu’on recopie une formule, les références relatives (A2) se décalent ; les références absolues ($A$2) restent fixes.

Exemple : pour calculer la TVA de chaque ligne avec un taux placé dans la cellule F1, on écrit =B2*$F$1. Sans les dollars, la formule recopiée en ligne 3 deviendrait =B3*F2, qui pointe vers une cellule vide.

Il existe aussi des références mixtes : $A2 (colonne fixe) ou A$2 (ligne fixe), utiles pour les tableaux à double entrée.

Les fonctions conditionnelles

Notez que les versions françaises des tableurs utilisent généralement le point-virgule comme séparateur d’arguments.

Les fonctions de recherche

RECHERCHEV cherche une valeur dans la première colonne d’un tableau et renvoie une valeur d’une autre colonne : =RECHERCHEV(A2;Tarifs!A:C;3;FAUX). Le dernier argument FAUX impose une correspondance exacte : l’oublier est une erreur classique, qui peut renvoyer un résultat faux sans message d’erreur.

RECHERCHEX, disponible dans les versions récentes, est plus souple : =RECHERCHEX(A2;Tarifs!A:A;Tarifs!C:C;"Introuvable"). Elle fonctionne quelle que soit la position des colonnes et gère directement le cas « non trouvé ».

INDEX/EQUIV est l’alternative compatible avec toutes les versions : =INDEX(Tarifs!C:C;EQUIV(A2;Tarifs!A:A;0)).

Trier, filtrer et nettoyer

• Trier sur plusieurs niveaux (par région, puis par montant décroissant).
• Filtrer pour n’afficher que certaines lignes.
• Supprimer les doublons et repérer les espaces en trop (fonction SUPPRESPACE).
• Convertir des nombres stockés comme du texte, une source fréquente d’erreurs de calcul.

Les tableaux croisés dynamiques

Ils permettent de synthétiser une grande liste en quelques clics : total des ventes par région et par mois, nombre de commandes par client, etc. Pour un test intermédiaire, sachez :

1. créer un tableau croisé dynamique à partir d’une plage de données ;
2. placer les champs en lignes, en colonnes et en valeurs ;
3. changer le calcul (somme, nombre, moyenne) ;
4. filtrer et actualiser après modification des données sources.

La mise en forme conditionnelle et les graphiques

• Colorer automatiquement les montants négatifs ou les dates dépassées.
• Choisir le bon graphique : courbes pour une évolution dans le temps, histogramme pour comparer des catégories, secteurs seulement pour quelques parts d’un total.
• Donner un titre et des libellés d’axes clairs.

Le traitement de texte et les autres outils

Les tests bureautiques ne se limitent pas au tableur. Selon le poste, on peut vous demander :

• Traitement de texte : styles de titres, table des matières automatique, en-têtes et pieds de page, numérotation des pages, publipostage (fusion d’un courrier avec une liste d’adresses), suivi des modifications.
• Messagerie et agenda : règles de tri, partage de calendrier, organisation de réunions.
• Présentation : masque des diapositives, cohérence de mise en page.
• Outils collaboratifs : partage de documents, droits d’accès, commentaires, versions.

Pour les notions générales (sécurité, fichiers, outils en ligne), entraînez-vous avec le test de culture numérique.

Un plan d’entraînement sur trois semaines

Astuce : construisez vos propres jeux de données fictifs. Inventer une liste de commandes (date, client, ville, produit, quantité, prix) et se poser des questions dessus (« Quel client a le plus commandé en mars ? ») est l’un des meilleurs entraînements.

Exercice corrigé

Un tableau contient en colonne A le nom des commerciaux, en colonne B la région et en colonne C le montant des ventes (lignes 2 à 200). Le taux de commission est indiqué dans la cellule F1.

Questions : (1) Quel est le total des ventes de la région « Ouest » ? (2) Quelle formule, en D2, calcule la commission de la ligne, de manière à pouvoir être recopiée vers le bas ?

Voir la correction

1. =SOMME.SI(B2:B200;"Ouest";C2:C200)
2. =C2*$F$1 : la référence au taux est absolue pour rester fixe lors de la recopie.

Le jour du test

• Lisez toute la consigne avant de commencer : le résultat attendu est parfois précisé (format, emplacement, arrondi).
• Vérifiez vos résultats par un calcul de contrôle (un total recalculé autrement, un comptage).
• Ne restez pas bloqué : si une fonction ne vous revient pas, passez à la suite et revenez-y.
• Pour un exercice pratique, gardez une présentation lisible : en-têtes clairs, formats de nombres cohérents.
• Si l’interface diffère de la vôtre (version, langue), prenez une minute pour vous repérer : la plupart des fonctions ont les mêmes noms en français.

Les erreurs les plus fréquentes

• Oublier les $ lors de la recopie d’une formule.
• Utiliser RECHERCHEV sans l’argument FAUX.
• Additionner des nombres stockés comme du texte.
• Saisir des valeurs « en dur » au lieu de formules : le fichier devient faux à la moindre modification.
• Choisir un graphique inadapté aux données.

En résumé

Pour réussir un test Excel ou bureautique : situez honnêtement votre niveau, maîtrisez les références absolues, les fonctions conditionnelles et de recherche, les tableaux croisés dynamiques, puis entraînez-vous sur vos propres jeux de données. Pour savoir quelles compétences bureautiques une offre attend réellement, lisez Identifier les compétences recherchées dans une offre.