この記事を読むことで、
・VLOOKUP関数
・IFERROR関数
・OFFSET関数
・COUNTA関数
・テーブル機能
の概要が掴めます。
Excelで関数を使うとファイルが重くなりますよね。
また、エラーが表示されると見映えも悪くなります。
そこで、実務でよく使う関数である上記の関数の合わせ技を使ってみたいと思います。
では、国民的アニメを題材としたVLOOKUP関数の説明に入ります。
この様な簡易的な表を作成致します。
このシートのセルD2には、サザエさんに出て来る「ワカメ」ちゃんの名前があります。
作品名である「サザエさん」を黄色いセルに表示させます。
黄色いセルにVLOOKUP関数を入れます。
先にこの関数の中身である引数の意味からお伝えします。
=VLOOKUP(ワカメ,表の範囲,列,完全一致)
という意味の指定をして行きます。
表の「キャラ」をキーにして、１列目が「キャラ」、2列目が「作品名」なので、この場合、表示させたい作品名は「2」になります。
完全一致には「FALSE（フォルス。数字の0ゼロでも可）」を入れます。
ここでは、完全一致にはFALSEを入れるんだなぁ〜〜〜って感じで、
あまり深く考えないで、取り敢えず先に進みましょう。
黄色いセルE2に入力する具体的な式は
=VLOOKUP(D2,A2:B5,2,FALSE)
と式を入れます。
では、別のキャラクターの名前を入れるので、一旦ワカメちゃんワカメちゃんを消します。
あ、エラーが出ちゃった（汗）
そんな時は、エラーの時には何も表示させないように、IFERROR関数を使って式を修正します。
式の意味は、
=IFERROR(OKな値 , エラーメッセージなど)
で、OKの値のところに、先ほどのVLOOKUP関数が入ります。
具体的な式には、
=IFERROR(VLOOKUP(D2,A2:B5,2,FALSE),”左のセルにキャラ名を入力して下さい。”)
これでエラーが消えました。
エラーメッセージに所を「”（ダブルクォーテーション）」で囲います。
では、キャラクターの所に、「丸尾くん」と入れます。
無事に作品名である「ちびまる子ちゃん」と表示されました。
これで、めでたしめでたし・・・。
なのですが、この表に追加をする場合もありますよね。
灰色の所が追加分です。
こういう時は、表の範囲を1つ1ついじったり、または、面倒だから100行ぐらい！！
=VLOOKUP(D2,A2:B100,2,FALSE)
なんてする方法もありますが、
そうなると、関数でExcelファイルが重くなるんです。
そこで、加わった行数だけ自動で範囲を動かせるようにします。
まず、その為には、この表は何行分のデータがあるのかを数えるCOUNTA関数を使います。
D列のオレンジのセルに式を入れます。
オレンジのセルの所に、
=COUNTA(A:A)
と式を入れました。
これで、A列の件数が数えられます。
表のタイトルの分があるので、1を引いておきます。
=COUNTA(A:A)-1
これにより、キャラクターの「のび太」〜「みぎわさん」まで「7行」であることが求まりました。
更に、このCOUNTA関数を使って、表の範囲を自動的に拡張致します。
ここで、VLOOKUP関数の第2引数である、表の範囲の所にOFFSET関数を使うことが出来ます。
OFFSET関数の中で、COUNTAで求めた行数を使います。
これ以降OFFSET関数が登場するので、先に下記の記事を読んでおくと、
チョット分かりやすくなるかもしれません。
OFFSETとMATCH関数でドラゴンボールを探せ！！
OFFSET関数と組み合わせます。
現在、この様な式が入っていると思います。
=IFERROR(VLOOKUP(D2,A2:B5,2,FALSE),”左のセルにキャラ名を入力して下さい。”)
この部分の表の範囲であるA2:B5の箇所をOFFSET関数に修正します。
=IFERROR(
VLOOKUP(
D2,
OFFSET(A2,0,0,COUNTA(A:A)-1,2),2,FALSE),”左のセルにキャラ名を入力して下さい。”)
となります。
この例でのOFFSET関数の中身、
OFFSET(A2 , 0 , 0 , COUNTA(A:A)-1 , 2)
は、
OFFSET(基準のセル , 下に移動分 , 横に移動分 , 高さ , 幅)
です。
ちょっとややこしいな・・・と思われた方は、OFFSET関数については、先に下記の記事を読んでおくと、少しだけ分かりやすくなるかもしれません。
OFFSETとMATCH関数でドラゴンボールを探せ！！
基準のセルから下に0、横に0移動します。つまり、移動先はA2自身です。
高さがA2から7つ分の「COUNTA(A:A)-1」で、幅は2つ分が表の範囲になり、これを自動で可変できるようにしました。
では、追加した部分が表示されることを確認します。
E2の名前に「キルア」と入力してみます。
キルアの作品名がハンターハンターと表示されました。
では、更に表を追加して、追加した「いくらちゃん」を検索値にしてみます。
作品名が「サザエさん」と表示され、COUNTAも1行増えて8行になりました。
これで、関数を使った表の範囲の可変が出来ました。
ここまでご覧の読者様の中には、
「OFFSET関数やCOUNTA関数が少々面倒ですので、もっと手軽な方法は無いの？」
と思われた方もいらっしゃるかも知れません。
もっと手っ取り早い方法は、表をテーブルに変換して、テーブル名をVLOOKUPの引数に設定してしまうという方法もございます。
まず、上の表内のどこでも良いのでお好きなセルを選択して、[Ctrl] + [T]を押します。
先頭行を見出しにして、OKを押します。
テーブル内を選択して、「テーブルタブ」を選択します。
この状態で、左側のテーブル1の所に、このテーブルの名前を付けます。
このテーブルに付ける名前は「anime_Table」にしました。
それでセルE2のVLOOKUP関数の第2引数の式を
=IFERROR
(VLOOKUP
(D2,anime_Table,2,FALSE)
,”左のセルにキャラ名を入力して下さい。”)
と修正します。
この状態で、テーブルにした範囲に新しいアニメキャラを追加します。
SPY×FAMILYの「アーニャ」にしました。
テーブルでは隣接した一番下に入力すると、自動で範囲にされるので、
無事にアーニャの作品名に「SPY×FAMILY」が表示されました。
また、COUNTAの所も9行になりました。
ここで、このanime_Tableの色使いが見づらい配色なので調整します。
テーブル内の範囲を選択して、「テーブルタブ」をクリックして、なるべく「淡色」から好きな色を選びます。
お疲れ様でした。ブレイクタイムPhotoは、
目白庭園の紅葉です。
ご精読誠にありがとうございました。
■ Excelの学習にオススメ本の紹介 ■
「できるYouTuber式 Excel現場の教科書（長内孝平氏）」
単行本（ソフトカバー）
Kindle版
「なんの才能もない発達障害者の声: 〜 届かない声を届けたい 〜 (障害者雇用向け)」
私、猫★シエスタの著作です。殆どが「発達障害者として働くことや障害とSNSについて」ですが、障害者雇用の事務作業で特に使用頻度の高いキーに絞ったExcelなどのショートカットキーの紹介と、演習問題も掲載しております。
Kindle版
■ 関連記事 ■
