Excelで別シートのデータを自動反映させたいときはないでしょうか。
けど、そんな中で悩むことは、
・1セルづつ数式を入れるのは面倒なので、まとめてデータを参照したいが方法がわからない
・元データが増えても参照先で反映されないので、動的に参照するようにしたいが方法がわからない
ですよね。
今回はそんなお悩みを解決する
・元データの最終行を取得し、動的にデータを取得する方法
についてまとめます！
もくじ
これまでの別シートのデータを自動反映させる方法の問題点
これまで、別シートのデータを参照する場合は、「＝＜別シート名＞！+＜セル番地＞」の数式を利用することが一般的でした。
この方法の場合はカンタンに別シートのデータを参照できる一方で、セルひとつづつに数式を入力する必要があり、数式の入れ忘れや、元データの変動の度に数式も修正しなければならない手間がありました。
今回はこれらの手間を一気に解消できるMicrosoft 365からの新機能である「スピル」を使った方法についてご紹介します。
1行でExcelの別シートのデータを自動反映させるイメージ
スピル1行でExcelの別シートのデータを自動反映させるイメージについて説明をします。
サンプルでは元データシートに表とデータを入力し、参照先シートで元データシートの表を反映するようにしています。
参照先シートに1行の数式を入れると、
元データシートの表が一気に反映されていきます。
次に元データシートへデータを付け加えます。
範囲指定を固定値とすると参照先で反映されることはありませんでしたが、最終行を動的に取得する機能を追加することで、参照先シートの数式をまったく変えることなく増分データが反映することができます。
さらに、参照先でもデータの表示形式を変えることができます。
参照先で並び順を変えたり、
取得データを絞ったりもできます！
それでは早速実装して試してみましょう。
別シートのデータを自動反映させる対象の表を用意する
別シートのデータを自動反映させる対象となる表を用意しましょう。
サンプル表は以下のようにA列を番号、B列を商品名、C列を売上にし、シート名を元データとしました。
参照先は項目だけ用意してください。
方法1：スピルの範囲指定で別シートのデータを自動反映させる
一つ目の別シートのデータを自動反映させる方法はスピルの範囲指定で参照する方法となります。
スピルとは配列のことで、これまで1つのセルに対して1つの数式を入れて処理していましたが、配列になったことで、一つの数式で複数の値を複数のセルに出力することができるようになりました。
これを動的配列数式とよばれています。
数式は以下となります。参照先シートのA2セルに入力します。
=元データ!A2:C8はい、元データシートの表が反映されましたね。
ちなみに数式がない部分をゴーストと呼ぶようです。
元データの表の最終行を自動取得する
なお、範囲指定の部分が固定値となりますので、当然ながら元データの表にデータが追加されても参照先シートでは反映されません。
そこで、元データの表の最終行を自動取得し、参照先で参照範囲が動的になるよう変更していきたいと思います。
数式は以下となります。参照先シートのA2セルに入力します。
=INDIRECT("元データ!A2:C"&COUNTA(元データ!A:A))はい、元データシートの表が反映されましたね。
まず、元データ表のA列の値がある件数をカウントします。
COUNTA(元データ!A:A)次に”元データ!A2:C”と最終行となるカウント値を結合し、INDIRECT関数で文字列を数式として認識させます。
INDIRECT("元データ!A2:C"&方法2：スピル系SORT関数で別シートのデータを自動反映させる
2つ目の別シートのデータを自動反映させる方法はスピル系関数であるSORT関数を使用します。
SORT関数はその名の通り並び替えをおこなう関数となり、この関数で別シートのデータを参照と同時に並び替えを行っていきたいと思います。
SORT関数の書式と機能
SORT関数の書式と機能については以下の通りです。
| 関数名 | SORT |
|---|---|
| 機能 | 範囲または配列の内容を並べ替えます。 |
| 書式 | =SORT(範囲, ［並べ替えインデックス］, ［順序］, ［方向］) |
| 引数 | 並べ替えインデックス：並べ替えの基準となる行または列番号を指定します。既定は1です。 順序：目的の並べ替え順序を数値で指定します。昇順の場合は 1で既定、降順の場合は -1となります。 方向：目的の並べ替え方向を示す論理値を指定します。FALSE(既定)の場合は、行で並べ替え、TRUE の場合は、列で並べ替えます。 |
| エラー値 | #VALUE! |
SORT関数で別シートのデータを自動反映させる
さっそくSORT関数でデータを自動で並び変えてみましょう。
表を作成したシートのF2セルに以下の数式を入力します。
=SORT(元データ!A2:C8,1,1,FALSE)はい、並び替えができましたね。
SORT関数の第1引数「範囲」にA2:C8を指定し、
その範囲の1列目つまりA列を指定し昇順に並び替えています。
SORT関数の範囲で指定する最終行を自動的に取得する
これまでSORT関数の範囲は「A2:C8」のように固定値でしたが最終行判定を行い、範囲を動的にするよう変更をしてみましょう。
数式は以下の通りです。F2へ実装をしてみましょう。
=SORT(INDIRECT("元データ!A2:C"&COUNTA(元データ!A:A)),1,1,FALSE)はい、最終行が自動で判別され、表のデータすべてが反映されていますね。
方法3：スピル系SORTBY関数で別シートのデータを自動反映させる
3つ目の別シートのデータを自動反映させる方法はスピル系関数であるSORTBY関数を使用します。
SORTBY関数はSORT関数と同じく並び替えをおこなう関数となり、このSORTBY関数を使用して別シートのデータを参照と同時に並び替えを行っていきたいと思います。
SORTBY関数の書式と機能
SORTBY関数の書式と機能については以下の通りです。
| 関数名 | SORTBY |
|---|---|
| 機能 | 範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。 |
| 書式 | =SORTBY(範囲, 基準1, ［順序1］, ［基準2, 順序2］…) |
| 引数 | 範囲：データの範囲を指定します。 基準：並べ替えの基準となる範囲を指定します。 順序：目的の並べ替え順序を数値で指定します。昇順の場合は 1で既定、降順の場合は -1となります。 |
| エラー値 | #VALUE! |
SORTBY関数で別シートのデータを自動反映させる
さっそくSORTBY関数でデータを自動で並び変えてみましょう。
表を作成したシートのF2セルに以下の数式を入力します。
=SORTBY(元データ!A2:C8,元データ!A2:A8,1)はい、並び替えができましたね。
SORTBY関数の第1引数「範囲」にA2:C8を指定し、
第二引数の並び替え基準を”A2:A8”で指定し、第三引数並び替えを1で昇順に並び替えています。
SORTBY関数の範囲で指定する最終行を自動的に取得する
これまでSORTBY関数の範囲は”A2C8”、”A2:A8”のように固定値でしたが最終行判定を行い、範囲を動的にするよう変更をしてみましょう。
数式は以下の通りです。F2へ実装をしてみましょう。
=SORTBY(INDIRECT("元データ!A2:C"&COUNTA(元データ!A:A)),INDIRECT("元データ!A2:A"&COUNTA(元データ!A:A)),1)はい、最終行が自動で判別され、表のデータすべてが反映されていますね。
方法4：スピル系FILTER関数で別シートのデータを自動反映させる
4つ目の別シートのデータを自動反映させる方法はスピル系関数であるFILTER関数を使用します。
FILTER関数は対象の表から条件で絞り込みをおこなう関数となり、このFILTER関数を使用して別シートのデータを参照と同時に絞り込みを行っていきたいと思います。
FILTER関数の書式と機能
FILTER関数の書式と機能については以下の通りです。
| 関数名 | FILTER |
|---|---|
| 機能 | 対象の表から条件で絞り込んだ結果を表示します。 |
| 書式 | =FILTER(範囲,含む（条件）,空の場合) |
| 引数 | 範囲：データの範囲を指定します。 含む（条件）：抽出条件を指定します。 空の場合：空の場合の処理内容を指定します。 |
| エラー値 | #VALUE! |
FILTER関数で別シートのデータを自動反映させる
さっそくFILTER関数でデータを自動で並び変えてみましょう。
表を作成したシートのF2セルに以下の数式を入力します。
=FILTER(元データ!A2:C8,元データ!A2:A8=1,"")はい、絞り込みができましたね。
FILTER関数の第1引数「範囲」にA2:C8を指定し、
第二引数の絞り込み条件を”A2:A8”が1であった場合抽出されるとし、第三引数は条件にマッチしなかった場合は空””になるように指定しています。
また、B列の商品名で部分一致させ抽出することもできます。
サンプルは商品名に「いも」が含まれてる行のみ抽出する内容となっています。
=FILTER(元データ!A2:C4,IFERROR(FIND("いも",元データ!B2:B4),0))FILTER関数の範囲で指定する最終行を自動的に取得する
これまでFILTER関数の範囲は”A2C8”、”A2:A8”のように固定値でしたが最終行判定を行い、範囲を動的にするよう変更をしてみましょう。
元シートの表にデータを加えます。
数式は以下の通りです。F2へ実装をしてみましょう。
=FILTER(INDIRECT("元データ!A2:C"&COUNTA(元データ!A:A)),INDIRECT("元データ!A2:A"&COUNTA(元データ!A:A))=1,"")はい、最終行が自動で判別され、表のデータの追加分が反映されていますね。
さいごに
いかがでしょうか。
今回は、
・元データの最終行を取得し、動的にデータを取得する方法
についてまとめました。
また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。
この記事の関連キーワード
コメントを残す