Excelの数式検証機能を検証してみた記録

事務担当
こんにちは。今日は、事務担当の私から、Excelの数式検証機能について調べたり、試したりした結果をレポートします!
目次

Excelの数式は「シンプルイズベスト」

分かりやすく、直しやすく、使いやすく。

数式の中身ががシンプルだと、まず何といっても「分かりやすい」です。

事務担当
VLOOKUP関数を使っているのなら、一覧表から値を取得してくるものなんだな、COUNTIF関数を使っているならある条件のものを数えたいんだな、って具合に。

複雑な計算処理といえば格好良いですが、要は単純処理の組み合わせです。ひとつひとつの数式ができることは総じて単純なので、 数式を組み合わせて複雑な処理を実現するときは、ひとつひとつの処理ステップのゴールを見えるように可視化して、「分かりやすく」数式を組み合わせることが大切。
1セル1数式ぐらいの単純なものであれば、業務の途中で条件が変更になっても「直しやすい」ですし、他の資料に応用するときも途中まではコピペできるので、「使いやすい」です。

「分かりやすく」数式を組み合わせることについて、次の記事でも解説してます。

誰でも使えるExcel資料 ≠ 自分が使いやすいExcel資料

でも、世の中のすべてのExcel資料がそんな「シンプルイズベスト」で「分かりやすい」資料じゃないっていうのは、皆さんも実感されているはず。資料を作成する人が、自分にとって便利なように利用できる高い自由度があることが、Excelというツールが、資料作成によく利用される理由であるのは確実。

この数式って便利なんだけど、私には以前から使っているこっちの書き方のほうがしっくりくるのよね。
この資料は自分しか使わないし、ググって調べた長い式で、ひとつのセルにまとめて書いちゃおう。

人が作った資料は、詳しく見ていけばいくほど面白いことに人の個性が見えてきます。でも・・・。
自宅で使う家計簿計算であれば「自分」最適が何より。けれど、会社においては、従業員「全体」で無駄を減らして業務効率を上げることが期待値。そういった点で、「誰でも使えるExcel資料 > 自分が使いやすいExcel資料」という優先度を付けた発想を思い浮かべてみることも、大切なポイントになりそうです。

他人が作ったExcelブックって・・・

突然ですが、社内に、Word、Excel、Power Pointなどなど、「IT何でもできる人」いませんか?なんとなくやりたいことを伝えると、さささーっと資料作ってくれる人。普段使いの集計資料も「こんなのでいいかな?」って。すごいですよね。憧れます。実際に使ってみると、他の分析資料でも応用できそうなところが!これはラッキーです!

でも、処理用セルや途中計算用のセルがひとつもなくて、複雑な数式がずらーっとひとつのセルに。うーん降参。。。

応用したいけど、どこからどこまでコピペすればよいか分からない。忙しそうだし、頼むよりは自分で理解できればいいんだけど・・・

ここまでの例は稀かもしれませんが、作り方が難しすぎて転用できない資料って結構たくさんありませんか?
確かに「そのまま一切の変更をせずに使う」だけなら、難しすぎても構わないのでしょうが、せっかくの社内資産です。他にも有効に使いたい。
どんなことを表示しているセルなのか、加筆修正が必要なら追加で何をしなくちゃいけないのか。資料をよりよく使いやすくしたいのに、セルのそもそもの目的を理解するために時間を使うのなんて、もったいない!

そんなときに活躍するのが、今回の数式検証機能です。

数式の検証機能を分かりやすく解説

「数式の検証」って?

何がしたいセルのなのか、を理解するためのコツとして、「数式の検証」を紹介します。
なんだそれ?と思ったアナタ。実は違う名前で見たことがあるはず。エラーになったセルにカーソルを合わせると表示されるエクスクラメーションマーク(いわゆるビックリマーク「!」)。
下向き三角▼を押すと、エラーの詳細を表示してくれますが、そのなかに、いるんです。「計算の過程を表示」って。この「計算の過程を表示」が、「数式の検証」のことになります。

①数式が設定されているセルを選択して、「計算の過程を表示」を選択します。

②ポップアップ画面「数式の計算」が表示され、エラーになったセルの数式を表示します。「検証」ボタンを押すと、下線で示された計算式を検証します。(今回は、カンマ「 , 」がないため、エラーになっている状態の図です。)

③計算結果を代入し、さらに計算式がある場合はその検証を行います。

④計算結果が終了すると、下線付き数式がなくなります。もう一度確認したいときは「再び開始」ボタンを押します。終了する場合は「閉じる」ボタンを押します。

どうやって使うの?

エラーになっているセルにしか使えないの?

この「計算の過程を表示」は、エラーのセルにしか表示されませんが、セルに入力した計算式を検証する方法もあります。この場合は、調べたいセルを選択した状態で、「数式」タブを選択し、「ワークシート分析」の中の「数式の検証」アイコンをクリックします。

使ってみよう!

複雑化した数式の計算

例を挙げて、使ってみることにします。

次の図では、D2セルに何だか複雑な数式が設定されていることが分かります。「えーと、ISNAで、MATCHの値を取得できなかったときは、ん?「B2」って何が入ってたっけ?」
・・・では、「数式の検証」を行っていきましょう。

数式の検証 開始

①D2のセルを選択し、「数式の検証」アイコンをクリックします。ポップアップ画面「数式の計算」が表示され、D2セルの数式を表示します。

数式の検証 ステップイン

②下線付きの数式が参照している値だった場合にのみ、ボタンを押すことができます。今回は、B2セルを参照していたので、B2のセルにはどんな値が入力されているかを確認するために「ステップイン」ボタンを押します。

“A001″という値の表示が確認できます。

数式の検証 ステップアウト

③数式内にて参照されている値を、「ステップイン」して確認した場合に、ボタンを押すことができます。1階層でも「ステップイン」していれば、定数まで確認できなくても「ステップアウト」ボタンを押すことができるようになります。今回は、B2セルに定数「A001」といった値が入力されていることを確認できたため「ステップアウト」ボタンを押します。

直近でステップインや検証などで得られた結果は斜体で表示されます。「ステップイン」して確認した定数”A001″が斜体で表示されていますね。

数式の検証 検証

④下線付きの数式の結果を検証します。今回は、定数”A001″が””(空文字)ではないことについて検証するため、「検証」ボタンを押します。

検証の結果、「TRUE」が返されました。先ほど同様に、直近で得られた結果が斜体となって表示され、次に検証できる数式に下線が引かれていることが分かります。

分かるまで繰り返す

⑤「数式の検証」を利用するのは、あくまで「このセルが何をしているか」目的を理解するためなので、の疑問点が解決したら、そこで閉じてしまって構いません。また、次に検証する数式が意味のない数式であった場合は、細かい検証処理はスキップされます。

例えば、IF(TRUE, [値が真の場合], [値が偽の場合])の数式は、[値が偽の場合]にどんな数式が記入されていようと、参照することはないので検証は行われません。

いかがですか?

深い階層にある数式も確認できる

「数式の検証」を利用すると、参照したセルが参照している数式に使われている参照のセルが・・・なんて、深い階層にある数式も検証することができます。

セルの目的を理解してカスタマイズを

例に挙げたExcelブックは、各拠点ごとの在庫数を取得して、シート「total倉庫在庫」に在庫数を表示させるものでした。シートが複数に分かれていたり、エラーを表示させないために関数をネストしていたり、それらを考慮するとどうしても長々とした数式になってしまいます。

更に、シートのリンクセルを用意してみたり、あえてエラーのまま表示させて修正箇所を分かるようにしてみたり、いろいろ改善できそうです。
もちろん現状でも使えないことはないですが、この資料を使用しているメンバーはどうでしょうか?明日からチームに参画してくる新人は? ほんの少しでも「誰でも使えるExcel資料」に近づけることができれば、それだけでメンバーの悩む時間を削ることができます。

使いづらい資料も時間をかけずに使いやすく!

親切な人が提供してくれた集計用資料、エンジニアが作成した複雑な分析用Excelブック。うまく使いこなせばそれだけで業務効率が上がります。

社内のメンバーみんなが「何でもできる人」になって、お互いに成長しあう。すごいですよね。憧れます。まずは一歩、中身を「検証して理解」するところから始めてみてもいいかもしれません。

目次
閉じる