Excel 自动化报表 #

┌─────────────────────────────────────────────────────────────┐
│                    自动化报表要素                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  1. 数据源：原始数据表                                       │
│  2. 计算层：公式和函数                                       │
│  3. 展示层：报表和图表                                       │
│  4. 交互层：下拉选择、切片器                                 │
│                                                             │
└─────────────────────────────────────────────────────────────┘

将数据转换为表格（Ctrl+T）：

优点：
- 自动扩展范围
- 公式自动填充
- 结构化引用
- 便于维护

示例：
=SUM(表1[销售额])  而不是 =SUM(B2:B100)

使用 OFFSET 创建动态范围：
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

使用 INDEX 创建动态范围：
=Sheet1!$A$1:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))

Excel 365 使用动态数组：
=A1#（溢出范围引用）

按部门汇总销售额：
=SUMIFS(数据[销售额], 数据[部门], A1)

按月份汇总：
=SUMIFS(数据[销售额], 数据[日期], ">="&B1, 数据[日期], "<="&EOMONTH(B1, 0))

按产品汇总：
=SUMIFS(数据[销售额], 数据[产品], C1)

创建下拉列表：
数据 → 数据验证 → 序列

数据源：
=$A$1:$A$10  或  =INDIRECT("列表")

使用选择值：
=SUMIFS(数据[销售额], 数据[部门], $H$1)

="销售报表 - " & TEXT(TODAY(), "yyyy年mm月dd日")

="部门：" & H1 & " 销售额：" & SUMIFS(...)

根据选择显示不同数据：
=IF(H1="全部", SUM(数据[销售额]), SUMIFS(数据[销售额], 数据[部门], H1))

多条件选择：
=SUMIFS(数据[销售额], 
        IF(H1<>"全部", 数据[部门], 数据[部门]), 
        IF(H1<>"全部", H1, 数据[部门]))

关键指标展示：

    A              B
1   总销售额       =SUM(数据[销售额])
2   平均销售额     =AVERAGE(数据[销售额])
3   最高销售额     =MAX(数据[销售额])
4   最低销售额     =MIN(数据[销售额])
5   记录数         =COUNTA(数据[销售额])

同比/环比：
=TEXT((本月-上月)/上月, "0.0%")

计算增长率：
=(本期-上期)/上期

累计值：
=SUM($B$2:B2)

移动平均：
=AVERAGE(OFFSET(B2, 0, 0, 7, 1))  7天移动平均

前N名：
=LARGE(数据[销售额], 1)  第1名
=LARGE(数据[销售额], 2)  第2名
=LARGE(数据[销售额], 3)  第3名

对应名称：
=INDEX(数据[产品], MATCH(LARGE(数据[销售额], 1), 数据[销售额], 0))

Excel 365：
=SORT(数据, 数据[销售额], -1)  按销售额降序
=INDEX(SORT(数据, 数据[销售额], -1), SEQUENCE(10), {1, 2})  前10名

数据源（数据表）：
    A          B          C          D
1   日期       部门       产品       销售额

报表：
    A              B
1   报表月份       2024年1月（下拉选择）
2
3   本月销售额     =SUMIFS(数据[销售额], 
                            数据[日期], ">="&DATE(YEAR($B$1), MONTH($B$1), 1),
                            数据[日期], "<="&EOMONTH($B$1, 0))
4   本月订单数     =COUNTIFS(数据[日期], ">="&..., 数据[日期], "<="&...)
5   平均订单额     =B3/B4
6
7   部门销售排名：
8   部门           销售额
9   销售           =SUMIFS(数据[销售额], 数据[部门], A9, ...)
10  技术           =SUMIFS(数据[销售额], 数据[部门], A10, ...)

数据源：
    A          B          C          D
1   产品       库存       安全库存   状态
2   产品A      50         100        =IF(B2<C2, "需补货", "正常")
3   产品B      200        100        =IF(B3<C3, "需补货", "正常")

预警汇总：
需补货产品数：=COUNTIF(D:D, "需补货")
需补货产品列表：=FILTER(A:A, D:D="需补货")

条件格式：
D列="需补货" → 红色填充

数据源：
    A          B          C
1   员工       日期       状态
2   张三       2024-01-01 出勤
3   张三       2024-01-02 请假

统计表：
    A          B          C          D          E
1   员工       出勤天数   请假天数   迟到天数   状态
2   张三       =COUNTIFS(A:A, A2, C:C, "出勤")
              =COUNTIFS(A:A, A2, C:C, "请假")
              =COUNTIFS(A:A, A2, C:C, "迟到")
              =IF(B2>=20, "正常", "异常")

数据源：
    A          B          C          D
1   客户       日期       金额       产品

分析表：
    A              B
1   客户数         =COUNTA(UNIQUE(数据[客户]))
2   总销售额       =SUM(数据[金额])
3   平均客单价     =B2/B1
4   复购率         =COUNTIF(客户购买次数, ">1")/B1
5
6   客户分级：
7   A级客户（>10万）=COUNTIF(数据!E:E, "A级")
8   B级客户（5-10万）=COUNTIF(数据!E:E, "B级")
9   C级客户（<5万） =COUNTIF(数据!E:E, "C级")

数据源：
    A          B          C          D          E
1   项目       开始日期   结束日期   进度%      状态
2   项目A      2024-01-01 2024-03-31 80%        =IF(D2=100%, "完成", 
                                                 IF(TODAY()>C2, "逾期", 
                                                 IF(TODAY()>=B2, "进行中", "未开始")))

进度汇总：
完成项目：=COUNTIF(E:E, "完成")
进行中：=COUNTIF(E:E, "进行中")
逾期：=COUNTIF(E:E, "逾期")

甘特图数据：
开始天数：=B2-项目开始日期
持续天数：=C2-B2

报表日期：=TODAY()
报表月份：=TEXT(TODAY(), "yyyy年mm月")
报表周期：=TEXT(EOMONTH(TODAY(), -1)+1, "yyyy年mm月") & " - " & 
          TEXT(EOMONTH(TODAY(), 0), "yyyy年mm月")

本月销售额：=SUMIFS(...)
上月销售额：=SUMIFS(..., 上月条件)
同比增长：=(本月-上月)/上月

使用条件格式：
=AND(截止日期-TODAY()<=7, 状态<>"完成")

7天内到期且未完成的项目显示红色