シナジーマーケティング株式会社

Menu

知らないと損をする!データ・リストの名寄せに使えるExcel小技集~上級編①

本記事は、データの名寄せ作業を簡単に効率化できるExcel(エクセル)の機能や関数をご紹介するシリーズの初級編中級編の続編です。

・【初級編】知らないと損をする!リストやデータ作成・整理に使える6つのExcel(エクセル)小技集
・【中級編】知らないと損をする!データ整理・名寄せに使える6つのExcel(エクセル)小技集

データの名寄せ作業には、「3つのステップ」があります。それは、データの【整理】【絞り込み(フィルタリング)】【統合(マージ・名寄せ)】です(詳細は前回の中級編の「データの名寄せ作業:3つのステップ」を参照ください)。

この順で行わなければ、名寄せしたデータに誤りが生じる可能性が大きくなるので、気をつけましょう。また、今回はこの3つのステップの【絞り込み(フィルタリング)】でのExcel作業で活躍する関数やテクニックをご紹介します。

  • 複数のリスト・データを名寄せ(マージ)する必要があるが、方法がわからない
  • データクレンジング作業に時間がかかる、業務効率が悪い
  • Excelで作業はしているが、「if関数」や「vlookup関数」などの関数が使えない

などの課題を抱えておられるマーケティング・販促担当者様にオススメです。

1.【絞込】基本の基本「オートフィルタ」を使いこなす

「オートフィルタ」は、Excelのシート内にあるリストからデータを抽出する機能です。項目が横方向に並んでいるリストであれば、ボタンひとつで、各項目の選択肢や記載されている内容を利用してデータを絞り込めるようになります。

145_matuzawa_02

145_matuzawa_03

2.【絞込】複数条件での絞り込みもこんなに簡単(オートフィルタ)

「オートフィルタ」の機能のみで、複数の条件による絞り込みが可能です。特殊な関数を知らなくても、簡単に効率的にデータを絞り込めるようになるので、ぜひ活用したいところです。

項目に格納されているデータが「年月日」や「数値(金額や回数など)」や「テキスト」など、それぞれのデータの種類に応じて、さらに柔軟なフィルタリングが可能になるメニューが用意されています。
たとえば、年月日が格納されている項目のフィルタメニューには「日付フィルター」というメニューが表示されます。数値の項目の場合は「数値フィルター」です。

145_matuzawa_04

145_matuzawa_05

項目のセルに格納されているデータが「年月日型」だった場合は、上の図②のように自動的に「日付フィルター」というメニューが表示され、期間に関する多種多様な条件での絞り込みが可能になります。

さらに、この図の「価格」のような数値型データが格納されている項目についても同様に、「数値型フィルター」というメニューが自動的に表示されます。

145_matuzawa_23

145_matuzawa_07

この工程だけで、【都道府県が「大阪」】かつ【価格が「15万円以上30万円より小さい(未満)」】という条件で、リストのデータを絞り込めました。

なお、テキスト型でデータが格納されている項目をオートフィルタした場合は、「テキストフィルター」というメニューが表示されます。

145_matuzawa_08

3.【絞込】もしこんなフィルタリングができたら…をかなえる「if関数」

IF関数は、オートフィルタを使いこなしても絞り込めないような「さらに複雑な条件でデータを絞り込みたいとき」に利用します。

おそらく、名寄せに関するほとんどの絞り込み作業は、1.と2.でご紹介した「オートフィルタ」を使いこなすことで実施できます。しかし、IF関数は、オートフィルタ以上のことができ、名寄せ作業以外でもフル活用できるExcelを代表する関数のひとつです。これを期に、概要だけでもつかんでおきましょう。

①IF関数とは

IF関数とは、Excelの論理関数のひとつで、値または数式が条件を満たしているかどうかを判定します。下記のような構造の関数です。

145_matuzawa_09

要するに、IF関数を使えば、条件に合うかどうか(真か偽か)によってセルに表示される結果をコントロールできるということです。名寄せ作業でよくやる「複数条件に応じて◯✕フラグを立てる」などの作業にピッタリです。
ちなみに、2.のオートフィルタで絞り込んだ条件と同じ条件を、IF関数でセットし◯✕フラグを立ててみました。

145_matuzawa_24

上の図のJ2に書いたIF関数は、以下のとおりです。

145_matuzawa_11

②複雑な絞り込み条件の設定に必要な3つの要素

 ①の関数の中には、以下のように3つの要素が含まれています。

145_matuzawa_12

まずは、【IF関数】。赤枠の部分です。
そして、もうひとつは、そのIF関数の中に記載されている【AND関数】。青枠の部分です。
最後は、AND関数の中に記載されている【演算子】。黄枠の部分です。

「それぞれの条件をどのように組み合わせるのか」を定義する関数「AND/OR/NOT関数」

【AND関数】は、それぞれの条件(演算子で指定している内容)を【かけあわせる(AかつB)】関数で、論理関数の一種です。このように「それぞれの条件(AやB)をどのように組み合わせるのか」を定義する関数には、他に【OR関数(AかBか)】や【NOT関数(Aではない)】などが挙げられます。
特に、Excelを使っての名寄せや集計など、データを扱う場面では、AND関数やOR関数がIF関数とセットになって記載されていることは少なくありません。

たとえば、①の関数を【OR関数】に書き換えた場合…

145_matuzawa_14

NOT関数を使って、「①でない場合」を式にすると…

145_matuzawa_14

このように、これらの関数を使って、IFの中に書かれている「複数の条件」をどのように組み合わせるかを指定します。

「演算子」がIF関数の可能性を広げる

145_matuzawa_12

個々の具体的な条件を記載しているのは、【演算子】です。この種類を把握しておけば、オートフィルタでできないデータの絞り込みでどんなことができるのか、さらにイメージしやすくなります。次の項目で詳細をご紹介します。

4.IF関数での絞り込みをパワーアップさせる(演算子)

 演算子には、比較演算子、算術演算子、文字列連結演算子、参照演算子の 4 種類があります。3.で利用していた以下の演算子(赤文字部分)は、この中の「比較演算子」にあたります。

E2=“大阪府”
I2>=150000
I2<300000

演算子は種類が多いので、まずは一覧でみてみましょう。

①演算子一覧

以下の「算術演算子」と「比較演算子」が、よくIFとセットで利用されるものです。

  意味 内容[セルへの入力例]
算術演算子
+ (プラス記号) 加算 [=3+3]→6
– (マイナス記号) 原産 [=3-3]→0
* (アスタリスク) 乗算 [=3*3]→9
/ (スラッシュ) 割り算 [=3/3]→1
% (パーセント) パーセント [3%]→3%
^ (キャレット) べき算 [=3^3]→27 ※3の3乗
比較演算子
=(等号) 右辺と左辺が等しい(=12=12) A1とB1が等しいとき [if(A1=B2,1,0)]
>(~より大きい) 左辺が右辺より大きい(=12>12) A1がB1より大きいとき [if(A1>B2,1,0)]
<(~より小さい) 左辺が右辺より小さい(=12<12) A1がB1より小さいとき [if(A1<B2,1,0)]
>=(~以上) 左辺が右辺以上(=12>=12) A1がB1より以上のとき [if(A1>=B2,1,0)]
<=(~以下) 左辺が右辺以下(=12<=12) A1がB1より以下のとき [if(A1>=B2,1,0)]
<>(不等号) 左辺と右辺が等しくない(=12<>12) A1がB1以外のとき [if(A1<>B2,1,0)]
文字列演算子
&(アンバサンド) 文字列の結合/連結 [=”比較”&”演算子”]→比較演算子
参照演算子
:(コロン) セル範囲の参照演算子 A1 A2 A3のセルを参照 [=A1:A3]
;(カンマ) 複数選択の参照演算子 A1 A3のセルを参照 [=A1;A3]
(スペース) 共通部分を示す参照演算子 D2:D4 C3:D3を参照 [=D2:D4 C3:D3]

※Office のサポートページにも、演算子についての詳細説明(計算演算子と優先順位)があります。

一覧の「比較演算子」の部分をご覧になった方はすでにお気づきかもしれませんが、「比較演算子」の条件はオートフィルタの機能で代用可能です。名寄せ作業でIF関数を利用する場合、注目すべきは「算術演算子」の部分です。

②オートフィルタかIF関数かは、「算術演算子」の要不要で決定する

名寄せしたいデータの絞り込みを、「オートフィルタ」で行うか「IF関数」で行うか。これについて判断するポイントは、「算術演算子」を使う必要があるかどうかです。

145_matuzawa_16

この場合は、ANDの中に比較演算子しかありません。何らかの「計算をした結果」で絞り込むわけではないので、「算術演算子」を利用する必要がない(=オートフィルタでより簡単に絞り込める)のです。

では、次のような場合はどうでしょう。

145_matuzawa_25

上記のリスト内で、購入金額の累計(初期費用と利用期間中の月額費用の合算)が20万円以上の会員を特定(絞り込み)したい場合、まずは会員別の累計購入金額の計算が必要なので、「算術演算子」を利用しなければ絞り込めません(オートフィルタでは一度で絞り込むことはできません)。

145_matuzawa_18

145_matuzawa_19

複雑な条件でも、1つの式で◯✕フラグを立てることができました。

ちなみに、同じ条件でも演算子とオートフィルタを活用すれば、絞り込むこともできます。

145_matuzawa_20

145_matuzawa_21

145_matuzawa_22

簡単な機能の組み合わせで、複雑な絞り込みやフラグ立ては可能にはなりますが、やはり二度手間になるので一発でIF関数の方が使えるようになると楽ですね!

次回は、<【統合(マージ・名寄せ)】の工程>で活用できる関数やExcelテクニックをご紹介する予定です。お楽しみに!

Excel(エクセル)小技集シリーズ

Excel(エクセル)の管理から卒業したいあなたへ

※記載されている内容は掲載当時のものであり、一部現状とは内容が異なる場合があります。ご了承ください。

シンプル。仕事捗る。~エクメルンは、あなたのExcelリストにメールを一斉配信するサービスです。~

データからお客様を知るための実践手法を大公開!
顧客管理のノウハウ・ドゥハウ【活用編】

  • データを活用するための情報管理
  • データを活用するための現状の改善点
  • 顧客データの分析とその具体的な方法

など、「顧客データの分析をもとにしたデータ活用=本来のCRM」に関する実践的な手法とそのポイントをまとめています。

本書を参考に、自社の顧客管理(CRM)のさらなる最適化をしてみましょう!

顧客管理のノウハウ・ドゥハウ【活用編】

無料ダウンロード

PageTop