Excelで関数を使うとファイルが重くなりますよね。
また、エラーが表示されると見映えも悪くなります。
そこで、実務でよく使う関数である、
・VLOOKUP関数
・IFERROR関数
・OFFSET関数
・COUNTA関数
・テーブル機能
の合わせ技を使ってみたいと思います。
では、国民的アニメを題材としたVLOOKUP関数の説明に入ります。
この様な簡易的な表を作成致します。
このシートの右側の表には、サザエさんに出て来る、
ワカメちゃんの名前があります。
作品名である「サザエさん」を黄色いセルに表示させます。
黄色いセルにVLOOKUP関数を入れます。
先に意味からお伝えします。
=VLOOKUP
(ワカメ,表の範囲,列,完全一致)
という意味です。
列の2は、表の範囲が名前をキーにして、名前が１列目、作品名が2列目なので、この場合は「2」になります。
完全一致には「FALSE」を入れます。
ここでは、完全一致にはFALSEを入れるんだなぁ〜〜〜って感じで、
あまり深く考えないで、取り敢えず先に進みましょう。
具体的なセルを入れた値はこの様になります。
=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行」であることが求まりました。
これにより、表の範囲を7行分に拡張すれば良いです。これで、VLOOKUP関数の第2引数である、表の範囲の所にOFFSET関数を使うことが出来ます。
OFFSET関数の中で、COUNTAで求めた行数を使います。
これ以降OFFSET関数が登場するので、先に下記の記事を読んでおくと、
チョット分かりやすくなるかもしれません。
OFFSETとMATCH関数でドラゴンボールを探せ！！
OFFSET関数と組み合わせます。
=IFERROR(VLOOKUP(D2,A2:B5,2,FALSE),”左のセルにキャラ名を入力して下さい。”)
の、表の範囲である、A2:B5を修正します。
=IFERROR(
VLOOKUP(
E2,
OFFSET(A2,0,0,COUNTA(A:A)-1,2)
,2,FALSE),””)
=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(基準のセル , 下に移動分 , 横に移動分 , 高さ , 幅)
です。
基準のセルから下に0、横に0移動します。
つまり、移動先はA2自身です。
高さがA2から7つ分の「COUNTA(A:A)-1」で、幅は2つ分が、
表の範囲になり、これを自動で可変できるようにしました。
ちょっとややこしいな・・・と思われた方は、
OFFSET関数については、先に下記の記事を読んでおくと、
チョット分かりやすくなるかもしれません。
OFFSETとMATCH関数でドラゴンボールを探せ！！
では、追加した部分が表示されることを確認します。
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が見づらい配色なので調整します。
テーブル内の範囲を選択して、「テーブルタブ」をクリックして、なるべく「淡色」から好きな色を選びます。
お疲れ様でした。
ここで一旦、写真で休憩を挟みます。
代々木公園で撮影した薔薇です。
■ Excel / スプレッドシートの学習にオススメ本の紹介 ■
「できるYouTuber式 Excel現場の教科書（長内孝平氏）」
単行本（ソフトカバー）
Kindle版
「できるYouTuber式 Googleスプレッドシート 現場の教科書」
（神川陽太氏 ＆ 長内孝平氏）
単行本（ソフトカバー）
Kindle版
「なんの才能もない発達障害者の声: 〜 届かない声を届けたい 〜 (障害者雇用向け)」
私、猫★シエスタの著作です。
殆どが「発達障害者として働くことの悩み」の内容ですが、Excelを含む障害者雇用の事務作業に便利なショートカットキーについても少しだけ掲載しております。
Kindle版
皆さま、大変お疲れ様でした。
この記事最後のブレイクタイムPhotoは・・・
房総半島の外房、御宿の海です。
仕事や勉強のリフレッシュに、趣味で写真を撮っておりますので、宜しかったら フォトストック写真ACさん の投稿もご覧頂けますと、大変嬉しい限りでございます！！
こちら、無料の「ダウンロードユーザー」に登録して頂けると、無料で写真のダウンロードが可能になります。
※ 先にGoogleアカウントを作成して頂くと、登録が ラク です♪
最後までご精読、誠にありがとうございました！！
フォトストック写真ACさんのプロフィールページ
最後までお付き合い頂き、誠にありがとうございました！！
■ 関連記事 ■
自己紹介
取りあえず事務職で働きたいから手っ取り早くExcel教えてくれに戻る
急にプレゼン資料を作ることになったから手っ取り早くパワポ教えてくれ
Wordのポイントを手っ取り早く教えてくれ！！！
コロナ禍・アフターコロナのテレワーク対策（Googleが提供するツール）
メインメニューに戻る
