如何在 Calc(或 Excel)上定义动态范围?
假设我有一个 Libreoffice.org Calc(也许这也适用于 MS Excel)对象,定义为 $Sheet1.$A$1:$A$4
范围。
我还声明了一个值为 1 的常量。出于此模型的目的,我们将其称为 startingLine
。
这两个对象都在“定义名称”对话框中正确定义(快捷键:Ctrl+F3)。
我想做的是将定义范围的行转换为变量。在我看来,只需将其定义为:$Sheet1.$A$startingLine:$A$4
,但这不起作用。 :-/
我正在寻找一个简单的电子表格解决方案,不幸的是宏这次无法为我做到这一点。不过,使用 R1C1 参考的解决方案就足够了。 :)
任何帮助将非常感激!
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
.
I also have declared a constant with value 1. For this mockup purpose, let's call it startingLine
.
Both objects are properly defined in the Define Names dialog (shortcut: Ctrl+F3).
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. :-/
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. :)
Any help will be greatly appreciated!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您将需要使用此公式的一个版本(excel)
=CELL("内容",INDIRECT(CONCATENATE("A",startingLine)))
这将为您提供单元格 A1 的内容,其中
startingLine
是常量 1。此外,如果我们将endingLine
定义为 A,我们可以替换公式中的 A:=CELL("内容",INDIRECT(CONCATENATE(endingLine,startingLine)))
为您定义范围可能是
=INDIRECT(CONCATENATE("$sheet1.$A$",startingLine,":$A$4"))
例如求和(在Excel中)
=SUM((INDIRECT(CONCATENATE("$A$",startingLine,":$A$4"))))
但我不熟悉定义范围自由办公室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 defineendingLine
as A, we can replace A in the formula:=CELL("contents",INDIRECT(CONCATENATE(endingLine,startingLine)))
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要利用 LibreOffice calc 中列表的结果作为辅助列表的定义,请执行以下操作:
在
DATA>DEFINE RANGE
中定义的第一个列表,例如“早餐”、“晚餐”,其中每个项目都是一个列表包含多行,统称为“早餐”或“晚餐”。详细菜单选项
名为“早餐”的列表的定义可能包括:培根、豆类、鸡蛋、薯饼、蘑菇等……这个列表最好在单独的表格上定义。
如果您想根据“早餐”或“晚餐”的选择从每个列表中选择菜单选项,您需要在 data>validity 中定义一个包含这两个选项的列表。假设这是在单元格
A2
中定义的。顶级选项
将名为“早餐”或“晚餐”的两个详细菜单选项与在两者之间进行选择,请使用公式:
在单元格 C2 中。
当您从 A2 中定义的列表中选择一个选项时,电子表格现在将使用单元格 A2 中的条目在单元格 C2 中提供正确的选项列表。
我希望这是有道理的 - 非常感谢 Waldir Leoncio 的提醒!
To utilise the results of a list in LibreOffice calc as the definition for a secondary list the following works:
First lists defined in
DATA>DEFINE RANGE
e.g. "Breakfast", "Dinner" where each item is a list containing a number of lines and is collectively named as either "Breakfast" or "Dinner".detailed menu choices
the definition of the list named "Breakfast" might include: Bacon, Beans, Eggs, Hash Brown, Mushrooms, etc... this list is best defined on a separate sheet.
If you want to select the menu choices from each list based on the selection of either "Breakfast" or "Dinner" you need a list defined in data>validity with those two options. Say this is defined in cell
A2
.Top level choices
To connect the two detailed menu choices named either "Breakfast" or "Dinner" with the choice between either, use the formula:
in say cell C2.
When you select a choice from your defined list in A2, the spreadsheet will now use the entry in cell A2 to provide the correct list of choices in cell C2.
I hope that makes sense - and a big thanks to Waldir Leoncio for the reminder!