我正在使用一个函数从一张纸上获取我需要的一些信息。
=QUERY( COE_ALL!A:Z, "SELECT B where K = 'Jeremy' order by A ", 0)
问题是填充的名称作为"名字姓氏"(如"约翰史密斯"(出现,我需要它们以逗号作为"史密斯,约翰"的查询。
我无法编辑数据来自的原始工作表,但我需要对其进行更改,以便我可以引用具有该格式的主工作表。
尝试:
=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(QUERY(SPLIT(
QUERY(COE_ALL!A:Z, "select B where K = 'Jeremy' order by A", 0), " "),
"select Col2,Col1", 0)),,999^99)), " ", ", "))
试试这个:
=query({COE_ALL!B:B, arrayformula(query(arrayformula(split(COE_ALL!K:K," ")),"Select Col1") & ", " & query(arrayformula(split(COE_ALL!K:K," ")),"Select Col2"))},"Select Col1,Col2 where Col2 like '%Jeremy%'")