ここからは複数シート使い分けの実践的な活用例を見ていく。シートをまたいだコピーや、集計して複数シートをまとめる方法など、必要なテクニックもマスターしよう。
Excel「複数シート」活用の奥義<応用編>――インデックス
- 別々のシートにある同じ形の表同士を合計するには?
- 表を複数シートにまとめて貼り付ける
- 串刺し集計をする
- 知ッ得――複数シートにまたがる参照=「3-D参照」
- 別々のシートにある形が異なる表を合算するには?
- 形が異なる表を合算する
- 知ッ得――統合後も、元のデータを変更する可能性がある場合は?
- 「名前」を利用して別シートの表を式や入力リストに使うには?
- ひと押し――裏方のシートは隠しておこう
- 別シートにある「商品一覧表」をVLOOKUP関数で参照
- 別シートにある「商品リスト」を入力リストに表示
- 知ッ得――セル範囲に名前を付けるときの注意点は?
- Excel「複数シート」活用の奥義 基本編はこちらから
- (さらに続く応用編の奥義は、1月26日に掲載予定)
別々のシートにある同じ形の表同士を合計する
まず「東京」「大阪」「名古屋」という支店ごとにシートを作成し、それらを合算する例を見てみましょう。どの支店も取り扱い商品が同じで、どの表も同じ形のものとします。
まずシートの作成ですが、作りたい表が同じ形なので、1つのシートを作ったら、残りのシートには表をコピーしてしまいましょう。この作業は、シートを「作業グループ」にしておいて「フィル」機能を使うと手早くできます。
各支店ごとのシートと全社合計用のシートが用意できたら、支店シートどうしを串刺し集計しましょう。各シートの表が同じ形なので、たとえば「家電・照明」の「4月」の売上は、すべてC5セルとなります。
このような場合、“「東京」~「名古屋」のC5セル”というようにセルをまたがって参照することによって、串刺し集計を行なうことができます。
表を複数シートにまとめて貼り付ける
先頭シートに表を作っておく。
(1) 先頭シートを表示し、(2) をクリックしてシート全体を選択。いちばん最後のシート見出しを(3)[Shift]キー+クリックします。
複数シートが選択され、タイトルバーに「作業グループ」と表示されます。
メニューバーから[編集]→[フィル]→[作業グループへコピー]を選んで左の画面を出します。
(4)[すべて]を選んで(5)[OK]を押します。
(6) をクリックして2枚目のシートを表示。表がコピーされています。
シート名と、(7) B2セルを「大阪」に変えます。
同様に、(8)、(9) のシート名とB2セルも変更します。
各シートにデータを入力して完成。さらにここでは「全社」シートだけセルの色を変えてあります。
串刺し集計をする
(1)「全社」シートを表示して、(2) C5セルを選択し、ツールバーの[Σ](オートサム)ボタンをクリック。「=SUM()」と入力されます。
(3)「東京]をクリックして先頭シートを表示し、(4) C5セルをクリック。この時点で式は「=SUM(東京!C5)」となります。
続けて、(5)[Shift]キーを押しながら、シート見出し[名古屋]をクリック。式は「=SUM('東京:名古屋'!C5)」となります。[Enter]キーで入力完了です。
(6)、(7) のようにオートフィルして、C5:E7の範囲に式だけをコピーすれば完成します。
知ッ得 1
複数シートにまたがる参照=「3-D参照」
上記のように串刺し集計の操作をすると、関数式の引数に「'東京:名古屋'!C5」と入力されます('はシート名が文字列の時に付く記号)。
これを「3-D参照」と呼ぶ。これで「東京」~「名古屋」シートのC5セルという意味になり、連続シートの集計ができます。
(次ページ、「別々のシートにある形が異なる表を合算する」に続く)
この連載の記事
-
第3回
ビジネス
仕事に役立つ! 複数シートの検索&合算技――応用編 Part.2 -
第2回
ビジネス
もっと効率化! Excel「複数シート」を仕事に活用 -
第1回
ビジネス
仕事を加速する! Excel「複数シート」活用の奥義 - この連載の一覧へ