Hey Chris, no macros needed here—Excel’s built-in functions can handle both tasks perfectly, even with variable row counts. Let’s break this down step by step:

A. 计算工作表的数据行数 #

Since your row count varies (300, 354, etc.), we’ll use dynamic formulas to get the exact number of data rows (we’ll assume your first row is a header):

方法1：基于单列统计（适合无空白数据的列） #

If column A has no blank cells in your data range, use this formula in an empty cell (e.g., F1):

=COUNTA(A:A) - 1

• COUNTA(A:A) counts all non-empty cells in column A (including the header)
• Subtract 1 to exclude the header row, giving you the total number of data rows

方法2：基于所有列统计（更稳妥，避免单列空白） #

If some columns might have blank cells at the end, use this to get the maximum non-empty row count across all 5 columns:

=MAX(COUNTA(A:A), COUNTA(B:B), COUNTA(C:C), COUNTA(D:D), COUNTA(E:E)) - 1

This ensures you get the true total data row count even if one column ends earlier than others.

B. 自动计算每列的中位数 #

We’ll use a dynamic range with the MEDIAN function so it automatically adapts to your row count. Assuming your data starts at row 2 (row 1 is header):

1. For column A’s median, enter this formula in an empty cell (e.g., the row right below your last data row in column A, or F2):

=MEDIAN(A2:INDEX(A:A, COUNTA(A:A)))

2. Copy this formula to columns B, C, D, and E (just drag the fill handle across the target cells)

公式解释： #

• INDEX(A:A, COUNTA(A:A)) dynamically returns the last non-empty cell in column A
• A2:INDEX(...) creates a range from the first data row (A2) to the last data row in column A
• MEDIAN() calculates the median for that dynamic range—no need to update the formula when your row count changes!

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