在Excel中的最后一项之前，如何将单词列表连成“和”句子？
我想加入Excel中的单词列表(而不是在VBA中.在工作表中使用Excel公式)，其规格如下：
公式应忽略空单元格。
如果单元格数组中有多个项，则公式应该将最后项之前的单词与"and“连接起来。
公式应该添加"，“之间的项目，如果有两个以上的项目。
例如: A1=dog A2=cat A3=bird A4=fish结果将是:狗、猫、鸟和鱼。
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)。“我最喜欢穿的是狗服。”但是如果它返回不止一个动物，它会连接另外两个像这样的细胞: Cell C1“我最喜欢穿的东西是”和“狗，猫，鸟”和细胞C2的“服装”。所以它会说：“我最喜欢穿的东西是狗、猫和鸟的服装。”
如果你好奇的话，我的数据和动物和服装没有任何关系。我正在编写一个程序，该程序将对心理测试进行评分，然后根据测试成绩创建一个解释性报告(我是一名心理学家)。
-Mary安妮
回答 4
Stack Overflow用户
发布于 2015-03-07 13:45:39
玛丽·安妮：
现在是使用VBA的好时机！但如果你不愿意，有一种方法可以实现你的目标没有它。
你必须在这里说明所有可能的结果。四种不同的动物，这意味着你有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：
玛丽·安妮-我是个书呆子，不得不这么做。这是VBA解决方案，您可以有任意多的名字！将此代码粘贴到工作簿中的一个新模块中(转到Developer -> Visual，然后插入->新模块并粘贴)，然后您可以像常规函数一样在工作表中使用它。只要给出名字的范围，你就可以去了！-Matt
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
Stack Overflow用户
发布于 2015-03-07 18:32:32
VBA更简单。公式非常复杂，因为Excel没有本机函数，不允许将范围连在一起。但是，考虑到您已经编写了多达8只动物，根据您的规则将A1:A8的内容连在一起的公式是可以做到的。可以在明显位置的公式中更改这些位置。
我做了一个修改:我可能错了，但我相信英语规则表明应该省略上一次and之前的逗号，所以我这样做了。如有必要，可以加进去。编辑：进一步调查揭示了美国和英国规则的不同:美国规则是按照你的要求，英国规则省略逗号之前的连词。我将修改公式和UDF以符合美国的公约。
在公式中，修改是将comma放置在and之前。UDF中的变化也是很小的。
该公式是由下列序列构造的：
因此，把这些公式加在一起，只指A1:A8，我们最终得到了这个怪物：
=SUBSTITUTE(IFERROR(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","))-2),",",",and ",LEN(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","))-2))-LEN(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","))-2),",",""))),MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","))-2)),",",", ")下面是一个VBA解决方案，它将允许任意数量的项；它按照与上面相同的规则进行连接。
Option Explicit
Function ConcatRangeWithAnd(RG As Range, Optional Delim As String = ", ")
Dim COL As Collection
Dim C As Range
Dim S As String
Dim I As Long
Set COL = New Collection
For Each C In RG
If Len(C.Text) > 0 Then COL.Add C.Text
Next C
Select Case COL.Count
Case 0
Exit Function
Case 1
ConcatRangeWithAnd = COL(1)
Case 2
ConcatRangeWithAnd = COL(1) & " and " & COL(2)
Case Else
For I = 1 To COL.Count - 1
S = S & COL(I) & ", "
Next I
ConcatRangeWithAnd = S & "and " & COL(COL.Count)
End Select
End FunctionStack Overflow用户
发布于 2020-11-26 06:39:55
使用新的TEXTJOIN函数，可以很容易地做到这一点。
步骤1:将TEXTJOIN函数与", "分隔符一起使用，并将ignore_empty设置为TRUE。这将为您提供逗号分隔、串连的字符串，而忽略空白值。
步骤2:使用COUNTA函数计算列表中非空白条目的数目。并从中减去1。此时，您可能希望使用MAX函数将值降为1。
步骤3:使用SUBSTITUTE函数将第2步中计算的逗号的最后一个实例替换为一个" and "。
把这一切结合在一起：
=SUBSTITUTE(TEXTJOIN(", ",TRUE,A1:A14),", "," and ",MAX(1,COUNTA(A1:A14)-1))在上面的公式中插入任何您想要的范围，而不是A1:A14，您将得到一个逗号分隔连接和一个和在最后一个字之前。
https://stackoverflow.com/questions/28915165
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号