Google Sheets:如何在一步中应用公式后选择所有输出单元格。

huangapple go评论68阅读模式
英文:

Google-Sheets: How to select all output cells after applying formula in 1-Step

问题

我想保留输出数值并在应用公式后删除公式。
为了做到这一点,目前我需要两个步骤:

  1. 手动选择所有输出单元格(CTRL + Shift +向下键)。
  2. 将所有输出单元格复制粘贴为数值。

是否有更快的方法?

英文:

I want to keep output values and remove formula after applying it.
In order to do so, currently I have to steps:

  1. Manually select all output cells(CTRL + Shift + down key) .
  2. Copy-Paste all output cells as values.

Is there more faster way to do so ?

答案1

得分: 1

Although I'm not sure whether this method is included in your expected direct, for example, how about using Google Apps Script?

示例脚本:

请将以下脚本复制并粘贴到电子表格的脚本编辑器中,并保存脚本。然后,请重新打开 Google 电子表格。通过这样做,“示例”自定义菜单将被创建。

function onOpen() {
  SpreadsheetApp.getUi().createMenu("示例")
    .addItem("选择的单元格", "sample1")
    .addItem("所有数据单元格", "sample2")
    .addToUi();
}

function sample1() {
  const range = SpreadsheetApp.getActiveRange();
  range.copyTo(range, { contentsOnly: true });
}

function sample2() {
  const range = SpreadsheetApp.getActiveSheet().getDataRange();
  range.copyTo(range, { contentsOnly: true });
}
  • 当您从自定义菜单中运行“选择的单元格”进行测试时,请选择要使用的单元格,然后运行“选择的单元格”。通过这样做,所选单元格的公式将被删除,只留下数值。

  • 当您从自定义菜单中运行“所有数据单元格”进行测试时,请运行“选择的单元格”。通过这样做,活动工作表中所有数据单元格的公式将被删除,只留下数值。

参考链接:

英文:

Although I'm not sure whether this method is included in your expected direct, for example, how about using Google Apps Script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, please reopen Google Spreadsheet. By this, the custom menu of "sample" is created.

function onOpen() {
  SpreadsheetApp.getUi().createMenu("sample")
    .addItem("selected cells", "sample1")
    .addItem("all data cells", "sample2")
    .addToUi();
}

function sample1() {
  const range = SpreadsheetApp.getActiveRange();
  range.copyTo(range, { contentsOnly: true });
}

function sample2() {
  const range = SpreadsheetApp.getActiveSheet().getDataRange();
  range.copyTo(range, { contentsOnly: true });
}
  • When you test "selected cells" is run from the custom menu, please select the cells you want to use and run "selected cells". By this, the formulas of selected cells are removed and the values are left.

  • When you test "all data cells" is run from the custom menu, please run "selected cells". By this, the formulas of all data cells in the active sheet are removed and the values are left.

Reference:

huangapple
  • 本文由 发表于 2023年4月20日 05:23:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76058913.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定