Excelで文字を加工（文字列操作）。楽天ふるさと納税の処理。

Excelで文字を加工するパターンを知っておくと、役立ちます。
事例をあげて、そのパターンをまとめてみました。

※Excelの画面　by　SIGMA fp

 

データの文字をExcelで打ち直すのは避けたい

Excelを使うとき、紙の書類だと、入力しなければいけないので、絶対に避けたいものです。
しかしながら、紙ではなくデータであっても、そのままではExcelで使えない場合もあります。

せっかくデータなのに、打ち直すなんてことは同じく避けたいものです。
たとえば、こういった データで、

・月と日のデータが必要
・都道府県と市区町村のデータが必要な場合。
・10,000円という文字ではなく、10000という数字が必要（「10,000円」では計算に使えない）

ということがあり、目の前にデータがあるのに、打ち直すのは悲しいことです。

文字加工のパターンを覚えておきましょう。
または、この記事の存在をそのときに思い出していただければ。

そもそも、加工をしなければいけないケースとは、Excel からデータを読み取ってプログラミングで効率化するときです。
私は今回取り上げた事例である、楽天ふるさと納税のデータも加工して、下ごしらえし、RPA UiPath で自動処理（確定申告コーナーというサイトに自動入力）しています。

Excel マクロを使う場合も、こういった下ごしらえは必要です。

また、売上、経費といったデータを加工して、別ソフトに取り込むというケースでも、下ごしらえが欠かせません。

 

文字列加工のパターン

Excelでの文字列加工のパターンは、マクロを除くと、置換、区切り位置、関数の3つです。

置換

置換とは、特定の文字を他のものに置き換えること。

たとえば、「10,000円」で、「円」を空白に置換すれば、10000として数字として扱えます。

空白が邪魔な場合にも使うと便利です。
（Excelでは空白を文字としてとらえています）

ただ、この置換は、毎回手でやらなければいけません。

 

区切り位置

区切り位置は、特定の文字や記号で、そのセルの内容を分けること。
たとえば、曜日まで入ってしまっている日付は、Excel 上で日付データとして使うことができません。
曜日の最初の（で区切るようにすれば、日付データのみを取り出すことができます。

また、都道府県と市区町村の間にスペースが入っていれば、区切り位置で［スペース］を指定し、2つに分けることができるのです。

［寄付額：］というようなものが入っている場合も、：で区切って、データを取り出すことができます。

ただ、この区切り位置も置換と同様、毎回手作業をしなければいけません。

 

関数

関数を使えばあらかじめ入れておき、必要なデータ量だけコピーして使えば、自動的に処理ができます。
たとえば日付から、「月」を取り出すなら、使う関数はMONTHです。
「日」ならday、「年」ならYEARを使えば、かんたんに取り出せます。

一方、曜日入りの日付から「月」を取り出すなら、MONTHは使えません。
そもそも日付に使う関数だからで、「2017/12/23（土）」は文字だからです。

この場合は、MIDで、6文字目から2文字と指定して、取り出すことができます。
（この場合、一桁の月が、01、02、03…というパターンだから通用しますが、そうでない場合は、もっと複雑になります）

 

都道府県と市区町村が同じセルにある場合で、スペースで区切られているときは、まだましです。
そこで区切るよう関数で指定できます。
（区切り位置のほうが手軽ですが）

FINDでそのスペースを見つけ、その位置までをLEFTで抜き出すことで、このように取り出すことができるのです。

市区町村は、スペースの位置の次から最後まで取り出すという処理にしています。
取り出す文字数は、都道府県・市区町村の文字数ー都道府県の文字数という計算式です。

 

都道府県と市区町村が区切られていない場合は、
・都道府県名の神奈川県、和歌山県、鹿児島県が4文字
・それ以外は3文字
であることを利用して、都道府県：市区町村のうち左から4文字目が「県」だったら、左から4文字取り出し、そうでなかったら左から3つ取り出すという処理を使います。

あとは、都道府県名の文字数を把握し、その次の文字から、必要な分を取り出すというしくみです。

ケースによっては、数式がかなり複雑になりますが、一度入れてしまえば自動的に取り出すことができるのです。
（場合によってはマクロのほうがかんたんな場合もあります。別記事を書くかもしれませんが。）

事例として、楽天ふるさと納税の処理をあげてみました。
ふるさと納税に関して、ふるさとチョイスの処理はこちらの記事にまとめてあります。

ブラウザ（ふるさと納税）からExcelへデータ加工事例とコツ＆RPA UiPathで自動入力

RPA でやる前の下ごしらえです。

楽天ふるさと納税のデータ処理

「世の中のウェブサービス・ソフトは、すべからく CSV データを提供すべき」という法律をつくって欲しいぐらいですが、なかなかそうもいきません。
楽天ふるさと納税もそうです。

申込完了メールをGmail から抽出するという方法もできなくはありませんが、サイトのデータを使ってExcelで加工するのであれば、寄付履歴をコピーしてExcelに貼り付けるしかありません。

画像も含めて貼り付けられるので、［貼り付けのオプション］をクリックし、次のアイコンをクリックしましょう。

その後も、こんな感じでデータとして、とても使えるものではありません。
ただ規則性はあるので 寄付額がある行に、データを集めるようにします。
データの一番右が円、つまり寄付額があるなら、処理するような式をつくり、日付はそのまま連動（セルA5）し、

MONTHやDAYで、取り出せます。

都道府県・市区町村はひとまず取り出し、

前述した方法で、都道府県と市区町村をわけましょう。

寄附金額は、文字もまざっていてやっかいです。
パターンとしては、MIDで、左から6文字目から6文字取り出せば、金額が4桁のときも5桁のときも取り出せます。

さらには空白が入っているので、この空白をSUBSTITUTE（サブスティテュート）で、置き換えて、取り除きます。
この空白はただの半角スペースではないので、実際のデータからコピーして式に貼り付けるようにしましょう。
最終的には、文字列を数値に変える Value 関数を入れて、整えます。

こういった数式をつくっておいてすべての行にコピーすれば、整理されたデータが手に入るのです。たまにパターンが崩れることもあるかもしれませんが。

データ処理、文字列処理ができるようにパターンを押さえておきましょう。
そして自分がつくるまたはお願いするようなデータ形式は、このような文字処理をしないでいいように整えたいものです。

数字（数値）、文字、日付と役割を明確にし、分けて使う可能性があるものはセルを分けるというのも大事です。
同じ文字の加工でも、くっつけるのはかんたん（＆でつなぐ）なのですが、分けるのは手間がかかりますので。

---

■編集後記

昨日は税理士業。
確定申告を。
RPAを改善しつつ。

■「1日1新」
Nik Collection 3 購入
SOELU 滝汗ダンスエクササイズ

■娘(3歳11ヶ月）日記
昨日から保育園で進級。
先生はそのままで、部屋を変わりました。
なんだかうれしそうでした。
また違うおもちゃもあったようで。