Google表中的调查完成问题

Question

我正在尝试获取有关我使用Google表格的某些调查结果中完成的信息的信息，因为每个调查都有疑问和子问题，子问题都有条件，例如：4。您今天好吗？多个选择答案：好，坏，不愿意说，因此，如果我们单击好的，我们就有3个答案选项，有条件，子提示将是：为什么？因此，在我的调查中，如果有人回答“是”，则有8个问题，问题4、7和8有有条件的问题。现在，这是我计算完成百分比的问题，我使用了以下关系：调查中的输入数量/预期答案数量，但是正如我在条件之前提到的那样，此 预期答案< /strong>此变量是动态的，具体取决于问题4、7和8的答案。因此，如果有人在进行调查的20个人，我想为每种情况获得此变量，如果有人将信息具有ID 20 id。因此，对于每个答案记录，数字将根据问题4、7和8的输入而变化。我已经准备了Google表中的文件有些帮助。

https://docs.google.com/spreadsheets/d/1uov1tihy3jfqpdj2gpdj2gzxvjjlensdelacwwt0s4torbrw/edit？usp = usp = sharing？

a href =“

< a href =“ https://i.sstatic.net/ugcjc.png” rel =“ nofollow noreferrer”>

Answer 1

如果主要或子问题的任何部分使整个主/子问题计算为1使用：

QUERY(TRANSPOSE(B2:F12),,9^9)

如果主/sub问题的所有部分都算作1使用常规范围：

AS2:BB12

如果我弄错了这些范围：

=ARRAYFORMULA(IF(""={TRANSPOSE(TRIM({
 QUERY(TRANSPOSE(B2:F12),,9^9);   QUERY(TRANSPOSE(G2:X12),,9^9);   QUERY(TRANSPOSE(Y2:AH12),,9^9); 
 QUERY(TRANSPOSE(AI2:AR12),,9^9); QUERY(TRANSPOSE(BC2:BD12),,9^9); QUERY(TRANSPOSE(BG2:BH12),,9^9); 
 QUERY(TRANSPOSE(BI2:BR12),,9^9); QUERY(TRANSPOSE(BS2:BU12),,9^9); QUERY(TRANSPOSE(BV2:BX12),,9^9); 
 QUERY(TRANSPOSE(BY2:CA12),,9^9); QUERY(TRANSPOSE(CB2:CD12),,9^9); QUERY(TRANSPOSE(CE2:CG12),,9^9); 
 QUERY(TRANSPOSE(CH2:CJ12),,9^9); QUERY(TRANSPOSE(CK2:CM12),,9^9); QUERY(TRANSPOSE(CN2:CP12),,9^9); 
 QUERY(TRANSPOSE(CQ2:CS12),,9^9); QUERY(TRANSPOSE(CT2:CV12),,9^9)})), AS2:BB12, BE2:BF12}, 0, 1))

---

要总结一下，有17个查询和2列的常规范围，例如17+12 = 29：

=ARRAYFORMULA(MMULT(IF(""={TRANSPOSE(TRIM({
 QUERY(TRANSPOSE(B2:F12),,9^9);   QUERY(TRANSPOSE(G2:X12),,9^9);   QUERY(TRANSPOSE(Y2:AH12),,9^9); 
 QUERY(TRANSPOSE(AI2:AR12),,9^9); QUERY(TRANSPOSE(BC2:BD12),,9^9); QUERY(TRANSPOSE(BG2:BH12),,9^9); 
 QUERY(TRANSPOSE(BI2:BR12),,9^9); QUERY(TRANSPOSE(BS2:BU12),,9^9); QUERY(TRANSPOSE(BV2:BX12),,9^9); 
 QUERY(TRANSPOSE(BY2:CA12),,9^9); QUERY(TRANSPOSE(CB2:CD12),,9^9); QUERY(TRANSPOSE(CE2:CG12),,9^9); 
 QUERY(TRANSPOSE(CH2:CJ12),,9^9); QUERY(TRANSPOSE(CK2:CM12),,9^9); QUERY(TRANSPOSE(CN2:CP12),,9^9); 
 QUERY(TRANSPOSE(CQ2:CS12),,9^9); QUERY(TRANSPOSE(CT2:CV12),,9^9)})), AS2:BB12, BE2:BF12}, 0, 1), 
 SEQUENCE(29, 1, 1, 0)))

现在要跳过sub sub sub quests of空，我们是否可以做：

=ARRAYFORMULA(IF(""=TRANSPOSE(TRIM({
 QUERY(TRANSPOSE(AS2:BB12),,9^9)})), 1, 0))

然后：

​

​“ rel =“ nofollow noreferrer”>

---

所以最后一步是获取“％”％完整性”：

=ARRAYFORMULA(MMULT(IF(""={TRANSPOSE(TRIM({
 QUERY(TRANSPOSE(B2:F12),,9^9);   QUERY(TRANSPOSE(G2:X12),,9^9);   QUERY(TRANSPOSE(Y2:AH12),,9^9); 
 QUERY(TRANSPOSE(AI2:AR12),,9^9); QUERY(TRANSPOSE(BC2:BD12),,9^9); QUERY(TRANSPOSE(BG2:BH12),,9^9); 
 QUERY(TRANSPOSE(BI2:BR12),,9^9); QUERY(TRANSPOSE(BS2:BU12),,9^9); QUERY(TRANSPOSE(BV2:BX12),,9^9); 
 QUERY(TRANSPOSE(BY2:CA12),,9^9); QUERY(TRANSPOSE(CB2:CD12),,9^9); QUERY(TRANSPOSE(CE2:CG12),,9^9); 
 QUERY(TRANSPOSE(CH2:CJ12),,9^9); QUERY(TRANSPOSE(CK2:CM12),,9^9); QUERY(TRANSPOSE(CN2:CP12),,9^9); 
 QUERY(TRANSPOSE(CQ2:CS12),,9^9); QUERY(TRANSPOSE(CT2:CV12),,9^9)})), AS2:BB12, BE2:BF12}, 0, 1), 
 SEQUENCE(29, 1, 1, 0))/(29-IF(""=TRANSPOSE(TRIM({QUERY(TRANSPOSE(AS2:BB12),,9^9)})), 1, 0)))