Fiche de révision : Maîtriser Excel et ses analyses

📋 Plan du Cours

1. Structure et mise en forme des feuilles
2. Formules, références et fonctions
3. Calculs conditionnels et mise en forme
4. Recherches et tableaux structurés
5. Création et structure des tableaux croisés
6. Analyse et exploration des tableaux croisés
7. Visualisation et actualisation des analyses

📖 1. Structure et mise en forme des feuilles

🔑 Notions clés & Définitions

• Classeur : Fichier composé de plusieurs feuilles de calcul.
• Feuille de calcul : Constituée de lignes et de colonnes qui forment des cellules où l’on saisit les données.

★ À maîtriser

• Excel permet d’organiser, de mettre en forme et d’analyser des données dans une grille de lignes, de colonnes et de cellules.

• La mise en forme modifie l’affichage des données sans modifier leur valeur.

Compléments

• La préparation à l’impression comprend :

  • le réglage des marges et de l’orientation
  • la définition des zones d’impression
  • l’ajout d’en-têtes et de pieds de page
  • le contrôle des sauts de page

• Les outils de mise en forme couvrent : les formats numériques, notamment les dates, la monnaie et les pourcentages, la police, la taille et l’alignement du texte, les styles de cellule, les couleurs de cellule

📖 2. Formules, références et fonctions

★ À maîtriser

• Une formule Excel peut utiliser : la saisie manuelle, les opérateurs arithmétiques +, -, * et /, les opérateurs de comparaison =, <, >, <= et >=, des références de cellules

📌 Une référence relative peut évoluer lorsqu’une formule est copiée, tandis qu’une référence absolue conserve son repérage.

• Les fonctions mathématiques intégrées comprennent :
  • SUM
  • AVERAGE
  • PRODUCT
  • MIN
  • MAX

Compléments

• Pour réutiliser une formule, on peut :

  • employer la poignée de recopie
  • utiliser le copier-coller
  • appliquer la formule à une plage de cellules

• Les fonctions intégrées de texte comprennent :

  • LEN
  • FIND
  • TRIM
  • LEFT
  • RIGHT
  • MID
  • CONCATENATE
  • UPPER
  • LOWER
  • PROPER

• Excel propose des fonctions pour arrondir ou tronquer les nombres (ROUND, INT et TRUNC), compter des cellules (COUNT, COUNTA et COUNTBLANK) et effectuer des calculs sur les dates et les heures.

📖 3. Calculs conditionnels et mise en forme

🔑 Notions clés & Définitions

• Fonction IF : La fonction IF teste une condition et renvoie un résultat si elle est vraie et un autre si elle est fausse ; sa structure est IF(logical_test, value_if_true, value_if_false).

★ À maîtriser

📌 La fonction IFS teste plusieurs conditions sans imbriquer des fonctions IF et renvoie le résultat correspondant à la première condition vraie.

• Les fonctions logiques se distinguent ainsi :
  • AND exige que toutes les conditions soient vraies
  • OR exige qu’au moins une condition soit vraie
  • NOT inverse une condition

📌 COUNTIF, SUMIF et AVERAGEIF effectuent respectivement un comptage, une somme et une moyenne selon un critère, tandis que COUNTIFS, SUMIFS et AVERAGEIFS appliquent plusieurs critères.

Compléments

• Les fonctions d’information comprennent :

  • ISNUMBER
  • ISTEXT
  • ISBLANK
  • ISERROR

• La mise en forme conditionnelle peut utiliser : des règles de mise en évidence selon un seuil, des règles de valeurs hautes ou basses, des barres de données, des échelles de couleurs, des jeux d’icônes, des règles personnalisées fondées sur des formules

📖 4. Recherches et tableaux structurés

🔑 Notions clés & Définitions

• XLOOKUP : Fonction de recherche flexible qui peut chercher dans toutes les directions, traiter directement les valeurs non trouvées et ne nécessite pas de numéro de colonne.

★ À maîtriser

• VLOOKUP recherche une valeur dans la première colonne d’un tableau et renvoie une valeur d’une autre colonne, tandis que HLOOKUP recherche dans la première ligne et renvoie une valeur située en dessous.

• Avec VLOOKUP, une recherche exacte s’obtient avec FALSE ou 0 en dernier argument ; une recherche approximative s’obtient avec TRUE ou en omettant cet argument et exige des données triées.

• Dans la combinaison INDEX/MATCH, MATCH trouve la position d’une valeur et INDEX renvoie la valeur située à cette position.

Compléments

• 🔄 Pour créer un tableau Excel :

  1. sélectionner un jeu de données
  2. utiliser Ctrl + T ou Insertion → Tableau
  3. vérifier que les en-têtes sont inclus

• Les tableaux Excel utilisent des références structurées, par exemple Table[Sales] à la place de A2:A100, et s’étendent automatiquement lorsque de nouvelles données sont ajoutées.

💡 Astuce mémo

VLOOKUP cherche dans la première colonne ; HLOOKUP dans la première ligne.

📖 5. Création et structure des tableaux croisés

📝 Points essentiels

• 🔄 Pour créer un tableau croisé dynamique : sélectionner le jeu de données, de préférence sous forme de tableau, choisir Insertion → PivotTable, choisir une nouvelle feuille ou une feuille existante pour son emplacement

📌 Un jeu de données propre et structuré, sans lignes vides et avec des en-têtes clairs, est la bonne pratique pour créer un tableau croisé dynamique.

• Les quatre zones sont :

  • Lignes, pour afficher des catégories verticalement
  • Colonnes, pour afficher des catégories horizontalement
  • Valeurs, pour calculer les données
  • Filtres, pour filtrer globalement le tableau

• Un tableau croisé dynamique réorganise les données dynamiquement sans modifier le jeu de données d’origine.

📖 6. Analyse et exploration des tableaux croisés

★ À maîtriser

• Les calculs courants comprennent :

  • SUM pour le total
  • COUNT pour le nombre d’enregistrements
  • AVERAGE pour la moyenne
  • MIN pour la plus petite valeur
  • MAX pour la plus grande valeur

• Les outils de filtrage comprennent : les menus déroulants des lignes ou colonnes pour sélectionner des éléments, les segments interactifs, qui peuvent contrôler plusieurs tableaux croisés, les chronologies, qui filtrent les dates par années, trimestres et mois

Compléments

• Pour changer le calcul d’un champ de valeurs, on ouvre Value Field Settings depuis le champ de valeurs et on choisit le calcul souhaité.

• Le tri d’un tableau croisé dynamique peut être croissant ou décroissant, ou fondé sur des valeurs, par exemple pour afficher les ventes les plus élevées.

• Le regroupement peut réunir :

  • les dates par mois ou par année
  • les valeurs numériques par intervalles
  • les éléments en catégories personnalisées

📖 7. Visualisation et actualisation des analyses

★ À maîtriser

• Un graphique croisé dynamique créé à partir d’un tableau croisé est lié à celui-ci et se met à jour avec ses filtres.

• Les tableaux croisés dynamiques ne se mettent pas à jour automatiquement lorsque les données sources changent ; on peut les actualiser par clic droit → Actualiser ou par Données → Actualiser tout.

Compléments

• Un champ calculé dans un tableau croisé dynamique peut définir Profit = Sales − Costs sans modifier les données sources.

• Les bonnes pratiques consistent à : convertir les données en tableau au préalable, garder des données propres et structurées, utiliser des noms de champs explicites, éviter la mise en forme manuelle susceptible d’être réinitialisée, actualiser les données régulièrement

💡 Astuce mémo

Les données sources changent → actualiser le tableau croisé.