25 Astuces Excel pour les Formules

Problème : Comprendre les dépendances et identifier les erreurs dans les formules complexes.

Solution :

• Formules > Audit de formules > Repérer les antécédents
• Repérer les dépendants pour voir quelles cellules utilisent cette formule
• Évaluer la formule pour voir le calcul étape par étape

Raccourcis : Ctrl+[ pour aller aux antécédents, Ctrl+] pour les dépendants.

Problème : Assembler du texte et des nombres dans une même cellule.

Solutions :

• Fonction CONCATENER : =CONCATENER(A1;" ";B1)
• Opérateur & : =A1&" "&B1
• Fonction CONCAT : =CONCAT(A1:C1) (Excel 2016+)
• Fonction JOINDRE.TEXTE : =JOINDRE.TEXTE(" ";VRAI;A1:C1)

Astuce : Utilisez JOINDRE.TEXTE pour ignorer les cellules vides automatiquement.

Problème : Reproduire des formules en adaptant automatiquement les références.

Techniques :

• Recopie simple : Glissez la poignée de recopie
• Recopie rapide : Double-cliquez sur la poignée pour remplir jusqu'à la fin des données
• Collage spécial : Copiez puis Collage spécial > Formules

Astuce : Utilisez Ctrl+D pour remplir vers le bas, Ctrl+R pour remplir vers la droite.

Problème : Effectuer des calculs avec les dates et heures.

Fonctions essentielles :

• AUJOURD'HUI() : Date actuelle
• MAINTENANT() : Date et heure actuelles
• DATEDIF(date1;date2;"unité") : Différence entre dates
• JOUR.SEMAINE(date) : Numéro du jour de la semaine
• FIN.MOIS(date;mois) : Dernier jour du mois

Exemple : =DATEDIF(A1;AUJOURD'HUI();"Y") pour calculer l'âge en années.

Problème : Éviter l'affichage d'erreurs disgracieuses dans les calculs.

Solutions :

• SIERREUR : =SIERREUR(A1/B1;"Division impossible")
• SINONDISP : =SINONDISP(RECHERCHEV(...);"Non trouvé")
• ESTNA : =SI(ESTNA(RECHERCHEV(...));"";RECHERCHEV(...))

Types d'erreurs : #DIV/0!, #N/A, #VALEUR!, #REF!, #NOM?, #NUM!, #NULL!

Problème : Créer des conditions logiques complexes.

Syntaxe : =SI(condition;valeur_si_vrai;valeur_si_faux)

Variantes avancées :

• SI imbriqués : =SI(A1>90;"A";SI(A1>80;"B";"C"))
• SI.CONDITIONS : =SI.CONDITIONS(A1>90;"A";A1>80;"B";VRAI;"C")
• SI avec ET/OU : =SI(ET(A1>0;B1<100);"OK";"KO")

Astuce : Utilisez SI.CONDITIONS pour éviter les imbrications complexes.

Problème : Effectuer des calculs sur des plages entières en une seule formule.

Création : Tapez la formule puis appuyez sur Ctrl+Shift+Entrée

Exemples :

• Somme de produits : {=SOMME(A1:A10*B1:B10)}
• Compter avec critères multiples : {=SOMME((A1:A10="X")*(B1:B10>100))}
• Maximum conditionnel : {=MAX(SI(A1:A10="Ventes";B1:B10))}

Note : Excel 365 gère automatiquement les formules matricielles dynamiques.

Problème : Alternative plus flexible à RECHERCHEV pour les recherches bidirectionnelles.

Syntaxe : =INDEX(plage_retour;EQUIV(valeur_cherchée;plage_recherche;0))

Avantages :

• Recherche vers la gauche possible
• Plus rapide que RECHERCHEV
• Moins sensible aux modifications de structure

Exemple : =INDEX(C:C;EQUIV(E1;A:A;0)) pour chercher E1 dans A:A et retourner la valeur correspondante de C:C.

Problème : Créer des conditions complexes avec plusieurs critères.

Fonctions clés :

• ET : =ET(A1>0;B1<100;C1="OK")
• OU : =OU(A1="Oui";B1="Oui")
• NON : =NON(A1="")
• XOU : =XOU(A1>0;B1>0) (l'un ou l'autre, pas les deux)

Combinaisons : =SI(ET(A1>0;OU(B1="X";B1="Y"));"Valide";"Invalide")

Problème : Effectuer des calculs mathématiques complexes.

Fonctions utiles :

• ARRONDI : =ARRONDI(A1;2) (2 décimales)
• ENT : =ENT(A1) (partie entière)
• MOD : =MOD(A1;2) (reste de la division)
• PUISSANCE : =PUISSANCE(A1;2) (A1 au carré)
• RACINE : =RACINE(A1)
• ABS : =ABS(A1) (valeur absolue)

Exemple pratique : =SI(MOD(LIGNE();2)=0;"Pair";"Impair") pour identifier les lignes paires.

Problème : Rendre les formules plus lisibles et maintenables.

Création de noms :

• Sélectionnez la plage
• Formules > Définir un nom
• Donnez un nom explicite (ex: "Chiffre_Affaires")

Utilisation : =SOMME(Chiffre_Affaires) au lieu de =SOMME(B2:B100)

Avantages : Formules auto-documentées, maintenance facilitée, moins d'erreurs.

Problème : Accélérer les calculs dans les gros fichiers.

Techniques d'optimisation :

• Éviter les références entières : A1:A1000 plutôt que A:A
• Utiliser INDEX/EQUIV : Plus rapide que RECHERCHEV
• Limiter les formules volatiles : MAINTENANT(), ALEA(), INDIRECT()
• Calcul manuel : Formules > Options de calcul > Manuel

Astuce : Remplacez les formules par leurs valeurs quand les données sont finales.

Problème : Effectuer différents types de calculs de pourcentages.

Formules courantes :

• Pourcentage simple : =A1/B1 (format en %)
• Variation : =(B1-A1)/A1
• Pourcentage du total : =A1/SOMME($A$1:$A$10)
• Pourcentage cumulé : =SOMME($A$1:A1)/SOMME($A$1:$A$10)

Astuce : Utilisez les références absolues ($) pour fixer les totaux dans les calculs de pourcentages.

Problème : Rechercher des valeurs dans des tableaux de données.

Syntaxe : =RECHERCHEV(valeur;tableau;colonne;[correspondance])

Types de correspondance :

• FAUX ou 0 : Correspondance exacte
• VRAI ou 1 : Correspondance approximative (tableau trié)

Gestion d'erreurs : =SIERREUR(RECHERCHEV(...);"Non trouvé")

Alternative moderne : Fonction RECHERCHEX (Excel 365)

Problème : Contrôler le comportement des références lors de la copie de formules.

Types de références :

• Relative : A1 (s'adapte lors de la copie)
• Absolue : $A$1 (reste fixe)
• Mixte : $A1 (colonne fixe) ou A$1 (ligne fixe)

Raccourci : F4 pour basculer entre les types de références

Exemple : =A1*$B$1 pour multiplier chaque valeur par une constante.

Problème : Additionner selon des critères complexes.

Fonctions :

• SOMME.SI : =SOMME.SI(A:A;"Ventes";B:B)
• SOMME.SI.ENS : =SOMME.SI.ENS(C:C;A:A;"Ventes";B:B;">100")
• SOMMEPROD : =SOMMEPROD((A:A="Ventes")*(B:B>100)*C:C)

Critères avec caractères génériques : "*Vente*", "A?", ">=100"

Astuce : SOMMEPROD est plus flexible pour les critères complexes.

Problème : Analyser des données avec des statistiques descriptives.

Fonctions de base :

• MOYENNE : =MOYENNE(A1:A10)
• MEDIANE : =MEDIANE(A1:A10)
• MODE : =MODE.SN(A1:A10)
• ECARTYPE : =ECARTYPE.STANDARD(A1:A10)
• CENTILE : =CENTILE.INC(A1:A10;0,9)

Fonctions conditionnelles : MOYENNE.SI, NB.SI, MAX.SI.ENS

Problème : Traiter et transformer des données textuelles.

Fonctions essentielles :

• GAUCHE/DROITE/STXT : Extraire des parties de texte
• NBCAR : =NBCAR(A1) (nombre de caractères)
• CHERCHE/TROUVE : Position d'un texte dans un autre
• SUBSTITUE : =SUBSTITUE(A1;"ancien";"nouveau")
• MAJUSCULE/MINUSCULE/NOMPROPRE : Changer la casse

Exemple : =NOMPROPRE(SUBSTITUE(A1;"_";" ")) pour nettoyer des noms.

Problème : Convertir des lignes en colonnes et vice versa dynamiquement.

Syntaxe : =TRANSPOSE(plage)

Utilisation :

• Sélectionnez une plage de destination (inversée)
• Tapez =TRANSPOSE(A1:C5)
• Validez avec Ctrl+Shift+Entrée (versions anciennes)

Avantage : La transposition se met à jour automatiquement si les données sources changent.

Problème : S'assurer que les formules fonctionnent correctement.

Techniques de validation :

• Évaluer la formule : Formules > Évaluer la formule
• Afficher les formules : Ctrl+` pour voir toutes les formules
• Vérification d'erreurs : Formules > Vérification des erreurs
• Mode pas à pas : F9 pour évaluer une partie de formule

Bonnes pratiques : Testez avec des valeurs connues, documentez les formules complexes.

Problème : Créer des formules qui s'adaptent automatiquement aux données.

Nouvelles fonctions :

• FILTRE : =FILTRE(A:C;B:B>100)
• TRIER : =TRIER(A:C;2;-1) (tri par colonne 2, décroissant)
• UNIQUE : =UNIQUE(A:A)
• SEQUENCE : =SEQUENCE(10;1;1;1) (1 à 10)

Avantage : Ces formules retournent des plages dynamiques qui se redimensionnent automatiquement.

Problème : Effectuer des calculs financiers complexes.

Fonctions clés :

• VPM : =VPM(taux;npm;va) (paiement mensuel)
• VA : =VA(taux;npm;vpm) (valeur actuelle)
• VF : =VF(taux;npm;vpm;va) (valeur future)
• TRI : =TRI(flux) (taux de rentabilité interne)
• VAN : =VAN(taux;flux) (valeur actuelle nette)

Exemple : =VPM(5%/12;60;-20000) pour un prêt de 20000€ sur 5 ans à 5%.

Problème : Créer des formules avec plusieurs niveaux de fonctions.

Techniques :

• Planification : Décomposez la logique étape par étape
• Test progressif : Construisez la formule par parties
• Parenthèses : Utilisez-les pour clarifier l'ordre des opérations

Exemple complexe :

Astuce : Utilisez Alt+Entrée pour créer des retours à la ligne dans les formules longues.

Problème : Créer des règles de mise en forme basées sur des formules personnalisées.

Création :

• Sélectionnez la plage
• Accueil > Mise en forme conditionnelle > Nouvelle règle
• Choisissez "Utiliser une formule pour déterminer..."

Exemples de formules :

• =MOD(LIGNE();2)=0 : Lignes paires
• =A1>MOYENNE($A$1:$A$100) : Supérieur à la moyenne
• =JOURSEM(A1;2)>5 : Week-ends

Problème : Créer des fonctions sur mesure pour des besoins spécifiques.

Méthode :

• Alt+F11 pour ouvrir l'éditeur VBA
• Insérer > Module
• Créer une fonction avec le mot-clé Function

Exemple simple :

Function DoubleValeur(nombre As Double) As Double
    DoubleValeur = nombre * 2
End Function

Utilisation : =DoubleValeur(A1) dans une cellule Excel.