大幅な時間短縮に！　コピペでまるっとデータ抽出「COLUMNS関数」の使い方

データをまるっと抽出する方法

おさ氏：どうも、みなさん、こんにちは。「おさとエクセル」のおさです。 今日は、VLOOKUP関数の第8弾をやってまいります。

今日のテーマは、VLOOKUP関数でデータ1件まるごと引っ張って来ようよということです。これどういうことかと言うと、これまでは、検索値に指定したキーワードから、たとえば仕入れ値というキーワード1件だけ拾ってきてね、みたいなことをやってきました。

実際のシート上では、たとえば、みなさんが名簿を渡されたとき、複数列に渡っていろんな情報が書かれていますよね。電話番号とか自分の名前とか備考欄とか、たくさんあると思います。

これをIDに紐付けて、すべてこっちへ引っ張ってくるときに、1個1個、列番号2、3、4、5みたいな感じで手入力してると、ものすごく時間がかかってめんどくさいですね。そのときに便利な関数、「COLUMNS関数」「LOWS関数」という2つの関数がございますので、今日はそれを勉強したいと思います。

さっそくやっていきましょう。よろしくお願いします！

では、ワークシートで見ていきましょう

では、こちらのワークシートをご覧ください。

今日は、前回の動画で学んだ「テーブル機能」をすでに盛り込んだ表を上の方に用意しました。具体的には、一番左側にIDが書かれていて、IDごとに個人情報が右側にずらっと書かれています。これを下のほうに引っ張ってくるということを、今日はやりたいと思います。

単純に、今まで勉強してきた方法でできるかどうかを最初に見てみましょう。

=（イコール）で始めて、VLOOKUP、第一引数はこのIDで、選択範囲はこちらですよと、テーブルですね。列番号としては、2列目の「おさない」がほしいので、2。完全一致の0（ゼロ）。それで、エンターを押してあげると「おさない」の値が返ってきました。

これを右側にバーーっとコピペをやっていくと、単純に、エラーが返ってきてしまいましたね。

エラーの原因は？

まず、なぜか見ていきましょう。第一引数の検索値が、相対参照になってしまっているので、右側を見てみるとわかりますが、C11を参照してしまっています。そもそもこの時点で間違えてますよね。

まずはここを絶対参照にしてあげます。

これでエンターを押して、今度こそうまくいくかなと思って、右側にコピペしてみます。そうすると、「おさない」と。おさないだらけですね。

なんで、おかしいぞと思いますよね。今度、おかしいところは、第3引数の2という値。これを固定値にしてしまっていると、右側を見てもすべて2なんです。今、どんどん見ているんですけど、2になってますよね。

これが意味することがなにかと言うと、今、検索値としてB11「150-0001」を、テーブル2上の表から探してきてね、ということは指定できています。150-0001が見つかりました。そのときに返す値として、「2列目の値を返してね」ということが、すべてに組み込まれてしまっているというわけなんですね。2列目というのは、具体的には、名前の苗字ですね。これを返してくることになってしまっています。

ということは、第3引数をうまいこと変数値に変えてあげないと、簡単にコピペができないということになってしまいます。コピペができないとどうなるかと言うと、1個1個選択して、（第3引数を変えて）「こうへい」と。

次もここを4に変えて、電話番号オッケーみたいな感じで。

こういう感じでめんどくさいんですよね。1個1個やっていくと。こんなことやってたら日が暮れてしまいますので、関数をひとつだけ設定して、あとはコピペでなんとかなんないかなということを、頭使ってやってみます。

COLUMNS関数の使い方

このときに便利な関数が、COLUMNS関数ということになります。これは、選択範囲に含まれる列の数を数えてくれる関数です。

具体的に説明しましょう。COLUMNSと打ってあげて、たとえば、今1件だけデータを選択してあげると、返ってくる値は1です。

これがどういうことかと言うと、B11というところをセル範囲に選択しましたが、「ここには1列分のデータが含まれていますよ」ということを教えてくれています。

次に、ここからここまで（B11:D11）の3列分を指定してあげると、もちろん3という値が返ってきますね。3列分ありますよということを教えてくれています。

我々が今、したいことはなにかと言うと、「おさない」というところのデータを引っ張ってくるためには、2という列番号がほしいんですね。そのためには、ここには2という値を返してほしいし、ここには3という値を返してほしい。どんどん右側になるにつれて、1の等差数列で返してほしいというのが、正直なところだと思います。

実際にどう指定するのか

どうすればいいのか。答え、言いましょう。絶対参照と相対参照の違いをしっかり理解している方だったら、これを理解できると思います。

まず左側は、絶対列参照にしてあげましょう。右側は、列参照にはしなくていいんですが、そもそも相対参照だけで設定しておいてください。

こういう感じですね。「COLUMNS（$B11:B11）」と、これでエンターを押すと、ここは今1列分しか選択できていないので、データ1件ですよと返してくれています。

これで右側にコピペしてみましょう。そうすると、いい感じで返ってきました。2、3、4、5、6、7、8、9、10と。

これね、1件1件見ていくと、2件分選択してくれてますね。B11、C11と。相対参照ですので、コピペ1つ右側にすると、相対参照のここだけ、列が動いてくれているというわけです。いい感じですね。

では、このCOLUMNS関数の使い方をVLOOKUP関数のなかに当てはめてあげましょう。この第3引数のところですね。

普通にCOLUMNSと打って……スペルだけ間違えないようにしてくださいね。で、ここを設定して、絶対列参照にしてあげます。3回押してあげるとこうなりますね。コロンで、今2列目のところですので、C11と入力してあげました。これでエンターを押してあげると、「おさない」という値が返ってきましたね。

この3つ目の引数のところを中身みて見ると、しっかり2と返ってきてくれると思います。はい、返ってますね。こういうことです。

これをしっかり右側にコピペしてあげると、全ていい感じにデータが反映されているわけですね。はい、返ってきました。

これを自由に動かしてやっていくと（IDを変えると）、どんどんデータが変わるというわけです。はい、変わってますね。

これが、COLUMNS関数とVLOOKUP関数を組み合わせてできる素敵な仕組みです。覚えておきましょう。

データを縦に使いたいときは

今、上のほうのデータをまったく同じフォーマットで、横に引っ張ってくることができるようになったんですが、これを縦に引っ張ってくることも可能です。

今、COLUMNS関数を使ってきましたね。一回、このCOLUMNS関数をたたんで、今度はROWS関数というものを見ていきます。

こちらの関数の中身を見てみてください。

今、第1引数には検索値「$C$14」というのが、絶対参照で組み込まれています。第2引数は、テーブル2、ここ全体を範囲選択してくれています。

で、3つ目ですね。ROWS関数で値が示されていますが、これの中身を見てみると、2という値が返されていますね。

B14と14を指定しています。先ほどと違うのがなにかと言うと、$マークの位置が違いますよね。かつ、右側のコロンのあとのこの値もさっきは最初からCだったと思うんですけど、今度は、Bを指定して＋1で調整しているというのが、先ほどと違うところです。

ROWS関数の場合は、列を固定したいのではなくて、行を固定しておかないといけないので、14という数字の前に$マークを組み込んでいます。こういう感じで、縦にも引っ張ってくることができるようになるので、みなさんもしっかり手を動かしてやってみてください。

【gazou33 0:09:05】

関数の中身はこんな感じです。ここ止めてね、ぜひぜひ復習してみてください。

今日は、ROWS関数とCOLUMNS関数という新しい関数が出てきましたが、この2つ、しっかり対比しながら、どっちがどっちするんだっけというのをしっかり覚えて、VLOOKUP関数に当てはめていくと、すごく便利になると思います。今日は以上ですね。どうも、ありがとうございました。

時間短縮のためにぜひ覚えてください

みなさん、お疲れ様でした。今日は、VLOOKUP関数の第三引数をもっと便利に使い倒そうぜというのを大枠のテーマでやってきました。具体的には、COLUMNS関数とROWS関数ですね、この二つを組み合わせることで、第三引数をもっと便利に使い倒せるようになりますよというお話でした。

ここまで来ると、こんなことまでする必要あるのとか思う方もいらっしゃると思うんですけど、メンテナンスの時間を削減したい人とか、あるいは、もっと簡単にコピペで済ませたい方とかね。そういう時間を短縮したいと思う方は、今回のようなところもしっかり押さえておくといいと思います。

ですので、たぶんあと1回か2回、VLOOKUP関数シリーズが続きますが、しっかりマスターして「VLOOKUP関数だったら俺に任せろ」と社内で言えるように、みなさんでマスターしていきましょう。

今日は、以上ですね。またお会いしましょう。See you next time. Bye Bye!