マーケティング

記事一覧

新入社員ならこれだけはやっとけ! エクセルスキル(中級編)

このエントリーをはてなブックマークに追加
新入社員ならこれだけでやっとけ! エクセルスキル(中級編)

エクセルスキル(基礎編)でご紹介した関数は覚えられましたか?
 
【過去の記事】
新入社員ならこれだけはやっとけ! エクセル関数(基礎編)
新入社員ならこれだけはやっとけ!エクセルスキル(基礎編)
 
今回は中級編として、まず覚えておきたい絶対参照・相対参照と、それらを使った関数(vlookup、sumif)についてご紹介します。
 
vlookup関数・sumif関数はWeb広告の数値分析やレポート作成時、別々の表やシートにある同一のキーワードを基準に、指定したデータを抽出して紐付ける事ができますので、まず知っておかなければならない関数です。
 
関数を覚えることでエクセル業務の作業効率が上がり、その結果新たな時間を作りだすことができます。
是非マスターして下さい。

1. 絶対参照・相対参照について

数値が入っているセルをコピーして別のセルへ貼り付けると、エラー表示 もしくは 空白になってしまったことはありませんか?
それは、実はそのセルに数式が入っており、参照先のセルも同時に移動してしまったためです。
 
数式が入っているセルをコピーして別のセルに貼り付けると、相対的に参照先のセルが移動するため相対参照と呼ばれております。
逆にどこにコピーしても参照先のセルを固定することができます。それが絶対参照です。
 
まず、相対参照の例を見てみましょう。
 
C1に「=A1*B1」と入力し
C1に「=A1*B1」と入力
 
C1のセルをコピーしてC2に貼り付けると
C1のセルをコピーしてC2に貼り付ける
 
C2は「=A2*B2」と変化します。
C2は「=A2*B2」と変化します
 
次に絶対参照の例です。
 
C1に「=$A$1*B1」と入力します。$マークをAの両端に付けてます。
C1に「=$A$1*B1」と入力
 
C1のセルをコピーしてC2に貼り付けると
C1のセルをコピーしてC2に貼り付ける
 
C2は「=$A$1*B2」と変化します。
C2は「=$A$1*B2」と変化します
 
絶対参照は、数式入力時に該当セルにカーソルを合わせてクリックして、F4を押すことによって簡単に付ける事ができます。
 
F4を1回押すと両端に$マーク
F4を1回押すと両端に$マーク
 
F4を2回押すと右端に$マーク
F4を2回押すと右端に$マーク
 
F4を3回押すと左端に$マーク
F4を3回押すと左端に$マーク
 
F4を4回押すと$マークが消えます。
F4を4回押すと$マークが消えます
 
$マークを両端につけると、どのセルに貼り付けても参照元は変わりませんが、右端に$マークを付けると行を固定し、左端に$マークを付けると列を固定します。
 
行固定の例を見てみましょう。
 
C1に「=A$1*B1」と入力し
C1に「=A$1*B1」と入力
 
C2に貼り付けると
C2に貼り付ける
 
C2は「=A$1*B2」に変化します。行は変わっていません。
C2は「=A$1*B2」に変化します。行は変わっていません。
 
C1をD1に貼り付けると
C1をD1に貼り付ける
 
D1は「=B$1*C1」に変化しますが、相対的に参照元が変わっています。
D1は「=B$1*C1」に変化。相対的に参照元が変わっています。
 
次に列固定の例を見てましょう。
 
C1に「=$A1*B1」と入力し
C1に「=$A1*B1」と入力
 
C2に貼り付けると
C2に貼り付ける
 
C2は「=$A2*B2」に変化します。相対的に参照元が変わっています。
C2は「=$A2*B2」に変化。相対的に参照元が変わっています。
 
C1をD1に貼り付けると
C1をD1に貼り付ける
 
D1は「=$A1*C1」と変化します。列は変わっていません。
D1は「=$A1*C1」と変化。列は変わっていません。
 
このように行のみ固定したり、列のみ固定することができます。
 
絶対参照・相対参照という名前は覚えなくても良く、F4で$マークを付けるとズレないようにすることができるという事は確実に覚えましょう!

2. vlookup関数・sumif関数の使う場面と使い方

vlookup関数・sumif関数は、様々な場面で活用できますが、リスティング広告のレポート作成時だと下記のような使い方ができます。

  • Googleのキーワードレポートへ、同じキーワードで運用しているYahoo!の数字データを紐付けたい
  • 先月のキーワードレポートへ今月の数字を紐付けて比較したい
  • キャンペーン、グループ単位で表示回数とクリック数を足し算して比較したい

今までYahoo!やGoogleから出てきたレポートとにらめっこしながら比較していた数値分析が、関数を使う事により一瞬で抽出できるようになりますので、とても便利な関数です。
それでは、各関数について簡単な事例を踏まえてご説明していきます。

vlookup関数ってどんな関数?

vlookupとは、指定した範囲の中から条件に一致したデータを検索し、取り出してくれる関数です。
 
まず、vlookup関数の構文を見てみましょう。
 
vlookup(検索値, 範囲, 列番号, 検索の型)
vlookup関数には4つの引数がありますが、ポイントとなるのは最初の3つで、

  1. どのデータで [検索値]
  2. どこを検索して [範囲]
  3. どの列にある値を取り出すか [列番号]
  4. 引数[検索の型]これは難しいので、0もしくはFALSEと入力すると覚えておきましょう。

を表しています。

vlookup関数はどんな時に使うの?

商品ID、商品名、単価といったデータを持ったリストを事前に作成しておき、見積書などで商品IDを入力すると商品名や単価が自動的に入力されるような時によく使われます。
 
vlookup関数
 
実際に商品名を引っ張る時の数式をいれてみます。
見積書の1行目へ商品ID 「5」を入力し、商品名をリストから引っ張りたい場合の数式は
「=vlookup(B7,G6:I15,2,0)」です。
 
vlookup関数
 
もう少し分かりやすくすると
 
vlookup関数
 
検索値は商品IDの対象セルです。
範囲は商品リスト全体を指定します。
列番号は、商品リストの左から2列目に商品名があるため「2」と入力します。
検索方法は、「0」もしくは「FALSE」と入力します。
 
すると下記の通り「コンパス」と入力されました。
 
vlookup関数
 
続いてD7にも単価を引っ張る数式を入れます。
vlookup関数
 
もう少し分かりやすくすると
vlookup関数
 
検索値は商品IDの対象セルです。
範囲は商品リスト全体を指定します。
列番号は、商品リストの左から3列目に単価があるため「3」と入力します。
検索方法は、「0」もしくは「FALSE」と入力します。
 
そうすると下記のとおり商品IDの番号に応じて自動で商品名、単価が商品リストから引っ張られます。
 
vlookup関数
 
また、絶対参照・相対参照を使用するとコピーペーストするだけで下の行にも同様の数式を反映させることができます。
C7へ「=vlookup($B7,$G$6:$I$15,2,0)」と入力し
 
vlookup関数
 
下まで引っ張ると
 
vlookup関数
 
各商品IDに紐づいた商品名が自動で入力されます。
 
vlookup関数

sumif関数ってどんな関数?

Sumifとは、条件を指定して数値を合計してくれる関数です。
 
まず、sumif関数の構文を見てみましょう。
 
sumif(範囲, 検索条件, 合計範囲)
sumif関数には3つの引数があります。

  1. この範囲の中で [範囲]
  2. この条件に該当しているセルがあれば [検索条件]
  3. この範囲のデータを合計する[合計範囲]

を表しています。

sumif関数はどんな時に使うの?

商品単位で売上日や売上金額がそれぞれ入力されているシートから、日付単位で売上を集計する際に使われます。
 
sumif関数
 
実際に売上データから日別売上の合計値を算出する数式をいれてみます。
C6へ「=SUMIF(F6:F18,B6,H6:H18)」と入力します。
 
sumif関数
 
もう少し分かりやすくすると
 
sumif関数
 
範囲は売上データの日付を選択します。
検索条件は、合算したい日付を選択します。
合計範囲は合算したい金額の範囲を選択します。
 
そうすると下記のとおり検索条件に一致した日付に該当する売上金額が算出されます。
 
sumif関数
 
また、予めC6へ絶対参照・相対参照で$マークをつけておきましょう。
C6「=SUMIF($F$6:$F$18,$B6,$H$6:$H$18)」
 
sumif関数
 
下まで引っ張ると
 
sumif関数
 
日付単位で売上金額が算出されます。
 
sumif関数
 
今回は日付を検索条件で指定しましたが、商品名を指定して商品単位で売上合計を算出することもできます。

3. まとめ

vlookup関数、sumif関数についてご紹介しましたが、どちらの関数も絶対参照・相対参照を使うことで効率が格段にUPします。何度も言うようですが、$マークをつけるということを覚えておけばOKです。
 
ちなみに、vlookupは文字でも数字でも引っ張ってくることができますが、sumifは数字のみ合算してくれます。
最初はどちらを使うべきなのか判断に迷う時があると思いますが、数をこなして慣れると、どの場面でどの関数を使うべきかが瞬時に判断できるようになります。
 
今後の数値分析やレポート作成時に是非活かしてください。
 
最後までお読み頂きありがとうございます。

カテゴリー/

マーケティング

タグ/
このエントリーをはてなブックマークに追加
この記事を書いた人
Livewire 編集部Livewire 編集部
Web、マーケティングに携わる方々へのインタビューを通して、ビジネスの背景やお考えをご紹介します。今後は、他の分野にも取材先を広げていく予定です!
お問い合わせはこちら

関連記事

TOP