Quelques exemples de fonctions dans Calc

Discussions à propos du tableur Calc.
Les questions sur les macros doivent être postées dans la section dédiée en dessous.

Modérateur : Vilains modOOs

Règles du forum
Cette section est uniquement dédiée au tableur Calc. Vous ne devez pas poster ici de questions sur les macros mais utiliser la section éponyme.
MichelXld
Membre OOrganisé
Membre OOrganisé
Messages : 66
Inscription : 10 janv. 2006 22:33

Quelques exemples de fonctions dans Calc

Message par MichelXld »

Le modérateur a écrit : :alerte: Aucune question dans ce fil qui sert de présentation aux formules de Calc ;

Les questions sur les formules et fonctions sont à poser en section Tableur
Bonjour

J'ouvre ce nouveau post car je ne savais pas trop ou placer ces informations , en espérant que cela puisse aider quelqu'un




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





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 , dans la cellule A1
=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 valeurs : CAR(48 )=0 , 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)


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)


Afficher une lettre aléatoire entre A et Z
=CAR(ENT(ALEA()*26+1)+64)
3 autres solutions pour afficher une lettre aléatoire minuscule ou majuscule
=CHOISIR(ARRONDI(ALEA();0)+1;CAR(ENT(ALEA()*26+1)+64);CAR(ENT(ALEA()*26+1)+96))
=CAR(ENT(ALEA()*26+1)+64+CHOISIR(ARRONDI(ALEA();0)+1;0;32))
=CAR(ENT(ALEA()*26+1)+CHOISIR(ARRONDI(ALEA();0)+1;64;96))


Renvoie OUI si la cellule A1 est du texte
=SI(ESTTEXTE(A1);"OUI";"NON")


Compter le nombre de "a" dans la cellule A1 (Les A majuscules ne sont pas comptés)
=NBCAR(A1)-NBCAR(SUBSTITUE(A1;"a";""))


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 XLD , uniquement quand saisi en majuscules
=SOMMEPROD((EXACT(A1:A10;"XLD")*1))


Compter le nombre de cellules en majuscules dans la plage A1:A10
=SOMMEPROD((A1:A10<>"")*(EXACT(A1:A10;MAJUSCULE(A1:A10))))
Remarque : les données numériques sont considérées en majuscule


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


Créer une suite logique de lettres ( de A à Z )
Tout d'abord saisissez la formule dans la cellule A1
=CAR(64+LIGNE())
ensuite utilisez la poignée de recopie vers le bas

pour obtenir une suite de lettres minuscules vous pouvez utiliser :
=MINUSCULE(CAR(64+LIGNE()))





Statistiques
**************

Compter le nombre de valeurs numériques dans la plage A1:A10
=NB(A1:A10)


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


Compter le nombre de cellules vides dans la plage A1:A10
=NB.VIDE(A1:A10)


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 "xld" , dans la colonne A
=NB.SI(A1:A10;"xld")


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)


Afficher la plus petite valeur immédiatement superieure à 0
=PETITE.VALEUR(A1:A10;NB.SI(A1:A10;"<="&0)+1)


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 le classement de toutes les cellules de la plage A1:A10 , les unes par rapport aux autres
(les données numériques sont dans la plage A1:A10)
=RANG(A1;DECALER($A$1;0;0;NBVAL($A$1:$A$10);1))
Saisissez le formule dans la cellule B1 et ensuite faites un "Glisser" , jusqu'à la cellule B10


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


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 numériques différentes dans la plage A1:A10
=SOMMEPROD(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 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)

=SOMMEPROD((MOIS(A2:A31)=12)*(A2:A31>0)) ne comptabilise pas les cellules vides ou contenant 0
(le 0 est interprété comme étant le 30/12/1899, donc comptabilisé sur décembre par défaut)


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)





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)

Les arguments associés à la fonction SOUS.TOTAL :
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(A1:A10;"Xld";B1:B10)


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)


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 traite identiques en valeurs entieres , la totalité des décimales étant 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 derniere )
=ARRONDI($A$1/$B$1;0)
Le montant de la derniere 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)


Compter le nombre de fois ou "OOo" dans la plage A1:A10 , est classé "number one" dans la plage B1:B10
= SOMMEPROD((A1:A10 = "OOo")*(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)


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 .


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


Afficher la Valeur absolue d'un nombre ( nombre sans son signe )
=ABS(-10)
Cet exemple renvoie le résultat : 10


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


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


Retrouver le plus petit multiple commun dune 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)




Scientifiques
**************

Convertit un nombre décimal compris entre -512 et 511 en nombre binaire.
=DECBIN(100;8 )
renvoie 01100100.
8 est le nombre de chiffres à utiliser.
100 est le nombre décimal. S'il s'agit d'un nombre négatif, la fonction renvoie un nombre binaire composé de 10 caractères.
Le bit maximum est le bit de signe, les 9 autres bits sont la valeur.


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 pieces sur un ensemble total de 10 pièces
=COMBIN(10;3)




Les Dates et les Heures
****************************

Généralité sur les dates dans les formules
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écifié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)


Soustraire des heures qui sont au format "20h15"
=CNUM(SUBSTITUE(A2;"h";":"))-CNUM(SUBSTITUE(A1;"h";":"))
(Adaptez le format de la cellule conteant la formule)


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


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

Remarque :
Utiliser le format [hh]:mm:ss pour afficher un nombre d'heures supérieur à 24 dans une cellule


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


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 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
(Appliquer 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)


Afficher le premier Lundi du mois, pour une date saisie dans la cellule A1
=A1-JOUR(A1)+9-JOURSEM(A1-JOUR(A1))
Suite à ce fil de discussion, préférer soit :
=A1-JOUR(A1)+1+MOD(-JOURSEM(A1-JOUR(A1)+1;3);7)
ou
=DATE ( ANNEE(A1) ;MOIS(A1) ; 7*1 ) - JOURSEM ( DATE(ANNEE(A1) ;MOIS(A1) ; 7 ) ; 3 )
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 jour du mois en cours
=FIN.MOIS(AUJOURDHUI();0)


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


Multiplier des heures (au format hh:mm ) par un nombre
par exemple 01:30 x 2 = 3
=A1*B1*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)
Pensez au adapter le format des cellules


Mesurer le temps écoulé entre 2 dates au format jj.mm.aa hh:mm
A1 : heure de départ
A2 : heure d'arrivée
Ces deux cellules sont au format jj/mm/aaaa hh:mm
=ENT(A2-A1)&" j "&(HEURE(A2)<HEURE(A1))*24+HEURE(A2)-HEURE(A1)&" h"


Afficher le 3eme dimanche du mois de juin (Fêtes des Pères)
L'année est saisie en B1
=("22/6/"&B1)-JOURSEM("1/6/"&B1;2)




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(A1:A10000);1))


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 .


Insérer un lien hypertexte dans la cellule, pour ouvrir un autre document
=LIEN_HYPERTEXTE("file:///C:/Documents and Settings/michel/monFichier.txt";"cliquez ici!")
Attention à bien utiliser des Slash "/" , et non des antislash "\"

Si vous souhaitez créer un lien hypertexte vers un emplacement précis dans un document Writer,
il faut utiliser un signet pour définir cet emplacement.
L'exemple suivant crée un lien hypertexte vers le signet "monSignet" dans le document nommé monFichier.sxw
=LIEN_HYPERTEXTE("file:///C:/Documents and Settings/michel/monFichier.sxw#monSignet";"cliquez ici!")


Afficher la lettre de la colonne , pour la cellule ou est placée cette formule .
=GAUCHE(ADRESSE(1;COLONNE();4);NBCAR(ADRESSE(1;COLONNE();4))-1)


Chercher la valeur "Xld" 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("Xld";A1:D10;4;0)


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 ,
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


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


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;"")





Informations
***************

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 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 renvoie juste le nom de la feuille précédé des caractères "#$ si le classeur n'est pas enregistré


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





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 cible
=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
Cet exemple affiche le texte "OK" si la cellule A1=10 ou si la cellule A2=20
=SI(OU(A1=10;A2=20);"VRAI";"")




Divers
*********

Pour afficher les formules dans les cellules :
Menu Outils
Options
OpenOffice.orf Calc
Affichage
cochez l'option "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


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





bon Week end
michel
Dernière modification par MichelXld le 18 févr. 2006 21:18, modifié 3 fois.
Kinay
Membre lOOyal
Membre lOOyal
Messages : 39
Inscription : 04 déc. 2005 20:51
Localisation : 17
Contact :

Message par Kinay »

C'est un très bon travail et une bonne aide pour ceux qui ne maîtrise pas les formules dans un tableur.
Encore bravo
OOo 2.0 sous winXP SP2
boiseur

Message par boiseur »

Bonjour,

Merci de ce travail dans lequel j'ai trouvé la réponse à une question posé il y a quelques jours pour une base de données (Fonction : Durée)
Je vais toutefois vérifier mais il n'y a pas de raison pour que cela marche dans calc et pas dans Base :
-----------------------------------------------------------------
Remarque :
Utiliser le format [hh]:mm:ss pour afficher un nombre d'heures supérieur à 24 dans une cellue
-----------------------------------------------------------------

Autre remarque :
L'imbrication de fonction dans SI est limitée à 7 fois.

Cordialement
Joggerfou
Membre hOOnoraire
Membre hOOnoraire
Messages : 101
Inscription : 09 nov. 2005 21:11
Localisation : Reims
Contact :

Message par Joggerfou »

Très bon travail et très bonne initiative

Joggerfou
C'est pas parce que c'est payant que c'est mieux !!!
LibO Version: 6.0.0.3 sur Ubuntu 16.04
cege2
NOOuvel adepte
NOOuvel adepte
Messages : 12
Inscription : 27 janv. 2006 18:54

Message par cege2 »

merci :lol: encore des exemples s'il vous plait
débutante en formule
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

Message par Papayes »

Bonjour,
Merci Michel :D ,
je réponds à la "débutante" :wink: en formule..cege2...
encore une fois , il faut visiter le site de Christian
en particulier cette page où chaque fonction est en hyperlien pour accéder à des exemples...
http://christianwtd.free.fr/index.php?r ... =MenuCalc3
merci Christian... :D
A+
papayes
"Tout ce qui n'est pas donné est perdu"
Lucile
Membre hOOnoraire
Membre hOOnoraire
Messages : 150
Inscription : 30 janv. 2006 17:22
Localisation : Marne la Vallée, France

Message par Lucile »

bonjour MichelXld

ce serait pas mal d'avoir ce travail sous forme d'un PDF.

merci pour ces exemples.
2.3.1 sous Windows XP SP2 (boulot) et Ubuntu (maison) version officielle
<3
Joggerfou
Membre hOOnoraire
Membre hOOnoraire
Messages : 101
Inscription : 09 nov. 2005 21:11
Localisation : Reims
Contact :

Message par Joggerfou »

Bonjour Lucile
Il suffit simplement de faire un copier coller dans ooo writer, puis simplement générer en pdf et voilà un super document
Joggerfou
C'est pas parce que c'est payant que c'est mieux !!!
LibO Version: 6.0.0.3 sur Ubuntu 16.04
Avatar de l’utilisateur
vinc-mai
PassiOOnné
PassiOOnné
Messages : 540
Inscription : 22 déc. 2005 15:13
Localisation : Rhône-Alpes

Message par vinc-mai »

Bravo! Merci pour ce post dans lequel je viendrai piocher les formules dont j'aurai besoin.
:D
(Ooo 2.3 sur windows XP)
OOo 2.3 sur Ubuntu 7.10
jibla
NOOuvel adepte
NOOuvel adepte
Messages : 17
Inscription : 04 févr. 2006 07:04

Message par jibla »

:lol: et bien sur pour tout copier on peut utiliser le clic sur la molette centrale de la souris ...et hop le tour est joué dans ooo et sans passer par le clicdroit-copier-coller.. d'la balle !
Open Office 2.0

Linux Kubuntu dapper 6.02

PIII 1ghz - 1024 SDRAM
jumbo444
InconditiOOnnel
InconditiOOnnel
Messages : 769
Inscription : 10 mars 2006 13:09
Localisation : Rouen, France

Collage par clic milieu

Message par jumbo444 »

Cette dernière fonctionnalité (collage par clic milieu) n'est pas propre à OOo mais aux systèmes d'exploitation Unix.

Laurent
LibO 5.4.6.2 sous Ubuntu 16.04
MichelXld
Membre OOrganisé
Membre OOrganisé
Messages : 66
Inscription : 10 janv. 2006 22:33

Message par MichelXld »

bonjour à toutes et à tous

et merci pour vos messages

ci joint d'autres exemples



Texte
*****

Extaire tous les caracteres d'une phrase saisie dans la cellule A1
=STXT($A$1;COLONNE()-COLONNE($A$1);1)
Saisissez la formule en B1 et utilisez la poignée de recopie vers la droite


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 caractere
=STXT(A1;2;4)


Vérifier si la cellule D2 contient du texte ou des nombres :Renvoie FAUX s'il s'agit de texte
=ESTNONTEXTE(D2)
=ESTNUM(D2)


Vérifier si la cellule D2 contient du texte ou des nombres :Renvoie VRAI s'il s'agit de texte
=ESTTEXTE(D2)


Compter le nombre de chaines "mimi" contenu dans la cellule A1
=(NBCAR(A1)-NBCAR(SUBSTITUE(A1;"mimi";"")))/4




Statistiques
*********

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")
Etirez la formule jusqu'en B20


Renvoyer la probabilité d'un échantillon avec une distribution binomiale.
Syntaxe : B ( tirages ; probabilité_s ; limite_inf ; limite_sup)
Tirages représente le nombre de tirages.
Probabilité_s détermine la probabilité unique d'un résultat expérimental.
Limite_inf détermine la limite inférieure du nombre de tirages.
Limite_sup détermine la limite supérieure du nombre de tirages.
Exemple :
Quelle est la probabilité pour qu'un six soit obtenu deux fois avec dix lancer de dés ?
La probabilité d'avoir un six (ou tout autre nombre additionné) est 1/6 ; la formule en découlant est :
=B(10;1/6;2) donne 29,07 % de probabilité.(pensez à formater la cellule en pourcentage )


Compter le nombre de valeurs numeriques différentes dans la plage A1:A10 qui peux contenir du texte et des cellules vides
=SOMME(SI(FREQUENCE(A1:A10;A1:A10)>0;1))




Math et Trigo
***********

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


Convertir un nombre d'une unité à une autre unité.
La synthaxe : CONVERT_ADD(nombre;de_unité;à_unité)
Par exemple convertir 212 degrés Fahrenheit en degrés Celsius (100)
=CONVERT_ADD(212;"F";"C")
Consultez l'aide en ligne OOo pour visualiser les différentes unité de mesures utilisables


Déterminer la valeur d'abscisse au croisement entre 2 courbes d'un graphique
=(ORDONNEE.ORIGINE(A4:A5;C4:C5)-ORDONNEE.ORIGINE(B4:B5;C4:C5))/(PENTE(B4:B5;C4:C5)-PENTE(A4:A5;C4:C5))
Les ordonnées Y sont dans les colonnes A et B
Les abscisses X sont dans la colonne C


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


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


Somme des cellules A5 dans les Feuilles 1à 3 : Utilisation des références 3D
=SOMME(Feuille1.A5:Feuille3.A5)


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


Réduire un nombre de 2 chiffres en faisant l'addition de ses 2 chiffres et le refaire jusqu'à ce que ce nombre n'ait plus qu'un seul chiffre.
Exemple 84 donne 8+4 =12 qu'il faut encore reduire à 1+2 =3.
Des solutions données par Phibou
=SI(MOD(A1;9)=0;9;MOD(A1;9))
=MOD(A1;9)+9*(MOD(A1;9)=0


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


Calculer la racine carré de la somme des différences de valeurs au carré ( auteur : Phibou )
=RACINE(SOMMEPROD((A1:A20-B1:B20)^2))






Les Dates et les Heures
********************

Calculer la différence en années entre deux dates
=ANNEES(A1;B1;1)


Déterminer si une année est bissextile : (1=VRAI , 0=FAUX)
=ESTBISSEXTILE(A1)


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


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 et la plage B1:B10 contient les valeurs à additionner
La Cellule D1 contient le numero du mois qui va servir à filtrer les données additionnées : 1= Janvier , 2 = Février ...etc ...


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


Ajouter ou oter 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 donne une date future, tandis qu'une valeur négative donne une date passée.
Pensez à appliquer un format date à la cellule contenant la formule.


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





Recherches et matrices
********************

Créer un lien hypertexte :
Afficher le texte "Cliquez sur forum-openoffice.org" dans la cellule et exécuter l'hyperlien http://www.forum-openoffice.org lorsque vous cliquez dessus.
=LIEN_HYPERTEXTE("http://www.";"Cliquez sur ")&"forum-openoffice.org"


Ouvrir l'explorateur Windows sur un répertoire spécifique
=LIEN_HYPERTEXTE("file:///C:/Documents and Settings/michel";"Ouvrez le répertoire")


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


Récupérer la donnée de la cellule A1 dans le classeur fermé "test.ods"
='file:///C:/documents and settings/michel/dossier/general/excel/test.ods'#$feuille1.A1

Un autre exemple en passant par des cellules intermédiaire
Saisissez en A1 le nom du répertoire
Saisissez en A2 le nom du classeur Calc
Saisissez en A3 le nom de la feuille
Saisissez en A4 le nom de la cellule
Puis utilisez cette formule
=INDIRECT("'"& CELLULE("CONTENTS";A1) & "\" & CELLULE("CONTENTS";A2) & "'#$"& CELLULE("CONTENTS";A3) &"."& CELLULE("CONTENTS";A4))


Regrouper des éléments par numéro d'équipe , sans espaces dans la colonne de résultat
Le lien sur le forum
http://user.services.openoffice.org/fr/ ... .php?t=733
Le fichier
http://oooforum.free.fr/cijoint/fichier ... 091301.ods

un autre exemple qui utilise Index Equiv entre 2 feuilles
Le fichier
http://oooforum.free.fr/cijoint/fichier ... 120355.ods





Informations
**********

Vérifier si la cellule D1 contient une formule ( Renvoie VRAI ou FAUX )
=ESTFORMULE(D1)


Renvoyer une adresse de cellule en tant que texte, en fonction des numéros de ligne et de colonne spécifiés.
Syntaxe : =ADRESSE(Ligne;Colonne;ABS;Feuille)
Ligne représente le numéro de ligne pour la référence à la cellule.
Colonne représente le numéro de colonne pour la référence à la cellule
ABS détermine le type de référence :
1: absolue ($A$1)
2 : ligne absolue, colonne relative (A$1)
3 : ligne relative, colonne absolue ($A1)
4 : relatif (A1)
Exemples
=ADRESSE(1;1;2;"Feuille2") ....renvoie Feuille2.A$1
=ADRESSE(1;4;4;"Feuille1") ....renvoie Feuil1.D1

Utiliser de la fonction ADRESSE pour afficher le contenu de la cellule D1
=INDIRECT(ADRESSE(1;4;4;"Feuille1"))


Afficher la formule contenue dans la cellule D12
=FORMULE(D12)
Si la cellule D12 ne contient pas de formule , l'erreur #N/A s'affiche


Afficher le type de donnée contenu dans une cellule
=TYPE(C2)
Les valeurs renvoyées :
1 = nombre
2 = texte
4 = valeur booléenne
8 = formule
16 = valeur d'erreur

La formule renvoie une erreur si la cellule est vide .Dans ce cas , pour ne pas afficher l'erreur vous pouvez utiliser
=SI(C2="";"";TYPE(C2))


Renvoie VRAI si la cellule A1 affiche une erreur ( autre que #NA )
=ESTERR(A1)


Renvoie VRAI si la cellule A1 affiche une erreur ( #NA compris )
=ESTERREUR(A1)


Vérifier la présence des valeurs VRAI ou FAUX dans la cellule A1
=ESTLOGIQUE(A1)


Déterminer si le contenu de la cellule A1est vide : Renvoie VRAI ou FAUX
=ESTVIDE(A1)


Vérifier si la cellule A1 contient la valeur d'erreur #N/A :Renvoie VRAI ou FAUX
=ESTNA(A1)


Afficher la valeur d'erreur #N/A dans la cellule
=NA()


La fonction N : Renvoyer 1 si la cellule A1 contient la valeur VRAI
=N(A1)
Remarques sur la fonction N :
=N(VRAI) renvoie 1
=N(FAUX) renvoie 0
=N(100) renvoie 100
=N("abc") renvoie 0


Afficher le nom de la feuille dans une cellule
http://www.mail-archive.com/users@fr.op ... 10676.html


Afficher l'index de la feuille contenant la cellule
=FEUILLE()





Logique
******

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 la cellule A1 est vide
=ESTVIDE(A1)
Vérifier si la cellule A1 est non vide
=NON(ESTVIDE(A1))




bonne journée
michel
OOo 2.1 & WinXP(sp2)
atd
Membre lOOyal
Membre lOOyal
Messages : 34
Inscription : 14 déc. 2005 10:51

Message par atd »

Bonsoir MichelXld

Un grand merci pour ce travail et :bravo:

Grâce à toi, je peux enfin faire vivre mes tableaux et les rendre plus efficaces.

Bien qu'au début ce ne soit pas si évident Calc me plaît de plus en plus mais là je crois que je vais pouvoir avancer plus vite.

Encore merci.
OOo 2.0.2 + Win SP2
Eternity
NOOuvel adepte
NOOuvel adepte
Messages : 20
Inscription : 27 oct. 2006 15:30
Localisation : France

Message par Eternity »

Oui, merci bcp.
Openoffice.org 2.0 - Windows XP Pro
MERCIER Philippe
Fraîchement OOthentifié
Messages : 1
Inscription : 27 févr. 2008 16:13
Localisation : Drôme

Mille et une fonctions de Michel

Message par MERCIER Philippe »

Je suis nouveau sur ce forum et c'est pour cela que la réponse n'est pas en relation avec la date d'émission, (1 an) mais ...

Merci Michel pour toutes ces fonctions.
C'est une vrai base de données.
Bonne journée.
open office 2.3.1 sous windows XP SP2
Avatar de l’utilisateur
Phengizy
Membre lOOyal
Membre lOOyal
Messages : 25
Inscription : 21 juil. 2007 00:05
Localisation : Arras

Re: Quelques exemples de fonctions dans Calc

Message par Phengizy »

:bravo: c'est ce que je cherchais
2.4 sous Linux Ubuntu 8.04
jdesc
NOOuvel adepte
NOOuvel adepte
Messages : 15
Inscription : 14 avr. 2008 18:28

Re: Quelques exemples de fonctions dans Calc

Message par jdesc »

:bravo: :bravo: :bravo: merci beaucoup de ce rappel que je m'empresse de mettre en mémoire dans mon dossier feuille de calcul.

MERCI encore
souvent au bord du gOOouffre !!!
openOffice.org 2.2
windows XP SP2
mozilla-Thunderbird
Melanie et Bertrand
Fraîchement OOthentifié
Messages : 7
Inscription : 28 mai 2009 12:46

Re: Quelques exemples de fonctions dans Calc

Message par Melanie et Bertrand »

Extraordinaire Michel,
Merci beaucoup.
OpenOffice 3 sous windows Vista
jxei148
Membre OOrganisé
Membre OOrganisé
Messages : 59
Inscription : 24 sept. 2009 17:13

Re: Quelques exemples de fonctions dans Calc

Message par jxei148 »

SUPER :bravo:
OpenOffice 3. 4.1 sous Windows 7 Edition familiale premium 64 bits
chastrusse
Fraîchement OOthentifié
Messages : 1
Inscription : 21 janv. 2010 10:25

Re: Quelques exemples de fonctions dans Calc

Message par chastrusse »

Bonjour,

Passionné par les tableurs mais peu doué, j'utilise beaucoup les réalisations et les conseils des "Maitres", un grand MERCI pour ces exemples de fonctions, une réponse à boiseur au sujet de la fonction "SI" limitée à 7 conditions, en l'associant à "OU" on dépasse les 18 conditions, =si(ou(
Bravo à vous tous,
open office 3.1.1 sous windows 7
Avatar de l’utilisateur
ventlibre
Membre OOrganisé
Membre OOrganisé
Messages : 64
Inscription : 12 nov. 2008 09:37

Re: Quelques exemples de fonctions dans Calc

Message par ventlibre »

bravo,
très beau travail
une vraie caverne d'Ali Baba
OpenOffice 4.1.1 sous WinXP, SP3
JPLUNUS
Membre lOOyal
Membre lOOyal
Messages : 32
Inscription : 07 sept. 2010 22:58

Re: Quelques exemples de fonctions dans Calc

Message par JPLUNUS »

Bonsoir,
Michel que voici un très beau travail bien utile pour les débutants dont je fais partie
Merci et bravo
:bravo:
LibreOffice 7.1.2.2(x64) sous Windows 10 famille 19041 et Java 1.8.0_291-b10
molopeglau
Membre lOOyal
Membre lOOyal
Messages : 38
Inscription : 03 sept. 2008 09:58

Re: Quelques exemples de fonctions dans Calc

Message par molopeglau »

:super: Un grand merci pour cette aide si précieuse pour nous les débutants... :super:
LibreOffice 3.5.4 sous debian, xp
Verrouillé