Axel ROBIN excel-en-line.

fr
Axel ROBIN [Link]
Introduction : ...................................................................................... 1
Les références de cellule ..................................................................... 1
Les types d’erreurs ............................................................................... 5
Les formules matricielles ................................................................... 10
Les caractères génériques ................................................................ 17
Formules de recherche : .............................................................. 18
RECHERCHEV ..................................................................................... 18
RECHERCHEH .................................................................................... 21
RECHERCHEX ..................................................................................... 23
EQUIV ................................................................................................... 33
INDEX ................................................................................................... 37
BDLIRE................................................................................................. 42
CHOISIR ............................................................................................... 44
Formules logiques : ....................................................................... 45
ET ......................................................................................................... 45
OU......................................................................................................... 46
SI........................................................................................................... 47
[Link] / Microsoft 365 ............................................................. 52
[Link] / Microsoft 365 ......................................................... 53
SIERREUR ........................................................................................... 54
Formules de texte : ........................................................................ 55
MAJUSCULE ....................................................................................... 55
MINUSCULE ........................................................................................ 55
NOMPROPRE ...................................................................................... 56
TEXTE .................................................................................................. 57
SUPPRESPACE ................................................................................... 58

Axel ROBIN [Link]

 SUBSTITUE ......................................................................................... 59
NBCAR ................................................................................................. 61
[Link] / Microsoft 365 ....................................................... 62
GAUCHE .............................................................................................. 64
DROITE ................................................................................................ 64
CHERCHE ............................................................................................ 65
TROUVE ............................................................................................... 66
STXT ..................................................................................................... 67
TRIER / Microsoft 365 ......................................................................... 69
UNIQUE / Microsoft 365 ...................................................................... 70
FILTRE / Microsoft 365 ....................................................................... 72
Formules de date : ......................................................................... 75
ANNEE ................................................................................................. 75
MOIS ..................................................................................................... 75
JOUR .................................................................................................... 76
DATE .................................................................................................... 76
AUJOURDHUI ...................................................................................... 77
JOURSEM ............................................................................................ 78
[Link] ....................................................................................... 79
[Link] ................................................................................ 80
[Link] .............................................................................................. 81
DATEDIF .............................................................................................. 82
[Link] ........................................................................... 83
[Link] .................................................................. 84
[Link] .......................................................................... 85
[Link] ................................................................. 86
Formules mathématiques : ........................................................ 87
SOMME ................................................................................................ 87
[Link] ............................................................................................ 89
Axel ROBIN [Link]
 [Link] ................................................................................... 90
SOMMEPROD ...................................................................................... 92
[Link] ....................................................................................... 98
[Link] .................................................................... 100
SEQUENCE / Microsoft 365 ............................................................. 102
ARRONDI ........................................................................................... 104
[Link] .................................................................................... 105
[Link] ................................................................................... 106
[Link] .................................................................. 107
Formules statistiques :................................................................ 108
NB ....................................................................................................... 108
NBVAL................................................................................................ 108
[Link] ............................................................................................. 108
[Link] .................................................................................................. 109
[Link] .......................................................................................... 110
MODE ................................................................................................. 112
MEDIANE ........................................................................................... 113
MOYENNE .......................................................................................... 114
ECARTYPEP ...................................................................................... 115
ECARTYPE ........................................................................................ 117
RANG ................................................................................................. 118
FREQUENCE ..................................................................................... 120
[Link] ............................................................................... 122
[Link] ............................................................................ 124
MIN ..................................................................................................... 126
MAX .................................................................................................... 127
[Link] / Microsoft 365 .............................................................. 128
[Link] / Microsoft 365 ............................................................. 129

Axel ROBIN [Link]

Formules financières : ................................................................ 130
VAN .................................................................................................... 130
TRI ...................................................................................................... 131
Formules de localisation :......................................................... 132
LIGNE ................................................................................................. 132
COLONNE .......................................................................................... 132
ADRESSE .......................................................................................... 133
LIEN_HYPERTEXTE .......................................................................... 134
DECALER .......................................................................................... 135
INDIRECT ........................................................................................... 140
Formules d’information : .......................................................... 143
ESTFORMULE ................................................................................... 143
ESTVIDE ............................................................................................ 143
CELLULE ........................................................................................... 144

Axel ROBIN [Link]

Introduction :

Télécharger l’ebook complet

[Link] 1 Sommaire
Télécharger l’ebook complet

[Link] 2 Sommaire
Télécharger l’ebook complet

[Link] 3 Sommaire
Télécharger l’ebook complet

[Link] 4 Sommaire
Signification : L’erreur "#DIV/0" survient lorsqu’on tente de diviser un
nombre par 0 (zéro) ou par une cellule vide.

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.

[Link] 5 Sommaire
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).

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.

[Link] 6 Sommaire
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" :

[Link] 7 Sommaire
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" :

[Link] 8 Sommaire
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.

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).

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.

[Link] 9 Sommaire
Télécharger l’ebook complet

[Link] 10 Sommaire
Télécharger l’ebook complet

[Link] 11 Sommaire
Télécharger l’ebook complet

[Link] 12 Sommaire
Télécharger l’ebook complet

[Link] 13 Sommaire
Télécharger l’ebook complet

[Link] 14 Sommaire
Télécharger l’ebook complet

[Link] 15 Sommaire
Télécharger l’ebook complet

[Link] 16 Sommaire
Télécharger l’ebook complet

[Link] 17 Sommaire
Formules de recherche :

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.

[Link] 18 Sommaire
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

[Link] 19 Sommaire
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).

[Link] 20 Sommaire
Télécharger l’ebook complet

[Link] 21 Sommaire
Télécharger l’ebook complet

[Link] 22 Sommaire
Télécharger l’ebook complet

[Link] 23 Sommaire
Télécharger l’ebook complet

[Link] 24 Sommaire
Télécharger l’ebook complet

[Link] 25 Sommaire
Télécharger l’ebook complet

[Link] 26 Sommaire
Télécharger l’ebook complet

[Link] 27 Sommaire
Télécharger l’ebook complet

[Link] 28 Sommaire
Télécharger l’ebook complet

[Link] 29 Sommaire
Télécharger l’ebook complet

[Link] 30 Sommaire
Télécharger l’ebook complet

[Link] 31 Sommaire
Télécharger l’ebook complet

[Link] 32 Sommaire
Télécharger l’ebook complet

[Link] 33 Sommaire
Télécharger l’ebook complet

[Link] 34 Sommaire
Télécharger l’ebook complet

[Link] 35 Sommaire
Télécharger l’ebook complet

[Link] 36 Sommaire
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.

[Link] 37 Sommaire
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.

[Link] 38 Sommaire
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.

[Link] 39 Sommaire
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.

[Link] 40 Sommaire
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).
[Link] 41 Sommaire
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).

[Link] 42 Sommaire
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).

[Link] 43 Sommaire
Télécharger l’ebook complet

[Link] 44 Sommaire
Formules logiques :

Télécharger l’ebook complet

[Link] 45 Sommaire
Télécharger l’ebook complet

[Link] 46 Sommaire
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é.

[Link] 47 Sommaire
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.

[Link] 48 Sommaire
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.

[Link] 49 Sommaire
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.

[Link] 50 Sommaire
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€.
[Link] 51 Sommaire
 / Microsoft 365

Télécharger l’ebook complet

[Link] 52 Sommaire
 / Microsoft 365

Télécharger l’ebook complet

[Link] 53 Sommaire
Télécharger l’ebook complet

[Link] 54 Sommaire
Formules de texte :

Télécharger l’ebook complet

Télécharger l’ebook complet

[Link] 55 Sommaire
Télécharger l’ebook complet

[Link] 56 Sommaire
Télécharger l’ebook complet

[Link] 57 Sommaire
Télécharger l’ebook complet

[Link] 58 Sommaire
Télécharger l’ebook complet

[Link] 59 Sommaire
Télécharger l’ebook complet

[Link] 60 Sommaire
Télécharger l’ebook complet

[Link] 61 Sommaire
 / Microsoft 365

Télécharger l’ebook complet

[Link] 62 Sommaire
Télécharger l’ebook complet

[Link] 63 Sommaire
Télécharger l’ebook complet

Télécharger l’ebook complet

[Link] 64 Sommaire
Télécharger l’ebook complet

[Link] 65 Sommaire
Télécharger l’ebook complet

[Link] 66 Sommaire
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.

[Link] 67 Sommaire
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.

[Link] 68 Sommaire
 / Microsoft 365

Télécharger l’ebook complet

[Link] 69 Sommaire
 / Microsoft 365

Télécharger l’ebook complet

[Link] 70 Sommaire
Télécharger l’ebook complet

[Link] 71 Sommaire
 / 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")

[Link] 72 Sommaire
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.

[Link] 73 Sommaire
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")

[Link] 74 Sommaire
Formules de date :

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.

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
[Link] 75 Sommaire
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

Télécharger l’ebook complet

[Link] 76 Sommaire
Télécharger l’ebook complet

[Link] 77 Sommaire
Télécharger l’ebook complet

[Link] 78 Sommaire
Télécharger l’ebook complet

[Link] 79 Sommaire
Télécharger l’ebook complet

[Link] 80 Sommaire
Principe : La formule [Link] 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é.

= [Link](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 :
= [Link]("10/02/2020" ; 0) = 29/02/2020
= [Link]("15/02/2020" ; 1) = 31/03/2020
= [Link]("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.

= [Link](A2 ; 1) = 30/06/2020
= [Link](A3+45 ; 0) => [Link]("02/07/2020" ; 0) = 31/07/2020
= [Link](A4 ; 1)+10 => 30/06/2020+10 = 10/07/2020
= [Link](A5 ; 1)+15 => 30/06/2020+15 = 15/07/2020

[Link] 81 Sommaire
Télécharger l’ebook complet

[Link] 82 Sommaire
Télécharger l’ebook complet

[Link] 83 Sommaire
Télécharger l’ebook complet

[Link] 84 Sommaire
Principe : La formule [Link] 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.

= [Link](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).

= [Link](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.
[Link] 85 Sommaire
Télécharger l’ebook complet

[Link] 86 Sommaire
Formules mathématiques :

Télécharger l’ebook complet

[Link] 87 Sommaire
Télécharger l’ebook complet

[Link] 88 Sommaire
Télécharger l’ebook complet

[Link] 89 Sommaire
Télécharger l’ebook complet

[Link] 90 Sommaire
Télécharger l’ebook complet

[Link] 91 Sommaire
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
[Link] 92 Sommaire
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

[Link] 93 Sommaire
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.

[Link] 94 Sommaire
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)

[Link] 95 Sommaire
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 :

[Link] 96 Sommaire
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 €

[Link] 97 Sommaire
Télécharger l’ebook complet

[Link] 98 Sommaire
Télécharger l’ebook complet

[Link] 99 Sommaire
Télécharger l’ebook complet

[Link] 100 Sommaire

Télécharger l’ebook complet

[Link] 101 Sommaire

 / Microsoft 365

Télécharger l’ebook complet

[Link] 102 Sommaire

Télécharger l’ebook complet

[Link] 103 Sommaire

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

[Link] 104 Sommaire

Principe : La formule [Link] permet d’arrondir un nombre à sa
valeur inférieure en fonction du nombre de chiffres souhaités.

= [Link](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 => [Link](A1 ; 0) = 12
12,147 => [Link](A1 ; 1) = 12,1
12,147 => [Link](A1 ; 2) = 12,14
1147,55 => [Link](A1 ; -1) = 1 140
1147,55 => [Link](A1 ; -2) = 1 100
1147,55 => [Link](A1 ; -3) = 1 000

[Link] 105 Sommaire

Principe : La formule [Link] permet d’arrondir un nombre à sa
valeur supérieure en fonction du nombre de chiffres souhaités.

= [Link](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 => [Link](A1 ; 0) = 13
12,147 => [Link](A1 ; 1) = 12,2
12,147 => [Link](A1 ; 2) = 12,15
1147,55 => [Link](A1 ; -1) = 1 150
1147,55 => [Link](A1 ; -2) = 1 200
1147,55 => [Link](A1 ; -3) = 2 000

[Link] 106 Sommaire

Télécharger l’ebook complet

[Link] 107 Sommaire

Formules statistiques :

Télécharger l’ebook complet

Télécharger l’ebook complet

Télécharger l’ebook complet

[Link] 108 Sommaire

Télécharger l’ebook complet

[Link] 109 Sommaire

Télécharger l’ebook complet

[Link] 110 Sommaire

Télécharger l’ebook complet

[Link] 111 Sommaire

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.

[Link] 112 Sommaire

Télécharger l’ebook complet

[Link] 113 Sommaire

Télécharger l’ebook complet

[Link] 114 Sommaire

Télécharger l’ebook complet

[Link] 115 Sommaire

Télécharger l’ebook complet

[Link] 116 Sommaire

Télécharger l’ebook complet

[Link] 117 Sommaire

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.

[Link] 118 Sommaire

=> 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.

[Link] 119 Sommaire

Télécharger l’ebook complet

[Link] 120 Sommaire

Télécharger l’ebook complet

[Link] 121 Sommaire

Télécharger l’ebook complet

[Link] 122 Sommaire

Télécharger l’ebook complet

[Link] 123 Sommaire

Principe : La formule [Link] permet de renvoyer la k-ième plus
grande valeur d’une série de données.

= [Link](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

connaitre les 3 notes les plus élevées.

Pour la meilleure note :

= [Link](B2:B11 ; 1) = 20
Pour la 2ème note la plus élevée :
= [Link](B2:B11 ; 2) = 18
Pour la 3ème note la plus élevée :
= [Link](B2:B11 ; 3) = 15
[Link] 124 Sommaire
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([Link]((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([Link](A2:A11=G3)*(B2:B11=H3)*(E2:E11) ;
{1;2;3})) = 1 200 €
[Link] 125 Sommaire
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

connaitre la note la moins élevée.

= MIN(B2:B11) = 7
=> Le résultat renvoyé est le même qu’avec la formule [Link]
pour k=1.

[Link] 126 Sommaire

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

connaitre la note la plus élevée.

= MAX(B2:B11) = 20
=> Le résultat renvoyé est le même qu’avec la formule [Link]
pour k=1.

[Link] 127 Sommaire

 / Microsoft 365

Télécharger l’ebook complet

[Link] 128 Sommaire

 / Microsoft 365

Télécharger l’ebook complet

[Link] 129 Sommaire

Formules financières :

Télécharger l’ebook complet

[Link] 130 Sommaire

Télécharger l’ebook complet

[Link] 131 Sommaire

Formules de localisation :

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 apparait qui est renvoyée.

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.

[Link] 132 Sommaire

Télécharger l’ebook complet

[Link] 133 Sommaire

Télécharger l’ebook complet

[Link] 134 Sommaire

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.

[Link] 135 Sommaire

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.
[Link] 136 Sommaire
=>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.

[Link] 137 Sommaire

=> 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 :

[Link] 138 Sommaire

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.

[Link] 139 Sommaire

Télécharger l’ebook complet

[Link] 140 Sommaire

Télécharger l’ebook complet

[Link] 141 Sommaire

Télécharger l’ebook complet

[Link] 142 Sommaire

Formules d’information :

Télécharger l’ebook complet

Télécharger l’ebook complet

[Link] 143 Sommaire

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.
[Link] 144 Sommaire