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

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

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

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





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



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



    配列数式での列指定が可能

    0
      配列数式を使わない人には関係ない話ですが。
      今までのExcelでは、配列数式の参照セルに列全体を指定することができませんでした。
      まず普通の配列数式。Excel 2003です。



      セルE2には

      {=SUM(IF(A1:A8=$D$2,B1:B8,0))}


      という配列数式が入力されています。参照しているセル範囲は「A1:A8」と「B1:B8」です。
      これを「A列全体」と「B列全体」というように指定してみます。入力されているデータが可変のときは、ついこうやりたくなりますよね。



      ところが、配列数式の引数には"列全体"を指定することができませんでした。これがExcel 2007では可能になっています。
      こちらも最初は普通の配列数式から。



      問題ないですね。続いてExcel 2003ではエラーになった"列全体"の指定です。



      というように正しい結果を返してくれます。

      ところで、Excel 2007はワークシートの領域が大きくなっています。Excel 2003で"列全体"ということは、ワークシートの行数である65,536個のセルが計算対象となるのに対して、Excel 2007のワークシートは1,048,576行もあります。同じように"列全体"を参照してしまうと、ナント16倍も大きいセル範囲を指定することになります。これは、速度的にどうなんでしょう?いくら配列数式といっても、再計算に一晩かかるようでは使い物になりません。

      実際に計測してみました。上で使ったワークシートの計算に要する時間です。なお、Excel 2003は列全体を指定できませんので、65535セルを参照しています。

      【Excel 2003】




      【Excel 2007】




      「計算所要時間」とは、数式が初めて計算(評価)されるときに要する時間です。「再計算所要時間」は、一度計算された数式が二度目以降に行う再計算です。Excelには"スマート再計算エンジン"というのが搭載されていて、一度計算した数式は、その数式に影響を及ぼすセルが変化したときだけ、もう一度はじめから計算をやり直す仕組みになっていますので、二度目以降の再計算は、一度目に比べて非常に高速です。

      計測してみると、やはりExcel 2007の方が遅いですね。しかし、無理もありません。「A:A」と「B:B」で合計2,097,152個のセルを参照しているのですから。配列数式に列全体を指定できるようになったからといって、多用すると計算時間も増大するかもしれませんね。

      なお、上の計測結果はあくまで"目安"とお考えください。Excelで数式の計算時間を計測するのは、かなりデリケートな作業です。上に書いたように、一度目と二度目以降では計算方法が変わってきますし、数式におけるセルの依存関係を表す"計算チェーン"の構成と再構成も計算時間に影響してきます。さらに、Windowsのキャッシュも無視できません。計測の環境とタイミングによっては、異なる数値が導き出されるかもしれません。そのうち、数式の計算時間を計測するベンチマークでも作って、詳細に検討してみましょう。いずれにしても、列全体を参照した場合は、より多くのセルを参照することになるExcel 2007の方が、若干遅いのは間違いないと思います。


      オートフィルタのバグ

      0
        某掲示板で教えてもらいました。ここでは、下図のようなリストを例にします。



        まず正常な動作から。

        1.アクティブセルをA1に置いてオートフィルタを設定します。
         このアクティブセルの位置は重要なポイントです。


        2.A列を「女」で絞り込みます。


        3.結果はこうなります。


        4.セルB3からセルB7までドラッグして選択します。


        5.Deleteキーを押します。


        6.A列のフィルタを解除します。


        7.絞り込まれたデータだけが削除されています。


        今度はバグの操作です。

        8.セル範囲A1:A2(タイトル行)を選択してオートフィルタを設定します。


        9.A列を「女」で絞り込みます。


        10.セルB3からセルB7までドラッグして選択します。


        11.Deleteキーを押します。


        12.A列のフィルタを解除します。


        13.絞り込まれたデータ以外も削除されてしまいます。


        この現象はオートフィルタを設定するときに、どのセルを選択していたかによって挙動が異なります。

        (A)リスト内の任意のセルにアクティブセルを置いた場合 → 正常
        (B)リスト全体(ここではセル範囲A1:B7)を選択した場合 → 正常
        (C)タイトル行(ここではセル範囲A1:B1)だけを選択した場合 → 異常
        (D)1行目全体を選択した場合 → 異常

        となります。
        Excel 2003では発生しませんし、オートフィルタ設定時の選択セルによって結果が異なるという仕様は変です。オートフィルタや並べ替えのとき、Excelは対象のリスト範囲を自動的に認識しますが、それに関連したバグと見て間違いないでしょう。


        Topページへ戻る
        calendar
        1234567
        891011121314
        15161718192021
        22232425262728
        293031    
        << January 2017 >>
        selected entries
        categories
        archives
        recent comment
        recent trackback
        recommend
        recommend
        recommend
        recommend
        recommend
        links
        profile
        search this site.
        others
        mobile
        qrcode