数据有效性的妙用

WPS表格“数据有效性”在数据录入中的妙用利用WPS表格强大的制表功能，我们能够很轻松完成表格制作，数据的统计分析等任务。

但是在数据录入的过程中难免会输入一些错误的数据。

比如重复的学号、身份证号，超出正常范围的无效数据。

另外，怎么样快速输入一些有特定范围并且重复输入的数据。

这些问题我们都可以利用WPS中“数据有效性”这一工具很好的解决。

输入前设置“数据有效性”避免错误案例一：在WPS表格中输入数据时，有时会要求某列或者某个区域的单元格数据具有唯一性，比如学生的学号，员工的ID，身份证号等。

我们在输入时有时会出错导致数据重复出现，而且这种错误又很难检查。

我们就可以利用“数据有效性”来解决这一问题。

1、打开工作薄，如下图1，图12、选中“学号”一列，点击“数据”菜单，选择“有效性”，如图2，图23、打开“数据有效性”对话框，在“设置”选项下，单击“允许”右侧的下拉按钮，在弹出的下拉菜单中，选择“自定义”选项，然后在下面“公式”文本框中输入公式“=COUNTIF(a:a,a1)=1”（不包括引号），如图3，图34、切换到“出错警告”填写标题和错误信息，单击“确定”，如图4，图45、下面我们开始输入一些数据检验一下效果。

输入不重复的数据时，可以正常显示，如图5，图5当输入重复数据时，弹出错误警告对话框，要求要求用户检查后输入正确数据，如图6，图6注：=COUNTIF（A：A，A1）=1 意思是,在A列每个单元格只能出现一次,当第二次出现时因为函数值,COUNTIF（A：A，A1）=2,将会出现错误提示. =COUNTIF（A：A，A1）>1 意思是,在A列当一个单元格的内容在该列是第二次,第三次……出现，单元格变变成红色。

案例二：我们在使用WPS表格处理数据时，很多情况下输入的数据是有一定的范围的，比如输入学生的各科成绩时，一般是百分制，分数的范围是0—100之间的小数。

超过范围的就是无效数据，我们可以输入数据之前设置数据的有效范围避免出现错误。

“数据有效性”几点妙用在Excel中，利用“数据有效性”不但能够限制数值输入位数、限定数值输入范围及避免数据重复输入等，而且还能够轻松圈出指定数据。

限制数值的可输入位数实例描述：小王是办公室的文秘，她需要录入单位职工的基本信息。

由于身份证号码多达15或18位，有时多输一位，有时少输一位，输入起来非常容易出错。

如果对单元格进行限制，当输入的位数不对时，就给出提示，岂不是很好？其实可以利用设置“数据有效性”就可以达到上述要求的。

首先，为了让输入的身份证号码能正确显示出来，需要输入身份证号码的单元格区域（如E3至E305）请将单元格设置为文本格式。

然后，选择“数据→有效性”菜单，打开“数据有效性”对话框，在“设置”选项卡的有效条件“允许”处选择“自定义”，在出现的“公式”下面的方框中输入公式“=OR(LEN(E3)=15,LEN(E3)=18)”，再选择“出错警告”选项卡并设置一个“出错警告”，确定返回。

这样，但输入的号码位数不正确时，系统会给出提示（图1）。

图1避免重复数据的输入实例描述：小王在手工录入职工信息时，职工的身份证号码都是唯一的。

由于需要录入的职工信息有300多条，很容易造成视觉疲劳，看错行，从而造成数据的重复录入。

为了解决上述问题，可以通过“数据有效性”来防止数据的重复输入。

选中需要输入身份证号码的单元格区域(如E3至E305)，同样选择“数据→有效性”菜单，打开“数据有效性”对话框，在“设置”选项卡的有效条件“允许”处选择“自定义”，然后在下面“公式”方框中可将公式修改为: =AND(COUNTIF(E:E,E3)=1，OR(LEN(E3)=15,LEN(E3)=18))，其中COUNTIF(E:E,E3)=1就是为了防止数据重复录入的公式，确定返回。

以后在上述单元格中无论输入了重复的身份证号码还是录入位数发生错误时，系统会弹出提示对话框，并拒绝接受输入的号码（图2）。

图2限定输入数值的范围实例描述：小郑是教务处的干事，需要输入学生的实验考查成绩，考查成绩通常介于0—20之间的整数。

数据有效性数据有效性是指数据在一定范围内符合某种条件或规范，具有可信度和准确性。

在数据处理和分析中，数据有效性是非常重要的一环，因为它直接影响到数据的准确性和可信度，从而影响到整个分析结果的可靠性。

一、数据有效性的重要性1.提高数据质量：数据有效性可以确保数据的准确性和可信度，减少错误和异常数据对分析结果的影响，从而提高数据的质量。

2.减少数据分析误差：数据分析的误差往往来自于无效或低质量的数据。

通过提高数据的有效性，可以减少数据分析的误差，提高分析结果的准确性和可靠性。

3.保护数据安全：在数据处理和分析过程中，如果数据存在有效性问题，可能会导致数据的丢失、篡改或泄露，从而给数据安全带来威胁。

因此，数据有效性也是保护数据安全的重要手段之一。

二、数据有效性的判断方法1.范围判断：根据数据的性质和实际情况，确定数据的合理范围。

如果数据超出这个范围，则认为数据无效。

例如，对于年龄字段，如果数据为负数或超过最大可能的年龄，则认为数据无效。

2.规则判断：根据数据的特征和业务规则，设定一些规则来判断数据的有效性。

例如，对于信用卡账单数据，可以通过规则判断是否存在不合规的交易行为，如是否出现负消费等。

3.逻辑判断：通过逻辑推理来判断数据的有效性。

例如，在分析公司财务数据时，可以通过逻辑判断来检查是否存在不合理的数据，如收入和支出的比例是否符合实际情况等。

4.对比判断：将不同数据源或不同时间点的数据进行对比，以判断数据的有效性。

例如，可以通过对比历史数据和当前数据来判断是否存在异常变化。

5.机器学习算法判断：利用机器学习算法对数据进行训练和学习，自动识别出不符合规则的数据，提高数据的有效性和准确性。

三、提高数据有效性的方法1.建立完整的数据质量管理体系：从数据的采集、存储、处理和分析等方面建立完善的数据质量管理体系，确保数据的完整性和准确性。

2.引入先进的数据处理工具和技术：采用先进的数据处理工具和技术，如自然语言处理、图像识别等，提高数据处理的速度和质量。

数据的有效性作用
数据的有效性是指数据的准确性、完整性、可靠性和相关性。

在数据分析和决策过程中，数据的有效性起着重要的作用：
1. 支持决策制定：有效的数据提供了有关组织所面临问题和机会的准确信息，帮助管理层基于实际情况做出明智的决策。

2. 识别趋势和模式：有效的数据能够揭示隐藏在数据背后的趋势和模式，为组织提供了洞察力，帮助做出战略规划和预测未来走向。

3. 发现问题和机会：有效的数据可以帮助组织发现内部和外部环境中的问题和机会，从而改进业务流程和提高竞争力。

4. 改进绩效评估：有效的数据可以用于评估和监测组织的绩效表现，发现潜在问题并实施改进措施。

5. 支持创新和研发：有效的数据可用于支持创新和研发活动，帮助组织发现新产品、新服务和新市场，以及改善现有业务。

总之，数据的有效性是数据分析和决策过程中的关键要素，对组织的发展和竞争力有重要影响。

Excel数据有效性有啥用？多级下拉选项设置，让你的工作更
高效
Excel数据有效性是Excel表格操作技巧中，非常重要的一个组成部分。

其中数据有效性最重要的一个环节那就是下拉菜单栏的设置，通过设置下拉菜单栏，我们可以将表格变得更加规范而且更加有效率。

今天我们就来详细的学习一下，一、二、三级下拉菜单栏如何设置。

技巧一：一级下拉菜单栏制作
一级下拉菜单选项设置是数据有效性中最简单的操作方法：
第一步：选择需要制作下拉菜单栏的区域，点击数据—数据有效性—清单/序列；
第二步：数据区域选择对应的一级栏目即可。

技巧二：二级下拉菜单栏制作
二级菜单栏制作方法其实也非常简单，它需要我们根据之前做的一级菜单栏设置后，在对其进行另外的操作：
第一步：先按照一级菜单栏的方法制作一级下拉菜单栏；
第二步：选择一级栏和二级菜单栏区域，因为一级菜单栏目录为左边一列。

所以点击公式—根据所选内容创建—最左列。

第三步：选择需要制作二级菜单栏的区域，点击数据—数据有效性，选择序列，来源使用公式=Indirect(H3)，H3也就是一级栏目所在位置。

技巧三：三级下拉菜单栏制作
与二级下拉菜单栏相似，当我们需要制作更高级下拉菜单选项时，我们就需要用到这个多级制作方法。

第一步：创建二级目录与三级目录的区域，
第二步：同二级下单栏制作方法，按同样的方法制作三级下拉菜单栏，数据来源同样用函数=Indirect（I3），I3为二级目录所在位置。

通过上面的学习，现在你学会了如何快速制作多级下拉菜单选项了吗？
操作技巧：
（此处已添加圈子卡片，请到今日头条客户端查看）。

Excel技巧应用篇：数据有效性工具Excel的数据有效性是一个十分有用的工具，不仅可以防止在单元格输入无效的数据，还能够有效提高数据录入的效率。

点击“数据”选项卡，点击“数据有效性”工具，然后选择“数据有效性”菜单，便可打开“数据有效性”对话框。

首先来看看“设置”标签，点击“允许”下拉框，可以查看对单元格进行数据有效性设置的种类，如果选择其中的“整数”、“小数”、“日期”、“时间”或者“文本长度”，系统会要求同时设置数据的大小起始或者长短等，则相应单元格只能输入限制范围内的该数据类型，否则系统报错。

如果选择“序列”，则需要给定一个清单，单元格只能在给定的清单中选择输入，当光标移至该单元格时，会出现供选择的下拉框。

用户也可以选择在“自定义”，利用函数公式进行更加灵活的数据有效性设置。

点开“输入信息”标签，可以在“输入信息”中备注录入提醒等，光标选中单元格时可以显示这些提醒信息。

最后再看看“出错警告”标签。

通过选择“样式”可以设置单元格输入无效数据后的报错方式：“停止”表示如果输入无效数据必须重新输入否则不能继续下一步；选择“警告”和“信息”可以忽略错误继续下一步操作。

用户还可以在“错误信息”框中输入自定义的错误信息替代系统预设的“错误信息”。

“数据有效性”按钮之下还有“圈释无效数据”和“清除无效数据标识圈”菜单，其作用是用红色椭圆标识已经设置数据有效性但录入了无效数据的单元格和清除该标识。

接下来，我们用实例说明。

1、限制只能输入早于今天的日期光标选中单元格或者单元格区域，如前所述，打开“数据有效性”对话框，“允许”下拉框中选择“日期”，“数据”下拉框选择“小于或等于”，在“结束日期”处键入公式“=T oday()”，表示只允许录入小于等于今天即录入日的日期。

Excel中的日期实质其实就是数字，一般采用的是1900年日期系统，即数字1 代表1900/1/1，日期每增加一天，数字加1，所以日期也是可以比较大小的。

技巧1 在单元格创建下拉列表有许多新手在EXCEL中第一次见到下图所示的下拉列表时，都以为是程序做的，当他们知道图中下拉列表只是一个普通的利用数据有效性完成的EXCEL技巧时，他们会觉得很惊奇。

那么，现在我们一起学习一下，怎么利用数据有效性来做个下拉列表吧：第一步在一个连续的单元格区域输入列表中的项目,如图中E7:E11有个商品名称的表第二步选中A2单元格，单击“菜单”——“数据”——“有效性”,在“数据有效性”对话框的"设置"选项卡中,在“允许”下拉列表中选择“序列”项.第三步在"来源"框中输入“=$E$8:$E$11”（或输入“=”号后，用鼠标选中E8:E11）第四步勾选"忽略空值"与"提供下拉箭头"复选框,如图所示第五步单击"确定"按钮,关闭"数据有效性"对话框. 这样,就能实现第一张图所示的效果了。

如果列表的内容较少,或者不方便在工作表中输入列表项目,也可以省略上述的第一步,然后将第三步的操作改为:直接在"来源"框中输入列表内容,项目之间以半角的逗号分隔.如图所示在一般情况下,数据的有效性中的序列来源,只能引用当前工作表中的单元格区域。

如果希望能够引用其他工作表中的单元格区域,则必须先为单元格区域定义名称,然后在"来源"框中输入名称.例如,将另一张工作表中的A2:A10区域，名称定义为“SPMC”,然后在“数据有效性”的“来源”框中输入“=SPMC”。

当我们需要对表格中的项目进行特别说明时,常常会使用EXCEL的批注功能。

给单元格做批注的方法，这里不多浪费时间。

而给大家介绍一下另类批注：使用批注多了，我们会发现EXCEL的批注也有不足之处:一、批注框的大小尺寸会受到单元格行高变化的影响;二、批注框的默认情况下，是只显示标识符。

必须把光标悬停在单元格的上方批注内容才会显示出来，否则即使当单元格处于活动状态时，它也不会显示;三、是在上面2种情况的共同作用下，加上拆分（冻结）窗口下的插入、拖曳等工作表操作，会导致批注的位置远离原来的单元格，而被主人遗忘，并随着主人对单元格的复制或格式刷操作而被大量复制，这也是造成文件增肥的主要原因之一。

【Excel基础知识】数据有效性的五个高级用法（上）公众号回复2016 下载office2016预习内容：【Excel使用攻略】学会这一招，再也不怕底下人乱填表格了！数据有效性是对单元格或单元格区域输入的数据从内容到数量上的限制。

对于符合条件的数据，允许输入；对于不符合条件的数据，则禁止输入。

这样就可以依靠系统检查数据的正确有效性，避免错误的数据录入。

在实际工作中，往往使用数据有效性来制作下拉菜单，提高输入效率。

如下面的例子：对D2单元格进行有效性设置，允许处填序列，来源处选择提前设定好的内容区域。

确定后效果如下图：这样，一个简单的下来输入菜单就完成了。

但是，当遇到来源范围不在一列的时候，例如下图：再按之前的做法就会弹出对话框，因为数据列表必须是单一行或列。

这时候，我们就需要使用第一个高级技巧了。

多列数据源的有效性如何实现？1、选择单列数据源后定义名称选择一部分数据列表，定义名称：选择单列的数据列表后，在名称栏出输入“bm”，确定后效果如图：bm是代办部门的缩写，也可以用其他字符代替。

这里涉及到了另一个重要的知识点，名称的用法，请持续关注，今后会有名称的用法分享！选定其他区域后则显示单元格地址，如果显示为名称，则说明定义成功。

2、设置数据有效性接下来可以设置数据有效性：注意来源处输入：=bm，bm就是刚才定义的名称，如果你使用的其他名称，这里要做相应的修改。

确定后可以看到效果：下拉选项有了，但是并不是全部内容，仅仅是a列的四个内容。

接下来要做的就是第三步，也是最重要的一步了。

3、修改名称按住ctrl健，按一下f3功能键，调出名称管理器，如图：将引用位置修改为数据的实际区域，完成修改后点一下左边的“√”：然后关闭对话框。

弹出的对话框直接点是，再来看看我们的下拉选项，是不是很惊喜呢？至此，关于多列数据源的有效性引用就完成了。

今天学习的这个技巧里，用到了有效性的基本设置和使用名称框快速定义名称等技巧。

数据有效性的优点及应用数据有效性是指数据是否准确、完整、可信、相关、及时以及适用于特定应用的性质。

在信息时代，有效的数据对于个人、组织和社会都具有重要的意义。

以下是数据有效性的优点及应用。

一、优点：1.决策支持：有效的数据可以为决策提供准确、完整的信息，减少决策风险。

它能够帮助管理者更好地理解现状、预测趋势以及制定合适的策略。

2.提高效率：有效的数据可以促进信息的传递与共享，减少信息的重复收集和处理。

有了正确的数据，人们可以有效地进行工作，提高工作效率。

3.改善质量：有效的数据能够提供真实、准确的信息，帮助企业提高产品和服务的质量。

通过对数据进行分析，企业可以及时发现问题并采取措施进行改进。

4.增强竞争力：有效的数据能够提供有关市场、客户和竞争对手的信息，帮助企业制定更合适的战略，增强竞争力。

5.降低成本：有效的数据可以帮助企业减少资源浪费和成本开支。

通过对数据进行分析，企业可以找到优化的方式，提高资源利用效率，从而降低成本。

二、应用：1.市场营销：有效的数据可以帮助企业了解市场需求和客户偏好，制定针对性的营销策略。

通过对数据的分析，企业可以找到潜在客户和市场机会，提高市场营销效果。

2.供应链管理：有效的数据可以帮助企业更好地管理供应链，优化物流与库存。

通过对数据进行分析，企业可以及时调整供应计划，降低库存成本，提高供应链的响应能力。

3.客户关系管理：有效的数据可以帮助企业建立和维护良好的客户关系。

通过对客户数据的分析，企业可以了解客户需求和行为，提供个性化的服务，并增加客户满意度和忠诚度。

4.风险管理：有效的数据可以帮助企业监控和评估风险，制定风险管理策略。

通过对数据的分析，企业可以识别潜在风险，并采取相应的措施降低风险。

5.绩效评估：有效的数据可以帮助企业评估和监控绩效。

通过对数据的分析，企业可以了解员工和部门的绩效情况，发现问题并及时进行调整，以提高整体绩效水平。

6.科学研究：有效的数据对于科学研究至关重要。