【EXCEL】区域数据转为单列数据

南子98

已于 2024-05-01 18:57:56 修改

阅读量197

点赞数 1

分类专栏：软件应用文章标签： excel

于 2024-04-30 12:43:32 首次发布

本文链接：https://blog.csdn.net/jimmy33777/article/details/138341455

版权

软件应用专栏收录该内容

20 篇文章 2 订阅

订阅专栏

本文介绍了如何利用INDEX函数结合ROW(),COUNTA(),ROUNDUP()和MOD()函数，根据行号和列数规则，将区域数据逐行转换为单列数据的详细步骤。通过行号和列号的构造，确保了数据在单列下拉列表中的正确布局。

摘要由CSDN通过智能技术生成

【需求描述】区域数据按逐行扫描转换为单列数据

【实现方式】INDEX函数 + 行号&列号索引

【切入重点】行号、列号构造

① 行号：单列下拉时，从1开始，重复次数=列数 → 1,1,1,2,2,2,3,3,3（以此类推）

ROUNDUP((ROW(A1)/COUNTA($B$2:$E$2)),0)

/*

1. row（A1）：构造下拉时1,2,3,4,5,6......

2. COUNTA($B$2:$E$2)：列数

ROW(A1)/COUNTA($B$2:$E$2) = 1/3,2/3,3/3,4/3... = 0.333,0.666,1,1.333，.....

3. 需ROUNDUP函数将小数化为整数，方能构造行号（向上取整）


*/

② 列号：单列下拉时，重复1，2，3，... ，列数，1，2，3，... ，列数（以此类推）

IF(MOD(ROW(A1),COUNTA($B$2:$E$2))=0,COUNTA($B$2:$E$2),MOD(ROW(A1),COUNTA($B$2:$E$2)))

/*

1. row（A1）：构造下拉时1,2,3,4,5,6......

2. COUNTA($B$2:$E$2)：列数

3. MOD(ROW(A1),COUNTA($B$2:$E$2)) 

① 当 ROW(An) 不能被列数整除时（IF条件FLASE），说明提取数据在同一行，返回相应的余数作列号

② 当 ROW(An) 被列数整除时（IF条件TRUE），说明到达当前行的最后一列，返回相应的列数作列号

PS：IF函数的目的避免MOD(ROW(A1),COUNTA($B$2:$E$2))=0时，返回列号为0，而导致出错

*/

【完整实现代码】

INDEX(区域，行号，列号)

/*

1. 区域：B2:E10

2. 行号：ROUNDUP((ROW(A1)/COUNTA($B$2:$E$2)),0)

3. 列号：IF(MOD(ROW(A1),COUNTA($B$2:$E$2))=0,COUNTA($B$2:$E$2),MOD(ROW(A1),COUNTA($B$2:$E$2)))

*/