Spreadsheet Formulas Cheat Sheet: The 20 You Actually Need — csv-x.com

Je me souviens encore du moment où j'ai réalisé que je faisais des feuilles de calcul de manière incorrecte depuis sept ans. C'était en 2019, et j'étais assise dans une salle de conférence avec mon équipe dans une entreprise de logistique de taille moyenne, regardant notre CFO copier-coller manuellement des données entre trois fichiers Excel différents pour générer notre rapport trimestriel. Le processus lui a pris quatre heures. Quatre heures de son temps à 150 $ de l'heure, chaque trimestre, parce que personne ne s'était donné la peine d'apprendre VLOOKUP.

Cette journée a tout changé pour moi. Je suis Sarah Chen, et j'ai passé les 11 dernières années en tant que consultante en opérations financières, travaillant avec plus de 200 entreprises pour rationaliser leurs flux de données. J'ai vu des entreprises perdre littéralement des milliers d'heures sur des tâches de feuilles de calcul qui pourraient être automatisées avec juste une poignée de formules. L'ironie ? La plupart des gens pensent qu'ils doivent maîtriser des centaines de fonctions pour être "bons en Excel". Ils n'en ont pas besoin. D'après mon expérience, 20 formules gèrent environ 95 % des scénarios commerciaux du monde réel.

Ceci n'est pas une encyclopédie complète de chaque fonction de feuille de calcul jamais créée. C'est l'arsenal éprouvé que j'ai utilisé pour faire économiser à mes clients plus de 12 000 heures de travail manuel. Ce sont les formules qui comptent vraiment lorsque vous essayez de clôturer les comptes, d'analyser les données de vente ou de comprendre pourquoi vos chiffres d'inventaire ne correspondent pas.

La Fondation : L'Arithmétique de Base Qui Alimente Tout

Avant de nous lancer dans des choses compliquées, parlons des trois formules qui forment la colonne vertébrale de littéralement chaque modèle financier que j'ai jamais construit. Elles peuvent sembler évidentes, mais j'ai vu des analystes seniors les gâcher de manière à coûter de l'argent aux entreprises.

SUM est le cheval de travail. La syntaxe est très simple : =SUM(A1:A10) additionne tout ce qui se trouve dans cette plage. Mais voici ce que la plupart des gens ne savent pas : vous pouvez utiliser SUM avec plusieurs plages non contiguës. =SUM(A1:A10, C1:C10, E5) fonctionne parfaitement et est beaucoup plus clair que d’écrire les références de cellules individuelles. Une fois, j'ai audité un modèle financier où quelqu'un avait écrit =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 au lieu d'utiliser SUM. Quand ils ont eu besoin d'insérer une ligne, la formule s'est cassée. Ne soyez pas cette personne.

AVERAGE est tout aussi simple mais étonnamment puissant. =AVERAGE(B2:B50) vous donne la moyenne de votre ensemble de données. L'idée clé ici est de comprendre quand utiliser AVERAGE par rapport à AVERAGEIF (que nous couvrirons plus tard). Dans mon travail de consultante, j'ai constaté qu'environ 60 % du temps où les gens utilisent AVERAGE, ils ont en fait besoin d'une moyenne conditionnelle. Si vous calculez les ventes moyennes mais que vous souhaitez exclure les zéros ou les valeurs aberrantes, une simple AVERAGE vous induira en erreur.

COUNT et COUNTA sont des jumeaux avec des personnalités différentes. COUNT ne compte que les cellules contenant des nombres, tandis que COUNTA compte toute cellule non vide. Cette distinction compte plus que vous ne le pensez. Lorsque j'audite la qualité des données, j'utilise =COUNTA(A:A)-COUNT(A:A) pour trouver instantanément combien de cellules dans la colonne A contiennent du texte au lieu de chiffres. Si ce nombre est plus élevé que prévu, je sais qu'il y a un problème de saisie de données. Ce simple contrôle a permis de détecter des milliers de dollars d'erreurs comptables pour mes clients.

Fonctions de Recherche : Le s

Si je pouvais enseigner à quelqu'un trois formules, deux d'entre elles seraient des fonctions de recherche. C'est la différence entre passer 30 minutes à faire correspondre des données manuellement et passer 30 secondes à écrire une formule qui le fait instantanément.

"D'après mon expérience de travail avec plus de 200 entreprises, 20 formules gèrent environ 95 % des scénarios commerciaux du monde réel. La plupart des gens perdent du temps à essayer de maîtriser des centaines de fonctions qu'ils n'utiliseront jamais."

VLOOKUP est le classique, et malgré des alternatives plus récentes, il est encore incroyablement utile. La syntaxe est =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Voici un exemple réel du mois dernier : j'avais un client avec 5 000 enregistrements clients dans une feuille et 5 000 enregistrements de transactions dans une autre. Ils avaient besoin de faire correspondre les noms des clients aux ID de transactions. =VLOOKUP(A2, Customers!A:D, 3, FALSE) a fait en trois secondes ce qui aurait pris des heures manuellement.

La chose critique que tout le monde se trompe : ce quatrième argument. FALSE signifie correspondance exacte, TRUE signifie correspondance approximative. Pour 99 % des cas d'utilisation commerciale, vous voulez FALSE. J'ai vu des rapports financiers entiers corrompus parce que quelqu'un l'a laissé sur TRUE (ou l'a omis, ce qui le fait par défaut à TRUE) et a obtenu des correspondances approximatives au lieu de correspondances exactes.

XLOOKUP est le remplacement moderne, et honnêtement, il est meilleur à presque tous les égards. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) semble compliqué, mais en pratique, c'est plus clair. La fonction killer ? Elle peut rechercher à gauche, ce que VLOOKUP ne peut pas faire. Récemment, j'ai migré l'ensemble du système de reporting d'un client de VLOOKUP à XLOOKUP et réduit leurs erreurs de formule d'environ 40 % parce que XLOOKUP gère les données manquantes de manière plus élégante.

INDEX-MATCH est le choix des utilisateurs avancés. C'est en fait deux formules combinées : =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). Cette combinaison est plus flexible que VLOOKUP et plus rapide avec de grands ensembles de données. Lorsque je travaille avec des feuilles de calcul de plus de 50 000 lignes, INDEX-MATCH fonctionne de manière nettement meilleure. La courbe d'apprentissage est plus raide, mais une fois que vous l'avez, vous ne revenir jamais en arrière. Je l'utilise pour des modèles financiers complexes où je dois rechercher des valeurs en fonction de deux critères simultanément.

Logique Conditionnelle : Faire Penser Vos Feuilles de Calcul

C'est ici que les feuilles de calcul se transforment de calculatrices en outils de prise de décision. Les formules conditionnelles vous permettent d'intégrer de la logique dans vos données, et elles sont absolument essentielles pour tout type de reporting automatisé.

IF est la drogue d'entrée pour l'automatisation des feuilles de calcul. =IF(logical_test, value_if_true, value_if_false) vous permet de créer une logique de ramification. Un exemple simple : =IF(B2>1000, "Valeur Élevée", "Standard") catégorise les clients en fonction du montant d'achat. Mais la vraie puissance vient du nesting des IF. J'ai construit des calculateurs de commission avec cinq instructions IF imbriquées qui déterminent automatiquement les bonus des représentants commerciaux en fonction de plusieurs critères. La clé est de les garder lisibles : utilisez une indentation appropriée et des commentaires.

IFS est l'alternative plus claire aux IF imbriqués, disponible dans les versions plus récentes des feuilles de calcul. Au lieu de =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F"))), vous écrivez =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F"). C'est plus lisible et moins sujet à des erreurs de parenthèses. Je suis passé à IFS il y a environ trois ans et mon temps de débogage des formules a été réduit de moitié.

SUMIF et SUMIFS sont des puissances de sommation conditionnelle. =SUMIF(range, criteria, [sum_range]) additionne les valeurs qui répondent à une condition. =SUMIFS étend cela à plusieurs conditions. Exemple réel : j'avais un client de vente au détail qui devait calculer les ventes totales par région et catégorie de produit. =SUMIFS(Sales!C:C, Sales!A:A, "Ouest", Sales!B:B, "Électronique") leur a donné exactement cela. Avant d'apprendre SUMIFS, ils utilisaient des tableaux croisés dynamiques pour tout, ce qui était excessif et plus lent à mettre à jour.

COUNTIF et COUNTIFS fonctionnent de la même manière mais comptent au lieu de sommer. Je les utilise constamment pour des vérifications de qualité des données. =COUNTIF(A:A, "Erreur") me dit combien de cellules dans la colonne A contiennent le mot "Erreur". =COUNTIFS(A:A, ">100", B:B, "<50") compte les lignes où la colonne A est supérieure à 100 ET la colonne B est inférieure à 50. Ces formules m'ont fait gagner d'innombrables heures de validation manuelle des données.