如何基于Netmask匹配CSV中的CIDR，在Excel生成指定格式新列？

现有两个文件：

• 一个CSV文件存储CIDR与Netmask的对应关系
• 一个Excel表格需新增newmask列，根据每行的Netmask值匹配CSV中的CIDR，生成格式为「Netmask值 (CIDR)」的内容

以下是几种实用实现方式：

方法一：Excel内置函数（适合少量数据） #

步骤： #

1. 将CIDR对应CSV文件导入Excel（直接打开CSV或通过「数据」选项卡导入），假设导入后的数据位于Sheet2，A列为CIDR，B列为Netmask
2. 在目标Excel表的newmask列首行（比如F2单元格），根据Excel版本选择公式：
   • 若使用Excel 365/2021及以后版本，用XLOOKUP更简洁：

     =E2&" ("&XLOOKUP(E2,Sheet2!$B:$B,Sheet2!$A:$A,"")&")"
     

   • 兼容旧版Excel，用INDEX+MATCH组合：

     =E2&" ("&INDEX(Sheet2!$A:$A,MATCH(E2,Sheet2!$B:$B,0))&")"
     

   （注：E2为当前行的Netmask列单元格，需根据实际列调整）
3. 下拉公式到所有数据行，自动生成newmask列内容

方法二：Python脚本批量处理（适合大量数据） #

步骤： #

1. 确保已安装pandas和openpyxl库（用于读写Excel），若未安装，执行命令：

   pip install pandas openpyxl
   

2. 编写脚本（保存为generate_newmask.py）：

   import pandas as pd
   
   # 读取CIDR与Netmask的对应CSV
   cidr_map = pd.read_csv("cidr_netmask.csv")
   # 读取目标Excel表格
   network_data = pd.read_excel("network_data.xlsx")
   
   # 构建Netmask到CIDR的映射字典
   mask_cidr_dict = dict(zip(cidr_map["Netmask"], cidr_map["CIDR"]))
   
   # 生成newmask列
   network_data["newmask"] = network_data["Netmask"].apply(
       lambda mask: f"{mask} ({mask_cidr_dict[mask]})"
   )
   
   # 保存结果到新Excel文件
   network_data.to_excel("network_data_with_newmask.xlsx", index=False)
   

3. 运行脚本，会在当前目录生成包含newmask列的新Excel文件

方法三：Excel Power Query（适合需自动化更新的场景） #

步骤： #

1. 打开目标Excel表，点击「数据」>「获取数据」>「从文件」>「从CSV」，导入CIDR对应CSV，在Power Query编辑器中确认数据无误后，选择「关闭并加载」>「仅创建连接」
2. 再次点击「数据」>「获取数据」>「从表格/区域」，将目标Excel表导入Power Query编辑器
3. 在编辑器中，点击「合并查询」>「合并为新查询」：
   • 选择目标表的Netmask列作为合并依据
   • 选择CIDR连接表的Netmask列进行匹配，连接类型选「左外部」
4. 展开合并后的列，仅勾选CIDR列
5. 点击「添加列」>「自定义列」，输入公式：

   = [Netmask] & " (" & [CIDR] & ")"
   

   将自定义列命名为newmask
6. 点击「关闭并加载」，结果会自动同步到Excel表格中

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