エクセルで集計

質問

小さな会社で経理してます。
今だに顧客ごとの毎月の売上＆入金の集計をするために
手書きの伝票から売上台帳の各顧客ごとのページに手書きで転記し、
電卓で月ごとの集計しているのですが、
それをexcel(2007)で集計したいのです。知恵をお貸し下さい。

私の入社前は全てがアナログの大変な世界でしたが、
現在は私の精一杯の知識を駆使して、
請求書だけは手書き＆電卓計算しなくて発行できるようにしました。

方法は、手書きの伝票のデータ１か月分をexcelに入力し、

伝票no. / 日付 / 顧客no. / 顧客名 / 請求額
------------------------------------------
50123　/ 5/03 / 1234 / ABC商店 / 25000

顧客名は別BOOKのリストの顧客NO.からLOOKUP関数で自動で表示されるようにしてます。
別シートにコピーして顧客ごとにソート＆小計して消費税と合計請求額も計算式で求め、
それをwordで作った請求書に差し込み印刷するという方法です。

つまり売上集計のベースとなるデータの一部
（一部、というのは、現金・振込など入金に関しては今のところデータ化してないので）
はもうexcelに既にあるのです！
なのにそこから顧客ごとの毎月の集計をするいい方法が思い付きません。

自動化したい売上台帳は現在顧客ごとに以下のように手書きしてます。

ABC商店
========
日付　　伝票NO. 　　　 / 売上金額 / 消費税 / 受入金額 / 値引額 / 差引残高
5/01　　（前月残）　　　/..................../..................../..................../..................../　31,500　　　
5/03　　 50123　　　　/　10,000　/..................../..................../..................../
5/10　　 ○○銀行　　/..................../..................../　30765　/..................../
5/10　　振込手数料　/..................../..................../..................../　　735　/
5/18　　50333 　　　　/　40,000　/..................../..................../..................../
5/20　　消費税　　　　/..................../　　2,500　/..................../..................../　　　　　　　
5/24　　 50123　　　　/　 20,000　/..................../..................../..................../
--------------------------------------------------------------------------
５月計　　　　　　　　　/　70,000　/　　2,500　/　30,765　/　　735　　/　72,500

小さな会社とはいえ、年間に取引する顧客数は150から200社にのぼり、
売上台帳は１年終わると幅4～5センチのファイルがパンパンになります。
しかも、よく社員が過去の分を参照するため見る必要があるので、
簡単に該当部分が画面上に表示できるほうが助かります。

関数の知識はある程度ありますが、
VBAに関しては本を読んでなんとなくどんなものか知ってるぐらいで、
実際に使ったことはありませんが、必要なら頑張って勉強するつもりです。

いいアイデアや具体的な方法、また参考になる本・サイトなど
役に立ちそうな事ならなんでもいいです。どうか教えて下さい。
できるところまで自力でやってみたいのです（経理ソフトに頼らず）
よろしくお願いします。

なお、表示幅の狭いOK Waveでは表が崩れてわかりにくいかもしれません。
その場合は　教えてGooのサイトから見て下さい。

回答No.2

MarcoRossiItaly · ベストアンサー

質問者さんが要望されているようなクロス集計をするには、Excelでは「ピボットテーブル」という機能を使います。多数の複雑な数式で集計すると計算が重くなるといった問題もありますが、ピボットテーブルでは、そういうことは全くありません。表計算ソフトの本分といった大事な機能ですので、ぜひ活用してください。

概念・操作
Excel2003　http://allabout.co.jp/gm/gc/297727/
Excel2007　http://www.eurus.dti.ne.jp/yoneyama/Excel2007/excel2007-piv.html

※ピボットテーブル用のツールバーは、テーブル内にカーソルを置いているときのみ出現します。
※できた表の右クリックなどから「フィールドの設定」で指定することで、個数、合計、最大最小、平均などを自動計算してくれます。
※各「ボックス」（以前のバージョンでは「エリア」）には、複数の項目を任意の順序で配置することができます。

もしも部分的にピボットテーブルでうまく抽出できない箇所があれば、うまくできるように、データベース（抽出元の表）のほうをきちんと整備しておくということも大事です。コピー＆ペースト、置換、セルの結合の不使用などをしておきます。

数値の計算はせず、各項目のリストアップの目的のみでピボットテーブルを使うという場合もあるかと思います。フィルタの機能で抽出した結果と比べると、ある程度まとめてくれますので。

データの更新は、手動にはなりますが、ボタンを押せば実行されます。

帳票の作成の手順は、こんな感じがお勧め。

(1)データ貼り付け用のシートを用意し、毎回、これにピボットテーブルで抽出した結果（あるいはそれを別途、数式その他の機能で少しだけ処理した結果）を値のみ貼り付ける。
(2)印刷用のシートを用意し、そこに帳票の様式を、書式も含めてしっかり作っておく。データ貼り付け用のシートからのリンクをセルに貼り、結果が表示されるようセットしておく。毎回、このシートを（紙、PDF、DocuWorksなどに）印刷することで、帳票とする。
(3)社員さんが各人でPCを使える環境なら、共有フォルダにExcelファイルを放り込んでおいて、それも見てもらう。検索の機能も使えるので、分厚い紙ファイルをめくるよりはラクに目的データにアクセスできそう。

毎日など頻繁にこういった作業をする場合は、マクロ化しておくと更に速いですが、月1回・1表のみといった程度なら、特に大変ということもないでしょう。

お礼

ピボットテーブルは今までつかったことがないので、
回答いただいてからこの２～３日いろいろ調べてみました。
ピボットテーブルの概要を見て、
確かに使えるかもしれない、と思いましたが、
まだ正直？？？なので、
本やサイトを参考に具体例などををみて一度トライしてみたいと思います。

そのピボットテーブルを活用した帳票作成の手順まで
とても具体的に教えてもらいありがとうございます。
ピボットテーブルが使えるようになったら、
こちらもぜひ試そうと思います。

回答ありがとうございました。

回答No.1

aokii

ホームタグの並べ替えとフィルタのフィルタで、フィルタ設定をABC商店のみチェックして表示してみて下さい。

お礼

それだと、該当の会社の売上伝票の一覧を表示することはできると思いますが、
教えて欲しいのは、その売上伝票のデータも利用したうえで
入金のデータも加えた、各社ごとの売上集計（売掛残高）シートを作る方法です。

1か月分の全会社の伝票データの入力されたシートと入金データのシートから
なんとかうまいこと抽出＆転記して
サンプルで示したような各社ごとの売上集計の表を作れないものか、と。

会社数がすくなければ地道に各社のシートへコピペしますが、
なにせ150～200社ありますので、そうもいかず。
そういうことをできるようにするためには
何か関数なりVBAなりが必要になるのではないかと思ったのです。

長文で質問の趣旨がわかりにくかったかもしれません。ごめんなさい。
回答ありがとうございました。