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

山

数据分析与数据处理

动态名称的创建与应用：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(000RIGHT(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.

L

·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%,还贷年限为3