Excelで関数を使うとファイルが重くなりますよね。
また、エラーが表示されると見映えも悪くなります。
そこで、実務でよく使う関数である、
・VLOOKUP関数
・IFERROR関数
・OFFSET関数
・COUNTA関数
・COLUMN関数
の合わせ技を使ってみたいと思います。
OFFSET関数については、先に下記の記事を読んでおくと、
チョット分かりやすくなるかもしれません。
OFFSETとMATCH関数でドラゴンボールを探せ！！
では、国民的アニメを題材としたVLOOKUP関数の説明に入ります。
このシートの右側の表には、サザエさんに出て来る、
ワカメちゃんの名前があります。
作品名である「サザエさん」を黄色いセルに表示させます。
黄色いセルにVLOOKUP関数を入れます。
先に意味からお伝えします。
=VLOOKUP
(ワカメ,表の範囲,列,完全一致)
という意味です。
列の2は、表の範囲が名前をキーにして、名前が１列目、作品名が2列目なので、この場合は「2」になります。
完全一致には「FALSE」を入れます。
ここでは、完全一致にはFALSEを入れるんだなぁ〜〜〜って感じで、
あまり深く考えないで、取り敢えず先に進みましょう。
具体的なセルを入れた値はこの様になります。
=VLOOKUP(E2,A2:B5,2,FALSE)
と式を入れます。
お疲れ様でした。
ここで一旦、写真で休憩を挟みます。
立川市の国営昭和記念公園の向日葵です。
では、別のキャラクターの名前を入れるので、一旦ワカメちゃんワカメちゃんを消します。
あ、エラーが出ちゃった（汗）
そんな時は、エラーの時には何も表示させないように、IFERROR関数を使って式を修正します。
式の意味は、
=IFERROR(OKな値 , エラーの値)
具体的には、
=IFERROR
(VLOOKUP
(E2,A2:B5,2,FALSE),””)
で、OKの値のところに、先ほどのVLOOKUP関数が入ります。
これでエラーが消えました。
では、キャラクターの所に、
「丸尾くん」と入れます。
ちゃんと作品名である「ちびまる子ちゃん」と
表示されました。
お疲れ様でした。
ここで一旦、写真で休憩を挟みます。
目白庭園の紅葉の写真です。
これで、めでたしめでたし・・・。
なのですが、この表に追加をする場合もありますよね。
灰色の所が追加分です。
こういう時は、表の範囲を1つ1ついじったり、
または、面倒だから100行ぐらい！！
=VLOOKUP(E2,A2:B100,2,FALSE)
なんてする方法もありますが、
そうなると、関数でExcelファイルが重くなるんです。
なので、加わった行数だけ自動で範囲を動かせるようにします。
まず、その為には、この表は何行分のデータがあるのかを数えるCOUNTA関数を使います。
上の表の右側のH列のオレンジのセルに式を入れます。
見ずらいので、ちょっと拡大しますね。
オレンジのセルの所に、
=COUNTA(A:A)
と式を入れました。
これで、A列の件数が数えられます。
表のタイトルの分があるので、
1を引いておきます。
=COUNTA(A:A)-1
これにより、表の範囲を7行分に拡張すれば良いです。これで、VLOOKUP関数の表の範囲の所にOFFSET関数を使うことが出来ます。
OFFSET関数の中で、COUNTAで求めた行数を使います。
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とMATCH関数でドラゴンボールを探せ！！
この例でのOFFSET関数の中身、
OFFSET(A2 , 0 , 0 , COUNTA(A:A)-1 , 2)は、
OFFSET(基準のセル , 下に移動分 , 横に移動分 , 高さ , 幅)
です。
基準のセルから下に0、横に0移動します。
つまり、移動先はA2自身です。
高さがA2から7つ分の「COUNTA(A:A)-1」で、幅は2つ分が、
表の範囲になり、これを自動で可変できるようにしました。
では、追加した部分が表示されることを確認します。
E2の名前に「キルア」と入力してみます。
キルアの作品名がハンターハンターと表示されました。
では、更に表を追加して見ます。
これで、表の範囲の可変が出来ました。
お疲れ様でした。
ここで一旦、写真で休憩を挟みます。
写真は、多摩モノレールです。
多摩モノレールを立川南駅から柴崎体育館駅まで乗って、日野方面に南下して、立日橋から撮影しました。
ここまでご覧の読者様の中には、
「OFFSETとかCOUNTAとか、めんどくせぇ、もっと手軽な方法は無いの？」
と思われた方もいらっしゃるかも知れません。
もっと手っ取り早い方法は、表をテーブルに変換して、テーブル名をVLOOKUPの引数に設定してしまうという方法もございます。
まず、表内のどこでも良いので、[Ctrl] + [T]を押します。
先頭行を見出しにして、OKを押します。
テーブルに名前を付けられるので、今回は「anime_Table」としてみました。
それで式を
=IFERROR
(VLOOKUP
(E2,anime_Table,2,FALSE),””)
と修正します。
その状態で、テーブルにした範囲に新しいアニメキャラを追加します。
SPY×FAMILYのアーニャにしました。
テーブルでは隣接した一番下に入力すると、自動で範囲にされるので、
無事にアーニャの作品名に「SPY×FAMILY」が表示されました。
お疲れ様でした。
ここで一旦、写真で休憩を挟みます。
同じく、多摩モノレールです。
次に、この表では「名前」と「作品名」 の２列だけですが、もっと列がある表の場合は、COLUMN(コラム)関数を指定すると便利です。
現在は、
=IFERROR
(VLOOKUP
(E2,anime_Table,2,FALSE),””)
となっておりますが、この２列目を指定している２にCOLUMN関数を使います。
実際にVLOOKUP関数の中に組み込む前に、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は・・・
房総半島の外房、御宿の海です。
仕事や勉強のリフレッシュに、趣味で写真を撮っておりますので、宜しかったら フォトストック写真ACさん の投稿もご覧頂けますと、大変嬉しい限りでございます！！
こちら、無料の「ダウンロードユーザー」に登録して頂けると、無料で写真のダウンロードが可能になります。
※ 先にGoogleアカウントを作成して頂くと、登録が ラク です♪
最後までご精読、誠にありがとうございました！！
最後までお付き合い頂き、誠にありがとうございました！！
■ 関連記事 ■
自己紹介
取りあえず事務職で働きたいから手っ取り早くExcel教えてくれに戻る
急にプレゼン資料を作ることになったから手っ取り早くパワポ教えてくれ
Wordのポイントを手っ取り早く教えてくれ！！！
コロナ禍・アフターコロナのテレワーク対策（Googleが提供するツール）
メインメニューに戻る