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

117

ブラウザ(ネットを見るソフト)にデータがあれば、Excelに貼り付けて加工し、利用できる可能性があります。
その事例とコツをまとめてみました。

image

 

PC内にあるデータは利用したい

データの利点は、利用できることです。
データを加工することもできますし、取り込んだり、連動させたりすることができます。
手間はかかりますが、コピペすることも。

データをみながらデータを入力するのは、悲しいことです。

 

PC内にあるデータ、ディスプレイの中にみえるデータは、できる限り活用しましょう。
ただ、その活用にちょっとしたコツがいる場合もあります。
それぞれのデータ連携にふさわしい形のデータならいいのですが、そのことは世の中それほど考えられていません。

CSVデータをダウンロードできるようにしてくれれば、好きなように加工できるのですが、加工されたくないのか手間がかかるからか、そうなっていないことも多いものです。

そういったときは、Excelで加工すれば使えるようになります。

たとえば、ブラウザのデータ(ネット上で見れるデータ)なら、ひとまずコピーしてExcelへ貼り付ければ、活路を見いだせることも多いです。

CSVダウンロードが有料のサービスも、これをやれば、データを活用できます。

ふるさと納税データの加工(ふるさとチョイス→Excel)

 

今回の事例は、ふるさと納税のデータ。
ふるさと納税というサイトのデータで、このデータを活用したいという事例です。

 

ブラウザのデータをExcelへコピペ

ブラウザからダウンロードできない場合、こういった明細を表示して、

ex-it_02

選択してコピーしましょう。
こういった場合、見出し(タイトル)も含めてコピーしたほうがうまくいきます。

ex-it_05

 

コピー後、Excelへ貼り付けると、こうなります。
書式(デザイン)もそのまま貼り付けられてしまうのです。
こういったときは、Ctrlキーを押すと、右下の(Ctrl)というアイコンを選択でき、

image

右側の[貼り付け先の書式に合わせる]を選べば、

image

 

Excel上の書式(デザイン)に従って貼り付けられます。

image

 

今回の事例では、月、日、都道府県、市区町村、寄付金額を活用したいというものです。

確定申告に使います。

 

日付データの加工は、MID、VALUE関数

日付のデータを確認すると、こうなっています。
(冒頭の事例データを少し加工しました)
これじゃダメです。
「2018/01/06(土)」という文字になっています。

image

データとして活用するなら、日付データになっていなければいけません。
貼り付けてくると、こういう問題は起こりがちです。
(CSVデータダウンロードでもこういったことはありますが……)

 

この「2018/01/06(土)」から、月を取り出し、データとして使えるようにします。
泥のついたゴボウを洗うようなものです。
まず、「01」を取り出したいので、MID関数を使います。
=MID(〇、△、□)で、〇のうち△文字目から□文字を取り出すという意味です。
この場合、セルB2(2018/01/06(土))のうち、6文字目から2文字なので、

=MID(B2,6,2)

となります。

結果は、「01」となりました。

image

◆スポンサードリンク

 

この「01」は、やはり文字です。
「月」は、文字ではなく数値でなければデータ活用できません。

この文字を数値にするには、VALUE関数を使います。

これで、「1」となりました。

image

同様に、「日」も処理しましょう。

image

 

Excelの[区切り位置]という機能を使う方法もありますが、このシートを再利用することを考えると、関数で処理しておいたほうが楽です。
他の人のデータ、他の年のデータがあっても、A列からF列に貼り付ければ、同じように処理できます。
(データの数に合わせて数式をコピーする必要はあります)

 

都道府県・市区町村の処理は、LEFT、FIND、MID関数

次に「自治体名」というデータを都道府県と市区町村に分けます。
これもこの後のデータ加工のためです。
幸い、このデータは、都道府県と市区町村に間に半角スペースが入っています。
いいデータです。
[区切り位置]機能を使って半角スペースで区切る方法もありますが、先ほどの日付のときと同様に、この後のデータ利用を考えて関数で処理します。

 

左から△文字を取り出すLEFT関数が使えますが、都道府県は、3文字の場合(北海道、青森県など)と、4文字の場合’(鹿児島県、和歌山県、神奈川県)があるので、工夫しなければいけません。

まずは、半角スペースの位置を関数で指定します。
=FIND(〇、△)で、〇を△から探してその位置を数値で表現するという意味ですので、

=FIND(” “,C2)

なら、半角スペース(” “)をC2(北海道 三笠市)から探してくるわけです。

4文字目に半角スペースがあるので、「4」と表示されました。
image

 

この4という位置があれば、左から4文字目という取り出し方ができるのです。
=LEFT(C2,FIND(” “,C2))
ただ、これだと、「北海道 」(北海道の後に半角スペース)になってしまいます。

4文字ではなく、3文字になればいいのですが、3と指定するわけにはいきません。
それが4のときもあるからです。
そこで、「半角スペースの位置から1をひいた数」と指定します。

=LEFT(C2,FIND(” “,C2)-1)

 

こうすれば、「北海道」と取り出せることができるわけです。

image

 

市区町村も同様に、やります。

image

◆スポンサードリンク

 

ただ、市区町村の場合、都道府県とは、処理を変えなければいけません。
都道府県→左から半角スペースの位置まで

市区町村→半角スペースから最後まで
という処理です。

この「最後まで」という処理にはいろいろと方法がありますが、今回MIDを使いました。
MIDは、=MID(〇、△、□)で、〇のうち△文字目から□文字を取り出すという意味です。
この場合、□文字は市区町村によって変わります。
市区町村名を数えてそれを指定する方法もありますが、多めに指定しておけば、それで十分です。

たとえば、100文字としておきましょう。
これで、「三笠市」を取り出せます。

image

 

金額の加工はSUBSTITUTE関数

 

寄付金額は、10,000円とあります。
これはそのまま使えそうですが、そうではありません。
「10,000円」という文字だからです。
このままでは計算にも使えませんし、データ活用もできません。

円を消せば、数値として使えそうです。
文字を消す場合、その文字を空白に置き換える方法があります。
SUBSTITUTE(サブスティチュート)関数は、

=SUBSTITUTE(〇,△,□)

で、〇に含まれる△を□に置き換えるという意味です。

これを使えば、ひとまず「10,000」という文字になりますので、数値にするため

image

VALUEをつけておきましょう。
これで10000になります。

image

 

これで、集計にも使えるようなデータになりました。

 

 

□スポンサードリンク

Excelデータ→確定申告ソフトへの自動入力(RPA UiPath)

この後のデータ活用の事例として、Excelデータを確定申告ソフトへ自動入力するものをとりあげます。
確定申告ソフト側で取り込めればいいのですが、そんな気の利いたことをしてくれません。

 

RPAのUiPathを使って無料で使える確定申告書等作成コーナーに自動入力していきます。
これもデータ活用の方法の1つです。

RPAについては、こちらの記事を参考にしていただければ。

RPAツールUiPathが日本語化。UiPath再入門「ネットバンクにログインして明細を表示」 | EX-IT
Excelからブラウザへデータを入力することを自動化できます。

 

ふるさと納税を入力する画面からスタートし(ブラウザを開きログインするところからも自動化できます)、[入力する]をクリック、
image

さらに、日付や金額、ふるさと納税先を入力していきます。

image

 

RPAでプログラミングしておけば、Excelにあるデータの分だけ自動的に入れてくれるわけです。

しかも、プログラミング、つまり人の伝え方が正しければ、この入力を間違えることはありませんし、人がやるよりも速く処理できます。

 

image

 

参考に、このプログラムは次のとおりです。
[Excelアプリケーションスコープ]で、データの入ったExcelファイルを指定し、[範囲を読み込む]で該当シートを読み込みます。

 

image

シート名を”Sheet1”、範囲を””にすれば、シートにあるデータをすべて読み取ってくれるので楽です。
右側のプロパティの[データテーブル]でExcelFurusatoという名前を付けておきましょう。
あとで使います。
(Ctrl+Kで変数を登録します。詳しくはこういった記事を参考にしていただければ)

UiPath×Excel。Excelデータを読み取り、ブラウザで交通費検索。無料で使えるRPAツール。 | EX-IT

 

image

 

ブラウザの操作は、UiPathのレコーディング機能で、記録します。

ex-it_41

たとえば、[入力する]をクリックすると、

image

このように記録されます。
これを実行すれば、[入力する]ボタンをクリックすることを自動化できるというしくみです。

image

 

データの入力画面もひとまず記録させてから、加工します。

image

 

[繰り返し(各行)]を使い、「コレクション」欄にExcelFurusatoを指定しましょう。
読み取った範囲で繰り返すという意味です。

 

 

image

こういったデータを読み取っているので、見出し(1行目)で指定できます。
Row(”月”).Tostirng

とすれば、G列の「月」のデータを繰り返し読み取り、ブラウザに入れていってくれるというしくみです。
こうすれば、データがいくつあっても自動で間違いなく入力できます。

Tostringは、文字にするという意味です。
UiPathでは入力するときは文字を使います。

image

 

月と日を入れ、

image

 

項目を選択するところは、ふるさと納税なので、次のように選択し、チェックボックスのチェックも記録し、

image

都道府県名、市区町村名の選択、金額の入力と、Excelのデータから読みとって入れていきます。

image

 

最後は、ちょっと一工夫を。
最後のデータだと、[次へ進む]、まだデータがあるなら[別の寄付金を入力する]という処理です。

[条件分岐]を使って、指定しています。

image

 

(ExcelFurusato.Rows.IndexOf(row)+1).ToString= ExcelFurusato.Rows.Count.ToString

というのは、「ExcelFurusato(Excelのデータ範囲)の今処理している行+1が、ExcelFurusatoの全データ数と一緒なら」

という意味です。

プログラム上、0、1、2、3と数え、2行目だと1となるため、1+1の2にし、3行目だと2となるため、2+1の3にしています。

2行目を処理しているときは、全データ数は4と違うので[別の寄付金を入力する]

3行目を処理しているときは、全データ数は4と違うので[別の寄付金を入力する]

4行目を処理しているときは、全データ数は4と一致するので、[次へ進む](すべて入力完了)

という処理です。
これが、100でも同じ処理をやってくれます。

プログラム、PCにあわせて、最適なデータの形に整える(人が余計なことをしない)というのが、効率化のコツです

RPAの部分はともかく、ブラウザからのデータ加工はよく使いますし、便利ですので、ぜひ使ってみていただければ。

 



■編集後記

昨日は、お客様宅で、個別コンサルティング。
確定申告についてでした。
毎年、家族連れでお邪魔しております。
パパたちは仕事、子どもたちは遊んでいるという感じで。

 

■昨日の「1日1新」

新宿 ホッパオ
とある税務手続

 

■昨日の娘日記
お客様のご自宅で、ビーズ、人形、買い物ごっこなどで遊んでもらい、大喜びでした。
帰りは爆睡。
夜もそうそうに寝てました。