Excel操作のコツ（6）
ひろです。
さて、続けて各種コツ・テクニックの紹介をしていきたいと思います。
～ここから過去の記事～
その前に改めて、頻繁に使用する基本機能を整理しておきたいと思います。
Excel
フィルター機能：一番上の行を項目として、各列を、たとえば数値の大小にるフィルターや並べ替えが可能
区切り位置機能：1つのセルに複数のデータが入っている場合に、区切りとなる文字等を指定して自動的に各データを複数のセルにそれぞれ格納する
ゴールシーク：特定のセル（計算結果）が特定の値（たとえば100等）をとる時の、別の特定セル（計算式の変数）の数値を計算
データテーブル：マトリクスで感応度分析が可能
データの入力規則：ドロップダウンリスト等を作成可能
PPT
オブジェクトの配置機能：オブジェクトを、たとえば左揃えや中央揃えにしたり、等間隔に整列させたり、といったことができる
透明色の指定機能：ピクチャの特定色を透明化する（たとえば「白」の背景の会社ロゴ等のピクチャの「白」を透明に指定することで、ロゴ部分だけが抽出されたピクチャにすることができる）
ピクチャのトリミング機能：ピクチャの不要な箇所を隠して表示するようにする機能
この辺りは頻繁に使いますね。各機能の詳細なご紹介は他の書籍やサイトにお任せしようと思います。
では続けていきましょう。
事業計画等のケース分けを上手に作ろう！
さて、事業計画でも何でもよいのですが、「様々なケースを分岐できるようにしたい！」なんてこと、ありますよね。
そんな時には、
①まずは纏めやすい位置にパラメータを集めて通常通りに計算式を作成
②そこから必要なケース分をコピペ+パラメータを設定
③choose関数等でケース分岐を可能にして完成！
となります。
まあ、こんなのは、イメージを見た方が早いですね。
ここまでは普通の作成方法ですね。ここからです。
ここまでくれば、あとはケースによって数値が変わるようにすればよいだけです。
これでケース分岐が完成しました！ 意外と簡単に作成可能ですね。
さて、このようなケース分岐を作成していると、「途中でパラメータを追加したい」「途中でパラメータを削除したい」といったことがあります。
excelにおいては、「追加する時はリンクの最終的な元から」「削除するときはリンクの最終的な先から」というルールに従えば問題ないと思います。
すなわち、パラメータを追加するときは、各ケースで当該パラメータ部分を作成→上のchoose関数の部分を作成（同じ位置関係にしている限り、行を丸ごとコピペするだけでOK）→当該パラメータを用いるよう、業績部分の数式を変更 という流れになります。
パラメータを削除するときは逆に、当該パラメータを用いている業績部分の数式を変更→choose関数の部分を削除→各ケースの当該パラメータ部分を削除、という流れが「#REF!」等を生じさせることのない、綺麗な削除になるかと思います。
事業計画等のケース別の数値をとっておこう！（比較グラフ等を作ろう！）
さて、上記のようにケース分岐を作成した場合、「売上グラフを作成したいが、Base caseのみではなく、全てのケースを比較可能にするため1つのグラフにまとめたい」なんてこと、ありますよね。
かといって、まさかBase caseを選択→売上を「値」で貼り付けてとっておく→Upside caseを選択→売上を張り付けておき…なんて手間暇のかかること、やっていられません。
そんな時は、「自己セルを参照」させることで、特定ケースの数値を、仮にケースを変えた場合であっても、維持する、というテクニックが使えます。
具体的には、たとえばケースAの売上高（たとえば1,000円）を表示させたいセルには、「もしケースAであれば売上高にリンクし、そうでなければ自己セルを参照する」という数式を組みます。
これによって、「ケースAを選択している場合は売上高1,000円へのリンクから1,000円を表示し、そうでなければ1,000円を表示している自己のセルへのリンクから1,000円を表示する」ということができるようになります。
なお、この際は、Excelの「ファイル」→「オプション」→「数式」にある「反復計算を行う」にチェックを入れるようにして下さい。
では具体例のイメージを見てみましょう。
これで準備完了です。
これで、見事にケース分岐を1つのグラフで表示・比較できるようになりました！ もちろん、他の項目等にもどんどん応用可能ですので、これで大抵は対応できるかと思います。
さて、だいぶ便利な機能ではありますが、「一度、各ケースを全て選択しないと、数値は更新されない」点には留意が必要です。
たとえば「各ケースのパラメータを変更した」ような場合、一度Base/Upside/Downsideを全て選択してグラフに使用している数値を更新しなくては、いつまでも自己セルを参照するので古いパラメータ設定のままのケース数値になってしまいます。
頻繁にパラメータが変わるような場合は、「グラフの数値はちゃんと更新したっけ？ していないっけ？」と混乱してくることも考えられるので、そのような場合のために、たとえばnow関数と自己セル参照を組み合わせることで、「各ケースの数値は何時何分に更新したか」も維持しておくようにすれば、更新漏れを抑止することができるようになります。
「Ctrl + →/↓」の動きこそcontrolしよう！
これはまあ、ちょっと作業を楽にする工夫、といったものです。
Ctrl + 矢印キーで手早く移動できることはご紹介していますが、特に「→」と「↓」については、意図せず、XFD列や1048576行にすっ飛んでしまいがちです。
もちろん逆の矢印キーを押せばすぐに戻ることができるのですが、やはり「ほんのわずかなタイムロスと精神的な微疲労」に繋がるため（笑）、すっ飛ばないようにする工夫があります。
これはまあ、見た方が早いですね。
L列をご覧下さい。以上です。
…まあ、原始的な方法です。「*」をずらーっと並べて配置しておくことで、すっ飛んでいかないよう「壁」を作っています。
K列を空けたのは、業績数値部分を編集している時にはJ列で止まりたいからです（K列に「*」があると、K列で止まるので業績数値部分を編集しづらい）。
これを応用すると、たとえば以下のように、A列の任意の位置で止まるようにしておくことで、編集しやすくすることもできます。
たとえばこんな風ですね。A22セルにも、すっ飛んでしまわないように「*」を入れておきます。
こういった工夫でも、だいぶ作業が早くなります。
テーブルに1行おきに色をつけて見やすくしよう！
テーブルを作成していて、項目数が多いと、「自分が正しい行を見ているのか分からない！」なんてことになる場合、ありますよね。
たとえばこんな感じの場合です。
たとえばF7セルにある「◆◆◆◆」を先に見て、即座に「対象Eのことだ！」とは分からず、対象DやFに見えてしまうこともあるかと思います。
そんな時には、1行おきに色をつけたい所ですね。とはいっても、まさか力技で1行1行、手作業で色をつけるわけにもいきません。手間暇がかかりますし、行を後で追加／削除した時にはその分ずれてしまい、塗り直しになる可能性も高いです。
そこで、「条件付き書式」の機能を活用します。
下の図を見た方が早いとは思いますが、書式を変更したい範囲を指定した上で、「条件付き書式」の「新しいルール」から、「数式を使用して～」を選択し、数式に「mod(row(),2)=0」と入力します。また、右下の「書式」の「塗りつぶし」から、1行おきにつけたい色を選択します。
mod関数は割り算を行った「余り」を算出してくれる関数で、上の式の意味は「セルは●行にあるが、この行数は2で割ったら余りが0であるか（割り切れるか）」を判定する式で、0である場合に「書式」の「塗りつぶし」が適用されることになります。
今回の場合、一番上にある対象Aが3行目にあり、一番上は塗りつぶしをしたくないため、その下4行目から塗りつぶしされるよう最後は「=0」としていますが、たとえば一番上の対象Aが4行目にあり5行目から塗りつぶしたいような場合は、「=1」として、「1余りが出る場合に」という条件に変更することもできます。
このようにして、無事に1行おきに色がつくようになり、もちろんコピペすれば条件付き書式もコピペされるため、簡単に増やせますし、行が削除された場合も、自動的に色が切り替わるようになります。
列をまたがって文字を表示させよう！
セルの「書式設定」→「配置」→「横位置」→「選択範囲内で中央」、を選択することで、「セルを結合して中央揃え」と同一の見た目で表示できます。
ただし、以下の通り、実は内部の処理としては、当然異なるものになっています。
これが重要なのは、「列を挿入したい／削除したい」場合です。「選択範囲内で中央」であれば狙った位置への挿入／削除は簡単ですが、「セルを結合して中央揃え」ではかなり難しいことになります（一応、マウスでG列を直接クリックすれば大丈夫ですが）。
これも実は、時間の節減の上では結構重要な処理だったりします。
これの行versionの処理があればいいんですけどねー…
～ここまで過去の記事～
というわけで、今回はここまでとします。
ご参考までに、今回使用したExcelファイルをアップロードしておきましたので、必要ならダウンロードして下さい（mustではないと思います）。
ではではまた！
この記事が気に入ったらサポートをしてみませんか？