您的位置:新葡亰496net > 新葡亰496net > 新葡亰496net如何用Excel做每月的数据汇总表,制作

新葡亰496net如何用Excel做每月的数据汇总表,制作

发布时间:2019-06-16 07:52编辑:新葡亰496net浏览(164)

    问题:如何用Excel做每月的数据汇总表?

    问题:excel表格透视表怎么做?

    问题:Excel问题?

    新葡亰496net 1

    问题:制作excel表格有什么规则?

    回答:

    回答:

    回答:

    几百个业务人员的数据反馈,几十家分公司的每日汇总,各渠道数据的汇总,部门内部数据的汇总等等,相信在很多没有完善CRM数据系统的公司,可能每天都都需要有人去做这样的事情。总之,非高效没意义的工作我们能自动化智能化的代替我们则会想方设法去代替。今天我们就用Excel中的PowerBI系列插件帮我们去完成这样的工作。

    回答:

    这个可以解决。我打开一个我现在的表单,给你演示,具体操作如下:

    style="font-weight: bold;">如果只能学Excel中的一项功能,这项功能就是数据透视表,可见数据透视表在Excel中的地位。

    解决方法:

    所以,今天我们的学习目标就分为以下两方面:
    完成上百甚至上千上万份同格式的文件的汇总合并

    这个问题不错,就此将我使用excel的经验总结一下。

    1、打开一个数据文档:新葡亰496net 2

    数据透视表可以使用户通过简单的拖拽操作,完成复杂的数据分类汇总,可以说是Excel中最实用、最常用的功能,没有之一。

    这里没明白是将颜色填充到“缺货”的日期里,还是将颜色一直填充直到缺货;我们以第一种情况为例子,如下图效果。

    结合PowerPivot完成上百万数据的透视分析

    excel表格有很多种,即可作为数据源使用,又可作为统计表,一定情况下,也是非常不错的数据可视化工具。

    2、选中需要汇总的所有数据,注意,不能有合并过的单元格。

    今天星爷带你见识数据透视表的十八般武艺,零基础让你轻松入门数据透视表。

    新葡亰496net 31.在C4单元格内输入公式=SUM(E4:N4), 在D4单元格输入公式=B4-C4,向下填充好公式;选择D4:D6,设置条件格式“小于0”显示红色;

    如果觉得以上内容对你有所帮助,我们就接着往下看:
    PS:关于PowerQuery的简单介绍可以关注我们【简快Excel】查看历史文章,有详细介绍。
    合并上百份报表的操作思路:
    操作步骤:
    第一步 将要合并的文件放到我们待合并文件夹

    我这里主要讲的是excel作为数据源的规则,因为数据源是excel最基础最强大的功能,统计分析以及数据展现都是在数据源的基础上进行的,数据源没有问题,会为后面的操作打下一个良好的基础。

    新葡亰496net 43、找到数据透视功能,并点击,会出现以下结果。新葡亰496net 5看是选择用汇总图表还是汇总图。现在以汇总表为例,请点击。新葡亰496net 6这个就是你开始选中的范围。点击确定就行。“新工作表”表示是在另一个新表上出现的汇总,推荐用这个,就算错了,母版不会丢失。然后会出现以下图示。新葡亰496net 7

    001 基本操作

    NO.1 创建数据透视表

    使用原始数据表进行各销售员对应各产品的销量。

    根据你想要呈现的数据报表,透视一下,让你轻松“拖”出来。

    新葡亰496net 8

    新葡亰496net 9

    看到没?

    数据透视表的学习成本非常低,通过简单的拖拽就能完成数据的分类汇总。

    很多新手学习数据透视表时会搞不懂字段、区域、筛选、行、列……等等这些乱七八糟的名词之间的关系。

    我用一张图告诉你字段在各区域之间的对应关系。

    新葡亰496net 10

    数据透视表字段与区域之间的关系

    NO.2 更改数据源

    数据透视表的一个优势就是,数据源变更后进行简单的更新就可反映到数据报表中。有两种方式,第一种是数据透视表刷新:如果只是数据源中的数值进行了更新,采用刷新即可。

    新葡亰496net 11

    刷新数据透视表源数据

    如果是数据源的结构或布局变更了,这时采用更改数据源的方式。

    新葡亰496net 12

    NO.3 插入计算字段

    计算字段极大扩展了数据透视表的计算功能,属于神一般的存在。比如原始数据表中有一列数据为目标值,有一列数据为实际值,那么在数据透视表中可以通过计算字段输入公式=实际值/目标值,来求出汇总的达成率。

    新葡亰496net 13

    NO.4 使用切片器

    这是制作动态图表的利器。

    新葡亰496net 14

    新葡亰496net 152.在O4:O6单元格内输入公式=IF(D4<0,"库存不足,请补仓","")。

    新葡亰496net 16

    即便跳出excel,其他软件要引用excel数据,也需要一个良好的数据源格式。

    4、把需要的类目拉到最左边的位置。新葡亰496net 17会出现以下图示。

    002 布局与格式调整

    NO.5 更改值汇总依据

    区域是数据透视表的核心部分,通过数据透视表提供的强大数据计算功能,可以使用多种汇总方式和值显示方式来计算值字段数据。

    值汇总依据,就是你要用数据透视表对原始数据中的数值进行怎样的计算,比如求和、计数还是求平均值。

    新葡亰496net 18

    NO.6 更改值显示方式

    数据透视表不仅可以按照不同的方式汇总数据,它还可以按照不同的方式显示数据,从而更清晰的看出数据之间的关系和逻辑。

    ①总计的百分比

    总计百分比含义是:每一项分类汇总的值占总计的百分比

    新葡亰496net 19

    各项之和为100%

    ②父级百分比

    总计的百分比关注的是个体占整体的情况,往往只有一个维度,除此之外,还可以展示局部百分比,比如求出每个销售员在各地区的销售额占比。

    新葡亰496net 20

    各小组里的项之和为100%

    在Excel 2016中,数据透视表中共有14种“值显示方式”。

    NO.7 更改数据透视表布局

    默认生成的数据透视表布局比较呆板,有多个方面可以设置不同形式的布局。

    新葡亰496net 21

    新葡亰496net 223. 选择日期下方E4:N6设置条件格式=SUM($E4:E4)>$B4,设置好颜色;

    我们先放入10份文件测试,完成后再给大家演示100份文件的合并
    第二步 我们在文件夹外新建一个Excel文件汇总数据使用
    第三步 新建查询开始链接我们的源数据
    我们点击数据标签下边的【新建查询】→【从文件】→【从文件夹】,如下图:

    这些规格,不是说非此不可,只是会在后期的深度加工中或多或少的出现错误,错误是数据统计分析的大忌,修正错误不如在一开始就刻意避免它。

    5、然后,鼠标右键分别点击各类目,如图1,出现对话框时,点2“字段设置”。新葡亰496net 23

    003 数据透视表组合功能

    数据透视表中有一个叫做“组合”的功能,一方面它能自动识别日期、数值等可计算的字段,然后按照给定的跨度进行组合,比如组合出按年、季度、月、日,甚至小时、分……的汇总;另一方面,也可通过手动选择的方式,将文本格式的数据按照自定义的方式进行组合,比如组合出一线城市、二线城市等等。

    通过分组功能将这些不同数据类型的数据项按多种组合方式进行分组,大大增强了数据表分类汇总的延伸性,方便用户提取满足特定需求的数据子集。

    NO.8 按时间维度组合

    新葡亰496net 24

    本图来自网络

    NO.9 按数值组合

    新葡亰496net 25

    本图来自网络

    时间维度和数值维度的组合,逻辑上都是一样的,都是基于大小进行分组组合,这两者可以成为自动组合,数据透视表还允许对文本类型进行自定义组合。

    NO.10 自定义组合

    还有一种组合方式叫作自定义组合,比如要组合的对象是文本字段,或者对日期进行非等距的组合。

    比如:一线城市和二线城市销售情况分析

    按住Ctrl键选择北京、广州、上海、深圳四个城市,然后点击“鼠标右键”选择“创建组”。接着,选中剩下的所有城市,点击“鼠标右键”→选择“创建组”,数据透视表按照我们所选定的内容进行组合。

    新葡亰496net 26

    通过这十个技巧,你掌握数据透视表了么?

    style="font-weight: bold;">关注「精进Excel」,如果任意点开三篇文章,没有你想要的知识,算我耍流氓!

    回答:

    大家好,我是Excel大全,头条原创作者,每日分享实用Excel小技巧。

    刚好看见这个问题,我就来分享下我的经验,希望能帮到你!

    我将从以下方面回答这个问题:数据透视表怎么做?

    • 一个简单例子,使用透视表,无需任何公式,3步搞定分组统计;

    • 插入透视表的方式;

    • 透视表布局;

    • 分组统计;

    • 计算字段;

    • 切片器;

    • 透视图;

    新葡亰496net 274. 如下图,只要在相对应的日期下方输入消耗量累积超库存时就会显示并提示了。

    新葡亰496net 28

    1、最初的数据最好为一维表

    一维表的特点就是第一行为字段,每一列都是此字段下的同类信息。如下图所示:

    新葡亰496net 29想对应的,二维是两个维度对应相应的数据,一般列对应一个维度,行对应一个维度,这样数据比较直观,所以常用在汇总表、统计表中,如下图所示,列为日期,行为产品:

    新葡亰496net 30二维表通常是在一维表基础上加工而来,所以一维表是基础。

    一维表和二维表是可以转换的,这里就不详述了。

    出现如下对话框:新葡亰496net 31

    01 简单透视表,3个步骤搞定分组统计

    • 第一步,插入透视表;

    • 第二步,用鼠标拖动字段到透视表区域;

    • 第三步,设置行列标签分组统计方式,搞定!

    新葡亰496net 32

    通过这个例子,先了解下透视表的大致使用方式和透视表的强大之处。

    新葡亰496net 335. 如是要设置成第二种颜色显示情况,如下图将条件格式中的>改成<就可以了。

    完成后点击浏览找到我们的文件夹路径点击确定,也可以复制我们的路径直接点击确定即可。

    2、同列为同类信息,不要混合

    这个也比较好理解,日期列都是日期,不要夹杂销量,筛选、计算、计数等等都比较方便。

    新葡亰496net 34

    点击确定。新葡亰496net 35这个,我们可以看到这个是以日期为分类的汇总方式。如果你想以品牌为分类,可以把品牌类目拉到实施日期前边就行了。你试试看。

    插入透视表的方式

    有两种方式,可以插入透视表

    • 法1:菜单选择插入-透视表;

    • 法2:按下<Alt> D,松开,再按P

    新葡亰496net 36

    其中,方法1最常用,一般的统计分析基本上都能搞定;方法2,更多地用于多表合并情况;

    新葡亰496net 37

    新葡亰496net 38

    3、字段名称唯一,且不为空

    字段是指第一行的名称,唯一同一个性质的数据放在同列,例如所有的日期都放在日期列里,字段名称也为日期,如果再有一个日期列,可能你自己知道他们的区别,但是别人不知道,机器也不知道,机器运算时就会出错。当然,日期也可分订单日期、发货日期、回款日期等多列。

    不为空是指第一行的字段名称不要为空,比如创建透视表时,空字段就会报错。

    不知道是否有帮助到您,请关注,点赞哟!以后可以常联系!

    透视表布局

    透视表的布局有4个重要区域,行标签、列标签、值区域和筛选页;

    新葡亰496net 39

    看似很复杂一个界面,其实很简单,通过鼠标,将面板中的字段,拖拽到相关的区域,便完成简单的透视表。

    若对透视表的布局样式不满意,还可以通过设计菜单中各种功能进行调整,如下所示:

    新葡亰496net 40

    可以直接套用样式,或者手动调整透视表的每个细节,如常用的传统列表的形式:

    新葡亰496net 41

    1. 有不明白的地方可以关注后私信我。

    然后我们点击编辑,即可进入PowerQuery界面。
    第四步 PowerQuery编辑获取数据
    从第三步的表里我们看到了这几列包含了我们的文件名,后缀,创建日期,修改日期,访问日期,以及他的文件夹路径等属性,就是没有直接看到我们表里的数据,其实我们表里的数据是被以二进制的方式存储在【Content】列了。我们仅仅需要一个函数就能获取其中的内容,我们具体操作下。
    1、添加列里,点击自定义列,输入=Excel.Workbook([Content],true)

    4、信息格式正确

    这个是比较常见的错误,用文本形式保存的数据、日期等等。不同的格式在统计中会有不同的结果,这对正确性是非常不利的。

    因此,日期就用日期格式,数据就用数值格式,这个是非常好的习惯,也是规则。

    新葡亰496net 42

    回答:

    分组统计、字段设置、计算字段

    在行或者列标签字段上,右键鼠标,在菜单中,有一个组合功能,利用这个组合,可以完美地组合出各种统计维度,如,月报、季报、年报、按部门、地区等等;

    新葡亰496net 43

    而在值区域上右键,即可设置统计方式,如,计数,求和,平均等;

    新葡亰496net 44

    透视表中,还有一个计算字段,有了这个计算字段,我们就不需要修改数据源了

    新葡亰496net 45

    新葡亰496net 46新葡亰496net 47

    新葡亰496net 48

    5、每个单元格最好为单一的数据信息

    比如销量列,就是单纯的销售数据,而不要带有单位,为后期的统计增加不必要的麻烦。如果需要单位,可以另外备注,也可单独列单位字段。

    具体步骤可参考

    切片器 图表

    切片器 图表=动态图表;

    切片器,就类似一个多字段的筛选器

    新葡亰496net 49

    图表,就不用说了吧,一份报告最终的展示方式

    新葡亰496net 50

    那,切片器 图表,便是那些高手手中高大上的动态图表了。

    回答:

    注意:公式需要区分大写
    2、然后,我们删除我们不需要的干扰列,我们按住ctrl选择我们的表名称[Name]列和[自定义]列

    6、不含有合并单元格

    这是因为合并单元格只保留左上角一个单元格中的数据,统计汇总时,其他单元格会当做空值处理,这也会造成后期数据统计的错误。

    新葡亰496net 51

    透视表的刷新

    这个差点给漏掉了,当数据源修改时,数据透视表得刷新下,才能同步更新哦!

    新葡亰496net 52

    1、选中如图所示区域

    新葡亰496net 53

    7、数据源中不要含有小计

    这个也是经验之谈,原始数据与合计数据混合的表格,会为后期的数据加工带来不必要的麻烦,比如数据更新时要同时更新合计数据,比如再次加工分析,也要考虑合计的影响。

    因此,数据源中最好不要任何的合计数据,如果需要,再次加工就好,这样数据源可以做为基础数据,多次使用,按需加工成不同类型的汇总表。

    新葡亰496net 54

    或者到主页查看纯干货:excel表格快速汇总的多种办法,拿走不谢!

    好了,这个透视表的问题就先到这吧,希望能帮到你!

    我是Excel大全,头条原创作者,每日分享Excel实用小技巧。

    您的关注、转发、收藏、点赞、评论,都是对我莫大的支持,谢谢!

    回答:

    1. 首先,选中原始数据区域,点击-。

      新葡亰496net 55

    2. 如果刚才已经选中了数据区域,在中最上面的选择区域框可以看到选中的单元格区域,如果刚才没有选中,也可以在此选取。

      新葡亰496net 56

    3. 数据透视表提供了在新工作表创建和在当前工作表创建的选择,如果数据透视表较大,内容很多,建议在新工作表中生成透视表。

      新葡亰496net 57

    4. 根据实际情况选择完上述两个设置后,点击按钮。

      新葡亰496net 58

    5. 这时,在excel中就出现了一个新工作表。左面是空白的透视表区域,右边是数据透视表字段列表,可以拖动和设置。

      新葡亰496net 59

    6. 新葡亰496net,数据透视表列表字段处显示的字段名称是原始数据区域的抬头,可以拖动到下面的四个框中。

      新葡亰496net 60

    7. 我们把字段拖到行标签位置,拖动到位置。这时,我们可以看到左方的数据透视表已经按照不同的人名将数量进行了汇总。

      新葡亰496net 61

    8. 行标签处可以插入多个,但是前后顺序要根据自己的需求来选择。比如说,我们要按人员和名称分别看汇总数,可以将字段拖到字段下面,结果如下图所示:

      新葡亰496net 62

    9. 上面的结果是2007版透视表的样式,我们可以通过如下图的设置改成2003版数据透视表的样子。显示样式根据自己的习惯决定。

      新葡亰496net 63

      新葡亰496net 64

    10. 如果不喜欢看到这些汇总数字,可以做数据透视表有汇总字样的单元格上鼠标右键,选择取消汇总数。

      新葡亰496net 65

      新葡亰496net 66

    11. 数据透视表数据还可以方便的生成数据透视图。

      首先,选中透视表上任意一个单元格,选择-。

      新葡亰496net 67

    12. 选择一个合适的图表类型,点击按钮。

      新葡亰496net 68

    13. 这时,就可以看到自动生成的数据透视图了,非常方便。

      新葡亰496net 69

      新葡亰496net 70

    14. 选中数据透视表上任何一个单元格,菜单栏上将激活和两个选项卡,可以对报表布局、样式等进行更多设置。

      新葡亰496net 71

      新葡亰496net 72

    回答:

    1 创建数据透视表、字段重命名、排序

    新葡亰496net 73

    2 手工也能排序:选中往下拖

    新葡亰496net 74

    3 必不可少操作:刷新

    新葡亰496net 75

    4 详解数据透视表布局

    新葡亰496net 76

    5 强大的筛选功能:最大,最小,介于都可以

    新葡亰496net 77

    6 怎么让数据动起来:将原数据设置为数据透视表下的“表格”

    新葡亰496net 78

    7 数据可以多次使用:方便各种统计

    新葡亰496net 79

    8 值可以排序,汇总数据也可以哦

    新葡亰496net 80

    更多财税职场和管理会计最新资讯,搜索关注管理会计小秘书(微信号:glkjxms)

    回答:

    Excel数据透视表实际应用之物料汇总

    1举例:多个车型配置使用的物料向下排列没有合并用量。
    新葡亰496net 81

    2.鼠标点击右边空白处任意单元格-菜单插入-数据透视。

    新葡亰496net 82

    3.弹出创建数据透视表对话框-框选列标A到F -透视结果放置位置可以选在现有工作表-确定。
    新葡亰496net 83

    4.拖动字段到序号对应位置。
    新葡亰496net 84

    5.注意现在的数值是计数,我们汇总用量是求和,单击计数项-值字段设置-点击求和。
    新葡亰496net 85

    6.鼠标点击下汇总结果-菜单栏设计-分类汇总-不显示分类汇总。
    新葡亰496net 86

    7.设计-报表布局-点击以表格形式显示-点击重复所有项目标签。
    新葡亰496net 87

    8.汇总结果样式。

    新葡亰496net 88

    回答:

    关于Excel表格做数据透视可以从以下10个基础入门及超级实战方面来学习。

    新葡亰496net 89

    新葡亰496net 90

    3、展开自定义列,对Kind列筛选,让其等于Sheet,避免表格中有表格引用,或者区域名称导致汇总出现重复或者部分错误,这块我们就能看到我们具体的每个工作簿中工作表的名称,每个工作表的内容将被存储在了[Data]列,我们随便点击一个单元格便能看到里边的数据。

    8、数据源最好是一个连续的整体,不要有空行或空列

    这个也比较好理解,比如在套用表格式时,空行或空列会直接将表的区域隔开,默认为数据区域到空行或空列为止。

    在数据透视表中,空行或空列也会报错,或者是空值。

    目前想到的就这些,希望能帮你建立一个完美的数据源表格~


    专注分享高效工作技能,助你“职场UP”,欢迎点赞和关注。

    回答:

    谢邀!

    Excel表格软件的设计宗旨就是方便对于数据的编辑和统计处理。因此,在制作excel表格里,要充分考虑后期使用的便利性,一切的设计都基于这点出发。一方面方面自己对表格数据及统计结果的使用,另一方面也要方便别人的使用。

    新葡亰496net 91

    因此,在制作excel表格里,要遵循以下原则:

    回答:

    1、数据透视入门

    新葡亰496net 92

    新葡亰496net 93

    2、随意调整分析角度,数据爱怎么看怎么看

    新葡亰496net 94

    新葡亰496net 95

    新葡亰496net 96

    2、然后依次点击-开始-条件格式-新建规则

    新葡亰496net 97

    学会利用现有的数据模板

    新葡亰496net 98

    您没提供更多关于原始数据的信息。那么我假设有两种情况。

    3、除了求和,还可以做各种计数、平均、百分比等分析

    新葡亰496net 99

    新葡亰496net 100

    新葡亰496net 101

    4、我们点击[Data]列的展开按钮,去掉使用原始列名作为前缀的勾选,点击确定。

    原始数据表只输入数据,不进行任何统计和运算

    把原始数据表复制一份,对复制的表格文件进行统计和运算,确保原始数据的安全。

    新葡亰496net 102

    原始数据是每天一个表格

    这种情况用powerquery即可。必须确保第一行是表格标题,之后的每一行都是一条完整的记录。当然,如果表格是其他样式的,也可以处理,只是多了些步骤而已。

    将这些表格丢在一个文件夹或者每个月一个文件夹,都没问题。

    在PowerBI Desktop中新建一个基于文件夹的powerquery查询连接,成功后powerquery显示如下:

    新葡亰496net 103

    然后对数据进行清洗、规范化,建立数据模型,添加需要的度量值,最后形成的报表示例如下:

    新葡亰496net 104

    可以根据月份和每月的第几周来筛选数据,如果保持筛选器全部不选中,则为总数据。这只是个示例,还可以基于已有的数据模型建立更多丰富的可视化图表。

    当原始数据更新后,只需要刷新下更改结果就自动出来了。如果是使用了powerbi的网络服务,还可以自动刷新,无需手动刷新。

    在excel中可以实现类似效果,操作方式基本一样。

    4、切片器——超级便利的数据筛选按钮

    新葡亰496net 105

    新葡亰496net 106

    3、点击“使用公式确定要设置格式的单元格”,在下面对话框中输入公式:=SUM($D2:D2)<=$C2 ,同时点击“格式”,在弹出的界面中选择想要的颜色,点击确定就搞定

    新葡亰496net 107

    表格标题的设置的要求:

    表格必须有且只有一行标题。没有标题或多行标题,Excel在进行统计处理的的时候就会出错。

    原始数据在一个表格中

    这个就更简单了,只要数据规范(没有合并单元格、第一行是表格标题,之后每一行都是一条完整的记录),可以直接在excel中用数据透视表的方式汇总。

    也可以用powerbi建立更复杂的数据模型,进行更复杂的分析。

    不再给示例了。

    5、数据组合——随意地合并统计

    新葡亰496net 108

    新葡亰496net 109

    5、我们就看到数据已经被合并好了,没有用的列我们删除即可:

    每列数据格式必须是标准的格式;

    例如输入日期。Excel中能识别的标准日期格式有“2018-4-22”或者这种“2018/4/22”,不要随意的输入“20180422”这种形式的。后期,如果需要调用跟日期有关的函数时,是无法正确使用的。

    结语

    涉及多表多文件数据汇总,强烈推荐powerbi。会为你节省大量时间,确保不会加班。

    如果你愿意,你可以把你的原始数据稍微处理下,发一个示例给我,提出要求,我可以帮你做个数据模型,你拿去就可以用。免费。

    回答:

    6、双击出明细,点哪儿得嘛儿

    新葡亰496net 110
    新葡亰496net 111

    回答:

    新葡亰496net 112

    必要时使用编码代替大量文字

    当表格中需要文字描述字数较多,并且品类较多时,可以给相应对象编码简捷的数字加字母的编码,便于编辑和统计。

    最简单直接的方法,就是使用数据透视表

    数据透视表是Excel中的一个核心工具,简直就是神器啊!

    新葡亰496net 113

    你需要:

    1,规范的源数据;

    2,合理的字段设计;

    3,插入数据透视表;

    除了汇总每月数据,数据透视表能做的事情还太多太多了。

    建议你找一个数据透视表的教程,花两三个小时学习一遍,你会受益无穷的。

    style="font-weight: bold;">我是微软认证讲师MCT,微软MOS认证大师。关注我,一起玩Office。

    回答:

    有每日明细吗?有的话,可以使用数据透视分析

    7、一键搞定动态数据源

    新葡亰496net 114
    新葡亰496net 115

    对于这个题目,有不同的思路。

    6、接着我们修改下数据类型即可。
    7、我们点击关闭并上载下拉功能,关闭并上载至,按钮,我们简单解释一下,上边的加载到表,就是加载到我们的EXCEL界面,数据量在100万以内的选择这个没有任何问题。下边的【将此数据添加到数据模型】勾选后可以将查询获取的汇总数据加载到PowerPivot中,PoewrPivot配合仅创建连接表(即不直接存储数据到Excel表格区域)

    慎用合并单元格:

    合并单元格后,会使很多的表格操作都无法进行。如果需要上报,也要自己备份一份无合并单元格的表格,便于后期修改。

    8、分类汇总很简单

    新葡亰496net 116

    看示例,意思是想知道某种产品的入库,出库以及结存情况。并且把结存情况和月需求量想对比,如果低于安全库存就报警提示。示例表格是一个二维表格,并不能实现自动化,所以可以用Excel建立一个数据模型,出入库数据录入完毕后,就能看到库存情况,以及是否需要补仓。

    新葡亰496net 117

    表格文件的命名

    同部门或同单位甚至同系统内,应该使用相同的文件命名方式,方便大家之间的表格文件的交流。如:“2018年4月人事部考勤记录表”,看到文件名,就知道了文件的大概内容,一目了然。

    新葡亰496net 118

    关于这个问题,朋友们有什么补充,欢迎在下方留言。

    我是今日头条一只吃花的猫的作者。专注于Windows及Office相关操作的学习和使用。朋友们如果喜欢,麻烦点赞、评论、转发!谢谢支持!期待着与大家的交流和探讨。

    回答:

    excel规则很多需要慢慢学习哦!

    今天跟大家分享一个小技巧!

    《列字段太多怎么办?那就做个下拉菜单吧!》

    新葡亰496net 119

    Excel表格列数据太多阅读太费劲?

    鼠标左拖右拉眼花缭乱?

    那么给他个下拉菜单就够了!

    新葡亰496net 120

    举例上表中有四列数据,现在我们将对表题部分做下拉菜单,选中字段后即可选择指定列。

    1、选中整表数据后单击选择中功能

    新葡亰496net 121

    2、在对话框中勾选单击确定。

    新葡亰496net 122

    3、在单元格名称框中就会出现我们所有的列字段。

    动图演示

    新葡亰496net 123

    同理我们还可以对所有的行标题做个下拉菜单。但是意义可能不是很大,因为行本身自带筛选功能呢。

    9、行列表头合并居中,规范又美观

    新葡亰496net 124
    新葡亰496net 125

    先创建三个工作表,汇总表内容格式如下:

    8、我们先选择加载到表,看下效果,我们看到10个表460行的数据就被我们加载完成了:

    10、创新的实战应用:表间数据不规范不统一时应用数据透视极大提升对比效率

    小勤:大海,现在公司手工盘点表可麻烦了,老跟系统的数据对不上,每次盘点对帐都得费半天劲儿。他们手工录的表里货品代码就经常少一个横杠、多一个横杠的,有的“文艺”干脆就写成“文”,对起来真的很麻烦。

    新葡亰496net 126

    大海:的确很难避免这种情况,盘点任务经常是分配给不同人去完成的,现场盘点人员水平参差不齐,都是自己手工习惯的表,短时间估计也很难规范统一,数据汇总后就有各种问题,你很难用公式去匹配。这个时候其实可以考虑用数据透视,先对大类,看看哪些大类是对不上的,然后再针对有差异的大类对明细,缩小对照范围。你现在可以试着把2个数据透视都放到一张表里看看。显然,大类的时候“厨具”、“卫生”、“文艺”三类都有差异。

    新葡亰496net 127

    我们再把细类放进来,然后把没有差异的大类通过筛选去掉。

    新葡亰496net 128

    用同样的方式筛选手工盘存数据透视表后进行对比,细类里有差异的地方也就这些了。

    新葡亰496net 129

    小勤:嗯,现在范围很细了,只要核对“锅”、“卷纸”和“相册”就可以了。

    大海:对的,这个时候双击出明细就好用了。比如先核对“锅”的。分别双击“锅”的统计数据,生成明细(为避免搞混2个明细表,生成时最好重命名一下):

    新葡亰496net 130

    明细如下:

    新葡亰496net 131

    为方便2表之间的核对,可以新建窗口:

    新葡亰496net 132

    然后重排一下:

    新葡亰496net 133

    新葡亰496net如何用Excel做每月的数据汇总表,制作excel表格有什么规则。在2个垂直并排的窗口中分别选中系统表和手工表进行对比:

    新葡亰496net 134

    显然,差异就是这个了。用同样的方法可以找到其他差异的地方。

    小勤:嗯,这样一步步缩小范围,对不规范的数据对比真能省不少功夫。

    大海:对的,所以表间的数据对比得看情况。如果数据都很规范,那就简单了,当数据不规范的时候,就要想办法逐步缩小范围。


    欢迎关注

    私信我即可下载60 基础函数、10 数据透视及新功能Power系列功能汇总训练材料

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

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

    回答:

    要做excel表格透视表,首先从创建数据透视表开始。

    新葡亰496net 135

    新葡亰496net 136

    创建数据透视表

    步骤:选中数据,点击--,选择位置。再直接拖动字段到指定区域。

    新葡亰496net 137

    出入库表格式一样,格式为:

    9、我们探索下100 的表格的合并效果以及我们PowerQuery的优势,我们复制原始数据10份,在待合并文件夹。

    增加新字段

    如下图,表中已知销量和单价,可增加并计算出销售额。

    步骤:点击数据透视表,--,输入,输入,点击添加。新葡亰496net 138

    新葡亰496net 139

    新葡亰496net 140

    刷新数据

    原表格数据进行修改,数据透视表可以刷新数据。

    步骤:1、选择--,选择位置。

    2、修改数据后,点击数据透视表,-。新葡亰496net 141

    汇总表中蓝色底纹区域,均为函数自动生成,不需要手动输入,其中当日数据反映的是当日的情况,累计数据反映的是累计出入库的情况。结存的数据为期初 入库累计-出库累计。

    10、我们需要重新做一遍刚才的工作吗?NO!NO!NO!我们只需要刷新下我么的数据源即可,以下方法学习一种即可
    刷新方法1:数据标签下边的全部刷新功能

    仅对行数据汇总

    数据透视表可以仅对行数据汇总。

    步骤:如图,对进行求和,数据透视表,将拖动至,出现行与列的总计。点击--。新葡亰496net 142

    当日数据用的是多条件求和函数sumifs,累计数据用的是条件求和函数sumif,函数公式为:

    新葡亰496net 143

    批量创建工作表

    数据透视表可以快速创建多个工作表。

    步骤:1、输入需要创建工作表的名称;

    2、选择-,勾选之前所输入的工作表名称,拖动到;

    3、选择---。新葡亰496net 144

    回答:

    n

    {!-- PGC_VIDEO:{"thumb_fingerprint": 7651297475218376071, "vid": "273e3957441f4ab1940901fa83d21f51", "vu": "273e3957441f4ab1940901fa83d21f51", "duration": 4242, "thumb_height": 360, "thumb_neardup_id": 9076112635957273149, "neardup_id": 5382587299326459173, "video_size": {"high": {"duration": 4242, "h": 480, "subjective_score": 0, "w": 640, "file_size": 58412277}, "ultra": {"duration": 4242, "h": 720, "subjective_score": 0, "w": 960, "file_size": 79551849}, "normal": {"duration": 4242, "h": 360, "subjective_score": 0, "w": 480, "file_size": 50413565}}, "vname": "\u7b2c\u516d\u8bb2.wmv", "hash_id": 5382587299326459173, "status": 0, "media_id": 1579335960823821, "thumb_width": 480, "external_covers": [{"mimetype": "webp", "source": "dynpost", "thumb_height": 360, "thumb_url": "5c180000c6346295ea17", "thumb_width": 480}], "item_id": 6514912803189424654, "user_id": 6278328253, "thumb_url": "5c1000141164c2849957", "md5": "3996ff8940b5010a2a166c3805f92d50", "vposter": "", "src_thumb_uri": "5c1000141164c2849957", "sp": "toutiao", "group_id": 6514912803189424654} --}

    当日入库:=SUMIFS(入库表!$E$2:$E$9,入库表!$D$2:$D$9,汇总表!B4,入库表!$C$2:$C$9,汇总表!$C$2)

    刷新方法2:在当前表格里右键,点击刷新

    创建数据透视表、改汇总方式、汇总多列、自动创建透视表,看完视频全懂了!

    累计入库:=SUMIF(入库表!$D$2:$D$9,汇总表!B4,入库表!$E$2:$E$8)

    新葡亰496net 145

    私信回复24获取课件,边看边练!

    回答:

    选择需要做成透视表的数据源区-->进入“插入“-->”数据透视表"-->点击即可完成.

    新葡亰496net 146

    当日出库:=SUMIFS(出库表!$E$2:$E$9,出库表!$D$2:$D$9,汇总表!B4,出库表!$C$2:$C$9,汇总表!$C$2)

    刷新方法3:在右侧查询界面,右键刷新,如果没有看到右侧的工作表查询的话,点击数据标签下的显示查询按钮即可

    累计出库:=SUMIF(出库表!$D$2:$D$9,汇总表!B4,出库表!$E$2:$E$8)

    新葡亰496net 147

    图标集的数据是用累计结存和月需求量想对比,如果是正数就是绿色箭头,如果是负数就是红色箭头。

    11、点击刷新,我们等待不到2s钟的时间,100份报表就帮我们汇总完成了,有兴趣的同学也可以测试下1000份。

    图标集的使用方法是,选中改数据区域,点击条件格式——图标集,选中其中一个,然后点击编辑规则。设计大于零时显示为绿色箭头,小于零时显示为红色箭头,并勾选只显示图标集。

    新葡亰496net 148

    新葡亰496net 149

    关于PowerQuery文件夹的合并就介绍这么多,如果原始数据非Excel格式的话而是Csv的话,把Excel.Workbook替换为Csv.Document([Content],[Delimiter=”,”, Encoding=936]),其他操作都一样。
    接下来我们探索下如何利用这个方法实现千万级别的数据透视分析
    1、我们修改原始文件,中两个表的数据量到92万行,保存关闭

    经过这样的设置以后,就可以实现输入完毕原始数据,就能自动汇总,并显示月需求量和库存量的关系。当然如果想设置安全库存提醒也可以用函数来实现。

    新葡亰496net 150

    新葡亰496net 151

    2、直接刷新,我们看到表格会报错,因为我们可以看到右侧加载了185万行数,Excel文件里直接放不下了,好了我们看下怎么处理吧。

    最后需要注意的是:sumifs函数只能在Excel高版本中才能使用,图标集也是适用于Excel的高级版本。

    新葡亰496net 152

    如果你需要这个源文件,私信我,我分享给你。如果你不懂如何去用,也可以留言或评论。期待与你交流。

    3、我们在我们右侧我们的查询上点击右键,选择【加载到】,然后如下图所示选择,点击确定。

    新葡亰496net 153

    4、我们点击PowerPivot下的管理按钮,查看下我们的数据,如果没有PowerPivot标签点击Ecxel的【COM加载项】勾选下即可,注意需要13或者16专业增强版以上才有此功能:

    新葡亰496net 154

    新葡亰496net 155

    5、我们185万的数据就被存储在Excel的PowerPivot中的,接下来我们点击插入透视表,选择使用此工作簿的数据模型为源数据即可。

    新葡亰496net 156

    6、我们简单拖拽看下效果,一个185万行数据产生的透视表就被我们做出来了。

    新葡亰496net 157

    新葡亰496net如何用Excel做每月的数据汇总表,制作excel表格有什么规则。总结:
    1、快速合并文件夹文件,我们看到我们仅需一个函数Excel.Workbook配合可视化的一些操作即可完成。2、后续只需要一键刷新即可完成汇总。3、通过Excel插件PowerPivot的配合我们完成了185万行数据的透视表的创建,对传统Excel分析的能力做了一个很大的突破。
    其实关于Excel中商务智能系列套件对我们未来的很多分析业务需求提供了更多的可能,本文所用示例文件可以加群198086726获取,更多文章欢迎关注【简快Excel】(PowerBIPro)如果本文对你有帮助,欢迎点赞转发。

    本文作者:雷公子,如需转载请联系授权

    本文由新葡亰496net发布于新葡亰496net,转载请注明出处:新葡亰496net如何用Excel做每月的数据汇总表,制作

    关键词: