Formules et fonctions Excel

Pourquoi ai-je besoin de formules pour travailler dans Excel? Microsoft Excel n’est pas seulement un programme pour créer des feuilles de calcul. En fait, c’est un outil puissant pour travailler avec des données tabulaires. L’avantage le plus important de MS Excel est les fonctions intégrées, ainsi que la possibilité de créer des formules pour le calcul. Ces formules peuvent être très simples ou très complexes.
Comment créer une formule dans Excel
Utilisation des fonctions dans Excel
Fonctions financières Excel
Fonctions de date dans Excel
Fonctions mathématiques Excel
Fonctions statistiques Excel
Travailler avec des tableaux Excel
Fonctions des bases de données Excel
Fonctions de texte Excel
Fonctions logiques Excel

Comment créer une formule dans Excel

Les utilisateurs de PC débutants croient souvent qu’il est très difficile de créer des formules dans Excel, et n’utilisent donc pas cette fonctionnalité de l’application. En fait, pour maîtriser les calculs les plus simples dans Excel, il suffit de passer un peu de temps.

MS Excel vous permet d’effectuer diverses opérations sur les tables de données de deux manières:

Créer des formules manuellement.
Utilisation des fonctions Excel intégrées.

Comment créer une formule dans Excel? Toute formule, qu’elle soit écrite à la main ou créée à l’aide de fonctions intégrées, commence par un signe “=”. Ensuite, allez directement au calcul. Afin de comprendre le mécanisme de création de formules, nous illustrons ceci avec l’exemple le plus simple.

Il y a une table avec le code des marchandises, leur quantité et prix. En face de chaque article, vous devez indiquer le montant total du coût des marchandises. Ainsi, dans chaque cellule de la colonne finale, il doit y avoir un produit du prix de la quantité de cette marchandise.
Créons une formule qui va automatiquement multiplier le contenu de ces cellules.

1. Placez le curseur dans la cellule D2.
2. Dans la ligne de formule, écrivez = B2 * C2 et appuyez sur Entrée.

Dans la cellule D2, le résultat de la multiplication du contenu des cellules B2 et C2 apparaîtra.

Si vous copiez la cellule D2, le contenu de la cellule (numéro 48) n’est pas copié, mais la formule.

Et cette formule changera automatiquement en fonction du nombre de cellules. Par exemple, si la cellule D2 est copiée dans D3, les nombres de cellules dans la formule augmentent de 1, c’est-à-dire que la formule dans la cellule D3 prend la forme =B3*C3.

Dans l’exemple ci-dessus, il suffit d’écrire la formule une seule fois, puis de simplement la copier dans les cellules du bas en tirant le coin inférieur droit.

S’il est nécessaire de copier non pas la formule, mais le contenu de la cellule, vous devez modifier légèrement la formule en mettant le signe $ dans ses éléments. Par exemple, pour la cellule D2, cette formule prend la forme =$B$2*$C$2.

La figure montre que le contenu de la cellule a été copié, pas la formule.

Vous pouvez également ne modifier qu’une partie des éléments de la formule. Par exemple, nous multiplions la quantité de biens dans chaque cellule par le prix des biens avec le code 111111. Pour la cellule D2, la formule est un exemple de la forme =B2*$C$2. Si cette formule est copiée puis collée dans une autre cellule, le second facteur restera inchangé. Autrement dit, dans cet exemple, la quantité de tout produit sera multipliée par 12.

Dans ces exemples, nous n’avons considéré que les formules les plus simples. En fait, ils peuvent être avec beaucoup d’éléments, des signes mathématiques, des crochets. Il est important de se souvenir de la priorité d’effectuer des calculs dans la formule. Au début, l’action est toujours effectuée entre parenthèses. Ensuite, l’exponentiation, la multiplication et la division sont calculées. Et seulement après cette addition et soustraction. Si vous doutez que vous avez correctement défini la séquence de calculs dans la formule Excel, placez les éléments nécessaires entre parenthèses. Même si elles peuvent être omises par les règles des mathématiques.

Parfois les utilisateurs oublient que la condition obligatoire pour multiplier les éléments de la formule est la multiplication du signe *, car les règles des mathématiques permettent d’omettre le signe de multiplication. Par exemple, la formule “(a+b)c” selon les règles des mathématiques sera considérée comme correctement écrite, alors qu’Excel va donner une erreur. La formule dans ce cas doit avoir la forme =(A2+B2)*C2.

Utilisation des fonctions dans Excel

Les fonctions dans Excel sont un autre élément important de cette application. Dans son essence, la fonction est un enregistrement court des formules fréquemment utilisées. Par exemple, au lieu d’écrire la formule =B2+B3+B4+B5+B6, vous pouvez utiliser la fonction =SUM(B2: B6). Il y a beaucoup de fonctions, elles sont divisées en catégories. Avec l’aide d’eux, vous pouvez effectuer non seulement des calculs mathématiques, mais aussi effectuer des calculs statistiques, financiers, d’ingénierie et bien plus encore.

Pour les débutants, il est recommandé d’utiliser l’assistant de fonction lors de l’exploration des fonctions Excel. Vous pouvez le trouver en cliquant sur l’icône de la fonction située près de la barre de formule ou en cliquant sur le triangle dans le menu Excel supérieur près de l’icône de la somme.

Considérons un exemple simple. Vous devez calculer le montant total pour la colonne spécifiée. Nous utilisons à cette fin une fonction qui calcule la quantité dans la plage spécifiée de cellules.

Cliquez sur la cellule où le montant total sera indiqué.
Appuyez sur l’icône d’appel de fonction.
Sélectionnez la fonction souhaitée dans la liste des catégories et cliquez sur OK. Dans ce cas, ce sera une fonction de SUM. Chaque fonction a une brève description de ce qu’elle fait.
Sélectionnez les arguments de la fonction. Dans ce cas, ce sera la plage de cellules de D2 à D8.
Après avoir cliqué sur le bouton “OK” dans la cellule finale, le résultat du calcul du montant total apparaît.

Pour chacune des fonctions Excel de l’assistant de fonction, vous pouvez voir un bref conseil ou appeler l’aide sur cette fonction. La fonction sélectionnée peut être éditée dans la barre de formule. Il est également possible d’entrer la fonction manuellement en la tapant simplement dans la barre de formule sans utiliser l’assistant de fonction.

Fonctions financières Excel

Il est impossible d’imaginer le travail d’un comptable, économiste ou financier dans cette application sans utiliser les fonctions Excel. En outre, les fonctions financières peuvent être utilisées dans d’autres professions liées aux calculs économiques. Ils peuvent être utiles même pour les utilisateurs ordinaires d’ordinateurs personnels.

Pour utiliser l’une de ces fonctions, vous devez sélectionner la section appropriée dans l’assistant de fonction et cliquer sur le bouton “OK”.

Dans la liste apparue des fonctions financières, sélectionnez la fonction souhaitée et cliquez sur le bouton “OK”.

Ensuite, dans une nouvelle fenêtre, spécifiez les arguments de la fonction et cliquez sur OK.

Considérons un exemple d’utilisation d’une fonction financière. L’un des éléments les plus recherchés de cette section est la fonction YIELD. Il calcule la rentabilité des titres pour lesquels les intérêts sont payés selon une certaine périodicité. Comment utiliser la fonction YIELD?

Sélectionnez la cellule dans laquelle la valeur de cette fonction va résider. Trouvez le YIELD dans la liste des fonctions financières et sélectionnez-le.
Dans la fenêtre des arguments, cliquez dans le champ du premier argument, puis cliquez sur la cellule avec sa valeur. La valeur de cette cellule apparaîtra dans le champ d’argument. De même, remplissez les champs restants.
Après avoir cliqué sur le bouton “OK” dans la cellule “YIELD”, la valeur calculée de cette fonction apparaît.

Les fonctions financières les plus couramment utilisées sont:

FV. Trouve la valeur future de l’investissement.
IRR. Taux de rendement interne.
MIRR. Taux de rendement interne modifié.
IMPT. Les paiements d’intérêts pour la période spécifiée.
PV. Valeur présentée des investissements.
RATE. Calcule le taux d’intérêt sur une annuité.
EFFECT. Le taux d’intérêt réel.

Fonctions de date dans Excel

Les fonctions de date et d’heure sont très pratiques à utiliser lorsque certaines manipulations avec intervalles de temps sont nécessaires. Par exemple, lorsque vous devez calculer le nombre de jours d’une date à une autre, déterminez le jour de la semaine auquel correspond la date spécifiée, ajoutez quelques jours ou mois à la date spécifiée, et bien plus encore. Les fonctions de date et d’heure sont plusieurs dizaines. Considérons dans l’exemple le plus simple comment de telles fonctions sont utilisées.

Il y a un tableau avec les colonnes “Year”, “Month”, “Day”. Il est nécessaire d’ajouter une colonne avec une date au format dd.mm.yyyy.

Cliquez sur la cellule Date et appelez l’assistant de fonction. Sélectionnez la catégorie “Date & Time”, et dans la liste des fonctions, sélectionnez “Date”.
Dans la fenêtre des arguments, entrez les adresses des cellules “Year”, “Month”, “Day”. Et cliquez sur OK.
Dans la cellule “Date” la date apparaîtra dans le format requis. Pour que la date apparaisse dans les cellules restantes de la colonne, il suffit de copier cette cellule en déplaçant la souris sur le coin inférieur droit.

Voici quelques fonctions plus fréquemment utilisées dans cette section.

DAY. Renvoie le jour à partir de la date spécifiée.
EDATE. Compte la date dans le nombre de mois spécifié. Si vous spécifiez une valeur négative pour l’argument Month, la fonction calculera la date qui précède celle spécifiée.
EOMONTH. Affiche la date du dernier jour du mois spécifié.
HOUR. Convertit le nombre spécifié en horloge ou sélectionne uniquement des heures à partir de la date sélectionnée.
NETWORKDAYS. Compte le nombre de jours ouvrables entre les deux dates spécifiées.

Fonctions mathématiques Excel

La section des fonctions mathématiques couvre non seulement les calculs mathématiques, mais aussi les calculs trigonométriques. C’est probablement la fonction la plus utilisée. L’une des fonctions mathématiques les plus célèbres est la fonction SUM. Considérons un exemple d’utilisation de cette fonction, lorsque vous devez calculer la quantité de l’une des colonnes de la table.

Sélectionnez la cellule où sera situé le montant requis, allez dans l’assistant de fonction et sélectionnez la catégorie “Math & Trig”.

Sélectionnez la fonction SUM dans cette catégorie et cliquez sur OK.

Tapez les arguments appropriés et cliquez sur OK. Dans la cellule sélectionnée, la valeur de la somme du contenu des cellules dans la plage spécifiée apparaît.

Les fonctions mathématiques couramment utilisées sont les suivantes:

SUMIF. Compte le montant dans les cellules indiquées, mais sous réserve des conditions spécifiées.
ROUND. Affiche la valeur numérique arrondie au nombre de caractères spécifié. S’applique uniquement aux cellules individuelles, pas à une plage de cellules.
PRODUCT. Compte le produit de nombres individuels ou le contenu des cellules spécifiées.
RANDBETWEEN. Affiche un nombre aléatoire dans la plage entre les valeurs spécifiées.
ROMAN. Convertit les chiffres arabes dans les cellules indiquées en chiffres romains.

Fonctions statistiques Excel

Comme son nom l’indique, les fonctions statistiques sont destinées à l’analyse statistique des données dans des tableaux Excel. Une grande partie de ces fonctions est utilisée pour l’analyse des probabilités. Peut-être, ces fonctions sembleront très compliquées, mais parmi elles il y a quelques fonctions assez simples que même les utilisateurs ordinaires peuvent utiliser dans leur travail.

AVERAGE. La fonction est conçue pour calculer la valeur moyenne dans les plages de cellules. De plus, il peut y avoir plusieurs plages. Dans les calculs, des plages et des cellules non contiguës peuvent également être utilisées.
AVERAGEIF. La fonction est similaire à la précédente, mais les valeurs de calcul de la moyenne sont sélectionnées en fonction de la condition spécifiée.
MAX. Trouve la valeur maximale dans la plage de cellules spécifiée.
LARGE. Trouve la valeur spécifiée dans la plage de cellules. Par exemple, vous pouvez trouver la deuxième plus grande valeur ou la dixième plus grande valeur de la liste.
MODE. Cette fonction trouve la valeur la plus courante dans le tableau de données spécifié.

Travailler avec des tableaux Excel

Ce groupe de fonctions vous permet de traiter les données d’un tableau. Le résultat de ces fonctions peut être une valeur ou un tableau de valeurs. Pour aller à la liste des fonctions pour travailler avec des tableaux, vous devez aller dans la catégorie “Lookup & Reference” de l’assistant de fonction. Les fonctions de tableau sont difficiles à comprendre, mais dans certains cas, elles peuvent être le seul moyen de résoudre des tâches Excel complexes sans utiliser une variété de macros et de formules longues.

Par exemple, nous discuterons de l’utilisation de la fonction TRANSPOSE. Il convertit la plage horizontale des cellules en verticale et vice versa. La première ligne du tableau devient la première colonne du nouveau tableau et ainsi de suite.

Par exemple, il y a un tableau avec les lignes January, February, March et le nombre de jours dans chaque mois. Il est nécessaire de le transformer afin que les noms des mois passent horizontalement.

Sélectionnez le tableau de cellules vides avec trois colonnes et deux lignes.
Ouvrez l’assistant de fonction, trouvez la catégorie “Lookup & Reference” et sélectionnez la fonction “TRANSPOSE”. Cliquez sur OK
Sélectionnez tous les éléments du tableau initial avec la souris. Cliquez sur OK.
Pour que la fonction démarre, cliquez sur la souris dans la barre de formule et appuyez sur CTRL + SHIFT + ENTER. Ce raccourci devrait mettre fin à toute fonction pour travailler avec des tableaux.
La nouvelle table ressemblera à ceci.

Fonctions des bases de données Excel

La base de données dans Excel est, en fait, des données ordonnées. Afin de travailler avec ces fonctions, les tables doivent satisfaire plusieurs conditions.

La table ne doit pas contenir de lignes ou de colonnes vides. Vous ne pouvez pas utiliser des cellules fusionnées.
Chaque colonne doit avoir un en-tête. L’en-tête ne doit pas contenir de cellules vides ou fusionnées.
Les données de chaque colonne doivent être strictement du même type. C’est-à-dire soit numérique, soit textuelle, soit de date et ainsi de suite. Les cellules numériques ne peuvent pas être vides. En l’absence de sens, zéro est mis là.

Pour utiliser les fonctions de base de données, vous pouvez, par exemple, utiliser le tableau suivant:

Chacune des fonctions pour travailler avec des bases de données utilise trois arguments:

Base de données C’est un complexe de cellules interconnectées, où les lignes sont des enregistrements de la table de base de données, et les colonnes sont des champs.
Champ C’est la colonne de la table qui utilise la fonction à calculer. Il peut s’agir soit de son nom entre guillemets (par exemple, “Price”), soit du numéro de cette colonne dans l’ordre (par exemple, 3 pour la colonne “Price”).
Critère. C’est l’intervalle des cellules avec des conditions données. Il doit contenir au moins un nom de colonne et une cellule avec la condition en dessous.

Un exemple de fonction de base de données est la fonction DCOUNT. En l’utilisant, vous pouvez compter les lignes dans la table de base de données qui satisfont les conditions spécifiées. Cette fonction est pratique à utiliser en présence de critères complexes basés sur des formules.

Fonctions de texte Excel

Les fonctions de texte, comme son nom l’indique, sont conçues pour fonctionner avec les données de texte des tables Excel. Mais ces fonctions peuvent être utilisées dans le travail avec des données numériques. Considérez le travail de l’une des fonctions de texte dans un exemple simple.

Il y a une table où le contenu des cellules est écrit en majuscules. Vous devez les convertir en minuscules.

Allez dans l’assistant de fonction et sélectionnez la catégorie “Text”. Sélectionnez la fonction LOWER et appuyez sur “OK”.
Dans les arguments de la fonction qui apparaissent, spécifiez la cellule dont vous voulez convertir le contenu, puis cliquez sur OK.

Il existe de nombreuses autres fonctions pour travailler avec des données texte.

CONCATENATE. Combine le contenu de plusieurs cellules spécifiées dans une cellule.
UPPER. Rend toutes les lettres de la cellule spécifiée en majuscules.
PROPER. Capitalise seulement la première lettre.
EXACT. Compare le contenu de deux chaînes de texte en fonction de la casse. Si la valeur est la même, la fonction renvoie “TRUE”. Sinon – “FALSE”.
TRIM. Supprime tous les espaces supplémentaires sauf pour les espaces simples.

Fonctions logiques Excel

Les fonctions logiques sont l’une des fonctions les plus courantes d’Excel. Souvent, ils sont utilisés lors de calculs financiers. Les fonctions logiques vérifient la validité des conditions données et, si les conditions sont remplies, la fonction produit la valeur “True”, et sinon “False”. Tous dans leur travail utilisent des signes de comparaison, tels que “=”, “<“, “>” et ainsi de suite.

Pour utiliser les fonctions logiques dans le travail, vous devez aller dans l’assistant de fonction et sélectionner la catégorie “Logical”.
Considérons brièvement quelques-uns d’entre eux.

IF. Cette fonction renvoie un résultat si la condition spécifiée est true et un autre résultat si false.
TRUE. Cette fonction est utilisée uniquement en conjonction avec d’autres fonctions. Il renvoie uniquement l’expression logique “True” et n’a aucun argument.
FALSE. Similaire à la fonction TRUE, renvoie uniquement l’expression logique “False”.
NOT. Modifie la valeur logique obtenue par le sens opposé, en remplaçant “True” par “False” et vice versa.

Ainsi, en utilisant MS Excel, vous pouvez créer des rapports et des documents financiers, mathématiques, statistiques et autres. L’application vous permet non seulement de générer ces rapports, mais également de les chiffrer en toute sécurité. Les experts recommandent fortement de mettre une protection contre les accès non autorisés aux fichiers critiques. Même si vous perdez par la suite la clé des données cryptées, vous pouvez facilement retirer mot de passe Excel oublié à l’aide de programmes spéciaux.