Excel常用电子表格公式大全【汇总篇】(组图)

Excel是每个人都常用的电子表格软件。掌握一些常用公式可以使我们更好地使用Excel电子表格。以下是学习编辑器编译的一些常见Excel电子表格公式，供您参考。

常见的Excel电子表格公式[摘要]

一、Excel基本公式

1、查找重复的内容公式：= IF（COUNTIF（A：A，A2)> 1，“重复”，“”）。

2、使用出生日期来计算年龄公式：= TRUNC（（DAYS360（H6，“ 2009/8/30”，FALSE））/ 360，0)。

3、根据输入的18位ID号计算出生年份和月份的公式：= CONCATENATE（MID（E2,7，4)，“ /”，MID（E2,11，2)，“ /”，MID（E2,13，2)）。

4、让系统从输入的ID号中自动提取性别，您可以输入以下公式：

= IF（LEN（C2) = 15，IF（MOD（MID（C2,15，1)，2)= 1，“ Male”，“ Female”）），IF（MOD（MID（ C2,17，1)，2)= 1，“ Male”，“ Female”））公式中的“ C2”代表输入ID编号的单元格。

1、总和：= SUM（K2：K56)-求和从K2到K56的面积；

2、 Average：= AVERAGE（K2：K56)-平均K2 K56的面积;

3、排名：= RANK（K2，K $ 2：K $ 56)-排名55名学生的成绩；

4、级别：= IF（K2> = 85，“ Excellent”，IF（K2> = 74，“ Good”，IF（K2> = 60，“ Pass”，“ Fail”）））

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：K6 0)）-找到从K2到K56的人数70〜84.5分；假设结果存储在单元格K61中；

（[6)= COUNTIF（K2：K56，“> = 60”）-SUM（K57：K6 1)）—找出从K2到K56的60〜69.5分的人数区域；假设结果存储在单元格K62中；

（7)= COUNTIF（K2：K56，“

注意：COUNTIF函数还可以计算特定区域中的男性和女性人数。

例如：= COUNTIF（C2：C351，“ male”）-查找C2至C351地区的男性人数（总共350人）；

9、优良率：= SUM（K57：K6 0) / 55 * 100

10、通过率：= SUM（K57：K62) / 55 * 100

1 1、标准差：= STDEV（K2：K56)-找出从K2到K56（55人）的表现波动（值越小，该班级学生之间的表现差异越小，相反，它表明该类是两极化的；

1 2、条件求和：= SUMIF（B2：B56，“男”，K2：K56)-假设B列存储了学生的性别，而K列存储了学生的分数，则此函数返回结果是班上男生成绩的总和；

1 3、多条件求和：{= SUM（IF（C3：C322 =“男”，IF（G3：G322 = 1,1，0)））}}-假设列C（C3： C322区域存储学生的性别，G列（G3：G322区域）存储学生的班级代码（1、 2、 3、 4、 5)，然后此函数返回的结果表示在第一类中查找男孩人数；这是一个数组函数。输入后，需要按Ctrl + Shift + Enter组合键（产生“ {……}”），无法输入“ {}”手动，并且只能通过组合键来生成。

1 4、根据出生日期自动计算年龄：= TRUNC（（DAYS360（D3，NOW（）））/ 360，0)

————假设D列存储了学生的出生日期，E列输入了产生学生年龄的函数。

1 5、 Word中的三个提示：

①连续输入三个“〜”以得到波浪线。

②输入三个连续的“-”以获得一条直线。

输入三个连续的“ =”以获取双直线。

在一、excel中，当一个单元格满足特定条件时，如何在另一个单元格中显示特定颜色，例如：

A1> 1，C1显示为红色

A1

方法如下：

在单元格1、中，单击单元格C1，单击“格式”>“条件格式”，将条件1设置为：

公式= A1 = 1

2、单击“格式”->“字体”->“颜色”，单击红色，然后单击“确定”。

条件2设置为：

公式= AND（A1> 0，A1

3、单击“格式”->“字体”->“颜色”，单击绿色，然后单击“确定”。

条件3设置为：

公式= A1

单击“格式”->“字体”->“颜色”，单击，然后单击“确定”。

4、在设置了三个条件之后，单击“确定”，它将出现。

二、如何在EXCEL中控制每列数据的长度并避免重复输入

1、定义具有数据有效性的数据长度。

使用鼠标选择要输入的数据范围，单击“数据”->“有效性”->“设置”，然后将“有效性条件”设置为“允许”，“文本长度”，“等于”更改为“ 5”（具体条件可以根据您的需要进行更改）。

您还可以定义一些提示消息，错误警告消息以及是否打开中文输入法等。定义后单击“确定”。

2、使用条件格式来避免重复。

选择A列，单击“格式”->“条件格式”，将条件设置为“公式= COUNTIF（$ A：$ A，$ A 1)> 1”，单击“格式”->“字体“->“颜色”，选择红色后单击两次“确定”。

以这种方式设置后，如果长度不正确，将提示您输入数据，如果重复数据，字体将变为红色。

三、如何识别EXCEL中B列与A列之间的区别？

（一)，如果需要比较两列A和B的同一行中的数据：

假设第一行是标题，请单击单元格A2，单击“格式”->“条件格式”，并将条件设置为：

“单元格值”“不等于” = B2

单击“格式”->“字体”->“颜色”，选择红色，然后单击两次“确定”。

使用格式刷向下复制单元格A2的条件格式。

可以根据此方法设置B列。

（二)，如果是A列和B列之间的整体比较（也就是说，同一数据不在同一行中）：

假设第一行是标题，请单击单元格A2，单击“格式”->“条件格式”，并将条件设置为：

“公式” = COUNTIF（$ B：$ B，$ A2) = 0

单击“格式”->“字体”->“颜色”，选择红色，然后单击两次“确定”。

使用格式刷向下复制单元格A2的条件格式。

可以根据此方法设置B列。

按照上述方法进行设置后，AB列中的数据将不会被着色，带有B列但没有B列或带有B列而没有A列的A列中的数据将标记为红色字体。

四、如何在EXCEL中按行批量排序

假设有大量的数据（值），并且每一行都需要从最大到最小排序，该怎么做？

因为按行排序和按列排序只能有一个主键，所以当主键相同时，可以按辅助键进行排序。因此，该问题不能通过分类解决。解决方法如下：

1、假设您的数据在A到E列中，请在单元格F1中输入公式：

= LARGE（$ A1：$ E1，COLUMN（A 1)）

使用填充手柄将公式向右和向下复制到相应的范围。

您的原始数据将按行降序出现在F到J列中。如有必要，请使用“粘贴特殊/值”将其复制到其他位置。

注意：可以根据您的实际情况（数据范围）修改步骤1中的公式。如果要从小到大排序，公式将更改为：= SMALL（$ A1：$ E1，COLUMN（A 1)）

五、巧妙地使用函数组合进行多条件计数统计

示例：第一行是标题，A列是“名称”，B列是“类”，C列是“语言得分”，D列是“入学结果”，现在我们需要计算“ class”如果为“ 2”，则“语言分数”大于或等于104，并且“录取结果”为“重”的人数。统计结果存储在此工作表的其他列中。

公式如下：

= SUM（IF（（（B2：B9999 =“二”）*（C2：C9999> = 10 4) *（D2：D9999 =“重本”），1，0)）

输入公式后，按Ctrl + Shift + Enter使其自动添加数组公式符号“ {}”。

六、如何判断单元格是否包含指定的文本？

假定判断单元格A1是否存在“指定文本”，可以使用以下任何公式：

= IF（COUNTIF（A1，“ *”＆“指定的文本”＆“ *”）= 1，“是”，“否”）

= IF（ISERROR（FIND（“ Specified text”，A1，1)），“ No”，“ Yes”）

查找特定区域中唯一数据的数量

例如，找到A1：A100范围内的非重复数据数，并且某个数重复多次，只有一次。有两种计算方法：

一种是使用数组公式：

= SUM（1 / COUNTIF（A1：A100，A1：A10 0)）

输入公式后，按Ctrl + Shift + Enter使其自动添加数组公式符号“ {}”。

第二种是使用乘积和函数：

= SUMPRODUCT（1 / COUNTIF（A1：A100，A1：A10 0)）

七、工作簿中有很多工作表，如何快速组织目录工作表

1、使用宏3.0检索每个工作表的名称，方法：

Ctrl + F3显示一个自定义名称对话框，将其命名为X，然后在“引用位置”框中输入：

= MID（GET.WORKBOOK（1)，FIND（“]”，GET.WORKBOOK（1)）+ 1,10 0)

好

2、使用HYPERLINK函数批量插入连接，方法：

在目录工作表（通常是第一张工作表）的单元格A2中输入公式：

= HYPERLINK（“＃'”＆INDEX（X，ROW（））＆“'！A1”，INDEX（X，ROW（）））

向下填充公式，直到发生错误并生成目录。

有关Excel电子表格公式的相关文章：

1.多个Excel表条件自动求和公式

2.Excel中18种最常用的公式计算技术的总结

3.Excel表计算公式教程

4.Excel幂公式教程（2种）

5.教您如何使用“ Excel表自动积分公式”

6.Excel表乘法函数公式

7.excel多表分类和汇总公式的用法

如何使用8.excel表格公式

9.如何在Excel中设置公式的自动计算和手动计算

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/shumachanpin/article-351765-1.html