関数を使いこなすなら3つの参照方法をマスターせよ！！　VLOOKUP関数とも相性バツグンな基礎知識

今回は、Excelの基礎編第4弾です。

今回は参照(さんしょう)について知っておきましょう。

前回の第3弾から読みたい場合は、下のリンクをクリックして下さい。

Excel初心者でも大丈夫！！　自動計算機能は関数で実現出来る　四則演算とVLOOKUP関数でバッチリ決めろ！！

参照とは、簡単に言うと「セルの範囲を指定する方法」のことです。

とは言っても伝わりづらいですよね。

この参照というのは、VLOOKUP関数を使う時やリスト機能を使うときなどに特に重要になってくる機能です。

最初は難しく感じるかもしれませんが、順番に覚えていけば良いので慌てずに少しずつコツを押さえていきましょう。

それでは、早速本編へLet’s Go!!!

そもそも参照って何？

参照とは、主に関数などでセル(セル範囲)を指定することです。

セルの参照には3種類あります。

相対参照(そうたいさんしょう)・絶対参照(ぜったいさんしょう)・複合参照(ふくごうさんしょう)の3種類です。

なんだか難しそうですが心配はいりません。

難しそうなのは文字だけです。では、ひとつづつ見ていきましょう。

相対参照(そうたいさんしょう)とは？

相対参照とは、行列の範囲を柔軟に変更してくれる参照方法のことです。

例えば、『=SUM(B1:C1)』という式が『A1のセル』に入力されていたとしましょう。

これが『B2～C10』まで計算する対象があった場合、A1のセルをオートフィルでコピーすると、『=SUM(B2:C2)・ =SUM(B3:C3) ・ =SUM(B4:C4)… 』といったように、行数を変更してくれます。

また、列の場合も同じで、右側にフィルコピーをした場合は、

『=SUM(C1:D1)・ =SUM(D1:E1) ・ =SUM(E1:F1)… 』といったように行列方向に柔軟に参照範囲を変更してくれるというのが最大の特徴です。

この相対参照の長所は、参照する範囲が柔軟に変わってくれるところでしたね。

逆に言うと、参照範囲がずれることで結果が変わってしまうとも言えます。

とはいえ、言葉だけでは分かりづらいので図解にしてみましょう。

今回は、「カニ漁」に例えてみます。

《漁師》を「参照元(関数のセル)」・《網が届く範囲》を「参照範囲(合計範囲)」・《海流》を「参照方法」としてみました。

図解の左側には、A1のセルに「=SUM(B1:C10)」という式が入力されています。

合計する範囲は、網の届く「B1のセル～C10のセル範囲」なので今回の場合は、10行×2列=20セルにカニが1匹ずついるので、20匹のカニが捕れることになります。

今夜の鍋は、豪勢なことでしょう！！　ヤッホー！！

次に「A2のセルにフィルコピー」するとどうなるでしょう？

そう、図解の右側のように「=SUM(B2:C11)」となり、船の位置がズレてしまいますね。

でも船から投げられる範囲は変わらないので、「9行✕2列」の範囲にしかカニがいないので、取れる量は18匹になってしまいます。

この作業をどんどん繰り返していくと、取れるカニの量はそのうち「0」になってしまいますね。

これは、列方向にズレても同じことが言えるので、ちょっとした違いなのに結果は全然変わってしまいます。

これを「計算」という頭に戻すと計算ミスになります。

つまり、参照方法を正しく理解していないといくら関数を使って簡単に計算出来るように設定しても意味が無くなってしまうことにもなるのでキチンと抑えておきましょう。

絶対参照(ぜったいさんしょう)とは？

絶対参照とは、参照範囲を絶対に動かさないでね。という固定をするための参照方法です。

SUM関数で表現すると「=SUM($B$1:$C$10)」と表現します。

このようにセルの範囲である「A1:A10」の記号や数字の前にそれぞれ「＄」マークをつけます。

ちなみに絶対参照を設定するときは、例えば「A1」と入力した時に「F4キー」を押しましょう。

手動で「$」マークを押しても良いんですが、間違いを防ぐために慣れておくと勝手に絶対参照にしてくれるので便利なんです。

もし、設定のときに「F4」キーを押しすぎてしまったときは、慌てずに絶対参照に切り替わるまで何度か押していれば大丈夫です。

これは、言ってみれば、船の碇(いかり)のようなモノです。

相対参照とは違ってフィルコピーをしても合計される範囲は変わらないので、計算ミスをすることはなくなりますね。

もちろん。列を変更しても参照範囲が変わることはないのでどこでも同じ金額が表示されるようになります。

本当の漁だったら毎日安定して捕れるので最高かもしれませんね。

複合参照(ふくごうさんしょう)とは？

複合参照とは、行か列を固定する参照方法です。

相対参照でも絶対参照でも対応出来ない場合に活躍します。

たとえば、A～Z列の範囲は固定したいけれど、2行目以降も同じ範囲で合計したい時などに使います。

いわゆる応用した使い方になるので、たとえばこんな風に設定します。

『=SUM(A$1:$A10)』相対と絶対参照の中間ですよね。

この場合は、1行目とA列を固定しています。

今回の例では、結果、「B1～C11」の範囲に広がりました。

結果、カニが捕れる量に変化はありません、ただ1つ注意点があります。

それは、下に伸びた先に数字が含まれていると、その数字も足してしまうことです。

これも間違いの原因ですね。

参照範囲の設定は、関数や四則演算を始めExcelの肝になる仕組みなので、シッカリ抑えておきましょう。

この複合参照は、どんな時に使うんでしょう？

例えば、Excelでカレンダーを作るときなどでしょうか。

カレンダーを作ると言っても案外難しいんです。

固定された1年間のカレンダーを作ることは比較的かんたんなんですが、1ヶ月づつ10年の分のカレンダーを自動的に切り替えて表示してくれるようなものを作らなければいけない場合、色々な応用をしないと意外と作れないものです。

基本的には、相対参照をシッカリ押さえて絶対参照を理解しておく、慣れてきてもう少し難しいことが出来るようになってから、試行錯誤した結果使うことになる参照方法です。

参照方法をマスターするメリットって何？

参照方法をマスターするメリットは、こんな場合です。

約1,200件の店舗がある企業に務めていると仮定しましょう。

その中の「月間売上額」と「順位」をVLOOKUUP関数を使って表にまとめる必要があったとします。

月間売上額のVLOOKUP関数の式を数式バーで見てみましょう。

『=VLOOKUP($G2,$C$2:$E$1200,2,0)』という式が入力されています。

ここでは、「$G2」は複合参照になっています。

ここで肝になるのが、「G列」を固定しておくことです。

G列には、店舗名が入っていますよね。

売上と順位を求めたいので、店舗名を元に「売上の列と順位の列」それぞれから数字を引っ張ってきたいと言うのが今回の目的です。

これを日本語で表現すると、「〇〇店の売上列から金額」を「順位の列から順位」をそれぞれ調べて表示してね。ということになります。

今回のケースは、こんな仕組みになっています。

月間売上額の行数が変わっても、順位の列になっても参照している行列は固定されているので、ほぼ同じ内容の関数を設定してしまえば、サクッと間違えること無く表示してくれます。

応用がきくようになれば、設定次第で自動化が出来るということですね。

検索する値は、複合参照で柔軟にして調べてくる参照範囲は絶対参照で固定するこの使い方ができるようになると、Excelの活用の幅が広がるんだなというイメージを持ってもらえればOKです。

実際に触ってみたいなと思ったら、サンプルデータを置いておくのでダウンロードしてみて下さいね。

まとめ

どうでしたか。参照方法について上手く伝わったでしょうか？

参照方法は、全部で3つありますがほとんどの場合関数と組み合わせることが多いです。

特にVLOOKUP関数などの検索も目的にしている関数との相性はバッチリですね。

参照方法を把握しておかないと、データが入力された表のちょっとした変更や

検索したい項目が増えた場合、計算をいちいちやり直さなくてはいけなかったりすることで、間違いが増えるという本末転倒なことにもなりかねません

最初は、慣れるのに苦労するとおもいますが、まずは特徴を捉えましょう。

相対参照がどんなものかが分かってしまえば、他はそんなに難しくは無いんですよ。

あとは、どう組み合わせるか？　どうすれば応用がきくのか？　ということを考えて使っていると、ある日ハッと気づく時があったりするものです。

少しずつでいいので、参照方法には慣れておきましょう。

最後まで読んでいただきありがとうございます。

それではまた、お会いしましょう。　SeeYouAgain!!