Excel函数、图表与数据分析典型实例

目录
第1篇 基础部分
第1章 Excel界面和基本操作
1.1 Excel用户界面
1.1.1 用户界面
1.1.2 功能区
1.1.3 对话框的使用
1.2 编辑工作簿
1.2.1 工作薄和工作表
1.2.2 创建新的工作薄
1.2.3 打开工作薄
1.3 编辑工作表
1.3.1 选定和重命名工作表
1.3.2 插入与删除工作表
1.3.3 移动或复制工作表
1.3.4 隐藏与显示工作表
1.4 输入数据
1.4.1 输入文本
1.4.2 输入数值
1.4.3 常用数据输入技巧
第2章 Excel函数应用基础
2.1 Excel公式概述
2.1.1 公式的组成
2.1.2 公式的输入
2.2 数据源的引用
2.2.1 引用相对数据源
2.2.2 引用绝对数据源
2.2.3 引用混合数据源
2.3 Excel函数应用基础
2.3.1 函数的输入
2.3.2 定义与使用名称
2.3.3 公式中的错误值
2.3.4 函数的分类
第2篇 Excel函数应用
第3章 数学和三角函数应用
3.1 常用的数学函数
3.1.1 SUM和SUMIF函数:计算产品的销售总额
3.1.2 ABS函数:计算产品销售差值
3.1.3 EXP和POWER函数:计算幂值
3.1.4 COMBIN函数:计算组合数
3.1.5 MOD函数:计算两数相除的余数
3.1.6 SQRT函数:计算平方根
3.1.7 LN、LOG和LOG10函数:计算对数
3.1.8 FACT和FACTDOUBLE函数:计算阶乘和双倍阶乘
3.1.9 INT函数:计算四舍五入后的整数值
3.1.10 CEILING和FLOOR函数:按条件舍入
3.1.11 ODD和EVEN函数:将数字舍入为奇、偶数
3.1.12 MROUND函数:计算指定基数舍入数值
3.1.13 GCD函数:计算最大公约数
3.1.14 LCM函数:计算最小公倍数
3.1.15 MULTINOMIAL函数:计算和的阶乘与阶乘乘积的比值
3.1.16 PI函数:返回数学常量
3.1.17 PRODUCT函数:返回乘积值
3.1.18 RAND函数:返回随机数
3.1.19 RANDBETWEEN函数:返回指定两数之间的随机数
3.1.20 ROUND函数:舍入数字
3.1.21 ROUNDDOWN和ROUNDUP函数:舍入数字
3.1.22 SERIESSUM函数:返回幂级数之和
3.1.23 ROMAN函数:转换成文本形式的罗马数字
3.1.24 SIGN函数:返回数字的符号
3.1.25 SORTPI函数:返回与PI乘积的平方根
3.1.26 SUMSQ函数:求平方和
3.1.27 TRUNC函数:取整函数
3.2 基本的三角函数
3.2.1 DEGREES和RADIANS函数:转换角和弧度
3.2.2 SIN、COS和TAN函数:计算正三角函数
3.2.3 ASIN、ACOS、ATAN函数:计算反三角函数
3.3 数组函数
3.3.1 SUMPRODUCT函数:计算数组乘积的和
3.3.2 SUMX2MY2和SUMX2PY2函数:计算平方差和平方和之和
3.3.3 SUMXMY2函数:计算差的平方和
3.3.4 MDETERM函数:计算数组矩阵行列式的值
3.3.5 MMULT函数:计算矩阵乘积
3.3.6 MINVERSE函数:计算逆矩阵
3.4 应用实例
3.4.1 根据员工的业绩统计工资及产品的库存量
3.4.2 计算员工领取工资的人民币各面值的张数
第4章 日期和时间函数应用
4.1 日期系统介绍
4.2 常用的计算日期的函数
4.2.1 TODAY函数:显示当前日期
4.2.2 NOW函数:显示当前时间
4.2.3 YEAR函数:转换日期的年份
4.2.4 MONTH函数:转换日期的月份
4.2.5 DAY函数:转换日期的天数
4.2.6 DAYS360:计算日期相差天数
4.2.7 WEEKDAY函数:确定星期几
4.2.8 WEEKNUM函数:确定某日期在一年中是第几周
4.2.9 NETWORKDAYS函数:计算两个日期之间工作日的天数
4.2.10 DATE函数:计算代表特定日期的序列号
4.2.11 DATEDIF函数:计算两个日期之间的差值
4.2.12 DATEVALUE函数:计算文本日期的序列数
4.2.13 EDATE函数:计算某个日期之前或之后指定月份的日期
4.2.14 EOMONTH函数:计算某个日期之前或之后指定月份最后一天的序列号
4.2.15 YEARFRAC函数:计算两个日期之间的天数占全年总天数的百分比
4.3 常用的计算时间的函数
4.3.1 HOUR函数:转换小时数
4.3.2 MINUTE函数:转换分钟数
4.3.3 SECOND函数:转换秒数
4.3.4 TIME函数:计算特定时间的小数值
4.3.5 TIMEVALUE函数:计算特定时间的小数值
4.4 应用实例
第5章 逻辑函数应用
5.1 基本的逻辑函数
5.1.1 FLLSE和TRUE函数:返回逻辑值
5.1.2 AND函数:进行逻辑与运算
5.1.3 OR函数:进行逻辑或运算
5.1.4 NOT函数:进行逻辑非运算
5.1.5 IF函数:按条件返回值
5.1.6 IFERROR函数:返回公式中的错误
5.2 应用实例
5.2.1 判断是否为闰年
5.2.2 评定学生成绩等级
第6章 查询和引用函数应用
6.1 基本的查询和引用函数
6.1.1 ADDRESS函数:显示引用地址
6.1.2 AREAS函数:计算引用中包含的区域个数
6.1.3 LOOKUP函数:查找数据
6.1.4 HLOOKUP函数:水平查找
6.1.5 VLOOKUP函数:竖直查找
6.1.6 MATCH函数:数组中查找值
6.1.7 CHOOSE函数:列值中查找值
6.1.8 COLUMN和ROW函数:计算引用的列标和行号
6.1.9 COLUMNS和ROWS函数:计算引用的数目
6.1.10 INDEX函数:得到指定的内容
6.1.11 INDIRECT函数:得到指定的内容并显示结果
6.1.12 HYPERLINK函数:创建跳转
6.1.13 OFFSET函数:调整新的引用
6.2 应用实例
第7章 文本函数应用
7.1 基本的文本函数
7.1.1 CHAR函数:将代码转换为对应的字符
7.1.2 CODE函数:将字符转换为对应的代码
7.1.3 FIND和FINDB函数:查找文本字符串
7.1.4 SEARCH和SEARCHB函数:查找文本字符串
7.1.5 EXACT函数:比较字符串
7.1.6 CONCATENATE函数:合并字符串
7.1.7 LEFT和LEFTB函数:返回左边字符串
7.1.8 RIGHT和RIGHTB函数:返回右边字符串
7.1.9 MID和MIDB函数:查找特定的字符
7.1.10 LEN和LENB函数:计算字符串长度
7.1.11 UPPER和LOWER函数:文本大小写转换
7.1.12 TEXT函数:将数值转换为文本
7.1.13 VALUE函数:将文本转换为数值
7.1.14 REPLACE和REPLACEB函数:替换文本
7.1.15 SUBSTITUTE函数:替换文本
7.1.16 REPT函数:复制文本
7.1.17 TRIM函数:清除文本中的空格
7.1.18 CLEAN函数:删除不能打印的字符
7.1.19 ASC和WIDECHAR函数:全角和半角英文字母的转换
7.1.20 DOLLAR和RMB函数:转换为货币文本
7.1.21 BAHTTEXT函数:转换为泰语文本
7.1.22 T函数:返回值引用的文本
7.2 应用实例
第8章 信息函数应用
8.1 IS类信息函数
8.1.1 ISBLANK函数:计算空白单元格数
8.1.2 ISNA函数:判断错误类型
8.1.3 ISEVEN和ISODD函数:判断参数的偶奇
8.1.4 ISLOGICAL函数:判断参数是否为逻辑值
8.1.5 ISNONTEXT函数:判断参数是否为非字符串
8.1.6 ISTEXT函数:判断参数是否为文本
8.1.7 ISNUMBER函数:判断参数是否为数字
8.1.8 ISREF函数:判断参数是否为引用
8.1.9 ISERR和ISERROR函数:判断参数是否为错误值
8.2 其他信息函数
8.2.1 CELL函数:获得单元格信息
8.2.2 ERROR.TYPE函数:判断参数的错误类型
8.2.3 TYPE函数:获得数值的类型
8.2.4 N函数:返回转换为数值的值
8.2.5 INFO函数:获得当前操作环境的信息
8.3 应用实例
第9章 数据库函数应用
9.1 常用数据库函数
9.1.1 DAVERAGE函数:计算平均值
9.1.2 DCOUNT函数:计算符合条件的数字单元格数
9.1.3 DCOUNTA函数:计算符合条件的非空单元格数
9.1.4 DGET函数:计算符合条件的单个值
9.1.5 DMAX和DMIN函数:计算符合条件的最值
9.1.6 DPRODUCT函数:计算指定数值的乘积
9.1.7 DSUM函数:计算符合条件的数值之和
9.1.8 DSTDEV和DSTDEVP函数:作为样本计算标准偏差
9.1.9 DVAR和DVARP函数:作为样本计算方差
9.2 应用实例
第10章 工程函数应用
10.1 计算贝塞尔(Bessel)值
10.1.1 BESSELI函数:计算修正的Bessel函数值
10.1.2 BESSELK函数:计算修正的Bessel函数值
10.1.3 BESSELJ函数:计算Bessel函数值
10.1.4 BESSELY函数:计算Bessel函数值
10.2 各种数制之间的转换
10.2.1 DEC2BIN和BIN2DEC:十进制和二进制的转换
10.2.2 DEC20CT和OCT2DEC函数:十进制和八进制的转换
10.2.3 DEC2HEX和HEX2DEC函数:十进制和十六进制的转换
10.2.4 BIN20CT和OCT2BIN函数:二进制和八进制的转换
10.2.5 BIN2HEX和HEX2BIN函数:二进制和十六进制的转换
10.2.6 OCT2HEX和HEX20CT函数:八进制和十六进制的转换
10.3 对复数进行处理
10.3.1 COMPLEX函数:转换为复数
10.3.2 IMABS函数:计算复数的模
10.3.3 IMREAL和IMAGINARY函数:计算实虚系数
10.3.4 IMCONJUGATE函数:计算共轭复数
10.3.5 IMSUM和IMSUB函数:计算复数的和与差
10.3.6 IMSIN和IMCOS函数:计算复数的正余弦
10.3.7 IMDIV函数:计算两个复数的商
10.3.8 IMEXP函数:计算复数的指数
10.3.9 IMLN、IMLOGIO和IMLOG2函数:计算复数的对数
10.3.10 IMPRODUCT函数:计算复数的乘积
10.3.11 IMSQRT函数:计算复数的平方根
10.3.12 IMPOWER函数:计算复数的n次幂
10.4 其他工程函数
10.4.1 CONVERT函数:度量衡转换
10.4.2 ERF和ERFC函数:返回误差积分数
10.4.3 DELTA函数:检测两个值是否相等
10.4.4 GESTEP函数:判断某值是否大于等于一个特定值
10.4.5 IMARGUMENT函数:返回以弧度表示的角
10.5 应用实例
10.5.1 度量衡转换
10.5.2 核对数据
第11章 统计函数应用
11.1 一般的统计函数
11.1.1 AVERAGE和AVERAGEA函数:计算平均值
11.1.2 MAX和MIN函数:计算最大值和最小值
11.1.3 MAXA和MINA函数:计算最大值和最小值
11.1.4 COUNT和COUNTA函数:统计单元格个数
11.1.5 COUNTIF函数:按条件统计
11.1.6 COUNTBLANK函数:计算空白单元格的个数
11.1.7 FREQUENCY函数:计算员工的年龄分布情况
11.1.8 RANK函数:计算员工的销售业绩排名
11.1.9 MODE函数:返回出现频率最多的数值
11.1.10 LARGE和SMALL函数:按条件返回最值
11.1.11 MEDIAN函数:返回中值
11.1.12 QUARTILE函数:计算四分位数
11.1.13 GROWTH函数:预测指数增长值
11.1.14 GEOMEAN函数:计算几何平均值
11.2 数理统计
11.2.1 VAR和VARA函数:计算样本方差
11.2.2 STDEV和STDEVA函数:计算标准偏差
11.2.3 STDEVP和STDEVPA函数:计算样本总体的标准偏差
11.2.4 NORMDIST函数:计算正态累积分布函数
11.2.5 NORMINV函数:计算正态累积分布函数的反函数
11.2.6 NORMSDIST函数:计算标准正态累积分布函数
11.2.7 NORMSINV函数:计算标准正态累积分布函数的反函数
11.2.8 TDIST函数:计算学生T分布的概率
11.2.9 TINV函数:计算学生T分布的T值
11.2.10 FOIST函数:计算F概率分布
11.2.11 FINV函数:计算F概率分布的反函数
11.2.12 FTEST函数:计算F检验的结果
11.2.13 CONFIDENCE函数:计算置信区间
11.2.14 CORREL函数:计算相关系数
11.2.15 AVEDEV函数:计算绝对偏差平均值
11.2.16 BETADIST函数:返回Beta累积分布函数
11.2.17 BETAINV函数:返回Beta累积分布的反函数
11.2.18 BINOMDIST函数:返回一元二项式分布的概率值
11.2.19 CHIDIST函数:返回x2分布的单尾概率
11.2.20 CHIINV函数:返回x2分布的单尾概率的反函数
11.2.21 CRITBINOM函数:返回累积二项式分布大于等于临界值的最小值
11.2.22 CHITEST函数:返回独立性检验值
11.2.23 TRIMMEAN函数:返回数据集的内部平均值
11.2.24 COVAR函数:计算协方差
11.2.25 DEVSQ函数:计算平均值偏差的平方和
11.2.26 EXPONDIST函数:计算指数分布
11.2.27 FORECAST函数:根据已有的数值返回未来值
11.2.28 FISHER函数:返回x点的Fisher变换
11.2.29 FISHERINV函数:返回Fisher变换的反函数值
11.2.30 GAMMADIST函数:计算伽玛分布
11.2.31 GAMMAINV函数:计算伽玛分布的反函数
11.2.32 GAMMALN函数:计算伽玛分布的自然对数
11.2.33 HARMEAN函数:计算数据集合的调和平均值
11.2.34 HYPGEOMDIST函数:返回超几何分布
11.2.35 INTERCEPT函数:计算截距
11.2.36 KURT函数:返回数据集的峰值
11.2.37 TREND函数:返回线性回归拟合线的Y值
11.2.38 LINEST函数:对已知数据进行最佳直线拟合
11.2.39 LOGEST函数:计算最佳指数回归拟合曲线
11.2.40 LOGNORMDIST函数:返回x的对数累积分布函数
11.2.41 LOGINV函数:返回x的对数累积分布函数的反函数
11.2.42 NEGBINOMDIST函数:返回负二项式分布
11.2.43 PEARSON函数:返回Pearson乘积矩相关系数
11.2.44 RSO函数:计算Pearson乘积矩相关系数的平方
11.2.45 PERCENTILE函数:返回数值的第K个百分点的值
11.2.46 PERCENTRANK函数:返回特定数值的百分比排位
11.2.47 POISSON函数:返回泊松分布
11.2.48 WEIBULL函数:返回韦伯分布
11.2.49 PROB函数:返回特定数值落在某区间的概率
11.2.50 SKEW函数:返回分布的偏斜度
11.2.51 SLOPE函数:计算拟合的线性回归直线的斜率
11.2.52 ZTEST函数:返回z检验的单尾概率值
11.3 应用实例
11.3.1 统计选手得分和名次
11.3.2 统计员工培训成绩表
第12章 财务函数应用
12.1 有关本金和利息计算的财务函数
12.1.1 PMT函数:计算银行贷款的每期偿还额
12.1.2 PPMT和IPMT函数:计算还款中的本金和利息
12.1.3 CUMPRINC和CUMIPMT函数:计算阶段本金和利息
12.1.4 COUPDAYBS函数:计算截止到成交日的天数
12.1.5 COUPDAYS函数:计算成交日所在的付息日的天数
12.1.6 COUPDAYSNC函数:计算成交日到下一个付息日之间的天数
12.1.7 COUPNCD函数:计算成交日之后下一个付息日的数字
12.1.8 COUPNUM函数:计算成交日和到期日之间的付息次数
12.1.9 COUPPCD函数:计算成交日之前的上一个付息日
12.1.10 ISPMT函数:返回特定投资期内要支付的利息
12.1.11 RECEIVED函数:返回到期收回的总金额
12.2 有关投资计算的财务函数
12.2.1 PV函数:计算投资的现值
12.2.2 NPV函数:计算非固定回报投资的净现值
12.2.3 FV函数:计算某项投资的未来值
12.2.4 XNPV函数:计算不定期发生的现金流的净现值
12.2.5 FVSCHEDULE函数:计算利率可变时的未来值
12.3 有关报酬率计算的财务函数
12.3.1 IRR函数:计算现金流的内部收益率
12.3.2 MIRR函数:计算现金流的修正内部收益率
12.3.3 XIRR函数:计算不定期发生现金流的修正内部收益率
12.3.4 EFFECT函数:计算有效的年利率
12.3.5 NOMINAL函数:计算名义年利率
12.3.6 TBILLEQ函数:计算国库券的等效收益率
12.3.7 TBILLPRICE函数:计算国库券的价格
12.3.8 TBILLYIELD函数:计算国库券的收益率
12.4 有关折旧计算的函数
12.4.1 SLN函数:计算线性折旧值
12.4.2 SYD函数:按年限总和折旧法计算折旧
12.4.3 DB函数:用固定余额递减法计算折旧
12.4.4 DDB函数:用双倍余额递减法计算折旧
12.4.5 AMORDEGRC函数:计算每个折旧期间的折旧值
12.4.6 AMORLINC函数:按线性折旧法计算折旧值
12.4.7 VDB函数:计算指定期间的资产折旧值
12.5 有关证券计算的函数
12.5.1 INTRATE函数:计算一次性付息证券的利率
12.5.2 PRICE函数:计算定期付息有价证券的价格
12.5.3 PRICEDISC函数:计算折价发行的有价证券的价格
12.5.4 PRICEMAT函数:计算到期付息的有价证券的价格
12.5.5 YIELD函数:计算有价证券的收益率
12.5.6 YIELDDISC函数:计算折价发行的有价证券的年收益率
12.5.7 YIELDMAT函数:计算到期付息的有价证券的年收益率
12.5.8 DISC函数:计算有价证券的贴现率
12.5.9 DURATION函数:计算有价证券的修正期限
12.5.10 ODDFPRICE函数:计算有价证券的价格
12.5.11 ODDFYIELD函数:计算有价证券的收益率
12.5.12 ODDLPRICE函数:计算末期付息日不固定的有价证券的价格
12.5.13 ODDLYIELD函数:计算末期付息日不固定的有价证券的收益率
12.6 应用实例
12.6.1 计算应还利息和本金
12.6.2 贷款经营表
第3篇 Excel图表操作
第13章 图表操作基础
13.1 创建图表
13.1.1 选择数据源并创建图表
13.1.2 插入图表标题
13.1.3 图表的组成和选中
13.2 各种图表类型及应用范围
13.2.1 柱形图
13.2.2 条形图
13.2.3 折线图
13.2.4 XY散点图
13.2.5 饼图
13.2.6 圆环图
13.2.7 曲面图
13.2.8 股价图
13.2.9 雷达图
13.3 编辑图表
13.3.1 移动和更改图表大小
13.3.2 更改图表类型
13.3.3 设置图表文字格式
13.3.4 设置布局格式
13.3.5 设置坐标轴格式
13.3.6 设置图表对象的填充效果和边框
13.3.7 更改图表数据源
第14章 柱形图和条形图
14.1 柱形图
14.1.1 绘制产品销售数量图表
14.1.2 绘制员工职称级别与工资关系图
14.1.3 企业收入与支出的对比
14.2 条形图
14.2.1 绘制每天网站访问人数图
14.2.2 绘制显示员工工资分布区间图表
第15章 折线图和散点图
15.1 折线图
15.1.1 绘制气温变化趋势图
15.1.2 绘制价格趋势图
15.2 散点图
15.2.1 绘制函数图像
15.2.2 绘制广告额与产品销售额关系图表
第16章 饼图和圆环图
16.1 饼图
16.1.1 绘制年度支出比例图
16.1.2 绘制复合饼图分析企业存款情况
16.2 圆环图
16.2.1 绘制公司支出比例图
16.2.2 利用仪表式的图表反映仪器工作状态
第17章 其他图表
17.1 股价图
17.2 面积图
17.3 雷达图
第4篇 Excel数据统计
第18章 基本数据分析
18.1 数据排序
18.1.1 数据排序的规则
18.1.2 按列排序
18.1.3 按行排序
18.1.4 按多列或多行排序
18.2 数据筛选
18.2.1 自动筛选
18.2.2 高级筛选
18.3 分类汇总
18.3.1 简单的分类汇总
18.3.2 多级分类汇总
18.3.3 分级显示数据列表
第19章 数据透视表
19.1 数据透视表简介
19.1.1 数据透视表
19.1.2 适合数据透视表的数据
19.2 创建数据透视表
19.2.1 指定数据和数据透视表的存放位置
19.2.2 指定数据透视表的布局
19.2.3 设置数据透视表的格式
19.3 修改数据透视表
19.3.1 更改数据透视表中的数据
19.3.2 对数据透视表中的数据进行排序
19.3.3 改变数据透视表的汇总方式
19.3.4 创建数据透视图
19.3.5 复制和删除数据透视表
第20章 规划求解
20.1 安装规划求解
20.2 规划求解
20.2.1 问题描述
20.2.2 建立工作表
20.2.3 使用规划求解工具求解
20.2.4 创建规划求解报告
20.3 规划求解相关操作
20.3.1 更改规划求解的求解方法
20.3.2 加载规划求解模型
20.4 规划求解实例
第5篇 Excel综合应用实例
第21章 综合应用实例
21.1 学生成绩的处理与分析
21.1.1 学生成绩表的统计
21.1.2 学生成绩表的分析
21.1.3 学生成绩表的查询
21.2 员工工资管理
21.2.1 计算应发工资
21.2.2 计算扣税所得额和个人所得纳税
21.2.3 计算实发工资
21.2.4 制作工资条
21.2.5 对工资进行部门汇总
21.2.6 绘图表示工资项目比例分布
21.3 建立贷款还息动态图
21.3.1 图表最终效果展示与分析
21.3.2 根据已有条件建立图表数据源
21.3.3 创建贷款还息簇状柱形图
21.3.4 建立饼图同步显示
21.3.5 添加控件实现自主控制