数据分析与数据与数据处理

数据分析与数据处理动态名称的创建与应用：82•利用OFFSET与COUNTA函数的组合，可以创建动态名称；例如82表格，如果行数记录发生变化的情况下，如何保持计算结果的正确性呢？•定义DATA为下面的公式：•=OFFSET($C$4,,,COUNTA($C:$C)-1)•缺陷：单元格中间不能有空格。单元格中下拉列表的创建与数据有效性：102•在单元格中进行下拉列表的设置：102；钩选忽略空格和提供下拉列表：•利用有效性设置批注：103；数据有效性输入信息选项卡；•标题：提示：•输入信息：请输入您的姓名。快速切换有效性的来源：•在通常情况下，当用户使用有效性时，只能处理一组数据源；在多组数据源下的数据列如何切换呢？•A2单元格数据有效性下引入：=OFFSET(list.1,,A1-1)排序与筛选：•超过3列的数据排序：121；•由于在多列排序时，先被排序的列会在后面按其他列保持自己的序列；所以在本例中，或者直接使用排序按钮：顺序为：责任人、完成日期、开始日期、项目、类别。•或者：先完成日期为主关键字、责任人为次关键字排序后，再以类别为主关键字、项目与开始日期为次关键字排序即可。按特定的顺序排序：•创建自定义序列：122；多列排序要一次一次排：•按笔画排序：123；•按行排序：124；此时只能选择非标题列中数据区域。•按字符数量排序：125；(设置字符数量列，最后删掉即可)；125•按颜色排序或筛选：126•随机排序：127；借用随机列完成；•排序字母与数字的混合内容：128；•公式：=LEFT(A1,1)&RIGHT(“000”&RIGHT(A1,LEN(A1)-1),3)•返回排序前的表格：129；借用排序列；•自动筛选：自定义筛选；前5个；•高级筛选：设置筛选条件；•单个查询：VLOOKUP()函数；模糊查询要求第一列升序排列；区域可包括第一列。•分类汇总；两级分类汇总。•IIPMT函数：•固定利率，等额本息还款条件下计算在某一特定还款期内的需要向银行偿还的利息额。•语法：IPMT(rate,Per,Nper,Pv,Fv,Type);•假如你为购房贷款100000元，如果采用10年还清方式，年利率为7.11%,每期末还款。每一月利息额计算公式为•=IPMT(7.11%/12,1,120,-100000);偿还利息有多少？•592.50.•PPMT函数：•固定利率，等额本息还款条件下计算在某一特定还款期内的需要向银行偿还的本金额。•语法：PPMT(rate,Per,Nper,Pv,Fv,type)•假如你为购房贷款100000元，如果采用10年还清方式，年利率为7.11%,每期末还款。每一月利息额计算公式为•=PPMT(7.11%/12,1,120,-100000);偿还利息有多少？•574.26.•CUMIPMT()函数：•固定利率，等额本息还款条件下计算在给定的Start-period,End-period期间累计向银行偿还的利息金额。•语法：CUMIPMT(rate,Nper,Pv,Start-period,End-period,type);•假如你为购房贷款100000元，如果采用10年还清方式，年利率为7.11%,每期末还款。第一年支付的利息总数为多少？•=CUMIPMT(7.11%/12,120,100000,1,12,0);-6880.94•CUMPRINC()函数：•固定利率，等额本息还款条件下计算在给定的Start-period,End-period期间累计向银行偿还的本金金额。•语法：CUMPRINC(rate,Nper,Pv,Start-period,End-period,type);•假如你为购房贷款100000元，如果采用10年还清方式，年利率为7.11%,每期末还款。第一年支付的利息总数为多少？•=CUMPRINC(7.11%/12,120,100000,1,12,0);-7120.20•单变量模拟运算表:•小王要贷款50万元买房,年利率为12%,还贷年限为30年,现在分析不同的年利率对(假设在11%--13%之间变动)对每月还贷额度的影响.采用单变量模拟运算表来解决问题.•双变量模拟运算表:•小王要贷款50万元买房,年利率为12%,还贷年限为30年,现在分析不同的年利率和贷款年限对(假设在年利率11%--13%之间变动,还款年限在26—30年之间变动)对每月还贷额度的影响.采用双变量模拟运算表来解决问题.•规划求解:•美航公司根据合同要求,向客户提供水泵;按照规定一月份交货30台,二月份交货80台,三月份交货90台;美航公司的最大生产能力为每月11台,生产成本为:•F(x)=40X+0.25X2;其中X为台数.很容易看出生产规模越大,平均生产成本越低.如果生产的台数大于交货数量,剩余部分可以下月交货,但是每台水泵需要5元的保存费用.•因此,也不是规模越大越好.如何安排每月的产量才能满足交货合同,并使综合费用最低.