Les fonctions d'Excel

• Annexes
• Sommaire
• Bonnes pratiques et débogage des formules

Cette annexe vous propose un petit index non exhaustif des fonctions d'Excel. Les fonctions sont rangées par catégorie, présentées et illustrées avec des exemples concrets. On peut donc dire que ce chapitre fait suite à celui sur les fonctions, même si quelques rappels seront faits . N'hésitez pas à faire des recherches ici car bien souvent, on se casse la tête alors qu'une fonction toute prête existe déjà pour ce qu'on veut faire…

Durant ce chapitre, nous allons voir le groupe « Bibliothèque de fonctions » de l'onglet « Formules » :

• Carte d'identité de la fonction
• Les fonctions Mathématiques
• Les fonctions Logiques
• Les fonctions de Recherche et Référence
• Les fonctions Statistiques
• Les fonctions Texte
• Les fonctions Date et Heure

Carte d'identité de la fonction

Dans cette première partie, je vais vous présenter ce qu'est une fonction et aussi comment en écrire une dans Excel.

Qu'est-ce qu'une fonction ?

Vous connaissez le rôle de la formule ? Non ? Et bien on va commencer par là. Une formule, c'est ce que vous entrez dans la cellule. Voici un exemple :

=100+200 Grâce à cette formule, Excel pourra effectuer l'addition de deux nombres. Vous allez me dire que cette opération est simple, ce qui est vrai. Cette formule ne dépend que d'elle-même et non pas des autres cellules. On parle alors de contenu « statique ». Cela signifie que si je modifie les autres cellules, le résultat de celle-ci ne changera pas.

Vous vous doutez maintenant qu'il y a un autre type de contenu, c'est le contenu « dynamique ». Voici un exemple :

=B1+C5 Ici, on ne peut connaître le résultat de l'opération si on ne connaît pas la valeur des cellules en B1 et C5. C'est ce que l'on appelle un contenu dynamique. Il n'y a pas besoin de modifier la formule proposée pour que le résultat change. Il suffit de modifier les valeurs des cellules B1 et C5 pour que le résultat change. Testez par vous même. Entrez dans la cellule A1 l'exemple proposé plus haut : =B1+C5. Puis, amusez-vous à modifier les valeurs des cellules en B1 et C5, vous observerez que le résultat de cette addition change en A1.

Un contenu dynamique peut dépendre de cellules statiques ou de cellules dynamiques. Dans l'exemple précédent, il peut y avoir en B1 une valeur ou une autre formule.

Pour résumer, le contenu statique affiche un résultat sans dépendre d'autres cellules alors qu'un contenu dynamique dépend d'autres cellules.

Les formules sont très souvent utilisées dans un contenu dynamique. Pour faciliter l'utilisation de ces formules, Excel dispose d'une longue liste de « FONCTIONS ». L'utilisateur n'a plus qu'à fournir les paramètres des fonctions (lorsqu'elles en prennent) et Excel se charge d'effectuer les différentes opérations. Les fonctions permettent de faire des opérations arithmétiques (addition, soustraction, multiplication, division), des opérations logiques (comparaison de données) et d'autres que l'on découvrira au fil du tutoriel. Certaines fonctions combinent plusieurs types d'opérations et c'est grâce à ces combinaisons qu'Excel nous facilite la tâche. Par exemple, la fonction « MOYENNE » nous évite de faire l'addition de toutes les valeurs, de compter le nombre de valeurs et de diviser la somme obtenue par le nombre de valeurs.

Je vous présente ici des exemples de formules entrées dans les cellules d'Excel et à côté le type de contenu : statique ou dynamique avec des explications.

Ce tableau présente plusieurs exemples différents pour illustrer le contenu statique et dynamique. Vous comprendrez peut-être mieux en lisant la description des fonctions plus tard dans ce cours.

Pour reprendre, voici comment se présente une fonction :

=NOM_DE_LA_FONCTION(PARAMETRE1;PARAMETRE2;…) On voit donc qu'une fonction est composée du signe égal (=), de son nom et des paramètres, aussi appelés arguments, qu'elle prend en compte (s'il y en a, ils ne sont pas obligatoires). Ces paramètres peuvent être de différents types et le nombre de paramètres varie aussi beaucoup selon les fonctions.

Comment une fonction est-elle renseignée ?

Pour écrire une fonction, il y a plusieurs solutions et je vais vous en présenter trois. Il existe d'autres solutions comme l'utilisation du VBA Excel mais c'est plus complexe. Dans ce tutoriel, je vous présente les plus simples et les plus courantes.

Première solution

La première solution que nous allons présenter est l'entrée de la fonction directement dans la cellule en l'écrivant soit dans la cellule, soit dans la barre de formule, de cette façon (exemple de la fonction « SOMME » que nous verrons plus tard) :

Il faut alors connaître la fonction, c'est la méthode la plus utilisée lorsque l'on connaît les fonctions et qu'on les utilise souvent.

Deuxième solution

Par le ruban, dans l'onglet « Formules » et dans la rubrique « Bibliothèque de fonctions » puis en déroulant la liste d'une des catégories et en choisissant la fonction voulue. Toujours avec l'exemple de la fonction « SOMME », vous devriez avoir ça :

Dans le menu déroulant, on sélectionne la fonction que l'on veut et une fenêtre s'ouvre :

Il suffit alors de remplir les champs, Excel nous aide avec des informations en bas sur la fonction et sur le paramètre à entrer. Il faut ensuite cliquer sur « OK ». La formule est alors entrée dans la cellule active et peut être modifiée dans la barre de formule.

Pour sélectionner des cellules dont on ne connaît pas les coordonnées par cœur (c'est souvent le cas), il suffit de cliquer à droite du champ ici : et une autre fenêtre (plus petite) s'ouvre :

A ce moment, il vous suffit de sélectionner la ou les cellules souhaitées pour le paramètre. Si vous ne savez pas, cliquez sur la petite icône à droite de la fenêtre : . Vous revenez ainsi sur la fenêtre pour insérer la fonction.

Troisième solution

Par le ruban également, dans l'onglet « Formules » et dans la rubrique « Bibliothèque de fonctions » cliquer sur « Insérer une fonction ».

Une fenêtre s'ouvre alors :

Il faut donc soit décrire la fonction et Excel vous la trouve, soit sélectionner la fonction dans la liste en dessous lorsqu'elle est connue. Si on ne sait pas dans quelle catégorie elle se trouve, sélectionner « Tous ».

La fonction « SOMME » est toujours notre exemple pour cette troisième solution :

Cliquer alors sur « OK ». S'ouvre alors la fenêtre que l'on a vu lors de la deuxième solution. Il faut alors suivre la même procédure qu'à partir de cette fenêtre pour entrer la fonction.

Vous savez maintenant comment écrire une fonction, nous allons maintenant commencer avec les premières fonctions dans la deuxième partie.

Les fonctions Mathématiques

Dans cette première partie, nous allons étudier les fonctions « Mathématiques » d'Excel. Elles se trouvent ici : A partir du ruban et de l'onglet « Formules », de la rubrique « Bibliothèque de fonctions » et dans la catégorie « Maths et trigonométrie ».

Ou à partir du ruban et de l'onglet « Formules », de la rubrique « Bibliothèque de fonctions » et de cliquer sur « Insérer une fonction ». Une fenêtre s'ouvre, sélectionner dans le menu déroulant de la catégorie : « Math & trigo. » :

Je vais vous proposer des fonctions de base de la catégorie « Mathématiques et trigonométrie » qui ne sont pas forcement intuitives. D'autres fonctions existent mais sont très simples d'utilisation.

Pour suivre avec moi cette sous-partie et vous exercer de votre côté, je vous propose de :

Télécharger le fichier fonctions_mathematiques.xlsx

Ce classeur Excel contient tous les exemples utilisés dans cette sous-partie. Il y a la base des exemples, à vous d'entrer les formules.

INTRODUCTION

Dans cette introduction, nous allons parler des opérateurs et des priorités mathématiques. Cela peut paraître facile, mais un rappel n'est pas une perte de temps pour certains. Les trois fonctions qui suivent permettent d'effectuer les opérations suivantes : addition, soustraction, multiplication, division. Un petit tableau qui récapitule les signes utilisés pour ces opérations :

Dans une formule Excel, on peut utiliser ces opérateurs pour effectuer des calculs. Mais lorsqu'il s'agit d'additionner 50 cellules, la formule devient très longue. C'est pourquoi les fonctions sont utiles.

Petit rappel mathématique : les opérations de multiplication et division sont prioritaires sur les opérations d'addition et de soustraction.

Une formule est lue et exécutée de gauche à droite et effectue les opérations dans l'ordre. Mais elle respecte les propriétés opératoires rappelées juste avant. La formule effectue donc d'abord toutes les multiplications et divisions et ensuite les additions et soustractions. Si des additions doivent être effectuées avant les multiplications par exemple, il faut alors utiliser les parenthèses. Ainsi, une addition entre parenthèses est effectuée AVANT une multiplication. Voici des exemples :

J'espère que ça vous a rappelé de bons souvenirs et que vous connaissez maintenant ces opérations et opérateurs. Des erreurs courantes viennent de ces priorités opératoires non prises en compte par l'utilisateur.

SOMME

Que permet-elle ?

Elle permet l'addition de plusieurs nombres ou cellules.

Comment s'écrit-elle et quels paramètres ?

La fonction SOMME s'écrit de la façon suivante et prend un nombre d'arguments très variable.

=SOMME(100;250) Mais la plupart du temps, on ne connaît pas les nombres à additionner on utilise alors les coordonnées de cellules de cette façon :

=SOMME(E2;F4) On peut aussi additionner plusieurs cellules différentes ou même des plages de cellules. Pour plusieurs cellules on utilise le point-virgule (;) pour séparer les cellules. Lorsqu'il s'agit d'une plage de cellules, on entre la première cellule de la plage et la dernière cellule de cette même plage séparées par deux points (:). Pour vulgariser et bien retenir, le point-virgule (;) signifie "et", et les deux points (:) signifient "jusqu'à".

=SOMME(E2;F4;G6) pour calculer la somme des valeurs des cellules E2, F4 et G6. =SOMME(E2:E5) pour calculer la somme des valeurs des cellules E2, E3, E4 et E5.

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Dans la colonne B on a les formules entrées dans la colonne C et qui nous donnent les résultats de la capture d'écran.

Nous allons voir maintenant un exemple plus concret. Dans une équipe de handball, nous allons voir combien de buts chaque joueur a marqués (résultats fictifs). Voici ce que ça donne :

Nous venons de voir une utilisation concrète de la fonction SOMME mais elle est souvent combinée à d'autres fonctions. Vous savez quand même comment faire une somme de plusieurs cellules.

PRODUIT

Que permet-elle ?

Elle permet de multiplier plusieurs nombres ou cellules entre eux.

Comment s'écrit-elle et quels paramètres ?

La fonction PRODUIT s'écrit de la même façon que la fonction SOMME et fonctionne exactement de la même façon.

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Avec un exemple plus concret, on peut voir l'utilité de la fonction dans une facture par exemple et on peut combiner la fonction SOMME :

QUOTIENT

Que permet-elle ?

Elle permet de renvoyer la partie entière d'une division.

Comment s'écrit-elle et quels paramètres ?

La fonction QUOTIENT s'écrit de la façon suivante et prend deux paramètres : le diviseur et le dividende.

=QUOTIENT(100;25) Mais la plupart du temps, on ne connaît pas les nombres à diviser on utilise alors les coordonnées de cellules de cette façon :

=QUOTIENT(E2;F4)

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Avec un exemple plus concret, on peut voir l'utilité de la fonction dans le calcul de la répartition des denrées par élève, étant donné qu'il est difficile de distribuer des quarts de bonbons, il est préférable d'avoir des valeurs entières :

Simplifier ces fonctions

Nous venons de voir trois fonctions d'Excel qui sont très souvent utilisées et peuvent être simplifiées grâce aux opérateurs numériques que nous avons vus en introduction. Les voici :

Nous pouvons prendre comme exemple un bulletin de notes pour regrouper l'addition, la multiplication et la division. Pour calculer la moyenne d'un élève au bac, on calcule dans un premier temps le nombre de points que rapporte chaque matière en multipliant la note par le coefficient. Dans un second temps, on obtient le nombre total de points obtenus et le nombre de coefficients total. Enfin, pour calculer la moyenne on divise le nombre de points par le nombre de coefficients pour avoir la moyenne sur 20. Dans notre exemple, notre élève de terminale S spécialité physique-chimie (précision qui n'a aucun intérêt ! ), obtient la moyenne de 13,71 :

Voilà la partie la plus simple de ce tutoriel de terminée. Bah ouais, on a juste vu les fonctions de calcul de base… On attaque la suite avec une nouvelle fonction.

MOD

Que permet-elle ?

Elle permet de renvoyer le reste d'une division.

Comment s'écrit-elle et quels paramètres ?

La fonction MOD s'écrit de la façon suivante et prend deux paramètres (comme pour la fonction QUOTIENT en fait).

=MOD(100;18) Mais la plupart du temps, on ne connaît pas les nombres à diviser on utilise alors les coordonnées de cellules de cette façon :

=MOD(E2;F4)

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Pour ce qui est de l'exemple plus concret, on peut reprendre la liste des denrées par enfants. Mais ici, la colonne de résultat nous donne les restes après le partage équitable des denrées.

PGCD

Que permet-elle ?

Elle permet de renvoyer le plus grand dénominateur commun de plusieurs nombres ou cellules.

Comment s'écrit-elle et quels paramètres ?

La fonction PGCD s'écrit de la même façon que la fonction SOMME.

=PGCD(E2;F4;G6) pour calculer le PGCD des valeurs des cellules E2, F4 et G6. =PGCD(E2:E5) pour calculer le PGCD des valeurs des cellules E2, E3, E4 et E5.

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Vous ne voyez pas l'utilité du PGCD ? Voici un exemple : vous cherchez à couvrir une surface de 210 cm sur 135 cm avec des carreaux de carrelage. Il vous faut le moins de carreaux possible donc des carreaux les plus grands possible. Il faut aussi qu'on ait que des carreaux entiers. En effet, couper un carreau de carrelage, c'est pas facile… On cherche alors la taille d'un carreau (carré) de carrelage. On utilise alors le PGCD!

Petite pause

Nous allons faire une pause dans les fonctions pour présenter le concept de condition utile dans … beaucoup de fonctions et notamment dans les prochaines fonctions présentées. C'est une pause dans l'étude des fonctions, mais pas dans l'apprentissage ! Ce passage est très important, mais pas compliqué. Il faut bien comprendre tout ça pour utiliser à bon escient les fonctions qui comportent des conditions. Pour démarrer, on va expliquer ce qu'est une condition. Une condition commence toujours par un SI. Dans la vie courante, on peut dire : "Si je finis de manger avant 13h, je vais regarder le journal télévisé". On peut aussi aller plus loin en disant "Sinon, j'achète le journal". Pour Excel, c'est la même chose. On a une fonction SI présentée plus en détail dans la partie sur les fonctions logiques qui fonctionne de la même façon. Une condition et donc un "si", une valeur si c'est vrai et une valeur si c'est faut (qui correspond au sinon).

Pour faire une condition, il faut un critère de comparaison. Lorsque vous faites un puzzle, vous triez en premier les pièces qui font le tour pour délimiter le puzzle et aussi parce que le critère de comparaison entre les pièces est simple : sur les pièces du tour, il y a un côté plat. Donc lorsque vous prenez une pièce en main, vous comparez les côtés de la pièce à un côté plat et vous la mettez soit dans la boîte des pièces du tour soit dans les pièces qui seront retriées par la suite.

Dans Excel, ce critère de comparaison est soit une valeur, une cellule ou encore du texte. On compare les données d'une cellule à notre critère de comparaison et Excel renvoie VRAI si la comparaison est juste sinon Excel renvoie FAUX et Excel exécute ce que vous lui avez dit de faire en fonction de ce que renvoie la comparaison.

Pour comparer des valeurs numériques ou même du texte, on utilise des signes mathématiques. Le plus connu des signes de comparaison est égal à (=). Si les valeurs sont égales, alors fait ça sinon fait ci. Je vous donne la liste de tous les opérateurs utilisés dans Excel pour les comparaisons :

On peut donc avec ces opérateurs de comparaison, faire toutes les comparaisons possibles entre deux valeurs. On va alors s'entraîner à faire des comparaisons avec des données :

Alors, vous savez à quoi correspondent toutes ces comparaisons? La réponse juste en dessous.

Pour comparer à une valeur sans passer par la cellule, on entre la valeur telle quelle : A1=10. Par contre si on veut comparer à du texte, il faut alors le mettre entre guillemets : A1="Jean".

On va complexifier cette notion de condition et de comparaison au fil du cours dans les différentes fonctions.

SOMME.SI

Que permet-elle ?

Elle permet l'addition de plusieurs nombres ou cellules selon un critère de comparaison.

Comment s'écrit-elle et quels paramètres ?

La fonction SOMME.SI s'écrit de la façon suivante et prend 2 ou 3 paramètres.

=SOMME.SI(plage;critère;[somme_plage]) Le premier paramètre est la plage, c'est l'ensemble des cellules à comparer. Le second est le critère de comparaison, c'est à ce critère que la fonction va comparer les cellules de la plage. Enfin, le troisième paramètre est facultatif. S'il n'est pas présent, ce sont les valeurs de la plage qui sont additionnées. Si le paramètre somme_plage est renseigné, ce sont les cellules de cette plage qui sont additionnées.

Ce qui nous donne pour une écriture avec des données aléatoires:

=SOMME.SI(E2:E8;">10";F2:F8) On vient de voir que quand il y avait une comparaison, Excel renvoie VRAI si elle est juste sinon elle renvoie FAUX et qu'Excel exécute ce qu'il faut en fonction. Ici, l'instruction qui est faite par Excel après la comparaison, c'est la prise en compte ou non de la valeur. En fait si la condition est vraie, la valeur correspondante est prise en compte, sinon elle n'est pas prise en compte dans l'addition. On ne choisit pas ce que fait la fonction après avoir renvoyé VRAI ou FAUX, c'est la fonction qui s'en charge seule. On choisit seulement avec la fonction SI étudiée dans les fonctions logiques.

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Petite explication : la fonction regarde la cellule B2 et la compare au critère que nous avons entré, si cette valeur est plus grande que 320, alors j'intègre la cellule C2 dans l'addition, sinon je ne prends pas en compte la cellule C2. La fonction répète cela pour toutes les cellules de la plage de B2 à B10.

Pour voir si vous avez compris, on va refaire un exemple avec un cas concret. On va faire nos courses. Pour économiser, on achète que les articles à moins de 10€. Combien d'articles vais-je avoir à la sortie du magasin ?

Si le prix du savon est inférieur à 10€ alors j'achète les 2 savons (donc 2 articles). On fait la même chose pour toutes les lignes de la plage. On se retrouve à la sortie du magasin avec 14 articles. Alors que si nous avions tout acheté, nous aurions eu 16 articles.

Voilà ce qu'il y a à savoir sur la fonction SOMME.SI.

SOMMEPROD

Que permet-elle ?

Elle permet de comptabiliser des données en multipliant des matrices entre elles. Pour être clair, elle permet de compter le nombre d'entrées d'une liste selon des conditions, mais aussi d'additionner des cellules d'une liste selon des conditions.

Comment s'écrit-elle et quels paramètres ?

La fonction SOMMEPROD s'écrit de la façon suivante et prend un nombre très variable d'arguments.

=SOMMEPROD((plage1="critère1")(plage2="critère2")(plage3)*…) Les paramètres sont tous les mêmes, ce sont des plages de cellules. Elles peuvent prendre deux formes. Soit, comme dans l'exemple plage1 et plage2, elle est suivie d'un critère de comparaison soit, comme dans l'exemple plage3, elle ne possède pas de critère de comparaison. On peut compter le nombre de lignes où la plage1 (colonne 1) est égale à critère1 et où la plage2 (colonne 2) est égale à critère2. Si on insère une troisième plage (colonne 3), on additionne les cellules de cette plage.

Comme pour la fonction précédente, on utilise des critères de comparaison qui varient selon le type de données dans les plages. Voici un exemple plus concret pour expliquer (une exemple est plus parlant qu'un long discours) :

=SOMMEPROD((E2:E8="Jacques")(F2:F8="Janvier")(G2:G8)) La première plage est comparée au critère "Jacques", la seconde au critère "Janvier" et la troisième n'a pas de critère de comparaison. La fonction va donc regarder sur la première ligne, si la première cellule est égale à "Jacques" et si la seconde cellule de la ligne est égale à "Janvier". Si c'est le cas, alors la valeur de la troisième cellule de la ligne est prise en compte dans l'addition. S'il n'y avait pas de troisième plage, la fonction se contente de compter le nombre de lignes où les deux comparaisons sont vraies. On obtient ainsi le nombre de fois où Jacques et Janvier sont sur la même ligne.

Avec cette fonction on peut compter le nombre de lignes qui respectent les conditions ou alors additionner les cellules de chaque ligne. Nous allons voir un exemple concret directement pour que ce soit plus facile à comprendre.

Un exemple concret

Nous allons utiliser une feuille de données que je vous montre ici (et disponible dans le fichier téléchargeable) :

Ce tableau représente les ventes de chaque vendeur d'un magasin sur les trois premiers mois de l'année. Il y a 4 vendeurs (Jean, Pierre, Paul, Jacques).

Comment faire pour savoir le nombre de ventes de Paul au mois de Mars ? En utilisant la fonction SOMMEPROD pardi ! Pour cela il faut entrer la formule suivante :

=SOMMEPROD((A2:A31="Paul")*(B2:B31="Mars")) On obtient bien 3 ! Et oui Paul a fait 3 ventes au mois de Mars. Maintenant on cherche à savoir combien d'argent a rapporté Paul au mois de Mars. Il suffit de multiplier par la colonne "Montant" de cette manière :

=SOMMEPROD((A2:A31="Paul")(B2:B31="Mars")(C2:C31)) Tada ! On obtient donc 2230. En effet, la fonction a effectué le calcul suivant : 840+660+730=2230. C'est top non ? On peut faire plein de combinaisons avec cette formule ! Mais attention il y a quelques règles à respecter :

Je vous propose d'autres exemples pour bien comprendre et voir un peu ce que l'on peut faire avec cette fonction.

Exemple 1 : compter le nombre de ventes réalisées par Jean (on peut aussi réaliser cette opération avec la fonction NB.SI) :

=SOMMEPROD((A2:A31="Jean")*1) On obtient ainsi : 8. Cet exemple n'est pas le meilleur pour montrer la puissance de la fonction, mais elle montre qu'on n'est pas obligé d'avoir beaucoup de paramètres compliqués.

Exemple 2 : compter le nombre de ventes supérieures à 600€ au mois de Janvier :

=SOMMEPROD((B2:B31="Janvier")*(C2:C31>600)) On obtient ainsi : 2. On peut ainsi combiner les conditions pour prendre les valeurs comprises entre 200 et 600 par exemple.

Exemple 3 : totaliser la somme accumulée grâce à Pierre aux mois de Janvier et Mars :

=SOMMEPROD((A2:A31="Pierre")((B2:B31="Janvier")+(B2:B31="Mars"))(C2:C31)) On obtient ainsi : 2760.

Pour synthétiser ce tableau, on peut créer ces deux tableaux :

Dans chaque cellule non grisée, on a des fonctions SOMMEPROD. Je vous laisse vous entraîner en essayant de reproduire ces tableaux. Si vous avez des questions, demandez-moi dans les commentaires ou par MP. Pour les cellules grisées, on peut utiliser la fonction SOMME tout simplement. Je propose, pour bien apprivoiser la fonction étudiée, de l'utiliser pour obtenir les mêmes résultats qu'avec la fonction SOMME. Vous en êtes largement capable, j'ai confiance en vous ! .

Nous en avons fini avec la fonction SOMMEPROD et j'espère que vous avez compris. Elle est vraiment très puissante et utile pour synthétiser des tableaux comme on vient de le faire !

PI

Que permet-elle ?

Elle permet de renvoyer la valeur de pi.

Comment s'écrit-elle et quels paramètres ?

Elle s'écrit de la façon suivante mais ne demande aucun paramètre :

=PI()

Un exemple d'utilisation

On cherche à calculer le périmètre et l'aire de différents disques selon leur rayon :

RACINE

Que permet-elle ?

Elle permet de calculer la racine carrée d'un nombre ou d'une cellule.

Comment s'écrit-elle et quels paramètres ?

Elle ne prend qu'un paramètre, un nombre ou une cellule.

=RACINE(100) =RACINE(E2)

Un exemple d'application

En course d'orientation, je dois aller du point A au point B. Je connais la distance à vol d'oiseau entre ces deux points. Par contre, le carré au centre ne me permet pas d'aller tout droit c'est une forêt de buisson. Je dois donc calculer la distance à parcourir en prenant le chemin (trait noir).

On utilise alors le fameux théorème de Pythagore qui nous dit que AB²+AC²=BC² lorsque le triangle est rectangle en A. Ici, nous avons un carré donc les deux segments sont de mêmes longueurs et 2x²=AB². Il faut alors résoudre cette petite équation. 2x étant la distance à parcourir. Voici la réponse grâce à Excel :

ARRONDI

Que permet-elle ?

Elle permet d'arrondir le résultat d'un quotient par exemple au nombre significatif que l'on veut.

Comment s'écrit-elle et quels paramètres ?

Elle prend deux paramètres, le chiffre à arrondir et le nombre de décimal à afficher. On l'écrit ainsi :

=ARRONDI(valeur;nombre_de_décimale) =ARRONDI(100,029384;2) On obtient alors la valeur 100,02. C'est très pratique au lieu de formater les cellules avec deux décimales avant de faire les calculs.

Un exemple théorique et un exemple concret

Pour vous montrer comment on utilise la fonction, on l'applique à des données aléatoires.

On vient de voir dans l'exemple que l'on peut appliquer des valeurs négatives. Vous avez sûrement deviné que ça permet d'arrondir avant la virgule et donc à la dizaine (pour -1) près ou à la centaine (pour -2) près.

Vous avez vraiment besoin d'un exemple concret pour cette fonction ? Allez, pour le fun et parce que je suis sympa, je vous en propose un. De plus la répétition permet l'apprentissage donc ça ne vous fera pas de mal ! . J'aime bien la bouffe alors encore un exemple sur des courses ! .

ARRONDI.INF et ARRONDI.SUP

Que permettent-elles ?

Comme la fonction ARRONDI, elles permettent d'arrondir un chiffre selon un nombre de décimales ou, en utilisant les nombres négatifs, d'arrondir avant la virgule. Pour la fonction ARRONDI.INF on arrondit à l'inférieur alors qu'avec ARRONDI.SUP on arrondit au supérieur. On ne se préoccupe plus de savoir ce qui suit la partie tronquée.

Comment s'écrivent-elles et quels paramètres ?

De la même façon que la fonction ARRONDI. Elles prennent 2 paramètres, le nombre à arrondir et le nombre de décimales.

=ARRONDI.INF(valeur;nombre_de_décimale) =ARRONDI.SUP(valeur;nombre_de_décimale) Je ne vais pas vous en dire plus sur cette fonction puisque c'est la même chose que pour la fonction ARRONDI. Je ne peux m'empêcher de vous proposer un exemple quand même :

ALEA.ENTRE.BORNES

Que permet-elle ?

Elle permet de renvoyer un nombre entier aléatoire qui est situé entre deux bornes spécifiées par l'utilisateur (c'est à dire vous).

Comment s'écrit-elle et quels paramètres ?

Elle prend deux paramètres obligatoires, la borne minimale (la valeur sera supérieure ou égale à cet argument) et la borne maximale (la valeur sera supérieure ou égale à cet argument).

=ALEA.ENTRE.BORNES(borne_minimale;borne_maximale) Avec des valeurs aléatoires, on a ceci :

=ALEA.ENTRE.BORNES(0;100) Si vous entrez cette formule chez vous, vous n'obtenez jamais le même résultat. C'est pourquoi je ne vous donne pas ce que j'ai parce que ce n'est pas forcement la même que vous. Mais on peut aussi spécifier des cellules (lorsque l'on entre des valeurs dans les cellules au lieu de modifier la formule) comme ceci :

=ALEA.ENTRE.BORNES(E2;F2)

Un exemple que vous pouvez adapter

Je vous présente ici un exemple avec différentes bornes totalement aléatoires et vous n'aurez pas les mêmes valeurs que moi. D'ailleurs, si vous recopiez la formule avec les mêmes bornes, vous n'aurez pas la même valeur.

Une combinaison avec la fonction ARRONDI

Pour obtenir un nombre aléatoire parmi les dizaines de 0 à 100. On cherche à avoir 0, 10, 20, 30, 40, 50, 60, 70, 80, 90 ou 100. Comment faire ? En combinant la fonction ARRONDI et la fonction ALEA.ENTRE.BORNES! Voici la réponse :

Vous pouvez donc adapter cet exemple, mais aussi combiner d'autres fonctions entre elles !

Les fonctions Logiques

Dans cette seconde partie, nous allons étudier les fonctions « Logiques » d'Excel.

Ou à partir dur ruban et de l'onglet « Formules », de la rubrique « Bibliothèque de fonctions » et de cliquer sur « Insérer une fonction ». Une fenêtre s'ouvre, sélectionner dans le menu déroulant « Logique ».

Je vais vous proposer ici l'intégralité des fonctions de Logique. Ne vous inquiétez pas, ce n'est pas pour ça que c'est une grosse partie, car trois fonctions ne seront pas aussi détaillées que les autres. Il n'y a pas beaucoup de fonctions dans cette catégorie.

Pour suivre avec moi cette sous-partie et vous exercer de votre côté, je vous propose de :

Télécharger le fichier fonctions_logiques.xlsx

Ce classeur Excel contient tous les exemples utilisés dans cette partie. Il y a la base des exemples, à vous d'entrer les formules.

SI

Que permet-elle ?

Elle permet de renvoyer une valeur ou une autre selon une condition. Tient, une condition, on en a déjà parlé. En effet, on dans la petite pause effectuée lors de la partie précédente, on a étudié les conditions, les critères de comparaison et les opérateurs permettant ces comparaisons. La fonction renvoie VRAI si la condition est respectée et FAUX si elle ne l'est pas.

Comment s'écrit-elle et quels paramètres ?

Cette fonction prend un paramètre obligatoire : le test logique (c'est une autre façon d'appeler la condition). Puis deux paramètres optionnels qui sont très souvent renseignés sinon la condition n'est pas très utile.

=SI(test_logique;[valeur_si_vrai];[valeur_si_faux]) Le premier paramètre est donc le test logique tel que : C3=126. Ensuite, il faut mettre, entre guillemets si l'on souhaite mettre du texte, les valeurs si le test est bon tout d'abord puis s'il est faux. On a vu que la fonction renvoyait VRAI ou FAUX si la condition était respectée ou non. De ce fait, si la fonction renvoie VRAI, elle affiche alors la valeur si VRAI et affiche la valeur si FAUX si la fonction renvoie FAUX.

=SI(G23=I8;A2;B7)

En ce qui concerne les deux autres paramètres (valeur_si_vrai et valeur_si_faux), on peut les renseigner entre guillemets pour du texte, on peut mettre une valeur de cellule, on peut également décider de ne rien rentrer si la condition n'est pas respectée par exemple. Pour cela on utilise le double guillemets comme ceci : "". Ainsi, on affiche du texte qui n'a aucun caractère, donc on n'affiche rien.

Une autre petite information pour terminer avant les exemples, si l'on veut par exemple savoir si une valeur est contenue dans un intervalle (plus petit que mais aussi plus grand que), il faudrait alors que C3 soit plus petit que 100 mais aussi plus grand que 10. Dans ce cas, on peut utiliser une fonction SI dans une fonction SI de cette façon :

=SI(C3<100;SI(C3>10;valeur_si_vrai;valeur si C3 n'est pas plus grand que 10);valeur si C3 n'est pas plus petit que 100) Ainsi, vous pouvez spécifier du texte selon si la valeur est trop petite ou trop grande. Ça peut être intéressant pour alerter l'utilisateur du classeur pourquoi la valeur entrée n'est pas conforme.

Des exemples d'applications pour pratiquer et apprendre

Dans un premier temps, nous allons utiliser comme depuis le début de ce cours, des données aléatoires puis dans un second temps un exemple concret.

Voilà pour ce qui est des valeurs aléatoires. Vous pouvez donc jouer avec pour vous les approprier.

Je vous propose un exemple de l'utilisation de la fonction SI imbriquée. On a une liste de notes obtenues au baccalauréat par des élèves. On leur attribut alors une mention (premier tableau) en fonction de la note. J'ai ajouté une coloration conditionnelle pour bien différencier les niveaux. La formule de la cellule C11 est notée sous le tableau.

Cette fonction SI très utilisée dans Excel est souvent combinée à d'autres fonctions que nous allons voir par la suite. Elle est aussi intégrée dans d'autres fonctions comme celle vues précédemment : SOMME.SI ou SOMMEPROD.

ET et OU

Que permettent-elles ?

Ces deux fonctions permettent de faciliter l'écriture des fonctions SI lorsque vous avez plusieurs conditions à respecter. La fonction ET permet de dire que deux ou plusieurs conditions soient respectées pour que la fonction renvoie VRAI et la fonction OU permet de dire que seulement une des deux ou plusieurs conditions doivent être respectées pour que la fonction renvoie VRAI.

Comment s'écrivent-elles et quels paramètres ?

Ces deux fonctions prennent un paramètre obligatoire et peuvent en prendre plusieurs si on veut plusieurs conditions dans ces fonctions. Voici la syntaxe :

=ET(condition1;[condition2];…) =OU(condition1;[condition2];…) Les conditions sont en fait des tests logiques vu lors de la fonction précédente et fonctionne exactement de la même façon. On va plutôt se pencher sur la différence entre ET et OU.

La fonction ET exige que toutes les conditions soient vraies pour renvoyer VRAI, si une seule des conditions est fausse, alors la fonction renvoi FAUX. La fonction OU exige qu'une seule des conditions soit vraie pour renvoyer VRAI.

Vous avez compris ? Pas trop n'est-ce pas. Et bien on va voir toutes les possibilités avec deux conditions avec la fonction ET et deux conditions avec la fonction OU. Pour chaque ligne, on donne ce que renvoie la condition 1 et ce que renvoie la condition 2 de la fonction puis le résultat que renvoie la fonction. Des exemples très simples sont mentionnés pour vous aider à comprendre.

Vous avez compris l'intérêt de ces fonctions ? Je vous vois ne pas osez, mais si allez y dites-le ! Oui, oui, on va les utiliser en les combinant avec la fonction SI pardi ! On écrit alors :

=SI(ET(condition1;condition2);valeur_si_vrai;valeur_si_faux) La fonction affiche la valeur_si_vrai si la fonction ET renvoie VRAI et la valeur_si_faux si la fonction ET renvoie FAUX.

Si tu te poses cette question, remontes un peu la page et lis le passage ! On vient d'expliquer quand est-ce que la fonction ET renvoyait VRAI et quand elle renvoyait FAUX. C'est le même fonctionnement avec la fonction OU.

Différents exemples d'application

Pour donner un exemple de l'utilisation de la fonction ET, on va utiliser un tableau de recrutement de mannequin. Pour qu'elle soit admissible, une fille doit mesurer au moins 172 cm, peser au maximum 60 kg et avoir un tour de poitrine de 85. Voici le résultat :

Un autre exemple très simple pour finir sur ces fonctions à propos de la fonction OU. Elle analyse si l'utilisateur est un utilisateur Windows ou non.

SIERREUR

Que permet-elle ?

Elle permet d'afficher une valeur "par défaut" dans une cellule si le calcul initialement prévu provoque une erreur. Par exemple, une division par 0 va afficher #DIV/0!, on va alors utiliser cette fonction pour afficher le message que l'on veut.

Comment s'écrit-elle et quels paramètres ?

Cette fonction ne prend que deux paramètres, mais les deux sont obligatoires. Le premier est la valeur à afficher normalement et la seconde, la valeur à afficher en cas d'erreur de la première.

=SIERREUR(valeur;valeur_si_erreur) Cette fonction est très simple à comprendre et permet de ne plus afficher les vilains messages d'erreur d'Excel et d'expliquer plus explicitement les erreurs.

=SIERREUR(E2;E3) Avec en E2, une division par 0 et en E3 le texte suivant : "Vous essayer de diviser un nombre par 0". L'utilisateur du classeur sait alors ce qu'il doit corriger.

Un exemple

Pour une division par 0 :

Il existe d'autres types d'erreurs décrites ici par Etienne-02.

Nous en avons fini avec les fonctions de la catégorie Logique. Elles ne sont pas nombreuses et je ne vous ai pas présenté les fonctions VRAI, FAUX et NON qui renvoient respectivement, VRAI, FAUX et l'inverse de la valeur logique de l'argument (VRAI pour FAUX et FAUX pour VRAI). Pour les deux premières fonctions, il n'y a pas d'argument. Pour la dernière, elle prend comme paramètre une valeur.

Avançons dans notre domptage des fonctions Excel avec la catégorie suivante.

Les fonctions de Recherche et Référence

Dans cette partie nous allons étudier les fonctions de la catégorie « Recherche et référence » d'Excel.

Ou à partir dur ruban et de l'onglet « Formules », de la rubrique « _» et de cliquer sur « <italique>Insérer une fonction</italique> ». Une fenêtre s'ouvre, sélectionner dans le menu déroulant des catégories : « <italique>Recherche & Matrices</italique> ».

Comme dans les autres catégories, nous centrerons notre étude sur les fonctions utiles que vous ne connaissez peut-être pas.

Pour suivre avec moi cette sous-partie et vous exercer de votre côté, je vous propose de :

Télécharger le fichier fonctions_recherche_et_reference.xlsx

Ce classeur Excel contient tous les exemples utilisés dans cette partie. Il y a la base des exemples, à vous d'entrer les formules.

COLONNE et LIGNE

Que permettent-elles ?

Nous traitons les deux fonctions en même temps, car elles ont le même rôle, mais dans un sens différent. Vous l'aurez deviné, la fonction COLONNE dans le sens vertical et LIGNE dans le sens horizontal. Ces fonctions permettent de renvoyer le numéro de la colonne ou celui de la ligne selon la fonction d'une cellule ou d'une plage de cellule ou même le nom d'une plage de cellule.

On peut nommer des plages de cellule sur Excel pour éviter d'avoir à la référencer avec les lettres et chiffres des colonnes. Ainsi, une plage de cellule allant de la cellule A1 à la cellule C5 est appelée A1:C5. Lorsque cette plage est sélectionnée, faites un clic droit et cliquez sur Nommer une plage….

Une fenêtre s'ouvre, il suffit de remplir le champ Nom et de cliquer sur OK.

Vous remarquez que, à gauche de la barre de formule, apparaît le nom de votre plage. C'est ce nom de plage que l'on peut envoyer à la fonction COLONNE ou LIGNE pour connaître le numéro de colonne ou ligne où elle se trouve. C'est le numéro de la première colonne ou ligne de la plage qui est renvoyé.

On va pouvoir passer au détail de ces fonctions.

Comment s'écrivent-elles et quels paramètres ?

Les deux fonctions ne prennent qu'un paramètre qui n'est pas obligatoire. Si la fonction n'a pas de paramètres, elle renvoie le numéro de ligne ou colonne de la cellule dans laquelle elle est entrée. Si elle a un paramètre, elle renvoie le numéro de ligne ou colonne de ce paramètre (cellule, plage ou nom de plage).

=COLONNE(Ma_plage) =COLONNE() =LIGNE(Ma_plage) =LIGNE()

Quelques exemples

On utilise cette fonction surtout pour des noms de plage puisque pour les autres, les coordonnées de cellule permettent de donner ces renseignements. Par contre, nous avons des chiffres pour les colonnes et non les lettres utilisées par Excel. Il faudra alors connaître la correspondance entre les chiffres et les lettres.

COLONNES et LIGNES

Que permettent-elles ?

Euh regarde bien, il y a un "s" à chaque fonction, elles ont donc une autre fonction. Je l'admets elles fonctionnent de la même façon que les fonctions précédentes. Mais ces fonctions renvoient la largeur ou la hauteur d'une plage de cellule. C'est intéressant lorsque vous n'avez pas de renseignement sur la plage, par exemple lorsque l'on dispose simplement du nom de la plage.

Comment s'écrit-elle et quels paramètres ?

Elles ne prennent qu'un paramètre, mais cette fois-ci il est obligatoire sinon la fonction ne fonctionne pas.

=COLONNES(plage) =LIGNES(plage)

Quelques exemples

En reprenant le tableau des plages précédentes, on peut connaître la largeur et la hauteur de chaque plage.

En combinant ces 4 fonctions (COLONNE, LIGNE, COLONNES, LIGNES), on peut transformer le nom de la plage en coordonnées de cellule. Dans cet exemple on cherche alors à trouver les coordonnées de chaque plage.

Ma générosité me perdra, mais là je vous accorde que des petites explications ne seraient pas de trop.

Ça ne vous suffit pas ? Je vous l'accorde, c'est encore flou. On va passer une par une les lignes de ce dernier tableau. Chaque ligne correspond à une colonne du tableau précédent. Dans la première colonne, on cherche le numéro de la colonne de la première cellule de la plage avec la fonction COLONNE. Dans la colonne D, on cherche grâce à la fonction RECHERCHEV (étudiée un peu plus loin dans ce cours), la lettre correspondante à ce chiffre dans l'alphabet. Voici le tableau que l'on utilise pour la recherche :

Je vous expliquerai cette fonction plus en détail au moment voulu. Ici vous voyez qu'on affiche une lettre au lieu d'un chiffre grâce à la fonction. Dans la colonne suivante, on affiche le numéro de ligne de la première cellule avec la fonction LIGNE. On obtient alors les coordonnées de la première cellule de la plage. Grâce à la fonction CONCATENER, que nous verrons plus tard, on peut afficher les coordonnées de la cellule. On travaille ensuite sur la plage en rapportant la largeur et la hauteur de la plage grâce aux fonctions COLONNES et LIGNES respectivement.

Puis dans les quatre colonnes suivantes, on cherche où se termine la plage en ayant les coordonnées de la dernière cellule. Pour le numéro de colonne, on ajoute à la position de la première cellule, la largeur de la plage. On enlève ensuite 1 puisque sinon on additionne la largeur de la plage et le numéro de colonne donc on se décale d'une colonne en trop. On rattrape cette erreur en faisant "-1". On utilise la même fonction RECHERCHEV pour trouver la lettre correspondante comme pour la première cellule. Pour le numéro de ligne, on additionne le numéro de ligne de la première cellule et la hauteur de la plage et on enlève 1 pour les mêmes raisons que la largeur. On obtient ainsi, avec la fonction CONCATENER, les coordonnées de la dernière cellule de la plage.

Enfin dans la dernière colonne, on utilise de nouveau la fonction CONCATENER pour assembler les coordonnées de la première cellule et de la dernière cellule. On obtient ainsi les coordonnées de la plage.

J'espère que ces explications vous ont aidées à comprendre malgré que vous ne connaissiez pas toutes les fonctions. Mais je voulais vous montrer l'utilité de ces fonctions pour connaître les coordonnées d'une plage sans regarder le détail des plages.

RECHERCHEV

Que permet-elle ?

Comme on l'a vu un peu dans la description précédente, elle permet de rechercher une valeur dans un tableau, plage de cellule ou matrice et de renvoyer une valeur associée. Elle cherche dans la première colonne et renvoie une valeur d'une des autres colonnes sur la même ligne.

Comment s'écrit-elle et quels paramètres ?

Cette fonction prend plusieurs paramètres, trois obligatoires et un facultatif. Voici comment elle s'écrit :

=RECHERCHEV(valeur_cherchée;plage;numero_colonne;[valeur_proche])

• La valeur cherchée peut être une valeur chiffrée, du texte (qui sera alors entre guillemets) ou une cellule (et donc la valeur qu'elle contient). Elle doit être obligatoirement dans la première colonne sinon la cellule contenant la fonction RECHERCHEV vous renvoie l'erreur suivante : <italique>#NOM?</italique>.
• Ensuite on spécifie la plage dans laquelle on fait la recherche, soit en écrivant les coordonnées des cellules (exemple : A1:B16) soit en spécifiant le nom de la plage (exemple : ma_plage).
• Enfin, le troisième paramètre concerne le numéro de la colonne dans laquelle la fonction doit chercher la valeur à retourner. Il est donc inutile d'indiquer la première colonne puisque c'est dans celle-ci que la recherche est faite.
• En ce qui concerne le paramètre facultatif, il peut prendre que deux valeurs différentes : VRAI ou FAUX. S'il n'est pas spécifié, il a pour valeur VRAI. Quand il vaut VRAI, la première colonne doit être dans l'ordre croissant et la fonction recherche une valeur approximative. Quand il vaut FAUX, la fonction cherche la valeur exacte. Si la fonction ne trouve pas la valeur exacte, elle renvoie : <italique>#N/A</italique>.

Des exemples d'applications

Un premier exemple où l'on recherche un nombre et renvoi un autre nombre. Dans cet exemple, on a un barème où sont représentés des temps dans la colonne de gauche et la note correspondante dans la seconde colonne. Ainsi dans le tableau de droite, on entre le temps de chaque élève en face de son nom et la fonction se charge de trouver elle-même la note correspondante.

Ici on ne cherche pas une valeur exacte, mais dans quel intervalle se trouve notre valeur pour lui attribuer une note. Il n'y a donc qu'une valeur qui change entre les différentes formules, c'est la valeur recherchée.

L'exemple suivant n'est pas très révélateur parce que nous n'avons pas beaucoup de données (je ne vais pas faire l'inventaire de toutes les villes de France ! ). Mais imaginez que vous ayez une liste interminable de données. Vous pouvez, grâce à la fonction RECHERCHEV, faire la phrase que vous voulez en entrant juste le nom de la ville dans la cellule D11 (vous pouvez même faire une liste déroulante pour choisir la ville, je vous présente cette solution juste après).

Nous avons défini la plage de recherche aux cellules B2:F9 que l'on a renommée <italique>villes</italique>. Nous n'avons pas pris la première colonne en compte pour que la recherche se fasse dans la colonne <italique>Ville</italique>.

Voici les deux formules entrées dans les cellules B13 et B14 :

=CONCATENER(D11;" est une ville de ";RECHERCHEV(D11;villes;3);" habitants qui s'appellent les ";RECHERCHEV(D11;villes;5);".") =CONCATENER("La densité est de ";RECHERCHEV(D11;villes;2);" habitant/km². Elle fait partie de la région ";RECHERCHEV(D11;villes;4);".") Nous allons maintenant mettre une liste déroulante pour choisir la ville. Je vais, par la même occasion, vous montrer comment faire une liste déroulante. On commence maintenant par l'explication du fonctionnement.

Lorsque l'on choisit une ville dans la liste déroulante, celle-ci est rattachée à une cellule. On peut alors chercher la ville dans notre tableau pour avoir les informations sur la ville.

Pour insérer la liste déroulante, placer votre curseur sur la cellule D11. Puis, dans l'onglet Données, dans la rubrique Outils de données, cliquer sur Validation des données.

Une fenêtre s'ouvre alors. Nous allons renseigner les champs et valider puis la liste sera créée. Dans la fenêtre qui s'ouvre, l'onglet Options est présélectionné sinon faites-le. Puis dans la liste Autoriser, sélectionnez Liste.

Laisser les paramètres par défaut. Pour la source, sélectionner la liste des villes que vous voulez voir apparaître. Pour cela placer le curseur dans la case Source et sélectionner les villes puis appuyer sur OK.

Vous avez maintenant une magnifique liste déroulante. Votre utilisateur ne pourra pas se planter dans l'orthographe de la ville et faire planter sa recherche.

Vous pouvez alors sélectionner la ville que vous voulez et les informations apparaissent automatiquement. Sympa non ?

Nous en avons fini avec la fonction RECHERCHEV. Cette fonction est très utile dans l'utilisation d'Excel. Alors, entraînez-vous et relisez ce passage si besoin.

RECHERCHEH

Que permet-elle ?

Cette fonction permet de faire exactement la même chose que la fonction RECHERCHEV mais dans l'autre sens, c'est à dire à l'horizontale.

Comment s'écrit-elle et quels paramètres ?

Comme je vous l'ai déjà dit, elle fonctionne exactement de la même façon que la fonction RECHERCHEV. Elle prend le même nombre de paramètres (3 obligatoires et un facultatif). Je vous la présente ici :

=RECHERCHEH(valeur_cherchée;plage;numero_ligne;[valeur_proche]) Je vais vous présenter un exemple juste pour vous entraîner. C'est juste que le sens s'inverse, on passe de la verticale à l'horizontale.

Un exemple

Comme pour l'exemple sur les villes, ces fonctions de recherche sont très utiles lorsque vous avez de longues listes ou alors des listes déroulantes qui renvoient des nombres et donc vous cherchez l'élève correspondant à ce nombre.

Je ne détaille pas cette capture d'écran, les formules sont indiquées. C'est la même manipulation que la fonction précédente. La plage A1:I6 est appelée <italique>notes</italique>.

RECHERCHE (forme vectorielle)

Que permet-elle ?

Elle permet de rechercher une valeur dans une colonne ou une ligne (c'est ce que l'on appelle un vecteur) et de renvoyer la valeur correspondante contenue dans un autre vecteur (ligne ou colonne) de même taille. Les données du vecteur dans lequel la fonction cherche doivent être triées dans l'ordre croissant.

Comment s'écrit-elle et quels paramètres ?

Cette fonction a trois paramètres obligatoires. Le premier est la valeur cherchée, le deuxième est le vecteur de recherche et le troisième est le vecteur de résultat. On note alors la fonction ainsi :

=RECHERCHE(valeur_cherchée;vecteur_de_recherche;vecteur_de_résultat) Souvenez-vous que le vecteur est soit une ligne soit une colonne et que les vecteurs de recherche et de résultat doivent être de même longueur.

Cette fonction est utilisée lorsque les vecteurs ne sont pas au même niveau dans le tableur ou si vous avez pour chaque vecteur, un nom de plage. Il suffit de noter les noms de plage correspondant pour effectuer la recherche. Cela remplace la fonction RECHERCHEV ou RECHERCHEH et devoir connaître le numéro de colonne pour le résultat.

Des exemples, encore des exemples

Reprenons notre tableau sur les villes de France. On va effectuer les mêmes recherches, mais en appliquant des noms de plage à chaque colonne.

Voici l'exemple, je vous présente la formule ensuite :

On a donc utilisé l'exemple de Marseille. Dans la cellule D11 on a donc la valeur Marseille. La formule qui nous permet d'avoir la phrase de présentation est la suivante :

Ne vous préoccupez pas de la fonction CONCATENER pour l'instant nous la verrons en temps voulu. Attardons-nous plutôt sur les fonctions RECHERCHE. Nous avons donc à chaque fois la même valeur recherchée, le même vecteur de recherche, mais c'est le vecteur de résultat qui change. Le nom des plages nous permet de bien nous repérer dans cette longue formule et la fonction RECHERCHE permet à cette formule d'être plus lisible.

Nous avons vu que la fonction RECHERCHE (forme vectorielle) est de la même utilité que les fonctions RECHERCHEV et RECHERCHEH mais dans des contextes un peu différents.

RECHERCHE (forme matricielle)

Que permet-elle ?

Elle permet de chercher une valeur dans une matrice (un tableau) et de renvoyer la valeur correspondante de la dernière ligne ou colonne. Elle combine donc les fonctions RECHERCHEV et RECHERCHEH mais elle ne permet de renvoyer la valeur que de la dernière colonne ou ligne.

Comment s'écrit-elle et quels paramètres ?

Elle ne prend que deux paramètres obligatoires. Le premier la valeur recherchée et le second la matrice dans laquelle il faut faire la recherche. On a donc cette syntaxe :

=RECHERCHE(valeur_recherchée;matrice)

Bonne question. Déjà, vous savez que la recherche se fait toujours dans le premier vecteur (colonne ou ligne). S'il y a plus de colonnes que de lignes, alors la recherche se fait dans la première ligne (recherche horizontale). S'il y a plus de ligne que de colonne alors c'est l'inverse, la recherche se fait dans la première colonne (recherche verticale). S'il y a autant de lignes que de colonnes, la matrice est donc un carré, la recherche se fait dans la première colonne (recherche verticale).

On voit donc que si la matrice a plusieurs lignes et plusieurs colonnes, on ne peut avoir de renseignement que sur la dernière colonne ou la dernière ligne. Toutes les lignes ou colonnes entre les deux ne peuvent être trouvées. Il faut alors changer de fonction (RECHERCHEV, RECHERCHEH ou RECHERCHE forme vectorielle).

Cette fonction est utilisée lorsque vous n'avez que deux colonnes ou deux lignes. Comme ça, vous n'avez pas à spécifier la colonne de résultat (le troisième paramètre des fonctions RECHERCHEV et RECHERCHEH).

Un exemple

Pour cet exemple, je vous propose un exemple qui va peut-être être abstrait pour vous. On a enregistré toutes les minutes la fréquence cardiaque (FC) d'un marcheur pendant 90 minutes. On a alors un tableau de deux colonnes (une pour le temps en minutes et l'autre pour la FC en battement par minute = BPM) et 90 lignes.

Si on utilise la fonction recherche, elle se fera dans le sens vertical puisqu'on a beaucoup plus de lignes que de colonnes. On peut alors connaître la FC à un moment précis de la marche avec la fonction RECHERCHE forme matricielle. La plage est appelée <italique>FC</italique> (A2:B91).

Voici les résultats que l'on peut en tirer avec la fonction RECHERCHE (forme matricielle) :

TRANSPOSE

Que permet-elle ?

Elle permet de mettre sur une ligne des données en colonne et inversement. Elle est utile lorsque vous avez un tableau à double entrée à faire et que les entrées verticales et horizontales sont identiques. Quand on modifie la plage à transposer, l'autre plage se modifie aussi. Nous verrons cela dans les exemples.

Comment s'écrit-elle et quels paramètres ?

Cette fonction est particulière puisqu'elle prend la forme d"une matrice. Vous vous demandez sûrement ce qu'est une matrice… On a déjà utilisé le terme puisque c'est un tableau lorsque l'on regarde la matrice, elle a la même forme qu'un tableau (un nombre de ligne et de colonne défini). C'est en fait une plage de cellules spéciales qui varie sans que l'on modifie directement cette plage. On ne va pas s'éterniser sur la définition. Nous allons l'illustrer plus tard.

{=TRANSPOSE(plage)} On voit que des crochets apparaissent, mais ils ne sont pas entrés par le clavier. Ceux-ci sont entrés par une combinaison de touches que nous allons voir. Pour transposer la plage A2:A21 (donc une colonne) nous allons écrire la formule suivante dans la cellule B1 :

=TRANSPOSE(A2:A21)

Dans la cellule s'affiche une erreur : #VALEUR!. C'est normal puisque la plage (la ligne) n'a pas la même taille que l'autre plage (la colonne) en nombre de cellules. Pour la transposer, on va alors sélectionner le nombre de cellules voulu à partir de B2 c'est-à-dire 20 cellules (jusqu'à la colonne U).

Ensuite on appuie sur la touche F2. Le curseur se place alors dans la cellule de la formule (la première cellule de la sélection). Il faut alors appuyer simultanément sur CTRL+SHIFT+Entrée. La touche SHIFT est aussi appelée touche MAJ. Voilà, on obtient donc une ligne qu'avec des zéros. Bah oui parce qu'on a rien mis dans la colonne ! . On va donc maintenant faire un exemple pour bien voir ce que ça donne.

Si vous voulez exploiter les données ensuite, faites un copier-coller et un collage spécial en sélectionnant Valeurs dans la petite fenêtre qui s'ouvre. Pour supprimer une matrice, sélectionner entièrement la matrice et appuyer sur supprimer. Ne pas prendre qu'une cellule sinon vous aurez un message d'erreur très difficile à enlever.

Deux exemples

Un exemple dans le football, on cherche à avoir un tableau qui résume les scores de l'année en Ligue 1. On entre tout d'abord la liste des équipes dans les cellules A1 à A21 comme ceci :

Pour pouvoir résumer les scores, on va alors entrer les mêmes équipes dans les cellules de B1 à U1 avec la fonction TRANSPOSE. Sélectionner les cellules B1 à U1 puis appuyer sur F2. Ensuite entrer la formule suivante :

=TRANSPOSE(A2:A21) Puis appuyer sur la combinaison de touches vue auparavant : Ctrl+Shift+Entrée. Vous obtenez alors un tableau à double entrée avec les mêmes noms d'équipes.

Ensuite vous pouvez trier la liste dans l'ordre alphabétique des équipes dans la colonne A et la liste des équipes de la ligne 1 se mettra à jour toute seule. Vous pouvez aussi modifier le nom d'une équipe mais, comme je vous l'ai dit, que dans la colonne A (la plage à transposer).

Pour un autre exemple, je vous laisse aller voir celui-ci qui est très bien fait.

Nous en avons fini avec les fonctions de Recherche et référence. Il en existe d'autres, vous pouvez les découvrir en les testant mais elles sont moins utilisées._

Les fonctions Statistiques

Dans cette partie, nous allons étudier les fonctions « Statistiques » d'Excel. Elles se trouvent ici : A partir du ruban et de l'onglet « Formules », de la rubrique « Bibliothèque de fonctions » et dans la catégorie « Plus de fonction » puis « Statistiques » :

Une fenêtre s'ouvre, sélectionner dans le menu déroulant de la catégorie : « Statistiques ».

Comme dans les autres catégories, nous centrerons notre étude sur les fonctions utiles que vous ne connaissez peut-être pas.

Pour suivre avec moi cette sous-partie et vous exercer de votre côté, je vous propose de :

Télécharger le fichier fonctions_statistiques.xlsx

Ce classeur Excel contient tous les exemples utilisés dans cette partie. Il y a la base des exemples, à vous d'entrer les formules.

Dans cette catégorie, il y a beaucoup de fonctions très poussées sur les statistiques. Je décrierais que les fonctions de bases que vous serez amené à utiliser et non les fonctions complexes comme la LOI.KHIDEUX.

MAX et MIN

Que permettent-elles ?

Ces fonctions permettent de renvoyer le maximum et le minimum d'une liste de nombres.

Comment s'écrivent-elles et quels paramètres ?

Ces fonctions prennent au moins un paramètre et ce nombre peut aller jusqu'à 255 plages de cellule. On peut donc comparer un grand nombre de valeurs.

**=MAX(plage1;plage2;nombre1;nombre2;…)

=MIN(plage1;plage2;nombre1;nombre2;…)** La fonction renvoie la plus petite valeur trouvée dans cette liste de valeurs.

Un exemple théorique et un exemple concret

Avec des données aléatoires on obtient ceci :

On cherche dans notre exemple, le nombre maximum et minimum de chaque paramètre du classement.

MOYENNE

Que permet-elle ?

Elle renvoie la moyenne d'une liste de valeurs. Tout le monde a déjà eu des moyennes à l'école et connaît le principe. C'est cette fonction qui permet de faire ça.

Comment s'écrit-elle et quels paramètres ?

Comme pour les fonctions précédentes, elle prend au minimum un paramètre et peut en prendre jusqu'à 255 paramètres.

=MOYENNE(plage1;nombre1;plage2;…) Elle est donc très simple d'utilisation. On va donc pouvoir se passer d'un exemple théorique et faire directement un exemple pratique.

Un exemple concret

On va faire la moyenne des prix des voitures proposées par un garage.

MOYENNE.SI

Que permet-elle ?

Elle combine la fonction MOYENNE et la fonction SI pour donner la moyenne d'une série de valeurs qui respectent une condition.

Comment s'écrit-elle et quels paramètres ?

Cette fonction prend deux paramètres obligatoires et un facultatif. Le premier est la plage à comparée, le second la condition et le troisième, la plage des cellules dont il faut faire la moyenne si elle diffère du premier paramètre. Ce paramètre est utile si l'on veut comparer les cellules d'une colonne mais faire la moyenne de la colonne adjacente.

=MOYENNE.SI(plage1;condition;[plage2]) Nous avons déjà vu ce type de fonction avec SOMME.SI. C'est la même chose sauf que là, au lieu d'additionner des valeurs on fait leur moyenne.

Un exemple théorique et un exemple concret

Nous allons utiliser des données aléatoires pour le premier exemple puis un exemple d'application concret.

Pour l'exemple concret, on va faire la moyenne des notes à un test de logique d'un groupe de personne en fonction de leur quotient intellectuel (QI).

Cet exemple ressemble beaucoup à celui de la fonction SOMME.SI et pour cause, elle fonctionne de la même façon.

MEDIANE

Que permet-elle ?

Elle permet de renvoyer la médiane d'une série de nombres. La médiane est le centre de cette série. La répartition des valeurs de cette série de part et d'autre de la médiane est de 50% pour chaque partie. Cela signifie qu'il y a autant de valeur sous la médiane qu'au dessus.

Comment s'écrit-elle et quels paramètres ?

C'est comme pour les autres fonctions statistiques, on peut lui donner jusqu'à 255 valeurs.

=MEDIANE(plage1;nombre1;nombre2;plage2) C'est utile pour séparer un groupe en deux de façon équitable comme nous l'avons fait à l'exemple précédent en prenant la médiane des QI qui était 140.

Un exemple théorique et un exemple concret

Un exemple avec des données aléatoires :

La fonction MEDIANE peut être combinée à la fonction MOYENNE. Si ces deux valeurs sont proches, cela signifie que la répartition des valeurs est symétrique. On peut également rajouter la fonction MODE qui renvoie la valeur qui revient le plus souvent dans une liste. Si les trois fonctions renvoient la même valeur, alors la série a une distribution symétrique. En comparant les valeurs renvoyées par les fonctions MEDIANE et MOYENNE, on peut voir si, par exemple dans une classe, il y a des élèves qui "tirent" la classe vers le haut ou vers le bas. Sur une liste de produit, on peut voir aussi si un des articles à un prix beaucoup plus faible ou plus élevé.

ECARTYPE

Que permet-elle ?

Elle permet de renvoyer l'écart type d'une série de valeur. L'écart type mesure la dispersion des valeurs autour de la moyenne. La fonction ECARTYPE part de l'hypothèse que la série de valeur est un échantillon de la population. Pour évaluer l'écart type d'une population totale, il faut utiliser la fonction ECARTYPEP qui fonctionne de la même façon. En statistique, l'écart type permet d'évaluer à partir d'un échantillon aléatoire d'une population, la dispersion des valeurs de la population entière. Cette fonction est donc spécifique au monde des statistiques.

Comment s'écrit-elle et quels paramètres ?

Elle prend les mêmes paramètres que les autres fonctions, des nombres, plages de cellule au nombre de 255 maximum.

=ECARTYPE(nombre1;nombre2;nombre3;…)

Exemple

En voyant notre exemple, on peut donc affirmer statistiquement que par rapport à la moyenne d'âge, la dispersion est de plus ou moins 7,18 ans. Pour ce qui est du nombre d'enfants, la dispersion est de plus ou moins 1,68. Enfin, pour la taille, la dispersion est de plus ou moins 8,07cm.

FREQUENCE

Que permet-elle ?

Elle permet de renvoyer la répartition des valeurs d'une série dans des intervalles définis.

Comment s'écrit-elle et quels paramètres ?

Comme la fonction TRANSPOSER, c'est une fonction matricielle. Elle prend deux paramètres obligatoires, le premier la plage des valeurs à classer et le second, les intervalles. Les valeurs d'intervalles doivent être dans l'ordre croissant. La première valeur représente le premier intervalle : toutes les valeurs inférieures à cette première valeur d'intervalle composent le premier intervalle. Les valeurs inférieures à la deuxième valeur d'intervalle composent le deuxième intervalle et ainsi de suite jusqu'à la dernière valeur. La dernière valeur permet de définir deux intervalles : le premier avec les valeurs inférieures à cette valeur d'intervalle qui composent l'avant-dernier intervalle et le second avec les valeurs supérieures à cette valeur d'intervalles qui composent donc le dernier intervalle. Un petit schéma explicatif :

Si on a 9 valeurs pour définir nos intervalles, on aura alors 10 intervalles (d'où le x+1)

=FREQUENCE(plage_de_valeur;plage_d'intervalle) Lorsque l'on écrit la fonction dans une cellule, il faut ensuite sélectionner verticalement une cellule de plus que le nombre de cellules qu'occupe les limites des intervalles. Ensuite appuyer sur F2 puis simultanément sur Ctrl+Shift+Entrée. C'est ce que nous avons fait pour la fonction TRANSPOSE.

L'exemple comme explication

La première fréquence correspond au nombre de valeurs inférieures à 10, la deuxième le nombre de valeurs inférieures à 20 et ainsi de suite jusqu'au nombre de valeurs supérieures à 90.

Un petit exemple sur la préférence du type de film d'une population. On a 6 types de films et on obtient la répartition (fréquence) selon les préférences de chacun.

On a mis des valeurs d'intervalle situées entre les nombres associés aux films pour être sûr d'avoir des valeurs différentes des valeurs d'intervalle.

NB

Que permet-elle ?

Elle permet de renvoyer le nombre de cellules d'une plage qui comporte un nombre.

Comment s'écrit-elle et quels paramètres ?

Cette fonction est une fonction de la catégorie des statistiques et donc fonctionne de la même façon. Il suffit de lui donner en paramètre la plage que l'on veut compter. On peut donner jusqu'à 255 valeurs de plages.

=NB(plage1;plage2) La fonction additionne le nombre de la première et de la seconde plage.

Un exemple théorique et un exemple concret

Avec des données aléatoires, ça donne ça :

NBVAL et NB.VIDE

Je ne vais pas décrire ses fonctions dans le détail parce qu'elles fonctionnent comme la fonction NB. Elles ont la même fonction : compter. La fonction NBVAL compte les cellules non vides (donc compte les cellules contenant du texte) d'une plage alors que NB.VIDE compte le nombre de cellules vides d'une plage.

NB.SI

La fonction NB.SI fonctionne comme la fonction SOMME.SI et la fonction MOYENNE.SI. Elle permet de compter les cellules selon une condition.

Les fonctions Texte

Dans cette partie, nous allons étudier les fonctions « Texte » d'Excel.

Une fenêtre s'ouvre, sélectionner dans le menu déroulant de la catégorie : « Texte ».

Je vais vous proposer ici une liste non exhaustive des fonctions de la catégorie « Texte » qui sont peu connues mais qui peuvent vous êtes utiles.

Pour suivre avec moi cette sous-partie et vous exercer de votre côté, je vous propose de :

Télécharger le fichier fonctions_texte.xlsx

Ce classeur Excel contient tous les exemples utilisés dans cette partie. Il y a la base des exemples, à vous d'entrer les formules.

CONCATENER

Que permet-elle ?

Cette fonction, je l'ai déjà utilisée dans le tutoriel car elle est très pratique pour afficher du texte. En effet, elle permet de mettre bout à bout des chaînes de caractère (du texte) pour n'en former qu'une. Si vous avez lu le cours jusque-là, vous en avez vu l'utilité. Elle permet de présenter le texte de façon lisible pour l'utilisateur.

Comment s'écrit-elle et quels paramètres ?

Cette fonction prend autant d'arguments qu'il y a de chaînes de caractères à mettre bout à bout (jusqu'à 255).

=CONCATENER(texte1;[texte2];…) Pour les chaînes qui comprennent du texte, il faut les mettre entre guillemets. Si on veut aussi concaténer des cellules, alors les guillemets sont omis. Il y a un point-virgule (;) entre chaque chaîne à concaténer.

=CONCATENER("Le résultat de la somme est : ";B12) La fonction nous permet de faire une phrase dans une seule cellule contenant le résultat d'une opération qui changera si le résultat change.

Je ne sais pas si les exemples sont utiles puisque j'en est déjà fait auparavant… ! Mais je sens que vous en demandez, donc c'est parti !

Les exemples

Pour l'exemple, on va demander à l'utilisateur de remplir un petit tableau afin de lui raconter une histoire.

Bon j'avoue que c'est pas adapté à chaque personne, ça dépend de ce que l'utilisateur entre dans les cellules, mais c'est pour montrer ce que l'on peut faire avec cette fonction.

EXACT

Que permet-elle ?

Elle permet de comparer 2 chaînes de caractères et dire si elles sont identiques ou non.

Comment s'écrit-elle et quels paramètres ?

Cette fonction prend deux paramètres obligatoires : les deux chaînes de caractères.

=EXACT(texte1;texte2) La fonction renvoie VRAI si les deux arguments sont identiques et FAUX s'ils ne le sont pas. La fonction différencie les majuscules et les minuscules.

Un exemple théorique

Avec des données aléatoires :

CHERCHE

Que permet-elle ?

Elle permet de chercher dans une chaîne de caractère, c'est-à-dire du texte, une autre chaîne de caractère (un mot par exemple).

Comment s'écrit-elle et quels paramètres ?

La fonction prend deux paramètres obligatoires et un facultatif. Les deux paramètres obligatoires sont : le texte recherché et le texte dans lequel on fait la recherche. Le paramètre facultatif est le numéro du caractère à partir duquel la recherche s'effectue. S'il n'est pas renseigné, la recherche commence au premier caractère (au début du texte).

=CHERCHE(texte_cherché;texte_de_recherche;[n°_de_départ]) Cette fonction renvoie la place du texte recherché en comptant le nombre de caractères qui le sépare du numéro de départ.

Cette fonction est utile lorsqu'elle est combinée à d'autres de remplacement que l'on verra plus tard.

Voici des exemples

Je vous présente ici des exemples simples :

DROITE et GAUCHE

Que permettent-elles ?

Elles permettent d'extraire les caractères du début (GAUCHE) et de fin (DROITE) de la chaîne de caractère.

Comment s'écrivent-elles et quels paramètres ?

Ces fonctions sont décrites ensemble puisqu'elles font la même chose. La différence, c'est que GAUCHE commence à gauche de la chaîne donc au début et DROITE à droite de la chaîne donc à la fin. Elles prennent deux paramètres, un obligatoire : la chaîne de caractère d'où on extrait les caractères et l'autre facultatif : le nombre de caractères à extraire. S'il n'est pas renseigné, la fonction extrait un seul caractère.

=DROITE(texte;[nombre_de_caractère_à_extraire]) =GAUCHE(texte;[nombre_de_caractère_à_extraire]) Rappelons que si le texte est mis directement il faut des guillemets mais souvent on utilise des coordonnées de cellules. Dans ce cas, il ne faut pas de guillemet.

Un exemple théorique et un exemple concret

Avec des mots sans importance :

Cette fonction peut être utilisée pour savoir si un nom est au pluriel ou non. On va alors faire une condition et afficher une phrase.

Je vais décrire un peu la formule. On a donc une condition pour savoir si le mot est au pluriel ou pas. Un mot au pluriel prend un s ou un x donc dans la condition SI on ajoute la fonction OU (soit un s soit un x). En suite si celle-ci vaut VRAI, ça veut dire que le mot est au pluriel et on afficher à l'aide la la fonction CONCATENER, une petite phrase.

Voilà, je vous ai proposé une utilisation très basique mais vous pouvez ainsi afficher des phrases en fonction des premiers ou derniers caractères.

MAJUSCULE et MINUSCULE

Que permettent-elles ?

Elles permettent de mettre soit en majuscule soit en minuscule tous les caractères d'une cellule.

Comment s'écrivent-elles et quels paramètres ?

Ces deux fonctions sont simples d'utilisation, elles ne prennent qu'un paramètre : le texte à transformer.

=MAJUSCULE(texte_à_transformer) =MINUSCULE(texte_à_transformer) Cette fonction peut servir à mettre les noms de famille en majuscule lors de la concaténation par exemple.

Des exemples pour illustrer

La fonction MINUSCULE peut être utilisée lorsque l'on a vu la fonction CONCATENER. En effet, on a fait une phrase qui prend en compte ce qu'entre l'utilisateur. Il aurait très bien pu mettre des majuscules aux mots entrés. L'utilisation de la fonction MINUSCULE aurait permis d'améliorer la présentation.

NOMPROPRE

Que permet-elle ?

Elle permet de transformer une chaîne de caractère en un nom propre. Je vous rappelle qu'un nom propre prend une majuscule et le reste est en minuscule. Cette fonction met donc une majuscule à la première lettre d'un mot et le reste du mot en minuscule, et ce, pour tous les mots du texte.

Comment s'écrit-elle et quels paramètres ?

Comme pour les fonctions précédentes, elle ne prend qu'un paramètre : le texte à transformer. A chaque espace, la fonction définit un nouveau mot et mettra donc une majuscule au mot.

=NOMPROPRE(texte_à_transformer) Elle est donc très simple d'utilisation aussi.

Des exemples simples

NBCAR

Que permet-elle ?

Elle permet de compter le nombre de caractères présents dans une chaîne de caractère.

Comment s'écrit-elle et quels paramètres ?

Toujours la même chose, elle prend comme paramètre obligatoire le texte dont il faut compter les caractères.

=NBCAR(texte_à_compter) Elle est souvent combinée à d'autres fonctions pour savoir où démarrer une recherche ou savoir où commencer à changer du texte.

Des exemples

Je vais vous proposer un exemple plus poussé à la fin des fonctions Texte pour comprendre l'utilité de celle-ci.

REMPLACER

Que permet-elle ?

Elle permet de remplacer dans un texte un mot par un autre. C'est ici que les autres fonctions vues précédemment seront utiles.

Comment s'écrit-elle et quels paramètres ?

Cette fonction prend quatre paramètres obligatoires. Le premier est le texte dans lequel on souhaite faire le remplacement. Le second est le numéro du caractère où commence le remplacement. Le troisième est le nombre de caractères à remplacer. Le quatrième est le texte de remplacement. Je rappelle que les quatre paramètres sont obligatoires.

=REMPLACER(texte_de_base;numéro_caractère_début;nombre_de_caractère_à_remplacer;texte_de_remplacement)

Des exemples d'application pour bien comprendre

Je pense que l'image parle d'elle-même. On va utiliser les fonctions de Texte pour trouver les numéros de caractères, on ne s'amuse pas à compter. De plus si on modifie la première colonne, le reste s'adapte automatiquement.

Voilà on en a fini avec les fonctions Texte. Il en existe d'autres plus ou moins utiles selon ce dont vous avez besoin mais je pense avoir présenté les plus utiles. Pour les autres, vous pouvez les utiliser et vous former seuls à l'aide d'Excel.

Les fonctions Date et Heure

Dans cette partie, nous allons étudier les fonctions « Date et Heure » d'Excel.

Une fenêtre s'ouvre, sélectionner dans le menu déroulant « Date & Heure ». Nous allons ici traiter des fonctions qui concernent l'horloge et donc les dates et les heures avec la notion de temps.

Pour suivre avec moi cette sous-partie et vous exercer de votre côté, je vous propose de :

Télécharger le fichier fonctions_date_et_heure.xlsx

Ce classeur Excel contient tous les exemples utilisés dans cette partie. Il y a la base des exemples, à vous d'entrer les formules.

INTRODUCTION

Une petite introduction à ces fonctions de Date & Heure qui nous permettra de comprendre le fonctionnement de ces dates. A chaque fois que l'on entre une date ou une heure dans une cellule du tableur Excel, elle s'affiche selon un format (Heure ou Date). Si vous lui appliquer le format Nombre ou Texte, vous verrez d'afficher un nombre. On l'appelle le numéro de série et il est utilisé par Excel pour calculer la date et l'heure. Le numéro 1 correspond au 1er janvier 1900. En effet, Excel prend pour base le 1er janvier 1900 pour faire ses calculs. Le numéro de série, vous l'aurez peut-être compris, renvoie le nombre de jours passés depuis cette date. Au jour où j'écris ce tutoriel, en ce 23 juin 2010, le numéro de série est : 40352. Cela signifie qu'il s'est écoulé 40 352 jours depuis le 1er janvier 1900. On peut ainsi soustraire et additionner facilement des jours!

Alors Excel a pensé à tout, enfin celui qui l'a créé. Pour les heures, ce sont les chiffres après la virgule qui permettent de la définir. A midi (12h), il y a la moitié du temps de passé pour la journée. De ce fait, 12h correspond à 0,5. S'il n'y a pas de chiffre après la virgule l'heure est de 00:00:00.

Après avoir étudié le fonctionnement des dates, nous allons attaquer les fonctions. Nous pourrons ainsi développer le fonctionnement de celles-ci, mieux les comprendre et mieux les utiliser. Allez hop, c'est parti !

AUJOURDHUI et MAINTENANT

Que permettent-elles ?

Ces fonctions renvoient la date du jour (AUJOURDHUI) et l'heure (MAINTENANT) au moment où la feuille est calculée. Comme pour la fonction ALEA.ENTRE.BORNES, les valeurs changent à chaque fois que l'on effectue un calcul dans la feuille. L'heure se met donc à jour à chaque calcul effectué dans le classeur qui contient la formule. MAINTENANT peut aussi renvoyer la date du jour, pour avoir l'heure, il suffit de changer le format de la cellule en Heure au lieu de Date.

Comment s'écrivent-elles et quels paramètres ?

Ces fonctions ne prennent pas de paramètres et donc sont très simples d'utilisation.

=AUJOURDHUI() =MAINTENANT()

L'application

Je ne peux que proposer un exemple très simple puisque ces fonctions sont à utiliser avec les autres pour mettre à jour les classeurs automatiquement à l'ouverture de ceux-ci. J'ai précisé le format utilisé à côté de chaque ligne.

Nous allons maintenant nous attarder à une série de fonctions qui fonctionne de la même façon, mais ne renvoie des valeurs différentes. Ces fonctions permettent d'avoir l'année, le mois, le jour, l'heure, la minute et la seconde d'une date. Ces fonctions sont les suivantes : ANNEE, MOIS, JOUR, HEURE, MINUTE, SECONDE.

ANNEE, MOIS, JOUR, HEURE, MINUTE, SECONDE

Que permettent-elles ?

Elles permettent de renvoyer un nombre correspondant à l'année (de 1900 à 9999), le mois (de 1 pour janvier à 12 pour décembre), le jour (de 1 à 31), l'heure (de 0 à 23), la minute (de 0 à 59) et la seconde (de 0 à 59) d'un numéro de série.

Comment s'écrivent-elles et quels paramètres ?

Toutes ces fonctions s'écrivent de la même façon et ne prennent qu'un paramètre obligatoire : le numéro de série. C'est-à-dire le numéro représentant la date que l'on souhaite analyser.

=ANNEE(numéro_de_série) =MOIS(numéro_de_série) =JOUR(numéro_de_série) =HEURE(numéro_de_série) =MINUTE(numéro_de_série) =SECONDE(numéro_de_série)

On peut tout à fait écrire dans une formule : =ANNEE(AUJOURDHUI()) par exemple pour économiser la case où serait inscrit la date du jour. Mais il peut être intéressant de réserver une case pour la fonction AUJOURDHUI() et ensuite faire référence à cette cellule sur toute la feuille. Ainsi, le nombre de caractères dans une formule est moindre et c'est plus lisible.

Exemple d'application

Je vous ai montré un exemple simple, mais vous pouvez utiliser toutes ses fonctions dans des cas plus complexes.

JOURSEM

Que permet-elle ?

Elle renvoie le numéro du jour de la semaine d'une date.

Comment s'écrit-elle et quels paramètres ?

Cette fonction prend deux paramètres, un obligatoire le numéro de série et le second facultatif le type de retour. Je ne reviens pas sur le premier paramètre que vous connaissez. Par contre, le second prend trois valeurs différentes : 1, 2 ou 3.

Ce type de retour va dépendre de ce que vous utilisez comme classification des jours de la semaine. Avec le premier type (1), dimanche = 1 jusqu'à samedi = 7. Avec le second type (2), lundi = 1 jusqu'à dimanche = 7. Enfin, avec le dernier type (3), lundi = 0 jusqu'à dimanche = 6.

Ainsi vous pouvez utiliser le type que vous voulez. Par défaut, c'est le type 1 qui est utilisé.

=JOURSEM(numéro_de_série;[type_de_résultat]) Cette fonction va permettre de savoir si le jour de la date est un jour de week-end ou non ou alors un jour non travaillé par un employé. On peut aussi l'utiliser pour comparer deux dates, savoir si c'est le même jour ou non.

Pour afficher le jour en toutes lettres, on peut soit utiliser la fonction SI et mettre 6 conditions, ce qui nous donne une grosse formule. On peut aussi utiliser la fonction RECHERCHE avec un petit tableau dans lequel est associé à chaque chiffre le jour correspondant. Je vous laisse essayer cette méthode pour vous entraîner.

Un exemple théorique et un exemple concret

Pour l'exemple théorique, on prend des dates au hasard et on regarde quel jour de la semaine est renvoyé.

Si vous avez téléchargé le fichier d'exemple, vous avez la réponse, il est juste en dessous. Sinon, le voici :

Pour notre exemple plus concret, on va s'amuser à savoir quel était le jour de la semaine le jour de votre naissance.

Pour vérifier votre formule, vous pouvez afficher la date en format Date longue.

NO.SEMAINE

Que permet-elle ?

Cette fonction renvoie le numéro de la semaine (dans une année) d'un numéro de série (une date).

Comment s'écrit-elle et quels paramètres ?

Elle prend un paramètre obligatoire, le numéro de série et un facultatif. Le paramètre facultatif prend la valeur 1 si on veut faire commencer une semaine au dimanche et la valeur 2 si l'on veut faire commencer une semaine au lundi.

Si vous utilisez votre classeur Excel à titre personnel et si vous utilisez cette fonction pour faire des calculs entre les semaines, vous pouvez ignorer la norme européenne.

Des exemples d'application

DATE

Que permet-elle ?

Elle permet de renvoyer une date comme un numéro de série selon trois paramètres : l'année, le mois et le jour.

Comment s'écrit-elle et quels paramètres ?

Elle prend les trois paramètres précédemment cités comme paramètres obligatoires. On peut ainsi afficher des dates diverses.

=DATE(année;mois;jour)

Les exemples

Dans notre second exemple, on va pouvoir donner le nombre de jours qui sépare la date du jour de votre anniversaire.

On va analyser un peu comment on fait pour trouver ça. Je vous donne tout d'abord la formule que j'ai mise en C5. J'ai séparé la formule en deux pour plus de faciliter. L'autre est en D5 et on va l'analyser juste après.

Pour commencer à comprendre, il faut savoir ce qu'il y a en D5. En D5, on regarde le nombre de jours qui sépare la date d'aujourd'hui de votre anniversaire. Comment ? Ça on va le voir tout à l'heure.

On commence par la première condition. Si le nombre de jours est supérieur à 1, on met un "s" à "jours" précédé du nombre de jour. Si ça n'est pas le cas, il reste deux solutions : soit c'est la veille il ne reste plus qu'un jour soit c'est le jour même et c'est donc votre anniversaire. On refait une condition SI. Si D5 est supérieur à 0 (donc égal à 1), on ne met pas de "s" à "jour". Sinon (c'est qu'il est égal à 0), alors on affiche "Joyeux anniversaire".

On va maintenant s'attarder à ce qui est dans la cellule D5.

Premièrement on compare la date du jour et la date d'anniversaire. Comment ? Et bien c'est simple, grâce à la fonction DATE. La date du jour est égale à C2. Pour connaître la date d'anniversaire, il faut le jour et le mois de la date de naissance, mais l'année en cours. Donc on utilise la formule suivante :

DATE(ANNEE(C2);MOIS(C3);JOUR(C3)) Dans un premier temps comme je l'ai dit, on regarde si la date du jour n'est pas l'anniversaire. Si c'est le cas, on renvoie 0 (pour que la formule en C5 fonctionne). Si ce n'est pas le cas, on regarde si la date du jour est inférieure à la date d'anniversaire. Si c'est le cas, ça signifie que le prochain anniversaire de cette personne est dans la même année. Il est alors simple de savoir le nombre de jours qui sépare les deux dates par une simple soustraction. Si la date du jour n'est pas inférieure (c'est qu'elle est supérieure), on renvoie donc la valeur si faux de la condition précédente. L'anniversaire de la personne est passé dans l'année en cours, le prochain est donc l'année suivante. On va faire la différence de la date d'anniversaire de l'année suivante en ajoutant tout simplement 1 à l'année en cours. De cette façon :

DATE(ANNEE(C2)+1;MOIS(C3);JOUR(C3)) On obtient ainsi le nombre de jours qui sépare la date d'anniversaire et le jour actuel. Cette formule renvoie 0 si c'est l'anniversaire et ensuite le nombre de jour. Vous avez bien compris que si à la place de tous les D5 de la formule en C5 étaient écrits en entier, la formule serait trèèèèèèèès longue.

Voilà un exemple dans lequel on utilise la fonction DATE. Il en existe bien d'autre évidemment.

NB.JOURS.OUVRES

Que permet-elle ?

Elle renvoie le nombre de jours ouvrés compris entre deux dates.

Comment s'écrit-elle et quels paramètres ?

Elle prend deux paramètres obligatoires : la date de début et la date de fin. Un paramètre facultatif peut être renseigné, c'est la date des jours fériés de la période.

=NB.JOURS.OUVRES(date_début;date_fin;[dates_jours_fériés]) Les dates doivent être entrées en format date avec le numéro de série. Pour les jours fériés, vous pouvez créer un tableau qui référence tous les jours fériés de l'année. Ainsi, le paramètre "jours_fériés" sera renseigné grâce à ce tableau.

L'exemple

Je vous propose quelque exemple simple, il n'est pas utile de développer plus cette fonction. Elle est très utile pour compter le nombre de jours travaillés dans une période donnée et ainsi connaître le temps de travail des salariés.

J'ai utilisé comme jours fériés la liste suivante (jours_feries = G3:G12) :

SERIE.JOUR.OUVRE

Que permet-elle ?

Elle renvoie la date précédente ou suivant la date indiquée selon un nombre de jours ouvrés. Par exemple, on cherche à quelle date je vais avoir travaillé 100 jours à partir d'aujourd'hui.

Comment s'écrit-elle et quels paramètres ?

Elle fonctionne un peu comme la fonction précédente avec deux paramètres obligatoires : la date de début et le nombre de jours ouvrés. Les jours fériés sont encore présents en paramètre facultatif.

=SERIE.JOUR.OUVRE(date_début;nombre_de_jours;[jours_féries]) Je rappelle que les dates doivent être entrées sous forme de date et non de texte.

L'exemple de fonctionnement

Ouf, pas fâché d'avoir terminé ce petit index . La liste est évidement non exhaustive, à vous de jouer en cherchant les fonctions dans Excel directement, sur le site de Miscrosoft ou sur le forum du Site du Zéro.

Si vous avez envie de voir une fonction apparaître dans ce glossaire, il suffit de le demander en commentaire ou par MP et je me chargerai de l'intégrer si elle me semble utile.

• Annexes
• Sommaire
• Bonnes pratiques et débogage des formules