Macro - Syllabi 2010-Mod X Expert FR-UK.pdf · 4.2.1 Quand les fonctions nécessitent-elles une formule matricielle ? ... une feuille de calcul ... de la galerie de commandes rassemblées

Excel 2010

Module X

Expert

Sygma-D sprl ProsysInfo sprl Rue des Arts, 17 Rue du Déversoir 38 B - 1400 Nivelles B - 6200 Châtelet [email protected] [email protected] +32 (0)475.50.24.24 +32 (0)71.40.06.69 www.sygma-d.com

mailto:[email protected]

mailto:[email protected]

http://www.sygma-d.com/

1-2 Vérification des erreurs | Excel 2010.

Table des matières

1 Vérification des erreurs ............................................................................................ 4

1.1 Analyser les formules ....................................................................................................... 4

1.2 Corriger une par une un ensemble d'erreurs récurrentes ............................................... 4

1.3 Repérer les antécédents. .................................................................................................. 5

1.4 Évaluer une formule ......................................................................................................... 6

2 Formules avec inconnues ......................................................................................... 7

2.1 Valeur cible ....................................................................................................................... 7

2.2 Solveur .............................................................................................................................. 8

2.2.1 La cellule cible .............................................................................................................. 8

2.2.2 Les cellules variables .................................................................................................... 9

2.2.3 Contraintes .................................................................................................................. 9

2.2.4 Comment puis-je déterminer l'optimisation de la rentabilité des produits ? ............. 9

2.2.5 Comment puis-je induire ce modèle dans le Solveur ? ............................................. 10

2.3 Informations de base ...................................................................................................... 10

2.4 Quelles sont les méthodes utilisées par le Solveur ? ..................................................... 12

2.4.1 Nous avons démontré que notre problème est un modèle linéaire. ........................ 18

2.4.2 Existe-t-il une solution pour chaque modèle de Solveur ? ........................................ 19

2.4.3 Rapport de Solveur .................................................................................................... 20

2.4.4 Sauvegarde d'un scénario .......................................................................................... 22

3 Scénarii ................................................................................................................. 23

3.1 Créer des scénarii ........................................................................................................... 23

3.2 Afficher un Scénario ....................................................................................................... 27

4 Formules matricielles ............................................................................................. 30

4.1 Détail des formules matricielles ..................................................................................... 30

4.1.1 Manipulation des formules matricielles .................................................................... 30

4.1.2 Les sommes conditionnelles ...................................................................................... 30

4.2 Quand est-il nécessaire d'introduire une matrice ? ....................................................... 33

4.2.1 Quand les fonctions nécessitent-elles une formule matricielle ? ............................. 33

4.2.2 Formules qui font appele à plusieurs cellules ........................................................... 34

5 Mise en forme conditionnelle avancée faisant appel à des formules ....................... 35

5.1 Détection de doublons ................................................................................................... 35

5.2 Masquer des résultats de formules retournant un message d'erreur ........................... 36

1-3 Vérification des erreurs | Excel 2010.

5.3 Marquer les valeurs présentes dans une plage .............................................................. 36

5.3.1 Mettre les week-ends et les jours fériés en évidence ............................................... 37

6 Protéger une feuille de calcul ................................................................................. 41

6.1 Protection de cellules ...................................................................................................... 41

6.2 Activer la protection ....................................................................................................... 41

6.3 Ôter la protection de la feuille ........................................................................................ 42

6.4 Protection d'un classeur ................................................................................................. 42

6.4.1 Protéger la structure ................................................................................................. 43

6.4.2 Protéger une fenêtre ................................................................................................. 43

6.5 Masquer des données, des feuilles et des formules ....................................................... 43

6.5.1 Masquer la totalité d'un classeur .............................................................................. 43

6.5.2 Afficher à nouveau un classeur masqué .................................................................... 43

6.5.3 Masquer une feuille................................................................................................... 44

6.5.4 Afficher une feuille masquée .................................................................................... 44

6.5.5 Masquer une formule ................................................................................................ 45

7 Modification .......................................................................................................... 47

7.1 Conserver les modifications ............................................................................................ 47

7.2 Aperçu général des changements dans une feuille de calcul séparée ........................... 49

7.3 Accepter et refuser des changements ............................................................................ 50

7.4 Accepter, ignorer les changements conflictuels ............................................................. 50

7.5 Cesser de partager un classeur ....................................................................................... 52

4 Vérification des erreurs | Excel 2010.

1 Vérification des erreurs

1.1 Analyser les formules

Une formule dépend au minimum de deux cellules de données. Le résultat d'une formule est indiqué dans une autre cellule. Il existe donc une relation entre ces cellules.

1.2 Corriger une par une un ensemble d'erreurs récurrentes

Si vous avez contrôlé les erreurs d'une feuille de calcul, les erreurs ignorées réapparaîtront à chaque ouverture de la feuille de calcul.

Sélectionnez la feuille de calcul dont vous voulez contrôler les erreurs.

Si vous avez activé le mode de calcul manuel, appuyez sur la touche F9 de votre clavier pour forcer le calcul de la feuille.

Cliquez sur Vérification des erreurs (Error Checking) dans le groupe Audit de formule (Formula Auditing) de l'onglet Formules (Formulas).

La boîte de dialogue vérification des erreurs (Error Checking) apparaît si des erreurs sont détectées.

Si vous ignorez des erreurs, vous pouvez revenir sur le contrôle de ces erreurs (la boîte de dialogue Vérification des erreurs (Error Checking) peut rester ouverte):

Cliquez sur l'onglet fichiers (File), Options (Options) puis sur Formules (Formulas).

Dans la section Vérification des erreurs (Error Checking), cliquez sur Rétablir les erreurs ignorées (Reset Ignored Errors) puis sur OK.

Cliquez sur Reprendre dans la fenêtre de Vérification des erreurs (Error Checking).


Cliquez sur Vérification des erreurs (Error Checking) dans le groupe Audit de formule (Formula Auditing) de l'onglet Formules (Formulas).

Dans la boîte de dialogue, choisissez l'action correspondant à ce que vous souhaitez. Les actions proposées dépendantes du type d'erreur rencontré.

Remarque : lorsque vous double-cliquez sur une cellule contenant une formule, Excel indique en couleurs les adresses des cellules antécédentes et utilise les mêmes couleurs pour les situer dans la feuille de calcul. Cela vous permet de voir au premier coup d'œil quelle(s) est (sont) la (les) cellule(s) qui provoque(nt) des erreurs.

1.3 Repérer les antécédents.

Sélectionnez l'onglet Formules (Formulas) et cliquez sur la commande Repérer les antécédents (Trace Precedents) dans le groupe Audit de formules (Formule Auditing).

Sélectionnez la formule à examiner. Les flèches indiquent les différents intervenants de la formule.

Sélectionnez l'onglet Formules (Formulas) et cliquez sur la commande Repérer les dépendants (Trace Dependents) dans le groupe Audit de Formules (Formule Auditing).

Sélectionnez une cellule source. La flèche indique la formule dans laquelle l'adresse de cette cellule est utilisée.


1.4 Évaluer une formule

Sélectionnez l'onglet Formules (Formulas) et cliquez sur la commande Évaluation de formule (Evaluate Formula) dans le groupe Audit de Formules (Formule Auditing).

Cliquez sur évaluer pour parcourir les différentes étapes de l'évaluation de la formule.

7 Formules avec inconnues | Excel 2010.

2 Formules avec inconnues

2.1 Valeur cible

Valeur cible fait partie de la galerie de commandes rassemblées sous le nom Analyse Scénario du groupe Outils De Données.

Si vous connaissez le résultat d'une formule mais pas la valeur à donner à un des intervenants de cette formule pour atteindre le résultat escompté, utilisez la fonctionnalité Valeur cible (Goal Seek). La fonctionnalité Valeur cible (Goal Seek) calcule la valeur à donner à l'intervenant pour atteindre le résultat souhaité dans la cellule calculée.

Sélectionnez l'onglet Données (Data) et cliquez sur la commande Analyse Scénario (What-If-Analysis) dans le groupe Outils de données (Data Tools).

Sélectionnez : Valeur Cible (Goal Seek)

J'ai promis à un bon client qu’il pourrait obtenir 10 livres d'exercices pour un total de 700 € TVA incluse. Le prix de base d'un livre d'exercices est de 65 € plus 21 % de TVA. Le prix total est donc de 787 €. Je dois ajuster le prix de base de mon livre d'exercices pour arriver au total de 700€ TVAC.


Choisissez dans la fenêtre suivante si vous voulez garder les nouvelles valeurs ou restaurer les anciennes valeurs.

2.2 Solveur

Utilisez le Solveur (Solver) lorsque vous souhaitez déterminer le meilleur moyen de faire quelque chose. Autrement dit, lorsque vous souhaitez ajuster les valeurs de certaines cellules dans une feuille de calcul afin d'atteindre le résultat souhaité (maximisées ou minimisées en option).

Un modèle d'optimalisation comporte trois parties:

la Cellule cible (Target cell)

les Cellules variables (Changing cells)

les Contraintes (Constraints).

2.2.1 La cellule cible

Comme son nom l'indique la Cellule cible (Target Cell) représente la valeur de l'objectif à atteindre dans le problème.


Par exemple : optimiser les revenus mensuels.

2.2.2 Les cellules variables

Les Cellules variables (Changing cells) sont les cellules de la feuille de calcul dont la valeur peut être ajustée afin d'atteindre la valeur de la cellule cible.

Exemple: le rythme mensuel de production de chaque produit.

2.2.3 Contraintes

Les contraintes (Constraints) sont les critères que vous définissez pour les cellules variables.

Exemple : ne pas dépasser les ressources disponibles et ne pas produire plus de produits qu'il n'y a de demande pour ces produits

2.2.4 Comment puis-je déterminer l'optimisation de la rentabilité des produits ?

Les entreprises planifient souvent la production de leurs produits suivent un calendrier mensuel ou hebdomadaire. Dans sa forme la plus simple, se pose la question de la gestion de la quantité de composants d'un produit afin d'en optimiser le profit. L'optimisation des produits doit souvent obéir aux contraintes suivantes :

L'on ne peut par exemple, pour une combinaison de produits, pas utiliser plus de ressources qu'il n'y en a de disponibles.

Chaque produit doit obéir à une contrainte. La production mensuelle ne peut pas être plus importante que la demande, ce qui entraînerait une perte de la surproduction (pensez par exemple aux médicaments qui ont une date de péremption).

Dans l'exemple suivant, nous avons essayé de résoudre le problème de la production d'une gamme de produits

Supposons que vous soyez dans une société pharmaceutique pouvant produire six produits dans son usine. La production nécessite de la main-d’œuvre et de matériaux de base.

La ligne 4 du tableau ci-dessus indique le nombre d'heures de travail nécessaires à la fabrication de 1 kg de chaque produit. Par exemple, produire un kilo du produit numéro 1 requiert 6 heures de travail et 3,2 kg de matériaux.

Le prix au kilo de chaque médicament est indiqué à la ligne 6. Le prix unitaire à la ligne 7 et le bénéfice au kilo à la ligne 9. Par exemple, le produit numéro 2 est vendu 11€/kilo. Ses coûts de production sont de 5,70€. Donc, la vente d'un kilo de produit numéro 2 engendre 5,30 € de bénéfice.


La demande mensuelle de chaque médicament est indiquée à la ligne 8. Par exemple, la demande de produit numéro 3 est de 1.041 kg.

Pour le mois en cours, 4.500 heures de main-d’œuvre et 1.600 kg de matières premières sont disponibles. Comment optimiser les gains de cette société pour ce mois ?

2.2.5 Comment puis-je induire ce modèle dans le Solveur ?

Nous avons maintenant vous montrer comment définir la cellule cible, les différentes cellules variables ainsi que les contraintes dans le Solveur. Un clic sur le bouton Solveur présentera la solution suivant vos critères.

Attention ! Le Solveur n'est pas installé par défaut dans le Ruban. Nous devrons donc l'installer nous-mêmes manuellement. Une fois installé, le Solveur reste dans le Ruban.

Cliquez sur la commande Fichier (File) puis sur Options (Options)

Choisissez Compléments (Add-Ins)

Choisissez Gérer (Manage) – Compléments Excel (Excel Add-ins) et cliquez sur le bouton Atteindre (Go).

Après que le Solveur (Solver add-in) ai été chargé, la commande Solveur (Solver) est disponible dans le groupe Analyse (Analysis) de l'onglet Données (Data)

2.3 Informations de base

Sélectionnez l'onglet Données (Data) et cliquez sur la commande Solveur (Solver) dans le groupe Analyse (Analyse).


Taper dans la zone Valeur Cible (Set Objective) l'adresse de la cellule dont vous voulez définir la valeur à atteindre. Cette cellule doit contenir une formule.

Voici la méthode à suivre :

Pour que la valeur cible soit aussi élevée que possible, cliquez sur Max.

Pour que la valeur cible soit aussi basse que possible, cliquez sur Min.

si vous souhaitez donner une valeur spécifique à la cellule cible, cliquez sur Valeur (Value Off) et indiquez la valeur souhaitée.

Indiquez dans la zone En changeant les cellules variables (By Changing Variable Cells) les adresses des cellules dont la valeur peut être modifiée. Les références non contiguës sont à séparer par un “;”. Les cellules variables doivent être directement ou indirectement liées à la formule de la cellule cible. Vous pouvez entrer maximum 200 cellules variables.

Indiquer toutes les restrictions nécessaires dans la zone Soumis à des contraintes (Subject to the Constraints) :

cliquez sur le bouton Ajouter (Add).

Indiquez dans la zone Référence de cellule (Cell Reference) l'adresse de la cellule ou de la plage de cellules que vous voulez soumettre à des contraintes. Vous pouvez également utiliser les noms de cellules ou de plage si vous en avez défini.

Choisissez la relation (<=, =, >=, Ent (int), bin ou dif) que vous voulez établir entre la cellule et la contrainte.

Si vous cliquez sur Ent (int), le terme Entier (Integer) apparaît dans la zone restrictions.

Si vous cliquez sur bin, le terme binaire (Binary) apparaît dans la zone restrictions. Si

vous cliquez sur Dif, Tous différents (AllDifferent) apparaît dans la zone Restrictions.

Si vous choisissez <=, = ou >= pour la relation dans la zone Restriction, entrez un nombre, une adresse ou un nom de cellule ou bien une formule.

Remarque : Vous pouvez appliquer les relations ent, bin et dif uniquement dans des contraintes appliquées à des cellules variables de décision.

Cliquez sur Résoudre (Solve) et effectuez l'une des actions suivantes:

si vous souhaitez garder les valeurs proposées par Excel, cliquez sur Conserver la solution du Solveur (Keep Solver Solution) dans la boîte de dialogues Résultat du Solveur (Solver Results).

Si vous souhaitez revenir aux valeurs originales, cliquez sur Restaurer les valeurs originales (Restore Original Values).

Remarque :

vous pouvez interrompre le processus de résolution en appuyant sur la touche ESC. La feuille de calcul sera à nouveau calculée avec les dernières valeurs indiquées dans les cellules variables.

Si vous souhaitez produire un rapport détaillant la solution trouvée par le Solveur, choisissez un type de rapport dans la zone Rapport (Reports) puis cliquez sur OK. Le rapport est généré sur une nouvelle feuille de calcul du classeur. Si le Solveur ne trouve pas de solution, seuls certains rapports, ou aucun rapport ne seront disponibles.

Si vous souhaitez conserver les valeurs des cellules variables dans un scénario exploitable plus tard cliquez sur Sauver le scénario (Save Scenario) dans la boîte de dialogue Résultat du Solveur (Solver Results). Nommez le scénario dans la zone Nom de scénario (Scenario Name).


2.4 Quelles sont les méthodes utilisées par le Solveur ?

Vous pouvez sélectionner l'un des trois algorithmes de méthode de résolution dans la boîte de dialogue Paramètres du Solveur (Select a solving method) :

GRG non linéaire (Generalised Reduced Gradient) est utilisé pour les problèmes non linéaires.

LP Simplex est utilisé pour les problèmes linéaires.

Evolutionair est utilisé pour les problèmes non continus.

Un exemple :

Sélectionnez l'onglet Données (Data) et cliquez sur la commande Solveur (Solver) dans le groupe Analyse (Analyse).

La boîte de dialogue Paramètres du Solveur (Solver Parameters) apparaît.


Indiquez l'adresse de cellule $D$12 dans la zone Valeur cible (Set target cell). Indiquez les adresses de cellules D2:I2 dans la zone En changeant les cellules (By changing cell) puis indiquez le nombre de kilos à obtenir pour chaque médicament.

Nous pouvons maintenant ajouter des contraintes à notre modèle.

Cliquez sur le bouton Ajouter (Add).

La boîte de dialogue Ajout de contraintes (Add constraint) s'ouvre.

Indiquez les contraintes suivantes :

Cliquez dans la zone Référence de cellules (Cell reference) et entrez la plage D14:D15. Choisissez <= dans la liste au milieu de la boîte de dialogue. Cliquez dans la zone avec l'étiquette Contraintes (Constraint) et entrez la plage F14:F15.


Le Solveur (Solver) teste différentes valeurs pour les cellules variables en tenant compte des combinaisons qui satisfont à la fois à la contrainte indiquant que D14 <= F14 (main-d'œuvre utilisée est inférieure ou égale à main-d'œuvre disponible) et que D15 <= F15 (consommation de matières premières est inférieure ou égale aux ressources disponibles)

Cliquez maintenant sur Ajouter (Add) dans la boîte de dialogue Ajout de contraintes (Add constraint) pour ajouter des contraintes de valeur. Remplissez la boîte de dialogue Ajout de contraintes (Add constraint) comme indiqué ci-dessous.

Le Solveur (Solver) recherche maintenant des combinaisons de valeurs pour les cellules variables, il ne prend en compte que les combinaisons qui satisfont aux contraintes suivantes :

D2 <= D8 (la quantité préparée du médicament de type 1 est inférieure

ou égale à la quantité demandée pour le médicament de type 1)

E2 <= E8 (la quantité préparée du médicament de type 2 est inférieure


F2 <= F8 (la quantité préparée du médicament de type 3 est inférieure


G2 <= G8 (la quantité préparée du médicament de type 4 est inférieure


H2 <= H8 (la quantité préparée du médicament de type 5 est inférieure


I2 <= I8 (la quantité préparée du médicament de type 6 est inférieure


Cliquez sur OK. Si vous avez bien suivi toutes les étapes, la fenêtre Paramètres du Solveur (Solver Parameters) ressemble à ceci:


Dans la boîte de dialogue Paramètres du Solveur (Solver Parameters) ajoutez une contrainte spécifiant qu'aucune des valeurs dans les cellules variables ne peut être négative.


Sélectionnez Supposé Non Négatif (Assume non-negative), puis cliquez ensuite sur OK.

L'option Supposé non-négatif (Assume non-negative) permet de stipuler que le Solveur (Solver) ne peut pas utiliser de valeurs négatives pour les cellules variables.

Choisissez LP Simplex dans la zone Sélectionnez une méthode de résolution (Select a Solving Method) pour résoudre un problème linéaire.


Un modèle du Solveur est linéaire dans les conditions suivantes :

la cellule cible est calculée sur base de termes similaires à (cellules variables)*(constante).

Chaque contrainte satisfait la condition qui s'applique au modèle linéaire. Cela signifie que chaque contrainte est évaluée en ajoutant ensemble les conditions du formulaire (changement de cellule) * (constante) en additionnant et en comparant ces sommes à une constante.

Pourquoi est-ce que ce problème-ci est linéaire ?

La cellule cible est calculée comme suit :

(Profit par kilo pour le médicament 1) *

(Production en kilos pour le médicament 1) +


(Production en kilos pour le médicament 2) +

...


(Production en kilos pour le médicament 6)

Ces calculs suivent un modèle où la valeur de la cellule cible dépend de formules telles que (cellules variables)*(constante).


La limitation de main-d'œuvre est évaluée par la main-d'œuvre disponible comparée à la valeur qui est calculée comme suit:

(Main-d’œuvre utilisée par kilo de médicaments 1) *

(Production en kilos de médicaments 1) +



...



Ceci signifie que chaque contrainte d'utilisation de main-d’œuvre est évaluée sur base de formules telles que celle-ci : (cellules variables)*(constante) et que les résultats obtenus sont comparés à une constante. De la même manière, la limite fixée pour l'utilisation de la main-d’œuvre pour chaque produit répond aux exigences du modèle linéaire.

Les contraintes pour les facteurs demandés suivent le modèle suivant :

(Production du médicament 1)<=(Demande pour le médicament 1)


...


Chaque contrainte de la demande satisfait également la condition qui s'applique au modèle linéaire parce que chacune est évaluée en ajoutant ensemble les conditions du formulaire (cellules variables) * (constante) en additionnant et en comparant ces sommes à une constante

2.4.1 Nous avons démontré que notre problème est un modèle linéaire.

Si un modèle du Solveur est linéaire l'on peut être sûr que la solution optimale peut être trouvée pour le modèle du Solveur. Si un modèle du Solveur n'est pas linéaire, il n'est pas certain que la solution optimale puisse être trouvée.

Le Solveur utilise un algorithme très efficace (la méthode Simplex) pour rechercher la solution optimale. Si vous ne sélectionnez pas cette option du modèle de Solveur linéaire alors, un algorithme très inefficace (la méthode GRG2) est utilisé et il se peut que la solution optimale pour le modèle ne soit pas trouvée.


Lorsque vous cliquez sur le bouton Résoudre (Solve), le Solveur calcule la solution optimale comme expliqué ci-dessus. Sélectionnez Conserver la solution (Keep solver Solutions) si vous souhaitez conserver la solution optimale dans votre feuille de calcul.

Notre firme pharmaceutique peut maximaliser ses gains mensuels et obtenir 6.625,20 € en produisant uniquement 596,67 kg de médicaments numéro 4, 1.084 kg de médicaments numéro 5. Nous ne pouvons pas déterminer si ce profit maximum de 6.625,20 € peut être atteint par d'autres moyens. Nous savons seulement que l'entreprise, avec ses ressources limitées ainsi qu'avec la demande, ne pourra, ce mois-ci, gagner plus de 6.625,20 €. Mais elle ne répondra pas à la demande de médicaments et risquer de perdre des parts de marché si elle arrête la production des médicaments numéros 1, 2, 3 et 6.

2.4.2 Existe-t-il une solution pour chaque modèle de Solveur ?

Supposons que la demande pour chaque produit doive être respectée.

Dans ce cas, il vous faut modifier les contraintes de D2:I2 <= D8:I8 en D2:I2 >= D8:I8.

Voici comment faire :

Ouvrez le Solveur.

Cliquez sur Contraintes (Constraints) D2:I2 <= D8:I8 et cliquez ensuite sur Modifier (Change).

Modifiez la zone du milieu en “>=” puis cliquez sur OK.

Le Solveur cherchera maintenant la solution en tenant compte de la demande pour chaque produit.


Lorsque vous cliquez sur Solveur (Solve), Excel affiche le message suivant : le Solveur n'a pas pu trouver de trouver une solution plausible (Solver could not find a feasible solution). Cela signifie que la société, avec ses ressources limitées, ne peut pas répondre à la demande pour chaque produit. Soyez tranquille : le modèle est correct ! Le message indique simplement que la demande pour chaque produit ne peut être satisfaite que si vous disposez de plus de main d'œuvre et/ou de plus de marchandises.

2.4.3 Rapport de Solveur

Si vous souhaitez obtenir le rapport de la solution trouvée par le Solveur, choisissez un ou plusieurs types de rapports dans la partie droite de la fenêtre puis sur OK. Le(s) rapport(s) est(sont) présenté(s) sur une(des) feuille(s) séparée(s).


Exemple d'un rapport de sensibilité (terme à vérifier)


2.4.4 Sauvegarde d'un scénario

Si vous souhaitez enregistrer les valeurs trouvées par Excel pour votre calcul comme un scénario visualisable par après, cliquez sur Enregistrer le Scénario (Save Scenario) dans la boîte de dialogue Résultats du Solveur (Solver Results). Nommez ensuite votre scénario dans la zone Nom du Scenario (Scenario Name).

23 Scénarii | Excel 2010.

3 Scénarii

L’analyse de simulation est le processus qui consiste à modifier des valeurs dans les cellules pour voir comment ces modifications affectent le résultat des formules dans la feuille de calcul.

Trois types d’outils d’analyse de scénarios sont fournis avec Excel : Scénarios, Tables de données et Valeur cible. Les outils Scénarios et Tables de données acceptent des jeux de valeurs en entrée et déterminent les résultats possibles. Une table de données fonctionne avec une ou deux variables uniquement, mais elle peut accepter plusieurs valeurs différentes pour ces variables. Un scénario peut accepter plusieurs variables, mais il ne peut prendre en charge que 32 valeurs au maximum. L’outil Valeur cible fonctionne différemment des autres outils dans la mesure où il prend un résultat et détermine les valeurs d’entrée possibles qui permettent d’obtenir ce résultat.

Un Scénario est un ensemble de valeurs qu’Excel enregistre et qu’il peut remplacer automatiquement dans les cellules d’une feuille de calcul. Vous pouvez créer et enregistrer différents groupes de valeurs dans une feuille de calcul, puis passer d’un Scénario à l’autre pour visualiser les différents résultats.

Les scénarii vous permettent de :

Prédire le résultat d'un modèle de feuille de calcul.

Obtenir différents groupes de valeurs dans une feuille de calcul et les sauvegarder.

Afficher les différents résultats de ces nouveaux scénarii.

3.1 Créer des scénarii

Si vous voulez par exemple établir un budget alors que votre chiffre d'affaires est incertain, vous pouvez définir différents chiffres d'affaires puis prendre une décision après examen des différents scénarii.

Exemple :

Vous souhaitez établir un budget, mais ne savez pas quel est l'impact budgétaire des dépenses. Dans ce cas, vous pouvez définir des valeurs différentes pour les dépenses et ensuite basculer entre les différents scénarii afin d'obtenir une vue d'ensemble des résultats possibles.

Dans cet exemple nous planifions un budget.


Certaines dépenses peuvent être différentes d'un mois à l'autre.


Pour obtenir ceci sous forme de scénario :

Commencez par nommer les cellules variables (par exemple, ici, nommez la cellule B3 Mortgage)

Ouvrez le bouton Analyse Scénarios (What If Analyse) dans le groupe Outils de données (Data Tools) de l'onglet Données (Data).

Sélectionnez Gestionnaire de Scénarios (Scenario Manager).

Cliquez sur Ajouter (Add) et nommez le Scénario

Sélectionnez Cellules variables (Changing Cells) afin de déterminer les cellules que vous souhaitez personnaliser dans vos différents Scénarii.

Introduisez éventuellement des commentaires dans le champ de commentaires afin de fournir des informations utiles et spécifiques à ce scénario.


Une deuxième fenêtre apparaît donnant la possibilité de mettre à jour les différentes cellules. Nous vous conseillons de conserver un scénario avec les valeurs de cellules d'origine.

Cliquez sur le bouton Ajouter (Add) afin de créer les scénarii suivants.


Confirmer les valeurs en cliquant sur OK.

Le nouveau Scénario apparaît dans la liste de gestion de Scénarii.

3.2 Afficher un Scénario

dans la fenêtre Gestionnaire de scénario (Scenario Manager). Sélectionnez le nom d'un Scénario et cliquez sur le bouton Afficher (Show).

Ajoutez un menu déroulant afin de basculer rapidement entre les différents Scénarii.

Vous obtenez le résultat suivant :


Créez un rapport pour comparer les différents Scénarii. Vous pouvez opter pour un résumé ou pour un rapport de Tableaux croisés dynamiques.

Dans la fenêtre Gestionnaire de scénario (Scenario Manager) cliquez sur le bouton Synthèse (Summary).

Indiquez l'adresse des cellules résultantes pour lesquelles vous souhaitez comparer vos variations.

Excel crée alors une feuille de calcul séparée comportant un rapport de synthèse incluant les différentes valeurs des cellules variables ainsi que leur impact sur les cellules résultantes


1 = Ajout d'un commentaire

2 = Nom de cellules (plus significatif que des références cellule)

3 = Les résultats des différents scénarii

Dans le cas où vous choisiriez un rapport de tableaux croisés dynamiques le résultat ressemblera à ceci :

30 Formules matricielles | Excel 2010.

4 Formules matricielles

4.1 Détail des formules matricielles

La façon d'introduire une formule matricielle est totalement différente de celle que vous utilisez pour entrer des formules ordinaires.

Les formules matricielles ne s'insèrent pas en utilisant un simple [Enter] (voire [Ctrl] + [Enter] pour insérer des données dans la plage sélectionnée), mais en utilisant la combinaison de touche [Ctrl] + [Maj (Shift)] + [Enter].

Si vous terminez l'introduction de votre formule par cette combinaison de touches, Excel l'interprète comme étant une formule matricielle et ajoute des accolades autour de celle-ci.

Le simple ajout d'accolades à une formule de donne pas pour autant le résultat souhaité. Excel interprète la formule comme étant une valeur texte. Si le but est que la formule matricielle donne des résultats émanant de différentes cellules, sélectionnez ces cellules avant d'introduire la formule.

Les trois raisons fondamentales pour lesquels vous utiliseriez une formule matricielle sont les suivantes :

vous utilisez une fonction qui nécessite l'emploi d'une formule matricielle en Excel.

Vous introduisez une formule dont les composantes doivent faire partie d'une formule matricielle.

Vous introduisez une formule dans laquelle les résultats incluent plus d'une cellule.

4.1.1 Manipulation des formules matricielles

Les règles suivantes s'appliquent aux formules matricielles qui s'étendent sur plusieurs cellules :

La totalité des cellules contenant la formule n'est pas modifiée lorsque vous terminez votre entrée par la touche [Enter]. Toutes les cellules doivent être modifiées simultanément.

Si vous sélectionnez une cellule et que terminez d'en modifier le contenu via [Ctrl] + [Shift] + [Enter], toutes les cellules équipées de cette formule sont modifiées.

Vous ne pouvez ni déplacer ni supprimer une partie des cellules incluses dans la formule matricielle.

4.1.2 Les sommes conditionnelles

Une application fréquente de formule matricielle est l'établissement de calculs conditionnels. Vous souhaitez par exemple récupérer et additionner les ventes de Brown.

=SOMME(SI(A2:A15="Brown";B2:B15;0))

=SUM(IF(A2:A15="Brown";B2:B15;0))


Pour comprendre comment fonctionne une formule complexe, Excel vous offre deux manières d'afficher sa construction

4.1.2.1 Première manière

Sélectionnez l'onglet Formules (Formulas) et cliquez sur la commande Audit de Formule (Evaluate Formula) dans le groupe Audit de formules (Formule Auditing).

Étape 1

Cliquez sur le bouton Évaluer (Evaluate) pour comprendre ce que signifie l'expression Sales="Brown". Le résultat est une matrice avec des valeurs VRAI et FAUX :


Étape 2

Étape 3

Étape 4

La fonction SI renvoie son deuxième argument s'il s'avère que le résultat du test logique est VRAI.

Étape 5

Dans ce cas-ci la fonction additionne la première et la septième valeur dans la plage soit 1 et 60. Ces valeurs sont comprises de entre 9 valeurs, dont 7 sont nulles, la fonction SI renvoie la valeur VRAI car la totalité de ses valeurs ne sont pas "FAUX".

Étape 6

La fonction SOMME retourne alors le total, soit 61.

4.1.2.2 Deuxième manière

Double-cliquez sur la cellule qui contient la formule et sélectionnez avec le pointeur de la souris la partie de la formule que vous souhaitez évaluer. Appuyez alors sur la touche [F9] de votre clavier afin d'évaluer le composant sélectionné. Cette méthode traite la formule. Pour annuler l'opération appuyez sur la touche [Esc] de votre clavier sinon, le résultat de l'évaluation remplacera la partie évaluée de la formule.


4.2 Quand est-il nécessaire d'introduire une matrice ?

4.2.1 Quand les fonctions nécessitent-elles une formule matricielle ?

4.2.1.1 Transposer

Supposons que vous soyez en présence d'une plage de données composées d'une ou de plusieurs colonnes et souhaitiez les voir sous forme de rangées. Vous pouvez résoudre ce problème en faisant un Copier-Collage spécial puis en cochant Transposer. Cette méthode n'est certainement pas aussi dynamique que la fonction matricielle Transpose.

Sélectionnez la plage (ici, E12:M13) vers laquelle vous souhaitez transposer les cellules (A1:B10). Si vous ne sélectionniez que la cellule E12, vous n'obtiendrez que le contenu de la cellule $A$1.

=TRANSPOSE(A1:F10) =TRANSPOSE(A1:F10)

N'oubliez pas de terminer votre formule par la combinaison de touches [Ctrl]+[Maj (Shift)]+[Enter]

4.2.1.2 FREQUENCE (FREQUENCY)

Calcule la fréquence d’apparition des valeurs dans une plage de valeurs, puis renvoie des nombres sous forme de matrice verticale. Par exemple, utilisez la fonction FREQUENCE (FREQUENCY) pour déterminer combien de résultats d’un test entrent dans une plage de résultats donnée. Dans la mesure où la fonction FREQUENCE (FREQUENCY) renvoie une matrice, elle doit être tapée sous forme de formule matricielle

Dans l'exemple ci-dessous, la fonction FREQUENCE (FREQUENCY) utilise la plage D3:D7 comme plage de valeurs. Elle définit les frontières d'une série de catégories. La fonction FREQUENCE (FREQUENCY)


renvoie le nombre d'occurrences de chaque valeur dans chaque catégorie. Étant donné que cinq catégories sont définies dans cinq rangées de la matrice intervalle, commencez par sélectionner une plage de cinq cellules (donc une par catégorie). Celles-ci contiendront les valeurs retournées par la matrice utilisant la fonction FREQUENCE (FREQUENCY). Dans l'exemple ci-dessous il s'agit de la plage G5:G9. Terminez l'introduction de la formule par la combinaison de touches [Ctrl] + [Maj(Shift)] + [Enter]. La fonction renvoie les cinq valeurs différentes dans la plage G5:G9. Si vous aviez utilisé uniquement la touche [Enter], vous auriez obtenu le même résultat dans chacune des cinq cellules.

=FREQUENCY (_1970;Klassen)

4.2.1.3 Divers

D'autres fonctions doivent être introduites sous forme de matrice: DROITEREG (LINEST), LOGREG (LOGEST), TENDANCE (TREND), CROISSANCE (GROWTH), INVERSEMAT (MINVERSE) et PRODUITMAT (MMULT). Avant d'utiliser ces fonctions, sélectionnez la plage de cellules de la feuille de calcul dans laquelle les résultats seront affichés.

4.2.2 Formules qui font appel à plusieurs cellules

Chaque fois que vous souhaitez construire une formule qui a fait appel directement à une plage de cellules, et ne souhaitez pas que le résultat de la formule soit dépendant de l'endroit de la plage de cellules sources, utilisez une formule matricielle.

L'illustration ci-dessous présente un type de tableau que l'on retrouve souvent dans une feuille de calcul : la matrice de base (B1:F1 et B2:F2) qui doit être multipliées pour donner une matrice résultante (ici en D4:H4). La matrice résultante doit comprendre plusieurs cellules organisées de la même manière que les matrices sources.

=$B$1:$F$1*$B$2:$F$2

35 Mise en forme conditionnelle avancée faisant appel à des formules | Excel 2010.

La formule dans la plage D4:H4 ne contient aucune fonction, elle contient uniquement des plages de cellules et des opérations arithmétiques. La formule est établie de façon matricielle.

En D6:H6, la formule ne fonctionne pas pour les cellules G6:H6 parce qu'elles sont en dehors des colonnes B:F. Elle retourne donc des valeurs erronées. Pour éviter cela, introduisez les formules en tant que matrice.

5 Mise en forme conditionnelle avancée faisant appel à des formules

5.1 Détection de doublons

Afin de mettre en évidence les valeurs qui sont répétées plus d'une fois dans une colonne, utilisez la mise en forme conditionnelle suivante :

=NB.SI(A1:A19;A1)>1

=COUNTIF(A1:A19,A1)>1

Cette formule peut être modifiée pour détecter des doublons dans une plage de grande dimension, par exemple dans la plage B5:E15. La formule doit alors ressembler à ceci :

=NB.SI($B$5:$E$15;B5)>1

=COUNTIF($B$5:$E$15,B5)>1


5.2 Masquer des résultats de formules retournant un message d'erreur

Plusieurs formules peuvent retourner un message d'erreur (par exemple #N/A) le temps que l'intégralité des données nécessaires au traitement soient disponibles. La mise en forme conditionnelle permet de masquer ces messages d'erreur, en appliquant la même couleur d'arrière-plan et de texte à la cellule lorsque le résultat de la formule suivante renvoie le résultat VRAI :

=ESTERREUR(A1)

=ISERROR(A1)

5.3 Marquer les valeurs présentes dans une plage

Cette formule modifie la mise en forme des valeurs rencontrées dans une plage pour les mettre en évidence. Ceci peut être utilisé par exemple pour contrôler les résultats du Lotto.

=EQUIV(B6;LottoNumbers;0)

=MATCH(B6 LottoNumbers;0)


Ou bien encore :

=COUNTIF(LottoNumbers;B16)

=NB.SI(LottoNumbers;B16)

5.3.1 Mettre les week-ends et les jours fériés en évidence

Dans un calendrier il serait pratique de mettre les jours fériés et les week-ends en évidence pour avoir une meilleure vue d’ensemble.


1.1.1.1 Mettre les week-ends en évidence :

=JOURSEM(A1;2)>5

=WEEKDAY(A1,2)>5

1.1.1.2 Mettre les week-ends et les jours fériés en évidence :

Condition 1

=EQUIV(C1;Hollidays;0)

=MATCH(C1;Hollidays;0)

Cette formule permet d'utiliser une plage de cellule dans laquelle vous indiquez les jours fériés (éventuellement dans une autre feuille de calcul) pour les mettre en évidence dans le calendrier.


Condition 2

=JOURSEM(C1;2)>5

=WEEKDAY(C1;2)>5

1.1.1.3 Mettre en évidence la même manière les week-ends et les jours fériés :

=OU(JOURSEM(E1;2)>5;NON(ESTERREUR(EQUIV(E1;Hollidays;0)))))

=OR(WEEKDAY(E1;2)>5;NOT(ISERROR(MATCH(E1;Hollidays;0)))))


41 Protéger une feuille de calcul | Excel 2010.

6 Protéger une feuille de calcul

Protégez une feuille de calcul pour empêcher d'autres utilisateurs de cette feuille de calcul de supprimer ou de modifier son contenu. Cette protection peut être appliquée tant sur les feuille de calcul que sur les feuilles de graphique.

6.1 Protection de cellules

Par défaut, Excel protège l'intégralité de la feuille de calcul. Si vous souhaitez que certaines cellules restent modifiables après protection, il vous faut les indiquer au préalable.

Indiquer qu'une cellule ne doit pas être protégée :

Sélectionnez les cellules concernées

Cliquez avec le bouton droit de la souris sur les cellules sélectionnées et choisissez Format de cellules (Format Cells)

Sélectionnez l'onglet Protection (Protection)

Décochez la case Verrouillée (Locked)

La protection des cellules n'est active que lorsque la feuille de calcul est protégée.

6.2 Activer la protection

Sélectionnez la feuille à protéger.


Cliquez sur la commande Protéger la feuille (Protect Sheet) dans le groupe Modification (Changes) de l'onglet “Révision” (Review).

Choisissez un mot de passe de qualité (ce choix n'est pas obligatoire)

Cochez dans la liste les manipulations que vous souhaitez autoriser.

6.3 Ôter la protection de la feuille

Sélectionnez la feuille que vous souhaitez déprotéger.

Cliquez sur la commande « Ôter la protection » (Unprotect Sheet) dans le groupe Modification (Changes) de l'onglet Révision (Review).

Entrez le mot de passe si vous en avez indiqué un.

Cliquez sur OK.

6.4 Protection d'un classeur

Il existe plusieurs manières de protéger les modifications ou les vues d'un classeur.

Cliquez sur la commande « Protéger le classeur » (Protect Workbook) dans le groupe Modification (Changes) de l'onglet Révision (Review)


6.4.1 Protéger la structure

Protégez la structure de votre classeur pour empêcher d'autres utilisateurs de supprimer, copier, déplacer, masquer ou ajouter des feuilles de calcul (il sera également impossible d'ajouter un graphique dans une nouvelle feuille).

6.4.2 Protéger une fenêtre

Protégez la fenêtre pour empêcher d'autres utilisateurs de modifier, déplacer ou masquer une fenêtre. Ceci est intéressant dans le cas où vous souhaitez que chaque fenêtre apparaisse toujours de la même manière.

La protection de la fenêtre ôte les boutons réduire et restaurer ainsi que le symbole de redimensionnement.

6.5 Masquer des données, des feuilles et des formules

Le fait de masquer des données et des feuilles diminue le nombre de fenêtres présentes sur votre écran et prévient des changements non souhaités.

6.5.1 Masquer la totalité d'un classeur

Cliquez sur la commande Masquer (Hide) dans le groupe Fenêtre (Window) de l'onglet Affichage (View).

6.5.2 Afficher à nouveau un classeur masqué

Cliquez sur la commande Afficher (Unhide) dans le groupe Fenêtre (Window) de l'onglet Affichage (View).

Sélectionnez le classeur souhaité dans la boîte de dialogue.

Cliquez sur OK ou appuyez sur la touche [ENTER] de votre clavier.


6.5.3 Masquer une feuille

Cliquez sur la commande Format (Format) dans le groupe Cellules (Cells) de l'onglet Accueil (Home).

Il n'est pas possible de masquer toutes les feuilles d'un classeur, la dernière feuille ne pourra pas être masquée.

6.5.4 Afficher une feuille masquée

Cliquez sur la commande Format (Format) dans le groupe Cellules (Cells) de l'onglet Accueil (Home).


Choisissez l'option Afficher.

Sélectionnez la feuille souhaitée dans la fenêtre qui apparaît.

Cliquez sur OK ou appuyez sur la touche [Enter] de votre clavier.

6.5.5 Masquer une formule

Si vous souhaitez masquer une formule dans la barre de formule.

Sélectionnez la cellule ou la plage de cellules dont vous souhaitez masquer la formule.

Cliquez avec le bouton droit de la souris sur la sélection et choisissez la commande Format de Cellule (Format Cells) dans le menu local.

Cliquez sur l'onglet Protection (Protection).


Cochez la case Masquée (Hidden)

Cliquez sur OK ou appuyez sur la touche [Enter] de votre clavier

Remarque : cette protection ne prend cours que lorsque la protection de la feuille est activée.

47 Modification | Excel 2010.

7 Modification

7.1 Conserver les modifications

Partagez le classeur si plusieurs utilisateurs doivent pouvoir travailler en même temps sur une copie de ce classeur.

Cliquez sur la commande Partager le classeur (Share Workbook) dans le groupe Modifications (Changes) de l'onglet Révision (Review).

Un classeur partagé permet à plusieurs utilisateurs de modifier simultanément des valeurs ou la mise en forme d'éléments du classeur. Il vaut mieux conserver la trace des changements apportés par des réviseurs potentiels à un classeur partagé enregistré sur un disque réseau.

Activer la commande Suivi des modifications (Track Changes) pour un classeur non partagé, partage celui-ci et active le suivi des modifications.

Cliquez sur la commande Suivi des modifications (Track Changes) dans le groupe Suivi (Changes) de l'onglet Révision (Review).


Le partage n'est pas aussi souple que celui d'un fichier Word. En effet, Excel restreint fortement les actions des utilisateurs :

Pas d'application

Cellules

Fusion de cellules Vous pouvez examiner les cellules qui sont fusionnées avant de partager le classeur.

Ajout ou suppression d'un ensemble de cellules

Vous pouvez ajouter ou supprimer un ensemble de colonnes et de lignes

Feuille de calcul Supprimer une feuille de calcul

Mise en forme conditionnelle et validation de données

Ajout ou modification de mise en forme conditionnelle

Vous pouvez constater l'effet de la mise en forme conditionnelle que vous avez utilisée.

Restrictions au changement de règles de validation de données

Vous pouvez voir l'effet des restrictions et des messages d'avertissement émanant des nouvelles règles de validation choisies.

Objets, graphiques, formes et hyperliens

Ajout ou modification d'objets, de graphiques, de formes et d'hyperliens.

Dessins Utilisation des fonctions de dessin

Mots de passe

Attribuer un mot de passe à une feuille de calcul indépendante ou un ensemble d'un classeur aux fins de le protéger

Les règles de protection du classeur avant le partage celui-ci restent d'actualité après le partage.

Modifications ou suppressions de mots de passe

L'attribution de mots de passe avant le partage du classeur reste d’application après le partage celui-ci.

Scenarios La sauvegarde, la visualisation ou le changement de scénario

mode plan, groupes et sous totaux

Grouper les données ou les mettre sous forme de plan.

Ajout de sous totaux automatiques

Mise sous forme de tableau et tableau croisé dynamique

Mettre sous forme de tableau Insertion de tableaux croisés dynamiques ou modifications d'aspects de ceux-ci


Macros

Écriture enregistrement, examen, modification ou application de macros

Dans un classeur partagé vous pouvez appliquer des macros qui ont été faites avant que le classeur ne soit partagé. Par contre si une partie de la macro ne fonctionne pas parce que certains éléments ne sont pas disponibles, la macro est arrêtée

Les cellules qui sont modifiées, ajoutées ou supprimées sont automatiquement mises en forme dans une autre couleur.

Après que le classeur ait été sauvé vous pourrez examiner chaque changement en sélectionnant chaque cellule colorée. Un commentaire apparaît vous indiquant qui a fait le changement et les détails de ce changement.

7.2 Aperçu général des changements dans une feuille de calcul séparée

Cliquez sur la commande Suivi des modifications (Track Changes) dans le groupe Modifications (Changes) de l'onglet Révision (Review).

Activez Suivi des modifications dans une nouvelle feuille de calcul (List changes on a new sheet) pour obtenir une vue générale de tous les changements qui ont été effectués dans la feuille de calcul


7.3 Accepter et refuser des changements

Cliquez sur la commande Suivi des modifications (Track Changes) dans le groupe Modifications (Changes) de l'onglet Révision (Review). Choisissez Accepter ou Refuser les modifications. (Accept/Reject Changes)

Une boîte de dialogue apparaît

Excel passe tous les changements en revue via la boîte de dialogue et indique la cellule concernée sur la feuille de calcul.

7.4 Accepter, ignorer les changements conflictuels

Accepter ou refuser les changements conflictuels lors de la sauvegarde d'un classeur partagé



Voici comment sauvegarder un classeur partagé lorsque la boîte de dialogue illustrée ci-dessus apparaît :.

Lisez les informations conflictuelles concernant les changements exécutés par d'autres utilisateurs.

Sélectionnez le changement souhaité

Cliquez sur Accepter (Accept) si vous souhaitez conserver ce changement.

Cliquez sur Refuser (Reject) si vous ne souhaitez pas conserver le changement.

Répétez les étapes ci-dessus pour chaque modification.

Cliquez sur Accepter tout (Accept All) si vous voulez conserver toutes les modifications.

Cliquez sur Refuser tout (Reject All) si vous voulez refuser toutes les modifications apportées par les autres utilisateurs.

7.5 Cesser de partager un classeur

En arrêtant le partage d'un classeur, vous retrouvez toutes les fonctions et possibilités d'Excel. Le traçage des modifications n'est dès lors plus disponible.

Cliquez sur la commande Partager le classeur (Share Workbook) dans le groupe Modifications (Changes) de l'onglet Révision (Review).

Décocher la case “Autoriser les changements par plus…”("Allow changers by more…")

Documents

Macro - Syllabi 2010-Mod X Expert FR-UK.pdf · 4.2.1 Quand les fonctions nécessitent-elles une formule matricielle ? ... une feuille de calcul ... de la galerie de commandes rassemblées