超实用：用数据有效性+函数

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单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。

操作步骤：先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！）；选上"忽略空值"和"提供下拉菜单"两个复选框。

excel中数据的有效性的应用

例3：横跨两个工作表来制作下拉菜单
用INDIRECT函数实现跨工作表
在例2中，选择来源一步把输入=$Z$1:$Z$8换成=INDIRECT("表二!$Z$1:$Z$8"),就可实现横跨两个工作表来制作下拉菜单。
[应用二]自动实现输入法中英文转换
有时，我们在不同行或不同列之间要分别输入中文和英文。我们希望Excel能自动实现输入法在中英文间转换。
大兄弟：COUNTIF函数（计数求和）
COUNT函数，顾名思义是用来计数的，统计所选择区域的数值型单元格个数。COUNTIF是COUNT函数的引伸与拓展，在计数时加上先前条件，只有符合计数的条件才进行统计计算。比如，从员工信息表中，计算出有多少人的年龄大于35岁。
下面我们来看一个典型的分类计数汇总的例子。这里有一张销售流水记录表，每名销售人员累计做了多少“销售订单个数”呢？
操作步骤：先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！）；选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。
大兄弟COUNTIF正常工作需要两个参数——条件区域（本例为左侧表中“销售人员”一列）和计数条件（本例为右侧表中的人员姓名）。要计算第一位销售人员的“订单数”，很简单，输入函数公式 “=COUNTIF($C$2:$C$16,E2)”即可（见图1）。

excel数据有效性序列的使用

[应用一]下拉菜单输入的实现例1：直接自定义序列有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。

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

操作步骤：先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！）；选上"忽略空值"和"提供下拉菜单"两个复选框。

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

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

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

操作步骤：先在同一工作表内的打印区域外要定义序列填好(假设在在Z1：Z8），如“单亲家庭，残疾家庭，残疾学生，特困，低收人，突发事件，孤儿，军烈属”等，然后选择要实现效果的列（资助原因）；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域Z1：Z8（如果记得，可以直接输入=$Z$1:$Z$8；选上"忽略空值"和"提供下拉菜单"两个复选框。

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

数据有效性

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

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

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

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

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

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

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

excel 多重函数

excel 多重函数Excel是一款功能强大的电子表格软件，它提供了多重函数来处理和分析数据。

本文将介绍一些常用的Excel多重函数，并探讨它们的使用方法和应用场景。

一、SUM函数SUM函数是Excel中最基本的函数之一，它用于求取一组数值的和。

例如，我们可以使用SUM函数来计算一个销售团队的总销售额，或者计算某一列数据的总和。

二、AVERAGE函数AVERAGE函数用于计算一组数值的平均值。

它非常适用于需要对某一列数据进行统计分析的情况。

例如，我们可以使用AVERAGE 函数来计算某一列的平均销售额，或者计算某一群人的平均年龄。

三、COUNT函数COUNT函数用于计算某一列数据中的非空单元格数量。

它可以帮助我们统计数据的个数，从而更好地了解数据的分布情况。

例如，我们可以使用COUNT函数来计算某一列数据中的有效销售记录数量，或者计算某一群人中的男性人数。

四、MAX函数和MIN函数MAX函数用于求取一组数值中的最大值，而MIN函数用于求取一组数值中的最小值。

它们可以帮助我们找到数据中的极值，并对数据进行范围的分析。

例如，我们可以使用MAX函数和MIN函数来找到某一列数据中的最高销售额和最低销售额。

五、IF函数IF函数是一种条件函数，它用于根据某个条件来返回不同的结果。

IF函数在数据分析中非常常用，可以帮助我们根据特定条件对数据进行分类和筛选。

例如，我们可以使用IF函数来判断某一列数据中是否存在异常值，或者根据某一列数据的大小来判断销售业绩的好坏。

六、VLOOKUP函数VLOOKUP函数是一种查找函数，它用于在一个范围内查找某个值，并返回与之对应的值。

VLOOKUP函数在数据处理中非常有用，可以帮助我们根据某个关键字进行数据的匹配和查找。

例如，我们可以使用VLOOKUP函数来查找某个客户的联系信息，或者根据某个产品的编号查找对应的价格。

七、PivotTable透视表透视表是Excel中非常强大的数据分析工具，它可以帮助我们对大量的数据进行汇总和分析。

excel表格计算公式大全、使用技巧

excel表格计算公式大全、使用技巧Excel 表格计算公式大全、使用技巧在当今数字化办公的时代，Excel 表格无疑是我们处理数据和信息的得力工具。

熟练掌握 Excel 表格的计算公式和使用技巧，能够极大地提高工作效率，让数据分析和处理变得更加轻松准确。

接下来，就让我们一起深入了解 Excel 表格的计算公式大全以及实用的使用技巧。

一、常用计算公式1、求和公式 SUMSUM 函数用于计算一组数值的总和。

例如，要计算 A1 到 A10 单元格的数值总和，可以使用公式＝SUM(A1:A10)。

2、平均值公式 AVERAGEAVERAGE 函数用于计算一组数值的平均值。

比如，计算 B1 到B20 单元格的平均值，公式为＝AVERAGE(B1:B20)。

3、计数公式 COUNTCOUNT 函数用于计算包含数值的单元格数量。

假设要统计 C1 到C15 中包含数值的单元格个数，公式是＝COUNT(C1:C15)。

4、最大值公式 MAX 和最小值公式 MINMAX 函数用于找出一组数值中的最大值，MIN 函数则用于找出最小值。

比如，在 D1 到 D25 单元格中，求最大值用＝MAX(D1:D25)，求最小值用＝MIN(D1:D25)。

5、条件求和公式 SUMIF 和条件计数公式 COUNTIFSUMIF 函数可以根据指定的条件对数据进行求和。

例如，要计算 E 列中大于 50 的数值之和，公式为＝SUMIF(E:E,＂＞50"）。

COUNTIF 函数用于根据条件统计单元格数量，比如统计 F 列中等于“苹果”的单元格个数，公式是＝COUNTIF(F:F,＂苹果"）。

6、查找与引用公式 VLOOKUPVLOOKUP 函数用于在表格或区域中查找指定的值，并返回与之对应的其他列的值。

例如，在一个包含学生姓名、学号和成绩的表格中，根据学号查找对应的成绩，公式可以是＝VLOOKUP(查找学号, 查找区域, 成绩所在列数, 0)。

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 、分数段人数统计：（ 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，平时整理工资表、台账、出财务报表等等，都是必不可少的，那么，如何利用函数公式来提高工作效率尤为重要。

今天，小编吐血推荐几个常用的Excel函数公式，亲测好用，辛辛苦苦整理大半天，绝对值得收藏！先从入门级开始吧！【小白入门级】1.排序需要将某一列数据进行排序时，首先，点击左上角的“开始”——“排序”倒三角——“自定义排序”，如下图：然后会弹出两个选项的提示：（1）扩展选定区域把排序的结果扩展到表格中的其他列次，例如第一列的姓名等信息，会随同排序，这样排序之后信息更准确。

【例】按“税额”该列进行排序排序前：排序后：（2）以当前选定区域排序只排序选定的当列，其他列次不动。

排序前：排序后：推荐选择“扩展选定区域”，之后再选择排序的主要关键字，例如上图中选择按照“税额”排序，排序依据选择“数值”，次序根据需要选择“升序”或者“降序”。

2.筛选（1）按照标题筛选同时按住“Ctrl+A”全选，或者选中标题所在行次，点击左上角的“开始”——“数据”——“自动筛选”即可，会按照标题项目出现下拉框。

如下图：（2）筛选重复项同时按住“Ctrl+A”全选，或者选中需要筛选重复项的区域，点击左上角的“开始”——“条件格式”——“突出显示单元格规则”——“重复值”，如下图：弹出如下提示框，根据需要选择设置重复项突出显示的颜色即可。

3.冻结窗格财务日常工作中，用到的表格比较大，涉及的项目数据比较多时，下滑时表头就跟着滑过去了，不方便查看及核对数据，这时就可以用到“冻结窗格”的功能。

首先，把鼠标点到需冻结的行次或列次的相邻单元格，例如下图中需要冻结表头和序号列，只要用鼠标选中与“姓名”和序号“1”的相邻单元格“郭靖”。

然后点击左上角“开始”——“视图”——“冻结窗格”即可。

4.表间取数计算财务工作中还会经常遇到多个Sheet页中互相取数计算的情况，那么怎么才能快速的调用数据过来呢？首先，可以把鼠标点到需计算的单元格中，输入等号“=”，然后用鼠标点击左下角切换到需调用数据的Sheet页中，选中需调用的数据后敲“Enter”键即可。

Excel常用函数公式大全(实用)

Excel常用函数公式大全(实用)Excel常用函数公式大全1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。

=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这一区域进行求和；2、平均数：=AVERAGE()——对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地区（55名学生）的最高分；7、最低分：=MIN()——求K2到K56区域（55名学生）的最低分；8、分数段人数统计：（1）=COUNTIF(")——求K2到K56地区100分的人数；假定把结果存放于K57单位格；（2）=COUNTIF(,">=95")－K57——求K2到K56地区95～99.5分的人数；假定把结果存放于K58单位格；（3）=COUNTIF(,">=90")－SUM()——求K2到K56地区90～94.5分的人数；假定把结果存放于K59单位格；（4）=COUNTIF(,">=85")－SUM()——求K2到K56地区85～89.5分的人数；假定把结果存放于K60单位格；（5）=COUNTIF(,">=70")－SUM()——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；（6）=COUNTIF(,">=60")－SUM()——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；（7）=COUNTIF(,"<60")——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；说明：COUNTIF函数也可计算某一区域男、女生人数。

EXCEL中的数据有效性及其应用

EXCEL中的数据有效性及其应用在日常的数据处理过程中，Excel作为一种强大的工具，常常被广泛应用。

数据有效性是Excel中的一个重要功能，有助于确保输入数据的准确性和一致性。

通过设置数据有效性，用户能够限制单元格中的输入类型，从而减少错误，提高数据质量。

数据有效性功能概述首先需要明白，数据有效性是指对输入数据的限制和规范。

Excel允许用户为单元格设定特定的输入规则，这些规则可以是特定的数值范围、文本长度、下拉列表等。

通过这样的方法，用户可以有效控制用户的输入，避免因为输入错误而导致的数据处理问题。

其中，数据有效性功能的设置过程相当简单。

用户只需选中需要设置的单元格，切换到“数据”选项卡，找到“数据有效性”即可开始配置。

弹出的对话框中提供多种选项，用户可以根据实际需求选择。

数据有效性的应用场景数据有效性在多个场景中都展现出其重要性。

比如在多个工作人员共同使用同一个Excel文件时，数据有效性能够有效避免因个体差异而造成的输入错误。

设想一下，如果多名员工在填写客户信息表时，某个员工输入了“男性”或“女”，另一个则填写了“男”或“女”，这种不一致无疑会给数据分析带来麻烦。

通过使用数据有效性，用户可以设定明确的下拉列表，规定输入内容的唯一性，有效避免误差。

在财务数据录入中，数据有效性同样发挥着至关重要的作用。

财务报表中通常需要输入很多数字，而若有一个财务人员误将文本输入到数值型单元格，那么后续的数据分析可能会遭受很大影响。

设置数据有效性可以确保输入的数据类型符合要求。

对于那些需要输入特定日期的信息场合，合适的日期有效性设置也是不可或缺的。

用户可以选择一个日期范围，确保输入的日期都在合理的时间段内，比如不能输入未来的日期或不合规的历史日期。

如何配置数据有效性配置数据有效性并不复杂，下面简要介绍几个步骤：选定单元格：选择希望进行数据有效性设置的单元格或区域。

打开数据有效性功能：在“数据”选项卡中找到“数据有效性”。