例題を使って、色々な関数で遊ぼうのコーナー！
今回は1年間のエサ代集計を題材に表を作っていきます。
各種データについて
まず、今回のお題の各シートについて。
各月のシート
1～12のシートがあり、各シートにその月の氏名とエサ代が入力されています。
毎月、全員の名前があるとは限らず、今回は3月と4月でアシカ吾郎とアザラシ聖子を不在としてます。
集計シート
各月のシートからエサ代を読み込み、色々な集計をして遊ぶためのシートです。
全員分の氏名と、項目名だけを用意したところからスタートします。
集計開始
途中、失敗例を挟みながら作業を進めていきましょう。
エサ代を読み込む
まず、各月のエサ代を読み込みましょう。
今回はVLOOKUP関数を使って読み込みます。
VLOOKUP関数についての解説はコチラをご覧ください。
式：=VLOOKUP(A5,'1'!A:B,2,FALSE)
これで、セイウチ太郎の1月のエサ代、10万円が読み込めました。
ではこの式を、オートフィルを使ってセイウチ太郎の1月～アザラシ聖子の12月まで反映させましょう。
オートフィルとは、元になる式を用意したら、あとはドラッグするだけで式を広範囲に展開できたりする便利機能です。
一気に最後までオートフィルできないので、まずは縦にB5～B8セルまでオートフィルで展開し、B5:B8が範囲選択されたまま、更にM列までオートフィルで展開しましょう。すると・・・
上手くいきませんね、原因を探るためにC5セル（セイウチ太郎の2月）がどうなっているか調べてみましょう。
セイウチ太郎2月の式：=VLOOKUP(B5,'1'!B:C,2,FALSE)
下記の3つの問題が見つかりました。
- 検索値が氏名（A列）ではなくB列にズレてしまった
- 2月分のエサ代を表示したいのに探しにいくシートが1月のまま
- 検索先の範囲もA:BがB:Cにズレてしまった
これを１ずつ解決していきましょう。
読み込み時の問題を解決していく
3つの問題を1つずつ潰していきましょう。
検索値がA列からB列にズレてしまう対策
これは、検索値を絶対参照で固定することで解決できます。
検索値（氏名）は、縦方向（1，2，3，4行目）には変わってほしいですが、横方向（A、B、C、D列）には変わってほしくありません。
ですので、A5のAの前にドルマークを着けて、Aを固定します。
直接ドルマークを入力しても良いですが、式内のA5部分を選んでる時にF4キーを押すと、$A$5→A$5→$A5の順に切り替えられます。
式：=VLOOKUP($A5,'1'!A:B,2,FALSE)
探しにいくシートが1月のまま
次に、エサ代を探しにいくシートが1月シートから変わらない問題を解決します。
今回は項目にある月とシート名が同じになるようにしているので、’1’!の部分を、セル参照するようにします。
式：=VLOOKUP(A5,'B4'!A:B,2,FALSE)
一見、シート名にB4セルの1を使おうとしているように見えますが、これではダメです。
理由はここでは省きますが、INDIRECT関数を組み合わせる必要があります。
式：=VLOOKUP($A5,INDIRECT("'" & B$4 & "'!A:B"),2,FALSE)
このように、INDIRECT関数の中で、'B$4'!A:Bという文字を組み立てるようにします。
見にくいですが、この時シングルクオーテーションを付け漏れないように注意しましょう。
INDIRECT関数についてや、VLOOKUP関数との組み合わせについては、コチラの記事をぜひご覧ください。
検索先の範囲がA:BからB:Cにズレてしまう
最後に、検索先の範囲がズレてしまう件ですが、何もしなくていいです。
というか、先のINDIRECT関数を使った時に解決できています。
検索値にINDIRECT関数を使ったことで、=VLOOKUP($A5,INDIRECT("'" & B$4 & "'!A:B"),2,FALSE)という式になったわけですが、INDIRECT関数内でダブルクオーテーションで囲まれた部分については、オートフィルをしても変わらないのです。
なので、おしまい。
直した式でもう一度挑戦
ここまでで、VLOOKUP関数の式を直してきましたので、もう一度オートフィルをしてみましょう。
だいぶ良い感じになりましたが、あと一歩ですね。
今回の例題では、3月と4月のシートでは、アシカ吾郎とアザラシ聖子が不在になっています。
そこで、VLOOKUP関数の結果が#N/A（対象が見つかりませんでした）になってしまいましたね。
VLOOKUP関数の#N/A対策
それでは最後にもう一押し、VLOOKUP関数に手を入れていきましょう。
先ほど作ったVLOOKUP関数の式を、IFERROR関数で囲みます。
式：=IFERROR(VLOOKUP($A5,INDIRECT("'"&B$4&"'!A:B"),2,FALSE),"")
これで、VLOOKUP関数でエラーになった場合は""が、つまり空欄が表示されるようになりました。
今回のように、#N/Aなどのエラーを表示させない方法についての詳しい内容はコチラ。
今度こそオートフィル成功
IFERROR関数でエラー対策もしたら、もう一度オートフィルで式を展開しましょう。
色々ありましたが、今度こそ完成ですね！
いろいろ集計
ようやく、基になるデータが揃いましたので、いろいろ集計して遊んでみましょう。
今回は合計、平均、順位、最大、最小の５つを求めてみます。
合計 SUM関数
SUM関数で数値を合計してみましょう。
使い方は簡単で、合計したい範囲を指定するだけです。
月合計の式：=SUM(B5:B8)
個人合計の式：=SUM(B5:M5)
N8セルは総合計になるわけですが、月合計や個人合計の延長線で式を作るのも良いですが、=SUM(B5:M8)のような範囲指定のやり方もあります。
平均 AVERAGE関数
平均値を計算するのには、AVERAGE関数を使います。
式：=AVERAGE(B5:M5)
セイウチ太郎の年間累計は178なので、12カ月で割って、平均は14.833・・・となります。
今回、アシカ吾郎とアザラシ聖子は３，４月が空欄になっていますので、この場合は10カ月で割るようになります。
ここは好みが割れるのですが、12カ月で割りたい場合はAVERAGEA関数を使うことで、空欄があるセルも分母に含んでくれます。
ややこしいですが、空欄があるのと、なんの式も入力されていない無では扱いが違い、無の場合はAVERAGEA関数を使っても分母に含まれません。
他にも、IFERROR関数の式で、エラーの場合の値を""から0に変更し、AVERAGEIF関数で0超の場合のみを対象にして、0を表示しつつも10カ月で割るといった方法もあります。
アシカ吾郎のIFERROR式：=IFERROR(VLOOKUP($A7,INDIRECT("'"&B$4&"'!A:B"),2,FALSE),0)
アシカ吾郎のAVERAGEIF式：=AVERAGEIF(B7:M7,">0")
順位 RANK関数
指定した値が、範囲内で何番目に大きいかを求める事ができる、RANK関数を使います。
今回は、年間累計が大きい順に順位をつけてみます。
セイウチ太郎の式：=RANK(N5,$N$5:$N$8)
順位を知りたい値、範囲の順に設定するのですが、オートフィルで他の人にも式を展開する際、順位を知りたい値はもちろん変動させたいのでドルマークはつけません。
ですが、範囲は固定させたいので、絶対参照にして動かないようにしておきます。
また、式の最後に,1を付けることで、小さい順の順位を求める事もできます。
小さい順を求める式：=RANK(N5,$N$5:$N$8,1)
最大 MAX関数
MAX関数を使って、その人の1年間のエサ代の内、一番エサ代が高かった月の金額を求めてみましょう。
セイウチ太郎の式：=MAX(B5:M5)
範囲を指定するだけなので簡単ですね。
最小 MIN関数
今度はMIN関数を使って、その人の1年間のエサ代の内、一番エサ代が低かった月の金額を求めてみましょう。
セイウチ太郎の式：=MIN(B5:M5)
MAX関数同様、こちらも範囲を指定するだけなので簡単ですね。
番外編：ミニグラフ（スパークライン）
あまり注目されることがないのですが、エクセルには、セル内にミニグラフを表示させる、スパークラインという機能があります。
セル内に表示させる以上、どうしても小さいグラフになってしまうので、細かい情報までは拾えませんが、なんとなくの数値の傾向を見たりするには便利です。
あと、なんか楽しい。
使い方は簡単で、挿入タブ→スパークライン→表の種類の順に選び、最後にデータの範囲を選んであげるだけで完成です。
実戦から得るものは多い
今回のようなシンプルな集計表だけでも、多くの関数が登場しました。
この事から、使いたい関数を探すのではなく、実際の表から、こういう関数や使い方もあるんだぞと逆引きするのも有りなんじゃないかと思い、勉強用に記事を書いてみました。
何か１つでも、あーなるほどねって思ってもらえれば幸い。
コメント