excel常用宏.pdf

《excel常用宏.pdf》由会员分享，可在线阅读，更多相关《excel常用宏.pdf（19页珍藏版）》请在三一文库上搜索。

1、实用文档 文案大全 1.拆分单元格赋值 Sub 拆分填充 () Dim x As Range For Each x In ActiveSheet.UsedRange.Cells If x.MergeCells Then x.Select x.UnMerge Selection.Value = x.Value End If Next x End Sub 2.Excel 宏 按列拆分多个excel Sub Macro1() Dim wb As Workbook, arr, rng As Range, d As Object, k, t, sh As Worksheet, i& Set rng =

2、Range(“A1:f1“) Application.ScreenUpdating = False Application.DisplayAlerts = False arr = Range(“a1:a“ & Range(“b“ & Cells.Rows.Count).End(xlUp).Row) Set d = CreateObject(“scripting.dictionary“) For i = 2 To UBound(arr) If Not d.Exists(arr(i, 1) Then Set d(arr(i, 1) = Cells(i, 1).Resize(1, 13) Else

3、Set d(arr(i, 1) = Union(d(arr(i, 1), Cells(i, 1).Resize(1, 13) End If Next k = d.Keys t = d.Items For i = 0 To d.Count - 1 Set wb = Workbooks.Add(xlWBATWorksheet) With wb.Sheets(1) rng.Copy .A1 t(i).Copy .A2 End With wb.SaveAs Filename:=ThisWorkbook.Path & “ & k(i) & “.xlsx“ wb.Close Next 实用文档 文案大全

4、Application.DisplayAlerts = True Application.ScreenUpdating = True MsgBox “ 完毕 “ End Sub 3.Excel 宏 按列拆分多个sheet 在一个工作表中是许多的公司订单记录，如何将它按公司名分拆成一个个工作表，用VBA 实现相当便捷。以下是演试： 原始工作簿： 运行 VBA代码后的工作簿： 代码如下： 1.需要先把数据按照分拆的那一列字段排序 2.如果你想应用在你的表格中，只需将所有resize(1,3)中的 3 修改，改成你的表格 的列数。如果你总表有8 列就改成resize(1,8)即可 3.如果你想根据表

5、格的第一列拆分，需要把Sheet1.Cells(i, 2) Sheet1.Cells(i - 1, 2)Then Worksheets.Add after:=Worksheets(Sheets.Count) Set sh = ActiveSheet sh.Name = Sheet1.Cells(i, 2) sh.Range(“a1“).Resize(1, 3) .Value = Sheet1.Range(“a1“).Resize(1, 3).Value sh.Range(“a65536“).End(3).Offset(1, 0). Resize(1, 3).Value = Sheet1.Cel

6、ls(i, 1).Resize(1, 3).Value Else sh.Range(“a65536“).End(3).Offset(1, 0). Resize(1, 3).Value = Sheet1.Cells(i, 1).Resize(1, 3).Value End If Next i Application.ScreenUpdating = True End Sub 实用文档 文案大全 4.Excel 宏 多工作表合并 Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(what:=

7、“*“, _ After:=sh.Range(“A1“), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub s() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow

8、 As Long Application.ScreenUpdating = False Application.EnableEvents = False 新建一个“汇总”工作表 Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets(“ 汇总 “).Delete On Error GoTo 0 Application.DisplayAlerts = True Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = “汇总 “ 开始复

9、制的行号，忽略表头，无表头请设置成1 StartRow = 2 For Each sh In ActiveWorkbook.Worksheets If sh.Name 0 And shLast = StartRow Then Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast) If Last + CopyRng.Rows.Count DestSh.Rows.Count Then MsgBox “ 内容太多放不下啦！“ GoTo ExitSub End If CopyRng.Copy With DestSh.Cells(Last +

10、 1, “A“) .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If End If Next ExitSub: Application.Goto DestSh.Cells(1) DestSh.Columns.AutoFit Application.ScreenUpdating = True Application.EnableEvents = True End Sub 实用文档 文案大全 5.多个 sheet拆成多个 excel Sub

11、Macro1() Dim sht As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each sht In Sheets sht.Copy ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & “ & sht.Name & “.xlsx“ ActiveWorkbook.Close Next Application.DisplayAlerts = True Application.ScreenUpdating = True E

12、nd Sub 或者 Private Sub 分拆工作表 () Dim sht As Worksheet Dim MyBook As Workbook Set MyBook = ActiveWorkbook For Each sht In MyBook.Sheets sht.Copy ActiveWorkbook.SaveAs Filename:=MyBook.Path & “ & sht.Name, FileFormat:=xlNormal 将工作簿另存为EXCEL 默认格式 ActiveWorkbook.Close Next MsgBox “文件已经被分拆完毕!“ End Sub 实用文档

13、文案大全 6.利用 txt 提取文件夹中的所有文件名称 1、 在那个文件夹内新建一个.TXT 文件 （如 wenjian.txt） ， 用记事本单开输入dir 1.txt 保 存退出将刚才的 .TXT （wenjian.txt） 更名为 .bat 文件 （wenjian.bat） 双击 wenjian.bat 文件运行一次，在文件夹内多出一个1.txt文件打开 1.txt文件，将其中的内容粘贴到 Excel 中，数据 分列处理就可以得到你要的文件名列表了！ 7.一列拆成两列 Excel 电子表格的功能非常强大，无论是 拆分 还是 合并单元格 都可以轻松完成。有时候 我们编辑数据的时候将“ 名称

14、 ” 和“ 价格 ” 全部放到了一个单元格中了，有什么方法可以快速将 这些数据拆分开呢？下面Word联盟以具体实例来为大家详细介绍操作方法。 Excel表格中的数据拆分 案例说明： 水果名称与水果价格全部在一个单元格中，只是用 “ 空格 ” 分隔开。 我们将这 些以空格分隔开的数据分别拆分到两个单元格中。 首先，我们在Excel 表格中选中需要拆分的列； 实用文档 文案大全 然后，单击菜单栏的“ 数据 ” ，在 下拉列表 中选择 “ 分列 ” 命令； 实用文档 文案大全 此时，需要3 个步骤来完成数据在表格中的拆分，“ 文本分列向导- 3 步骤之1” ， 我们只需选择默认的“ 分割符号 ” 再

15、单击下面的 “ 下一步 ” 按钮； 实用文档 文案大全 然后， 继续在 “ 文本分列向导- 3 步骤之2” 下面的 “ 分隔符号 ” 中勾选 “Tab 键 ” 、“ 空 格” 和“ 连续分隔符号视为单个处理” 。（现在我们可以在“ 数据预览 ” 中看到拆分的效果）最 后单击 “ 下一步 ” ； 最后一个步骤，我们单击“ 完成 ” 就可以了。 实用文档 文案大全 拆分好的表格效果如下图所示： 提示：以上的表格数据拆分工作必须要有一定的规律才可以实现，比如文字与数字之间 有空格或者逗号、 分号等其他任何符号或有规律的字符都可以完成拆分工作。如果中间没空 格或者有规律的字符，那么这项拆分数据表格的工

16、作就无法实现了。 实用文档 文案大全 8.根据颜色不同做IF 判断 如果有很多种颜色，可以插入一列公式 =颜色，将颜色值求出 实用文档 文案大全 在取值列用公式=IF(颜色 =6,1050,IF( 颜色 =0,L2) ，如果有多种颜色，就再加多层IF 实用文档 文案大全 9.一个工作薄中有许多工作表如何快速整理 出一个目录工作表 1、用宏 3.0 取出各工作表的名称，方法： Ctrl+F3 出现自定义名称对话框，取名为X，在 “ 引用位置 ” 框中输入： =MID(GET.WORKBOOK(1),FIND(“,GET.WORKBOOK(1)+1,100) 确定 2、用 HYPERLINK函数批

17、量插入连接，方法： 在目录工作表（一般为第一个sheet）的 A2 单元格输入公式： =HYPERLINK(“#“&INDEX(X,ROW()&“!A1“,INDEX(X,ROW() 将公式向下填充，直到出错为止，目录就生成了。 10.常用公式 计算有值的单元格的平均值 =AVERAGEIF(C8:T8,“1,“重复 “,“)。 2、 用出生年月来计算年龄公式：=TRUNC(DAYS360(H6,“2009/8/30“,FALSE)/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),“/“,MID(E2,11,2),“/“,MID

18、(E2,13,2)。 4、 从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,“男“,“ 女“),IF(MOD(MID(C2,17,1),2)=1,“男 “,“ 女“) 公式内的“ C2 ”代表的是输入身份证号码的单元格。 1、求和： =SUM(K2:K56) 对 K2 到 K56 这一区域进行求和； 实用文档 文案大全 2、平均数： =AVERAGE(K2:K56) 对 K2 K56 这一区域求平均数； 3、排名： =RANK(K2，K$2:K$56) 对 55 名学生的成绩进行排名； 4、等级： =I

19、F(K2=85,“优“,IF(K2=74,“良“,IF(K2=60,“及格 “,“ 不及格 “) 5、学期总评： =K2*0.3+M2*0.3+N2*0.4 假设K列、 M列和 N列分别存放着学生的“平 时总评”、“期中”、“期末”三项成绩； 6、最高分： =MAX(K2:K56) 求 K2到 K56 区域（ 55 名学生）的最高分； 7、最低分： =MIN(K2:K56) 求 K2到 K56 区域（ 55 名学生）的最低分； 8、分数段人数统计： （1） =COUNTIF(K2:K56,“100“) 求K2 到 K56 区域 100 分的人数；假设把结果存放于 K57单元格； （2） =CO

20、UNTIF(K2:K56,“=95“)K57 求 K2到 K56 区域 9599.5 分的人数；假设把 结果存放于K58 单元格； （3）=COUNTIF(K2:K56,“=90“) SUM(K57:K58) 求 K2到 K56区域 9094.5 分的人数； 假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,“=85“) SUM(K57:K59) 求 K2到 K56区域 8589.5 分的人数； 假设把结果存放于K60单元格； （5）=COUNTIF(K2:K56,“=70“) SUM(K57:K60) 求 K2到 K56区域 7084.5 分的人数； 假设把结果存放于K6

21、1单元格； （6）=COUNTIF(K2:K56,“=60“) SUM(K57:K61) 求 K2到 K56区域 6069.5 分的人数； 假设把结果存放于K62单元格； （7） =COUNTIF(K2:K56,“条件格式”，条件1 设为： 公式 =A1=1 2、点“格式”- “字体” - “颜色”，点击红色后点“确定”。 条件 2 设为： 公式 =AND(A10,A1“字体” - “颜色”，点击绿色后点“确定”。 实用文档 文案大全 条件 3 设为： 公式 =A1“字体” - “颜色”，点击黄色后点“确定”。 4、三个条件设定好后，点“确定”即出。 二、 EXCEL 中如何控制每列数据的长度

22、并避免重复录入 1、用数据有效性定义数据长度。 用鼠标选定你要输入的数据范围，点“数据 “-“ 有效性 “-“ 设置 “ ，“有效性条件 “ 设成 “允许 “ 文本长度 “ 等于 “5“ （具体条件可根据你的需要改变）。 还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点“ 确定 “ 。 2、用条件格式避免重复。 选定 A列，点“ 格式 “-“ 条件格式 “，将条件设成“公式 =COUNTIF($A:$A,$A1)1 ”，点“格式 “-“ 字体 “-“ 颜色 “，选定红色后点两次“ 确定 “。 这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。 三、

23、在 EXCEL 中如何把B列与 A列不同之处标识出来？ （一） 、如果是要求A、B两列的同一行数据相比较： 假定第一行为表头，单击A2单元格，点“格式”- “条件格式”，将条件设为: “单元格数值” “不等于” =B2 点“格式” - “字体” - “颜色”，选中红色，点两次“确定”。 用格式刷将A2 单元格的条件格式向下复制。 B列可参照此方法设置。 （二） 、如果是A列与 B列整体比较（即相同数据不在同一行）： 假定第一行为表头，单击A2单元格，点“格式”- “条件格式”，将条件设为: “公式” =COUNTIF($B:$B,$A2)=0 点“格式” - “字体” - “颜色”，选中红色，

24、点两次“确定”。 用格式刷将A2 单元格的条件格式向下复制。 B列可参照此方法设置。 按以上方法设置后，AB列均有的数据不着色，A列有 B列无或者B列有 A列无的数据标记为 红色字体。 四、 EXCEL 中怎样批量地处理按行排序 假定有大量的数据(数值 ) ，需要将每一行按从大到小排序，如何操作？ 由于按行排序与按列排序都是只能有一个主关键字, 主关键字相同时才能按次关键字排序。 所以，这一问题不能用排序来解决。解决方法如下： 1、假定你的数据在A至 E列，请在 F1 单元格输入公式： =LARGE($A1:$E1,COLUMN(A1) 实用文档 文案大全 用填充柄将公式向右向下复制到相应范围

25、。 你原有数据将按行从大到小排序出现在F 至 J 列。如有需要可用“选择性粘贴/ 数值”复制 到其他地方。 注：第 1 步的公式可根据你的实际情况（数据范围）作相应的修改。如果要从小到大排序, 公式改为 :=SMALL($A1:$E1,COLUMN(A1) 五、巧用函数组合进行多条件的计数统计 例：第一行为表头，A列是“姓名”，B列是“班级”，C列是“语文成绩”，D列是“录取 结果”，现在要统计“班级”为“二”，“语文成绩”大于等于104，“录取结果”为“重 本”的人数。统计结果存放在本工作表的其他列。 公式如下： =SUM(IF(B2:B9999=“ 二“)*(C2:C9999=104)*(

26、D2:D9999=“重本 “),1,0) 输入完公式后按Ctrl+Shift+Enter键, 让它自动加上数组公式符号“。 六、如何判断单元格里是否包含指定文本？ 假定对 A1单元格进行判断有无“指定文本 “, 以下任一公式均可: =IF(COUNTIF(A1,“*“&“指定文本 “&“*“)=1,“有“,“ 无“) =IF(ISERROR(FIND(“ 指定文本 “,A1,1),“无“,“ 有“) 求某一区域内不重复的数据个数 例如求 A1:A100 范围内不重复数据的个数，某个数重复多次出现只算一个。有两种计算方法： 一是利用数组公式： =SUM(1/COUNTIF(A1:A100,A1:A100) 输入完公式后按Ctrl+Shift+Enter键, 让它自动加上数组公式符号“。 二是利用乘积求和函数： =SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)