- ベストアンサー
Excel VBAで在庫状況に基づいて売上表の行を削除する方法
ka_na_deの回答
- ka_na_de
- ベストアンサー率56% (162/286)
#2です。 作り直しました。問題無いか試してみてください。 問題がなければ、教えてください。 追加補足質問の(2)に移ります。 Sub test3() Dim Ws1 As Worksheet Dim Ws2 As Worksheet Dim myLastRow1 As Long Dim myLastRow2 As Long Dim myCustomer As Long Dim myStocks As Long Dim i As Long Dim j As Long Dim k As Long Dim flg As Boolean Dim myRange As Range Dim mySum As Long Application.ScreenUpdating = False Set Ws1 = Worksheets("在庫状況") Set Ws2 = Worksheets("売上表") myLastRow1 = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row myLastRow2 = Ws2.Cells(Ws2.Rows.Count, "A").End(xlUp).Row With Ws2 For i = 2 To myLastRow1 myCustomer = Ws1.Cells(i, "A").Value myStocks = Ws1.Cells(i, "D").Value k = 0 mySum = 0 flg = False If .AutoFilterMode Then .AutoFilterMode = False With .Range("A1").CurrentRegion .AutoFilter Field:=1, Criteria1:=myCustomer .AutoFilter Field:=5, Criteria1:="No" End With Set myRange = .Range("A1:A" & myLastRow2).SpecialCells(xlCellTypeVisible) Set myRange = Intersect(.Range("A2:A" & myLastRow2), myRange) If Not myRange Is Nothing Then myRange.EntireRow.Delete If .AutoFilterMode Then .AutoFilterMode = False myLastRow2 = Ws2.Cells(Ws2.Rows.Count, "A").End(xlUp).Row With .Range("A1").CurrentRegion .AutoFilter Field:=1, Criteria1:=myCustomer .AutoFilter Field:=5, Criteria1:="Yes" End With Set myRange = .Range("A1:A" & myLastRow2).SpecialCells(xlCellTypeVisible) Set myRange = Intersect(.Range("A2:A" & myLastRow2), myRange) If Not myRange Is Nothing Then mySum = Application.WorksheetFunction.Subtotal(9, myRange.Offset(, 3)) End If If myStocks < mySum Then With myRange.Offset(, 3) For j = 1 To .Cells.Count k = .Cells(j).Value + k If k > myStocks Then If flg = False Then .Cells(j).Value = .Cells(j).Value - k + myStocks .Cells(j).EntireRow.Copy Ws2.Rows(myLastRow2 + 1).Insert Shift:=xlDown Application.CutCopyMode = False Ws2.Cells(myLastRow2 + 1, "D").Value = k - myStocks Ws2.Cells(myLastRow2 + 1, "E").Value = "No" myLastRow2 = myLastRow2 + 1 flg = True Else .Cells(j).Offset(, 1).Value = "No" End If ElseIf k = myStocks Then flg = True End If Next j End With ElseIf mySum < myStocks Then Ws1.Rows(i).EntireRow.Copy .Rows(myLastRow2 + 1).Insert Shift:=xlDown Application.CutCopyMode = False .Cells(myLastRow2 + 1, "D").Value = myStocks - mySum myLastRow2 = myLastRow2 + 1 End If Next i .AutoFilterMode = False .Range("A1").CurrentRegion.Sort _ Key1:=.Range("A1"), Order1:=xlAscending, _ Key2:=.Range("E1"), Order2:=xlDescending, _ Header:=xlGuess End With Application.ScreenUpdating = True Set Ws1 = Nothing Set Ws2 = Nothing Set myRange = Nothing End Sub
関連するQ&A
- Excel VBA 集計方法
1つのファイルに2つのシートがあります。 集計結果を反映するシートと元データのシートで別れています。 <元データシート> 品番 品名 バージョン 数量 11 ABC Soft 2000 4 22 XYS Beta 2003 3 23 HU22 hyoukaban 2000 4 45 298 Software 1998 7 22 XYS Beta 2003 11 25 XYS Beta 2008 3 <集計結果シート> 品名 バージョン 数量 XYS Beta 2008 3 XYS Beta 2003 14 298 Software 1998 7 ABC Soft 2000 4 HU22 hyoukaban 2000 4 ※表がずれていると思います。 バージョンは4桁の数字です。 マクロを実行して、自動的に元データの情報を集計して 集計結果シートに反映したいと思います。 (1)品番は一意の番号です (2)同じ品名ごとに並べて、同じ品名が見つかった場合はバージョンの新しいものが上になるようにしたいです。 (3)品番は集計結果シートには反映していません。 集計結果シートのような結果にするには、どのようなマクロを書けば いいのか悩んでいます。 サンプルコード等参考になるものがございましたら、お教えください。
- ベストアンサー
- その他MS Office製品
- Excel VBA 集計方法
1つのファイルに2つのシートがあります。 集計結果を反映するシートと元データのシートで別れています。 <元データシート> 品番 品名 バージョン 数量 11 ABC Soft 2000 4 22 XYS Beta 2003 3 23 HU22 hyoukaban 2000 4 45 298 Software 1998 7 22 XYS Beta 2003 11 25 XYS Beta 2008 3 27 XYS Beta 2008 6 <集計結果シート> 品名 バージョン 数量 XYS Beta 2008 3 XYS Beta 2003 14 298 Software 1998 7 ABC Soft 2000 4 HU22 hyoukaban 2000 4 ※表がずれていると思います。 バージョンは4桁の数字です。 マクロを実行して、自動的に元データの情報を集計して 集計結果シートに反映したいと思います。 (1)同じ品名ごとに並べて、同じ品名が見つかった場合はバージョンの新しいものが上になるようにしたいです。 (2)品番は集計結果シートには反映していません。 (3)同じ品名、バージョンで異なる品番がございます。 同じ品名、バージョンであれば品番が異なっても1つに集計することは可能でしょうか。 →(3)だけが理解できていません。(1)と(2)は解決済みです。
- ベストアンサー
- その他MS Office製品
- ExcelのVBAで作りたいんです
Excelのマクロで以下のようなものを作りたいのですが、調べても適当なものが見つからず、どのようなコードを書いたらいいのか分からないので有識者の方のお力をお貸しください。 Sheet1に検索ワードが入力された表を作成しておきます。(表自体は数十行程度で1列だけ) Sheet2にSheet1に書かれている検索ワードがあるかどうかを調べて、該当した場合にはその行を削除する。 もしくは別シートに移動する。 この作業をSheet1の検索ワード行数分自動で処理する。 Sheet2は数百行程度で複数列ありますが、検索ワードが該当さえすれば削除(移動)してしまって構いません。 また、話は変わりますがこのようなものも作りたいのです・・・。 CSVからの整形用マクロがあるのですが、整形処理が終了したら整形済みのシート(例としてSheet2~5)から1つのシートにまとめたい(Sheet1へ)と思っているのですが、データがどこまで入力されているか(Sheet2~5に)の判断と、Sheet1へのコピーでSheet1にどの行までデータが入っていてどの行から追加すればいいのかをお教えください。
- 締切済み
- オフィス系ソフト
- エクセルでの集計について
エクセルでの集計について質問です。 【4月】 (営業A) (営業B) A B C D E F 会社名 品名 売上金額 会社名 品名 売上金額 ○○ ×× ¥10,000 □□ △△ ¥50,000 □□ ■■ ¥30,000 ★★ ○○ ¥20,000 ○○ ☆☆☆ ¥50,000 ○○ ??? ¥30,000 上記のように1枚のシートに営業さんごとに分けて月別の売上表を作っています。 最後のシートに個人ごとの年間売上げ表を作りたいのですが、 【年間合計】 (営業A) (営業B) A B C 会社名 売上金額 売上金額 ○○ ¥60,000 ¥30,000 □□ ¥30,000 ¥50,000 ★★ ¥0 ¥20,000 上記のように会社名ごとに年間合計を作る場合にはどのような方法がありますでしょうか?(年間合計に品名は必要ありません) SUMIFを入力してみたら、ものすごく式が長くなってしまいました… 他に方法がありましたら宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel 2007 マクロ 特定条件の抽出方法
Excel 2007 マクロ作成について質問させていただきます。 Sheet1に下記2つの表があります。 表の内容を変更せずに(列を追加する等) 特定条件を別Sheet2にコピーして貼り付けたいと思います。 <くだもの在庫表> 型番 商品名 産地 本数 AA-1 りんご 国産 1 AA-2 なし 外国産 4 VB-3 ばなな 外国産 <野菜在庫表> 型番 商品名 本数 HU-3 きゅうり 5 LO-7 いも LLU-89 だいこん 3 <条件> 本数が空白以外の行をコピーしてSheet2に貼り付けます。 結果イメージは画像をご参照ください。 2つの表の列数が異なるため、ピボットテーブルを作成して フィルタオプションで抽出することは難しいとおもいます。 どのようなマクロになりますでしょうか。 アドバイスよろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- 在庫表シートから、売れた商品の「整理番号」を含む行を削除したいのですが
マクロで”在庫管理”をしようとしています。 ”在庫表”シートのI列の2行目(セル;I2)に「整理番号」のタイトル、データは3行目から下方向に4桁の数値で並べて200行程度あります。(行方向には、仕入日、商品名、仕入先、金額などのデータが入れてあります。) 「売上入力シート」に、売れた「整理番号」をセル:D3から下方向に数量分入力し、それを売上表に転記(ここは出来ました)後、在庫表から売れた「整理番号」を含む行を削除したいのですが、これの”マクロコード”ができず、悩んでおります。 ヒントでも教えていただければ、助かります。 マクロの勉強を始めて数ヶ月の初心者ですが、よろしくお願いします。
- ベストアンサー
- Windows Me
- エクセルで表の相違確認をVBAでしたい。
お世話になっております。 下記の様な表がシート1とシート2に有、 シート2がシート1と比較して、行が多かったり、または 数量だけが変わっていたり、行が減っていたり、 表の行数は100から200行くらいあるので、VBAでどこが 違うかをすぐ分かる様に確認したいと思っています。 下記の表はあくまでも例ですが、実際は同じNO・同じ品番・同じ品名・同じ数量で複数行あったりします。 どの様に記述していいか分からなく、ご教示下さいます様宜しくお願い致します。 記 A列 B列 C列 D列 E列 No 品番 品名 地区 数量 236 A6TET みかん 市川 3本 248 A7TGR りんご 松戸 5本 273 B9GKT ぶどう 柏 14本 273 A8UFE すいか 行徳 6本 381 C2ROF いちご 平井 22本
- ベストアンサー
- Visual Basic
- Excel97で集計
Excel97を使っています。 D列 K列 L列 2 ○○商事 鈴木太郎 100 150 3 ○○商事 田中一郎 250 50 4 ▲▲株式会社 150 100 5 ○○商事 小林花子 200 50 6 ▲▲株式会社 50 200 7 (株)○○商事 100 150 8 △△テクノロジー(株) 300 10 という表があります。 D列を丸めて、K列とL列の合計を出したいのですが、 どのようにしたらいいでしょうか? D列を丸める際に、 例えば、2、3、5、7行目は丸めて、 D列に「○○商事 計」 K列に、650 K列に、400 と表示したいのです。 事前に並べ替えをしていても、上のように離れていたりしています。 また、実は上の内容をマクロでやりたいのですが、 そちらも解る方がいらっしゃれば教えて頂きたいです。 宜しくお願いします!
- ベストアンサー
- オフィス系ソフト
- 【excelマクロ】重複データをチェックしてその行を削除・表を集計して整頓するマクロ
MC28SP 会社で資材の在庫管理表を作成しているのですが、大変困っております。 マクロ初心者で技術不足なのでどうかご教授願います。 「資材受け入れシート」として、下の表があります。 1 2 3 4 受入日 品名 Lot 数量 7/7 A BNR32 10 7/8 A BNR32 5 7/10 B SW200 2 7/7 B AE860 4 7/8 B SW200 12 7/9 C GD300 10 7/7 C GD300 1 7/7 C DC200 7 これを2列目「品名」をキーとして「Lot」を確認し、同じ(つまり同じ物)であればその行を削除して、数量を加算して1行にまとめるマクロを作りたいのです。ポイントは(1)2列目「品名」の重複確認のみで行削除ではなく、3列目「Lot」も確認する必要があることと、(2)削除してからその「品名」がある行に削除した「数量」分加算しなくてはいけないことだと考えているのですが・・・。 1 2 3 4 受入日 品名 Lot 数量 7/8 A BNR32 15 7/10 B SW200 14 7/7 B AE860 4 7/9 C GD300 11 7/7 C DC200 7 「受入日」の所はできれば最終日になれば良いかなと思っています。 会社で期限を決められているのですが、手こずってしまい前へ進みません。説明が分かりづらいかもしれませんが、どうか宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセル関数での集計
営業マン毎に新規顧客営業管理表があります。 {鈴木のリスト} A B 1 <顧客名> <対象性> ・・・ 2 三菱商事 対象 3 住友商事 対象 4 双日 非対象 5 清水建設 対象 6 ・・・ {山田のリスト} A B 1 <顧客名> <対象性> ・・・ 2 鹿島建設 対象 3 大成建設 非対象 4 竹中工務店 対象 5 ・・・ 上の二つのリストは、同一ファイルの別々のシートになっています。 これとは別に、顧客名簿一覧表があります。 A B C 1 <顧客名> <新規対象客> <住所> 2 三菱商事 3 住友商事 4 双日 5 清水建設 6 ・・・ ここで、上の新規顧客営業管理表の二つのシートにおける、<顧客名>と<対象性>の二つの条件を判断して、上の顧客名簿の<新規対象客>の欄に「新規対象客」、または、「新規非対象客」と表示させたいのです。 例えば、顧客名簿一覧表の三菱商事について、鈴木のリストと山田のリストの二つを参照して、<対象性>に対象とあれば、「新規対象客」、そうでなければ「未対象客」と表示させたいのです。 一つのシートで一つの条件で判断するのであれば、=IF(countif(範囲,A1),"取引顧客",”未取引顧客”)でできると以前教わりましたが、(1)顧客名と対象性の両方に合致すること、(2)参照先が2つの表(シート)になることから、まったく手に負えません。どうかご教示願います。
- ベストアンサー
- オフィス系ソフト
補足
修正ありがとうございます。 内容を確認しました。 問題ございません。 引き続きよろしくお願いいたします。 別件ですが、先日ご紹介頂きました「世界でいちばん簡単なExcel VBAのe本」を購入しました。代入やカウンタ変数の箇所はとてもわかりやすく理解できました。基本文法は何度も繰り返し勉強して理解を深めたいと思います。