财会玩转Excel第7期-公开课大纲.xlsx

中国农业银行中国农业银行中国农业银行中国银行中国银行中国银行中国农业银行中国农业银行中国农业银行中国农业银行Alt+Enter53梁保华梁保华537990吕祖善33356王金山29310黄小晶9262四则运算15蓝天20886韩寓群5600李成玉24333罗清泉650明月55207参观Excel知识线与财会工作线的完美结合为财务软件的数据分析提供支持开启职业生涯的新起点，助你从平凡走向卓越财财会会玩玩转转EXCEL公公开开课课正式课报名QQ400 004 9448主讲：郭辉_伊格ExcelHome出品http:/ 666 936Excel知识线与财会工作线的完美结合为财务软件的数据分析提供支持开启职业生涯的新起点，助你从平凡走向卓越财财会会玩玩转转EXCEL公公开开课课第一次公开课案例1-1专业素质体现-报销单据处理1-2专业素质体现-收据引发的联想1-3EXCEL无处不在-工作日志1-4恼人的银行卡号核对1-5多角度应收款账龄分析1-6为你配备一个秘书-承兑到期提醒1-7财务报表汇总1-8总账与明细账核对表1，报这个班我能学到什么正式课目录2，对我工作能有多大帮助实际案例演示3，我能达到什么水平1、在Excel方面：可以掌握各类排序、分列、筛选、高级筛选、定位、单元格设置等基础操作。熟练掌握十个以上的函数，了解并能够独立使用的函数50个。提上技巧能够充分满足日常工作。2、在财务方面：了解到更多的财务操作办法和财务工作思路，培养善于挖掘提高效率的意识，培养善于发现问题的意识。3、给大家树立一种观念，凡是重复性比较大的工作、凡是操作比较繁琐的工作、凡是需要大量计算的工作，都有可能通过EXCEL的介入而实现高效完成。财财会会玩玩转转EXCEL（7）第第一一次次公公开开课课无论你是一个普通会计，还是一个高级管理会计，熟练驾驭EXCEL对提高工作效率，提高管理水平都是必须的。孙子兵法说：兵者，国之大事，死生之地，存亡之道，不可不查也。我认为，EXCEL相对于财务工作也是一个死生之地，存亡知道，不可不懂。正式课报名QQ400 00494481、在Excel方面：可以掌握各类排序、分列、筛选、高级筛选、定位、单元格设置等基础操作。熟练掌握十个以上的函数，了解并能够独立使用的函数50个。提上技巧能够充分满足日常工作。2、在财务方面：了解到更多的财务操作办法和财务工作思路，培养善于挖掘提高效率的意识，培养善于发现问题的意识。3、给大家树立一种观念，凡是重复性比较大的工作、凡是操作比较繁琐的工作、凡是需要大量计算的工作，都有可能通过EXCEL的介入而实现高效完成。财财会会玩玩转转EXCEL（7）第第一一次次公公开开课课无论你是一个普通会计，还是一个高级管理会计，熟练驾驭EXCEL对提高工作效率，提高管理水平都是必须的。孙子兵法说：兵者，国之大事，死生之地，存亡之道，不可不查也。我认为，EXCEL相对于财务工作也是一个死生之地，存亡知道，不可不懂。第第二二次次公公开开课课案案例例报名正式课后，完成三个小任务可获得的奖品。2-1导出数据深加工-成本数据分类汇总https:/ 004 9448报名正式课后，完成三个小任务可获得的奖品。https:/ 在在课课程程中中的的互互动动中中，凡凡是是表表示示同同意意、是是、认认可可、已已经经完完成成等等内内容容时时，用用1 1表表示示，反反之之用用0 0。确定、是、同意、已完成-1-TRUE-真不同意、未完成、否定-0-FALSE-假EXCEL-EXCEL-伊伊格格从事财务工作23年（1992年）学习EXCEL16年（1999年）2008年出版EXCEL高校办公系列丛书单位:元售价金额税金金额成本金额费用金额利润金额1234567891011红莲1只102100111429.6433020.630.49542.97 17.2719240.8 14.0715673.58-2.19-2436.729096统机日历2只4394010134.973532.470.5757.85 15.821597.41 14.8151496.333.77380.884293镀钛表3只1531042829.9512818.410.34143.84 17.537503.77 15.9626831.63-3.88-1660.831103镀钛日历表4只593706835.082385.130.4832.33 16.781140.76 17.3881182.370.4429.675869三鱼表5只29980128.8828.880.080.08 10.2410.24 33.3233.32-14.76-14.762997三鱼日历表6只245000.0017752.970.00147.27039491.5907833.860-29719.75245仿劳力士日历7只5761031938.8212382.420.46146.19 15.654992.9 20.2566461.542.45781.795442高档机械表8只1355403045.991379.790.5115.44 15.09452.73 26.891806.733.50104.8913524军工日历9只258904748.152263.110.3918.32 24.621157.13 27.1781277.36-4.04-189.72542红莲坤表10只13414046937.2217454.440.37173.68 16.987965.43 19.5229155.880.34159.4512945红莲日历坤表11只14347041837.8115805.540.50210.19 14.806185.87 19.3978108.113.111301.3713929太行怀表12只17360147.8647.860.860.86 37.4537.45 22.9722.97-13.42-13.421735镂空怀表13只398011256.136286.330.6673.55 12.301377.51 18.3642056.8 24.812778.47286石英电子表14只49789010010.551054.680.2121.09 12.301229.92 3.9867398.67-5.95-59549689高档石英15只-33614031665.1220579.281.08341.33 10.053176.97 27.2168600.23 26.778460.75-33930产品销售利润表编制单位:石家庄凯斯达表业股份有限公司1999年11月30日产品名称行次规格计量单位上年结转数量本年实际产量本年实际销售数量产品销售利润年末产品结存数量产品销售收入产品销售税金及附加产品销售成本产品销售费用单位:元售价金额税金金额成本金额费用金额利润金额1234567891011红莲1只102100111429.6433020.630.49542.97 17.2719240.8 14.0715673.58-2.19-2436.729096统机日历2只4394010134.973532.470.5757.85 15.821597.41 14.8151496.333.77380.884293镀钛表3只1531042829.9512818.410.34143.84 17.537503.77 15.9626831.63-3.88-1660.831103镀钛日历表4只593706835.082385.130.4832.33 16.781140.76 17.3881182.370.4429.675869三鱼表5只29980128.8828.880.080.08 10.2410.24 33.3233.32-14.76-14.762997三鱼日历表6只245000.0017752.970.00147.27039491.5907833.860-29719.75245仿劳力士日历7只5761031938.8212382.420.46146.19 15.654992.9 20.2566461.542.45781.795442高档机械表8只1355403045.991379.790.5115.44 15.09452.73 26.891806.733.50104.8913524军工日历9只258904748.152263.110.3918.32 24.621157.13 27.1781277.36-4.04-189.72542红莲坤表10只13414046937.2217454.440.37173.68 16.987965.43 19.5229155.880.34159.4512945红莲日历坤表11只14347041837.8115805.540.50210.19 14.806185.87 19.3978108.113.111301.3713929太行怀表12只17360147.8647.860.860.86 37.4537.45 22.9722.97-13.42-13.421735镂空怀表13只398011256.136286.330.6673.55 12.301377.51 18.3642056.8 24.812778.47286石英电子表14只49789010010.551054.680.2121.09 12.301229.92 3.9867398.67-5.95-59549689高档石英15只-33614031665.1220579.281.08341.33 10.053176.97 27.2168600.23 26.778460.75-33930产品销售利润表编制单位:石家庄凯斯达表业股份有限公司1999年11月30日产品名称行次规格计量单位上年结转数量本年实际产量本年实际销售数量产品销售利润年末产品结存数量产品销售收入产品销售税金及附加产品销售成本产品销售费用 在在课课程程中中的的互互动动中中，凡凡是是表表示示同同意意、是是、认认可可、已已经经完完成成等等内内容容时时，用用1 1表表示示，反反之之用用0 0。确定、是、同意、已完成-1-TRUE-真不同意、未完成、否定-0-FALSE-假单位:元售价金额税金金额成本金额费用金额利润金额1234567891011红莲1只102100111429.6433020.630.49542.97 17.2719240.8 14.0715673.58-2.19-2436.729096统机日历2只4394010134.973532.470.5757.85 15.821597.41 14.8151496.333.77380.884293镀钛表3只1531042829.9512818.410.34143.84 17.537503.77 15.9626831.63-3.88-1660.831103镀钛日历表4只593706835.082385.130.4832.33 16.781140.76 17.3881182.370.4429.675869三鱼表5只29980128.8828.880.080.08 10.2410.24 33.3233.32-14.76-14.762997三鱼日历表6只245000.0017752.970.00147.27039491.5907833.860-29719.75245仿劳力士日历7只5761031938.8212382.420.46146.19 15.654992.9 20.2566461.542.45781.795442高档机械表8只1355403045.991379.790.5115.44 15.09452.73 26.891806.733.50104.8913524军工日历9只258904748.152263.110.3918.32 24.621157.13 27.1781277.36-4.04-189.72542红莲坤表10只13414046937.2217454.440.37173.68 16.987965.43 19.5229155.880.34159.4512945红莲日历坤表11只14347041837.8115805.540.50210.19 14.806185.87 19.3978108.113.111301.3713929太行怀表12只17360147.8647.860.860.86 37.4537.45 22.9722.97-13.42-13.421735镂空怀表13只398011256.136286.330.6673.55 12.301377.51 18.3642056.8 24.812778.47286石英电子表14只49789010010.551054.680.2121.09 12.301229.92 3.9867398.67-5.95-59549689高档石英15只-33614031665.1220579.281.08341.33 10.053176.97 27.2168600.23 26.778460.75-33930产品销售利润表编制单位:石家庄凯斯达表业股份有限公司1999年11月30日产品名称行次规格计量单位上年结转数量本年实际产量本年实际销售数量产品销售利润年末产品结存数量产品销售收入产品销售税金及附加产品销售成本产品销售费用单位:元售价金额税金金额成本金额费用金额利润金额1234567891011红莲1只102100111429.6433020.630.49542.97 17.2719240.8 14.0715673.58-2.19-2436.729096统机日历2只4394010134.973532.470.5757.85 15.821597.41 14.8151496.333.77380.884293镀钛表3只1531042829.9512818.410.34143.84 17.537503.77 15.9626831.63-3.88-1660.831103镀钛日历表4只593706835.082385.130.4832.33 16.781140.76 17.3881182.370.4429.675869三鱼表5只29980128.8828.880.080.08 10.2410.24 33.3233.32-14.76-14.762997三鱼日历表6只245000.0017752.970.00147.27039491.5907833.860-29719.75245仿劳力士日历7只5761031938.8212382.420.46146.19 15.654992.9 20.2566461.542.45781.795442高档机械表8只1355403045.991379.790.5115.44 15.09452.73 26.891806.733.50104.8913524军工日历9只258904748.152263.110.3918.32 24.621157.13 27.1781277.36-4.04-189.72542红莲坤表10只13414046937.2217454.440.37173.68 16.987965.43 19.5229155.880.34159.4512945红莲日历坤表11只14347041837.8115805.540.50210.19 14.806185.87 19.3978108.113.111301.3713929太行怀表12只17360147.8647.860.860.86 37.4537.45 22.9722.97-13.42-13.421735镂空怀表13只398011256.136286.330.6673.55 12.301377.51 18.3642056.8 24.812778.47286石英电子表14只49789010010.551054.680.2121.09 12.301229.92 3.9867398.67-5.95-59549689高档石英15只-33614031665.1220579.281.08341.33 10.053176.97 27.2168600.23 26.778460.75-33930产品销售利润表编制单位:石家庄凯斯达表业股份有限公司1999年11月30日产品名称行次规格计量单位上年结转数量本年实际产量本年实际销售数量产品销售利润年末产品结存数量产品销售收入产品销售税金及附加产品销售成本产品销售费用OFFICEEXCELWORD基础操作函数图表VBA单元格设置IF折线图排序SUM柱形图筛选VLOOKUP饼图硬盘EXCELWORD基础操作函数图表VBAACCESSOFFICEWORDACCESS硬盘WORDPowrPointOutLookOFFICEPowrPointOutLook硬盘OutLookOutLookCADEXCEL基础操作函数图表VBACPUEXCEL基础操作函数图表VBAWORDACCESSWORDACCESSPowrPointCADACCESSPowrPointCPUACCESS软件OutLookPowrPoint硬件OutLookPowrPoint软件PhotoShopEXCEL基础操作硬件内存EXCEL基础操作函数软件WORD函数图表VBAPhotoShopEXCEL硬件WORD图表VBAEXCEL函数ACCESSPhotoShopWORDACCESS内存PowrPointOutLookPhotoShopPowrPointOutLook内存PhotoShopOutLookOutLookEXCEL基础操作函数图表VBAEXCEL基础操作函数图表VBAWORDACCESSWORDACCESSPowrPointACCESSPowrPointACCESSOutLookPowrPointOutLookPowrPoint奖金档数钻石类奖金K铂金累奖金黄金类奖金完成率姓名黄金任务数13020100%10%梁保华2000260402010.01%20%吕祖善2200390603020.01%30%王金山24004120804030.01%40%黄小晶180051501005040.01%50%吴新雄230061801206050.01%60%韩寓群280072101407060.01%70%李成玉260082401608070.01%80%罗清泉220092701809080.01%90%奖金档数钻石类奖金K铂金累奖金黄金类奖金完成率起始比率完成率起始比率姓名黄金任务数13020100.00%10.00%梁保华2000260402010.01%20.00%吕祖善2200390603020.01%30.00%王金山24004120804030.01%40.00%黄小晶180051501005040.01%50.00%吴新雄230061801206050.01%60.00%韩寓群280072101407060.01%70.00%李成玉260082401608070.01%80.00%罗清泉220092701809080.01%90.00%黄金完成数完成比例黄金奖金21346.70%285029.55%369053.75%18201.11%310034.78%29304.64%354036.15%395279.64%黄金完成数完成比例黄金奖金21346.70%10285029.55%30369053.75%6018201.11%10310034.78%4029304.64%10354036.15%40395279.64%80序序号号还还款款日日期期本本金金利利息息月月供供本本金金余余额额累累计计归归还还本本金金累累计计还还款款贷贷款款额额12009/01/17 1,149.93 903.00 2,052.93278,850.071,149.932,052.93期期限限（月月）22009/02/17 1,153.64 899.29 2,052.93277,696.432,303.574,105.87利利率率（年年）32009/03/17 1,157.36 895.57 2,052.93276,539.063,460.946,158.80月月供供42009/04/17 1,161.09 891.84 2,052.93275,377.974,622.038,211.73总总支支付付额额52009/05/17 1,164.84 888.09 2,052.93274,213.135,786.8710,264.66利利息息总总额额62009/06/17 1,168.60 884.34 2,052.93273,044.546,955.4612,317.6072009/07/17 1,172.36 880.57 2,052.93271,872.178,127.8314,370.53 月供=PMT（年利率/12，贷款期限，贷款本金）82009/08/17 1,176.14 876.79 2,052.93270,696.039,303.9716,423.4692009/09/17 1,179.94 872.99 2,052.93269,516.09 10,483.9118,476.39102009/10/17 1,183.74 869.19 2,052.93268,332.35 11,667.6520,529.33112009/11/17 1,187.56 865.37 2,052.93267,144.79 12,855.2122,582.26122009/12/17 1,191.39 861.54 2,052.93265,953.39 14,046.6124,635.19132010/01/17 1,195.23 857.70 2,052.93264,758.16 15,241.8426,688.12142010/02/17 1,199.09 853.85 2,052.93263,559.07 16,440.9328,741.06152010/03/17 1,202.95 849.98 2,052.93262,356.12 17,643.8830,793.99162010/04/17 1,206.83 846.10 2,052.93261,149.29 18,850.7132,846.92172010/05/17 1,210.73 842.21 2,052.93259,938.56 20,061.4434,899.86182010/06/17 1,214.63 838.30 2,052.93258,723.93 21,276.0736,952.79192010/07/17 1,218.55 834.38 2,052.93257,505.38 22,494.6239,005.72二202010/08/17 1,222.48 830.45 2,052.93256,282.90 23,717.1041,058.65贷款额212010/09/17 1,226.42 826.51 2,052.93255,056.48 24,943.5243,111.59期限（月）222010/10/17 1,230.38 822.56 2,052.93253,826.11 26,173.8945,164.52利率（年）232010/11/17 1,234.34 818.59 2,052.93252,591.76 27,408.2447,217.45月供242010/12/17 1,238.32 814.61 2,052.93251,353.44 28,646.5649,270.38总支付额252011/01/17 1,242.32 810.61 2,052.93250,111.12 29,888.8851,323.32利息总额262011/02/17 1,246.32 806.61 2,052.93248,864.80 31,135.2053,376.25272011/03/17 1,250.34 802.59 2,052.93247,614.45 32,385.5555,429.18282011/04/17 1,254.38 798.56 2,052.93246,360.08 33,639.9257,482.11292011/05/17 1,258.42 794.51 2,052.93245,101.66 34,898.3459,535.05302011/06/17 1,262.48 790.45 2,052.93243,839.18 36,160.8261,587.98312011/07/17 1,266.55 786.38 2,052.93242,572.62 37,427.3863,640.91三322011/08/17 1,270.64 782.30 2,052.93241,301.99 38,698.0165,693.85贷款额332011/09/17 1,274.73 778.20 2,052.93240,027.25 39,972.7567,746.78期限（月）342011/10/17 1,278.84 774.09 2,052.93238,748.41 41,251.5969,799.71利率（年）352011/11/17 1,282.97 769.96 2,052.93237,465.44 42,534.5671,852.64月供362011/12/17 1,287.11 765.83 2,052.93236,178.33 43,821.6773,905.58总支付额372012/01/17 1,291.26 761.68 2,052.93234,887.08 45,112.9275,958.51利息总额382012/02/17 1,295.42 757.51 2,052.93233,591.65 46,408.3578,011.44392012/03/17 1,299.60 753.33 2,052.93232,292.06 47,707.9480,064.37402012/04/17 1,303.79 749.14 2,052.93230,988.26 49,011.7482,117.31412012/05/17 1,308.00 744.94 2,052.93229,680.27 50,319.7384,170.24422012/06/17 1,312.21 740.72 2,052.93228,368.06 51,631.9486,223.17432012/07/17 1,316.45 736.49 2,052.93227,051.61 52,948.3988,276.10442012/08/17 1,320.69 732.24 2,052.93225,730.92 54,269.0890,329.04452012/09/17 1,324.95 727.98 2,052.93224,405.97 55,594.0392,381.97462012/10/17 1,329.22 723.71 2,052.93223,076.74 56,923.2694,434.90472012/11/17 1,333.51 719.42 2,052.93221,743.23 58,256.7796,487.84482012/12/17 1,337.81 715.12 2,052.93220,405.42 59,594.5898,540.77492013/01/17 1,342.13 710.81 2,052.93219,063.30 60,936.70 100,593.70502013/02/17 1,346.45 706.48 2,052.93217,716.84 62,283.16 102,646.63512013/03/17 1,350.80 702.14 2,052.93216,366.05 63,633.95 104,699.57522013/04/17 1,355.15 697.78 2,052.93215,010.90 64,989.10 106,752.50532013/05/17 1,359.52 693.41 2,052.93213,651.37 66,348.63 108,805.43542013/06/17 1,363.91 689.03 2,052.93212,287.47 67,712.53 110,858.36552013/07/17 1,368.31 684.63 2,052.93210,919.16 69,080.84 112,911.30562013/08/17 1,372.72 680.21 2,052.93209,546.44 70,453.56 114,964.23572013/09/17 1,377.15 675.79 2,052.93208,169.30 71,830.70 117,017.16582013/10/17 1,381.59 671.35 2,052.93206,787.71 73,212.29 119,070.09592013/11/17 1,386.04 666.89 2,052.93205,401.67 74,598.33 121,123.03602013/12/17 1,390.51 662.42 2,052.93204,011.16 75,988.84 123,175.96612014/01/17 1,395.00 657.94 2,052.93202,616.16 77,383.84 125,228.89622014/02/17 1,399.50 653.44 2,052.93201,216.66 78,783.34 127,281.83632014/03/17 1,404.01 648.92 2,052.93199,812.66 80,187.34 129,334.76642014/04/17 1,408.54 644.40 2,052.93198,404.12 81,595.88 131,387.69652014/05/17 1,413.08 639.85 2,052.93196,991.04 83,008.96 133,440.62662014/06/17 1,417.64 635.30 2,052.93195,573.40 84,426.60 135,493.56672014/07/17 1,422.21 630.72 2,052.93194,151.19 85,848.81 137,546.49682014/08/17 1,426.80 626.14 2,052.93192,724.40 87,275.60 139,599.42692014/09/17 1,431.40 621.54 2,052.93191,293.00 88,707.00 141,652.35702014/10/17 1,436.01 616.92 2,052.93189,856.99 90,143.01 143,705.29712014/11/17 1,440.64 612.29 2,052.93188,416.35 91,583.65 145,758.22722014/12/17 1,445.29 607.64 2,052.93186,971.06 93,028.94 147,811.15732015/01/17 1,449.95 602.98 2,052.93185,521.11 94,478.89 149,864.08742015/02/17 1,454.63 598.31 2,052.93184,066.48 95,933.52 151,917.02752015/03/17 1,459.32 593.61 2,052.93182,607.16 97,392.84 153,969.95762015/04/17 1,464.02 588.91 2,052.93181,143.14 98,856.86 156,022.88772015/05/17 1,468.75 584.19 2,052.93179,674.39#158,075.82782015/06/17 1,473.48 579.45 2,052.93178,200.91#160,128.75792015/07/17 1,478.23 574.70 2,052.93176,722.67#162,181.68802015/08/17 1,483.00 569.93 2,052.93175,239.67#164,234.61812015/09/17 1,487.78 565.15 2,052.93173,751.88#166,287.55822015/10/17 1,492.58 560.35 2,052.93172,259.30#168,340.48832015/11/17 1,497.40 555.54 2,052.93170,761.91#170,393.41842015/12/17 1,502.23 550.71 2,052.93169,259.68#172,446.34852016/01/17 1,507.07 545.86 2,052.93167,752.61#174,499.28862016/02/17 1,511.93 541.00 2,052.93166,240.68#176,552.21872016/03/17 1,516.81 536.13 2,052.93164,723.87#178,605.14882016/04/17 1,521.70 531.23 2,052.93163,202.17#180,658.07892016/05/17 1,526.61 526.33 2,052.93161,675.57#182,711.01902016/06/17 1,531.53 521.40 2,052.93160,144.04#184,763.94912016/07/17 1,536.47 516.46 2,052.93158,607.57#186,816.87922016/08/17 1,541.42 511.51 2,052.93157,066.15#188,869.81932016/09/17 1,546.39 506.54 2,052.93155,519.75#190,922.74942016/10/17 1,551.38 501.55 2,052.93153,968.37#192,975.67952016/11/17 1,556.38 496.55 2,052.93152,411.99#195,028.60962016/12/17 1,561.40 491.53 2,052.93150,850.58#197,081.54972017/01/17 1,566.44 486.49 2,052.93149,284.14#199,134.47982017/02/17 1,571.49 481.44 2,052.93147,712.65#201,187.40992017/03/17 1,576.56 476.37 2,052.93146,136.09#203,240.33100 2017/04/17 1,581.64 471.29 2,052.93144,554.45#205,293.27101 2017/05/17 1,586.74 466.19 2,052.93142,967.71#207,346.20102 2017/06/17 1,591.86 461.07 2,052.93141,375.84#209,399.13103 2017/07/17 1,597.00 455.94 2,052.93139,778.85#211,452.06104 2017/08/17 1,602.15 450.79 2,052.93138,176.70#213,505.00105 2017/09/17 1,607.31 445.62 2,052.93136,569.3