可以“跳过"阵列的数组公式行数 [英] arrayformula that can "skip" rows

问题描述

我需要在Google电子表格中引入功能，该功能允许用户编辑数组公式的结果.要求的原因是，ARRAYFORMULA为一组单元格设置了默认值，但是用户有时需要覆盖这些默认值.我想知道这是否遥不可及.

I need to introduce functionality into a google spreadsheet that will allow the user to edit the result of an array formula. The reason for the requirement is that an ARRAYFORMULA sets a default value for a group of cells, but the user sometimes needs to overwite these defaults. I'd like to know if this is even remotely possible.

示例:

Row(#)|Array_1 |Array_2
------------------------------------
 1    |a       |=arrayformula(Array_1)
 2    |b       |""
 3    |c       |""
 4    |d       |""

因此，Array_2中的所有行均由数组公式填充.但是，用户希望直接转到Array_2中的第二个单元格并更改其值.当然，根据设计，ARRAYFORMULA会损坏.有什么方法可以修改ARRAYFORMULA，以便它可以跳过用户编辑的单元格并继续前进，就好像什么都没有完成一样.

So all rows in Array_2 are populated by an array formula. However the user wants to go directly to the second cell in Array_2 and change its value. Of course, by design ARRAYFORMULA will break. Is there some way to modify ARRAYFORMULA, so that it will simply skip over the cell that the user has edited and continue on its way as if nothing has happeded?

推荐答案

我意识到这是一个老问题，但是我今天正在寻找这个问题，并制作了一个对我有用的脚本.

I realize this is an old problem but I was searching for this today and made a script that works for me.

在第二列中编辑单元格时，此脚本会将公式放在相邻的单元格中.这样，如果您需要手动输入某些内容，而无需事先将公式放入所有行中，则可以覆盖公式.在大多数情况下，我会不小心编辑公式并将其弄乱，因此对我来说效果更好.

This script puts a formula in an adjacent cell when a cell is edited in the second column. This way you can just overwrite the formula if you need to input something manually and you don't need to have the formulas go into all of the rows beforehand. I had people accidentally edit the formula and mess it up most of the time when they were pre-filled, so this works better for me.

function onEdit() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetList = ["Sheet1","Sheet2","Sheet3"]; // list of sheets to run script on

    for (i = 0; i < sheetList.length; i++) {

        var sheetName = ss.getSheetByName(sheetList[i]);

        // only runs if sheet from sheetList is found
        if (sheetName != null) {

            var aCell = sheetName.getActiveCell();
            var col = aCell.getColumn();
            var adjacentCell = aCell.offset(0, -1);
            var formula = 'INPUT FORMULA HERE'; // put the formula you want in the adjacentCell here. Don't use it in an arrayformula

            // only runs if active cell is in column 2, if the adjacentCell is empty, and if the active cell is not empty(otherwise it runs if you delete something in column 2)
            if(col==2 && adjacentCell.getValue()=="" && aCell.getValue()!="") {

                adjacentCell1.setValue(formula);
            }
        }
    }
}

这篇关于可以“跳过"阵列的数组公式行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！