エクセルか、アクセスで下記のようなことが出来ますでしょうか?
まずは下記Webページに貼り付けた画像をみていただきたいのですが、
http://f58.aaa.livedoor.jp/~works/setumei.html
加工前状態で取引先からエクセルファイルが来ます。
これはテスト用データなので件数少ないですが、実際は1500行(1500営業所)ほどあります。
これを加工後の形になるようにしたいと考えています。
表のご説明をしますと、営業所毎に荷物を配送するのですが、
取引先からは営業所毎に配送する数量だけ来ます。
それを元にこちらで加工するのですが、列項目それぞれの意味としては、
「1箱の最大入り数」:梱包する箱にいくつ品物が入るかの個数です。
「必要箱数」:1箱の最大入り数に応じて、営業所毎に梱包箱がいくつ必要かを表しています。
「箱連番(営業所毎)」:営業所毎に、箱に連番を付けています。
「伝票番号(営業所毎)」:営業所毎の伝票に連番を付けますが、複数口の場合同一番号になるようにします。
「箱固有番号」:重複しない固有の箱番号を付けています。
「箱内数量」:それぞれの箱内に、いくつの品物が入ってるかを表しています。
説明が長くなってしまい申し訳ございません。
本題ですが、上記の表の中で、「箱内数量」を計算式で求めるにはどうすればよいでしょうか?
それと、営業所毎に複数口配送となる場合(黄色い塗りつぶしの箇所です)、
箱数に応じて行数も増やしたいのですが、自動処理で良い方法はないでしょうか?
それと、表が完成したのちに出力したいのですが、
B6サイズ程の伝票に、各行毎に1枚1枚伝票を印字したいのですが、
これはアクセスでないと出来ませんか?
※もし1500行あれば、各行毎に伝票枚数も1500枚となるように。
色々と大変な質問をして誠に申し訳ございません。
どうか宜しくお願いいたします。
Webページ上で、テスト用データもダウンロードできるようにしてありますので、
なにかのお役に立てますと幸いでございます。
No.8ベストアンサー
- 回答日時:
何度もすみません、中途半端だったので
以下のコードを作成してみました
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
結果は画面の通りです。
「1箱の最大入り数」と「必要箱数」は事前に手入力と関数を入れておきますが。
お礼が遅くなりまして誠に申し訳ございませんでした!!
VBAはまったく分からなかったので、ほんとに助かります。
まさに思い描いていた形になりました。
こんなに便利にエクセルが使えるだなんてとてもビックリしてます。
まだ実践で使ってないのでまずはテストを重ねてから、ありがたく使わせていただきたいと思います。
誠にありがとうございました。
No.7
- 回答日時:
No6です。
>上記の表の中で、「箱内数量」を計算式で求めるにはどうすればよいでしょうか?
=MIN(C2,B2-SUMIF(E$1:E1,E2,C$1:C1)) 下コピィでいけそうですね。
先ほどのVBAのコードですが、箱数の部分が抜けていました。
必要であれば編集の上、ご利用ください。
No.6
- 回答日時:
一部、VBAの力を借りますが宜しいでしょうか。
加工前のシートで
「1箱の最大入り数」:梱包する箱にいくつ品物が入るかの個数です。
手入力ですよね。
「必要箱数」:1箱の最大入り数に応じて、営業所毎に梱包箱がいくつ必要かを表しています。
=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
- 回答日時:
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
お礼が遅くなりまして誠に申し訳ございませんでした!!
しかも何度もご教示いただきまして、お手数をおかけしましてほんとに恐縮しております。
ベストアンサーが1人しか選べなくてとても迷いました・・・。
申し訳ございません・・・。
しかしながら、tom04様の温かいお心遣いがとても伝わってきました。
感動致しました。
心よりお礼を申し上げます。
誠にありがとうございました。
No.4
- 回答日時:
No.2・3です!
何度もごめんなさい。
投稿した後に気づきました。
Sheet1のA列には営業所名が重複してあるわけですよね?
その場合は前回の方法では正確な値は表示できません。
というわけで
今までの方法は無視してください。
どうもたびたびごめんなさいね。m(__)m
No.3
- 回答日時:
No.2です!
たびたびごめんなさい。
前回の作業用の数式ですが、
J2セルの数式を
=IF(OR($I2="",$I2<COLUMN(A1)),"",ROW(A1)*1000+COLUMN(A1))
に訂正してください。
前回の数式ですと、空白の行まで数値が表示されると思います。
どうも何度も失礼しました。m(__)m
No.2
- 回答日時:
こんばんは!
参考になるかどうか判りませんが・・・
↓の画像のように無理矢理作業用の表を作成してやってみました。
とりあえず、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
- 回答日時:
エクセルの場合。
加工後の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などにこのままつるつるっとコピーして入れておくと出てきます。
行を「勝手に追加してくれる」機能はありません。マクロが書けるならマクロで,出来なければ今はとりあえず手作業で(いままで通り?)「必要箱数」の計算結果を見ながら行を挿入してください。
マクロ自体はそんなに難易度の高いものではありませんが,それでも今はまだマクロなんて無理という時は,うかつにここで誰かに作ってもらったのを信じて使うとあとで困りますので,少し勉強して自作するか誰かマクロ使いを捜して実際のブックに合わせてマクロを書いて貰ってください。
表の並び順とかがちょっとでも変わると直ちにマクロも変更になりますので,要件が変わった時すぐに対応してマクロを正しく適切に直してくれる人を捕まえておくか,そういう仕事のお手伝いをしてくれる人が見つからなければご自分で作成できるようになってください。
なおマクロが自作できるなら,行追加に併せて「箱内数量」もマクロに計算させて,入れさせてしまった方が多分簡単です。
>伝票印刷
こちらもやっぱりマクロが使えれば出来ますが,ワードに伝票様式を作成して差込印刷すれば,マクロとか出来なくてもすぐに作成でもできます。
ただしその場合,箱数が増えて追加した行にも「漏れなく」データを書き入れておかないといけません。今みたいに空きのまま,グループの先頭行を見てみたいな手抜きをすると収拾がつかなくなりがちです。
#あんまり「それからそれから」のご質問で,その一つ一つに精緻な回答を付けているときりがありませんので,出来る範囲の事をとりあえず自力で作成してみて下さい。
不明の点や自分でやってみて出来なかった部分は,具体的な問題箇所の状況(ここまでこうやって,ここで躓いたと)を添えてピンポイントに,遠慮なくまたご質問を投稿なさってみてください。
お礼が遅くなりまして誠に申し訳ございませんでした!!
箱内数量の数式は大変助かりました。
ありがたくご利用させていただきます。
誠にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 郵便・宅配 宅急便コンパクトの厚み制限って最近厳しくなったのでしょうか? 今までは、メルカリ等で売れた商品を梱包 3 2023/05/04 08:57
- その他(プログラミング・Web制作) プログラミング pythonの問題について 2 2022/04/19 00:41
- Excel(エクセル) 荷捌作業効率をあげるためのエクセル関数を教えてください。 8 2022/10/07 08:17
- 統計学 統計量および正規分布と分散の加法性の演習問題です。 5 2023/07/29 10:46
- ゴミ出し・リサイクル Mac買取について。付属の箱が入りません 1 2022/08/06 17:00
- 会社・職場 伝え方(言葉選び、表現方法)について教えて下さい。 人事考課の目標設定でどう書けば上司に伝わるか悩ん 1 2022/05/28 19:05
- その他(IT・Webサービス) [PayPay銀行キャッシュカード拾得の件] このメールは本物か? 6 2022/09/15 21:52
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) 【Excel質問】 「本日の日付」から指定条件を満たす営業日経過後の日数を表示させる関数式 3 2022/06/06 23:28
- Visual Basic(VBA) VBA シート間の転記で、条件の追加コードの書き方について教えて下さい。 13 2023/02/26 09:31
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
アクセスクエリの計算
-
ACCESS VBA でのエラー解決の根...
-
Access VBA を利用して、フォル...
-
Accessのクエリで、replace関数...
-
Vba Userformを前面に出すについて
-
accessでlaccdbファイルが削除...
-
Accessのスプレッドシートエク...
-
Accessのデータ型の日付/時刻型...
-
エクセルのデータをアクセスに...
-
accessの代わりになるもの
-
Accessのフォーム上のテキスト...
-
Access VBA でHTML文を表示したい
-
Accessで作ったデータベースをw...
-
accessデータを指定したExcel、...
-
実行時エラー3131 FROM 句の構...
-
Accessに関する質問です。 クエ...
-
Accessのリンクテーブルのパス...
-
CSVファイルの「0落ち」にVBA
-
Accessで独自メニューバーまた...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
Access Error3061 パラメータが...
-
Microsoft365にAccessってあり...
-
Accessのクエリで、replace関数...
-
Accessのリンクテーブルのパス...
-
Access VBA [リモートサーバー...
-
ACCESS VBA でのエラー解決の根...
-
accessデータを指定したExcel、...
-
Accessのスプレッドシートエク...
-
CSVファイルの「0落ち」にVBA
-
【Access】Dcount関数の複数条...
-
Accessのフォーム上のテキスト...
-
Access VBA を利用して、フォル...
-
実行時エラー3131 FROM 句の構...
-
Vba Userformを前面に出すについて
-
Accessでフォームに自動入力し...
-
Accessレポートのチェックボッ...
-
Accessのテキストボックスの入...
-
Access 複数条件検索の設定が上...
-
accessのフォームに設置したボ...
おすすめ情報