excel公式总结（一）

包含了：运算符（文本、算数、比较、单元格等）、数组公式、常用函数一包含（IF函数、vlookup函数、筛选求和函数=subtotal、条件函数、舍入运算函数=round函数、随机选取函数=rand、数学函数、逻辑判断函数、排错函数、转换函数、截取函数）注意事项类等。方便大家快速找寻函数，帮助大家进行函数入门。

excel公式介绍（一）

• 运算符
  • 算术运算符
    • + 加 - 减 * 乘 / 除 % 百分比 ^ 乘幂
  • 文本连字符
    • & 连接
  • 比较运算符
    • = 等于 > 大于 < 小于 >= 大于或等于 <= 小于或等于 <> 不等于
  • 单元格引用
    • , 联合运算符 多个引用合并成一个引用 : 区域运算符 引用两个单元格之间全部区域
  • 运算优先级
    • 最高优先级 引用运算符里面的冒号（：）以及逗号（，）和空格运算符。 第二优先级 负号（-） 第三优先级 百分号（%） 第四优先级 求幂（^） 第五优先级 乘法（*）和除法（/） 第六优先级 加法（+）和减法（-） 第七优先级 连接运算符（&） 第八优先级 等于（=），大于（>），小于（<），大于等于（>=），小于等于（<=），不等于（<>）
  • ps：表格的下边缘 为 1048576 因为2的20次方是1048576
• 数组公式
  • 定义：原来是一个一个运算、现在是一组一组运算，就叫做数组。
  • 显示：按住ctrl+shift 再点击回车，出现中括号，表示确定为数组， 就会呈现数组的结果
  • 记忆：三键结尾
    • 按住ctrl+shift 再点击回车，出现中括号，表示确定为数组， 就会呈现数组的结果
• IF函数
  • IF函数
  • IF函数+NOW函数
    • 场景
      • 用NOW函数和IF函数和循环引用进行实时记录数据值和数据产生时间
      • 即：录入数据并在其后一列记录数据记录时间
    • 原理
      • NOW函数可以获取时间
      • IF函数可以进行判断条件
    • 示例
      • 如果A2不是空 如果（B2）本来就有时间，那我就等于我自己本身 如果（B2）自己没有时间，就等于当前时间
• vlookup函数
  • vlookup函数
  • vlookup函数+混合引用
    • 可以引入column函数来进行自动变化，我要索引的列
    • 举例
      • 公式为：
        • 子主题
• 条件函数=sum/countif
  • 条件求和
    • sumif函数（单条件求和）
      • sumif函数（区域，条件，【求和区域】）
      • 解释
        • 区域是指：正在该区域内进行搜索匹配字段
        • 条件是指：你所要求得目标字段那个单元格
        • 求和区域：就是你要算那个数字那一列
        • 最后的结果输出的是符合该条件下的总和
    • sumifs函数（多条件求和）
      • sumifs函数（求和区域，区域1，条件1，【区域2，条件2】.....）
      • ps：sumifs函数（多条件求和，逻辑上是既....又....的值）
      • 解释
        • 求和区域：就是你要算那个数字那一列
        • 区域1，在区域1中找条件1单元格内容
        • 区域2，在区域1中找条件2单元格内容
        • 条件和区域总是成对出现的
  • 条件计数
    • countif 函数
      • countif 函数用来统计该区域内，统计该数值出现了多少次
      • 场景
        • 利用公式制定输入规则/数据有效性
          • 小于一定数值时，才可输入。（小于等于才可输入）
          • 只能防止手动填写的，不能防止复制粘贴的
          • 数据有效性只会对空白单元输入时候有效，其他已输入单元格不会更改或者仅提示
    • countifs函数
      • countifs函数则是表示多个区域和条件计数（2个以上的区域计数的和）
  • 其他条件统计函数
    • 最大值条件函数
      • maxifs
    • 最小值条件函数
      • minfs
    • 平均数条件函数
      • averageifs
• 筛选求和函数=subtotal
  • subtotal函数
    • 异同
      • sum无论你是否使用筛选都对筛选无反应，总是求的是总集
      • subtotal中的sum函数会随着你筛选的数值条件不同而使求和的结果发生改变
    • 筛选求和
      • subtotal中的sum，即=subtotal（9，某列）1-11的函数：只会求去筛选之后的值
    • 筛选+隐藏求和
      • subtotal中的sum，即=subtotal（109，某列）101-111的函数：会求去筛选之后的值以及隐藏之后的值
    • 函数适用范围：sum>subtotal9>subtotal109
• 舍入运算函数=round函数
  • 四舍五入
    • round函数
    • 写法：=round（单元格，2/3/4）后面的数字代表保留几位小数
  • 直接进位
    • 对小数部分直接进位，向前补一位
    • roundup函数
    • 写法：=roundup（单元格，0/1/2）后面的数字代表保留几位小数，0代表直接保留整数
  • 直接舍去
    • 对小数部分直接进行舍去，不向前进行补位
    • 写法：=rounddown（单元格，0/1/2）后面的数字代表保留几位小数，0代表直接保留整数
  • round函数中，数字也可以用负数来表示，比如12345，=round（单元格，-2），就会变为12300，-2就可以表示为可以按整百取整
• 随机选取函数=rand
  • rand函数
    • 写法：=rand（），默认括号内不写数字，会在0-1之间随机产生数字，小数点后十五位的数字
  • randbetween函数
    • 写法：=randbetween（1-5）
    • 随机好随，但是难点在于防止重复
    • randbetween可以用于取某一范围的整数
• 数组公式
  • 数组：一组乘一组，一排乘以一排
  • 数组公式三键结尾
  • sumproduct函数
    • sumproduct就是数组版的sum
  • 举例
    • 先乘后加数据等
    • 多条件统计实例
      • • 第五列判断海鲜粉=海鲜粉
        • 第六列判断销售一部=销售一部
        • 第七列表示为且的关系（求交集）、两个true相乘为1，两个/一true一flase相乘为0。
        • 因此第八列用第七列乘单价
        • 第九列用sum处理即可解决
  • sumifs函数其实更快，要比sumproduct函数快17倍
• 数学函数
  • ABS绝对值
    • 绝对值（正负都为正值），处理的是数字
    • 写法：=ABS（单元格），然后下拉
  • MEDIAN中位值
    • 求某一区域的中位值
    • 若为偶数列：最中间那两个的平均值 ， 奇数列：最中间那个
    • 写法：=MEDIAN（那一列）
  • MODE函数求众数
    • 出现次数最多的，如果出现次数一样，就取第一个次数出现最多的
    • 写法：=MODE（那一列）
  • MOD函数求余数
    • 写法：=MOD（分母，分子）
    • 除以2还余1就叫做奇数，能除赶紧就叫做偶数
  • ROW函数（行号）/column函数（列号）
    • 写法：=ROW（单元格）就可以知道行数
    • 写法：=column（单元格）就可以知道列数
• 逻辑判断函数
  • IF函数（条件函数）
    • 如果，是...怎么样....，否则不是....怎么样.....
  • IF嵌套（嵌套条件函数）
    • N情况，就用N-1个IF
      • 举例：如果是省就显示XX省，市就显示XX市。可以用if多层嵌套
  • IF函数区间用法
    • 错误写法
    • 在表格中位于某个区间，永远不能用：20>A2>1这样的写法来表示某个单元格位于某个区间，表示大于某值，小于某值。
    • 原因
      • A2会和20做一个判断，得到ture和false在和1进行判断，结果无意义
    • 正确写法
    • 像剥洋葱一样，一层一层往下去拨。写的时候要理清楚逻辑结构
  • IF函数与逻辑运算
    • AND函数
      • AND函数表示的且的关系，ABC...D等条件都满足
    • 举例：and+if函数
    • 举例：AND和OR函数
      • =if（or（and（B2=“男”，C2>=22），and（B2=“女”，A2>=20）），“是”，“否”）
      • 由内往外，由小往大
• 排错函数
  • IFERROR查错误函数
    • 出错函数范围太宽，有可能是函数写错了，也有可能是公式确实找不到信息。但都会改为你想要改的值。
    • 写法：IFERROR（错误列，错误之后显示的）
    • =IFERROR（A2，0）如果A2错误就显示为0
  • IFNA函数查错误函数
    • IFNA函数用于查询公式确实找不到信息情况，如果公式错误依旧会显示公式出错误。
    • 写法同IFERROR函数写法
  • IFNA函数和IFERROR区别
    • 范围不同，所有的错误都算ERROR，所以IFERROR会屏蔽所有错误，而IFNA函数专门用来屏蔽VLOOKUP找不到的错误
• 转换函数
  • IFS函数
    • IF函数的升级版，加S表示多情况下。
    • 在此函数中每一种都需要写清楚情况
    • =IFS（E6>=2000000，“A级”，E6>=1200000，“B级”，E6>=600000，“C级”，E6<600000，“D级”）
    • 解释为：
      • 若E6大于2百万，为A级别，
      • 否则，大于120万，为B级
      • 否则，大于60万，为C级
      • 否则，小于60万，为D级
  • SWITCH函数
    • 一 一对应关系
• 截取函数
  • 字节函数（文本函数带B）
    • 理解字节：
      • 一个中文字是两个字节，字母和数字是单字节
    • 如果是字节是文本，取不到时候就会留取空白，比如：空白的，取五个字节，那么输出结果为：空白 ，后面有“的”一半，就会用空格来表示
  • 左截取
    • 从左截取：=left（，）
    • 从左截取：=leftb（，）
  • 右截取
    • 从右截取：=right（，）
    • 从右截取：=rightb（，）
  • 中间截取
    • 从中间截取：=mid（A2，2，3）
    • 从中间截取：=midb（A2，2，3）
  • 字符、字节数量
    • 计算字符数量：=len（）
    • 计算字节数量：=lenb（）
    • 可以利用字节、字符数来进行分列文本和数字情况（等同于智能分列）
    • 场景
      • 分列汉字
      • 分列数字：先分列汉字，在倒退数字
• 注意事项类
  • 绝对引用和相对引用的区别
    • 绝对引用，即便选中整个区域，公式也无法进行复用 在使用相对引用时，公式或函数中的行或列会随着行号和列标的变化而自动变化。 绝对引用的特点就是在使用绝对引用时，公式或函数中的行或列是绝对不会发生变化的。
  • 混合引用
    • A1 相对引用，横向纵向都可以拉，拉取会变 $A$1 绝对引用，横向纵向都不可以拉，拉取不会变 $A1 混合引用，纵向可以拉，拉取会变，横向不可拉。 A$1 混合引用，横向可以拉，拉取会变，纵向不可拉。
    • 用引用公式来做9*9乘法表格