Définitions de Base
Avant de plonger dans les fonctions il est bon de rappeler quelques définitions de base
Excel : Le logiciel Excel de Microsoft est un tableur électronique. Un tableur permet d’effectuer de calculs de manière rapide et précise.La feuille électronique produite par Excel est appelée une feuille de calcul.
Classeur :Lorsqu’on travaille avec Excel un classeur est un fichier, chaque classeur peut contenir jusqu’à 255 feuilles. Chaque feuille est identifiée par un onglet qui est situé au bas de votre écran. Pour ajouter une feuille on doit faire Insertion Feuille pour les anciennes versions du logiciel ou cliquer sur le + sur la barre en bas de feuille
.
Onglets : Les onglets au bas de la feuille de calcul permettent de regrouper votre travail dans des ensembles de feuilles appelés classeurs.Vous pouvez donner des noms à vos onglets de feuille.Par défaut le nom des onglets est Feuil1 pour le premier onglet Feuil2 pour le second etc..Pour Changer le nom d’un onglet Clic droit sur sur l’onglet et cliquez sur renommer
Feuille de calcul : La fenêtre de la feuille de calcul contient un quadrillage de colonnes et de lignes. Les colonnes sont étiquetées alphabétiquement(A,B,C..) et les lignes le sont numériquement(1,2,3…). Seule une petite fraction de la feuille de calcul est visible à la fois, elle peut contenir 256 colonnes et 65536 lignes.
Cellule :L’intersection d’une ligne et d’une colonne s’appelle une cellule. Une cellule peur contenir du texte, des nombres, des formules
.
Adresse : Chaque cellule possède un emplacement unique (adresse de la cellule) qui est identifié par les coordonnées de la colonne et de ligne. Par exemple l’adresse de la cellule du coin supérieur gauche et A1
Plage :Regroupement de plusieurs cellules. Par exemple la plage A3 à B6
Étiquette: Une étiquette est une cellule qui contient du texte ou des informations numériques inutilisées dans des calculs(date, heure…)
.
Valeur : Les valeurs peuvent être des nombres, des formules et des fonctions sont utilisées dans les calculs.Excel interprète une donnée comme une valeur lorsqu’il s’agit d’un nombre ou que la donnée commence par des symboles spéciaux +,-@, #, ou $. Par défaut toute ces valeurs sont alignées à droite dans la cellule. Quand une cellule contient à la fois du texte et des nombres il ne s’agit pas d’une formule valide et Excel traite l’entrée comme une étiquette.
Formules : Les formules permettent de faire des calculs numériques.Une formule commence habituellement par l’indicateur de formule, le signe égal =, et contient des adresses de cellules et des noms de plages.Si vous modifiez la valeur d’une cellule, toute formule contenant une référence à cette cellule sera automatiquement recalculée. Lorsqu’une formule comprend plusieurs opérateurs arithmétiques Excel détermine l’ordre d’exécution des opérations selon les règles de préséance suivante :
- Parenthèse
- Les exposants
- Les multiplications et les divisions
- Les additions et les soustractions
Exemple :
Résultat différent si les parenthèses sont supprimées
Fonctions : Les fonctions sont des formules prédéfinies permettant d’effectuer facilement des calculs complexes. Les fonctions débutent comme les formules par le signe = les fonctions sont toujours utilisées avec une plage de données
=somme(argument) Ex =somme(A1 :A2)
Fonctions Excel par l’exemple
SOMME()
Syntaxe
=somme(argument1 ;argument2 ;….)
=somme(argument)
ou
Pour additionner entre elles plusieurs cellules contiguës ou non on peut utiliser la fonction somme()
Écrire dans la cellule ou doit apparaître le résultat ; =somme(
Sélectionner ensuite à la souris la ou les plages de cellules à additionner. Validez avec la touche entrée
Exemple
ou
Somme.Si()
Syntaxe
Somme.si(Champ Critère ;Critère ;Champ Somme)
La fonction Somme.si() calcule la somme des cellules vérifiant le critère.
Si()
Syntaxe
Si(Condition ;formule1;formule2)
La fonction Si() évalue la condition
Si la condition est vraie la formule1 est appliquée
Si la condition est fausse la formule2 est appliquée
Exemple 1
Si le salaire est inférieur au plafond, la retenue est égale au salaire multiplié par le coefficient de retenue vieillesse
Si le salaire est supérieur au plafond, la retenue est égale au plafond multiplié par le coefficient de retenue vieillesse
Exemple 2
Le message Rupture apparaît en face des produits dont le stock est inférieur au minimum sinon OK est affiché
Exemple 3
Nous faisons apparaître la quantité Minimum-Stock
Exemple 4
Affichage du pourcentage d’augmentation du CA
Conditions multiples
Syntaxe
Si(Et(cond1 ;cond2 ; …..) ;formule1_si_vrai ; formule2_si_faux)
Si(Ou( (cond1 ;cond2 ; …..) ;formule1_si_vrai ; formule2_si_faux)
Exemple 1
Le message Actif est affiché si l’âge est compris entre 18 et 60
Si Imbriqués
Syntaxe
Si(cond1 ;Si(cond2 ;formule1 ;formule2) ;Si(cond3 ;formule3 ;formule4))
Affichage d’un message si produit n’existe plus
Exemples divers
Affichage de caractères spéciaux(police Windings)
OU()
Les conditions posées seront multiples et pour que la réponse aux conditions soit « VRAI », il faudra que l’une au moins se vérifie
Syntaxe
=Si(OU(cond1 ;cond2 ;…condN) Action à réaliser si au moins une des conditions est satisfaite ;action à réaliser si aucune des conditions n’est satisfaite
Exemple :Une entreprise souhaite verser une prime de fin d’année à ses commerciaux s’ils remplissent l’une ou l’autre des conditions suivantes
Avoir plus de 5 ans d’ancienneté OU Avoir réalisé plus de 1 million d’euros de chiffre d’affaires
NB.si()
La fonction Nb.Si()compte le nombre de cellules vérifiant le critère spécifié.
Syntaxe
Nb.Si(champCritère ; Critère)
Moyenne()
La fonction Excel MOYENNE renvoie la moyenne d’une série de valeurs.
Syntaxe
MOYENNE(plage_de_valeurs)
ou
=MOYENNE(valeur1; valeur2; valeur3; etc)
.
Vous pouvez également combiner la fonction MOYENNE avec la fonction ARRONDI. Le résultat se trouvera arrondi.
Faire une moyenne si condition (moyenne conditionnelle)
Il est possible de faire une moyenne conditionnelle. Il faudra utiliser la fonction MOYENNE.SI, elle permettra d’ajouter un critère.
Syntaxe
Moyenne.si(Plage Critère ; Critère ;plage moyenne)
Prix moyen d’une chaise
Fonction ANNEE
La fonction DATE est une fonction Excel permettant d’extraire l’année d’une date. Cette date peut être issue d’un calcul, d’une cellule ou d’une date saisie directement dans la fonction.
Syntaxe
=ANNEE( date )
Utiliser la fonction ANNEE
=ANNEE( « 31/12/2024 » )
Résultat : 2024
=ANNEE( A1 )
A1 contient la date 31/12/2024
Résultat : 2024
=ANNEE( AUJOURDHUI() )
Résultat : année en cours
Calculer l’année actuelle
ANNEE( AUJOURDHUI() )
Calculer l’année d’une date
= ANNEE( « 31/12/2023 » )
= ANNEE( A1 )
3 façons de Calculer l’année précédant une date
= ANNEE( « 31/12/2023 » ) – 1
= ANNEE( A1 ) – 1
= ANNEE ( AUJOURDHUI() ) -1
3 façons de Calculer l’année suivant une date
= ANNEE( « 31/12/2023 » ) + 1
= ANNEE( A1 ) + 1
= ANNEE( AUJOURDHUI() ) + 1
Calculer l’année en cours avec 2 chiffres
=CNUM( DROITE( ANNEE( A1 ) ; 2 ) )
Si vous n’utilisez pas la fonction CNUM, le format de la réponse sera du texte. La fonction CNUM vient indiquer que le format voulu est numérique.
Fonction MOIS
La fonction MOIS est une fonction Excel permettant d’extraire le mois d’une date. Cette date peut être issue d’un calcul, d’une cellule ou d’une date saisie directement dans la fonction.
Syntaxe
=MOIS( date )
La fonction MOIS extrait le mois à partir d’une date et renvoie le résultat sous forme de nombre de 1 ou 2 digits (ex: 1 pour Janvier, 12 pour Décembre).
Utiliser la fonction MOIS
Fonction Jour
La fonction JOUR est une fonction Excel permettant d’extraire le JOUR d’une Date. Cette date peut être issue d’un calcul, d’une cellule ou d’une date saisie directement dans la fonction.
Syntaxe
=JOUR( date )
Formule Excel – Nombre de jours entre deux dates
= date_de_fin – date_de_début
Explications
Pour Excel, les dates sont mémorisées comme des nombres sur lesquels il vient appliquer un format particulier :
1 pour le 01/01/1900,
2 pour le 02/01/1900,…
Le 25/04/2025 correspond au chiffre 45771
Le 30/05/2025 correspond à 45806.
Nous obtenons une formule de la forme suivante:
= C5-B5
= 45806-45771
Résultat : 35
Fonction JOURS
La fonction JOURS calcule le nombre de jours entre deux dates. Il suffit d’indiquer la date de début et la date de fin. C’est une autre solution pour arriver au même résultat.
= JOURS(C5;B5)
Résultat : 5
Fonction DATEDIF
La fonction DATEDIF peut également calculer la différence entre deux dates. Pour cela, il faudra appliquer une syntaxe particulière.
= DATEDIF( B5 ; C5 ; « d » )
Résultat : 5
Cette fonction a beaucoup d’autres options (calcul de la différence en mois, en années,…).
Nombre de jours entre une date et la date du jour
Pour calculer la différence entre une date donnée et aujourd’hui, il faut utiliser la fonction AUJOURDHUI. Celle-ci permet de récupérer la date du jour.
= AUJOURDHUI() – date_de_début
ou
= date_de_fin – AUJOURDHUI()
NO.SEMAINE
NO.SEMAINE est une fonction qui permet de calculer le numéro de la semaine à partir d’une date. Le résultat sera une valeur comprise entre 1 et 54. Par défaut, les semaines démarrent le dimanche (format anglo-saxon).
Vous devrez indiquer un second argument (valeur 2) pour qu’Excel calcule à partir du lundi.
Syntaxe
= NO.SEMAINE( date ; [type_renvoi] )
Cette fonction possède deux arguments.
- date. Date à partir de laquelle nous voulons calculer le numéro de la semaine.
- type_renvoi –optionnel. A quel jour démarre la semaine.
Par défaut la valeur sera 1 pour le dimanche.
Saisissez 2 pour le lundi
Fonction SUBSTITUE
La fonction SUBSTITUE est une fonction Excel permettant de remplacer un caractère ou une chaîne de caractères dans un texte par une autre valeur.
Syntaxe
= SUBSTITUE( texte ; ancien_texte ; nouveau_texte ; [no_position] )
Cette fonction possède 4 arguments :
texte Texte dans lequel il faut remplacer le caractère ou la chaîne de caractères.
ancien_texte Caractère ou chaîne de caractères à remplacer.
nouveau_texte Valeur de remplacement.
[position] Argument optionnel (par défaut 1). Numéro d’occurrence du caractère à remplacer.
Fonction REMPLACER
La fonction REMPLACER est une fonction Excel permettant de remplacer des caractères (lettres ou nombres) par d’autres sur la base de leurs positions connues à l’intérieur d’un texte.
Syntaxe
= REMPLACER( ancien_texte ; no_départ ; no_car ; nouveau_texte )
Cette fonction possède 4 arguments.
Ancien_texte Le texte dont une partie est à remplacer.
No_départ Position du 1er caractère à remplacer.
No_car Nombre de caractères à remplacer.
Nouveau texte – Texte de remplacement.
Récupérer la partie droite d’un texte après un caractère spécifique
Les fonctions utilisées NBCAR, CHERCHE,DROITE
La fonction NBCAR (cellule)compte le nombre de caractères dans la cellule.
La fonction CHERCHE Indique la position de début du texte cherché.
CHERCHE(texte_cherché;
plage de recherche
No_départ (facultatif)
La fonction DROITE renvoie le ou les derniers caractères d’une chaîne de texte en fonction du nombre spécifié.
=DROITE(«Jean GUILLEMET»;14-5)
Dans cet exemple la fonction droite affiche les 9 derniers caractères
=DROITE(«Jean GUILLEMET»;9) affiche GUILLEMET
RECHERCHEV
La fonction RECHERCHEV est une fonction Excel permettant de rechercher verticalement une valeur et de renvoyer une des valeurs située dans les cellules à sa droite. La fonction RECHERCHEV permet de faire des recherches partielles grâce aux caractères génériques (ex: qui contient « production »)
La fonction RECHERCHEV s’écrit de la façon suivante :
= RECHERCHEV(valeur_cherchée;matrice;index_col;[valeur_proche] )
Cette fonction possède quatre arguments :
Valeur cherchée Valeur à rechercher dans la première colonne de la matrice.
Matrice Sélection de cellules qui doit démarrer par la colonne où se situe la valeur cherchée et se terminer à minima au niveau de la colonne où est le résultat à renvoyer.
Index de colonne Numéro de la colonne où se situe le résultat à renvoyer par rapport à la matrice sélectionnée.
valeur proche VRAI = valeur approximative / FAUX = valeur exacte.
Faire un RECHERCHEV sur Excel ?
Définir la matrice de recherche
Le plus important à comprendre est que la fonction RECHERCHEV va rechercher la valeur que vous lui indiquez dans la première colonne de votre matrice. Assurez-vous que le tableau contienne en première colonne la valeur cherchée.
Si ce n’est pas le cas, et que votre colonne de recherche se situe au milieu de votre tableau ce n’est pas grave tant que votre colonne de résultat se situe à sa droite.
Sélectionner la valeur cherchée
Elle peut être contenue dans une cellule ou être saisie directement dans la formule
= RECHERCHEV(A9………valeur contenue dans la cellule
= RECHERCHEV( H123…..valeur saisie
Sélectionner la matrice
Sélectionnez la matrice. Il s’agit de la plage des données qui commence au niveau de la colonne de recherche et qui se termine a minima à la colonne des résultats.
Indiquer le numéro de la colonne de la valeur à récupérer
Indiquez dans quelle colonne de votre matrice se situe le résultat que vous voulez récupérer. Vous devez indiquer le numéro de la colonne par rapport à la matrice sélectionnée (dans notre exemple c’est la colonne 4).
=rechercheV(A9;A2:E10;4;0)
Recherche approximative ou recherche exacte
La fonction RECHERCHEV vous permet de faire des recherches exactes ou des recherches approximatives.
Pour cela vous devez saisir : FAUX ou 0 pour une recherche exacte VRAI ou 1 ou omettre l’argument pour une recherche approximative.
=recherchev(A9;A9:E10;4;0)
Mise en place d’un bon de commande (a télécharger)
Utilisation des fonctions RECHERCHEV
