前言：本文为本人学习B站向天歌出品系列视频——《Excel大神上分攻略》时的学习笔记，希望通过记录的方式强制自己将内容学会并为了方便将来遗忘而创作

函数

  • 输入函数名的前几个字母后若要选择Excel联想的函数需使用小键盘的箭头，然后使用TAB键来选择联想的函数

相对引用与绝对引用

使用位于列前则将列进行绝对引用，行前将行绝对引用

例：$A$2表示只使用A2这个单元格，$A2表示使用A列，但对应的行则使用相对的行，A$2表示使用第二行，对应的行根据相对关系使用

PS：按下F4可以切换引用的模式

求和函数

快速求和：Alt + = 会直接智能地将求和结果放到选中区域或智能将列区域求和放到列的下方

定位求和：Ctrl + G定位空值后进行求和

合并单元格的求和：将合计栏选中，在第一格中输入sum(要求和的目标栏第一格:最后一格）-sum(合计栏单元格第二格：最后一格)

多表求和：

  1. 输入SUM后选择要求和的目标表格的目标单元格，选择的每个单元格使用英文逗号隔开

  1. 或者输入SUM后先按住Shift选中要求和的表格，再选择要求和的目标单元格

  2. 合并计算：数据-合并计算-引用位置-选择好每个表格的数据的位置后点击添加，记得将标签的首行和最左列选中然后进行合并计算

条件求和：

  1. SUMIF函数（条件区域，条件，求和区域）

  • 条件注意要加上引号，区域绝对引用

    • 可用通配符查找：”*“
    • 条件的目标是中文则需要用英文引号引住
    • 更好用的方法是将条件(即要求的目标)做成新单元格后当做条件来求和

  1. SUMIFS函数

    （求和区域，条件区域1，条件1，条件区域2，条件2，……）

  • 这个函数能使用更多的条件，上一个函数只能使用一个条件

  1. SUMPRODUCT函数

    ①求乘积（区域1，区域2，区域3，……）

  • 每个区域大小一致

    ②多条件求和

    （条件1*条件2*……*条件n*求和区域）

  • 区域不能采取整列引用，比如A：A

  • 公式拖拽填充时，要考虑绝对引用

SUBTOTAL函数
包含隐藏值 不包含隐藏值 作用
1 101 平均值
2 102 非空数值计算
3 103 非空单元格计数
4 104 最大值
5 105 最小值
6 106 乘积
7 107 标准偏差
8 108 总体标准偏差
9 109 求和
10 110 方差
11 111 总体方差

平均值

  1. 【平均值】：AVERAGE（数字1，数字2，……，数字n）或者

AVERAGE（数字区域）

  1. 【条件平均值】：AVERAGEIF（条件区域，条件，求平均值区域）
  • 注意绝对引用

最值

  1. 【最大值】：MAX（数字1，数字2，……，数字n）或者
    MAX（数字区域）
  2. 【最小值】：MIN，参数同上
    MAX常结合数组来使用
    比如最新的个税公式：
    =ROUND(MAX((A4-5000)*{0.03;0.1;0.2;0.25;0.3;0.35;0.45}-{0;210;1410;2660;4410;7160;15160},0),2)

四舍五入、绝对值函数

  1. 【四舍五入】：ROUND（数字，保留小数位数）

    1表示保留小数点后1位，0表示保留整数，-1表示保留到十位，以此类推

  2. 【直接舍去】：ROUNDDOWN（数字，保留小数位数）
  3. 【向上取整】：ROUNDUP（数字，保留小数位数）
  4. 【只保留整数】：INT（数字）
  5. 【取绝对值】：ABS（数字）

随机数

  1. 【随机整数】：=RANDBETWEEN（0,100）
  2. 【0-1随机小数】：=RAND()
  3. 【n1~n2随机小数】：公式 =n1+RAND()(n2-n1)
    例如”3~50随机小数“
    =3+RAND()    (50-3)
    按F9或者有编辑操作该函数会时时刷新，选择性粘贴为数值可固定数值

    计数函数

  4. 【统计数字个数】：COUNT（区域1，区域2，……）
  5. 【非空单元格数量】：COUNTA（区域1，区域2，……）
  6. 【空单元格数量】：COUNTBLANK（区域1，区域2，……）
  7. 【条件计数】COUNTIF（计数区域，计数条件）

    求余数：

    MOD（被除数，除数）
    返回结果为余数，例如：
    =MOD（28,5），结果为3
    =MOD（3,5），结果仍为3
    判断奇偶：=IF(MOD(A84,2)=0,”偶数”,”奇数”)