Formules et fonctions Excel : apprenez avec des EXEMPLES de base
Charlotte Miller
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 didacticiel, nous travaillerons avec les ensembles de données suivants.
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 | Poser Foundation | 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.
Nous allons maintenant écrire la formule qui calcule le sous-total
Mettre le focus sur la cellule E4
Entrez la formule suivante.
=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 résultat suivant
L'image animée suivante vous montre comment sélectionner automatiquement l'adresse de cellule et appliquer la même formule aux autres lignes.
Erreurs à éviter lorsque vous travaillez avec des formules dans Excel
- Rappelez-vous les règles de Brackets de division, multiplication, addition et soustraction (BODMAS). Cela signifie que les expressions entre parenthèses sont évaluées 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.
- 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.
- 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.
- 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.
Validez votre panier
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. Il faudrait utiliser la formule suivante.
= 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 tableau suivant présente certaines des fonctions numériques courantes.
| Ratio S / N | FONCTION | CATÉGORIE | DESCRIPTION | UTILISATION |
|---|---|---|---|---|
| 1 | ISNUMBER | Info | 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 tableau suivant présente 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 | Info | 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 tableau suivant présente 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
La 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.
=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
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)
