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.
| 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) |
| 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) |
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))))) |
| |
|
|
| |
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. |
| =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) |
| |
|
|
| |
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)) |
| 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. |
| = 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 |
| |
|
|
| |
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) |
100 représente le nombre de caractères maxi du nom de feuille. |
| Nom répertoire et feuille |
=SI(GAUCHE (CELLULE ("filename"); 5)="'file";
SUBSTITUE (DROITE (CELLULE("filename"); NBCAR (CELLULE ("filename"))
-9); "%20"; " ");
"") |
Uniquement si fichier enregistré. |
