Les formules et les fonctions sont les éléments de base du travail avec des données numériques dans Excel. Cet article vous présente les formules et les fonctions.
Dans cet article, nous aborderons les sujets suivants.
- Qu'est-ce que les formules dans Excel?
- Erreurs à éviter lorsque vous travaillez avec des formules dans Excel
- Qu'est-ce que la fonction dans Excel?
- L'importance des fonctions
- Fonctions communes
- Fonctions numériques
- Fonctions de chaîne
- Fonctions de date et d'heure
- Fonction de recherche V
Données des didacticiels
Pour ce didacticiel, nous travaillerons avec les ensembles de données suivants.
Budget des fournitures pour la maison
| S / N | OBJET | QTÉ | LE PRIX | TOTAL | Est-ce abordable? |
|---|---|---|---|---|---|
| 1 | Mangues | 9 | 600 | ||
| 2 | Des 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 maison
| S / N | OBJET | DATE DE DÉBUT | DATE DE FIN | DURÉE (JOURS) |
|---|---|---|---|---|
| 1 | Arpenter les terres | 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?
FORMULAS IN 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 trouve 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 cellule A2 et D2 puis multiplie les valeurs trouvées dans ces adresses de cellule."/"est l'opérateur arithmétique de division"2"est une valeur discrète
Exercice pratique de formules
Nous travaillerons avec les exemples de données pour le budget du logement 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 doit ressembler à ceci.
Nous allons maintenant écrire la formule qui calcule le sous-total
Définissez le focus sur la cellule E4
Entrez la formule suivante.
=C4*D4
ICI,
"C4*D4"utilise la multiplication d'opérateur arithmétique (*) 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 à d'autres lignes.
Erreurs à éviter lorsque vous travaillez avec des formules dans Excel
- Rappelez-vous les règles des crochets de division, multiplication, addition et soustraction (BODMAS). Cela signifie que les expressions entre crochets sont évaluées en premier. Pour les opérateurs arithmétiques, la division est d'abord évaluée, suivie d'une 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 de feuille 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 doit être accepté par une cellule, c'est-à-dire des nombres uniquement.
- Pour vous assurer que vous travaillez avec les adresses de cellule 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 pouvez contre-vérifier pour vous assurer qu'il s'agit des adresses de cellule souhaitées.
- Lorsque vous travaillez avec de nombreuses 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.
Découvrez les 10 meilleures formules de feuille de calcul Excel
Qu'est-ce que la fonction dans Excel?
FUNCTION IN EXCEL est une formule prédéfinie qui est 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 SOMME qui calcule la somme de la plage A1: A2.
- SUM pour la somme d'une plage de nombres
- MOYENNE pour calculer la moyenne d'une plage donnée de nombres
- 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 . Supposons que vous souhaitiez 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. En utilisant une formule, vous devrez référencer les cellules E4 à E8 une par une. Vous devrez 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 à partir de 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.
| S / N | FONCTION | CATÉGORIE | LA DESCRIPTION | USAGE |
|---|---|---|---|---|
| 01 | SOMME | Maths et trig | Ajoute toutes les valeurs dans une plage de cellules | = SOMME (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 | MOYEN | Statistique | Calcule la valeur moyenne dans une plage de cellules | = MOYENNE (E4: E8) |
| 05 | COMPTER | Statistique | Compte le nombre de cellules dans une plage de cellules | = NOMBRE (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, [plage_somme]) | = SUMIF (D4: D8, "> = 1000", C4: C8) |
| 08 | MOYENNEIF | Statistique | Calcule la valeur moyenne dans une plage de cellules qui répondent aux critères spécifiés. = AVERAGEIF (plage, critères, [plage_moyenne]) | = MOYENNEIF (F4: F8, "Oui", E4: E8) |
| 09 | JOURS | Date et heure | Renvoie le nombre de jours entre deux dates | = JOURS (J4, C4) |
| dix | À PRÉSENT | Date et heure | Renvoie la date et l'heure actuelles du système | = MAINTENANT () |
Fonctions numériques
Comme son nom l'indique, ces fonctions opèrent sur des données numériques. Le tableau suivant présente certaines des fonctions numériques courantes.
| S / N | FONCTION | CATÉGORIE | LA DESCRIPTION | USAGE |
|---|---|---|---|---|
| 1 | NOMBRE | Informations | Renvoie True si la valeur fournie est numérique et False si elle n'est pas numérique | = NOMBRE (A3) |
| 2 | RAND | Maths et trig | Génère un nombre aléatoire entre 0 et 1 | = RAND () |
| 3 | ROND | Maths et trig | Arrondit une valeur décimale au nombre spécifié de points décimaux | = ROND (3.14455,2) |
| 4 | MÉDIAN | Statistique | Renvoie le nombre au milieu de l'ensemble de nombres donnés | = MÉDIAN (3,4,5,2,5) |
| 5 | PI | Maths et trig | Renvoie la valeur de la fonction mathématique PI (π) | = PI () |
| 6 | PUISSANCE | 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 | ROMAIN | 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 textuelles. Le tableau suivant présente certaines des fonctions de chaîne courantes.
| S / N | FONCTION | CATÉGORIE | LA DESCRIPTION | USAGE | COMMENTER |
|---|---|---|---|---|---|
| 1 | GAUCHE | Texte | Renvoie un certain nombre de caractères spécifiés à partir du début (côté gauche) d'une chaîne | = GAUCHE ("GURU99", 4) | Gauche 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 caractères de "GURU99" |
| 3 | MILIEU | Texte | Récupère un certain nombre de caractères au milieu d'une chaîne à partir d'une position de début et d'une longueur spécifiées. = MID (texte, numéro_début, nombre_carres) | = MID ("GURU99", 2,3) | Récupération des caractères 2 à 5 |
| 4 | ISTEXTE | Informations | Renvoie True si le paramètre fourni est Text | = ISTEXT (valeur) | value - La valeur à vérifier. |
| 5 | TROUVER | 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. = FIND (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. = REMPLACER (ancien_texte, numéro_début, num_chars, nouveau_texte) | = REMPLACER ("Toiture", 2,2, "xx") | Remplacez "oo" par "xx" |
Fonctions de date et d'heure
Ces fonctions sont utilisées pour manipuler les valeurs de date. Le tableau suivant présente certaines des fonctions de date courantes
| S / N | FONCTION | CATÉGORIE | LA DESCRIPTION | USAGE |
|---|---|---|---|---|
| 1 | DATE | Date et heure | Renvoie le nombre qui représente la date en code Excel | = DATE (2015,2,4) |
| 2 | JOURS | Date et heure | Trouvez le nombre de jours entre deux dates | = JOURS (J6, C6) |
| 3 | MOIS | Date et heure | Renvoie le mois à partir d'une valeur de date | = MOIS ("02/04/2015") |
| 4 | MINUTE | Date et heure | Renvoie les minutes d'une valeur de temps | = MINUTE ("12:31") |
| 5 | AN | Date et heure | Renvoie l'année à partir d'une valeur de date | = ANNEE ("04/02/2015") |
Fonction RECHERCHEV
La fonction RECHERCHEV est utilisée 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 minimale.
Voici une liste des formules et fonctions Excel importantes
- Fonction SOMME =
=SUM(E4:E8)
- Fonction MIN =
=MIN(E4:E8)
- Fonction MAX =
=MAX(E4:E8)
- Fonction MOYENNE =
=AVERAGE(E4:E8)
- Fonction COUNT =
=COUNT(E4:E8)
- Fonction JOURS =
=DAYS(D4,C4)
- Fonction RECHERCHEV =
=VLOOKUP (C12, A4:B8, 2, FALSE)
- Fonction DATE =
=DATE(2020,2,4)