ロット数で抽出したい
- 多数の品名があり、それぞれ複数のロットのデータがあります。ロット数が4ロット以上の品名だけを抽出してデータ解析をしたいのですが、うまい方法はあるでしょうか?
- 品名は1000以上、全ロット数は30000ほどあります。現在は=countif($A$1:$A$30000,A1)という数式を使って抽出していますが、時間がかかってしまいます。
- もっと簡単な方法でロット数が4ロット以上の品名を抽出する方法を教えていただきたいです。
- ベストアンサー
ロット数で抽出したい
多数の品名があり、それぞれ複数のロットのデータがあります。 例えば、 A列 B列 C列 品名A ロットNo.123 データ0.1 品名B ロットNo.235 データ0.5 品名C ロットNo.567 データ3.1 品名A ロットNo.125 データ0.2 品名D ロットNo.425 データ1.2 : : : と言う感じで、品名は1000以上、全ロット数は30000ほどあります。 ここでロット数が4ロット以上の品名だけを抽出してデータ解析をしたいのですが、うまい方法はあるでしょうか? 一応、D列の一行目に =countif($A$1:$A$30000,A1) と入力後、オートフィルで全てコピーしてやれば後はオートフィルターで4以上を指定してやればよいのですが、この数式をオートフィルでコピーする際に非常に時間がかかります(20分くらい)。 もっと簡単にやれる方法があれば教えて頂けると助かります。
- shibisei
- お礼率63% (53/83)
- オフィス系ソフト
- 回答数3
- ありがとう数2
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
データの並べ替えを行っても問題がなければ下記の方法で少しは早くなると思います(COUNTIFの実行回数が減るはずなので・・・)。 ・品名順に並べ替える ・D2に=IF(A2=A1,D1,COUNTIF($A$2:$A$30000,A2)) ・式をD3~D30000までコピーする ・オートフィルタでD列が4以上のフィルタをかける
その他の回答 (2)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんばんは。 十分に検証を重ねてはいますが、出来上がりは、あまり芳しい状態ではありませんでした。内容的には、簡単ですが、いかに速くする、ということを目的にして作ったのですが、残念ながら、せいぜい6分を切る程度でした。問題になったのは、Excelのデータベース演算が標準で、ワイルドカードになっていること、CountIf を使うと、ひじょうに遅くなるということでした。この処理に手間が掛かりました。 途中で、これは、データベースのグループ化で抽出したほうが速いと気が付きました。 出力値は、E列から、3列を使いますが、K列にクライテリアを置くようになっています。 テンポリー出力したものは、最後に削除しています。 必ず、<標準モジュール>に貼り付けしてくださるようにお願いします。 3万件で、6分というところが目処です。あまり長い場合は、どこかでトラブルが発生しているかもしれません。予想のつかないトラブルがあるかもしれません。 ところどころで、画面を更新させるようにはしています。 今回、途中で止めるオプションはつけてありませんが、Ctrl+Break で止まります。しかし、オブジェクトを抱え込んだままですので、できればそのまま使わずに、一旦、終了させたほうが安全です。 '<標準モジュール> Option Explicit Sub PickupCount() Dim rng As Range Dim a(), ar() Dim rtn As Long, buf As Long Dim i As Long Dim rnum As Variant, rngValue As Variant Dim CriteriaRng As Range '設定 Const PickUp As Integer = 4 'カウント数の下限 Range("D1").ClearContents Range("E1:F1").CurrentRegion.ClearContents Set rng = Range("A1", Range("A65536").End(xlUp).Offset(, 2)) 'AdvancedFilterの前の条件をクリア Call DbNamesDelete Application.ScreenUpdating = False '並べ替え rng.Sort Key1:=Range("A1"), _ Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ SortMethod:=xlPinYin '抽出1 rng.Resize(, 1).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("E1"), _ Unique:=True Application.ScreenUpdating = True Application.ScreenUpdating = False 'Pickupのカウント(以上)の抽出 With Range("E1", Range("E65536").End(xlUp)) rngValue = Range("E1", Range("E65536").End(xlUp)).Value 'Countifの代用 buf = 1 For i = LBound(rngValue, 1) + 1 To UBound(rngValue, 1) rtn = Application.Match(rngValue(i, 1), rng.Resize(, 1), 1) .Cells(i, 1).Offset(, 1).Value = rtn - buf buf = rtn Next i 'クライテリアの消去 Call DbNamesDelete Range("E1:F1").Value = Array(Range("A1").Value, "QTY") Range("H1:H2").Value = Application.Transpose(Array("QTY", ">=" & PickUp)) If Range("E1").CurrentRegion.Rows.Count = 1 Then GoTo LineQuit .Resize(, 2).AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=Range("H1:H2"), _ Unique:=False On Error Resume Next .SpecialCells(xlCellTypeVisible).Copy Range("K1") On Error GoTo 0 ActiveSheet.ShowAllData Range("E1").CurrentRegion.ClearContents Range("H1:H2").ClearContents End With Application.ScreenUpdating = True Application.ScreenUpdating = False 'クライテリアの作成 ' Range("K2", Range("K1").End(xlDown)).Offset(, 1).Formula = "=""<>""&RC[-1]&""?""" Range("K2", Range("K1").End(xlDown)).Offset(, 1).Value = _ Range("K2", Range("K1").End(xlDown)).Offset(, 1).Value Set CriteriaRng = Range("K1", Range("K1").End(xlDown).Offset(, 1)) Range("K1").Offset(, 1).Value = Range("K1").Value Application.ScreenUpdating = True Application.ScreenUpdating = False '抽出2 Call DbNamesDelete rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=CriteriaRng, _ CopyToRange:=Range("E1").Resize(, 3), _ Unique:=False LineQuit: CriteriaRng.ClearContents Application.ScreenUpdating = True Call DbNamesDelete Set rng = Nothing: Set CriteriaRng = Nothing End Sub Sub DbNamesDelete() Dim nm As Name 'データベース関数の予約語を削除 For Each nm In ThisWorkbook.Names If nm.Name Like "*[DatabaseCriteriaExtract]*" Then nm.Delete Next End Sub
お礼
う~、色々とありがとうございます! けれど申し訳ありませんが、よく分かりません。 また1の回答の方の方法で、何とかなるようです。 ご面倒おかけしました!
- Wendy02
- ベストアンサー率57% (3570/6232)
VBAで作ってみました。 >品名は1000以上、全ロット数は30000ほどあります。 ただ、1つ確認ですが、 >4ロット以上の品名だけを 品名だけでよいのですか? それが違うと、無駄になるので、確認してから、アップロードします。 それと、できれば、フィールド行が1つほしいところです。 (つまり、1行目を、A1:品名 B1:ロットNo. C1:データ) としていただきたいこと。そうしないと、数の計算を間違えることがあります。 フィルターオプション(AdvancedFilter)を使う理由からです。 なお、確認のために、数えた数を、品名の隣に出します。 例: E列 F列 品名 数 a 40 b 25 計測時間は、私の古いパソコンで、品目1,000件、1つずつ調べて、30,000個のデータを、出力するのに、約2分20秒程度かかります。
補足
お世話様です。 >品名だけでよいのですか? 誤解しやすい表現ですみませんでした。 品名だけであればピボットで簡単にできますね。 4ロット以上ある品名と、個々のロットのNo.とデータを抽出するのが目的です。逆に言うと、ロット数が3ロット以下の品名のデータを除外したいのです。 ですので、はじめの質問で記載した方法で実行すれば可能なのですが、それの処理速度を改善したいと考えています。 >それと、できれば、フィールド行が1つほしいところです。 につきましては、問題ありません。実際にはフィールド行使用しています。 よろしくお願いします。
関連するQ&A
- 【エクセル】抽出データを上に詰めて表示させたい。
重複するデータを上に詰めて表示さセル方法が解らないです。 B列はA列の元データから重複分は表示しないようにしています。 C列はA列の元データから重複分の数を数えています。 │A │B │C│ D │ 1│あああ │あああ │3│ 2│いいい │いいい │2│ 3│あああ │ │ │ 4│ええ │ ええ │2│ 5│おおお │おおお │1│ 6│ええ │ │ │ 7│いいい │ │ │ 8│あああ │ │ │ 9│うううう│うううう│1│ ・ ・ 上記のような表で、A列が元データで、 B列にはセルB1から、=IF(COUNTIF($A$1:A1,A1)>1,"",A1)という関数を オートフィルで下まで伸ばしています。 C列は、=IF(COUNTIF($A$1:A1,A1)>1,"",COUNTIF($A$1:$A$100,A1))と いう関数を使用しています。 そこで、B列C列の何も表示されていないセルを詰めて、上に詰めて 表示させたいです。マクロは良くわからないのでなるべく関数で お願いします。ちなみに、今使用している関数も、こうしたほうが いいというのがありましたら。訂正してもらえると助かります。 解りづらいところがありましたら、補足します。 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- 2つのBook間で共通のキーワードを使いデータを転記するには?
下記のようなデータがある場合、Book1/sheet1のD列へ Book2/sheet1 C列のデータを転記したい。キーワードは 各Book B列のロットNO.です。どのようなマクロを 組めばいいのでしょうか? Book1/sheet1 A B C D 1 品名 ロットNO. 数量 2 A A123 25 50(転記) 3 A A234 20 75(転記) 4 A A345 22 60(転記) ・ Book2/sheet1 A B C D 1 品名 ロットNO. 時間(HR) 2 A A123 50 3 A A234 75 4 A A345 60 ・
- 締切済み
- オフィス系ソフト
- ExcelのCOUNTIFで条件の値に1を足す方法
ExcelのCOUNTIFを使って集計をしています。 「Log」とういうシートのデータを固定で範囲指定(オートフィルで範囲が変わってしまうため)し、その中に「Sheet1」のA列のデータ(文字列:氏名)といくつ合致しているかを集計しようとしています。 =COUNTIF(Log!$B$7:$B$510,"*A1*") 上記数式をG1に入力しオートフィルでコピーしてもコピー先の数式は何も変わりませんでした。 =COUNTIF(Log!$B$7:$B$510,"*A2*")と手入力し再度コピーすると ・・・"*A1*"、・・・"*A2*"、・・・"*A1*"、・・・"*A2*"となるだけでした。 どうすればオートフィルでA1,A2,A3…となってくれるでしょうか? もし他の(簡単な)方法があれば教えていただければ幸いです。 使用ソフト:Excel2002 よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- エクセルのオートフィルタでのデータ抽出&カウント
エクセルのローデータでのオートフィルタでのデータ抽出&カウントで困っています。 A列にNo.、B列にデータB、C列にデータC、D列にデータDがあるとします。 それぞれの列に1~9までの数字があり、 それぞれの列、 データの一番下(正確には1行開けて)にはSUMやCOUNTIFなどの関数(計算式)が入っています。 で、これをフィルターにかけ抽出した際、この抽出したデータのみで計算の値を表示したいのですが、 やってみると 全データの計算の値で表示されてしまいます。 どうすればよいのでしょうか? 大変困っています。 よろしくお願いいたします。
- 締切済み
- オフィス系ソフト
- Excel マクロで連番作成方法を教えてください。
OS:Windwos2000 Excel2000 以下の内容をマクロで実施する方法を教えてください。 過去の質問を確認しましたが、 私の希望する内容とは分部とはことなるため、 質問させていただきました。 A列に入力されているデータの最終行まで 各B列、C列、D列にオートフィル機能を使って数式コピーを行いたい。 ※A列のデータ数は固定ではなく増減します。 ※B列、C列、D列の先頭行には数式を入力済みです。 ※オートフィル機能でなくても問題ありません。 よろしくお願いいします。 A列 B列 C列 D列 E列 データ =LEFT(A1,4) - =MID(A1,8,14) =B1&C1&D1 データ =LEFT(A2,4) - =MID(A2,8,14) =B2&C2&D2 データ =LEFT(A3,4) - =MID(A3,8,14) =B3&C3&D3 データ =LEFT(A4,4) - =MID(A4,8,14) =B4&C4&D4 データ =LEFT(A5,4) - =MID(A5,8,14) =B5&C5&D5 ・
- ベストアンサー
- オフィス系ソフト
- オートフィルターで抽出されたデータの参照方法について
1000件以上のデータをA,B、C列にオートフィルターをかけて抽出されたデータのA,B、C列およびD列目の値を参照する式を組みたいのですが、当然のことながらオートフィルターをかける都度、表示されるセル番地が変わるので式がくめません。 多分、マクロでコピーしてどこかにペーストすればいいのでしょうが、まったくわかりません。お教えください。
- 締切済み
- オフィス系ソフト
- オートフィルで数式をコピー出来ない
例えばA列にデータが縦に1列 B列に別のデータが縦に1列 C列でAをBで割って達成率を出したい。 C1のセルが「=A1/B1」となるような式で C1のセルをオートフィルでコピーしたら 思っていたのと違う式でコピーされた。 オートフィルで下が C1=A1/B1 , C2=A2/B2 , C3=A3/B3・・・・のようにその行ごとに計算できる様にするにはどうすればいいでしょう? 設定みたいなものが必要でしたら教えてください。
- ベストアンサー
- オフィス系ソフト
- エクセルの文字列中の指定した位置の文字列の置き換え(REPLACE)。
エクセルの文字列中の指定した位置の文字列の置き換え(REPLACE)。 エクセルのセルA1に 「A123B1234X9876C123DD」と入力されています。 これを 「A123-B123X9876-C123-DD」としたいです。 現在 ・B1のセルに=REPLACE(A1,5,0,"-") ・C1のセルに=REPLACE(B1,15,0,"-") ・D1のセルに=REPLACE(C1,20,0,"-") と入れて、D1にできた値の 「A123-B123X9876-C123-DD」 をコピーして E1のセルに「形式を選択して貼り付け」「値」で貼り付けして B1~D1を削除して ・A1のセルには元の「A123B123X9876C123DD」 ・B1のセルには編集後の「A123-B123X9876-C123-DD」 が表示されるようにしています。 このA列が100行もあるとうんざりです。 式をオートフィルでコピーしていくのですが ・B1に式を入れてオートフィルでB100までコピー ・C1に式を入れてオートフィルでC100までコピー ・D1に式を入れてオートフィルでD100までコピー ・D1~D100をコピーしてE1~E100に「形式を選択して貼り付け」「値」で貼り付け ・B,C,D列を列削除 3回は式を入れないといけないです。一発で編集する方法はありますでしょうか?
- ベストアンサー
- オフィス系ソフト
- セルに数式が入ってるかどうかを取得する関数は?
C1には、=SUM(A1:B1) C2には、0 が入力されています。 実際、A1:B1の値は0なので、 C1もC2も0が表示されています。 D列で、ちゃんとC列にsum関数が入ってるか調べてたいのですが =COUNTIF(C1,"*sum*") をオートフィルしても、0が返ります。 C3に「sum」と言う文字列を入れて、=COUNTIF(C3,"*sum*")をしたら 1が返りました。 COUNTIF関数は文字列は認識しますが数式は認識しないようです。 このような場合、セルに数式が入ってるかを取得する方法はありますか?
- ベストアンサー
- Excel(エクセル)
- 10進数を5進数に変換する式
A1=1,A2=3,A3=5・・・(10進数)とした場合、B1=1,B2=3,B3=10・・・(5進数)のように変換するときオートフィルで100列まで表示したいのです。 どのような式になるでしょうか。 回答よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
お礼
早速やってみました。 少しどころか、非常に早く処理が可能でした。 一分もかかりません。 COUNTIFの実行回数を減らすのが有効なのですね。 ありがとうございました!