郑州股票分析联盟

【主调】除了参差不齐的面板数据,我还没见过什么东西能让人气到撞墙

托兰格回响2020-07-31 10:42:36

本来想着这周末推一期关于女权运动的简单评论,蹭一波热点。然而看了看,在我的视野范围内关于女生节与妇女节的争论已经没有两年前那样轰轰烈烈了。所以这个热点不蹭也罢,将来另外做推送吧。

直接促成这篇推送的内容的是从我从昨天晚上开始的万分痛苦的经历。为了整理一个项目的结项报告所需的数据,我花费了几个小时时间重复极其简单的设置、下载、复制、粘贴、筛选、比较、汇总工作,期间还因为手滑出现了很多错误和波折,终于在得到一个存在许多瑕疵的可以用的数据表之后,我决心研究一下怎么用Excel来实现对“参差不齐的面板数据”的整理。

因为我还不怎么会用Python……


首先来举例说明一下什么叫做参差不齐的面板数据,以及为什么它让人头大。



这是一组胡乱打出来的数据。现在强行假设李华想研究一下从第二到第五列这四个因素的值之间的关系,然而刚准备动手的时候李华就傻眼了。

这补丁一样的数据该怎么用啊?!

于是李华放弃了尝试这种不靠谱的研究,开始写英语作文了。


当然实际的研究中所收集到的数据并不会像上图所示那样支离破碎,但是鉴于很多情况下我们需要从不同的来源收集数据,难免出现对于研究对象在研究时间内的因素数据统计的错位情况。这句话的断句是:难免出现{对于[研究对象(在研究时间内)的因素数据]的统计}的错位情况。甚至在一些情况下,即使是来自于相同机构的数据,也因为数据可获得性的差异,出现类似的错位。这种错位的表现是,一些对象的一些因素在一些时间点没有数据。换句话说,就是面板上破了几个洞。

如果数据量比较小,比如研究对象数与研究时间点数相乘的数量也就是样本数在200以内,这些有破洞的地方都可以手动清除。如果要研究连续几年的数据,那么如果某个对象在所研究年份中有数据缺失,那么这个对象的所有数据都需要被清除,这样一来,的工作量就变大了;而如果样本数成千上万,用手动清除是无法完成的事情。即使用筛选功能,操作量也非常大。

然而更困难的地方在于,“有破洞的面板”本身就不好得到。因为数据来源不同,各因素的表格不可能空出其他因素的数据的位置,如果直接拼合起来,那么一行的各列数据所描述的就不是同一个样本。在数据量大的情况下,这个问题几乎无法通过手动匹配粘贴来实现。

那么有没有一种方法,使用Excel内置的函数辅助筛选,能提高工作效率,让像我这样缺乏技术积累的学生免除机械工作的痛苦呢?


下面多图预警。为了说明这套方法的实用性,我直接在项目所使用的数据上进行操作。


首先明确数据整理的目标:所需要的数据是给定企业在2011年到2016年每年年末的财务报表指标和财务分析指标。这些数据已经分别下载好了。

所选择的企业是以股票代码的方式给定的。所下载的数据分为母公司报表和合并报表,用A和B区分。

整理的过程主要有两部分,第一是筛选因素完备的样本,也就是所有八项指标都有数据的样本;第二是筛选时间完备的企业,也就是所有六年都有数据的企业。而这两步筛选都要建立在一个共同的基础上,这个基础就是包含所有因素的大面板,也就是带有“破洞”的面板。


—————开始操作—————


这张表是将不同因素的表直接并联形成的。可以看出来,一行的数据有些并不属于同一个样本。



表格的下缘,参差不齐。右侧的数据则比左边要少很多。



将数据分开到六个表中。

需要整理的总共有八个因素,不过其中有两组两个因素是同时输出的,所以各自合在一张表里。



为了实现对数据的抽取,这是关键的一步。因为MATCH函数对数据的查找无法同时验证编号、日期、报表类型三个条件,所以需要用合并公式把三个条件合成一个,输出在G列。G列的选取考虑到有些表格有两列因素数据,所以留出了空位。



得到结果。自动填充的操作是,选中第一个ID,拖动右侧滑块到最后一行数据,按住Shift键点击最后一个ID的位置,然后按Ctrl+d。

可以看到,对于每一个样本,合并条件也就是ID都是不相同的。这样一来就能够实现按照ID来查找任意一条样本。

(不是很清楚时间信息是怎么被处理成数字的。)



将ID公式复制到每一个因素表的G列。

统一在G列是为了后续输入公式的时候不必反复调整。



任意选取一个因素表(图中选取了第六张表),使用筛选功能来得到研究所需的样本。

之所以任意选取,是因为同时在各张因素表中有数据的样本,必然存在于任意一张因素表中。当然在操作中可以选择样本量最少的一张表,以简化后续操作。

图中,选择每一年的12月(31日)数据。

同时,所需要的数据是合并报表数据,所以在报表类型一栏选A。



此时显示的就是第六张表里在研究时间范围内的样本了。

注意,此时的样本在研究时间范围内,但并不意味着所有出现的企业都具有全部六年的数据。时间完备的样本要在最后进行筛选。



将编号与时间两列粘贴到新表中。

在第一行填入所需要的变量。最好与最终分析时需要使用的数据表的表头一致。

这一张表用于筛选具有全部八个因素的数据的样本。



从第六张表中复制样本对应的ID。



构造面板的关键步骤:使用INDEX函数MATCH函数的组合。



综合税率数据在第四张表上。使用跨表引用。

D:D的意思是所有D列的数据。

$表示绝对引用。因为每一张表的数据都在D列(有些在E列也有数据),如果不绝对引用,公式向右复制的时候,数组指向的位置就没有数据了。

之所以选取所有D列的单元格,不仅仅是因为懒。原因有两个:①每张表的数据数量各不相同,无法确切规定引用区域的下限;②之后还要引用ID数据,所选区域直接从第一行开始也避免了错位。



MATCH函数。在这里的用处是,通过寻找本行样本的ID在因素数据表里的位置,为INDEX函数提供指引,从而获得本行样本的对应因素数据。

查找值即为本行样本ID。



查找区域是因素数据表的ID所在的那一列。对于所有的因素数据表,ID都在G列,这在前面提到过。

同样使用绝对引用。



匹配类型输入0,表示精确匹配。



可以看到数据已经过来了。



向右复制公式,调整表格序号,得到各因素数据。

对于在E列的数据,还需要调整INDEX的数组位置。



完成所有因素数据的公式设置。




向下填充,数据全部都有了。

现在的数据就是在所研究的时间内的所有数据。

这时候需要大致检查一下数据,如果出现#N/A则说明对应的因素数据表中ID列有缺失,公式无法找到,需要补上。



接下来需要筛选出因素完备的样本

在右侧建立公式,计算数据表区域内非空单元格的个数减去内容为0的单元格个数。

注意:一般来说,如果原始数据缺失,那么上述的数据抽取结果会显示为0。然而有一些因素数据本身是可以为零的,比如最后一列的“税收返还”,那么在计算内容为0的单元格个数的时候就不应该包括在内。



得到判断结果,Y表示第一行的数据是因素完备的。



向下填充。可以看到一些缺失数据的样本被标记为N。

之后再次使用筛选功能,将因素完备的样本数据复制到新的表上,进行时间完备的筛选



在右侧建立公式,计算本行样本的企业编号在所有企业编号中出现的次数。

因为从上一张表开始的所有数据都是在2011到2016年中的,所以最多只会出现6次。如果出现次数少于6次,说明缺失某些年份的数据。



筛选,复制粘贴到新表上。此时所有数据都是可用的了,符合所有要求。

看到整齐的末端是不是感到心旷神怡啊?



—————整理方法介绍到此结束—————


在做完上面的操作之后,我又重新做了一下昨天晚上的另一批数据。尽管有心理准备但是还是令人十分吃惊,前一天用了快四个小时整理还错误百出的数据,直接套用上面的表格,十分钟就得到了筛选干净的结果。从九千多条数据到一千多条数据,用鼠标滚轮滚动浏览都得半天,使用函数竟然如此轻松。


之前也在票圈问过类似问题的处理方法,有同学和师兄指导说用Python的Pandas工具包可以做到。然而考虑到很多同学(比如说我)尚未掌握Python的使用方法,而更多地还是使用传统的Excel,我觉得这份经验还是很值得传播的。


既然是托兰格回响的推送,上纲上线是必须的。这两天的经历让我深刻体会到了什么叫做无创造性的工作,什么又叫做创造性的工作。在创新之前,谁也不知道创新的成果什么时候能出现,投入的时间又能有多少回报。此时很多人就选择了保守的传统方法,以为只要有决心和毅力,就能够通过简单数据搬运劳动的堆砌达到目的。然而事实证明了,对于软件功能的使用,乃至软件本身的创造才是先进生产力。

向广大程序员同志们致敬!