The 10 Spreadsheet Formulas That Handle 90% of Real Work \u2014 CSV-X.com

Je me souviens encore du jour où j'ai réalisé que j'avais fait des tableurs de travers pendant sept ans. C'était en 2019, et j'étais assis dans une salle de conférence exiguë dans une entreprise de logistique de taille moyenne à Cleveland, regardant leur responsable des opérations copier-coller manuellement des données entre trois fichiers Excel différents pour ce qui devait être la quarantième fois ce mois-ci. Chaque opération de collage prenait environ 90 secondes. Il faisait cela environ 200 fois par mois. Cela représente 300 heures par an — près de deux mois de travail complets — consacrées à une tâche qui pourrait être automatisée avec une seule formule.

Je m'appelle Marcus Chen, et j'ai passé les 14 dernières années en tant qu'analyste des systèmes d'affaires, travaillant avec tout le monde, des entreprises du Fortune 500 aux start-ups fringantes comptant cinq employés. J'ai examiné plus de 3 000 tableurs au cours de ma carrière, et je peux vous dire avec une certitude absolue que la plupart des gens utilisent environ 5% de la puissance qui leur est disponible. Mais : vous n'avez pas besoin de maîtriser les plus de 400 fonctions d'Excel ou de Google Sheets. Vous devez maîtriser exactement dix.

Ces dix formules gèrent environ 90% du travail réel en entreprise. Je ne parle pas de modélisation financière exotique ou d'analyse statistique. Je parle de la routine quotidienne : suivre les stocks, calculer les commissions, gérer les délais de projet, analyser les données de vente et rapprocher les comptes. Le travail qui paie réellement les factures.

Au cours des cinq dernières années, j'ai formé 847 professionnels dans 23 secteurs, et j'ai suivi leurs modèles d'utilisation des formules. Les données sont remarquablement cohérentes : ces dix formules représentent 87-93% de toute l'utilisation des formules dans les tableurs de production. Tout le reste est soit un travail spécialisé, soit, plus souvent, quelqu'un qui utilise une formule compliquée alors qu'une simple suffirait.

Les Fondements : VLOOKUP (ou son cousin moderne, XLOOKUP)

Commençons par la formule qui a changé ma carrière. VLOOKUP est le couteau suisse du travail sur tableurs, et si vous devez apprendre une seule formule de cet article, faites-en celle-ci. Dans mes données de suivi, VLOOKUP et ses variantes représentent environ 31% de tous les usages de formules dans les tableurs d'entreprise.

Voici ce que fait VLOOKUP : il recherche une valeur dans un tableau et renvoie une valeur correspondante d'une autre colonne. Cela semble simple, mais cela résout un nombre énorme de problèmes du monde réel. Besoin de faire correspondre les noms des clients à leurs numéros de compte ? VLOOKUP. Vous voulez extraire des données de prix de votre liste de produits maîtres ? VLOOKUP. Essayez de rapprocher deux rapports de systèmes différents ? Vous l'avez deviné.

La syntaxe de base est : =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Laissez-moi vous donner un exemple réel d'un client avec lequel j'ai travaillé l'année dernière. Ils dirigeaient une entreprise de distribution en gros avec 4 200 SKU. Chaque semaine, ils recevaient des données de vente provenant de leur système de point de vente avec des codes produits, mais sans noms ni catégories de produits. Quelqu'un recherchait manuellement chaque code dans leur liste de produits maîtres et saisissait les informations. Cela prenait environ 6 heures chaque semaine.

Avec VLOOKUP, nous avons réduit cela à zéro heures. La formule associait automatiquement chaque code produit à la liste maîtresse et tirait le nom, la catégorie, le fournisseur et les données de coût. La formule ressemblait à ceci : =VLOOKUP(A2,ProductMaster!A:E,2,FALSE). Cette formule unique leur a fait économiser 312 heures par an.

Maintenant, si vous utilisez Microsoft 365 ou Excel 2021, vous avez accès à XLOOKUP, qui est essentiellement le frère plus intelligent et plus jeune de VLOOKUP. C'est plus flexible, plus facile à lire, et ça ne se casse pas lorsque vous insérez des colonnes. La syntaxe est même plus simple : =XLOOKUP(lookup_value, lookup_array, return_array). Je recommande d'apprendre XLOOKUP si vous y avez accès, mais VLOOKUP fonctionne toujours très bien et est plus universellement compatible.

La faute la plus courante que je vois avec VLOOKUP, c'est que les gens oublient de régler le quatrième argument sur FALSE (ou 0). Cet argument contrôle si vous voulez une correspondance exacte ou approximative. Pour 95% des cas d'utilisation en entreprise, vous voulez FALSE pour une correspondance exacte. Utiliser TRUE ou omettre cet argument peut vous donner des résultats extrêmement incorrects qui sont difficiles à repérer.

Le Cheval de Travail : SUMIF et SUMIFS

Si VLOOKUP est le couteau suisse, SUMIF est le camion de livraison fiable des formules de tableurs. Il représente environ 18% de l'utilisation des formules dans mon ensemble de données, et pour de bonnes raisons : il résout le problème incroyablement commun de "somme toutes les valeurs qui répondent à certains critères."

Après avoir analysé plus de 3 000 tableurs dans 23 secteurs, le modèle est indéniable : dix formules gèrent 90% du travail réel en entreprise. Tout le reste est soit spécialisé, soit trop compliqué.

La syntaxe de base de SUMIF est : =SUMIF(range, criteria, [sum_range])

Voici un scénario que je rencontre constamment : vous avez un rapport de ventes avec 500 transactions, et vous devez connaître les ventes totales pour la région du Nord-Est. Sans SUMIF, vous ajoutez manuellement les chiffres (sujet aux erreurs et qui prend du temps) ou filtrez et copiez les données vers un autre emplacement (désordonné et difficile à maintenir). Avec SUMIF, c'est une formule : =SUMIF(B:B,"Northeast",C:C).

Mais la véritable puissance réside dans SUMIFS (notez le S), qui vous permet d'utiliser plusieurs critères. J'ai travaillé avec un client du secteur manufacturier qui avait besoin de calculer les coûts de production totaux par ligne de produit, par trimestre, et par installation. Ils avaient un tableur avec 8 700 lignes de données de production. Avant SUMIFS, ils créaient des tableaux croisés dynamiques pour chaque combinaison possible dont ils pourraient avoir besoin. Avec SUMIFS, ils pouvaient obtenir n'importe quelle combinaison à la demande.

La formule ressemblait à ceci : =SUMIFS(E:E,A:A,"Widget A",B:B,"Q2",C:C,"Facility 3"). Cette formule unique a remplacé ce qui avait été un processus de 45 minutes de filtrage, de copie et de calcul. Ils devaient effectuer ces calculs environ 30 fois par mois, donc nous parlons d'une économie d'environ 22 heures de travail par mois.

Voici une astuce pro qui m'a pris des années à découvrir : vous pouvez utiliser des jokers dans vos critères. Besoin de sommer tous les produits qui commencent par "PRO" ? Utilisez =SUMIF(A:A,"PRO*",B:B). L'astérisque est un joker qui correspond à n'importe quel caractère. C'est incroyablement utile lorsque vous traitez des données d'entrée incohérentes.

Une autre technique puissante consiste à utiliser des références de cellule pour vos critères au lieu de les coder en dur. Au lieu de =SUMIF(A:A,"Northeast",B:B), utilisez =SUMIF(A:A,D2,B:B) où D2 contient "Northeast". Cela rend votre tableur dynamique et plus facile à mettre à jour. J'ai vu ce changement unique réduire le temps de maintenance des tableurs de 40% dans les organisations qui ont régulièrement besoin de modifier leurs critères d'analyse.

Le Prise de Décision : Instructions IF

Les instructions IF sont le moteur logique des tableurs, représentant environ 16% de l'utilisation des formules. Elles vous permettent de prendre des décisions basées sur des conditions, ce qui est fondamental pour presque tous les processus d'entreprise. La syntaxe de base est merveilleusement simple : =IF(logical_test, value_if_true, value_if_false)

Mais ne vous laissez pas tromper par la simplicité. J'ai vu des instructions IF résoudre des problèmes allant de simples calculs de commission à des flux de travail d'approbation complexes. Un de mes exemples préférés vient d'une entreprise immobilière pour laquelle j'ai consulté en 2021. Ils devaient calculer les commissions des agents en fonction d'une structure en paliers : 3 % pour les ventes inférieures à 500K $, 3,5 % pour les ventes entre 500K $ et 1M $, et 4 % pour les ventes supérieures à 1M $.

La formule était : =IF(B2<500000,B2*0.03,IF(B2<1000000,B2*0.035,B2*0.04)). Cette instruction IF imbriquée gérait automatiquement les trois niveaux. Avant sa mise en œuvre, leur équipe comptable classait manuellement chaque vente et appliquait le taux correct. Avec 340 transactions par mois, cela prenait environ 8 heures de travail. La formule l'a réduit à zéro.

Voici quelque chose de crucial que j'ai appris : la plupart des gens s'arrêtent aux simples instructions IF, mais la vraie puissance vient de la combinaison de IF avec d'autres fonctions. Par exemple, combiner IF avec AND ou OR vous permet de tester plusieurs conditions. Besoin de flagger les commandes qui sont à la fois supérieures à 10 000 $ et provenant de nouveaux clients ? =IF(AND(B2>10000,C2="New"),"Priority","Standard").

J'ai travaillé avec une équipe de service client qui devait prioriser les tickets de support en fonction de trois facteurs : niveau de client (Premium, Standard, Basic), gravité du problème (Élevé, Moyen, Faible) et temps depuis la soumission. Nous avons construit une formule utilisant des instructions IF imbriquées combinées avec des fonctions AND qui automatisent...