当合并单元格出现时，Excel公式=Ax&Bx&Cx&Dx

I'm working with big table with structure similar to picture below, but more columns(A-AZ) and really more rows. And for some reason I need to get whole row into one string, like you can see as "Expected result", but I'm getting really bad data. Do you know how to get right results? Without VBA, if possible

我正在处理大表，它的结构与下面的图片类似，但是更多的列(A-AZ)和更多的行。出于某种原因，我需要把整行放到一个字符串中，就像你看到的“预期结果”一样，但我得到的数据非常糟糕。你知道如何得到正确的结果吗?没有VBA,如果可能的话

OK, I don't know how to close this question, but I'm closing it by this. I'm using merged cells, because it's required to use them, and everybody here yells, that merged cells are evil so this question is closed as unsolvable.

好吧，我不知道如何结束这个问题，但是我用这个来结束它。我使用合并的单元格，因为使用它们是必须的，这里的每个人都大声说，合并的单元格是邪恶的，所以这个问题是无法解决的。

3 个解决方案

#1

To expand on the comments from mattdeak and Brad, the issue is that cells A3:A6 are merged. As a result, the value "Lamp" is actually only in cell A3, and cells A4:A6 are blank. Therefore, J4 sees A4="" and B4="", giving you the indicated result (only the value in C4).

为了扩展mattdeak和Brad的评论，问题是A3:A6被合并。因此，“Lamp”的值实际上只存在于cell A3中，而cell A4:A6是空的。因此，J4看到A4=""和B4="" "，给出指定的结果(只有C4中的值)。

The easiest way, as the commenters noted, is to unmerge the cells and copy all the data into each row. If your cells have to stay merged, I recommend the approach used in this answer. Here's how it works:

正如评论者指出的那样，最简单的方法是取消单元格的合并，并将所有数据复制到每一行中。如果您的单元格必须保持合并，我推荐在这个答案中使用的方法。它是如何工作的:

Pick a space where you can add a second set of Table columns. I'll use CA since you said you have columns A-AZ.
选择一个可以添加第二组表列的空间。我用CA，因为你说你有A-AZ列。
In CA3, enter the formula =IF(ISBLANK(A3),CA2,A3).
在CA3中，输入公式=IF(ISBLANK(A3)，CA2,A3)。
Fill right from CA3 through DZ3. DZ3 should thus refer to AZ3.
从CA3到DZ3填充。因此，DZ3应指AZ3。
Fill down CA3:DZ3 for as many rows as you have. At this point. CA3:DZ<last row> is a copy of your table with everything filled in.
填写CA3:DZ3和你所拥有的一样多。在这一点上。CA3:DZ <最后一行> 是填充了所有内容的表的副本。
Update the formula to be =CA3 & CB3 & ... for however many columns you need to merge. Use the values from CA:DZ and you should be OK!
更新公式为=CA3 & CB3 &…无论需要合并多少列。使用CA:DZ的值，您应该可以!

#2

If you can use a little VBA, you can create a User Function in a public module as follow:

如果您可以使用一个小的VBA，您可以在公共模块中创建一个用户函数，如下所示:

Public Function MergedValue(r As Range) As Range
    Application.Volatile
    If Not r.MergeCells Then
       Set MergedValue = r
    Else
       Set MergedValue = r.MergeArea.Cells(1, 1)
    End If
End Function

Then replace all =Ax & Bx & Cx ... formulas by =MergedValue(Ax) & MergedValue(Bx) & MergedValue(Cx) ...

然后替换所有=Ax & Bx和Cx…由=MergedValue(Ax)和MergedValue(Bx)和MergedValue(Cx)得出的公式

Edit: Added Application.Volatile at the start

编辑:添加应用程序。开始时挥发性

Edit 2: Without using Application.Volatile

编辑2:不使用Application.Volatile

Public Function MergedValue(r As Range, RangeToCheck as range) As Range
    If Not r.MergeCells Then
       Set MergedValue = r
    Else
       Set MergedValue = r.MergeArea.Cells(1, 1)
    End If
End Function

Call it with =MergedValue(A1,$A$1:$C$3) for example if you want to check cells in range [A1:C3].

例如，如果要检查范围内的单元格，可以使用=MergedValue(A1，$ 1:$C$3)。

#3

-1

=concat(offset(A3,-mod(row()+3,6),,4)) & concat(offset(B3, -mod(row()+1,2),,2)) & C3 & concat(offset(D3,-mod(row()+3,6),,4)) & concat(offset(E3, -mod(row()+1,2),,2)) & concat(offset(F3, -mod(row()+1,2),,2))

to get A column: in j3, -mod(row()+3,6) = 0. so concat(offset(A3,0,0,4)) = A3&A4&A5&A6 = A3.

要获取列:在j3中，-mod(行()+3,6)= 0。所以concat(偏移量(A3, 0,0,0,4) = A3& a4 & a5 & a6 = A3。

to get B column: in j3, -mod(row()+1,2) = 0. so cancat(offset(B3,0,0,2)) = B3&B4 = B3.

要得到B列:在j3中，-mod(行()+1,2)= 0。所以cancat(偏移量(b3,0,0,2) = B3& b4 = B3。

And so on D, E, F column.

然后是D E F列。

There is another method (writed on 2016/6/20)

还有一种方法(写于2016/6/20)

step 1.Select A3 cell and press Ctrl + A.

步骤1。选择A3单元，按Ctrl + A。

step 2.Then press Ctrl + 1 (The "1" key with "!", Not the "1" in number pad)

步骤2。然后按Ctrl + 1(“1”键为“!”)，而不是数字本中的“1”)

step 3.Select "Alignment" tag, disable "Merge Cells"

步骤3。选择“对齐”标签，禁用“合并单元格”

step 4.Then "Home" -> "Find & Select" -> "Go to special" -> select "Blanks" (the short cut key sequence is: Alt -> h -> f -> d -> s -> k -> Enter)

步骤4。然后“Home”->“Find & Select”->“Go to special”->选择“Blanks”(快捷键序列为:Alt -> h -> f -> d -> s -> k -> Enter)

step 5.The active cell be B4, then write =b3 and press ctrl + Enter

第5步。激活单元为B4，然后写入=b3并按ctrl + Enter。

step 6.Then in J3 cell write down: =CONCATENATE(A3,B3,C3,D3,E3,F3) or =CONCAT(A3:F3)

步骤6。然后在J3单元格中写入:=连接(A3、B3、C3、D3、E3、F3)或=CONCAT(A3:F3)

#1

Pick a space where you can add a second set of Table columns. I'll use CA since you said you have columns A-AZ.
选择一个可以添加第二组表列的空间。我用CA，因为你说你有A-AZ列。
In CA3, enter the formula =IF(ISBLANK(A3),CA2,A3).
在CA3中，输入公式=IF(ISBLANK(A3)，CA2,A3)。
Fill right from CA3 through DZ3. DZ3 should thus refer to AZ3.
从CA3到DZ3填充。因此，DZ3应指AZ3。
Fill down CA3:DZ3 for as many rows as you have. At this point. CA3:DZ<last row> is a copy of your table with everything filled in.
填写CA3:DZ3和你所拥有的一样多。在这一点上。CA3:DZ <最后一行> 是填充了所有内容的表的副本。
Update the formula to be =CA3 & CB3 & ... for however many columns you need to merge. Use the values from CA:DZ and you should be OK!
更新公式为=CA3 & CB3 &…无论需要合并多少列。使用CA:DZ的值，您应该可以!

#2