Excelでデータを管理していると、
- 商品別
- 部署別
- 担当者別
にシートを分けて一覧を作成したいことがあります。
通常はフィルターやコピー&ペーストでデータを振り分けますが、件数が増えると手間がかかります。
そこで今回は、
FILTER関数とCELL関数を組み合わせて、シート名に応じたデータを自動表示する方法を紹介します。
一度設定してしまえば、
✅ シート名を変更するだけ
✅ データが自動で切り替わる
✅ コピー作業が不要
になります。
目次
この記事でできること
✅ FILTER関数で条件抽出する
✅ CELL関数でシート名を取得する
✅ シート名を条件にしてデータを抽出する
✅ シート別一覧を自動作成する
早見表
| やりたいこと | 数式 |
|---|---|
| シート名取得 | CELL(“filename”) |
| 末尾3文字取得 | RIGHT(CELL(“filename”),3) |
| データ抽出 | FILTER(Data,条件,”-“) |
| シート名で抽出 | FILTER(Data,Data[カテゴリー]=RIGHT(CELL(“filename”),3),”-“) |
概要
エクセル作業の中で面倒な作業の一つが、1シートの内容を複数シートに振り分ける作業です
この面倒な作業を、「1つのセルへの入力で複数のデータを出力するFilter関数」と「シート名を自動抽出するCell関数」を組み合わせて簡単に処理する方法を解説します!

今回の解説で対象とする「振り分け作業」は次のパターンです
1.あるファイルの1シートの中に、データをまとめる

2.1.のファイルの中に複数のシートを作成し、「シート名」を変更

3.2.の「シート名」にあわせて、1.のデータを振分け

上の作業は、日常的によく見られる作業だと思います
今回は、上の3.の箇所を「Filter関数xCell関数」で省力化する方法を以下に解説していきます!
ポイント
●ポイント1⇒Cell関数とは?
Cell関数とは 、主にセルの情報を取得するための関数です
今回は、シート名を取得するために使用します
Cell関数は、あるシート上にてCELL(“fileame”)と入力すると、次のGIF画像のように「フィルパス+ファイル名+シート名」を抽出します

上のGIF画像では「テスト」というシート名を自動抽出しています
シート名が常に同じ桁数であれば、RIGHT関数でシート名を抽出できます
●ポイント2⇒FILTER関数とは?
FILTER関数では次のように3つの引数を指定し、ⅰの範囲から、ⅱの条件に一致するデータを抽出します
FILTER(ⅰ.範囲、ⅱ.抽出条件、ⅲ.[省略可])
抽出範囲のデータの行数が可変であれば、抽出元のデータ範囲を事前にテーブル化しておくのがおすすめです
例えば、抽出元を以下のようにDataの名前でテーブル化しておいたとします

そして、シート別に抽出したいのは、左から2列目のカテゴリー別だとします
この場合、=FILTER(Data,Data[カテゴリー]=”aaa”)と書きます

では、
ポイントを2つ解説したところで、詳細な解説に入ります!(最後のまとめの章で、使用に関する注意点が1点あります)
解説
シート名から上の画像の「カテゴリー/3桁」を抽出し、Filter関数の条件となるように数式を書くと、次の様になります
=FILTER(Data,Data[カテゴリー]=RIGHT(CELL("filename"),3),"-")
このようにFILTER関数を入力すれば、シート名を変えた時には、次のGIF画像のように「シート名に合うデータ」を抽出します

シート名を「CCC」 にした時、「データ」のシートからカテゴリーが「CCC」のデータを抽出しています
<まとめ>
Filter関数は1つのセルに入力しさえすれば、条件に合う複数のデータを一括で出力してくれますので、集計作業にはとても便利です
Filter関数の条件の設定を工夫すれば、集計作業の幅を格段に広がります
なお、今回のFilter関数の使い方には1つ注意点があります
振分けを行った2つめのシートにてFilter関数を入力した際は、1つ目のシートの内容が2つめのシートの内容に置き換わってしまいます

ですので、1つのシートでFilter関数を入力した後「値コピー」でデータの確定を行うと安全に処理を進めていくことができます
今回は以上です
FILTER関数も含めて1つのセル入力で複数のデータ出力を行う「新関数」に興味のある方は、ぜひこちらも参照してください










コメントを残す