GSheet常用函数快速参考

发表于 | 分类于 |

摘要: 本文总结一些常用的 GSheet 函数，提高 GSheet 编辑的效率。

regexreplace 批量替换多组数据

Functions：

  1. regexreplace
  2. ArrayFormula

需求：给定一列数据 T，然后给定一组映射数据 M(K-V)，要求替换数据列 T 中的所有的 K 到 V。

该方案解决方法有点繁琐，但是目前是最简单的方法，就是嵌套使用 regexreplace。

1
=ArrayFormula(regexreplace(regexreplace(regexreplace(regexreplace(  regexreplace(regexreplace(  regexreplace(  regexreplace(  regexreplace(A2:A,D2,E2),D3,E3)  ,D4,E4)  ,D5,E5)  ,D6,E6),D7,E7)  ,D8,E8),D9,E9),D10,E10))

简单的说就是依次替换映射数据，然后把上一次替换结果传入下一次的输入。

REGEXREPLACE

Replaces part of a text string with a different text string using regular expressions.

Sample usage

REGEXREPLACE("Spreadsheets", "S.*d", "Bed")

Syntax

REGEXREPLACE(text, regular_expression, replacement)

  • text – The text, a part of which will be replaced.

  • regular_expression – The regular expression. All matching instances in text will be replaced.

  • replacement – The text that will be inserted into the original text.

Notes

  • Google products use RE2 for regular expressions. Google Sheets supports RE2 except Unicode character class matching. Learn more on how to use RE2 expressions.
  • This function only works with text (not numbers) as input and returns text as output. If a number is desired as the output, try using the VALUE function in conjunction with this function. If numbers are used as input, convert them to text using the TEXT function.

See also

REGEXEXTRACT: Extracts matching substrings according to a regular expression.

REGEXMATCH: Whether a piece of text matches a regular expression.

SUBSTITUTE: Replaces existing text with new text in a string.

REPLACE: Replaces part of a text string with a different text string.

Examples

VLOOKUP 批量数据分类

Functions：

  1. vlookup:

需求：给定一列数据 T，给定一组映射数据，映射数据表示数据值范围及映射目标值，批量将数据 T 根据映射关系转化为映射值。

举个简答的例子：

我们有一组概率范围对应的评级级别：

prob level
0 0
0.001 1
0.006 2
0.012 3
0.03 4
1 5

表示如果概率落在上述 prob 的范围内，则级别为 level 数据，比如 0<0.0005<0.001，则 0.0005 级别是 0

实现方法：

1
=VLOOKUP(A2,$D$1:$E$6,2,1)
  1. 其中 A2 表示目标数据，及 0.0005
  2. $D$1:$E$6 表示映射关系数据，这里我们必须保证这个返回的第一列是和目标数据相关的，用于搜索数据。
  3. 第三个参数需要重点注意，他和第二个参数相关，表示最终返回哪个数据，这个值是在第二个参数数据的 index，比如我们第二个参数是一个有 2 列的数据，那么如果 该参数 = 1， 表示返回第二列数据。
  4. is_sorted：需要设置为 1，只有 TRUE 才会返回最相近的匹配。

完全匹配用例

该函数还可以实现搜索一列，然后替换对应的字段，但是要求数据具有一定规律，比如 Prefex。这样就可以通过一些其他公式实现。

1
=IFERROR(VLOOKUP(LEFT(H7,LEN(H7)-2),data!$C$2:$D$52,2,0)&RIGHT(H7,1))

data!$C$2:$D$52 是一个 key value 对照关系。
我们对目标字符串 LEFT(H7,LEN(H7)-2) 进行处理，转化成数据的 key 列，这样就可以通过 VLOOKUP 来替换为 value 了。注意这里最后一个参数是 0 ，也就是不是有序的，这个才能实现全局匹配。

IFERROR 表示如果找不到我们可以显示其他内容，比如留空。

VLOOKUP

Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

Sample usage

VLOOKUP(10003, A2:B26, 2, FALSE)

Syntax

VLOOKUP(search_key, range, index, [is_sorted])

  • search_key – The value to search for. For example, 42, 'Cats' or I24.
  • range – The range to consider for the search. The first column in the range is searched for the key specified in search_key.
  • index – The column index of the value to be returned, where the first column in range is numbered 1.
    • If index is not between 1 and the number of columns in range, #VALUE! is returned.
  • is_sorted – [TRUE by default] – Indicates whether the column to be searched (the first column of the specified range) is sorted. FALSE is recommended in most cases.
    • It’s recommended to set is_sorted to FALSE. If set to FALSE, an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.
    • If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.

组合多项字符串及附加操作

Functions：

  1. JOIN
  2. SPLIT
  3. SUBSTITUTE

需求：组合多项字符串为逗号分隔字符串，并且末尾不能有多于的逗号。这里实际上使用 JOIN 就可以实现，但是如果我们需要 JOIN 4 个项目，但是并不是所有的 Cell 都有数据，比如有的有 3 个数据，有的有 1 个数据，这样只用 JOIN 就是有出现 ,,, 分隔符之间无字符。所以我们需要使用 SPLIT 进行一次清理，因为 SPLIT 默认选项remove_empty_text 是 true，就会把空白的分割值忽略掉。

1
=SUBSTITUTE(JOIN(",",SPLIT(JOIN("%",M2:P2),"%",0)),"%","")

这里的实现步骤：

  1. 先将数据使用 % JOIN
  2. 然后 SPLIT 这样空白的就没有了
  3. 再用 , JOIN

SPLIT function

Divides text around a specified character or string and puts each fragment into a separate cell in the row.

Examples

Make a copy

Sample usage

SPLIT("1,2,3", ",")

SPLIT("Alas, poor Yorick"," ")

SPLIT(A1, ",")

Syntax

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

  • text – The text to be divided.

  • delimiter – The character or characters to be used to split text.

    • By default, each character in delimiter is considered individually, e.g. if delimiter is ‘the’, then text is divided around the characters ‘t’, ‘h’ and ‘e’. Set split_by_each to FALSE to turn off this behaviour.

  • split_by_each – [ OPTIONAL – TRUE by default ] – Whether or not to divide text around each character contained in delimiter.

  • remove_empty_text – [ OPTIONAL – TRUE by default ] – Whether or not to remove empty text messages from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cells values are added between consecutive delimiters.

Notes

  • Note that the character or characters to split the string around will not be contained in the result themselves.

See also

CONCATENATE: Appends strings to one another.

参考：https://docs.google.com/spreadsheets/d/e/2PACX-1vTkNsIXXIjvzgbw5_65Z72wlUPCN-7HIPfJu9bawd4imBMf5dIvg0VWiUrU6QE_xAJkw_J57v6kcOT4/pubhtml