《常用Excel函数和快捷键说明》由会员分享，可在线阅读，更多相关《常用Excel函数和快捷键说明（30页珍藏版）》请在金锄头文库上搜索。
1、1常用 Excel 函数说明1、自动编号函数：MAX($A$1:A1)+1含义：如果 A1 编号为 1，那么 A2MAX($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、避免输入相同的数据函数：COU
2、NTIF(A:A,A2)=1含义：在选定的单元格区域中保证输入的数据的唯一性。假设在 A 列中避免输入重复的姓名，先选中相应的单元格区域，如 A2 至A500，依次选中“数据-有效性-设置” ，在 “允许”下拉栏中选“自定义” ，在“公式”栏中输入“COUNTIF(A:A,A2)=1” 。单击“确定”完成。4、在单元格中快速输入当前日期函数：today()含义：快速输入当前日期，并可随系统时间自动更新。方法：在选中单元格中输入“today()”回车，即可输入当前日期。5、在单元格中快速输入当前日期及时间函数：now()2含义：快速输入当前日期及时间，并可随系统时间自动更新。方法：在选中单元格中
3、输入“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 中输入函数表达式“=CONCA
4、TENATE(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)”，然后按下回车键，第一位学生的名次也就出来了。怎么，名次不对？不要紧，往3后看。对了，还要说一句，那就是上面公式中那个“$F$7”是“总分”栏中的最后一个数据的位置，实际
5、中可以根据表格中的实际情况来填写。3. 选中 F2 和 G2 单元格，然后拖动填充柄向下至最后一个单元格，看到了吗？所有人的总分和名次都在瞬间完成了（如图 2）。而且更妙的是，按照这种方法，如果总分相同，名次也是相同的。数据越多，这种方法就越能体现出它的优势。9、根据出生年月日快速算出人的年龄函数：DATEDIF(D3,2004-5-31,y)含义：根据出生日期快速算出到某一日期的实足年龄方法：在“出生年月”的右面插入一列，在 E3 单元格里输入公式“=DATEDIF(D3,2004-5-31,y)”，回车。然后利用填充柄将公式复制到其他单元格即可算出其他人的实足年龄。10、设定文本长度限制函
6、数：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”快捷键，在公式最外层添加数组公式标记（一对大括号，记住，这个标记可不能手工输入），简单地4解释一下：Column 函数可以得到所在列的列数，比如公式“=COLUMN(C2)”得到
7、的结果就是“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
8、*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 应你5的要求而显示的表面结果，如果你让=c1+c2所在的单元格保留两位小数的
9、话，它仍会显示 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 中不包含任何数值，函数 FR
10、EQUENCY 返回零数组；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 准备存放各年龄段的统计结果。然后在编辑6栏输入公式“=FREQUENCY(A2:A600),I2
11、:I10)”，按下 Ctrl+Shift+Enter 组合键即可在选中单元格中看到计算结果。其中位于 J2 单元格中的结果表示年龄小于 20 岁的职工人数，J3 单元格中的数值表示年龄在 20 至 25 之间的职工人数等。13、用数组公式求总分某小学部分学生语文 E 列、数学 F 列成绩的统计表，在“总分”列，我们可以用简单求和再填充的方式取得每个同学的总分，但这里我们特意用数组公式来做一下：首先，选中“总分”列的单元格范围（G2:G20），然后在公式栏中输入公式“=E2:E20+F2:F20”，并按 Ctrl+Shift+Enter 组合，可以看到，G2:G29 范围内所有的“总分”一次性求
12、出！14、用 SUM 函数求特定范围的人数1、计算出两个班的男女生人数。单元格 B22 中的公式为：=SUM(A2:A20=一(1)班)*(D2:D20=男)，再按CtrlShiftEnter 键。这个数组公式创建了一个条件求和，若在 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
13、和 D2:D20 不在工作表中，因此必须使用数组公式)。7求女生一(1)班的女生人数也是一样，把公式改为：=SUM(A2:A20=一(1)班)*(D2:D20=女)，当然，如果求一(2)班或是其它班级的男女生数也是一样的道理，请大家自己领会。2、求及格（60 分以上）人数或是优秀（90 分以上）人数的时候，单科的往往比较简单，用一个简单的公式就行了。但是要求双科的及格人数或是双科的优秀人数，就要用数组函数才能做到了。同样以上面的工作表为例，计算出语文、数学双科的优秀人数。在 B25 中输入数组公式：=SUM(E2:E20=90)*(F2:F20=90)，再按CtrlShiftEnter 键。在
14、这个公式中，若是 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、=“场部”）)15、把数值分成段，如 16-20、21-30、31-40、41-50、51-60 等，并以字母 A代表 16-20 的各个数值，以 B 代表 21-30 的各个数值，以此类推。函数表达：8=IF(M150,E,IF(M140,D,IF(M130,C,IF(M120,B,IF(M115,A)16、运用 MID 函数从身份证号码中计算出生年月日、性别和年龄MID 函数作用是返回文本串中从指定位置开始特定数目的字符，该数目由用户指定（另有一个名为 MIDB 的函数，其作用与 MID 完全一样，不过 MID仅适用于单字节文字，而 MIDB 函数则可用于汉字等双字节字符），利用该功能我们
16、就能从身份证号码中分别取出个人的出生年份、月份及日期，然后再加以适当的合并处理即可得出个人的出生年月日信息。 假设 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),女, 男)” 。这就表示取身份证号码的第
