ASTUCES CALC
Accès direct : Menu astuces, page 1, page 2, page 3, page 4, page 5,
Assemblage / désassemblage d'une formule.
Ici nous décomposons une formule pour calculer le dernier jour précis d'un mois. Nous appliquons =FIN.MOIS(DATE(année; mois; 1);0) - JOURSEM(FIN.MOIS(DATE(année; mois; 1);0);2) + JourCherche - SI(JOURSEM(FIN.MOIS(DATE(année; mois; 1); 0); 2) < JourCherche; 7; 0)
Année est en A1, Mois en B1 et JourCherche en C1.
Ce petit schéma illustre les différents assemblages de cette formule.
Explications complémentaires.
| Formules | Commentaires |
| DATE(année; mois; 1) | On cherche la date du 1er jour du mois de l'année |
| FIN.MOIS(DATE(année; mois; 1);0) | On complète la formule précédente en cherchant la date de fin de mois. |
| JOURSEM(FIN.MOIS(DATE(année; mois; 1);0);2) | On cherche le jour de la semaine de cette fin de mois. Ce jour sera numéroté de 1 (lundi) à 7 (dimanche). |
| SI(JOURSEM(FIN.MOIS(DATE(année; mois; 1); 0); 2) < JourCherche; 7; 0) | Si le résultat de JOURSEM() est inférieur au jour recherché, on applique la valeur 7 (qui va être retranchée, lisez la formule), sinon, la valeur 0(sans effet) |
Quelques exemples de calculs
Les méthodes sont variées, vous pourrez en trouver d'autres pour un résultat identique. Ces exemples peuvent rebuter un débutant. Il est cependant possible de les appliquer sans pour autant les comprendre (dommage).
Nota. Dans les formules citées, années, mois, JourCherche, sont des valeurs qui peuvent être inscrites dans des cellules. Par exemple, =DATE(année;1;1) peut-être assimilé à =DATE(A1;1;1), si la valeur année est dans la cellule A1.
Certaines formules présentées ici proviennent de discussions sur des forums. Les formules matricielles sont entre les caractères { et }. Il ne faut pas les inscrire. Ces caractères s'inscrivent automatiquement en validant la formule, non pas avec la touche Entrée, mais avec la combinaison Ctrl + Maj + Entrée.
Plage représente une zone de cellules, comme par exemple A1:A10, la plage peut-être nommée (Ctrl+F3).
Cellule représente une cellule, comme par exemple A1.
OOo2 Formules applicables pour OOo2 uniquement.
| On cherche | Formules | Commentaires | ||
| Formules avec les dates | ||||
| Dernier jour du mois en cours, date entière | =FIN.MOIS(AUJOURDHUI();0) | formater la cellule suivant besoins. | ||
| Dernier jour du mois en cours, jour de la semaine | =JOURSEM(FIN.MOIS(AUJOURDHUI();0);2) | semaine du lundi au dimanche | ||
| Idem, avec affichage du jour de la semaine | =CHOISIR(JOURSEM(DATE( ANNEE (AUJOURDHUI()); MOIS (AUJOURDHUI()); JOURSDANSMOIS (AUJOURDHUI())); 2); "lundi"; "mardi"; "mercredi"; "jeudi"; "vendredi"; "samedi"; "dimanche") | semaine du lundi au dimanche | ||
| Dernier lundi de l'année précédente | =DATE(année;1;1)-JOURSEM(DATE(année;1;1);0) | |||
| Premier lundi de l'année | =DATE(année;1;1) - JOURSEM(DATE(année;1;1);0) + SI(JOURSEM(DATE(année;1;1);0)>0;7;0) | |||
| Premier lundi du mois | =DATE(année;mois;1)-JOURSEM(DATE(année;mois;1);0) + SI (JOURSEM (DATE (année; mois; 1); 0)>0; 7; 0) | |||
| Dernier lundi du mois | =DATE(année; mois; JOURSDANSMOIS(DATE(année; mois; 1))) - JOURSEM(DATE(année; mois; JOURSDANSMOIS (DATE(année; mois; 1)));0) | |||
| Dernier jour précis d'un mois | =FIN.MOIS(DATE(année; mois; 1);0) - JOURSEM(FIN.MOIS (DATE(année; mois; 1);0);2) + JourCherche - SI(JOURSEM (FIN.MOIS (DATE (année; mois; 1); 0); 2) < JourCherche; 7; 0) | JourCherche = jour de la semaine, de lundi (1) à dimanche (7) | ||
| Premier jour précis d'un mois | = (1er jour du mois) + (JourCherche - JOURSEM(1er jour du mois)) + (JOURSEM (1er jour du mois)> JourCherche) * 7 | 1er jour du mois = date entière. JourCherche de 1 (dimanche) à 7 (samedi). Retourne la date correspondante aux critères de date et du jour cherché dans la semaine. | ||
| Premier lundi du mois | = ("01/09/2006") + (2 - JOURSEM("01/09/2006")) + (JOURSEM("01/09/2006")>2) * 7 | retourne 1er lundi de septembre 2006 | ||
| =(A1) + (2 - JOURSEM(A1)) + (JOURSEM(A1)>2)*7 | A1 contient la date, retourne 1er lundi | |||
| Premier dimanche du mois | = ("01/09/2006") + (1 - JOURSEM("01/09/2006")) + (JOURSEM("01/09/2006")>1) * 7 | retourne le 1er dimanche de septembre 2006 (3/09/2006) | ||
| Dernier jour précis d'un mois | =(MOIS.DECALER(1er jour du mois; 1)) + (JourCherche - JOURSEM(MOIS.DECALER (1er jour du mois; 1))) + (JOURSEM(MOIS.DECALER(1er jour du mois; 1))>JourCherche) *7 - 7 | 1er jour du mois = date entière. JourCherche de 1 (dimanche) à 7 (samedi). Retourne la date correspondante aux critères de date et du jour cherché dans la semaine. | ||
| dernier vendredi du mois | =(MOIS.DECALER("01/09/2006"; 1)) + (6- JOURSEM(MOIS.DECALER("01/09/2006"; 1))) + (JOURSEM (MOIS.DECALER ("01/09/2006"; 1))>6) *7 - 7 | retourne le dernier vendredi de septembre 2006 (29/09/2006) | ||
| =(MOIS.DECALER(A1; 1)) + (6- JOURSEM(MOIS.DECALER(A1; 1))) + (JOURSEM (MOIS.DECALER (A1; 1))>6) *7 - 7 | Retourne le dernier vendredi de la date en A1 | |||
| Numéro du jour dans l'année | =AUJOURDHUI()-DATE(ANNEE(AUJOURDHUI());1;0) | |||
| Premier jour de la semaine | =AUJOURDHUI()- JOURSEM(AUJOURDHUI(); 2) + 1 | JourSemaine de 0 à 6 (dimanche au samedi) | ||
| Dernier jour dans la semaine précédent la date | =AUJOURDHUI() - MOD(AUJOURDHUI() - JourSemaine ;7) | |||
| Dernier jour dans la semaine suivant la date | 7=AUJOURDHUI() - MOD(AUJOURDHUI() - JourSemaine;7)+7 | |||
| Numéro de trimestre | =PLAFOND(MOIS(date entière)/3; 1) | Ex: =PLAFOND (MOIS ("10/5/2005")/3; 1) | ||
| Date anniversaire dans un an | =MOIS.DECALER(AUJOURDHUI(); 12) | La différence des 2 types de formules se fait sur les années bissextiles. Cas particulier : si cellule contient 29/02/2004 (année bissextile) le retour sera suivant cas: 28/02/2005 ou 01/03/2005. | ||
| =MOIS.DECALER(cellule;12) | ||||
| =DATE(ANNEE(AUJOURDHUI())+1; MOIS(AUJOURDHUI()); JOUR(AUJOURDHUI())) | ||||
| =DATE(ANNEE(cellule)+1; MOIS(cellule); JOUR(cellule)) | ||||
| Texte et date dans une cellule | ="Dossier du " & TEXTE(AUJOURDHUI(); "jj/mm/aaaa") | Voir détails possibilités TEXTE | ||
| ="Dossier du " & TEXTE(cellule; "jjjj jj mmmm aaaa") | ||||
| Calcul âge | =(ANNEES(A1; MAINTENANT();0)) & " ans " & (NB.MOIS(A1; MAINTENANT();0) - (ANNEES(A1; MAINTENANT(); 0))*12) & " mois " & (SI(JOUR(MAINTENANT()) - JOUR(A1) >=0; JOUR(MAINTENANT()) - JOUR(A1); JOUR(MAINTENANT()) + (JOURSDANSMOIS(MOIS.DECALER (MAINTENANT(); -1)) - JOUR(A1)))) & " jours" | Date de naissance en A1, à comparer à la date du jour | ||
| Calcul âge (méthode détaillée). Voir aussi fonction additionnelle DATE_DIF |
en C1 (années) | =(ANNEES(A1;B1;0)) | Date de naissance en A1 et date à comparer en B1 | |
| en D1 (mois) | =(NB.MOIS(A1;B1;0) - (ANNEES(A1;B1;0))*12) | |||
| en E1 (jours) | =SI((JOUR(B1)-JOUR(A1))<0; (SI((JOUR(B1)-JOUR(A1))<0; JOUR(B1); (JOUR(B1) - JOUR(A1)))) + ((JOURSDANSMOIS (DATE (ANNEE(B1); MOIS(B1)-1; 1))) - JOUR(A1)); (SI((JOUR(B1)-JOUR(A1))<0; JOUR(B1); (JOUR(B1) - JOUR(A1))))) | |||
| Comptages de dates (jour, mois, année) | = SOMMEPROD((JOUR(plage) = JourCherche) * 1) | On cherche un jour précis (JourCherche) dans une plage de cellule. Le 3ème exemple est une fonction matricielle Ctrl + Maj + Entrée | ||
| = SOMMEPROD((JOUR(A1:A10) = 15) * 1) | ||||
| {=SOMME(JOUR(A1:A10)= 15)} | ||||
| = SOMMEPROD((MOIS(plage) = MoisCherche) * 1) | On cherche un mois précis (MoisCherche) dans une plage de cellule. Le 3ème exemple est une fonction matricielle Ctrl + Maj + Entrée | |||
| = SOMMEPROD((MOIS(A1:A10) = 10) * 1) | ||||
| {=SOMME(MOIS(A1:A10) = 10)} | ||||
| = SOMMEPROD((ANNEE(plage) = AnCherche) * 1) | On cherche une année précise (AnCherche) dans une plage de cellule. Le 3ème exemple est une fonction matricielle Ctrl + Maj + Entrée. L'année à chercher est sur 4 chiffres, même si l'affichage est 2 chiffres. | |||
| = SOMMEPROD((ANNEE(A1:A10) = 2004) * 1) | ||||
| {=SOMME(ANNEE(A1:A10) = 2004)} | ||||
| Formules avec les heures | ||||
| Salaire pour un nombre d'heures | =((heures : minutes ) * 24) * montant horaire) | Ex: =(10:30 * 24 * 50) Une conversion est automatiquement effectuée par Calc: =(0,4375 * 24* 50) | ||
| Compter des heures de nuit | = 1 - heure début + heure de fin | Pour des horaires commençant avant minuit et finissant après minuit. Explications ici. | ||
| =SI(A1>B1; 1-A1+B1; B1-A1) | Exemple avec heure début en A1 et fin en A2, utilisable avec les horaires jours et nuit. | |||
| Arrondir des heures | =ARRONDI.AU.MULTIPLE(heure; TEMPS(arrondi heures; arrondi minutes; arrondi secondes)) | Valeurs d'arrondis recommandées inférieures à 60. Une valeur 60 ou supérieure reporte sur l'unité supérieure (ex de secondes vers minutes). La correction est automatique. | ||
| =ARRONDI.AU.MULTIPLE(A1; TEMPS(0; 5 ; 0)) | Heure en A1, arrondi à 5 minutes. | |||
| =ARRONDI.AU.MULTIPLE(15:23; TEMPS(0; 15 ; 0)) | Arrondi à 15mn, retourne 15:30:00 (suivant format) | |||
| Arrondir heure supérieure | =PLAFOND(heure; arrondi heure) | |||
| =PLAFOND("14:12:23"; "00:10:00") | Arrondi à 10mn, retourne 14:20:00 (suivant format) | |||
| Arrondir heure inférieure | =PLANCHER(heure; arrondi heure) | |||
| =PLANCHER("14:12:23"; "00:10:00") | Arrondi à 10mn, retourne 14:10:00 (suivant format) | |||
| Conversion heures en valeurs décimales | = Temps / 1: | N'oubliez pas les 2 points (:) après le 1. Calc convertit les données en 0,0625 / 0,0416666666666667 et retourne 1,5 |
||
| = 1:30 / 1: | ||||
| Conversion valeurs décimales en heures | = valeur décimale * 1: | N'oubliez pas les 2 points (:) après le 1. Calc transforme les données en 1,5 * 0,0416666666666667 et retourne 1:30 (suivant format) |
||
| = 1,5 * 1: | ||||
| Calcul vitesse | =Distance / (temps * 1440) * 60 | Dans le 2ème exemple, 1:20 (1heure, 20 minutes) est automatiquement transformé en 0,0555555555555556 3ème exemple, Distance en A1 et Temps en B1 |
||
| =100 / (1:20 * 1440) * 60 | ||||
| =A1/(B1*1440)*60 | ||||
| Formules comptage de caractères, textes, positions, recherches | ||||
| Nombre de caractères dans une cellule | =NBCAR(A1) - NBCAR(SUBSTITUE(A1;"a";"")) | Dans cet exemple on cherche la lettre "a" en minuscule | ||
| Transformer des texte de valeurs avec espaces en valeur | =CNUM (SUBSTITUE (A1;" "; "")) | Pour une valeur sous forme de texte en A1. SUBSTITUE remplace les espaces par des chaînes vides (Attention : entre guillemets, un espace au premier, rien au second). CNUM transforme en valeur. | ||
| Nombre de lignes dans une plage | =lignes(plage) | Ex: =lignes(A1:A10) | ||
| Avant-dernière valeur d'une plage de données | =PETITE.VALEUR(plage;2) | 2 pour 2ème petite valeur. | ||
| Où est l'avant-dernière valeur | =EQUIV(PETITE.VALEUR(plage;2); plage; 0) | Position de la première valeur lue dans le tableau | ||
| Nombre avant-dernière valeur | =NB.SI(plage;PETITE.VALEUR(plage;2)) | |||
| Nombre de valeurs | =NB.SI(plage; valeur) | |||
| Nombre de textes | =NB.SI(plage; "texte") | |||
| Nombre de données inférieures à valeur | {=SOMME((plage) < valeur)} | Formule matricielle (Ctrl + Maj + Entrée) | ||
| Nombre de valeurs entre mini et maxi | {=SOMME((plage>10) * (plage<15))} | Formule matricielle (dans le 1er cas). Dans ces exemples, compte le nombre de valeurs supérieures à 10 et inférieures à 15 dans plage. Résultat identique dans les 2 cas. | ||
| =NB.SI(plage; "<15") - NB.SI(plage; "<=10") | ||||
| OOo2 Nombre de valeurs différentes dans une plage de cellules | {=SOMME(SI(plage<>""; 1/NB.SI(plage; plage)))} | Formule matricielle | ||
| Nombre de mots dans une plage de cellules | {=SOMME(plage = "test")} | Formule matricielle (Ctrl + Maj + Entrée). Retourne le nombre de fois où on trouve le mot test dans la plage | ||
| Faire une moyenne sans tenir compte de valeur précise | =SOMME(A1:A10) / NB.SI(A1:A10;"<>0") | Calcul la moyenne des éléments dans la plage A1:A10, sans tenir compte de valeur 0 (pas cellule vide). Dans les 2ème et 3ème exemples, la valeur à exclure est en B1. La seconde est matricielle (Ctrl+ Maj+ Entrée) | ||
| {=MOYENNE(SI(A1:A10<>B1;A1:A10;""))} OOo2.x | ||||
| = SI(ESTVIDE(B1); SOMME(A1:A10) / NB.SI(A1:A10;"<>0"); (SOMME(A1:A10) - (NB.SI(A1:A10; B1)*B1)) / NBVAL(A1:A10)) | ||||
| Moyenne pondérée | =SOMMEPROD(Plage_Valeurs; Plage_Coefficients) / NBVAL (Plage_Coefficients) | Permet de faire un moyenne en tenant compte de coefficients différents. | ||
| Numéroter les doublons | =SI(ESTVIDE(A1);""; NB.SI($A$1:$A$10; A1) - NB.SI($A2:$A$10;A1)) | Affecte un n° d'ordre aux données identiques dans la plage de données A1:B10. Formule à copier en B1 et recopier jusqu'en ligne 10 | ||
| Compter les cellules vides | {=SOMME(ESTVIDE(A1:A10))} | Formules matricielles (Ctrl + Maj + Entrée) | ||
| {=SOMME(ESTVIDE(plage))} | ||||
| Compter les cellules non vides | {=SOMME(NON(ESTVIDE(A1:A10)))} | |||
| {=SOMME(NON(ESTVIDE(plage)))} | ||||
| Dernière cellule d'une colonne contenant un mot précis | {=MAX(SI(A1:A10="test";LIGNE(A1:A10);0))} | Formules matricielles (Ctrl + Maj + Entrée). Dans le 1er exemple on cherche le mot test dans la plage A1:A10 | ||
| {=MAX(SI(plage="test";LIGNE(plage);0))} | ||||
| Additionner les cellules identiques de toutes les feuilles | =SOMME(Feuille1.A1:Feuille3.A1) | Exemple pour un classeur de 3 feuilles. Les ajouts, suppressions, modifications de noms de feuille, n'entrainent pas d'erreur. | ||
| Contourner SOMME.SI() avec une cellule vide | {= SOMME((plage données ="") * plage somme)} | = SOMME.SI(A1:A10; ""; B1:B10) ne fonctionne pas. La formule ci-contre permet de contourner l'obstacle. Formule matricielle (Ctrl+ Maj+ Entrée) | ||
| {= SOMME((A1:A10="") * B1:B10)} | ||||
| Additionner avec des cellules en erreur | =SOMME.SI(plage; "<>0") | Addition d'une plage de cellules sans erreur, même si certaines cellules contiennent #NA ou ERR suivi du numéro d'erreur. | ||
| OOo2 Nombre de texte présents dans une plage de cellules, y compris en cas de répétitions dans les cellules. | =SOMMEPROD(NBCAR(plage)- NBCAR(SUBSTITUE(plage; texte; ""))*1) / longueur texte | On compte le nombre d'échanges possibles | ||
| =SOMMEPROD(NBCAR(A1:10)- NBCAR(SUBSTITUE(A1:10; "abc"; ""))*1) /3 | Dans cet exemple, on compte le nombre de abc, et on divise par 3, longueur de la chaine abc | |||
| =SOMMEPROD(NBCAR(A1:A10)- NBCAR(SUBSTITUE(A1:A10; B1;""))*1) /NBCAR(B1) | Exemple plus général, avec données dans la plage A1:A10 et la chaine à comparer en B1 | |||
| comptage de caractères spéciaux (NB.SI inefficace) | {=SOMME(ESTNUM(TROUVE("/"; plage)))} | Compte le nombre de caractères / dans la plage. Formule matricielle (Ctrl+ Maj+ Entrée) | ||
| {=SOMME(ESTNUM(TROUVE("*"; A1:B10)))} | Compte le nombre de caractères / dans A1:B10. Formule matricielle (Ctrl+ Maj+ Entrée) | |||
| Compter le nombre de décimales | =SI(ESTERREUR(TROUVE(","; A1)); 0; (NBCAR(A1) - TROUVE(","; A1))) | Compte le nombre de décimales d'une valeur ou d'un résultat de formule, se trouvant dans la cellule A1. LibO3 & OOo3-> | ||
| Formules nom fichier, répertoire | ||||
| Nom de feuille | =DROITE (CELLULE ("filename"); NBCAR (CELLULE("filename")) - CHERCHE("$"; CELLULE ("filename"); 1)) | Si changement de nom de feuille, mise à jour par Ctrl + Maj + F9 | ||
| =DROITE(CELLULE("filename"); NBCAR (CELLULE("filename")) -1 - CHERCHE("#"; CELLULE ("filename"))) | ||||
| =DROITE(CELLULE("filename"); NBCAR (CELLULE("filename")) -2 - CHERCHE ("'"; CELLULE ("filename"); 2)) | ||||
| =STXT(CELLULE("filename"); TROUVE("$"; CELLULE ("filename";A2)) +1; 100) | ||||
| =SI(1;STXT(CELLULE("FILENAME");CHERCHE("$";CELLULE("FILENAME");1)+1;256);MAINTENANT()) | Mise à jour automatique | |||
| Nom répertoire, fichier et feuille | =SI(GAUCHE (CELLULE ("filename"); 5)="'file"; SUBSTITUE (DROITE (CELLULE("filename"); NBCAR (CELLULE ("filename")) -9); "%20"; " "); "") | Uniquement si fichier enregistré. | Si changement de nom de feuille, mise à jour par Ctrl + Maj + F9 | |
| Nom répertoire seul | =GAUCHE(A1; CHERCHE(CAR(5); SUBSTITUE(A1; "/"; CAR(5); NBCAR(A1) - NBCAR(SUBSTITUE(A1; "/"; ""))))) | Extraction à partir de la formule précédente. Exemple pour formule en A1. Utilisation d'un caractère peut usité (5) pour les recherches. | ||
| Nom fichier seul | =GAUCHE(DROITE(A1; NBCAR(A1) - CHERCHE(CAR(5); SUBSTITUE(A1; "/"; CAR(5); NBCAR(A1) - NBCAR(SUBSTITUE(A1; "/"; ""))))); CHERCHE("'"; DROITE(A1; NBCAR(A1) - CHERCHE(CAR(5); SUBSTITUE(A1; "/"; CAR(5); NBCAR(A1) - NBCAR(SUBSTITUE(A1; "/"; "")))))) -1) | |||
Vous avez d'autres formules utiles. Contactez-moi.
Ce site n'est plus mis à jour. Merci aux nombreux visiteurs depuis 2003. Consultez le Wiki LibreOffice. |
|||||
|
Le tableur Calc par l'exemple : christianwtd@free.fr
|
. | Site optimisé pour Firefox
|
|||
Dernière modification : 28/02/2011 à 16h33
|
| Compteur non certifié   |
Déjà plusieurs visites sur ce site depuis quelque temps (au moins vous et moi)
