lundi 15 décembre 2014

Le tour d'Excel en 50 fonctions.

Fonction Excel : Fonctions de base
50 fonctions pour vous aider à tirer profit d'Excel. Vous trouverez sur cette page un florilège des fonctions qu'un jour ou l'autre vous serez amené à utiliser.















1 - Introduction :


Sans vouloir être réducteur et résumé Excel à 50 fonctions, je vais vous présenter ici, celles que j'utilise le plus dans mes fichiers, développements, et qui me permettent de résoudre la plus part des problèmes rencontrés au quotidien.
Les fonctions détaillées ci-dessous existent pour la plus part dans les versions 2007,2010 et 2013 d'Excel. Certaines ne sont maintenues que pour des raisons de compatibilité avec 2007, mais restent très utiles.
Pour des raisons évidentes, les formules présentées sur cette page, le sont de façon résumée, mais chacune d'elle a fait ou fera l'objet d'un article dédié.
Les exemples d'utilisation présentés, ne sont là que pour vous donner une idée des possibilités offertes par ces fonctions, ils ne sont pas exhaustifs.
Parfois les fonctions vont vous sembler peu intéressantes ou basiques, mais ne vous y fiez pas, imbriquées entre-elles, elles deviennent de redoutables outils de résolution de problèmes.
Si vous utilisez au quotidien, d'autres fonctions que celles présentées ici, n’hésitez pas à nous en parler dans les commentaires, nous sommes là pour nous améliorer!

2 - Date et Heure :

Les fonctions Dates et Heures permettent la manipulation et le calcul du temps, elles sont spécifiques à cette utilisation.

  • ANNEE: renvoie l'année, entre 1900 et 9999, à partir d'un numéro de série utilisé par Excel. =ANNEE(49897) renvoie 2036. Idéal pour extraire l'année d'une date et l'utiliser pour d'autres calculs par la suite.
  • AUJOURDHUI: Donne la date du jour au format JJ/MM/AAAA. Si nous sommes le 13 décembre 2014 =AUJOURDHUI() donne 13/12/2014. Vous permet à l'ouverture d'Excel, d'actualiser la date du jour. Utile si l'on a besoin d'avoir une date de référence dans un fichier, par exemple un suivi de trésorerie journalière.
  • DATEVAL: Convertit une date texte en date utilisable par Excel. Si nous avons 10/12/2014 au format texte dans la cellule A1,  =DATEVAL(A1) donne 41983 dans le format Excel. A utiliser si par exemple les données rapatriées dans le tableur, le sont au format texte.
  • JOUR: Extrait le numéro du jour pour une date donnée. =JOUR("21/12/2014") donnera 21. Utile pour faire des plannings ou des calendriers entre autre.
  • JOUR360: Calcul le nombre de jours entre deux dates, sur la base d'une année de 360 jours. Si vous avez 41994 en A1 et 42345 en B1 alors =JOURS360(A1;B1) donnera 346, nombre correspondant à l'écart entre ces deux dates. =JOUR("21/12/2014";"07/12/2015") affichera la même chose. Peut s'avérer utile pour des calculs financiers qui font référence à des écarts de dates.
  • JOURSEM: indique le jour de la semaine à partir d'une date. Si vous avez 12/12/2014 en A1, =JOURSEM(A1;1) donnera 1 et =JOURSEM(A1;2) donnera 7 suivant le critère Type_retour que vous aurez choisi. 1 donne dimanche comme premier jour, 2 donnes Lundi comme premier jour et dimanche comme le septième, etc.....Vous avez jusqu'à 10 Type_retour différents de quoi adapter Excel à tous vos besoins. Tout indiqué pour un calendrier nécessitant des journées de semaine décalées, exemple magasin.
  • MOIS: Extrait le numéro du mois pour une date donnée. =JOUR("21/12/2014") donnera 12. Utile pour faire des plannings ou des calendriers par exemple.
  • NO.SEMAINE: Renvoie le numéro de la semaine correspondant à une date donnée. Si nous reprenons l'exemple ci-dessus =NO.SEMAINE(A1) donne 1. 

3 - Information :

Les fonctions informations, vont nous permettre de vérifier le contenu des cellules auditer et  de faire réagir le tableur suivant nos besoins en fonction des résultats.

  • CELLULE: Vous donne une série d'information sur une cellule, adresse, contenu, protection... =CELLULE("contenu";A1) vous indique ce qu’elle contient, =CELLULE("Protege";A1) vous informe sur l'état de la cellule, est-elle protégée ou non. Utile pour faire réagir une VBA en fonction de l'information fournie ou toute autre chose.
  • ESTERR: vérifie si le contenu d'une cellule fait référence à une erreur (#NA, #Valeur,...) et renvoie VRAI ou FAUX. A utiliser mixée avec un SI par exemple pour déterminé une action à tenir.
  • ESTERREUR: Ressemble à si méprendre à ESTERR.
  • ESTNA : Plus spécifique que les deux précédentes fonctions, vérifie si la cellule contient l'erreur #NA et renvoie VRAI ou FAUX. Comme la plus part des fonctions EST est à mixer avec SI.
  • ESTVIDE: Contrôle qu'une référence renvoie à une cellule vide et renvoie VRAI ou FAUX. Si A1 contient 3, =ESTVIDE(A1) renvoie FAUX.  Comme la plus part des fonctions EST est à mixer avec SI.

4 - Logique :

Les fonctions logiques permettent le contrôle de la réalisation ou non de conditions que nous avons préalablement défini.

  • ET: Vérifie si toutes les conditions sont vraies et renvoie VRAI ou FAUX. Si nous avons 3 en A1, 5 en B1 et 2 en C1, =ET(A1>C1;B1>C1) donne VRAI car toutes les conditions sont vrais. Utilisation principale avec la fonction SI.
  • OU: A l'inverse de ET, OU vérifie si au moins une condition est vraie et renvoie VRAI ou FAUX. Reprenons l'exemple ci-dessus, =OU(A1>C1;C1>B1) renvoie VRAI car une des deux conditions est vraie. A mixer avec SI principalement.
  • SI: La fonction par excellence, vérifie que la condition est respectée et renvoie la valeur spécifiée si la condition est vraie et une autre si la condition est fausse. Toujours avec le même exemple cité pour ET, =SI(A1>C1;1;2) donne 1 car la condition A1>C1 est vraie. A utiliser dans quasiment tous les cas de figure où l'on veut faire des tests. Les conditions SI peuvent s'enchainer les unes derrière les autres, pour tester plusieurs conditions à la suite, mais attention à la visibilité de la formule lorsque l'on revient dessus après quelques temps.  
  • SIERREUR: Arrivée depuis 2007 dans Excel, cette fonction permet en cas d'erreur, de faire réagir le tableur, comme nous le voulons. Imaginons que nous ayons une RECHERCHEV basée sur l'exemple de ET et qui donne une erreur, SIERREUR(RECHERCHEV(6;A1:C1;2);0) nous permet de renvoyer une valeur 0 plutôt qu'une erreur Excel type #NA ou #VALEUR. C'est nettement plus sympathique pour présenter un tableau. Cette fonction se substitue à un mixe SI et ESTERR par exemple.
  • VRAI: Permet de renvoyer VRAI dans une autre fonction.
  • FAUX: Renvoie FAUX dans une autre fonction.
Pour en apprendre plus sur les fonctions logiques vous pouvez consulter cette page.

5 - Maths et Trigonométrie :

Les fonctions Mathématiques permettent d'exécuter des calculs simples ou complexes.

  • ALEA: Fonction volatile. Renvoie un nombre aléatoire entre 0 et 1.
  • ALEA.ENTRE.BORNES: Fonction volatile. Renvoie un nombre aléatoire entre deux nombres que vous spécifiez.
  • ARRONDI: Arrondi un nombre au nombre de chiffre indiqué. =ARRONDI(3,14116;2) donne 3,14, car nous lui avons demandé d'arrondir à 2 chiffres après la virgule. Contrairement au formatage d'une cellule avec deux chiffres après la virgule, ARRONDI transforme le nombre 3,14116 pour en faire 3,14. Cette fonction vous permet lors de calculs générant x chiffres après la virgule d'éviter de les trainer dans les tableaux suivants et ainsi de créer des résultats incompréhensibles au final.
  • MOYENNE: Comme son nom l'indique cette fonction calcule la moyenne de nombres ou de cellules contenant des nombres. =MOYENNE(3;10;4;56) renvoie 18,25.
  • SOMME: Calcule la somme d'une série de cellules contenant des nombres. Si A1=2 et B1=10, =SOMME(A1:B1) affiche 12.
  • SOMME.SI: Cette somme conditionnelle, additionne les cellules spécifiées pour un certain critère et affiche la somme des résultats.  Si en A1 à A7 nous avons une suite de prénoms, en B1 à B7 des montants, =SOMME.SI(A1:A7;"Mélanie";B1:B7) donnera la somme de tous les montants correspondant au critère Mélanie. Il existe aussi SOMME.SI.ENS pour faire la même chose mais avec plusieurs critères. Ces fonctions sont très utiles pour extraire des informations d'un tableau et les réutiliser par ailleurs dans votre fichier.
  • SOUS.TOTAL: Intercalée dans vos tableaux ou listes, cette fonction, suivant certains critères, effectuera un sous-total qui pourra par la suite être additionné pour faire un total général. En fonction du critère choisit vous pourrez obtenir, la somme, la moyenne, le maximum, le minimum, le produit,..., de la zone de cellules de référence. 

6 - Recherche et Référence :

Les fonctions Recherche et Référence permettent la définition de plage de cellules et l'interrogation de celles-ci par la suite.
  • ADRESSE: Crée une référence absolue ou relative, par rapport à une ligne et à une colonne. =ADRESSE(1;1;1) donne $A$1, =ADRESSE(1;1;2) donne A$1. Un exemple de son utilisation est donné sur le post Afficher une image ou un logo en fonction d'un choix.
  • CHOISIR: Comme son nom l'indique, cette fonction choisit une valeur parmi une multitude de valeurs, en fonction d'un numéro d'index. Si nous avons 2 en C5, 3 en D5 et 4 en E5, =CHOISIR(3;C5;D5;E5) affichera 4 qui correspond à la valeur de la troisième cellule. Utile pour sélectionner une donnée dans une liste ou tableau, en fonction d'un critère que vous sélectionnez dans une liste ou d'un résultat calculé auparavant.
  • COLONNE: Renvoie le numéro d'une colonne de référence. =COLONNE(A1) affichera 1.
  • DECALER: Sert à définir une plage de cellules dans une feuille en partant d'une cellule, on décale la référence de cette cellule d'un certain nombre de lignes et de colonnes. Difficile de donner ici un exemple, j'utilise cette fonction indispensable pour gérer des bases de données par exemple.
  • EQUIV: Recherche un élément spécifique dans une plage de cellules et renvoie sa position relative. Si nous avons 2 en C5, 3 en D5 et 4 en E5, =EQUIV(2;C5:E5;0) donnera 1, car la valeur 2 est située dans la première cellule de la zone de référence.  A associer avec INDEX.
  • INDEX: Permet de renvoyer la valeur d'une cellule se trouvant à l'intersection d'une ligne et d'une colonne dans un tableau. Si nous avons 2 en C5, 3 en D5 et 4 en E5, =INDEX(C5:E5;1;2) affichera 3. A mixer avec EQUIV.
  • LIEN_HYPERTEXTE: Crée un raccourci permettant d’ouvrir un document stocké sur un serveur réseau, un intranet ou sur Internet. Lorsque vous cliquez sur la cellule contenant la fonction LIEN_HYPERTEXTE, Microsoft Excel ouvre le fichier stocké à l’adresse emplacement_lien. Syntaxe LIEN_HYPERTEXTE(emplacement_lien, [nom_convivial]). Exemple, les fichiers de vos collaborateurs font référence, à l'aide de LIEN_HYPERTEXTE, à un fichier source contenant des liens divers, vous pouvez mettre à jour ces liens pour tout le monde uniquement en modifiant le fichier source. 
  • LIGNE: Renvoie le numéro d'une Ligne de référence. =LIGNE(A1) affichera 1.
  • RECHERCHE: Cherche dans la première ligne ou colonne d'un tableau pour la valeur spécifiée, puis renvoie une valeur à partir de la même position dans la dernière ligne ou colonne de la matrice.
  • RECHERCHEH: Cherche une valeur donnée dans la première ligne de la matrice d'un tableau et renvoie une valeur se trouvant sur la même colonne mais dans une autre ligne de la matrice du tableau. Si nous avons 1, 2, 3 en A1 à C1 et Paris, Lille, Rouen en A2 à C2, =RECHERCHEH(1;A1:C2;2) affichera Paris.
  • RECHERCHEV: Cherche une valeur donnée dans la première colonne de la matrice d'un tableau et renvoie une valeur se trouvant sur la même ligne mais dans une autre colonne de la matrice du tableau. Si nous avons 1, 2, 3 en A1 à A3 et Paris, Lille, Rouen en B1 à B3, =RECHERCHEV(2;A1:B3;2) donnera Lille.

7 - Statistiques :

Les fonctions Statistiques nous permettent de classer nos calculs, de déterminer des maxima ou des minima et par la suite d'utiliser ces résultats ultérieurement.

  • MAX: Renvoie le plus grand nombre d'un ensemble de valeur. Si nous avons 1, 2, 3 en A1 à A3, =MAX(A1:A3) affichera 3 qui est la valeur la plus forte de la liste.
  • MIN: Renvoie le plus petit nombre d'un ensemble de valeur. Si nous avons 1, 2, 3 en A1 à A3, =MIN(A1:A3) affichera 1 qui est la valeur la plus faible de la liste.
  • NB.SI: Détermine le nombre de cellules non vide dans une liste, répondant à la condition spécifiée. Si nous avons 2, 3, 3, 0, 4 en A1 à A5, =NB.SI(A1:A5;3) donnera 2 car il existe deux cellules correspondantes au critère 3.
  • NB.VIDE: Compte le nombre de cellule vide dans une plage spécifiée. Si nous reprenons l'exemple de NB.SI. Le résultat de =NB.VIDE(A1:A5) sera égale à 0 car aucune cellule n'est vide.
  • NBVAL: A l'inverse de NB.VIDE, cette fonction détermine le nombre de cellule non vide. Avec le même exemple =NBVAL(A1:A5) affichera 5 car aucune cellule n'est vide dans la liste.
  • RANG: Renvoie le rang d'un nombre dans une liste de valeur en fonction d'un ordre de tri. Toujours avec le même exemple, =RANG(A1;A1:A5;1) donnera 2 et =RANG(A1;A1:A5;0) donnera 4. Formule maintenue pour la compatibilité avec les versions antérieures à 2007.

8 - Texte :

Ces fonctions permettent la manipulation, l'extraction, la conversion de texte dans le tableur.

  • CNUM: Converti un nombre écrit en texte en nombre. Si vous avez 23 stocké en texte en A1, =CNUM(A1) affichera 23 converti en nombre et vous pourrez ainsi utiliser votre résultat pour d'autres calculs par la suite.
  • CONCATENER: Assemble plusieurs chaines de caractère, nombre ou texte, pour n'en former qu'une seule. Si nous avons Bonjour, les, amis en A1 à A3, =CONCATENER(A1;"";A2;"";A3) afficher Bonjours les amis comme résultat. =CONCATENER(A1;A2;A3) donne Bonjourslesamis.
  • DROITE: Extrait les derniers caractères situés à droite de la cellule, en fonction d'un nombre défini. Si nous avons Bonjours en A1, =DROITE(A1;5) affichera jours.
  • GAUCHE: Extrait les premiers caractères situés à gauche de la cellule, en fonction d'un nombre défini. Si nous avons Bonjours en A1, =GAUCHE(A1;3) affichera Bon.
  • NBCAR: Renvoie le nombre de caractères contenus dans une cellule. Avec Bonjours en A1, =NBCAR(A1) donnera 8.
  • STXT: Renvoie un nombre déterminé de caractères contenus dans une cellule, à partir d'une position définie et suivant un nombre défini. Toujours avec le même exemple, =STXT(A1;4;1) donnera j.
  • TROUVE: Renvoie la position de départ d'une chaine de texte à l'intérieur d'une autre chaine de texte. Si nous avons Bonjours les amis dans la cellule A1, =TROUVE("les";D11;1) donnera 10, =TROUVE("Les";D11;1) donnera une erreur, car cette fonction distingue les majuscules et les minuscules.

Si vous imbriquez entre elles les fonctions texte ci-dessus vous pourrez extraire n'importe quelle information d'un texte, comme montré dans le tuto de cette page.


9 - Téléchargements :


Fonctions Excel.

10 - Autres références au sujet (ou articles complémentaires):


Les 50 fonctions essentielles.

11 - Autres liens :

Excel
Forum

Print Friendly and PDF


Cet article vous a intéressé, merci de le recommander avec un Google +1 et/ou de le partager sur Facebook, Twitter, Viadeo ou LinkedIn, en utilisant les liens mis à votre disposition.

Pour suivre nos prochains articles abonnez-vous au Blog ou inscrivez-vous au flux RSS.

Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner
Pays/territoire : Bry-sur-Marne, France

2 commentaires :

  1. Merci. Excel m’a toujours semblé compliqué dans sa maîtrise, la ça devrait aller mieux :)

    RépondreSupprimer