excel实用快捷键与常用函数公式大全.docx
- 文档编号:24313352
- 上传时间:2023-05-26
- 格式:DOCX
- 页数:34
- 大小:2.47MB
excel实用快捷键与常用函数公式大全.docx
《excel实用快捷键与常用函数公式大全.docx》由会员分享,可在线阅读,更多相关《excel实用快捷键与常用函数公式大全.docx(34页珍藏版)》请在冰豆网上搜索。
excel实用快捷键与常用函数公式大全
整理EXCEL函数中,最常见的公式大全,一看就会,看完就用。
一、数字处理
01.取绝对值
=ABS(数字)
02.数字取整
=INT(数字)
03.数字四舍五入
=ROUND(数字,小数位数)
二、判断公式
04.把公式返回的错误值显示为空
公式:
C2
=IFERROR(A2/B2,"")
说明:
如果是错误值则显示为空,否则正常显示。
05.IF的多条件判断
公式:
C2
=IF(AND(A2<500,B2="未到期"),"补款","")
说明:
两个条件同时成立用AND,任一个成立用OR函数。
三、统计公式
06.统计两表重复
公式:
B2
=COUNTIF(Sheet15!
A:
A,A2)
说明:
如果返回值大于0说明在另一个表中存在,0则不存在。
07.统计年龄在30~40之间的员工个数
=FREQUENCY(D2:
D8,{40,29})
08.统计不重复的总人数
公式:
C2
=SUMPRODUCT(1/COUNTIF(A2:
A8,A2:
A8))
说明:
用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。
09.按多条件统计平均值
F2公式
=AVERAGEIFS(D:
D,B:
B,"财务",C:
C,"大专")
10.中国式排名公式
=SUMPRODUCT(($D$4:
$D$9>=D4)*(1/COUNTIF(D$4:
D$9,D$4:
D$9)))
四、求和公式
11.隔列求和
公式:
H3
=SUMIF($A$2:
$G$2,H$2,A3:
G3)
或
=SUMPRODUCT((MOD(COLUMN(B3:
G3),2)=0)*B3:
G3)
说明:
如果标题行没有规则用第2个公式
12.单条件求和
公式:
F2
=SUMIF(A:
A,E2,C:
C)
说明:
SUMIF函数的基本用法
13.单条件模糊求和
公式:
详见下图
说明:
如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。
14.多条求模糊求和
公式:
C11
=SUMIFS(C2:
C7,A2:
A7,A11&"*",B2:
B7,B11)
说明:
在sumifs中可以使用通配符*
15.多表相同位置求和
公式:
b2
=SUM(Sheet1:
Sheet19!
B2)
说明:
在表中间删除或添加表后,公式结果会自动更新。
16.按日期和产品求和
公式:
F2
=SUMPRODUCT((MONTH($A$2:
$A$25)=F$1)*($B$2:
$B$25=$E2)*$C$2:
$C$25)
说明:
SUMPRODUCT可以完成多条件求和
五、查找与引用公式
17.单条件查找
公式1:
C11
=VLOOKUP(B11,B3:
F7,4,FALSE)
说明:
查找是VLOOKUP最擅长的,基本用法
18.双向查找
公式:
=INDEX(C3:
H7,MATCH(B10,B3:
B7,0),MATCH(C10,C2:
H2,0))
说明:
利用MATCH函数查找位置,用INDEX函数取值
19.查找最后一个符合条件记录
公式:
详见下图
说明:
0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值
20.多条件查找
公式:
详见下图
说明:
公式原理同上一个公式
21.指定非空区域最后一个值查找
公式;详见下图
说明:
略
22.区间取值
公式:
详见下图
公式说明:
VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。
六、字符串处理公式
23.多单元格字符合并
公式:
c2
=PHONETIC(A2:
A7)
说明:
Phonetic函数只能对字符型内容合并,数字不可以。
24.截取除后3位之外的部分
公式:
=LEFT(D1,LEN(D1)-3)
说明:
LEN计算出总长度,LEFT从左边截总长度-3个
25.截取-之前的部分
公式:
B2
=Left(A1,FIND("-",A1)-1)
说明:
用FIND函数查找位置,用LEFT截取。
26.截取字符串中任一段
公式:
B1
=TRIM(MID(SUBSTITUTE($A1,"",REPT("",20)),20,20))
说明:
公式是利用强插N个空字符的方式进行截取
27.字符串查找
公式:
B2
=IF(COUNT(FIND("河南",A2))=0,"否","是")
说明:
FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功。
28.字符串查找一对多
公式:
B2
=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
说明:
设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果
七、日期计算公式
29.两日期间隔的年、月、日计算
A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。
计算:
相隔多少天?
=datedif(A1,B1,"d")结果:
557
相隔多少月?
=datedif(A1,B1,"m")结果:
18
相隔多少年?
=datedif(A1,B1,"Y")结果:
1
不考虑年相隔多少月?
=datedif(A1,B1,"Ym")结果:
6
不考虑年相隔多少天?
=datedif(A1,B1,"YD")结果:
192
不考虑年月相隔多少天?
=datedif(A1,B1,"MD")结果:
9
datedif函数第3个参数说明:
"Y"时间段中的整年数。
"M"时间段中的整月数。
"D"时间段中的天数。
"MD"天数的差。
忽略日期中的月和年。
"YM"月数的差。
忽略日期中的日和年。
"YD"天数的差。
忽略日期中的年。
30.扣除周末的工作日天数
公式:
C2
=NETWORKDAYS.INTL(IF(B2 说明: 返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。 周末和任何指定为假期的日期不被视为工作日 八、其他常用公式 31.创建工作表目录的公式 把所有的工作表名称列出来,然后自动添加超链接,管理工作表就非常方便了。 使用方法: 第1步: 在定义名称中输入公式: =MID(GET.WORKBOOK (1),FIND("]",GET.WORKBOOK (1))+1,99)&T(NOW()) 第2步、在工作表中输入公式并拖动,工作表列表和超链接已自动添加 =IFERROR(HYPERLINK("#'"&INDEX(Shname,ROW(A1))&"'! A1",INDEX(Shname,ROW(A1) 32.中英文互译公式 =FILTERXML(WEBSERVICE(" 1.那些神奇的快捷键Shortcuts 一次输入一串文字 若要在某个单元格范围内快速输入相同的文本或公式,请执行下列步骤: 选择要填充的单元格范围。 键入文本或公式,但是不要按Enter键,而是按Ctrl+Enter。 数据将显示在您所选择的范围中。 2.一键插入日期和时间 如果希望在单元格中快速输入当前日期,请按Ctrl+;,然后按Enter键。 要在单元格中快速输入当前时间,请按Ctrl+: ,然后按Enter键。 3.一键为上文的数值加和 求和的时候你是否有还是用函数? 其实,只要在要求和的区域同时按住“Alt”和“=”,就可以快速求和。 4.调整数字格式 使用Ctrl+Shift+数字键,可以将数值转换为所需要的格式: CRTL+SHIFT+`常规格式 CTRL+SHIFT+1整数 CTRL+SHIFT+20: 00AM/PM时间格式 CTRL+SHIFT+31-JAN-00日期格式 CTRL+SHIFT+4货币格式 CTRL+SHIFT+5百分比格式 CTRL+SHIFT+6科学记数格式 5.将文字变为图片,还能随时更新 你可以复制某个单元格范围,然后将结果图片粘贴到工作表中。 图片会与单元格范围相链接,并会根据内容更改和格式更改进行更新。 步骤如下: 选择单元格范围。 在“编辑”菜单上,单击“复制”。 选择要在其中显示图片的单元格。 按住Shift键,同时在“编辑”菜单上单击“粘贴图片链接”。 结果会得到一个快照,当源单元格发生更改或格式变化时,该快照会随之更新。 6.F4键 在Excel中,F4能发挥神奇的“重复上一步操作“功能。 什么意思呢? 比如我刚刚为某一段文本设置好字体、颜色、缩进、格式。 选择另一个表格,再按F4,就自动把刚刚设置的动作再重复一遍,应用到现在新选择; 还可以干什么? 做表格时候,“在下方添加新行”这样的命令,全部都可以用F4重复! 7.一键隐藏行、列 在Excel中,按下Ctrl+9,可以隐藏选定的行。 按下Ctrl+0,可以隐藏选定的列。 8.自动填充数据 您可以双击选定单元格的填充柄,沿某一列向下填充单元格内容,填充行数与相邻列的行数相同。 例如,如果在单元格A1: A16中键入了数据,在单元格B1中键入了一个公式或文本,按Enter键,然后双击填充柄(就是那个小小的黑点),则Excel将沿着该列,从单元格B1到单元格B16自上而下填充数据。 9.一键创建图表 要快速创建图表,请选择图表数据,然后按F11。 要创建新的工作表,请按Shift+F11。 10.双击分屏幕 双击Excel右上角的小横杠,轻松将页面分为两屏! 数据太多时同时看前面的内容,完全无压力。 分屏后再双击分隔线,即可回到单屏模式。 12.冻结行列 冻结行列: 或者由于数据太多,下拉后就不知道数据栏对应的项目是什么了,因此冻结第一列或第一行的很重要。 具体操作相当简单: 视图→冻结窗口,如下图: 13.其他Excel快捷键大全 请注意后面的英文单词。 CTRL+F查找find CTRL+H替换 CTRL+G定位/转到goto CTRL+Y重复上一次操作 CTRL+1设置单元格格式对话框 CTRL+B/CTRL+2加粗bold CTRL+I/CTRL+3斜体italic CTRL+U/CTRL+4下划线underline CTRL+SHIFT+7添加细实线外边框 CTRL+SHIFT+-清除区域内所有边框 …… 14.那些神奇的函数和公式Formulas 插入当前的日期和时间 想要知道当前的日期和时间,可以使用“Today”函数和“Now”函数。 当输入“=TODAY()”时,返回当前日期。 当输入“=NOW()”时,返回当前时间。 不管您何时打开工作薄,当需要在工作表上显示当前日期时,TODAY函数非常有用。 它还可用于计算时间间隔。 例如,如果您知道某人出生于1963年,您可使用以下公式计算对方到其今年生日为止的年龄: =YEAR(TODAY())-1963 此外,假如你想知道今天是本年度的第几周,也可以通过将Today函数与Weeknum函数一起来使用。 =WEEKNUM(TODAY()) 插入随机数字 有时我们需要在表格中插入一些随机的数字和字符。 这时可以使用RAND函数。 RAND函数返回大于等于0及小于1的均匀分布随机实数,其语法是: =RAND() 如果要生成指定范围内的随机数字,可以使用RANDBETWEEN函数。 RANDBETWEEN函数可以直接返回位于指定的两个数之间的一个随机整数,其语法是: RANDBETWEEN(bottom,top) 参数“bottom”为指定的最小整数,参数“top”为指定的最大整数。 如公式: =RANDBETWEEN(1,100) 返回1-100之间的随机整数,可能包含1或100。 或者你可以和领英小编一起来学学如何插入随机电话号码,比如138开头的吧: 将多个列中的文本联接起来 最简单而且最好用的一个“&”: 可以合并单元格。 或者你还可以使用&运算符或CONCATENATE函数,将多个列中的文本连缀或合并起来。 例如,如果在单元格A1: B14中键入以下数据。 要获得公司全名,请在单元格C1中,键入以下公式之一: =CONCATENATE(A1,"",B1) 或者 =A1&""&B1 注意: 单元格间的空格("")用于在显示文本间插入空格。 15.快速录入的技巧 比如要录入性别时,设置单元格格式-自定义: 指定数字为特定代表,比如1=男,2=女。 需注意的是,条件必须放在“[]”里。 如下图。 设置好之后,在录入数据时直接录入1和2,就会自动显示男和女了。 在Excel表格中加入斜线,这个可以有! 先设置单元格格式,加入斜线。 把国家和GDP放在两行,两行之间用Alt+enter强制换行。 先对单元格设置左对齐,将订购日期放在最左侧;然后在项目前加几个空格。 Vlookup大杀器! Excel中功能超级强大的函数有很多,但Vlookup必须是其中最强悍的那个。 Vlookup相当于数据工作的四六级测试,而且问得深一些很容易测出学习能力。 Vlookup函数和数据透视表,必熟练掌握的技能而很多人不会,这两招可提高效率不知多少倍。 但可惜太多的人还是用Ctrl+F的慢慢算… VLOOKUP是一个查找和引用函数。 例如,按员工号查找某位员工的姓氏,或通过查找员工的姓氏查找该员工的电话号码(就像使用电话簿)。 语法: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) 别着急,仔细看看这些参数都是什么概念: Vlookup可以用来在一个茫茫的数据源中,自动让Excel找出你要的某个数据的相关资料,并由此返回该值相对应当前行中指定列处的数值。 比如你要在这个表格中找到公司姓袁的员工,可以使用下面这个函数: =VLOOKUP("袁",B2: E7,2,FALSE) 学习函数本身很容易,但是放在应用场景就会有很多问题,很多时候你会碰到,为什么取不到数,为什么出错,为什么取数错误。 这些都能解决,你才算真正掌握Vlookup。 一些其他常用的函数 SUM和SUMIF: 求和/条件求和函数 AVERAGE和AVERAGEIF: 求均值/条件均值函数 COUNT和COUNTIF: 计量/条件计量函数 ROUND函数: 四舍五入 TRUNC函数: 取整 LEN函数: 求字符数 MODE函数: 出现频率最多值 FREQUENCY: 数据频率分布函数 IF函数: 按照条件返回相应的值 DATEDIF函数: 计算两个日期之间相隔的年/月/日。 公式为=DATEDIF(起始日期,结束日期,"返回值")返回值: y=年,m=月,d=天。 快捷键篇 1.那些神奇的快捷键Shortcuts 一次输入一串文字 若要在某个单元格范围内快速输入相同的文本或公式,请执行下列步骤: 选择要填充的单元格范围。 键入文本或公式,但是不要按Enter键,而是按Ctrl+Enter。 数据将显示在您所选择的范围中。 2.一键插入日期和时间 如果希望在单元格中快速输入当前日期,请按Ctrl+;,然后按Enter键。 要在单元格中快速输入当前时间,请按Ctrl+: ,然后按Enter键。 3.一键为上文的数值加和 求和的时候你是否有还是用函数? 其实,只要在要求和的区域同时按住“Alt”和“=”,就可以快速求和。 4.调整数字格式 使用Ctrl+Shift+数字键,可以将数值转换为所需要的格式: CRTL+SHIFT+`常规格式 CTRL+SHIFT+1整数 CTRL+SHIFT+20: 00AM/PM时间格式 CTRL+SHIFT+31-JAN-00日期格式 CTRL+SHIFT+4货币格式 CTRL+SHIFT+5百分比格式 CTRL+SHIFT+6科学记数格式 5.将文字变为图片,还能随时更新 你可以复制某个单元格范围,然后将结果图片粘贴到工作表中。 图片会与单元格范围相链接,并会根据内容更改和格式更改进行更新。 步骤如下: 选择单元格范围。 在“编辑”菜单上,单击“复制”。 选择要在其中显示图片的单元格。 按住Shift键,同时在“编辑”菜单上单击“粘贴图片链接”。 结果会得到一个快照,当源单元格发生更改或格式变化时,该快照会随之更新。 6.F4键 在Excel中,F4能发挥神奇的“重复上一步操作“功能。 什么意思呢? 比如我刚刚为某一段文本设置好字体、颜色、缩进、格式。 选择另一个表格,再按F4,就自动把刚刚设置的动作再重复一遍,应用到现在新选择; 还可以干什么? 做表格时候,“在下方添加新行”这样的命令,全部都可以用F4重复! 7.一键隐藏行、列 在Excel中,按下Ctrl+9,可以隐藏选定的行。 按下Ctrl+0,可以隐藏选定的列。 8.自动填充数据 您可以双击选定单元格的填充柄,沿某一列向下填充单元格内容,填充行数与相邻列的行数相同。 例如,如果在单元格A1: A16中键入了数据,在单元格B1中键入了一个公式或文本,按Enter键,然后双击填充柄(就是那个小小的黑点),则Excel将沿着该列,从单元格B1到单元格B16自上而下填充数据。 9.一键创建图表 要快速创建图表,请选择图表数据,然后按F11。 要创建新的工作表,请按Shift+F11。 10.双击分屏幕 双击Excel右上角的小横杠,轻松将页面分为两屏! 数据太多时同时看前面的内容,完全无压力。 分屏后再双击分隔线,即可回到单屏模式。 12.冻结行列 冻结行列: 或者由于数据太多,下拉后就不知道数据栏对应的项目是什么了,因此冻结第一列或第一行的很重要。 具体操作相当简单: 视图→冻结窗口,如下图: 13.其他Excel快捷键大全 请注意后面的英文单词。 CTRL+F查找find CTRL+H替换 CTRL+G定位/转到goto CTRL+Y重复上一次操作 CTRL+1设置单元格格式对话框 CTRL+B/CTRL+2加粗bold CTRL+I/CTRL+3斜体italic CTRL+U/CTRL+4下划线underline CTRL+SHIFT+7添加细实线外边框 CTRL+SHIFT+-清除区域内所有边框 …… 14.那些神奇的函数和公式Formulas 插入当前的日期和时间 想要知道当前的日期和时间,可以使用“Today”函数和“Now”函数。 当输入“=TODAY()”时,返回当前日期。 当输入“=NOW()”时,返回当前时间。 不管您何时打开工作薄,当需要在工作表上显示当前日期时,TODAY函数非常有用。 它还可用于计算时间间隔。 例如,如果您知道某人出生于1963年,您可使用以下公式计算对方到其今年生日为止的年龄: =YEAR(TODAY())-1963 此外,假如你想知道今天是本年度的第几周,也可以通过将Today函数与Weeknum函数一起来使用。 =WEEKNUM(TODAY()) 插入随机数字 有时我们需要在表格中插入一些随机的数字和字符。 这时可以使用RAND函数。 RAND函数返回大于等于0及小于1的均匀分布随机实数,其语法是: =RAND() 如果要生成指定范围内的随机数字,可以使用RANDBETWEEN函数。 RANDBETWEEN函数可以直接返回位于指定的两个数之间的一个随机整数,其语法是: RANDBETWEEN(bottom,top) 参数“bottom”为指定的最小整数,参数“top”为指定的最大整数。 如公式: =RANDBETWEEN(1,100) 返回1-100之间的随机整数,可能包含1或100。 或者你可以和领英小编一起来学学如何插入随机电话号码,比如138开头的吧: 将多个列中的文本联接起来 最简单而且最好用的一个“&”: 可以合并单元格。 或者你还可以使用&运算符或CONCATENATE函数,将多个列中的文本连缀或合并起来。 例如,如果在单元格A1: B14中键入以下数据。 要获得公司全名,请在单元格C1中,键入以下公式之一: =CONCATENATE(A1,"",B1) 或者 =A1&""&B1 注意: 单元格间的空格("")用于在显示文本间插入空格。 15.快速录入的技巧 比如要录入性别时,设置单元格格式-自定义: 指定数字为特定代表,比如1=男,2=女。 需注意的是,条件必须放在“[]”里。 如下图。 设置好之后,在录入数据时直接录入1和2,就会自动显示男和女了。 在Excel表格中加入斜线,这个可以有! 先设置单元格格式,加入斜线。 把国家和GDP放在两行,两行之
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 实用 快捷键 常用 函数 公式 大全