Excel条件格式公式应用大全·优选.
(完整版)Excel表格公式大全(苦心整理)
Excel表格公式大全来源：鲍利的日志1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式：=CONCA TENA TE(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中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如：A1〉1时，C1显示红色0<A1<1时，C1显示绿色A1<0时，C1显示黄色方法如下：1、单元击C1单元格，点“格式”>“条件格式”，条件1设为：公式 =A1=12、点“格式”->“字体”->“颜色”，点击红色后点“确定”。
条件2设为：公式 =AND(A1>0,A1<1)3、点“格式”->“字体”->“颜色”，点击绿色后点“确定”。
条件3设为：公式 =A1<0点“格式”->“字体”->“颜色”，点击黄色后点“确定”。
4、三个条件设定好后，点“确定”即出。
二、EXCEL中如何控制每列数据的长度并避免重复录入1、用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围，点"数据"->"有效性"->"设置"，"有效性条件"设成"允许""文本长度""等于""5"（具体条件可根据你的需要改变）。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点"确定"。
2、用条件格式避免重复。
选定A列，点"格式"->"条件格式"，将条件设成“公式=COUNTIF($A:$A,$A1)>1”，点"格式"->"字体"->"颜色"，选定红色后点两次"确定"。
这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。
Excel条件格式公式应用大全
Excel条件格式公式应用我们知道，Excel“条件格式”功能可以根据单元格内容有选择地自动应用格式，它为Excel增色不少的同时，还为我们带来很多方便。
如果让“条件格式”和公式结合使用，则可以发挥更大的威力，下面提供几个在“条件格式”中使用公式的应用实例，希望能给读者朋友带来一些启发。
一、判别输入是否正确在输入如身份证等有固定位数的号码，出现位数不正确的情形时，我们希望Excel能够给出提示。
虽然可以使用“数据有效性”设置实现，但是当输入出错时，Excel 总会弹出一个提示的对话框，有朋友可能觉得这样“唐突”的提醒有点影响心情，那就让“条件格式”来“温和”的提醒吧。
1、创建“条件格式”的公式假设我们通过“条件格式”，把符合位数(15位或18位)的号码所在单元格的填充色设置为绿色，输入完成后，通过查看单元格的填充色是否变为绿色，就可以知道输入的正确性了。
由于身份证号码数据是属于“文本”类型的，先选中需要存放身份证号码的A2:A52单元格区域，将它们的数字格式设置为“文本”。
然后在A2:A52单元格区域处于被选中的状态下，选择菜单“格式→条件格式”命令，打开“条件格式”对话框，单击“条件1”下方的下拉箭头，在弹出的下拉列表中选择“公式”(图1)。
接着在其右边的文本框中输入公式“=OR(LEN(A2)=15,LEN(A2)=18)”，然后单击“格式”按钮，在打开的“单元格格式”对话框中选择“图案”选项卡，选择绿色作为符合条件的单元格的填充色(图2)。
设置好后单击“确定”按钮，返回“条件格式”对话框，检查无误再次单击“确定”就完成了条件格式的设置(图3)。
小提示：上面的操作，先选中了一个单元格范围A2:A52，然后为这个单元格范围设置条件格式的公式。
在这种情况下，公式中应使用选择范围中左上单元格的引用，此例中为A2。
公式输入完成后，可以查看一下这个范围中的其它单元格的条件格式公式，如A8单元格，为“=OR(LEN(A8)=15,LEN(A8)=18)”，这是由于上面的引用为相对应用，它会根据单元格的实际偏移量自动改变，从而得到适合其它单元格的公式。
条件格式规则使用公式 excel
条件格式规则使用公式excel全文共四篇示例，供读者参考第一篇示例：Excel中的条件格式是一种非常强大的工具，可以帮助用户根据一定的条件来为数据进行自动化的着色、标记等操作。
在使用条件格式时，我们可以通过设置规则来制定条件并应用格式，使得数据在满足条件时自动发生变化。
除了可以使用Excel自带的一些条件格式规则外，也可以通过公式来自定义条件格式规则，从而实现更加灵活的数据处理需求。
在Excel中使用公式来创建条件格式规则有一定的复杂性，但也能够实现更加灵活的定制化效果。
以下将详细介绍如何通过公式来创建条件格式规则，并为读者提供一些常用的公式示例。
我们需要了解一些基本概念。
在条件格式中，我们可以设置“格式所有单元格”、“仅格式化单元格中的最高/最低数值”等选项，来决定条件格式规则的适用范围。
条件格式规则也可以由多个条件组合而成，例如AND、OR逻辑运算符可以帮助我们实现更复杂的规则。
接下来，我们将介绍一些常见的公式示例，帮助读者更好地理解如何使用公式来创建条件格式规则。
1. 根据数值范围设置背景色：假设我们有一个数据表，其中包含一列数字数据，我们希望在这列数字数据中，大于90的单元格背景色为绿色，小于60的单元格背景色为红色。
我们可以按照以下步骤设置条件格式规则：选中数据范围-> 在“开始”选项卡中点击“条件格式” -> 选择“新规则” -> 选择“使用公式确定要格式化的单元格” -> 编写以下公式：=AND(A1>90,A1<>"") //为大于90的数据设置绿色背景=AND(A1<60,A1<>"") //为小于60的数据设置红色背景点击确定即可应用条件格式规则。
=A1>B1通过以上两个示例，我们可以看到，通过使用公式来创建条件格式规则，我们可以轻松实现更加复杂的数据处理需求，并且可以实现更灵活的规则定制。
500个excel函数公式用法示例,500个函数公式模板,教你系统掌握
Excel函数公式1. Excel函数公式用法示例：（1）SUM函数：SUM(A1:A5)，用于求某一范围内的值的总和。
（2）AVERAGE函数：AVERAGE(A1:A5)，用于求某一范围内的值的平均值。
（3）IF函数：IF(A1>B1,“A1大于B1”,“A1小于等于B1”)，用于判断某一条件是否满足，并执行不同的动作。
（4） CONCATENATE函数：CONCATENATE(A1,B1,C1)，用于将多个单元格的内容拼接起来，形成一个新的文本。
（5） COUNTIF函数：COUNTIF(A1:A5,“>=20”)，用于统计某一范围内满足某一条件的单元格数量。
2. 500个函数公式模板：（1）VLOOKUP函数：VLOOKUP(A1,B1:C5,2,FALSE)，根据某一范围内的值，在另一范围内查找特定的值。
（2）INDEX函数：INDEX(A1:C5,2,2)，用于返回某一范围内指定行和列的值。
（3）MATCH函数：MATCH(A1,B1:C5,0)，用于查找某一值在某一范围内的位置。
（4）VLOOKUP函数：VLOOKUP(A1,B1:C5,2,TRUE)，根据某一范围内的值，在另一范围内查找最接近的值。
（5）LEN函数：LEN(A1)，用于返回某一单元格中字符串的长度。
（6）COUNT函数：COUNT(A1:A5)，用于统计某一范围内的非空单元格的数量。
（7）MAX函数：MAX(A1:A5)，用于求某一范围内的最大值。
（8）MIN函数：MIN(A1:A5)，用于求某一范围内的最小值。
（9）LEFT函数：LEFT(A1,5)，用于返回某一字符串中指定位置开始的几位字符。
（10）RIGHT函数：RIGHT(A1,5)，用于返回某一字符串中指定位置结束的几位字符。
（完整版）Excel表格公式大全（苦心整理）
（完整版）Excel表格公式大全（苦心整理）Excel表格公式大全来源：鲍利的日志1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式：=CONCA TENA TE(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表格中公式大全1
Excel常用电子表格公式大全1、查找重复内容：（格式—条件格式）公式=IF(COUNTIF($D$2:D2,D2)>1,"重复","不重复")或=IF(COUNTIF($D$2:D2,D2)>1,"重复","")=COUNTIF($D$2:$D$500,$D2)>=22、数据--筛选--高级筛选--输入数据区域--勾选"选择不重复记录"--确定3、=COUNTIF(D$2:D2,D2)>1这个是提示一列中第二个重复。
EXCEL一列内如何区分出重复的文字问：EXCEL表的客户名录,如果区分是否重复答：在客户名录后面一列（假设为B列）输入=if(countif($B$1:B1,B1)>1,"重复","不重复")4、EXCEL条件格式教程:用条件格式辨别重复数值在excel2003中，我们会发现辨别一个范围中的重复数值是非常有帮助的。
可以使用下面的条件格式公式。
在该例子中，格式应用于范围A1:G2中所有非惟一单元格中=COUNTIF($A$1:$G$12,A1)>1在一个范围中只对非重复数值应用格式，使用下面的公式=COUNTIF($A$1:$G$12,A1)=1实例：将A1:G2中的重复值添加特殊的格式。
1.选择要设置交替行阴影的单元格区域A1:G2，点击菜单栏的“格式”——“条件格式”，将弹出“条件格式”对话框。
2.在“条件格式”对话框中的“条件1”下拉列表框中选择“公式”，然后在公式输入框中输入公式“=COUNTIF($A$1:$G$12,A1)>1”，并设置其格式为蓝色加粗。
然后添加“条件2”公式为“=COUNTIF($A$1:$G$12,A1)=1”，并设置其格式为红色加粗。
3.点击“确定”按钮关闭“条件格式”对话框。
条件格式规则使用公式 excel
条件格式规则使用公式 excel
在Excel中，条件格式规则可以使用公式来创建更加灵活和个性化的格式化效果。
使用公式可以根据特定的条件来对单元格进行格式化，使得单元格能够根据公式的结果自动变化样式。
要使用公式创建条件格式规则，首先选中你想要应用条件格式的单元格范围，然后点击“开始”选项卡中的“条件格式”按钮。
在弹出的菜单中选择“新建规则”，然后选择“使用公式确定要格式化的单元格”。
接下来，在弹出的对话框中，你可以输入你想要应用的条件格式规则的公式。
例如，你可以使用IF函数来根据特定的条件来设置格式，比如=IF(A1>10, TRUE, FALSE)。
这个公式的意思是，如果A1的值大于10，则返回TRUE，否则返回FALSE。
在设置公式时，你可以使用单元格引用、数学运算符、逻辑函数等来创建你想要的条件格式规则。
例如，你可以使用AND、OR、NOT等函数来组合多个条件，以实现更加复杂的格式化效果。
在输入完公式后，你可以点击“格式”按钮来设置符合条件时
的格式样式，比如背景色、字体颜色、边框样式等。
设置完格式后，点击“确定”按钮即可完成条件格式规则的创建。
总的来说，使用公式创建条件格式规则可以帮助你根据特定的
条件来对单元格进行灵活的格式化，使得数据在呈现时更加直观和
易于理解。
希望这些信息能够帮助到你。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Excel条件格式公式应用
我们知道，Excel“条件格式”功能可以根据单元格内容有选择地自动应用格式，它为Excel增色不少的同时，还为我们带来很多方便。
如果让“条件格式”和公式结合使用，则可以发挥更大的威力，下面提供几个在“条件格式”中使用公式的应用实例，希望能给读者朋友带来一些启发。
一、判别输入是否正确
在输入如身份证等有固定位数的号码，出现位数不正确的情形时，我们希望Excel能够给出提示。
虽然可以使用“数据有效性”设置实现，但是当输入出错时，Excel 总会弹出一个提示的对话框，有朋友可能觉得这样
“唐突”的提醒有点影响心情，那就让“条件格式”来“温和”的提醒吧。
1、创建“条件格式”的公式
假设我们通过“条件格式”，把符合位数(15位或18位)的号码所在单元格的填充色设置为绿色，输入完成后，通过查看单元格的填充色是否变为绿色，就可以知道输入的正确性了。
由于身份证号码数据是属于“文本”类型的，先选中需要存放身份证号码的
A2:A52单元格区域，将它们的数字格式设置为“文本”。
然后在A2:A52单元格区域处于被选中的状态下，选择菜单“格式→条件格式”命令，打开“条件格式”对话框，单击“条件1”下方的下拉箭头，在弹出的下拉列表中选择“公式”(图1)。
接着在其右边的文本框中输入公式“=OR(LEN(A2)=15,LEN(A2)=18)”，然后单击“格式”按钮，在打开的“单元格格式”对话框中选择“图案”选项卡，选择绿色作为符合条件的单元格的填充色(图2)。
设置好后单击“确定”按钮，返回“条件格式”对话框，检查无误再次单击“确定”
就完成了条件格式的设置(图3)。
小提示：上面的操作，先选中了一个单元格范围A2:A52，然后为这个单元格范围设置条件格式的公式。
在这种情况下，公式中应使用选择范围中左上单元格的引用，此例中为A2。
公式输入完成后，可以查看一下这个范围中的其它单元格的条件格式公式，如A8单元格，为“=OR(LEN(A8)=15,LEN(A8)=18)”，这是由于上面的引用为相对应用，它会根据单元格的实际偏移量自动改变，从而得到适合其它单元格的公式。
2、实现的具体效果
现在来测试一下上面设置可以实现的效果，在A2：A52区域的单元格中输入一些身份证号码，当位数是18位或15位时，所在单元格的填充色自动变为“绿色”，而位数不对的身份证号码，所在单元格的填充色不发生任何改变(图 4)，从是否变色我们就可以判断输入的正确性了。
全部输入并确认正确后，如果需要删除单元格条件格式，则先选中A2:A52单元格区域，然后打开“条件格式”对话框，单击如图3中的“删除”按钮，在打开的“删除条件格式”对话框中勾选“条件1”复选框，单击确定即可(图 5)。
二、找出销售额的前三名
如图6中的B2:B12单元格中存放着销售额数据，要找出其中的前三名，让它们以蓝色字体显示。
先选中B2:B12单元格，打开如图1所示的对话框，输入公式
“=B2>LARGE($B$2:$B$12,4)”然后将符合条件的字体格式设置为蓝色即可。
实现的具体效果如下图(图 6)。
说明：虽然可以对“销售额”数据列排序找出前三名，但是，可能我们希望以日期为顺序排列，这时“条件格式”就可以做到“两全其美”了。
三、让符合特殊条件的日期突出显示
有时，我们可能希望符合特殊条件的日期所在的单元格突出显示，比如星期六或星期天。
这时我们可以先选中日期所在的单元格，如图6中的A2:A12，然后打开如图1所示的单元格，输入公式“=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)”，然后设置符合条件的单元格填充色为阴影即可。
小提示：函数WEEKDAY(serial_number,return_type)的功能为返回某日期为星期几，Serial_number??表示一个顺序的序列号，代表要查找的那一天的日期。
当参数return_type 为2时，函数返回数字 1（星期一）到数字 7（星期日）之间的整数。
四、让工作表间隔固定行显示阴影
当单元格数据行较多，我们为了让显示效果更加醒目，可以让工作表间隔固定行显示阴影，效果如下图所示(图 7)。
上面的效果是使用了公式“=MOD(ROW(),2)=0”，如果要间隔两行显示阴影则用公式“=MOD(ROW(),3)=0”，其余依次类推。
小提示：函数MOD(number,divisor)返回两数相除的余数，其中Number为被除数，Divisor为除数。
函数ROW(reference)返回引用的行号。
其中Reference??为需要得到其行号的单元格或单元格区域，如果省略 reference，则假定是对函数 ROW 所在单元格的引用。
“条件格式”与公式结合还有非常多的应用，感兴趣的朋友不妨再研究研究哦。
最新文件---------------- 仅供参考--------------------已改成word文本--------------------- 方便更
改。