OFFICE版EXCEL技巧收集
OFFICE版EXCEL技巧收集
[作者]:⽂章作者[来源]:⽂章来源[点击]: 540－550 [时间]:2006-01-06 14:51:53
1、两列数据查相同值对应的位置
=MATCH(B1,A:A，0)
2、已知公式得结果
定义名称=EVALUATE(Sheet1!C1)
已知结果得公式
定义名称=GET.CELL(6,Sheet1!C1)
3、强制换⾏
⽤Alt+Enter
4、超过15位数字输⼊
这个问题问的⼈太多了，也收起来吧。⼀、单元格设置为⽂本；⼆、在输⼊数字前先输⼊' 5、
在EXCEL2000中，我们可以设置⼯作簿的共享来加快数据的录⼊速度，⽽且在⼯作过程中还可以随时查看各⾃所做的改动。当多⼈⼀起在共享⼯作簿上⼯作时，Excel会⾃动保持信息不断更新。在⼀个共享⼯作簿中，各个⽤户可以输⼊数据、插⼊⾏和列以及更改公式等，甚⾄还可以筛选出⾃⼰关⼼的数据，保留⾃⼰的视窗。
⼀、设置共享⼯作簿
我们可以先举个例⼦：假设有甲⼄丙丁四个⽤户分⼯合作共同完成录⼊操作，四⼈分别在001、002、003、004号机器上同时操作，这四台机器都已经与000号电脑通过⽹络连接了起来，学⽣学籍资料信息2002.xls存放在000号机器的硬盘E：上。
1、在“⼯具”菜单中单击“共享⼯作簿”命令，然后单击“编辑” 标签，如图所⽰。
2、选择“允许多⽤户同时编辑，同时允许⼯作簿合并”复选框，在“⾼级”标签中可以对“⾃动更新间隔”、“
⽤户间修订冲突解决⽅法”以及“个⼈视图属性”等进⾏设置，在通常情况下，“⾼级”标签中的各个参数可直接使⽤系统默认值，然后单击[确定]即可。
3、出现提⽰时，保存⼯作簿。
4、在“⽂件”菜单中单击“另存为”，然后将共享⼯作簿保存在其他⽤户可以访问到的⼀个⽹络资源上（如000号机器的E:）。⼆、使⽤共享⼯作簿
1、查看冲突⽇志
冲突⽇志在共享⼯作簿中，是为以前编辑时所作的更改保存的信息，信息包括每个更改的作者、进⾏更改的时间和被更改的数据内容等。
查看冲突⽇志有两种⽅法：⼀是在⼯作表上将⿏标箭头停留在被修订过的单元格上，突出显⽰详细的修改信息；⼆是在单独的冲突⽇志⼯作表上罗列出每⼀处冲突，⽅法如下：（1）在“⼯具”菜单中，选择“修订”⼦菜单，再单击“突出显⽰修订”命令。
（2）选中“编辑时跟踪修订信息，同时共享⼯作簿”复选框，该复选框将开启⼯作簿共享和冲突⽇志。
（3）选中“在屏幕上突出显⽰修订”复选框，这样在⼯作表上进⾏需要的修改后，Excel 会以突出显⽰的颜⾊标记修改、插⼊或删除的单元格。
（4）选中“在新⼯作表中显⽰修订”选项，将启动冲突⽇志⼯作表。
（5）单击[确定]按钮，当弹出对话框提⽰保存⼯作簿时，请再次单击[确定]按钮，保存⼯作簿。
⼆、合并⼯作簿
当合并⽤户需要获得各⾃更改的共享⼯作簿的若⼲备份时，Excel需要共享⼯作簿的每个备份都保留有从创建备份之⽇到合并
之⽇的所有冲突⽇志，同时如果天数超过指定的保留时间，就不能再合并备份。保险的办法是将冲突⽇志保留⾜够长的时间，或是输⼊⼀个极⼤的天数，例如1000天。
合并⼯作簿具体操作如下：
（1）在欲合并的另⼀个⼯作簿⽂件之中，打开共享⼯作簿的备份。
（2）单击“⼯具”菜单中的“合并⼯作簿”命令。
（3）如果有提⽰，请保存共享⼯作簿。
（4）在“将选定⽂件合并到当前⼯作簿”对话框中，单击包含有合并更改内容的共享⼯作簿的某⼀个备份，
然后单击[确定]按钮。
（5）重复第（2）步到第（4）步，直到共享⼯作簿的所有备份都被合并。
做好以上设置后，其他⽤户都可以在⾃⼰的电脑中同时编辑、浏览约定的⼯作簿，同时000号操作员可以在⾃⼰的窗⼝中看到并管理所有⽤户共享编辑这个⼯作簿。为⽅便实际操作，应将每位⽤户姓名更改为真名实姓，⽅法是：单击“⼯具→选项→常规→⽤户姓名→输⼊姓名→确定”，这样在打开“共享⼯作簿”弹出的对话框（如前图所⽰）中便可正确显⽰正在使⽤本⼯作薄的所有⽤户姓名和开始使⽤时间。为了避免⼏个⽤户修改同⼀单元格的内容，可以估计⼀下数据中的记录数量，让⼏位⽤户每⼈分配不同的录⼊部分。⽐如让甲从⼯作薄的
第1条开始录⼊，让⼄从⼯作薄的第200⾏开始……但应注意在“共享⼯作簿”状态下不能同时编辑批注和建⽴超链接，这两项只能在不共享状态下进⾏。
提⽰?
1、如果需要取消共享⼯作簿，只需要在图1的“编辑”标签中，清除“允许多⽤户同时编辑，同时允许⼯作簿合并”项即可，不过这将使其他⽤户丢失所有未保存的修改。
2、对⼯作簿进⾏共享设置后，可以由⽹络上的多位⽤户同时访问和修改，为避免丢失修订记录，可以
为⼯作簿指定⼀个密码来保护共享，或保护其他⼯作表及⼯作簿特性，可作如下操作：
（1）单击“⼯具”菜单的“保护”命令，从其⼦菜单中选择“保护并共享⼯作簿”命令，弹出“保护共享⼯作簿”对话框（如图）。
（2）选中“以追踪修订⽅式共享”复选框，⽤这种⽅式就可以共享⼯作簿且避免丢失修订记录。如果希望其他⽤户在关闭冲突⽇志或撤消⼯作簿共享状态时输⼊密码，请在“密码”框中键⼊密码，然后在出现提⽰时再次输⼊以便确认。为⼯作簿提供共享保护以后，其他⽤户就不能撤消⼯作簿共享状态或者关闭冲突⽇志了，除⾮知道正确的密码。
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列数据合计成⼀列
=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&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、⾃定义函数
Public Function name()
excel怎么自动求减法
Dim filename As String
filename = www.doczj/doc/19d339bec77da26925c5b013.html
name = filename
End Function
19、这个在⼀个帖⼦中的我就整搬⼀下（⾃已的帖^_^）
如何获取⼀个⽉的最⼤天数
:"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01
数据区包含某⼀字符的项的总和，该⽤什么公式
=sumif(a:a,"*"&"某⼀字符"&"*"，数据区)
最后⼀⾏为⽂本：
=offset($b$1,MATCH(CHAR(65535),b:b)-1,)
最后⼀⾏为数字：
=offset($b$1,MATCH(9.9999E+307,b:b)-1,)
或者：=lookup(2,1/(b1:b1000<>""),b1:b1000)
评委打分中，如何去掉两个以上最⾼分，两个以上最底分，求剩余⼈员的平均分？同时显⽰出被去掉的分数。
看看trimmean()函数帮助。
被去掉的分数：
最⼤两个：=large(data,{1;2})
最⼩两个：=small(data,{1;2})
怎样很简单的判断最后⼀位是字母
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+
现在是=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不能⽤的（否则空字符也计算进去了）
=COUNTIF($E$3:$E$65536,"?*")
动态求和公式,⾃A列A1单元格到当前⾏前⾯⼀⾏的单元格求和.
=SUM(INDIRECT("A1:A"&ROW()-1))
20、⽐较好⽤的EXCEL⽂档修复⼯具
ExcelRecovery
这个是⽹络上⼀个哥们的⾃学笔记5
51、我在单元格中填上―="现在是"&NOW()‖，想显⽰⽬前的时间，为什么会变成―今天是38645.6480‖，有什么办法解决吗？
⽅法1=now()单元格设置为你想显⽰的⽅式，如：现在是yyyy年m⽉d⽇h点m分。
⽅法2、直接⽤TEXT()函数：=TEXT(NOW(),"现在是yyyy年m⽉d⽇h点m分")
第⼀种⽅法较好，可以参与计算。
=TEXT(NOW(),"现在是yyyy/mm/dd h:mm AM/PM")
显⽰现在是2005/04/14 4:42 PM
52、⾃定义单元格格式[=0]"男";[=1]"⼥"; 则可实现输⼊0显⽰为―男‖。输⼊1显⽰为―⼥‖。
53、excel⾥⾯如何添加⾃定函数帮助信息呢
在VBE打开对象浏览器，到⾃定函数，右键——属性中添加
54、怎么解决Excel⾃动将０开头的数字⾃动删除０？
是从―获取外部数据‖导⼊的吗？在第3步可以选择―列数据格式‖为⽂本，这样零就不会被删除了。
55、打开多个EXCEL⽂档，照理应该在状态栏显⽰多个打开的⽂档，以便各⽂档互相切换，但现在只能显⽰⼀个⽂档，必须关掉⼀个才能显⽰另⼀个，关掉⼀个再显⽰另⼀个，不知何故?
可以从―窗⼝‖菜单中切换窗⼝。
或者改回你原来的样⼦：⼯具/选项/视图，选中任务栏中的窗格。
56、⽬的：表中>50000的单元格红⾊显⽰。做法：选择整张表，在条件格式命令中，设置了―>50000以红⾊填充单元格―的条件，出现的问题：表头(数值为⽂本)的单元格也呈红⾊显⽰。我知道，原因是因为区域选择得不对，如果只选择数字区域不会出现这种情况，如果表结构简单，则好处理，如果表格结构复杂，这样选择就很⿇烦。有没有办法选择整张表，但是表头(数值为⽂本)的单元格不被条件格式。
答：条件格式设置公式=--A1>50000
问=--A1>50000中的--代表什么意思，
答：转变为数值.与+0,*1，是⼀样的效果。
57、、如何打印⾏号列标？
答：⽂件菜单-----页⾯设置---⼯作表----在打印选项中的⾏号列标前打勾。
58、如何打印不连续区域？
答：按CTRL键不松，选取区域，再点⽂件菜单中的打印区域--设置打印区域。
59、打印时怎样⾃动隐去被0除的错误提⽰值?
答：页⾯设置—⼯作表，错误值打印为空⽩
60、如何设置A1当⼯作表打印页数为1页时,A1=1，打印页数为2页时,A2=2,...?