Excel公式根据另一列中的值填充一列

本文介绍了Excel公式根据另一列中的值填充一列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我该如何制定一个公式，以便在为 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).