如何在Excel中找出URL列表里最常见的3-6字母序列？

针对你的需求，以下是两种可行的解决方案，分别适配Excel环境和更高效的Python脚本场景：

方法一：Excel Power Query + 数据透视表 #

适合熟悉Excel操作的用户，能处理5万条数据量级：

1. 提取域名主体
   假设URL存放在A列，在B列输入以下公式提取去掉前缀（http/https/www）和后缀（.com/.org等）的域名主体：

   =LET(
       raw_url, A1,
       no_protocol, IF(ISNUMBER(SEARCH("://", raw_url)), TEXTAFTER(raw_url, "://"), raw_url),
       no_www, IF(LEFT(no_protocol, 4)="www.", RIGHT(no_protocol, LEN(no_protocol)-4), no_protocol),
       domain_only, TEXTBEFORE(no_www, "/", 1),
       main_part, TEXTBEFORE(domain_only, ".", -1),
       main_part
   )
   

   下拉填充公式到所有行，得到纯域名主体列。

2. 用Power Query生成所有目标长度的字母子串

   • 选中A:B列，点击「数据」→「从表格/区域」导入Power Query编辑器
   • 添加自定义列，输入以下M代码生成3-6长度的纯字母子串（自动统一小写、过滤非字母字符）：

     = let
         text = [main_part],
         clean_text = Text.Select(text, {"a".."z", "A".."Z"}),
         len = Text.Length(clean_text),
         sequences = List.TransformMany(
             {3,4,5,6},
             (n) => if len >= n then List.Generate(
                 () => 0,
                 (i) => i <= len - n,
                 (i) => i + 1,
                 (i) => Text.Middle(clean_text, i, n)
             ) else {},
             (n, seq) => Text.Lower(seq)
         )
     in sequences
     

   • 点击自定义列右侧的「展开到新行」，将所有子串拆分为单独行
   • 关闭编辑器并将数据上载到新工作表

3. 统计频率并排序
   选中展开后的子串列，插入数据透视表：

   • 将子串字段拖到「行」和「值」区域（值区域选择「计数」）
   • 按计数降序排序，最顶部的就是出现次数最多的序列

方法二：Python脚本（高效处理大数据） #

适合有基础编程能力的用户，处理5万条数据速度更快：

1. 准备数据
   将所有URL保存为文本文件urls.txt，每行一个URL。

2. 编写并运行脚本

   import re
   from collections import Counter
   
   # 定义目标序列长度和需要排除的域名后缀
   target_lengths = [3, 4, 5, 6]
   excluded_suffixes = {'.com', '.org', '.net', '.cn', '.edu', '.gov'}
   
   def clean_domain(url):
       # 移除协议和www前缀
       url = re.sub(r'https?://(www\.)?', '', url)
       # 移除路径部分
       url = url.split('/')[0]
       # 移除域名后缀
       for suffix in excluded_suffixes:
           if url.endswith(suffix):
               url = url[:-len(suffix)]
               break
       # 只保留字母并转小写
       return re.sub(r'[^a-zA-Z]', '', url).lower()
   
   # 读取URL列表
   with open('urls.txt', 'r', encoding='utf-8') as f:
       urls = [line.strip() for line in f if line.strip()]
   
   # 收集所有符合要求的序列
   all_sequences = []
   for url in urls:
       domain = clean_domain(url)
       domain_len = len(domain)
       for length in target_lengths:
           if domain_len >= length:
               for i in range(domain_len - length + 1):
                   all_sequences.append(domain[i:i+length])
   
   # 统计频率并输出前10个最常见序列
   seq_counter = Counter(all_sequences)
   print("最常见的3-6字母序列：")
   for seq, count in seq_counter.most_common(10):
       print(f"{seq}: {count} 次")
   

原公式无效的原因 #

=INDEX(range, MODE(MATCH(range, range, 0))) 是用来查找整个单元格内容的重复最大值，而你的需求是提取单元格内的子串并统计，两者逻辑完全不匹配，因此无法生效。

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