大家好，我正在嘗試獲取有關匯出為 Excel 格式的某些調查結果的完整性資訊，我正在使用 Google 表格，因為每個調查都有問題和子問題，子問題都有條件，例如：4。你今天？多選答案：好，壞，不想說，所以我們有 3 個答案選項，如果我們點擊好，有一個條件，子問題將是：為什么？。所以在我的調查中有 8 個問題，如果有人回答“是”，問題 4、7 和 8 有條件問題。現在這是我的問題來計算我使用這個關系的完整性百分比：number of inputs in the survey/number of expected answers，但正如我之前提到的，條件會影響這個預期的答案這個變數是動態的，取決于問題 4、7 和 8 的答案。所以我想為每個案例獲取這個變數，如果有人輸入資訊將有一個 ID，如果我們有 20 人進行調查，我們將有 20 個 ID。因此，對于每條答案記錄，數字都會根據問題 4、7 和 8 的輸入而改變。我已經在 Google 表格中準備了一份檔案，將采用我嘗試過的完整方法，但仍然很難做到我想要的正確方法一些幫助。
uj5u.com熱心網友回復：
如果 MAIN 或 SUB 問題的任何部分使整個 MAIN/SUB 問題計為 1，請使用：
QUERY(TRANSPOSE(B2:F12),,9^9)
如果 MAIN/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 個具有 12 列的常規范圍，例如 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 問題，如果為空，我們可以這樣做：
=ARRAYFORMULA(IF(""=TRANSPOSE(TRIM({
QUERY(TRANSPOSE(AS2:BB12),,9^9)})), 1, 0))
接著：
再次，如果您有更多要跳過的內容，請添加：
所以最后一步是獲得“百分比完整性”：
=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)))
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/493933.html
標籤：if 语句 谷歌表格 条件语句 谷歌表格公式 谷歌查询语言