《excel实用操作技巧》由会员分享，可在线阅读，更多相关《excel实用操作技巧（20页珍藏版）》请在金锄头文库上搜索。
1、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
2、!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(D
3、ATA,中国),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 1
4、1 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)这
5、样设置好后,在 A1 输入的数值会自动累加的 B2 单元格中，而在其他单元格输入数值时，B2 单元格的数值不会变化。十一、快速输入数值尾部多个零的简便方法:在单元格中直接输入:=125*6单元格值自动会变为 125000000.如果先将单元格格式设为自定义类型0, 就可以不用输入等号 ,直接输入 125*6即可.EXCEL 实 用操作技巧 (二)一、如何改变 Excel 中起始页的页号？假如起始页页号要为 7，点 视图-页眉页脚,点自定义页脚, 将第 &页码 页改为 第 &页码+6 页, 点两次确定 。二、Excel 中当被引用单元格发生剪切操作时 ,如何保持引用单元格的内容仍然引用原位置的内
6、容？例如，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)四、如何只显示（筛
7、选）奇数行或偶数行？在后面空白列(假定为 F 列 )的第 2 行输入筛选条件: =MOD(ROW(A2),2)=0 选定该表所有数据列,点数据- 筛选-高级筛选,条件区域选择: =$F$1:$F$2 点确定 。这样就只显示偶数行。 如果要只显示奇数行，将公式改为： =MOD(ROW(A2),2)五、如何仅通过自定义单元格格式设置让数据缩小 1000 倍显示，并且千分位后的内容不显示？比如让 333000111 显示成 333,000输入数字后,将单元格格式设为自定义, 在类型框中输入: #,#0, 点确定 。六、在 excel 表格中的涉密内容如何不让其显示？用 exce 记录合同信息，有些合
8、同涉密，不希望显示出来，或者用*号显示，比如：在一个单元格里输入“合同名称”四个字，但我希望别人打开这个表格时看到的这个单元格里显示的是“*”，而我通过某种途径还可以知道这个“*”后面的内容是“合同名称” 。先在那个单元格输入公式: =IF(IV1=123,合同名称,*) 然后选定全表,点格式-单元格-保护, 取消锁定前面的对勾。 再选定公式单元格,点格式-单元格-保护, 选中 锁定和隐藏。 点工具 -保护 -保护工作表,输入两次密码,点 确定。 这样设置好后别人看到的就是星号，也不能对这个单元格删除和修改。 你自己要看的时候，点工具 -保护-撤消工作表保护,输入密码，这时点击该单元格时编辑栏
9、可以看到公式内容，但单元格显示的还是星号。你再在 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),是,否) 将公式
10、向下复制。九、如何计算单元格中数学表达式的值？假定 A1 内容为 21*17+5/4,要在 B1 单元格计算其结果。单击 B1，然后点“插入” 菜单选择“名称”命令中的“定义 ”子命令，出现“定义名称”对话框。 在“在当前工作表中的名称”中输入定义的名称“X”，在下方的“引用位置”编辑栏中输入: =EVALUATE(A1) 单击确认按钮退出。 在 B1 中输入“=X” (注:不含引号 )公式可向下复制。十、如何将 EXCEL 中的信息导入 Outlook Express 的联系人中？在 EXCEL 中点 文件-另存为,保存类型中选择CSV(逗号分隔)(*.csv), 文件名任起。 在 Outl
11、ook 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 单元格用数据有效性设
12、置一个选择(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(fil
13、ename),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第
14、G/通用格式号然后输入阿拉伯数字进行填充。二是利用公式直接生成，公式分别为：=第&TEXT(ROW(A1),DBNum1G/ 通用格式)& 期=第&TEXT(ROW(A1),DBNum2G/ 通用格式)& 号十五、EXCEL 中如何使用自定义函数？打开 EXCEL，按 Alt+F11 调出 VBA 编辑器，点“ 插入”-“模块” ，将自定义的函数模块内容加入进去。比如“将单元格字符串反序输出”的函数模块：Public Function MyStrReverse(ByVal sString As Variant) As StringApplication.VolatileDim strResul
15、t As StringstrResult = 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),选
