《Excel单元格的相对引用和绝对引用.doc》由会员分享,可在线阅读,更多相关《Excel单元格的相对引用和绝对引用.doc(5页珍藏版)》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、Excel单元格的相对引用和绝对引用1. Excel单元格的相对引用和绝对引用相对引用:a1=a2+a3绝对引用:a1=a$2+$a$3绝对引用:a1=$a2+$a$32. Excel公式插入回车符并显示出来自动换行在公式中加”&CHAR(10)&”,同时要将单元格设置为自动换行。3. Excel自动换行的函数Char(10) p 4. Excel单元格不显示0和错误#/0的公式=IF(or(公式=0,ISERROR(公式),公式) =IF(or(公式=0,ISERROR(公式),公式) =IF(or(公式=0,ISERROR(公式),公式) ISNA函数用于测试数值类型,如果测试值为错误值#
2、N/A,则返回TRUE,否则返回FALSE。5. Excel的单元格日期格式转换成文本格式的简单方法如“2008年7月18日”或“2008-7-18”的日期格式转换成“20080718”的8位文本格式。公式“=text(A2,yyyymmdd)”(注:如果日期中还包含时、分、秒,则公式可改成“=text(A2,yyyymmddhhmmss)”。其中的文本格式可以按自己的要求灵活运用,如“yymmdd h:m:s”表示只要年份的后两位、时间中用冒号将时分秒隔开并且不满两位时前面不加“0”。6. Excel返回行号、列号函数返回行号:=row()返回列号:=column()7. Excel页脚 底
3、端标题行设置方法选择页脚区域,按住Shift键,编辑复制图片,再把图片插入到自定义页脚里(2003版可以,2000没有这个功能)。8. Excel如何录制宏执行宏?简单的方法:自己可练习创建一个“宏”,(就是录下自已需要做的操作步骤)方法:点击工具宏录制新宏弹出窗口确认宏名称这时就会弹出一个录像框,你就可以一步一步点击你要做的操作,按下“停止”,就结束了宏录制!你再进入工具宏选择刚录下的宏名称点击“编辑”这里面就是你要的宏代码了,自已可以研究了!如下面就是一个宏,执行后会自己打开:工作表3代码是:Sub 按钮1_单击()Sheet (Sheet3)End Sub9. Excel打开工作薄自动执
4、行宏?先录制宏,再宏名字改成sub auto_open就变成自动了10. Excel击活工作表自动执行宏?Sub Macro适合行高() .宏名 ZYC录制 时间: 2014-9-23 .注释行Private Sub worksheet_activate()Cells(1, 1) = Now()Rows(3:3).SelectRows(3:3).EntireRow.AutoFitEnd Sub先打开对应工作表,操作alt+F11,在视图中选正代码窗口,然后复制粘贴上面代码。11. Excel击活工作表6-10行自动执行最适合的行高,然后选正单元格A6的宏?Private Sub workshe
5、et_activate() Rows(6:10).Select Rows(6:10).EntireRow.AutoFitRange(A6).SelectEnd Sub12. Excel隐藏单元格中的数据?有时候,我们可能不希望别人看到Excel某些单元格或者区域中的数据内容,可以选定这些单元格内容,然后按下“Ctrl+1”组合键,在弹出的“单元格格式”对话框中,单击“数字”选项卡,然后选择“自定义”分类,在其中的“类型”文本框中输入半角符号“;”即可,这样即便是修改字体颜色也无法显示隐藏的内容了。13. Excel统计空白单元格、数字单元格、数字及非空单元格个数?=countBlank(A1:
6、A100) 统计空白单元格数量=count(A1:A100) 统计数字单元格个数=counta(A1:A100) 统计数字及非空单元格个数=countif(A1:A100,A1) 条件统计 统计区域中A1同值的个数14. Excel提取文件夹中的所有文件名称 1、在那个文件夹内新建一个.TXT文件(如wenjian.txt),用记事本单开输入dir 1.txt保存退出将刚才的.TXT(wenjian.txt)更名为.bat文件(wenjian.bat)双击wenjian.bat文件运行一次,在文件夹内多出一个1.txt文件打开1.txt文件,将其中的内容粘贴到Excel中,数据分列处理就可以得
7、到你要的文件名列表了!2、VBA(2003版)在那个文件夹下新建Excel文件,打开新建的Excel文件,右击工作表标签(如Sheet1),查看代码在代码编辑器中输入以下代码Sub Test()Dim i As IntegerDim strPath As StringstrPath = ThisWorkbook.PathWith Application.FileSearch .LookIn = strPath .SearchSubFolders = True .Filename = *.* If .Execute 0 Then For i = 1 To .FoundFiles.Count Ra
8、nge(A & i) = .FoundFiles(i) Next i End IfEnd WithEnd Sub回到Excel表格中,工具宏宏选择Sheet1.Test执行看看效果吧!15. Excel单元格中显示该文件夹内的文件数量或名称 假设这个文件夹是 D:fda1、插入-名称-定义,或按CTRL+F3,打开excel名称管理器定义一个名称比如:aaa引用位置输入:=FILES(d:fda*.*) &T(NOW()2、然后在任一工作表某一单元格输入:=COUNTA(aaa)则返回文件个数。3、如果在任一工作表某一单元格输入: =if(row(a1)counta(aaa),index(aa
9、a,row(a1)4、再下拉公式进行填充,则依序填充文件名。16. Excel一列中输入文件名,另一列自动生成指定文件夹同名文件超链接A列为输入文件名,B列返回超链接B1中输入:=HYPERLINK(D:FDA&A1,A1)其中d:fda为指定文件夹路径。17. Excel中怎么把行转换成列复制需转换的单元格行,选择性粘勾选“转置”。在页面投置工作表选项中勾选单色打印,可以不打印底纹图案。18. Excel高亮显示光标所在行或列?在Excel中选择菜单“格式条件格式”命令,弹出“条件格式”对话框。在“条件”下拉框中选择“公式”并在右侧中输入下面相应的公式,然后点击“格式”按钮,设置相应的格式,
10、完成后,按“确定”按钮。不过当前单元格编辑过才行,按过回车。高亮单元格:条件格式:=CELL(address)=ADDRESS(ROW(),COLUMN()高亮行条件格式:=ROW()=CELL(row)或者: =CELL(row)=ROW()高亮行和列条件格式:=(row()=cell(row)+(column()=cell(col)高亮当前单元格为止的相应行和列,呈反L形条件格式:=OR(AND(CELL(row)=ROW(),CELL(col)+1COLUMN(),AND(CELL(col)=COLUMN(),CELL(row)+1ROW()高亮当前单元格为止的相应行和列,呈反L形,且当
11、前单元格背景色改变、字体加粗:条件格式:=CELL(address)=ADDRESS(ROW(),COLUMN()”“=OR(AND(CELL(row)=ROW(),CELL(col)+1COLUMN(),AND(CELL(col)=COLUMN(),CELL(row)+1ROW()19. Excel单元格中插入页码方法(以Excel 2003版本为基础,下文中所有“填写”后跟的内容不包括内容最外侧的全角引号)一、定义引用项(以下定义方法通过单击菜单“插入/名称/定义”弹出定义对话框操作)1、定义“页码”:“名称”中填写“页码”,“引用位置”中填写“=IF(ISERROR(MATCH(ROW(
12、),GET.DOCUMENT(64),1),1,MATCH(ROW(),GET.DOCUMENT(64),1)+1)”2、定义“总页数”:“名称”中填写“总页数”,“引用位置”中填写“=GET.DOCUMENT(50)+RAND()*0”。3、定义“第 N 页”:“名称”中填写“第 N 页”,“引用位置”中填写“=第 &页码& 页”。4、定义“第 N 页 共 ? 页”:”:“名称”中填写“第 N 页 共 ? 页”,“引用位置”中填写“=第 &页码& 页 共 &总页数& 页”。二、单元格插入页码1、插入法:指定单元格,单击菜单“插入/名称/粘贴”,根据需要的格式选择即可。2、输入法:指定单元格,
13、在上部输入区输入等号加上面定义的名称,如“=第 N 页 共 ? 页”就是本例提问的格式。三、深入探讨通过分析上述“定义引用项”内容,您会发现,其实只需要定义“页码”和“总页数”就可以了。需要使用的时候,可直接指定单元格,输入上述“引用内容”的内容就可达成格式化页码的需要。如本例提问要求的格式化日期,可直接指定单元格后输入“=第 &页码& 页 共 &总页数& 页”就OK了。20. Excel隔行变色的方法条件格式公式说明:=MOD(ROW(),2)=0,实现的效果是偶数行自动填充底纹颜色=MOD(ROW(),2)=1,实现的效果是奇数行自动填充底纹颜色如果需要奇偶行都设置颜色,只需要设定两个条件
14、即条件1和条件2同样的方法,可以实现excel隔列变色,只需将公式中的row变为COLUMN就可以了,即:=MOD(COLUMN(),2)=021. Excel字符替代函数=replace(目前的文本,想从第几个数字开始改,共需要改几个字,用什么改)=substitute(目前的文本,需要替换的文本,新的文本(替换后)5)换替新文替需的( 么用几需改开几想文前 =) 即 将只变隔 以法件条条条定需,设偶颜底填行奇现 =) (颜底填行果效0 ( (明公方色变了 ” & 码 =输格指接日格要例如需化成可内容用上格元指,用需以就数和“要实现会,项义上探入式的例是” 页第如称义加入入输在定法可即式要根
15、贴称插单击元:法页格”页&页页&码第中”引页 共 “填名: 共 “” 码 填位引” 第中“: “”0 ) 写置引”总写”名”总) ) ,( , . ( =填置引”“写名:页义作框义出定名/“单法定项义号引侧容括不后写所下础版0 方码插元) ) ,( ( , ( ) = “) ( :粗加字景格前,反和行止元) + ,( ) + ,( 式 反列相止单) (=( ) ) =式和( ( 亮) = =式格元车回,辑格前钮”确按,的应设式击然,的下侧并式选框件框对格条,令式“菜 列行所示高案纹印可打单项作投”转“性行单转成转么 径路定 : ),& ( :输超列,件 名件序,充换公拉新) 替 ( ( ) (
16、=入格一表任数数 =输一某任后)( . : 置 个定理名 打+ 或义-名-、 夹称或数内文该格 吧果看 选宏格 回 = ( 需 .开 = 0 . 即 *= 将 变 以 =件 定 偶 颜 =行 件: ,:定径 转性投项印高行列菜令对框并下击应,”辑车格= 亮 和) ()列式 ( ) )止,景粗 =) () 插 版所不侧义定“出义页写引 , )”写 )0“中 位码 :“ ”第页格法单称式法定加如第例入义会实和以用格内成如日接 方( 果填 , 公. = 格 下输 (= 回 宏 吧该内称-义 定 (后一 数任辑代 看) 签 作右,0 的开, 下件在=) !表文 要以理 列中 贴中 , .=开件 .出内
17、在行文 击) .更) .将出 入 输记, . 如.( 个内文 那据数 元隐 ( 0:( 0 ( ( 宏 元单,的适执0 作 码代粘复然码中在 + 表应对 ):( . ( ( _ 释. - 间时 宏. 行 宏宏表活 动动 改名,宏宏执薄作打 ( )_按 :代 工己会执个面下了以,代的是这“击称的录入你宏就止下作的你代步一看就像 个出这称右名确 出新,宏下点法)作做自是!,”建要习己:列)功这 0,版中0 页定片再 图制,内 ,域 方 置标 页( =( :)函 列号 ”.”将面位满开分号中时后的示 ,灵的己式本其) ( 成改,分含中果:) 式本位 式日” -“日月 0方简的成式期元的 回则 回,/值为测型数测函)式)( 0式 )公)式 ,式 )式 ,公 公0/0不单 0 函行自 行动自格要,& &换动示符回式$ $ $+ 用 +=用引绝和的格 引对引相元 相引格引=用$ $回换&格自行 单/ ) ) 式测数为, 元期方 日日 )果,成 )己, 示的分位”号 页标, 再页0这)建,作点出确出一的下入入录“是以下执己 ) 打执, 宏 .时 - (. : +码复 执的 0 : ( 元那个( 输5
限制150内