如何在 Calc(或 Excel)上定义动态范围? [英] How to define dynamic ranges on Calc (or Excel)?

问题描述

假设我有一个 Libreoffice.org Calc(也许这也适用于 MS Excel)对象定义为 $Sheet1.$A$1:$A$4 范围.

Let's say I have a Libreoffice.org Calc (maybe this goes for MS Excel too) object defined as the range $Sheet1.$A$1:$A$4.

我还声明了一个值为 1 的常量.为了这个模型，我们称之为 startingLine.

I also have declared a constant with value 1. For this mockup purpose, let's call it startingLine.

这两个对象都在定义名称"对话框中正确定义(快捷键:Ctrl+F3).

Both objects are properly defined in the Define Names dialog (shortcut: Ctrl+F3).

我想做的是将定义范围的行变成变量.在我看来，只需像这样定义它:$Sheet1.$A$startingLine:$A$4，但这不起作用.:-/

What I would like to do is to turn the lines of the defined range into variables. In my mind, all it'd take would be to define it like this: $Sheet1.$A$startingLine:$A$4, but this doesn't work. :-/

我正在寻找一个简单的电子表格解决方案，不幸的是，这次宏不会为我做这件事.不过，使用 R1C1 参考的解决方案就足够了.:)

I'm looking for a simple spreadsheet solution, unfortunately macros won't do it for me this time. A solution using R1C1 reference would be good enough, though. :)

任何帮助都会非常感谢！

推荐答案

你需要使用这个公式的一个版本(excel)=CELL("contents",INDIRECT(CONCATENATE("A",startingLine)))
这将为您提供单元格 A1 的内容，其中 startingLine 是一个常数 1.此外，如果我们将 endingLine 定义为 A，我们可以在公式中替换 A:
=CELL("contents",INDIRECT(CONCATENATE(endingLine,startingLine)))

You will need to use a version of this formula (excel) =CELL("contents",INDIRECT(CONCATENATE("A",startingLine)))
This gives you the contents of cell A1 where startingLine is a constant 1. Additionally, if we define endingLine as A, we can replace A in the formula:
=CELL("contents",INDIRECT(CONCATENATE(endingLine,startingLine)))

为您定义一个范围可能是 =INDIRECT(CONCATENATE("$sheet1.$A$",startingLine,":$A$4"))
例如求和(在 excel 中) =SUM((INDIRECT(CONCATENATE("$A$",startingLine,":$A$4")))) 但我不熟悉自由办公室

To define a range for you might be =INDIRECT(CONCATENATE("$sheet1.$A$",startingLine,":$A$4"))
for instance to sum (in excel) =SUM((INDIRECT(CONCATENATE("$A$",startingLine,":$A$4")))) but I am unfamiliar with the defining ranges in libreoffice

这篇关于如何在 Calc(或 Excel)上定义动态范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！