こんにちは、私よりも Excel に詳しい皆さん! 何かお力添えいただけるとありがたいです。簡潔に説明します。
3 列のテーブルがあります。月 (1、2、3 など)、営業担当者名、売上です。各行は、その月に営業担当者が得た売上額です。
次のように配置されます: 最初の 1000 行はすべて Jan/1、次の 1000 行はすべて Feb/2、など。
過去 3 か月間の各営業担当者の平均売上金額を取得する必要があります。ただし、過去 3 か月間のうち 1 か月が空白の場合は、3 か月の平均が必要になります。
したがって、たとえば 9 月の空白の結果を認識し、代わりに次に最近の結果 (8 月、7 月など) を取得する数式が必要です。そうすれば、常にその営業担当者の最新の 3 か月の結果の平均が算出され、1 つまたは 2 つの結果だけの平均が算出されることはありません。
今のところ、このために非常にシンプルなピボット テーブルがあります。この問題に気付くまでは、私の目的にうまく適合していました :(
これを実行する方法 (ピボット テーブルなど) はありますか? 営業担当者は 1,000 人以上いるので、手動では実行できません。何らかの理由で 1 か月間に数字がない人がたくさんいるので、その点を考慮して計算を行う必要があります。
ご協力いただきありがとうございます。他に追加できる内容や不明な点があればお知らせください。
答え1
空白のない直近3か月の平均を計算する良い方法は知りませんが、ここにとてもハッキーなやり方です。これにより、これを達成するためのより良い方法が生まれるかもしれません。
生データでテーブルを作成し、月 (降順) と人別に並べ替えます。
次に、Sales 列をフィルターし、空白を選択解除します。次に、それらの内容を別の領域にコピーして貼り付けます。
次に、「順位」列を作成します。セル D2 の数式で、各人の各月を順位付けします (最新のものは 1 など)。
=IF(B2=B1,D1+1,1)
次に、個別の人物 (列 F) を含む別の領域と、その人物の最新の 3 か月のプレースホルダー列 (ラベル 1、2、3 の列) を作成します。
セル G2 の数式:
=SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)
それを列タイトル 3 までドラッグして下にドラッグします。最後に、平均の数式を作成します。
答え2
並べ替え、フィルタリング、コピーを行わずに元のデータをそのまま残す別のアプローチを採用すると、次に示すように 3 つのヘルパー列がデータ リストに追加されます。
カラム行1列目から始まる単純なインデックスシリーズです人2と同じです人しかし、IF関数を使用して、ゼロの行にnull文字列を挿入します販売。Mカウントカウントを提供します。人、0以外の月販売。
Mカウント次に示すように、各営業担当者の行を含む結果リストを操作します。
結果リストの列月は、各営業担当者のデータリストで売上高がゼロでない月の数を単純に数えます。セルI2の数式は
=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")
行M1を示しています行データリスト内の各営業担当者の売上高がゼロでない最初の月に対応する値。セルJ2の数式は
=MATCH(H2,$E$2:$E$21,0)
コラム月1、月2そして月3列内の月番号はMカウント（データリストの）3か月平均を計算するために使用される最初の、2番目と3番目の月に対応し、営業1、営業2そして販売3これらはこの 3 か月間の売上額です。平均計算された3か月平均です。
セルK2、L2、M2の式はそれぞれ
=I2-1
=K2+1
=L2+1
セルN2の数式は
=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)
そして、Mカウントデータ リストの列 (範囲$F$2:$F$21、次を参照)。セル N2 はセル O2 と P2 にコピーされます。
範囲 I2:Q2 は、結果リストの後続のすべての行にコピーできます。
データリスト - 列Mカウント
セルF2の数式は
=IF(C2>0,1,0)
これにより、セルに 0 または 1 が入ります。0 は、セル E2 の人物がセルに表示されている月に売上がなかったことを示しB2、1 は、その人物にとって売上があった最初の月であったことを示します。
セルF3の数式はかなり複雑で、
=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))
最初のIFのTRUE部分は、行が次の値に該当する場合は0を返します。販売ゼロです。
さもないと、販売がゼロでない場合、2番目のIFがトリガーされます。この2番目のIFでは、人セルE3の値が結果テーブルで調べられ、 行M1値（データリスト内の最初の月の販売に対応する行番号）は、行セル D3 の値の場合、行 3 はセル E3 で識別される人物の売上高がゼロでない最初の行に対応することを意味します。
もし、行M1そして行値が異なる場合、データ テーブルの行 3 は、セル E3 の担当者の (最初の月の後の) 次の月の売上に対応します。この場合、月番号は、データ リストの前の行のセル E3 の営業担当者の列 F の最大値に 1 を加算することによって取得されます。この最大値を決定するために、MAXIFS 関数が使用されます。
セル F3 の数式は、データ リストの次の行にコピーされます。数式で相対アドレスと絶対アドレスを適切に使用することで、MAXIFS 関数はコピー内の適切なセル範囲を使用するようになります。
ノート
- MAXIFSを使用するにはExcel 2019以降が必要です
- (i) データは時系列になっており、(ii) 営業担当者と月の組み合わせごとに、売上データの非ゼロ行が最大で 1 行あることが前提となります。
- 画像では、青いフォントは数式を示し、黒いフォントは静的な値を示します。