Excelにおける、データと閲覧の分離

Excelはそもそも表計算アプリケーションであり、それに設計モデルを厳密に当てはめて議論するのは無理がありますし、DataだのViewだのを本格的にどうこうしようとしてもしょうが無いです。しかし、Excelを使う際に、

• データ

• 閲覧

これを分けて考えておくのは、実務上とても重要です。

まず、AIで架空の表を作り、それをExcelのテーブルにしました。

Amazon等の総合ショッピングモールサイトの売上一覧、という感じです。これは、

• 1つのセルに一つのデータ（データは複数形ですが、意味は通るので単数形もデータとします）を格納

• 1件を横並び（行）で表現

• 見出しで、何を表現するデータであるかを表記

• 同じ種類のデータは縦（列）に並ぶ

このような整然な構造をとっています。またそもそも、Excelのテーブルは、シート内で上記のような構造を強制させる仕組みです。従って、テーブル内のセルは結合出来ません。

これが、業務に必要な情報の集まりを整理したデータである、と捉えます。

業務では、このようなデータを基盤にして、それを加工したり、帳票表示させたりします。つまり、
データ→閲覧
という流れでアプリケーションを構成するわけです。恐らくもっとも解りやすいのが、

• 住所録（データ）→年賀状

このような構成でしょう。初学者向けのOfficeの教材でも良く採り上げられます。別シートに葉書きのレイアウトを作り、そこにデータを参照させ各人の年賀状を表示させていったり、Wordにデータを流し込んで、複数枚のレターファイルを作る（差し込み印刷）などします。※長年、Microsoft Officeを使っていながら、差し込み印刷を知らない人もいます

年賀状を14人分作るとします（最近は紙の年賀状も、ほぼ見なくなりました）。その場合、シートを14枚つくって、それぞれのシートの宛名部分や差出人のセルを探し出し、そこに文字を入力していく作業を想像してみてください。実に面倒でしょう。これが、
データと閲覧が絡み合っている
ような状態です。そのような事は避けておくべきです。私は、SEがそのような構成の仕様書などを作っているのを何度も見た事があります。全く同じ方眼紙シートを何枚も作ってあり、そこに異なる文字列を入力していくのです

さて、冒頭で示した、架空の売上表を使います。
なお、多くの場合、データとそれを加工する所はシートを分けて管理しますが、本記事では、説明画像のスペースの都合もありますし、何より近くにあるほうが構造的な繋がりが解りやすいので、テーブルの周りに加工したものを配置した画像を示します。

まず、絞り込みをやってみましょう。ある列について、着目する値を有する行のみを表示させるわけです。
Excel2021からであれば、FILTER関数が使えます。

まず、見出しを作っておきます。テーブルの見出し行の指定子で参照すれば、そのままスピルします。※書式は適当に適用して行きます

=sales[#見出し]

次に、左端の見出しセルの真下に、絞り込んだ表を入れましょう。
試しに、商品カテゴリが食品であるものに絞った表にします。

=FILTER(
   sales,
   sales[商品カテゴリ]="食品"
 )

FILTER関数の第1引数は配列、第2引数は、第1引数で渡した配列の可視不可視を定義する1次元配列です。テーブル内の着目する列本体と一致させたい（絞り込みたい）値を等号で結べば、列ベクトル=スカラーの比較となり、真偽値の配列が返ります。これが可視条件（TRUEは可視）となり、結果的に、商品カテゴリが食品である配列が返って、それがスピルされるという寸法です。

これだと何一つ汎用性がありませんので、数式内にリテラル（"食品"）を書くのを避け、入力規則のリストなどで入力したものを参照させるなどすると、汎用的になるでしょう。

単に絞り込むのであれば、オートフィルターを使えば済む話です。Excelのフィルターは極めて強力です。FILTER関数等で絞り込むのは、別シートに抽出して使う場合などです。

次は、1件ずつを要約したカード表示をさせるのを考えます。SharePoint Listsや、色々のローコードプラットフォームでのアプリケーションを使っていれば、そういう加工（ビュー）が標準搭載されているのを見るでしょう。これをやります。

ポイントは、そういうカード表示では大抵、全ての列は要らない所です。
業務システムからCSVファイルを取得し、そこから加工して使いたい場合を考えると解りやすいでしょう。自分の業務には不要な列がたくさんあるものです。それを除外したいわけです。

カード表示は、見出しにあたる部分を縦に並べたほうが視認性が良い事があります。しかるに、Excelのテーブルはデータベース的な構造なので、横方向で1件ずつのまとまりを表現しています。どうしましょうか。

いまは、注文IDを検索し

• 注文日時

• 商品カテゴリ

• 商品名

• 合計金額

• 都道府県

を抽出して確認したいとします。

表示させる列が固定的であれば、抽出する見出しを縦に並べて、テーブルからルックアップさせる方法が使えます。

まず、検索ボックスを作っておきましょう。

売上表の注文IDを、データの入力規則で参照し、リスト入力出来るようにしておきます。

次に左側に（スペースの都合上です）、カード用のテーブルを作ります。縦表示させるので、列名自体をテーブルの列で管理します。抽出された値は右側に表示させます。

さて、これからどう抽出しましょうか……XLOOKUP関数の出番です。
ロジックは、

• 検索ボックスで入力されているIDを参照して

• 売上表のID列を検索し

• 所望の列の値を取得

こうです。ここで重要なのは、
売上表の列名と、カードで表示されている（つまり抽出列の各値の）列名を同じにする
事です。
数式は次のように書きましょう。※エラーハンドリングは省略

=XLOOKUP(
   search_box[検索],
   sales[注文ID],
   INDIRECT("sales[" & [@抽出列] & "]")
 )

言葉で書くと、

1. search_boxテーブルのボディ部分を参照して

2. salesテーブルの注文ID列を検索し

3. 見つかったら、自分がいる行の抽出列にある値を取得し、salesテーブルへの構造化参照を作成して、参照しに行った列から値を取得する

このようです。3番目が複雑ですね。

INDIRECT("sales[" & [@抽出列] & "]")

第3引数を抜き出しました。いま私たちは、カードテーブルの抽出列にある値と同じ名前の列を、売上表に探しに行こうとしています。それは構造化参照だと、

=sales[注文日時]

こうなります（注文日時の場合）。このようなものを動的に作るために、INDIRECT関数を使うのです。

後は、検索ボックスで所望の値を選択すれば、それに対応したカードの出来上がり、です。

これは、実際に私が実務で使用している構成のものです。表示する列内容の変更が少ないのであれば、これで充分。

いまは、カード用テーブルを作って、各抽出列に対応したXLOOKUPを作りました。これを、一括で作る方法も考えてみましょう。

一括表示といえばもちろん、FILTER関数です。ですが、今は要件として、

• 縦表示

• 列を全部は使わない

このようなものがあります。そのままフィルターしたのでは出来ません。どうしましょう。

FILTER関数は、配列の可視不可視を設定して、絞り込んだ配列を返す関数でした。これは、縦横、つまり行列のどちら方向にも適用できます。第2引数の配列を、行ベクトル（横）か列ベクトル（縦）にするかの違いです。
今回は、

• 注文日時

• 商品カテゴリ

• 商品名

• 合計金額

• 都道府県

上記5列分のデータを抽出してカード化するのでした。なれば、まず
必要な表示列でフィルター
すれば良いのです。

FILTER関数の第2引数は、スイッチの役割をするような配列です。そして、着目する列に絞り込みたいのだから、スイッチそのものを表現するテーブルを作れば良いのです。

売上表と全く同じ見出しを持つテーブルを作り、本体は1行にして、全てFALSEを入れました。これがスイッチ群です。次に、表示したい列がある所をTRUEに、つまりスイッチオンにしましょう。

そして、これを使えば、表の列を絞り込んだ配列が得られます。実験として、売上表のすぐ下に作りましょうか。

=FILTER(sales[#すべて],visible)

うまく絞り込まれましたね。ここでは見出しごとテーブルをフィルターするので、指定子はsales[#すべて]と書きました。そして、visibleテーブルの本体をスイッチとして投入するだけです。これは行ベクトルなので、フィルターは列でおこなわれます。

このように、列が絞り込めましたが、このままでは、所望の注文IDで絞り込んだカードではありません。しかも、まだ横方向です。

先ほどの、列での絞り込みFILTERをいったん削除して、今度は、注文IDでの絞り込みを作りましょう。やりかたは当然、よく使われる方法です。初めのほうで実施した絞り込みを、1件しか無いのが判っているID列でおこなうのです。

=FILTER(sales,sales[注文ID]=search_box[検索])

もはや、このようなFILTER関数は、シンプルに見えてきたのではないでしょうか。salesテーブルの本体部分を、注文IDを対象にして、検索ボックスにある値で絞り込むというだけです。

これは、行を絞り込んだだけです。しかし作りたいカードは縦向きです。だから、これを縦向きにしましょう。そう、TRANSPOSE関数です。TRANSPOSEは転置、つまり、
行と列をひっくり返す
関数です。フィルターしたものをTRANSPOSE関数でくるんであげましょう。

=TRANSPOSE(
   FILTER(sales,sales[注文ID]=search_box[検索])
 )

フィルターされた行が引っくり返って、列になっていますね。

さあ、部品は揃いました。カードを作るロジックは次のようです。

見出し部分↓

=TRANSPOSE(
   FILTER(sales[#見出し],visible)
 )

• salesテーブルの見出し行を

• visibleテーブルで定義したスイッチ群に従って列でフィルター

• 転置させる

テーブル本体部分↓

=TRANSPOSE(
   FILTER(
      FILTER(sales,sales[注文ID]=search_box[検索]),
      visible
   )
 )

• salesテーブルの本体を、search_boxの値でフィルター（検索は注文IDでおこなう）

• 更に、visibleテーブルで定義したスイッチ群に従ってフィルター

• 転置させる

ここでは、見出しと本体部分を分けて構成していますが、STACK系関数を使ってまとめて作る方法もあります。色々工夫して実装していけば良いでしょう。

このように、一口にカード的なビューを作ると言っても、複数のアプローチがあります。Excelには、とにかくたくさんのワークシート関数が組み込まれていますので、

• 使用者のExcelバージョン

• 共有対象のExcelバージョン

• 表の配置

• 可読性やメンテナンス性

等々の要件を検討し、より扱いやすい作りかたを模索するのが良いでしょう。そのためには、
様々の手法を知っておく
のが肝要です。たとえば、シンプルにテーブルの行をカード表示させたいなら、フォーム機能を使えば簡単だったりするのです。

繰り返すと、データと閲覧を分けるのが重要なのは当然として、それをどう分離し、どのような方法で閲覧部分を構成していくか、というのは、複数の要件を勘案して丁寧に考えていくべきものであって、最良な方法がシンプルに提示できるような事ではありません。