Réaliser un graphique de type Heat Map

Comment réaliser un graphique de type Heat Map

Je vais vous montrer une nouvelle représentation graphique qui peut vous être utile pour visualiser des données autrement que par un graphique standard. 

Ce type de représentation (appelé en anglais Heat Map) permet d'afficher par intervalle d'heures une valeur quelconque.

Voici un aperçu d'un graphique de type Heat Map :

 

Le principe de ce graphique est que plus les valeurs sont importantes et plus la couleur de la cellule est intense. D'un point de vue visuel, il est très pratique car rapidement on retrouve les tranches d'heures qui ont le plus grand nombre.

Par exemple, les tranches horaires extrêmes (très tôt le matin et tard le soir) sont très claires. Les valeurs sont donc très minimes à contrario des plages de milieu de journée.

Vous l'aurez compris ce type de graphique peut s'adapter à beaucoup de type de données. 

(Exemple : Représenter un trafic d'appels téléphonique, suivi d'un Standard téléphonique ou d'une Hot Line, ou bien représenter le trafic de visite d'un site web,...)

Etape n° 1 : Récupérer les données 

Prenons pour illustrer notre exemple les données suivantes :

Ces données peuvent être saisies ou bien récupérées d'un site web ou outils de statistiques. 

Etape n° 2 : Définir les règles des différentes couleurs à appliquer 

Nous allons définir précisément les règles que nous allons appliquer pour chacune des couleurs. Dans l'exemple illustré, j'ai décidé d'appliquer les règles correspondant à la légende ci-dessous : 

Vous pouvez ainsi définir votre propre critère et vos propres couleurs. 

Etape n° 3 : Appliquer des mises en forme conditionnelle en fonction de chacun de nos critères.

Nous allons créer une mise en forme conditionnelle dans notre tableau. Nous allons commencer par la règle des valeurs supérieures à 1000 et appliquer une couleur Noire

1. Sélectionner la plage à mettre en forme, ici C3:G14

2. Onglet Accueil/Groupe Style/Mise en forme conditionnelle/

    Règles de mise en surbrillance des cellules / Supérieur à 

3. Dans la boite de dialogue suivante : 

     - Taper 1000 dans la première cellule 

     - Avec dans la deuxième cellule un Format Personnalisée de type :
                           Police = Couleur Noire 

                           Remplissage = Couleur Noire 

Explication : le faire de mettre la Police de caractère de la même couleur que le Remplissage de la cellule permet de masquer les valeurs. 

4. Vous devriez avoir le tableau suivant : 

Vous l'aurez compris pour chacun de nos critères, nous allons créer une mise en forme conditionnelle dans notre tableau tenant compte des couleurs de la légende. Nous refaire un exemple pour la règle n° 2 (Valeurs comprises en 801 et 1000, on applique une couleur bleue très foncée).

1. Sélectionner la plage à mettre en forme, ici C3:G14

2. Onglet Accueil/Groupe Style/Mise en forme conditionnelle/

    Règles de mise en surbrillance des cellules / Entre...

3. Dans la boite de dialogue suivante : 

     - Taper 801 dans la première cellule  et 1000 dans la deuxième

     - Avec dans la troisième cellule un Format Personnalisée de type :
                           Police = Couleur Bleue Foncée 

                           Remplissage = Couleur Bleue Foncée

4. Vous devriez avoir le tableau suivant : 

Vous l'aurez compris, il suffit de faire de même pour chacun de nos critères et vous obtiendrez votre représentation de type Heat Map.

Alors que pensez-vous de ce nouveau graphique ?

Besoin de convertir des valeurs ? Alors vous avez la fonction Convert()

Comment convertir des valeurs sous Excel ? 

Vous aurez besoin, au moins une fois, dans Excel de pouvoir convertir des mesures (Mètres, Degré,...) d'un pays vers un autre ou bien d'une unité de mesure vers une autre. 

Et bien, Excel vous propose une fonction intitulée CONVERT() qui va vous proposer une panoplie de conversion d'unité de mesure, dont certaines sont parfois étonnantes.

La syntaxe de cette fonction est la suivante : 

CONVERT(Nombre; de_unité; à_unité)

Prenons l'exemple suivant : 

Objectif : Nous souhaitons d'une valeur exprimée en M3 (ici : Cellule D2) pouvoir la convertir en Litre en cellule E2

Pour utiliser cette fonction, taper dans la cellule E2 la formule suivante : 

=CONVERT(D2;"m^3";"l") =>  Vous obtenez la valeur 1 000 

En effet, 1 M3 vaut bien 1 000 litres.

Quelques exemple d'utilisation de la fonction Convert() :

Et vous, est-ce que vous utilisez la fonction Convert ? 

Comment utiliser la fonction SI

Utiliser la fonction SI 

Il est parfois très utile de réaliser des tests afin d'en extraire un résultat à partir de plusieurs conditions. 

Avec les dernières versions d'Excel, nous avons la possibilité désormais de réaliser 64 conditions maxi.

Prenons l'exemple ci-dessous pour illustrer la fonction SI : 

L'objectif est d'afficher dans les cellules D2:D6 le prix d'un abonnement en fonction de l'âge de la personne.

Vous l'aurez compris, nous aurons besoin de réaliser plusieurs tests avant de pouvoir inscrire le tarif. La fonction SI va nous permettre de réaliser ces tests. 

Syntaxe de la fonction SI :  

     =SI(Test Logique ; Valeur si Vrai ; Valeur si Faux)

Se positionner sur la cellule D2 et taper la formule suivante : 

=SI(C2<16;$C$9;$C$10) 

Explications :

 - Test logique : C2>16  (si l'âge de la personne contenu en C2 est supérieure 16 ans)

 - Valeur si Vrai : on affiche le tarif de 22€ correspondant au tarif enfant (moins de 16 ans)

 - Valeur si Faux : on affiche le tarif suivant 34€ correspondant aux adultes (entre16 et 60)

Vous l'aurez compris, nous avons un troisième test à réaliser, la formule telle que nous l'avons écrite ne prend pas en compte ce troisième test. Nous allons donc devoir rajouter un test supplémentaire en utilisant un autre SI. 

Nous allons donc devoir imbriquer plusieurs SI

Imbrication d'un SI 

Reprenons notre formule initiale en D2 et imbriquons un deuxième SI :

=SI(C2>16;$C$9;SI(C2<=60;$C$10;$C$11))

Copier ensuite cette formule sur les cellules D3 à D6

Sur ce même principe, vous avez la possibilité d'imbriquer autant de SI que vous avez besoin. Il vous est désormais possible de tester non plus une mais beaucoup plus de conditions en fonction de vos besoins.

Il est bien sûr pas utile de tester la dernière condition car cette dernière sera pas défaut la valeur si Faux. 

Résultat : 

Alors après cet exemple, avez-vous des cas concrets d'utilisation de la fonction SI imbriqués ?  

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 :