Comment faire un histogramme dans Excel 2010, 2013 et 2016
Le tutoriel montre 3 techniques différentes pour tracer un histogramme dans Excel - en utilisant l'outil Histogramme spécial d'analyse ToolPak fonction de fréquence ou NB.SI.ENS et PivotChart.
Qu'est-ce qu'un histogramme dans Excel?
Wikipedia définit un histogramme de la manière suivante: « Histogramme est une représentation graphique de la distribution des données numériques. » Absolument vrai, et ... tout à fait clair :) Eh bien, nous allons réfléchir à une autre façon histogrammes.
Avez-vous déjà fait un graphique à barres ou d'une colonne pour représenter certaines données numériques? Je parie que tout le monde a. Un histogramme est une utilisation spécifique d'un graphique à colonnes où chaque colonne représente la fréquence des éléments dans une certaine plage. En d'autres termes, un histogramme affiche graphiquement le nombre d'éléments dans les intervalles consécutifs ne se chevauchant pas, ou des bacs.
Par exemple, vous pouvez faire un histogramme pour afficher le nombre de jours avec une température comprise entre 61-65, 66-70, 71-75, etc. degrés, le nombre de ventes avec des montants entre 100 $ 199 $, 200 $ 299 $, 300 $ - 399 $, le nombre d'élèves avec des scores de test entre 41-60, 61-80, 81-100, et ainsi de suite.
Comment créer un histogramme dans Excel en utilisant l'utilitaire d'analyse
Chargez le complément Utilitaire d'analyse en
Pour ajouter l'analyse des données complément à votre Excel, procédez comme suit:
Maintenant, l'utilitaire d'analyse est chargé dans Excel, et sa commande est disponible dans le groupe d'analyse sur l'onglet Données.
Spécifiez la plage bin de l'histogramme Excel
Avant de créer un histogramme, il y a encore une préparation à faire - ajouter les bacs dans une colonne séparée.
Bins sont des nombres qui représentent les intervalles dans lesquels vous souhaitez regrouper les données sources (données d'entrée). Les intervalles doivent être de taille consécutive, sans chevauchement et généralement égale.
L'outil Histogramme Excel comprend les valeurs de données d'entrée dans des bacs en fonction de la logique suivante:
- Une valeur est comprise dans un certain bin si elle est supérieure à la plus faible lié et égale ou inférieure à la plus grande destination de ce bac.
- Si vos données d'entrée contiennent des valeurs supérieures à la plus haute bac, tous ces numéros seront inclus dans la catégorie de.
- Si vous ne spécifiez pas la plage de bin, Excel va créer un ensemble de bacs de façon équilibrée entre les valeurs minimales et maximales de votre plage de données d'entrée.
Compte tenu de ce qui précède, tapez les numéros de bin que vous souhaitez utiliser dans une colonne séparée. Les bacs doivent être entrées dans l'ordre croissant. et votre gamme de bac d'histogramme Excel doit être limitée à la plage de données d'entrée.
Faire un histogramme en utilisant l'analyse d'Excel ToolPak
Avec l'utilitaire d'analyse a permis et bacs spécifié, procédez comme suit pour créer un histogramme dans votre feuille Excel:
Pointe. Si vous avez inclus les en-têtes de colonne lors de la sélection des données d'entrée et la plage bin, sélectionnez les étiquettes case à cocher.
Pour placer l'histogramme sur la même feuille, cliquez sur Plage de sortie. puis entrer dans la cellule supérieure gauche de la table de sortie.
Pour coller la table de sortie et de l'histogramme dans une nouvelle feuille ou un nouveau classeur, sélectionnez Nouvelle feuille de calcul ou Ply Nouveau classeur. respectivement.
Enfin, choisissez l'une des options supplémentaires:
- Pour présenter les données dans la table de sortie dans l'ordre décroissant de fréquence, sélectionner la case Pareto (histogramme triés).
- Pour inclure une ligne de pourcentage cumulé dans votre tableau d'histogramme Excel, sélectionnez la zone Pourcentage cumulatif.
- Pour créer un graphique d'histogramme intégré, sélectionnez la zone de sortie graphique.
Pointe. Pour améliorer l'histogramme, vous pouvez remplacer la valeur par défaut Bins et fréquence avec des titres d'axe plus significatifs, personnaliser la légende du graphique, etc. En outre, vous pouvez utiliser la conception, la mise en page et les options de format des Outils de graphique pour modifier l'affichage de l'histogramme par exemple, supprimer les écarts entre les colonnes. Pour plus de détails, s'il vous plaît voir comment personnaliser et améliorer l'histogramme Excel.
Comme vous venez de voir, il est très facile de faire un histogramme dans Excel en utilisant l'utilitaire d'analyse. Cependant, cette méthode a une limitation significative - le tableau d'histogramme intégré est statique. ce qui signifie que vous aurez besoin de créer un nouvel histogramme chaque fois que les données d'entrée est modifiée.
Pour un automatiquement updatablehistogram. vous pouvez utiliser les fonctions Excel ou de construire un tableau croisé dynamique comme illustré ci-dessous.
Comment faire un histogramme dans Excel à l'aide des formules
Une autre façon de créer un histogramme dans Excel utilise la fonction de fréquence ou NB.SI.ENS. Le plus grand avantage de cette approche est que vous ne devez refaire votre histogramme à chaque changement dans les données d'entrée. Comme un tableau Excel normale, votre histogramme mettra automatiquement à jour dès que vous modifiez, ajouter ou supprimer des nouvelles valeurs d'entrée existantes.
Création d'un histogramme en utilisant la fonction FREQUENCE Excel
La fonction la plus évidente pour créer un histogramme dans Excel est la fonction de fréquence qui renvoie le nombre de valeurs qui se situent dans des plages spécifiques, en ignorant les valeurs de texte et des cellules vides.
La fonction de fréquence a la syntaxe suivante:
- Tableau_données - un ensemble de valeurs pour lesquelles vous voulez compter les fréquences.
- Matrice_intervalles - un tableau de bacs pour regrouper les valeurs.
Dans cet exemple, le tableau_données est B2: B40, tableau bin est D2: J8, donc nous obtenons la formule suivante:
S'il vous plaît garder à l'esprit que la fréquence est une fonction très spécifique, donc suivre ces règles pour le faire fonctionner à droite:
- Une formule de fréquence Excel doit être saisie comme une formule de tableau à plusieurs cellules. Tout d'abord, sélectionnez une plage de cellules adjacentes où vous voulez sortir les fréquences, puis tapez la formule dans la barre de formule, et appuyez sur Ctrl + Maj + Entrée pour le compléter.
- Il est recommandé d'entrer dans une formule plus de fréquence que le nombre de bacs. La cellule supplémentaire est nécessaire pour afficher le nombre de valeurs au-dessus du plus haut bin. Par souci de clarté, vous pouvez l'étiqueter « Plus » comme dans la capture d'écran ci-dessous (mais ne comprennent pas « Plus » cellule de votre matrice_intervalles!):
Comme l'option Histogramme de l'utilitaire d'analyse. La fonction de fréquence Excel renvoie des valeurs qui sont supérieures à un bac précédent et inférieur ou égal à un intervalle donné. La dernière formule de fréquence (dans la cellule E9) renvoie le nombre de valeurs supérieures à la plus élevée bin (à savoir le nombre de jours de livraison de plus de 35).
Pour rendre les choses plus faciles à comprendre, la capture d'écran ci-dessous montre les bacs (colonne D), des intervalles correspondant (colonne C), et les fréquences calculées (colonne E):
Création d'un histogramme en utilisant la fonction NB.SI.ENS
Une autre fonction qui peut vous aider à calculer les distributions de fréquences pour tracer l'histogramme dans Excel est NB.SI.ENS. Et dans ce cas, vous devrez utiliser 3 formules différentes:
- La formule de la première cellule - bac supérieur (F2 dans la capture d'écran ci-dessous):
= NB.SI.ENS ($ B $ 2: $ B $ 40, »<="-$D2)
= NB.SI.ENS ($ B $ 2: $ B $ 100, ">" - $ J8) La formule compte combien de valeurs dans la colonne B sont supérieures à la plus haute bac à J8.
= NB.SI.ENS (B $ 2: $ B $ 40, ">" - $ D2, B $ 2: $ B 40 $, »<="-$D3)
La formule compte le nombre de valeurs dans la colonne B qui sont supérieures à la poubelle dans la ligne au-dessus et inférieure ou égale à la poubelle dans la même rangée.
Pointe. Si vous prévoyez d'ajouter des lignes de données d'entrée à l'avenir, vous pouvez fournir une plus grande gamme dans vos formules de fréquence ou NB.SI.ENS, et vous ne devrez pas changer vos formules que vous ajoutez plus de lignes. Dans cet exemple, les données sources sont dans les cellules B2: B40. Mais vous pouvez fournir la plage B2: B100 ou même B2: B1000, juste au cas :) Par exemple:
= Fréquence (B2: B1000, D2: D8)
D'une manière générale, vous avez déjà un histogramme pour vos données d'entrée, mais il faut certainement quelques améliorations. Plus important encore, pour rendre votre histogramme Excel facile à comprendre, vous devez remplacer les étiquettes par défaut de l'axe horizontal représenté par des numéros de série avec vos numéros de bin ou des plages.
S'il n'y a aucun moyen que vous pouvez taper les étiquettes d'histogramme souhaité sur votre feuille, vous pouvez les saisir directement sur le graphique, indépendamment des données de feuille de calcul. La dernière partie de ce tutoriel explique comment faire, et montre un couple d'autres améliorations qui peuvent être apportées à votre histogramme Excel.
Comment faire un histogramme avec un graphique croisé dynamique
1. Créez un tableau croisé dynamique
Pour créer un tableau croisé dynamique, allez à l'onglet Insertion> groupe tables et cliquez sur tableau croisé dynamique. Et puis, déplacer le champ de livraison à la zone RANGS, et l'autre champ (N ° de commande dans cet exemple.) Dans la zone de valeurs, comme le montre la capture d'écran ci-dessous.
3. Créez les intervalles (bacs)
L'étape suivante consiste à créer les intervalles ou les bacs. Pour cela, nous utiliserons l'option de regroupement. Cliquez-droit sur une cellule sous des étiquettes de ligne dans votre tableau croisé dynamique et sélectionnez Groupe ...
Dans la boîte de dialogue de regroupement, spécifiez le début et la fin des valeurs (généralement Excel entre la valeur minimale et maximale automatiquement en fonction de vos données) et tapez l'incrément (longueur d'intervalle) souhaité dans la zone Par.
4. Tracer un histogramme
Et maintenant, polir votre histogramme avec deux finitions:
Personnaliser et améliorer votre histogramme Excel
Si vous créez un histogramme en utilisant l'utilitaire d'analyse, les fonctions Excel ou un graphique croisé dynamique, vous voudrez peut-être souvent pour personnaliser le graphique par défaut à votre goût. Nous avons un tutoriel spécial sur les graphiques Excel qui explique comment modifier le titre du graphique, la légende, les axes titres, changer les couleurs du graphique, mise en page et le style. Et ici, nous allons discuter de quelques personnalisations spécifiques à un grands histogramme Excel.
Modifiez les étiquettes des axes sur un graphique de l'histogramme Excel
Lors de la création d'un histogramme dans Excel avec l'utilitaire d'analyse, Excel ajoute les étiquettes d'axe horizontal sur la base des numéros de bin que vous spécifiez. Mais si, sur le graphique de l'histogramme Excel, vous souhaitez afficher des plages au lieu des numéros de bin? Pour cela, vous aurez besoin de changer les étiquettes d'axe horizontal en effectuant ces étapes:
Supprimer l'espacement entre les barres
Lorsque vous effectuez un histogramme dans Excel, les gens attendent souvent des colonnes adjacentes de se toucher, sans aucune lacune. Ceci est une chose facile à corriger. Pour éliminer l'espace vide entre les barres, il suffit de suivre les étapes suivantes:
Voici comment dessiner un histogramme dans Excel. Pour une meilleure compréhension des exemples abordés dans ce tutoriel, vous pouvez télécharger l'exemple de feuille Excel Histogramme avec les données source et les graphiques de l'histogramme. Je vous remercie pour la lecture et l'espoir de vous voir sur notre blog la semaine prochaine.