【Google Sheets】Google workspace 中的excel常用公式

国外协同办公用的是Google workspace，而里面数据管理用的是Google Sheets，虽然一部分和office的excel一样，但也有线上文档专用的功能

这里的公式和 Google App Script （GAS）用的不是一个东西。GAS 的常用代码在另一篇文字总结。

• vlookup：纵向查找。在范围的第一列中搜索某个键值，并返回所找到的行中指定单元格的值。

这个和excel的一样，只能匹配查询范围左边第一列的值返回右侧的值

=VLOOKUP(搜索的值, 搜索的范围, 返回第几列的值, 查询模式)

搜索的值：字面意思

搜索的范围：字面意思，但搜索的值只在第一列中查询，且一定要包含需要返回的值的列

返回第几列的值：字面意思，注意第一列的序号是1而不是0

查询模式：0/false是默认完全匹配，1/true近似匹配，通常都是设置0（如果有多个值相同时返回最前/上的一个值）

• xlookup：交叉查询。在查询范围中找到匹配值时，返回相应值在结果范围中的对应值。

这个和excel的一样，比VLOOKUP高级可以直接返回任意一列想查询值的值

=xlookup(搜索的值, 搜索的范围, 返回值的范围, 没匹配时返回的值,查询模式,返回值的查询模式)

搜索的值：字面意思

搜索的范围：字面意思，但只能是一列

返回值的范围：字面意思，但只能是一列

没匹配时返回的值：如果查询不到对应的值时展示的值，默认空

查询模式：重点

0 表示完全匹配；

1 表示完全匹配，或寻找大于搜索的值的下一个值；

-1 表示完全匹配，或寻找小于搜索的值的下一个值；

2 表示通配符匹配。如果未指定，则默认寻找完全匹配的内容。（没用过）

返回值的查询模式：重点

1 表示从第一个条目搜索到最后一个条目（默认模式）

-1 表示从最后一个条目搜索到第一个条目；

2 表示通过二分搜索在整个范围中进行搜索，并假定范围中的内容是以升序排序；（没用过）

-2 表示通过二分搜索在整个范围中进行搜索，并假定范围中的内容是以降序排序。（没用过）

• IMPORTRANGE：从指定的电子表格中导入一组单元格

这个是Google sheet里有的，能从其他sheet文档里获取/导入/引用指定数据

=IMPORTRANGE(Sheet的链接,sheet名字!单元格范围)

Sheet的链接：字面意思，好像只能是Google sheet的链接，注意用英文双引号括起来

sheet名字!单元格范围：字面意思，但这里有个用法是（sheet名字!A2:A），这种就能排除前面1列标题列，可以调整数值起始范围

注意：Sheet的链接参数不能少，如果要引用当前Sheet其他分页时也要加入Sheet的链接

不能这样写：

=IMPORTRANGE(当前sheet其他分页名字!单元格范围)

如果要引用当前Sheet其他分页时可以使用下面的公式query()

• query：对数据运行一项采用 Google Visualization API 查询语言的查询。

这个公式类似于SQL

=QUERY(查询范围, "SQL语句", 标题行数量)

查询范围：字面意思

"SQL语句"：需要使用Google Visualization API 查询语言，参考链接在下面

https://developers.google.com/chart/interactive/docs/querylanguage?hl=zh-cn

标题行数量：为空或者-1时由系统自行判断（没有用过）

目前我用得最多的并不是里面的查询功能，而是引用当前Sheet其他分页的内容

可以直接写成

=QUERY(查询范围)

这样就能自动导入指定范围内的所有值，且是清除了各种格式和公式的纯文本内容

• SORT：依据一列或多列中的值对给定数组或范围中的各行进行排序。

能直接对给定范围内的数值进行排序后输出

=SORT(范围, 排序列, 排序规则, [排序列2, …], [排序规则2, …])

范围：字面意思

排序列：范围内第几列，要与后一项排序规则进行匹配

排序规则：0/false为降序，1/true为升序，默认升序

[排序列2, …], [排序规则2, …]：如果有多列排序条件则可以按需添加

若所有的列默认按升序，则可以省略写成

=SORT(范围, 排序列, [排序列2, …] )

但如果有一列是降序，则所有都要写上排序规则

• FILTER：返回过滤后的源范围版本，其中仅包含满足指定条件的行和列。

对所选范围内数据进行过滤后输出，可以包括多个条件

=FILTER(范围, 条件1, [条件2, …])

范围：字面意思

条件1, [条件2, …]：可以是等式或者不等式等条件

• 多个公式复用

上述的公式都能相互套用

例如

引用其他链接/文档的数据后进行排序

=Sort(IMPORTRANGE(Sheet的链接,sheet名字!单元格范围), 排序列, 排序规则, [排序列2, …], [排序规则2, …])

引用当前文档的数据后进行过滤再输出

=FILTER(QUERY(查询范围), 条件1, [条件2, …])

这样就能保证只要有一份原始数据，其他数据都能通过公式进行输出，保证了内容的准确性，减少人工操作导致的失误