第4回はvlookup関数を使って材料を取得していきたいと思います。
今回のシリーズでは妄想でラーメン屋のレシピを作って行こうと思っています。スプレッドシートの関数やgas(Google Apps Script)の勉強メモとして書いて行こうと思います。
材料名を取得する前にレシピデータを作る
00原本をコピーしてシート名：”01野菜”を作る
内容は以下
00原本をコピーしてシート名：”02肉”を作る
内容は以下
材料を取得していく
今回は以下の赤く囲った場所にデータを取得していきます。
まずは関数を使用する予定のセルをオレンジ色に塗る
VLOOKUP関数を使って材料名を取得する
VLOOKUP関数のお勉強メモ
N列、O列、P列を選択して左に3列挿入
VLOOKUP(検索キー, 範囲, 指数)
P19セルに以下を入力
=VLOOKUP(B18,R19:V118,4)
エラー、該当なし (not applicable:#N/A)ですね。
イメージ
VLOOKUPさん、ちょっと探し物をお願いしたいです。
P18=VLOOKUP()
うんいいよ
分類コードはここに書いてあります(B18)
この資料の4列目から探してほしいです(R19:V118,4)
=VLOOKUP(B18,R19:V118,4)
調べようとしたけど、分類コード(B18)に何も書いてないよ。
該当なしっ！！！！(#N/A)書いておくよ
すみません。
分類コード(B18)書き忘れてました。
分類コードを書くの忘れていました、すみません。
分類コード(B18)は”01″です。
分類コード”01″は食材分類名”野菜”だよ。
ありがとうございました。
セルの絶対参照
p18をコピーまたはオートフィルで複製するとVLOOKUP(検索キー, 範囲, 指数)の範囲がずれていってしまうのでずれないようにします。
=VLOOKUP(B18, $R$19:$V$120, 4)
F4キーを押して$を付けるとずれません。
#N/Aを無視する
=IFERROR(VLOOKUP(B18, $R$19:$V$120, 4), "")
今度また私が分類コードを書くの忘れていたら”#N/A”じゃなくて””(空文字)を書いておいてください。
了解。
VLOOKUP関数とIMPORTRANGE関数で別スプレッドシートからデータを取得する
品名（D18セルに）書くやつが以下です。
=IFERROR(VLOOKUP(C18,IMPORTRANGE("XXXXXXXXXXXXXXXXXXXXXXXXX",B18&P18&"!A1:E1000"),2),"")以前IMPORTRANGE関数を使用して別スプレッドシートからデータを取得しましたね。忘れた人は下のリンクで見れます。
IMPORTRANGE関数はここで説明してあります。
以下のデータの鶏ガラを取得したい
D18に入力したい
ちょっとややこしいですが私の脳内イメージで説明していきます。
VLOOKUPさんまたまた探し物をお願いしたいのですが。
VLOOKUP(検索キー, 範囲, 指数)
検索キーはここに書いてあります(C18)
=VLOOKUP(C18,
“001”ですね。どこの資料（範囲）から探せばいいですか？
いま資料が手元にないんです。
IMPORTRANGEくんに資料を取ってきてもらうのでちょっと待っててください。
IMPORTRANGEくん、お願いがあります。
IMPORTRANGE(スプレッドシートのURL, 範囲の文字列)
なんでしょうか？
資料室（レシピデータのURL）に行って”02肉”（B18&P18）っていう資料があったら、全て（&”!A1:E1000″）コピーしてVLOOKUPさんに渡してもらいたいです。
IMPORTRANGE(“XXXXXXXXXXXXXXXXXXXXXXXXX”,B18&P18&”!A1:E1000″)
行ってきます。
見つけたぞ。
ただいま。
VLOOKUPさん資料のコピーです。
=VLOOKUP(C18,IMPORTRANGE(“XXXXXXXXXXXXXXXXXXXXXXXXX”,B18&P18&”!A1:E1000″)
IMPORTRANGEくんありがとう。
自分さん、指数（材料名）は何行目ですか？
2行目が材料名なのでそこの行にコード”001″(C18)と一致するならメモをよろしくお願いします。
=VLOOKUP(C18,IMPORTRANGE(“XXXXXXXXXXXXXXXXXXXXXXXXX”,B18&P18&”!A1:E1000″),2)
“001”、、、ありました”鶏ガラ”ですね。
D18セルに書いておきます。
ありがとう。
先ほども言いましたが、もし見つけられなければ空でお願いしますね。
IFERROR(値, エラーの場合の値)
=IFERROR(VLOOKUP(C18,IMPORTRANGE(“XXXXXXXXXXXXXXXXXXXXXXXXX”,B18&P18&”!A1:E1000″),2),””)
私のイメージではこんな感じです。
vlookup関数を使って単位を取得する
F18セルに単位を取得する。
材料名の応用ですね。
材料名
=IFERROR(VLOOKUP(C18,IMPORTRANGE("XXXXXXXXXXXXXXXXXXXXXXXXX",B18&P18&"!A1:E1000"),2),"")単位
=IFERROR(VLOOKUP(C18,IMPORTRANGE("XXXXXXXXXXXXXXXXXXXXXXXXX",B18&P18&"!A1:E1000"),3),"")
vlookup関数を使って原価を取得する
H18セルに原価を取得する
これも材料名の応用ですが、歩留まりが関係してきます。
材料名
=IFERROR(VLOOKUP(C18,IMPORTRANGE("XXXXXXXXXXXXXXXXXXXXXXXXX",B18&P18&"!A1:E1000"),2),"")単位
=IFERROR(VLOOKUP(C18,IMPORTRANGE("XXXXXXXXXXXXXXXXXXXXXXXXX",B18&P18&"!A1:E1000"),3),"")原価
=IFERROR(VLOOKUP(C18,IMPORTRANGE("XXXXXXXXXXXXXXXXXXXXXXXXX",B18&P18&"!A1:E1000"),4)*E18/G18,"")歩留まりを考える
市場で1Kgのお魚を1000円で購入してきたとします。
以下を見て歩留まりで原価と売値を考えましょう。
お刺身の歩留まり
刺身で提供する場合、頭、内臓、骨、皮を捨てますね、約50%捨てるので歩留まり50%
1000円*1k/50%=2000円
煮魚の歩留まり
煮魚で提供する場合、内臓だけを捨てるので約20%を捨てるので歩留まり80%
1000円*1k/85%=1250円
今回作ったスプレッドシートのダウンロードはこちら
次回
鶏がらスープをいい加減完成したい！！
コメント