Avez-vous déjà utilisé la fonction ROMAIN() ?

Transformer vos nombres ou dates en chiffre Romain

Il vous est certainement déjà arrivé d'avoir eu besoin d'écrire des chiffres ou des dates en chiffre romain, et vous ne vous rappeler pas comment les traduire. Et bien savez-vous qu'il existe une fonction dans Excel qui permet de réaliser cette traduction. 

La fonction à utiliser, vous l'aurez déjà deviné est ROMAIN, cette dernière permet de convertir un nombre arabe en nombre romain, sous forme de texte.

La syntaxe est la suivante : ROMAIN(nombre, [type]) 

• Nombre : Représente le chiffre arabe que vous souhaitez convertir
• Type : Représente un argument déterminant le type de chiffres romains que vous souhaitez obtenir
  (Il existe plusieurs types d'écriture allant de la valeur 0 à 4; 0 étant l'écriture classique) 

1- Exemple de traduction d'un nombre :

2- Exemple de traduction d'une date :

Et voilà, alors allez-vous désormais utiliser la fonction ROMAIN ?

Réaliser un tri alphabétique avec une formule

Utiliser une formule pour réaliser un tri alphabétique d'une liste

Aujourd'hui je vais vous montrer comment réaliser un tri alphabétique d'une liste sans passer par l'outil Tri d'Excel mais en utilisant une formule. 

Pour illustrer cette formule, prenons l'exemple ci-dessous : 

Vous l'avez compris, l'objectif sera de réaliser un tri alphabétique de cette liste en utilisant un formule

Pour réaliser ce travail nous aurons besoin des fonctions : 

- NB.SI()

- INDEX()

- EQUIV()

1) Nommer la plage de cellule à trier : 

- Sélectionner la plage de cellule : B4:B11

- Onglet Formules/Groupe Noms Définis/Définir un nom/

- Compléter la boite de dialogue comme suit : 

2) Etape n° 1 - Calculer le nombre de ligne : 

- Se positionner sur la cellule C4

- Taper la formule : =NB.SI(Plage;"<="&$B4)

- Copier cette formule jusqu'en C11

Nous avons le résultat suivant : 

3) Etape n° 2 - Afficher la liste triée alphabétiquement : 

- Se positionner sur la cellule D4

- Taper la formule suivante : =INDEX(Plage;EQUIV(LIGNE()-3;$C$4:$C$11;0))

- Copier cette formule jusqu'en D11

Nous avons le résultat suivant :

Notre liste a été triée alphabétiquement, si vous changer un nom de votre plage, le recalcul se fera automatiquement.

4) Astuce   : 

Savez-vous que nous aurions pu réaliser la même chose en une seule fois sans passer par les 2 étapes ? 

- Sélectionner la plage de cellules : E4:E11

- Taper la formule  : = INDEX(Plage;EQUIV(LIGNE()-3;NB.SI(Plage;"<="&Plage);0))

- Valider la formule avec : Ctrl + Maj + Entrée

Votre liste est désormais triée : 

Comment utiliser la fonction FREQUENCE()

Avez-vous déjà utiliser la fonction FREQUENCE() ?

Cette fonction est méconnue et pourtant elle apporte des fonctionnalités permettant de réaliser des statistiques dans une table de données. 

La fonction FREQUENCE permet de calculer la fréquence d’apparition des valeurs dans une plage de valeurs, puis renvoie des nombres sous forme de matrice verticale.

Pour illustrer l'utilisation de cette fonction, prenons l'exemple suivant : 

Notre professeur "statisticien" souhaite connaître le nombre de notes comprise entre les intervalles suivantes : 

• Notes inférieures ou égales à 10 
• Notes comprises entre 11 et 14
• Notes comprises entre 15 et 16
• Notes supérieures à 16 

1) Créer la matrice d'intervalle : 

• Taper votre matrice comme ceci : 

2) Calculer la fréquence :

• Sélectionner la plage de cellules :  O3:O6
• Taper la formule :  =FREQUENCE(L3:L14;N3:N6)
• Valider en appuyant sur les touches :  Ctrl + Maj + Entrée
  (Permet de réaliser un calcul matriciel)

3) Nous obtenons le résultat suivant :

Interprétation :

3 personnes ont une notre inférieure ou égale à 10

5 personnes ont une note supérieure à 10 et inférieure ou égale à 14

3 personnes ont une note supérieure à 14 et inférieure ou égale à 16

1 personne à une note supérieure à 16

Afficher la plus petite et la plus grande valeurs d'une liste de données

Comment afficher les valeurs extrêmes d'un tableau

Il est parfois utile de retrouver et d'afficher les valeurs extrêmes d'une liste de données d'un tableau. Excel possède des fonctions, souvent méconnues, pour réaliser ce type affichage. 

Prenons l'exemple ci-dessous regroupant dans un tableau les notes de plusieurs épreuves réalisées par des élèves : 

Il serait intéressant pour l'enseignant de réaliser des statistiques et afficher par catégorie d'épreuves les notes les plus faibles et les plus grandes. 

Nous allons utiliser les deux fonctions suivantes : 

• PETITE.VALEUR()
• GRANDE.VALEUR()

1) Affichage de la note la plus faible pour chaque épreuves :

• Se positionner sur la cellule C16 et taper : =PETITE.VALEUR(C3:C14;1)
• Copier la formule de D16 à F16

2) Affichage de la note la plus haute pour chaque épreuves :

• Se positionner sur la cellule C18 et taper : =GRANDE.VALEUR(C3:C14;1)
• Copier la formule de D18 à F18

Nous obtenons le résultat suivant : 

Créer un diagramme de Gantt avec Excel

Comment réaliser une représentation de type Diagramme de Gantt

Il est parfois utile de représenter graphiquement l'avancement d'un projet par rapport à plusieurs tâches à réaliser.  Si vous ne possédez pas de logiciel spécifique de gestion de projet (tel que MS Project), et bien il est possible de réaliser ce type de diagramme avec Excel. 

Pour illustrer notre explication, prenons les données suivantes : 

Etape 1 :  Création du graphique : 

• Sélectionner la plage de cellules : B16:E24
• Onglet Insertion/Groupe Graphiques/Barres/Barres empilées

Nous obtenons le graphique ci-dessous : 

Etape 2 :  Modification du graphique : 

• Dans le graphique, cliquer sur la série de données concernant la Date de début (couleur Bleue) 
• Dans les Outils de graphique, Onglet Mise en forme/Groupe Styles de formes/Dans Remplissage de forme, sélectionner l'option Aucun remplissage
• Cliquer sur l'axe vertical représentant les Tâches pour le sélectionner puis faite un clic droit et sélectionner l'option Mise en forme de l'axe...
• Dans Options d'axe, cocher sur Abscisses en ordre inverse
• Cliquer sur l'axe horizontal représentant les dates pour le sélectionner puis faite un clic droit et sélectionner l'option Mise en forme de l'axe...
• Compléter la boite de dialogue comme suit : 

Explications : 

Dans le champ Minimum : 41883 correspond à la valeur de la date du 01/09/2014

Dans le champ Maximum : 42004 correspond à la valeur de la date du 31/12/2014

Dans le champ Unité principale : 15 correspond à l'échelle de 15 jours

Dans le champ Unité secondaire : 1 correspond à l'échelle d'un jours

Astuce : Pour connaître la valeur d'une date, il suffit de taper la date souhaitée dans une cellule et de la transformer en format de type nombre 

• Toujours dans la même boite de dialogue, sélectionner l'option Nombre et choisir le format Date : jj/m
• Dans la légende, sélectionner la valeur Date début et supprimer là en tapant sur la touche Suppr 
• Positionner la légende en dessous du graphique : dans les Outils de graphique/Onglet Disposition/Légende/Sélectionner l'option Afficher la légende en bas

Nous obtenons le résultat suivant : 

Comment mettre en évidence le Réalisé par rapport au Prévisionnel

 Trois façon de représenter l'évolution des ventes réalisées par rapport aux objectifs fixés

Il est parfois utile de représenter graphiquement le réaliser par rapport au prévisionnel lors d'une vente ou d'un budget.

A partir des données ci-dessous : 

Voici 3 représentations possibles :

• Représentation n° 1 :

• Représentation n° 2 : 

• Représentation n° 3 :

Et vous laquelle de ces représentations préférez-vous ? 

Réaliser un Graphique de type Courbe Séparée

Comment réaliser un graphique de type Courbe Séparée

Il souvent très utile graphiquement de pouvoir séparer les représentations afin que les données puissent être  lisible plus facilement. 

Mais un exemple vaut mieux qu'un long discours, prenons les données d'origine de notre tableau suivant à représenter  : 

 et l'objectif à atteindre est celui-ci :  

Nous voyons sur cette représentation très facilement l'évolution des ventes pour chacun des commerciaux sur les 4 dernières années. 

Afin de réaliser ce type de graphique, nous aurons besoin de redisposer les données de notre tableau de la façon suivante : 

Une fois les données remanier, nous allons pouvoir créer notre représentation graphique 

• Sélectionner les plages de données : B3:B18 et D3:E18
  Maintenir la touche Ctrl pour sélectionner la seconde plage non adjacente
• Onglet Insertion/Groupe Graphique/Lignes/Type de graphique Courbe 

Vous obtenez le graphique ci-dessous : 

• Cliquer sur le légende et taper sur la touche Suppr pour la retirer
• A ce stade, vous n'avez plus que de la mise en forme de votre choix à réaliser à partir des onglets  Outils de graphique/Mise en forme 

Comment représenter une évolution de Chiffre d'Affaire sans graphique

Représenter une évolution de Chiffre d'Affaire avec l'outil SmartArt

Il n'est pas toujours utile de passer par la construction d'un graphique pour représenter une évolution ou une tendance de ventes ou de chiffre d'affaire.

Il est parfois plus simple d'utiliser l'outil SmartArt disponible dans Excel

Prenons l'exemple ci-dessous : 

Nous souhaitons réaliser une représentation graphique de l'évolution du Chiffre d'affaire. 

Bien sûr nous aurions pu réaliser cela en insérant un graphique de type Barre ou Courbe.

Mais, une autre possibilité est d'utiliser l'outil SmartArt.

• Onglet Insertion/Groupe Illustrations/SmartArt/
• Sélectionner le type de SmartArt : Processus  puis Flèche vers le haut, comme ci-dessous : 

• Taper les valeurs dans le champ Texte, comme l'exemple ci-dessous : 

Le résultat final est le suivant : 

Calculer les jours fériés du calendrier français

Comment calculer les jours fériés d'une année calendaire 

Il est souvent utile lorsque l'on travaille sur la gestion de plannings et de calendriers de gérer les jours fériés de l'année en cours. 

Je vais vous montrer comment réaliser le tableau ci-dessous qui regroupe les 13 jours fériés du calendrier français et ensuite l'utiliser dans des formules de calculs de jours ouvrés.

A) Calcul des jours fériés :

• Taper l'année en cours dans la cellule A1 : 2014 
• Calcul du jour de l'an (A3) : =DATE(A1;1;1)
• Calcul du jour de Pâques (A4) :
  =DATE(A1;3;29,56+0,979*MOD(204-11*MOD(A1;19);30)-JOURSEM(DATE(A1;3;28,56+0,979*MOD(204-11*MOD(A1;19);30))))
• Calcul du Lundi de Pâques (A5) : =A4+1
• Calcul du jour de la Fête du travail (A6) :=DATE(A1;5;1)
• Calcul du jour de la victoire (A7) : =DATE(A1;5;8)
• Calcul du jour de l'Ascension (A8) : =A4+39
• Calcul de la Pentecôte (A9) : =A4+49
• Calcul de Lundi de Pentecôte (A10) : =A4+50
• Calcul du jour de la Fête Nationale (A11) : =DATE(A1;7;14)
• Calcul du jour de l'Assomption (A12) : =DATE(A1;8;15)
• Calcul du jour de la Toussaint (A13) : =DATE(A1;11;1)
• Calcul du jour de l'Armistice (A14) : =DATE(A1;11;11)
• Calcul du jour de Noël (A15) : =DATE(A1;12;25)

B) Mise en forme en Gras des jours fériés tombant un Samedi ou un Dimanche :

• Sélectionner la plage de cellules : (A3:A15)
• Onglet Accueil / Groupe Style / Mise en forme conditionnelle/Nouvelle règle...
• Compléter la boite de dialogue comme suit :

C) Nommer la plage des jours fériés :

• Sélectionner la plage de cellules : (A3:A15)
• Onglet Formules/Groupe Noms définis/Définir un nom
• Compléter la boite de dialogue comme suit :

D) Utilisation des jours fériés dans des calculs :

• Exemple n° 1 : Calculer le nombre de jours ouvrés avec les jours fériés entre le
  mardi 1 avril 2014 et le jeudi 15 mai 2014 

• Exemple n° 2 : Calculer le nombre de jours ouvrés sans les jours fériés entre le
  mardi 1 avril 2014 et le jeudi 15 mai 2014 

Exclure des données hors-norme dans les calculs

Comment exclure d'un calcul des données hors-normes

Prenons l'exemple suivant pour illustrer notre calcul : 

Explications :

Pour le mois de janvier, la moyenne classique est de 17193. On peut observer que les chiffres de JACQUES et de HENRI sont hors normes. Pour éviter de fausser nos calculs, nous allons donc demander à Excel d'exclure de notre calcul les deux valeurs extrêmes (la plus petite et la plus grande).

Calculs :

Si nous prenons le mois de Janvier : 

• Notre plage contient 7 valeurs : (C34:C40)
• Nous allons utiliser un pourcentage de 0,3 : 7 valeurs *0,3 = 2,1
  - Le multiple de 2 le plus proche de 2,1 est 2
• Les critères seront (pour la ligne 42) :
  - matrice : C34:C40
  - pourcentage : 0,3

Formule : 

La formule sera : =MOYENNE.REDUITE(C34:C40;0,3)

Résultat : 

Excel va donc exclure du calcul la valeur la plus petite et la valeur la plus grande. 7000 et 49800 ne seront donc pas pris en compte dans le calcul de la moyenne réduite (ligne 42) du mois de janvier.
Pour comparaison, la moyenne incluant tous les montants est calculée en ligne 41. Elle est nettement supérieure.

Créer un graphique évolutif avec la fonction Decaler()

Comment créer un graphique évolutif avec la fonction Decaler()

Lors de précédent tutoriel, je vous ai montré des possibilités d'utilisation de  la fonction Decaler(). Aujourd'hui je vais vous expliquer comment réaliser un graphique évolutif et dynamique grâce à cette fonction. 

Pour illustrer notre explication, prenons l'exemple ci-dessous : 

Dans cet exemple, les données de mon graphique vont passer de Août à Septembre pour aller jusqu'en Décembre. Au lieu de mettre à jour à chaque fois notre graphique lors d'une saisie d'une nouvelle donnée, il serait plus simple d’avoir un graphique évolutif et dynamique qui grandit en même temps que ses données source. 

C’est ce que nous allons apprendre à faire: créer un graphique évolutif. 

I) Etape n° 1 : Créer l'axe horizontal et la série de données évolutifs

Pour créer un graphique dynamique de la sorte, il faut donc créer :

-          Une série de données évolutive

-          Et un axe horizontal évolutif

Nous allons passer évidemment par la fonction DECALER (pour bien comprendre cette fonction, vous pouvez vous référez à mes articles précédents sur la fonction Decaler)

a) Nom pour série de données évolutive :

-          Dans le ruban : Onglet Formules/ Groupe Noms définis/Définir un nom

-          Dans la boite de dialogue Nouveau nom, dans Nom écrire Paul, Fait référence à écrire: =DECALER(Feuil1!$B$2;1;;NBVAL(Feuil1!$B$3:$B$14))

-          Valider

Faire de même pour les données concernant Pierre, la formule à taper dans Fait référence à sera : 
=DECALER(Feuil1!$C$2;1;;NBVAL(Feuil1!$C$3:$C$14)) et le nom : Pierre

b) Nom pour l'axe horizontal des mois évolutif :

-          Dans le ruban : Onglet Formules/ Groupe Noms définis/Définir un nom

-          Dans la boite de dialogue Nouveau nom, dans Nom écrire Axe, Fait référence à écrire: =DECALER(Feuil1!$A$2;1;;NBVAL(Feuil1!$A$3:$A$14))

-          Valider

II) Etape n° 2 : Insérer les noms créés dans le graphique

Maintenant que les noms ont été créés, on va les mettre comme données source dans le graphique.

-          Sélectionner le graphique;

-          Bouton droit de la souris, cliquer sur Sélectionner les données;

-          Cliquer sur Paul, puis sur Modifier

Dans la boite dialogue modifier la série, dans valeur écrire la formule =Feuil1!Paul et valider

Faire de même avec les données de Pierre, dans valeur écrire la formule =Feuil1!Pierre et valider

Dans la boite de dialogue Sélectionner la source de données, du côté de Etiquettes de l’axe horizontal, cliquer sur Modifier

Dans la boite de dialogue, écrire =Feuil1!Axe et valider 2 fois 

Et voilà, votre graphique est maintenant évolutif. Dès qu'une données sera ajoutée, par exemple celle du mois de Septembre, le graphique évoluera également dynamiquement comme ceci :

Réaliser des sommes conditionnelles

Trois façons de réaliser des sommes conditionnelles

Il existe plusieurs manières de réaliser des sommes conditionnelles. Je vais vous montrer trois façons différentes de réaliser des sommes à partir d'un critère.

Prenons l'exemple ci-dessous pour illustrer ces méthodes : 

L'objectif est de réaliser la somme des ventes effectuée par chacun des commerciaux, pour cela nous allons saisir les noms des commerciaux dans la plage de cellules E4:E99 comme ceci:

A) Méthode n° 1 : Utiliser la fonction Somme.si

• Se positionner sur la cellule F4 
• Taper la formule : =SOMME.SI($B$4:$B$21;E4;$C$4:$C$21)
• Copier cette formule sur la plage F5:F9

B) Méthode n° 2 : Utiliser la fonction SommeProd

• Se positionner sur la cellule H4 
• Taper la formule : =SOMMEPROD(($B$4:$B$21=E4)*$C$4:$C$21)
• Puis valider la formule avec les touches : Ctrl +Maj + Entrée
• Copier cette formule sur la plage H5:H9

B) Méthode n° 3 : Utiliser la fonction Somme

• Se positionner sur la cellule J4 
• Taper la formule : =SOMME(($B$4:$B$21=E4)*$C$4:$C$21)
• Puis valider la formule avec les touches : Ctrl +Maj + Entrée
• Copier cette formule sur la plage J5:J9

Nous obtenons le résultat suivant : 

Désormais vous savez réaliser des sommes conditionnelles de trois manières différentes. A vous de choisir celle qui vous convient le mieux.

La fonction SOMMEPROD() - Suite

Comment utiliser la fonction SOMMEPROD() pour compter et calculer avec des critères

Je vous ai présenté lors d'un précédent tutoriel l'utilisation de la fonction SOMMEPROD(), aujourd'hui nous allons voir que cette fonction permet de réaliser des calculs complexes en tenant compte de critères.

Prenons notre exemple précédent pour bien illustrer cette fonction : 

• Premier objectif : compter le nombre de fois que l'on retrouve un critère dans le tableau (l'équivalent serait la fonction NB.SI()
• Second objectif : faire en une seule formule les sommes et le produit à partir d'un critère

Exemple 1 : Compter le nombre de ventes réalisées par Jacques dans la région Ouest 

• En G3 taper : Jacques
• En I3 taper : Ouest 
• Se positionner sur la cellule I6 et taper la formule suivante :
   =SOMMEPROD((B2:B20=I3)*(C2:C20=G3))

Explications :

- Pour la colonne B2:B20 (qui correspond à nos régions), la fonction va vérifier pour chaque ligne d'enregistrement si le lieur en question correspond au lieu présent dans notre critère (ici Ouest en cellule I3)

- Pour la colonne C2:C20 (qui correspond à nos vendeurs) , la fonction va tester si pour chaque enregistrement la valeur correspond à notre critère (ici Jacques en celllule G3)

- Le fait d'avoir inscrit le * entre les deux tests permet de compter uniquement si les deux tests ont la valeur VRAI en même temps. En résumé, le "*" va se comporter ici comme l'opérateur logique "et"

Résultat :

Exemple 2 : Calculer le montant des ventes réalisées par Jacques et en région Ouest

• Se positionner sur la cellule I8 et taper la formule suivante :
  =SOMMEPROD(((B2:B20=I3)*(C2:C20=G3)*(D2:D20)*(E2:E20)))

Résultat :

Vous pouvez désormais modifier les valeurs dans les zones de critères afin d'obtenir les calculs correspondants. 

La fonction SOMMEPROD()

Comment utiliser la fonction SOMMEPROD

Je vais vous montrer comment utiliser la fonction SOMMEPROD qui est malheureusement très peu utilisée et qui vous allez voir est très puissante et permet de réaliser des calculs parfois très complexe.

Prenons l'exemple ci-dessous :

Si je vous demande de calculer le montant des ventes total, comment vous y prenez-vous ?

• Je suppose que vous allez dans une première étape ajouter un colonne supplémentaire
• Puis vous y ferez un calcul du genre Quantité * Prix Unitaire pour chacune des cellules de la colonne et ainsi vous obtiendrez le montant par jour des ventes.
• Enfin vous ajouterez une cellule Total dans laquelle vous ferez l'addition de chaque vente. 

Avec la fonction SOMMEPROD, vous pouvez éviter toutes ces étapes

• Se positionner sur la cellule : E22
• Ecrire la formule : =SOMMEPROD(D2:D20;E2:E20)

Explication : 

La fonction SOMMEPROD va multiplier les plages de cellules sélectionnées et ensuite faire la somme.  Elle peut ainsi réaliser ce même type de calcul pour plusieurs plages de cellules.

Résultat : 

J'aborderais dans un prochain tutoriel d'autres façons d'utiliser la fonction SOMMEPROD, vous serez certainement  surpris de la puissance de cette dernière

Comment utiliser la fonction INDIRECT ?

Comment animer un tableau ou un graphique avec la fonction INDIRECT()

Aujourd'hui je vais vous montrer comment rendre dynamique les données d'un tableau ou d'un graphique très facilement en utilisant la fonction INDIRECT.

Prenons l'exemple suivant représentant les ventes annuelles réalisées par plusieurs commerciaux  :

A) Etape 1 : Nommer les tableaux 

• Sélectionner la première plage de cellules : B5:E17
• Onglet Formules/Groupe Noms définis/Définir un nom /
• Compléter la boite de dialogue comme ci-dessous puis valider par OK

Faire de même pour les 2 autres tableaux :

• Sélectionner le deuxième tableau : G5:J17
• Nommer la zone des cellules  : Paul
• Sélectionner le troisième tableau : L5:O17
• Nommer la zone des cellules : Jacques

B) Etape 2 : Réaliser une validation de données 

Cette étape va consister de réaliser une liste déroulante affichant les noms des trois commerciaux  (Dans notre cas, il s'agira des 3 zones nommées précédemment)

• Sélectionner la cellule B20
• Onglet Données/Groupe Outils de données/Validation de données/Validation de données...
• Compléter la boite de dialogue comme suit puis valider par OK:

• Tester votre liste déroulante, vous devez voir apparaître la liste des trois commerciaux 

C) Etape 3 : Créer le tableau dynamique

Cette étape va consister à créer un tableau dynamique à partir de notre liste déroulante et de la fonction INDIRECT()

• Sélectionner la plage de cellules B24:E34  qui correspond  à notre trame de tableau qui possède 13 lignes (En-têtes + 12 mois de l'année) et 4 colonnes (Les mois + 3 produits)
• Dans la barre de formule taper : =INDIRECT(B20) puis valider par les touches : Ctrl + Maj + Entrée (permet de réaliser un calcul matriciel)
• Votre formule est désormais entre accolades { }, comme ceci : {=INDIRECT(B20)}
• Désormais, si vous changez le nom de votre liste déroulante en sélectionnant le nom d'un autre commercial, le tableau associé à ce dernier s'affichera.

D) Etape 4 : Créer le graphique dynamique

Cette dernière étape va consister à faire une représentation graphique du tableau créé précédemment

• Sélectionner la plage de cellules : B24:E34
• Onglet Insertion/Groupe Graphiques/Colonne/Histogramme Empilé
• Votre graphique est désormais dynamique, puisqu'il affichera les données du tableau matriciel qui lui-même est lié à notre liste déroulante.

Votre représentation devrait ressembler à celle-ci :