こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

営業所ごと相殺処理について(excel)

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

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

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

<表1>

[買掛金]       [ 売掛金]
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

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

<表2>

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

このようになります。

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

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

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

投稿日時 - 2007-02-10 10:44:51

QNo.2739884

TYP

困ってます

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

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

投稿日時 - 2007-02-10 22:40:29

補足

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

中盤で書いた売掛金の振り分け
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 
 
   

投稿日時 - 2007-02-11 11:25:16

お礼

ありがとうございます。

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

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

投稿日時 - 2007-02-11 10:43:05

ANo.2

#1です。

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

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

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

投稿日時 - 2007-02-10 19:06:07

お礼

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

投稿日時 - 2007-02-11 10:40:47

ANo.1

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

投稿日時 - 2007-02-10 11:24:17

補足

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

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

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

投稿日時 - 2007-02-10 11:47:17

あなたにオススメの質問