提取Excel数组中最后6个唯一日期及对应列的解决方案问询

Hey there! Let's work through your Excel problem—extracting the last 6 unique dates from your dataset, along with all the matching game data (home/away teams, goals). I've got two reliable solutions for you, depending on your Excel version and workflow preference:

If you have a modern Excel version with dynamic array support, this is quick and automatically updates as your data changes.

Step 1: Get sorted unique dates (newest first) #

Pick a blank cell (e.g., G2) and enter this formula. Adjust the range A2:A100 to match your actual date column range:

=SORT(UNIQUE(A2:A100),,-1)

This will spill out all unique dates sorted from the most recent to oldest.

Step 2: Extract the top 6 (newest) dates #

In another cell (e.g., H2), use the TAKE function to grab the first 6 entries from the sorted list:

=TAKE(G2#,6)

The # references the spilled array from the previous formula, so it'll automatically adjust if your unique date list changes.

Step 3: Filter all matching data #

Finally, in a blank cell (e.g., J2), use FILTER to pull all rows where the date is in your 6 unique dates:

=FILTER(A2:E100,ISNUMBER(XMATCH(A2:A100,H2:H7)))

This will spill out every row of data tied to those 6 dates, keeping all columns intact.

Power Query is great for larger datasets or if you prefer a visual, repeatable workflow.

Step 1: Load data into Power Query #

• Select your entire dataset (including headers: DATE, HOME, AWAY, FTHG, FTAG)
• Go to the Data tab → click From Table/Range (check "My table has headers" if prompted) to open the Power Query Editor.

Step 2: Get the 6 newest unique dates #

• Click the DATE column, go to the Transform tab → Sort → Sort Descending (puts newest dates first)
• With the DATE column still selected, go to Home tab → Remove Duplicates (now you have unique dates sorted newest to oldest)
• Go to Home → Keep Rows → Keep Top Rows, enter 6 and click OK. You now have your 6 target dates.

Step 3: Merge back with original data #

• Click Home → Close & Load To → choose Only Create Connection (this saves the 6-date list as a query without loading it to a sheet)
• Go back to the Power Query Editor (reopen the original data query if needed), click Home → Merge Queries → Merge Queries as New Query
• Select your original data table as the first table, and the 6-date query as the second table. Set the join condition to match the DATE columns, and choose Inner Join (only keeps rows that match)
• Click the expand icon on the merged column, check all columns except the duplicate DATE column, then click OK.
• Adjust column order if needed, then click Home → Close & Load to send the filtered data to a new worksheet.

Quick Notes #

• Make sure your DATE column is formatted as actual dates (not text). If it's text, select the column, right-click → Format Cells → choose Date, or use the DATEVALUE function to convert it.
• For dynamic updates: With the array formula method, changes to your data will auto-refresh. With Power Query, right-click the results table → Refresh.

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