搜索
1常用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()2含义：快速输入当前日期及时间，并可随系统时间自动更新。方法：在选中单元格中输入“＝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)”，然后按下回车键，第一位学生的名次也就出来了。怎么，名次不对？不要紧，往3后看。对了，还要说一句，那就是上面公式中那个“$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)”得到4的结果就是“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四舍五入后6A2=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所在的单元格保留两位小数的话，5它仍会显示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组合键即可在选中单元格中看到计算结果。其中位于J26单元格中的结果表示年龄小于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不在工作表中，因此必须使用数组公式)。7求女生一(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的各个数值，以此类推。函数表达：8=IF(M150,E,IF(M140,D,IF(M130,C,IF(M120,B,IF(M115,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位身份证，只需对有关函数的取值位置进行适当调9整即可（如将“=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、DATED