理想化
____A___B___C Result
1___@___@___? @ 4
2___!___!___@ ! 3
3___?___!___@ ? 2
我尝试=query(G6:FI1005,"select A,B,C count(A:C)")或filter(A1:C3),但它们失败并显示警告。
我希望有一个答案,我也想知道下面更困难的情况。
____A___B___C___D___E___F Result
1___NO__''__OK__''__OK__'' @ at 2
2___@___at__@___at__!___sin ! sin 1
3___?___que_@___at__?___que ? que 1
1:选择column1为"OK“的行
2:显示下一个单元格的值。
3:也算数。
发布于 2018-08-12 05:37:29
对于问题1,请尝试以下公式:
=QUERY(ArrayFormula(TRANSPOSE(SPLIT(JOIN(",",{A:A;B:B;C:C}),","))&{"",""}),"select Col1,count(Col2) group by Col1 order by count(Col2) desc")
对于问题2,请尝试:
=ArrayFormula(QUERY(TRANSPOSE(SPLIT(CONCATENATE(FILTER(A2:F,A1:F1="OK")&" "&FILTER(B2:G,A1:F1="OK")&"|"),"|"))&{"",""},"select Col1,count(Col2) where Col1<>' ' group by Col1 order by count(Col2) desc"))
我设置了这个示例表来展示它们的工作方式:
https://docs.google.com/spreadsheets/d/1hfFETs_5Tia5UILNGTBD9oUneZfSJZ8VeVgwnNetsu8/edit?usp=sharing
https://stackoverflow.com/questions/51802693
复制相似问题