Formules et fonctions Excel : apprenez avec des EXEMPLES de base

Les formules et les fonctions sont les éléments constitutifs du travail avec des données numériques dans Excel. Cet article vous présente les formules et les fonctions.

Données des didacticiels

Pour ce tutoriel, nous travaillerons avec le suivantwing jeux de données.

Budget fournitures pour la maison

Ratio S / N ARTICLE QTÉ PRIX TOTAL Est-ce abordable?
1 Mangues 9 600
2 Oranges 3 1200
3 Tomates 1 2500
4 Huile de cuisson 5 6500
5 L'eau tonique 13 3900

Calendrier du projet de construction de maisons

Ratio S / N ARTICLE DATE DE DÉBUT DATE DE FIN DURÉE (JOURS)
1 Terrain d'arpentage 04/02/2015 07/02/2015
2 Fondation laïque 10/02/2015 15/02/2015
3 Toiture 27/02/2015 03/03/2015
4 Peinture 09/03/2015 21/03/2015

Qu’est-ce que les formules dans Excel ?

FORMULES EN EXCEL est une expression qui opère sur des valeurs dans une plage d'adresses de cellules et d'opérateurs. Par exemple, =A1+A2+A3, qui recherche la somme de la plage de valeurs de la cellule A1 à la cellule A3. Un exemple de formule composée de valeurs discrètes comme =6*3.

=A2 * D2 / 2

ICI,

  • "=" indique à Excel qu'il s'agit d'une formule et qu'il doit l'évaluer.
  • "A2" * D2" fait référence aux adresses de cellules A2 et D2 puis multiplie les valeurs trouvées dans ces adresses de cellules.
  • "/" est l'opérateur arithmétique de division
  • "2" est une valeur discrète

Exercice pratique sur les formules

Nous travaillerons avec les exemples de données du budget de la maison pour calculer le sous-total.

  • Créer un nouveau classeur dans Excel
  • Entrez les données indiquées dans le budget des fournitures pour la maison ci-dessus.
  • Votre feuille de calcul devrait ressembler à ceci.

Exercice pratique de formules

Nous allons maintenant écrire la formule qui calcule le sous-total

Mettre le focus sur la cellule E4

Entrez le suiviwing formule.

=C4*D4

ICI,

  • "C4*D4" utilise l'opérateur arithmétique multiplication (*) pour multiplier la valeur de l'adresse de cellule C4 et D4.

Appuyez sur la touche Entrée

Vous obtiendrez le suiviwing résultat

Exercice pratique de formules

Le following L'image animée vous montre comment sélectionner automatiquement l'adresse de cellule et appliquer la même formule aux autres lignes.

Exercice pratique de formules

Erreurs à éviter lorsque vous travaillez avec des formules dans Excel

  1. Rappelez-vous les règles de Brackets de division, multiplication, addition et soustraction (BODMAS). Cela signifie que les expressions sont brackets sont évalués en premier. Pour les opérateurs arithmétiques, la division est évaluée en premier, suivie de la multiplication, puis l'addition et la soustraction sont les dernières à être évaluées. En utilisant cette règle, nous pouvons réécrire la formule ci-dessus comme =(A2 * D2) / 2. Cela garantira que A2 et D2 sont d'abord évalués puis divisés par deux.
  2. Les formules des feuilles de calcul Excel fonctionnent généralement avec des données numériques ; vous pouvez profiter de la validation des données pour spécifier le type de données qui doivent être acceptées par une cellule, c'est-à-dire uniquement des nombres.
  3. Pour vous assurer que vous travaillez avec les adresses de cellules correctes référencées dans les formules, vous pouvez appuyer sur F2 sur le clavier. Cela mettra en évidence les adresses de cellule utilisées dans la formule et vous pourrez vérifier par recoupement pour vous assurer qu'il s'agit bien des adresses de cellule souhaitées.
  4. Lorsque vous travaillez avec plusieurs lignes, vous pouvez utiliser des numéros de série pour toutes les lignes et avoir un nombre d'enregistrements au bas de la feuille. Vous devez comparer le nombre de numéros de série avec le total de l'enregistrement pour vous assurer que vos formules incluent toutes les lignes.

Date du départ
Top 10 des formules de feuille de calcul Excel

Qu’est-ce que la fonction dans Excel ?

FONCTION DANS EXCEL est une formule prédéfinie utilisée pour des valeurs spécifiques dans un ordre particulier. La fonction est utilisée pour des tâches rapides telles que la recherche de la somme, du nombre, de la moyenne, de la valeur maximale et des valeurs minimales pour une plage de cellules. Par exemple, la cellule A3 ci-dessous contient la fonction SUM qui calcule la somme de la plage A1:A2.

  • SUM pour la sommation d'une plage de nombres
  • MOYENNE pour calculer la moyenne d'une plage de nombres donnée
  • COUNT pour compter le nombre d'éléments dans une plage donnée

L'importance des fonctions

Les fonctions augmentent la productivité des utilisateurs lorsqu'ils travaillent avec Excel. Disons que vous souhaitez obtenir le total général du budget de fournitures pour la maison ci-dessus. Pour simplifier les choses, vous pouvez utiliser une formule pour obtenir le total général. À l’aide d’une formule, vous devrez référencer les cellules E4 à E8 une par une. Vous devrez utiliser le suivantwing formule.

= E4 + E5 + E6 + E7 + E8

Avec une fonction, vous écririez la formule ci-dessus comme

=SUM (E4:E8)

Comme vous pouvez le voir avec la fonction ci-dessus utilisée pour obtenir la somme d'une plage de cellules, il est beaucoup plus efficace d'utiliser une fonction pour obtenir la somme que d'utiliser la formule qui devra référencer un grand nombre de cellules.

Fonctions communes

Examinons certaines des fonctions les plus couramment utilisées dans les formules MS Excel. Nous commencerons par les fonctions statistiques.

Ratio S / N FONCTION CATÉGORIE DESCRIPTION UTILISATION
01 SUM Maths et trig Ajoute toutes les valeurs dans une plage de cellules = SUM (E4: E8)
02 MIN Statistique Recherche la valeur minimale dans une plage de cellules =MIN(E4:E8)
03 MAX Statistique Recherche la valeur maximale dans une plage de cellules =MAX(E4:E8)
04 MOYENNE Statistique Calcule la valeur moyenne dans une plage de cellules = MOYENNE (E4: E8)
05 COUNT Statistique Compte le nombre de cellules dans une plage de cellules = COUNT (E4: E8)
06 LEN Texte Renvoie le nombre de caractères dans une chaîne de texte = LEN (B7)
07 SUMIF Maths et trig Ajoute toutes les valeurs d'une plage de cellules qui répondent à un critère spécifié.
=SUMIF(plage,critères,[sum_range])
=SUMIF(D4:D8,”>=1000″,C4:C8)
08 AVERAGEIF Statistique Calcule la valeur moyenne dans une plage de cellules répondant aux critères spécifiés.
= MOYENNEIF (plage, critères, [plage_moyenne])
= MOYENNE SI (F4:F8, "Oui", E4:E8)
09 JOURS Date & Heure Renvoie le nombre de jours entre deux dates =JOURS(J4,C4)
10 MAINTENANT Date & Heure Renvoie la date et l'heure actuelles du système = MAINTENANT ()

Fonctions numériques

Comme leur nom l'indique, ces fonctions opèrent sur des données numériques. Le suiviwing Le tableau présente certaines des fonctions numériques courantes.

Ratio S / N FONCTION CATÉGORIE DESCRIPTION UTILISATION
1 ISNUMBER Informations Renvoie True si la valeur fournie est numérique et False si elle n'est pas numérique =ESTNUM(A3)
2 RAND Maths et trig Génère un nombre aléatoire entre 0 et 1 = RAND ()
3 ROUND Maths et trig Arrondit une valeur décimale au nombre spécifié de points décimaux =ROND(3.14455,2)
4 MEDIAN Statistique Renvoie le nombre au milieu de l'ensemble de nombres donnés =MÉDIANE(3,4,5,2,5)
5 PI Maths et trig Renvoie la valeur de la fonction mathématique PI(π) =IP()
6 RENFORCER Maths et trig Renvoie le résultat d'un nombre élevé à une puissance.
PUISSANCE( nombre, puissance)
=PUISSANCE(2,4)
7 MOD Maths et trig Renvoie le reste lorsque vous divisez deux nombres =MOD(10,3)
8 ROMAN Maths et trig Convertit un nombre en chiffres romains = ROMAIN (1984)

Fonctions de chaîne

Ces fonctions Excel de base sont utilisées pour manipuler des données texte. Le suiviwing Le tableau montre certaines des fonctions de chaîne courantes.

Ratio S / N FONCTION CATÉGORIE DESCRIPTION UTILISATION COMMENTAIRE
1 en stock Texte Renvoie un nombre de caractères spécifiés à partir du début (côté gauche) d'une chaîne =GAUCHE("GURU99",4) Il reste 4 caractères de « GURU99 »
2 DROIT Texte Renvoie un nombre de caractères spécifiés à partir de la fin (côté droit) d'une chaîne =DROITE("GURU99",2) À droite 2 personnages de « GURU99 »
3 MID Texte Récupère un certain nombre de caractères au milieu d'une chaîne à partir d'une position de départ et d'une longueur spécifiées.
=MID (texte, start_num, num_chars)
=MID("GURU99",2,3) Récupération des personnages 2 à 5
4 ISTEXT Informations Renvoie True si le paramètre fourni est Text =ISTEXT(valeur) value – La valeur à vérifier.
5 IDENTIFIER Texte Renvoie la position de départ d'une chaîne de texte dans une autre chaîne de texte. Cette fonction est sensible à la casse.
=TROUVER(find_text, Within_text, [start_num])
=TROUVER(« oo », « Toiture »,1) Trouvez oo dans « Toiture », le résultat est 2
6 REMPLACER Texte Remplace une partie d'une chaîne par une autre chaîne spécifiée.
=REPLACE (old_text, start_num, num_chars, new_text)
=REPLACE("Toiture",2,2,"xx") Remplacez « oo » par « xx »

Fonctions date et heure

Ces fonctions sont utilisées pour manipuler les valeurs de date. Le suiviwing le tableau montre certaines des fonctions de date courantes

Ratio S / N FONCTION CATÉGORIE DESCRIPTION UTILISATION
1 DATES Date & Heure Renvoie le nombre qui représente la date dans le code Excel = DATE (2015,2,4)
2 JOURS Date & Heure Trouver le nombre de jours entre deux dates =JOURS(J6,C6)
3 MOIS Date & Heure Renvoie le mois à partir d'une valeur de date =MOIS(« 4/2/2015 »)
4 MINUTE Date & Heure Renvoie les minutes à partir d'une valeur temporelle =MINUTE(« 12:31 »)
5 ANNÉE Date & Heure Renvoie l'année à partir d'une valeur de date =ANNÉE(«04/02/2015»)

Fonction RECHERCHEV

Le Fonction RECHERCHEV est utilisé pour effectuer une recherche verticale dans la colonne la plus à gauche et renvoyer une valeur dans la même ligne à partir d'une colonne que vous spécifiez. Expliquons cela dans un langage profane. Le budget des fournitures pour la maison comporte une colonne de numéro de série qui identifie de manière unique chaque élément du budget. Supposons que vous ayez le numéro de série de l'article et que vous souhaitiez connaître la description de l'article, vous pouvez utiliser la fonction RECHERCHEV. Voici comment fonctionnerait la fonction RECHERCHEV.

Fonction RECHERCHEV

=VLOOKUP (C12, A4:B8, 2, FALSE)

ICI,

  • "=VLOOKUP" appelle la fonction de recherche verticale
  • "C12" spécifie la valeur à rechercher dans la colonne la plus à gauche
  • "A4:B8" spécifie le tableau de table avec les données
  • "2" spécifie le numéro de colonne avec la valeur de ligne à renvoyer par la fonction RECHERCHEV
  • "FALSE," indique à la fonction RECHERCHEV que nous recherchons une correspondance exacte de la valeur de recherche fournie

L'image animée ci-dessous montre cela en action

Fonction RECHERCHEV

Téléchargez le code Excel ci-dessus

Résumé

Excel vous permet de manipuler les données à l'aide de formules et/ou de fonctions. Les fonctions sont généralement plus productives que l’écriture de formules. Les fonctions sont également plus précises que les formules car la marge d’erreur est très minime.

Voici une liste des formules et fonctions Excel importantes

  • Fonction SOMME = =SUM(E4:E8)
  • Fonction MINI = =MIN(E4:E8)
  • Fonction MAX = =MAX(E4:E8)
  • Fonction MOYENNE = =AVERAGE(E4:E8)
  • Fonction COMPTE = =COUNT(E4:E8)
  • Fonction JOURS = =DAYS(D4,C4)
  • Fonction RECHERCHEV = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • Fonction DATE = =DATE(2020,2,4)