よくある、一つずつコピペするしかなさそうな作業。「どうにか楽にできないか？」と、トライしてみました。

他にもやり方があるかと思うのですが、私のやってみた方法です。

長文（大作！）なので、少しずつ読んでいただけたら嬉しいです🙂

お客様からのご依頼内容

180店舗のマルエツの店舗名リスト（Excel）に対して、都道府県と、住所を入力してほしい。

店舗の住所は以下URLから拾ってほしい。

https://www.maruetsu.co.jp/index.php/shop　（2019年9月時点）

リストのイメージ　※実際の案件のときのデータではありません。

一番最初に思い付くのは、「リストの店舗名をコピーしてWEBページで検索をかけ、見つかったところの住所をコピペでリストに転載する」という方法です。が、それをもっと楽にできる方法がありそう。。。

考えたことと、やったこと

これから私が考えたことと、どういう手順を踏んだのかを書いていきますが、よかったらぜひ、自分だったらどうするかなと考えてみてから読んで頂けたらと思います＾＾

では、スタート！

まず最初に、WEBの店舗情報をどうにかリスト化できれば、店舗名で一致を取ってVLOOKUP関数でデータを引っ張って来られるなと、考えました。

なので、次にWEBの情報をリスト化する方法を考えようと思い、サイトを見に行くと、表記にはある程度法則性がありました。

ここからどうやってリストを作ろうかなと思ったときに、試しに一店舗分を丸ごと範囲選択して、Excelにコピペ（値貼り付け）してみました。

すると、このようになりました。

これはいけるのでは・・・？と、今度は全店舗のデータを貼り付けたらどうなるのかと試してみると、同じ形式で下に続いて全店舗分のデータがA列に貼り付けられました。（やったー！←心の声）

間に大量に入ってくる空行が邪魔だったので、まずそれを削除しようと思い、「条件を選択してジャンプ」で、空白セルを選び、空白セルだけが選択された状態で「削除」→「上方向にシフト」を選択して、空白を詰めました。

あとはフィルタ機能を使ってうまいこと絞り込めばいけるのではないか！？

一番上に一行追加して適当な項目名を入力し、フィルタを有効にします。

フィルタの「指定の値で終わる」を使い、店で終わる行を絞り込むと、298行のデータに、

フィルタの「指定の値で始まる」を使い、〒で始まる行を絞り込むと、299行のデータになりました。

・・・店で終わらない店名が、1つある・・・。😱ガーン

その店名を探し出さないと。。

店で終わらない店名を絞り込みたいので、以下のように設定してみると・・・

店で終わらない店名を発見！！！

「店）」で終わる店名が１つありました。

営業時間の情報にも店）で終わるものがあったので、1562行と1975行は削除しました。

これで改めて絞り込みを行います。

フィルタの「指定の値で終わる」を使い、店で終わるもしくは店）で終わる行を絞り込むと、299行のデータになりました。

フィルタの「指定の値で始まる」を使い、〒で始まる行を絞り込むと、299行のデータになりました。

（よし！←心の声再び）

絞り込んだそれぞれのデータを新しいシートに列をわけて貼り付けます。

こんな表が出来上がりました！

次は住所を、都道府県とそれ以降にわける必要があります。

まず、郵便番号はいらないので、それを分けるために、区切り位置の機能をつかって列を分けます。

住所の値が入っている範囲をCtrl＋Shift＋↓で範囲選択してから、区切り位置を選択して

「スペースによって右または左に揃えられた固定長フィールドのデータ」を選択して

次へ→次へ→完了

すると、列が分かれました！

郵便番号はいらないのでB列は削除します。

次は都道府県を分けないといけないのですが、これに関しては以前読んだ『たった1日で即戦力になるExcelの教科書』の173ページにその方法が載っていたことを覚えていたので、本を片手に関数を組んでみました。

※関数の詳しい解説が知りたい方はぜひ『たった1日で即戦力になるExcelの教科書』を、読んでみてください＾＾（Excelブートキャンプ関数編の課題図書です）

使用したのはIF関数、MID関数、LEFT関数、RIGHT関数とLEN関数です。

（ネット検索すると、親切な解説ページがたくさんあります。ちなみに、前述した『たった一日で…』の著書　吉田さんのページURLはこちらです→https://sugoikaizen.com/excel_list/if/）

ここまで出来ました！！！

次は、店舗名です。

お客様からもらっているリストには、店舗名に、「店」が含まれていませんでした。

文字列置換で「店」を消すことは簡単ですが、それだと、末尾以外に「店」という文字が含まれていた場合にも消されてしまいます。

ここで、さきほどの住所を分けた関数を参考に、最後の文字だけを消す関数を考えて、組んでみました。

使用したのはLEFT関数とLEN関数です。

（もし関数の事を勉強したいけれど自分一人ではなかなか・・・と思う方は、ぜひExcelブートキャンプ関数編への参加をご検討ください😘Excelブートキャンプは随時開催予定です！宣伝ですみません😅）

これで、求めていた表が完成しました！

あとは、この表から店舗名で一致をとって、住所を取得するVLOOKUP関数を、お客様からもらっているリストに組んで、完成！

と、そんな簡単には終わらないのですねー。

VLOOKUP関数を入れたあと、フィルタの選択肢を確認すると、＃N/Aというエラーがありました。

これは、店舗名が一致するデータが、見つからなかったということです😥

お客様からもらったリストと、WEBの店舗名が完全に一致しないものが、いくつかあるようです。

これに関しては、一つ一つ確認し、店舗名の一部で検索をかけてみて一致するものを探して、手作業で穴埋めするしかありませんでした。

その数は26件。

180件の手作業よりは、圧倒的に楽な数だったと思います😅

この26件分は手作業で穴埋めし、これでやっと、リストの完成です！

詳しい手順まとめ

①WEBサイトで範囲選択

②Excelに値貼り付け

③空白ジャンプ（「ホーム」→「検索と選択」→「条件を選択してジャンプ」→「空白セル」→「OK」）をして、削除して上詰め（選択されたセル上で右クリック→「削除」→「上方向にシフト」→「OK」）

④フィルタを設定（「ホーム」→「並び替えとフィルタ」→「フィルタ」）

⑤店で終わる ＝ 298（フィルタ「テキストフィルタ」→「指定の値で終わる」→「店」→「OK」）

⑥〒ではじまる ＝ 299（フィルタ「テキストフィルタ」→「指定の値で始まる」→「〒」→「OK」）

⑦店の数が一つ足りないので、店でおわらない店舗名をさがす（フィルタ「テキストフィルタ」→「ユーザー設定フィルター」→「店」→「を含む」→「AND」→「店」→「で終わらない」→「OK」）

→「店）」で終わる店名を一つ発見！これが原因

⑧「店）」で終わる　＝　3

→「閉店）」とか「開店）」も一緒にひっかかっていたので、この行を削除　

⑨店で終わるOR店）で終わるで絞り込んだものを別のシートにコピペ（フィルタ「テキストフィルタ」→「指定の値で終わる」→「店」→「OR」→「指定の値で終わる」→「店）」→「OK」）

⑩〒で始まるで絞り込んだものも⑨の隣にコピペ（フィルタ「テキストフィルタ」→「指定の値で始まる」→「〒」→「OK」）

⑪住所を区切り位置で二つにわける（住所の値が入っている範囲をCtrl＋Shift＋↓で範囲選択してから、「区切り位置」→「スペースによって右または左に揃えられた固定長フィールドのデータ」→「次へ」→「次へ」→「完了」）

⑫関数で都道府県とそれ以下に分ける

⑬関数で店名から店をとる

⑭お客さま作成リストにVLOOKUP関数で、必要情報を持ってくる

⑮フィルタで、エラーで絞りこんで、エラーの箇所は手作業でWEBを検索して埋めていく（部分的な文字を切り出して検索）

我ながらよくやった！と思っていますが(笑)、こんな方法もあるよ、と思いつく方がいたら、ぜひ教えてください👍

■あらかじめ知っておくといいなあと思うExcel機能

• ・条件を選択してジャンプ
• ・フィルタ（始まる、終わる）
• ・区切り文字
• ・都道府県を分ける関数
• ・VLOOKUP関数

■今回ひらめいたこと

• ・WEBをまるっとコピペしたらどうなるだろう？
• ・店名から「店」をとるなら、置換機能だと、もしかすると消してはいけない店も消してしまうかもしれないから、最後の一文字を消すようにしたいなぁ。
  →そうだ、都道府県でやったことが応用できるのでは！？

ひらめくと、嬉しいですよね☆

余談ですが、私は、知識が増えるほど、自分の中に持てる『ひっかかり』も増えていくのを感じています。

ひっかかったものをいくつか試してみて、そこからひらめきが産まれ、

そのひらめきで作業時間を半分にできてしまうことだって、あります。

だからこそ、自分の中にたくさんの『ひっかかり』を作るために、色々な情報に触れておくようにしたいなと、思っています。

（家事と育児に追われてあっという間に一日が過ぎるので、本とか全然読めないですが、ひまわり通信で紹介されるショートカット情報などは必ず目を通して、ネットで触れられる情報には触れていくだけでも、違うかなと思います😄）

ちなみに今回の作業は結局、色々と試行錯誤しながらの作業だったので、時間は1時間ほどかかりました。

手作業よりも、トータルで時間がかかってしまうとしても、関数やマクロで処理すべき理由

それは、正確だから。この一点につきます。

例えば今回の作業を、関数などは使わずに全て手作業で実施した場合、ショートカットなど駆使して一店舗当たり20秒でできれば、1時間で終わります。

ただし、コピペを駆使したとしても、人の手で作業をする限り、貼り付け先を間違えてしまう可能性は０ではなく、確実なデータを作るためにはダブルチェック作業をしないといけません。

そのダブルチェックに同じだけかかれば、2時間の仕事となります。

一方、上述した手順ならば、26店舗分のダブルチェックだけで済みます。

（そもそも関数が間違っていたら元も子もないので、そのチェックは必要だと思いますが、一つ分の関数だけチェックすれば、あとはそれをすべてに反映すればいいだけなので、やはり時間はそれほどかからないと思います。）

作業時間だけではなく、その正確さもぜひ加味して、どうやって作業するのかを選択していってほしいなと、思います。

おわりに

じつは、このお仕事、普段からマクロを作り慣れた人ならマクロで、40分くらいの作業時間で完成します。

（※実際の納品はマクロで行いました）

でもふと、「これ、マクロでやらなくてもできたんじゃないか・・・？」と思い、やってみたら、できちゃいました😊

マクロを覚えるのはなかなかハードルが高いと感じる人には、Excelの機能だけで完結するアイディア勝負もいいなと思って、今回共有させて頂きました。

みなさんのお仕事の、何かの『ひっかかり』になれれば、幸いです😌

長文、読んでいただき、ありがとうございました💡

YPPメンバー　小川可亜那