見出し画像

【Excel】VLOOKUP関数ハック！どこまで工夫できるかやってみよう🐟✨

出戻りガツオ🐟

こんにちは！出戻りガツオです！
連日投稿！ネタどうしよう！！！

とりあえずこんな時は好きなExcelネタで「VLOOKUP関数」を他の関数や機能と組み合わせて、どれだけ工夫できるか見てみよ～ってやってみます！

  • XLOOKUP関数しか勝たん

  • マクロでやればいい

  • Power Queryでやれば？

といったお声は、この記事では聞いてないので、
よろしゅうおねがいいたしやす( ｀・∀・´)ﾉﾖﾛｼｸ

我が家のExcelが古かったので・・・Onlineでなるべく機能充実させてやるので、画面がちょこちょこ変わったりしますがご了承ください🐟

お題 - VLOOKUP関数だらけの比較データを作ろう！

バックオフィス - 給与担当に焦点を当てて。
よくある業務。成果物の比較ですね。

👆のサービスで作った疑似給与データで比較作業をします。
全部「VLOOKUP関数」で検証！

シート「10月」
シート「9月」

この二つのシートの"どのセルがアンマッチ"か、ひたすら「VLOOKUP関数」でチェックしていきます。※こういう業務、めっちゃあります(-_-;)

手法はいくらでもありますが・・・

こんな感じの比較表を「速く」「ラクして」

比較表作る方法をいくつか書いて、メリデメ挙げていきましょう！
 - 数式を入れるのはシート「10月」とします。

検証タイム！！

オーソドックス

オーソドックスなやり方

[ '9月'!A:J ]が検索範囲ですね。A2は社員番号。
絶対参照のためにFALSE - これは後のケースでも一緒になります。

関数ウィザードを使うと毎回指定が大変です。

$ - 絶対参照を使ってラクできるようにしましょうか！

$を付けることでコピー＆ペーストしたときに、列番号がズレない
赤枠・橙色のセルはコピー&ペーストで大体の情報が埋まる

上記の場合だと・・・

列番号の編集だけで済む

列番号だけ修正すればコピー&ペーストで完了します。
しかし、列番号の修正ももはや手間・・・

関数は下まで埋める必要がある

当然ですが下まで関数を埋める必要があります。これも手間ですねえ🧐

列番号を取得する計算式も追加してみましょうか！

この関数はシート「9月」における[ 年齢 ]列の計算式です！

MATCH関数を組み合わせれば、列番号も計算でとれそうですね！

形を崩して紹介するとこんな形☆

説明用に形を崩しましたが、本来は平文です。
これを使うことで列番号も自動計算。

気を付けてほしいのはMATCH関数の[ 検索値 C1 ]の指定ですね。
関数をオートフィルするときに、行番号が変わってはいけません。
しかしコピー＆ペースト用に列番号である[ C ]は相対的に動いてほしい、
これを達成するには、行番号だけ絶対参照にすることで、相対的に動くところと動かないところを設定できます！

式が難しくなりますが一歩前進ですね！

赤枠・橙色のセルはコピー&ペーストで全ての情報が埋まる
だ・け・ど　関数は下まで埋める必要がある

関数を下まで埋める作業も最早面倒・・・
さて、どうしようか・・・

スピル を使ってみる！

ここでデータをOneDriveに挙げて、Excel関数の風呂敷を広げてみます！
使うのは「SPILL(スピル)」

Enter キーを押して数式を確定すると、Excel は出力範囲のサイズを動的に調整し、その範囲内の各セルに結果を配置します。

動的配列数式とスピル配列の動作

この機能を使うことによって、Excel関数の出力先を「ひとつのセル」ではなく、「複数のセル」に対して結果を返すことが出来ます！

この入力で
複数行の計算結果を返せる
ポイントは検索値が配列になっていること！

これを使うことによってコピペだけで最早できちゃう！凄い！！

来月以降もコピペ一発で終わらせたいな・・・
そんなときはINDIRECT関数の出番です！

セル参照を文字列と関数を組み合わせて設定することができます！

ちょっと難しそうですが最初だけです！

複数の関数の合わせ技でドンドンラクができる！
面白いですね！Twitterを見ていると猛者が、こ、これは一体何なんだ・・・っていう関数芸を披露しているので面白いですよｗ

是非見てみてください！

手段は多いことに越したことはない

今回紹介した手法はモダンExcelの一環になります。
表計算ソフトとして多く使われている「Excel」ですが、バージョンアップもどんどんされ、知らないうちに自分のお仕事を魔法のように終わらせるテクニックも増えてきています！

今回は関数バージョンで紹介しましたが、「Power Query」や「Excel VBA」をはじめ、問題解決の手段は沢山あります。

しかしながら、今対面している業務に対して、
「何が持続的に使えて効率が良いだろうなっていう観点」があると、寿命が長い業務効率化が達成できると思っています！

私も勉強中のみですが、興味のある方は是非調べてみてくださいね！🐟✨

最近の記事の紹介

最後に！！！

いつもお読みいただきありがとうございます！
ITを使って仕事を楽しくする一助になりたいと思ってますので
お読みいただいた方はぜひTwitterもフォローしてください！

Power PlatformやPythonやExcel、Google Apps Scriptなどなど雑多につぶやきます。よなよなエールが大好きです🍺

リプ、いいね👍、RT大歓迎です！
強く求めてます🐟😂🐟
業務改善フレンズ大歓迎！！切磋琢磨しましょ～♪♪

それではまた今度！ばいば〜い！

この記事が気に入ったらサポートをしてみませんか？