2013 SupportDeCours - €¦ · Cliquer sur la flèche à droite de la zone Nom permettant d'activer la liste déroulante Cliquer sur le nom correspondant à la plage de cellules à

AIDE-MEMOIRE

Excel 2013

Avancé

N V A N A S S C H E

H T T P : / / W W W . N A T H A L I E V A N A S S C H E . B E

EXCEL 2013 [Avancé] – Aide-mémoire ! Page 2

Chapitre 01 : Optimiser la gestion des feuilles et des classeurs

N. VANASSCHE http://www.nathalievanassche.be

Feuille de route

CHAPITRE 01 : OPTIMISER LA GESTION DES FEUILLES ET DES CLASSEURS

Application 1. Utiliser l’adressage absolu et mixte _________________________________________________ 4

Application 2. Utiliser des noms dans un classeur _________________________________________________ 5

Application 3. Calculer sur plusieurs feuilles de calcul ______________________________________________ 7

Application 4. Travailler avec plusieurs classeurs ________________________________________________ 12

Application 5. Utiliser des fonctions logiques ___________________________________________________ 13

Application 6. Imbriquer plusieurs fonctions ____________________________________________________ 16

Application 7. Realiser un audit de feuille de calcul _______________________________________________ 17

CHAPITRE 02 : MAITRISER L'EXPLOITATION DES TABLEAUX DE TYPE BASE DE DONNEES

Application 8. La validation de donnees _______________________________________________________ 20

Application 9. Filtrer des bases de donnees ____________________________________________________ 21

Application 10. Les fonctions de base de donnees ________________________________________________ 26

Application 11. Les fonctions de recherche _____________________________________________________ 27

Application 12. Le formatage conditionnel _____________________________________________________ 28

CHAPITRE 03 : MAITRISER LES OUTILS DE GESTION ET DE SYNTHESE

Application 13. Analyser des hypotheses ______________________________________________________ 32

Application 14. Concevoir un tableau/graph croise dynamique ______________________________________ 39

CHAPITRE 04 : L’AUTOMATISATION DES TACHES

Application 15. Créer et utiliser une macro-commande ___________________________________________ 45




Chapitre 01

Optimiser la gestion

des feuilles et des classeurs




APPLICATION 1. Utiliser l’adressage absolu et mixte

Qu’est-ce que l’adressage absolu ? Une adresse absolue est une référence permanente, c’est à dire qu’elle ne s’adapte pas lors d’une copie. Pour créer une adresse absolue, il suffit d’ajouter un signe dollar devant chaque élément d’une adresse. EX : A1 devient $A$1 Pour transformer une adresse relative en adresse absolue, il suffit de cliquer 1X sur l’adresse normale dans la barre de formule et d’appuyer sur F4. L’adressage mixte ne bloque la cellule que dans un sens … $A1 La cellule sera bloquée dans le sens des colonnes, elle ne pourra donc pas s’étirer de la gauche vers la droite mais bien du haut vers le bas. A$1 La cellule sera bloquée dans le sens des lignes, elle ne pourra donc pas s’étirer du haut vers le bas mais bien de la gauche vers la droite. Ainsi… A/

B/

C/

D/

= $A$3 * B2 ou

= $A3 * B2

= B2 + $C$1 ou

= B2 + C$1

= $A3 – ($A3 * B$2)

= C$1 + $B2




APPLICATION 2. Utiliser des noms dans un classeur

Qu'est-ce qu'un nom ? � Un nom est un identifiant d'un élément dans un classeur,

Ex : une cellule, une zone de données, un graphique, une forme ou tout autre objet. � Si vous donnez un nom à une plage de données, vous pouvez l'utiliser dans les formules. Les avantages des noms : 1° Les noms rendent les formules plus compréhensibles et plus faciles à utiliser 2° En entrant une formule manuellement, on se rappelle plus facilement d'un nom que d'une adresse, de plus il est facile de

coller un nom grâce à la touche F3. 3° On peut accéder rapidement aux zones nommées grâce à la zone nom 4° Quand on sélectionne une cellule ou une plage de cellule nommée contenant un nom, celui-ci apparaît dans la zone nom.

Exemple : B2 : E13 se nomme [Résultats] =SOMME(Résultats)

=MOYENNE(Résultats)

=MAX(Résultats)

=MIN(Résultats)

Pour coller un nom dans une formule : F3

1. Comment insérer un nom ? � Sélectionner les cellules (contiguës ou non) � Tapez le nom et validez par Entrée.

- Cliquer dans la zone NOM de la barre de formule Sans espace ou avec un underscore ou un point (pas d'autres symboles)

- Les combinaisons de lettres ou de chiffres sont autorisées mais doivent commencer par une lettre - Jamais de référence de cellule Ex : B4 - Pas plus de 255 caractères - La casse n'est pas prise en compte

Ou � Sélectionner la plage de cellules � Dans le ruban Formules, dans la section Noms définis, cliquer sur Définir un nom � Tapez le nom choisi � Ajouter / Ok




2. Comment atteindre un nom ? � Cliquer sur la flèche à droite de la zone Nom permettant d'activer la liste déroulante � Cliquer sur le nom correspondant à la plage de cellules à atteindre 3. Comment insérer un nom dans une formule ? � Construire la formule comme d'habitude en tapant le nom de la plage de cellules ou � Construire la formule comme d'habitude en tapant directement ou en collant F3 le nom de la plage de cellules 4. Comment créer des noms automatiquement à partir de cellules voisines ? � Sélectionner toutes les cellules avec leurs étiquettes � Dans le ruban Formules, dans la section Noms définis, cliquer sur Créer à partir de la sélection � Dans la boîte de dialogue Créer des noms cochez d’où Excel doit les extraire 5. Comment effacer des noms ? � Dans le ruban Formules, dans la section Noms définis, cliquer sur Gestionnaire de noms � Cliquer sur le nom / Supprimer

Attention : dans le cas où ce nom est utilisé dans une formule, celle-ci sera rendue invalide. 6. Comment visualiser les zones nommées ? � Mettre le zoom à 39% � Une bordure s'affiche autour des zones nommées, leur nom est écrit en bleu 7. Déplacer des zones nommées � Couper / Coller une zone nommée engendre pour Excel une modification de référence (il adapte)

Attention : Pas si vous coupez seulement une partie de la zone, si vous copiez, vous n'obtiendrez pas 2X le même nom ! 8. Comment obtenir une liste des noms d'un classeur avec leur adresse ? � Cliquer dans une zone vide de la feuille � F3 puis Coller une liste

La liste qu'Excel colle dans votre feuille occupe 2 colonnes, la 1ère contient les noms et la seconde contient l'adresse des plages de données.

9. Comment faire référence à des noms d'un autre classeur ? � Attention, celui-ci doit être ouvert. � Exemple : =Moyenne (Budget.xls! Ventesmensuelles)

La formule suivante utilise une plage nommées ventes_mensuelles définie dans un classeur Budget.xls.




APPLICATION 3. Calculer sur plusieurs feuilles de calcul

Objectifs : Consolider plusieurs feuilles de calcul d’un classeur

Utiliser le mode plan

Travailler en mode « Groupe de travail »

Réaliser une mise en forme personnalisée de nombres

Construire rapidement un graphique 3D

1. Entrer une formule à partir de plusieurs feuilles de calcul � Cliquez dans la cellule qui doit accueillir la réponse et tapez = � Cliquez sur l’onglet de la feuille de calcul contenant le 1er chiffre � Cliquez sur la cellule contenant le nombre à utiliser � Tapez l’opérateur (+, -, / ou *) � Cliquez sur l’onglet de la feuille de calcul contenant le 2ème chiffre � Cliquez sur la cellule contenant le nombre à utiliser � Répéter ces opérations pour les autres nombres � Validez par Enter lorsque le calcul est terminé. 2. Consolider plusieurs feuilles de calcul par une fonction L’emplacement des tableaux dans les différentes feuilles de calcul à consolider doit être le même. Les feuilles à consolider doivent se suivre dans le classeur. � Positionnez-vous dans la feuille « Bilan », sur la cellule où la formule de calcul doit être construite (par exemple B5, ventes

totales, 1er trimestre).

� Cliquez sur l’outil somme automatique du ruban Accueil � Parmi les onglets de feuilles, cliquez sur la première feuille à consolider. � Sélectionnez la plage contenant les ventes du 1er trimestre, tous secteurs confondus. � En maintenant la touche majuscule enfoncée, cliquez sur l’onglet de la dernière feuille à consolider (dans notre exemple la

feuille France). La barre de formule affiche =SOMME(Belgique:France!B3:B7)

� Validez en appuyant sur la touche Enter � Le résultat peut être étiré sur les autres trimestres Les formules pour le tableau Détail par trimestre seront donc les suivantes …

1er trimestre

Ventes totales =SOMME(Belgique:France!B3:B7)

Ventes moyennes =MOYENNE(Belgique:France!B3:B7)

Ventes maximales =MAX(Belgique:France!B3:B7)

Ventes minimales =MIN(Belgique:France!B3:B7) 3. Consolider plusieurs feuilles de calcul par la fonction Consolider

Création du tableau Bilan Général – Total des ventes Dans cette méthode, l’emplacement des tableaux dans les différentes feuilles de calcul à consolider ne doit pas être obligatoirement le même. De plus, l’ordre des feuilles à consolider dans le classeur n’a pas d’importance. Le tableau de synthèse sera construit automatiquement, il n’est donc pas nécessaire d’en élaborer la mise en forme à l’avance (en-têtes de lignes et de colonnes) comme dans la méthode précédente. � Positionnez-vous dans la feuille "Bilan". � Sélectionnez la cellule à partir de laquelle le tableau sera construit – A12 dans le premier tableau. � Dans le ruban Données, choisissez Consolider - la boîte suivante s'ouvre :




� Par défaut c'est la fonction "SOMME" qui est sélectionnée dans la zone "Fonction", il en existe d’autres (moyenne, max,

min, écart-type, ...) � Les étiquettes « Ligne du haut » et « Colonne de gauche » si elles sont activées, permettent au tableau d’être construit

avec le nom des colonnes (ici les trimestres) et le nom des lignes (ici les secteurs). � Cliquez sur l’onglet de la feuille Belgique pour la rendre active. � Sélectionnez à présent la plage de cellule A2:E7 comprenant les données et les en-têtes de la feuille « Belgique » � Cliquer sur le bouton Ajouter pour valider. La zone choisie devient une source d’information de la feuille « Bilan ». Elle

s’ajoute dans le cadre Toutes les Références � Répéter l'opération pour les feuilles Italie, Espagne et France � Avant de quitter la boîte de dialogue, cochez l'option « Lier aux données source » pour créer un lien dynamique entre les

données sources et la feuille de bilan. Remarque : la création d’un lien dynamique alourdi votre fichier, il est parfois préférable de recommencer la consolidation pour mettre à jour les tableaux de grande taille.

� Cocher les options « Lignes du Haut » et « Colonne de gauche » pour reproduire la structure exacte du tableau � Répétez les mêmes manipulations pour le dernier tableau - Bilan Général – Moyenne des ventes � S’il s’agit d’un lien dynamique, la feuille « Bilan » apparaît alors en mode plan

Représente les niveaux, le 2 développe le détail des données, le 1 ne fait apparaître que les totaux

Chaque + permet de détailler les données de la ligne de totaux correspondante, Le + fait alors place à un – de manière à réduire ces données après consultation

4. Passer en mode « Groupe de travail » � Sélectionner toutes les feuilles de calcul devant faire partie du groupe (à l’aide de la touche CTRL ou SHIFT)




� Lorsque plusieurs feuilles sont sélectionnées, la barre de titre de votre programme affiche Nom du classeur [Groupe de travail]

� Tout ce que vous tapez dans la feuille active se répercutera dans les autres feuilles du classeur Pour quitter le mode « groupe de travail » � Cliquer sur une feuille du classeur ne faisant pas partie du groupe Ou � Clic droit sur l’ensemble des onglets sélectionnés / Dissocier les feuilles 5. Mettre des nombres en forme � Dans le ruban Accueil, cliquez sur le lanceur de boîte de dialogue Nombre

ou Dans le ruban Accueil, section Cellules, cliquez sur Format, puis sur Format de cellules

� Dans l’onglet Nombre, choisir la catégorie personnalisée. � Dans la zone type, saisir #.##0" Pièces" � Cette technique vous permet de mettre derrières des nombres n’importe laquelle des chaînes de caractères ! 6. Créer un graphique 6.1. Insérer un graphique recommandé Pour créer dans Excel un graphique de base que vous pouvez modifier et mettre en forme par la suite …

� La première étape consiste à entrer les données du graphique de façon appropriée dans une feuille de calcul.

� Sélectionnez ensuite les données à représenter

� Pour laisser Excel faire le travail (EXCEL 2013), commencez avec un graphique Excel recommandé

dans ruban Insertion 6.2. Insérer un graphique précis � Si vous cherchez un graphique précis, choisissez le type de graphique que vous voulez utiliser dans l’onglet Insertion,

groupe Graphiques. � Cliquez sur un type de graphique, puis sur le sous-type de graphique que vous souhaitez utiliser. � Pour afficher tous les types de graphiques disponibles, cliquez sur la flèche en bas à droite de la boîte :

Le graphique est placé sur la feuille de calcul en tant que graphique incorporé, POUR LE PLACER DANS UNE NOUVELLE FEUILLE � Cliquez sur le graphique incorporé dans la feuille de graphique pour le sélectionner et afficher les outils graphiques. � Dans l'onglet Création, groupe Emplacement, cliquez sur Déplacer le graphique. � Sous Emplacement, cliquez sur Nouvelle feuille. 6.3. Insérer un graphique rapidement Pour CREER RAPIDEMENT UN GRAPHIQUE se basant sur le type de graphique par défaut : � sélectionnez les données à présenter, puis appuyez sur Alt+F1 pour insérer le graphique en tant que graphique incorporé � ou F11 pour afficher le graphique dans une nouvelle feuille de calcul.




Pour modifier le type de graphique par défaut : � Après avoir sélectionné le type de graphique et son sous-type dans la boîte de dialogue Insérer un graphique, cliquez sur

Définir comme graphique par défaut. 6.4. Utiliser l’analyse instantanée des données (Excel 2013) � Sélectionnez les cellules contenant les données que vous voulez analyser. � Cliquez sur le bouton Loupe d’analyse rapide dans le coin inférieur droit des données sélectionnées. � Dans la galerie Analyse rapide, sélectionnez l’onglet souhaité. Sélectionnez Graphiques pour voir vos données sous forme

de graphique.

Remarque : Les "sparklines" sont de petits graphiques tracés par Excel dans une cellule, sans passer par l’interface graphiques. 6.2. Modifier un graphique existant 6.2.1. Ajustements rapides (Excel 2013)

Ajustez rapidement votre graphique à l’aide des trois boutons à droite de votre graphique, ils permettent : � d’ajouter des éléments tels que des titres et des étiquettes de données, � de modifier l’apparence et la disposition de votre graphique, � de définir les données que vous voulez afficher

6.2.2. Ajustements plus précis (Excel 2013)

Pour accéder à des fonctionnalités supplémentaires de conception et de mise en forme, cliquez à un endroit quelconque dans le graphique pour ajouter les outils de graphique dans le ruban, puis cliquez sur les options que vous voulez dans les onglets Création et Format.

� L'onglet Création permet d'afficher les séries de données par lignes ou par colonnes, de modifier les données source du

graphique, de modifier son emplacement, son type, d'enregistrer le graphique comme modèle ou de sélectionner des options de présentation et de mise en forme prédéfinies.

� L'onglet Format, il permet d'ajouter des couleurs de remplissage, de changer le style de trait et d'appliquer des effets spéciaux.




6.2.3. Ajustements avec davantage de contrôle (Excel 2013)

� Sélectionnez l’élément de graphique (les séries de données, les axes ou les titres, …), � Cliquez dessus avec le bouton droit, � Format / [élément du graphique] � Un nouveau volet Format apparaît avec des options adaptées à l’élément de graphique sélectionné.

Cliquez sur les petites icônes en haut du volet pour accéder à d’autres parties du volet offrant d’autres options. Si vous cliquez sur un autre élément de graphique, le volet Office se met automatiquement à jour sur base du nouvel élément de graphique.




APPLICATION 4. Travailler avec plusieurs classeurs

Objectifs : Calculer à partir de plusieurs classeurs

Consolider plusieurs classeurs

1. Entrer une formule à partir de plusieurs classeurs � Ouvrez tous les classeurs dont vous avez besoin � Cliquez dans la cellule qui doit accueillir la réponse et tapez = � Activer le 1er classeur (barre des tâches Windows) et cliquez sur l’onglet de la feuille de calcul, cliquez sur la cellule

contenant le nombre à utiliser � Tapez l’opérateur (+, -, / ou *) � Activer le 2ème classeur, cliquez sur l’onglet de la feuille de calcul, cliquez sur la cellule contenant le nombre à utiliser � Répéter ces opérations pour les autres nombres � Validez par Enter lorsque le calcul est terminé. Exemple : Formule en B4 de la feuille Bilan Somme

=SOMME('[Exportation Ent A.xls]2000'!B4;'[Exportation Ent B.xls]2000'! B4; '[Exportation Ent C.xls]2000'!B4) Remarque Les formules peuvent être étirées comme d’habitude à condition d’enlever les $ qui bloquent les cellules (utilisez F4) 2. Consolider plusieurs classeurs � Ouvrir tous les classeurs � Dans le ruban Données, activez Consolider � Même méthode que pour consolider plusieurs feuilles … 3. Basculer entre plusieurs fenêtres Vous pouvez basculer d’un classeur à l’autre à l’aide de liste disponible dans l’onglet Affichage � Sélectionner l’onglet Affichage / Ouvrez la liste Changer de fenêtre Disposer les fenêtres simultanément sur l’écran � Sélectionner l’onglet Affichage / Ouvrez la liste Fenêtre / Réorganiser tout � Astuce : double-cliquer sur une barre de titre permet d’afficher cette fenêtre là en plein écran




APPLICATION 5. Utiliser des fonctions logiques

Objectifs : Utiliser la fonction logique Si

Protéger une feuille de calcul

Préparer une feuille de calcul pour l’impression

Enregistrer et utiliser une feuille de calcul en tant que modèle

1. Les fonctions logiques

LA FONCTION SI Renvoie une valeur si la valeur de l’argument est vraie et une autre valeur si cette valeur est fausse. Plusieurs fonctions SI peuvent être imbriquées les unes dans les autres. La fonction si est souvent utilisée en accompagnement avec d’autres fonctions

Arguments : Test_logique : Est l’affirmation à laquelle sera répondu VRAI ou FAUX Valeur_si_vrai : Représenta la valeur, la formule ou la fonction renvoyée si le test_logique est vrai Valeur_si_faux : Représenta la valeur, la formule ou la fonction renvoyée si le test_logique est faux Exemple concret : une remise de 5% est accordée si le total est inférieur à 1000, sinon, cette remise est de 10%

2. Protéger une feuille de calcul � Sélectionner les données qui doivent rester accessibles et modifiables � Dans le ruban Accueil, cliquez sur le lanceur de boîte de dialogue Nombre

ou Dans le ruban Accueil, section Cellules, cliquez sur Format, puis sur Format de cellules

� Affichez l’onglet Protection � Désactiver la case Verrouillée




Remarque : Par défaut, toutes les cellules sont verrouillées mais ceci n’a aucun effet sans la protection. � Dans le ruban Révisions, cliquez sur Protéger la feuille

Cochez ce que vous souhaitez autoriser Entrez un mot de passe (facultatif)

Déverrouiller une feuille de calcul � Dans le ruban Révisions, cliquez à nouveau sur Protéger la feuille � Entrer le mot de passe si nécessaire Protéger un classeur � Dans le ruban Révisions, cliquez sur Protéger le classeur, cliquez sur Protéger la structure et les fenêtres

Si vous cochez STRUCTURE: On ne pourra ni insérer ni supprimer les feuilles du classeur Si vous cocher FENETRE: La fenêtre du classeur ne pourra ni être déplacée, ni redimensionnée

3. Insérer un en-tête et un pied de page

� Ruban Mise en page / Lancez la boîte de dialogue Mise en page et cliquez sur l’onglet En-tête et Pied de page � Entrer les données directement dans les zones prévues à cet effet

4. Préparer l’impression d’une feuille de calcul

� Ruban Mise en page / Lancez la boîte de dialogue Mise en page � Dans l’onglet Marge, activer les options centrer horizontalement et verticalement � Dans l’onglet Page, travailler éventuellement l’échelle pour que les données tiennent sur une page A4

5. Enregistrer une feuille de calcul en modèle ...

� Appliquez les mises en forme et les personnalisations que vous souhaitez voir apparaitre dans votre futur modèle. � Lors de l'enregistrement, vous avez le choix entre 2 types d'extension:

*.xltx (Modèle par défaut) ou *.xltm (Modèle autorisant les macros) Choisissez une de ces deux extensions dans la liste déroulante « type de fichier » de votre boîte « enregistrer sous », nommez votre classeur et cliquez sur le bouton "Enregistrer" pour valider.

6. Pour utiliser le modèle

� Cliquez sur Fichier / Nouveau




� Sélectionnez les modèles Personnel pour afficher vos propres modèles � Double cliquez sur le nom pour le lancer.

Un classeur normal, mais disposant de vos personnalisations, s'ouvre et vous pouvez travailler dessus comme pour n'importe quel autre fichier Excel. Vous remarquerez que dans chaque session Excel, le nom du modèle est suivi d'un numéro d'index incrémenté (NomModèle1, NomModèle2 ... etc... ) .

7. Supprimer un modèle

� Accédez au modèle (stocké dans le dossier par défaut d’Excel), il se trouve dans vos documents, dans un sous-dossier Modèles Office personnalisés (C:\Users\NomUtilisateur\Documents\Modèles Office personnalisés\modele.xltx)

� Supprimer le fichier comme d’habitude




APPLICATION 6. Imbriquer plusieurs fonctions

Objectifs : Imbriquer plusieurs fonctions logiques (SI, ET, OU)

La fonction SI peut être imbriquée, cela permet d’augmenter les critères et les résultats ... 1. Imbriquer plusieurs fonctions � Se placer dans la cellule de réception comme d’habitude � Coller la 1ère fonction � Se placer dans l’argument de la fonction qui devra voir apparaître une 2ème fonction � Cliquer sur la liste déroulante à gauche de la barre de formule et cliquer sur la fonction choisie

� Remplir la seconde fonction comme d’habitude

� Pour revenir à la fonction précédente, cliquer sur son nom dans la barre de formules

� Valider par Ok

LES FONCTIONS OU ET ET Ces fonctions s’imbriquent dans la fonction SI de manière à élargir les possibilités de son test_logique … Chaque argument (valeur_logique) contiendra un test logique qui sera relié par ET ou OU selon la fonction choisie




APPLICATION 7. Réaliser un audit de feuille de calcul

� Microsoft Excel fournit des outils qui vous permettent de cerner les problèmes de vos feuilles de calcul. Par exemple, la

valeur affichée dans une cellule peut être le résultat d'une formule ou être utilisée par une formule qui engendre un résultat erroné. Les commandes d'audit affichent sous forme graphique (analysent) les relations entre les cellules et les formules à l'aide de flèches d'audit.

� Lorsque vous auditez une feuille de calcul, vous pouvez analyser les antécédents (les cellules qui fournissent des données à une cellule déterminée) ou les dépendants (les cellules qui dépendent de la valeur d'une cellule donnée).

Exemple d’audit :

Pour afficher une flèche d'audit pour chaque cellule fournissant directement des données à la cellule active :

� Dans le groupe Audit de formules de l'onglet Formules, cliquez sur Repérer les antécédents Pour afficher une flèche d'audit pour chaque cellule dépendant de la cellule active

� Dans le groupe Audit de formules de l'onglet Formules, cliquez sur Repérer les dépendants .

Pour identifier le niveau de cellules suivant dépendant de la cellule active, cliquez à nouveau sur ou Les flèches bleues indiquent les cellules qui ne comportent pas d'erreur et les rouges celles qui provoquent des erreurs. Si la cellule sélectionnée est référencée par une cellule figurant dans une autre feuille de calcul ou un autre classeur, une

flèche noire pointe de la cellule sélectionnée vers une icône de feuille de calcul . L'autre classeur doit être ouvert pour qu'Excel puisse repérer ces dépendances. Supprimer les flèches d’audit D’un niveau à la fois en commençant par le dépendant le plus éloigné de la cellule active :

� Dans le groupe Audit de formules de l'onglet Formules, cliquez sur la flèche à côté de Supprimer les flèches, puis cliquez sur Supprimer les flèches des dépendants ou Supprimer les flèches des antécédents.

� Pour supprimer un niveau supplémentaire de flèches d'audit, cliquez de nouveau sur ce bouton. Pour supprimer toutes les flèches d'audit de la feuille de calcul :

� Dans le groupe Audit de formules de l'onglet Formules, cliquez sur Supprimer les flèches .

� Le bouton Afficher les formules affiche les formules plutôt que leur réponse dans les cellules (elles peuvent ainsi être facilement imprimées).

EXCEL 2013 [Avancé] –Aide-mémoire ! Page 18

Chapitre 02 : Maîtriser l'exploitation des tableaux de type base de données


� Le bouton Vérification des erreurs reprend les options d'aide sous forme d'une boîte de dialogue. � Le bouton Evaluer la formule évalue la formule étape par étape. � La fenêtre espion permet d’inspecter, de contrôler ou de confirmer les calculs de formules et leurs résultats dans des

feuilles de calcul volumineuses, lorsque des cellules ne sont pas visibles dans une feuille de calcul, vous pouvez les visualiser avec leurs formules dans la barre d’outils Fenêtre Espion.




Chapitre 02

Maîtriser l'exploitation des

tableaux de type base de

données




APPLICATION 8. La validation de données

Objectif : Protéger une feuille de calcul lors de la saisie par de tierces personnes

Spécifier des critères de validation � Sélectionnez l’étendue de cellules concernées par la règle de validation � Dans le ruban Données, cliquez sur Validation de données

L’onglet Option Sélectionner une option dans le champ AUTORISER L’option PERSONNALISÉE permet d’utiliser une formule logique vérifiant la validation des données ; si cette formule renvoie VRAI, la donnée associée est considérée comme valide, lorsque la formule renvoie FAUX, le message spécifié dans Alerte d’erreur apparaît. Spécifier les conditions dans le menu DONNÉES L’onglet message de saisie Saisissez le message à afficher lorsque l’utilisateur sélectionne la cellule considérée. Ce message peut prévenir l’utilisateur du type de données attendu. L’onglet Alerte d’erreur Saisissez le message d’erreur à afficher lorsque la donnée saisie ne correspond pas aux critères spécifiés. Pour supprimer toutes les données non valides, il suffit de sélectionner ARRÊT, cette étape est optionnelle.




APPLICATION 9. Filtrer des bases de données

Objectifs : Utiliser les tableaux et leurs fonctionnalités

Utiliser le filtrage élaboré pour interroger une base de données

Un tableau est utilisé pour gérer et analyser vos bases de données. Plus précisément, elle comprend une ligne d’en-tête (une description du contenu des colonnes) suivie d’autres lignes contenant des données (des valeurs ou du texte). Si vous êtes amené à stocker des informations dans une feuille de calcul, les tableaux facilitent ensuite le classement et l'exploitation de ces données. Vous pouvez grâce à cette fonctionnalité: � Filtrer et trier les données des colonnes. � Utiliser les références du tableau dans vos formules. � Ajouter une ligne de totaux. � Différencier facilement les lignes et les colonnes en appliquant un style dans le tableau. � Servir de base pour un tableau croisé dynamique ou un graphique. � Ajouter facilement de nouveaux enregistrements et de nouvelles colonnes (possibilité de redimensionnement

automatique). � Mettre à jour automatiquement les objets liés au tableau (formules, TCD, graphiques ...). � Supprimer les doublons. � Ajouter en une seule fois des formules dans toutes les cellules d'une colonne. L'efficacité du tableau repose sur une base de données correctement structurée : � Chaque cellule de la première ligne contient le nom des champs (en-tête). � Le nom de chaque champ doit être le plus explicite possible. � Les lignes suivantes contiennent les enregistrements. � La base ne doit pas contenir deux noms de champs identiques. � Evitez les cellules vides dans les champs qui stockent des données numériques. � La base ne doit pas contenir de colonnes vides ou de lignes complètement vides � Evitez des types de données différents dans une même colonne (texte, date, numérique). � La base doit stocker uniquement des données brutes (pas de lignes de sous totaux, pas de sous ensembles ...). 1. Pour créer un tableau :

� Sélectionner dans la BDD les cellules concernées par l'analyse, en incluant les en-têtes de colonnes. � Sélectionnez le ruban "Insertion", puis cliquez sur le bouton "Tableau" dans le groupe "Tableaux".

La fenêtre suivante permet de reconfirmer l'emplacement des données source. Si vous décochez l'option "Mon tableau contient des en-têtes", l'application ajoute une première ligne dans le tableau et des noms de champ s'affichent par défaut: Colonne1, Colonne2, Colonne3 ...

� Cliquez sur le bouton OK pour valider, votre tableau est créé. Vous constatez que la table reçoit une mise en forme particulière et des boutons sont accessibles dans l'entête de chaque colonne, afin de filtrer et trier les données.




2. Utiliser les références structurées dans les formules Une référence structurée permet de faire facilement référence aux différentes parties d’un tableau et réduit les risques d'erreurs dans vos feuilles de calcul car vous n'avez plus besoin de modifier les formules lorsque des lignes et des colonnes sont ajoutées ou supprimées dans le tableau. Les références structurées de tableaux sont utilisables dans les formules, les plages nommées, les graphiques et les tableaux croisés dynamiques. Les références structurées vont faire appel aux noms de champs (colonnes), lorsque vous saisissez le nom du tableau dans une formule, suivi d'un crochet ouvert =Tableau1[, Excel propose les arguments (spécificateurs) disponibles, dans la liste de saisie semi automatique:

En plus des noms de colonnes, la liste contient des spécificateurs généraux: [#Tout] fait référence au tableau complet (en-têtes et ligne de totaux compris). [#Données] fait référence aux données, sans les en-têtes ni la ligne de totaux. [#En-tête] fait référence aux données d'en-tête. [#Totaux] fait référence à la ligne de totaux. [#Cette ligne] fait référence à la même ligne que celle qui contient la formule (est souvent utilisée dans les colonnes calculées). Exemples :

Additionne les valeurs de la colonne "Montant": =SOMME(Tableau1[Montant]) Affiche la valeur maxi contenue dans la colonne "NbHeures": =MAX(Tableau1[NbHeures])

Chaque argument doit être placé entre crochets et séparé par des points-virgules. 3. Utiliser les colonnes calculées Lorsque vous entrez une formule dans une des cellules d’une colonne, celle-ci est automatiquement recopiée dans toutes les autres cellules de la colonne (pas besoin d'utiliser les poignées de recopies). De la même manière, si une formule est modifiée dans le tableau, toutes les autres formules sont également mises à jour dans la colonne. Les risques d'erreur sont donc limités.

Une balise active de correction automatique s'affiche ensuite dans la cellule et permet de gérer les nouvelles colonnes calculées: Le bouton "Annuler la colonne calculée" supprime les formules dans la colonne. Le bouton "Arrêter la création automatique de colonnes calculées" empêche tout nouvel ajout automatique de colonne calculée. L'option "Contrôler les options de correction automatique"

CHAMP

ENREGISTREMENT




permet d'afficher l'onglet "Mise en forme automatique au cours de la frappe" de la boîte de dialogue "Correction automatique". Remarque : Une colonne calculée peut également contenir des formules différentes de la formule de colonne initiale : � Saisissez votre formule spécifique dans une cellule de colonne calculée.

Appuyez sur la touche "Entrée" (Toutes les formules de la colonne sont ainsi mises à jour). � Puis cliquez sur le bouton "Annuler" dans la barre d'outils Accès rapide (La modification de toutes les formules est annulée

et la cellule a conservé votre nouvelle formule spécifique). 4. Utiliser les lignes de totaux L'option "Ligne de totaux" est affichée dans la dernière ligne du tableau à la suite des enregistrements et permet d'effectuer des calculs dans chaque colonne (Somme, Moyenne, Min, Max, EcartType ...etc...). L'intérêt de la ligne de totaux réside dans son actualisation automatique lorsque vous ajoutez, masquez ou supprimez des lignes. Le style utilisé permet de mieux visualiser cette dernière ligne particulière. Il existe plusieurs solutions pour insérer une ligne de totaux: � Insérer une formule de somme automatique en bas d'une colonne numérique. � Clic droit dans le tableau/Table/Ligne des totaux. � Sélectionner une cellule du tableau/Onglet "Création" dans le ruban/Cocher "Ligne des totaux" dans le groupe "Options de

style de tableau". Lorsque vous cliquez dans une cellule d'une ligne de totaux, Excel vous propose une liste de fonctions à appliquer sur les données de la colonne: Si vous masquez puis réaffichez la ligne de totaux, les paramètres de calcul sont gardés en mémoire et s'adaptent, même si vous ajoutez ou supprimez des enregistrements entre temps. 5. Le groupe Outils

Le bouton "Synthétiser avec un tableau croisé dynamique" utilise les données du tableau pour créer un TCD. Le bouton "Supprimer les doublons" supprime les enregistrements en double dans la base. La boîte de dialogue permet de spécifier les paramètres

pour la suppression des doublons: Précisez si la première ligne est un en-tête (Celle-ci ne sera pas prise en compte pour la suppression). Par défaut, toutes les colonnes de la plage sont cochées. Cela signifie que la recherche de doublon est effectuée sur des lignes complètes. Vous pouvez décocher certains champs afin d'effectuer la requête sur une ou plusieurs colonnes spécifiques du tableau. Le bouton "Convertir en plage" permet de transformer le tableau en plage de cellules standard : � Toutes les données et la mise en forme sont conservées � Les boutons de filtre et de tri disparaissent � Les formules sont figées � Les références structurées sont remplacées par des références de cellules classiques 6. Le groupe Données de table externe




Une source de données externe peut être: Un mappage XML. Un tableau stocké sur un serveur SharePoint. Le résultat d'une requête depuis une base de données (Access, un autre classeur, un fichier txt, des données Web ... etc ...).

Le bouton "Exporter" exporte les données du tableau vers un serveur SharePoint (pour partager les données et permettre à d'autres utilisateurs de lire ou de modifier les informations). Le bouton "Actualiser" gère la mise à jour du tableau. Une option en bas du menu déroulant permet d'administrer les propriétés de connexion. Le bouton "Ouvrir dans le navigateur" permet d'afficher la liste SharePoint dans une page html. Le bouton "Supprimer la liaison" rompt le lien entre le tableau et la source de données externe. 7. Utiliser le filtrage élaboré – Analyse de 3 cas 1. Préparez les différents filtres ci-après en colonnes E et F : Premier cas

Vendeur Mois

René Janvier

Deuxième cas

Vendeur Mois

René

Janvier

Troisième cas

J’obtiendrai les fiches de René du mois de janvier Opérateur ET

J’obtiendrai les fiches de René ou toutes les fiches du mois de janvier Opérateur OU

Cliquer dans la BDD1 Dans le ruban Données / Groupe Trier et filtrer / Cliquer sur Avancé





quantité

>150

Quelques opérateurs de comparaison

= Egal

> Plus grand

<= Plus petit ou égal

<> Différent

Janvier Champ contenant Janvier et rien d'autre

C* Champ contenant un texte commençant par la lettre C

*i*e Champ contenant un i et un e dans le texte

*Comté* Champ contenant le mot "Comté"

Sm* Champ contenant un texte commençant par Sm

S*S Champ contenant un texte commençant et finissant par S

<>Janvier Champ contenant n'importe quel texte mais pas Janvier

<>C* Champ contenant n'importe quel texte ne commençant pas par la lettre C

>K Champ contenant un texte commençant par une lettre de L à Z

S?S Champ de 3 lettres commençant et finissant par S

=????? Champ contenant exactement 5 lettres

~? Champ contenant un point d'interrogation

="" Tout sauf ce qui est vide

= Champ vide

<> Champ non vide

01/01/2015 Correspond à la date précise

<01/01/2015 Champ strictement antérieurs au 01/01/2015

>=01/01/2015 Champ à partir de la date du 01/01/2015

J’obtiendrai les fiches dont les quantités sont supérieures à 150.





APPLICATION 10. Les fonctions de base de données

Objectifs : Utiliser les fonctions BDSOMME, BDMOYENNE, BDMAX, BDMIN, BDNBVAL, … � Exemple concret :

Vendeur Mois Quantité

René Janv 248

René Fév 131

René Mars 534

Louis Janv 320

Louis Fév 799

Louis Mars 86

Stéphane Janv 284

Stéphane Fév 150

Stéphane Mars 328

Michel Janv 665

Michel Fév 522

Michel Mars 739

1. Construire la zone de critère (ne pas hésiter à dupliquer pour éviter les erreurs)

Mois Quantité

Janvier

2. Cliquer dans la cellule qui devra obtenir le résultat / Collez la fonction BDSOMME

Base de données : Sélectionner la base de donnée contenant les informations (nommer la plage de cellule permet de les gérer plus facilement) Champ : Indiquer le numéro de la colonne de la b.d.d. contenant les champs à additionner ou le nom de la colonne (ex:quantité), ceci étant toujours correct si les colonnes de la bdd se réorganisent (un nom reste bon mais pas un numéro dans ce cas). Toutefois, taper un nom peut être générateur d'erreur. Critère : Etiquette de colonne + la condition Remarque : Si vous remplacez le mois de janvier par le mois de février, ça s’adapte ! Attention : Il est impossible d’étirer ni de sélectionner plusieurs lignes au départ

Vous souhaitez obtenir la somme des ventes des mois de janvier




APPLICATION 11. Les fonctions de recherche

Objectifs : Utiliser la fonction RECHERCHEV

Utiliser la fonction RECHERCHEH

Qu'est-ce qu'une formule de recherche ?

� Le but principal de ces formules est de chercher une valeur précise à l'intérieur d'un tableau et de renvoyer une donnée corrélée à la valeur recherchée.

A/ La fonction Recherche V

� Cette fonction recherche une valeur un tableau et renvoie la valeur contenue dans la même ligne et sur une autre colonne.

� La recherche s'effectue verticalement.

Paramètres de la fonction Recherche V VALEUR_CHERCHEE : Valeur à rechercher dans la 1ère colonne de Table_matrice TABLE_MATRICE : Matrice sur laquelle porter la recherche, il est plus facile de la nommer pour éviter de devoir la bloquer Attention : Cette table_matrice doit être triée par ordre croissant * NO_INDEX_COL : N° de la colonne contenant la valeur à renvoyer VALEUR_PROCHE : Optionnel mais il vaut mettre Faux car alors Excel utilisera une recherche exacte et, si aucune valeur ne correspond, cette fonction renverra #N/A.

Exemple :

* En réalité, si valeur_proche est fixée sur Faux (ou 0), la liste n'a plus besoin d'être triée par ordre croissant car, si la valeur n'est

pas dans la liste, Excel affichera un message d'erreur signalant que le produit n'existe pas.

B/ La fonction Recherche H

� Fonction identique à la fonction Recherche V mais le tableau dans lequel s'effectue la recherche est horizontal.

� Cette fonction recherche donc une valeur dans la première ligne d'un tableau puis affiche le contenu de la cellule qui se situa sur la même colonne que la valeur cherchée.




APPLICATION 12. Le formatage conditionnel

Objectifs : Réaliser une mise en forme conditionnelle basée sur des valeurs

Réaliser une mise en forme conditionnelle basée le contenu d'une autre cellule ou d'une formule

Réaliser une barre de données progressive à l’aide de la mise en forme conditionnelle

Comment affecter une mise en forme conditionnelle ? Il permet d’appliquer un format spécifique pour chaque cellule, de manière automatique en fonction de son contenu, elle permet également de réaliser certaines analyses sur une série de valeurs. � Sélectionnez une plage de cellules � Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche en regard de

Mise en forme conditionnelle � Les règles de mise en surbrillance des cellules et les règles des valeurs plus/moins élevées permettent d'effectuer des mises

en forme conditionnelles simples :

� Sélectionnez l'option Autres règles pour accéder à plus d'options …




� Les Barres de données Une barre de données vous aide à voir la valeur d'une cellule par rapport à d'autres cellules. La longueur de la barre de données représente la valeur dans une cellule. Plus la barre est haute, plus la valeur est élevée et moins elle est haute, plus la valeur est basse. � Sélectionnez les cellules ayant les valeurs pour la barre de données. � Sélectionnez l'option Barre de données et le style de présentation de votre choix. � Élargissez la colonne pour mieux représenter la barre de données.

� Vous pouvez également ajouter des nuances de couleurs et des jeux d'icônes pour

représenter de manière différente les barres de données.

Nuances de couleurs Jeux d'icônes

Un jeu d'icônes permet d'annoter et de classer des données en trois à cinq catégories séparées par une valeur seuil. Chaque icône représente une plage de valeurs. Par exemple, dans le jeu 3 Jeu d'icônes de flèches, la flèche rouge orientée vers le haut représente les valeurs les plus élevées, la flèche jaune horizontale les valeurs du milieu et la flèche verte orientée vers le bas les valeurs les plus basses (elles indiquent l’évolution de la proportion par rapport à l’écart le plus important de la plage de cellules). Vous pouvez également utiliser des formules pour indiquer quand le format sera appliqué : � Sous l'onglet Accueil, cliquez sur la flèche en regard de Mise en forme conditionnelle et sélectionnez Nouvelle règle puis

Utiliser des formules …





Chapitre 03 : Maîtriser les outils de gestion et de synthèse


Chapitre 03

Maîtriser les outils de gestion et

de synthèse




APPLICATION 13. Analyser des hypothèses

Objectifs : Utiliser la fonction VPM (fonction financière)

Utiliser le Gestionnaire de scénarios

Utiliser une table de données à simple et double entrée

Utiliser la valeur cible

Utiliser le Solveur

Une feuille de calcul servant à réaliser une analyse d’hypothèses est souvent appelée modèle parce qu’elle sert à produire de

nombreux résultats.

Pour effectuer une analyse d’hypothèses dans une feuille de calcul, vous modifiez la valeur d’une ou de plusieurs cellules

d’entrées (cellules contenant des données plutôt que des formules) puis vous observez les effets sur les cellules dépendantes (qui

contiennent généralement une formule et qui s’adaptent en fonction des cellules d’entrées).

Analyse d’hypothèses sur un emprunt d’investissement A/ Utiliser la fonction VPM Cette fonction permet de calculer le remboursement périodique d'un emprunt (Capital + intérêt) sur la base de remboursements constants et d'un taux d'intérêt fixe.

Exercices � Je souhaiterais obtenir le montant d'une échéance mensuelle pour un emprunt de 240.000 € à un taux d'intérêt de 5,8 %

par an . L'emprunt s'étale sur une durée de 35 ans. Réponse : 1336,37€

� Je souhaiterais obtenir le montant d'une échéance mensuelle pour un emprunt de 3200€ à un taux d'intérêt de 0,6 % par mois . L'emprunt s'étale sur une durée de 1 an. Réponse : 277,18€

B/ Analyses de plusieurs hypothèses

� Supposons un emprunt de 25000 € à un taux de 10,50% sur 4 ans …

Les questions nécessitant, ici, une analyse d’hypothèses sont les suivantes : 1. Que devient la mensualité si le taux d’intérêt passe à

10% ? 2. Que se passe-t-il si le prêt est fait sur 5 ans ? 3. Qu’en est-il de la mensualité si le montant emprunté

baisse légèrement ?




C/ Gestionnaire de scénarios Vous avez créé un modèle qui répond à vos besoins. On vous offre plusieurs possibilités pour améliorer votre performance. Mais

laquelle est la meilleure ?

Vous pourriez les essayer dans votre modèle l'un après l'autre. Cependant, le gestionnaire de scénarios vous permet de

comparer rapidement plusieurs hypothèses, ou scénarios pour utiliser le terme d'Excel, et de générer un tableau de synthèse

avec les résultats.

Attention: cette commande n’est réellement utile que si vous avez déjà un modèle complet et opérationnel. Toutes les variables

doivent se retrouver sur une même feuille de calcul.

Nous allons utiliser le gestionnaire de scénario pour envisager 3 scénarios d’investissement (voir ci-dessus) 1. Sélection des cellules qui varieront selon les différents scénarios Ici B4 :B6 2. Dans le ruban Données / Analyse de scénario / Gestionnaire de scénario 3. Cliquer sur AJOUTER pour définir un scénario 4. Compléter la boîte Ajouter un scénario, ici, nous reproduisons l’emprunt initial

5. Validez par OK, la boîte valeurs de scénarios s’ouvre …

6. Cliquez sur OK, la boîte gestionnaire de scénarios réapparaît Dans la zone NOM DE SCENARIO, entrez Taux d’intérêt à 10% 7. 8. Répétez les mêmes opérations pour l’emprunt sur 5 ans et la réduction du montant emprunté à 21000€

Elle reflète bien les valeurs initiales de l’emprunt : 25000€ à 10,5% sur 4 ans

Dans la boîte Valeurs de scénarios, modifiez le taux en B5 / OK




Afficher les scénarios 1. Sélectionner le scénario à afficher dans la liste de scénarios 2. Cliquer sur le bouton AFFICHER 3. La simulation apparaît dans la feuille de calcul, en arrière-plan Générer une synthèse de scénarios La méthode ci-avant nécessite de basculer entre les différents scénarios pour les comparer, la synthèse va permettre de

comparer tous ces résultats sur une même feuille de calcul

1. Sélectionnez la plage A4:B10 et collez un nom automatiquement aux cellules B4 :B10 en fonction de la colonne de gauche

(voir chapitre 1) Cette manipulation a pour objectif de rendre notre synthèse plus claire

2. Dans le ruban Données / Analyse de scénario / Gestionnaire de scénario Cliquez sur le bouton SYNTHESE, la boîte suivante apparaît …

3. Validez par OK, la synthèse des scénarios apparaît sous forme tabulaire dans une nouvelle feuille

Le rapport présente un plan, il est donc possible d’afficher et de masquer des niveaux de détail Fusion de scénarios Une limitation des scénarios est qu'ils sont seulement disponibles sur la feuille de calcul sur lequel ils ont été créés. Le bouton de fusion de scénarios vous permet de copier tous les scénarios d'une feuille de calcul à une autre ou même à un autre document d'Excel. Cela peut être pratique lorsque plusieurs personnes travaillent sur un même modèle mais développent des scénarios différents. D/ Utiliser une table de données Une table de données est une plage de cellules qui montre comment la modification d’une ou deux variables dans vos formules

peut affecter le résultat de ces formules. Les tables de données fournissent un raccourci permettant de calculer plusieurs

résultats en une seule opération ainsi qu’un mode d’affichage et de comparaison des résultats de toutes les différentes

variations dans votre feuille de calcul.

Nous sélectionnons ici comme cellules résultantes la plage B8:B10 car ce sont les cellules dépendantes qui sont concernées ici




Table de données à une entrée � Reprenez votre modèle de remboursement et complétez-le comme suit … � La cellule E4 faisant appel à la cellule B8, elle contiendra donc = B8

1. Sélectionnez la plage D4:E11 (structure de la table) 2. Dans le ruban Données / Analyse de scénario / Tables de données

3. Validez par Ok, Excel génère les mensualités selon chaque taux d’intérêts 4. Mettez les nombres en forme comme d’habitude Table de données à double entrée � Reprenez votre modèle de remboursement et complétez-le comme suit … � La cellule D4 faisant appel à la cellule B8, elle contiendra donc = B8

1. Sélectionnez la plage D4 :G11 2. Dans le ruban Données / Analyse de scénario / Tables de données

Nous plaçons notre référence dans Cellules d’entrée en colonne car les variables d’entrées sont placées en colonne, Placez dans cette zone la référence B5 car la cellule B9 (référencée en E4) utilise le taux d’intérêt annuel référencé en B5

La durée du prêt figure dans une ligne et doit donc apparaître dans la zone Cellule d’entrée en ligne Le taux d’intérêt figure dans une colonne et doit donc apparaître dans la zone Cellule d’entrée en colonne




3. Validez par Ok et mettez vos nombres en forme comme d’habitude

� Vous obtenez ainsi les différentes mensualités en fonction des taux d’intérêts et de la durée E/ Utiliser la valeur cible La valeur cible permet de définir en quoi la valeur des cellules doit être modifiée de manière à produire un résultat donné à la

sortie.

Exemple : L’outil valeur cible peut vous permettre de déterminer combien d’unités vous devez vendre afin d’obtenir un

objectif commercial déterminé � Reprenez votre modèle de remboursement, nous souhaitons savoir combien il est possible d’emprunter si l’on veut

maintenir le total versé à 28000€ 1. Cliquer en B9

La 1ère étape consiste à sélectionner une cellule cible, celle-ci contient obligatoirement une formule et nous y substituerons une valeur particulière – la cible

2. Dans le ruban Données / Analyse de scénario / Valeur cible 3. Complétez-la comme suit …

4. Validez par Ok, la boîte Etat de la recherche s’ouvre vous confirmant qu’une solution a bien été trouvée

Lorsque vous validez par Ok, votre feuille de calcul affiche les nouvelles valeurs F/ Utiliser le Solveur Le solveur d’Excel identifie la valeur la plus juste pour une formule en modifiant des valeurs d’entrée de la feuille de calcul. La

cellule contenant la formule est nommée cellule cible. Les cellules contenant des valeurs pouvant être modifiées sont appelées

cellules variables.

Le solveur permet de réaliser une analyse d’hypothèses complexe impliquant plusieurs valeurs d’entrées ou lorsque celles-ci

doivent répondre à des contraintes spécifiques.

1. Où trouver le solveur ?

L'accès au solveur se fait par l'onglet Données

Si vous ne voyez pas cet outil, c'est que la macro complémentaire solveur n'a pas été activée.

La cellule cible reste B9 Nous souhaitons limiter le total versé à 28000 Nous souhaitons faire varier le montant emprunté (B4)




L’application SOLVEUR ne fait pas partie intégrante du logiciel Excel, elle constitue une macro complémentaire qui n’est pas systématiquement disponible. 2. Charger l’application Dans les options d'Excel, choisir compléments, dans la liste des compléments inactifs cherchez Complément solveur, dans le bas de la fenêtre, choisissez Atteindre. Dans la fenêtre qui s'ouvre, cochez le solveur : 3. Utiliser le SOLVEUR

Imaginons un commerçant souhaitant fixer sa marge commerciale en fonction des quantités vendues, du prix de vente unitaire et du prix d'achat unitaire. B1, B2 et B3 est tapé au clavier, ce sont les cellules variables. La cellule B4 contient la formule suivante =B1*(B2-B3). La cellule B4 est la cellule résultante dont la valeur dépend des cellules B1, B2 et B3.

Notre commerçant souhaite atteindre une marge commerciale de 800.000 € mais certaines contraintes s'imposent : La quantité vendue ne doit pas être supérieure à 600 unités.

La concurrence limite le prix de vente à un maximum de 3500 €.

Le prix d'achat unitaire ne peut être inférieur à 1800 €.

Le solveur sera complété comme suit :

La cellule cible est celle qui contient l'objectif, c'est à dire la marge commerciale. Sa valeur a été fixée à 800 000 €. Cette valeur peut également être définie comme un maximum ou un minimum.

Les cellules variables contiennent les valeurs de départ du problème.

Les contraintes sont les valeurs imposées éventuellement par la situation étudiée.

Méthodes de résolution utilisées par le Solveur : Vous pouvez choisir n’importe lequel des trois algorithmes (ou méthodes de résolution): GRG non linéaire : Destiné aux problèmes non linéaires simples. Simplex PL : Destiné aux problèmes linéaires. Evolutionary : Destiné aux problèmes complexes.




La fenêtre d'ajout d'une contrainte, dans l'exemple, la quantité vendue a été fixée à un maximum de 600 unités :

Aller plus loin avec l’outil solveur : http://www.solver.com/excel-solver-help




APPLICATION 14. Concevoir un Tableau/Graph croisé dynamique

Objectifs : Générer un tableau croisé dynamique

Modifier les champs utilisés dans le rapport

Modifier la formule utilisée dans le rapport

Regrouper les données

Formater un tableau croisé dynamique

Analyser les données en 3D

Mettre à jour les données d’un tableau croisé dynamique

Créer un graphique croisé dynamique

Les tableaux croisés dynamiques permettent de synthétiser, d’analyser, d’explorer et de présenter vos données. Ce genre de

tableau peut remplacer un nombre interminable de lignes et de colonnes en une représentation intelligible et claire.

Un tableau croisé dynamique permet de représenter des données sur plusieurs dimensions, il est interactif : après l’avoir créé, il

est possible de modifier l’organisation des données et même insérer de nouvelles formules pour calculer de nouveaux résultats.

1. Générer un tableau croisé dynamique recommandé (Excel 2013) � Assurez-vous que vos données ont des en-têtes de colonnes ou des en-têtes de tableaux, et qu’elles ne contiennent pas

de lignes vides � Cliquez dans la base de données � Cliquez sur Insertion > Tableaux croisés dynamiques recommandés

� Dans la boîte de dialogue Tableaux croisés dynamiques recommandés, cliquez sur une disposition de tableau croisé

dynamique pour obtenir un aperçu, puis sélectionnez celui qui vous convient le mieux. � Cliquez sur OK. � Excel place le tableau croisé dynamique sur une nouvelle feuille de calcul et affiche la Liste des champs afin que vous

puissiez réorganiser les données du tableau croisé dynamique comme vous le voulez. 2. Générer un tableau croisé dynamique � Assurez-vous que vos données ont des en-têtes de colonnes ou des en-têtes de tableaux, et qu’elles ne contiennent pas

de lignes vides � Cliquez dans la base de données � Cliquez sur Insertion > Tableaux croisés dynamiques � La boîte de dialogue Assistant Tableau et graphique croisés dynamique s’ouvre

La fenêtre qui s'affiche, permet: De redéfinir la plage de données source. Vous pouvez faire référence à une plage de cellules, une plage nommée ou à un tableau. De choisir une source externe De définir l'emplacement du TCD.

� Validez par OK




� Une fenêtre nommée "Champs de tableau croisé dynamique" s'affiche sur la droite de l'application. Cette nouvelle interface sert de support principal pour gérer la création et la modification de vos tableaux croisés dynamiques. La "Liste de champs" contient tous les noms d'entêtes de la base de données, ainsi que les champs calculés (valeurs). Ce sont donc tous les champs disponibles pour créer le tableau croisé dynamique. Vous pouvez les utiliser comme étiquette de colonnes ou de lignes, comme filtre de rapport ou en tant que champ de valeur. Un champ coché signifie qu'il est actuellement pris en compte dans le TCD. Il faut intégrer les noms de champs dans votre tableau pour pouvoir analyser vos données,

A savoir …

Champs en colonne : Champ vertical, chaque élément de ce champ occupera une colonne Champ en ligne : Champ horizontal, chaque élément de ce champ occupe une ligne Valeurs : s’affichent sous forme de valeurs numériques synthétisées dans le tableau croisé dynamique, Excel possède plusieurs méthodes pour résumer les tableaux : Somme, Moyenne, Max, … La zone "Filtres" s’affichent comme des filtres de rapport de premier niveau au-dessus du tableau croisé dynamique. Faites un simple glisser/déposer pour mettre en forme votre

rapport. Sélectionnez les champs dans la liste et faite les glisser dans

la zone de votre choix, en fonction des analyses à effectuer.

Pour supprimer un champ du TCD, effectuez l'opération inverse:

glissez/déposez de la zone vers la liste.

Permet de changer l’apparence des sections dans la liste des champs, cliquez sur ce bouton puis sélectionnez la disposition souhaitée.

2. Modifier un tableau croisé dynamique 2.1. Filtrer manuellement

Pour vous concentrer sur une partie plus petite des données d’un tableau croisé dynamique pour effectuer une analyse plus précise, vous pouvez filtrer les données.

Lorsque vous cliquez sur un nom de champ dans le TCD, vous remarquerez un triangle noir qui s'affiche sur la droite. Cette petite icône donne accès aux options de tri et de filtre dans le rapport. La liste des éléments apparait dans la partie inférieure de la boîte de dialogue. Décochez les éléments que vous ne souhaitez pas prendre en compte dans le tableau croisé dynamique. Vous pouvez appliquer des tris d'affichage croissant ou décroissant aux étiquettes et aux valeurs afin de mettre en avant les chiffres les plus importants de votre rapport. Les filtres permettent d'afficher ou de masquer des éléments à partir des conditions que vous aurez défini, chaque type de donnée (texte, numérique ou chronologique) disposant de critères spécifiques.




Pour supprimer tous les filtres en une fois, cliquez n’importe où sur le tableau croisé dynamique, puis cliquez sur Analyse > Effacer > Effacer les filtres. 2.2. Exploiter les segments

Cliquez n’importe où dans le tableau croisé dynamique pour afficher les Outils de tableau croisé dynamique sur le ruban. Cliquez sur Analyse > Insérer un segment Un segment apparaît pour chaque champ que vous avez coché dans la boîte de dialogue Insérer des segments.

Les segments sont des composants de filtrage proposant des boutons avec lesquels vous filtrez les données dans un tableau croisé dynamique. Dans chaque segment, cliquez sur les éléments que vous voulez afficher dans le tableau croisé dynamique, vous connaissez ainsi le détail du filtrage effectué sans ouvrir la liste des éléments (comme dans le filtre manuel). 2.2 Modifier la formule utilisée

La somme est la fonction utilisée par défaut pour les tableaux croisés dynamiques

� Cliquez droit dans votre TCD puis sur Paramètres des champs de valeurs

� Sélectionnez Moyenne dans la liste Synthèse par, le tableau croisé dynamique sera recalculé en utilisant la fonction moyenne au lieu de la somme

� Cliquez sur le bouton Format de Nombre, sélectionnez le format monétaire Euro

Vous pouvez cumuler plusieurs synthèses du même champ dans la zone, cela

permet de visualiser rapidement des informations complémentaires sur le champ à

analyser.

3. Analyser les données en 3D Le tableau croisé dynamique est à deux dimensions après avoir défini des champs de colonne et de ligne. Un tableau croisé peut être converti en outil d’analyse 3D en lui ajoutant un filtre du rapport. � Déplacez le bouton de champ Service de la zone des colonnes vers la zone Filtres




4. Grouper ou dissocier des données dans un rapport de tableau croisé dynamique Le regroupement de données dans un tableau croisé dynamique peut vous aider à afficher un sous-ensemble de données à

analyser.

� Dans le tableau croisé dynamique, cliquez avec le bouton droit sur un champ numérique ou de date et d’heure, puis cliquez sur Grouper.

� Complétez les zones Début et Fin selon vos besoins

Pour supprimer un regroupement, cliquez avec le bouton droit sur un élément des données groupées, puis cliquez sur Dissocier. 5. Mettre à jour les données d’un tableau croisé dynamique Les données affichées dans un tableau croisé dynamique sont liées directement à la liste source, celle qui a servi à créer le

tableau. Celui-ci ne peut pas être directement modifié.

� Revenez dans votre tableau croisé dynamique et cliquez dans la plage de données � Cliquez sur le bouton Actualiser du ruban Options 6. Modifier la mise en forme d’un tableau croisé � Cliquez dans le TCD � Cliquez sur Styles de tableau dans le ruban création pour sélectionner un format 7. Créer un rapport de graphique croisé dynamique � Cliquez dans la feuille contenant la BDD � Sélectionnez le ruban "Insertion", groupe Graphiques, cliquez

Graphiques recommandés.




� Sous l’onglet Graphiques recommandés, sélectionnez un graphique avec l’icône de graphique croisé dynamique dans le coin supérieur. Un aperçu de votre graphique croisé dynamique apparaît dans le volet de visualisation.

� Si aucun graphique croisé dynamique ne répond à vos attentes, cliquez sur Graphique croisé dynamique sous l’onglet Insertion (et non sur Graphiques recommandés). Un graphique croisé dynamique vide apparaît, ainsi que la liste de champs à l’aide de laquelle vous pouvez ajouter ou réorganiser des champs dans votre graphique croisé dynamique. Dans la Liste de champs, sélectionnez les champs que vous voulez afficher dans le graphique croisé dynamique.

� Après avoir créé un graphique croisé dynamique, vous pouvez le personnaliser comme tout autre graphique standard. Un graphique croisé dynamique affiche non seulement les séries de données, les catégories et les axes du graphique de la même

façon qu’un graphique standard, mais il offre également des contrôles de filtrage interactifs directement sur le graphique pour

vous permettre d’analyser rapidement un sous-ensemble de vos données.

Aller plus loin ! Créer un rapport de tableau croisé dynamique dans Excel qui combine des données issues de plusieurs tables Access :

http://office.microsoft.com/fr-be/excel-help/didacticiel-analyse-de-donnees-de-tableau-croise-dynamique-a-l-aide-d-un-modele-de-donnees-dans-excel-2013-HA102922619.aspx?CTT=5&origin=HA102897373


Chapitre 04 : L’automatisation des tâches


Chapitre 04

L’automatisation des tâches




APPLICATION 15. Créer et utiliser une macro-commande

Objectifs : Créer une macro

Affecter une macro à un bouton, créer un groupe de ruban personnalisé

Exécuter une macro

Consulter une macro-commande en VB

Qu’est-ce qu’une macro ? Si vous effectuez fréquemment une série de commandes ou de tâches, Excel vous permet de l'automatiser à l'aide d'une macro.

Concrètement, la macro est une suite d'instructions indiquant à Excel une suite d'opérations à réaliser. Effectuer une série de

calculs, copier, vérifier, modifier, supprimer le contenu de cellules...

Toutes les macros Excel sont écrites dans le langage de programmation Visual Basic for Applications, abrégé en VBA. Lorsque

vous créez une macro avec l’enregistreur de macro Excel, celui-ci écrit les instructions VBA nécessaires.

� Nous souhaitons ici créer une macro qui nous permettra d’appliquer toute une série de mises en forme aux cellules

sélectionnées. 1. Créer, enregistrer une macro

1. Placez-vous dans une cellule vide, 2. Cliquez sur le ruban Affichage et déroulez le bouton Macros, cliquez sur Enregistrer une macro 3. Précisez le nom de la macro,

Attention à ne jamais mettre d’espace dans les noms de macros. Evitez de même tous les caractères latins (Apostrophes, accents, etc...).

4. Et son emplacement pour la sauvegarder, Dans la liste Enregistrer la macro dans, sélectionnez le classeur où vous voulez conserver la macro. Si vous souhaitez qu’une macro soit disponible chaque fois que vous utilisez Excel, sélectionnez Classeur de macros personnelles.

5. Cliquez sur Ok pour démarrer l’enregistrement de votre macro 6. Réalisez les actions que vous souhaitez enregistrer 7. Lorsque la macro est terminée, il faut arrêter l’enregistrement :

Cliquez sur le ruban Affichage pour afficher sa barre d’outils, déroulez le bouton Macros et cliquez sur Arrêter l’enregistrement.

2. Utiliser la macro

1. Ruban Affichage / Dans le groupe Macros / Afficher les macros 2. Cliquer sur votre macro puis Exécuter

3. Créer un bouton pour votre macro

Pour exécuter une macro à partir d’un bouton de la barre d’outils Accès rapide, vous devez tout d’abord ajouter le bouton à la barre d’outils

1. Déballez la barre d’accès rapide et cliquez sur Autres commandes dans la liste,

2. La boîte de dialogue Options Excel s’affiche, sélectionnez la rubrique Personnaliser,

3. A l’aide de la première liste déroulante, choisissez Macros,




4. Sélectionnez votre macro, 5. Cliquez sur le bouton Ajouter 6. En bas de la boîte de dialogue, cliquez sur le

bouton Modifier, 7. Choisissez une image pour votre bouton,

cliquez sur Ok, 8. Cliquez à nouveau sur Ok pour valider la

boîte de dialogue,

9. Votre macro apparaît dans la barre d’accès rapide au dessus des rubans, il ne reste plus qu’à la tester.

4. Exécuter une macro en cliquant sur un bouton dans un groupe personnalisé sur le ruban Vous pouvez créer un groupe personnalisé qui apparaît sur un onglet dans le ruban, puis affecter une macro à un bouton dans ce groupe. 1. Clic droit sur le ruban à personnaliser / Personnaliser le ruban 2. Cliquez sur Nouveau Groupe et affectez-lui un nom 3. Vous pourrez ensuite lui ajouter vos macros et personnaliser l’apparence

du bouton et du texte comme détaillé ci-avant Vous pouvez, par exemple, ajouter un groupe personnalisé nommé « Mes

macros » à l’onglet Développeur, puis ajouter vos macros personnalisées à ce

nouveau groupe.

5. Macro absolue ou relative Macro absolue

Si vous sélectionnez une zone ou une cellule précise après avoir lancé l'enregistrement, la macro s'exécutera de même et appliquera vos instructions à ou à partir de cette cellule ou plage précise, sans modification possible : c'est une macro absolue. Procédez ainsi lorsque vous voulez absolument que la macro affecte la même cellule, comme placer une date dans la cellule A1. Macro relative

Si vous sélectionnez la cellule ou plage avant de lancer la macro, le processus de sélection ne sera pas enregistré dans la macro et celle-ci s'exécutera sur ou à partir de la zone active, quelle qu'elle soit : c'est une macro relative. Les macros relatives sont beaucoup plus polyvalentes. Pour enregistrer la macro relativement, cliquez sur le bouton Utiliser les références relatives, dans le groupe Code de l'onglet Développeur, avant de lancer l'enregistrement.