EXCEL实用操作技巧.doc

EXCEL实用操作技巧一、如何快速选定不相邻的多个单元格区域？比如：要同时选定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、0348、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=1,8,15,22,29,36,43)*(1*A1选项-重新计算，选中自动重算和迭代计算，将最多迭代次数设为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行输入筛选条件: =MOD(ROW(A2),2)=0 选定该表所有数据列,点数据-筛选-高级筛选,条件区域选择: =$F$1:$F$2 点确定。这样就只显示偶数行。 如果要只显示奇数行，将公式改为： =MOD(ROW(A2),2)五、如何仅通过自定义单元格格式设置让数据缩小1000倍显示，并且千分位后的内容不显示？比如让333000111显示成333,000输入数字后,将单元格格式设为自定义,在类型框中输入: #,#0, 点确定。六、在excel表格中的涉密内容如何不让其显示？用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,3),2),50,40),是,否) 将公式向下复制。九、如何计算单元格中数学表达式的值？假定A1内容为21*17+5/4,要在B1单元格计算其结果。单击B1，然后点“插入”菜单选择“名称”命令中的“定义”子命令，出现“定义名称”对话框。 在“在当前工作表中的名称”中输入定义的名称“X”，在下方的“引用位置”编辑栏中输入: =EVALUATE(A1) 单击确认按钮退出。 在 B1中输入“=X” (注:不含引号)公式可向下复制。十、如何将EXCEL中的信息导入Outlook Express的联系人中？在EXCEL中点文件-另存为,保存类型中选择CSV(逗号分隔)(*.csv),文件名任起。 在Outlook Express中点文件-导入-其他通讯薄,选中文本文件(以逗号分隔),点导入,找到你保存的*.csv文件,按提示分别导入各项内容即可。Outlook 2003可以直接导入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$1MONTH(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(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),DBNum1G/通用格式)&期=第&TEXT(ROW(A1),DBNum2G/通用格式)&号十五、EXCEL中如何使用自定义函数？打开EXCEL，按Alt+F11调出VBA编辑器，点“插入”-“模块”，将自定义的函数模块内容加入进去。比如“将单元格字符串反序输出”的函数模块：Public Function MyStrReverse(ByVal sString As Variant) As String Application.Volatile Dim strResult As String strResult = StrReverse(sString) MyStrReverse = strResultEnd Function如果你要对软件保密的话，在“模块”上按右键，VBAproject属性保护中设置密码，别人就看不到你的源程序了。退出VBA编辑器，返回到EXCEL界面，点“文件”-“另存为”，“保存类型”选择“Microsoft Office Excel加载宏”，取名为“字符串反序输出”，会自动加上扩展名.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,点数据-有效性-设置,允许下面选择序列,在来源框中输入下面的公式:=INDIRECT(sheet1!&CHAR(64+MATCH(A2,一级,0)&2:&CHAR(64+MATCH(A2,一级,0)&10) 点确定。 这样SHEET2工作表中A2:A10有一级下拉菜单的内容供选择，B2:B10有对应的二级下拉菜单内容供选择。十七、EXCEL中如何提取汉字的拼音首字母?(收集)=IF(A1=,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单元格直接输入: =INDEX(A:A,SMALL(IF(MATCH(A$1:A$100,A$1:A$100,)=ROW(A$1:A$100),ROW(A$1:A$100),65536),ROW()& 公式以CTRL+SHIFT+回车结束。二十、如何将一个单元格中的数字去掉重复的后从小到大排序输出在另一个单元格中?假定A1为209537233，要在B1得到023579，公式如下：=IF(ISERROR(FIND(0,A1),0)&SUBSTITUTE(SUM(IF(FIND(ROW($1:$9),A1&123456789)单元格-保护,取消锁定前面的。2、再选定你要保护的单元格,点格式-单元格-保护,在锁定前面打上。3、点工具-保护-保护工作表,输入两次密码,点两次确定即可。四、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如：A11时，C1显示红色0A11时，C1显示绿色A1“条件格式”，条件1设为：公式 =A1=12、点“格式”-“字体”-“颜色”，点击红色后点“确定”。条件2设为：公式 =AND(A10,A1“字体”-“颜色”，点击绿色后点“确定”。条件3设为：公式 =A1“字体”-“颜色”，点击黄色后点“确定”。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)=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名。（二）、用公式排序(中国式排名)： 假定成绩在E列,请在F2输入公式：=SUM(IF(E$2:E$1000E2,1/COUNTIF(E$2:E$1000,E$2:E$1000)+1公式以Ctrl+Shift+Enter三键结束。第二种方法分数相同的名次也相同，不过随后的名次不会空缺。十七、什么是单元格的相对引用、绝对引用和混合引用？相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。 具体情况举例说明：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编辑器-插入-模块,输入如下内