常用函数

sum-求和

  • SUM(number1,number2,…)
    • SUM(单个或多个单元格)
    • 可以用,逗号，对多个区域求和
  • SUM(列名)
    • 例如，SUM(A:A)

  • SUM(行名)

    • 例如，SUM(1:1)

      sumif-单条件求和

  • SUMIF(range,criteria,[sum_range])

  • SUMIF(条件判断所在的区域,条件,[用来求和的数值区域])

    sumifs-多条件求和

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

  • SUMIFS(用来求和的数值区域,条件1判断所在的区域1,条件1,条件2判断所在的区域2,条件2…)
  • 如果条件是>=10
    • SUMIFS(用来求和的数值区域,条件1判断所在的区域1,”>=”&10)

  • 注意事项

    • 像”美团”这样的字符串是文本格式，需要加英文双引号
    • 条件参数直接引用单元格或者使用函数则不需要英文双引号
    • 大于等于等符号也需要添加英文双引号””，并使用&才能与后面的条件值相连

      日期函数

  • YEAR(serial_number)

    • YEAR(日期)
  • MONTH(serial_number)
    • MONTH(日期)
  • DAY(serial number)
    • DAY(日期)
  • DATE(year,month,day) 日期组合
    • DATE(代表年份的数值,代表月份的数值,代表日份的数值)
    • 比如：DATE(YEAR(B31),MONTH(B31)-1,DAY(B31))
      • 计算出B31，前一个月这一日的日期
  • 求得每月最后一天
    • =DATE(YEAR(B41),MONTH(B41)+1,1)-1

SUBTOTAL-求和

  • SUBTOTAL(function_num,ref1,[ref2]…)
  • SUBTOTAL(指定函数,选择区域1,[选择区域2],…)

  • 它可以根据源数据的筛选进行不同的运算

    IF函数

  • IF(logical_test, value_if_true, [value_if_false])

    • IF(逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值])
    • [value_if_false]:该参数选填，没有该参数时，返回值False

      VLOOKUP函数

  • VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

    • VLOOKUP(要查找的数据、要查找的位置和要返回的数据的区域、要返回的数据在区域中的列号(从1开始)、返回近似匹配或精确匹配-指示为1/TRUE 或0/FALSE)
    • 要查找的数据，必须位于后面数据区域中的第1列
    • 近似匹配
      • 达到自定义分箱的效果
      • 数字类型
        • 会去在条件中找小于等于该值的最大值

  • 小案例

    • =VLOOKUP(I96&”*”,F96:G103,2,TRUE)
      • 可以使用*通配符做条件，或者?占位符做匹配

        MATCH函数

  • MATCH(lookup_value, lookup_array, [match_type])

    • MATCH(查找项,查找区域,0)
    • 0：精确匹配
    • 功能：找到这一项位于这个区域中的第几个

      INDEX函数

  • INDEX(array, row_num, column_num)

    • INDEX(区域,行号,列号)
    • 功能：找到这个单元格的值
  • 如果行位置是0，会返回整列；列位置是0，会返回整行

实用技巧

固定函数中的行和列的引用

我们直接引用B15单元格，输入=B15，然后向右拖拽，会变为C15，向下拖拽会变为B16

  • =$B15
    • 现在，我们在列号B前加$，那么列就不会变了拖动句柄向右，列号不变，为$B15
    • 拖动句柄向下，行号会变，为$B16

  • =B$15

    • 现在，我们在行号15前加$，那么行就不会变了拖动句柄向下，行号不变，为B$15
    • 拖动句柄向右，列号会变，为C$15

      环比与同比

      同比=(本期数-同期数)/同期数=本期数/同期数-1 环比=(本期数-上期数)/上期数=本期数/上期数-1

  • 年

    • 2020年环比
    • = (2020年数据-2019年数据)/2019年数据
    • =2020年数据/2019年数据-2019年数据/2019年数据
    • =2020年数据/2019年数据-1
  • 月
    • 2020年7月环比 =2020年7月数据/2020年6月数据-1
    • 2020年7月同比 =2020年7月数据/2019年7月数据-1

  • 日

    • 2020年7月1日环比 =2020年7月1日数据/2020年6月30日数据-1
    • 2020年7月1日的月同比 =2020年7月1日数据/2020年6月1日数据-1
    • 2020年7月1日的周同比 =2020年7月1日数据/2020年6月24日数据-1

      自动根据列名查找数据

  • match、index—起使用：自动根据列名查找数据

  • index(数据区域,match(行查找项, index数据区域的相对区域,0),match(列查找项, indexB数据区域的相对区域,0))

    自动根据列名的数据和条件进行求和

  • sumifs、match、index—起使用：自动根据列名的数据和条件进行求和

  • =sumifs(index(数据区域,match(行查找项,index数据区域的相对区域,0),match(列查找项,indexB数据区域的相对区域,0)),条件1判断所在的区域1,条件1,条件2判断所在的区域2,条件2…)

    案例素材

    周报

    Excel练习.xlsx