EXCEL函数学习Word格式.docx

EXCEL函数学习Word格式.docx

《EXCEL函数学习Word格式.docx》由会员分享，可在线阅读，更多相关《EXCEL函数学习Word格式.docx（24页珍藏版）》请在冰豆网上搜索。

4　王五　　三年五　女　　　33

5　李六　　三年五　女　　　46

　　现在求求出三年五班女生的总余额。

　　公式：

=SUMPRODUCT（（B2:

B5="

三年五"

）*（C2:

C5="

女"

）*（D2:

D5））

　　解释：

SUMPRODUCT在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

　　语法SUMPRODUCT（array1,array2,array3,...）　Array1,array2,array3,...为2到30个数组，其相应元素需要进行相乘并求和。

　　在这里（（B2:

D5））为一个数组，其中（B2:

）为确定满足准备求和条件的单元格所在的行，*（D2:

D5）则是满足求和条件的单元格的范围。

　　第三个函数不常用，我对它也不是很了解，为尊重作者原著，我只有把他的内容贴出来，请见谅。

　　其实上面的第二个方式（sum函数嵌套if函数）和第三个方式（sumproduct函数）不但可以实现双条件求和，也可以实现三条件求和甚至多条件求和的。

只不过函数稍微变更一下的。

　　四、除了上述三个方式外，很多朋友也喜欢用条件过滤、排序、自动筛选、分类汇总等方式来进行“条件求和”的，也是非常简单的，我不叙述了，另外我还推荐一个在EXCEL2007中的新函数。

　　五、SUMIFS函数

　　该函数在EXCEL2007中被广泛使用，使用起来更加方便，例如第三个方式中的例子，我们就可以输入公式：

“=SUMIFS（D2:

D5,B2:

B5,"

C2:

C5,"

）”，是不是更轻松？

Excel2003中复制行数据到列中

在整理表格数据的时候，经常会用到行列转置的情况。

当你需要将行数据转置存放于列中时，可以考虑用今天的方法来复制数据。

　　步骤如下：

　　①首先，选中该行数据，将其复制；

　　②将鼠标选中希望从某列开始地方的单元格中，如下图中，我希望从A3单元格开始；

　　③单击菜单栏中的“编辑”，在弹出菜单中选择“选择性粘贴”；

　　④在弹出的“选择性粘贴”窗口中将最下面的“转置”勾选，最后单击确定按钮即可。

　　此时，这些数据就会从行排序，变成列排序了。

当然，我们也可以用同样的方法，将列中数据复制到行中。

Excel的文档密码破解之谜

经常和办公软件打交道的人肯定知道，如果把加密文档的密码忘了就相当于将这个文件报废了,那将会是一件多么可怕的事情。

因此在网上，就经常有类似——求助如何破解Excel文件密码之类的问题，而回答的答案，基本都是一款名为officePasswordRemover软件，声称可以轻松破解Excel等Office文件的密码，那我们来看下这个OfficePasswordRemover到底是什么？

首先我们在网上下载一个OfficePasswordRemover，运行解压好的OPRemove.exe这个程序，打开程序后可以看到中间文本框后面有个类似文件夹的按钮，点击它浏览打开要破解的文档，选择后点击移除密码，这时软件会提示需要连接到Internet，因为要给该软件的服务器发送数据并解密。

不过大家可以放心，软件不会泄露任何个人隐私，之后呆几秒钟后便出现“该文档已经被成功破解”，点确定即可，这时候就大功告成了。

在这里要注意的是，破解后的文章名字会有点变化，加了“DEMO”这四个字母，翻译过来就是示范的意思，不必在意，重命名去掉就可以了。

文章至此，读者可能又有下面的问题了，难道Excel文件的密码就这么不经破解，而大家的文件都没有安全保证了？

其实这个软件只能破解Excel默认加密方式下的文件密码。

当我们在设定密码时，在Excel的工具/选项的安全性中，打开权限密码的后面有一个高级按钮，把加密码的方式选择RC4开头的任一加密方式，之后再保存文件，这时再用这个软件破解时，就会提示——程序无法恢复当前文档的打开密码。

该文件使用非标准的CSP加密码（代码0x2）。

由此可见，Excel的打开密码不是传说中的那么脆弱，只是默认的加密方式容易被破解罢了，而大部份的用户又不了解这一点，而使类似OfficePasswordRemover之类的软件就能破解其文件的密码。

当你用了非默认的加密方法后，如果这时密码忘记了，那……技术就是双刃剑，成也是它，败也是它。

Excel2003中如何快速辨别重复数据！

Excel中有自动分辨哪些是相同数据的公式，你知道吗？

学会应用他们可以帮助你整理数据的时候很快的帮你找到重复的数据，今天blue1000跟大家分享的就是Excel2003中如何快速辨别重复数据？

　　方法一、

　　Excel分别对AB列两列数据对比，比如A2=B2，就返回相同，否则返回不相同。

　　C2公式为：

=IF（A2=B2,"

相同"

"

不同"

），这样就可以实现excel两列对比。

　　不过细心的朋友会发现，A5和B5实质上有大小写区分的，因此使用这个公式不是完全准确。

Excel中exact函数可以完全区分大小写，因此C2公式可以更改为：

=IF（EXACT（A2,B2）=TRUE,"

），然后下拉复制公式，完成excel两列数据对比。

　　方法二、

　　AB两列都是客户的姓名，需要找到两列重复的客户名称，并标示出来。

　　C1单元格输入数组公式：

=IF（COUNTIF（A:

A,B1）=0,"

不重复"

"

），然后下拉完成excel两列数据对比。

　　方法三、

　　在Excel中通过比较两列中的数据来查找重复项。

如果A列的数据没有在B列出现过，就保留单元格为空。

如果A列的数据在B列出现过，就返回A列对应的数据。

　　C1输入公式：

=IF（ISERROR（MATCH（A1,$B$1:

$B$5,0））,"

A1）。

Match部分得到的结果是#N/A或者数字，然后用ISERROR函数，将#N/A错误值进行处理，ISERROR（#N/A）得到TRUE，ISERROR（数字）得到false，最外面用IF函数来进行判断，如果第一参数是true，就执行第二参数，否则执行第三参数。

这个应用是巧用excel两列对比，完成查找重复项。

　　方法四、

　　Excel中用vlookup函数来对比两列。

　　B1单元格公式为：

=IF（ISNA（VLOOKUP（A1,$C$1:

$C$6,1,））,"

←A有C无"

←AC共有"

）

　　D1单元格公式为：

=IF（ISNA（VLOOKUP（C1,$A$1:

$A$6,1,））,"

←C有A无"

←CA共有"

　　然后下拉复制公式，完成Excel两列对比。

如何将简介说明文字添加在Excel2003的单元格中

给重要的单元格添加简介和说明，这样可以让我们很轻松的找到要找的东西。

只要将鼠标放到某个单元格上就会显示我们初始时填写的简介说明文字了。

可以避免数据太多导致找数据时的困难。

blue1000教你如何将简介说明文字添加在Excel2003的单元格中。

　　在Excel2003单元格中添加简介说明文字方法：

　　①打开表格，选中需要添加简介说明的单元格，然后右键点击，选择“插入批注”；

　　②在弹出的“插入批注”中输入简介说明文字即可完成。

　　添加好Excel批注后，你只需要将鼠标放到该单元格上就会自动显示你先前输入的简介说明文字了。

　　删除批注方法：

　　选中添加过简介说明的单元格，单击右键，在弹出菜单中选择“删除批注”即可。

Excel表格数字转汉字大写的方法

转换方法：

　　①选中需要转换的单元格，单击“菜单栏”的“格式”，在下来菜单中选择“单元格”命令；

　　②在弹出的“单元格格式”对话框中“数字”下面的的“分类”里面选择“自定义”；

　　③在右边的“类型”中输入：

[dbnum2]0000年00月00日；

　　此时，20120205就变成了贰零壹贰年零贰月零伍日。

如何在Excel2003中快速设置表格背景以及边框的颜色

　①首先，选中需要设置背景和边框颜色的单元格，你可以全部选中，也可以按住Ctrl加鼠标左键选中个别单元格；

　　②单击菜单栏的“格式”，在弹出菜单中选择“单元格”命令；

　　③在弹出的“单元格格式”窗口中选择“边框”，然后在“颜色”中设置自己想要的颜色，再点击“外边框”按钮，就可以给单元格加上边框了；

　　④上面步骤以及设置了边框颜色，下面我们在进入“图案”中给单元格设置表格背景颜色（如下图）确定即可。

EXCEL表格如何画两个斜线？

在使用Excel表格中，中没有“绘制斜线表头”功能，但是我们却经常要用到斜线表头，为此常常使人郁闷。

其实，在Excel中制作斜线表头也有很多方法。

blue1000这里就给大家介绍三种Excel斜线表头的画法（下面均以Excel2000为例进行介绍）。

方法一：

利用单元格格式中的边框实现

1.利用单元格中分行的效果：

首先在单元格中输入“性别”，这时候如果按回车键的话，光标就会转到其他单元格中去，所以必须按“Alt+回车键”，光标转到下一行，接着输入“姓名”，然后再在“性别”前加入若干空格（或插入文本框分别输入性别、姓名），达到效果。

2.在单元格中设置斜线：

选择单元格，在右键菜单中选择“设置单元格格式”，在出现的对话框中选择“边框”标签，进行斜线的设置。

优势：

能够比较迅速地绘出标准的斜线表头。

劣势：

只能在单元格中画出对角斜线，并不能画出任意斜线。

织梦好，好织梦

方法二：

利用绘图工具实现，这也是大家比较常用的一种方式。

首先在Excel中选择一个单元格，然后打开Excel的“绘图”工具栏，单击该工具栏的“直线”图标，将所需要的线段全部画在工作表上，并调整好合适的位置，可以为表格创建出任意样式的斜线表头来。

下面我们就说明具体制作。

1.制作字符文本框注意：

为了能在斜线区域中方便地调整字符位置，使字符在斜线区域中合理地布局，需要将每一文本框中设定只有一个字符。

其原因也很简单，这样能尽可能减小表头空间大小，从而将表头做得更加紧凑。

使用“方框”按钮可以方便地在斜线上下方添加文字，但文字周围有边框，要想取消，可选中文字框，调出快捷菜单，选择“对象格式”→“图案”，选择“无边框”即可。

2.创建直线对象单击“绘图”按钮，选取“直线”，鼠标变成十字形状，将直线移至要添加斜线的开始位置，按鼠标左键拖动至终止位置，释放鼠标，斜线就画出来了。

3.组合斜线表头在组合斜线表头时，最好按照从上到下的次序进行。

首先，将一条直线放入表头中，单击鼠标使直线出现编辑点，然后分别拖动线段两端的编辑点，将直线与斜线表头表格线上的相应位置相重合，然后在该线的中段产生一个新的编辑点，将直线拖放到表格线的相应位置上；

这样斜线区域就设置好了；

最后，再添加该斜线区域中的标题字符或图像，这一区域的全部操作就完成了。

copyrightdedecms

能够实现各种不规则的斜线表头。

对于初学者来说难度颇高，需要一定的技巧。

方法三：

粘贴word中已制好的斜线表头

对于Word中有多种添加斜线表头的方式，如“表格”菜单中的“绘制斜线表头”即有多种样式，也可按上述方法操作绘制斜线表头，完成大家熟悉的表头制作后，复制到Excel中，同样可达到相应的效果。

这种方法最为简便，也很省力。

Word只提供了五种斜线表头样式，选择范围有限。

Excel表格中超链接批量取消的方法

　Excel表格中有时候会有超链接，对自己没有用的超链接很烦人，只有几个超链接的话还好说，但是如果遇到那种大面积的超链接怎么办呢？

本文就来教你Excel表格中超链接批量取消的方法。

Excel表格中取消超链接的方法非常简单，我们只需选中链接后单击右键，然后在弹出菜单中选择“取消超链接”即可。

但如果表格中出现了大量的超链接，我们又该如何取消呢？

若一个一个的手动去取消那就太浪费时间了，有什么好的法子可以一次性将这些超链接全部取消呢？

这里blue1000告诉大家一个好方法，利用“宏”来批量取消超链接。

　　本次演示版本使用Excel2003

　　Excel如何批量取消超链接

　　①单击菜单栏中的“工具”按钮，选择“宏”-->

宏；

　　②在弹出的“宏”窗口中的“宏名”中输入“批量取消超链接”再单击“创建”按钮；

　　③此时会进入Excel模块界面中：

　　　1、在“Sub批量取消超链接（）”与“EndSub”之间输入代码：

“Cells.Hyperlinks.Delete”；

　　　2、单击“保存”按钮，将其保存起来；

　　　3、单击“视图Microsoft 

Excel”返回到Excel界面中；

　　④返回到Excel界面中后，单击菜单栏的“工具”-->

宏-->

宏，在窗口中选择先前创建的“批量取消超链接”宏，单击“执行”按钮即可。

　　此时，Excel表格中所有的超链接就会全部被取消了。

Excel最常用的18个公式运算技巧

大家经常在用Excel处理表格和数据的过程中，会用到公式和函数，本文我们就为大家总理整理一18个Excel常用公式及运算技巧使用方法，希望对大家有所帮助。

　　一、查找重复内容公式：

AA2）>

1”重复””"

）。

　　二、用出生年月来计算年龄公式：

=TRUNC（（DAYS360（H6”2009/8/30″FALSE））/3600）。

　　三、从输入的18位身份证号的出生年月计算公式：

=CONCATENATE（MID（E274）”/”MID（E2112）”/”MID（E2132））。

　　四、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：

=IF（LEN（C2）=15IF（MOD（MID（C2151）2）=1”男””女”）IF（MOD（MID（C2171）2）=1”男””女”））公式内的“C2”代表的是输入身份证号码的单元格。

　　五、求和：

=SUM（K2:

K56）——对K2到K56这一区域进行求和；

　　六、平均数：

=AVERAGE（K2:

K56）——对K2K56这一区域求平均数；

　　七、排名：

=RANK（K2，K$2:

K$56）——对55名学生的成绩进行排名；

　　八、等级：

=IF（K2>

=85”优”IF（K2>

=74”良”IF（K2>

=60”及格””不及格”）））

　　九、学期总评：

=K2*0.3+M2*0.3+N2*0.4——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩；

　　十、最高分：

=MAX（K2:

K56）——求K2到K56区域（55名学生）的最高分；

　　十一、最低分：

=MIN（K2:

K56）——求K2到K56区域（55名学生）的最低分；

　　十二、分数段人数统计：

（1）=COUNTIF（K2:

K56”100″）——求K2到K56区域100分的人数;

假设把结果存放于K57单元格；

（2）=COUNTIF（K2:

K56”>

=95″）-K57——求K2到K56区域95～99.5分的人数;

假设把结果存放于K58单元格；

　　（3）=COUNTIF（K2:

=90″）-SUM（K57:

K58）——求K2到K56区域90～94.5分的人数;

假设把结果存放于K59单元格；

　　（4）=COUNTIF（K2:

=85″）-SUM（K57:

K59）——求K2到K56区域85～89.5分的人数;

假设把结果存放于K60单元格；

　　（5）=COUNTIF（K2:

=70″）-SUM（K57:

K60）——求K2到K56区域70～84.5分的人数;

假设把结果存放于K61单元格；

　　（6）=COUNTIF（K2:

=60″）-SUM（K57:

K61）——求K2到K56区域60～69.5分的人数;

假设把结果存放于K62单元格；

　　（7）=COUNTIF（K2:

K56”<

60″）——求K2到K56区域60分以下的人数;

假设把结果存放于K63单元格；

　　说明：

COUNTIF函数也可计算某一区域男、女生人数。

　　如：

=COUNTIF（C2:

C351”男”）——求C2到C351区域（共350人）男性人数；

　　十三、优秀率：

=SUM（K57:

K60）/55*100

　　十四、及格率：

K62）/55*100

　　十五、标准差：

=STDEV（K2:

K56）——求K2到K56区域（55人）的成绩波动情况（数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化）；

　　十六、条件求和：

=SUMIF（B2:

B56”男”，K2:

K56）——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；

　　十七、多条件求和：

{=SUM（IF（C3:

C322=”男”IF（G3:

G322=110）））｝——假设C列（C3:

C322区域）存放学生的性别，G列（G3:

G322区域）存放学生所在班级代码（1、2、3、4、5），则此函数返回的结果表示求一班的男生人数;

这是一个数组函数，输完后要按Ctrl+Shift+Enter组合键（产生“{……｝”）。

“{｝”不能手工输入，只能用组合键产生。

　　十八、根据出生日期自动计算周岁：

=TRUNC（（DAYS360（D3NOW（）））/3600）

　　———假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。

Excel2007如何给表格加密让他人无法修改

在制作一些重要报表时，我们常常需要给Excel表格加密或者设置成只读属性来加以防护这些重要表格。

只有通过输入正确的密码后才可以正常打开。

设置成只读的话，可以预防他人修改文件，只能读取表格，今天来给大家讲解下Excel2007中如何给表格加密，设置成只读属性！

让他人无法修改。

　　①在Excel2007中单击“office按钮”，在弹出菜单栏中选择“另存为”中的“Excel工作薄”命令；

　　②在弹出的“另存为”窗口中单击左下角的“工具”旁边的三角按钮，然后选择“常规选项”；

　　③此时会弹出一个“常规选项”的对话框，我们可以看到有两个密码权限设置，如果要给表格加密我们可以在“打开权限密码”后面输入密码，此后，我们打开表格时必须输入正确的密码才可以读取表格。

这里我们选择“修改权限密码”在后面输入密码，然后将“建议只读”勾选，再确定，然后在弹出的“确认密码”中重新输入一遍刚才输入的密码；

　　④最后单击“保存”按钮即可。

　　以后当我们再次打开该表格时就回提示“请输入密码以获取写权限，或以只读方式打开。

”

Excel公式应用错误提示“#REF!

”的解决方法

很多朋友在使用Excel的时候会出现“#REF!

”.可能是函数的参数本应该是单一值，却提供了一个区域作为参数，输入一个数组公式时，忘记按Ctrl＋Shift＋Enter键。

　　常见原因：

如果公式返回的错误值为“#REF!

”，这常常是因为公式中使用了无效的单元格引用。

通常如下这些操作会导致公式引用无效的单元格：

删除了被公式引用的单元格；

把公式复制到含有引用自身的单元格中。

　　处理方法：

避免导致引用无效的操作，如果已经出现错误，先撤销，然后用正确的方法操作。

　　具体实例：

如图的工作表，我们利用公式将代表日期的数字转换为日期，在B2中输入了公式“=DATE（LEFT（A2,4）,MID（A2,5,2）,RIGHT（A2,2））”并复制到B4单元格。

　　这时如果把A2:

A4单元格删除，则会出现“#REF!

”错误，这是因为删除了公式中引用的单元格。

　　先执行“撤消删除”命令，然后复制B2:

B4单元格区域到A2:

A4，也会出现“#REF!

”错误，这是因为把公式复制到了含有引用自身的单元格中。

　　由于这时已经不能撤销，所以我们先把A2:

A4中的数据删除，然后设置单元格格式为“常规”，在A2:

A4中输入如图19所示的数据。

　　为了得到转换好的日期数据，正确的操作方法为：

先把B2:

B4复制到一个恰当的地方，如D2:

D4，粘贴的时候执行选择性粘贴，把“数值”粘贴过去。

这时D2:

D4中的数据就和A列及B列数据“脱离关系”了，再对它们执行删除操作就不会出错了。

要得到图22的效果，需要设置D2:

D4的格式为“日期”。

Excel2007编辑批注、删除批注

要编辑一个批注，需激活单元格，右击，然后从快捷菜单中选择"

编辑批注"

当完成更改后，单击任意单元格。

要删除单元格批注，激活含有批注的单元格，然后选择"

审阅"

->

"

批注"

删除飞或右击，然后从快捷菜单中选择"

删除批注"

课外excel函数学习：

12.LEFT或LEFTB

　　用途：

根据指定的字符数返回文本串中的第一个或前几个字符。

此函数用于双字节字符。

　　语法：

LEFT（text，num_chars）或LEFTB（text，num_bytes）。

　　参数：

Text是包含要提取字符的文本串;

Num_chars指定函数要提取的字符数，它必须大于或等于0。

Num_bytes按字节数指定由LEFTB提取的字符数。

　　实例：

如果A1=电脑爱好者，则LEFT（A1