excel-sheet

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Skill Paths

技能路径

Workspace skills:
```
.github/skills/
```
Global skills:
```
C:/Users/LOQ/.agents/skills/
```

Comprehensive skill for working with Microsoft Excel spreadsheets using MCP servers.

工作区技能：
```
.github/skills/
```
全局技能：
```
C:/Users/LOQ/.agents/skills/
```

这是一款使用MCP服务器处理Microsoft Excel电子表格的综合技能。

When to Use This Skill

何时使用本技能

Any task where .xlsx is an input, output, or transformation target
Creating, editing, formatting, or analyzing Excel files
Building workbooks from data or templates
Applying consistent formatting and formulas
Automating Excel workflows via MCP tools

任何以.xlsx作为输入、输出或转换目标的任务
创建、编辑、格式化或分析Excel文件
从数据或模板构建工作簿
应用统一的格式和公式
通过MCP工具自动化Excel工作流

Typical Activation Triggers

典型激活触发词

.xlsx work: spreadsheets, workbooks, worksheets, data analysis
Cell operations: read, write, format, merge/unmerge
Worksheet management: create, copy, delete, rename
Analysis: charts, pivot tables, formulas

.xlsx相关工作：电子表格、工作簿、工作表、数据分析
单元格操作：读取、写入、格式化、合并/取消合并
工作表管理：创建、复制、删除、重命名
分析操作：图表、数据透视表、公式

Part 1: Excel Spreadsheets (.xlsx) via MCP

第一部分：通过MCP操作Excel电子表格（.xlsx）

Quick Reference

快速参考

Task	Activation / Tool
Create workbook; create/copy/delete/rename worksheets; add charts; add pivot tables	`activate_worksheet_management_tools`
Copy/delete/format/merge/unmerge cells; delete rows	`activate_cell_management_tools`
Insert/delete columns	`activate_column_management_tools`

Note: There is no
activate_workbook_management
— workbook creation is part of
activate_worksheet_management_tools
.

任务	激活方式/工具
创建工作簿；创建/复制/删除/重命名工作表；添加图表；添加数据透视表	`activate_worksheet_management_tools`
复制/删除/格式化/合并/取消合并单元格；删除行	`activate_cell_management_tools`
插入/删除列	`activate_column_management_tools`

注意： 没有
activate_workbook_management
工具 —— 工作簿创建功能包含在
activate_worksheet_management_tools
中。

Tool Activation Order

工具激活顺序

javascript

activate_worksheet_management_tools();  // workbook + sheet management, charts, pivot tables
activate_cell_management_tools();        // cell CRUD, formatting, merge/unmerge, delete rows
activate_column_management_tools();      // insert/delete columns

javascript

activate_worksheet_management_tools();  // workbook + sheet management, charts, pivot tables
activate_cell_management_tools();        // cell CRUD, formatting, merge/unmerge, delete rows
activate_column_management_tools();      // insert/delete columns

Workbook and Worksheet Operations

工作簿和工作表操作

javascript

// Create a new workbook
mcp_excel_create_workbook({ filename: "report.xlsx" })

// Create worksheets (call per sheet needed)
mcp_excel_create_worksheet({ filename: "report.xlsx", sheet_name: "Summary" })
mcp_excel_create_worksheet({ filename: "report.xlsx", sheet_name: "Raw Data" })
mcp_excel_create_worksheet({ filename: "report.xlsx", sheet_name: "Charts" })

// Copy a worksheet (good for applying template structure)
mcp_excel_copy_worksheet({ filename: "report.xlsx", source_sheet: "Summary", destination_sheet: "Summary_Q2" })

// Rename a worksheet
mcp_excel_rename_worksheet({ filename: "report.xlsx", old_name: "Sheet1", new_name: "Dashboard" })

// Delete a worksheet
mcp_excel_delete_worksheet({ filename: "report.xlsx", sheet_name: "Sheet1" })

javascript

// Create a new workbook
mcp_excel_create_workbook({ filename: "report.xlsx" })

// Create worksheets (call per sheet needed)
mcp_excel_create_worksheet({ filename: "report.xlsx", sheet_name: "Summary" })
mcp_excel_create_worksheet({ filename: "report.xlsx", sheet_name: "Raw Data" })
mcp_excel_create_worksheet({ filename: "report.xlsx", sheet_name: "Charts" })

// Copy a worksheet (good for applying template structure)
mcp_excel_copy_worksheet({ filename: "report.xlsx", source_sheet: "Summary", destination_sheet: "Summary_Q2" })

// Rename a worksheet
mcp_excel_rename_worksheet({ filename: "report.xlsx", old_name: "Sheet1", new_name: "Dashboard" })

// Delete a worksheet
mcp_excel_delete_worksheet({ filename: "report.xlsx", sheet_name: "Sheet1" })

Cell Content: Write, Copy, Delete

单元格内容：写入、复制、删除

Activate:

activate_cell_management_tools()

javascript

// Write a value or formula to a cell
mcp_excel_write_cell({ filename: "report.xlsx", sheet_name: "Summary", cell: "B2", value: "Revenue" })
mcp_excel_write_cell({ filename: "report.xlsx", sheet_name: "Summary", cell: "C2", value: 1800000 })
// Write a formula
mcp_excel_write_cell({ filename: "report.xlsx", sheet_name: "Summary", cell: "D2", value: "=C2-B2" })

// Copy a cell range to another location (e.g. duplicate template rows)
mcp_excel_copy_range({
  filename: "report.xlsx",
  sheet_name: "Raw Data",
  source_range: "A1:F1",           // header row
  destination_cell: "A100"          // paste starting cell
})

// Delete a cell range (clear content)
mcp_excel_delete_range({ filename: "report.xlsx", sheet_name: "Raw Data", range: "G2:G50" })

// Delete an entire row (rows shift up)
mcp_excel_delete_row({ filename: "report.xlsx", sheet_name: "Raw Data", row_index: 5 })

激活方式：
activate_cell_management_tools()

javascript

// Write a value or formula to a cell
mcp_excel_write_cell({ filename: "report.xlsx", sheet_name: "Summary", cell: "B2", value: "Revenue" })
mcp_excel_write_cell({ filename: "report.xlsx", sheet_name: "Summary", cell: "C2", value: 1800000 })
// Write a formula
mcp_excel_write_cell({ filename: "report.xlsx", sheet_name: "Summary", cell: "D2", value: "=C2-B2" })

// Copy a cell range to another location (e.g. duplicate template rows)
mcp_excel_copy_range({
  filename: "report.xlsx",
  sheet_name: "Raw Data",
  source_range: "A1:F1",           // header row
  destination_cell: "A100"          // paste starting cell
})

// Delete a cell range (clear content)
mcp_excel_delete_range({ filename: "report.xlsx", sheet_name: "Raw Data", range: "G2:G50" })

// Delete an entire row (rows shift up)
mcp_excel_delete_row({ filename: "report.xlsx", sheet_name: "Raw Data", row_index: 5 })

Cell Formatting

单元格格式化

javascript

// Format a single cell (header style)
mcp_excel_format_cell({
  filename: "report.xlsx",
  sheet_name: "Summary",
  cell: "A1",
  bold: true,
  font_size: 12,
  font_color: "FFFFFF",
  bg_color: "1B3A5C",
  alignment: "center",
  border: "thin"
})

// Format a range (apply consistent styling across headers)
mcp_excel_format_range({
  filename: "report.xlsx",
  sheet_name: "Summary",
  range: "A1:F1",
  bold: true,
  font_color: "FFFFFF",
  bg_color: "1B3A5C",
  alignment: "center"
})

// Format data rows (alternate shading — apply to even rows manually or via range loop)
mcp_excel_format_range({
  filename: "report.xlsx",
  sheet_name: "Summary",
  range: "A2:F2",
  bg_color: "D6EAF8"
})

javascript

// Format a single cell (header style)
mcp_excel_format_cell({
  filename: "report.xlsx",
  sheet_name: "Summary",
  cell: "A1",
  bold: true,
  font_size: 12,
  font_color: "FFFFFF",
  bg_color: "1B3A5C",
  alignment: "center",
  border: "thin"
})

// Format a range (apply consistent styling across headers)
mcp_excel_format_range({
  filename: "report.xlsx",
  sheet_name: "Summary",
  range: "A1:F1",
  bold: true,
  font_color: "FFFFFF",
  bg_color: "1B3A5C",
  alignment: "center"
})

// Format data rows (alternate shading — apply to even rows manually or via range loop)
mcp_excel_format_range({
  filename: "report.xlsx",
  sheet_name: "Summary",
  range: "A2:F2",
  bg_color: "D6EAF8"
})

Merge and Unmerge Cells

合并与取消合并单元格

javascript

// Merge cells for a section header spanning columns
mcp_excel_merge_cells({ filename: "report.xlsx", sheet_name: "Summary", range: "A1:F1" })

// Unmerge if editing is needed
mcp_excel_unmerge_cells({ filename: "report.xlsx", sheet_name: "Summary", range: "A1:F1" })

javascript

// Merge cells for a section header spanning columns
mcp_excel_merge_cells({ filename: "report.xlsx", sheet_name: "Summary", range: "A1:F1" })

// Unmerge if editing is needed
mcp_excel_unmerge_cells({ filename: "report.xlsx", sheet_name: "Summary", range: "A1:F1" })

Column Management

列管理

Activate:

activate_column_management_tools()

javascript

// Insert a blank column before column C (index 2, 0-based)
mcp_excel_insert_column({ filename: "report.xlsx", sheet_name: "Summary", column_index: 2 })

// Delete a column (e.g. remove a scratch column)
mcp_excel_delete_column({ filename: "report.xlsx", sheet_name: "Summary", column_index: 6 })

激活方式：
activate_column_management_tools()

javascript

// Insert a blank column before column C (index 2, 0-based)
mcp_excel_insert_column({ filename: "report.xlsx", sheet_name: "Summary", column_index: 2 })

// Delete a column (e.g. remove a scratch column)
mcp_excel_delete_column({ filename: "report.xlsx", sheet_name: "Summary", column_index: 6 })

Charts

图表

Activate:

activate_worksheet_management_tools()

javascript

// Add a chart to a worksheet
mcp_excel_create_chart({
  filename: "report.xlsx",
  sheet_name: "Charts",
  chart_type: "bar",         // bar | line | pie | column | area
  data_sheet: "Summary",
  data_range: "A1:D5",
  title: "Revenue by Quarter",
  position: { left: 1, top: 1, width: 8, height: 5 }  // inches
})

激活方式：

activate_worksheet_management_tools()

javascript

// Add a chart to a worksheet
mcp_excel_create_chart({
  filename: "report.xlsx",
  sheet_name: "Charts",
  chart_type: "bar",         // bar | line | pie | column | area
  data_sheet: "Summary",
  data_range: "A1:D5",
  title: "Revenue by Quarter",
  position: { left: 1, top: 1, width: 8, height: 5 }  // inches
})

Pivot Tables

数据透视表

javascript

// Create a pivot table from raw data
mcp_excel_create_pivot_table({
  filename: "report.xlsx",
  source_sheet: "Raw Data",
  source_range: "A1:F200",
  destination_sheet: "Pivot",
  destination_cell: "A3",
  rows: ["Region", "Product"],
  columns: ["Quarter"],
  values: [{ field: "Revenue", aggregation: "sum" }]
})

javascript

// Create a pivot table from raw data
mcp_excel_create_pivot_table({
  filename: "report.xlsx",
  source_sheet: "Raw Data",
  source_range: "A1:F200",
  destination_sheet: "Pivot",
  destination_cell: "A3",
  rows: ["Region", "Product"],
  columns: ["Quarter"],
  values: [{ field: "Revenue", aggregation: "sum" }]
})

End-to-End Excel Workbook Workflow

端到端Excel工作簿工作流

javascript

// ── 1. Activate ──────────────────────────────────────────────────
activate_worksheet_management_tools();
activate_cell_management_tools();
activate_column_management_tools();

// ── 2. Create workbook and sheets ────────────────────────────────
mcp_excel_create_workbook({ filename: "q3_report.xlsx" })
mcp_excel_rename_worksheet({ filename: "q3_report.xlsx", old_name: "Sheet1", new_name: "Summary" })
mcp_excel_create_worksheet({ filename: "q3_report.xlsx", sheet_name: "Raw Data" })
mcp_excel_create_worksheet({ filename: "q3_report.xlsx", sheet_name: "Charts" })

// ── 3. Write headers (Summary sheet) ─────────────────────────────
const headers = [["Region","Q1","Q2","Q3","Total","Change%"]]
// write each header cell then format the range
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"A1", value:"Region" })
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"F1", value:"Change%" })
mcp_excel_format_range({ filename:"q3_report.xlsx", sheet_name:"Summary", range:"A1:F1",
  bold:true, font_color:"FFFFFF", bg_color:"1B3A5C", alignment:"center" })

// ── 4. Write data rows + formulas ────────────────────────────────
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"A2", value:"APAC" })
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"B2", value:1200000 })
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"E2", value:"=SUM(B2:D2)" })
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"F2", value:"=(D2-B2)/B2" })

// ── 5. Style data rows (alternating) ─────────────────────────────
mcp_excel_format_range({ filename:"q3_report.xlsx", sheet_name:"Summary", range:"A2:F2", bg_color:"D6EAF8" })
mcp_excel_format_range({ filename:"q3_report.xlsx", sheet_name:"Summary", range:"A3:F3", bg_color:"FFFFFF" })

// ── 6. Add chart ─────────────────────────────────────────────────
mcp_excel_create_chart({ filename:"q3_report.xlsx", sheet_name:"Charts",
  chart_type:"column", data_sheet:"Summary", data_range:"A1:D5",
  title:"Revenue by Region & Quarter", position:{ left:1, top:1, width:9, height:5 } })

// ── 7. Pivot table ───────────────────────────────────────────────
mcp_excel_create_pivot_table({ filename:"q3_report.xlsx",
  source_sheet:"Raw Data", source_range:"A1:F200",
  destination_sheet:"Summary", destination_cell:"A20",
  rows:["Region"], columns:["Quarter"], values:[{ field:"Revenue", aggregation:"sum" }] })

javascript

// ── 1. Activate ──────────────────────────────────────────────────
activate_worksheet_management_tools();
activate_cell_management_tools();
activate_column_management_tools();

// ── 2. Create workbook and sheets ────────────────────────────────
mcp_excel_create_workbook({ filename: "q3_report.xlsx" })
mcp_excel_rename_worksheet({ filename: "q3_report.xlsx", old_name: "Sheet1", new_name: "Summary" })
mcp_excel_create_worksheet({ filename: "q3_report.xlsx", sheet_name: "Raw Data" })
mcp_excel_create_worksheet({ filename: "q3_report.xlsx", sheet_name: "Charts" })

// ── 3. Write headers (Summary sheet) ─────────────────────────────
const headers = [["Region","Q1","Q2","Q3","Total","Change%"]]
// write each header cell then format the range
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"A1", value:"Region" })
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"F1", value:"Change%" })
mcp_excel_format_range({ filename:"q3_report.xlsx", sheet_name:"Summary", range:"A1:F1",
  bold:true, font_color:"FFFFFF", bg_color:"1B3A5C", alignment:"center" })

// ── 4. Write data rows + formulas ────────────────────────────────
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"A2", value:"APAC" })
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"B2", value:1200000 })
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"E2", value:"=SUM(B2:D2)" })
mcp_excel_write_cell({ filename:"q3_report.xlsx", sheet_name:"Summary", cell:"F2", value:"=(D2-B2)/B2" })

// ── 5. Style data rows (alternating) ─────────────────────────────
mcp_excel_format_range({ filename:"q3_report.xlsx", sheet_name:"Summary", range:"A2:F2", bg_color:"D6EAF8" })
mcp_excel_format_range({ filename:"q3_report.xlsx", sheet_name:"Summary", range:"A3:F3", bg_color:"FFFFFF" })

// ── 6. Add chart ─────────────────────────────────────────────────
mcp_excel_create_chart({ filename:"q3_report.xlsx", sheet_name:"Charts",
  chart_type:"column", data_sheet:"Summary", data_range:"A1:D5",
  title:"Revenue by Region & Quarter", position:{ left:1, top:1, width:9, height:5 } })

// ── 7. Pivot table ───────────────────────────────────────────────
mcp_excel_create_pivot_table({ filename:"q3_report.xlsx",
  source_sheet:"Raw Data", source_range:"A1:F200",
  destination_sheet:"Summary", destination_cell:"A20",
  rows:["Region"], columns:["Quarter"], values:[{ field:"Revenue", aggregation:"sum" }] })

Part 2: Workbook Structure Standards

第二部分：工作簿结构标准

Excel Workbook Structure

Excel工作簿结构

Summary Sheet: Key metrics and insights
Data Source Sheet: Raw data
Calculations Sheet: Formulas and logic
Analysis Sheets: Pivot tables, charts
Documentation Sheet: Assumptions and notes

摘要工作表：关键指标和洞察
数据源工作表：原始数据
计算工作表：公式和逻辑
分析工作表：数据透视表、图表
文档工作表：假设条件和说明

Formatting Standards

格式化标准

Fonts

字体

Headings: Bold, larger (12-14pt)
Body: Readable size (10-11pt)
Consistency: Use same font families throughout

标题：加粗、字号较大（12-14pt）
正文：易读字号（10-11pt）
一致性：全程使用相同字体族

Colors

颜色

Headers: Brand color background, white text
Data rows: Alternating colors for readability
Highlights: Use for key metrics or alerts

表头：品牌色背景、白色文字
数据行：交替颜色以提升可读性
高亮：用于关键指标或提醒

Spacing

间距

Consistent column widths: Auto-fit or standard widths
Row heights: appropriate for content
Alignment: Left for text, right for numbers

统一列宽：自动适配或使用标准宽度
行高：与内容匹配
对齐方式：文本左对齐，数字右对齐

Part 3: Quality Checklist

第三部分：质量检查清单

markdown

undefined

markdown

undefined

Excel Quality Checklist

Structure

Separate sheets: Summary | Raw Data | Charts | (Pivot)
Sheet 1 renamed from "Sheet1" to "Summary" or "Dashboard"
Header row formatted (bold, brand color bg, white text)
Header row frozen (row 1) where needed

Separate sheets: Summary | Raw Data | Charts | (Pivot)
Sheet 1 renamed from "Sheet1" to "Summary" or "Dashboard"
Header row formatted (bold, brand color bg, white text)
Header row frozen (row 1) where needed

Data Integrity

Formulas used for totals/ratios (never hardcoded)
No merged cells inside data ranges (only in header/title areas)
Consistent column data types (all numbers, all dates, etc.)
Empty rows/columns cleaned up (delete_row / delete_column)

Formulas used for totals/ratios (never hardcoded)
No merged cells inside data ranges (only in header/title areas)
Consistent column data types (all numbers, all dates, etc.)
Empty rows/columns cleaned up (delete_row / delete_column)

Analysis

Chart added to Charts sheet with clear title and labeled axes
Pivot table added for large datasets
Alternating row shading applied for readability

Chart added to Charts sheet with clear title and labeled axes
Pivot table added for large datasets
Alternating row shading applied for readability

Validation

Formulas reference correct sheet and range (test with sample values)
Pivot table source range includes all data rows
Chart data range verified to match actual data

---

Formulas reference correct sheet and range (test with sample values)
Pivot table source range includes all data rows
Chart data range verified to match actual data

---

Part 4: Common Excel Formulas

第四部分：常用Excel公式

Lookup Formulas

查找公式

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Examples:

=VLOOKUP("SKU-100", A2:D50, 3, FALSE)
  → Find SKU-100 in column A, return value from column C

=VLOOKUP(B2, Products!A:E, 4, FALSE)
  → Cross-sheet lookup; find B2's value in Products sheet column A, return column D

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

示例：

=VLOOKUP("SKU-100", A2:D50, 3, FALSE)
  → Find SKU-100 in column A, return value from column C

=VLOOKUP(B2, Products!A:E, 4, FALSE)
  → Cross-sheet lookup; find B2's value in Products sheet column A, return column D

XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Examples:

=XLOOKUP("Widget", B2:B100, E2:E100, "Not found")
  → Search B column for "Widget", return corresponding E value

=XLOOKUP(TODAY(), A2:A100, B2:B100, , -1)
  → Find today's date or nearest earlier date, return column B

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

示例：

=XLOOKUP("Widget", B2:B100, E2:E100, "Not found")
  → Search B column for "Widget", return corresponding E value

=XLOOKUP(TODAY(), A2:A100, B2:B100, , -1)
  → Find today's date or nearest earlier date, return column B

Math Formulas

数学公式

SUM

=SUM(A1:A100)                    → Sum a range
=SUM(A1:A100, C1:C100)           → Sum multiple ranges

=SUM(A1:A100)                    → Sum a range
=SUM(A1:A100, C1:C100)           → Sum multiple ranges

SUMIF / SUMIFS

=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Examples:

=SUMIF(B2:B100, "Electronics", D2:D100)
  → Sum column D where column B = "Electronics"

=SUMIFS(E2:E100, B2:B100, "West", C2:C100, ">=2025-01-01")
  → Sum E where region is "West" AND date ≥ Jan 1 2025

=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

示例：

=SUMIF(B2:B100, "Electronics", D2:D100)
  → Sum column D where column B = "Electronics"

=SUMIFS(E2:E100, B2:B100, "West", C2:C100, ">=2025-01-01")
  → Sum E where region is "West" AND date ≥ Jan 1 2025

Text Formulas

文本公式

CONCATENATE / CONCAT / TEXTJOIN

=CONCATENATE(A1, " ", B1)          → "John Smith" (legacy)
=CONCAT(A1, " ", B1)               → Same, modern
=A1 & " " & B1                     → Operator shorthand

=TEXTJOIN(", ", TRUE, A1:A10)
  → Join non-empty cells with comma+space: "Alpha, Beta, Gamma"

=CONCATENATE(A1, " ", B1)          → "John Smith" (legacy)
=CONCAT(A1, " ", B1)               → Same, modern
=A1 & " " & B1                     → Operator shorthand

=TEXTJOIN(", ", TRUE, A1:A10)
  → Join non-empty cells with comma+space: "Alpha, Beta, Gamma"

Date Formulas

日期公式

TODAY / NOW

=TODAY()          → Current date (no time)
=NOW()            → Current date and time
=TODAY() + 30     → 30 days from today

=TODAY()          → Current date (no time)
=NOW()            → Current date and time
=TODAY() + 30     → 30 days from today

DATEDIF

=DATEDIF(start_date, end_date, unit)

Unit	Returns
"Y"	Complete years
"M"	Complete months
"D"	Days

Examples:

=DATEDIF(A1, TODAY(), "Y")         → Years since date in A1
=DATEDIF(A1, A2, "M")             → Months between two dates

=DATEDIF(start_date, end_date, unit)

单位	返回结果
"Y"	完整年数
"M"	完整月数
"D"	天数

示例：

=DATEDIF(A1, TODAY(), "Y")         → Years since date in A1
=DATEDIF(A1, A2, "M")             → Months between two dates

Statistical Formulas

统计公式

AVERAGE / MEDIAN

=AVERAGE(A1:A100)                → Arithmetic mean
=AVERAGEIF(B1:B100, ">0")       → Average of positive values only
=MEDIAN(A1:A100)                 → Middle value

=AVERAGE(A1:A100)                → Arithmetic mean
=AVERAGEIF(B1:B100, ">0")       → Average of positive values only
=MEDIAN(A1:A100)                 → Middle value

COUNT / COUNTA / COUNTIF / COUNTIFS

=COUNT(A1:A100)                                → Count numeric cells
=COUNTA(A1:A100)                               → Count non-empty cells
=COUNTIF(B1:B100, "Complete")                  → Count cells matching criteria
=COUNTIF(C1:C100, ">500")                      → Count cells > 500
=COUNTIFS(B1:B100, "East", C1:C100, ">1000")   → Multiple criteria

=COUNT(A1:A100)                                → Count numeric cells
=COUNTA(A1:A100)                               → Count non-empty cells
=COUNTIF(B1:B100, "Complete")                  → Count cells matching criteria
=COUNTIF(C1:C100, ">500")                      → Count cells > 500
=COUNTIFS(B1:B100, "East", C1:C100, ">1000")   → Multiple criteria

Conditional Formulas

条件公式

IF

=IF(condition, value_if_true, value_if_false)

Examples:

=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))
  → Nested grade assignment

=IF(AND(B1>0, C1>0), B1*C1, 0)
  → Multiply only if both positive

=IF(condition, value_if_true, value_if_false)

示例：

=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))
  → Nested grade assignment

=IF(AND(B1>0, C1>0), B1*C1, 0)
  → Multiply only if both positive

IFS (Excel 2019+)

IFS（Excel 2019+）

Evaluates multiple conditions in order — first TRUE wins.

=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")
  → Cleaner than nested IF; TRUE acts as default/else

按顺序评估多个条件 —— 第一个为真的条件生效。

=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")
  → Cleaner than nested IF; TRUE acts as default/else

Part 5: MCP Tool Activation Guide

第五部分：MCP工具激活指南

Activation Commands

激活命令

When working with Excel spreadsheets, use these MCP activation patterns:

javascript

// Excel documents (no activate_workbook_management — worksheet tools cover workbook creation)
activate_worksheet_management_tools();  // create workbook, worksheets, charts, pivot tables
activate_cell_management_tools();        // write/copy/delete cells, formatting, merge/unmerge
activate_column_management_tools();      // insert/delete columns

处理Excel电子表格时，使用以下MCP激活模式：

javascript

// Excel documents (no activate_workbook_management — worksheet tools cover workbook creation)
activate_worksheet_management_tools();  // create workbook, worksheets, charts, pivot tables
activate_cell_management_tools();        // write/copy/delete cells, formatting, merge/unmerge
activate_column_management_tools();      // insert/delete columns

Workflow Examples

工作流示例

Template-Based Workbook Generation

基于模板的工作簿生成

Start with template (Excel)
Copy template to new filename
Write data to appropriate cells
Apply formulas for calculations
Apply formatting consistently across sheets
Save final version

从模板开始（Excel文件）
复制模板到新文件
写入数据到对应单元格
应用公式进行计算
统一应用格式到所有工作表
保存最终版本

Part 6: Best Practices

第六部分：最佳实践

MCP-Specific Best Practices

MCP专属最佳实践

Activate tools as needed: Enable MCP tool groups only when required to avoid unnecessary overhead
Validate operations: Confirm operations completed successfully, especially for batch operations
Handle errors gracefully: Catch and report MCP tool errors with context for troubleshooting
Batch operations: Use array-based operations when available for efficiency
Document file paths: Use clear, relative paths and maintain documentation of file locations

按需激活工具：仅在需要时启用MCP工具组，避免不必要的开销
验证操作：确认操作成功完成，尤其是批量操作
优雅处理错误：捕获并报告MCP工具错误，同时提供故障排查上下文
批量操作：尽可能使用基于数组的操作以提升效率
记录文件路径：使用清晰的相对路径，并记录文件位置

Excel Best Practices

Excel最佳实践

Use formulas: Never hardcode calculated values
Consistent formatting: Apply same styles across sheets
Clear naming: Use descriptive sheet and range names
Data validation: Use dropdown lists and constraints
Test formulas: Verify with sample data before distribution

使用公式：绝不硬编码计算值
统一格式：在所有工作表中应用相同样式
清晰命名：使用描述性的工作表和区域名称
数据验证：使用下拉列表和约束条件
测试公式：分发前使用样本数据验证

References & Resources

参考资料

Documentation

文档

Excel Formulas Reference — Excel formula patterns and Power Query M basics

Excel Formulas Reference — Excel公式模式和Power Query M基础

Examples

示例

Excel Workbook Examples — Examples of creating workbooks programmatically

Excel Workbook Examples — 程序化创建工作簿的示例

Scripts

脚本

CSV to XLSX Converter — Python script to convert CSV files to formatted Excel workbooks

CSV to XLSX Converter — 将CSV文件转换为格式化Excel工作簿的Python脚本

Usage

使用方法

bash

undefined

bash

undefined

Basic usage

python csv-to-xlsx.py input.csv

Specify output file

python csv-to-xlsx.py input.csv -o output.xlsx

Custom header colors

python csv-to-xlsx.py input.csv --header-color 2E74B5 --header-font-color FFFFFF

Generate chart from numeric columns

python csv-to-xlsx.py input.csv --chart --chart-type bar

undefined

python csv-to-xlsx.py input.csv --chart --chart-type bar

undefined

Requirements

依赖要求

bash

pip install openpyxl

bash

pip install openpyxl