Excel常用技巧(三)——函数

最新推荐文章于 2023-10-19 19:54:28 发布
最新推荐文章于 2023-10-19 19:54:28 发布
阅读量918 收藏 2
点赞数 1
文章标签： 数据分析 excel
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ytraister/article/details/108574562
版权

Excel常用函数

》本文转载自：ExcelHome，http://www.excelhome.net/lesson/article/excel/1897.html
》并对以上链接进行稍做整理

1、IF函数条件判断

IF函数是最常用的判断类函数之一，能完成非此即彼的判断。
如下图，考核得分的标准为9分，要判断B列的考核成绩是否合格。
编写：=IF(B4>=9,"合格","不合格")
在这里插入图片描述
IF，相当于普通话的“如果”，常规用法是：

  • =IF(判断的条件,符合条件时的结果,不符合条件时的结果)

2、多条件判断

如下图所示，如果部门为生产、岗位为主操 有高温补助。在D列使用公式：
编写：=IF(AND(B2="生产",C2="主操"),"有","无")
在这里插入图片描述
AND函数对两个条件判断，如果同时符合，IF函数返回“有”，否则为无。

3、条件求和

如下图所示，使用SUMIF函数计算一班的总成绩：
编写：=SUMIF(D2:D5,F2,C2:C5)
在这里插入图片描述
SUMIF用法是：

  • =SUMIF(条件区域,指定的求和条件,求和的区域)

用通俗的话描述就是：
如果D2:D5区域的班级等于F2单元格的“一班”，就对C2:C5单元格对应的区域求和。

4、多条件求和

如下图所示，要统计部门为生产,并且岗位为主操的补助总额。
编写：=SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2)
在这里插入图片描述
SUMIFS用法是：

  • =SUMIFS(求和的区域，条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……)

5、条件计数

如下图，要统计指定店铺的业务笔数。也就是统计B列中有多少个指定的店铺名称。
编写：=COUNTIF(B2:B12,E3)
在这里插入图片描述
COUNTIF函数统计条件区域中，符合指定条件的单元格个数。常规用法为：

  • =COUNTIF(条件区域,指定条件)

6、多条件计数

要求：统计统计部门为生产,并且岗位为主操的人数
编写：=COUNTIFS(B2:B9,F2,C2:C9,G2)
在这里插入图片描述
COUNTIFS函数统计条件区域中，符合多个指定条件的单元格个数。常规用法为：

  • =COUNTIFS(条件区域1,指定条件1,条件区域2,指定条件2……)

7、条件查找

VLOOKUP函数一直是大众情人般的存在，函数的语法为：

  • =VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)

如下图，要查询F3单元格中的员工姓名是什么职务。
编写：=VLOOKUP(F3,B2:D6,2,0)
在这里插入图片描述
使用该函数时，需要注意以下几点：

  1. 第1个参数，选择需要查找的值【注：查找值必须位于查询区域(紫色部分)中的第一列】
  2. 第2个参数，选择需要查找的区域【注：查找值必须位于查询区域(紫色部分)中的第一列】
  3. 第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列（即：部门那一列(第2列)）
  4. 第4参数一般用0（或FASLE）以 精确匹配 方式进行查找
  5. 如果查找值与数据区域关键字的数据类型不一致，会返回错误值 “#N/A”

8、多条件查找

如下图所示，要求查询部门为生产,并且岗位为部长的姓名。
编写：=LOOKUP(1,0/((B2:B9=F2)*(C2:C9=G2)),A2:A9)
在这里插入图片描述
LOOKUP函数多条件查询写法为：

  • =LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)
  • 函数中的1和0是固定的
  • 万能套用公式：=lookup(1,0/(查找值=查找列),结果列)

9、合并多个单元格内容

要连接合并多个单元格中的内容，可以使用&符号完成。如下图，要合并A列的姓名和B列的电话号码，可以编写：=A2&B$1&B2
在这里插入图片描述

10、合并带格式的单元格内容

合并带有格式的内容时，Excel默认按常规格式进行合并，但是如果是日期、时间或是其他有格式的数值，结果就会让人大失所望了：
在这里插入图片描述
如何才能正确连接出需要的字符串呢？其实很简单，C2公式为：=A2&TEXT(B2,"y年m月d日")
在这里插入图片描述
首先使用TEXT函数，把B列的日期变成具有特定样式的字符串，然后再与A列的姓名连接，就变成了最终需要的样式。

11、文本替换——替换部分电话号码

如下图所示，要将手机号码的中间四位换成星号。编写：=SUBSTITUTE(B2,MID(B2,4,4),"****",1)
在这里插入图片描述
SUBSTITUTE函数的用法是：

  • SUBSTITUTE(要替换的区域,旧文本,新文本,[替换第几个])
  1. 先使用MID函数取得B列号码中的中间4位；
  2. 再用 “ ***** ” 替换掉这部分内容；
  3. 最后一个参数使用1，表示只替换第一次出现的内容。

比如第九行的电话号码是13801010101，最后四位和中间四位相同，如果不指定1，就会全部替换掉了。

12、文本提取

》有三个方法：LEFT()、RIGHT()、MID()

① LEFT函数：
LEFT英文翻译成中文是“左边”的意思，顾名思义是用来提取字符串左边的几个字符。
LEFT函数有两个参数，其构造是：=LEFT（想要提取的字符串，返回左边的几个字符）

  • 如下图操作：（以身份证为例）
    在这里插入图片描述

② RIGHT函数：
RIGHT英文翻译成中文是“右边”的意思，顾名思义是用来提取字符串右边的几个字符。
RIGHT跟LEFT一样只有两个参数，其构造是：=RIGHT（想要提取的字符串，返回右边的几个字符）

  • 如下图操作：（以身份证为例）
    在这里插入图片描述

③ MID函数：
MID英文翻译成中文是“中间的”的意思，顾名思义是用来提取字符串中间的几个字符。
MID函数有三个参数，其构造是：=MID（想要提取的字符串，从字符串的第几位开始，向右返回几个字符）

13、提取混合内容中的姓名

如下图，要从A列姓名电话中提取出姓名，除了使用高版本的自动填充功能，还可以使用公式完成：=LEFT(A2,LENB(A2)-LEN(A2))
在这里插入图片描述
LENB函数将每个汉字（双字节字符）的字符数按2计数，LEN函数则对所有的字符都按1计数。因此“LENB(A2)-LEN(A2)” 返回的结果就是文本字符串中的汉字个数。
LEFT函数从文本字符串的第一个字符开始，返回指定个数的字符，最终提取出员工姓名。【RIGHT函数则相反，MID函数则从中间取】

14、根据身份证号码提取出生年月

》若出现身份证号码为科学计数法格式，那么就先删除，再将该列设置单元格格式为：文本，即可。
编写：=1*TEXT(MID(B2,7,8),"0-00-00")
在这里插入图片描述
①首先使用MID函数从B2单元格的第7位开始，提取出表示出生年月的8个字符，结果为：
“19780215”
②再使用TEXT函数将字符串转换为日期样式：
“1978-02-15”
③然后通过*1计算，将其转换为真正的日期。最后设置为日期格式即可。

15、数据查找 / 数据验证

以下图数据为例，需要从J列中，查找是否存在 “数据分析”的字段
在这里插入图片描述
》该案例用到了 FIND()函数、ISNUMBER()函数

FIND()函数的用法：

  • FIND( 要查找的文本，文本所在的单元格，从第几个字符开始查找[可选，省略默认为1，从第一个开始查找])
  • 它返回的是数值格式，即字段在查找区域的哪个位置

ISNUMBER()函数的用法：

  • ISNUMBER( VALUE )
  • 判断引用的参数或指定单元格中的值是否为数字，正确返回TRUE，否则返回FALSE

案例讲解：
》若FIND函数返回数值，则ISNUMBER函数将该数值转换为TRUE，表示在查找区域中找到该字段
》若FIND函数返回不是数值，则ISNUMBER函数将该值转换为FALSE，表示在查找区域中未找到该字段
》通常，FIND函数和ISNUMBER函数结合使用，准确判断字段是否存在

16、屏蔽函数公式返回的错误值

在使用函数公式过程中，经常会返回一些诸如 #N/A、#NAME? 之类的错误值，要屏蔽这些错误值其实很简单，只需在原公式外侧加上一个IFERROR函数就好。
IFERROR函数的用法为：

  • =IFERROR(原公式,出现错误时要返回的内容)

如果公式正确，就返回原有计算结果，如果公式返回的是错误值，就返回用户指定的显示内容。

17、四舍五入函数

ROUND函数这个想必大家经常用到吧，就是对数值按指定的位数四舍五入。比如：=ROUND(8/9,3)
就是将8/9的计算结果四舍五入到三位小数，结果为0.889

18、提取日期时间中的日期值

要从日期时间数据中提取出日期，可以使用以下公式：=INT(A2)
要继续提取时间，只需要做个减法，就ok了：
在这里插入图片描述

19、生成随机数

RANDBETWEEN能够在指定范围内生成一组随机数据
函数的用法是：

  • =RANDBETWEEN(数字下限,数字上限)

比如以下公式，就是生成60到100之间的随机数：=RANDBETWEEN(60,100)

20、带单位的数值相加

如下图所示，计算 100g+2000g+300g 的值：
在这里插入图片描述
用left()函数结合len()函数，获取到数值结果，接着用sum(--)函数对结果进行求和计算，再到sum()后面加上：&"g" 给定单位，最后按 ‘ctrl+shift+enter’，求得最终结果。

21、计算文本算式

如下图，要计算单元格中的文本算式，先单击第一个要输入公式的单元格
定义名称 ：计算
引用位置：=EVALUATE(C2)
然后在单元格中输入公式：=计算
在这里插入图片描述

22、比较大小写的单词是否相同

如果在A1和A2单元格中分别输入大小写的单词，使用以下公式判断时，Excel会默认二者是相同的：=A2=B2
在这里插入图片描述
如需区别大小写，可以使用公式：=EXACT(A2,B2)
EXACT()函数 区分大小写，但忽略格式上的差异

23、$符号的运用

简单讲解：

》$表示静态引用（也叫绝对引用）的意思。即固定引用的单元格的行或列。键盘上按F4可以调出该符号

》一个单元格地址由两部分组成：一个是字母（代表列数），一个是数字（代表行数）。

  • 字母横着拉会变，竖向拉，不管前面加不加$都不会变。
  • 同样，数字竖向拉会变，横着拉不管加不加$都不会变。

》相对引用和绝对引用的精髓就是：$在谁前面，谁不动。

以乘法表为例，如下二图所示：
在这里插入图片描述在这里插入图片描述
》如C2单元格，设置为：=C$1*$A2。在A2前加$符号，那么往右延长，该A2固定不变。即行值不变。
》如B3单元格，设置为：=B$1*$A3。在B1中间加$符号，那么往下延长，该B1固定不变。即列值不变。

ytraister
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
c++如何将两个if函数合并_这几个牛A函数，你用过吗？
weixin_34081203的博客
12-12 1519
介绍一下今天的几位主角：1、IFS函数2、MAXIFS函数3、MINIFS函数4、CONCAT函数5、TEXTJOIN函数一、IFS函数IFS 函数检查是否满足一个或多个条件，且是否返回与第一个 TRUE 条件对应的值。IFS 可以轻松取代复杂的多层嵌套 IF 语句。用一句话说清楚它的语法：=IFS(条件1判断语句，条件1结果，条件2判断语句，条件2结果，条件3判断语句，条件3结果，......)...
C++ if嵌套语句
wjbl39hy的博客
04-07 803
嵌套的基本格式 嵌套if语句有2种形式： 嵌套在else分支中，格式如下： if (表达式1) 语句1; else if (表达式2) 语句2;     else if …         else语句n;嵌套在if分支中，格式如下：     if (表达式1){         if (表达式2) 语句1;         else 语句2 ;     }     el
C++ 入门 - 4 - 判断语句 —— if语句
最新发布
litingCSDN1的博客
10-19 5638
在 C++ 中程序也可以进行判断, 比如判断 N / 2 是否等于 5.又比如判断输入的是否是 123.好了, 我们的判断语句怎么写呢, 让我们看一看~
C++语言之 if 语句
一个程序猿~
05-13 4638
C++有三种选择结构：if、if-else、switch。本文会介绍 if 和 if-else。if 语句是用来判定所给定的条件是否满足，根据判定的结果（真或假）决定执行给出的两种操作之一。
内联函数online的定义和作用
qq_44667165的博客
09-26 3525
说明： 内联函数是一个小型函数，以牺牲空间来节省函数调用带来的额外开销，一般用以比较小函数或者使用频率很高的函数，函数内部内部没有复杂的语句，如开关语句switch，循环语句for等 定义： （1）在 C++ 中，可以在定义函数时，在返回值类型前面加上 inline 关键字 inline int max() { ...... return 0; } （二）直接在类的内部进行函数的声明和定义,则默认其是内联函数 class A { public: int max() { ...... ret
十条EXCEL使用技巧.pdf
11-28
具体方法是：在编辑栏中输入一个等号，其后接函数名，然后按Ctr+A键，Excel则自动进入“函数指南——步骤2之2”。 8. 如何把选定的一个或多个单元格拖放至新的位置？ 按住Shift键可以快速修改单元格内容的次序。...
Excel使用技巧大全
04-27
Excel 使用技巧集锦——163种技巧 目 录 一、 基本方法 7 1. 快速选中全部工作表 7 2. 快速启动EXCEL 7 3. 快速删除选定区域数据 7 4. 给单元格重新命名 7 5. 在EXCEL中选择整个单元格范围 7 6. 快速移动/复制单元格...
Excel技巧大全
05-06
Excel 使用技巧集锦——163种技巧 目 录 一、 基本方法 7 1. 快速选中全部工作表 7 2. 快速启动Excel 7 3. 快速删除选定区域数据 7 4. 给单元格重新命名 7 5. 在Excel中选择整个单元格范围 7 6. 快速移动/复制...
EXCEL函数公式大全.pdf
05-15
本资源摘要信息旨在总结 EXCEL 中的常用函数公式和技巧，帮助用户更好地掌握 EXCEL 的功能和应用。 1. 查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。该公式可以用来查找重复的内容，例如查找重复的学号、...
未定义与 struct 类型的输入参数相对应的函数 fetch_Excel办公实操，认识函数和公式，就是那么简单...
weixin_39849762的博客
11-27 1093
什么是公式在Excel中，公式是以"="号为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。公式通常存放于单元格内，通过运算返回的值对所在单元格进行赋值。 Excel的公式是为了有目的地计算结果，只能在单元格或内存中返回值，而不能对单元格执行删除或对其他单元格进行赋值等动作。公式的组成要素为等号"="、运算符和常量、单元格引用、函数和名称(包括表工具中的字段)等，如图所示。各要素的详细含...
C++ 嵌套 if 语句
学习日常分享
09-18 623
在 C++ 中，嵌套 if-else 语句是合法的，这意味着您可以在一个 if 或 else if 语句内使用另一个 if 或 else if 语句。嵌套 if 语句是一种 if 语句的变体，其中一个 if 语句可以在另一个 if 语句中嵌套。嵌套 if 语句可以帮助您更精确地测试多个条件。
if多个条件判断时要想清楚
Chz_1的博客
07-01 3019
#include <stdio.h> #include <stdlib.h> int main() { int i = 0; for (i = 0; i < 5; i++) { if (i != 1 || i != 2) { printf("[1] i = %d\n", i);//全部打印 } } printf("=====...
6-4 使用函数统计指定数字的个数_计算机二级Office考试，Excel函数你知多少？
weixin_34294809的博客
12-14 1051
已经报名全国计算机等级考试的小伙伴是不是正在紧张的备考中呢，对于二级Office的操作题知识你掌握了多少呢？对于新手来说，按难度来排名，应该就属Excel了吧，最大难点就是函数公式了，所以小编整理了关于Excel函数的信息分享给大家，转需哦！1.函数名：SUM()功能：该函数返回某一单元格区域中的所有数字之和格式：SUM(SUM (number 1, number2...)其中，nu...
MATLAB遇到问题：错误使用eval，未定义与‘struct‘类型的输入参数相对应的函数‘workspacefunc‘
热门推荐
Wendy的博客
04-03 1万+
以前MATLAB一直运行的运行的好好地，在我写一个新项目时，突然出现错误： 错误使用eval 未定义与’struct’类型的输入参数相对应的函数’workspacefunc’ 然后命令行窗口出现一系列错误： 网上一搜索，一种常见的解决方法是： 一、在matlab的command里输入: restoredefaultpath;matlabrc. 回车； 二、建一个名为“pathdef.m”的文件复制到在matlab的安装目录/toolbox/local下； 三、在matlab的command里输入“sav
cocos 时间函数需要什么引用_cocos2dx 与android函数的互相调用及使用jni
weixin_39956182的博客
12-21 101
cocos2dx 与android函数的相互调用及使用jni哈喽大家好 ，之前一阵子一直在忙项目，没怎么发过博文，挺想看看cocos2dx与安卓是如何交互的，所以就学习了一下(因为我是小菜嘛 想多学点东西 嘻嘻！！！)做了这么一个小test，虽然不多代码但小菜弄了许久，发上来给大家分享下如果有什么错误或建议欢迎大家为我指出来啊 小菜感激不尽。。。其实一开始是在想如何在cocos2dx中加入广告及第...
未定义与 ‘string‘ 类型的输入参数相对应的函数 ‘uplus‘
pangweijian的博客
10-27 2415
在matlab中使用eval执行字符串命令时，字符串太长使用三个点进行换行时，提示错误，如图。 是designer不支持这种方式还是eval这个函数不支持？？？？ eval(["app.Label_"+"21"+".Position(1) = app.TabGroup.Position(3)/15+10;"+"app.Label_"+"21"+".Position(2) = app.TabGroup.Position(4)-200;"]) ...
2.3 分支结构-if语句的嵌套
芝麻软件工作室的专栏
06-11 5376
如果if语句中的“内嵌语句”（即语句1或语句2）也是if语句，就构成了“嵌套if语句”。 嵌套的基本格式 嵌套if语句有2种形式： 嵌套在else分支中，格式如下： if (表达式1) 语句1; else if (表达式2) 语句2;     else if …         else语句n;嵌套在if分支中，格式如下：     if (表达式1){         i
使用函数统计指定数字的个数
前景
04-09 3998
#include&lt;stdio.h&gt; int countdigit(int number,int digit) { int i,a,b=0; while(number!=0) { a=number%10; if(a==digit) { b++; } number/=10; } return b; } int main() { int b,c,a; ...
5-5 使用函数统计指定数字的个数
WangYutao的博客
09-01 1万+
//使用函数统计指定数字的个数 #include int countdigit(int number,int digit); int main(int argc,char const *argv[]) { int number,digit; scanf("%d%d",&number,&digit); while(1) { if(digit>=0&&digit<=9) brea
excel常用数据分析函数
07-12
Excel常用的数据分析函数包括： 1. SUM：求和函数，用于计算一组数值的总和。 2. AVERAGE：平均值函数，用于计算一组数值的平均值。 3. COUNT：计数函数，用于统计一组数值中的非空单元格数量。 4. MAX：最大值函数，用于找出一组数值中的最大值。 5. MIN：最小值函数，用于找出一组数值中的最小值。 6. MEDIAN：中位数函数，用于找出一组数值的中间值。 7. MODE：众数函数，用于找出一组数值中出现频率最高的值。 8. STDEV：标准差函数，用于计算一组数值的标准差。 9. VLOOKUP：垂直查找函数，用于根据指定的值在一个区域中查找匹配的值。 10. IF：条件函数，用于根据指定条件返回不同的值。 这些函数可以帮助你在Excel中进行常见的数据分析和统计操作。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值