当前时间： 2026-08-05 07:35:09
分类：办公文件
评论(0)
WPS表格|OFFSET函数完整实战教程一、基础语法
=OFFSET(基准单元格,行偏移量,列偏移量,[区域高度],[区域宽度])参数详解
- 基准单元格：偏移的起始基点，建议使用绝对引用$锁定
基础示例
含义：以A1为起点，向下偏移2行，向右偏移1列，定位B3单元格。二、高频办公实战案例
案例1：配合SUM实现动态区域求和
=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))作用：自动统计A列所有有内容单元格之和，新增数据无需手动修改求和范围。案例2：MATCH+OFFSET实现单行横向提取数据
=OFFSET($A1,MATCH(H1,A2:A$100,0),COLUMN(A1))案例3：制作联动二级下拉菜单（经典用法）
=OFFSET($B2,MATCH(D1,A2:A100,0)-1,0,COUNTIF(A2:A100,D$1),1)三、避坑重点
- OFFSET属于易挥发函数，表格数据量大时，过多使用会造成表格卡顿。
- 需要固定基点，记得加上$绝对引用，拖动公式不会偏移起点。
四、拓展知识
OFFSET只生成单元格引用，不直接读取数值；经常搭配SUM、MATCH、COUNTIF、COLUMN组合使用，是制作动态图表、联动下拉菜单的核心函数。
基本
文件
流程
错误
SQL
调试
- 请求信息 : 2026-08-07 17:00:54 HTTP/1.1 GET : https://www.yeyulingfeng.com/a/901332.html
- 运行时间 : 0.085410s [ 吞吐率：11.71req/s ] 内存消耗：4,655.10kb 文件加载：145
- 缓存信息 : 0 reads,0 writes
- 会话信息 : SESSION_ID=45027f139411636939d7f05ce9a62ae9
- CONNECT:[ UseTime:0.000369s ] mysql:host=127.0.0.1;port=3306;dbname=wenku;charset=utf8mb4
- SHOW FULL COLUMNS FROM `fenlei` [ RunTime:0.000618s ]
- SELECT * FROM `fenlei` WHERE `fid` = 0 [ RunTime:0.000261s ]
- SELECT * FROM `fenlei` WHERE `fid` = 63 [ RunTime:0.000266s ]
- SHOW FULL COLUMNS FROM `set` [ RunTime:0.000497s ]
- SELECT * FROM `set` [ RunTime:0.000224s ]
- SHOW FULL COLUMNS FROM `article` [ RunTime:0.000515s ]
- SELECT * FROM `article` WHERE `id` = 901332 LIMIT 1 [ RunTime:0.000321s ]
- UPDATE `article` SET `lasttime` = 1786093254 WHERE `id` = 901332 [ RunTime:0.000762s ]
- SELECT * FROM `fenlei` WHERE `id` = 64 LIMIT 1 [ RunTime:0.000262s ]
- SELECT * FROM `article` WHERE `id` < 901332 ORDER BY `id` DESC LIMIT 1 [ RunTime:0.000436s ]
- SELECT * FROM `article` WHERE `id` > 901332 ORDER BY `id` ASC LIMIT 1 [ RunTime:0.000436s ]
- SELECT * FROM `article` WHERE `id` < 901332 ORDER BY `id` DESC LIMIT 10 [ RunTime:0.005492s ]
- SELECT * FROM `article` WHERE `id` < 901332 ORDER BY `id` DESC LIMIT 10,10 [ RunTime:0.001123s ]
- SELECT * FROM `article` WHERE `id` < 901332 ORDER BY `id` DESC LIMIT 20,10 [ RunTime:0.002318s ]
0.086889s
夜雨聆风