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 Excel régulièrement, je parie que vous utilisez beaucoup de formules. Mais l'élaboration d'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 simple! Lorsque vous entrez une fonction seule (SOMME, MOYENNE, etc.), vous n'avez pas besoin de saisir les parenthèses de fermeture finales. Par exemple, vous pouvez simplement entrer:



= SOMME (A1: A10

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



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

Avant - parenthèses fermantes omises

Après avoir appuyé sur Entrée - parenthèses 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 dans 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 conçue 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. Il existe plusieurs façons de procéder.

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

Saisissez 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 cellule pour les rendre absolues, mais cette astuce suppose que vous ne voulez pas faire cela, quelle qu'en soit la raison.

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. Maintenant, copiez et collez les formules dans un nouvel emplacement. Après avoir collé les formules et après avoir sélectionné toutes les formules, inversez le processus de recherche et de remplacement. Recherchez le hachage (#) et remplacez un signe égal (=). Cela rétablira les formules en ordre de marche. (Crédit: Tom Urtis )

4. Double-cliquez sur la poignée de recopie pour copier les formules vers le bas

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 dans une colonne entière de formules en quelques touches. Cependant, la poignée de recopie 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 sera un ensemble complet de données, vous pouvez simplement double-cliquer sur la poignée de recopie 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 entrez. 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 (Ctrl + D). Assurez-vous simplement de sélectionner d'abord la formule d'origine et les cellules cibles. C'est plus rapide que le copier / coller, mais pas aussi vite que la poignée de recopie, 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. Utilisez un tableau pour saisir automatiquement des formules

Un moyen encore plus rapide d'entrer des formules consiste à d'abord convertir votre tableau en un tableau Excel officiel. La terminologie ici prête à confusion, car toutes les données avec plus d'une colonne sont techniquement un «tableau», mais Excel a une structure formelle appelée tableau qui offre de nombreux avantages. Une fois que vous avez converti vos données en table (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 économise 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 met à 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 = [@ heures] * [@ taux] au lieu de = C4 * D4. Cependant, vous pouvez saisir directement les adresses des cellules et elles seront conservées.

6. Utilisez l'onglet Saisie semi-automatique + pour accéder aux fonctions

Lorsque vous entrez un signe égal et commencez à taper, Excel commence à 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 «candidates» ci-dessous. Cette liste se rétrécit avec chaque lettre que vous tapez. Une fois la fonction souhaitée sélectionnée dans la liste, vous pouvez «demander» à Excel de la saisir pour vous en appuyant sur tab. Sous Windows, les fonctions sont sélectionnées automatiquement à mesure que vous tapez. Sur un Mac, les options sont présentées mais non sélectionnées, vous devez donc faire une étape de plus: utilisez la touche fléchée pour sélectionner la fonction souhaitée, puis appuyez sur tab ou retour pour qu'Excel entre la fonction à votre place.

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

Fonction d'auto-complétion 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 tout en cliquant sur chaque référence et Excel entrera automatiquement des virgules pour vous. Par exemple, vous pouvez entrer 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 toute fonction dans laquelle 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é entrées par Excel

8. Utilisez la fenêtre info-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 d'indices pour sélectionner des arguments. Cela peut être un gain de temps réel si la formule est compliquée, surtout si elle contient beaucoup de parenthèses imbriquées. Pour sélectionner des arguments, travaillez en deux étapes. À la première étape, cliquez pour placer le curseur dans la fonction dont vous souhaitez sélectionner l'argument. Excel affichera alors un indice pour cette fonction qui affiche tous les arguments. À la deuxième étape, 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

Envie d'en savoir plus? Nous proposons un cours complet sur Formules et fonctions Excel .

9. Insérer des espaces réservés d'argument 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 souhaiterez peut-être qu'Excel ajoute des espaces réservés pour tous les arguments de fonction à la fois. Si tel 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 Ctrl + Maj + 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 modifiez-le à la valeur souhaitée.

Assurez-vous que la fonction est reconnue

Ctrl + Maj + A insère des arguments nommés

10. Déplacez le «truc de la formule» hors de votre chemin

Parfois, lorsque vous entrez une formule, le la fenêtre d'indication 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'indices 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. En fonction de 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 plutôt que directement dans la cellule.

Saisissez le bord de la pointe de l'écran

Faites glisser la pointe de l'écran 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 sur une feuille de calcul en même temps. Pour ce faire, utilisez simplement 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 une bonne façon 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 masquées) d'Excel: 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 est constituée de cellules contenant des formules. Pour sélectionner toutes les cellules contenant des formules dans 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.

Ctrl + G pour ouvrir Aller à 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 Collage spécial pour convertir des formules en valeurs statiques

Un problème courant dans Excel est la nécessité d'arrêter 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. Le moyen le plus simple de le faire est d'utiliser Collage spécial. Tout d'abord, sélectionnez les formules que vous souhaitez convertir et copier 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 avaient calculées.

Formules sélectionnées et copiées

Collage 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éplacées dans le futur d'une semaine? 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 plus 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 numéro 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 numéro 7 aux dates que vous avez sélectionnées, les faisant avancer dans le temps de 7 jours, sans avoir besoin de créer des colonnes d'assistance.

Copiez la valeur de la température et sélectionnez les dates

Coller spécial avec des valeurs + ajouter

Toutes les dates sont avancées de 7 jours

Pour augmenter un ensemble de prix de 10%, utilisez la même approche. Entrez 1.10 dans une cellule et copiez-la 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 y trouverez de nombreuses utilisations intelligentes.

Copiez la valeur de la température et sélectionnez les prix

Coller 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, disons que vous avez 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 est dans la cellule A1, vos formules pourraient ressembler à ceci:

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

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 manière rend les formules beaucoup plus lisibles et évite de saisir beaucoup de 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 saisissez 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 inséré automatiquement dans la formule. En prime, vous pouvez également accéder facilement à 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 les noms aux formules existantes

Que se passe-t-il lorsque vous avez déjà créé des formules, puis créé un plage nommée vous voulez les utiliser? 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 à des formules existantes. Sélectionnez simplement les formules auxquelles vous souhaitez appliquer des noms, puis utilisez la fonction Appliquer les 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 dans l'onglet Formules du ruban

Sélectionnez la plage nommée à appliquer

Plage nommée appliquée automatiquement

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

Si vous travaillez avec une formule plus complexe, il y a de fortes chances que cela prenne un certain temps pour que la formule fonctionne correctement. Mais il se peut que vous manquiez de temps et que vous deviez revenir à la formule plus tard pour qu'elle fonctionne comme vous le souhaitez. Malheureusement, Excel ne vous permettra pas d'entrer une formule qui n'est pas conforme. Si vous essayez, Excel se plaindra bruyamment du fait 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: convertissez simplement temporairement la formule en texte. Pour ce faire, vous pouvez soit ajouter une apostrophe unique 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 pouvez revenir à la formule et reprendre le travail.

18. Soyez conscient des fonctions qu'offre 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 les 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 couramment utilisées et des formules qui les utilisent.

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

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

La clé pour construire des formules que vous pouvez copier élégamment vers de nouveaux emplacements tout en fonctionnant correctement, consiste à utiliser la bonne combinaison de références absolues et relatives. La raison pour laquelle cela est si important est que cela 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 la possibilité d'erreurs dans un classeur en limitant le nombre de formules à vérifier.

Cependant, la conversion des références entre les références relatives et absolues peut être une nuisance - taper tous ces signes dollar ($) est fastidieux et sujet 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 `` tournera '' vers l'option suivante dans cet ordre: entièrement relatif (A1)> entièrement absolu ($ A $ 1)> ligne absolue (A $ 1)> colonne absolue ($ A1).

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

Lorsque vous vous débattez avec une formule, c'est parfois parce que vous pensez qu'une partie de la formule renvoie une certaine valeur, mais en fait, elle renvoie autre chose. 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 ne connaissent pas 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 des 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 est 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 de conseil de fonction pour sélectionner des arguments entiers). Une fois que 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 un Mac. Pour quitter la formule sans apporter de modifications, utilisez simplement Echap.

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. Evaluate Formula résout une formule une étape à la fois. Chaque fois que vous cliquez sur le bouton Évaluer, Excel résoudra la partie soulignée de la formule et vous montrera le résultat. Vous pouvez trouver Evaluer la formule dans l'onglet Formules du ruban, dans le groupe Audit de formule. Pour utiliser Evaluer 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 - c'est 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 entièrement résolue.

Vidéo: Comment évaluer des formules complexes

Remarque: la formule d'évaluation 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. Construire des formules complexes en petites étapes

Lorsque vous avez besoin de créer une formule plus complexe et que vous ne savez pas comment le faire, commencez par l'approche générale et certaines 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 souhaitez é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 depuis 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 déterminer le nombre de caractères à extraire en utilisant la fonction FIND pour localiser la position du premier caractère d'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 logique d'utiliser plages nommées comme des variables pour rendre vos formules plus flexibles et plus faciles à utiliser. Par exemple, si vous faites beaucoup de concaténation (joignant des valeurs de texte ensemble), 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 alors voir cette vidéo pour un exemple détaillé .

25. Utiliser 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 faites. 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 incorporer directement dans les étiquettes qui apparaissent sur la feuille de calcul à l'aide de la concaténation, généralement avec la fonction TEXT.

Par exemple, normalement, vous pourriez avoir une étiquette qui dit «coût du café» suivi d'un coût calculé. Avec la concaténation, vous pouvez faire en sorte que l'étiquette dise «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 IF imbriquée, le suivi des arguments vrais et faux dans une tempête de parenthèses peut devenir très déroutant. Lorsque vous mélangez les parenthèses, il est facile de se tromper dans la 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 TRUE. Cela rendra la formule plus semblable à un tableau.

Vidéo: Comment rendre les IF imbriquées plus faciles à lire

27. Entrez les fonctions avec la saisie semi-automatique

Lorsque vous entrez une fonction, Excel essaiera de deviner le nom de la fonction souhaitée et présentera une liste de saisie semi-automatique dans laquelle vous pourrez faire votre choix. La question est: 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 tabulation, Excel ajoute la fonction complète et laisse le curseur actif entre les 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 appuyer sur Tab pour insérer la fonction.

28. Utilisez la somme automatique pour entrer les formules SUM

Vous n'avez pas toujours la possibilité d'utiliser cette somme automatique, mais lorsque vous le faites, c'est satisfaisant. La somme automatique fonctionne 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 devinera la plage que vous essayez de additionner et insérera la fonction SOMME 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 SOMME soit.

AutoSum insérera 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 une table entière. Sélectionnez simplement un tableau complet de nombres, y compris des cellules vides sous le tableau et à droite du tableau, et utilisez le raccourci. Excel ajoutera les fonctions SOMME 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 effectivement 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 et coller, de remplir ou d'utiliser la poignée de recopie. 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 les modifications dont vous avez besoin et appuyez sur Ctrl + Entrée.

Auteur Dave Bruns


^