作者別: it-yobi

以下のような行数や列数が異なる複数のマトリックス表を集計する方法について解説していきます。

「統合」機能の活用

このような集計を行いたい場合は、[統合]という機能を活用します。
まずは集計結果を表示したい空の表を作成します。

要素の数（行数と列数）が分からない場合は、大きめに作成すると良いです。

次に作成した表全体を選択し、[データ]タブの中の[統合]を選択します。

表示された設定画面にて、[集計の方法]を指定し、[統合元]に集計対象のマトリックス表を追加していきます。
追加する方法は、[統合元範囲]のテキストボックスを選択し、対象のマトリックス表を選択することでアドレスが入力されるため、[追加]を選択します。
この作業を繰り返し、[統合元]に集計対象の全てのマトリックス表の範囲を追加します。

[統合元]に対象のマトリックス表の範囲を追加することができましたら、[統合の基準]の[上端行]と[左端列]をチェックし[OK]で確定します。

以上の作業を行うことによって、集計結果を空の表に反映することができます。

注意

最初に選択した範囲の要素の数（行数と列数）が少ない場合は、部分的な集計結果しか表示することができません。
そのため、集計結果を表示する空の表を作成する際は、要素の数に注意する必要があります。

大きめに選択することに関しては問題ないため、要素の数が分からない場合は大きめに選択しましょう。

また、集計結果の内容は、集計対象のセルと紐づいていません。
そのため、集計対象の値を変更したとしても、集計結果には反映されないので注意する必要があります。
集計対象の表を紐づけたい場合は、統合の設定画面にて[統合元データとリンクする]にチェックします。

そのようにすると、各表の個々の要素が数式で参照され、SUM関数などで集計された表に変換されます。
※以下の表では全ての行や列を表示させていますが、４～６行目、８～９行目などの合計の詳細に関しては自動でグループ化されます。

ただ、個々の表の要素（行や列）の追加には対応していないため、扱いには注意する必要があります。

補足

集計結果を表示させる表に関して要素の順番を予め指定したい場合は、要素が予め入力されている空の表を用意することで、その表に合った要素の順番で集計結果を表示することができます。

以下の売上合計（セルC2）は、SUM関数を用いて表を参照して求められています。

ただSUM関数での集計の場合、フィルター機能などを活用して、表の一部を非表示にしたとしても集計されてしまいます。
こちらでは、非表示のセルを集計対象から除外して集計する方法について解説していきます。

SUBTOTAL関数の活用

非表示のセルを集計対象から除外した集計を行う方法には、SUBTOTAL関数を活用する方法があります。
この関数の使い方は、以下の通りです。

=SUBTOTAL(集計方法, 参照1, [参照2], [参照3], …)
// 集計方法：集計する方法の番号を以下の表を参考に指定する
// 参照：集計する対象の値を指定する（[参照2]以降は省略可能）

実際にSUBTOTAL関数を用いて、非表示のセルを集計対象から除外した合計を求める場合は、以下の数式になります。

=SUBTOTAL(109,D5:D10)
// 集計方法「109」：非表示のセルを除外した合計

SUBTOTAL関数を活用することによって、フィルター機能などを活用して、表の一部を非表示にすると、以下のように集計対象から除外されるようになります。

SUBTOTAL関数では、この１つの関数で１１個の関数の役割を持ちます。
また、１１個の関数の役割に加え、非表示のセルを集計対象とするかどうかを指定することができるので、個々の集計関数（SUM、COUNTなど）よりも便利な関数になります。

同じ項目でよく絞り込みをする場合は、「スライサー」という機能を活用することで効率的に絞り込みを行うことができます。

こちらでは、スライサーの機能の設定方法について解説していきます。

1. テーブルに変換

スライサーを活用するためには、最初に対象の表をテーブルにする必要があります。
表をテーブルにするには、表の中を選択し、[挿入]タブから[テーブル]を選択します。
表示された「テーブル作成」という画面にて、表の範囲が正しく選択されていることを確認し、表の先頭行に項目名が含まれる場合は、[先頭行をテーブルの見出しとして使用する]にチェックします。

上記の画面にて[OK]で確定することで、以下のように表をテーブルにすることができます。

テーブルについての詳しい解説は、以下の記事にてご確認ください。

＞テーブルとは

2. スライサーの挿入

作成したテーブルを選択すると、[テーブルデザイン]というタブが表示されるため、[テーブルデザイン]タブから[スライサーの挿入]を選択します。
また、次に表示される画面にて、絞り込みを行いたい対象の項目にチェックして[OK]で確定します。

以上の手順で「スライサー」を作成することができます。
必要に応じて、スライサーを選択した時に表示される[スライサー]タブより、列数などの細かな設定を行います。

スライサーを移動させ、１行目に配置した例が以下になります。

スライサーは項目単位で作成され、それぞれのスライサーに設定した項目に、存在している値の種類分のボタンが表示されます。
上記では、「支店」の項目を元に作成しているため、スライサーには「支店」の項目に含まれる「大阪、東京、福岡、名古屋」のボタンが表示されています。

それぞれのボタンを選択することで、簡単に表を絞り込むことができます。

複数の値で絞り込みたい場合は、Ctrlキーを押しながら選択、もしくは右上のチェックマークを選択した後に複数選択することで絞り込みできます。

絞り込みを解除する場合は、右上の「×」を選択することで瞬時に解除することができます。

数式が入力されているセルを誤って触ってしまい、数式が消えてしまうことがあります。
また、表にある数式列の一部が直接入力されている場合、修正時に正しく反映されなくなります。
その対策として、数式が入力されているセルを色付けして、どのセルに数式が入力されているのかを分かりやすくする方法について解説していきます。

条件付き書式の設定

数式が入力されているセルを自動で色付けするには、条件付き書式を活用します。

まずは、数式の有無を確認したい範囲を全選択します。

次に、[ホーム]タブの中の[条件付き書式]から[新しいルール]を選択します。

条件付き書式の設定画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、表示されたテキストボックス内に以下の数式を入力します。

=ISFORMULA(A1)
// A1はアクティブセル

こちらでは、セルA1を基準に全体を選択しているため、セルA1に対しての数式を入力しています。
ISFORMULA関数では、指定したセルに数式が入力されている時に「TRUE」、数式でない時に「FALSE」を返します。
そのため条件付き書式にて、対象のセルに数式が入力されている時のみに「TRUE」となり、条件を満たすようにしています。

数式の入力ができましたら、条件を満たした時に反映する書式を設定します。
[書式]を選択し、こちらでは、[塗りつぶし]タブより薄い黄色を設定しています。

好みの書式を設定することができましたら[OK]で確定して、条件付き書式の設定画面を閉じることで、以下のように数式が入力されているセルに対し、自動で色付けすることができます。

以下の表のように、特定の項目に対し、同じ値が繰り返された場合に文字の色を自動で薄くする方法について解説していきます。

連続した同じ値の文字の色を薄くする

実現するには、「条件付き書式」を活用します。
まずは、対象の項目の範囲を全て選択します。こちらでは、セルA3以降を全て選択しています。

次に、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択し、表示された設定画面にて、色を薄くする条件を指定します。

設定画面の[ルールの種類]を[数式を指定して、…]にし、表示されたテキストボックス内に以下の数式を入力します。

=A3=A2
// 選択基準のセル（A3）の値がその上のセル（A2）の値と等しい場合

数式で参照しているセルは必要に応じて変更してください。
次に、[書式]を選択し、文字の色を設定します。

設定後は、確定して条件付き書式の設定画面を閉じることによって反映することができます。

補足

文字の色を変更する時などと、既に設定した条件付き書式を編集する場合は、[条件付き書式]の[ルールの管理]にて行います。

以下のような数式の場合、データを追加した際に数式の参照範囲を毎回修正する必要があります。

=SUM(B2:B6)
// 対象範囲がB2~B6の場合

今後、データが追加されることを考慮した数式の作成方法について３通りで解説していきます。

方法１

予め参照範囲を広めに指定することで、データの追加に対応することができます。

=SUM(B2:B100)
// 予め100行目まで指定する

この方法のデメリットは、行の削除などがあった際に、対応範囲が変わってしまうという点になります。
例えば、以下の表の６行目のデータを行ごと削除してみます。

そのようにすると、以下のように数式の参照範囲まで小さくなってしまいます。

=SUM(B2:B99)
// B100→B99

このような作業を繰り返すことで、気が付いた時には参照範囲外になり、正しく集計されない場合もあるかと思います。

方法２

先ほどとは異なり、対象の列（行）全体を指定することで、データの追加に対応することができます。

=SUM(B:B)
// SUM関数では参照範囲内の数値のセル以外は集計対象外になる

この方法の場合は、列指定では列を削除した時にも参照範囲が変わらなくなります。

ただ、この方法にもデメリットが存在します。
それは、参照範囲が膨大になることによる処理速度の低下です。
SUM関数のように、参照範囲が広くても処理速度に大きく影響しない関数も存在しますが、SUMIF関数などの条件付き関数やVLOOKUP関数などの検索関数など、関数によっては、処理速度に影響する可能性があります。
そのため、列（行）全体の指定を多用する場合は、注意する必要があります。

方法３

テーブルを活用することで、データの追加に対応することができます。
この方法の場合、対象の範囲をテーブルに変換する必要があります。
テーブルに変換するには、対象の範囲内のセルを選択し、[挿入]タブの[テーブル]を選択します。

作成したテーブルを選択すると、[テーブルデザイン]タブが表示されるため、必要に応じてテーブルのデザインやテーブル名を変更してください。

テーブルに変換した状態で、数式にてテーブルの対象範囲を指定すること、自動的に構造化参照で参照式が入力されます。

=SUM(テーブル1[売上])
// テーブル名[項目名]

この参照方法の場合は、テーブルのサイズに合わせて参照範囲が設定されるため、データの追加時にも対応することができます。

おすすめの方法

表を参照する場合は、３つ目の方法で参照を活用することを推奨します。
テーブルと構造化参照については、以下の記事にて解説しております。

＞テーブルの活用方法
＞構造化参照とは