【エクセル】FILTER関数等を使ってひとつのデータからグループ毎にシート分割する。
今回は、ひとつのデータを一定のグループ毎にシート分割する方法について考えてみました。VBA・パワークエリは使わずにワークシート関数のみを使用して処理します。（但し、Microsoft365以降で使用可能な関数を含みます）
【目次】
１．やりたいこと
次のような売上データがあって、例えば部門毎にシートを分割して管理したいといったケースとなります。
【データ】‥事前にテーブル化してあり、「テーブル1」という名前にしています。
２．FILTER関数等を使ってシートを分割する～その１～
まず、新規ワークシートを挿入して、A1セルに次の数式を入力します。
=テーブル1[#見出し]
テーブル1の見出しが入ります。
次に、A2セルに次の数式を入力します。
=FILTER(テーブル1,テーブル1[部門]=INDEX(SORT(UNIQUE(テーブル1[部門])),SHEET()-1,1))
部門Aのデータが入ります。
数式の内容について次の画像にまとめています。
Sheet1を右隣りにコピーします。
すると、自動的に部門Bのデータが入りました。これは、前画像説明の数式のINDEX関数の第2引数に入れたSHEET関数が、シートを追加したことによってカウントアップ(+1）されたことによるものです。
同様にシートをコピー追加していけばＣ、Ｄのデータができます。デメリットとしては、例えばこのあとシート名を各部門名に変更したとして、更にその後で別のワークシートを挿入したりすると、シート名と中身が相違してしまうことです。それを踏まえて次の項目では少しやりかたを変えてみます。
３．FILTER関数等を使ってシートを分割する。～その２～
別法です。新規ワークシートを挿入して、A1セルに=テーブル1[#見出し]を入れるところまでは最初と同じです。
今度は、A2セルに次の数式を入力します。
=FILTER(テーブル1,テーブル1[部門]=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))
この数式の上記青字部分はCELL関数によってファイル名・シート名を表示させ、そこからRIGHT、LEN、FIND関数でシート名を抜き出しています。（ファイルを一旦保存しておく必要があります。）次画面ではこの数式結果がエラーとなっています。FILTER関数の第2引数は訳すと、
テーブル1[部門]= Sheet1（前述のシート名を抜き出した結果です）
となりますが、テーブル1の部門に Sheet1 という名前の部門は存在しない為です。
ですので、シート名を A に変更してあげると、次の通りA部門のデータが表示されます。
シートをコピーし、部門B、Cについても同様にシート名を変えればできあがります。
シート分割する数にあまり変動がないようであればこの対応でよいかなと思いますが、部門数が増えたときにシートの追加漏れがないようチェック用の管理シートを作成しておこうと思います。
新規ワークシートを挿入して名前をシート管理（何でもよいです）とします。
A1セルに「部門」、B1セルに「シートNo.」等と手入力します。
A2セルに次の数式を入力します。
=SORT(UNIQUE(テーブル1[部門]))
～その1～の関数の一部と同じで、テーブル1の部門列の一意の値を昇順にソートしたものが表示されます。
B2セルに次の数式を入力後、コピーして最下行まで貼り付けます。
=IFERROR(SHEET(TEXT(A2,"@")),"シートを追加してください。")
SHEET関数を使って、A部門（A2セル）のシートがこのブックの中の何番目に位置するかが計算結果として表示されます。IFERROR関数でエラーの場合は、”シートを追加してください”と表示するようにしました。今回、テーブル1にD部門は登場していますが、この時点でシートを追加していないため、B5セルの結果はエラーとなり、メッセージが表示されています。
ＣのシートをコピーしてＤを追加してみます。
シート管理のB5のメッセージがシート番号に変わりました。
４．さいごに
今回、FILTER関数等を使って試しにやってみましたが、VBAであればもっと簡単にできると思います。パワークエリでも考えてみたのですが、分割する対象が増えた場合に自動的にクエリ（テーブル）を増やしてワークシートに反映するといった処理は難しそうです。
