excel 中数据的有效性的应用.docx

excel 中数据的有效性的应用.docx

《excel 中数据的有效性的应用.docx》由会员分享，可在线阅读，更多相关《excel 中数据的有效性的应用.docx（11页珍藏版）》请在冰豆网上搜索。

excel中数据的有效性的应用

excel中数据的有效性的应用

       3、防止数据输入错误

       典型应用如下：

（1）防止日期错误：

       只准输入日期或某个日期之后的特定日期：

点击EXCEL菜单“数据-有效性-”，在“设置-允许”对话框中选择“日期”、并在相应位置输入起始日期。

（2）只准输入整数：

       在“设置-允许”对话框中选择“整数”

       （3）防止输入重复值：

       首先要选定整行/列或单元格区域（比如选中B列），然后再点击EXCEL菜单“数据-有效性-”，在“设置”对话框中选择“自定义”、在“公式”中输入“=COUNTIF（B:

B,B1）<2”

       检验一下：

在B3单元格中输入“A”，看看会出现什么结果？

       （4）只能输入大于上一行的数值（或日期）：

       注意引用区域的最后一行行标为相对引用。

A4的有效性公式为：

=MAX（$B$3:

$B4）

       4、条件输入

       只准输入符合一定条件的数据：

（1）只能输入大于左侧的数字

（2）按条件输入_根据左侧条件来决定右侧单元格如何输入：

       下图中，如果单据类型选择了“入库单”，则只能在“入库数量”所在列即C列输入数据，而不能在“出库数量”所在列即D列输入数据；反之亦然。

       C列公式为：

=IF（B5="入库单",ISNUMBER（C5）,FALSE）

       D列公式为：

=IF（B5="出库单",ISNUMBER（D5）,FALSE）

[应用一]下拉菜单输入的实现

例1：

直接自定义序列

有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：

优秀，良好，合格，不合格。

我们希望Excel2000单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。

操作步骤：

先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！

）；选上"忽略空值"和"提供下拉菜单"两个复选框。

点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。

例2：

利用表内数据作为序列源。

有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。

操作步骤：

先在同一工作表内的打印区域外要定义序列填好（假设在在Z1：

Z8），如“单亲家庭，残疾家庭，残疾学生，特困，低收人，突发事件，孤儿，军烈属”等，然后选择要实现效果的列（资助原因）；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域Z1：

Z8（如果记得，可以直接输入=$Z$1:

$Z$8；选上"忽略空值"和"提供下拉菜单"两个复选框。

点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。

例3：

横跨两个工作表来制作下拉菜单

用INDIRECT函数实现跨工作表

在例2中，选择来源一步把输入=$Z$1:

$Z$8换成=INDIRECT（"表二!

$Z$1:

$Z$8"）,就可实现横跨两个工作表来制作下拉菜单。

[应用二]自动实现输入法中英文转换

有时，我们在不同行或不同列之间要分别输入中文和英文。

我们希望Excel能自动实现输入法在中英文间转换。

操作步骤：

假设我们在A列输入学生的中文名，B列输入学生的英文名。

先选定B列，点击进入"数据\有效性"，打开"数据有效性"对话框；选择"输入法"对话框，在"模式"下拉菜单中选择"关闭（英文模式）"；然后再"确定"，看看怎么样。

[应用三]数据唯一性检验

员工的身份证号码应该是唯一的，为了防止重复输入，我们用“数据有效性”来提示大家。

操作步骤：

选中需要建立输入身份证号码的单元格区域（如B2至B14列），执行“数据→有效性”命令，打开“数据有效性”对话框，在“设置”标签下，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自定义”选项，然后在下面“公式”方框中输入公式：

=COUNTIF（B:

B,B2）=1，确定返回。

以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并拒绝接受输入的号码。

运用Excel三大条件函数解决实际问题！

秋尽冬至，又到一年年终时。

在工作中，数据的运算量和汇总的操作一下比平时多了不少。

众所周知，在Excel中可以对数据进行各种不同的运算和汇总，今天我向大家介绍与条件相关的函数三兄弟，分别是“COUNTIF”、“SUMIF”和“IF”函数。

他们有一个共同的特点——都姓字符“IF”。

大兄弟：

COUNTIF函数（计数求和）

COUNT函数，顾名思义是用来计数的，统计所选择区域的数值型单元格个数。

COUNTIF是COUNT函数的引伸与拓展，在计数时加上先前条件，只有符合计数的条件才进行统计计算。

比如，从员工信息表中，计算出有多少人的年龄大于35岁。

下面我们来看一个典型的分类计数汇总的例子。

这里有一张销售流水记录表，每名销售人员累计做了多少“销售订单个数”呢？

大兄弟COUNTIF正常工作需要两个参数——条件区域（本例为左侧表中“销售人员”一列）和计数条件（本例为右侧表中的人员姓名）。

要计算第一位销售人员的“订单数”，很简单，输入函数公式“=COUNTIF（$C$2:

$C$16,E2）”即可（见图1）。

二兄弟：

SUMIF函数（条件求和）

SUM函数的作用是对数据求和，而SUMIF对它进行了引伸和拓展，比如计算“金额”在1元以上的数据总和、按照人员或产品分类计算数据总和等等。

它有3个参数，分别是条件区域、判断条件、实际的求和区域（如果它与“条件区域”是一个区域，就可省略）。

在上例中，计算每位“销售人员”的订单总金额，就要使用SUMIF函数来协助了。

如果要计算每个人的销售订单总金额，把左侧表的“销售人员”一列当作“条件区域”，把右侧表的每个名单当作求和“条件”，把左侧表的每笔“订单金额”当作“实际求和区域”，在G2单元格中输入数据计算公式“=SUMIF（$C$2:

$C$16,E2,$B$2:

$B$16）”（见图2），第1名销售人员的“订单总额”就瞬间产生了。

小提示：

在本例的COUNTIF函数和SUMIF函数中，由于“销售人员”区域与“订单总额”区域都是固定的，所以在函数中引用这两列地址时，要使用“绝对地址”，也就是在地址前添加“$”符号。

三兄弟：

IF函数

逻辑高手IF函数是一个条件函数，它可以通过设置的条件进行逻辑判断。

如果在刚才的数据汇总表中再添加一列“销售奖金”数据，“销售奖金”发放的方法是：

如果某个人的订单总额大于￥1500,000，那么“奖金”数用总额×5%，否则“奖金”就用总额×3%。

所以，第1位销售人员“销售奖金”的计算公式应为“=IF（G2>1500000,G2*0.05,G2*0.03）”，如图3所示。

结合刚才的知识，推测一下这个公式中3个参数的涵义吧。

“G2>1500000”是IF函数的判断条件，“G2*0.05”是条件成立的操作，“G2*0.03”是条件不成立的操作。

名师点睛：

*数据的分类汇总是Excel最常见的应用。

分类汇总的方法很多，包括使用函数和公式。

设置的条件可看作是分类的依据，用“COUNTIF函数”进行分类计数汇总，用“SUMIF函数”进行分类求和汇总，非常方便。

*IF函数的作用是根据判断条件的真假，自动进行分支操作。

比如根据身份证号码的奇偶来判断性别，根据绩效数据来填写成绩等等。

在实际应用时，一个非常实用的应用是：

把IF函数的“真”或“假”参数嵌套成另一个IF函数，实现多种分支操作。

如2个IF函数的嵌套可写成“=IF（条件，真，IF（条件，真，假））”，这样一来就可以实现3个分支判断了（在Excel中，最多是7层函数嵌套）。

*重复数据的筛选问题常常让我们感到非常棘手。

COUNTIF函数除了可以实现分类计数汇总外，它和IF函数配合，还可以实现对重复数据的标识与筛选，从而将1列中的重复数据删除。

在如下图4所示的案例中，使用了公式“=IF（COUNTIF（$C$1:

C1,C1）>1,”1”,”0”）”为重复数据的后面添加了标识“1”，为不重复数的后面添加了标识“0”。

在公式中，COUNTIF是条件计数，可利用它统计出从这列的第1个数据起，某个数出现了几次，再用IF函数判断，若出现2次以上就添加“1”，若出现1次则就添加“0”。

最后用Excel中的自动筛选功能，将所有标识为“1”的数据行删除，就能把重复数据统统清理掉了。

与“IF”相关的函数三兄弟，不但为运算添加了“条件”，而且为我们打通了很多解决问题的道路，只要我们肯用心发掘，一定还能够找到更多的问题解决之道

很多单位都组建了局域网，虽然有不少能在局域网上聊天的工具，但一来使用不方便，二来不安全（容易被老板发现）。

为此，笔者建议大家用Excel来聊天。

 用Excel来聊天

　　★第一步:

打开Excel2003，新建一个工作簿文档，取名保存（如“工作记录.xls”）。

　　★第二步:

执行“工具→共享工作簿”，打开“共享工作簿”对话框（见图1），选中“允许多用户同时编辑，同时允许工作簿合并”选项，确定返回。

　　★第三步:

将上述工作簿文档保存在局域网上某台电脑的一个共享文件夹中。

　　★第四步:

局域网内用户同时打开上述工作簿，大家约定好输入的单元格位置（如A用户在A列输入内容、B列输入时间；B用户在C列输入内容、D列输入时间……）。

　　★第五步:

选定相应的单元格（如A1），将聊天内容输入到其中，再选中B1单元格，按下“Ctrl+Shift+;”组合键输入系统当前时间。

　　★第六步:

单击工具栏上的“保存”按钮，将上述输入内容保存一下，对方只要再按一下“保存”按钮，即可看到上述输入的内容。

　　★第七步:

如果老板来了，只要切换到其他工作表（如Sheet2）中（可以事先输入一些与工作相关的内容），即可转换到“工作状态”。

既方便又安全！

　　小提示

　　提高效率，多人同时录入一个Excel文件这个应用主要利用了Excel的“允许多用户同时编辑，同时允许工作簿合并”功能，在实际工作中，可以用做多人共同录入一个Excel表格，Excel会自动保持信息不断更新。

比如:

有A、B、C、D四个用户分工合作共同完成2005.xls文件的录入，首先打开这个文件，并按上面的操作勾选“允许多用户同时编辑，同时允许工作簿合并”，最后点击“文件→另存为”，将其保存在E电脑的D盘中。

接下来四个用户就可以同时在这个文件中录入了。