営業所ごと相殺処理について（excel）

質問

仕事上、どうにか処理効率をあげようとexcelで試行錯誤してきましたが、どうにもうまくいかないので、質問させていただきます。

当社の営業所の某会社からに対する買掛金と売掛金がこれだけ

あったとします。
（A~Dはすべて当社の営業所です）

＜表１＞

[買掛金]　　　　　　 [ 売掛金]
A 営業所　100　　　 A 営業所 300　
B　営業所　50　　　　B 営業所 150
C　営業所 200 　　C 営業所 150
D　営業所　150　　　 D 営業所 100
合計　　　 500　 　　　　　　　 700

この場合500円が相殺金額となります。
当社の場合売掛金がある営業所で入力処理を行いますので、
相殺金額500円分の売掛金は以下のように割り当てたとします。
A営業所　300
B営業所　150
C営業所　50

次に相殺する買掛金の営業所名も入力しなくてはいけないので
それぞれの売掛金に買掛金を割り振ると

＜表２＞

[売掛金]　　　[買掛金]
A営業所　200　C営業所　200
A営業所　100　A営業所　100
B営業所　150　D営業所　150
C営業所　50　 B営業所　50

このようになります。

上記のように単純な数字ならよいのですが、現実はそうはいきません。どこの営業所に割り当てるかは特に制限はないのですが、できるだけ処理の件数を少なくするには大きい数字から処理をしていくのがベターだととは思います。

＜表１＞がエクセルにあったとして、うまく＜表３＞のような形にもっていこうと、計算式を考えてたのですが、なかなかうまくいきません。マクロを組もうにもうまくいきません。

どなたか、ヒントをいただければと思います。　
よろしくお願いいたします。

回答No.3

imogasi

問題が理解できてない恐れはありますが。
貝掛金の列には同じ営業所が２行以上に渡って出現することがありえるのですね。
（売掛金も同様でしょう）
この場合ＶＬＯＯＫＵＰやＭＡＴＣＨ関数で複数の相手を捕らえるのは、関数では複雑になると思う。
ーー
ということはマクロ（ＶＢＡ）向きの問題で
売掛金の列を営業所名でソート
買掛金の列を営業所名でソート
両列を営業所名でマッチングロジックを使う
同一営業所の買掛金と売掛金どちらか大きい方から小さいほうを順次引き算する。（引けない場合はそちらを残す）
このマッチングのロジックはオフラインバッチ処理に有用です。
ーー
http://www.istudy.ne.jp/resume/BB/japan/fe_2.htm
ＣＯＢＯＬや情報処理受験の、この手の本には載っています。

お礼

ありがとうございます。

マッチングのロジックという言葉自体はじめてききました。
一番困っていたところが、
＞同一営業所の買掛金と売掛金どちらか大きい方から小さいほうを順次引き算する。（引けない場合はそちらを残す）
の部分でした。

ネットでさがしてみて、ないようでしたら本の購入も考えて見ます。

補足

大きいほうから小さいほうを順次引き算することですが、
自分では下記のような計算で行っているのですが、なんぜ規則がなくてまいっていました^^;

中盤で書いた売掛金の振り分け
A営業所　300
B営業所　150
C営業所　50
を
↓買掛金の数字の「高い順」（これが一番いいのかは不明ですが）から引いていったとして
300（A 営業所）-200（C営業所）=200
↓売掛金が100残っているので、次に高いB営業所の買掛金で解消　　
100（A 営業所）-150（B営業所）=-50　
↓買掛金の50割り当てれなかったので次のB営業所の150で解消
150（B 営業所）-50（B営業所）=100
↓売掛金が100残っているので、A営業所の買掛金で解消
100（B 営業所）-100（A営業所）=0
のこりの売掛金を残りの買掛金で解消
50（C営業所）　-50（B営業所）=0

回答No.2

noname#40742

#1です。

＜表２＞への自動算出となるとマクロですね。
作るとなると次のようなステップを
思い浮かべます。やりようはいろいろありますが。

＜表１＞から＜作業表＞へコピペ。（左右入替）
同一営業所の売掛買掛同額行を＜表２＞へ移行、行詰め（しなくてもよい、次の並べ替え範囲をしっかり指定する）
買掛、売掛それぞれ降順に並べ替え。※
上から相殺額を算出確定＜表２＞へ移行。

といった手順です。
※のところはどう組み合わせるかで答は違ってきます。

お礼

ありがとうございます。
＞※上から相殺額を算出確定＜表２＞へ移行。
の処理でどうすればいいか悩んでます。
ヒントをいただいたので、がんばってやってみます。

回答No.1

noname#40742

すみません、途中まで見えてきたように思うのですが、補足要求します。
＜表３＞が抜けてるなら書き足してください。もちろん表３の意味もお願いします。
割り振りの＜表２＞作成は手入力ですよね。

補足

表２は、最終的にこのような形で作り上げたいという例としてのエクセルの表なのです。
＜表２＞の２行目なら、
(1)　A営業所で入力してください
(2)（売掛の欄には）A営業所と入力してください
(3)（買掛に欄には）C営業所と入力してください
(4)（値段の欄には）200と入力してください

という意味がつたわればそれでいいのです。
そういった意味が伝わる表をエクセルでつくって、各営業所に本社送信したいのです。　　　　　

表１をみて、実際に電卓などで計算して手入力では作れるのですが、excelでその計算過程を反映させて、うまく加工したり、マクロを組もうとすると混乱してしまいます。
＜表３＞はないのです。すいません。