数据分析与数据与数据处理.ppt

《数据分析与数据与数据处理.ppt》由会员分享，可在线阅读，更多相关《数据分析与数据与数据处理.ppt（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据分析与数据处理数据分析与数据处理动态名称的创建与应用：动态名称的创建与应用：8282 利用利用OFFSETOFFSET与与COUNTACOUNTA函数的组合，可以创建动态名函数的组合，可以创建动态名称；例如称；例如8282表格，如果行数记录发生变化的情况下，如表格，如果行数记录发生变化的情况下，如何保持计算结果的正确性呢？何保持计算结果的正确性呢？定义定义DATADATA为下面的公式：为下面的公式：=OFFSET($C$4,COUNTA($C:$C)-1)=OFFSET($C$4,COUNTA($C:$C)-1)缺陷：单元格中间不能有空格。缺陷：单元格中间不能有空格。单元格中下拉列表的创建

2、与数据有效性：单元格中下拉列表的创建与数据有效性：102102 在单元格中进行下拉列表的设置：在单元格中进行下拉列表的设置：102102；钩选忽略空格和；钩选忽略空格和提供下拉列表：提供下拉列表：利用有效性设置批注：利用有效性设置批注：103103；数据有效性输入信息选项卡；数据有效性输入信息选项卡；标题：提示：标题：提示：输入信息：请输入您的姓名。输入信息：请输入您的姓名。快速切换有效性的来源：快速切换有效性的来源：在通常情况下，当用户使用有效性时，只能处理一组数在通常情况下，当用户使用有效性时，只能处理一组数据源；在多组数据源下的数据列如何切换呢？据源；在多组数据源下的数据列如何切换呢？A

3、2A2单元格数据有效性下引入：单元格数据有效性下引入：=OFFSET(list.1,A1-1)=OFFSET(list.1,A1-1)排序与筛选：排序与筛选：超过超过3 3列的数据排序：列的数据排序：121121；由于在多列排序时，先被排序的列会在后面按其他列保由于在多列排序时，先被排序的列会在后面按其他列保持自己的序列；所以在本例中，或者直接使用排序按钮：持自己的序列；所以在本例中，或者直接使用排序按钮：顺序为：责任人、完成日期、开始日期、项目、类别。顺序为：责任人、完成日期、开始日期、项目、类别。或者：先完成日期为主关键字、责任人为次关键字排序或者：先完成日期为主关键字、责任人为次关键字排

4、序后，再以类别为主关键字、项目与开始日期为次关键字后，再以类别为主关键字、项目与开始日期为次关键字排序即可。排序即可。按特定的顺序排序：按特定的顺序排序：创建自定义序列：创建自定义序列：122122；多列排序要一次一次排：；多列排序要一次一次排：按笔画排序：按笔画排序：123123；按行排序：按行排序：124124；此时只能选择非标题列中数据区域。；此时只能选择非标题列中数据区域。按字符数量排序：按字符数量排序：125125；(设置字符数量列，最后删掉即设置字符数量列，最后删掉即可可)；125125 按颜色排序或筛选：按颜色排序或筛选：126126 随机排序：随机排序：127127；借用随机列

5、完成；借用随机列完成；排序字母与数字的混合内容：排序字母与数字的混合内容：128128；公式：公式：=LEFT(A1,1)&RIGHT(“000”&RIGHT(A1,LEN(A1)-1),3)=LEFT(A1,1)&RIGHT(“000”&RIGHT(A1,LEN(A1)-1),3)返回排序前的表格：返回排序前的表格：129129；借用排序列；借用排序列；自动筛选：自定义筛选；前自动筛选：自定义筛选；前5 5个；个；高级筛选：设置筛选条件；高级筛选：设置筛选条件；单个查询：单个查询：VLOOKUP()VLOOKUP()函数；模糊查询要求第一列升序函数；模糊查询要求第一列升序排列；区域可包括第一

6、列。排列；区域可包括第一列。分类汇总；两级分类汇总。分类汇总；两级分类汇总。IIPMTIIPMT函数：函数：固定利率，等额本息还款条件下计算在某一特定还款期固定利率，等额本息还款条件下计算在某一特定还款期内的需要向银行偿还的利息额。内的需要向银行偿还的利息额。语法：语法：IPMT(rate,Per,Nper,Pv,Fv,TypeIPMT(rate,Per,Nper,Pv,Fv,Type););假如你为购房贷款假如你为购房贷款100000100000元，如果采用元，如果采用1010年还清方式，年还清方式，年利率为年利率为7.11%,7.11%,每期末还款。每一月利息额计算公式为每期末还款。每一月

7、利息额计算公式为=IPMT(7.11%/12,1,120,-100000);=IPMT(7.11%/12,1,120,-100000);偿还利息有多少？偿还利息有多少？592.50.592.50.PPMTPPMT函数：函数：固定利率，等额本息还款条件下计算在某一特定还款期固定利率，等额本息还款条件下计算在某一特定还款期内的需要向银行偿还的本金额。内的需要向银行偿还的本金额。语法：语法：PPMT(rate,Per,Nper,Pv,Fv,typePPMT(rate,Per,Nper,Pv,Fv,type)假如你为购房贷款假如你为购房贷款100000100000元，如果采用元，如果采用1010年还清

8、方式，年还清方式，年利率为年利率为7.11%,7.11%,每期末还款。每一月利息额计算公式为每期末还款。每一月利息额计算公式为=PPMT(7.11%/12,1,120,-100000);=PPMT(7.11%/12,1,120,-100000);偿还利息有多少？偿还利息有多少？574.26.574.26.CUMIPMT()CUMIPMT()函数：函数：固定利率，等额本息还款条件下计算在给定的固定利率，等额本息还款条件下计算在给定的Start-Start-period,Endperiod,End-period-period期间累计向银行偿还的利息金额。期间累计向银行偿还的利息金额。语法：语法：C

9、UMIPMT(rate,Nper,Pv,Start-period,End-CUMIPMT(rate,Nper,Pv,Start-period,End-period,typeperiod,type););假如你为购房贷款假如你为购房贷款100000100000元，如果采用元，如果采用1010年还清方式，年还清方式，年利率为年利率为7.11%,7.11%,每期末还款。第一年支付的利息总数为多每期末还款。第一年支付的利息总数为多少？少？=CUMIPMT(7.11%/12,120,100000,1,12,0);-6880.94=CUMIPMT(7.11%/12,120,100000,1,12,0);-

10、6880.94 CUMPRINC()CUMPRINC()函数：函数：固定利率，等额本息还款条件下计算在给定的固定利率，等额本息还款条件下计算在给定的Start-Start-period,Endperiod,End-period-period期间累计向银行偿还的本金金额。期间累计向银行偿还的本金金额。语法：语法：CUMPRINC(rate,Nper,Pv,Start-period,End-CUMPRINC(rate,Nper,Pv,Start-period,End-period,typeperiod,type););假如你为购房贷款假如你为购房贷款100000100000元，如果采用元，如果采用

11、1010年还清方式，年还清方式，年利率为年利率为7.11%,7.11%,每期末还款。第一年支付的利息总数为多每期末还款。第一年支付的利息总数为多少？少？=CUMPRINC(7.11%/12,120,100000,1,12,0);-7120.20=CUMPRINC(7.11%/12,120,100000,1,12,0);-7120.20 单变量模拟运算表单变量模拟运算表:小王要贷款小王要贷款5050万元买房万元买房,年利率为年利率为12%,12%,还贷年限为还贷年限为3030年年,现在分析不同的年利率对现在分析不同的年利率对(假设在假设在11%-13%11%-13%之间变动之间变动)对对每月还贷

12、额度的影响每月还贷额度的影响.采用单变量模拟运算表来解决问题采用单变量模拟运算表来解决问题.双变量模拟运算表双变量模拟运算表:小王要贷款小王要贷款5050万元买房万元买房,年利率为年利率为12%,12%,还贷年限为还贷年限为3030年年,现在分析不同的年利率和贷款年限对现在分析不同的年利率和贷款年限对(假设在年利率假设在年利率11%-11%-13%-13%之间变动之间变动,还款年限在还款年限在26302630年之间变动年之间变动)对每月还对每月还贷额度的影响贷额度的影响.采用双变量模拟运算表来解决问题采用双变量模拟运算表来解决问题.规划求解规划求解:美航公司根据合同要求美航公司根据合同要求,向

13、客户提供水泵向客户提供水泵;按照规定一月按照规定一月份交货份交货3030台台,二月份交货二月份交货8080台台,三月份交货三月份交货9090台台;美航公美航公司的最大生产能力为每月司的最大生产能力为每月1111台台,生产成本为生产成本为:F(xF(x)=40X+0.25X)=40X+0.25X2 2;其中其中X X为台数为台数.很容易看出生产规模很容易看出生产规模越大越大,平均生产成本越低平均生产成本越低.如果生产的台数大于交货数量如果生产的台数大于交货数量,剩余部分可以下月交货剩余部分可以下月交货,但是每台水泵需要但是每台水泵需要5 5元的保存费元的保存费用用.因此因此,也不是规模越大越好也不是规模越大越好.如何安排每月的产量才能满如何安排每月的产量才能满足交货合同足交货合同,并使综合费用最低并使综合费用最低.