如何合并Google Sheets中因Google Forms分区产生的同名学生多行列数据

Hey there! I’ve run into this exact scenario with Google Forms feeding into Sheets—when form sections split a single respondent’s data across multiple rows, it’s a real pain to clean up. Let’s walk through two reliable solutions to get your student data merged into one row per student.

Solution 1: Use QUERY with PIVOT (Quick & Automated) #

This is my go-to method because it handles grouping and pivoting in one step, no manual dragging required. Let’s assume your raw data is in a sheet named RawData, with columns:

• Column A: Student Name
• Column B: Subject
• Column C: Teacher’s Score
• Column D: Teacher Name

In a new blank sheet, paste this formula into cell A1:

=QUERY(RawData!A:D, "SELECT A, MAX(C), MAX(D) WHERE A IS NOT NULL GROUP BY A PIVOT B", 1)

How this works: #

• SELECT A grabs the student names
• MAX(C), MAX(D) pulls the score and teacher name for each subject (since each student-subject pair only has one entry, MAX just returns that single value)
• GROUP BY A groups all rows by student name
• PIVOT B turns the distinct subject values into columns, so each subject’s score and teacher get their own column per student
• The final 1 tells QUERY to use the first row as headers

Solution 2: UNIQUE + XLOOKUP (More Customizable) #

If you need more control over column order or want to handle edge cases (like missing subjects), this combination works great:

1. Get unique student names: In cell A1 of your new sheet, paste:

   =UNIQUE(RawData!A:A)
   

   This will list each student exactly once.

2. Pull subject-specific data: For example, to get the score for Math (replace "Math" with your actual subject name) in cell B2, use:

   =XLOOKUP($A2&"Math", RawData!A:A&RawData!B:B, RawData!C:C, "No Data")
   

   To get the corresponding teacher name in cell C2:

   =XLOOKUP($A2&"Math", RawData!A:A&RawData!B:B, RawData!D:D, "No Data")
   

3. Repeat for other subjects: Just copy the formulas and replace "Math" with your other subject names (e.g., "Science", "English") to fill in all columns.

Pro Tips: #

• Fix name inconsistencies: If students have names with extra spaces or mixed capitalization, clean them up first with =TRIM(A:A) and =LOWER(A:A) in helper columns, then use those cleaned columns in your formulas.
• Handle missing data: The "No Data" in XLOOKUP will show a friendly message if a student doesn’t have a record for a subject—you can replace this with "" to leave cells blank instead.

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