Modélisation de problèmes avec Excel

Le défi de la modélisation

Un bon chiffrier est d’abord un bon modèle du problème.

Excel n’est qu’un outil qui exécute ce que vous avez déjà pensé.

Approche réflexe

Ouvrir Excel immédiatement
Créer des colonnes au hasard
Essayer des formules jusqu’à ce que “ça marche”
Oublier comment ça fonctionne 2 semaines plus tard
Recommencer à zéro la prochaine fois…

Approche réfléchie

Comprendre le problème (papier/crayon)
Identifier les éléments et règles (schéma)
Concevoir le modèle (structure)
Implémenter dans Excel (outils)
Réutiliser et adapter facilement

Les trois types de données à reconnaître

Données saisies

Informations qui changent à chaque utilisation

Cliquez pour détails⌄

Données fixes

Règles, paramètres et constantes du système

Cliquez pour détails⌄

Données calculées

Résultats obtenus automatiquement par formules

Cliquez pour détails⌄

Quiz: Reconnaître les types de données

🎯Quiz #1: Classification des données

Dans un système de facturation, le "Prix unitaire" d'un article au catalogue est:

Pour un suivi de budget étudiant, la "Date de la dépense" est:

Dans un calcul de paie, le "Taux de cotisation RRQ" (6.15% en 2024) est:

Le "Pourcentage du budget utilisé" (dépenses ÷ budget × 100) est:

Dans un registre de ventes, le "Nom du client" est:

Démarche de modélisation en 3 phases

Phase 1: Penser le problème (HORS Excel!)

Définir l’objectif

Questions directrices:

À quoi servira ce chiffrier concrètement?
Quelle décision m’aidera-t-il à prendre?
Qui l’utilisera? (moi seul? mon équipe? un client?)
À quelle fréquence? (quotidien? mensuel? annuel?)

Exercice: Complétez cette phrase pour VOTRE problème:

“Ce chiffrier sert à [action] afin de [résultat souhaité].”

Exemples GCF:

Contexte	Objectif bien formulé
Budget personnel	”Ce chiffrier sert à suivre mes dépenses mensuelles par catégorie afin de respecter mon budget et épargner 300$/mois.”
Petite entreprise	”Ce chiffrier sert à calculer automatiquement les factures clients avec TPS/TVQ afin de réduire les erreurs et gagner du temps.”
Projet de groupe	”Ce chiffrier sert à répartir équitablement les dépenses communes afin de savoir qui doit combien à qui.”

Cartographier les éléments

Activité de réflexion (10 min, sur papier):

Prenez votre problème et listez tous les éléments réels qui doivent être représentés.

Méthode recommandée:

Posez-vous: “De quoi parle ce problème?” (objets, personnes, actions)
Pour chaque élément, listez ses attributs (caractéristiques)
Identifiez les relations entre éléments

Exemple: Suivi budgétaire étudiant

ÉLÉMENTS PRINCIPAUX:

1. Transaction de dépense
   Attributs:
   - Date (quand?)
   - Montant (combien?)
   - Catégorie (quel type: alimentation, transport, etc.)
   - Mode de paiement (comptant, débit, crédit?)
   - Description (quoi précisément?)
   - Essentielle ou non? (nécessité)
   - Reçu disponible? (justificatif)

2. Budget mensuel
   Attributs:
   - Revenus prévus (total)
   - Limites par catégorie
   - Mois concerné
   - Notes/objectifs

3. Catégorie de dépense
   Attributs:
   - Nom
   - Limite budgétaire
   - Priorité (essentielle/variable)
   - Couleur pour graphiques

RELATIONS:
- Chaque transaction appartient à UNE catégorie
- Chaque catégorie a PLUSIEURS transactions
- Un budget s'applique à UN mois

Formuler les règles

Principe clé: Avant toute formule Excel, écrivez les règles en français simple.

Format recommandé: “Si [condition], alors [action], sinon [action alternative]”

Exemples pour le budget étudiant:
1. Règle d’alerte:
  “Si une dépense individuelle dépasse 10% du budget mensuel total, afficher une alerte ‘GROSSE DÉPENSE’”
2. Règle de statut:
  “Si le total dépensé dans une catégorie dépasse sa limite, statut = ‘DÉPASSEMENT’, sinon si > 80% de la limite, statut = ‘ATTENTION’, sinon statut = ‘OK’”
3. Règle de calcul:
  “Budget restant = Revenus mensuels - Total de toutes les dépenses du mois”
4. Règle de projection:
  “Budget quotidien suggéré = Budget restant ÷ Nombre de jours restants dans le mois”
5. Règle de validation:
  “Une transaction ne peut pas avoir un montant négatif ou nul”
Donnez un numéro à chaque règle métier. Quand vous créerez vos formules Excel, vous pourrez ajouter des commentaires comme “Règle #3” pour tracer la logique. Précieux pour la maintenance!

Phase 2: Concevoir le modèle

Organisation en feuilles: le principe “Une feuille = Un rôle”

📊

Feuille Opérationnelle

Données détaillées, une ligne par élément

Cliquez pour détails⌄

⚙️

Feuille Paramètres

Règles, seuils, constantes du système

Cliquez pour détails⌄

📈

Feuille Tableau de Bord

Résumés, indicateurs, visualisations

Cliquez pour détails⌄

Quiz: Où placer cette information?

🎯Quiz #2: Organisation des feuilles

Vous créez un chiffrier de facturation. Où placeriez-vous "la liste complète de toutes les factures émises cette année"?

Pour le même chiffrier, où placeriez-vous "le taux de TPS actuel (5%)"?

Toujours pour la facturation, où placeriez-vous "le total des ventes du trimestre avec un graphique d'évolution mensuelle"?

Pour un suivi de stock, où placeriez-vous "le seuil minimum avant réapprovisionnement" (ex: 10 unités)?

Phase 3: De la règle à la formule

Correspondance règles métier ↔ fonctions Excel

Règle métier:
“Si [condition], alors [valeur A], sinon [valeur B]”

Formule Excel:
=SI(condition; valeurA; valeurB)

Exemples GCF:

Règle en français	Formule Excel
”Si le solde est négatif, afficher ‘DÉFICIT’, sinon ‘OK‘“	`=SI(C2<0; "DÉFICIT"; "OK")`
”Si la note ≥ 60, afficher ‘Réussi’, sinon ‘Échoué‘“	`=SI(D2>=60; "Réussi"; "Échoué")`
”Si dépensé > budget, taxe de pénalité de 50$, sinon 0$“	`=SI(E2>F2; 50; 0)`

Règle métier:
“Compter combien d’éléments remplissent [condition]”

Formule Excel:
=NB.SI(plage; critère)

Exemples GCF:

Règle en français	Formule Excel
”Compter combien de factures sont ‘Payées‘“	`=NB.SI(E:E; "Payée")`
”Compter combien de ventes ont dépassé 1000$“	`=NB.SI(C:C; ">1000")`
”Nombre de dépenses dans la catégorie ‘Transport‘“	`=NB.SI(D:D; "Transport")`

Règle métier:
“Additionner les montants qui remplissent [condition]”

Formule Excel:
=SOMME.SI(plage_critère; critère; plage_somme)

Exemples GCF:

Règle en français	Formule Excel
”Total des ventes du vendeur ‘Marie‘“	`=SOMME.SI(A:A; "Marie"; C:C)`
”Total dépensé en ‘Alimentation‘“	`=SOMME.SI(D:D; "Alimentation"; C:C)`
”Somme des factures impayées”	`=SOMME.SI(E:E; "Impayée"; B:B)`

Exercice guidé: Budget mensuel étudiant

Mise en situation

Profil: Léa, étudiante en Gestion, 20 ans
Revenus mensuels: 1500$ (prêts/bourses + emploi à temps partiel)
Problème: Dépense souvent plus que prévu et finit le mois “à sec”

Besoin: Un chiffrier pour:

Suivre ses dépenses quotidiennes
S’assurer de ne pas dépasser son budget global
Identifier les catégories où elle dépense trop
Savoir combien il lui reste pour la fin du mois

Objectif

Votre tâche: Formuler l’objectif en une phrase claire.

Format: “Ce chiffrier sert à [action] afin de [résultat].”
💡 Cliquez pour voir la solution
Objectif formulé:

“Ce chiffrier sert à suivre en temps réel le budget mensuel de Léa afin de prévenir les dépassements et ajuster ses habitudes de dépenses avant la fin du mois.”

Détails complémentaires:
- Utilisateur: Léa elle-même
- Fréquence d’utilisation: Mise à jour après chaque dépense (ou au moins 2-3x par semaine)
- Décision supportée: “Puis-je me permettre cette dépense maintenant?”
- Succès mesurable: Finir le mois avec au moins 50$ restants

Cartographie

Votre tâche: Sur papier, listez tous les éléments à modéliser avec leurs attributs.

Indices:

Pensez aux différentes dépenses de Léa
Pensez à son budget global
Pensez aux catégories de dépenses

💡 Cliquez pour voir la solution

Éléments identifiés:

1. DÉPENSE (élément répétitif)
   - Date de la dépense (ex: 2024-10-15)
   - Montant (ex: 42.50$)
   - Catégorie (ex: Alimentation, Transport, Loisirs...)
   - Description (ex: Épicerie IGA)
   - Essentielle? (Oui/Non - pour analyse)

2. BUDGET MENSUEL (configuration)
   - Mois concerné (ex: Octobre 2024)
   - Revenu total (1500$)
   - Date de réception du revenu (ex: 1er du mois)

3. CATÉGORIE DE DÉPENSE (paramètre)
   - Nom de la catégorie
   - Budget alloué pour cette catégorie
   - Priorité (Essentielle/Variable)

RELATIONS:
- Chaque dépense appartient à UNE catégorie
- Le total des budgets alloués par catégorie = revenu mensuel
- Plusieurs dépenses par catégorie

Classification

Votre tâche: Pour chaque attribut identifié, déterminez s’il est Saisie, Fixe, ou Calculé.
💡 Cliquez pour voir la solution
Classification des données:
DONNÉES SAISIES (changent à chaque dépense):
- Date de la dépense
- Montant dépensé
- Catégorie sélectionnée
- Description
- Essentielle (Oui/Non)
DONNÉES FIXES (paramètres du modèle):
- Revenu mensuel: 1500$
- Budgets par catégorie:
  - Logement: 800$
  - Alimentation: 300$
  - Transport: 150$
  - Loisirs: 100$
  - Autres: 150$
- Date de début du mois
DONNÉES CALCULÉES (formules):
- Total dépensé (global)
- Total dépensé par catégorie
- Budget restant (global)
- Budget restant par catégorie
- Pourcentage utilisé (global et par catégorie)
- Statut (OK / Attention / Dépassement)
- Nombre de jours écoulés dans le mois
- Nombre de jours restants
- Dépense moyenne quotidienne (actuelle)
- Budget quotidien recommandé pour finir le mois
Règles métier

Votre tâche: Formulez au moins 5 règles métier en français simple.

Format suggéré: “Si [condition], alors [action]”

💡 Cliquez pour voir la solution
Règles métier formulées:
Règle #1 - Budget restant global:
“Le budget restant = Revenu mensuel − Total de toutes les dépenses du mois”
Règle #2 - Pourcentage utilisé:
“Le pourcentage utilisé = (Total dépensé ÷ Budget alloué) × 100”
Règle #3 - Statut par catégorie:
“Si pourcentage > 100%, statut = ‘DÉPASSEMENT’ (rouge)
Sinon si pourcentage > 80%, statut = ‘ATTENTION’ (orange)
Sinon, statut = ‘OK’ (vert)”
Règle #4 - Total par catégorie:
“Total dépensé dans une catégorie = Somme de toutes les dépenses ayant cette catégorie”
Règle #5 - Alerte grosse dépense:
“Si une dépense individuelle > 10% du revenu mensuel (150$), afficher ‘GROSSE DÉPENSE’”

Structure

Votre tâche: Concevez l’organisation en feuilles et esquissez la structure de chacune.

💡 Cliquez pour voir la solution

Organisation en 3 feuilles:

Feuille “Tableau de bord”

Rôle: Vue synthétique, aucune saisie
Contenu:

BUDGET MENSUEL - OCTOBRE 2024

Revenu: 1 500,00 $
Dépensé: 1 247,85 $
Restant: 252,15 $
% utilisé: 83,2%

DÉTAIL PAR CATÉGORIE:

Catégorie	Budget	Dépensé	Restant	%	Statut
Logement	800	800	0	100%	OK
Alimentation	300	287,35	12,65	96%	ATTENTION
Transport	150	85,50	64,50	57%	OK
Loisirs	100	75,00	25,00	75%	OK
Autres	150	0	150,00	0%	OK

Feuille “Transactions”

Rôle: Saisie détaillée
Contenu:

Date	Catégorie	Montant	Description	Alerte
2024-10-01	Logement	800,00	Loyer octobre	Oui
2024-10-03	Alimentation	87,35	Épicerie IGA	Oui
2024-10-05	Transport	55,50	Passe mensuelle	Oui
2024-10-08	Loisirs	45,00	Cinéma + restau	Non
2024-10-12	Alimentation	125,00	Épicerie Costco	Oui
…	…	…	…	…

Feuille “Paramètres”

Rôle: Configuration du système
Contenu:

Revenu mensuel: 1 500,00 $
Seuil “grosse dépense”: 150 $ (10% du revenu)
Objectif épargne: 50 $ minimum

Formules clés

Maintenant seulement, vous pouvez penser aux formules Excel.
💡 Cliquez pour voir les formules principales
Formules à implémenter:
Dans la feuille “Transactions”
Section intitulée « Dans la feuille “Transactions” »
Colonne H - Alerte grosse dépense (Règle #5):
```
=SI(C2>Paramètres!$B$10; "GROSSE DÉPENSE"; "")
```
Dans la feuille “Tableau de bord”
Section intitulée « Dans la feuille “Tableau de bord” »
Total dépensé (Règle #1):
```
=SOMME(Transactions!C:C)
```
Budget restant:
```
=Paramètres!$B$3 - B5
```
(où B5 contient le total dépensé)
Pourcentage utilisé (Règle #2):
```
=B5/Paramètres!$B$3*100
```
Total par catégorie (Règle #4) - ex: Alimentation:
```
=SOMME.SI(Transactions!$B:$B; "Alimentation"; Transactions!$C:$C)
```
Statut par catégorie (Règle #3):
```
=SI(E2>100%; "DÉPASSEMENT";
    SI(E2>80%; "ATTENTION"; "OK"))
```
(où E2 contient le % utilisé pour cette catégorie)
Jours restants dans le mois:
```
=FIN.MOIS(Paramètres!$B$2; 0) - AUJOURDHUI()
```
Budget quotidien suggéré (Règle #6):
```
=B6/B12
```
(où B6 = budget restant, B12 = jours restants)

Les 5 pièges les plus fréquents

Symptôme: Ouvrir Excel avant de comprendre le problème.

Conséquences:

Structure confuse
Formules incompréhensibles
Difficulté à modifier ou étendre
Temps perdu en essais/erreurs

Solution: Garder Excel fermé pendant au moins 15 minutes. Utiliser papier/crayon pour:

Définir l’objectif
Cartographier les éléments
Classifier les données
Formuler les règles

Signe que vous êtes prêt: Vous pouvez expliquer votre modèle à quelqu’un sans ordinateur.

Symptôme: Mettre les valeurs fixes directement dans les formules.

Exemple problématique:

=C2 * 0.05      // Taux de TPS caché!
=D2 * 1.09975   // Taux de TVQ caché!

Problème: Si le taux change, vous devez modifier TOUTES les formules.

Solution: Cellules nommées et feuille Paramètres

=C2 * TauxTPS
=D2 * (1 + TauxTVQ)

Où TauxTPS et TauxTVQ sont définis visiblement dans la feuille Paramètres.

Symptôme: Une formule de 3+ lignes avec multiples SI imbriqués.

Exemple:

=SI(ET(A2>1000;B2="Québec";C2<30);A2*0.15;
SI(A2>500;SI(B2="Québec";A2*0.10;A2*0.08);
SI(A2>100;A2*0.05;0)))

Problèmes:

Impossible à lire
Impossible à déboguer
Impossible à maintenir

Solution: Décomposer en colonnes auxiliaires

Col D: =A2>1000           // Vente importante?
Col E: =B2="Québec"       // Client QC?
Col F: =C2<30             // Jeune entreprise?
Col G: =ET(D2;E2;F2)      // Cas spécial?
Col H: [formule simple basée sur les résultats ci-dessus]

Quiz final: Audit d’un chiffrier

🎯Quiz #3: Reconnaître et corriger les erreurs

Vous recevez un chiffrier d'un collègue. La formule =C2*0.14975 apparaît dans 200 cellules. Quel est le problème principal?

Un étudiant vous montre sa formule: =SI(A2>100;SI(B2="Oui";SI(C2<50;"Type1";SI(C2<100;"Type2";"Type3"));"Invalide");"Trop petit"). Que lui conseillez-vous?

Votre chiffrier de budget personnel a une seule feuille qui contient: vos transactions, vos objectifs budgétaires, des totaux et des graphiques, tout mélangé. Quelle est la meilleure amélioration?

Vous créez un chiffrier de facturation. Comment devriez-vous gérer la liste des clients?

Synthèse: La démarche complète

Phase 1: Penser (hors Excel)

Définir l’objectif en une phrase
Cartographier tous les éléments
Classifier: Saisie / Fixe / Calculé
Formuler les règles en français

Phase 2: Concevoir

Organiser en feuilles logiques
Esquisser la structure de chaque feuille
Identifier les relations entre données
Prévoir la validation des saisies

Phase 3: Implémenter

Créer la structure dans Excel
Traduire les règles en formules
Tester méthodiquement
Documenter pour le futur

Modélisation de problèmes avec Excel

Le défi de la modélisation

Les trois types de données à reconnaître

Données saisies

Données fixes

Données calculées

Quiz: Reconnaître les types de données

🎯Quiz #1: Classification des données

Démarche de modélisation en 3 phases

Phase 1: Penser le problème (HORS Excel!)

Phase 2: Concevoir le modèle

Organisation en feuilles: le principe “Une feuille = Un rôle”

Feuille Opérationnelle

Feuille Paramètres

Feuille Tableau de Bord

Quiz: Où placer cette information?

🎯Quiz #2: Organisation des feuilles

Phase 3: De la règle à la formule

Correspondance règles métier ↔ fonctions Excel

Exercice guidé: Budget mensuel étudiant

Mise en situation

Feuille “Tableau de bord”

BUDGET MENSUEL - OCTOBRE 2024

Feuille “Transactions”

Feuille “Paramètres”

Dans la feuille “Transactions”

Dans la feuille “Tableau de bord”

Les 5 pièges les plus fréquents

Quiz final: Audit d’un chiffrier

🎯Quiz #3: Reconnaître et corriger les erreurs

Synthèse: La démarche complète

Message final: Au-delà d’Excel