blocks|key|2847027|text|今天的答案是数据菜单下的“命名和保护范围”。工作很顺利。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|entityMap^0^^$0|@$1|2|3|4|5|6|7|C|8|@]|9|@]|A|$]]]|B|$]]

The answer as per today is "Named and protected ranges" under Data menu. Works smoothly.

blocks|key|1016542|text|目前无法锁定给定的单元格。但是，您可以在电子表格中创建新的工作表并锁定这些工作表。如果你真的想阻止她改变公式，你可以把所有的结果/公式都放在第二张纸上，引用第一张表的数据，然后锁上第二张表，这样它就只能读给她了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1016543|为了引用另一个工作表中的数据，请使用语法sheetname!range。例如，要取第2行到第10行中1‘S列A的平均值，可以使用Average(sheet1!A2:A10)。|offset|length|style|CODE|1016544|若要添加工作表，请使用电子表格右下角的%2B符号。您可以使用电子表格左下角的选项卡和下拉箭头在工作表之间切换并重命名工作表。|1016545|📷|atomic|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://i.stack.imgur.com/118wy.png|imageAlt^0|0|K|F|1S|M|0|J|1|0|0|1|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]|$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|10|8|@$D|11|E|12|F|G]]|9|@]|A|$]]|$1|J|3|K|5|L|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S|T|-4]]]]

It is not currently possible to lock a given cell. However, you can create new sheets in the spreadsheet and lock those. If you really want to prevent her from changing the formulas, you could have all of the results/formulas be in a second sheet, referring to the first sheet's data, then lock the second sheet so it would be read-only to her. 

In order to reference data from one sheet in another, use the syntax <code>sheetname!range</code>. For example, to take the average of sheet1's column A in rows 2 through 10, you would use <code>Average(sheet1!A2:A10)</code>. 

To add sheets, use the <code>+</code> sign on the bottom right side of the spreadsheet. You can switch between and rename sheets using the tabs and dropdown arrows on the lower left side of the spreadsheet.

<a href="https://i.stack.imgur.com/118wy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/118wy.png" alt="google docs screenshot"/></a>

blocks|key|1016569|text|我发现，如果您创建了一个附加了不应该编辑的单元格的注释，当他们将鼠标悬停在单元格上编辑时，您可以让它警告某个人。当然，它并不能真正锁住手机，但希望有足够的时间看到纸条弹出，也许你妈妈会停止手动更改这些号码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1016570|我认为一次只能添加一个单元格的注释，因此取决于有多少单元格是公式，这个解决方案可能是不可行的。|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|F|8|@]|9|@]|A|$]]]|D|$]]

I found out that if you create a comment attached the cells that should not be edited, you can have it warn someone when they hover the mouse over the cell to edit it. It, of course, doesn't actually lock the cell, but hopefully, enough times seeing the note pop up and maybe your mom will stop manually changing those numbers.

I think you can only add comments one cell at a time, so depending on how many cells are formulas, this solution may not be viable.

blocks|key|2846972|text|您可以使用google应用程序脚本在javascript中编写自己的锁定机制。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2846973|例如：|2846974|假设B列有一个公式，它等于A%2B+1列的值。如果我们看单元格(B3)，它应该有一个"=A3%2B1“的公式。|2846975|要编写脚本以防止修改，您可以使用脚本编辑器编写类似的内容：|2846976|function+onEdit(event)
{
++var+ss+=+SpreadsheetApp.getActiveSpreadsheet();
++var+sheet+=+SpreadsheetApp.getActiveSheet();
++var+CellRow+=+SpreadsheetApp.getActiveRange().getRow();
++var+CellColumn+=+SpreadsheetApp.getActiveRange().getColumn();

++if+(CellColumn+==+2){
++++sheet.getRange(CellRow,+CellColumn).setFormula("=A"%2BCellRow%2B"%2B1");
++++Browser.msgBox("DON'T+MESS+WITH+THE+FORMULAS!");
++}
}|code-block|syntax|javascript|2846977|如果每次有人编辑B列中的任何单元格时，它都能正常工作，脚本将自动将公式放回其中，并给出警告消息。|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|T|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|U|8|@]|9|@]|A|$]]]|O|$]]

You can use google apps script to write your own locking mechanism in javascript.

For example:

Lets say you have a formula in column B and it is equal to the value in column A + 1. If we look at cell (B3) it should have a formula in it of "=A3+1"

To write a script to protect this from modification you would write something like this using Script Editor:

<pre><code>function onEdit(event)
{
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = SpreadsheetApp.getActiveSheet();
 var CellRow = SpreadsheetApp.getActiveRange().getRow();
 var CellColumn = SpreadsheetApp.getActiveRange().getColumn();

 if (CellColumn == 2){
 sheet.getRange(CellRow, CellColumn).setFormula("=A"+CellRow+"+1");
 Browser.msgBox("DON'T MESS WITH THE FORMULAS!");
 }
}
</code></pre>

If you get it working every time someone edits any cell in column B the script will automatically put your formula back in and give your a warning message.

blocks|key|1016661|text|解决办法之一是使用数据验证。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1016662|转到“数据”菜单，单击“验证”.，然后定义需要“保护”的单元格范围。例：SheetA!F2:F9999.然后选择条件:文本，等于。然后在方框中输入类似于"=*“而不带引号的内容。取消选中“允许无效数据.”盒。保存。|offset|length|style|CODE|1016663|这应该只允许用户输入一个以=符号开头的值(这是要保护的公式的开始)。如果他们输入=符号，那么他们可以修改那个单元格。|1016664|星号允许=后面的任何内容都是有效的。您可以进一步指定它以更多地保护它:+Ex:+Text、equals、=sum(*)或=AVG(B*:D*)等等。|entityMap^0|0|10|F|0|0|1G|7|1O|B^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@$D|O|E|P|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Q|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|R|8|@$D|S|E|T|F|G]|$D|U|E|V|F|G]]|9|@]|A|$]]]|L|$]]

A workaround this is to use data validation.

Go to Data menu, click validation..., then define your range of cells that you need to "protect". Ex: <code>SheetA!F2:F9999</code>. Then select Criteria: Text, equals. Then enter into the box something like: "=*" without the quotation marks. Uncheck the "allow invalid data..." box. Save.

This should only let user enter a value that begins with = sign (which is the beginning of your formula to protect). If they enter the = sign then they can modify that cell. 

The asterisk allows anything after the = to be valid. You could specify it further to protect it more: Ex: Text, equals, <code>=sum(*)</code>, or <code>=AVG(B*:D*)</code>, etc...

blocks|key|1016586|text|试着把她应该输入文字的盒子变成不同的颜色，告诉她只在彩色的盒子里写字。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|entityMap^0^^$0|@$1|2|3|4|5|6|7|C|8|@]|9|@]|A|$]]]|B|$]]

Try making the boxes where she is supposed to be entering the text a different colour and tell her to write only in the coloured boxes.

blocks|key|1016710|text|Excel遇到相同问题时，从后台得到的简单解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1016711|另一种方法是通过数据验证来做到这一点。在填充了所需内容的单元格之后，|1016712|选择数据验证并使用自定义选项。对于公式，输入=""|header-two|offset|length|style|CODE|1016713|然后选择“拒绝输入”。|entityMap^0|0|0|M|3|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]]|M|$]]

Simple solution from back when Excel had the same problem:

Another way you can do this is through Data Validation. After you have a cell populated with the desired contents, 

Choose Data Validation and use the Custom option. For the formula enter <code>=""</code>

Then select "Reject input".

blocks|key|1016684|text|在Microsoft中，我可以锁定整个工作表，然后选择性地解锁单个单元格或范围。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1016685|在Google电子表格中，我将所有单元格锁定在一个或多个单独的范围内，只有其他用户可以输入的单元格除外。参见我今天制作的Google工作表的示例：示例|offset|length|1016686|Excel的一个不同之处是，在Google表中，所有锁定的单元格都有灰色对角线条作为背景。|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.google.com/spreadsheets/d/10usRU-YTrwIymMTyQ-7EWqu5X1CPGAZ9u0jIe2lblCY/edit#gid=0^0|0|21|2|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]]|H|$I|$5|J|K|L|A|$M|N]]]]

In Microsoft Excel I can lock the entire sheet and then selectively unlock individual cells or ranges.

In Google Spreadsheets, I lock all cells in one or more separate ranges except the cells that are to be available for input by other users. See this example of a Google sheet I made today: <a href="https://docs.google.com/spreadsheets/d/10usRU-YTrwIymMTyQ-7EWqu5X1CPGAZ9u0jIe2lblCY/edit#gid=0" rel="nofollow">sample</a> 

One variation from Excel is that all in the Google sheet all locked cells have grey diagonal stripes as background.

I created very simple spreadsheet application in Google Spreadsheet for my mom. But I really need to lock/protect the cell with formulas so she cannot edit them. Every time I told her to try new versions she overwritten the formulas.

Any idea how to approach that from technical point of view?

I searched the net and it seems to me that it is not doable right now in Google Apps.

How to lock/protect cells in Google Spreadsheets

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我在Google电子表格中为我妈妈创建了非常简单的电子表格应用程序。但是我真的需要用公式锁定/保护这个单元格，这样她就不能编辑它们了。每次我让她尝试新版本时，她都会改写公式。你知道如何从技术的角度来处理这个问题吗？我搜索了一下网络，在我看来，它现在在Google应用程序中是不可行的。

问如何锁定/保护Google电子表格中的单元格
EN

回答 8

Web Applications用户

Web Applications用户

Web Applications用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何锁定/保护Google电子表格中的单元格EN

回答 8

Web Applications用户

Web Applications用户

Web Applications用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何锁定/保护Google电子表格中的单元格
EN