Excel/R矩阵操作：将胜负邻接矩阵转换为净胜矩阵

Got it, let's break down how to turn your win-count adjacency matrix into a net win matrix using either Excel or R. First, let's confirm the logic: the net win value for Person X vs Person Y is X's wins over Y minus Y's wins over X. That's exactly what your expected output shows—for example, Steve vs Joe is 2 (Steve's wins) minus 8 (Joe's wins) = -6, which matches the sample.

Excel Method #

Let's assume your original data is in cells A1:E5 (A1 is "Loser/Winner", A2:A5 are the names, B1:E1 are the names, and B2:E5 are the win counts). Here's how to build the net win matrix:

• Set up the new matrix structure: Copy the row and column headers to a new area (e.g., A7:E11) so you have the same "Loser/Winner" labels and names.
• Diagonal values: These are always 0 (since someone can't win against themselves). You can either manually type 0 in cells like B8, C9, D10, E11, or use a formula to auto-fill:

  =IF($A8=B$7, 0, "")
  

  Drag this across all diagonal cells to populate the zeros.
• Non-diagonal values: For any cell (e.g., B9 which is Joe vs Steve), use this formula to calculate net wins:

  =INDEX($B$2:$E$5, MATCH($A9, $A$2:$A$5, 0), MATCH(B$7, $B$1:$E$1, 0)) - INDEX($B$2:$E$5, MATCH(B$7, $A$2:$A$5, 0), MATCH($A9, $B$1:$E$1, 0))
  

  • The first INDEX/MATCH grabs how many times the row name (Joe) beat the column name (Steve).
  • The second INDEX/MATCH grabs how many times the column name (Steve) beat the row name (Joe).
  • Subtract the two to get the net win.
• Fill the matrix: Drag the formula across all non-diagonal cells, and you'll get your expected net win matrix.

R Method #

If you prefer using R for data manipulation, this is super straightforward—no manual dragging needed. Here's the code:

# Create your original win-count matrix as a data frame
win_data <- data.frame(
  Loser_Winner = c("Steve", "Joe", "Chan", "Jess"),
  Steve = c(0, 8, 9, 4),
  Joe = c(2, 0, 5, 6),
  Chan = c(8, 2, 0, 9),
  Jess = c(4, 5, 6, 0)
)

# Convert to a matrix with row names matching the player names
rownames(win_data) <- win_data$Loser_Winner
win_matrix <- as.matrix(win_data[, -1])  # Remove the first column of labels

# Calculate net wins: subtract the transposed matrix from the original
net_win_matrix <- win_matrix - t(win_matrix)

# View the result
print(net_win_matrix)

When you run this, you'll get exactly the expected output:

Steve Joe Chan Jess
Steve      0  -6   -1    0
Joe        6   0   -3   -1
Chan       1   3    0   -3
Jess       0   1    3    0

内容的提问来源于stack exchange，提问作者Five Star