Here's a high level guide to getting what you want along with a sample in this file.
Step 1 - Combine Files to Sheet with Unified Columns
Objective
The goal here is to:
- Combine all of your data from other files to single sheet
- Merge the data to be in a single column for each field (i.e. Column A has ID, Column B has score).
- No breaks in rows.
- No formulas.
To illustrate, I made this fake list based loosely on your
description.
Method
You probably can do this manually, but a macro could also be used. If you expect to do this year over year, you might look into vba to open close files in a folder. However, since that wasn't part of question, you can do copy-paste (better yet make a kid do it!). Just make sure there's only one header for each column, and all of the data records align. Probably should do copy paste value if you have any formulas.
Step 2 - Show Summation
There's a couple ways this could be done. A pivot table is probably the most sensible because you could include each quiz as a column to see the total. You could also use a pivot table to do averages by student etc.
TO make a pivot table, I would recommend going on YouTube and they will do a better job of explaining than me.
On that same file I made as an example, I included some tabs to illustrate the power of pivot tables and a couple graphs.
Hope that helps. If you have specific technical questions on this, you might consider asking separately.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…