excel常用宏

文案大全.拆分单元格赋值Sub拆分填充()DimxAsRangeForEachxInActiveSheet.UsedRange.CellsIfx.MergeCellsThenx.Selectx.UnMergeSelection.Value=x.ValueEndIfNextxEndSub.Excel宏按列拆分多个excelSubMacro1()DimwbAsWorkbook,arr,rngAsRange,dAsObject,k,t,shAsWorksheet,i&Setrng=Range("A1:f1")Application.ScreenUpdating=FalseApplication.DisplayAlerts=Falsearr=Range("a1:a"&Range("b"&Cells.Rows.Count).End(xlUp).Row)Setd=CreateObject("scripting.dictionary")Fori=2ToUBound(arr)IfNotd.Exists(arr(i,1))ThenSetd(arr(i,1))=Cells(i,1).Resize(1,13)ElseSetd(arr(i,1))=Union(d(arr(i,1)),Cells(i,1).Resize(1,13))EndIfNextk=d.Keyst=d.ItemsFori=0Tod.Count-1Setwb=Workbooks.Add(xlWBATWorksheet)Withwb.Sheets(1)rng.Copy.[A1]t(i).Copy.[A2]EndWithwb.SaveAsFilename:=ThisWorkbook.Path&"\"&k(i)&".xlsx"wb.CloseNextApplication.DisplayAlerts=TrueApplication.ScreenUpdating=TrueMsgBox”完毕”EndSub.Excel宏按列拆分多个sheet在一个工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中是许多的公司订单记录，如何将它按公司名分拆成一个个工作表，用VBA实现相当便捷。以下是演试：原始工作簿：ABCD.E-1公司编号公司名订单号14=E121XX百货6545-3E121双百货3524E121XX百货,2弘5]5E122：XX副食店46126E122-XX副食店234.47E123'YY6278E123-TI3456*E124;ZZ275410E125AA"256211E125146321:2',■：维图团=好自选留老曹，\、口。与阐闻。闻运行VBA代码后的工作簿:代码如下：需要先把数据按照分拆的那一列字段排序如果你想应用在你的 表格 关于规范使用各类表格的通知入职表格免费下载关于主播时间做一个表格详细英语字母大小写表格下载简历表格模板下载 中，只需将所有resize(1,3)中的3修改，改成你的表格的列数。如果你总表有8列就改成resize(1,8)即可如果你想根据表格的第一列拆分，需要把Sheet1.Cells(i,2)<>Sheet1.Cells(i-1,2)和sh.Name=Sheet1.Cells(i,2)的2换成1Subs()Application.ScreenUpdating=FalseDimshAsWorksheet,iAsIntegerFori=2ToSheet1.[a65536].End(3).RowIfSheet1.Cells(i,2)<>Sheet1.Cells(i-1,2)ThenWorksheets.Addafter:=Worksheets(Sheets.Count)Setsh=ActiveSheetsh.Name=Sheet1.Cells(i,2)sh.Range("a1").Resize(1,3).Value=Sheet1.Range("a1").Resize(1,3).Valuesh.Range("a65536").End(3).Offset(1,0).Resize(1,).Value=Sheet1.Cells(i,1).Resize(1,3).ValueElsesh.Range("a65536").End(3).Offset(1,0).Resize(1,).Value=Sheet1.Cells(i,1).Resize(1,3).ValueEndIfNextiApplication.ScreenUpdating=TrueEndSub.Excel宏多工作表合并FunctionLastRow(shAsWorksheet)OnErrorResumeNextLastRow=sh.Cells.Find(what:="*After:=sh.Range("A1"),_Lookat:=xlPart,_LookIn:=xlFormulas,_SearchOrder:=xlByRows,_SearchDirection:=xlPrevious,MatchCase:=False).RowOnErrorGoTo0EndFunctionSubs()DimshAsWorksheetDimDestShAsWorksheetDimLastAsLongDimshLastAsLongDimCopyRngAsRangeDimStartRowAsLongApplication.ScreenUpdating=FalseApplication.EnableEvents=False'新建一个“汇总”工作表Application.DisplayAlerts=FalseOnErrorResumeNextActiveWorkbook.Worksheets("汇总").DeleteOnErrorGoTo0Application.DisplayAlerts=TrueSetDestSh=ActiveWorkbook.Worksheets.AddDestSh.Name="汇总"'开始复制的行号，忽略表头，无表头请设置成1StartRow=2ForEachshInActiveWorkbook.WorksheetsIfsh.Name<>DestSh.NameThenLast=LastRow(DestSh)shLast=LastRow(sh)IfshLast>0AndshLast>=StartRowThenSetCopyRng=sh.Range(sh.Rows(StartRow),sh.Rows(shLast))IfLast+CopyRng.Rows.Count>DestSh.Rows.CountThenMsgBox"内容太多放不下啦！"GoToExitSubEndIfCopyRng.CopyWithDestSh.Cells(Last+1,"A").PasteSpecialxlPasteValues.PasteSpecialxlPasteFormatsApplication.CutCopyMode=FalseEndWithEndIfEndIfNextExitSub:Application.GotoDestSh.Cells(1)DestSh.Columns.AutoFitApplication.ScreenUpdating=TrueApplication.EnableEvents=TrueEndSub.多个sheet拆成多个excelSubMacro1()DimshtAsWorksheetApplication.ScreenUpdating=FalseApplication.DisplayAlerts=FalseForEachshtInSheetssht.CopyActiveWorkbook.SaveAsFilename:=ThisWorkbook.Path&"\"&sht.Name&".xlsx"ActiveWorkbook.CloseNextApplication.DisplayAlerts=TrueApplication.ScreenUpdating=TrueEndSub或者PrivateSub分拆工作表()DimshtAsWorksheetDimMyBookAsWorkbookSetMyBook=ActiveWorkbookForEachshtInMyBook.Sheetssht.CopyActiveWorkbook.SaveAsFilename:=MyBook.Path&"\"&sht.Name,FileFormat:=xlNormal'将工作簿另存为EXCEL默认格式ActiveWorkbook.CloseNextMsgBox"文件已经被分拆完毕!"EndSub.利用txt提取文件夹中的所有文件名称1、在那个文件夹内新建一个.TXT文件(如wenjian.txt),用记事本单开输入dir>1.txt保存退出将刚才的.TXT(wenjian.txt)更名为.bat文件(wenjian.bat)双击wenjian.bat文件运行一次，在文件夹内多出一个1.txt文件打开1.txt文件，将其中的内容粘贴到Excel中，数据——分列处理就可以得到你要的文件名列表了！.一列拆成两列Excel电子表格的功能非常强大，无论是拆分还是合并单元格都可以轻松完成。有时候我们编辑数据的时候将“名称”和“价格”全部放到了一个单元格中了，有什么方法可以快速将这些数据拆分开呢？下面Word联盟以具体实例来为大家详细介绍操作方法。Excel表格中的数据拆分案例说明：水果名称与水果价格全部在一个单元格中，只是用“空格”分隔开。我们将这些以空格分隔开的数据分别拆分到两个单元格中。①首先，我们在Excel表格中选中需要拆分的列；Ln.-'Jn.J4567on--9称子果枝眼蕉萄龙瓜子莲莓名橘苹荔龙香葡火西梨搐草区5S：.0&39；5，3.-2L6.9.0535212h'%*11\.5he"i/ShgE2d£品曰七3/|<>J.com选图Cjlicrosof?:Ezcjelj^gBookl:邕|文件⑪编辑④视图3插入•①格式⑪工具①额据⑪窗口世）|帮助01〕■4x：n簧宋体金泌▼」b』抖J冬季军।邕，3，1hl▼塞名称②然后，单击菜单栏的''数据”，在下拉列表中选择''分列”命令;l*Jcrccel-Bookl理竽蟒咫冲第辖区f视图世L插入①格式。工具①额据①汇窗口世)排序鼠.、:：帮助皿1,宋伟A1称子果枝眼蕾萄龙瓜子莲号名橘军嘉龙香葡火西梨榴草*55：-039；53；-2160氏冬6；.353：1213F12U滞选(D备名称BC分列d期一导人外部数据®〕列表辽〕工MLQ0刷新数据3〕同数据透视表和数推分类汇总",产有触件(L)一IL►~H-[\_Shee-tl/Sheet2/SheetS/图@一党|自选图用也一\、口。嵋回闻二圈度|务▼旦♦酱।JE就绪③此时，需要3个步骤来完成数据在表格中的拆分，''文本分列向导-3步骤之r,我们只需选择默认的''分割符号〃再单击下面的''下一步〃按钮；④然后，继续在''文本分列向导-3步骤之2〃下面的''分隔符号〃中勾选''Tab键〃、''空格〃和''连续分隔符号视为单个处理〃。（现在我们可以在''数据预览〃中看到拆分的效果）最后单击卜少；⑤最后一个步骤，我们单击''完成〃就可以了。拆分好的表格效果如下图所示:EGBMicrosoftEzcel-BooltJ.0:篁I文件⑪编辑也启视图迎产螺默俭I呼盘对）工具①额据⑪窗口也）Dl_-笈ABC5EF7T1名称橘字苹果荔枝龙眼,-3：.53548,34159,54X▼B』U享叁A▼12:.：帮助01）汨B1宋伟6香蕉3.27,：168火龙果99西瓜15/3.--10.^i,l7.小J11福莲3212,草莓12131/IVM彳►h\Sheetl/SheetJxSheets/㈣■V[[绘图购一国自选图用\、口O±J端划二阖源IW%▼▼二■―里就绪数字■■R提示：以上的表格数据拆分工作必须要有一定的规律才可以实现，比如文字与数字之间有空格或者逗号、分号等其他任何符号或有规律的字符都可以完成拆分工作。如果中间没空格或者有规律的字符，那么这项拆分数据表格的工作就无法实现了。8.根据颜色不同做IF判断色射U".胃”◊石*iA豆E布0应|期索的用等*五因自■回0g•一向礴白血蜘14（汨研幅3M一日则国"型 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 RttHR-的我？ 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 同于用三两的勖・色均流耳坤赤寸启后督班工至h*i口|□云用B降，冒'彳『岫铲立内暮61强L的名版5|!羽逋iW]■'口$口佗Excd如回熊1用单元恪理1砺ii式『r,才己0")子列排序=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,55.2-大中型建筑业打分卡问题清单’！B:D,3,0)数数=C0UNTIF(I:I,"V")A列相同的F列值和同UMIF(A:A,A2,F:F)倒序=INDEX($A$1:$A$119,ROWS($A$1:$A$119)-R0WS($A$1:A1)+1)两个相同再取值=VL00KUP(A2&$B$l,Sheet4!$B:$J,9,0)改名=CHOOSE(MATCH(,O/FIND({〃1A〃，〃1B〃，〃2A〃，〃2B〃，〃3A〃，〃3B〃，〃4A〃，〃4B〃，〃5A〃，〃5B〃，〃6A〃，〃6B〃，〃7A〃，〃7B〃，〃8A〃，〃8B〃},N358),),〃是〃，〃是〃，〃是〃，〃是〃，〃是〃，〃是〃，〃是〃，〃是〃，〃是〃，〃是〃,〃是〃，〃是〃,〃是〃，〃是〃,〃是〃，〃是〃)改日期格式：=TEXT(A4,〃yyyy/m/dd〃)=TEXT(B4,双重条件统计个数选择情况统计一股一般V一股一股一般熟悉一股一般V一股一般一般一般一般熟悉熟悉_般■JV212//19VVV010二SUMPRODUCT(($B$3:$AK$3=AM$3)*($B4:$AK4="J"))二COUNTIFS($B$3:$AK$3,AM$3,$B4:$AK4,"J")1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复"，"")。2、用出生年月来计算年龄公式：二TRUNC((DAYS360(H6,"2009/8/30”,FALSE))/360,0)。3、从输入的18位身份证号的出生年月计算公式：二CONCATENATE(MID(E2,7,4),"/",MID(E2,n,2),"/",MID(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)——对K2K56这一区域求平均数；3、排名：=RANK(K2，K$2:K$56)——对55名学生的成绩进行排名；4、等级：=IF(K2>=85，"优"，讦«2>=74,"良"，讦«2>=60,“及格"，"不及格”)))5、学期总评：:K2*0.3+M2*0.3+N2*0.4——假设K歹lj、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——求K2到K56区域95〜99.5分的人数；假设把结果存放于K58单元格；(3):COUNTIF(K2:K56,">=90")-SUM(K57:K58)——求K2到K56区域90〜94.5分的人数；假设把结果存放于K59单元格；(4):COUNTIF(K2:K56,">=85")-SUM(K57:K59)——求K2到K56区域85〜89.5分的人数；假设把结果存放于K60单元格；(5):COUNTIF(K2:K56,">=70")-SUM(K57:K60)——求K2到K56区域70〜84.5分的人数；假设把结果存放于K61单元格；(6):COUNTIF(K2:K56,">=60")-SUM(K57:K61)——求K2到K56区域60〜69.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、 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 差：二STDEV(K2:K56)——求K2到K56区域(55人)的成绩波动情况(数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化)；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),则此函数返回的结果表示求一班的男生人数；这是一个数组函数，输完后要按Ctrl+Shift+Enter组合键(产生”{……}”)。“{}”不能手工输入，只能用组合键产生。14、根据出生日期自动计算周岁：:TRUNC((DAYS360(D3,NOW()))/360,0)假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。15、在Word中三个小窍门：①连续输入三个“~”可得一条波浪线。②连续输入三个“-”可得一条直线。连续输入三个“=”可得一条双直线。一、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如：A1〉1时，C1显示红色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中怎样批量地处理按行排序假定有大量的数据（数值），需要将每一行按从大到小排序，如何操作？由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以，这一问题不能用排序来解决。解决方法如下：1、假定你的数据在A至E歹U，请在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键,让它自动加上数组公式符号〃{}〃。六、如何判断单元格里是否包含指定文本？假定对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))