すべてのカテゴリ

魔法のスプレットシート第2回です。今回は、住所情報から都道府県抽出する関数の作り方に解説していきます。活用する場面で言うとお客さんが入力した住所などから都道府県を取り出して都道府県ごとに集計したいなどが考えられるかなと思います。このブログではスプレットシートの関数の作り方について解説していくのでフォローしていただけると嬉しいです。早速解説していくのですが、結論から言うとREGEXEXTRACT関数で都道府県名いずれかにヒットすると言う正規表現を指定して部分一致抽出することで実現できます。具体的な例を示すと=REGEXEXTRACT(A2,"(北海道|青森県|岩手県|宮城県|秋田県|山形県|福島県|茨城県|栃木県|群馬県|埼玉県|千葉県|東京都|神奈川県|新潟県|富山県|石川県|福井県|山梨県|長野県|岐阜県|静岡県|愛知県|三重県|滋賀県|京都府|大阪府|兵庫県|奈良県|和歌山県|鳥取県|島根県|岡山県|広島県|山口県|徳島県|香川県|愛媛県|高知県|福岡県|佐賀県|長崎県|熊本県|大分県|宮崎県|鹿児島県|沖縄県)")（ふざけているように見えますがこれが経験上一番正確です）正規表現とは簡単にいうと文字列のパターンを特定の記号で表現したものです。プログラマーなどでないと身近に感じないかもしれないですが、知っておくとデータ処理でできることがかなり増えるのでぜひ勉強してみてください。（いつかここでも解説します。）今回のケースで言うと北海道|青森県|岩手県の部分だと、「北海道」または「青森県」または「岩手県」の文字列のいずれかにヒットするという正規表現になります。これを４７都道府県に

魔法のスプレットシート第1回です。今回は、vlookupの検索キー（検索する値）が複数ある場合の関数の作り方に解説していきます。活用する場面で言うと各商品の都道府県別の売り上げをvlookupで持ってきたいなどが考えられるかなと思います。このブログではスプレットシートの関数の作り方について解説していくのでフォローしていただけると嬉しいです。早速解説していくのですが、結論から言うとvlookup関数の範囲を指定する部分をfilter関数を用いて書くことで実現すことができます。具体的な例を示すと=vlookup(検索キー1,filter(データ範囲,条件列=検索キー2),取得列番号,false)のような形です。考え方で言うと、指定しきれない条件を検索範囲を指定する段階で先に絞ってしまうといった感じです。上記の例で言うとC2の場合、vlookupを描ける範囲をりんごのみ絞って、それに対して東京都で検索をかけています。なお、今回使ったfilter関数はスプレットシートの行を絞り込むフィルタ機能を関数にしたものです。=filter(表示範囲,条件範囲1,条件1,[条件範囲2,条件2 ...])といった感じで複数の条件で表示範囲を絞って表示させることができとても便利です。また、この方法のメリットは複雑な条件に対しても実現できることだと思います。例えば、今回の例で言うと都道府県別に各商品の売り上げを持ってくるものですがこれに10000円以上の売り上げのみを持ってきたいなどになった際でも=vlookup(A2,filter(E:G,F:F=B2,G:G>=10000),3,false)と