如何在 Calc(或 Excel)上定义动态范围?

发布于 2025-01-03 11:24:37 字数 405 浏览 2 评论 0原文

假设我有一个 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

游魂 2025-01-10 11:24:37

您将需要使用此公式的一个版本(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 define endingLine 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

甚是思念 2025-01-10 11:24:37

要利用 LibreOffice calc 中列表的结果作为辅助列表的定义,请执行以下操作:

DATA>DEFINE RANGE 中定义的第一个列表,例如“早餐”、“晚餐”,其中每个项目都是一个列表包含多行,统称为“早餐”或“晚餐”。
详细菜单选项​​

名为“早餐”的列表的定义可能包括:培根、豆类、鸡蛋、薯饼、蘑菇等……这个列表最好在单独的表格上定义。

如果您想根据“早餐”或“晚餐”的选择从每个列表中选择菜单选项,您需要在 data>validity 中定义一个包含这两个选项的列表。假设这是在单元格 A2 中定义的。
顶级选项

将名为“早餐”或“晚餐”的两个详细菜单选项​​与在两者之间进行选择,请使用公式:

=indirect(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:

=indirect(A2)

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!

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文