ここから本文です

Excelの式を教えてください。①取引先発注データ(Sheet1)には、納入日、商品、数量...

yuk********さん

2017/3/1917:21:04

Excelの式を教えてください。①取引先発注データ(Sheet1)には、納入日、商品、数量、納入開始時間のデータが記載されています。
この情報を②当社注文フォーム(Sheet2)に転記したいです。

②には日付と商品、納入時間が固定で表示されています。

画像にあるとおり、
3/1は商品A、400個、納入開始時間は20:00です。
但し、一度に納入できるのは100個(10PLT)まで、残りは自動的に次の納入時間である
0:00、2:30、5:30の時間に自動で割り当てたいです。
よって、②当社注文フォーム(Sheet2) D2=100、I2=100、M2=100、Q2=100と表示させたいです。
C2のPLTのセルは、=D2/10の式を入れ、PLT数を表示させます。

大変御手数ですが、Excelの式をご教示いただきたいです。
よろしくお願い申し上げます。

Sheet2,Sheet1,D2,当社注文フォーム,納入日,k.Column,i.Offset

閲覧数:
172
回答数:
2
お礼:
50枚

違反報告

ベストアンサーに選ばれた回答

yas********さん

2017/3/1922:44:24

はじめまして~

こんなに列に規則性が無ければ、マクロ処理でしょうか。
下記のコードを標準モジュールにコピペしてから、マクロを起動するとできます。

Sub Sample()
Dim i As Range
Dim j As Range
Dim k As Range
Dim flag As Boolean
Dim myNum As Long
Dim myRow1 As Long
Dim myRow2 As Long
Dim myRow As Long
Dim myCol As Long
Dim myTime As Double
Sheets("当社注文フォーム").Activate
Application.ScreenUpdating = False
With Sheets("取引先発注データ")
myRow1 = .Cells(Rows.Count, 3).End(xlUp).Row
myRow2 = Cells(Rows.Count, 2).End(xlUp).Row
myCol = Cells(1, Columns.Count).End(xlToLeft).Column
For Each i In .Range("B2:B" & myRow1)
myNum = i.Offset(, 1)
myTime = i.Offset(, 2).Value
For Each j In Range("B2:B" & myRow2)
If j & "_" & j.Offset(, -1) = i & "_" & i.Offset(, -1) Then
myRow = j.Row
Exit For
End If
Next j
For Each k In Range("C1").Resize(, myCol)
If k = myTime Then flag = True
If VarType(k.Value) = 5 And flag = True Then
If myNum > 100 Then
Cells(myRow, k.Column) = 100
Cells(myRow, k.Column - 1) = 10
myNum = myNum - 100
ElseIf myNum <= 100 Then
Cells(myRow, k.Column) = myNum
Cells(myRow, k.Column - 1) = myNum / 10
myNum = 0
flag = False
Exit For
End If
End If
Next k
Next i
End With
Application.ScreenUpdating = True
End Sub

いかがですか?

はじめまして~

こんなに列に規則性が無ければ、マクロ処理でしょうか。...

  • 質問者

    yuk********さん

    2017/3/2010:27:33

    yasuragihonokaさん

    ご回答いただきありがとうございます。
    マクロ、、コピペして作成してみました。
    画像のとおり、出来ました。ありがとうございます。

    いくつか質問させてください。
    別のExcel表にこのマクロを使用する場合、
    (一度の配送可能数量、PLT当たりの入数、入力されているセルが異なる場合(例えば、「日付」はA1ではなく、A8等)
    マクロの式の" "の中を対応させ、数量の100の箇所を変更、PLT入数の10の箇所を変更すれば良いのでしょうか。

    質問では分かりやすい数字、表で問合せさせていただいた為、
    実際に使用する表への展開をしてみたいのです。
    実際に使用する表で試してみたのですが、うまくマクロが実行できず
    お手数ですが教えていただきたいです。

  • その他の返信(2件)を表示

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

この回答は投票によってベストアンサーに選ばれました!

ベストアンサー以外の回答

1〜1件/1件中

was********さん

2017/3/1918:51:45

C:D列を挿入して今の数式でSheet1のデータを表示させます。
E2セル以降全てのセルについて同じ数式でまかなうとします。
=IF(ISNUMBER(E$1),数量,F2/10)
数量=IF(OR(E$1<開始時刻,E$1>終了時刻),0,IF(E$1=終了時刻,終了時数量,100))
C:Dから以下の値を数式中で返します。
開始時刻=$D2
終了時刻=$D2+(CEILING($C2/100,1)-1)/24
終了時数量=$C2-(CEILING($C2/100,1)-1)*100
これから、
数量=IF(OR(E$1<$D2,E$1>$D2+(CEILING($C2/100,1)-1)/24),0,IF(E$1=$D2+(CEILING($C2/100,1)-1)/24,$C2-(CEILING($C2/100,1)-1)*100,100))
先の数式と合わせましょう。
=IF(ISNUMBER(E$1),IF(OR(E$1<$D2,E$1>$D2+(CEILING($C2/100,1)-1)/24),0,IF(E$1=$D2+(CEILING($C2/100,1)-1)/24,$C2-(CEILING($C2/100,1)-1)*100,100)),F2/10)

※項目行の時刻は右方が大きいようになっていますね。
表示形式を「h:mm」とすれば、添付された図のようになります。
20:00<21:00<22:00<23:00<24:00<25:00・・・

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

この質問につけられたタグ

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる