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 :