- ?
EXCEL快速插入N多个指定名称的工作表,这种方法颠覆了三观
木槿
展开
导读:
在职场,很多妹子都遇到过这种情况,要在EXCEL中插入N多个工作表,而且要指定名称,说到插入工作表,很多人首先想到的就是插入工作表,而后右键工作表标签,命名,可是,如果有1000个,你也这样弄?这就是效率差,只要10秒,工作表就可以新建完毕!
作者简介:
MOS办公软件国际认证大师级
10年办公应用经验,企业部门负责人
思路:
在EXCEL中,有数据透视表,我们是否可以在EXCEL中,专门新建一列作为名称,而后将列数据写入到数据透视表筛选区域呢?数据透视表,还有一个功能,就是将字段作为显示报表筛选页,这样就可以将筛选字段作为工作表名称,以筛选字段名称为名称,建立多个工作表步骤1:制作我们要作为工作表名称的列字段,这里用1-30月作为工作表名称,也就是新建30个工作表
详细操作:
在A列,输入1-30个序列数据在B列,输入月,而后下拉,我们可以直接双击第一个单元格右下角的十字光标,就会自动填充【这个是快速填充的实用技巧】在C列输入公式=A1&B1,将A/B列数据连接起来,并复制【这个是两列合并的实用技巧】将数据选择性粘贴到A列,就缠上了1月到30月,30个数据步骤2:建立数据透视
操作步骤:
选中A列第一个单元格,而后在键盘上同时按住ctrl+shift+enter键,全选A列数据区域【这个是全选数据区域的实用技巧】在EXCEL的上方功能区,点击插入,而后在弹出的选项卡里面找到表格,数据透视表点击数据透视表,而后在弹出的对话框,直接点击确定即可,也可以修改透视表的展示位置点击数据透视表区域,而后在右侧的字段列表中,我们找到工作表名字段,将他拖动到筛选窗口这样就形成了一个数据透视步骤3:将筛选字段,作为工作表名称,新建工作表
操作步骤:
首先鼠标左键单击数据透视表区域,而后在上方功能区,找到分析选项卡,点击分析,而后在弹出的选项卡里面,右侧,找到数据透视表选项,点击选项右侧的向下的三角符号。点击三角符号后弹出一个显示报表筛选页,而后我们点击点击后,我们就可以看到,30个工作表,瞬间就新建完成,而且工作表名称和我们第一步命名的是一样的,说明此方法是可行的。方法讲解到这里,很多亲们,都已经掌握了,希望能够对大家有所帮助,喜欢的亲们, 请关注我们,一起学习更多办公实用教程。
- ?
一键篇 | Excel批量生成指定名称工作表技巧
雨莹
展开
在使用Excel表格过程中,我们会发现一个工作簿中可以包含多个工作表,仅03版就有能创建二百多个(具体数值是255),在往上的高版本更是可以创建数量惊人的工作表格,数量能够达到上万。对于如此多的工作表格,如何只是新建工作表,我们可以设置默认工作簿包含几个工作表设置来简单实现。但是如果需要对这么多的工作表标签指定命名,若我们还是用这种常规的命名方式(双击改名),那真是会把人的累的虚脱。今天本教程就教大家如何根据指定单元格内容,以工作表名方式批量创建工作表技巧。感兴趣的朋友,赶紧来看看吧!
1、首先是创建工作表名称:这个可以自己输入,也可以复制粘贴,也可以导入文本。不管你用什么方式创建,反正只要是合法字符的名称即可。教程以Excel为例,创建工作表的名称如下,A1单元格为创建表格标题,从A2到A20单元格均为需要创建工作表的名称。
2、代码汇编:在Excel界面按下组合键Alt+F11快速调出VBA窗口;在VBA界面中单击菜单栏插入,然后选择模块命令;此时会弹出一个模块窗口,在模块窗口复制粘贴以下代码(最好是将输入法切换至中文输入法模式下复制粘贴):
Sub 批量生成指定名称工作表()
Dim i%, j%, k%
i = Application.CountA(Range("a2:a20"))
For j = 2 To i Step 1
Sheets.Add after:=Sheets(Sheets.Count)
k = Worksheets.Count
Sheets(k).Select
Sheets(k).Name = Sheets(1).Cells(j, 1).Value
Next
End Sub
3、结果显示:将鼠标光标定位在这段代码当中,然后按下F5键运行该段代码程序(或单击菜单栏中的运行,然后选择运行子程序命令,如上图所示)。待代码运行完毕,按下Alt+F11关闭VBA窗口,返回Excel工作界面查看结果,发现已经按照我们的要求批量生产了一堆工作表。
- ?
EXCEL多表资料提取汇总方法,值得收藏
唐士萧
展开
EXCEL多表资料提取汇总,如果你还在复制粘贴,那就太落后了;
如果领导给你一份含有公司所有人简历的资料表,让你把所有人员的信息提取汇总到一个表中,公司有几十或几百人,只靠复制粘贴,恐怕一天都不一定能完成;
今天我们来讲一下多表资料提取汇总的方法,分三步完成;
本教程基于excel2016;
首先,各人员姓名所在的工作表格式必须相同;
在“汇总”表内设置好表头;
1.指定名称
【公式】--【定义名称】--【名称】处随便命一个名称ML,【引用位置】处输入=GET.WORKBOOK(1);
2.提取工作表名称
在O3单元格输入=INDEX(ML,ROW(A1)),向下填充;
这样各工作表对应的名称就提取出来了;
3.提取资料
在B2单元格输入=INDIRECT($O3&"!B2"),B2姓名所对应的名字在“周小林”表中在B2单元格;
C2输入=INDIRECT($O3&"!D2"),C2性别所对应的性别在“周小林”表中在D2单元格;
D2输入=INDIRECT($O3&"!B3"),D2民族所对应的民族在“周小林”表中在B3单元格;
下同
将B2到N2单元格分别输入完成后,选择B2:N2,下拉填充;
注意点:
1)日期列数字,选中,设置单元格格式,日期
2)选中B2:N4区域,复制,右键选择性粘贴,数值,就可以删除O列;
3)因GET.WORKBOOK(1)是宏表函数,所以在完成后,将工作表“另存为”后缀为“xlsm”格式或者“启用宏的工作薄”;
- ?
如何解决Indirect函数在不同类型工作表名称下的多表引用问题
兔灰灰
展开
之前的课程中我们讲解了如何引用多个工作表数据,今天我们来讲解一下,如何解决Indirect函数在不同类型工作表名称下的多表引用问题。
一、案例介绍:求出1-6号个人销售总金额
如上图,表格中有1号-6号人员每天的数据,怎么求出6天这个人的销售总金额?
函数介绍:
SUM(SUMIF(INDIRECT(ROW($1:$6)&"!A:A"),A2,INDIRECT(ROW($1:$6)&"!g:g")))
1、此处运用条件求和的方式,因为每天每人的数据都是有不一样的情况,所以最开始用sumif条件求和;
2、INDIRECT(ROW($1:$6)&"!A:A")代表将1-6号每天的人名提取出来;
3、INDIRECT(ROW($1:$6)&"!g:g"),代表将提前出来的人名=A2时,提取出1-6号G列的销售数据。
4、用sumif函数与indirect函数查找出每一个符合条件的销售金额后,最后用sum函数进行求和即可。
二、案例延伸
1、当工作表名称为数字格式时候,如何引用多个工作表数据:
如上面案例当中,各月的工作表名称分别以1、2、3、4、5、6等格式进行分类汇总。这个时候我们的Indirect函数可以使用INDIRECT(ROW($1:$6)&"!A:A"),来进行对工作表1-6的A列进行引用。
2、当工作表名称为文本格式时候,如何引用多个工作表数据:
如上图,工作表名称以1月-6月等依次排序。
函数介绍:
SUM(SUMIF(INDIRECT((ROW($1:$6)&"月")&"!A:A"),A2,INDIRECT((ROW($1:$6)&"月")&"!g:g")))
函数INDIRECT((ROW($1:$6)&"月")&"!A:A")用&与双引号对文本进行注释,函数解析后的结果就是工作表row(1月:6月)!A:A,对应的G列数据的引用也是一样。
- ?
Excel VBA:汇总多个工作簿每个工作表名称包含指定关键词的数据
金蛇君
展开
今天再分享下汇总指定文件夹下每个工作簿中工作表名称包含某个指定关键词的小代码(当不指定关键词时,则默认汇总所有工作表数据)。
举个栗子。假设有一文件夹,内有十几个工作簿,每个工作簿又各有多个不等数量的工作表,现在我们只想汇总每个工作簿中工作表名称包含“看见星光”的数据,那就可以使用我们今天分享的小代码了。
如果是想把所有工作表的数据一股脑全部汇总呢?不管它什么“看见星光”还是“看见月光”的——也可以使用今天的代码,程序运行中弹出的输入关键词对话框什么都不填直接确定就可以了。
小贴士:
1、(重复说明)如果需要汇总所有工作表的数据,关键词对话框什么都不填直接确定就可以了、另外关键词不区分字母大小写。
2、如果需要汇总的工作表含有多个不同的关键词……也是可以的,由于代码汇总后的数据后增加一个“来源工作表”的字段,表亲们可以先把所有工作表的数据汇总,然后根据“来源工作表”字段对数据明细进行筛选删除操作。
代码如下:
Sub Collectwks()
Dim Sht As Worksheet, rng As Range, Sh As Worksheet
Dim Trow&, k&, arr, brr, i&, j&, book&, a&
Dim p$, f$, Headr, Keystr
'
With Application.FileDialog(msoFileDialogFolderPicker)
'取得用户选择的文件夹路径
.AllowMultiSelect = False
If .Show Then p = .SelectedItems(1) Else Exit Sub
End With
If Right(p, 1) <> "\" Then p = p & "\"
'
Keystr = InputBox("请输入需要合并的工作表所包含的关键词:", "提醒")
If StrPtr(Keystr) = 0 Then Exit Sub
'如果点击了inputbox的取消或者关闭按钮,则退出程序
Trow = Val(InputBox("请输入标题的行数", "提醒"))
If Trow < 0 Then MsgBox "标题行数不能为负数。", 64, "警告": Exit Sub
Set Sht = ActiveSheet
Application.ScreenUpdating = False '关闭屏幕更新
Cells.ClearContents
Cells.NumberFormat = "@"
'清空当前表数据并设置为文本格式
ReDim brr(1 To 200000, 1 To 2)
'定义装汇总结果的数组brr,最大行数为20万行,2列是临时的
'
f = Dir(p & "*.xls*") '开始遍历工作簿
Do While f <>""
If f <> ThisWorkbook.Name Then '避免同名文件重复打开出错
With GetObject(p & f)
'以'只读'形式读取文件时,使用getobject方法会比workbooks.open稍快
For Each Sh In .Worksheets '遍历表
If InStr(1, Sh.Name, Keystr, vbTextCompare) Then
'如果表中包含关键词则进行汇总(不区分关键词字母大小写)
Set rng = Sh.UsedRange
If rng.Count > 1 Then
'如果rng的单元格数量大于1……
book = book + 1 '标记一下是否首个Sheet,如果首个sheet,BOOK=1
a = IIf(book = 1, 1, Trow + 1) '遍历读取arr数组时是否扣掉标题行
arr = rng.Value '数据区域读入数组arr
If UBound(arr, 2) + 2 > UBound(brr, 2) Then
'动态调整结果数组brr的最大列数,避免明细表列数不一的情况。
ReDim Preserve brr(1 To 200000, 1 To UBound(arr, 2) + 2)
End If
For i = a To UBound(arr) '遍历行
k = k + 1 '累加记录条数
brr(k, 1) = f '数组第一列放工作簿名称
brr(k, 2) = Sh.Name '数组第二列放工作表名称
For j = 1 To UBound(arr, 2) '遍历列
brr(k, j + 2) = arr(i, j)
Next
Next
End If
End If
Next
.Close False '关闭工作簿
End With
End If
f = Dir '下一个表格
Loop
If k > 0 Then
Sht.Select
[a1].Offset(IIf(Trow = 0, 1, 0)).Resize(k, UBound(brr, 2)) = brr '放数据区域
[a1].Resize(1, 2) = [{"来源工作簿名称","来源工作表名"}]
MsgBox "汇总完成。"
End If
Application.ScreenUpdating = True '恢复屏幕更新
End Sub
- ?
批量提取工作表的名称,实时动态更新超级简单!
迎梦
展开
工作表名称太多,怎么快速提取文件名称?现在简单的分享两个办法吧,接下来看下具体做法:
一、同一工作簿中提取(10和13版本需要安装powerquery插件)
①点击公式——定义名称——输入名字——引用位置输入公式=GET.WORKBOOK(1) 点击确定
②在B2中输入公式
=IFERROR(INDEX(name,ROW(A1)),"") 下拉填充即可获取所有工作表名称
2、去除工作簿名称
在C2单元格中输入完整名称后按CTRL+E即可快速填充下面所有的内容
PS:这一步也可以使用数据菜单下的分列快速去除前缀。
二、不同文件夹下的工作表名称包括保存的路径
1、导入源文件:点击数据——新建查询——从文件——从文件夹——浏览——点击需要提取名称的文件夹——确定——编辑(此方法也可以从工作簿中获取名称)
2、整理字段:①将路径移动到开头——选择路径和名称删除其他列——选中名称列点击转换——文本列——拆分列——按分隔符——自定义——输入英文状态下的"句号"——高级——列数为2列——确定——删除name2列
②导入表格中:点击开始——关闭上载至——加载到现有链接——点击加载就OK了
最最关键的是如果你的文件夹中增加表格、文档、或者其他的文件,只要点击刷新后这边就会实时更新啦!动画演示新增加一个word文档:
需要将鼠标放在表格区域内——点击查询——刷新即可
好啦,今天的推荐就到这里啦!有些小伙伴可能因为电脑中的文件太多,这时候就可利用新建查询来创建目录链接,查找起来就会很方便了!更多小技巧欢迎关注我吧!
- ?
Excel中怎样批量建立固定名称的工作表,并让他们美美哒!
Debbie
展开
部分同学有时会遇到这种问题——在空白的Excel表中一下子需要建立很多的工作表,最好是名称也能一下子记录好,而不是自己一个个去重命名。
有位同学说,老师,我这样重命名下来,累也累死了!
这个操作应该还是有部分同学知道并用过的——就是使用数据透视表来做!
插入——数据透视表
选中我们需要插入的工作表名称——勾选“现有工作表”——选中C1(当然其他的单元格也可以)
这是我们出来的透视表——勾选“带插入工作表名称”——拉到“筛选器”中——关闭
分析——选项——显示报表筛选项
确定
好了
咱们还是美化下,这样看的也清楚!(右键“工作表标签颜色”可以设置哦!)
今天的知识,你学会了吗?哪里不明白的请在讨论区提问哦!
更多Excel学习和问题交流请加群!558669700
当然你有更好的方法也可以在讨论区分享给我们
【剧透:下个知识点——批量设置工作表格式】
如何在Excel多个工作表中批量建立下拉菜单:看前面的文章哦!
- ?
怎么搜索Excel中的全部工作表(sheet)
支冬寒
展开
使用Excel时,经常需要在表格中查找指定的数据或者文字等内容。当然,查找的操作比较简单,但Excel中默认只是在当前的工作表(Sheet)中进行查找。如果一个Excel工作簿(或者说是一个Excel文档)中有很多工作表,那么要想在所有的工作表中查找指定内容,默认设置下就需要逐个打开各个工作表,再逐个进行查找操作。这种情况时我们可以在查找时特别设置一下,让Excel在当前工作簿的所有工作表中进行查找,从而提高工作效率。下面以Excel2007为例介绍如何设置,以供参考。
●点击Excel中的“查找”或者按键盘的“Ctrl+F”组合键打开“查找和替换”对话框后,点击其中的“选项”按钮。
●点击选项按钮后,查找和选择对话框中会展开更多的选项。如果在“范围”选项右侧显示的是“工作表”,则说明现在设置的只是在当前的工作表中进行查找。如果想要查找所有的工作表,需点击“范围”右侧的下拉框。
●弹出下拉列表后,在列表中点击“工作簿”选项。即将查找范围设置成了当前工作簿中的所有工作表。
●然后再输入要查找的内容,点击“查找全部”按钮。如果想逐个显示查找结果,则需点击“查找下一个”按钮。
●点击“查找全部”按钮后,对话框下方会显示出查找结果列表,在其中的“工作表”列中会显示包含查找内容的工作表名称。在某个查找结果所在的行中点击鼠标,Excel就会自动打开对应的工作表,并且选中对应的单元格。
这个小技巧可以让我们在Excel工作簿中进行查找时更快速的找到需要的结果,提高工作效率。
- ?
Excel技巧:批量创建、修改、计算多个工作表,提取工作表名称
清楚
展开
小王天天和Excel打交道,这不,到年底了,要整理的Excel文件多起来了,每份Excel中的工作表也多起来了。合并、修改、查询、计算,忙得不亦乐乎。
其实,关于工作表的批量操作,有以下这些小技巧。学会了,几分钟就可以完成几小时甚至一天的工作。
一、批量创建指定名称的工作表
应用:为一年12个月创建12个工作表、为一个月31天创建31个工作表、为小组里的每个人创建一个工作表。
步骤1
输入要创建的工作表名称,选中这些名称,插入数据透视表,将“工作表名称”(即标题)拖动到“筛选器”下。
步骤2
选中数据透视表中的“工作表名称”,点击“显示报告筛选页”,这样瞬间就创建好了多个指定名称的工作表啦。
步骤3
选中第一个工作表,按照Shift键,同时点击最后一个工作表,这样就选中了所有的工作表,接着删除工作表中多余的透视表。
小tips
工作表名称旁边有4个小箭头,分别可以跳转到“第一个”、“前一个”、“后一个”和“最后一个”工作表。
二、批量修改工作表中的内容(好像复印机一样)
应用:批量添加表头、批量修改单元格格式、批量去除网络线。
方法很简单,先制作第一份表格,然后复制表格,按Shift键选中所有的表格,最后粘贴即可。
每个工作表都粘贴了表格之后,想要修改格式、添加相同的内容。同样可以按Shift键选中所有的工作表,然后修改其中的一个即可。
还有影响美观的网络线,也可以一次性批量取消。
好啦,小王已经把模板创建好了,将表格放到共享目录下,招呼每个人去填写自己的那一部分,一个人坐在位置上慢悠悠慢悠悠地喝茶。
三、快速切换各个工作表(一键目录)
应用:展示报告时,除了查看所有表格的汇总结果,也常常需要查看某一个特别的工作表。
不需要任何的公式,只要点击工作表名称左侧的空白区域,就会看到所有的工作表名称,点击表名即可实现跳转。
四、提取所有工作表名称、批量修改工作表名称
虽然有一键目录的功能,可还是有朋友想要提取出所有的工作表名称。
应用:制作目录、创建公式。
方法1
首先新建名称“sheetName=GET.WORKBOOK(1)”。
接着输入以下第二个公式,即可直接获得工作表名称;如果觉得这个公式太长了,也可以使用第一个公式,之后再用分列提取真正的工作表名称。
公式1:=INDEX(sheetName,ROW(A1))公式2:=REPLACE(INDEX(sheetName,ROW(A1)),1,FIND("]",INDEX(sheetName,ROW(A1))),"")
方法2
对于Excel2016,我们可以压缩Excel文件,然后解压出来,打开“xl”文件夹下的workbook.xml文件,也可以快速提取出所有的工作表名称。
修改这份workbook.xml文件,还可以批量修改工作表名称、快速调整工作表顺序。修改完毕,将这些Excel源码文件重新压缩,再修改回“.xlsx”格式。
五、汇总多个工作表的数据
应用:计算一年12个月的销量总和、计算一个月的平均数据。
汇总多个工作表的数据,可以使用“合并计算”功能,也可以使用数据透视表多重合并计算,还可以直接使用公式。
同事们填写好自己的数据后,小王是这样输入公式的。
看明白了吗,和普通的公式输入并没有太大的区别。只是在选择公式参数时,先按Shift键选中了所有的工作表,然后点击其中的一个单元格,即可计算出所有相同位置的数据的总和啦。
相关阅读:《如何将多个Excel文件或工作表合并成一个》、《WPS Excel: 如何合并多个Excel文件》、《合并多个Excel文件,不管什么版本的Excel或WPS都适用》。
学习,为了更好的生活。欢迎点赞、评论、关注和点击头像。
- ?
小技巧:excel工作簿快速提取各个工作表名称
路易丝
展开
excel工作簿快速提取各个工作表名称的方法:
1.定义名称“获取表名”,在“插入”菜单下点击“名称”下的“定义”。
2.名称定义为get ,可以随便设置,在下方输入函数“=get.workbook(1)”。
3.在单元格中,选择多个单元格,输入公式=transpose(get),然后按ctrl+shift+enter三键输入数组计算。
4.可以看到,工作表名称是获得了,但前面的前缀还要删除掉。选择所有的工作表名称,ctrl+c,再右击,在弹出的菜单中选择“选择性粘贴”。
5.在“选择性粘贴”窗口中选择“数值”后点击“确定”按钮。
6.在“数据”菜单下选择“分列”。
7.在“分列”窗口中我们选择“固定宽度”。
8.如图将做分隔线定位在工作表前。
9.点击下一步骤,选择“不导入此列(跳过),最后点击”确定按钮。这个时候就可以提取出所有工作表的名称了。
(本文内容由百度知道网友雷筱轩33贡献)