Google Sheet Split转置连接 [英] Google Sheets Split Transpose Join
本文介绍了Google Sheet Split转置连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
https://docs.google.com/spreadsheets/d/1OCJU5BGAWMt99cuufWlsua0oNCZCYjrEEFfH-YWbj4Q/edit?usp=sharing
推荐答案
尝试:
=ARRAYFORMULA(SORT(UNIQUE(IFERROR(SPLIT({IFERROR(FILTER(A2:A, B2:B=""));
TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF((
IFERROR(SPLIT(B2:B, ";"))<>""),"♦"&A2:A&"♣"&
IFERROR(SPLIT(B2:B, ";")), )),,99^99)),,99^99), "♦"))}, "♣")))))
或:
=ARRAYFORMULA(ARRAY_CONSTRAIN(SORT(UNIQUE(IFERROR(SPLIT({
IFERROR(FILTER(A2:A&"♣ ♣"&ROW(A2:A), B2:B="", A2:A<>""));
TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF((
IFERROR(SPLIT(B2:B, ";"))<>""),"♦"&A2:A&"♣"&
IFERROR(SPLIT(B2:B, ";"))&"♣"&ROW(A2:A), ))
,,99^99)),,99^99), "♦"))}, "♣"))), 3, 1), 99^99, 2))
这篇关于Google Sheet Split转置连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文