【難問です・・】エクセル関数について

質問

どうやっても解決しませんでしたので、
投稿させていただきます。
ご教授頂ければ幸いです。

下記の様に、3種類の伝票において、それぞれの発送代金がありますが、
実際は伝票1種類でまとめて発送代金を計上しますので、
伝票番号1000011を例にすると、まとめて1,000円になると致します。
個別の発送代金では499円+498円なので、3円の相違があるのですが、
このように相違が出た場合には、【図2】のように、それぞれの個別の伝票番号において、
最も発送代金が大きい行に差異を加算することで調整したいのですが、
この差異の調整が上手くいきません・・・。

【図1】の状態までは関数でできているのですが、一番左の列に
差異解消の為の関数を入れるにはどのような関数を用いたら良いのでしょうか？
同じ伝票番号ごとの最大発送代金の行を抽出するのと、
その場合に、差異を加算する（同じ行とは限らない）のは頭で考える分にはすぐなのですが、
エクセルでの再現は私には難しすぎます・・・
何卒、宜しくお願い致します。

【図1】
伝票番号　発送代金　　　　伝票番号一口の　　一口の発送代金と
　　　　　　　　　　　　　　　　　　　　発送代金　　　　個別トータルの差異
1000011　　　499
1000011　　　498　　　　　　　　　　1,000　　　　　　　　　　3
1000022　　　995
1000022　　　998
1000022　　　997　　　　　　　　　　3,000　　　　　　　　　　10
1000033　　　1,995
1000033　　　1,990　　　　　　　　 4,000　　　　　　　　　　 15

【図2】
伝票番号　発送代金　　　　伝票番号一口の　　一口の発送代金と　　　発送代金(差異解消)
　　　　　　　　　　　　　　　　　　　　発送代金　　　　個別トータルの差異
1000011　　　499　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　502　（← +3円）
1000011　　　498　　　　　　　　　　1,000　　　　　　　　　　3　　　　　　　　　498
1000022　　　995　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　995
1000022　　　998　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1,008　（← +10円）
1000022　　　997　　　　　　　　　　3,000　　　　　　　　　　10　　　　　　　　　997
1000033　　　1,995　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2,010　（← +15円）
1000033　　　1,990　　　　　　　　 4,000　　　　　　　　　　 15　　　　　　　　1,990

回答No.10

tom04 · ベストアンサー

No.1～3・7です。

＞「先頭の一つ」ではなく、「最後の一つ」にまとめて
＞加算することもできるのでしょうか？

というコトですので・・・
No.1の画像の配置通りで、データが3行目からあるとします。
Excel2007以降のバージョンではE3セルに
=IF(A3="","",IF(AND(B3=MAX(IF(A$3:A$1000=A3,B$3:B$1000)),COUNTIFS(A:A,A3,B:B,B3)=COUNTIFS(A$3:A3,A3,B$3:B3,B3)),B3+SUMIF(A:A,A3,D:D),B3))

Excel2003までのバージョンではE3セルに
=IF(A3="","",IF(AND(B3=MAX(IF(A$3:A$1000=A3,B$3:B$1000)),SUMPRODUCT((A$3:A$1000=A3)*(B$3:B$1000=B3))=SUMPRODUCT((A$3:A3=A3)*(B$3:B3=B3))),B3+SUMIF(A:A,A3,D:D),B3))

どちらも配列数式ですので、Shift＋Ctrl＋Enterで確定してみてください。

※　No.7の画像のようにデータが2行目以降にある場合は
E2セルに式を入力するようになりますが、
上記数式の「3」の部分をすべて「2」に置き換えてもらえば大丈夫のはずです。

※　好奇心旺盛の方であれば、最大値が3個以上ある場合2番目に入れたい！という欲求が出てくると思いますが、
それに対応しようとするとIF関数で条件を分岐しなければならなくなり、かなり長い数式になってしまいますので
とりあえずこの程度で・・・m(_ _)m

お礼

No.1様

「最後の一つ」にまとめての加算もできました。
ひとりではこの式には絶対たどりつけなかったと思います。
本当にありがとうございました！！

回答No.9

kagakusuki

御質問文には、

>【図1】の状態までは関数でできているのですが、

とありましたので、「伝票番号一口の発送代金」や「一口の発送代金と個別トータルの差異」を求める関数に関しては、質問者様が既に自力で関数を考え付いているものだと思っておりましたので、特に触れずにおりましたが、回答No.5様の御回答に対するお礼の中に、

>確かにご提言いただきましたD列での差異算出ができれば、解決します。

と書かれておられるという事は、「伝票番号」と（差異未解消の）「発送代金」のデータのみから、「伝票番号一口の発送代金」や「一口の発送代金と個別トータルの差異」を求める関数とはなっていないという事なのでしょうか？
　それでしたら、「伝票番号一口の発送代金」を求める関数の一例は次の様なものとなります。

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF($A$1:INDEX($A:$A,ROW()),INDEX($A:$A,ROW()))=COUNTIF($A:$A,INDEX($A:$A,ROW())),ROUNDUP(SUMIF($A:$A,INDEX($A:$A,ROW()),$B:$B),-3),""))

　尚、この関数は、1000円単位で切り上げる関数となっていますが、もし、1000円単位で四捨五入する場合には、次の様な関数となります。

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF($A$1:INDEX($A:$A,ROW()),INDEX($A:$A,ROW()))=COUNTIF($A:$A,INDEX($A:$A,ROW())),ROUND(SUMIF($A:$A,INDEX($A:$A,ROW()),$B:$B),-3),""))

　又、1000円単位ではなく100円単位で処理する場合には、上記の関数の中の「-3」の所を「-2」に置き換えて下さい。

　それから、「一口の発送代金と個別トータルの差異」を求める関数一例は次の様なものとなります。

=IF(ISNUMBER(INDEX($C:$C,ROW())),INDEX($C:$C,ROW())-SUMIF($A:$A,INDEX($A:$A,ROW()),$B:$B),"")

　これらは、通常のワークシート関数ですので、自動的に値が表示されます。
　又、Excel2002でも使用可能な関数です。

お礼

順を追った大変分かりやすいご説明をいただき、
ありがとうございました。
ご質問本文の通り、【図1】の状態までは関数でできておりました。ただし、差異を同じ伝票番号の一番下の行に表示するというやり方でしたので、ご回答者様のご提案の通り、
そもそも差異を該当の行に始めから算出できれば分かりやすいと思いました！本当にありがとうございました。

回答No.8

kagakusuki

回答No.4です。

>早速試させていただきましたところ、「入力した式は正しくありません」と出てしまい、数式中の6つめのROWがハイライトされます。
>エクセル2002に起因するエラーでしょうか？

　回答No.2様が

>※　COUNTIFS関数を使用していますので、Excel2007以降での対応となります。

と仰っておられる事に対して、質問者様の御補足では何も触れておられなかったため、質問者様が御使いのExcelはExcel2007以降のものだと判断し、回答No.4の関数を御伝え致しましたが、回答No.4の投稿後になってから回答No.3に対して付けられた御補足によりますと、現在手元で使っておられるExcelはExcel2002なのですね。
　Excel2007よりも前のバージョンのExcelでは、ネスト（関数の中に関数を何重の入れ子にしているかを表す数）のレベルが7レベルまでの関数しか使う事が出来ません。
　回答No.4の関数のネストレベルは8ですので、Excel2002では使う事が出来ません。
　一方、Excel2007からはネストレベルが64まで可能となっていますので、通常使用するのがExcel2007という事でしたら、そちらで確認して頂いた方が良いと思います。

　尚、Excel2003以前のExcelで同じ事を行う場合には、E2セルに入力する関数を次の様なものとして下さい。

=IF(AND($A2<>"",ISNUMBER($B2)),$B2+IF(ROW()-MATCH($A2,$A:$A,0)+1=MATCH(MAX(OFFSET(INDEX($B:$B,MATCH($A2,$A:$A,0)),,,COUNTIF($A:$A,$A2))),OFFSET(INDEX($B:$B,MATCH($A2,$A:$A,0)),,,COUNTIF($A:$A,$A2)),0),SUMIF($A:$A,$A2,$D:$D),0),"")

　これは、回答No.4の関数の中の「INDEX($A:$A,ROW())」という部分を「$A2」に、「INDEX($B:$B,ROW())」という部分を「$B2」に、それぞれ置換したものです。
　それでは何故回答No.4では、最初から「$A2」や「$B2」にしておかなかったのかと申しますと、「$A2」や「$B2」とした場合には、元データであるA列のセルやB列のセルに対して、切り取りや挿入、並べ替え等のセルの位置が変わってしまう様な操作を行った際に、セルの位置関係がずれてしまい、関数によって得られる値がおかしくなってしまう事があるためです。
　一方、セルの指定方法として「INDEX($A:$A,ROW())」や「INDEX($B:$B,ROW())」等の様にしますと、A2セルやB2セルといった特定のセルを指定している訳ではなく、「A列の中で『この関数が入力されているセル』と同じ行番号の所にあるセル」、「B列の中で『この関数が入力されているセル』と同じ行番号の所にあるセル」という指定方法になりますから、元データであるA列のセルやB列のセルに対して、切り取りや挿入、並べ替え等のセルの位置が変わってしまう様な操作を行った場合でも、正しい結果が得られるからです。

　それから、回答No.4の関数の動作確認をExcel2002で行われるのでしたら、回答No.4の関数の中の一部である、ネストのレベルが深い所に記述されている関数を作業列に移しておき、その作業列で得られた結果を利用して、回答No.4の関数の中のネストのレベルが浅い所に記述されている関数による処理を行う様にすれば良い訳です。
　具体的には、まず、回答No.4の関数である

=IF(AND(INDEX($A:$A,ROW())<>"",ISNUMBER(INDEX($B:$B,ROW()))),INDEX($B:$B,ROW())+IF(ROW()-MATCH(INDEX($A:$A,ROW()),$A:$A,0)+1=MATCH(MAX(OFFSET(INDEX($B:$B,MATCH(INDEX($A:$A,ROW()),$A:$A,0)),,,COUNTIF($A:$A,INDEX($A:$A,ROW())))),OFFSET(INDEX($B:$B,MATCH(INDEX($A:$A,ROW()),$A:$A,0)),,,COUNTIF($A:$A,INDEX($A:$A,ROW()))),0),SUMIF($A:$A,INDEX($A:$A,ROW()),$D:$D),0),"")

の中の

MAX(OFFSET(INDEX($B:$B,MATCH(INDEX($A:$A,ROW()),$A:$A,0)),,,COUNTIF($A:$A,INDEX($A:$A,ROW()))))

という部分（伝票番号が同じものの中における、最も高い発送金額を求める関数）を取り出し、それを、その部分が動作する条件である

IF(AND(INDEX($A:$A,ROW())<>"",

と組み合わせた

=IF(AND(INDEX($A:$A,ROW())<>"",ISNUMBER(INDEX($B:$B,ROW()))),MAX(OFFSET(INDEX($B:$B,MATCH(INDEX($A:$A,ROW()),$A:$A,0)),,,COUNTIF($A:$A,INDEX($A:$A,ROW())))),"")

という関数を、適当な列（ここでは仮にG列を使う事にします）の2行目以下に入力しておきます。
　そして、E2以下には、回答No.4の関数の中の

MAX(OFFSET(INDEX($B:$B,MATCH(INDEX($A:$A,ROW()),$A:$A,0)),,,COUNTIF($A:$A,INDEX($A:$A,ROW()))))

の部分を、「G列の中で『この関数が入力されているセル』と同じ行番号の所にあるセル」の値を返す関数である

INDEX($G:$G,ROW())

に置き換えた、

=IF(AND(INDEX($A:$A,ROW())<>"",ISNUMBER(INDEX($B:$B,ROW()))),INDEX($B:$B,ROW())+IF(ROW()-MATCH(INDEX($A:$A,ROW()),$A:$A,0)+1=MATCH(INDEX($G:$G,ROW()),OFFSET(INDEX($B:$B,MATCH(INDEX($A:$A,ROW()),$A:$A,0)),,,COUNTIF($A:$A,INDEX($A:$A,ROW()))),0),SUMIF($A:$A,INDEX($A:$A,ROW()),$D:$D),0),"")

という関数を入力して下さい。
　これで、Excel2002を使って、回答No.4の関数の動作確認を行う事が出来ます。

お礼

大変ご丁寧に教えていただきましてありがとうございました。
普段は2007verを使用しているのですが、先週末のみ、
2002verの環境しかありませんでしたので、問題を複雑にしてしまいました。ネスト等色々と違いがあることを勉強させていただきました。本当にありがとうございました。

回答No.7

tom04

No.1～3です。

現在の環境がExcel2002というコトですので、
↓の数式にしてみてください。（やっぱり配列数式です）
=IF(A2="","",IF(AND(B2=MAX(IF(A$2:A$1000=A2,B$2:B$1000)),SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))=1),B2+SUMIF(A:A,A2,D:D),B2))

尚、前回は2行目までが項目行で3行目からのデータが前提の数式でしたが、
↓の画像のようにデータは2行目以降にあるとします。

おそらくExcel2003までのバージョンでも大丈夫だろうと思います。m(_ _)m

回答No.6

bunjii

>具体的には、＃NUM!エラーになってしまいます。
>エクセル2002の環境だからでしょうか？
Excel 2007以降の場合はそのままで使えると思いますが、Excel 2003以前のバージョンでは列全体の指定に無理があるようです。
対処法として2行目から必要行数の最大値にしてみると解決すると思います。
A:AをA$2:A$100000のようにA列からC列の指定範囲を変更してください。

回答No.5

bunjii

「一口の発想代金と個別トータルの差異」をD列に自動で算出する方法を提言します。
差異の金額は発想代金の最大値の行にしましたので「（差異解消）」では発送代金と差異を加算したもので良いことになります。

=IF(A2="","",IF(B2=MAX(IF(A:A=A2,B:B,0)),SUMIF(A:B,A2,C:C)-SUMIF(A:B,A2,B:B),""))

この式は配列の値を返しますのでCtrl+Shift+Enterで確定します。
確定したとき数式バーには｛=IF(A2="","",IF(B2=MAX(IF(A:A=A2,B:B,0)),SUMIF(A:B,A2,C:C)-SUMIF(A:B,A2,B:B),""))｝のように表示されますので確認してください。

お礼

ご解答ありがとうございます。
確かにご提言いただきましたD列での差異算出ができれば、
解決します。

早速試してみたのですが、掲載いただいた図のようになりませんでした。
具体的には、＃NUM!エラーになってしまいます。
ちなみに、Ctrl+Shift+Enterで確定しない場合には、
D7の15円だけが正しく表示され、残りのD列はブランク
表示されます。エクセル2002の環境だからでしょうか？
普段使っているエクセルは2007ですので、週明けに再度
試してまたご報告させていただきます。
ありがとうございます。

回答No.4

kagakusuki

もし伝票番号が御質問文中の例の様に、同じ伝票番号は必ず連続して入力されている場合には、次の様な方法で発想代金を完全に自動的（「Shift＋Ctrlキー」の操作が不要）に表示させる事が出来ます。

　今仮に、伝票番号がA列に、発送代金がB列に、一口の発送代金と個別トータルの差異がD列に、それぞれ入力されていて、発送代金をE列に表示させるものとします。

　まず、E2セル（別にE2セルでなくともE列上の、どのセルでも構いませんが）に次の関数を入力して下さい。

=IF(AND(INDEX($A:$A,ROW())<>"",ISNUMBER(INDEX($B:$B,ROW()))),INDEX($B:$B,ROW())+IF(ROW()-MATCH(INDEX($A:$A,ROW()),$A:$A,0)+1=MATCH(MAX(OFFSET(INDEX($B:$B,MATCH(INDEX($A:$A,ROW()),$A:$A,0)),,,COUNTIF($A:$A,INDEX($A:$A,ROW())))),OFFSET(INDEX($B:$B,MATCH(INDEX($A:$A,ROW()),$A:$A,0)),,,COUNTIF($A:$A,INDEX($A:$A,ROW()))),0),SUMIF($A:$A,INDEX($A:$A,ROW()),$D:$D),0),"")

　そして、E2セルをコピーして、E3以下に貼り付けて下さい。

　これで、E列に差異を解消した発送代金が表示されます。

お礼

ご解答ありがとうございます。
早速試させていただきましたところ、「入力した式は正しくありません」と出てしまい、数式中の6つめのROWがハイライトされます。
エクセル2002に起因するエラーでしょうか？
通常使用しておりますエクセルは2007以降のものですので、
週明けに改めて試してみたいと存じます。
何卒よろしくお願いします。

回答No.3

tom04

続けてお邪魔します。

補足で
＞実は「最大値が複数ある場合」は、「按分」ではなく、
＞どれか1つ（例えば先頭の1つ）にまとめて加算したい・・・
というコトですので、

先頭の一つに加算する場合です。
アップしている画像通りの配置としてE3セルに
=IF(A3="","",IF(AND(B3=MAX(IF(A$3:A$1000=A3,B$3:B$1000)),COUNTIFS(A$3:A3,A3,B$3:B3,B3)=1),B3+SUMIF(A:A,A3,D:D),B3))

という数式にしてみてください。
※　前回同様、配列数式になります。

おそらくこれで最大値の最初に出現した行に加算されると思います。m(_ _)m

お礼

No.1様

ありがとうございました。
希望通りに動作致しました！

何といいますか、無駄のない美しい式に感じます・・
（きちんと中身が理解できている訳ではないのですが）

後学の為に宜しければ、是非、教えて頂きたいのですが、
「先頭の一つ」ではなく、「最後の一つ」にまとめて
加算することもできるのでしょうか？
興味本位なのですが、可能でしたら、その場合の計算式も
教えて頂ければ幸いです。
（甘えてばかりですみません。自分でも考えてみます）

取り急ぎ御礼まで
何卒、宜しくお願い致します。

補足

No.1様

早速ありがとうございます！
この週末に使えるエクセルの環境が2002なものですから、
週明けに再度ご教授いただいた数式を試して
みたいと思います。（早く試したいです＾＾）
また改めて結果をご報告させていただければ幸いです。
本当にありがとうございます。

回答No.2

tom04

No.1です！
投稿後に気づいたのですが、最大値が複数ある場合は按分しなければならないのでは？

その場合の方法です。
前回の画像通りの配置だとして、E3セルに
=IF(A3="","",IF(B3=MAX(IF($A$3:$A$1000=A3,B$3:B$1000)),B3+SUMIF(A:A,A3,D:D)/COUNTIFS(A:A,A3,B:B,B3),B3))

これも配列数式になりますので、操作方法は前回同様です。
これで同じ「伝票番号」に「最大発送代金」が複数あってもそれぞれに割り振りできると思います。
（割り切れない場合の端数の処理はしていません）

※　COUNTIFS関数を使用していますので、Excel2007以降での対応となります。

何度も失礼しました。m(_ _)m

補足

No.1様

駄目で元々と思い、投稿させていただいたのですが、
こんなに早く回答がいただけるとは本当にありがとうございます。

ご指摘の重要な点が、ひとつ抜けておりましたが、
実は「最大値が複数ある場合」は、「按分」ではなく、
どれか1つ（例えば先頭の1つ）にまとめて加算したいと
思っております。

ご教授いただいた式を参考に私も思案したいと思いますが、
もしまた教えていただけるようでしたら、
何卒よろしくお願いします。

PS
今回の投稿前にCOUNTIFやMUCH等色々考えたのですが、
全然検討違いで、嬉しい驚きです。
配列数式｛　｝も初めて使いました。
奥が深いです！

回答No.1

tom04

こんばんは！
すでみ画像「1」までは完成済みだというコトですので、
一例です。

↓の画像でE3セルに
=IF(A3="","",IF(B3=MAX(IF(A$3:A$1000=A3,B$3:B$1000)),B3+SUMIF(A:A,A3,D:D),B3))

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　E3セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これをオートフィルで下へコピー！
これで画像のような感じになります。

※　とりあえず1000行目まで対応できる数式にしていますが、
データ量が極端に多い場合、配列数式はPCにかなりの負担をかけてしまいますので
別の方法を考える必要があると思います。m(_ _)m