Excelで「データ項目を抽出・削除・入れ替え・計算して新ファイル作成」する時間を1000分の1にしたアドオンができるまで

　私は、ある小売り企業のWebサイト作成担当をしていたことがあます。
　規模が十数名という会社だったので、当然のようにWebショップの商品登録もやっていました。
　人数を聞けば小さい会社ですが取扱商品数は５０００種以上なので、更新があるたびにいちいちカート管理画面でやっていては追いつきません。
　そこでcsv等を使った一括登録をするわけですが、それでも毎月１５０件以上の入れ替えがあるし、仕事は登録だけではありませんから、始めの頃はまったく追い付きませんでした。

　これは、そんな状況から商品登録作業時間を１００分の１以下に短縮したExcelアドオン作成の話です。

複雑なことはしないのに、時間がかかる

とりあえずコピペ→手修正

　一括登録を始めた頃は、手作業で「社内データからExcelへ、ぽちぽちコピー＆貼り付け（以下コピペと表記）をし、登録に使えるよう修正」していました。
　手作業は、商品も、商品ごとの設定項目もたくさんあるだけに、修正も大変、間違いも当然多く、大変でした。
　社内データを登録したい順に並べてリストにすれば、列単位ではまとめてコピペできますが、それでは「管理画面で入力するよりはけっこうマシ」な程度で、やはり追いつきませんでした。

式を使ったテンプレート

　次にやってみたのは、登録用にするブックに式を入力して元データのある別のブックからデータを取ってくる方法。
　「登録用に定型語句を追加する」「データを組み合わせてWeb用の商品名にする」位は自動になったぶん楽になりましたが、カテゴリや税率はあいかわらず商品名を見ての判断が必要です。
　なによりこの方法ではcsvで保存する前に元のファイル（社内データ）を閉じてしまうとデータが消えます。
　それにcsv保存は自動で思いがけない変換をされてしまうことがあるので、開きなおすと値が変わっていることもあります。ハイフンを含む数字や日付などにはよく起こりますね。
　それを避けるには、すべてのセルを「文字列のみ」にする必要がありました。
　そのため、セルの書式設定を文字列に固定してからデータを埋め、データがそろったら「全データをコピー → 同じ場所に値だけにして貼り付け」をしていました。
　データの入ったセルの量が多いとコピペにも時間がかかり、この方法でも「若干早くなったな」程度でした。

ストレス

　この頃には、
「たいしたことしてないのに、時間かかるなぁ…」
　と思い始めました。
　手作業で間違いが発生するのもかなり嫌でした。「貼り付け位置を間違う」「内容がずれる」「税率を間違って販売額が正しくない」等はよくありました。
　客対応は担当者が別なので、間違うと、かかわる人が増えるせいもあってその都度注意されますし。
　もともと住所や電話番号なども読み上げたときの音のつながりメインで記憶しているタイプなので「目で見てチェックする」というのはやや苦手。普通の人よりミスが多かったのでしょう。
　そもそも手作業なら一定数間違いが出るのは当然だと思いますが、そういう前提はなかったですね。なので間違い指摘が続くとイライラMAX！(笑)
　いつの間にか「Webショップの商品登録が本業で、社のWeb更新・情報管理がサブ業務」みたいになってしまって、「 話が違うよ… 」的なストレスも加わり
「せっかくＰＣ使うのだから、間違いなくやらせたい」
　と、そればかり考えるように。

　そこで、やってみようと思ったのがマクロでした。

間違いを！減らし！たい！んだよ！

とりあえず記録マクロ

　それまではマクロを使ったことがなくてイメージがわかなかったのですが、ネット検索で調べてみると「記録する」方法が出てきて「なるほどな」と思いました。
　記録する方法でなるべく手間をかけずに登録フォーマットにするにはどうしたらいいか試行錯誤したところ、
「元のデータから新しいファイルへ必要行（商品の単位）をコピペし、貼り付けた新しいファイルの列を移動したり式の設定をしたりして、ある程度形にする」
　ことに落ち着きました。
　これによって始めの頃よりはかなり早くなりましたが、やはり手作業はけっこう発生するし、手作業が多ければ間違いもたいして減りません。

　どちらかというと早さよりも間違いがないようにしたかったので、まだこの方法では不満でした。

人間が手を出さない方が間違わないはず

　そこで
「間違いがないようにするにはどうすればいいか？」
　と考えたとき、
「データを作るときには極力人間がかかわらない方がいい」
　という結論になりました。
　この職よりかなり前にはなりますが、プログラマだったこともある私。マクロを調べたときに記録ではなく「書く」こともできるということは知っていたので、

　それなら自分で書いてしまえ！

　と、なりました。
　ExcelのマクロVBAは初めて書くので、色々事例を調べたりMicrosoftのサイトでリファレンスをみたり。業務の合間に書くのでなかなか進みませんでしたが、２ヶ月くらいでなんとか完成。

　コピペで作っていたころは１０件のデータ作成に１時間位かかっていたものが、間違いはないしほとんど修正も必要ないものが５分かからずにできる！

　マクロにやらせて良いことは、
　カート登録データに間違いがあっても、ほとんどの場合、元（社内）のデータが間違っているから
　なので、気持ちの負担が少ないことですね。

　やっぱりＰＣにやらせて自分は楽した方が断然いい！
　そこからちょっと自信をつけたので、いろいろ速くする技も調べて使い、５分かかっていた量でも１分程度に短縮。
　自分で書いておいて感動ものでした。

データフォーマットが変わる→マクロ作り直し

　感動もつかの間。
　借りているカートの新機能追加や変更で、データフォーマットが変わりました。
　前述のマクロは記録したものを参考にして書いたので、データ項目の判定を列位置でしていました。
　登録用のフォーマットが変わったということは、列に項目が加わったり項目自体が変わったりしたということで、マクロを修正。
　カートが便利になるほど項目が増える、そうするとマクロの改修が必要になる。これではいたちごっこですね。
　逆戻りしたような気がして、またふつふつと不満が…。

このままのマクロではダメだ！

　ほぼ同じころ、社内データからExcel形式へ出力する列並びが変わりました。
　理由を調べると、実店舗用ＰＯＰ出力担当が、価格帯ごとにＰＯＰデザインを変えるためにプリントのつど、列位置の移動をしているということが判明。
　社内の商品データは、ＰＣ導入時に作った(！)古いデータを、手の空いている人が入力する方法で編集・追加・削除して使ってきたため、誰でもデータ構成を変えることができる状態だったのです。
　それまで私がデータを使おうとした時たまたまいつも同じ並びだっただけなのでした。

　データ運用ルールが存在しない、なんなら社長自らも変更していると分かったので、こちらが対応するしかないという判断をし、今度はデータのタイトル行をみて列を判定するマクロに書き直しました。
　しかしこれも、項目が追加されればその追加された項目を判定するコードを追加しなくてはなりません。

　双方ともに変わる可能性がある。
　いつ変わるかも分からない。

　毎回項目が変わるたびに改修なんて面倒くさい！

　そこで「マクロを変えずに済むには、項目の変換を何らかの形で指示すれば良いのでは？」ということに気づいた私。
　変換表を読ませれば「社内データ→カート登録用データ」の変換ができるマクロを組むことにしました。

変換表方式に

マクロにどれが項目か分からせる

　変換表を読ませて実行するマクロを作成するにあたって、まず問題となるのは変換表の形式でした。
　単純に元のデータのタイトル行にある項目名を指定するだけだと、

・項目１と項目２を組み合わせて、新しい１つの項目にする
・複数の項目を使った計算をする

　などができません。（なぜできないかを書くと技術的な話が必要になってくるので、ここでは割愛します）
　しかし四則演算位はできて欲しいし、登録用のデータなので商品ページに表示するHTMLもある程度自動で書かせたい。
　そこで、項目名を記号で囲ってマクロが判断できるようにすることにしました。
　最初は正規表現の特殊文字ではない"<"と">"で囲おうかと思ったのですが、変換表にHTMLを使いたいときに判定が難しくなるため、一般的なデータには使われる率が低いと思われる"["と"]"に決定しました。

変換表の作成を簡単にする

　ただすべての使いたいタイトルを手作業で"[""]"で囲うのは大変過ぎます。
　しかも、万一その囲い記号がタイトル名に含まれていたら正しく変換できません。
　そこで、囲い記号に印をつけた上で囲うマクロも作ることにしました。

　例： 項目[1] というタイトルに囲い記号をつける
　　　 項目[1] → [項目\[1\]]

　このマクロを使うと、選択されているセルが"[""]"で囲われます。
　これで変換表作成が少し楽になりました！

変換表を作る

　"[""]"囲いをするマクロで作ったセル内容をコピーして変換表を作ります。
　いきなり作成に取り掛からず、"[""]"囲いされたセルを任意のテキストエディタにコピペして保存しておくとExcelの履歴数をかせげる(※)ので、MIFES・EmEditorフリー・TeraPadなどに一時的に貼り付けていました。
（※Excelは「セルの中身」を選択すると、セルの選択とセルの中身の選択で２回の履歴となるので、セルの中身からコピペするより外部からコピペした方がUnDoできる回数が増えます）

　変換表作成例はこんな感じ。

◆例１： 「価格[セール]」という項目に税率1.1をかけたい
　　　　 　↓　変換表は
　　　　 [項目\[セール\]]*1.1

◆例２： 「キャッチコピー」「コメント」という項目から商品ページの紹介文を作る
　　　　 　↓　変換表は
　　　　 <span style="font-size: xx-large">[キャッチコピー]</span><br>[コメント]

　こういった指示を書いて変換表ができたら、いざ変換！

速い！

　量的には１回の変換で２０００セル位は作るのですが、半分くらいは社内データとは関係なく固定なので、本当に速くなりました。
　始めの頃の手作業だと慣れても１０件に１時間位かかったので１件に平均６分間違い多々、マクロを導入した初期が３０件位まで３～５分だったので１件８秒程度でなお間違いあり。
　でも変換表を使うタイプまで改造が済んだら、３０件位はかかっても１秒強。
　手作業時代からは実に1000倍速以上に。間違い修正時間も考慮したらもっと速いですね。
　マクロ初期時代と比べても25倍以上の速さに。

これ、汎用性があるのでは？

アドオンにすれば他の人も使えそう

　ここまで作って思ったのは、「そこそこ汎用性があるのではないか？」ということ。
　そこで、仕事とは別に、配布方法について調べ始めました。

　そのままだとコード丸見えだけど？
　xlsmファイルだと「マクロリストからの実行」なので使い勝手が悪くない？
　配布はどうやって？

　等々調べていると、「アドオン」にすればよいと分かりました。アドオンならリボンにボタンをつけることもできます。
　「アドオンなんて、もともとあるものを有効にするかどうか」だと思っていたのに、自作できると知って狂喜(笑)。
　アドオンの作り方を調べまくって、作っては試し作っては試し。とりあえず形になったので、自分のExcelに設置。

　さくさく作業、便利です。

配布します（とりあえず無料です）

　自分が使う分には問題がないことが分かったので、作りかけサイトにお試し版を置きました。
　ツール系ダウンロードサイトを使う手もあったのですが、それほど出来に自信満々でもないので、「需要があったらね」と、あとまわし。

　興味がおありの方は　　　からどうぞ。
　もしお使いいただいた方がいたら感想など下さると喜びます。

　「もう少し洗練されたら販売したいな」と、「この程度なら無料でもいい？」を行ったり来たりしております。
　質の向上次第では販売も視野に入れています。いまダウンロード時にメルアド入力してくださった方には、バージョンアップしてもずっと無料のつもりです。

　興味がおありの方は　　　　　どうぞ。