Partage pratique de compétences Excel : un graphique pour gérer la comparaison des données, la tendance et la contribution proportionnelle

Dans le travail quotidien, nous rencontrons souvent des tableaux de comparaison de données. Les tableaux de tendances des données représentent également des compétences que nous devons connaître. Je pense que de nombreux étudiants peuvent les compléter. Mais récemment, l'étrange leader d'un camarade de classe a proposé : « Cela doit avoir à la fois un effet de comparaison et un effet de tendance, et plusieurs séries de graphiques doivent être affichées par mois tout au long de l'année, et tous doivent être regroupés en un seul graphique ! 

Donc, la « difficulté » dans notre travail n’est pas le manque de compétences, mais l’imagination du patron. Alors aujourd'hui, je vais vous donner une idée pour réaliser un "graphique de comparaison/tendance multi-séries".

[Source des données]

Avant de regarder l'article ci-dessous, les étudiants peuvent y réfléchir par eux-mêmes Si on vous demandait de concevoir de telles données, que feriez-vous ?

【Texte】

QuantityS'il n'y a qu'un seul ensemble de données

Rien à dire, utilisez simplement un histogramme, un graphique à barres, un graphique en beignet, tout ce que vous voulez.

Même si nous avons besoin de voir des pourcentages et des lignes de tendance, nous pouvons insérer une colonne auxiliaire de pourcentages dans la colonne C des données, comme suit :

ÉTAPE 1 : Sélectionnez la zone des cellules A1:B4, Insertion de barre d'outils - Graphique - Graphique à colonnes groupées

Ensuite, vous obtiendrez la première ébauche d'un graphique à colonnes ;

ÉTAPE 2 : Cliquez avec le bouton droit n'importe où dans la zone du graphique et dans le menu contextuel, cliquez avec le bouton gauche sur "Sélectionner les données".

Dans la zone de configuration contextuelle, cliquez sur "Ajouter" une série à gauche, saisissez les données comme suit ou sélectionnez la zone de la feuille de calcul pour saisir le contenu

ÉTAPE 3 : Cliquez n'importe où dans la zone du graphique ; , Cliquez avec le bouton droit de la souris et sélectionnez « Modifier le type de graphique »

Changez le type de graphique de la série de proportions en « Graphique à nuages ​​de points avec ligne lisse et marqueurs de données ».

Grâce à cela, nous avons obtenu le prototype de la carte pour la deuxième étape.

ÉTAPE 4 : Sélectionnez le graphique de la série de proportions dans la figure, cliquez avec le bouton droit de la souris et sélectionnez l'option « Formater la série de données » dans le menu 

Dans les options de série, cliquez sur ; "Axe de coordonnées secondaire" »,

Obtenez le graphique comme suit :

Embellissons l'interface du graphique et ajoutons des étiquettes de données. Nous n'entrerons pas dans les détails. Donnons un exemple de graphique.

QuantityRegardons les principales données d'aujourd'hui

Je dois dire que dans le travail réel, la production de graphiques doit toujours être ciblée et proéminente pour exprimer certaines données si beaucoup d'informations sont regroupées en une seule. image, cela semblera déroutant.

La combinaison d'un graphique à colonnes et d'un graphique linéaire (l'auteur utilise ici un graphique à nuages ​​de points) est très couramment utilisée, et cette combinaison a à la fois une comparaison et une tendance, ce qui est très pratique à regarder. Alors nos principales données d’aujourd’hui peuvent-elles également produire un tel effet ? Comme le montre l'image ci-dessous :

【Planification initiale du dessin】

Si nous décidons d'utiliser un histogramme + un graphique linéaire pour représenter ce tableau, alors nous devons avoir une esquisse sur papier ou dans notre esprit : chaque ensemble de graphiques correspond aux données sur 12 mois d'un groupe de projet. La difficulté est que les extrémités inférieures de chaque groupe de graphiques doivent être alignées ; la polyligne doit apparaître dans le groupe de graphiques de ce projet.

【Sélection du type de graphique】

Évidemment, si plusieurs graphiques à colonnes sont superposés, nous devons choisir « Graphique à colonnes empilées », mais si nous opérons avec des données originales, ce sera le graphique ci-dessous.

Les extrémités inférieures de chaque groupe d'éléments ne sont pas alignées, ce qui n'est pas ce dont nous avons besoin, mais si nous insérons un "graphique d'espace réservé" au milieu de chaque série, pouvons-nous "surélever" chaque série de graphiques ? Ensuite, définissez la couleur et la bordure des graphiques d'espace réservé sur « aucune couleur », vous pouvez simuler l'effet de chaque ligne étant un histogramme et alignée en bas, comme suit :

Les étudiants peuvent y jeter un œil, ici Nous ont supprimé les couleurs des graphiques d'espace réservé aux points A et B. Les graphiques d'espace réservé verts au point C sont toujours là. Pouvez-vous voir que les extrémités inférieures sont alignées.

Une fois que nous avons de l'espace pour les espaces réservés, nous pouvons ajouter des points de dispersion à ces espaces pour marquer les proportions, et enfin connecter ces points de dispersion pour simuler l'effet d'un graphique linéaire. Ainsi, pour la ligne de tendance, nous utilisons un « diagramme à nuages ​​de points avec des lignes lisses et des marqueurs de données ».

【Organiser les données】

Nous devons insérer un graphique d'espace réservé entre les deux séries de graphiques. S'il est pris en compte dans le tableau de données, il s'agit d'ajouter une donnée auxiliaire. Jetons un coup d'œil à l'étape de tri des données.

"Souvent, de bons graphiques ne sont pas créés à partir de données sources." Être doué dans l'utilisation des données auxiliaires est un signe important de création de graphiques avancée, c'est pourquoi il existe également un dicton selon lequel "l'utilisation de graphiques se résume en fin de compte à l'utilisation". de fonctions."

Les caractères "rouges" dans l'image ci-dessus sont les valeurs d'affichage des étiquettes de données de la série, tandis que les caractères "bleus" sont les graphiques d'espace réservé du graphique et les points de marquage des points de dispersion. les idées de conception de fonction de chaque donnée une par une :

Quantity A : Valeur de l'étiquette de données du projet A

• Fonction de cellule B8 : =B2

weight Point A espace réservé : graphique d'espace réservé du projet A

• Fonction de cellule B9 : =MAX( $B$2:$M$4)*2-B8

Explication : B2:M4 est la zone de données source. Nous utilisons le double de la valeur maximale dans les données comme référence de proportion la plus élevée dans la. Ensuite, les graphiques réalisés par chaque projet La proportion sera paire. Le principe est : valeur du point de données + valeur du point d'occupation correspondante = valeur maximale dans la source de données * 2, donc valeur du point d'occupation correspondante = valeur maximale dans la source de données *. 2 - valeur du point de données

Quantity B : Valeur de l'étiquette des données du projet de la fonction de cellule B

• B10 : =B3

● Espace réservé du point B : graphique de l'espace réservé du projet B

• Fonction de la cellule B11 : =MAX( $B$2:$M $4)*2-B10

Quantity Espace réservé au point C : le graphique d'espace réservé du projet C

• Fonction de cellule B12 : =B4

● Espace réservé au point C : le graphique d'espace réservé de projet C

• Fonction de cellule B13 : =MAX($B$2:$M$4)*2-B12

weight Proportions de A, B et C : points de données des projets A, B et C, respectivement ce mois-ci

• Fonction de cellule B14 : =B2/SUM(B$2:B$4)

• Fonction de cellule B15 : =B3/SUM(B$2:B$4)

• Fonction de cellule B16 : = B4/SUM(B$2:B$4)

Quantity Un point de proportion : le graphique de tendance du projet A, correspondant à la position des coordonnées du point de dispersion.

• Fonction de cellule B17 : =B8+B9*1/3

Explication : Le nuage de points lui-même nécessite deux points de coordonnées horizontales et verticales. Si nous ne définissons pas l'axe X, la valeur par défaut est le numéro de série, comme suit :

Afin de mieux s'adapter à l'histogramme, notre axe Y est fixé à "correspondant à un tiers du point d'occupation du projet".

Quantity Points de proportion B et C : tableaux de tendance des projets B et C, correspondant aux positions de coordonnées des points de dispersion.

• Fonction de cellule B18 : =SOMME(B8:B10)+B11*1/3

• Fonction de cellule B19 : =SOMME(B8:B12)+B13*1/3

Instructions : Les étudiants doivent Notez que comme les coordonnées sur l'axe Y des points dispersés de chaque projet ont des exigences de « hauteur », leur source de données doit accumuler les données d'autres projets, plus la position d'un tiers du point de proportion correspondant, de sorte que la comparaison Fermer au diagramme à colonnes correspondant.

【Création de graphiques】

La partie la plus difficile de l'organisation des données est terminée. Vous souvenez-vous encore de la façon dont nous avons inséré des graphiques lorsque nous avons créé un seul ensemble de données ? Dépêchez-vous et essayez-le !

Zone bleue : histogramme

Sélectionnez la zone A7:M13, insérez « histogramme empilé » et ajoutez des « étiquettes de données » aux séries A, B et C

Zone jaune : points de dispersion Figure

Suivez les étapes de conception d'un graphique pour un seul ensemble de données au début de l'article et répétez les étapes 2 et 3.

• Sélectionnez la zone B17:M17 et insérez un "nuage de points avec des lignes lisses et des marqueurs de données"

• Sélectionnez la zone B18:M18 et insérez un "nuage de points avec des lignes lisses et des marqueurs de données"

• Sélectionnez la zone B19: M19, insérez « Nuage de points avec ligne lisse et marqueurs de données »

Ajoutez « Étiquette de données »

Ajoutez « Étiquette de données » du nuage de points et sélectionnez « Étiquette de données » dans Dans « Définir le format de l'étiquette de données", configurez-le pour afficher le contenu en fonction de la "valeur dans la cellule" correspondante :

De cette façon, les élèves obtiendront le graphique suivant "rugueux".

【Post-arrangement】

Le travail de post-arrangement peut être effectué en se référant à la méthode "première suppression, deuxième ajustement, troisième rangée" décrite dans la photo de l'auteur E.

1. Supprimez « l'axe vertical » et les « graduations » dans le graphique, ainsi que les légendes qui n'ont pas besoin d'être affichées

2. la ligne lisse et supprimez ceux qui n'ont pas besoin d'être affichés. La colonne d'espace réservé et les points de marquage sur la ligne lisse sont définis sur aucune ligne ni aucun remplissage.

3. Grâce aux opérations ci-dessus, le graphique a essentiellement pris forme. Sélectionnez une série de diagramme à colonnes, ajustez la largeur de l'écart à 5 %,

Ajustez la position de l'étiquette de données sur le diagramme à colonnes. l'axe À l'intérieur, ajustez la position de l'étiquette de données sur la ligne lisse à centrer

Ajustez ensuite la position de chaque élément dans le graphique, et changez la police, la couleur de la police, etc. , pour compléter l'effet de notre photo ci-dessus.

【Note de l'éditeur】

Concernant la réalisation de graphiques, il est recommandé de ne pas utiliser tous les effets par défaut d'Excel. Un graphique ressemble plus à une conception graphique. L’esthétique de chacun est différente et les résultats sont différents. Cependant, puisque la « visualisation » est destinée à être vue par d’autres, elle doit être belle. Enfin, permettez-moi de partager la palette de couleurs utilisée dans le didacticiel graphique d'aujourd'hui. Entrez simplement la valeur RVB dans "Autres couleurs - Personnaliser".

Recommandations d'apprentissage associées : tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!