有没有自动的方法来标准化Google工作表数据 [英] Is there any automatic way to normalise google sheet data
本文介绍了有没有自动的方法来标准化Google工作表数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个超过3000行的数据集,并且我有一些采用这种格式的列
(A / B / C)==表(1)
,我想将它们标准化具有特定ID的行==表(2):
I have a dataset with more than 3000 rows and I have some columns in this format
( A / B / C )==Table (1)
and I want to normalize each row with specific ID == Table (2):
推荐答案
={X1:Y1, "ID_RF", Z1;
ARRAYFORMULA(QUERY({QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(LEN(X2:X), "♠"&X2:X&"♦"&Y2:Y&"♦"&SPLIT(Z2:Z, "/"), )), , 999^99)), , 999^99),
"♠")), "♦")), "where Col3 !=''"), TRANSPOSE(SPLIT(JOIN(",", REPT(SPLIT(
QUERY(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(LEN(X2:X), "♠"&X2:X&"♦"&Y2:Y&"♦"&SPLIT(Z2:Z, "/"), )), , 999^99)), , 999^99),
"♠")), "♦")), "where Col3 !=''"),
"select Col2,count(Col2) where Col2 is not null group by Col2 label count(Col2)''"),
",")&",", QUERY(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(LEN(X2:X), "♠"&X2:X&"♦"&Y2:Y&"♦"&SPLIT(Z2:Z, "/"), )), , 999^99)), , 999^99),
"♠")), "♦")), "where Col3 !=''"),
"select count(Col2) where Col2 is not null group by Col2 label count(Col2)''"))),
","))&COUNTIFS(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(LEN(X2:X), "♠"&X2:X&"♦"&Y2:Y&"♦"&SPLIT(Z2:Z, "/"), )), , 999^99)), , 999^99),
"♠")), "♦")), "select Col2 where Col3 !=''"),
QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(LEN(X2:X), "♠"&X2:X&"♦"&Y2:Y&"♦"&SPLIT(Z2:Z, "/"), )), , 999^99)), , 999^99),
"♠")), "♦")), "select Col2 where Col3 !=''"), ROW(INDIRECT("A1:A"&
COUNTA(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(LEN(X2:X), "♠"&X2:X&"♦"&Y2:Y&"♦"&SPLIT(Z2:Z, "/"), )), , 999^99)), , 999^99),
"♠")), "♦")), "select Col2 where Col3 !=''")))), "<="&ROW(INDIRECT("A1:A"&COUNTA(
QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(LEN(X2:X), "♠"&X2:X&"♦"&Y2:Y&"♦"&SPLIT(Z2:Z, "/"), )), , 999^99)), , 999^99),
"♠")), "♦")), "select Col2 where Col3 !=''")))))}, "select Col1,Col2,Col4,Col3"))}
这篇关于有没有自动的方法来标准化Google工作表数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文