エクセル表加工のアドバイス求む

質問

先日エクセルの操作に困って質問をアップしたところ、たちまち快刀乱麻を断つ如き解決法を伝授いただきました。調子に乗ってもう一度相談させていただきます。

　あるところから商品販売データが毎月提供されます。表のＡ列には商品コード、Ｂ列には販売数量、Ｃ列には販売金額が入っています。（本当はもう少し複雑な表ですが、相談のために単純化しました）

　商品は全てのアイテムが毎月販売されるとは限らず、販売終了となるアイテムや新規取り扱いとなるアイテムもあり、表中の商品はアイテム数も種類も毎月異なります。当然表の行数は毎月異なることになります。

　この単月毎の表を１２ヶ月の商品毎の販売数・販売金額の一覧表にしたいのです。できることであれば毎月最新のデータで表を更新して直近１２ヶ月の表を毎月作成したいのです。

　どうぞ、良いアイディアを教えて下さい。

回答No.10

kazuhiko5681

間違えて回答するボタンを押してしまいました。補足させて下さい。
先程も書きましたように、　アクセスなら１回フォームを設定すればあなた様が思われていることは実現することができます。
でも、アクセスを使うのではなくエクセルでこのことを実現したいのであれば、やはりマクロを書くしか方法はないと思います。
もし、私でよろしければさほど難しいマクロではないので、書くコードをお知らせすることができます。
ご希望の節は、お知らせ下さい。

回答No.9

kazuhiko5681

はじめまして。私もpop_up_pump_upさんが回答されたように、アクセスを使ったほうがいいと思います。
ところで、あなた様がアクセスの使い方がわからないということですが、マイクロソフトのサポートセンターへ問い合わせてみたらいかがでしょうか？
あなた様のやりたいことが実現できるまで、同じことを何回質問してもいやな顔をせず、わかるまで担当者が責任を持って対応してくれます。
サポートには、無償サポートと有償サポートの２種類があります。
無償サポートというのは、初めてソフトを入手した時、どのソフトにもついています。ソフトの種類によって質問できる回数が違いますので、詳しくは担当者に聞いてみて下さい。
有償サポートというのは、無償サポート終了後にサポートを受ける方法です。私はスポット契約をお勧めします。この方法は質問ごとに契約センターと契約し、その度問題が解決するまでサポートを受けるという方法です。１回の契約料は、マクロを含まない時は４０００円含む時は１８０００円です。
有償サポートの場合は、０１２０－１７－０１９６へ電話をかければ担当者が対応して下さいます。
無償サポートにつきましては、担当部署の電話番号を忘れてしまいましたので、０３－５４５４－８０００(マイクロソフト本社)へ電話をかけ、担当部署の電話番号を聞いてみて下さい。
私もこの方法を利用して、エクセル・ワード・アクセス・ＶＢＡをある程度使いこなせるようになりました。
あなた様もこの方法を利用すれば、必ずアクセスをある程度自由に使いこなすことができるようになると思います。

お礼

ご丁寧な回答をありがとうございました。Accessに挑戦してみましょう。

回答No.8

cafedemocha

Accessを使うに当たって、悩まれる事が多いのは、テーブル・クエリー・フォーム・レポート等のそれぞれの役割ですね
簡単に言っちゃえば、テーブルは、データそのものを格納しておくための物
クエリーは、データを条件に基づいて抽出したり、計算させたり、様々な加工を施す物、まぁ篩みたいなものです、フォームは、極普通の使い方では、テーブルにデータを入力する際に作業を容易にするための物(それ以外にもかなりの用途はありますけどね)
で、レポートは、最終的にまとめられた結果を印刷する等の体裁を整える物
こんな風に考えると、理解し易いかもしれないですね

マクロやモジュールは、これらの操作を自動化したり、一覧に無いユーザー定義の関数を作成したり、自動的に値を挿入したりする等の操作や、セキュリティを上げたり等、様々な事を行う物ですね

特に、モジュールで定義して、構造を変えられなくすると、ユーザーレベルでは、一旦完成したデータベースを作り替えられる心配が無くなり、まぁ安心ですね

お礼

ありがとうございます。

　確かに、用語をきちんと理解することはソフトを使いこなす上で大切なことですよね。

回答No.7

imogasi

「更新」と言う処理を、Ｖｌｏｏｋｕｐ関数と手操作でやって
見ました。
（１）テストデータとして（マスタファイルに当たる）を作る（Ａ１：Ｅ８）。１１月の数量・金額列のＤ，Ｅ列は空白でスタート。(列の並びが本画面では乱れると思いますが、ご注意ご容赦を。)
１０月 １１月
商品コード 数量 金額 数量 金額
001-001 　　　1 　　　　10 #N/A #N/A
001-002 　　　2 　　　　20 #N/A #N/A
001-003　　　　4 32 2 8
002-001 　　　2　 16 5 40
002-002　　　　3 9 #N/A #N/A
003-001 　　　5 　　　　5　　　2 20

（２）１１月実績のデータとして（トランザクションデータに
当たる）Ｆ１：Ｉ７にデータを作る。初めはＩ列は空白。
１１月
商品コード 数量 金額 商品コード２
001-003 2 8 001-003
002-001 5 40 002-001
003-001 2 20 003-001
002-003 3 30 #N/A
00４-002 2 20 #N/A

（３）Ｄ３セルに関数式=VLOOKUP($A3,$F$3:$H$7,2,FALSE)を
入れる。（絶対番地にするところと、Ｆａｌｓｅは留意）
Ｄ４から最下行まで複写。
（４）Ｅ３セルに関数式=VLOOKUP($A3,$F$3:$H$7,3,FALSE)を
入れる。絶対番地にするところと、Ｆａｌｓｅは留意）
Ｅ４から最下行まで複写。
＊＊以上でマスターにあるトランザクションのデータは商品コード一致分は数量、金額を転記が済んだ。残るは１１月新顔売上の分をどう追加するか。
（５）Ｉ３に関数式=VLOOKUP($F3,$A$3:$A$8,1,FALSE)を入れる。（絶対番地にするところと、Ｆａｌｓｅは留意）
Ｉ４から最下行まで複写。
（６）Ｆ３からＩ７を範囲指定して、データ－並べ替え
最優先されるキー－商品コード２、先頭行－データ、ＯＫで並べ替えする。＃Ｎ／Ａは下に沈殿する。
（７）Ｉ列が＃Ｎ／Ａ分のみＦ列を範囲指定する
（８）Ａ列の最下行の次行以下に貼り付ける。
（９）Ｄ列、Ｆ列を式複写を最下行まで延ばす。
　　　新顔の数量、金額を転記が済んだ。

お礼

imogasiさん、ありがとうございます。

　実はこの方法こそ今私が実際に行っている作業とほぼ同じことなのです。いかにも手作業、という感じですよね。もっと楽に出来るようにして、ワープロ打ちやデータ入力くらいの作業しか経験のない事務担当でも出来るようにしたいのです。

回答No.6

imogasi

＃５の者です。解答を入れて補足を良く見ると、解答では
題意の取り違えているようです。昨晩に解答ない状態で考え始めたので、わからず、済みません。
年間表として年月を横に流して、
商品コード、１０月販売数、１０月販売額、１１月販売数、１１月販売額、１２月販売数、１２月販売額・・・９月販売数、９月販売額、年間販売数、年間販売額としたいのであれば
エクセルＶＢＡを使い、年間表、単月表を共に商品コードで
ソート（並び替え）しておき、商品コードをマッチング・キー
として年間表の最右列に追加し、１２ヶ月の販売数と販売額をそのプログラム中で足せして合計列を追加すれば出来ます。開始に当たって去年の同月分は列削除で削除しておく。プログラムロジックは情報処理試験にも出るトランザクション更新のものを使えば良い。

お礼

No.5へお礼を書いている間にNo.6の回答がアップされていました。

　よくよく見ればimogasiさｎ、前回の質問（最大値を取る支店名の表示）でもお世話になりました。その節はありがとうございました。

　ＶＢＡを使えばできる、という回答があることは予想していました。残念ですがAccess同様、VBAもまったく素人で手が出ません。

回答No.5

imogasi

年間表と月間表を考える。
下記テストデータをＡ１：Ｄ１１まで作る。年間表のつもり。
月間表の９月分を貼り付けた後の状態。
年月 商品コード 販売数 販売金額
2001年10月 001-002 1 10
2001年10月 002-001 3 20
2002年4月 001-002 2 20
2002年4月 001-005 5 40
2002年4月 002-009 2 10
2002年8月 001-001 1 10
2002年9月 001-001 2 20
2002年9月 001-002 4 50
2002年9月 001-005 2 30
2002年9月 002-001 3 20
（１）昨年９月分を削除。（先月年間表を年月で並び替えし
　　　２０００／９月の範囲を指定しＤＥＬキーで削除。
（２）２００２／９月分（単月表）を最下行以下にコピーする。
（３）データ－並び替えをクリック
　　　最優先されるキー－商品コード
　　　２番目に優先されるコード－年月
　　　範囲の先頭行－タイトル
　　　ＯＫをクリック。
（４）メニューのデータ－集計をクリック。
　　「集計の設定」の画面になる。
（５）グループの基準－商品コード
　　　集計の方法－合計
　　　集計するフィールド－販売数と販売金額にチェックを
　　　入れる。
　　　「集計行をデータの下に入れる」チェックを入れる。
　　　ＯＫをクリック。
（６）行番号の左に出る、集計レベル「１，２，３」の２を
　　　クリック。
　　下記になります。
年月 商品コード 販売数 販売金額
001-001 計 3 30
001-002 計 7 80
001-005 計 7 70
002-001 計 6 40
002-009 計 2 10
総計 25 230
年月が邪魔なと思えば、商品コードから販売金額までを
範囲指定して印刷する。
元に戻すのは、データ－集計－全て削除で明細表示状態に
戻る。

お礼

早朝よりありがとうございます。

　申し訳ありません、私の質問の書き方が不十分で悪かったのですが、他の方への補足やお礼に書いたとおり、得られた結果はテキストファイルに落として他のソフトに取り込むデータとするため、商品毎にデータが横方向に並んでいないといけないのです。

　私の説明不足でご迷惑をおかけして済みません。

回答No.4

noname#134301

アクセス使ってみてはどうですか？
ある期間の商品ごとの販売数・販売金額をもとめたいのならアクセスの方が楽ですよ。提供されるデータをアクセスにインポートし、そこで加工してからエクセル上に持ってくるというやりかたです。

この作業、エクセルだけでやるにはかなりしんどいと思います。

お礼

やっぱりAccessですか。Accessっておぼえたらかなり色々使えそうですよね。すでに私のパソコンにはインストールされているのですが勉強の時間がなくてほっぽらかしです。どうしたらおぼえられるのでしょうか。

　真夜中にもかかわらず、ご回答いただきありがとうございます。私は一旦眠らせていただきます。

回答No.3

BraveStory

年月の列を追加し、毎月のデータをつなぎ合わせたシートを作る。あとはそれを、ピボットテーブルでまとめれば（縦の項目に商品コード、横に年月、値に販売数量と金額で）、見やすくなるのではないでしょうか。

お礼

なるほど、年月の列の挿入は良い考えですね。ただ、No.1の方の補足にも書いたとおり、最終的にはテキストファイルに落として他のソフトにインポートを目的としているので、ピボットテーブルではない方法が望ましいのです。

　ご回答ありがとうございます。こんな真夜中なのに。

回答No.2

fu-raibo-

恐らく12ヶ月集計のワークシートを作っておいて
そこに自動集計されるようにするのが理想なんだとは思いますが
それは思いつかなかったので、スマートじゃないけど一応集計はできると言う方法を。

集計用のワークシートを作ってそこに12ヶ月分のデータを連続してコピーします。
商品コードとかのタイトルは一行目だけで後はデータだけをコピーです。

商品コード　販売数量　販売金額
ABC 2 500
DEF 5 1000

商品コード　販売数量　販売金額
ABC 2 500
GHQ 3 1500

こんな感じで12ヶ月分あったとしたら、これを全部くっつけて

商品コード　販売数量　販売金額
ABC 2 500
DEF 5 1000
ABC 2 500
GHQ 3 1500

と言う表を作ります(商品コードでソートとかの処理はいりません。単純に張りつけていくだけです)。
この出来上がった表に対して、ピポットテーブルで行を商品コード、データを販売金額の合計にすると、

合計：販売金額
商品コード　計
ABC 1000
DEF 1000
GHQ 1500

なんて感じの集計表ができます。

データに販売数量の合計もいれると

商品コード　データ 計
ABC 合計：数量 4
合計：販売金額 1000
DEF 合計：数量 5
合計：販売金額 1000
GHQ 合計：数量 3
合計：販売金額 1500

と言う表になります。

とりあえずこんなやり方でどうでしょう。

補足

No.1の方に補足したとおりです。戴いたご回答ですと、ちょっと結果が違ってしまいます。私の質問がいい加減で済みません。

回答No.1

cafedemocha

単月ごとの表が既にあって、それを、一覧集計する・・・
そう理解して宜しいのでしょうか？
その場合は、SUMIF関数などを組み合わせて使えば簡単ですが、この先様々なデータ処理を行おうとするのであれば、既に、Excelで処理する範疇を超えているようですね、ファイルも重くなります

集計表には、全てのアイテムを記載しておき、それぞれの月ごとにSUMIF関数で集計させれば、データの入力に伴い、集計表に反映されていきます

お節介ですが、Access等のデータベース導入を検討されては如何でしょうか

補足

わかりにくくて済みません。

　現在ある表は質問の中に書いたとおりです。この表が１月分、２月分というように月ごとにファイルとして存在します。結果として欲しい表は、Ａ列に商品コード、Ｂ列にｎ月の販売数、Ｃ列にｎ月の販売金額、Ｄ列にｎ＋１月の販売数、Ｅ列にｎ＋１月の販売金額．．．．という表です。

　実はこうして加工した後、テキストファイル（CVS）に落として他のソフトにインポートして使うのです。

　Accessは勉強している時間がなくて、宝の持ち腐れになっています(T_T)。Accessを憶えるいい勉強法を教えて下さい、といったら別の質問になってしまいますね。