无损失汇总重复数据：企业年度多表合并与去重标记方案咨询

一、工具选择 #

• 新手入门首选Excel/Google Sheets：纯可视化操作，无需代码，上手快，适合当前数据量不大的场景。
• 若后续需处理超大规模数据或自动化重复任务，选R：代码化处理，扩展性强，批量操作更高效。

二、Excel操作步骤 #

1. 收集唯一企业ID #

• 新建名为Master的工作表，将所有年份sheet里的id列数据复制到Master的A列（跳过表头）。
• 选中A列，点击「数据」→「删除重复值」，保留唯一ID列表。

2. 提取经纬度信息 #

• 在Master的B列（latitude）输入公式：=XLOOKUP($A2, 2020!$A:$A, 2020!$B:$B, XLOOKUP($A2, 2021!$A:$A, 2021!$B:$B, ""))（替换2020、2021为你的实际年份，多年份依次嵌套，无数据则显示空值）。
• C列（longitude）同理，将公式里的$B:$B替换为$C:$C即可。

3. 添加年份存在标记列 #

• 比如2020年标记列（D列），输入公式：=NOT(ISNA(XLOOKUP($A2, 2020!$A:$A, 2020!$A:$A)))，下拉填充后，存在显示TRUE，不存在显示FALSE。
• 其他年份列重复此操作，修改公式里的年份sheet名称即可。

三、Google Sheets操作步骤 #

逻辑和Excel一致，仅公式细节调整：

1. 收集唯一ID #

• 新建Master表，用公式提取所有年份ID并去重：=UNIQUE({2020!A2:A; 2021!A2:A; 2022!A2:A})（替换年份为实际sheet名，用分号分隔不同sheet的范围）。

2. 提取经纬度 #

• B列latitude公式：=IFERROR(VLOOKUP(A2, 2020!A:C, 2, FALSE), IFERROR(VLOOKUP(A2, 2021!A:C, 2, FALSE), ""))。
• C列longitude把公式里的第3个参数2换成3。

3. 年份标记 #

• D列2020年标记：=NOT(ISNA(VLOOKUP(A2, 2020!A:A, 1, FALSE)))，下拉填充即可。

四、R语言操作步骤（适合进阶） #

1. 安装并加载依赖包 #

install.packages(c("readxl", "dplyr", "tidyr", "writexl"))
library(readxl)
library(dplyr)
library(tidyr)
library(writexl)

2. 读取所有sheet数据 #

# 替换为你的本地文件路径
file_path <- "企业年度数据.xlsx"
# 读取所有sheet到列表
sheet_data <- excel_sheets(file_path) %>%
  purrr::map(~read_excel(file_path, sheet = .x))

3. 合并数据并生成标记列 #

master_data <- bind_rows(sheet_data) %>%
  # 保留每个ID的唯一经纬度
  distinct(id, latitude, longitude, .keep_all = TRUE) %>%
  # 生成年份存在的TRUE/FALSE标记
  pivot_wider(
    id_cols = c(id, latitude, longitude),
    names_from = year,
    values_from = year,
    values_fn = ~!is.na(.x),
    values_fill = FALSE
  )

4. 导出汇总表 #

write_xlsx(master_data, "企业汇总表.xlsx")

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