エクセル簡単テクニック　−後編

1　表を使いやすくするためのテクニック

表が完成した後、データを追加したり、入れ替えたり頻繁に使っていく表は、データを入力し易いように設定しておくと便利です。

1-1　セルの保護・ロック

ワークシートは、表完成後にデータを入力していて、誤って数式を壊してしまうおそれがあるため保護した方が良いですね。

　メニューバーの「書式」をクリックし、「セル」をクリックすると下の画像の「セルの書式設定」のダイアログボックスが表示されます。

この「セルの書式設定」に「保護」タブがあり、クリックすると「ロック」の左横チェックボックスにチェックが入っています。

　セルの保護は勘違いしやすいのですが、セル単位で特定のセルのみ保護（ロック）するものではなく、ワークシート単位で保護するときに、特定のセルのみロックを解除できるということです。

　つまりワークシートを保護すれば、標準の状態でセルは保護（ロック）されており、ワークシートを保護しても、データを入力するセルは、ロックを解除しておかないと入力できません。

上のサンプルの「自動車購入価格比較表」では、黄色に配色したセルは、たとえ表が完成していても、車種を変えたりしてデータを入れ直すことができる入力セルです。

　そのため黄色のセルを選択して「保護」タブにある「ロック」の左横チェックボックスのチェックを外しています。

　そして、その後にワークシートの保護をかけないと、セルの保護（ロック）を解除した意味が無いので、メニューバーの「ツール」をクリックし、ホップアップメニューの「保護」の「シートの保護」をクリックし、「OK」をクリックして、シートの保護をかけます。

　またシートの保護の解除は、メニューバーの「ツール」をクリックし、ホップアップメニューの「保護」の「シートの保護の解除」をクリックするだけと簡単です。

　なおExcellのバージョン2002では、「シートの保護」のダイアログボックスがExcell2000と少し異なり、場合によっては許可する操作の詳細な設定ができますが、普通はそのまま「OK」をクリックすれば良いでしょう。

　こんな面倒な「シートの保護」は必要ないと思われるかもしれません。
　しかし完成した表のデータ入力のときに、誤って数式などを変更してしまうおそれもあり、便利な機能ですから是非使ってください。

1-2　入力方向の切替

表が完成した後、データを追加したり、入れ替えたり頻繁に使っていく表は、データを入力し易いように入力した後にセルを移動する方向を変更しておくと便利な場合があります。

　普段入力後に「下」のセルに移動する設定であれば、データ入力後に「Enter」キーを押すと、同じ列の一つ下のセルに移動し、続けて下のセルのデータを入力するのであれば、それで良いのですが、横方向に入力していく表は「Enter」キーを押した後に下のセルに移動しては不便です。

　サンプルの表は、基本的には縦方向に入力する表であり、データ入力後に下のセルに移動した方が使いやすいのですが、表完成後に、例えばネットで値引き情報を調べて、値引額のみ横方向に入力していく場合は、セルの移動する方向を右に変更すると便利です。

　具体的には、メニューバーの「ツール」をクリックし、プルダウンメニューの「オプション」をクリックすると下の画像の「オプション」のダイアログボックスが表示されます。

この「オプション」の「編集」タブをクリックすると左側３段目に「入力後にセルを移動する」という設定項目があります。

　この「入力後にセルを移動する」の左横チェックボックスにチェックが入っており、その「方向」の右側の枠に「下」と表示されていると変更が必要です。

　この「方向」の枠の右の▼をクリックし「右」に変更すれば、入力後に右方向にセルが移動するようになります。

このサイトの他のエクセルのファイルで説明用に使用している「汎用マルチ集計表」や「勤務時間実績表」などサンプル表は、入力後に右方向にセルが移動すると便利な表が多いように、横方向に入力する表は意外と多く、入力方向の切替をマスターすると入力が楽になります。

　なお、逆に入力後に右方向にセルが移動する設定となっていて、下のセルに移動するように変更したい場合も、この「オプション」の「編集」タブの「方向」で「下」に変更することが可能です。

1-3　表示ズーム

パソコンの画面の解像度とも関係がありますが、画面のサイズの問題で少し大きな表は画面に納まらないと見にくいですね。

メニューバーの「表示」をクリックし、プルダウンメニューの「ズーム」をクリックすると「ズーム」のダイアログボックスが表示され、「倍率」をクリックして選び、ワークシートの拡大、縮小ができます。

　なお表示されている倍率ではなく任意の倍率を指定することも可能です。

　右の画面のように、最下段の「指定」を選び、その右側の枠内に例えば「70」と入力し、「OK」をクリックすれば70%で表示されます。

縮小して文字や数値があまり小さくなると読めないでしょうが、「75%」ぐらいの倍率で、スクロールバーを使用しなくても表の全体が見れると便利であり、サンプルの「自動車購入価格比較表」は、ここに説明用の画像として載せるために全て「75%」に縮小しています。

1-4　行または列の非表示

表が完成した後、表を見るために不要な行や列を非表示にし、表をコンパクトにして重要な項目のみ表示することも解りやすくするテクニックです。

　行単位、あるいは列単位の非表示の設定は、行番号または列番号を右クリックし、表れるプルダウンメニューの「表示しない」をクリックするだけと簡単です。

　例えば、サンプル表の行5「排気量」、行6「車両重量」、行13「自動車税」〜行18「消費税」を非表示にしたものが次の表です。

　

　行番号を見ると非表示にした行が抜けていて、行4の次は行7、行12の次は行19となっています。

　この表では税額計算のために「排気量」と「車両重量」を入力していますが、「グレード」が解れば比較には十分でしょうね。また税・費用の内訳も省けばコンパクトになり、随分見やすい表になります。

　なお連続する行は、行番号13の上で左クリックし、そのまま下に行番号18までマウスを滑らせれば一括選択できるので、その後に右クリックし「表示しない」をクリックすれば、行13〜行18が非表示となります。

　また非表示にした行は、表示されていないので行番号は抜けています。非表示にした行の再表示は、番号が飛んでいる前後、この場合行番号14と行番号19を含めて行を選択し、右クリックし「再表示」をクリックすれば元に戻り表示されます。

＜スポンサーリンク＞

2　リストの並べ替え

表としては完成していても、少し工夫すると随分見易くなるものです。
　エクセルでは、データをランダムに入力しておいて、後から規則性のある順番に並べ替えることが簡単にできます。まあ規則性といっても、ABC・・・Z順、数値の小さい順（昇順）、ZYX・・・A順、数値の大きい順（降順）ぐらいのものですが、３つのキー項目によりリストの並べ替えをすることができます。

　「並べ替え」は難しくないのですが、説明は少しややこしいので、下の簡単な表を用意しました。
　メニューバーの「データ」をクリックし、プルダウンメニューの「並べ替え」をクリックすると、「並べ替え」のダイアログボックスが表示されます。（下の右側の画像）

　ただし、あらかじめ「並べ替え」をする範囲、つまり複数のセルを選択しないとダイアログボックスは表示されません。

　このダイアログボックスで、最優先されるキー、２番目に優先されるキー、３番目に優先されるキーを選び、昇順または降順を選択し「OK」をクリックすれば並べ替えはできますが、行を並べ替えるか、列を並べ替えるか、指定した範囲の先頭行（列）は見出し（タイトル）かデータか、など少し注意が必要です。

下にある行を上に持っていったり、上にある行を下に下げたりして、縦方向に行を移動させて並べ替えることが標準の設定となっています。つまり並べ替えの方向が上下移動で行を並べ替えるのであれば簡単です。

　単純に、行単位で上下に移動させるときは、複数のセルの範囲を指定するときに、行単位で複数の行を選択し、その後、メニューバーの「データ」をクリックし、「並べ替え」をクリックして表示されるダイアログボックスで「最優先されるキー」に列を選び、「昇順」か「降順」をチェックして選び「OK」をクリックしてダイアログボックスボックスを閉じれば完了です。

上の表で、行3〜行10を一括選択し、最優先されるキーとして「F列」、つまり金額を選び、「降順」を選び、金額の高い順に並べ替えたのが下の表です。（このとき範囲の先頭行は「データ」にチェックされていることを確認してください。）

　商品名「C」が、金額\3,600と最も高いので表の一番上に移動してますね。

　２番目に優先されるキーと、３番目に優先されるキーを選んでないので、金額\3,000の「G」と「H」、金額\1,500の「B」と「E」は、もともと上にあったものが上にあります。

次に、２番目に優先されるキーとして「E列」、つまり数量を加えてみました。
　数量も多いものから並べるとして、「降順」をチェックして選び、下の左の画像のダイアログボックスで「OK」をクリックして並べ替えた表が、下の右の表です。

　前の表との違いは、商品名「E」と「B」入れ替わっており、金額\1,500が同じ中で、数量は「E」が10、「B」が３と、「E」が多いため「B」の上にきています。

２番目に優先されるキーを指定する必要がある場合は、最優先されるキーのデータの値が、同じ値のデータがある場合に、その同じ値のデータを別のキーで区別するために用いるものであり、普通は「最優先されるキー」だけで並べ替えても十分なことが多いでしょう。

　3番目に優先されるキーの指定も、最優先されるキーと２番目に優先されるキーでは序列が付かないときに使うもので、指定の仕方は同じです。

　行単位で、上下に移動させ並べ替えていては、不都合なこともあります。
　もちろん行単位で並べ替えて支障ない表もありますが、実はこの表の場合も、青色に配色したセルは見出し（タイトル）であり、B列のナンバーは動いて欲しくないのです。

　そのためには、やはりセルの範囲を指定して並べ替えた方が良いですね。
　範囲の指定は、C3〜F10の薄黄色に配色したデータセルのみ指定する方法と、それに行2の「商品名」〜「金額」の見出しを加えたC2〜F10のセルを指定する方法と2通りあります。

　いずれも結果は同じで、下の右の表のようになるのですが、「商品名」〜「金額」の見出しを範囲に加えた場合は、下の左の画像のように、「範囲の先頭行」は「タイトル」にチェックを入れる必要があります。

　範囲の先頭行は、行２で、「商品名」〜「金額」の見出し（タイトル）であり、「最優先されるキー」の欄に「金額」、「２番目に優先されるキー」の欄に「数量」が選べるようになっており、「列F」、「列E」と表示されるより解りやすいですね。

　一方C3〜F10の薄黄色のデータセルのみ指定した場合は、もちろん「範囲の先頭行」は「データ」にチェックを入れ、このときの並べ替えのダイアログボックスは上の画像と同じになります。

　なお、B列のナンバーも含めて範囲を指定しては、B列のナンバーまで動いてしまうので、範囲に含めてはいけないことは、当然のことです。

これまで行を上下方向に並べ替える説明をしました。
　次は列を左右に移動させ並べ替えてみましょう。

列を並べ替えるためには、並べ替えダイアログボックスの左下「オプション」をクリックし、上の右の画像の「並べ替えオプション」をまず表示させます。この「並べ替えオプション」の中段の「方向」を｢列単位」をクリックして選び、「OK」をクリックして閉じます。

　この後は、元の並べ替えダイアログボックスに戻って、「最優先されるキー」が行を選べるように変わっていますので、後はこれまで説明したことと行と列が違うだけで同様の方法で扱えます。

　サンプルの「自動車購入価格比較表」は、行を上下に並べ替えても意味がないですね。
　並べ替えるとすると、列を左右方向に並べ替える、つまり支払い額合計の少ない順に左側から並べると解りやすい表になります。

　具体的には、範囲の指定は、D3〜H24のデータセルとして、「最優先されるキー」は「行22」の支払い額合計を選び、「昇順」を選択して並べ替えたものが下の表です。

　結果は、最も支払い額合計の少ない「NOTE」が一番左側に来て、最も支払い額合計の多い「カローラランクス」が一番右に来ています。

サンプルの表は小さな表ですが、大きな表になれば、並べ替えて順番が解ることで随分役に立つ場合があります。このサイトの「ADSLコーナー」のファイルも、多くのデータを収集して集計したり、グラフ化したりするために並べ替えを多く用いています。
　並べ替えるだけで、傾向など実態が把握できることもありますから是非使ってみてください。

＜参考＞自家用車購入価格比較表で使用している数式の説明

このファイルは、表を見やすく使いやすくするための説明ファイルですから、サンプルの「自家用車購入価格比較表」で使用している数式については参考までに簡単に紹介します

　以下の各行の数式は、数式の入力されている先頭列（列C）の数式を載せています。

なお自動車税は排気量による区分が多いため長い式になっていますが、IF関数のネストの制限から、この表では排気量3500CC以下の車のみ計算できるという制限があります。

　また自動車取得税は、低燃費車の軽減措置について、現状は18年3月31日までと時限があるため考慮していません。

　関数は、IF関数、SUM関数、ROUNDDOWN関数、INT関数、MIN関数、MAX関数を使用していますが説明を省略します。ここで用いた主な関数は「エクセルIF関数を使おう」、「エクセル数式で計算しよう」、「エクセルなぜ使えないの」というファイルで説明しており、そちらを参考としてください。

この「自家用車購入価格表」をクリックしてインターネットエクスプローラで開いて、ファイルメニューの「Microsoft Excel for　Windowsで編集」をクリックすると、エクセルのファイルとして使うことができます。もし車を購入することを検討されているのであれば、自由にデータを入力して使ってみてください。

3　入力規則プルダウンリストの使用

セルへの文字や数字の入力は、基本的には制限なく自由に入力することが可能ですが、データの入力規則を設定して制限することができます。この制限は、セルの書式設定で、文字、数値、通貨、％などセルに入力する表記を整えるための書式ではなく、まさに条件に合う値しか入力できないように入力規則として制限を課しています。

　この入力規則が不便であれば、使わなければ良いのですが、例えば20〜30の間の数値しか入力をしないセルであれば、そのように入力制限を掛ければ、ほかの数値や文字を入力しようとしても「入力した値は正しくありません」とホップアップ表示がされて、入力ができなくてキャンセルせざるを得ないため、入力間違いを防ぐ強力なエラーチェック機能となります。

　そして入力規則の使い方で最も便利なこと、つまり入力の手間を省くために役立つことは、入力規則で限られたリストから選択することで、文字や数値の入力の手間を省くことでしょう。

上の表は、「エクセル複数シートの活用」でサンプルとして説明した簡単家計簿で、この表ではVLOOKUP関数を使用することで、文字の入力を省いています。

つまり、上のシートのC列の区分の欄には入力は不要で、右の「区分」という名前のシートのリストの範囲のA列の番号を、上のシートのB列のセルに入力すると、「区分」シートを参照して入力した番号と一致するB列の文字が表示されるようなっています。

　例えば、0なら収入、1なら食費、8なら交通・通信というように、上のシートのB列のセルに番号を入力するだけで「区分」シートを参照することで文字入力の手間を省いています。

　このように、項目の数が多いときは、別シートに参照リストを作成して、VLOOKUP関数を使用して参照する方法が便利ですが、項目の数が少ないときは、入力規則で定めたプルダウンメニューのリストボックスから入力データを選択することで、入力する手間を省く方法もよく使われています。

　そこで、上の表のB列の番号を入力するNO欄を削除した下の表で、この区分の列に入力規則のプルダウンリストを設定してみましょう。

上の表では、区分の欄がB列となっていますが、このB列の入力用の複数のセルを選択して、メニューバーの「データ」をクリックして、プルダウンメニューの「入力規則」をクリックすると、左下の「データの入力規則」の設定画面が表示されます。

この左上の画面の入力値の種類のボックスには、初期設定で「すべての値」となっていて、入力制限はかかっていないので、文字でも数値でも自由に入力することができます。

　この入力値の種類のボックスの右横の▼ボタンをクリックすると、右上の画面のプルダウンメニューが表示され、その中の「リスト」をクリックして設定を行います。

続いて左上の画面のように、元の値のボックスに、選択リストに表示される文字を入力します。この例では、「収入,食費,住居・光熱費,家具・家事用品,被服・履物,保健医療,交通・通信,教養娯楽費,その他」と9区分の文字を入力しています。

　この文字や数値の入力の際に注意することは、間のカンマ「,」は半角で入力する必要があることです。

　このように元の値のボックスに、文字や数値を直接入力すればよいのですが、この簡単家計簿の「区分」シートのように別にリストがある場合は、右上の画面のようにリストを参照するように設定することもできます。

　この例での元の値のボックスには、「=INDIRECT("区分!b2:b10")」と入力していますが、この式は「区分」シートのｂ2セル〜b10セルを参照して、これらの参照先の別シートのセルの値を表示させるためにINDIRECT関数を使用しています。

　わざわざ別に参照リストを作成するメリットは、変更するときにリストを修正すればよいので簡単という面がありますが、いずれにしても数多くの選択項目があればプルダウンリストから探すのは面倒です。

そして入力規則の設定が終わって上の表になります。この入力規則が設定してあるB列のセルは解り易く黄色に着色しており、B列のセルをクリックし選択すると、そのセルの右横に▼ボタンが現われ、この▼をクリックすると上の画面のようにプルダウンリストが表示され、この表示されている項目をクリックして選択することができます。

　ただし、この簡単化家計簿の区分では、プルダウンリストに「収入」〜「その他」までの9区分を表示するのにスクロールバーが使われており、一目で全ての選択項目が確認できないことは少し不便であり、やはり表示される項目としては多すぎるでしょう。

上の表のように、VLOOKUP関数を使用しなくても同じような表ができましたが、選択項目が少ないときは入力規則のプルダウンリストを使い、選択項目が多いときはVLOOKUP関数を使用するというように使い分けると、完成した後で使い易いでしょう。

おわりに

この「エクセル簡単テクニック」は、説明用の画像を多く用いたため前編と後編にファイルを分割しました。まだ「前編」を読まれていなければ、表を見やすくするためのテクニックを説明している「エクセル簡単テクニック前編−セルの書式設定」に目を通されることをお勧めします。

★　My Free-style PC サイト内関連ファイルのご案内