Excel 2016:根据另一张工作表中的匹配条件用列表填充列

Excel 2016:根据另一张工作表中的匹配条件用列表填充列

我尝试用最好的措辞来表达标题,并会尽力解释。如果需要,我会接受任何关于重新命名标题的建议。

我有两张工作表,我想用它们来跟踪运动员参加某项运动的轮次。它们本质上是相同的数据,只是呈现方式不同。

在一张表中,我有 12 行,每行代表一名球员,球员的姓名在 B 列。 

每行有 11 列,每列代表游戏的每一轮。

例如,

================================================
|  A  |    B    |    C    |   ...   |    M     |
================================================
| No. | Player  | Round 1 |   ...   | Round 11 |
------------------------------------------------
|  1  | Male 1  |    I    |         |    I     |
|  2  | Male 2  |    I    |         |    I     |
|  3  | Male 3  |    I    |    I    |          |
|  4  | Male 4  |    I    |    I    |          |
|  5  | Male 5  |    I    |    I    |    I     |
|  6  | Male 6  |         |    I    |    I     |
|  7  | Male 7  |         |    I    |    I     |
|  8  | Female1 |    I    |         |          |
|  9  | Female2 |    I    |    I    |          |
|  10 | Female3 |    I    |    I    |    I     | 
|  11 | Female4 |         |    I    |    I     |  
|  12 | Female5 |         |         |    I     |

仅限 5男性每轮游戏最多可玩 3 人。女性玩家可以进行一轮游戏。

因此,如果玩家参加,我会在适用回合的列中输入“I”,如果他们不参加该回合,则输入空白。

在另一张表中,我有一些行代表“入场”的球员和“出场”的球员。然后我有一些列代表比赛的回合。

不过,在这些单元格中,我目前正在手动输入(复制/粘贴)该轮次入场或出场的球员的姓名。

================================================
|  A  |    B    |    C    |   ...   |    M     |
================================================
| No. | Status  | Round 1 | Round 2 | Round 3 |
|-----------------------------------------------
| 1   |   IN    | Male 1  | Male 3  | Male 1  |
| 2   |   IN    | Male 2  | Male 4  | Male 2  |
| 3   |   IN    | Male 3  | Male 5  | Male 6  |
| 4   |   IN    | Male 4  | Male 6  | Male 7  |
| 5   |   IN    | Male 5  | Male 7  | Male 7  |
| 6   |   IN    | Female1 | Female2 | Female3 |
| 7   |   IN    | Female2 | Female3 | Female4 |
| 8   |   IN    | Female3 | Female4 | Female5 |
| 9   |   OUT   | Male 6  | Male 1  | Male 3  |
| 10  |   OUT   | Male 7  | Male 2  | Male 4  |
| 11  |   OUT   | Female4 | Female1 | Female1 |
| 12  |   OUT   | Female5 | Female5 | Female2 |

如何根据第一张表的行和状态自动为第二张表的列填充球员姓名?

编辑:

更新了我的问题,以提供有关我正在使用的数据的更精确的详细信息。

编辑:

在示例中添加了列名。

答案1

我能够用两个“辅助矩阵”做到这一点。“辅助单元格”是从输入数据中导出/计算一些中间值的单元格。然后,该中间值将在另一个单元格中使用,以产生所需的结果。在表格数据表中有一个“辅助列”是很常见的。我期待看到是否有人可以简化我的答案并使其更优雅,但这是我在 59 分钟内能想出的最好的答案。

可以将辅助工具与实际数据放在同一张工作表中,放在不同的列或行中,通常是隐藏的。或者你可以把它们放在不同的工作表中。由于这个答案需要两个,我将说明这两种技术;然后你可以选择始终使用其中一种技术。

我假设你的源数据(在你的问题中呈现)在Sheet1!A2:D8,标题在第 1 行。输入

=IF(B2="", "blank", B2)

进入单元格 AB2,然后向下拖动/填充AB8至 列 的右侧 AD。结果为

   |    AA    |    AB    |    AC    |    AD    |
---+----------+----------+----------+----------+
 1 |          |          |          |          |
 2 |          |   I      |   blank  |   I      |
 3 |          |   I      |   blank  |   I      |
 4 |          |   I      |   I      |   blank  |
 5 |          |   I      |   I      |   blank  |
 6 |          |   I      |   I      |   I      |
 7 |          |   blank  |   I      |   I      |
 8 |          |   blank  |   I      |   I      |

我相信无需解释。

现在变得更加复杂。创建Sheet3并输入以下公式:

  • B2=MATCH("I", Sheet1!AB$2:AB$8, 0)
  • B3=MATCH("I", OFFSET(Sheet1!AB$2,B2,0):Sheet1!AB$8, 0) + B2
  • B7=MATCH("blank", Sheet1!AB$2:AB$8, 0)
  • B8=MATCH("blank", OFFSET(Sheet1!AB$2,B7,0):Sheet1!AB$8, 0) + B7

将单元格 B3向下拖/填充至B6。如果您更改方案,导致同时有超过两名“出局”玩家,则需要将单元格 B8向下适当拖/填充。然后,与之前一样,将 列 拖/填充 B至 列 右侧 D。您应该得到:

   |    A    |    B    |    C    |    D    |
---+---------+---------+---------+---------+
 1 |         |         |         |         |
 2 |         |      1  |      3  |      1  |
 3 |         |      2  |      4  |      2  |
 4 |         |      3  |      5  |      5  |
 5 |         |      4  |      6  |      6  |
 6 |         |      5  |      7  |      7  |
 7 |         |      6  |      1  |      3  |
 8 |         |      7  |      2  |      4  |

解释:

  • B2I 包含范围 中第一个单元格的(相对)位置Sheet1!AB$2:AB$8。这是 1,因为Sheet1!AB2包含I,并且它是该范围内的第一个单元格。类似地,C2和分别D2包含31,因为Sheet1!AC4Sheet1!AD2(第三个和第一个单元格,分别对应于 Player3 和 Player1)是这些列中第一个单元格的位置I

  • 正在查看B3OFFSET(Sheet1!AB$2,B2,0)OFFSET(Sheet1!AB$2,1,0),这相当于Sheet1!AB3。所以这MATCH是在查看范围Sheet1!AB3:AB8。这MATCH也会返回1,因为Sheet1!AB3包含I,并且它是中的第一个单元格范围。然后我们将B2( 1) 添加到该范围,得到2,这是第二 ISheet1!AB2:AB8

  • 类似地,MATCH单元格中的 D4 正在查看范围Sheet1!AD4:AB8。这MATCH将返回3,因为Sheet1!AD6是 中的第一个单元格包含 的范围I。添加D3( 2) 可得到5,因为该行对应于 Player5。

  • 然后我们在第 7 行和第 8 行做同样的事情,只寻找blank

    我并不需要Sheet1!AA:AD辅助矩阵,但显然你不能用它MATCH("", …)来查找空白单元格。

现在我们可以顺坡滑行并=INDEX(Sheet1!$A$2:$A$8, Sheet3!B2)投入Sheet2!B2

+--------+---------+---------+---------+
| Status | Round 1 | Round 2 | Round 3 |
+--------+---------+---------+---------+
| IN     | Player1 | Player3 | Player1 |
| IN     | Player2 | Player4 | Player2 |
| IN     | Player3 | Player5 | Player5 |
| IN     | Player4 | Player6 | Player6 |
| IN     | Player5 | Player7 | Player7 |
| OUT    | Player6 | Player1 | Player3 |
| OUT    | Player7 | Player2 | Player4 |

与上述对应的截图

答案2

这个 VBA 代码可以解决问题

Public Sub players_rounds()
    first_sheet = "Sheet1"
    second_sheet = "Sheet2"
    Dim wkb As Workbook
    Dim wk1 As Worksheet
    Dim wk2 As Worksheet
    Set wkb = ThisWorkbook
    Set wk1 = wkb.Worksheets(first_sheet)
    Set wk2 = wkb.Worksheets(second_sheet)
    wk1_lastColumn = wk1.Cells(1, Columns.Count).End(xlToLeft).Column
    wk1_lastRow = wk1.Cells(Rows.Count, 1).End(xlUp).Row
    count_in = 0
    For i = 2 To wk1_lastRow
        If wk1.Cells(i, 2) = "I" Then count_in = count_in + 1
    Next i
    wk2.Cells.Clear
    wk2.Rows(1).value = wk1.Rows(1).value
    wk2.Cells(1, 1).value = "Status"
    count_out = wk1_lastRow - count_in - 1

    For i = 2 To count_in + count_out + 1
        If i <= count_in + 1 Then
            wk2.Cells(i, 1).value = "IN"
        Else
            wk2.Cells(i, 1).value = "OUT"
        End If
    Next i

    For i = 2 To wk1_lastRow
        thisplayer = wk1.Cells(i, 1)
        For j = 2 To wk1_lastColumn
            playervalue = wk1.Cells(i, j)
            playerround = wk1.Cells(1, j)
            If playervalue = "I" Then
                firstrow = 2
                lastrow = count_in + 1
            Else
                firstrow = count_in + 2
                lastrow = count_in + count_out + 1
            End If
            For k = 2 To wk1_lastColumn
                If wk2.Cells(1, k) = playerround Then
                    For m = firstrow To lastrow
                        If wk2.Cells(m, k) = "" Then
                            wk2.Cells(m, k) = thisplayer
                            m = lastrow
                            k = wk1_lastColumn
                        End If
                    Next m
                End If
            Next k
        Next j
    Next i
End Sub

使用Alt+打开 VBA / Macros F11,单击ThisWorkbook右键单击并插入在其下创建一个模块。将代码粘贴到右侧。

检查变量的值first_sheet是否second_sheet与工作表的名称匹配。

每当您执行此宏时,它都会根据第一张表上的值更新第二张表。

为了使它更加自动魔法双击宏,双击第一个工作表,在右侧选择工作表改变

让它看起来像这样:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call players_rounds
End Sub

现在,每次您在第一个表中进行更改时,第二个表就会更新。

相关内容