※freeeの月次推移表…
会計ソフトのデータをExcelで加工する場面では、XLOOKUP関数を使うことがよくあります。
ただ、freee会計の月次推移表を使う場合、そのままではうまく連動しないケースもあります。
今回はその原因と対処法をまとめてみました。
XLOOKUPは「マスタ」をつくるのが基本
Excelを使うときによく使っている関数の1つがXLOOKUPです。
XLOOKUPは、マスタとなる別シートを用意しておくことで、
検索値（今回の事例では「借方科目」）を、マスタから探し出して、
同じ検索値をもつデータを連動させることができます。
もしマスタに該当するものがない場合は、データも連動できないので、こういう場合は「#N/A」と表示されます。
そのときは連動できるようにするために、その都度マスタを更新してメンテナンスしておく必要はあります。
経理でExcelを使うなら、XLOOKUPは親和性のある関数ですし、マクロやAIを動かすよりも効率よく資料をつくることもできます。
実際、freee会計にインポートするデータは、毎月このしくみでつくっています。
ただ、そんなXLOOKUPでも、うまく連動できないこともあります。
検索値を1つに絞りきれないようなケースです。
freee会計の月次推移表では検索値が一致せず連動しない
今回の事例は、freee会計からエクスポートできる「損益計算書 – 月次推移表」です。
freee会計では、このように月次推移表をエクスポートできます。そのCSVデータをエクスポートして、
Excelのシートに、月次推移表をコピペしておき、
データを連動させたい別シートには、B3セル「売上高」と入力しておき、この「売上高」を検索値にして、XLOOKUP関数で月次推移表のデータを連動させることができます。
ただ、ここでその連動がうまくいかないのがfreee会計の月次推移表。
このような「0」表示になり、連動してほしい金額が表示されません。
これはなぜかというと、キーとなる検索値が一致していないためです。
月次推移表をよく見ると、「売上高」の行には金額が入っていない列があります。
XLOOKUPは最初に一致した行を参照するため、金額が入っていない行を拾ってしまい、「0」と表示されてしまいます。
つまり、freee会計の月次推移表のCSVデータ、そのレイアウトが原因。
3行目の売上高、その下の方にある販売管理費の行もブランク…なくてもいいのにと思うのですが…（マネーフォワードの月次推移表も似たようなものですけどね）。
そのため、XLOOKUPそれ自体は何も間違っていないということです。
そうなると、検索値をどうやって指定してあげるかという話になってきます。
freee月次推移表をXLOOKUPで連動させる2つの工夫
それではXLOOKUPの検索値をどう指定してあげるか。
考えられる2パターンをあげてみました。
「売上高」で検索値できるようにする
まずは別シートにコピペしたfreee会計の月次推移表をそのまま使わない方法です。
ちょっとだけ加工します。
コピペする別シートのA列に、このように「売上高」と入れておき、B列以降に月次推移表を貼り付けるようにする方法です。
A列「売上高」を検索値にしても、このように連動することができます
とはいえ、加工する手間は発生します。
それに、「元のデータはそのまま置いておくだけ」というのが、資料をつくる上では鉄則とも言えます。
そこで、次の方法を探ります。
検索値を「売上高計」にする
XLOOKUPは、
=XLOOKUP(検索値, 検索範囲, 返す範囲）
という数式で、返す値を連動させることができる関数なわけですが、数式のはじめに入れる”検索値”は文字列にすることもできます。
つまり、「売上高」の入力があるセル（$B3）を入れなければ、返す値を指定できないということではないのです。
そこで、マスタ（＝freee会計の月次推移表）シートを見てみると、7行目「売上高計」の金額が連動できれば目的は達成できることがわかります。
そこで、XLOOKUP関数の数式の検索値を”売上高計” としてみると、
「売上高」にその合計額を連動することできました。
8月以降の列についても、セルをコピーしていけば連動されます。
LOOKUP系の関数は、経理でExcelを使うなら相性がいいものですし、使えそうな場面を見極めつつ積極的に使っておきたい関数です。
XLOOKUP、VLOOKUP、HLOOKUPについては、過去にこんな記事も書いていますのでよろしければ。
共通する大事なことは、元データは触らない、マスタとなるシートをつくったら都度カスタマイズしておく、試すことを恐れない、といったところかと。
XLOOKUPは便利な関数ですが、元データの構造によっては思ったように動かないこともあります。
ただ、そんな場合でも、関数を疑うのではなく、データの持ち方や検索値の指定を見直してみることが大切です。
元データの特性を理解しながら、うまく付き合っていきたいところです。
■編集後記
昨日はオフ
朝の家事とタスク、習慣を
家族で散歩しつつ、ベッドの視察と
息子のカバンの買い物に出かけました
気温9℃でも日差しがあって暖かく散歩日和
雪解けのあとで道路に残った砂ぼこりはすごいですけどね
マスク着用でなんとか
■昨日の1日1新
・Gemimi Liveで息子のGarten Of BANBAN（switch版）を実況翻訳
・息子とベッドの視察
・麺がうまい醤油らぁ麺（エースコック）
■息子（11歳）
近所の家具屋さんでベッドの視察に
進学のタイミングで息子用のベッドも新調しようかということで
色（パイン材の明るい色）も決まりました。
ただ、ベッドのサイズをどれにするかが決まらず…
部屋の寸法を測ってくるのを忘れてしまって
シングルかセミダブルか
メーカー側の事情でシングルの幅が△7cm狭くなっていたので
息子はどっちでもいいと言ってますが
大は小をということでセミダブルかなぁとは思ってますが…
