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

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.

Nommer une plage de cellule

Imprimer E-mail
12 mars 2014

Pour travailler avec une meilleure efficacité, il est souvent utile de nommer une plage de cellule. Par ex : B3*Taux_TVA est plus explicite que B3*A1. Pour nommer une cellule ou une plage il existe plusieurs méthodes, mais préférez à toutes la méthode suivante :

  • Sélectionnez la plage de cellule.

  • Cliquez dans la zone Nom de la barre de formule.

  • Tapez un nom explicite.

  • Validez avec la touche Entrée.

Pour le nom de la plage n'utilisez que les lettres, chiffres (jamais comme 1er caractère) et le caractère de soulignement (_), ne mettez aucun espace. Une bonne méthode pour rendre lisible le nom est d'utiliser soit, le caractère _ , soit, les majuscules, exemple : pour nommer une plage représentant le taux d'augmentation, tapez TauxAugmentation ou taux_augmentation ou Taux_Augmentation.

Pour visualiser les différentes plages nommées, Insertion/Nom/Définir, la boîte de dialogue suivante s'affiche :

  • Dans la zone Noms dans le classeur, vous pouvez inscrire un nom (suivre les règles de validation indiquée ci-dessus) ou visualiser le nom que vous avez sélectionné dans la liste.

  • La zone Fait référence à, vous indique l'adresse de la plage, vous pouvez alors la modifier, soit, directement dans la zone, soit, en utilisant le bouton et en sélectionnant la plage dans le classeur.

  • Le bouton Ajouter permet d'ajouter une plage nommée, le nom doit se trouver dans la liste Noms dans le classeur et l'adresse dans Fait référence à.

  • Le bouton Supprimer supprime le nom sélectionné dans la liste.

Référence 3D

Imprimer E-mail
12 mars 2014

La méthode est simple et utilise le collage spécial.

  • Sélectionnez la plage de cellules source, celle à laquelle vous voulez faire référence dans une autre feuille.
  • Copiez la plage.
  • Sélectionnez la cellule supérieure gauche de la plage de destination.
  • Clic-droit/Collage spécial.
  • Sélectionnez Coller Tout.
  • Cliquez sur le bouton Coller avec liaison.
Vous pouvez constater que les cellules contiennent une formule de type =Adresse d'une cellule.