Lernzettel: Maîtrise des outils d'Excel avancés

📋 Plan du Cours

1. Valeur cible Goal Seek : principe et paramètres
2. Valeur cible : exemple de moyenne à atteindre
3. Valeur cible : limites et ajustements progressifs
4. Solveur : objectif, contraintes et fonctionnement
5. Solveur : modélisation, contraintes et structuration
6. Fonction RECHERCHEV : paramètres et correspondance
7. Fonction RECHERCHEX : version améliorée
8. Fonctions Texte : CONCAT, GAUCHE, DROITE et STXT

📖 1. Valeur cible Goal Seek : principe et paramètres

🔑 Notions clés & Définitions

• Valeur cible : Outil de feuille de calcul qui ajuste une seule variable pour obtenir un résultat fixé dans une cellule cible.
• Cellule cible : Cellule qui contient une formule et dont Excel doit forcer la valeur à atteindre la valeur souhaitée.
• Valeur souhaitée : Résultat numérique que l’utilisateur veut obtenir dans la cellule cible après ajustement.
• Cellule à modifier : Variable unique qu’Excel change automatiquement pour faire converger la cellule cible vers la valeur souhaitée.

📝 Points essentiels

• La valeur cible s’utilise quand une cellule doit prendre une valeur précise, en modifiant une seule variable.
• La cellule cible doit obligatoirement contenir une formule, sinon l’outil ne peut pas calculer le résultat.
• Les paramètres à renseigner sont : cellule cible, valeur souhaitée, cellule à modifier.
• Excel ajuste progressivement la cellule à modifier en ajoutant ou retranchant de petites valeurs jusqu’au résultat demandé.
• Si la valeur initiale est négative, l’outil peut retourner une valeur négative (exemple avec -1 menant à -2).
• Pour de meilleurs résultats, il vaut mieux retrancher 1 plutôt qu’ajouter 3 dans l’idée d’ajustement progressif.

💡 Astuce mémo

Une seule variable pour viser une seule cible : « 1 entrée → 1 résultat ».

📖 2. Valeur cible : exemple de moyenne à atteindre

🔑 Notions clés & Définitions

• Moyenne générale : Résultat calculé à partir de notes, utilisé comme cellule cible pour fixer une note d’entrée.
• Audit : Matière dont la note sert de variable à modifier pour atteindre une moyenne générale donnée.
• Cellule G2 : Cellule contenant la moyenne générale à atteindre via la valeur cible.
• Cellule F2 : Cellule représentant la note d’Audit que l’outil ajuste automatiquement.

📝 Points essentiels

• Exemple : trouver la note d’Audit pour que la moyenne générale soit égale à 15.
• La cellule à définir comme cible est G2, car elle contient la moyenne générale.
• La valeur à atteindre est 15, donnée comme valeur souhaitée.
• La cellule à modifier est F2, car elle correspond à la note d’Audit ajustée par Excel.
• L’exemple aboutit à une note d’Audit égale à 19.5 pour atteindre la moyenne demandée.
• Le scénario se lance via le menu Données/Analyse/Scénarios/Valeur cible (chemin indiqué dans le cours).

💡 Astuce mémo

Moyenne en G2, note à ajuster en F2 : « G = moyenne, F = facteur ».

📖 3. Valeur cible : limites et ajustements progressifs

🔑 Notions clés & Définitions

• Ajustements progressifs : Mécanisme de recherche où Excel modifie la variable par petites variations pour atteindre la valeur cible.
• Limite de modélisation : Restriction pratique liée au fait que la valeur cible ne modifie qu’une seule variable.
• Bénéfice : Résultat calculé à partir d’un prix, d’une quantité et de coûts, utilisé comme cible dans l’exemple.
• Épargne mensuelle : Variable à déterminer pour atteindre un montant final sur une durée donnée.

📝 Points essentiels

• Le principe d’ajustement progressif consiste à ajouter ou retrancher de petites valeurs jusqu’à obtenir le résultat souhaité.
• L’exemple de fonctionnement illustre une logique d’itération : avec une mise initiale à -1, la sortie peut être -2 (pas 2).
• Exemple bénéfice : déterminer la quantité à vendre pour obtenir un bénéfice de 1 000 €.
• Dans l’exemple bénéfice, la formule de bénéfice est : Bénéfice=(Prix × Quantité)−Coûts.
• Exemple bénéfice : Prix unitaire = 20, Coûts fixes = 2000, Bénéfice visé = 1000, avec Quantité ? comme variable.
• Exemple épargne : déterminer l’épargne mensuelle pour atteindre 5 000 € en 12 mois, avec Épargne initiale = 1000 et Montant final = 5000.

💡 Astuce mémo

Valeur cible = « petites marches » et « une seule marche à la fois ».

📖 4. Solveur : objectif, contraintes et fonctionnement

🔑 Notions clés & Définitions

• Solveur : Outil avancé qui optimise ou atteint une valeur en modifiant plusieurs cellules tout en respectant des contraintes.
• Contraintes : Règles imposées au modèle (budget, limites, ressources) que le Solveur doit respecter pendant l’optimisation.
• Objectif : Critère numérique à maximiser, minimiser ou fixer à une valeur précise (égalité) dans le Solveur.
• Variables : Cellules modifiables que le Solveur ajuste simultanément pour satisfaire l’objectif et les contraintes.

📝 Points essentiels

• Le Solveur est plus puissant que la valeur cible car il modifie plusieurs cellules en même temps.
• Le Solveur peut maximiser une valeur, minimiser une valeur ou atteindre une valeur précise.
• Le Solveur respecte des contraintes définies par l’utilisateur (budget, limites, ressources).
• Le fonctionnement cherche à modifier le moins possible les variables tout en respectant les contraintes.
• Comme pour la valeur cible, Excel modifie des cellules pour atteindre un objectif, mais avec plusieurs variables.
• Dans les exemples, l’objectif peut être un score ou un budget, pas seulement un calcul scolaire.

💡 Astuce mémo

Solveur = « plusieurs leviers + règles » pour optimiser un objectif.

📖 5. Solveur : modélisation, contraintes et structuration

🔑 Notions clés & Définitions

• Recensement des contraintes : Étape où l’on liste toutes les contraintes du problème pour ne rien oublier avant de lancer le Solveur.
• Conception de la feuille de calcul : Organisation du fichier Excel pour représenter chaque contrainte par des cellules dédiées.
• Traduction des contraintes : Mise en équations dans Excel, par exemple sous la forme Expression_1 ≤ Expression_2, via des cellules de calcul.
• Structuration du modèle : Qualité de l’organisation Excel qui conditionne fortement la réussite du Solveur.

📝 Points essentiels

• La principale difficulté du Solveur vient de la modélisation du problème, pas seulement de l’outil.
• Étape 1 : recenser toutes les contraintes (budget, ressources, limites, etc.) sans en oublier.
• Étape 2 : concevoir la feuille pour intégrer les contraintes, avec des cellules bien définies pour chaque contrainte.
• Étape 3 : traduire chaque contrainte en expressions Excel, par exemple Expression_1 ≤ Expression_2.
• Pour une contrainte de type Expression_1 ≤ Expression_2, il faut prévoir une cellule pour chaque expression.
• Le succès dépend surtout de la bonne structuration du modèle Excel, pas uniquement du paramétrage du Solveur.

💡 Astuce mémo

3 étapes : contraintes → cellules → équations : si une étape manque, le Solveur échoue.

📖 6. Fonction RECHERCHEV : paramètres et correspondance

🔑 Notions clés & Définitions

• RECHERCHEV : Fonction Excel qui recherche une valeur dans la première colonne d’une plage et renvoie une valeur d’une autre colonne.
• Valeur recherchée : Valeur lue par Excel pour lancer la recherche dans la première colonne de la plage.
• Plage de recherche : Zone de cellules où Excel cherche la valeur recherchée dans la première colonne de cette plage.
• Numéro de colonne : Indice de la colonne dans la plage à partir de laquelle Excel renvoie le résultat.
• Type de correspondance : Paramètre qui choisit une correspondance exacte (FAUX) ou approximative (VRAI) pour la recherche.

📝 Points essentiels

• RECHERCHEV nécessite 3 (ou 4) paramètres : valeur recherchée, plage, numéro de colonne, type de correspondance.
• La recherche s’effectue uniquement dans la première colonne de la plage spécifiée.
• En correspondance approximative, la première colonne doit être triée par ordre croissant.
• En correspondance approximative, si la valeur n’est pas trouvée, RECHERCHEV renvoie la valeur inférieure la plus proche.
• En correspondance exacte (FAUX), si la valeur n’est pas trouvée, la fonction retourne #N/A.
• Exemple de syntaxe : RECHERCHEV(A1; C2:F6; 3; FAUX) pour une recherche exacte.

💡 Astuce mémo

RECHERCHEV = « première colonne → on renvoie une autre colonne ».

📖 7. Fonction RECHERCHEX : version améliorée

🔑 Notions clés & Définitions

• RECHERCHEX : Fonction Excel qui recherche une valeur et renvoie un résultat en permettant de choisir la colonne de recherche et la colonne de retour.
• Colonne de recherche : Colonne (ou plage) où Excel cherche la valeur de référence.
• Colonne résultat : Colonne (ou plage) dont Excel renvoie la valeur associée à la ligne trouvée.
• Valeur_proche : Paramètre qui indique si la correspondance doit être exacte (FAUX) ou approximative (VRAI) selon la fonction.

📝 Points essentiels

• RECHERCHEX cherche dans n’importe quelle colonne, ce qui la rend plus flexible que RECHERCHEV.
• RECHERCHEX est présentée comme plus facile car elle permet de définir directement les plages de recherche et de résultat.
• Exemple : trouver le prix des bananes avec la formule =RECHERCHEX(2;A2:A4;C2:C4).
• Dans l’exemple, A2:A4 correspond à la colonne de référence (référence) et C2:C4 à la colonne résultat (prix).
• Pour RECHERCHEH (mentionnée dans le cours comme version avec index), le paramètre FAUX signifie correspondance exacte.
• Exemple donné : RECHERCHEH(2;A1:C3;3;FAUX) pour chercher la valeur 2 dans la plage A1:C3 et renvoyer la ligne/colonne indiquée par le paramètre.

💡 Astuce mémo

RECHERCHEX = « je choisis où je cherche et où je renvoie ».

📖 8. Fonctions Texte : CONCAT, GAUCHE, DROITE et STXT

🔑 Notions clés & Définitions

• CONCAT : Fonction qui assemble plusieurs morceaux de texte en une seule chaîne.
• GAUCHE : Fonction qui extrait un nombre de caractères depuis le début (à gauche) d’un texte.
• DROITE : Fonction qui extrait un nombre de caractères depuis la fin (à droite) d’un texte.
• STXT : Fonction qui extrait une portion du texte à partir d’une position et sur une longueur donnée.
• NBCAR : Fonction qui compte le nombre de caractères d’une chaîne de texte.

📝 Points essentiels

• CONCAT (ou CONCATENER) sert à assembler plusieurs textes, par exemple =CONCAT(A1;" ";B1) pour ajouter un espace.
• GAUCHE extrait les caractères à gauche d’un texte, par exemple =GAUCHE(A1;3) pour prendre 3 caractères.
• DROITE extrait les caractères à droite d’un texte (fonction listée comme équivalente à GAUCHE mais côté fin).
• STXT extrait une partie au milieu du texte avec une position et une longueur, par exemple STXT(A1;2;3).
• NBCAR(A1) compte le nombre de caractères dans la cellule A1.
• Le cours liste aussi REMPLACER, SUBSTITUE et TEXTE comme fonctions texte, avec des exemples d’usage (remplacer, substituer, formater).

💡 Astuce mémo

GAUCHE = début, DROITE = fin, STXT = milieu (avec position + longueur).

📊 Tableaux de synthèse

Valeur cible vs Solveur

Outil	Variables modifiées	Contraintes
Valeur cible	Une seule cellule à modifier	Aucune contrainte explicitement mentionnée dans l’outil
Solveur	Plusieurs cellules en même temps	Respecte des contraintes définies par l’utilisateur

RECHERCHEV vs RECHERCHEX

Fonction	Colonne de recherche	Retour
RECHERCHEV	Toujours la première colonne de la plage	Colonne renvoyée via un numéro de colonne
RECHERCHEX	Colonne choisie via une plage	Valeur renvoyée via une plage résultat

⚠️ Pièges & confusions fréquents

1. Avec la valeur cible, la cellule cible doit contenir une formule, sinon l’outil ne peut pas fonctionner.
2. Avec RECHERCHEV en correspondance approximative, oublier de trier la première colonne par ordre croissant peut donner un résultat faux.
3. Confondre FAUX et VRAI : FAUX correspond à une recherche exacte (sinon #N/A si introuvable), tandis que VRAI autorise l’approximatif.
4. Penser que RECHERCHEV peut chercher dans n’importe quelle colonne : la recherche se fait uniquement dans la première colonne de la plage.
5. Confondre GAUCHE et STXT : GAUCHE prend depuis le début, alors que STXT nécessite une position et une longueur.

✅ Checklist Examen

1. Savoir définir la valeur cible et identifier ses 3 paramètres : cellule cible, valeur souhaitée, cellule à modifier.
2. Savoir expliquer le principe d’ajustement progressif de la valeur cible (ajouter/retrancher de petites valeurs) et l’idée d’un point de départ négatif.
3. Être capable de reproduire l’exemple de moyenne : cellule cible G2, valeur cible 15, cellule à modifier F2, et résultat attendu 19.5.
4. Savoir distinguer le Solveur de la valeur cible : plusieurs variables, objectif (max/min/égalité) et respect des contraintes.
5. Savoir lister les 3 étapes de modélisation du Solveur : recenser contraintes, concevoir la feuille, traduire en expressions Excel.
6. Maîtriser RECHERCHEV : paramètres, recherche dans la première colonne, tri requis en approximatif, comportement exact (FAUX) et approximatif (VRAI).
7. Savoir utiliser l’idée de RECHERCHEX : recherche dans une colonne choisie et renvoi via une colonne résultat, avec l’exemple =RECHERCHEX(2;A2:A4;C2:C4).
8. Connaître les fonctions texte demandées : CONCAT pour assembler, GAUCHE pour extraire à gauche, DROITE pour extraire à droite, STXT pour extraire une portion au milieu.