excel_中数据的有效性的应用

1、.excel 中数据的有效性的应用        3、 防止数据输入错误        典型应用如下：        1 防止日期错误：        只准输入日期或某个日期之后的特定日期：点击EXCEL菜单“数据-有效性-， 在“设置-允许对话框中选择“日期、并在相应位置输入起始日期。  

2、0;             2 只准输入整数：        在“设置-允许对话框中选择“整数        3 防止输入重复值：        首先要选定整行/列或单元格区域比方选中B列，然后再点击EXCEL菜单“数据-有效性-， 在“设置对话框中选择“自定义、在“

3、公式中输入“=COUNTIFB:B,B1<2                 检验一下：在B3单元格中输入“A，看看会出现什么结果？        4 只能输入大于上一行的数值或日期：                注意引用区域的最后

4、一行行标为相对引用。A4的有效性公式为：=MAX$B$3:$B4        4、 条件输入        只准输入符合一定条件的数据：        1 只能输入大于左侧的数字                2 按条件输入_根据左侧条件

5、来决定右侧单元格如何输入：        以以下图中，假设单据类型选择了“入库单，那么只能在“入库数量所在列即C列输入数据，而不能在“出库数量所在列即D列输入数据；反之亦然。                C列公式为：=IFB5="入库单",ISNUMBERC5,FALSE        D列公式为

6、：=IFB5="出库单",ISNUMBERD5,FALSE应用一下拉菜单输入的实现例1：直接自定义序列有时候我们在各列各行中都输入同样的几个值，比方说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。我们希望Excel2000单元格可以象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。操作步骤：先选择要实现效果的行或列；再点击"数据有效性"，翻开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入

7、"优秀，良好，合格，不合格"注意要用英文输入状态下的逗号分隔！；选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。例2：利用表内数据作为序列源。有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。操作步骤：先在同一工作表内的打印区域外要定义序列填好假设在在Z1：Z8，如“单亲家庭，残疾家庭，残疾学生，特困，低收人，突发事件，孤儿，军烈属等，然后选择要实现效果

8、的列资助原因；再点击"数据有效性"，翻开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；“来源栏点击右侧的展开按钮有一个红箭头，用鼠标拖动滚动条，选中序列区域Z1：Z8假设记得，可以直接输入=$Z$1:$Z$8；选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。

9、例3：横跨两个工作表来制作下拉菜单用INDIRECT函数实现跨工作表在例2中，选择来源一步把输入=$Z$1:$Z$8换成=INDIRECT"表二!$Z$1:$Z$8",就可实现横跨两个工作表来制作下拉菜单。应用二自动实现输入法中英文转换有时，我们在不同行或不同列之间要分别输入中文和英文。我们希望Excel能自动实现输入法在中英文间转换。操作步骤：假设我们在A列输入学生的中文名，B列输入学生的英文名。先选定B列，点击进入"数据有效性"，翻开"数据有效性"对话框；选择"输入法"对话框，在"形式"下拉

10、菜单中选择"关闭英文形式"；然后再"确定"，看看怎么样。应用三数据唯一性检验员工的身份证号码应该是唯一的，为了防止重复输入，我们用“数据有效性来提示大家。操作步骤：选中需要建立输入身份证号码的单元格区域如B2至B14列，执行“数据有效性命令，翻开“数据有效性对话框，在“设置标签下，按“允许右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自定义选项，然后在下面“公式方框中输入公式：=COUNTIFB:B,B2=1，确定返回。以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并回绝承受输入的号码。运用Excel三大条件函数解决实际问题！秋尽冬至

11、，又到一年年终时。在工作中，数据的运算量和汇总的操作一下比平时多了不少。众所周知，在Excel中可以对数据进展各种不同的运算和汇总，今天我向大家介绍与条件相关的函数三兄弟，分别是“COUNTIF、“SUMIF和“IF函数。他们有一个共同的特点都姓字符“IF。大兄弟：COUNTIF函数计数求和COUNT函数，顾名思义是用来计数的，统计所选择区域的数值型单元格个数。COUNTIF是COUNT函数的引伸与拓展，在计数时加上先前条件，只有符合计数的条件才进展统计计算。比方，从员工信息表中，计算出有多少人的年龄大于35岁。下面我们来看一个典型的分类计数汇总的例子。这里有一张销售流水记录表，每名销售人员累

12、计做了多少“销售订单个数呢？ 大兄弟COUNTIF正常工作需要两个参数条件区域本例为左侧表中“销售人员一列和计数条件本例为右侧表中的人员姓名。要计算第一位销售人员的“订单数，很简单，输入函数公式 “=COUNTIF$C$2:$C$16,E2即可见图1。二兄弟：SUMIF函数条件求和SUM函数的作用是对数据求和，而SUMIF对它进展了引伸和拓展，比方计算“金额在1元以上的数据总和、按照人员或产品分类计算数据总和等等。它有3个参数，分别是条件区域、判断条件、实际的求和区域假设它与“条件区域是一个区域，就可省略。在上例中，计算每位“销售人员的订单总金额，就要使用SUMIF函数来协助了。假设要计算每个

13、人的销售订单总金额，把左侧表的“销售人员一列当作“条件区域，把右侧表的每个名单当作求和“条件，把左侧表的每笔“订单金额当作“实际求和区域，在G2单元格中输入数据计算公式 “=SUMIF$C$2:$C$16,E2,$B$2:$B$16见图2，第1名销售人员的“订单总额就瞬间产生了。小提示：在本例的COUNTIF函数和SUMIF函数中，由于“销售人员区域与“订单总额区域都是固定的，所以在函数中引用这两列地址时，要使用“绝对地址，也就是在地址前添加“$符号。三兄弟：IF函数逻辑高手IF函数是一个条件函数，它可以通过设置的条件进展逻辑判断。假设在刚刚的数据汇总表中再添加一列“销售奖金数据，“销售奖金发

14、放的方法是：假设某个人的订单总额大于￥1500,000，那么“奖金数用总额×5%，否那么“奖金就用总额×3%。所以，第1位销售人员“销售奖金的计算公式应为“=IFG2>1500000,G2*0.05,G2*0.03，如图3所示。结合刚刚的知识，推测一下这个公式中3个参数的涵义吧。“G2>1500000是IF函数的判断条件，“G2*0.05是条件成立的操作，“G2*0.03是条件不成立的操作。名师点睛：*数据的分类汇总是Excel最常见的应用。分类汇总的方法很多，包括使用函数和公式。设置的条件可看作是分类的根据，用“COUNTIF函数进展分类计数汇总，用“SUMI

15、F函数进展分类求和汇总，非常方便。*IF函数的作用是根据判断条件的真假，自动进展分支操作。比方根据身份证号码的奇偶来判断性别，根据绩效数据来填写成绩等等。在实际应用时，一个非常实用的应用是：把IF函数的“真或“假参数嵌套成另一个IF函数，实现多种分支操作。如2个IF函数的嵌套可写成“=IF条件，真，IF条件，真，假，这样一来就可以实现3个分支判断了在Excel中，最多是7层函数嵌套。*重复数据的挑选问题常常让我们感到非常棘手。COUNTIF函数除了可以实现分类计数汇总外，它和IF函数配合，还可以实现对重复数据的标识与挑选，从而将1列中的重复数据删除。在如以以下图4所示的案例中，使用了公式“=I

16、FCOUNTIF$C$1:C1,C1>1,1,0为重复数据的后面添加了标识“1，为不重复数的后面添加了标识“0。在公式中，COUNTIF是条件计数，可利用它统计出从这列的第1个数据起，某个数出现了几次，再用IF函数判断，假设出现2次以上就添加“1，假设出现1次那么就添加“0。最后用Excel中的自动挑选功能，将所有标识为“1的数据行删除，就能把重复数据统统清理掉了。与“IF相关的函数三兄弟，不但为运算添加了“条件，而且为我们打通了很多解决问题的道路，只要我们肯用心开掘，一定还可以找到更多的问题解决之道很多单位都组建了局域网，虽然有不少能在局域网上聊天的工具，但一来使用不方便，二来不平安容

17、易被老板发现。为此，笔者建议大家用Excel来聊天。 用Excel来聊天第一步:翻开Excel2003，新建一个工作簿文档，取名保存如“工作记录.xls。第二步:执行“工具共享工作簿，翻开“共享工作簿对话框见图1，选中“允许多用户同时编辑，同时允许工作簿合并选项，确定返回。 第三步:将上述工作簿文档保存在局域网上某台电脑的一个共享文件夹中。 第四步:局域网内用户同时翻开上述工作簿，大家约定好输入的单元格位置如A用户在A列输入内容、B列输入时间；B用户在C列输入内容、D列输入时间。 第五步:选定相应的单元格如A1，将聊天内容输入到其中，再选中B1单元格，按下“Ctrl+Shift+;组合键输入系统当前时间。第六步:单击工具栏上的“保存按钮，将上述输入内容保存一下，对方只要再按一下“保存按钮，即可看到上述输入的内容。 第七步:假设老板来了，只要切换到其他工作表如Sheet2中可