画像と商品データをECサイトで紐づけるために、画像のファイル名をカンマ(コンマ)でつなぐ必要があるんだ。
ぶっちゃけ手作業なんかでは出来ないからいい方法はないかな…
こんな悩みを解決します。
エクセルのマクロ(VBA)を使って「-」より前が共通しているセルの文字をカンマで繋ぐ方法(コードはコピペでOK)
僕はECキューブで制作した自社サイトを運営しているのですが
画像とサイトを紐づけるために、画像ファイル名をカンマで繋いだデータが必要だと知った時は焦りました。
いったいどうすれば…?まさか一個ずつ手作業でやるなんて冗談じゃないぞ
なんて、冷や汗をダラダラ流しながら解決策を探した結果
いろいろと参考にして組み合わせたマクロがちょうどいい感じに動いたので紹介します。
マクロなんて使ったことないよ!
という人も安心してください。
基本的にコピペでOK。
エクセル初心者、PC初心者でも出来るよう説明していきます。
「-」より前が共通しているセルの文字をカンマで繋ぐエクセルマクロ
ではまず、具体的にどんな動きをさせていくか
実際に「画像ファイル名をカンマで繋ぐ」工程で説明します。
a001-01.jpg |
a001-02.jpg |
a001-03.jpg |
a002-01.jpg |
a002-02.jpg |
b001-01.jpg |
上記のようエクセルに添付された画像ファイル名を「-」より前(「a001」「a002」など)が一致しているセル
を「,」で繋いでいきます。(下記)
a001-01.jpg | a001-01.jpg,a001-02.jpg,a001-03.jpg | “a001-01.jpg,a001-02.jpg,a001-03.jpg” |
a001-02.jpg | a002-01.jpg,a002-02.jpg | “a002-01.jpg,a002-02.jpg” |
a001-03.jpg | b001-01.jpg | “b001-01.jpg” |
a002-01.jpg | ||
a002-02.jpg | ||
b001-01.jpg |
実際にはダブルクォーテーション(”)で囲う必要があるので、そこまで自動で行えるようにしています。
使用するコードはこちら
Sub 画像のファイル名をカンマでつなぐ()
Dim i As Long
Dim j As Long
Dim Lastrow As Long
Dim LastrowD As Long
Dim Buf As String
Dim text As String
text = """"
'最終行の取得
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
'-よりも前の文字列をB列に返す
For i = 2 To Lastrow
If InStr(Cells(i, 1), "-") > 0 Then
Cells(i, 2) = Left(Cells(i, 1), InStr(Cells(i, 1), "-") - 1)
End If
Next
'重複データをまとめてデータをつなげる()
j = 1
For i = 2 To Lastrow
Buf = Buf & Cells(i, 1) & ","
If Cells(i, 2) <> Cells(i + 1, 2) Then
Cells(j + 1, 3) = Cells(i, 2)
Cells(j + 1, 4) = Left(Buf, Len(Buf) - 1)
Buf = ""
j = j + 1
End If
Next
'最終行の取得
LastrowD = Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To LastrowD
Buf = Cells(i, 4).Value
Cells(i, 5).Value = text & Buf & text
Next
End Sub
手順説明の時にもう一度紹介するので、ここではコピーしなくていいですよ
では手順に沿って説明します。
手順1:エクセルのA列に画像ファイル名を貼り付ける
まずは元となる画像ファイル名をエクセルのA列の2行目以降に貼り付けていきます。
画像ファイルの名前を一括で取得する方法はこちらの記事を参考にしてください。
手順2:VBAを開いてコードを貼り付ける
マクロ、VBAを使ったことがない人もいると思うので開くところから説明します。
まずはキーボードの左下にある「Altキー」を押しながら右上にある「F11」を押してください。
僕はノートPCなので多少違いがあるかもしれませんが、位置関係は大体一緒のはずです。
押すとVBAが開きます。
ノートPCを使っていて「開かないぞ?」という人は
「Altキー」の二つ左にある「Fnキー」を一度押してから再度試してください。
次はコードを貼り付けるところまで説明します。
上にあるバーから「挿入」→「標準モジュール」の順にクリックしてください。
すると下記のようなシートが表示されます。
このシート内にコードをコピペすればOKです。
Sub 画像のファイル名をカンマでつなぐ()
Dim i As Long
Dim j As Long
Dim Lastrow As Long
Dim LastrowD As Long
Dim Buf As String
Dim text As String
text = """"
'最終行の取得
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
'-よりも前の文字列をB列に返す
For i = 2 To Lastrow
If InStr(Cells(i, 1), "-") > 0 Then
Cells(i, 2) = Left(Cells(i, 1), InStr(Cells(i, 1), "-") - 1)
End If
Next
'重複データをまとめてデータをつなげる()
j = 1
For i = 2 To Lastrow
Buf = Buf & Cells(i, 1) & ","
If Cells(i, 2) <> Cells(i + 1, 2) Then
Cells(j + 1, 3) = Cells(i, 2)
Cells(j + 1, 4) = Left(Buf, Len(Buf) - 1)
Buf = ""
j = j + 1
End If
Next
'最終行の取得
LastrowD = Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To LastrowD
Buf = Cells(i, 4).Value
Cells(i, 5).Value = text & Buf & text
Next
End Sub
コピーして貼り付けてください
手順3:マクロを実行
最後にマクロを実行しましょう。
上にあるバーから「ツール」→「マクロ」の順にクリックします。
あとはマクロを実行するだけです。
エクセルに戻ると下記のような感じにマクロが実行されています。
D列に「-」より前が一致したA列をカンマで繋いだものが並んでいます。
E列はD列をダブルクォーテーション「”」で囲ったものです。
必要な方をドラッグしてコピーしてください。
「ー」ではなく「_」では出来ないの?
今回のコードでは出来ませんが、少しだけコードを変更すれば可能です。
もともとは「_」で紹介されていたコードを僕が使いやすい仕様に変更したコードなので大丈夫です。
変更する箇所は”ー”のところを”_”にするだけでOKです。
実際に変更して実行した結果は以下です。
まとめ~たくさんの商品を扱うECサイト運営者はぜひ覚えて欲しい~
今回は、マクロ(VBA)を使って「-」より前が一致するセルをカンマで繋ぐマクロを紹介しました。
手順は
- エクセルのA列2行目以降にデータを貼り付ける
- 「Alt]+「F11」でVBAを開く
- 挿入→「標準モジュール」でシートを開く
- 開いたシートにコードをコピペする
- 「ツール」→「マクロ」→「実行」でマクロを動かす
以上の工程を踏めばOKです。
「ー」ではなく「_」でデータを保存している人はコードの”ー”の部分を”_”に変更してください。
ECサイトに多くの商品をアップしている人にとって画像ファイル名の取得は時短に関わる作業です。
ぜひ作業時間の短縮に活用してください。