Google Sheet 函数和工具使用技巧
Functions函数使用
数据抓取
systemctl stop nginx
systemctl start nginx
nginx -t
nginx -s reload
数据拆分
#1 s-api
systemctl restart php7.3-fpm
统计和去重复
#1 s-api
systemctl restart php7.3-fpm
数据多列合并 pdf简要教程
#1 多个字段合并成一个字段
=JOIN("_",B5,C5,IF(ISBLANK(D5),0,D5))
#2 将多列数据引入：{,}
{importedPayMethods!H4:H,importedPayMethods!B4:B}
{FILTER(importedPayMethods!H4:H,importedPayMethods!F4:F),FILTER(importedPayMethods!B4:B,importedPayMethods!F4:F)}
=JOIN("_",B5,C5,IF(ISBLANK(D5),0,D5))
#3 将多列数据合并成同一列：{;}
={FILTER(report_by_trade!$A$2:A,NOT(ISBLANK(report_by_trade!$A$2:A)));FILTER(report_by_trade!$A$2:A,NOT(ISBLANK(report_by_trade!$A$2:A)))}
#4 将多列数据合并成同一列 并去重：UNIQUE（{;}）
=UNIQUE({A3:A12;B3:B12})
#5 合并汇总
#5.1 水平合并 Horizontal column combination formulas
=ARRAYFORMULA(A3:A&" "&B3:B)
=ARRAYFORMULA(CONCAT (A3:A,B3:B))
#5.2 垂直合并 Vertical column combination formulas
={A3:A12;B3:B12}
=UNIQUE({A3:A12;B3:B12})
=FILTER({A3:A12;B3:B12}, LEN({A3:A12;B3:B12}))
数据过滤 FILTER
# 对表格中某一列进行过滤 过滤包括 不包含和包含两组条件
=FILTER('game-support-product-list'!A:J,NOT(COUNTIF(FILTER($D2:$D,NOT(ISBLANK($D2:$D))),'game-support-product-list'!J:J)),COUNTIF(FILTER($C2:$C,NOT(ISBLANK($C2:$C))),'game-support-product-list'!J:J))
# FILTER筛选不连续的多列数据
=FILTER({'tableA'!M:M,'tableA'!I:I,'tableA'!N:N,'tableA'!D:D,'tableA'!W:W},'tableA'!A:A=TRUE())
# 不在某一个范围中的值
=FILTER('V2'!A:A,NOT(ISBLANK('V2'!A:A)),ISNA(VLOOKUP('V2'!A:A,$B$2:$B$170,1,FALSE)))
数据排序Sort
# 获取表格importedPayMethods的H列和B列，并先按B列升序 再按H列升序
=SORT({FILTER('importedPayMethods!'H4:H,'importedPayMethods!'F4:F),FILTER('importedPayMethods!B4:B','importedPayMethods!'F4:F)},2,TRUE,1,TRUE)
两个工作表比对
# 新建第三个工作表 在第三个工作表中 用IF进行比对
=IF(payMethodsConfigV2!A1<>payMethodsConfigV2M!A3,JOIN(" && ",payMethodsConfigV2!A1,payMethodsConfigV2M!A3),"equ")
排列组合
# tableA[a1,a2,a3,a4,...,an] 和 tableB[b1,b2,b3,b4,...,bn] 排列组合后，生成 tableC[a1b1,a1b2,...,a1bn,...,anbn]
# 第一步 创建排列组合工作表，将tableA!a:a 放入第一列，将tableB!b:b trans后放入第一行，
=FILTER('tableA'!A$2:A,NOT(ISBLANK('tableA'!A$2:A)))
=TRANSPOSE(FILTER('tableB'!B$2:B,NOT(ISBLANK('tableB'!B$2:B))))
# 第二步 生成所有排列组合
=IF($B4=C$3,IF($B4="","",JOIN("&&",$A4,C$2)),"")
# 第三步 用flaten函数 将二维排列组合表 专为同一列
=SORT(UNIQUE(FLATTEN(PChannelAndProductsMap!$C4:$MZ)))
带变量的报表汇总 INDIRECT
# 直接引用其他sheet单元函数:A2存储了sheetname 引用该sheetname下的B列
INDIRECT($A$2&"!B:B")
# 根据变了filter数据
# 用A2存储需要引用的sheetname
A2 = productSales01
# 用A4存储需要引用的sheetname中的某一列
A4 =$A$2&"!B:B"
# 用INDIRECT 来引用sheetname的数据
=FILTER(INDIRECT($A$4),NOT(ISBLANK(INDIRECT($A$4))))
引用锁定 $
# 查询目标值 是否在某一个范围中
=VLOOKUP(B173,$B$2:$B$170,1,FALSE)
# 查询目标值 是否在某一个范围中
=QUERY($B$2:$B$170,"select B where B="&C173,FALSE)
查询是否存在
# 查询目标值 是否在某一个范围中
=VLOOKUP(B173,$B$2:$B$170,1,FALSE)
# 查询目标值 是否在某一个范围中
=QUERY($B$2:$B$170,"select B where B="&C173,FALSE)
批量VLOOKUP
# 批量查询 输出单列
=ARRAYFORMULA(IF(ISBLANK(J2:J),,IFNA(VLOOKUP(J2:J,dataStatus!A:J,2,FALSE))))
# 批量查询 输出多列
=ARRAYFORMULA(IF(ISBLANK(J2:J),,IFNA(VLOOKUP(J2:J,dataStatus!A:J,{2,3,4,5,6,7,8,9,10},FALSE))))
时间戳日期转换
# 时间戳转日期
=ARRAYFORMULA(IF(ISBLANK(U2:U),,VALUE(U2:U)/86400+DATE(1970,1,1)))
Query sql使用
sql查询
-
例子
# 数据筛选+分组+统计
=QUERY(DATA!A1:B6,"select A, sum(B), count(B) group by A")
# function替代：
=UNIQUE(DATA!A2:A)
=SUM(FILTER(DATA!$B$2:B,DATA!$A$2:A=A2))
=COUNTA(FILTER(DATA!$B$2:B,DATA!$A$2:A=A2))
# group by
=QUERY('game-support-product-list'!A:M,"select A,count(I) where J contains 'utomik' group by A")
# 错误1 没有使用max，sum，count相关统计 不能使用group by
=QUERY('game-support-product-list'!A:M,"select A where J contains 'utomik' group by A")
# 错误2 所有没参与函数统计的列都需要被group by：group by A, B可工作
=QUERY('game-support-product-list'!A:M,"select A,max(I),B where J contains 'utomik' group by A")