Excel函数与公式速查宝典（视频案例版彩色印刷）

本书由Excel精英部落组织编写。Excel精英部落是一个Excel技术研讨、项目管理、培训咨询和图书创作的Excel办公协作联盟，其成员多为长期从事行政管理、人力资源管理、财务管理、营销管理、市场分析及Office相关培训的工作者。本书具体编写人员有吴祖珍、姜楠、陈媛、王莹莹、汪洋慧、张发明、吴祖兵、李伟、彭志霞、陈伟、杨国平、张万红、徐宁生、王成香、郭伟民、徐冬冬、袁红英、殷齐齐、韦余靖、徐全锋、殷永盛、李翠利、柳琪、杨素英、张发凌等。

第1章  公式与函数基础

1.1  使用公式进行数据计算

1．公式的运算符

2．输入公式

3．复制公式完成批量计算

1.2  公式计算中函数的使用

1．函数的作用

2．函数的构成

3．用“函数参数”向导设置函数参数

4．函数嵌套运算

1.3  公式计算中的数据源的引用

1．引用相对数据源

2．引用数据源

3．引用当前工作表之外的单元格

4．定义名称方便数据引用

1.4  数组运算

1．多单元格数组公式

2．单个单元格数组公式

第2章  逻辑函数

2.1  “与”“或”条件判断

1．AND（判断多个条件是否同时成立）

2．OR（判断多个条件是否至少有一个条件成立）

3．TRUE（返回逻辑值TRUE）

4．FALSE（返回逻辑值FALSE）

5．NOT（对逻辑值求反）

2.2  IF函数（根据条件判断返回指定的值）

第3章  数学与三角函数

3.1  求和及按条件求和运算

1．SUM（对给定的数据区域求和）

2．SUMIF（按照指定条件求和）

3．SUMIFS（对满足多重条件的单元格求和）

4．SUMPRODUCT（将数组间对应的元素相乘，并返回乘积之和）

3.2  数据的舍入

1．ROUND（对数据进行四舍五入）

2．INT（将数字向下舍入到接近的整数）

3．TRUNC（不考虑四舍五入对数字进行截断）

4．ROUNDUP（远离零值向上舍入数值）

5．ROUNDDOWN（靠近零值向下舍入数值）

6．CEILING.PRECISE（向上舍入到接近指定数字的某个值的倍数值）

7．FLOOR.PRECISE（向下舍入到接近指定数字的某个值的倍数值）

8．MROUND（舍入到接近指定数字的某个值的倍数值）

9．EVEN（将数字向上舍入到接近的偶数）

10．ODD（将数字向上舍入到接近的奇数）

11．QUOTIENT（返回商的整数部分）

3.3  其他数据学运算函数

1．ABS函数（求值）

2．SUMSQ（计算所有参数的平方和）

3．SUMXMY2（求两个数组中对应数值之差的平方和）

4．SUMX2MY2（求两个数组中对应数值的平方差之和）

5．PRODUCT（求指定的多个数值的乘积）

6．MOD（求两个数值相除后的余数）

7．GCD（求两个或多个整数的公约数）

8．LCM（计算两个或多个整数的小公倍数）

9．SQRT（数据的算术平方根）

10．SQRTPI（计算指定正数值与π的乘积的平方根值）

11．SUMTOTAL（返回列表或数据库中的分类汇总）

3.4  阶乘、随机与矩阵计算

1．FACT（计算指定正数值的阶乘）

2．FACTDOUBLE（返回数字的双倍阶乘）

3．MULTINOMIAL（返回参数和的阶乘与各参数阶乘乘积的比值）

4．RAND（返回大于等于0小于1的随机数）

5．RANDBETWEEN（返回指定数值之间的随机数）

6．MDETERM（返回一个数组的矩阵行列式的值）

7．MINVERSE（返回数组矩阵的逆矩阵）

8．MMULT（返回两个数组的矩阵乘积）

3.5  指数、对数与幂计算

1．EXP（返回e为底数指定指数的幂值）

2．LN（计算一个数的自然对数）

3．LOG（计算指定底数的对数值）

4．LOG10（计算以10为底数的对数值）

5．POWER（返回给定数字的乘幂）

3.6  三角函数计算

1．RADIANS（将角度转换为弧度）

2．DEGREES（将弧度转换为角度）

3．SIN（返回某一角度的正弦值）

4．COS（返回某一角度的余弦值）

5．TAN（返回某一角度的正切值）

6．ASIN（返回数值的反正弦值）

7．SINH（返回双曲正弦值）

8．ASINH（返回指定实数的反双曲正弦值）

9．ACOS（返回数值的反余弦值）

10．COSH（返回任意实数的双曲余弦值）

11．ACOSH（返回指定实值的反双曲余弦值）

12．ATAN（返回数字的反正切值）

13．ATAN2（返回直角坐标系中给定X及Y的反正切值）

14．TANH（返回任意实数的双曲正切值）

15．ATANH（返回参数的反双曲正切值）

16．PI（返回圆周率π）

第4章  统计函数

4.1  求平均值及按条件求平均值计算

1．AVERAGE（计算算术平均值）

2．AVERAGEA（返回平均值）

3．AVERAGEIF（返回满足条件的平均值）

4．AVERAGEIFS（返回满足多重条件的平均值）

5．GEOMEAN（返回几何平均值）

6．HARMEAN（返回数据集的调和平均值）

7．TRIMMEAN（截头尾返回数据集的平均值）

4.2  统计符合条件的数据条目数

1．COUNT（统计含有数字的单元格个数）

2．COUNTA（统计包括文本和逻辑值的单元格数目）

3．COUNTIF（统计满足给定条件的单元格的个数）

4．COUNTIFS（统计同时满足多个条件的单元格的个数）

5．COUNTBLANK（计算空白单元格的数目）

4.3  小值统计

1．MAX（返回数据集的值）

2．MIN（返回数据集的小值）

3．MAXA（返回参数列表的值）

4．MINA（返回参数列表的小值）

5．LARGE（返回表格某数据集的某个值）

6．SMALL（返回某数据集的某个小值）

4.4  排位统计

1．MEDIAN（返回中位数）

2．RANK.EQ（返回数组的排位）

3．RANK.AVG（返回数字列表中的排位）

4．QUARTILE.INC（返回四分位数）

5．PERCENTILE.INC（返回第k个百分点值）

6．PERCENTRANK.INC（返回百分比排位）

4.5  方差、协方差与偏差

1．VAR.S（计算基于样本的方差）

2．VARA（计算基于样本的方差）

3．VAR.P（计算基于样本总体的方差）

4．VARPA（计算基于样本总体的方差）

5．STDEV.S（计算基于样本估算标准偏差）

6．STDEVA（计算基于给定样本的标准偏差）

7．STDEV.P（计算样本总体的标准偏差）

8．STDEVPA（计算样本总体的标准偏差）

9．COVARIANCE.S（返回样本协方差）

10．COVARIANCE.P（返回总体协方差）

11．DEVSQ（返回平均值偏差的平方和）

12．AVEDEV函数（计算数值的平均偏差）

4.6  数据预测

1．LINEST（对已知数据进行直线拟合）

2．TREND（构造线性回归直线方程）

3．LOGEST（回归拟合曲线返回该曲线的数值）

4．GROWTH（对给定的数据预测指数增长值）

5．FORECAST（根据已有的数值计算或预测未来值）

6．SLOPE（求一元线性回归的斜率）

7．INTERCEPT（求一元线性回归的截距）

8．CORREL（求一元线性回归的相关系数）

9．STEYX（返回预测值时产生的标准误差）

4.7  假设检验

1．Z.TEST（返回z检验的单尾P值）

2．T.TEST（返回t检验的双尾P值）

3．F.TEST（返回f检验的结果）

4.8  概率分布函数

4.8.1  二项式分布概率

1．BINOM.DIST（返回一元二项式分布的概率）

2．BINOM.INV（返回使累积二项式分布大于等于临界值的小值）

3．BINOM.DIST.RANGE 函数（返回试验结果的概率）

4．NEGBINOM.DIST（返回负二项式分布）

4.8.2  正态分布概率

1．NORM.DIST（返回指定平均值和标准偏差的正态分布函数）

2．NORM.INV（返回正态累积分布的反函数值）

3．NORM.S.DIST（返回标准正态分布的累积函数）

4．NORM.S.INV（返回标准正态分布反函数值）

5．LOGNORM.DIST（返回x的对数累积分布函数）

6．LOGNORM.INV（返回x的对数累积分布函数的反函数值）

4.8.3  X²分布概率

1．CHISQ.DIST （返回X²分布）

2．CHISQ.INV（返回X²分布的左尾概率的反函数）

3．CHISQ.DIST.RT（返回X²分布的右尾概率）

4．CHISQ.INV.RT（返回X²分布的右尾概率的反函数）

4.8.4  t分布概率

1．T.DIST（返回学生的左尾t分布）

2．T.DIST.RT（返回学生的右尾t分布）

3．T.DIST.2T（返回学生的双尾t分布）

4．T.INV（返回学生t分布的左尾反函数）

5．T.INV.2T（返回学生t分布的双尾反函数）

4.8.5  F概率分布

1．F.DIST（返回两个数据集的（左尾）F概率分布）

2．F.DIST.RT（返回两个数据集的（右尾）F 概率分布）

3．F.INV（返回（左尾）F概率分布函数的反函数）

4．F.INV.RT（返回（右尾）F概率分布的反函数值）

4.8.6  Beta分布概率

1．BETA.DIST（返回 Beta 分布）

2．BETA.INV（返回Beta累积概率密度函数的反函数值）

4.9  其他函数

1．PERMUT（返回排列数）

2．PERMUTATIONA函数（允许重复的情况下返回排列数）

3．MODE.SNGL（返回数组中的众数）

4．MODE.MULT（返回一组数据集中出现频率的数值）

5．FREQUENCY（频数分布统计）

6．PROB（返回数值落在指定区间内的概率）

7．HYPGEOM.DIST（返回超几何分布）

8．KURT（返回数据集的峰值）

9．SKEW（返回分布的偏斜度）

10．CONFIDENCE.T（学生t分布返回总体平均值的置信区间）

11．CONFIDENCE.NORM（正态分布返回总体平均值的置信区间）

12．PEARSON（返回Pearson乘积矩相关系数）

13．RSQ（返回皮尔生乘积矩相关系数的平方）

14．GAMMA.DIST（返回伽玛分布函数的函数值）

15．GAMMA.INV（返回伽玛累积分布函数的反函数值）

16．GAMMALN（返回伽玛函数的自然对数）

17．GAMMALN.PRECISE（返回伽玛函数的自然对数）

第5章  文本函数305

5.1  文本合并、长度统计、文本比较函数

1．CONCATENATE（合并两个或多个文本字符串）

2．LEN（返回文本字符串的字符数）

3．LENB（返回文本字符串的字节数）

4．EXACT（比较两个文本字符串是否完全相同）

5．REPT（按照给定的次数重复文本）

6．TRIM（删除文本中的多余空格）

7．CLEAN（删除文本中不能打印的字符）

5.2  查找字符在字符串中的位置

1．FIND（查找指定字符中在字符串中的位置）

2．FINDB（查找指定字符串在字符串中的位置（按字节算））

3．SEARCH（查找字符串的起始位置）

4．SEARCHB（查找字符串的起始位置（按字节算））

5.3  提取文本

1．LEFT（按指定字符数从左侧提取字符）

2．LEFTB（按指定字节数从左侧提取字符）

3．RIGHT（按指定字符数从右侧提取字符）

4．RIGHTB（按指定字节数从右侧提取字符）

5．MID（从任意位置提取指定数量的字符）

6．MIDB（从任意位置提取指定字节数的字符）

5.4  文本新旧替换

1．REPLACE（用指定的字符和字符数替换文本字符串中的部分文本）

2．REPLACEB（用指定的字符和字节数替换文本字符串中的部分文本）

3．SUBSTITUTE（替换旧文本）

5.5  文本格式转换

1．TEXT（设置数字格式并将其转换为文本）

2．DOLLAR（四舍五入数值，并添加千分位符号和$符号）

3．RMB（四舍五入数值，并添加千分位符号和￥符号）

4．FIXED（将数字显示为千分位格式并转换为文本）

5．UPPER（将文本转换为大写形式）

6．LOWER（将文本转换为小写形式）

7．PROPER（将文本字符串的首字母转换成大写）

8．VALUE（将文本型数字转换成数值）

9．ASC（将全角字符转换为半角字符）

10．WIDECHAR（将半角字符转换为全角字符）

11．CODE（返回文本字符串中个字符的数字代码）

12．CHAR（返回对应于数字代码的字符）

13．BAHTTEXT函数（将数字转换为泰语文本）

14．T（判断给定的值是否是文本）

第6章  日期与时间函数

6.1  返回当前日期

1．NOW（返回当前日期与时间）

2．TODAY（返回当前的日期和时间）

6.2  构建与提取日期

1．DATE（构建标准日期）

2．YEAR（返回某日对应的年份）

3．MONTH（返回日期中的月份）

4．DAY（返回日期中的天数）

5．WEEKDAY（返回指定日期对应的星期数）

6．WEEKNUM（返回日期对应一年中的第几周）

7．EOMONTH（返回指定月份前(后)几个月后一天的序列号）

6.3  日期计算

1．DATEDIF（用指定的单位计算起始日和结束日之间的天数）

2．DAYS360（按照一年360天的算法计算两日期间相差的天数）

3．YEARFRAC（从开始日到结束日所经过的天数占全年天数的比例）

4．EDATE（计算间隔指定月份数后的日期）

6.4  关于工作日的计算

1．WORKDAY（获取间隔若干工作日后的日期）

2．WORKDAY.INTL函数

3．NETWORKDAYS（计算两个日期间的工作日）

4．NETWORKDAYS.INTL函数

6.5  时间函数

1．TIME（构建标准时间格式）

2．HOUR（返回时间值的小时数）

3．MINUTE（返回时间值的分钟数）

4．SECOND（返回时间值的秒数）

6.6  文本日期与文本时间的转换

1．DATEVALUE（将日期字符串转换为可计算的序列号）

2．TIMEVALUE（将时间转换为对应的小数值）

第7章  查找和引用函数

7.1  ROW与COLUMN函数

1．ROW（返回引用的行号）

2．ROWS（返回引用中的行数）

3．COLUMN（返回引用的列标）

4．COLUMNS（返回引用中包含的列数）

7.2  LOOK类函数

1．VLOOKUP（在数组列中查找并返回指定列中同一位置的值）

2．LOOKUP（查找并返回同一位置的值）