需要帮助在单个单元格中获取多个值,并在 Excel 中满足条件 [英] Need Help getting Multiple Values in Single Cell with Condition to be satisfied in excel
问题描述
我需要帮助来获取满足条件的单个单元格中的值.
I need help in getting values in a single cell with the condition to be satisfied.
我想要一个单元格中值小于 95% 的违约者.
I want defaulter who is having a value of less than 95% in a single cell.
例如
推荐答案
如果有动态数组公式FILTER和TEXTJOIN:
If one has the dynamic array formula FILTER and TEXTJOIN:
=TEXTJOIN(CHAR(10),TRUE,FILTER(A2:A7,E2:E7<.95))
确保输出单元格启用了自动换行.
Make sure that the output cell has Wrap Text enabled.
如果有 TEXTJOIN 但没有 FILTER (Excel 2019),则使用此数组公式:
If one has TEXTJOIN but not FILTER (Excel 2019) then use this array formula:
=TEXTJOIN(CHAR(10),TRUE,IF(E2:E7<.95,A2:A7,""))
这需要在退出编辑模式时使用 Ctrl-Shift-Enter 而不是 Enter 来确认.
This would need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
如果两者都没有,则将此代码放在标准模块中:
If neither then place this code in a standard module:
Function TEXTJOINIFS(rng As Range, delim As String, ParamArray arr() As Variant)
Dim rngarr As Variant
rngarr = Intersect(rng, rng.Parent.UsedRange).Value
Dim condArr() As Boolean
ReDim condArr(1 To Intersect(rng, rng.Parent.UsedRange).Rows.Count) As Boolean
Dim i As Long
For i = LBound(arr) To UBound(arr) Step 2
Dim colArr() As Variant
colArr = Intersect(arr(i), arr(i).Parent.UsedRange).Value
Dim j As Long
For j = LBound(colArr, 1) To UBound(colArr, 1)
If Not condArr(j) Then
Dim charind As Long
charind = Application.Max(InStr(arr(i + 1), ">"), InStr(arr(i + 1), "<"), InStr(arr(i + 1), "="))
Dim opprnd As String
If charind = 0 Then
opprnd = "="
Else
opprnd = Left(arr(i + 1), charind)
End If
Dim t As String
t = """" & colArr(j, 1) & """" & opprnd & """" & Mid(arr(i + 1), charind + 1) & """"
If Not Application.Evaluate(t) Then condArr(j) = True
End If
Next j
Next i
For i = LBound(rngarr, 1) To UBound(rngarr, 1)
If Not condArr(i) Then
TEXTJOINIFS = TEXTJOINIFS & rngarr(i, 1) & delim
End If
Next i
TEXTJOINIFS = Left(TEXTJOINIFS, Len(TEXTJOINIFS) - Len(delim))
End Function
然后这样称呼它:
=TEXTJOINIFS(A2:A7,CHAR(10),E2:E7,"<"&0.95)
这篇关于需要帮助在单个单元格中获取多个值,并在 Excel 中满足条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!