您的位置:新葡亰496net > 新葡亰496net > 新葡亰496net:Excel的超链接批量替换及幸免相关超

新葡亰496net:Excel的超链接批量替换及幸免相关超

发布时间:2019-09-23 01:46编辑:新葡亰496net浏览(98)

    问题:平时的工作中,很多时候数据处理都离不开excel这个电子表格软件。用得好,能提高工作效率。用得不好,也就跟手工处理差不多。你的生活中,工作中见过最好或最差劲的excel用法是什么?

    问题:如何让一个Excel表格的信息自动同步到另一个表格中?

    这是《从产品角度学EXCEL》系列第二篇。

    问题:excel有哪些高级技能?

    在excel中,添加的超级链接,如果链接的表或文件,改了名或移动了位置,所有相关的超链接都会失效。可excel没有批量替换超链接的命令,你只能一个一个的重新添加。

    回答:

    回答:

    前言请看:从产品角度学EXCEL-系列0-为什么要关注EXCEL的本质

    回答:

    新葡亰496net 1

    最牛的方法当然是摸透excel的运作规律,从内部对excel进行改造,下来展示给你的excel用法,绝对让你惊掉下巴!

    先说一个简单的吧。

    本文不接受无授权转载,如需转载,请先联系我,非常感谢。

    Excel有哪些高级技能?我也来说说我的看法,纯属个人意见。

    一直以来,很多人都以为excel中超链接无法批量替换。其实是有办法的完成的。在excel2003中利用脚本编辑器可以完成,但2010版中不再支持脚本编辑器。于是我们找到了另一种“可能你从未见过的方法”。

    新葡亰496net 2

    1、如下图,在同一文件夹里有两个excel文件,一个文件名是“城市基础数据.xls”,里面是原始数据,现在需要将原始数据里的全部或部分数据同步到另一个文件“新数据.xls”这个文件里。

    1、EXCEL是怎么工作的

    在序言里,我们大致讲了一下为什么要写这篇文章,以及其整体架构。而从这章开始,我们将简单看一下EXCEL是什么,以及它是怎么工作的。

    从EXCEL的定义来说,它是一款用于处理数据的电子表格软件。当我们打开EXCEL时,看到的一大片方格子,就是excel所处理的主要对象——表格(spreadsheet)

    基本上EXCEL的所有功能都是围绕着表格进行的。有用于存放数据的一个个单元格,有用来统计和计算单元格数据的excel内置函数,有对表格的一列或一行做的筛选与排序,还有各种数据统计与可视化的功能。

    那么,在了解了excel处理的对象是表格及其里面的数据时,你有没有想过,当我们把大批量的数据导入到excel表格时,excel是怎样存储和调用这些数据的呢?它也会是以表格的形式,存储在计算机里,以便我们调用吗?难道开发excel软件的程序员们,在设计时就是在后台画了一个又一个方格子,等着我们按格子填数?

    对上述的猜想,答案是否定的。

    对于开发excel的人来说,计算机并没有那么五彩缤纷。

    那 一个又一个的方格子,是程序员写了很多行代码,把各种功能组合出来以后形成的最终效果。当你回过头来再深入计算机的底层,你会发现每当你操作一个个方格 子,背后有一行行特定的代码解释了你的操作,转换成一串串01010101001给计算机,让它控制各微小电路的开关,最终回馈给你想要的内容。而这些代 码也好,0000111100010101也好,都是纯文本的。

    于是我们可以这样说,excel在运作的过程中,在计算机的后台,是各式纯文本的信息在传输与运作。正因如此,当我们想要了解EXCEL是怎么运作的时候,我们可以从解读这些纯文本信息如何存放、解析里,一窥这程序的运作并了解EXCEL的本质。

    那么回到我们刚刚的问题,数据在excel里是怎样被存储和调用的,这个自然也可以从纯文本的角度来解释。

    我们来看看这个网页里的信息吧:

    《微软Office Open XML文件格式介绍》:

    简单概括一下上面网页的内容,就是:

    office系列的产品,都是用Office Open XML文件格式压缩存储的。

    我们日常所用的一个个独立的xlsx文件,其实是一个个压缩文件。它们把若干个XML格式的纯文本文件压缩在一起,而EXCEL就是读取这些压缩文件的信息,最后给我们展现一个完全图形化的电子表格。

    新葡亰496net:Excel的超链接批量替换及幸免相关超链接都失效的艺术,如何让贰个Excel表格的新闻自动同步到另三个报表中。譬如说,当我们有一个EXCEL的文件,我们可以把它的后缀名从.xlsx改为.zip,右键解压缩后,就会出现一个文件夹。

    新葡亰496net 3新葡亰496net 4

    文件夹里有_rels, docProps及xl三个文件夹。

    在 xl文件夹里存放着这个excel文件的大部分主体内容。如theme文件夹里存放着这个excel的主题设置,sharedStrings里存放着各个 单元格里的信息,而worksheets文件夹里则记录着各个sheet之间如何互相调用。这些,都是以XML的文本格式存放在磁盘里的。

    新葡亰496net 5

    举个例子来说,假如我们在一个工作表里输入以下的字符串:

    新葡亰496net 6

    我们在excel里看到的是一个整齐的表格,但是在xlsharedStrings.xml里,这些字符是以这样的文本形式被存储的:

    新葡亰496net 7

    excel会在后期用各种代码去读取这些xml里的纯文本文件,最后在图形化界面里以一个整齐的电子表格形式,把这些文本展示出来。

    所以实际上,EXCEL对信息的储存,就是把各种各样的信息打上标签以后,以纯文本的形式存储在XML文件里。而当我们需要读取信息时,它通过调取这些文本的关系,把纯文本解码为我们看到的电子表格里的信息

    我们去理解EXCEL怎么工作,其实就是理解这些纯文本信息是什么,它怎么被编译。这样,我们可以更轻松得接触EXCEL的本质,理解它是怎么被设计出来,会有什么功能。

    于是,我们终于带出了这一系列教程的重点:从EXCEL的设计本质出发,去学习EXCEL

    我们将沿着这些纯文本代表的信息,以及其分类,逐个去讲述包括:单元格的信息存储、EXCEL功能的应用、图表的特殊存在。敬请期待。


    扩展阅读:

    1、EXCEL是用C 语言开发的: 

    2、计算机是用0、1来存储数据的相关阅读:

    一个博客上的解释:  
    《计算机科学概论》11版 第1章

    3、wiki上对EXCEL的定义:

    4、微软Office Open XML文件格式介绍: 

    另外,正如上面文章所讲的,office open xml文件格式,给开发excel的人带来了太多便利。我们还可以搜一下用其他语言操作excel的人,是怎么利用这个文件格式的。有兴趣的可以先扩展阅读一下

    还有什么不能做?——细谈在C#中读写Excel系列文章之一: 

    用 XmlReader 读取 Excel 2007 文件 

    微信公众号:

    新葡亰496net 8

    所谓技能,就是技能,本身并没有高级/低级之分。只要能帮我们解决问题,那就一定是有效的技能。

    替换步骤:

    Excel文件确实可以通过压缩软件打包进压缩包中,但是把Excel自身变成压缩文件,多数人应该还没有听说过。

    新葡亰496net 9

    Excel中,每个功能点,都有对应的业务需求和场景,所以,每个功能点都有用,都很高级。如,vlookup和index match,都能查询到数值,但,说不出那个高级,哪个低级。

    1 、把Excel文件名后辍由 xlsx修改为rar。双击打开压缩包。

    01 Excel与压缩包迷之关系

    其实,Office三姐妹在2007(或以上版本)中采用的后缀分别是xlsx、docx、pptx。

    新葡亰496net 10

    这些格式相比旧版本都多了个X,它们本质上是一个压缩包,这样的好处是存储相同容量的信息将占用较小的空间。

    具体到Excel,构成Excel的所有源码文件都在这个压缩包中,因此通过将其后缀更改为.zip或.rar即可提取构成Excel的核心源码文件。

    是不是感觉发现了微软的小秘密。

    我们将Excel修改后缀为zip,解压出内部文件,看看都有什么?3个文件夹1个xml文件。

    这里面是什么鬼东西,能怎么玩?让我们一步步扒光它……

    新葡亰496net 11

    小结Excel变成压缩包的原理图。

    新葡亰496net 12

    2、同时打开这两个文件。如下图,这是“城市基础数据.xls”文件表格里的内容。

    这里,我就介绍几个我认为可能被很多人低估的技能吧。

    新葡亰496net 13

    02 批量导出Excel中的图片

    如果我们的Excel工作表中有很多图片,比如做的员工通讯录,里面含有每一个员工的头像照片,需要将他们批量导入了,一个一个另存为速度就太慢了。

    将其转化为压缩包,然后解压,你会赫然发现,所有图片已经静静地在那里等你。

    Excel等信息存放在解压后的xl文件夹中的media文件夹里,如图是获取Excel中所有图片的步骤。

    新葡亰496net 14

    新葡亰496net 15

    透视表

    很多人统计分析数据还在用各种各样复杂的公式,从而低估了透视表的强大功能。

    透视表,简简单单几个操作,基本上就能搞定日常80%的报表。

    新葡亰496net 16

    多张工作表,那也不在话下

    不仅汇总,还能拆分数据

    新葡亰496net 17

    画个图表,那也是不在话下

    新葡亰496net 18

    如此的透视表,你低估了他的功能了吗?

    • 数据分列

    相信很多表亲都受到过格式的困扰。日期格式不统一?数字和文本混合,Excel傻傻分不清?拆分单元格内容?其实,分列轻轻松松就搞定了。

    新葡亰496net 19

    • 列表格式

    新葡亰496net 20

    都想着如何统计分析,其实,你是否想到过,统计分析的困难度,很大是取决于数据源的格式。而,日常习惯中,我们往往会在数据源中添加一系列看似很酷的功能,如,合并单元格,多维数据等。殊不知,这些都加深了统计分析的难度。

    所以,建议大家对源数据的保存,都用列表的形式。

    这里仅作抛砖引玉,欢迎大家讨论。

    2、依次打开xl / worksheets 文件夹,用记事本方法打开sheet1.xml文件。替换3日为4日。(一定要根据提示点保存)

    新葡亰496net,03 破解工作表加密

    如图第二张表「销售记录」是使用密码保护的工作表。可是自己设置的密码却忘记了,该怎么办?

    新葡亰496net 21

    这就叫做搬起石头砸自己的脚吧。

    ①首先修改Excel的后缀名为「.zip」或者「.rar」,是压缩文件的格式就行。

    注意:Excel会提示你修改后缀名之后会导致文件不可用,不要理它,点击「是」。

    新葡亰496net 22

    ②用压缩软件打开刚才创建的压缩包,从压缩包中\xl\worksheets\路径下找到加密的工作表。因为我们只有sheet2为加密工作表,所以打开这个工作表即可。

    通常,为了防止乱搞,把原始Excel文件搞乱,我建议大家把此文件复制到桌面之后再进行下一步操作。

    新葡亰496net 23

    ③假设你已经将压缩包中的Sheet2复制到桌面了,那么用打开它, ,输入“pro”查找找到加密代码,找到之后将<>中的那一坨连同<>符号一起删掉,然后保存。

    <sheetProtection algorithmName="SHA-512" hashValue="0botjAbUl3c8nnRthv/9xqEJw1FA6ErQJGpCGWzty5mQjyr89b11v9ffpnHIJOm3mk9Rv3YgnF10xQ3FEjTbvQ==" saltValue="5wf6E/o5M RHJTelz8A5hg==" spinCount="100000" sheet="1" objects="1" scenarios="1"/>

    新葡亰496net 24

    ④将桌面的上已经删除密码保护的sheet2直接拖到压缩的文件的\xl\worksheets\路径下,替换到原来的sheet2。

    新葡亰496net 25

    ⑤把zip文件改回.xlsx文件,然后大摇大摆去修改工作表吧,因为excel工作表保护密码消失了。

    新葡亰496net 26

    3、“新数据.xls”打开后,在其中一个工作表A1单元格输入“=”号,然后用鼠标选择“城市基础数据.xls”这个文件,并点击A1单元格,然后回车确定,在“新数据.xls”A1单元格就会生成公式“=[城市基础数据.xls]Sheet1!$A$1”。

    更多实用小技巧,记得关注我哦!

    回答:

    不要一说Excel高级技能就说VBA,VBA的确很好,但是,如果一项技能不能为绝大多数普通用户使用,即使高级,也不应该被誉为“最高”。

    相比较而言,我更加推荐Excel2016的新功能Power Query、Power Pivot等Power系列(Excel2010或Excel2013可到微软官方下载相应的插件)——这几项技能才是真“高级”,不仅功能十分强大,而且,Excel新手也能轻松掌握!

    以下从使用评价、功能概要、应用案例、学习曲线对比、学习材料等五个方面进行说明。

    新葡亰496net 27

    04 批量修改工作表名称

    一个工作簿中存放了一整年的销售记录表,有一个汇总表以及1-12月的销售明细。现在想把工作表名称中“2008年”字样统一改为“2010年”,同样使用压缩文件的方式可以快速搞定。

    新葡亰496net 28

    ①将Excel文件的扩展名修改为.zip

    ②双击zip压缩包→xl文件夹,找到workbook.xml文件,并将workbook.xml复制到桌面(注意不要解压压缩文件)。

    新葡亰496net 29

    ③以记事本的方式打开桌面上的workbook.xml文件,单击菜单命令“编辑”→“替代”,在弹出的“替换”对话框中,“查找内容”文本框输入:2008年,“替换为”文本框输入:2010年,单击“全部替换“按钮,关闭”替换“对话框;

    新葡亰496net 30

    ④保存文件,并将其粘贴回压缩文件夹中,会提示“此位置已包含同名文件”,点击复制与替换。

    新葡亰496net 31

    ⑤将zip文件扩展名再改回原来的.xlsx,然后打开此Excel文件,发现所有的工作表名称都已经改变了。

    新葡亰496net 32

    新葡亰496net 33

    首先,看一下用过的人对这几个新功能的评价

    注:明明是表名是4日,为什么显示和点击 Sheet1?

    05 批量修改批注

    通过摸索,星爷发现,Excel转化成压缩包之后,其中xl文件夹是放置Excel中各种元素的地方,比如Excel中插入了图片,在xl文件夹中就会生成一个media文件夹;在Excel单元格中插入了批注,xl文件夹中就会生成一个comments1.xml文件,用来存放批注。

    因此,在Excel文件中难以批量操作的“批注”,就可以利用压缩包方法解决。

    比如批量修改批注。

    双击zip压缩文件,双击打开 xl 文件夹,找到名为comments1.xml的文件,将其复制到桌面。

    新葡亰496net 34

    右键单击comments1.xml文件,在右键菜单中依次单击——。在记事本中打开后,所有Excel中批注的内容都在这个文件中了,在这里我们可以方便的使用查找替换进行批量性该,比如:删掉所有批注中的“学费”两字。

    如图可以采用替换的方式,将学费替换为空值。

    新葡亰496net 35

    完成之后保存comments1.xml文件,并将其粘贴回压缩文件包中。

    最后zip文件扩展名再改回原来的.xlsx,然后打开此Excel文件,工作表中所有地方的批注都进行了相应的改变。

    知道了这个原理之后,我们应该还能解锁更多的玩法,等着你去发现。

    4、删除公式中的绝对引用符号“$”,公式为“=[城市基础数据.xls]Sheet1!A1”,然后向左和向下复制公式。(当然公式也完全可以用键盘输入)

    1、逆天给力的Power Query

    新葡亰496net 36

    在这里显示是根据工作表插入先后的sheet顺序号。如第1次插入的就是sheet1,以此类推。在工作表标签上右键单击“查看代码”,在新打开窗口右上位置可以看到对应关系。

    今日互动

    关于Excel与压缩包,你还有哪些玩法?

    ·The End·

    作者:安伟星,微软Office认证大师,领英中国专栏作者,《玩转职场Excel》图书作者

    回答:

    17个数据透视表和SQL实用动画:

    1、多角度分析数据

    新葡亰496net 37

    2、更改值的汇总依据

    新葡亰496net 38

    3、排序让数据更加直观

    新葡亰496net 39

    4、原来手工也能排序

    新葡亰496net 40

    5、对销售额进行排名

    新葡亰496net 41

    6、筛选销售额前5名的客户

    新葡亰496net 42

    7、让更改的数据源随时刷新

    新葡亰496net 43

    8、插入表格,让数据源“动”起来

    新葡亰496net 44

    9、日期组合真奇妙

    新葡亰496net 45

    10、手工组合实现客户分级

    新葡亰496net 46

    11、善借辅助列,实现客户实际销售额分析

    新葡亰496net 47

    12、利用数据透视图让分析更直观

    新葡亰496net 48

    13、查询所有记录

    新葡亰496net 49

    14、查询不重复客户

    新葡亰496net 50

    15、查询符合条件的客户

    新葡亰496net 51

    16、统计产品购买金额

    新葡亰496net 52

    17、统计批发跟零售金额

    新葡亰496net 53

    回答:

    最开始使用Excel的时候,什么都不懂。有谁不是从最开始的手动输入进阶过来的?

    所以我刚上班的时候用Excel也是low爆了,快捷键只知道一个Ctrl C和Ctrl V。尤其是在录入身份证信息的时候,不管怎么弄都是无法显示完整的数字,无奈只能求助隔壁的小姐姐。

    新葡亰496net 54

    现在终于知道身份证号要怎么设置,才能够完整显示了。这里也给大家附上解决方案:

    ①先选中单元格,再右键点击“设置单元格格式”,选择“文本”。

    新葡亰496net 55

    ②然后就可以输入身份证号,并在数字前插入英文单引号,再按下回车键即可。

    新葡亰496net 56

    不过等遇到的问题多了,百度查找答案后,也就慢慢积累了一些Excel技巧。例如以下这几个我就觉得挺好用!

    1、ctrl E(快速填充)

    拆分单元格信息

    新葡亰496net 57

    手机号码分段显示和加密

    新葡亰496net 58

    手机号码分段显示和加密

    新葡亰496net 59

    快速提取身份证号码中的生日

    新葡亰496net 60

    2、[F4]键

    轻松插入多个空白行

    新葡亰496net 61

    高效合并多个单元格

    新葡亰496net 62

    为多个单元格设置填充颜色

    新葡亰496net 63

    许多小伙伴都认为Excel函数公式很高级,会用Excel函数公式就是牛。虽然我觉得Excel技巧里没有最牛,只有更牛。但是不得不承认,Excel函数公式用对了确实可以大幅度提高工作效率。例如以下这几个函数格式:

    1、提取出生年月

    =TEXT(MID(C3,7,8),"0-00-00")

    新葡亰496net 64

    2、计算及格人数

    =COUNTIF(B2:B13,">=60")

    新葡亰496net 65

    最后再给大家一个经验之谈:如果想要学习怎么用Excel,光看书看教程都是纸上谈兵,尤其是公式基本上看过不用的话很快就忘记。建议是遇到问题后,先百度搜一下,然后边学边用,这样可以加深学习印象哦。

    新葡亰496net 66

    回答:

    最牛的是用Excel做商业建模,比如投资决策模型,整本书看完完全不能理解的节奏。这个时候excel工具已经是其次了,关键是模型背后的思考和解决问题的方式。

    次一点的是Power Query 和VBA ,前者用在数据处理,后者重在应对复杂业务逻辑。

    再次一点是超级链接和函数,比如跨文件取数,跨表页数据更新。

    更次一点大概就是数据透视和图表展示。

    LOW的用法是一个单元格一个单元格敲公式,改公式,不知道用替换。

    更LOW的用法是,看到excel 里数字前的绿色三角一个个点掉转换成数字,

    最最LOW的是ctrl c 和 ctrl v ,重复操作超过五次,就应该考虑用便捷方式了。

    自从接触财务以后,反正我已经信了EXCEL是最好的BI工具。

    回答:

    新葡亰496net 67

    2、被誉为引爆Excel数据分析、20年来最大革新的Power Pivot

    新葡亰496net 68

    新葡亰496net 69

    最牛

    1 小试刘刀。老师在数理统计上用Excel的函数做概率实验,只是几个函数,但是对于普通的职场小白这可是神技能啊。

    新葡亰496net 70

    2 大神吧

    函数 vba,分分钟get前面那个当做计算器用过的人。

    新葡亰496net 71

    3 骨灰级

    绘画用什么,ps,不不不,国外大神告诉你用什么,吓得我瑟瑟发抖。

    新葡亰496net 72

    新葡亰496net 73

    5、复制后就得到与“城市基础数据.xls”某工作表完全一样的数据,基础表数据发生变化,目标表也随之变化。

    3、可视化地图只需3步的神器Power Map

    新葡亰496net 74

    3、把文件名后辍由rar再改回xlsx。

    菜鸟

    1.哎,那个表格在哪里啊!!!!!!

    2.表格就是放东西的,玩什么玩。。

    新葡亰496net 75

    回答:

    Excel中最牛的用法,一定那些可以轻松用于解决实际工作中的大问题的功能,而不是那些只是看起来很牛,而学不会或用不来的东西,因为那个跟你没有关系,或者说对你来说没有意义。因此,我现在非常推荐大家在学好Excel基础功能的情况下,加紧对Excel2016新功能(Excel2010或Excel2013可到微软官方下载相应的插件)的学习,不仅超级强大,而且十分简单易学。举例如下:

    新葡亰496net 76

    4、横空出世的逆天神器Power BI

    新葡亰496net 77

    设置完成。重新打开文件,发现链接已可使用。

    Power Query秒解顾客最后消费记录神难题

    小勤:大海,为提高顾客服务体验,公司现在要求除了将顾客的所有消费记录提出来外,还要求将顾客的最后一次消费记录提取出来,发给现场的销售人员,方便他们提供更好的服务。

    大海:鹅……厉害了噢。开始这么高级的服务了。

    小勤:是啊,但我就惨了,每天数据都在增加,每天出报表……

    大海:这个问题啊,以前有些大神专门研究过,还写过不少神公式,给你看看:

    新葡亰496net 78

    还有最后消费金额的公式:

    新葡亰496net 79

    小勤:晕啊。这么复杂。

    大海:真说起来,这个公式的复杂程度其实不算个大问题,更麻烦的是这个公式涉及数据量大的时候,计算起来会很卡。

    小勤:那怎么办好呢?

    大海:现在有PowerQuery,秒解,简单到没朋友,看啊。

    Step-1:获取数据

    新葡亰496net 80

    Step-2:反转行

    新葡亰496net 81

    Step-3:对姓名列删除重复项

    新葡亰496net 82

    Step-4:再次反转行(如不需要保持原数据顺序,此步骤可省略)

    新葡亰496net 83

    Step-5:数据上载

    新葡亰496net 84

    小勤:666!只要点2下就好了!

    大海:你说呢?以后新订单数据进来就刷一下。

    小勤:嗯。这太好了,要不能这样的话,公司要真靠数据来提升服务水平可太难了。

    大海:的确,服务的提升必须要有最新的数据来保证,这就是快速(敏捷)数据分析的价值啊。

    6、需要说明的是上述同步变化是在两张表同时打开的情况下,若打开目标表之前没有打开基础表,会出现如下提示窗口,点击“更新”即可完成数据同步。

    第二、Power系列新功能到底是什么?

    Power系列分别负责数据分析全过程从数据接入整理→统计分析→图表展现等的不同重点工作,相应功能侧重点如下所示:

    • Power Query就是用来做数据查询、转换的,还记得Excel里的数据导入吗?你可以理解为它的升级版;

    • Power Pivot是用来做数据建模和分析的,还记得Excel里的数据透视吗?你可以理解为它的超级版;

    • Power View是用来做数据展示的,还记得Excel里的图表吗?你可以理解为它的整合版;

    • Power Map是专门用来做数据的地图化展示的,这是Excel里原来没有的。

    • Power BI可以理解为微软将这四块功能做了个大整合,然后推出的一个独立的工具。

    新葡亰496net 85

    新葡亰496net 86

    Power Query:2步搞定数据转换神难题

    小勤:大海,刚有个同事在问我这种情况怎么办!公式怎么写啊?

    新葡亰496net 87

    大海:为什么又要公式?

    小勤:还有大批量数据都需要做这样的迁移啊。公式不是更加自动一点吗?

    大海:PowerQuery不是更加自动吗?第一反应为什么不是用Power Query?

    小勤:鹅……对哦!Power Query是可以整合、转换数据,并可以自动刷新的。

    大海:嗯。就这个问题来说,去掉数据获取(导入)、数据上载头尾两个简单步骤后,用Power Query只要2步就搞定了:逆透视后再透视。

    Step-0:数据获取

    新葡亰496net 88

    Step-1:逆透视

    新葡亰496net 89

    Step-2:透视

    新葡亰496net 90

    最后,数据上载

    新葡亰496net 91

    小勤:真是太简单了!那如果写公式的话呢?

    大海:公式的话可以看作多条件求和或多行列数据匹配的情况考虑,比如用sumifs、sumproduct、if index match等组合的方式,但都比较复杂,而且有个前提:业务经理、月份、存货分类等行列标题的内容要先生成,即结果表的框架先生成,只用公式读取其中的数值内容。如果要连行列标题(业务经理、月份、存货分类)都要靠公式整出来的话,基本没救。

    小勤:好吧。有合适的工具,掌握有效的技能,真是事半功倍啊!

    新葡亰496net 92

    第三,举2个应用例子

    兰色说:兰色是03年开始接触excel的,当年很多“逆天”的excel技巧,在今天看来已是很初级的技巧,这需要感谢一代又代excel高手们的无私分享。

    更多Power Query精彩实战案例

    可以用Power Query来轻松迅速解决的Excel问题举不胜举,我曾经分享过一系列的实战案例,供参考:

    新葡亰496net 93


    新葡亰496net 94

    回答:

    1、批量汇总Excel多工作簿多工作表数据只需4步

    step01-从文件夹获取数据(如果数据仅在1个工作簿内,选择“从工作簿”)

    新葡亰496net 95

    新葡亰496net 96

    step02-用Excel.Workbook函数解析工作簿多表信息(如果所有表都在1个工作簿里,此步骤可以省略)

    新葡亰496net 97

    step03-展开所有工作簿的所有工作表信息

    新葡亰496net 98

    step04-展开所有工作表的数据明细(如果希望只导入其中部分表,可以在此步骤进行筛选)

    新葡亰496net 99

    通过以上简单的4个步骤,所有工作簿的所有工作表数据将汇总到一起,如下图所示:

    新葡亰496net 100

    如果还需要对其中的数据进行其他的操作,那么可以继续在Power Query里完成,这样,以后在新的数据出现时,可以直接在结果表里一键刷新,就象以前需要用VBA导入时点个按钮一样方便,完全不需要再重复操作!

    我是大海,微软认证Excel专家,企业签约Power BI顾问

    可以使用Excel函数:Vlookup

    2、多表数据联合分析,用Power Pivot建模只需拉根线

    以订单表、订单明细表以及产品主数据三表关联分析为例。

    新葡亰496net 101

    Step01-依次将数据添加到数据模型

    新葡亰496net 102

    加载完毕后,Power Pivot中数据如下:

    新葡亰496net 103

    Step02-切换到关系图视图

    点击“关系视图”,看到3个表的内容分别显示在3个不同的框框里,用鼠标按住这些框框的顶部名称区域就可以按需要拖放到不同位置。

    新葡亰496net 104

    Step03-构建表间关系

    订单表、订单明细表、产品表之间的关系是:订单表里的每个订单对应订单明细表里多个订单(产品)项目,订单明细里的产品可以从产品表里获取更详细的相关信息。

    新葡亰496net 105

    结果如下:

    新葡亰496net 106

    按同样的方法还可以建立订单明细表和产品表之间的关系,最后结果如下:

    新葡亰496net 107

    这样,3个表之间的关系就建好了,后续就可以直接从各个表里拖拽需要的信息进行数据透视等分析,如下所示:

    新葡亰496net 108

    新葡亰496net 109

    新葡亰496net 110

    比如,要分析各种产品类别的销量:

    新葡亰496net 111

    让我们一起学习,共同进步!

    回答:

    Excel简直就是神器,实在太强大了,不仅可用来办公,还能制作游戏,甚至是进行画作创作。下边是无意中发现的,一个日本老人用Excel来画画,其所创作的画作多为日式传统风格的山水风景图。至于为何选择Excel表格类来进行创作,老人表示“成本是首要原因,专业的绘画应用通常价格昂贵并且平台门槛较高,而Excel只需要一台电脑即可。此外,它简易的操作可以让上色更为顺手”。下图为老人的画作:新葡亰496net 112新葡亰496net 113新葡亰496net 114新葡亰496net 115

    回答:

    举例:同步A车型物料表 表1的单价到B车型的物料表 表2。(注意事项表1作为基础数据,A列料号必须是唯一的,否则公式出错,有重复的话公式选取最上面的一个数据同步,如下面5-8行)。

    第四,对比一下Excel函数、VBA以及Power Query的学习曲线

    以Power Query为例对比一下Power系列功能的学习曲线:

    新葡亰496net 116

    可以看出,Power系列功能可以在非常短的时间内掌握其大多数的使用要点,并学以致用——应用到实际工作中去解决问题,这个过程比学习Excel函数还短,收效更大。

    先说一下我见过的最low的Excel的用法。

    学校收《报考指南》书费和体检费,我收起了学生的费用去交给负责这件事的副主任。我拿着钱进屋看到副主任的桌子上,凌乱的放着用纸包起来的钱。眼睛盯着电脑,手里拿着手机,走近一看,他居然在用手机上的计算器算各班的应交费用。我当时的表情是这样的,

    新葡亰496net 117

    能把Excel用到这种程度我也真是服服的了。

    新葡亰496net 118

    第五,更多的应用参考案例

    Excel神技能Power系列的应用远不止于上面两个简单例子,还有一系列原来Excel里必须通过非常复杂难懂的神长公式、VBA代码或者SQL才能解决甚至难以解决的问题,通过Power系列功能可以非常轻松的解决掉。

    至于最牛逼的,我没有见过,我觉得我用的就比较牛逼。

    我负责学校的成绩分析,成绩出来后我可以在半个小时内,生成全校一千多学生,一百位老师的,各种成绩数据。包含每位老师的单进线、双进线、完成率、折合完成率、综合完成率,老师在全校的排名,在组内的排名,在班内的排名,各个班级的排名,各个教研组的排名。

    我还可以把,全年级的课程表放到一个Excel文件内,实现班级成绩表查询,每位老师的个人成绩表查询,每个教研组的成绩表查询。

    好吧,这些用法也算不上高大上,但是作为一个一线老师能够用到这样,我觉得可以啦。

    我是辛哥,如果您觉得对,请您关注我,如有不当,欢迎评论区留言指教!

    新葡亰496net 119

    回答:

    最牛的:

    大三的时候参加过一个叫做企业竞争模拟大赛的活动,见到别的团队用的Excel预测表,可以把当期的财务数据录取进去,输入一些本期的决策数据,自动规划求解出一个最优解,更牛逼的是可以预测出之后八期的结果。里面用到的公式啥的就不说了,那都已经跟小儿科了,用到的数据分析工具,规划求解之类的,还有vba。简直就是一个独立的程序。

    工作以后见到同事用Excel链接服务器,每天自动更新公司运营数据,生成图表,全自动哦,至今也没搞懂他那个表是怎么写的。

    最low的,这个好像不太好说。比较常见的一列数据有数值有文本,有的有空格有的没空格,最烦遇到这样的数据,压根没法批量处理

    回答:

    各有各的牛:

    有图表牛的,南丁格尔玫瑰图。

    有VBA牛的,表格合并,拆分,格式化。

    有函数牛的:VLOOKUP,SUMPRODUCT,SUMIFS,INDEX,MATCH等等,加上数组函数,没有做不到,只有想不到。

    LOW的,

    第一low,合并单元格。

    第二low,数据不规范,空格、文本数字混合,日期小数点等等。

    方法:

    1、如Power Query实战案例

    新葡亰496net 120

    1.在B车型物料表的E2坐标单元格输入 等号-右上角框线处选择Vlookup函数(用过一次后就不需要选)-确定。

    2、如Power Pivot应用案例

    新葡亰496net 121


    新葡亰496net 122


    新葡亰496net 123

    我是大海,微软认证Excel专家,企业签约Power BI顾问

    2.以B表料号链接A表料号的单价,在截图里面的弹窗1位置点击B表C2单元格-2位置框选A表A-F列的列标-位置3处填5-位置4填0-点确定。
    新葡亰496net 124

    让我们一起学习,共同进步!

    回答:

    我个人在数据方面工作也有些年了,对excel来说,用的还是比较多,虽然有些时候,会用其它的分析工具,比如:python、R等。但是日常工作的绝大多数都是excel。excel的高级技能在这里就谈一下个人的一些感受。

    3.选中B表的E2单元格的右下角下拉公式。

    第一、高级筛选,我觉得可以算得上一个高级技能,突破常规的单列筛选的限制,可筛选多列,每列设置一定的条件。

    高级筛选,在excel的功能栏里没有筛选那么明显,可以看得出来,这个功能本身使用的不是那么频繁。但是确实还是挺不错的,其实在平常的工作中,相信部分的人也和我一样,喜欢用筛选这个功能,而不是高级筛选这个功能,每列单独筛选。

    新葡亰496net 125

    新葡亰496net 126

    第二、就是我们所说的VBA功能,VBA可以使得我们根据我们自己的逻辑对电子表格进行处理。

    工作中我会遇到拆分电子表格的事,经常会用到VBA去处理,这个时候VBA的做点就出来了,虽然我们可以通过一些其它的方法去做实现拆分的目标,但VBA在你一次逻辑实现以后,可以做到后续的拆分变得相当的快,运行一下,等一会结果就出来了。上学的时候老师还要求用VBA做一个简单的系统,当然VBA的学习有一定的难度。

    新葡亰496net 127

    以上是我列举的两个在工作中觉得相对来说是比较高级的技能,当然excel是相当强大,可以说90%以上的数据分析工具excel都可以胜任。欢迎在评论中发表不同的观点。

    回答:

    新葡亰496net 128

    教程:

    回答:

    EXCEL的功能主要体现在3个方面:一是界面中的各种操作,二是各类函数的功能,三是通过VBA实现复杂的个性需要。

    从EXCEL的数据处理效率上来说,比如对于10万行的数据进行处理,VBA的处理速度最慢,各类内置函数的处理速度次之,EXCEL的各类常规处理操作则最快(如筛选)。但从功能实现上来说,VBA能实现的功能最多,各类内置函数次之,各界面的功能操作按钮则只能实现那些常用操作。

    使用EXCEL,主要根据自己的需求来学习,一些很少用到的东西,知道就行了,需要用时再去查看帮助也来得及。各界面的功能按钮功能,是必须要知道和掌握的,即使不熟悉一些按钮命令的用法,也要知道那些按钮有什么作用,以备需要用时去查询帮助。常用的函数是必须要掌握的,至于VBA,一般人很少用得上,能掌握最好,掌握不了也没什么遗憾。

    回答:

    我能说有人用Excel单元格作为像素点,然后使用Excel作画吗~感觉这是最原始的功能,也是最高大上的功能……

    我不是Excel的死忠粉,所以一直没研究过这个是怎么完成的~很佩服作画的这些人。前几天看到有人用Excel生成二维码和条形码的视频,这个需要加载控件,不过这个确实是个好功能,不需要人们再在网页上寻找相关功能插件了,而且还可以当公式的批量转换。

    其实数据透视也算Excel比较高级点的功能吧,毕竟日常工作知道使用数据透视的还是少数,需要数据统计工作的小童鞋,可以研究一下~工作中常使用的图表模版,报表格式模版,这些模版的定做也是必不可少的技能。

    当然我还想提的是Excel的VBA编程,也许会点编程让大家看起来更高大上~

    结果如下图。

    新葡亰496net 129

    同理如果B表只有料号一列有数据,其他数据也可以用Vlookup同步更新。

    回答:

    可以参考这篇文章在excel和wps中使用SUMIFS函数对满足多重条件的单元格求和

    哎,答案文不对题,我本来是回答另外一个问题的。

    本文由新葡亰496net发布于新葡亰496net,转载请注明出处:新葡亰496net:Excel的超链接批量替换及幸免相关超

    关键词: