利用Excel2007数据透视表进行预算差额分析

1、 /7 /7利用Excel2007数据透视表进行预算差额分析数据透视表可以轻而易举地将存放于一张工作表中数据列表进行汇总分析，但要对不同工作表，甚至不同工作簿中的多个数据列表进行合并汇总，则需要创建多重合并计算数据区域的数据透视表。图160-1是某公司在某年度的费用预算和实际发生额数据，如果要对预算和实际发生额表格进行汇总并分析预算与实际发生额的差异。具体步骤为：A5U1月口科目宅称-21办公用品664301出基熒26.61440123,639501过桥过踣费344601交通工具消軽I.B8S7Q1手机目话贲0,99680102?01邮乏棗1051002办公用品3.2301102Q.&2&22

2、出希牟羯闕41302过折过路贡29802交玄工具淆话2,31502手机目话费3,3361&2修理棗191702邮寄覆2.B291002迄荒附加1141&3办公用品3,7772003出走灵16.8042103出租车竇2282203抵税运竇13,6152303过斬过路灵2.27&2403话费补64825亜汚工且消耗3,42725037.3TH4H奎异分折fSM实际发生二ABCDEFI月J日+凭证号数联I科目容務-舍甑”0131;3-0D02B&吕差羯122,0030131i-0004略270.000131ifi-0013聘出差费6.0DQ.0D0131-.5-0015t!差櫃1,947,30601

3、31iE-0016略3.09650;0131ifi-0017昭1,947.30F01F31iLOG18R5-3,182.000131i-0D19出差羯3.4D1.0DID:0131i-0020昭4.624JB0131.-0022出差费5,aS4.5Dr0131te-OQ23R&吕差羯1,592.70Br0131ie-0004略办公用m553-001415W0131tfi-0002昭手机电话資100.00F01F31记LODDGR5-手机电话费4s6500D0131i-OD06手机电话总1.026,20:i190131i-aoo7昭手机电话灵350.00F0131.-ODoe略手机电话疣SDOO

4、D0131;3-0D09手机电话克5Q0,0D2010131ifi-0010略手机氐话费1.000-002122230131记-0011略手机电话貝1,690.00r01F31-.5-0014略手机电话蛊427.72r0131iE-0D21手机电话捏7D0,0D2425250131tfi-0005邮咼灵37.00F01F31i4X1交通工具消耗2D00Dr01肿.ie-0D02交通工具消耗2.1B0.0DH卜H.差畀L廿折fSn宴际发生图160-1某公司费用预算和实际发生额数据步骤1单击工作簿中“差异分析”工作表标签，激活“差异分析”工作表，按组合键激活“经典的数据透视表和数据透视图向导”，单

5、击“多重合并计算数据区域”单选按钮，如图160-2所示：ACF11121314差耳伶析56151617231IB冈1完成数据却春和数据刼曲向导歩驟1（K3歩j定待分祈救据的数据遞卿?1ierasoftOfficeEkc&I数据現|表竝3I基库逋o外臧据源W蜃重宜殒環翹国醱左V所需创建的报表类型Q睚删表O数据匡狈图狠敷据删徳CL）图160-2选择多重合并计算数据区域选项步骤2单击“下一步”按钮，在弹出的“数据透视表和数据透视图向导-步骤2a（共3步）”对话框中单击“自定义页字段”的单选按钮，然后单击“下一步”按钮激活“数据透视表和数据透视图向导-步骤2b（共3步）”对话框，如图160-3所示。图

6、160-3激活数据透视表指定合并计算数据区域对话框诘为母一T貝子段选走一T煎目蘇签来标识选走的数菇区堀.Tfe（?）数据谯观表湘数据谯腿閹向导-歩黑11供H步）1a话指定待分祈数据的數毎縛裘型：:Mier=otOFia*Exaal数据列焦或数据1库（5）0外部数据浊（1）01多童吾井讦茸数据底颐忑烹月i不药五耘硕聂更致函廷也EIA：所畫创耀的掘衣类型.数捂逶瞰（X）0数招透现團數据透观表（1）取消丄一工（3）|下一步T完咸取消上一歩下-步v完成子氐iUO子段4血职消上一歩下一歩墮）、咸的佶先捋走姜建立在数菇珈義中的贝予删目ooO10203步骤3在弹出的“数据透视表和数据透视图向导-步骤2b（共

7、3步）”对话框中选择“页字段数目为“1”，将光标定位到“选定区域”编辑框，选定“预算”工作表的B1:C158单元格区域，单击“添加”按钮完成第一个合并区域的添加，在“字段1”下方的编辑框中输入“预算”，如图160-4所示：图160-4添加合并区域步骤4重复操作步骤3，将“实际发生”工作表中的E1:F802单元格区域添加到选定区域中，同时将其字段命名为“实际发生”，完成后如图160-5所示：图160-5添加合并区域步骤5单击“下一步”按钮，在弹出的“数据透视表和数据透视图向导步骤3（共3步）”对话框中指定数据透视表的创建位置“差异分析！$A$2”然后单击“完成”按钮完成多重合并计算数据区域数据透

8、视表的创建，如图160-6所示： /7鬼暑压视去沖懸IB廷祝囲向导歩彈强供3刘6X苗臣？Jkt足旦台汇思S?谢工惟垂戲3眶规.违圭区就:AC1帀1全邹*123字和皿:更列3行E常预臣计5办公用上7B314.6S1003505JI78314.69100350671S上1垄费r?出祖车贲3MSS30刪8拒親运垂21166045211660.4&9固定巨话县19206.3319206.3310过拆过路娈735fit.579561.511话贵补47524752rI；计畀机薩材6756.96756.913交連二三消走146M8.93i4ei4a.9GH交遥工具務哩70621.46和521.46宁盘月丘2

9、96013296Q13M6其他S155915617设备檯用吏2S6S73.91290879.9118二机电话喪13746L85137461,8519很薑毎77178771178201326132621邮誉杲3W7B2S39470.ZB22运長附加1448M6144834.823Att2370B2&.75237&B29.75 #/7 #/7图160-6多重合并计算数据区域的数据透视表步骤6在数据透视表字段列表中将“页1”字段移动至“列标签”编辑框内，数据透视表也相应的发生变化，如图160-7所示： /7敵詰透視表字检列表X选挥要涵加到报歳的宇段:回行选聲要両m到抿表的字忌:行刻值K1blzRlz

10、在IU下区程间拖动宇段V抿志佶选a列标签|皿T1|列LJ行标矍S数佰厅求和项i+推迟冇局更新更新在以下氏越间3S动宇庄捲迟布局更新 #/7 /7ABcU匕123我和顼:伍页1-列*4-預算预算汇总-|实际量生实际盖生汇总总计5行-金額&办公用品427204272035594.B9355&1.6&76314.697出差晚46005446006557500.71557500.711003505.718出租车费135521365216036160363048S9抵琥运費9346693B&13174.4511617.46211680.4510固定电话费10477104778729.33S729.331

11、9206.3311迥祈过路费325703257640&85.540935.573561.512话竇补259226&221602160475213计算机耗材368636863070.93070.96756.914交通工具消耗6M726M7232676.S2676.93146148.9315交通工具幅圧313413134439177.4&39177.-4670521.4616劳烬用品1&1481614813453.9413453.M2&601.941/苴他4462如駆46&J4E93915510设雷使用费1&4S315&W3130395.&1130395.9120&876.9119手机电话费610

12、97610&776364.&5763&1.S5137401.3520逞餐長343D234302428T642B767717821爆理费4664&6&0680132622邮寄是21233212S318243.281S243.2839476.2823运葵附加705637056574271.37427.3144834.624总计11048441104B441265984.751265964.752370028.75图160-7将数据透视表的页字段移动至列字段步骤7去除数据透视表的汇总和总计列，添加计算项“差异”，公式为“差异=预算-实际发生”，最终完成的预算差额分析表，如图160-8所示：AED12

13、3求利项:值页1列4预算实际岌生差异5行E金額金额金飯6亦金用品42J20.0D35,594.697J5.317出奎费446,005.00557,500.71-111,495.713岀租车费13.5520016,936.00-,304.009抵税运贲92/Q6.0D11Sa17445-24,688.4510固定电话费10X77.QQ3729.331.747.6711迥桥迥路费3.576.0040,985.50-8,409.5012话费补2.592.002.160.00432.0013廿算机耗材3.6B6.003,070.90615.1014交暹工具消耗63.472.0082,676.93-19,204.9315交通工具修理31,344.0039,177.46-7,833.4616劳保用屈16,148.0013,453.942,694.0617其他4.462.004,693.00-231.001S谡番使用费156,483.00130,395.9126,087.0919手机电话费61,097.0076,364.85-15,267