仕入・売上の見やすい集計表を作りたい

質問

その日、その都度発生した、仕入れ（売上げ）結果を取引先別に

「品名」「数量」「単価」を記録した『表(1)』と

表(1)からさらに種類別に数量を累計した実績表なる『表(2)』があります

この2種類の表をもとに、取引先別の平均単価が月単位で解るような

表に作り変えたいと思っています。

どうすれば見やすく解りやすい表になるのかご伝授いただければ幸いです。

回答No.4

KURUMITO

回答Ｎｏ3です。
返事をありがとう。
平均単価が月毎などを考えるならむしろ表1ではお示しのような項目で日付はシートを月単位で考えるのではなく連続して入力していくことがよいでしょう。
その後のデータの解析はデータ数が多くなるときには特にそうですが配列数式のような難しい関数を使いますと計算に負担がかかりますのでできるだけ簡単な関数が使えるように作業列を作って対応することです。
表1がシート1に有るとしてＡ1セルには日付、Ｂ1セルには取引先名、Ｃ1セルには品名、Ｄ1セルには数量、Ｅ1セルには単価、Ｆ1セルには金額の文字列がそれぞれ入力されており下方にはデータが入力されているとします。
日付のＡ列では2/1のように入力すれば数式バー上では2013/2/1のように表示されますね。このように数式バー上で年、月、日などが表示されるように入力していることとします。
そこで作業列をＧ列からＩ列までに用意します。
Ｇ2セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,MAX(G$1:G1)+1,INDEX(G$1:G1,MATCH(B2,B:B,0))))

この作業列は取引先名を自動で表示させるためにも使用します。
Ｈ2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(COUNTIF(C$2:C2,C2)=1,MAX(H$1:H1)+1,INDEX(H$1:H1,MATCH(C2,C:C,0))))

この作業列は品名を自動で表示させるためにも使用します。
Ｉ2セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",G2*100000000+DATE(YEAR(A2),MONTH(A2),1)*1000+H2)

データを取引先別、年月別、品名別などで抽出するために使用します。

お求めの表ですがシート3に表示させることにしてシート3では次のようにします。
Ａ1セルには2013年2月のように検索・表示したい年月を入力します。数式バー上では2013/2/1のように表示されるようにします。平成24年2月と表示したい場合にはセルを右クリックして「セルの書式設定」から「表示形式」の「ユーザー設定」で種類の窓には　ggge"年"m"月"　と入力してＯＫします。　　　Ｂ1セルには取引先別品名別平均単価とでも入力します。
Ａ３セルから下方には品名を表示するために次の式をＡ3セルに入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$H:$H),"",INDEX(Sheet1!$C:$C,MATCH(ROW(A1),Sheet1!$H:$H,0)))

2行目には取引先名を表示するためＢ2セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)>MAX(Sheet1!$G:$G),"",INDEX(Sheet1!$B:$B,MATCH(COLUMN(A1),Sheet1!$G:$G,0)))

Ｂ3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR($A3="",B$2="",COUNTIF(Sheet1!$I:$I,INDEX(Sheet1!$G:$G,MATCH(B$2,Sheet1!$B:$B,0))*100000000+$A$1*1000+INDEX(Sheet1!$H:$H,MATCH($A3,Sheet1!$C:$C,0)))=0),"",SUMIF(Sheet1!$I:$I,INDEX(Sheet1!$G:$G,MATCH(B$2,Sheet1!$B:$B,0))*100000000+$A$1*1000+INDEX(Sheet1!$H:$H,MATCH($A3,Sheet1!$C:$C,0)),Sheet1!$E:$E)/COUNTIF(Sheet1!$I:$I,INDEX(Sheet1!$G:$G,MATCH(B$2,Sheet1!$B:$B,0))*100000000+$A$1*1000+INDEX(Sheet1!$H:$H,MATCH($A3,Sheet1!$C:$C,0))))

今回具体的な表の構成をお示しいただくことで詳細の手法を示すことができたわけです。これを参考にしてお望みの表を完成させてください。

回答No.3

KURUMITO

表1の構成と表2の構成をお知らせください。
それでないと答えようがありません。
また、平均単価は品名ごとですよね。

補足

表(1)は

◯月◯日「取引先名」「品名」「数量」「単価」「金額」
　・
　・
　・
と月末まで続きます。

表(2)は
　　　　　　　　　　◯月◯日　　　　　　◯月◯日
「取引き種類」　数量　　　累計　　　数量　　　累計・・・・と続きます。

回答No.2

papafuji

表１　　仕入先コード　　品名コード　　品名　　　　数量　　　　単価　
　　　　　A社　０３　　　００１　　　Aライター　　５０　　　　１００
　　　　　B社　０５　　　００２　　　Bライター　　１００　　　１２０

表２　　　　　０３　　　　００１　　　Aライター　　５０　　　１００
　　　　　　　０５　　　　００２　　　Bライター　　１００　　１２０
と共に得意先コードと商品コードまたはJANコードで管理すれば仕入先も商品も管理しやすいです。
又仕入れした日付を２５０２２１のように書いてコードとあわせて管理すると商品単品管理がスムーズにいきます。平均単価などは得意先コードで並べ替えれば良いし、商品コードで並べ替えると得意先別仕入れ明細も如実に分かりますよ。
JANが有ればそれを利用しても良いし無ければ自社コードをつくっても5000品くらいまででしたら５桁で商品コードをつくって置けば伝票などもこれで管理すると楽になるし、得意先管理や仕入先管理も楽に出来ますよ。ではまた・・・

補足

ありがとうございます。

JANコードですか？初めて聞きます（汗）

もっと詳しく解れば良いのですが。

回答No.1

-9L9-

「どうすれば」とは？あなたが欲しいと思っている形にすればいいでしょ？

補足

海藤ありがとうございます。

自分が欲しい形にするためにお聞きしています。