Les Fonction en Excel

I. Généralités

Les symboles opérateurs:
Pour commencer, voici la liste des symboles opérateurs que vous pouvez utiliser lors de l'écriture des formules:

Addition + Soustraction - Multiplication * Division / Exposant ^ Pourcentage % Egal = Différent <> Supérieur > Supérieur ou égal >= Inférieur < Inférieur ou égal <=

La langue d'écriture des formules doit toujours être identique à la version d'Excel installée sur le PC.


Nommer les formules:
Cette méthode a de nombreux avantages:
Raccourcit la longueur des formules dans la barre de formules. Il est possible de nommer plusieurs portions de formules (La limite de chaque nom est de 256 caractères).
Nommer les formules permet d'accélerer sensiblement le temps de calcul (jusqu'à 20%).
Les formules sont masquées dans la feuille de calcul.
les formules matricielles nommées ne sont plus à valider par Ctrl, Maj et Entrée.

Pour attribuer un nom, tout d'abord, rédigez votre formule normalement dans la cellule.
Sélectionnez la fonction dans la barre de formules.
Faites un Copier de la formule(Ctrl+C).
Revalidez ta formule (Touche Entrée)
Ensuite, Menu Insertion/Nom/Définir.
Dans le champ "Noms dans le classeur": Saisissez le nom que vous souhaitez donner à la formule (par exemple EssaiNom)
Dans le champ "Fait référence à": Effacez la référence qui s'y trouve et faites un collage de la fonction (Ctrl+V).
Cliquez sur le bouton OK pour valider.
Effacez la formule contenue dans la cellule et remplacez la par: =EssaiNom


Valider une formule matricielle:
Cliquez dans la barre de formule et appuyez simultanément sur les touches Ctrl + Maj + Entrée.
Ensuite, dans la barre de formule, apparait une accolade à chaque extrémité de la formule: {=MaFormule}
Nota:
Les validations ne sont plus nécéssaires si vous nommez les formules


Les références relatives:
Une référence relative (qui s'affiche sous la forme =A1+A2) est basée sur la position relative de la cellule qui contient la formule et les références de cellules spécifiées dans la formule. Si la position de la cellule qui contient la formule change, la référence est modifiée. Si vous copiez la formule dans d'autres lignes ou colonnes, la référence est automatiquement adaptée en conséquence.
Par exemple, si vous copiez une référence relative =A1+A2 contenue dans la cellule B2 vers la cellule B3, la formule est automatiquement transformée en =A2+A3.


Les références absolues (Utilisation du symbole $):
Une référence de cellule absolue (qui s'affiche sous la forme =$A$1+$A$2) spécifie des cellules se trouvant à un endroit fixe. Si la position de la cellule qui contient la formule change, la référence absolue reste inchangée.
Par exemple, si vous copiez une référence absolue =$A$1+$A$2 de la cellule B2 vers la cellule B3, la formule reste la même dans les deux cellules.
Les formules utilisent des références relatives par défaut. Il faut donc les transformer en références absolues manuellement en ajoutant les symboles $.


Les références mixtes:
Une référence mixte comprend soit une colonne absolue et une ligne relative ($A1,$B1,...), soit une ligne absolue et une colonne relative (A$1,B$1,...).
Par exemple, si vous copiez une référence mixte =A$1*2 de la cellule A2 vers la cellule B3, la formule est transformée en =B$1*2.


Les références 3D:
Si vous voulez analyser des données de la même cellule ou de la même plage de cellules sur plusieurs feuilles de calcul, utilisez une référence 3D. Celle-ci inclut la référence de cellule ou de plage de cellules précédée d'une plage de noms de feuilles de calcul. Excel utilise alors toutes les feuilles de calcul comprises entre le premier et le dernier noms de la référence.
Par exemple, =SOMME(Feuil2:Feuil13!B5) additionne toutes les valeurs contenues dans la cellule B5 de l'ensemble des feuilles de calcul situées entre la Feuil2 et la Feuil13 incluses.

Pour effectuer le total des cellules de toutes tes feuilles, vous pouvez aussi saisir:
=SOMME('*'!B5)
L'étoile va se transformer automatiquement en prenant toutes les feuilles en compte (hormis celle qui contient la formule).


Les opérateurs conditionnels dans les fonctions SOMMEPROD:

* : ET + : OU - : SAUF Les opérateurs <, >, <>, <=, >= , = renvoient des valeurs logiques VRAI ou FAUX

Ensuite, lorsque que le résultat logique répond à la requête, la somme est établie sur le principe ci dessous:

VRAI * VRAI = 1 VRAI * FAUX = 0 VRAI * 1 = 1 VRAI * 0 = 0

II. Texte

'Compter le nombre de caractères contenus dans la cellule A1(Chaque espace compte pour un caractère) =NBCAR(A1)

'Mettre en majuscule la première lettre de chaque mot =NOMPROPRE(A1)

'Convertir en majuscule le texte contenu dans la cellule A1 =MAJUSCULE(A1)

'Convertir en minuscule le texte contenu dans la cellule A1 =MINUSCULE(A1)

'La fonction CAR renvoie le caractère ANSI spécifié (dans l'exemple ci dessous, le résultat = 0) =CAR(48) 'D'autres exemples de résultats: CAR(48)=0 ,CAR(57)=9 ,CAR(65)=A ,CAR(90)=Z ,CAR(97)=a ,CAR(122)=z

'Renvoyer le numéro de code d'une lettre: (dans l'exemple ci dessous, le résultat = 65 ) =CODE("A") 'D'autres valeurs: CODE(0) renvoie 48 , CODE("z") renvoie 122

'Supprimer tous les espaces à l'exception des espaces simples entre les mots. =SUPPRESPACE(A1)

'Contrôler si le texte dans la cellule A1 contient le texte "dvp" (1=oui ,0=non) =NB.SI(A1;"*dvp*")

'Remplacer des données dans une chaine de caractères (exemple: Remplacer "1999" par "2005") =SUBSTITUE("test 1999 et essai 1999"; "1999"; "2005") 'Ne remplacer que la deuxième occurence correspondant aux caractères cible =SUBSTITUE("test 1999 et essai 1999"; "1999"; "2005";2)

'Extraire le premier mot d'un texte saisi dans la cellule A1 =GAUCHE(A1;CHERCHE(" ";A1;1)-1)

'Afficher une lettre aléatoire entre A et Z =CAR(ENT(ALEA()*26+1)+64) 'Une autre solution pour afficher une lettre aléatoire minuscule ou majuscule =CAR(ENT(ALEA()*26+1)+CHOISIR(ARRONDI(ALEA();0)+1;64;96))

'Extraire le dernier mot d'un texte saisi dans la cellule A1: {=DROITE(A1;EQUIV(" ";STXT(A1;NBCAR(A1) - LIGNE(INDIRECT("1:" & NBCAR(A1)));1);0))}

'Afficher le plus grand nombre de caractères contenu dans une cellule de la plage A1:A10 {=MAX(NBCAR(A1:A10))}

'Vérifier si une cellule contient du texte =SI(ESTTEXTE(A1);"OUI";"NON")

'Compter le nombre de "a" dans la cellule A1 =NBCAR(A1)-NBCAR(SUBSTITUE(A1;"a";"")) 'Un autre exemple qui compte le nombre de "mimi" dans la cellule A1 =(NBCAR(A1)-NBCAR(SUBSTITUE(A1;"mimi";"")))/4

'Compter le nombre de mots (séparés par un espace) dans la cellule A1 =NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";"")) + 1

'Compter le nombre de cellules qui contient du texte dans la plage A1:A10 =SOMMEPROD(ESTTEXTE(A1:A10)*1)

'Compter le nombre de cellules qui contient le texte DVP, uniquement quand saisi en majuscules =SOMMEPROD((EXACT(A1:A10;"DVP")*1))

'Compter le nombre de cellules en majuscules dans la plage A1:A10 =SOMMEPROD((A1:A10<>"")*(EXACT(A1:A10;MAJUSCULE(A1:A10))))

'Vérifier si les données sont en majuscule, minuscule ,ou les deux ,dans la cellule A1 =SI(EXACT(A1;MAJUSCULE(A1));"MAJUSCULE";SI(EXACT(A1;MINUSCULE(A1));"MINUSCULE";"MAJUSCULE ET MINUSCULE"))

'Concaténer sur 2 lignes, dans une meme cellule. 'Le renvoi à la ligne automatique doit être activé dans la cellule contenant la formule. =A1&CAR(10)&A2

'Inverser l'ordre des valeurs de la cellule A1 (Uniquement pour des valeurs numériques). 'A valider avec CTRL + MAJ + ENTREE =SOMME(STXT(A1;NBCAR(A1)-LIGNE(INDIRECT("1:" & NBCAR(A1)))+1;1)*10^(NBCAR(A1)-LIGNE(INDIRECT("1:" & NBCAR(A1)))))

'Créer une suite logique de lettres (de A à Z) 'Tout d'abord saisissez la formule dans la cellule A1 puis utilisez la poignée de recopie vers le bas. =CAR(64+LIGNE()) 'Pour obtenir une suite de lettres minuscules vous pouvez utiliser: =MINUSCULE(CAR(64+LIGNE()))

'Convertir en nombre une chaîne de caractères représentant un nombre. =CNUM(A1)

'Extraire une partie des données saisies dans la cellule A1. 'L'exemple effectue une extraction du 2eme au 5eme caractère. =STXT(A1;2;4)

Transformer une valeur (qui est au format texte) en format numérique: =A1*1

'Additionner une plage de cellules Contenant des Espaces Redondants. 'Formule à valider par Ctrl+Maj+Entree. L'exemple ne fonctionne pas s'il y a des cellules vides. =SOMME(CNUM(SUBSTITUE(A1:A10;" ";"")))

Inverser la position du Nom et du prénom. 'il ne doit y avoir qu'un espace dans la chaîne de caractères pour que cela fonctionne. =DROITE(A1;NBCAR(A1)-CHERCHE(" ";A1))&" "&GAUCHE(A1;CHERCHE(" ";A1))

Transformer une chaine "Prénom NOM" en "NOM P." Nota: Pour fonctionner les prénoms composés doivent être séparés par des tirets. Si la chaine de caratères est en A1, vous pouvez saisir en B1: =DROITE(A1;NBCAR(A1)-CHERCHE(" ";A1))&" "&GAUCHE(A1;CHERCHE(" ";A1)) Ensuite dans la cellule C1 vous insèrez cette formule matricielle (à valider par Ctrl+Maj+Entree): =GAUCHE(B1;NBCAR(B1)-NBCAR(DROITE(B1;EQUIV(" ";STXT(B1;NBCAR(B1)-LIGNE(INDIRECT("1:"&NBCAR(B1)));1);0)))+1)&"." "Mimi EXEMPLENOM" renvoie "EXEMPLENOM M." "Jean-Mimi DE LA EXEMPLENOM" renvoie "DE LA EXEMPLENOM J."

'Comment retrouver la position du caractère astérisque (*) dans un chaîne. 'Si vous utilisez la fonction CHERCHE il faut mettre un tilde devant l'astérique: ~* =CHERCHE("~*";A1) 'Ou utilisez directement la fonction TROUVE: =TROUVE("*";A1)

'Forcer l'écriture d'une lettre et 4 chiffres dans la cellule A1 'Sélectionnez la cellule A1 'Utilisez le menu Données 'Validation 'Onglet "Options" 'Sélectionnez l'option "personnalisé" dans le champ "Autoriser:" 'Dans le champ "Formule", saisissez: =ET(NON(ESTNUM(GAUCHE(A1)*1));NON(ESTNUM(STXT(A1;1;1)*1));ESTNUM(DROITE(A1;4)*1)) 'Cliquez sur le bouton OK pour valider.

III. Statistiques

'Compter le nombre de données numériques dans la colonne A =NB(A:A)

'Compter le nombre de cellules non vides dans la colonne A =NBVAL(A:A)

'Compter les cellules non vides dans une plage qui contient des formules renvoyant "" =SOMMEPROD((A1:A10<>"")*1)

'Compter le nombre de cellules contenant la valeur "dvp", dans la colonne A =NB.SI(A:A;"dvp")

'Compter le nombre de cellules vides dans la plage A1:A10 =NB.VIDE(A1:A10) 'Remarque issue de l'aide en ligne Excel: 'Les cellules contenant des formules qui renvoient " " (texte vide) sont également comptées, 'ce qui n'est pas le cas des cellules contenant la valeur zéro.

'Afficher la 2eme valeur la plus élevée de la plage A1:A10 =GRANDE.VALEUR(A1:A10;2)

'Afficher la plus petite valeur de la plage A1:A10, sans tenir compte des 0 =PETITE.VALEUR(A1:A10;NB.SI(A1:A10;0)+1) 'Une autre solution (valider par Ctrl+Maj+Entree) =MIN(SI(A1:A10=0;"";A1:A10))

'Afficher la valeur mini d'une plage sans prendre en compte les 0 et les champs vides =MIN(SI(A1:A10>0;A1:A10;"")) 'Formule matricielle à valider par Ctrl+Maj+Entrée

'Afficher la plus petite valeur immédiatement supérieure à 0 =PETITE.VALEUR(A1:D1;NB.SI(A1:D1;"<="&0)+1) =MIN(SI(A1:D1>0;A1:D1)) 'La 2ème fonction est une matricielle, à valider par Ctrl+Maj+Entrée.

'Afficher la plus grande valeur, inférieure à 15, dans la plage A1:A5 =MAX(SI($A$1:$A$5<15;$A$1:$A$5)) 'Formule matricielle à valider par Ctrl+Shift+Entrée

'Renvoie la somme des 6 plus grandes valeurs d'une plage. 'Les doublons éventuels de la 6eme valeur sont comptabilisés. =SOMME(A1:A10*(A1:A10>=GRANDE.VALEUR(A1:A10;6))) 'Formule matricielle à valider par Ctrl+Maj+Entree 'Une autre possibilité (qui ne prend pas en compte les doublons de la 6eme valeur) =SOMME(GRANDE.VALEUR(A1:A10;{1.2.3.4.5.6}))

'Afficher la moyenne des 5 plus petites valeurs de la plage A1:A100 =SI(NBVAL(A1:A100)>=5;MOYENNE(PETITE.VALEUR(A1:A100;{1.2.3.4.5}));MOYENNE(A1:A100))

'Afficher le classement de toutes les cellules de la plage A1:A10, les unes par rapport aux autres. =RANG(A1;$A$1:$A$10;1) 'Saisissez cette formule dans la cellule B1 et ensuite faites un "Glisser", jusqu'à la cellule B10

'Indiquer de façon visuelle le rang d'un nombre dans une série. 'La fonction RANG permet de déterminer la position d'un nombre dans une liste d'arguments, 'mais vous pouvez aussi y associer les fonctions REPT et CAR afin de donner un aspect visuel au résultat. 'Si par exemple la série de nombre est en A1:A10 'En B1, vous saisissez: =REPT(CAR(7);RANG(A1;$A$1:$A$10;1)) 'Puis vous étirez la formule vers le bas, jusqu'en B10.

'Afficher la valeur numérique qui apparait le plus souvent dans la plage A1:A10 =MODE(A1:A10)

'Afficher la donnée (texte ou numérique) qui apparait le plus souvent dans la plage A1:A10 {=INDEX(A1:A10;EQUIV(MAX(NB.SI(A1:A10;A1:A10));NB.SI(A1:A10;A1:A10);0))}

'Afficher la donnée qui apparaît le plus souvent et au moins 3 fois dans la plage A1:A10 =SI(NB.SI(A1:A10;MODE(A1:A10))>2;MODE(A1:A10);"")

'Compter le nombre de valeurs identiques entre deux plages =SOMMEPROD(NB.SI(A1:A10;B1:B10))

'Compter le nombre de valeurs identiques (unique) entre deux plages =SOMMEPROD((FREQUENCE(A1:A10;B1:B10)>0)*1)-1

'Compter le nombre d'éléments qui répond à plusieurs critères sur différentes colonnes =SOMMEPROD((A1:A10 ="Valeur1")*(B1:B10="Valeur2")*(C1:C10="Valeur3"))

'Compter le nombre de données différentes dans la plage A1:A10 =SOMMEPROD(1/NB.SI(A1:A10;A1:A10)) 'Une autres solution s'il y a des cellules vides dans la plage: =SOMMEPROD(SI(A1:A10<>"";1/NB.SI(A1:A10;A1:A10)))

'Compter le nombre de valeurs numériques différentes dans la plage A1:A10 'Remarque: La plage A1:A10 peut contenir du texte et des cellules vides = SOMME(SI(FREQUENCE(A1:A10;A1:A10)>0;1)) 'Une autre solution =SOMME(N(FREQUENCE(A1:A10;A1:A10)>0)) 'Une autre possiblité à valider par Ctrl+Maj+Entree =SOMME(SI(ESTNUM(A1:A10);1/NB.SI(A1:A10;A1:A10))) 'Un autre exemple qui ne prend pas en compte les 0 (à valider par Ctrl+Maj+Entree) =SOMME(SI(A1:A10>0;1/NB.SI(A1:A10;A1:A10)))

'Compter le nombre de valeurs comprises entre 50 et 60 dans la plage A1:A10. =SOMMEPROD((A1:A10 >=50)*(A1:A10 <=60)) 'Une autre solution =NB.SI(A1:A10;">="&50)-NB.SI(A1:A10;">="&60)

'Renvoie "oui" si le contenu de la cellule B1 apparaît dans la plage A1:A10 =SI(NB.SI(A1:A10;B1);"oui";"non")

'Compter le nombre de valeurs comprises entre 10 et 20 dans la plage A1:A10 (bornes non compries) =FREQUENCE(A1:A10; {20;10} )

'Compter le nombre de lignes dont la date correspond au mois de Décembre 'A1:A10 est la plage contenant les dates au format jj/mm/aaaa '12 est l'index du mois de Décembre =SOMMEPROD((MOIS(A1:A10)=12)*1)

'Compter le nombre de valeurs Impaires dans la plage de cellules B1:B20 =SOMMEPROD((MOD(B1:B20;2)=1)*1) =SOMMEPROD(MOD(B1:B20;2)*1) =SOMMEPROD((B1:B20=IMPAIR(B1:B20))*1)

'Retrouver les données uniques et les doublons dans une plage de cellules 'Les données à controler sont dans la plage A1:A20 'Saisissez la formule ci-dessous dans la cellule B1 =SI(NB.SI($A$1:$A$20;A1)>1;"Multiple";"Unique") 'Puis étirez la formule jusqu'en B20

IV. Math et Trigo

'Vérifier si la valeur de la cellule est paire(la formule renvoie 1) ou impaire(la formule renvoie 0) =(A1=PAIR(A1))*1

'Savoir si la valeur de la cellule est paire(la formule renvoie Vrai) 'ou impaire(la formule renvoie Faux) =(A1=PAIR(A1))

'SOUS.TOTAL Renvoie le sous-total d'une plage de cellules (somme de la plage A2:A5 dans l'exemple) =SOUS.TOTAL(9;A2:A5) Un Sous Total ne prend en compte que sur les données visibles résultant du filtrage d'une liste. Des sous-totaux déjà existants à l'intérieur de la plage ne sont pas pris en compte. Les codes de formules associées: 1 Moyenne 2 NB 3 NBVAL 4 MAX 5 MIN 6 PRODUIT 7 ECARTYPE 8 ECARTYPEP 9 SOMME 10 VAR 11 VAR.P 'Un Sous Total peut être affecté à plusieurs plages de cellules '(moyenne des plages A2:A5 et C2:C5 dans cet exemple) =SOUS.TOTAL(1;A2:A5;C2:C5)

'Compter le nombre de X dans la plage A2:A10, si la valeur associée dans la colonne B est inférieure à 50 =SOMMEPROD((A2:A10="X")*(B2:B10<50))

'Additionner les valeurs de la colonne B si la colonne A contient le mot "dvp" =SOMME.SI(A:A;"dvp";B:B)

'Additionner les valeurs d'une plage (A1:A5), dont certaines cellules contient des erreurs. {=SOMME(SI(ESTERREUR(A1:A5);"";A1:A5))}

'Renvoyer un nombre aléatoire entre 1 et 3 =ENT(ALEA()*3+1)

'Insérer un exposant dans une formule (exemple exposant 2) =A1^2 'Remarque: la fonction puissance permet d'obtenir le même résultat =PUISSANCE(A1;2)

Extraire la racine carré d'un nombre =A1^(1/2)

Extraire la racine cubique d'un nombre =A1^(1/3)

Arrondir la valeur de la cellule A1 à l'entier immédiatement inférieur =ENT(A1)

'La différence entre les fonctions TRONQUE et ENT: 'Les deux fonctions renvoient des nombres entiers. 'Les fonctions ENT et TRONQUE diffèrent uniquement lorsqu'il s'agit de nombres négatifs. 'TRONQUE supprime la partie décimale d'un nombre (l'exemple ci-dessous renvoie -10) =TRONQUE(-10,5;0) 'ENT arrondit les nombres à l'entier immédiatement inférieur (l'exemple ci-dessous renvoie -11) =ENT(-10,5)

'Arrondir la valeur de la cellue A1 à 2 chiffre apres la virgule =ARRONDI(A1;2)

'Arrondit A1 à la valeur entière immédiatement supérieure =ARRONDI.SUP(A1;0)

'Arrondit A1 à la valeur supérieure comportant trois décimales =ARRONDI.SUP(A1;3)

'Arrondit A1 à gauche du séparateur décimal, à la centaine immédiatement supérieure. =ARRONDI.SUP(A1;-2)

'Arrondit A1 à la valeur entière immédiatement supérieure =ARRONDI.INF(A1;0)

'Arrondit A1 à la valeur inférieure comportant trois décimales =ARRONDI.INF(A1;3)

'Arrondit A1 à gauche du séparateur décimal, à la centaine immédiatement inférieure =ARRONDI.INF(A1;-2)

'Arrondir la valeur de la cellule A1 au multiple de 5 le plus proche (par exemple 17,6 renvoie 20) =ARRONDI.AU.MULTIPLE(A1;5)

'Arrondir aux 5 centièmes inférieurs =A1-MOD(A1;0,05) =A1-MOD(A1;5%)

'Calculer des montants de traites identiques en valeurs entières. 'La totalité des décimales est appliquée sur la dernière traite 'La somme totale est en A1 , le nombre de traites est en B1 'Le montant de chaque traite (hormis la dernière) =ARRONDI($A$1/$B$1;0) 'Le montant de la dernière traite =A1-(ARRONDI(A1/B1;0)*(B1-1))

'Arrondir à la dizaine la plus proche (213,05 renvoie 210) (216,001 renvoie 220) =ARRONDI(A1/10;0)*10

'Récuprérer la partie décimale de la cellule A1 (ne fonctionne que pour les données positives) =A1-ENT(A1)

'Additionner toutes les valeurs négatives de la plage A1:A10 {=SOMME(A1:A10*(A1:A10<0))}

'Compter le nombre de fois ou "dvp" (dans la plage A1:A10) est classé "number one" 'dans la plage B1:B10 =SOMMEPROD((A1:A10="dvp")*(B1:B10="number one"))

'Calculer une moyenne sans tenir compte des zéros =SOMME(A1:A10)/NB.SI(A1:A10;">0")

'Calculer la moyenne de la plage A1:A10, 'à condition que les données correspondantes de la plage B1:B10 contiennent le mot "oui". =SOMMEPROD((B1:B10)*(A1:A10="oui"))/NB.SI(A1:A10;"oui")

'Afficher le résultat de la formule (A1+A2), uniquement quand les deux cellules sont remplies. =SI(ET(A1<>"";A2<>"");A1+A2;"")

'Afficher le résultat de la formule (A1/B1), uniquement si le résultat ne renvoie pas une erreur. =SI(ESTERREUR(A1/B1);"";A1/B1)

'Additionner les cellules A1 pour les Feuil1 à Feuil4 =SOMME(Feuil1:Feuil4!A1)

'Additionner les cellules de la plage C1:C10, 'si la cellule associée dans la plage A1:A10 est égal à "dvp" et 'si la cellule dans la plage B1:B10 est égal à 50. =SOMMEPROD((A1:A10="dvp")*(B1:B10=50)*(C1:C10)) 'Les plages de cellules pour chaque matrice doivent etre identiques. 'Le même exemple que ci-dessus adapté au cas ou il y aurait des valeurs numériques 'au format texte dans les plages B1:B10 et C1:C10. =SOMMEPROD((A1:A10="xld")*(B1:B10*1=50);C1:C10*1)

'Additionner les cellules de la plage A1:A10, alors que chaque premier caractère 'commençe par une lettre. {=SOMME(DROITE(A1:A10;(NBCAR(A1:A10)-1))*1)}

'Additionner les cellules de la plage B1:B10 si les phrases contenues dans 'la plage A1:A10 contiennent le mot "mimi". =SOMMEPROD(ESTNUM(CHERCHE("mimi";A1:A10))*B1:B10)

'Afficher le sinus d'une valeur définie en degrés. 'Dans le fonction SIN(nombre), nombre représente l'angle exprimé en radians. 'il faut dont multiplier la valeur par PI()/180 ou utiliser la 'fonction RADIANS pour la convertir en radians =SIN(RADIANS(30)) =SIN(30*PI()/180)

'Afficher la Valeur absolue d'un nombre (nombre sans son signe) =ABS(-10)

'Additionner les valeurs absolues de la plage A1:A5: (sans tenir compte du signe des nombres) =SOMMEPROD(ABS(A1:A5))

'Retrouver le plus grand diviseur commun d'une plage de cellules =PGCD(A1:A5)

'Retrouver le plus petit multiple commun d'une plage de cellules =PPCM(A1:A5)

'Renvoie une valeur arrondie au nombre entier IMPAIR le plus proche, en s'éloignant de zéro. =IMPAIR(A1)

'Renvoie une valeur arrondie au nombre entier PAIR le plus proche, en s'éloignant de zéro. =PAIR(A1)

'Additionner toutes les valeurs comprises entre 10 et 50 (bornes comprises) =SOMME.SI($A1:$A100;">="&10)-SOMME.SI($A1:$A100;">"&50)

'Additionner les données de la plage A1:A10, en plafonnant le résultat maxi à 800 =MIN(800;SOMME(A1:A10))

'Insérer dans une colonne, une suite de nombre croissants qui évolue toutes les 11 lignes. 'Placez la formule en A1 puis utilisez la poignée de recopie vers le bas. =ARRONDI.SUP(LIGNE(A1)/11;0) =ENT((LIGNE()-1)/11)+1

'Additionner les cellules de la plage B1:B10, si les cellules en A1:A10 sont vides =SOMMEPROD(ESTVIDE(A1:A10)*(B1:B10))

'Vérifier si la valeur de la cellule A1 est un multiple de 5 =SI(MOD(A1;5)=0;"VRAI";"FAUX") 'Une autre possibilité qui renvoie 1 s'il s'agit d'un multiple, et 0 dans le cas contraire =(MOD(A1;5)=0)*1

'Retrouver un angle (en degrés) à partir de la valeur de la tangeante '(exemple tangeante=1 renvoie 45 degrés) =ATAN(1)*180/PI()

'Additionner des données qui sont au format alpha numérique (la valeur plus l'unité). 'Par exemple: 15 kilos, 20 kilos ... 'Les données à additionner sont dans la plage A1:A10. 'Saisissez l'unité en B1 (kilos), puis utilisez la formule suivante dans une autre cellule: =SOMME(SI(ESTNUM(TROUVE($B$1;$A$1:$A$10));CNUM(GAUCHE($A$1:$A$10;TROUVE($B$1;$A$1:$A$10)-1));0)) 'A valider par Ctrl+Maj+Entrée

'Faire la somme des divisions entre les plages A1:A100 et B1:B100 '(l'équivalent de (A1/B1)+(A2/B2)+(A3/B3) ... 'Cet exemple prend en compte la possibilité que la colonne B contienne des 0 (division par 0) ou des celules vides. =SOMME(SI((ESTNUM(A1:A100/B1:B100));A1:A100/B1:B100)) 'Formule en matricielle à valider par Ctrl+Maj+Entrée

'Comment Additionner des cellules en fonction de la couleur de fond, sans macro ? 'Par exemple, additionner les cellules dont la couleur de fond et jaune, dans la plage A1:A10 'Sélectionnez la plage B1:B10 'Menu Insertion / Nom / Définir 'Dans le champ "Nom dans le classeur", indiquez le mot "Test" 'Dans le champ "Fait référence à" saisissez la formule: =LIRE.CELLULE(63; Feuil1!A1) 'Cliquez sur le bouton Ajouter , puis sur le bouton OK pour valider. 'Il s'agit d'une utilisation détournée des anciennes fonctions XL4. '63 Renvoie le couleur de l'arrière-plan de la cellule. 'Sélectionnez la cellule B1 'Saisissez la formule. =Test 'Validez la formule. 'Utilisez les poignées de recopie pour étirer la formule vers le bas. 'Ensuite dans la cellule C1, vous pouvez utiliser une formule qui compte le nombre de fois qu'apparaît un code couleur: '(6= couleur jaune) 'Par exemple =NB.SI(B1:B10;6) 'Le seul inconvénient, il faut revalider la formule =Test lorsque vous modifiez la couleur d'une cellule car 'le recalcul automatique et le lancement du recalcul (F9) ne fonctionneront pas. 'Pour effectuer la mise à jour du résultat, le plus rapide consiste à ressaisir la formule =Test en B1, 'et utiliser les poignées de recopie vers le bas.

V. Scientifiques

'Convertir une valeur décimale en binaire (macro complémentaire de l'utilitaire d'analyse) =DECBIN(A1)

'Tester l'égalité de deux nombres 'Renvoie 1 si les arguments sont égaux, sinon renvoie 0. =DELTA(A1;B1)

'Renvoyer le nombre de combinaisons possibles, en fonction de l'argument choisi. 'Exemple: Nombre de combinaisons pour former des groupes de 3 pièces sur un ensemble total de 10 pièces. =COMBIN(10;3)

'Convertir un nombre d'une unité vers une autre. 'La synthaxe: CONVERT(nombre;de_unité;à_unité) 'Par exemple convertir 68 degrés Fahrenheit en degrés Celsius (20) =CONVERT(68; "F"; "C") 'Consultez l'aide en ligne Excel pour afficher les différentes unités de mesures utilisables. 'La macro complémentaire Utilitaire d'analyse doit être activée: 'Menu Outils 'Macros complémentaires. 'Cochez la case "Utilitaire d'analyse" 'Cliquez sur OK.

VI. Les Dates et les Heures

'Généralité sur les dates Pour Excel, 1 = 1 jour = 24 heures

'Renvoie le jour de la semaine pour une date spécifiée dans la cellule A1: Dimanche=1, Samedi=7 =JOURSEM(A1) 'Renvoie le jour de la semaine pour une date spécifiée dans la cellule A1: Lundi=1, Dimanche=7 =JOURSEM(A1;2) 'Renvoie le jour de la semaine pour une date spécifiiée dans la cellule A1: Lundi=0, Dimanche=6 =JOURSEM(A1;3)

'Afficher le numéro du jour (pour la date d'aujourd'hui) =AUJOURDHUI()-DATE(ANNEE(AUJOURDHUI());1;0)

'Effectuer la somme des valeurs de la plage B1:B10, 'quand la date (plage A1:A10) est comprise entre le 01.01.04 et le 31.12.04 {=SOMME((B1:B10)*(A1:A10>=DATEVAL("01/01/04"))*(A1:A10<=DATEVAL("31/12/04")))}

'Soustraire des heures qui sont au format "20h15" =CNUM(SUBSTITUE(A2;"h";":"))-CNUM(SUBSTITUE(A1;"h";":"))

'Afficher le nombre de jours dans un mois, pour une date définie dans la cellule A1 =JOUR(DATE(ANNEE(A1);MOIS(A1)+1;0))

'Afficher la date du jour au format texte =TEXTE(AUJOURDHUI(); "jjjj jj mmmm aaaa")

'Convertir un numéro de mois (saisi dans la cellule A1) en nom de mois =TEXTE("1/"&A1;"mmmm")

'Calculer le temps écoulé pour des heures saisies en A1(début) et A2(fin) =MOD(A2-A1;1) 'Les cellules doivent etre au format [hh]:mm

'Extraire le nombre de journées de 8 heures, 'pour un total d'heures saisi dans la cellule A1 au format [hh]:mm =ENT(A1*24/8) 'Et pour récupérer le restant d'heures =((A1*24/8)-ENT(A1*24/8))*8

'Transformer une date qui est sous la forme 1965.05.26, par 26.05.1965 =DROITE(A1;2)&"."&STXT(A1;6;2)&"."&GAUCHE(A1;4)

'Afficher Vrai si la date saisie dans la cellule A1 est un jour de Week end, sinon renvoie Faux =JOURSEM(A1;2)>5

'Calculer le temps écoulé entre 2 dates (en années, mois et jours) 'Exemple pour calculer l'age d'une personne dont la date de naissance est saisie dans la cellule A1 =DATEDIF(A1;AUJOURDHUI();"y")&" ans , "&DATEDIF(A1;AUJOURDHUI();"Ym")&" mois et "&DATEDIF(A1;AUJOURDHUI();"Md")&" jours"

'Vérifier si la date saisie dans la cellule A1 correspond au jour anniversaire (Aujourdui) =SI(ET(DATEDIF(A1;AUJOURDHUI();"ym")=0;DATEDIF(A1;AUJOURDHUI();"md")=0);"Oui";"Non")

'Contrôler si une personne, dont l'age est saisi dans la cellule A1, est majeure. =SI(DATEDIF(A1;AUJOURDHUI();"y")>=18;"Majeur";"Mineur")

'Calculer l'age en tenant compte du mois et de l'année de naissance. 'La date est saisie dans la cellule A1 =ANNEE(AUJOURDHUI()-A1)-1900&" ans "&MOIS(AUJOURDHUI()+1-A1)-1&" mois "

'Afficher la moyenne du temps passé (en mm:ss), 'avec en A1 le temps total en secondes et en A2 le nb d'occurrences. =A1/24/60/60/A2 'La cellule contenant la formule doit etre au format [mm]:ss

'Afficher le numéro de semaine pour une date saisie en A1 =ENT(MOD(ENT((A1-2)/7)+0,6;52+5/28))+1

'Convertir des secondes (saisies dans la cellule A1) en heures. 'La cellule contenant la formule doit être au format hh:mm:ss =A1/60/60/24 'Une autre solution (la cellule contenant la formule toujours au format hh:mm:ss ) =A1*"0:0:1"

'Convertir en heure une somme de minutes =SOMME(A1:A10)/1440 'Appliquez le format heure à la cellule de résultat

'Compter le nombre de dates correspondant au mois de Février(2) dans la plage de cellules A1:A10 =SOMMEPROD((MOIS(A1:A10) = 2)*1) 'Une autre solution pour compter le mois de Janvier dans une plage pouvant contenir des cellules vides. (Pour Excel, =MOIS (cellule vide) = 1 ) = SOMMEPROD((A$1:A$20<>"")*(MOIS(A$1:A$20) =1))

'Afficher le premier Lundi du mois, pour une date saisie dans la cellule A1 =A1-JOUR(A1)+9-JOURSEM(A1-JOUR(A1)) 'Pensez à adapter le format de la cellule contenant la formule

'Afficher le dernier jour du mois, pour une date saisie dans la cellule A1 =FIN.MOIS(DATE(TEXTE(A1;"aaaa");TEXTE(A1;"mm");1);0) 'Une autre solution =DATE(ANNEE(A1);MOIS(A1)+1;0)

'Afficher le dernier Lundi du mois, pour une date saisie dans la cellule A1 =DATE(ANNEE(A1);MOIS(A1)+1;2)-JOURSEM(DATE(ANNEE(A1);MOIS(A1)+1;))

'Afficher le dernier jour du mois en cours =FIN.MOIS(AUJOURDHUI();0)

'Afficher le numéro de trimestre, pour une date saisie en A1 ="TRIMESTRE "&ENT((MOIS(A1)+2)/3)

'Vérifier si une date saisie dans la cellule A1 appartient à une année bissextile =SI(MOIS(DATE(ANNEE(A1);2;29))=2;"Bissextile";"Non bissextile")

'Convertir un nombre d'heures spécifique en journées (7,4h = 1j) =(ENT(A1/7,4))/24 'Par exemple: 6,8 renvoie 0 et 7,6 renvoie 1 Appliquez le format [hh] à la cellule contenant la formule

'Multiplier des heures (au format hh:mm) par un nombre par exemple 01:30 x 2 = 3 =A1*B1*24

'Convertir des heures décimales en heures minutes =A1/24 La cellule contenant la formule doit etre au format [hh]:mm Si par exemple vous saisissez 2,5 dans la Cellule A1, la cellule contenant la formule renvoie 02:30

'Convertir des heures minutes en décimales. Par exemple 10:30 devient 10,5 =(A1-ENT(A1))*24

'Ajouter 30 minutes à une heure saisie en A1 =A1+TEMPS(0;30;0)

'Arrondir à l'heure la plus proche (2:45 devient 3:00) =ARRONDI(A1/(1/24);0)*(1/24)

'Ajouter ou ôter un nombre de mois à une date spécifée =MOIS.DECALER(A1;3) 'La date de départ est en A1. 3 correspond au nombre de mois. 'Une valeur de mois positive indique une date future, tandis qu'une valeur négative 'indique une date passée. 'Pensez à appliquer un format date à la cellule contenant la formule. 'Si vous rencontrez des problèmes avec cette fonction, utilisez EDATE. =EDATE(E1;3)

Oter 24 mois à la date du jour. =DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI())-24;JOUR(AUJOURDHUI()))

'Additionner des valeurs en fonction d'un mois cible =SOMMEPROD((MOIS(Totaux!A1:A10)=D1)*(Totaux!B1:B10)) 'Dans la feuille "Totaux", la plage A1:A10 contient les dates "jj/mm/aaaa" 'et la plage B1:B10 contient les valeurs à additionner. 'La Cellule D1 contient le numéro du mois qui va servir à filtrer les données additionnées: '1= Janvier , 2 = Février ...etc ...

'Compter le nombre de dates différentes, et qui correspondent à un Dimanche, dans la plage A1:A5 =SOMME(SI(JOURSEM(A1:A5)=1;1/NB.SI(A1:A5;A1:A5))) 'Fonction à valider par Ctrl+Maj+Entree. 'Remarque: la formule accepte des cellules vides dans la Plage A1:A5

'Compter le nombre de dates différentes, et qui correspondent à un Samedi, dans la plage A1:A5 =SOMME(SI(JOURSEM(A1:A5)=7;SI(A1:A5>0;1/NB.SI(A1:A5;A1:A5)))) 'Fonction à valider par Ctrl+Maj+Entree 'Cette formule ne compte pas les cellules vides (Une cellule vide correspond au samedi 00-01-1900)

'Additionner les heures qui correspondent à des dimanches et appliquer un 'coefficient multiplicateur (x2) au résultat. Les jours sont en A1:A10, Les heures en B1:B10 =SOMMEPROD((JOURSEM(A1:A10)=1)*B1:B10)*2

'Retrouver le mois en fonction du numéro de semaine et de l'index du jour. 'En paramètres: L'annee dans la cellule A1. Le numéro de semaine dans la cellule A2. 'L'index du jour dans la cellule A3: 'Lundi=0 , Mardi=1, Mercredi=2 ...etc... =TEXTE(DATE(A1;1;3)-JOURSEM(DATE(A1;1;3))-5+(7*A2)+A3;"mmmm")

Compter le nombre de jours ouvrés entre 2 dates, les samedi compris la date de début est saisie en A1 la date de fin en B1 =SOMMEPROD((JOURSEM(LIGNE(INDIRECT(A1&":"&B1)))>1) *ESTNA(EQUIV(LIGNE(INDIRECT(A1&":"&B1));;0))) Nota: Dans cet exemple, les jours fériés ne sont pas comptabilisés.

Compter le nombre de Lundi pour des dates saisies dans la plage de cellules G3:G20 =SOMMEPROD((JOURSEM(G3:G20)=2)*1)

'Soustraire deux dates qui sont au format jj/mm/aaaa hh:mm 'et obtenir un résultat en jj hh:mm =TEXTE(ENT($A$2-$A$1);"00")&" jour(s) "&TEXTE(MOD($A$2-$A$1;1);"hh:mm")

'Retrouver le dernier jour d'un trimestre pour une date saisie en A2 et le numéro de trimestre saisi en B2 =DATE(ANNEE(A2)+((MOIS(A2)+(B2*3))-(MOD((MOIS(A2)+(B2*3));12)))/12;((MOD((MOIS(A2)+(B2*3));12))- MOD((MOD((MOIS(A2)+(B2*3));12));3))+1;1)-1 =DATE(ANNEE(A2)+ENT(B2/4);ENT((MOIS(A2)+MOD(B2;4)*3)/3)*3+1;0)

'Afficher le dernier Vendredi du mois, pour une date saisie dans la cellule A2 =DATE(ANNEE(A2);MOIS(A2)+1;1)-JOURSEM(DATE(ANNEE(A2);MOIS(A2)+6;6)) '1=Dimanche '2=Lundi '3=Mardi '4=Mercredi '5=Jeudi '6=Vendredi '7=Samedi 'Autre solution =DATE(ANNEE(A2);MOIS(A2)+1;1)-MOD(DATE(ANNEE(A2);MOIS(A2)+1;1)+5;7)-3

VII. Recherches et matrices

'Renvoyer la dernière donnée saisie dans la colonne A '(ne fonctionne pas s'il y a des cellules vides) =INDIRECT(ADRESSE(NBVAL(A:A);1)) 'Une autre solution pour des valeurs numériques =RECHERCHE(9^9;A:A) 'Pour des valeurs alphanumériques =RECHERCHE("zzz";A:A)

'Renvoyer le numéro de la derniere ligne non vide dans la colonne A. 'Validez par Ctrl+Maj+Entree. la formule fonctionne s'il y a des cellules vides dans la plage =MAX(NON(ESTVIDE(A1:A100))*LIGNE(A1:A100))

'Renvoyer le numéro de la derniere ligne contenant une valeur numérique, dans la plage A1:A100. =MAX(ESTNUM(A1:A100)*LIGNE(A1:A100)) 'A valider par Ctrl+Maj+Entree 'La plage peut contenir des cellules vides.

'Renvoyer le numéro de la derniere ligne ne contenant pas de valeur numérique =MAX(NON(ESTNUM(A1:A20))*LIGNE(A1:A20)) 'A valider par Ctrl+Maj+Entree La plage ne doit pas contenir de cellules vides sinon celles ci seront prises en compte comme des données 'non numériques.

'Afficher la position d'une valeur recherchée (exemple: "dvp") dans la plage cible A1:A10 =EQUIV("dvp";A1:A10;0) 'Si le mot recherché est dans la cellule A3, le résultat renvoyé sera 3 'La valeur recherchée peut etre numérique ou du texte. 'La spécificité des recherches de texte: 'La fonction EQUIV n'est pas sensible aux majuscules ou minuscules. 'Si vous ne connaissez que le début du mot recherché, 'il est possible d'insérer des valeurs génériques (une astérisque ou un point d'interrogation). 'Par exemple : =EQUIV("dv*";A1:A10;0) L'astérisque est équivalent à une séquence de caractères, le point d'interrogation à un caractère unique.

'Insérer un lien hypertexte dans la cellule, pour ouvrir un autre document =LIEN_HYPERTEXTE("C:\test.xls";"Cliquez ici !") 'Si vous souhaitez créer un lien hypertexte vers un emplacement précis 'dans un document Microsoft Word, il faut utiliser un signet pour définir cet emplacement. 'L'exemple suivant crée un lien hypertexte vers le signet "Signet1" 'd'un document Word nommé monFichier.doc =LIEN_HYPERTEXTE("C:\monFichier.doc#Signet1";"Cliquez ici")

'Accéder à la cellule D10 dans la Feuil2 du même classeur =LIEN_HYPERTEXTE("[monClasseur]Feuil2!D10";"Mon lien hypertexte") '3 Remarques: 'Même si vous restez dans le meme classeur, il faut préciser son nom. 'Si le nom de la feuille contient un espace, 'le nom doit etre encadré par des apostrophes: =LIEN_HYPERTEXTE("[monClasseur.xls]'ma feuille'!D10";"Mon lien hypertexte") 'Si le nom du classeur est amené à changer dans le temps vous pouvez utiliser: =LIEN_HYPERTEXTE("["&STXT(CELLULE("nomfichier");TROUVE("[";CELLULE("nomfichier"))+1;TROUVE("]" ;CELLULE("nomfichier"))- TROUVE("["; CELLULE("nomfichier"))-1)&"]'ma feuille'!D10";"Mon lien hypertexte")

'Afficher la lettre de la colonne pour la cellule contenant cette formule. =GAUCHE(ADRESSE(1;COLONNE();4);NBCAR(ADRESSE(1;COLONNE();4))-1)

'Chercher la valeur "dvp" dans la colonne gauche de la plage A1:D10, 'et renvoyer la valeur située dans la même ligne et dans la 4eme colonne de la plage. =RECHERCHEV("dvp";A1:D10;4;0)

'Récupérer la valeur de la cellule A1 d'un classeur fermé, nommé "monFichier.xls" ='C:\Documents and Settings\[monFichier.xls]Feuil1'!A1 'Il est aussi possible de récupérer des infos dans un classeur placé sur le Web ='http://monSite/Dossier/[leClasseur.xls]Feuil1'!$A1

'Faire une RECHERCHEV dans un classeur fermé: 'Rechercher "DVP" dans la colonne A du classeur fermé et afficher la donnée de la colonne B =RECHERCHEV("DVP";'C:\[test.xls]Feuil1'!$A:$B;2;FAUX)

'Compter le nombre de caractères à droite de l'Arobas "@" =NBCAR(A1)-TROUVE("@";A1;1)

'Afficher de façon aléatoire une des données se trouvant dans la plage A1:A10 =INDEX(A1:A10;ENT(ALEA()*10+1);1)

'Afficher de façon aléatoire une des données contenue dans la formule =CHOISIR(ENT(ALEA()*6+1);"Valeur1";"Valeur2";"Valeur3";"Valeur4";"Valeur5";"Valeur6")

'Insérer une variable dans une formule. 'Dans l'exemple ci dessous, si la cellule B1=5, la formule effectuera la somme de la plage A1:A5 =SOMME(INDIRECT("A1:A"&B1))

'Récupérer une donnée sur 2 dans la colonne A 'Pour commencer la recherche dans la 1ere ligne de la colonne A: =DECALER($A$1;(LIGNE()-1)*2;0) 'Pour commencer la recherche dans 2eme ligne de la colonne A: =DECALER($A$1;LIGNE()*2-1;0) 'Dans les 2 cas, il faut étirer les formules dans la colonne de résultat

'Additionner une colonne sur 2 dans la ligne 1 'En commençant dans la premiere colonne: =SOMMEPROD(1:1*MOD(COLONNE($1:$1);2)) 'En commençant dans la deuxieme colonne: =SOMMEPROD(1:1*MOD(COLONNE($1:$1)-1;2))

'Additionner une cellule sur 10 dans la plage A1:A100 =SOMMEPROD((MOD(LIGNE(A1:A100);10)=0)*1;A1:A100)

'Renvoyer le rang de la cellule A2 dans la plage A1:A10 '(équivalent de la position dans la plage apres un tri) =RANG(A2;A1:A10;0) 'Ne fonctionne que pour des données numériques. 'Remplacez 0 par 1 pour utiliser l'ordre décroissant.

Afficher une série de données dans le sens inverse. Les données de base sont dans la plage A1:A10. Saisissez la formule dans chaque cellule de la plage B1:B10 =DECALER($A$1;10-LIGNE();0) Une autre solution =SI(A1="";"";INDIRECT("A"&NBVAL(A:A)-LIGNE()+1))

'Trouver la valeur maxi (dans la plage C2:C5) attribuée à une donnée "DVP" 'se trouvant dans la plage B2:B5. 'La donnée "DVP" peut apparaitre plusieurs fois parmi d'autres données, 'et affecté de valeurs différentes. =MAX((B2:B5="DVP")*C2:C5) 'Formule à valider par Ctrl+Shift+Entree

'Trouver la valeur la plus élevée, et qui commence par 99, dans la plage A1:A10 =MAX((GAUCHE(A1:A10;2)="99")*A1:A10) 'valider par Ctrl+Shift+Entree (source excelTip)

'Afficher la dernière donnée texte dans la plage de cellules A1:A10 =RECHERCHE(2;1/NON((A1:A10="")+ESTNUM(A1:A10));A1:A10)

Retrouver les données de la plage A1:A10 qui sont aussi dans la plage B1:B10. Saisir la formule en C1 par exemple, puis l'étirer vers le bas. =SI(NB.SI($B$1:$B$10;A1)>0;A1;"")

'Ne prendre en compte que les lignes impaires pour additionner les valeurs de la plage A1:A10, 'et seulement si la plage B1:B10 = "x". =SOMMEPROD(((MOD(LIGNE(1:10);2)=1)*A1:A10)*(B1:B10="x")) 'Et pour les lignes paires =SOMMEPROD(((MOD(LIGNE(1:10);2)=0)*A1:A10)*(B1:B10="x"))

'Ne prendre en compte que les lignes impaires pour compter le nombre de valeurs supérieures à 10 'dans la plage A1:A10, et seulement si la plage B1:B10 = "x". =SOMMEPROD(((MOD(LIGNE(1:10);2)=1)*A1:A10>10)*(B1:B10="x"))

'Compter le nombre de lignes dans un plage de cellules =LIGNES($A$1:A5)

'Trouver le Nieme mot dans une phrase. 'La phrase est saisie en A1. En B1, saisisssez la position du mot à extraire. 'Placez la formule en A2 par exemple. =SI(B1>NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""));DROITE(A1;NBCAR(A1)-TROUVE("^^";SUBSTITUE (A1;" ";"^^";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";"")))));SI(B1=1;STXT(A1;1;TROUVE("^^";SUBSTITUE (A1;" ";"^^";1))-1);STXT(A1;TROUVE("^^";SUBSTITUE(A1;" ";"^^";B1-1))+1;TROUVE("^^";SUBSTITUE (A1;" ";"^^";B1))-TROUVE("^^";SUBSTITUE(A1;" ";"^^";B1-1))-1)))

'Transposer une colonne en ligne. 'Si la première valeur est en A1, placez par exemple cette formule en B1 =INDIRECT("A"&COLONNE()-1) 'Utilisez ensuite les poignées de recopie vers la droite

'Extraire les données d'une liste sans doublons. 'Si par exemple votre liste initiale est dans la plage A1:A15, 'En B1, vous saisissez =A1 En B2, vous saisissez cette formule matricielle (à valider par Ctrl+Maj+Entree) =SI(B1="";"";INDEX(A2:A$16;MIN(SI(NB.SI(B$1:B1;A2:A$15);LIGNE(A$16)-LIGNE()+1;LIGNE(A2:A$15)-LIGNE()+1)))) 'puis vous étirez la formule vers le bas. Nota: La cellule A16 ne doit pas contenir de données.

'Récupérer la valeur de la première cellule résultant d'un filtre automatique '(exemple pour un filtre placé dans la colonne A) =INDEX(A1:A100;MIN(SI(SOUS.TOTAL(3;DECALER(A2;LIGNE(A1:A100);));LIGNE(A3:A100)));1) 'A valider par Ctrl+Maj+Entrée

'Extraire une donnée sur 5, contenue dans la plage A1:A100 =SI(LIGNE()=1;A1;INDEX($A$2:$A$100;(LIGNE()-2)*5+5)) '(formule à saisir dans la 1eme ligne d'une autre colonne puis à étirer vers le bas)

'Extraire les valeurs impaires contenues dans la plage A1:A5 'En B1: =(A1=IMPAIR(A1))*1 'Formule à étirer vers le bas 'En C1: =EQUIV(1;$B$1:$B$5;0) 'En C2: =EQUIV(1;INDIRECT("B"&(SOMME($C$1:C1)+1)&":B5");0) 'Puis vous étirez la formule vers le bas. 'En D1: =SI(ESTERREUR(DECALER($A$1;SOMME($C$1:C1)-1;0));"";DECALER($A$1;SOMME($C$1:C1)-1;0)) 'Puis vous étirez la formule vers le bas. '(Les colonnes B et C peuvent ensuite être masquées si besoin)

'Comment faire une RECHERCHEV sur plusieurs feuillles: 'Si par exemple, les données sont dans la plage C3:F20 de chaque feuille (Feuil1 à Feuil4): 'Utilisez: =RECHERCHEV(FeuilResult!A2;INDIRECT("Feuil"&NB(EQUIV(FeuilResult!A2;Feuil1!C3:C20;0))+ NB(EQUIV(FeuilResult!A2;Feuil2!C3:C20;0))*2+NB(EQUIV(FeuilResult!A2;Feuil3!C3:C20;0))*3+ NB(EQUIV(FeuilResult!A2;Feuil4!C3:C20;0))*4&"!C3:F20");4;FAUX) 'Si la donnée saisie (FeuilResult!A2) est trouvée, la fonction renvoie la valeur de la colonne F. 'La formule suppose que la donnée recherchée est unique dans le colonne C de 'chaque plage (Feuil1!C3:C20 à Feuil4!C3:C20). INDIRECT("Feuil"&NB(EQUIV(FeuilResult!A2;Feuil1!C3:C20;0))+NB(EQUIV(FeuilResult!A2;Feuil2!C3:C20;0))*2+ NB(EQUIV(FeuilResult!A2;Feuil3!C3:C20;0))*3+NB(EQUIV(FeuilResult!A2;Feuil4!C3:C20;0))*4 'Permet de définir dans quelle feuille faire la recherche.

'Touver la valeur la plus proche entre bornes: 'La fonction renvoie la donnée correspondante de la colonne E si la valeur de la cellule B1 '(la plus proche, compris entre +/- 0.2) est trouvée dans la plage $D$1:$D$5. =SI(SOMMEPROD(($D$1:$D$5>=B1-0,2)*($D$1:$D$5<=B1+0,2))=1;RECHERCHEV(B1+SI(ABS(PETITE.VALEUR ($D$1:$D$5;NB.SI($D$1:$D$5;"<=" &B1))-B1)>PETITE.VALEUR($D$1:$D$5;NB.SI($D$1:$D$5;"<"&B1)+1) -B1;PETITE.VALEUR($D$1:$D$5;NB.SI($D$1:$D$5;"<"&B1)+1)-B1;PETITE.VALEUR($D$1:$D$5;NB.SI ($D$1:$D$5;"<="&B1))-B1);$D$1:$E$5;2;FAUX);"Aucune valeur dans la tolérance")

'Comment faire pour identifier la valeur de la première cellule non vide, à gauche de la cellule M1? =INDEX(A1:L1;MAX(COLONNE(A1:L1)*(A1:L1<>""))) 'formule matricielle à valider par Ctrl+Maj+Entrée

'Comment tester l'appartenance d'une cellule à une liste de valeurs 'Cet exemple renvoie 1 si la valeur de la cellule A1 existe dans la matrice {1;4;7;18}, sinon elle renvoie 0. =SOMMEPROD(({1;4;7;18}=A1)*1)

Lister toutes les occurences contenant mot "test": (Lister toutes les cellules qui contiennent par exemple test_toto, toto_test...) Par exemple tes données (test_toto, tot_test ...) sont dans la colonne A (à partir de la cellule A2), dans une feuille nommée "Saisie". Dans une deuxième feuille, en D2, vous allez indiquer la chaîne de caractères à rechercher: "test". Dans la cellule K2, saisissez cette formule: =EQUIV("*"&D$2&"*";DECALER(Saisie!A$2:A$1000;SOMME(K$1:K1);0);0) puis utilisez les poignées de recopie vers le bas. Dans la cellule L2, saisissez: =INDEX(Saisie!A$2:A$1000;SOMME(K$2:K2)) puis utilisez les poignées de recopie vers le bas. Vous pourrez si vous le souhaitez ensuite, masquer la colonne K et les valeurs #N/A.

VIII. Informations

'Utilisez la fonction "INFO" pour afficher des informations sur l'environnement d'exploitation: 'Exemple: Afficher la version du système d'exploitation. =INFO("versionse") 'Afficher la version d'Excel =INFO("version")

Généralités sur la fonction CELLULE: Renvoie des informations sur la mise en forme, la position ou le contenu de la cellule supérieure gauche d'une référence. (voir l'aide en ligne Excel pour plus de détails: ci-dessous quelques exemples d'utilisation) 'Afficher le chemin et le nom du classeur =CELLULE("filename") 'Remarque: La fonction renvoie une chaine vide si le classeur n'est pas sauvegardé 'Afficher le répertoire du classeur =GAUCHE(CELLULE("filename");CHERCHE("[";CELLULE("filename");1)-2) 'Remarque: La fonction renvoie une chaine vide si le classeur n'est pas sauvegardé 'Créer un lien hypertexte pour ouvrir l'explorateur windows sur le répertoire contenant le classeur =LIEN_HYPERTEXTE(GAUCHE(CELLULE("filename");CHERCHE("[";CELLULE("filename");1)-2);"Lien vers le répertoire") 'Afficher le nom du classeur et de la feuille =STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1));300) 'Remarque: La fonction renvoie une chaine vide si le classeur n'est pas sauvegardé 'Afficher le nom du classeur =STXT(CELLULE("nomfichier");TROUVE("[";CELLULE("nomfichier"))+1;TROUVE("]";CELLULE("nomfichier"))- TROUVE("[";CELLULE("nomfichier"))-1)

IX. Logique

Utiliser plusieurs conditions dans la fonction Si: 'Dans cet exemple, la formule renvoie "Faux" si toutes les cellules A1 ,B1 et C1 sont vides 'et Vrai si au moins une des cellules est non vide =SI(ET(A1="";B1="";C1="");"Faux";"Vrai")

'Vérifier si la valeur de la cellule A1 est comprise entre 10 et 20. =SI(A1<10;"AU DESSOUS";SI(ET(A1>=10;A1<=20);"DANS PLAGE";"AU DESSUS"))

'Renvoyer 0 si le résultat d'une formule est négatif. =SI(SOMME(A1:A10)>0;SOMME(A1:A10);0) 'Une autre solution =MAX(0;SOMME(A1:A10))

'Vérifier si la valeur de la cellule A1 est comprise entre 10 et 20 (renvoie Vrai ou Faux) =ET(A1>=10;A1<=20)

'La fonction OU: 'Renvoie VRAI si au moins un argument est VRAI et FAUX si tous les arguments sont FAUX. '30 conditions peuvent être testées. 'Cet exemple affiche le texte "OK" si la cellule A1=10 ou si la cellule A2=20. =SI(OU(A1=10;A2=20);"VRAI";"")

'Afficher la valeur 10 si la cellule A1 contient le texte "Option1", ou la valeur 20 si A1 'contient "Option2". Si une autre donnée est saisie en A1, le résultat affiché sera une cellule vide. =SI(A1="Option1";10;SI(A1="Option2";20;"")) 'Cet autre exemple affiche 0 si une autre donnée est saisie en A1 =(A1="Option1")*10+(A1="Option2")*20

'Vérifier si 2 plages de cellules (A1:A10 et B1:B10) sont identiques. 'La formule renvoie VRAI si les données des 2 plages sont identiques. =ET(A1:A10=B1:B10)

'Vérifier si la cellule A1 est vide. =ESTVIDE(A1) 'Vérifier si la cellule A1 est non vide =NON(ESTVIDE(A1))

X. Divers

Pour afficher les formules dans les cellules plutot que le résultat: Menu Outils Options Onglet Affichage Cochez l'option Formules D'autres solutions existent: Utilisez les touches racourci Ctrl + " (Ctrl + guillemet) Appliquez le même raccourci clavier pour réafficher les résultats à la place des formules. Il est aussi possible de précéder la formule d'une apostrophe (ou d'un espace) pour qu'elle s'affiche dans la cellule. '=A1+A2

La signification des codes d'erreur dans les résultats de formules: NUL! : Survient lorsque vous spécifiez une intersection de deux zones qui, en réalité, ne se coupent pas. DIV/0! : Survient lorsqu'un nombre est divisé par zéro (0). VALEUR! REF! : Survient lorsque les coordonnées d'une cellule ne sont pas valide. NOM? : Survient lorsque l'application ne reconnaît pas le texte dans une formule. NOMBRE! : Survient lorsqu'une formule ou une fonction contient des valeurs numériques non valides. N/A : Survient lorsqu'une valeur n'est pas disponible pour une fonction ou une formule.

'Insérer un commentaire dans la formule =A1*A2+N("mon commentaire")

Remplacer une formule par son résultat: Sélectionnez la formule dans la barre de formules puis Cliquez sur la touche F9 Une autre solution: Après avoir sélectionné la cellule: Utilisez les touches F2, F9 puis Entrée. Si la saisie de la formule:est en cours, utilisez les touches F9 puis Entrée.

Effacer une plage de cellules mais pas les formules: Sélectionnez la plage de cellules Appuyez sur la touche F5 du clavier Cliquez sur le bouton "Cellules" Sélectionnez "Constantes" Cochez le ou les types de données à supprimer (texte, nombre ...) Cliquez sur "Ok" Les cellules correspondantes sont sélectionnées Vous pouvez alors appuyer sur la touche "Suppr" du clavier

Afficher la boîte de dialogue "Arguments de la fonction" pendant la saisie de la formule. Par exemple, saisissez dans une cellule: =NBVAL Ensuite appuyez sur la combinaison de touches Ctrl+A

Afficher les arguments d'une fonction dans la barre de formules. Par exemple, saisissez dans une cellule: =NBVAL Ensuite appuyez sur la combinaison de touches Ctrl+Shift+A

Comment vérifier rapidement l'orthographe des fonctions? Saisissez les noms de fonctions en minuscules. Les noms de fonctions valides seront automatiquement transformées en majuscule lorsque vous appuyerez sur la touche Entrée.