7 Astuces pour travailler avec des formules Excel - des nuls

Plusieurs éléments peuvent vous aider à être aussi productif que possible lors de l'écriture et de la correction des formules Excel. Vous pouvez voir toutes vos formules à la fois et corriger les erreurs une par une. Vous pouvez utiliser des assistants de compléments pour aider à écrire des fonctions.

Priorité de l'opérateur principal

L'un des facteurs les plus importants dans la rédaction des formules est la correction des opérateurs, et je ne parle pas des opérateurs de compagnies de téléphone. Cela a à voir avec les opérateurs mathématiques - vous savez, de petits détails tels que les signes plus, et les signes de multiplication, et où vont les parenthèses. La priorité de l'opérateur - l'ordre dans lequel les opérations sont effectuées - peut faire une grande différence dans le résultat. Vous avez un moyen facile de conserver la priorité de votre opérateur dans l'ordre. Tout ce que vous devez vous rappeler est "S'il vous plaît excusez ma chère tante Sally. "

Cette expression est un mnémonique pour ce qui suit:

Parenthèses
Exposants
Multiplication
Division
Ajout
Soustraction

Ainsi, les parenthèses ont le première priorité (la plus élevée), et la soustraction a la dernière priorité. Eh bien, pour être honnête, la multiplication a la même priorité que la division et l'addition a la même priorité que la soustraction, mais vous avez l'idée!

Par exemple, la formule = 1 + 2 × 15 équivaut à 31. Si vous pensez que cela devrait égaler 45, vous feriez mieux de rendre visite à votre tante! La réponse est égale à 45 si vous incluez des parenthèses, comme ceci: = (1 + 2) × 15.

Obtenir l'ordre des opérateurs corrects est critique pour le bien-être de votre feuille de calcul. Excel génère une erreur lorsque les nombres de parenthèses ouvertes et fermées ne correspondent pas, mais si vous voulez ajouter deux nombres avant la multiplication, Excel ne sait pas que vous avez simplement laissé les parenthèses!

Quelques minutes de rafraîchissement de votre mémoire sur l'ordre de l'opérateur peuvent vous faire économiser beaucoup de maux de tête sur la route.

Formules d'affichage

Au cas où vous ne l'auriez pas remarqué, il est assez difficile d'afficher vos formules sans les modifier accidentellement. C'est parce que chaque fois que vous êtes en mode "modifier" et que la cellule active a une formule, la formule peut incorporer l'adresse de n'importe quelle autre cellule sur laquelle vous cliquez. Cela bouleverse complètement les choses.

Ne serait-il pas facile de regarder à toutes vos formules? Il y a un moyen! C'est simple. Cliquez sur Fichier en haut à gauche de l'espace de travail Excel, cliquez sur Options, sur l'onglet Avancé et faites défiler jusqu'à la section Options d'affichage pour cette feuille de calcul.

Options de réglage.

Notez la case à cocher Afficher les formules dans les cellules au lieu de leurs résultats calculés.Cette case indique à Excel que pour les cellules ayant des formules, la formule elle-même est affichée à la place du résultat calculé. La figure suivante montre une feuille de calcul qui affiche les formules. Pour revenir à l'affichage normal, répétez ces étapes et désélectionnez l'option. Cette option permet de voir facilement toutes les formules!

Visualiser les formules en toute simplicité

Vous pouvez modifier les fonctions accidentellement même si vous avez sélectionné l'option Afficher les formules. Soyez prudent en cliquant autour de la feuille de calcul.

Corriger les formules

Supposons que votre feuille de calcul comporte des erreurs. Ne paniquez pas! Cela arrive même aux utilisateurs les plus avisés, et Excel peut vous aider à comprendre ce qui ne va pas. Dans l'onglet Formules de la section Audit de formule, le bouton Vérification des erreurs est disponible. Cliquez sur le bouton pour afficher la boîte de dialogue Vérification des erreurs, illustrée ici. Autrement dit, la boîte de dialogue apparaît si votre feuille de calcul a des erreurs. Sinon, il affiche simplement un message indiquant que la vérification d'erreur est terminée. C'est si intelligent!

Vérification des erreurs.

Lorsqu'il y a erreurs , la boîte de dialogue apparaît et reste affichée pendant que vous travaillez sur chaque erreur. Les boutons Suivant et Précédent vous permettent de parcourir toutes les erreurs avant la fermeture de la boîte de dialogue. Pour chaque erreur détectée, vous choisissez l'action à effectuer:

Aide sur cette erreur: Ceci conduit au système d'aide et affiche la rubrique correspondant au type d'erreur.
Afficher les étapes de calcul: La boîte de dialogue Evaluate Formula s'ouvre et vous pouvez voir étape par étape le mode de calcul de la formule. Cela vous permet d'identifier l'étape particulière à l'origine de l'erreur.
Ignorer l'erreur: Excel a peut-être tort. Ignorer l'erreur
Modifier dans la barre de formule: C'est un moyen rapide de réparer vous-même la formule si vous n'avez pas besoin d'autre aide.

La boîte de dialogue Vérification des erreurs comporte également un bouton Options. Cliquez sur le bouton pour ouvrir l'onglet Formules de la boîte de dialogue Options Excel. Dans l'onglet Formules, vous pouvez sélectionner des paramètres et des règles pour la reconnaissance et le déclenchement des erreurs.

Utiliser les références absolues

Si vous utilisez la même formule pour un groupe de cellules, comme celles qui descendent une colonne, la meilleure méthode consiste à écrire une fois la formule, puis à la faire glisser vers les autres cellules en utilisant la poignée de remplissage. Le problème est que lorsque vous faites glisser la formule vers de nouveaux emplacements, toutes les références relatives changent.

Souvent, cette est l'intention. Lorsqu'il existe une colonne de données et une colonne de formules adjacente, chaque cellule de la colonne de formule fait généralement référence à son voisin dans la colonne de données. Mais si les formules font toutes référence à une cellule qui n'est pas adjacente, l'intention est généralement que toutes les cellules de formule fassent référence à une référence de cellule immuable. Obtenez ceci pour fonctionner correctement en utilisant une référence absolue à la cellule.

Pour utiliser une référence absolue à une cellule, utilisez le signe dollar ($) avant le numéro de la ligne, avant la lettre de la colonne, ou avant les deux. Pour ce faire, lorsque vous écrivez la première formule, avant de le faire glisser vers d'autres cellules, ou vous devrez mettre à jour toutes les formules.

Par exemple, n'écrivez pas ceci:

= A4 x (B4 + A2)

Ecrivez comme suit:

= A4 x (B4 + $ A $ 2)

De cette façon, toutes les formules font référence à A2 peu importe où vous les copiez, au lieu que cette référence se transforme en A3, en A4, et ainsi de suite.

Utiliser l'audit des formules

Il existe des précédents et des dépendants. Il y a des références externes. Il y a une interaction partout. Comment pouvez-vous suivre d'où viennent les références à la formule?

Utilisez les outils d'audit de formule, c'est comme ça! Dans l'onglet Formules se trouve la section Audit de formules. Dans la section sont divers boutons qui contrôlent la visibilité des flèches de vérification d'audit.

Formules d'audit.

La barre d'outils d'audit de formule comporte plusieurs fonctionnalités qui vous permettent de parcourir vos formules. En plus de montrer les flèches de traçage, la barre d'outils vous permet également de vérifier les erreurs, d'évaluer les formules, de rechercher des données non valides et d'ajouter des commentaires aux feuilles de calcul.

Utiliser la mise en forme conditionnelle

Tout comme la fonction IF renvoie une certaine valeur lorsque la première condition d'argument est vraie et une autre lorsqu'elle est fausse, la mise en forme conditionnelle permet d'appliquer un certain format à une cellule lorsqu'une condition est vraie. Sous l'onglet Accueil de la section Styles se trouve un menu déroulant avec de nombreuses options de mise en forme conditionnelle.

Cette figure montre certaines valeurs qui ont été traitées avec un formatage conditionnel. La mise en forme conditionnelle vous permet de définir la condition et de sélectionner le format appliqué lorsque la condition est remplie. Par exemple, vous pouvez spécifier que la cellule doit être affichée en gras italique lorsque la valeur qu'elle contient est supérieure à 100.

Application d'un format lorsqu'une condition est remplie.

Les conditions sont définies en tant que règles. Les types de règles sont

Formatez toutes les cellules en fonction de leurs valeurs.
Formatez uniquement les cellules qui contiennent …
Formatez uniquement les valeurs classées en haut ou en bas.
Formate uniquement les valeurs supérieures ou inférieures à la moyenne.
Mettre en forme uniquement des valeurs uniques ou en double.
Utilisez une formule pour déterminer les cellules à formater.

Lorsque la condition est vraie, le formatage peut contrôler les éléments suivants:

Bordures
Paramètres de police (style, couleur, gras, italique, etc.)
Remplissage (couleur ou motif d'arrière-plan d'une cellule)

Les cellules peuvent également être formatées avec des schémas de couleurs ou des images d'icônes placés dans la cellule.

Utiliser la validation des données

Dans l'onglet Données, dans la section Outils de données, vous trouverez la validation des données. La validation des données vous permet d'appliquer une règle à une cellule (ou à des cellules) de telle sorte que l'entrée doit respecter la règle. Par exemple, une cellule peut être configurée pour n'accepter qu'une entrée entière entre 50 et 100.

Réglage de la validation des données.

Lorsque l'entrée ne passe pas la règle, un message s'affiche.

Pris en train de faire une mauvaise entrée.

Le message d'erreur peut être personnalisé. Par exemple, si quelqu'un entre le mauvais numéro, le message d'erreur affiché peut dire Noodlehead - apprendre à compter! Ne laisse pas le boss voir ça.