常用办公软件 Excel技巧的整理讲解.docx
《常用办公软件 Excel技巧的整理讲解.docx》由会员分享，可在线阅读，更多相关《常用办公软件 Excel技巧的整理讲解.docx（53页珍藏版）》请在冰点文库上搜索。
常用办公软件Excel技巧的整理讲解
常用办公软件Excel技巧的整理、讲解，在这里给读者们看一看，给大家一些提示，希望在你在平时能用得上。
1、两列数据查找相同值对应的位置
=MATCH（B1,A:
A，0）
2、已知公式得结果
定义名称=EVALUATE（Sheet1!
C1）
已知结果得公式
定义名称=GET.CELL（6,Sheet1!
C1）
3、强制换行
用Alt+Enter
4、超过15位数字输入
这个问题问的人太多了，也收起来吧。
一、单元格设置为文本;二、在输入数字前先输入'
5、如果隐藏了B列，如果让它显示出来?
选中A到C列，点击右键，取消隐藏
选中A到C列，双击选中任一列宽线或改变任一列宽
将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。
6、EXCEL中行列互换
复制，选择性粘贴，选中转置，确定即可
7、Excel是怎么加密的
（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置
（2）、工具>>选项>>安全性
8、关于COUNTIF
COUNTIF函数只能有一个条件，如大于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（A1,11,2））,"错误身份证号"））
（2）、=TEXT（MID（A2,7,6+（LEN（A2）=18）*2）,"#-00-00"）*1
10、想在SHEET2中完全引用SHEET1输入的数据
工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。
11、一列中不输入重复数字
[数据]--[有效性]--[自定义]--[公式]
输入=COUNTIF（A:
A,A1）=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF（A:
A,A5）>1》格式选红色
12、直接打开一个电子表格文件的时候打不开
“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上
13、Excel下拉菜单的实现
[数据]-[有效性]-[序列]
14、10列数据合计成一列
$A,（ROW（）-2）*10+1,,10,1）） =SUM（OFFSET（
15、查找数据公式两个（基本查找函数为VLOOKUP,MATCH）
（1）、根据符合行列两个条件查找对应结果
=VLOOKUP（H1，A1：
E7，MATCH（I1，A1：
E1，0），FALSE）
（2）、根据符合两列数据查找对应结果（为数组公式）
=INDEX（C1：
C7，MATCH（H1&I1,A1:
A7&B1:
B7,0））
16、如何隐藏单元格中的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,"","*",LEN（A1）-LEN（SUBSTITUTE（A1,"",""））））+1,LEN（A1））
（4）、自定义函数
PublicFunctionname（）
DimfilenameAsString
filename=ActiveWorkbook.name
name=filename
EndFunction
19、如何获取一个月的最大天数
:
"=DAY（DATE（2002,3,1）-1）"或"=DAY（B1-1）",B1为"2001-03-01
数据区包含某一字符的项的总和,该用什么公式
=sumif（a:
a,"*"&"某一字符"&"*",数据区）
最后一行为文本：
$b,MATCH（CHAR（65535）,b:
b）-1,） =offset（
最后一行为数字：
$b,MATCH（9.9999E+307,b:
b）-1,） =offset（
或者：
=lookup（2,1/（b1:
b1000<>""）,b1:
b1000）
评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?
同时显示出被去掉的分数。
看看trimmean（）函数帮助。
被去掉的分数：
最大两个：
=large（data,）
最小两个：
=small（data,）
怎样很简单的判断最后一位是字母
right（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,）））
在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?
=INDIRECT（"A1"&"!
"&"E1"）A1为工作表名
奇数行求和=SUMPRODUCT（（A1:
A1000）*MOD（ROW（A1:
A1000）,2））
偶数行求和=SUMPRODUCT（（A1:
A1000）*NOT（MOD（ROW（A1:
A1000）,2）））
查看字符串字数
=LEN（A1）
求非空单元格数量
公式计算出来的数据，COUNTA不能用的（否则空字符也计算进去了）
$E536,"?
*"）$E:
=COUNTIF（
动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.
=SUM（INDIRECT（"A1:
A"&ROW（）-1））
20、比较好用的Excel文档修复工具
ExcelRecovery
21、EXCEL开方运算
将8开3次方，可以用这个公式，在单元格中输入 =8^（1/3）
22、单元格中的数据分散对齐
文本格式》全角输入
23、查找工作表中的链接
Ctrl+~或编辑》链接
24、如何让空单元格自动填为0
选中需更改的区域》查找》空》替换》0
25、把Word里的数字转换到Excel
方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值
选中》表格转换为文本》粘贴》分列》对分列选项设置为文本
另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置
如何恢复损坏的Excel文件
打开一个以前编辑好的Excel工作簿，却发现内容混乱，无法继续进行编辑，而且还不能够进行打印。
这是很多朋友在处理Excel文件时都可能会遇到的一个问题，面对这种情况时，我们应该怎么办呢？
出现上述情况的原因应该是该文件已经被损坏了，此时你不妨尝试使用一下笔者为大家介绍的以下几种办法，或许能够恢复损坏的Excel文件（以Excel2003为例）。
招数一:
将工作簿另存为SYLK格式
如果Excel文件能够打开，那么将工作簿转换为SYLK格式可以筛选出文档的损坏部分，然后再保存数据。
首先，打开需要的工作簿。
在“文件”菜单中，单击“另存为”命令。
在“保存类型”列表中，单击“SYLK（符号连接）（*.slk）”选项（图1），然后单击“保存”按钮。
关闭目前开启的文件后，打开刚才另存的SYLK版本即可。
图1保存为SYLK
招数二:
转换为较早的版本
如果由于启动故障而没有保存Excel工作簿，则最后保存的版本可能不会被损坏。
当然，该版本不包括最后一次保存后对文档所作的更改。
关闭打开的工作簿，当系统询问是否保存更改时，单击“否”。
在“文件”菜单中，单击“打开”命令，双击该工作簿文件即可。
招数三:
打开并修复工作簿
如果Excel文件根本不能够使用常规方法打开，那么可以尝试Excel2003中的“打开并修复”功能，该功能可以检查并修复Excel工作簿中的错误。
在“文件”菜单中，单击“打开”命令。
通过“查找范围”框，定位并打开包含受损文档的文件夹，选择要恢复的文件。
单击“打开”按钮旁边的箭头，然后单击“打开并修复”即可（图2）。
图2打开并修复
招数四:
用Excel查看程序打开工作簿
在用尽各种方法仍不能解决问题的情况下，大家不妨考虑一下使用第三方软件开展恢复工作。
Excel查看程序是一个用于查看Excel工作簿的免费的实用程序，可从微软网站上得到，最新版本为ExcelViewer2003（下载地址：
）。
双击下载文件xlviewer.exe启动安装程序，然后按照说明完成安装。
安装完毕，单击“开始”菜单中的“MicrosoftOfficeExcelViewer2003”即可启动该软件（图3），尝试打开损坏的工作簿。
大家可以在该程序中打开损坏的工作簿，然后复制单元格，并将它们粘贴到Excel的一个新工作簿中。
如果以后要删除Excel查看程序，可通过“控制面板”中的“添加/删除程序”进行删除。
图3ExcelViewer
但愿以上方法能够帮助你恢复损坏的Excel文件。
如果无法恢复损坏工作簿中的数据，那么就需要重新创建部分或整个文档了。
Excel2007中用填充序列填数据的两种方法
使用Excel2007录入数据时，经常会需要输入一系列具有相同特征的数据，例如周一到周日、一组按一定顺序编号的产品名称等。
如果经常用到同样的数据，可以将其添加到填充序列列表中，以方便日后的使用。
第1种方法是自己手动添加一个新的自动填充序列，具体操作步骤如下。
1.单击Office按钮，然后单击“Excel选项”按钮，打开“Excel选项”对话框。
2.单击左侧的“常规”选项卡，然后单击右侧的“编辑自定义列表”按钮，如图1所示。
此时将会打开“自定义序列”对话框。
图1
3.在“输入序列”下方输入要创建的自动填充序列，如图2所示。
图2
4.单击“添加”按钮，则新的自定义填充序列出现在左侧“自定义序列”列表的最下方，如图3所示。
图3
5.单击“确定”按钮，关闭对话框。
第2种方法是从当前工作表中导入一个自定义的自动填充序列，具体操作步骤如下。
1.在工作表中输入自动填充序列，或者打开一个包含自动填充序列的工作表，并选中该序列。
2.单击Office按钮，然后单击“Excel选项”按钮，打开“Excel选项”对话框。
3.单击左侧的“常规”选项卡，然后单击右侧的“编辑自定义列表”按钮，打开“自定义序列”对话框。
此时在“从单元格中导入序列”右侧框中出现选中的序列，如图4所示。
图4
4.单击“导入”按钮，序列出现在左侧“自定义序列”列表的最下方。
如何合并Excel工作簿
你会不会有大量Excel工作簿需要合并到一起呢？
你可以手动完成这一工作，但这个工作可能会花费你一整天的时间。
要完成合并工作，最好使用宏，这样只需花几分钟便可搞定。
本文所介绍的技巧将向你说明如何创建这样一个宏，看看如何使用宏来节省你的宝贵时间。
（本技巧可应用于MicrosoftExcel97、Excel2000、Excel2002、和Excel2003。
）
JoydipDas在合并数个工作簿时遇到了问题。
这些工作簿总数约有200，保存在同一文件夹中，其中大多数工作簿中只包含一个单独的工作表，但有些工作簿包含多个工作表。
这些工作簿中的工作表需要被添加到一个新的工作簿中。
合并数量如此巨大的工作簿的最简单方法——如果经常要这样做的话——是使用宏。
下面的宏会显示一个对话框，提示你选择要合并的文件。
（你可以通过按下Ctrl键单击的方法选择多个工作簿。
）它会使用代码在你所选的工作簿列表中循环，打开每个工作簿并将其中的工作表移动到工作簿的末尾。
在将工作表添加到工作簿末尾的过程中，Excel会复制工作表的名称并自动根据检测结果附加
（2）、（3）等数字编号。
工作簿中与其他工作表相关的任何公式也会自动更新的新的名称。
Excel实现快速求取各班各科最高分
学校里经常组织考试，每次考试结束之后照例都是对成绩的分析和比较。
各平行班每班每学科的最高分也是要比较的数据之一，那么在Excel2003中怎样才能快速得到需要的数据呢？
下面的方法您不妨一试。
我们以图1所示的工作表为例。
班级位于“原始分”工作表A列，语文等各科成绩及总分分别位于C列
至H列。
我们将在不对原表进行排序、筛选等操作的基础上得到各班各学科的最高分。
图1Excel中原始工作表（点击看大图）
一、启动数据透视表
点击菜单命令“数据→数据透视表和数据透视图”，启动“数据透视表和数据透视图向导”。
一共有三步，第一步选中“MicrosoftOffice$109”；最后一步选中“新建工作表”单选项以便将产生的数据透视表放到新的工作表中。
点击“完成”按钮后即可打开新的工作表，并得到如图2所示的视图效果。
$H$1:
$AExcel数据列表或数据库”单选项和“数据透视表”单选项；第二步在“选定区域”输入框中输入数据区所在的单元格范围“
图2Excel启动数据透视表（点击看大图）
二、统计成绩最高分
在“数据透视表字段列表”对话框中，选定“班级”项目，将其拖放到视图中“将行字段拖至此处”，松开鼠标，即可得到班级列表。
将“语文”项目拖至“请将数据项拖至此处”，再将“数学”也拖至此处。
此时得到的是各班这两门学科的总和成绩，而且这两门学科排列位置是上下并列，很不方便。
我们可以用鼠标拖动数据透视表中“数据”按钮至右侧“汇总”处，如图3所示，可以使这两门学科并排放置。
然后再将其它各门学科拖至数据区，就可以很快得到我们希望的表格了。
图3Excel中设置成绩汇总（点击看大图）
但是现在各班统计的是各学科的总和成绩，并非是最高分。
不过，这很容易改变。
如图4所示，先点击B5:
B13单元格区域中某一单元格，再点击“数据透视表”工具栏右数第二个按钮“字段设置”，然后在打开的“数据透视表字段”对话框中选择“汇总方式”为“最大值”，确定后就可以求到各班语文成绩的最高分了。
其它各科照此办理，就行了。
图4Excel中修改汇总方式（点击看大图）
如果您看不到“数据透视表”工具栏，只要点击菜单命令“视图→工具栏→数据透视表”就可以看到了。
此种方法方便快捷，可以自动产生表格，不需要我们自行设计，感觉不错吧？
Excel函数实例：
在间隔数据中排定名次
发布于2007-09-2101:
38来源：
网海拾贝
用Excel表格做了学生考试成绩汇总表，格式如图1所示。
需要根据AA列中相应科目的成绩排出名次，并将结果放到AB列相应的单元格中。
排定名次要使用RANK函数，其语法是“RANK（number,ref,order）”其中，参数“number”是需要找到排位的数字；而参数“ref”则是数字列表的引用；第三个参数如果省略则是按降序排列，这正是我们需要的。
比如公式“=RANK（A3,A2:
A6）”的意思就是要得到A3单元格数据在A2:
A6单元格数据中的排名。
显然，在本例中要使用RANK函数，但有一个问题是必须要解决的。
假设我们要针对语文学科的总分来排名次。
学生们的语文总成绩分布在AA4、AA12、AA20……等单元格中，所处单元格区域并不连续，每8行有一个成绩。
怎样才能将它们选中，并作为排名的区域呢?
这个问题也曾经困扰了我很长时间，最后发现利用求余数函数可以使问题得到顺利解决。
不过，有些准备工作是要做的。
还是以语文成绩的排名为例。
先将AA列的数据复制到别的位置，等准备工作完成后再粘贴回来。
先选中AA4单元格，输入公式“=1/（MOD（ROW（）,8）-4）”，回车后即可发现出现“#DIV/0!
”的错误提示。
拖动该单元格填充句柄向下至AA379，则会从AA4单元格开始，每8行出现相同的错误提示，其它各行均为数字。
点击功能区“开始”选项卡“编辑”功能组“查找和选择”按钮，在弹出的菜单中点击“定位条件”命令，打开“定位条件”对话框。
选中“公式”单选项，并只保留选中随后出现的“错误”复选项，如图2所示。
确定后就可以发现，凡是出现错误的提示的单元格就处于被选中状态了。
现在点击功能区“公式”选项卡“定义的名称”功能组“定义名称”按钮，在打开的“新建名称”对话框的“名称”输入框中输入“ymzf”。
确定关闭对话框。
以后只要我们在名称框中输入“ymzf”，回车，就可以再次选中AA列中全部语文学科对应的单元格了，如图3所示。
按照上面的方法，只要能让错误提示分别出现在相应学科所在行，那么就可以利用定位条件来选中它们。
因为语文学科所处的单元格所在行除以8的余数为4，所以我们采用公式“=1/（MOD（ROW（）,8）-4）”制造了除数为0的错误提示。
那么数学、英语等其它学科则可以分别根据其行数除以8的余数不同，重复上面的操作过程，只是将公式分母中“-4”分别变成“-5”、“-6”、“-7”、“-0”、“1”、“2”、“3”就可以了。
将所到的各学科区域分别以“sxzf”、“yyzf”等名称命名。
但准备工作仅仅做这些还是不够的。
因为我们排出的名次应该放在AB列而不是AA列。
所以我们还要在再用上面的方法在AB列中选中各学科对应的区域，并分别以“ymmc”、“sxmc”、“yymc”等名称命名，以便将来在这些区域中输入不同的公式。
至此，我们的准备工作才算是完成了。
现在我们可以将临时放到别处的总分粘贴回AA列单元格中了，再剩下的事儿就是用RANK函数排名的问题了。
咱还是先根据语文成绩排名吧。
先在名称栏输入“ywmc”，回车，将AB列语文学科所对应的单元格全部选中，此时AB372单元格会处于被激活状态。
我们只要在编辑栏输入公式“=RANK（AA372,ywzf）”，并按下“Ctrl+Enter”就可以在全部选中的单元格中输入公式并得到名次结果了。
最后的结果如图1所示。
其它学科的名次排定依此法办理。
够简单吧?
至此，我们针对各学科的排名工作就算是大功告成了。
Excel技巧：
快速录数据让单位自动补全
用Excel做表格，经常遇到需要在某一列使用同一单位的情况。
如果先在第一个单元格输入对应单位的名称，然后使用“拖拽”的方法使其它行复制这一单位，达到的效果不尽人意，要么需要再改单位前的数值，要么单位与数值分在两列。
我们通过预先设置某一列的格式可以让这些单位“自动补全”。
这里以将某一列的单位设置为厘米（CM），最终的效果是在该列任一单元格中输入数字后，按下回车键或将光标移至其它单元格，在数字后会自动补齐“CM”。
第一步：
运行Excel，单击选中需要设置格式的列。
右键单击选中的列，在弹出的快捷菜单中选择“设置单元格格式”（如图1）。
第二步：
在打开的“单元格格式”对话框中，在“数字”标签中，选中“自定义”项，在右侧“类型（T）:
”下方的文本框中输入“0.00"CM"”（如图2,不含外侧引号），最后单击“确定”按钮。
经过这样设置之后，在该列单元格中输入数据时，不用再考虑单位问题了，直接输入数字即可。
小提示：
其中“0.00”表示该列单元格保留两位小数，小数点后面几个“0”就代表几位小数，具体多少可根据需要自行设置。
如果是其它单位符号，只要更改双引号中的CM为相应的字符即可。
Excel实现多级联动
在Excel表中多级联动还是需要点技巧的,主要还是接触Excel表太少,毕竟不是微软办公高手,呵呵,在网上发现有人写的3级以上联动需要宏,可是,宏在很多时候会被误杀掉,而自己刚好又要用到3级联动,所以就自己研究了下.发现并不如想象中那么麻烦,没有使用宏,只是使用了几个简单的函数达到目的,想到可能有不少人会有这方面的需求,就写在自己的Blog上了,欢迎大家共同探讨.
例子见附件：
首先,需要创建2个工作表,一个用来显示最终的结果,一个用来存放原始数据,当然,在我的例子中我用了3个,纯属无聊而已.先看第1个表格
A
B
C
D
E
1
配件名称
品牌
型号参数
价格
2
显示器
AOC
177Si
1250.00
这个只是简单的表,在附件中的Excel有更详细的.我列出这个表,主要是想讲一下我的思路.品牌,型号参数是个可以选择的项目,价格则根据不同的型号变动.就是说,根据配件名称选择品牌,根据品牌选择型号参数,由型号参数来确定价格.那么,在确定基本的思路后,就要开始考虑如何定义数据结构了.请看下面的表格.
A
B
C
D
E
1
greatwall
美格
BenQ
AOC
2
M92
B71
T705
177Si
3
M157
FP71+
4
M172
FP92W
F
G
B71
1266.00
M92
1450.00
M157
1199.00
M172
1369.00
T705
1280.00
FP71+
1270.00
FP92w
1499.00
177Si
1250.00
这2个表分别是品牌与型号参数,型号参数与价格.由于没有使用宏,如果把这2个表合在一起,分开会比较麻烦,至少我还没有想到什么解决办法.数据结构确定了,就开始做多级联动.首先做第一个下拉列表,就是显示器类别.选中表2中A1到D1,在名称框里输入"显示器"后回车,然后在表1里选中B2,然后点数据->有效性->设置->允许里选"序列",在"来源"里输入"=显示器",这样,第一个下拉列表就制作完成了,点一下下拉箭头,可以看见GreatWall,美格等的选择项,现在制作第2个下拉列表,就是型号参数.选中表2中A1到D4区域,然后点插入->名称-
升级会员 