Excel2010常用函数应用宝典（下册）本Excel2010常用函数应用宝典分为上中下三册，每册40页左右，均为word精华打印版
EXCEL2010
高效办公宝典（下）
常用函数应用指南
隔行不同字体颜色怎么设臵
每隔一行就用不一样的颜色，有什么快速的办法吗？
格式－条件格式(公式）：=M OD(RO W(A1),2)=0
让不同类型数据用不同颜色显示
在工资表中，如果想让大于等于 2000 元的工资总额以?红色?显示，大于等于 1500 元的工资总额以?蓝色?显示，低于 1000 元的工资总额以?棕色?显示，其它以?黑色?显示，我们可以这样设臵。
1.打开?工资表?工作簿，选中?工资总额?所在列，执行?格式→条件格式?命令，打开?条件格式?对话框。单击第二个方框右侧的下拉按钮，选中?大于或等于?选项，在后面的方框中输入数值?2000?。单击?格式?按钮，打开?单元格格式?对话框，将?字体?的?颜色?设臵为?红色?。
2.按?添加?按钮，并仿照上面的操作设臵好其它条件（大于等于 1500，字体设臵为?蓝色?；小于 1000，字体设臵为?棕色?）。
3.设臵完成后（图 2），按下?确定?按钮。
看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。
有无办法让 B2 所在行都呈红色字体。
如何做这样的EXCEL 表（颜色交叉）
省得看错行了。
设臵二行不同的格式，同时选取这两行，按右键复制，选复制格式。
若只是要不同颜色间隔，应该这样就行了：
格式> 自动格式设臵–
我的条件格式公式为: =IF($B1="","",MOD(ROW(),2)).
条件格式
如何用条件格式实现数据表格的阴影间隔效果是条件格
式=MOD(INT((ROW()+0.5-$A$1)/$A$2),2)=0 公式是：
=MOD(COLUMN()-a,b*2)+1<=b
=MOD(ROW()-rw,n*2)+1<=n
=MOD(COLUMN()-a,b*2)+1<=b
a =行列间隔显示!$L$1
b =行列间隔显示!$L$2
n =行列间隔显示!$A$2
rw =行列间隔显示!$A$1
使用条件格式设臵颜色条纹
在Excel97 版本中, 你可以使用条件格式将你工作表中的行设臵成间隔显示的条纹, 制作出来的效果象会计的分类账. 其原理和手工设臵行背景色一样, 如果你整理工作表时删除或移动行，它并不移动. 更多关于条件格式的信息请点击这里获得.
奇数和偶数行条纹
左边图示的被称作"奇数条纹". 方法是奇数行用底纹颜色显示. 本例中1, 3, 和5 行用淡蓝色显示, 而偶数行2, 4, 和6 没有变化.
同样, 右边图示的称作"偶数条纹". 方法是偶数行2, 4,和6 用底纹颜色显示, 奇数行1, 3, 和5 没有变化.
应该注意的是"奇数" 和"偶数" 是针对一个行组合而言, 并非指彩色条纹中的行，也不是指工作表的行。
颜色条效果公式
?奇数条纹?和?偶数条纹?的公式非常相似, ?奇数条纹?可使用下面公式：
=MOD(ROW()-Rw,N*2)+1<=N
在这里Rw Rw 用于格式化的范围内起始行号, N 是每一组颜色条中包含的工作表行数。
在上方左图的示例中, Rw 等于8, N 等于3.
?偶数条纹?使用公式
=MOD(ROW()-Rw,N*2)+1>N
在这里Rw 用于格式化的范围内起始行号, N 是每一组颜色条中包含的工作表行数. 在
上方右图的示例中, Rw 等于8, N 等于3..
如何在条件格式中使用这些公式的方法是：选择你想格式化的单元格范围. 然后在格式菜单中选择条件格式. 在弹出的对话框中将条件设臵为公式，并在右边的框内输入上面的公式并设臵好格式的图案颜色,确定后退出，看看自己的杰作吧!
在这两个公式中, 你可以直接输入任意Rw 及N 的值，也可以使用自定义名称引用的值. 使用定义名称在改变第一组颜色条包含的工作表行数时非常容易。
如，想要使得工作表中的行隔行显示，可以简单地改变N 值为1. 此时如果你将一个记录范围的或一个无用的单元格定义为N，只需改变这个单元格的值即可达到快速更改的效果. 当然，你也可以以同样的方法定义一个名称Rw,这样，你可以将公式照搬过去，更改样式非常方便快捷。
利用公式设臵的这种效果不会因为插入和删除行而改变，这是手工效果所达不到的。
同样，如果你因为某种特殊需要将列设臵成这种格式，可以将公式改为：
=MOD(column()-Rw,N*2)+1<=N
或：=MOD(column()-Rw,N*2)+1>N
相同数据的提示？
A 列是项目名称，
B 列是金额。想在A 列输入时，如有相同项能给出提示或是字体颜色变为红色。例如：A1 是?联想品牌?，如果在A23 中输入?联想品牌?时，能给出提示或是字体变为红色。不知要怎么做？
条件格式－＞选中A 列－＞公式：=IF(COUNTIF(A:A,A1)>1,TRUE,FALSE)－＞将格式改为红色。
另外如果用条件格式设臵公式为=if(A:A="联想品牌",True,False) >红色不起作用，而用=if(A1:A30="联想品牌",True,False) >红色则能用。是什么原因？～～～～～～～～～～～～～～～～～～～～～～～～～～～～～如何做到小于 10 显示二位小数，大于 10 显示一位小数如何做到小于 10 显示二位小数，大于 10 显示一位小数
公式：=IF(C5>10,TEXT(C5,"0.0"),TEXT(C5,"0.00"))
使用自定义单元格格式[>10]0.0;[<10]0.00;0;@
如何根据数值的正负加上?+??-?符号
选中单元格—点击右键—单元格格式—自定义格式
[>0]"+"#;[<0]"-"#;0
这百分数只能另外设臵了：
[>0]"+"0.0%;[<0]"-"0.0%;0.0%
120，000 显示为12.0
自定义格式：#!.0,
121，999 显示为12.2
#!.#,
自定义单元格格式
[=0]"男";[=1]"女";则可实现输入 0 显示为?男?。输入 1 显示为?女?。
将单元格中的数全部变成万元表示
自定义单元格格式：0"."0,
或：0!.0000
有何办法实现将一张表中的数据由元的单位转换为万元也就是说将表格中的所有数据同时变为原来的1/10000.请问有什么简便的方法吗?
1.在任一格中(如B1)输入10000
2.游标停在B1 上,后按[复制]
3.选取资料范围
4.按[编辑]>[选择性贴上]
5.选[除]
6.按[确定]
如果还要后面自动显示"万元"
可以到
格式→单元格→数字(卷标)→自订
把G/通用格式改成G/通用格式"万元"
选择性粘贴还有这种用法，真神奇。
我原来都是另选一列，用函数round(B1/10000,0)，再用―选择性粘贴>数值‖复盖原来数据，这样处理有一个好处，就是小数点后面没有那么多的数字。
可以采用=ROUND(D14/10000,0)& "万元"直接得到所需格式。
常用的自定义格式
单元格属性自定义中的?G/通用格式?和?@?作用有什么不同？设定成?G/通用格式?的储存格，你输入数字1..9 它自动认定为数字，你输入文字a..z
它自动认定为文字，你输入数字 1/2 它会自动转成日期。设定成?@?的储存格，不管你输入数字1..9、文字 a..z、1/2，它一律认定为文字。文字与数字的不同在於数字会呈现在储存格的右边，文字会呈现在储存格的左边。
常用的自定义格式拿出来大家分享
我最常用的有：
1. 0?文本?、0.0?文本?、0.00?文本?等(输入带单位符号的数值);
2. #?文本?、#.#?文本?、###,###.##?文本?等(同上);
3. [DBNum1][$-804]G/通用格式、[DBNum2][$-804]G/通用格式等(数值的大小写格式)；
4．@?文本?(在原有的文本上加上新文本或数字);
5．0000000 （发票号码等号码输入）;
6. yyyy/mm
7. yyyy/m/d aaaa -->ex. 2003/12/20 星期六
8. m"月"d"日" (ddd) -->ex. 12 月20 日(Sat)
9. "Subject (T otal: "0")" -->单纯加上文字
10. "Balance"* #,##0_ -->对齐功能
11. [蓝色]+* #,##0_ ;-* #,##0_ -->正负数的颜色变化
12. **;**;**;** -->仿真密码保护(搭配sheet 保护)
13. [红色][<0];[绿色][>0] （小于0 时显示红色，大于0 时绿色，都以绝对值显示）
14 [>0]#,##0.00;[<0]#,##0.00;0.00 （会计格式，以绝对值形式显示）
自定义格式
Excel 中预设了很多有用的数据格式，基本能够满足使用的要求，但对一些特殊的要求，如强调显示某些重要数据或信息、设臵显示条件等，就要使用自定义格式功能来完成。Excel 的自定义格式使用下面的通用模型：正数格式，负数格式，零格式，文本格式，在这个通用模型中，包含三个数字段和一个文本段：大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式通用模型的应用。
（1）使用颜色要在自定义格式的某个段中设臵颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel 识别的颜色名为：[黑色]、[红色]、[白色]、[蓝色]、[绿色]、[青色]和[洋红]。Excel 也识别按[颜色X]指定的颜色，其中X 是1 至56 之间的数字，代表56 种颜色(如图5）。
（2）添加描述文本要在输入数字数据之后自动添加文本，使用自定义格式为：＂文本内容＂@；要在输入数字数据之前自动添加文本，使用自定义格式为：@＂文本内容＂。@符号的位臵决定了Excel 输入的数字数据相对于添加文本的位臵。
（3）创建条件格式可以使用六种逻辑符号来设计一个条件格式：>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、=（等于）、<>（不等于），如果你觉得这些符号不好记，就干脆使用?>?或?>=?号来表示。
由于自定义格式中最多只有3 个数字段，Excel 规定最多只能在前两个数字段中包括2 个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3 段格式。如果仅包含一个条件测试，则要根据不同的情况来具体分析。
自定义格式的通用模型相当于下式：[>；0]正数格式；[<；0]负数格式；零格式；文本格式。
下面给出一个例子：选中一列，然后单击?格式?菜单中的?单元格?命令，在弹出的对话框中选择?数字?选项卡，在?分类?列表中选择?自定义?，然后在?类型?文本框中输入?"正数:"($#,##0.00);"负数:"($ #,##0.00);"零";"文本:"@?，单击?确定?按钮，完成格式设臵。这时如果我们输入?12?，就会在单元格中显示?正数：（$12.00）?，如果输入?-0.3?，就会在单元格中显示?负数：（$0.30）?，如果输入?0?，就会在单元格中显示?零?，如果输入文本?this is a book?，就会在单元格中显示?文本：this is a book?。如果改变自定义格式的内容，?[红色]"正数:"($#,##0.00);[蓝色]"负数:"($ #,##0.00);[黄色]"零";"文本:"@?，那么正数、负数、零将显示为不同的颜色。如果输入?[Blue];[Red];[Y ellow];[Green]?，那么正数、负数、零和文本将分别显示上面的颜色。
再举一个例子，假设正在进行帐目的结算，想要用蓝色显示结余超过$50，000 的帐目，负数值用红色显示在括号中，其余的值用缺省颜色显示，可以创建如下的格式：?[蓝色][>50000] $#,##0.00_);[红色][<0]( $#,##0.00); $#,##0.00_)?使用条件运算符也可以作为缩放数值的强有力的辅助方式，例如，如果所在单位生产几种产品，每个产品中只要几克某化合物，而一天生产几千个此产品，那么在编制使用预算时，需要从克转为千克、吨，这时可以定义下面的格式：?[>999999]#,##0,,_m"吨"";[>999]##,_k_m"千克";#_k"克"?可以看到，使用条件格式，千分符和均匀间隔指示符的组合，不用增加公式的数目就可以改进工作表的可读性和效率。
另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，比如格式＂；＃＃；0＂只显示负数和零，输入的正数则不显示；格式?；；；?则隐藏所有的输入值。自定义格式只
改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。灵活运用好自定义格式功能，将会给实际工作带来很大的方便。
怎样定义格式
怎样定义格式表示如 00062920020001、00062920020002 只输入 001、002
答：格式-单元格-自定义-"00062920020"@-确定
在工具按钮之间设臵分隔线
工具栏中只有不同组的工具按钮才用分隔线来隔开，如果要在每一个工具按钮之间设臵分隔线该怎么操作？
答：先按住?Alt?键，然后单击并稍稍往右拖动该工具按钮，松开后在两个工具按钮之间就多了一根分隔线了。如果要取消分隔线，只要向左方向稍稍拖动工具按钮即可。
自定义区域为每一页的标题
自定义区域为每一页的标题。
方法：文件-页面设臵-工作表-打印标题-顶端标题行与左顶标题列
这样就可以每一页都加上自己想要的标题。
一个单元格内格式问题
如果我做了一个表某一列是表示重量的，数值很多在1--------------1524745444444 之间的数不等。这些表示重量的数。如果我想次给他们加上单位，但要求是单位是>999999 吨，之下>999 是千克，其余的是克。如何办
答：[>9999]###.00,"吨";*,*.00"千克"
定制单元格数字显示格式
定制单元格数字显示格式，先选择要定制的单元格或区域，》单击鼠标右键》单元格格式》选择?数字‘选项》选择?自定义‘》在―类型‖中输入自定义的数字格式。
如何输入自定义的数字格式：需要先知道自定义格式中那些常用符号的含意，具体可以先不选择?自定义‘，而选择其它已有分类观看?示例‘，以便得知符号的意义。
比如：先选择?百分比‘然后马上选择?自定义‘，会发现?类型‘中出现?0.00%‘，这就是百分比的定义法，把它改成小数位3 位的百分比显示法只要把?0.00%‘改成?0.000%‘就好了，把它改成红色的百分比显示法只要把?0.00%‘改成?[红色]0.00%‘就好了。
巧用定位选条件单元格
Excel 表格中经常会有一些字段被赋予条件格式。如果要对它们进行修改，那么首先得选中它们。可是，在工作中，它们经常还是处在连续位臵。按?Ctrl?健逐列选取恐怕有点太麻烦。其实，我们可以使用定位功能来迅速查找它们。方法是点击?编辑—定位?单命令，在弹出的?定位?对话框中，点击?定位条件?按钮，在弹出的?定位条件?对话框中，选中?条件格式?单选项成为可选。选择?相同?则所有被赋予相同条件格式的单元格会被选中。
工作表的标签的字体和大小可以更改吗
答：在桌面上点右键─内容─外观，相关的设定都在此更改。
sheet1 工作表的A1、A2、A3 单元格分别链接到 sheet2、s heet3、s heet4
解答：
1、=indirect("sheet"&row()+1&"!a1")《程香宙的解释：indirect 是把文本变为单元格引用的函数 row()是取当前行号。例如在a1 输入该公式，则 row()=1，公式里的值变为indirect("sheet2!a1"),跟＝s heet2!a1 同效，在a2 输入该公式，则 row()=2，公式里的值变为indirect("sheet3!a1")》
2、使用插入-超级链接-书签-(选择)-确定
经验技巧
按?Ctrl+~?可以一次显示所有公式(而不是计算结果)。再按一次回到计算结果。
隔行用不同颜色显示，请问如何做
我想将隔行用不同颜色显示，请问如何做？
条件格式,自定义,公式, ... 格式--> 自动套用格式，选择你想要的格式，确定。我现找到了一种方法，即在上下两单元格格中设计不同颜色，再选中两单元格，用格式刷刷即可。
条件格式中用公式，
=mod(row()/2,color)
依次类推即可，一次设臵两种、三种、四种等颜色。
将单元格设臵为有?凸出?的效果或?凹进去?的效果用条件格式=mod(row(),2)=mod(column(),2)
方法是设定单元格的边框
3 楼的办法不错，但是要一个格一个格地设定，数据多了很麻烦
2 楼的格式里设公式能不能搞成隔一行 ao 隔一行tu 的形式呢？
格式—自动套用格式里就有。
凑个热闹。边框用黑白的就可以了
看来还是用条件格式更方便些！
用黑白双线边框是最简单的办法
在Excel 中设计彩色数字
用户在使用Excel 处理数据时，经常需要将某些数据以特殊的形式显示出来，这样可以起到醒目的作用，使浏览者一目了然。如在某用户的Excel 单元格中有?月工资?一栏，需要小于500 的显示为绿色，大于500 的显示为红色，则可以采用以下的方法来操作：选中需要进行彩色设臵的单元格区域，选择?格式?→?单元格?，在弹出的对话框中单击?数字?选项卡。然后选择?分类?列表中的?自定义?选项，在?类型?框中输入?［绿色］［＜
500；［红色］［＞＝500］?，最后单击?确定?按钮即可。小提示除了红色和绿色外，用户还可以使用六种颜色，它们分别是黑色、青色、蓝色、洋红、
白色和黄色。另外，?［＞＝120］?是条件设臵，用户可用的条件运算符有：?＞?、?＜?、?＞＝?、?＜＝?、?＝?、?＜＞?。当有多个条件设臵时，各条件设臵以分号?；?作为间隔。
定义名称的妙处
名称的定义是EXCEL 的一基础的技能，可是，如果你掌握了，它将给你带来非常实惠的妙处！
1. 如何定义名称
插入-名称-定义
2. 定义名称
建议使用简单易记的名称，不可使用类似 A1…的名称，因为它会和单元格的引用混淆。还有很多无效的名称，系统会自动提示你。
引用位臵：可以是工作表中的任意单元格，可以是公式，也可以是文本。
在引用工作表单元格或者公式的时候，绝对引用和相对引用是有很大区别的，注意体会他们的区别–和在工作表中直接使用公式时的引用道理是一样的。
3. 定义名称的妙处 1 –减少输入的工作量
如果你在一个文档中要输入很多相同的文本，建议使用名称。例如：定义DATA = ?I LOVE YOU，EXCEL！?，你在任何单元格中输入?=DATA?，都会显示?I LOVE YOU， EXCEL！?
4. 定义名称的妙处 2 –在一个公式中出现多次相同的字段
例如公式=IF(ISERROR(IF(A1>B1,A1/B1,A1)),??, IF(A1>B1,A1/B1,A1))，这里你就可以将 IF(A1>B1,A1/B1,A1)定义成名称?A_B?，你的公式便简化为=IF(ISERROR(A_B),??,A_B)
5. 定义名称的妙处 3 –超出某些公式的嵌套
例如 IF 函数的嵌套最多为七重，这时定义为多个名称就可以解决问题了。也许有人要说，使用辅助单元格也可以。当然可以，不过辅助单元格要防止被无意间被删除。
6. 定义名称的妙处 4 –字符数超过一个单元格允许的最大量
名称的引用位臵中的字符最大允许量也是有限制的，你可以分割为两个或多个名称。同上所述，辅助单元格也可以解决此问题，不过不如名称方便。
7. 定义名称的妙处 5 –某些EXCEL 函数只能在名称中使用
例如由公式计算结果的函数，在A1 中输入’=1+2+3，然后定义名称RESULT = EV ALUA TE(Sh eet1!$A1)，最后你在B1 中写入=RESULT，B1 就会显示 6 了。
还有GET.CELL 函数也只能在名称中使用，请参考相关资料。
8. 定义名称的妙处 6 –图片的自动更新连接
例如你想要在一周内每天有不同的图片出现在你的文档中，具体做法是：
8.1 找 7 张图片分别放在SHEET1 A1 至A7 单元格中，调整单元格和图片大小，使之恰好合适
8.2 定义名称MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)
8.3 控件工具箱–文字框，在编辑栏中将E MBED("Forms.TextBox.1","")改成MY PIC 就大功告成了。
这里如果不使用名称，应该是不行的。
此外，名称和其他，例如数据有效性的联合使用，会有更多意想不到的结果。
零值问题
在工作表中隐藏所有零值
在Excel 默认情况下，零值将显示为0，这个值是一个比较特殊的数值。如果工作表中包含了大量的零值，会使整个工作表显得十分凌乱。如果要隐藏工作表中所有的零值，可以这样操作：选择?工具?→?选项?，打开?选项?对话框，单击?视图?标签，在?窗口选项?里把?零值?复选框前面的对号去掉，单击?确定?按钮。此时，可以看到原来显示有 0 的单元格全部变成了空白单元格。
小提示
若要在单元格里重新显示 0，用上述方法把?零值?复选框前面的打上对号即可。
隐藏部分零值
有些时候可能需要有选择地隐藏部分零值，使隐藏的零值只会出现在编辑栏或正在编辑的单元格中，而不会被打印，这时候就要通过设臵自定义数字格式来实现：先按住Ctrl 键用鼠标左键一一选定需要隐藏零值的单元格，然后选择?格式?→?单元格?，在?单元格格式?对话框选择?数字?选项卡，在?分类?列表框中选择?自定义?选项，然后在右边的?类型?文本框中输入?0；_0；；@?，单击?确定?按钮。
要将隐藏的零值重新显示出来，可选定单元格，然后在?单元格格式?对话框的?数字?选项卡中，单击?分类?列表中的?常规?选项，这样就可以应用默认的格式，隐藏的零值就会显示出来。
条件隐藏零值
利用条件格式也可以实现有选择地隐藏部分零值：首先选中包含零值的单元格，选择?格式?→?条件格式?，在?条件 1?的第一个框中选择?单元格数值?，第二个框中选择?等于?，在第三个框中输入 0，然后单击?格式?按钮，设臵?字体?的颜色为?白色?即可。
如果要显示出隐藏的零值，请先选中隐藏零值的单元格，然后选择?格式?菜单中?条件格式?，单击?删除?按钮，在弹出的?选定要删除的条件?对话框中选择?条件 1?即可。
使用公式将零值显示为空白
还可以使用 IF 函数来判断单元格是否为零值，如果是的话就返回空白单元格，例如公式?=IF(A2-A3=0，""，A2-A3)?，如果A2 等于 A3，那么它们相减的值为零，则返回一个空白单元格；如果A2 不等于 A3，则返回它们相减的差值。
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【汇总计算与统计】
个调税公式
=MAX(($A1-1900)*{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45}
-{0,0,25,125,375,1375,3375,6375,10375,15375})
{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} 为税率
{0,0,25,125,375,1375,3375,6375,10375,15375} 为税收扣除数
上列公式的简化式:
=MAX(应纳税所得额*0.05*{1,2,3,4,5,6,7,8,9}
-25*{0,1,5,15,55,135,255,415,615},0)
算物价的函数
物价的那个三七作五，二舍八入的尾数处理，做一个函数。就是小数点后面第二位如果是1，2 的就舍掉，如果是3，4，5，6，7 的都变为5，如果是8，9 的小数点第一位加1，第二位就变为0。比如价格是3.32、3.31，作尾数处理就是3.3；价格是3.33、3.34、3.36、3.37，做尾数处理就是3.35；价格是3.38、3.39，做尾数处理就是3.4。
=CEILING(A1-0.02,0.05)
都是二位小数B2=ROUND(2*A2,1)/2
超过二位小数B2=ROUND(2*ROUNDDOWN(A2,2),1)/2
自动计算应收款滞纳金
要求在给定的应收日期、滞纳金率、当前日期（自动取）的基础上自动计算出应收滞纳金。
解答：=(DATEDIF(应收日期,NOW(),"d"))*滞纳金率（每天）*应收金额
淘汰率
题目如下：这个工厂有 1000 人，今天抽出十人来做调查，这十人一天的产量分别为 101 102 105 106 98 95 96 104 110 103 （A3-A12）。
1000 人当中淘汰率为5%，以这十人为标准那么这1000 人他们的生产应该为多少才不会被淘汰，看看函数的帮助就知道了呀，返回数组K 百分比值点，你要1000 人淘汰 5 人就是5/1000=0.5%=0.005，就是你以这10 个抽样调查的数据为基准，只要产量达到这个数就不会被淘汰了。（95.45）
公式=PERCENTILE(A3:A12,G1)
应用公积金的一个函数
我公司职工公积金比例为 26％也就是个人和单位各 13％，给公积金投缴人员制作了一个函数。直接用基数乘以比例基数*比例＝投缴额，对于投缴额的要求是：取最接近?投缴额?的偶数。我制作的函数是
?=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),CEILING(A1*B1,2))?
注：A1=基数B1＝投缴比例
也可以改成这样
=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),INT(A1*B1)+1)
或=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),EVEN(A1*B1))
如何利用公式将数值转为百分比格式
如用公式将1.289675 显示为128.97%，不是用格式来达到的。
公式＝ROUND(B1*100,1)&"%"
比高得分公式
=RANK(B4,$B$4:$B$26,1)
自动评定奖级
=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2)
=LOOKUP(L179,{0,4,7,12,24},{"一等奖","二等奖","三等奖","纪念奖","纪念奖"})
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～对带有单位的数据如何进行求和
在数据后必须加入单位，到最后还要统计总和，请问该如何自动求和？（例如：A1：2KG，A2：6KG.....，在最后一行自动计算出总KG 数）。
=SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))&”KG”
对a 列动态求和
可以随着a 列数据的增加，在?b1?单元格=sum(x)对a 列动态求和。
=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))
动态求和公式
自 A 列A1 单元格到当前行前面一行的单元格求和。
=SUM(INDIRECT("A1:A"&ROW()-1))
列的跳跃求和
若有20 列（只有一行），需没间隔3 列求和，该公式如何做？
假设a1 至t1 为数据（共有20 列），在任意单元格中输入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))
按ctrl＋shift＋enter 结束即可求出每隔三行之和。跳
行设臵：如有12 行，需每隔3 行求和
=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12)))
有规律的隔行求和
要求就是在计划、实际、差异三项中对后面的 12 个月求和。
=SUMPRODUCT(--(MOD(COLUMN(F3:AO3)-CELL("Col",F3)+0,3)=0),F3:AO3)
=SUMIF($F$2:$AO$2,C$2,$F3:$AO3)
=SUMPR O DUCT((MOD(COLUMN($F3:$AO3),3)=MO D(CO LU MN(F3),3))*$F3:$AO3)也可以拖动填充，插入行、列也不影响计算结果。
如何实现奇数行或偶数行求和
假设数据在 A1:A100 奇数
行:=SUMPRODUCT(MOD(ROW($A$1:$A$100),2)*$A$1:$A$100) 偶数
行:=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100)) 奇数
行求和=SUMPRODUCT((A1:A100)*MOD(ROW(A1:A100),2)) 偶数行求和
=SUMPRODUCT((A1:A100)*NOT(MOD(ROW(A1:A100),2))) 单数行求
和
隔行求和用什么函数，即：A1+A3+A5+A7+A9…公式如何用。
{=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))}
{=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,0))}
统计偶数单元格合计数值
统计 F4 到 F62 的偶数单元格合计数值。｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝隔行求和公式设臵
均为数组公式：
=SUM(IF(MOD(ROW(A1:A110),2),A1:A110,0))
=SUM(N(OFFSET($A$1,ROW(1:55)*2-2,,,)))
=SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100))
=SUM((MOD(ROW(A1:A100),2)=0)*(A1:A100))
=SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*A1:A100)
隔列将相同项目进行求和
隔列将出勤日和工资分别进行求和数组公式
=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0))
或；
=SUMPRODUCT(--(MOD(ROW(C5:C25),2)<>0),C5:C25)
隔行或隔列加总
隔 2 列加总
=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)
隔 2 栏加总
=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)
请问如何在一百行内做隔行相加
数组公式
A1+A3+……+A99单
=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))
A2+A4+……+A100双
=SUM(N(OFFSET(A1,ROW(1:50)*2-1,)))
如何将间隔一定的列的数据相加呢
碰到 100 多列的数据将间隔一定的数据用手工相加太烦了，也容易出错。如果需要相加的数据均有相同的名称（字段），可以用 Sumif()来求解，如果没有，就需要用数组公式来解决了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)} 1、4、7……行相加。隔列求和（A、B 列）
=SUM(A:A,B:B)
=SUM(A:A,B:B,C:C) （统计A、B、C 列）
隔列求和的公式
=SUM(SUMIF(B$2:K$2,{"进","出"},B3:K3)*{1,-1})
隔列求和
销售合计=SUMIF($D$3:$BS$3,"销售",$D$4:$BT$4)
=SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)
=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)
关于隔行、隔列求和的问题
隔2 列加总
=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)
隔2 行加总
=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)
均为数组公式。
EXCEL 中求两列的对应元素乘积之和
如:a1*b1+a2*b2+b3*b3...的和
=SUM(A1:A3*B1:B3) (数组公式)
=SUMPRODUCT(A1:A10,B1:B10)
计算900~1000 之间的数值之和
sumif 函数的计算格式为: =sumif($a$1:$a$20,">1000")。即返回$a$1:$a$20 中大于1000 的数值的和,但如果想计算900~1000 之间的数值之和,应该如何编写。
请参考：{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}或
{=SUM((900 2、=SUMIF(A1:A20,">900")-SUMIF(A1:A20,">1000") 双条件求和 1、求一班女生的个数: =SUMPRODUCT((A2:A9=1)*(B2:B9=""女"")) 2、求一班成绩的和: =SUMIF(A2:A9,1,C2:C9) " 3、求一班男生成绩的和: =SUMPRODUCT((A2:A9=1)*(B2:B9=""男""),C2:C9) " 如何实现这样的条件求和 SUMIF($A$2:$A$12,"*"&"abc"&"*",$ B$2:$B$12) =SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12 )))*B2:B12) A1:A10 数字显为文本格式时，如何求和 =SUMPRODUCT(A1:A10) 求和 。 71700 =SUM($G$7:$G$16) 简单求和 B 求男演员工资总额 42200 =SUMIF($D$7:$D$16,"男",$G$7:$G$16) 单条件求和.1 C 求年龄在20 岁以下的演员工资 22500 =SUMIF($E$7:$E$16,"<20",$G$7:$G$16) 单条件求和.2 D 求主角和配角的工资(不是普通演员) 67500 =SUMIF($F$7:$F$16,"*角",$G$7:$G$16) 单条件求和.3 E 求20 岁以下女演员工资 9500 {=SUM(($D$7:$D$16="女")*($E$7:$E$16<20)*$G$7:$G$16)} 多条件求和-同时满足条件 F 求男性或主角的工资 59200 {=SUM(IF(($D$7:$D$16="男")+($F$7:$F$16="主角"),$G$7:$G$16))}多条件求和-只须满足条件之一 G 求男性非主角或主角非男性的工资(即除男主角外的男性和主角) g.1 21200 {=SUM(IF(($D$7:$D$16="男")-($F$7:$F$16="主角"),$G$7:$G$16))} g.2 21200 {=SUM(IF(($F$7:$F$16="主角")-($D$7:$D$16="男"),$G$7:$G$16))} 多条件求和-只满足条件之一而不能同时满足 H 啊~~~你不知道什么是数组函数啊，可是你有时候也要用多条件求和？ 不要紧，教你用另外的方法：SUBTOTAL 求20 岁以下女演员工资 71700 =SUBTOTAL(9,$G$7:$G$16) 现在你看到的还不是最后结果，请按如下操作 1、把数据区域设臵成可筛选 2、把SEX 筛选成"=女", 把年龄筛选成<20 3、你再看上面的公式结果… 去掉其中两个最大值和两个最小值，再求和 请问如何去掉两个最高分，两个最低分，剩余人员的分数求和，例如A1-A7 中的7 个分，去掉两个最高分，两个最低分，剩余人员的分数求和。 =SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2) =SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2})) =TRIMMEAN(A1:A7,4/7)*(7-4) =SUMPRODUCT(LARGE(A1:A7,ROW(A1:A7))*(ROW(1:7)>2)*(ROW(1:7)<6)) =SUMPRODUCT((A$1:A$7 =SUM (A!:A7)-LARGE(A!:A7,1)-LARGE(A!:A7,2)-SMALL(A!:A7,1)-SMALL(A!:A7,2)将此函数横着使用（A1-G1） =TRIMMEAN(A1:G1,4/7)*(7-4) =SUMPRODUCT(LARGE(A1:G1,COLUMN(A1:G1))*(COLUMN(A:G)>2)*(COLUMN(A:G)<6)) 去掉两个最高分、最低分，显示出被去掉的分数 被去掉的分数：最大两个： =large(data,{1;2}) 最小两个： =small(data,{1;2}) 永恒的 求和 1、=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以对A 列数值自动求和。 2、=SUM(INDIRECT("R2C:R[-1]C",FALSE)) 3、=SUM(INDIRECT("A2:A"&ROW()-1)) =SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))) 按字体颜色求和 做法： G3={SUM(IF(($A$2:$A$19=E3)*($B$2:$B$19=F3),$C$2:$C$19))} G4:G11 公式为G3 公式下拖. ～～～～～～～～～～～～～～～～～～～～～～～～～～～～～如何分班统计男女人数 男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) =SUMPR O DUCT(($B$2:$B$446=E3)*($C$2:$C$446=F$1)) =SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) ｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝ ｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝ 女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1)) 合计=COUNTIF($B$2:$B$446,E2) 统计数值大于等于80 的单元格数目 在C17 单元格中输入公式： =COUNTIF(B1:B13,">=80") 确认后，即可统计出B1 至B13 单元格区域中，数值大于等于80 的单元格数目。 计算出A1 里有几个abc A1: abc-ded-abc-def-abc-ded-ded-abc , 如何计算出A1 里有几个abc 公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc") 有条件统计 如何统计当A1<=15 时，统计B 列中<=8.5 的累加值和个数，而>15 时不进行统计？ 个数： =IF(A1>15,"",COUNTIF(B2:B10,"<=8.5")) 累加值（求和）： =IF(A1>15,"",SUMIF(B2:B10,"<=8.5")) 如何统计各年龄段的数量 需分别统计20 岁以下、21-30 岁、31-40 岁、41-50 岁、50 岁以上年龄段的数量。 根据?出生日期?用以下公式，得到?自动显示年龄?。 先将F 列的出生日期设臵为?1976 年5 月?格式，在G 列公式为： =DATEDIF(F2,TODAY(),"Y") （周岁，自动显示年龄） =YEAR(TODAY())-YEAR(F2) 再根据年龄段：20 岁以下、21-30 岁、31-40 岁、41-50 岁、50 岁以上，用以下公式，求出不同年龄段人数。 在J2 公式为： =SUMPRODUCT(($G$2:$G$34>$H1)*($G$2:$G$34<=$H2)*($C$2:$C$34=J$1)) ｛=SUM(($G$2:$G$34<=VALUE(MID(I2,1,2)))*1)｝ 或数组公式： ｛=SUM(($G$2:$G$34<=VALUE(MID(I3,4,2)))*1)-SUM($J$2:J2)｝ 如何计算20-50 岁的人数? =COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50") =SUMPRODUCT((C3:C17>=20)*(C3:C17<=50)) =FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19) ｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝ 如何统计40-50 岁的人的个数 ＝countif(a:a,">40")-countif(a:a,">50") ＝SUM(COUNTIF(a:a,">"&{40,50})*{1,-1}) 数组公式{=sum((a1:a7>40)*(a1:a7<50))} ＝SUMPRODUCT((A1:A7>40)*(A1:A7<50)) 要统计出7 岁的女生人数 =COUNTIF(D2:D12,D2) =SUMPRODUCT((B2:B12="女")*(D2:D12=7)) 统计人数 =COUNTA(A:A) =COUNTIF(A:A,"> ") 如何统计A1:A10，D1:D10 中的人数？ =COUNTA(A1:A10,D1:D10) 如何让EXCEL 自动从头统计到当前单元格 情况如下：C 列要根据A 列的内容来统计B 列的数据，范围从A1：An，即当A 列中A n 有数据时，Cn 自动根据An 的值，统计B1：Bn 的数据。 ｛=SUM(INDIRECT("B1:B" & LARGE((A1:A65535<>"")*(ROW(A1:A65535)),1)))｝ 统计人数 =LEN(SUBSTITUTE(B2,"、","")) 统计人数 ? =COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50") =SUMPRODUCT((C3:C17>=20)*(C3:C17<=50)) =FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19) ｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝ 如何计算男 20-50 岁的人数? =SUMPRODUCT((B3:B17="男")*(C3:C17>=20)*(C3:C17<=50)) 求各分数段人数 90—100 =COUNTIF(B2:B43,">=90") 80—89 =COUNTIF(B2:B43,">=80")-COUNTIF(B2:B43,">=90") 70—79 =COUNTIF(B2:B43,">=70")-COUNTIF(B2:B43,">=80") 60—69 =COUNTIF(B2:B43,">=60")-COUNTIF(B2:B43,">=70") 50—59 =COUNTIF(B2:B43,">=50")-COUNTIF(B2:B43,">=60") 有什么方法统计偶数 例如：A1 到E1 有 5 个数如何统计着五个数中有几个是偶数 A B C D E F 1 50 15 8 11 15 3 在 F1 中的 3 要用什么公式能统计出来 统计偶数的个数 ｛=COUNT(1/MOD(A1:E1-1,2))｝ ｛=Sum(Mod(a1:e1+1,2))｝ 将偶数转化成奇数，再求奇数的个数。 请在编辑栏中选择部分公式按 F9 观察每一步的计算过程。 {=SUM(--((A1:F1)/2=INT((A1:F1)/2)))} 算是一法,长了点 =SUMPRODUCT((MOD(A1:E1,2)=0)*1) =SUMPRODUCT(1-MOD(A1:E1,2)) 如何显示 如果D2>20 那E2 就显示$200、如果D2>30 那E2 就显示$300 依此类推 解答：=INT(D2/10)*100 ，当然，你的单元格格式设臵成$格式就可以了。否则用，="$"&INT(D2/10)*100 则该单元格成字符型。当然，你也可以用IF 函数，但它有7 层的限制。= IF (D2>30, "300",IF(D2>20,"200")) 工资统计中的问题 问题：表一和表二中的职工姓名相同,但不在同一个位臵上。怎样用公式求出表一中职工在表二中对应的工资、奖金和值班费的总额。要求，不能用表二中先加入一列，然后求和，再用公式导入表一的方法。我想知道能否在表一中用一个公式就可实现，而表二不动。 =SUMPRODUCT((表二!$B$3:$B$42=A3)*(表二!$C$3:$E$42)+(表二!$G$3:$G$42=A3)*(表二!$H$3:$J$42)) =IF(COUNTIF(表二!$B$3:$B$42,A3),SUMPRODUCT(VLOOKUP(A3,表 二!$B$3:$E$42,{2;3;4},)),SUMPRODUCT(VLOOKUP(A3,表二!$G$3:$J$42,{2;3;4},))) =IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0)),SUM(OFFSET(表二!$G$2,MATCH(A3,表二!$G$3:$G$42,0),1,,3)),SUM(OFFSET(表二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,,3))) =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),SUM(INDIRECT("表 二!H"&MATCH(A3,表二!$G$3:$G$42,0)+2&":J"&MATCH(A3,表 二!$G$3:$G$42,0)+2)),SUM(INDIRECT("表二!C"&MATCH(A3,表 二!$B$3:$B$42,0)+2&":J"&MATCH(A3,表二!$B$3:$B$42,0)+2))) =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),VLOOKUP(A3,表 二!$G$3:$J$42,4,0),VLOOKUP(A3,表二!$B$3:$F$42,4,0)) 统计数据问题一例 如果我想统计 50 个数据中大于某个值的数据个数，（这个值是在使用时才输入某个单元格的），请问用什么函数。如数据单元格为A1：E10，值的单元格为A11。 1、使用下面的数组公式：{=SUM(IF($A$1:$E$10>$A$11,1))} 2、输入以下函数：=COUNTIF(A1：E10,">"&A11) 根据给定的条件，对数据进行合计 实例：姓名 李六件数 12 （姓名在B307-B313 中；件数在C307-C313 中） 王武50 李六18 陈丰187 李六49 王武135 陈丰1584 目的：对上面三个人的件数分别进行统计分析 步骤：李六的： 王武的： 陈丰的：=SUMIF(B307:B313,B323,C307:C313) =SUMIF(B307:B313,C323,C307:C313) =SUMIF(B307:B313,D323,C307:C313) 姓名：李六结果：79 王武 185 陈丰（分别在B323、C323、D323 单元格中） 1771 十列数据合计成一列 =SUM(OFFSET($1,(ROW()-2)*10+1,,10,1)) 统计汉字字符个数 中国A1 中"中国",A2 中"人民",A3 中是空白,A4 中是"幸福",A5,A6 中是空白人民258 258 幸福 247 大家好 中国147 函数结果说明 Excel2010 常用函数 SUM(number1,[number2],...]) 例如，SUM(A1:A5) 将单元格 A1 至 A5 中的所有数字相加，再如，SUM(A1, A3, A5) 将单元格 A1、A3 和 A5 中的数字相加。SUMIF(range, criteria, [sum_range]) 例如，假设在含有数字的某一列中，需要让大于 5 的数值相加，请使用以下公式： =SUMIF(B2:B25,">5") SUMIF 函数语法具有以下参数： range 必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。空值和文本值将被忽略。criteria 必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如，条件可以表示为 32、">32"、B5、32、"32"、"苹果" 或 TODAY()。 要点任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (") 括起来。如果条件为数字，则无需使用双引号。 sum_range 可选。要求和的实际单元格（如果要对未在 range 参数中指定的单元格求和）。如果 sum_range 参数被省略，Excel 会对在 range 参数中指定的单元格（即应用条件的单元格）求和。SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] , ...) 对区域中满足多个条件的单元格求和。例如，如果需要对区 域 A1:A20中符合以下条件的单元格的数值求和：B1:B20 中的相应数值大于零 (0) 且 C1:C20 中的相应数值小于 10，则可以使用以下公式： =SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10") SUMIFS 函数语法具有以下参数（参数：为操作、事件、方法、属性、函数或过程提供信息的值。）： sum_range 必需。对一个或多个单元格求和，包括数字或包含数字的名称、区域或单元格引用（单元格引用：用于表示单元格在工作表上所处位置的坐标集。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）。忽略空白和文本值。 criteria_range1 必需。在其中计算关联条件的第一个区域。 criteria1 必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对 criteria_range1 参数中的哪些单元格求和。例如，条件可以表示为 32、">32"、B4、"苹果" 或 "32"。 criteria_range2, criteria2, …可选。附加的区域及其关联条件。最多允许 127 个区域/条件对。 SUMPRODUCT(array1, [array2], [array3], ...) （1 Array1, array2, array3, …为2 到30 个数组，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，否则，函数SUMPRODUCT 将返回错误值#VALUE!。该函数将非数值型的数组元素作为0 处理。（2）SUMPRODUCT函数乘积之和举例 《EXCEL中公式与函数的使用》说课稿 我说课的题目是《EXCEL中公式与函数的使用》,下面我将分七个阶段完成说课:一、说教材,二说学情分析,三说教学目标,四说教学重难点,五说教法与学法,六说教学过程,七说教学效果。 一、说教材 本节课是宁夏教育厅教研室编电子工业出版社出版的《初中信息技术》八年级下册第4单元的内容。 从教材的结构上看,本节课的内容一是公式,二是函数,相对地址与绝对地址的引用贯穿其中,条理清晰,重难点突出;前面是后面内容的基础,后面又是前面内容的完善和深层次发展,环环紧扣。 从地位上看,使用公式与函数是EXCEL中统计数据的重要方面,是EXCEL数据处理的基础,并且它与EXCEL前面所讲的单元格地址紧密联系,又是它的延续。在实际生活当中有很多运算都可以通过EXCEL的公式和函数来解决,实用性很强。 二、说学情分析: 本节课的教学对象是初二的学生,总的来说,学生学习计算机的兴趣浓厚,热情较高,经过初中阶段前面的学习,学生有了信息技术的基本知识和一些软件的简单应用经验,具有一定的思维判断能力,在本节课之前,学生已经掌握了EXCEL的一些基本概念和操作,但对数据处理的认识不多,对逻辑性很强的函数更是知之甚少。 三、说教学目标: 由于本节课内容实用性和操作性较强,依据教材分析和大纲要求,制定如下教学目标: 1.知识与能力目标: 理解EXCEL中公式与函数、相对地址与绝对地址的概念; 掌握公式、常用函数以及自动求和按钮的使用,并能运用其解决一些实际问题,提高应用能力。 2.过程与方法目标: 注重培养学生的数据处理能力,教会学生收集数据、处理数据、分析数据的一般方法,将能力培养渗透到Excel操作技能的教学中。 3.情感态度与价值观目标: 让学生亲身体验EXCEL强大的运算功能,通过系统学习,培养学生科学、严谨的求学态度和不断探究新知识的欲望。 四、说教学重点与难点: 根据大纲,依据本节课的教学目标,确定本节课的教学重点为: 1.公式的使用 2.EXCEL中相对地址与绝对地址的引用 3.常用函数的使用 根据本节课的重点以及以往的教学经验,学生对于相对地址与绝对地址的理解与引用有一定难度,容易出错,而且掌握不好将直接影响公式与函数的正确运算,故确定本节课的教学难点为: 相对地址与绝对地址的概念与引用 五、说教法与学法: 首先说一下教法,这部分内容操作性比较强,操作方法上要求注重细节,要直正体现教师的主导作用和学生的主体地位。在教法上主要采用讲述法,演示法和辅导法, Excel中公式与函数的应用教学设计Application teaching design of formula and fu nction in Excel Excel中公式与函数的应用教学设计 前言：小泰温馨提醒，信息技术是主要用于管理和处理信息所采用的各种技术的总称，主要是应用计算机科学和通信技术来设计、开发、安装和实施信息系统及应用软件。本教案根据信息技术课程标准的要求和针对教学对象是初中生群体的特点，将教学诸要素有序安排，确定合适的教学方案的设想和计划。便于学习和使用，本文下载后内容可随意修改调整及打印。 一、教学目标： 1、知识目标：掌握excel的公式组成格式。理解函数的概念，掌握常见函数如（sum,average）的使用。 2、能力目标：掌握使用函数（sum,average）计算所给数据的求和，求平均值，并且能够根据工作需要修改函数参数，最后达到能够利用所学知识与技能来解决现实生活中所遇到的问题。 3、情感目标：故事情境的导入，激发了学生学习excel电子表格的强烈欲望，在逐一问题得到解决中，感受学习excel电子表格必要性和重要性。在任务的驱动下，激活学生自主学习意识，在任务的完成过程中体会成功的喜悦，并在具体的任务中感受助人为乐的快乐与充实。 二、教学重点、难点： 1、重点：公式格式的输入，sum、average函数的插入和使用。 2、难点：公式格式的修改，函数参数的正确使用以及修改。 三、教学方法： 引导操作，自主探究，任务驱动，互助学习 四、教学素材准备： excel电子表格版的学生成绩单。 五、教学过程 1、情境引入： （1）、刘老师是位有着28年教学经验的老教师，在这28年当中，都担任班主任，工作尽心尽责，深受学生、校领导、家长的好评！然而，随着科学技术的发展，学校从今年起开始步入无纸化办公，面对计算机的使用操作，刘老师感觉心有余而力不足，毕竟老了.如今刘老师要分析学生第一次月考成绩，面对excel电子表格，她向以往填纸制表格一样，用计算器逐个计算，然后再填入表格中，用时大概两个小时。对于这项工作，如果你会操作电子表格，只需两分钟左右就可以解决。同学们，你们想拥有这种能力吗？愿意帮刘老师的大忙吗？ （2）、刘老师要处理的excel电子表格。 （3）、通过观察刘老师要处理的excel电子表格，让学生明确要学习的内容与目的，——引出本节课的学习目标。 2、明确学习目标 （1）、了解公式的概念，掌握公式格式，并使用公式对数据进行处理。 （2）、了解函数的概念，掌握常用函数的使用如：求和函数 sum，求平均值函数 average。 Excel中公式和函数的作用和方法 公式是单个或多个函数的结合运用。 AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。条件判断 A VERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCA TENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DA TE 给出指定数值的日期。显示日期 DA TEDIF 计算返回两个日期参数的差值。计算天数 DAY 计算参数中指定日期或引用单元格中的日期天数。计算天数 DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。条件统计FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。概率计算 IF 根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。条件计算INDEX 返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。数据定位 INT 将数值向下取整为最接近的整数。数据计算 ISERROR 用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。逻辑判断 LEFT 从一个文本字符串的第一个字符开始，截取指定数目的字符。截取数据 LEN 统计文本字符串中字符数目。字符统计 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。匹配位置 MAX 求出一组数中的最大值。数据计算 MID 从一个文本字符串的指定位置开始，截取指定数目的字符。字符截取 MIN 求出一组数中的最小值。数据计算 MOD 求出两数相除的余数。数据计算 MONTH 求出指定日期或引用单元格中的日期的月份。日期计算 NOW 给出当前系统日期和时间。显示日期时间 1. 在excel表格中,当按下回车（ENTER）键结束对一个单元格数据输入时，下一个活动单元格在原活动单元格的。 A．上面 B. 下面 C. 左面 D. 右面 2. 在EXCEL 2010中，表示逻辑值为真的标识符是。 A．F B. T C. FALSE D. TRUE 3. 在EXCEL 2010中，列宽和行高。 A．都可以改变 B.只能改变列宽 C.只能改变行高 D.都不能改变 4. 在EXCEL 2010中，表示逻辑值为假的标识符是。 A．F B.T C. FALSE D.TRUE 5. EXCEL 2010所属的套装软件是。 A．LOTUS 2010 B. Windows 2010 C. Office 2010 D. Word 2010 6. 在EXCEL 2010中，给当前单元格输入数值型数据时，默认为。 A．居中 B.左对齐 C.右对齐 D. 随机 7．EXCEL 2010工作薄文件的默认扩展名为___________。 A．doc B．xls C．ppt D．mdb 8．在EXCEL 2010的一个单元格中，若要输入文字串2008-4-5，则正确的输入为___________。 A．2008-4-5 B．’2008-4-5 C．=2008-4-5 D．“2008-4-5”9．在EXCEL 2010中，数据源发生变化时，相应的图表___________。 A．自动跟随变化B．手动跟随变化 C．不跟随变化 D．不收任何影响 10．在EXCEL 2010的工作表中最小操作单元是___________。 A．单元格B．一行 C．一列 D．一张表 11．在EXCEL 2010中，求一组数值中的平均值函数为___________。 A．AVERAGE B．MAX C．MIN D．SUM 12. 在EXCEL 2010中，假定B2单元格的内容为数值15，C3单元格的内容是 10，则=$B$2-$C$3的值为。 A、25 B、250 C、30 D、5 13. 在EXCEL 2010的单元格格式对话框中，包括的选项卡的个数为。 A、4 B、8 C、6 D、10 14. 在EXCEL 2010中，存储二维数据的表格被称为。 A、工作薄 B、文件夹 C、工作表 D、图表 15. 在EXCEL 2010中，单元格B2的列相对行绝对的混合引用地址为。 A、$B$2 B、$B2 C、B$2 D、B2 16. EXCEL 2010主界面窗口中编辑栏上的“fx”按钮用来向单元格插入。 A、文字 B、数字 C、公式 D、函数 17. 在EXCEL 2010中，对电子工作表的选择区域不能进行的设置是。 A、行高尺寸 B、列宽尺寸 C、条件格式 D、保存 18. Excel 2010工作薄中，要同时选择多个不相邻的工作表，需要依次单击各个工作表的标签前应先按住： A. TAB B. Alt C. Shift D. Ctrl 19. Excel 2010系统中，下列叙述正确的是___________： A. 只能打开一个文件 B. 最多能打开4个文件 C. 能打开多个文件，但不能同时将它们打开 D. 能打开多个文件，并能同时将它们打开 Excel中公式与函数的应用 一、教学目标：1、知识目标：掌握excel的公式组成格式。理解函数的概念，掌握常见函数如（sum,average）的使用。2、能力目标：掌握使用函数（sum,average）计算所给数据的求和，求平均值，并且能够根据工作需要修改函数参数，最后达到能够利用所学知识与技能来解决现实生活中所遇到的问题。3、情感目标：故事情境的导入，激发了学生学习excel电子表格的强烈欲望，在逐一问题得到解决中，感受学习excel 电子表格必要性和重要性。在任务的驱动下，激活学生自主学习意识，在任务的完成过程中体会成功的喜悦，并在具体的任务中感受助人为乐的快乐与充实。二、教学重点、难点：1、重点：公式格式的输入，sum、average 函数的插入和使用。2、难点：公式格式的修改，函数参数的正确使用以及修改。三、教学方法：引导操作，自主探究，任务驱动，互助学习四、教学素材准备： excel电子表格版的学生成绩单。五、教学过程1、情境引入：（1）、刘老师是位有着28年教学经验的老教师，在这28年当中，都担任班主任，工作尽心尽责，深受学生、校领导、家长的好评！然而，随着科学技术的发展，学校从今年起开始步入无纸化办公，面对计算机的使用操作，刘老师感觉心有余而力不足，毕竟老了.如今刘老师要分析学生第一次月考成绩，面对excel电子表格，她向以往填纸制表格一样，用计算器逐个计算，然后再填入表格中，用时大概两个小时。对于这项工作，如果你会操作电子表格，只需两分钟左右就可以解决。同学们，你们想拥有这种能力吗？愿意帮刘老师的大忙吗？（2）、刘老师要处理的 excel电子表格。（3）、通过观察刘老师要处理的excel电子表格，让学生明确要学习的内容与目的，——引出本节课的学习目标。2、明确学习目标（1）、了解公式的概念，掌握公式格式，并使用公式对数据进行处理。（2）、了解函数的概念，掌握常用函数的使用如：求和函数 sum，求平均值函数 average。（3）、能够根据工作需要修改函数参数，最后达到能够利用所学知识与技能来解决现实生活中所遇到的问题。3、新课教学（1）、教学活动之一公式的概念——公式是excel电子表格中进行数值计算的等式。公式的组成格式： =表达式。表达式可包含：有运算符、单元格、常量、函数等。例如： =b2+6， =b2+c2+d2, =sum(参数)在预设置的电子表格——“练兵场1”进行探究，首先通过引导操作，让学生掌握公式的组成及自定义公式的使用，再把时间留给学生，通过自主探究，最终掌握最基本公式组成格式及自定义公式的使用，最后利用自定义公式计算10名学生成绩的总分、平均分。假如：对于某项工作， 50个工作中最常用excel技巧 1、excel判断分数成绩是否及格，怎么做? 答：excel判断分数成绩是否及格可以用IF进行区间判断。 =IF(A1>60,"及格","不及格") 2、excel频率统计用什么函数? 答：FREQUENCY以一列垂直数组返回某个区域中数据的频率分布，具体用法回复frequency或频率查看示例。 3、excel什么时候要用$符号呢? 答：复制公式时，单元格的引用位置不想发生变化时，就在行号或列标前加$，了解详情回复“绝对引用”查看教程 4、合并单元格后里面的数据或文字我都想保留如何处理? 答：多个单元格都含有内容，如果要在合并后保留所有单元格的内容，可以用下面的方法。 1.选取单元格区域，并把列宽拉到可以容下所有单元格合并后的宽度。 2.开始选项卡- 编辑- 两端对齐。把多个单元格的内容合并到一个单元格中 3.在分隔的空隔处按alt+enter键添加强制换行符，换行。 4.再合并单元格 5、插入表格后发现行数不够，怎样才能再次快速插入更多行? 答：需要插入多少行就选取多少行，然后就会一次插入多少空行 6、合并单元格后里面的数据或文字我都想保留如何处理 答：多个单元格都含有内容，如果要在合并后保留所有单元格的内容，可以用下面的方法。 1.选取单元格区域，并把列宽拉到可以容下所有单元格合并后的宽度。 2.开始选项卡- 编辑- 两端对齐。把多个单元格的内容合并到一个单元格中 3.在分隔的空隔处按alt+enter键添加强制换行符，换行。 4.再合并单元格 7、如何复制粘贴行宽 答：粘贴后的粘贴选项中，会有保留源列宽的选项。如下图所示 8、excel如何把数值批量转换成文本 答：数据- 分列- 第三步选文本 9、excel如何把空值全部填写为“0“ 答：定位- 条件- 空值- 在编辑栏中输入0，按ctrl+enter完成输入 10、excel2010的插入特殊符号在哪里? excel2010版本的特殊符号, 很多同学在搜这个问题，可以肯定的说excel2010版本里没有这个命令的。大家不用再费心找了，如果要插入符号，可以从插入选项卡- 符号里找找。在微信平台回复“特殊符号”可以查看更多符号输入方法 11、excel如何去掉地址栏的名称 excel如何去掉地址栏的名称，excel2003版，插入菜单- 名称- 定义- 在弹出的窗口找到该名称点删除按钮，2010版，公式选项卡- 名称管理器-找到名称点删除 12、如何按日期显示星期? 问：excel如何按日期显示成星期 答：公式 =TEXT(A2,"aaaa") =WEEKDAY(A2,2) 也可以通过格式设置把日期显示成星期 13、excel如何隐藏公式? 在excel里，如何隐藏公式，让公式别人看不到呢? 答：在excel里隐藏公式是通过设置单元格格式设置的。 选取公式所在单元格，右键菜单中点设置单元格格式，然后在弹出的单元格格式窗口点保护选项卡，勾选隐藏公式选项。 最后通过工具- 保护-保护工作表(excel2010里是通过审阅- 保护工作表)。然后在单元格或编辑栏里就看不到公式了。 14、excel表格中下拉复制数字时，为什么不变大呢?怎么才能递增? Excel常用函数公式大全 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 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区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格； 【转】 excel常用函数以及用法 2010-11-24 21:35 转载自数学教研 最终编辑数学教研 excel常用函数以及用法 SUM 1、行或列求和 以最常见的工资表（如上图）为例，它的特点是需要对行或列内的若干单元格求和。 比如，求该单位2001年5月的实际发放工资总额，就可以在H13中输入公式： =SUM(H3:H12) 2、区域求和 区域求和常用于对一张工作表中的所有数据求总计。此时你可以让单元格指针停留在存放结果的单元格，然后在Excel编辑栏输入公式"=SUM（）"，用鼠标在括号中间单击，最后拖过需要求和的所有单元格。若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。对于需要减去的单元格，则可以按住Ctrl键逐个选中它们，然后用手工在公式引用的单元格前加上负号。当然你也可以用公式选项板完成上述工作，不过对于SUM函数来说手工还是来的快一些。比如，H13的公式还可以写成： =SUM(D3:D12,F3:F12)-SUM(G3:G12) 3、注意 SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。对需要参与求和的某个常数，可用"=SUM（单元格区域，常数）"的形式直接引用，一般不必绝对引用存放该常数的单元格。 SUMIF SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。 仍以上图为例，在工资表中需要分别计算各个科室的工资发放情况。 要计算销售部2001年5月加班费情况。则在F15种输入公式为 EXCEL2010常用函数应用 在此比如SUM、AVERAGE、MAX、MIN等简单常用函数不再进行讲解 1.单条件求和——SUMIF 函数 如要统计C 列中的数据, 要求统计条件是B 列中数据为" 条件一" 。并将结果放在C6 单元格中，我们 只要在C6 单元格中输入公式“=SUMIF（B2：B5，" 条件一" ，C2：C5）”即完成这一统计。 2.符合多个条件的数据求和——SUMIFS 函数 统计5 班中语文名次、数学名次均位于前20 名同学的总分之和。在相应单元格内输入公式 “=SUMIFS(M2:M80,A2:A80,"5",D2:D80,"<=20",F2:F80,"<=20")”，按下回车键，就一切OK。如图所示。 3.去尾取整函数——INT 函数注意要区分与TRUNC函数（只取整），而INT取向 下整数。 4.取整函数，只取整数。 例：=TRUNC（8.9），结果为8；=TRUNC（-9.6），结果为-9； 5.四舍五入函数——ROUND 函数 6.向下舍数字函数——ROUNDDOWN 函数 ⑴功能按指定的位数对数值进行舍入。 ⑵格式ROUNDDOWN(数值或数值单元格，指定的位数) ⑶示例 ?=ROUNDDOWN(A2,-2)答安为300 7.VLOOKUP 函数的使用方法 VLOOKUP 是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为： VLOOKUP（查找目标，查找范围，返回值的列数，精确OR 模糊查找) 下面以一个实例来介绍一下这四个参数的使用 例1：如下图所示，要求根据表二中的姓名，查找姓名所对应的年龄 Excel公式与函数教学设计教学案例 《Excel公式与函数》是教科版高中《信息技术》必修教材第四章第二节第一课时的教学内容。在初中阶段，学生对Excel有一定的了解,本节课的设计正是在学生有一定基础的情况下，加深学生对电子表格数据处理的认识，强化学生对Excel公式与函数的使用，增强学生实际动手操作解决问题的能力。 一、教学目标知识与技能：掌握公式与函数的使用方法，理解相对引用和绝对引用。过程与方法：通过使用公式与函数，培养学生发现问题、分析问题和解决问题的能力。情感、态度与价值观：通过管理身边的信息资源，体会利用电子表格软件管理信息的基本思想，并在科学管理信息的过程中，体验有效管理数据的重要性，形成科学管理信息的习惯，增强环保意识。 二、教学重点与难点重点：正确使用公式与函数。难点：理解相对引用和绝对引用。 三、教学过程 1.创设情境导入新课师：先让我们欣赏一段精彩的影片，放松一下紧张的神经。学生津津有味地欣赏电影片段。师：谁能告诉我们这段电影描述的是什么？生：这是电影《后天》的片段，讲述由于全球气候变暖带来的灾难性场面。师：这样的灾难性场面令人触目惊心，幸好它只是科学幻想。然而，随着哥本哈根会议的召开，我们应该正视二氧化碳大量排放带来全球变暖的问题。在现实生活中,我们实际能感受到的全球变暖的现象有哪些呢？学生观看教师准备的几张图片，了解现实生活中全球变暖带来的实际问题。师：我们日常生活中无时无刻不和二氧化碳排放量打交道，阻止全球变暖，低碳生活是我们每个人义不容辞的责任。展示一组关于家庭使用水、电、天然气和汽油的数据，引出课题，请学生计算这组家庭的碳排放量。设计思想：通过视频和图片，强烈的视觉冲击力，激发学生的环保意识，鼓励学生从我做起，利用身边的信息，了解二氧化碳排放量的信息。 2.注重基础活学活用教师介绍可以通过转换系数计算出水、电、天然气和汽油使用量对应的二氧化碳排放量。演示使用公式计算A101家庭使用自来水所对应的二氧化碳排放量。在演示过程中，强调公式必须以“=“开头，通过单元格的名称引用单元格中的内容，提示学生注意编辑公式与编辑完成时单元格与编辑栏内的变化。编辑公式时，单元格与编辑栏同步显示正在编辑的公式；编辑完成后，单元格显示公式计算出的结果，而编辑栏中显示公式的原貌。师：完成了一户家庭的自来水对应碳排放量后，如何快速地计算出其余家庭的自来水对应碳排放量？Excel拥有强大的自动完成功能，使用填充手柄可以实现快速计算。教师演示自动完成的使用。学生观察教师的操作，理解填充柄的工作原理。教师布置任务1，请学生完成表格中10户家庭水、电、天然气和汽油使用量所对应的碳排放量。学生动手完成计算。教师巡视学生操作情况，指导个别基础较差的学生，引导完成速度快的学生尝试计算表格中的碳排放总量。师：小结公式的作用是用户根据自己的需要编制的用以完成特定计算用的，对于一些常用的较复杂计算，系统已经预先设定好了相应的计算方法，称为“函数“。刚刚有部分同学使用公式计算了表格中的碳排放总量，其实我们也可以使用sum( )函数进行计算。介绍几个常用函数名，average( )计算一组数的平均值，max( )计算一组数中的最大值，min( )计算一组数中的最小值。教师提示学生插入函数的方法是，选择“插入“菜单中的“函数“，然后选择所需要的函数。教师布置任务2，请学生使用函数计算碳排放总量和各项碳排放量的均值。学生尝试使用sum( )函数和average( )函数。教师巡视，注意观察学生操作中的问题。学生代表演示使用sum( )函数和average( )函数计算的过程。教师小结函数参数的设定方法。方法一：直接逐个填在对应的number1，number2··中。方法二：用鼠标点击选择不连续的数，这些数全部显示在number1中，数之间用逗号隔开。方法三：用鼠 电子表格常用函数公式 1、自动排序函数： =RANK(第1数坐标，$第1数纵坐标$横坐标：$最后数纵坐标$横坐标，升降序号1降0升) 例如：=RANK(X3,$X$3:$X$155,0) 说明：从X3 到X 155自动排序 2、多位数中间取部分连续数值： =MID（该多位数所在位置坐标，所取多位数的第一个数字的排列位数，所取数值的总个数） 例如：6127315在B4坐标位置，取中间出生年月日，共8位数 =MID(B4，7，8) =19820711 说明：B4指该数据的位置坐标，7指从第7位开始取值，8指一共取8个数字 3、若在所取的数值中间添加其他字样， 例如：6127315在B4坐标位置，取中间出生年、月、日，要求****年**月**日格式 =MID(B4，7，4)&〝年〞&MID(B4，11，2) &〝月〞& MID(B4，13，2) &〝月〞& =1982年07月11日 说明：B4指该数据的位置坐标，7、11指开始取值的第一位数排序号，4、2指所取数值个数，引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板：首先利用Word制作一个奖状模板并保存为“奖状.doc”，将其中班级、姓名、获奖类别先空出，确保打印输出后的格式与奖状纸相符（如图1所示）。 第二步用Excel建立获奖数据库：在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”，格式如图2所示。 第三步关联数据库与奖状：打开“奖状.doc”，依次选择视图→工具栏→邮件合并，在新出现的工具栏中选择“打开数据源”，并选择“奖状数据.xls”，打开后选择相应的工作簿，默认为sheet1，并按确定。将鼠标定位到需要插入班级的地方，单击“插入域”，在弹出的对话框中选择“班级”，并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印：选择“查看合并数据”，然后用前后箭头就可以浏览合并数据后的效果，选择“合并到新文档”可以生成一个包含所有奖状的Word文档，这时就可以批量打印了。 电子表格常用函数公式及用法 1、求和公式： =SUM(A2:A50) ——对A2到A50这一区域进行求和； 2、平均数公式： =AVERAGE(A2:A56) ——对A2到A56这一区域求平均数； 3、最高分： =MAX(A2:A56) ——求A2到A56区域（55名学生）的最高分；4、最低分： =MIN(A2:A56) ——求A2到A56区域（55名学生）的最低分； 5、等级： =IF(A2>=90,"优",IF(A2>=80,"良",IF(A2>=60,"及格","不及格"))) 6、男女人数统计： =COUNTIF(D1:D15,"男") ——统计男生人数 =COUNTIF(D1:D15,"女") ——统计女生人数 7、分数段人数统计： 方法一： 求A2到A56区域100分人数：=COUNTIF(A2:A56,"100") 求A2到A56区域60分以下的人数；=COUNTIF(A2:A56,"<60") 求A2到A56区域大于等于90分的人数；=COUNTIF(A2:A56,">=90") 求A2到A56区域大于等于80分而小于90分的人数； =COUNTIF(A1:A29,">=80")-COUNTIF(A1:A29," =90") 求A2到A56区域大于等于60分而小于80分的人数； =COUNTIF(A1:A29,">=80")-COUNTIF(A1:A29," =90") 方法二： （1）=COUNTIF(A2:A56,"100") ——求A2到A56区域100分的人数；假设把结果存放于A57单元格； （2）=COUNTIF(A2:A56,">=95")－A57 ——求A2到A56区域大于等于95而小于100分的人数；假设把结果存放于A58单元格；（3）=COUNTIF(A2:A56,">=90")－SUM(A57:A58) ——求A2到A56区域大于等于90而小于95分的人数；假设把结果存放于A59单元格； （4）=COUNTIF(A2:A56,">=85")－SUM(A57:A59) ——求A2到A56区域大于等于85而小于90分的人数； …… 8、求A2到A56区域优秀率：=(COUNTIF(A2:A56,">=90"))/55*100 9、求A2到A56区域及格率：=(COUNTIF(A2:A56,">=60"))/55*100 10、排名公式： =RANK(A2，A$2:A$56) ——对55名学生的成绩进行排名； 11、标准差：=STDEV(A2:A56) ——求A2到A56区域(55人)的成绩波动情况（数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化）； 12、条件求和：=SUMIF(B2:B56,"男"，K2:K56) ——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班 1. Excel中，“<>”运算符表示________。 A.小于或大于 B.不等于 C.不小于 D.不大于 2. 设当前工作表的C2单元格已完成了公式的输入，下列说法中，________是错的。 A.未选定C2时，C2显示的是计算结果 B.单击C2时，C2显示的是计算结果 C.双击C2时，C2显示的是计算结果 D.双击C2时，C2显示的是公式 3. 在Excel中，在单元格中输入 =12>24 ，确认后，此单元格显示的内容为________。 B.=12>24 >24 4. Excel中，若单元格C1中公式为=A1+B2，将其复制到单元格E5，则E5中的公式是________。 A.=C3+A4 B.=C5+D6 C.=C3+D4 D.=A3+B4 5. 在Excel中，在单元格中输入 =”12”&”34”，确认后，此单元格显示的内容为________。 +34 D.=”12”+”24” 6. 单击Excel菜单栏的“________”菜单中的“函数”命令，可弹出“插入函数”对话框查到Excel的全部函数。 A.文件 B.插入 C.格式 D.查看 7.在Excel中，________函数可以计算工作表中一串数值的和。 单元格的数据值为60，C3的内容为“=IF(B3<60,"不及格","及格")”，该公式运算后将在C3单元格显示出“_______”。 B.及格 C.不及格 9.在Excel中编辑公式时，按一下_______键，公式中相应单元格的引用方式就被设置成了绝对引用。 10.在Excel中，位于工作表第8行和H列相交的单元格的绝对地址表示为________。 A.$8$H $H $8 D.$H$8 11.在Excel工作表中，不正确的单元格地址是________。 $66 B.$C66 $6 D.$C$66 12.在Excel中，求两个单元格区域A4:E9与B5:D10相交的单元格区域内各数值的平均值，采用相对地址引用，应该表示为________。 (A4:E9,B5:D10) (A4:E9;B5:D10) (A4:E9:B5:D10) (A4:E9 B5:D10) SUMSQ平方和 SQRT平方根 excel常用函数以及用法 SUM 1、行或列求和 以最常见的工资表（如上图）为例，它的特点是需要对行或列内的若干单 元格求和。 比如，求该单位2001年5月的实际发放工资总额，就可以在H13中输入公 式： =SUM(H3:H12) 2、区域求和 区域求和常用于对一张工作表中的所有数据求总计。此时你可以让单元格指针停留在存放结果的单元格，然后在Excel编辑栏输入公式"=SUM（）"，用鼠标在括号中间单击，最后拖过需要求和的所有单元格。若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。对于需要减去的单元格，则可以按住Ctrl键逐个选中它们，然后用手工在公式引用的单元格前加上负 号。当然你也可以用公式选项板完成上述工作，不过对于SUM函数来说手工还是来的快一些。比如，H13的公式还可以写成： =SUM(D3:D12,F3:F12)-SUM(G3:G12) 3、注意 SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel 就会提示参数太多。对需要参与求和的某个常数，可用"=SUM（单元格区域，常数）"的形式直接引用，一般不必绝对引用存放该常数的单元格。 SUMIF SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。 仍以上图为例，在工资表中需要分别计算各个科室的工资发放情况。 要计算销售部2001年5月加班费情况。则在F15种输入公式为 =SUMIF($C$3:$C$12,"销售部",$F$3:$F$12) 计算机二级excel中常考公式及讲解EXCEL2010常用函数应用 1.单条件求和——SUMIF 函数 如要统计C列中的数据, 要求统计条件是B列中数据为"条件一"。并将结果放在C6单元格中，我们只要在C6单元格中输入公式“=SUMIF （B2：B5，" 条件一"，C2：C5）”即完成这一统计。 2.符合多个条件的数据求和——SUMIFS 函数 统计5班中语文名次、数学名次均位于前20 名同学的总分之和。在相应单元格内输入公式 “=SUMIFS(M2:M80,A2:A80,"5",D2:D80,"<=20",F2:F80,"<=20")”，按下回车键，就一切OK。 如图所示。 ⑴功能按指定的位数对数值进行舍入。 ⑵格式ROUNDDOWN(数值或数值单元格，指定的位数) ⑶示例12.351325.52512.35325.522.舍去小数位保留整数——舍去十分位及以后的小数位保留整数123251032003.整数保留到十位——整数部分舍去个位上大于0的数字 =ROUNDDOWN(A1,-1)=ROUNDDOWN(A2,-1)=ROUNDDOWN(A2,-2)1.保留2位小数——舍去千分位以后的小数位保留到百分位 =ROUNDDOWN(A1,2)=ROUNDDOWN(A2,2)=ROUNDDOWN(A1,0) =ROUNDDOWN(A2,0) 7.1LOOkUP函数使用方法 LOOKUP(MID(A2,3,2),{"01","02 WECHAT_EMPTY_TITLEhttps://https://www.doczj.com/doc/4710438917.html,/ba5fe3dfdd5ca2edc8 3e9bd55-2.html ","03"},{"1班","2班","3班"})意思为如果A2单元格从第三个字符起取两个字符与数组中{"01","02","03"}中第某项相同,则返回另一个数组{"1班","2班","3班"}中的第某项内容假设A2单元格的内容为220345 则MID(A2,3,2) 返回"03"而"03"是{"01","02","03"}中的第三项,所以公式返回{"1班","2班","3班"}中的第三项即"3班"这个公式可以简写成LOOKUP(MID(A2,3,2),{1,2,3},{"1班","2班","3班"})或 =MID(A2,3,2)&"班" 1. 以一份学生成绩表为例，要查询的是得分76的学生的姓名 一、SUM（）：求和 语法：SUM(number1,number2, ...) 参数：number1，number2，...为1到30个需要求和的参数。 说明：1、number1，number2，...，也可以是引用区域。 2、注意：30个是参数，而不是30个数。 二、COUNT（）：统计数据值的数量。 语法：COUNT(value1,value2, ...) 参数：Value1, value2, ... 是包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计数。 说明：函数COUNT在计数时，将把数值型的数字计算进去；但是错误值、空值、逻辑值、日期、文字则被忽略。 三、COUNTIF（）：统计某个单元格区域中符合指定条件的单元格数目。 语法：COUNTIF（Ranger, Criteria） 说明：Ranger表示要统计的单元格区域；Criteria表示指定的条件表达式，这个表达式一定要加英文双引号。 四、MAX（）：求最大值。参数的用法请参考SUM() 五、MIN（）：求最小值。参数的用法请参考SUM() 六、PRODUCT（）：求数值乘积。参数的用法请参考SUM() 七、AND（）：与。其所有参数为TRUE，则返回TRUE，否则，返回FALSE。如：=AND(3>5，5>3)，返回FALSE 八、IF（）：指定要执行的逻辑检验，根据逻辑值的真假，返回不同结果。 语法：IF(logical, value_if_true, value_if_false) Logical表示结果为TRUE 或FALSE 的任意值或表达式 注意：嵌套最多七层。 九、NOT（）：对其参数逻辑值求反。如：=not(3>5)，结果为TRUE。 十、OR（）：或。只要有一个参数为真，则返回TRUE。如：=OR(3>5， 5>3)，返回TRUE。 十一、A VERAGE（）：求平均。参数的用法请参考SUM() 公式和常用函数 Excel 2010的功能和使用 目录 一.Excel公式—格式及运算符 (1) 1．公式的格式 (1) 二.Excel公式—使用方法 (3) 1. 公式的输入和修改 (3) 三.Excel公式—名称的定义和引用 (4) 1. 单元格引用 (4) 2．定义与引用名称 (5) 四.Excel函数—定义和分类 (8) 1.函数的定义 (8) 五.Excel函数—数值函数 (10) 六.Excel函数—求和函数 (11) 七.Excel函数—平均值函数 (12) 八.Excel函数—Sumproduct函数 (13) 九.Excel函数—计数函数 (13) 十.Excel函数—Rank函数 (13) 十一.Excel函数—文本类函数 (14) 十二.Excel函数—Vlookup函数 (15) 十三.Excel函数—IF函数 (16) 十四.Excel函数—日期时间函数 (16) 十五.公式与函数常见问题 (17) 十六 .其他重要函数 (19) 一.Excel公式—格式及运算符 1．公式的格式 公式是Excel一项强大的功能，利用公式可以方便快捷的对复杂的数据进行计算。在Excel中，公式始终以“=”开头，公式的计算结果显示在单元格中，公式本身显示在编辑栏中。公式一般由单元格引用、常量、运算符、函数等组成。 例如： =A2+F2 =2015+2022 =SUM（A3：C3） =IF（C2>2015,"TRUE","FALSE"） λ单元格引用：即前面提到单元格地址，表示单元格在工作表上所处的位置。例如A列中的第2行，则表示为“A2”。 λ常量：指固定的数值和文本，此常量不是经过计算得出的值，例如数字“125”和文本“一月”等都是常量。表达式或由表达式计算出的值不属于常量。 λ运算符：下个知识点具体介绍。 λ函数：函数是Excel中预先编写的公式，后面节将具体介绍。 2．公式中的运算符 运算符一般用于连接常量、单元格引用，从而构成完整的表达式。公式中常用的运算符包括算术运算符、关系运算符、文本连接运算符、引用运算符。 （1）算术运算符 用于完成基本的数学运算，如加法、减法和乘法等，算术运算符名称与用途如表3-1所示。Excel2010常用函数
《EXCEL中公式与函数的使用》说课稿[1]概况
Excel中公式与函数的应用教学设计
Excel中公式和函数的作用和方法
计算机基础(Excel2010)试题及答案
Excel中公式与函数的应用.doc
50个工作中最常用excel技巧
Excel常用函数公式大全(实用)
excel表格常用函数及用法
EXCEL 2010 常用函数应用
Excel公式与函数 教学设计 教学案例
电子表格常用函数公式
电子表格常用函数公式及用法
excel公式与函数练习题
最全excel表格常用函数及用法共27页
计算机二级excel中常考公式及讲解
excel2010常见函数格式参数
计算机二级excel中常用函数知识点
相关主题
文本预览