网友问到一个常见的题目:参考下图,想要藉由下拉式清单来挑选分歧种别的资料,该若何处置?
处置原则是下拉式清单可使用「资料验证」功能,而挑选资料凡是会用到阵列公式。
【準备工作】
拔取「级数」栏位中有资料的贮存格範围,按一下 Ctrl+Shift+F3 键,界说名称:级数。
【输入公式】
贮存格G2:{=IFERROR(OFFSET($B$1,SMALL(IF(级数=$F$1,ROW(级数),FALSE),ROW(1:1))-1,COLUMN(A:A)-1,,),"")}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键。
複製贮存格G2,贴至贮存格G2:H2。複製贮存格G2:H2,往下各列贴上。
参考下图,对公式加以阐发:
IF(级数=$F$1,ROW(级数),FALSE):在阵列公式中,判定阵列值是不是和贮存格F1的内容不异,若是是则传回测试列对应的 ROW 函数值(参考栏B和栏C的内容),不然传回 FALSE。
SMALL(IF(级数=$F$1,ROW(级数),FALSE),ROW(1:1)):利用 SMALL 函数找寻上式所对应的列号之第1,2,3,…个(参考栏D的内容)。例如:第一个的列号为1,第二个的列号为7,…。
将上式获得的第1,2,3,…个列号,代入 OFFSET 函数中,获得对应的贮存格内容。
OFFSET($B$1,SMALL(IF(级数=$F$1,ROW(级数),FALSE),ROW(1:1))-1,COLUMN(A:A)-1,,)
此中 COLUMN(A:A)=1,若是向複製时会酿成 COLUMN(B:B)=2、COLUMN(C:C)=3、…。
可以节制在 OFFSET 函数中传那一「栏」的位置。
这个公式不管几栏的资料,只要向右複製,便可传回对应的贮存格内容。
最后,再透过 IFERROR 函数,将没有传回贮存格内容的值(毛病讯息#NUM!)改以空缺显示。
至于贮存格的下拉式选单,则在「资料验证」中设定贮存格内许可:清单;来历:1、2、3、4、5、六。(参考下图)
创作者先容 vincent