《最简单的现金流水账数据透视表分析法.docx》由会员分享，可在线阅读，更多相关《最简单的现金流水账数据透视表分析法.docx（22页珍藏版）》请在三一文库上搜索。
1、最简单的现金流水账数据透视表分析法首先说明，本人非专业会计，数据透视表也极不熟悉，仅自己试着玩，当然是原创。以前做过单位现 金流水账，经常需要月、季度、半年、全年分析账目收支情况，即收入、支出主要是哪些项目等。每次都 去计算，那就太麻烦了。其实用数据透视表，就可以一劳永逸，设置好后，会自动生成月、季度、半年的 收支汇总情况，很是方便，下面以 EXCEL201的例。下图即为数据透视表分析的结果：时间 E类别 Jj项目求和项：余额-1月3收入办公经费收入280203门面收入41121其他收入458112专项收入5473355收入汇总6252791二支出办公费支出-138421党建费用支出-8651
2、加班费支出-9665交通费支出-17263津贴支出-8475年休补贴支出-4164全勤奖支出-3409宣传费吏出-156212支出汇总-3462601月汇总5906531弗月二收入办公经费收入5984门面收入28498其他收入88专项收入996收入汇总35566-支出办公费支出 心N务曲Fff古山-355当然，要制作现金流水账的数据透视分析，也得象纸质做原始账一样录入原始数据，且录入时有一定的规范（简单）要求，如下图:AECDEF1时间项目4gfc-agT收入支出余额22013-1T办公经费收入略200002000032013-1-2门面岐入略300005000042013-1-3专项收入略1
3、50006500052013-1-4其他收入略100007500062013-1-5办公费支出略2500050000T2013-1-6津贴支出略30004700082013-1-7r交逋费支出略50004200092013T-8党建费用支出略-一 500037000一102013-1-9宣传费支出略200035000112013-1-10加班费支出略512029880122013-1-11全勤奖支出略255527325132013-1-12年休补贴支出略150025925-L-.八 E .1、e J原始数据录入格式，几乎与纸质账一样，当然你也可以增加“记账号”等内容，但就象 上图的摘要一样，对
4、数据透视表无大碍，可以忽略不计。为了后面好操作，注意：凡是“收入”在后缀上“收入”两字，凡是“支出”在后缀上“支出”两字，录入要按账目产生的时间为序。原始数据录入完毕，选中其录入的所有内容后，在 EXCEL201QL依次操作：插入t表格 t弹出的对话框直接确定，如下图：可 E D国硬或厨曲冒爪号阉七及互E晶蹄W开始 插入页面布局公式 滤 审闻 眺囹 常用命令 设计500037000I 19 901 *11 1 1仝5! 蛇专出fflX-QEjizrj' （费果是一次性的，不再录入数据的话，此步可以不要。如果今后要继续在此表中增加数据， 则必须这样做，才能建立动态的数据透视表，今后再录入
5、数据后，只需按一下“刷新”，就 是最新数据汇总分析。）1、创建数据透视表：将光标放在上面列表中任意单元格，如A9，点击：插入数据透视表弹出“创建数据透视表”对话框直接确定。75000ESI却状SmdrtArt畀葛真图柱形图折矮图 饼图 星形囹面积囹歆U党建费用支出2013-1-9宣传费支出200035000512029880开始页面布局公式 数据 窜阅 视图常用食令书秋SmartArt彝基藏图囹片等腐画柱形图拆线囹德图条形图面A 2013-1-8100002500050。03000470002、这时就会自动打开一张新工作表，在新表中有数据透视表（暂无数据）、数据透视表字段列表（表格(Ctrl
6、+ T)父虺黄丸iX割建一 ,用于曾豪吩 析帽关盆据.O 有关详蝴相助.请技F1.2U13-1-Y20000200002013-V82013-1-10加班费支出国豳d? ；，言汇甘EE参1夕, lA A盲200005000065000500006500075000500042000200003000015000500042000500037000inT宫估葬专由7nnn喝AM3000015000100002500050000300047000襁入约晤琶视去寻三二责豆、奔匚下艺云9奕技T-8I党建.费用支出通过度寻芬春WL玉可以 方便麟冽arc怠夏杂数 据.井可齿甘宣看洋蟾 曰& 有关详
7、蛔醪助，清技F1.2UHFI-7 您费支出uU23145ad6 1:7疵使用表,可以方便圮五中行招旺 耳害日】充昔格艾，般在表的右侧，因设置不同，外观略有差异）,如下图。将鼠标对准“数据透视表字段列表”中的“时如下，即为拖动的初步透视表，但时间和项目显示不便丁查看，且由丁原始数据的收入、支 出列内有空格，数据透视表默认以“计数”来汇总，应当调整为“求和”来汇总，才能显示 原始数据。下一步我们将进一步调整。AEC1_23行标签 J计数项：收入 计数项:支出4-2013-1-1156办公经费收入12013-1-2178门面收入1-2013-1-3191T专项收入12013T-41U12其他收入12
8、013-1-511314办公费支出12013T-611516津贴支出1E2013-1-?1171S交通费支出12013T-8119120党建费用支出13 2013-1-912122宣传费支出12013-110123 一24加班费支出13 2013-1-11125 -26全勤奖支出1-2013-1-121幻23年休补贴支出1二 2013TT312932办公经费收入13 2013-1-141门面收入1s2013-1-15133_34专项收入1-2013-1-161其他收入1omi 01 _ 1： "np I E | F I G数据透裸表字段列表x选择要添加到报表的字段:间目要入出额时项摘
9、收支余00口回0口在以下区域间拖动宇段：v报表筛选za列标签E数值龙数值计数项:收入 计数项:支出3、数据透视表调整：首先，将光标定位丁收支列中任意单元格，如B4,点右键t值汇总依据t求和，即可将“计数”变为“求和”而显示收入的具体数值，用类似方法把支出改为原始 数值。林-u M%，圉行标签L*.计数项：收B £ =撰T '出一皿3 ABCD420131-1115办公经费收入复制©'2013-1-2第单元陪悟式门面收入La二 2013-1-310专项收入-2013-1-4J昂新因11其他收入排序国*12三2013-1-5X删蜂"计蜀I项:收入&qu
10、ot;(V)13办公费支出1415162013-1-6.津贴支出-2013T-7值汇总依据(M)*17交通费支出*4新洎细僖息旧平均值叵|1820131-8最不侑(MJ19党建费用支出电值字盟瞄回”203 2013-19项隐藏字段列表E最小瘩(D2122宣传费支出t 2013-1-10路加班费支出1真恤项顿741下图为“收入”、“支出”均以“求和”方式显示原始数值。43行标签求和项二收入求和项二支出42013-1-120000§办公经费收入2000064 2013-1-2300007门面收入300008-2013-1-3150009 _专项收入1500010-2013-1-41000
11、OU其他收入100001220131-525000；13办公费支出25000Hd2013-1-6300015津贴支出300016t 2013-1-7500017交通费支出50004、接下来为了美观，进一步调整透视表。单击透视表中任意单元格如A10,调出“数据透视表工具” T设计T报表布局T以大纲形式显小。如下图：画订不下商冒 茗言土苫Hr初；j元五插入页面布局公式 数据市阅 视,：行括霓I邀边行E1列朝镶边列A10以压缩形式显示（S二'*-4123 b4567 二8 业101112行标签2013-1-2013-1门由= 2013-11以表觥式显示CD重复所有项目标摆®不重夏项
12、目标签色）201314!10000其他收入100002013-1-5C求和项:支出25000'以大纲形式显示”后，透视表由原来的三列变为四列，更加直观，见下图21_ |J|_|时间，项目,求和顼：收入求和项二支出4匚-2013-1-12000056办公经费收入20000-2013-1-23000078门面收入30000-2013-1-315000910专项收入15000-2013-1-410000It 1T其他收入10000-2013-1-525000i.314办公费支出25000-20131-6300015津贴支出300016-2013-1-7500017交通费支出5000isl20
13、13-1-850001 Q者德薯田古中mm5、接下来，我们为了便丁更好地汇总分析，我们将对“收入”、“支出”、“时间”进行组合 左键点击表中“项目”右侧倒三角形，弹出下拉菜单T标签筛选T结尾是，将弹出对话框， 如下图3时间 二项目/求和项：收入 求和项:支出汁序降字其慨龄.20000200003000030000150001 5uuu27QC?星 Piz笙选）aa办公费支出*办公姓费收入/党建费用支出/加班费支出行交通费支出,津贴支出m门面收入封年休补贴支出封其他收入封全勤奖支出W宣佳费支出7取消年休补贴支出彗于宜于（U开头是（D”， 开头K皇CD”，至尾K是包含K包含（跖大于®K亍
14、或等于C0i，“ 小于“ 小于亍5000痂00300030005000500050005000200020005120fsi202555255515001500在弹出的“标签筛选（项目）”对话框的“结尾是”右边输入“收入”并确定。如下34567e91011121314时间 二项目二求和项：收入 求和项:支出20000办公经费曳入海。0-2013-1-2门面收乂.2013-1-3显手的项目的标鉴：专项收入结尾是 v 收入-2013-1-4其他收入-2013-1-5办公贵岚H2013-1-G用？代表单个字符用*代表任意多个字符确定3tfW取消则将显示所有结尾是“收入”的项目，如下时间项目求和项二收
15、入 求和项二支出-2013-1-120000-2013-1-2办公经费收入20000300002013-1-3门面收入3000015000, H20131-4专项收入1500010000二 2013T-13其他收入10000254658r 2013-1-14办公经费收入25465855656、用鼠标选中“项目”下面所有“收入”后，依次点击：数据透视表工具T选项T找到“分 组”卡T将所选内容分组。这时就会新增一列“项目2”，其下为“数据组1”。3时间二项目2'项目二求和项：收入求和项:支出4-2013-1-1200005裁据组6办公经费收入200007-2013"1_23000
16、08-数据坦19门面收入30000102013-1-3150001112 尊数据祖115000100002013-1-4专项收入单击“项目2”单元格，在上方的编辑栏（只能在编辑栏内操作）内将“项目 2”改写为“类 别”，并“回车”即可。nr甚坝*数瘢财活动字段涵L,1AE1CD123时间T项目21项目应求和项：收人4公67-2013-1-1200(数据组1办公经费收入200<5 2013-1-2300（R-；勃抿新1选中第一个“数据组1”，用上述方法将其改为“收入”，效果如下：/ 13时间类别 刁顼目丁求和项二收入 求和项:支出4-2013-1-1200005弓收入6 .办公经费收入20
17、0007= 2013-1-2300008lr收入9门面收入3000010=)201500011臼收入12专项收入1500013-2013-1-41000014粒入15其他收入100CJ016-2013-1-1325465817M入上图显示的全为“收入”项目内容（实际为筛选为收入）,点击“项目”右侧的筛选图标，在 下拉菜单中单击“从“项目”中活除筛选”，即可将项目中所有内容含支出内容都显示出来。重复上面的第5、6两步操作，将“项目”的“支出”组合起来，效果如下图:时间类别项目孑求和项:收入求和项:支出2013-1-525000-2013-1-6-支出2办公费支出250003000-2013-17
18、-支出12津贴支出300050002013-1-8-支出2交通费支出50005000一 QA1Q支出.2党建费用京出5000然后，再单击“项目”右侧的筛选图标，在下拉菜单中单击“从“项目”中活除筛选” ，则所 有收入、支出内容都显示出来，如图：7、对现金流水账，一般是按月来进行汇总分析，那么我们就将“时间”按月进行组合。先选 中所有“时间”，再依次：数据透视表工具T选项T分组T将所选内容分组T弹出“分组”对 话框T “起始丁”、“终止丁”前默认为勾选保持不变（如未勾选就勾选上）T “步长”中选 中“月” T确定。如下图J剧圉芸汇苫图，妙 z7' ! A *;数握送根表名称：活孟字:7格
19、所选方春舞1 >h ：i绵意祸表1时闾蕾龄,勉字跖噩龙折暨整人字段既龈&合 亚将字段洒3U排序SLX切Mfij活动字段排序乖选A42CI13-1-1ABC文件开始 插入页面布局公式 数振 牵阅 视图常用命令时间5678910一 2013T-11E收入办公经项-2013-1-2二收入门面收111213-2013-1-3二收入专项收，141520131-4c收入其他收'16-2013-1S17-支出DE下图即为按月汇总的现金收支流水账3时间二类别，项目 二求和项：收入求和项二支出4U月62527913462605T支出6办公费支出1304217党建费用支出86513加班费支出
20、966S9交逋费支出1726310津贴支出847511年休补贴支出416412全勤奖支出340913宣传费支出15621214三收入15办公经费收入28020316门面收入4112117其他收入45811218专顼收入547335519T 2月355663240320、支出21茹公费支出35522党建费用支出696323加班费支出6924交通费支出896525津贴支出496526年休补贴支出995927全勤奖支出5928宣传费支出106829力收入30办公经费收入598431门面收入2849832其他收入8833专项收入99634tr-买月I * II !40006448大家看了上图，只有“收
21、入”、“支出”没有“余额”，下面我们将通过“添加计算字段”的方 法来求出“余额”。8、将光标定位在透视表中数值区域任意单兀格，调出“数据透视表工具” T选项T “计算”组T计算字段T弹出“插入计算字段”对话框T选中对话框中“名称”右侧中内容“字段 T' 并改写为“空格（键）+余额”（因原始数据表中有“余额” 一项，此处加空格是为了区别，否 则不能操作）T将对话框中“公式”右边的“ =0”的“删除”将光标定位在“=”后面，用鼠 标单击对话框下边“字段”中的“收入”，则“公式”变为“=收入”，然后收入后面输入“-” 号，再点击“字段”内的“支出”，从而将“公式”改写为“=收入-支出”，然后
22、确定即可。请参见下两图：ABCD；号_FGH123时间,类别一项目求和项:怛入.求和项:支出625279111月3462605-支出办公费支出 党建费用支出12全勤奖支出_j3宣传费支出14日收入15办公经费收入16门面收入1?其他收入18专项收入19曰2月20二支出1011加班费支出 交通费支出 津贴支出 年休补贴支出21办公费支出字段堕）：插入字段里）确定美3时间 ,类别，项目'求和项：收入求和项二支出4 二! 1 月62527913462605678910111213141516171819 - 2 月20Vi 添加值）1产板入-支出1名舔0）:公式（H）：间目要五时项摘收插入字
23、段（1-确宗关闭下图为增加“ 余额”计算字段后的效果，但其中的余额仅为每一行收入、支出相减的余额, 并不是我们想要的余额。3时间类别二项目T求和项:收入求和项：支出求和项二余额4' 1月625279134626059065315二支出6办公费支出138421-1384217党建费用支出8651-86518加班费支出9665-96659交通费支出17263-1726310津贴爰出8475-847511年.休补贴支出4164-416412全勤奖支出3409-340913宣传费支出156212-15621214-收入15外公经费收入2802032S020316)门面收入41121411211
24、7其他收入45011245311218专项此入54733555473355192月3556632403316320-支出瓦办公费支出335-35522党建费用支出6963-696323加班费支出&9-699、单击余额中任意有数值的单元格，右键调出下拉菜单T值字段设置T弹出“值字段设置” 对话框T值显示方式（下方默认一般为“无计算”）T点击值计算方式下输入框右侧下拉箭头, 将默认值改为“按某一字段汇总” T再在其下方的“基本字段”内选择“时间” T确定。即 可按时间来汇总余额。如图：求和项:支出求和项：余额346260590653132403口T384211138421865196651
25、7263847541643409156212355696369896541643409.56212一4164值字段设置源名称：余额自定曳名称：求和项：余颤32403355696369896549659959591068值汇，民方式值显示方式 值显示方式圈基本顼I基本字段（Z）:数字格式Q） L 踊定取消10、单击数据透视表内任意单元格如 D9，调出“数据透视表工具” t设计t找到“布局”级 T点击“分类汇总”图标T在组的底部显示所有分类汇总，即可得到以“时间”月为单位的 分类汇总表，在每月的底部就有当月收入、支出、余额的汇总数据，分别见下图：国I 口圉;口届鱼考：匚闩已*妙*妙，iAI宇 I开
26、始 插入 K面布局 公式 薮据 宰阅 祝图 甯为命令 选项I设计文件7行忘题n镶迥行/ 7.5C 畋列不显示分类汇琶旧）在阻的底部显示所有分类汇总国在组的顶部显示所有分类汇总CD汇尊中包含舞选映加班费支出8兄炷火片乂出DEF（TF求和项:收入求和项:支出求和项:余额62527913462605906531壶!.七表杉式浅项玄通费支出91011津贴支出 年休补贴支出138421-1384218651-86519665-966517263-172638475-84754164-41643时间-类别2项目刁求和项二收入求和项二支出求和项二余额4-1月5-支出6办公费支出138421-1384217党
27、建费用支出8651-86518加班费支出%65-96659交通费支出17263-1726310津贴支出0475-8475可年休补贴支出4164-416412全勤奖支出3409-340913宣传费支出156212-15621214支出汇总346260-3462601S收入16办公经费收入28020328020317门面收入411214112118其他收入458112458112可专项收入5473355547335520收入汇总62527916252791211月汇总6252791346260590653122-2月23支出办公费支出355-138776党建费用支出6963-1561426加班费支
28、出69T73427交通费支出6%5-2622828津贴支出4965-1344029年休补贴支出9959-1412330全勤奖支出59-346831宣传费支出1068-15728032支出汇总32403-37866333-收入34办公经费收入598428618735门面收入284986961936其他收入8845020037专项收入99654743513S收入汇喜355666288357Z92月汇总3556632403590969440-3月但上图先显示“支出”后显示收入，与日常做账不一致。适当调整即可：左键单击“类别” 右侧的下拉箭头（倒三角形）,在弹出的排序筛选菜单中，选择“升序”即可。如下
29、图：3时间-类别/项目求和项：收入求和项:支出求和项:余额4-1月5日收入6办公经费收入2802032802037门面收入411214112181其他收入458112458112专项收入5473355547335510收入汇总6252T91625279111-支出12办公费支出138421-13842113党建费用支出8651-865114加班费支出9665-966515交通费支出17263-172S316津贴支出8475-847517年休补贴支出4164-416418全勤奖支出3409-340919宣传费支出156212-15621220支出汇总346260-346260211月汇总6252
30、791346260590653122-2月23-收入24办公经费收入59B428618725门面收入2849869S1926其他收入8845820027专项收入99654T435128收入汇总35566628835729二|支出30办公费支出355-138776党建费用支出6963-1561432加班费支出69- 973433交通费支出8965-2622834津贴支出4965-1344035年倘卜贴支出9959-1412336全勤奖支出59-346837宣传费支出1068-15728038支出汇总32403-378663392月指总O 口35566324035909694通过以上步骤，现金流水
31、账按时间进行分类汇总就出来了， 当然上表还可以进一步进行美化 且如果现在仅为到2月底的数据，3月乂在原始数据表中新增数据后，也不用重新再制作透 视表，只需到透视表中刷新即可，操作如下：单击数据透视表任意单元格，调出“数据透视 表工具” T选项T找到“数据”组里的“刷新” T点击“刷新”即可，这时透视表会自动将 3月的结果按原来的分类汇总方式自动生成分析表。如下图：ABCDEF4T月5二收入6办公经费收入2802032802037门面收入41121411218其他收入453112450112_ 9.专项收入5473355547335510收入汇总6252791625279111-支出12办公费支
32、出138421-13842113党建费用支出8G51-865114加班费支出9665-966515交逋费支出17263-1726316津贴支出8475-847517年休补贴支出4164-416418全勤奖支出3409-340919宣传费支出156212-15621220支出汇总346260-346260汇月汇总6252791346260590653122弗月23二收入24办公经费收入5984286187玉门面收入284986961926其他收入88458200专项收入996547435128收入汇总35566628835729支出30办公费支出355-13877631党建费用支出6963-13
33、61432加班费支出69*9734交逋费支出8965-26228津贴支出4965-13035年休补贴支出9959-1412336全勤奖支出59-346837宣传费支出1068-15728038支出汇总32403-378663392月汇总3556632+03590969440邪月41二收入42门面收入35007311943其他收入50045870044收入汇总4000629235745-支出46办公费支出498T39274M 4* ' 1汇总原始 J就话.L按上述方法，作出好现金流水账数据透视表后，今后直接在原始数据里新增数据，然后 到数据透视表里“刷新”，就可以得出最新的汇总分析结果，很方便。也可以在“数据透视表 字段列表”中“数值”框内的“收入”、“支出”拖出，就更直观，自己摸索。本人菜鸟，纯届为了自己方便，搞起玩的，绝对原创，如有不对之处，敬请谅解