MAXIFSで得意先ごとの最大受注金額を調べる

LWP | MAXIFSで得意先ごとの最大受注金額を調べる

MAXIFSで得意先ごとの最大受注金額を調べる

図解1|一行は一つの受注

淡水社の受注別集計表A1:D7。ココさんが一行を示し、ナマズさんが受注番号と金額を照合する

図1|商品ごとの明細を受注番号でまとめた表を使い、受注一件の金額を比べます。

解説1|何の金額を比べるかを先に決める

受注一件の金額を比べる

全国規模の中華食材卸「淡水社」で、営業のナマズさんが得意先ごとの大きな受注を調べています。「この得意先から、いくらの受注があったかを見たいんだ」。ココさんが、受注番号と金額の列を指します。「商品ごとの明細ではなく、受注ごとにまとまった金額で比べましょう」。

一つの受注に商品が三つあれば、受注明細には三行できます。そこで金額の最大値を求めると、受注全体ではなく、一つの商品明細の金額を比べてしまいます。今回は、明細の金額を受注番号ごとに合計済みの「受注別集計表」を使います。

入力例

行

A列

B列

C列

D列

1

受注番号

得意先

状態

受注金額

2

J001

C001

確定

7200

3

J002

C001

確定

10400

4

J003

C002

確定

30000

5

J004

C001

確定

10400

6

J005

C001

仮

24000

7

J006

C003

確定

6000

一行は一つの受注で、A列の受注番号は重複していません。B列は得意先コード、C列は受注の状態、D列は税抜金額です。D2:D7には空欄や文字列を混ぜず、数値を入力します。会社、コード、状態、金額はこの記事のための架空の教材条件です。

読むポイント

受注した時点の金額と、出荷や返品を反映した売上確定額は、集計の基準が異なります。今回は受注金額を比べます。明細を受注単位へまとめる処理や、売上額との照合は、この表を作る前の作業として分けておきます。

図解2|全体の最大と得意先の最大は違う

全体の最大30000円と得意先C001だけの最大24000円を比較する。ココさんが対象の四行を示し、ナマズさんが得意先を照合する

図2|最大値を求める前に、どの行を比較対象にするかを決めます。

解説2|MAXIFSは条件に合う行の中から最大値を返す

全体を比べると別の得意先も入る

受注金額の列だけをMAXで調べると、六件すべてが比較対象です。最大は、得意先C002のJ003にある30000円になります。C001からの受注を調べたいナマズさんには、別の得意先の金額が返っていることになります。

関数の役割

=MAX(D2:D7)

この式の結果は30000です。MAXは、ここではD2:D7の数値をまとめて比べます。

=MAXIFS(D2:D7,B2:B7,"C001")

MAXIFSでは、金額の範囲に加えて、条件を調べる範囲と条件を指定します。B列がC001の行に絞ると、比較する金額は7200、10400、10400、24000の四つです。結果は24000になります。

結果を比べる

比較対象

対象の件数

最大受注金額

すべての受注

6

30000

得意先がC001の受注

4

24000

読むポイント

「この24000円は仮受注だね。今回は確定した受注だけで比べたいな」とナマズさんが言います。ココさんはC列を確かめます。「それなら、得意先に加えて状態の条件も必要ですね」。得意先の条件だけでは、確定と仮は区別されません。

図解3|金額の列と条件の列を組み合わせる

F2の得意先コードC001、D2:D7の金額、B2:B7の得意先を式の引数に対応させる。ココさんは表と式を指し、ナマズさんは条件セルを変える

図3|同じ行の得意先と金額を組にして比べ、条件だけをセルで差し替えます。

解説3|条件をセルに置くと数式を共通にできる

検索条件

F2に調べたい得意先コードC001を入力し、H3に次の式を入力します。F列とH列は受注表の外に設けた確認用の欄です。

=MAXIFS($D$2:$D$7,$B$2:$B$7,F2)

関数の役割

最初の$D$2:$D$7が最大値を求める金額の範囲、次の$B$2:$B$7が得意先を判定する範囲です。最後のF2から条件のC001を読み取ります。2行目ならB2で条件を調べ、同じ行のD2を比較に加えるかどうかを決めます。

範囲は同じ行数・列数にそろえます。この例では、開始行も2行目、終了行も7行目にそろえています。大きさが同じでも行をずらすと、別の受注の得意先と金額を組み合わせてしまいます。

計算結果

F2に入力するコード

H3の結果

元表での確認

C001

24000

J005の金額

C002

30000

J003の金額

C003

6000

J006の金額

読むポイント

ココさんは「得意先を変えるときはF2を書き換えれば、式は同じです」と説明します。ナマズさんは、金額と受注番号を一件ずつ照合します。

この式の$D$2:$D$7と$B$2:$B$7は、列記号と行番号の前にドル記号を付けているので、コピーしても参照範囲が変わりません。一方、F2は相対参照なので、H3を一行下へコピーするとF3へ移ります。今回はH3に式を一つ置き、F2の値を変えて使います。データを8行目以降へ追加したときは、金額と条件の範囲をどちらも広げてください。

図解4|複数の条件をすべて満たす行に絞る

得意先C001かつ確定の三行を選び、最大10400円を返す。仮受注24000円と他の得意先30000円は対象外になる

図4|得意先と状態の両方が一致した行だけを比較します。

解説4|得意先と状態を対にして指定する

検索条件

F2にC001、F3に確定を入力します。F3は受注の状態を選ぶための条件セルです。H4には次の式を入力します。

=MAXIFS($D$2:$D$7,$B$2:$B$7,$F$2,$C$2:$C$7,$F$3)

関数の役割

MAXIFSでは、条件を調べる範囲と条件を一組ずつ追加します。この式では、B列がF2の得意先に一致することと、C列がF3の状態に一致することを、同じ行で調べます。どちらか一方だけの一致では、比較対象に入りません。

計算結果

受注番号

得意先

状態

金額

今回の対象

J001

C001

確定

7200

対象

J002

C001

確定

10400

対象

J004

C001

確定

10400

対象

J005

C001

仮

24000

対象外

対象の三件では、最大の受注金額は10400円です。J003の30000円は得意先が異なるため、J005の24000円は状態が異なるため、対象から外れます。

読むポイント

「C001なら、仮も含めると24000円。確定だけに絞ると10400円なんだね」とナマズさんが確認します。業務で比較したい条件を先に決め、その条件が式に入っているかを確かめましょう。確認後はF2をC001、F3を確定に戻して、以降の例を試します。

図解5|0円と該当なしを分ける

MAXIFSの0と同条件のCOUNTIFSの件数を比較する。C999は該当0件、別の確認例でJ006の金額だけを0にしたC003は該当1件になる

図5|最大値だけで判断せず、同じ条件に合う受注が何件あるかも確かめます。

解説5|該当する受注の有無はCOUNTIFSで調べる

関数の役割

MAXIFSは条件に合う行がないときも0を返します。H4が0になっただけでは、金額が0の受注があるのか、受注が見つからなかったのかを区別できません。H5に、同じ条件を使って件数を数える式を置きます。

=COUNTIFS($B$2:$B$7,$F$2,$C$2:$C$7,$F$3)

この式では、得意先と状態が両方一致した行を数えます。H4のMAXIFSと、条件範囲・条件セルをそろえることが大切です。D列の金額は、すべて数値で入力済みという前提で比べます。

計算結果

確認する状態

H4の最大値

H5の件数

読み方

元表でC001・確定

10400

3

対象は三件

元表でC999・確定

0

0

該当する受注なし

D7だけ0にした表でC003・確定

0

1

該当する受注の金額が0

三つ目は数式の挙動を比べるためだけの確認例です。F2をC003にし、D7を6000から0に変えて試します。無料取引として承認されたという業務設定ではありません。確認後はD7を6000、F2をC001へ戻し、F3は確定のままにします。

読むポイント

件数が0なら、比較対象の受注がありません。コードや状態の指定、参照範囲を確かめます。件数がある場合は最大値を読み、0が返ったなら元の金額を確認します。件数があることだけでは、金額列の空欄や文字列まで正しいとは分かりません。実務の表では、数値で入力されているかも別に確認してください。

図解6|最大の金額から元の受注を確認する

C001の確定受注で最大となる10400円はJ002とJ004の二件。ココさんとナマズさんが同額の二行と受注番号を確認する

図6|MAXIFSが返すのは最大の金額です。同じ最大額の受注が複数あることも確認します。

解説6|最大額が同じ受注を一件に決めつけない

金額と受注番号を組にして確認する

元の受注表で、得意先C001かつ確定の最大額は10400円です。この金額に当たるのはJ002とJ004の二件です。MAXIFSの結果が一つの数値でも、該当する受注が一件だけとは限りません。

受注番号

得意先

状態

受注金額

J002

C001

確定

10400

J004

C001

確定

10400

「同じ額が二件あるなら、両方の受注を確認しよう」とナマズさんが言います。ココさんも「この式が返したのは金額なので、受注番号は元の表で確かめましょう」と答えます。少数行の例なら、得意先・状態・金額を元表と順に照合できます。行数が多い表では、フィルタ表示などで確認する行を絞ります。

結果を報告する前に

今回の結果は「得意先C001の確定受注は三件あり、最大額は10400円。その金額の受注はJ002とJ004の二件」です。対象の条件、件数、最大額、該当する受注番号を合わせると、何を比較した結果かを相手へ伝えられます。

条件を変えて使うときも、最大値の対象範囲、条件の列、同じ条件で数えた件数を確認します。表に新しい行を追加したら、数式の範囲へ含まれているかも確かめてください。

出典

Google公式 MAXIFS、Google公式 COUNTIFS。Googleスプレッドシートの教科書 V057の「範囲の集計と順位を扱うグループ」をもとに、受注単位の教材例として構成しました。