搞定excel数据自动分栏

演示数据及功能

首先我们看一下我们的演示数据及功能，我们数据工作表中有3列的数据，分别是序号，姓名，学号，标题占1行，我们计划每页放40行数据，每页分三栏。横向三栏的模板，数据第1行1、2、3，第2行对应4、5、6这样数据横向展示的横向分栏模板。纵向三栏的模板，数据第一栏依次对应1-40，第二栏对应41-80，第三栏对应81-120，第二页第一栏对应121-160，第二页第二栏对应261-200，第二页第三栏对应200-240。

算法分析与所需函数

要实现自动分栏，其核心算法就是计算出模板工作表中单元格与数据工作表中单元格的对应关系，要计算出对应的列和行，然后引用相应单元格即可。为了方便计算我们在数据表中定义几个名称：F1单元格名称为biaoti，记录标题所占行数；F2单元格名称为lanshu，记录每页分栏的数量；F3单元格名称为hangshu，记录每页显示数据的行数，不包括标题行。

我们需要先了解一下今天要用到ROW、COLUMN、CHAR、ROUNDDOWN、MOD、IF、INDIRECT这几个函数的用法，如果你对这些函数比较熟悉，可以跳过直接到实例操作部分：

ROW函数

返回引用的行号，其语法格式如下：

ROW([reference])

ROW 函数语法具有下列参数：

Reference 可选。 需要得到其行号的单元格或单元格区域。

如果省略 reference，则假定是对函数 ROW 所在单元格的引用。

在这里我们只需要用row()获取当单前单元格的行号。

COLUMN

返回引用的列号，其语法格式如下 ：

COLUMN([reference])

COLUMN函数语法具有以下参数：

Reference 可选。要返回其列号的单元格或单元格范围。

如果省略 reference，则假定是对函数 COLUMN 所在单元格的引用。

在这里我们只需要用column()获取当单前单元格的列号。

CHAR函数

根据数字代码返回对应的字符其语法格式如下：

CHAR(number)

number : 数字范围 1 - 255 ，使用的是当前计算机字符集中的字符代码。

在这里column()如果在A列则返回值是1， 65 对应 "A" 字符，所以我们用char(column()+64)，将其转换为列号A。

ROUNDDOWN函数

ROUNDDOWN向下舍入数字，其语法格式如下：

ROUNDUP(number,num_digits)

Number 为需要向上舍入的任意实数。

Num_digits 舍入后的数字的位数。

在今天的实例中，纵向分栏时，我们需要计算模板单元格对应数据单元格的页数，第一栏中应该是（页数-1）*hangshu*lanshu+biaoti，而计算页数就要用到ROUNDUP函数，页数的计算公式就是ROUNDUP((ROW()-biaoti)/hangshu,0)。大家可以思考一下这里为什么不用ROUNDDOWN而用ROUNDUP-1。

MOD函数

返回两数相除的余数，其语法格式如下：

MOD(number, divisor)

MOD 函数语法具有下列参数：

Number 必需。 要计算余数的被除数。

Divisor 必需。 除数。

在今天的实例中MOD((ROW()-biaoti),hangshu)，返回当前行在模板当前页中中的行数。

IF函数

判断是否满足某个条件，如果满足返回一个值，如果不满足则返回如别一个值，其语法格式如下：

IF（Logical_test,Value_if_true,Value_if_false）

logical_test 必需。 计算结果为 TRUE 或 FALSE 的任何值或表达式。

value_if_true 可选。 logical_test 参数的计算结果为 TRUE 时所要返回的值。

value_if_false 可选。 logical_test 参数的计算结果为 FALSE 时所要返回的值。

在今天的实例中，每页的最后行MOD((ROW()-biaoti),hangshu)计算结果为0，所以我们要通过if函数判断MOD((ROW()-biaoti),hangshu)是否为0，来判断是不是最后一行，如果是最后行，则返回hangshu，否则返回MOD((ROW()-biaoti),hangshu)，其完整的公式如下：= IF(MOD((ROW()-biaoti),hangshu)=0,hangshu,MOD((ROW()-biaoti),hangshu))

INDIRECT函数

返回字符串所指定的引用，其语法格式如下：

INDIRECT(Ref_text,A1)

Ref_text单元格引用，该引用所指向单元格中存放有对另一单元格的引用，引用的形式为A1、R1C1或是名称。

A1逻辑值，用以指明Ref_text单元格中包含的引用方式。R1C1格式=FALSE；A1格式=TRUE或忽略。

在今天的实例中的我们引用的是A1这种格式比如=INDIRECT("A1")。

数据准备

我们数据工作表中有3列的数据，分别是序号，姓名，学号，标题占1行，为了方便我们编辑公式增加公式的可读性，我们在E1中输入“标题行数”，F1单元格名称定义为lanshu，记录每页分栏的数量，本例标题行为1；E2中输入“每页栏数”，F2单元格名称定义为lanshu，记录每页分栏的数量，本例以3栏为列；E3单元格中输入“每页行数”， F3单元格名称为hangshu，记录每页显示数据的行数，不包括标题行，本例每页定义为30行。

横向三栏模板的制作

相对于纵向分栏，横向分栏较为简单，而分三栏又较有代表性，我们可以很容易更改为二栏或者四栏，或者更多，所以我们先来学习一下横向三栏模板的制作。

列的计算

第一栏第一行的列也就是模板的A列对应的也是数据表中的A列，偏移量为0，按照前面我们讲过的要转换成字母COLUMN()-0+64，其中0是偏移量为了后面第二栏第三栏方便修改公式，也增加公式的可读性，这样A列就是65，我们再用CHAR转换一下就得到列标A，其公式如下：CHAR(COLUMN()-0+64)

行的计算

模板中第1栏中第1行对应的是数据表中的第2行，第2栏中第1行对应数据表中的是第3行，第3栏中第1行对应的是数据表中的第4行，第1栏中第2行对应的是数据表中第5行，第2栏第2行对应的是数据表中的第5行，第3栏第2行对应的是数据表中的第6行，其算法为（当前行数-标题行-1）*栏数+标题行+1，公式为=(ROW()-biaoti-1)*lanshu+biaoti+1

单元格地址的转换

我们用INDIRECT函数将计算的单元格转换一下，其公式为：“=INDIRECT("数据!"&CHAR(COLUMN()-0+64)&(ROW()-biaoti-1)*lanshu+biaoti+1)”。这是第一栏第一行第一个单元格的公式，我们拖动填充第1行，再填充第一栏。

第二栏算法及实现

第二栏列的计算时我们需要调整一下偏移量-4，其列的计算公式为CHAR(COLUMN()-4+64)。

第二栏行的计算时我们需要调整一下行的偏移量第1栏加1，其行的计算公式为=(ROW()-biaoti-1)*lanshu+biaoti+2。

第二栏第一行第一个单元格的公式为=INDIRECT("数据!"&CHAR(COLUMN()-4+64)&(ROW()-biaoti-1)*lanshu+biaoti+2)，我们拖动填充第1行，再填充第二栏。

第三栏算法及实现

第三栏列的计算时我们需要调整一下偏移量-8，其列的计算公式为CHAR(COLUMN()-4+64)。

第三栏行的计算时我们需要调整一下行的偏移量第1栏加2，其行的计算公式为=(ROW()-biaoti-1)*lanshu+biaoti+3。

第三栏第一行第一个单元格的公式为=INDIRECT("数据!"&CHAR(COLUMN()-8+64)&(ROW()-biaoti-1)*lanshu+biaoti+3)，我们拖动填充第1行，再填充第三栏。

这样我们就完成横向三栏模板的制作。

纵向三栏模板的制作

纵向三栏模板的制作相对于横向三栏模板的制作要复杂一些，它需要考虑到分页的问题，我们确定分栏数和每页有效数据的行数，然后计算出每页包含多少行有效数据，然后再计算出模板中单元格对应的数据表中的单元格。

列的计算

列的计算与方法与横向模板中列的计算方法相同，其公式如下：CHAR(COLUMN()-0+64)，这里就不再多说了了。

行的计算

首先我们要计算出当前单元格在当前页的行数，我们在讲MOD和IF函数时已经以此为例给大家讲过了。MOD((ROW()-biaoti),hangshu)，返回当前行在模板当前页中中的行数。如果是每页的最后一行余数为0，我们还需要通过if函数将0换成hangshu。

我们还需要计算出当前页前面的数据总行数，我们在讲ROUNDUP函数时也已经讲过了页数的计算，ROUNDUP((ROW()-biaoti)/hangshu,0) 。也讲过了此页以前数据的总行数的公式(ROUNDUP((ROW()-biaoti)/hangshu,0)-1)*lanshu*hangshu。

我们用此页以前数据的总行数+本页本栏前数据的行数+本单元格在本页中的行数+标题行数，就是当前单元格对应数据表中单元格的行数，其完整公式如下：((ROUNDUP((ROW()-biaoti)/hangshu,0)-1)*lanshu*hangshu+hangshu*0+(IF(MOD((ROW()-biaoti),hangshu)=0,hangshu,MOD((ROW()-biaoti),hangshu)))+biaoti)

单元格地址的转换

我们用INDIRECT函数将计算的单元格转换一下，其公式为：“=INDIRECT("数据!"&CHAR(COLUMN()-0+64)&((ROUNDUP((ROW()-biaoti)/hangshu,0)-1)*lanshu*hangshu+hangshu*0+(IF(MOD((ROW()-biaoti),hangshu)=0,hangshu,MOD((ROW()-biaoti),hangshu)))+biaoti))”。这是第一栏第一行第一个单元格的公式，我们拖动填充第1行，再填充第一栏。

第二栏算法及实现

第二栏列的计算时我们需要调整一下偏移量-4，其列的计算公式为CHAR(COLUMN()-4+64)。

第二栏行的计算时我们需要调整一下行的偏移量，将第1栏第1行中的hangshu*0改为hangshu*1，其行的计算公式为((ROUNDUP((ROW()-biaoti)/hangshu,0)-1)*lanshu*hangshu+hangshu*1+(IF(MOD((ROW()-biaoti),hangshu)=0,hangshu,MOD((ROW()-biaoti),hangshu)))+biaoti)。

第二栏第一行第一个单元格的公式为=INDIRECT("数据!"&CHAR(COLUMN()-4+64)&((ROUNDUP((ROW()-biaoti)/hangshu,0)-1)*lanshu*hangshu+hangshu*1+(IF(MOD((ROW()-biaoti),hangshu)=0,hangshu,MOD((ROW()-biaoti),hangshu)))+biaoti))，我们拖动填充第1行，再填充第二栏。

第三栏算法及实现

第三栏列的计算时我们需要调整一下偏移量-8，其列的计算公式为CHAR(COLUMN()-4+64)。

第三栏行的计算时我们需要调整一下行的偏移量，将第1栏第1行中的hangshu*0改为hangshu*2，其行的计算公式为((ROUNDUP((ROW()-biaoti)/hangshu,0)-1)*lanshu*hangshu+hangshu*2+(IF(MOD((ROW()-biaoti),hangshu)=0,hangshu,MOD((ROW()-biaoti),hangshu)))+biaoti)

第三栏第一行第一个单元格的公式为=INDIRECT("数据!"&CHAR(COLUMN()-8+64)&((ROUNDUP((ROW()-biaoti)/hangshu,0)-1)*lanshu*hangshu+hangshu*2+(IF(MOD((ROW()-biaoti),hangshu)=0,hangshu,MOD((ROW()-biaoti),hangshu)))+biaoti))，我们拖动填充第1行，再填充第三栏。

这样我们就完成横向三栏模板的制作。

欢迎到下方观看视频中去观看详细的视频讲解，并长按点赞支持我！