Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Excel 2007 Utilisation Avancée MANUEL DE RÉFÉRENCE © Microsoft FAC SIMILE Ce fa

Excel 2007 Utilisation Avancée MANUEL DE RÉFÉRENCE © Microsoft FAC SIMILE Ce fac similé présente la table des matières, l'index et quelques pages de l'ouvrage. AVERTISSEMENT Microsoft Excel 2007 est une marque déposée de la société Microsoft. Toutes les marques citées dans cet ouvrage sont des marques déposées par leurs propriétaires respectifs. Cet ouvrage n’est pas une documentation officielle de Microsoft Excel 2007 et son contenu n’engage pas la société Microsoft. Ce document contient des informations protégées par copyright. Réf : PK0303 – Manuel A4 Microsoft Excel 2007 Utilisation Avancée Auteur : Philippe Moreau, Patrick Morié Mars 2008 Editeur TSoft 10, rue du Colisée 75008 Paris http://www.tsoft.fr Tél. : 01 56 88 29 64 Fax : 01 53 76 03 64 Ce pictogramme mérite une explication. Son objet est d’alerter le lecteur sur la menace que représente pour l’avenir de l’écrit, particulièrement dans le domaine de l’édition technique et universitaire, le développement massif du photocopillage. Le Code de la propriété intellectuelle du 1er juillet 1992 interdit en effet expressément la photocopie à usage collectif sans autorisation des ayants droit. Or, cette pratique s’est généralisée dans les établissements d’enseignement supérieur, provoquant une baisse brutale des achats de livres et de revues, au point que la possibilité même pour les auteurs de créer des œuvres nouvelles et de les faire éditer correctement est aujourd’hui menacée. En application de la loi du 11 mars 1957, il est interdit de reproduire intégralement ou partiellement le présent ouvrage sans autorisation de l'éditeur ou du Centre Français d’Exploitation du Droit de Copie, 20 rue des Grands-Augustins, 75006 Paris. ©TSOFT, Paris 2008 Avant-propos Conçu par des formateurs expérimentés, cet ouvrage vise à vous apprendre à utiliser efficacement les fonctions avancées du logiciel Microsoft Excel 2007. Il fait suite à un autre manuel consacré aux fonctions de base, chez le même éditeur. Ce guide s’adresse donc à des utilisateurs ayant déjà assimilé et mis en pratique les fonctions de base de Microsoft Excel 2007. Ce manuel présente sous forme de fiches pratiques l’utilisation des fonctions avancées du logiciel 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 plus ancienne d’Excel 2007 ou sur un autre logiciel tableur, ces fiches vous aideront à maîtriser rapidement les fonctions avancées d’Excel 2007. Pour réussir votre formation, vous trouverez chez le même éditeur un manuel de cas pratiques à réaliser pour mettre en œuvre les fonctions que vous aurez étudiées dans le présent ouvrage. Vous disposerez de vingt deux cas pratiques, qui vous prépareront à concevoir vos propres applications de manière autonome. Ces cas pratiques constituent un parcours de formation. La réalisation du parcours complet permet de s’initier seul en autoformation. Conventions typographiques Pour faciliter la compréhension visuelle par le lecteur de l’utilisation pratique du logiciel, nous avons adopté les conventions typographiques suivantes : Gras : les onglets, les groupes, les boutons et les zones qui sont sur le Ruban. Gras : noms des sections dans les menus ou dans les boîtes de dialogue (*). Italique : noms des commandes dans les menus et noms des boîtes de dialogue (*). Police bâton : noms de dossier, noms de fichier, 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 ». © Tsoft – Excel 2007 Utilisation Avancée 1 TABLE DES MATIÈRES 1 - CALCULS ET SIMULATIONS .......................................................................... 3 Calculer sur des dates.........................................................................................4 Utiliser les fonctions de recherche........................................................................5 Références circulaires et itérations.......................................................................7 Valeur cible........................................................................................................8 Scénarios...........................................................................................................9 Tables de données (d’hypothèses)..................................................................... 11 Solveur............................................................................................................ 13 2 - POUR AMÉLIORER VOTRE EFFICACITÉ ....................................................... 15 Commentaires.................................................................................................. 16 Rechercher et remplacer................................................................................... 17 Vérifier l’orthographe........................................................................................ 19 Générer une série............................................................................................. 20 Listes personnalisées ........................................................................................ 21 Mise en forme conditionnelle............................................................................. 22 Utiliser des liens hypertextes............................................................................. 25 Automatiser avec les macros ............................................................................. 28 3 - DESSINS, IMAGES ET OBJETS GRAPHIQUES.............................................. 31 Insérer une image ou un clipart......................................................................... 32 Créer une zone de texte.................................................................................... 35 Insérer un Smartart.......................................................................................... 36 Insérer des formes ........................................................................................... 39 Positionner les objets........................................................................................ 42 4 - REPRÉSENTATION GRAPHIQUE DES DONNÉES .......................................... 45 Créer un graphique........................................................................................... 46 Modifier les données source .............................................................................. 47 Disposer les éléments sur le graphique .............................................................. 48 Mettre en forme les éléments du graphique........................................................ 49 Modifier le type de graphique............................................................................ 51 Modèles de graphique, copier le graphique......................................................... 54 5 - GESTION ET ANALYSE DE DONNÉES .......................................................... 55 Consolidation ................................................................................................... 56 2 © Tsoft – Excel 2007 Utilisation Avancée Trier, transposer une plage de cellules ...............................................................58 Créer un tableau de données.............................................................................59 Filtrer un tableau de données ............................................................................61 Fonctions statistiques sur Base de données ........................................................64 Sous-totaux......................................................................................................65 Mode Plan........................................................................................................66 Tableaux croisés dynamiques ............................................................................68 Graphique croisé dynamique .............................................................................73 Se connecter à une source de données externe ..................................................74 Utiliser Microsoft Query.....................................................................................76 6 - CONTRÔLE, ÉCHANGE ET COLLABORATION ................................................ 79 Contrôler la validité des données à la saisie ........................................................80 Contrôler l’accès fichier par mot de passe...........................................................81 Verrouillage et protection des cellules ................................................................82 Limiter les autorisations (IRM)...........................................................................84 Travailler à plusieurs sur un classeur..................................................................85 Importer et exporter des fichiers texte ...............................................................88 Échanger des données entre Excel et Access ......................................................91 Insérer un tableau Excel dans un document Word...............................................93 Envoyer un classeur par messagerie...................................................................95 Publier au format HTML ....................................................................................96 7 - ANNEXES...................................................................................................... 97 Où sont passées les commandes d’Excel 2003 ....................................................98 Paramétrage des options Excel 2007................................................................105 INDEX.............................................................................................................. 111 CALCULS ET SIMULATIONS 1 4 © Tsoft – Excel 2007 Utilisation Avancée 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égionales et linguistiques de Windows. Pour modifier ce seuil, accédez au panneau de configuration Windows, Horloge, langue et région, puis Options régionales et linguistique, cliquez sur [Personnaliser le format], puis sous l’onglet Date : modifiez la zone contenant le seuil. SAISIR DES DATES OU DES HEURES Pour saisir une date, séparez les jours, mois et année par / ou par -, par exemple 24/12/49, 24-12-49. Si le mois est saisi en lettres, vous pouvez le séparer par des espaces 24 déc 49. Pour saisir une heure, utilisez le séparateur deux-points (:), par exemple 22:5:10 correspond à 22H 05 minutes et 10 secondes. LE NUMÉRO DE SÉRIE 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 12: correspond à 0,5 – 18: correspond à 0,75 – 18:15 correspond à 0,760416666666667. Il existe un autre système de numérotation des dates partir du 1er janvier 1904 (utilisé par Excel pour Mac). Dans les options avancées d’Excel, il est possible d’activer une case < Utiliser le calendrier depuis 1904> pour utiliser ce système de date. CALCULER SUR DES DATES = A2-A2 renvoie le nombre de jours écoulés entre les deux dates contenues dans A1 et A2. =AUJOURDHUI() renvoie la date du jour actuel. =PLAFOND(MOIS(A1)/3;1) calcule le numéro du trimestre (n° du mois /3, arrondi supérieur). =FIN.MOIS(A1;0) cette fonction renvoie la date du dernier jour du mois de la date. =FIN.MOIS(A1;-1)+1 renvoie la date du 1er jour du mois (dernier jour du mois précédent+1). =NO.SEMAINE(A1) renvoie le numéro de semaine de la date. =JOUR(FIN.MOIS(A1)) renvoie le nombre de jour du mois (n° du dernier jour du mois). ="Échéance:"&TEXTE(A1;"jjjj mmmm aaaa") affiche la date en toutes lettres dans un texte. =TEXTE(A1;"jjjj") renvoie le jour de la semaine en toutes lettres. =TEXTE(A1;"mmmm") renvoie le mois en toutes lettres. =SI(AUJOURDHUI()>A1;"date dépassée") affiche le texte si la date dans A1 est passée. =110/24/60 convertit 110 mn en heure et minutes, soit 01:50 en format hh:mm. =9,75/24 convertit l’heure en décimale 9,75 en heure et minutes, soit 9:45 en format hh:mm. © Tsoft – Excel 2007 Utilisation Avancée 5 UTILISER LES FONCTIONS DE RECHERCHE Par exemple, vous avez une liste d’articles et vous voulez créer un devis dans lequel vous voulez sélectionner les titres des articles. Les informations concernant les articles (référence et prix) doivent s’afficher automatiquement dans le devis lorsque vous avez entré un titre. Liste déroulante pour faciliter la saisie des titres Dans les cellules B5:B10 de saisie des titres, une liste déroulante d’entrées provenant de la plage nommée Titre ($A$2:$A$13 dans la feuille Tarif) peut être définie pour sélectionner le titre plutôt que d’avoir à le saisir. Sélectionnez les cellules B5:B10, puis sous l’onglet Données>groupe Outils de données, cliquez sur la flèche du bouton Validation de données. Sélectionnez Liste, sélectionnez le nom de plage Titre, validez par [OK]. Lorsque vous cliquez sur une de ces cellules, un uploads/Litterature/ excel-2007-manuel 1 .pdf