Exercice 1 : Calcul des Écarts sur Volume et Prix Contexte...
Dans ce cours Excel, nous montrons les fonctions Excel les plus utilisées et les plus puissantes d’Excel.
cours gratuit Excel / fonctions Excel / les meilleures fonctions Excel
Cours écrit par : ZA
Ce cours Excel permettra d’augmenter le potentiel de votre travail, d’augmenter vos opérations et dissipera les doutes que vous avez pu avoir depuis que vous avez commencé à utiliser des feuilles de calcul. Êtes-vous prêt à découvrir quelles formules Excel vous utiliserez probablement le plus à partir de maintenant ?
Les principales fonctions d’Excel sont déjà implémentées par le logiciel lui-même. Cela signifie qu’ils sont disponibles et prêts à être utilisés dans votre tableur, que vous utilisiez le programme Microsoft, mais aussi si vous utilisez Calc depuis OpenOffice et LibreOffice.
Excel a des centaines de fonctions et de fonctions déjà implémentées et prêtes à l’emploi. A chaque fois que l’on ira appeler une fonction, celle-ci sera tapée dans la barre de formule et prendra comme arguments les valeurs contenues dans certaines cellules, généralement un nombre limité et prédéfini, pour aller déposer le résultat dans une cellule précise, ou dans plusieurs cellules si, par exemple, la formule est répliquée pour une colonne entière. Les formules couramment utilisées dont nous disposons sont :
Nous découvrons quelles autres formules nous pouvons utiliser dans la feuille de calcul.
Une méthode de trouver toutes les fonctions et de trouver celle qui convient le mieux à vos besoins est la suivante :
Il peut arriver que la formule que vous recherchez ne soit pas présente parmi celles déjà prêtes dans Excel. Dans ce cas, il est conseillé de contacter un programmeur expérimenté qui, avec l’aide de VBA, pourra créer le programme dont vous avez besoin. Pour des besoins spécifiques vous pouvez me contacter en utilisant la page me contacter de ce site.
La fonction SI peut effectuer un test logique et renvoyer une valeur en présence d’une variable booléenne dont la valeur est VRAI, et une autre valeur en présence d’une variable booléenne dont la valeur est FAUX.
Je sais que ce concept peut sembler, à première vue, difficile à comprendre, mais son application et son explication sont plus faciles qu’on ne le pense.
A titre d’exemple, supposons que vous souhaitiez écrire la chaîne “Passed” dans une cellule si la valeur de la cellule A1 est supérieure à 60, ou écrire “Failed” si la valeur de la cellule A1 est inférieure à 60. Pour ce faire, nous allons utilisez la formule suivante :
= SI (A1> 60, “Réussi”, “Échec”)
Nous allons donc aller dans n’importe quelle cellule (autre que A1 !) et écrire la formule suivante, que nous lirons comme ceci :
Si la valeur de A1 est supérieure à 60, imprimez “Pass”, sinon imprimez “Fail”
C’est l’équivalent de la construction conditionnelle if utilisée dans les langages de programmation. Évidemment, le terme print ne signifie pas qu’un terme est imprimé avec l’imprimante, mais que la chaîne “Passed” est affichée dans la cellule.
Vous voyez donc sur la figure que la formule SI a été saisie dans la barre de formule. Cela évaluera la valeur dans la cellule A1, puis imprimera ce qui lui a été demandé.
Maintenant que nous savons comment fonctionne la construction conditionnelle, élément essentiel si nous voulons donner une certaine “intelligence” au programme ou, plus précisément dans ce cas, au tableur que nous allons créer, nous pouvons passer à quelque chose de plus spécifique et complexe, la fonction COUNTIF .
Compter Si ( COUNTIF ), c’est une fonction pour compter les cellules qui répondent à certains critères. COUNTIF (..) peut être utilisé pour compter les cellules contenant des dates, des nombres, du texte, qui répondent à certains critères. La fonction COUNTIF prend en charge les opérateurs logiques :
Majeur, noté par>
Mineur, noté < Différent, <>
Égal, =
Si vous avez besoin de plus d’informations sur ce sujet, lisez le post sur les opérateurs logiques dans Excel et vous aurez ces concepts plus clairs.
La fonction SUMIF d’Excel impose une expression conditionnelle et si cela se produit, la somme ne sera effectuée que des éléments qui remplissent cette condition spécifique et les éléments qui ne la respectent pas seront rejetés.
La formule de somme conditionnelle est la suivante :
SUMIF (plage ; condition );
Où “plage” est la plage de cellules que vous souhaitez réellement ajouter. Il peut s’agir d’une colonne entière, d’une ligne entière ou de plusieurs colonnes et lignes. Le paramètre d’entrée “condition” est plutôt la condition que les cellules doivent respecter pour être sommées.
Exemple : Étant donné une liste de notes d’un élève de nombres allant de 0 à 10, on veut n’ajouter que les notes qui dépassent la suffisance (une opération bien étrange, mais qui donne en fait l’idée de l’opération que l’on s’apprête à réaliser en dehors).
Donc, étant donné une colonne, avec les notes des élèves, on va représenter dans une autre cellule, la somme des nombres supérieurs à 5. On sélectionne la cellule qui contiendra la somme et on écrit la formule suivante dans la barre de formule :
= SUMIF (B1 : B6, “> 5”)
La fonction de RECHERCHEV (appelée vlookup en anglais) est l’une des fonctions les plus importantes d’Excel, que beaucoup de gens ne connaissent malheureusement pas, mais qui faciliterait certainement leur travail avec les feuilles de calcul.
La fonction s’exprime avec la formule RECHERCHEV et celle-ci va rechercher une portion d’information contenue dans un grand tableau contenant beaucoup de données et va insérer les données trouvées dans un nouveau tableau, généralement plus petit.
Peut-être qu’un exemple clarifiera mieux le fonctionnement de cette opération particulière.
Imaginons que nous ayons 2 tables. Le premier est une très grande liste (plus de 100 par exemple) d’élèves avec leurs notes sur 3 devoirs de classe différents. Le deuxième tableau est plutôt une liste de trois élèves. Ce que nous voulons faire, c’est prendre les données relatives à l’affectation de 3e classe de la plus grande table et les mettre dans la plus petite table.
Ainsi, comme vous pouvez le voir sur l’image ci-dessous, nous avons une liste de 3 étudiants :
Ce que nous voulons faire, c’est utiliser la table VLOOKUP pour extraire, à partir d’un très grand registre de notes, les notes liées à la troisième tâche et les mettre dans le carnet de notes réalisé par les 3 étudiants.
Étant donné l’exemple précédent dans lequel nous avions une liste d’étudiants avec leurs notes, supposons que nous voulions compter le nombre d’étudiants dans notre “registre de classe” et que nous souhaitions mettre le résultat dans une cellule. Pour ce faire, nous devons utiliser une fonction spécifique : la fonction de valeur de comptage.
Cette fonction est représentée par la formule COUNTVALUE, une formule qui prend la plage de cellules à compter comme paramètres d’entrée. Évidemment, COUNTVALUE ne comptera que les cellules pleines.
Dans l’exemple précédent où l’on veut compter le nombre d’élèves contenu dans la première colonne, on écrira :
= COUNTVALUE (A1 : A6)
où A1 : A6 est la plage de cellules à compter. Le résultat sera 6.
L’image suivante clarifiera tous vos doutes:
Cette fonction identifie l’élément minimum d’un ensemble de cellules dans Excel, qu’il s’agisse d’une ligne ou d’une colonne ou d’un ensemble de celles-ci.
En fait, supposons que nous ayons une plage de cellules et que nous voulions identifier l’élément qui a la plus petite valeur parmi elles, nous utiliserons alors la fonction MIN.
La formule MIN prend une plage de cellules et renvoie l’élément avec la plus petite valeur.
Exemple : Étant donné le registre des notes d’une classe, nous voulons trouver la note la plus élevée prise par les élèves. La formule à utiliser sera celle-ci :
=MIN(B1:B7)
La formule de calcul de l’élément maximum dans Excel calcule à la place l’élément le plus grand, compte tenu d’une plage de cellules.
Prenons, par exemple, la même liste de notes d’étudiants. Avec la fonction MAX, nous calculerons la note la plus élevée. La formule MAX prendra une plage de cellules comme paramètre d’entrée et renverra la valeur la plus élevée parmi elles.
La fonction AUJOURD’HUI est la fonction typique utilisée pour obtenir la date du jour, sans avoir à l’écrire vous-même. En fait, supposons que pour une raison quelconque, dans notre feuille Excel, nous voulons écrire la date d’aujourd’hui dans une cellule et nous voulons que cela se mette à jour automatiquement tous les jours et chaque fois que nous ouvrons notre feuille de calcul, alors nous avons trouvé la bonne fonction pour nous.
oici son implémentation qui, entre autres, ne nécessite aucun argument :
= AUJOURD’HUI ()
En regardant l’image et, plus précisément, la barre de formule, on remarque que la fonction ne nécessite pas de paramètres d’entrée.
Pour obtenir l’heure et la date du jour, nous pouvons utiliser la formule Excel MAINTENANT. Quant à la fonction AUJOURD’HUI, elle ne nécessite pas de paramètres d’entrée et imprime l’heure d’aujourd’hui avec la date d’aujourd’hui à suivre.
Voici la formule MAINTENANT en action :
= MAINTENANT ()
La différence entre AUJOURD’HUI et MAINTENANT est que le premier imprime uniquement la date, le second imprime la date et l’heure.
La formule de somme Excel est peut-être la plus utilisée de toutes. La tâche de cette fonction est d’additionner toutes les valeurs contenues dans une plage de cellules et de renvoyer un résultat en le stockant dans une autre cellule. La formule à utiliser sera SOMME.
Exemple : en supposant donc que nous ayons un inventaire des produits achetés, nous voulons additionner les coûts des produits individuels pour voir quand nous avons dépensé au total.
En se référant à l’image, nous utiliserons la formule suivante :
= SOMME (B1 : B5)
La formule doit être saisie (comme sur l’image) dans la barre de formule.
La fonction E est une fonction logique qui vérifie plusieurs critères et renvoie VRAI si TOUS les critères sont vrais, sinon elle renvoie faux.
Généralement cette fonction est liée à l’utilisation de la fonction IF, une fonction qui effectuera certaines opérations si la déclaration E est vraie, sinon la formule IF agira différemment.
Revenons à la fonction logique E(), voyons comment cela fonctionne :
Si les deux arguments de la fonction sont vrais, la fonction renvoie VRAI.
Si au moins un des arguments de la fonction est faux, la fonction renvoie FALSE.
La formule générale est celle-ci :
E (logique1 ; logique2)
Nous allons maintenant voir un exemple pratique avec des applications de l’utilisation de E () dans la formule SE.
En supposant que nous ayons une liste de notes de deux étudiants, nous voulons vérifier s’ils ont tous les deux réussi. Pour ce faire, nous devons vérifier que les votes des deux sont supérieurs à 5 (donc à partir de 6, c’est bien). Pour cela nous utilisons la formule E(), qui prendra deux conditions comme valeurs d’entrée :
Première année d’élève> 5
Deuxième année d’élève> 5
Si les deux conditions sont remplies, avec l’utilisation de la formule SI, la chaîne “PROMOTED” sera imprimée à l’écran (affichée à l’écran), sinon la chaîne “AU MOINS UN ÉLÈVE CONTRÔLÉ” sera imprimée à l’écran. Ci-dessous la formule complète avec l’image des deux fonctions en action.
= SI (ET (B2> 5; B3> 5); “PROMOUVÉ” ; “AU MOINS UN ÉLÈVE RETOURNÉ”)
La fonction O () est très similaire à la fonction E (). O () vérifie si au moins une des deux conditions est vraie.
Contrairement à la fonction E (), où les deux conditions doivent être vraies pour renvoyer VRAI, la fonction O () n’a besoin que de l’une des deux conditions pour que la fonction renvoie VRAI. Si les deux conditions sont fausses, la fonction renverra FALSE.
Formule générale:
= O (état 1 ; état 2)
Exemple:
= O (A1> 5; A2> 5)
Pour retourner TRUE, une des deux cellules (A1 ou A2) doit être > 5.
Comme pour la fonction E(), la fonction O() peut être utilisée conjointement avec la fonction SE() vue plus haut (voir le paragraphe précédent si vous ne comprenez pas de quoi je parle).
La formule Left renvoie le côté gauche d’une chaîne, en fonction du nombre de caractères spécifié. Si le nombre de caractères du deuxième argument de la formule est 10, la chaîne analysée sera coupée après les dix premiers caractères et renvoyée.
= GAUCHE (texte, number_characters_from_left)
Renvoie la partie droite d’une chaîne, en fonction du nombre de caractères spécifiés comme deuxième argument. La fonction commence à compter à partir de la droite, le nombre de caractères que vous souhaitez récupérer dans une chaîne.
== DROITE (texte, nombre_caractères_de_droite)
Cette formule fusionne simplement deux chaînes en une seule. Ainsi, si par exemple la première chaîne est “Bonjour”, et la seconde est “que faites-vous ?”, la chaîne résultante sera “Bonjour, que faites-vous ?”.
La formule générale est :
= CONCATENER (chaîne1; chaîne2)
C’est le correspondant de la fonction PROPER d’Excel dans la version anglaise. La formule en question opère sur des textes et convertit la première lettre de chaque mot d’une phrase en majuscule et toutes les autres en minuscules. La fonction recevra l’adresse d’une cellule ou d’une chaîne en entrée. Voyons-le en action :
= START SHIFT (C4)
Come si vede nell’immagine, il testo della cella C4 viene modificato e inserito in A1.
Si, par contre, nous voulons mettre tout le texte en majuscule, nous utilisons la formule SHIFT. Ceci est utilisé de la même manière que la formule SHIFT START, mais le résultat sera différent.
Ainsi par exemple, le texte “J’apprends excel”, avec SHIFT sera transformé en “J’APPRENDS EXCEL”.
Beaucoup d’entre vous ont entendu parler des nombres aléatoires, c’est-à-dire des nombres aléatoires obtenus à partir d’une fonction. Plus précisément, la fonction RANDOM, renvoie une valeur aléatoire allant de 0 à 1, donc toute valeur allant de 0 à 1.
Où utiliser cette fonctionnalité ? Supposons que nous ayons besoin de remplir un tableau vide pour faire des tests et que ce tableau soit composé de milliers de cellules. Dans ce cas, il peut être utile de le remplir avec des valeurs aléatoires que nous pouvons obtenir grâce à la formule RANDOM, avec les précautions appropriées. En fait, si nous avons besoin d’une valeur comprise entre 0 et 10, il suffit de sélectionner une cellule et d’écrire la formule suivante dans la barre de formule :
= RANDOM () * 10
Mais que se passe-t-il exactement ? La fonction RANDOM() nous renverra une valeur allant de 0 à 1 (Ex. : 0,23455309), par la suite cette valeur sera multipliée par 10, nous aurons donc comme résultat 2,3455309. Ainsi les résultats renvoyés iront de 0 à 10. Si à la place on veut des valeurs allant de 0 à 100, il suffit d’écrire :
= RANDOM() * 100
La fonction RANDOM.TRA, quant à elle, est configurée pour obtenir un nombre aléatoire entre deux nombres, par exemple :
Si vous ne comprenez pas le concept de nombre aléatoire, il est conseillé de vous référer d’abord à la fonction ci-dessus, puis de lire le paragraphe précédent.
Voyons un exemple de la fonction :
= RANDOM (-100, 78)
Dans le cas où nous avons une valeur à virgule flottante ou pour être plus clair un nombre avec des décimales (Ex.: 43,40505) et que nous voulons qu’il devienne un entier, en l’arrondissant, nous pouvons utiliser la fonction Excel ROUND.
La fonction générique est :
= ROUND(nombre, nombre_chiffres)
Nous avons vu plus de fonctions Excel qui pourraient être utiles pour effectuer des tâches courantes. Généralement ce sont les plus importants, donc aussi les plus utilisés. Mais la liste ne s’arrête pas là. Comme indiqué au début, il reste encore des centaines de fonctions à explorer, et c’est donc à vous de poursuivre la recherche et l’étude de manière indépendante. Le conseil que je peux te donner c’est de tester. Avec un peu de patience, vous arriverez certainement à la solution.
Voici une série d’exercices conçus pour perfectionner vos compétences Excel. Les corrigés sont inclus pour…
Excel offre plusieurs méthodes pour calculer une moyenne tout en tenant compte des filtres ou…
Excel propose plusieurs fonctions pour insérer ou manipuler la date actuelle. Voici les principales méthodes…
Lorsque des nombres sont stockés sous forme de texte dans Excel, ils ne peuvent pas…
Extraire uniquement les chiffres d'une cellule contenant du texte et des nombres mélangés est une…
Pour supprimer plusieurs caractères spécifiques (par exemple, des symboles, chiffres ou lettres indésirables) dans des…
This website uses cookies.