小さな会社で経理してます。
今だに顧客ごとの毎月の売上&入金の集計をするために
手書きの伝票から売上台帳の各顧客ごとのページに手書きで転記し、
電卓で月ごとの集計しているのですが、
それをexcel(2007)で集計したいのです。知恵をお貸し下さい。
私の入社前は全てがアナログの大変な世界でしたが、
現在は私の精一杯の知識を駆使して、
請求書だけは手書き&電卓計算しなくて発行できるようにしました。
方法は、手書きの伝票のデータ1か月分を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 /..................../..................../..................../
--------------------------------------------------------------------------
5月計 / 70,000 / 2,500 / 30,765 / 735 / 72,500
小さな会社とはいえ、年間に取引する顧客数は150から200社にのぼり、
売上台帳は1年終わると幅4~5センチのファイルがパンパンになります。
しかも、よく社員が過去の分を参照するため見る必要があるので、
簡単に該当部分が画面上に表示できるほうが助かります。
関数の知識はある程度ありますが、
VBAに関しては本を読んでなんとなくどんなものか知ってるぐらいで、
実際に使ったことはありませんが、必要なら頑張って勉強するつもりです。
いいアイデアや具体的な方法、また参考になる本・サイトなど
役に立ちそうな事ならなんでもいいです。どうか教えて下さい。
できるところまで自力でやってみたいのです(経理ソフトに頼らず)
よろしくお願いします。
なお、表示幅の狭いOK Waveでは表が崩れてわかりにくいかもしれません。
その場合は 教えてGooのサイトから見て下さい。
No.2ベストアンサー
- 回答日時:
質問者さんが要望されているようなクロス集計をするには、Excelでは「ピボットテーブル」という機能を使います。
多数の複雑な数式で集計すると計算が重くなるといった問題もありますが、ピボットテーブルでは、そういうことは全くありません。表計算ソフトの本分といった大事な機能ですので、ぜひ活用してください。概念・操作
Excel2003 http://allabout.co.jp/gm/gc/297727/
Excel2007 http://www.eurus.dti.ne.jp/yoneyama/Excel2007/ex …
※ピボットテーブル用のツールバーは、テーブル内にカーソルを置いているときのみ出現します。
※できた表の右クリックなどから「フィールドの設定」で指定することで、個数、合計、最大最小、平均などを自動計算してくれます。
※各「ボックス」(以前のバージョンでは「エリア」)には、複数の項目を任意の順序で配置することができます。
もしも部分的にピボットテーブルでうまく抽出できない箇所があれば、うまくできるように、データベース(抽出元の表)のほうをきちんと整備しておくということも大事です。コピー&ペースト、置換、セルの結合の不使用などをしておきます。
数値の計算はせず、各項目のリストアップの目的のみでピボットテーブルを使うという場合もあるかと思います。フィルタの機能で抽出した結果と比べると、ある程度まとめてくれますので。
データの更新は、手動にはなりますが、ボタンを押せば実行されます。
帳票の作成の手順は、こんな感じがお勧め。
(1)データ貼り付け用のシートを用意し、毎回、これにピボットテーブルで抽出した結果(あるいはそれを別途、数式その他の機能で少しだけ処理した結果)を値のみ貼り付ける。
(2)印刷用のシートを用意し、そこに帳票の様式を、書式も含めてしっかり作っておく。データ貼り付け用のシートからのリンクをセルに貼り、結果が表示されるようセットしておく。毎回、このシートを(紙、PDF、DocuWorksなどに)印刷することで、帳票とする。
(3)社員さんが各人でPCを使える環境なら、共有フォルダにExcelファイルを放り込んでおいて、それも見てもらう。検索の機能も使えるので、分厚い紙ファイルをめくるよりはラクに目的データにアクセスできそう。
毎日など頻繁にこういった作業をする場合は、マクロ化しておくと更に速いですが、月1回・1表のみといった程度なら、特に大変ということもないでしょう。
ピボットテーブルは今までつかったことがないので、
回答いただいてからこの2~3日いろいろ調べてみました。
ピボットテーブルの概要を見て、
確かに使えるかもしれない、と思いましたが、
まだ正直???なので、
本やサイトを参考に具体例などををみて一度トライしてみたいと思います。
そのピボットテーブルを活用した帳票作成の手順まで
とても具体的に教えてもらいありがとうございます。
ピボットテーブルが使えるようになったら、
こちらもぜひ試そうと思います。
回答ありがとうございました。
No.1
- 回答日時:
ホームタグの並べ替えとフィルタのフィルタで、フィルタ設定をABC商店のみチェックして表示してみて下さい。
それだと、該当の会社の売上伝票の一覧を表示することはできると思いますが、
教えて欲しいのは、その売上伝票のデータも利用したうえで
入金のデータも加えた、各社ごとの売上集計(売掛残高)シートを作る方法です。
1か月分の全会社の伝票データの入力されたシートと入金データのシートから
なんとかうまいこと抽出&転記して
サンプルで示したような各社ごとの売上集計の表を作れないものか、と。
会社数がすくなければ地道に各社のシートへコピペしますが、
なにせ150~200社ありますので、そうもいかず。
そういうことをできるようにするためには
何か関数なりVBAなりが必要になるのではないかと思ったのです。
長文で質問の趣旨がわかりにくかったかもしれません。ごめんなさい。
回答ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 政治 日本で訴訟件数が少ないのは、自民党とビッグモーターが詐欺組織だからですか? 2 2023/07/27 11:30
- 会社経営 代表の考え方が気に入らないです 私は今の会社で働いて2年半 元夫と元夫の友人が立ち上げた会社に属して 2 2022/06/19 06:36
- Excel(エクセル) エクセルでSUMIFS関数で条件範囲の部分が#valueになる。 4 2023/04/28 12:42
- 財務・会計・経理 事業復活支援金の事前確認について 2 2022/04/11 16:07
- 営業・販売・サービス トラブル客の来店時の対応 私は令和2年~毎年勤務先の人材派遣会社で契約先のスズキディーラーの初売りの 1 2023/01/03 09:53
- その他(税金) 事業復活支援金の事前確認ってどこまでチェックされるのでしょうか? 1 2022/04/22 11:55
- 会計ソフト・業務用ソフト 事業内容に適した、見積・請求書・顧客管理ソフト、システムを探しています。 2 2022/11/11 13:28
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける (再質問) 4 2022/09/14 22:51
- 消費税 インボイス制度 2 2022/11/19 14:44
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける 3 2022/09/10 07:55
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ピボットテーブルでは時間の表...
-
エクセルで特定の色の数字だけ...
-
【エクセル】オートフィルタで...
-
VBAで重複する項目を1つにまと...
-
ピボットを更新すると数式が入...
-
ピボットの集計方法「合計」初...
-
VLOOKUP関数とCOUNT関数等の組...
-
ピボットテーブルで同じデータ...
-
いい機能だけど、毎回めんどく...
-
EXCEL 重複データの集計の仕方
-
エクセルのSUM関数
-
EXCELで項目ごとの金額を合計す...
-
エクセルで集計
-
エクセル2000 ID別・週別に集...
-
エクセル 条件を満たす最大値...
-
アンケートの集計
-
エクセルのピボットテーブルで...
-
EXCELで増減率を計算したいです
-
SUBTOTALが変わってしまいます。
-
複数のピボットテーブルを一括...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルで特定の色の数字だけ...
-
ピボットテーブルでは時間の表...
-
フィルタをしても最下行を常に...
-
VBAで重複する項目を1つにまと...
-
ピボットの集計方法「合計」初...
-
アンケートの集計
-
ピボットを更新すると数式が入...
-
エクセルの小計を自動的に色づ...
-
エクセル、リソース不足エラー...
-
EXCELで増減率を計算したいです
-
EXCEL 重複データの集計の仕方
-
excelで集計の合計を降順に並べ...
-
VLOOKUP関数とCOUNT関数等の組...
-
ピボットテーブルで同じデータ...
-
常にタイトル行と合計行を表示...
-
いい機能だけど、毎回めんどく...
-
Excelの集計結果だけをコピー貼...
-
EXCELで0を除いた平均値...
-
ピボットテーブルのページエリ...
おすすめ情報