Link Search Menu Expand Document

Excel 笔记

excel 常用函数

excel 合并多个单元格数据和同一个单元格文字、函数共存

文字使用""包裹起来,函数之间加&,最前面需要=

同一个单元格文字、函数共存

  • 总数+具体计算结果数值
="总数:"&SUM(A1:A10)

合并多个单元格文字和数据

  • 使用下面方法即可得到合并结果
=A2&"减去"&B2&"小明得到"&C2&"苹果比原来多"&D2

含文字的单元格求和

=SUM(--SUBSTITUTE(B2:B11,"元",""))

计算 B2 至 B11 单元格数据总和,B 列单元格内容如下:34234元

多区域或跨表操作

多工作表求和

=SUM('1:5月'!C2)

计算 工作表:1月、2月、3月、4月、5月中 C 列数值总和。 部分软件可能不支持。

多区域求和

=SUM(B2:D2,F2,H2)

计算 B2、C2、D2和F2、H2的总和

筛选数据计算

条件计算注意!!

  • 计算区域不要出现 #N/A 类似内容,不然会无法结算出结果。

月份汇总计算合计或平均数

数据明细增加月份辅助列,多条件计算时,使用辅助列求和或求平均数

=TEXT(A2,"yyyy-mm")
  • 假设 A2= 2022-07-21,公式提取到的内容为 2022-07
  • 然后做汇总表的时候,A列可以使用 2022-07、2022-06来区分月份

条件求和

=SUM((B2:B11="日用品")*(E2:E11))

B列商品类型,E列销售额,公式计算日用品的销售总额

一个条件求和

=SUMIFS(D2:D11,A2:A11,H2)

D列:数值数据; A列:产品信息,如苹果、橘子、柠檬; H2=柠檬;

多条件求和

=SUMIFS(D2:D11,A2:A11,"南部",C2:C11,"肉类")

D列:数值数据; A列:地区信息,如南部、北部; C列:产品类型,如肉类、蔬果类;

公式汇总计算南部肉类总数。

多条件求平均值

=AVERAGEIFS(D2:D11,A2:A11,"南部",C2:C11,"肉类")

方法和多条件求和一样

条件计数

=SUM(1*(B2:B11="日用"))

B列商品类型,公式计算日用品出现次数

指定相同数值、文本计数

=COUNTIF(A:A,D2)

D2 的内容是大白菜,假设A列有10个单元格大白菜,公式运算后会计算出 10;

指定相同数值、文本的和

=SUMIF(A:A,D2,B:B)

D2 的内容是大白菜,假设A列是名称,B列是加固,公式运算后会计算所有的大白菜合计总价。 A列匹配数据,B列取值计算。

复杂的函数计算

运算嵌套规则

=IF(B2=n1,"显示内容1",IF(B2=n2,"显示内容2",IF(B2=n3,"显示内容3")))
  • B2 表示 B2 单元格;
  • n2表示具体的内容;
  • 显示内容2表示运算结果,最终显示在当前编辑单元格的内容
  • 逻辑:当B2等于n1时,显示显示内容1

IF AND 函数运算案例

一个活动,会员通过使用积分兑换到的话费可以比其他用户多

兑换规则

积分会员兑换其他兑换
332
664
12128

记录表

 ABCD
1登记日期类型积分活动奖励
22021-05-31会员1212
32021-05-31其他32
42021-05-31其他128

先匹配C列,会员的积分可以获得对应的奖励,然后根据规则,如果是用户类型为”其他“就根据对应积分进行减去操作。

把函数放到 D2 然后后面的手动拉。就可以看到记录表上的效果。

下面如果是文字就必须带上""(英文双引号),数字可以不带,但是如果使用下拉列表功能,最好是带上,不然无法计算出正确结果。

=IF(C2="3","3",IF(C2="6","6",IF(C2="12","12","0")))-IF(AND(B2="其他",C2="3"),"1",IF(AND(B2="其他",C2="6"),"2",IF(AND(B2="其他",C2="12"),"4","0")))

参考 https://www.yinxiang.com/everhub/note/fa76ae54-ea72-4105-b71f-cdd590413962 https://blog.mimvp.com/article/42400.html

IF AND 运算案例,大于并且小于等于

=IF(B2>=100,”优秀“,IF(AND(B2>=95,B2<100),"良好",IF(AND(B2>=90,B2<95),”一般“)))

常用计算的函数

Ecxel 计算有数据的表格个数

=COUNTA(A1:B7) 计算区域中非空单元格的个数

平均值

函数说明
=AVERAGE(A2:A6)A2 到 A6 区域单元格中数字的平均值。
=AVERAGE(A2:A6, 10)先计算 A2 到 A6 区域单元格中数字平均值,最终计算这个平均值与数字 10 的平均值。
AVERAGEIF
=AVERAGEIF(A2:A6,"=*苹果",B2:B6)(A 列产品名,B 列售价)求陕西苹果、黄苹果、红苹果的价格平均值。
=AVERAGEIF(A2:A6,"<>*(苹果)",B2:B6)(A 列产品名,B 列售价)求所有苹果以外产品价格的平均值。
=AVERAGEIF(A2:A5,"<5")(A 列人数,B 列费用)求人数小于 5 的单元格人数平均值。
=AVERAGEIF(A2:A5,">5",B2:B5)(A 列人数,B 列费用)求人数大于 5 的单元格,费用的平均值。

LOOKUP 函数

使用 LOOKUP 匹配两个条件或多个条件提取指定值

=LOOKUP(2,1/(明细[型号]=b2)/(明细[颜色]=$c$1),(明细[价格]))

假设我们有个手机报价明细表,有三列:型号、颜色、价格; 假设颜色只有红色、蓝色、黑色、白色,这个时候我们想直观的知道一共型号在这四个颜色覆盖情况; 我们新建一个表,标题为:型号、红色、蓝色、黑色、白色; 新建表单元格坐标:型号(B1)、红色(C1)、蓝色、黑色、白色; 上面的函数公式基于表格格式,设置表格格式并且命名好后,比设置 A:A 或者 A2:A40 要方便快捷,并且非常直观明了。

LOOKUP 反向查询一个条件提取指定值 列倒着查

=LOOKUP(1,0/(D10=B2:B17),A2:A17)

A列产地,B列水果品种,D10=苹果。获取苹果产地。

VLOOKUP 函数

使用 VLOOKUP 比对数据内容自动按要求填写数据

查找指定指标的值,提取另外一张表项目相应的数据值。

有时候我们需要把两个表格数据合并,但是数据列数不同,相同指标的数据也不在同一行,这个时候就很难处理。使用VLOOKUP函数就可以很好解决多列相同指标值合并。

避免数据遗漏,可以结合”高亮标记两列重复值“使用。

因为两张表产品所在列不一致,现在要在今天的价格后面给产品补充对应昨天的价格。 下面案例之所以选取的是一整列,是为了避免拉公式时单元格定位会递增。

 ABCDE
1产品昨天价格产品今天价格昨天价格
2苹果8香蕉4写函数公式
3柠檬6苹果7拉取公式
4香蕉3橘子7拉取公式
函数说明
=VLOOKUP(C2,A:B,2,FALSE)假设公式写在 E2 单元格,C2 代表要查询的产品,在A和B列查询,读取第二列(B)的值,FALSE 标识完全匹配 TRUE 标识近似匹配。E2 计算结果为3

这条 VLOOKUP 公式说明:=VLOOKUP(查询值, 查询范围, 取值列为查询范围的第几列, 是否完全一致)

使用 VLOOKUP 根据范围取值,如分数的等级

 ABCDE
1成绩等级 分数区间等级
286写函数公式(结果:良) 0
370拉取公式 60合格
478拉取公式 70一般
595拉取公式 80
640拉取公式 90

B2 的函数公式:

=LOOKUP(A2,$D$2:$D$6,$E$2:$E$6)

这条 VLOOKUP 公式说明:=LOOKUP(查询值,对应表查询值范围,对应表对应值范围)

合并条件后使用 VLOOKUP 多条件查询

=VLOOKUP(h15&i15,'版权'!c:j,3,0)
  • 版权工作表中c列=a&b。
  • 当前工作表h15、i15数据同版权工作表中a列、b列。

INDEX 函数

通过 INDEX 和 MATCH 进行多条件查询

=INDEX(J17:J23,MATCH(H16,H17:H23))

在 J17:J23 中查找,H17:H23 包含 H16

  • 查询数据列:J17:J23
  • 通过制定值去查找匹配对应列:H16,H17:H23

从多个区域中提取数据(多区域用法)多范围

给定2个区域,取第2个区域的第5行第3列的值。

公式: =INDEX((A2:E9,G2:K9),5,3,2)

通过 INDEX 和 MATCH 倒着查询通过条件提取值

=INDEX(A:A,MATCH(G2,C:C,))

条件:G2,检索C列,提取A列

条件编辑

提取两列重复的(未测试)

=IF(ISERROR(MATCH(A2,$B$2:$B$25,0)),"",A2)

Excel 秒数转换成时分秒时长格式

  • A2 替换为秒数所在单元格
=TEXT(A2/3600/24,"[H]:mm:ss")

批量提取一列单元格文本中的数字

 AB
1文本数字
2身高180厘米180
3体重100公斤100
4烦恼丝5000根5000
5吃饭5碗5
  • 在单元格 B2 手动输入 A2中的数值
  • 按回车键 Enter & Return
  • 按快捷键 Ctrl + E 或者 Control + E 或者 Command + E
  • 会自动提取 A 列整列的数值,不同的软件快捷键可能不同,可以都试一下

自动获取数据

获取现在的日期年月日,今天日期

获取现在年月日

=TODAY()

获取现在年份

=YEAR(TODAY())

获取现在的时间(年月日时分秒)

=NOW()

计算特定日期时间举例现在差多少天、小时、分钟

A2 单元格内容:2021-11-23 16:50:22;

=TEXT(NOW()-A2,"d天 h小时 mm分钟")

自动获取本月有多少天

=DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,))

=DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1)
// 下月第一天的前一天为本月最后一天

自动获取本月最后一天的日期

=EOMONTH(TODAY(),0)
// 下月第0天即上月最后一天

=eomonth(today(),0)
// 本月最後一天

=eomonth(today(),-1)
// 上月最後一天

=eomonth(today(),1)
// 下月最後一天

=eomonth(B1,0)
// 某月最後一天,B1=2022-01-14

自动获取本月第一天的日期

=eomonth(today(),-1)+1
// 本月第一天

=eomonth(today(),-2)+1
// 上月第一天

=eomonth(today(),0)+1
// 下月第一天

=eomonth(B1,-1) +1
// 某月第一天,B1=2022-01-14



原文網址:https://kknews.cc/news/q54x9g.html

Ecxel 时间戳格式和日期格式互转

  • 其中的8为 8*3600 当地时区,中国为 UTC+8

时间戳转常见日期时间格式

  • A2 替换为时间戳所在单元格
  • 计算后如果现实不正常,将其计算的单元格的格式改为日期时间格式
=(A2+8*3600)/86400+70*365+19

普通日期转时间戳

  • A3 替换为普通日期时间所在单元格
=(A3-70*365-19)*86400-8*3600

日期时间函数:TODAY

函数说明
=TODAY()返回当前日期。
=TODAY()+5返回当前日期加 5 天。 例如,如果当前日期为 1/1/2012,此公式会返回 1/6/2012。
=DATEVALUE("2030-1-1")-TODAY()返回当前日期和 1/1/2030 之间的天数。 请注意,单元格必须为“常规”或“数值”格式才能正确显示结果。
=DAY(TODAY())返回一月中的当前日期 (1 - 31)。
=MONTH(TODAY())返回一年中的当前月份 (1 - 12)。 例如,如果当前月份为五月,此公式会返回 5。
=DATE(YEAR(A2),MONTH(A2),DAY(A2))提取指定单元格年、月、日

日期转为星期

=TEXT(A1,"aaaa")
=TEXT(A1,"周aaa")
=TEXT(A1,"bbbb")

A1 为原日期所在单元格,假设 A1 的值是 2021-12-13; aaaa 转为中文:星期一; aaaa 转为中文:周一; bbbb 转为英文:Monday。


筛选最小值 MINIFS 或者最大值 MAXIFS,最早时间或最晚时间

 ABCDEFG
1uiddateamount uidamountamount(min)
21012022/2/310 10110010
31012022/5/6100 10220020
41022022/2/320  =MAXIFS(D:D,B:B,F8,C:C,MAX(C:C))=MINIFS(D:D,B:B,F8,C:C,MIN(C:C))
51022022/5/6200    
  • 上表公式,通过 uid 获取对应用户最新日期的 amount 值

日期格式转换

指定日期格式(内容固定格式不需要通过工具栏设置格式)

=TEXT(A2,"yyyymmdd")
// 20211223 转换格式

=TEXT(A2-1,"yyyy-mm-dd")
// 2021-12-22 减少一天并转换格式

当A2单元格填写的内容为 2021/12/23,以下为转换不同格式公式案例和计算天数公式案例。

yyyymmdd 的具体写法可以参考“设置单元格格式”面板。

这样写就不会变成一串看不懂的数据了,也不会因为复制粘贴格式乱了。

更多格式参考(TEXT函数不限于指定日期、时间的格式):TEXT 函數Excel-用公式更改日期格式(TEXT)

提取日期中的月份

=MONTH(A2)

将 A2 中的日期转为(提取)月份。

提取日期中的月份转为阿拉伯数字显示

=TEXT(B2,"[DBnum1]m月")

将 B2 单元格中 的十二月 转为(提取)月份数字 12。

计算时间差

计算两个日期天数差,间隔天数

=DATEDIF(A2,B2,"d")

A2 开始时间,B2 结束时间,两者间隔的天数

计算两个日期周数差,间隔几个星期

=DATEDIF(A2,B2,"d")/7

A2 开始时间,B2 结束时间,两者间隔的周数,因为一周是7天所以除以7

计算两个日期月份数差,间隔几个月

=DATEDIF(A2,B2,"m")

A2 开始时间,B2 结束时间,两者间隔的月份数

计算两个日期年份差,间隔几年

=DATEDIF(A2,B2,"y")

A2 开始时间,B2 结束时间,两者间隔的年份数

计算两个日期年月日差,间隔几年几个月几天

=DATEDIF(A2,B2,"y")&"年"
&DATEDIF(A2,B2,"ym")&"月"
&B2-DATE(YEAR(B2),MONTH(B2),1)&"天"

A2 开始时间,B2 结束时间,两者间隔的几年几个月几天。 如:2011-05-15 2021-11-10 计算结果为 10年5月9天。 便于阅读公式进行了换行,直接复制张贴到单元格即可,单元格的格式请选择常规。

計算兩個日期之間的差

日期加几年或者加1年加1个月加1天

=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))

A2 是原来要被加的时间,如果要加一个月在可以写成MONTH(A2)+1,加一天DAY(A2)+1+1中的1可以改成具体的单元格,做的灵活计算。

串联数据保持日期格式

合并多个单元格时间让日期不变成数字

 ABC
1开始日期结束日期名称
22021-12-122021-12-13双十二
=CONCATENATE(TEXT(A2,"yyyy-mm-dd")," ",C2)
=CONCATENATE(TEXT(A2,"yyyy-mm-dd")&" 至 "&CONCATENATE(TEXT(B2,"yyyy-mm-dd")

第一行的计算结果为:2021-12-12 双十二。 第二行的计算结果为:2021-12-12 至 2021-12-13

函数常见符号

常用符号说明
&合并
<>不等于
?通配符问号匹配单个字符
*通配符星号匹配任意字符
+?多个条件,或者?
-?多个条件,并且?

COUNTIF 函数

  • 统计函数,统计满足指定条件单元格的数量
  • COUNTIF 计算不区分字母大小写
函数说明
=COUNTIF(A2:A5,"男")统计 A2 到 A5 单元格中含“男”的单元格的数量
=COUNTIF(A2:A5,A4)统计 A2 到 A5 单元格中含 A4 中的值(男)的单元格的数量
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)计算单元格 A2 到 A5 中 A2 中的值(男)和 A3 中的值(女)的单元格的数量。 两次使用 COUNTIF 来指定多个条件,每个表达式一个条件。 也可以使用 COUNTIFS 函数。
=COUNTIF(B2:B5,">55")统计单元格 B2 到 B5 中值大于 55 的单元格的数量。
=COUNTIF(B2:B5,"<>"&B4)统计单元格 B2 到 B5 中值不等于 B4 中的值(90) 的单元格的数量。
=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")统计单元格 B2 到 B5 中值大于 (>) 或等于 (=) 32 且小于 (<) 或等于 (=) 85 的单元格的数量。
=COUNTIF(A2:A5,"*")统计单元格 A2 到 A5 中包含(任何)文本的单元格的数量。
=COUNTIF(A2:A5,"???le")统计单元格 A2 到 A5 为 5 个字符且以字母“le”结尾的单元格的数量,如匹配 apple。
=COUNTIFS(A1:A6,">-100",A1:A6,"<0.1")数据中计算大于-100并且小于0.1的个数
=COUNTIF(A2:A15, "SM *")以指定内容开头的内容计数,如SM-

数据透视表(明细按周、月、年汇总数据)

  1. 插入——数据透视表
  2. 选择分析数据的表或区域,选择放置透视表的位置
  3. 勾选字段,编排字段的行、列、值
  4. 完成数据透视表创建
  5. 按日期汇总:在日期列右键选择组合
  6. 组合面板设定步长,完成组合设定

其他

计算结果只显示最后2位或4位

指定只显示小数点后几位,默认会四舍五入

=ROUND("1.342423523432",2)
// 结果:1.34

=ROUND(11/3,4)
=ROUND(A1/B1,4)
// A1=11 B1=3
// 结果:3.6667

if (ISBLANK) 判断单元格为空时显示什么 不为空显示什么

=IF(ISBLANK(b2),"未上线","已发布")

C2 单元格输入公式,当 B2 单元格中没有内容时显示未上线,如果有内容(如日期)就显示已发布

修正 #N/A 错误

=IFERROR(FORMULA(),0)

使用 XLOOKUP、VLOOKUP、HLOOKUP、LOOKUP 或 MATCH 函數找不到指定值时就会出现 #N/A 错误。 FORMULA() 表示你原来的公式,替换它。 0表示查不到指定值时显示的内容,也可以写成 查不到 或者指定单元格的内容 A5

IFERROR 还能解决 #DIV/0#REF! 报错.

单元格命名

解决的问题:拉公式时定位的单元格会随着变动的问题;快速引用常用单元格;移动单元格不影响引用。

操作举例:点击B1单元格,窗口右上角有个【B1】的输入框(在函数输入栏左边),点击编辑内容后回车。不能使用简单字母开头+数字的命名,这会和单元格定位的命名冲突,会直接跳到对应单元格。

建议:书名100、book_name_100、name100.

固定或锁定公式中的单元格或范围

指定单元格或表的范围时加上$,在拉或者复制粘贴公式时就可以保持公式中的单元格或表范围不变。

比如原来是 A3 写成 $A$3后,再拉取公式或者粘贴公式就不会位移变成 A4A5

一键批量删除相同的图片

  1. 选择图片(如果图片带链接,点击会打开链接,可以右键选择);
  2. 【开始】选项卡最右边(排序和筛选边上)的【查找和选择】按钮,并选择【定位条件】;
  3. 在定位条件的操作面板选择【对象】,然后点【确定】
  4. Excel 就自动选择了所有的相同图片对象,按【Delete】件可以一键删除全部相同的图片。

Excel 下拉菜单、下拉清单、序列

  1. 设置好下拉菜单内容,如:状态、开启、禁用(放到一行或者一列)
  2. 点击要添加下拉菜单的单元格
  3. 顶部菜单选项卡:
    1. 数据
    2. 数据验证——
    3. (验证条件——允许)序列——
    4. (来源)选择单元格:开启、禁用的位置
    5. 确定

参考文档:https://udn.com/news/story/11017/5168287

对比2个单元格内容是否相同

# 方法1一般用法
=IF(A2=B2,"相同","不同")

# 方法2精确区分大小写用法
=IF(EXACT(A2,B2)=TRUE,"相同","不相同")

对比 A2 和 B2 的值是否相同,在C2计算结果。

然后可以给C列添加高亮条件,将不同的都高亮。

查询指定范围最大值和最小值

支持查询时间列中最新时间和最早时间

查询最大值

=MAX(A2:A55)

查询最小值

=MIN(A2:A55)

找到大于0的最小值,避免特殊字符

=MINIFS(B2:B100,B2:B100,">0")
  • 查询范围输入两次,然后设定取值大于0

计算单元格内容字符数

计算指定单元格

=LEN(A2)

直接计算指定内容

=LEN(text)

计算多个单元格的字符总数

=SUM(LEN(A2),LEN(A3),LEN(A4))

计算指定单元格中指定字符的总数

计算 A4 单元格中小写z的数量

=LEN(A4)-LEN(SUBSTITUTE(A4,"z","")) 

数据转为特殊文本格式

 ABCD
1转换前转换后公式说明
2123451.2万元=TEXT(A2,"0!.0,万元")专用公式,暂无找到设置小数点后两位的方法,小数点后会四舍五入
2123451.23万元=TEXT(A2/10000,"0.00"&"万元")简易通用公式转换,小数点后会四舍五入
20.3626336.26%=TEXT(A2,"0.00%") 
299+99=TEXT(A2,"+0;-0;0")正数面前增加一个+加号
20.2+20.00%=TEXT(A2,"+0.00%;-0.00%;0.00%")百分比正数增加加号
2    
2    

非公式或函数操作

高亮标记两列重复值

选择两列数据 菜单栏》开始》条件格式》突出显示单元格规则》重复值

提取一列重复内容中的唯一值

菜单栏》数据》(筛选)高级》

  • 选择“将筛选结果复制到其他位置”
  • 列表区域:原数据,很多重复值的单元格区域
  • 条件区域:可忽略
  • 复制到:筛选出来的数据填充到指定的位置
  • 勾选“不重复的记录”

点【确定】后,会将列表区域中重复数据的不重复内容提取出来

阅读记录

看过未尝试存档

Excel 相关学习站点