第3回はMATCH関数とINDEX関数を使ってユニークな製品コードレシピコードを入力していきたいと思います。
今回のシリーズでは妄想でラーメン屋のレシピを作って行こうと思っています。スプレッドシートの関数やgas(Google Apps Script)の勉強メモとして書いて行こうと思います。
ユニークなコードとは被らないコードの事です。
例えば電話番号やマイナンバー、銀行口座です。
ユニークな番号じゃないと大変なことになりますよね。
製品コードの説明
※今気づきましたが、J2セルの製品コードとL10セルの名前が”製品cd”で被ってる。J2セルを変更”製品cd”→”レシピcd”
レシピコードの説明
電話番号で例えるなら
店舗cd：2桁は市外局番 ジャンルcd：2桁は市内局番 製品cd：3桁は加入者番号 レシピcd：7桁は電話番号
レシピコードは7桁で行きたいと思います。
なぜ7桁なのか。
店舗cd2桁（01～99） ジャンルcd2桁（01～99） 製品cd3桁（001～999） 合計7桁
前回から作成している鶏がらスープのレシピは、
店舗cd：01,"らぁめん妄想家" ジャンルcd：99,"半製品" 製品cd：001,"鶏がらスープ" レシピcd：0199001
例えば醤油ラーメンのレシピなら
店舗cd：01,"らぁめん妄想家" ジャンルcd：01,"ラーメン" 製品cd：001,"醬油ラーメン" レシピcd：0101001
塩ラーメンのレシピ
店舗cd：01,"らぁめん妄想家" ジャンルcd：01,"ラーメン" 製品cd：002,"塩ラーメン" レシピcd：0101002
おつまみチャーシューのレシピ
店舗cd：01,"らぁめん妄想家" ジャンルcd：02,"おつまみ" 製品cd：001,"おつまみチャーシュー" レシピcd：0102001
という感じで被らないようにレシピcdでレシピを管理していきたいなと思います。
店舗cdのデータを取得する
店舗名”らぁめん妄想家”の店舗コードは”01″なのでJ11セルに”01″が入力されるように関数を使って自動入力されるようにしていきましょう。
（スプレッドシート”レシピデータ”→シート”コード一覧参照”）
使用する関数はMATCH関数とINDEX関数です。
MATCH関数を使う
P11セルにMATCH関数を書いてみました。
=MATCH(J8,P19:P118,0)
何をしたいか電話番号で例えてみます。
※MATCHに対する私のイメージです。
MATCH(一致)さん、そのメモ(J8)に書いてある”らぁめん妄想家”って知ってます？
電話番号を知りたいのです。
ちょっとわからないですね。
資料(P19:P118)を使ってください。そこからから調べてほしいです。
資料(P19:P118)さえあれば大丈夫です。資料の2番目(P20)にありましたよ！完全に一致[,0]しました！2って書いたメモをこの引き出し(P11)に入れておきますね。
資料のニ番目なんですね。予約したいので市外局番(O列)とか解ります？
無理ですね。絶対に無理です。
いやいや絶対に隣(O列)に書いてあるはずだと思うんだけどな。
MATCH(一致)さんは絶対に教えてくれない。”らぁめん妄想家”という文字が2番目にあるという情報だけが解った。
結果P11に2が入力された。
MATCH(検索キー, 範囲, [検索の種類]) MATCH 関数は、範囲 のセルの範囲で指定した項目を検索し、その範囲内の項目の相対的な位置を返します。
INDEX関数を使う
MATCH関数は2番目としか教えてくれない（返してくれない）ので
店舗cd(J11セル)にINDEX関数を書いてみました。
=INDEX(O19:O118,P11)
これで店舗cdの”01″が取得できました。
※INDEXに対する私のイメージです。
INDEX(索引)くん、”らぁめん妄想家”の市外局番を調べてるんですが、解りますか？
資料と何番目に書いてあるかわかれば調べますよ。
ありがとう。
資料はこれです(O19:O118)
何番目かはさっきMATCH(一致)さんに調べてもらったから引き出し(P11)に入ってるはずです。
引き出し(J8)にありました、2番目なんですね。
この資料(O19:O118)から索引してみたところ、2番目の市外局番は”01″っす！
ようやくらぁめん妄想家の市外局番[“01”]までたどり着いたけどめんどくさいな。
いちいち引き出し(P11)使うし。
INDEX(参照, [行], [列]) INDEX 関数はテーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。
INDEX関数とMATCH関数を使って店舗コードを取得する
ちょっと面倒なのでMATCH(一致)さんとINDEX(索引)くんに同時に指示を出します。
店舗cd(J11セル)に以下を入力する
=INDEX(O19:O118,MATCH(J8,P19:P118,0))
MATCH(一致)さん、INDEX(索引)くん、二人で”らぁめん妄想家”の市外局番を調べて下さい。
資料(P19:P118)がこれですね。資料の2番目(P20)にありましたよ！完全に一致[,0]しました！INDEX(索引)くん2番目です。
MATCH(一致)さんありがとう、2番目なんですね。
この資料(O19:O118)から索引してみたところ、2番目の市外局番は”01″っす！
“01”をJ11セルに書いておきます。
引き出し(P11セル)は必要ないので削除します。
ジャンルcdのデータを取得する
次にジャンルコードを取得します。
ジャンルcd(K11セル)に以下を入力する
=INDEX(O19:O118,MATCH(J8,P19:P118,0))
さっき取得した店舗cdとほぼ一緒です。
製品cdのデータを入力する
製品コードは手入力でOK。
“001鶏がらスープ”なので001を入力します。
レシピcdのデータを取得する
レシピコード=”店舗コード”&”ジャンルコード”&”製品コード”
なのでK2セルに以下を入力する
=J11&K11&L11
“店舗コード”と”ジャンルコード”と”製品コード”をの文字を&で結合しているだけなので簡単ですね。
次回
鶏がらスープに使う品名(D17)や単位(F17)単価(H17)を取得したいと思います。
コメント