导出每月中位数值（Microsoft Excel）

约翰有一个庞大的工作表，其中包含约60年的每日数据。他想提出一个公式，该公式将计算数据涵盖的时间段内每个月的中位数。

在提出解决方案之前，最好先提出一些假设。就本技巧而言，我们假设每日数据在A列和B列中。A列是与数据关联的日期，B列是每个日期的实际数据值。此外，单元格A1和B1包含每列的标题。这意味着实际数据大约在A2：B22000的范围内。

为了使您的公式更易于使用，您应该在A列和B列中都定义数据的名称。选择日期范围（例如A2：A22000）并为其指定名称，例如AllDates。选择相应数据的范围（例如B2：B22000），然后使用相同的过程创建一个名称，例如AllData。

您可以使用数组公式来计算实际中位数。这涉及建立另一个包含中位数的数据表。在单元格E1中放置标题，例如“月”，在单元格F1中放置“中位数”。在单元格E2中，放置数据集第一个月的第一天，例如1/1/1940。在单元格E3中，输入一个月后的日期，例如2/1/1940。选择这两个单元格（E2：E3），然后将填充手柄向下拖动数据表中所需的月数。

如果您的60年的数据中没有空白，请在单元格F2中输入以下公式：

通过按Ctrl + Shift + Enter最终确定公式，这将告诉Excel这是一个数组公式。然后，您可以将F2中的公式复制到F列中每个在E列中具有相应月份的单元格中。该公式分析B列中的日期，以及年和月是否等于您在E2单元中放置的日期，则根据所有相应的数据点计算中位数。

如果您的60年数据中有空白（A列中的日期，B列中没有相应的值），则该公式将空白视为零值。如果确实有空格，则可能会导致中间值偏斜。要解决此问题，您可以使用其他数组公式来检查并忽略任何空白值：

以这种方式使用数组公式有一个警告。如果您有60年的数据，其中包含大约22,000个单个值，那么那仍然是很多个月：其中大约720个。这意味着您要创建720个数组公式，每个公式都分析22,000个数据值以得出答案。这需要进行大量的计算，因此无论何时重新计算工作表，您都会注意到Excel的响应速度变慢。

如果呆滞成为问题，那么您可以考虑重新组织原始数据，以便工作表的每一行代表一个月。 A列可能包含该行的月份（1/1 / 1940、2 / 1 / 1940、3 / 1/1940等），而B：AF列则是每个月的1到31天。然后，表中的相交单元格可以包含该月每天的数据点，并且您可以使用AG列中的MEDIAN函数来计算每个月的中位数。仍然可以得到720个公式，但是这些都是常规公式，每个公式只需要处理一个月的数据，而不是需要每个数组处理60年数据的数组公式。结果是更快的计算。

当然，对于大多数人而言，重组如此大量数据的想法足以使您彻夜难眠。相反，您可以采用完全不同的方法来分析数据。这种方法之所以可行，是因为中位数是一种非常容易手动计算的统计函数。您只需对数据集进行排序，如果数据集中的项目数为奇数，则选择中间项目。如果项目数是偶数，则取两个中间项目的平均值。

为了准备分析数据，需要做一些事情。

首先，使用某种方式唯一标识每个数据点的月份将很方便。在单元格C2中，放置以下公式：

这导致将诸如194001、194002、194003等的值存储在列C中。这是唯一的月份值。现在，您应该按C列然后按B列对数据进行排序。这两种排序都应按升序进行，这样最终可以使数据首先按年/月，然后按年/月内的值排序。

接下来，您需要向数据中添加小计。从“数据”菜单中选择“小计”，这将显示“小计”对话框。您想要在C列的每次更改中添加一个小计。要使用的功能是Count，并且要将小计添加到B列中。单击OK时，您将得到720小计，数据中每个月一个范围，每个都显示该月有多少个数据项的计数。

要获取中位数，请向单元格D2中添加一个公式：

该公式检查单元格B2中的内容，如果它包含单词“ Count”，则它知道这是小计行。在这种情况下，它将检查以查看单元格C2中的计数是奇数还是偶数。如果是奇数，则使用INDIRECT函数来获取该月的中位数。如果是偶数，则将月份的两个中间值相加并分成两半。

结果是您现在有了每个月的中位数，与小计在同一行。这样，您可以折叠数据的轮廓（单击数据左侧轮廓区域中的加号）

因此您只显示这些小计行。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧（12727）适用于Microsoft Excel 2007、2010、2013、2016、2019和Office 365中的Excel。您可以在此处为Excel的较早菜单界面找到此技巧的版本：

导出每月中位数值。