知らないと損するExcel Tips
1.はじめに
こんにちは。株式会社SHIFT 技術統括部の力石です。
主にお客さまのアプリケーション開発プロジェクトで、性能改善、品質向上などの非機能関連のコンサルティングをしています。
多くの方はExcelを使って仕事をしていると思います。
Excelには多くの機能が多くあり、他の人の操作を見ていると新たな発見をすることも多いと思います。
私がWeb会議でExcel画面を共有して操作しているときに操作方法を聞かれた機能や、便利だが認知度が低いと思われる機能を紹介します。
2. 関数の参照先の表示
Excelで以下の表があったとします。
計算式は以下のように設定していたとします。
関数が埋め込まれているカラムがどこを参照しているか調べる方法はいくつかありますが、代表的なものは以下の2つではないでしょうか。
方法1：定義した関数の内容を表示して、参照先のカラムを特定します。
上記の図でE2のカラムだとC2とD2を参照しています。
方法2：関数が書かれているカラムをダブルクリックして、参照先カラムの色が変わるのでそれで判断します。
例
方法1,2,は多くの人が知っていると思います。
参照しているカラムが離れていたり大量にある場合は、もっとお薦めの方法があります。
方法3：「参照先のトレース」と「参照元のトレース」を使用する方法です。数クリックで以下のように参照先が矢印で表示されます。
例：
この方法だと視覚的にわかりますので、参照先が大量にある場合間違いをすぐに発見できます。また、離れていてスクロールしないと表示されないカラムでも矢印は表示されるので参照先を追いかけるのが簡単になります。
操作方法は以下となります。
参照元または参照先となるセルを1つ選択して、メニューの[数式]の[参照元のトレース]ボタンまたは[参照先のトレース]ボタンをクリックします。それだけで矢印が表示されます。
参照元の参照元というように何段階も参照している場合は、[参照元のトレース]ボタンをクリックするごとに次の参照先が表示されます。
別のシートを参照している場合は、以下のように破線の矢印が表示されます。矢印をダブルクリックしますと、[ジャンプ]画面が表示されますので[移動先:]に表示されている行を選択し、[参照先(R):]を指定し、[OK]ボタンをクリックすると参照先のセルが表示されます。
[参照元のトレース]または[参照先のトレース]機能を複数のセルに対しておこないたい場合は、複数セルを選択して[参照先のトレース]ボタンを教えても最初のセルしか矢印が表示されません。
この場合は、複数のセルを選択したあとに、「[参照先のトレース]ボタンをクリックし[Enter]キーを入力」を繰り返すことで、複数のセルに対して矢印を表示することができます。
3. セルに書いた関数の表示
セルに書いた関数の内容を見たいときどのようにしていますか?
セルをクリックして「数式バー」に表示したり、セルをダブルクリックして表示したりすることが多いのではないでしょうか?
上記の方法だと複数のセルに書いた内容を一度に見ることができません。
複数セルに書いた内容を一度に表示する場合、メニューバーの[数式]→ワークシート分析の[数式の表示]ボタンをクリックします。
そうするとシート全体に対して、書いた関数が表示されます。
このときに列の幅が自動で広がりますが、関数全体が表示されるほど広がらないことがありますので、必要であれば列の幅を広げてください。
4. VLOOKUP関数の高速化
数万セルにVLOOKUP関数使用し、範囲パラメータのセル数が大量な場合に、計算が終わるまでの時間が長くて困ったことはないでしょうか?
いくつかの前提条件はありますが、VLOOKUP関数の書き方を工夫することで劇的に性能改善することが可能です。
VLOOKUP関数を次のように書くことが多いと思います。
例：VLOOKUP関数を実行しているシート
VLOOKUP関数が参照しているシート
性能改善でポイントとなるのが、VLOOKUP関数の4番目のパラメータです。このパラメータは検索方法を指定し、TRUEまたはFALSEのどちらかを指定できます。 値の意味はExcelのヘルプによると以下のように書かれています。
検索方法には検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、論理値(近似値を含めて検索＝TRUE または省略、完全一致の値を検索＝FALSE)で指定します。
ヘルプの内容だと意味がよく分からないですよね?TRUEのときの動きを簡単に言いますと次のようになります。
検索値で指定された値(1つ目のパラメータ)と同じ値が、範囲(2つ目のパラメータ)にあればその値を返します。
ない場合は、範囲の上から順に一致している値を探していき、範囲の値の方が大きくなれば1つ前の値を返します。
範囲で指定した1つ目の値の方が大きい場合は「#N/A」を返します。
もっと分かりやすく言いますと次のようになります。
一致した場合はその値をかえします。一致していない場合は、検索値より小さい値で一番近い値を返します。
注意点として、TRUEの場合はFALSE時と違い、範囲(2つ目のパラメータ)で指定している箇所はソートしている必要があります。
4番目のパラメータにTRUEを設定したときの例は次のようになります。
TRUEの場合は、FALSEに比べて性能が大幅に良くなりますが、一致した値を求めたい場合には使用できません。しかし関数を少し工夫しますと、検索方法にFALSEを指定したのと同様の結果にすることが可能です。
設定内容は下図のF列を参照してください。
性能改善版のVLOOKUP関数を実行しているシート
VLOOKUP関数が参照している[データ]シート
例えばF6セルは次のように動きます。
D6セルの内容を、範囲で指定した[MASTER]シートのD列から見つけます。その値がD6セルと一緒であれば、IF文のTRUEに書いている
VLOOKUP関数が実行され、範囲で指定したE列の値を戻します。
違った場合は、IF文のFALSEに書いている、「#N/A」が戻ります。(NA()は「#N/A」を戻す関数です)
このときIF文の条件式にあるVLOOKUP関数も、IF文のTRUE時に動くVLOOKUP関数も3番目のパラメータはTRUEになっており高速に処理できます。
実際にどのくらい速くなるか試したところ、修正前は20秒かかっていたものが、1秒以下になりました。 VLOOKUP関数が遅くて困っている方は、修正量もごくわずかなので試してみてください。
5.まとめ
Excelは機能も多く、全て習得するのは現実的ではありません。
周りの方の作業を目にする機会があれば、新たな発見があると思います。そのときは操作方法を聞いて、テクニックの幅を広げてみてはどうでしょうか。
本記事が好評であれば他のExcel Tipsについても記事にしていこうと思います。
【このほかの記事】
執筆者プロフィール：力石 知也
大手SIなどでアプリケーション開発からインフラ関連まで幅広く約30年経験。
現在は性能改善、品質向上などの非機能関連のコンサルティングを実施。
お問合せはお気軽に
https://service.shiftinc.jp/contact/
SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/
SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/
SHIFTの導入事例
https://service.shiftinc.jp/case/
お役立ち資料はこちら
https://service.shiftinc.jp/resources/
SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/