Google表格中的引用随着新表单的提交而发生了变化 [英] References changing in Google Sheets with new forms submissions

问题描述

我有一个Google表格，该表格从Google表单中获取，然后使用该表格制作视图以在Google网站上显示数据和图形.我注意到图形突然显示无数据"，并且当我检查时，似乎由于某种原因，馈送此类表的范围已将其引用的单元格从提交的表单更改为最新的行，而不是保留范围，即使它是固定的.

I have a Google sheet that feeds from a Google form and then I use the sheet to make views to show data and graphics on Google sites. I noticed that suddenly the graphics said 'No Data', and when I checked, it seemed that for some reason the ranges that feed such tables had changed their referenced cells to the newest row from the submitted forms instead of retaining the range even if it was fixed.

我不知道为什么或如何发生这种情况，但是它发生在不同的地方，不同的公式以及工作表，不是全部发生，而是很多发生.以前它工作正常.

I have no idea why or how this happened, but it happened in different places and different formulas along with the sheets, not on all but quite on many. Previously it was working fine.

这同样适用于1个单元格和列数组的公式.更改仅是代替使用了L2:L，而是将2更改为54，这样即使$固定了，也像L54:L一样结束了.

This happened to formulas for 1 cell and column arrays alike. The change was merely that instead of using let's say L2:L, it changed the 2 for 54 so it ended like L54:L, even if it was fixed with $.

例如:最初$AA$2:$AA变为$AA$54:$AA

注意:这影响了随机部分，并且具有相同公式的其他列可能不会受到影响，无论它们引用的是同一列还是相邻的列.

NOTE: This has affected random sections and other columns that have the same formula may not be affected, regardless as to if they refer the same columns or adjacent ones.

以下是此更改的示例，其中54应该是2.

The following are examples of this change, where 54 should have been 2.

1:可在整个列上使用的数组.

1: An array that works on an entire column.

    ={"Person";ArrayFormula(IF(LEN(A54:A), 'Responses'!AL54:AL&"" &                                 
    'Responses'!AK54:AK&
    " " & 'Responses'!AJ54:AJ&
    " " & 'Responses'!AI54:AI
    ,""))}

2:仅具有固定范围的响应数

2: A mere count of responses that has a fixed range

    =COUNTIF('Responses'!$AY$54:$AY, "si")

和3:计数不同的值

    =countifs('Responses'!$AA$54:$AA,"Sí",
                'Responses'!$AD$54:$AD, "<>Rod",
                'Responses'!$AD$54:$AD, "<>Ato",
                'Responses'!$AD$54:$AD, "<>Ref",
                'Responses'!$AD$54:$AD, "<>Cua"
                )

没有错误消息.

我只是想了解这是如何发生的，以避免它在其他表单提交中再次发生(由其他人完成)并进行更正(希望不是手动进行的).

I'm just trying to comprehend how this happened to avoid it happening again on further forms submissions (done by another person) and to correct this (hopefully not manually).

推荐答案

真正锁定需要使用 INDIRECT 的范围:

to truly lock down the range you need to use INDIRECT:

={"Person"; 
 ARRAYFORMULA(IF(LEN(INDIRECT("A2:A")), INDIRECT("Responses!AL2:AL")&"" &
 INDIRECT("Responses!AK2:AK")&
 " " & INDIRECT("Responses!AJ2:AJ")&
 " " & INDIRECT("Responses!AI2:AI"), ))}

---

=COUNTIF(INDIRECT("Responses!AY2:AY"), "si")

---

=COUNTIFS(INDIRECT("Responses!AA2:AA"), "Sí",
          INDIRECT("Responses!AD2:AD"), "<>Rod",
          INDIRECT("Responses!AD2:AD"), "<>Ato",
          INDIRECT("Responses!AD2:AD"), "<>Ref",
          INDIRECT("Responses!AD2:AD"), "<>Cua")

这篇关于Google表格中的引用随着新表单的提交而发生了变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！