当前位置:科学 > 正文

IF函数详解,如何用Excel、Python、SQL、Tableau实现IF函数功能

2023-01-27 19:53:29  来源:财务随想

IF函数详解,Excel+Python+SQL+Tableau四个工具同时登场

IF 函数是 Excel 中最常用的函数之一,它可以对值进行逻辑比较。这个函数的语法非常符合人类语言,


(资料图)

如果……就……否则……”

比如说如果你喜欢我,我们就结婚,否则就不结婚

用IF来实现就是=IF(“you love me”,”we are married”,”we aren’t married”)

使用逻辑函数 IF 函数时,如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。

第一个参数是条件真假值,第二个参数是条件为真时的返回值,第三个参数是条件为假时的返回值

这个函数还可以写成无限嵌套模式(俗称套娃),IF(IF(IF(IF,……,但是个人不建议这样写,因为这种写法实在是太容易给自己弄迷糊,如果是多条件判断,可以考虑IFS,如果只是取值判断建议写成IF()+IF()+IF() 单IF函数相加这种形式完成

本例主要解决以下问题:

1如果成绩大于等于600分,则返回"优秀",否则返回"普通"

2如果成绩大于等于600分,则返回"优秀",大于等于500分小于600分返回"普通",小于500分返回"不优秀"

3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀"

4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通"

5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ”

6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名

实现方式:Excel、Python、SQL、Tableau

Excel实现

1如果成绩大于等于600分,则返回优秀,否则返回普通

在J2单元格输入公式:=IF(I2>=600,优秀,普通)

2如果成绩大于等于600分,则返回优秀,大于等于500分小于600分返回普通,小于500分返回"不优秀"

在J2单元格输入公式:=IF(I2>=600,优秀,IF(I2>=500,普通,"不优秀"))

说明:此时一共有两个IF函数,里面嵌套了一个,这个语句先判断第一个IF,如果大于等于600,则返回优秀,然后把剩下的小于600的单元格区域丢给第二个IF函数判断,因为此时这里面都是小于600分的单元格了,所以只需要在判断条件处写大于等于500即可,没必要再加小于600分的条件,然后进行判断,把里面500分及以上的返回普通,剩下的丢给后面,如果还有IF的话就再继续判断,没有的话,则统一返回最后一个IF函数里面的第三个参数,这里也就是"不优秀",如果不用嵌套,改用IFS函数如何实现本例呢?

改用IFS函数实现,在J2单元格输入公式:=IFS(I2>=600,优秀,I2>=500,普通,I2<500,"不优秀")

用法:IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

这里面最少要有两个参数,第一个参数是判断条件,第二个参数是返回值,最多可以判断127个条件,也就是254个参数,和SUM求和的参数极限类似

IFS 函数检查是否满足一个或多个条件,且返回符合第一个TRUE 条件的值,判断时是按顺序依次进行,这个函数可以取代多个嵌套 IF 语句,并且有多个条件时更方便阅读

如果找不到 TRUE 条件,则此函数返回 #N/A! 错误,例如只输入一个大于600分的条件判断:=IFS(I2>=600,"优秀")

这时小于600分的部分会返回#N/A,也就是查不到

此时我们需要前面加一个IFERROR进行修复一下,比如小于600分的返回普通,输入公式:=IFERROR(IFS(I2>=600,"优秀"),"普通")

IFERROR用法说明:IFERROR(value, value_if_error)

第一个参数是正常返回的值,当第一个参数无法返回值时返回第二个参数作为结果

还有一个函数IFNA(),用法基本和这个一样

这里相当于用了一个复合函数,首先IFS(I3>=600,"优秀") 作为IFERROR函数的第一个参数,这里可以完成大于等于600分以上条件的返回,这个参数IFERROR并不理会,而只有当这个条件不能返回时,即前面所说的查不到的#N/A部分,IFERROR函数会自动把这一部分的单元格返回第二个参数,这里面也就是返回“普通”

3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀"

输入公式=IFERROR(IFS(AND(B11="男",I11>600),"男优秀",AND(B11="女",I11>600),"女优秀"),"普通")

说明:AND和OR函数

AND是且判断,都为真时为真,只要有一个为假则为假,AND(1=1,2=2,3=3,4=4)为真,AND(1=1,2=2,3=3,4=5)为假

OR是或判断,都为假时为假,只要有一个为真则为真,OR(1=2,3=4,5=6,7=8)为假,OR(1=2,3=4,5=6,7=7)为真

这两个一般都需要配合前面的IF函数进行使用,应该很少有单独使用场景

4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通"

输入公式:=IF(OR(E2>120,F2>120),"单科优秀","普通")

5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ”

输入公式:=IFERROR(IFS(ISNUMBER(FIND("剑",E2)),"剑法",ISNUMBER(FIND("刀",E2)),"刀法"),"其他")

IS类函数返回逻辑值:ISNUMBER 搭配 FIND函数使用,可以对指定单元格以判断是否存在关键字词进行对应返回

类似的函数还有以下:

FIND用法说明:FIND(find_text, within_text, [start_num])

第一个参数是要查找的关键字,这里我们输入的是“剑”或者“刀”,此参数严格区分大小写,并且不允许使用通配符

第二个参数是要在哪个文本里查找,我们这里在技能列查找,比如田伯光的技能“狂风刀法”

第三个参数可选,是从第几个开始查询,默认为1

拆解公式:FIND("刀","狂风刀法",1) 这个公式返回的是一个数字3,也就是说刀字在这个单元格字符里面的第3位,只要能返回数字不管在第几位,也就意味着这个单元格里包含有我们要查找的关键字

然后我们再用ISNUMBER函数把这个数字3转化为逻辑值真,为IFS函数提供参数用

ISNUMBER(FIND("刀","狂风刀法",1))返回值 为TRUE

我们可以看到除了田伯光技能’狂风刀法‘里面含有“刀”字,返回的是刀法,其他大多为剑法

这个函数用处场景较多,之前我曾见过某同事在处理大量数据时,用筛选包含关键字的形式一次一次地手动输入返回值,如果能用这个函数则会极大提高数据处理效率

6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名

FIND查找函数配合字符提取函数LEFT,RIGHT,MID等,可以实现强大的字符提取功能:

从以下字符串中提取省名,=MID(A1,1,FIND(“省”,A1))

我们发现如果用LEFT进行向左直接取数字几位的话无法实现,因为有的省是两个字,有的是三个字,这时我们就需要借助FIND对每一行的省名有几个字符进行判断

FIND("省",A1))返回值显示包括省字在内的省名个数,比如前面两条返回的是3,黑龙江的返回的为4

MID用法:MID(text, start_num, num_chars)

第一个参数表示,要从哪个字符串里提取,这里我们取A列

第二个参数表示,从第几个字符开始提取,这里我们填1,表示从头进行提取

第三个参数表示,提取多少个字符,这里正是FIND函数起作用的地方

如前,假如提取省名不包括省字本身,则只需要在第三个参数后面减1即可,也就是提取字符少一位

=MID(A1,1,FIND("省",A1)-1)

这里我们也可以使用LEFT函数,=LEFT(A1,FIND("省",A1))

LEFT函数只有两个参数,第一个是要提取的单元格,第二个是提取的位数,从左

同理:RIGHT函数两个参数,第一个是要提取的单元格,第二个是提取的位数,从右

如果把大庆市改为齐齐哈尔市,这回我们提取后面的市名,公式:=MID(A1,FIND("省",A1)+1,10)

这里FIND函数传递的值是作为MID函数的第二个参数,也就是从第几位开始提取,FIND("省",A1)+1表示出现省字后面第一个字符的位数,第三个参数可以写一个比较大的数字,只要提取的位数小于这个数值,则默认全部提取到最后

还可以用RIGHT提取:=RIGHT(A1,LEN(A1)-FIND("省",A1))

这里RIGHT的第二个参数是用了计算得来了,省名的位数FIND("省",A1),LEN(A1)表示全部的位数,两者相减得到的则是要提取的市名的位数

还可以再升级,后面加上县名等,我们从中间提取市名=MID(A1,FIND("省",A1)+1,FIND("市",A1)-FIND("省",A1))

说明:

FIND("省",A1)+1 表示市名第一个文本出现在第几位上

FIND("市",A1)-FIND("省",A1) 表示市名文本的字符长度,例如第一个,FIND("市",A1)=6表示市字出现在第6位,FIND("省",A1)=3,省字出现在第三位,两者的差=6-3=3 则是“铁岭市”的字符长度

Python实现:

1如果成绩大于等于600分,则返回"优秀",否则返回"普通"

代码如下:

def score_if(score):

if score >=600:

a = "优秀"

else:

a = "普通"

return a

df["是否优秀"] = df["总成绩"].apply(lambda x:score_if(x))

这种我们是用lambda表达式实现:

前面自定义一个函数,score_if 进行逻辑判断取数

df["是否优秀"] = df["总成绩"].apply(lambda x:score_if(x))

这条语句相当于把总成绩那列应用到函数score_if,然后把返回值赋给”是否优秀”列

附:本例还可以用一行代码完成,使用lambda匿名函数,这是一种没有名称的函数

df["是否优秀"] = df["总成绩"].apply(lambda x: "优秀" if x>=600 else "普通")

2如果成绩大于等于600分,则返回"优秀",大于等于500分小于600分返回"普通",小于500分返回"不优秀"

代码如下:

def score_if(score):

if score >=600:

a = "优秀"

elif score>=500:

a = "普通"

else:

a = "不优秀"

return a

df["是否优秀"] = df["总成绩"].apply(lambda x:score_if(x))

这里第9行代码也可以写成这样 df["是否优秀"] = df["总成绩"].apply(score_if)

appy 是应用的意思,这行表示把总成绩列作为score_if函数的参数,然后函数返回值赋给是否优秀列

如果用lambda函数,一行代码:

df["是否优秀"] = df["总成绩"].apply(lambda x: "优秀"if x>=600 else("普通"if x>=500 else"不优秀"))

3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀"

代码如下:

def score_if(score,sex):

if score >=600 and sex=="男":

a = "男优秀"

elif score >=600 and sex=="女":

a = "女优秀"

else:

a ="普通"

return a

df = pd.read_excel("c:/study_note/xiao_if.xlsx",sheet_name="成绩单")

df["是否优秀"] = df.apply(lambda df:score_if(df["总成绩"],df["性别"]),axis=1)

附:还有一种写法如下

def score_if(score,sex):

if score >=600 and sex=="男":

a = "男优秀"

elif score >=600 and sex=="女":

a = "女优秀"

else:

a ="普通"

return a

df = pd.read_excel("c:/study_note/xiao_if.xlsx",sheet_name="成绩单")

df["是否优秀"] = None

for i in range(len(df)):

df["是否优秀"][i] = score_if(df["总成绩"][i],df["性别"][i])

这里用到了条件且在自定义函数里面,因为自定义函数有两个参数,我再后面用了一个for循环语句

第10行:新建一个字段,命名为‘是否优秀’

第11行,让变量i进行循环,len(df)表示原表的行数,等于18,range(18)相当于让i从0取到17

第12行,把每一行的总成绩列值作为函数的第一个参数,性别列值作为第二个参数,然后把返回值赋给是否优秀列,因为i是变量,从0到17(意义表示从第1行到第18行)

在python中and和or函数,布尔类型也是只有True和False两种取值,python中的布尔运算符and,or,not全是小写

逻辑运算和Excel一样

但是当用pandas库的数据类型 DataFrame 和Series 时进行逻辑运算时,此时运算符变成

& 表示 and 且

| 表示 or 或

~ 表示not 非

4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通"

代码:

def score_if(chi,math):

if chi >=120 or math >=120:

a = "单科优秀"

else:

a ="普通"

return a

df = pd.read_excel("c:/study_note/xiao_if.xlsx",sheet_name="成绩单")

df["是否优秀"] = df.apply(lambda df:score_if(df["语文"],df["数学"]),axis=1)

5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ”

代码如下:

df = pd.read_excel("c:/study_note/xiao_if.xlsx",sheet_name="花名册")

col_name = df.columns.tolist()

col_name.insert(col_name.index("技能")+1,"技能类型")

df = df.reindex(columns=col_name)

def tech(x):

if x.find("剑") >=0:

a = "剑法"

elif x.find("刀")>=0:

a = "刀法"

else:

a = "其他"

return a

df["技能类型"] = df["技能"].apply(tech)

第3行:把原表所有列名转换为列表格式,并传递给变量col_name

第4行:在列名列表里‘技能’文本后面新插入一个文本‘技能类型’,作为一个新的字段列名

第5行:把新增加的字段传递给原表df

第6行: x.find(参数),表示在x单元格查找参数所有的位置,如果单元格里存在参数,则返回位置数字,如果不存在则返回-1,如果参数在单元格第1位,则返回0,因为在python里顺序是从0开始,0代表第一位,x.find("剑") >=0表示单元格值里面存在“剑”这个关键词,相当于Excel时面函数ISNUMBER(FIND("剑","独孤九剑",1))返回值为TRUE一样

6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名

代码如下:

df["省"] = df["地址"].apply(lambda x: x[0:x.find("省")+1])

df["市"] = df["地址"].apply(lambda x: x[x.find("省")+1:x.find("市")+1])

df["县"] = df["地址"].apply(lambda x: x[x.find("市")+1:])

这里直接用了python里面的切片(slicing)语法:sequence[start:stop:step]

sequence是序列的意思,第一个参数是起始位置,第二个参数是结束位置(不包括本身,相当于数学里面的左闭右开区间),第三个参数是步长,举例:a = “辽宁省铁岭市昌图县八面城镇“在本例中文本值作为一个序列

a[0:3]表示:从第1位开始取3位(0,1,2)

a[:3]表示:和上面一样,只是省略了第1个参数

a[::]表示全取,三个参数都省略了

a[-4:] 表示从右边第三位开始向右取数取到最后

a[::2]表示每两个元素取一个

a[-1:-5:-1]表示从右往左取4位,这里面第二个参数和第三个参数都是负数,第二个参数相当于从右往左数第5位(是开区间不取本身),第三个参数1是步长,每个都取,负号表示从右往左取

三、SQL实现方法:

1如果成绩大于等于600分,则返回"优秀",否则返回"普通"

代码如下:

SELECT姓名,总成绩, IF(总成绩>=600,"优秀","普通") AS是否优秀 FROMscore ;

2如果成绩大于等于600分,则返回"优秀",大于等于500分小于600分返回"普通",小于500分返回"不优秀"

代码如下:

SELECT姓名,总成绩, IF(总成绩>=600,"优秀",IF(总成绩>=500,"普通","不优秀")) AS是否优秀 FROMscore ;

上面这条查询也可以写成下面这样:

SELECT姓名,总成绩,

CASEWHEN总成绩>=600THEN"优秀"

WHEN总成绩>=500THEN"普通"

ELSE"不优秀"

ENDAS是否优秀

FROMscore ;

3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀"

代码如下:

SELECT姓名,总成绩,

CASEWHEN总成绩>=600AND性别="男"THEN"男优秀"

WHEN总成绩>=600AND性别="女"THEN"女优秀"

ELSE"普通"

ENDAS是否优秀

FROMscore ;

在MYSQL中AND 和 OR函数,布尔类型也是只有True和False两种取值

NOT 符号是! 非

AND 符号是 && 且

OR 符号是|| 或

XOR 表示异或

a XOR b 这个逻辑运算相当于 a AND (NOT b) 或者 (NOT a) AND b

算法与Excel一样

4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通":

SELECT姓名,语文,数学,

CASEWHEN语文>=120OR数学>=120THEN"单科优秀"

ELSE"普通"

ENDAS是否优秀

FROMscore ;

5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ”

代码如下:

SELECT姓名,技能,

CASEWHEN技能 REGEXP"[刀]"THEN"刀法"

WHEN技能 REGEXP"[剑]"THEN"剑法"

ELSE"其他"

ENDAS技能类型

FROMnames;

6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名

代码如下:

SELECT地址,

MID(地址,1,LOCATE("省",地址)) AS省,

MID(地址,LOCATE("省",地址)+1,locate("市",地址)-LOCATE("省",地址)) AS市,

MID(地址,LOCATE("市",地址)+1,locate("县",地址)-LOCATE("市",地址)) AS

FROMtemp3;

这里MID函数不同于Excel,SQL里是可以引入负数作为第二个参数,代表的意思是从右往左,locate本身就是定位的意思,这个函数相当于Excel里的FIND函数

比如SELECTmid("basketball",-4);这里省略了第三个参数,从这个字符最右往左数第4位开始取数,一直向右取到完

SELECTmid("basketball",-4,2);如果加上第三个参数,从这个字符最右往左数第4位开始取数,取数长度为2位

本例还可以用LEFT 和RIGHT函数进行提取:代码如下

SELECT地址,

LEFT(地址,LOCATE("省",地址)) AS省,

MID(地址,LOCATE("省",地址)+1,locate("市",地址)-LOCATE("省",地址)) AS市,

RIGHT(地址,CHAR_LENGTH(地址)-LOCATE("市",地址)) AS

FROMtemp3;

四、Tableau实现:

1如果成绩大于等于600分,则返回"优秀",否则返回"普通"

打开Tableau软件,连接到工作表成绩单,然后创建一个计算字段输入公式:

IF [总成绩] >= 600 THEN "优秀" ELSE "不优秀" END

2如果成绩大于等于600分,则返回"优秀",大于等于500分小于600分返回"普通",小于500分返回"不优秀"

计算字段代码如下:

IF [总成绩] >=600 THEN "优秀"

ELSEIF [总成绩] >=500 THEN "普通"

ELSE "不优秀" END

3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀"

计算字段代码如下:

IF [总成绩] >=600 AND [性别]=="男" THEN "男优秀"

ELSEIF [总成绩] >=600 AND [性别]=="女" THEN "女优秀"

ELSE "普通" END

4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通"

代码如下:

IF [语文] >=120 OR [数学] >=120 THEN "单科优秀"

ELSE "普通" END

5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ”

新建Tableau工作簿,并连接到花名册表,新建计算字段技能类型,代码如下:

IF CONTAINS([技能],"剑") THEN "剑法"

ELSEIF CONTAINS([技能],"刀") THEN "刀法"

ELSE "其他" END

CONTAINS函数是Tableau特有的,返回的也是一个布尔逻辑值,相当于SQL中的WHEN技能 REGEXP"[刀]"(这里是正则查找,返回的也是逻辑值),也相当于Excel中的ISNUMBER(FIND("刀","狂风刀法",1))

6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名

代码:

计算字段省:MID([地址],1,FIND([地址],"省"))

计算字段市:MID([地址],FIND([地址],"省")+1,FIND([地址],"市")-FIND([地址],"省"))

计算字段县:MID([地址],FIND([地址],"市")+1,LEN([地址])-FIND([地址],"市"))

需要注意,Tableau中的FIND函数和Excel中的FIND函数两个参数的位置正好相反

到此四种工具全部完成任务,如果觉得有用请点下关注,可以和本人互动交流,共同学习进步

关键词: Excel Python

推荐阅读

iOS13正式版推送 iOS13正式版推送值得更新吗?

苹果发布iOS13 5,iOS和iPadOS13 5系统正式版发布,系统新增加了多项功能,用户在安装了正确的开发人员配置文件后可以从Apple开发人员中心 【详细】

虎门大桥怎么样了 虎门大桥没有问题可以恢复通车了吗?

关于虎门大桥已经连续几天成为热门话题了,5月5日,最开始为了保障安全,对交通实行双向封闭。5月10日,组织报告测评,而今天根据官方消息 【详细】

2025年取消燃油车 2025年取消燃油车是真的吗?

相信很多人都听说过中国2025年取消燃油车,接下来小编就带大家介绍一下相关知识,大家可以了解一下。汽车一般使用汽油、柴油等化石燃料作为 【详细】

未成年打赏可退还是真的吗?未成年打赏可退有什么法律依据?

今日,未成年打赏可退还的消息引发人们的热切讨论,未成年人网络打赏引发的纠纷,多年来成为人们关注的社会热点问题,那么未成年打赏可退还 【详细】

首都第二国际机场在哪里 首都第二国际机场为何选址大兴?

首都第二国际机场为何选址大兴?大兴位于北京的南部,为什么选在南边建设机场?现在北京机场有几个?为了确保首都的安全,南部修建了北京第二 【详细】

相关新闻

关于我们  |  联系方式  |  免责条款  |  招聘信息  |  广告服务  |  帮助中心

联系我们:85 572 98@qq.com备案号:粤ICP备18023326号-40

科技资讯网 版权所有