超实用：用数据有效性+函数
EXCEL控的数据校验与错误处理技巧
EXCEL控的数据校验与错误处理技巧在现代信息化高速发展的背景下，电子表格软件Excel已经成为了重要的办公工具之一。
而在Excel的使用过程中，数据校验与错误处理是每个Excel控必须掌握和运用的技巧之一。
本文将就Excel控的数据校验与错误处理技巧进行详细的论述。
一、数据校验技巧1. 设置数据有效性在Excel中，可以通过设置数据有效性来限制用户输入的数据范围，以达到数据校验的目的。
选择需要进行校验的单元格，点击“数据”-“数据工具”-“数据有效性”，在弹出的对话框中选择合适的条件和设置，如设置数字范围、日期范围、文本长度等，从而确保数据的准确性。
2. 使用公式进行条件判断在Excel中，也可以通过使用公式进行条件判断，以实现数据的校验。
例如，通过使用IF函数、AND函数、OR函数等，可以根据用户定义的条件来判断数据是否有效。
通过将校验公式应用到相应的单元格中，可以实现数据的自动校验。
3. 利用条件格式设置校验规则除了设置数据有效性和使用公式进行条件判断外，还可以通过条件格式来设置校验规则。
选择需要进行校验的单元格或区域，点击“开始”-“条件格式”，选择相应的条件格式，如数据条、色阶、图标等，然后设置相应的条件，根据条件的满足情况对数据进行自动标注或着色，从而方便地进行数据校验。
二、错误处理技巧1. 使用IFERROR函数处理错误在Excel中，经常会出现各种各样的错误，如除以零错误、引用错误等。
为了更好地处理这些错误，可以使用IFERROR函数来捕捉错误并进行相应的处理。
通过使用IFERROR函数，可以使得在出现错误时显示指定的值或执行特定的操作，从而提高数据的处理效率。
2. 利用条件格式标识错误除了使用IFERROR函数处理错误外，还可以借助条件格式来标识错误。
通过设置条件格式，将错误的单元格以特定的格式进行标记，如加粗、红色填充等，使得错误的数据一目了然，方便及时发现和修正。
3. 利用筛选和排序功能定位错误当Excel中存在大量数据时，通过筛选和排序功能可以方便地定位错误。
运用excel函数公式统计学生成绩的方法
下面是几个常用的公式，一定用得着哦。
1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。
1、求和： =SUM(K2:K56) ——对K2到K56这一区域进行求和；2、平均数： =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数；3、排名： =RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名；4、等级： =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))5、学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩；6、最高分： =MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分；7、： =MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分；8、分数段人数统计：=COUNTIF(E3:G16,">=50")-COUNTIF(E3:G16,">59")去掉最高分，最低分后求平均分=(SUM(C2:C8)-MAX(C2:C8)-MIN(C2:C8))/(COUNT(C2:C8)-2) trimmean(a1:a10,2/10) –A1到A10中，去掉20%的人数；（1） =COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格；（2） =COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；（3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格；（4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；（5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；（6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；（7） =COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；说明：COUNTIF函数也可计算某一区域男、女生人数。
数据验证Excel的数据有效性设置技巧
数据验证Excel的数据有效性设置技巧在Excel 中进行数据验证是非常重要的，因为它可以确保你的数据是准确无误的。
Excel提供了各种不同的验证选项，可以帮助你对数据进行验证和限制。
在本文中，我将告诉你如何使用数据验证来设置Excel数据的有效性。
1.Dropdown数据验证Dropdown数据验证可以帮助你创建数据清单中的下拉列表。
这样可以使数据的输入更准确和方便，也有助于减少数据输入中的错误。
具体操作如下：首先，你需要打开Excel表格，然后选择单元格，然后点击数据工具菜单中的数据验证选项。
在弹出的窗口中，选择“列表”选项，然后在“源”框中输入你需要添加的选项。
最后点击“确定”按钮完成设置。
2.日期数据验证日期数据验证可以帮助你确保输入的日期格式是正确的。
具体操作如下：首先，你需要打开Excel表格，然后选择需要添加日期数据验证的单元格，然后点击数据验证选项卡，在下拉菜单中选择“日期”选项。
在“设置”框中，选择你需要使用的日期格式，然后点击“确定”按钮即可完成设置。
3.数字数据验证数字数据验证可以帮助你确保输入的数字类型是正确的。
具体操作如下：首先，你需要打开Excel表格，然后选择需要添加数字数据验证的单元格，然后点击数据验证选项卡，在下拉菜单中选择“整数”或“小数”选项。
在“设置”框中，输入你需要使用的数字范围，然后点击“确定”按钮即可完成设置。
4.文本数据验证文本数据验证可以帮助你确保输入的文本类型是正确的。
具体操作如下：首先，你需要打开Excel表格，然后选择需要添加文本数据验证的单元格，然后点击数据验证选项卡，在下拉菜单中选择“文本长度”或“自定义”选项。
在“设置”框中，输入你需要使用的文本内容，然后点击“确定”按钮即可完成设置。
数据验证是一个非常重要的Excel功能，可以方便快捷地验证和限制输入数据的类型和格式。
在你使用Excel时，务必学习和掌握数据验证的技巧，以提高你的数据准确性和效率。
excel表格显示当天时间函数怎么用
excel表格显示当天时间函数怎么用
1：我们通常加入系统时间是在单元格内输入=NOW()函数，系统自动生成当前时间。
这样做如果该工作表中任意一个单元格数据发生变化，所有使用=NOW()生成时间的单元格都会跟随全部变化。
2：为了实现时间不跟随表格内容改变而改变，达到固定时间目的，我们通过使用数据有效性来实现。
在任意单元格插入=NOW()函数，如实验表格中D1列。
3：单击【数据】选项卡，选择【数据有效性】选项，弹出数据有效性设置窗口。
4：设置有效性条件为“序列”，数据来源设置为步骤2中的D1列，将引用改为绝对引用，即"=$D$1"，点击确定保存。
5：在A3、A4、A5单元格输入内容测试，会发现，B3、B4、B5时间不会随着单元格变化而变化。
但直接使用NOW()函数的A1、A2单元格却会跟随着进行变化。
猜你感兴趣：。
Excel实用技巧 怎么样在Excel 2013中设置数据有效性
怎么样在excel 2013中设置数据有效性第一步：打开需要设置数据有效性的excel表格（我们需要在B列输入限定的电脑品牌）2第二步：选中需要设置有效性的“B列”，再选中工具栏的“数据”底下的“数据验证”第三步：打开“数据验证”，“设置“--验证条件--允许”序列“第四步：来源选择已设置好的序列内容，点击”确定“第五步：选中”B“列，熟悉的数据有效性又回来了允许设置的有效性标准类型在数据有效性设置对话框中“允许”下拉框下可设置的数据类型包括以下几种：①任何值：当选择任何值时，Excel会取消当前单元格及区域的数据有效性设置。
但是该数据类型仍然允许我们设置输入信息，在用户选中单元格或区域时给用户相应的提示。
②整数：当选择整数时，用户的输入必须为一个整数，否则无效。
我们可以通过数据下拉列表指定输入数值的范围。
③小数：当选择小数时，用户的输入必须为一个数值，否则无效。
我们可以通过数据下拉列表指定输入数值的范围。
④序列：当选择序列时，用户需要设置输入项列表限定可以输入的内容，非列表项的输入无效。
设置完成后，当用户选择单元格时会出现一个下拉列表，列明所有有效值，用户可以从中选择来完成输入。
⑤日期：当选择日期时，用户的输入必须为一个日期，否则无效。
我们可以通过数据下拉列表指定输入日期的范围。
⑥时间：当选择时间时，用户的输入必须为一个时间值，否则无效。
我们可以通过数据下拉列表指定输入时间的范围。
⑦文本长度：当选择文本长度时，将限制用户输入文本长度或数据位数，我们可以通过数据下拉列表指定文本长度或数据位数的范围，超过该范围的输入将无效。
⑧自定义：当选择自定义时，用户必须输入一个控制输入项有效的逻辑公式。
需要注意的是，即使数据有效性起作用，用户也可能输入无效的数据。
当用户对已经有数据的单元格或区域设置数据有效性时，并不影响之前已经输入的数据，之前输入的数据仍保存在单元格中。
同时，数据有效性规则不适用于公式计算的结果，如果某单元格或区域有公式，则该单元格和区域的数据性有效性设置无效。
使用VLOOKUP函数判断输入的城市数据有效性
问题描述：
在《城市2》表格里面输入城市名称时，如果与《城市1》不匹配，则会弹出一个提示。
处理方法：
①在《城市2》表格里面选择A2单元格，之后再点击【数据】，【数据有效性】，在“数据有效性”对话框里面的【设置】里面选择“自定义”，之后输入公式
=NOT(ISERROR(VLOOKUP($A2,城市1!$A$1:$A$5000,1,0)))，在“出错警告”里面输入所需的提示信息，如：不存在该城市；
②同理，选择B2、C2单元格，再按上面的操作步骤进行操作，之后分别输入公式=NOT(ISERROR(VLOOKUP($B2,城市1!$B$1:$B$5000,1,0))),
=NOT(ISERROR(VLOOKUP($C2,城市1!$C$1:$C$5000,1,0)))。
之后向下进行复制。
③当输入的信息与《城市1》不匹配时，将会弹出警告提示。
注：是天津市哦，否则就会有警告。
如何在Excel中设置数据有效性的复杂规则
如何在Excel中设置数据有效性的复杂规则Excel是一款功能强大的电子表格软件，它不仅可以帮助我们处理和分析数据，还可以通过设置数据有效性来限制用户输入的范围，以保证数据的准确性和一致性。
在Excel中，我们可以设置简单的数据有效性规则，如列表、整数或日期范围等。
然而，有时候我们需要设置更为复杂的数据有效性规则，以满足特定的业务需求。
本文将介绍如何在Excel中设置数据有效性的复杂规则。
1. 打开Excel并选择要设置数据有效性的单元格或单元格区域。
2. 点击Excel菜单栏中的“数据”选项卡，然后在“数据工具”组中点击“数据有效性”按钮，弹出数据有效性对话框。
3. 在数据有效性对话框的“设置”选项卡中，选择“自定义”选项。
4. 在“公式”输入框中，输入满足数据有效性规则的公式。
例如，如果要限制输入的范围在1到100之间，可以输入“=AND(A1>=1,A1<=100)”。
5. 在“输入提示”选项卡中，可以为用户提供输入时的提示信息。
输入提示可以是文本、单元格范围或固定的值。
在提示信息输入框中，输入相应的提示内容。
6. 在“错误警告”选项卡中，可以设置当用户输入不符合数据有效性规则时的错误提示。
可以选择显示一个警告或禁止输入。
7. 点击“确定”按钮，完成数据有效性设置。
通过以上步骤，我们可以简单地设置数据有效性规则。
然而，有时候我们需要更为复杂的规则来满足特定的要求。
下面将介绍一些常见的复杂规则设置方法。
1. 使用逻辑函数在设置数据有效性规则时，我们可以使用Excel的逻辑函数来实现复杂的条件判断。
例如，我们可以使用IF函数来根据某个条件判断是否允许输入数据。
例如，要求在输入数据时，某个单元格的值必须大于另一个单元格的值，可以设置公式为“=IF(A1>B1,TRUE,FALSE)”。
只有当A1的值大于B1时，才允许输入。
2. 使用自定义数据验证Excel提供了自定义数据验证功能，可以通过编写VBA宏代码来实现更为灵活的数据有效性规则。
excel中数据的有效性如何应用
excel中数据的有效性如何应用（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单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。
操作步骤：先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！）；选上"忽略空值"和"提供下拉菜单"两个复选框。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
• 很多WPS的小伙伴都会做二级、三级的下 拉菜单，甚至5级下拉菜单，但是你会做带 联想效果的下拉菜单不？！只要输入第一 个字，会把和这个字有关联的选项智能地 在下拉菜单中显示出来，然后再供选择。 这个时候，即使面对成千上万的数据的时 候，也就So Easy得多啦~~
• 只要把数据有效性+函数 结合使用就可以了~
• 1.在工作表“sheet1”做出对应的名字，
2.在工作表”sheet2”做出要输入 “姓名”的区域，例如本次分享的 sheet2，单元格A3到A10的区域
3.选中A3到A10的单元格，点击“数 据”--“有效性”
• 4.在“有效性条件”中选择“序列”，然后 输入公式： =OFFSET(Sheet1!$A$1,MATCH(LEFT($A3)&"*", Sheet1!$A:$A,0)1,,COUNTIF(Sheet1!$A:$A,LEFT($A3)&"*"))
6.设置好之后就可以做到“数据有效 性”也可以联想啦，