EXCEL如何让”通过公式获取数据“的数据源作为下拉菜单的来源，并且是可以自动更新的？

发布网友 发布时间：2022-05-12 06:10

我来回答

共5个回答

热心网友 时间：2023-11-24 21:08

这个问题，颇有点难点，但很意义，考虑后还是共享一下，希望对你有用。
核心用 INDIRECT(ref_text, [a1])函数 来实现，一级菜单来自表第一行 1:1, 二级菜单来自一级菜单的对应明细内容，注考虑到你的数据是来自公式offset()取得的，参数前加上完全路径，如A1:D1---sheet1!A1:D1,
操作：1、整理数据源表，如图，F列不要有合并单元格；2、一级菜单定义：选择区域F2:F9---数据---数据有效性---允许：序列---数据来源：sheet1!A1:D1 (为了以后扩展，范围参数改大一点，如A1:AX1 )
2、二级菜单定义：一行一行定义，定位到G2，数据---数据有效性---允许：序列---数据来源：=INDIRECT($F$2)，G3---=INDIRECT($F$3)..... 不能下拉;
3、英文名称根据F列的值，用Vlookup( )函数匹配，H2=VLOOKUP(F2,Sheet2!F2:G20,2,0)

热心网友 时间：2023-11-24 21:08

选中F2:F1000，直接进行 数据验证设置，序列里的公式(禁止G列有数据时更改F列数据)：

=IF(ISBLANK(G2),OFFSET($A$1,,,1,LOOKUP(1,0/($A$1:$E$1<>""),COLUMN($A:$E))))

选中G2:G1000，直接进行 数据验证设置，序列里的公式：

=OFFSET($A$1,1,MATCH(F2,$A$1:$C$1,)-1,(LOOKUP(1,0/($A$1:$A$1000<>""),ROW($1:$1000))-1)/2,1)

 3.H2粘贴公式：

=OFFSET($A$1,(LOOKUP(1,0/($A$1:$A$1000<>""),ROW($1:$1000))-1)/2+MATCH(G2,OFFSET(A:A,,MATCH(F2,$A$1:$E$1,)-1),)-1,MATCH(F2,$A$1:$E$1,)-1)

下拉

4.如果F列要联想输入，需辅助列，J1输入公式：

=IFERROR(INDEX($A$1:$E$1,SMALL(IF(ISNUMBER(SEARCH(INDIRECT(CELL("address")),$A$1:$E$1)),COLUMN($A:$E)),ROW(A1))),"")

ctrl+shift+回车，下拉16384行(忽略循环引用提醒)

选中F2:F1000，直接进行 数据验证设置，序列里的公式：

=IF(ISBLANK(G2),OFFSET($J$1,,,LOOKUP(1,0/($J$1:$J$16384<>""),ROW($1:$16384))))

注：a.如果不想在F列单元格输入联想字符而直接显示全部列表，需双击如何空白单元格（包括要输入的单元格），或按delete、F2、F9或退格键

    b.设置时G列需置空，且取消出错禁止选项

追答已改

热心网友 时间：2023-11-24 21:09

热心网友 时间：2023-11-24 21:09

热心网友 时间：2023-11-24 21:10