分析ツールの関数が標準で使える

0
    今までのExcelにはワークシート関数が二種類ありました。ひとつは、いつでも使える標準のワークシート関数。数値の合計を返すSUM関数や、条件分岐にかかせないIF関数などお馴染みの関数群です。もうひとつは分析ツールというアドインを組み込むことで使用可能になるワークシート関数です。分析ツールで提供されるワークシート関数には、指定した範囲内の乱数を返すRANDBETWEEN関数や、インチ・メートル・パスカルなどさまざまな単位を変換するCONVERT関数などがありました。

    分析ツールのワークシート関数はアドインとして提供されていましたので、[分析ツール]アドインを組み込まないと使用できませんでした。ところが、Excel 2007では、これら分析ツールのワークシートをアドインを組み込まなくても標準で使用可能になりました。

    下図はRANDBETWEEN関数を使っているところです。ワークシート関数のオートコンプリートにも表示されます。





    確認のために[アドイン]ダイアログボックスを開いてみましたが、間違いなく組み込まれていません。



    [分析ツール]で提供されていたワークシート関数は、複雑な学術計算を行うものが多いですが、中には日常でも便利に使える関数があります。下図はCONVERT関数で摂氏と華氏を変換しているところです。



    数式のネストレベルが拡大

    0
      数式で、関数の中に関数を入れることをネストまたは入れ子と呼びます。たとえば次のような使い方です。



      上の数式はIF関数の中に他の関数が入っていません。ネストしていない数式です。対して下の数式は、IF関数の中でもう一つIF関数が使われています。これがネストしている数式です。青色で示したIF関数は「1つめのネストしている関数」という意味でネストレベル1の関数とも呼ばれます。

      さて、このように関数の中に他の関数を入れて、その関数の中にもさらに別の関数を入れて、そのまた関数の中にも・・・と関数のネストを繰り返したとき、いったい何個の関数をネストできるのでしょう。実はExcel 2003までの仕様では、最大のネストレベルが7と決まっていました。





      Excel 2007では、許される関数のネストレベルが最大64になりました。
      Excel 2003ではエラーになって入力できなかった数式も、ほら!ご覧の通り。



      本当に64レベルまで入力できるか、実際にやってみました。



      IF(A1="",〜) の中で IF(A2="",〜) がレベル1のネスト、IF(A3="",〜) がレベル2のネスト・・・と続けて、IF(A65="",〜)がレベル64のネストです。問題なく入力できました。念のため、もう1つネストを深くしてみると、



      ちゃんとエラーになりました。しかも、エラーメッセージに「ネストが深すぎる」とエラーの原因まで明記されています。Excel 2003のエラーメッセージよりもわかりやすくなりましたね。でも、ヘルプで「ネスト」と書かれているのに、エラーメッセージでは「入れ子」となっています。このへんは用語を統一して欲しいものです。

      新しいワークシート関数

      0
        Excel 2007で追加されるワークシート関数は次の通りです。

        AVERAGEIF
        AVERAGEIFS
        COUNTIFS
        CUBEKPIMEMBER
        CUBEMEMBER
        CUBEMEMBERPROPERTY
        CUBERANKEDMEMBER
        CUBESET
        CUBESETCOUNT
        CUBEVALUE
        IFERROR
        SUMIFS

        CUBE〜というのは、SQL Serverからデータを抽出して、ピボットテーブルを作成するときなどに使用する「キューブ」を操作するための関数です。一般的にはあまり使われないでしょう。新関数の中で注目したいのは、

        AVERAGEIF
        AVERAGEIFS
        COUNTIFS
        IFERROR
        SUMIFS

        です。簡単に紹介しましょう。

        ■AVERAGEIF
        今までSUMIF関数とCOUNTIF関数はありましたが、指定した条件で平均を計算するAVERAGEIF関数はありませんでした。今回、新登場です。まぁ、SUMIF/COUNTIFってやれば平均も計算できたんですけどね。Tipsがひとつ減ってしまいました(^^;



        ■AVERAGEIFS,COUNTIFS,SUMIFS
        指定した条件に一致するデータだけ計算する「○○IF」関数は、今まで条件をひとつしか指定できないという制限がありました。新しいExcel 2007では「○○IF」関数に複数条件を指定できる「○○IFS」関数が追加されました。
        ここでは、SUMIFS関数を例にして使い方をご紹介しましょう。



        最初の引数には、条件に一致したとき合計する数値が入力されているセル範囲を指定します。上図ではセル範囲C2:C10です。条件は「条件が入力されているセル範囲」と「条件」を対にして指定します。ここでは「セル範囲A2:A10(名前)」が「田中である」と、「セル範囲B2:B10(住所)」が「横浜である」という2つの条件を指定しました。該当するセルはC2とC8なので、計算結果は「846」となります。

        注意しなければならないのは、指定した複数の条件はAND指定(かつ)となることです。「名前が"田中"または"鈴木"」という条件指定はできません。そりゃそうです。それなら従来のSUMIF関数を2つ使えばいいんですから。そうではなく「名前が"田中"かつ住所が"横浜"」という条件で計算ができます。関数の引数を見る限り、指定できる条件の数に制限はないように思います。

        SUMIFS関数の引数は次の通りです。

        SUMIFS(合計対象範囲,条件範囲1,条件1,[条件範囲2,条件2],[条件範囲3,条件3]...)

        ■IFERROR
        個人的に大注目なのは、このIFERROR関数です。たとえばVLOOKUP関数を使うとき、検索値のセルが空欄だったり、検索値が見つからなかったときなどにVLOOKUP関数はエラーになります。そこで今までは

          =IF(ISERROR(VLOOKUP(…)),"",VLOOKUP(…))

        のように、IF関数とISERROR関数を組み合わせてエラーを表示しないようにしてきました。IFERROR関数はその名の通り、このIF関数とISERROR関数を組み合わせた関数です。引数は次の通り。

        IFERROR(値,エラーの場合の値)

        さっきの「=IF(ISERROR(VLOOKUP(…)),"",VLOOKUP(…))」は次のように書けます。

          =IFERROR(VLOOKUP(…),"")

        VLOOKUP関数が正常に計算されるときはその値を返し、VLOOKUP関数がエラーになる場合は2番目の引数に指定した式を実行します。IFERROR関数自体はエラーにならないところがポイントです。



        上図のセルE2には=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,FALSE),"エラー")という式を入力し、セル範囲E3:E5にコピーしました。セルD3は空欄ですからVLOOKUP関数はエラーになります。また、VLOOKUP関数の第4引数にFALSEを指定して完全一致にしていますので、セルD4の「1006」は見つからずエラーになります。

        Excelに実用的な新ワークシート関数が追加されるのは、SUMIF関数などが登場したExcel 2000以来です。新しいSUMIFS関数とIFERROR関数は、Excelの使い方を大きく変える可能性を秘めた関数です。

        関数オートコンプリート

        0
          関数を入力するときの支援機能です。これは便利!Excel 2007新機能の中でも、一番のオススメ機能です。

          セルにRANK関数を入力する場合、「=R」まで入力すると、



          「R」で始まる関数がリストに表示されます。さらに「=RA」まで入力すると絞り込まれます。



          入力したい関数が見つかったら、矢印キーで選択します。選択した関数をセルに入力するにはTABキーを押します。



          関数によっては、計算の方法を決めるための引数を指定するものもあります。今回のRANK関数では、第3引数に"順位を昇順で求めるか、それとも降順で求めるか"を指定するために「0」または「1」を指定します。ところが、ほとんどの人は「あれ?どっちがどっちだったかな?」と悩みます。Excel 2007の関数オートコンプリートでは、こうした「列挙定数」もリストから選択できます。うれしいことに、何を指定するとどうなるのかといった解説付きです。



          これで関数の入力が簡単になりました。
          余談ですが、Excelをマスターしたいと思うのなら、絶対に![関数の挿入]ダイアログボックス(または関数ウィザード)を使ってはいけません。もし[関数の挿入]ダイアログボックスを使っていて「なかなか関数をマスターできないな…」とお悩みなら、その原因は[関数の挿入]ダイアログボックスを使っているからです。

          閑話休題。
          関数オートコンプリートで列挙定数がリスト表示される関数は、CELL、FV、HLOOKUP、MATCH、PMT、PV、RANK、SUBTOTAL、およびVLOOKUPだけです。

          関数オートコンプリート機能で表示されるドロップダウンリストでは、次のキー操作が可能です。

          上矢印キー:1つ上の関数を選択します
          下矢印キー:1つ下の関数を選択します
          PageUpキー:表示されているリストの先頭関数を選択します
          PageDownキー:表示されているリストの末尾関数を選択します
          Homeキー:リストの先頭関数を選択します
          Endキー:リストの末尾関数を選択します
          Tabキー:選択した関数をセルに入力します
          Escキー:リストを閉じます
          Alt+↓キー:関数オートコンプリートのオン/オフを切り替えます

          PageUp・PageDownキーとHome・Endキーは、ドロップダウンリストに全ての関数が表示しきれず、スクロールバーが表示されているときに動作が異なります。

          ドロップダウンリストで選択した関数をセルに入力するには、Tabキーを押すか、入力したい関数名をダブルクリックします。たとえば「=s」という挿入トリガで関数オートコンプリートを起動して「SUM」を選択した場合には、Tabキーまたはダブルクリックで「=SUM(」が入力されます。

          VBEでは

            Range("A1").

          まで入力すると、関数オートコンプリートのようなドロップダウンリストが表示されます。ちなみにこれを「自動メンバー表示」と呼びます。ここで「End」を選択して「(」を入力すると、コードペインには

            Range("A1").End(

          と入力されます。
          しかし、Excel 2007の関数オートコンプリートは違います。セルに

            =s

          と入力してリストから「SUM」を選択し、その状態で「(」を入力すると

            =s(

          と、何ともマヌケで腹立たしい結果に終わります。関数オートコンプリート機能では「選択した関数名に続く(や"などの記号を入力しても、自動的に補完してくれない」です。このへんのUIは、VBEのように"賢く"して欲しかったですね(^^;

          Topページへ戻る
          calendar
              123
          45678910
          11121314151617
          18192021222324
          252627282930 
          << November 2018 >>
          selected entries
          categories
          archives
          recent comment
          recent trackback
          recommend
          recommend
          recommend
          recommend
          recommend
          links
          profile
          search this site.
          others
          mobile
          qrcode