【办公自动化Excel】公式和函数的应用

1. 公式的基本知识

注意

• 输入公式时，必须以等号开头
• 公式中只能用英文小括号相对引用与绝对应用
• 参数间隔用逗号
• 错误类型：#NAME!函数名错误/字符串未添加引号；
  #VALUE变量引用错误；
  #VIO/0以零做分母

2. 单元格的引用

• 相对引用：复制到其它单元格时单元格自动调整
• 绝对引用：复制到其它单元格时单元格不变，用$表示，如$B$4
  相对引用变为绝对引用：选中要绝对引用的内容，按F4
• 混合引用：复制到其它单元格时单元格的名字自动调整

3. 函数

3.1文本函数

• 连接函数CONCATENATE(A1,A2)
• 比较函数EXACT(“M1”,”M2”)正TRUE，否FALSE。
• 查找函数：SEARCH(查找内容，范围，开始的位置)
  SEARCH(“ACT”,J12,1)返值为第一个字符出现的下标。
• 删除空格函数:TRIM()
• 字符串替换函数： REPLACE(指定字符串、替换开始位置、替换个数、新的字符串)
• 格式转换函数 数字转变为固定格式文本TEXT(12.23，”$0.00”)，返回为$12.23

3.2统计函数

• 统计数值函数：
  包含数值型个数COUNT(区域)；
  包含非空单元格COUNTA(区域) ；
  包含空白单元格的数量COUNTBLACK(区域)

3.3数值计算函数

• 条件统计函数COUNTIF(统计区域，逻辑表达式)
  COUNTIF（D3:D86, “迟到”）
• 多条件统计函数：COUNTIFS(统计区域1，逻辑表达式1,统计区域2，逻辑表达式2)
• 排位函数RANK(排位单元格，排位范围，排位方式0和省略降序，非零升序)
• 数组乘积函数
  对应数组相乘再相加SUMPRODUCT(A2:A4,C2:C4)；
• 取整函数：INT（）
• 四舍五入函数 ROUND（数值，保留的小数位）
• 取余函数MOD(被除数，除数)
• 条件求平均函数
  AVERAGEIF(条件区域，逻辑表达式平均值区域)
• 多条件求平均值函数
  AVERAGEIFS(平均值区域，条件区域1，逻辑表达式1，条件区域2，逻辑表达式2)

3.4时间函数

• 日期函数：TODAY()返回型YYYY-MM-DDb)
• 日期和时间函数：NOW()返回型YYYY-MM-DDHH:MM
• 年函数 YEAR()
• 小时函数 HOUR()
• 星期函数 WEEKDAY(日期，返回类型的数字)，1和省略表示1是星期天；2表示返回是从星期一开始

3.5查找和引用函数

• 列查找函数：VLOOKUP(查找数值，查找的范围，返回的列在所选范围的第几列)
• 行查找函数：HLOOKUP(查找数值，查找的范围，返回的行在所选范围的第几列)
• 向量型查找函数
  LOOKUP（查找的数据，需查找列包含一行或一列区域，查找的行或列）；
  数组型
  LOOKUP（查找的值数字、文本、逻辑值、单元格引用，数组）
• 引用函数：OFFSET(基点单元格，移动行，移动列，高，宽)

3.6逻辑函数

• 条件判断函数：IF()
  单元格为空不显示 IF(E65=0,””, D65/COUNTA（D3：D64）
• 逻辑与函数：AND()，同时真为真
• 错误处理函数：IFERROR(判断，错误提示)

3.7数据库函数

• 求和：
  DSUM（求数据库范围，“求和区域”，条件区域） 函数法名称必须与数据库里面的相同，否则为0， 在一行表示与，列表示或
• 平均值：DAVERAGE()
• 最大值：DMAX()
• 包含数字的单元格个数
• DCOUNT(列表数据库单元格区域，指定数据列，包含给定条件的区域)

4. 数组公式

• 数组公式的建立
  选择存放单元格，输入数组公式，Ctrl+Shift+Enter。
• 数组公式的修改
  单击编辑栏，修改数组公式，Ctrl+Shift+Enter。
• 数组公式的运算规则与应用
  相同类型数组计算,返回同样数组，不匹配报错
  一个数组与数进行计算时，每一个元素均与之进行计算
  M列与N行——M*N的数组行内元素用“，”分隔，行与行之间用“；”
  分隔数组公式的运行，编辑需要按Ctrl+Alt+Enter，只能在编辑栏修改或按F2。