月ごと、支出カテゴリーごとに値を割り当てる

Question

データとして次の最初の表があります。2 番目の表のような結果を取得する方法はありますか。

開始月と終了月は mm-yyyy で表示されることに注意してください。

計算を明確にするために：

• 2 番目のテーブルには、いずれかのイベント間隔に含まれる各月の列があります。
• 各イベントの費用は、そのイベントの月間で均等に分割されます。

• この例では、経費カテゴリが複数のイベントに使用されていないことが示されていますが、そのような状況が発生する可能性もあります。その場合、特定のコスト カテゴリの経費は毎月集計されます。

  たとえば、両方のイベントに広告費がかかり、イベント A の広告費が 600 ドルだったとします。イベント A は 3 か月間のイベントなので、毎月の割り当て額は 200 ドルになります。したがって、表 2 の 4 月の広告費は、イベント A が 200 ドル、イベント B が 500 ドル、つまり 700 ドルになります。

Answer 1

率直に言って、最初の表は人間が読むには適しているかもしれませんが、コンピュータが計算したり統計をとったりするのには適していません。表2を取得するには、理想的には分割したものを取得するだけです。各月、各イベント、各カテゴリーの費用(そしてそれらをいくつかのヘルパー セルに保存します)、それに応じて合計します。分割された費用を取得するには、まず、特定の月が時間の範囲内にあるかどうかのフラグを取得する必要があります。

12 か月必要だと想定しています。また、開始月のセルにはその月の最初の日の日付 (たとえば、B22017 年 4 月の場合は 2017-04-01) が入り、それに応じて終了月のセルにはその月の最終日の日付 (たとえば、2017 年 6 月の場合は 2017-06-30) が入ると想定していますC2。

1. フラグ:H1から をS1各月の日付として使用します (つまり、01/2017 (2017-01-01)、02/2017 (2017-02-01)、...、12/2017 (2017-12-01))。その月が範囲内にある場合は、列から の左側のセルを のフラグとして使用しますH。SではH2、数式 を使用します=AND($B2<=H$1,H$1<=$C2)。$注意:セルへの絶対参照を設定して簡単にコピーできるようにするには、数式内のの使用方法が異なることに注意してください。これは、現在の月 (現在の列の一番上の行) が開始月と終了月の間にある場合は true を返し、そうでない場合は false を返すことを意味します。次に、この数式を右の列までコピーしS、イベントの最後までコピーします。

2. カウント: 列をT、経費を分割する月のカウンターとして使用します。 の数式はT2です=COUNTIF(H2:S2,TRUE)。数式を下にコピーします。

3. 各カテゴリーの分割費用:再度、次の式での の使用に注意してください$。

   3.1 1位: 列UからAF(12 列の最初のグループ) を 1 番目のカテゴリ (つまり「広告」) の分割費用として使用します。 の式はU2で=IF(H2,$E2/$T2,0)、その月のフラグが true (つまりその月が範囲内) の場合は分割費用 (広告費用/月数) を返し、それ以外の場合はゼロを返します。次に右にコピー (列 までAF) し、下にコピーします。

   3.2 第2カテゴリー: 列AGからAR(12 列の 2 番目のグループ) を 2 番目のカテゴリ (つまり「フェアとセミナー」) の分割費用として使用します。 の式はAG2です=IF(H2,$F2/$T2,0)。次に、右 ( までAR) にコピーし、下にコピーします。

   3.3 3番目のカテゴリー: 列ASからBD(12 列の 3 番目のグループ) を 3 番目のカテゴリ (つまり「宿泊費」) の分割費用として使用します。 の式はAS2です=IF(H2,$G2/$T2,0)。次に、右 ( までBD) にコピーし、下にコピーします。

4. まとめると表 2 では、各月のすべてのイベントに対応する費用を合計します。