Voici un extrait de l’ebook : Index formules Excel : Il s’agit d’un guide complet sur les formules Excel scindé en 2 parties : une première partie introduction sur toutes les clés à maîtriser pour comprendre les formules Excel et une seconde partie avec les 80 formules les plus utilisées.
Introduction :
Les types d’erreurs
> ERREUR « #DIV/0 » : Division par zéro.
Signification : L’erreur « #DIV/0 » survient lorsqu’on tente de diviser un nombre par 0 (zéro) ou par une cellule vide.
> ERREUR « #NUL! » : Valeur nulle.
Signification : L’erreur « #NUL! » survient lorsqu’on utilise un opérateur de plage incorrect dans une formule ou lorsqu’on utilise un opérateur d’intersection (espace) entre deux références de plage de cellules qui ne se coupent pas.
Exemple : On a un tableau qui récapitule les ventes de Janvier à Décembre.
Formule pour la source de la liste déroulante en cellule D3 :
= SOMME(B5:B7 B8:B10) = #NUL!
=> La formule renvoie l’erreur #NUL! car les deux plages de cellules (CA de Avril à Juin et CA de Juillet à Septembre) ne se coupent pas.
> ERREUR « #NOMBRE! » : Nombre non valide.
Signification : L’erreur « #NOMBRE! » survient lorsqu’une formule contient des valeurs numériques non valides :
> Un nombre est trop grand ou trop petit pour Excel : Les valeurs
doivent être comprises entre -1*10307 et 1*10307.
> Une formule qui se calcule par itération (Exemple : formule TRI) ne
parvient pas à trouver un résultat.
> Une valeur incorrecte est utilisée comme argument dans une formule
qui exige une valeur numérique (Exemple : si on renseigne 150euros
dans une formule au lieu de 150).
> ERREUR « #N/A » : Valeur manquante.
Signification : L’erreur « #N/A » survient généralement lorsqu’une formule ne trouve pas ce qu’on lui demande de rechercher.
=> Cette erreur est le plus souvent liée à l’utilisation des formules de recherche : RECHERCHEV, RECHERCHEH, RECHERCHEX et EQUIV.
Exemples :
Pour toutes les formules de recherche :
> La table de recherche n’est pas en référence absolue : Cela peut créer des erreurs sur une formule RECHERCHEV qu’on étire sur plusieurs lignes car la table matrice ne prendra plus en compte l’ensemble des valeurs du tableau de départ.
> Le format de la valeur cherchée est différent de celui des valeurs de la table de recherche : C’est le cas où la valeur cherchée est une valeur numérique et que les valeurs correspondantes de la table de recherche sont au format texte ou inversement => Dans ce cas-là il faut convertir les valeurs au format texte en nombre.
> La valeur cherchée n’est tout simplement pas retrouvée dans la table matrice.
Pour la formule RECHERCHEV :
> Si la valeur cherchée n’est pas située tout à gauche de la table matrice (sur la première colonne à gauche) et donc Excel ne peut pas la retrouver.
Pour la formule RECHERCHEH :
> Si la valeur cherchée n’est pas située tout en haut de la table matrice (sur la première ligne en haut) et donc Excel ne peut pas la retrouver.
> ERREUR « #REF! » : Référence de cellule non valide.
Signification : L’erreur « #REF! » survient lorsqu’une formule fait référence à une cellule qui n’est pas valide : Par exemple dans le cas où on a supprimé ou collé des cellules alors que d’autres formules y faisaient référence.
Exemple : On a un tableau qui permet de calculer le résultat d’une entreprise.
Si on supprime la ligne « MCV » :
Pour éviter cette erreur : Avant de supprimer une cellule il est possible de vérifier en amont si cette cellule est déjà utilisée dans une formule.
=> Pour cela il faut cliquer sur la cellule concernée puis dans l’onglet « Formules » il faut se rendre dans le groupe « Vérification des formules » et cliquer sur « Repérer les dépendants » :
=> Dans l’exemple si on effectue cette manipulation pour la cellule « MCV » Excel nous informe que cette cellule est utilisée dans la formule qui permet déterminer le « Résultat » :
> ERREUR « #VALEUR! » : Valeur non valide.
Signification : L’erreur « #VALEUR! » survient lorsque la saisie de la formule est incorrecte (= Type d’argument inapproprié) :
> Une plage de cellules est utilisée dans une formule qui ne demande qu’une seule valeur.
> Les éléments de la formule ne sont pas compatibles (Ex : = 10 + « cinq »).
> Du texte est saisi dans une formule qui demande une valeur numérique.
> ERREUR « #NOM? » : Nom non valide.
Signification : L’erreur « #NOM? » survient lorsqu’une formule utilise un texte qui n’est pas reconnu par Excel.
Plusieurs cas possibles :
> Erreur dans la saisie de la formule (Ex : =RECERCHEV au lieu de =RECHERCHEV).
> Plage nommée introuvable (car elle n’existe pas ou qu’il y a une faute de frappe dans la saisie du nom).
> Texte saisi dans une formule sans être placé entre guillemets : Dans ce cas Excel interprète ce texte comme étant une référence à une plage nommée et non comme à du simple texte (Ex : =GAUCHE(Jean;1)=#NOM? Alors que =GAUCHE(« Jean »;1)=J).
> ERREUR « ####### » : Problème d’affichage valeur numérique.
Signification : L’erreur « ####### » survient lorsque la largeur d’une colonne n’est pas suffisante pour afficher la totalité d’une valeur numérique (date, heure ou nombre).
=> Cette erreur apparait aussi en cas de calculs sur des heures qui aboutissent à un résultat négatif.
(…)
Formules de recherche :
RECHERCHEV
Principe : La formule Excel RECHERCHEV permet de rechercher une valeur dans la première colonne d’une table de données puis de renvoyer la valeur d’une cellule qui se situe sur la même ligne que la valeur recherchée.
= RECHERCHEV(valeur_cherchée ; table_matrice ; no_index_col ; [valeur_proche])
- Valeur qu’on souhaite retrouver dans la table matrice.
- Table dans laquelle on veut rechercher la valeur cherchée et extraire des données situées sur la même ligne.
- Colonne dans laquelle on veut extraire l’information souhaitée.
- [Facultatif] > Type de correspondance souhaité :
- FAUX ou 0 (Correspondance exacte) = Retrouve la première valeur exactement égale à la valeur cherchée (choix par défaut si non renseigné).
- VRAI ou 1 (Correspondance approximative) = Retrouve la plus grande valeur qui est inférieure ou égale à la valeur cherchée ; Les valeurs de l’argument « table_matrice » doivent être triées par ordre croissant.
Remarque : Il faut que la valeur_cherchée soit située tout à gauche de la table_matrice et les informations qu’on souhaite extraire sur la même ligne à droite de la valeur cherchée.
Exemple type de correspondance = FAUX : On a un tableau qui liste les véhicules d’une entreprise ; On a une liste déroulante (en cellule G1) qui contient l’immatriculation du véhicule et on souhaite retrouver plusieurs informations concernant e véhicule sélectionné (« XP30DM » dans l’exemple).
=> La condition est remplie puisque la valeur cherchée (immatriculation du véhicule) est bien située à gauche des informations qu’on souhaite rechercher (propriétaire et carburant).
> Propriétaire = RECHERCHEV($F$1 ; $A$1:$D$6 ; 2 ; FAUX) = Marie
=> no_index_col = 2 car on souhaite afficher l’information contenue dans la colonne « Propriétaire » et cette colonne est la 2ème colonne en partant du début de la « table_matrice » (début cellule A1 donc colonne A).
=> Figer la valeur cherchée et la table matrice permet d’étirer la formule pour les autres recherches et de modifier uniquement le numéro d’index colonne.
> Carburant = RECHERCHEV($F$1 ; $A$1:$D$6 ; 4 ; FAUX) = Gazole
Exemple type de correspondance = VRAI : On a une liste de clients et on souhaite déterminer le taux de remise à appliquer en fonction du total des ventes HT :
- De 0€ à 499€ => aucune remise
- De 500€ à 1999€ => 2% de remise
- De 2000€ à 4999€ => 3% de remise
- 5000€ ou plus => 5% de remise
=> Deux critères à respecter pour que la formule RECHERCHEV retrouve le bon taux de remise :
- Indiquer la borne inférieure de chaque intervalle.
- Classer les valeurs dans l’ordre croissant.
=> Il faut bien laisser la valeur cherchée en référence relative et figer la « table_matrice » pour la première formule pour pouvoir ensuite étirer la formule sur les autres lignes du tableau.
=> no_index_col = 2 car on souhaite afficher le taux de remise en fonction de la correspondance avec le montant des ventes HT (situé à gauche du taux de remise).
INDEX
Principe : La formule INDEX permet de renvoyer une valeur située dans une table de données en fonction d’un numéro de ligne et d’un numéro de colonne.
= INDEX(matrice ; no_lig ; [no_col])
- Plage de cellules dans laquelle on souhaite renvoyer la valeur en fonction du numéro de ligne et de colonne.
- Indique le numéro de ligne à utiliser pour renvoyer la valeur dans la matrice.
- Indique le numéro de colonne à utiliser pour renvoyer la valeur dans la matrice.
Remarque : Cette formule s’utilise principalement combinée avec une ou deux formule(s) EQUIV.
Exemple 1 : On a un tableau avec une liste d’immatriculations avec plusieurs informations les concernant ; On souhaite retrouver le propriétaire de l’immatriculation « XP30DM ».
=> Pour retrouver le résultat il faut combiner les formules INDEX et EQUIV.
= INDEX(B2:B6 ; EQUIV(« XP30DM »;A1:A6;0)) = Marie
> L’immatriculation « XP30DM » est retrouvée à la ligne 3 à partir de la première cellule de la matrice de recherche (A1) de la formule EQUIV.
> Il n’est pas nécessaire de renseigner le numéro de colonne (argument facultatif) puisque la matrice ne comporte qu’une seule colonne (colonne B).
=> La formule INDEX renvoie donc la valeur située à la ligne 3 de la matrice B2:B6.
=> Le résultat renvoyé est le même qu’avec la formule RECHERCHEV pour une correspondance exacte = FAUX ou 0.
Exemple 2 : Suite de l’exemple 2 sur la formule EQUIV : On a une liste de clients et on souhaite déterminer le taux de remise à appliquer en fonction du total des ventes HT.
=> Pour retrouver le résultat il faut combiner les formules INDEX et EQUIV.
Formule en cellule F2 :
= INDEX($B$3:$B$6 ; EQUIV(E2;$A$3:$A$6;1) ; 1)
=> Le numéro de colonne est statique puisqu’on recherche dans une seule colonne donc soit on précise 1 pour le numéro de colonne soit on ne renseigne pas cet argument qui est dans ce cas inutile.
=> Le résultat renvoyé est le même qu’avec la formule RECHERCHEV pour une correspondance approximative = VRAI ou 1.
Exemple 3 : Suite de l’exemple 3 sur la formule EQUIV : On a une liste de commerciaux et on souhaite retrouver le montant de la prime à octroyer en fonction du taux de service.
=> Pour retrouver le résultat il faut combiner les formules INDEX et EQUIV.
Formule en cellule F2 :
= INDEX($B$3:$B$6 ; EQUIV(E2;$A$3:$A$6;-1) ; 1)
=> Le numéro de colonne est statique puisqu’on recherche dans une seule colonne donc soit on précise 1 pour le numéro de colonne soit on ne renseigne pas cet argument qui est dans ce cas inutile.
=> La formule RECHERCHEV ne peut pas faire de recherche à partir de barème décroissant.
Exemple 4 : On a un tableau avec une liste d’immatriculations avec plusieurs informations les concernant ; On souhaite retrouver une information dans le tableau en fonction de 2 paramètres : L’immatriculation et le type d’information.
=> Pour retrouver le résultat il faut combiner les formules INDEX et EQUIV.
Formule en cellule B3 :
Possibilité 1 = INDEX(A5:E14 ; EQUIV(B1;A5:A14;0) ; EQUIV(B2;A5:E5;0))
Possibilité 2 = INDEX(B6:E14 ; EQUIV(B1;A6:A14;0) ; EQUIV(B2;B5:E5;0))
> L’immatriculation « KJP-523-LMP » est retrouvée à la ligne 5 à partir de la première cellule de la matrice de recherche (A6) de la 1ère formule EQUIV.
> L’information « N° de dossier » est retrouvée à la colonne 3 à partir de la première cellule de la matrice de recherche (B5) de la 2nde formule EQUIV.
=> La formule INDEX renvoie donc la valeur située au croisement de la ligne 5 et de la colonne 3 de la matrice B6:E14.
=> La formule RECHERCHEV ne peut pas faire de recherche à partir de critères en ligne et en colonne.
Remarque : Pour retrouver la bonne valeur mieux vaut appliquer la méthode de la « Possibilité 1 » : Dans ce cas la « matrice » de la formule INDEX et les « matrice_recherche » des formules EQUIV doivent avoir le même point de départ (cellule A5 dans l’exemple).
BDLIRE
Principe : La formule Excel BDLIRE permet d’extraire une valeur en fonction d’un champ et d’un critère à partir d’une colonne ou d’une base de données.
=> La formule BDLIRE est idéale pour faire une extraction de données.
= BDLIRE(base de données ; champ ; critères)
- Représente la plage de cellules de la base de données : La première ligne de la liste doit contenir les étiquettes de chaque colonne.
- Indique la colonne à utiliser pour la fonction => 3 options possibles :
- Entrez l’étiquette de la colonne entre guillemets doubles (par exemple « Prénom »).
- Cliquer directement sur l’en-tête de la colonne souhaitée.
- Nombre sans guillemets qui fait référence à la position de la colonne dans la liste (par exemple 1 pour la première colonne, 2 pour la seconde et ainsi de suite).
- Représente la plage de cellules qui contient les conditions spécifiées. Il faut bien que les noms des en-têtes de colonnes soient exactement les mêmes que ceux utilisés pour la base de données.
Remarques :
- Pour le champ et les critères il faut bien que les données soient en lignes et non en colonne.
- En cas de recherche sur la gauche c’est une alternative bien plus simple à l’utilisation de la combinaison des formules Index et Equiv.
- Avec la formule BDLIRE impossible de comparer 2 colonnes entre elles comme il est possible de le faire avec la formule RECHERCHEV.
- Si aucun enregistrement ne correspond aux critères => La formule renvoie l’erreur #valeur.
- Si plusieurs enregistrements répondent aux critères, la BDLIRE renvoie l’erreur #nombre (Contrairement à RechercheV qui elle pointe le premier trouvé dans le cas où la valeur cherchée est retrouvée plusieurs fois).
Exemple : On a un tableau qui renseigne plusieurs informations concernant les salariés d’une entreprise : Les salariés sont identifiés par un matricule.
=> On a une liste déroulante pour le choix du matricule et on souhaite obtenir plusieurs informations concernant le salarié en fonction du matricule sélectionné.
Formule en cellule B12 :
= BDLIRE($A$1:$F$9 ; B11 ; $A$11:$A$12)
=> Il faut bien laisser la cellule pour le champ en référence relative pour pouvoir ensuite étirer la formule sur les autres lignes du tableau (Nom, Service et Poste).
(…)
Formules logiques :
SI
Principe : La formule SI permet d’émettre une condition et de faire apparaître un résultat différent en fonction de la vérification ou non de cette condition.
= SI(test_logique ; [valeur_si_vrai] ; [valeur_si_faux])
- Test qu’on souhaite vérifier.
- Valeur qui apparaît si la condition est vérifiée.
- Valeur qui apparaît si la condition n’est pas vérifiée.
Remarques :
- La formule SI peut être combinées avec n’importe quelle formule qui peut être utilisée pour définir le test logique.
- En cas d’utilisation de plusieurs SI imbriqués pour appliquer un barème les conditions doivent être placées dans le bon ordre pour obtenir le résultat escompté puisque la formule globale renverra la [valeur_si_vrai] correspondant à la première formule SI dont le test logique sera vérifié.
Exemple 1 : Un seul test logique.
On a un tableau de présence et on souhaite faire apparaître le texte « Présent » lorsqu’il y a une croix dans la colonne présence et « absent » dans le cas inverse.
Formule en cellule C2 :
= SI(B2= »X » ; « Présent » ; « Absent »)
=> Il faut bien laisser la cellule à tester en référence relative pour pouvoir ensuite étirer la formule sur les autres lignes du tableau.
Exemple 2 : Trois possibilités (ou plus) avec valeurs alphabétiques.
On a une liste de produits à répartir en 3 catégories :
- Céréales : Avoine, blé, riz.
- Oléagineux : Amandes, noisettes, noix.
- Légumineuses : Flageolets, lentilles, pois-chiches.
Formule en cellule B2 :
= SI( OU(A2= »Avoine »;A2= »Blé »;A2= »Riz ») ; « Céréales » ; SI(OU(A2= »Amandes »;A2= »Noisettes;A2= »Noix ») ; « Oléagineux » ; « Légumineuses ») )
=> Il faut bien laisser la cellule à tester en référence relative pour pouvoir ensuite étirer la formule sur les autres lignes du tableau.
=> Dans ce type de cas (test sur valeurs alphabétiques) l’ordre des formules SI n’a pas d’importance puisque la vérification d’un test logique est unique : Il ne peut pas y avoir plusieurs tests logiques qui sont vrais.
Exemple : Si A2 = A alors A2 ne sera pas égal à B ni à C.
Exemple 3 : Trois possibilités ou plus avec valeurs numériques (Barème).
On souhaite attribuer une mention en fonction de la moyenne des notes obtenues par des étudiants à un examen :
- Si la note est supérieure ou égale à 16 => « Mention TB »
- Si la note est supérieure ou égale à 14 => « Mention B »
- Si la note est supérieure ou égale à 12 => « Mention AB »
- Si la note est supérieure ou égale à 10 => « Admis »
- Si la note est inférieure ou égale à 10 => « Refusé »
Formule en cellule C2 :
= SI( B2>=16 ; « Mention TB » ; SI(B2>=14 ; « Mention B » ; SI(B2>=12 ; « Mention AB » ; SI(B2>=10 ; « Admis » ; « Refusé »))) )
=> On commence bien les conditions dans le bon ordre pour obtenir le bon résultat ; Si on avait commencé par le test logique B3>=10 ; « Admis » alors toutes les notes supérieures ou égales à 10 auraient le résultat « Admis » sans les mentions car une note >=12 ou >=14 ou >=16 est forcément >=10 et ainsi de suite.
Exemple 4 : Tests logiques à conditions multiples.
On a un tableau qui récapitule les ventes générées par un commercial sur toute une année et on souhaite attribuer une prime en fonction des ventes :
- Prime = 100€ si nombre ventes > 10 ET montant ventes > 4 500€
- Prime = 50€ si nombre ventes > 10 ET montant ventes > 4 500€
- Aucune prime dans les autres cas.
Formule en cellule D2 :
= SI( ET(B2>10;C2>4500) ; 100 ; SI(OU(B2>10;C2>4500) ; 50 ; « Aucune prime ») )
=> On commence bien par la prime la plus difficile à obtenir (100€) et on termine par celle qui nécessite le moins de condition (aucune prime) ; Si on commence par la prime de 50€ dans l’ordre des formules SI alors la formule renverra aussi une prime de 50€ même si les 2 conditions sont remplies car dans l’exemple si les conditions sont remplies pour l’obtention d’une prime de 100€ alors elles le sont pour une prime de 50€.
(…)
Formules de texte :
STXT
Principe : La formule STXT permet d’extraire un nombre défini de caractères à partir d’un emplacement spécifié.
= STXT(texte ; no_départ ; no_car)
- Texte qui contient les caractères à extraire.
- Position dans le texte du premier caractère à extraire.
- Nombre de caractères à extraire.
Exemple : On souhaite extraire le code postal (sur 5 caractères) situé au milieu d’une chaine de caractère (en cellule A1).
JeanROBIN13100Aix-en-Provence => STXT(A1 ; 11 ; 5) = 13100
=> Le code postal commence au 11ème caractère et compte 5 caractères.
Exemple combiné 1 : Extraction de la plaque d’immatriculation.
= STXT(A2 ; CHERCHE(« ??- ???- ?? »;A2) ; 9)
=> Le début de l’immatriculation est retrouvé avec la formule CHERCHE et on sait que l’immatriculation compte 9 caractères.
Exemple combiné 2 : Extraction du numéro de patient.
= STXT(A2 ; CHERCHE(« Num????? »;A2) ; 8)
=> Il faut bien laisser l’argument « texte » des formules STXT et CHERCHE en référence relative pour la première formule pour pouvoir ensuite étirer la formule sur les autres lignes du tableau.
Problème : Pour la patiente Julie NUMOY la formule ne renvoie pas le bon résultat car la formule CHERCHE ne tient pas compte de la casse et renvoie la position de la première correspondance « NUMOYNum ».
=> Afin de retrouver le bon résultat il faut utiliser la formule TROUVE qui elle tient compte de la casse.
= STXT(A2 ; TROUVE(« Num »;A2) ; 8)
=> Le début du numéro du patient est retrouvé avec la formule TROUVE et on sait que le numéro de patient compte 8 caractères.
FILTRE / Microsoft 365
Principe : La formule FILTRE permet de réaliser des filtres dynamiques dans une plage de données en fonction de critères.
= FILTRE(tableau ; inclure ; [si_vide])
- Plage de données qu’on souhaite filtrer.
- Filtres à appliquer au tableau.
- [Facultatif] : Contenu à renseigner dans le cas où la formule renvoie un résultat vide (cas où aucune donnée du tableau ne correspond aux filtres appliqués) => à saisir entre guillemets (« ») s’il s’agit d’un texte.
Remarques :
- Pour appliquer plusieurs filtres dans l’argument « inclure » il faut les saisir sous forme de matrice => Exemple: (filtre 1)*(filtre 2).
- La formule FILTRE est une formule propagée.
Exemple : On a une liste de commandes et on souhaite filtrer les commandes en fonction du nom client, de la désignation et du montant.
1) Filtrer sur les commandes passées par le client Super elec :
Formule en cellule F2 :
= FILTRE(A2:D11 ; B2:B11=K3 ; « Aucune commande trouvée »)
2) Filtrer sur les commandes d’aspirateurs passées par le client Big market :
Formule en cellule F2 :
= FILTRE(A2:D11 ; (B2:B11=K3)*(C2:C11=K5) ; « Aucune commande trouvée »)
3) Filtrer sur les commandes de mixeurs passées par le client Big market :
Formule en cellule F2 : Idem que pour 2)
=> La formule renvoie le texte renseigné dans l’argument [si_vide] puisqu’aucun résultat n’est retrouvé par la formule FILTRE : En effet le client Big market n’a passé aucune commande de mixeur.
4) Filtrer sur les commandes entre 1 000€ et 3 000€ :
Formule en cellule F2 :
= FILTRE(A2:D11 ; (D2:D11>=K3)*(D2:D11<=L3) ; « Aucune commande trouvée »)
(…)
Formules de date :
ANNEE
Principe : La formule ANNEE permet de renvoyer l’année à partir d’une date sous la forme d’un nombre entier compris entre 1900 et 9999.
= ANNEE(numéro_de_série)
- Date à partir de laquelle on souhaite extraire l’année.
Exemple : On souhaite extraire l’année à partir d’une date contenue en cellule A2 (17/10/2020).
= ANNEE(A2) = 2020
Remarque : Le 01/01/1900 correspond au numéro séquentiel 1 et le décompte des dates postérieures se fait à partir de cette date ; Par exemple la date du 01/01/2020 correspond au numéro séquentiel 43831 car 43 831 jours se sont écoulés depuis le 01/01/1900.
MOIS
Principe : La formule MOIS permet de renvoyer le mois à partir d’une date sous la forme d’un nombre entier compris entre 1 (Janvier) et 12 (Décembre).
= MOIS(numéro_de_série)
- C’est la date à partir de laquelle on souhaite extraire le mois.
Exemple : On souhaite extraire le mois à partir d’une date contenue en cellule A2 (17/10/2020).
= MOIS(A2) = 10
JOUR
Principe : La formule JOUR permet de renvoyer le jour à partir d’une date sous la forme d’un nombre entier compris entre 1 et 31.
= JOUR(numéro_de_série)
- C’est la date à partir de laquelle on souhaite extraire le jour.
Exemple : On souhaite extraire le jour à partir d’une date contenue en cellule A2 (17/10/2020).
= JOUR(A2) = 17
FIN.MOIS
Principe : La formule FIN.MOIS permet de renvoyer la date du dernier jour du mois précédant ou suivant une date de départ en fonction du nombre de mois indiqué.
= FIN.MOIS(date_départ ; mois)
- Date de début du calcul.
- Nombre de mois avant ou après la date de départ :
- Valeur négative pour reculer du nombre de mois dans le passé.
- Valeur positive pour avancer du nombre de mois dans le futur.
- 0 pour calculer la date de fin du mois de la date de départ.
Exemple 1 :
= FIN.MOIS(« 10/02/2020 » ; 0) = 29/02/2020
= FIN.MOIS(« 15/02/2020 » ; 1) = 31/03/2020
= FIN.MOIS(« 08/10/2020 » ; 2) = 31/12/2020
Exemple 2 : On a des factures et on souhaite connaitre la date d’échéance en fonction des conditions de paiement.
= FIN.MOIS(A2 ; 1) = 30/06/2020
= FIN.MOIS(A3+45 ; 0) => FIN.MOIS(« 02/07/2020 » ; 0) = 31/07/2020
= FIN.MOIS(A4 ; 1)+10 => 30/06/2020+10 = 10/07/2020
= FIN.MOIS(A5 ; 1)+15 => 30/06/2020+15 = 15/07/2020
SERIE.JOUR.OUVRE
Principe : La formule SERIE.JOUR.OUVRE permet de déterminer un jour ou une liste de jours en ne tenant compte que des jours ouvrés : La formule compte 5 jours ouvrés par semaine et exclut donc les week-end.
=> La formule permet aussi de prendre en compte les jours fériés à exclure du calcul.
= SERIE.JOUR.OUVRE(date_départ ; nb_jours ; [jours_fériés])
- Correspond à la date de départ du calcul.
- Nombre de jours ouvrés avant ou après la date de départ :
- Valeur négative pour retrouver une date passée.
- Valeur positive pour retrouver une date future.
- [Facultatif] : Liste des jours à exclure du calcul.
Exemple : On souhaite déterminer la date butoir d’un projet avec les conditions suivantes : Date de début du projet = 08/07/2020 (cellule E1) et nombre de jours ouvrés prévus pour réaliser le projet = 5 (cellule E2).
= SERIE.JOUR.OUVRE(E1 ; E2 ; B2:B12) = 16/07/2020
> Cette date correspond à la date de départ + 5 jours ouvrés sans les week-ends (11 et 12/07/2020) et sans les jours fériés (14/07/2020) :
=> 1 = 09/07 ; 2 = 10/07 ; 3 = 13/07 ; 4 = 15/07 ; 5 = 16/07.
Pour considérer la date de départ comme un jour ouvré utilisé pour réaliser le projet il faut soustraire 1 jour au résultat pour obtenir la date butoir du 15/07/2020.
Formules mathématiques :
SOMMEPROD
Principe : La formule SOMMEPROD permet de faire la somme des produits des matrices indiquées.
= SOMMEPROD(matrice1 ; [matrice2] ; [matrice3] …)
- Matrice pour lesquelles on souhaite multiplier les valeurs entre elles et faire la somme des produits.
Remarques :
- Il est possible de réaliser d’autres opérations que la multiplication : Pour cela il faut remplacer les points-virgules entre les matrices par les opérateurs souhaités (- ; + ; * ; /) => Une fois toutes les opérations calculées les résultats sont additionnés.
- Il est possible d’ajouter des conditions aux calculs effectués : Pour cela il faut mettre entre parenthèses les matrices et insérer entre les matrices les opérateurs souhaités.
Exemple 1 : Somme de multiplications.
On a un tableau qui récapitule les ventes d’une entreprise et on souhaite calculer le total du CA généré.
= SOMMEPROD(D2:D10 ; E2:E10) OU = SOMMEPROD(D2:D10 * E2:E10)
Détail du calcul : 2 950 €
= 5*100 + 2*50 + 3*100 + 4*100 + 6*50 + 5*100 + 3*50 + 6*50 + 4*100
Exemple 2 : Somme de multiplications avec conditions.
Suite de l’exemple 1 excepté que cette fois-ci on souhaite calculer le total du CA généré uniquement dans la ville de LYON sur la vente de tables.
= SOMMEPROD((B2:B10= »LYON ») * (C2:C10= »Chaise ») * (D2:D10) * (E2:E10))
Détail du calcul : 250 €
= 2*50 + 3*50
Exemple 3 : Somme de multiplications de matrices de tailles différentes.
On a la liste des notes obtenues par les étudiants d’une classe et on souhaite calculer la moyenne générale sur l’ensemble des épreuves.
= SOMMEPROD(B2*B3:B10 + C2*C3:C10 + D2*D3:D10) /(B2+C2+D2) /8
Détail du calcul : 13,26
= ((5*20 + 5*19 + 5*12 + 5*6 + 5*5 + 5*18 + 5*12 + 5*14) + (2*16 + 2*8 + 2*11 + 2*7 + 2*15 + 2*10 + 2*10 + 2*19) + (3*18 + 3*19 + 3*12 + 3*16 + 3*14 + 3*13 + 3*8 + 3*13)) / (5+2+3) / 8
*(5 + 2 + 3) = 10 => Somme des coefficients.
*8 => Nombre d’étudiants.
Exemple 4 : Opérations multiples.
On a un tableau qui récapitule les ventes d’une entreprise et on souhaite calculer le total du CA généré.
= SOMMEPROD(B2:B6 * (C2:C6 – D2:D6))
Détail du calcul : 5 430 €
= 10*(250-85) + 5*(200-70) + 8*(500-180) + 12*(50-15) + 15*(15-5)
Exemple 5 : Somme avec conditions en ligne et en colonne.
On a un tableau qui récapitule les chiffres d’affaires mensuels des 3 dernières années.
=> on souhaite retrouver le chiffre d’affaires réalisé sur une période précise en fonction du mois (en ligne) et de l’année (en colonne).
=> Le mois et l’année sont sélectionnés à partir d’une liste déroulante :
Calcul pour l’année :
= SOMMEPROD((B1:D1=H3) * (B2:D13)) = 145 794 €
Calcul pour le mois et l’année :
= SOMMEPROD((A2:A13=F3) * (B1:D1=H3) * (B2:D13)) = 9 742 €
Calcul pour le trimestre et l’année :
=> Dans ce cas il faut créer une nouvelle colonne pour pouvoir indexer le numéro du trimestre en fonction du mois.
=> Cela a pour effet de décaler les colonnes de choix :
= SOMMEPROD((B2:B13=H3) * (C1:E1=I3) * (C2:E13)) = 35 572 €
ARRONDI
Principe : La formule ARRONDI permet d’arrondir un nombre en fonction du nombre de chiffres souhaités.
= ARRONDI(nombre ; no_chiffres)
- C’est le nombre à arrondir.
- Correspond au nombre de chiffres souhaité pour arrondir le nombre.
Trois possibilités pour l’argument no_chiffres :
- Si no_chiffres est supérieur à 0 => L’argument nombre est arrondi en fonction du nombre de décimales indiquées.
- Si no_chiffres est égal à 0 => L’argument nombre est arrondi au nombre entier le plus porche.
- Si no_chiffres est inférieur à 0 => L’argument nombre est arrondi à gauche de la virgule en fonction du nombre indiqué.
Exemple : On souhaite arrondir le nombre contenu dans la cellule A1 en fonction du cas :
12,147 => ARRONDI(A1 ; 0) = 12
12,147 => ARRONDI(A1 ; 1) = 12,1
12,147 => ARRONDI(A1 ; 2) = 12,15
1147,55 => ARRONDI(A1 ; -1) = 1 150
1147,55 => ARRONDI(A1 ; -2) = 1 100
1147,55 => ARRONDI(A1 ; -3) = 1 000
ARRONDI.INF
Principe : La formule ARRONDI.INF permet d’arrondir un nombre à sa valeur inférieure en fonction du nombre de chiffres souhaités.
= ARRONDI.INF(nombre ; no_chiffres)
- C’est le nombre à arrondir.
- Correspond au nombre de chiffres souhaité pour arrondir le nombre à l’inférieur.
Les trois possibilités pour l’argument no_chiffres sont les mêmes que pour la formule ARRONDI.
Exemple : On souhaite arrondir à sa valeur inférieure le nombre contenu dans la cellule A1 en fonction du cas :
12,147 => ARRONDI.INF(A1 ; 0) = 12
12,147 => ARRONDI.INF(A1 ; 1) = 12,1
12,147 => ARRONDI.INF(A1 ; 2) = 12,14
1147,55 => ARRONDI.INF(A1 ; -1) = 1 140
1147,55 => ARRONDI.INF(A1 ; -2) = 1 100
1147,55 => ARRONDI.INF(A1 ; -3) = 1 000
ARRONDI.SUP
Principe : La formule ARRONDI.SUP permet d’arrondir un nombre à sa valeur supérieure en fonction du nombre de chiffres souhaités.
= ARRONDI.SUP(nombre ; no_chiffres)
- C’est le nombre à arrondir.
- Correspond au nombre de chiffres souhaité pour arrondir le nombre à sa valeur supérieure.
Les trois possibilités pour l’argument no_chiffres sont les mêmes que pour la formule ARRONDI.
Exemple : On souhaite arrondir à sa valeur supérieure le nombre contenu dans la cellule A1 en fonction du cas :
12,147 => ARRONDI.SUP(A1 ; 0) = 13
12,147 => ARRONDI.SUP(A1 ; 1) = 12,2
12,147 => ARRONDI.SUP(A1 ; 2) = 12,15
1147,55 => ARRONDI.SUP(A1 ; -1) = 1 150
1147,55 => ARRONDI.SUP(A1 ; -2) = 1 200
1147,55 => ARRONDI.SUP(A1 ; -3) = 2 000
(…)
Formules statistiques :
MODE
Principe : La formule MODE permet de retrouver la valeur la plus fréquente dans une plage de données.
= MODE(plage de nombres)
- Plage de cellules à l’intérieur de laquelle on souhaite retrouver la valeur la plus fréquente.
Exemple : On a la liste des notes obtenues par des élèves et on veut connaître la note qui est la plus souvent obtenue.
= MODE(B2:B11) = 8
=> La note « 8 » est la note la plus souvent obtenue par les élèves de cette classe.
RANG
Principe : La formule RANG permet de classer des valeurs sur Excel (dans l’ordre croissant ou décroissant).
= RANG(nombre ; référence ; [ordre])
- Nombre pour lequel on souhaite déterminer le rang.
- Plage de cellules qui contient toutes les valeurs à partir de laquelle on souhaite déterminer le rang.
- [Facultatif] : Pour un ordre décroissant il faut laisser vide (ou entrer 0) et pour un ordre croissant il faut entrer une valeur différente de 0.
Remarque : Si deux valeurs ou plus sont identiques alors elles obtiennent le même rang et le rang suivant saute en fonction du nombre de valeurs identiques.
Exemple : On la liste des notes obtenues par des élèves et on veut calculer le rang de chaque élève en fonction de la note obtenue ; en classant l’élève avec la meilleure note au rang numéro 1.
=> Il faut bien laisser la cellule qui contient la note en référence relative et figer la « référence » pour pouvoir ensuite étirer la formule sur les autres lignes du tableau.
=> Les élèves avec la note de 8 sont classés au rang 7 et comme cette note apparaît 3 fois, le rang suivant est le rang 10 puisque dans ce cas le rang 7 occupe les rangs 7, 8 et 9.
GRANDE.VALEUR
Principe : La formule GRANDE.VALEUR permet de renvoyer la k-ième plus grande valeur d’une série de données.
= GRANDE.VALEUR(matrice ; k)
- La plage de données dans laquelle on cherche la k-ième plus grande valeur.
- La position de la plus grande valeur à renvoyer (Exemple : k = 2 pour renvoyer la 2ème plus grande valeur de la plage de données).
Exemple 1 : On a la liste des notes obtenues par 10 élèves et on souhaite connaître les 3 notes les plus élevées.
Pour la meilleure note :
= GRANDE.VALEUR(B2:B11 ; 1) = 20
Pour la 2ème note la plus élevée :
= GRANDE.VALEUR(B2:B11 ; 2) = 18
Pour la 3ème note la plus élevée :
= GRANDE.VALEUR(B2:B11 ; 3) = 15
Exemple combiné 1 : On a la liste des ventes réalisées dans 2 villes (Lyon et Paris) sur 2 produis (Table et Chaise) et on souhaite faire la somme des 5 plus grosses commandes.
Formule en cellule G3 :
=SOMME(GRANDE.VALEUR((E2:E11) ; {1;2;3;4;5})) = 4 800 €
Exemple combiné 2 : Même exemple qu’exemple combiné 1 mais cette fois-ci on souhaite faire la somme des 3 plus grosses commandes passées dans la ville de Paris pour le produit Chaise.
Formule en cellule G6 :
=SOMME(GRANDE.VALEUR( (A2:A11=G3)*(B2:B11=H3)*(E2:E11) ; {1;2;3} )) = 1 200 €
MIN
Principe : La formule MIN permet de renvoyer la plus petite valeur parmi une liste de valeurs.
= MIN(plage de nombres)
- Plage de cellules pour laquelle on souhaite retrouver la plus petite valeur.
Exemple : On a la liste des notes obtenues par 10 élèves et on souhaite connaître la note la moins élevée.
= MIN(B2:B11) = 7
=> Le résultat renvoyé est le même qu’avec la formule PETITE.VALEUR pour k=1.
MAX
Principe : La formule MAX permet de renvoyer la plus grande valeur parmi une liste de valeurs.
= MAX(plage de nombres)
- Plage de cellules pour laquelle on souhaite retrouver la plus grande valeur.
Exemple : On a la liste des notes obtenues par 10 élèves et on souhaite connaître la note la plus élevée.
= MAX(B2:B11) = 20
=> Le résultat renvoyé est le même qu’avec la formule GRANDE.VALEUR pour k=1.
(…)
Formules de localisation :
LIGNE
Principe : La formule LIGNE permet de renvoyer le numéro de ligne en fonction d’une référence de cellule.
= LIGNE([référence])
- [Facultatif] : Cellule pour laquelle on souhaite obtenir le numéro de ligne : Si cet argument n’est pas renseigné c’est la référence de la cellule dans laquelle la formule LIGNE apparaît qui est renvoyée.
COLONNE
Principe : La formule COLONNE permet de renvoyer le numéro de colonne en fonction d’une référence de cellule.
= COLONNE([référence])
- [Facultatif] : Cellule pour laquelle on souhaite obtenir le numéro de colonne : Si cet argument n’est pas renseigné c’est la référence de la cellule dans laquelle la formule COLONNE apparait qui est renvoyée.
DECALER
Principe : La formule DECALER permet de renvoyer la référence d’une cellule ou d’une plage de cellules en fonction d’un décalage exprimé en nombre de lignes/nombre de colonnes ainsi que d’une taille exprimée en hauteur/largeur.
= DECALER(réf ; lignes ; colonnes ; [hauteur] ; [largeur])
- Référence par rapport à laquelle le décalage doit être opéré (= Point de départ).
- Nombre de lignes vers le haut ou vers le bas dont la référence de la cellule tout en haut à gauche de la réf doit être décalée :
- Valeur négative pour effectuer un décalage en dessous de la référence de départ.
- Valeur positive pour effectuer un décalage au-dessus de la référence de départ.
- Nombre de colonnes vers la gauche ou vers la droite dont la référence de la cellule tout en haut à gauche de la réf doit être décalée :
- Valeur négative pour effectuer un décalage à gauche de la référence de départ.
- Valeur positive pour effectuer un décalage à droite de la référence de départ.
- [Facultatif] : Hauteur de la plage de cellules de la référence renvoyée => Doit être un nombre positif.
- [Facultatif] : Largeur de la plage de cellules de la référence renvoyée => Doit être un nombre positif.
Remarques :
- Cette formule ne sert pas à décaler des cellules dans la feuille Excel, elle renvoie simplement une référence.
- Si les arguments hauteur ou largeur sont omis, les valeurs par défaut de ces arguments sont celles de la réf.
- La formule DECALER peut être utilisée avec toutes les fonctions exigeant une référence comme argument.
Exemple 1 : Liste déroulante dynamique.
On souhaite créer une liste déroulante dynamique (dont la taille s’adapte en fonction du nombre de valeurs) à partir d’une liste de modèle de voitures dont la source n’est pas mise sous forme de tableau.
> La liste à partir de laquelle on souhaite créer la liste déroulante se trouve dans la feuille « Modèles » :
=> Pour parvenir à créer cette liste déroulante dynamique il faut combiner les formules DECALER et NBVAL :
> La formule DECALER va permettre de sélectionner la liste de manière dynamique grâce à l’argument [hauteur].
> La formule NBVAL va justement permettre de définir le nombre de cellules à prendre en compte pour l’argument [hauteur] de la formule DECALER en comptant le nombre de cellules non vides dans la liste de voitures.
=>Il faut donc insérer cette formule combinée pour la source de la liste déroulante du choix du modèle.
Formule pour la source de la liste déroulante :
= DECALER(Modèles!A2 ; 0 ; 0 ; NBVAL(Modèles!A:A)-1)
- Modèles!A2 = Première valeur de la liste (« « C1 » » dans l’exemple).
- 0 ; 0 = Aucun décalage ni sur les lignes ni sur les colonnes pour rester sur la première valeur de la liste comme point de départ.
- NBVAL(Modèles!A:A)-1 = Pour la hauteur on compte le nombre de cellules non vides dans la colonne dans laquelle on a la plage de cellules qui sert à créer la liste déroulante et on enlève 1 pour ne pas prendre en compte la cellule non vide qui correspond à l’en-tête de colonne.
=> Si on ajoute le modèle « Picasso » dans la liste de véhicules :
=> La source de la liste déroulante s’adapte à cette nouvelle entrée :
Exemple 2 : Combinaison avec la formule SOMME.
On souhaite calculer le CA généré sur les n premiers mois : n correspond donc au nombre de mois pour lesquels on souhaite calculer le total du CA cumulé (en partant du mois de Janvier).
Formule en cellule D4 :
= SOMME( DECALER(B2 ; 0 ; 0 ; D2) )
Détail du calcul : 4 500 €
= 1 500 => Point de départ (cellule B2 = CA de Janvier)
+ 1 400 (cellule B3 = CA de Février) + 1 600 (cellule B4 = CA de Mars) => Car décalage de 3 par rapport au point de départ (cellule B2)
= CA des 3 premiers mois.
Formules d’information :
CELLULE
Principe : La formule CELLULE permet de renvoyer un type d’information concernant une cellule.
= CELLULE(type_info ; [référence])
- Type d’information qu’on souhaite obtenir :
- « adresse »
- « contenu » (Renvoie le texte qui apparaît dans la cellule ; Si la cellule contient une formule alors elle renvoie le résultat de la formule)
- « couleur » (Attention ce type d’info ne renvoie pas la couleur de la cellule)
- …
- [Facultatif] : Cellule pour laquelle on souhaite obtenir le type d’info choisi : Si cet argument n’est pas renseigné c’est le type d’info de la cellule dans laquelle la formule CELLULE apparait qui est renvoyé.
Exemples :
= CELLULE(« contenu« ; C3) = $C$3
= CELLULE(« adresse« ; C3) = Excel
= CELLULE(« adresse« ) = $B$2 => La référence de cellule n’est pas précisée donc par défaut la formule renvoie l’adresse de la cellule dans laquelle elle se trouve.
>> Télécharger l’extrait au format PDF
=> Retour à la page d’accueil
