16.13
Microsoft_Excel：中位数、四分位距和箱线图
在 Microsoft Excel 中，计算中位数、四分位距和创建箱线图有助于了解数据的分布情况。
中位数和四分位距：中位数使用公式 `=MEDIAN(range)’ 计算，该公式提供数据集的中间值。四分位数将数据分成四个相等的部分。要找到第一和第三四分位数，请分别使用 ‘=QUARTILE(ran…
考虑以下三个数据集。现在，同时选中它们，选择“插入”选项，然后选择“插入统计图表”，再选择“箱线图”。
在箱线图中，中央线表示数据的中位数或第二四分位数。箱子的边缘分别代表第一和第三四分位数。最后，端点显示所选数据范围内的最小值和最大值。
在 Microsoft Excel 中，使用 MEDIAN 函数来确定数据的中位数。
类似地，使用 MIN 和 MAX 函数来确定最小值和最大值。
两个不同的函数——QUARTILE.EXC 和 QUARTILE.INC——返回四分位数值。
函数 QUARTILE.EXC 在计算四分位数时会排除数据的端点，即最小值和最大值。选择数组的数据范围，并为四分位数选择 1、2 或 3，以获得第一、第二和第三四分位数值。
函数 QUARTLE.INC 考虑整个数值范围。此处，为四分位数值选择 0、1、2、3 或 4，以获取数据的最小值、第一四分位数、中位数、第三四分位数和最大值。
View the full transcript and gain access to JoVE Core videos
Q1: What does the central line in a box plot represent?
The central line in a box plot represents the median, also called the second quartile, which is the middle value of your dataset. In Microsoft Excel, you calculate the median using the MEDIAN function applied to your data range. This line divides the data into two equal halves.
Q2: How do you calculate quartiles in Excel using QUARTILE.EXC and QUARTILE.INC?
QUARTILE.EXC excludes minimum and maximum values when calculating quartiles; use values 1, 2, or 3 to get the first, second, or third quartile. QUARTILE.INC includes the entire data range; use values 0 through 4 to get minimum, Q1, median, Q3, and maximum. Both functions require your data range and a quart parameter.
Q3: What do the box edges represent in a box plot?
The box edges indicate the first quartile (Q1) on the left and the third quartile (Q3) on the right. The distance between these edges represents the interquartile range, which measures how spread out the middle 50% of your data is. This visual representation helps identify data variability and distribution shape.
Q4: How are minimum and maximum values shown in a box plot?
Extreme points in a box plot show the minimum and maximum values in your selected data range. These are displayed as the endpoints of the whiskers extending from the box. In Excel, use the MIN and MAX functions to determine these values before creating your box plot visualization.
Q5: What does the interquartile range measure in your data?
The interquartile range (IQR) measures data spread by calculating the difference between the third quartile (Q3) and first quartile (Q1): IQR = Q3 - Q1. This value represents the range containing the middle 50% of your observations, providing insight into data concentration and variability independent of extreme values.
Q6: How do outliers appear in an Excel box plot?
Outliers appear as individual dots beyond the whiskers in an Excel box plot when extreme values exist in your dataset. These points represent data values that fall outside the typical range, helping you quickly identify unusual observations. Box plots are valuable for detecting data skewness, variability, and outliers in statistical analysis.
Q7: What steps do you follow to create a box plot in Excel?
To create a box plot in Excel, select your datasets, choose Insert, select Insert Statistic Chart, then select Box and Whisker. Excel automatically calculates the median, quartiles, and minimum and maximum values. The resulting visualization displays the IQR as the box, whiskers as the data range, and any outliers as dots beyond the whiskers.