# Excel Bar Chart Visualization

读取多工作表Excel文件，自动处理合并单元格与数据清洗，进行交叉分组统计并生成带总计行的结果表，最后绘制支持中英文字体的美化柱状图，适用于多维度数据汇总与可视化分析。

- Repository: https://github.com/OpenSenseNova/SenseNova-Skills (6002 stars)
- Category: Engineering
- License: MIT
- Last updated: 2026-10-29
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add OpenSenseNova/SenseNova-Skills --skill excel-bar-chart-visualization -g -a claude-code -y`
Codex: `npx skills add OpenSenseNova/SenseNova-Skills --skill excel-bar-chart-visualization -g -a codex -y`
Cursor: `npx skills add OpenSenseNova/SenseNova-Skills --skill excel-bar-chart-visualization -g -a cursor -y`
Gemini CLI: `npx skills add OpenSenseNova/SenseNova-Skills --skill excel-bar-chart-visualization -g -a gemini-cli -y`

## Description

读取多工作表Excel文件，自动处理合并单元格与数据清洗，进行交叉分组统计并生成带总计行的结果表，最后绘制支持中英文字体的美化柱状图，适用于多维度数据汇总与可视化分析。

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

## Skill Steps

> This sub-skill covers one capability of the Excel workflow. For reading/counting/Parquet optimization, see the parent workflow SKILL.md.

### Step1: 数据合并与清洗
```python
combined_df = pd.concat(data_frames, ignore_index=True)

# 数据清洗：使用正则表达式统一命名
if '题型' in combined_df.columns:
    combined_df['题型'] = combined_df['题型'].astype(str).str.replace('判', '判断题', regex=False)

# 处理合并单元格技巧1：前向填充
if '流程描述' in combined_df.columns:
    combined_df['流程描述'] = combined_df['流程描述'].fillna(method='ffill')

# 处理合并单元格技巧2：通过逻辑判断与手动映射还原完整名称
group_col = '项目阶段'
target_col = '控制要点'
if group_col in combined_df.columns and target_col in combined_df.columns:
    project_stages, control_points = [], []
    current_stage = None
    for _, row in combined_df.iterrows():
        stage = row[group_col]
        point = row[target_col]
        if pd.notna(point) and point != target_col:
            if pd.notna(stage):
                current_stage = stage
            project_stages.append(current_stage)
            control_points.append(point)
    combined_df = pd.DataFrame({
        group_col: project_stages,
        target_col: control_points
    })
```

### Step2: 交叉分析与分类映射
```python
# 分类映射函数骨架
if group_col in combined_df.columns:
    stage_mapping = {
        '碎片值1': '标准分类A',
        '碎片值2': '标准分类A',
        '碎片值3': '标准分类B',
        '异常值': '其他'
    }
    combined_df[f'{group_col}_合并'] = combined_df[group_col].map(stage_mapping).fillna('其他')
    grouped_stats = combined_df.groupby(f'{group_col}_合并')[target_col].count().sort_values(ascending=False)
elif '题目分类' in combined_df.columns and '题型' in combined_df.columns:
    # 交叉分析 crosstab/pivot
    grouped_stats = combined_df.groupby(['题目分类', '题型']).size().unstack(fill_value=0)
else:
    grouped_stats = combined_df.groupby(combined_df.columns[0]).size()
```

### Step3: 统计结果输出与下载
```python
import tempfile
import os

output_path = os.path.join(tempfile.gettempdir(), "统计结果.xlsx")

The rest: https://github.com/OpenSenseNova/SenseNova-Skills/blob/HEAD/skills/sn-da-excel-workflow/capability/excel-data-visualization/bar-chart-visualization/SKILL.md