函数公式、职场模板 、财务应用、分析图表、练习题、快捷键、软件工具、表格合并、图表及可视化、Office 365、Power Query、表格美化、符号作用、条件格式、学会骗、一本不正经、避坑指南、数据整理、筛选技巧、偷懒宝典、漂亮3D、WPS技巧、PPT技巧、Word技巧
👆上面是分类专题👆
👇下面是最新文章👇
· 正 · 文 · 来 · 啦 ·
读者小田田在Excel偷懒的技术读者群提问:
我要生成1,2,3,5,6,7,9,10,11序列,怎么设公式啊。
读者清风乍起追问:
像这种序列 有没有套路公式?
今天龙逸凡就给大家总结一下,用公式生成序列的方法与技巧。
一、基础知识
要生成序列,离不开两个最基本的函数:
ROW、COLUMN
它们返回指定单元格的行号、列号,也就是告诉你单元格在第几行/第几列
比如:
ROW(B3)结果为3
COLUMN(B3)结果为2
我们通常以这两个函数的计算结果为基础,进行数学运算,生成各种序列。
序列公式通常用到下面这些函数:
1、INT函数
作用:取整,
比如:INT(1/3)结果为0,INT(7/2)结果为3。
INT函数在序列中,通常用于将某个数字重复N遍,比如下面的公式就是将序列数字重复3遍:
=INT(COLUMN(C1)/3)
请注意与后文应用案例5和7的差异。
2、MOD函数
作用:取余数,
比如MOD(3,2)结果为1,MOD(7,5)结果为2
通常用于生成1-N的重复序列:
3、^
作用:计算次方
与-1搭配食用,味道更佳。
4、ISODD、ISEVEN函数
作用:判断是否为奇数/偶数,
通常将它当“开关”使用。
=--ISODD(COLUMN(A1))
5、ABS函数
作用:计算绝对值,
可用于生成“荡秋千”序列
6、ROWS、COLUMNS函数
作用:计算单元格区域中的有几行、有几列
上面ABS函数的公式用到了一个技巧,用一个数字去减行号列号。
我们可以利用此技巧,结合ROWS、COLUMNS函数,再结合相对引用、绝对引用,就可生成“过山车序列”。
7、CEILING、FLOOR函数
作用:向上/下按倍取数
这个公式可以用INT*3来代替
二、应用案例
前面介绍了基础知识,接下来,龙逸凡跟大家一起,将上面的函数进行各种组合,生成不同的序列。
序列1、走一步歇一下
=ROW(A1)*ISEVEN(ROW(A1))/2
本序列结合INDEX函数,可用于生成工资条
序列2、走一步歇两步
=INT(ROW(A5)*(MOD(ROW(A5),3)=1)/3)+(MOD(ROW(A5),3)=0)
本序列与INDEX配合,可用于双行工资条
走一步歇两步②
=INT(ROW(A4)*(MOD(ROW(A4),3)=1)/3)
序列3、两个序列交替递增
=INT(COLUMN(B1)/2)-ISEVEN(COLUMN(A1))
序列4、N的倍数时以上一个替代,并跳过
=COLUMN(A1)-MOD(MOD(COLUMN(E1),3),2)
序列5、N的倍数时重复一次
=INT(COLUMN(B1)*4/5)
序列6、每四行插入一空行
在前面公式的基础上,用MOD加一个开关,就可生成每N行插入一标题行的序列。
=INT(ROW(A2)*4/5)*(MOD(ROW(),5)<>0)
序列7、N的倍数时,跳过
=INT(COLUMN(D1)*5/4)-4
序列8、单数倍以内取单,双数倍内取双
=INT(COLUMN(D1)*9/4)-8
小结:
根据序列5、序列7、序列8的公式可以总结出一个通用的序列模型公式
=INT(ROW(An)*x/y)-k
对上面公式中的n、x、y、k进行变更,就可生成不同特性的序列,大家可动手测试一下。
序列9、“宇宙大爆炸”序列
=ROUND((2*COLUMN())^0.5,0)
序列10、斐波那契数列
=POWER(5,-0.5)*(((1+5^0.5)/2)^COLUMN(A1)-((1-5^0.5)/2)^COLUMN(A1))
题外话:
斐波那契序列有一些比较神奇的特性,比如
平方项:从第二项开始,每个偶数项的平方都比前后两项之积少1,每个奇数项的平方都比前后两项之积多1。
黄金分割特性:随着数列项数的增加,前一项与后一项之比越来越接近黄金分割的数值0.6180339887……
每4个连续的数中有且只有一个被3整除;每5个连续的数中有且只有一个被5整除;每6个连续的数中有且只有一个被8整除;每7个连续的数中有且只有一个被13整除……
联系客服