重複データの削除(もう少し詳しく)

0
    Excel 2007には重複データを削除する機能が、標準機能として追加されました。おそらく、こうしたニーズが世界中で高かったのでしょうね。どんな機能かは、以前にレビューした下記のページをごらんください。

    重複データの削除

    さて、この[重複の削除]機能ですが、もう少し詳しく見てみましょう。

    ■残るのは必ず先頭のデータ
    たとえば次のようなリストを例にします。重複するデータは[Name]と[Area]です。わかりやすく、重複データには同じ色をつけています。



    [データ]タブの[重複の削除]ボタンをクリックすると次のダイアログボックスが表示されます。今回重複をチェックしたいのは[Name]と[Area]ですから、[Age]のチェックボックスをオフにして[OK]ボタンをクリックします。





    ここで注意しなければいけないのは、削除されるのは必ず下に存在するデータだということです。言い方を変えれば残されるのは必ず先頭のデータです。まったく同一のデータが重複していたのなら、どれを残しても問題はありません。しかし、今回のケースのように[Name]と[Area]は重複しているけど[Age]が異なっている場合、これを区別したいときもあるでしょう。完全に同一のデータを削除するとき以外は、慎重に操作する必要があります。

    ■空いた位置には空白セルが挿入される
    [重複の削除]機能は、重複しているセルを削除する機能です。では、削除されたセルはどうなるのでしょう。次のような実験をしてみました。



    リストの下に、書式を変更したセルを置きました。[重複の削除]によってセルが削除されると、通常の[セルの削除]と同じように周辺のセルがシフトすると予想したからです。また、もしシフトせずに新しいセルが挿入されるのなら、その新しいセルの書式はどうなるのかを調べました。



    結果はブランクセル(空白セル)が挿入されました。周辺のセルがシフトすることもありません。ちなみに、削除されるのはリスト内のセルだけです。行全体が削除されるわけではありませんので、リストの左右に別のデータが入力されていても影響を受けることはありません。

    ■数式が入力されている場合は注意が必要
    リスト内に数式が入力されていた場合はどうでしょう。結論から書くと、最下行でSUM関数やAVERAGE関数などを使っているセルは削除の対象範囲になりません。削除の対象範囲でないのですから、当然削除もされません。ただし、これは関数を使っていた場合です。その数式が、=A1+B1のようにセルの参照式だった場合は削除の対象範囲に含まれます

    【関数を使っていた場合】




    【参照式を使っていた場合】




    削除されてしまったセルを参照式が参照しいた場合、その参照は変化します。リストの内部に数式が入力されていた場合も、理屈は同じです。ただし、削除されたセルを解決するようにインテリジェンスな処理は行われません。

    【関数を使っていた場合】




    【参照式を使っていた場合】




    Excel 2007の新しい[重複の削除]機能は、計算式を含まないリストで実行した方がよさそうですね。カンですが、この機能はあまり使われないような気がします。用途が限定されて、汎用性が乏しいですからね。

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

    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の方が、若干遅いのは間違いないと思います。


      複数ブックごとExcelを終了させる方法

      0
        やっと見つけた!Excelの終了ボタン」に書いた複数ブックを開いている状態Excel自体を終了させる方法ですが、もうひとつ発見したので書いておきます。

        やり方は簡単です。Shiftキーを押しながらExcelの閉じるボタン[×]をクリックするだけです。これで、開いているすべてのブックを閉じて、Excel自身も終了することができます。

        てゆーか、こういうのは、ちゃんとヘルプとかに書いてくれないと、誰も気づかないでしょう。それとも、複数ブックを開いた状態でExcelを終了されたくない大人の事情でもあるのでしょうか・・・

        テーブルの行列を入れ替える

        0
          テーブルのネタをもうひとつ。
          テーブル書式に設定した範囲を、行列を入れ替えてコピーしてみましょう。
          テーブルを選択してコピーし、[貼り付け]ボタンの▼をクリックします。



          ところが、お目当ての[行列を入れ替える]がグレイアウトして使えません。
          テーブルはExcelが内部的に管理している特別な領域ですので、行列を入れ替えてしまうと困るのでしょう。
          まぁ、理由はわかりますが、とりあえずデータだけでもコピーしたいところです。
          そこで[形式を選択して貼り付け]を実行してみます。



          しかし、こちらも[行列を入れ替える]オプションがグレイアウトしています。
          テーブルの行列を入れ替えてコピーすることはできないのでしょうか・・・
          いえいえ、実は方法があります。
          右上の[コピー元のテーマを使用してすべて貼り付け]オプションをオンにすると、[行列を入れ替える]オプションが使えるようになります。



          書式はコピーされませんが、とりあえずデータだけは行列を入れ替えてコピーできました。



          テーブルの左上セルを空欄にしたい

          0
            Excel 2007には強力なテーブル機能が搭載されました。今までのリスト機能は、お世辞にも便利とは言えず、とても積極的に活用しようとは思えませんでした。ところが、Excel 2007のテーブル機能は名前が変わっただけでなく、実にさまざまな改良が加えられました。条件付き書式と並んで、Excel 2007の"イチオシ"機能といっても過言ではありません。そういえば、このレビューではまだテーブル機能について紹介していませんでした。書くことが多いので、そのうちじっくり解説します。

            さて、非常に便利なテーブル機能ですが、ひとつだけ気に入らない点があります。実際にやってみましょう。

            テーブルにしたい表を選択して[テーブルとして書式設定]ボタンをクリックします



            範囲の確認で[OK]ボタンをクリックすると、テーブルに変換されます



            非常に簡単な操作でテーブルに変換できたのですが、よく見るとセルA1に「列1」という文字が勝手に挿入されてしまいました。邪魔です。ここは空欄でいいんです。元のデータはそうなってたはずです。勝手に変えないで欲しいです。そこで、アクティブセルをセルA1に移動してDeleteキーを押すと・・・削除できない。セルA1の「列1」は消すことができないんです。

            もちろん「支店名」など別の文字に書き換えることはできますが、空欄にはできません。実はテーブル機能では、こうしたタイトルが必須なのです。「1月」「2月」や「東京支店」「横浜支店」などのタイトルは、テーブル内を参照するときに使用されます。



            上図は、セルE2からテーブル内のセル(ここではA2)を参照する式です。テーブル内のセルA2は、参照式を入力した2行目と「列1」列が交差するセルですので「=テーブル4[[#この行],[列1]]」で表されます。このような参照方法を行うことで、テーブル内での計算式が包括的に管理され、行列の追加や削除、数式の変更などに対して柔軟に対応できます。なお、こうした参照を構造化参照と呼びます。

            構造化参照のために、空欄だったセルA1には「列1」という仮の列見出しが入力されました。まぁ、それはわかりますが、それでも、やっぱり、邪魔ですね。無粋です。この「列1」を削除することはできませんが、隠すことなら可能です。半角でも全角でもいいですから、スペースを入れておけばいいんです。ただし、テーブル内の列見出しに、スペースは1つしか指定できませんから注意してください。テーブル内に同じ列見出しは複数存在できないからです。

            新しいテーブル機能と構造化参照については、まだたくさんのネタがあります。そのうちじっくり解説しましょう。

            図のリンク貼り付け

            0
              Excelには図のリンク貼り付けという機能があります。任意のセル範囲をコピーして、別の場所に図として貼り付ける機能です。貼り付けられるのはただの図ではなく、元のセル範囲にリンクされた図です。コピー元のデータを編集すると、貼り付けた図も変化します。貼り付くのは図ですから、大きさや位置などを自由にレイアウトできます。Excel 2003で実行するところをご覧ください。

              1.Shiftキーを押しながら[編集]メニューをクリック


              2.コピー元が図として貼り付きます


              3.図なので大きさの変更も自由です


              この「図のリンク貼り付け」機能は、隠しメニューになっていました。Shiftキーを押しながら[編集]メニューをクリックしないとコマンドが表示されません。では、[編集]メニュー自体がなくなってしまったExcel 2007では、どうやって実現するのでしょう。実はこんなところに隠れていました。

              1.[貼り付け]ボタンの下にある▼をクリックし、[図]をポイントします


              2.実行結果は同じです


              やっと見つけた!Excelの終了ボタン

              0
                中途半端なMDI」で書いたように、Excel 2007は中途半端なMDIになっています。複数のブックを開いている状態で、Excelの閉じるボタンをクリックしても、Excelが終了するわけではなく、アクティブなブックが閉じられるだけです。挙動に関しては「中途半端なMDI」をご覧ください。

                [Officeメニュー]を開けば、右下に[Excelの終了]ボタンがあります。



                この[Excelの終了]ボタンをクリックすれば、開いているすべてのブックをすべて閉じると同時に、Excel自体も終了することができます。しかし、そのために毎回[Officeメニュー]を開かなければならないのは面倒くさいです。

                何か方法はないものかと探していたら、クイックアクセスツールバー(QAT)に追加できるボタンの中に、Excelを終了させるボタンを発見しました。

                1.QATの[クイックアクセスツールバーのカスタマイズ]ボタンをクリックして[その他のコマンド]を実行します


                2.[コマンドの選択]リストで「Officeメニュー」を選択すると、下の方に[終了]というコマンドがあります。これがExcelを終了させるボタンです


                3.QATに登録しておけば、クリックするだけでExcel自体を終了することができます


                日付のグループ化

                0
                  オートフィルタの新機能です。まだ書いていませんでしたよね(^^;

                  下図のような日付データが入力されていたとします。



                  オートフィルタを設定して▼ボタンをクリックすると、次のように日付がグループ化されています。



                  それぞれのグループを展開すると、次のように月単位で日付データがまとめられています。



                  最小の単位は"月"です。"週"や"四半期"単位ではグループ化されません。
                  標準では、こうした日付データにオートフィルタを設定した場合、自動的にグループ化されるようになっていますが「勝手にグループ化しないで!」というユーザーのために、グループ化させない設定も用意されています。[Excelオプション]ダイアログボックス[詳細設定]グループの[オートフィルタメニューで日付をグループ化しない]をオフにすればグループ化されません。




                  この設定はブックごとにオン/オフできます。この設定はブックに記録されますので、設定をオフにしたブックを次に開いたときにも有効です。

                  さて、オートフィルタには他にもまだ新機能が隠されています。そのひとつが「今月」や「昨年」などで絞り込む日付フィルタです。オートフィルタを使うユーザーの多くは、日付データを扱う・・・なんてユーザビリティ・テストの結果を反映したのでしょうか。まぁ、確かに日付データを絞り込む場合には便利かもしれません。



                  要するに、古い「フィルタオプションの設定」を自動的に設定してくれる機能だと思いますが、マクロ記録してみると興味深いコードが生成されました。それはまた、別のコンテンツで。

                  ステータスバーの消し方

                  0
                    Excel 2003は[オプション]ダイアログボックスの[表示]タブで、Excelのパーツごとに表示/非表示を設定できました。




                    Excel 2007でも[オプション]ダイアログボックスで、パーツの表示/非表示を設定できるのですが、さらに細かい指定が可能になっています。



                    「数式バー」などExcel全体に影響するパーツと、「スクロールバー」や「シート見出し」などシートごとに表示/非表示を指定できるパーツなどに分類されています。でも、ちょっと待ってください。いくら探しても「ステータスバー」を非表示にするオプションが見あたりません。どうやら[オプション]ダイアログボックスで設定できなくなってしまったようです。ちなみに、リボン[表示]タブの[表示/非表示]グループにも、いくつかの設定項目が用意されているのですが、ここでも「ステータスバー」を消すことはできません。



                    Excel 2007では「ステータスバー」を非表示にする設定が割愛されたようです。Excel 2007のステータスバーには、マクロ記録を開始するボタンや、機能が拡張されたオートカルクなどが配備されていますので、これを隠すのは想定外ということでしょうか。手動操作でステータスバーを消すことはできませんが、以下のマクロで非表示にできます。


                    Application.CommandBars("Status Bar").Visible = False


                    グラフのオプション

                    0
                      グラフのネタをもうひとつ。
                      Excelの[設定]ダイアログボックスには[グラフ]というタブがありました。そこでは、折れ線グラフで空白データをどのようにプロットするかとか、非表示になっているセルをプロットするかどうかなどを指定できました。



                      この[グラフ]タブは、グラフを選択しているときだけ操作が可能でした。Excel 2007では[設定]ダイアログボックスも大きく変化したので、グラフを選択しているからといって何かが変わるわけではありません。では、これらの設定はどこで指定すればいいのでしょう。

                      1.リボンの[グラフツール]-[デザイン]を開き、左から2番目[データ]グループの[データの選択]ボタンをクリックします


                      2.表示される[データソースの選択]ダイアログボックスで[非表示および空白のセル]ボタンをクリックします


                      3.表示される[非表示および空白のセルの設定]ダイアログボックスで設定します




                      これを見つけるのには苦労しました。今までのUIが「グラフの設定」だったのに対して、Excel 2007ではまず「データの選択」を実行しなければなりません。それで表示される[データソースの選択]ダイアログボックスは、通常データのプロット位置を変更したり、軸のラベルを設定するときなどに使います。まさかここから「空白セルのプロット」と「非表示セルのプロット」が開くとは想像できません。

                      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