1. HOME
  2. テックブログ
  3. Excelのファイル上でWebフォームのマネをする

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

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

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

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

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

動作上の仕様

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

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

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

作り方

フォームデータの前準備

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



figure 1

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

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

figure 2

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

figure 3

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

figure 4

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

figure 5

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

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

リストを作るための設定

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

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

役割 名前 参照範囲に入力する値
メーカー用 maker_scope =OFFSET(maker!$A$2,0,0,COUNTA(maker!$A:$A),1)
車種名用 name_scope =OFFSET(name!$A$2,0,0,COUNTA(name!$A:$A),1)
グレード用 grade_scope =OFFSET(grade!$A$2,0,0,COUNTA(grade!$A:$A),1)


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

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

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

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

<入力規則の設定>

役割 「元の値」に入れる内容
メーカー用 =maker_scope
車種名用 =OFFSET(name_scope,MATCH(D2,name_scope,0)-1,1,COUNTIF(name_scope,D2),1)
グレード用 =OFFSET(grade_scope,MATCH(CONCATENATE($D2,$E2),grade_scope,0)-1,1,COUNTIF(grade_scope,CONCATENATE($D2,$E2)),1)

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

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

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

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


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

figure 6

Tips

  • 入力規則の設定時、エラーメッセージ等の設定をしなければ、手動入力も可能です。この時、マスタデータと同じ名称を入れないと次の選択肢が正しく表示されません。
  • また、すでに選択された入力規則をコピーし、同じ列の未入力のセルにペーストすると入力の手間が省けます。今回の場合、メーカー名や車種名が同じであれば、一気にコピペすることで1つずつ選択していく部分が一気に捗ります。
  • マスタデータ側(figure 2〜4)に別の列にIDなど、別の情報を追加しVLOOKUP関数を活用すると、選択した内容から自動でその情報を取り出すことが可能です。
    実際のExcelファイルでは、運用上のデータ投入をより簡単に且つ確実にデータができあがるような仕組み/関数処理を施しており、間違っていた場合の処理もExcelの関数内で可能にしています。

おわりに

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

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

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

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

ファブリカコミュニケーションズで働いてみませんか?

あったらいいな、をカタチに。人々を幸せにする革新的なサービスを、私たちと一緒に創っていくメンバーを募集しています。

ファブリカコミュニケーションズの社員は「全員がクリエイター」。アイデアの発信に社歴や部署の垣根はありません。

“自分から発信できる人に、どんどんチャンスが与えられる“そんな環境で活躍してみませんか?ご興味のある方は、以下の採用ページをご覧ください。

◎ 新卒採用の方はこちら
◎ キャリア採用の方はこちら

この記事を書いた人

齋藤 直彦
IS事業本部 IT管理統括部長兼CISO
齋藤 直彦

おすすめの記事