基于数值范围选取单列动态单元格区域并计算中位数

Got it, let's break this down into actionable steps to get exactly what you need:

1. Identify the Start of Your Range (Closest E value ≤50) #

How you calculate this depends on whether your column E is sorted or not:

For Sorted (Ascending) Column E: #

Use the MATCH function to find the largest value ≤50 and get its row number:

=MATCH(50, E:E, 1)

• The 1 argument tells MATCH to look for the closest match that's less than or equal to 50, perfect for sorted data.

For Unsorted Column E: #

Use AGGREGATE to hunt down the highest row where E has a value ≤50 (this ensures you get the closest match if there are multiple values near 50):

=AGGREGATE(14, 6, ROW(E:E)/(E:E<=50), 1)

• 14 calls the LARGE function, 6 ignores errors from rows that don't meet the condition, and 1 grabs the top (highest row) result.

2. Identify the End of Your Range (Closest E value ≥550) #

Again, sorted vs unsorted has different approaches:

For Sorted (Ascending) Column E: #

First find the last row with a value ≤550, then jump to the first row with a value ≥550:

=MATCH(550, E:E, 1) + IF(COUNTIF(E:E,550)=0, 1, 0)

• If 550 exists in E, we just use that row; if not, we add 1 to get the next row which will be the first value over 550.

For Unsorted Column E: #

Use AGGREGATE again, this time to get the lowest row where E has a value ≥550:

=AGGREGATE(15, 6, ROW(E:E)/(E:E>=550), 1)

• 15 calls the SMALL function, so we get the earliest row that meets the ≥550 condition.

3. Calculate the Median of Corresponding G Column Values #

Now combine your start and end rows with INDEX and MEDIAN to get the result. You can either store the start/end row numbers in separate cells, or nest everything into one formula.

Example (All-in-One for Unsorted Data): #

=MEDIAN(INDEX(G:G, AGGREGATE(14,6,ROW(E:E)/(E:E<=50),1)):INDEX(G:G, AGGREGATE(15,6,ROW(E:E)/(E:E>=550),1)))

Error Handling (Optional): #

If there are no values ≤50 or ≥550 in E, the formula will throw an error. Wrap it in IFERROR to make it user-friendly:

=IFERROR(MEDIAN(INDEX(G:G, AGGREGATE(14,6,ROW(E:E)/(E:E<=50),1)):INDEX(G:G, AGGREGATE(15,6,ROW(E:E)/(E:E>=550),1))), "No valid range found")

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