1行でExcel別シートのデータを自動反映する方法4選！最終行も自動取得！

Excelで別シートのデータを自動反映させたいときはないでしょうか。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

これまでの別シートのデータを自動反映させる方法の問題点

これまで、別シートのデータを参照する場合は、「＝＜別シート名＞！+＜セル番地＞」の数式を利用することが一般的でした。

この方法の場合はカンタンに別シートのデータを参照できる一方で、セルひとつづつに数式を入力する必要があり、数式の入れ忘れや、元データの変動の度に数式も修正しなければならない手間がありました。

今回はこれらの手間を一気に解消できるMicrosoft 365からの新機能である「スピル」を使った方法についてご紹介します。

1行でExcelの別シートのデータを自動反映させるイメージ

スピル1行でExcelの別シートのデータを自動反映させるイメージについて説明をします。

サンプルでは元データシートに表とデータを入力し、参照先シートで元データシートの表を反映するようにしています。

参照先シートに1行の数式を入れると、

元データシートの表が一気に反映されていきます。

次に元データシートへデータを付け加えます。

範囲指定を固定値とすると参照先で反映されることはありませんでしたが、最終行を動的に取得する機能を追加することで、参照先シートの数式をまったく変えることなく増分データが反映することができます。

さらに、参照先でもデータの表示形式を変えることができます。

参照先で並び順を変えたり、

取得データを絞ったりもできます！

それでは早速実装して試してみましょう。

タカヒロ

スピルはMicrosoft365以降で実装された機能となりますので、それ以前バージョンでは使用できないことご了承ください。

別シートのデータを自動反映させる対象の表を用意する

別シートのデータを自動反映させる対象となる表を用意しましょう。

サンプル表は以下のようにA列を番号、B列を商品名、C列を売上にし、シート名を元データとしました。

参照先は項目だけ用意してください。

方法1：スピルの範囲指定で別シートのデータを自動反映させる

一つ目の別シートのデータを自動反映させる方法はスピルの範囲指定で参照する方法となります。

スピルとは配列のことで、これまで1つのセルに対して1つの数式を入れて処理していましたが、配列になったことで、一つの数式で複数の値を複数のセルに出力することができるようになりました。
これを動的配列数式とよばれています。

タカヒロ

分かりにくいと思いますので、早速数式を実装して確認してみましょう。

数式は以下となります。参照先シートのA2セルに入力します。

はい、元データシートの表が反映されましたね。

タカヒロ

数式がない部分にも値が表示されていることが確認できましたね。
ちなみに数式がない部分をゴーストと呼ぶようです。

元データの表の最終行を自動取得する

なお、範囲指定の部分が固定値となりますので、当然ながら元データの表にデータが追加されても参照先シートでは反映されません。

そこで、元データの表の最終行を自動取得し、参照先で参照範囲が動的になるよう変更していきたいと思います。

数式は以下となります。参照先シートのA2セルに入力します。

はい、元データシートの表が反映されましたね。

まず、元データ表のA列の値がある件数をカウントします。

次に”元データ!A2:C”と最終行となるカウント値を結合し、INDIRECT関数で文字列を数式として認識させます。

方法2：スピル系SORT関数で別シートのデータを自動反映させる

2つ目の別シートのデータを自動反映させる方法はスピル系関数であるSORT関数を使用します。
SORT関数はその名の通り並び替えをおこなう関数となり、この関数で別シートのデータを参照と同時に並び替えを行っていきたいと思います。

SORT関数の書式と機能

SORT関数の書式と機能については以下の通りです。

SORT関数で別シートのデータを自動反映させる

さっそくSORT関数でデータを自動で並び変えてみましょう。

表を作成したシートのF2セルに以下の数式を入力します。

はい、並び替えができましたね。

SORT関数の第1引数「範囲」にA2:C8を指定し、

その範囲の1列目つまりA列を指定し昇順に並び替えています。

SORT関数の範囲で指定する最終行を自動的に取得する

これまでSORT関数の範囲は「A2:C8」のように固定値でしたが最終行判定を行い、範囲を動的にするよう変更をしてみましょう。

数式は以下の通りです。F2へ実装をしてみましょう。

はい、最終行が自動で判別され、表のデータすべてが反映されていますね。

方法3：スピル系SORTBY関数で別シートのデータを自動反映させる

3つ目の別シートのデータを自動反映させる方法はスピル系関数であるSORTBY関数を使用します。
SORTBY関数はSORT関数と同じく並び替えをおこなう関数となり、このSORTBY関数を使用して別シートのデータを参照と同時に並び替えを行っていきたいと思います。

SORTBY関数の書式と機能

SORTBY関数の書式と機能については以下の通りです。

SORTBY関数で別シートのデータを自動反映させる

さっそくSORTBY関数でデータを自動で並び変えてみましょう。

表を作成したシートのF2セルに以下の数式を入力します。

はい、並び替えができましたね。

SORTBY関数の第1引数「範囲」にA2:C8を指定し、

第二引数の並び替え基準を”A2:A8”で指定し、第三引数並び替えを1で昇順に並び替えています。

SORTBY関数の範囲で指定する最終行を自動的に取得する

これまでSORTBY関数の範囲は”A2C8”、”A2:A8”のように固定値でしたが最終行判定を行い、範囲を動的にするよう変更をしてみましょう。

数式は以下の通りです。F2へ実装をしてみましょう。

はい、最終行が自動で判別され、表のデータすべてが反映されていますね。

方法4：スピル系FILTER関数で別シートのデータを自動反映させる

4つ目の別シートのデータを自動反映させる方法はスピル系関数であるFILTER関数を使用します。
FILTER関数は対象の表から条件で絞り込みをおこなう関数となり、このFILTER関数を使用して別シートのデータを参照と同時に絞り込みを行っていきたいと思います。

FILTER関数の書式と機能

FILTER関数の書式と機能については以下の通りです。

FILTER関数で別シートのデータを自動反映させる

さっそくFILTER関数でデータを自動で並び変えてみましょう。

表を作成したシートのF2セルに以下の数式を入力します。

はい、絞り込みができましたね。

FILTER関数の第1引数「範囲」にA2:C8を指定し、

第二引数の絞り込み条件を”A2:A8”が1であった場合抽出されるとし、第三引数は条件にマッチしなかった場合は空””になるように指定しています。

また、B列の商品名で部分一致させ抽出することもできます。
サンプルは商品名に「いも」が含まれてる行のみ抽出する内容となっています。

FILTER関数の範囲で指定する最終行を自動的に取得する

これまでFILTER関数の範囲は”A2C8”、”A2:A8”のように固定値でしたが最終行判定を行い、範囲を動的にするよう変更をしてみましょう。

元シートの表にデータを加えます。

数式は以下の通りです。F2へ実装をしてみましょう。

はい、最終行が自動で判別され、表のデータの追加分が反映されていますね。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。