数据透视表学习-07:数据源常见问题及解决办法

数据源是透视表之源。没有了数据源,透视表也就无从创建。数据源杂乱,透视表计算过程就会变得复杂,计算出的数据还极可能出现纰漏,99%的数据源杂乱问题都是和字段有关的。

1 字段名缺失

所谓字段名,也就是标题;字段名缺失,也就是标题缺失,这应该是最常见的一种问题。

有时候确实是用户疏忽了,标题没填,比如下图,B1单元格标题缺失。


字段名缺失

但更多时候是由于用户使用了多行表头,或者标题行使用了合并单元格。

多行表头

透视表是默认数据区域的第一行为标题的,它并不承认也不接受多行表头以及合并单元格格式的标题等。碰到这种情况,我们只能改变自己,适应世界,修改标题,适应透视表规则。

2 字段名重复

字段名重复

比如上图所示,有三个同为“成绩”的字段名。透视表对此——是接受的,但它会自动对重复的字段名进行编号,阿大、老二、小三……成绩1、成绩2、成绩3这样。



尽管如此,还是建议大家不要使用重复的字段名。毕竟重复的字段名可读性较差,容易造成字段混淆。

3 日期字段格式错误

当字段类型为日期时,日期格式不规范,会被Excel错将日期视为文本值。我们既无法正常使用透视表的日期组合功能,也无法准确的按日期维度对数据进行统计分析。

将错误的日期格式转换为正确的格式有很多种方法,最常用的有两种,一种是查找替换,另外一种是分列。

查找替换主要针对日期值使用了错误的分隔符,比如使用了半角句号作为年月日的连接符:2019.6.21。
分列主要针对日期的结构进行转换。比如,当错误的日期格式是YMD结构时,在【分列向导第3步】的日期设置框中选择YMD即可。如果错误的日期格式是YDM结构,则选择YDM。(这个问题在上一章中有提到,可去参考)


查找替换.gif

4 数值型字段格式错误

本是数值型的字段被误作成了文本值,在透视表中是无法正常统计求和、求平均、求极值的。

数值是文本格式

5 字段的数据类型不统一

字段类型不统一是指一个字段出现了2到多种数据类型。如下图所示,在数值型成绩列出现了文本值罢考、缺考、作弊等文本值。


字段数据类型不统一

我们在第2次学习时讲过,当字段类型为数值时,透视表默认放入值区域,并默认采用求和的汇总方式。当字段类型为文本时,透视表默认放入行区域,如果放入值区域,则默认使用计数的方式。

当字段类型出现混杂的情况时,即无法使用以上规则,方便操作,也无法便捷的使用数值的组合功能。所以通常建议修改数据类型达成一致,比如此例中,将罢考,缺考、作弊替换为0。

批量处理不同的数据类型问题,我们通常使用Excel的定位功能。见第6次学习内容。

7 字段记录缺失

字段记录也就是数据的明细,有时候会见到整行空白,有时会见到个别空白单元格。

由于透视表默认是选取活动单元格相连区域作为数据源范围的,当数据明细存在整行空白的情况时,透视表将无法自动选取全部数据范围,需要我们手动框选,容易造成计算数据的遗漏。



对于整行空白,可以通过筛选的方式,多列筛选出空白单元格,然后整行删除。当列数较多时,也可以使用辅助列的方式筛选删除。

在辅助列输入以下函数公式,即可判断数据明细是否整行为空白。

=COUNTBLANK(数据行)=COUNTA(标题行)

如下图所示C2:E2是数据行,C1:E1是标题行,标题行需绝对引用。


由于透视表在计数时,是忽略空白单元格的,因此当数据明细个别单元格为空白时,可能会造成不符合预期的计数错误。通常建议采用定位空格的方法,将空白单元格填充为合适的数据。比如数值型字段填充为0。

定位空值
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 212,222评论 6 493
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,455评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,720评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,568评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,696评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,879评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,028评论 3 409
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,773评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,220评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,550评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,697评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,360评论 4 332
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,002评论 3 315
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,782评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,010评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,433评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,587评论 2 350

推荐阅读更多精彩内容