Excel 如何用公式提取另一单元格数值中没有包含给定数值中数值？ excel中有怎么从一个单元格中提取出不包含另一个单元格中的...

为方便设计公式，结果数据分别放到不同的列，如图：

C1用公式：

=IFERROR(INDEX(TRIM(MID(SUBSTITUTE($B1,",",REPT(" ",200)),(ROW($1:$100)-1)*200+1,200)),SMALL(IF(MMULT(N(TRIM(MID(SUBSTITUTE($B1,",",REPT(" ",200)),(ROW($1:$100)-1)*200+1,200))=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",200)),(COLUMN($A:$CV)-1)*200+1,200))),ROW($1:$100)^0)=0,ROW($1:$100)),COLUMN(A1))),"")

数组公式，需要按组合键（CTRL+SHIFT+回车）完成公式，右拉，下拉。

如何用公式提取另一单元格数值中没有包含给定数值中数值。

写了个比较麻烦的

'自定义公式-按ALT+11-点击插入-点击模块

Function 对比(a As String, b As String)

c = Split(a, ",", -1, 1)

d = Split(b, ",", -1, 1)

For i = 0 To UBound(d)

For j = 0 To UBound(c)

If j = 0 Then m = 0

If d(i) = c(j) Then m = m + 1

If m > 0 Then Exit For

Next j

If m = 0 Then t = t & "," & d(i)

Next i

对比 = Right(t, Len(t) - 1)

End Function

使用的时候 =MyExcept(A1,B1)

按alt+f11进入VBA，右键插入模块，贴以下代码

Function MyExcept(data1 As range, data2 As range) As String

arr1 = data1.Value
arr2 = data2.Value

arr1 = Replace(arr1, " ", "")
arr1 = "," & arr1 & ","

arr2 = Replace(arr2, " ", "")

temp = ""
arr2 = Split(arr2, ",")

For Each j In arr2
Debug.Print j
If j <> "" And InStr(arr1, "," & j & ",") = 0 Then
temp = temp & j & ","
End If
Next

If Len(temp) > 0 Then temp = Left(temp, Len(temp) - 1)
MyExcept = temp

End Function

=TEXTJOIN(",",,IF(ISERR(FIND(","&TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),ROW(1:50)*99-98,99))&",",","&A1&",")),TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),ROW(1:50)*99-98,99)),""))

数组公式三键结束。

textjoin是office2019的函数，如果版本低，请先安装一个高版本的。

如果B1内容就是3-18，公式可以简化为=TEXTJOIN(",",,IF(ISERR(FIND(","&ROW(3:18)&",",","&A1&",")),ROW(3:18),""))

EXCEL中某一单元格的值由另外两个单元格计算得出，当其中一个单元格没有数据时，如何不进行计算？~

设C1=A1+B1。
C1公式为： =IF(OR(A1="",B1=""),"",A1+B1)

=SUBSTITUTE(A1,B1,"")