此问题为纵横循环序列数的延续,在第一行输入任意数字,如存在1-40之间的数,则公式返回的结果从中排除。
函数公式解决:
=SMALL(IF(ISNA(MATCH(SEQUENCE(40),$1:$1,)),SEQUENCE(40),""),MOD(COLUMN(A1)+ROW(A1)-2,41-COUNT(UNIQUE(MATCH($1:$1,SEQUENCE(40)),1)))+1)
考虑到公式下拉需要超过40行,此处用Sequence(40)取代传统的Row(1:40)
利用Match函数,查找1至40在第一行中是否存在,如存在仍返回原数,否则返回空。
利用Small函数将结果从小到大排列,剔除因为匹配而产生的空值。Small函数的第二参数,由Mod函数生成1至指定个数的循环。
Mod函数的第一参数从1起始,右拉下拉都会以1为步长递增。
Mod函数的第二参数需要根据第一行数据的变化而变化,先用Unique去除第一行数据中的重复值,再将这一结果与Sequence(40)进行匹配,剔除1至40以外的数据,再用Count函数对其计算,最后用41减去这一结果,计算出循环的周期。
标签:第一行,函数,SEQUENCE,40,循环,纵横,序列,Mod From: https://www.cnblogs.com/officeplayer/p/17557442.html