返回目录:excel表格制作
Excel问答,是对老徐漫谈粉丝们提出的优秀问题进行的答疑解惑。希望能帮助到每一个看到此文的读者。
更多Excel问答文章请关注老徐漫谈头条号。
粉丝问题
最近有粉丝问到在 Excel 中使用下拉式清单的问题。如下图,在一个进货商的数据清单A1~A7中,每一个包含的数据清单数量并不相同。
【方法一:清单内容不变动】
如果列表项目的内容不会变动,则可以使用名称定义+INDIRECT函数来处理。
1. 选取单元格A2:H8。
2. 在[特殊目标]对话框中选取「常数」,结果只会选取有数据的单元格。
3. 在单元格选取状态下,按 Ctrl+Shift+F3 键,勾选「最左栏」项目。
如此可以进货商的项目名称定义为名称。
名称定义结果如下:(因为名称A1和单元格的地址相同,所以会自动加上「_」。)
4. 选取单元格B12,进入[数据验证]对话框。在[数据验证准则]区中设定:
单元格内允许:清单;来源:=INDIRECT(A12&"_")。
【方法二:清单内容会变动】
如果列表项目的内容会变动,则可以在数据验证中使用 OFFSET 处理。
选取单元格A1:A8,按 Ctrl+Shift+F3 键,勾选「顶端列」,定义名称:进货商。
接着,选取单元格B12,进入[数据验证]对话框。在[数据验证准则]区中设定:
单元格内允许:清单;来源:=OFFSET($A$1,MATCH(A12,进货商,0),1,1,COUNTA(OFFSET($A$1,MATCH(A12,进货商,0),1,1,7)))
其中:
(1) MATCH(A12,进货商,0)
找出单元格A12内容在进货商中的位置。(传回一个数字)
(2) OFFSET($A$1,MATCH(A12,进货商,0),1,1,7)
根据第(1)式的传回值代入 OFFSET 函数,找出单元格A12所对应的数据区间(本例为:单元格B6:H6)
(3) COUNTA(OFFSET($A$1,MATCH(A12,进货商,0),1,1,7))
计算在单元格A12所对应的数据区间有几个有文字的单元格。(本例传回5)
(4) OFFSET($A$1,第(1)式,1,1,第(3)式)
找出单元格A12所对应的数据区间(本例为:单元格B6:H6)中有内容的单元格(本例为:单元格B6:F6)。