excel常用宏资料

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 = Range(\

Application.ScreenUpdating = False Application.DisplayAlerts = False

arr = Range(\ Set d = CreateObject(\ 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(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 & \ wb.Close Next

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. 如果你想根据表格的第一列拆分，需要把Sheet1.Cells(i, 2) <> Sheet1.Cells(i - 1, 2)和

sh.Name = Sheet1.Cells(i, 2)的2换成1 Sub 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 = ActiveSheet

sh.Name = Sheet1.Cells(i, 2)

sh.Range(\Resize(1, 3).Value = Sheet1.Range(\Resize(1, 3).Value

sh.Range(\0).Resize(1, 3).Value = Sheet1.Cells(i, 1).Resize(1, 3).Value Else

sh.Range(\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:=\

After:=sh.Range(\ 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 As Long

Application.ScreenUpdating = False Application.EnableEvents = False

'新建一个“汇总”工作表

Application.DisplayAlerts = False On Error Resume Next

ActiveWorkbook.Worksheets(\汇总\ On Error GoTo 0

Application.DisplayAlerts = True

Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = \汇总\

'开始复制的行号，忽略表头，无表头请设置成1 StartRow = 2

For Each sh In ActiveWorkbook.Worksheets

If sh.Name <> DestSh.Name Then Last = LastRow(DestSh) shLast = LastRow(sh)

If shLast > 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 + 1, \ .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 Macro1()

Dim sht As Worksheet

Application.ScreenUpdating = False Application.DisplayAlerts = False For Each sht In Sheets sht.Copy

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & \ ActiveWorkbook.Close Next

Application.DisplayAlerts = True Application.ScreenUpdating = True End 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

6.利用txt提取文件夹中的所有文件名称

1、 在那个文件夹内新建一个.TXT文件（如wenjian.txt），用记事本单开输入 dir> 1.txt 保

存退出 将刚才的.TXT（wenjian.txt）更名为.bat文件（wenjian.bat） 双击wenjian.bat文件运行一次，在文件夹内多出一个1.txt文件 打开1.txt文件，将其中的内容粘贴到Excel中，数据——分列处理就可以得到你要的文件名列表了！

7.一列拆成两列

Excel电子表格的功能非常强大，无论是拆分还是合并单元格都可以轻松完成。有时候我们编辑数据的时候将“名称”和“价格”全部放到了一个单元格中了，有什么方法可以快速将这些数据拆分开呢？下面Word联盟以具体实例来为大家详细介绍操作方法。

Excel表格中的数据拆分

案例说明：水果名称与水果价格全部在一个单元格中，只是用“空格”分隔开。我们将这些以空格分隔开的数据分别拆分到两个单元格中。

①首先，我们在Excel表格中选中需要拆分的列；

②然后，单击菜单栏的“数据”，在下拉列表中选择“分列”命令；

③此时，需要3个步骤来完成数据在表格中的拆分，“文本分列向导 - 3 步骤之 1”，我们只需选择默认的“分割符号”再单击下面的“下一步”按钮；

④然后，继续在“文本分列向导 - 3 步骤之 2”下面的“分隔符号”中勾选“Tab 键”、“空格”和“连续分隔符号视为单个处理”。（现在我们可以在“数据预览”中看到拆分的效果）最后单击“下一步”；

⑤最后一个步骤，我们单击“完成”就可以了。

拆分好的表格效果如下图所示：

提示：以上的表格数据拆分工作必须要有一定的规律才可以实现，比如文字与数字之间有空格或者逗号、分号等其他任何符号或有规律的字符都可以完成拆分工作。如果中间没空格或者有规律的字符，那么这项拆分数据表格的工作就无法实现了。

8.根据颜色不同做IF判断

如果有很多种颜色，可以插入一列 公式=颜色，将颜色值求出

在取值列用公式=IF(颜色=6,1050,IF(颜色=0,L2))，如果有多种颜色，就再加多层IF

9.一个工作薄中有许多工作表如何快速整理出一个目录工作表

1、用宏3.0取出各工作表的名称，方法：

Ctrl+F3出现自定义名称对话框，取名为X，在“引用位置”框中输入： =MID(GET.WORKBOOK(1),FIND(\确定

2、用HYPERLINK函数批量插入连接，方法：

在目录工作表（一般为第一个sheet）的A2单元格输入公式： =HYPERLINK(\将公式向下填充，直到出错为止，目录就生成了。

10.常用公式

计算有值的单元格的平均值 =AVERAGEIF(C8:T8,\子列排序 =IF(A2=A1,C1+1,1) 数字转字母 =CHAR(A57+64)

字母转数字 =CODE(B58)-64 截取 =MID(B5,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({\6B\是\是\是\是\是\是\是\是\是\是\是\是\是\是\是\是\改日期格式： =TEXT(A4,\

=TEXT(B4,\

双重条件统计个数

=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,\。

3、 从输入的

4、 从输入的身份证号码内让系统自动提取性别，可以输入以下公式：

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,\男\女\男\女\公式内的“C2”代表的是输入身份证号码的单元格。

1、求和： =SUM(K2:K56) ——对K2到K56这一区域进行求和；

18

位身份证号的出生年月计算公式：

=CONCATENATE(MID(E2,7,4),\。

2、平均数： =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数；

3、排名： =RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名；

4、等级： =IF(K2>=85,\优\良\及格\不及格\

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,\——求K2到K56区域100分的人数；假设把结果存放于K57单元格；

（2） =COUNTIF(K2:K56,\－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；

（3）=COUNTIF(K2:K56,\－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格；

（4）=COUNTIF(K2:K56,\－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；

（5）=COUNTIF(K2:K56,\－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；

（6）=COUNTIF(K2:K56,\－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；

（7） =COUNTIF(K2:K56,\——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；

说明：COUNTIF函数也可计算某一区域男、女生人数。

如：=COUNTIF(C2:C351,\男\——求C2到C351区域（共350人）男性人数；

9、优秀率： =SUM(K57:K60)/55*100

10、及格率： =SUM(K57:K62)/55*100

11、标准差： =STDEV(K2:K56) ——求K2到K56区域(55人)的成绩波动情况（数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化）；

12、条件求和： =SUMIF(B2:B56,\男\，K2:K56) ——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；

13、多条件求和： ｛=SUM(IF(C3:C322=\男\｝ ——假设C列（C3:C322区域）存放学生的性别，G列（G3:G322区域）存放学生所在班级代码（1、2、3、4、5），则此函数返回的结果表示求一班的男生人数；这是一个数组函数，输完后要按Ctrl＋Shift＋Enter组合键(产生“｛??｝”)。“｛｝”不能手工输入，只能用组合键产生。

14、根据出生日期自动计算周岁：=TRUNC((DAYS360(D3,NOW( )))/360,0)

———假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。

15、在Word中三个小窍门：

①连续输入三个“~”可得一条波浪线。

②连续输入三个“-”可得一条直线。

连续输入三个“=”可得一条双直线。

一、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如： A1〉1时，C1显示红色 0

1、单元击C1单元格，点“格式”>“条件格式”，条件1设为： 公式 =A1=1

2、点“格式”->“字体”->“颜色”，点击红色后点“确定”。 条件2设为： 公式 =AND(A1>0,A1<1)

3、点“格式”->“字体”->“颜色”，点击绿色后点“确定”。

条件3设为： 公式 =A1<0

点“格式”->“字体”->“颜色”，点击黄色后点“确定”。 4、三个条件设定好后，点“确定”即出。

二、EXCEL中如何控制每列数据的长度并避免重复录入

1、用数据有效性定义数据长度。

用鼠标选定你要输入的数据范围，点\数据\有效性\设置\，\有效性条件\设成\允许\文本长度\等于\（具体条件可根据你的需要改变）。

还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点\确定\。 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中怎样批量地处理按行排序

假定有大量的数据(数值)，需要将每一行按从大到小排序，如何操作？

由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以，这一问题不能用排序来解决。解决方法如下： 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=\二\重本\输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号\。

六、如何判断单元格里是否包含指定文本？

假定对A1单元格进行判断有无\指定文本\以下任一公式均可: =IF(COUNTIF(A1,\指定文本\有\无\=IF(ISERROR(FIND(\指定文本\无\有\

求某一区域内不重复的数据个数

例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只算一个。有两种计算方法： 一是利用数组公式：

=SUM(1/COUNTIF(A1:A100,A1:A100))

输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号\。 二是利用乘积求和函数：

=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))