如何在Microsoft Excel中显示=FILTERXML函数返回的多个值？
Ah, I’ve run into this exact issue before! The problem here isn’t with the formula itself—it’s about how you’re entering the array formula in older Excel versions. Let’s break this down:
Why You’re Only Seeing the First Value
When you use FILTERXML to extract multiple nodes (like all <name> elements from the XML), it returns an array of values. But if you enter the formula into a single cell and press Ctrl+Shift+Enter, Excel will only display the first item in that array. The curly braces {} confirm it’s an array formula, but without a range selected, it can’t spill all results.
Solution for Pre-Excel 365 (Older Versions)
Follow these steps to get all values:
- Estimate the number of results: The w3schools sample XML has 5
<food>entries, so you’ll need 5 cells. - Select a range of cells: Click and drag to highlight cells where you want the results (e.g., A1:A5).
- Enter the formula: Type
=FILTERXML(WEBSERVICE("https://www.w3schools.com/xml/simple.xml"),"//food/name")into the formula bar—don’t add curly braces yourself. - Confirm as array formula: Press
Ctrl+Shift+Entertogether. Excel will wrap the formula in{}automatically, and each cell in your selected range will populate with a different food name.
Solution for Excel 365/2021 (Dynamic Arrays)
If you’re using a newer Excel version with dynamic array support, this is way simpler:
- Just enter the formula into a single cell (no need to select a range or press Ctrl+Shift+Enter). Excel will automatically "spill" all results down into adjacent cells below it.
Quick Check
To verify how many items the formula returns, you can use COUNTA(FILTERXML(WEBSERVICE("https://www.w3schools.com/xml/simple.xml"),"//food/name"))—this should give you 5 for the sample XML, so make sure you select at least 5 cells if using an older Excel version.
内容的提问来源于stack exchange，提问作者empenoso
