英文:
Google Sheet Formula - Reformat data via cumulated formulas to have a specific table
问题
I have this simplified spreadsheet (link) containing answers from a survey in the sheet "Answers Survey".
在这个简化的电子表格中（链接），包含了来自“Answers Survey”工作表的调查答案。
In the sheet, "Analysis | Answers Survey", I would like to reformat these data in one formula (instead of 4 formulas as in my example) to obtain the same table than in the range "A100:J123".
在“Analysis | Answers Survey”工作表中，我想使用一个公式（而不是我示例中的4个公式）重新格式化这些数据，以获得与范围“A100:J123”中相同的表格。
Could you please help me to obtain this "special" formula ?
你能帮我获得这个“特殊”的公式吗？
Thanks in advance.
提前感谢。
Regards
问候
英文:
I have this simplified spreadsheet (link) containing answers from a survey in the sheet "Answers Survey".
In the sheet, "Analysis | Answers Survey", I would like to reformat these data in one formula (instead of 4 formulas as in my example) to obtain the same table than in the range "A100:J123".
Could you please help me to obtain this "special" formula ?
Thanks in advance.
Regards
答案1
得分: 1
=let(Σ;'Answers Survey'!A3:AA;
Γ;sort(unique(filter(choosecols(Σ;1;2;6);index(Σ;;7)<>""));1;1;2;1;3;1);
{Γ\byrow(Γ;lambda(Λ;bycol(switch(choosecols(Σ;7;8;9;10;11);"high";3;"medium";2;"low";1;"N/A";;;);lambda(avg_;round(average(ifna(filter(avg_;byrow(choosecols(Σ;1;2;6);lambda(z;join(;z)))=join(;Λ))));1)))))
byrow(Γ;lambda(Λ;bycol(choosecols(Σ;12;27);lambda(join_;textjoin(char(10);1;ifna(filter(join_;byrow(choosecols(Σ;1;2;6);lambda(z;join(;z)))=join(;Λ))))))))})
英文:
Added a possible solution here for you to test out:
=let(Σ;'Answers Survey'!A3:AA;
Γ;sort(unique(filter(choosecols(Σ;1;2;6);index(Σ;;7)<>""));1;1;2;1;3;1);
{Γ\byrow(Γ;lambda(Λ;bycol(switch(choosecols(Σ;7;8;9;10;11);"high";3;"medium";2;"low";1;"N/A";;;);lambda(avg_;round(average(ifna(filter(avg_;byrow(choosecols(Σ;1;2;6);lambda(z;join(;z)))=join(;Λ))));1)))))\
byrow(Γ;lambda(Λ;bycol(choosecols(Σ;12;27);lambda(join_;textjoin(char(10);1;ifna(filter(join_;byrow(choosecols(Σ;1;2;6);lambda(z;join(;z)))=join(;Λ))))))))})
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论