excel函数使用技巧Word格式.docx
《excel函数使用技巧Word格式.docx》由会员分享，可在线阅读，更多相关《excel函数使用技巧Word格式.docx（51页珍藏版）》请在冰豆网上搜索。
右上角的"
工具"
常规>
设置
（2）工具>
选项>
安全性
8、关于COUNTIF
COUNTIF函数只能有一个条件，如大于90，为=COUNTIF（A1:
A10,"
=90"
）
介于80与90之间需用减，为=COUNTIF（A1:
80"
）-COUNTIF（A1:
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》格式选红色
如果是要整个工作表中不输入重复数据，则选中整个表---数据--有效性--自定义--公式
输入=COUNTIF（$1:
$65535,A1）=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）自定义函数
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,"
&
某一字符"
数据区）
最后一行为文本：
=offset（$b$1,MATCH（CHAR（65535）,b:
b）-1,）
最后一行为数字：
=offset（$b$1,MATCH（9.9999E+307,b:
或者：
=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+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不能用的（否则空字符也计算进去了）
=COUNTIF（$E$3E$65536,"
?
动态求和公式,自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中打开文本文件－对导入文本对话框进行对应设置
1、在相对引用、绝对引用和混合引用间快速切换在Excel进行公式设计时，会根据需要在公式中使用不同的单元格引用方式，这时你可以用如下方法来快速切换单元格引用方式：
选中包含公式的单元格，在编辑栏中选择要更改的引用，按F4键可在在相对引用、绝对引用和混合引用间快速切换。
例如选中“A1”引用，反复按F4键时，就会在$A$1、A$1、$A1、A1之间切换。
2、快速复制公式内容
当我们在Excel工作表中修改某个单元格中的公式后，如果要将同列的其余单元格中的公式进行复制时，大多数情况下都是利用拖动填充柄的方式来进行公式复制。
现在你试试在单元格右下角的小黑十字上用鼠标快速点击两下，是不是同列单元格中的所有的公式都复制好了，效果不错吧。
3、让活动单元格向任意方向移动
在Excel中，你是否知道按下回车键后，活动单元格可以向上、向左、向右移动，甚至原地不动。
在“工具”菜单上，单击“选项”，再单击“编辑”选项卡，选中“按Enter键后移动”复选框，然后在“方向”框中选择移动方向，若要保留在当前单元格中原地不动则清除该复选框
4、快速命名单元格区域先将需要命名的单元格区域选中，然后用鼠标点击编辑栏左端名称框，直接输入单元格名称即可。
5、选择一个含有超链接的单元格
在Excel中选择含有超链接单元格是一件比较麻烦的事情。
如果直接点击该单元格，就会跳转到超链接目标去了。
若要在选择含有超链接单元格时不跳转到超链接目标上，你只要在单击单元格的同时按住鼠标按键直到光标变为空心十字形状，然后放开鼠标按钮就可选择这个含有超链接的单元格。
此外，你也可以先选择含有超链接单元格旁边的单元格，然后利用方向键键盘上方向键将活动单元格移动到含有超链接的单元格中，也能达到同样效果。
6、快速输入公式
如果你需要在B3至B20单元格区域录入格式相同的公式，这时你可先将B3至B20单元格区域选定，然后按F2键再键入公式。
公式录入后按Ctrl+Enter，这样就可以在所选择区域内所有单元格中输入同一公式。
在这里你还可以选择多个不相邻的单元格区域来进行此项操作。
7、隐藏单元格中的所有值
有时候，我们需要将单元格中所有值隐藏起来，这时你可以选择包含要隐藏值的单元格。
选择“格式”菜单中“单元格”命令，单击“数字”选项卡，在“分类”列表中选择“自定义”，然后将“类型”框中已有的代码删除，键入“;
”（三个分号）即可。
9、添加纸牌游戏
使用Excel时间长了，感觉有点累了，让我们休息一下，来玩一盘纸牌游戏吧。
选择“工具”菜单下的“自定义”命令，打开“自定义”窗口，点击“命令”标签卡，在“类别”中选择“工具”，从“命令”中找到有纸牌形状且为“自定义”名称的图标将其拖到常用工具栏中，关闭自定义窗口，然后点击上具栏上纸牌按钮就可开始玩纸牌游戏了。
1.两次选定单元格
有时，我们需要在某个单元格内连续输入多个测试值，以查看引用此单元格的其他单元格的效果。
但每次输入一个值后按Enter键，活动单元格均默认下移一个单元格，非常不便。
此时，你肯定会通过选择“工具”\“选项"
\“编辑"
，取消“按Enter键移动活动单元格标识框"
选项的选定来实现在同一单元格内输入许多测试值，但以后你还得将此选项选定，显得比较麻烦。
其实，采用两次选定单元格方法就显得灵活、方便：
单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定此单元格（此时，单元格周围将出现实线框）。
2.“Shift＋拖放"
的妙用
在拖放选定的一个或多个单元格至新的位置时，同时按住Shift键可以快速修改单元格内容的次序。
具体方法为：
选定单元格，按下Shift键，移动鼠标指针至单元格边缘，直至出现拖放指针箭头“?
，然后进行拖放操作。
上下拖拉时鼠标在单元格间边界处会变为一个水平“工"
状标志，左右拖拉时会变为垂直“工"
状标
升级会员 