图片 57

有些朋友后台给小奚留言说很喜欢之前讲的透视表内容,就是做好一维的源数据表

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

小技巧:阿拉伯数字变大写文字

9、数据源汇总不能出现字段数据格式不一致

数据源中存在字段数据格式不一致,在进行数据透视时,则出现多字段分类汇总:

图片 1

说明:数据源中的“平台”中应该是“A/B/C”三个类别,但是由于数据源中“平台”字段中两个平台的名称前面多了空格,和其他的名称格式不一致,则透视表默认为其他类别,进行分类求和汇总。

处理:删除空白,保证分类字段格式一致

Excel表格的基本操作技巧

17  透视表模板套用

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

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

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

图片 2

Ch 1 换个角度玩Excel

11、数据源中不能出现日期类数据格式不统一

数据源中存在日期类数据,在进行数据透视时,则无法识别日期类别,尤其以数据库系统导出数据和网页下载数据为特殊:

图片 3

处理:将分类的日期列进行“按照日期格式”的分列,保证格式为常见的日期格式

图片 4

打开文件夹,未保存的文件就找到了。打开后另存就OK。

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

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

图片 5

2、填写顺序

2、数据源不能存在空白行

数据源中存在空白行,在进行数据透视时,出现多余空白行分类汇总、数据计算:

图片 6

处理:删除空白行

image.png

删除透视表只需要全选透视表,直接按detele键就能全部删除。

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

小技巧:删除列

5、数据透视表刷新-数据源更新

图片 7

说明:每次手动更新数据源时,由于数据源的“行数”局限,每次需重新更该透视表的数据区域,避免更新的新区域,未进入透视计量范围内。

图片 8

真聪明,在Excel里面已经内置了一些透视表的模板,我们可以选择自己喜欢的模板,直接套用就行了,还是在【设计】选项卡里面。

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

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

小技巧:批量录入

(1)不显示分类汇总

图片 9

说明:不显示所有的分类汇总

image.png

复制透视表的情况其实蛮常见的,因为有的时候选取的数据源是相同的,需要做不同维度的汇总分类,如果不想重新新建sheet,那么复制透视表后在这基础上更改字段是最好的方法。

6、不含有合并单元格

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

1、标题的位置

三、数据透视表常见使用

image.png

第二种方法是需要在源表建立辅助列,然后用VLOOKUP的模糊匹配,这个我们会在下一次讲到。

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

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

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

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


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

回答:

谢邀!

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

图片 10

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

给大家总结一下这本书的核心内容。

3、数据透视表刷新设置

刷新数据分为手动刷新和自动刷新

操作在透视表中的任意区域,单击右键,选择“刷新”,刷新一个透视表。

图片 11

重排后效果

07  修改行列字段顺序

表格文件的命名

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

图片 12

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

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

回答:

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

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

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

图片 13

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

鼠标左拖右拉眼花缭乱?

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

图片 14

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

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

图片 15

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

图片 16

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

动图演示

图片 17

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

注:此方法只适用于转化整数,带有小数的无法正常转化

1、数据源不能存在空白列

数据源中存在空白列或空白分割列,在进行数据透视时,出现拦截报错:

图片 18

处理办法:将空白列列头进行“辅助填充”或“删除空白列”。

图片 19

【先做一个辅助表】-【文件】-【选项】-【高级】-【常规】-【编辑自定义列表】-【选择最开始建立的辅助表】-以后就只需要排序就能按我们希望的字段顺序出现了。

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

图片 20

图片 21

步骤1:选取销售员一列需要设置下拉菜单的单元格区域(这一步不能少),打开数据有效性窗口(excel2003版数据菜单

【问】:为什么我不能对透视表的日期进行月、季度、年的分组呢?

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

小技巧:数据列的移动

(3)在组的顶部显示所有的分类汇总

类似“在底部显示所有分类汇总”,将所有的汇总行显示在每个组类别的顶部。

20.快速调整最适合列宽

【提问】:为什么小奚选择的是在组的顶部显示分类汇总,最后Excel还是在组的底部显示的分类汇总呢?要结合我们前面讲的内容哦,知道答案的同学请大声在留言区告诉我吧!

回答:

如果没有数据就填写「0」或者填写文字说明,一定不能空着什么也不写

5、数据源中不能存在列汇总求和单元格

数据源中存在列汇总单元格,在进行数据透视时,只默认求和合并单元格的首行的对应数据,剩余行为空白。

图片 22

处理:去掉透视数据源区域的求和所在列

显示后效果

但是在实际运用中,特别是对于金额段的实际运用,我们常常需要的是不等距组合,比如:1000-2000金额段,2000-5000金额段。对于不等距的组合我们该怎么操作呢?

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

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

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

未完待续···

12、数据源中透视区域选择不全

数据源区域,在进行透视时,选择区域不全,导致透视不全面:

图片 23

处理:重新选择数据源区域,涵盖所有记录


1、隔行插入空行

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

3、分隔列

一、数据透视表使用数据源要求

图片 24

好了,废话不多说,直接上操作:【右键】-【数据透视表选项】-【布局和格式选项卡】-【合并且居中排列带标签的单元格】(注意:合并行标签只对表格形式布局有效,对大纲式和压缩式无效,不信你可以试试哟!)

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

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

图片 25

标题放哪里呢?要么写在工作簿中,要么写在工作表处

1、数据透视表-报表布局格式设置

数据透视表的报表布局格式常见有5种,即压缩报表形式、大纲报表形式、常见报表格式、重复所有项目标签格式、不重复所有项目标签格式。

下面的演示分为两部分:

行汇总百分比,例如:老板想知道单个销售在每个产品的上售卖金额占比是多少,我们就需拉行汇总百分比给他看。

表格标题的设置的要求:

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

图片 26

(2)数据透视表进行“值显示方式”的分类计量

图片 27

说明:“值显示方式”可以快捷的数据进行层级分析,2016版本的Excel数据透视表内有14种显示方式,但是常见的百分比分析是最常使用的分析利器。

例如:按照列汇总的百分比显示:

图片 28

图片 29

不等距组合有两种操作方式:

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

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

类似于第一宗罪

7、数据源中不能存在重复字段信息

数据源中存在重复字段信息,在进行数据透视时,难以区分字段具体信息,不便于计量:

图片 30

处理:尽量保持字段名称唯一

12.同时修改多个工作表
按shift或ctrl键选取多个工作表,然后在一个表中输入内容或修改格式,所有选中的表都会同步输入或修改。这样就不必逐个表修改了。

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

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

这样行与列就互换了,筛选出你想筛选的行,再「转置」回来就行了。

(1)数据透视表进行重复记录统计

利用数据透视表统计数据源中出现“平台”类别的个数:

图片 31

说明:数据源中各个平台的重复个数按照计数统计,例如,A平台是“阿里平台”一共出现6次,即表示该报表中有6个产品是属于“阿里平台”的。

选取A列区域 – ctrl+g打开定位窗口 – 空值 – 删除整行

嗯,确实是这样,有的时候我们并不需要看分类汇总,但是透视表会自动显示,有的时候我们甚至都不需要看总计,那么怎么隐藏和显示分类汇总和总计呢?还是在【设计】选项中哦!

4、信息格式正确

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

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

图片 32

Ch 2 十宗罪进行时

6、数据透视表的可视化设置

数据透视表建立之后,可以联动数据透视图,可认为是数据透视表的可视化,

通过不同的字段维度筛选,实现多维度的数据展示。

图片 33

数据透视表是结合Excel进行数据分析必备神技之一,左手VLOOKUP,右手数据透视表,召集神技,数据分析不在话下。

(注:2017.10.15,台风天的工作技能积累打卡,记录点滴数据分析成长之路,纵使分析工具换过千千万万,Excel之美,难相忘,感谢老大的启蒙!不足之处,望见谅,后续更新)**

Excel表格的基本操作技巧

END

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

比如将表格中的月与日分开,利用「数据」—「分列」

5、数据类别分组设置

在建立数据透视表之后,在跟踪日期的汇总时,需将日期按照分组进行分析,则需将日期进行组合分类,即将日期分为年、月、日、季度或自定义分组的天数进行分类汇总。

说明:2016版本Excel新增自动按照月度的数据汇总

操作:数据透视表的按照日期列,右键-“创建组”-“分组”-“自动”-“起始于”-“终止于”-“步长”。

图片 34

分组结果为:

图片 35

注:演示过程中打开定位窗口的组合键是 ctrl + g

那我们就要从透视表的三类展示姿势开始说起了,这三类布局分别是:压缩形式、大纲形式、表格形式。在哪里找到这三类布局呢?

慎用合并单元格:

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

图片 36

文|仟樱雪

image.png

当然,大多数的同学可能会对合并行标签更感兴趣一点,因为小奚发现,大多数的同学在操作Excel的时候,对合并单元格尤为热衷。(虽然合并单元格一直被称为Excel处理数据时的一大杀手)

学会利用现有的数据模板

图片 37

很多朋友喜欢将表格的第一行留给标题,选中单元格—合并居中—写上标题,以前我也这么干,但这样做其实破坏了源数据表。

二、数据透视表的基本格式设置

图片 38

有些朋友后台给小奚留言说很喜欢之前讲的透视表内容,问能不能整理出一个Excel透视表的合集,只要是透视表的内容都能在里面找,今天小奚就给大家带来了这篇文章。

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

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

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

图片 40二维表通常是在一维表基础上加工而来,所以一维表是基础。

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

整章只有一个中心思想,就是做好一维的源数据表,以不变应万变。什么是一维源数据表呢,就是一张明细表,不使用任何公式。

6、删除数据透视表

操作:“分析”-“操作”区域-“选择”-“整个透视表”-按Delete键,即可删除数据透视表。

图片 41

19.快速复制公式

例:

1、数据透视表进行数据过滤

image.png

【问】:既然可以Ctrl键选中想要分组的内容来分组,那么如果对日期来分组是不是也可以这样呢?

6、源数据表中不要合并单元格

神技1:数据透视表使用数据源要求

11.同时查看一个excel文件的两个工作表

其实小奚曾经也是这么干的,当时字段还特别多,小奚拿着鼠标点右键,上移下移了无数次,差点没崩溃。

图片 42

4、数据源不能存在行合并单元格

数据源中存在行合并单元格,在进行数据透视时,出现分类汇总、数据计算错误:

图片 43

说明:数据源中存在合并单元格时,进行透视分类汇总时,只默认求和合并单元格的首行或首列的对应数据,剩余行或列则为空白。

处理:拆分合并单元格,然后向下填充,形成完整行列数据格式。

图片 44

有眼尖的同学已经发现啦,其实压缩形式就是我们Excel默认的透视表格式,它主要的特点呢就是:

不要在源数据表中做合计,破坏数据的完整性

(3)数据透视表切片器筛选设置

操作:“数据透视表工具”-“分析”-“插入切片器”-“选择筛选字段”

图片 45

筛选切片器设置:

图片 46

说明:根据切片器的筛选,控制透视表的显示区域,进行数据过滤。

图片 47

(友情提醒,多图,请在wifi下观看!流量壕请无视本条)

8、将源数据记录在一张表中,不要分成N张表进行记录

(5)以不重复所有项目标签的形式显示报表

图片 48

说明:该形式报表将所有重复记录进行不显示设置。

进行如上设置后,我们就可以在销售员一列看到下拉菜单了。

看完动图大家应该比较清楚在哪里显示和隐藏分类汇总和总计了吧?

最近在看伍昊的《你早该这么玩Excel》,不太记得自己当时为什么会买这本书,可能是受了亚马逊推荐书单的蛊惑吧。

神技2:数据透视表的基本格式设置

图片 49

列汇总百分比,例如:老板想看每个销售在单个产品上的占比是多少,我们就需要拉列汇总百分比给他看。

每次你合并单元格时都会提示你只保留第一个单元格的数据,因为你合并以后,Excel只能识别第一个单元格的数据,其他被合并的单元格数据都为空。

8、数据源中不能出现数据类型未转换

数据源中字段的数据类型不一致,即分类汇总求和列的数据存在文本和数值格式(存在带“绿帽子”数据和不带“绿帽子”数据),在进行数据透视时,分类汇总求和会变成计数:

图片 50

处理:将数据源求和列,进行数据格式一致性处理,即统一为不带“绿帽子”的数值类型数据进行求和,分列处理即可。

14.快速关闭所有excel文件

【问】:小奚,透视表自己出来的行列字段的顺序有的时候并不是我们想要的顺序,是不是只能【右键】-【移动】-【上移/下移/移至开头或结尾】?

图片 51

(2)数据透视表类别筛选设置

图片 52

说明:将类别拖入“透视表字段”的筛选区域,则可进行类别筛选,进行数据过滤。

为什么我测试没有恢复成功?你是怎么知道恢复文件的路径的?

当你想要对列与列之间的数据做区分的时候,请巧用边框线,千万别空出一列来作为分隔栏,这样会破坏数据的完整性。


图片 53

「在Excel默认的规则里,连续数据区域的首行为标题行,空白工作表的首行也被默认为标题行」

关于透视表的运用,是办公必备技能中除了VLOOKUP之外必备的神技之二,分分钟让数据无处遁形,主要的精髓如下

Excel表格的基本操作技巧

表格形式的透视表是小奚最常用的一种形式。它的主要特点呢是:

如何将2134写成「贰仟壹佰叁拾肆」

3、数据透视表进行数据计算

数据计算:在数据透视表中新增计算计算,根据函数逻辑新增。

例如:平台销售日报中的收入、数量、单价、成本等字段,现需计量每个产品的利润、利润率字段,进行产品毛利的分析;

操作:“数据透视表工具”-“分析”-“字段、项目和集”-“计算字段”-输入字段名称-输入公式“=收入-成本”。

图片 54

13.恢复未保存文件
打开路径:C:\Users\Administrator\AppData\Roaming\Microsoft\Excel\
,在文件夹内会找到的未保存文件所在的文件夹,如下图所示。

操作:将以文本形式储存的数字按数值类型储存,方法是:选中-分列-完成,刷新透视表即可。

10、汇总表误用手工来做

3、数据源不能存在列合并单元格

数据源中存在列标题合并单元格,在进行数透视时,出现“单元格引用错误”报错:

图片 55

处理:引用数据源,去掉标题区域

15.制作下拉菜单
例:如下图所示,要求在销售员一列设置可以选取的下拉菜单。

上篇:基础操作

就目前看下来的情况,并不推荐大家买这本书,感觉作者写了很多「废话」来凑成这本书。

10、数据源中数据不能带单位透视分类汇总

数据源中存在带单位数据,在进行数据透视时,则求和默认为计数,更改为求和时,默认带单位的数据为文本类型数据,无数据呈现:

图片 56

处理:去掉单位,进行透视分类求和汇总

排列方式

更高级的用法

将月与日合并,利用「&」符号

2、数据透视表-分类汇总格式设置

数据透视表分类汇总格式,常见的有3种,即不显示分类汇总、在组的底部显示分类汇总、在组的顶部显示分类汇总。

设置重排窗口

4、多余的合计行

(1)数据透视表进行各种计算口径的分类汇总

图片 57

说明:点击数据透视表的“数值”字段,出现“值字段设置”,出现各种计算口径的计算设置,可进行最大值、最小值、计数、求和等的计算。

注:以上内容为作者整理加总结得到,如有异议,可与作者联系,仅供大家交流学习

如果存在多个字段的情况下,怎么筛选出老板想要的呢?直接使用行标签去筛选是行不通的,解决方法是:将鼠标点在列标签外面一格,然后使用筛选功能,具体操作请看下图。

发表评论

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

相关文章