摘要: 本文总结一些常用的 GSheet 函数,提高 GSheet 编辑的效率。
regexreplace 批量替换多组数据
Functions:
- regexreplace
- 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 intext
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 theTEXT
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:
- 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) |
- 其中 A2 表示目标数据,及 0.0005
$D$1:$E$6
表示映射关系数据,这里我们必须保证这个返回的第一列是和目标数据相关的,用于搜索数据。- 第三个参数需要重点注意,他和第二个参数相关,表示最终返回哪个数据,这个值是在第二个参数数据的 index,比如我们第二个参数是一个有 2 列的数据,那么如果 该参数 = 1, 表示返回第二列数据。
- 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'
orI24
.range
– The range to consider for the search. The first column in the range is searched for the key specified insearch_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.
- If
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.
- It’s recommended to set
组合多项字符串及附加操作
Functions:
- JOIN
- SPLIT
- SUBSTITUTE
需求:组合多项字符串为逗号分隔字符串,并且末尾不能有多于的逗号。这里实际上使用 JOIN 就可以实现,但是如果我们需要 JOIN 4 个项目,但是并不是所有的 Cell 都有数据,比如有的有 3 个数据,有的有 1 个数据,这样只用 JOIN 就是有出现 ,,,
分隔符之间无字符。所以我们需要使用 SPLIT
进行一次清理,因为 SPLIT
默认选项remove_empty_text
是 true,就会把空白的分割值忽略掉。
1 | =SUBSTITUTE(JOIN(",",SPLIT(JOIN("%",M2:P2),"%",0)),"%","") |
这里的实现步骤:
- 先将数据使用
%
JOIN - 然后
SPLIT
这样空白的就没有了 - 再用
,
JOIN
SPLIT function
Divides text around a specified character or string and puts each fragment into a separate cell in the row.
Examples
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’. Setsplit_by_each
toFALSE
to turn off this behaviour.
- By default, each character in
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 (ifTRUE
). IfFALSE
, 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.