您的位置:新葡亰496net > 新葡亰496net > 新葡亰496net:您见过的excel最牛和最low用法是怎么

新葡亰496net:您见过的excel最牛和最low用法是怎么

发布时间:2019-11-10 05:20编辑:新葡亰496net浏览(96)

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

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

      在第二篇文章中我已经向大家详细介绍了Excel 2007以后版本的文件的OpenXML结构。所谓的OpenXML结构从本质上来讲其实就是一个ZIP压缩包,所有基于OpenXML数据结构的文件都一样。这样文件的结构是开放的,你不需要借助于任何第三方的组件就可以读取文件中的数据,或者修改文件的内容。而且所有资源文件诸如图片和多媒体文件都是被编译成一个固定的格式存放在压缩包中,而不是传统形式上的流。另外,以压缩包的形式存储数据也使得文件所占用的空间更小。

    问题:如何创建Excel工作表目录?

    Excel的主要处理对象是表格,就是打开Excel文件时看到的一个个方格子。

    回答:

    新葡亰496net 1

      这些都是它的优点。其实,微软在.NET中已经提供了相应的类库来帮助我们操作OpenXML文件,就像我在第二篇文章中向大家介绍的一样,微软把OpenXML压缩包称之为一个Package,在Package中的文件称作Parts,不同的Parts有自己的Content Type。Content Type可以描述任何内容,如application XML,user XML,images,sounds,videos,或者其它二进制内容。各个Part之间通过一个被称之为relationship的part连接起来,这个part其实也是一种特殊的XML文件,后缀为".rels",在Package中可以找到它。下图说明了一个Package中各个Part之间的关系,

    回答:

    基本上Excel的所有功能都是围绕着表格(也就是这些方格子)进行的。

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

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

    新葡亰496net 2

    Excel中含有多个工作表,如何才能快速定位到具体的某一个工作表呢?我们就可以通过创建目录的方法来解决。下面来看看具体如何创建目录的方法。

    表格主要包括以下几部分:

    新葡亰496net 3

    替换步骤:

      虽然.NET中提供的Package类可以帮助我们非常方便地解析Excel包,但是也有许多的局限性,有的时候你不得不使用一些方法来自己读取包中的内容,或者尝试去修改包中的数据。

    1、添加超链接

    如果表格数量不是太多的话,可以通过创建“目录”工作表,并手动添加超链接的方法,来制作目录。

    新葡亰496net 4

    (1)存放数据的单元格;

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

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

      1. 将Package作为一个标准的zip文件进行解压。

    2、使用名称管理器

    选中B1单元格后,点击“公式—名称管理器”,在引用位置中添加公式:=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW());

    接着,在B1单元格中输入:=IFERROR(HYPERLINK(目录&"!A1",MID(目录,FIND("]",目录) 1,99)),""),下拉菜单完成目录填充。

    新葡亰496net 5

    (2)用于计算的内置函数;

    01 Excel与压缩包迷之关系

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

    新葡亰496net 6

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

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

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

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

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

    新葡亰496net 7

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

    新葡亰496net 8

    新葡亰496net 9

      2. 找到Package中你想要读取的parts。有许多的方法可以帮助你找到这些parts,你可以解析relationship,也可以直接通过path来定位到某一个文件,不过不太确定path在将来是否会有变化。或者还可以通过part的content type来找到它。

    3、查找功能

    选中所有工作表后,在表格中插入一栏,输入“ml”;然后打开查找功能(Ctrl F),将查找范围选择“工作薄”,勾选“单元格匹配”,点击查找全部,就可以快速转换工作表了。

    新葡亰496net 10

    以上就是几个Excel创建工作表的方法了,希望对大家有所帮助。

    回答:

    采用Excel2016的超级强大新功能Power Query创建Excel工作表目录是迄今为止最简单好用的解决方案(2010或2013可到微软官方下载插件)。

    创建Excel工作表目录,以前可用手动添加超链接、或兼容性检查生成错误链接然后调整、又或者写VBA代码等方法来实现,但是,这些方法其实都比较麻烦。

    而使用Power Query,不仅操作非常简单,而且也可以在工作簿中的表格出现变化时直接刷新得到最新目录。实现步骤如下:

    (3)对表格的行或列做的排序和筛选;

    02 批量导出Excel中的图片

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

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

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

    新葡亰496net 11

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

      3. 读取part中的内容。如果parts是以XML的形式存放的,通过标准的XML类库就可以非常方便地读取到数据。如果是其它形式,如图片、声音或视频文件则也可以通过相应的方法来获取到内容。

    1、新建查询-从文件-从工作簿

    新葡亰496net 12

    (4)数据统计和可视化。

    03 破解工作表加密

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

    新葡亰496net 13

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

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

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

    新葡亰496net 14

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

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

    新葡亰496net 15

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

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

    新葡亰496net 16

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

    新葡亰496net 17

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

    新葡亰496net 18

    新葡亰496net 19

      另一方面,你也可以创建一个基于OpenXML的文件(如采用非COM组件的形式创建一个Excel文件),

    2、编辑接入的工作簿数据

    新葡亰496net 20

    1、数据在Excel中是怎么存储和调用的?

    04 批量修改工作表名称

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

    新葡亰496net 21

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

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

    新葡亰496net 22

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

    新葡亰496net 23

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

    新葡亰496net 24

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

    新葡亰496net 25

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

      1. 创建或复制所有必须的parts。通过标准XML类库来创建这些基于XML数据格式的parts,或者从其它package中复制这些parts,或者采用任何其它你所熟悉的方法。

    3、删掉除工作表名称外的其他列

    新葡亰496net 26

    office系列的产品都是用office open xml文件格式压缩存储的,我们看到xlsx文件,其实是一个压缩文件。它们把若干个xml格式的纯文本压缩在一起,而Excel就是读取的这些压缩文件的信息,最后给我们呈现一个图形化的表格。

    05 批量修改批注

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

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

    比如批量修改批注。

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

    新葡亰496net 27

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

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

    新葡亰496net 28

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

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

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

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

      2. 创建relationships部分。也就是创建一个后缀为".rels"的特殊XML文件。

    4、工作表名称数据返回Excel

    新葡亰496net 29

    新葡亰496net 30

    今日互动

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

    ·The End·

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

    回答:

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

    1、多角度分析数据

    新葡亰496net 31

    2、更改值的汇总依据

    新葡亰496net 32

    3、排序让数据更加直观

    新葡亰496net 33

    4、原来手工也能排序

    新葡亰496net 34

    5、对销售额进行排名

    新葡亰496net 35

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

    新葡亰496net 36

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

    新葡亰496net 37

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

    新葡亰496net 38

    9、日期组合真奇妙

    新葡亰496net 39

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

    新葡亰496net 40

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

    新葡亰496net 41

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

    新葡亰496net 42

    13、查询所有记录

    新葡亰496net 43

    14、查询不重复客户

    新葡亰496net 44

    15、查询符合条件的客户

    新葡亰496net 45

    16、统计产品购买金额

    新葡亰496net 46

    17、统计批发跟零售金额

    新葡亰496net 47

    回答:

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

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

    新葡亰496net 48

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

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

    新葡亰496net 49

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

    新葡亰496net 50

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

    1、ctrl E(快速填充)

    拆分单元格信息

    新葡亰496net 51

    手机号码分段显示和加密

    新葡亰496net 52

    手机号码分段显示和加密

    新葡亰496net 53

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

    新葡亰496net 54

    2、[F4]键

    轻松插入多个空白行

    新葡亰496net 55

    高效合并多个单元格

    新葡亰496net 56

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

    新葡亰496net 57

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

    1、提取出生年月

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

    新葡亰496net 58

    2、计算及格人数

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

    新葡亰496net 59

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

    新葡亰496net 60

    回答:

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

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

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

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

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

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

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

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

    回答:

    新葡亰496net 61

      3. 将整个package压缩成一个zip包,然后将文件的后缀修改成需要的类型(如docx,xlsx,或者pptx等)。只要package中的结构符合要求,修改之后的文件可以直接被打开。当然,该过程可以在完全没有安装office的机器上完成(例如服务器),然后分发给需要的地方使用。

    5、输入公式创建工作表目录链接

    新葡亰496net 62

    简单的5步操作就完成了Excel工作表目录的创建,不需要写任何代码,只要鼠标操作加一个简单的函数即可,非常简单,而且随着工作表的增减,只需要在链接目录表里右键刷新就得到最新的目录信息!


    新葡亰496net 63

    新葡亰496net 64

    新葡亰496net 65

    上图中“打赏数据.xlsx”文件是我们平常用到的Excel文件,在文件名后边加上“.zip”,变为“打赏数据.xlsx.zip”,解压缩这个zip文件得到一个xml文件和三个文件夹。

    最牛

    新葡亰496net:您见过的excel最牛和最low用法是怎么样,Excel的超链接批量替换及幸免相关超链接都失效的主意。1 小试刘刀。老师在数理统计上用Excel的函数做概率实验,只是几个函数,但是对于普通的职场小白这可是神技能啊。

    新葡亰496net 66

    2 大神吧

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

    新葡亰496net 67

    3 骨灰级

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

    新葡亰496net 68

    新葡亰496net 69

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

      所有这一切包括packages,parts,content types,以及relationships都被称之为OpenXML文档,微软将这个叫做Open Packaging Conventions。

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

    新葡亰496net 70

    菜鸟

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

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

    新葡亰496net 71

    回答:

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

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

      有关Excel OpenXML的内部结构以及一些比较重要的节点含义在前一篇文章中已经做了一些介绍,除了直接使用.NET类库中的Package类之外,我们当然也可以使用其它的第三方类库或者自己编写代码读取包中的内容,这里有一个.NET的开源类库专门用来操作zip压缩文件。

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

    回答:

    答:以下方法适合所有版本Excel

    方法1:手工添加

    方法2:利用公式(快)

    方法3:利用编程(更快)

    目标:为 Shee2-Sheet6 添加目录,"目录"工作表已事先准备好。

    新葡亰496net 72

    效果:

    新葡亰496net 73

    • 手工添加

    在“目录”表的A2单元格右键,选择"超链接",打开对话框,选好单元格地址和工作表名即可。

    新葡亰496net 74

    • 利用公式

    按Ctrl F3,打开名称对话框,新建一个名称,照图输入内容。

    输入名字:Name

    引用位置:=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1)) 1,99)&T(NOW())

    新葡亰496net 75

    回到"目录"工作表,在单元格A2中输入公式,下拉至空白。

    =IFERROR(HYPERLINK("#"&INDEX(Name,ROW(A2))&"!A1",INDEX(Name,ROW(A2))),"")

    新葡亰496net 76

    • 利用编程

    按ALT F11组合键,打开VB窗口,执行"插入"-"模块",复制下面代码进去,按F5运行一步生成。

    Sub AddHyperlinks()

    Dim i As Integer

    Dim Cell As Range

    Set Cell = Sheets("目录").Range("A2")

    For i = 2 To Sheets.Count

    If Sheets(i).Name <> "目录" Then

    Cell.Parent.Hyperlinks.ADD anchor:=Cell, Address:="", SubAddress:=Sheets(i).Name & "!A1", TextToDisplay:=Sheets(i).Name

    End If

    Set Cell = Cell.Offset(1, 0)

    Next

    End Sub

    回答:

    选中全部表格→在一个所有表格都没有内容的单元格(最好是A1)输入=AAA1→检查兼容性→将检查结果复制到一个新的表格→编辑检查结果,把他变成目录→把所有输入的=AAA1删除掉→完成

    新葡亰496net 77

    回答:

    谢邀。

    不是很明白你的意思, 你是说要将所有的sheet建立一个目录吗?

    可以这样,在插入菜单中点击插入超链接,然后选择链接到本文档中的位置,就可以指定要查看的工作表。这样应该可以满足我理解的要求了。

    如果是,请点赞。不是的话,请回复。

    新葡亰496net 78

    回答:

    创建工作表目录,可以采用公式实现:

    1.实用宏表函数,get.workbook提取所有当前活动工作簿下的所有工作表名称,注意宏表函数在高版本得先定义名称再使用。根据工作表名工作簿与工作表后中括号的特性,采用find与mid函数搭配识别及提取所需要的工作表名数组。由于手机拍摄电脑图片太模糊,我写的公式是定义w来盛放,为方便更新增加了个易失性函数,公式为:w=mid(get.workbook(1),find("]",get.workbook(1)) 1,100)&t(now())

    2.使用index和now函数配合逐一提取w中的所有工作表名称,为防止工作表名提取完出现错误值使用iferror函数,用空值替换。公式编写为=iferror(index(w,row(1:1)),""),往下填充公式即可获得所需要的目录。

    3.若需要超链接跳转,可以使用hyperlink函数进行链接点击跳转对应的工作表。但是注意下,要加个#’,且iferror函数要用到最外层,让出现错误值时显示为空。手机照电脑图片实在看不清,不好打字,需要这个公式的可以关注联系我。

    其中xl文件夹里存放着Excel的大部分主题内容。

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

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

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

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

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

    新葡亰496net 79

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

    新葡亰496net 80

    小勤:晕啊。这么复杂。

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

    小勤:那怎么办好呢?

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

    Step-1:获取数据

    新葡亰496net 81

    Step-2:反转行

    新葡亰496net 82

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

    新葡亰496net 83

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

    新葡亰496net 84

    Step-5:数据上载

    新葡亰496net 85

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

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

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

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

    新葡亰496net 86

    theme:存放着这个Excel的主题设置;

    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等组合的方式,但都比较复杂,而且有个前提:业务经理、月份、存货分类等行列标题的内容要先生成,即结果表的框架先生成,只用公式读取其中的数值内容。如果要连行列标题(业务经理、月份、存货分类)都要靠公式整出来的话,基本没救。

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

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

      提供一个程序集下载吧,源代码大家去上面这个网址下载。ICSharpCode.ShareZipLib.zip

    sharedString:存放着各个单元格里的信息;

    更多Power Query精彩实战案例

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

    新葡亰496net 92


    新葡亰496net 93

      借用该类库中的ExtractZip()和CreateZip()方法可以帮助我们读取或修改(创建)Excel文件。当然这个类库中的某些代码是使用较早的.NET版本中的语法和对象来编写的,诸如ArratList、Hashtable等,它可能不适合在Silverlight工程中使用(支持Silverlight的.NET Framework与普通的.NET Framework有许多区别并且变化比较频繁),接下来的文章中我会向大家介绍如何在Silverlight工程中使用它。但现在并不妨碍我们在其它类型的工程中使用。

    worksheets:记录各个sheet之间互相调用关系。

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

      来看一看实际的例子。这里有一个类提供了一些方法用来读取和修改Excel文件中的数据:

    新葡亰496net 94

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

    回答:

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

    回答:

      1 using System.Collections.Generic;
      2 using System.Data;
      3 using System.Globalization;
      4 using System.IO;
      5 using System.Text;
      6 using System.Xml;
      7 using ICSharpCode.SharpZipLib.Zip;
      8 
      9 namespace XlsxReadWrite
     10 {
     11     internal static class XlsxRW
     12     {
     13         public static void DeleteDirectoryContents(string directory)
     14         {
     15             var info = new DirectoryInfo(directory);
     16 
     17             foreach (var file in info.GetFiles())
     18             {
     19                 file.Delete();
     20             }
     21 
     22             foreach (var dir in info.GetDirectories())
     23             {
     24                 dir.Delete(true);
     25             }
     26         }
     27 
     28         public static void UnzipFile(string zipFileName, string targetDirectory)
     29         {
     30             new FastZip().ExtractZip(zipFileName, targetDirectory, null);
     31         }
     32 
     33         public static void ZipDirectory(string sourceDirectory, string zipFileName)
     34         {
     35             new FastZip().CreateZip(zipFileName, sourceDirectory, true, null);
     36         }
     37 
     38         public static IList<string> ReadStringTable(Stream input)
     39         {
     40             var stringTable = new List<string>();
     41 
     42             using (var reader = XmlReader.Create(input))
     43             {
     44                 for (reader.MoveToContent(); reader.Read(); )
     45                 {
     46                     if (reader.NodeType == XmlNodeType.Element && reader.Name == "t")
     47                     {
     48                         stringTable.Add(reader.ReadElementString());
     49                     }
     50                 }
     51             }
     52 
     53             return stringTable;
     54         }
     55 
     56         public static void ReadWorksheet(Stream input, IList<string> stringTable, DataTable data)
     57         {
     58             using (var reader = XmlReader.Create(input))
     59             {
     60                 DataRow row = null;
     61                 int columnIndex = 0;
     62                 string type;
     63                 int value;
     64 
     65                 for (reader.MoveToContent(); reader.Read(); )
     66                     if (reader.NodeType == XmlNodeType.Element)
     67                         switch (reader.Name)
     68                         {
     69                             case "row":
     70                                 row = data.NewRow();
     71                                 data.Rows.Add(row);
     72 
     73                                 columnIndex = 0;
     74 
     75                                 break;
     76 
     77                             case "c":
     78                                 type = reader.GetAttribute("t");
     79                                 reader.Read();
     80                                 value = int.Parse(reader.ReadElementString(), CultureInfo.InvariantCulture);
     81 
     82                                 if (type == "s")
     83                                     row[columnIndex] = stringTable[value];
     84                                 else
     85                                     row[columnIndex] = value;
     86 
     87                                 columnIndex  ;
     88 
     89                                 break;
     90                         }
     91             }
     92         }
     93 
     94         public static IList<string> CreateStringTables(DataTable data, out IDictionary<string, int> lookupTable)
     95         {
     96             var stringTable = new List<string>();
     97             lookupTable = new Dictionary<string, int>();
     98 
     99             foreach (DataRow row in data.Rows)
    100                 foreach (DataColumn column in data.Columns)
    101                     if (column.DataType == typeof(string))
    102                     {
    103                         var value = (string)row[column];
    104 
    105                         if (!lookupTable.ContainsKey(value))
    106                         {
    107                             lookupTable.Add(value, stringTable.Count);
    108                             stringTable.Add(value);
    109                         }
    110                     }
    111 
    112             return stringTable;
    113         }
    114 
    115         public static void WriteStringTable(Stream output, IList<string> stringTable)
    116         {
    117             using (var writer = XmlWriter.Create(output))
    118             {
    119                 writer.WriteStartDocument(true);
    120 
    121                 writer.WriteStartElement("sst", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
    122                 writer.WriteAttributeString("count", stringTable.Count.ToString(CultureInfo.InvariantCulture));
    123                 writer.WriteAttributeString("uniqueCount", stringTable.Count.ToString(CultureInfo.InvariantCulture));
    124 
    125                 foreach (var str in stringTable)
    126                 {
    127                     writer.WriteStartElement("si");
    128                     writer.WriteElementString("t", str);
    129                     writer.WriteEndElement();
    130                 }
    131 
    132                 writer.WriteEndElement();
    133             }
    134         }
    135 
    136         public static string RowColumnToPosition(int row, int column)
    137         {
    138             return ColumnIndexToName(column)   RowIndexToName(row);
    139         }
    140 
    141         public static string ColumnIndexToName(int columnIndex)
    142         {
    143             var second = (char)(((int)'A')   columnIndex % 26);
    144 
    145             columnIndex /= 26;
    146 
    147             if (columnIndex == 0)
    148                 return second.ToString();
    149             else
    150                 return ((char)(((int)'A') - 1   columnIndex)).ToString()   second.ToString();
    151         }
    152 
    153         public static string RowIndexToName(int rowIndex)
    154         {
    155             return (rowIndex   1).ToString(CultureInfo.InvariantCulture);
    156         }
    157 
    158         public static void WriteWorksheet(Stream output, DataTable data, IDictionary<string, int> lookupTable)
    159         {
    160             using (XmlTextWriter writer = new XmlTextWriter(output, Encoding.UTF8))
    161             {
    162                 writer.WriteStartDocument(true);
    163 
    164                 writer.WriteStartElement("worksheet");
    165                 writer.WriteAttributeString("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
    166                 writer.WriteAttributeString("xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    167 
    168                 writer.WriteStartElement("dimension");
    169                 var lastCell = RowColumnToPosition(data.Rows.Count - 1, data.Columns.Count - 1);
    170                 writer.WriteAttributeString("ref", "A1:"   lastCell);
    171                 writer.WriteEndElement();
    172 
    173                 writer.WriteStartElement("sheetViews");
    174                 writer.WriteStartElement("sheetView");
    175                 writer.WriteAttributeString("tabSelected", "1");
    176                 writer.WriteAttributeString("workbookViewId", "0");
    177                 writer.WriteEndElement();
    178                 writer.WriteEndElement();
    179 
    180                 writer.WriteStartElement("sheetFormatPr");
    181                 writer.WriteAttributeString("defaultRowHeight", "15");
    182                 writer.WriteEndElement();
    183 
    184                 writer.WriteStartElement("sheetData");
    185                 WriteWorksheetData(writer, data, lookupTable);
    186                 writer.WriteEndElement();
    187 
    188                 writer.WriteStartElement("pageMargins");
    189                 writer.WriteAttributeString("left", "0.7");
    190                 writer.WriteAttributeString("right", "0.7");
    191                 writer.WriteAttributeString("top", "0.75");
    192                 writer.WriteAttributeString("bottom", "0.75");
    193                 writer.WriteAttributeString("header", "0.3");
    194                 writer.WriteAttributeString("footer", "0.3");
    195                 writer.WriteEndElement();
    196 
    197                 writer.WriteEndElement();
    198             }
    199         }
    200 
    201         public static void WriteWorksheetData(XmlTextWriter writer, DataTable data, IDictionary<string, int> lookupTable)
    202         {
    203             var rowsCount = data.Rows.Count;
    204             var columnsCount = data.Columns.Count;
    205             string relPos;
    206 
    207             for (int row = 0; row < rowsCount; row  )
    208             {
    209                 writer.WriteStartElement("row");
    210                 relPos = RowIndexToName(row);
    211                 writer.WriteAttributeString("r", relPos);
    212                 writer.WriteAttributeString("spans", "1:"   columnsCount.ToString(CultureInfo.InvariantCulture));
    213 
    214                 for (int column = 0; column < columnsCount; column  )
    215                 {
    216                     object value = data.Rows[row][column];
    217 
    218                     writer.WriteStartElement("c");
    219                     relPos = RowColumnToPosition(row, column);
    220                     writer.WriteAttributeString("r", relPos);
    221 
    222                     var str = value as string;
    223                     if (str != null)
    224                     {
    225                         writer.WriteAttributeString("t", "s");
    226                         value = lookupTable[str];
    227                     }
    228 
    229                     writer.WriteElementString("v", value.ToString());
    230 
    231                     writer.WriteEndElement();
    232                 }
    233 
    234                 writer.WriteEndElement();
    235             }
    236         }
    237     }
    238 }
    

    上图可以看到在表格中字符是怎样以文本的形式存储的。

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

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

    新葡亰496net 99

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

      1. DeleteDirectoryContents()方法用来清空临时目录的内容,该临时目录被用来存放解压Excel之后的文件。

    也就是说Excel在后期会用各种代码去读取这些xml纯文本,然后在图形化界面把这些文本展示出来。

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

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

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

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

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

    新葡亰496net 100

    回答:

    最牛的:

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

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

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

    回答:

    各有各的牛:

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

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

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

    LOW的,

    第一low,合并单元格。

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

      2. UnzipFile()方法直接使用了开源类库中的ExtractZip()方法,将Excel文件解压到临时目录。

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

      3. ZipDirectory()方法使用开源类库中的CreateZip()方法将临时目录中的内容重新打包成zip压缩文件。

      4. ReadStringTable()方法用来读取xl/sharedStrings.xml文件中节点t的内容。其实应该是直接读取si节点的内容,应为并不是所有的si节点都有t子节点。

      考虑一个情况:在Excel的单元格中,选中一部分内容,如内容中的某一个数字或某一个单词,然后对它单独设置样式。通过这种方法你可以将Excel单元格中的某一部分内容设置为粗体、上标,还可以在单元格内进行换行等。如下图:

    新葡亰496net 101

      在Excel单元格内设置的样式被存放到sharedStrings.xml文件中后会变成如下这种形式:

    <si>
        <r>
          <t xml:space="preserve">  Short-term investments (including securities loaned 
        of </t>
        </r>
        <r>
          <rPr>
            <b/>
            <sz val="8"/>
            <color rgb="FF404040"/>
            <rFont val="Verdana"/>
            <family val="2"/>
          </rPr>
          <t>$9,999</t>
        </r>
        <r>
          <rPr>
            <sz val="8"/>
            <color rgb="FF404040"/>
            <rFont val="Verdana"/>
            <family val="2"/>
          </rPr>
          <t xml:space="preserve"> and $8,888)</t>
        </r>
      </si>
    

      这个时候节点si中的内容就不是单纯的子节点t了。因此,如何解析XML文件需要根据实际情况去考虑,这主要取决于你在Excel文件中存储的内容。

      5. ReadWorksheet()方法会按照指定的工作表XML文件(如"xl/worksheets/sheet1.xml")在sharedStrings.xml文件中查找数据,并将结果存放到一个DataTable中。

      6. CreateStringTables()和WriteStringsTable()方法用来创建一个sharedStrings.xml文件。

      7. WriteWorksheet()方法用来创建一个工作表XML文件。

      来看看如何调用:

     1 private void ReadInput(object sender, RoutedEventArgs e)
     2 {
     3     // Get the input file name from the text box.
     4     var fileName = this.inputTextBox.Text;
     5 
     6     // Delete contents of the temporary directory.
     7     XlsxRW.DeleteDirectoryContents(tempDir);
     8 
     9     // Unzip input XLSX file to the temporary directory.
    10     XlsxRW.UnzipFile(fileName, tempDir);
    11 
    12     IList<string> stringTable;
    13     // Open XML file with table of all unique strings used in the workbook..
    14     using (var stream = new FileStream(Path.Combine(tempDir, @"xlsharedStrings.xml"),
    15         FileMode.Open, FileAccess.Read))
    16         // ..and call helper method that parses that XML and returns an array of strings.
    17         stringTable = XlsxRW.ReadStringTable(stream);
    18 
    19     // Open XML file with worksheet data..
    20     using (var stream = new FileStream(Path.Combine(tempDir, @"xlworksheetssheet1.xml"),
    21         FileMode.Open, FileAccess.Read))
    22         // ..and call helper method that parses that XML and fills DataTable with values.
    23         XlsxRW.ReadWorksheet(stream, stringTable, this.data);
    24 }
    25 
    26 private void WriteOutput(object sender, RoutedEventArgs e)
    27 {
    28     // Get the output file name from the text box.
    29     string fileName = this.outputTextBox.Text;
    30 
    31     // Delete contents of the temporary directory.
    32     XlsxRW.DeleteDirectoryContents(tempDir);
    33 
    34     // Unzip template XLSX file to the temporary directory.
    35     XlsxRW.UnzipFile(templateFile, tempDir);
    36 
    37     // We will need two string tables; a lookup IDictionary<string, int> for fast searching 
    38     // an ordinary IList<string> where items are sorted by their index.
    39     IDictionary<string, int> lookupTable;
    40 
    41     // Call helper methods which creates both tables from input data.
    42     var stringTable = XlsxRW.CreateStringTables(this.data, out lookupTable);
    43 
    44     // Create XML file..
    45     using (var stream = new FileStream(Path.Combine(tempDir, @"xlsharedStrings.xml"),
    46         FileMode.Create))
    47         // ..and fill it with unique strings used in the workbook
    48         XlsxRW.WriteStringTable(stream, stringTable);
    49 
    50     // Create XML file..
    51     using (var stream = new FileStream(Path.Combine(tempDir, @"xlworksheetssheet1.xml"),
    52         FileMode.Create))
    53         // ..and fill it with rows and columns of the DataTable.
    54         XlsxRW.WriteWorksheet(stream, this.data, lookupTable);
    55 
    56     // ZIP temporary directory to the XLSX file.
    57     XlsxRW.ZipDirectory(tempDir, fileName);
    58 
    59     // If checkbox is checked, show XLSX file in Microsoft Excel.
    60     if (this.openFileCheckBox.IsChecked == true)
    61         System.Diagnostics.Process.Start(fileName);
    62 }
    

      你所要做的只是对XML进行操作,仅此而以!

      支持.NET操作zip压缩包的类库应该还有很多,任何一个都可以,因为Excel的OpenXML文件本身就是一个标准的zip压缩包。但是上面的方法还是有一个局限性,那就是需要临时目录来存放解压之后的文件,以及重新打包时所指定的源文件。对普通的Windows应用程序或asp.net应用程序而言这个并没有什么困难,只要权限允许,读写临时目录没有任何问题,但是在Silverlight中则有所不同,因为在Silverlight中读写客户端文件需要比较高的安全级别和认证,这就导致解压文件会有困难,一个简单的方法就是直接在文件的流中进行解压和修改,然后再将流打包成zip文件。下一篇文章中我会向大家介绍如何在Silverlight中使用,以及如何定义一个类来完成Excel文件中某些字符串样式的修改。

    本文由新葡亰496net发布于新葡亰496net,转载请注明出处:新葡亰496net:您见过的excel最牛和最low用法是怎么

    关键词: