VBAで条件に一致する複数セルの隣のセルの値を取得し、まとめる方法教えて下さい!
下記例の様に、A列に商品コード、B列にサイズ値の入力されたデータが8万行ほどあり、別シートに商品コード毎にサイズ値をまとめたいです。
例:
処理前
A列(商品コード) B列(サイズ値)
abc1000 S
abc1001 S
abc1001 M
abc1001 L
abc1002 XS
abc1002 S
処理後(別シート)
A列(商品コード) B列(サイズ値)
abc1000 S
abc1001 S:M:L
abc1002 XS:S
COUNTIFで連番を振ってMATCHとINDEX関数で抽出し、まとめるという方法で実現は出来たのですが、処理にかなりの時間を要するのでVBAならもっと高速にできるのではないかと思い質問させて頂きました。
よろしくお願い致します。
No.3ベストアンサー
- 回答日時:
>データが8万行ほどあり
ちんたらやってると、手でやるより遅くなります。
#1行目タイトル行、2行目からデータとする
sub macro1()
dim lastRow as long
’準備
lastrow = cells(rows.count, "A").end(xlup).row
range("A:B").sort key1:=range("A1"), order1:=xlascending, header:=xlyes
range("C:C").insert shift:=xlshifttoright
range("C2:C" & lastrow).formula = "=IF(A1=A2,C1&"":"","""")&B2"
’コード抽出
range("E:F").insert shift:=xlshifttoright
range("A:A").advancedfilter action:=xlfiltercopy, copytorange:=range("E1"), unique:=true
’結果転記
lastrow = cells(rows.count, "E").end(xlup).row
with range("F2:F" & lastrow)
.formula = "=VLOOKUP(E2,A:C,3)"
.value = .value
end with
range("C:C").delete shift:=xlshifttoleft
end sub
No.2
- 回答日時:
>処理にかなりの時間を要するのでVBAならもっと高速にできるのではないかと思い質問させて頂きました。
使用する関数の組み合わせなどに影響しますが、一般にエクセルに用意されている関数はプログラムが洗練されているため、極めて処理速度が速いので、必ずしもVBAで処理速度を向上できるとは限りません。
ただし再計算に時間がかかる場合はシートの動きが重くなるので計算方法を手動などにする必要があります。
このようなケースではピボットテーブルの機能を利用するのがお勧めです。
データ範囲をホームタブの「テーブルとして書式設定」でテーブルにしておき(この操作でデータの追加に自動対応します)、挿入タブのピボットテーブルで行フィールドに商品コード、列フィールドとΣ値(データフィールド)にサイズをドラッグしてピボットテーブルを完成させ、テーブル上で右クリックし「ピボットテーブルオプション」の集計とフィルタタブで列と行の総計を表示するのチェックを外します(添付画像の左側のテーブル)。
テーブルの右側の適当なセルに()添付画像ではK5セルに以下の式を入力し下方向にオートフィルコピーします。
=IF(B5,B$4&" ","")&IF(C5,C$4&" ","")&IF(D5,D$4&" ","")&IF(E5,E$4&" ","")&IF(F5,F$4&" ","")
これで、適宜不要な列(B列からJ列)を選択して右クリックから「非表示」にすればご希望の集計データになっています。
ちなみに、提示した数式はサイズ数の種類が最大5つある場合ですので、必要に応じて適宜関数をつなげてください。
また、区切り文字は全角スペースにしてありますが、コロンにしたい場合は以下のように数式を変更してください。
=SUBSTITUTE(TRIM(IF(B5,B$4&" ","")&IF(C5,C$4&" ","")&IF(D5,D$4&" ","")&IF(E5,E$4&" ","")&IF(F5,F$4&" ",""))," ",":")
No.1
- 回答日時:
こんにちは!
一例です。
標準モジュールに↓のコードをコピー&ペーストしてマクロを実行してみてください。
(Sheet1のデータをSheet2に表示するようにしてみました)
Sub Sample1() 'この行から
Dim i As Long, c As Range, wS As Worksheet
Set wS = Worksheets("Sheet2")
wS.Range("A:B").ClearContents
With Worksheets("Sheet1")
.Range("A:A").AdvancedFilter , Action:=xlFilterInPlace, unique:=True
.Range("A:A").Copy wS.Range("A1")
.ShowAllData
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
Set c = wS.Range("A:A").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If wS.Cells(c.Row, "B") = "" Then
wS.Cells(c.Row, "B") = .Cells(i, "B")
Else
wS.Cells(c.Row, "B") = wS.Cells(c.Row, "B") & ":" & .Cells(i, "B")
End If
Next i
End With
End Sub 'この行まで
こんな感じではどうでしょうか?m(_ _)m
早速のご回答ありがとうございます!
もちろんPCのスペックも関係あるとは思いますが、7万行弱のデータで動かしてみたら処理に10分以上掛ってしまいました。
VBAも書き方一つで処理速度は大幅に違うようですね。
参考とさせて頂きます。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Excel(エクセル) 重複データの抽出について 2 2023/07/21 14:52
- その他(Microsoft Office) EXCEL VLOOKUPに関する質問 5 2023/02/08 11:38
- Visual Basic(VBA) Excel vbaについて知恵もしくは、コード教えて下さいm(__)m ① 表にあるデータをコピー、 2 2022/09/01 23:57
- Visual Basic(VBA) VBA シート間の転記で、条件の追加コードの書き方について教えて下さい。 13 2023/02/26 09:31
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Visual Basic(VBA) 【VBA】複数行あるカンマ区切りのデータを全て縦に一列に並べたい 5 2022/04/13 17:03
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Visual Basic(VBA) チームごとにどの商品を何個希望しているか数量を算出したいです。 A列(A2~A265)に各チーム名が 3 2023/07/18 18:46
- Visual Basic(VBA) vbaマクロについて シート1のA列にある商品コードが シート2のB列にある商品コードに該当する場合 2 2023/05/17 13:41
このQ&Aを見た人はこんなQ&Aも見ています
-
「どうして捨てられないの?」前妻の物を捨てられない男性の心理って?
前妻の物を捨てられない理由に加え、捨てるための手段はあるのかを専門家に聞いてみた!
-
[初心者です]VBAで指定列からAを検索し、発見したら隣のセルに値0を入れるマクロ。
Access(アクセス)
-
vba 2つの条件が一致したらコピーして別シートに値のみ貼り付け
Visual Basic(VBA)
-
Exel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について
Visual Basic(VBA)
-
-
4
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
5
エクセルで指定した行で条件に一致したセルの右隣のセルを取得したい
Excel(エクセル)
-
6
excel VBA 2つのシートの特定の列を比較して同じ値のセルがあったらその行を上書きしたい
Excel(エクセル)
-
7
エクセル 同じ値を探して隣の数値をコピーする
Excel(エクセル)
-
8
【VBA】2つのシートの値を比較して条件一致したら、同じ行の隣の値を別ブックへ転記したいです。 VB
Visual Basic(VBA)
-
9
別のシートから値を取得するとき
Visual Basic(VBA)
-
10
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
-
11
エクセルで同一シート内にある特定文字の右隣のセルの値を合計する方法はあ
Excel(エクセル)
-
12
VBA 値と一致した行の一部の列のデータを転記について教えてください
Visual Basic(VBA)
-
13
自動で複数セルの内容をシート名にする
Excel(エクセル)
-
14
VBA 列全体を別シートの列と比較し、同じ値がある行の、右端に値をコピーする方法について
Excel(エクセル)
-
15
【VBA】特定の値が入った行をコピーして別シートに貼り付ける方法をおしえていただきたいです。
Excel(エクセル)
-
16
【VBA】指定した検索条件に一致したら別シートに転記したい
Visual Basic(VBA)
-
17
《エクセルVBA》「他の人が該当ファイルを使用中の場合」の処理
Excel(エクセル)
-
18
VBAを使って検索したセルをコピーして別の場所に貼り付ける。
Visual Basic(VBA)
-
19
EXCEL VBA セルに既に入力されている文字に文字を追加する
Excel(エクセル)
-
20
ExcelVBAを使って、値がある場合は作業を繰り返し実行するプログラムを作成したい。
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
2列に入っているデータを1列...
-
【ExcelVBA】ダブルクォーテー...
-
エクセルの数式バーのフォント...
-
【ExcelVBA】名前を付けて保存→...
-
Excel VBAで全ての矢印を赤色に...
-
エクセル関数を使って
-
エクセルでファイルの最終更新...
-
エクセルをエクセレントに究める
-
数字入力後他の文字等が表示さ...
-
【Excel】別シートから条件に合...
-
Excelに詳しい方! B列が「日...
-
エクセル シート表示
-
Excel 小さくなったスクロール...
-
excel2013 MonthDays 関数が使...
-
エクセルで 自動的に◯や数字を...
-
EXCELの散布図で日付が1900年に...
-
Excel関数について教えてくださ...
-
特定の文字列を含む、住所を抽...
-
LOOKUP関数を使えばいいのでし...
-
西暦や和暦の表示をyyyymmdd表...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報