如何基于列匹配可变行数？Excel/VBA/R/Python实现方案问询

需求说明 #

基于前6列原始数据，计算最后2列：

• IP2Match：仅针对IPIndex=1的行，列出同Trial内，与当前点（CurrentFixX/CurrentFixY）欧氏距离≤100的IPIndex=2对应的CurrentFixIndex，用逗号分隔；IPIndex=2的行留空
• CountMatches：统计IP2Match列的匹配数量，IPIndex=2的行留空

欧氏距离公式：$d=\sqrt{(x_2-x_1)^2 + (y_2-y_1)^2}$

方案1：Excel公式（适合小数据量，无需编程） #

假设数据位于A:H列，表头在第1行，从第2行开始为数据：

IP2Match（G2单元格） #

=IF(A2=1,TEXTJOIN(",",TRUE,FILTER($D$2:$D$13,($C$2:$C$13=C2)*($A$2:$A$13=2)*((($E$2:$E$13-E2)^2+($F$2:$F$13-F2)^2)^0.5<=100))),"")

下拉填充至所有行。

CountMatches（H2单元格） #

=IF(A2=1,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1*(G2<>""),"")

下拉填充至所有行。

说明：FILTER筛选符合条件的记录，TEXTJOIN拼接结果；通过统计逗号数量+1计算匹配数，空值时自动返回0。

方案2：VBA（适合中大数据量，Excel内自动化） #

打开Excel后按Alt+F11进入VBA编辑器，插入模块并粘贴以下代码，运行即可自动计算：

Sub CalculateIPMatches()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim trialNum As Integer, ip1X As Double, ip1Y As Double
    Dim matchList As String, matchCount As Integer
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        If ws.Cells(i, "A").Value = 1 Then
            trialNum = ws.Cells(i, "C").Value
            ip1X = ws.Cells(i, "E").Value
            ip1Y = ws.Cells(i, "F").Value
            matchList = ""
            matchCount = 0
            
            For j = 2 To lastRow
                If ws.Cells(j, "C").Value = trialNum And ws.Cells(j, "A").Value = 2 Then
                    distance = Sqr((ws.Cells(j, "E").Value - ip1X) ^ 2 + (ws.Cells(j, "F").Value - ip1Y) ^ 2)
                    If distance <= 100 Then
                        If matchList <> "" Then matchList = matchList & ","
                        matchList = matchList & ws.Cells(j, "D").Value
                        matchCount = matchCount + 1
                    End If
                End If
            Next j
            
            ws.Cells(i, "G").Value = matchList
            ws.Cells(i, "H").Value = matchCount
        Else
            ws.Cells(i, "G").Value = ""
            ws.Cells(i, "H").Value = ""
        End If
    Next i
End Sub

方案3：Python（适合大数据量、批量处理） #

使用pandas库实现，步骤如下：

1. 安装依赖：pip install pandas openpyxl
2. 运行以下代码（替换文件名）：

import pandas as pd
import numpy as np

# 读取Excel文件
df = pd.read_excel("your_input_file.xlsx")

# 定义匹配函数
def get_matches(row):
    if row["IPIndex"] != 1:
        return pd.Series(["", 0])
    # 筛选同Trial的IPIndex=2数据
    trial_ip2 = df[(df["Trial"] == row["Trial"]) & (df["IPIndex"] == 2)]
    # 计算欧氏距离
    dists = np.sqrt((trial_ip2["CurrentFixX"] - row["CurrentFixX"])**2 + 
                   (trial_ip2["CurrentFixY"] - row["CurrentFixY"])**2)
    # 提取符合条件的CurrentFixIndex
    matches = trial_ip2[dists <= 100]["CurrentFixIndex"].tolist()
    # 生成结果
    match_str = ",".join(map(str, matches)) if matches else ""
    return pd.Series([match_str, len(matches)])

# 应用函数到每行
df[["IP2Match", "CountMatches"]] = df.apply(get_matches, axis=1)

# 保存结果
df.to_excel("your_output_file.xlsx", index=False)

方案4：R（适合统计分析场景） #

使用tidyverse生态实现，步骤如下：

1. 安装依赖：install.packages(c("tidyverse", "readxl", "writexl"))
2. 运行以下代码（替换文件名）：

library(tidyverse)
library(readxl)
library(writexl)

# 读取数据
df <- read_excel("your_input_file.xlsx")

# 计算匹配结果
result_df <- df %>%
  group_by(Trial) %>%
  mutate(
    IP2Match = if_else(IPIndex == 1,
                       map_chr(row_number(), ~{
                         current <- cur_data()[.x,]
                         ip2_rows <- cur_data() %>% filter(IPIndex == 2)
                         dists <- sqrt((ip2_rows$CurrentFixX - current$CurrentFixX)^2 + 
                                        (ip2_rows$CurrentFixY - current$CurrentFixY)^2)
                         matches <- ip2_rows$CurrentFixIndex[dists <= 100]
                         if (length(matches) > 0) paste(matches, collapse = ",") else ""
                       }),
                       ""),
    CountMatches = if_else(IPIndex == 1, str_count(IP2Match, ",") + 1*(IP2Match != ""), 0)
  ) %>%
  ungroup()

# 保存结果
write_xlsx(result_df, "your_output_file.xlsx")

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