Excel常用公式

总结

工作学习中常用的Excel公式，主要有拼接、截取字符串，IF、VLOOKUP函数等，可搭配其他笔记联合使用。

详情

拼接字符串

excel表格公式那一列需要常规格式，公式才生效
//&符号拼接,表格格式需要常规格式

//示例1
=D1&".json"

//示例2
=""""&A1&""""&","

//示例3
=""""&A1&""""&";"

//示例4
="'"&A1&"'"&","

//示例5
="('"&A1&"'"&","&"'"&B1&"'"&","&"'"&C1&"'"&","&"'"&D1&"'"&","&"'"&E1&"'"&","&"'"&F1&"'"&")"&","

//示例6：拼接生成sql
="UPDATE stu SET status=1,score=" & C1 & " WHERE `code`='" & A1 & "';"

截取特定字符串

测试原文如下：
{"BaseBoardUuid":"08304DF4-0FF9-1620-0429-090950000000","CPU":"Intel(R) Core(TM) i3-4170 CPU @ 3.70GHz i386","CurDisk":"C:","DISK":["C:/ 61.3G/80.0G","D:/ 124.3G/127.0G","E:/ 126.0G/127.0G","F:/ 70.3G/77.8G"],"GPU":{"list":[{"description":"Intel(R) HD Graphics 4400","deviceId":"1054","revision":"6","screenList":[{"dpi":"1366*768","id":"0"}],"screenSize":1,"sharedSysMem":"1474M","subSysId":"274599973","sysMem":"0M","vendorId":"32902","videoMem":"64M"}],"size":1},"LANIP":["192.168.7.29"],"MEMORY":"2.06G/3.38G","MachineName":"FOUNDER-PC28","MachineUniqueId":"658cb77b-6476-496d-a21c-690768b53e9e","OS":"Windows 7 Version 6.1 (Build 7601: SP 1) 6.1.7601 32bit","ScreenInfo":{"count":1,"height":728,"height_cm":27,"width":1366,"width_cm":48},"openGL":"4.3","isTest":{"CPU":true,"硬盘":true,"内存":true,"操作系统":true}}

//MID：用于提取文本 
//FIND：用于查询字符出现位置
//从单元格 E2 中提取从 "CPU" 前一个字符开始，到 "CurDisk" 前一个字符结束之间的字符串内容
=MID(E2,FIND("CPU",E2)-1,FIND("CurDisk",E2)-FIND("CPU",E2)-1)
则取出文本："CPU":"Intel(R) Core(TM) i3-4170 CPU @ 3.70GHz i386"

//两个双引号表示输出一个",""""&"OS"&""""&":" 就等于"OS":
=MID(E2,FIND(""""&"OS"&""""&":",E2),FIND("ScreenInfo",E2)-FIND(""""&"OS"&""""&":",E2)-3)
则取出文本："OS":"Windows 7 Version 6.1 (Build 7601: SP 1) 6.1.7601 32bit

IF 函数

//如果C2是空,输出D2,否则输出C2;
=IF(ISBLANK(C2),D2,C2)

//如果H2是空,F2为空则"未参加",否则根据F2判断;如果H2不为空,否则根据H2合格状态判断;
=IF(ISBLANK(H2), (IF(ISBLANK(F2), "未参加", IF(F2 = "合格", "合格", "不合格")), IF(H2= "合格", "合格", "不合格"))

//IF
=IF(A2=G2,"是","否")

查询函数-VLOOKUP

//示例一:
//E2是关联列,根据E2列精确匹配表中的K~L列,匹配则返回K~L中的第2列,FALSE表示精确匹配
=VLOOKUP(E2,K:L,2,FALSE)

//示例二:
//E2是关联列,根据E2列精确匹配补充成绩表的B~K列,匹配到则返回B~K中的第2列,FALSE表示精确匹配
=VLOOKUP(E2,[Temp.xlsx]Sheet1!$B:$K,2,FALSE)