Article original : Microsoft Excel Tutorial – How to Create Formulas and Functions
Les feuilles de calcul ne servent pas uniquement à organiser des données en lignes et en colonnes. La plupart du temps, vous les utilisez également pour l'analyse de données.
Microsoft Excel est l'une des applications de feuille de calcul les plus largement utilisées, en particulier dans les domaines de la finance et de la comptabilité. Cela est en partie dû à son interface utilisateur facile à utiliser et à la profondeur inégalée de ses fonctions.
Dans cet article, vous apprendrez :
- Ce que sont les formules Excel
- Comment écrire une formule dans Excel
- Ce que sont les fonctions Excel
- Comment travailler avec une fonction Excel
- Enfin, nous examinerons les fonctions dynamiques d'Excel.
Que dois-je installer sur mon ordinateur pour suivre cet article ?
Pour suivre cet article, vous devez avoir Microsoft Excel installé sur votre ordinateur. Nous utiliserons un ordinateur Windows pour cet article.
Comment puis-je installer Microsoft Excel sur mon ordinateur ?
Suivez ces étapes pour installer Microsoft Excel sur votre ordinateur Windows :
- Connectez-vous à www.office.com si vous n'êtes pas déjà connecté.
- Connectez-vous avec le compte associé à votre abonnement Microsoft 365. Vous pouvez également essayer Office gratuitement.
- Une fois connecté, sélectionnez « Installer Office » depuis la page d'accueil d'Office. Cela téléchargera automatiquement Microsoft Office sur votre ordinateur Windows.
- Exécutez l'installateur pour configurer Microsoft Office et sélectionnez "Fermer" une fois terminé.
- Une fois terminé, sélectionnez le bouton "Démarrer" (situé dans le coin inférieur gauche de votre écran) et tapez "Microsoft Excel".
- Cliquez sur Microsoft Excel pour l'ouvrir.
- Acceptez le contrat de licence, et commençons.
Que sont les formules Excel ?
Une formule Excel est une expression qui effectue une opération basée sur la valeur d'une cellule ou d'une plage de cellules. Vous pouvez utiliser une formule Excel pour :
- Effectuer des opérations mathématiques simples telles que l'addition ou la soustraction.
- Effectuer une opération simple comme la concaténation de données catégorielles.
Il est important de comprendre deux choses : les formules Excel commencent toujours par le signe égal "=" et elles peuvent retourner une erreur si elles ne sont pas correctement exécutées.
Quels opérateurs sont utilisés dans les formules Excel ?
Il existe quatre types différents d'opérateurs dans Excel : arithmétiques, de comparaison, de concaténation de texte et de référence. Mais pour la plupart des formules, vous utiliserez généralement ces trois-là :
Opérateurs arithmétiques
+ | Addition |
- | Soustraction |
/ | Division |
* | Multiplication |
^ | Exponentiation |
Opérateurs de comparaison
= | Égal à |
> | Supérieur à |
< | Inférieur à |
>= | Supérieur ou égal à |
<= | Inférieur ou égal à |
<> | Différent de |
Concaténation de texte
Ici, vous avez simplement le signe esperluette "&" pour joindre du texte.
Comment puis-je créer une formule Excel ?
Prenons un scénario simple en utilisant l'un des opérateurs arithmétiques.
En mathématiques, pour additionner deux nombres, disons 20 et 30, vous calculerez cela en écrivant : 20 + 30 =
Et cela vous donnera 50.
Dans Excel, voici comment cela se passe :
- Tout d'abord, ouvrez une feuille de calcul Excel vierge.
- Dans la cellule A1, tapez 20.
- Dans la cellule A2, tapez 30.
- Pour les additionner, tapez = 20 + 30 dans la cellule A3.
- Ensuite, appuyez sur ENTRÉE sur votre clavier. Excel calculera instantanément cela et retournera 50.
J'ai mentionné précédemment que chaque formule commence par le signe égal "=". C'est ce que je voulais dire. Pour écrire une formule, vous tapez le signe égal suivi des valeurs numériques. Cela s'applique également aux cas de soustraction, de division, de multiplication et d'exponentiation.
Prenons un autre scénario simple en utilisant l'un des opérateurs de comparaison. Supposons que nous voulons savoir si 30 est supérieur à 40.
Dans Excel, voici comment nous le ferions :
- Tapez =30>40
- Appuyez sur ENTRÉE.
- Cela retournera FAUX car 30 n'est pas supérieur à 40. Excel utilise VRAI et FAUX pour les déclarations logiques, de la même manière que nous, humains, disons oui et non.
Enfin, prenons un autre scénario simple en utilisant l'opérateur de concaténation de texte – le signe esperluette "& ". Cela fonctionne avec vos types de données de chaîne et vous l'utilisez pour joindre du texte.
Supposons que nous avons "Bienvenue", "À", et "FreeCodeCamp" tous dans différentes cellules—A1, A2, et A3—de votre feuille de calcul. Nous taperions =A1&" "&A2&" "&A3 pour les joindre.
L'espace entre guillemets " " représente le fait que nous voulons un espace entre nos mots.
Un autre conseil : la barre de formule montre la formule utilisée pour générer une valeur.
Que sont les fonctions Excel ?
Les fonctions Excel sont des formules préétablies qui effectuent des calculs et des opérations mathématiques, statistiques et logiques en utilisant vos valeurs et arguments.
Pour les fonctions Excel, vous devez savoir que :
- Ce sont des formules, donc oui, elles commencent également par le signe égal "=".
- L'ordre est très important.
Il existe plus de 500 fonctions disponibles. Vous pouvez trouver toutes les fonctions Excel disponibles dans l'onglet Formules du Ruban.
Mais pourquoi utiliser une fonction lorsque vous pouvez simplement écrire une formule ?
Voici quelques avantages des fonctions Excel.
- Pour améliorer la productivité et l'efficacité.
- Pour simplifier les calculs complexes.
- Pour automatiser votre travail.
- Pour visualiser rapidement les données.
De quoi sont composées les fonctions Excel ?
Contrairement aux formules, les fonctions Excel sont composées d'une structure avec des arguments que vous devez passer.
Chaque fonction :
- Commence par le signe égal "="
- A un nom. Certains exemples sont RECHERCHEV, SOMME, UNIQUE, et RECHERCHEX.
- Nécessite des arguments qui sont séparés par des virgules. Vous devez savoir que les points-virgules sont utilisés comme séparateurs dans des pays comme l'Espagne, la France, l'Italie, les Pays-Bas et l'Allemagne. Vous pouvez, cependant, changer cela via les paramètres d'Excel.
- Les arguments entre crochets [] sont optionnels
- A une parenthèse ouvrante et fermante.
- A une info-bulle d'argument qui vous montre ce que vous devez passer.
Il existe quelques exceptions. Par exemple,
- La fonction DATEDIF ne s'affiche pas dans Excel car ce n'est pas une fonction standard et donne des résultats incorrects dans quelques circonstances. Cependant, voici la syntaxe :
DATEDIF(date_de_naissance, AUJOURDHUI(), "y")
- Les fonctions comme PI(), ALEA(), MAINTENANT(), AUJOURDHUI() ne nécessitent aucun argument.
Comment utiliser les fonctions Excel
Examinons quelques fonctions :
Comment utiliser la fonction SOMME() dans Excel
Selon la documentation, la fonction SOMME additionne les valeurs. Voici la syntaxe :
Syntaxe de la somme Excel
Supposons que nous avons une ligne de nombres de 1 à 10 et que nous voulons les additionner. Pour y parvenir, nous taperons simplement =SOMME(A1:A10). Le A1:A10 retourne simplement un tableau de nombres situés dans les cellules A1 à A10, c'est-à-dire A1, A2, et A3 jusqu'à A10.
Puisque le deuxième argument est optionnel, cela signifie qu'une somme(A10) retournera une valeur. Dans notre cas, elle retournera simplement 10 puisque A10 contient la valeur 10. Essayez-le.
Si vous écriviez cela en utilisant l'opérateur d'addition, vous auriez écrit :
=1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10
ou
=A1 + A2 + A3 + A4 + A5 + A6 + A7 + A8 + A9 + A10
Cela ne semble pas très productif ou efficace.
Comment utiliser la fonction AUJOURDHUI() dans Excel
Selon la documentation, la fonction AUJOURDHUI affiche la date actuelle sur votre feuille de calcul. Elle ne nécessite également aucun argument. Voici la syntaxe :
Excel affiche automatiquement la date actuelle selon les paramètres de date et d'heure de votre ordinateur. Il en va de même pour la fonction MAINTENANT(), qui affiche la date et l'heure actuelles.
Comment utiliser la fonction CONCATENER() dans Excel
Examinons une fonction de texte. Vous utilisez CONCATENER pour joindre deux ou plusieurs chaînes de texte en une seule chaîne.
Voici la syntaxe :
Syntaxe CONCATENER() d'Excel
Supposons que nous voulons joindre "Ceci est" avec "freeCodeCamp" – mais dans votre cellule, vous avez simplement "freeCodeCamp".
freeCodeCamp Excel
Si vous allez écrire une chaîne à l'intérieur d'une formule, vous devez l'écrire entre guillemets comme ceci " ".
Pourquoi ?
De cette manière, Excel ne pensera pas que vous essayez d'écrire une autre fonction.
Comment utiliser la fonction CONCATENER d'Excel
Cela retournera la phrase "Ceci est freeCodeCamp"
Comment utiliser CONCATENER() dans Excel
Comment utiliser la fonction RECHERCHEV() dans Excel
C'est l'une des formules ou fonctions les plus intéressantes et les plus couramment utilisées d'Excel. Vous l'utilisez pour trouver une valeur dans un tableau ou une plage par ligne.
Voici un scénario :
Nous avons un tableau simple qui montre divers films avec leur genre, studio principal, score du public %, rentabilité, pourcentage de tomates pourries, recettes mondiales et année. J'utiliserai simplement les 10 premières lignes des données d'exemple de ce GitHub Gist.
Je veux que, chaque fois que je tape un film dans la cellule jaune, l'année s'affiche dans la cellule verte. Utilisons RECHERCHEV pour le trouver.
Voici la syntaxe de RECHERCHEV :
Syntaxe Vlookup d'Excel
Outre l'écriture de vos formules dans la cellule, vous pouvez également les écrire en utilisant le bouton Insérer une fonction (fx) d'Excel, qui se trouve près de la barre de formule.
Essayons cela.
- Écrivez
=Vlookup(dans la cellule verte. - Cliquez sur fx. Une boîte de dialogue s'ouvrira montrant tous les arguments dont cette formule a besoin.
- Saisissez la valeur pour chaque argument.
- Lookup_value (argument requis) : C'est ce que vous voulez trouver. Dans notre cas, il s'agit du film "Youth in Revolt" qui se trouve dans la cellule B1.
- Table_array (argument requis) : Il vous demande simplement le tableau contenant les données. Vous lui donnez le tableau entier, qui dans notre cas est A4:H13
- Col_index_num (argument requis) : Il vous demande le numéro de colonne du tableau que vous avez donné. Dans notre cas, nous voulons l'année. Cela se trouve dans la colonne 8.
Range_lookup (argument optionnel) : Enfin, nous choisissons si nous voulons une correspondance approximative (VRAI) ou une correspondance exacte (FAUX).
VRAI signifie correspondance approximative, donc il retourne la valeur la plus proche ou une estimation.
FAUX signifie correspondance exacte, donc il retourne une erreur si elle n'est pas trouvée.
Nous opterons pour FAUX car nous voulons la correspondance exacte.
- Cliquez sur "OK." Excel retournera 2010.
Cependant, vous pouvez écrire cela dans la cellule en tapant =RECHERCHEV(B1,A4:H13,8,FAUX) dans votre cellule.
Conseils et règles lors de l'écriture de fonctions Excel
Lors de l'écriture de notre fonction, Excel fournit quelques conseils de formule.
- L'info-bulle d'argument ne disparaît pas tant que vous n'avez pas fermé la dernière parenthèse.
- La barre de formule montre votre formule.
- L'argument que vous êtes en train d'écrire est toujours en surbrillance. Jetez un œil à la valeur de recherche dans l'image ci-dessous.
- Les crochets [] vous indiquent qu'il est optionnel.
- Enfin, le code couleur – notre B1 est en bleu, et la cellule B1 est en bleu pour nous guider sur la cellule ou le tableau qui a été sélectionné. La même chose se produira pour A4:H13 lorsque nous le sélectionnerons comme argument table_array.
Comment travailler avec des fonctions imbriquées dans Excel
Une fonction imbriquée est lorsque vous écrivez une fonction dans une autre fonction. Par exemple, trouver la moyenne de la somme des valeurs.
Le premier conseil lors de l'écriture d'une fonction imbriquée sera de traiter chaque fonction individuellement. Donc, adressez la première fonction avant d'adresser la deuxième. Un conseil pro serait de regarder l'info-bulle d'argument lors de l'écriture.
Prenons un scénario simple.
Nous avons deux tableaux de nombres. Chacun contient les scores des étudiants de la classe. Je veux additionner les deux tableaux avant d'obtenir la moyenne.
Commençons.
- Tapez votre = suivi de la moyenne.
- Le premier nombre sera la somme des scores de la première classe.
- Le deuxième nombre sera la somme des scores de la deuxième classe.
Fonction imbriquée : Syntaxe MOYENNE et SOMME d'Excel
Enfin, n'oubliez pas la parenthèse fermante.
Ainsi, la formule sera =MOYENNE(SOMME(B3:B8),SOMME(D3:D8)).
Comment travailler avec les fonctions de tableau dynamique dans Excel
Les fonctions de tableau dynamique sont des formules associées au comportement de déversement de tableau.
Auparavant, vous écriviez une fonction et elle retournait une seule entrée. Nous appelons ces types de fonctions des formules de tableau héritées.
Les fonctions de tableau dynamique, en revanche, retourneront des valeurs qui entreront dans les cellules voisines. Voici quelques exemples de fonctions de tableau dynamique :
- UNIQUE
- TEXTE.DECALER
- FILTER
- SEQUENCE
- TRIER
- TRIERPAR
- ALEA.MATRICE
Examinons la formule UNIQUE.
Comment utiliser la formule UNIQUE() dans Excel
La formule unique fonctionne en retournant la valeur unique d'un tableau ou d'une liste. Utilisons les données d'exemple de film de ce GitHub Gist. Ce tableau contient 77 lignes de films, à l'exclusion de l'en-tête.
Essayons d'obtenir les années uniques de notre ensemble de données – c'est-à-dire les années sans doublons.
Pour ce faire :
- Tapez
=UNIQUE( - Sélectionnez l'ensemble du tableau de valeurs de la colonne année : =UNIQUE(H2:H78)
- Fermez la parenthèse et appuyez sur Entrée.
Bien que la formule ait été écrite dans une seule cellule, la valeur retournée s'est déversée dans les cellules en dessous. C'est le comportement de déversement de tableau.
Comment créer vos propres fonctions dans Excel
Microsoft Excel a publié un ensemble de nouvelles fonctions pour rendre les utilisateurs plus productifs. L'une de ces fonctions était la fonction LAMBDA.
La fonction LAMBDA vous permet de créer des fonctions personnalisées sans macros, VBA ou JavaScript, et de les réutiliser dans un classeur.
Le meilleur ? vous pouvez la nommer.
Comment utiliser la fonction LAMBDA() dans Excel
Cette fonction LAMBDA augmentera la productivité en éliminant le besoin de copier et coller cette formule, ce qui peut être sujet à des erreurs.
Voici la syntaxe LAMBDA :
Fonction LAMBDA d'Excel
Commençons par un cas d'utilisation simple en utilisant les données d'exemple de film de ce GitHub Gist.
L'ensemble de données de films
Nous avions une colonne appelée "Recettes mondiales", essayons de trouver la valeur en Naira.
- Créez une nouvelle colonne et appelez-la "Recettes mondiales en Naira".
- Juste en dessous du nom de notre colonne, Cellule I2, tapez
=lAMBDA( - LAMBDA nécessite un paramètre et/ou un calcul.
Le paramètre signifie la valeur que vous voulez passer, dans notre cas d'utilisation, nous voulons changer la valeur brute. Appelons cela brut.
Le calcul signifie la formule ou la fonction que vous voulez exécuter. Pour nous, ce sera de le multiplier par le taux de change. Pour le moment, c'est 670. Donc écrivons brut * 670.
Comment utiliser la fonction LAMBDA dans Excel
- Appuyez sur Entrée. Cela retournera une erreur car brut n'existe pas et vous devez faire savoir à Excel ces noms.
Utilisation de LAMBDA d'Excel
- Pour utiliser la fonction nouvellement créée, vous devez copier la syntaxe écrite.
Écriture de fonction avec LAMBDA d'Excel
- Allez dans le ruban de formule et ouvrez le gestionnaire de noms.
Gestionnaire de noms d'Excel utilisant la fonction LAMBDA
Définissez les paramètres du gestionnaire de noms :
Le nom est simplement ce que vous voulez appeler cette fonction. Je vais avec NairaConvert.
- La portée doit être le classeur car vous voulez utiliser cette fonction dans le classeur.
- Les commentaires expliquent ce que fait votre fonction. Cela agit comme une documentation.
- Dans référer à, vous devez coller la syntaxe de la fonction copiée.
Gestionnaire de noms d'Excel
Appuyez sur Ok.
Pour utiliser cette nouvelle fonction, vous l'appelez avec le nom que vous lui avez donné—NairaConvert—et vous lui donnez le brut qui est notre recette mondiale sur G2.
Fonction personnalisée avec LAMBDA
- Fermez la parenthèse et appuyez sur Ok
Calcul avec LAMBDA d'Excel
Où puis-je en apprendre davantage sur Excel ?
Il existe une multitude de ressources pour apprendre Microsoft Excel de nos jours. Tellement qu'il est difficile de déterminer lesquelles sont à jour et utiles.
La meilleure chose que vous puissiez faire est de trouver un tutoriel utile et de le suivre jusqu'à la fin, plutôt que d'essayer d'en suivre plusieurs à la fois. Je vous conseille de commencer par le Tutoriel Microsoft Excel pour débutants - Cours complet de freeCodeCamp, disponible sur YouTube.
Vous devriez également rejoindre des communautés comme la Communauté d'apprentissage Microsoft Excel et d'analyse de données. Cependant, si vous cherchez une compilation de ressources, consultez les tags Excel de la publication freeCodeCamp.
Si vous avez aimé lire cet article et/ou si vous avez des questions et souhaitez entrer en contact, vous pouvez me trouver sur LinkedIn ou Twitter.