La création d'un tableau simple

Vous souhaitez obtenir ce petit tableau récapitulant et cumulant les recettes des salles de spectacle de votre commune sur les six premiers mois de l'année ?

Voici les différentes étapes pour y parvenir

Commencez toujours par travailler le contenu du tableau avant de vous attaquer à sa présentation.

1)     Travailler le contenu du tableau

A)   Saisir les données non calculables

• Pour écrire dans une cellule, cliquez dans la cellule puis tapez le texte (nombre ou texte) et validez en déplaçant le curseur dans une autre cellule.
• Ne saisissez pas le symbole monétaire €.  Vous l'ajouterez automatiquement lors de l'étape de mise en forme.
• Pour modifier le contenu d'une cellule, double cliquez dessus.
• Pour effacer la totalité du contenu d'une cellule, sélectionnez-la puis appuyez sur la touche SUPPR de votre clavier.

Vous devez obtenir cette première ébauche.

B)    Effectuer les calculs

1) Total par salle

• Cliquez dans la cellule H3 qui doit contenir le cumul des recettes mensuelles pour le ciné Melies

• Appuyez sur le bouton Σ (Somme automatique)

• Excel va pré-sélectionner les cellules à cumuler. Vérifiez qu'il s'agit des bonnes cellules.

• Validez en appuyant sur la touche Entrée du clavier.
• Recopiez le calcul. 

En effet, la méthode de calcul pour le cumul des recettes mensuelles des 2 autres salles est identique. Pointez la souris sur le petit carré situé à l'extrémité inférieure droite de la cellule H3 puis dès qu'une petite croix apparaît, faites glisser la souris sur les cellules H4 et H5.

2) Total par mois

• Cliquez dans la cellule B6 qui doit contenir le cumul des recettes des trois salles pour le mois de janvier
• Appuyez sur le bouton Somme automatique Σ
• Excel va pré-sélectionner les cellules à cumuler.
• Validez en appuyant sur la touche Entrée du clavier.
• Recopiez le calcul sur les cellules C6 à G6

3) % ciné
Que représentent les recettes du ciné-Mélies par rapport au total mensuel des recettes ? Pour cela, il faut diviser le chiffre du cine melies de janvier (B3) par rapport au total général de janvier (B6)

• Cliquez dans la cellule B7
• Tapez = puis saisissez la formule de calcul B3/B6
• Validez
• Recopiez la formule de calcul sur les cellules C7 à G7

C)   Enregistrer le classeur

Cliquez sur FICHIER ENREGISTRER
Donnez un nom significatif à votre tableau afin de pouvoir le retrouver plus facilement ultérieurement.

2) Travailler la présentation du tableau
    A)   La mise en forme du tableau

Avant d'effectuer un choix de mise en forme, commencez toujours par sélectionner les cellules dont vous souhaitez modifier la présentation. Sinon, seule la cellule en cours d’utilisation sera concernée.
Le ruban « Accueil » permet d’appliquer les choix les plus courants de mise en forme.

Pour ajouter le symbole €

• Sélectionnez les cellules concernées

• Appuyez sur le bouton Format Nombre Comptabilité

Excel a automatiquement ajouté, sur toutes les cellules sélectionnées, le symbole € et deux décimales après la virgule

• Appuyez sur le bouton "Réduire les décimales" pour enlever les deux décimales non significatives

B)    La mise en page

Cliquez sur FICHIER/IMPRIMER

L’écran se divise en deux parties :

A gauche les options de mise en page et d’impression et à droite l’aperçu de votre feuille

Le bouton Orientation pour basculer portrait / paysage

          

3)     Imprimer le classeur

4)     Fermer le classeur

Utiliser des noms

Un nom défini représente une cellule ou une plage de cellules.

Attribuer un nom à une plage de cellules permet de se servir de ce nom, plutôt que des adresses de cellules, dans une formule de calcul afin de rendre celle ci plus compréhensible.
Ainsi, la formule =somme(B2:B45) est moins explicite que
=somme(Frais_deplacement_mars) 

Dans celle ci, la zone B2:B45 est représentée par le nom Frais_deplacement_mars

Ce nom peut aussi servir pour atteindre directement une zone particulière d'une feuille

Pour attribuer un nom à une cellule ou une plage de cellule

1) Sélectionner la cellule ou la plage de cellules à nommer.
2) Cliquer sur la zone "nom" située à l'extrémité gauche de la barre de formules
3) Taper le nom (255 caractères max, pas d'espace)
4) Ne pas oublier de valider en appuyant sur la touche "Entrée"

Par défaut, ce nom sera reconnu dans l’ensemble des feuilles de calcul du classeur

Autre solution : Utiliser la rubrique Nom située dans l’onglet formule

1)   Sélectionner la cellule ou la plage de cellules à nommer.

2)   Cliquer sur l’onglet Formules dans le ruban puis Définir un nom (Define Name)

3)   Dans Le dialogue qui s’ouvre, taper le nom que vous souhaitez attribuer à la cellule.

4)   Indiquer si le nom est valable pour tout le classeur ou juste pour la feuille active

5)  Valider par "Entrée

Règles de nommage

Un nom ne peut excéder 255 caractères, ni contenir d'espace. Seuls, les lettres, chiffres, points et le soulignement _ sont acceptés.
Un nom de cellule ne peut non plus être identique à une adresse de cellules.

De plus, le premier caractère d'un nom ne peut être qu'une lettre ou le caractère _ 

Définir des noms à partir d’une sélection de plages de cellules

Les tableaux comportent déjà des en-têtes de colonne explicites. Pourquoi ne pas s’en servir pour nommer les plages du tableau ?

1) Sélectionner la plage à laquelle attribuer un nom, y compris les étiquettes de colonnes et/ou de lignes.

2) Dans le ruban, sélectionner l'onglet Formules puis "Depuis la sélection" (Create from selection)

3) Dans la boîte de dialogue, indiquer si Excel doit utiliser les libellés de la  première ligne (Produit, production,...chiffre d'affaire TTC) et/ou de la première colonne (Rosiers, oliviers...Bonzai) pour nommer les zones de cellules.

4) Dans notre exemple, nous allons cocher uniquement ligne du haut car nommer les lignes ne va pas nous servir.

Désormais, chaque colonne de notre tableau porte un nom. Par exemple, vente se réfère aux cellules C5:C9. Les noms font uniquement référence aux cellules contenant des valeurs et excluent les étiquettes  existantes.

Cela va rendre les formules de calcul plus claires.

La formule =Production-Vente est plus rapide à comprendre que =B5-C5

Utiliser un nom dans un calcul

Pour utiliser un nom lors de la saisie d’une formule de calcul, il suffit de taper le nom ou de le sélectionner à partir d'une liste.

• Taper la première lettre du nom et sélectionner le nom dans la liste qui s'affiche.
• Ou, dans l'onglet Formules, cliquer sur Utiliser dans la formule (Use in formula) et sélectionner le nom que vous souhaitez utiliser.
• Ou lors de la saisie de la formule, appuyer sur la touche de fonction F3 qui permet d’appeler la liste des noms lors de la saisie d’une formule.

Si le nom est mal orthographié ou a été supprimé, le message d'erreur #NOM s'affiche.
Le gestionnaire de noms

 
Le « Gestionnaire de noms » (Name manager) situé dans le groupe « noms définis » de l’onglet « Formules » permet de modifier ou de supprimer un nom défini.

La protection des données

Excel vous permet de protéger vos documents afin d'empêcher que des données importantes soient accidentellement ou intentionnellement modifiées, déplacées ou supprimées d’une feuille de calcul ou d’un classeur. 

Excel offre deux sortes de protection. La protection des données d'une feuille ou la protection de la structure d'un classeur.

---

Protéger une feuille

Par défaut, lorsque vous protégez une feuille de calcul, toutes les cellules de cette feuille sont verrouillées et il est impossible de les modifier ou d'en changer la présentation.

Pour protéger la totalité des cellules d'une feuille, Cliquez sur l'onglet "Révision" et choisissez "Protéger la feuille" dans le groupe "Modifications"

Une boîte de dialogue s'affiche pour affiner la protection

Profitez en pour mettre un mot de passe afin d’empêcher toute tentative de déprotection de votre feuille.

Si vous ne souhaitez pas bloquer la totalité des cellules de la feuille mais permettre la modification de certaines cellules, commencez par déverrouiller les cellules concernées puis dans un second temps, activez la protection de la feuille

Pour déverrouiller les cellules ou les plages de cellules que d’autres utilisateurs pourront modifier

1. Sélectionnez chaque cellule ou plage que vous souhaitez déverrouiller
2. Cliquez sur l'onglet Accueil, choisissez "Format" dans le groupe Cellules
   
3. Sous l’onglet Protection, désactivez la case à cocher Verrouillé , puis cliquez sur OK.

---

Protéger le classeur

La protection du classeur permet de : 

•  Protéger les fenêtres d'un classeur pour qu'elles ne puissent pas être déplacées ou redimensionnées.

•  Verrouiller la structure d'un classeur ce qui empêche les utilisateurs d’ajouter,de déplacer, de supprimer des feuilles de calcul ou d’afficher des feuilles de calcul masquées. 

Pour protéger la structure et les fenêtres d'un classeur, Cliquez sur l'onglet "Révision" et choisissez "Protéger le classeur" dans le groupe "Modifications"

• Pour protéger la structure d’un classeur, cocher Structure.
• Pour que les fenêtres du classeur conservent la même taille et la même position à chaque ouverture du classeur, cocher fenêtres 

Le mot de passe est facultatif. Mais il évite que tous les utilisateurs puissent déprotéger le classeur et modifier les éléments protégés.

---

Attention : La protection de feuille ou la protection du classeur n'empêche pas un utilisateur d'accéder à votre classeur.

Pour interdire à un utilisateur d’entrer dans un classeur, il faut utiliser l’option d’enregistrement avec mot de passe.

Dans le menu Fichier, choisissez « Informations » puis   cliquez sur le bouton « Protéger le classeur » situé dans l'onglet Autorisations

Sélectionnez alors « Chiffrer avec mot de passe » et tapez deux fois votre mot de passe.

Le mode Plan

Le mode plan permet de regrouper et synthétiser les données d'un tableau excel en masquant les lignes de détails.
Le mode plan contribue à améliorer la lisibilité des grands tableaux.

A vous de jouer !

A partir de la feuille suivi des formations, regrouper les dépenses afin de n'afficher que le total réalisé et le budget.

Puis regrouper les données par région / département

Les graphiques à bulles

Les graphiques standard représentent les données à l'aide de deux dimensions : les abscisses et les ordonnées. Les graphiques à bulles rajoutent une  troisième dimension d'analyse avec la taille des bulles. 

Les graphiques à bulles permettent de remplacer les points de données d'un graphique par des bulles dont la taille est proportionnelle à la valeur du point.

Un graphique à bulles met en relation 3 séries de données.

• Une série numérique pour l'axe des X
• Une série numérique pour l'axe des Y
• Une série numérique pour la taille des bulles

Chaque point de données est représenté par un trio de valeur (x,y,z).

Le tableau ci-dessous indique le nombre de touristes, la durée moyenne et le panier moyen dépensé lors de leur séjour dans les 5 villes indiquées.

Un graphique de comparaison tel que des histogrammes ne pourrait représenter que deux de ces trois données. 

La série depenses moyenne a été affecté à un axe secondaire afin d'en faciliter l'affichage. Mais la série nuitées moyenne reste "invisible" car ses valeurs sont trop peu élevées par rapport aux depenses moyenne.

Essayons le graphique à bullle

Sélectionnez les cellules B2:B7. Ne prenez pas les noms de ville.
puis dans l'onglet INSERTION, cliquer sur GRAPHIQUE RECOMMANDES puis sur l'onglet tous les graphiques qui s'affiche.
Les graphiques à bulles sont une sous catégorie des nuages de points. 

Nous obtenons bien nos 3 dimensions :

• les nuitées (axe des X)
• le nombre de visiteurs (axe des Y)
• le montant moyen dépensé représenté par la taille des bulles

Par contre, nous ne savons pas à quoi correspondent les différentes bulles.

Ajouter les étiquettes 

1. Sélectionner le graphique et cliquer sur le +coté droit du graphique. 
2. Cocher Étiquettes de données puis Autres options
3. Cocher valeurs à partir des cellules
4. Sélectionner la plage de cellules contenant les valeurs à utiliser comme étiquettes

Mettre en forme l'étiquette

La mise en forme conditionnelle

Les principes

La mise en forme conditionnelle facilite la mise en évidence de cellule qui présentent un intérêt ou qui se trouvent dans ou en dehors des normes définies. 

Elle modifie l'aspect d'une plage de cellules en fonction d'une condition que vous indiquez.

Si la condition est vraie, la plage de cellules est mise en forme en fonction de cette condition, si la condition est fausse, la plage de cellules n'est pas mise en forme.

Lorsque la valeur de la cellule change et ne correspond plus aux conditions indiquées, la mise en forme conditionnelle ne s'applique plus.

La mise en forme conditionnelle EXCEL 2003

Elle permet de modifier :

• Le style (gras, italique, barré…) et la couleur du texte
• Le style et la couleur de l'encadrement
• La couleur de fond de la cellule

Excel 2003 n'autorise que 3 mises en forme conditionnelles possibles pour une cellule.

La mise en forme conditionnelle EXCEL 2007

Elle permet de modifier :

• Le style (gras, italique, barré…) et la couleur du texte
• Le style et la couleur de l'encadrement
• La couleur de fond de la cellule
• Le format des nombres.

Excel 2007 fournit les nouveaux types de mise en forme conditionnelle suivants :

• Les échelles de couleur
• Les barres de données
• Les Jeux d'icônes
• Les valeurs classées en bas ou en haut
• Les valeurs au-dessus ou en-dessous des valeurs moyennes
• Les valeurs uniques ou dupliquées

Ces types de mise en forme conditionnelle ne sont pas reconnus par les versions précédentes d’Excel si vous enregistrez votre classeur Excel 2007 dans un format antérieur.

EXCEL 2007 autorise jusqu'à 64 mises en forme conditionnelles possibles pour une cellule

Les différents types de règles.

Vous pouvez créer directement vos règles sans passer par les assistants.

Commencez toujours par sélectionner la plage de cellules où la mise en forme conditionnelle doit s’appliquer.

Puis sélectionnez "Nouvelle règle" à partir de l'Onglet Accueil, Groupe Style, bouton Mise en forme conditionnelle

Une boîte de dialogue vous permet d’effectuer vos choix parmi six types de mise en forme conditionnelle

• Mettre en forme toutes les cellules d’après leur valeur

A utiliser pour les nuances de couleur, les barres de données ou les jeux d’icône.

• Appliquer une mise en forme uniquement aux cellules qui contiennent

Indiquez les critères de sélection (valeur spécifique, valeur comprise entre, cellules vides…)

• Appliquer une mise en forme uniquement aux valeurs rangées parmi les premières ou les dernières valeurs

Indiquez les X valeurs souhaitées en valeur ou en pourcentage

• Appliquer une mise en forme uniquement aux valeurs au-dessus ou en dessous de la moyenne

Indiquez la mise en forme souhaitée

• Appliquer une mise en forme uniquement aux valeurs uniques ou aux doublons

Sélectionnez dans la liste le choix unique ou le choix en double

• Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué

A utiliser lorsque la cellule à mettre en forme n’est pas la cellule sur laquelle portera le critère.

Exemple : Mettre en rouge le nom des collaborateurs qui n’ont pas effectué 4h d’accueil.

Tapez = puis saisissez la formule de calcul à vérifier.

Cette formule doit pouvoir renvoyer la valeur vrai ou la valeur faux.

(Exemple : = A1<4)

 

Si la condition est vérifiée, la mise en forme définie s’appliquera

Les graphiques sparkline

Apparus avec la version 2010 d'Excel, les sparkline sont des petits graphiques insérés directement dans une cellule de calcul.
Ils permettent de visualiser rapidement une tendance ou de repérer les valeurs max - min d'une série de valeurs.

Prenons un exemple :

Voici les résultats semestriels de trois agences. 
La colonne H doit permettre de visualiser en un seul coup d'oeil l'évolution des ventes.
Un graphique sparkline inséré dans les cellules H2:H4 va répondre à ce besoin.

Création du sparkline

1. Placez le curseur dans la cellule H2 puis dans l'onglet INSERTION sélectionnez le groupe Graphiques sparkline. 
2. Choisissez l'un des 3 types proposés : En courbes, Histogramme, ou Positif/négatif.
   
   
     
3. Dans la boîte Création, indiquez la plage des cellules contenant les données sur lesquelles vous souhaitez baser le sparkline.
   (Ici les cellules B2:G2 contenant les ventes de l'agence de Lyon)

Après validation, recopiez le contenu de la cellule H2 sur les cellules H2:H4.
Un sparkline s'affichera dans chacune des cellules.

Désormais tout changement de valeur dans une des cellules sources du sparkline se répercutera sur le graphique. 

Modifier la présentation d'un sparkline.

Un onglet dédié aux manipulations sur les graphiques sparkline s'affiche lorsque le curseur se trouve sur une cellule contenant un graphique sparkline.
Vous pouvez :

• Modifier le type de sparkline

• Afficher un point (=marqueur) représentant chaque valeur de cellule source

• Mettre en évidence la valeur la plus haute ou la plus basse

• Modifier la couleur du graphique ou des marques
• Effacer un ou la totalité des sparkline

Il est préférable de positionner le graphique sparkline à coté des cellules contenant les données à représenter et ce afin de faciliter la lisibilité des sparklines.