LWP | MAXIFSで得意先ごとの最大受注金額を調べる
MAXIFSで得意先ごとの最大受注金額を調べる
図解1|一行は一つの受注

図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|全体の最大と得意先の最大は違う

図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|金額の列と条件の列を組み合わせる

図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|複数の条件をすべて満たす行に絞る

図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円と該当なしを分ける

図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|最大の金額から元の受注を確認する

図6|MAXIFSが返すのは最大の金額です。同じ最大額の受注が複数あることも確認します。
解説6|最大額が同じ受注を一件に決めつけない
金額と受注番号を組にして確認する
元の受注表で、得意先C001かつ確定の最大額は10400円です。この金額に当たるのはJ002とJ004の二件です。MAXIFSの結果が一つの数値でも、該当する受注が一件だけとは限りません。
受注番号 |
得意先 |
状態 |
受注金額 |
|---|---|---|---|
J002 |
C001 |
確定 |
10400 |
J004 |
C001 |
確定 |
10400 |
「同じ額が二件あるなら、両方の受注を確認しよう」とナマズさんが言います。ココさんも「この式が返したのは金額なので、受注番号は元の表で確かめましょう」と答えます。少数行の例なら、得意先・状態・金額を元表と順に照合できます。行数が多い表では、フィルタ表示などで確認する行を絞ります。
結果を報告する前に
今回の結果は「得意先C001の確定受注は三件あり、最大額は10400円。その金額の受注はJ002とJ004の二件」です。対象の条件、件数、最大額、該当する受注番号を合わせると、何を比較した結果かを相手へ伝えられます。
条件を変えて使うときも、最大値の対象範囲、条件の列、同じ条件で数えた件数を確認します。表に新しい行を追加したら、数式の範囲へ含まれているかも確かめてください。
出典
Google公式 MAXIFS、Google公式 COUNTIFS。Googleスプレッドシートの教科書 V057の「範囲の集計と順位を扱うグループ」をもとに、受注単位の教材例として構成しました。
