使用 ImportHTML 或 ImportXML 在 Google 表格中選擇不連續的列、刪除行和添加排序列 (Using ImportHTML or ImportXML to Select Non-Consecutive Columns In Google Sheets, Remove Rows, & Add A Sort Column)

問題描述

使用 ImportHTML 或 ImportXML 在 Google 表格中選擇不連續的列、刪除行和添加排序列 (Using ImportHTML or ImportXML to Select Non‑Consecutive Columns In Google Sheets, Remove Rows, & Add A Sort Column)

我想從 https:// 導入表格信息www.pro‑football‑reference.com/years/2020/draft.htm 進入谷歌表格。但是，我試圖避免拉入空單元格以及我在其他工作表中已經擁有的信息。以下是我的問題：

1. 我想要的唯一列是 Round (col1)、Pick (Col2) 和 Player (Col4)。我已經嘗試過使用 ImportHTML，到目前為止，我所能做的就是抓取整個表格。
2. 我想創建一個名為“Rd.Pick”的新列，它將選擇列轉換為什麼的表示在各自的回合中選擇他們。所以又名 Pick 33 將顯示 2.1
3. 最後，我希望能夠刪除在一輪的最後一個選秀之間但在下一輪的第一個選秀之前列出的行。鑑於這些行中的文本與標題行匹配，我不確定該怎麼做。

參考解法

方法 1:

try:

=ARRAYFORMULA(QUERY({
 QUERY(IMPORTHTML("https://www.pro‑football‑reference.com/years/2020/draft.htm", 
 "table", 1), "select Col4"), 
 QUERY(IMPORTHTML("https://www.pro‑football‑reference.com/years/2020/draft.htm", 
 "table", 1), "select Col1")&"."&
 QUERY(IMPORTHTML("https://www.pro‑football‑reference.com/years/2020/draft.htm", 
 "table", 1), "select Col2")}, "where not Col2 matches '\.'", 1))

方法 2:

This is just to answer the question from your comment above ‑ how to convert the sequential draft pick number to a number like 3.12, 12th pick in the 3rd round. This formula is a bit brute force, but it works:

={"Round‑Pick";
ArrayFormula(ifna(ifs(
  D2:D=1,"1."& text(E2:E,"00"),
  D2:D=2,"2."& text(E2:E‑max(filter(D$2:E,D$2:D=1)),"00"),
  D2:D=3,"3."& text(E2:E‑max(filter(D$2:E,D$2:D=2)),"00"),
  D2:D=4,"4."& text(E2:E‑max(filter(D$2:E,D$2:D=3)),"00"),
  D2:D=5,"5."& text(E2:E‑max(filter(D$2:E,D$2:D=4)),"00"),
  D2:D=6,"6."& text(E2:E‑max(filter(D$2:E,D$2:D=5)),"00"),
  D2:D=7,"7."& text(E2:E‑max(filter(D$2:E,D$2:D=6)),"00")
  ),""))}

If you put that in NFLDraft!F1, it should do what you want. You could then hide Column E if you like.

UPDATED: To provide the format you've requested, with leading zero.

(by InStackOfHelp、player0、kirkg13)

參考文件

1. Using ImportHTML or ImportXML to Select Non‑Consecutive Columns In Google Sheets, Remove Rows, & Add A Sort Column (CC BY‑SA 2.5/3.0/4.0)