Connaître tout Excel, optimiser avec le Solveur, écrire ses macros

Le ruban d'Excel 365 a neuf onglets permanents : Fichier, Accueil, Insertion, Dessin, Mise en page, Formules, Données, Révision, Affichage, plus Automatiser (scripts Office) et Aide, et l'onglet Développeur quand on l'affiche. L'expert connaît surtout les onglets contextuels, ceux qui n'existent que lorsqu'un objet est sélectionné :

Objet sélectionné	Onglet(s) qui apparaissent
Un Tableau	Création de tableau
Un tableau croisé	Analyse du tableau croisé dynamique, Création
Un graphique	Création de graphique, Format
Une forme, une image, un SmartArt	Format de la forme, Format de l'image, Création SmartArt + Format
Un segment, une chronologie, un sparkline	Segment, Chronologie, Sparkline
Un en-tête en mode Mise en page	En-tête et pied de page
Une requête Power Query chargée	Requête

Le bouton Options d'affichage du ruban (flèche à droite du ruban) propose Ruban simplifié (une ligne), Afficher les onglets uniquement et Plein écran ; Ctrl+F1 replie et déplie le ruban.

Un classeur qu'on livre peut être signé : la signature garantit qui l'a validé et qu'il n'a pas changé depuis. Excel propose deux formes, toutes deux dans Fichier › Informations › Protéger le classeur ou Insertion › Texte › Ligne de signature :

Forme	Ce qu'on voit	Ce que ça prouve
Ligne de signature	Un cadre « X ____ » avec le nom du signataire suggéré, son titre, des instructions ; une fois signé, la signature (texte ou image) apparaît dedans	L'identité du signataire et l'intégrité du fichier, si un certificat numérique a servi à signer
Signature numérique invisible	Rien dans la feuille ; une icône de signature dans la barre d'état et un volet Signatures	Idem, sans élément visible
Marquer comme final	Un bandeau « Marqué comme final », classeur en lecture seule	Rien : un simple avertissement, qu'un clic sur Modifier quand même lève

Signer exige un certificat : délivré par une autorité (payant, reconnu partout) ou auto-signé (gratuit, valable sur son poste seulement). Toute modification après signature invalide la signature : c'est son rôle. Un classeur signé se partage en lecture ; pour le modifier, on retire la signature, on change, on signe à nouveau.

Une formule imbriquée (SI dans SIERREUR dans INDEX…) donne un résultat faux et on ne voit pas où. Formules › Vérification des formules › Évaluer la formule la déroule : à chaque clic sur Évaluer, la partie soulignée est calculée et remplacée par son résultat.

Bouton	Ce qu'il fait
Évaluer	Calcule la partie soulignée (Excel suit l'ordre des opérations et des parenthèses)
Pas à pas détaillé	Entre dans la cellule soulignée pour voir sa formule (actif quand la partie soulignée est une référence à une formule)
Pas à pas sortant	Revient à la formule de départ
Redémarrer	Recommence depuis le début, une fois la formule entièrement évaluée

L'alternative rapide : F9 dans la barre de formule

Sélectionne un morceau de formule dans la barre de formule et appuie sur F9 : Excel affiche sa valeur. Puis Échap, impérativement : Entrée remplacerait le morceau par sa valeur, définitivement.

Un symbole est un caractère comme un autre : Insertion › Symboles › Symbole ouvre la boîte Caractères spéciaux : on choisit la police, le sous-ensemble (Symboles monétaires, Flèches, Opérateurs mathématiques…), on double-clique le caractère. Le code du caractère (Unicode hexadécimal, par exemple 20AC pour €) s'affiche en bas : tapé dans une cellule puis Alt+X, il se transforme en caractère.

Besoin	Moyen
€, ©, →, ≠, ≤	Boîte Caractères spéciaux, ou Alt+0128 (€) sur le pavé numérique
Un symbole par formule	=UNICAR(8364) donne € ; =CAR(169) donne © ; =UNICODE("→") donne le code
Une coche ✓ dans une liste	Police Wingdings 2 ou Segoe UI Symbol, ou =UNICAR(10003)
Une formule mathématique à montrer	Insertion › Symboles › Équation : un objet avec son onglet Équation (fractions, indices, racines, intégrales) ; les équations intégrées (aire du cercle, formule quadratique…) s'insèrent d'un clic

Formes, images, graphiques, SmartArt : tous sont des objets posés au-dessus de la grille, avec les mêmes outils.

Outil	Où	Ce qu'il règle
Volet Format de la forme	Clic droit › Format de la forme, ou Ctrl+1 sur l'objet	Remplissage (uni, dégradé, image, transparence), Contour, Effets (ombre, lumière), Taille et propriétés, Options de texte
Modifier la forme	Format de la forme › Insérer des formes › Modifier la forme	Changer de forme (un rectangle devient une flèche, texte conservé) ou Modifier les points (déplacer chaque sommet)
Aligner, Grouper, Rotation	Format › Organiser	Aligner plusieurs objets, les grouper pour les déplacer ensemble, faire pivoter
Avancer, Reculer	Format › Organiser	L'ordre d'empilement : quel objet passe devant
Volet Sélection	Accueil › Rechercher et sélectionner › Volet Sélection, ou Alt+F10	La liste de tous les objets de la feuille : les renommer, les masquer (œil), changer leur ordre, sélectionner un objet caché sous un autre

La valeur cible change une cellule pour atteindre un résultat. Le Solveur change plusieurs cellules pour maximiser, minimiser ou atteindre une cellule objectif, en respectant des contraintes. C'est un complément : Fichier › Options › Compléments › Atteindre : Compléments Excel › cocher Complément Solver ; il apparaît alors dans Données › Analyse.

Le cas Datashop : un budget d'achat

Vingt références, un coût d'achat, une marge unitaire, un budget de 20 000 €. Combien commander de chaque pour maximiser la marge totale sans dépasser le budget ni le stock maximal de chaque produit ?

1. Objectif à définir : la cellule de la marge totale (=SOMMEPROD(quantités;marges)) ; Max.
2. Cellules variables : la colonne des quantités à commander.
3. Contraintes : Ajouter : dépense totale <= 20 000 ; quantités <= stock maxi ; quantités = ent (entières) ; et la case Rendre les variables sans contrainte non négatives.
4. Méthode de résolution : Simplex PL pour un problème linéaire (le nôtre), GRG non linéaire quand des carrés, divisions ou puissances interviennent, Évolutionnaire pour les cas avec SI ou RECHERCHE.
5. Résoudre.

Lire et garder les résultats

La boîte Résultats du solveur dit si une solution a été trouvée (« Le Solveur a trouvé une solution satisfaisant toutes les contraintes »). On choisit Conserver la solution du solveur ou Rétablir les valeurs d'origine, et on coche des rapports : le rapport Réponses (valeurs initiales et finales, contraintes saturées ou non), Sensibilité (de combien l'objectif bouge si une contrainte se desserre), Limites. Chaque rapport devient une feuille.

Tu sais exécuter une macro (séance 2). Place à l'enregistrement : Excel note tes gestes et les écrit en VBA.

1. Enregistre le classeur en .xlsm avant (ou choisis le Classeur de macros personnelles).
2. Affichage › Macros › Enregistrer une macro (ou l'onglet Développeur, ou le bouton d'enregistrement de la barre d'état).
3. La boîte : un nom sans espace (Nettoyer_import), une touche de raccourci (Ctrl+Maj+lettre, pour ne pas écraser un raccourci d'Excel), Enregistrer la macro dans : Ce classeur, Nouveau classeur, ou Classeur de macros personnelles (disponible dans tous tes classeurs, sur ce poste), une description.
4. Fais les gestes, proprement, sans hésiter : tout est enregistré, les erreurs aussi.
5. Arrêter l'enregistrement.

Références relatives ou absolues

Par défaut, la macro enregistre des adresses absolues : « sélectionner C5 ». Avec Utiliser les références relatives (bouton à cocher avant d'enregistrer), elle enregistre des déplacements : « deux cellules à droite ». La première convient à une mise en page fixe, la seconde à un traitement ligne par ligne qu'on relance ailleurs.

Gérer ses macros

Besoin	Geste
Voir ou corriger le code	Boîte Macro › Modifier, ou Alt+F11 : le module dans l'explorateur de projet
Changer le raccourci ou la description	Boîte Macro › Options
Supprimer	Boîte Macro › Supprimer (pour le classeur personnel, afficher d'abord PERSONAL.XLSB par Affichage › Afficher)
Un bouton pour la lancer	Une forme › clic droit › Affecter une macro

Le résultat net est en Synthese!B40, et tu modifies des hypothèses sur une autre feuille : à chaque changement, il faudrait aller voir. Formules › Vérification des formules › Fenêtre Espion ouvre une petite fenêtre, toujours visible, qui affiche pour chaque cellule espionnée : classeur, feuille, nom, cellule, valeur et formule, mis à jour en direct.

1. Ajouter un espion…, puis sélectionne la ou les cellules (on peut en ajouter plusieurs d'un coup).
2. Travaille n'importe où : la fenêtre suit. Un double-clic sur une ligne de la fenêtre y conduit.
3. Supprimer un espion quand c'est fini. La fenêtre s'ancre en haut ou reste flottante ; les espions sont enregistrés avec le classeur.

En BA3, tu construis, mets en forme et actualises un tableau croisé. L'expert ouvre la boîte Analyse du tableau croisé dynamique › Options (ou clic droit › Options du tableau croisé dynamique) et sait ce que chacun de ses six onglets change :

Onglet	Réglages utiles
Disposition et mise en forme	Centrer les cellules fusionnées ; Conserver la mise en forme des cellules lors de la mise à jour ; Ajuster automatiquement la largeur des colonnes (à décocher sur un tableau de bord) ; valeur à afficher pour les cellules vides et les erreurs
Totaux et filtres	Afficher les totaux généraux des lignes et des colonnes ; autoriser plusieurs filtres par champ
Affichage	Boutons de développement, en-têtes de champs, disposition classique (glisser-déposer dans la grille), trier de A à Z ou dans l'ordre de la source
Impression	Répéter les étiquettes de lignes, saut de page après chaque élément
Données	Actualiser les données lors de l'ouverture du fichier ; enregistrer ou non les données sources avec le fichier ; nombre d'éléments à conserver par champ (les éléments fantômes)
Texte de remplacement	Accessibilité

Trois gestes d'expert

• Grouper : clic droit sur une date › Grouper : mois, trimestres, années d'un coup ; sur un nombre : des tranches (0-100, 100-200…).
• Disposition du rapport (onglet Création) : forme compactée, mode Plan, forme tabulaire ; Répéter toutes les étiquettes d'éléments pour une feuille réutilisable par RECHERCHEV.
• Champs, éléments et jeux › Champ calculé : une colonne de calcul dans le tableau croisé (=Total_HT*0,2), qui n'existe pas dans la source.

Un tableau croisé sait bien plus qu'additionner. Tout se règle dans Paramètres des champs de valeurs (clic droit sur une valeur, ou Analyse › Champ actif › Paramètres de champ), qui a deux onglets :

Onglet	Choix	Exemple
Synthèse des valeurs par	Somme, Nombre, Moyenne, Max, Min, Produit, Nombre de nombres, Écart type, Variance	La commande moyenne par canal, le nombre de commandes par mois
Afficher les valeurs	% du total général, % du total de la colonne, % du total de la ligne, % de, % du total de la ligne parente, Différence par rapport, % de différence par rapport, Résultat cumulé, % du résultat cumulé, Rang du plus petit au plus grand, Index	La part de chaque canal, l'évolution d'un mois à l'autre, le CA cumulé depuis janvier

Le même champ peut être glissé deux fois dans Valeurs : une fois en somme, une fois en % du total : deux colonnes côte à côte.

Sous-totaux et totaux généraux

Onglet Création : Sous-totaux (ne pas afficher, en haut du groupe, au bas du groupe), Totaux généraux (activés pour les lignes et les colonnes, désactivés, ou l'un des deux). Un clic droit sur une étiquette › Paramètres de champ permet de choisir, pour ce champ, plusieurs fonctions de sous-total (Somme et Moyenne) ou aucune.

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