exce实用l操作技巧

1、EXCEL实用操作技巧2007-11-17 13:12:47| 分类：默认分类|举报|字号订阅一、如何改变Excel中起始页的页号?假如起始页页号要为7，点"视图"->"页眉页脚"，点"自定义页脚,将"第&页码 页"改为"第&页码+6页",点两次"确定"。二、Excel中当被引用单元格发生剪切操作时，如何保持引用单元格的内容仍 然引用原位置的内容？例如，A1等于B5时，当B5的内容剪切到C8时,A1将等于C8的内容。如 果要求不管B5作什么操作，A1始终要等于B5

2、的内容，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选定该表所有数据列，点"数据&qu

3、ot;->"筛选"->"高级筛选"，条件区域选择:=$F$1:$F$2点"确定"。这样就只显示偶数行。如果要只显示奇数行，将公式改为:=MOD(ROW(A2),2)=1五、如何仅通过自定义单元格格式设置让数据缩小1000倍显示，并且千分位后的内容不显示?比如让333000111显示成333,000输入数字后，将单元格格式设为"自定义"，在"类型"框中输入:#,#0,""点"确定"。六、在excel表格中的涉密内容如何不让其显示?用exce记录合

4、同信息，有些合同涉密，不希望显示出来，或者用*号显示,比如：在一个单元格里输入 合同名称”四个字，但我希望别人打开这个表格时看 到的这个单元格里显示的是 “ *，而我通过某种途径还可以知道这个“ *后面的内容是合同名称”先在那个单元格输入公式：=IF(IV1=123,"合同名称","*"然后选定全表，点"格式->"单元格"->"保护"，取消"锁定"前面的对勾。再选定公式单元格，点"格式->"单元格"->"保护"，

5、选中"锁定"和"隐藏"。点"工具"->"保护"->"保护工作表"，输入两次密码，点"确定"。这样设置好后别人看到的就是星号，也不能对这个单元格删除和修改。你自己要看的时候，点"工具"->"保护"->"撤消工作表保护",输入密码，这时 点击该单元格时编辑栏可以看到公式内容，但单元格显示的还是星号。你再在 IV1单元格输入123，该单元格就显示为合同名称”了。七、如何将excel表中所有的空单元格

6、填上某个数字？选定表格区域范围，点"编辑"->"定位",点"条件定位",选中"空值",点"确定"，输 入某个数字，按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

7、),"是","否")将公式向下复制。九、如何计算单元格中数学表达式的值?假定A1内容为21*17+5/4,要在B1单元格计算其结果。单击B1，然后点插入”菜单选择 名称”命令中的定义”子命令，出现 定义名 称”寸话框。在在当前工作表中的名称”中输入定义的名称“X'在下方的引用位置”编辑 栏中输入：=EVALUATE(A1)单击确认按钮退出。在B1中输入“ =X'注:不含引号）公式可向下复制。十、如何将EXCEL中的信息导入 Outlook Express的联系人中?在EXCEL中点"文件"->"另存为&

8、quot;,"保存类型"中选择"CSV（逗号分隔）（*csv）", 文件名任起。在Outlook Ex press中点"文件"->"导入"->"其他通讯薄"，选中"文本 文件（以逗号分隔）",点"导入",找到你保存的*.csv文件，按提示分别导入各项内容 即可。Outlook 2003可以直接导入EXCEL文件（需要安装转换插件）。卜一、如何求单元格中任意数字的所有各个位数之和?如5126各位数的和5+1+2+6=14，公式如下:=SU MP

9、RODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1),1)注：A1可以为任意位数字。十二、如何让用户在C1单元格选择的月份后，A列中自动显示当年当月所 有日期，而在B列自动填充对应的星期几？假定C1单元格用数据有效性设置一个选择(1-12)月份的下拉菜单,A1单元 格输入公式：=IF(C$1="","",IF(C$1v>MONTH(DATE(YEAR(TODAY(),C$1,ROW(),"",DATE(YEAR(TODAY(),C$1,ROW()B1单元格输入公式：=IF

10、(A1="","",TEXT(WEEKDAY(A1),"AAAA")将两个公式向下复制到第31行。将A列单元格格式设置为你要的日期格式。十三、如何在单元格中引用工作表名、工作簿名、工作簿所在文件夹名?在工作簿已经保存的情况下，CELL("filename")可以得到带完整路径的工作 表名。用字符函数截取相应的部分即可。引用工作表名:=RIGHT(CELL("file name"),LEN(CELL("file name")-FIND(T,CELL("file nam

11、e")引用工作簿名:=MID(CELL("file name"),FIND(T,CELL("file name")+1,FIND(T,CELL("filename")-FIND(T,CELL("file name")-5)引用工作簿所在文件夹名:=TRIM(LEFT(RIGHT(SUBSTITUTE(CELL("file name"),"",RE PT("",100),200),100)十四、如何填充特殊序列(如第一期、第二期，第壹号、第贰号)?

12、有两种方法:是设置自定义”单元格格式，类型”分别为:DBNumI"第"G/通用格式"期"DBNum2"第"G/通用格式"号"然后输入阿拉伯数字进行填充。二是利用公式直接生成，公式分别为:="第"& TEXT(R0W(A1),"DBNum1G/ 通用格式")&"期"="第"& TEXT(R0W(A1),"DBNum2G/ 通用格式")&"号"十五、EXCEL中如何使用

13、自定义函数?打开EXCEL，按Alt+F11调出VBA编辑器，点 插入”-> 模块”将自定义 的函数模块内容加入进去。比如将单元格字符串反序输出”的函数模块:Pu blic Fu nction MyStrReverse(ByVal sStri ng As Varia nt) As Stri ngApp licatio n.VolatileDim strResult As Stri ng strResult = StrReverse(sStri ng)MyStrReverse = strResultEnd Function如果你要对软件保密的话，在 模块”上按右键，VBAproject属性

14、-保护中 设置密码，别人就看不到你的源程序了。退出VBA编辑器，返回到EXCEL界面，点 文件”> 另存为”保存类型” 选择“Microsoft Office Excel加载宏”取名为 字符串反序输出”，会自动加上扩 展名.xla。使用时，点工具”-> 加载宏”点浏览”找到你保存的字符串反序输出.xla ” 文件，点确定”如果可用加载宏”里面已经有此项，选中后点 确定”即可。假定A1为“ijdf152中国人id897”，在B1直接输入自定义的函数:=MyStrReverse(A1)即可得到结果“798d人国中251fdji十六、EXCEL中如何设置二级关联下拉菜单？将你的一级菜单的

15、内容输入到 SHEET1工作表的第一行（假定为A1:G1）,选定A1:G1,将该区域命名为"一级"（注:不含引号）,将一级菜单对应的二 级菜单内容分别输入到A至G列第二行以下的各行中（假定为第2至10行）。在SHEET2中，第一行为表头，选定 A2:A10（可根据你的需要改变范围），点"数据"->"有效性"->"设置"，允许下面选择"序列"，在来源框中输入=一级点"确定"。用鼠标选定B2:B10,点"数据"->"有效性"

16、;->"设置",允许下面选择"序列"，在来源框中输入下面的公式：=INDIRECT("sheet1!"&CHAR(64+MATCH(A2,级,0)&"2:"&CHAR(64+MATCH(A2, 一级,0)&"10")点"确定"。这样SHEET2工作表中A2:A10有一级下拉菜单的内容供选择,B2:B10有对应的二级下拉菜单内容供选择。十七、EXCEL中如何提取汉字的拼音首字母?（收集）=IF(A1="","&

17、quot;,LOOKU P(C0DE(A1),45217+0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,77 63,8472,9264,"A","B","C","D","E","F","G","H","J","K","L","M

18、","N","O"," P","Q","r","S","t","w","X","Y","Z")注:公式只对常用汉字有效，部分生辟汉字未按拼音排列，无法用公式找到。十八、EXCEL中如何禁止输入完全相同的行假定你准备将数据输入到 A1:C100区域（三列100行），先用鼠标选定该 区域，点"数据"->"有效性"->"设置","允许"下面选择"自定义"，在"公式"下面的框中 输入：=SU MP RODUCT($A$1:$A$100