從此逗號分隔的資料中查找大于和小于 75 的值 100,150,75,50,10,30,250
uj5u.com熱心網友回復：
最好的方法是使用 VBA。該解決方案是微不足道的，所以我會先給出它，我認為不需要解釋。如果您被限制使用公式，我將使用 LET 和 FILTER 為當前 Excel 提供解決方案。我將首先解釋那個，因??為它比沒有它們的解決方案更容易思考。
VBA：
Function getValues(str As String) As String
Dim a As Variant, arr As Variant, buffer As String, comma As String
arr = Split(str, ",")
buffer = ""
comma = ""
For Each a In arr
If a < 75 Then
buffer = buffer & comma & a
comma = ","
End If
Next
getValues = buffer
End Function
這將在逗號分隔串列中回傳所有小于 75 的值。我僅給出 <75 的示例，因為我不確定您的條件是什么，而且我認為這很簡單，您可以將比較調整到任何內容。
在 Excel 365（帶有動態陣列函式）中，假設您的串列在 A1 中：
=LET(
vals, LET(
n, 100,
length, LEN(A1)-LEN(SUBSTITUTE(A1,",","")) 1,
str, SUBSTITUTE(A1, ",", REPT(" ", n)),
container, ROW(OFFSET(A1, 0, 0, length)),
1*MID(str, IF(container=1, 1, (container-1)*100), n)
),
FILTER(vals, vals<75)
)
首先，LET 允許我們定義一組對、一個名稱和一個值（可以是一個公式），然后在公式中使用這些對，也就是最后。我將介紹內部的 LET 函式。
n：這是一個任意大的數字。稍后我們將了解如何使用它。
長度：這是字串中值的數量，通過替換原始字串中的逗號并從原始字串的長度中減去該長度來計算。
str：這是原始逗號分隔的字串，其中逗號替換為 n 個空格。
容器：這里使用OFFSET來構造一個與串列中值的數量相同大小的陣列。引數是參考、行偏移量、列偏移量、[高度]。行和列的偏移量是 0。我們真的不想偏移任何東西，我們只是想回傳一個合適大小的陣列供以后使用。可選引數height是訣竅。我們length從 LET 定義中傳遞它。我使用的參考是 A1，但這并不重要，因為我們不關心這些值。任何單元格參考都可以在這里作業。
陣列構建完成后，我們將 OFFSET 函式包裝在 ROW 中，這將生成一個從 1 到 的數字序列length。
現在，公式：
1*MID(str, IF(container=1, 1, (container-1)*100), n)
What we are going to do here is extract the values from the string that has the commas replace for n spaces. Since we replaced the commas with a sequence of n spaces, we know that each number comes no earlier than 100 times something and is surrounded by spaces, so all we need to pass to MID is the starting points for each extraction: 1, 100, 200, 300, etc., and we will extract n characters at a time.
To get those indexes, we are using the container array we generated earlier. We need to pass the row numbers * 100, but we just have to pass 1 for the very first one, so that we don't give MID a 0 and generate an error.
This will generate the list of all values, but remember that they are padded strings, so we multiply the whole thing by 1 to get them back to numbers. I think you could also use --.
Now take that interior LET and wrap it in another LET so we can assign the output of that formula to another variable (vals) and then we can just use FILTER to return only the values less than 75.
And that is that. If you need these back as a comma separated list, wrape the output of this in TEXTJOIN. One note, eventually the extraction will break down, because the values aren't zero width. In that case you can adjust the size n up OR you could take the average length of the values and compensate for them in the padding. I'm not going to demonstrate that here, but just be aware that you might have to.
There is a way to do this for Excel 2007 up, but I will add that a little bit later. It is ugly as can be. And here's hoping I didn't make any typos copying this all out of Excel. Use the VBA solution if at all possible.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/352400.html
標籤：擅长
下一篇：Excel中的單元格條件格式