公式计算中的 BaseRow / BaseColumn 到底有什么用？

收藏

本帖最后由 Ellia.Duan 于 2023-11-29 11:22 编辑

背景：

有用户在设置公式时，会遇到baseRow，baseColumn这两个参数的设置，有时候不太明白，好像输入（0，0）没有什么影响，输入当前要执行公式计算的单元格的（row,col）也对公式值没有什么影响。在筛选，数据验证，条件格式中设置公式后，为什么引用的单元格一直在变？那么该如何正确使用baseRow，baseColumn以及这两个参数到底有什么用呢？本文将带着这些问题去了解baseRow，baseColumn。

一、BaseRow,BaseColumn
当使用R1C1模式，公式将转为相对引用。例如=R[1]C[1], 表示的引用当前单元格个右下角偏移1的单元格。
如下图所示：
image.png38157958.png

A1单元格引用了R[1]C[1] ,即B2的值。
在上述例子中，“当前单元格”A1就是所谓的baseRow，baseColumn。
初步理解之后，我们再来看一个例子：

let spread = designer.getWorkbook();
let sheet = spread.getActiveSheet();
sheet.setValue(3,3,1);
sheet.setValue(4,4,2)
sheet.setValue(5,5,3)

复制代码

我们分别为D4,E5,F6 设置了值。
接下来使用evaluateFormula函数执行公式“=R[1]C[1]”，传入不同的baseRow，baseColumn。

console.log(GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet,'=R[1]C[1]',2,2,true))
console.log(GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet,'=R[1]C[1]',3,3,true))
console.log(GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet,'=R[1]C[1]',4,4,true))

复制代码

结果是什么呢？按照上面的理解，公式'=R[1]C[1]' 的基准行列是（2,2），所以查找以（2,2）为当前单元格的右下角一个的单元格数值，即单元格行列为（3,3）的值，即为1 。
image.png314963988.png

结果分别为1，2，3。同一个公式，不同的baseRow，baseColumn，返回的值不同。

二、EvaluateFormula
理解了baseRow， baseColumn后，我们再来理解下evaluateFormula这个API。
image.png49306334.png

如上图所示：evaluateFormula()可以传入5个参数，分别是上下文sheet，公式表达式formula ，基准行列baseRow,baseColumn及是否是R1C1模式useR1C1
我们再来看一个例子：
4ea9b8c629140d2b240cfbb1170468e.png308045348.png

4ea9b8c629140d2b240cfbb1170468e.png308045348.png

在上图中，执行相同的公式”Rank(2,"A1:A3",0)"，不同的baseRow和baseColumn。但是公式结果是一样的。

综上所述：按照上文的解释，如果设置useR1C1为true ，则baseRow和baseColumn需要设置为当前单元格，公式计算结果才会正确。
否则设置useR1C1为false，则baseRow和baseColumn没有实际意义。

除此之外，还有一个特殊的用例：table公式计算。
image.png494395446.png

如上图所示，有一个起始位置为(4,1)的表格，现在对其第一列和第二列进行公式计算。
如下面的代码

console.log(GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, "[@column1] + [@column2]", 0, 0))

复制代码

按照上面的内容，给baseRow和baseColumn设置了0 ，但是控制台却报错了：
image.png524849547.png

如果执行下面的这行代码

console.log(GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, "[@column1] + [@column2]", 5, 1))

复制代码

发现结果正确，所以这里的baseRow,baseColumn需要基于所在行列的起始位置。
image.png77373656.png

三、Filter
在SpreadJS中如何实现excel的高级筛选功能，在这篇文章中有这么几行公式

var formulaCondition = new spreadNS.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.formulaCondition, {
formula: "=COUNTIF($B$11:$B$13,A1)",
customValueType: GC.Spread.Sheets.ConditionalFormatting.CustomValueType.formula
});

复制代码

上述代码是对A1-A7进行筛选，判断是否存在B11-B13的值，但是为什么公式中仅提到了A1,没有设置区域A1~A7呢？
这是因为在range中执行公式时，公式会根据range，调整baseRow和baseColumn，你看到的是A1，其实在Range中每个单元格自行执行的时候，就会变为A1~A7。
如果上述公式写为："=COUNTIF($B$11:$B$13,A1:A7)"，那么含义是，在A1:A7中找，只要有B11:B13就返回true，那么筛选的结果就全是true。
所以在Filter中，当设置一个公式时，他会根据range，内部计算baseRow，baseColumn。

四、DataValidation
用数据验证实现联动效果，关于如何实现，可以参考这篇文章：利用数据验证实现级联效果
这里有一行代码是：

var dv1 = GC.Spread.Sheets.DataValidation.createFormulaListValidator("=INDIRECT(A2)");
sheet.setDataValidator(1, 1, 5,1,dv1,GC.Spread.Sheets.SheetArea.viewport);

复制代码

在B2-B6单元格这个区域中设置公式数据验证"=INDIRECT(A2)" ,但是在实际中，此时点击B3单元格，查看其数据验证，发现其公式为”=INDIRECT(A3)“。 image.png556210021.png

所以在DataValidation ，当设置一个公式时，如同Filter，会根据range，内部计算baseRow，baseColumn。

此外，还有conditional format ,也适用于这一规则，这里将不再详细介绍。

五、custom name
在customName中设置formula为”Sheet1!$A$1“，此时customName的baseRow， baseColumn没有意义，因为$A$1是绝对位置。
但是在customName设置formula为”Sheet1!A1“时，customName的baseRow，baseColumn就是必须的。
如设置这行代码

sheet.addCustomName("动物","=Sheet1!A1", 1, 1);

复制代码

上述代码设置baseRow， baseColumn为B2，所以在B2单元格设置公式“=动物”,就会出现A1单元格的值。因为会根据baseRow， baseColumn算出A1的偏移。
但是如果此时点击任意一个单元格，输入公式"=动物" , 然后打开名称管理器去看那个custom name的公式。。发现不停在变。如下动图所示：
image.png563581847.png

所以，在custom name中，如果是绝对定位，baseRow， baseColumn没有意义。
如果是相对定位，baseRow， baseColumn是当前actieCell 。
关于相对引用与绝对引用，可以查看这篇文章：https://www.jianshu.com/p/245e9eee78e7

		自动登录	找回密码
密码			立即注册

公式计算中的 BaseRow / BaseColumn 到底有什么用？

0 个回复

SpreadJS 认证

SpreadJS 高级认证