Corrigé du Test Excel : prérequis de Power BI

Nous vous proposons un article de comprendre les bases d'Excel. Cela est indispensable pour pouvoir prétendre à faire du power BI.

Nombre de point nécessaire pour la formation power BI : 20/38

Sommaire

Thème 0 : Introduction à Excel
Thème 1 : Mise en forme - Fonctions simples
Thématique 2: Fonctions logiques SI / ET / OU
Thème 3 : Filtre / Mise en forme sous tableau
Thème 4 : Graphiques
Thème 5 : Fonctions – Date / Statistiques / Texte
Thème 6 : Tableaux Croisés Dynamiques (TCD)

Thème 0 : Introduction à Excel

Question 1 – Les formats de fichiers Excel

Excel propose plusieurs formats courants :

.xls : Ancien format (avant 2007), limité en lignes, colonnes et fonctionnalités. À convertir en format récent (ex. .xlsx) pour gagner en performance et en légèreté.
.xlsm : Identique au .xlsx mais accepte les macros VBA. Astuce : convertir un .xlsx en .xlsm peut réduire considérablement la taille du fichier.
.csv : Fichier texte brut où les données sont séparées par des virgules (ou autres délimiteurs). Idéal pour l’échange entre applications, mais sans mise en forme (gras, couleurs, etc.).

❌ Le format .xlsR n’existe pas.

Question 2 – Différence entre filtre et lignes masquées

Quand Excel saute des lignes (ex. de la ligne 3 à la 11), deux cas possibles :

Filtre activé : Des lignes sont temporairement invisibles via un filtre. L’icône d’entonnoir dans l’en-tête le confirme.
Lignes masquées : Les lignes sont volontairement masquées (clic droit > Masquer) mais toujours présentes. Pour les afficher : sélectionner les lignes voisines > clic droit > Afficher.

✅ Ici, il s’agit de lignes masquées.

Question 3 – Faire disparaître le ruban

Deux méthodes :

Ctrl + F1 : Raccourci clavier pour masquer/afficher le ruban.
Double-clic sur un onglet (ex : Accueil) : Cache ou fait réapparaître le ruban.

✅ La bonne réponse est le double-clic sur un onglet.

Question 4 – Définition d’un saut de page

Un saut de page délimite la fin d’une page à l’impression. Excel insère automatiquement ces sauts ou permet de les placer manuellement via l’onglet Mise en page.

Pour visualiser les sauts automatiques : Fichier > Imprimer. Les lignes pointillées indiquent où les pages se séparent.

❌ Le "limitateur de page" n’existe pas.

✅ Réponse : saut de page.

Question 5 – Qu’est-ce qu’un commentaire dans Excel ?

Un commentaire est une note attachée à une cellule, repérable par un triangle rouge. En passant la souris ou en cliquant sur la cellule, la note s’affiche.

Pour ajouter un commentaire : clic droit sur la cellule > Insérer un commentaire.

Les commentaires permettent d’ajouter des explications ou des instructions et peuvent être personnalisés (taille, police, couleurs).

✅ Réponse : : Un commentaire

Thème 1 : Mise en forme - Fonctions simples

Question 1 – Quelle syntaxe pour une formule ?

Une formule Excel doit toujours commencer par un symbole. Le plus courant est "=", mais "+" ou "-" fonctionnent également. Sans cela, Excel considère le contenu comme du texte.

Exemple correct :=A1+B1 renverra la somme des cellules A1 et B1.

✅ Réponse attendue : 12

Question 2 – Calcul d’une augmentation salariale

Pour calculer uniquement l'augmentation :👉 BQ3 * BR3

Pour obtenir le salaire avec augmentation :👉 BQ3 * BR3 + BQ3

🎯 Astuce : pensez toujours à décomposer la question entre "augmentation seule" et "salaire total avec augmentation".

Question 3 – Priorité des opérations

Les règles de priorité sont les suivantes :

Parenthèses
Multiplication / Division
Addition / Soustraction

Ici :(6) - 2 * (4) = 6 - 8 = -2

✅ Réponse : -2

Question 4 – Reconnaissance de l’interface Excel

L’icône représentant deux flèches qui s’écartent correspond à l’accès aux paramètres avancés de la police.

Depuis cette fenêtre, vous pouvez :

Changer la police, la taille, le style (gras, italique, souligné)
Appliquer des effets (barré, exposant)
Modifier la couleur et d'autres options typographiques

✅ Réponse : Afficher les paramètres de la police et me proposer d'autres options

Question 5 – Fonction MAX et piège d’observation

La fonction MAX(C3:C7) recherche le plus grand nombre entre C3 et C7.

Observation :

Valeur max entre C3 et C7 = 3200
Erreur courante : prendre en compte des cellules hors de la plage indiquée.

✅ Réponse : 3200

Synthèse du test :

Ce test vérifie vos bases Excel mais aussi votre capacité à :

Lire attentivement une consigne
Appliquer des notions de logique et de mathématiques simples
Reconnaître des éléments de l’interface d’Excel

👉 Ce type de question est typique d’un niveau faux débutant, où certaines bases restent encore à consolider.

Thématique 2: Fonctions logiques SI / ET / OU

Question 1 – Lier deux cellules texte

Pour lier deux cellules contenant du texte dans Excel, deux options principales :

Fonction CONCATENER (ancienne méthode)
Opérateur "&" (méthode rapide et moderne)

⚠ Le format [@Nom] provient de la mise en forme sous tableau, qui modernise l’affichage et la structuration de vos données.

Avantages de la mise en forme sous tableau :

Organisation claire en colonnes et lignes
Filtres et tris automatiques
Calculs dynamiques (somme, moyenne…)
Mise en forme cohérente
Tableau réactif aux ajouts/suppressions

✅ Réponse : "&"

Question 2 – Syntaxe de nom de plage

Un nom de plage permet de remplacer une référence type C2:C6 par un nom (ex. PlageN) pour rendre vos formules plus lisibles.

✅ La formule est correcte uniquement si la plage s’appelle bien "PlageN".

Avantages :

Formules plus compréhensibles
Réutilisation facile
Souplesse avec les fonctions classiques (SOMME, MOYENNE, etc.)

Question 3 – Formule SI sur le mot "Homme"

Vous souhaitez tester si la cellule F2 contient "Homme" et afficher "oui" ou "non" selon le cas.

Structure de base : =SI(condition; "valeur si vrai"; "valeur si faux")

Application : =SI(F2="Homme";"oui";"non")

⚠ Attention à la casse : "Homme" doit être écrit avec un "H" majuscule.

Question 4 – Valeur retournée par la formule

Formule : =SI(H2<=100;I2;"N/A")

H2 = 50 (50 ≤ 100 ? Oui)
Si vrai → valeur de I2
I2 = 200

✅ Réponse : 200

Question 5 – SI + ET combinés

Formule : =SI(ET(H2<=100;I2="ok");"FAUX";"VRAI")

H2 = 50 → 50 ≤ 100 ? Oui
I2 = "ok" ? Oui
ET renvoie VRAI si les deux conditions sont vraies

Donc la réponse renvoyée est "FAUX".

✅ Réponse : "FAUX"

Question 6 – SI imbriqués

Formule : =SI(H2>=100;SI(1000>I2;"OUI";"NON");"PAS OK")

H2 = 101 (>100) → passe à la 2ᵉ condition
I2 = 300 (<1000) → vrai

✅ Réponse : "OUI"

Question 7 – Références absolues avec $

Lorsque vous copiez une cellule avec $ (ex : $A$1), la référence ne change pas.

Ici, vous faites un glisser-déposer, mais le $ empêche le décalage.

✅ Réponse : 20% (même valeur que la cellule source)

Question 8 – Cellule modifiable malgré la protection

Explication : même si la feuille est protégée et les cellules verrouillées, l’utilisateur peut autoriser des zones modifiables via la fonction "Autoriser la modification des plages".

✅ Réponse : Une plage modifiable a été définie pour la cellule verte.

Thème 3 : Filtre / Mise en forme sous tableau

Question 1 – Quelle affirmation est fausse ?

Excel permet de filtrer sur :

Valeurs numériques (ex. supérieur à, inférieur à)
Textes (ex. contient, commence par)
Dates (avant, après, aujourd'hui)
Couleurs de cellules (fond ou police)
États (vide, non vide, erreurs)
Formats de données (texte, nombre, pourcentage)
Critères personnalisés via formules

Mais on ne peut pas filtrer sur la police d’écriture (gras, italique, etc.).

✅ Réponse : Une police d’écriture

Question 2 – Calculer une moyenne qui s’adapte aux filtres

La seule fonction qui prend en compte les lignes visibles après un filtre est SOUS.TOTAL().

Exemple :SOUS.TOTAL(101; plage) pour une moyenne SOUS.TOTAL(109; plage) pour une somme

⚠ Attention : SOUS.TOTAL ignore les lignes masquées manuellement, mais pas celles masquées par un filtre automatique.

✅ Réponse : SOUS.TOTAL()

Question 3 – Filtre multi-critères complexes

Vous souhaitez appliquer deux critères différents sur une même colonne :

Managers avec un salaire > 2500 €
Techniciens avec un salaire entre 1800 € et 2200 €

Les filtres standards ne le permettent pas directement. Solution recommandée : utiliser les filtres avancés d’Excel.

✅ Réponse : Filtres avancés

Question 4 – Syntaxe de calcul en mise en forme sous tableau

Dans un tableau structuré (mise en forme sous tableau), les formules utilisent la syntaxe suivante :=SOMME(MonBeauTableau[Colonne1]+MonBeauTableau[Colonne2])

La formule est valide uniquement si le tableau a été renommé en "MonBeauTableau" via le Gestionnaire de noms.

✅ Réponse : Oui, si le tableau s’appelle bien "MonBeauTableau"

Question 5 – Résultat du filtre avancé

Critères du filtre avancé :

Colonne C = "Excel" et Colonne D ≤ 1200
Colonne C = "Word"

Analyse de la plage A6:D12 :

Lignes 9 et 10 respectent le critère "Word"
Ligne 12 respecte le critère "Excel" avec montant ≤ 1200

✅ Réponse : Lignes 9, 10 et 12

Petit rappel théorique sur les filtres avancés :

Permettent de filtrer avec des conditions ET et OU combinées
Peuvent copier les résultats filtrés vers une autre zone
Idéal pour des filtres complexes (multi-colonnes avec plusieurs critères)

Thème 4 : Graphiques

Question 1 – À quoi correspondent les chiffres ?

1 = Titre du graphique
2 = Étiquette de données
3 = Table de données
4 = Légende

Question 2 – Identifier les types de graphiques

1 = Courbe
2 = Histogramme
3 = Aires
4 = Secteur (ou Camembert)

📈 Courbe

Idéal pour suivre des tendances sur une période.
Représentation précise des valeurs avec des points bien visibles.
Plus lisible qu’un graphique en aire pour les données exactes.

📊 Histogramme

Utilisé pour analyser la distribution de données quantitatives.
Visualisation rapide des fréquences, écarts et valeurs extrêmes.
Parfait pour comparer des groupes sur une même échelle.

🟢 Aires

Utile pour illustrer des variations cumulées ou continues.
Donne un effet de "volume" avec des zones colorées sous la courbe.
Possibilité d’empiler les aires pour comparer plusieurs séries.

🥧 Secteur (Camembert)

Met en avant des proportions ou des répartitions (%).
Utilisé pour représenter des parts de marché, des répartitions budgétaires ou des résultats de sondages.

🎯 Ces quatre types sont les bases incontournables pour vos démonstrations et rapports.

Question 3 – Passer du graphique 1 au graphique 2

Pour modifier l’apparence (espacement entre les barres) :

Sélectionner la série de données
Clic droit > Mettre en forme la série de données
Ajuster la largeur de l’intervalle

✅ Réponse : Changer l’option "largeur de l’intervalle" après avoir sélectionné la série

Thème 5 : Fonctions – Date / Statistiques / Texte

Question 1 – Que donnera la cellule C4 ?

La fonction FIN.MOIS() renvoie la dernière date du mois à partir d’une date de départ et d’un nombre de mois à ajouter.

Exemple :=FIN.MOIS("02/02/2019";1)➔ On part de février 2019, on ajoute 1 mois ➔ fin mars 2019 ➔ 31/03/2019

✅ Réponse : 31/03/2019

Question 2 – Fonction MAINTENANT()

MAINTENANT() renvoie la date et l’heure actuelles selon l’horloge de l’ordinateur.

Pas d’argument nécessaire.
La cellule se met à jour automatiquement à chaque recalcul ou ouverture du fichier.

✅ Réponse : Elle renvoie la date du jour et l'heure du jour, sous la forme de date et d'heure

Question 3 – Compter les femmes avec plus de 5 ans d’ancienneté

Utilisez NB.SI.ENS() pour appliquer plusieurs critères simultanément.

Formule :=NB.SI.ENS(BC2:BC9;"Femme";BD2:BD9;">5")

Critère 1 : sexe = Femme
Critère 2 : ancienneté > 5 ans

✅ Réponse : =NB.SI.ENS(BC2:BC9;"Femme";BD2:BD9;">5")

Question 4 – Somme des coûts de formation pour les "Employés" formés sur "Excel"

La fonction SOMME.SI.ENS() additionne les valeurs correspondant à plusieurs conditions.

Formule :=SOMME.SI.ENS(E2:E6;D2:D6;"Excel";B2:B6;"Employé")

Critère 1 : formation = Excel
Critère 2 : statut = Employé

✅ Réponse : =SOMME.SI.ENS(E2:E6;D2:D6;"Excel";B2:B6;"Employé")

Question 5 – Remplacer du texte sans utiliser "Rechercher/Remplacer"

La fonction SUBSTITUE() permet de modifier un texte directement via une formule.

Syntaxe :SUBSTITUE(texte; ancien_texte; nouveau_texte; [num_occurrence])

Elle remplace l'ancien texte par le nouveau, sans action manuelle.

✅ Réponse : Avec la fonction SUBSTITUE

Thème 6 : Tableaux Croisés Dynamiques (TCD)

Un TCD est un outil puissant pour analyser, synthétiser et visualiser rapidement des données volumineuses. Excel permet de créer des TCD pour produire des indicateurs et des résumés dynamiques.

Question 1 – Dans mon TCD, j'ai "la somme d'ancienneté" par genre. Je souhaite obtenir une moyenne des anciennetés par genre. Comment fait-on cela ?

Pour obtenir la moyenne au lieu de la somme dans un TCD :

Clic droit sur "Somme de Ancienneté"
Sélectionner "Paramètres des champs de valeur"
Choisir "Moyenne" dans la liste

✅ Réponse : Paramètres des champs de valeur > Moyenne

💡 Vous pouvez également :

Ajouter des champs calculés
Appliquer des groupements (dates, valeurs)
Personnaliser l’aspect visuel du TCD pour plus de lisibilité

Question 2 – J'ai groupé mon TCD par "Année" et je l'ai trié par ordre croissant. Comment fait-on cela ?

Si le découpage par Année est automatique (versions Excel > 2016) :

Cliquez sur "Étiquettes de lignes"
Choisissez "Trier de A à Z"

⚠ Avant de créer un TCD, vérifiez bien :

La source (bon fichier ou plage)
La structure (colonnes bien nommées, sans cellule vide)
La fiabilité des formats (dates cohérentes, nombres homogènes)

✅ Réponse : Étiquettes de lignes > Trier de A à Z

Question 3 – Pourquoi ce message d’alerte à l’actualisation ?

Lors de l’actualisation d’un TCD, si la plage de données s’agrandit, le TCD risque d’écraser des données situées en dessous.

🚩 Message d’alerte : Excel vous signale qu’une zone sera écrasée.

Bonne pratique :

Ne jamais empiler plusieurs TCD verticalement
Les placer côte à côte pour éviter les conflits

✅ Réponse : La taille du TCD augmente et risque d’écraser des données existantes

Thème 7 : Fonctions Avancées et RechercheX

Question 1 – Que va renvoyer la fonction ?

La fonction RECHERCHEX(B4; G2:G9; F2:F9; "Pas ok") recherche la valeur de la cellule B4 (ici "Rose") dans la plage G2:G9.

"Rose" est trouvée en G8.
La valeur renvoyée sera donc celle de F8, soit deep.

✅ Réponse : deep

💡 À retenir : RECHERCHEX remplace avantageusement RECHERCHEV, avec plus de souplesse (recherche horizontale ou verticale possible et colonne de retour libre).

Question 2 – Dans quel cas la fonction RECHERCHEX va fonctionner ?

RECHERCHEX fonctionne aussi bien en recherche verticale qu'en horizontale, contrairement à RECHERCHEV ou RECHERCHEH.

✅ Réponse : Si on fait une recherche Horizontale

Question 3 – Je suis passé du tableau 1 au tableau 2 en utilisant la fonction office 365 : TRIER. Pouvez-vous me dire la formule exacte ?

La fonction TRIER permet d’organiser dynamiquement un tableau sans modifier les données sources.

Ici, on trie la plage F2:I9 sur la 2ᵉ colonne en ordre décroissant (paramètre -1).

✅ Réponse : =TRIER(F2:I9; 2; -1)