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も見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
[初心者です]VBAで指定列からAを検索し、発見したら隣のセルに値0を入れるマクロ。
Access(アクセス)
-
vba 2つの条件が一致したらコピーして別シートに値のみ貼り付け
Visual Basic(VBA)
-
-
4
Exel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について
Visual Basic(VBA)
-
5
エクセル 同じ値を探して隣の数値をコピーする
Excel(エクセル)
-
6
EXCEL VBA セルに既に入力されている文字に文字を追加する
Excel(エクセル)
-
7
VBA 値と一致した行の一部の列のデータを転記について教えてください
Visual Basic(VBA)
-
8
excel VBA 2つのシートの特定の列を比較して同じ値のセルがあったらその行を上書きしたい
Excel(エクセル)
-
9
【VBA】2つのシートの値を比較して条件一致したら、同じ行の隣の値を別ブックへ転記したいです。 VB
Visual Basic(VBA)
-
10
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
-
11
エクセルで同一シート内にある特定文字の右隣のセルの値を合計する方法はあ
Excel(エクセル)
-
12
エクセルで指定した行で条件に一致したセルの右隣のセルを取得したい
Excel(エクセル)
-
13
【VBA】特定の値が入った行をコピーして別シートに貼り付ける方法をおしえていただきたいです。
Excel(エクセル)
-
14
エクセルVBAで、条件に一致するセルへ移動
Excel(エクセル)
-
15
VBAを使って検索したセルをコピーして別の場所に貼り付ける。
Visual Basic(VBA)
-
16
ExcelVBAを使って、値がある場合は作業を繰り返し実行するプログラムを作成したい。
Visual Basic(VBA)
-
17
《エクセルVBA》「他の人が該当ファイルを使用中の場合」の処理
Excel(エクセル)
-
18
VBAで文字列を数値に変換したい
Excel(エクセル)
-
19
EXCEL VBAで、セルの文字列の前後に文字を入力する方法は?
その他(Microsoft Office)
-
20
【VBA】コピー&複数個所のペースト繰り返し
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル vbaについて教えてく...
-
Excel 複数列 A列B列C列一致 D...
-
【マクロ】名前を保存する際に...
-
エクセルであるセルに数字を入...
-
エクセルファイルがファイルの...
-
Excelについて教えてください
-
エクセル マクロ チェックボックス
-
【Excel】数字を3倍にし、なお...
-
エクセルVBA、別ブックへ転記す...
-
Excel分析ツールでのポアソン回...
-
Excelでセルの値が同じか...
-
エクセルで年休を管理する方法...
-
エクセル共有したが、アクセス...
-
エクセルのデーターが2か月前の...
-
【マクロ】顧客番号にて一致さ...
-
(マクロ)データをAブックからB...
-
エクセルを使っていて2024/5/15...
-
エクセル②
-
UNIQUE関数が使えないバージョ...
-
【マクロ】その時、その時で変...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報