- ?
合并多个Excel文件,不管什么版本的Excel或WPS都适用
如若
展开
昨日,分享了使用Power Query插件快速合并多个Excel的方法,可惜很多朋友表示还没用上Excel2016。所以,今天分享一个新方法,不管什么版本的Excel,或者WPS表格,都适用。
实例
将一个部门所有人半年的销售数据合并到一份Excel文件中。
思路
我们想获取单元格A1的内容,就会输入公式“=A1”;如果要获取Sheet1下的A1内容,就会输入“=Sheet1!A1”;如果要获取的在“解晴.xlsx”文件下,就会输入“='D:\数据\[解晴.xlsx]Sheet1'!A1”。基于此,我们可以不用打开Excel文件,便轻松合并多个Excel。
步骤1:提取Excel文件名称
在命令提示符中输入“dir /b Excel文件所在路径”获取所有Excel文件的名称,复制名称到一个空白Excel工作表中。然后复制A列所有的名称,选中“B1:H8”区域,按“Ctrl + V”粘贴,这样“A-H”列都粘贴上了Excel文件名。
为什么是“A-H”列呢,因为原数据就是这样的。
步骤2:Excel名称转换成一列
复制“A-H”列数据粘贴到Word文档中,选择“只保留文本”,然后使用“文本转表格”工具转换成一列数据的表格。
步骤3:创建辅助表,获取源数据单元格地址
将一列的Excel名称粘贴到汇总表的A列,在“B-I”列输入“A1-H8”,即要合并的Excel文件中数据的单元格地址。
复制“B1-I8”数据,然后在输入栏左侧的名称中输入“B9:I40”,按Enter键选中这块区域,最后按“Ctrl + V”粘贴,效果同步骤1。
步骤4:输入公式,连接Excel文件名和数据地址
J1公式:="t='D:\数据\["&$A1&"]Sheet1'!"&B1
公式中的“D:\数据\”是Excel文件路径,“Sheet1”是要合并的Excel文件中的工作表名称。
公式输入后,向右填充,直到结果显示“H1”为止。然后选中这些公式,双击填充所有的公式。
步骤5:粘贴为数值,然后将文本公式转换成真正的公式
复制步骤4的结果,选择性粘贴为数值。然后将“t=”替换为“=”。
所有Excel文件的内容就都合并到一起啦。
看起来似乎有点复杂,但其实很容易,只要稍微理解一下整个的思路就好了。
方法优点:
不限Excel版本和 wps版本,比Power Query灵活。可用于合并多个工作表。想提取几行或几列都可以,取决于你在创建辅助表时,输入了多少单元格地址。本例中的“A1-H1”是标题行,创建辅助表时,删除“A1-H1”,就不会多次提取到标题了。
谢谢阅读,每天学一点,省下时间充实自己。欢迎点赞、评论、关注和点击头像。
- ?
Excel函数公式:你会输入m或m吗?
瓦蓝
展开
在Excel中经常也会遇到非常专业的数学符号,例如m,m等等。那么在Excel中怎么输入了?有没有非常便捷的方法了?
一、格式输入法。
方法:
1、在目标单元格输入m3或m2.
2、选中3或2。
3、Ctrl+1打开【设置单元格格式】对话框。
4、选中【特殊效果】中的【上标】。
备注:
其实利用此方法不仅可以输入上标,还可以输入下标、删除线,方法其实是一模一样的。
二、输入法。
方法:
1、在目标单元格输入m。
2、利用输入法输入“立方”或“平方”。
3、选择“立方”或“平方”。
- ?
EXCEL实用小技巧,EXCEL提示语如何做,不是批注哦!
玉蝴蝶
展开
有的时候工作中用到别人的EXCEL,选中某一格的时候,显示有提示语,但是又不同于批注,感觉感觉方便,那么这个是怎么做的呢?
让Excel出现错误数据提示
Excel除了可以对单元格或单元格区域设置数据有效性条件并进行检查外,还可以在用户选择单元格或单元格区域时显示帮助性“输入信息”,也可以在用户输入了非法数据时提示“错误警告”。
方法: 选取单元格或单元格区域,单击“数据→有效性”命令,单击“输入信息”选项卡,选定“选定单元格时显示输入信息”复选框,输入标题,如“注意”,输入显示信息如“这里应输入负数!”(),单击“确定”按钮。
操作方法此后,再选定那些单元格或单元格区域时,Excel将自动提示上述信息。
就是这么简单,你会了吗?
- ?
Excel条件格式怎么用if公式标出满足多条件的单元格或整行
傅芷容
展开
在制作 Excel 表格过程中,有时需要用颜色标出满足一定条件的单元格,有时又需要标出满足一定条件的整行;它们可以用“条件格式”中的“使用公式确定要设置格式的单元格”加if条件实现。用这个方法不但可以标出满足一个条件的单元格或整行,并且可以标出满足多个条件的单元格或整行;另外,还可以自由选择用何种颜色标示。以下就是用条件格式加if标出满足一个条件或多条件的单元格或整行的具体操作方法,实例中操作所用版本均为 Excel 2016。
一、Excel条件格式怎么用if公式标出满足条件的单元格
1、假如要用绿色标出某班各科成绩平均分80分以上的学生姓名。框选所有学生成绩记录(即 A3:K30 这片区域),选择“开始”选项卡,单击“条件格式”,在弹出的菜单中选择“新建规则”,打开“新建格式规则”窗口,选择“使用公式确定要设置格式的单元格”,在“为符合此公式的值设置格式”下输入公式 =IF(K3>=80,B3);单击“格式”,打开“设置单元格格式”窗口,选择“绿色”,单击“确定”,返回“新建格式规则”窗口,再单击“确定”,则用绿色标出所有平均分在80分以上的学生姓名,操作过程步骤,如图1所示:
图12、注意:
A、不能框选表格的标题列,应该从表格记录行(即从 A3 单元格)开始框选,否则会发生错误,错误通常发生在标题行下的第一行,往往是不符合要求也用颜色标出。
B、要求用颜色标出第一列,但公式 =IF(K3>=80,B3) 中却不能写直接写 A3,因为 A3 这列不是数值,如果直接写 A3,将无法标出满条件的学生姓名,所以用数值列 B3 代替。
二、Excel条件格式怎么用if公式标出满足条件的整行
1、假如把某班学生成绩表中平均分在90分以上的整行用蓝色标出。按住 Alt,按一次 H,按一次 L,按一次 N,打开“新建格式规则”窗口,选择“使用公式确定要设置格式的单元格”,在“为符合此公式的值设置格式”下输入公式 =$K3>=90;单击“格式”,选择“填充”选项卡,选择“蓝色”,单击两次“确定”;再次按住 Alt,按一次 H,按一次 L,按一次 R,打开“条件格式规则管理器”窗口,选中“公式:=$K3>=90,在“应用于下”对应的输入框中把引用改为 =$3:$30;单击“编辑规则”,原来输入的公式发生了变化,把它改回原来的公式 =$K3>=90,单击两次“确定”,则用蓝色标出平均分在90分以上的学生,操作过程步骤,如图2所示:
2、在“条件格式规则管理器”窗口修改引用单元格后,原来输入的公式常常会发生变化,所以一定要返回检查,变化了则把它改回原来的公式。
3、=$3:$30 是对行的绝对引用,即绝对引用第三行到第30行,也就是只引用记录行,不要引用表格标题行,否则可能发生错误。
三、Excel怎么用多个if条件标出满足条件的单元格
1、假如要用橙色标出平均分在80分以上、C语言在90分以上的学生姓名。框选学生成绩表(即可选中 A3:K30 这片区域),按住 Alt,按一次 H,按一次 L,按一次 N,打开“新建格式规则”窗口,选择“使用公式确定要设置格式的单元格”,输入公式 =IF(K3>=80,IF(G3>=90,B3)),如图3所示:
2、单击“格式”,打开“设置单元格格式”窗口,选择“填充”选项卡,选择“标准色”下的“橙色”,如图4所示:
图43、单击“确定”,返回“新建格式规则”窗口,再次单击“确定”,则用橙色标出所有平均分在80分以上、C语言在90分以上的学生姓名,如图5所示:
图54、公式说明。公式 =IF(K3>=80,IF(G3>=90,B3)) 共有两个 IF,即 IF 中嵌套 IF;其中 K3>=80 是第一个 IF 的条件,如果 K3>=80 为真,则执行第二个 IF;如果为假,则什么了不返回;如果第二个 IF 为真,则返回 B3,否则什么也不返回。
- ?
Excel2010如何移动或复制整个工作表
比尔
展开
在Excel2010中,我们可以将Excel工作表移动或复制到同一工作簿的其他位置或其他Excel工作簿中。但在Excel2010移动或复制工作表时需要十分谨慎,因为若移动了工作表,则基于工作表数据的计算可能出错。
一、 同一工作簿中移动和复制工作表
在同一个工作簿中,直接拖动工作表标签至所需位置即可实现工作表的移动;若在拖动工作表标签的过程中按住【Ctrl】键,则表示复制工作表。
二、不同工作簿间的移动和复制工作表
要在不同工作簿间移动和复制工作表,可执行以下操作。
1、打开要进行移动或复制的源工作簿和目标工作簿,单击要进行移动或复制操作的工作表标签,然后单击“开始”选项卡“单元格”组中“格式”按钮,在展开的列表中选择“移动或复制工作表”项,打开“移动或复制工作表”对话框,如图1所示。
2、在“将选定工作表移至工作簿”下拉列表中选择目标工作簿;在“下列选定工作表之前”列表中选择要将工作表复制或移动到目标工作簿的位臵;若要复制工作表,需选中“建立副本”复选框。最后单击“确定”按钮,即可实现不同工作簿间工作表的移动或复制。
通过Excel2010移动或复制工作表这篇教程,我们了了解工作表组的作用。利用Excel工作表组功能,可以快捷地在同一工作簿的多张工作表的相同位置输入或编辑一批相同或格式类似的工作表,从而提高工作效率。
- ?
20个可以让任何人成为Excel专家的Excel技巧(上)
宁傻姑
展开
在Microsoft将Excel开发为2010版之后,它提供了比以往更多的惊喜。为了处理大量的大数据,您不能忽视Excel在日常工作中扮演的重要角色。但是,对于初学者和高级用户来说,仍然有许多有用的提示和技巧不可避免地被忽视。以下是您可能不知道的20个有用的Excel电子表格秘密。请注意,所有这些功能都基于Microsoft Excel 2010。
excel技巧1.一键选择全部
您可能知道如何使用Ctrl + A快捷键选择全部,但很少有人知道只需单击角落按钮,如下面的屏幕截图所示,所有数据都将在几秒钟内被选中。
excel模板2.批量打开Excel文件
当您有多个文件需要处理时,不是一个接一个地打开文件,只需单击一下就可以轻松打开它们。选择要打开的文件,然后按键盘上的Enter键,所有文件将同时打开。
excel模板3.在不同的Excel文件之间切换
当你打开不同的电子表格时,在不同的文件之间切换真的很烦人,因为有时候在错误的工作表上工作可能会破坏整个项目。使用Ctrl + Tab可以自由地在不同文件之间切换。当使用Windows 7打开时,此功能也适用于Firefox中不同Windows选项卡等其他文件。
excel模板4.创建一个新的快捷菜单
通常,顶部菜单中有三个快捷方式,分别是“保存”,“撤消键入”和“重复键入”。但是,如果要使用更多快捷方式,例如“复制”和“剪切”,则可以按如下方式设置它们:
文件 - >选项 - >快速访问工具栏,从左侧列向右添加剪切和复制,保存。您将在顶部菜单中看到另外两个快捷方式。
excel模板5.向单元格添加对角线
例如,在创建同学地址列表时,您可能需要在第一个单元格中使用对角线链接来分隔行和列的不同属性。怎么做?每个人都知道Home-> Font-> Borders可以改变单元格的不同边框,甚至可以添加不同的颜色。但是,如果单击“更多边框”,您将获得更多惊喜,例如对角线。单击并保存 - 您现在可以立即进行保存。
excel模板6.添加多个新行或列
您可能知道添加一个新行或列的方法,但是如果您需要通过重复此操作X次来插入多个这样的行,则会浪费很多时间。如果要在上方或左侧添加X行或列,最好的方法是拖动并选择X行或列(X为两个或更多)。右键单击突出显示的行或列,然后从下拉菜单中选择“插入”。新行将插入到您首次选择的列的行上方或左侧。
excel模板7.快速移动和复制单元格中的数据
如果要在电子表格中移动一列数据,快速方法是选择它并将指针移动到边框,在转到交叉箭头图标后,拖动以自由移动列。如果要复制数据怎么办?您可以在拖动之前按Ctrl键移动; 新列将复制所有选定的数据。
excel模板8.快速删除空白单元格
由于各种原因,某些默认数据将为空白。如果您需要删除这些以保持准确性,尤其是在计算平均值时,快速的方法是过滤掉所有空白单元格并一键删除它们。选择要过滤的列,转到数据 - >过滤器,在向下按钮显示后,撤消全选,然后选择最后一个选项,空白。所有空白单元格将立即显示。返回主页并直接单击“删除”,将删除所有这些内容。
excel模板9.使用外卡模糊搜索
您可能知道如何使用快捷键Ctrl + F激活快速搜索,但有两个主要的通配符 - 问号和星号 - 用于Excel电子表格以激活模糊搜索。当您不确定目标结果时使用。问号代表一个字符,星号代表一个或多个字符。如果您需要搜索问号和星号作为目标结果怎么办?别忘了在前面添加一条波浪线。
excel模板10.在列中生成唯一值
您了解过滤器的关键功能,但很少有人使用高级过滤器,当您需要从列中的数据过滤唯一值时,将重复应用高级过滤器。单击以选择列,然后转到数据 - >高级。将出现一个弹出窗口。如屏幕截图所示,单击复制到另一个位置,该位置应与第二个红色矩形区域一致。然后通过键入值或单击区域选择按钮来指定目标位置。在此示例中,可以从列C生成唯一年龄并在列E中显示。不要忘记仅选择“唯一记录”,然后单击“确定”。E列中显示的唯一值可以是C中原始数据的对比度,这就是建议将其复制到另一个位置的原因。
excel模板本文由蚂蚁工资条整理发布,转载请注明出处。
蚂蚁工资条可将Excel工资表自动生成工资条,并以短信、邮件或微信的形式发送给员工。使用蚂蚁工资条,将极大地提升HR的工作效率,也能减少与员工的沟通成本,促进员工满意度的提升。
- ?
excel文件打不开提示文件格式或文件扩展名无效
迎南
展开
1.根据系统给出的提示先了解分析出现文件打不开问题原因,如下图提示的是文件格式或文件扩展名无效。
2.知道原因后我们要先看一下文件的后缀,打开“我的电脑”在右上角找到“组织”点开,找到文件夹很搜索选项。
3.在对话框中选择第二项“查看”,在高级选项中找到“隐藏已知文件类型的扩展名”把前面的勾去掉
4.现在我们就可以看到文件的后缀了,右键点击文件“重命名”把文件后缀改成“xls”
- ?
如何将多个Excel文件或工作表合并成一个
伍香彤
展开
应用场景1
老板:小王啊,调查一下大家对我们人事部的服务有什么意见或建议没。小王:好的。
小王做好一份Excel调查表模板,分发下去。然后收到了100份结果文件。
应用场景2
老板:小王啊,还有5分钟我就要开会了,我们12家门店的销售情况表汇总到一起了吧。
小王应该怎么合并这些Excel文件?一个一个复制到手软吗?
合并多个Excel文件
第一步:将所有的Excel调查表文件存放在同一个文件夹下。打开一个空白Excel文件,点击“新建查询”,选择“从文件夹”中导入,然后粘贴所有Excel调查表所在路径。
第二步:在“组合”下拉菜单中选择“合并和编辑”。
第三步:选中要合并的工作表。
第四步:点击Power Query编辑器中的“关闭并上载”。
稍等片刻,所有的文件就都合并到同一个工作表啦。
可以看到表头的两行重复显示了很多遍。使用定位或筛选功能,删除多余的行即可。
当然,也可以在上述步骤4“关闭并上载”之前删除一些行。
更新数据
小王好开心,100份Excel调查表就这么轻松地合并到一起了。小丽:小王,我的调查表修改了一下,发一份新的给你吧。小王……
小王,莫急,直接在原来的文件夹下替换小丽的调查表,然后在合并后的汇总表上,右键一下,点击“刷新”即可。
合并多个Excel工作表
和合并excel文件类似,用Power Query同样可以快速完成。
第一步:打开一个空白Excel文件,点击“新建查询”,选择“从工作簿”中导入,然后打开需要汇总的Excel文件。
第二步:选中所有要合并的工作表名称,然后点击“编辑”。
第三步:按照图示的步骤,点击“追加查询”,然后将所有的工作表添加进来。
最后点击“关闭并上载”,所有工作表内容就合并到一个工作表下啦。同样的,如果原始的表格数据更新,在汇总表上刷新一下即可同步更新数据。
注意:以上步骤都基于Power Query插件,Excel2016才支持。使用低版本的Excel或WPS的朋友请查考明日的文章。
相关阅读:《多个word文档怎么合并》。
谢谢阅读,每天学一点,省下时间充实自己。欢迎点赞、评论、关注和点击头像。
- ?
Excel提示Excel词典xllex.dll文件丢失或损坏
雍浩然
展开
最近有Win10用户反映,运行Excel表格的时候出现提示“Excel词典xllex.dll文件丢失或损坏”,导致Excel表格运行失败,这让用户非常烦恼。那么,Win10运行Excel表格提示“Excel词典xllex.dll文件丢失或损坏”怎么办呢?接下来,我们就一起往下看看Win10运行Excel表格提示“Excel词典xllex.dll文件丢失或损坏”的解决方法。
Excel系列软件最新版本下载
excel2007软件版本:免费完整版应用工具立即查看
excel2010软件版本:免费完整版应用工具立即查看
Excel表格制作软件版本:2.0Android软件立即查看
1、找到桌面Excel图标,右击,在菜单中选择属性;
2、选择兼容模式那个栏目,勾选兼容模式,以管理员身份运行此程序,然后确定;
3、双击打开Excel软件,跳出对话框,点击是;
4、点击是;
5、这样Excel就打开了,下次就不会出现这个问题了。
以上就是Win10运行Excel表格提示“Excel词典xllex.dll文件丢失或损坏”的具体解决方法,按照以上方法进行操作,相信就能顺利解决该问题了。
- ?
让Excel自动添加表示数据上升、下降的箭头
Cornelius
展开
在用Excel对数据进行同比或者环比统计时,在统计结果中添加表示数据上升或者下降的箭头看起来会更加直观。Excel有多种添加上升、下降的箭头的方法,例如可以手动添加、可以用自定义单元格格式的方法添加、还可以用VB代码添加,但笔者感觉这些方法操作起来或者比较繁琐、或者专业性较强。这里以Excel2010为例介绍一种笔者感觉相对比较简单的用条件格式添加上升、下降的箭头的方法,以供参考。
先选择要添加上升、下降箭头的单元格范围。
点击Excel“开始”选项卡中“条件格式”下拉菜单中的“图标集”。
在图标集的子菜单中点选一个上升、下降箭头样式。
点选箭头样式后,之前所选单元格范围中的数据前就自动添加表示上升、下降的箭头了。如果统计结果中没有0值,则箭头就添加完毕了。但如果结果中有0值,有时会发现0值被标上了上升或者下降的箭头,而通常我们需要把0值标上水平的箭头。这时我们需要按下述方法再设置一下。
再选择添加上升、下降箭头的单元格范围。
点击“开始”选项卡中“条件格式”下拉菜单中的“管理规则”。
弹出“条件格式规则管理器”对话框后,点击其中的“编辑规则”选项卡。
点击“编辑规则”选项卡中下图所示的第一个选择大于小于号的下拉框。
点选下拉菜单中的“>”号。
点选“>”号后,在右侧的值输入框中输入“0”。
在值输入框右侧的“类型”下拉菜单中点选“数字”。
按以上方法,把第二行中的各项依次设置成“>=”、“0”、“数字”。
此时第三行会自动被设置成“当<0”。
设置好后,点击“确定”按钮。
再点击一次“条件格式规则管理器”对话框中的“确定”按钮。
这样,所选范围中的0值就会自动添加水平样式的箭头,其他数值会自动添加上升、下降箭头。