我尝试用最好的措辞来表达标题,并会尽力解释。如果需要,我会接受任何关于重新命名标题的建议。
我有两张工作表,我想用它们来跟踪运动员参加某项运动的轮次。它们本质上是相同的数据,只是呈现方式不同。
在一张表中,我有 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 |
解释:
B2
I
包含范围 中第一个单元格的(相对)位置Sheet1!AB$2:AB$8
。这是1
,因为Sheet1!AB2
包含I
,并且它是该范围内的第一个单元格。类似地,C2
和分别D2
包含3
和1
,因为Sheet1!AC4
和Sheet1!AD2
(第三个和第一个单元格,分别对应于 Player3 和 Player1)是这些列中第一个单元格的位置I
。正在查看
B3
—OFFSET(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
,这是第二I
在Sheet1!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
现在,每次您在第一个表中进行更改时,第二个表就会更新。