EXCEL使⽤技巧收集（⾮常实⽤的⼏个：符合条件，显⽰特定颜⾊控制单元格
输⼊数字长度A...
EXCEL使⽤技巧收集（⾮常实⽤的⼏个：符合条件，显⽰
特定颜⾊/控制单元格输⼊数字长度/AB两列查不同之
(2013-01-25 10:58:32)
转载▼
标签：
杂谈
EXCEL使⽤技巧收集
EXCEL使⽤技巧(1)
⼀、求字符串中某字符出现的次数：
例：求A1单元格中字符"a"出现的次数：
=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))
⼆、如何在不同⼯作薄之间复制宏：
1、打开含有宏的⼯作薄，点“⼯具/宏(M)…”，选中你的宏，点“编辑”，这样就调出了VB编辑器界
⾯。
2、点“⽂件/导出⽂件”，在“⽂件名”框中输⼊⼀个⽂件名（也可⽤默认的⽂件名），注意扩展名
为“.bas”，点“保存”。
3、将扩展名为“.bas”的⽂件拷贝到另⼀台电脑，打开EXCEL，点“⼯具/宏/VB编辑器”，调出VB
编辑器界⾯，点“⽂件/导⼊⽂件”，到你拷贝过来的⽂件，点“打开”，退出VB编辑器，你的宏已
经复制过来了。
三、如何在EXCEL中设置单元格编辑权限(保护部分单元格)
1、先选定所有单元格，点"格式"->"单元格"->"保护"，取消"锁定"前⾯的"√"。
2、再选定你要保护的单元格，点"格式"->"单元格"->"保护"，在"锁定"前⾯打上"√"。
3、点"⼯具"->"保护"->"保护⼯作表"，输⼊两次密码，点两次"确定"即可。
四、excel中当某⼀单元格符合特定条件，如何在另⼀单元格显⽰特定的颜⾊
⽐如：
A1〉1时，C1显⽰红⾊
0A1<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列整体⽐较（即相同数据不在同⼀⾏）：
假定第⼀⾏为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为:
“公式”=COUNTIF($B:$B,$A2)=0
点“格式”->“字体”->“颜⾊”，选中红⾊，点两次“确定”。
⽤格式刷将A2单元格的条件格式向下复制。
B列可参照此⽅法设置。
按以上⽅法设置后，AB列均有的数据不着⾊，A列有B列⽆或者B列有A列⽆的数据标记为红⾊字体。
七、在EXCEL中建⽴下拉列表按钮（提供选择项）
选定你要设置下拉列表的单元格，点“数据”->“有效性”->“设置”，在“允许”下⾯选择“序列”，在“来源”框中输⼊你的下拉列表内容，各项之间⽤半⾓逗号隔开，如：
A,B,C,D
选中“提供下拉前头”，点“确定”。
⼋、阿拉伯数字转换为⼤写⾦额
假定你要在A5输⼊阿拉佰数字,B5转换成中⽂⼤写⾦额(含元⾓分)，请在B5单元格输⼊如下公式: =IF((INT(A5*10)-INT(A5)*10)=0,TEXT(INT(A5),"[DBNum2]G/通⽤格
式")&"元"&IF((INT(A5*100)-INT((A5)*10)*10)=0,"整","零"&TEXT(INT(A5*100)-INT(A5*10)*10," [DBNum2]G/通⽤格式")&"分"),TEXT(INT(A5),"[DBNum2]G/通⽤格式")&"元"&IF((INT(A5*100)-INT((A5)*10)*10)=0,TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/通⽤格式")&"⾓
整",TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/通⽤格式")&"⾓"&TEXT(INT(A5*100)-
INT(A5*10)*10,"[DBNum2]G/通⽤格式")&"分"))
九、EXCEL中怎样批量地处理按⾏排序
假定有⼤量的数据，需要将每⼀⾏按从⼤到⼩排序，如何操作？
由于按⾏排序与按列排序都是只能有⼀个主关键字，主关键字相同时才能按次关键字排序。所以，这⼀问题不能⽤排序来解决。解决⽅法如下：
1、假定你的数据在A⾄E列，请在F1单元格输⼊公式：
=LARGE($A1:$E1,COLUMN(A1))
⽤填充柄将公式向下复制到相应⾏。
2、⽤⿏标选定F列，⽤“查/替换”的⽅法，将该列的"$A"替换成"$A$"，"$E"替换成"$E$"。
3、⽤⿏标选定F列所有有公式的单元格，⽤填充柄将公式向右复制到J列。
你原有数据将按⾏从⼤到⼩排序出现在F⾄J列。如有需要可⽤“选择性粘贴/数值”复制到其他地⽅。
注：第1步的公式可根据你的实际情况（数据范围）作相应的修改。
⼗、巧⽤函数组合进⾏多条件的计数统计
例：第⼀⾏为表头，A列是“姓名”，B列是“班级”，C列是“语⽂成绩”，D列是“录取结果”，现在要统计“班级”为“⼆”，“语⽂成绩”⼤于等于104，“录取结果”为“重本”的⼈数。统计结果存放在本⼯作表的其他列。
公式如下：
输⼊完公式后按Ctrl+Shift+Enter键，让它⾃动加上数组公式符号"{}"。
EXCEL使⽤技巧(2)
⼗⼀、EXCEL中某个单元格内⽂字⾏间距调整⽅法。
当某个单元格内有⼤量⽂字时，很多⼈都觉得很难将其⾏间距按⾃⼰的要求进⾏调整。现介绍⼀种⽅法可以让你任意调整单元格内⽂字的⾏间距：
右击单元格，点"设置单元格格式"->"对齐"，将"⽔平对齐"选择"靠左"，将"垂直对齐"选择"分散对齐"，选中"⾃动换⾏"，点“确定”。你再⽤⿏标将⾏⾼根据你要求的⾏距调整到适当⾼度即可。
注：绿⾊内容为关键点，很多⼈就是这⼀点设置不对⽽⽆法调整⾏间距。
⼗⼆、如何在EXCEL中引⽤当前⼯作表名
如果你的⼯作薄已经保存，下⾯公式可以得到单元格所在⼯作表名:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))⼗三、相同格式多⼯作表汇总求和⽅法
假定同⼀⼯作薄有SHEET1⾄SHEET100共100个相同格式的⼯作表需要汇总求和，结果放在SHEET101⼯作表中，请在SHEET101的A1单元格输⼊：
=SUM(
单击SHEET1标签，按住Shift键并单击SHEET100标签，单击A1单元格，再输⼊：
）
此时公式看上去内容如下：
=SUM('SHEET1:SHEET100'!A1)
按回车后公式变为
=SUM(SHEET1:SHEET100!A1)
所以，最简单快捷的⽅法就是在SHEET101的A1单元格直接输⼊公式：
=SUM('SHEET1:SHEET100'!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名。
（⼆）、⽤排序加公式：
1、先在后⾯⽤填充柄增加⼀列（假定为G列）与⾏号相同的序列数。
2、将全表按分数列（E列）排序，在F2单元格输⼊1，在F3单元格输⼊公式：
=IF(E3=E2,F2,F2+1)
将公式向下复制到相应⾏。
3、选定公式列，点“复制”，在F1单元格点右键，点“选择性粘贴/数值”，点“确定”。
4、将全表按最后⼀列（G列）排序，删除最后⼀列。
第⼆种⽅法分数相同的名次也相同，不过随后的名次不会空缺。
⼗七、什么是单元格的相对引⽤、绝对引⽤和混合引⽤？
相对引⽤、绝对引⽤和混合引⽤是指在公式中使⽤单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。
具体情况举例说明：
1、相对引⽤，复制公式时地址跟着发⽣变化，如C1单元格有公式：=A1+B1
当将公式复制到C2单元格时变为：=A2+B2
当将公式复制到D1单元格时变为：=B1+C1
2、绝对引⽤，复制公式时地址不会跟着发⽣变化，如C1单元格有公式：=$A$1+$B$1
当将公式复制到C2单元格时仍为：=$A$1+$B$1
当将公式复制到D1单元格时仍为：=$A$1+$B$1
3、混合引⽤，复制公式时地址的部分内容跟着发⽣变化，如C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$A2+B$1
当将公式复制到D1单元格时变为：=$A1+C$1
规律：加上了绝对地址符“$”的列标和⾏号为绝对地址，在公式向旁边复制时不会发⽣变化，没有加上绝对地址符号的列标和⾏号为相对地址，在公式向旁边复制时会跟着发⽣变化。混合引⽤时部分地址发⽣变化。
注意：⼯作薄和⼯作表都是绝对引⽤，没有相对引⽤。
技巧：在输⼊单元格地址后可以按F4键切换“绝对引⽤”、“混合引⽤”和“相对引⽤”状态。
⼗⼋、求某⼀区域内不重复的数据个数
例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只算⼀个。有两种计算⽅法：⼀是利⽤数组公式：
=SUM(1/COUNTIF(A1:A100,A1:A100))
输⼊完公式后按Ctrl+Shift+Enter键，让它⾃动加上数组公式符号"{}"。
⼆是利⽤乘积求和函数：
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
⼗九、EXCEL中如何动态地引⽤某列的最后⼀个单元格？
在SHEET2中的A1单元格中引⽤表SHEET1中的A列的最后⼀个单元格中的数值(SHEET1中A 列的最后⼀个单元格的数值不确定，随时会增加⾏数)：
=OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0,1,1)
或者:      =INDIRECT("sheet1!A"&COUNTA(Sheet1!A:A))
注：要确保你SHEET1的A列中间没有空格。
⼆⼗、如何在⼀个⼯作薄中建⽴⼏千个⼯作表
右击某个⼯作表标签，点"插⼊"，选择"⼯作表"，点"确定"，然后按住Alt+Enter键不放，你要多少个你就按住多久不放，你会看到⼯作表数量在不断增加,⼏千个都没有问题。
⼆⼗⼀、如何知道⼀个⼯作薄中有多少个⼯作表
⽅法⼀：
点"⼯具"->"宏"->"VB编辑器"->"插⼊"->"模块"，输⼊如下内容:
Sub sheetcount()
Dim num As Integer
num = ThisWorkbook.Sheets.Count
Sheets(1).Select
Cells(1, 1) = num
End Sub
运⾏该宏，在第⼀个(排在最左边的)⼯作表的A1单元格中的数字就是sheet的个数。
⽅法⼆：
按Ctrl+F3(或者点"插⼊"->"名称"->"定义")，打开"定义名称"对话框
定义⼀个X
"引⽤位置"输⼊:
=get.workbook(4)
点"确定"。
然后你在任意单元格输⼊=X
出来的结果就是sheet的个数。
⼆⼗⼆、⼀个⼯作薄中有许多⼯作表如何快速整理出⼀个⽬录⼯作表
1、⽤宏3.0取出各⼯作表的名称，⽅法：
Ctrl+F3出现⾃定义名称对话框，取名为X，在“引⽤位置”框中输⼊：
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,100) 确定
2、⽤HYPERLINK函数批量插⼊连接，⽅法：
在⽬录⼯作表（⼀般为第⼀个sheet）的A2单元格输⼊公式：
=HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))
将公式向下填充，直到出错为⽌，⽬录就⽣成了。
EXCEL使⽤技巧(3)
⼀、建⽴分类下拉列表填充项
  我们常常要将企业的名称输⼊到表格中，为了保持名称的⼀致性，利⽤“数据有效性”功能建了⼀个分类下拉列表填充项。
  1.在Sheet2中，将企业名称按类别（如“⼯业企业”、“商业企业”、“个体企业”等）分别输⼊不同列中，建⽴⼀个企业名称数据库。
  2.选中A列（“⼯业企业”名称所在列），在“名称”栏内，输⼊“⼯业企业”字符后，按“回车”键进⾏确认。
  仿照上⾯的操作，将B、C……列分别命名为“商业企业”、“个体企业”……
  3.切换到Sheet1中，选中需要输⼊“企业类别”的列（如C列），执⾏“数据→有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下⾯的“来源”⽅框中，输⼊“⼯业企业”，“商业企业”，“个体企业”……序列（各元素之间⽤英⽂逗号隔开），确定退出。
  再选中需要输⼊企业名称的列（如D列），再打开“数据有效性”对话框，选中“序列”选
项后，在“来源”⽅框中输⼊公式：=INDIRECT（C1），确定退出。
  4.选中C列任意单元格（如C4），单击右侧下拉按钮，选择相应的“企业类别”填⼊单元格中。然后选中该单元格对应的D列单元格（如D4），单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填⼊该单元格中。
  提⽰：在以后打印报表时，如果不需要打印“企业类别”列，可以选中该列，右击⿏标，选“隐藏”选项，将该列隐藏起来即可。
  ⼆、建⽴“常⽤⽂档”新菜单
  在菜单栏上新建⼀个“常⽤⽂档”菜单，将常⽤的⼯作簿⽂档添加到其中，⽅便随时调⽤。
  1.在⼯具栏空⽩处右击⿏标，选“⾃定义”选项，打开“⾃定义”对话框。在“命令”标签中，选中“类别”下“新菜单”项，再将“命令”下⾯的“新菜单”拖到菜单栏。
  按“更改所选内容”按钮，在弹出菜单的“命名”框中输⼊⼀个名称（如“常⽤⽂档”）。 
2.再在“类别”下⾯任选⼀项（如“插⼊”选项），在右边“命令”下⾯任选⼀项（如“超链接”选项），将它拖到新菜单（常⽤⽂档）中，并仿照上⾯的操作对它进⾏命名（如“⼯资表”等），建⽴第⼀个⼯作簿⽂档列表名称。
  重复上⾯的操作，多添加⼏个⽂档列表名称。
  3.选中“常⽤⽂档”菜单中某个菜单项（如“⼯资表”等），右击⿏标，在弹出的快捷菜单中，选“分配超链接→打开”选项，打开“分配超链接”对话框。通过按“查范围”右侧的下拉按钮，定位到相应的⼯作簿（如“⼯资.xls”等）⽂件夹，并选中该⼯作簿⽂档。
字符串是什么颜  重复上⾯的操作，将菜单项和与它对应的⼯作簿⽂档超链接起来。
  4.以后需要打开“常⽤⽂档”菜单中的某个⼯作簿⽂档时，只要展开“常⽤⽂档”菜单，单击其中的相应选项即可。
  提⽰：尽管我们将“超链接”选项拖到了“常⽤⽂档”菜单中，但并不影响“插⼊”菜单中“超链接”菜单项和“常⽤”⼯具栏上的“插⼊超链接”按钮的功能。
三、让不同类型数据⽤不同颜⾊显⽰
  在⼯资表中，如果想让⼤于等于2000元的⼯资总额以“红⾊”显⽰，⼤于等于1500元的⼯资总额以“蓝⾊”显⽰，低于1000元的⼯资总额以“棕⾊”显⽰，其它以“⿊⾊”显⽰，我们可以这样设置。
  1.打开“⼯资表”⼯作簿，选中“⼯资总额”所在列，执⾏“格式→条件格式”命令，打开“条件格式”对话框。单击第⼆个⽅框右侧的下拉按钮，选中“⼤于或等于”选项，在后⾯的⽅框中输⼊数值“2000”。单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜⾊”设置为“红⾊”。
  2.按“添加”按钮，并仿照上⾯的操作设置好其它条件（⼤于等于1500，字体设置为“蓝⾊”；⼩于1000，字体设置为“棕⾊”）。
  3.设置完成后，按下“确定”按钮。  看看⼯资表吧，⼯资总额的数据是不是按你的要求以不同颜⾊显⽰出来了。
  四、制作“专业符号”⼯具栏
  在编辑专业表格时，常常需要输⼊⼀些特殊的专业符号，为了⽅便输⼊，我们可以制作⼀个属于⾃⼰的“专业符号”⼯具栏。
  1.执⾏“⼯具→宏→录制新宏”命令，打开“录制新宏”对话框，输⼊宏名如“fuhao1” 并将宏保存在“个⼈宏⼯作簿”中，然后“确定”开始录制。选中“录制宏”⼯具栏上的“相对引⽤”按钮，然后将需要的特殊符号输⼊到某个单元格中，再单击“录制宏”⼯具栏上的“停⽌”按钮，完成宏的录制。
  仿照上⾯的操作，⼀⼀录制好其它特殊符号的输⼊“宏”。
  2.打开“⾃定义”对话框，在“⼯具栏”标签中，单击“新建”按钮，弹出“新建⼯具栏”对话框，输⼊名称——“专业符号”，确定后，即在⼯作区中出现⼀个⼯具条。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。