[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Excel VBAでの質問です。』（ayu）
ある材料が揃うと一品料理ができるというマクロを組みたいです。
初心者なのでよくわからずつまずいています。
sheet1（材料）
いちご15個
みかん2個
りんご3個
さとう3個
バナナ10個
レモン2個
sheet2(料理名（１人分）
＜イチゴジャム＞
いちご2個
さとう1個
＜バナナジャム＞
バナナ2個
さとう1個
レモン1個
この場合だとイチゴジャムを作りたいときに材料のシートへ探しに行き、材料の個数を数え、
sheet3に最大の人数を表示させたいです。今の場合ですと、イチゴジャムは最大3人分バナナジャムも最大3人分は作れるというものをsheet3に表示させたいです。
sheet3
バナナジャム最大3人分可能
イチゴジャム最大3人分可能
この場合ですがどちらかを先に料理してしまいますと、どちらかが作れなくなるので、優先をきめたりもできるようにしたいです。
現在は＝MIN（A1,A2。。。。）と材料の最小値を検索し、
バナナジャム、3と表示させています。
お手数ですが、よろしくお願いします。
＜ 使用office2007：、使用 Windows7 ＞
とりあえずタイトルとニックネーム欄だけ復旧しました。 Excel と OS も記載ください。
質問へのコメントは後ほど。 （Mook） 2014/09/23(火) 17:58
優先順位を決めて最大だけというのであれば、まだ実現できそうですね。 材料からそれぞれの料理に必要な分だけ引いて行けばいいでしょうか。
おもしろそうなのでとりあえずのサンプルです。 今回はシートを使わないで、マクロ中で直接データを書いていますが、 データの入出力をシートに変更すれば、質問のような内容になると思います。
'// ツール ⇒ 参照設定 で Microsoft Scripting Runtime にチェック
Sub Sample()
Dim 食糧庫 As New Scripting.Dictionary
材料登録 食糧庫
Dim イチゴジャム As New Scripting.Dictionary
イチゴジャム("いちご") = 2
イチゴジャム("さとう") = 1
MsgBox "イチゴジャムだけなら" & 調理(食糧庫, イチゴジャム) & "個できます。"
材料登録 食糧庫
Dim バナナジャム As New Scripting.Dictionary
バナナジャム("バナナ") = 2
バナナジャム("さとう") = 1
バナナジャム("レモン") = 1
MsgBox "バナナジャムだけなら" & 調理(食糧庫, バナナジャム) & "個できます。"
材料登録 食糧庫
MsgBox "イチゴジャム" & 調理(食糧庫, イチゴジャム) & "個と" & vbNewLine _
& "バナナジャム" & 調理(食糧庫, バナナジャム) & "個ができます。"
End Sub
Sub 材料登録(食糧庫 As Scripting.Dictionary)
食糧庫.RemoveAll
食糧庫("いちご") = 6
食糧庫("みかん") = 2
食糧庫("りんご") = 3
食糧庫("さとう") = 4
食糧庫("バナナ") = 10
食糧庫("レモン") = 2
End Sub
Function 調理(食糧庫 As Scripting.Dictionary, レシピ As Scripting.Dictionary) As Long
Dim 最大数 As Double
最大数 = -1
Dim 材料
For Each 材料 In レシピ.Keys
If 最大数 = -1 Then
最大数 = 食糧庫(材料) / レシピ(材料)
Else
最大数 = Application.Min(最大数, (食糧庫(材料) / レシピ(材料)))
End If
Next
調理 = CLng(Application.Floor(最大数, 1))
For Each 材料 In レシピ.Keys
食糧庫(材料) = 食糧庫(材料) - レシピ(材料) * 調理
Next
End Function
（Mook） 2014/09/23(火) 19:16
Sub Sample()
Dim 食糧庫 As New Scripting.Dictionary
材料登録 食糧庫
Dim イチゴジャム As New Scripting.Dictionary
イチゴジャム("いちご") = 2
イチゴジャム("さとう") = 1
※↑の部分を
frm1.ListBox1.Text = Worksheets("ﾚｼﾋﾟ").Cells(2, 2).Value
とExcelのシートから値を持ってきたいのですが、.Text.Caption、、色々やっても
怒られてしまいわからないので、教えていただきたいです。
※Dim イチゴジャム As New Scripting.Dictionary
のイチゴジャムの部分ですが、1234と整数型で登録したい場合はエラーがかかります。
ﾚｼﾋﾟ名は（イチゴジャム）は数字とテキスト型が混在するので、テキスト型で登録してしまいたいのですが、その辺りもよくわからないので教えていただきたいです。
（ayu） 2014/09/24(水) 10:40
frm1（おそらくユーザフォーム？）も唐突に出てきているので、どのような目的のものかは 説明が欲しいです。
Excel シートから持ってくるときは、B列に材料名、C列に個数があれば、
イチゴジャム(Worksheets("ﾚｼﾋﾟ").Cells(行, "B").Value) = Worksheets("ﾚｼﾋﾟ").Cells(行, "C").Value
のような形を 行 のループでまわすことになると思います。
レシピ名は変数なので実際の名前にする必要はありません。 使いまわせば、複数用意する必要もありませんね。
For n=1 To レシピ数
料理名 = レシピ読込み( レシピ, n ) '// この行はイメージです。このままでは動きません。
MsgBox 料理名 & " は " & 調理( 食料庫, レシピ ) & "人分出来ます。"
Next
のようにすれば、出来るかと思います。 「レシピ読込み」が上で書いたワークシートからの読込み処理のイメージです。 （Mook） 2014/09/24(水) 11:46
在庫の出し入れを行う際に、ﾚｼﾋﾟの材料をfrm1のListbox1に表示させ、
入庫、出庫の出し入れを行いたいです。
いちごジャムを作成するときは、コンボボックスでイチゴジャム（ﾚｼﾋﾟ名）を選択し、
選択後に
Listbox1に
いちご
さとう
と材料のみ表示させたいんですが、
イチゴジャム(Worksheets("ﾚｼﾋﾟ").Cells(行, "B").Value) = Worksheets("ﾚｼﾋﾟ").Cells(行, "C").Value
だと、イチゴジャムが変数になるので、
Listbox1＝AbbItem.イチゴジャム
とListbox1に格納すればいいのでしょうか？
何人分できるかは、MsgboxではなくExcelのセルに表示させ、優先順位はチェックボックスか何かで優先順位を決めたいです。
（ayu） 2014/09/24(水) 14:36
あまりいっぺんのことを一度にやろうとせず、まずはシートにデータがある前提で （１）MsgBoxで処理結果が出ること それができたら、順に （２）結果をシートに書くこと （３）在庫の入出庫の作成すること （４）優先順位の追加すること
のように、一つずつ機能を確認していったほうがいいと思います。 （チェックボックスで優先順位って付きます？）
急がば回れ、二兎を追うものは・・・、ということで。
質問されている内容は（１）だけでも結構かかるお話だと思いますよ。 （Mook） 2014/09/24(水) 15:18
わからないのはまず検索してみると良いと思います。 まずは、こんなところを読んでみてください。 http://www.niji.or.jp/home/toru/notes/17.html http://msdn.microsoft.com/ja-jp/library/cc428065.aspx （Mook） 2014/09/24(水) 15:47
Sub Sample()
Dim 食糧庫 As New Scripting.Dictionary
材料登録 食糧庫
Dim イチゴジャム As New Scripting.Dictionary
イチゴジャム(Worksheets("料理名").Cells(2, 1).Value) = Worksheets("料理名").Cells(2, 2).Value
イチゴジャム(Worksheets("料理名").Cells(3, 1).Value) = Worksheets("料理名").Cells(3, 2).Value
MsgBox "イチゴジャムだけなら" & 調理(食糧庫, イチゴジャム) & "個できます。"
材料登録 食糧庫
Dim バナナジャム As New Scripting.Dictionary
バナナジャム(Worksheets("料理名").Cells(2, 3).Value) = Worksheets("料理名").Cells(2, 4).Value
バナナジャム(Worksheets("料理名").Cells(3, 3).Value) = Worksheets("料理名").Cells(3, 4).Value
バナナジャム(Worksheets("料理名").Cells(4, 3).Value) = Worksheets("料理名").Cells(4, 4).Value
MsgBox "バナナジャムだけなら" & 調理(食糧庫, バナナジャム) & "個できます。"
材料登録 食糧庫
Dim リンゴジャム As New Scripting.Dictionary
リンゴジャム(Worksheets("料理名").Cells(2, 5).Value) = Worksheets("料理名").Cells(2, 6).Value
リンゴジャム(Worksheets("料理名").Cells(3, 5).Value) = Worksheets("料理名").Cells(3, 6).Value
リンゴジャム(Worksheets("料理名").Cells(4, 5).Value) = Worksheets("料理名").Cells(4, 6).Value
MsgBox "リンゴジャムだけなら" & 調理(食糧庫, リンゴジャム) & "個できます。"
材料登録 食糧庫
MsgBox "イチゴジャム" & 調理(食糧庫, イチゴジャム) & "個と" & vbNewLine _
& "バナナジャム" & 調理(食糧庫, バナナジャム) & "個と" & vbNewLine _
& "リンゴジャム" & 調理(食糧庫, リンゴジャム) & "個ができます。"
End Sub
あるセルにイチゴジャムがあったら、そこの材料名を探すにはどうしたらいいでしょうか？
今の場合だとイチゴジャムの材料は
さとうといちごですが、、、その材料を指定せずループで値を取る取得する方法を教えてください。
（ayu） 2014/09/29(月) 15:47
まず料理名シートの構成を説明してください。 できれば上記のサンプルにあったものが良いです。
気になる点は料理名がすべての材料セルにあるのかないのか、ないとしたらその料理の 最後の行は どのように判断したらよいのか、です。 （Mook） 2014/09/29(月) 16:45
データの列構成により、コードは変わってくるので、先のコメントの回答を聞いてから コード化したかったですけれど、下記のようなデータ構成想定です。 （【食糧庫】シート はA列がなく、B列、C列は【レシピ】シートと同構成）。
【レシピ】シート A B C イチゴジャム いちご 2 さとう 1 バナナジャム バナナ 2 さとう 2 レモン 1 リンゴジャム リンゴ 2 さとう 2 レモン 1
'//---------------------------------------------------------
Sub Sample()
'//---------------------------------------------------------
'// ツール ⇒参照設定 ⇒ 「Microsoft Scripting Runtime」にチェック
Dim 食糧庫 As New Scripting.Dictionary
Dim レシピ As New Scripting.Dictionary
レシピ読込 レシピ
Dim 料理
For Each 料理 In レシピ.keys
食料庫の在庫 食糧庫
MsgBox 料理 & "だけなら" & 調理(食糧庫, レシピ(料理)) & "個できます。"
Next
食料庫の在庫 食糧庫
Dim 結果 As String
For Each 料理 In レシピ.keys
If 結果 <> "" Then 結果 = 結果 & vbNewLine
結果 = 結果 & 料理 & "は" & 調理(食糧庫, レシピ(料理)) & "個できます。"
Next
MsgBox 結果
End Sub
'//---------------------------------------------------------
Sub 食料庫の在庫(食糧庫 As Scripting.Dictionary)
'//---------------------------------------------------------
'// 「食糧庫」シートに B列：材料名、C列：個数
'//---------------------------------------------------------
食糧庫.RemoveAll
Dim 行 As Long
With Worksheets("食糧庫")
For 行 = 1 To .Cells(Rows.Count, "B").End(xlUp).Row
If 食糧庫.Exists(.Cells(行, "B").Value) = False Then
食糧庫(.Cells(行, "B").Value) = .Cells(行, "C").Value
Else
食糧庫(.Cells(行, "B").Value) = 食糧庫(.Cells(行, "B").Value) + .Cells(行, "C").Value
End If
Next
End With
End Sub
'//---------------------------------------------------------
Sub レシピ読込(レシピ As Scripting.Dictionary)
'//---------------------------------------------------------
'// 「レシピ」シートに A列：料理名、B列：材料名、C列：個数
'// A列：料理名は材料内の先頭行にのみ記載
'//---------------------------------------------------------
Dim 行 As Long
Dim 料理 As String
With Worksheets("レシピ")
For 行 = 1 To .Cells(Rows.Count, "B").End(xlUp).Row
If .Cells(行, "A").Value <> "" Then
料理名 = .Cells(行, "A").Value
If レシピ.Exists(料理名) = False Then
Set レシピ(料理名) = New Scripting.Dictionary
End If
End If
レシピ(料理名)(.Cells(行, "B").Value) = .Cells(行, "C").Value
Next
End With
End Sub
'//---------------------------------------------------------
Function 調理(食糧庫 As Scripting.Dictionary, レシピ As Scripting.Dictionary) As Long
'//---------------------------------------------------------
Dim 最大数 As Double
最大数 = -1
Dim 材料
For Each 材料 In レシピ.keys
If 最大数 = -1 Then
最大数 = 食糧庫(材料) / レシピ(材料)
Else
If 食糧庫(材料) = 0 Then
調理 = 0
Exit Function
Else
If レシピ.Exists(材料) = False Then
調理 = 0
Exit Function
End If
最大数 = Application.Min(最大数, (食糧庫(材料) / レシピ(材料)))
End If
End If
Next
調理 = CLng(Application.Floor(最大数, 1))
For Each 材料 In レシピ.keys
食糧庫(材料) = 食糧庫(材料) - レシピ(材料) * 調理
Next
End Function
（Mook） 2014/09/29(月) 20:53
フォームにて（frm1）材料名をコンボボックスに（cmd_Hin)料理名を表示させたいのですが、
今の場合空白があるため、コンボボックスに表示されないのですが、どうしたらよいでしょうか？
Private Sub UserForm_Initialize() 'フォームを読み込んだとき
Dim i As Integer
frm1.txt_Name = Clear '料理作る人
frm1.txt_Date = Date '作業日（今日の日付）
frm1.cmd_Hin = Clear '料理名
frm1.C1.Caption = "***"
frm1.R1.Caption = "***"
i = 1
Do Until Worksheets("レシピ").Cells(i, 1).Value = "" '品種選択
i = i + 1
frm1.cmd_Hin.AddItem Worksheets("レシピ").Cells(i, 1).Value
Loop
End Sub
（ayu） 2014/09/30(火) 11:08
質問内容が一段落したら、質問を立て直して今回のリンクを記載すると良いと思います。 今回の程度であれば、ついででもよいですけれど、これまでとはまったく関係無い内容の ようなので。
空白を登録したくなければ、空白でない判定をして登録すればよいですよね。 AddItem の部分を If で判定してから処理すればよいので、まずは書いてみませんか。 自分で考えたほうが、理解も応用も出来るようになると思いますので。 （Mook） 2014/09/30(火) 12:01
アドバイスありがとうございます！！
（ayu） 2014/09/30(火) 12:27
追加コメントです。
今のコードだと最初の後で抜けてしまうので、
Do Until Worksheets("レシピ").Cells(i, 1).Value = "" '品種選択
は 最終行までのFor 文にした方が良いですね。
（Mook） 2014/09/30(火) 12:59
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.