谷歌表格中如何用ImportXML将提取数据拆分至多列？

Hey there! Let's fix that row-based output and get the clean columnar layout you want, plus add those extra metrics you need. Here's a step-by-step breakdown with a streamlined formula:

Key Approach #

Instead of just pulling raw numbers, we'll first grab both the metric labels and their corresponding values from the page, then match the specific metrics you care about to build a structured table with headers on top and data in a single row below.

Full Formula #

Paste this into an empty cell (like A1) and it will generate your desired table automatically:

=LET(
  // Fetch all metric labels from the page
  metric_labels, IMPORTXML("https://www.socialexplorer.com/profiles/essential-report/zcta5-48105.html", "//div[contains(@class,'c-label')]"),
  // Fetch all corresponding numerical values
  metric_values, IMPORTXML("https://www.socialexplorer.com/profiles/essential-report/zcta5-48105.html", "//div[contains(@class,'c-num')]"),
  // Define the exact metrics you want to include (customize this list as needed)
  desired_metrics, {"Total Population", "Land Area (Square Miles)", "Population Density", "Median Age", "Bachelor's Degree or Higher", "Per Capita Income"},
  // Create the header row
  header_row, desired_metrics,
  // Match each desired metric to its value and format as a data row
  data_row, ARRAYFORMULA(
    VALUE(SUBSTITUTE(INDEX(metric_values, MATCH(desired_metrics, metric_labels, 0)), ",", ""))
  ),
  // Combine headers and data into a single table
  {header_row; data_row}
)

How It Works #

1. LET Function: This lets us name variables for readability, so we don't repeat the same IMPORTXML calls multiple times.
2. Metric Labels & Values: We use IMPORTXML with two XPath queries to pull both the names of metrics (e.g., "Total Population") and their associated numbers.
3. Desired Metrics List: Customize the desired_metrics array to add/remove any metrics you want—just make sure the text matches exactly what's on the Social Explorer page.
4. Data Row Cleanup: The SUBSTITUTE and VALUE functions remove commas from numbers and convert them to numerical values, making it easier to do calculations later.
5. Final Table: We stack the header row on top of the data row to get your requested columnar layout.

Troubleshooting Tips #

• If a value returns #N/A, double-check that the text in desired_metrics matches the label on the page exactly (capitalization, spaces, and punctuation matter!).
• If the formula breaks over time, the page's HTML structure might have changed—inspect the page to verify the c-label and c-num class names are still used for metrics and values.

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