Got it, let's walk through recreating that Excel-style stacked bar chart using your dataset. I'll use R's ggplot2 package (part of the tidyverse) since it's flexible enough to match Excel's typical look while keeping the code clean. Here's the step-by-step breakdown:

Step 1: Load Required Packages #

First, we'll load the tidyverse bundle—it includes ggplot2 for plotting and tidyr to reshape our data into a format ggplot prefers:

library(tidyverse)

Step 2: Define Your Dataset #

Let's start by defining the exact dataset you provided:

df <- data.frame(
  year = c(rep(2013, 4), rep(2014, 4), rep(2015, 3)),
  quarter = c(rep(c("Q1", "Q2", "Q3", "Q4"), 2), c("Q1", "Q2", "Q3")),
  Miss = c(5, 3, 7, 4, 6, 5.7, 12, 15, 20, 33, 42),
  Meet = c(100-40-5, 100-37-3, 100-29-7,100-28-4, 100-20-6, 100-29-5.7, 100-27-12, 100-25-15, 100-13-20, 100-5-33, 100-12-42),
  Exceed = c(40, 37, 29, 28, 20, 29, 27, 25, 13, 5, 12)
)

Step 3: Reshape Data to Long Format #

Excel often works with wide data, but ggplot performs best with long-format data (one row per observation). We'll use pivot_longer to restructure our performance categories into a single column:

df_long <- df %>%
  pivot_longer(
    cols = c(Miss, Meet, Exceed), 
    names_to = "Performance", 
    values_to = "Percentage"
  )

Step 4: Build the Stacked Bar Chart #

Now we'll create the stacked bar chart, adding labels inside each segment, adjusting colors to match Excel's typical palette, and adding your footnote:

ggplot(df_long, aes(x = interaction(year, quarter, sep = " "), y = Percentage, fill = Performance)) +
  # Create stacked bars
  geom_col(position = "stack") +
  # Add percentage labels centered in each bar segment
  geom_text(
    aes(label = paste0(Percentage, "%")), 
    position = position_stack(vjust = 0.5),
    size = 3.5
  ) +
  # Use a color palette similar to Excel's default
  scale_fill_brewer(palette = "Set1") +
  # Add titles and labels
  labs(
    title = "Performance Metrics by Year and Quarter",
    x = "Year-Quarter",
    y = "Percentage (%)",
    fill = "Performance Category",
    caption = "Data source: [Insert your specific source here]"
  ) +
  # Use a clean theme close to Excel's style
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold"),
    # Rotate x-axis labels to prevent overlap
    axis.text.x = element_text(angle = 45, hjust = 1),
    # Style the footnote (caption)
    plot.caption = element_text(hjust = 0, size = 10, color = "gray50")
  )

Key Notes: #

• The interaction(year, quarter) combines year and quarter into a single x-axis label (e.g., "2013 Q1") for clarity.
• position_stack(vjust = 0.5) ensures percentage labels are perfectly centered in each bar segment.
• You can swap out the Set1 palette for any other Excel-like colors using scale_fill_manual(values = c("#color1", "#color2", "#color3")) if you need an exact match.

内容的提问来源于stack exchange，提问作者Seymour