mcpField
E
excel-analysis

excel-analysis

github.com/spjoshis数据与数据库7111 次安装38 周前更新
代码仓库

关于 excel-analysis

面向业务分析人员的 Excel 分析技能，适用于临时分析到利益相关方报告等工作。内容涵盖数据透视表、公式、Power Query 转换，以及维护可靠工作簿的实践。

主要功能

  1. 01构建数据透视表分析
  2. 02使用查找与汇总公式
  3. 03通过 Power Query 转换数据
  4. 04使用结构化表和命名区域
  5. 05验证数据并记录假设

适用场景

  1. 01按地区和月份分析销售额
  2. 02清理并转换 CSV 数据
  3. 03制作财务模型和业务计算
  4. 04准备利益相关方报告

安装

复制下面的命令，在终端中运行。

$npx skills add https://github.com/spjoshis/claude-code-plugins --skill excel-analysis

安装后在 Claude Code 中输入/excel-analysis确认已加载。

详细介绍

Excel 分析

掌握使用数据透视表、公式、Power Query 和高级功能进行数据分析，以开展业务分析。

何时使用此技能

  • 临时分析
  • 快速报告
  • 数据清理
  • 财务建模
  • 业务计算
  • 数据转换
  • 利益相关者报告
  • 自助式分析

核心概念

1. 数据透视表
## Sales Analysis Pivot Table

**Source Data**: Sales transactions (Date, Region, Product, Sales Rep, Amount)

**Pivot Table Setup**:
- Rows: Region, Sales Rep
- Columns: Month (Date grouped by month)
- Values: Sum of Amount
- Filters: Product, Date Range

**Calculated Fields**:
- Average Sale = Sum of Amount / Count of Transactions
- Target vs Actual = Sum of Amount - Target
- % of Total = Sum of Amount / Grand Total

**Formatting**:
- Currency format for amounts
- Percentage for % of Total
- Conditional formatting: Above/below average
2. 实用公式
# VLOOKUP - Lookup customer name from ID
=VLOOKUP(A2, Customers!A:B, 2, FALSE)

# SUMIFS - Sum sales for specific region and product
=SUMIFS(Sales, Region, "West", Product, "Widget")

# INDEX/MATCH - More flexible than VLOOKUP
=INDEX(Customers!B:B, MATCH(A2, Customers!A:A, 0))

# Array Formula - Count unique values
=SUM(1/COUNTIF(A2:A100, A2:A100))

# TEXTJOIN - Combine values with delimiter
=TEXTJOIN(", ", TRUE, A2:A10)

# IFS - Multiple conditions
=IFS(A2>90, "A", A2>80, "B", A2>70, "C", TRUE, "F")

# Date calculations
=EDATE(A2, 3)  # Add 3 months
=NETWORKDAYS(A2, B2)  # Business days between dates
3. Power Query
## ETL with Power Query

**Transform Sales Data**:
1. Load data from CSV
2. Remove duplicates
3. Filter out cancelled orders
4. Split full name into first/last
5. Change date format
6. Group by customer, sum amounts
7. Add calculated column: Tier (based on total)
8. Load to Excel table

**M Code Example**:
```m
let
    Source = Csv.Document(File.Contents("sales.csv")),
    RemoveDuplicates = Table.Distinct(Source),
    FilterCancelled = Table.SelectRows(RemoveDuplicates, each [Status] <> "Cancelled"),
    SplitName = Table.SplitColumn(FilterCancelled, "Name", Splitter.SplitTextByDelimiter(" "), {"FirstName", "LastName"}),
    GroupedRows = Table.Group(SplitName, {"CustomerID"}, {{"TotalSales", each List.Sum([Amount]), type number}}),
    AddTier = Table.AddColumn(GroupedRows, "Tier", each if [TotalSales] > 10000 then "Gold" else if [TotalSales] > 5000 then "Silver" else "Bronze")
in
    AddTier


## Best Practices

1. **Use tables** - Structured references, auto-expansion
2. **Name ranges** - Formulas more readable
3. **Avoid merged cells** - Breaks sorting, filtering
4. **Document assumptions** - Comments, separate tab
5. **Validate data** - Data validation rules
6. **Use shortcuts** - Ctrl+T (table), Alt+D+P (pivot)
7. **Power Query for ETL** - Repeatable transformations
8. **Version control** - Save versions, track changes

## Resources

- **Excel Jet**: Formula reference and tips
- **Mr. Excel**: Advanced Excel techniques

评论

支持 Markdown