Excelで関数を使うとファイルが重くなりますよね。
また、エラーが表示されると見映えも悪くなります。
そこで、実務でよく使う関数である、
・VLOOKUP
・IFERROR
・OFFSET
・COUNTA
・COLUMN
の合わせ技を使ってみたいと思います。
OFFSET関数については、先に下記の記事を読んでおくと、
チョット分かりやすくなるかもしれません。
OFFSETとMATCH関数でドラゴンボールを探せ!!
では、国民的アニメを題材とした関数の説明に入ります。

このシートには、サザエさんに出て来る、
ワカメちゃんの名前があります。
作品名である「サザエさん」を黄色いセルに表示させます。
黄色いセルに
=VLOOKUP(E2,A2:B5,2,FALSE)
と式を入れます。

式の意味は、
=VLOOKUP(検索値 , 表の範囲 , 2列目 , 一致)
です。
もっと詳しく書くと、
=VLOOKUP(ワカメなどのキャラ名 , 表の範囲 , 2列目の作品名 , 完全に一致)
では、別のキャラクターを入れて見ます。
ワカメちゃんの所を丸尾くんにしたいので、
一旦ワカメちゃんを消します。

エラーが出ちゃった。
そんな時は、
=IFERROR(VLOOKUP(E2,A2:B5,2,FALSE),””)
と、式を修正します。
式の意味は、
=IFERROR(OKな値 , エラーの値)
です。

では、キャラクターの所に、
「丸尾くん」と入れます。

ちゃんと作品名である「ちびまる子ちゃん」と
表示されました。
これで、めでたしめでたし・・・。
なのですが、この表に追加をする場合もありますよね。
灰色の所が追加分です。

こういう時は、
=VLOOKUP(E2,A2:B5,2,FALSE)の表の範囲を1つ1ついじったり、
または、面倒だから100行ぐらい!!
=VLOOKUP(E2,A2:B100,2,FALSE)
なんてする方法もありますが、
そうなると、関数でExcelファイルが重くなるんです。
なので、加わった行数だけ自動で範囲を動かせるようにします。
まず、その為には、何行使うのかを数えるCOUNTA関数を使います。

オレンジのセルに
=COUNTA(A:A)
と式を入れました。
これで、A列の件数が数えられます。
表のタイトルの分があるので、
1を引いておきます。
=COUNTA(A:A)-1

これにより、表の範囲を7行分に拡張すれば良いです。
OFFSET関数と組み合わせます。
=IFERROR(VLOOKUP(E2,A2:B5,2,FALSE),””)
の、表の範囲である、A2:B5を修正します。
=IFERROR(VLOOKUP(E2,OFFSET(A2,0,0,COUNTA(A:A)-1,2),2,FALSE),””)
となります。
OFFSET関数の中身、
OFFSET(A2 , 0 , 0 , COUNTA(A:A)-1 , 2)は、
OFFSET(基準のセル , 下に移動分 , 横に移動分 , 高さ , 幅)
です。
基準のセルから下に0、横に0移動します。
つまり、移動先はA2自身です。
高さがA2から7つ分の「COUNTA(A:A)-1」で、幅は2つ分が、
表の範囲になり、これを自動で可変できるようにしました。
ちょっとこの説明分かりづらいと思われた方は、
OFFSETとMATCH関数でドラゴンボールを探せ!!
をご覧下さい。
では、追加した部分が表示されることを確認します。
E2の名前に「キルア」と入力してみます。

キルアの作品名がハンターハンターと表示されました。
では、更に表を追加して見ます。

これで、表の範囲の可変が出来ました。
お疲れ様でした。
ここまでご覧の読者様の中には、
「OFFSETとかCOUNTAとか、めんどくせぇ、もっと手軽な方法は無いの?」
と思われた方もいらっしゃるかも知れません。
もっと手っ取り早い方法は、表をテーブルに変換して、テーブル名をVLOOKUPの引数に設定してしまうという方法もございます。
まず、表内のどこでも良いので、[Ctrl] + [T]を押します。

先頭行を見出しにして、OKを押します。

テーブルに名前を付けられるので、今回は「anime_Table」としてみました。

それで式を
=IFERROR
(VLOOKUP
(E2,anime_Table,2,FALSE),””)
と修正します。
その状態で、テーブルにした範囲に新しいアニメキャラを追加します。
SPY×FAMILYのアーニャにしました。

テーブルでは隣接した一番下に入力すると、自動で範囲にされるので、

このように、VLOOKUP関数でも適用致します。
また、この表では「名前」と「作品名」 の2列だけですが、もっと列がある表の場合は、COLUMN(コラム)関数を指定すると便利です。
現在は、
=IFERROR
(VLOOKUP
(E2,anime_Table,2,FALSE),””)
となっておりますが、この2列目を指定している2にCOLUMN関数を使います。
F列の任意のセルに、
=COLUMN(F1)
と入力すると、このように「6」と言う結果になりました。

次に、D列に任意のセルに、
=COLUMN(D1)
は、「4」となりました。

この表は、E列から始まっていますよね。
それで、E列が1列目、F列が2列目にしたいので、その前のD列の「4」を引くと、
=COLUMN(F1)-COLUMN(D1)
で、「2」と求まりました。

この式を右に持っていくと、F列が2列目、G列が3列目、H列が4列目になるのですが・・・

このように、2、2、2・・・となってしまいました。
原因は、F列を行固定の複合参照にしていない、D列を行列固定の絶対参照にしていないからです。
そこで、
=COLUMN(F$1)-COLUMN($D$1)
と数式を変えました。
F1は[F4]キーを2回で、行固定の複合参照に、D1は[F4]キー1回の絶対参照にします。
それで、横に引っ張ると・・・
このように、2、3、4・・・となりました。

因みに、オートフィルオプションは、表以外の空白セルのところで、[Back Space] または、 [Space]で消えます。
これを、表の中で使うと、
=IFERROR
(VLOOKUP
(E2,anime_Table,
COLUMN(F$1)-COLUMN($D$1),
FALSE),””)
となりました。
お疲れ様でした。
ブレイクタイムPhotoは、

房総半島の外房、御宿の海です。
メインメニューへ
取りあえず事務職で働きたいから手っ取り早くExcel教えてくれへ
プログラミングと資格
これからプログラミングをはじめる方へ
息抜きに、写真で癒し(=^・^=)
自己紹介