Comment créer un graphique auto-expansible

Créer un graphique élastique 

Je vous ai présenté lors d'un tutoriel précédent comment faire évoluer un graphique automatiquement en utilisant la fonction DECALER(). 

Je vais vous présenter une autre manière de rendre un graphique dynamique sans utiliser de formules. 

Prenons le tableau ci-dessous comme base de travail : 

Etape n° 1 : Mettre sous forme de tableau vos données.

Se positionner sur une cellule de votre tableau (Ex. : Cellule B5) puis dans l'onglet Accueil, groupe Style, cliquer sur la commande Mettre sous forme de tableau puis sélectionner le style de votre choix. 

Etape n° 2 : Créer le graphique.

Se positionner sur une cellule de votre tableau (Ex. : Cellule B5) puis dans l'onglet Insertion, groupe Graphiques, puis sélectionner le style de votre graphique (Exemple : Courbe 2D). 

Etape n° 3 : Rajouter les mois et les valeurs chiffrée dans votre tableau.

A présent, votre graphique va s'ajuster automatiquement dès lors que des nouvelles données seront rajoutées dans votre tableau. 

Et vous, comment actualisez-vous vos graphiques...

L'outil Remplissage instantané sous Excel 2013

Comment utiliser l'outil remplissage instantané sous Excel 2013

Dans un précédent tutoriel, je vous ai présenté comment utiliser l'outil  d'analyse rapide des données sous Excel. Aujourd'hui, je vais vous montrer un nouvel outil d'Excel 2013 permettant de vous éviter de la saisie supplémentaire et donc gagner du temps.  

Excel 2013 deviendrait-il intelligent ? Pour illustrer cet outil, prenons la liste ci-dessous :

1- Comment concaténer des chaines de caractères : 

Nous allons créer dans la colonne D une liste de valeurs affichant les prénoms et les noms de la table. Dans les versions précédentes d'Excel, nous aurions dû dans la cellule D2 utiliser la fonction : =CONCATENER(C2;B2) et faire une poignée de recopie vers le bas pour copier la fonction. 

Et bien dorénavant, plus besoin d'utiliser des fonctions, Excel va vous remplir votre liste en tenant compte d'un exemple. Pour indiquer à Excel ce que nous voulons faire, il faut taper les valeurs que nous voulons dans notre cellule. 

- Se positionner en cellule D2 et saisir : Robert Troigne puis valider par Entrée

- En cellule D3 saisissez : Tabatha Sicard

Dès que vous taper les premières lettre de Tabatha, Excel a compris ce que vous désirez faire et vous propose de compléter à votre place liste. Il suffit de faire Entrée pour valider sa proposition. 

2- Créer des adresses émail à partir d'un prénom et d'un nom : 

Sur le même principe que précédemment, il vous est possible de générer des trigrammes ou adresse émail dès lors que vous avez une liste comprenant des prénoms et des noms de personnes. 

Ci-dessous quelques exemples de remplissage instantanée : 

Alors, vous pensez maintenant qu' Excel 2013 est intelligent...

Analyser ses données avec Excel 2013

Utiliser la loupe d'analyse rapide sous Excel 2013

Avec l'arrivée d'Excel 2013, de nouvelles fonctionnalités sont apparues et notamment la loupe d'analyse rapide. Cette fonctionnalité va vous permettre de réaliser une analyse des données de vos tableaux. 

1- Comment faire apparaître la loupe d'analyse rapide :

Pour faire apparaître la loupe d'analyse rapide, il faut tout simplement réaliser une sélection de votre tableau ou de vos données. Apparaît ensuite la loupe d'accès rapide en bas à droite de votre sélection, comme le montre l'illustration suivante : 

2- Quels outils sont proposés par la loupe d'analyse rapide :

Les outils d'analyses que propose la loupe sont les suivants : 

- Mise en forme (Barre de données, Jeux d'icônes, Top 10...)
- Graphiques (Tous les graphiques, Graphiques recommandés)
- Totaux (Somme, Moyenne, Poucentage...)
- Tableaux (Mise sous tableau, Tableau Croisé Dynamique)
- Graphiques Sparkline (Courbe, Histogramme, Positif/Négatif)

3- Exemples d'analyse rapide : 

Je suis persuadé que ces nouveaux outils vont vous permettre de réaliser des analyses de vos données beaucoup plus rapidement désormais. 

A bientôt sur un prochain tuto...

Comment calculer une marge commerciale sous Excel

Calculer une marge commerciale 

• Définition : La marge commerciale est un indicateur de la rentabilité d'une activité et intervient dans la formation des prix.

La marge commerciale brute est la différence entre le prix de vente hors taxe et le coût d'achat hors taxe.

• Formule :      Marge commerciale = Prix de vente HT - Coût d'achat HT 

Calculer un taux de marge commerciale 

• Définition : Le taux de marge commerciale est le rapport entre la marge commerciale brute et le coût d'achat

La marge commerciale brute est la différence entre le prix de vente hors taxe et le coût d'achat hors taxe.

• Formule :   Taux de marge  = (Prix de vente - Prix d'achat) / Prix d'achat

                

Exemple de calcul : 

Calculer une moyenne pondérée

Calcul d'une moyenne pondérée

Il est facile sous Excel de calculer une moyenne grâce à l'outil de fonctions automatiques en utilisant la fonction Moyenne().

Mais il peut arriver d'avoir besoin de réaliser une moyenne pondérée notamment tenant compte d'une deuxième série (ou matrice). La fonction que nous allons utiliser pour réaliser la somme des produits de plusieurs plages est SOMMEPROD(). 

Prenons l'exemple d'un professeur qui souhaite réaliser une moyenne pondérée des notes obtenues par ses élèves tenant compte d'un coefficient appliqué pour chacune des matières.

Prenons l'exemple ci-dessous : 

Syntaxe de la fonction : =SOMMEPROD(matrice1;matrice2;...)

• Pour réaliser la somme des produits des plages C7:I7 et C6:I6 en cellule M7. La formule est la suivante : =SOMMEPROD(C7:I7;$C$6;$I$6). La plage des coefficients devant servir pour les autres élèves, cette plage doit être figée. La formule peut ensuite être recopier sur la plage M8:M12
• Pour calculer la moyenne pondérée en cellule N7, il suffit de taper la formule suivante : =M7/$K$6 puis recopier cette formule sur la plage N8:N12

Le résultat obtenu est le suivant : 

Nous remarquons que le résultat obtenu avec la fonction moyenne et celui obtenu avec la moyenne pondérée des notes est différent car tenant compte des coefficients par matières. 

Réaliser des rapports avec un TCD et Mise en forme conditionnelle

Réaliser un rapport avec la fusion d'un Tableau Croisé dynamique et d'une Mise en forme Conditionnell

Il est désormais possible avec les dernières versions d'Excel de combiner Tableau Croisé Dynamique (TCD) et Mise en Forme Conditionnelle. Cette combinaison va nous permettre de réaliser de superbe rapport d'activité ou tableau de bord. 

L'avantage est que la rapport se mettra automatiquement à jour lorsque les données seront ajoutées dans la base de données. 
Prenons l'exemple de la Base ci-dessous pour illustrer cette leçon :

1. La base de données :

Lorsque nous parlons de la base de données (ou liste), cette dernière ne doit pas avoir de lignes vides, ni de fusion de cellules. Par contre elle doit posséder une en-tête pour chaque colonne, la dernière étape est de mettre cette base de données sous forme de Tableau 

Onglet Accueil/Groupe Style/Mettre sous forme de tableau

2. La création du tableau croisé dynamique :

a) Sélectionner une cellule de la Base de données, puis dans le ruban :

Outils de tableau/Onglet Création/Groupe Outils/Tableau croisé dynamique. Dans la nouvelle boite de dialogue valider par OK. Par défaut, toute la base de données aura été sélectionnée et le TCD sera généré dans une nouvelle feuille de calcul.

b) Dans la nouvelle feuille, dans la feuille Liste des champs, déplacer les champs : 

 - "Commercial" et "Région" dans Lignes 

 - "Montant" dans Valeurs. Mettre 2 fois Montant dans valeurs. Oui, oui deux foix, vous allez voir pourquoi. 

Votre  rapport doit ressembler à cela :

3. Changeons le 2nd champ "Montant" et appliquons un tri :

Dans la liste de champs, cliquer sur "Somme de Montant2", dans le menu cliquer sur "Paramètres de champs de valeur".

Dans la nouvelle boite de dialogue, cliquer sur l'onglet "Afficher les valeurs", dans la liste déroulante, choisir : % du total général et cliquer sur OK.

Pour appliquer le tris dans le Tableau croisé dynamique : 

• Le tri des sous-totaux : se mettre sur un des sous-totaux au choix en cliquant avec le bouton droit de la souris, Trier/ Trier  du plus grand au plus petit.
• Le tri sur les valeurs par Région : se mettre sur l'une des valeurs de vente d'une région en cliquant avec le bouton droit de la souris, Trier/Trier du plus grand au plus petit.

Après avoir effectué ces tris, votre TCD sera toujours trier selon cet ordre quel que soit le nombre de fois que vous l'actualiserez.

Vous pouvez également en profiter pour personnaliser le format de vos valeurs dans le TCD.

4. Mise en forme conditionnelle dans notre TCD :

Pour rajouter la mise en forme conditionnelle, dans le TCD, se mettre sur un des pourcentages (Attention : pas les sous-totaux), dans : 

Onglet Accueil/Groupe Style/Mise en forme conditionnelle/Barre de données/Autres règles

Votre TCD est pratiquement prêt, il suffit maintenant de faire quelques modifications selon votre choix : 

• Modifier les noms automatiques "Somme de Montant" et "Somme de Montant2"
• Changer le modèle de TCD : Outils TCD/Onglet Création/Groupe style de tableau croisé dynamique.

Et voici les différents résultats que vous pouvez obtenir : 

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