Livrer un classeur qu'on partage, qui calcule un prêt et qui lit l'avenir

Un classeur se livre sous la forme que son destinataire sait lire :

Besoin	Geste	Ce qu'il faut savoir
Lire sans modifier	Fichier › Exporter › Créer PDF/XPS (ou Enregistrer sous › PDF)	Options : feuilles actives, classeur entier, sélection ; ignorer les zones d'impression ; compatible PDF/A
Réimporter dans un autre logiciel	Enregistrer sous › CSV UTF-8	Une seule feuille, aucune formule, aucune mise en forme ; UTF-8 garde les accents
Travailler à plusieurs en même temps	Enregistrer sur OneDrive ou SharePoint, puis Partager	La co-édition : chacun voit le curseur des autres ; l'historique des versions (BA3) devient disponible
Changer de format	Fichier › Exporter › Modifier le type de fichier	Classeur 97-2003, Modèle, Texte (séparateur tabulation), ODS
Publier un tableau de bord	Fichier › Publier › Publier sur Power BI	Le pont vers le cours Power BI

Actualiser des données externes

Une requête ou une connexion a des propriétés (Données › Requêtes et connexions › clic droit › Propriétés, ou Données › Propriétés depuis le Tableau chargé) : Actualiser toutes les n minutes, Actualiser les données lors de l'ouverture du fichier, Activer l'actualisation en arrière-plan (continuer à travailler pendant le chargement), Supprimer les données du fichier avant l'enregistrement (un fichier léger, rechargé à l'ouverture).

Trois fonctions suffisent à raisonner sur un prêt ou un placement. Toutes partagent les mêmes arguments : taux (par période), npm (nombre de périodes), va (valeur actuelle : le capital), vc (valeur capitalisée, 0 par défaut), type (0 : paiement en fin de période, 1 : en début).

Question	Formule	Pour 15 000 € à 3 % sur 48 mois
Quelle mensualité ?	=VPM(3%/12;48;-15000)	332,01 €
Combien de mois pour 400 € par mois ?	=NPM(3%/12;-400;15000)	39,4 mois
Quel taux pour 350 € sur 48 mois ?	=TAUX(48;-350;15000)*12	5,6 % par an

Les deux pièges

• Le taux est par période. Un taux annuel de 3 % sur des mensualités s'écrit 3%/12 ; et le TAUX obtenu est mensuel, à multiplier par 12.
• Les signes. Excel raisonne en flux : ce qu'on reçoit est positif, ce qu'on verse est négatif. Un capital emprunté positif donne une mensualité négative ; d'où le -15000 pour une mensualité positive. Mélanger les signes donne #NOMBRE! ou un résultat absurde.

Les cousines : VA (quel capital pour cette mensualité ?), VC (que vaut un placement après n versements ?), INTPER et PRINCPER (la part d'intérêts et de capital d'une mensualité donnée : le tableau d'amortissement). Toutes sont dans Formules › Financier ou dans Insérer une fonction, catégorie Finances.

Un classeur livré ne doit contenir que des noms utiles et justes. Le Gestionnaire de noms (Ctrl+F3) est l'outil de cette revue :

Contrôle	Comment	Décision
Noms en erreur	Filtrer › Noms avec erreurs : la colonne Valeur affiche #REF!	Supprimer, ou corriger Fait référence à
Noms venus d'un copier-coller de feuille	Deux noms identiques, l'un étendue Classeur, l'autre étendue Feuille	Garder celui qui sert, supprimer l'autre (après Ctrl+F dans les formules)
Noms de tableau	Filtrer › Noms de tableaux : Tableau1, Tableau2	Renommer (Création de tableau › Nom du tableau) : Commandes, Produits
Noms masqués	Créés par des compléments ou le Solveur (solver_adj…), invisibles dans le Gestionnaire	Les laisser : ils sont au Solveur
Noms sans commentaire	Colonne Commentaire vide	Documenter : à quoi sert ce nom
La liste pour le dossier	F3 › Coller une liste sur une feuille Documentation	À livrer avec le classeur

Un lien hypertexte rend un classeur navigable : une table des matières en première feuille, un retour « Accueil » sur chaque onglet, un lien vers la procédure. Ctrl+K (ou Insertion › Lien) ouvre la boîte Insérer un lien hypertexte :

Lier à	Cas	Détail
Fichier ou page web existant(e)	Un site, un PDF sur le réseau	Adresse ; Info-bulle pour le texte au survol
Emplacement dans ce document	Une feuille et une cellule, ou un nom défini	La table des matières interne
Créer un document	Un nouveau fichier à rédiger plus tard	Rare
Adresse de courrier	Un courriel pré-rempli (objet compris)	mailto:

Par formule

=LIEN_HYPERTEXTE("#Synthese!A1";"Aller à la synthèse") : le # désigne un emplacement interne ; =LIEN_HYPERTEXTE("https://…";"Site") pour le web ; =LIEN_HYPERTEXTE("mailto:"&B2;"Écrire") construit le lien depuis une cellule. L'intérêt : des liens générés en série, sur une colonne de 140 clients.

Gérer

• Clic droit › Modifier le lien hypertexte, Supprimer le lien hypertexte ; pour sélectionner la cellule sans suivre le lien : clic long, ou les flèches.
• Les styles de cellules Lien hypertexte et Lien hypertexte visité contrôlent l'aspect de tous les liens de la boîte (pas ceux de la fonction).
• Fichier › Options › Vérification › Options de correction automatique › Lors de la frappe : décocher pour qu'Excel cesse de transformer toute adresse tapée en lien.

La boîte Insertion › Graphiques recommandés › Tous les graphiques liste dix-sept familles. Le choix se fait d'après la question posée aux données, et la préparation des données découle du type :

Question	Type	Données attendues
Comparer des catégories	Histogramme, Barres	Une colonne d'étiquettes, une ou plusieurs de valeurs
Suivre dans le temps	Courbes, Aires ; Histogramme si peu de points	Des dates ou périodes en première colonne
Montrer des parts d'un tout	Secteurs, Anneau (plusieurs séries), Compartimentage, Rayons de soleil (hiérarchie)	Une seule série pour les secteurs ; des niveaux pour les hiérarchiques
Relier deux mesures	Nuage de points (XY) ; Bulles pour une troisième	Deux colonnes numériques (trois pour les bulles)
Expliquer une variation	Cascade	Un départ, des plus et des moins, une arrivée (définir les totaux)
Montrer une déperdition	Entonnoir	Des étapes décroissantes (visites, paniers, commandes)
Une distribution	Histogramme statistique, Zone et valeur (boîte à moustaches)	Une seule colonne de valeurs
Par territoire	Carte	Des noms de pays, régions, villes
Deux échelles	Graphique combiné (axe secondaire, BA3)	Deux séries d'unités différentes

Création de graphique › Sélectionner les données reste la boîte maîtresse : ajouter ou retirer une série, Intervertir les lignes/colonnes, modifier les étiquettes de l'axe, et Cellules masquées et cellules vides (relier les points par un trait, ou laisser un trou).

Un sparkline est un graphique dans une cellule : Insertion › Graphiques sparkline › Courbe, Histogramme ou Positif/Négatif, une plage de données, une plage d'emplacement. L'onglet contextuel Sparkline fait le reste :

Groupe	Réglages
Sparkline	Modifier les données (plage, cellules vides : intervalles, zéro, relier)
Type	Courbe (une tendance), Histogramme (des valeurs), Positif/Négatif (le signe seulement)
Afficher	Point haut, Point bas, Points négatifs, Premier point, Dernier point, Marqueurs : des repères colorés sur la courbe
Style	Une galerie, Couleur sparkline, Couleur de marqueur (une couleur par type de point)
Groupe	Axe : même échelle pour tout le groupe (sinon chaque sparkline a la sienne, et les lignes ne se comparent pas) ; Grouper, Dissocier, Effacer

Une formule vers un autre classeur (BA3) crée une liaison. Le classeur qui en contient affiche à l'ouverture un bandeau « Avertissement de sécurité : mise à jour automatique des liens désactivée » ou une question « Voulez-vous mettre à jour ? ». L'expert gère cela depuis Données › Requêtes et connexions › Liens du classeur (le volet qui remplace la boîte « Modifier les liens » des anciennes versions) :

Action	Effet
Actualiser (un lien, ou tous)	Relit les valeurs dans le classeur source, même fermé
Ouvrir le classeur source	Pour voir d'où viennent les chiffres
Modifier la source	Le fichier a été renommé ou déplacé : on désigne le nouveau
Rompre les liens	Les formules deviennent des valeurs : le classeur est autonome, définitivement
Paramètres (Invite de démarrage)	Demander, mettre à jour sans demander, ou ne pas mettre à jour

Les liens se cachent aussi ailleurs que dans les formules : dans les noms définis, les règles de mise en forme conditionnelle, les validations de données, les graphiques, les formes. Quand « Rompre les liens » ne suffit pas et que le bandeau revient, c'est là qu'il faut chercher (Ctrl+F sur .xls dans les formules, puis le Gestionnaire de noms).

Trois fonctions que la paie, la logistique et les ressources humaines utilisent tous les jours :

Fonction	Ce qu'elle calcule	Exemple
NB.JOURS.OUVRES(début;fin;[fériés])	Le nombre de jours ouvrés (lundi à vendredi) entre deux dates, bornes comprises, moins les jours fériés listés	=NB.JOURS.OUVRES(B2;C2;Feries!$A$2:$A$12) : le délai réel de livraison
SERIE.JOUR.OUVRE(début;jours;[fériés])	La date qui tombe n jours ouvrés après (ou avant, négatif) une date	=SERIE.JOUR.OUVRE(B2;10;Feries!$A$2:$A$12) : l'échéance à 10 jours ouvrés
NB.JOURS.OUVRES.INTL, SERIE.JOUR.OUVRE.INTL	Les mêmes, avec un week-end au choix (dimanche seul, vendredi-samedi…)	Un magasin ouvert le samedi : week-end 11 (dimanche seul)
DATEDIF(début;fin;"unité")	L'écart en années, mois ou jours entiers ; unités "y", "m", "d", "ym" (mois sans les années), "md", "yd"	=DATEDIF(E2;AUJOURDHUI();"y") : l'ancienneté en années révolues

DATEDIF est une fonction non documentée : elle n'apparaît ni dans Insérer une fonction ni dans la saisie semi-automatique, mais elle existe depuis toujours (héritée de Lotus). On l'écrit entièrement à la main, et "y" avec les guillemets. Un âge « 34 ans, 7 mois » : =DATEDIF(B2;AUJOURDHUI();"y")&" ans, "&DATEDIF(B2;AUJOURDHUI();"ym")&" mois".

Un contrôle de formulaire est un objet qui écrit dans une cellule liée ; le reste du modèle lit cette cellule. Ils vivent dans Développeur › Contrôles › Insérer, partie Contrôles de formulaire (la partie ActiveX, en dessous, exige du VBA et pose des problèmes de sécurité : on l'évite).

Contrôle	Cellule liée	Usage
Case à cocher	VRAI / FAUX	« Inclure les commandes annulées » : =SI($B$1;…;…)
Bouton d'option (plusieurs dans une zone de groupe)	1, 2, 3… (le numéro du bouton coché)	Choisir un scénario
Zone de liste déroulante, zone de liste	Le rang de l'élément choisi	=INDEX(liste;$B$1) retrouve la valeur
Barre de défilement, bouton de sélection (toupie)	Un nombre entre min et max, par pas	Faire varier un taux de 0 à 10 % en direct
Bouton	aucune : il lance une macro	« Actualiser », « Imprimer »
Étiquette, Zone de groupe	aucune	Habiller, regrouper des boutons d'option

1. Insérer › le contrôle, puis tracer sur la feuille.
2. Clic droit › Format de contrôle › onglet Contrôle : la cellule liée (et, pour une liste, la plage d'entrée ; pour une barre, min, max, pas).
3. Les formules lisent la cellule liée. Clic droit › Modifier le texte pour le libellé.

La pyramide des âges

1. Trois colonnes : tranche d'âge, effectif hommes en négatif, effectif femmes en positif.
2. Insertion › Barres empilées (horizontales).
3. Format de l'axe horizontal › Nombre › format personnalisé 0;0 : les négatifs s'affichent sans signe.
4. Format de la série › Largeur de l'intervalle : 0 à 10 % pour des barres jointives ; étiquettes de l'axe vertical à gauche (Position de l'étiquette : Bas).

Le diagramme ombrothermique

Précipitations en histogramme sur l'axe principal, températures en courbe sur l'axe secondaire (graphique combiné, BA3) ; convention : l'échelle des précipitations vaut le double de celle des températures (P = 2T), à régler dans les options des deux axes (maximum fixe). Au-dessus de la courbe, le mois est sec.

Le boursier (rappel)

Colonnes dans l'ordre du sous-type (Ouverture, Max, Min, Clôture) ; les bougies montantes et descendantes se colorent dans Format de la série.

Les bulles

Trois colonnes numériques : X, Y, et la taille de la bulle (le CA, l'effectif). Insertion › Nuage de points › Bulles. Format de la série : La taille représente l'aire (lisible) ou la largeur ; Mettre les bulles à l'échelle. Les étiquettes de données prennent le nom depuis une quatrième colonne (Valeur à partir des cellules).

Ces six questions reprennent toute la séance, au format et au niveau de la certification TOSA. Réponds sans revenir en arrière.