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関数でも適用致します。
また、この表では「名前」と「作品名」 の２列だけですが、もっと列がある表の場合は、COLUMN(コラム)関数を指定すると便利です。
現在は、
=IFERROR
(VLOOKUP
(E2,anime_Table,2,FALSE),””)
となっておりますが、この２列目を指定している２にCOLUMN関数を使います。
F列の任意のセルに、
=COLUMN(F1)
と入力すると、このように「６」と言う結果になりました。
次に、D列に任意のセルに、
=COLUMN(D1)
は、「４」となりました。
この表は、E列から始まっていますよね。
それで、E列が1列目、F列が2列目にしたいので、その前のD列の「4」を引くと、
=COLUMN(F1)-COLUMN(D1)
で、「２」と求まりました。
この式を右に持っていくと、F列が２列目、G列が3列目、H列が4列目になるのですが・・・
このように、２、２、２・・・となってしまいました。
原因は、F列を行固定の複合参照にしていない、D列を行列固定の絶対参照にしていないからです。
そこで、
=COLUMN(F$1)-COLUMN($D$1)
と数式を変えました。
F1は[F4]キーを2回で、行固定の複合参照に、D1は[F4]キー１回の絶対参照にします。
それで、横に引っ張ると・・・
このように、２、３、４・・・となりました。
因みに、オートフィルオプションは、表以外の空白セルのところで、[Back Space] または、 [Space]で消えます。
これを、表の中で使うと、
=IFERROR
(VLOOKUP
(E2,anime_Table,
COLUMN(F$1)-COLUMN($D$1),
FALSE),””)
となりました。
・
・
・
Excelもスプレッドシートも、学ぶ範囲が広すぎたり、自分が頑張って勉強してもあまり実務に役に立たなかったりということに悩んでおりました。
その悩みを解決する本を見つけましたので、早速紹介させて頂きます。
最近の学びでは、本と動画が連動していて、本にQRコードが付いていて、YouTube動画で講師の方が分かりやすく説明して下さる、「本×動画」という新しい学び方に目から鱗でした。
動画登録者20万越えの教育系YouTuberでお馴染み、Yousefulの長内孝平氏、神川陽太氏のExcelとスプレッドシートの本では、「現場の教科書」というタイトルが付いてある通り、実務に直結した内容をまずは本で要点を掴んで、動画で詳しく解説して下さっています。
個人的にはお二人の動画最後の「See you next time! Bye Bye!!」が好きで勉強のモチベーションが上がる教材です。
講師の長内氏も神川氏も私よりもだいぶ若い方ですので、私にとってはまさに中高年の学び直しという感じですね。
若い方から教わることに抵抗のある方もいらっしゃるかも知れませんが、Yousefulの講師の方は親しみやすいので大丈夫です！
できるYouTuber式 Excel現場の教科書
ビジネス教育系ユーチューバー長内孝平氏
ベストセラー1位に輝いたExcelの本です。
長内氏はMicrosoft本社が公式で認めるテクノロジーの専門家であり、徹底的な分かりやすさで語りかけるように動画でExcelを教えて下さっています。
学習用のサンプルも付いています。
できるYouTuber式 Googleスプレッドシート 現場の教科書
神川陽太氏 ＆ 長内孝平氏
この本には、スプレッドシートだけでなく、Excelではどう解くのかも書かれているので、スプレッドシートとExcelの復習を同時に行いたい方には、かなりオススメです！
教材のサンプルのスプレッドシートは閲覧用ですが、コピーをして自分のスプレッドシートに貼り付けて演習を行うことが可能です。
お疲れ様でした。
ブレイクタイムPhotoは、
房総半島の外房、御宿の海です。
■ 関連記事 ■
自己紹介
取りあえず事務職で働きたいから手っ取り早くExcel教えてくれに戻る
急にプレゼン資料を作ることになったから手っ取り早くパワポ教えてくれ
Wordのポイントを手っ取り早く教えてくれ！！！
コロナ禍・アフターコロナのテレワーク対策（Googleが提供するツール）
メインメニューに戻る