Excel混合柱状图制作请求：堆叠系列嵌入Intake系列

Got it, let's build exactly the chart you're describing—where your three stacked series ("Adoptions", "Best Friends", "New Hope") sit inside the larger orange "Intake" bars, making their proportion of total intake crystal clear. This is a great way to visualize both subcategory contributions and their relationship to the overall intake metric.

I'll walk you through two common implementation options: Plotly (interactive, great for dashboards) and Matplotlib (static, perfect for reports).

Option 1: Interactive Chart with Plotly #

This approach uses an overlayed bar setup—we'll make the "Intake" bar semi-transparent as a background, then stack the three subcategories on top of each other in the same position.

import plotly.graph_objects as go
import pandas as pd

# Sample data (replace with your actual data)
data = pd.DataFrame({
    'Month': ['Jan', 'Feb', 'Mar', 'Apr'],
    'Intake': [150, 180, 200, 170],
    'Adoptions': [60, 75, 80, 70],
    'Best Friends': [40, 50, 55, 45],
    'New Hope': [30, 35, 40, 30]
})

fig = go.Figure()

# Add the orange Intake bar as a semi-transparent background
fig.add_trace(go.Bar(
    x=data['Month'],
    y=data['Intake'],
    name='Intake',
    marker_color='#ff7f0e',  # Orange color
    opacity=0.3  # Transparency lets stacked bars show through
))

# Add the three stacked subcategory bars
fig.add_trace(go.Bar(
    x=data['Month'],
    y=data['Adoptions'],
    name='Adoptions',
    marker_color='#1f77b4'
))

fig.add_trace(go.Bar(
    x=data['Month'],
    y=data['Best Friends'],
    name='Best Friends',
    marker_color='#2ca02c'
))

fig.add_trace(go.Bar(
    x=data['Month'],
    y=data['New Hope'],
    name='New Hope',
    marker_color='#d62728'
))

# Configure layout for overlay and stacking
fig.update_layout(
    barmode='overlay',
    bargap=0.15,  # Space between month columns
    title='Intake vs. Subcategory Contributions',
    xaxis_title='Month',
    yaxis_title='Count',
    legend=dict(orientation='h', yanchor='bottom', y=1.02, xanchor='right', x=1)
)

# Group the three subcategories into one stack, separate from Intake
fig.update_traces(offsetgroup=1, selector=dict(name=['Adoptions', 'Best Friends', 'New Hope']))
fig.update_traces(offsetgroup=0, selector=dict(name='Intake'))

fig.show()

How this works: #

• The semi-transparent orange "Intake" bar acts as a reference for total intake each period.
• The three subcategories are stacked together in the same position, so their combined height shows how much of intake they account for.
• Any gap between the stacked top and the Intake bar height represents unaccounted-for intake (if applicable).

Option 2: Static Chart with Matplotlib #

If you prefer a static, publication-ready chart, Matplotlib works perfectly. We'll plot the Intake bar first as a background, then stack the three subcategories on top.

import matplotlib.pyplot as plt
import pandas as pd

# Sample data (replace with your actual data)
data = pd.DataFrame({
    'Month': ['Jan', 'Feb', 'Mar', 'Apr'],
    'Intake': [150, 180, 200, 170],
    'Adoptions': [60, 75, 80, 70],
    'Best Friends': [40, 50, 55, 45],
    'New Hope': [30, 35, 40, 30]
})

x = range(len(data['Month']))
bar_width = 0.35

fig, ax = plt.subplots(figsize=(10, 6))

# Plot orange Intake bar as background
ax.bar(x, data['Intake'], bar_width, label='Intake', color='#ff7f0e', alpha=0.3)

# Stack the three subcategories
bottom_values = [0] * len(data)

# Adoptions
ax.bar(x, data['Adoptions'], bar_width, label='Adoptions', color='#1f77b4', bottom=bottom_values)
bottom_values = [a + b for a, b in zip(bottom_values, data['Adoptions'])]

# Best Friends
ax.bar(x, data['Best Friends'], bar_width, label='Best Friends', color='#2ca02c', bottom=bottom_values)
bottom_values = [a + b for a, b in zip(bottom_values, data['Best Friends'])]

# New Hope
ax.bar(x, data['New Hope'], bar_width, label='New Hope', color='#d62728', bottom=bottom_values)

# Customize the chart
ax.set_xticks(x)
ax.set_xticklabels(data['Month'])
ax.set_title('Intake vs. Subcategory Contributions', fontsize=14)
ax.set_xlabel('Month', fontsize=12)
ax.set_ylabel('Count', fontsize=12)
ax.legend()

plt.tight_layout()
plt.show()

Key details here: #

• The bottom parameter handles the stacking—each subsequent bar starts where the previous one ended.
• The semi-transparent Intake bar lets you instantly compare the total stacked subcategories to the full intake value.

Either approach will give you the exact mixed stacked-embedded chart you need. Just swap out the sample data with your actual values, and adjust colors/layout to match your preferences!

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