Google Sheet 学习笔记

Google Sheet 公式：

sheet!$A:$B,'sheet new'!$A3:$D

countifs, sumifs

• 非空判断=sumifs(C1:C6,A1:A6,"<>") or =sumifs(C1:C6,A1:A6,"<>"&"")

vookup

• 进阶：自定义数据范围(组合多列为1列)
  =VLOOKUP(A2&E2,{ARRAYFORMULA(sheet1!A:A&sheet1!B:B),sheet1!D:D},2,0)
• 进阶：返回多列
  =ARRAYFORMULA(VLOOKUP($E2,rate!$A:$E,{2,3,4},0))

字符串处理

• 字符拼接：=concatenate("(",text(C11/sum($C11,$E11,$G11,$I11),"0.00%"),")")
• 分隔符+字符拼接textjoin(分隔符, 是否忽略空白, 文本1, 文本2)

  实例——TEXTJOIN(“|”,1,F2:F3) # 结果 文本1｜文本2｜…

query(data, sql,[headers])

data::查询数据源，sql::查询语句，[headers]::标题数，数据上方的标题行的数目，省略则-1，估计。
参考link

• 聚合+重命名 =query(A2:C6,"select A, avg(B) group by A label avg(B) 'avg_order'")
• pivot D 类似透视表里的选列(The pivot clause is used to transform distinct values in columns into new columns.)

=query(A1:D6,"
select A, avg(B), sum(C) 
where A = '' / where A is null / 
group by A 
pivot D 
order by A desc 
label avg(B) 'avg_order', sum(C) 'sum_gmv' ",1)

pivot D : d1、d2计算B; d1、d2计算C
透视表D：d1计算B,C; d2计算B,C

• 时间维度的聚合 =query(A1:C6,"select year(A), avg(B) group by year(A)", 1)
  A必须是时间格式

• offset 2是剔除输出结果中的first 2 rows

• 先对两列进行计算，再求平均
  =QUERY({ARRAYFORMULA(B1:B5-C1:C5),J1:J5,D1:D5},"select Col2,avg(Col1) where Col3 is not null group by Col2 ",0)

• 多个范围拼接成为 数据源【可避免数据源增减列导致error】

• =query({A:F;H:M;O:T;V:AA},“select * where Col1>0”,1)

• =query({A:A;D:D;E:E},“select * where Col1>0”,1)

• query引用单元格的值

• =query(A3:C8,"select * where A <> '"&A7&"'")

• 高阶 query + matches/ like/ contains/ starts with/ ends with

  • matches
    

importrange(link, range)

实例：=importrange(“docs.google.com…”,“sheet!A2:B5”)
注：sheet name修改不会自动变化；sheet name中间可以带空格

• query+importrange：=query(importrange("url",sheet), "select * ")

时间函数

• today()

常用函数（不知归何类）

• unique(A:A)