[[20171003234513]]　『計算式の結果分行を挿入したい』（1239）　ページの最後に飛ぶ

『計算式の結果分行を挿入したい』（1239）

毎月発送作業があり
本の重さが変わるので
箱の数が変わります

シート1 に本一冊分の重さを入力したら
例:2.5グラム

シート2 に
会社発送部数
A社 500
B社 500
C社 450
D社 300

となっていたら

一箱最大20キロまでとして

行を計算分挿入して【部】を付けたして
一箱に入る部/全体数部
としてその分行を挿入したいです

例:
A社の場合

 500 ×  一冊の重さ ＝全体の重さ
        (シート1で入力した数)
        (今回は2.5グラム)
全体の重さ ÷ 一箱20キロ  ＝ 一箱にはいる部数
                        入る数が小数点になった場合は
                        本は半分に分けられないので
                        32.5 だったら32 と計算しています

500 ÷ 一箱に入る部数＝必要な箱数

―――――――――――――――――――――――――――――――――――――――――――
A社の計算
500×2.5 ＝1250
1250÷20＝62.5 ※本は半分に出来ないので
500÷62＝8.06
62×8＝496
8箱に62冊ずつだと4冊足らないので
500 －496＝4
8箱使用の打ち4箱は62冊入れて残り4箱には63冊入れる

B社の計算
450×2.5＝1125
1125÷20＝56.25 ※本は半分に出来ないので
450÷56＝8.03
56×8＝448 8箱に56冊ずつだと2冊足らないので
450－448＝2
8箱使用のうち6箱は56冊入れて残り2箱には57冊入れる

C社の計算
300×2.5＝700 全体の重さ
700÷20＝35 一箱に入る本の数
300÷35＝8.5 必要な箱の数
35×8＝280

300－280＝20
8箱に35冊ずつだと20部足りないので
9箱目に20冊入れる

シート2
A社 62/500部
A社 62/500部
A社 62/500部
A社 62/500部
A社 63/500部
A社 63/500部
A社 63/500部
A社 63/500部
B社 62/500部
B社 62/500部
B社 62/500部
B社 62/500部
B社 63/500部
B社 63/500部
B社 63/500部
B社 63/500部
C社 56/450部
C社 56/450部
C社 56/450部
C社 56/450部
C社 56/450部
C社 56/450部
C社 57/450部
C社 57/450部
D社 35/300部
D社 35/300部
D社 35/300部
D社 35/300部
D社 35/300部
D社 35/300部
D社 35/300部
D社 35/300部
D社 20/300部

上記は可能でしょうか？
よろしくお願いします。

＜使用アプリ：エクセル2000、使用 OS：WindowsXP ＞

 なんかおかしくねぇ？
 20,000g/箱÷2.5g/冊=8000冊/箱
 部が10冊単位だとしても、1箱800部/箱だと思うのですが。

 仮に62冊として、20,000g÷62部=322gで、計算合わないですよ

 私が間違ってる？
（稲葉） 2017/10/04(水) 06:04

 計算がおかしく無いですか？

 箱の容積に入る最大の個数を求めて、
 出荷量を最大個数で割るんじゃ無いんですか？

[[20160722161708]]『80/120/160 箱ではなく、マスで。』（QPちゃん）
[[20160519153851]]『VBA「160/80/120まで達したら右のセルへ」』（QPちゃん）
[[20160516160218]]『VBA「160まで達したら右のセルへ」』（QPちゃん）

 此方のような事がしたいんじゃ無いんですか？

（sy） 2017/10/04(水) 07:44

稲葉　さん　　Ｓｙさん　　ありがとうございます。

すみません。
計算式まちがえていました。

また　質問　内容も　まちがえていました。
すみません。

毎月　発送の　作業があり　　1箱の重さは　20キログラムまで　
入荷してくる　本が　1梱包　100　部　でくるまれていて
それをくずさずになるべくつかえるように

例　：　本1冊が97グラム　の本が　100　梱包で来た場合
　　　　1冊を約100グラムと計算し

        200冊　×　100　グラム　=　20000グラム（20キロ）
  　　　
　　　　1箱に入れられる　200　部　を　シート　1に　入力

　　　　本1冊が120グラム　の本が　100　梱包で来た場合
　　　　20000グラム　÷　120　グラム　＝　133.33　　※少数点になったら
　　　　1箱に入れられる　133　部　を　シート　1　に入力

シート　1　に　　

1箱　に　何部　（数字を入力）　入れられると
　※　1冊が　97グラム　の場合
Ａ社　の計算
　　1000　×　97　グラム　＝　97000
　　97000　÷　20000　＝4.85
　　5箱必要となるので　元の行のしたに　4行　挿入したいです。

Ｂ社　の計算　
　　800　×　97グラム　＝77600
　　77600　÷　20000　＝3.88
　　4箱必要となるので　元の行のしたに　3行　挿入したいです。　

Ｃ社　の計算
　　500　×　97グラム　　＝48500
　　48500　　÷　20000　＝2.42
　　3箱必要となるので　　元の行のしたに　2行　挿入したいです。　

Ｄ社　の計算
　　300　×　97グラム　＝29100
　　29100　÷　20000　＝　1.45
　　2箱必要となるので　元の行のしたに　1行　挿入したいです。　

シート2　に
Ａ社　　1000
Ｂ社　　 800
Ｃ社　　 500
Ｄ社　　 300

だったら

シート　2　に
Ａ社　　　200/1000部
Ａ社　　　200/1000部　　
Ａ社　　　200/1000部　　
Ａ社　　　200/1000部　　
Ａ社　　　200/1000部　　
Ｂ社　　　200/800部
Ｂ社　　　200/800部　　
Ｂ社　　　200/800部
Ｂ社　　　200/800部
Ｃ社　　 200/500部
Ｃ社　　 200/500部
Ｃ社　　 100/500部　　※入荷が　100部梱包なのでそれをなるべく崩さずにいけるように
Ｄ社　　　200/300部
Ｄ社　　　100/300部

としたいです。

よろしくお願いします。
　

（1239） 2017/10/04(水) 10:31

Sub main()
'元データSheet1をSheet2に展開

    Dim r As Range, c As Range, n, m As Long, x As Long, y As Long, i As Long
    Sheets("Sheet2").Cells.ClearContents
    Set r = Sheets("Sheet2").Range("A1")
    n = InputBox("一冊のグラム数を入力")
    If Val(n) <= 0 Then Exit Sub
    For Each c In Sheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeConstants)
        m = WorksheetFunction.RoundUp(c.Offset(, 1).Value * n / 20000, 0)
        x = WorksheetFunction.RoundDown(20000 / Val(n), -2)
        y = c.Offset(, 1).Value
        For i = 1 To m
            r.Value = c.Value
            r.Offset(, 1).Value = WorksheetFunction.Min(x, y) & "/" & c.Offset(, 1).Value & "部"
            r.Offset(, 3).Value = WorksheetFunction.Min(x, y)
            y = y - x
            Set r = r.Offset(1)
        Next i
    Next c
End Sub
（mm） 2017/10/04(水) 14:11

 >本1冊が120グラム　の本が　100　梱包で来た場合 
 >20000グラム　÷　120　グラム　＝　133.33　　※少数点になったら 
 20000÷120 は 166 じゃないんですか？

 それと
 >※入荷が　100部梱包なのでそれをなるべく崩さずにいけるように
 との事ですけど、

 例えば、1冊130gの本で、300冊の出荷の場合はどうなるんですか？

 20000÷130＝153

 150冊で2箱にするのか？
 100冊で3箱にするのか？

 どっちですか？

（sy） 2017/10/04(水) 21:31

 今一レイアウトが想像できない？？？

 シート2に以下のようになっているんですよね？
 Ａ社　　1000 
 Ｂ社　　 800 
 Ｃ社　　 500 
 Ｄ社　　 300

 シート1は1冊の重さを入れるだけ？
 どのセルに入力するんですか？

 と言うか1冊分の重さと言う事は1つのセルですよね？
 ならシート2に入力した方が良くないですか？

 それと、シート2のデータを分解するんですか？
 元のデータは残して置かないと、間違えた時などに修正が出来なくなりますよ。
 それでも良いんですか？

 元のデータは残しておいて、別のシートに転記する事を強くお勧めします。

 mmさんのコード、計算式が間違ってます。

 >x = WorksheetFunction.RoundDown(20000 / Val(n), -2)
 とxを100冊単位にするのなら、
 m は　WorksheetFunction.RoundUp(y / x, 0) にしないと 80 などの時に数が合わなくなりますよ。

（sy） 2017/10/04(水) 22:21

mmさん　ｓｙさん　ありがとうございます。

すみません。下記
120では　なく　150　でした。　

　間違い：本1冊が120グラム　の本が　100　梱包で来た場合
　　　　　20000グラム　÷　120　グラム　＝　133.33　　※少数点になったら
　　　　 1箱に入れられる　133　部　を　シート　1　に入力

　生：　　本1冊が150グラム　の本が　100　梱包で来た場合
　　　　　20000グラム　÷　150　グラム　＝　133.33　　※少数点になったら
　　　　 1箱に入れられる　133　部　を　シート　1　に入力

＞例えば、1冊130gの本で、300冊の出荷の場合はどうなるんですか？
＞ 20000÷130＝153
＞ 150冊で2箱にするのか？
＞ 100冊で3箱にするのか？

すみません。
担当者　に聞いたところ　　梱包数　100部　を崩さず　1箱　20キロ以内
で出荷するので

300部　発送　の時は　200部（1箱）100部（1箱）にする　とのことでした。

質問時の　計算式　では　おおむね　どのくらい　入りそうとだしているだけのようです。　すみません。

下記の　条件　で　行を挿入は可能でしょうか？

梱包数　100部　を崩さず　1箱　20キロ　以内になるようにと条件があり
シート　1　に　Ｂ：3　に　1冊の　本の重さを　入力　

シート　1　　は　Ｂ：3　に　一冊の本の重さを入力するだけのシートです

＞今一レイアウトが想像できない？？？

シート　2　に
Ａ列：郵便番号
Ｂ列：住所
Ｃ列：住所2
Ｄ列：名称
Ｅ列：部署
Ｆ列：担当1
Ｇ列：担当2
Ｈ列：電話番号
Ｉ列：部数
Ｊ列：発送種別

と1行ごとに　データがはいっています。

すみません。　よろしくお願いします。

（1239） 2017/10/05(木) 09:29

 >担当者　に聞いたところ　　梱包数　100部　を崩さず　1箱　20キロ以内 
で出荷するので 
 >300部　発送　の時は　200部（1箱）100部（1箱）にする　とのことでした。 
 文章はちゃんと読んで下さい。
 そんな事は聞いてませんよ。
 1冊130gの時は、最大で153冊までしか箱に入りませんよね。
 200冊は入らないので200と100と言うのは不可能でしょ。
 そう言う場合は150冊で2箱にするのか、
 100冊で3箱にするのかを聞いてるんです。

 それと行の挿入は可能です。
 可能ですが入力する人が何か1カ所でもデータを間違えていた場合、
 マクロを実行すると変な結果になるし、後から間違いに気付いても
 マクロ実行後には、元に戻したり修正する事が出来なくなりますよ。
 と言う事です。

（sy） 2017/10/05(木) 12:38

ｓｙ　さん　ありがとうございます。

箱数は　　150冊で2箱　です。

データは　データ元Ｅｘｃｅｌがあり　それを　コピーしてきたＥｘｃｅｌの方に
マクロをつけて動かして　エラーが出ても　　取り直し可能になっています。

（1239） 2017/10/05(木) 14:17

手作業でした方が早くないですか？

仕様がはっきりしないうちにああだこうだ言っても、無駄ばっかりですよね？

電卓打ちながら、エクセルで一覧作ってもいいのでは？

（まっつわん） 2017/10/05(木) 14:22

仕事用の大事なツールならなおのこと有料でプロに作っていただいてはいかが？

今の状態では何もかもが信用できないかも。

担当者とプログラム作成者と直接話をしてもらって
何回か打合せしてから、
開発に向かった方がいいように思います。

（まっつわん） 2017/10/05(木) 14:27

 >データ元Ｅｘｃｅｌがあり　それを　コピーして
 なら大丈夫ですね。

 >箱数は　　150冊で2箱　です。 
 箱数は最小にして、同じ箱数になる時は、100冊単位にしたいと言う事ですね？

 重量が149g、20000÷149≒134 の時、1箱最大134冊しか入らない場合、
 出荷数400なら、134×1、133×2
 出荷数500なら、134×1、133×2、100×1
 出荷数600なら、134×1、133×2、100×2
 となります。
 間違いないですか？

 重量が150g、20000÷150≒133 の時、端数分100を3分割だと1足りなくなるので、
 1箱最大端数分100を4分割の125冊しか入らない場合、出荷数500部の時も、
 125冊が4箱　にしたいと言う事ですね？

 エラー処理は全くしてません。

 Sub test()
    Const BoxWt As Long = 20000
    Const bUnit As Long = 100
    Dim Wt As Long
    Dim Vol As Long
    Dim BoxCnt As Long
    Dim ShipNum As Long
    Dim Remaining As Long
    Dim fraction As Long
    Dim i As Long
    Dim k As Long

    Wt = Sheets("Sheet1").Range("B3").Value
    Vol = WorksheetFunction.RoundDown(BoxWt / Wt, 0)

    For i = Range("I" & Rows.Count).End(xlUp).Row To 2 Step -1
        ShipNum = Range("I" & i).Value
        BoxCnt = WorksheetFunction.RoundUp(ShipNum / Vol, 0)
        Range("I" & i + 1).EntireRow.Resize(BoxCnt - 1).Insert
        If BoxCnt * (Vol - (Vol Mod 100)) >= ShipNum Then
            For k = 0 To BoxCnt - 2
                Range("I" & i + k).Value = Vol - (Vol Mod 100) & "/" & ShipNum & "部"
            Next k
            Range("I" & i + BoxCnt - 1).Value = _
                    ShipNum - (Vol - (Vol Mod 100)) * (BoxCnt - 1) & "/" & ShipNum & "部"
        Else
            Remaining = ShipNum - (Vol - (Vol Mod 100)) * BoxCnt
            fraction = WorksheetFunction.RoundUp(100 / (Vol Mod 100), 0)
            For k = 0 To BoxCnt - 1
                If Remaining > 0 Then
                    Range("I" & i + k).Value = Vol - (Vol Mod 100) + _
                            WorksheetFunction.RoundDown(100 / fraction, 0) - _
                            (Remaining Mod fraction > 0) & "/" & ShipNum & "部"
                    Remaining = Remaining - WorksheetFunction.RoundDown(100 / fraction, 0) _
                            + (Remaining Mod fraction > 0)
                Else
                    Range("I" & i + k).Value = Vol - (Vol Mod 100) & "/" & ShipNum & "部"
                End If
            Next k
        End If
    Next i

 End Sub

（sy） 2017/10/06(金) 05:28

 （sy） 2017/10/06(金) 05:28 のコードは以下のレイアウトに基づいて、I列の部数を操作しています。
 重量はシート1のB3セルの値を読込んでいます。

 >シート　2　に 
 >Ａ列：郵便番号 
 >Ｂ列：住所 
 >Ｃ列：住所2 
 >Ｄ列：名称 
 >Ｅ列：部署 
 >Ｆ列：担当1 
 >Ｇ列：担当2 
 >Ｈ列：電話番号 
 >Ｉ列：部数 
 >Ｊ列：発送種別

 >シート　1　に　Ｂ：3　に　1冊の　本の重さを　入力

（sy） 2017/10/06(金) 05:37

syさん mmさん稲葉さんまっつわんさん
ありがとうございます。

Syさん
何度も何度も説明不備すみませんでした。
全体が中々、把握出来ない中コードを考えてくださり
本当にありがとうございました。

まっつわんさん
担当者との話し合いを重ねてからその通りだと思います。
何度も確認をしたのですが、担当者が頭でわかった事を
だから～こう。との説明に毎回なってしまい。
だからの前提にあるものを聞き取るのが難しく
把握できた物を解釈して質問をしていました。
全体(条件どうしたい)の解釈が出来ない内は
次回から質問は控えようと思いました。
ありがとうございました。

（1239） 2017/10/06(金) 09:15

コメント返信：

[ 一覧(最新更新順) ]

[[20171003234513]] 『計算式の結果 分 行を挿入したい』（1239） ページの最後に飛ぶ

[[20171003234513]]　『計算式の結果分行を挿入したい』（1239）　ページの最後に飛ぶ