VlookUP関数の使い方と実例集（Googleスプレッドシート）
こんにちは、エドです！
VlookUP関数はもう色々な解説が出そろっています。
いまさら何を解説するんだという方もいるかもしれませんが、VLOOKUP関数はネットで調べても基本的な部分しか教えてくれません。
まだまだ詳しく知らないよという方も、基本的な使い方をマスターしたぞ！という方も満足する「他では見れない事例集付き」の解説です！
ここではエクセルではなくGoogleスプレッドシートをメインとして解説をしています。ですがそのままエクセルで使えることも多いので、普段エクセルしか使わない方も参考になると思います。
すぐ実例が見たい方は目次から飛んでくださいね。
VLOOKUP関数とは？（Googleスプレッドシート）
VLOOPUP関数は「”キー”となる値をもとに表からデータを参照し、それと同じ行にある別な列の値を取得」します。
下図では「ピーマン」をキーに、商品リストを見に行きます。
その後「ピーマンと同じ行の2番目の値を取得する」というように指定しています。
データを別に持っておき、他の表などでデータの値を参照して持ってくるイメージです。
別にデータを持っておくので間違えて書き換えてしまうこともなくなり、データがどこかに存在すればいつでもその情報を引き出すことができるので、多種多様な場面で使えるエース級の関数です。
VLOOKUP関数の使い方
基本構文は下記の通りです。
=VLOOKUP（検索値, 範囲, 列番号, 検索の型）
わかりにくいのでわかりやすく書くと
=VLOOKUP ( {キーとなる値} , {参照する表} , {何番目の値を取得するか} , {見つからない場合の動き} )
となります。見つからない場合の動きはわかりにくいので後述します。とりあえずこの部分は"false"と書いておけばいいです。
｛キーとなる値｝ ：この文字をもとに表を参照します
｛参照する表｝ ：探しに行く表を範囲で指定します
｛何番目の値を取得するか｝ ：表の左端を１として、何番目の値を取得するか
｛見つからない場合の動き｝ ：false ※意味は否定、～しないという意味
先ほどの表を例にしてみましょう
VLOOKUPを書く場所は「C３」です。ここのセルに以下のように記載します。
=VLOOKUP ( B3 , B6 : C12 , 2 , false )
日本語で書くと「B3の値をもとにB6:C12の表を見に行って、見つかったら2番目の列の値を持ってきて。見つからない場合はよけないことしないで」となります。
値が見つからない場合
参照元のキーが表にあればよいですが、なかった場合に初めて「検索の型（見つからなかった場合の動き）」が動きます。
この検索の型は「true」と「false」のどちらかを書きます。*1
falseの場合、一致するものがなければエラーを返します。
trueの場合、一番近い値（近似値）を取得します。
わかりにくいですが、キーが350の場合、表のなかの350を超えない最大値を近似値として取得します。非常にわかりにくいうえ、予期せぬデータが取得されることもあるのでtrueは滅多に使用されません。
実際に使ってみる
①まずキーとなる値を指定する＋カンマ（,）
②次に参照する表を指定する＋カンマ（,）
③取り出す列番号を指定する
④最後にFalseを記入
別なシートにある表を参照する場合
参照する表が別なシートにある場合、表の指定にひと工夫必要です。
=VLOOKUP（検索値, 'シート名'!範囲, 列番号, 検索の型）
→「 'シート名'! 」を付けます。シート名をシングルコーテーション「'」で囲み、ビックリマークを後ろにつけます。
例）
=VLOOKUP ( B3 , 'シート1'!B6 : C12 , 2 , false )
実例集
ここからは実例をあげながら解説していきます。
後半につれマニアックになっていくのでマニアな方は後半から参照してみてください。
①キーが一番左にない場合
キーが表の2番目にある場合は、参照する表を2番目の列から指定すればよいです
開始位置が「C6」から始まっていることに注意してください。
②複数の行からそれぞれ表の値を取得する場合
参照するテーブルはすべて同じですが、キーの値だけ各行ごとに変えています
複数行で同じ表を参照する場合、行列を固定するのが便利です。*2
C9:D15といった指定の前に「＄」を付けることで、コピーしても列や行が勝手にずれなくなります。関数のC9:D15の部分にカーソルを当てて、F4を押すと＄を挿入してくれます。手作業でも可能です。
③キーの値に文字を追加して検索したい場合
実は検索値（キー）の文字は追加したり関数を書いたりができます。
上記では「ANM-332」という商品コードですが、参照する表の商品コードには「-OK」という文字が必ず最後についています。
この場合「検索値＆”文字”」といった形でも検索することができます。
④VLOOKUPで値がなくエラーになった場合にエラーを表示させない
VLOOKUPは参照先の表に値がない場合はエラー（#N/A）になります。
普段使いなら気にならないかもしれませんが、ビジネスで使用する場合は悪目立ちしてしまいます。
こんな時はIferror関数で囲ってあげると効果的です。
=IFERROR ( VLOOKUP（検索値, 'シート名'!範囲, 列番号, 検索の型）, エラーの場合の値)
IFERROR関数は、エラーの場合に特定の値を返す関数です。
= iferror ( エラー判定するセル , エラー時の値 )
という構成になっており、エラー時の値は省略できます。省略時は空白。
⑤VLOOKUP関数とArrayFormula関数と組み合わせ（スプレッドシート限定）
一気にマニア度が上がります。例えば1000行の表で１つ１つVLOOKUP関数をコピーするのは格好が悪いですね。実際に間違って途中の行を削除してしまった等のミスもよくあります。こんなときはArrayformula関数と組み合わせてみましょう。
※Arrayformula関数の説明はここでは省きます。詳しく知りたい方は
Arrayformula関数とは？便利な使い方や実例も！（Googleスプレッドシート） - 初心者脱出！SEO情報ブログ
を参照してくださいね
Arrayformula関数と組み合わせると、販売リストの行数がどれだけあっても関数は1セルに書くだけで全部のセルに値が入ります。
VLOOKUP関数を毎回コピーする煩わしさから解放されるとともに、ミスも減ります。
=arrayformula(VLOOKUP(B4,C9:F15,2,false))
下図ではArrayformula関数がD4セルに入っています。D5セルには￥1,600と入っていますが、これは自動で出力されています。セルが何万行でも書く関数はひとつだけ。
⑥1つのVLOOKUP関数で複数列をまとめて返す（スプレッドシート限定）
Arrayformula関数と組み合わせた時限定ですが、１つのVLOOKUP関数で複数列をまとめて取得することもできます。
普通は下図のように、販売リストの単価、商品名、備考の３つのデータを取得したい場合、3列ともVLOOKUP関数が必要です。
しかしArrayformula関数を組み合わせると・・・
C4セル一つ入力するだけで、D4、E4列のデータをすべて一度に取得できます。
=arrayformula(VLOOKUP(B4,C9:F15,{2,3,4},false))
ちなみに上記の例では、C5、C6にもVLOOK関数が必要です。しかし行も組み合わせることができるので、C4セルに１つ関数を書くだけで、C4～E6すべてに値を入れることができます。
まとめ
いかがだったでしょうか？
VLOOKUP関数はメジャーになって多くの人が使えるようになった関数ではありますが、まだまだポテンシャルがあることがわかってもらえたと思います。
私は本職がSEOコンサルタントなのですが、この関数を覚えたことによって数倍速くレポート作成ができるようになっています。
他にも「VLOOKUPで完全なあいまい検索」「VLOOKUPとQUERY関数の組み合わせ」「VLOOKUPで検索値の列より左に取得したい値がある場合」など面白い工夫ができます！
要望があれば続編も作成してみるので期待してくださいね。
Arrayformula関数とは？便利な使い方や実例も！（Googleスプレッドシート）
こんにちは、エドです。
エクセルと似たような使い方ができますが、スプレッドシートにしかない便利な関数がいくつかあります。
今回は超絶便利な関数「Arrayformula関数」について解説します！
読み方はたぶんアレイフォーミュラ。
Arrayformulaを使う前に、こんな悩みがありませんか？
・データが大量にある時、VLOOKUP関数を全セルにコピーするのは煩わしい
・データの最終行がわからないけど、関数を入れておきたい
・データが増えたり減ったりするときに自動で値を計算したい
・列ごとに関数を入れるのが大変
これらを解決できる神関数がArrayformulaです！
Arrayformula関数とは？
Arrayformula関数は配列変数を受け取りセルに展開してくれる関数です。
なんのこっちゃ？と思われるかもしれませんが、要は複数セルの関数をまとめて1セルで記載できるよ、ということですね。
例えばA1～B4シートの値を1セルに関数を入れるだけで関数の入っていないセルにまでコピーしたりできます。赤セルにだけ関数が書いてあります
=Arrayformula(A1:B4)
この「１つ関数を書くだけで何も書いていないセルまで更新できる」のがArrayformula関数の素晴らしいところです！
※Arrayformula関数はそれだけじゃないんですけど、上記が一番便利な使い方ですね
Arrayformula関数の構文
=Arrayformula( 配列を返す数式 )
配列を返す数式のところに多種多様な数式を書けるのが魅力です
Arrayformula関数の使い方
この関数は本当に色々な使い方があるので、アイディア次第でなんでもできちゃいます。そのなかでも特に便利に使えそうな使い方を紹介します！
１．配列をそのまま返す（セルのコピー）
まずは他のセルの値を丸ごとコピーする方法
=ARRAYFORMULA( [セルの範囲] )
関数を入力したセルはE1だけ！
【解説】
Arrayformula関数は「配列を返す」関数なので、アウトプットも複数返すことができます。この場合は範囲指定でセル情報をA2～B4まで6個分指定しています。
1セルのコピーは「=A2」のように指定しますよね。
それの6個分の入力情報があるということです。6個のセルの情報を受け取ったのでArrayformulaは6個分のセル情報を吐き出しています。
範囲指定には「行列の情報」も含まれています。
ですから行列のそのままコピーされるというわけです。
ちょっとまった、配列ってなに？
配列とは縦と横で構成された二次元の表みたいな考え方です。（三次元以上もあります）
そう考えるとエクセルやスプレッドシートのセルも配列と言えます。
もちろん1列だけでも1行だけでも複数個あれば配列です。
２．複数セルまとめて計算
こんなデータがあったとします。利益の部分を「売上 - 原価」にしたいとき、
通常は 「 = B2 - C2 」と計算しますね。
それを全部のD列のセルにコピペして・・・
これがArrayformula関数なら１セルでまとめて計算できます。
=Arrayformula( B2 : B5 - C2 : C5 )
ちなみに
✕ =Arrayformula( B2 - C2 )
○ =Arrayformula( B2 : B5 - C2 : C5 )
です。
これはArrayformula関数の特徴で「入力側も配列にしないと配列が返らない」からです。実は項番１の範囲指定も「配列」なのです。
３．文字列の一括置換
まとめて関数で置換したい場合、SUBSTITUTE関数を使用します。エクセルではすべてのC列のセルに関数をコピペしなくてはなりません。
しかしArrayformulaを使用すれば1セルに関数を書くだけで実現できます。
=arrayformula( substitute( A2:A5 , "牛乳" , "" ) )
（よくあるミス）
範囲指定するのを忘れていた、もしくはArrayformula関数で括っていないときにこの指定をしても値は１セルしか更新されません。
４．VLOOKUPとの組み合わせ（複数行の一括抽出）
真骨頂です。VlookUPと組み合わせることによって、1列全部のVlookupを1回の関数で対応できちゃいます。
まずは普通のVlookupで１セルだけ値を取得する方法
Arrayformulaを使うとこの通り。赤いセル以外は何も関数は入っていません。
=Arrayformula( VLOOKUP ( A2:A5 , $D$2:$E$5 , 2 , false ) )
注意する点はいつもの通り赤字の範囲指定部分ですね。比較対象のほうも配列で指定してあげる必要があります。
５．VLOOKUPとの組み合わせ（複数行列の一括抽出）
Arrayformulaを使ってVlookUpをすると非常に便利なことがわかりました。
では下図のような場合はいかがでしょうか？
赤いセル２つにそれぞれのArrayformulaで括ったVlookupを設定しますか？もちろんそれでも実現は可能です。
B2セル =Arrayformula(VLOOKUP(A2:A5,$E$2:$G$5,2,false))
C2セル =Arrayformula(VLOOKUP(A2:A5,$E$2:$G$5,3,false))
これも列を配列で返すことによってなんと関数は１つで実現可能なんです。
=Arrayformula ( VLOOKUP ( A2:A5 , $E$2:$G$5 , {2,3} , false ) )
ポイントはVlookupの列を返す部分。{ }で括られていますね。これにより列も配列にすることが可能になります。
関数は赤いセルに１つだけ。2列くらいならあまり恩恵はありませんが、これが10列だったらどうでしょう？便利なことがわかるかと思います。
６．出力するデータの範囲が決まっていない場合
例えば売上データを毎日追加していくタイプの表を運用していたとします。
データは毎日追加されますが、関数をその都度範囲を変更するのは面倒ですね。
今日 =Arrayformula(B2:B5-C2:C5)
次の日 =Arrayformula(B2:B6-C2:C6)
効率厨の私からすると面倒でたまりません。
この場合、範囲を指定せずに列丸ごと指定することも可能です。
この場合、B6、C6が更新されると自動的にD6も更新されるようになります。
=Arrayformula( B2:B - C2:C )
B列、C列の終わりを指定しない（数字を書かないと全列指定、行も同じ）
データがない行に変な値がずらっと並ぶのはがダサいんだけど？
おっしゃる通りでございます。この場合は「ここにデータがある時だけデータを更新する」という指定を入れて解決です。
=Arrayformula( IF ( A2 : A = "" , "" , B2:B - C2:C ) )
赤字が追加した部分です。
「もしA2が空白だったら、空白を返す、それ以外なら計算してね」という意味です。
ここでもIF文は配列で書いてあげる必要があるので注意ですね。
Arrayformula関数の注意点
ここまで見ていただければとても便利に使える関数だということは分かったかと思います。ですが万能に見えるこの関数にも弱点があります。
使えない関数
この関数は「対応していない関数」が存在しています。
・Index関数、Match関数
・Sumifs、Countifs、Ifs関数
・sum、average関数 ※工夫すれば行ける
これらは「基本的には」使用できないので注意しましょう。
特にIndex/Match、お前は対応していてほしかった・・・
更新する範囲に文字を手入力すると全部エラー
Arrayformulaでの出力結果範囲内に、手入力で文字を入れたり他の関数で上書きするようになっているとエラーが発生します。
（めちゃめちゃよくあります）
いかがでしたでしょうか？
今回は神関数Arrayformulaを解説しました！
機会があれば他の機能についても解説してみたいと思います！
正規表現で置換する「REGEXREPLACE関数」の使い方と便利な応用技（Googleスプレッドシート）
こんにちは、エドです。
エクセルと似たような使い方ができますが、スプレッドシートにしかない便利な関数がいくつかあります。
「REGEXREPLACE」関数の概要
その一つが今回紹介する「REGEXREPLACE」関数。
読みづらいですけど、RegularExpressionsReplaceなので「レッグエグリプレイス」ですかね。脳内ではそう呼んでいます（笑
さて、今回ご紹介するREGEXREPLACEですが、エクセルでも有志が作成したアドオンを入れたり、VBAを使うことで同様のことができます。デフォルトでは使えないのでスプレッドシートにしかない関数として紹介しています。
この関数はセルの内容を正規表現で置換する関数です。
使い道が思いつかない方が大半だと思いますが、スプレッドシートで日々の目標管理やモニタリングをしている方には非常に便利です。
後半に応用編を載せていますが、私も実際の業務で使っているほど便利です。
論理思考が必要になるのでプログラマー脳の方のほうが相性いいかもしれませんね。
※正規表現はかなり融通が利き、カッコに囲まれた部分だけ置換、URLからドメインだけ取得など色々な操作が可能です。ただし書き方はある程度勉強が必要です。
ここでは正規表現の書き方は深く言及していません。細かい解説は以下のサイト様が詳しく記載してありますので参考になります。
基本的な使い方
まずは基本的な使い方から。とにかく色々なことができるので、よく使いそうな例をいくつか解説します。
正規表現を使わずに普通に置換する
=REGEXREPLACE( [ 置換したいセル ] , [ 正規表現 ] , [ 置換文字列 ] )
まずは簡単な使い方から。
=REGEXREPLACE(A1,"カレー","ラーメン")
普通のSUBSTITUTE関数を使用したときと同じ使い方です。
A1セルの内容から「カレー」を探し出し、「ラーメン」に置換しています。
これだとこの関数を使う意味がないので、正規表現を使ってみましょう。
特定の文字列の前を消す
=REGEXREPLACE( [ A1 ] , [ ".+カレー" ] , [ ”” ] )
「.+」は"任意の文字1文字以上の繰り返し"という意味になります。難しいですが単純にカレーの文字が出てくる前に何か文字があって、それが何文字あっても対象、という考え方です。
カレーは消したくないのなら
=REGEXREPLACE( [ A1 ] , [ ".+カレー" ] , [ ”カレー” ] ) や
=REGEXREPLACE( A1 , ".+(カレー)" , "$1" )
となります
カッコに囲まれた文字だけ置換
=REGEXREPLACE( A1 , "「.+」" , "ラーメン" )
カッコが（）にの場合は注意が必要です。正規表現でも（）を使うので、コンピュータ側が正規表現なのか文字そのものが対象なのかわからなくなるのです。
こういう場合は￥（半角では\）をその文字の前につけてあげます。 これをエスケープシーケンスといいます。単純にエスケープということもあります。
※ほかにも "？" や " . " や " * " などもエスケープが必要です
↓は\をつけなかった例
文字の順番を入れ替える
=REGEXREPLACE( A1 , "(.+)(カレー.+)" , "$2$1" )
カレーが出てくる前の1文字以上連続する文字と、カレー以降の文字全部の2つに分け、順番を入れ替えています。
$1、$2はその前の正規表現の（）に関連していて、$1が一つ目のかっこ、$2が２つめのかっこになります。
$2、$1としているのが入替の部分ですね。
(.+)が赤字の部分、(カレー.+)が青字の部分です。赤字が$1、青字が$2です。
便利な使い方
ダブルコーテーションに囲まれたカンマを削除したい
例えば以下はGoogle広告のエクスポートデータですが、これをスプレッドシートで管理するとします。このままカンマ区切りで分割してしまうと・・・
あれ？何かおかしい！
そう、数値のカンマも区切られてしまうんですね。
（"テキストを列に分割"の機能を使用すると、すべて文字列として扱われるのでこのように壊れたりはしませんが、書式が文字型になってしまいます。書式を維持するにはSPLIT関数が良いのですが、この問題に引っ掛かります）
前フリが長くなりましたが、その場合の置換方法
=REGEXREPLACE(A2,"""(.+?),(.+?)""","$1$2")
ワークシート関数のREGEXREPLACEは正規表現をダブルコーテーションで囲まなければならないので、ダブルコーテーションそのものを正規表現で書くにはちょっとコツがいります。
ダブルコーテーションを2つ書くとダブルコーテーションの中にダブルコーテーションを書くことができます（？？）
=REGEXREPLACE(A2,"""(.+?),(.+?)""","$1$2")
緑が正規表現で指定したダブルコーテーション。
１．ダブルコーテーションで始まり
２．その中で1文字以上連続でカンマが出てくるまで
※？は最小一致といいますが詳しくは割愛。正規表現にヒットする一番小さい単位を持ってくるという意味です。
３．カンマの後のダブルコーテーションが出てくるまで
４．そのヒットしたカンマ前とカンマ後をカンマ抜きで置換
という流れになります。
以上、REGEXREPLACE関数でした！
機会があれば他の機能についても解説してみたいと思います！
その他関数
文字列を分割する「SPLIT関数」の使い方と”応用技”（Googleスプレッドシート）
こんにちは、エドです。
エクセルと似たような使い方ができますが、スプレッドシートにしかない便利な関数がいくつかあります。
その一つが「特定のセルを指定した文字で分割するSPLIT関数」です。
※便利な応用技を見たい方はこちらからどうぞ！
SPLIT関数の概要
SPLIT関数とは、特定のセルの文字列を指定した文字で区切って展開する関数です。
エクセルの「区切り位置の指定」と同じ機能ですが、こちらは関数化することでセルの値が更新された直後に反映させることができます。
A2のセルを変更すると、B、C、D列がすぐに変わる！
※ほかのセルには何もしていません
（エクセルになぜないのか？ってくらい便利です）
SPLIT関数の使い方
＊基本的な使い方＊
SPLIT（[ 分割したい文字列 ] , [ 区切り文字 ] )
区切り文字は””（ダブルコーテーション）で囲みます。
１．文字列を指定した文字（カンマなど）で区切る
こちらの例は関数で「カンマ」でA2のセルを区切っています
B2セルにSPLIT関数を入れ、A2セルを指定して区切り文字を「,」にしています。
C～Dセル（区切られた個数分のセル）は空白にしておきます。
空白にしていない場合はエラーになります（C列に値が入っている）
２．文字列を指定した文字列（2文字以上）で区切る
＊区切り文字を2文字以上指定する場合の使い方＊
SPLIT（[ 分割したい文字列 ] , [ 区切り文字 ] , [ 各文字の分割をする ])
区切り文字は1文字でなくとも構いません。
しかし失敗することがあります
期待した結果はこっちのはず・・・！？
これはSPLIT関数では「文字列を指定した場合各文字ごとに区切る」という仕様のためです。AかBかCで区切られてしまうんですね。
その場合はSPLIT関数で次のように指定します
=SPLIT（ A2 , "ABC" , false)
A2の文字列を、ABCで区切ります、各文字ごとに区切りますか？→Noという意味になります。（Yesならtrue、指定しない場合はデフォルトがtrueになります）
無事に区切れました
３．空白行を削除しないで区切る
＊空白行も含めて文字列を区切る場合の使い方＊
SPLIT（[ 分割したい文字列 ] , [ 区切り文字 ] , [ 各文字の分割をする ] , [ 空白行を削除する ])
次のケースを見てみます
普通に区切れているように見えますが・・・
４つに区切られてなきゃおかしいはず！
これはSPLIT関数では「区切った後の文字が空白なら表示しない」という仕様のためです。何もないデータなんだから表示しなくていいだろということですね。
しかしこれはCSVデータなどで表示列数が決まっているときは困ります。
その場合はSPLIT関数で次のように指定します
=SPLIT（ A2 , "," , false , false)
A2の文字列を、カンマで区切ります、各文字ごとに区切りますか？→No、空白行は削除しますか？→Noという意味になります。
無事に４つの列に分割できました。
SPLIT関数応用編
便利なSPLIT関数の使い方
１．SPLITで区切った値の〇番目を取得する
通常はSPLITするとすべての分割された値がセルに入ります
ですが以下の方法を使うと、分割されたうちの好きな〇番目の値を取得することができます。
=INDEX(SPLIT( [ 分割したい文字列 ] , [ 区切り文字 ] ), [ 取得したい列番号 ] )
SPLITで分割されたn番目の値を取得する
最小値は１、〇番目の値を取得すると直感的に指定してOKです。
=INDEX(SPLIT( A2 , "/" ),1)
=INDEX(SPLIT( A2 , "/" ),2)
SPLITで分割された最後の列を取得する
=INDEX(SPLIT( A2 , "/" ),COLUMNS(SPLIT( A2 , "/")))
取得する列の番号を指定する部分にCOLUMNS関数を指定しています。これは列数を返す関数なので、以下のように読み解けます。
=INDEX(SPLIT( A2 , "/" ),COLUMNS(SPLITで分割した列数)))
↓
=INDEX(SPLIT( A2 , "/" ),最大の列番号)
※上記の例ではCOLUMNS(SPLIT( A2 , "/") は3が返ります
ちなみにArrayformula関数と組み合わせるとうまくいきません。
それはIndex関数の正式な書式が↓だからです。
=INDEX(SPLIT( [ 分割したい文字列 ] , [ 区切り文字 ] ), [ 取得したい行番号 ],[ 取得したい列番号 ] )
通常1セルだけ指定する場合は、
=INDEX(SPLIT( A2 , "/" ),1)
で取得できますが、正確には
=INDEX(SPLIT( A2 , "/" ),0,1)
と記載する必要があることを覚えておくとよいです。
２．区切った後のセルに書式設定を設定する
普通に区切るとこうなりますが、、、
C列を日付型、D列を通貨型にあらかじめ指定しておけば書式が適用されます。
実はテキストを列に分割では全セルが自動設定になってしまいます。これを
防ぐためにもSPLIT関数は有効です。
３．他の関数と組み合わせる
ArrayFormula関数と組み合わせる
便利な関数にArrayFormula関数があります。
配列を返す関数で、要するに「まとめて関数を適用」できる有用な関数です。
こんな感じで、1セルに値を入れるだけで他のセル表を丸ごと持ってきたりできます
そこにSPLIT関数を指定することもできます。
分割と配列に格納を両方同時にできるんですね。
こんな風に設定すると、1セル関数を入れただけでA列全部に適用できる素晴らしい機能があります。
=ARRAYFORMULA(SPLIT(A1:A," "))
値がないっていない行はエラーになっちゃいますが、それを防止するなら以下のようにします
=ARRAYFORMULA(IFERROR(SPLIT(A1:A," ")))
エラーの場合は何も表示しないという指定を追加することで、値のある行だけSPLITが適用されているように「見せかける」ことができます。
ちなみにARRAYFORMULA(INDEX(SPLIT( A2 , "/" ),2))のような指定はできません！
これはIndex関数がArrayformula関数に対応していないためです。
上記は間違いでした。Index関数の問題ではなく指定方法の間違いでした。
ただしくは
ARRAYFORMULA(INDEX(SPLIT( A2 , "/" ),0,2))
とすることでArrayformula関数と組み合わせることができます。
Vlookup関数と組み合わせる
力技ですが、Vlookup関数を使用する時に、参照元の値の一部と表を突き合わせることもできます。
下の例だと「今日/みかん/100円」のうちSPLITで「みかん」だけ抽出して突合せをしています。
これを応用すると、こんなこともできます。
※広告CSVデータのうち、キャンペーン名だけ取り出してVLOOKUPをあてに行き名称を取得する
一般的には使わない手法ですが、覚えておくと便利な機能だったりしますよ！
以上、SPLIT関数でした！
機会があれば他の機能についても解説してみたいと思います！
【サーチコンソール】サイトのURLリストを作成したい
サイトのURLリストを作成したい場合、クロールする方法やサイトマップからデータを取得する方法があるが、
サーチコンソールを使用するという方法もある。
もちろん権限がないと使えないが、サイト担当でも自分たちのサイトのURLリストを所持していないケースも多い。
そういった場合に活用できる一つの方法になる。
まず、サーチコンソールにアクセスし、
検索パフォーマンスからページを選択する
ダウンロードしたい場合は右上にある矢印のエクスポートを選択する。
そこから「クエリ」「デバイス」「フィルタ」「ページ」「検索での見え方」「国」「日付」と全部取得できる。
ダウンロードの場合上限1000件。それ以上はAPIを使ってとることになる。
【テクニカルSEO】GoogleBotのクロール量が急増したときの対処（大量アクセスが来た場合）
こんにちは、エドです。
下書きはたくさんあるんですけど公開できるレベルに至ってないので
これが２記事目になりますね。
今回はGoogleBot大暴れの巻きです。
タイトルは「対処」となっていますが、
結論から言うとすべてを救う対処法はありません。ヒドイ。
少し長いので、情報だけが欲しい方は目次からどうぞ。
- ターゲットユーザー
- 読むのに必要なスキル
- GoogleBotとは
- GoogleBotのクロール
- GoogleBot大量アクセスの問題
- アクセス制限に必要な情報
- 大量アクセス発生時のGoogle見解
- アクセス制限の方法
ターゲットユーザー
- 中規模～大規模サイトの（運用者 or SEO担当者）
読むのに必要なスキル
- インフラ基礎知識
- リダイレクトの基礎知識
- クローラの基礎知識
GoogleBotとは
GoogleBotとは、検索したときに検索結果に出すサイト情報を集める
Googleが作成したクローラ（自動プログラム）です。
いくつかバリエーションがあり、パソコン用、スマホ用などが存在しています。
一覧はこちら ※2020/05/27現在
https://support.google.com/webmasters/answer/1061943?hl=ja
GoogleBotは優秀で、某〇nigや〇aiduのクローラのようにコントロール不能にならず、
Robots.txtを見てくれてメタタグにも従ってくれます（基本的には）
そして、GoogleBotもですが、ほとんどのクローラはUserAgent*1を持ち、
アクセス元の識別が可能になっています。
本当に優秀なGoogleBotですが、いざ大暴れ（短時間の大量アクセス）した
際の対処法がかなり少ないのはご存じでしょうか？
GoogleBotのクロール
通常、GoogleBotはサイト規模に応じて負荷をかけないように調整してくれています。
負荷というのは、時間単位のアクセス数です。
短期間のアクセスが多すぎると、サイト側にとってはDDos攻撃*2と変わりがありません。
※Googleにその気がなくても
突如襲い来る「大量アクセス」
つまり通常は適切にアクセスしてくるGoogleBotですが、
たまになぜか異常なほどの大量アクセスを送ってくることがあります。
通常時の数十倍、通常時で１０万件／日なら、５００万件／日なんて規模で
アクセスしてくる場合もあります。
SEO担当が気づくことは「まれ」
そして、SEO担当がこれに気づくこと自体滅多にありません。
気づくのはたいていインフラ側の担当者さんです。
大規模サイトの場合、サーバーダウン手前でアラートが鳴り対処できるように
設定されています。
インフラの担当者が大量アクセスを解析して
どうもGoogleBotっぽい。SEOの担当者側で解決するべき問題では？
となるのです。
※余談ですが、筆者は大規模サイトのSEO担当者時代、サーバーログのうち
BOTだけを抜き出したデータベースを作成していました。
それをグラフで毎日Slackに送って、異常値がないかを監視してたりもします
異常値があるときは一人で大騒ぎしていました(笑)
GoogleBot大量アクセスの問題
では大量アクセスされると何が問題なのでしょうか？
「そりゃサーバーダウンするかもしれないからだろ？」
その通りですが、クローラの大量アクセスというのはGoogleに限らずちょくちょく
ありまして、その都度アクセス制限をかけるのが普通です。
「GoogleBotもアクセス制限かけりゃいいんじゃないの？」
そう、ここが問題です。
GoogleBotにアクセス制限をかけてしまうと、GoogleBotがページを辿れなくなって
しまうだけではなく、他にも問題が発生します。
GoogleBotアクセス制限かけた場合の問題
- Googleがページを辿れなくなる
- 大量のエラーを返すとクロール頻度が下がる可能性がある
- エラーを返したページは正当に評価されない可能性がある
それぞれ問題を見ていきます。
Googleがページを辿れなくなる
これはそのまま、クロールにたいして制限しているので見れなくなりますよね。
更新されたページがあっても見ることはできず、リンクも辿れなくなります。
もちろん削除されたページも判別できません。
大量のエラーを返すとクロール頻度が下がる可能性がある
Googleがこれに言及しています。
＞サーバーのエラー率が上昇すると、Googlebot はリクエストを抑制してサーバーの過負荷を防ぎます
https://support.google.com/webmasters/answer/35253?hl=ja
エラーを返すとはページのステータスコードで判断されます。
よくある 404（ページが見つかりません）などもステータスコードですね。
その他 500（サーバーエラー）、503（サーバーアクセス負荷）、
429（アクセス過多）などが大量アクセス時に返すことの多いステータスコードです。
これらを返されると、Googleは「サーバーの調子が悪いんだな」と判断して
クロール頻度を下げるのです。
エラーを返したページは正当に評価されない可能性がある
当然GoogleBotはページを読めないので、評価の更新はされません。
また、エラーが続くと「ページ自体が不安定」と解釈される可能性もあります。
※断言できないのがSEOの難しいところです
簡単にまとめると
「GoogleBotのアクセス制限をかけたら”SEO評価ダウン”がありうる」
ということが問題になるのです。
そのまま放置したらサーバーダウンし、アクセス制限をかけたら
SEOがダウンする・・・前門の虎、後門の狼のようなこの状況。
しかし、サイトがダウンしたらSEOもへったくれもないので、当然サーバーダウン
対策を取ることになります。
SLA（サービスレベル保障）が99.999％のようなサイトならなおさらですね。
アクセス制限に必要な情報
通常、アクセスを制限するために必要な情報は一般的なケースでは
- IPアドレス
- UserAgent
のどちらかです。
GoogleBotのUserAgentもIPアドレスも公開されています。
https://support.google.com/webmasters/answer/80553?hl=ja
UserAgent
https://support.google.com/webmasters/answer/1061943?hl=ja
IPアドレスは大量にあり、IPレンジで範囲制限をしなければならないので、
私はUserAgentを推奨します。
※UserAgentは容易な反面、偽装が可能です。偽装されたとしても偽装ごと
アクセス制限かければいいので問題ないと考えていますが、気になる方は
IPアドレスの実装をお勧めします
大量アクセス発生時のGoogle見解
Googleに問い合わせても、「ツールを使ってくれ」か
「我々は特に問題は発生していない」
として回答がもらえないことが多いです。
超大規模サイトになると調査はしてくれますが、
基本的に「自分たちで解決してくれ」と言われてしまいます。
仕方ない。自分たちでやるしかない。
アクセス制限の方法
ではアクセス制限の方法はどういった方法があるでしょうか。
Search Consoleでクロール頻度を設定する
こちらのクロール頻度設定ページから変更が可能です。*3
※SearchSoncoleにログインした状態でクリックしてください
頻度の設定で、低いにすればクロール頻度を減らせます*4
メリット
・ インフラの設定をしなくともSearchConsoleのみで完結する
・GoogleBotに明確な意思表示ができる
デメリット
・反映されるまでに時間がかかる
・効果がないケースもある（通常は効果があります）
・クロール調整のバーが出ないサイトもある
エラーステータスコードを返す
こちらはサイト全体か、ページ別どちらでも対応が可能です。
アクセスしてきた際にHTTPステータスコードにエラーコードを返すというものです。
返す場合は以下のステータスコードから最適と思われるものを返します
・500
・503
・429
500はInternalServerErrorといい、内部サーバーエラーを指します。
503はService Unavailableといい、サーバー高負荷など一時的にアクセス不可を指します
429はToo Many Requestsといい、アクセス過多。リクエスト数限界を意味します。
これらはエラーのため、一般ユーザーもその制限を受けてしまいます！
つまり全ページでエラーを返してしまうと実質サービス停止に近いもので
かなり注意が必要です。
ここで、ある程度インフラ知識のある方ならふと思うのが
「GoogleBotのみエラーコード返却すればいいんじゃない？」
問題ない行為なのか？に関しては
私個人の見解としては以下の理由からグレーゾーンとしています。
- GoogleBotのみが悪さをしている
- クローキングには当たらない（はず）
GoogleBotのみが悪さをしている
これは問題のあるBOTを制限する行為で、サイト側からすれば運営上必要です。
何もおかしい行為ではありません。
クローキングには当たらない
クローキングをご存じでしょうか？Googleガイドラインにも記述のある
ユーザーとBotに表示させるコンテンツを別々にする行為をいいます。
私の解釈は「悪意を持って、Botに見せるコンテンツ変化させること」と
解釈していますので大丈夫なはず、という見解です。
（正直なところ、ここは意見の分かれ目だと思います）
メリット
・ 絶対これ以上負荷が増えない
・ 対応にSEOの知識を必要としない
・ 対応直後から効果がある
デメリット
・人間もアクセスできない
・サーバーの設定変更が必要
・知識がない人間が扱うと致命的なミスがありうる
エラーステータスコードを返す その２
こちらもアクセスしてきた際にHTTPステータスコードにエラーコードを
返すというものです。
その１と違うのが、制限する範囲を絞る部分です。
具体的にいうと
- サーバー台数のうち半数のみ制限をかける
- ロードバランサーで片方のみに制限をかける
- 特定ページのみ制限をかける
というものです。
ここまで専門的だと私もあまり詳しくはないので詳細は割愛しますが、
最後はわかりやすいですね(笑)
メリット
・ 負荷が緩やかになる
・ 人間もBotも半数はアクセスできる
・ 対応にSEOの知識を必要としない
・ 対応直後から効果がある
デメリット
・ 人間もBotも半数しかアクセスできない
・サーバーの設定変更が必要
・知識がない人間が扱うと致命的なミスがありうる、その1よりもさらに高度
まとめ
これら以外にもテクニカルな解決方法はあるかもしれませんが、
基本的にツールかサーバー側でアクセス制限をかけるしか対処方法は
ありません。
まずはSearchConsoleでクロール制限を行い、
クロール頻度が変わらないようだったらサーバー制限をかけると
いうのが現状ベストかと思います。
※本記事に関して、間違いや問題発言がありましたらコメント欄にて教えて
いただけますと幸いです。
その他、他にもこんな方法があるよ！というのも歓迎です！私のほうで検証出来たら
追記したいと思います！
