基础数据示例如图,若A1输入“A”、B1输入“一”。让C1自动出现下拉列表1/2/3。并能重1、2、3中选择一个填入C1。可以用怎样的公式?
解题思路(一个操作相对简单易于理解的方法):
根据要求,可以构造2个辅助列:
1)辅助列A,A1=B1&C1,将B、C列内容连起来方便后面计数;
2)辅助列H(也可以是别的列),H1:H20输入1到20作为序列号备用。
然后:
选中D1,点击“数据”选项卡——“数据验证”(低版本EXCEL叫“数据有效性”)——“设置”中的“允许”选择“序列”,来源粘贴公式 =INDIRECT("H1:H"&COUNTIF($A$1:$A1,A1)),确定退出。下拉复制D1单元格。
此时就可以下拉选择需要的序号了,辅助列可以隐藏起来。
点开图片看动图演示
如果要用公式的话,方法如下:
1、在D列左边插入一空白列,在C1单元格输入公式=IFERROR(INDEX(G$1:G$16,SMALL(IF((E$1:E$16=A$1)*(F$1:F$16=B$1),ROW($1:$16),9^9),ROW(A1))),"")这是个数组公式,按 CTRL + SHIFT + ENTER 结束公式,使公式前后产生大括号{},再下拉公式到第16行。
2、单击D1单元格,点数据,有效性,允许,序列,将来源设置成=$C$1:$C$16区域,确定。
3、在C列的列标字母C上点鼠标右键,隐藏,即可将C列作隐藏,这样在A1、B1输入不同内容时,D1单元格就会有不同的下拉菜单了。