月間中央値の導出（Microsoft Excel）

ジョンは、約60年にわたる毎日のデータを含む巨大なワークシートを持っています。彼は、データがカバーする期間の各月の中央値を計算する式を考え出したいと考えています。

解決策を提案する前に、いくつかの仮定を立てることが最善です。このヒントの目的のために、日次データが列AとBにあると仮定します。列Aにはデータに関連付けられた日付があり、列Bにはそれらの各日付の実際のデータ値があります。さらに、セルA1とB1には、各列の見出しが含まれています。これは、実際のデータがほぼA2：B22000の範囲にあることを意味します。

数式を使いやすくするには、列Aと列Bの両方でデータの名前を定義する必要があります。日付の範囲（たとえば、A2：A22000）を選択し、AllDatesなどの名前を割り当てます。対応するデータの範囲（たとえば、B2：B22000）を選択し、同じプロセスを使用してAllDataなどの名前を作成します。

配列数式を使用して、実際の中央値を計算できます。これには、中央値を含む別のデータテーブルの設定が含まれます。セルE1に「月」、セルF1に「中央値」などの見出しを配置し​​ます。セルE2に、データセットの最初の月の最初の日（1940年1月1日など）を配置します。セルE3に、1940年2月1日など、1か月後の日付を入力します。これらの2つのセル（E2：E3）を選択し、データテーブルで必要な月数だけ塗りつぶしハンドルを下にドラッグします。

60年間のデータに空白がない場合は、セルF2に次の数式を入力します。

Ctrl + Shift + Enterを押して数式を完成させます。これにより、これが配列数式であることがExcelに通知されます。次に、F2の数式を、列Eに対応する月がある列Fの各セルにコピーできます。数式は、列Bの日付を分析し、年と月がセルE2に入力した日付と等しいかどうかを分析します。の場合、中央値は対応するすべてのデータポイントから計算されます。

60年間のデータに空白がある場合（列Aに日付があり、列Bに対応する値がない場合）、式は空白をゼロ値として扱います。空白がある場合、中央値が歪む可能性があります。これを回避するには、空白の値をチェックして無視する別の配列数式を使用できます。

この方法で配列数式を使用する場合の注意点が1つあります。 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年のデータを処理する必要がある配列数式ではなく、それぞれが1か月分のデータを処理するだけで済みます。その結果、計算がはるかに高速になります。

もちろん、ほとんどの人にとって、このような膨大な量のデータを再編成するというアイデアは、夜に目を覚まし続けるのに十分です。代わりに、データを分析するためにまったく異なるアプローチを取ることができます。中央値は手動で計算するのが非常に簡単な統計関数であるため、このアプローチが可能です。データセットを並べ替えるだけで、データセット内のアイテムの数が奇数の場合は、中央のアイテムを選択します。アイテムの数が偶数の場合、2つの中間アイテムの平均を取ります。

データを分析する準備をするために、やるべきことがいくつかあります。

まず、各データポイントの月を一意に識別する方法があると便利です。セルC2に次の数式を配置します：

これにより、194001、194002、194003などの値が列Cに格納されます。これは一意の月の値です。ここで、データを列C、次に列Bで並べ替える必要があります。どちらの並べ替えも昇順で行う必要があります。これにより、データは最初に年/月で並べ替えられ、次に年/月内の値で並べ替えられます。

次に、データに小計を追加する必要があります。 [データ]メニューから[小計]を選択すると、[小計]ダイアログボックスが表示されます。列Cの変更ごとに小計を追加します。使用する関数はCountで、小計を列Bに追加します。[OK]をクリックすると、データの各月に1つずつ、720の小計が作成されます。範囲。それぞれが、その月にあったデータ項目の数を示します。

中央値を取得するには、セルD2に数式を追加します。

数式はセルB2の内容を調べ、「カウント」という単語が含まれている場合は、これが小計行であることがわかります。その場合、セルC2のカウントが奇数か偶数かを確認します。奇数の場合は、INDIRECT関数を使用して、その月の中央値を取得します。偶数の場合、その月の2つの中間値が合計され、半分に分割されます。

その結果、小計と同じ行に、各月の中央値が表示されます。したがって、データのアウトラインを折りたたむことができます（データの左側にあるアウトライン領域のプラス記号をクリックします）

これらの小計行のみを表示するようにします。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント（12727）は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice 365のExcelに適用されます。Excelの古いメニューインターフェイス用のこのヒントのバージョンは、次の場所にあります。

link月間中央値の導出。