こんにちは、シャイ丸です。GW中はあまりにも暇だったので長文結構書けたのですが仕事再開し忙しくtwitterもブログも中々書けていませんでした。前編だけ書いて放置していたエクセル術ですが反響も驚異的に大きかったので後編書いてみようと思います。

後半で書く予定であったのはこの３つです。これくらい出来ないと人間ではない。獣（ケダモノ）である。

・関数

・ピボットテーブル

・グラフ化

前編も含め6つのトピックを書きましたがこれが出来れば晴れてホモ・サピエンスです。ホモ・エレクトスのみなさん、キーボードを叩いて日々精進。

www.shiningmaru.com

4.関数：兎にも角にもvlookup!

そもそも「関数」という概念に不慣れなそこのあなた、生き恥をさらしているのでここで覚えましょう。インプット（引数）があって関数（処理）を経てアウトプット（出力）を出す。これが関数。関数にインプットを与えればなにか出てくる、生物に食事を与えればなんか出てくる、バカに仕事を与えても何もアウトプットが出てこない、おかしいなぁ。

さて、実際に重要な関数を使いながら学んでいきましょう。

vlookup

頻出関数、vlookupです。どのような状況で使うか、これはIDが付与されたマスターデータ（前編で述べたようなフォーマットに乗っ取る）を参照しながら新たな分析を作る際に良く使います。下図のような状況を想定して下さい、黄色でハイライトされた部分が上司に埋めてくれと要求された部分です、実際はIDの列は実際に見るのは「原価テーブル」などで縦に非常に長い場合が多いのでそのまま使うのは不便です。そのときにvlookup!対象のIDのデータのみ参照可能です。

ここで活躍vlookup、IDが分かればなんと黄色でハイライトされた部分は関数のみで埋めることが出来るのです、幸せ。

「=vlo」まで入力しましょう。すると候補となる関数が表示されます。その中にvlookupがあります、tabを押してみましょう。vlookupが選択されたと同時に入力すべき値が表示されます、親切な設計ですねー。vlookp(検索値、範囲、列番号、検索方法）となります、右側に表示されているのが上述の引数、要はインプットデータです。これをカンマ区切りで入力すればvlookupは機能するわけですね。

検索値とはなんでしょうか、

これは「ID」です。vlookupとしては元のテーブルを見たいという願いがあるのですが「どこを」と思います、vlookupに対して「これを見ろ」というのが検索値になります。そしてこれはデータの左端になくてはなりません。この値を見ろ、という第一引数に入れる値は図で言えば「分析テーブル」の2が入力されているセル、要は要求したテーブルから「２」を探せという命令になります。範囲とは検索するテーブル全体を指します。ここでは赤線でくくられた表を選択することになりますね。選択しましょう。

関数

出来ましたか？出来ると上手のようになります。ここでは「２を検索しろ、指定した範囲のテーブルから」と要求するまでは済んでいます。次はコンピューターは「２を検索してその行を発見した、どの値を取り出せばよいのだ」と疑問を持っています、指定してあげましょう。指定してあげないと何も出来ないのがコンピューター、でも指定してあげると人間を遥かに凌ぐ能力を発揮するコンピューター、ああ可愛い、ありがとうウィリアム・ショックレー。関数を完成させます。

上記関数の結果は？

お見事！ID２（パッソ）の平均原価が入りました、「列番号」の3て何、と思いますよね、これは参照値（ここではID）から見た右へのセル番号です。1で指定するとID２が入ります、一番目に見るセルですから二番目であればID２で「パッソ」、３番目で平均原価の500となるわけです。この調子で他のセルも埋めていきましょう。他のセルもイチから埋めるか？当然違います、コピーします。

あれれー蘭ねぇちゃん、この「範囲」引数に追加されたドル（＄）て何ー？

コナン君、これはね、数式で使う値をセル指定したときに数式セルが移動しても参照セルを固定する技なんだよ。すごーい欄ねーちゃん（欄・・・）。

そうなのです、実はエクセルの数式はコピーして貼り付けると参照セルは絶対参照ではなく相対参照になるため想定した動作とずれてしまうのです。ここでは例えば・・・下図のように＄を付けない状態で貼り付けをしたとしますね、４でも同じことをやろう！と思ってctrl+cからのctrl+vをした場合

してみた

このようになってしまいます、何がずれているかわかりますか、本当は参照して欲しい表は変わらないのにセルが一つしたにずれているので連動して参照する表（緑の枠）もずれています。これはいかんですよね、これを防ぐのが＄マークです。＄をつければその次に来る値（例えば上のB15の15前に$をつけてB$15にすれば・・

上図のように「範囲」を$を用いて固定しています。するとvlookupをどのセルにコピーしても参照する表の範囲は変わりません。これで同じマスターデータを用いた分析が出来ます。ID２で平均原価に作ったセルをコピーしてみましょう。

指定している範囲は同じになっており想定した通り探すIDがずれています。IDが大量にあってもこれでコピペだけで平均原価を参照出来ますね。

さて、この＄マーク実はベタ打ちでなくてもF4で出現させることが可能です。

このようにセルを指定している部分にカーソルをあわせF4を押してみます。

何が起きたか気付きますか？$がBの前にも出現しました。英文字は横のセルを指定しています。つまりここではこのセルを別のセルにコピーしても横のBセルは固定されます。これを固定しない状態でC列にコピーしたりするとBからCへ変わるので想定外の動作をします。読むだけでは実感しづらいので是非やってみてください。

関数についての論議ですがvlookupでだいぶ長くなってしまいました。ちなみにこのvlookup一発で様々なテーブルが統合出来るのは何故出来るのか、それはIDが統一されているからです。これが事業会社に行くとあるある、イタリアではZX1、日本ではZX5iと呼ばれている同じ部品が。もはや人間の目視でテーブルを統合するしかありません、この際には目視で統合するためvlookupではなくilookup=i look up(私がやります)、つまり人力統合となりコンサルタントと呼ばれる民族は死罪に処されます、もといルーティンですね。こういった、嘘だろ・・・が存在しない事業会社などありません。覚悟して下さい。

vlookupが長すぎたので他の関数は自分で練習して下さい（雑）、count、sum、sumifs、powerあたりいじってみましょう。

5.ピボットテーブル

ピボット？なんだバスケかと思ったそこの君、違うぞ。先ほどの表を例に学んでいきましょう。

 

先ほどのデータに座席数を追加しました。ここで上司から意味はわからないけど「おい、君今2シーターの平均原価を出してくれないか」と言われたらどうしますか。2のものを指定してaverage関数で算出しますか？そんなスマートでないことをしてはいけない、4,7とか増えたらその度にその処理をするのかい？君はそんなことはしなくていい。範囲を指定してピボットテーブルを組むだけでいい。え、どういうことかって？

リボンの「挿入」をクリックすると左端に「ピボットテーブル」という謎のアイコンが表れる。

エクセルの所作通りに横軸（ラベルが振られているところ含め）と縦軸を選択、分析を来ないたい表の範囲をハイライトする。そしてピボットテーブルをクリック！

 

 

謎の画面が現れました！これでOK。同時に右端に下図のような表示があることが確認出来るだろう。

「なにこれ」と最初は思うだろう、主に使うのは「列ラベル」以外の３つだ。

・レポートフィルタ

要は使うデータを限定します、ということです。２シーターのデータしか使わないということであればここに「座席」をドラッグ＆ドロップしてみて使ってみよう。

・行ラベル

縦軸を規定します。ここでは座席をドラッグ＆ドロップしてみましょう。

・値

行ラベルで縦軸を定義したら次は横軸に表示する値はどうするか？これを規定するのがこの「値」欄です。平均原価をドラッグ＆ドロップしましょう。

するとどうだ、平均原価がめでたく表示されました！ちなみにここで記載されているのは「単純平均」であり、「荷重平均」でないことに注意（＊次回の記事でこの差は記載します）。

これでシーターがいくつになってもさくっと分析が出来るわけですね。vloopupとピボットテーブルができればエクセルはとりあえず半人前にはなれます、みんな頑張って。

6.グラフ化：とにかく綺麗に、意味を明確に

 さて、分析が出来ました。分析結果をエクセルの表のまま印刷してマネジメントレポートをしようもんなら評価は半減どころではなく10%以下になるでしょう。セルに入った数値の羅列を見せられたところで伝えられた人間は何をすればいいのかわからないからです。ここは語りたい部分が多いので最終章持ち越しにさせて下さい！