如何用另一个单元格锁定动态生成的单元格的内容 [英] how to lock content of a dynamically generated cell with another cell
问题描述
背景
我喜欢使用
替代问题
解决这个问题的另一种方法可以是每次更改数据表时(由于用户地图的更改)..可以将新数据集转储到新数据表(即data-v2)中，并且另一个估算表可以创建(即estimate-v2).这将具有商业意义，因为它向客户显示了估算的变化.在这种情况下，如何简单地将估算从 estimate 复制并粘贴到 estimate-v2 并仍将它们与适当的估计值联系起来?
简短回答
无法将手动输入的值直接锁定到公式结果行.而是使用后端/前端架构类型.
广泛的说明
后端表.创建一张要用作镜子"的 Cardboard 吧！数据和工程师在电子表格中输入的估算值(一个后端，两个前端)
1.1 备选方案 1:使用脚本更新来自两个源的数据值.
1.2 备选方案 2:使用公式连接来自两个来源的数据值.前端工作区.使用此表手动输入时间估算数据.
2.1 为了更轻松地保持数据同步，请包含 Cardboard 中的 id 吧！CSV 文件(假设这是一个持久 ID)
- 2.1.1 您可以隐藏此列以保持当前布局的外观.
2.2 从后端表传递数据
- 2.2.1 如果您继续使用公式，请冻结"数据以防止手动输入估计值的数据仅通过复制和粘贴值而错位.这可以通过使用 UI 命令、键盘快捷键或脚本来完成.
备注
如果您将在会议上使用电子表格，我认为时间驱动的触发器或自定义菜单比编辑触发器更好，因为运行脚本需要时间以避免在会议期间分心.
如果您想保留修订版本，请在每次导入 CSV 文件或召开估算审核会议时制作一份电子表格副本.保留副本作为修订快照并继续处理原始文件.这是因为 Google 表格修订历史记录可能有太多详细信息，无法轻松跳转到主要修订，但如果您需要进行详细的历史修订，这可能会有所帮助.
稍后您可以使用 IMPORTRANGE 将所有修订估计放在一起，即制作一张估计随时间变化的图表.
基于公式的实现示例.
起点
假设您有两个电子表格- 数据:它保存从第一次导出到 CSV 文件的数据.- 估计:它包含屏幕截图中显示的布局.
步骤 1
将 id 值添加到估算表中.假设它作为 D 列插入.
步骤 2
在 Cardboard it! 上对纸板进行更改后，下载并导入一个新的 CSV 文件，假设这个新表称为 data-v2.为简单起见，在本例中，它被添加到同一个电子表格中.
步骤 3
在data-v2!L1中加入以下公式
<块引用>=ArrayFormula(ARRAY_CONSTRAIN({{"frontend","backend"};IFERROR(VLOOKUP(B2:B,{'estimate-v1'!D:D,'estimate-v1'!E:F},{2,3},FALSE()),)},counta(A:A),2000000))
评论公式
=ArrayFormula(//告诉 Google Sheets 非数组函数应该像数组一样工作函数，结果应该扩展到所需的区域(行/列)ARRAY_CONSTRAIN(//将结果限制为所需的行数.{//开始一个新的数组{前端"，后端"}；//带有列标题的数组IFERROR(//如果出错将返回空白查找(B2:B,//具有查找值的范围.{'estimate-v1'!D:D,'estimate-v1'!E:F},//数组.考虑到这是我的首选符号那个 ID 列将来可以移动，但此时它可以被替换通过'估计-v1'！D:F{2,3},//这使得 VLOOKUP 返回一个包含两列的数组.第一列是用第二列的值填充，该列用来自第二列的值填充第三栏.FALSE()//这告诉 VLOOKUP 第二个参数没有排序.),)},counta(A:A),//这将结果行限制为所需的行(丢弃空白行)2000000//一个很大的数字(它是最大的单元格数)))步骤 4
- 为新的估算审核创建一个新表.
将以下公式添加到 A1
=QUERY('data-v2'!A2:M,"select C,E,D,B,L,M where A='card' and E<>'white' order by I,J标签 C 'name', E 'color', D 'notes', B 'id', L '前端', M '后端' ",0)全选，复制并粘贴为值.
- 应用格式和公式来计算估计总数.
纸板吧！CSV 标头
- 亲切
- id
- 姓名
- 说明
- 颜色
- 状态
- 尺寸
- 注释
- x
- y
- image_url
另见
参考文献
background
I like to use story mapping to gather requirements from clients (and to create estimates for them) using cardboardit.com. I'm currently making a sample estimate for a project that I have completed in the past (http://vibereel.com/).
Cardboardit offers the ability to dump data into a csv file, which can been in raw form in the data tab in this sheet
The data tab is pretty raw, so i used this formula to make it look nicer in the estimate tab:
=QUERY(data!A2:J,"select C,E,D where A='card' and E<>'white' order by I,J label C 'name', E 'color', D 'notes' ",0)
I then ask engineers to fill in the estimates in the rows. Engineers fill in the estimates under the other two columns (front end and backend)..
problem
The problem is when I realise that I missed some elements in the user map. So I adjust the user map, export the data once again and dump it in the data sheet. considering that the above formula is smart, the estimate sheet gets updated immediately and accurately.
However the problem is that the values the engineers entered are now misaligned with the new values.. Ie a front end task called "edit vibereel" previously had an estimate of 1 day, but once the new data got entered.. that one got misplaced
question
how can I do this so that
- whenever engineers enter a value under the two columns, those values become locked, or linked to the tasks they were originally intended for under the name column.
alternative question
Another way of solving this can be that each time the data sheet is changed (due to changes in user map).. the new data set can be dumped into a new data sheet (ie data-v2), and another estimate sheet can be created (ie estimate-v2). this would make business sense as it shows the client the change in estimates.. In that case how would it be possible to simply copy and paste the estimates from estimate to estimate-v2 and still link them to the appropriate estimates?
Short answer
It's not possible to directly lock manually entered values to formula resulting rows. Instead use a backend/frontend architecture kind.
Broad instructions
Backend table. Create a sheet to be used as a "mirror" of the Cardboard it! data and of the estimates entered by the Engineers on the spreadsheet (One backend, two frontends)
1.1 Alternative 1: Use scripts to update the data values from the two sources.
1.2 Alternative 2: Use formulas to join the data values from the two sources.Frontend workspace. Use this sheet for manual data input of the time estimates.
2.1 To make easier keep data synced, include the id from the Cardboard it! CSV file (assuming that this is a persistent ID)
- 2.1.1 You could hide this column to keep the look of your current layout.
2.2 Pass data from the backend table
- 2.2.1 If you keep using formulas, "freeze" the data to prevent data that manually entered estimates be misaligned by copy and paste values only. This could be done by using UI commands, keyboard shortcuts or a script.
Remarks
If you will be using the spreadsheet on meetings, I think that a time-driven trigger or a custom menu are better than a on edit trigger due to time that it take to run the script to avoid distractions during the meeting.
If you want to keep revisions, make a copy of the spreadsheet each time you import a CSV file or have estimates review meeting. Keep the copy as revision snapshot and keep working on the original file. This is because the Google Sheets revision history could have too many details to make easy to jump to a major revision, but could be helpful if you need to do a detailed history revision.
Later you could use IMPORTRANGE to put together all the revision estimates, i.e. to make a chart of the estimates change overtime.
Implementation Example based on formulas.
Start point
Let say that you have two spreadsheets - data : It holds the data from the first export to a CSV file. - estimates : It holds the layout shown on the screen shot.
Step 1
Add the id values to the estimates sheet. Let say that it's inserted as Column D.
Step 2
After changes were made to the cardboard on Cardboard it!, download and import a new CSV file, let say that this new sheet is called data-v2. For simplicity, on this example it's added on the same spreadsheet.
Step 3
Add the following formula to data-v2!L1
=ArrayFormula(ARRAY_CONSTRAIN({{"frontend","backend"};IFERROR(VLOOKUP(B2:B,{'estimate-v1'!D:D,'estimate-v1'!E:F},{2,3},FALSE()),)},counta(A:A),2000000))
Commented formula
=ArrayFormula( // Tells Google Sheets that non array functions should work as array
functions and that the result should be expanded to the required area (rows/columns)
ARRAY_CONSTRAIN( // Limit results to the required number of rows.
{ // Starts a new array
{"frontend","backend"}; // Array with the column headers
IFERROR( // In case of error will return a blank
VLOOKUP(
B2:B, // Range with the values look for.
{'estimate-v1'!D:D,'estimate-v1'!E:F}, // Array. This is my preferred notation considering
that that the ID column could be moved in the future, but at this time it could be replaced
by 'estimate-v1'!D:F
{2,3}, // This makes that VLOOKUP returns a array with two columns. The first column is
filled up with values from the second column, the column is filled up with values from the
third column.
FALSE() // This tells VLOOKUP that the second argument is not sorted.
),)
},
counta(A:A), // This limits the resulting rows to those that are required (discards blank
rows)
2000000 // A big number (it's the maximun number of cells)
))
Step 4
- Create a new sheet for the new estimates review.
Add the following formula to A1
=QUERY('data-v2'!A2:M,"select C,E,D,B,L,M where A='card' and E<>'white' order by I,J label C 'name', E 'color', D 'notes', B 'id', L 'front end', M 'backend' ",0)Select all, copy and paste as values.
- Apply formatting and formulas to calculate the estimates totals.
Cardboard it! CSV headers
- kind
- id
- name
- description
- color
- status
- size
- annotation
- x
- y
- image_url
See also
References
这篇关于如何用另一个单元格锁定动态生成的单元格的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
