|
一、如何快速选定不相邻的多个单元格区域？ 比如：要同时选定A1:A100和D1:D100两个区域。 方法一： 在单元格地址栏直接输入： A1:A100,D1:D100 回车。 方法二： 单击A1，按住SHIFT单击A100，按住CTRL（要松开SHIFT键）单击D1，按住SHIFT（要松开CTRL键）单击D100。 二、如何用函数对两个工作表进行整行比较？ 假定两个工作表（SHEET1和SHEET2）均有4列，行数和数据顺序不一样，现在要找到两个表中完全相同的记录。请在SHEET1表的E1输入公式： =IF(SUMPRODUCT((Sheet2!A$1:A$1000=A1)*(Sheet2!B$1:B$1000=B1)*(Sheet2!C$1:C$1000=C1)*(Sheet2!D$1:D$1000=D1)),"有","") 将公式向下复制。 将SHEET1表按E列排序，所有E列含有“有”的行就集中在一起了。 三、如何查找某个数据在一个单元格区域中的位置，并得到其所有单元格名称？ 假定要在A1:E100区域查找“中国”这个词所在的所有单元格的名字，先将A1:E100区域定义名称为“DATA”（主要是缩短公式并增加通用性），然后在F1单元格输入公式： =IF(COUNTIF(DATA,"中国"),IF(ROW()>COUNTIF(DATA,"中国"),"",ADDRESS(INT(SMALL(IF(DATA="中国",ROW(DATA)*100+COLUMN(DATA)),ROW())/100),MOD(SMALL(IF(DATA="中国",ROW(DATA)*100+COLUMN(DATA)),ROW()),100),4,1)),"没有") 公式以CTRL+SHIFT+回车结束。 将公式向下复制，直到出现空白。 四、用LOOKUP、CHOOSE等函数替代IF函数进行多条件判断的方法一例： A列为文本格式的数值（01、02、03……48、49），现要将其分为“左边”和“右边”两组标明在B列，分组条件是01 02 03 04 08 09 10 11 15 16 17 18 22 23 24 29 30 31 36 37 38 43 44 45为“左边”，05 06 07 12 13 14 19 20 21 25 26 27 28 32 33 34 35 39 40 41 42 46 47 48 49为“右边”。 公式一： =CHOOSE(MOD(1*A1,7)+1,"右","左","左","左",IF(1*A1<24,"左","右"),"右","右")&"边" 公式二： =IF(SUMPRODUCT((1*A1>={1,8,15,22,29,36,43})*(1*A1<={4,11,18,24,31,38,45})),"左","右")&"边" 公式三： =IF(ISERR(FIND(A1,"01 02 03 04 08 09 10 11 15 16 17 18 22 23 24 29 30 31 36 37 38 43 44 45")),"右边","左边") 公式四： =LOOKUP(1*A1,{1,5,8,12,15,19,22,25,29,32,36,39,43,46;"左","右","左","右","左","右","左","右","左","右","左","右","左","右"})&"边" 从公式长度来看，公式一是最短的，但是它需要数据有一定规律才能实现。公式四是最长的，但是它只用了一个函数，对于按照数值大小来分组（可以分为更多的组）的条件判断最适合，具有通用性。公式二和公式三只适合将数据分为两组的情况，如果分为多组公式会很长。另外，公式三要求原数据必须为“文本”格式，其他三个公式无此要求（既可以是文本也可以是数值格式）。 在实际工作中可以根据需要灵活使用不同的公式。 五、用星号('*')代替工作表名称快速输入公式（收集于OFFICE精英俱乐部） 比如当前工作薄中有SHEET1至SHEET100共100个工作表，现在你要在SHEET1工作表的A1单元格对后面99个工作表的B1单元格求和，可输入公式： =SUM(‘*’!B1) 回车后公式自动变为： =SUM(SHEET2:SHEET100!B1) 也就是说，公式中’*’可以代替本工作薄中除本工作表以外的所有工作表名。 六、利用公式快速制作工资条(隔行插入表头)的方法: 假定原数据在SHEET1中，第一行为表头，请在SHEET2的A1单元格输入公式： =IF(MOD(ROW(),2),Sheet1!A$1,OFFSET(Sheet1!$A$1,ROW()/2,COLUMN()-1,,)) 将公式向右向下复制就可以了。 =CHOOSE(MOD(ROW()-1,3)+1,SHEET1!A$1,INDEX(SHEET1!A:A,INT((ROW()+2)/3)),"") 七、利用Ctrl＋*快速选定整个工作表区域(收集): 如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl＋*键可选定整个表格。Ctrl＋*选定的区域为：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。
按ALT+178输入平方，按ALT+179输入立方。并且用这种方法输入的上标“平方”和“立方”还可以用在函数和公式中。 九、VLOOKUP函数查找右边列的值后得到同一行左边列的值: 比如在SHEET2表的B1单元格通过公式在SHEET1表的B列查找SHEET2表中A1的值,找到后返回SHEET1表中同一行中A列的值,公式如下: =VLOOKUP(A1,IF({1,0},SHEET1!B$1:B$1000,SHEET1!A$1:A$1000),2,0) 公式可向下复制. 要点:公式中IF函数中的两个区域不能引用整列。否则公式会出错。 当然，这种公式其实可以用INDEX和MATCH两个函数代替。 十、如何将A1单元格中输入的数值累加到B2单元格中？ 先点"工具"->"选项"->"重新计算"，选中"自动重算"和"迭代计算"，将"最多迭代次数"设为1，然后在B2单元格输入公式: =IF((CELL("ROW")=ROW()-1)*(CELL("COL")=1),A1+B2,B2) 这样设置好后,在A1输入的数值会自动累加的B2单元格中，而在其他单元格输入数值时，B2单元格的数值不会变化。 十一、快速输入数值尾部多个零的简便方法: 在单元格中直接输入: =125**6 单元格值自动会变为125000000. 如果先将单元格格式设为自定义类型"0",就可以不用输入等号,直接输入125**6即可. EXCEL实用操作技巧(二) 一、如何改变Excel中起始页的页号？ 假如起始页页号要为7，点"视图"->"页眉页脚",点"自定义页脚,将"第 &[页码] 页"改为"第 &[页码]+6 页",点两次"确定"。 二、Excel中当被引用单元格发生剪切操作时,如何保持引用单元格的内容仍然引用原位置的内容？ 例如，A1等于B5时,当B5的内容剪切到C8时,A1将等于C8的内容。如果要求不管B5作什么操作, A1始终要等于B5的内容，A1单元格公式如下： =INDIRECT("B5") 三、如何在公式单元格前面插入或删除列时始终引用当前单元格左边相邻单元格的值？ 比如C1单元格，目前引用的是B1单元格的值。要求，当在C列前面插入一列时，公式单元格（变为D1）仍然引用相邻左边单元格C1的值。而当在C列前面删除一列时，公式单元格（变为B1）仍然引用相邻左边单元格A1的值。 公式： =OFFSET(A1,0,COLUMN()-2,1,1) 四、如何只显示（筛选）奇数行或偶数行？ 在后面空白列(假定为F列)的第2行输入筛选条件: 五、如何仅通过自定义单元格格式设置让数据缩小1000倍显示，并且千分位后的内容不显示？ 比如让333000111显示成333,000 输入数字后,将单元格格式设为"自定义",在"类型"框中输入: 六、在excel表格中的涉密内容如何不让其显示？ 用exce记录合同信息，有些合同涉密，不希望显示出来，或者用*号显示，比如：在一个单元格里输入“合同名称”四个字，但我希望别人打开这个表格时看到的这个单元格里显示的是“****”，而我通过某种途径还可以知道这个“****”后面的内容是“合同名称”。 先在那个单元格输入公式: 七、如何将excel表中所有的空单元格填上某个数字? 选定表格区域范围,点"编辑"->"定位",点"条件定位",选中"空值",点"确定",输入某个数字,按Ctrl+回车。 八、如何根据A列的身份证号码在B列判断女的是否大于40岁，男的是否大于50岁？ 在B1输入公式: 九、如何计算单元格中数学表达式的值？ 假定A1内容为21*17+5/4,要在B1单元格计算其结果。 单击B1，然后点“插入”菜单选择“名称”命令中的“定义”子命令，出现“定义名称”对话框。 公式可向下复制。 十、如何将EXCEL中的信息导入Outlook Express的联系人中？ 在EXCEL中点"文件"->"另存为","保存类型"中选择"CSV(逗号分隔)(*.csv)",文件名任起。 在Outlook Express中点"文件"->"导入"->"其他通讯薄",选中"文本文件(以逗号分隔)",点"导入",找到你保存的*.csv文件,按提示分别导入各项内容即可。 十一、如何求单元格中任意数字的所有各个位数之和？ 如5126各位数的和5+1+2+6=14，公式如下： =SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) 注：A1可以为任意位数字。 十二、如何让用户在C1单元格选择的月份后，A列中自动显示当年当月所有日期，而在B列自动填充对应的星期几？ 假定C1单元格用数据有效性设置一个选择(1-12)月份的下拉菜单,A1单元格输入公式: 将A列单元格格式设置为你要的日期格式。 十三、如何在单元格中引用工作表名、工作簿名、工作簿所在文件夹名？ 在工作簿已经保存的情况下，CELL("filename")可以得到带完整路径的工作表名。用字符函数截取相应的部分即可。 引用工作表名： =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) 引用工作簿名： =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-5) 引用工作簿所在文件夹名： =TRIM(LEFT(RIGHT(SUBSTITUTE(CELL("filename"),"\",REPT(" ",100)),200),100)) 十四、如何填充特殊序列（如第一期、第二期……，第壹号、第贰号……）？ 有两种方法： 一是设置“自定义”单元格格式，“类型”分别为： [DBNum1]"第"G/通用格式"期" [DBNum2]"第"G/通用格式"号" 然后输入阿拉伯数字进行填充。 二是利用公式直接生成，公式分别为： ="第"&TEXT(ROW(A1),"[DBNum1]G/通用格式")&"期" ="第"&TEXT(ROW(A1),"[DBNum2]G/通用格式")&"号" 十五、EXCEL中如何使用自定义函数？ 打开EXCEL，按Alt+F11调出VBA编辑器，点“插入”->“模块”，将自定义的函数模块内容加入进去。 比如“将单元格字符串反序输出”的函数模块： Public Function MyStrReverse(ByVal sString As Variant) As String 如果你要对软件保密的话，在“模块”上按右键，[VBAproject属性]→[保护中设置密码]，别人就看不到你的源程序了。 退出VBA编辑器，返回到EXCEL界面，点“文件”->“另存为”，“保存类型”选择“Microsoft Office Excel加载宏”，取名为“字符串反序输出”，会自动加上扩展名.xla。 使用时，点“工具”->“加载宏”，点“浏览”找到你保存的“字符串反序输出.xla”文件，点“确定”。 如果“可用加载宏”里面已经有此项，选中后点“确定”即可。 假定A1为“ijdf152中国人id897”，在B1直接输入自定义的函数： = MyStrReverse(A1) 即可得到结果“798di人国中251fdji”。 十六、EXCEL中如何设置二级关联下拉菜单? 十七、EXCEL中如何提取汉字的拼音首字母?(收集) 十八、EXCEL中如何禁止输入完全相同的行 假定你准备将数据输入到A1:C100区域(三列100行),先用鼠标选定该区域,点"数据"->"有效性"->"设置","允许"下面选择"自定义",在"公式"下面的框中输入: 十九、如何查找某列中不重复的数据并连续存放在另一列中? 假定原数据在A1:A100区域中，其中有若干数据重复，先将A1数据复制到B1，然后在B2单元格输入数组公式： 一、求字符串中某字符出现的次数：
例：求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显示红色
0<A1<1时，C1显示绿色
A1<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
选中“提供下拉前头”，点“确定”。
八、阿拉伯数字转换为大写金额(最新收集)
假定你要在A1输入阿拉佰数字,B1转换成中文大写金额(含元角分),请在B1单元格输入如下公式:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(A1>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A1))),"[dbnum2]")&TEXT(RIGHT(FIXED(A1),2),"[dbnum2]元0角0分;;元"&IF(ABS(A1)>1%,"整",)),"零角",IF(ABS(A1)<1,,"零")),"零元",),"零分","整")
九、EXCEL中怎样批量地处理按行排序
假定有大量的数据(数值)，需要将每一行按从大到小排序，如何操作？
由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以，这一问题不能用排序来解决。解决方法如下：
1、假定你的数据在A至E列，请在F1单元格输入公式： =LARGE($A1:$E1,COLUMN(A1)) 用填充柄将公式向右向下复制到相应范围。 你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值”复制到其他地方。 注：第1步的公式可根据你的实际情况（数据范围）作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1)) 十、巧用函数组合进行多条件的计数统计
例：第一行为表头，A列是“姓名”，B列是“班级”，C列是“语文成绩”，D列是“录取结果”，现在要统计“班级”为“二”，“语文成绩”大于等于104，“录取结果”为“重本”的人数。统计结果存放在本工作表的其他列。
公式如下：
=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。
十一、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、相对引用，复制公式时地址跟着发生变化，如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()))
将公式向下填充，直到出错为止，目录就生成了。 |
|
|
