エクセルでレシピ検索

質問

エクセル2000を使って、冷蔵庫に入っている食材で作る献立を自動表示させるものを作りたいのですが、
検索条件にしたい事がたくさんあって、何から始めたら良いのかわかりません(ToT)
どなたかヒマな時で結構ですのでよろしくお願いします

＊＊＊何をしたいか＊＊＊

(1)　冷蔵庫の中にある食材（買った日付の古いものが優先）を使った料理名を表示させたい

(2)　冷蔵庫の中にある食材をフルに活用（最大公約数？）はしなくて良い。（買いものに行かなくても、あるものだけで作るという検索はしなくて良い）

(3)　約300種類の料理名の中から、最近作っていないものを優先して検索したい。

(4)　「冷蔵庫の中にあるものリスト」にある、一番古い食材を使った料理名を筆頭に、一番新しい（買ったばかりの）食材を使った料理名まで表示させたい。その時、例えば冷蔵庫の中にある食材が10種類あるとして、ひとつの料理にその中から3種類使うという場合は、残りの7種類の食材のうち、一番古いものを使った料理名が次に表示されるようにしたい。

(5)足りない食材を「買い物リスト」として別の表に表示させたい。その時、何の料理に使うのかわかるようにしたい。
　例）にんじん／シチュー

＊＊＊すでにやってること＊＊＊

(1)約300種類の料理名と、使う材料を表にしてあります。
　　例）肉じゃが／じゃがいも／牛薄切り肉／絹さや
　　　　※「／」はセル1つ分を使っています

(2)冷蔵庫の中にある食材は、買い物した段階で買った日付と食材名を表にしてあります。また、使い切った食材はその都度削除しています。

私のエクセルの技術は、中級くらいです。
関数は、分からないものがあれば調べて活用できる程度です。
できれば簡単でシンプルな表で作りたいです。
３年間も何度か作ってみましたが、挫折していました。

どうかよろしくお願いします

回答No.5

kazuhiko5681 · ベストアンサー

こんばんわ。

[ブックの構成]

　・シート１(冷蔵庫にある食材を入力しておくためのシート)
　　Ｂ列に食品名・Ａ列にその食材の賞味期限を入力する。
　　 (Ｂ列には、必ず食材を入力しておくこと)
　　賞味期限は、古い順に必ず並べておく
　　１行目はタイトル行に当てています。　
・シート２(料理名と使う食材を入力しておくためのシート)
　　Ａ列に調理した日付Ｂ列に料理名・Ｃ列以降にＣ・Ｄ・・・の順に使う食材を入力する。
・シート３(食材の古い順に料理名・使う食材を表示させるためのシート)
　　空白のままにしておく
まず、下記の要領でコードをコピー＆ペーストして下さい。

１.新規ブックを立ち上げ、シート１・シート２に、上に書いたとおりにデータを入力する。(既存のデータをコピー＆ペーストしてもよい)
２.ＡＬＴ＋Ｆ１１キーを押して、ＶＢＥ画面を開き、表示された画面の左上のVBAProjectにマウスポインターをあわせ、右クリック後出てきたプルダウンメニューの挿入をクリックし、出てきたプルダウンメニュの標準モジュールをダブルクリックする。
３.表示された右側の白い部分に、下記のコードをコピー＆ペーストする。

Sub myWorkbook_Open()

Dim myMsb As Integer
Dim myClm As Integer
Dim myRow As Integer
Dim i As Integer
Dim myZairyo As String
Dim myRange As Range
Dim myAdr As String
Dim myCook As String
Dim myFlg As Integer
Dim myCnt As Integer
Dim myCell As Range

myMsb = MsgBox("献立一覧を表示しますか？", vbYesNo + vbQuestion, "作 業 選 択")
If myMsb = vbNo Then Exit Sub

Do
myClm = myClm + 1
Loop Until Worksheets(2).Cells(1, myClm).End(xlDown).Row = Worksheets(2).Rows.Count
myClm = myClm - 1
myRow = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row

myFlg = 0
For i = 2 To Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
myZairyo = Worksheets(1).Cells(i, 2).Value
Set myRange = Worksheets(2).Range("A2:" & Worksheets(2).Cells(myRow, myClm).Address).Find(myZairyo, LookAt:=xlWhole)
If Not myRange Is Nothing Then
myAdr = myRange.Address
Do
myCook = myRange.End(xlToLeft).Offset(0, 1).Value
myMsb = MsgBox("今日の献立は" & Chr(13) & "" & Chr(13) & " " & myCook & Chr(13) & "" & Chr(13) & "でよろしいですか？", vbYesNo + vbQuestion, "献　立　確　認")
If myMsb = vbYes Then
myFlg = 1: Exit For
End If
Set myRange = Worksheets(2).Range("A2:" & Worksheets(2).Cells(myRow, myClm).Address).FindNext(myRange)
Loop While Not myRange Is Nothing And myRange.Address <> myAdr
End If
Next i
If myFlg = 0 Then Exit Sub

myClm = 0
Do
myClm = myClm + 1
If myClm = 1 Then
If Worksheets(3).Range("A1").Value = "" Then
Worksheets(3).Range("A1").Value = myRange.End(xlToLeft).Offset(0, 1).Value
Worksheets(3).Range("B1").Value = "ある材料"
Worksheets(3).Range("B2").Value = "不足材料"
Else
Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(3, 0).Value = myRange.End(xlToLeft).Offset(0, 1).Value
Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Value = "ある材料"
Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Value = "不足材料"
End If
myClm = 3
End If

myZairyo = Worksheets(2).Cells(myRange.Row, myClm).Value
myRow = Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Row
Set myCell = Worksheets(1).Range("B1:" & Cells(Rows.Count, 2).Address).Find(myZairyo, LookAt:=xlWhole)
If myCell Is Nothing Then
Worksheets(3).Cells(myRow + 1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = myZairyo
Else
Worksheets(3).Cells(myRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = myZairyo
End If
Loop Until Worksheets(2).Cells(myRange.Row, myClm).Offset(0, 1).Value = ""

End Sub

次に下記の要領で下記のコードをコピー＆ペーストして下さい。

１.ＶＢＥ画面のVBAProgectと書いてある画面左上の下にあるSheet2をダブルクリックし、Sheet2のコードエディターを表示させる。
２.表示された右側の白い部分に下記のコードをコピー＆ペーストする。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Integer
Dim myClm As Integer

myRow = Target.Row
If Target.Address <> Cells(myRow, 1).Address Then Exit Sub
Do
myClm = myClm + 1
Loop Until Worksheets(2).Cells(1, myClm).End(xlDown).Row = Worksheets(2).Rows.Count
myClm = myClm - 1
myRow = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:" & Cells(myRow, myClm).Address).Select
Selection.Sort key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess

End Sub

最後に、マクロを実行させるツールバーを作成します。次のように操作して下さい。

１.ＡＬＴ＋Ｆ１１キーでエクセルの画面にもどる。
２.メニューバーにカーソルを合わせて右クリックし、出てきたプルダウンメニューの中のユーザー設定をダブルクリックする。
３.出てきたダイアログボックスの右側の新規作成ボタンをクリックし、ツールバー名を献立表と入力後ＯＫボタンで閉じる。
４.ユーザー設定ダイアログボックスの分類の中の新しいメニューをクリックし、右のコマンドの下にある新しいメニューにマウスポインターをあわせ、マウスの左ボタンを押したまま作成したツールバーの上にマウスポインターをあわせ、そこでボタンを離す。(新しいメニュと黒枠で囲まれて表示される)
５.作成したツールバーにマウスポインターをあわせて右クリックして出てきたプルダウンメニューの中の名前を献立表作成と変更し、その下にあるマクロの登録ボタンをクリックする。
６.マクロの登録ダイアログボックスの白い部分にmyWorkbook_Open()と書かれている部分をダブルクリックしてＯＫボタンでボックスを閉じる。
７.ＯＫボタンを押してユーザー設定ダイアログボックスも閉じる。
これでツールバーにマクロが登録されました。献立表作成と書かれている部分をクリックするとマクロが走り、シート３のＡ列に調理日が一番古い料理名Ｃ列以降の１行目に冷蔵庫にある材料名・Ｃ列以降の２行目に不足材料名が表示されます。
再度マクロボタンを押すと、上記に表示された次に調理日が古い料理名・例倉庫にある食材・買わなければならない食材が表示されます。
シート２の調理日を変更すると自動的に日付の古い順にソートされます。

一度お試しになってみて下さい。もし、お気に召さないような時は、ki-aaaさんがお書きになったような方法で詳細をお知らせ下さい。

お礼

どうもありがとうございます！！
最高です！！

お金を払いたいほどです＾＾
本当は自分で作れなきゃだめですよね・・・
これってＶＢＥ画面にペーストした内容を見ながら本で勉強すればいいのでしょうか・・・

kazuhiko5681さん　天才！！
ありがとうございました！！

・・・・またよろしくお願いします（笑）

回答No.4

ki-aaa

こんにちわ。　面白そうなので、どういう風に処理するか、考えてみました。料理には、詳しくないので、はじめに断っておきます。

>(1)約300種類の料理名と、使う材料を表にしてあります。
　　例）肉じゃが／じゃがいも／牛薄切り肉／絹さや

これを、具体的に書きます。

■料理材料表

A1・・調理年月日　B1・・料理名　C1:M1・・主要料理材料(品名)　N1:V1・・調味料等　W1:AG1・・有っても無くても良い材料

食材の、照合は、主要料理材料のみとする。

調理年月日が変化したら、自動的に古い順に並び替えをおこなうよう、マクロを実行します。また、同じ日付けのものは、上にきたものを、より古いと判断します。

>(2)冷蔵庫の中にある食材は、買い物した段階で買った日付と食材名を表にしてあります。また、使い切った食材はその都度削除しています。

これも、具体的に書きます。

■購入済材料表

A1・・購入年月日　B1・・品名　C1・・数量　D1・・賞味日数　E1・・作業用セル　F1・・賞味期限

入力するのはA1,B1,C1,D1です。F1はA1とD1を加えたものです。これも、同じ日付けだったら、上にあるほうが古いものとします。

■実行手順

1　購入済材料表から、E列にフラグのたっていない一番古い、賞味期限のものを選ぶ。
2　1で選んだ品名を料理材料表の、古いものから、照合すル。
3　2で合わなかったら、E列にフラグを立てて、1に戻る。
3.1　ひとつも、合致する料理が無かったら、その旨表示して、9へ飛ぶ。
4　2で合うものがあったら、その料理名を書き出す。
5　このとき、他の、主要料理材料も、品名と照合する。
6　4で書き出した料理名の中で、合致した、主要料理材料の、セル(文字)の色を変える。
7　合致した、購入済材料表の品名のE列にフラグを立てる。

8　前に、すでに選んだ料理があることを記憶して1にもどる(追加の料理を選ぶ)かどうか、判断する。
9　購入済材料表のE列に立てたフラグを消して、処理を終わる。

一応、こんな処理手順を考えました。kazuhiko5681さん、私に教えてくださったように、この方にも、教えてくださいな。

お礼

どうもありがとうございます
む、、、ムズカシイですね。。。
勉強が必要のようです＾＾；

回答No.3

kazuhiko5681

こんばんわ。

[ブックの構成]

　・シート１(冷蔵庫にある食材を入力しておくためのシート)
　　Ｂ列に食品名・Ａ列にその食品を買ってきた日付を入力する。
　　 (Ｂ列には、必ず食材を入力しておくこと)
　　日付は、古い順に必ず並べておく
　・シート２(料理名と使う食材を入力しておくためのシート)
　　Ａ列に料理名・Ｂ列以降にＢ・Ｃ・・・の順に使う食材を入力する。
・シート３(食材の古い順に料理名・使う食材を表示させるためのシート)
　　空白のままにしておく
このような設定で、サンプルマクロを組んでみました。ブックを立ち上げると同時に、メッセージボックスが表示され、ハイを押すとシート３に料理名と使う食材が食材の古い順に表示されます。いいえを押すとシート３が空白のまま立ち上がってきます。これを実行させるためには、次のように操作します。

１.新規ブックを立ち上げ、シート１・シート２に、上に書いたとおりにデータを入力する。(既存のデータをコピー＆ペーストしてもよい)
２.ＡＬＴ＋Ｆ１１キーを押して、ＶＢＥ画面を開き、表示された画面の左上のVBAProjectと書かれている下のThisWorkbookをダブルクリックし、ThisWorkbookのコードエディターを表示させる。
３.表示された右側の白い部分に、下記のコードをコピー＆ペーストする。
４.ブックを名前をつけて保存終了する。
５.再度ブックを開く。

これで、確認してみて下さい。もしご不明な点・不具合等がありましたらご遠慮なくお知らせ下さい。

Private Sub Workbook_Open()

Dim myMsb As Integer
Dim myAdr1 As String
Dim i As Integer
Dim myZairyo As String
Dim myRange As Range
Dim myAdr2 As String
Dim j As Integer
Dim myFlg As Integer
Dim myRow As Integer
Dim Adr3 As String
Dim k As Integer

myMsb = MsgBox("献立一覧を表示しますか？", vbYesNo + vbQuestion, "作 業 選 択")
If myMsb = vbNo Then Exit Sub

myAdr1 = Worksheets(2).Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Address
With Worksheets(2).Range("B1:" & myAdr1)
For i = 2 To Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
myZairyo = Worksheets(1).Cells(i, 2).Value
Set myRange = .Find(myZairyo, LookAt:=xlWhole)
If Not myRange Is Nothing Then
myAdr2 = myRange.Address
Do
If Worksheets(3).Range("A1").Value = "" Then
Worksheets(3).Range("A1").Value = myRange.End(xlToLeft).Value
Else
Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Value = myRange.End(xlToLeft).Value
End If

For j = 2 To Range(myAdr1).Column
If Worksheets(2).Cells(myRange.Row, j).Value <> "" Then
myFlg = 0
myZairyo = Worksheets(2).Cells(myRange.Row, j).Value
myRow = Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Row
For k = 2 To Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
If Worksheets(1).Cells(k, 2).Value = myZairyo Then
myFlg = 1: Exit For
End If
Next k
If myFlg = 1 Then
If Worksheets(3).Range("B1").Value = "" Then
Worksheets(3).Range("B1").Value = myZairyo
Else
Worksheets(3).Cells(myRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = myZairyo
End If
Else
If Worksheets(3).Range("B2").Value = "" Then
Worksheets(3).Range("B2").Value = myZairyo
Else
Worksheets(3).Cells(myRow + 1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = myZairyo
End If
End If
myFlg = 0
End If
Next j

Set myRange = .FindNext(myRange)
Loop While Not myRange Is Nothing And myRange.Address <> myAdr2
End If

Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Value = "×"
Next i
End With

For i = 1 To Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Row
If Worksheets(3).Cells(i, 1).Value = "×" Then
Worksheets(3).Rows(i & ":" & i).ClearContents
End If
Next i

End Sub

お礼

どうもありがとうございます！！
マクロってすごいですね～

質問ですが、一部の食材でシート３に反映されないものがあります。なぜでしょう・・・
シート1と2は、１行目はタイトルにした方がいいですか？

それと・・・贅沢を言えば・・・＾＾；
シート３に反映される献立を、その料理を最後に作った日が古いものを優先にして、ひとつだけ表示させたいです。
〈今は、たぶんその食材を使う献立全てが表示される〉
そして、不足している食材をシート４に買い物リストとして表示させることはできますか？
もしお時間がありましたら　よろしくおねがいします。m(__)m

・・・ここまで作っていただいて、、、わがまま言ってすみません。。。
わたしもマクロ勉強します！！

できるひとはかっこいいですね！

回答No.2

kazuhiko5681

初めまして。サンプルマクロを組んでみたいと思います。少し時間を下さい。マクロを実行すれば、貴方様の思い通りの動作を簡単に実行することができると思います。

お礼

ありがとうございます！！
マクロは、食わず嫌いのように避けて通ってきたのですが＾＾；
この機会に克服したいと思います！

・・・ということは、この質問は締め切らない方が良いですね＾＾

お手数おかけします
よろしくお願いします

回答No.1

misatoanna

作ってできないことはないと思いますが、ご質問の中で挙げられたご要望に関して、
多くの不明点があります(下記)し、明確にされたとしても、かなり煩雑なものになる
と思います。また、運用面でも難しいのではないでしょうか。

> １．冷蔵庫の中にある食材（買った日付の古いものが優先）を使った料理名を表示
　　　　・いくつ表示するのか。
　　　　・該当する料理が複数ある場合は何を優先させるのか。
　　　　・前回使ったが使いきれずに残っている一番古い食材を使った料理は表示
　　　　　対象にするのか。

> ２．冷蔵庫の中にある食材をフルに活用（最大公約数？）はしなくて良い。
> 　　　（買いものに行かなくても、あるものだけで作るという検索はしなくて良い）
　　　　・本体以外すべて買わなければならないというものまで表示してよいのか。

> ３．約300種類の料理名の中から、最近作っていないものを優先して検索したい。
　　　　・次のような記録はどのようにして残すのか。
　　　　　a)料理リスト：料理した日付（場合によっては朝食、昼食、夜食の別まで）
　　　　　b)食材リスト：余った食材ごとの使用した日付（同上）
　　　　自動的に記録する手法をとったとしても、このシステムに頼らず当日の思い
　　　　つきで料理を作った場合は、前記 a) b) に関するデータを手作業で入力する
　　　　必要がある。

> ４．冷蔵庫の中にあるものリスト　にある、一番古い食材を使った料理名を筆頭に、
> 　　　＜中略＞冷蔵庫の中にある食材１０種類の中から３種類使うという場合は、
> 　　　残りの７種類の食材のうち、一番古いものを使った料理名を次に表示。
　　　　・上項１と同じ問題あり。

総合的に判断しますと、単純に「食材を指定して料理リストを表示させ、不足食材を
確認する」程度の内容でよいような気がしますが‥‥。

お礼

ご回答ありがとうございます！
おっしゃる通り、手入力が発生するなら、不足食材を確認する程度のもので良いかもしれませんね＾＾；

一応、挙げて頂いた不明点についてお答えします。

＞いくつ表示するのか。
●ひとつです。

＞該当する料理が複数ある場合は何を優先させるのか。
●このシステムをはじめに使う時だけ、前回料理した日を適当に１月１日から入れていって、古い日付を優先にしてもよいと考えています。

＞前回使ったが使いきれずに残っている一番古い食材を使った料理は表示対象にするのか。
●対象にします。残っているものも、最後に使った日付ではなく、買った日付をいかしたいとおもっています。

＞本体以外すべて買わなければならないというものまで表示してよいのか。
●よいです。

＞次のような記録はどのようにして残すのか。
＞　　　　a)料理リスト：料理した日付（場合によっては朝食、昼食、夜食の別まで）
＞　　　　b)食材リスト：余った食材ごとの使用した日付（同上）
●aは、手入力です。bは、使用した日付は入力せず、買った日をいかします。今日の段階で一番古い食材が、使ったけれども残った場合、明日もそれは一番古い食材になります。

＞４．〈中略〉上項１と同じ問題あり。
●ひとつです。