エクセルか、アクセスで下記のようなことが出来ますでしょうか？

質問

エクセルか、アクセスで下記のようなことが出来ますでしょうか？

まずは下記Webページに貼り付けた画像をみていただきたいのですが、
http://f58.aaa.livedoor.jp/~works/setumei.html

加工前状態で取引先からエクセルファイルが来ます。
これはテスト用データなので件数少ないですが、実際は1500行（1500営業所）ほどあります。

これを加工後の形になるようにしたいと考えています。

表のご説明をしますと、営業所毎に荷物を配送するのですが、
取引先からは営業所毎に配送する数量だけ来ます。

それを元にこちらで加工するのですが、列項目それぞれの意味としては、
「１箱の最大入り数」：梱包する箱にいくつ品物が入るかの個数です。

「必要箱数」：１箱の最大入り数に応じて、営業所毎に梱包箱がいくつ必要かを表しています。

「箱連番（営業所毎）」：営業所毎に、箱に連番を付けています。

「伝票番号（営業所毎）」：営業所毎の伝票に連番を付けますが、複数口の場合同一番号になるようにします。

「箱固有番号」：重複しない固有の箱番号を付けています。

「箱内数量」：それぞれの箱内に、いくつの品物が入ってるかを表しています。

説明が長くなってしまい申し訳ございません。
本題ですが、上記の表の中で、「箱内数量」を計算式で求めるにはどうすればよいでしょうか？

それと、営業所毎に複数口配送となる場合（黄色い塗りつぶしの箇所です）、
箱数に応じて行数も増やしたいのですが、自動処理で良い方法はないでしょうか？

それと、表が完成したのちに出力したいのですが、
B6サイズ程の伝票に、各行毎に１枚１枚伝票を印字したいのですが、
これはアクセスでないと出来ませんか？
※もし1500行あれば、各行毎に伝票枚数も1500枚となるように。

色々と大変な質問をして誠に申し訳ございません。
どうか宜しくお願いいたします。

Webページ上で、テスト用データもダウンロードできるようにしてありますので、
なにかのお役に立てますと幸いでございます。

回答No.8

hallo-2007 · ベストアンサー

何度もすみません、中途半端だったので
以下のコードを作成してみました
Sub ボタン1_Click()
With Sheets("加工後")
.Range("2:65536").ClearContents
For i = 2 To Range("A65536").End(xlUp).Row
For j = 1 To Range("D" & i).Value
GYOU = .Range("A65536").End(xlUp).Row + 1
.Range("A" & GYOU).Value = Range("A" & i).Value
.Range("B" & GYOU).Value = Range("B" & i).Value
.Range("C" & GYOU).Value = Range("C" & i).Value
.Range("D" & GYOU).Value = Range("D" & i).Value
.Range("E" & GYOU).Value = j
.Range("F" & GYOU).Value = "111-1111-" & Right("1111" & (i - 1), 4)
.Range("G" & GYOU).Value = GYOU - 1
.Range("H" & GYOU).FormulaR1C1 = _
"=MIN(RC[-5],RC[-6]-SUMIF(R1C[-2]:R[-1]C[-2],RC[-2],R1C[-5]:R[-1]C[-5]))"
Next j
Next i
End With
End Sub
結果は画面の通りです。
「１箱の最大入り数」と「必要箱数」は事前に手入力と関数を入れておきますが。

お礼

お礼が遅くなりまして誠に申し訳ございませんでした！！
VBAはまったく分からなかったので、ほんとに助かります。
まさに思い描いていた形になりました。
こんなに便利にエクセルが使えるだなんてとてもビックリしてます。
まだ実践で使ってないのでまずはテストを重ねてから、ありがたく使わせていただきたいと思います。
誠にありがとうございました。

回答No.7

hallo-2007

No6です。
＞上記の表の中で、「箱内数量」を計算式で求めるにはどうすればよいでしょうか？
=MIN(C2,B2-SUMIF(E$1:E1,E2,C$1:C1))　下コピィでいけそうですね。
先ほどのVBAのコードですが、箱数の部分が抜けていました。
必要であれば編集の上、ご利用ください。

回答No.6

hallo-2007

一部、VBAの力を借りますが宜しいでしょうか。
加工前のシートで
「１箱の最大入り数」：梱包する箱にいくつ品物が入るかの個数です。
手入力ですよね。
「必要箱数」：１箱の最大入り数に応じて、営業所毎に梱包箱がいくつ必要かを表しています。
=ROUNDUP(B2/C2,0)　の関数で下へコピィします。
＞営業所毎に複数口配送となる場合（黄色い塗りつぶしの箇所です）、
＞箱数に応じて行数も増やしたいのですが、自動処理で良い方法はないでしょうか？
この部分にVBAを使いますが、VBエディターを起動して
Sub ボタン1_Click()
With Sheets("加工後")
.Range("2:65536").ClearContents
For i = 2 To Range("A65536").End(xlUp).Row
For j = 1 To Range("D" & i).Value
GYOU = .Range("A65536").End(xlUp).Row + 1
.Range("A" & GYOU).Value = Range("A" & i).Value
.Range("B" & GYOU).Value = Range("B" & i).Value
.Range("C" & GYOU).Value = Range("C" & i).Value
.Range("D" & GYOU).Value = j
.Range("E" & GYOU).Value = "111-1111-111" & (i - 1)
.Range("F" & GYOU).Value = GYOU - 1
Next j
Next i
End With
End Sub
を貼り付けて閉じておきます。
別途、シート名で　加工後　という名前のシートを準備して、1行目に項目を入れてください。
マクロで　ボタン1　を実行すると
箱固有番号までは自動で得られると思います。
＞上記の表の中で、「箱内数量」を計算式で求めるにはどうすればよいでしょうか？
は申し訳ありませんが、別途関数を検討してください。

回答No.5

tom04

No.2・3・4です！
ほんとぉ～っに！何度もごめんなさい。

お詫びの印と言っては失礼なのですが、もう一度考えてみました。
あまり美しくないのですが、もう1列作業用の列を追加して
営業所名の重複があってもH列に数値を表示できる方法です。
尚、重複がある営業所に関しては営業所名の後ろに、2や3の数値が余分についてしまいます。

↓の画像の作業列I2セルに
=IF(COUNTIF($A$2:A2,A2)=1,A2,A2&COUNTIF($A$2:A2,A2))

J2セルに
=IF(B2="","",IF(MOD(B2,C2)=0,INT(B2/C2),INT(B2/C2)+1))
として、I2・J2セルを範囲指定し、J2セルのフィルハンドルで下へコピー

K2セルは
=IF(OR($J2="",$J2<COLUMN(A1)),"",ROW(A1)*1000+COLUMN(A1))
として、列方向と行方向にオートフィルでコピーします。

Sheet2のA2セルには
=IF(COUNT(Sheet1!$K$2:$T$1000)<ROW(A1),"",INDEX(Sheet1!$I$2:$I$1000,INT(SMALL(Sheet1!$K$2:$S$1000,ROW(A1))/1000)))
として、下へコピー！

最後にSheet2のH2セルは
=IF(A2="","",IF(INDEX(Sheet1!$B$2:$B$1000,MATCH(A2,Sheet1!$I$2:$I$1000,0))>=COUNTIF(Sheet2!$A$2:Sheet2!A2,A2)*INDEX(Sheet1!$C$2:$C$1000,MATCH(A2,Sheet1!$I$2:$I$1000,0)),INDEX(Sheet1!$C$2:$C$1000,MATCH(A2,Sheet1!$I$2:$I$1000,0)),MOD(INDEX(Sheet1!$B$2:$B$1000,MATCH(A2,Sheet1!$I$2:$I$1000,0)),INDEX(Sheet1!$C$2:$C$1000,MATCH(A2,Sheet1!$I$2:$I$1000,0)))))
としてこれも下へコピーします。

もし、入数が「50」と決まっているのであれば
H2セルの数式は
=IF(A2="","",IF(INDEX(Sheet1!$B$2:$B$1000,MATCH(A2,Sheet1!$I$2:$I$1000,0))>=COUNTIF(Sheet2!$A$2:Sheet2!A2,A2)*50,50,MOD(INDEX(Sheet1!$B$2:$B$1000,MATCH(A2,Sheet1!$I$2:$I$1000,0)),50)))
でも大丈夫だと思います。

以上、今回も長々と書いてしまいました。

何度もごめんなさいね。m(__)m

お礼

お礼が遅くなりまして誠に申し訳ございませんでした！！
しかも何度もご教示いただきまして、お手数をおかけしましてほんとに恐縮しております。
ベストアンサーが１人しか選べなくてとても迷いました・・・。
申し訳ございません・・・。
しかしながら、tom04様の温かいお心遣いがとても伝わってきました。
感動致しました。
心よりお礼を申し上げます。
誠にありがとうございました。

回答No.4

tom04

No.2・3です！
何度もごめんなさい。

投稿した後に気づきました。
Sheet1のA列には営業所名が重複してあるわけですよね？

その場合は前回の方法では正確な値は表示できません。

というわけで
今までの方法は無視してください。

どうもたびたびごめんなさいね。m(__)m

回答No.3

tom04

No.2です！
たびたびごめんなさい。

前回の作業用の数式ですが、
J2セルの数式を
=IF(OR($I2="",$I2<COLUMN(A1)),"",ROW(A1)*1000+COLUMN(A1))
に訂正してください。

前回の数式ですと、空白の行まで数値が表示されると思います。

どうも何度も失礼しました。m(__)m

回答No.2

tom04

こんばんは！
参考になるかどうか判りませんが・・・
↓の画像のように無理矢理作業用の表を作成してやってみました。
とりあえず、A・H列の表示だけの方法です。
Sheet1の1000行目まで対応できる数式にしています。

Sheet1に作業用の表を設けます。
作業列I2セルに
=IF(B2="","",IF(MOD(B2,C2)=0,INT(B2/C2),INT(B2/C2)+1))
という数式を入れフィルハンドルの（＋）マークでダブルクリック、又はオートフィルで下へコピーします。
これでSheet2にA列のデータを表示させるために何行必要かを表示させます。
そして、作業表のJ2セルに
=IF($I2<COLUMN(A1),"",ROW(A1)*1000+COLUMN(A1))
という数式を入れ、列方向（右）にオートフィルでコピーします。
空白になっても構いませんので、しっかり右へコピーしておきます。
（今回は数式の関係でR列までのコピーとしておきました。）

そして、オートフィルした2行目全てを範囲指定し、R2セルのフィルハンドルで下へコピーすると
画像のような数値が表示されると思います。
この数値を元に、Sheet2に表示させます。

Sheet2のA2セルに
=IF(COUNT(Sheet1!$J$2:$R$1000)<ROW(A1),"",INDEX(Sheet1!$A$2:$A$1000,INT(SMALL(Sheet1!$J$2:$R$1000,ROW(A1))/1000)))
という数式を入れ、オートフィルで下へコピー

そして、H2セルには
=IF(A2="","",IF(INDEX(Sheet1!$B$2:$B$1000,MATCH(A2,Sheet1!$A$2:$A$1000,0))>=COUNTIF($A$2:A2,A2)*INDEX(Sheet1!$C$2:$C$1000,MATCH(A2,Sheet1!$A$2:$A$1000,0)),INDEX(Sheet1!$C$2:$C$1000,MATCH(A2,Sheet1!$A$2:$A$1000,0)),MOD(INDEX(Sheet1!$B$2:$B$1000,MATCH(A2,Sheet1!$A$2:$A$1000,0)),INDEX(Sheet1!$C$2:$C$1000,MATCH(A2,Sheet1!$A$2:$A$1000,0)))))

という数式を入れこれも下へコピーします。

これで画像のような感じになります。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

回答No.1

keithin

エクセルの場合。

加工後のH2の数式は
=MIN(INDEX(C:C,MATCH(F2,F:F,0)),INDEX(B:B,MATCH(F2,F:F,0))-(ROW()-MATCH(F2,F:F,0))*INDEX(C:C,MATCH(F2,F:F,0)))
などのような感じで，H3やH4からH7などにこのままつるつるっとコピーして入れておくと出てきます。

行を「勝手に追加してくれる」機能はありません。マクロが書けるならマクロで，出来なければ今はとりあえず手作業で（いままで通り？）「必要箱数」の計算結果を見ながら行を挿入してください。

マクロ自体はそんなに難易度の高いものではありませんが，それでも今はまだマクロなんて無理という時は，うかつにここで誰かに作ってもらったのを信じて使うとあとで困りますので，少し勉強して自作するか誰かマクロ使いを捜して実際のブックに合わせてマクロを書いて貰ってください。
表の並び順とかがちょっとでも変わると直ちにマクロも変更になりますので，要件が変わった時すぐに対応してマクロを正しく適切に直してくれる人を捕まえておくか，そういう仕事のお手伝いをしてくれる人が見つからなければご自分で作成できるようになってください。

なおマクロが自作できるなら，行追加に併せて「箱内数量」もマクロに計算させて，入れさせてしまった方が多分簡単です。

＞伝票印刷
こちらもやっぱりマクロが使えれば出来ますが，ワードに伝票様式を作成して差込印刷すれば，マクロとか出来なくてもすぐに作成でもできます。
ただしその場合，箱数が増えて追加した行にも「漏れなく」データを書き入れておかないといけません。今みたいに空きのまま，グループの先頭行を見てみたいな手抜きをすると収拾がつかなくなりがちです。

＃あんまり「それからそれから」のご質問で，その一つ一つに精緻な回答を付けているときりがありませんので，出来る範囲の事をとりあえず自力で作成してみて下さい。
不明の点や自分でやってみて出来なかった部分は，具体的な問題箇所の状況（ここまでこうやって，ここで躓いたと）を添えてピンポイントに，遠慮なくまたご質問を投稿なさってみてください。

お礼

お礼が遅くなりまして誠に申し訳ございませんでした！！
箱内数量の数式は大変助かりました。
ありがたくご利用させていただきます。
誠にありがとうございました。