excel - 如何在Excel中的最后一项之前将单词列表连接成一个带有 "and"的句子？

Question

我想将 Excel 中的单词列表(不是在 VBA 中......在工作表中使用 Excel 公式)加入以下规范:

公式应忽略空单元格。

如果单元格数组中有多个项目，公式应在最后一项之前将单词与“and”连接起来。

如果有两个以上的项目，公式应该在项目之间添加“，”。

例子:
A1=狗
A2=猫
A3=鸟
A4=鱼
结果将是:狗、猫、鸟和鱼

A1=dog
A2=cat
A3=(empty cell)
A4=fish
Result would be: dog, cat, and fish

A1=dog
A2=(empty cell)
A3=bird
A4=(empty cell)
Result would be: dog and bird

A1=dog
A2=(empty cell)
A3=(empty cell)
A4=(empty cell)
Result would be: dog

请问好看吗？我保证我已经搜索并搜索了答案。

编辑:谢谢你，ExcelArchitect，我明白了!这是我第一次使用自定义函数。您可以像使用工作表中的任何其他功能一样使用它!这太棒了。

不要碰运气，但是如果结果中只有一个单词，如何让两个单元格与我的结果连接，如果有多个单词，如何让两个单元格与我的结果连接？示例:如果您为我创建的函数只返回“狗”，我希望它将一个单元格与文本(B1)“我最喜欢穿的衣服是一个”连接起来，然后是“狗”，然后是另一个单元格(B2 ) 上面写着“服装”。造句“我最喜欢穿的衣服是狗装”。但如果它返回不止一种动物，它会像这样连接另外两个单元格:单元格 C1“我最喜欢穿的衣服是”和“狗、猫和鸟”和单元格 C2“服装”。所以它会说“我最喜欢穿的衣服是狗、猫和鸟的服装。”

如果你很好奇，我的数据真的与动物或服装无关。我正在编写一个程序，它将对心理测试进行评分，然后根据测试分数创建一份解释性报告(我是一名心理学家)。

——玛丽安妮

Answer 1

玛丽安妮:

这将是使用 VBA 的好时机!但是，如果您不想这样做，有一种方法可以在没有它的情况下实现您的目标。

您必须在这里考虑所有可能的结果。使用 4 种不同的动物，这意味着您有 15 种结果:



你的等式只需要考虑所有 15 个。它很长，因此很长。因此，如果您想将超过 4 种动物变成短语，则应该走 VBA 路线。

这是我的设置:



A7中的公式如下:

=IF(AND(A2<>"", A3="", A4="", A5=""), A2, IF(AND(A2="", A3<>"", A4="", A5=""), A3, IF(AND(A2="", A3="", A4<>"", A5=""), A4, IF(AND(A2="", A3="", A4="", A5<>""), A5, IF(AND(A2<>"", A3<>"", A4="", A5=""), A2&" and "&A3, IF(AND(A2<>"", A3="", A4<>"", A5=""), A2&" and "&A4, IF(AND(A2<>"", A3="", A4="", A5<>""), A2&" and "&A5, IF(AND(A2="", A3<>"", A4<>"", A5=""),A3&" and "&A4, IF(AND(A2="", A3<>"", A4="", A5<>""), A3&" and "&A5, IF(AND(A2="", A3="", A4<>"", A5<>""),A4&" and "&A5, IF(AND(A2<>"", A3<>"", A4<>"", A5=""), A2&", "&A3&", and "&A4, IF(AND(A2<>"", A3<>"", A4="", A5<>""), A2&", "&A3&", and "&A5, IF(AND(A2<>"", A3="", A4<>"", A5<>""), A2&", "&A4&", and "&A5, IF(AND(A2="", A3<>"", A4<>"", A5<>""), A3&", "&A4&", and "&A5, A2&", "&A3&", "&A4&", and "&A5))))))))))))))

这是通过Excel:



玛丽安妮——我是个 Nerd ，我不得不这样做。这是 VBA 解决方案，您可以拥有任意数量的名称!将此代码粘贴到工作簿中的新模块中(转到 Developer -> Visual Basic，然后插入 -> 新模块，然后粘贴)，然后您可以在工作表中像常规函数一样使用它。只需给它名称所在的范围，您就可以开始了! -马特

Function CreatePhrase(NamesRng As Range) As String
'Creates a comma-separated phrase given a list of words or names
Dim Cell As Range
Dim l As Long
Dim cp As String

'Add commas between the values in the cells
For Each Cell In NamesRng
    If Not IsEmpty(Cell) And Not Cell.Value = "" And Not Cell.Value = " " Then
        cp = cp & Cell.Value & ", "
    End If
Next Cell

'Remove trailing comma and space
If Right(cp, 2) = ", " Then cp = Left(cp, Len(cp) - 2)

'If there is only one value (no commas) then quit here
If InStr(1, cp, ",", vbTextCompare) = 0 Then
    CreatePhrase = cp
    Exit Function
End If

'Add "and" to the end of the phrase
For l = 1 To Len(cp)
    If Mid(cp, Len(cp) - l + 1, 1) = "," Then
        cp = Left(cp, Len(cp) - l + 2) & "and" & Right(cp, l - 1)
        Exit For
    End If
Next l

'If there are only two words or names (only one comma) then remove the comma
If InStr(InStr(1, cp, ",", vbTextCompare) + 1, cp, ",", vbTextCompare) = 0 Then
    cp = Left(cp, InStr(1, cp, ",", vbTextCompare) - 1) & Right(cp, Len(cp) - InStr(1, cp, ",", vbTextCompare))
End If

CreatePhrase = cp
End Function

希望有帮助!
马特，来自 ExcelArchitect.com