こんばんは!
G.O.D.ツール担当の北野です!

昨日のサッカーW杯、
見ましたか??

サランスクの奇跡だ!
と盛り上がってますね!

僕は移動の車の中で聞いてました。

勝ったことは
もちろん印象に残ってるんですが、
他にも印象的な話がありました。

監督の交代が4月でしたっけ。

試合開始前、
準備期間が足りなかったのではないか、という話題。

解説の岡田元代表監督が
「自分にどうしようもないことを考えるよりも、
自分ができることに力を注ぐべき」
的な話をしていたのが印象に残ってます。

過去は変えられないですし、
自分にどうしようもないことを考えても仕方ない。
その通りだな、と思いました。

前回はゴッドハンドAmazon道場のお話をしました。

その前まで連載していました、
利益計算シートの話に戻りたいと思います!

▼今回のテーマ
「為替データを使って、ドルを円に換算する」

前回までの作業が完了していると、
こんな感じの画面になっていると思います。

前回は、為替レートの情報を、
GOOGLEFINANCE関数を使って取得しましたね。

この情報を使って、
販売合計のドルから、
販売合計の円を算出しましょう!

まず、
販売合計の列の右側に1列追加しましょう。

ドルの販売合計と列タイトルを分けるため、
販売合計($)と
販売合計(¥)にしました。
この名前は、お好みで付けてください。
 

この販売合計(¥)の列に、
販売合計($) * 販売日の為替
という計算式を入れたいですね。

為替レートのシートの情報を参照するのに、
VLOOKUP関数を使いましょう。

VLOOKUPを使おうと思うと、
参照する値が必要です。

為替レートのシートには、
・日付
・為替レート
この2つの情報しかなく、
為替レートの情報を取り出したいので、
日付で参照する必要があります。

なので、
販売日の列も追加しましょう。

お好みの位置で良いですが、
僕はA列の「Sales Record Number」の右に1列追加しました。

そして、
この販売日の列に、
SoldListingのシートから
Sale Dateのデータを引っ張ってこれば
販売日の入力は完了、

、、、

するのですが、

Sale Dateの英語表記の日付だと、
スプレッドシート上では「日付」として扱ってくれません。

「文字列」として認識されてしまい、
為替レートからデータを引っ張ってくるための「日付」のデータとしては使えないのです。。

この「文字列」の日付情報を「日付」として扱えるようにしなければなりません。

ややこしくなってきましたね。。
がんばってついてきてくださいね!

ここで使える関数は、DATE関数です。
https://support.google.com/docs/answer/3092969?hl=ja

この関数を使って
=DATE(年, 月, 日)と入力できれば良さそうです。

そしてさらに、
LEFT関数、RIGHT関数、MID関数を使えば、
Sale Dateの文字列から日付の文字列を抽出することができます!

LEFT関数
指定した文字列の先頭から部分文字列を返します。
⇒https://support.google.com/docs/answer/3094079?hl=ja

RIGHT関数
指定した文字列の末尾から部分文字列を返します。
⇒ https://support.google.com/docs/answer/3094087?hl=ja

MID関数
指定した文字列の指定位置から部分文字列を返します。
⇒ https://support.google.com/docs/answer/3094129?hl=ja

LEFT関数で左から3文字 = 月の省略形を表す3文字

RIGHT関数で右から2文字 = 年を表す2文字

MID関数で5文字目から2文字 = 日を表す2文字

LEFT関数で抽出した省略形を、
月を表す数字2文字に変換して、

DATE関数に引数として渡してやれば、
販売日を日付として設置することができます!

文字だけで見ると、
結構ややこしそうですね。。

考えるよりも、
実際にやってしまいましょう!

B2に完成形のDATE関数を入れたいので空けておきます。

B3にLEFT関数で3文字抽出する数式を入れてみます。
=LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)

B4にRIGHT関数で2文字抽出する数式を入れてみます。
=RIGHT(VLOOKUP(A4,SoldListing!A:AN,23,false),2)

B5にMID関数で5文字目から2文字抽出する数式を入れてみます。
=MID(VLOOKUP(A4,SoldListing!A:AN,23,false),5,2)

B3で抽出した文字列を数字に変換します。
Janだったら1、Febだったら2、というようなIF関数を並べていきます。
数式はとても長くなりますが、やっていることは12回IF関数を繋げているだけです。
=IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Jan”,”1″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Feb”,”2″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Mar”,”3″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Apr”,”4″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”May”,”5″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Jun”,”6″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Jul”,”7″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Aug”,”8″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Sep”,”9″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Oct”,”10″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Nov”,”11″,IF(LEFT(VLOOKUP(A3,SoldListing!A:AN,23,false),3)=”Dec”,”12″,””))))))))))))

これで、年,月,日の数字が出揃いました!
これらの数字を、DATE関数に入れてやれば、
販売日が出来上がります!
B2にDATE関数を入れましょう。
=DATE(“20″&RIGHT(VLOOKUP(A2,SoldListing!A:AN,23,false),2),IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Jan”,”1″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Feb”,”2″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Mar”,”3″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Apr”,”4″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”May”,”5″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Jun”,”6″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Jul”,”7″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Aug”,”8″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Sep”,”9″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Oct”,”10″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Nov”,”11″,IF(LEFT(VLOOKUP(A2,SoldListing!A:AN,23,false),3)=”Dec”,”12″,””)))))))))))),MID(VLOOKUP(A2,SoldListing!A:AN,23,false),5,2))

年のところ、頭に”20″を加えてます。
“18”だけだと、1918年になってしまうためです。

VLOOKUPの参照元はA2になってますのでご注意ください。

個人的には、
VLOOKUP関数が繰り返し出てくるので、
変数に置き換えられないかな、とか思うのですが、、
良い方法ご存知な方いらっしゃいましたら、
感想フォームから教えてください。

これで、為替レートから情報を引っ張る準備ができました!

K2に販売日の為替レートを求める数式を入れてみましょう。
=VLOOKUP(B2,’為替レート’!A:B,2)

これが合っているか確認してみるため、
実際に販売日の為替レートを
為替レートシートで確認してみましょう。

サンプルの場合、2017/12/05が$112.499だったらOKですね!

実際に見てみると…

12/4のレートがヒットしてますね?!

これは多分、近似値を取ってくるからだと思います。

2017/12/04 23:58:00 と
2017/12/05 23:58:00

12/5(の0:00:00)に近いのは、
12/4の方です。
なので12/4のデータを取得しているんだと思います。

解決策は、日付に+1をしてやることです。
=VLOOKUP(B2+1,’為替レート’!A:B,2)
これで、販売日の為替レートが得られます。

為替レートを取得できたので、
あとは販売合計($)と掛け算をしてやれば、
日本円の販売合計額が算出できますね!
=J2*VLOOKUP(B2+1,’為替レート’!A:B,2)
(表示形式は、通貨(日本円)にしておきましょう)

販売額を日本円で求められたので、
あとは、売上から経費を引いてやれば利益が求められます!
P列に利益の列を作りましょう!
=K2-L2-M2-N2-O2

以上で、利益計算シートが完成です!
お疲れ様でした!

あとは、
SoldListingのデータを最新情報に更新して、
経費を入力するだけで利益計算が可能です!

今回の為替レートのところが、
何気に一番大変だったかもしれませんね!

1度作ってしまえば
日々の運用はとてもラクになりますので、
ぜひ最初から読み返して
利益計算シートを作ってみてくださいね!

◆紙さんと対談しました!
http://www.tr-imagination.com/q1d8

◆メルマガ・動画への感想はこちらへ!
http://www.tr-imagination.com/impressions

 

 

 

【無料】はじめてのeBay、Amazon 輸出ビジネス無料セミナー

輸出ビジネスは誰でも簡単に始められます。
副業から始めましょう。
給料ではないお金を稼ぎましょう。
パソコン1台で、いつでもどこでもビジネスができるようになります。

パソコン初心者でもOK!
ビジネス初心者でもOK!
英語は中学1年生レベルで十分です!

 

【丸投げ】eBayリミットアップ交渉代行サービス(初回 1,980円)

弊社は、【eBayリミットアップ交渉】のエキスパートです。
ジーオーディに【丸投げ】でご依頼ください!

2018年5月実績:54名様
<10品 500ドルの初心者アカウントを35倍アップしました!>

→350品 17,500ドル(35倍) 5名様
→200品 10,000ドル(20倍) 3名様
→100品 5,000ドル(10倍) 21名様
→ 50品   250ドル (5倍) 13名様
→ 20品   100ドル (2倍) 12名様