EXCEL字符替代函数

1、EXCEL字符替代函数集团标准化工作小组 #Q8QGGQT-GX8G08Q8-GNQGJ8-MHHGN#Excel字符替代函数1. Excel字符替代函数replace（目前的文本，想从第几个数字开始改，共需要改几个字，用什么改） =substitute（目前的文本，需要替换的文本，新的文本（替换后）2. Excel公式相对引用和绝对引用相对引用:a1 =a2+a3绝对引用:a1 =a$2+$a$3绝对引用：a1 =$a2+$a$33. Excel公式插入回车符并显示出来自动换行在公式中加”&CHAR（10）&”，同时要将单元格设置为自动换行。4. Excel自动换行的函数Ch

2、ar（ 10）Ap5. Excel单元格不显示0和错误#/0的公式=IF（or（公式=0,ISERROR（公式公式）=IF（or（公式=0,ISERROR（公式公式）=IF（or（公式=0,ISERROR（公式公式）ISNA函数用于测试数值类型，如果测试值为错误值#N/A,贝IJ返回TRUE,否则返回FALSE。6. Excel的单元格日期格式转换成文本格式的简单方法如“2008 年 7 月 18 日”或“2008-7-18公式“=text（A2,”yyyymmdd"）"（注:如果日期中还包含时、分、秒，则公式可改成 “=text（A2,"yyyymmddhhmm

3、ss"）”。其中的文本格式可以按自己的要求灵活运用，如 “yymmdd h:m:s”表示只要年份的后两位、时间中用冒号将时分秒隔开并且不满两位时 前面不加“0二7. Excel返回行号、列号函数返回行号：=row（）返回列号：=column（）8.Excel页脚 底端标题行设置方法选择页脚区域，按住Shift键,编辑一复制图片，再把图片插入到自定义页脚里(2003版可以，2000没有这个功能)。9. Excel如何录制宏执行宏简单的方法：自己可练习创建一个宏”，(就是录下自己需要做的操作步骤)方法：点击一工具一宏一录制新宏一弹出窗口一确认宏名称一这时就会弹 出一个录像框，你就可以一步

4、一步点击你要做的操作，按下“停止”，就结束了宏录制！ 你再进入工具一宏一选择刚录下的宏名称一点击“编辑”一这里面就是你要的宏 代码了，自己可以研究了！如下面就是一个宏，执行后会自己打开：工作表3代码是：Sub按钮1_单击()Sheet (Sheet3)End Sub10. Excel打开工作薄自动执行宏先录制宏，再宏名字改成sub auto_open就变成自品了11. Excel击活工作表自动执行宏Sub Macro适合行高()宏名1 ZYC录制时间：2014-9-23注释行Private Sub worksheet_activate()Cells(1, 1) = Now()RowsC'

5、;3:3").SelectRows(3:3").End Sub先打开对应工作表，操作alt+Fll,在视图中选正代码窗口，然后复制粘贴上面代码。12. Excel击活工作表6-10行自动执行最适合的行高，然后选正单元格A6的宏Private Sub worksheet_activate()Rows(n6:10").SelectRows("6:10").Range(nA6H).SelectEnd Sub13. Excel隐藏单元格中的数据有时候，我们可能不希望别人看到Excel某些单元格或者区域中的数据内容，可以 选定这些单元格内容，然后按下“C

6、trl+1”组合键，在弹出的“单元格格式”对话框 中,单击“数字"选项卡，然后选择“自定义”分类，在其中的“类型”文本框中输入 半角符号“；”即可，这样即便是修改字体颜色也无法显示隐藏的内容了。14. Excel统计空白单元格、数字单元格、数字及非空单元格个数=countBlank(A1 :A100)'统计空白单元格数量=count(A1 :A100)'统计数字单元格个数=counta(A1 :A100)'统计数字及非空单元格个数=countif(A1:A100,A1)'条件统计统计区域中A1同值的个数15. Excel提取文件夹中的所有文件名称1、在

7、那个文件夹内新建一个.TXT文件(如),用记事本单开输入dir>保存退出将刚才的.TXT ()更名为.bat文件()双击文件运行一次，在文件夹内多出一个文件打开文件，将其中的内容粘贴到Excel中，数据一分列处理就可以得到你要的文件名 列表了 ！2、VBA (2003 版)在那个文件夹下新建Excel文件，打开新建的Excel文件，右击工作表标签(如Sheetl),查看代码一在代码编辑器中输入以下代码Sub Test()Dim i As IntegerDim strPath As StringstrPath =With.Lookln = strPath.SearchSubFolders

8、= True.Filename = "*.*HIf .Execute > 0 ThenFor i = 1 To .Range("A" & i) = .FoundFiles(i)Next iEnd IfEnd WithEnd Sub回到Excel表格中，工具一宏一宏一选择一执行看看效果吧！16. Excel单元格中显示该文件夹内的文件数量或名称假设这个文件夹是D:fda1、插入H名称H定义,或按CTRL+F3,打开excel一名称管理器一定义一个名称一比如：aaa引用位置输入：=FILES("d:fda*.*") &T(NO

9、W()2、然后在任一工作表某一单元格输入：=COUNTA(aaa)则返回文件个数。3、如果在任一工作表某一单元格输入：=if(row(a1 )>counta(aaa),"",index(aaa,row(a1)4、再下拉公式进行填充，则依序填充文件名。17. Excel一列中输入文件名，另一列自动生成指定文件夹同名文件超链接A列为输入文件名，B列返回超链接B1 中输入：=HYPERLINK(,D:FDA,'&A1 ,A1)其中” d:fda”为指定文件夹路径。18. Excel中怎么把行转换成列复制需转换的单元格行，选择性粘勾选“转置二在页面投置工作表选

10、项中勾选单色打印，可以不打印底纹图案。19. Excel高亮显示光标所在行或列在Excel中选择菜单“格式条件格式”命令，弹出“条件格式”对话框。在“条件”下拉框中选择“公式”并在右侧中输入下面相应的公式，然后点击“格式”按钮，设置相应的格式，完成后，按“确定”按钮。不过当前单元格编辑过才行，按过回车。高亮单元格：条件格式：=CELL("address")=ADDRESS(ROW(),COLUMN()条件格式：=ROW()=CELL("row")或者：=CELL("row")=ROW()局另行和列条件格式:=(row()=cen(&q

11、uot;row")+(column()=cell("col")高亮当前单元格为止的相应行和列，呈反L形条件格式：=OR(AND(CELL("row")=ROW()5CELL(',cor')+l>COLUMN(),AND(CELL(”col")=COLUMN(),CELL("row'')+l>ROW()高亮当前单元格为止的相应行和列，呈反L形，且当前单元格背景色改变、字体加粗：条件格式：=CELL("addiess")=ADDRESS(ROW(),COLUMN()

12、,H4=OR(AND(CELL("row")=ROW(),CELL("cor')+l>COLUMN(),AND(CELL("cor')=COLUMN(),CELL(',row,' )+l>ROW()20. Excel单元格中插入页码方法(以Excel 2003版本为基础，下文中所有“填写”后跟的内容不包括内容最外侧的全角引 号)一、定义引用项(以下定义方法通过单击菜单“插入/名称/定义”弹出定义对话框操作)1、定义“页码”：“名称”中填写“页码”，呵用位置”中填写U=IF(ISERROR(MATCH(ROW()

13、,(64), 1), 1 ,MATCH(ROW(),(64), 1)+1)”2、定义“总页数”：“名称”中填写“总页数”，“引用位置”中填写“=(50)+RAND()*0”。3、定义“第N页”:“名称”中填写“第N页”，“引用位置”中填写"第"&页码&“页4、定义"第N页共页"："：“名称”中填写“第N页共页”，“引用位置”中填写“=”第”&页码&“页共"&总页数&”页心。二、单元格插入页码1、插入法：指定单元格，单击菜单“插入/名称/粘贴”，根据需要的格式选择即可。2、输入法：指定单元格，在上部输入区输入等号加上面定义的名称，如“=第N页共 页”就是本例提问的格式。三、深入探讨通过分析上述“定义引用项”内容，您会发现，其实只需要定义“页码”和“总页数”就可以 To需要使用的时候，可直接指定单元格，输入上述“引用内容”的内容就可达成格式化 页码的需要。如本例提问要求的格式化日期，可直接指定单元格后输入“="第"&页码&am