Google Sheets数据透视表分组异常及QUERY函数列添加求助

Got it, let's tackle this problem head-on. The pivot table issue you're facing happens because it only groups by first name, merging distinct people who share the same first name (like James McLeod vs James Stringer). The QUERY function is the right fix here—we just need to adjust it to group by all unique identifiers for each person, not just their first name.

Step-by-Step Solution #

1. First, create a new tab (let's name it Final Summary for clarity) where you want your final summary to live.
2. In cell A1 of this new tab, paste this QUERY formula:

   =QUERY(Results, "SELECT A, B, C, SUM(F) WHERE A IS NOT NULL GROUP BY A, B, C ORDER BY SUM(F) DESC LABEL SUM(F) 'Total Miles'", 1)
   

Formula Breakdown #

Let's break down each part to confirm it matches your needs:

• SELECT A, B, C, SUM(F): Pulls in the FirstName (A), LastName (B), Role (C) columns, plus calculates the total miles by summing the Miles column (F) for each person.
• WHERE A IS NOT NULL: Filters out blank rows to avoid including empty or header-only entries.
• GROUP BY A, B, C: This is the key fix! Instead of grouping by just first name, we group by all three fields that uniquely identify a person. This ensures people with matching first names (but different last names or roles) are treated as separate entries.
• ORDER BY SUM(F) DESC: Sorts results from highest total miles to lowest, just like you wanted.
• LABEL SUM(F) 'Total Miles': Renames the summed miles column to a clear, readable header.
• The final 1 tells QUERY that your source data (Results) has a header row, so it preserves those labels in the output.

Quick Check for Named Range #

Double-check that your Results named range is correctly set to include columns A:F from the original Data tab. If not, go to Data > Named ranges to redefine it—make sure it covers all your actual data rows.

This formula will give you a clean summary with all the columns you need, properly sorted by total miles, and no merging of distinct people with matching first names.

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