Exceller

29 façons de gagner du temps avec les formules Excel

29 Ways Save Time With Excel Formulas

Les formules sont le pain et le beurre d'Excel. Si vous utilisez régulièrement Excel, je parie que vous utilisez beaucoup de formules. Mais élaborer une formule de travail peut prendre beaucoup trop de temps. Dans cet article, je partage quelques bons conseils pour vous faire gagner du temps lorsque vous travaillez avec des formules dans Excel.





Vidéo: 20 astuces pour gagner du temps avec les formules Excel

1. N'ajoutez pas les parenthèses finales à une fonction

Commençons par quelque chose de vraiment facile ! Lorsque vous entrez une fonction seule (SOMME, MOYENNE, etc.), vous n'avez pas besoin d'entrer les dernières parenthèses fermantes. Par exemple, vous pouvez simplement saisir :





= SOMME (A1 : A10

et appuyez sur retour. Excel ajoutera les parenthèses fermantes pour vous. C'est une petite chose, mais pratique.



Remarque : cela ne fonctionnera pas si votre formule contient plusieurs parenthèses.

Avant - parenthèses fermantes omises

Après avoir appuyé sur Entrée - les parenthèses sont ajoutées automatiquement

2. Déplacer une formule et empêcher les références de changer

Les adresses relatives sont l'une des fonctionnalités les plus puissantes d'Excel : lorsque vous copiez une formule vers un nouvel emplacement, toutes les adresses relatives changent. C'est souvent exactement ce que vous voulez, car la réutilisation des formules est l'une des techniques les plus importantes d'une feuille de calcul bien construite et facile à entretenir.

Mais parfois, vous devez déplacer ou copier une formule vers un nouvel emplacement et vous ne voulez pas du tout que la formule change. Vous pouvez le faire de plusieurs manières.

Si vous déplacez simplement une formule vers un emplacement à proximité, essayez de faire glisser et de déposer. Faire glisser gardera toutes les adresses intactes et inchangées.

Saisir le bord de la sélection

Faites glisser vers un nouvel emplacement

Si vous déplacez une formule vers un emplacement plus éloigné, utilisez le copier-coller. (Win : Ctrl + X, Ctrl + V Mac : Cmd + X, Cmd + V ) Lorsque vous coupez une formule, ses références ne changent pas.

3. Copiez une formule et empêchez les références de changer

Remarque : vous pouvez modifier toutes les références de cellules pour les rendre absolues, mais cette astuce suppose que vous ne voulez pas le faire, pour une raison quelconque.

Si vous avez juste besoin de copier une seule formule, sélectionnez la formule entière dans la barre de formule et copiez-la dans le presse-papiers. Vous pouvez ensuite coller à un nouvel emplacement. Le résultat sera une formule identique à l'original.

Pour copier un grouper des formules vers un nouvel emplacement sans affecter les références, vous pouvez utiliser rechercher et remplacer. Sélectionnez les formules que vous souhaitez copier, puis recherchez et remplacez le signe égal (=) dans les formules par le caractère dièse (#). Cela convertira les formules en texte. Copiez et collez maintenant les formules dans un nouvel emplacement. Après avoir collé les formules et avec toutes les formules sélectionnées, inversez le processus de recherche et de remplacement. Recherchez le dièse (#) et remplacez le signe égal (=). Cela remettra les formules en ordre de marche. (Crédit: Tom Urtis )

4. Double-cliquez sur la poignée de remplissage pour copier les formules

Lorsque vous ajoutez des formules à des tableaux, l'une des choses que vous faites le plus souvent est de copier la formule de la première ligne du tableau à la dernière ligne du tableau. Si vous connaissez les raccourcis clavier pour naviguer dans les données dans Excel, vous pouvez les utiliser pour coller rapidement une colonne entière de formules en quelques frappes. Cependant, la poignée de remplissage est encore plus rapide, car il n'est pas nécessaire de naviguer vers le bas du tableau.

La poignée de remplissage est le petit rectangle qui se trouve dans le coin inférieur droit de toutes les sélections dans Excel. Tant que la formule se trouve dans une colonne à côté d'une autre colonne contient un ensemble complet de données, vous pouvez simplement double-cliquer sur la poignée de remplissage pour copier la formule jusqu'en bas du tableau.

La poignée de remplissage

Excel copie la formule vers le bas

Remarque : Cette astuce ne fonctionnera pas s'il n'y a pas une colonne complète de données à gauche de la formule que vous saisissez. Excel utilise ces données pour déterminer à quelle distance de la feuille de calcul copier la formule. Vous pouvez cependant utiliser cette approche suivante...

Si la sélection n'est pas trop grande, vous pouvez également remplir des formules dans une feuille de calcul à l'aide du raccourci pour Remplir vers le bas (Contrôle + D). Assurez-vous simplement de sélectionner d'abord la formule d'origine et les cellules cibles. C'est plus rapide que copier/coller, mais pas aussi rapide que la poignée de remplissage, surtout si vous copiez la formule dans un grand groupe de cellules.

Vidéo: Raccourcis pour se déplacer rapidement dans une grande liste

5. Utiliser un tableau pour saisir automatiquement des formules

Un moyen encore plus rapide de saisir des formules consiste à d'abord convertir votre tableau en un tableau Excel officiel. La terminologie ici est déroutante, car toute donnée avec plus d'une colonne est techniquement un «tableau», mais Excel a une structure formelle appelée un tableau qui offre de nombreux avantages. Une fois que vous avez converti vos données en un tableau (les deux plates-formes : Ctrl + T), toutes les formules que vous entrez dans la première ligne seront automatiquement copiées sur toute la longueur du tableau. Cela permet de gagner beaucoup de temps et permet également d'éviter les erreurs.

Entrez la formule normalement

Appuyez sur Entrée pour copier la formule vers le bas

En prime, chaque fois que vous mettez à jour une formule dans un tableau, Excel mettra à nouveau à jour toutes les formules similaires dans la même colonne.

Remarque : les formules d'un tableau utiliseront automatiquement des références structurées (c'est-à-dire dans l'exemple ci-dessus =[@Hours]*[@Rate] au lieu de =C4*D4. Cependant, vous pouvez saisir directement les adresses des cellules et elles seront conservées.

6. Utilisez la saisie semi-automatique + onglet pour entrer des fonctions

Lorsque vous entrez un signe égal et commencez à taper, Excel commencera à faire correspondre le texte que vous entrez à la vaste liste de fonctions disponibles. Au fur et à mesure que vous tapez, vous verrez une liste de fonctions « candidats » apparaître ci-dessous. Cette liste se rétrécira à chaque lettre que vous tapez. Une fois que la fonction souhaitée est sélectionnée dans la liste, vous pouvez « demander » à Excel de la saisir pour vous en appuyant sur la touche tab. Sous Windows, les fonctions sont sélectionnées automatiquement au fur et à mesure que vous tapez. Sur un Mac, les options sont présentées mais non sélectionnées, vous devez donc faire une étape supplémentaire : utilisez la touche fléchée pour sélectionner la fonction souhaitée, puis appuyez sur la touche Tab ou Retour pour qu'Excel entre la fonction pour vous.

Mettez en surbrillance la meilleure correspondance et appuyez sur l'onglet

Fonction de saisie semi-automatique d'Excel

7. Utilisez Ctrl + clic pour entrer des arguments

Vous n'aimez pas taper des virgules entre les arguments ? Vous pouvez demander à Excel de le faire pour vous. Lorsque vous entrez des arguments dans une fonction, maintenez simplement la touche Contrôle (Mac: Commande) enfoncée pendant que vous cliquez sur chaque référence et Excel entrera automatiquement des virgules pour vous. Par exemple, vous pouvez saisir une formule telle que : =SUM(A1, B10, C5:C10) en saisissant '=SUM(' puis en cliquant sur chaque référence tout en maintenant la touche Contrôle enfoncée. Cela fonctionnera avec n'importe quelle fonction où vous fournissez des références comme arguments.

Contrôle ou Commande cliquez sur la cellule suivante

Contrôle ou Commande cliquez à nouveau

Toutes les virgules ont été saisies par Excel

8. Utilisez la fenêtre de conseil de formule pour sélectionner des arguments

Chaque fois que vous travaillez avec une formule contenant une fonction Excel, n'oubliez pas que vous pouvez toujours utiliser la fenêtre de conseil pour sélectionner des arguments. Cela peut être un réel gain de temps si la formule est compliquée, surtout si elle contient beaucoup de parenthèses imbriquées. Pour sélectionner des arguments, procédez en deux étapes. À la première étape, cliquez pour placer le curseur à l'intérieur de la fonction dont vous souhaitez sélectionner l'argument. Excel affichera alors un indice pour cette fonction qui affiche tous les arguments. À l'étape deux, cliquez sur l'argument que vous souhaitez sélectionner. Excel sélectionnera l'intégralité de l'argument, même s'il contient d'autres fonctions ou formules. C'est un bon moyen de sélectionner des arguments lors de l'utilisation de F9 pour déboguer une formule.

Survolez l'argument dans l'info-bulle de la formule

Cliquez sur l'argument pour sélectionner à l'intérieur de la formule

Vous voulez en savoir plus ? Nous offrons un cours complet sur Formules et fonctions Excel .

9. Insérer des espaces réservés pour les arguments de fonction avec un raccourci

Normalement, lorsque vous entrez une fonction, Excel présente des conseils sur chaque argument lorsque vous ajoutez des virgules. Mais parfois, vous voudrez peut-être qu'Excel ajoute des espaces réservés pour tous les arguments de fonction à la fois. Si c'est le cas, vous serez heureux de savoir qu'il existe un raccourci pour cela.

Lorsque vous entrez une fonction, une fois qu'Excel a reconnu le nom de la fonction, tapez Control + Shift + A (les deux plates-formes). Par exemple, si vous tapez '=DATE(' puis utilisez Ctrl + Maj + A, Excel vous donnera '=DATE(année,mois,jour)'. Vous pouvez ensuite double-cliquer sur chaque argument (ou utiliser l'astuce Fonction fenêtre pour sélectionner chaque argument) et remplacez-le par la valeur souhaitée.

Assurez-vous que la fonction est reconnue

Contrôle + Maj + A insère des arguments nommés

10. Éloignez-vous de la « formule »

Parfois, lorsque vous saisissez une formule, le la fenêtre d'indice de formule vous gêne , bloquant votre vue des autres cellules que vous souhaitez voir sur la feuille de calcul. Lorsque cela se produit, n'oubliez pas que vous pouvez déplacer la fenêtre d'indice hors de votre chemin. Passez la souris sur le bord de la fenêtre jusqu'à ce que vous voyiez le curseur changer, puis cliquez et faites glisser vers un nouvel emplacement. Ensuite, vous pouvez continuer à saisir ou à modifier votre formule. Selon la structure de votre feuille de calcul, une autre façon de gérer ce problème consiste à modifier la formule dans la barre de formule au lieu de directement dans la cellule.

Saisissez le bord de la pointe de l'écran

Faites glisser l'info-bulle hors de votre chemin

11. Basculer l'affichage de toutes les formules à la fois

Chaque fois que vous modifiez une cellule contenant une formule, Excel affiche automatiquement la formule au lieu de son résultat. Mais parfois, vous pouvez voir toutes les formules d'une feuille de calcul en même temps. Pour cela, il suffit d'utiliser le raccourci clavier pour afficher les formules : Ctrl + ~ (c'est un tilde). Avec ce raccourci, vous pouvez rapidement basculer l'affichage de toutes les formules sur une feuille de calcul ou désactiver. C'est un bon moyen de voir toutes les formules à la fois et de vérifier la cohérence des formules.

Control + ` révèle toutes les formules

Toutes les formules visibles

12. Sélectionnez toutes les formules dans une feuille de calcul à la fois

Une autre façon de voir toutes les formules dans une feuille de calcul consiste à les sélectionner. Vous pouvez le faire en utilisant les fonctionnalités les plus puissantes (et cachées) d'Excel : c'est Aller à > Spécial (Ctrl + G). Avec cette commande, vous pouvez sélectionner toutes sortes de choses intéressantes dans Excel, y compris des cellules vides, des cellules contenant des nombres, des cellules vides et bien plus encore. L'une des options concerne les cellules contenant des formules. Pour sélectionner toutes les cellules contenant des formules sur une feuille de calcul, tapez simplement Ctrl + G pour afficher la boîte de dialogue Atteindre, puis cliquez sur le bouton Spécial, puis sélectionnez Formules. Lorsque vous cliquez sur OK, toutes les cellules contenant des formules seront sélectionnées.

Contrôle + G pour ouvrir Go To spécial

Cliquez sur le bouton Spécial

Sélectionnez des formules

Toutes les formules sont sélectionnées

Si vous souhaitez sélectionner uniquement un sous-ensemble de formules dans une feuille de calcul, effectuez d'abord une sélection, puis utilisez la même commande.

13. Utilisez le collage spécial pour convertir les formules en valeurs statiques

Un problème courant dans Excel est la nécessité d'empêcher les valeurs calculées de changer. Par exemple, vous souhaitez peut-être simplifier une feuille de calcul en supprimant les colonnes « d'assistance » que vous avez utilisées pour générer certaines valeurs. Mais si vous supprimez ces colonnes avec des formules qui y font toujours référence, vous obtiendrez une charge d'erreurs #REF. La solution consiste d'abord à convertir les formules en valeurs, puis à supprimer les colonnes supplémentaires. La façon la plus simple de le faire est d'utiliser Collage spécial. Tout d'abord, sélectionnez les formules que vous souhaitez convertir et copiez-les dans le presse-papiers. Ensuite, avec les formules toujours sélectionnées, ouvrez la boîte de dialogue Collage spécial (Win : Ctrl + Alt + V, Mac : Ctrl + Cmd + V) et utilisez l'option Valeurs. Cela remplacera toutes les formules que vous avez sélectionnées par les valeurs qu'elles ont calculées.

Formules sélectionnées et copiées

Coller spécial invoqué

Plus de formules !

14. Utilisez Collage spécial pour ajuster les valeurs en place

Un autre problème courant dans Excel est la nécessité de modifier de nombreuses valeurs en place. Par exemple, vous avez peut-être une liste de 500 prix de produits et vous devez augmenter tous les prix de 5%. Ou peut-être avez-vous une liste de 100 dates qui doivent toutes être décalées d'une semaine dans le futur ? Dans de tels cas, vous pouvez ajouter une colonne « d'assistance » à votre table, effectuer le calcul requis, convertir les résultats en valeurs, puis les copier sur la colonne d'origine. Mais si vous n'avez besoin que d'un simple calcul, Collage spécial est beaucoup plus simple et rapide, car vous pouvez modifier la valeur directement sans aucune formule supplémentaire.

Par exemple, pour convertir un ensemble de dates en place en une semaine plus tard, procédez comme suit : ajoutez le nombre 7 à n'importe quelle cellule de la feuille de calcul, puis copiez-le dans le presse-papiers. Ensuite, sélectionnez toutes les dates que vous souhaitez modifier. Ensuite, utilisez Collage spécial > Opérations > Ajouter. Lorsque vous cliquez sur OK, Excel ajoute le nombre 7 aux dates que vous avez sélectionnées, les avançant dans le temps de 7 jours, sans qu'il soit nécessaire de créer des colonnes d'aide.

Copier la valeur temporaire et sélectionner les dates

Collage spécial avec Valeurs + Ajouter

Toutes les dates ont avancé de 7 jours

Pour augmenter un ensemble de prix de 10 %, utilisez la même approche. Entrez 1.10 dans une cellule et copiez-le dans le presse-papiers. Sélectionnez ensuite les prix que vous souhaitez modifier et utilisez Collage spécial > Opérations > Multiplier pour convertir tous les prix en place. Une fois que vous aurez compris cette astuce, vous en trouverez de nombreuses utilisations intelligentes.

Copier la valeur temporaire et sélectionner les prix

Collage spécial avec Valeurs + Multiplier

Tous les prix ont augmenté de 10%

Remarque : cela ne fonctionne qu'avec des valeurs. N'essayez pas avec des formules !

15. Utilisez des plages nommées pour rendre les formules plus lisibles

L'un des plus anciens conseils de pro du livre est d'utiliser plages nommées dans vos formules pour les rendre plus lisibles. Par exemple, supposons que vous ayez une feuille de calcul simple qui montre les heures travaillées pour une petite équipe. Pour chaque personne, vous souhaitez multiplier ses heures travaillées par un seul taux horaire. En supposant que le taux horaire se trouve dans la cellule A1, vos formules pourraient ressembler à ceci :

=B3 * $A
=B4 * $A
=B5 * $A

Mais si vous nommez la cellule A1 'hourly_rate', vos formules ressembleront à ceci :

=B3 * taux_horaire
=B4 * taux_horaire
=B5 * taux_horaire

Nommer les plages de cette façon rend les formules beaucoup plus lisibles et évite de saisir de nombreux signes dollar ($) pour créer des références absolues.

Nommer les plages est facile. Sélectionnez simplement la ou les plages/cellules que vous souhaitez nommer, puis tapez un nom dans la zone de nom et appuyez sur Entrée. Maintenant que vous avez nommé une plage, Excel l'utilisera chaque fois que vous pointez et cliquez sur la plage pendant que vous créez une formule - lorsque vous cliquez sur une plage nommée, vous verrez son nom automatiquement inséré dans la formule. En prime, vous pouvez également naviguer facilement vers la plage nommée quand vous le souhaitez. Sélectionnez simplement le nom dans la liste déroulante qui apparaît à côté de la zone de nom.

Formule normale sans plage de noms

Nommer la cellule C2

Formule mise à jour pour utiliser la plage nommée

16. Appliquer automatiquement des noms aux formules existantes

Que se passe-t-il lorsque vous avez déjà créé des formules, puis créez un plage nommée voulez-vous utiliser en eux? Rien, en fait. Excel n'apportera aucune modification à vos formules existantes ni ne proposera d'appliquer automatiquement les nouveaux noms de plage. Cependant, il existe un moyen d'appliquer des noms de plage aux formules existantes. Sélectionnez simplement les formules auxquelles vous souhaitez appliquer des noms, puis utilisez la fonction Appliquer des noms.

Une fois la fenêtre Appliquer les noms ouverte, sélectionnez les noms que vous souhaitez appliquer et cliquez sur OK. Excel remplacera toutes les références correspondantes par les noms que vous avez sélectionnés.

Sélectionnez toutes les cellules de formule auxquelles appliquer la plage nommée

que fait le pays pour exceller

Appliquer des noms sur l'onglet Formules du ruban

Sélectionnez la plage nommée à appliquer

Plage nommée appliquée automatiquement

17. Enregistrer une formule qui n'est pas terminée

Si vous travaillez avec une formule plus complexe, il y a de fortes chances qu'il faille un certain temps pour que la formule fonctionne correctement. Mais vous manquez peut-être de temps et devez revenir à la formule plus tard pour la faire fonctionner comme vous le souhaitez. Malheureusement, Excel ne vous permettra pas d'entrer une formule qui est cassée. Si vous essayez, Excel se plaindra bruyamment que la formule contient des erreurs et ne vous laissera pas continuer tant que vous n'aurez pas résolu tous les problèmes.

Cependant, il existe une solution de contournement simple : il suffit de convertir temporairement la formule en texte. Pour ce faire, vous pouvez soit ajouter une seule apostrophe au début de la formule (avant le =), soit simplement supprimer complètement le signe égal. Dans les deux cas, Excel cessera d'essayer d'évaluer la formule et vous permettra de la saisir telle quelle. Plus tard, vous pourrez revenir à la formule et reprendre le travail.

18. Soyez conscient des fonctions offertes par Excel

Des fonctions existent pour résoudre des problèmes spécifiques. Vous pouvez considérer une fonction comme une formule prédéfinie avec un nom, un objectif et une valeur de retour spécifiques. Par exemple, la fonction PROPER n'a qu'un seul but : elle met des mots en majuscule. Donnez-lui un texte comme « NEW York CITY », et il vous rendra « New York City ». Les fonctions sont incroyablement pratiques lorsqu'elles résolvent un problème que vous rencontrez, il est donc logique de vous familiariser avec les fonctions disponibles dans Excel.

Voir: Guide des fonctions Excel et 500 exemples de formules Excel pour une liste des fonctions et formules couramment utilisées qui les utilisent.

Remarque : les gens sont souvent confus par la terminologie utilisée pour parler des fonctions et des formules. Une façon simple d'y penser est la suivante : tout ce qui commence par un signe égal dans Excel est une formule. Par cette définition, toutes les fonctions sont également des formules, et les formules peuvent contenir plusieurs fonctions.

19. Utilisez F4 pour basculer les références relatives et absolues

La clé pour construire des formules que vous pouvez copier élégamment vers de nouveaux emplacements et qui fonctionnent toujours correctement, consiste à utiliser la bonne combinaison de références absolues et relatives. La raison pour laquelle cela est si important est qu'elle vous permet de réutiliser des formules existantes au lieu d'en créer de nouvelles, et la réutilisation de la même formule réduit considérablement le risque d'erreurs dans un classeur en limitant le nombre de formules à vérifier.

Cependant, la conversion de références entre références relatives et absolues peut être une nuisance - la saisie de tous ces signes dollar ($) est fastidieuse et sujette aux erreurs. Heureusement, il existe un excellent raccourci qui vous permet de basculer rapidement entre les 4 options disponibles pour chaque référence : (Windows : F4, Mac : Commande + T). Placez simplement le curseur dans une référence en utilisant le raccourci. Chaque fois que vous l'utilisez, Excel passe à l'option suivante dans cet ordre : entièrement relatif (A1) > entièrement absolu ($A) > ligne absolue (A) > colonne absolue ($A1).

20. N'oubliez pas que les formules et les fonctions renvoient une valeur. Toujours.

Lorsque vous avez du mal avec une formule, c'est parfois parce que vous pensez qu'une partie de la formule renvoie une certaine valeur, mais qu'elle renvoie en fait quelque chose d'autre. Pour vérifier ce qui est réellement renvoyé par une fonction ou par une partie d'une formule, utilisez l'astuce F9 ci-dessous.

Remarque : Le mot « retour » vient du monde de la programmation, et il confond parfois les personnes qui découvrent ce concept. Dans le contexte des formules Excel, il est utilisé comme ceci 'La fonction LEN renvoie un nombre' ou 'La formule de la cellule A1 renvoie une erreur'. Chaque fois que vous entendez le mot « retour » avec une formule, pensez simplement « résultat ».

21. Utilisez F9 pour évaluer les parties d'une formule

Le raccourci F9 (fn + F9 sur un Mac) permet de résoudre des parties d'une formule en temps réel. C'est un outil fantastique pour déboguer des formules plus volumineuses, lorsque vous devez vérifier que le résultat d'une partie particulière de la formule correspond à ce que vous attendez. Pour utiliser cette astuce, modifiez la formule et sélectionnez l'expression ou la fonction que vous souhaitez évaluer (astuce - utilisez la fenêtre d'astuces de fonction pour sélectionner des arguments entiers). Si vous avez une sélection, appuyez sur F9. Vous verrez cette partie de la formule remplacée par la valeur qu'elle renvoie.

Remarque : sous Windows, vous pouvez annuler F9, mais cela ne semble pas fonctionner sur Mac. Pour quitter la formule sans apporter de modifications, utilisez simplement Esc.

Vidéo: Comment vérifier et déboguer une formule avec F9

22. Utilisez Formula Evaluator pour résoudre une formule étape par étape

Lorsque l'utilisation de F9 pour évaluer une formule devient trop fastidieuse, il est temps de sortir du matériel plus gros : Évaluer la formule. Évaluer la formule résout une formule étape par étape. Chaque fois que vous cliquez sur le bouton Évaluer, Excel résout la partie soulignée de la formule et vous montre le résultat. Vous pouvez trouver Évaluer la formule dans l'onglet Formules du ruban, dans le groupe Audit de formule. Pour utiliser Évaluer la formule, sélectionnez une formule et cliquez sur le bouton du ruban. Lorsque la fenêtre s'ouvre, vous verrez la formule affichée dans une zone de texte avec un bouton Évaluer ci-dessous. Une partie de la formule sera soulignée — il s'agit de la partie actuellement « en cours d'évaluation ». Lorsque vous cliquez sur évaluer, la partie soulignée de la formule est remplacée par la valeur qu'elle renvoie. Vous pouvez continuer à cliquer sur évaluer jusqu'à ce que la formule soit complètement résolue.

Vidéo: Comment évaluer des formules complexes

Remarque : Évaluer la formule n'est disponible que dans la version Windows d'Excel. La version Mac adopte une approche différente, appelée Formula Builder, qui affiche les résultats lorsque vous créez une formule. Ce n'est pas tout à fait la même fonctionnalité, mais c'est très utile, et vous pouvez l'utiliser avec des formules qui existent déjà.

23. Construisez des formules complexes par petites étapes

Lorsque vous devez créer une formule plus complexe et que vous ne savez pas comment le faire, commencez par l'approche générale et quelques valeurs codées en dur. Ajoutez ensuite plus de logique pour remplacer les valeurs codées en dur une étape à la fois. Par exemple, disons que vous voulez écrire une formule qui extrait le prénom d'un nom complet. Vous savez que vous pouvez utiliser la fonction GAUCHE pour extraire du texte à partir de la gauche, mais vous ne savez pas comment calculer le nombre de caractères à extraire. Commencez par LEFT(full_name, 5) pour que la formule fonctionne. Ensuite, découvrez comment remplacer le nombre 5 par une valeur calculée. Dans ce cas, vous pouvez calculer le nombre de caractères à extraire en utilisant la fonction FIND pour localiser la position du premier caractère espace.

Vidéo: Comment construire une formule complexe étape par étape

24. Utilisez des plages nommées comme des variables

Dans de nombreux cas, il est judicieux d'utiliser plages nommées comme des variables pour rendre vos formules plus flexibles et plus faciles à utiliser. Par exemple, si vous effectuez beaucoup de concaténation (rassemblez des valeurs de texte), vous pouvez créer vos propres plages nommées pour les caractères de nouvelle ligne, les caractères de tabulation, etc. De cette façon, vous pouvez simplement vous référer directement aux plages nommées. au lieu d'ajouter beaucoup de syntaxe complexe à vos formules. En prime, si vos plages nommées contiennent des valeurs de texte, vous n'avez pas besoin d'utiliser de guillemets autour du texte lorsque vous les ajoutez à une formule. Cette idée un peu difficile à expliquer donc voir cette vidéo pour un exemple détaillé .

25. Utilisez la concaténation dans les étiquettes pour clarifier les hypothèses

Lorsque vous créez une feuille de calcul qui repose sur certaines hypothèses, il peut être difficile de montrer clairement les hypothèses que vous formulez. Souvent, vous aurez une certaine zone sur la feuille de calcul pour les entrées et une autre zone pour les sorties, et il n'y a pas de place pour afficher les deux en même temps. Une façon de s'assurer que les hypothèses clés sont claires consiste à les intégrer directement dans les étiquettes qui apparaissent sur la feuille de calcul à l'aide de la concaténation, généralement avec la fonction TEXTE.

Par exemple, normalement, vous pourriez avoir une étiquette indiquant « Coût du café » suivi d'un coût calculé. Avec la concaténation, vous pouvez faire en sorte que l'étiquette indique « Coût du café (2,00 $/tasse) : ce qui indique clairement que vous avez supposé un coût de 2,00 $/tasse.

Vidéo: clarifier les hypothèses avec la concaténation

26. Ajoutez des sauts de ligne aux IF imbriqués pour les rendre plus faciles à lire

Lorsque vous créez une formule SI imbriquée, le suivi des arguments vrais et faux dans un blizzard de parenthèses peut devenir très déroutant. Lorsque vous vous disputez les parenthèses, il est facile de se tromper de logique. Cependant, il existe un moyen simple de rendre une formule avec plusieurs instructions IF beaucoup plus facile à lire : ajoutez simplement des sauts de ligne à la formule après chaque argument VRAI. Cela rendra la formule plus lisible comme un tableau.

Vidéo: Comment rendre les SI imbriqués plus faciles à lire

27. Saisir des fonctions avec la saisie semi-automatique

Lorsque vous entrez une fonction, Excel essaie de deviner le nom de la fonction souhaitée et présente une liste de saisie semi-automatique parmi laquelle vous pouvez faire votre choix. La question est de savoir comment accepter l'une des options affichées tout en restant en mode édition ? L'astuce consiste à utiliser la touche de tabulation. Lorsque vous appuyez sur la touche de tabulation, Excel ajoute la fonction complète et laisse le curseur actif entre parenthèses afin que vous puissiez remplir les arguments selon vos besoins. Sur un Mac, vous devez d'abord utiliser la touche fléchée vers le bas pour sélectionner la fonction que vous souhaitez ajouter, puis appuyez sur Tab pour insérer la fonction.

28. Utilisez AutoSum pour saisir des formules SUM

Vous n'avez pas toujours la chance d'utiliser cette somme automatique, mais quand vous le faites, c'est satisfaisant. La somme automatique fonctionne à la fois pour les lignes et les colonnes. Sélectionnez simplement une cellule vide à droite ou en dessous des cellules que vous souhaitez additionner et tapez Alt + = (Mac : Commande + Maj + T). Excel va deviner la plage que vous essayez de additionner et insérer la fonction SUM en une seule étape. Si vous souhaitez être plus précis, afin qu'Excel ne devine pas, sélectionnez d'abord la plage que vous souhaitez additionner, y compris la cellule dans laquelle vous souhaitez que la fonction SUM soit.

AutoSum insère même plusieurs fonctions SUM en même temps. Pour additionner plusieurs colonnes, sélectionnez une plage de cellules vides sous les colonnes. Pour additionner plusieurs lignes, sélectionnez une plage de cellules vides dans une colonne à droite des lignes.

Enfin, vous pouvez utiliser la somme automatique pour ajouter les totaux des lignes et des colonnes en même temps pour un tableau entier. Sélectionnez simplement un tableau complet de nombres, y compris les cellules vides sous le tableau et à droite du tableau, et utilisez le raccourci. Excel ajoutera les fonctions SUM appropriées dans les cellules vides, vous donnant les totaux des colonnes, les totaux des lignes et un total général en une seule étape.

29. Entrez la même formule dans plusieurs cellules à la fois

Souvent, vous devrez entrer la même formule dans un groupe de cellules. Vous pouvez le faire en une seule étape avec le raccourci clavier Ctrl + Entrée. Sélectionnez simplement toutes les cellules en même temps, puis entrez la formule normalement comme vous le feriez pour la première cellule. Ensuite, lorsque vous avez terminé, au lieu d'appuyer sur Entrée, appuyez sur Ctrl + Entrée. Excel ajoutera la même formule à toutes les cellules de la sélection, en ajustant les références au besoin. Lorsque vous utilisez cette approche, vous n'avez pas besoin de copier-coller, de remplir ou d'utiliser la poignée de remplissage. Vous avez terminé en une seule étape.

Vous pouvez également utiliser cette même technique pour modifier plusieurs formules en même temps. Sélectionnez simplement toutes les formules à la fois, apportez la modification dont vous avez besoin et appuyez sur Ctrl + Entrée.

Auteur Dave Bruns


^