EXCEL使用技巧部分收集部分原创Word格式文档下载.docx
《EXCEL使用技巧部分收集部分原创Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《EXCEL使用技巧部分收集部分原创Word格式文档下载.docx（16页珍藏版）》请在冰点文库上搜索。
用exce记录合同信息，有些合同涉密，不希望显示出来，或者用*号显示，比如：
在一个单元格里输入“合同名称”四个字，但我希望别人打开这个表格时看到的这个单元格里显示的是“****”，而我通过某种途径还可以知道这个“****”后面的内容是“合同名称”。
先在那个单元格输入公式:
=IF（IV1=123,"
合同名称"
"
****"
）
然后选定全表,点"
格式->
单元格"
保护"
取消"
锁定"
前面的对勾。
再选定公式单元格,点"
选中"
和"
隐藏"
工具"
保护工作表"
输入两次密码,点"
这样设置好后别人看到的就是星号，也不能对这个单元格删除和修改。
你自己要看的时候，点"
撤消工作表保护"
输入密码，这时点击该单元格时编辑栏可以看到公式内容，但单元格显示的还是星号。
你再在IV1单元格输入123，该单元格就显示为“合同名称”了。
七、如何将excel表中所有的空单元格填上某个数字?
选定表格区域范围,点"
编辑"
定位"
条件定位"
空值"
输入某个数字,按Ctrl+回车。
八、如何根据A列的身份证号码在B列判断女的是否大于40岁，男的是否大于50岁？
在B1输入公式:
=IF（YEAR（TODAY（））-1*TEXT（（LEN（A1）=15）*19&
MID（A1,7,2+（LEN（A1）=18）*2）,"
0000"
）>
IF（MOD（MID（A1,15+（LEN（A1）=18）*2,1）,2）,50,40）,"
是"
否"
将公式向下复制。
九、如何计算单元格中数学表达式的值？
假定A1内容为21*17+5/4,要在B1单元格计算其结果。
单击B1，然后点“插入”菜单选择“名称”命令中的“定义”子命令，出现“定义名称”对话框。
在“在当前工作表中的名称”中输入定义的名称“X”，在下方的“引用位置”编辑栏中输入:
=EVALUATE（A1）
单击[确认]按钮退出。
在B1中输入“=X”（注:
不含引号）
公式可向下复制。
十、如何将EXCEL中的信息导入OutlookExpress的联系人中？
在EXCEL中点"
文件"
另存为"
保存类型"
中选择"
CSV（逗号分隔）（*.csv）"
文件名任起。
在OutlookExpress中点"
导入"
其他通讯薄"
文本文件（以逗号分隔）"
找到你保存的*.csv文件,按提示分别导入各项内容即可。
Outlook2003可以直接导入EXCEL文件（需要安装转换插件）。
十一、如何求单元格中任意数字的所有各个位数之和？
如5126各位数的和5+1+2+6=14，公式如下：
=SUMPRODUCT（1*MID（A1,ROW（INDIRECT（"
1:
&
LEN（A1）））,1））
注：
A1可以为任意位数字。
十二、如何让用户在C1单元格选择的月份后，A列中自动显示当年当月所有日期，而在B列自动填充对应的星期几？
假定C1单元格用数据有效性设置一个选择（1-12）月份的下拉菜单,A1单元格输入公式:
=IF（C$1="
IF（C$1<
>
MONTH（DATE（YEAR（TODAY（））,C$1,ROW（）））,"
DATE（YEAR（TODAY（））,C$1,ROW（））））
B1单元格输入公式:
=IF（A1="
TEXT（WEEKDAY（A1）,"
AAAA"
））
将两个公式向下复制到第31行。
将A列单元格格式设置为你要的日期格式。
十三、如何在单元格中引用工作表名、工作簿名、工作簿所在文件夹名？
在工作簿已经保存的情况下，CELL（"
filename"
）可以得到带完整路径的工作表名。
用字符函数截取相应的部分即可。
引用工作表名：
=RIGHT（CELL（"
）,LEN（CELL（"
））-FIND（"
]"
CELL（"
）））
引用工作簿名：
=MID（CELL（"
）,FIND（"
["
））+1,FIND（"
））-5）
引用工作簿所在文件夹名：
=TRIM（LEFT（RIGHT（SUBSTITUTE（CELL（"
）,"
\"
REPT（"
"
100））,200）,100））
十四、如何填充特殊序列（如第一期、第二期……，第壹号、第贰号……）？
有两种方法：
一是设置“自定义”单元格格式，“类型”分别为：
[DBNum1]"
第"
G/通用格式"
期"
[DBNum2]"
号"
然后输入阿拉伯数字进行填充。
二是利用公式直接生成，公式分别为：
="
TEXT（ROW（A1）,"
[DBNum1]G/通用格式"
）&
[DBNum2]G/通用格式"
十五、EXCEL中如何使用自定义函数？
打开EXCEL，按Alt+F11调出VBA编辑器，点“插入”->
“模块”，将自定义的函数模块内容加入进去。
比如“将单元格字符串反序输出”的函数模块：
PublicFunctionMyStrReverse（ByValsStringAsVariant）AsString
Application.Volatile
DimstrResultAsString
strResult=StrReverse（sString）
MyStrReverse=strResult
EndFunction
如果你要对软件保密的话，在“模块”上按右键，[VBAproject属性]→[保护中设置密码]，别人就看不到你的源程序了。
退出VBA编辑器，返回到EXCEL界面，点“文件”->
“另存为”，“保存类型”选择“MicrosoftOfficeExcel加载宏”，取名为“字符串反序输出”，会自动加上扩展名.xla。
使用时，点“工具”->
“加载宏”，点“浏览”找到你保存的“字符串反序输出.xla”文件，点“确定”。
如果“可用加载宏”里面已经有此项，选中后点“确定”即可。
假定A1为“ijdf152中国人id897”，在B1直接输入自定义的函数：
=MyStrReverse（A1）
即可得到结果“798di人国中251fdji”。
十六、EXCEL中如何设置二级关联下拉菜单?
将你的一级菜单的内容输入到SHEET1工作表的第一行（假定为A1:
G1）,选定A1:
G1,将该区域命名为"
一级"
（注:
不含引号）,将一级菜单对应的二级菜单内容分别输入到A至G列第二行以下的各行中（假定为第2至10行）。
在SHEET2中，第一行为表头，选定A2:
A10（可根据你的需要改变范围），点"
有效性"
设置"
允许下面选择"
序列"
在来源框中输入
=一级
点"
用鼠标选定B2:
B10,点"
在来源框中输入下面的公式:
sheet1!
CHAR（64+MATCH（A2,一级,0））&
2:
10"
这样SHEET2工作表中A2:
A10有一级下拉菜单的内容供选择，B2:
B10有对应的二级下拉菜单内容供选择。
十七、EXCEL中如何提取汉字的拼音首字母?
（收集）
LOOKUP（CODE（A1）,45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"
A"
B"
C"
D"
E"
F"
G"
H"
J"
K"
L"
M"
N"
O"
P"
Q"
R"
S"
T"
W"
X"
Y"
Z"
}））
或者:
=LOOKUP（CODE（A1）,45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},MID（"
ABCDEFGHJKLMNOPQRSTWXYZ"
ROW（$1:
$23）,1））
注:
公式只对常用汉字有效，部分生辟汉字未按拼音排列，无法用公式找到。
十八、EXCEL中如何禁止输入完全相同的行
假定你准备将数据输入到A1:
C100区域（三列100行）,先用鼠标选定该区域,点"
允许"
下面选择"
公式"
下面的框中输入:
=SUMPRODUCT（（$A$1:
$A$100=$A1）*（$B$1:
$B$100=$B1）*（$C$1:
$C$100=$C1））=1
这样就不能输入完全相同的行了，必须重新输入该行直到没有重复的行它才会接受你输入的数据。
如果你的列数超过三列，将公式作相应修改即可。
十九、如何查找某列中不重复的数据并连续存放在另一列中?
假定原数据在A1:
A100区域中，其中有若干数据重复，先将A1数据复制到B1，然后在B2单元格输入数组公式：
=IF（SUMPRODUCT（1/COUNTIF（A$1:
A$100,A$1:
A$100））<
ROW（）,"
INDEX（A:
A,MIN（IF（COUNTIF（B$1:
B1,A$1:
A$100）=0,ROW（A$1:
A$100）））））
将公式向下复制，直到出现空白为止。
补充：
下面这个数组公式可以从B1单元格直接输入:
=IF（SUMPRODUCT（1/COUNTIF（A$1:
A,SMALL（IF（MATCH（A$1:
A$100,）=ROW（A$1:
A$100）,ROW（A$1:
A$100）,10^4）,ROW（））））
公式以CTRL+SHIFT+回车结束。
二十、如何将一个单元格中的数字去掉重复的后从小到大排序输出在另一个单元格中?
假定A1为209537233，要在B1得到023579，公式如下：
=IF（ISERROR（FIND（0,A1））,"
0"
SUBSTITUTE（SUM（IF（FIND（ROW（$1:
$9）,A1&
123456789"
）<
=LEN（A1）,ROW（$1:
$9）*10^（9-ROW（$1:
$9））））,0,"
公式以CTRL+SHIFT+回车结束
一、求字符串中某字符出现的次数：
例：
求A1单元格中字符"
a"
出现的次数：
=LEN（A1）-LEN（SUBSTITUTE（A1,"
））
二、如何在不同工作薄之间复制宏：
1、打开含有宏的工作薄，点“工具/宏（M）…”，选中你的宏，点“编辑”，这样就调出了VB编辑器界面。
2、点“文件/导出文件”，在“文件名”框中输入一个文件名（也可用默认的文件名），注意扩展名为“.bas”，点“保存”。
3、将扩展名为“.bas”的文件拷贝到另一台电脑，打开EXCEL，点“工具/宏/VB编辑器”，调出VB编辑器界面，点“文件/导入文件”，找到你拷贝过来的文件，点“打开”，退出VB编辑器，你的宏已经复制过来了。
三、如何在EXCEL中设置单元格编辑权限（保护部分单元格）
1、先选定所有单元格,点"
格式"
前面的"
√"
2、再选定你要保护的单元格,点"
前面打上"
3、点"
输入两次密码,点两次"
即可。
四、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色
比如：
A1〉1时，C1显示红色
0<
A1<
1时，C1显示绿色
0时，C1显示黄色
方法如下：
1、单元击C1单元格，点“格式”>
“条件格式”，条件1设为：
公式=A1=1
2、点“格式”->
“字体”->
“颜色”，点击红色后点“确定”。
条件2设为：
公式=AND（A1>
0,A1<
1）
3、点“格式”->
“颜色”，点击绿色后点“确定”。
条件3设为：
公式=A1<
0
点“格式”->
“颜色”，点击黄色后点“确定”。
4、三个条件设定好后，点“确定”即出。
五、EXCEL中如何控制每列数据的长度并避免重复录入
1、用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围，点"
，"
有效性条件"
设成"
文本长度"
等于"
5"
（具体条件可根据你的需要改变）。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点"
2、用条件格式避免重复。
选定A列，点"
条件格式"
，将条件设成“公式=COUNTIF（$A:
$A,$A1）>
1”，点"
字体"
颜色"
，选定红色后点两次"
这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。
六、在EXCEL中如何把B列与A列不同之处标识出来？
（一）、如果是要求A、B两列的同一行数据相比较：
假定第一行为表头，单击A2单元格，点“格式”->
“条件格式”，将条件设为:
“单元格数值”“不等于”=B2
“颜色”，选中红色，点两次“确定”。
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
（二）、如果是A列与B列整体比较（即相同数据不在同一行）：
“公式”=COUNTIF（$B:
$B,$A2）=0
按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B列有A列无的数据标记为红色字体。
七、在EXCEL中建立下拉列表按钮
选定你要设置下拉列表的单元格，点“数据”->
“有效性”->
“设置”，在“允许”下面选择“序列”，在“来源”框中输入你的下拉列表内容，各项之间用半角逗号隔开，如：
A,B,C,D
选中“提供下拉前头”，点“确定”。
八、阿拉伯数字转换为大写金额
假定你要在A1输入阿拉佰数字,B1转换成中文大写金额（含元角分）,请在B1单元格输入如下公式:
=IF（INT（A1）*100=A1*100,TEXT（INT（A1）,"
元整"
IF（INT（A1*10）=A1*10,TEXT（INT（A1）,"
元"
TEXT（A1*10-INT（A1）*10,"
角整"
TEXT（INT（A1）,"
IF（INT（A1*10）=INT（A1）*10,"
零"
TEXT（RIGHT（INT（A1*10））,"
角"
TEXT（RIGHT（A1*100）,"
分"
九、EXCEL中怎样批量地处理按行排序
假定有大量的数据（数值），需要将每一行按从大到小排序，如何操作？
由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。
所以，这一问题不能用排序来解决。
解决方法如下：
1、假定你的数据在A至E列，请在F1单元格输入公式：
=LARGE（$A1:
$E1,COLUMN（A1））
用填充柄将公式向右向下复制到相应范围。
你原有数据将按行从大到小排序出现在F至J列。
如有需要可用“选择性粘贴/数值”复制到其他地方。
第1步的公式可根据你的实际情况（数据范围）作相应的修改。
如果要从小到大排序,公式改为:
=SMALL（$A1:
十、巧用函数组合进行多条件的计数统计
第一行为表头，A列是“姓名”，B列是“班级”，C列是“语文成绩”，D列是“录取结果”，现在要统计“班级”为“二”，“语文成绩”大于等于104，“录取结果”为“重本”的人数。
统计结果存放在本工作表的其他列。
公式如下：
=SUM（IF（（B2:
B9999="
二"
）*（C2:
C9999>
=104）*（D2:
D9999="
重本"
）,1,0））
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"
{}"
十一、EXCEL中某个单元格内文字行间距调整方法。
当某个单元格内有大量文字时，很多人都觉得很难将其行间距按自己的要求进行调整。
现介绍一种方法可以让你任意调整单元格内文字的行间距：
右击单元格,点"
设置单元格格式"
对齐"
将"
水平对齐"
选择"
靠左"
垂直对齐"
分散对齐"
自动换行"
，点“确定”。
你再用鼠标将行高根据你要求的行距调整到适当高度即可。
绿色内容为关键点，很多人就是这一点设置不对而无法调整行间距。
十二、如何在EXCEL中引用当前工作表名
如果你的工作薄已经保存,下面公式可以得到单元格所在工作表名:
十三、相同格式多工作表汇总求和方法
假定同一工作薄有SHEET1至SHEET100共100个相同格式的工作表需要汇总求和，结果放在SHEET101工作表中，请在SHEET101的A1单元格输入：
=SUM（
单击SHEET1标签，按住Shift键并单击SHEET100标签，单击A1单元格，再输入：
此时公式看上去内容如下：
=SUM（'
SHEET1:
SHEET100'
!
A1）
按回车后公式变为
=SUM（SHEET1:
SHEET100!
所以，最简单快捷的方法就是在SHEET101的A1单元格直接输入公式：
然后按回车。
十四、如何判断单元格里是否包含指定文本？
假定对A1单元格进行判断有无"
指定文本"
以下任一公式均可:
=IF（COUNTIF（A1,"
*"
）=1,"
有"
无"
=IF（ISERROR（FIND（"
A1,1））,"
十五、如何替换EXCEL中的通配符“?
”和“*”？
在EXECL中查找和替换时，?
代表任意单个字符，*代表任意多个字符。
如果要将工作表中的"
?
替换成其他字符，就只能在查找框中输入~?
~和~*~才能正确替换。
另外如果要替换~本身,在查找框中要输入~~才行。
十六、EXCEL中排名次的两种方法：
（一）、用RANK（）函数：
假定E列为成绩，F列为名次，F2单元格公式如下：
=RANK（E2,E:
E）
这种方法，分数相同时名次相同，随后的名次将空缺。
例如：
两个人99分，并列第2名，则第3名空缺，接下来是第4名。
（二）、用公式排序：
假定成绩在E列,请在F2输入公式：
=SUMPRODUCT（（INDIRECT（"
E$2:
E$"
MAX（IF（E$2:
E$10000<
ROW（E$2:
E$10000））））>
E2）*（1/COUNTIF（INDIRECT（"
E$10000））））,INDIRECT（"
E$10000）））））））+1
公式以Ctrl+Shift+Enter三键结束。
第二种方法分数相同的名次也相同，不过随后的名次不会空缺。
十七、什么是单元格的相对引用、绝对引用和混合引用？
相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。
具体情况举例说明：
1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：
=A1+B1
当将公式复制到C2单元格时变为：
=A2+B2
当将公式复制到D1单元格时变为：
=B1+C1
2、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：
=$A$1+$B$1
当将公式复制到C2单元格时仍为：
当将公式复制到D1单元格时仍为：
3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：
=$A1+B$1
=$A2+B$1
=$A1+C$1
规律：
加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。
混合引用时部分地址发生变化。
注意：
工作薄和工作表都是绝对引用，没有相对引用。
技巧：
在输入单元格地址后可以按F
升级会员 