大西瓜的小院
large picture of the cover

Execl的基本使用

Excel的学习与经验利用

一、Excel的基本使用

​ 首先，明确Excel是进行制作表格与数据分析的一项强大的软件对我们学习该软件是十分重要的，本文档旨在将常用的功能、快捷键、函数与VBA功能进行总结方便日后使用过程中的查询。

1. 11个基本操作与思路

​ 打开Excel首先应该进行的7个操作(Excel的根本在于制作“容易看”的表格)。为了达成“容易看”的原则，在制作表格时有以下7个基本规则和4个美化方法

  • 基本规则1 根据用途和输出来决定字体
  • 基本规则2 调整行高
  • 基本规则3 制表是不要盲目从A1开始
  • 基本规则4 文字左对齐，数字右对齐(当然也可以根据自己的要求居中)
  • 基本规则5 标明数值的千分位隔符和单位
  • 基本规则6 设置缩进(该功能在开始->对齐方式)
  • 基本规则7 调整列宽(可以多选几行统一设置)
  • 美化方法1 视情况隐藏网格线
  • 美化方法2 结合表格内容画出边框
  • 美化方法3 区分使用数值的颜色
  • 美化方法4 设置背景色

==单元格中所出现的主要错误提示一览==

  • Excel表中出现的主要的提示和错误信息
显示内容 错误信息的内容和处理方法
单元格左上角的三角记号 一般用鼠标移动到三角可以查看错误原因和解决方案
###### 相对于输入的值列宽不足时的提示信息，拉大列宽即可显示
1E+10 输入非常大的数据时，将会以科学计数法显示该值。设置千位分隔符或者拉大列宽后将显示正常数值
#NAME? 单元格名称指定或者函数不正确时的提示信息。确定输入值正确性后修正
#REF! 无法引用指定单位格时的提示信息。该错误信息多出现在有删除操作时，可以确认一下引用处输入值是否正确并修正
#VALUE! 输入了不恰当的值。修改输入值
#DIV/0! 用0做除法运算的提示信息。
#N/A 缺少必要的值时的提示信息。修正输入值
#NUM! Excel计算中输入的值过大或者过小。修正输入值
#NULL! “半角空格运算符”是用来指定两个单元格区域的交集的，当半角空格前后的单元格区域内不存在交集时就会报错，将半角空格改为逗号或冒号

2. 高效技巧

  1. 高效管理Excel工作表
  • 为工作表指定合适的排序方式规则
  • 根据内容和目的设置表格标签的颜色
  • 适当清理无效的数据表
  • 将工作表表名设置为与内容相符的短名称
    • 注意：不可用于工作表名称的符号:冒号(:)、货币符号(￥)、斜杠(/)、问好(?)、星号(*)、方括号([])不可用于工作表名称
  1. 隐藏功能与组合功能
  • Excel中可以右键选择对单元格或数据表隐藏，但要少用除非需要备份原始数据表可以
  • 在数据菜单中有组合功能，在处理批量数据时需要根据需求进行分组隐藏时可以使用该功能提高管理效率
  • 组合最多三级嵌套
  1. 选择”跨列居中“而不是合并单元格
  • 该功能位于位于[设置单元格格式->对齐->跨列居中]
  • 纵向合并时先合并再旋转90°
  1. 跨单元格斜线画法
  • 使用[插入->形状->直线]，按住Alt键能画出对准单元格边框的线
  1. 为单元格增加批注
  • 以批注形式记录补充事项与评论[审阅->新建批注]
  1. 条件格式的基本操作(操作很多具体不在基础篇展开)
  • 让想强调注意的数据更加醒目
  • 可以设置和更改满足指定条件的单元格的格式
  • 可以迅速找到要重点注意的数据和可能输入有误的地方
  • 一个单元格内可以设置多条格式规则
  1. 窗格冻结
  • 简单的单行冻结，不详述
  • 多行冻结[选择需要冻结的区域的下一行(下一列)的首个格子->冻结窗格]
  1. 快速输入当时的时间和日期
  • 现在的日期：Ctrl+;
  • 现在的时间：Ctrl+Shift+;
  1. 禁止编辑工作表
  • 审阅->保护工作表/保护工作簿
  1. 公式与函数基础

​ 公式是为了解决某个计算问题而建立的计算式。公式以等号开头，中间使用运算符连接。但公式并非只是用于常量间的运算。

  • 公式的运算符(4类)
    • 算数运算符（“+”，“-”，“*”，“/”等）
    • 比较运算符（“>”，“<”，“>=”，“<>”等）
    • 文本连接运算符（仅一个&，用于连接多个单元格的字符串，且其中可加入符号例如：[=A2&“：”B2]）
    • 引用运算符（三个）
运算符 作用 示例
：（冒号） 表示整个区域都作为运算对象 A1:D8
，（逗号） 联合多个特定区域引用计算 SUM(A1:C2,C2:D10)
（空格） 交叉运算，即对两个共同引用区域中共有的单元格进行计算 A1:B8 B1:D8
  • 函数
    • 函数是为了简化复杂的计算公式
    • 函数的构成（等号+函数名称+括号内的参数）
    • 函数可以嵌套

二、Excel的实用功能

1. 数据的输入

1.1 基本输入技巧

1.1.1 输入生僻字的技巧

输入和生僻字部首相同的汉字->选中该字->插入->符号->找到目标字

1.1.2 输入3/4变“3月4日”

Excel会默认用数字斜杠表示日期。

  • 方法1：在输入分数前先输入“0”，例如[0 3/4]；

  • 方法2：设置单元格格式为分数

1.1.3 “墨迹公式”方便的写公式

过程略，但输入完公式后，后期如果需要调整公式，可以在“公式工具”中选择相同的命令进入修改

1.1.4 编辑文本时使用特殊符号修饰

插入->符号

1.1.5 输入身份证号码或长编码

单元格格式改为文本重新输入

1.1.6 输入以0开始的数据

  • 方法1：在输入数据前加（’）即可（会显示问题，建议少用）
  • 方法2：设置单元格格式为文本重新输入

1.1.7 快速输入规范日期

  • 方法1：

1.2 批量输入技巧

1.3 外部数据的导入

2. 数据验证

2.1 数据验证设置

2.2

3. 自定义单元格格式与输入特殊数据

4. 数据查找/替换和复制/粘贴

5. 数据编排与整理

6. 表格的设置与美化

7. 打印表格

三、 数据分析

1. 条件格式

1.1 可以应用的格式类型

1.2 创建自己的规则

1.3 使用图形的条件格式

1.4 创建基于公式的规则

1.5 条件格式规则的管理

2. 数据筛选

2.1 数值筛选

2.2 文本筛选

2.3 日期筛选

2.4 高级筛选

2.5 其他筛选技巧

3. 排序、分类汇总

3.1 数据排序

3.2 分类汇总

4. 透视表

4.1 创建数据透视表

4.2 数据透视表的示例

4.3 数据透视表的显示效果

4.4 值字段的设置

4.5 报表数据的查看

4.6 创建数据透视表

5. 图表

5.1 按分析目的选表图

5.2 迷你图的应用

5.3 编辑图表对象

5.4 图表美化

5.5 应用示例

6 高级分析工具

6.1 模拟运算与单变量求解

6.2 规划求解

6.3 分析工具库

四、函数

1. 逻辑函数

1.1 “与”“或”条件判断

  • AND(判断多个条件是否同时成立)

​ 语法规则：AND(logical1, [logical2], …)

  • OR(判断多个条件是否至少有一个条件成立)

​ 语法：OR(logical1,logical2,…)

  • TRUE(返回逻辑值TRUE)
  • FALSE(返回逻辑值FALSE)
  • NOT(对逻辑值求反)

1.2 IF函数(根据条件判断返回指定的值)

2. 数学与三角函数

2.1 求和及按条件求和运算

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

​ 语法：SUM(number1,[number2],…)

  • SUMIF（按照指定条件求和）

    • 语法：SUMIF(range，criteria，sum_range)

    • 第一个参数：Range为条件区域，用于条件判断的单元格区域。

    • 第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。

    • 第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。当省略第三个参数时，则条件区域就是实际求和区域。

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

    • 语法：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

    • criteria_range1为计算关联条件的第一个区域。

    • criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range1参数中的哪些单元格求和。例如，条件可以表示为32、“＞32”、B4、“苹果”、或"32"。

    • criteria_range2为计算关联条件的第二个区域。

    • criteria2为条件2。和 3 均成对出现。最多允许127个区域、条件对，即参数总数不超255个。

    • sum_range 是需要求和的实际单元格。包括数字或包含数字的名称、区域或单元格引用。忽略空白值和文本值。

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

​ 语法：SUMPRODUCT(array1, [array2], [array3], …)

2.2 数据的舍入

  • ROUND（对数据进行四舍五入）
  • INT（将数字向下舍入到最接近的整数）
  • TRUNC（不考虑四舍五入对数字进行截断）
  • ROUNDUP（远离零值向上舍入数值）
  • ROUNDDOWN（靠近零值向下舍入数值）
  • CEILING.PRECISE（向上舍入到最接近指定数字的某个值的倍数值）
  • FLOOR.PRECISE（向下舍入到最接近指定数字的某个值的倍数值）
  • MROUND（舍入到最接近指定数字的某个值的倍数值）
  • EVEN（将数字向上舍入到最接近的偶数）
  • ODD（将数字向上舍入到最接近的奇数）
  • QUOTIENT（返回商的整数部分）

2.3 其他数据学运算公式

  • ABS（求绝对值）
  • SUMSQ（计算所有参数的平方和）
  • SUMXMY2（求两个数组中对应数值之差的平方和）
  • SUMX2MY2（求两个数组中对应数值的平方差之和）
  • PRODUCT（求指定的多个数值的乘积）
  • MOD（取余数）
  • GCD（求两个或多个整数的最大公约数）
  • LCM（计算两个或多个整数的最小公倍数）
  • SQRT（数据的算数平方根）
  • SQRTPI（计算指定正数值与π的乘积的平方根值）
  • SUMTOTAL（返回列表或数据库中的分类汇总）

2.4 阶乘、随机与矩阵运算

  • FACT（计算指定正数值的阶乘）
  • FACTDOUBLE（返回数字的双倍阶乘）
  • MULTINOMIAL（返回参数和的阶乘与各参数阶乘乘积的比值）
  • RAND（返回大于等于0小于1的随机数）
  • RANDBETWEEN（返回指定数值间的随机数）
  • MDETERM（返回一个数组的矩阵行列式的值）
  • MINVERSE（返回数组矩阵的逆矩阵）
  • NMULT（返回两个数组的矩阵乘积）

2.5 指数、对数与幂运算

  • EXP（返回e为底数指定指数的幂值）
  • LN（计算一个数的自然对数）
  • LOG（计算指定底数的对数值）
  • LOG10（10为底的对数值）
  • POWER（返回任意指定幂值）

2.6 三角函数计算

  • RADIANS（将角度转换为弧度）
  • DEGREES（将弧度转换为角度）
  • SIN（正弦）
  • COS（余弦）
  • TAN（正切）
  • ASIN（反正弦）
  • ACOS（反余弦）
  • ATAN（反正切）
  • ATAN2（返回直角坐标系中给定X，Y的反正切）
  • SINH（双曲正弦）
  • COSH（双曲余弦）
  • TANH（双曲正切）
  • ASINH（反双曲正弦）
  • ACOSH（反双曲余弦）
  • ATANH（反双曲正切）
  • PI（π）

3. 统计函数

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

  • AVERAGE（算数平均值）
  • AVERAGEA（平均值）
  • AVERAGEIF（满足条件的平均值）
  • AVERAGEIFS（返回满足多重条件的平均值）
  • GEOMEAN（几何平均值）
  • HARMEAN（数据集的调和平均值）
  • TRIMMEAN（截头尾返回数据集的平均值）

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

  • COUNT（统计含有数字的单元格个数）
  • COUNTA（统计包括文本和逻辑值的单元格数目）
  • COUNTIF（统计满足条件的单元格数目）
  • COUNTIFS（统计同时满足多个条件的单元格数目）
  • COUNTBLANK（统计空白单元格数目）

3.3 最大最小值统计

  • MAX（返回数据集的最大值）
  • MIN（返回数据集的最小值）
  • MAXA（返回参数列表的最大值）
  • MINA（返回参数列表的最小值）
  • LARGE（返回表格某数据集的某个最大值）
  • SMALL（返回某数据集的某个最小值）

3.4 排位统计

  • MEDIAN（返回中位数）
  • RANK（返回一列数字的数字排位）
  • RANK.EQ（返回数组的最高排位）
  • RANK.AVG（返回数字列表中的排位）
  • QUARTILE.INC（返回四分卫数）
  • PRECENTILE.INC（返回第k个百分点值）
  • PERCENTRANK.INC（返回百分比排位）

3.5 方差、协方差与偏差

  • VAR.S（计算基于样本的方差）
  • VAR.A（计算基于样本的方差）
  • VAR.P（计算基于样本总体的方差）
  • VARPA（计算基于样本总体的方差）
  • STDEV.S（计算基于样本估算标准偏差）
  • STDEVA（计算基于给定样本的标准偏差）
  • STDEV.P（计算样本总体的标准偏差）
  • STDEVPA（计算样本总体的标准偏差）
  • COVARIANCE.S（返回样本协方差）
  • COVARIANCE.P（返回总体协方差）
  • DEVSQ（返回平均值偏差的平方和）
  • AVEDEV（计算数值的平均绝对偏差）

3.6 数据预测

  • LINEST（对已知数据进行最佳直线拟合）
  • TREND（构造线性回归直线方程）
  • LOGEST（回归拟合曲线返回该曲线的数值）
  • GROWTH（对给定的数据预测指数增长值）
  • FORECAST（根据已有的数值计算或预测未来值）
  • SLOPE（求一元线性回归的斜率）
  • INTERCEPT（求一元线性回归的截距）
  • CORREL（求一元线性回归的相关系数）
  • STEYX（返回预测值时产生的标准误差）

3.7 假设检验

  • Z.TEST（返回z检验的单尾P值）
  • T.TEST（返回t检验的双尾P值）
  • F.TEST（返回f检验的结果）

3.8 概率分布函数

3.8.1 二项式分布概率

  • BINOM.DIST（返回一元二项式分布的概率）
  • BINOM.INV（返回使累积二项式分布大于等于临界值的最小值）
  • BINOM.DIST.RANGE（返回试验结果的概率）
  • NEGBINOM.DIST（返回负二项式分布）

3.8.2 正态分布概率

  • NORM.DIST（返回指定平均值和标准偏差的正态函数）
  • NORM.INV（返回正态累积分布的反函数）
  • NORM.S.DIST（返回标准正态分布的的累积函数）
  • NORM.S.INV（返回标准正态分布反函数值）
  • LOGNORM.DIST（返回x的对数累积分布函数）
  • LOGNORM.INV（返回x的对数累积分布函数的反函数值）

3.8.3 $\chi^2$分布概率

  • CHISQ.DIST（返回$\chi^2$分布）
  • CHISQ.INV（返回$\chi^2$分布的左边的反函数）
  • CHISQ.DIST.RT（返回$\chi^2$分布的右边概率）
  • CHISQ.INV.RT（返回$\chi^2$分布的右边概率的反函数）

3.8.4 t分布概率

  • T.DIST（左边t分布）
  • T.DIST.RT（右边t分布）
  • T.DIST.2T（双边t分布）
  • T.INV（左边t分布的反函数）
  • T.INV.2T（双边t分布的反函数）

3.8.5 F概率分布

  • F.DIST（左边F分布）
  • F.DIST.RT（右边F分布）
  • F.INV（左边F分布的反函数）
  • F.INV.RT（右边F分布的反函数）

3.8.6 Beta分布概率

  • BETA.DIST（Beta分布）
  • BETA.INV（返回Beta累积概率密度函数的反函数值）

3.9 其他函数

  • PERMUT（返回排列数）
  • PERMUTATIONA（允许重复的情况下返回排列数）
  • MODE.SNGL（返回数组中的众数）
  • MODE.MULT（返回数据集中频率最高的数值）
  • FREQUENCY（频率分布统计）
  • PROB（返回数值落在指定区间内的概率）
  • HYPGEOM.DIST（返回超几何分布）
  • KURT（返回数据集的峰值）
  • SKEW（返回分布的偏斜度）
  • CONFIDENCE.T（t分布返回总体平均值的置信区间）
  • CONFIDENCE.NORM（正态分布返回总体平均值的置信区间）
  • PEARSON（返回Pearson乘积矩相关系数）
  • RSQ（返回皮尔生乘积矩相关系数的平方）
  • GAMMA.DIST（返回伽玛分布函数的函数值）
  • GAMMA.INV（返回伽玛累积分布函数的反函数值）
  • GAMMALN（返回伽玛函数的自然对数）
  • GAMMALN.PRECISE（返回伽玛函数的自然对数）

4. 文本函数

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

  • CONCATENATE（合并两个或多个文本字符串）
  • LEN（返回文本字符串的字符数）
  • LENB（返回文本字符串的字节数）
  • EXACT（比较两个文本字符串是否完全相同）
  • REPT（按照给定的次数重复文本）
  • TRIM（删除文本中的多余空格）
  • CLEAN（删除文本中不能打印的字符）

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

  • FIND（查找指定字符在字符串中的位置）
  • FINDB（查找指定字符在字符串中的位置（按字节算））
  • SEARCH（查找字符串的起始位置）
  • SEARCHB（查找字符串的起始位置（按字节算））

4.3 提取文本

  • LEFT（按指定字符数从最左侧提取字符）
  • LEFTB（按指定字节数从最左侧提取字符）
  • RIGHT（按指定字符数从最右侧提取字符）
  • RIGHTB（按指定字节数从最右侧提取字符）
  • MID（从任意位置提取指定数量的字符）
  • MIDB（从任意位置提取指定数量的字符）

4.4 文本新旧替换

  • REPLACE（用指定的字符和字符数替换文本字符串中的部分文本）
  • REPLACEB（用指定的字符和字节数替换文本字符串中的部分文本）
  • SUBSTITUTE（替换旧文本）

4.5 文本格式转换

  • TEXT（设置数字格式并将其转换为文本）
  • DOLLAR（四舍五入数值，并添加千分位符号或$符号）
  • RMB（四舍五入数值，并添加千分位符号或￥符号）
  • FIXED（将数字显示为千分位格式并转换为文本）
  • UPPER（将文本转换为大写形式）
  • LOWER（将文本转换为小写形式）
  • PROPER（将文本字符串的首字母转换成大写）
  • VALUE（将文本型数字转换成数值）
  • ASC（将全角字符转换为半角字符）
  • WIDECHAR（将半角字符转换为全角）
  • CODE（返回文本字符串中第一个字符的数字代码）
  • CHAR（返回对应于数字代码的字符）
  • BAHTTEXT（将数字转换为泰语文本）
  • T（判断是否为文本）

5. 日期与时间函数

5.1 返回当前日期

  • NOW（返回当前日期与时间）
  • TODAY（返回当前日期与时间）

5.2 构建与提取日期

  • DATE（构建标准日期）
  • YEAR（返回日期对应的年份）
  • MONTH（返回日期对应的月份）
  • DAY（返回日期对应的天数）
  • WEEKDAY（返回指定日期对应的星期数）
  • WEEKNUM（返回日期对应一年中的第几周）
  • EOMONTH（返回自定月份前（后）几个月最后一天的序列号）

5.3 日期计算

  • DATEDIF（用指定的单位计算起始日和结束日之间的天数）
  • DAYS365（按照一年360天的算法计算两日期间相差的天数）
  • YEARFRAC（从开始日到结束日所经历的天数占全年天数的比例）
  • EDATE（计算间隔指定月份数后的日期）

5.4 关于工作日的计算

  • WORKDAY（获取间隔若干工作日后的日期）
  • WORKDAY.INTL
  • NETWORKDAYS（计算两个日期间的工作日）
  • NETWORKDAYS.INTL

5.5 时间函数

  • TIME（构建标准时间格式）
  • HOUR（返回小时）
  • MINUTE（返回分钟）
  • SECOND（返回秒）

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

  • DATEVALUE（日期字符串转可计算的序列号）
  • TIMEVALUE（将时间转换为对应的小数值）

6. 查找与引用函数

6.1 ROW与COLUMN函数

  • ROW（返回引用的行号）
  • ROWS（返回引用中的行数）
  • COLUMN（返回引用的列标）
  • COLUMNS（返回引用中包含的列数）

6.2 LOOK类函数

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

    • 语法格式

      VLOOKUP(lookup_value, table_array,col_index_num, range_lookup)

    • 语法释义

      VLOOKUP(查找值,查找范围,返回值所在列数,精确OR模糊查找)

    • 第1参数lookup_value是要在单元格区域或数组的第一列中查找的值。如果查询区域首列中包含多个符合条件的查找值，则VLOOKUP函数只能返回第一个查找值对应的结果。如果没有符合条件的查找值，将返回错误值 #N/A。

      第2参数table_array是需要查询的单元格区域或数组，该参数的首列应包含第1参数。

      第3参数col_index_num用于指定返回查询区域中第几列的值，该参数如果超出待查询区域的总列数，VLOOKUP函数将返回错误值#REF!，如果小于1则返回错误值#VALUE!。

      第4参数rangelookup为可选参数，用于决定函数的查找方式。如果为0或FALSE，则为精确匹配方式，而且支持无序查找；如果为TRUE或省略参数值，则以所有小于查询值的最大值进行匹配，同时要求查询区域的首列按照升序排序。

      VLOOKUP函数各参数性质、参数说明和参数的设置原则如下表1所示：

      参数 性质 说明 参数的设置原则
      lookup_value 必需 表示需要在数组第一列中查找的值 可以是数值、引用或文本字符串
      table_array 必需 表示指定的查找范围 可以使用对区域或区域名称的引用
      col_index_num 必需 表示待返回的匹配值的序列号 指定为1时，返回数据表第一列中的数值，指定为2时，返回第二列中的数值，依次类推
      range_lookup 可选 表示指定在查找时是要精确匹配，还是大致匹配 FALSE表示精确匹配，TRUE或忽略表示大致匹配

  • XLOOKUP（根据指定的查找值在给定的数据范围或数组中搜索，并返回与该查找值相对应的结果）

    • 语法格式
      XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found], [match_mode], [search_mode]

    • 语法释义
      XLOOKUP(查找值, 查找数组, 返回数组, [未找到时的返回值], [匹配模式], [搜索模式])

    • 第一参数lookup_value是必需参数，指定需要查询的值；
      第二参数lookup_array是必需参数，指定查询的单元格区域或数组；
      第三参数return_array是必需参数，指定返回结果的单元格区域或数组；
      第四参数if_not_found是可选参数，指定找不到有效的匹配项时返回的值;如果找不到有效的匹配项，同时该参数缺失，XLOOKUP函数返回错误值“#N/A”；
      第五参数match_mode是可选参数,表示匹配模式，共有四个选项，各选项含义如表所示；

    • match_mode参数选项 含义
      0 默认值，表示完全匹配
      -1 当查无完全匹配项时，返回下一个较小项
      1 当查无完全匹配项时，返回下一个较大项
      2 表示支持通配符查询(默认不支持)

      第六参数search_mode是可选参数，表示搜索模式，共有四个选项，各选项含义如表2所示。

      search_mode参数选项 含义
      1 默认值，表示从第一项开始向下搜索；
      -1 表示从最后一项开始向上搜索；
      2 要求lookup_array按升序排序，执行二进制搜索；如果lookup_array未排序，将返回无效结果。
      -2 要求lookup_array按降序排序，执行二进制搜索；如果lookup_array未排序，将返回无效结果。

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

  • HLOOKUP（查找数组首行，并返回指定单元格值）

6.3 经典组合INDEX+MATCH

  • MATCH（查找并返回找到的值）

    • 语法：MATCH(lookup_value, lookup_array, [match_type])

    • lookup_value必需参数，需要在 lookup_array 中查找的值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

    • lookup_array必需参数，要搜索的单元格区域。

    • match_type可选参数，数字 -1、0 或 1。match_type 参数指定 Excel 如何在 lookup_array 中查找 lookup_value 的值。此参数的默认值为 1。

      • （1）Match_type：1 或省略，表示MATCH 函数会查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列，例如：…-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE。

        （2）Match_type：0，表示MATCH 函数会查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列。

        （3）Match_type：-1，表示MATCH 函数会查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, … 等等。

  • INDEX（从引用或数组中返回指定位置处的值）

    • 语法：INDEX(array, row_num, [column_num])

    • Array必需。单元格区域或数组常量。

    • 如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选参数。

    • 如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。

    • Row_num必需。选择数组中的某行，函数从该行返回数值。如果省略 row_num，则必须有 column_num。

    • Column_num可选。选择数组中的某列，函数从该列返回数值。如果省略 column_num，则必须有 row_num。

6.4 动态查找

  • OFFSET（通过给定的偏移量得到新的引用）
  • GETPIVOTDATA（返回存储在数据透视表中的数据）

6.5 引用函数

  • CHOOSE（从给定的参数中返回指定的值）
  • AREAS（返回引用中涉及的区域个数）
  • ADDRESS（建立文本类型的单元格地址）
  • INDIRECT（返回由文本字符串指定的引用）
  • TRANSPOSE（返回转置单元 格区域）
  • FORMULATEXT（返回指定引用处使用的公式）

7. 信息函数

7.1 获取信息函数

  • CELL（返回有关单元格格式、位置或内容的信息）
  • INFO（返回当前操作环境的信息）
  • TYPE（返回单元格内的数值类型）
  • ERROR.TYPE（返回与错误值对应的数字）

7.2 IS判断函数

  • ISBLACK（判断单元格是否为空）
  • ISNUMBER（检测给定值是否为数字）
  • ISTEXT（检测给定值是否为文本）
  • ISNONTEXT（检测给定值是不是文本）
  • ISEVEN（判断数字是否为偶数）
  • ISODD（判断数字是否为奇数）
  • ISLOGICAL（检测给定值是否为逻辑值）
  • ISERROR（检测给定值是否为任意错误值）
  • ISNA（检测给定值是否为#N/A错误值）
  • ISERR（检测给定值是否为#N/A以外的错误值）
  • ISREE（检测给定值是否为引用）
  • ISFORMULA（检测单元格内容是否为公式）

7.3 其他信息函数

  • NA（返回错误值#N/A）
  • N（ 将参数转换为数值并返回）
  • PHONETIC（ 连接文本 ）
  • SHEET（返回工作表编号）
  • SHEETS（返回工作表总数量）

8. 财务函数

8.1 投资计算

  • FV（返回某项投资的未来值）
  • FVSCHEDULE（计算投资在变动或可调利率下的未未值）
  • PV（返回投资的现值）
  • NPV（返回一项投资的净现值）
  • XNPV（返回一组不定期现全流的净现值）
  • NPER（返回某项投资的总期数）
  • EFFECT（计算实际年利率）
  • NOMINAL（计算名义年利率）

8.2 本金和利息计算

  • PMT（返回贷款的每期付款额）
  • PPMT（返回给定期间内本全偿还额）
  • IPMT（返回给定期限内的利息偿还额）
  • ISPMT（等额本全还款方式下的利息计算）
  • CUMIPMT（返回两个期间的累计利息）

8.3 偿还率函数

  • IRR（计算内部收益率）
  • MIRR（计并修正内部收益率）
  • XIRR（计并不定期现全流的内部收益率）
  • RATE（返回年全的 各期利率）

8.4 资产折旧计算

  • SLN（直线法计提折旧）
  • SYD（年数总和法计提折旧）
  • DB（固定余额递减法计算折旧值）
  • DDB（双倍余额递减法计算折旧值）
  • VDB（返回指定期间的折旧值）
  • AMORDEGRC（计算每个会计期间的折旧值）
  • AMORLINC（返回每个会计期间的折旧值）

9. 数据库函数

9.1 常规统计

  • DSUM（从数据库中按给定条件求和）
  • DAVERAGE（从数据库中按给定条件求平均值）
  • DCOUNT（从数据库中按给定条件统计记录条数）
  • DMAX（从数据库中按给定条件求最大值）
  • DMIN（从数据库 中按给定条件求最小）
  • DGET（从数据库中提取符合条件的单个值）
  • DPRODUCT（从数据库中返回满足指 定条件的数值的乘积）

9.2 方差、标准差计算

  • DSTDEV（按指定条件以样本估算标准偏差）
  • DSTDEVP（按指定条件计算总体标准偏差）
  • DVAR（按指定条件以样本估算总体方差）
  • DVARP（按指定条件计算总体方差）

10. 工程函数

10.1 进制编码转换函数

10.1.1 二进制编码转换为其他进制编码

  • BIN2OCT（二进制编码转换为八进制编码）
  • BIN2DEC（二进制编码转换为十 进制编码）
  • BIN2HEX（二进制编码转换为十六进制编码）

10.1.2 十进制编码转换为其他进制编码

  • DEC2BIN（十进制编码转换为二进制编码）
  • DEC2OCT（十进制编码转换为八进制编码）
  • DEC2HEX（十进制编码转换为十六进制编码）

10.1.3 八进制编码转换为其他进制编码

  • OCT2BIN（八进制编码转换力二进制编码）
  • OCT2DEC（八进制编码转换为十进制编码）
  • OCT2HEX（八进制编码转换为十六进制编码）

10.1.4 十六进制编码转换为其他进制编码

  • HEX2BIN（十六进制编码转换为二进制编码）
  • HEX2OCT（十六进制编码转换为八 进制编码）
  • HEX2DEC（十六进制编码转换为十进制编码）

10.2 复数计算公式

  • CONPLEX（将实系数及虚系数转换为复数）
  • IMABS（返回复数的模）
  • IMREAL（返回复数的实系数）
  • IMAGINARY（返回复数的虚系数）
  • IMCONJUGATE（返回复数的共辄复数）
  • IMSUM（计算两个或多个复数的和）
  • IMSUB（计算两个复数的差）
  • IMDIV（计算两个复数的商）
  • IMPRODUCT（计算两个复数的积）
  • IMEXP（计算复数的指数）
  • IMSQRT（计算复数的平方根）
  • IMARGUMENT（将复数转换为以弧度表示的角）
  • IMSIN（计算复数的正弦值）
  • IMSINH（计算复数的双曲正弦值）
  • IMCOS（计算复数的余弦值）
  • IMCOSH（计算复数的双曲余弦值）
  • IMCOT（计算复数的余切值）
  • IMCSC（ 计算复数的余割值）
  • IMCSCH（计算复数的双曲余割值）
  • IMSEC（计算复数的正割值）
  • IMSECH（计算复数的双曲正割值）
  • IMTAN（计算复数的正切值）
  • IMLN（计算复数的自然对数）
  • IMLOG10（返回复数以10为底的常用对数）
  • IMLOG2（返回复数以2为底的对数）
  • IMPOWER（计算复数的n次幕值）

10.3 Bessel函数

  • BESSELJ（ 返回Bessel函数值）
  • BESSELI（返回修正Bessel函数值）
  • BESSELY（返回Bessel函数值）
  • BESSELK

10.4 其他工程函数

  • DELTA（测试两个数值是否相等）
  • GESTEP（比较给定参数的大小）
  • ERF（返回误差函数在上下限之间的积分）
  • ERFC（返回从x到无穷大积分的互补ERF函数）
  • ERF.PRECISE（返回误差函数在0与指定下限间的积分）
  • ERFC.PRECISE（返回从x到无穷大积分的互补）
  • CONVERT（从一种度量系统转换到另一种度量系统）
  • BITAND（ 按位进行AND运算）
  • BITOR（按位进行OR运算）
  • BITXOR（返回两个数值的按位“异或“ 结果）
  • BITLSHIFT（左移指定位数并返回十进制编码）
  • BITRSHIFT（右移指定位数并返回十进制编码）

site logo

你好呀！

© 2024 大西瓜的小院