Excel多边形挑战：如何通过X、Y坐标创建扩展水平条形图

Step 1: Define Core Layout Parameters #

First, set up adjustable variables to control the chart's size, spacing, and scale—tweak these based on your desired output dimensions:

• categories: Your list of categories paired with their percentages (e.g., [("A", 20), ("B", 40), ("C", 50), ("D", 70), ("E", 90)])
• max_percentage: The X-axis maximum (100 in your case, since you're extending bars to full percentage)
• category_spacing: Vertical distance between the center of each bar (e.g., 40 units)
• bar_height: Height of each horizontal bar (e.g., 20 units)
• start_y: Y-coordinate for the center of the topmost category (e.g., 30 units from the top edge)
• start_x: Left edge of the chart (0, since we're using percentage values directly for X)

Step 2: Calculate 7 Coordinates Per Category #

Each category’s 7 coordinate pairs define the full extended bar shape:

1. Left bottom of the vertical anchor line
2. Left top of the vertical anchor line
3. Top right corner of the filled bar segment
4. Bottom right corner of the filled bar segment
5. Bottom end of the extended line (to max percentage)
6. Top end of the extended line
7. Back to the top right of the filled bar (to close the extended line’s top segment)

Example Implementation (Python) #

def generate_extended_bar_coords(categories, max_percent=100, category_spacing=40, bar_height=20, start_y=30):
    chart_coordinates = {}
    for idx, (category, percent) in enumerate(categories):
        # Calculate Y positions for this category
        y_center = start_y + idx * category_spacing
        y_top = y_center + bar_height / 2
        y_bottom = y_center - bar_height / 2
        
        # Generate the 7 required coordinate pairs
        category_points = [
            (0, y_bottom),          # 1. Left bottom anchor
            (0, y_top),             # 2. Left top anchor
            (percent, y_top),       # 3. Filled bar top right
            (percent, y_bottom),    # 4. Filled bar bottom right
            (max_percent, y_bottom),# 5. Extended line bottom end
            (max_percent, y_top),   # 6. Extended line top end
            (percent, y_top)        # 7. Return to filled bar top right
        ]
        chart_coordinates[category] = category_points
    return chart_coordinates

# Test with your categories
my_categories = [("A", 20), ("B", 40), ("C", 50), ("D", 70), ("E", 90)]
result = generate_extended_bar_coords(my_categories)

# Print output
for cat, points in result.items():
    print(f"Category {cat}:")
    for i, (x, y) in enumerate(points, 1):
        print(f"  Point {i}: ({x}, {y})")

Step 3: Rendering Tips #

Once you have the coordinates, use them to draw the chart with your preferred tool:

• Filled bar: Use points 2 → 3 → 4 → 1 to draw a rectangle and apply a fill color.
• Vertical anchor: Draw a solid line between points 1 and 2.
• Extended line: Draw dashed lines between points 3→6 and 4→5 to distinguish it from the filled bar.
• Scaling: If you need pixel coordinates for a specific canvas, multiply X values by (canvas_width / max_percent) and adjust Y values to fit your canvas height.

Customization Ideas #

• Adjust category_spacing and bar_height to make the chart more compact or spacious.
• Add small tick marks at points 5 and 6 for visual clarity.
• For interactive charts (e.g., with D3.js), use these coordinates to build SVG path elements dynamically.

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