ネットショップを運営していると、 セールに合わせて価格を変更したり商品名にキーワードを追加したり、 ダウンロードした商品データを編集したりする機会がたくさんあります。セールリストを作成することになって、Excelでの集計作業に頭を悩ませている人も多いのではないでしょうか。アパレル関係のショップの場合、販売ページ上と在庫管理の単位が異なる場合があり、それも集計作業をややこしくする原因のひとつとなっています。
販売数や在庫数を加味したセールリストを作る場合、 サイズカラーを含めたSKUごとの実績データが必要となります。ただし、販売しているのがアイテム単位の場合、親番号に対するセール価格を決めなければいけません。ボーダーTシャツSサイズ赤色の在庫が2枚、 ボーダーTシャツMサイズ青色の在庫が5枚というSKU単位のデータを元に、ボーダーTシャツのセール価格を決めるということです。
Excelで効率的にさくっと算出したいところですが、親番とSKUごとの商品コードという異なる単位が混在していてはうまく計算することができません。そこで当記事では、そんな状態から効率的にセールリストを作成できる Excel関数を使用した数式を紹介します。Excelのことなんて全く知らなかったわたし自身が、10年間ECサイトと関わる上で確実に役立った数式です。
SKUごとの商品コードから親番を抽出
管理上の理由から、商品コードにはショップごとのルールが決まっており、多くのショップでは「親番-サイズカラー」という形が採られています。「Tshirt-m-red」のように親番の後にサイズやカラーを付け加えたり、「 Tshirt-0201 」のようにサイズコードやカラーコードを定めて数字を付け加えたりする場合もあるようです。
この 「親番-サイズカラー」から親番だけを抽出する数式は、関数を組み合わせて作ります。
LEFT関数で指定文字数を抽出する
LEFT関数は、左端から指定文字数分の文字列を抽出する関数です。LEFT(文字列,[文字数]）の形で入力します。A1セルに「Tshirt-m-red」という商品コードが入力されている場合、「=LEFT(A1,6)」と入力すると左から6文字、親番の「Tshirt」が抽出できます。
ちなみに、RIGHT関数という関数もあり、こちらは右から指定文字数を抽出します。末尾のカラーコードだけ抽出したい場合などに使ってみてください。
親番の文字数が決まっている場合はLEFT関数だけで事足りますが、親番が「knit」「skirt」などいろいろな文字数である場合、文字数を指定することができません。
FIND関数と組み合わせる
親番の文字数が一定でない場合は、LEFT関数にFIND関数を組み合わせます。
FIND関数は、検索したい文字が何文字目にあるかをカウントしてくれる関数です。 FIND(検索文字列,検索対象,[開始位置]）の形で入力します。A1セルに「Tshirt-m-red」という商品コードが入力されている場合、「=FIND(“-“,A1)」と入力すると7と表示されます。検索文字列として指定した ハイフンが7文字目にあるということです。
このFIND関数で、LEFT関数の指定文字数を算出します。A1セルが「Tshirt-m-red」の場合、「=LEFT(A1,FIND(“-“,A1)-1)」の数式で親番を抽出できます。FIND関数で指定したハイフンは、抽出したい親番の次の文字です。実際に抽出したいのは親番だけなので、 FINF関数の後に「-1」を付け加えるのを忘れないようにしましょう。
商品コードの中に記号がたくさん含まれる場合
親番の取り方にはショップそれぞれの法則があると思います。その法則によっては、前述の数式ではうまく親番を抽出できないことがあります。その場合は、数式を少しアレンジして対応してみてください。
例として、「Tshirt-1」「skirt-2」などのように、親番自体にハイフンが含まれる場合について考えてみましょう。
カウントした文字数の後ろに注目
A1セルが「Tshirt-m-red」の場合、ハイフンの位置は7文字目でした。「Tshirt-1-m-red」の場合も7文字目ですが、ハイフン位置は親番の途中です。そこで、先ほどの数式を「=LEFT(A1,FIND(“-“,A1)+1)」に変更してみましょう。「-1」から「+1」に変更することで、抽出する文字数をハイフンの後の数字を含んだところまで指定することができます。
ただ、この方法ですと 「Tshirt-12」「skirt-123」など、連番が2桁や3桁になると抽出できなくなるという問題点があります。
SUBSTITUTE関数で置き換える
SUBSTITUTE関数は、文字の置き換えができる関数です。SUBSTITUTE(文字列, 検索文字列,置換文字列,[置換対象])の形で入力します。セール用キーワードを削除したり新しいキーワードに置換したりすることができるので、いろいろな場面で役に立ってくれることでしょう。
この関数の置換対象は、文字列の中に指定文字が複数含まれていた場合に、どれを置換対象とするかを指定できます。省略可能ですが、その場合は全ての文字が置換されます。「=SUBSTITUTE(A1,”-“,”★”)」なら全てが置換されて「Tshirt★1★m★red」となり、「=SUBSTITUTE(A1,”-“,”★”,2)」なら2つ目のハイフンだけが置換された「Tshirt-1★m-red」となるということです。対象のハイフンだけを置換することができれば、置換した文字の位置をFIND関数でカウントし、LEFT関数で親番を抽出することが可能となります。
別のセルでそれぞれ数式を入力してもいいですし、ひとつのセルに数式をまとめることも可能です。まとめる場合は「=LEFT(A1,FIND(“★”,SUBSTITUTE(A1,”-“,”★”,2))-1)」と、FIND関数の検索対象の部分にSUBSTITUTE関数を使用する形となります。
関数自体よりも組み合わせが大切
Excelの関数を知っていると、「手作業だとちょっとしんどいな」と思うようなことを効率化できます。いろいろな関数を知っているとさぞかし便利だろうと思われるかもしれませんが、実際は数式を考えることの方が重要です。
知っている関数をどう組み合わせたら望む結果が得られるのかを考え、それを見つけることがExcel効率化のキーポイントです。その中で、便利な関数や使い勝手の良い関数は自然と覚えます。逆に、あまり使わない関数は自然と忘れていくことでしょう。
今回紹介した関数はどれも簡単な関数ですが、組み合わせ方によっていろいろなことが実現可能となります。新たな関数を覚えるときは、スタンダードな使い方に加えて、どう応用したら自分の業務より便利に使えるかを一緒に考えるようにしてみてください。面倒なセールリスト作成も、効率的に行うことができるようになることでしょう。
