根据行中其他单元格的内容在Excel 2007中填充单元格 [英] Fill cells in Excel 2007 based on content of other cells in row

问题描述

我有一个包含四列（V1 V2，V3 V4）的Excel 2007电子表格，其中包含用逗号分隔的值（整数[0-20]）。值不必是连续的。



我想创建一个新的列（例如newV），其单元格只包含一个整数，如果它包含在该行的前四列中的至少两列中。这可以通过Excel中的公式或宏来完成吗？如果是这样的话呢？谢谢。

解决方案

如果每个值只能在每个单元格中出现一次：

 函数IfAtLeast（rng As Range，num As Integer）
 Const SEP As String =，
 Dim c As Range，d As Object，arr ，我As Long，tmp，k 
 Dim rv As String 
 rv =
设置d = CreateObject（scripting.dictionary）
对于每个c在rng 
 arr = Split（c.Value，SEP）
对于i = LBound（arr）到UBound（arr）
 tmp = Trim（arr（i））
如果不是d.exists （tmp）然后d.Add tmp，0 
d（tmp）= d（tmp）+ 1 
下一个i 
下一个c 
对于每个k在d.keys 
如果d（k）> = num则rv = rv& IIf（Len（rv）> 0，，，）& k 
下一个k 
 IfAtLeast = rv 
结束函数
  

用法：

  = IfAtLeast（A2：D2,2）
  

编辑：如果您遇到麻烦的UDF问题，那么使用子

来调试问题通常比较容易

  Sub Tester（）
 debug.print IfAtLeast（Activesheet.Range（A2：D2），2）
 End Sub 
  




I have an Excel 2007 spreadsheet populated with four columns (V1 V2, V3 V4) that contain values (integers [0-20]) separated by a comma. Values need not be continuous.

I would like to create a new column (e.g. newV) whose cells only contains an integer if it is included in at least two of the previous four columns from that row. Can this be done with a formula or macro within Excel? If so what would be a good way to approach this? Thank you.

解决方案

If each value can only appear once in each cell:

Function IfAtLeast(rng As Range, num As Integer)
    Const SEP As String = ","
    Dim c As Range, d As Object, arr, i As Long, tmp, k
    Dim rv As String
    rv = ""
    Set d = CreateObject("scripting.dictionary")
    For Each c In rng
        arr = Split(c.Value, SEP)
        For i = LBound(arr) To UBound(arr)
            tmp = Trim(arr(i))
            If Not d.exists(tmp) Then d.Add tmp, 0
            d(tmp) = d(tmp) + 1
        Next i
    Next c
    For Each k In d.keys
        If d(k) >= num Then rv = rv & IIf(Len(rv) > 0, ",", "") & k
    Next k
    IfAtLeast = rv
End Function

Usage:

=IfAtLeast(A2:D2,2)

EDIT: if you're having problems trouble-shooting a UDF then it is often easier to debug the issue using a sub

Sub Tester()
    debug.print IfAtLeast(Activesheet.Range("A2:D2"), 2)
End Sub

这篇关于根据行中其他单元格的内容在Excel 2007中填充单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！