常用Excel函数和快捷键详细说明.docx

常用Excel函数和快捷键详细说明.docx

《常用Excel函数和快捷键详细说明.docx》由会员分享，可在线阅读，更多相关《常用Excel函数和快捷键详细说明.docx（22页珍藏版）》请在冰豆网上搜索。

常用Excel函数和快捷键详细说明

常用Excel函数说明

1、自动编号

函数：

＝MAX（$A$1:

A1）+1

含义：

如果A1编号为1，那么A2＝MAX（$A$1:

A1）+1。

假设A1为1，那么在A2中输入“＝MAX（$A$1:

A1）+1”，回车。

然后用填充柄将公式复制到其他单元格即可。

2、统计某一区域某一数据出现的次数

函数：

＝COUNTIF（K4:

K60,”新开”）

含义：

统计K4到K60单元格中“新开”出现的次数。

假设在K61中输入“新开”，在K62中输入“＝COUNTIF（K4:

K60,”新开”）”后回车，即可统计出K4至K60单元格中“新开”出现的次数。

3、避免输入相同的数据

函数：

＝COUNTIF（A:

A,A2）=1

含义：

在选定的单元格区域中保证输入的数据的唯一性。

假设在A列中避免输入重复的姓名，先选中相应的单元格区域，如A2至A500，依次选中“数据-有效性-设置”，在“允许”下拉栏中选“自定义”，在“公式”栏中输入“＝COUNTIF（A:

A,A2）=1”。

单击“确定”完成。

4、在单元格中快速输入当前日期

函数：

＝today（）

含义：

快速输入当前日期，并可随系统时间自动更新。

方法：

在选中单元格中输入“＝today（）”回车，即可输入当前日期。

5、在单元格中快速输入当前日期及时间

函数：

＝now（）

含义：

快速输入当前日期及时间，并可随系统时间自动更新。

方法：

在选中单元格中输入“＝now（）”回车，即可输入当前日期及时间。

6、缩减小数点后的位数

函数：

＝TRUNC（A1,1）

含义：

将两位小数点变为一位小数点。

方法：

例如，A1的数值为“36.99”，需要在B1中显示A1数值小数点后的一位，则在B1中直接输入函数表达式“=TRUNC（A1,1）”，而后将在B1中显示“36.9”。

7、将数据合二为一

函数：

＝CONCATENATE（A1,B1）

含义：

将Excel中的两列数据合并至一列中。

方法：

如果要将Excel中的两列数据合并至一列中，可以使用文本合并函数。

例如，A1中的数值为“1”，B1中的数值为“2”，在C1中输入函数表达式“=CONCATENATE（A1,B1）”，而后在C1中就会显示合并后的数值“12”。

8、为成绩排名次

函数：

RANK:

返回某数字在一列数字中相对于其他数值的大小排名。

＝RANK（F2,$F$2:

$F$7）

含义：

利用给出的总成绩自动排出名次。

方法：

1.在“总分”列的F2单元格输入“=SUM（C2:

E2）”，然后按下回车键，第一个学生的总分就会计算出来了。

2.在“名次”列的“G2”单元格输入“=RANK（F2,$F$2:

$F$7）”，然后按下回车键，第一位学生的名次也就出来了。

怎么，名次不对？

不要紧，往后看。

对了，还要说一句，那就是上面公式中那个“$F$7”是“总分”栏中的最后一个数据的位置，实际中可以根据表格中的实际情况来填写。

3.选中F2和G2单元格，然后拖动填充柄向下至最后一个单元格，看到了吗？

所有人的总分和名次都在瞬间完成了（如图2）。

而且更妙的是，按照这种方法，如果总分相同，名次也是相同的。

数据越多，这种方法就越能体现出它的优势。

9、根据出生年月日快速算出人的年龄

函数：

＝DATEDIF（D3,"2004-5-31","y"）

含义：

根据出生日期快速算出到某一日期的实足年龄

方法：

在“出生年月”的右面插入一列，在E3单元格里输入公式“=DATEDIF（D3,"2004-5-31","y"）”，回车。

然后利用填充柄将公式复制到其他单元格即可算出其他人的实足年龄。

10、设定文本长度限制

函数：

＝OR（LEN（C2）=15,LEN（C2）=18）

含义：

在C2单元格中输入的数值只能是15位或者是18位（如身份证号）。

方法：

选定需要的单元格区域，依次选择（数据－有效性－自定义，在公式栏中输入：

＝OR（LEN（C2）=15,LEN（C2）=18）,回车。

11、隔列求和

函数：

=SUM（（C2:

L2）*（MOD（COLUMN（C2:

L2）,2）=1））

然后可千万不要回车，而是按下“Ctrl+Shift+Enter”快捷键，在公式最外层添加数组公式标记（一对大括号，记住，这个标记可不能手工输入），简单地解释一下：

Column函数可以得到所在列的列数，比如公式“=COLUMN（C2）”得到的结果就是“3”。

而MOD函数是取余数的，比如“=MOD（9,2）”就是计算9除以2所得到的余数。

按下“Ctrl+Shift+Enter”快捷键是为了得到数组公式。

上面的公式其实最终的计算方式是：

C2:

L2区域中的每一单元格逐个与当前单元格所在列的列数除以2所得的余数相乘，并将所得的结果累加。

当列数为偶数时，余数为0，所以累加时就不会计算在内了。

因此可以看出，正因为我们要累加的都是奇数列，所以公式中我们使用了“MOD（COLUMN（C2:

L2）,2）=1”。

假如需累加数据都位于偶数列，那就应该改为“MOD（COLUMN（C2:

L2）,2）=0”了。

11、D3=round（A3*0.08,1）函数的应用

含义：

按指定的位数对数值进行四舍五入。

例：

A1=555,B1=33,C1=（A1+B1）*0.01=5.88四舍五入后6

A2=222,B2=88,C2=（A2+B2）*0.05=15.5四舍五入后16

结果C1+C2=21为什么不是22呢?

问题补充：

还有种情况就是A3=99,D3=A3*0.08=7.92用公式好像不能四舍五入

可见，你虽然对单元格数值进行了四舍五入的处理，但只对表面的显示结果有影响，而在excel看来，它还是原始的数值。

同理，21也只是excel应你的要求而显示的表面结果，如果你让"=c1+c2"所在的单元格保留两位小数的话，它仍会显示21.38。

将"=c1+c2"所在的单元格修改"=round（c1,0）+round（c2,0）"可显示22。

第二个问题也是一样的，你可以在D3中输入“=round（a3*0.08,0）”（0表示你要保留的小数位数是零位，即取整数；如果保留一位就是1；保留两位就是2喽。

）

12、某一列中各年龄段人数的统计。

（1）函数分解

　　FREQUENCY函数以一列垂直数组返回某个区域中数据的频率分布。

　　语法:

FREQUENCY（data_array,bins_array）

　　Data_array为一数组或对一组数值的引用，用来计算频率。

如果data_array中不包含任何数值，函数FREQUENCY返回零数组；Bins_array为间隔的数组或对间隔的引用，该间隔用于对data_array中的数值进行分组。

如果bins_array中不包含任何数值，函数FREQUENCY返回data_array中元素的个数。

（2）实例分析

　　首先在工作表中找到空白的I列（或其他列），自I2单元格开始依次输入20、25、30、35、40...60，分别表示统计年龄小于20、20至25之间、25至30之间等的人数。

然后在该列旁边选中相同个数的单元格，例如J2:

J10准备存放各年龄段的统计结果。

然后在编辑栏输入公式“=FREQUENCY（A2:

A600）,I2:

I10）”，按下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结果。

其中位于J2单元格中的结果表示年龄小于20岁的职工人数，J3单元格中的数值表示年龄在20至25之间的职工人数等。

13、用数组公式求总分

　　某小学部分学生语文E列、数学F列成绩的统计表，在“总分”列，我们可以用简单求和再填充的方式取得每个同学的总分，但这里我们特意用数组公式来做一下：

　　首先，选中“总分”列的单元格范围（G2:

G20），然后在公式栏中输入公式“=E2:

E20+F2:

F20”，并按Ctrl+Shift+Enter组合，可以看到，G2:

G29范围内所有的“总分”一次性求出！

14、用SUM函数求特定范围的人数

　　1、计算出两个班的男女生人数。

单元格B22中的公式为：

=SUM（（A2:

A20="一

（1）班"）*（D2:

D20="男"）），再按Ctrl＋Shift＋Enter键。

这个数组公式创建了一个条件求和，若在A2:

A20中出现值“一

（1）班”，则返回一个逻辑值“true”，值为“1”，若D2:

D20中出现值“男”，也返回一个逻辑值“true”，值为“1”，则数组公式将与其相对应的值相乘并累加，若是1*1=1,则加1，若是其他就返回1*0=0或是0*1=0,则累加零。

（虽然数组A2:

A20和D2:

D20均在工作表中，但其相乘的数组A2:

A20和D2:

D20不在工作表中，因此必须使用数组公式）。

　　求女生一

（1）班的女生人数也是一样，把公式改为：

=SUM（（A2:

A20="一

（1）班"）*（D2:

D20="女"）），当然，如果求一

（2）班或是其它班级的男女生数也是一样的道理，请大家自己领会。

　　2、求及格（60分以上）人数或是优秀（90分以上）人数的时候，单科的往往比较简单，用一个简单的公式就行了。

但是要求双科的及格人数或是双科的优秀人数，就要用数组函数才能做到了。

同样以上面的工作表为例，计算出语文、数学双科的优秀人数。

　　在B25中输入数组公式：

=SUM（（E2:

E20>=90）*（F2:

F20>=90）），再按Ctrl＋Shift＋Enter键。

在这个公式中，若是E2:

E20和F2:

F20中的两个值都满足>=90（大于或等于90），则返回一个值1*1=1，sum就累加1，如果两列中有一个不能满足>=90（大于或等于90），则返回1*0=0或是0*1=0，就累加0。

当然，这是两个班的双科优秀人数，如果只求一个班的，则要再加一个条件，使数组公式改为：

=SUM（（E2:

E20>=80）*（F2:

F20>=80）*（A2:

A20="一

（1）班"）），求一

（2）班的则要把后面的“一

（1）班”改为“一

（2）班”，其它的情况下大家应该能灵活运用。

求双科及格人数的函数大家也照着自己做一下吧。

实例：

=SUM（（B1:

B89=“男”）*（E1:

E89=“场部”））

15、把数值分成段，如16-20、21-30、31-40、41-50、51-60等，并以字母A代表16-20的各个数值，以B代表21-30的各个数值，以此类推。

函数表达：

=IF（M1>50,"E",IF（M1>40,"D",IF（M1>30,"C",IF（M1>20,"B",IF（M1>15,"A"）））））

16、运用MID函数从身份证号码中计算出生年月日、性别和年龄

MID函数作用是返回文本串中从指定位置开始特定数目的字符，该数目由用户指定（另有一个名为MIDB的函数，其作用与MID完全一样，不过MID仅适用于单字节文字，而MIDB函数则可用于汉字等双字节字符），利用该功能我们就能从身份证号码中分别取出个人的出生年份、月份及日期，然后再加以适当的合并处理即可得出个人的出生年月日信息。

假设B列为身份证号码，将光标移至C3单元格中，然后输入“="19"&MID（B3,7,2）&"年"&MID（B3,9,2）&"月"&MID（B3,11,2）&"日"”内容。

其中MID（B3,7,2）就是从身份证号码的第7位开始取2位数，得出该员工的出生年份，MID（B3,9,2）就是得出该员工的出生月份，而MID（B3,11,2）则是该员工的出生日期，这些信息再加上年、月、日等文字就会组成该员工的准确出生年月日“1970年1月1日”。

接下来我们应将光标移至D3单元格中，然后输入“=IF（MID（B3,15,1）/2=TRUNC（MID（B3,15,1）/2）,"女","男"）”。

这就表示取身份证号码的第15位数，若能被2整除，这表明该员工为女性，否则为男性。

再接下来我们将光标移至E3单元格中，然后输入“=DATEDIF（C3,"2009年1月8日","y"）”。

该员工的十足年龄即得出。

最后我们只需利用自动填充功能对其他各个员工的出生年月日、性别、年龄进行填充即可。

上面都是以15位身份证为例进行介绍的，18位身份证的操作方法与此类似，广大用户若使用的是18位身份证，只需对有关函数的取值位置进行适当调整即可（如将“="19"&MID（B3,7,2）&"年"&MID（B3,9,2）&"月"&MID（B3,11,2）&"日"”修改为“=MID（B3,7,4）&"年"&MID（B3,11,2）&"月"&MID（B3,13,2）&"日"”）。

17、DATEDIF

计算两个日期之间的天数、月数或年数。

提供此函数是为了与Lotus1-2-3兼容。

语法

DATEDIF（start_date,end_date,unit）

Start_date为一个日期，它代表时间段内的第一个日期或起始日期。

日期有多种输入方法：

带引号的文本串（例如"2001/1/30"）、系列数（例如，如果使用1900日期系统则36921代表2001年1月30日）或其他公式或函数的结果（例如，DATEVALUE（"2001/1/30"））。

有关日期系列数的详细信息，请参阅NOW。

End_date为一个日期，它代表时间段内的最后一个日期或结束日期。

Unit为所需信息的返回类型。

Unit返回

"Y"时间段中的整年数。

"M"时间段中的整月数。

"D"时间段中的天数。

"YM"start_date与end_date日期中月数的差。

忽略日期中的日和年。

"YD"start_date与end_date日期中天数的差。

忽略日期中的年。

"MD"start_date与end_date日期中天数的差。

忽略日期中的月和年。

说明

MicrosoftExcel按顺序的系列数保存日期，这样就可以对其进行计算。

如果工作簿使用1900日期系统，则Excel会将1900年1月1日保存为系列数1。

而如果工作簿使用1904日期系统，则Excel会将1904年1月1日保存为系列数0，（而将1904年1月2日保存为系列数1）。

例如，在1900日期系统中Excel将1998年1月1日保存为系列数35796，因为该日期距离1900年1月1日为35795天。

请查阅MicrosoftExcel如何存储日期和时间。

ExcelforWindows和ExcelforMacintosh使用不同的默认日期系统。

有关详细信息，请参阅NOW。

示例

DATEDIF（"2001/1/1","2003/1/1","Y"）等于2，即时间段中有两个整年。

DATEDIF（"2001/6/1","2002/8/15","D"）等于440，即在2001年6月1日和2002年8月15日之间有440天。

DATEDIF（"2001/6/1","2002/8/15","YD"）等于75，即在6月1日与8月15日之间有75天，忽略日期中的年。

DATEDIF（"2001/6/1","2002/8/15","MD"）等于14，即开始日期1和结束日期15之间的差，忽略日期中的年和月。

特别提醒：

这是Excel中的一个隐藏函数，提供此函数是为了与Lotus1-2-3兼容。

在函数向导和在Excel2000版本以上的帮助里面找不到的（但在Excel2000的帮助里面有），可以直接输入使用，对于计算年龄、工龄等非常有效。

18、=TEXT（A1,”0000-00-00”）

含义：

把日期格式19990101转换为1999-01-01。

例如：

A1=19990101，先把B1单元格设置为日期格式，如0000-00-00。

然后再B1单元格中输入：

=TEXT（A1,”0000-00-00”），回车，即可得到1999-01-01。

19、[Excel]如何批量转换日期格式

　　在Excel中输入职工出生时间时，为了简单都输入成“yymmdd”形式，但上级部门一律要求输入成“yyyy-mm-dd”格式，那么一千多名职工出生时间肯定不能每个手工转化。

最快速的方法是：

先选定要转化的区域。

点击“数据→分列”，出现“文本分列向导”对话框。

勾选“固定宽度”，连续两次点击“下一步”按钮，在步骤三对话框的“列数据格式”中，选择“日期”，并选定“YMD”形式，按下“完成”按钮，以前的文本即转化成了需要的日期了。

单元格格式设置——

选中数据单元格区域；

执行“格式/单元格/数字/分类/自定义”操作，在右边“类型”栏中将“G/通用格式”修改为“0000"."00"."00”（不含外引号）后“确定”。

操作与效果——

直接输入数字如20060315，显示为2006.03.15

20、有出生日期和死亡日期excel2007怎样批量计算死亡年龄？

设A1为出生日期，B1为去世日期，在C1中输入下列公式：

=DATEDIF（A1,B1,"y"）

计算结果为周岁，可随意向下填充。

Excel快捷键说明

EXCEL快捷键（集合）

Excel  快  捷  键

一、Ctrl组合快捷键

【Ctrl】+【（】    取消隐藏选定范围内所有隐藏的行。

【Ctrl】+【）】    取消隐藏选定范围内所有隐藏的列。

【Ctrl】+【&】    将外框应用于选定单元格。

【Ctrl】+【_】    从选定单元格删除外框。

【Ctrl】+【~】    应用“常规”数字格式。

【Ctrl】+【$】    应用带有两位小数的“货币”格式（负数放在括号中）。

【Ctrl】+【%】    应用不带小数位的“百分比”格式。

【Ctrl】+【^】    应用带有两位小数的“指数”格式。

【Ctrl】+【#】    应用带有日、月和年的“日期”格式。

【Ctrl】+【@】    应用带有小时和分钟以及AM或PM的“时间”格式。

【Ctrl】+【!

】    应用带有两位小数、千位分隔符和减号（-）（用于负值）的“数值”格式。

【Ctrl】+【-】    显示用于删除选定单元格的【删除】对话框。

【Ctrl】+【*】    选择环绕活动单元格的当前区域（由空白行和空白列围起的数据区域）。

在数据透视表中，它将选择整个数据透视表。

【Ctrl】+【:

】    输入当前时间。

【Ctrl】+【;】    输入当前日期。

【Ctrl】+【`】    在工作表中切换显示单元格值和公式。

【Ctrl】+【'】    将公式从活动单元格上方的单元格复制到单元格或编辑栏中。

【Ctrl】+【"】    将值从活动单元格上方的单元格复制到单元格或编辑栏中。

【Ctrl】+【+】    显示用于插入空白单元格的【插入】对话框。

【Ctrl】+【1】    显示【单元格格式】对话框。

【Ctrl】+【2】    应用或取消加粗格式设置。

【Ctrl】+【3】    应用或取消倾斜格式设置。

【Ctrl】+【4】    应用或取消下划线。

【Ctrl】+【5】    应用或取消删除线。

【Ctrl】+【6】    在隐藏对象、显示对象和显示对象占位符之间切换。

【Ctrl】+【7】    显示或隐藏【常用】工具栏。

【Ctrl】+【8】    显示或隐藏大纲符号。

【Ctrl】+【9】    隐藏选定的行。

【Ctrl】+【0】    隐藏选定的列。

【Ctrl】+【A】    选择整个工作表。

如果工作表包含数据，则按【Ctrl】+【A】将选择当前区域。

再次按【Ctrl】+【A】将选择整个工作表。

当插入点位于公式中某个函数名称的右边时，则会显示“函数参数”对话框。

当插入点位于公式中某个函数名称的右边时，按【Ctrl】+【Shift】+【A】将会插入参数名称和括号。

【Ctrl】+【B】    应用或取消加粗格式设置。

【Ctrl】+【C】    复制选定的单元格。

如果连续按两次【Ctrl】+【C】，则会显示【MicrosoftOffice剪贴板】。

【Ctrl】+【D】    使用【向下填充】命令将选定范围内最顶层单元格的内容和格式复制到下面的单元格中。

【Ctrl】+【F】    显示【查找】对话框。

按【Shift】+【F5】也会显示此对话框，而按【Shift】+【F4】则会重复上一次查找操作。

【Ctrl】+【G】    显示【定位】对话框。

按【F5】也会显示此对话框。

【Ctrl】+【H】    显示【查找和替换】对话框。

【Ctrl】+【I】    应用或取消倾斜格式设置。

【Ctrl】+【K】    为新的超链接显示【插入超链接】对话框，或为选定的现有超链接显示【编辑超链接】对话框。

【Ctrl】+【L】    显示【创建列表】对话框。

【Ctrl】+【N】    创建一个新的空白文件。

【Ctrl】+【O】    显示【打开】对话框以打开或查找文件。

按【Ctrl】+【Shift】+【O】可选择所有包含批注的单元格。

【Ctrl】+【P】    显示【打印】对话框。

【Ctrl】+【R】    使用“向右填充”命令将选定范围最左边单元格的内容和格式复制到右边的单元格中。

【Ctrl】+【S】    使用其当前文件名、位置和文件格式保存活动文件。

【Ctrl】+【U】    应用或取消下划线。

【Ctrl】+【V】    在插入点处插入剪贴板的内容，并替换任何选定内容。

只有在剪切或复制了对象、文本或单元格内容后，才能使用此快捷键。

【Ctrl】+【W】    关闭选定的工作簿窗口。

【Ctrl】+【X】    剪切选定的单元格。

【Ctrl】+【Y】    重复上一个命令或操作（如有可能）。

【Ctrl】+【Z】    使用【撤消】命令来撤消上一个命令或删除最后键入的条目。

显示了自动更正智能标记时，按【Ctrl】+【Shift】+【Z】可使用【撤消】或【重复】命令撤消或恢复上一次自动更正操作。

二、功能键

【F1】    显示“帮助”任务窗格。

按【Ctrl】+【F1】可关闭并重新打开当前任务窗格。

按【Alt】+【F1】可创建当前范围中数据的图表。

按【Alt】+【Shift】+【F1】可插入新的工作表。

【F2】    编辑活动单元格并将插入点放在单元格内容的结尾。

如果禁止在单元格中进行编辑，它也会将插入点移到编辑栏中。

按【Shift】+【F2】可编辑单元格批注。

【F3】    将定义的名称粘贴到公式中。

按【Shift】+【F3】将显示【插入函数】对话框。

【F4】    重复上一个命令或操作（如有可能）。

按【Ctrl】+【F4】可关闭选定的工作簿窗口。

【F5】    显示【定位】对话框。

按【Ctrl】+【F5】可恢复选定工作簿窗口的窗口大小。

【F6】    切换到已拆分（【窗口】菜单，【拆分】命令）的工作表中的下一个窗格。

按【Shift】+【