God dag！Lone Wolf会計士です。
今回は少々テクニック的な話になりますが、Median関数とIf関数を組み合わせる方法を紹介します！
Median関数は数値データの中央値を計算する関数ですが、「ある条件を満たした場合の中央値」を計算したいときに条件を入れるパラメータがMedian関数自体にはないため、このテクニックが有用になります。
それではいってみましょう。
※なお、Average関数とMedian関数についての概略は以下の記事で紹介しております。
このブログはこんなブログ
このブログは「グローバル×会計専門性」を目指す人を対象に、「米国公認会計士の魅力やキャリア情報、及び「グローバル×会計専門性」というキャリアを歩むうえで役に立つノウハウが得られる」というコンセプトで運営しています。
前提知識はゼロかつ初心者向けの記事のため、「グローバル×会計専門性」のキャリアを目指すつもりがない方でも、会計のエッセンスの理解に役立つ記事の執筆を心掛けています。
ご参考にして頂ければ幸いです。
Median関数＋If関数
Median関数に条件式を加えたい場合、If関数を組み合わせることで実現できます。
具体的には、以下のような式を組むことになります。
Median (If(条件式, 中央値を計算したい数値の範囲))
以下の具体例で考えてみましょう。
架空の会社の2023年8月の売上が以下の通りだとします。
この8月の売上のサンプルとして、一日平均するとどのくらい売上が見込めるのか？について考えてみます。
まず、平均値 vs 中央値の選択ですが、客単価が100に近いものもあれば、一桁の場合もあります。また、来客数が異常な日（8月30日）もありますよね。
よって、外れ値がありそうなので今回は中央値を採用してみます。
普通に中央値を計算すると、以下の通り344になります。
ここで、この会社はお盆休みを取っていることに気づきます。具体的には8月10日～8月20日です。
さすがに休みの時の売上０は除外して考えたいよね…
以上から、8月10日～8月20日の期間は除外して中央値を計算してみます。
このように、Median関数に条件を加えるため、If関数の条件式を挿入し、
「D列が0でない場合（＝来客数が0でない場合）」
という条件を設定して、売上高の中央値を計算していることがわかりますね。結果は以下の通りです。
このように、中央値の計算に際して条件式を組み込むことができますので、ご参考としてください。
（補足）Averageif関数
補足となりますが、「ある条件を満たした場合の平均値」についてはAverageif関数というものが用意されています。
関数の構造は以下の通りです。
Averageif (範囲、検索条件、平均範囲)
指定の範囲の中で、ある条件を満たした場合、指定の範囲を平均する、という構造となります。
では実際に使ってみましょう。
上の例で考えてみます。同様にお盆休みを除いた売上の平均を求めたい場合、Averageif関数は以下の通りに使います。
D列の中で来客数が0でない日の売上高を平均してください、という計算式になっていますね。
結果は以下の通りです。
やはり外れ値がある場合、平均値はその影響を大きく受けていることがわかります。このような場合は中央値の採用が妥当ですね。
それでは今回はこのあたりで。Vi ses！
コメント