在 Excel(2013)中使用命名范围时,如何显示整个范围,但偏移一个单元格?

在 Excel(2013)中使用命名范围时,如何显示整个范围,但偏移一个单元格?

$A$2:$G$2我有一个名为 的范围Distances;稍后在工作表中,我想用B11:H11原始范围中的数据填充该范围。我可以从 单元格开始A11并输入=Distances,它将从 填充值A2。使用填充柄,我可以填充 的范围A11:G11,但是只要我尝试将范围放入B11:H11,值就会直接转换。(例如,只有第 2 列的值会转换到第 2 列,即使我希望第 1 列(范围的开始)中的值填充第 2 列(引用的开始)中的引用单元格。)我也无法从第 2 列(例如 )开始范围引用=DistancesB2获取与关联列的值不同的引用值。

我如何才能从工作表中的其他位置引用该范围,并让第一个引用单元格显示命名范围内第一个单元格的值?这可能吗?

编辑: 为了清楚起见,我添加了以下可视化效果,尽管@excellll 的答案已经很轻松地解决了这个问题……

如果我的命名范围是下面的 abdf 部分,并且我想显示相同的 abdf 值,但从 开始X,我该怎么做?

+-+-+-+-+-+-+-+
|a|b|d|f| | | |
+-+-+-+-+-+-+-+
| | | | | | | |
+-+-+-+-+-+-+-+
| | |X| | | | |
+-+-+-+-+-+-+-+
| | | | | | | |
+-+-+-+-+-+-+-+

@excellll 的回答表明解决方案涉及使用数组公式。

答案1

一种解决方案是将对命名范围的引用输入为数组公式。为此,请选择整个所需输出范围(B11:H11在您的示例中)。在不更改选择的情况下,键入公式

=Distances

然后按Ctrl+ Shift+ Enter。这会将公式输入到整个选定范围内,并会给出你想要的结果。

答案2

选项1: 您可以使用 INDEX 按行和列返回指定范围内的任何值。因此,在您的示例中,在 B11(或任何地方)中,您可以使用:

=INDEX(距离,1,1)

获取第一行和第一列的值。但这对于粘贴来说并不理想。因此,请使用当前列号并调整起始位置:

=INDEX(距离,1,COLUMN()-10)

这将顺利地复制过去。-10 是由于从第 11 列开始而进行的调整(在您的示例中)。

选项 2:您尝试过使用隐式交集方法,仅使用“=Distances”,该方法假设您想要查找该范围与当前单元格的行或列相交的位置(它只会与一行或一列相交,或者不相交)。相反,请尝试使用明确的交集,您指定两个范围,用空格分隔(这就是您不能在范围名称中使用空格的原因)。它们都可以是命名范围,或者在您的情况下,只需在 B11 中使用:

=距离 A:A

这将使 A2:G2 中的距离范围与 A 列(而不是公式所在的 B 列)相交。当然,这将顺利地横向或纵向复制。

相关内容