bonjour
j'espére que ces informations pourront etre utiles à quelqu'un
(surtout n'allez pas croire que je suis l'auteur de ces fonctions , et par avance toutes mes excuses si j'ai oublié de citer les créateurs de certaines formules )
Généralités
*********
Les symboles opérateurs :
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 etre identique à la version d'Excel installée sur le PC
Nommer les formules
Le lien sur le forum XLD
Lien supprimé
Pour valider une formule matricielle :
Cliquez dans la barre de formule et appuyez sur les touches Ctrl + Maj + Entrée
Ensuite , dans la barre de formule , apparait une accolade à chaque extrémité de la formule: {=MaFormule}
(Si vous nommez les formules matricielles , les validations ne sont plus nécéssaires)
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 , pour la cellule A1
=NOMPROPRE(A1)
La fonction CAR renvoie le caractère ANSI spécifié ( dans l'exemple ci dessous , le résultat = 0 )
=CAR(48)
d'autres valeurs : CAR(57)=9 , CAR(65)=A , CAR(90)=Z , CAR(97)=a , CAR(122)=z
Supprimer les espaces en trop dans le texte de la cellule A1
=SUPPRESPACE(A1)
Contrôler si la phrase dans la cellule A1 contient le texte 'xld' ( 1=oui , 0=non )
=NB.SI(A1;'*xld*')
Séparer le(s) prénom(s) et le nom saisis dans la cellule A1 : Une solution donnée par Phibou
Le lien sur le forum XLD
Lien supprimé
Remplacer des données dans une chaine de caracteres (exemple '1999' par '2005' )
=SUBSTITUE('test 1999 et essai 1999'; '1999'; '2005')
Ne remplacer que la deuxieme occurence correspondant aux caracteres 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)
Extraire le dernier mot d'un texte saisi dans la cellule A1 : Source Disciplus excelLabo
{=DROITE(A1;EQUIV(' ';STXT(A1;NBCAR(A1) - LIGNE(INDIRECT('1:' & NBCAR(A1)));1);0))}
Afficher le nombre de caracteres maxi contenu dans une cellule de la plage A1:A10
{=MAX(NBCAR(A1:A10))}
Renvoie OUI si la cellule A1 est du texte
=SI(ESTTEXTE(A1);'OUI';'NON')
Compter le nombre de 'a' dans la cellule A1
=NBCAR(A1)-NBCAR(SUBSTITUE(A1;'a';''))
Compter le nombre de cellules qui contient du texte dans la plage A1:A10
=SOMMEPROD(ESTTEXTE(A1:A10)*1)
Statistiques
**********
Compter le nombre de valeurs numériques das la colonne A
=NB(A:A)
Compter le nombre de cellules contenant la valeur 'xld' , dans la colonne A
=NB.SI(A:A;'xld')
Compter le nombre de cellules vides dans la plage A1:A10
=NB.VIDE(A1:A10)
Remarque 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))
Renvoie la somme des 6 plus grandes valeurs d'une plage
=SOMME(A1:A10*(A1:A10>=GRANDE.VALEUR(A1:A10;6)))
Formule matricielle à valider par Ctrl+Maj+Entree
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))
Trouver une valeur proche dans une colonne
Le lien sur le forum XLD
Lien supprimé
Afficher le classement de la cellule B1 , par rapport à la plage de valeurs B1:B10
=NB($B$1:$B$10)+1-RANG(B1;$B$1:$B$10;1)
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( une solution donnée par Jean-Marie)
=SI(NB.SI(A1:A10;MODE(A1:A10))>2;MODE(A1:A10);'')
Compter le nombre de valeurs identiques (unique) entre deux plages
=SOMMEPROD((FREQUENCE(A1:A10;B1:B10)>0)*1)-1
Compter le nombre de valeurs différentes dans la plage A1:A10
=SOMMEPROD(1/NB.SI(A1:A10;A1:A10))
Une autres solution de Monique , dans le cas ou 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 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 compris )
=FREQUENCE(A1:A10; {20;10} )
Math et Trigo
***********
Savoir 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 etre affecté à plusieurs plages de cellules( moyenne des plages A2:A5 et C2:C5 dans l'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 'xld'
=SOMME.SI(A:A;'xld';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 aleatoire 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 meme résultat
=PUISSANCE(A1;2)
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)
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 'xld' dans la plage A1:A10 , est classé 'number one' dans la plage B1:B10
= SOMMEPROD((A1:A10 = 'xld')*(B1:B10='number one'))
Calculer une moyenne sans tenir compte des zéros
=SOMME(A1:A10)/NB.SI(A1:A10;'>0')
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)
Effectuer la somme de la cellule A1, pour les Feuil1 à Feuil4
=SOMME(Feuil1:Feuil4!A1)
Arrondir à 10 ( 213,05 renvoie 210 , 216,001 renvoie 220
=ARRONDI(A1/10;0)*10
Additionner les cellules de la plage C1:C10 , si la cellule associée dans la plage A1:A10 est égal à 'xld' et si la cellule dans la plage B1:B10 est égal à 50
= SOMMEPROD((A1:A10 = 'xld')*(B1:B10=50)*(C1:C10))
Les plages de cellules pour chaque matrice doivent etre identiques .
Le meme exemple que ci-dessus adapté au cas ou il y aurait des valeurs numeriques 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 'rose'
une solution donnée par Monique
=SOMMEPROD(ESTNUM(CHERCHE('rose';A1:A10))*B1:B10)
une Demo de Monique & Celeda : Partager le bénéfice des entrées à une soirée de la manière suivante :
De 1 à 50 entrées, 100% vont au propriétaire
De 51 à 100, 50% pour propriétaire, 50% pour invité
Dès 101 entrées, 33% pour propriétaire, 66% pour invité
Le lien sur le forum XLD
Lien supprimé
Le fichier zippé
Lien supprimé
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)
Convertir des degrés décimaux en d mn s , une solution donnée par Phibou
en A1 : La valeur degré en décimal
en B1 : =ENT(A1)
en B2 : =ENT(60*(A1-B1))
en B3 : =3600*(A1-B1-1/60*B2)
Dans la cellule finale : =B1 & ' degrés ' & B2&' minutes ' & B3 &' secondes'
Afficher la racine Niéme d'un nombre
Par exemple : si la cellule A1 contient la valeur 27 , la formule ci-dessous renvoie 3
=A1^(1/3)
Scientifique
*********
Convertir une valeur décimale en binaire (macro complémentaire de l'utilitaire d'analyse)
=DECBIN(A1)
Les Dates et les Heures
*******************
Généralité sur les dates dans les formules Excel
Pour Excel, 1 = 1 jour = 24 heures
Renvoie le jour de la semaine pour une date spécifiiée dans la cellule A1 : Dimanche=1 , Samedi=7
=JOURSEM(A1)
Renvoie le jour de la semaine pour une date spécifiié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')
Calculer le temps écoulé , pour des heures saisies en A1(début) et A2(fin)
=MOD(A2-A1;1)
Les cellules A1 , A2 et celle contenant la formule doivent etre au format [hh]:mm
Télécharger la démo Cathy, Monique et Celeda pour avoir de nombreux exemples et commentaires sur les dates et les heures
Le lien dans la page de téléchargements d'XLD
https://www.excel-downloads.com/resources/dates-et-heures.221/
Calendrier Jour Partiel : Une démo de Monique et Celeda
Afficher dans une cellule , le nombre de jours ouvrés d'une année en déduisant les jours fériés ( cas de la fonction JOUR.SOUVRES ) mais en plus les jours de temps
partiel d'une personne
Le lien sur le forum XLD
Lien supprimé
Le fichier zippé
Lien supprimé
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 renvoyer 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 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 saisi 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')
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
Fontion NO.SEMAINE : Gestion des numéros de semaine qui sont décalés en 2005
Le lien sur le forum XLD
Lien supprimé
Convertir des secondes saisies dans la cellule A1 en heures ( la cellule contenant la formule au format hh : mm : ss )
=A1/60/60/24
Convertir en heure une somme de minutes
=SOMME(A1:A10)/1440
(Appliquer le format heure à la cellule de résultat )
Convertir des minutes en heures
Le lien sur le forum XLD
Lien supprimé
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 donnée par Monique pour le comptage du mois de Janvier dans une plage pouvant contenir des cellules vides , car 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 )
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
)
Compter le nombre de semaines entre 2 dates , plusieurs solutions données par Monique
Le lien sur le forum XLD
Lien supprimé
Afficher le numero 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')
Créer et gérer un agenda , encore une superbe demo de Monique
Le lien sur le forum XLD
Lien supprimé
Le fichier zippé
Lien supprimé
Recherches et matrices
*******************
Renvoyer la dernière valeur numérique d'une colonne contenant des cellules vides: une solution trouvée par André
Le lien sur le forum XLD
Lien supprimé
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 donnée par Jean Marie , qui fonctionne avec des cellules vides
( formule matricielle à valider par Ctrl+Maj+Entree )
=INDEX(A1:A1000;MAX(NON(ESTVIDE(A1:A1000))*LIGNE(A1:A1000));1)
Renvoyer le numéro de la derniere ligne non vide , dans la colonne A ( valider par Ctrl+Maj+Entree )
=MAX(NON(ESTVIDE(A1:A100))*LIGNE(A1:A100))
la formule fonctionne s'il y a des cellules vides dans la plage
Afficher la position d'une valeur recherchée (exemple : 'xld' ) dans la plage cible A1:A10
=EQUIV('xld';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 l'on ne connaît 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('xl*';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
=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' dans le document nommé monFichier.doc
=LIEN_HYPERTEXTE('C:\monFichier.doc#Signet1';'Cliquez ici')
Afficher la lettre de la colonne , pour la cellule ou est placée cette formule . Une solution donnée par Monique
=GAUCHE(ADRESSE(1;COLONNE();4);NBCAR(ADRESSE(1;COLONNE();4))-1)
Chercher la valeur 'xld' dans la colonne gauche de la plage A110 , et renvoyer la valeur située dans la même ligne et dans la 4eme colonne de la plage
=RECHERCHEV('xld';A110;4;0)
Récuperer la valeur de la cellule A1 d'un classeur fermé , nommé 'monFichier.xls'
='C:\[monFichier.xls]Feuil1'!A1
Compter le nombre de caracteres à 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 plabe A1:A5
=SOMME(INDIRECT('A1:A'&B1))
Récuperer une donnée sur 2 , dans la colonne A , (une solution de Monique )
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 , étendre les formules dans la colonne de résultat
Informations
**********
Utilisez la fonction 'INFO' pour afficher des informations sur l'environnement d'exploitation :
Exemple : Afficher la version du système d'exploitation utilisé
=INFO('versionse')
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 : le résultat est vide si le classeur n'est pas sauvegardé
Afficher le répertoire du classeur
=GAUCHE(CELLULE('filename');CHERCHE('[';CELLULE('filename');1)-2)
Remarque : le résultat est vide si le classeur n'est pas sauvegardé
Afficher le nom de l'onglet dans une cellule : des solutions données par André et Gérard
Le lien sur le forum XLD
Lien supprimé
Afficher des smileys différents en fonction de la valeur de la cellule A1
( la police de caractère doit etre de type 'Wingdings' , dans la cellule contenant la formule )
=SI(A1>=0;'J';'L')
Utiliser les anciennes macro XL4
Le lien sur le forum XLD
https://www.excel-downloads.com/threads/astuce-recente-lire-cellule.32955/
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 , sinon indiquer la position en dehors de la plage
=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)
Divers
*****
Pour afficher les formules dans les cellules :
Menu Outils
Options
onglet Affichage
cochez l'option Formules
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 resultat :
Sélectionnez la formule dans la barre de formules puis Cliquez sur la touche F9
très bon week end
MichelXld
Message édité par: MichelXld, à: 02/04/2005 20:47
j'espére que ces informations pourront etre utiles à quelqu'un
(surtout n'allez pas croire que je suis l'auteur de ces fonctions , et par avance toutes mes excuses si j'ai oublié de citer les créateurs de certaines formules )
Généralités
*********
Les symboles opérateurs :
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 etre identique à la version d'Excel installée sur le PC
Nommer les formules
Le lien sur le forum XLD
Lien supprimé
Pour valider une formule matricielle :
Cliquez dans la barre de formule et appuyez sur les touches Ctrl + Maj + Entrée
Ensuite , dans la barre de formule , apparait une accolade à chaque extrémité de la formule: {=MaFormule}
(Si vous nommez les formules matricielles , les validations ne sont plus nécéssaires)
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 , pour la cellule A1
=NOMPROPRE(A1)
La fonction CAR renvoie le caractère ANSI spécifié ( dans l'exemple ci dessous , le résultat = 0 )
=CAR(48)
d'autres valeurs : CAR(57)=9 , CAR(65)=A , CAR(90)=Z , CAR(97)=a , CAR(122)=z
Supprimer les espaces en trop dans le texte de la cellule A1
=SUPPRESPACE(A1)
Contrôler si la phrase dans la cellule A1 contient le texte 'xld' ( 1=oui , 0=non )
=NB.SI(A1;'*xld*')
Séparer le(s) prénom(s) et le nom saisis dans la cellule A1 : Une solution donnée par Phibou
Le lien sur le forum XLD
Lien supprimé
Remplacer des données dans une chaine de caracteres (exemple '1999' par '2005' )
=SUBSTITUE('test 1999 et essai 1999'; '1999'; '2005')
Ne remplacer que la deuxieme occurence correspondant aux caracteres 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)
Extraire le dernier mot d'un texte saisi dans la cellule A1 : Source Disciplus excelLabo
{=DROITE(A1;EQUIV(' ';STXT(A1;NBCAR(A1) - LIGNE(INDIRECT('1:' & NBCAR(A1)));1);0))}
Afficher le nombre de caracteres maxi contenu dans une cellule de la plage A1:A10
{=MAX(NBCAR(A1:A10))}
Renvoie OUI si la cellule A1 est du texte
=SI(ESTTEXTE(A1);'OUI';'NON')
Compter le nombre de 'a' dans la cellule A1
=NBCAR(A1)-NBCAR(SUBSTITUE(A1;'a';''))
Compter le nombre de cellules qui contient du texte dans la plage A1:A10
=SOMMEPROD(ESTTEXTE(A1:A10)*1)
Statistiques
**********
Compter le nombre de valeurs numériques das la colonne A
=NB(A:A)
Compter le nombre de cellules contenant la valeur 'xld' , dans la colonne A
=NB.SI(A:A;'xld')
Compter le nombre de cellules vides dans la plage A1:A10
=NB.VIDE(A1:A10)
Remarque 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))
Renvoie la somme des 6 plus grandes valeurs d'une plage
=SOMME(A1:A10*(A1:A10>=GRANDE.VALEUR(A1:A10;6)))
Formule matricielle à valider par Ctrl+Maj+Entree
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))
Trouver une valeur proche dans une colonne
Le lien sur le forum XLD
Lien supprimé
Afficher le classement de la cellule B1 , par rapport à la plage de valeurs B1:B10
=NB($B$1:$B$10)+1-RANG(B1;$B$1:$B$10;1)
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( une solution donnée par Jean-Marie)
=SI(NB.SI(A1:A10;MODE(A1:A10))>2;MODE(A1:A10);'')
Compter le nombre de valeurs identiques (unique) entre deux plages
=SOMMEPROD((FREQUENCE(A1:A10;B1:B10)>0)*1)-1
Compter le nombre de valeurs différentes dans la plage A1:A10
=SOMMEPROD(1/NB.SI(A1:A10;A1:A10))
Une autres solution de Monique , dans le cas ou 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 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 compris )
=FREQUENCE(A1:A10; {20;10} )
Math et Trigo
***********
Savoir 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 etre affecté à plusieurs plages de cellules( moyenne des plages A2:A5 et C2:C5 dans l'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 'xld'
=SOMME.SI(A:A;'xld';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 aleatoire 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 meme résultat
=PUISSANCE(A1;2)
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)
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 'xld' dans la plage A1:A10 , est classé 'number one' dans la plage B1:B10
= SOMMEPROD((A1:A10 = 'xld')*(B1:B10='number one'))
Calculer une moyenne sans tenir compte des zéros
=SOMME(A1:A10)/NB.SI(A1:A10;'>0')
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)
Effectuer la somme de la cellule A1, pour les Feuil1 à Feuil4
=SOMME(Feuil1:Feuil4!A1)
Arrondir à 10 ( 213,05 renvoie 210 , 216,001 renvoie 220
=ARRONDI(A1/10;0)*10
Additionner les cellules de la plage C1:C10 , si la cellule associée dans la plage A1:A10 est égal à 'xld' et si la cellule dans la plage B1:B10 est égal à 50
= SOMMEPROD((A1:A10 = 'xld')*(B1:B10=50)*(C1:C10))
Les plages de cellules pour chaque matrice doivent etre identiques .
Le meme exemple que ci-dessus adapté au cas ou il y aurait des valeurs numeriques 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 'rose'
une solution donnée par Monique
=SOMMEPROD(ESTNUM(CHERCHE('rose';A1:A10))*B1:B10)
une Demo de Monique & Celeda : Partager le bénéfice des entrées à une soirée de la manière suivante :
De 1 à 50 entrées, 100% vont au propriétaire
De 51 à 100, 50% pour propriétaire, 50% pour invité
Dès 101 entrées, 33% pour propriétaire, 66% pour invité
Le lien sur le forum XLD
Lien supprimé
Le fichier zippé
Lien supprimé
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)
Convertir des degrés décimaux en d mn s , une solution donnée par Phibou
en A1 : La valeur degré en décimal
en B1 : =ENT(A1)
en B2 : =ENT(60*(A1-B1))
en B3 : =3600*(A1-B1-1/60*B2)
Dans la cellule finale : =B1 & ' degrés ' & B2&' minutes ' & B3 &' secondes'
Afficher la racine Niéme d'un nombre
Par exemple : si la cellule A1 contient la valeur 27 , la formule ci-dessous renvoie 3
=A1^(1/3)
Scientifique
*********
Convertir une valeur décimale en binaire (macro complémentaire de l'utilitaire d'analyse)
=DECBIN(A1)
Les Dates et les Heures
*******************
Généralité sur les dates dans les formules Excel
Pour Excel, 1 = 1 jour = 24 heures
Renvoie le jour de la semaine pour une date spécifiiée dans la cellule A1 : Dimanche=1 , Samedi=7
=JOURSEM(A1)
Renvoie le jour de la semaine pour une date spécifiié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')
Calculer le temps écoulé , pour des heures saisies en A1(début) et A2(fin)
=MOD(A2-A1;1)
Les cellules A1 , A2 et celle contenant la formule doivent etre au format [hh]:mm
Télécharger la démo Cathy, Monique et Celeda pour avoir de nombreux exemples et commentaires sur les dates et les heures
Le lien dans la page de téléchargements d'XLD
https://www.excel-downloads.com/resources/dates-et-heures.221/
Calendrier Jour Partiel : Une démo de Monique et Celeda
Afficher dans une cellule , le nombre de jours ouvrés d'une année en déduisant les jours fériés ( cas de la fonction JOUR.SOUVRES ) mais en plus les jours de temps
partiel d'une personne
Le lien sur le forum XLD
Lien supprimé
Le fichier zippé
Lien supprimé
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 renvoyer 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 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 saisi 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')
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
Fontion NO.SEMAINE : Gestion des numéros de semaine qui sont décalés en 2005
Le lien sur le forum XLD
Lien supprimé
Convertir des secondes saisies dans la cellule A1 en heures ( la cellule contenant la formule au format hh : mm : ss )
=A1/60/60/24
Convertir en heure une somme de minutes
=SOMME(A1:A10)/1440
(Appliquer le format heure à la cellule de résultat )
Convertir des minutes en heures
Le lien sur le forum XLD
Lien supprimé
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 donnée par Monique pour le comptage du mois de Janvier dans une plage pouvant contenir des cellules vides , car 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 )
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
)Compter le nombre de semaines entre 2 dates , plusieurs solutions données par Monique
Le lien sur le forum XLD
Lien supprimé
Afficher le numero 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')
Créer et gérer un agenda , encore une superbe demo de Monique
Le lien sur le forum XLD
Lien supprimé
Le fichier zippé
Lien supprimé
Recherches et matrices
*******************
Renvoyer la dernière valeur numérique d'une colonne contenant des cellules vides: une solution trouvée par André
Le lien sur le forum XLD
Lien supprimé
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 donnée par Jean Marie , qui fonctionne avec des cellules vides
( formule matricielle à valider par Ctrl+Maj+Entree )
=INDEX(A1:A1000;MAX(NON(ESTVIDE(A1:A1000))*LIGNE(A1:A1000));1)
Renvoyer le numéro de la derniere ligne non vide , dans la colonne A ( valider par Ctrl+Maj+Entree )
=MAX(NON(ESTVIDE(A1:A100))*LIGNE(A1:A100))
la formule fonctionne s'il y a des cellules vides dans la plage
Afficher la position d'une valeur recherchée (exemple : 'xld' ) dans la plage cible A1:A10
=EQUIV('xld';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 l'on ne connaît 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('xl*';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
=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' dans le document nommé monFichier.doc
=LIEN_HYPERTEXTE('C:\monFichier.doc#Signet1';'Cliquez ici')
Afficher la lettre de la colonne , pour la cellule ou est placée cette formule . Une solution donnée par Monique
=GAUCHE(ADRESSE(1;COLONNE();4);NBCAR(ADRESSE(1;COLONNE();4))-1)
Chercher la valeur 'xld' dans la colonne gauche de la plage A1
10 , et renvoyer la valeur située dans la même ligne et dans la 4eme colonne de la plage=RECHERCHEV('xld';A1
10;4;0)Récuperer la valeur de la cellule A1 d'un classeur fermé , nommé 'monFichier.xls'
='C:\[monFichier.xls]Feuil1'!A1
Compter le nombre de caracteres à 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 plabe A1:A5
=SOMME(INDIRECT('A1:A'&B1))
Récuperer une donnée sur 2 , dans la colonne A , (une solution de Monique )
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 , étendre les formules dans la colonne de résultat
Informations
**********
Utilisez la fonction 'INFO' pour afficher des informations sur l'environnement d'exploitation :
Exemple : Afficher la version du système d'exploitation utilisé
=INFO('versionse')
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 : le résultat est vide si le classeur n'est pas sauvegardé
Afficher le répertoire du classeur
=GAUCHE(CELLULE('filename');CHERCHE('[';CELLULE('filename');1)-2)
Remarque : le résultat est vide si le classeur n'est pas sauvegardé
Afficher le nom de l'onglet dans une cellule : des solutions données par André et Gérard
Le lien sur le forum XLD
Lien supprimé
Afficher des smileys différents en fonction de la valeur de la cellule A1
( la police de caractère doit etre de type 'Wingdings' , dans la cellule contenant la formule )
=SI(A1>=0;'J';'L')
Utiliser les anciennes macro XL4
Le lien sur le forum XLD
https://www.excel-downloads.com/threads/astuce-recente-lire-cellule.32955/
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 , sinon indiquer la position en dehors de la plage
=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)
Divers
*****
Pour afficher les formules dans les cellules :
Menu Outils
Options
onglet Affichage
cochez l'option Formules
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 resultat :
Sélectionnez la formule dans la barre de formules puis Cliquez sur la touche F9
très bon week end
MichelXld
Message édité par: MichelXld, à: 02/04/2005 20:47
Dernière modification par un modérateur:
