Résumé

Ce manuel d’autoformation est destiné aux utilisateurs ayant une bonne pratique des fonctions de base d’Excel et souhaitant découvrir et maîtriser les fonctionnalités avancées de la version 2013 du logiciel.

   La première partie présente sous forme de fiches pratiques les fonctions avancées d’Excel 2013 : calculs et simulations, outils graphiques, analyse des données, import-export de données, etc.

   La seconde partie propose vingt-deux cas pratiques consistant à réaliser des documents d’entreprise complets intégrant les fonctions avancées étudiées dans la première partie.

L’ouvrage décrit comment effectuer les tâches Excel 2013 aussi bien avec la souris qu’à l’aide de l’écran tactile d’un ordinateur ou d’une tablette. Une annexe fournit les équivalences entre les commandes de l’ancienne version Excel 2003 et celles du Ruban d’Excel 2013. Une autre annexe recense les nouveautés, améliorations et modifications d’Excel 2013 par rapport à la version Excel 2010.

Les fichiers nécessaires à la réalisation des cas pratiques sont disponibles en téléchargement sur le site Web www.editions-eyrolles.com

Au sommaire

Guide des fonctions avancées d’Excel 2013. Calculs et simulations (itérations, valeur cible, scénarios, tableaux d’hypothèses, solveur…) • Mieux exploiter son classeur (rechercheremplace, séries, listes personnalisées, mise en forme conditionnelle, macros…) • Images, dessins et objets graphiques • Représentation graphique des données • Gestion et analyse de données : tris, filtres, sous-totaux, mode Plan, tableaux et graphiques croisés dynamiques, sources de données externes, Microsoft Query • Protection des fichiers, des cellules, travail collaboratif, importation et exportation de données… • Nouveautés d’Excel 2013, commandes tactiles. Vingt-deux cas pratiques. Techniques élaborées de mise en forme • Relance de factures impayées • Statistiques comparatives de prix • Fonctions financières • Formules conditionnelles • Formules matricielles • Recherche dans une table • Le mode Plan • Table d’hypothèses • Valeur cible • Scénarios • Liaisons entre feuilles • Consolidation • Gérer un tableau de données • Filtres • Sous-totaux • Tableaux croisés dynamiques • Images et pages Web • Accès à des données externes • Excel comme base de données • Maîtriser les graphiques • Contrôle et protection des données.

Biographie auteur

Les Guides de formation Tsoft

Rédigés par des professionnels de la formation, les Guides de formation Tsoft ont été adoptés par de nombreuses entreprises comme supports de cours ou manuels d’autoformation aux logiciels de bureautique.

Chaque ouvrage de la collection comprend une partie cours, conçue sous forme de fiches descriptives très pratiques, suivie de cas pratiques grâce auxquels vous saurez rapidement créer vos propres documents de manière autonome.

Après une carrière d’ingénieur chez IBM, Philippe Moreau a été responsable formation. Après avoir fondé et développé un important organisme de formation informatique en France, il dirige aujourd’hui la société d’édition Tsoft. Il est auteur depuis 15 ans de nombreux ouvrages sur Windows et sur les suites bureautiques.

www.editions-eyrolles.com

Excel 2013
avancé

Guide de formation avec cas pratiques

Philippe Moreau

TSOFT

10, rue du Colisée

75008 Paris

www.tsoft.fr

ÉDITIONS EYROLLES

61, bd Saint-Germain

75240 Paris Cedex 05

www.editions-eyrolles.com

Attention : la version originale de cet ebook est en couleur, lire ce livre numérique sur un support de lecture noir et blanc peut en réduire la pertinence et la compréhension.

En application de la loi du 11 mars 1957, il est interdit de reproduire intégralement ou partiellement le présent ouvrage, sur quelque support que ce soit, sans l’autorisation de l’Éditeur ou du Centre Français d’exploitation du droit de copie, 20, rue des Grands Augustins, 75006 Paris.

© TSoft et Groupe Eyrolles, 2013, ISBN : 978-2-212-13812-2

Dans la collection Les guides de formation Tsoft

P. MOREAU. – Excel 2013 initiation.

N°12811, 2013, 244 pages.

P. MOREAU. – Word 2010 initiation.

N°12879, 2010, 206 pages.

P. MOREAU. – Word 2010 avancé.

N°12880, 2010, 198 pages.

Y. PICOT, P. MOREAU. – Access 2010 Utilisateur.

N°12825, 2010, 352 pages.

S. LANGE. – Configuration et dépannage de PC.

N°13421, 4e édition, 2012, 624 pages.

Autres ouvrages

N. BARBARY. – Excel 2013 expert.

N°13692, à paraître en novembre 2013.

L.-G. MORAND. – Windows 8 avancé.

N°13483, 2013, 420 pages.

B. LEBELLE. – Convaincre avec des graphiques efficaces.

Sous Excel, PowerPoint, Tableau…

N°55399, 2012, 258 pages.

C. HERBADJI. – La gestion sous Excel et VBA.

N°55166, 2012, 344 pages.

A. FERNANDEZ. – L’essentiel du tableau de bord.

Méthode et mise en pratique avec Microsoft Excel.

N°54996, 2011, 252 pages.

C. PRINS et M. SEVAUX. – Programmation linéaire sous Excel.

N°12659, 2011, 388 pages.

T. C...

Avant-propos

Conçu par des pédagogues expérimentés, l’originalité de cet ouvrage est d’être à la fois un manuel de formation et un manuel de référence. Il traite les fonctions avancées du tableur Office Excel 2013, et il fait suite à un manuel d’initiation paru chez le même éditeur.

Ce manuel s’adresse donc à des utilisateurs ayant déjà assimilé et mis en pratique les fonctions de base d’Office Excel 2013.

FICHES PRATIQUES

La première partie, Manuel utilisateur, présente sous forme de fiches pratiques l’utilisation des fonctions avancées d’Excel 2013 et leur mode d’emploi. Ces fiches peuvent être utilisées soit dans une démarche d’apprentissage pas à pas, soit au fur et à mesure de vos besoins, lors de la réalisation de vos propres documents. Une fois ces fonctions maîtrisées, vous pourrez également continuer à vous y référer en tant qu’aide-mémoire. Si vous vous êtes déjà aguerri sur une version précédente d’Excel ou sur un autre logiciel tableur, ces fiches vous aideront à vous approprier rapidement les fonctions avancées d’Office Excel 2013.

CAS PRATIQUES

La seconde partie, Cas pratiques, consiste à réaliser de petites applications en se servant des commandes et des fonctions d’Office Excel 2013. Cette partie vous propose vingt-deux cas pratiques qui vous permettront de mettre en œuvre la plupart des fonctions étudiées dans la partie précédente, tout en vous préparant à concevoir vos propres applications de manière autonome. Ils ont été conçus pour vous faire progresser vers une bonne maîtrise des fonctionnalités avancées d’Office Excel 2013.

Ces cas pratiques constituent un parcours de formation ; la réalisation du parcours complet permet de s’initier seul en autoformation.

Un formateur pourra aussi utiliser cette partie pour animer une formation à l’utilisation avancée d’Office Excel 2013. Mis à disposition des apprenants, ce parcours permet à chaque élève de progresser à sa vitesse et de poser ses questions au formateur sans ralentir la cadence des autres élèves.

Les fichiers nécessaires à la réalisation de ces cas pratiques peuvent être téléchargés depuis le site Web www.editions-eyrolles.com. Pour cela, tapez le code 13812 dans le champ <RECHERCHE> de la page d’accueil du site puis appuyez sur .

Vous accéderez ainsi à la fiche de l’ouvrage sur laquelle se trouve un lien vers le fichier à télécharger. Une fois ce fichier téléchargé sur votre poste de travail, il vous suffit de le décompresser vers le dossier C:\Exercices Excel 2010 ou un autre dossier de votre choix.

Conventions typographiques

Pour faciliter la compréhension visuelle par le lecteur de l’utilisation pratique du logiciel, nous avons adopté les conventions typographiques suivantes :

Ruban : | noms des onglets, groupes et boutons ou zones qui sont sur le Ruban.
Italique : | noms des commandes dans les menus et nom des dialogues (*).
Saisie : | noms de dossiers, noms de fichiers, texte à saisir.
[xxxxx] : | boutons qui sont dans les boîtes de dialogue (*).
Actions : | les actions à réaliser sont précédées d’une puce.

(*) Dans cet ouvrage,

le terme « dialogue » désigne une « boîte de dialogue » ;

le terme « actionner un élément » signifie « cliquer ou...

PARTIE 1

MANUEL UTILISATEUR

CALCULS ET SIMULATIONS

1

CALCULER SUR DES DATES

PARAMÉTRER LE CHANGEMENT DE SIÈCLE

Lorsque vous saisissez une date avec la partie année sur deux chiffres, Excel interprète 00 à 29 comme 2000 à 2029 et 30 à 99 comme 1930 à 1999. Ce seuil de 29 est défini dans les options Région du panneau de configuration de Windows.

Pour modifier ce seuil, accédez au panneau de configuration Windows, sous Horloge, langue et région, actionnez Modifier les formats de date, d’heure ou de nombre, (ou catégorie Région) actionnez le bouton [Paramètres supplémentaires…], puis sous l’onglet Date : modifiez la zone contenant le seuil.

SAISIR DES DATES OU DES HEURES

Pour saisir une date, séparez les jour, mois et année par / ou par -, par exemple 24/12/69, 24-12-69. Si le mois est saisi en lettres, vous pouvez le séparer par des espaces 24 déc 69.

Pour saisir une heure, utilisez le séparateur deux-points (:), par exemple 22:5:10 correspond à 22H 05 minutes et 10 secondes.

UNE DATE EST UN NUMÉRO DE SÉRIE, UN HEURE UNE FRACTION DE JOUR

Excel enregistre la date comme un numéro de série. Le 1er janvier 1900 correspond au numéro 1, chaque date saisie correspond au nombre de jours écoulés depuis le 1er janvier 1900.

Excel enregistre une heure comme une fraction décimale de jour, par exemple : 0,5 correspond à 12 H ; 0,75 correspond à 18H ; à 0,760416666666667 correspond à 18 H 15.

Il existe un autre système de numérotation des dates à partir du 1er janvier 1904 (utilisé par Excel pour Mac). Il est possible d’utiliser ce système de date : dans les options d’Excel, rubrique Options avancées, sous Lors du calcul de ce classeur, cochez la case < Utiliser le calendrier depuis 1904>.

EXEMPLE DE CALCULS SUR DES DATES

=A1-A2 renvoie le nombre de jours écoulés entre les deux dates contenues dans A1 et...

POUR AMÉLIORER VOTRE EFFICACITÉ

2

COMMENTAIRES

Un commentaire est un texte de remarque associé à une cellule, s’affichant dans une bulle lorsque vous amenez le pointeur sur la cellule. Pour pouvoir être repérées, les cellules possédant un commentaire ont un petit rectangle rouge dans leur coin supérieur droit.

AJOUTER UN COMMENTAIRE

Sélectionnez la cellule, puis Onglet Révision>groupe Commentaires, actionnez le bouton Nouveau commentaire (Maj+F2) ; ou clic droit dans la cellule, puis Insérer un commentaire ; ou appui long sur la cellule, puis sur dans le menu, enfin Nouveau commentaire

Dans la bulle, saisissez le texte du commentaire, la touche sert à passer à la ligne dans le commentaire. Vous pouvez formater les caractères en utilisant les boutons Gras, Italique… sous l’onglet Accueil>groupe Police, terminez en actionnant une cellule quelconque.

AFFICHER LES COMMENTAIRES

Afficher temporairement le commentaire (souris seulement) : positionnez le pointeur sur la cellule dotée d’un triangle rouge dans son coin supérieur droit.

Afficher en permanence le commentaire d’une cellule : sélectionnez la cellule, puis Onglet Révision>groupe Commentaires, actionnez le bouton Afficher/masquer le commentaire  ; ou clic droit sur la cellule, puis Afficher/Masquer les commentaires ; ou appui long puis sur , enfin Afficher/Masquer les commentaires. La même action fait cesser cet affichage permanent.

Pour afficher en permanence les commentaires de toutes les cellules : Onglet Révision>groupe Commentaires, actionnez le bouton Afficher tous les commentaires .

MODIFIER LE TEXTE D’UN COMMENTAIRE

Onglet Révision>groupe Commentaires, actionnez le bouton Modifier le commentaire  ; ou clic droit, puis Modifier le commentaire ; ou, appui long puis sur , puis Modifier le commentaire.

Dans la bulle, effectuez les modifications dans le cadre, terminez en actionnant hors de la bulle.

SUPPRIMER UN COMMENTAIRE

Onglet Révision>groupe Commentaires, actionnez le bouton Supprimer  ; ou clic droit puis Effacer le commentaire ; ou appui long puis sur , puis Effacer le commentaire.

METTRE EN FORME UN COMMENTAIRE

Procédez comme pour modifier le commentaire, puis clic droit ou appui long sur le pourtour de la bulle du commentaire, puis sur l’option Format de commentaire, définissez le formatage, actionnez [OK].

PASSER D’UN COMMENTAIRE À L’AUTRE

Actionnez le bouton Suivant ou Précédent pour passer d’un commentaire à un autre dans tout le classeur.

IMPRIMER LES COMMENTAIRES AVEC LA FEUILLE

Affichez les commentaires en permanence, puis sous l’onglet Mise en page>groupe Mise en page, actionnez le Lanceur du groupe. Sous l’onglet Feuille, dans la zone <Commentaires> : sélectionnez Tel que sur la feuille ou À la fin...

DESSINS, IMAGES ET OBJETS GRAPHIQUES

3

INSÉRER ET METTRE EN FORME UNE IMAGE

On peut illustrer un classeur en y insérant des images, des photos ou des cliparts que l’on positionne ensuite dans la feuille (habillage). Ces images peuvent provenir de diverses sources : elles peuvent être extraites d’un document existant ou d’une page Web, provenir d’un fichier image enregistré, ou encore de la bibliothèque multimédia.

INSÉRER UNE IMAGE OU UN CLIPART

Insérer une image contenue dans un autre document ou une page Web

Le document contenant l’image à récupérer peut être au format Excel ou Word, mais aussi dans un autre format, par exemple une présentation PowerPoint ou une page Web.

Ouvrez dans son application d’origine le document contenant l’image, copiez l’image dans le presse-papiers Windows.

Basculez vers le classeur Excel, sélectionnez la cellule à l’emplacement où l’image doit être insérée puis, sous l’onglet Accueil>groupe Presse-papiers, actionnez Coller ou Ctrl+V.

Insérer une image à partir d’un fichier

Onglet Insertion>groupe Illustration, actionnez le bouton Image, choisissez le dossier contenant le fichier image , sélectionnez le fichier image et actionnez [Insérer] ou double-clic ou double-appui sur le fichier image (vous pouvez choisir les types de fichiers image ).

Depuis la version 2007, Office ne possède plus la commande d’Office 2003 qui permettait d’insérer une image directement à partir d’un scanneur, Insertion>Image>À partir d’un scanneur.

Cependant une commande de la Bibliothèque multimédia Microsoft permet de numériser, Fichier>Ajouts de clips dans la Bibliothèque multimédia>À partir d’un scanneur…

Insérer un clipart ou une image en ligne

La Bibliothèque multimédia Microsoft n’est plus intégrée à Office 2013, elle est remplacée par l’insertion d’images en ligne provenant de la collection d’images clipart Office.com et d’autres sources en ligne, images et vidéos sur Bing, Flickr ou encore votre page SkyDrive ou Facebook.

Sous l’onglet Insertion>groupe Illustrations, actionnez le bouton Images en ligne, actionnez ensuite Image clipart Office.com et saisissez un mot-clé dans cette zone, actionnez la case Rechercher.

Les images clipart sont associées à des mots-clés pour les retrouver facilement.

Faites défiler les cliparts, sélectionnez celui ou ceux que vous voulez insérer par un clic ou un appui (désélectionnez-les par le même geste), puis actionnez le bouton [Insérer].

Si vous êtes connecté à un compte Microsoft Live vous pouvez accéder à des photos et vidéos provenant de Facebook, Flickr...

REPRÉSENTATION GRAPHIQUE DES DONNÉES

4

CRÉER UN GRAPHIQUE

Sélectionnez la plage de cellules à représenter, vous pouvez inclure les étiquettes des lignes et des colonnes, dans l’exemple la plage A6:E9. Puis, sous l’onglet Insertion>groupe Graphiques, actionnez le bouton correspondant au type de graphique à créer Histogramme/Barres/Boursier…

Le bouton Graphiques recommandés ouvre un dialogue proposant les graphiques recommandés par Excel pour vos données avec leur aperçu, choisissez celui qui vous convient et validez par [OK], ou actionnez l’onglet Tous les graphiques pour choisir parmi les graphiques disponibles.

Dans la galerie, actionnez la vignette du graphique qui vous convient. Le graphique est créé sur la feuille en cours.

Il est possible de visualiser un aperçu du graphique avant d’actionner la vignette : (souris) pointez la vignette dans la galerie sans cliquer, (tactile) posez le doigt sur une vignette de la galerie puis glissez-le sur un autre vignette.

Un graphique est composé d’un ensemble d’éléments, Excel affiche le nom d’un élément lorsque vous amenez le pointeur sur l’élément.

Zone du graphique : ensemble du graphique et de ses éléments constitutifs.

Zone de traçage : zone délimitée par les axes et qui comprend toutes les séries de données.

Titre : texte descriptif automatiquement centré en haut du graphique ou aligné sur un axe.

Axes : lignes bordant la zone de traçage et servant de référence pour la mesure.

Série de données : série de points de données groupés représentant une série de données.

Point de donnée : forme représentant une donnée.

Étiquette de données : fournit une information sur un point de donnée ou sa valeur.

Légende : zone qui identifie les motifs ou les couleurs attribués aux séries de données.

L’élément sélectionné est signalé par des poignées de sélection.

Pour sélectionner un élément du...

GESTION ET ANALYSE DE DONNÉES

5

CONSOLIDATION

Une consolidation agrège les données de plusieurs plages source dans une plage destination, selon une fonction (somme, moyenne…) spécifiée. Vous pouvez consolider :

– en fonction de la position des cellules : on consolide les cellules occupant la même position dans les plages source de consolidation ;

– en fonction des étiquettes de ligne ou de colonnes : on consolide les cellules ayant les mêmes étiquettes de ligne et de colonne dans les plages source de consolidation.

Les plages source et la destination de consolidation peuvent être situées dans des feuilles distinctes du même classeur ou dans des classeurs distincts.

CONSOLIDER LES CELLULES SELON LEUR POSITION DANS LES PLAGES

Vous disposez de feuilles de calcul présentant les résultats d’un magasin. Vous désirez les consolider pour obtenir les résultats pour l’ensemble des magasins.

Créez une nouvelle feuille France pour y placer le résultat de la consolidation, actionnez la première cellule de la zone destination de consolidation (exemple C3 dans la feuille France).

Onglet Données>groupe Outils de données, actionnez le bouton Consolider.

– Ajoutez la référence d’une plage source : actionnez l’icône à droite de la zone <Référence> (cela réduit la boîte de dialogue), actionnez l’onglet de feuille Marseille, spécifiez la plage de données à consolider B2:E6 (*), actionnez à nouveau l’icône à droite de la zone . Puis, actionnez le bouton [Ajouter] .

(*) Souris : faites glisser le pointeur de la cellule B2 jusque sur la cellule E6 ; tactile : appui sur la cellule B2, tapez F8 ou : (deux-points), puis appui sur la cellule E6.

– Ajoutez de la même façon la référence à une deuxième plage source, D2:G6 dans la feuille Lyon, et ainsi de suite pour les autres plages source de consolidation.

Terminez par [OK].

CONSOLIDER LES DONNÉES SELON LES ÉTIQUETTES

Les cellules des plages de données source qui ont les mêmes valeurs d’étiquettes de ligne ou de colonne sont consolidée entre elles. Les étiquettes doivent être dans la ligne du haut et dans la colonne de gauche des plages source et destination de consolidation.

Le texte des étiquettes doit être strictement identique pour que les lignes ou les colonnes puissent être mises en correspondance. Si une étiquette de ligne ou de colonne ne correspond à aucune étiquette d’une plage source, les cellules de cette ligne ou colonne ne recevront pas de valeur.

Saisissez les étiquettes que vous voulez consolider dans la plage destination, puis sélectionnez cette plage destination incluant les étiquettes puis, sous l’onglet Données>groupe Outils de données, actionnez le bouton Consolider.

Dans le dialogue Consolider, ajoutez les plages source incluant les étiquettes de ligne et de colonne (cf. page précédente). Et, cochez les cases < Ligne du haut> et/ou < Colonne de gauche>.

Validez par [OK].

Les paramètres des plages de consolidation et des plages destination sont enregistrés : toute feuille dans laquelle une consolidation a été définie conserve ces paramètres, qui sont enregistrés avec le classeur, de sorte que vous les retrouvez à l’ouverture du classeur.

CONSOLIDER AVEC LIAISON

Dans les options de la boîte de dialogue de consolidation, vous pouvez cocher < Lier aux données source> pour créer une liaison permanente entre la plage destination et les plages source. Le tableau généré est alors structuré en mode Plan et mis à jour en permanence, grâce à des formules de référence externe.

Le tableau de consolidation contient des références vers les données source au niveau 2 de plan, et des formules d’agrégation au niveau 1 de plan. La cellule C3 contient la référence externe =Lyon!$F$6, la cellule D3 contient la référence externe =Lyon!$G$6, la cellule C4 contient la référence externe =Marseille!$D$6, la cellule D4 contient la référence externe =Marseille!$E$6 et les cellules C5 et C6 contiennent les formules d’agrégation =SOMME(C3:C4) et =SOMME(D3:D4).

TRIER, TRANSPOSER UNE PLAGE DE CELLULES

TRIER UNE PLAGE DE CELLULES

Trier rapidement les lignes sur une seule clé

Sélectionnez une cellule de la colonne clé de la plage à trier, Excel étend automatiquement la plage à trier jusqu’à la première colonne vide et jusqu’à la première ligne vide.

Sous l’onglet Données>groupe Trier et filtrer, actionnez le bouton Trier de A à Z ou le bouton Trier de Z à A .

Trier sur plusieurs clés ou trier de gauche à droite

Utilisez cette méthode lorsque la plage à trier comporte une ligne ou une colonne vide, ou si vous voulez trier à plusieurs niveaux, également pour trier de gauche à droite (au lieu de haut en bas).

Sélectionnez la plage à trier (si la plage ne...

CONTRÔLE, ÉCHANGE ET COLLABORATION

6

CONTRÔLER LA VALIDITÉ DES DONNÉES À LA SAISIE

Vous pouvez restreindre le type de données et les valeurs que les utilisateurs entrent dans les cellules. Vous pouvez prévoir des messages descriptifs d’aide à la saisie pour les utilisateurs.

La règle de validité n’est pas une composante du format de cellule, elle n’est donc pas copiée par copie de format. Mais elle peut l’être par copier-coller spécial en sélectionnant < Validation>.

Sélectionnez les cellules pour lesquelles vous désirez définir une règle de validité. Puis, Onglet Données>groupe Outils de données, actionnez le bouton Validation des données, puis l’option Validation des données.

– Onglet Options : spécifiez le type de données autorisées et l’intervalle des valeurs possibles, ces critères restent sans effet sur les valeurs déjà présentes dans les cellules.

– Onglet Message de saisie : spécifiez le message descriptif d’aide à la saisie.

– Onglet Alerte d’erreur : spécifiez l’action, par exemple Arrêt, qui sera effectuée en cas de saisie d’une valeur non valide, et un message descriptif de l’erreur.

Actionnez [OK] pour valider.

CONTRÔLER L’ACCÈS FICHIER PAR MOT DE PASSE

CONTRÔLER L’OUVERTURE D’UN CLASSEUR PAR MOT DE PASSE

Vous pouvez enregistrer un classeur avec un mot de passe pour la lecture et un mot de passe pour la modification. Seules les personnes connaissant le premier pourront ouvrir le fichier, seules les personnes connaissant le second pourront enregistrer le fichier.

Définir un mot de passe

Ouvrez le classeur, actionnez l’onglet Fichier, puis Enregistrer sous…, sélectionnez le dossier ou le bouton [Parcourir] (raccourci clavier F12), le dialogue Enregistrer sous s’affiche. Actionnez la flèche du bouton Outils, puis l’option Options générales…

– Tapez un mot de passe pour la lecture , et éventuellement un mot de passe pour la modification , validez par [OK]. Excel demande confirmation : retapez le mot de passe pour la lecture et validez par [OK], retapez le mot de passe pour la modification et validez par [OK].

Actionnez le bouton [Enregistrer] pour enregistrer le classeur.

Si vous avez protégé le fichier classeur avec les deux protections, les deux mots de passe sont demandés à l’ouverture du document. Si vous avez entré seulement un mot de passe pour la lecture, seul ce mot de passe est demandé et le classeur ouvert peut être modifié et enregistré.

Attention : dans un mot de passe, Excel fait la différence entre les majuscules et les minuscules.

Retirer la protection de fichier par mot de passe

Ouvrez le classeur, actionnez l’onglet Fichier, puis l’option Enregistrer sous…, sélectionnez le dossier ou le bouton [Parcourir] (raccourci clavier F12), actionnez la flèche du bouton Outils, puis l’option Options générales…

Effacez le(s) mot(s) de passe, validez par [OK], puis actionnez [Enregistrer].

RECOMMANDER LA LECTURE SEULE

Pour éviter que les relecteurs ne modifient accidentellement un fichier, vous pouvez prévoir l’affichage d’une invite à l’ouverture du fichier qui recommande mais n’impose pas l’ouverture du fichier en lecture seule.

Ouvrez le classeur, actionnez l’onglet Fichier, puis l’option Enregistrer sous…, sélectionnez le dossier ou le bouton [Parcourir] (raccourci clavier F12), actionnez la flèche du bouton Outils, puis l’option Options générales…

Cochez l’option < Lecture seule recommandée>, validez par [OK], puis [Enregistrer].

À l’ouverture du fichier, une invite mentionne que la lecture seule est recommandée.

Si l’utilisateur choisit [Oui], il peut tout de même modifier le classeur, mais il ne peut pas l’enregistrer sauf dans un autre fichier, sous un autre nom.

VERROUILLAGE ET PROTECTION DES CELLULES

VERROUILLER OU DÉVERROUILLER

...

ANNEXES

7

CORRESPONDANCES EXCEL 2003 – EXCEL 2013

Menu Fichier | Onglet | Groupe | Bouton
Fichier | Fichier
Nouveau | Fichier | Nouveau
Ouvrir… | Fichier | Ouvrir
Fermer | Fichier | Fermer
Enregistrer | Barre d’outils Accès rapide | outil Enregistrer
Enregistrer | Fichier | Enregistrer
Enregistrer sous | Fichier | Enregistrer sous
Enregistrer en tant que page Web | Fichier | Enregistrer sous | <Type> : Page Web
Enregistrer l’espace de travail | Retiré du produit
Recherche de fichiers | Dans l’Explorateur de fichiers
Autorisation | Accès illimité | Fichier | Informations | Protéger le classeur | Restreindre autorisation (Office pro Plus)
Autorisation | Ne pas distribuer | Fichier | Informations | Protéger le classeur | Restreindre autorisation (Office pro Plus)
Autorisation | Limiter les autorisations | Fichier | Informations | Protéger le classeur | Restreindre autorisation (Office pro Plus)
Historique des versions | Fichier | Informations | Versions
Aperçu de la page Web | Ajout de Aperçu page Web sur la barre d’outils Accès rapide
Mise en page | Mise en page | Mise en page | Lanceur du dialogue
Zone d’impression | Définir | Mise en page | Mise en page | Zone d’impression | Définir
Zone d’impression | Annuler | Mise en page | Mise en page | Zone d’impression | Annuler
Aperçu avant impression | Fichier | Imprimer
Imprimer | Fichier | Imprimer
Imprimer | Ajout de Imprimer à la barre d’outils Accès rapide
Envoyer à | Destinataire | Ajout de Envoyer au destinataire du message à la barre d’outils Accès rapide
Envoyer à | Expéditeur initial | Ajout de Répondre en incluant modifications à la barre d’outils Accès rapide
Envoyer à | Destinataire du message (pour révision) | Ajout de Envoyer pour révision à la barre d’outils Accès rapide
Envoyer à | Destinataire du message (en tant que pièce jointe) | Fichier | Partager | courrier électronique | En tant que pièce jointe
Envoyer à | Destinataire du routage | Retiré du produit
Envoyer à | Dossier Exchange | Ajout de Envoyer vers un Dossier Exchange à la barre d’outils Accès rapide
Envoyer à | Participant en ligne | Retiré du produit
Envoyer à | Destinataire utilisant le service de télécopie Internet | Fichier | Patager | Courrier électronique | Télécopie Internet
Propriétés | Fichier | Informations | Propriétés /Propriétés avancées
Documents récents | Fichier | Ouvrir | Classeurs utilisation récente
Quitter | Ajout de Quitter à la barre d’outils Accès rapide
Déconnexion | Retiré du produit

Menu Édition | Onglet | Groupe | Bouton
Annuler | Barre d’outils Accès rapide | Outil Annuler
Rétablir | Barre d’outils Accès rapide | Outil Rétablir
Couper | Accueil | Presse-papiers | Couper
Copier | Accueil | Presse-papiers | Copier
Presse-papiers Office | Accueil | Presse-papiers | Lanceur du dialogue
Coller | Accueil | Presse-papiers | Coller
Collage spécial | Accueil | Presse-papiers | Coller | Collage spécial
Coller comme lien hypertexte | Accueil | Presse-papiers | Coller | Collage spécial | Lien hypertexte
Remplissage | En bas | Accueil | Édition | Remplissage | En bas
Remplissage | À droite | Accueil | Édition | Remplissage | À droite
Remplissage | En haut | Accueil |

...

PARTIE 2

CAS PRATIQUES

CAS 1 : TECHNIQUES ÉLABORÉES DE MISE EN FORME

Créez un style de cellule et mettez en forme le tableau

Grisez une ligne sur deux

Définissez un en-tête et un pied de page

Utilisez la mise en forme conditionnelle

Fonctions utilisées

– Créer un style de cellule

– Griser une ligne sur deux

– En-têtes et pieds de page personnalisés

– Mise en page

– Mise en forme conditionnelle

Vous allez réviser certaines techniques élaborées de mise en forme dans Excel 2013, en particulier la mise en forme conditionnelle sur différents exemples : griser une ligne sur deux dans un grand tableau pour permettre une meilleure lisibilité, comparer visuellement des résultats par des barres colorées en fond de cellule, repérer visuellement des informations qui satisfont certains critères. Les données chiffrées se trouvent dans le classeur CasA1.xlsx, enregistré dans C:\Exercices Excel 2013. Ouvrez ce fichier classeur et enregistrez-le sous le nom CasA1-R.xlsx.

1-DÉFINISSEZ LES BORDURES DU TABLEAU DES POPULATIONS

Sélectionnez la feuille Population. Ajustez automatiquement la largeur de la première colonne au contenu des données : sélectionnez les cellules contenant les données puis, sous l’onglet Accueil>groupe Cellules, actionnez le bouton Format puis l’option Ajuster la largeur de colonne. Essayez dans un premier temps en sélectionnant une...

CAS 2 : RELANCE DE FACTURES IMPAYÉES

Les fonctions AUJOURDHUI() et FIN.MOIS()

Les calculs

Le résultat

Fonctions utilisées

– Fonction AUJOURDHUI()

– Format personnalisé

– Alignement vertical dans les cellules

– Calculs sur dates

– Hauteur des lignes

– Fonction TEXTE()

Ce cas pratique consiste à suivre les factures impayées d’une entreprise. Après avoir reporté dans une feuille de calcul, la liste des factures impayées, on calcule, pour chaque facture, son échéance normale et le nombre de jours de retard de règlement.

Les données sont fournies dans le classeur CasA2.xlsx, enregistré dans le dossier

C:\Exercices Excel 2013. Ouvrez le fichier et enregistrez-le sous le nom CasA2-R.xlsx.

1-INSÉREZ LA FONCTION AUJOURDHUI() AVEC L’ASSISTANT FONCTION

Le rôle de cette...

CAS 4 : UTILISER DES FONCTIONS FINANCIÈRES

Les formules

Le résultat

Fonctions utilisées

– Fonctions financières : VC, VPM, TAUX, TRI

– Assistant fonction

– Formatage automatique selon la saisie ou la formule

Vous allez utiliser les fonctions financières d’Excel pour réaliser trois types de calculs financiers : placement, emprunt et rentabilité d’un investissement.

Les données sont dans le classeur CasA4.xlsx, enregistré dans le dossier C:\Exercices Excel 2013. Ouvrez le fichier et enregistrez-le sous le nom CasA4-R.xlsx.

1-VALEUR DU CAPITAL FINAL DE VERSEMENTS RÉGULIERS À TAUX D’INTÉRÊT CONSTANT

Vous placez 10 000 € initialement en début d’un mois, puis 1 000 € chaque début de mois suivant. Le taux d’intérêt annuel est de 3,5 %. De combien disposerez-vous dans six ans ? Excel fournit une fonction pour ce calcul : VC(Taux;Npm;Vpm;Va;Type).

Taux est le taux sur...

CAS 6 : UTILISER DES FORMULES MATRICIELLES

Plage matricielle produit d’une matrice colonne par un taux

Plage matricielle produit de deux matrices colonne

La cellule contient une formule matricielle somme du produit de deux matrices

Fonctions utilisées

– Fonction de recherche dans une table

– Format des nombres

– Formule matricielle

– Utilisation des noms

– Tri de lignes

Une formule matricielle est entrée comme une formule unique dans une matrice de cellules et traite autant de calculs à la fois qu’il y a de cellules dans la matrice. On gagne du temps à la saisie, de plus la taille de la feuille de calcul est réduite. Autre particularité qui peut être vue comme un avantage, il n’est pas possible d’insérer ou de supprimer des cellules individuelles dans une plage matricielle sans enlever d’abord la formule matricielle, ce qui évite les erreurs de manipulation. Excel fournit des fonctions matricielles : produit, somme, multiplication, etc.

1-MULTIPLIEZ UNE COLONNE PAR UNE

...

CAS 10 : VALEUR CIBLE

Quel salaire brut permet d’atteindre 1 000 € de salaire net ?

Calcul de point mort

Fonctions utilisées

– Valeur cible

– Utilisation des noms

La fonction valeur cible permet de trouver quelle valeur d’une variable d’une formule permet d’atteindre un résultat pour cette formule. Par exemple, on sait calculer dans un tableau Excel le salaire net d’un salarié à partir du salaire brut en calculant les retenues de charges sociales. La fonction valeur cible permet de faire le raisonnement à l’envers, il répond à la question : quelle valeur du salaire brut correspond à un salaire net annoncé au salarié ?

...

CAS 13 : CONSOLIDATION

Les trois tableaux à consolider

Le tableau de consolidation avec liaison

Fonctions utilisées

– Copier une feuille entière

– Consolider par position

– Consolider par étiquettes

– Consolider avec liaison

La consolidation combine les données de plusieurs plages source de cellules dans une nouvelle plage, en utilisant une fonction que vous spécifiez, en général une somme. La commande de consolidation sert à consolider les données de plusieurs plages ayant une structure identique.

Dans ce cas pratique, les plages source de suivi des ventes de trois magasins doivent être consolidées dans une plage de suivi des ventes réunissant les trois magasins ensemble.

Les trois plages source sont dans trois fichiers différents. Nous allons créer un nouveau classeur pour consolider les ventes des trois magasins.

Les données des magasins sont fournies dans les classeurs...

CAS 17 : TABLEAU CROISÉ DYNAMIQUE

Ventes par client et par mois

Ventes par ville, commissions par ville

Nombre de factures par ville, par mois et par vendeur

Commissions par ville et par vendeur

Fonctions utilisées

– Créer un tableau croisé dynamique

– Modifier les options de ce type de tableau

Les tableaux croisés dynamiques sont des tableaux de synthèse sur le contenu d’un tableau de données. Ils sont dynamiques au sens où ils sont actualisés à chaque modification apportée dans le tableau de données.

Les données de ce cas pratique sont dans le fichier CasA17.xlsx, enregistré dans le dossier C:\Exercices Excel 2013. Ouvrez le fichier et enregistrez-le sous le nom CasA17-R.xlsx.

1-CRÉEZ LE TABLEAU CROISÉ DYNAMIQUE DES VENTES PAR CLIENT

...