L'informatique à votre portée
L'informatique prêt-à-porter

Faites appel à moi pour vos formations
sur les dpts 81, 31, 82, 11 et 67 (Strasbourg)
Confiez moi vos développements Office

FacebookTwitterGoogle BookmarksLinkedin

Arrondir un nombre

Imprimer E-mail
12 mars 2014

Vous avez 3 fonctions pour arrondir vos nombres ARRONDI, ARRONDI.INF (arrondi en tendant vers 0), ARRONDI.SUP (arrondi en s'éloignant de 0). Elles fonctionnent toutes 3 avec les mêmes paramètres, le 1er correspond à la valeur à arrondir, le 2ème correspond au nombre de décimales que vous désirez.

A B C D
1 PI ARRONDI ARRONDI.INF ARRONDI.SUP
2 =PI() =ARRONDI(A2;2) =ARRONDI.INF (A2;0) =ARRONDI.SUP (A2;0)
Affiche 3,14159265358979 3,14 3 14

Mais si vous voulez l'arrondir à n'importe quelle valeur (par exemple au multiple de 100 le plus proche), vous ne pouvez utiliser directement ces fonctions mais procédez ainsi :

soit la valeur 2353 dans la cellule A2, la formule est alors : =ARRONDI(A2/100;0)*100

Il vous suffit de remplacer la valeur 100 par votre nombre.

Trouver la valeur la plus proche dans une plage non ordonnée

Imprimer E-mail
12 mars 2014

Soit une plage de valeurs non triées, si vous désirez connaître la valeur la plus proche suivez l'exemple ci-dessous.

A B C D
1
2 10 Valeur à rechercher

12,5

3 15 Valeur la plus proche =MIN(SI(ABS(A2:A10-C2)=MIN(ABS(A2:A10-C2));A2:A10)) 12
4 25 Position de cette valeur dans la plage =EQUIV(C3;A2:A10;0)

5

5 65 N° de ligne de cette valeur =INDEX(LIGNE(A2:A10);C4)

6

6 12
7 45
8 30
9 5
10 6

Remonter le dernier jour du mois précédent

Imprimer E-mail
12 mars 2014

Il correspond tout simplement au jour 0 du mois actuel.

A B C D
1 Date Mois Année Dernier jour du mois précédent
2 03/12/02 =MOIS(A2) =ANNEE(A2) =DATE(C2;B2;0)

Vous pouvez également écrire directement la formule suivante :
=DATE(ANNEE(A2);MOIS(A2);0)

Effectuer un calcul sur les heures

Imprimer E-mail
12 mars 2014

Tout d'abord, pour travailler correctement avec les dates et les heures sous Excel, vous devez utilisez :

  • / comme séparateur de dates et non un espace ou un tiret (ex. : 12/07/02 et non 12 07 02 ou 12-07-02)
  • : comme séparateur d'heures (ex. : 12:37:40).
    Les premiers chiffres correspondent toujours aux heures, si vous ne devez entrer que des minutes et secondes, vous devez entrez 0: au début de votre saisie. Ex : 0:30:10

Un histogramme dans des cellules

Imprimer E-mail
12 mars 2014

Plutôt que de créer un graphique de type histogramme, vous pouvez utiliser une fonction pour mettre dans une cellule une barre horizontale dont la taille dépend d'une valeur d'une cellule.

Tapez par exemple en A1 la valeur 10. En B1 tapez la formule suivante : =REPT("n";D119) et appliquez la police Wingdings à cette cellule B1. Vous aurez alors ce résultat.

NB.SI

Imprimer E-mail
12 mars 2014

Cette fonction compte le nombre de cellules correspondant à un critère dans une plage. Elle fonctionne à peu près comme la fonction précédente. Sa syntaxe est nb.si(plage;critère)

  • plage représente la plage qui va être comptée.
  • critère doit être sous la forme d'un nombre, d'un texte ou d'une expression de comparaison. Ce critère doit être au format texte. Reportez-vous à la fonction SOMME.SI pour connaître toutes ses possibilités.

SOMME.SI

Imprimer E-mail
12 mars 2014

Cette fonction permet d'effectuer la somme d'une plage selon un critère. Ce critère peut s'appliquer sur la plage que vous voulez sommer ou sur une autre plage avec la seule condition que les 2 plages soient de même taille. Sa syntaxe est SOMME.SI(plage;critère;somme_plage)

  • plage représente la plage sur laquelle le critère sera appliquée, elle peut-être la même que somme_plage

INDEX et EQUIV

Imprimer E-mail
12 mars 2014

La fonction précédente comme vous le constatez à ces limites : la colonne de recherche doit se trouver à gauche de la colonne de résultat. Pour palier à ceci vous pouvez utilisez conjointement 2 fonctions :

  • EQUIV, qui va vous donner l'emplacement d'une valeur dans une colonne ou une ligne,
  • INDEX, qui renvoie une donnée d'une liste (en ligne ou colonne) à partir de sa position dans la liste.

RECHERCHEV

Imprimer E-mail
12 mars 2014

Cette fonction permet de rechercher une valeur dans une liste de donnée. Cette recherche est effectuée dans la colonne de gauche de la liste, lorsqu'elle est trouvée, Excel se déplace vers la droite sur la même ligne et lit la valeur dans la colonne indiquée.
Les paramètres dont elle a besoin sont donc :

  • valeur_cherchée : la valeur recherchée
  • table_matrice : la plage dans laquelle la valeur est recherchée
  • no_index_col : l'index de la colonne renvoyant la donnée
  • valeur_proche: VRAI pour rechercher la valeur la plus proche dans la 1ère colonne de la plage et FAUX pour rechercher la valeur exacte.

ALEA

Imprimer E-mail
12 mars 2014

La fonction ALEA() renvoie des nombres décimaux choisis aléatoirement entre 0 et 1. Pour récupérer des nombres décimaux entre la valeur a et b vous devez utiliser la formule ALEA()*(b-a)+a. Pour obtenir uniquement des nombres entiers vous devez utiliser la fonction ENT ainsi : ENT(ALEA()*(b-a)+a).
Excel 2007 nous simplifie la vie en proposant la fonction ALEA.ENTRE.BORNES(). Cette fonction est également disponible dans les autres versions si vous avez activé la macro complémentaire Utilitaire d'analyse (dans le menu Outils, sélectionnez Macro complémentaires..., puis cochez Utilitaire d'analyse. Celle-ci demande uniquement les bornes maximale et minimale et renvoi directement une valeur entière. Ainsi, si vous avez besoin d'un nombre entier aléatoire entre 10 et 100 les formules à votre disposition sont :

Calculer des échéances

Imprimer E-mail
12 mars 2014

Soit une date inscrite dans la cellule B2. Calculons maintenant des échéances :

 FormuleExplication  
30 jours net =B2+30 1 est égale à 1 journée
30 jours fin de mois =FIN.MOIS(B2;1) le 2ème paramètre de FIN.MOIS permet d'indiquer le nombre de mois décalé, ici 1 mois pour 30 jours.
30 jours fin de mois le 15 =FIN.MOIS(B2;1)+15 Il suffit d'ajouter 15 à la formule précédente
60 jours fin de mois le 10 =FIN.MOIS(B2;2)+10 Pour 60 jours, nous nous décalons de 2 mois

Depuis la version d'Excel 2007, la fonction FIN.MOIS fait partie des fonctions. Par contre, jusque la version 2003, pour avoir cette fonction, il vous faut activer la macro complémentaire Utilitaire d'analyse. Dans le menu Outils/macros complémentaire, cocher Utilitaire d'analyse.

Les lundis

Imprimer E-mail
12 mars 2014

de la semaine (la date est en B2) :

=B2-MOD(B2-2;7)

MOD renvoi le reste d'une division
Vous pouvez également récupérer le samedi, dimanche, … qui précède en utilisant respectivement 0 ou 1 ou … (jusque 6) à la place du 2 dans la formule.

le 1er d'une année (indiquée en C15) :

=DATE(C15;1;1)-MOD(DATE(C15;1;1)-2;7)+SI(ANNEE(DATE(C15;1;1)-MOD(DATE(C15;1;1)-2;7))=C15;0;7)

le 1er du mois (indiquée en C15) :

=DATE(C15;1;1)-MOD(DATE(C15;1;1)-2;7)+SI(ANNEE(DATE(C15;1;1)-MOD(DATE(C15;1;1)-2;7))=C15;0;7) c

Nombre de jours du mois d'une date

Imprimer E-mail
12 mars 2014

=JOUR(FIN.MOIS(B2;0))

FIN.MOIS avec le paramètre 0 permet de récupérer le dernier jour du mois de la date indiquée.
La fonction JOUR renvoi le n° du jour d'une date. Ici nous somme le dernier jour du mois donc le N° du jour correspond au nombre de jours du mois.

Le n° de la semaine d'une date

Imprimer E-mail
12 mars 2014

=NO.SEMAINE(B2;2)
Le paramètre 2 indique que le 1er jour de la semaine est le lundi (1 pour le dimanche).
Attention ! la fonction NO.SEMAINE d’Excel 2010 renvoi le n° de la semaine selon la règle suivante : la semaine 1 est la semaine contenant le 1er janvier de l’année.

Hors en France, la règle (Norme ISO) indique que :

  1. la semaine commence le lundi ;
  2. les jours de chaque semaine sont numérotés de 1 pour le lundi, à 7 pour le dimanche ;
  3. la semaine 1 est celle qui contient le premier jeudi de l'année.

Pour respecter cette règle, utilisez la formule suivante :
=ENT(MOD(ENT((B2-2)/7)+0,6;52+5/28))+1

Savoir si une année est bissextile

Imprimer E-mail
12 mars 2014

=JOUR(DATE(ANNEE(B2);3;0))=29

ANNEE(B2) renvoi l’année de la date en B2

DATE(année;mois;jour) renvoi une date à partir d’un n° d’année, d’un n° de mois et d’un n° du jour.

DATE(ANNEE(B2);3;0) renvoi le jour 0 du mois de mars de l’année de la date en B2. Le jour 0 correspond au  dernier jour du mois précédent, ici le dernier jour du mois de février.
Il ne reste plus qu’à vérifier si le jour de cette date est égale à 29, dans ce cas l’année est bissextile.