Excel公式根据另一列中的值填充一列 [英] Excel formula to fill a column based on value in another column

问题描述

我该如何制定一个公式，以便在为 ID 的特定值更新 Completed 中的值时，它会自动填充到 Completed的所有单元格中来获取特定的 ID ?而且，当我从 Completed 的一个单元格中删除该值时，它会自动从 Completed 的所有与 ID 中的值相对应的单元格中删除

How can I make a formula such that when update a value in Completed for a particular value of ID, it automatically gets filled in all cells of Completed for that particular ID? And, when I remove the value from one cell in Completed, it automatically gets removed from all cells in Completed that correspond to that value in ID.

例如在下面的数据中，我希望三个空白单元格分别自动填充为 4 ， 6 和 5 .

For eg. in the data below, I'd like the three blank cells automatically filled with 4, 6 and 5 respectively.

Role      ID      Completed
 A         1          3
 A         2          4
 A         5          3
 A         8          6
 B         2          
 B         8
 B        10          5
 C        10           
 C        15          2 

推荐答案

工作表更改解决方案

它做什么?

• 当目标列中的值更改为新值时，在同一 Source Column 中查找同一行的 Source Column .对于每个找到的值，此(找到)行中的值 Target Column (目标列)更改为提到的新值.

• When a value in Target Column is changed to a new value, the value in the same row of Source Column is being looked up in the same Source Column. With each found value, the value in this (found) row in Target Column is changed to the mentioned new value.

用法

• 要成功运行以下代码，必须复制两个代码适当地放在一个工作簿上:第一个是工作表模块，第二个是标准模块.
• 这里没有什么可运行的，一切都会自动运行.
• 唯一可以更改的是短代码中的最后三个值.

• To run the following successfully, both codes have to be copied to one workbook appropriately: the first to a sheet module and the second to a standard module.
• There is nothing to run here, everything runs automatically.
• The only thing that could be changed are the last three values in the short code.

1.工作表模块

以下代码将被复制到工作表模块中，例如 Sheet1

The following code is to be copied into a sheet module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    updateColumn Me, Target, "B", "C", 2
End Sub

• 您可以使用数字 2 和 3 代替"B" 和"C"
• 您可以根据需要更改值.
• 您可以将其复制到多个工作表模块并更改参数用于 SourceColumn ， TargetColumn 和 FirstRow .
• Me 和 Target 保持不变.
• Instead of "B" and "C" you can use the numbers 2 and 3.
• You can change the values as you see fit.
• You can copy it into multiple sheet modules and change the parameters for SourceColumn, TargetColumn and FirstRow.
• Me and Target stay the same.

2.标准模块

以下代码将复制到标准模块中，例如 Module1

The following code is to be copied into a standard module e.g. Module1

Option Explicit

Sub updateColumn(Sheet As Worksheet, _
                 TargetCell As Range, _
                 ByVal SourceColumn As Variant, _
                 ByVal TargetColumn As Variant, _
                 Optional ByVal FirstRow As Long = 4)
    
    If TargetCell.Cells.CountLarge > 1 Then GoTo MoreThanOneCell
    
    Dim rng As Range: Set rng = Sheet.Columns(TargetColumn)
    If Intersect(TargetCell, rng) Is Nothing Then GoTo NotInTargetColumn
    
    Set rng = rng.Find("*", , xlValues, , , xlPrevious)
    If rng Is Nothing Then GoTo EmptyTargetColumn
    If rng.Row < FirstRow Then GoTo FirstRowBelowLastRow
    
    Dim LastRow As Long: LastRow = rng.Row
    Set rng = Sheet.Columns(SourceColumn).Find("*", , xlValues, , , xlPrevious)
    If Not rng Is Nothing Then
        If rng.Row > LastRow Then LastRow = rng.Row
    Else ' Empty Source Column. Don't care.
    End If
    If FirstRow = LastRow Then GoTo OnlyOneCell
    
    Set rng = Sheet.Range(Sheet.Cells(FirstRow, TargetColumn), _
                          Sheet.Cells(LastRow, TargetColumn))
    If Intersect(TargetCell, rng) Is Nothing Then GoTo NotInTargetRange
    
    Dim ColOff As Long: ColOff = Sheet.Columns(SourceColumn).Column - rng.Column
    Dim Target As Variant: Target = rng.Value
    Dim Source As Variant: Source = rng.Offset(, ColOff).Value
        
    Dim i As Long, tVal As Variant, sVal As Variant
    tVal = TargetCell.Value
    sVal = TargetCell.Offset(, ColOff).Value
    Debug.Print TargetCell.Address, tVal, _
                TargetCell.Offset(, ColOff).Address, sVal
    On Error GoTo CleanExit
    For i = 1 To UBound(Source)
        If Source(i, 1) = sVal Then
            Target(i, 1) = tVal
        End If
    Next i
    'Application.EnableEvents = False
    rng.Value = Target
    
CleanExit:
   ' Application.EnableEvents = True
LastExit:
    Exit Sub

MoreThanOneCell:
    'Debug.Print "More than one cell."
    GoTo LastExit
NotInTargetColumn:
    'Debug.Print "Not in Target Column."
    GoTo LastExit
EmptyTargetColumn:
    'Debug.Print "Empty Target Column."
    GoTo LastExit
FirstRowBelowLastRow:
    'Debug.Print "First row below last row."
    GoTo LastExit
OnlyOneCell:
    'Debug.Print "Only one cell."
    GoTo LastExit
NotInTargetRange:
    'Debug.Print "Not in Target Range."
    GoTo LastExit
    
End Sub

您可以取消注释 Debug.Print 行，以监视立即窗口中的 Change事件的行为( CTRL + G )放在 VBE ( Alt + F11 )中.

You can uncomment the Debug.Print lines to monitor the behavior of the Change event in the Immediate window (CTRL + G) in VBE (Alt+F11).

这篇关于Excel公式根据另一列中的值填充一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！