Excel高频函数+序号填充全实操指南

核心内容：TOCOL、N、SUMPRODUCT、PIVOTBY四大函数详解+多场景序号填充方法，全流程附实战案例，可直接套用。

一、TOCOL函数：多行多列转一维数组

功能介绍

Excel 365动态数组函数，可将多行多列区域、多区域数据，按指定规则转换为**单列一维数组**，支持忽略空值、错误值、隐藏单元格，解决数据规整、汇总提取难题，替代传统复杂嵌套公式，大幅提升数据整理效率。

基础语法

=TOCOL(数据区域, [忽略类型], [扫描方式])

•忽略类型：0=不忽略，1=忽略空值，2=忽略错误值，3=忽略空值+错误值；

•扫描方式：1=按行扫描（默认），2=按列扫描。

场景实战（3个）

1.多区域数据汇总：将多个分散的销售数据区域转为单列，便于后续统计、排序，公式：=TOCOL(A2:C5,E2:G6,1)，一键合并多块数据；

2.二维表转单列清单：把月度二维销售表转为单列流水数据，适配数据透视、筛选，公式：=TOCOL(B2:M10,1)；

3.提取有效数据：忽略表格空白单元格与错误值，仅提取有效内容，公式：=TOCOL(A2:D20,3)，避免无效数据干扰。

二、N函数：数据类型转换+辅助计算

功能介绍

轻量型转换函数，将内容转为**数值格式**：文本/逻辑值FALSE转0，TRUE转1，数字保持不变，错误值返回错误；常作为辅助函数，搭配其他函数规避计算报错、简化逻辑判断、优化公式运行速度。

基础语法

=N(值/单元格/表达式)

场景实战（3个）

1.规避文本型数据求和报错：统计带文本的成绩列，公式：=SUM(N(B2:B20))，自动将文本转为0，不影响求和结果；

2.逻辑判断计数：统计成绩≥60分人数，公式：=SUM(N(B2:B20>=60))，TRUE转1、FALSE转0，直接求和计数；

3.公式注释辅助：在公式后加+N(“注释内容”)，不影响计算结果，方便后续复盘公式用途。

三、SUMPRODUCT函数：多条件求和/统计全能王

功能介绍

传统Excel全能函数，无需数组快捷键，支持**多条件求和、计数、乘积求和、加减乘除运算**，兼容所有Excel版本，核心逻辑：先将区域内数据相乘，再对乘积求和，搭配条件判断可实现各类复杂统计，替代SUMIF/COUNTIF多条件嵌套。

基础语法

=SUMPRODUCT(数组1, [数组2],…)；多条件写法：=SUMPRODUCT((条件1)*(条件2)*求和区域/计数区域)

场景实战（8个全覆盖）

1.单条件求和：统计销售部总业绩，=SUMPRODUCT((A2:A20=”销售部”)*B2:B20)；

2.多条件求和：统计销售部3月份业绩，=SUMPRODUCT((A2:A20=”销售部”)*(C2:C20=”3月”)*B2:B20)；

3.单条件计数：统计本科员工人数，=SUMPRODUCT((D2:D20=”本科”)*1)；

4.多条件计数：统计技术部本科员工数，=SUMPRODUCT((A2:A20=”技术部”)*(D2:D20=”本科”)*1)；

5.乘积求和：计算商品总价（单价×数量），=SUMPRODUCT(B2:B20,C2:C20)；

6.区间条件统计：统计60-80分人数，=SUMPRODUCT((E2:E20>=60)*(E2:E20<=80)*1)；

7.排除空值统计：统计非空业绩数据行数，=SUMPRODUCT(N(B2:B20<>””))；

8.跨表条件求和：多表统计同部门业绩，=SUMPRODUCT((Sheet1!A2:A20=”销售部”)*Sheet1!B2:B20)。

四、PIVOTBY函数：动态轻量数据透视

功能介绍

Excel 365专属动态数组函数，实现**轻量级动态数据透视**，无需创建数据透视表，按指定行/列字段分组，完成求和、计数、平均值等统计，数据更新后自动刷新结果，适配临时快速汇总场景。

基础语法

=PIVOTBY(行字段, 列字段, 值字段, 汇总方式, [参数])

场景实战（2个）

1.按部门统计业绩：一键汇总各部门总业绩，=PIVOTBY(A2:A20,,B2:B20,SUM)，自动生成部门–业绩透视表；

2.双维度统计：按部门+月份统计平均薪资，=PIVOTBY(A2:A20,C2:C20,D2:D20,AVERAGE)。

超级表组合应用

1.操作方法：选中数据源按Ctrl+T创建超级表，命名为“业绩表”；输入公式=PIVOTBY(业绩表[部门],,业绩表[业绩],SUM)；

2.核心场景：新增/删除数据后，超级表自动扩展，PIVOTBY结果同步更新，无需调整公式，适配持续更新的台账汇总。

五、Excel填充序号全方法（4类序号+多方案）

（一）普通序号（1-2-3连续序号）

1.方法1：拖动填充：A2输入1，A3输入2，选中两格下拉拖动，点击右下角选择“填充序列”；

2.方法2：ROW函数：=ROW()-1，下拉填充，表头行占1行则减1，适配固定行数表格。

（二）动态序号（增删行自动更新）

1.方法1：动态数组：=SEQUENCE(COUNTA(B2:B100))，B列为非空数据列，自动适配数据行数；

2.方法2：超级表+ROW：创建超级表，序号列输入=ROW()-ROW(表1[#Headers])，增删行序号自动刷新。

（三）合并单元格序号（合并后连续序号）

1.方法1：批量填充：选中合并单元格区域，输入=MAX(A$1:A1)+1，按Ctrl+Enter批量填充；

2.方法2：COUNT嵌套：=COUNT(A$1:A1)+1，适配各类合并单元格场景。

（四）不规则序号（仅非空行显示序号）

1.方法1：IF+ROW：=IF(B2<>””,COUNTA(B$2:B2),””)，B列有内容则显示序号，空行留白；

2.方法2：动态数组：=TOCOL(IF(B2:B100<>””,SEQUENCE(COUNTA(B2:B100)),NA()),2)，自动忽略空行生成序号。