从Excel合并列中检索值

Question

我正在对跨越多列的值进行 hlookup。我的数据类似于以下内容：

      A      B      C      D 
  ---------------------------  
1|       Col1          Col2
2|     x      y      z      w
3|
4|

在第3行和第4行（A3、B3、C3、D3等），我想要放置一些公式，这些公式将在工作簿的其他地方执行hlookup操作。关键是，我希望它查找A列和B列的"Col1"，以及C列和D列的"Col2"。 "Col1" 在A1中，但实际上是A1和B1合并的。当我引用A1时，"Col1"会出现，但当我引用B1时，返回值为空白。

有什么想法吗？

- cLFlaVA

---

如果单元格为空，偏移量是否总是-1？ - Fionnuala

在实际的电子表格中，有5个单元格合并在一起。我需要那五列引用合并单元格。明白吗？ - cLFlaVA

Answer 1

以下是另一种解决方案，适用于合并单元格具有不同宽度的情况，下面用一个例子进行说明：

1. 打开一个新的Excel表格，合并单元格B1、C1、D1
2. 在合并单元格中输入 Col1
3. 在B2中，输入公式 =B1，在C2中输入=C1，在D2中输入=D1
4. 应该会看到B2显示为 Col1，而C2、D2都是0
5. 在B3中输入公式=A3，然后复制该公式
6. 右键单击合并单元格B1:D1，选择“粘贴特殊 -> 公式”
7. 你应该会看到合并单元格中显示为0
8. 在合并单元格中输入Col1
9. 现在，所有的B2、C2、D2都应该显示为Col1，也就是说，现在你可以按照预期引用合并单元格了。

如果你有多个不同宽度的合并单元格，只需一次性将公式粘贴到所有的单元格中即可。

这种方法之所以奏效，是因为微软做出了一种特殊的设计选择。似乎当您在合并单元格中粘贴公式时，每个基础单元格都会接收到该公式（相反，如果输入值，则只有左上角的单元格会得到该值）。因此，您可以利用它，并粘贴一个引用其旁边单元格的公式，然后将要覆盖的左上角单元格替换为您想要的值，那么合并单元格下面的每个单元格都将具有该值。

- Martin

---

不错的技巧。简而言之，您可以使用“粘贴公式”将= <左侧单元格>分散到跨度中的每个单元格中，然后在第一个单元格中键入值。 - rkagerer

需要注意的几个小问题：在跨度内插入新列不会像您期望的那样复制相邻的公式（因此您需要重新应用这些技巧）。而且，在2010年之前的Excel版本中存在一个错误，即取消合并单元格，然后单击撤消按钮无法恢复“分散”的公式。 - rkagerer

不错的技巧！在Google Sheets中似乎不起作用。 - Clement

Answer 2

为了访问“Col1”和“Col2”标签，您可以使用以下公式:

=INDEX($1:$1,1,COLUMN()-MOD(COLUMN()-1,2))

注意：这假设您将相同数量的单元格分组在一起。如果是三个单元格，您只需将公式中的最后一个数字更改为3，以此类推。 编辑： 这里是它的工作原理:

INDEX($1:$1,1, x ) 返回第1行，第x列单元格的值。如果您的表格实际上不位于工作表的左上角，您可以将其更改为包含所有合并标签的实际范围。在这种情况下，它将是： INDEX($A$1:$D$1,1, x )

COLUMN() 返回当前单元格的列号（A列中为1，B列中为2等）。

MOD(COLUMN()-1,x) 返回从当前列到包含适当标签的列的偏移量。

- eJames

---

谢谢 :) 我喜欢你的“语法糖” - e.James

如果这个假设不成立，那么James Hole的解决方案更好，因为它是完全通用的。 - AticusFinch

这个函数非常精巧，但出于某些原因，我发现有时它不起作用，而其他时候它可能会进入一个曾经工作过但后来停止的“状态”。（并不是很有帮助，但希望也许别人遇到过类似的异常情况？） - tbone

Answer 3

我已经在VBA中编写了一个简单的函数来解决这个问题： ```html

我已经在VBA中编写了一个简单的函数来解决这个问题：

```

Function mergedText(rngMergedCell As Range)

    If rngMergedCell.MergeCells = True Then
        mergedText = rngMergedCell.MergeArea(1, 1)
    Else
        mergedText = rngMergedCell
    End If

End Function

如果单元格是合并的单元格，函数将返回合并单元格中第一个元素的值 - 这是合并单元格存储其值的位置。

- James Hole

Answer 4

e.James的提议的一个更通用的变体是：

={INDEX($A$1:A1, 1, MAX(NOT(ISBLANK($A$1:A1))*COLUMN($A$1:A1)-COLUMN($A$1)+1))}

这取决于合并单元格除第一个单元格外为空（除非您像马丁的提议一样）。

注意：花括号用于标记数组公式（不要输入它们，只需按alt + return键验证单元格中的公式）。

- Hoel

Answer 5

我意识到自己来晚了，但我找到了一个非常简单的答案。

例如，如果您的标签跨越4列a1:d1，并且如果您引用b1，则会返回“”。要动态查找正确的标签，请在新表中使用此函数：

=if(OriginalTable!B1="",ThisTable!A1,OriginalTable!B1)

我相信您会意识到，当您拖动时，它将捕获e1:h1等范围。这就是全部内容，希望能对某人有所帮助。

- gavin

---

这是一个非常棒的答案，不需要额外的函数/宏等。 - RPh_Coder

Answer 6

有了新的动态引用，现在有更多的选择。这是我编写的一个通用函数，它将搜索单元格左侧并返回第一个值。它没有进行优化，但对我来说已经足够。

=LET(
  TargetCell, A1,
  TargetRow, ROW(TargetCell),
  TargetCol, COLUMN(TargetCell),
  RowReference, INDIRECT(TargetRow & ":" & TargetRow),
  RowValues, TRANSPOSE(FILTER(RowReference,ISBLANK(RowReference)=FALSE)),
  RowValueColumns, MATCH(RowValues, RowReference,0),
  ReturnColumn, MAX(FILTER(RowValueColumns,RowValueColumns<=TargetCol)),
  Return, INDIRECT(ADDRESS(TargetRow,ReturnColumn)),

  Return
)

- Dave Thunes

---

这对我来说是最好的答案，因为它允许合并的大小是可变的。我知道这个问题特别提到了合并列，但也许在底部附上一个注释，说明如何稍作修改以使其适用于行，会更好一些。 - hackerb9

Answer 7

单元格B1和D2中没有任何值，只有A1和C1中有内容。

因此，您需要确保列A和B中的公式都将A1作为查找值，而列C和D中的公式都将C1作为查找值。

- BradC

---

我想避免这种情况，因为实际上有132列由6个合并单元格（6 * 22）组成。我需要一个可复制的公式，以便在公式需要更改时轻松进行。 - cLFlaVA