[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『請求金額の確認』（派遣）
いつも勉強させて頂いております。 今日金額の集計方法について教えて下さい。 導入した新しいシステムがいまいち不安定で請求金額が果たして正しいのかという 一抹の不安があります。 そこで記入しているシートで金額の確認を行っているのですが、取引先別ごとに 締め日が異なるため困っています。 シートは下記のような形です。
客先c 客名 品番 品名 金額 納期 aaa 日本 123 りんご 500 8/10 bbb 中国 456 パイン 300 8/15 ccc 韓国 789 なし 300 8/17 aaa 日本 123 りんご 500 8/30
例えば日本という会社の締め日が10日だった場合は８月の請求金額は500円。 末締めだった場合は1000円になります。 締め日は10日・15日・20日・末日の４パターンあるんですが、商社ごとに請求金額を 月毎に算出することは可能でしょうか？
１．商社別の締日情報 どこに、どの様なレイアウトで、どういったデータ型(特に末日)で存在するのですか？
２．納期
シリアル値ですね？
(半平太)
半平太 さま コメントありがとうございます。 納期の日付が客先に届いた日になっていますのでここの日付で集計をしたいと考えています。 レイアウトは上の表のような形で納期順に下行へどんどん増える形です。 シリアル値で入力しております。 宜しくお願い致します。 （派遣）
客名「日本」「中国」「韓国」が、実際には「何日締め」か知りたいのですが、、、
別のシートに、客別に締日が登録されているマスタがあるものと推測したのですが、 そうではないとしたら、算出する手がかりを何に求めれば良いのでしょうか？ (半平太)
恥ずかしながら今までは目で見て計算していたので別のシートに締め日があるというわけではないのです。。。 必要であればそのシートを作る事は可能なのですが現状としては何もありません。 ≧8/10 のような式で出来るのかと考えていました。すみません。。。 実際には１０日締めの商社が２件、15日締めが１件他の大多数は20日締めと末締めになっています。 （派遣）
前提：「データ」シートのサンプルを以下とします。 行 __A__ _B__ _C__ __D___ _E__ ___F___ 1 客先c 客名 品番 品名 金額 納期 2 aaa 日本 123 りんご 500 7月1日 3 aaa 日本 456 パイン 300 7月10日 4 ccc 韓国 789 なし 300 7月11日 5 aaa 日本 123 りんご 500 7月15日 6 ccc 韓国 789 なし 300 8月9日 7 aaa 日本 123 りんご 500 8月10日 8 bbb 中国 456 パイン 300 8月15日 9 aaa 日本 123 りんご 500 8月30日
１．「マスタ」シートの処理 この機会に、新規に作成して下さい。 フィルタオプションを利用するなどして、重複のないマスタシートを作成して下さい。
行 __A__ _B__ _C__ 1 客先c 客名 締日 2 aaa 日本 末 3 bbb 中国 末 4 ccc 韓国 10
２．「集計」シート この機会に、新規に作成してください。そこに以下の数式を入力します。
(1) G2セル =VLOOKUP(A2,マスタ!A:C,3,0)
(2) H2セル =IF(AND(DATE(YEAR($H$1),MONTH($H$1)-1+(G2="末"),N(G2))<F2,F2<=DATE(YEAR($H$1), MONTH($H$1)+(G2="末"),N(G2))),MONTH($H$1)&"月"&G2&"日締分","")
G2:H2セルを一括選択してフィルダウンします。
行 _A_ _B_ _C_ _D_ _E_ _F_ _G__ _H__ 1 締月 2 #N/A #N/A 3 #N/A #N/A 4 #N/A #N/A
３．「データ」シートから「集計」シートにデータをコピーしてください。 A:F列をコピーして、「集計」シートのA:F列に貼り付けます。
４．「集計」シートの処理 (1) H1セルに集計すべき月日を入力します。 ※8月締めなら、「8/1」とか「8/15」とか、8月中の日ならいつでも構いません。 面倒でしたら、H1セルには =Today() としておくことも可能です。 それだと、当日が属する月を基準にすることになります。
(2)こんなのが出来ますので、H列と客名を基準に並べ替えを行って、目的のデータを入手して下さい。 ↓ 行 __A__ _B__ _C__ __D___ _E__ ___F___ _G__ _____H_____ 1 客先c 客名 品番 品名 金額 納期 締月 8月30日 2 aaa 日本 123 りんご 500 7月1日 末 3 aaa 日本 456 パイン 300 7月10日 末 4 ccc 韓国 789 なし 300 7月11日 10 8月10日締分 5 aaa 日本 123 りんご 500 7月15日 末 6 aaa 日本 123 りんご 500 8月10日 末 8月末日締分 7 bbb 中国 456 パイン 300 8月15日 末 8月末日締分 8 ccc 韓国 789 なし 300 8月9日 10 8月10日締分 9 aaa 日本 123 りんご 500 8月30日 末 8月末日締分 10 #N/A #N/A (半平太)
集計の例を載せるのを忘れました。(半平太)
H列>客名の順で並べ替えた後、[メニュー]データ→集計を行った例
行 __A__ ____B____ _C__ __D___ _E__ ___F___ _G__ _____H_____ 1 客先c 客名 品番 品名 金額 納期 締月 8月30日 2 aaa 日本 123 りんご 500 7月1日 末 3 aaa 日本 456 パイン 300 7月10日 末 4 aaa 日本 123 りんご 500 7月15日 末 5 日本 合計 1300 0 6 ccc 韓国 789 なし 300 7月11日 10 8月10日締分 7 ccc 韓国 789 なし 300 8月9日 10 8月10日締分 8 韓国 合計 600 0 9 bbb 中国 456 パイン 300 8月15日 末 8月末日締分 10 中国 合計 300 0 11 aaa 日本 123 りんご 500 8月10日 末 8月末日締分 12 aaa 日本 123 りんご 500 8月30日 末 8月末日締分 13 日本 合計 1000 0 14 #N/A #N/A 15 総計 3200 #N/A
ただの確認だけであれば、こんな方法も・・・
A B C D E F G H I J
1 客先c 客名 品番 品名 金額 納期 客名 〆日 8月
2 aaa 日本 123 りんご 500 8/10 日本 末 1000
3 bbb 中国 456 パイン 300 8/15 中国 10 0
4 ccc 韓国 789 なし 300 8/17 韓国 20 300
5 aaa 日本 123 りんご 500 8/30
H列手入力。I列は、セルの書式設定を[#;-#;"末";@]として、手入力(末は0と入力)。
J1は、集計したい年月の日付入力（書式は[m"月"]）。
J2セルに↓（範囲は修正の事）を入れて、必要範囲にコピー。
=SUMPRODUCT(($B$2:$B$11=H2)*($F$2:$F$11>=DATE(YEAR($J$1),MONTH($J$1)-(I2<>0),I2+1))*($F$2:$F$11<=DATE(YEAR($J$1),MONTH($J$1)+(I2=0),I2)),$E$2:$E$11)
あくまでも、簡易に片付けたい場合だけという事で。。。
(sin)
おはようございます。 お礼が遅くなり申し訳ございません。 早速教えて頂いた方法で挑戦したいと思います。 時間がかかるかもしれませんが、自己啓発できるチャンスだと思って頑張ります。 ありがとうございました。また結果をご報告させて頂きます。 （派遣）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.