如何基于行/列多值范围对田间试验数据进行单元格求和与合并

我完全理解你手动处理这种地块分组的痛苦——尤其是要应对多种组合场景时，手动操作既低效又容易出错。下面我给你分享几个管道友好的R解决方案，完全基于行列逻辑自动生成分组，不用手动指定地块，还能轻松扩展到不同的作物、地点和组合规则：

核心思路 #

给每个需要组合的地块组分配一个唯一组ID，然后按这个组ID分组求和。组ID完全由行列的数学关系生成，不需要硬编码地块编号，通用性极强。

先准备示例数据（和你提供的一致） #

library(dplyr)

df <- tibble(
  Week = rep(34, 12),
  Plot = c(101,102,103,104,105,106,201,202,203,204,205,206),
  Row = rep(1:2, each=6),
  Column = rep(1:6, 2),
  Rep = rep(1:2, each=6),
  Variety = c("VarB","VarA","VarD","VarE","VarC","VarF","VarE","VarF","VarA","VarC","VarB","VarD"),
  Market_Ct = c(15,32,3,5,11,22,11,18,6,15,7,16),
  Market_Wt = c(1174,2450,234,440,882,1749,834,1266,513,899,550,1220),
  Unmark_Wt = c(671,136,127,657,430,683,262,863,317,356,261,755)
)

场景1：同一行内相邻两列地块组合（101&102、103&104...） #

用floor((Column - 1)/2) + 1生成组ID，每2列自动归为一组：

df_grouped_2col <- df %>%
  group_by(Week, Row) %>%
  mutate(group_id = floor((Column - 1)/2) + 1) %>%  # 每2列生成一个组ID
  group_by(Week, Row, group_id) %>%
  summarise(
    Plot_group = paste(Plot, collapse = "&"),  # 合并组内地块编号
    Rep = first(Rep),  # 保持重复组一致，按需调整
    Variety = paste(Variety, collapse = "+"),  # 合并组内品种
    Market_Ct = sum(Market_Ct),
    Market_Wt = sum(Market_Wt),
    Unmark_Wt = sum(Unmark_Wt),
    .groups = "drop"
  )

# 查看结果
df_grouped_2col

场景2：同一列内上下两行地块组合（101&201、102&202...） #

按Week和Column分组，直接对同一列的两行求和：

df_grouped_2row <- df %>%
  group_by(Week, Column) %>%
  summarise(
    Plot_group = paste(Plot, collapse = "&"),
    Rep = paste(Rep, collapse = "&"),
    Variety = paste(Variety, collapse = "+"),
    Market_Ct = sum(Market_Ct),
    Market_Wt = sum(Market_Wt),
    Unmark_Wt = sum(Unmark_Wt),
    .groups = "drop"
  )

场景3：同一行内每3列一组（101-103、104-106...） #

调整组ID的生成逻辑为floor((Column - 1)/3) + 1即可：

df_grouped_3col <- df %>%
  group_by(Week, Row) %>%
  mutate(group_id = floor((Column - 1)/3) + 1) %>%
  group_by(Week, Row, group_id) %>%
  summarise(
    Plot_group = paste(Plot, collapse = "-"),
    Variety = paste(Variety, collapse = "+"),
    Market_Ct = sum(Market_Ct),
    Market_Wt = sum(Market_Wt),
    Unmark_Wt = sum(Unmark_Wt),
    .groups = "drop"
  )

后续随机化品种/重复次数 #

分组完成后，你可以轻松用sample()实现随机分配：

# 示例：给每个组合组随机分配新品种（从原有品种池抽取）
unique_varieties <- unique(df$Variety)

df_grouped_random <- df_grouped_2col %>%
  mutate(
    New_Variety = sample(unique_varieties, size = n(), replace = TRUE),
    New_Rep = sample(1:(n()/2), size = n(), replace = TRUE)  # 重复次数减半示例
  )

Excel 替代方案 #

如果需要用Excel处理：

1. 新增辅助列生成组ID，比如同一行两列一组的公式：=FLOOR((C2-1)/2)+1（假设Column在C列）
2. 插入数据透视表，将Week、Row、辅助列拖到「行」区域，产量列拖到「值」区域选择求和
3. 随机化品种可以用公式：=INDEX(UNIQUE($F:$F), RANDBETWEEN(1, COUNTA(UNIQUE($F:$F))))（假设品种在F列）

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