Tableur EXCEL/Version imprimable

Tableur EXCEL

Une version à jour et éditable de ce livre est disponible sur la Wikiversité,
une bibliothèque de livres pédagogiques, à l'URL :
http://fr.wikiversity.org/wiki/Tableur_EXCEL

Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la Licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans Texte de dernière page de couverture. Une copie de cette licence est inclue dans l'annexe nommée « Licence de documentation libre GNU ».

Introduction

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Définitions

Excel

• Classeur : un classeur est un fichier dans lequel l'utilisateur stocke des données essentiellement numériques en vue de calcul ou d'affichage graphique. Chaque classeur peut contenir de nombreuses feuilles (de calculs ou graphiques) sélectionnable via des onglets (situés dans la barre d'état), il est ainsi possible d'organiser différentes sortes d'informations au sein d'un même fichier.
• Feuille de calcul : chaque feuille de calcul permet de saisir, contrôler, répertorier et analyser des données de même nature. Ces données sont contenues dans les cellules de la feuille, ces données pouvant être textuelles, numériques, fonctionnelles, ...
• Lignes et colonnes d’une feuille de calcul : chaque feuille de calcul peut contenir jusqu'à 1 048 576 lignes x 16 384 colonnes, soit plus de 16 milliards d'informations.
• Cellule : zone de la feuille de calcul correspondant à l'intersection d'une ligne et d'une colonne, constituée du nom de la colonne suivi du numéro de ligne => Exemple : A3 (cellule de la colonne A et de la ligne 3) . Une cellule peut contenir jusqu’à 32 767 caractères, que ces caractères soient numériques ou textuels.
• Plage de cellules : ensemble contigu ou non de cellules. C’est une zone de la feuille de calcul qui contient des données et des formules. Une plage contiguë est identifiée par les adresses des cellules formant les angles supérieur gauche et inférieur droit séparés par deux points. Exemple 1 => C1:E3 (cellules des colonnes C,D,E et des lignes 1,2,3). Exemple 2 => C1:E3, D5:F7 (cellules des colonnes C,D,E sur les lignes 1,2,3 et cellules des colonnes D,E,F sur les lignes 5,6,7 ).
• Nom de cellules : zone de la feuille de calcul nommée, toute plage ou toute cellule peut être nommée, ce nom pourra être utilisé dans tout calcul ultérieur. Exemple => Nommer la cellule Z11 (contenant la valeur 1000) HT, nommer la cellule Z12 (contenant la valeur 0,196) TauxTVA et calculer une TVA dans la cellule Z13 par la formule (=HT*TauxTVA).

Conteneurs et contenus

• Un Classeur possède un nom de fichier et contient une infinité de feuilles (la 3^e dimension des tableurs)
• Une Feuille de calcul possède un nom de feuille et contient des cellules (éventuellement nommées et éventuellement regroupées en plages)
• Une Plage (A1:C4) possède un nom de plage (si elle est nommée) et peut être composée d'un ensemble de cellules (disjointes ou non)
• Une Cellule (B3 intersection de la ligne 3 avec la colonne B) possède un nom de cellule (si elle est nommée) et gère une adresse et un contenu lui-même contenant une valeur et éventuellement une formule de calcul

Chacun des objets susnommés peut être identifié par un identifiant unique.

Environnement

Office-ms-excel

• Le lancement Excel (sous Windows) : Onglet Démarrer/Programmes, ou Double clic sur un Raccourci Excel ou Double clic sur un document Excel reconnu (soit par son extension, soit par son icône)

Microsoft Office 2013 logo

• Le Bouton OFFICE (Onglet Fichier à partir de 2010) : nouveau, ouvrir, enregistrer, imprimer, préparer, envoyer, publier, …, fermer (ou bouton de fermeture fenêtre), quitter, …
• La Barre d’outils rapides : permet un accès rapide aux actions
  • en standard : enregistrer, faire, défaire, enregistrer
  • à ajouter personnellement : aperçu, orthographe, trier, …
• Fenêtre principale : fenêtre container, elle comprend non seulement les sous-fenêtres sous forme de feuilles de cellules mais aussi toutes les barres d'utilisation du logiciel, chaque feuille est affichée via un onglet
• Les onglets d’outils : Accueil, Insertion, Mise en page, Formules, Données, Révision, Affichage, ... Ces onglets sont activés directement lors de l’utilisation de certaines fonctions. D'autres onglets ne sont accessibles que par paramétrage (Développeur, ...) ou par contexte d'utilisation (Outils de dessin, ...). Les onglets possèdent des sous-fenêtres en forme de barre horizontale composée de boutons, de champs textes et de listes déroulantes permettant un accès rapide à la quasi totalité des outils du tableur.
• La Barre d'état : sous-fenêtre en forme de barre horizontale composée de libellés et de champs textes indiquant les résultats de calculs (fonctions somme, moyenne, …), les modes d’affichage (Normal, Mise en page, Aperçu), les zooms et les messages furtifs.

Onglets

Excel-icon

• Fichier (ou Bouton OFFICE) : gère fichiers et paramètres (nouveau, ouvrir, enregistrer, imprimer, préparer, envoyer, publier, …, fermer, quitter)
• Accueil : Onglet principal qui gère la plupart des actions fréquentes

Cet onglet possède 7 sous onglets : presse-papiers, police, alignement, nombre, styles, cellules et édition.

• Insertion : Onglet fréquemment utilisé pour insérer tout type d'objet, qu’ils soient internes ou externes

Cet onglet possède 9 sous onglets : tableaux (simples et croisé dynamiques), illustrations, compléments, graphiques, graphiques sparkline, filtres (segment, chronologie), liens, texte et symboles.

• Mise en page : Onglet fréquemment utilisé pour affiner la présentation des feuilles avant impression

Cet onglet possède 5 sous onglets : thèmes, mise en page, mise à l'échelle, options de la feuille de calcul et organiser.

• Formules : Onglet fréquemment utilisé pour insérer toute fonction ou calcul

Cet onglet possède 4 sous onglets : bibliothèque de fonctions, noms définis, vérification des formules et calcul.

• Données : Onglet utilisé pour organiser, filtrer, contrôler les données affichées et saisies

Cet onglet possède 5 sous onglets : données externes, connexions, trier et filtrer, outils de données et plan.

• Révision : Onglet utilisé pour commenter, protéger, corriger les cellules affichées

Cet onglet possède 4 sous onglets : vérification, langue, commentaires et modifications.

• Affichage : Onglet utilisé pour affiner la présentation des feuilles à l’écran

Cet onglet possède 5 sous onglets : mode d'affichage, afficher, zoom, fenêtre et macros.

• Foxit reader PDF : Onglet utilisé pour convertir un classeur en PDF

Cet onglet possède 3 sous onglets : créer un PDF, paramètres généraux et informations connectées.

Accueil

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Le copier, le couper et le coller

capture d'écran Excel

• Copier : permet d'enregistrer dans une mémoire de l'ordinateur appelée le presse-papier les cellules, les textes, les objets sélectionnés (images, équations, …)
• Couper : permet de la même manière d'enregistrer dans une mémoire de l'ordinateur appelée le presse-papier la sélection, tout en effaçant cette même sélection
• Coller: permet d'insérer à un endroit désigné par l'utilisateur (en général grâce à la position du pointeur) le contenu de la mémoire de l'ordinateur appelée le presse-papier.

Les options de collage :

• Coller
• Coller les valeurs (uniquement)
• Coller les formules
• Coller transposer
• Coller mise en forme
• Coller les liaisons (le changement initial de la cellule change également au niveau du collage)

La mise en forme

EXCEL vous permet dans l'onglet Accueil de mettre en forme les cellules de tous vos tableaux avec une palette d’outils complète exposée sur le ruban :

• Paramètres de police (style d’écriture de caractères que l’on peut appliquer aux caractères) :
• Les effets : gras, italique, souligner, double souligner
• Les bordures : chaque cellule peut avoir un fond, un motif, être encadrée
• Les polices peuvent être également modifiées via "les attributs" : barré, exposant et indice

Le Pinceau brosse permet la reproduction de toute mise en forme de cellule. Elle permet en 3 clics d'appliquer le style d'une cellule à une autre cellule (ou une autre plage).

L'alignement

EXCEL vous permet dans l'onglet Accueil de gérer l’affichage interne de chaque cellule

• L’alignement : un alignement (horizontal ou vertical) permet de préciser la façon dont est affiché le contenu de la cellule (gauche, droite, centre, haut, centre, bas), l'alignement peut aussi être oblique ou indenté
• Le renvoi ou l'ajustement : un texte un peu trop long peut être soit affiché sur plusieurs lignes de la même cellule, soit ajusté à la taille de la cellule
• La fusion : une fusion de cellules permet de regrouper une plage de cellule sur la même cellule (exemple A1:A4 sur A1)

Le type et le format d'affichage

Les données du tableurs sont :

• des textes (mélange de caractères alphabétiques, de nombres de signes), les chaînes de caractères sont alignés à gauche dans une cellule
• des valeurs booléennes (VRAI, FAUX)
• des valeurs d'erreur (#...)
• des dates et des heures (jj/mm/aaaa hh:mm ...) sont considérées comme des nombres bénéficiant d'un formatage spécial
• des nombres, les nombres peuvent être décrits avec des chiffres et des caractères spéciaux [ + - ( ) , $ % . E e ] , la virgule détermine le séparateur décimal (en environnement français), les nombres négatifs doivent être précédés par un – ou être mis entre parenthèses. Un format spécial précise les différents affichages
  • entiers : nombres bruts, pourcentages
  • décimaux : fractions, notations scientifiques
  • monétaires : représentations en différentes monnaies

L'affichage conditionnel

L'affichage conditionnel permet de souligner visuellement et de manière dynamique certaines valeurs de cellules remarquables. Il se programme via un assistant en 4 phases :

• Une sélection de cellules
• Un type de condition sur la les cellules sélectionnées (mathématique ou statistique ou autre)
• Une ou plusieurs conditions à définir, la condition s'applique soit à une formule, soit à une valeur, soit à une couleur de cellule
• Un format d'affichage à appliquer quand une condition est atteinte, le format s'applique soit aux propriétés de police (taille, couleur, type, …), de bordure ou de motif, soit directement à un complément iconographique des cellules

L'affichage tableau

La mise sous forme de tableau permet d'enjoliver visuellement et de manière dynamique une plage de lignes et de colonnes sélectionnée. Les lignes et colonnes de la plage sont obligatoirement jointes. Elle se programme via un assistant en 3 phases :

• Une sélection de cellules
• Un choix de type de tableau : un style de tableau permet d’afficher le tableau avec "look and feel" concoctée par les graphistes de l'éditeur du logiciel
• Une confirmation des cellules adjacentes et d'une présence de ligne entête

La plage du tableau est nommée par EXCEL (attention à la manipulation de ces noms dans les formules) et l'outil de filtrage et de tri s'affiche sur la ligne entête.

Les outils pour les cellules

• Outil insérer : cet outil permet d’insérer cellules, lignes ou colonnes à partir d’une position donnée
• Outil supprimer : cet outil permet de supprimer cellules, lignes ou colonnes à partir d’une position donnée
• Outil format : cet outil permet de formater les cellules, d’ajuster les lignes et les colonnes, …

La recherche

• Outil rechercher : rapide, précise et efficace, la recherche de mots ou d’expressions dans une feuille entière, voire un classeur est très simple, ne pas hésiter à l’utiliser. La recherche peut s'effectuer sur des textes, des nombres, des cellules, des formules, des couleurs, …
• Outil remplacer : La recherche peut être complétée par une chaine de remplacement, ce remplacement pouvant se faire de façon unitaire ou global.
• Outil atteindre : rapide, précise et efficace, cette fonction recherche des noms ou des cellules directement par leur position et non par leur contenu.

Insertion

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Caractères spéciaux

On insère les caractères spéciaux via l'Onglet Insertion\Symbole

• Caractères spéciaux : symboles permettant d’agrémenter les documents de caractères issus de polices spéciales peu courantes, telles que les polices Wingdings.
• Insertion des Caractères spéciaux : après avoir actionné l’onglet Insertion l'utilisateur peut choisir une des polices et insérer un ou plusieurs des symboles de ces polices. Exemples de polices : Symbol, Webdings, Wingdings, Gautami, Music ...
• Suppression des Caractères spéciaux : le caractère spécial peut comme tout caractère être supprimé par les touches Suppr et Del ainsi que par l'action Couper

Équations

On insère une équation via l'Onglet Insertion\Équation

• Équations : caractères spéciaux avec formalisme mathématique. Exemples de caractères : Ω, π, β, =, <, √, ...
• Insertion des Caractères spéciaux: après avoir actionné l’onglet Insertion l'utilisateur peut insérer une formule mathématique et l'adapter par traitement de texte à son besoin
• Suppression des équations : l’équation peut comme toute boite de texte être supprimée par les touches Suppr et Del ainsi que par l'action Couper

Images

On insère une image via l'Onglet Insertion\Image

• Image : c’est un élément géré par une autre application, inséré directement dans l’application Microsoft Excel (Clipart, Wordart, Smartart, Dessin, Graphique, Photo, …)
• Modification de l'image : Excel démarre l’application gérant le type d'image sélectionné et permet les modifications via cette application
• Modification du cadre de l'image : Excel permet des manipulations du cadre, ce qui permet de modifier la taille d’une image, encadrer une image, découper une image, habiller une image, ...
• Rotation : c’ est une poignée spécifique qui permet de modifier l’affichage de tout objet (donc aussi une image ou texte) par rotation ou retournement (notion de pivot, d'orientation...)
• Suppression : Touche DEL ou Touche SUPPR ou Menu Edition\Couper

Dessins

On insère un dessin via l'Onglet Insertion\Dessin, cette interface permet de créer et manipuler les dessins effectués sur une feuille Excel, elle comprend un menu dessin, un menu formes, des boutons et des listes de valeurs à activer

• Objets : les objets manipulables sont les formes automatiques, les traits, les flèches, les rectangles, les cercles, les zones de texte, les objets WORDART, les images CLIPART, les autres formes. Chaque objet est inséré dans une zone spéciale manipulable elle aussi par des poignées (déplacer + , agrandir ↔ , réduire ↔ , ...).
• Ordre : c’ est une option qui permet de prioriser l’affichage d'objet ou de texte par rapport à d'autres (notion de premier plan, d'arrière plan, ...)
• Grouper : c’est une méthode de sélection qui permet de regrouper les différents objets d'un dessin afin de les déplacer ou de leur appliquer des propriétés et des méthodes (l'ensemble d'objets ne faisant plus qu'un).
• Dissocier : c’est une méthode de sélection qui permet de dégrouper les différents objets d'un dessin afin de les déplacer ou de leur appliquer des propriétés et des méthodes de manière individuelle.
• Suppression : Touche DEL ou Touche SUPPR ou Menu Edition\Couper

Sparklines

On insère un sparkline via l'Onglet Insertion\Graphique sparkline (nouveauté de Microsoft Excel 2010), c’est un graphique minuscule inséré dans une cellule de la feuille de calcul qui affiche une représentation visuelle des données. Ce type de graphique permet d’afficher les tendances et les valeurs remarquables d’une série de valeurs.

• Sélection : on sélectionne une ou plusieurs cellules cibles, puis un type de graphique sparkline via l’onglet Insertion (Courbes, Histogramme, Positif/Négatif) puis éventuellement des points spécifiques à mettre en valeur (premier, dernier, minimum, maximum, ...)
• Suppression : on sélectionne les cellules avec graphiques sparklines puis on les supprime via menu contextuel (clic droit)

Création de Graphiques

On insère un graphique via l'Onglet Insertion\Graphique, une barre d’outil graphique apparaît automatiquement lors de la création d’un graphique, l'assistant graphique propose une incorporation (zone graphique inclue dans la feuille de données du graphique) ou non (feuille graphique spécifique Excel qui ne peut contenir que des graphiques).

• Sélection : c’est grâce à une sélection cohérente que le graphique construit par EXCEL est lui-même cohérent, toute sélection hasardeuse crée un graphique au look and feel inattendu ...
• Options modifiables
  • Dimensions : 2D ==> se dit d’un graphique a 2 dimensions: longueur, largeur (abscisse et ordonnée), 3D ==> c’est un graphique 2D qui a en plus la notion de profondeur
  • Axes : axe des abscisses ==> c’est l’axe représenté horizontalement, il a un intitulé et une graduation, axe des ordonnées ==> c’est l’axe représenté verticalement, il a un intitulé et une graduation, axe des Séries ==> c’est l’axe représenté obliquement, il a un intitulé et une graduation
  • Séries de données : un groupe de valeurs numériques de même nature disposées les unes à la suite des autres dans une plage d’une feuille de calcul
  • Légendes et titres : expliquent ce que représente chaque partie du graphique et donne l’intitulé général de celui-ci

Gestion de Graphiques

Tout élément de graphique est modifiable (zone de graphique, type de graphique, zone de traçage, titre, étiquettes d'axe, axes, points de données, quadrillage, table de données, légende, ...).

Le typage du graphique est souvent conditionné à la sélection de séries de données, les principaux types sont les suivants

• Graphique en histogramme ou à barres : pour des données se présentant sous forme de valeurs isolées, séparées par des intervalles réguliers ou organisées en catégorie
• Graphique en lignes et courbes : pour représenter des tendances ou une évolution dans le temps de valeurs numériques. Il est intéressant lorsque le nombre des données est important
• Graphique en aires : permettent également de représenter des tendances et des évolutions. Peut montrer la part de chaque série de données par rapport à un total
• Graphique à nuage de points ou à bulles : permet de représenter des relations entre deux séries de valeurs numériques.

Graphique en radar: chaque abscisse possède son propre axe dont l’origine est le centre du graphique. Toutes les valeurs d’une même série sont reliées par des lignes

• Graphique en secteurs (alias camembert) ou en anneaux : sert à représenter la part que prennent divers éléments par rapport au total des valeurs des éléments
• Graphique en radars (alias toile d'araignée) : sert à représenter la position que prennent divers éléments par rapport à d'autres (exemple : différentes notes sur différents mois, ou différents CA sur moyenne, min et max)

Tableaux croisés dynamiques

Les tableaux croisés dynamiques, appelés parfois cubes ou hypercubes, permettent d’avoir une vue tabulaire regroupée des données. Ces mêmes données peuvent donner naissance à plusieurs synthèses instantanées différentes en fonction de la qualification de champs dimensionnels et de données.

• Les champs de pages : champs dimensionnels auxquels une orientation principale en page a été attribuée dans un tableau croisé. Chaque champ de page se place sur la 1ère ligne de la feuille de calcul. Chaque champ représente une dimensions très importante du TCD.
• Les champs de lignes : champs dimensionnels auxquels une orientation en ligne a été attribuée dans un tableau croisé. Chaque champ de ligne se place dans la 1ère colonne du tableau. Chaque champ représente une dimension importante du TCD.
• Les champs de colonnes : champs dimensionnels auxquels une orientation en colonne a été attribuée dans un tableau croisé. Chaque champ de page se place dans la 1ère ligne du tableau. Chaque champ représente une dimension moins importante du TCD.
• Les champs de données : champs qui contiennent les synthèses des données des champs dimensionnels. Chaque valeur de donnée se place dans une cellule intérieure du tableau. Pour pouvoir être synthétisées (dénombrement, somme, moyenne, ...), ces données doivent être numériques.

Une fois créé (ou importé) un tableau multidimensionnel dans une feuille de calcul, la création d'un tableau dynamique se fait via l’onglet insertion et l’assistant TCD.

• Sélectionner plage de données sources et feuille cible
• Déposer les dimensions
  • Champs de pages
  • Champs de colonne
  • Champs de ligne
  • Champs de données
  • Redéfinir des fonctions

On pourra ultérieurement, mettre à jour et insérer de nouvelles données sur ce TCD.

Graphiques croisés dynamiques

Ces graphiques permettent d’avoir une vue géométrique regroupée des données. Encore plus visuels que sur le TCD, les agrégats apparaitront sous formes de courbes ou d'histogrammes avec des échelles automatisées par les valeurs de données.

Chaque sous-dimension divisera l'axe la contenant en plusieurs sous-légendes.

Une fois créé (ou importé) un tableau multidimensionnel dans une feuille de calcul, la création d'un graphique se fait via l’onglet insertion et l’assistant GCD de la même manière qu'un TCD avec le choix du type de graphique en complément.

• Sélectionner plage de données sources et feuille cible
• Déposer les dimensions
  • Champs de pages
  • Champs de colonne
  • Champs de ligne
  • Champs de données
  • Redéfinir des fonctions

On pourra ultérieurement, mettre à jour et insérer de nouvelles données sur ce GCD.

Fichier

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

La gestion des classeurs

Les commandes de l’onglet fichier (ancien bouton office) permettent les opérations de gestion de fichiers suivantes :

• Informations : Protéger le classeur, Inspecter le classeur, Versions, Options d'affichage du navigateur
• Nouveau : Créer un classeur (de nom Classeur_)
• Ouvrir : Ouvrir un classeur existant
• Enregistrer : Enregistrer un classeur standard (.xlsx) ou tel qu’il a été ouvert
• Enregistrer sous : Enregistrer un classeur sous un nom et dans un format choisis (.xlsx)
• Imprimer : Imprimer un classeur ou une feuille, Choisir l'imprimante, Gérer les paramétrages d'impression
• Partager : Inviter des personnes, Envoyer en pièce-jointe par courrier électronique
• Exporter : Créer un document PDF/XPS, Modifier le type de fichier
• Fermer : Fermer un classeur
• Comptes : Informations sur l'utilisateur, Informations sur le produit
• Options : Options Excel

Enregistrer sous

Grâce à cette ligne de menu, l'utilisateur enregistre ses classeurs sur le dossier qu’il sélectionne (par défaut Mes documents), sous le nom qu’il choisit (par défaut classeur), avec l'extension qu’il désire (par défaut xlsx). Parmi les extensions :

• les plus fréquentes
  • Standard : .xlsx
  • Binaire : .xlsb
  • Standard avec macro : xlsm
  • Modèle : xlst
• les sorties formatées
  • textes : txt, csv, xml, html
  • impressions : pdf
  • opendocument : ods et xps
• les obsolètes (ancêtres datant des versions antérieures à 2007)
  • xls, xlt, ...

Par défaut, tout enregistrement standard est en format propriétaire, les autres formats représentant des formats d'échange (texte, impression ou document ouvert).

Les informations complémentaires

Les droits d’accès peuvent être finement réglés et protégés par mot de passe, sur le feuille comme sur le classeur, quelques commandes d'inspection vérifient les possibilités d’accès en partage sur le classeur existant

Les commandes de l’onglet fichier (ancien bouton office) permettent aussi de :

• Protéger le classeur
  • Finaliser, chiffrer, signer le classeur
  • Protéger la feuille active ou tout le classeur
• Préparer le partage
  • Inspection (propriétaire, commentaire, zone cachée, ...)
  • Accessibilité (légende, compréhension, ...)
  • Compatibilité (avec versions d'Excel anciennes, ...)
• Gérer les différentes versions du document

Impression

Grâce à l'aperçu avant impression, Excel affiche à l'écran une visualisation de ce qui va être imprimé, dans la plupart des cas l'utilisateur devra redéfinir ses propres paramètres d'impression après cette visualisation car le look écran ne correspondra pas au look impression. Par défaut Excel essaye d'imprimer toutes les cellules non vides sans pagination, sans marges, sans entête et sans pied de page prédéfinis; l'utilisateur doit donc régler ces paramètres ainsi que définir sa zone d'impression en sélectionnant uniquement les lignes et les colonnes souhaitées. Excel génère des sauts de page automatiquement quand le besoin s'en fait sentir à l'endroit où le texte va être coupé et où l'imprimante va passer à la page suivante, l'utilisateur peut forcer certains sauts de page quand les choix d'Excel ne lui conviennent pas.

Les commandes de l’onglet fichier ou de l'onglet Mise en page suivantes vont permettre de régler :

• Marge
• Orientation
• Taille
• Zone d’impression
• Saut de page
• Arrière plan
• ...

ASTUCE : "L'aperçu avant impression est à déposer impérativement sur la barre d’outils Accès Rapide."

Les options

Les nombreuses commandes de l’onglet fichier permettent de régler les options et les affichages

• Généralités et Formules
• Vérifications et Enregistrements
• Langues
• Options Avancées
• Contenus du Ruban
• Contenus de la Barre d’outils
• Compléments
• Confidentialité

Conseil

Ne pas trop personnaliser son poste de travail, cela peut empêcher d’autres utilisateurs de travailler correctement sur ce même poste

Aide en ligne

L'aide EXCEL francophone est de plus en plus précise et efficace, ne pas hésiter à l’utiliser, car non seulement elle explique de manière détaillée les notions et actions accessibles mais aussi donne des exemples d'utilisation très concrets, par exemple l'aide sur la fonction recherchev.

L'aide EXCEL s'actionne par le bouton aide ou la touche F1, elle va vous permettre de :

• Parcourir l’aide par chapitre, rechercher par mots clés, rechercher par table des matières
• Naviguer (Précédent, Suivant, Accueil), arrêter, actualiser
• Imprimer, maintenir (ou non) son écran au premier plan

Début de l'exemple

Exemple

Rechercher de l'aide sur la fonction recherchev ou de l'information sur les nouveautés de la version

Fin de l'exemple

Révision

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Vérification

Comme son compagnon WORD (traitement de texte), EXCEL apporte dans la partie gauche de l'onglet "Révision", une aide rédactionnelle complète comprenant :

La vérification orthographique des mots, la recherche de la définition ou d'un synonyme d'un mot dans le dictionnaire mais également de rechercher la traduction d'un mot.

ABC Orthographe

Grâce à cet outil, les cellules contenant des mots mal orthographiés sont signalées par les dictionnaires connus ou paramétrés.

Il suffit de cliquer sur la cellule dans laquelle nous souhaitons vérifier l'orthographe et EXCEL ouvre ensuite une fenêtre proposant la correction des mots. Possibilité de vérifier l'orthographe de plusieurs langues. Possibilité de continuer la vérification au début de la feuille. Possibilité d'ignorer l'erreur.

• Cliquez sur le mot mal orthographié, puis cliquez sur remplacer (ou remplacer tout, si plusieurs fautes à corriger en même temps).

Recherche

Cela permet la recherche de signification de mot via un moteur de recherche ou traduction de mot via un dictionnaire traducteur :

   1) Recherche : une signification est recherchée sur les dictionnaires connus ou paramétrés.

Cet onglet est très utile lorsque nous ne comprenons pas la signification d'un mot, ou lorsque nous souhaitons obtenir sa définition exacte. Il est également possible d'obtenir une traduction dans un grand nombre de langues !

• Écrire le ou les mot(s) souhaités puis cliquer sur le bouton vert pour "démarrer la recherche".

   2) Traduire : une recherche de traduction est effectuée sur les traducteurs connus ou paramétrés

• Écrire le mot à traduire puis cliquer sur "traduction", penser à saisir les langues dans lesquelles le mot doit être traduit.

Dictionnaire des synonymes

Une recherche de synonyme est effectuée sur les dictionnaires connus ou paramétrés.

• Même procédure que pour l'onglet précédent.

ASTUCE : "Vos dictionnaires et traducteurs préférés peuvent être en ligne (WEB) ou hors ligne (LOCAL)."

Commentaires

Le commentaire permet d'annoter une cellule avec du texte mais aussi de l'image, il est signalé dans le coin haut droit de la cellule commentée. Il est créé ou supprimé soit via le ruban, soit par clic droit sur la cellule sélectionnée.

Les commentaires peuvent être affichés ou masqués, une navigation est possible entre eux (précédent, suivant, ...).

Nouveau commentaire

• Cliquez sur la cellule sur laquelle vous souhaitez y ajouter un commentaire
• Écrire le commentaire dans la case jaune qui apparaît

Pensez à renseigner votre nom pour permettre aux autres utilisateurs du fichier de savoir qui a écrit le commentaire.

Supprimer

• Cliquez sur la cellule sur laquelle vous souhaitez supprimer le commentaire.

Précédent/Suivant

• Cliquez sur ces deux boutons afin de naviguer d'un commentaire à l'autre.

Afficher les entrées manuscrites

La dernière mise à jour inclut une nouvelle fonctionnalité, pensée spécialement pour les utilisateurs d’Office sur des appareils tactiles. Vous pouvez désormais ajouter des annotations avec des entrées manuscrites pour prendre des notes, surligner des extraits de texte, créer des formes rapides, ou écrire des équations mathématiques et les convertir en texte sur des appareils tactiles ou avec stylet. On peut faire tout cela en cliquant sur "Commencer la saisie manuscrite".

De plus, si votre appareil avec stylet utilise un stylet actif (et non un simple stylet), Office détecte lorsque votre stylet est à portée de votre écran et vous commencez la saisie manuscrite automatiquement.

En cliquant sur "Afficher les entrées manuscrites", on peut afficher ou masquer ce qu'on a fait avec la saisie manuscrite.

ASTUCE : "Certaines tablettes et écran tactiles peuvent écrire directement des notes sur l'écran, EXCEL les considère être une forme de commentaire."

Modifications

Protéger la feuille

Lorsque plusieurs utilisateurs sont amenés à modifier un même fichier, des modifications volontaires ou involontaires peuvent survenir comme la suppression de formules ou de données. Afin de restreindre ces modifications, il est possible de protéger la feuille avec un mot de passe. Ainsi, les cellules de la feuille ne pourront pas être modifiées si on n'ôte pas la protection avec le mot de passe dans le menu révision "Oter la protection de la feuille".

Lorsque l'on protège la feuille:

• On choisit le mot de passe ;
• On décide le degré de modifications accordé aux utilisateurs en cochant ou décochant les items: sélection des cellules, format des cellules, format de colonnes, format de lignes… Cela peut permettre aux utilisateurs de faire des modifications de formes essentiellement (couleur, taille des colonnes/lignes, filtres…). On peut voir dans cette image quelques exemples d'items:

items protection classeur

Si dans une feuille, on veut avoir des cellules modifiables et le reste verrouillé, il existe plusieurs méthodes.

Méthode 1:

• On sélectionne la plage concernée ;
• On clique sur "permettre la modification des plages" ;
• On nomme la plage et on applique la modification ;
• Ensuite, on clique sur "protéger la feuille".

Avantage: Cela permet de retrouver facilement les zones non protégées. On peut les supprimer si besoin.

Méthode 2:

• On sélectionne les cellules concernées ;
• On effectue un clic droit ;
• Puis dans "Format de cellule" on va dans le menu "Protection" ;
• On décoche "verrouillée" ;
• On protège la feuille.

Remarque: pour permettre la modification d'un graphique, seule la méthode 2 fonctionne: il faut décocher "verrouillée" dans propriété (format de la zone graphique).

Protéger le classeur

Gérer la protection du classeur permet de donner accès à la modification de l’ensemble des feuilles d'un classeur EXCEL (saisie et/ou modification des cellules). Certaines feuilles peuvent être protégées tandis que d'autres seront verrouillées. Cela permet de partager un fichier avec des collaborateurs en réduisant le risque de perte de données ou d'erreur de saisie par exemple.

La protection du classeur empêche la modification de la structure du classeur par les autres utilisateurs. En effet il n'est pas possible d'insérer, de supprimer, de renommer, de déplacer ou copier une feuille.

Partager le classeur

Partager le classeur permet à plusieurs collaborateurs de travailler en même temps sur le même fichier. Chacun pourra y apporter ses modifications. Lors du partage du classeur, plusieurs options concernant le suivi et la mise à jour des modifications sont proposées. Il est ainsi possible de conserver l’historique des modifications.

• Dans l’onglet Révision, cliquez sur « Partager le classeur »
• Sous l’onglet « Modification », cochez la case « Permettre une modification multi-utilisateur. Ceci permet également de fusionner des classeurs ».
• Sous l’onglet « Avancé », sélectionnez les options souhaitées puis cliquez sur OK
• S’il s’agit d’un nouveau classeur, nommez et choisissez l’emplacement du classeur
• S’il s’agit d’un classeur existant, cliquez sur OK pour enregistrer le classeur

Protéger et partager le classeur

Lorsqu’un fichier est partagé, toutes les personnes ayant accès au réseau sur lequel ce dernier est partagé peuvent le consulter et le modifier.

Ainsi, si le fichier contient des données confidentielles et que l’on veut par conséquent éviter l’accès de tous au fichier, il est possible d'y ajouter un mot de passe afin de le protéger. À l’ouverture du fichier un mot de passe sera demandé, seules les personnes ayant connaissance du mot de passe pourront y accéder et apporter leurs modifications.

Pour cela :

• Dans l’onglet « Révision », cliquez sur « Protéger et partager le classeur » ;

• Cochez la case « Partage avec suivi des modifications » si vous souhaitez empêcher la suppression du suivi des modifications ;
• Puis saisissez le mot de passe ;
• Enregistrez le classeur.

Suivi des modifications

Puisque le partage d’un fichier permet à plusieurs personnes d’y apporter des modifications, il peut parfois être utile de tracer les modifications apportées. L’option « suivi des modifications » nous permet de le faire. Celle-ci affiche l’historique des modifications apportées dans la feuille de calcul en détails.

Il faut dans un premier temps activer l’option :

• Dans l’onglet « Révision », cliquez sur « Suivi des modifications » ;
• Puis  « Afficher les modifications » ;
• Cochez la case « Suivre les modifications au fur et à mesure. Le classeur est partagé. » ;
• Puis cliquez sur « Ok ».

Le suivi des modifications est alors activé. Lors des prochaines ouvertures du fichier, vous pourrez cliquer sur « afficher les modifications ». Un commentaire apparaîtra sur chaque cellule modifiée avec le nom de l’utilisateur ayant effectué la modification, la date et la valeur modifiée.

Il est également possible de choisir l’affichage des modifications apportées selon des critères.

• Cliquez sur afficher les modifications.

Choisissez la date des modifications que vous souhaitez afficher en cochant la case « Le » puis en sélectionnant la date souhaitée.

• Vous pouvez choisir de ne pas afficher les modifications apportées par vous-même par exemple en cochant la case « Par » puis sélectionnant « Tout sauf moi » dans la liste proposée.

• Cliquez sur « Ok ».

On peut également accepter ou refuser les modifications: on révise les modifications apportées par les autres utilisateurs.

Remarque

Par défaut, EXCEL conserve l’historique des modifications pendant 30 jours et efface de façon permanente tout historique des modifications antérieur à ce nombre de jours. Pour conserver un historique pendant plus de 30 jours, entrez un nombre supérieur à 30.

Autres fonctionnalités

Partager maintenant

Dans les versions professionnelles d’Excel, il est possible, à partir de l’onglet Révision, de partager le fichier avec des personnes en enregistrant le document dans un emplacement OneDrive. Cela permet à plusieurs personnes de visualiser et/ou modifier le fichier partagé.

Pour partager le fichier :

• Cliquez sur « Partager maintenant » ;

• Puis « Partager avec des personnes » ;

• Cliquez sur « Enregistrer dans le cloud » ;
• Choisissez un emplacement partagé.

Une fois que le fichier est partagé, vous pouvez ensuite inviter d’autres personnes afin qu’elles puissent également y avoir accès. Pour cela :

• Cliquez sur « inviter des personnes » :
• Entrez l’adresse de messagerie de la personne concernée.

Envoyer par messagerie instantanée

Il est également possible d’envoyer le fichier par courrier électronique.

Pour ce faire:

• Cliquez sur « Partager » :

• Puis sur « Courrier électronique ».

Plusieurs possibilités vous sont proposées. Vous pouvez choisir d’envoyer le fichier en tant que pièce jointe si vous voulez permettre aux destinataires de modifier le fichier. Sinon vous pouvez choisir de l’envoyer en format PDF ou XPS pour conserver le fichier tel quel et empêcher la modification du fichier.

Données

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Import de données

Importer des fichiers texte

Il est possible d’importer des données d’un fichier texte de deux manières : vous pouvez soit ouvrir le document dans Excel, ou bien utiliser l’assistant d’importation de texte pour créer une plage de données externes.
Deux formats de fichier texte peuvent être utilisés :

• Le fichier texte délimité dans lequel des tabulations séparent les différents champs de texte
• Le fichier csv (comma separated values – valeurs séparées par une virgule) dans lequel des virgules (ou des points virgules) séparent les différents champs de texte

Il est également possible de modifier le caractère utilisé pour séparer les données d’un fichier texte délimité ou d’un fichier texte csv

Importer un fichier texte en s’y connectant

Excel offre la possibilité d’importer les données d’un fichier texte externe en créant une plage de données externe dans la feuille de calcul

• Cliquez sur la cellule où vous souhaitez importer les données du fichier texte.
• Dans l’onglet Data > Cliquez sur À partir du texte
• Dans la nouvelle fenêtre, localisez le fichier que vous désirez importer et cliquez sur Importer

L’assistant d’importation de texte s’affiche, si vous souhaitez importer vos données de façon brute, vous pouvez cliquer directement sur Terminer. Si vous souhaitez configurer l’importation de vos données, il est nécessaire de parcourir toutes les étapes de l’assistant d’importation.

• Dans la nouvelle fenêtre importation de données, vous pouvez cliquer sur Propriétés pour paramétrer certaines options d’importation, ou bien spécifier l’emplacement ou vous souhaitez importer vos données sous «Insérer les données dans»
• Cliquez sur OK pour terminer l’importation des données

Excel importe les données externes dans une plage de données à l’emplacement que vous lui aviez indiqué.

Assistant d’Importation de texte

L’assistant d’importation de texte se décline en 3 étapes et permet de spécifier les différents paramètres liés à l’importation des données externes.

Etape 1 sur 3

L’assistant d’importation vous indique le type de données que vous souhaitez importer. Un aperçu est disponible en bas de la fenêtre afin de pré visualiser les données qui sont disponibles dans le fichier et la manière dont elles seront formatées après l’importation.

• Type de données d’origine : Si le document contient des données qui sont séparées par des caractères (tabulations, virgules, points-virgules, etc…) alors choisissez Délimité. Si les différentes colonnes de données sont toutes de largeurs identiques (des colonnes de 5 caractères par exemple), choisissez Largeur Fixe
• Commencer l’importation à la ligne : Il peut arriver parfois que des documents textes contiennent plusieurs lignes d’en-tête spécifiant le contenu du document avant d’avoir les colonnes de données. Ce paramètre permet d’indiquer la première ligne qui devra être prise en compte dans l’importation.
• Origine du fichier : Ce paramètre permet de spécifier le jeu de caractères qui a été utilisé pour créer le fichier texte. Dans la plupart des situations, ce paramètre peut rester sur sa valeur de défaut.
• Aperçu du fichier : Cette zone permet d’avoir un aperçu de l’aspect du formatage des données une fois que celles-ci auront été importées dans la feuille de calcul

Etape 2 sur 3 (Données délimitées)

• Séparateurs : Choisissez le caractère qui permet de délimiter vos champs de texte dans votre fichier texte. Vous avez la possibilité d’activer la case à cocher Autre et de spécifier le caractère de délimitation si celui-ci n’est pas disponible dans la liste
• Interpréter des séparateurs identiques consécutifs comme uniques : Permet d’indiquer si l’importation des données doit prendre en compte la répétition ou non des séparateurs identiques. Par exemple, si le point-virgule est le caractère de délimitation, a;;;b donnera 4 colonnes alors qu’avec l’option activée, il n’y en aurait plus que 2).
• Identificateur de texte : Permet de spécifier à l’assistant d’importation le caractère d’identification des chaînes de caractères. Par exemple, si le point-virgule est le caractère de délimitation et le guillemet est le caractère d’identification de texte, alors h ; « i ; j » ; k donnera 3 colonnes au lieu de 4.
• Aperçu de données : Cette zone permet de s’assurer que les données du fichier sont bien séparées dans les colonnes que vous voulez avoir.

Etape 2 sur 3 (Données de largeur fixe)

• Aperçu de données : Cette zone de prévisualisation permet de définir la largeur des champs que l’on souhaite utiliser pour délimiter les données. Une ligne verticale représente un saut de colonne et peut être supprimée par un double-clic. Il est également possible de créer des sauts de colonne et de les déplacer.

Etape 3 sur 3

• Le bouton avancé permet de configurer :
  • Le séparateur de décimal ou de millier utilisé dans le document texte
  • Qu’une ou plusieurs valeurs numériques peuvent être négatives et se terminer par un signe moins
• Format des données en colonne : Permet de spécifier le type de données d’une colonne. Pour une colonne de type Date, vous pouvez spécifier le format d’affichage de la date (JJ/MM/AA, MM/JJ/AA, etc…). Choisissez Colonne non distribuée si vous ne désirez pas l’importer dans votre feuille de calcul.
• Cliquez sur Terminer pour finir l’importation des données

Tri des données

Le tri sur Excel 2007 permet de trier des données sur une plage ou une feuille sélectionnée, en fonction d’un ou plusieurs critères.

Il est possible de trier les données en fonction de textes (tri croissant : de A à Z ; ou tri décroissant : de Z à A), de nombres (tri croissant : du plus petit au plus grand ; ou décroissant : du plus grand au plus petit) ou encore de dates ou d’heures (tri des plus anciennes aux plus récentes ou des plus récentes aux plus anciennes). Excel propose également de trier les données en fonction d’une liste personnalisée ou en fonction de la mise en forme (couleur de la cellule, couleur de la police, icône de la cellule).

Le tri peut s’effectuer sur une ou plusieurs colonnes, mais également sur une ou plusieurs lignes.

Trier une unique colonne

1. Vérifiez que toutes les données de la colonne à trier sont au même format pour éviter les problèmes de tri. Si toutes les données ne sont pas au même format, mettre en forme(type et format d'affichage) toutes les données de la colonne.
2. Sélectionnez la plage de cellule de la colonne à trier (constituée d’une seule colonne) .
3. Dans l’onglet « Données » du ruban, sélectionnez « Trier » (tri personnalisé) ou cliquez directement sur le tri croissant ou décroissant si cela correspond au tri souhaité. Vous pouvez également faire un clic droit sur la plage de cellules sélectionnées et choisir trier (croissant : de A à Z ; décroissant : de Z à A ; ou tri personnalisé).
4. Si vous avez choisi le tri personnalisé, une boite de dialogue s’ouvre. Vous pouvez alors choisir le type de tri que vous souhaitez effectuer (Valeurs, couleur de la cellule, couleur du texte, icone). Sélectionnez le type de tri puis l’ordre souhaité.

Trier plusieurs colonnes

Il y a deux possibilités pour le tri de plusieurs colonnes :

• Tri de plusieurs colonnes avec un unique critère : Vous modifiez l’ordre d’une colonne et cela modifie l’ordre des lignes entières.

Exemple : Vous avez la liste d’une classe d’élèves qui ont chacun un nom de famille différent. Vous voulez trier les élèves selon leur nom de famille, dans l’ordre croissant. Vous souhaitez donc effectuer un tri en gardant les données des lignes correspondantes (Prénom, date de naissance, adresse…). Vous effectuez donc un tri sur plusieurs colonnes avec un unique critère.

• Tri de plusieurs colonnes avec plusieurs critères : Vous modifiez l’ordre de plusieurs colonnes grâce à un ordre de priorité.

Exemple : Vous souhaitez trier la liste des commandes clients de votre entreprise selon leur nom et la date de leur dernière commande. La priorité est le tri selon le nom, puis si le client a passé plusieurs commandes, celles-ci s’afficheront de la plus récente à la plus ancienne, et non pas dans un ordre aléatoire. C’est le tri sur plusieurs critères.

Tri avec un seul critère

1. Vérifiez que toutes les données de la colonne à trier sont au même format pour éviter les problèmes de tri.
2. Sélectionnez la plage de cellule à trier (constituée de plusieurs colonnes dans ce cas).
3. Dans l’onglet « Données » du ruban, sélectionner « Trier » (tri personnalisé) ou cliquez directement sur le tri croissant ou décroissant si cela correspond au tri souhaité. Vous pouvez également faire un clic droit sur la plage de cellules sélectionnées et choisir trier (croissant : de A à Z ; décroissant : de Z à A ; ou tri personnalisé).
4. Si vous avez choisi le tri personnalisé, une boite de dialogue s’ouvre. Vous pouvez alors choisir le type de tri que vous souhaitez effectuer (Valeurs, couleur de la cellule, couleur du texte, icone). Sélectionnez le type de tri puis l’ordre souhaité.
5. Validez en cliquant sur OK : Le tri s’effectue, et les données de chaque ligne suivent.

Tri avec plusieurs critères

1. Vérifiez que toutes les données de chaque colonne à trier sont au même format pour éviter les problèmes de tri.
2. Sélectionnez la plage de cellule à trier (constituée de plusieurs colonnes dans ce cas).
3. Dans l’onglet « Données » du ruban, sélectionner « Trier » (tri personnalisé). Vous pouvez également faire un clic droit sur la plage de cellules sélectionnées et choisir trier  tri personnalisé.
4. Une boite de dialogue s’ouvre. Vous pouvez alors choisir le type de tri que vous souhaitez effectuer pour chacune des colonnes (Valeurs, couleur de la cellule, couleur du texte, icone). Pour cela, ajoutez autant de niveau que nécessaire (si vous souhaitez trier en fonction de 2 critères, ajoutez 1 niveau à celui déjà présent. Pour trier en fonction de 3 critères, ajoutez 2 niveau, etc…)
5. Choisissez les colonnes à trier par ordre d’importance, le premier tri à faire par Excel se trouvant sur la 1ère ligne de la boite de dialogue.
6. Sélectionnez le type de tri puis l’ordre souhaité pour chacune des colonnes choisies.
7. Validez en cliquant sur OK.

Trier d’après une liste personnalisée

Deux listes personnalisées sont intégrées dans Excel :

• Jours de la semaine
• Mois de l’année

Pour créer une autre liste personnalisée :

1. Cliquez sur le bouton Microsoft Office, puis sur Options
2. Allez dans la catégorie Standard, puis sous Meilleures options pour travailler sous Excel, choississez Modifier les listes.
3. Dans la partie Listes personnalisées, cliquez sur Nouvelle liste.
4. Entrez les valeurs dans la zone Entrées de la liste. ATTENTION : Vous pouvez uniquement créer une liste personnalisée basée sur une valeur (texte, nombre, date ou heure). Vous ne pouvez pas créer de liste personnalisée basée sur une mise en forme (couleur de cellule, couleur de police, icône).
5. Cliquez sur Entrée à chaque nouvelle saisie de ligne.
6. Lorsque votre liste est terminée, cliquez sur Ajouter, puis deux fois sur OK.

Trier des lignes

1. Sélectionnez une plage de cellules.
2. Dans l’onglet Données, cliquez sur Trier
3. Cliquez sur Options. Une boite de dialogue « Options de tri » s’ouvre.
4. Choisissez « de la gauche vers la droite » pour l’orientation. Cliquez sur OK.
5. Effectuez le tri souhaité en choisissant la ligne, le type de tri et l’ordre de tri.
6. Cliquez sur OK pour valider.

Remarques

Remarques	Aide	Image
Boîte de dialogue "Attention"	Si cette boite de dialogue apparaît, cela signifie que d’autres colonnes sont en lien avec la colonne sélectionnée. Vous avez peut-être oublié de sélectionner certaines colonnes. Deux possibilités : - Vous souhaitez trier la ou les colonnes sélectionnées dans prendre en compte les colonnes adjacentes. Cochez « Continuer avec la sélection en cours » et cliquez sur OK. - Vous avez oublié de sélectionner dans votre plage de cellule une ou plusieurs colonnes que vous souhaitez faire apparaître dans votre tri. Cochez « Étendre la sélection » et cliquez sur OK.
Trier en respectant la casse	Dans l’onglet données, cliquez sur Trier. Cliquez sur options. Dans la nouvelle boite de dialogue, cochez « Respecter la casse ». Cliquez sur OK (à deux reprises)
Colonnes sans en-têtes	Si vous n’avez pas d’en-têtes sur vos colonnes, le tri ne prendra pas en compte la première ligne de votre sélection. Pour que toutes les lignes soient prises en compte : Cliquez sur Trier dans l’onglet « Données ». En haut à droite, décochez « Mes données ont des en-têtes ». Cliquez sur OK pour valider.	Mes données ont des en-têtes

Filtre automatique de données

Le filtre automatique est un outil permettant de faciliter la recherche d'informations au sein d'une plage continue de données. Cette recherche se réalise au travers de l’application de filtres sur une ou plusieurs colonnes de la plage de données, permettant donc à l'utilisateur de choisir les informations qu’il souhaite afficher ou masquer. L'utilisateur à la possibilité de filtrer des données numériques, textuelles, ou bien en fonction de la mise en forme des cellules (couleur du texte et de l'arrière-plan)

Exemple d’application du filtre automatique

Logistic data filter example 1

• Sélectionnez n’importe quelle cellule de la plage de données que l’on veut filtrer, pour des résultats plus explicites il est préférable que ces données comportent des en-têtes.
• Cliquez sur Données > Filtrer (Excel va automatiquement afficher des flèches sur les en-têtes des colonnes de votre plage de données)

Logistic data filter example 2

• Cliquez sur la flèche située à côté de l’en-tête de la colonne que vous souhaitez filtrer, et ensuite un menu contextuel s’affiche avec différentes options.
• Cliquez sur Filtre Textuel ou Filtre Numérique.
• Cliquez sur l’un des opérateurs de comparaison. Par exemple, pour afficher uniquement les nombres inférieurs à une valeur, choisissez Inférieur à...

Logistic data filter example 3

• Dans la boîte Filtre Automatique Personnalisé, indiquez ou sélectionnez le critère de tri de vos données. Par exemple pour afficher tous les nombres strictement inférieurs à 1000, dans le champ de saisie est inférieur à, tapez 1000.

Logistic data filter example 4

Utiliser le filtre automatique pour filtrer les données

Le filtre automatique permet de trouver des valeurs, d’afficher ou de masquer des valeurs, sur une ou plusieurs colonnes de données. Vous pouvez filtrer vos données à partir de listes de sélection ou bien en cherchant les valeurs que vous souhaitez afficher. Lorsque vous filtrez vos données, des lignes complètes sont masquées si les valeurs d’une ou plusieurs colonnes ne correspondent pas aux critères des filtres.

• Sélectionnez les données que vous voulez filtrer automatiquement
• Cliquez sur Données > Filtrer
• Cliquez sur la flèche située à côté de l’en-tête de la colonne que vous souhaitez filtrer et faites l’une des actions suivantes :
  • Choisissez une valeur spécifique : Cliquez sur (Sélectionner Tout) pour décocher toutes les cases à cocher, et sélectionnez ensuite les valeurs que vous souhaitez afficher pour la colonne concernée.
  • Recherchez une valeur : Dans le champ de Recherche, indiquez le texte ou le nombre que vous souhaitez afficher pour la colonne concernée.
• Cliquez sur OK pour appliquer le filtre

Retirer un filtre

Si vous ne trouvez pas les données que vous recherchez dans une feuille de calcul, celle-ci peuvent avoir été masquées par un filtre. Par exemple, si vous avez une colonne contenant uniquement des dates, il se peut que celle-ci soit filtrée pour n’afficher que certains mois. Vous pouvez retirer un filtre d’une colonne spécifique ou retirer complètement tous les filtres de la feuille de calcul.

Retirer un filtre d’une colonne de données

• Cliquez sur le bouton Filtrer situé à côté de l’en-tête de colonne
• Dans le menu contextuel cliquez sur "Effacer le filtre de l’en-tête de colonne".

Retirer tous les filtres de la feuille de calcul

• Cliquez sur la bascule Données > Filtrer, ceci désélectionnera le filtre entier

Ruban Filtre

Comment savoir si des données ont été filtrées ?

Si l’outil filtre automatique est actif, vous trouverez l’un des boutons suivant affiché à côté des en-têtes de colonnes :

• Ce bouton indique qu’un filtre est disponible et qu’il n’a pas été utilisé pour filtrer les données dans la colonne
• Ce bouton indique qu’un filtre a été appliqué pour filtrer ou trier des données dans la colonne

Dans la feuille de calcul suivante :

• Un filtre est disponible pour la colonne Stock mais n’a pas été utilisé.
• Le filtre dans la colonne Category a été utilisé pour ne pas afficher la category Engine, de ce fait les lignes 8 et 9 sont masquées.

Conversion de données

La fonction convertir permet de diviser une colonne de donnée texte en plusieurs colonnes. Par exemple vous pouvez à partir d’une colonne nom complet, la diviser en une colonne prénom et une colonne nom de famille.

1ère étape : sélectionnez la colonne de texte que vous voulez fractionner puis cliquez sur convertir.

2^e étape : la fenêtre « assistant de conversion » s’ouvre alors. Cliquez sur délimiter puis suivant. Cochez ensuite le mode de séparation que vous avez utilisé, par exemple espace. (Vous pouvez également cocher plusieurs modes de séparation si vous avez par exemple utilisé une virgule puis un espace entre vos données à fractionner). Un aperçu de vos données s’affiche en bas de la fenêtre. Cliquez sur suivant.

3^e étape : Vous pouvez choisir le format de vos nouvelles colonnes, par exemple texte. Ou bien vous pouvez laisser le format prédéfini par Excel. Un aperçu de vos nouvelles données s’affiche en bas de la fenêtre.

4^e étape : Réduisez la fenêtre en cliquant sur le bouton à droite de la ligne destination (situé au milieu de la fenêtre). Sélectionnez les cellules dans lesquelles vous souhaitez coller vos nouvelles données fractionnées.

5^e étape : Cliquez sur le bouton à droite de la ligne de destination pour agrandir la fenêtre. Cliquez sur terminer.

Suppression des doublons

Cette fonctionnalité permet de supprimer les doublons dans une feuille de calculs.

1. Pour supprimer des doublons, cliquez sur une cellule comprenant des données
2. Cliquez sur le bouton « SUPPRIMER LES DOUBLONS »
3. La fenêtre suivante apparaît
   

1. Cliquez sur le bouton « SELECTIONNER TOUT » pour sélectionner toutes les colonnes. Ou sur le bouton « DESELECTIONNER TOUT » pour désélectionner les colonnes.
   Vous pouvez choisir manuellement les colonnes dans lesquelles il y a des doublons à supprimer en cochant les colonnes (exemple : Année)
   La coche « Mes données ont des en-têtes » fait apparaître le nom des colonnes. Si vous décochez, les colonnes seront nommés « Colonne 1 » pour « Année », « colonne 2 » pour « Modèle » et « colonne 3 » pour « Prix ».
   

    Attention: une donnée dans une même colonne peut avoir été attribuée plusieurs fois, alors que les colonnes adjacentes ne sont pas identiques. Si vous cochez une seule colonne, par exemple "ANNEE", la fonction "SUPPRIMER LES DOUBLONS" effacera toutes les lignes qui ont la même année.

1. Cliquez sur le bouton « OK »
2. S’il n’y a pas de données en doublon, le message "Aucune valeur en double trouvée" apparaît.
3. S’il y a un doublon dans votre feuille de données, comme dans l’exemple ci-dessous :
   
   

Alors les données seront supprimées et le message " x valeurs en double trouvées et supprimées. Il reste y valeurs uniques." apparaît.
Le doublon est alors supprimé.

Validation de données

La validation des données permet de restreindre la saisie de certaines valeurs ou types de données par les utilisateurs du classeur excel. Ainsi les utilisateurs ne pourront pas saisir des données qui ne sont pas valides. Ou bien vous pouvez simplement avertir l’utilisateur que ces données ne sont pas valides. Vous pouvez aussi afficher un message d’instruction sur le type de donnée valide afin d’aider l’utilisateur à saisir les données attendues.

Utiliser la validation des données

• Limiter les données à des éléments prédéfinis dans une liste. Par exemple, vous pouvez limiter la saisie d’une zone géographique à certains pays (France, Allemagne, Belgique)
• Limiter les numéros en dehors d’une plage prédéfinie. Par exemple, vous pouvez limiter la valeur à saisir entre un minimum et en maximum que vous avez prédéfini.
• Limiter le nombre de caractères textuels Par exemple, vous pouvez limiter le texte autorisé dans une cellule à un certain nombre de caractère.
• Restreindre les dates en dehors d’une certaine plage de dates. Par exemple, vous pouvez spécifier une plage de dates comprises 1 mois avant la date du jour à 1 mois après la date du jour.
• Restreindre les heures en dehors d’une certaine plage horaire. Par exemple, vous pouvez spécifier une plage horaire correspondant à l’ouverture d’un magasin.

Vous pouvez utiliser des formules pour la validation des données. Par exemple pour restreindre une plage entre 1 mois avant la date du jour jusqu'à 1 mois après, entrez la formule =AUJOURDHUI()-30 comme date de début et =AUJOURDHUI()+30 comme date de fin de plage.

Vous pouvez utiliser vos formules dans autoriser : personnalisé. Par exemple pour autorisez la mention félicitation uniquement si la moyenne en cellule E2 est supérieure à 16. Dans personnalisé saisissez la formule SI(E2>16;"félicitation").

Vous pouvez utiliser le contenu d'une autre cellule pour autoriser les données de votre cellule. Par exemple pour que le contenu de la cellule résultat soit comprise entre le montant négatif des charges en cellule A2 et le montant des produit en cellule B2. Alors dans minimum saisissez =-A2 et en maximum =B2.

Vous pouvez entourer les données non valides en cliquant sur le bouton "entourer les données non valides". Ainsi toutes les données erronées sont entourées par des cercles rouges. Ces cercles disparaissent automatiquement lorsque les données sont corrigées, sinon il suffit de cliquer sur le bouton "effacer les cercles de validation".

Pour supprimer la validation de données pour une cellule, sélectionnez-la, cliquez sur Validation des données puis effacer tout (dans l'onglet paramètre).

Messages de validation des données

• Message de saisie, il s’affiche lorsque l’utilisateur place son curseur sur la cellule. Il permet de conseiller l’utilisateur sur le type de donnée attendu.

• Message d’erreur, il s’affiche lorsque l’utilisateur entre des données non valides. Il existe plusieurs types d’alerte.

Le type arrêt propose à l’utilisateur de choisir entre recommencer ou annuler ce qui efface la donnée erronée saisie dans la cellule.Ce type de message ne laisse pas la possibilité à l'utilisateur de confirmer qu’il a délibérément entré une donnée non valide.

Message d'avertissement

Le type avertissement laisse trois possibilités à l’utilisateur, "annuler", "non" pour modifier les données saisies et "oui" pour accepter les données non valides.

Message d'avertissement

Le type information, l’utilisateur peut choisir "ok" il accepte les données ou annuler.

Message d'avertissement

Il est possible de personnaliser le message d’erreur, sinon c’est un message par défaut qui s’affiche.

Attention, ces messages ne fonctionnent pas pour 3 cas : lorsque la cellule est remplie par copie ou remplissage, lorsque c’est une formule qui calcul les données de la cellule et lorsqu’une macro entre les données dans la cellule.

Remarque

lorsque vous définissez des paramètres de validation des données alors que les cellules contiennent déjà des données non valides alors rien ne se passe automatiquement. Il faut mettre en évidence les données non valides en les entourant.

Exemple d'application de la validation des données

1ère étape : Sélectionnez une ou plusieurs cellules à valider. Sous l’onglet Données, dans le groupe Outils de données, cliquez sur Validation des données.

2^e étape : Dans « critère de validation » vous pouvez choisir quelles données autoriser, par exemple liste. Puis cliquez sur la zone source et tapez la liste des valeurs séparées par un point-virgule. Par exemple : France ; Belgique ; Allemagne. Vous pouvez également créer cette liste de valeurs par référence à une plage de cellules situées ailleurs dans le classeur. Vérifiez que la case liste déroulante dans la cellule est cochée. Sinon, vous ne pourrez pas voir la flèche de la liste déroulant de la cellule.

validation des données 1

3^e étape : Testez la validation des données pour vérifier qu’elle fonctionne correctement. Essayez d’entrer dans les cellules des données valides et non valides pour vérifier que vos paramètres fonctionnent et que les messages s’affichent de la façon escomptée.

Consolidation de données

Pour synthétiser, résumer, présenter et afficher des résultats de différentes feuilles de calcul, il est possible de consolider les données de chaque feuille dans une feuille unique. Ces nouvelles feuilles uniques peuvent être créées dans le même classeur en tant que « feuille maître » ou dans des classeurs différents.

Pour consolider des données, utilisez la commande Consolider du groupe Outils de données, sous l’onglet Données.

Les différentes consolidations

Plusieurs procédures existent en fonction de ce que vous souhaitez faire :

• Consolidez par position si vous souhaitez organiser les données dans toutes les feuilles de calcul dans un ordre et à un endroit identique ;
• Consolidez par catégorie si vous souhaitez organiser les données différemment dans chaque feuille de calcul, mais utiliser les mêmes étiquettes de ligne et de colonne de sorte que la feuille de calcul maître puisse correspondre aux données;
• Consolidez par formule si vous souhaitez utiliser des formules contenant des références de cellules ou des référence 3D à d’autres feuilles de calcul que vous associez parce que vous ne disposez pas d’une position ou d’une catégorie fiable ;
• Utilisez un rapport de tableau croisé dynamique pour consolider les données à la place d'une consolidation.

Pour que la consolidation fonctionne :

• Assurez-vous que chaque première ligne de chaque colonne contient une étiquette, chaque colonne contient des formats identiques, et la liste ne contient aucune ligne ou colonne vide.
• Placez chaque plage dans une feuille de calcul différente. Ne placez aucune des plages dans la feuille de calcul sur laquelle vous allez placer la consolidation.
• Nommez chaque plage d’un nom différent.
  

Remarque

Attention ! La commande consolider remplit la zone de données nécessaire. Prenez soin de laisser suffisamment de cellules à droite et en dessous de cette cellule sélectionnée pour les données consolidées.

Consolidation par position

1. Choisissez les données à consolider dans chaque feuille de calcul.
2. Cliquez sur la cellule située dans l’angle supérieur gauche de la zone où vous souhaitez faire apparaître les données consolidées dans la feuille de calcul maître.
3. Sous l'onglet Données, dans le groupe Outils de données, cliquez sur Consolider.
4. Dans la zone Fonction, cliquez sur la fonction que vous souhaitez utiliser pour consolider les données.
5. Si la feuille de calcul est dans un autre classeur, cliquez sur Parcourir, à droite de la zone Référence pour choisir le fichier à consolider.
6. Tapez le nom que vous avez attribué à la plage, puis cliquez sur Ajouter. Répétez cette étape pour chaque plage à consolider.
7. Cliquez sur OK
   

Remarque

Attention ! Assurez-vous que toutes les plages ont la même disposition dans les feuilles

Consolidation par catégorie

1. Choisissez les données à consolider dans chaque feuille de calcul.
2. Cliquez sur la cellule située dans l’angle supérieur gauche de la zone où vous souhaitez faire apparaître les données consolidées dans la feuille de calcul maître.
3. Sous l'onglet Données, dans le groupe Outils de données, cliquez sur Consolider.
4. Dans la zone Fonction, cliquez sur la fonction que vous souhaitez utiliser pour consolider les données.
5. Si la feuille de calcul est dans un autre classeur, cliquez sur Parcourir, à droite de la zone Référence pour choisir le fichier à consolider.
6. Tapez le nom que vous avez attribué à la plage, puis cliquez sur Ajouter. Répétez cette étape pour chaque plage à consolider.
7. Cliquez sur OK
   

Remarque

Attention ! Assurez-vous que les étiquettes des colonnes ou des lignes que vous voulez associer ont la même orthographe et la même casse

• Exemple : Les étiquettes "CA annuel" et "Chiffre d'affaires annuel" sont différentes et ne seront pas consolidées.
• Toutes les étiquettes qui ne correspondent pas à celles situées dans les autres zones source génèrent des lignes ou des colonnes distinctes dans la consolidation.
• Vérifiez si toutes les catégories que vous ne souhaitez pas consolider sont dotées d’étiquettes uniques qui n’apparaissent que dans une seule plage source.

Consolidation par formule

1. Dans la feuille de calcul maître, insérez les étiquettes de ligne ou de colonne souhaitées pour les données consolidées.
2. Cliquez sur la cellule située dans l’angle supérieur gauche de la zone où vous souhaitez faire apparaître les données consolidées dans la feuille de calcul maître.
3. Tapez une formule permettant la consolidation :

Si les données à consolider figurent dans des cellules différentes de plusieurs feuilles de calcul	Si les données à consolider figurent dans les mêmes cellules de différentes feuilles de calcul
Entrez une formule avec des références de cellules à d’autres feuilles de calcul, une pour chaque feuille. Par exemple, =SOMME(feuille1!A1;feuille2!B6;feuille3!D9)	Entrez une formule contenant une référence 3D utilisant une référence à une plage de noms de feuille de calcul. Par exemple, =SOMME(feuille1:feuille3!A1)
ASTUCE : "Pour entrer une référence comme feuille1!D8 dans une formule sans la taper directement, cliquez sur l’onglet de la feuille de calcul où figure cette référence, puis sur la cellule concernée."	ASTUCE : "Si le classeur est configuré pour calculer automatiquement les formules, la consolidation par formule est toujours mise à jour automatiquement lorsque les données de feuilles de calcul distinctes changent."

Rapport de tableau croisé dynamique

Il est également possible de créer un tableau croisé dynamique à partir de plusieurs plages de consolidation. Cette méthode équivaut à la consolidation par catégorie, mais offre davantage de possibilités pour la réorganisation des catégories.

Analyse de scénarios

Gestionnaire de scénarios

À la différence de la valeur cible qui part du résultat pour trouver des données, le Gestionnaire de scénarios montre comment des données variables agissent sur les résultats.

Créer un scénario

1. Cliquez sur l’onglet « DONNEES », puis sur le bouton « Analyse de scénarios », puis Gestionnaire de scénarios.

2. Cliquez sur « AJOUTER »

3. Dans la fenêtre « Ajouter un scénario »

• Nommez le scénario
• Dans « cellules variables », indiquez la ou les cellule(s) qui seront modifiées par le gestionnaire de scénario
• Cochez « Changements interdits » afin de protéger les cellules variables
• Cochez « Masquer » pour que les cellules modifiées n’apparaissent pas

4. Cliquez sur OK. Dans la fenêtre « Valeurs de scénarios », saisir les différentes valeurs variables pour chaque cellule variable.

5. Cliquez sur OK. Il est possible de créer plusieurs scénarios.

Analyser les scénarios

Avant de lancer l’analyse, il est préférable de renommer les cellules afin de donner meilleure visibilité et compréhension des résultats.

1. Retournez dans le Gestionnaire de scénarios. Sélectionnez un scénario puis cliquez sur « Synthèse »

« Fusionner » permet de fusionner plusieurs scénarios de feuilles de calculs différentes.

En face de « Cellules variables » apparaît TAUX_DE_BAISSE. C’est le nom de la cellule variable renommée.

2. Choisir le type de rapport. • Synthèse de scénario • Scénario du rapport de tableau croisé dynamique (pas traité dans ce chapitre) Définir la plage de cellules à analyser. Cliquez sur OK

3. Analyse de scénarios Le scénario apparaît sous cette forme, dans une autre feuille de calcul.

La cellule à modifier était la F18 « TAUX_DE_BAISSE ». Dans cet exemple, deux scénarios avaient été créés: l’un avec une baisse de 2%, l’autre avec une baisse de 0,50%. Les cellules résultantes telle que « $B$21 » n’ont pas été renommées, contrairement à la cellule « CA_moy_trim1 ». Le résultat est ainsi plus compréhensible.

L’analyse de scénarios peut être modifiée (mise en forme, suppression d’information, etc.)

Valeur cible

« VALEUR CIBLE » permet de parvenir à un résultat souhaité qui est la « cible », par un ajustement de certaines valeurs. Les ajustements de données variables sont effectués par Excel une fois que le résultat souhaité, dit la valeur cible, a été indiqué

Pour atteindre la valeur cible :

1. Cliquez sur une cellule de la plage du tableau
2. Cliquez sur l’onglet Données puis sur le bouton Analyse scénarios. Enfin, cliquer sur Valeur cible.
3. Dans le champ Cellule à définir, saisir la cellule à modifier. La formule qui doit être saisie dans la cellule à modifier doit utiliser la cellule à modifier.
4. Dans le champ Valeur à atteindre, saisir le résultat souhaité, c'est-à-dire la valeur cible.
5. Dans le champ Cellule à modifier, saisir la ou les cellule(s) que le tableur Excel ajustera pour atteindre la valeur cible.
   
   

Dans cet exemple, nous voulons qu’Excel ajuste la cellule B5 « Nombre de mois » afin de calculer le montant des mensualités. Nous voulons rembourser l’emprunt avec des mensualités de 200 euros. Mais nous souhaitons garder comme taux d’intérêt 3.60%. Pour cela, Excel va modifier la valeur de la cellule B5 « Nombre de mois ».

1. Après avoir cliqué sur OK, l’ « État de la recherche » fait apparaître le résultat suivant.
   

Le taux d'intérêt est inchangé. Les mensualités sont passées à 200 euros comme demandé et le nombre de mensualités passé de 108 mois à 54 mois.

Table de données

La table de données permet d’effectuer des calculs sur un ensemble de données. La table de données présente les différents résultats selon des données variables et offre une comparaison rapide des différents résultats.

Pour créer une table de données :

1. Dans une cellule, entrez la formule qui utilise différentes données du tableau.
2. Saisir différentes variables. Par exemple, différents taux d’intérêt.
3. Sélectionnez la cellule comprenant la formule ainsi que les différents variables. Les résultats apparaissent à côté des données variables. Pour cela, sélectionnez également les cases vides à droite des cellules contenant les données variables.
   
4. Cliquez sur l’onglet Données puis sur le bouton Analyse scénarios. Enfin, cliquez sur Table de données.
5. Indiquer la valeur d’entrée, c’est-à-dire la valeur de base qui permettra d’établir différents résultats
   
6. Dans la fenêtre « Table de données », deux choix sont possibles :
   * Cellule d’entrée en ligne : si les variables sont en ligne, alors saisir la référence de la cellule d’entrée en ligne.
   * Cellule d’entrée en colonne : si les variables sont en colonne (comme dans l’exemple ci-dessus), alors saisir la référence de la cellule d’entrée en colonne.
   Dans cet exemple, la cellule d’entrée est la cellule J4 « Taux d’intérêt ». En effet, Excel va remplacer ce taux par les différentes variables (4,00%, 4,50%, etc.) afin d’avoir les différentes mensualités.
7. Cliquez sur OK. Les différents résultats apparaissent dans les cellules vides à droite des variables.
   

Groupement de données

Grouper des données

La fonctionnalité « Grouper » permet d’analyser des données en un sous-ensemble. Cette fonction permet aussi de voir rapidement les sous-totaux. La fonctionnalité « Grouper » regroupe les données par ligne ou par colonne.

Créer un groupe de données

1. Cliquez sur l’onglet « DONNEES »
   
2. Sélectionnez les colonnes ou les lignes à regrouper
   

Attention : les colonnes et les lignes ne peuvent pas être regroupées ensemble
Dans l’exemple, nous sélectionnons les colonnes B à D.

1. Cliques sur le bouton « Grouper » puis sur l’objet "Grouper"
   
2. La fenêtre suivante apparaît :
   

Il est possible de regrouper les données par :
* ligne (exemple : ligne 1 à ligne 5)
* colonne (exemple : colonne A à colonne E)
Cliquer sur le bouton OK
Cela créera un premier niveau de regroupement des données.

1. Les données sont regroupées par niveau :
   * niveau 1 : premier sous-ensemble. Dans l’exemple, le niveau 1 correspond aux colonnes B à C
   * niveau 2 : deuxième sous-ensemble. Il permet par exemple de regrouper des données dans le premier groupe existant (même méthodologie que pour créer un groupe de niveau 1)
   

Une ligne suivie du signe « - » apparaît. En cliquant sur le signe « - », les colonnes ou lignes se regroupent. En cliquant sur le signe « + », les données se dégroupent et les colonnes apparaissent.

Dissocier un groupe de données

La fonctionnalité « DISSOCIER » permet de supprimer les groupes de données (voir chapitre « GROUPER») Pour dissocier, il y a deux méthodes :

• sélectionnez les colonnes ou lignes groupées et cliquez sur « DISSOCIER »
• Ou cliquez sur le signe « + » puis sur « DISSOCIER »

Insérer des sous-totaux

Grâce à Excel, il est possible de calculer automatiquement les sous-totaux et les totaux d’une colonne à l’aide de l’outil « Sous-Total » de l’onglet « Données » (Plan).
Il vous est possible d’effectuer deux actions différentes :

• Insérer un seul niveau de sous-totaux
• Insérer des sous-totaux imbriqués

Attention, pour que la commande « Sous-Total » fonctionne correctement, il faut que :

• Chaque colonne comporte une étiquette dans la première ligne.
• Chaque ligne contienne des informations similaires.
• Aucune ligne ou colonne de la plage de cellule ne soit vide.

Insérer un seul niveau de sous-totaux.

1. Sélectionnez une cellule dans la plage de cellule à laquelle vous voulez ajouter des sous-totaux.
2. Triez la colonne qui forme le groupe pour lequel vous souhaitez faire le sous-total. Vous pouvez vous reporter au chapitre tri pour de l’aide.
3. Dans l’onglet « Données », cliquez sur « Sous-Total ». La boîte de dialogue « Sous Total » va alors s’afficher.
4. Dans la zone « A chaque changement de », sélectionnez la colonne dont vous voulez calculer le sous-total.
5. Dans « Utilisez la fonction », choisissez la fonction que vous voulez utiliser pour calculer les sous-totaux (somme, moyenne…etc)
6. Cliquez sur « OK »

Insérer des sous-totaux imbriqués.

1. Suivre la procédure pour insérer un unique niveau de sous-totaux
2. Effectuez de nouveau cette procédure, en prenant soin de décocher la case « Remplacer les sous-totaux existants » afin de ne pas écraser les anciens sous-totaux.
3. Répétez l’étape (jusqu’à sept fois) afin d’obtenir le nombre de sous-totaux souhaité.

Remarques

• Pour définir un saut de page automatique après chaque sous-total, cochez la case « Saut de page entre les groupes »
• Pour spécifier la ligne de synthèse des sous-totaux au-dessus de la ligne de détails, décochez la case « Synthèse sous les données ». En laissant cette case cochée, la ligne de synthèse apparaîtra en dessous de la ligne de détails.

Formules

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Cette leçon a pour but de présenter les fonctions du tableur Microsoft Excel.

Utiliser des formules en français dans un Excel anglais ou vice-versa est déconseillé, mieux vaut les traduire.

Insertion de fonction

Construction d'une formule

Toutes les lignes de formules sont préfixées par les symboles "=" , "+" ou "-".

En cas de saisie de valeur textuelle, ces symboles n'ont pas lieu d'être, on peut directement écrire la valeur.

Ce peut être une combinaison des éléments suivants :

• Des valeurs constantes, simples ou littérales numériques

1. Les valeurs numériques sont directement précisées Ex : =si(A6=1.5;B6*2;B6*0)
2. Les valeurs textuelles doivent être mises entre des guillemets Ex : =si(A6="toto";"oui";"non")

• Une référence à une valeur contenue dans une cellule ou une plage de cellule. Ainsi il faut juste indiquer l'adresse de la cellule ou la plage. Si Excel rencontre une référence dans une formule, alors il ira prendre la valeur se trouvant à l'endroit indiqué afin de l’utiliser dans les calculs. En cas de cellule vide cela produira la valeur zéro. S'il y a une modification de la valeur alors toutes les cellules qui l'utilisent verront leurs formules recalculées.

Il est possible de saisir les références au clavier , ou de faire un clic avec la souris lors de la saisie de la formule.

L'assistant fonction

Lorsque l’on débute l'édition d'une formule, le bouton assistant fonction apparaît et a une grande utilité (Il est tout à fait possible d'éditer une formule par la commande Insérer une Fonction de l'onglet Formules).

Cet assistant regroupe l’ensemble des fonctions de feuilles de calculs avec la description complète de chacune. Il va donc nous permettre de sélectionner une fonction et de la mettre dans une formule.

Lors de l'appel de l'assistant fonction, là une boite de dialogue s'affiche, elle représente la liste des fonctions. Il ne vous reste plus qu’à choisir la fonction que vous souhaitez mettre en œuvre. Toute fois, cette boite de dialogue contient deux listes : la liste déroulante des catégories (en haut) vous permet de choisir une catégorie dont les fonctions affiliées apparaissent dans la liste du dessous. Au sein de la liste déroulante du haut, la catégorie sélectionnée par défaut, à savoir "les dernières utilisées" fait apparaître dans la liste de droite les dernières fonctions entrées ou encore les plus courantes.

Sous les listes se présente la syntaxe et un court descriptif de la fonction sélectionnée. Lorsque vous avez choisi une fonction dans la liste des fonctions par un double clic, vous pouvez alors préciser l'un après l'autre les arguments nécessaires pour cette fonction. Lors de cette saisie, vous disposez à nouveau du bouton "fx" de manière à imbriquer des appels de fonction les uns dans les autres.

Différents types de fonctions

Vous trouverez dans cette sous-partie, les principales fonctions que propose le tableur Excel, sinon les plus utilisées.

Les fonctions des dates et heures

Fonction	Explication	Exemple
ANNEE (numéro de série)	Affiche l'année correspondante à une date ou un nombre (la première année de référence 01/01/1900 est représentée par le nombre 1 )	La fonction ANNEE de Microsoft Excel
AUJOURDHUI ()	Affiche la date d'aujourd'hui sans heures ni minutes ni secondes	La fonction AUJOURDHUI de Microsoft Excel
JOUR (numéro de série)	Affiche le jour correspondant à une date ou un nombre avec toujours la première année de référence 01/01/1900	La fonction JOUR de Microsoft Excel
JOURS360 (date_début;date_fin;méthode)	Affiche le nombre de jours entre deux dates (en comptant une année de 360 jours, soit 30 jours par mois), l'argument méthode de référence (américaine ou européenne) détermine la méthode de comptage	La fonction JOURS360 de Microsoft Excel
MAINTENANT ()	Affiche la date et l’heure en cours	La fonction MAINTENANT de Microsoft Excel
MOIS (numéro de série)	Affiche le mois correspondant à une date ou un nombre avec toujours la première année de référence 01/01/1900	La fonction MOIS de Microsoft Excel

Les fonctions financières

Fonction	Explication	Exemple
AMORLIN (Coût; valeur résiduelle; durée)	Calcule l'amortissement linéaire d'un bien pour une période donnée	La fonction AMORLIN de Microsoft Excel
INTPER (taux; per; npm; va; vc; type)	Calcule le montant des intérêts d'un investissement	La fonction INTPER de Microsoft Excel
VAN (taux; valeur1; valeur2; ...)	Calcule la valeur actuelle nette d'un investissement en utilisant un taux d'escompte ainsi qu'une série de décaissements (valeurs négatives) et d'encaissements (valeurs positives) futurs.	La fonction VAN de Microsoft Excel

Les fonctions mathématiques et trigonométriques

Fonction	Explication	Exemple
ABS (Nombre)	Renvoie la valeur absolue d'un nombre	La fonction ABS de Microsoft Excel
ALEA ()	Affiche un nombre décimal aléatoire compris entre entre 0 et 1, (depuis la version 2003 il existe une fonction bien plus précise et facile à utiliser la fonction ALEA.ENTRE.BORNES(n;m), par exemple ALEA.ENTRE.BORNES(1;100) renvie un nombre aléatoire compris entre 1 et 100 (variable)	La fonction ALEA de Microsoft Excel
ARRONDI (Nombre; no_chiffre)	Arrondit un nombre au nombre de chiffres indiqué	La fonction ARRONDI de Microsoft Excel
ENT (Nombre)	Arrondit un nombre à l'entier directement inférieur	La fonction ENT de Microsoft Excel
NB.SI (plage; critère)	Renvoie le nombre de cellules non vides répondant à un critère à l'intérieur d'une plage	La fonction NB.SI de Microsoft Excel
NB.VIDE (plage)	Compte le nombre de cellules vides à l'intérieur d'une plage	La fonction NB.VIDE de Microsoft Excel
PRODUIT (nombre1; nombre2; ...)	Calcule le produit de nombres ou de plages de nombres (30 nombres maximum par plage)	La fonction PRODUIT de Microsoft Excel
PUISSANCE (nombre; puissance)	Renvoie la valeur du nombre élevé à la puissance demandée (carré, cube, ...)	La fonction PUISSANCE de Microsoft Excel
RACINE (nombre)	Affiche la racine carré d'un nombre	La fonction RACINE de Microsoft Excel
SOMME (nombre1; nombre2; ...)	Calcule la somme d'une suite de nombres	La fonction SOMME de Microsoft Excel
SOMME.SI (plage; critère; somme_plage)	Additionne des cellules spécifiées seulement si un certain critère est atteint	La fonction SOMME.SI de Microsoft Excel

Pour sommer en fonction de plusieurs colonnes, la fonction SOMME.SI.ENS() est apparue avec Excel 2007, pour être lisible sur les versions antérieures du logiciel mieux vaut donc préférer SOMMEPROD().

Les fonctions de texte

Fonction	Explication	Exemple
CONCATENER (texte1;texte2;...)	Assemble une liste de chaines de caractères pour n'en former une seule par concaténation (les chaines de caractères sont mises bout à bout), cette fonction réalise la même opération que l'opérateur de caractères &, elle est parfois appelée addition de chaines de caractères	La fonction CONCATENER de Microsoft Excel
MAJUSCULE (texte)	Convertit un texte en majuscule	La fonction MAJUSCULE de Microsoft Excel
MINUSCULE (texte)	Convertit un texte en minuscule	La fonction MINUSCULE de Microsoft Excel
NOMPROPRE (texte)	Convertit la première lettre d'un mot en majuscule	La fonction NOMPROPRE de Microsoft Excel

Les fonctions de recherche

Fonction	Explication	Exemple
RECHERCHEV (valeur;table;index;proche)	La fonction RECHERCHEV permet d'afficher une valeur de la même ligne qu'une valeur_cherchée. La valeur_cherchée se trouve dans une colonne de référence. Cette colonne de référence est la première colonne de la table_matrice. Le no_index_col est le numéro de colonne dont on veut afficher le contenu, la colone de référence étant la colonne numéro 1. La [valeur_proche] correspond à la précision : VRAI si l’on veut une valeur proche, FAUX si l’on veut une correspondance exacte (FAUX automatiquement si le champ est non renseigné).	La fonction RECHERCHEV de Microsoft Excel
RECHERCHEH (valeur;table;index;proche)	La fonction RECHERCHEH permet d'afficher une valeur de la même colonne qu'une valeur_cherchée, le tableau correspond à la table de données dans laquelle on effectue notre recherche, le no_index_lig est le numéro de ligne (par rapport à la première ligne du tableau) dont on veut afficher le contenu.	La fonction RECHERCHEH de Microsoft Excel
EQUIV (valeur_cherchee; tableau_recherche;(type))	La fonction EQUIV renvoie la position d'une valeur_cherchee dans un tableau_recherche (un vecteur). Le type de recherche peut être égal à 0 (position de la première valeur exacte rencontrée), 1 (position de la dernière valeur inférieure ou égale à la valeur recherchée) ou -1 (position de la dernière valeur supérieure ou égale à la valeur recherchée)	La fonction EQUIV de Microsoft Excel
INDEX (matrice; no_lig; [no_col])	La fonction INDEX permet de retrouver une valeur dans une matrice en fonction de sa position au sein de cette dernière. Si on effectue cette recherche dans un vecteur, il faudra indiquer le numéro de ligne ou de colonne de la valeur recherchée. Si on recherche dans un tableau à deux dimensions, il faudra à la fois indiquer le numéro de ligne et le numéro de colonne.	La fonction INDEX de Microsoft Excel

Les fonctions de logique

Fonction	Explication	Exemple
ET (valeur_logique1; valeur_logique2; ...)	Affiche "VRAI" si tous les arguments sont vrais	La fonction ET de Microsoft Excel
OU (valeur_logique1; valeur_logique2; ...)	Affiche "VRAI" si un des arguments est vrai	La fonction OU de Microsoft Excel
SI (test_logique; valeur_si_vrai; valeur_si_faux)	Affiche valeur_si_vrai si le test_logique est VRAI, affiche valeur_si_faux dans le cas contraire (si les champs valeur_si_vrai et valeur_si_faux ne sont pas renseignés, VRAI et FAUX s'affichent dans leur cas respectifs)	La fonction SI de Microsoft Excel

Il existe de nombreuses autres fonctions proposées par le tableur Excel, vous pourrez les retrouver par le biais d'un simple clic sur l'icone fx insérer une fonction.

Gestion des noms

Les avantages des noms

Il peut être utile de nommer certaines cellule ou plages de cellules participant à des calculs ou formules plutôt que de garder les appellations standards lettrées en colonnes et numérotées en ligne (par exemple A3:B6). Cette technique apporte plusieurs avantages :

• Clarifier la formule. Exemple : en relecture la formule " Prix_HT*(1+TVA)" qui correspond à un calcul du prix TTC est plus compréhensible que la formule "A6*(1+C14)"
• Insérer de manière élégante le symbole "$" qui correspond au références fixes et relatives, un nom de cellule est traduit en une référence fixe, un nom de plage est une référence relative
• Saisir en semi-automatique des noms dans une formule
• Maintenir des formules est simplifiée par le Gestionnaire de noms

Règles pour créer un nom

Pour créer un nom il faut respecter 4 conditions :

• Le nom doit être composé de moins 255 caractères
• Le nom doit commencer soit par une lettre soit par le caractère soulignement " _"
• Le nom ne doit pas contenir d'espaces ni de signes de ponctuations
• Le nom ne doit pas ressembler à une adresse (A1, C6, L, T, etc.)

Créer un nom

Il existe plusieurs façons de créer un nom sous Excel :

• Sélectionner la cellule ou la plage et saisir le nom de la "zone de nom" ( au dessus de l'intersection des entêtes de lignes et de colonnes)

  

• Sélectionner la cellule ou la plage et effectuer un clic-droit et choisisser l'option "Définir un nom"

• Sélectionner la cellule ou la plage et cliquer sur l'onglet formule du ruban puis sur "Définir un nom"

• Sélectionner la cellule ou la plage et aller dans l'onglet formule et cliquer sur le "gestionnaire de nom" puis nouveau

Une fois le nom créé les modifications se feront via la "gestionnaire de nom" dans l'onglet "Formules" du ruban.

Repérage des antécédents et des dépendants

Parfois il est difficile de comprendre exactement, de détecter la source d'une erreur quand la formule utilise des cellules antécédentes ou dépendantes : Les cellules antécédentes sont désignées par une formule dans une autre cellule. Par exemple, si la cellule A8 contient la formule =C2, la cellule C2 est antécédente à la cellule A8. Les cellules dépendantes contiennent elles des formules qui ont pour référence d’autres cellules. Par exemple, si la cellule C2 contient la formule =A8, la cellule C2 est dépendante de la cellule A8.

Afin de procurer une aide pour la vérification de vos formules, il est possible d’utiliser les fonctions "Repérer les antécédent" et "Repérer les dépendants", qui ont pour utilité, premièrement, la représentation graphique et, ensuite, de pouvoir retracer les relations entre les formules et les cellules, grâce à des flèches d’audit de couleurs.

1. Cliquer sur le bouton "menu" en haut à gauche, puis sur "Options Excel", et enfin sur la catégorie "Options avancées".

2. Dans la partie "Afficher les options pour ce classeur", sélectionner le classeur utilisé, puis contrôler si l’option "Tous" est activée en dessous "Pour des objets, afficher".

3. Si des formules ont pour référence des cellules d’un autre classeur, il faut ouvrir cet autre classeur (Excel ne peut pas accéder à des cellules d’un classeur qui n’est pas ouvert, il ne peut pas aller chercher les informations nécessaires)

4. Les deux actions possibles sont donc :

- Pour repérer les cellules dont les données sont utilisées dans une formule (antécédents) :

1. Cliquer sur la cellule contenant la formule pour laquelle il faut retrouver les antécédents.

2. Pour qu'une flèche d’audit s'affiche pour chaque cellule ce qui permettra de fournir instantanément des données à la cellule active, sous l’onglet "Formules", dans le groupe "Audit de formules", sélectionner "Repérer les antécédents"

Les flèches de couleurs bleues montrent les cellules qui ne contiennent aucune erreur, alors que les flèches rouges montrent celles qui comportent des erreurs. Si la cellule sélectionnée est référencée dans un autre classeur ou par une cellule d’une autre feuille de calcul, une flèche noire s'affichera alors pointant de la cellule sélectionnée vers une icône de feuille de calcul "Icône de feuille de calcul". Par ailleurs, l’autre classeur se doit d’être ouvert lui aussi pour qu’Excel puisse faire le lien et déceler ces dépendances.

- Pour repérer les formules qui font référence à une cellule particulière (dépendants) :

1. Cliquer sur la cellule contenant la formule pour laquelle il faut identifier les dépendants.

2. Pour qu'une flèche d’audit s'affiche pour chaque cellule dépendant de la cellule active, sous l’onglet "Formules" du groupe "Audit de formules", cliquer sur "Repérer les dépendants"

Les flèches de couleurs bleues montrent les cellules qui ne contiennent aucune erreurs, alors que les flèches rouges montrent celles qui comportent des erreurs. Si la cellule sélectionnée est référencée dans un autre classeur ou par une cellule d’une autre feuille de calcul, une flèche noire s'affichera alors pointant de la cellule sélectionnée vers une icône de feuille de calcul "Icône de feuille de calcul". Par ailleurs, l’autre classeur se doit d’être ouvert lui aussi pour qu’Excel puisse faire le lien et déceler ces dépendances.

- Pour afficher toutes les relations dans une feuille de calcul :

1. Dans une cellule vide, taper =(signe égal).

2. Ensuite cliquer sur le bouton "Sélectionner tout" en haut à gauche de la feuille de calcul.

3. Cliquer sur la cellule, ensuite dans l’onglet "Formules", dans le groupe "Audit de formules", double-cliquer sur "Repérer les antécédents".

Pour terminer, s'il faut supprimer l’ensemble des flèches d’audit de la feuille de calcul, sous l’onglet "Formules", dans le groupe "Audit de formules", cliquer sur "Supprimer les flèches".

Traitement des erreurs

Différents types d'erreur rencontrée dans une cellule

Une formule peut parfois renvoyer des types d'erreurs qui sont caractérisées par 7 désignations :

Erreur	Explication	Exemples
#DIV/0!	La formule effectue une erreur de division par zéro (le numérateur est nul)	=265000/(10-10)
#NA	Une formule de recherche a échoué car une des valeurs obligatoires pour trouver un résultat est manquante, ou il existe trop de résultat que n'en peut utiliser une fonction	=SOMMEPROD((A1:A10="dvp")*(B1:B9="number one"))
#NOM?	La fonction contient un mot incompréhensible par EXCEL (en général un nom de plage ou un nom de formule mal orthographié)	=SOME(A1:B2000)
#NOMBRE!	La formule fait référence à une valeur numérique non valide (en général une donnée textuelle)	=MIN("titi";"toto")
#NUL!	Il existe un plage de cellule qui retourne une zone vide ou une intersection de deux zones qui, en réalité, ne se coupent pas	=SOMME(A1 A10)
#REF!	La formule renvoie une adresse incorrecte, elle a certainement été créée avec une référence correcte, mais vers une cellule qui a été supprimée ou qui n'est plus accessible	='C:\dossier\[NomDuClasseurNonTrouvé.xls]NomDeLaFeuilleInconnue'!$A$1)
#VALEUR!	Un opérateur ou un argument au sein de la formule est utilisé avec des valeurs inappropriées	=SOMME(10;a;titi;TRUE)

Quand une cellule affiche un type d'erreur, toutes les cellules qui utilisent son résultat affichent en cascade le même code d'erreur.

Différentes formules de gestion d'erreur

Il existe plusieurs formules de gestion d'erreur qui permettent d’éviter la propagation en cascade en testant l'erreur (et parfois en la corrigeant grâce à la fonction =SI) :

Formule	Explication	Exemples
ESTERR()	Renvoie la valeur "VRAI" si la cellule contient une erreur (différente cependant de l'erreur "#N/A") sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTERR(A1);valeur de remplacement;A1).	Fonction Esterr
ESTERREUR()	Renvoie la valeur "VRAI" si la cellule contient l'indication de n’importe quel type d'erreur sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTERREUR(A1);valeur de remplacement;A1).	Fonction Esterreur
ESTNA()	Renvoie la valeur "VRAI" si la cellule contient l'indication de l'erreur "#N/A" sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTNA(A1);valeur de remplacement;A1).	Fonction Estna
ESTVIDE()	Renvoie la valeur "VRAI" si la cellule testée est vide sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTVIDE(A1);valeur de remplacement;A1).	Fonction Estvide
ESTREF()	Renvoie la valeur "VRAI" si la cellule contient l'indication de l'erreur #REF! (adresse inconnue ou inaccessible) sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTREF(A1);valeur de remplacement;A1).	Fonction Estref

Options de calcul

Les options de calcul permettent de choisir à quel moment le logiciel calcule. Les commandes d'options de calcul se trouvent dans l'onglet "Formules", dans le groupe "Calcul".

Automatique

Permet de recalculer instantanément, suite à chaque modification.

Automatique sauf dans les tables de données

Permet de recalculer instantanément, suite à chaque modification, sauf si celle-ci intervient dans une table de données.

Manuel

Permet de faire des modifications sans que le logiciel ne calcule constamment. Il est possible de demander au logiciel de calculer à chaque fermeture du fichier, afin de disposer des bons chiffres la fois suivante. Pour ceci, il faut cocher "Recalculer le classeur avant de l'enregistrer" qui l’on peut trouver par le chemin Fichier/Options/Formules/Mode de calcul

Dans ce dernier cas, il faut donc indiquer à Excel lorsque l’on souhaite que les calcules se fassent. On dispose donc des 2 boutons suivants :

Calculer maintenant

Permet de calculer lorsque l'option de calcul n’est pas positionnée sur "Automatique"

Calculer la feuille

Permet de calculer la feuille sur laquelle on est positionnée, lorsque l'option de calcul n’est pas positionnée sur "Automatique".

Formules Complexes

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Bonjour !
Bienvenue sur notre page, nous sommes le dimanche 22 décembre 2024

La Fonction SI et ses dérivées

La fonction SI est une fonction EXCEL des plus connues, une des plus simples mais également une des plus compliquées, c’est pour ça qu’il est intéressant de bien comprendre l’utilisation de la fonction SI et de ses dérivées.

Ces fonctions nous permettent d'effectuer des calculs sous conditions.

Dans ce chapitre, nous allons traiter la fonction SI et l’ensemble des dérivées plus ou moins connues.

La fonction SI

Présentation de la fonction SI

La fonction SI est une fonction logique d’Excel. Elle permet l’affichage d’une valeur en fonction des conditions imposées. Elle se présente sous la forme:

SI(Test_logique;Valeur_si_vrai;Valeur_si_faux)

Elle est composée de 3 paramètres obligatoires qui sont :

Paramètre	Définition
Test_logique	Ce paramètre reprend la valeur étudiée
Valeur_si_vrai	Ce paramètre désigne la valeur que doit afficher la fonction si le test de logique est vrai
Valeur_si_faux	Ce paramètre désigne la valeur que doit afficher la fonction si le test de logique est faux

Exemple

Vous souhaitez afficher en face de chaque élève le résultat de validation d’un examen en fonction de la note qu’il a obtenu. Dans ce cas, les paramètres à prendre en compte sont :

	Valeurs
Test_logique	La note inférieure à 10
Valeur_si_vrai	Si la note est inférieure à 10, l'élève va au rattrapage
Valeur_si_faux	Si la note n’est pas inférieure à 10, l'élève valide

• 1ère étape: Cliquez sur la cellule C2

SI 1PNG

• 2ème étape: Insérez une fonction en cliquant sur f(x)
• 3ème étape: Entrez les paramètres suivants:

fonction SI 2 format PNG

• 4ème étape: Faites un copier/coller de la formule dans les cellules C3:C8

SI 3 PNG

Particularités de la fonction SI

La fonction SI peut également être utilisée de manière imbriquée. Cela signifie qu’au sein d’une fonction SI il peut y en avoir plusieurs. Excel accepte de recevoir jusqu’à 64 fonctions SI imbriquées.

Dans notre exemple précédent, les SI imbriqués vont permettre de mettre en évidence des critères supplémentaires. Ainsi, nous allons pouvoir définir des mentions en fonction des notes attribués aux élèves.

Les nouveaux critères impliquent 5 fonctions SI imbriquées :

• Les notes supérieures à 16 : mention très bien
• Les notes comprises entre 14 et 16 : mention bien
• Les notes comprises entre 12 et 14 : mention assez bien
• Les notes comprises entre 10 et 12 : mention passable
• Les notes comprises entre 8 et 10 : rattrapage
• Les notes inférieures à 8 : doublement

Afin d’intégrer ses critères, il convient d’utiliser la formule ET qui permet d’associer plusieurs tests de logique pour la première donnée de la fonction SI. Pour intégrer le SI imbriqué, il faut inscrire au niveau de la « valeur_si_faux » la nouvelle fonction SI. Ainsi la formule va s’écrire en cellule C2 :

=SI(B2>=16;"Très bien";SI(ET(B2<16;B2>=14);"Bien";SI(ET(B2<14;B2>=12);"Assez bien";SI(ET(B2<12;B2>=10);"Passable";SI(ET(B2<10;B2>=8);"Rattrapage";"doublement")))))

SI 4 PNG

Il est possible d'écrire la formule sans la formule ET. En effet dans le premier test Note>=16, si c'est faux, il est inutile de dire dans le second test que la note est inférieure à 16, c'est évident. Donc juste Note >=14 suffit, ce qui permet de raccourcir la formule. et ainsi de suite pour tous les tests. Les résultats sont identiques.

=SI(B2>=16;"Très bien";SI(B2>=14;"Bien";SI(B2>=12;"Assez bien";SI(B2>=10;"Passable";SI(B2>=8;"Rattrapage";"doublement")))))

Cette variante permet d'économiser 40 caractères sur la longueur de la formule !

Également, la fonction SI est souvent combinée avec les fonctions ET() et OU() et leurs dérivées !

La fonction MOYENNE.SI

Présentation de la fonction MOYENNE.SI

La fonction moyenne.si permet de calculer la moyenne des valeurs d’une plage répondant à un critère.

Elle se présente sous la forme :

MOYENNE.SI(plage; critères; [plage_moyenne]

Ses 3 paramètres correspondent à :

Paramètre	Définition	Nécessité
Plage	Ce paramètre va chercher à calculer la moyenne des cellules sélectionnées	Indispensable
Critères	Ce paramètre représente la condition, sous forme de chiffre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer	Indispensable
Plage_moyenne	Ce paramètre représente l’ensemble des cellules dont la moyenne est à calculer sous la condition choisie. Si cet argument est omis, c’est alors l’argument Plage qui est utilisé	Facultatif

Exemple

Vous souhaitez afficher la note moyenne des élèves qui poursuivent leurs études. Pour cela les paramètres à prendre en compte sont :

	Définition	Valeurs
Plage	Sélectionner la plage reprenant le critère de poursuite d’études 0	cellules C2:C10
Critères	Il faut que la réponse affichée soit « oui »	"Oui"
Plage_moyenne	ce sont les notes que l’on veut prendre en compte pour le calcul de la moyenne	cellules D2:D10

• 1 ère étape Cliquez sur la cellule
• 2 ème étape Insérez une fonction en cliquant sur "f(x)"
• 3 ème étape Entrez les paramètres définis précédemment, afin d'obtenir la formule suivante : =MOYENNE.SI(C2:C10;"oui";D2:D10)

MoyenneSI1PNG

La moyenne totale des étudiants poursuivant leurs études est de 12,6.

MoyenneSI2PNG

La fonction MOYENNE.SI.ENS

Présentation de la fonction MOYENNE.SI.ENS

La fonction moyenne.si.ens permet de calculer la moyenne des valeurs d’une plage répondant à plusieurs critères.

Elle se présente sous la forme:

MOYENNE.SI.ENS (plage_moyenne; plage_critères1; critère1; [plage_critères2; critère2]; ...)

Paramètre	Définition	Nécessité
Plage_moyenne	Ce paramètre va chercher à calculer la moyenne des cellules sélectionnées	Indispensable
Plage_critères1	Ce paramètre représente la plage sous la condition choisie en critère 1	Indispensable
Plage_critères2...	Ce paramètre peut être une plage complémentaire associée à la condition du critère 2	Facultatif
Critères1	Ce paramètre représente la condition, sous forme de chiffre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer	Indispensable
Critères2...	Ce paramètre peut représenter une condition supplémentaire que l’on veut respecter pour calculer notre moyenne	Facultatif

Exemple

Vous souhaitez afficher le note moyenne des élèves ayant eu un BAC ES avec une note supérieure à 10

Voici notre tableau de données,

MoyenneSI3PNG

• 1ère étape Cliquez sur la cellule
• 2ème étape Insérez une fonction en cliquant sur "f(x)"
• 3ème étape Entrez les paramètres suivants afin d'obtenir la formule suivante : =MOYENNE.SI.ENS(E2:E10;B2:B10;"ES";E2:E10;">10";D2:D10;">10") comme ci-dessus

	Définitions	Valeurs
Plage_moyenne	Il faut sélectionner la plage des notes que l’on veut prendre en compte pour le calcul de la moyenne	E2:E10
Plage_critères1	Le premier paramètre à prendre en compte c’est le type de BAC, il faut donc sélectionner la plage du type de BAC	B2:B10
Plage_critères2	Le second paramètre à prendre en compte est la note 1 obtenue au BAC	E2:E10
Plage_critères3	Le troisième paramètre à prendre en compte est la note 2 obtenue au BAC	D2:D10
Critères1	La moyenne doit se calculer uniquement pour les BAC ES	ES
Critères2	La moyenne doit comprendre uniquement les notes 1 supérieures à 10	>10
Critères3	La moyenne doit comprendre uniquement les notes 2 supérieures à 10	>10

Ainsi, nous observons que la moyenne des étudiants ayant obtenu un BAC ES et plus de 10 aux notes 1 et 2, est de 14.

MoyenneSI4PNG

La fonction SOMME.SI

Présentation de la fonction SOMME.SI

Cette fonction permet d’additionner des valeurs répondant à un critère dans une plage.

Elle se présente sous la syntaxe suivante:

SOMME.SI(plage_critère;critère;[somme_plage])

Paramètre	Définition	Nécessité
Plage_critères	Ce paramètre correspond à la plage de cellules où le critère sera recherché	Indispensable
Critères	Ce paramètre peut être exprimé sous forme de nombre, d’expression ou de référence de cellule. Il peut aussi être un caractère générique comme le « ? » ou « * ». Ces caractères font référence à un critère ou une chaine de critères quelconque	Indispensable
somme_plage	Ce paramètre additionne toutes les cellules correspondantes au critère dans la plage sélectionnée	Facultatif

Exemple SOMME.SI

Dans cet exemple, nous cherchons à déterminer le nombre de produits vendus. Pour obtenir le nombre de chaises vendues

	Valeurs
Plage_critère	Ce sont les cellules C2:C9 sous le nom "article" qui représentent les articles dans le tableau défini. La formule cherche dans cette plage le critère défini.
Critère	Le critère ici est une référence de cellule qui contient le nom de l’article dont on cherche à avoir la vente. C’est donc le mot chaise que la formule va chercher dans C2:C9.
somme_plage	Ce sont les cellules D2 :D9 sous le nom "quantité achetée" dans le tableau défini. À chaque fois que le mot chaise est inscrit dans C2:C9, la formule additionne toutes les quantités correspondantes.

D'après la syntaxe de la formule et ne reprenant nos paramètres définis ci-dessus, nous rentrons : =SOMME.SI(Tableau1[Article];F2;Tableau1[Quantité achetée])

SommeSI1 PNG

Selon notre exemple, nous obtenons que la quantité totale de chaises vendues est de 6 + 4 + 8 = 18

Particularités de la fonction SOMME.SI

• Si l’argument plage somme n’est pas renseigné alors Excel additionne les cellules de la plage de critère.
• La chaine de caractères a une taille maximale de 255.
• La plage_critère1 et la somme_plage peuvent avoir une taille différente.
• Les critères textes sont obligatoirement exprimés entre guillemets.

La fonction SOMME.SI.ENS

Présentation de la fonction SOMME.SI.ENS

Cette fonction permet d’additionner des valeurs répondant à plusieurs critères dans une plage.

Elle se présente sous la forme:

SOMME.SI.ENS(somme_plage;plage_critères;critères1;[plage_critères2;critères2];…)

Elle est composée de 3 paramètres obligatoires, et d'autres facultatifs:

Paramètre	Définition	Nécessité
somme_plage	Ce paramètre va chercher à calculer la somme des cellules sélectionnées	Indispensable
Plage_critères1	Ce paramètre représente la plage sous la condition choisie en critère 1	Indispensable
Plage_critères2...	Ce paramètre peut être une plage complémentaire associée à la condition du critère 2	Facultatif
Critères1	Ce paramètre représente la condition, sous forme de chiffre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer	Indispensable
Critères2...	Ce paramètre peut représenter une condition supplémentaire que l’on veut respecter pour calculer notre somme	Facultatif

Exemple SOMME.SI.ENS

Dans cet exemple, nous souhaitons savoir combien d’articles ont été vendus par ville. Il y a donc deux critères à respecter. Ces derniers sont des références de cellule.

	Définitions
somme_plage	Ce sont les cellules D2 :D9, représentant la quantité achetée. À chaque fois que les deux critères sont respectés, la formule additionne toutes les quantités correspondantes
Plage_critères1	Ce sont les cellules B2 :B9 qui contiennent la ville où a eu lieu la vente
Plage_critères2 ...	Ce sont les cellules C2 :C9 qui contiennent le produit vendu dans cette ville
Critères1	C’est une référence de cellule qui contient le nom de la ville. Ici ce sont les entêtes du tableau. La formule cherche dans B2 :B9 le nom de la ville
Critères2 ...	C’est une référence de cellule qui contient le nom de l'article. Ici ce sont les lignes du tableau. La formule cherche dans C2 :C9 le nom de l'article.

Afin de répondre à la question, entrons maintenant la formule avec les paramètres ci-dessus:

=SOMME.SI.ENS(Tableau1[Quantité achetée];Tableau1[Ville];G$1;Tableau1[Article];$F2)

NB: L'utilisation des "$" permet de verrouiller les lignes ou les colonnes de notre choix

SommesiENS1 PNG

D'après cet exemple, la quantité totale de chaises vendues à Evry est égale à 6

L'addition n'a lieu que si les deux critères sont strictement respectés

Particularités de la fonction SOMME.SI.ENS

• Le nombre de critères maximum est de 127 (N = 127)
• Les critères textes sont obligatoirement exprimés entre guillemets

La fonction NB.SI

Présentation de la fonction NB.SI

Cette fonction sert à compter le nombre de cellules qui contiennent un critère défini. Elle s'écrit sous la forme:

NB.SI(plage;critère)

Elle se compose de 2 paramètres obligatoires:

Paramètre	Définition	Nécessité
Plage	Ce paramètre correspond à la plage de cellules où le critère sera recherché	Indispensable
Critère	Ce paramètre peut être exprimé sous forme de nombre, d’expression ou de référence de cellule. Il peut aussi être un caractère générique comme le « ? » ou « * ». Ces caractères font référence à un critère ou une chaine de critères quelconque	Indispensable

Exemple NB.SI

Dans cet exemple, nous souhaitons savoir combien de fois des ventes ont eu lieu dans une ville. Ce qui revient à compter le nombre de fois que la ville apparaît dans le tableau

	Définitions
plage	Ce sont les cellules B2 :B9 représentant les différentes villes. Ce sont elles qui seront comptées si elles respectent le critère.
Critère	C’est une référence de cellule qui correspond au nom de la ville.

Ainsi, en rentrant la formule avec les paramètres définis: =NB.SI(Tableau1[Ville];F2)

NbSI1PNG

Ici, nous pouvons voir qu’il y a eu 3 ventes à Paris

Particularités de la fonction NB.SI

• Le critère peut être lui-même une formule
• Les plages ne doivent pas être nécessairement consécutives.

La fonction NB.SI.ENS

Présentation de la fonction NB.SI.ENS

Cette fonction sert à compter le nombre de cellules qui contiennent des critères définis.

Elle se définit ainsi:

NB.SI.ENS(plage_critères1;critères1;[plage_critères2;critères2];...)

Paramètre	Définition	Nécessité
Plage_critères1	Ce paramètre représente la plage sous la condition choisie en critère 1	Indispensable
Plage_critères2...	Ce paramètre peut être une plage complémentaire associée à la condition du critère 2	Facultatif
Critères1	Ce paramètre représente la condition, sous forme de chiffre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer	Indispensable
Critères2...	Ce paramètre peut représenter une condition supplémentaire que l’on veut respecter pour calculer notre somme	Facultatif

Exemple NB.SI.ENS

Dans cet exemple, nous souhaitons savoir combien fois ont été vendus des produits dans une ville. Cela revient à compter le nombre de fois où la combinaison ville-produit apparaît dans le tableau.

	Définitions
Plage_critères1	Ce sont les cellules B2 :B9 correspondant aux différentes villes. Ce sont elles qui seront comptées si elles respectent le critère.
Plage_critères2 ...	Ce sont les cellules C2 :C9 représentant les articles. Ce sont elles qui seront comptées si elles respectent le critère.
Critères1	C’est une référence de cellule qui correspond au nom de la ville
Critères2 ...	C’est une référence de cellule qui correspond au nom de l'article

La formule à insérer dans la cellule pour les différentes villes est donc : =NB.SI.ENS(Tableau1[Ville];G$1;Tableau1[Article];$F2)

NbsiENS1PNG

Selon nos résultats, nous pouvons déduire qu'aucune table n'a été vendue à Evry ou bien qu'un seul lit a été vendu à Étampes !

Particularités de la fonction NB.SI.ENS

• Les critères peuvent être eux-mêmes une formule
• Les plages ne doivent pas être nécessairement consécutives

La fonction SIERREUR

Présentation de la fonction SIERREUR

La fonction renvoit à une valeur spécifiée si une formule contient une erreur sinon elle indique le résultat de la formule. Elle permet donc de détecter et de traiter les erreurs.

Elle s'écrit:

SIERREUR(Valeur;Valeur_si_erreur)

Elle se compose de 2 paramètres obligatoires:

Paramètre	Définition	Nécessité
Valeur	C’est la formule ou l’argument vérifié.	Indispensable
Valeur_si_erreur	C’est la valeur à afficher si la formule contient une erreur. Elle peut être exprimée sous forme de nombre, d’expression ou de référence de cellule	Indispensable

Exemple SIERREUR

SIerreur PNG

Dans cet exemple, nous souhaitons afficher l’expression « Pas de table basse vendue cette année » lorsque la fonction RechercheV ne retrouve pas la valeur cherchée « table basse » qui se situe dans la cellule F2.

Pour cela il faut entrer la formule suivante : SIERREUR, en prenant en compte les paramètres suivants :

	Définitions
Valeur	RECHERCHEV(F2;C:C;1;Faux)
Critère	"Pas de table basse vendue cette année"

De cette manière nous avons immédiatement le résultat de la recherche et l’interprétation de l’erreur s’il y en a une.

Pour tester ses connaissances → QCM Fonction SI et ses dérivées
Pour s'entraîner avec des exercices → Exercice Fonction SI et ses dérivées

BBH2M (discussion)

Association Wikimédia France

Formules Index Equiv

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

RECHERCHEV ET RECHERCHEH

Présentation des fonctions RechercheV et RechercheH

Les fonctions de recherche d'Excel permettent de faire exécuter au tableur une recherche dans un tableau de valeurs et de renvoyer la valeur trouvée dans une cellule déterminée à l'avance. On pourra, par exemple, faire trouver par Excel la désignation et le prix unitaire d'un produit en saisissant simplement sa référence. Comme le fait l'ordinateur central d'un hypermarché quand il renvoie la référence et le prix d'un produit dont le code-barres a été lu en caisse ou saisi par la caissière. Cette fonction peut avoir de nombreuses applications : facture, bulletin de salaire, base de données...

L'abréviation de V signifie verticale et celle du H horizontale.

La RechercheV permet de rechercher des données en colonne et la RechercheH recherche des données en lignes.

Syntaxes

Les syntaxes générales des RechercheV et RechercheH sont les suivantes :

'=RECHERCHEV(valeur_cherchée, table_matrice, no_index_col, [valeur_proche])'

• valeur_cherchée : il s'agit de la cellule où Excel lit la valeur qui entraîne la recherche (exemple : la référence d'un produit conduit à la recherche de son prix et de sa désignation).

• table_matrice : il s'agit de la plage de cellules où Excel recherche les valeurs (exemple : un tarif comportant prix et désignations des produits).

• 'no_index_col' : il s'agit du numéro de la colonne du tableau où Excel doit trouver la valeur cherchée (exemple : les prix se trouvent dans la troisième colonne du tarif).
• valeur_proche : Cette information est facultative. Il s'agit de rentrer FAUX si nous voulons une correspondance exacte ou VRAI si nous voulons une correspondance approximative

=RECHERCHEH(valeur_cherchée, table_matrice, no_index_lig, [valeur_proche])

• valeur_cherchée : Obligatoire. Représente la valeur à rechercher dans la première ligne de la table. Il peut s’agir d’une valeur, d’une référence ou d’une chaîne de texte.
• table_matrice : Obligatoire. Représente la table de données dans laquelle est exécutée la recherche de la valeur. Utilisez une référence à une plage ou un nom de plage.
• no_index_lig : Représente le numéro de la ligne du tableau où Excel doit trouver la valeur cherchée.

• valeur_proche : Cette information est facultative. Il s'agit de rentrer FAUX si nous voulons une correspondance exacte ou VRAI si nous voulons une correspondance approximative

INDEX

RECHERCHEV c’est faire du vélo avec les roulettes. INDEX + EQUIV, c’est conduire votre première voiture.

La formule INDEX vous permet de retourner la valeur qui se trouve à la place que vous souhaitez dans une plage de cellule.

La syntaxe générale d'index est la suivant :

=INDEX(matrice; no_lig; [no_col])

• Matrice : Elle correspond soit à une plage de cellule soit une constante. C’est un élément indispensable au fonctionnement de la formule.
• No_lig : Le numéro de ligne permet de sélectionner la valeur qui doit être renvoyé. C’est un élément indispensable pour le fonctionnement de la formule.
• No_col : le numéro de colonne permet de sélectionner la valeur qui doit être renvoyé. Néanmoins, cet élément n’est pas un élément primordial pour le fonctionnement de la formule.

Si la matrice ne possède qu’une seule ligne, le numéro de la colonne n’est pas utile. A contrario, si le numéro de la ligne n’est pas utile, il faut quand même le marquer d’un 0.

Exemple 1 :

Exemple 1 - INDEX

Avec un peu d’automatisation, il n’y a plus besoin de toucher à la fonction. Elle dépendra seulement d’une cellule, avec un menu déroulant par exemple.

Exemple 2 :

Exemple 2 - INDEX

Nous pouvons rendre la fonction Index beaucoup plus intéressante grâce à la fonction EQUIV.

Néanmoins, la fonction INDEX et RECHERCHEV ou H est proche à tel point d’obtenir le même résultat.

INDEX ET EQUIV

La fonction Excel EQUIV recherche la position d'une valeur dans une plage de cellules^.

La syntaxe générale d'Equiv est la suivant :

= EQUIV (valeur_recherchée;plage;type)

• Valeur_recherchée : il faut y inscrire la valeur dont nous voulons connaitre la position. Cet élément est obligatoire dans la formule.
• Plage: elle doit contenir la position que nous recherchons. Cet élément est obligatoire dans la formule. De plus, la plage doit être dans l'ordre croissant ou décroissant suivant le "type".
• Type: 3 valeurs possibles : le 0 pour une valeur exacte; le 1 correspond à la valeur inférieur la plus proche de la valeur recherchée. Le -1 correspond à la valeur supérieur la plus proche de la valeur recherchée.

Exemple 3 :

Exemple 3 - INDEX EQUIV

Pour obtenir le CA du dossier 4, avec la fonction INDEX et RECHERCHEV, voici les formules respectives :

• =INDEX(C2:C11;EQUIV(E3;B2:B11;0);1)
• =RECHERCHEV(E3;B2:C11;2;FAUX)
• Nous arrivons donc au même CA : 10 693 €

Néanmoins, la différence commence à se voir si nous voulions chercher un numéro de dossier par rapport à un lieu . Voici les deux formules :

• Pour index =INDEX(A2:C11;EQUIV(E3;B2:B11;0);1)
• Pour la recherche : =RECHERCHEV(E3;A2:C11;-1)

Les résultats sont différents. INDEX nous donne 4 ce qui est correct, tandis que RECHERCHE nous donne #N/A. Voici la première limite de cette fonction, la valeur cherchée doit être dans la première colonne.

L’intérêt d’utiliser la fonction Index et EQUIV est de les assembler au total. C’est-à-dire de remplacer le numéro de ligne et le numéro de colonne sera recherché par la fonction EQUIV.

Exemple 4 :

Exemple 4 - INDEX EQUIV

Ici encore, une RECHERCHEV suffirait car le résultat est identique à celui de la formule INDEX et EQUIV.

• Index =INDEX($B$2:$H$13;EQUIV(D17;Mois;0);EQUIV(A17;Villes;0))
• RechercheV = RECHERCHEV(D17;A2:H13;6;FAUX)

Néanmoins, le numéro de la colonne de la RECHERCHEV est une valeur fixe, ce qui signifie que si nous intégrons une nouvelle colonne, la valeur que nous recherchons, ne sera plus la bonne. Ce ne sera pas le cas avec l'utilisation de la fonction INDEX avec la fonction EQUIV car nous demandons à la fonction EQUIV d’aller chercher dans le tableau, le titre de la colonne correspondant à notre recherche.

Exemple 5 

Exemple 5 - INDEX EQUIV

Liaison

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Liaison par les noms

• Les cellules ou plages de cellules peuvent être nommées
  • Sélectionner une plage
  • Définir un nom
  • Vérifier adressage relatif ou absolu
• Les fonctions peuvent s'appliquer aux cellules ou plages nommées
  • utilisation standard = fonction(plage)
  • utilisation via nom = fonction(plage nommée)

Plus qu'une référence absolue, un nom donné à une cellule, ou mieux à une plage de cellules, permet de manipuler les données de façon plus compréhensible. Ce nom pourra être utilisé, à l'avenir, dans les formules de façon absolue, s'il s'agit d'une cellule unique, ou même relative pour un groupe de cellules. Il pourra surtout être utilisé comme argument dans une fonction.

Liaison par les cellules

• Le Classeur
  • Un classeur EXCEL est un fichier Windows
  • Chaque classeur gère des feuilles de calcul ou de graphe
• La liaisons dynamique (=cellule liée)
  • Liaison dans la même feuille ==> =B2
  • Liaison dans le même classeur ==> ='liaisons feuilles'!D3
  • Liaison via un autre classeur ==> ='[démonstrations 8 sources pour liaisons.xlsx]liaisons cellules'!C2
• Actualisation des données
  • Directe si cellule liée via même feuille ou même classeur
  • Si cellule liée via classeur différent
    • Directe si les 2 classeurs sont ouverts
    • Avec alerte si le classeur source a été modifié alors que le classeur cible était fermé

Leçon sur les liaisons Excel

I) Qu’est-ce qu’une liaison ?

La liaison est un lien entre cellules, feuilles ou classeurs indiqué par une ou plusieurs références.

Il existe les références internes et les références externes

II) Pourquoi faire une liaison ?

La liaison nous est utile dans certains cas, par exemple lorsque l’on doit travailler sur plusieurs fichiers, et qu’ils doivent être mis à jours, régulièrement. Le classeur qui aura la liaison, aura ses données mis à jour automatiquement, avec la même présentation, mise en forme etc. …

III) Comment faire des liaisons, les modifier et les supprimer

a) La création des liens

Un lien est créé à chaque fois que nous créons une formule avec des références provenant :

♦ D'autres cellule

♦ D’autres feuilles (il faut sélectionner l’onglet de la feuille source où l’on souhaite trouver la donnée à relier puis sélectionner la cellule)

♦ D’autres classeurs (il faut ouvrir un classeur et sélectionner l’onglet de la feuille source  puis la cellule de la feuille voulue)

♦ D’autres fichiers, qui peuvent se trouver sur notre ordinateur, ou sur le Web

On commence par saisir le signe « = » dans une cellule après l’avoir sélectionné (cellule active).

b) La modification des liens

Voir grand 8

c) La suppression des liens

Voir grand 5 (rompre un lien)

IV) Les différents types de liaisons par référence interne :

a) Liaisons entre cellules

Cellules de mêmes feuilles :

Dans une cellule active on tape le signe « = » et on sélectionne la cellule où la donnée significative se trouve pour pouvoir créer une liaison et de ce fait reporté l’information.

Ex : Dans la cellule A1, on a le chiffre 5 et on souhaite le reporter dans la cellule A3.

On sélectionne la cellule A3 et on la rend donc active on tape « = » dans la barre de formule « fx » et on vient sélectionner la cellule A1, on appuie sur entrée.

En A3 on retrouve « =A1 ».

On peut aussi sélectionner une plage de cellules et lui donné un nom, on effectue les mêmes étapes, mais cette fois avec le nom de la plage de cellules.

Ces étapes marchent aussi avec les fonctions, par exemple la fonction somme.

b) Liaisons entre feuilles :

Cellules feuilles différentes:

Dans une cellule active on tape le signe « = » et on sélectionne la feuille où la cellule qui concerne la donnée significative se trouve pour pouvoir créer une liaison et de ce fait reporté l’information.

Ex : Dans une cellule A1 de la « Feuill1 », on a le nom « JeanAstronaute » et on souhaite le reporter dans la « Feuill2 » en cellule D1.

On sélectionnera la cellule de destination « D1 », on tapera dans la barre de formule « fx » un « = » pour commencer et ensuite on sélectionnera l’onglet « Feuill1 » puis la cellule source « A1 » et on appuiera sur entrée.

En D1 de la feuille 2 on aura donc « = Feuil1!A1 » ou si l’on a renommé la cellule A1 en « Nom » on aura « =Nom » car la cellule sera à caractéristique unique.

V) Les différents types de liaisons par référence externe :

a) Liaisons entre classeurs

Nous pouvons lier les données entre classeurs différents, le principe est très proche de celui du lien entre feuille différentes d’un même classeur excepté que les classeurs sont deux fichiers distincts.

La liaison entre classeur nous permet d’’utiliser des informations qui se trouvent dans un autre classeur.

La manière la plus courante est  d’utiliser cette façon de faire :

Il faut ouvrir deux classeurs Excel en ouvrant un premier fichier, en cliquant sur « fichier », « ouvrir» et en sélectionnant le classeur à ouvrir.

Dans une cellule par exemple A3 on écrit :

« = » On ouvre l’autre classeur, et l’on clique sur une cellule.

(Exemple avec un classeur « TEST » et « Liaison »)

This file is used for the lesson of the "liaison".

Quand l’autre classeur est fermé la formule devient :

This file is used for the lesson of the "liaison".

Il faudra alors rouvrir le classeur si l’on souhaite mettre les liaisons à jours, le message suivant apparaitra :

This file is used for the lesson of the "liaison".

Si l’on clique sur Mettre à jour, alors les données seront mise à jours, sinon non. Si les deux classeurs sont ouverts en même temps, les données seront mises à jour automatiquement.

On peut retrouver les liens entre classeurs dans l’onglet « données », et « modifier les liens »

This file is used for the lesson of the "liaison".

Attention: Il faut que les deux classeurs soient ouverts dans le même programme. 

Exemple :

Deux Excel d’ouverts ce n’est pas bon !

This file is used for the lesson of the "liaison".

Un Excel avec deux classeurs cela est bon !

This file is used for the lesson of the "liaison".

Pour passer d’un classeur à l’autre si votre écran est trop petit où qu’il y ait beaucoup trop de classeurs d’ouverts, vous pouvez aller dans :

Affichage         →          Changer de fenêtre         →         Choisir son classeur

This file is used for the lesson of the "liaison".

Attention: ll ne faut pas modifier l’emplacement du fichier

b) Liaison avec Power point

Une feuille entière :

La liaison des données d’une feuille de calcul Excel dans un fichier de type Power Point est possible. De plus, si les données changent vous pouvez également les mettre à jour.

Il s’agit de lier les données Excel (tableau, graphiques…) pour le plus souvent les mettre à jour plus rapidement.

Dans votre présentation PowerPoint, il faut aller dans l’onglet « Insertion » puis cliquer sur « Objet ».

Une boite de dialogue s’affiche comme suit :

This file is used for the lesson of the "liaison".

Il faut sélectionner « À partir d’un fichier », il faut cliquer ensuite sur parcourir et rechercher le classeur voulu pour lier les données à votre présentation.

Une fois le fichier sélectionné, cochez la case « liaison » et cliquez sur « Ok »

Des données précises :

Dans le classeur Excel, il faut sélectionner la plage de données à copier, puis cliquer sur copier et ensuite retourner sur le fichier power point, sélectionner le slide concerné, faites un clic sur « Coller » dans l’onglet « Accueil » et prenez « Collage spécial ».

Une boîte de dialogue s’affiche dans le collage spécial, il faut sélectionner « Coller le lien » et dans en tant que, il faut sélectionner « objet de feuille de calcul Microsoft Excel »

Cliquez sur « OK »

Avec la commande « Rechercher » :

Il faut faire appuyer sur la touche CTRL et la touche F en même temps, cela affiche une boîte de dialogue et en cliquant sur option on obtient un affichage comme suit :

This file is used for the lesson of the "liaison".

S’il s’agit d’une liaison externe on commence par saisir « [ » dans la barre correspondant au « rechercher ».

Pour afficher les liaisons externes et internes il faut mettre un « ! » dans la barre « rechercher ».

Ensuite dans la liste déroulante « dans : » on a le choix entre Feuille ou classeur selon si les liaisons recherchées sont internes ou externes, on clique sur « rechercher tout » et soit les références internes ou externes soit les deux s’affiche sous forme de liste dans la boîte de dialogue.

VI) Les liaisons par fonctions internet

Excel est doté de fonctions internet. Il existe donc des liens qui renvoient vers des sites internet, vers une autre feuille de calcul ou vers un autre classeur.

Lien vers un site internet

Création :

On peut créer un lien vers un site internet à partir d’une cellule active Excel.

Dans un premier temps il faut cliquer dans la cellule qui doit contenir le lien hypertexte, puis saisir l’adresse internet.

Ex : je saisis dans la cellule active : https://www.wikiversity.org  ou www.wikiversity.org  

En appuyant sur entrée le lien se convertit automatiquement en lien hypertexte, vous pouvez ensuite cliquer sur le lien pour avoir accès aux sites internet.

Affectation d’un lien :

Il faut pour créer un lien utile à partir d’un nom donné dans une cellule, dans un premier temps cliqué sur la cellule.

Il faut ensuite aller dans l’onglet « insertion » puis sur « lien hypertexte », une boite de dialogue s’affiche comme suit :

This file is used for the lesson of the "liaison".

L’onglet « Fichier ou page Web existant(e) » est déjà sélectionné, il faut donc saisir ou copier et coller le lien internet pour limiter les erreurs sur la ligne « adresse » en bas de la boite de dialogue et cliquer sur « Ok » pour valider et créer un lien hypertexte relier à un nom

Ex : Si on clique sur Wikiv cela lancera la page internet  « www.wikiversity.org »

Lien vers une cellule

Il faut pour créer un lien à partir d’une cellule active vide.

Il faut ensuite aller dans l’onglet « insertion » puis sur « lien hypertexte », une boite de dialogue s’affiche comme suit :

This file is used for the lesson of the "liaison".

Il faut aller sur l’onglet « emplacement dans ce document » car on part du principe qu’on lie deux cellules du même classeur mais de feuilles différentes.

On sélectionne la feuille dans référence de cellule dans laquelle est l’information à afficher par le lien et en haut dans « texte à afficher » on change le nom de la cellule qui permet d’identifier l’information.

On clique sur « Ok » pour valider.

Lien vers un autre classeur

Il faut suivre les mêmes étapes que la partie « Lien vers un site internet » dans la sous partie « affectation d’un lien », jusqu’à l’affichage de la boite de dialogue.

Ensuite il faut sélectionner « fichier ou page web existante », et rechercher le document à lier dans la liste ou copier/coller la destination dans « adresse » en bas de boite de dialogue.

On peut renommer le lien de la cellule dans « Texte à afficher », validez en appuyant sur entrée.

Quand vous cliquerez sur le lien, cela ouvrira le fichier.

Lien vers la messagerie

Après avoir inscrit un nom ou un nom de société dans une cellule, sélectionner la cellule, cliquer dans l’onglet « insertion » sur lien hypertexte,  la boite de dialogue s’affiche il faut cliquer en bas à gauche sur « adresse de messagerie » et saisir l’adresse dans « Adresse de messagerie » pour ensuite valider par « Ok ».

En cliquant sur l’adresse une fenêtre Outlook s’ouvrira.

VII) Les liaisons par le collage:

On souhaite copier et coller une cellule dans la même feuille, une autre feuille ou un autre classeur tout en gardant la cellule de référence.

On copie le contenu de la cellule de départ et on colle dans une autre cellule en faisant clique droit et en sélectionnant  « collage avec liaison »

This file is used for the lesson of the "liaison".

VIII) La mise à jour des liaisons Excel:

Lorsque l’on créée des références externes (liens), l’on peut contrôler si elles sont toujours à jour et à quel moment. Si la valeur d’une cellule a été modifiée, Excel ne recherchera pas la nouvelle valeur à moins de lui en faire la demande.

a) La mise à jour des liens en relation avec d’autres classeurs :

Quand les classeurs de destination (lié au classeur source) et sources (autres classeurs) sont ouverts en simultané, la mise à jour des liens se font automatiquement.

Quand un classeur de type destination est ouvert mais que le classeur de type sources n’est pas ouvert, Excel demande une autorisation pour mettre à jour les liens.

La mise à jour peut se faire manuellement si la demande ne s’affiche pas et donc mettre à jour seulement les liens qui vous intéressent.

b)La mise à jour manuelle des liens dans un classeur :

• 1)    Fermez tous les classeurs. Si un classeur de destination est ouvert et tous les autres fermés, la mise à jour sera inégale.
• 2)    Il faut ouvrir le classeur contenant les liens.
• 3)    Pour mettre à jour les liens de la barre de confidentialité, cliquez sur Options, puis sur Activer le contenu.

c)Mettre à jour manuellement certains liens dans d’autres classeurs :

Les deux premières étapes sont les mêmes que précédemment.

• 3)     Dans l’onglet « Données » et dans la rubrique « connexions », cliquez sur Modifier les liens d’accès puis « mettre à jour les valeurs »

IX) Pour aller plus loin : Les liaisons par macro VBA

Pour une liaison avec 2 classeurs:

Etape 1 : Ouvrir un fichier ->  

This file is used for the lesson of the "liaison".

Etape 2 : Effectuer un lien entre les deux classeurs ->

This file is used for the lesson of the "liaison".

Ou alors remplacer R9C1 par le nom d’une cellule:

This file is used for the lesson of the "liaison".

ActiveCell.FormulaR1c1 = "=Paris.xlsm!ICI"

Attention, si le fichier n’est pas ouvert cette macro ouvrira une fenêtre pour demander l’emplacement du fichier « Paris ». Une fois le fichier « Paris » sélectionner, la macro prendra la cellule demandé soit :

-         Dans La feuille 1 la cellule A9

L’utilité est de pouvoir reporter tous les totaux dans un autre classeur en ayant juste a appelé le nom de la cellule dans la macro (par exemple on remplacerait le nom « D9 « par « Total1 »,et « K10 » par « Total2 », alors la macro nous rapporterait le « Total 1 » dans A1 et le « total2 » en A2 du classeur 2

Etape 3 : Mettre à jour les fichiers ->

This file is used for the lesson of the "liaison".

Etape 4 : Supprimer les liens ->

This file is used for the lesson of the "liaison".

Les données sont maintenant collées en tant que valeurs, et ne seront plus modifiées.

Solveur

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Présentation des termes : Solveur et Valeur Cible

Qu'est-ce que le Solveur ?

Le solveur est une fonction présente dans EXCEL. C'est un outil très puissant qui permet à la fois d'optimiser et d'allouer des ressources. Cet outil est souvent utilisé pour résoudre des équations. En effet, il permet de trouver le minimum, le maximum ou la valeur la plus proche d'une donnée tout en respectant les contraintes que l'on a émise. Le solveur a donc le pouvoir de donner la meilleure solution, c'est-à-dire l'optimum.

En règle générale, le solveur est utilisé lorsque l'on recherche la valeur optimale d'une cellule donnée (la fonction économique) par l'ajustement des valeurs d'autres cellules (les variables) en respectant des conditions limitées par des valeurs numériques (les contraintes).

• La programmation linéaire avec le Solveur

Un programme linéaire est un problème d'optimisation de la forme c'est-à-dire maximiser ou minimiser une expression linaire en les variables (X1, X2,...,Xn) sous des contraintes. Le Solveur d'EXCEL peut résoudre ces problèmes par l'algorithme simplexe. Le simplexe est une méthode de calcul basée sur la méthode de Gauss-Jordan pour la résolution de systèmes d'équations linéaires.

• La programmation non linéaire avec le Solveur

Lorsque le modèle n’est pas linéaire, le Solveur doit tenter dans ce cas, de trouver une solution par approximations successives appelées "itérations".

Qu'est-ce que la Valeur Cible ?

La Valeur Cible est une fonction d'EXCEL qui a le même objectif que le Solveur mais sans les contraintes. Cet outil est utilisé lorsqu'on veut qu'une cellule atteigne une valeur particulière. La cellule doit obligatoirement contenir une formule. La Valeur Cible a besoin de 3 paramètres pour fonctionner :

• la référence de la cellule pour laquelle on veut affecter la valeur particulière
• la valeur déterminée pour cette cellule
• la cellule variable qui doit être modifiée pour atteindre la valeur cible.

Procédures à suivre sur Excel

Comment utiliser le Solveur ?

Il est possible que la fonction "Solveur" ne soit pas activée dans Excel.

Pour activer le Solveur dans Excel 2007/2010, il faut faire la manipulation suivante :

Menu => Fichier => Options => Compléments. Dans les compléments, cliquez sur "Atteindre" dans "Gérer les compléments Excel". Dans la fenêtre qui s'affiche, cochez "Complément Solveur". L'option "Solveur" apparaît dorénavant dans le menu "Données".

Ensuite, il faut paramétrer le solveur :

• Dans la zone "Cellule cible à définir", il faut déterminer la cellule pour laquelle on souhaite maximiser ou minimiser, c'est-à-dire la fonction économique. Dans ce cas, plusieurs choix sont possibles : maximiser "max", minimiser "min" ou alors si l'on veut que la cellule cible se rapproche le plus d'une valeur, il faut choisir le bouton "valeur" et indiquer la valeur souhaitée dans la zone à droite du bouton.

Quelques remarques :

- pour aller plus vite, cliquer directement dans la cellule cible plutôt que de taper sa référence au clavier.

- la cellule cible doit contenir une formule dépendant directement ou indirectement des cellules variables définies dans la zone "cellules variables"*.

• Dans la zone "Cellules variables", il faut déterminer les cellules pour lesquelles les valeurs peuvent être modifiées par le solveur en prenant en compte les contraintes et pour que la cellule cible atteigne le résultat attendu.

Quelques remarques :

- pour aller plus vite, cliquer-glisser sur les cellules variables plutôt que de les saisir au clavier.

- il est possible que le solveur propose automatiquement les cellules variables par rapport à la valeur cible. Attention à bien contrôler que les cellules définies soient les bonnes.

- pour information, il est possible de spécifier jusqu'à 200 cellules variables

• Pour définir les contraintes, il faut s'aider des boutons "ajouter", "modifier" ou "supprimer" de la boîte de dialogue pour établir la liste des contraintes dans la zone "contraintes".

Quelques remarques :

- la cellule à laquelle l'étiquette "cellule" fait référence contient habituellement une formule qui dépend des cellules variables.

- le solveur gère jusqu'à 200 contraintes.

• Une fois tous les paramètres du solveur renseignés, appuyer sur le bouton "résoudre" ce qui permet de résoudre le problème et d'obtenir le résultat. Les résultats du solveur peuvent être gardés ou rétablis par les valeurs d'origines.
• Il est possible de créer le rapport du solveur en sélectionnant celui qui nous concernera. Le rapport sera présent en bas de l'écran. Il donne l'évolution des cellules variables et de la cellule cible et rappelle également les différentes valeurs des contraintes, leurs formules et dans quelle mesure elles ont été respectées. Lorsque l'état est "lié" cela signifie que la valeur finale de la cellule contenant une contrainte atteint la valeur maximum. Quand l'état est "non lié" cela correspond au fait que la contrainte est respectée mais la valeur finale de la cellule n'est pas égale à la valeur minimum ou maximum de la contrainte.

Comment utiliser la Valeur Cible ?

Pour utiliser la fonction "Valeur Cible", il faut faire la manipulation suivante :

Menu => Données => Analyse de scénarios => Valeur Cible

Exemple : comment résoudre un problème donné ?

Exemple Solveur

Un commerçant a vendu 500 produits. Chacun d'entre eux est vendu 25 €. Leur prix d'achat était de 15 €. Le commerçant souhaite augmenter sa marge jusqu'à 15 000 €. Au vu de la concurrence, il sait qu'il ne pourra pas vendre plus de 700 produits et qu'aucun concurrent ne les vend plus de 40 € pièce. Ses fournisseurs, eux, n'augmenteront pas leur prix au-dessus de 10 €. Le solveur va donc agir sur les variables colorées afin de modifier la marge.

Etape 1 :

Objectif à atteindre : sélection de la cellule affichant le montant de la marge

Valeur : 15000

Cellules variables : quantités vendues, prix de vente et prix d'achat unitaire.

Les contraintes : On va limiter la quantité vendue à 700 produits, le prix de vente unitaire à 40 € et le prix d'achat unitaire à 10 €. Pour ajouter une contrainte, cliquez sur "Ajouter".

Limitation du prix de vente unitaire :

- on sélectionne la cellule correspondant au prix de vente

- on fixe la limite inférieure ou égale à 40

- il faut ajouter les contraintes pour la quantité et le prix d'achat Pour la quantité, on veut que la valeur soit inférieure ou égale à 700. Pour le prix d'achat, il faut que la valeur soit égale ou inférieure à 10 €.

Une fois la saisie de toutes les contraintes, on clique sur "Résoudre". Le solveur propose alors une solution et vous demande si vous souhaitez conserver sa solution ou rétablir les valeurs d'origines. Vous pouvez, en cochant la case correspondante, retourner aux paramètres pour effectuer des modifications.

Voici les résultats donnés par le solveur pour cet exemple :

Exemple Valeur Cible

Un élève a besoin d'arriver à une moyenne de 14 pour pouvoir obtenir son examen. Sur 4 notes, il lui en manque une pour former sa moyenne générale. Il va essayer de calculer quelle note il doit obtenir pour avoir 14 de moyenne générale.

Attention, la cellule E3 qui comprend la note moyenne et qui devra atteindre 14, doit comporter une fonction, dans ce cas, il s'agit de la formule de la moyenne. Sinon la valeur cible ne pourra pas s'appliquer.

Cellule à définir : celle qui contient l'objectif à atteindre, en l’occurrence ici, c'est la note moyenne.

Valeur à atteindre : valeur que devra atteindre la note moyenne, ici, il faut obtenir 14.

Cellule à modifier : c'est la cellule qui permettra d'atteindre la moyenne de 14, nécessaire à l'élève pour valider son examen. Dans cet exemple, il s'agit de la note d'Histoire.

Une fois que toutes les données ont été saisies, il faut valider.

Une nouvelle fenêtre s'ouvre et dans la cellule D3, apparaît le résultat.

La note minimale nécessaire en Histoire doit être de 18, pour cet exemple.

PowerPivot

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Ce chapitre a pour but de présenter les fonctions de PowerPivot.

Une leçon existe désormais sur Powerpivot indépendamment de la leçon sur le Tableur Excel.

Présentation

Le nom de cet outil est bien évocateur: "Power" pour "Puissance" et "Pivot" pour "Tableaux croisés dynamiques".

PowerPivot est un add-in d’EXCEL c'est-à-dire un module d’extension, il se trouve dans les outils de Business Intelligence (BI) simple, aussi nommée « Self-BI » et est fondé sur le principe des cubes de données.

PowerPivot existait dans le système SQL Server-Sharepoint utilisé par de nombreuses entreprises, il a été dans un premier temps proposé en tant que module d'extension gratuit pour la version EXCEL 2010. La version 2013 d'EXCEL comporte maintenant PowerPivot en standard.

PowerPivot est utilisé pour insérer et intégrer de grandes quantités de données dans des classeurs EXCEL. Les données ajoutées au classeur sont stockées en interne, sous la forme d'une base de données PowerPivot incorporée dans le fichier.xlsx qui peut être utilisée dans les rapports EXCEL ou Power View.

Dans EXCEL, le classeur offre toutes les fonctionnalités de visualisation des données et d'interaction avec celles-ci. Vous pouvez générer des classeurs sophistiqués basés sur des données analytiques performantes que vous générez facilement grâce à la création de tableau croisé dynamique.

PowerPivot est un logiciel intuitif et évolutif qui donne la possibilité aux utilisateurs de créer des rapports ou solution de REPORTING. Il permet également d'étendre les possibilités d’analyse de données d’EXCEL mais aussi d’obtenir rapidement des informations agrégées à partir d'un grand nombre de données.

Intérêt de l'outil

Les avantages de ce module d'extension sont nombreux, PowerPivot permet de:

• Multiplier la puissance des TCD dans Excel et permet des calculs impressionnants, qui peuvent traiter des millions de lignes comme s’il s’agissait de quelques milliers
• Travailler directement avec les données dans l'espace de travail EXCEL, l'onglet menu PowerPivot est intégré au ruban EXCEL.
• Ajouter et consolider des données émanant de plusieurs sources tels qu' EXCEL, ACCESS, SQL, HTML, ... Il suffit simplement d'associer les différentes bases de données entre elles avec un champ commun, par exemple l'identifiant d'un client
• Intégrer des données qui évoluent sans cesse aussi facilement qu’un flux RSS, la mise à jour se fait instantanément sans alourdir les fichiers Excel
• Utiliser Sharepoint et permettre ainsi de publier, partager et collaborer facilement
• Traiter les données avec un moteur d'analyse local (en mémoire xVelocity Analysis Services VertiPaq); ce dernier compacte, charge les données et les met à disposition d'objets de visualisation, comme des tableaux croisés dynamiques, dans une feuille de travail.
• Eviter des tâches d'administration ou de configuration, le moteur s'exécute dans le processus EXCEL(en tant que composant interne du complément PowerPivot à EXCEL).

En Conclusion, PowerPivot permet à EXCEL de devenir un outil de décisionnel (Business Intelligence) très puissant et en même temps simple d’utilisation.

Ajout de données

À partir de cet add-in, il est possible d'ajouter des données de différentes manières :

• à partir d’une liste de données (sous forme de plage ou de tableau) lors de la demande d’insertion d'un TCD => cocher "ajouter ces données au modèle de données". Dans ce cas, le TCD utilisera automatiquement le modèle de données. Il sera possible dans le volet de construction de ce TCD, de sélectionner "Actif" ou "Tous" pour afficher les champs du tableau ou les champs de l’ensemble des tableaux en relation.

Powerpivot

• à partir d’un tableau, il faudra pour cela ajouter automatiquement le tableau au modèle de données en établissant une relation avec un autre tableau : nouvelle fonctionnalité accessible depuis l’onglet "Données". Dans ce cas il faut préciser, lors de la création d’un tableau croisé, ce que l’on souhaite faire. Si on ne précise rien, c’est le tableau qui est utilisé pour la création du TCD. De ce fait, on peut ensuite préciser si l’on souhaite utiliser le modèle en sélectionnant "Plus de tables" dans la liste des champs du volet de construction du TCD. Sinon, on peut, comme dans le cas « d’ajout à partir d’une liste de données », cocher "Ajouter ces données au modèles de données". Ceci parait étonnant du fait que les données soient déjà dans le modèle.

Powerpivot

• à partir de données externes, pour la création du TCD, on devra cocher "Utiliser une source de données externes", puis on sélectionnera la connexion qui s'est créée dans le classeur, si on souhaite utiliser des données externes pour une relation, on peut :
  • Établir une connexion à ces données en cochant dans la fenêtre d'importation
  • "Tableau", ou si on ne souhaite pas les importer dans un onglet, "Ne créer que la connexion"
  • "Ajouter ces données au modèle de données"

Powerpivot

Powerpivot

Intérêt des relations PowerPivot

Sur le même principe qu'une base de données, les relations permettent de tirer parti des liens existants entre les données en évitant la répétition. La relation est toujours une relation de 1 à N, contrairement à EXCEL qui n'impose pas de relation naturelle.

Les relations PowerPivot évitent cependant des formules comme RECHERCHEV pour croiser les données de plusieurs tables si l'objectif de la formule est l'analyse par TCD. Par exemple avec :

• un tableau de factures où seul l'identifiant client apparait.
• un tableau de clients, où figurent l'adresse du client et l'identifiant du commercial chargé du client

La relation entre les deux tableaux permet une analyse du chiffre d'affaire par département ou par ville ou par commercial.

PowerPivot version ProPlus

Dans la version pro plus, on bénéficie de tous les avantages de l’add-in tels que :

• un onglet PowerPivot
• une interface spécifique permettant de visualiser et optimiser le modèle de données
• l'autorisation des calculs en langage DAX permettent de tirer toute la puissance possible des relations

Pour ajouter des données au modèle, en plus des trois possibilités citées pour toutes les versions, PowerPivot ProPlus permet :

• L’utilisation de la commande Ajouter au modèle de données de l'onglet PowerPivot
• Obtenir des données externes dans l'interface PowerPivot qui permet de :
  • n’importer que certains champs d'une table ou vue.
  • n’importer que certains enregistrements d'une table ou vue.
• créer une requête pour définir les données à importer.

Dans les 3 cas on obtient une requête, implicite ou explicite pouvant être modifiée à tout moment.

Langage DAX

Le langage DAX permet de réaliser des calculs pouvant être réalisés soit dans l’interface spécifique, soit depuis l’onglet PowerPivot. Cela permet d’obtenir des résultats non disponibles dans les tableaux croisés dynamiques classiques.

Les formules DAX sont utilisées dans des tables PowerPivot ou dans des tableaux croisés dynamiques Excel. DAX représente un ensemble de fonctions, d'opérateurs et de constantes qui peuvent être utilisés dans une formule ou une expression, afin de calculer une ou plusieurs valeurs. DAX vous aide à créer de nouvelles informations à partir de données figurant déjà dans votre modèle.

Les formules DAX sont très semblables aux formules Excel. Pour en créer une formule DAX, tapez sur le signe égal, suivi d'un nom de fonction ou d'une expression, et de toutes les valeurs requises.

Pour mieux comprendre prenons un exemple simple :

DAX langage

• A ==> l'opérateur de signe égal (=) indique le début de la formule
• B ==> la colonne [MontantVentes], les crochets indiquent une référence de colonne dans une formule, contrairement aux formules Excel qui font référence à une cellule, une formule DAX fait toujours référence à une colonne
• C ==> Opérateur mathématique de soustraction (-).
• D ==> la colonne référencée [CoutTotl]

Erreurs

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Photo face erreur excel

Les erreurs EXCEL

symbole dièse

Il existe différents types d'erreurs sur Excel. Nous traiterons ici les 8 erreurs les plus communes d'Excel ainsi que celle de la référence circulaire qui peuvent se résoudre de manières différentes en fonction de l'environnement de travail (PC ou Mac).

Certaines cellules contiennent des formules qui génèrent une erreur.

En général, c’est que la formule n’utilise pas la syntaxe définie. C’est-à-dire que les arguments ou les types de données attendus ne sont pas les bons ou ne correspondent pas. Ces valeurs d’erreur comprennent #REF!, #VALEUR!, #NOM?, #NOMBRE!, ######, #N/A, #NULL!, #DIV/0!, et sont détectables par un triangle vert qui s’affiche dans le coin supérieur gauche de la cellule.

Un autre type d’erreur peut être la référence circulaire : la formule fait, directement ou indirectement, référence à sa propre valeur. Lorsqu’une cellule affiche un code d’erreur, toutes les cellules qui utilisent son résultat affichent en cascade le même code d’erreur. Une seule formule erronée peut ainsi déclencher des centaines de codes d’erreur au sein des cellules qui dépendent de son résultat. Aussi pour trouver l’origine d’une erreur, nous pouvons utiliser les commandes de l’onglet Formules du ruban présentes au sein de la section nommée Audit de formules.

Erreurs Excel

Vérification des erreurs

Ces valeurs peuvent être corrigées une par une dans les formules. Il est possible d’utiliser certaines règles pour détecter les erreurs dans les formules, comme le correcteur orthographique mais qui cherche à trouver les erreurs des données entrées dans les cellules. Ceci permet de localiser les erreurs les plus courantes, et elles peuvent être corrigées de deux façons différentes soit au fur et à mesure de leur apparition soit l’une après l’autre.

Même si lors de la vérification des erreurs dans la feuille de calcul certaines erreurs ont pu être ignorées.

	PC	MAC
Vérification des erreurs	Sélectionnez la feuille de calcul pour laquelle vous souhaitez vérifier les erreurs. Si la feuille de calcul est calculée manuellement, appuyez sur F9 pour la recalculer maintenant. Sous l’onglet Formules, dans le groupe Audit de formules, cliquez sur Vérification des erreurs. Vérification erreurs Si des erreurs sont trouvées alors la boîte de dialogue Vérification des erreurs s'affiche. Si des erreurs ont été ignorées, vous pouvez les revoir. boite dialogue vérification des erreurs	Sélectionnez la feuille de calcul pour laquelle vous souhaitez vérifier les erreurs. Si la feuille de calcul est calculée manuellement, cliquer sur Recalculer la feuille dans le groupe Calcul de l'onglet Formules Sous l'onglet Formules, dans le groupe Formules d'audit, cliquez sur Rechercher les erreurs. Repérer des erreurs Mac Si des erreurs sont trouvées alors la boîte de dialogue Vérification des erreurs s'affiche. Si des erreurs ont été ignorées, vous pouvez les revoir. boite de dialogue erreurs

Information supplémentaire pour l’utilisation d'Excel sur PC

Lorsque la vérification des erreurs est activée dans Excel, grâce à l’info-bulle de la cellule affichant l’erreur, vous pouvez cliquez sur « Afficher les étapes du calcul » permettant ainsi de choisir la résolution adaptée à votre demande.

Afficher les étapes du calcul

Cette aide permet donc d'afficher les étapes du calcul, et de nous aider avec le mode "pas à pas" pour situer à quel moment précis l'erreur est faite

Evaluation de formule

L'erreur #REF!

L'erreur #REF peut avoir plusieurs causes. Généralement, elle apparaît lorsqu'une formule contient une cellule non référencée, non valable.

La formule contient une ou plusieurs références à des cellules qui ont été déplacées, supprimées ou remplacées

Solution 1 MAC/PC	Annulez vos modifications pour rétablir les cellules à leur état d’origine Dans la barre d’outils standard, cliquez sur Annuler (Ctrl+Z) immédiatement après avoir supprimé ou collé les cellules
Solution 2 MAC	Modifiez la formule pour supprimer les références aux cellules supprimées Sélectionnez la cellule Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule Modifiez la référence de plage pour y inclure soit la ligne, soit la colonne contenant la formule. Sélectionnez la cellule Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule
Solution 2bis PC	Modifiez la formule pour supprimer les références aux cellules supprimées Sélectionnez la cellule Dans la barre de saisie de la fonction, mettre à jour la plage de cellule afin d'éliminer les références aux cellules supprimées Modifiez la référence de plage pour y inclure soit la ligne, soit la colonne contenant la formule. Sélectionnez la cellule Dans la barre de saisie de la fonction, modifiez la référence de la place afin d'y inclure soit la ligne, soit la colonne contenant la formule

Une matrice non valide est utilisée dans l’une des fonctions matricielles, telle que INVERSMAT, PRODUITMAT et DETERMAT

Solution MAC	Solution PC
Vérifiez que les dimensions de la matrice sont appropriées aux arguments de la fonction matricielle. Sélectionnez la cellule Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule	Vérifiez que les dimensions de la matrice sont appropriées aux arguments de la fonction matricielle. Sélectionnez la cellule Dans l’onglet Formules, dans Fonctions, cliquez sur insérer une fonction insérer une fonction Modifiez la formule selon les arguments demandés pour la fonction choisie argument fonction somme.si.ens

L'erreur #VALEUR!

Cette erreur s’affiche lorsque la formule inclut des cellules qui contiennent des types de données non identiques.

En général, l’info-bulle nous amène des précisions concernant l’erreur et affiche « Le type de données d’une valeur utilisée dans la formule est incorrect ». Cette erreur peut se résoudre en faisant des modifications simples dans la formule.

Il manque un argument nécessaire dans la formule

Solution MAC/PC

Vérifiez que la formule ou fonction est correcte pour l’opérande ou l’argument nécessaire et que les cellules auxquelles la formule fait référence contiennent des valeurs valides. Excel ne peut convertir le texte dans le type de donnée approprié.  Par exemple, si la cellule A5 contient un nombre et que la cellule A6 contient le texte « Non disponible », la formule =A5+A6 renvoie l’erreur #VALEUR !. Utiliser la fonction SOMME ajouter les deux valeurs, car cette fonction ne prend pas en compte les données textuelles =SOMME(A5 :A6)

L'entrée de la formule matricielle est incorrecte

Solution MAC
Il est nécessaire d’utiliser la procédure suivante pour entrer la formule matricielle Sélectionnez la cellule ou plage de cellules contenant la formule matricielle, puis appuyez sur la combinaison de touches CTRL+U pour modifier la formule puis faites retour (CMD+RETOUR sur MAC)

Une référence de cellule, une formule ou une fonction est entrée en tant que constante matricielle

Solution MAC/PC
Vérifiez que la constante matrice ne contient pas de référence de cellule, de formule ou de fonction. Les constantes matricielles ne peuvent pas contenir de références de cellules, de lignes ou de colonnes de longueur inégale, de formules, de fonctions ou le caractère spécial dollar ($), parenthèses (()) ou pourcent (%).

Une plage de valeurs est fournie à un opérateur ou une fonction qui exige une valeur unique

Solution MAC	Solution PC
Modifiez la formule pour faire référence à une valeur unique. Sélectionnez la formule Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule.	Modifiez la formule pour faire référence à une valeur unique. Sélectionnez la formule Dans la barre de saisie de la fonction, modifiez de telle sorte que la formule fasse référence à une valeur unique

Autre causes principales

Causes	Solutions MAC/PC
La formule contient des éléments de cellule sous format texte et la formule effectue des calculs avec des opérateurs mathématiques (division, addition, multiplication, soustraction)	Respectez les arguments demandés selon la fonction utilisée (s’aider de l’info-bulle ou de l’aide en ligne excel si besoin) : Par ex, pour des opérations mathématiques n’utilisez que des nombres Respectez la casse: position des virgules ou point virgules ou deux-points ; Opter pour l’utilisation des fonctions que des opérateurs mathématiques afin de simplifier les calculs et éviter les erreurs ;

L'erreur #NOM?

Cette erreur apparaît lorsque la formule utilisée ne reconnaît par le texte contenu au sein de celle-ci.

On distingue plusieurs causes qui peuvent être à l’origine de l’erreur #NOM?

La formule utilise une fonction personnalisée qui n’est pas disponible

Il faut vérifier que le classeur contenant la fonction personnalisée est ouvert et que celle-ci fonctionne correctement.

Solution MAC	Après avoir,sélectionné la cellule, allez dans l’onglet Formules puis cliquer sur Concepteur de formule Modifiez ensuite la formule dans la section Arguments de la fenêtre Concepteur de formule.

La formule contient une référence à une cellule ou à une plage nommée qui n’est pas définie

Il faut vérifier que la cellule ou la plage nommée est correctement définie.

Solution MAC/PC	Allez dans le menu Insertion, cliquez sur Nom, puis sur Définir. Vérifiez que la cellule ou la plage est bien dans la liste. Sinon il faut la définir en cliquant sur Ajouter.

La formule contient un nom de fonction mal orthographié    

Il faut modifier la formule pour corriger l’orthographe. 

Solution MAC	Solution PC
Après avoir sélectionné la cellule, allez dans l’onglet Formules, puis cliquez sur Concepteur de formules Modifiez la formule dans la section Arguments	Sélectionnez le nom de la fonction dans la barre de formule « Fx= » puis appuyez sur F3 Sélectionnez le nom de la formule souhaité puis cliquez sur OK.

Les deux points (:) sont manquant à la référence d’une plage

Il faut vérifier que toutes les références de plage de la formule utilisent les deux points (:).

Par exemple, l’argument =SOMME(A1:C10) 

La référence d’une feuille ou d’un classeur contenue dans une formule contient une erreur (caractère non alphabétique, espace, non utilisation de guillemets simples)    

Il faut modifier la formule pour orthographiée correctement la référence du classeur ou de la feuille.    

Solution MAC	Solution PC
Après avoir sélectionné la cellule, allez dans l’onglet Formules puis cliquer sur Concepteur de formule Modifiez ensuite la formule dans la section Arguments de la fenêtre Concepteur de formule en utilisant des guillemets simples (‘) pour faire référence à la feuille ou au classeur.	Double-cliquez sur la cellule puis vérifier que la référence à la feuille ou au classeur soit correctement orthographié. Il faut notamment vérifier que de part et d’autre de la référence de la feuille ou du classeur il y ait des guillemets simples (' ').

Le texte entré dans une formule n’est pas mis entre double guillemets (« »)    

Excel interprète cette entrée comme une référence alors qu’il s’agit en réalité d’un texte. 

Il faut modifier la formule pour placer le texte entre double guillemets « ».

Solution MAC	Solution PC
Après avoir sélectionné la cellule, allez dans l’onglet Formules, puis cliquez sur Concepteur de formule. Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule en mettant des double guillemet entre le texte tapé.	Double-cliquez sur la cellule puis vérifier que le texte tapé soit mis entre double guillemets.

L'erreur #NOMBRE!

Cette erreur survient lorsqu’une formule ou une fonction contient des valeurs numériques non valides.

On distingue plusieurs causes qui peuvent être à l'origine de l'erreur #NOMBRE!

Un argument non numérique est utilisé dans une fonction qui nécessite un argument numérique

Il faut vérifier que les arguments utilisés dans la fonction sont des nombres. 

Solution MAC	Solution PC
Après avoir sélectionné la cellule, allez dans l’onglet Formules, puis cliquez sur Concepteur de formule Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule Supprimez tous les caractères non numériques de l’argument numérique dans la formule (n’oubliez pas les espaces)	Après avoir sélectionné la cellule, cliquez dans la barre de formule "Fx=" puis appuyez sur F3 Supprimez tous les caractères non numériques de l'argument numérique dans la formule (n'oubliez pas les espaces)

Une fonction qui se réitère, telle que TRI ou TAUX, n’arrive pas à trouver de résultat

Il faut modifier le nombre de fois qu’Excel itère les formules.   

Remarque :

Plus le nombre d'itérations est élevé, plus la durée nécessaire à l'exécution des calculs d'une feuille par Excel est importante.    

Solution MAC

Solution PC

Dans le menu Excel, cliquez sur Préférences. Sous Formules et listes, cliquez sur Calcul Bouton Préférences de calcul puis, sous Itération, activez la case à cocher Limiter l'itération. Dans la zone Itérations maximales, tapez le nombre d'itérations que vous voulez définir comme nombre maximal de fois où Excel effectue de nouveau les calculs. Dans la zone Modification maximale, tapez le montant que vous voulez définir comme montant maximal de modification entre les résultats de calcul. Plus le nombre est petit, plus le résultat est précis et plus la durée nécessaire à l'exécution des calculs d'une feuille par Excel est importante.

Dans le menu Outils, cliquez sur Options, puis sur l’onglet Calcul. Activez la case à cocher Itération. Pour définir le nombre maximal de fois où Excel recalcule, tapez le nombre d’itérations dans la zone Nb maximal d’itérations. Pour définir l’écart maximal entre les résultats du calcul, tapez le chiffre dans la zone Ecart maximal. Plus le nombre est petit, plus le résultat est précis et plus la durée nécessaire à l'exécution des calculs d'une feuille par Excel est importante.

La formule produit un nombre qui est trop grand ou trop petit pour être représenté dans Excel    

Il faut modifier la formule afin que son résultat puisse être représenté dans Excel.

Solution MAC/PC	Excel peut représenter des valeurs comprises entre -1 × 10307 et 1 × 10307

L'erreur ######

###### est une erreur d'affichage d'une valeur numérique, celle-ci apparaît lorsqu'une colonne est trop étroite pour afficher la totalité d'une donnée numérique.

Afin de corriger cette erreur, il suffit tout simplement d'élargir la colonne dans laquelle apparaît l'erreur.

Vidéo

Pour vous aider dans cette démarche voici une vidéo de tutorat qui vous permettra de mieux comprendre la modification à effectuer.

https://www.youtube.com/watch?v=mBu4V5jmOPY&feature=share

• Remarque :

Que ce soit sur MAC ou bien PC la solution reste la même.

L'erreur #N/A ou NA()

L'erreur #N/A ou NA() survient lorsqu'une donnée nécessaire au bon fonctionnement de la formule est manquante.

Les causes :

Les causes de cette erreur sont nombreuses et diverses, celle-ci apparaît donc lorsque :

• Les cellules référencées dans la formule contiennent déjà des données erronées #N/A ou NA().
• Une erreur lors de la sélection de la plage de cellules, celles-ci n'étant pas figée, ou bien lorsque la valeur_cherchée dans une ligne ou colonne est mal triée pour les fonctions RECHERCHEV, RECHERCHEH ou INDEX.

Remarque

Si dans le dernier argument de la fonction la valeur VRAI est choisi alors l'erreur concernant les lignes et colonnes non triées prendra effet, cependant si la valeur FAUX est choisi alors la recherche dans une ligne ou colonne non triée ne fera pas apparaître d'erreur.

-> Pour figer vos cellules il suffit de cliquer sur F4 ou bien d'insérer des dollars ($) dans la plage de données sélectionnées.

• La dimension des plages de cellules sélectionnées dans la formule ne sont pas les mêmes..

Exemple : Dans une formule =SOMMEPROD((A1:A6)*(A1:A5)) ici la matrice renvoie une erreur #N/A car les plages de cellules sélectionnées ne sont pas de même dimension. Afin de la corriger, il faut sélectionner la même plage de cellules donc soit de A1:A5 ou de A1:A6 dans notre exemple.

Solution MAC	Solution PC
Dans l'onglet Formules, dans Fonctions, cliquez sur Concepteur de formule Modifier la formule dans la section Arguments de la fenêtre Concepteur de formule	Dans l'onglet Formules, dans Vérification des formules, cliquez sur Vérification des erreurs Cliquez sur Aide sur cette erreur et suivez les indications

Vidéo

Pour vous aider dans cette démarche voici une vidéo de tutorat qui vous permettra de mieux comprendre la modification à effectuer.

https://www.youtube.com/watch?v=YGXMoEt363A&feature=share

L'erreur #NULL!

Causes:

• Intersection de plages de cellules n'étant pas adjacentes
• Séparateur non défini entre les rangs

Solutions:

• Il fait bien vérifier que les deux points (:) séparent la première cellule de la plage sélectionnée, de la dernière.

Par exemple: C1:C12 comprend la plage de cellules allant de la C1 à la C12

• Si deux zone de se coupent pas, on peut utiliser l'opérateur d'union (;)

Par exemple: Si on fait la somme entre deux plages de cellules: =SOMME(D3:D6;C3:C6)

Exemple:

Si on entre la formule suivante: SOMME(D3:D6 C3:C6), #NUL! est renvoyé car il n'y a pas de séparateur entre les rangs 1 et 2. Afin de corriger cette erreur il faut placer un point-virgule (;) entre les deux plages sélectionnés.

Exemple d'erreur #NULL! :

exemple d'erreur #null!

Exemple de solution pour #NULL!:

erreur #null! solution

L'erreur #DIV/O!

Causes:

L'erreur #DIV/0! survient lorsqu'un nombre est divisé par zéro (0), comme pour la division simple = 23÷0.

Dans le cas d'une formule, elle survient lorsqu'on fait référence a une cellule contenant 0 ou une cellule vide.

Solutions:

Afin de rectifier cette erreur il faut vérifier que:

• le diviseur de la formule n'est ni zéro (0), ni une cellule vide
• modifier la référence de la cellule dans la formule de façon a ce qu'elle ne soit ni vide, ni contenant la valeur zéro (0)
• entrer #N/A dans la cellule non référencée comme diviseur, ce qui remplacera le résultat de la formule par #N/A, signifiant que le diviseur est indisponible

Si l'erreur persiste, on peut utiliser la formule SI afin d'interrompre l’affichage de cette erreur. La formule SI va afficher 0 ou cellule vide au lieu de l'erreur #DIV/0.

Par exemple: Si nous avons l'opération =L1/L2

• SI(L2=0;0;L1/L2) pour faire afficher zéro (0)

erreur #Div/0!

• SI(L2=0;" ";L1/L2) pour faire afficher une valeur vide

Erreur #DIV/0! bis

Les erreurs de référence circulaire

Lorsque l’un des classeurs excel est ouvert et s’il contient une référence circulaire, un message d’avertissement de référence circulaire s’affiche.

Exemple d’une « référence circulaire » : formule dans C3 = C3+1.

L’implication de la cellule elle-même dans la formule de la cellule implique une répétition infinie à Excel et donc Excel arrête le processus et nous affiche un avertissement pour éviter une « boucle sans fin ».

Comment rechercher et supprimer des références circulaires ?

En général, vous pouvez rechercher et supprimez la référence circulaire. Mais sinon, excel peut également calculer une fois les cellules de la référence circulaire, ou il arrête automatiquement le calcul après 100 itérations ou bien lorsque l’écart qui existe entre les valeurs de la référence circulaire est supérieur à 0.001 entre les itérations.

Pour rechercher et supprimer une référence circulaire, vous pouvez vous aider du bouton Vérification des erreurs qui se trouve dans la section Audit de formules de l’onglet formules.  Puis en cliquant sur références circulaires, vous pouvez cliquez sur la première cellule répertoriée dans le sous-menu. Ensuite, Il y a deux cas possibles :

Cas 1	Soit vous pouvez déterminer la formule de la cellule et savoir si elle est la cause d’une référence circulaire
Cas 2	Soit vous ne pouvez pas savoir et il est nécessaire de cliquer sur la cellule suivante dans le sous-menu « références circulaires »

Remarque

Si la barre d’état affiche le mot « circulaire » suivi d’une référence à l’une des cellules contenues dans la référence circulaire. Sinon s’il n’y a pas de référence à une cellule, la feuille excel ne contient pas de référence circulaire.

Pour supprimer une référence circulaire, il faut continuer à regarder toutes les cellules afin de corriger la référence circulaire jusqu'à ce que la barre d'état n'affiche plus le mot «Circulaire»

Comment autoriser et utiliser une référence circulaire ?

Par défaut, les références circulaires sont indisponibles dans Excel et c’est pour cela que nous avons précédemment expliqué comment les trouver et les supprimer.

Pourtant, il est possible d’autoriser et d’utiliser ces références circulaires tout en les paramétrant.

Dans l’onglet Fichier, puis options, options Excel cliquez sur le menu Formules. Ici, nous voulons autoriser l’itération des références circulaires donc il faut cocher la case « Activer le calcul itératif ». Cette case nous permet de paramétrer le nombre maximal d’itérations et l’écart maximal entre les itérations. Par défaut, Excel met 100 itérations maximales pour un écart maximal de 0,001. Selon vos besoins, vous pouvez appliquez l’itération 1 fois, 2 fois … jusqu’à 100.

Photo dos erreur excel

Analyse de scénario

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Qui n’a jamais rêvé de résoudre des équations mathématiques de manière automatique ? Solveur vous permet de réaliser ce rêve.

Présentation de la fonction Solveur

Le solveur d'Excel est un outil de calcul mathématique qui permet de résoudre des équations du second degré. On lui fixe un objectif de résultat, en lui stipulant les contraintes et variables de l'énoncé. Il est alors chargé de calculer de façon automatique les possibilités existantes et d'en faire émerger la solution exacte (voire la plus proche) du problème posé.

Mise en place du Solveur d'Excel

Pour mettre en place la fonction solveur dans Excel il faut aller dans "option d'Excel" puis dans "compléments". Ensuite il faut sélectionner "complément solver" et cliquer sur "atteindre".

Les éléments nécessaires à l'exécution de la fonction

Afin de fonctionner correctement, la fonction solveur doit prendre en compte différents facteurs indispensables à sa réalisation. Parmi ces facteurs, on retrouve notamment :

• les variables de décision (les inconnus de l’inéquation)
• les contraintes (ex : capacité de production maximale)
• la fonction économique (minimiser ou maximiser)
• l’objectif (le résultat à atteindre avec la prise en compte de toutes les variables)

Les variables

Il est important de déterminer le nombre de variables à identifier. Excel peut calculer jusqu'à 200 variables. On notera alors dans une feuille d'excel toutes les variables à définir et on notera "0" dans les cellules à droite.

Les contraintes

Il faut déterminer les contraintes de notre problème. Elles peuvent aussi aller jusqu'à 200. Notre contrainte se notera sous forme de tableau comme pour les variables. Les contraintes devront être identifiées comme supérieures ou égales (≥) à un certain nombre, inférieures ou égales (≤), entre deux valeurs (ent), égales (=) à une valeur ou binaires (bin).

Les contraintes peuvent être soit des contraintes de limite c'est-à-dire liées aux éléments fixant un problème, soit des contraintes de disponibilité c'est-à-dire liées aux variables. En prenant notre exemple ci-dessous, les contraintes de limites sont les trois premières contraintes et la contrainte de disponibilité est la quatrième contrainte. Il y aura autant de contraintes de disponibilité qu'il y aura de variables.

La fonction économique

La fonction économique permet de déterminer une valeur optimum de notre problème. C'est la maximisation, la minimisation ou l'égalisation de l'objectif.

L'objectif

L'objectif est la valeur cible à définir dans les paramètres de la fonction solveur d'Excel. Cette cible doit soit minimiser une dépense, maximiser un profit ou être égale à une autre valeur. La cellule doit obligatoirement contenir une formule incluant les variables du problème.

Exemple de résolution d'un problème linéaire avec le solveur

L'entreprise Durty fabrique des chaussures de danse. Elle souhaite modifier son programme de production actuel pour les produits A (600 unités) et les produits B (200 unités). Lors de leur fabrication les produits passent par différents ateliers. Le tableau ci-dessous donne les informations concernant la durée de leur fabrication dans les ateliers.

	Produit A	Produit B	Heures disponibles
Assemblage (phase 1)	3	4	4200
Assemblage (phase 2)	1	3	2250
Vérification/Empaquetage	2	2	2600

Au vu de la concurrence, l'entreprise ne veut pas fabriquer plus de 1100 unités du produit A.

La marge du produit A est de 66 € l'unité alors que celle du produit B est de 84 €.

On veut déterminer le programme optimal de fabrication à mettre en œuvre c'est-à-dire celui qui maximiserait les bénéfices.

Variables de décision :

x : le nombre d'unités à fabriquer du produit A

y : le nombre d'unités à fabriquer du produit B

Les contraintes sont :

C1 => 3x+4y ≤ 4200 heures (heures disponibles à l'assemblage: phase 1)

C2 => x+3y ≤ 2250 heures (heures disponibles à l'assemblage : phase 2)

C3 => 2x+2y ≤ 2600 heures (heures disponibles: vérification/empaquetage)

C4 => x ≤ 1100 unités (quantité maximale pour A)

La fonction économique à maximiser est :

Z = 66x + 84y où Z correspond au bénéfice total

Résolution avec EXCEL

Il y a trois principales parties à fournir au solveur :

• La cellule à maximiser/minimiser
• La plage de variables de décision (x et y)
• Les contraintes

1) Les cellules B2 et C2 sont les variables du problème (x et y)

2) Les coefficients reliés aux variables sont inscrits de B5:C8

3) La quantité de ressources est indiquée et le sens de la contrainte (facultatif, seulement pour mémo)

4) Il faut indiquer le bénéfice/unité pour chaque variable (B11:C11)

5) Première contrainte : 3x +4y ≤ 4200. Il faut calculer l'expression de la partie gauche de l'équation avant d'activer le solveur. Exemple dans la cellule D5 la formule =$B$2*B5+$C$2*C5 est inscrite, équivalente à 3x +4y

6) Copiez cette formule pour les autres contraintes

7) La formule =B11*B2+C11*C2 est inscrite dans la cellule F12. C'est cette cellule qu'on maximisera car elle correspond à la fonction objectif 66x+84y

8) Menu : Outils/Solveur

9) Entrez les paramètres du solveur

• Cellule cible à définir : Ceci correspond à l'adresse de la fonction à optimiser
• Égale à : Cochez le type d'optimisation voulu. Le Max est coché car dans cet exemple nous voulons maximiser le bénéfice total.
• Cellules variables: Sélectionnez l'endroit dans le tableur où les variables se trouvent. Il ne doit pas avoir de cellules vides entre les variables. Les cellules B2:C2 représentent les variables de notre problème, c'est-à-dire celles qu'on désire déterminer.
• Contraintes : Vous devez spécifier chacune des contraintes de votre problème

Il ne faut pas oublier d'entrer les contraintes de non-négativité x≥0 , y≥0

1) Cliquez sur "Ajouter"

2) Cellule: Sélectionnez toutes vos variables : B2:C2

3) Inscrivez le sens >=

4) Contrainte : 0

x, y doivent être des entiers afin de ne pas produire des fractions d'unités.

1) Cliquez sur "Ajouter"

2) Cellule: Sélectionnez toutes vos variables

3) Choisissez "ent"

Pour enregistrer les autres contraintes

Cliquez sur "Ajouter"

Exemple : Pour la première contrainte : 3x +4y ≤ 4200 heures

1) Entrez l'adresse de la cellule contenant la formule : 3x +4y équivalente à (=B5*B2+C5*C2). On doit donc entrer D5

2) Le sens de l'équation <=

3) Le nombre de ressource 4200 ou son adresse F5

La première contrainte correspond à D5≤F5

Exemple : pour la dernière contrainte x ≤ 1100

Le solveur sépare l'équation en trois.

1) Le membre gauche de l'équation: c'est-à-dire l'adresse de la cellule contenant la formule B2*B8+C2*C8 donc D8

2) Le sens de l'équation : <=

3) Le membre gauche de l'équation : c'est-à-dire le nombre de ressources 1100 ou son adresse F8

Cliquez sur OK lorsque vous avez terminé d'entrer toutes vos contraintes.

Etant donné que nous voulons résoudre un programme linéaire, il est possible de le spécifier au solveur afin qu'il utilise la méthode adéquate pour résoudre le problème.

Cliquez sur "option", cochez "Modèle supposé linéaire" et cliquez sur OK.

10) Cliquez sur "Résoudre"

11) Le solveur a trouvé la solution optimale selon les contraintes.

Production de 1000 A et de 300 B et un bénéfice total de 91 200 €.

12) Le solveur vous demande si vous voulez garder cette solution à l'écran ou revenir à celle de départ. Choisissez garder la solution du solveur.

13) Appuyer sur OK

Conclusion

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Les changements liés aux versions

Les anciennetés EXCEL 2007

Avec sa nouvelle interface utilisateur orientée résultats, Microsoft Office Excel 2007 fournit des fonctions et des outils puissants pour vous permettre d’analyser, de partager et de gérer les données avec facilité.

• Ruban : une retouche graphique essentielle
• Plus de : lignes, colonnes, thèmes, styles, modèles, performances
• Thèmes et styles : les vôtres en plus
• Mise en forme conditionnelle : plusieurs règles permettent d'afficher couleurs, formes, … en fonction du contenu de cellules
• Rédaction de formule : assistants plus précis et plus faciles, références mieux structurées et accessibles
• Formules OLAP : vous pouvez exploiter des formules OLAP pour constituer des rapports de données OLAP complexes et de format libre
• …

Les actualités EXCEL 2010

Les nouvelles fonctionnalités et les fonctionnalités améliorées peuvent vous aider à être plus productif, à condition de les trouver quand vous en avez besoin. Comme les autres programmes Microsoft Office 2010, Excel 2010 inclut l’interface Microsoft Office Fluent qui est un système visuel personnalisable d’outils et de commandes.

• Ruban retouché : encore une retouche graphique, création et intégration de rubans personnels
• Plus de : thèmes, fonctions, filtres, performances
• Mode Backstage : encore de nouveaux outils Information permettant de modifier les options rapidement
• Mode WEBAPP: Afficher ou modifier un classeur EXCEL dans le navigateur
• Mode MOBILE: Si vous disposez de Windows Phone 7, vous pouvez utiliser Microsoft Office Mobile 2010 pour travailler avec vos fichiers depuis n’importe où, que vous soyez au bureau, à domicile ou en déplacement.
• Graphique SPARKLINE : petits graphiques insérés dans une cellule
• …

Les nouveautés EXCEL 2013

La première chose que vous voyez en ouvrant Excel est sa toute nouvelle interface. Non seulement elle est plus claire, mais elle est également conçue pour vous donner rapidement des résultats professionnels.

• Interface améliorée : encore une retouche graphique complète
• Nouveaux modèles : encore de nouveaux gabarits prêts à être utilisés
• Assistant graphique : un type de graphique vous est conseillé en fonction des données de votre tableau
• Remplissage automatique de cellules : une fonction rapide permettant de remplir les cellules avec des valeurs aléatoires
• Amélioration : filtrage de données par segment
• Utilisation: 1 classeur est ouvert sur 1 fenêtre, ce qui permet des comparaisons aisées de 2 classeurs sur 2 écrans différents
• …

Pour en savoir plus

• http://office.microsoft.com/fr-ch/excel-help/
• http://www.admexcel.com/trucform.htm
• http://www.docmemo.com/office/excel_erreurs.php
• http://silkyroad.developpez.com/VBA/GestionErreurs/
• http://www.linternaute.com/pratique/informatique/logiciels/excel/525/formules-excel-corriger-les-erreurs.html
• http://www.linternaute.com/hightech/astuces-excel/formules/index.shtml

GFDL

Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans texte de dernière page de couverture.