EXCEL 如何制作瀑布图,对于有些数据做图表非常适合用瀑布图,比如生活开支......
Excel技巧之——“数据有效性”的另类用法
Excel中有一项功能,名为“数据有效性”,在输入数据的过程中作用很大,可以防止输入数据错误等,对保证原始数据的正确性效果很好,来看示例:
工具/原料
- office/
方法/步骤
跨表使用数据有效性:
先制作一份无误的客户名称表,为以后输入数据提供准确无误的的原始数据,选中客户名称列,单击菜单栏“插入→名称→定义”(ctrl F3)打开“定义名称”对话框,为当前选中区域定义一个名称为”客户名称“(也可以按需要自定义),在”应用位置框中“输入或选择相应的单元格区域,确定即可定义一个名称呢。
在实际工作中,例如需要录入xx月份的销售明细表,在表中即可利用数据有效性进行,保证原始数据的正确性。选中表中的客户列,单击菜单栏”数据→有效性“,打开”数据有效性“对话框,在”设置“选项卡下,设置”允许“为序列,来源中输入步骤一定义的名称:客户名称,即可为客户列设置了数据有效性,在以后输入客户的信息时即可在下拉列表中选中,也可以手动输入,如果手动输入有误的话,会有错误提示,即可保证的原始数据的准确无误。
PS:因为数据有效性不允许跨表使用,因此通过定义名称的方法来实现跨表使用
扩展:如果以后在原始表格中增加了客户,例如增加了5个客户,只要再次打开“定义名称”对话框,选中已定义的名称,在应用位置框中将新增加的单元格区域添加即可
必杀技:在步骤一定义名称时,还可以将其定义为动态的,这样只要在”原始“表中新增客户,excel会自动扩展,无需上步骤那样手动修改。
在”引用位置“中输入公式:=offset(原始!$A$2,0,0,counta(原始!$A:$A)-1),确定即可。以后在”原始“表中新增客户时,数据有效性的下拉列表会自动扩展。
PS:offset函数为引用函数,本公式其解释就是以a2单元格为参照点,向下向右分别偏移0行0列(参数2、3指定),返回参数4和5指定的单元格区域,本例中,参数4为统计文本的个数,就是统计出a列中文本的个数,并减去1(此为本公式的关键,由于a列中的文本个数可变,因此,参数4的值为可变,导致了offset函数返回的单元格区域为一个动态区域。),本例中,参数5省略,表示返回区域的列数为1
输入后纠错,如果销售明细表已经做好了(客户名称已经输入了),数据有效性和公式审核相配合,可起到发现错误的功能。标示出无效数据输入,便于改正。
如图:选中客户列的非空单元格,数据→有效性,打开对话框,设置”允许“为序列,”来源“为=客户名称,建立好数据有效性。单击”工具→公式审核→打开公式审核对话框“,选择”圈释无效数据“,即可以将以前输入的无效错误数据找出来。
以上方法由办公区教程网编辑摘抄自百度经验可供大家参考!