Excel中的唯一范围不会返回唯一记录，但会返回所有其他记录 [英] Unique range in Excel doesn't return the unique record but all other records

问题描述

我在Excel中的唯一值有问题.

我使用了该查询的建议:

除了我尝试过的模块之外，还创建了名称管理器，在其中输入了以下公式:

  = OFFSET(Sheet1！$ U $ 3,0,0，COUNTA(Sheet1！$ U:$ U)-2,1) 

然后在V16单元格中分配了另一个包含此名称的公式

  = IFERROR(INDEX(List，MATCH(0，COUNTIF($ V $ 23:$ V23，List)，0))，") 

但不幸的是，我得到的是空白单元格.

我该如何解决这个问题?在V15单元格中，我只想拥有一个字符串，而不是N/A + N/A + N/A.

解决方案

请在模块中复制下一个函数:

 函数UniqueStr(rng作为范围)作为字符串Dim El As Variant对于每个El Inng.Value如果InStr(El，"N/A")= 0，则UniqueStr = El:退出函数下一个结束功能 

但是，如果在处理范围内有多个有效地址"，则该函数将仅返回第一个...

然后，在您需要接收返回的字符串的单元格中编写一个公式:

  = uniqueStr(U3:U6) 

在编写函数名称后，您可以使用光标选择需要处理的范围.

如果需要使用数组函数，则有两种可能性:

如果需要它增加要处理的行的每个范围(我的意思是如果第一个公式将分析"U3:U6"，则下一个行公式将处理"U4:U7".该函数将使用相对引用.

如果需要所有具有数组公式的单元格来处理相同的"U3:U6"字符串，请在您编写的公式中选择它，然后按 F4 .这样，该函数将使用绝对引用...看起来像这样:

  = uniqueStr($ U $ 3:$ U $ 6) 

I have got a problem with the unique value in Excel.

I used the advice from this query:

Extract unique value from the range in Excel

used the following module:

 Public Function unikue(rng As Range)
Dim arr, c As Collection, r As Range
Dim nCall As Long, nColl As Long
Dim i As Long
Set c = New Collection

nCall = Application.Caller.Count

On Error Resume Next
    For Each r In rng
        c.Add r.Text, CStr(r.Text)
    Next r
On Error GoTo 0
nColl = c.Count


If nCall > nColl Then
    ReDim arr(1 To nCall, 1 To 1)
    For i = 1 To nCall
        arr(i, 1) = ""
    Next i
Else
    ReDim arr(1 To nColl, 1 To 1)
End If

For i = 1 To nColl
    arr(i, 1) = c.Item(i)
Next i

unikue = arr
End Function

And typed the following formula in the cell V15:

 {=UNIKUE(U3:U6)}

Unfortunately instead of an address, which I want I am getting the prevailing N/A+N/A+N/A values, which I want to get rid of.

I want to have only the address string (bounded red) appearing in the V15 column.

Apart from the module I tried also created the name manager, where I input this formula:

=OFFSET(Sheet1!$U$3,0,0,COUNTA(Sheet1!$U:$U)-2,1)

and next allocated another formula including this name in the cell V16

       =IFERROR(INDEX(List,MATCH(0,COUNTIF($V$23:$V23,List),0)),"")

but unfortunately, I am getting the blank cell instead.

How can I solve this problem? In the cell V15, I want to have the only one string, which is other than N/A+N/A+N/A.

解决方案

Please copy the next function in a module:

Function UniqueStr(rng As Range) As String
   Dim El As Variant
   For Each El In rng.Value
    If InStr(El, "N/A") = 0 Then UniqueStr = El: Exit Function
   Next
End Function

But, if you will have more then one "address valid" in the processed range, the function will return only the first one...

Then, write a formula in the cell you need to receive the returned string:

=uniqueStr(U3:U6)

After writing the function name, you can select with the cursor the range you need to be processed.

If you need to use an array function, there are two possibilities:

If you need it to increment each range to be processed row (I mean if the first formula will analize "U3:U6", the next row formula will process "U4:U7". The function will use relative reference.

If you need that all the cells having the array formula to process the same "U3:U6" string, please select it in the formula you wrote and press F4. In this way the function will use Absolute reference... It will look like this:

=uniqueStr($U$3:$U$6)

这篇关于Excel中的唯一范围不会返回唯一记录，但会返回所有其他记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！