月単位の売上データを集計する方法（年月を指定して該当商品の売上データを集計する方法 No.283）

指定年月以降の該当商品の売上データを計算する方法 No283

ある表の表 1 は、工場の製品の毎日の売上レポートです。 3 つの列があり、1 つは販売日用、1 つは製品名用、もう 1 つは対応する販売用です。ソースデータの行数は数万行と比較的多いです。ここでは、デモンストレーションのためにデータの一部を傍受します。

クエリ テーブルを設計する必要があります。表 2 の日付クエリに該当する日付を入力します。入力した日付に対応する年と月に販売された製品と、これらの製品の販売額の概要が下に表示されます。

その効果は以下の図 1 に示されています。

図1

この要件は、非常に典型的なフィルタリングおよび要約の要件です。クエリ条件には日付が 1 つしかないため、フィルタリング機能の複数条件フィルタリングが必要です。複数条件フィルタリングを使用する前に、日付から年と月を抽出する関数をネストする必要もあります。

商品概要のため重複は出来ません。クエリ日付に該当する製品をフィルタリングした後、製品の重複を排除する必要があります。ここでは重複を削除する機能が使用されます。

最後に、この重複排除された製品を使用して複数条件スクリーニングを実行し、合計してエントリクエリの日付、エントリ日付に対応する年と月に販売された製品、およびこれらの製品に対応する販売額の概要を取得します。

多条件スクリーニングの核心は条件を判断することです。ここでの要件は、日付を入力して対応する年と月を取得することなので、最初のステップは日付を抽出することです。皆様の理解を容易にするために、ここでは関数を段階的に説明します。

関数 1: =YEAR(B3:B19) を入力して、販売ソース データの日付の年を抽出します。

関数 2: =MONTH(B3:B19) を入力して、販売ソース データの日付の月を抽出します。

クエリ条件の日付の年を抽出するには、関数 3: =YEAR(I2) を入力します。

クエリ条件の日付の月を抽出するには、関数 4: =MONTH(I2) を入力します。

その効果は以下の図 2 に示されています。

図2

上記では、販売ソースデータの日付とクエリ日付が補助列の形式で抽出されています。ここで、クエリの日付に対応する製品をフィルタリングする必要があります。補助列が事前に用意されているため、フィルタリング条件は、クエリ日付に対応する年と月がソースデータの年と月と等しいということになります。

入力機能:

=FILTER($C$3:$C$19,(E3#=H3)*(F3#=I3))

機能説明:

クエリの日付に対応する年と月がソースデータの年と月と等しい製品の詳細

その効果は以下の図 3 に示されています。

図3

上記のデータは補助列を使用しており、重複した項目があります。この場合、重複する項目を削除する関数をネストし、補助列の数式をそれに代入する必要があります。

入力機能:

=UNIQUE(FILTER($C:$C,(YEAR($B:$B)=YEAR($I))*(MONTH($B:$B)=MONTH($I))))

機能説明:

クエリ日付条件を満たす年と月に販売された製品については、重複を削除し、一意の値を保持します。

その効果は以下の図 4 に示されています。

図4

上記で製品フィルターがクエリされているため、対応するクエリ日付の年と月をこの製品に追加して 3 条件フィルターを形成し、表 1 の売上を取得できます。

入力機能:

=SUM(FILTER($D$3:$D$19,(YEAR($B$3:$B$19)=YEAR($G$2))*(MONTH($B$3:$B$19)=MONTH($G$2))*($C$3:$C$19=$F5)))

機能説明:

数式は非常に長く見えますが、実際には 3 つの条件 (年、月、製品の条件) で構成されています。 3つの条件でフィルタリングされた販売量をSUM関数を使用して合計し、商品の合計販売量を算出します。

その効果は以下の図5に示されています。

図5

上記のケースは、FILTER 関数の威力を示しています。この関数が使用可能になる前は、複数条件の集計と合計を実行するために SUMPRODUCT 関数を使用する必要がある場合があります。 FILTER 関数と比較すると、この関数は理解しにくいかもしれません。

初心者は、FILTER 関数と他の関数のいくつかの固定された組み合わせについて詳しく学ぶことができます。

ROWS+FILTER: フィルタリング後の数をカウントします

SUM+FLTER: フィルタリング後の合計

SORT+FLTER: フィルタリング後に並べ替える

UNIUQE+FLTER: スクリーニング後の重複排除

TOROW+FLTER: フィルタリング後に行（水平方向）に変換します。一般的に、1 次元データと 2 次元データ間の変換に使用されます。

PMCの生産計画と運用をGu Geと一緒に一生勉強すれば十分でしょうか？

Gu Geプロジェクトをフォローする