複数シートの集計について、行き詰ってしまったのでどなたかアドバイスをお願いします。
シートは1年度分の月ごと(契約日ベース)(4月~3月)に分かれています。
データは下記のようになっています。
種別 | 受付番号 | 注文番号 | 地域 | 契約日 | 金額 | 担当者
--------------------------------------------------------------
A | 1111 | A245 | 関西 | 4/1 | 70,000 | SA
--------------------------------------------------------------
A | 1122 | A873 | 関西 | 4/15 | 85,000 | FU
以下続く。
担当者は10人未満です。1シートに同じ担当者が何回か登場しますが、毎月同じメンバーが登場するわけではありません(例:4月はAさん・Bさん・Cさん、5月はCさん・Dさん等)。
集計に使いたい項目は、(1)契約日(2)担当者(3)金額です。
担当者ごとに、月合計を集計し、かつ3ヶ月ごとに合計金額を出し、最後の列には総合計を入れたいです。
担当者 | 4月 | 5月 | 6月 | 1Q合計 | 7月 | 8月 | …… | 19年度合計
ピボットテーブルの「複数のシート範囲」を選択してやってみたのですが、うまくレイアウトが組めませんでした。
列を一部、担当者・契約日・金額の順に入れ替えて、この3行だけで複数シートのピボットテーブルを試してみたのですが、どうもうまくいきません。
どなたか助けてください!
ピボットテーブルじゃなくてもOKです。ただマクロはできません。
No.1ベストアンサー
- 回答日時:
私だったら以下のようにやってみます。
・毎月のシート名を「4月」「5月」「6月」・・・と形式を揃える。
・「集計」シートを設ける。そのシートの
・A1 に「担当者」とし、A2から下に担当者を全員並べる
・B1に「4月」とし、C1から右に「3月」まで入れる。M1まで。
・B2に、
=SUMIF(INDIRECT(B$1&"!G2:G65536"),$A2,INDIRECT(B$1&"!F2:F65536"))
と入力して、右に横に引っ張る。
・そしたら、月ごとの担当者ごとの数字が落ちてきます。
・後は、Qごとの合計列、年度の合計列を追加すれば終わりです。
※indirect関数で、シート名から集計範囲を拾っていますので、シート名と、「集計」シートの1行目の見出しは同一にしてください。
(半角と全角など間違えると出ません。)
お望みの形式でなければすみません。
早速のご回答ありがとうございます!
でもやってみたんですが、全て「0」と表示されてしまいました…。出来が悪くて申し訳ありません(;_;)。私のやり方が間違っているだけだと思うのですが。
INDIRECT関数というのを使ったことが無いので教えていただきたいのですが、"!G2:G65536"や"!F2:F65536"というのは何を表しているのですか?このあたりを自分の表に合わせて書き換えれば良いのでしょうか?
アドバイスを読む限り、集計シート上でだけの作業に思えるのですが、月ごとのシートを開いて範囲指定などはしなくても良いのでしょうか?
なお、行数は月ごとに異なり、最終行に月ごとの小計行があります。行数を全シートで統一する必要がありますか?(質問部分に書き漏れていて申し訳ありません)
No.3
- 回答日時:
#1の者です。
私のほうこそ説明不足で恐れ入ります。
>集計シート上でだけの作業に思えるのですが、月ごとのシートを開いて範囲指定などはしなくても良いのでしょうか?
→はい、必要ありません。それがこの処理法の良いところです。
各月の範囲指定をしないでもいいように、範囲指定を2行目から65536行目としました(私はエクセル2003だったので)。
>なお、行数は月ごとに異なり、最終行に月ごとの小計行があります。行数を全シートで統一する必要がありますか?
→もちろん統一する必要はありません。
最終行に小計行があっても、集計シートではあがってこないでしょうから気にする必要はありません。
>"!G2:G65536"や"!F2:F65536"というのは何を表しているのですか
→これは、各月のシートにおけるG列、F列を示しています。
例示されている
>種別 | 受付番号 | 注文番号 | 地域 | 契約日 | 金額 | 担当者
を受けて、左から順にA列、B列と当てはめてそう書いたつもりでした。
すなわち、G列=担当者名、F列=金額です。
INDIRECT(B$1&"!G2:G65536") の意味は、B1=4月 の場合には、
4月!G2:G65536 をあらわしています。「4月シートのG列」です。
INDIRECT(B$1&"!F2:F65536")) の意味は、「4月シートのF列」です。
つまり、各月シートで、SUMIFで担当者ごとの合計金額を出す作業を12回やるべきところを、1回の作業で終わらせてる、ただそれだけです。
INDIRECT関数に関しては、私の説明よりも以下のURLが分かりやすいのでどうぞ。(ちょうど、今回のケースに近い形で説明があります)
また、最後のアウトプット(集計シートの構成)など私が誤解している場合にはご指摘ください。
参考URL:http://www.relief.jp/itnote/archives/001697.php
無事に集計できました。ありがとうございます!
本当に助かりました。
INDIRECT関数って便利ですね。今後も役立ちそうなので、勉強して自分でも使えるようにしたいです(今回は教えていただいたのを、よく意味が分からないまま流用しただけなので)。
No.2
- 回答日時:
#1です。
すみません一箇所訂正です。数式を入れた後の部分、
(誤) 右に横に引っ張る。
(正) 右に、下に引っ張る。
の間違いです。
4月~3月、そして、全担当者名の範囲まで引っ張るという意味です。
申し訳ありません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAで重複データを確認したい 5 2022/10/07 16:24
- Excel(エクセル) 列を自動で追加したい 3 2022/07/11 12:58
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) vba 同じブック内での転記について 4 2023/01/15 14:42
- その他(Microsoft Office) ピボットテーブルへの集計フィールド挿入 1 2023/02/26 11:33
- Visual Basic(VBA) 日付を重複させずに数えたい 4 2022/12/04 16:26
- クレジットカード 楽天カードのサポートが不便なのは何故? 1 2023/02/20 11:03
- Excel(エクセル) Excelについて 1 2023/03/06 10:26
- Excel(エクセル) 添付写真上のExcelシートのように時間と曜日ごとに担当者が振り分けられているシートがあります。 例 1 2023/03/08 13:02
- Excel(エクセル) Excel、同じフォルダ内のExcelファイルの特定シートのみを1つのファイルに集約したい 8 2022/09/07 15:12
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
Accessのクエリで、replace関数...
-
Vba Userformを前面に出すについて
-
Access IF文でテーブルに存在し...
-
【Access】Dcount関数の複数条...
-
access2021 印刷プレビューでの...
-
Access VBA [リモートサーバー...
-
Access VBA を利用して、フォル...
-
accessデータを指定したExcel、...
-
Accessのスプレッドシートエク...
-
ACCESS VBA でのエラー解決の根...
-
Access で半角スペースと全角ス...
-
Access VBA でHTML文を表示したい
-
Accessのテキストボックスの入...
-
Microsoft365にAccessってあり...
-
教えてください! アクセスの書...
-
実行時エラー3131 FROM 句の構...
-
Accessのフォーム上のテキスト...
-
ACCESS2019でのエラーメッセージ
-
CSVファイルの「0落ち」にVBA
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
Microsoft365にAccessってあり...
-
Accessのクエリで、replace関数...
-
ACCESS VBA でのエラー解決の根...
-
Accessのスプレッドシートエク...
-
Access VBA を利用して、フォル...
-
【Access】Dcount関数の複数条...
-
accessデータを指定したExcel、...
-
Accessレポートのチェックボッ...
-
Access VBA [リモートサーバー...
-
Vba Userformを前面に出すについて
-
Accessのリンクテーブルのパス...
-
実行時エラー3131 FROM 句の構...
-
Accessのフォーム上のテキスト...
-
CSVファイルの「0落ち」にVBA
-
Access 複数条件検索の設定が上...
-
アクセス 削除するレコードを含...
-
Access で半角スペースと全角ス...
-
access 更新クエリについて
-
Accessのクエリの結果を、既存...
おすすめ情報