Les fonctions à connaître pour maîtriser Excel

Il prend en charge de nombreuses fonctions qui vous permettent de traiter les chiffres de manière créative. Pour effectuer certaines actions dans Excel, vous devez utiliser des formules assez compliquées. Les fonctions présentées ici sont toutes disponibles dans Microsoft Excel 2010. Il y a très peu de différences dans les fonctions disponibles d'une version à l'autre, mais si vous utilisez Excel en anglais ou dans une autre langue, vous devrez peut-être les traduire. S'il y a d'autres fonctions que vous utilisez couramment et que vous aimeriez que j'ajoute ici aussi (dans un souci de personnalisation), n'hésitez pas à commenter ci-dessous !

Mathématiques et trigonométrie

• ALEA.ENTRE.BORNES –Dans Excel, pour obtenir un nombre entier aléatoire entre deux limites spécifiées. Par exemple : ALEA.BETWEEN.BORNES(1;42) renvoie un nombre compris entre 1 et 42 (ce qui est utile pour générer des numéros de loterie ou établir des listes de videurs dans les boîtes de nuit).
• ARRONDI – Permet d'arrondir un nombre, en fonction du nombre de chiffres après la virgule. Exemple : ARRONDI(42,1337;2) renvoie la valeur 42,13. Variantes : ARRONDI.SUP permet d’arrondir à la valeur supérieure, ARRONDI.INF permet d’arrondir à la valeur inférieure, et ARRONDI.AU.MULTIPLE permet d’arrondir à un multiple spécifique (au 0,5 plus proche etc.).
• PGCD –Met en évidence le plus grand diviseur commun de plusieurs valeurs entières. Par exemple, PGCD(12;30) renverra 6 car c'est la plus grande valeur de ce type.
• PI – La fonction ARRONDI vous permet d'utiliser la valeur π dans une cellule. Par exemple : ARRONDI(PI();2) = 3.14
• PRODUIT –Multiplie les données. Par exemple, PRODIT(2;2;2) = 8.
• PUISSANCE - Calcule la puissance d'une donnée.PUISSANCE(4;2) = 4² = 16. Vous pouvez utiliser un autre paramètre à la place 2 pour calculer des puissance complexes.
• ROMAIN –
• Permet de remplacer les chiffres arabes par des chiffres romains. Exemple : ROMAIN(2013;0) = MMXIII.
• SOMME – La fonction SOMME additionne des nombres. Par exemple, SOMME(13;29) = 42

Logique

• ET – Évalue plusieurs conditions et renvoie la valeur True si toutes les conditions sont remplies. Par exemple : And(1337-42=1295;1295+42=1337) renverra la valeur VRAI, car 1337-42=1295 (condition 1), 1295+42=1336 (condition 2).
• OU –Renvoie VRAI si au moins un des critères est vrai. Exemple : OR(1+1=2 ; 1+3 =4) renvoie VRAI car dans chaque cas, au moins une condition a été remplie (donc soit 2 soit 4 doit être vrai).
• SI – Vous pouvez l'utiliser pour tester une condition, puis renvoyer une valeur si cette condition est vraie, une autre si elle ne l'est pas. Par exemple : SI (average(A1:A15)) affichera "J'ai la moyenne" dans la cellule B2 si vos cellules d'entrée contiennent des notes comprises entre A1 et Z15 ; sinon (chaîne vide) ("Je n'ai pas") sera affiché.

Recherche et matrices

• COLONNE – Permet d’obtenir le numéro de colonne. Exemple : COLONNE(A42) renvoie la valeur 1, car la colonne A est la première colonne. Variante : COLONNE() renvoie la valeur correspondante à la colonne où se situe la formule Excel.
• RECHERCHE – Dans certains cas, les formules de recherche peuvent sauver des vies. Exemple : RECHERCHE(42;A:A;B:B) va chercher la valeur 42 dans la colonne A, puis retranscrire son équivalent dans la colonne B (sur la même ligne). RECHERCHE permet également de chercher dans une matrice ou un vecteur.
• RECHERCHEH – Même principe que RECHERCHE, mais pour chercher des valeurs selon la première ligne d’une matrice. Exemple : RECHERCHEH(« ville »;A1:D10;2;VRAI) permet de renvoyer la deuxième ligne d’une colonne dont la première ligne est ville au sein d’un tableau.
• RECHERCHEV – Le même principe que SEARCHH, mais recherche des valeurs en fonction de la première colonne d'une matrice. Exemple : SEARCHV("ville";A1:D10;2) renvoie la deuxième colonne de chaque ligne où la ville est en première position.
• TRANSPOSE – Permet de transformer une matrice en la transposant : par exemple, transformer des lignes en colonnes (ou vice versa). N'oubliez pas de valider votre formule avec Ctrl+Shift+Enter puisqu'il s'agit d'un calcul matriciel.

Date et heure

• ANNEE – Pour obtenir l'année correspondant à une date particulière. Par exemple : ANNEE("09/10/2013") donne l'année 2013.
• MOIS – Le même principe s'applique pour obtenir un mois. Par exemple, MOIS ("09/10/2013") renvoie la valeur 10
• JOUR – Même principe, pour obtenir le jour. Exemple : JOUR(« 09/10/2013 ») renvoie la valeur 9.
• JOURSEM – La fonction JOURSEM fonctionne de manière similaire, mais elle tient compte du jour de la semaine associé à une valeur donnée. Par exemple : JOURSEM("09/10/2013";2) renvoie 3 - parce que le mercredi est le troisième jour de cette semaine particulière.
• HEURE – Remplacez simplement 12 par l'heure dont vous avez besoin. Par exemple, HEURE("12:30:40") renverra la valeur "12".
• MINUTE – Pour obtenir les minutes d'une heure : par exemple, MINUTE("12:30:40") donne 30.
• AUJOURDHUI – La fonction AUJOURDHUI affiche la date actuelle, et d'autres fonctions similaires vous permettent d'afficher une date future spécifique. Par exemple :
• MAINTENANT – Cette fonction vous permet d'obtenir des informations plus précises sur la date et l'heure. Selon le formatage de votre cellule, cela peut donner des résultats tels que "année/mois/jour" ou "aaaa-MM-jj".
• FIN.MOIS – Permet d’obtenir le dernier jour du mois en cours. Exemple : FIN.MOIS(« 09/10/13 »;0) retourne le 31 octobre 2013. Vous pouvez modifier le 0 en +1 pour obtenir le dernier jour du mois suivant, ou -1 pour obtenir le dernier jour du mois précédent et ainsi de suite.
• FRACTION.ANNEE – Permet de convertir les mois et les jours.
• Exemple
• NB.JOURS.OUVRES –
• Cette fonction porte bien son nom, car elle vous permet de compter le nombre de jours travaillés.
• NO.SEMAINE – Renvoie le numéro de semaine correspondant à une date. Par exemple : NO.SEMAINE(AUJOURDHUI() ; 2) renvoie 41, car nous sommes dans la semaine 42 . Si aucun paramètre n'est spécifié, le fait que le lundi ou le dimanche commence en premier dépendra de vos paramètres de format par défaut.

Texte

• CNUM – Si une chaîne contient à la fois des chiffres et du texte, seul le chiffre est retranscrit. Utile pour supprimer le caractère € de vos chaînes de caractères ! Exemple : CNUM("42€") = 42.
• CONCATENER –
• Une fonction utile pour la concaténation de chaînes de caractères.
• MAJUSCULE – Vous pouvez convertir du texte en majuscules dans Excel en procédant comme suit
• MAJUSCULE(« Excel, c’est génial ») renvoie la valeur suivante : EXCEL, C’EST GÉNIAL.
• MINUSCULE – MINUSCULE("excel") renvoie EXCEL.
• NBCAR – La fonction NBCAR vous permet de compter le nombre de caractères contenus dans une chaîne de texte, espaces compris. Par exemple : NBCAR("Neque porro quisquam est qui dolorem ipsum") renvoie 42 - puisque cet extrait comporte 42 caractères au total.
• NOMPROPRE – Transforme la première lettre de tous les mots d’une chaîne de caractères. Exemple : NOMPROPRE(« le bon logiciel ») permet d’obtenir Le Bon Logiciel.
• REMPLACER – Permet de remplacer les caractères d’une plage de données. Exemple : REMPLACER(« coucou »;4;3; »leuvre ») va remplacer coucou par couleuvre. Le second paramètre (4) permet de commencer le remplacement au quatrième caractère, le troisième paramètre (3) correspond au nombre de caractères remplacés.
• SUBSTITUE – La fonction SUBSTITUTE est souvent plus pratique que la fonction REPLACE, car elle permet de rechercher une valeur puis de remplacer les occurrences par une autre. Exemple : SUBSTITUTE(D1;6;10) retournera 10 si la cellule D1 contient 6. Cela fonctionne aussi sur du texte !

Statistiques

• ECARTYPE – Comme vous pouvez le constater, cette fonction est utilisée pour calculer l'écart type au sein d'une population. Par exemple : ECARTYPE(42;1337;42) retourne 52.54
• GRANDE.VALEUR –
• Permet de déterminer la plus grande valeur d'une matrice de données. Par exemple, GRANDE.VALEUR(A:B;1) renvoie la plus grande valeur des colonnes A et B (ou de la ligne 1 si une seule colonne est spécifiée). Vous pouvez utiliser le chiffre 2 au lieu du chiffre 1 pour la ligne 2, le chiffre 3 pour la ligne 3, etc.
• MAX – Par exemple, la fonction MAX(A:B;42) renvoie la valeur maximale des colonnes A et B ou 42 si ce nombre est supérieur.
• PETITE.VALEUR – C’est l’inverse de GRAND.VALEUR.
• MIN – C’est exactement l’inverse de MAX.
• MEDIANE – Permet de calculer la médiane d'une série de données dans Excel. Par exemple : MEDIANE(10;40;30) renvoie 30, puisqu'il s'agit de la médiane de cette série.
• MODE – La fonction MODE vous permet de trouver le nombre ayant le plus d'occurrences dans une série de données.
• MOYENNE –
• AVERAGE(42;1337) calcule la moyenne de 42 et 1337, ce qui donne 689.5
• NB.SI – Cette fonction est utilisée pour compter combien de cellules d'une plage satisfont à une certaine condition.
• NB.VIDE–
• Cette fonction compte le nombre de cellules vides dans une plage donnée. Par exemple : NB.VIDE(A) renvoie 1048576 si la colonne A est complètement vide.
• PREVISION – La fonction de prévision vous offre un moyen simple de calculer des prévisions complexes. Vous devez spécifier une matrice de données indépendante, une matrice de données dépendante et la valeur d'intérêt pour chaque point de vos matrices d'entrée. Si, pour toutes les valeurs de la série A, les valeurs correspondantes d'une deuxième série B sont 10 fois plus grandes que celles de A, alors PREVISION(4 ; 2;B ;A) est égal à 42.
• RANG – La fonction RANG() vous permet de connaître le rang d'une valeur dans une série de données. Par exemple, si votre matrice contient 2 4 6 8 10 comme valeurs et que vous appliquez RANG(6;matrice;1) avec ce paramètre égal à 1, elle retournera 3 puisqu'il s'agit du troisième nombre (dans l'ordre croissant).

Ingénierie

• BINDEC – La fonction BINDEC convertit un nombre binaire en sa valeur décimale équivalente. Par exemple : 1100100 est converti en 100 (ce qui équivaut à 11001000 lorsqu'il est écrit en base 10).
• CONVERT – 
• Pour convertir une valeur d'une unité à une autre : les mètres (m) en milles nautiques (mn), les années (aa) en jours (jj)... Vous pouvez même convertir une cuillère à café en tasse et en litre, ainsi qu'en pinte.

Informations

• CELLULE – La fonction CELLULE renvoie des valeurs concernant les cellules de votre feuille de calcul. Par exemple : " CELLULE(type;A42) nous permet de savoir si la cellule est vide (i), contient du texte (l) ou une valeur (v).
• EST –De nombreuses fonctions commencent par EST dans Excel, comme ESTVIDE pour vérifier qu'une cellule est vide ou est.paire pour savoir si le nombre qu'elle contient est pair. Pensez également à Esttexte, qui vous permet de savoir si du texte existe quelque part dans votre feuille de calcul. ESTNUM renvoie VRAI si une valeur est un nombre, FAUX sinon. Par exemple : ESTNUM(42) renvoie VRAI parce que 42 est un nombre, mais ESTNUM("bob") l'était aussi ?
• INFORMATIONS – Permet d'obtenir des informations sur votre environnement de travail. Exemple : INFORMATION("version") vous indique la version d'Excel utilisée ; systexpl peut être utilisé pour connaître le système d'exploitation et le chemin d'accès aux répertoires.

Pour aller plus loin:

Excel: formule DATEDIF pour calculer la différence entre deux dates

Excel: fonction logique SI.CONDITION

Excel: Faire un arrondi inférieur ou supérieur

Excel: Fonction DATE – ANNEE – MOIS – JOUR

Excel: premier jour, dernier jour

Excel: Faire une somme

Calcul d'une moyenne avec Excel

Excel: nommer une variable

Excel: les raccourcis claviers à connaître