- 締切済み
ＤＳＵＭ関数で、集計機能の集計結果を除外する
小さな会社の事務をしています 特に資格なども持っていない、パソコンもエクセルも素人で事務をやるのも初めてです。 会計士さんに提出する出納帳とは別に、科目ごとの金額をわかりやすく分析した資料を作ってほしい、というような指示を受けました。正直どういうものを作ればいいのかよくわからないのですが、まかせると言われてしまったため素人なりに考えて １シート目 実際の出納帳のくくりよりも詳細な項目（科目）別けで伝票内容を記入し、月毎で科目別にソート＆データの集計機能で集計してある出納帳 ２シート目 DSUM関数を使って１シート目から参照し、項目別の合計を再度求める ↑の数値を参照して会計士に提出するのと同様の科目でくくり合計、これらを横に並べた表、というのを作りました １シート目は縦スクロールの出納帳 ２シート目の見た目は 項目 （会社名）文具買掛 ￥ｘｘｘ 項目 プリンター用品 ￥ｘｘｘ 項目 ＰＣ用品 ￥ｘｘｘ 事務用品費 ￥ｘｘｘｘ ーーーーーーーーー－－ーーーーーーーーーーーーーーーーーーーーーーーーーーーー ↑DSUM関数で１シート目から参照して合計） ↑左列の数値をオートサムで合計 というような風です。 気になるのは参照する１シート目から『△ 集計』の行を除外する方法がわからずどうしても結果が２倍（△１+△２+△３+集計結果（△１+△２+△３））になってしまうので、無理やり式の最後に÷２を入れて辻褄をあわせたのですがこれで問題ないのかということです。 自動で書き込まれる『集計』の文字をエクセルは認識しないようで検索文字列を『△ 集計』と指定すると見つけることが出来ず、『△』で指定すると何故か集計結果まで含めてしまいます （１シート目と２シート目で同じ合計値をそれぞれで求めてるのは、数値を見比べて気づかず式を消してしまっていたりズレていたりするのを見つけるのに良いと思うのでそのままにしたいです。） またDSUM関数を使うために、２シート目では項目の行がいちいち入っていますが（実際は目立たないよう小さなフォントで薄い色にしてありますが）もっとスマートな方法はないかということ。 もちろんDSUM関数にこだわりがあるわけではないので（ネットで調べたらこれが出てきました）もっと綺麗なやり方があればそれを教えていただけると嬉しいですし、そもそも表のつくりがまずいなどアドバイスがあれば教えて下さい。 わかりにくい文章になってしまいましたが読んでくださってありがとうございます、よろしくお願いします。
- nanami8529
- お礼率0% (0/4)
- Excel（エクセル）
- 回答数4
- ありがとう数11
- みんなの回答 （4）
- 専門家の回答
みんなの回答
- MackyNo1
- ベストアンサー率53% (1521/2850)
集計シートの「大枠科目名」というのがどの項目から出てくるのか不明ですが、これが仮に「摘要」欄に記載されているなら、ピボットテーブルを使えば添付画像のような集計を簡単に行うことができます。 挿入タブの「ピボットテーブル」からデータ範囲が正しいことを確認して、「摘要」「詳細項目」「日付」を行ラベルに、「収入」「支出」をΣ値にドラッグします。 日付のセルを右クリックして「グループ化」から「月」を選択して（都市がまたがる場合は「年」も選択）、行ラベルの「日付」をレポートフィルタにドラッグします。 収入数値の欄で右クリックしデータの集計方法を「合計」に変更します。同様に支出のフィールドも「合計」にします。 ピボットテーブル上で右クリックし、「ピボットテーブルオプション」から「セルとラベルを結合して中央揃え」にチェックを入れ、表示タブで「従来のピボットテーブルレイアウトを使用する」にチェックを入れると添付画像のような集計ができます。 ちなみに月ごとの集計を見るときは、レポートフィルタのドロップダウンリストから「すべて」を「10月」などに変更すれば、その月のデータのみの集計ができます。 また、最初にデータベースシートをテーブルとして書式設定しておいてからピボットテーブルを作成すれば今後のデータの追加に対応して自動的にデータ範囲を拡張してくれます。 ＃Officeソフトのバージョンが明記されていなかったので、ひとまずExcel2007以降のバージョンのケースで説明しましたが、Officeソフトはバージョンによって操作法や使用できる機能が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。
△１、△２、△３ の各セルには、 =SUBTOTAL(9,B2:B3) =SUBTOTAL(9,B5:B7) =SUBTOTAL(9,B9:B12) のような数式が、 集計結果（△１+△２+△３）のセルには、 =SUBTOTAL(9,B2:B12) のような数式が自動的に入力されているでしょ？ 》 無理やり式の最後に÷２を入れて辻褄をあわせ 》 たのですがこれで問題ないのかということです それでも問題ないけど、単に「集計結果（△１+△２+△３）のセル」添付図で言えば、セル B14 だけを参照すれば、「÷２を入れて辻褄をあわせ」る必要もありません。
- Higurashi777
- ベストアンサー率63% (6218/9760)
DSUM関数ではなく、SUMIF関数を使われては如何でしょうか？ 例えば、A列のA1～A100に項目名が、B列のB1～B100に金額が記載されている場合、 以下の式で「集計」という文字が含まれた行のデータのみを合計することが可能です。 =SUMIF($A1:$A100,"=*集計",$B1:$B100) 以上、ご参考まで。
- MackyNo1
- ベストアンサー率53% (1521/2850)
ご希望の集計はおそらくDSUM関数ではなく、ピボットテーブルを使えば、レイアウトの整った集計表が簡単に作成できると思われます。 データベースシートのレイアウト（度の列にどのような項目があるのか）と、集計シートのレイアウト（たとえば会社別の品名ごとの集計など）を提示されれば、具体的に操作法を提示できると思います。
補足
回答ありがとうございます。ピボットテーブルというものがあるのですね、全く知りませんでした、ありがとうございます。 書き忘れていましたが会社のソフトはエクセルの２０１３だったと思います。 ■データベースシート Ａ Ｂ Ｃ Ｄ Ｅ Ｆ 日付 詳細項目 収入 支出 摘要 店名 ※この時点で一度、月毎に項目別に集計＆総計を求めておきたい （現状は範囲選択＞データの集計＞「詳細項目」毎の「収入」「支出」を合計、にチェックで求めています） ■集計シート Ａ Ｂ Ｃ Ｄ Ｅ 詳細項目別合計 収入 支出 大枠科目名 収入合計 支出合計 △社売掛 ５００ ー ☆社売掛 ５００ ー 売掛金 １０００ － ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー A社文具買掛 ー ５０ プリンター用品 ー １０ PC用品 ー １０ 事務用品費 ー ７０ ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー C社食品買掛 ー ３００ B社食品買掛 ー ２００ 食料品仕入れ ー １００ 食料費 ー ６００ ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー 水道 － １００ ガス － １００ 電気 － １００ 光熱費 － ３００ ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー 総計 １０００ ９７０ ※店舗ごとの主に仕入れと売上げの差や、備品類の購入頻度と購入量、購入内容などを確認するための集計です、データベース打ち込み時点で（大元の出納帳は手書きです。。）、税金や保険料、給与などの科目を除外しているため総計の差し引きは求めません。 これでつたわるでしょうか？宜しくお願いします。