小さな会社の事務をしています
特に資格なども持っていない、パソコンもエクセルも素人で事務をやるのも初めてです。
会計士さんに提出する出納帳とは別に、科目ごとの金額をわかりやすく分析した資料を作ってほしい、というような指示を受けました。正直どういうものを作ればいいのかよくわからないのですが、まかせると言われてしまったため素人なりに考えて
1シート目
実際の出納帳のくくりよりも詳細な項目(科目)別けで伝票内容を記入し、月毎で科目別にソート&データの集計機能で集計してある出納帳
2シート目
DSUM関数を使って1シート目から参照し、項目別の合計を再度求める
↑の数値を参照して会計士に提出するのと同様の科目でくくり合計、これらを横に並べた表、というのを作りました
1シート目は縦スクロールの出納帳
2シート目の見た目は
項目
(会社名)文具買掛 ¥xxx
項目
プリンター用品 ¥xxx
項目
PC用品 ¥xxx 事務用品費 ¥xxxx
ーーーーーーーーー--ーーーーーーーーーーーーーーーーーーーーーーーーーーーー
↑DSUM関数で1シート目から参照して合計) ↑左列の数値をオートサムで合計
というような風です。
気になるのは参照する1シート目から『△ 集計』の行を除外する方法がわからずどうしても結果が2倍(△1+△2+△3+集計結果(△1+△2+△3))になってしまうので、無理やり式の最後に÷2を入れて辻褄をあわせたのですがこれで問題ないのかということです。
自動で書き込まれる『集計』の文字をエクセルは認識しないようで検索文字列を『△ 集計』と指定すると見つけることが出来ず、『△』で指定すると何故か集計結果まで含めてしまいます
(1シート目と2シート目で同じ合計値をそれぞれで求めてるのは、数値を見比べて気づかず式を消してしまっていたりズレていたりするのを見つけるのに良いと思うのでそのままにしたいです。)
またDSUM関数を使うために、2シート目では項目の行がいちいち入っていますが(実際は目立たないよう小さなフォントで薄い色にしてありますが)もっとスマートな方法はないかということ。
もちろんDSUM関数にこだわりがあるわけではないので(ネットで調べたらこれが出てきました)もっと綺麗なやり方があればそれを教えていただけると嬉しいですし、そもそも表のつくりがまずいなどアドバイスがあれば教えて下さい。
わかりにくい文章になってしまいましたが読んでくださってありがとうございます、よろしくお願いします。
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.4
- 回答日時:
集計シートの「大枠科目名」というのがどの項目から出てくるのか不明ですが、これが仮に「摘要」欄に記載されているなら、ピボットテーブルを使えば添付画像のような集計を簡単に行うことができます。
挿入タブの「ピボットテーブル」からデータ範囲が正しいことを確認して、「摘要」「詳細項目」「日付」を行ラベルに、「収入」「支出」をΣ値にドラッグします。
日付のセルを右クリックして「グループ化」から「月」を選択して(都市がまたがる場合は「年」も選択)、行ラベルの「日付」をレポートフィルタにドラッグします。
収入数値の欄で右クリックしデータの集計方法を「合計」に変更します。同様に支出のフィールドも「合計」にします。
ピボットテーブル上で右クリックし、「ピボットテーブルオプション」から「セルとラベルを結合して中央揃え」にチェックを入れ、表示タブで「従来のピボットテーブルレイアウトを使用する」にチェックを入れると添付画像のような集計ができます。
ちなみに月ごとの集計を見るときは、レポートフィルタのドロップダウンリストから「すべて」を「10月」などに変更すれば、その月のデータのみの集計ができます。
また、最初にデータベースシートをテーブルとして書式設定しておいてからピボットテーブルを作成すれば今後のデータの追加に対応して自動的にデータ範囲を拡張してくれます。
#Officeソフトのバージョンが明記されていなかったので、ひとまずExcel2007以降のバージョンのケースで説明しましたが、Officeソフトはバージョンによって操作法や使用できる機能が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。
No.3
- 回答日時:
△1、△2、△3 の各セルには、
=SUBTOTAL(9,B2:B3)
=SUBTOTAL(9,B5:B7)
=SUBTOTAL(9,B9:B12)
のような数式が、
集計結果(△1+△2+△3)のセルには、
=SUBTOTAL(9,B2:B12)
のような数式が自動的に入力されているでしょ?
》 無理やり式の最後に÷2を入れて辻褄をあわせ
》 たのですがこれで問題ないのかということです
それでも問題ないけど、単に「集計結果(△1+△2+△3)のセル」添付図で言えば、セル B14 だけを参照すれば、「÷2を入れて辻褄をあわせ」る必要もありません。
No.2
- 回答日時:
DSUM関数ではなく、SUMIF関数を使われては如何でしょうか?
例えば、A列のA1~A100に項目名が、B列のB1~B100に金額が記載されている場合、
以下の式で「集計」という文字が含まれた行のデータのみを合計することが可能です。
=SUMIF($A1:$A100,"=*集計",$B1:$B100)
以上、ご参考まで。
No.1
- 回答日時:
ご希望の集計はおそらくDSUM関数ではなく、ピボットテーブルを使えば、レイアウトの整った集計表が簡単に作成できると思われます。
データベースシートのレイアウト(度の列にどのような項目があるのか)と、集計シートのレイアウト(たとえば会社別の品名ごとの集計など)を提示されれば、具体的に操作法を提示できると思います。
この回答への補足
回答ありがとうございます。ピボットテーブルというものがあるのですね、全く知りませんでした、ありがとうございます。
書き忘れていましたが会社のソフトはエクセルの2013だったと思います。
■データベースシート
A B C D E F
日付 詳細項目 収入 支出 摘要 店名
※この時点で一度、月毎に項目別に集計&総計を求めておきたい
(現状は範囲選択>データの集計>「詳細項目」毎の「収入」「支出」を合計、にチェックで求めています)
■集計シート
A B C D E
詳細項目別合計 収入 支出 大枠科目名 収入合計 支出合計
△社売掛 500 ー
☆社売掛 500 ー 売掛金 1000 -
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
A社文具買掛 ー 50
プリンター用品 ー 10
PC用品 ー 10 事務用品費 ー 70
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
C社食品買掛 ー 300
B社食品買掛 ー 200
食料品仕入れ ー 100 食料費 ー 600
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
水道 - 100
ガス - 100
電気 - 100 光熱費 - 300
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
総計 1000 970
※店舗ごとの主に仕入れと売上げの差や、備品類の購入頻度と購入量、購入内容などを確認するための集計です、データベース打ち込み時点で(大元の出納帳は手書きです。。)、税金や保険料、給与などの科目を除外しているため総計の差し引きは求めません。
これでつたわるでしょうか?宜しくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・14歳の自分に衝撃の事実を告げてください
- ・架空の映画のネタバレレビュー
- ・「お昼の放送」の思い出
- ・昨日見た夢を教えて下さい
- ・ちょっと先の未来クイズ第4問
- ・【大喜利】【投稿~10/21(月)】買ったばかりの自転車を分解してひと言
- ・メモのコツを教えてください!
- ・CDの保有枚数を教えてください
- ・ホテルを選ぶとき、これだけは譲れない条件TOP3は?
- ・家・車以外で、人生で一番奮発した買い物
- ・人生最悪の忘れ物
- ・【コナン30周年】嘘でしょ!?と思った○○周年を教えて【ハルヒ20周年】
- ・ハマっている「お菓子」を教えて!
- ・最近、いつ泣きましたか?
- ・夏が終わったと感じる瞬間って、どんな時?
- ・10秒目をつむったら…
- ・人生のプチ美学を教えてください!!
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・都道府県穴埋めゲーム
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのピポットテーブルで...
-
ピボットテーブルのことです
-
保存ブックを開かずコピーペー...
-
IF関数を使用した数字に、カン...
-
Microsoft Formsによるアンケー...
-
エクセルの集計を数字以外です...
-
ピボットテーブルの項目間の計算
-
マクロで貼り付け位置を可変さ...
-
勤務表の中抜け集計の関数を教...
-
ある審査の結果を迅速にエクセ...
-
オートシェイプを色別に個数を...
-
エクセルの集計機能を横方向(...
-
エクセルで○や×の図形の集計は...
-
ピボット集計、月の並び順を自...
-
アクセスでアンケートフォーム...
-
ピボットテーブルの集計値をVBA...
-
アクセスのFormat関数に...
-
パワーポイントで資料を作る時 ...
-
EXCELのSUMPRODUCTでエラーにな...
-
ライブ配信で、ライバーが視聴...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ピボットテーブルのことです
-
マクロで貼り付け位置を可変さ...
-
エクセルのピポットテーブルで...
-
Microsoft Formsによるアンケー...
-
エクセルの集計を数字以外です...
-
勤務表の中抜け集計の関数を教...
-
エクセルで○や×の図形の集計は...
-
IF関数を使用した数字に、カン...
-
オートシェイプを色別に個数を...
-
ピボットテーブルの項目間の計算
-
エクセルの集計機能を横方向(...
-
保存ブックを開かずコピーペー...
-
エクセルで数値のプラス毎とマ...
-
エクセルのフッターについて
-
エクセル ピボットテーブルを更...
-
エクセルで工事台帳を作ってい...
-
ピボット 集計アイテム
-
ピボットテーブルへの集計フィ...
-
ピボットテーブルの集計値をVBA...
-
価格帯別集計 EXCELで効率の良...
おすすめ情報