はじめに

本記事は、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 は検索値に入力されたものと完全に一致する値のみを返します。