TOPページ > Excel・IT開発 > Excel実務のノウハウ > Excelで関数だけを使って月別総括集計表を作る1

Excelで関数だけを使って月別総括集計表を作る1|Excel実務のノウハウのお勉強

このエントリーをはてなブックマークに追加

Excelで関数だけを使って月別総括集計表を作る1

今回、こちらで提示している月別総括集計表は三鷹商工会にある「記帳の手引き」の表を基に作成しました。 Excelに適した表で、1つのシートに規則正しく入力するだけで、関数が自動的に月、費目を分別、合算して表示してくれるスグレモノです。
今回作成したExcel版では「入力用」シートに入力した内容が自動的に「集計」シートに反映されます。
ちょっとした家計簿代わりや確定申告用資料作成の段階で役立つ表です。今回は1回で説明しきれないので2、3回に分けて説明します。


月別総括集計表とは

弊社で確認している月別総括集計表は商工会から配布されている「紙バージョン」です。先日それを見たときにExcel化できるのではないか、ということを思って作成しました。
Excel版としての使い方(操作や関数)は下記をご覧いただきたいのですが、このExcel版を使うことにより、費目ごとの振り分けが自動で行えるだけでなく、ご自身の改良次第では時系列に見たり、前年比較など応用(経営管理)的な使い方もできます。

本ページでは紙→Excel版に変える部分について説明しています。


Excelで作成した月別総括集計表

Excelで作成した月別総括集計表は実はシンプルな関数を組み合わせて使っているだけです。 しかし今回の内容では関数の中でも上級者向けと思われる配列数式を使っています。この配列数式の意味が読み取れる方は、ある程度見ただけで動きが理解できるのではないかと思います。

■入力用シート(関数未使用)
規則正しく入力するだけでOK

■集計シート(関数使用、上記の「入力用シート」から自動的に判別して表示)
入力したシートのデータが自動的に集計

■集計シートの全体図
集計シートの全体図


使い方ついて

使用するシートは2枚だけです。
1.入力用シート→このシートに明細を入力
2.集計シート→入力された明細が自動的に振り分けられる

例えば領収書や、レシートを見て、入力用シートにどんどん明細を打ち込んでいくと勝手に集計シートに計算結果が表示されます。簡易性、利便性という点では抜群です。
なお、上記の入力用シートでは「摘要」、「収支」欄の文字は集計対象になっていないので、どんな文字を入れても大丈夫です。


制限事項について】

かなり便利なシートですが、残念ながら制限事項もございます。
もっともこの制限に該当するような規模ならば、Access、またはこのシートをプログラム化した方がよいかもしれません。

動作が重くなる
こちらはExcelの仕様上、多くの関数を使えば使うほど動作が遅くなります。
例えば「入力用シート」に何か明細を入力すると、集計シートに結果が反映させるまでにもたつき感が出るなどです。重たさの度合いは利用しているパソコンのスペックに依存するので一概には言えません。

入力する明細数を予め決めておく
今回作成した表では「入力用」シートに何明細(何件、何行)分まで入力するかを事前に関数に通知(設定)しておく必要があります。その為、その数を超える明細があった場合には集計対象となりませんので注意が必要です。
それならばと、予め多くの件数分(例えばExcel2003ではMax約65000件)を確保(設定)すると、上述の動作が重くなるというジレンマ状態になりますのでバランスを取って決める必要があります。
(レシート枚数換算なら月に何枚くらいで1枚にいくつ明細があるかなどで判断など)


このエントリーをはてなブックマークに追加

Excel実務のノウハウ 関連ページ


グローウィン有限会社
東京都三鷹市下連雀3-27-1 三協ビル2階-B 地図を見る
トップページ
入力代行関連
Excel・IT開発
中国アテンド
会社概要
プレスリリース
お問い合わせ
お問い合わせ内容一覧

英文ページ(English)
中文ページ
プレスリリース お問い合わせフォーム