如何在Google Sheets中仅导入网页所需的球队及排名数据？

核心问题拆解 #

原IMPORTXML公式抓回的是混有胜负记录、无关文本的杂乱数据，需要精准拆分提取球队名和5项纯数字排名。

分步解决方案 #

• 1. 抓取干净的原始数据行
  替换原XPath，用更精准的路径定位数据文本块：
  =IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()")
  这个公式会返回网页里所有纯文本数据，过滤掉多层嵌套的font标签干扰。

• 2. 过滤无效行，只保留球队数据
  用FILTER+正则匹配，只留下以数字开头的球队排名行（排除标题、空行）：
  =FILTER(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), REGEXMATCH(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), "^[0-9]+ "))

• 3. 拆分提取球队名和5项排名
  假设过滤后的数据在A列，在B1输入提取球队名的公式（自动去掉胜负记录）：
  =TRIM(REGEXREPLACE(INDEX(SPLIT(A1, " "), 2)&" "&INDEX(SPLIT(A1, " "), 3), "\(.*\)", ""))
  （注：如果球队名是单字，比如Duke，这个公式也能正常提取，多余空格会被TRIM清理）

  提取5项排名的纯数字（以第1项为例，C1单元格）：
  =INDEX(SPLIT(A1, " "), 4)
  后续4项依次修改索引为5、6、7、8即可，比如D1用INDEX(SPLIT(A1, " "),5)，以此类推。

• 4. 一键生成完整表格（数组公式）
  不想手动下拉公式？直接用数组公式一次性生成所有列，输入到任意空白单元格即可：

  =ARRAYFORMULA(IFERROR(
    {
      TRIM(REGEXREPLACE(INDEX(SPLIT(FILTER(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), REGEXMATCH(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), "^[0-9]+ ")), " "),,2)&" "&INDEX(SPLIT(FILTER(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), REGEXMATCH(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), "^[0-9]+ ")), " "),,3), "\(.*\)", "")),
      INDEX(SPLIT(FILTER(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), REGEXMATCH(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), "^[0-9]+ ")), " "),,4),
      INDEX(SPLIT(FILTER(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), REGEXMATCH(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), "^[0-9]+ ")), " "),,5),
      INDEX(SPLIT(FILTER(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), REGEXMATCH(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), "^[0-9]+ ")), " "),,6),
      INDEX(SPLIT(FILTER(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), REGEXMATCH(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), "^[0-9]+ ")), " "),,7),
      INDEX(SPLIT(FILTER(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), REGEXMATCH(IMPORTXML("https://sagarin.usatoday.com/2023-2/college-basketball-team-ratings-2022-23/", "//pre/text()"), "^[0-9]+ ")), " "),,8)
    }
  ))
  

  生成后，所有排名列都是纯数字，可直接用SUM等函数计算。

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