图片 59

工作中见过最好或最差劲的excel用法是什么,那么什么时候用word做表格

图片 1

3、如果有催款需求,便于催款

vlookup虽好,然难承大数据之重

原创 大海 Excel到PowerBI

小勤:大海,现在公司的数据量越来越大,现在有订单表和订单明细表,经常要将订单表的一些信息读取到订单明细表里,给相关的部门去用,原来只要几列数还好,vlookup读一下就是了,但现在,经常要很多数,用vlookup要累屎了。这个订单表还算少的,还很多其他的一张表里都好几十列了。

图片 2

图片 3

大海:呵呵,大数据时代嘛。几十列算少的啦,我上次一个项目上的合同表,有差不多300列,而且这还不算真正多的。

小勤:那怎么办啊!有时候按列顺序读还好,很多时候还不是按顺序的,简直就没法整啊。而且,满篇公式的时候,工作表都要跑不起来了。

大海:这个时候用vlookup的确有点吃力了,虽然vlookup是Excel中极其重要的函数,但是,在大数据时代,已经很难承起数据关联合并的重担了,所以微软才在Excel里加了PowerQuery的功能嘛,点点点,你想要哪些列就哪些列。

小勤:这么神奇?

大海:这段时间PowerQuery的神奇你也不少见了,不用惊讶。现在就告诉你怎么弄。

Step-1:获取订单表数据并仅创建表连接上载

图片 4

Step-2:获取订单明细表数据并仅创建表连接上载(需要直接上传结果的可以选择表)

图片 5

Step-3:回到PowerQuery界面(当然,前面一个步骤如果没有关闭并上载的话,不需要这一步)

图片 6

Step-4:选择要接入外部数据的查询,单击-

图片 7

Step-5:选择要接入的外部表、选择两表之间用于匹配的列(可以是多列匹配,文末以另一个例子该步骤的附图方式说明)

图片 8

Step-6:展开要接入表的列信息,选择要接入的列以及列名显示方式(是否加前缀)

图片 9

Step-7:查看接入的结果,上载数据

图片 10

Step-8:改变数据的加载方式(由“仅创建连接”方式改为“表”,若前面订单明细不是以“仅创建连接”的方式创建,该步骤不需要)

图片 11

小勤:这样真是太方便了,只要选一下匹配要用的列,选择一下要接入哪些列就搞定了!对了,刚才你不是说可以多列匹配吗?原来用vlookup的时候可麻烦了,还得增加辅助列先将那些列连接起来,然后再用辅助列来匹配。

大海:是的。在PowerQuery里也不需要了,只要在选择匹配列时按住ctrl键就可以选择多列了。只是要注意两个表选择匹配列的顺序要一致。如下图所示:

图片 12

小勤:太好了,以后数据列多的时候匹配取数就太简单了。

以上是使用Power
Query代替vlookup实现的两表合并的基本用法(虽然步骤看起来很多,实际关键步骤就2个,都是鼠标点点点就瞬间完成的事情)。

那么,前面提到的,如果两个表间存在的差异数据都要显示,怎么办呢?只要对其中的表间连接类型按以下情况进行适当的选择即可:

  • 左外部:只要订单表(左表)里有的数据,结果表里都会有,但有些因为明细表(右表)里没有,所以匹配过来后会成为null(空值)

    图片 13

  • 右外部:和左外部相反,即明细表(右表)里有的数据,结果表里都会有,但因为订单表(左表)里有部分数据没有,所以合并后用null值表示。

    图片 14

  • 完全外部:不管哪个表里的数据,全都进结果表,对于双方都有一些对方没有的,合并后显示为null值。

图片 15

  • 内部:跟完全外部相反,只有两个表都有的数据,才进结果表。

图片 16

  • 左反:只有订单表(左表)有而明细表(右表)没有的数据,才进结果表。这种用法经常用于检查如哪些订单缺了明细表等。

    图片 17

  • 右反:和左反相反,只有明细表(右表)有而订单表(左表)没有的数据,才进结果表。

图片 18

欢迎关注

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

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

回答:月末了,各部门报过来的数据,如何合并到一个文件里?

图片 19

过去,我们只能使用VBA或编写SQL语句。

现在,我们只需点击几次鼠标,书写一个公式。

图片 20

6个工作簿,数据结构都是一致的,我们需要把她们合并到一个工作簿里。

图片 21

,找到需要合并的文件夹。

图片 22

文件夹下每一个工作簿都被合并在一起。首列“内容”显示,是二进制数据的意思。

最后一列显示这些工作簿的地址。中间几列分别表示工作簿名称、后缀名、访问时间、修改时间、创建时间和文件属性。

图片 23

点击,进入,中间那几列无用,所以右键单击。

图片 24

如果此时直接点击二进制首列的”展开按钮”,会出现错误提示。

图片 25

这是因为,二进制数据无法直接提取。我们需要书写一条公式。

图片 26

在点击。

图片 27

在对话框,保留默认的,在列表框录入公式:

=Exel.Workbook([Content],true)

图片 28

注意,公式函数严格区分大小写(首字母大写)。

函数的第一个参数是需要转换的二进制字段,这个字段可以在右侧列表框双击选择,不必手工录入。

函数的第二个参数是逻辑值,如果原数据有标题行,这里应该添写true。

点击后,新增一列,数据类型显示为,右侧的列表显示了刚刚进行的步骤。

图片 29

随便选择数据的一个单元格,下方预览区会显示这个表的结构。

点击新增列标签右侧“展开按钮”,选择。

图片 30

每一个表会按列方向展开。其中Data数据类型仍然显示。

图片 31

我们再次点击数据列标签右侧的“展开按钮”。

图片 32

展开的数据已经将文件夹下所有工作簿合并在一起。

删除一些不需要的列。

图片 33

只留有效数据列,点击返回Excel。

图片 34

所有数据都已经合并到一个工作簿中。

图片 35

得到的合并数据实际上是一个,右键单击可以数据。

当文件夹下原工作簿内容变更,合并工作簿只要一次,即可更新数据。

展开数据时,如果选择,得到的数据会将同类项求和或计数。

怎么样,是不是比VBA要简单的多啊。

更多财税职场学习资讯,关注秀财网

回答:对于这个问题的回答,都是仁者见仁智者见智的事情。看到题主的需求,我的第一反应就是使用vlookup、index、lookup等函数。然而哪种更为简单呢,这个要根据实际情况而定。在某些情况下,我们甚至一个函数都不用也能快速地将表格整合在一起,比如使用复制粘贴或者Power
Query。

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

1、插入点定位于需要计算的单元格内,表格工具布局选项卡—数据组公式按钮—软件自动识别公式或者手动输入。(=sum(left)翻译成中文是:这个单元格等于左边单元格的和,sum是求和函数,left是左边的意思)。

使用函数的方式相对来说较为复杂,并且学函数使用也需要一个过程,在此不做推荐!推荐最简单、快捷的一种方式,数据透视表。具体见动态图:

2、查看需求,便于跟单,有日期,可以查看进度进程等各方面

如前面的答案,vlookup是方法之一,但是,用vlookup有一个问题,就是只能从一个表读取数据到另一个表,而不能实现两个表的完全整合,比如如果A表上有姓名和地址而B表上没有数据的,从B表读取A表的数据就读不出来,相反也是一样。

图片 36

个人认为在不熟悉Word中函数使用方法时,用我说的方法更高效些。毕竟Excel中的公式比Word中的公式好记且方便使用。

回答:

问题:服装公司跟单的时候用excel表格有什么作用?

排序法

  • 思路

首先将两张表合并成一张表。接着按照姓名进行排序,这样就会看到上下两行都是同一个人的信息。然后使用条件格式下的重复项功能标记出重复的姓名,将重复项整行复制出来。最后,给重复项的奇数行自动填充序列,偶数行保留空白,再按照序列排序就提取出了所有的电话啦。看起来似乎很复杂,其实操作起来是很快的。

  • 操作步骤

1.
如图,将两张表合并成一张,其中的关键字“姓名”放在同一列下,选中整张表,点击“数据”——“排序”,按照“姓名”列升序排列。图片 37
2.
选中所有数据,点击“开始”——“条件格式”——“新建规则”,如图,选择为重复值设置格式,点击“格式”按钮,设置好填充颜色。图片 383.
条件格式标记出了所有有地址和电话的人。点击筛选按钮,选择按照颜色筛选,将这些有颜色的行全部复制出来。在F1和F3输入1和2,选中前面四个单元格,如图。图片 394.
向下填充,使得所有的奇数行都填充上了序列,偶数行空白。图片 405.
选中整张表格,按照F列升序排列,这样奇偶数行就分开了,然后复制电话到G列,删除辅助列F列即可。图片 41

如果不想改变原有表格的顺序,可以在操作前添加好编号。操作完毕后,再按照编号排序,就可以恢复最初的顺序了。


谢谢阅读,欢迎点赞和评论,关注或点击头像可以看更多的内容哦!

回答:首先,把A表和B表合成一个文件。

图片 42

两表中的内容格式示例如下。

A表

图片 43

B表

图片 44

我们在A表后面的电话一列”C2″单元格中加入函数命令,与B表中的数据进行比对“=VLOOKUP(A2,B!A:B,2,FALSE)”

图片 45

函数命令的解释:

  1. “VLOOKUP”是纵向查找函数;

  2. (
    )中的“A2”代表A表的单元格A2;“B”代表的是B表(也就是表的名称);“!A:B”代表的是在B表的A列到B列之间查找;“2”代表查找列中的第2列;“FALSE”是判断命令;

  3. 整句命令的含义是,在B表中的A列到B列之间查找,是否有A表中A2单元格的数据,如果有,那么就把第2列的数据显示在A表C2单元格中。也就是在B表中两列数据中查找有没有“张三”这个人,如果有,那么在A表的电话一栏显示B表中“张三”的电话,你要先算好电话一列是查找列的第几列。
  4. 输入函数后回车,即可得到结果,如下:

图片 46

把鼠标移至C2单元格右下角,鼠标变成黑色十字,按住下拉,即可将函数格式复制到下面的单元格,结果如下图:

图片 47

你可以根据表格的实际情况,修改函数中的相关字符来达到最终效果。

回答:第一反应是用VLOOKUP函数来匹配,

不过要注意的是:姓名很容易出现重复的情况

为了避免这种特殊情况,建议先用透视表,查看一下,两个表格哪些姓名有重复

然后给每位客户设置唯一的ID,

再使用ID,作为VLOOKUP函数的索引,来匹配电话号码

这样得出的结果会更加精确

回答:Index+Match函数结合也可完美解决,效果类似于Vlookup,不过后者更便捷。

思路大致如下:首先将两个工作簿放到一个表中(使用Microsoft Query)。

如果有重复项的话,要提前删除重复项。

然后用Index+Match函数或Vlookup函数就可以了。

get√

回答:第一反应,想到的就是引用和匹配函数,首选就是vlookup函数了。

因为A表和B表的共同点就是客户的姓名,通过姓名就可以互相引用对方表格里的地址或者电话了。

详细步骤就不说了,大神们已经给出答案了。

欢迎关注我的头条号,如果有excel方面的问题,可以私信交流,为你答疑解惑。

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

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

图片 48

大海:为什么又要公式?

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

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

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

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

Step-0:数据获取

图片 49

Step-1:逆透视

图片 50

Step-2:透视

图片 51

最后,数据上载

图片 52

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

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

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

3、其实word和excel是一个套件里面,他们是想通的。(excel中每个单元格都是有名字,第一行行号为1,第二行行号为2;第一列列标为A,第二列列标为B,依次类推。第一行第一列单元格的名字为A1;第三行第2列的单元格名字为B3。)word同样可以这样命名,只是软件没有标出来。所以计算也可以这样算:
图片 53

发数据来,不看不知情况。

3、可以利用函数公式自动进行数据计算,提高工作效率。

回答:

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

回答:

只要做好进店日期或时间或提前做好排序即可,当然也可以设置自动记录,不用人为记录,只要数据录入进来,然后就是查询,按时间段查询或序号范围查询

回答:

刚看了前面几个答案,除了说用vlookup的方法基本答对之外,人气最高的答案貌似不是提问者所需要的,因为这个答案是指多个工作表的汇总,而不是提问者所需要的横向的合并。

03 破解工作表加密

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

图片 54

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

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

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

图片 55

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

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

图片 56

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

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

图片 57

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

图片 58

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

图片 59

首先还是讲下excel和word表格的区别。Excel也叫电子表格,word也能制作表格,两个软件都可以制作表格,但是有区别。Word更擅长文字处理,excel专门用来制作表格。那么什么时候用word做表格,什么时候用excel做表格呢?这样说吧,如果做的表格内大部分是字符的话就用word做,如果做的表格内有大量数据要进行计算、或者数据处理的话用excel做。举个例子,学生的课程表用word做,学生的成绩表用excel做。回到问题,数据在表格中要计算,本来在excel中会比较简单,但是少量、简单的计算word表格同样可以,下面我们来看下在word表格中怎么样计算

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章