freeeの推移表をExcelのXLOOKUP関数で加工する場合の問題点と解決策

会計ソフトfreee
推移表を Excel で加工するには XLOOKUP 関数が便利です。
その注意点と共にまとめてみました。

 

会計ソフトfreeeの推移表

推移表とは、月別の推移を見るための表です。
その月だけの数字を見るよりも、推移を見たほうが、変化もわかりますし、間違いや異常値もわかります。
会計ソフトfreee
の推移表は他の会計ソフトに比べるとまだ見やすいのですが、めちゃくちゃ見やすいというわけでもありません。

また自由に加工することもできないので、この推移表をExcelに出して加工するのがおすすめです。
Excelにすれば、さらに今後の予測を入れて、決算のときにどのぐらいの着地点になるかということもできます。

(事例の場合は4月までが実績で5月から12月を予測としています）。

会計データは今後のことも考えるときにも使いたいものです。

とはいえ、会計ソフトを見ながらExcelに入力することはやめましょう。

 

freeeの推移表をXLOOKUP関数で連動する場合の問題点

このfreeeの推移表をExcelにするには 、freeeの推移表の画面で、［エクスポート］→［CSV 形式でエクスポート-損益計算書］をクリックし。

次のボックスでは、［項目を整理する］にチェックを入れて［出力］をクリックします。

この後メールが来て、Excelをダウンロードできるようになるのですが、それを待っているのもめんどくさいので、右側の［出力した帳票］をクリックして、

ダウンロードしましょう。

エクスポートをしたら、サクッとダウンロードさせてくれればいいのですが。
以前要望を出したのですが通らず……。

エクスポートしたデータはこんな感じです。

通常であればこういったデータをExcelで連動するなら、フォーマットを準備して VLOOKUP 関数またはXLOOKUP関数を使うと、自動的に資料ができあがります。

今回は新しいほうのXLOOKUP関数を使ってみましょう。
（Excel のバージョンによっては使えない場合があります。）

VLOOKUP関数を使ってはいけない。よりシンプルなXLOOKUP関数を使う。

VLOOKUP関数を使った方法は以前書きました。
ネ申会計ソフトの推移表をExcelで加工する戦い。弥生・freee・MF。

しかしながらこのXLOOKUP関数を使うときには、freeeの推移表のエクスポート形式に問題があるのです。
freee
推移表はこんな感じでエクスポートされます。
こう見ると、売上高が2つ、売上原価が2つあるのが問題なのです。

VLOOKUP 関数、XLOOKUP関数は、指定した列（ここではA列）を上から検索して、たとえば「売上高」を見つけたら、今回の場合は B列にある数字（1月の売上高）を表示します。

しかしながら、売上高も売上原価も上にある、つまり最初に見つけたときのB列は空欄です。
そもそもなぜこうなっているか。
1つでいいやんと思いますが、freeeの推移表をそのままExcelにしているわけです。

Excelにするということは、そこで加工する、再利用するということなので、そのままではなくある程度を整えてほしいところではあります。
これは他の会計ソフトも一緒で、「ひとまずExcelにすればいい」というスタンスです。

この問題を解決するには複数の方法があります。
その1つはエクスポートした推移表のデータの、1つ目の売上高、売上原価を消すこと。

ただエクスポートするたびに、これを消さなければいけませんし、美しくありません。

他の方法のうち、おすすめの方法は次のとおりです。

 

freeeの推移表をXLOOKUP関数で連動するには

よく見ると「売上高 計」なら、ひとつしかありませんし、B列の数字も連動できそうです。

Excelの推移表のフォーマットのを「売上高」ではなく「売上高 計」としましょう。
この場合、「売上高、半角スペース、計」と入れないとエラーが出ます。
これで、XLOOKUP関数を入れてみましょう。

XLOOKUPでどの値を探すかというところで A2を指定します。
このあと、右へコピーすることを考え、F4キーを3回押し、 $A2 となるようにしておきましょう
$がついているA、つまり列を固定し、右にコピーしても必ず A 列を指定するようになります。

Excelの絶対参照のコツ。「＄」（鎖）で固定したいところだけ固定する。

次にそのセル A2の「売上高 計」をどこから探すか。
あらかじめエクスポートしたfreeeの推移表があるシート「data」からA列を指定します。
これも今後右にコピーすることを考えて、F4キーを1回押し、Aに＄をつけておきましょう。。

「売上高 計」を探し当てたら、どの列の数値を表示するかを指定します。
この場合 B列の1月の数字を表示したいので、B列を指定して Enter キーを押せば完成です。

このように売上高の 9728364が連動されます。
（ここでは、千円単位で表示するように設定しています）

他の月にもコピーするとこんな感じです。
1月はB列、コピーすると、2月はC列、3月はD列……と自動的に1つずつ右にずらしてくれます。

後は問題の「売上原価」。
「売上原価」以外のものに数式をコピーし、

「売上高 計」から「売上総損益金額」を引いて、「売上原価」を数式で出します。

これならば毎回手間をかけずに売上原価も連動できるのです。

ただ、この「売上総損益金額」という表示が私は好きではなくありませんし、お客様にもなじみがないものです。

freeeの場合、会計ソフト上は「売上総利益」なのですが、データをエクスポートすると「売上総損益金額」になります。
気になるのでしたら、こんな感じで 「売上総利益」にしましょう。
ただ、このままだとエラーが出るので（freeeの推移表には、「売上総利益」はないため）、XLOOKUP関数で、$A2 の部分を　”売上総損益金額”という固定のものにします。

こうすれば自動かつ、自分の好きな表示に変えられるわけです。

「売上高 計」を同様に「売上高」という表示にもできます。

少々イレギュラーはありますが、このしくみをつくっておけば、
1 会計ソフトをチェック
2 推移表データをエクスポートして開き、コピーし、Excelファイルに貼り付け
3 該当月に数式をコピー（5月のデータなら4月の数式を5月にコピー）
で、毎月、自動的にExcel推移表ができあがり、決算予測その他の資料に使えます。
（さらに私は、1と2をRPA UiPathで自動化しています）

freeeのExcelデータダウンロードを自動化するRPA（UiPath）

ぜひ使ってみていただければ。

 

 

---

■編集後記
PS5、引き続き外れ続けております。
もういいやって思えてきました。
（そう思うと当たることも期待しつつ）

■「1日1新」
LUMIX Webcam Software

■娘(3歳8ヶ月）日記
保育園に着いたら、ひとりでバックをかけたり、着替えを引き出しに入れたりし、それを窓から見届け、窓越しにタッチしています。
着替えをちゃんと折りたたんで入れているのは、ママを真似ているのでしょう。
パパには、ない部分ですし。