刷新单元而不是手动按下ctrl-Shift-E [英] refresh cell instead of pressing ctrl-Shift-E manually
问题描述
我在表单上提供了一些查询，将信息提取到数据库中。
我遇到的问题是， t显示查询显示：警告：一个或多个这些结果条目可能不会显示。选择ctrl + Shift + E显示它们。
我尝试过清除价值并粘贴它们无济于事。
我还能做什么？
我已经被自己多次咬了几次，所以有一些经验，我已经使用了一个heath-robinson解决方法，它已经为我工作。但是YMMV。
OP的问题讨论了公式运行良好的情况，但经过一段时间的操作后，Ctrl + Shift + E情况已经开始（还有其他一些情况，在这种情况下，由于一个公式试图覆盖另一个公式写入的单元格的结果，可能会出现Ctrl + Shift + E。在这种情况下，有时候在所需公式的开始处添加EXPAND会有诀窍。同样使用过滤器函数，而不是'IF'比较，可以清除previous = CONTINUE（1,2,3）单元格...但是这些可能都不会对OPs问题有所帮助。）
根据我的经验，电子表格可能会随着时间的推移发展出这样一种混乱的行为，因此需要Ctrl + Shift + E，这意味着一段时间里有几次使用电子表格导致数据发生变化。我可以进一步推测，当被引用的数据更具动态性时，这种混淆行为更有可能。添加的行，删除的行或者还有其他公式也可以从相同的数据集计算出来（更不用说这两个公式相互关联）。
我认为这个成语的混淆行为是合适的，因为电子表格的实际工程只是一个开放的框，仅供谷歌工程师使用;我们无法理顺其运作。因此，在OP的情况和我的经验中，电子表格运行良好，但如果在公式没有任何变化的情况下需要Ctrl + Shift + E，那么看起来神秘不可思议，只有在工作表中累积的数据更改通过使用。
我举了一个我的解决方案的例子，用我的一个例子。请注意，在我的情况下，数据以及有问题的公式都放在一张表单中，我称之为制作表
解决方法是使用脚本来复制'模板-sheet'，与生产表在同一电子表格中。模板表的结构与具有相同列标题的生产表完全相同，但仅包含几行样品数据。它与生产板具有相同的公式，它指的是自我相同的模板板（而不是生产板）中的样品数据。重要的是，模板工作表并未显示'混淆行为' - 如果模板的示例数据发生任何更改，则公式单元格中不需要Ctrl + Shift + E。它还包含了制作表的格式。
所以，当脚本运行时，它会创建模板表的副本;它随后将生产工作表的数据复制到此模板副本并重新应用这些格式。这副本成为新的制作表。旧生产表是隐藏的（在我的情况下，但可以删除）。
我有这个系统在12个人每天使用的任务列表电子表格上运行，任务通过Google表单全天进入（并且通过另一个脚本自身复制到制作表中，与本OP不相关，因此未显示）。一旦任务完成，任务的行将从生产表中删除。所以数据会在一天中不断增长和缩小。通过在insertSheet（）函数上定时触发，每晚创建复制表单。这里是我使用的脚本：
function insertSheet（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sheet = ss.getSheets（）[0]; //第一张是生产表
var sheetRange = sheet.getRange（'A3：P'）;
var sheetValues = sheetRange.getValues（）;
var d = Utilities.formatDate（new Date（），Session.getTimeZone（），'ddMMMyyyy -hh：mm：ss'）;
var ex = sheet.setName（'CCEs'+ d）;
var templateSheet = ss.getSheetByName（'templateSheet'）;
var s2 = ss.insertSheet（0，{template：templateSheet}）;
var height = sheetRange.getHeight（）;
s2.insertRowsAfter（2，height）
s2.setName（'CCEs'）;
templateSheet.hideSheet（）;
ex.hideSheet（）;
s2.getRange（'A3：P'+（sheetValues.length + 2））。setValues（sheetValues）;
格式（）; //
templateSheet.hideSheet（）;
ex.hideSheet（）;
函数格式（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var formatRange = ss.getSheetByName（'templateSheet'）。getRange（'B2：N2'）。copyTo（ss.getSheetByName（'CCEs'）。getRange（2,2，ss.getSheetByName（'CCEs'）） .getMaxRows（）-1,14），{formatOnly：true}）;
}
在部署此策略之前，电子表格会遇到confused-behavior几天左右。
该表包含三个会变得混淆的公式。它们对于OP来说并不重要，但我在这里复制的目的只是为了让读者对这个策略的成功感到满意：
= arrayformula（IFERROR（FILTER（if（row（O：O）= 1，Auto Time Stamp，iferror（1/0））& O：O& if（row（O：O）= 1，copy ），），len（A：A）），Error））
= Arrayformula（iferror（if（filter（L：L，len（A：A）） - filter（A：如果（int（filter（L：L，len（A：A）） - filter（A：A，len（A：A）））= 0，则text（ （A：A，len（A：A）），HH：mm），H：mm ），int（filter（L：L，len（A：A）） - filter（A：A，len（A：A）））&Day（s），& text（text（filter （A：A，len（A：A）），HH：mm），H：mm））， iferror（1/0）），Time Taken））
= arrayformula（IFERROR（if（（ISBLANK（FILTER（J1：J，LEN（A1：A）））*（FILTER G1：G，LEN（A1：A））=Normal）*（now（） - FILTER（A1：A，LEN（A1：A））> OverdueTimings！A1）），OVERDUE Normal （ISBLANK（FILTER（J1：J，LEN（A1：A）））*（FILTER（G1：G，LEN（A1：A））=Urgent）*（now（） - FILTER（A1：A，L EN（A1：A））>如果（（ISBLANK（FILTER（J1：J，LEN（A1：A）））*（FILTER（G1：G，LEN（A1：A））=非常紧急，过期定标！A2）），OVERDUE Urgent （IF（1/0））））），countif（if（（）（）（）（过滤V（紧急））， ISBLANK（FILTER（J1：J，LEN（A1：A）））*（FILTER（G1：G，LEN（A1：A））=Normal）*（now（） - FILTER（A1：A，LEN A1（A：）> OverdueTimings！A1）），OVERDUE Normal，如果（（ISBLANK（FILTER（J1：J，LEN（A1：A）））*（FILTER（G1：G，LEN（A1：A ）（）（））=Urgent）*（now（） - FILTER（A1：A，LEN（A1：A））> OverdueTimings！A2））OVERDUE Urgentif（（ISBLANK（FILTER（J1： LEN（A1：A）））*（FILTER（G1：G，LEN（A1：A））=Very Urgent）*（now（） - FILTER（A1：A，LEN（A1：A））> OverdueTimings！A3）），OVERDUE V.Orgent，iferror（1/0）））），OVERDUE *）& OVERDUE））
几天后，我们删除所有累积的旧生产表。
I have a few queries on the sheet where I extract info to a database.
The problem I have is that the code implemented was fine till some sheets won't display the query showing the: "warning: one or more of these results' entries may not be displayed. Select ctrl+Shift+E to show them.
I tried clearing the values and pasting them back to no avail.
what else can I do?
IMHO I don't think that there is a canonical answer to this question - there has always been a loud clamour from users around certain topics which merely dissipate into an eerie silence of the Google docs engineering team (this issue seems to be shrouded in the same eternal mystery as IMPORTRANGE frequently failing or SPLIT not creating a perfect matrix).
I have been bitten by this myself several times so have some experience. I have used a heath-robinson workaround, which has worked for me. But YMMV.
The OP's question discusses the situation where there is a formula which was working well, but after some time of operation, a Ctrl+Shift+E situation has started to occur.
(There are other situations where Ctrl+Shift+E might occur due to results of one formula attempting to overwrite onto cells written by another formula. In this scenario, sometimes adding EXPAND at the beginning of the desired formula does the trick. Also using Filter functions, rather than 'IF' comparisons, does mop-up previous =CONTINUE(1,2,3) cells ... but perhaps neither of these are likely to be of help to the OPs question).
In my experience the spreadsheet might develop such a 'confused' behaviour over time, so requiring Ctrl+Shift+E, meaning a period of time where there are several uses-of-the-spreadsheet resulting in changes to the data. I might further speculate that there is more likelihood of this confused behaviour when the referred-to data is more dynamic e.g. rows added, rows removed OR that there are other formulas which also calculate from the same data set (let alone that these two formulas relate to each other).
I think that the idiom 'confused-behaviour' is appropriate since the actual engineering of the spreadsheet is an open box only to google engineers; we cannot rationalise its workings. So it seems mysterious and magical how, in the OP's case and my experience, the spreadsheet was working well, but then altered behaviour to require Ctrl+Shift+E without there being any change in the formulas, only in the sheets' accumulated data changes through usage.
I am giving an example of my workaround using an example case of mine. Note that in my case the data and also the problematic formulas are in one single sheet which I am calling the production sheet
The workaround involves using a script to duplicate a 'template-sheet', in the same spreadsheet as the production sheet. The template-sheet is structurally identical to the production sheet with the same column headings, but with only a few lines of sample data. It has the same formulas as the production sheet, which refer to the sample data in the self-same template sheet (not to the production sheet). Importantly, the template sheet is not showing 'confused-behaviour' - no Ctrl+Shift+E is required in the formula cells should there be any changes to the template's sample data. It also contains the formating of the production sheet.
So, when the script runs, it creates a duplicate of the template-sheet; it subsequently copies over the production sheet's data to this template duplicate and reapplies the formats. This duplicate becomes the new production sheet. The old production sheet is hidden (in my case, but could be deleted).
I have this system operational on a Tasks List spreadsheet being used by 12 people daily, where Tasks come in by google form throughout the day (and are themselves copied over to the production sheet by another script, not shown as not relevant to this OP). Once a task is completed, the task's row is removed from the production sheet. So the data grows and shrinks constantly throughout the day. The duplicate sheet is created each night, by timed trigger upon the insertSheet() function. Here is the script I use:
function insertSheet(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet =ss.getSheets()[0]; //first sheet is the production sheet
var sheetRange = sheet.getRange('A3:P');
var sheetValues= sheetRange.getValues();
var d = Utilities.formatDate(new Date(), Session.getTimeZone(), 'ddMMMyyyy-hh:mm:ss');
var ex = sheet.setName('CCEs' + d);
var templateSheet = ss.getSheetByName('templateSheet');
var s2 = ss.insertSheet(0,{template: templateSheet});
var height = sheetRange.getHeight();
s2.insertRowsAfter(2, height)
s2.setName('CCEs');
templateSheet.hideSheet();
ex.hideSheet();
s2.getRange('A3:P' + (sheetValues.length+2)).setValues(sheetValues);
format(); //
templateSheet.hideSheet();
ex.hideSheet();
}
function format(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formatRange = ss.getSheetByName('templateSheet').getRange('B2:N2').copyTo(ss.getSheetByName('CCEs').getRange(2,2, ss.getSheetByName('CCEs').getMaxRows() -1,14), {formatOnly:true});
}
Prior to deploying this strategy, the spreadsheet would experience 'confused-behaviour' after a couple of days or so.
The sheet contained three formulas which would become confused. They are not important to the OP, but I duplicate here just to give the reader a feeling for the success of this strategy:
=arrayformula(IFERROR(FILTER(if(row(O:O) =1,"Auto Time Stamp ",iferror(1/0)) &O:O&if(row(O:O) =1,"copy",),len(A:A)),"Error"))
=Arrayformula(iferror(if(filter(L:L, len(A:A)) - filter(A:A, len(A:A))>0, if( int(filter(L:L, len(A:A)) - filter(A:A, len(A:A))) = 0 , text( text(filter(L:L, len(A:A)), "HH:mm") -text(filter(A:A, len(A:A)), "HH:mm") , "H:mm") , int(filter(L:L, len(A:A)) - filter(A:A, len(A:A))) & "Day(s), " & text( text(filter(L:L, len(A:A)), "HH:mm") -text(filter(A:A, len(A:A)), "HH:mm") , "H:mm") ) ,iferror(1/0)),"Time Taken"))
=arrayformula( IFERROR(if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Normal")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A1 )),"OVERDUE Normal", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A2 )),"OVERDUE Urgent", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Very Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A3 )),"OVERDUE V. Urgent", IFERROR(1/0)))),countif(if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Normal")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A1 )),"OVERDUE Normal", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A2 )),"OVERDUE Urgent", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Very Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A3 )),"OVERDUE V. Urgent", iferror(1/0)))),"OVERDUE *")& " OVERDUE"))
After several days, we delete all the accumulated old production sheets.
这篇关于刷新单元而不是手动按下ctrl-Shift-E的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
