excelvba资料大全精华版范本模板.docx
- 文档编号:28008645
- 上传时间:2023-07-07
- 格式:DOCX
- 页数:155
- 大小:127.28KB
excelvba资料大全精华版范本模板.docx
《excelvba资料大全精华版范本模板.docx》由会员分享,可在线阅读,更多相关《excelvba资料大全精华版范本模板.docx(155页珍藏版)》请在冰豆网上搜索。
excelvba资料大全精华版范本模板
很重要的EXCEL使用技巧
一、求字符串中某字符出现的次数:
例:
求A1单元格中字符”a”出现的次数:
=LEN(A1)-LEN(SUBSTITUTE(A1,”a",""))
二、如何在不同工作薄之间复制宏:
1、打开含有宏的工作薄,点“工具/宏(M)…",选中你的宏,点“编辑”,这样就调出了VB编辑器界面。
2、点“文件/导出文件”,在“文件名”框中输入一个文件名(也可用默认的文件名),注意扩展名为“。
bas”,点“保存"。
3、将扩展名为“。
bas”的文件拷贝到另一台电脑,打开EXCEL,点“工具/宏/VB编辑器",调出VB编辑器界面,点“文件/导入文件”,找到你拷贝过来的文件,点“打开”,退出VB编辑器,你的宏已经复制过来了。
三、如何在EXCEL中设置单元格编辑权限(保护部分单元格)
1、先选定所有单元格,点”格式"->"单元格”—>”保护",取消"锁定”前面的”√".
2、再选定你要保护的单元格,点”格式"—〉”单元格”->”保护”,在”锁定”前面打上"√".
3、点”工具"—〉"保护"->"保护工作表",输入两次密码,点两次"确定”即可。
四、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色
比如:
A1〉1时,C1显示红色
0 A1<0时,C1显示黄色 方法如下: 1、单元击C1单元格,点“格式”〉“条件格式",条件1设为: 公式=A1=1 2、点“格式”—〉“字体”—>“颜色”,点击红色后点“确定”。 条件2设为: 公式=AND(A1〉0,A1〈1) 3、点“格式”—>“字体"->“颜色”,点击绿色后点“确定”。 条件3设为: 公式=A1<0 点“格式"—>“字体”->“颜色”,点击黄色后点“确定”. 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 选中“提供下拉前头”,点“确定”。 八、阿拉伯数字转换为大写金额(最新收集) 假定你要在B1输入阿拉佰数字,C1转换成中文大写金额(含元角分),请在C1单元格输入如下公式: =SUBSTITUTE(SUBSTITUTE(IF(-RMB(B1),IF(B1〉0,,”负”)&TEXT(INT(ABS(B1)+0。 5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(B1,2),2),"[dbnum2]0角0分;;整”),),"零角",IF(B1^2<1,,”零”)),"零分”,”整”) 九、EXCEL中怎样批量地处理按行排序 假定有大量的数据(数值),需要将每一行按从大到小排序,如何操作? 由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序.所以,这一问题不能用排序来解决。 解决方法如下: 1、假定你的数据在A至E列,请在F1单元格输入公式: =LARGE($A1: $E1,COLUMN(A1)) 用填充柄将公式向右向下复制到相应范围。 你原有数据将按行从大到小排序出现在F至J列.如有需要可用“选择性粘贴/数值"复制到其他地方。 注: 第1步的公式可根据你的实际情况(数据范围)作相应的修改。 如果要从小到大排序,公式改为: =SMALL($A1: $E1,COLUMN(A1)) 十、巧用函数组合进行多条件的计数统计 例: 第一行为表头,A列是“姓名”,B列是“班级”,C列是“语文成绩”,D列是“录取结果",现在要统计“班级”为“二",“语文成绩”大于等于104,“录取结果”为“重本”的人数。 统计结果存放在本工作表的其他列。 公式如下: =SUM(IF((B2: B9999="二")*(C2: C9999>=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$1000〉E2,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编辑器”->”插入"->”模块",输入如下内容: Subsheetcount() DimnumAsInteger num=ThisWorkbook.Sheets。 Count Sheets (1)。 Select Cells(1,1)=num EndSub 运行该宏,在第一个(排在最左边的)工作表的A1单元格中的数字就是sheet的个数。 方法二: 按Ctrl+F3(或者点”插入”—>”名称”—>”定义"),打开"定义名称”对话框 定义一个X "引用位置"输入: =get。 workbook(4) 点"确定”。 然后你在任意单元格输入=X 出来的结果就是sheet的个数。 二十二、一个工作薄中有许多工作表如何快速整理出一个目录工作表 1、用宏3.0取出各工作表的名称,方法: Ctrl+F3出现自定义名称对话框,取名为X,在“引用位置”框中输入: =MID(GET.WORKBOOK (1),FIND(”]",GET.WORKBOOK (1))+1,100) 确定 2、用HYPERLINK函数批量插入连接,方法: 在目录工作表(一般为第一个sheet)的A2单元格输入公式: =HYPERLINK(”#'"&INDEX(X,ROW())&”’! A1",INDEX(X,ROW())) 将公式向下填充,直到出错为止,目录就生成了. ExcelXP小技巧 一、工作簿操作技巧 1。 打开固定工作簿: 如果您有几个固定的工作簿需要同时打开,可以将它们保存为工作区。 以后只要将工作区打开,Excel就会打开其中的每个工作簿。 保存工作区的方法是: 打开需要同时使用的多个工作簿,按照使用要求调整好窗口大小和位置。 再单击Excel“文件”菜单下的“保存工作区"命令,输入工作区名称后单击“保存”按钮.打开工作区的方法是: 选择“打开”对话框中的“文件类型"为“工作区”,选中要打开的工作区名称后单击“打开”按钮。 注意: 每个工作簿所作的修改必须分别保存,保存工作区并不能保存其中的工作簿。 2。 打开多个工作簿: 如果您要打开的多个工作簿不固定,可以采用以下方法操作: 打开ExcelXP的“打开"对话框,按住Ctrl键逐个选中您要打开的工作簿,最后单击“打开”按钮,所选工作簿就会被全部打开。 3.Excel启动工作簿: 如果您的工作簿需要随ExcelXP启动而打开,可以按照以下方法操作: 在“资源管理器”中选中工作簿,单击工具栏中的“复制”按钮。 Windows2000/XP,就应该打开C: \Programfiles\Microsoftoffice\office\xlstart文件夹(其中C是Windows所在盘符)。 用鼠标右键单击右窗格中的空白处,选择快捷菜单中的“粘贴快捷方式”命令,将工作簿图标放入xlstart文件夹。 以后只要运行ExcelXP,工作簿就会被打开。 如果您要自动打开多个工作簿,只要将它们保存为工作区,然后将工作区图标放入xlstart文件夹即可。 4。 多工作簿打印: 如果要打印的工作簿较多,通常采用逐个打开的方法打印.如果这些工作簿位于一个文件夹内,可以采用下面的方法一次打印: 单击“文件”菜单中的“打开”命令,按住Ctrl键选中需要打印的所有文件。 然后单击“打开”对话框中的“工具”下拉按钮,单击其中的“打印”命令,就可以一次打印多个工作簿了。 5.复制工作簿样式: ExcelXP允许用户将一个工作簿使用的样式复制到其他工作簿,从而扩大样式的使用范围,其操作步骤是: 打开含有待复制样式的源工作簿和目标工作簿。 单击后者“格式”菜单中的“样式”命令打开对话框,再单击“合并"按钮打开“合并样式”对话框.选中“合并样式来源”对话框中的源工作簿并单击“确定”按钮,源工作簿包含的样式就会复制到目标工作簿。 6.快速关闭工作簿: 如果您打开了多个ExcelXP工作簿,如果希望不退出ExcelXP快速关闭它们,可以按住Shift键打开“文件”菜单,可以看到其中出现了“关闭所有文件”命令。 选择该命令以后,ExcelXP会根据打开文件的状态弹出相应的对话框,您只要根据需要选择合适的按钮即可。 7.自动保存工作簿: 单击“工具”菜单中的“加载宏”命令,选中对话框中的“自动保存”命令,单击“确定”按钮后“工具"菜单即可出现一条“自动保存”命令。 单击该命令可打开“自动保存”对话框,对“自动保存时间间隔”、“保存选项”等进行设置。 二、工作表操作技巧 1。 多工作表打印: 如果需要同时打印工作簿中的多张工作表,可以采用下面的方法操作: 首先选中要打印的多个工作表,然后单击“文件"菜单中的“打印”命令即可。 2。 多工作表输入: 如果您想在多张工作表中输入相同内容,省略以后的复制、粘贴等操作,可以采用以下方法: 选中需要填充相同数据的工作表,在已选中的任意一张工作表内输入数据,则所有选中工作表的相应单元格会填入同一数据。 如果选中工作表后您想取消上述功能,可用鼠标右键单击任意一张工作表的标签,选择快捷菜单中的“取消成组工作表”命令。 如果需要将某张工作表已有的数据快速填充到其他工作表,可采用以下方法: 按下Ctrl键选中含有数据的工作表和待填充数据的工作表,再选中含有数据的单元格区域,最后单击“编辑"、“填充”子菜单下的“至同组工作表”命令,在对话框中选择要填充的内容(“全部”、“内容”或“格式”)后单击“确定”即可。 3。 更改标签颜色: ExcelXP允许用户修改工作表标签的颜色,其操作方法是: 用鼠标右键单击工作表标签,选择快捷菜单中的“工作表标签颜色"命令。 打开“设置工作表标签”对话框,在调色板中选择一种颜色,然后单击“确定”按钮即可。 4。 快速切换工作表: 如果ExcelXP工作簿中含有很多工作表,如果您想迅速切换到另一个工作表,只需将鼠标指针移到工作薄左下角的滚动按钮处,然后单击鼠标右键,选择快捷菜单中的工作表标签名称即可。 5。 隐藏工作表行或列: 要快速隐藏活动单元格所在的行,可以按快捷键Ctrl+9(不是小键盘上的数字键,下同);要隐藏活动单元格所在列,可以按快捷键Ctrl+0。 6.工作表页码连续排列: ExcelXP打印多个工作表时,每个工作表的表格页码都是从1开始编号的。 如果您需要将多个工作表的表格页码连续排列,可以采用下面的技巧: 首先采用上面介绍的方法查看第一个工作表的总页数,接着切换到第二个工作表。 单击“文件”菜单下的“页面设置”命令,打开“页面设置→页眉/页脚"选项卡,再单击“自定义页眉”(或“自定义页脚")按钮打开对话框。 根据需要单击其中的“左”、“中"或“右”框,再单击带有“#”字样的按钮,即可将“&[页码]”放入框中.如果您要让第二个工作表的首页码接着第一个工作表的尾页码,只需在“&[页码]”后输入后者的总页数(即尾页码,例如11),使其变成“&[页码]+11”的样式。 如果需要,您还可以在“&[页码]+11”前后加上“第”和“页”字样,使其变成“第&[页码]+11页”的样式。 最后按照工作表的连续打印方法操作,您就可以得到页码连续排列的工作表了。 7.同步滚动数据: 如果您需要同时观察工作表中相距较远的两列数据,可以按以下方法操作: 将鼠标移到水平滚动条右端的拆分框上,变成双向光标后水平拖动,即可用竖线将当前工作表分割为左右两个窗格。 拖动其中的滚动条使需要观察的数据可见,此后即可按常规方法使两列数据同步滚动。 如果需要取消竖直分割线,只需用鼠标双击它就可以了。 三、选中操作技巧 1.快速选中特定单元格: 选中当前工作表中的任意一个单元格,单击“编辑”菜单下的“定位"命令。 在打开的“定位”对话框中单击“定位条件”按钮,根据您要选中的区域类型,在“定位条件”对话框中选择需要选中的单元格类型。 例如“常量",此时可以复选“数字”、“文本”等项目,单击“确定”按钮以后,符合条件的所有单元格均被选中。 2。 快速选中数据区域: 如果您想选中活动单元格所在的数据区域,可以单击“编辑”菜单中的“定位”命令打开对话框。 然后单击其中的“定位条件”按钮,打开“定位条件"对话框.选中“当前区域"复选框,“确定”后活动单元格所在的连续数据区域就会被选中.如果要选中某行或某列中含有数据的区域,可以选中该区域中的某个单元格。 然后按住Shift键,双击活动单元格在待选中方向上的边线即可。 3.快速选中特定区域: 按F5可以快速选中特定的单元格区域。 如果您要选中A2: B10区域,最为快捷的方法是按F5,打开“定位”对话框,在“引用”栏内输入“A2: B10”回车即可. 4.快速选中单元格、区域、行或列: 鼠标单击可以将当前单元格选中;单击待选中区域的第一个单元格,然后拖动鼠标至最后一个单元格,可以将两者之间的区域选中;单击工作表的全选按钮,可以将当前工作表选中;按住Ctrl单击或使用鼠标拖动,可以选中不相邻的多个单元格或区域;选中某区域的第一个单元格,按住Shift键单击区域对角的单元格,可以将两者之间的矩形区域选中;单击行号可将对应的整行选中;单击列标号可将对应的整列选中;沿行号或列表号拖动鼠标,或者先选中第一行或第一列,按住Shift键选中结束行或列,就可以选中相邻的多个行或列;先选中第一行或第一列,然后按住Ctrl键选中其他不相邻的行或列;如果您想增加或减少活动区域中的单元格,可以按住Shift键单击选中区域右下角的最后一个单元格,活动单元格和所单击的单元格之间的矩形区域就会成为新的选中区域. 5。 选中超链接单元格: ExcelXP单元格如果存放的是超链接,就不能采用直接单击的方法选中,否则会打开超级链接目标。 此时可以采用以下两种方法: 一是单击单元格以后按住鼠标左键,直到光标变为空心十字以后松开左键,就可选中这个含有超链接的单元格.二是先选中超链接单元格周围的某个单元格,用←、→、↑、↓将光标移到含有超链接的单元格,也可以将其选中。 四、单元格操作技巧 1。 合并矩形区域: ExcelXP允许将矩形区域内的任意单元格合并,具体操作方法是: 选中要合并的单元格,单击“格式”工具栏中的“合并及居中”按钮。 则所有选中的单元格被合并为一个,而其中的数据则被放到单元格中间。 注意: ExcelXP只把合并区域左上角的数据放入合并后的单元格。 为此,输入标题之类的文本不能断续分布在几个单元格中,否则合并过程中会截去一部分数据。 2.快速设置边框: Excel单元格的默认情况下没有边框,ExcelXP的“边框”工具经过改进,可以快速设置单元格的边框和线形、颜色和粗细等属性。 操作方法是: 鼠标右键单击ExcelXP工具栏,选中快捷菜单中的“边框”打开工具栏。 若按下其中的“绘图边框网格”按钮,用鼠标拖过某个单元格区域,可以快速应用其中显示的线条样式和颜色。 若按下工具栏中的“擦除边框"按钮,然后拖过某个单元格区域,可以快速擦除所有的单元格边框。 3。 快速命名: ExcelXP工作表单元格都有自己的默认名称,如第2列第6行单元格的名称为B6。 如果要给某个单元格命名为“工资总额”,只要将该单元格选中.然后单击编辑栏左边的“名称框”,即可输入该单元格的新名称。 如果您要为一个单元格区域命名,只要将需要命名的区域选中,然后按上面介绍的方法操作即可。 4。 固定活动单元格: 选中某一单元格后按住Ctrl键,用鼠标再次单击这个单元格,其中将会出现一个实线框。 输入数据以后敲回车,活动单元格不会发生移动,仍静止在原处。 这对于需要反复输入数据进行试算的场合非常有用. 5。 单元格快速定位: 如果您要在大型工作表中定位到特定单元格,可以采用以下两种方法: 按F5打开“定位”对话框,在“引用”栏中输入待定位的单元格地址,单击“确定”按钮即可。 也可以用鼠标单击编辑栏左侧的名称框,在其中输入待定位的单元格地址回车。 6.隐藏单元格数值: 如果您需要隐藏单元格中数值,可以选中包含待隐藏数值的单元格。 单击“格式"菜单中的“单元格”命令,打开“单元格格式→数字”选项卡,选择“分类"列表下的“自定义”选项,将“类型”框中已有的代码删除,输入“;;;”(三个分号)后“确定”即可。 此后,只要选中数值已被隐藏的单元格,就可以在编辑栏中看到被隐藏的数值。 7.单元格链接: 进行数据比较时,需要将多个工作表中的某
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excelvba 资料 大全 精华版 范本 模板
![提示](https://static.bdocx.com/images/bang_tan.gif)