Excel公式

最近想学习一下Excel公式，发现Excel有自带的公式教程，过了一遍，记了一些笔记。

Excel公式教程.png

1.基础知识

• 基本的运算符号：加减乘除幂（+-*/^）
• 行和列：Excel行用数字，列用字母，共有一百多万行和16000多列
• 简单公式示例：

=A1+B1
=10+20
=SUM(A1:A10)

• 函数公式形式
  • 以=开头，随后是函数名称，后用括号括起来参数
  • 公式格式：=函数名(参数)
• 按Enter可以确认公式
• 按F2进入编辑模式，可以在单元格中看到公式并进行编辑
• 教程提供了两种函数列表
  • Excel函数按类别：https://support.microsoft.com/zh-cn/office/excel-%e5%87%bd%e6%95%b0%ef%bc%88%e6%8c%89%e7%b1%bb%e5%88%ab%e5%88%97%e5%87%ba%ef%bc%89-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb?ui=zh-cn&rs=zh-cn&ad=cn
  • Excel函数按字母顺序：https://support.microsoft.com/zh-cn/office/excel-%e5%87%bd%e6%95%b0%ef%bc%88%e6%8c%89%e5%ad%97%e6%af%8d%e9%a1%ba%e5%ba%8f%ef%bc%89-b3944572-255d-4efb-bb96-c6d90033e188?ui=zh-cn&rs=zh-cn&ad=cn

2. 函数简介

• 以 SUM函数为例，有以下几种方法可以使用：

• 输入=SUM(D3:D6)
• 输入=SUM(，用鼠标选择区域并按Enter
• 公式——自动求和——求和
• 选中求和结果单元格—— Alt = ——Enter

• 找到函数之后，按Tab，Excel将自动完成函数名并输入左括号 = SUM(

3. AVERAGE函数

• 可获取某区域单元格中数字的平均值
• 计算方法：

  • 自动求和——平均值
  • =AVERAGE(G3:G6)

• 延伸：
  • MEDIAN 函数：提供数据集的中间值（中位数）
  • MODE 函数 ：提供最常出现的值（众数）

4. MIN和MAX

• 最小值和最大值
• 计算方法：

  • 自动求和——最小值 / 最大值
  • =MIN(G3:G6) / =MAX(G3:G6)

• 可以对多个区域或值使用 MIN 或 MAX，用逗号连接

5. 日期和时间

• 日期函数

  • TODAY函数：
    • 提供当前日期，实时可变的
    • 公式：=TODAY( )
    • 返回形式：××年×月×日
  • 日期相减：
    • 距离某个日期还剩多少天
    • 两个具体日期 YYYY年M月D日 相减
    • 示例：=D7-D6 （D6和D7中有具体日期）
  • 日期相加：
    • 适用情况：帐单截止日期等
    • 对日期加上若干天
    • 示例：=D6+D10（D6代表当前日期，D10代表随机天数）

• 时间函数

  • NOW函数：
    • 提供当前时间，在每次Excel计算时进行更新
    • 公式： =NOW( )
    • 返回形式：时:分

• 扩展：

  • 改时间/日期格式：Ctrl+1——数字——日期/时间
  • 改小数位数：Ctrl+1——数字——数值——小数位数
  • Excel 根据从 1900 年 1 月 1 日开始的天数来记录日期和时间
  • 时间以分钟为单位记录在一天的小数部分里。如 2017/01/01 中午 12:30 实际存储为 42736.5208，计算的时候，如果想要显示小时数，则要*24。
  • 用键盘快捷键输入不会变化日期和时间：
    • 日期——Ctrl+；
    • 时间——Ctrl+Shift+：

6. 联接文本和数字

• 联接位于不同单元格的文本
• 使用与号 (&) 执行该操作
• 使用方式：
  • 文本和文本
    • =D3&C3
    • 用逗号连接=D3&", "&C3
    • 用空格连接=D3&" "&C3
  • 文本和数字
    • 日期和时间相连时，需要显式指示 Excel 如何设置公式数字部分的格式
    • 使用 TEXT 函数和格式代码
    • =C28 &" "& TEXT(D28,"YYYY年M月D日")
    • =C29&" "&TEXT(D29,"H:MM")
    • 如果不知道使用什么格式代码，可以使用“Ctrl+1”——“数字”，以所需方式设置任何单元格的格式,然后选择“自定义”选项。可以将显示的格式代码复制回公式中。

7. IF语句

• 在条件之间进行逻辑比较
• 通常指示某条件为 true 时执行某项操作，否则执行其他操作
• 形式
  • =IF(判断条件，为真的结果，为假的结果）
  • TRUE和 FALSE无需用引号括起来，并且 Excel 会自动将其设为大写，数字也无需用引号括起来。如 =IF(C9="苹果",TRUE,FALSE)
  • 常规文本需用引号括起来。如 =IF(C9="苹果","是","否")
• IF和其他函数配合使用
  • IF 语句可在满足特定条件时强制执行其他计算

8. VLOOKUP函数

• VLOOKUP，可查找左侧列中的值，如果找到匹配项，则会在右侧的另一列中返回信息

  
  vlookup.png

• VLOOKUP 找不到所需内容，返回错误 (#N/A) 的解决方法：

  • 如果你知道查找值存在，但查找单元格为空，你希望隐藏错误，可以使用 IF 语句。
    • =IF(C43=" "," ",VLOOKUP(C43,C37:D41,2,FALSE))
  • 如果不确定查找值是否存在，但仍想抑制 #N/A 错误，可以在单元格中使用名为 IFERROR 的错误处理函数：
    • =IFERROR(VLOOKUP(F43,F37:G41,2,FALSE)," ")
    • IFERROR 表示，如果 VLOOKUP 返回有效结果，则显示该结果，否则不显示任何内容 (" ")
    • 除了 (" ")，还可以使用数字（0、1、2 等）或文本，如“公式不正确”。

9. 条件函数

• 可以根据给定条件或指定的条件对某区域内进行求和、求平均值、计数或获取最小值或最大值

  
  sumif.png

• SUMIFS 与 SUMIF 相同，但它允许你使用多个条件

  
  sumifs.png

• 使用 COUNTIF 和 COUNTIFS，可以根据指定的条件对某区域的值进行计数

  • 这两个函数只有条件区域和条件，不对区域进行求值

    
    coutif.png

  • COUNTIFS 允许使用多个条件

    
    countifs.png

• 具有值参数的SUMIF：

  • 对符合条件的区域值进行累加操作

    
    sumif值.png

  • 不等于50：<>50