EXCEL 如何制作瀑布图,对于有些数据做图表非常适合用瀑布图,比如生活开支......
在Excel中如何制作1~N级无重复值的下拉菜单
Excel中使用下拉菜单,可使输入更快、更准确。比较常见的性别、姓名等1级下拉菜单,而2级或多级下拉菜单较少使用。本经验以下图数据为例教大家制作1-3级无重复值的下拉菜单,学会了3级下拉菜单,制作100级的下拉菜单均不成问题。
工具/原料
- excel 2007
一、1级下拉菜单
在F列中制作1级下拉菜单,选中F3单元格,数据-数据有效性-数据有效性V,在弹出的对话框中,设置允许为“序列”,来源可手动输入,也可用鼠标拉选,数据来源区域可适当增加一些,以方便后续数据增加时,下拉菜单内容自动增加。
确定后,点击F3处的下拉箭头,发现下拉菜单有重复值。在数据源区域有重复值时,需要使用辅助列来制作下拉菜单,以避免重复值的出现。
以I列为名称辅助列,在I3单元格中输入=INDEX($B$3:$B$200,MATCH(0,COUNTIF(I$2:I2,$B$3:$B$200),0))&"",同时按Ctrl Shift Enter完成输入,然后向下填充,此即形成不重复的B列中的数据。
将数据有效性设置中的来源改为=$I$3:$I$50,确定后即得无重复值的1级下拉菜单。
二、2级下拉菜单
首先,在J列计算各产品开始的行数,在J3单元格中输入=MATCH(I3,B:B,0)-1,下拉填充;在K列计算各产品所占的总行数,在K3单元格中输入=COUNTIF(B:B,I3),下拉填充。
由于C列型号有重复数据,增加型号辅助列。本例以L列为型号辅助列,在L3单元格中输入=INDEX($C$3:$C$200,MATCH(0,COUNTIF(L$2:L2,$C$3:$C$200),0))&"",同时按Ctrl Shift Enter完成输入,然后向下填充,此即形成不重复的C列中的数据。
在G3单元格中设置数据有效性:方法同1级下列菜单,在来源框中输入=OFFSET(L$1,VLOOKUP(F3,$I:$K,2,),,VLOOKUP(F3,$I:$K,3,),1),确定,得到2级无重复值的下拉菜单。
三、3级下拉菜单
同样,在M,N列分别计算型号的开始行数和总行数。以O列为批号辅助列,在O3单元格中输入=INDEX($D$3:$D$200,MATCH(0,COUNTIF(O$2:O2,$D$3:$D$200),0))&"",同时按Ctrl Shift Enter完成输入,然后向下填充,此即形成不重复的D列中的数据。
在H3单元格中设置数据有效性:方法同1级下列菜单,在来源框中输入=OFFSET(O$1,VLOOKUP(G3,$L:$N,2,),,VLOOKUP(G3,$L:$N,3,),1),确定,得到3级无重复值的下拉菜单。
设置好的下拉菜单可向下填充。
3级以上下拉菜单可按3级下拉菜单的制作方法进行制作。
注意事项
- 设置数据有效性时,来源区域可适当增加,以避免后续增加行而修改。
以上方法由办公区教程网编辑摘抄自百度经验可供大家参考!