エクセルで在庫情報と発送みたいなものを作りたい。

質問

エクセルで在庫情報と発送みたいなものを作りたいのですが、
まずは、これができるのか？どうか？知りたいのですが
（なんとなくできそうですが）
さらには、どうやったらいいのか？
エクセルエキスパートなデキる方、教えて頂けないでしょうか？

大きく２つあります。

例のようにプレゼントの発送と在庫をエクセルで表示したいのです。

この画像を参考にしてください。
http://bsmile.sakura.ne.jp/phptest/aa1.jpg

＝＝＝その１　イメージは在庫管理＝＝＝

添付画像のようなエクセルは関数でつくれますか？

シート「商品A～商品C」に任意に商品が入ってたとします。
（それぞれのシートはA,B,C列の３項目とします。）
（尚、左上の在庫情報はcounta関数をつかってるだけです。）

それを「まとめ」のシートの　B列に「商品？」を入力した場合
それにそって、ワークシート「商品A,B,C」から、商品を取り出して、
右に列記する事は可能でしょうか？
（サンプル左上の太枠で囲った、薄紫の部分）

なお、例の通り、B列には任意でワークシートの名前を入力するものとし
同じ「商品B」があれば、商品Bシートの2番目のファイルが並ぶものとします。

まずここまでをどう作ったらいいか？ほぼわかりません。

＝＝＝その２　イメージは発送処理とその後の在庫管理＝＝＝
その後マクロでも関数でもいいのですが、
「発送しました」ボタンか何かを付けて、
それを押すと

商品Aは1行目が
商品Bは3行目まで
商品Cは2行目まで

自動的に削除されてそれぞれが１行目に移動（在庫が減るというイメージ）し、

その１でのルールで内容が記載される
（つまり、商品Aは１つ、商品Bは３つ、商品Cは２つ内容が減る）

＝＝＝＝＝＝＝＝

こういったエクセルを作りたいのですが、
そもそも、左上のB列の名前に入ったワークシートを
どうやって、宣言すればいいか？すらわかってません。

それはこの関数だ！とか、参考になるWEBアドレス等あったらおしえていただけませんでしょうか？

この画像を参考にしてください。
http://bsmile.sakura.ne.jp/phptest/aa1.jpg

どうぞよろしくお願いいたします。m(_ _)m

回答No.3

tom04 · ベストアンサー

No.2です！

どうもごめんなさい。
確かにおっしゃる通り消えなくてはいけないデータが消えないようですね！
おそらく、一気にDeleteしてしまったのがいけないようですので
↓のコードに変更してみてください。
尚、一気に行削除するのではなく一度データを消去し、
最後にSheet見出し左から2番目～4番目のSheetの空白行を削除するようにしていますので
実際はもっとSheet数があるかもしれませんので、実データに合わせてみてください。

Private Sub CommandButton1_Click()
Dim i As Long, k As Long, str As String, c As Range
On Error Resume Next
For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row
str = Cells(i, 2)
With Worksheets(str)
Set c = .Range("A:A").Find(what:=Cells(i, "C"), LookIn:=xlValues, lookat:=xlWhole)
c.Resize(1, 3).ClearContents
End With
Next i
For k = 2 To 4 '←Sheet見出し左から2番目～4番目（商品A～商品C）のSheet
Worksheets(k).Range("A:C").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
Next k
i = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(5, "A"), Cells(i, "B")).ClearContents
End Sub

今度はお望み通りになれば良いのですが・・・m(_ _)m

回答No.4

KURUMITO

まとめのシートのＣ5セルには次の式を入力してＥ5セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR(COLUMN(A1)>3,$B5=""),"",INDEX(INDIRECT($B5&"!A:C"),COUNTIF($B$5:$B5,$B5),COLUMN(A1)))

その2についてはマクロを使うことになります。
まとめのシートにコマンドボタンを配置し、そのコマンドボタンを右クリックして「コードの表示」を選択します。
表示される画面では次のコードを入力します。

Private Sub CommandButton1_Click()
Dim m, n, s As Integer
Set WS1 = Worksheets("まとめ")
Set WS2 = Worksheets("商品Ａ")
Set WS3 = Worksheets("商品Ｂ")
Set WS4 = Worksheets("商品Ｃ")
m = WorksheetFunction.CountIf(WS1.Range("B5:B50"), "商品Ａ")
n = WorksheetFunction.CountIf(WS1.Range("B5:B50"), "商品Ｂ")
s = WorksheetFunction.CountIf(WS1.Range("B5:B50"), "商品Ｃ")
WS2.Range(WS2.Cells(1, 1), WS2.Cells(m, 3)).Delete Shift:=xlShiftUp
WS3.Range(WS3.Cells(1, 1), WS3.Cells(n, 3)).Delete Shift:=xlShiftUp
WS4.Range(WS4.Cells(1, 1), WS4.Cells(s, 3)).Delete Shift:=xlShiftUp
WS1.Range("C5").Select
End Sub

お礼

ありがとうございます！

こちらもすごいですね。
感謝です！

回答No.2

tom04

こんばんは！
一例です。

まず「まとめ」SheetのA・B列は手入力（B列はリストでもOK）とします。
データは5行目以降に入力するとして、
C5セルに
=IF($B5="","",INDEX(INDIRECT($B5&"!"&ADDRESS(1,COLUMN(A1),4)&":"&ADDRESS(100,COLUMN(A1),4)),COUNTIF($B$5:$B5,$B5)))
という数式を入れオートフィルで列・行方向にコピーしてみてください。

これでB列に商品が入力されれば別Sheetのデータが1行目から表示されると思います。

次に2番目の質問に関してですが、
「まとめ」Sheetにコマンドボタンを配置　→　コマンドボタン上でダブルクリック　→　VBE画面に
↓のコードをコピー＆ペーストしてみてください。

Private Sub CommandButton1_Click()
Dim i As Long, k As Long, str As String, c As Range
For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row
str = Cells(i, 2)
With Worksheets(str)
Set c = .Range("A:A").Find(what:=Cells(i, "C"), LookIn:=xlValues, lookat:=xlWhole)
k = c.Row
.Rows(k).Delete shift:=xlUp
End With
Next i
i = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(5, "A"), Cells(i, "B")).ClearContents
End Sub

※　「まとめ」SheetのA・B列のデータも消去するようにしています。
一旦コマンドボタンをクリックすると元に戻せませんので
今あるデータは一旦保存して試してみてください。

※　無理にコマンドボタンではなく、オートシェイプ（図形）を挿入　→　右クリック　→　マクロの登録
でも同様の感じで可能です。m(_ _)m

補足

すごい！すごすぎます！！
こんな短時間でありがとうございます。

本当にありがとうございます！

m(_ _)mm(_ _)mm(_ _)mm(_ _)mm(_ _)m

さて、試してみました。

その１（まるで、問題なしです。すばらしい！）

その２（ちょいと、問題あり？）

こちらのマクロをしてみたんですが、
消されてる行がおかしく感じます。

何かちょっとしたことで、修正すれば、
いけそうな気がするのですが、どこをどうしたらいいでしょうか？

問題点はこちらにアップしてあります。

bsmile.sakura.ne.jp/phptest/bb1.jpg

できればもう少しお付き合いいただけると嬉しいです！

ただ、本当にどうもありがとうございました！

回答No.1

ztb00540

多分ＶＢＡでなければ無理でしょう。またはＶＢになります。