はじめに
本記事は、Googleデータアナリティクスのプロフェッショナル認定証のプログラムより、参照させて頂いています。興味を持った方は、是非受講してみてください。
データ アグリゲーション
『データ アグリゲーションとは 複数のソースからデータを集め 1 つの要約されたコレクションに まとめるプロセス』
- データアナリストがトレンドを特定、比較し それぞれのデータ要素を単独で 分析するのでは得られないような インサイトを得るのに役立ちます。
- 一定期間のデータを要約・集計して、 統計情報、つまり 平均値や最小値、最大値、合計値などを 提供することもできます。
こうしたデータ アグリゲーションを 可能にするのが、関数です。
VLOOKUPの準備
LOOKUP とは Vertical Lookup（垂直方向の検索）の略です。
『ある列の中の ある値を検索して、それに対応する 情報を返す関数』
以前、VLOOKUP を使って あるセルの値を取り、別のセルで 一致するものを探したかと思います。 その結果、スプレッドシートにある 数字と文字で構成された商品コードと 別の表計算ソフトにある商品名を 一致させることができました。
しかしその前に、データを 適した形にする準備が必要です。 データクリーニングをしておけば 正確な結果が得られる可能性が 高くなります。
データ型の確認
まずは、一般的なデータクリーニングの 第一段階である、 データ型の違いから見ていきましょう。 たとえば、
- データセットに日付が 数値としてフォーマットされていたり
- 数値が文字列として 設定されている
場合があります。 データが一貫した形式でない場合 または表計算ソフトが認識できる 形式でない場合 VLOOKUP はデータをどう処理すればよいか 分からなくなり、エラーを返します。
テキストを数値に変換
今度は、テキストを数値に変換しましょう。
これを行うには、表示形式メニューで 数値の種類を選ぶこともできますが VALUE 関数を使うこともできます。 VALUE は、数値を示すべき文字列を 数値に変換する関数です。 たとえば、こんな感じです。
結果は 0 です。
この関数は文字列には機能しないからです。 しかし、VALUE 関数を当てはめると 自動でテキストを数値に変換してくれます。
列の下にドラッグすると 456 と 789 も数値に 変換されます。
では、別の SUM 関数を実行して テストしてみましょう。 =SUM と開き括弧を入力し B2:B4 と入力します。 これで B2、B3、B4 が SUM に含まれます。 括弧を閉じて Enter を押します。 これで、合計である 1,368 が表示されました。
余分なスペースを削除
次によくあるのが、表計算ソフトに 余分なスペースが入っていることで 起きるエラーです。 データクリーニングの際には 必ず TRIM を使用しましょう。 TRIM は、セルに追加された 余分なスペースを自動的に削除します。
重複
また、VLOOKUP のもう一つの典型的な ミスでもあり、データクリーニングで 簡単に発見できるのが、重複です。 重複している行がある場合、 検索で最初に一致したものだけが 返されます。 少し前に学んだように 重複の削除の機能では スプレッドシート内の重複した内容を 検索・削除することができます。 重
クリーンなデータは、すべての 基礎となることを常に 心に留めておきましょう。
VLOOKUPを使う
まずは VLOOKUP の構文から始めましょう。
- 103 が検索する値
- A2:B26 は検索する範囲
- VLOOKUP は A、B、C のような列名を認識しないため ２は列を示す数字を使用します。
- FALSE は、VLOOKUP に 完全一致を検索するよう指示
- これが TRUE であれば、関数は近似値を 返しますが、それは私たちが 欲しい結果ではないかもしれません。
早速、VLOOKUP を使ってみましょう。 データアナリストが VLOOKUP を使って行う 最も一般的なことの 1 つに、あるシートに 別のシートからデータを 取り込むことが挙げられます。 これはその一例です。
2 つの異なるシートに存在する データを扱っていますが ビジネス上の問いかけに答えるためには 両方の情報が必要であるとします。
- 上のシートには、従業員 ID と その時給が記載されています。
- 下のシートには、同じ従業員 ID と、 各々どれくらい働いたかの 勤務時間が表示されています。
ここで、VLOOKUP を使い 時給のシートから金額を検索し、 勤務時間のシートに追加してみましょう。
- 範囲 A2 から B5
- ドルの記号を追加して、セルの絶対参照で 範囲を固定することもできます。 これにより、他のセルにコピーする際に 数式が変更されるのを防ぐことができます。
一般的な VLOOKUP のエラーを確認する
1
VLOOKUP は、最初の一致しか返しません。 たとえ一致するものが たくさんあったとしてもです。 そして VLOOKUP が返すのは右側のデータだけで 左側の列は 見ることができません。
でも大丈夫、簡単な解決策があります。 データアナリストは通常、 参照したいデータの左側に 列をコピー＆ペーストすることで、 この問題を回避します。 こうすれば、参照する値は一番左の列にあり 欲しいデータはその右側にあることになります。
2
ここでもうひとつ、 よく直面する問題があります。 VLOOKUP の最初の数行が 正しい結果を返すとします。 しかし、 関数を下にコピーすると、問題が発生します。これは、関数のテーブル配列部分が ロックされていないか、絶対参照でないことが 原因だと思われます。絶対参照とは、 コピーしても行と列が変わらないように ロックすることです。 テーブルの配列をドル記号で囲めば この問題は解決します。 ドル記号は 参照範囲が変化しないよう 制御するものです。
3
また、VLOOKUP の結果を狂わせる もう 1 つの要因に、バージョン管理の問題が あります。 つまり、 ある関数が最初は完璧に動作していたのに その関数が参照しているシートの一部が 変更されてエラーになるといったことです。 たとえば、ユーザーが列を挿入した場合などです。 そうすると、関数内の列指定で 正しい列に導くことができなくなります。 このような場合、 VLOOKUP は不正な値を返します。 こうならないようにするために できることがいくつかあります。 まず、スプレッドシートをロックします。 こうすることで、他の人が変更するのを 防ぐことができます。
これを行うには、「データ」から 「シートと範囲の保護」を選択します。 他の表計算ソフトにも、同様の機能はあります。 次に、保護したいものを選択します。 この場合は、シート全体を保護します。 次に、警告を表示するか、編集できる人を 制限するかなどの権限を設定します。 ここでは自分だけを選びます。これで完了です。
しかし、他の人もそのスプレッドシートで 作業する必要がある場合もあります。 その人たちを制限すると、ひんしゅくを 買うかもしれませんので注意しましょう。 そのような場合は、MATCH を使いましょう。 これは、検索値の位置を 特定するための関数で、 バージョン管理にも有効です。 今すぐには触れませんが、万が一に備えて オプションとして知っておいてください。
4
最後の問題は、 完全一致と近似一致に関連するものです。 VLOOKUP を使う時、 関数内に TRUE と FALSE の どちらを入力するかで、異なる結果が 得られる可能性があります。 TRUE は近似値のマッチングを、 FALSE は完全一致でのマッチングを VLOOKUP で検索します。 つまり、このような関数の場合、 求めているテキストや数値に 最も近いものを探すように VLOOKUP に指示していることになります。 VLOOKUP は、指定した範囲の先頭から始まり 各セルの下に向かって垂直に、正しい値を検索する ものであると知っておくことが重要です。 VLOOKUP は、検索値と等しい、または それ以上の値を見つけると検索を終了します。 そのため、データアナリストは通常、 FALSE を使用します。 これにより、 VLOOKUP は検索値に入力されたものと 完全に一致する値のみを返します。