タテとヨコに集計したい

今回は、既存の表から飛び飛びのセルをタテヨコに集計する方法を調べます。

縦横集計

飛び飛びの位置にあるセルを選んで集計してみたいことがあります。

例えば、何かのデータの集計表があって、そこからさらに別の軸で集計したい時、つまり行方向と列方向で共通の項目の交差する「格子の目」にあたるセルを拾って合計値を見たいときなどです。

このような集計方法を何と呼ぶのかよく分からなかったのですが、この記事では仮に「縦横集計」と呼ぶことにします。

やりたいことは結局クロス集計なので、ピボットテーブルだったり、テーブル形式の元データがあれば集計し直すだけです。

ただそうもいかない表形式の場合もありますし、ちょっと値を確認したいだけの時にはデータをいじらずにお手軽に集計できたいです。

本記事では、Excel の基本機能を使って、お手軽に縦横集計を確認する方法がないか探っていきます。

縦横集計をするには

調べてみると色々方法があるようですが、レポート作成やデータ分析をするわけではなく、サクッと結果だけが知りたい時にできるお手軽そうな方法として、以下の Excel 機能を試して見ました。

• ステータスバーで確認する
• 統合を使う
• 配列数式を使う
• 交差参照を使う

ステータスバーで確認する

集計したいセルが10個程度までなら、Ctrl+クリックで飛び飛びにセル選択していくのもそんなに無理な話ではありません。

それならステータスバーで集計値を確認するのがお手軽です。

シートで複数セルを選択すると、その集計値が「ステータスバー」に表示されます。 ステータスバーは Excel ウィンドウの一番下にある領域です。

複数セルを選択すると、デフォルトではそれらの「平均」、「データの個数」、「合計」がステータスバーに表示されます。 「データの個数」は選択されたセルの個数になります。

ステータスバーを右クリックすると表示させる集計値を選択できて、 他に「数値の個数」、「最大値」、「最小値」があります。

この集計値は見るだけではなくて、クリックすればクリップボードにコピーもできます。

１回集計を見るだけならお手軽ですが、さらに選択セルを隣にずらした組の集計も得たいなど、複数回の集計には耐えられません。

---

---

統合を使う

本当にやりたいことは、表の行・列に、見出し、区分、種別といった項目データがあって、その値でカテゴリ化したクループでの集計でしょう。

その場合は Excel の「統合」機能が使えるかもしれません。

「統合」はもともと複数の表をひとつの表に集約する機能です（名前が分かりにくい😣）。 集約したセルの値は「合計」などで集計されます。

その際、各表のデータは、見出しの共通する項目によって集約することができます。

それはたとえ表が一つしか指定されていなかったとしても同じことです。

【手順例】

1. 集計データの出力先としたい位置のセルを１個選択します
   • 集計したい項目を含めることもできます（後述）
2. 「統合の設定」ダイアログを開きます
   • 「データ」タブ ⇒ 「データツール」セクション ⇒　「統合」ボタン
3. 「統合元範囲」で表のセル範囲を選択します
   • セル範囲には、集計項目として１行目と１列目を含めます
4. 「追加」ボタンで「統合元リストに追加します」
5. 「統合の基準」で「上端行」と「左端列」にチェックを入れます
6. 必要なら「集計の方法」ドロップダウンで集計方法を変更します
   • 「集計の方法」ドロップダウンで選択できる集計方法は以下の通りです
7. 「OK」ボタンを押します

各集計はもちろん数値データしか対象になりませんが、「個数」だけは、値が文字列でもカウントすることができます。 出欠表の「〇」の集計などにも応用できるかもしれません。

項目値の集約には英字の大文字と小文字が区別されませんの注意してください。

集計項目を指定したい

全ての項目で集計するのはなく、特定の項目のみを指定することもできます。

出力先として、あらかじめ項目を見出しとして用意して、それを含むセル範囲を選択しておきます。

元データの更新を自動反映したい

「統合」ダイアログで「統合元とリンクする」にチェックを入れると 元データの更新をが自動で反映できるようになります。 ただしその場合、出力先を元データとは別シートにする必要があります。

結果は元データへのセル参照で小計したグループ化（アウトライン）です。 詳細もパカパカと開いて見ることができます。 値の更新は自動反映されますが、行の追加や削除があったら当然作り直しです。

「統合」が使えるのは、カテゴリ項目がデータ範囲に隣接してすぐ上の行と左の列にある場合だけです。

項目列や行が集約したいデータ範囲から離れた場所にあるときには、表のレイアウトを変更するなどの手間がかかります。

---

---

配列数式を使う

数式なら、データ範囲と項目列/行が隣接していなくても、参照アドレスで指定できるはずです。

数式を使った縦横集計の方法は色々あるのですが、結構複雑な数式になります。ちょっと値を確認したいだけのときにそんな頭を使いたくありません。

そこで、「配列数式」を使えばだいぶ簡略化できます。

項目列と項目行についてある項目名の組を指定して縦横集計する数式は以下のようになります。

=SUM( (項目列範囲=行項目名) * (項目行範囲=列項目名) * データ範囲)

「配列数式」は入力時に注意が必要です。

最新の Excel (Microsoft 365 版 Excel と Excel 2021）使っている場合は、特に意識しなくても、このまま数式入力（Enter）するだけで OK です。 これは「動的配列数式」という新しい機能によります。

しかしそれ以前の Excel（～Excel 2019）をご使用の場合、 「配列数式」を入力をするには、通常のEnterではなく、「Ctrl + Shift + Enter」 というキーの組み合わせを同時に押す必要があります。 そうしないと、計算が行われず、#VALUE! エラーになってしまいます。

さて、上記数式では、１組の項目名による集計しかしていません。

もちろん、あらかじめ集計したい項目名を用意して数式から参照するように書き換えれば、複数の項目名の組についても数式のコピー＆ペーストで各集計値を出すことができます。 その際は参照先アドレスの一部を絶対参照（複合参照）で固定しておかないと、間違った範囲からの集計になりがちですので注意してください。

この配列数式は一見単純そうに見えますが、実際何をどうしているのか、普通の数式とどう違うのかは分かりにくいです。 ここでは配列による演算の計算が使われているのですが、それは後に解説します。

合計（SUM）以外の集計をしたい

さて、上記数式は FALSE が 0 扱いになることをあてにした掛け算をしています。

つまりこの作戦でうまくいくのは SUM 関数だけで、AVERAGE や COUNT といった他の集計関数に単純に置き換えることはできません。

他の集計関数では IF 関数を挟んだもう少し複雑な数式になります。

例えば AVERAGE 関数を使いたい場合、以下のようになります。

=AVERAGE( IF( (項目行範囲=行項目名) * (項目列範囲=列項目名), データ範囲 , ""))

集計関数は数値のみを集計するので、IF 関数で集計対象外のデータを空文字列（"")に置き換えています。

大文字と小文字を区別したい

Excel の数式の 比較演算子（=, <>, >, < など）は、英字の大文字と小文字を区別しません。 「"APPLE" = "Apple"」は真（TRUE）と判定されます。

大文字と小文字を厳密に区別したい場合には「=」の代わりに EXACT 関数を使います。

項目の大文字と小文字の違いを一緒くたにしないで縦横集計する数式は、比較を EXACT で置き換えて次のように表されます。

=SUM( EXACT(項目列範囲, 行項目名) * EXACT(項目行範囲, 列項目名) * データ範囲 )

=AVERAGE( IF( EXACT(項目列範囲, 行項目名) * EXACT(項目行範囲, 列項目名) ), データ範囲 , "") )

ここまでくると、だいぶ長い数式となって、お手軽とは言えなくなってきました。

配列の演算について

２次元のセル範囲の参照どうしを計算すると、各セルの値ごとに計算されます。
その結果は「配列」となり、最新の Excel ならスピルします。

さらにこの配列も、他のセル範囲や別の配列と、同様に計算できます。

参照や配列を組み合わせた時、どのように値が計算されるかは、 その形状によります。
　

　
今回の縦横集計の数式がどう展開されるのかを書き（描き）下すと次のようになります。
　

---

---

公差参照を使う

集計したい飛び飛びのセルが、項目名や区分など集約できる共通項目を持ない場合はどうしたらいいでしょうか。

最終手段として SUM 関数などに各セルの参照を追加していくしかなさそうなのですが、手作業（Ctrl+クリック）で任意の各セルを選択して、というのもつらいところです。

もし集計したいセルが、これまでのように格子目にあって縦横にそろっているのなら「交差参照」で少しは楽に選択できそうです。

「交差参照」とは、複数のセル範囲が重なっている範囲を表す参照のことです。

「交差参照」の指定には「 」（半角スペース）を使い、これは「交差演算子」と呼ばれます。

交差演算子を使えば、行と列の交差参照で格子目の参照を得られるはずです。

【手順例】

1. 出力先のセルを入力モードにし、「=」をタイプして数式の入力状態にします
   • 列選択・行選択をするので、干渉しない位置にあるセルを選ぶよう注意してください。別シートになら問題ありません。
2. 対象セルを含む列の列全体を列選択していきます
   • 列選択するにはCtrl キーを押しながらシート上の列見出し（A, B, C,...）をクリックします
   • カンマ区切りで列範囲のアドレスが数式に追加されていきます
3. 半角スペースを数式の最後に入力します
4. Ctrl キーを押しながら、集計セルを含む行の行全体を行選択していきます
   • 行選択するにはCtrl キーを押しながらシート左の行見出し（1, 2, 3, ...）をクリックします
   • カンマ区切りで行範囲のアドレスが数式に追加されていきます
5. 数式バーで差最終的に以下のようになるよう数式を編集します
   • 列アドレスのかたまりと行アドレスのかたまりは、それぞれ丸カッコで囲みます
   • 行アドレスのかたまりと行アドレスのかたまりの間に半角スペース一個（交差演算子）が挟まれているようにします
   • 全体を SUM 関数など任意の集計関数に渡します
     
6. Enter で入力完了します
   • 交差している位置のセルのみの値が集計されています

必要なら数式を縦横のセルにコピーします。相対参照で格子をずらした位置の集計が得られます。

ところで、Clrl キーを押しながらセルを選択していくと、その参照アドレスが「,」（カンマ）区切りで追記されていきますが、 このカンマも参照の複数のセル範囲を合わせた範囲を表す参照を作るという演算子で、「結合演算子」といいます。

交差演算子も結合演算子も「参照演算子」という参照を評価する演算子の一種です。 演算子の優先度が結合演算子より交差演算子の方が高いので、併用する場合には結合演算子で結合した参照の方を括弧で括っておく必要がありました。

参照演算子について

Excel 数式でつ変える参照演算子の仲間には４種があり、まとめるの以下表のようになります。
　

　
参照演算子の結果は数式のどこでも利用できるわけではありません。SUM 関数など、可変長引数で参照を受け取れる関数への引数としてなら問題ないようです。

「暗黙的交差演算子」は動的配列数式（スピルなど）がサポートされた Excel で追加された演算子です。
詳しく知りたい方は以下のマイクロソフトのサポートページを参照してください。

• Excel の数式に計算演算子を使用する
• 暗黙的な交差演算子:@

---

---

《おまけ》縦横集計をする LAMBDA 数式

LAMBDA 関数を使って縦横集計をする数式を作成してみました。 コピー＆ペーストで使えます。 Microsoft 365 版（サブスク版）の Excel でのみ使用できます。

使い方は、下記数式を「名前の定義」に適当な関数名でユーザ定義関数として登録するか、セルに直接貼り付けて、各引数にセル範囲を指定するだけす。

=LAMBDA(データ範囲, [項目列], [項目行],
    IF(NOT(ISREF(データ範囲)), "データ範囲はセル参照で指定してください",
        LET(
            data, データ範囲,
            rowDim, IF(ISOMITTED(項目列), OFFSET(data, 0, -1,,1), TAKE(項目列,,-1)),
            colDim, IF(ISOMITTED(項目行), OFFSET(data, -1, 0,1), TAKE(項目行,-1)),
            IFS(
                ROWS(rowDim) <> ROWS(data), "行数がそろいません",
                COLUMNS(colDim) <> COLUMNS(data), "列数がそろいません",
                TRUE, LET(
                    rowCats, UNIQUE(rowDim),
                    colCats, UNIQUE(colDim, TRUE),
                    consol, MAKEARRAY(ROWS(rowCats), COLUMNS(colCats),
                        LAMBDA(r, c, LET(
                            rowKey, CHOOSEROWS(rowCats, r),
                            colKey, CHOOSECOLS(colCats, c),
                            vGrp, FILTER(data, rowDim=rowKey),
                            vhGrp, FILTER(vGrp, colDim=colKey),
                            SUM(vhGrp)
                        ))
                    ),
                    HSTACK(
                        VSTACK("", rowCats),
                        VSTACK(colCats, consol)
                    )
                )
            )
        )
    )
)

【構文】

関数名を仮に「縦横集計」とした場合。

縦横集計(データ範囲, [項目列], [項目行])

【引数】

引数	内容
データ範囲	集計するデータ範囲の参照
項目列	行を集約する項目の範囲（省略可）
項目行	列を集約する項目の範囲（省略可）

【戻り値】

行と列について項目名で集約した合計値の配列。
1行目は列項目、1列名は行項目。

【説明】

この数式は項目行と項目列の項目名をもとに縦と横にセルを集約し合計した表を出力します。結果はスピルします。 出力には、見出しとして集約された項目行と項目列を含みます。

項目名は英字の大文字と小文字を区別しません。

「データ範囲」はセル範囲の参照（アドレス）である必要があります。

一方、「項目列」と「項目行」は「データ範囲」に隣接していなくてもかまいません。また、配列で指定してもOKです。

「項目列」は「データ範囲」と同じ行数である必要があります。複数列が指定された場合は、一番右の列が採用されます。

「項目行」は「データ範囲」と同じ列数である必要があります。複数行が指定された場合は、一番下の行が採用されます。

「項目列」や「項目行」は省略できます。これらを省略した場合、データ範囲に隣接する左の列が「項目列」に、上の行が「項目行」として使用されます。

【注意】 集計結果が合わないなど、不具合がありましたら、コメントなどでお知らせください。

まとめ

いろいろ試しましたが、お手軽さという意味ではどれも一長一短な感じです。

「統合」は、同様の機能としてピボットテーブルなどもっと便利で高度な機能の登場で、現在はほとんど日の目を見ないと思います。 筆者もそういう機能があるのを知ってはいましたが、今回初めて使いました。

配列や参照の演算はこれまで上級者向けの知識でしたが、今後スピルや LET 数式、 LAMBDA 数式などがポピュラーになるにつれ多用されることになると思います。 これまで触れることがなかった人の足掛かりとなるよう、少し解説も追加しました。

今回紹介した方法は、どれも一時的にデータを確認するためのものです。 レポート作成やデータ分析のための集計には、こんな小技を使わずに、ピボットテーブルや Power Query、CUBE関数といったそれ用の機能を使いましょう。

参考資料

• 複数シートのデータを統合する - マイクロソフトサポート
• Excel の数式に計算演算子を使用する - マイクロソフトサポート
• 暗黙的な交差演算子:@ - マイクロソフトサポート

関連記事

変更履歴

• [2022/10/31] 公開