Excel实现动态分页显示数据的技巧

前言

在日常处理 Excel 数据时，打印数据使我们必不可少的工作环节，但是有许多小伙伴都曾面临过这样的困境：在设定了打印区域后，随着数据的更新，新增的内容在打印时却未能被包含在内，这往往需要用户手动重新调整打印区域，不仅繁琐还容易出错，下面我们通过 Excel 实现动态分页显示数据的解决办法来解决这一问题。

示例

例如 A1:B10 是数据源区域，E2 单元格是页码（需要手动切换不同的页码），我们想要在动态打印区域即 G:H 列，设置每页显示 3 行数据源的数据，通过 E2 单元格的按钮切换页码。首行标题行 G2:H2 不计入行数，且是固定不变的。

目的：打印时不用反复设置打印区域进行打印了，始终是 G3:H5 区域不同页码的 3 行数据。

这个案例我们可以通过 OFFSET 函数得到解决方案。

OFFSET 函数回忆

OFFSET 函数是一个动态引用函数，用于根据指定的起始位置、偏移行数和列数，返回一个单元格或区域（由行高列宽确定区域）的引用。

它常用于动态调整数据范围或构建动态公式。

OFFSET 函数语法：

=OFFSET(基准单元格, 行偏移数, 列偏移数, [高度], [宽度])

基准单元格：基准位置（必填）。

• 行偏移数：向上（负数）或向下（正数）偏移的行数。
• 列偏移数：向左（负数）或向右（正数）偏移的列数。
• [高度]（可选）：返回区域的行数（默认为 1）。
• [宽度]（可选）：返回区域的列数（默认为 1）。

首先我们确定 OFFSET 函数的第一参数，基准单元格。

这个很简单，我们以固定单元格位置 A2 单元格作为起始基准单元格，这里因为涉及不到公式的下拉填充，用到的是数组溢出结果，所以可以不必对 A2 单元格做绝对引用的处理。

接下来我们确定 OFFSET 函数的第二参数，行偏移数。

我们可以通过输入公式：

=(E1-1)*3

来确定行偏移数。

E1 单元格页码为 1 时，G3 单元格返回 0，表示行偏移 0 行

以此类推：

• E1 单元格页码为 2 时，G3 单元格返回 3，表示行偏移 3 行
• E1 单元格页码为 3 时，G3 单元格返回 6，表示行偏移 6 行
• …….

以此类推，不做解释。

这样我们将上述公式带入 OFFSET 函数的第二参数，第三参数我们列偏移数规定为 0 即可（不作偏移）：

=OFFSET(A2,(E1-1)*3,0)

这样以 A2 单元格为基准，行偏移数分别向下偏移 0 行、3 行、6 行，列偏移数均为 0，偏移后的单元格依次为 A2 单元格日期“3 月 1 日”、A5 单元格日期“3 月 5 日”、A8 单元格日期“3 月 10 日”。

• E1 单元格页码为 1 时，首行日期为 A2 单元格日期“3 月 1 日”
• E1 单元格页码为 2 时，首行日期为 A5 单元格日期“3 月 5 日”
• E1 单元格页码为 3 时，首行日期为 A8 单元格日期“3 月 10 日”

最后确定 OFFSET 函数的第四和第五参数，即偏移后获取数据区域的高度与宽度。

OFFSET 函数的第四和第五参数：

=OFFSET(A2,(E1-1)*3,0,3,2)

因为目前偏移后的是某个单元格位置（默认高度与宽度均为 1），所以我们需要在此位置上（A2、A5、A8）分别向下取高度为 3，向右取宽度为 2 的区域。

那么得到的这三个区域就是每页的 3 行数据。

偏移的宽度也可以使用 COLUMNS 进行统计：

=OFFSET(A2,(E1-1)*3,0,3,COLUMNS(A:B))

COLUMNS(A:B)可以获取 A:B 区域的列数 2，这样做的好处是在 A:B 区域插入其他列的时候，OFFSET 的第五参数宽度是动态变化的。

最终的效果展示：

以上关于Excel实现动态分页显示数据的技巧的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。