こんにちわ、健康診断が近づいてきた事もあり、最近はジムへ行く回数を増やし、休日はロードバイクで数十km走る事にしています。
体重は減らず、むしろ増えていますが体脂肪率は落ちているので筋肉は付いてきてるのかな、と思い食にも気をつけようと自宅でサラダチキンを作るために低温調理器をポチってしまいました。
という訳で今日の話題は低温調理器のレビュー……ではなく前回、前々回に引き続き関数の使いこなし講座です。
今回はCSVチェックに使える関数を紹介していきます。
【リスティング運用者向け】作業を効率化する関数の紹介：データ成形編【Excel】
【リスティング運用者向け】作業を効率化する関数の紹介：分析編【Excel】
CSVチェックに使える関数
今回はCSVチェックに使える関数です。
CSVに限定する必要もないのですが、運用者向けという事で一番確認すべきは入稿時、それもCSVを作成する時だろうと思いCSVチェックを想定して紹介させていただきます。
紹介する関数は以下です。
- EXACT関数
- LEN関数、LENB関数
- HLOOKUP関数、VLOOKUP関数
EXACT関数
文字列が完全一致しているかを確認できます。
数文字くらいであれば目視でも確認できますが、長文や行数がものすごく多いようであれば目視は非常に非効率です。
広告文のチェックなどの時に使える関数です。
関数
EXACT(文字列1,文字列2)
文字列1と文字列2が一致していれば『TRUE』を、一致していなければ『FALSE』を返します。
使用例
上の表の左の列は「リスティング」「リフティング」「リスティソグ」「リスニング」をそれぞれランダムに並べています。
2列目以降で1行目と1列目の文字列がそれぞれ一致するかを精査しています。
数式は下記の通りです。
しかし、これではまだまだ見にくいですね。
そこでIF関数と組み合わせて見やすくします。
EXACT関数の結果が「TRUE」の時に「一致」という文字列を返すようにしました。
これで随分見やすくなりましたね。
このような形で他の関数と組み合わせて使うと非常に有効な関数だと思います。
LEN関数、LENB関数
次は文字数をカウントする関数です。
文字数オーバーなどをチェックするのに利用します。
LEN関数は文字数をカウントして、LENB関数はバイト数をカウントします。
全角文字が2バイト、半角文字が1バイトです。
Google、Yahoo!ともにレギュレーションは半角30文字（全角15文字）です。※2019年9月5日現在
そのため半角で英数字を使っている場合などはLENB関数を使わないと正しく文字数をカウントできません。
関数
LEN(文字列)
LENB(文字列)
それぞれの文字列の文字数、バイト数を返します。
文字列の部分はセルを指定してOKです。
使用例
上記の広告タイトルの文字数、バイト数をそれぞれカウントしています。
LEN関数で文字数をカウントすると16文字でオーバーしていますが、LENB関数でバイト数をカウントすると27バイトです。
上の表ではLENB関数を2で割って全角文字数に合わせているため13.5文字という認識です。
応用として半角の文字数だけをカウントする方法もあります。
LEN関数で出した文字列とLENB関数で出した文字列の差に2を掛けると半角のみの文字数をカウントできます。
手っ取り早く「=(LEN(B25)-(LENB(B25)/2))*2」としてしまう手もあります。
HLOOKUP関数、VLOOKUP関数
特定の文字列があるセルから指定したn番目の値を返す関数です。
関数で躓く人の多くがこのLOOKUP系の関数で挫折しているように感じます。
しかし、この関数をマスターできれば応用範囲も広がり更なる効率化に繋がりますので是非覚えていただきたい関数です。
関数
- HLOOKUP(検索値, 範囲, 行番号, 検索の型)
- VLOOKUP(検索値, 範囲, 列番号, 検索の型)
気をつけるべき点がいくつかありますが、運用者がチェックの際に使うのであれば
- 上から下（見出しから下向きに）抜き出したい時はHLOOKUP
- 左から右に抜き出したい時はVLOOKUP
- 検索値は範囲1行目（1列目）に含まれるものである事
- 行番号、列番号は検索値から何番目のセルを抜き出すかを指定している
- 検索の型はとりあえず「FALSE」か「0」にしておけばOK
これくらいを覚えておけば大丈夫です。
上の画像は上の表から下の青枠部に記載した文字列の情報をそれぞれ横に抜き出すとします。
VLOOKUP関数ですので「検索値」は「範囲」の一番左側にあり、「範囲」内にある「検索値」と一致する文字列から右にn番目を抜き出しています。
数式内の太字になっている数字（2とか3とか）が「範囲」内の何番目を抜き出すかを指定しています。
そのため左から順に2、3、4、5となっている訳です。
注意点としては「範囲」内の1番目は「検索値」と一致するので、『卵（全卵）』の1番目を抜き出すと『卵（全卵）』が返ってきます。
検索する列（行）を含めて何番目かという指定を行います。
検索の型は特に今回のようなチェックに使う場合は「FALSE」にしておけば問題ありません。
この検索の型は完全一致で検索するという意味です。
チェックの際には完全一致以外で検索してしまうとチェックの意味をなさなくなるため、必ず「FALSE」にしておきましょう。
「0」でも「FALSE」と同じ意味になります。
使用例
例えば上の画像左の表のような入札にしたいとします。
同じく右の表は入稿したデータだとします。
目視で精査するのはこの程度の行数であれば問題ないですが、これが数百行、数千行だと人間業ではありません。
こんな時こそHLOOKUP関数、VLOOKUP関数が役に立ちます。
※入札単価は適当です。実際の相場感とは関係ありません。
STEP1：HLOOKUP関数で必要な列を抜き出す
まずはデータを成形します。
例題くらいのものであればこの工程挟む意味はあまりないのですが、GoogleやYahoo!のCSVのように列数が多いと確認に必要な列だけを抜き出した方が分かりやすいです。
この工程ではHLOOKUP関数を使って1行目が「キーワード」の列を順番に持ってきています。
右表の1列目「番号」はセルを参照して行番号を指定するためのものです。
セルに連番を振っておいて、そのセルを参照すればコピーするだけで順番に参照していってくれます。
隣の「入札単価」も同様に参照して、必要な列だけを抜き出しました。
STEP2：VLOOKUP関数で指示書と照らし合わせる
続いて入札単価表の「入札単価」の隣に入稿データの「入札単価」をVLOOKUP関数を使って持ってきます。
数が少なければ目視で確認できるのでこの工程で終了でも構いません。
多い場合は次の工程に進みます。
STEP3：EXACT関数でチェックする
精査する数が多い場合は本記事の最初に紹介したEXACT関数を使って一致するかどうかを調べましょう。
これで関数を使ったチェックは終了です。
例題では一部一致しなかったものがありましたね、危ない危ない。
おまけ
使い慣れれば数式を繋げて利用できるので工程が省けます。
より効率的にチェック出来るようになりますね。
まとめ
他にもチェックに使える関数はありますが使用頻度の高いものを紹介させていただきました。
関数は算数に似ていて、求めたい結果のためにどの公式（関数）を使うのがベストかを考えるのであって公式（関数）自体は覚えてしまえばなんてことはありません。
更に実務上では繰り返し行う作業で利用する事が多くなるはずなのである程度使用するものや組み合わせる関数は定型化されてくると思います。
頻出パターンはさっさと定型化してしまって効率化を図りましょう。
次回はケース別で使える関数を紹介します。
