excel常用宏.pdf

《excel常用宏.pdf》由会员分享，可在线阅读，更多相关《excel常用宏.pdf（19页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、1. 1.拆拆分单元格赋值分单元格赋值Sub 拆分填充()Dim x As RangeFor Each x In ActiveSheet.UsedRange.CellsIf x.MergeCells Thenx.Selectx.UnMergeSelection.Value = x.ValueEnd IfNext xEnd Sub2. 2.E Excelxcel 宏宏 按列拆分多个按列拆分多个 excelexcelSub Macro1()Dim wb As Workbook, arr, rng As Range, d As Object, k, t, sh As Worksheet, i&Set

2、 rng = Range(A1:f1)Application.ScreenUpdating = FalseApplication.DisplayAlerts = Falsearr = 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 Set d(

3、arr(i, 1) = Union(d(arr(i, 1), Cells(i, 1).Resize(1, 13) End IfNextk = d.Keyst = d.ItemsFor 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.CloseNextApplication.DisplayAlerts

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

5、et1.Cells(i, 2) Sheet1.Cells(i - 1, 2)和sh.Name = Sheet1.Cells(i, 2)的 2 换成 1Sub s() Application.ScreenUpdating = False Dim sh As Worksheet, i As Integer For i = 2 To Sheet1.a65536.End(3).Row If Sheet1.Cells(i, 2) Sheet1.Cells(i - 1, 2) Then Worksheets.Add after:=Worksheets(Sheets.Count) Set sh = Acti

6、veSheetsh.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.Cells(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

7、 End If Next iApplication.ScreenUpdating = TrueEnd Sub4.Excel4.Excel 宏宏 多工作表合并多工作表合并Function LastRow(sh As Worksheet)On Error Resume NextLastRow = sh.Cells.Find(what:=*, _After:=sh.Range(A1), _Lookat:=xlPart, _LookIn:=xlFormulas, _SearchOrder:=xlByRows, _SearchDirection:=xlPrevious, _MatchCase:=Fals

8、e).RowOn Error GoTo 0End FunctionSub s()Dim sh As WorksheetDim DestSh As WorksheetDim Last As LongDim shLast As LongDim CopyRng As RangeDim StartRow As LongApplication.ScreenUpdating = FalseApplication.EnableEvents = False新建一个“汇总”工作表Application.DisplayAlerts = FalseOn Error Resume NextActiveWorkbook

9、.Worksheets( 汇总).DeleteOn Error GoTo 0Application.DisplayAlerts = TrueSet DestSh = ActiveWorkbook.Worksheets.AddDestSh.Name = 汇总开始复制的行号，忽略表头，无表头请设置成1StartRow = 2For Each sh In ActiveWorkbook.WorksheetsIf sh.Name DestSh.Name ThenLast = LastRow(DestSh)shLast = LastRow(sh)If shLast 0 And shLast = Start

10、Row ThenSet CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast)If Last + CopyRng.Rows.Count DestSh.Rows.Count ThenMsgBox 内容太多放不下啦！GoTo ExitSubEnd IfCopyRng.CopyWith DestSh.Cells(Last + 1, A).PasteSpecial xlPasteValues.PasteSpecial xlPasteFormatsApplication.CutCopyMode = FalseEnd WithEnd IfEnd IfNe

11、xtExitSub:Application.Goto DestSh.Cells(1)DestSh.Columns.AutoFitApplication.ScreenUpdating = TrueApplication.EnableEvents = TrueEnd Sub5. 5.多个多个 sheetsheet 拆成多个拆成多个 excelexcelSub Macro1() Dim sht As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each sht In Sheets

12、 sht.Copy ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & & sht.Name & .xlsx ActiveWorkbook.Close Next Application.DisplayAlerts = True Application.ScreenUpdating = TrueEnd Sub或者 Private Sub 分拆工作表() Dim sht As Worksheet Dim MyBook As Workbook Set MyBook = ActiveWorkbook For Each sht In MyBook.Sh

13、eets sht.Copy ActiveWorkbook.SaveAs Filename:=MyBook.Path & & sht.Name,FileFormat:=xlNormal将工作簿另存为 EXCEL 默认格式 ActiveWorkbook.Close Next MsgBox 文件已经被分拆完毕! End Sub6. 6.利用利用 txttxt 提取文件夹中的所有文件名称提取文件夹中的所有文件名称1、 在那个文件夹内新建一个.TXT 文件如 wenjian.txt ，用记事本单开输入 dir 1.txt 保存退出 将刚刚的.TXTwenjian.txt更名为.bat 文件wenjian

14、.bat 双击 wenjian.bat文件运行一次，在文件夹内多出一个 1.txt 文件 打开 1.txt 文件，将其中的内容粘贴到Excel 中，数据分列处理就可以得到你要的文件名列表了！7. 7.一列拆成两列一列拆成两列Excel 电子表格的功能非常强大， 无论是拆分还是合并单元格都可以轻松完成。有时候我们编辑数据的时候将“名称”和“价格”全部放到了一个单元格中了， 有什么方法可以快速将这些数据拆分开呢？下面Word 联盟以具体实例来为大家详细介绍操作方法。ExcelExcel 表格中的数据拆分表格中的数据拆分案例说明：水果名称与水果价格全部在一个单元格中， 只是用“空格”分隔开。我们将这

15、些以空格分隔开的数据分别拆分到两个单元格中。首先，我们在 Excel 表格中选中需要拆分的列；然后，单击菜单栏的“数据”，在下拉列表中选择“分列”命令；此时，需要 3 个步骤来完成数据在表格中的拆分，“文本分列向导 - 3 步骤之 1”，我们只需选择默认的“分割符号”再单击下面的“下一步”按钮；然后， 继续在“文本分列向导 - 3 步骤之 2”下面的“分隔符号”中勾选“Tab 键”、 “空格”和“连续分隔符号视为单个处理”。现在我们可以在“数据预览”中看到拆分的效果最后单击“下一步”；最后一个步骤，我们单击“完成”就可以了。拆分好的表格效果如以下图所示：提示： 以上的表格数据拆分工作必须要有一

16、定的规律才可以实现， 比方文字与数字之间有空格或者逗号、 分号等其他任何符号或有规律的字符都可以完成拆分工作。 如果中间没空格或者有规律的字符，那么这项拆分数据表格的工作就无法实现了。8. 8.根据颜色不同做根据颜色不同做 IFIF 判断判断如果有很多种颜色，可以插入一列 公式=颜色，将颜色值求出在取值列用公式=IF(颜色=6,1050,IF(颜色=0,L2)，如果有多种颜色，就再加多层IF9. 9.一个工作薄中有许多工作表如何快速整理一个工作薄中有许多工作表如何快速整理出一个目录工作表出一个目录工作表1、用宏 3.0 取出各工作表的名称，方法：Ctrl+F3 出现自定义名称对话框，取名为X，

17、在“引用位置”框中输入：=MID(GET.WORKBOOK(1),FIND(,GET.WORKBOOK(1)+1,100)确定2、用 HYPERLINK函数批量插入连接，方法：在目录工作表一般为第一个sheet的 A2 单元格输入公式：=HYPERLINK(#&INDEX(X,ROW()&!A1,INDEX(X,ROW()将公式向下填充，直到出错为止，目录就生成了。10.10.常用公式常用公式计算有值的单元格的平均值 =AVERAGEIF(C8:T8,0)子列排序 =IF(A2=A1,C1+1,1)数字转字母 =CHAR(A57+64)字母转数字 =CODE(B58)-64截取 =MID(B5

18、,3,99)分类 =IF(A2=A1,B1,B1+1)查找 =VLOOKUP(C:C,5.2-大中型建筑业打分卡问题清单!B:D,3,0)数数 =COUNTIF(I:I,)A 列相同的 F 列值和=SUMIF(A:A,A2,F:F)倒序 =INDEX($A$1:$A$119,ROWS($A$1:$A$119)-ROWS($A$1:A1)+1)两个相同再取值 =VLOOKUP(A2&$B$1,Sheet4!$B:$J,9,0)改名=CHOOSE(MATCH(,0/FIND(1A,1B,2A,2B,3A,3B,4A,4B,5A,5B,6A,6B,7A,7B,8A,8B,N358),),是,是,是,

19、是,是,是,是,是,是,是,是,是,是,是,是,是)改日期格式： =TEXT(A4,yyyy/m/dd)=TEXT(B4,HH:MM)双重条件统计个数=SUMPRODUCT($B$3:$AK$3=AM$3)*($B4:$AK4=)=COUNTIFS($B$3:$AK$3,AM$3,$B4:$AK4,)1、 查找重复内容公式：=IF(COUNTIF(A:A,A2)1,重复,)。2、 用出生年月来计算年龄公式：=TRUNC(DAYS360(H6,2009/8/30,FALSE)/360,0)。3 、从 输 入 的4、 从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)

20、=15,IF(MOD(MID(C2,15,1),2)=1,男,女),IF(MOD(MID(C2,17,1),2)=1,男,女)公式内的“C2”代表的是输入身份证号码的单元格。1、求和： =SUM(K2:K56) 对 K2 到 K56 这一区域进行求和；18位 身 份 证 号 的 出 生 年 月 计 算 公 式 ：=CONCATENATE(MID(E2,7,4),/,MID(E2,11,2),/,MID(E2,13,2)。2、平均数： =AVERAGE(K2:K56) 对 K2 K56 这一区域求平均数；3、排名： =RANK(K2，K$2:K$56) 对 55 名学生的成绩进行排名；4、等级：

21、 =IF(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 =COUNTIF(K2:K56,=95)K57

22、求 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 分的人数；假设把结果存放于 K61 单元格；6=COUNTIF(K2:K56,=60)SUM(K

23、57:K61) 求 K2 到 K56 区域 6069.5 分的人数；假设把结果存放于 K62 单元格；7 =COUNTIF(K2:K56,60) 求 K2 到 K56 区域 60 分以下的人数；假设把结果存放于 K63 单元格；说明：COUNTIF 函数也可计算某一区域男、女生人数。如：=COUNTIF(C2:C351,男) 求 C2 到 C351 区域共 350 人男性人数；9、优秀率： =SUM(K57:K60)/55*10010、及格率： =SUM(K57:K62)/55*10011、标准差： =STDEV(K2:K56) 求 K2 到 K56 区域(55 人)的成绩波动情况数值越小，说

24、明该班学生间的成绩差异较小，反之，说明该班存在两极分化 ；12、条件求和： =SUMIF(B2:B56,男，K2:K56) 假设 B 列存放学生的性别，K 列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；13、 多条件求和： =SUM(IF(C3:C322=男,IF(G3:G322=1,1,0)假设 C 列 C3:C322区域存放学生的性别，G 列G3:G322 区域存放学生所在班级代码1、2、3、4、5 ，则此函数返回的结果表示求一班的男生人数；这是一个数组函数，输完后要按CtrlShiftEnter 组合键(产生“”)。“”不能手工输入，只能用组合键产生。14、根据出生日期自

25、动计算周岁：=TRUNC(DAYS360(D3,NOW( )/360,0)假设 D 列存放学生的出生日期，E 列输入该函数后则产生该生的周岁。15、在 Word 中三个小窍门：连续输入三个“”可得一条波浪线。连续输入三个“-”可得一条直线。连续输入三个“=”可得一条双直线。一、excel 中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比方：A11 时，C1 显示红色0A11 时，C1 显示绿色A1“条件格式”，条件1 设为：公式 =A1=12、点“格式”-“字体”-“颜色”，点击红色后点“确定”。条件 2 设为：公式 =AND(A10,A1“字体”-“颜色”，点击绿色后点“确定”。

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

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

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

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

30、rl+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)