Excelのファイル上でWebフォームのマネをする

当社が運営する中古車検索サイト「車選びドットコム」で扱っている車種のマスタデータはかなり大量にあります。また提携先の車種のマスタデータもあるので、サイト維持のために車種名のマッチング作業を行うことが多々あります。マッチング作業とは、AというデータをBというデータに置き換えるためのデータを作る作業です。

Webフォームでマッチングをする際には、1件ずつselectタグで表示された中から選び、Javascript+APIなどで次のselectタグを動的に出す…という作りになることがほとんどです。
ただ大量にデータがある場合、Webフォームでは簡単にコピペをすることも厳しく、inputタグなんか使った時にはマッチングのクオリティも下がってしまうことにつながるため、Webフォームのままではかなりの苦行となってしまいます。

そこで色々な人が使い慣れたExcelでコピペをしやすくし、時短を図った時の内容について今回書いていきます。

ちなみにこれをGoogleスプレッドシートでやろうと思っているのですが、入力規則の範囲指定にOFFSETがうまく使えないなどがあり頓挫しています。

動作上の仕様

最初にこのExcelファイルを作ろうと思った時の仕様を書いておきます。

1. マスタデータは増減するので追加/削除などのデータを入れ替えた時に追従しやすいこと
2. 「メーカー名」「車種名」「グレード名」と選択した時に間違いがないこと
3. 文字入力もある程度でき、コピペで一気にデータを反映させることが可能にすること
4. マクロを組まずに作成すること

つまり「運用の状況になったら技術力の差があろうとなかろうと誰でもできる」ことを想定しています。

作り方

フォームデータの前準備

1．マスタとなるデータを準備します。これがないと始まりません。(figure 1)

2．新規ブックを作成します。

3．「メーカー名のリスト」のシートを作成し、シート名を[ maker ]とします。この時並べ替えをしておくと、VLOOKUPなどを使って別の加工がしやすくなります。(figure 2)

4．「メーカー名と車種名のリスト」のシートを作成し、シート名を[ name ]とします。同様に並べ替えをしておくと良いでしょう。(figure 3)

5．「データ全てのリスト」のシートを作成し、シート名を[ grade ]とします。この時「メーカー名と車種名を結合した列」と「グレードのリスト」の状態にします。(figure 4)
これは「メーカーが違っているが車種名が同じ」場合などでグレードの選択時に正しい値を返せなくなるのを防ぐためで、文字列を結合することでユニークなデータを生成する非常に有効な手段です。

6．マッチング用のシートも作成します。(figure 5)
A〜C列を元データでD〜F列が作業時の入力部分になり、D〜F列は全て入力規則で埋め尽くすこととなります。(列については説明のため、最小限にしています)

実際に使う時にはmaker/name/gradeの各シートは見えている必要もないので、非表示にしたり保護したりしてデータが誤って書き換えられないようにすると良いでしょう。

またシートを分けていますが、シートを分けたほうがデータ保護等の観点で管理しやすいだけですので、動作原理を理解していてデータ数が少量であれば1シートでできないことはありません。

リストを作るための設定

1．数式→「名前の管理」で、範囲に名前を付けます。

※名前は後で使う適当で構いません
この時OFFSET関数を使って、マスタ側の件数が増えても追従するようにしています。

参照範囲については、「COUNTA関数でA列に文字が入っている数を検出」し、「OFFSET関数で範囲を確定する」ということを行っています。
これにより、マスタとなるデータが増減をしてもCOUNTAで追従する…というわけです。

2．入力規則でリストを作っていきます。

具体的な入力内容は下記の＜入力規則の設定＞を参照してください。

1. D列で、メーカーを選択できるように入力規則で「入力値の種類」でリストを選択し、元の値に「=maker_scope」と入れます。名前の管理で設定した名称を入れるだけなので、ここは簡単です。
2. E列で、車種名を選択できるように、入力規則を使いますが、動的に絞り込んだ状態で表示させるには、ここでもOFFSET関数を使います。MATCH関数で、選択したメーカー(例:アウディ)を元に、車種名のリストの中からメーカー部分が合致した行の車種名だけの範囲(COUNTIF関数で返ってきた)を取り出すことになります。
3. グレード名も同様に入力規則でOFFSET関数を使い、MATCH関数でメーカー名と車種名を結合したデータを元に、グレード名のリストの中から合致した行のグレード名だけの範囲を取り出します。

＜入力規則の設定＞

3．一旦テストをしましょう。

メーカー→車種→グレードと選択していくと(マスタとなっている各シートが正しくできていれば)切り替わることが判ると思います。

4．テストが無事終わったら…

3ヶ所の入力規則の登録されたセルを、一気に必要なだけコピペし、忘れずに保存しましょう。

これでガンガンデータ入力ができるように(しやすく)なり、以下(figure 6)のようなデータが出来上がっていくことになります。

おわりに

無理やりExcelでセルにフォームのようなリストを作ってみました。

Tipsにある通り、これによりデータ投入しやすい加工済みデータを簡単に作成できるようになり、作業自体慣れていくと、Webフォームの数倍のスピードでデータを扱うことができています。

今回、当社での普段から取り扱っていることが多いことからサンプルとして車種マスタを使いましたが、他の情報でもやり方を応用することで同様にドロップダウンリストにより動的に別のセルの選択肢を抽出するということは十分に可能で、Excelファイル上のデータ入力時のミスを発生させづらい形を作り上げることができています。

神エクセル/Excel方眼紙のような扱い方ではなく、表計算ソフトとしてデータを活用する方法の1つではないかな…とか思います。