1、1常用电子表格公式一、 利用函数 COUNTA统计本班的应考人数(总人数)因为函数 COUNTA可以计算出非空单元格的个数，所以我们在利用此函数时，选取本班学生名字所在单元格区域(B3B12)作为统计对象，就可计算出本班的应考人数(总人数)。二、利用 COUNT、COUNTBLANK 和 COUNTIF函数分别统计各科参加考试的人数、统计各科缺考人数、统计各科各分数段的人数我们在输入成绩时，一般情况下，缺考的人相应的科目的单元格为空就可以了，是 0分的都输入 0。1、 查找重复内容公式：=IF(COUNTIF(A:A,A2)1,“重复“,“)。2、用出生年月来计算年龄公式：=TRUNC(DAY
2、S360(H6,“2009/8/30“,FALSE)/360,0)。3从输入的 18位身份证号的出生年月计算公式：格式为 2012/4/21的=CONCATENATE(MID(E2,7,4),“/“,MID(E2,11,2),“/“,MID(E2,13,2)。格式为 20120421的=CONCATENATE(MID(E4,7,4),MID(E4,11,2),MID(E4,13,2)4、 从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,“男“,“女“),IF(MOD(MID(C2,17,1),2)=1,“
3、男“,“女“)公式内的“C2”代表的是输入身份证号码的单元格。1、求和： =SUM(K2:K56) 对 K2到 K56这一区域进行求和；2、平均数： =AVERAGE(K2:K56) 对 K2 K56这一区域求平均数；3、排名： =RANK(K2，K$2:K$56) 对 55名学生的成绩进行排名；或=RANK(E2,$E$2:$E$5)，但遇到同分数时排名不连续。用下面的几个公式都可以获得中国式排名，还是以上图为例，在单元格中输入公式并向下拖：=SUMPRODUCT(D$2:D$19D2)*(1/(COUNTIF(D$2:D$19,D$2:D$19)+1或下面的几个数组公式，公式输入完毕后按
4、Ctrl+Shift+Enter 结束：=SUM(IF(D$2:D$19D2,1/COUNTIF(D$2:D$19,D$2:D$19)+1=SUM(-(FREQUENCY(D$2:D$19,IF(D$2:D$19=D2,D$2:D$19)0)=SUM(-IF(D$2:D$19=D2,MATCH(D$2:D$19,D$2:D$19,)=ROW($2:$19)-1)4、等级： =IF(K2=85,“优“,IF(K2=74,“良“,IF(K2=60,“及格“,“不及格“)5、学期总评： =K2*0.3+M2*0.3+N2*0.4 假设 K列、M 列和 N列分别存放着学生的“平时总评”、“期中”、“期
5、末”三项成绩；6、最高分： =MAX(K2:K56) 求 K2到 K56区域（55 名学生）的最高分；7、最低分： =MIN(K2:K56) 求 K2到 K56区域（55 名学生）的最低分；8、分数段人数统计：（1） =COUNTIF(K2:K56,“100“) 求 K2到 K56区域 100分的人数；假设把结果存放于K57单元格；（2） =COUNTIF(K2:K56,“=95“)K57 求 K2到 K56区域 9599.5 分的人数；假设把结果存放于 K58单元格；（3）=COUNTIF(K2:K56,“=90“)SUM(K57:K58) 求 K2到 K56区域 9094.5 分的人数；假
6、设把结果存放于 K59单元格；（4）=COUNTIF(K2:K56,“=85“)SUM(K57:K59) 求 K2到 K56区域 8589.5 分的人数；假设把结果存放于 K60单元格；（5）=COUNTIF(K2:K56,“=70“)SUM(K57:K60) 求 K2到 K56区域 7084.5 分的人数；假设把结果存放于 K61单元格；（6）=COUNTIF(K2:K56,“=60“)SUM(K57:K61) 求 K2到 K56区域 6069.5 分的人数；假设把结果存放于 K62单元格；（7） =COUNTIF(K2:K56,“条件格式”，条件 1设为：公式 =A1=12、点“格式”-“
7、字体”-“颜色”，点击红色后点“确定”。条件 2设为：公式 =AND(A10,A1“字体”-“颜色”，点击绿色后点“确定”。条件 3设为：公式 =A1“字体”-“颜色”，点击黄色后点“确定”。4、三个条件设定好后，点“确定”即出。二、EXCEL 中如何控制每列数据的长度并避免重复录入1、用数据有效性定义数据长度。用鼠标选定你要输入的数据范围，点“数据“-“有效性“-“设置“，“有效性条件“设成“允许“文本长度“等于“5“（具体条件可根据你的需要改变）。还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点“确定“。2、用条件格式避免重复。选定 A列，点“格式“-“条件格式“，将
8、条件设成“公式=COUNTIF($A:$A,$A1)1”，点“格式“-“字体“-“颜色“，选定红色后点两次“确定“。这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。三、在 EXCEL中如何把 B列与 A列不同之处标识出来？（一）、如果是要求 A、B 两列的同一行数据相比较：假定第一行为表头，单击 A2单元格，点“格式”-“条件格式”，将条件设为:“单元格数值” “不等于”=B2点“格式”-“字体”-“颜色”，选中红色，点两次“确定”。用格式刷将 A2单元格的条件格式向下复制。B列可参照此方法设置。（二）、如果是 A列与 B列整体比较（即相同数据不在同一行）：假定第一行
9、为表头，单击 A2单元格，点“格式”-“条件格式”，将条件设为:“公式”=COUNTIF($B:$B,$A2)=0点“格式”-“字体”-“颜色”，选中红色，点两次“确定”。用格式刷将 A2单元格的条件格式向下复制。B列可参照此方法设置。按以上方法设置后，AB 列均有的数据不着色，A 列有 B列无或者 B列有 A列无的数据标记为红色字体。四、EXCEL 中怎样批量地处理按行排序假定有大量的数据(数值)，需要将每一行按从大到小排序，如何操作？由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以，这一问题不能用排序来解决。解决方法如下：1、假定你的数据在 A至 E列，
10、请在 F1单元格输入公式：=LARGE($A1:$E1,COLUMN(A1)3用填充柄将公式向右向下复制到相应范围。你原有数据将按行从大到小排序出现在 F至 J列。如有需要可用“选择性粘贴/数值”复制到其他地方。注：第 1步的公式可根据你的实际情况（数据范围）作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1)五、巧用函数组合进行多条件的计数统计例：第一行为表头，A 列是“姓名”，B 列是“班级”，C 列是“语文成绩”，D 列是“录取结果”，现在要统计“班级”为“二”，“语文成绩”大于等于 104，“录取结果”为“重本”的人数。统计结果存放在本工作表的
11、其他列。公式如下：=SUM(IF(B2:B9999=“二“)*(C2:C9999=104)*(D2:D9999=“重本“),1,0)输入完公式后按 Ctrl+Shift+Enter键,让它自动加上数组公式符号“。六、如何判断单元格里是否包含指定文本？假定对 A1单元格进行判断有无“指定文本“,以下任一公式均可:=IF(COUNTIF(A1,“*“二、在输入数字前先输入5、如果隐藏了 B列，如果让它显示出来？ 选中 A到 C列，点击右键，取消隐藏选中 A到 C列，双击选中任一列宽线或改变任一列宽将鼠标移到到 AC列之间，等鼠标变为双竖线时拖动之。6、EXCEL 中行列互换复制，选择性粘贴，选中转
12、置，确定即可7、Excel 是怎么加密的4（1）、保存时可以的另存为右上角的“工具“常规设置（2）、工具选项安全性8、关于 COUNTIFCOUNTIF 函数只能有一个条件，如大于 90，为=COUNTIF(A1:A10,“=90“)介于 80与 90之间需用减，为 =COUNTIF(A1:A10,“80“)-COUNTIF(A1:A10,“90“)9、根据身份证号提取出生日期（1）、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2),IF (LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(
13、A1,11,2),“错误身份证号“)（2）、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),“#-00-00“)*110、想在 SHEET2中完全引用 SHEET1输入的数据工作组，按住 Shift或 Ctrl键，同时选定 Sheet1、Sheet211、一列中不输入重复数字数据-有效性-自定义-公式输入=COUNTIF(A:A,A1)=1如果要查找重复输入的数字条件格式公式=COUNTIF(A:A,A5)1格式选红色12、直接打开一个电子表格文件的时候打不开“文件夹选项”-“文件类型”中找到.XLS 文件，并在“高级”中确认是否有参数 1%,如果没有，请手工加上13、exc
14、el 下拉菜单的实现数据-有效性-序列14、 10 列数据合计成一列=SUM(OFFSET($A$1,(ROW()-2)*10+1,10,1)15、查找数据公式两个(基本查找函数为 VLOOKUP,MATCH)（1）、根据符合行列两个条件查找对应结果=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)（2）、根据符合两列数据查找对应结果(为数组公式)=INDEX(C1：C7，MATCH(H1-0; 或 选项视图零值去勾。呵呵，如果用公式就要看情况了。 17、多个工作表的单元格合并计算 =Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(
15、Sheet1:Sheet3!D4) 1816、如何隐藏单元格中的 0单元格格式自定义 0;-0; 或 选项视图零值去勾。呵呵，如果用公式就要看情况了。17、多个工作表的单元格合并计算=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)18、获得工作表名称（1）、定义名称：Name=GET.DOCUMENT(88)（2）、定义名称：Path=GET.DOCUMENT(2)（3）、在 A1中输入=CELL(“filename“)得到路径级文件名在需要得到文件名的单元格输入=MID(A1,FIND(“*“,SUBSTITUTE(A1,“,“
16、*“,LEN(A1)-LEN(SUBSTITUTE(A1,“,“)+1,LEN(A1)（4）、自定义函数Public Function name()Dim filename As Stringfilename = ActiveWorkbook.namename = filenameEnd Function19、如何获取一个月的最大天数5:“=DAY(DATE(2002,3,1)-1)“或“=DAY(B1-1)“,B1 为“2001-03-01数据区包含某一字符的项的总和,该用什么公式=sumif(a:a,“*“2)最小两个：=small(data,1;2)怎样很简单的判断最后一位是字母righ
17、t(a1)*1出错的字母=IF(ISNUMBER(-RIGHT(A1,1),“数字“,“字母“)=IF(ISERR(RIGHT(A1)*1),“字母“,“数字“)如何 设置单元格，令其不接受包含空格的字符选定 A列数据有效性自定义公式=iserror(find(“ “,a1)数据-有效性-自定义-公式=len(a1)=len(trim(a1)原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.现在是=sum(n(offset(a1,(row(1:10)-1)*3,)在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中
18、的数据,请问在公式里怎样引用?=INDIRECT(“A1“1)=2)如果 90 分算优秀，计算两门都优秀人数，公式略修改，=SUM(-(MMULT(-(A2:B50=90),1;1)=2)求三科均及格人数=SUM(-(MMULT(-(A1:C8=60),1;1;1)=3)用 offset函数三维引用，好处是不用辅助列，直接得结果 =SUMPRODUCT(-(COUNTIF(OFFSET(B2:F2,ROW(B3:F12)-2,0),“60“)=3) 就能求出三科均及格人数=SUMPRODUCT($B$2:$B$58=90)*($C$2:$C$58=90)*($D$2:$D$58=90)/COU
19、NTA($A$2:$A$58)语文数学及格人数：=SUMPRODUCT(A2:A10=60)*(B2:B10=60)语文合格率:（数组公式） =TEXT(SUM(N(A2:A10=60)/COUNTA(A2:A10),“0.00%“)数学合格率同上。不用函数筛选也是很容易的1.选中你要筛选（处理）的数据（会选择吧！用鼠标选中单元格的第一行的第一列按住鼠标左键不放托到最后一行的最后一列）2.松开鼠标，点击菜单栏的数据 -筛选-自动筛选3.这时候，你会看到每一列的第一行出现了倒三角4.点击倒三角，选择自定义 出现自定义自动筛选方式5.如果你想看及格率，可以在第一个文本框选择大于或等于 再后一个文本
20、框输入数据，如 60 分 这时，屏幕上只显示大于或等于 60 分得数据6.同样的道理，可以筛选其他列双科及格人数 =SUMPRODUCT(B2:B10=60)*(C2:C10=60) 以=60 为及格双科及格率 =C12/COUNTA(A2:A10) 补充：要百分比可采用以下方法：1. 设置单元格为百分比2. 公式改为：=TEXT(C12/COUNTA(A2:A10),“0.00%“) 0.00 即保留两位小数如何让 excel 表格中的公式不被删除 设置如下：1.选中全部工作表，设置单元格格式保护，将锁定的对勾去掉2.选中需要保护的行或列，设置单元格格式保护，选中锁定3.选 工具保护保护工作
21、表即可用， “工具-保护”保护工作表，配合右键 -单元格格式-保护（锁定、隐藏）这两种功能，就能实现有的单元格可以修改，有的不能修改，有的显示公式，有的不显示公式，这样的功能。36、工作表保护的口令忘记了怎么办？如果你想使用一个保护了的工作表，但口令又忘记了，有办法吗？有。选定工作表，9选择“编辑” “复制” 、 “粘贴” ，将其拷贝到一个新的工作薄中（注意：一定要是新工作簿） ，即可超越工作表保护。当然，提醒你最好不用这种方法盗用他人的工作表。37、 “$”的功用Excel 一般使用相对地址来引用单元格的位置，当把一个含有单元格地址的公式拷贝到一个新的位置，公式中的单元格地址会随着改变。你可
22、以在列号或行号前添加符号 “$”来冻结单元格地址，使之在拷贝时保持固定不变。在电子表格中怎样将日期格式进行转换 如何在电子表格中将 19901220 转换成 1990-12-20 的格式？ 如果日期所在单元格在 A 列，并从 A1 开始。插入一辅助列 B，在 B1 输入：=MID(A1,1,4)&-MID(A1,5,2)&-MID(A1,7,2)回车，向下填充该公式。选中 B 列复制粘贴选择性粘贴 数值确定删除 A 列求教：电子表格上的日期格式如 2010-10-5 替换成 20101005 的式样，怎样操作？ 全选要进行替换的所有单元格,或者直接点列号字母,鼠标右键-设置单元格格式-数字-自
23、定义-清除“ 类型“ 里面的内容后输入 “yyyymmdd“-确定在电子表格中进行日期转换怎么弄？ 如：把 2010-01-25 转换成 2010 年 01 月 25 日选中该单元格（整列的话就选整列),右键设置单元格式格式，在弹出的单元格格式窗口中点数字选项，在分类选项框中点选最下方的自定义，在右边的类型选项框中找到yyyy-“年“m“ 月 “d“日 这一项点一下，然后到类型两个字下方的编辑框中把 yyyy-“年“m“月“d“日改成 yyyy-“年“mm“月“dd“日,在类型两个字上方的示例方框中原本的 2010-1-25 就会变成 2010 年 01 月 25 日，点确定完成操作。2、用出生年月来计算年龄公式(计算到指定日期) ：=TRUNC(DAYS360(H6,“2009/8/30“,FALSE)/360,0)。14、根据出生日期自动计算周岁(计算到现在) ：=TRUNC(DAYS360(D3,NOW( )/360,0) 假设 D 列存放学生的出生日期， E 列输入该函数后则产生该生的周岁。
