長岡工業高等専門学校研究紀要

論第 52 巻（2016）

文入学志願者増加を目指す広報活動のためのオープンデータを活用したデータ分析
高橋 章1・上村 健二1・佐藤 拓史1・佐藤 秀一2
1 電子制御工学科（Dept. of Electronic Control Engineering, National Institute of Technology, Nagaoka College）
2 一般教育科－物理（Liberal Arts-Physics, National Institute of Technology, Nagaoka College）

Data Analysis for Publicity Campaign Aiming at Increasingin Candidates for Admission Utilizing Open Data
Akira TAKAHASHI1, Kenji KAMIMURA1 , Hiroshi SATOH1, and Shuichi SATO2
Abstract
This paper describes data analysis for publicity campaign aiming at increasing in candidates foradmission of National Institute of Technology, Nagaoka College. Both the efficiency and the accuracy ofreception procedure for Open Campus are increased by utilizing open data published on website of Niigata
prefecture, and summary tables of junior high schools and municipalities for participants are generated
automatically. In addition, databases of junior high schools are constructed by gathering open data, recordof entry for Open Campus, record of admission, and so on. Finally, as an example of data mining, the resultof promotion campaign to visit junior high schools selected from the databases is shown.
Key Words : open data, data mining, database, spreadsheet, spreadsheet function

１．はじめにービスも推進されている．

情報通信技術（ICT; Information and Communication
Technology）の進展により，インターネット上で教として，中学校からの入学志願者を安定確保するこ高等専門学校の学校経営における至上命題の一つ

育や商取引などを行うための様々なサービスが開発とがあげられる．長岡工業高等専門学校（以下，本校）においても，平成 25 年度に行われた学内組織され，利用が広がっている．膨大な顧客データや購改革により入学者対策室と広報委員会が一元化され，買履歴などを収集してデータベースを構築し，それ広報戦略室として志願者を増やすための広報活動をらを分析して動向の予測等につなげるデータサイエ担うこととなった．その代表的な活動として，中学ンスや，個々のユーザへのきめ細やかな対応や新た校で開催される高校説明会における学校紹介，本校な商品・サービス開発に役立つ付加価値を創造した

教員による中学校訪問の企画，オープンキャンパス

り意思決定に活用したりするデータマイニングが盛および入試説明会の計画・実施などがある．従来，んに行われており，これらを総称してビッグデー

これらの活動に付随して作成されるデータは，学生タと呼ぶことも多い．国や自治体が様々なデータをWeb 上で公開するオープンデータと呼ばれる行政サ課入試係で年度およびイベント毎に記録・管理され1)ていた．より戦略的な広報活動を行うには，個別に- 72 -

高橋 章・上村 健二・佐藤 拓史・佐藤 秀一

図-1

市町村立中学校（抜粋・一部加工）3)

作成されるデータを適宜集約して分析することによ

別の参加状況を迅速かつ的確に把握することが望まり，経年的な変動や，イベント間の参加者の推移なしい．しかし，従来は，国立大学附属の中学校名をどの動向を，迅速かつ正確に把握することが求めら入力する場合の大学名の省略形や「附属/付属」のれる．しかし，そのようなデータ分析を行うには，表記の揺れ，市町村立の中学校名を入力する場合に作表ソフトウェアやデータベース管理に関する知識間に「立」を加えるかどうかといった表記の揺れがとスキルばかりでなく，戦略的な視点が不可欠であ混入しやすく，集計する際の確認作業に手間がかかり，人的にも時間的にもコストが高いという課題がり，精度にも問題があった．

あった．

そこでオープンデータを活用し，参加者の所属中

本報告では，新潟県が Web で公開しているオー
プンデータ2)を活用し，オープンキャンパスの受付

学校の入力作業の労力を軽減し，集計作業の精度向上を図る．

作業における労力を軽減し，集計作業の迅速化および精度向上をはかるためのデータ処理について述べ

２．１

中学校一覧

る．さらに，県内の公立中学校の生徒数や，本校と新潟県が公開するオープンデータのうち，市町村の関わりをデータベース化して行った分析について

教育委員会・学校一覧（中学校一覧）は，県内の市町村立中学校 230 校を一覧表にした Microsoft Excel

述べる．これまでオープンキャンパスへの参加者がなく，出願者のいなかった中学校へ訪問活動を行うことによって，効果的に PR 活動の成果につながったデータマイニングの事例を示す．

形式のファイルである 3)．図-1 のように，印刷資料としても利用できる書式設定が行われているものの，2 行目以降は 1 行 1 レコードとして扱えるデータベース形式となっている．
2 行目はデータベースの見出し行で，1 レコード

２．オープンキャンパス受付・集計作業

が A 列～F 列の 6 フィールドからなり，それぞれ，No（学番），市町村，学校名，郵便番号，所在地，オープンキャンパス（OC）は中学生とその保護電話番号が格納されていることを示す．学番は上越者を対象とする，本校最大の PR イベントである．
平成 25 年度以降，参加者は増加傾向にあり，7 月の地区，中越地区，下越地区の順に付けられているが，一ヶ月間で 400 名を超える中学生からの参加申し込

なお，ファイルは毎年，年度当初に更新されるが，みを受け付け，8 月上旬の数日間で体験学習の割り

学校の統廃合が行われた年には学番が変化するため，付けを行い，参加者への通知文書の発送を行う．参注意が必要である．また，国立大学の附属中学校は加申し込みの手段には郵送，FAX，電子メールなどがあり，その大部分は手作業で受付簿に入力しなけ掲載されておらず，県立および市立の中等教育学校（中高一貫校）は平成 27 年度は記載されていたが，ればならないため，人的コストを低減し，入力ミス平成 28 年度では省かれているため，毎年確認と手等を防ぐ対策が望まれている．また，OC 終了後の動の追加作業が必要である．

同一市町村内の学校名の順に規則性はみられない．

入試説明会，秋季中学校訪問などのイベントの計画・実施を円滑に進めるために，中学校別や市町村- 73 -

入学志願者増加を目指す広報活動のためのオープンデータを活用したデータ分析

２．２ オープンキャンパスの受付簿作成
OC の受付簿として，参加者の情報を入力するシート ES と，中学校一覧を含むシート SL の 2 シートからなるファイルを作成する．シート SL には中学校一覧の内容をコピーする．受付作業者が，OC参加申込書から参加者の情報をシート ES に入力する際に，在籍中学校の情報をシート SL のデータベースから検索機能によって自動入力することで，入力作業の軽減と表記の揺れや誤変換の混入を予防す
る．
シート ES における学番，市町村，中学校を入力する 3 フィールドを順に G 列，H 列，I 列とする．
学番フィールドのセル g2 に，シート SL のデータベースに登録された在籍中学校の学番を入力するだけで，その中学校の市町村名を，セル h2 に自動入力させるためには，次の関数式を入力すればよい 5)：=vlookup($g2, SL!$A:$C, 2, false)
ここで，vlookup 関数は 4 つの引数を持つデータベース関数である．第 1 引数は検索値を，列を絶対参図-2照とする$g2 と指定している．第 2 引数は検索範囲を，SL!$A:$C として，シート SL の学番，市町村，学校名が含まれる A 列～C 列を絶対参照により指定している．vlookup 関数では検索範囲の左端の列（A 列）から検索値を探す．第 3 引数は列番号として，検索値が見つかった場合にそのレコード（行）の何列目のデータを取得するかを指定するが，今回は 2 列目（B 列の市町村）としている．第 4 引数は検索の型を指定するが，false の場合は検索値と厳密に一致するレコードのみを検索し，見つからなければエラー値を返す．true または第 4 引数を省略した場合には，検索値の近似値（検索値未満の最大値）を許容するが，これを指定する際には検索範囲の左端の列は昇順に並べておく必要がある．

セル i2 には，セル h2 の関数式をコピーした上で，vlookup 関数の第 3 引数を 3 として，学校名を取得するように変更する．そして，セル範囲 h2:i2 の関数式を，下方のセルにオートコピーすることで，受付簿の準備が整う．
なお，県外からの参加申込みがあった場合は，シート SL に適宜中学校情報を追加すればよい．
２．３

SL に参加者フィールド（E 列）を追加する．そして，セル e3 に次の関数式を入力する：

ところで，前述の関数式ではセル g2 が未入力のい：オープンキャンパスの集計自動化

中学校別の参加者数を集計するために，シート=countif(ES!$G:$G, a3)場合に，h2 にはエラー値#N/A が入力されてしまう．
これを避けるためには if 関数でラップすればよ

市町村別オープンキャンパス参加者集計表

すなわち，シートESのG列中で，セルa3と同じ学番の個数を数え上げる．
次に市町村別の集計シートML（図-2）を用意し，=if($g2="","",vlookup( 引数は省略 ))地区，市町村，参加人数を入力する3フィールドを

ここで，if 関数は第 1 引数の条件式が真であるときは第 2 引数，偽であるときは第 3 引数を返す．よって，前述の関数式ではセル g2 が未入力（空文字）

順にA列，B列，C列とする．市町村ごとの参加者を求めるため，当初はセルc3に次の関数式を入力すればよいと考えた：の場合は，h2 も空文字としておき，そうでなければ vlookup 関数による検索結果を自動入力する．
- 74 -=sumif(SL!$B:$B, b3, SL!$E:$E)

高橋 章・上村 健二・佐藤 拓史・佐藤 秀一

すなわち，シートSLのB列の値が，セルb3と一致す以上の作業により，集計はすべてオンタイムで実るレコードについて，E列の値の合計を求める．し行され，受付処理の任意の時点で最新の状況を正確かし，期待通りに動作しないためシートSLのデーに把握することができる．

タベースを精査したところ，市町村フィールド（B都道府県別の参加者数について，約95%が新潟県列）の文字列の最後に全角空白が1文字だけ加わっ内である現状では，手動で計数を行っても大きな労ているレコードが存在し，セルb3と不一致として処力ではない．自動的に求めるならばシートSLで，理されていることがわかった．一つ一つのセルの値各中学校の都道府県を入力するフィールドを用意しを調べて手作業で修正するのは煩雑であるため，シて，市町村別の集計と同様の処理を行えばよい．

ートSLに検索・照合用の市町村名フィールド（F列）を追加し，セルf3に次の関数式を入力する：

３．中学校訪問のための分析資料と活用=if(right(b3,1)="",left(b3,len(b3)-1), b3)
本校では毎年秋季に県内の中学校を訪問するPR

すなわち，セルb3の市町村名の右端から1文字目を活動を実施している．しかし，県土が広く中学校数right(b3,1)によって取り出し，それが全角空白かが多いため，全ての中学校を訪問するのは困難であどうかをif関数により場合分けする．真（全角空白）

り，毎年百数十校を選定して訪問活動を行っている．

ならば市町村名の右端1文字分を取り除いた文字列

最大限の効果を狙って訪問先の絞込みを行うために，をleft(b3,len(b3)-1)によって切り出す．偽（全角各中学校の生徒数を調べ，過去数年間の高校説明会空白ではない）ならば市町村名をそのまま取り出す．
4行目以降へオートフィルによって関数式をコピーへの参加（派遣）実績，オープンキャンパス参加者することで，全てのレコードに検索・照合用の市町ータベースを作成する．

数，志願者数，中学校訪問の実績を加えた分析用デ村名が用意できる．
そして，シートMLのセルc3には次の関数式を入３．１

力する：

中学校別生徒数

中学校学校別学級数・生徒数は，県教育委員会がまとめる学校基本調査関連データの一部として公開されているMicrosoft Excel形式のファイルである4)．

=sumif(SL!$F:$F, b3, SL!$E:$E)

図-3のように印刷資料用に加工された一覧表で，市4行目以降へオートフィルによって関数式をコピー

町村立の中学校ばかりでなく，国立大学の附属中学することで，全てのレコードに市町村毎の参加者数校，県立および市立の中高一貫校の各校について，を求めることができる．

公開前年度の学級数や生徒数が詳細にまとめられてシートMLの地区別集計（2，16，31行目）と合計いる．しかし，集計（小計）行や空白行が挿入され（35行目）は，subtotal関数を用いると保守管理がているため，1行を1レコードとするデータベースと容易となる．セルc2には次の関数式を入力する：

して直接扱うことはできない．特に「第一」，「東」などの複数の市町村に存在する中学校名は，単一行=subtotal(9, c3:c15)だけでは識別できない．市町村の掲載順序も新潟市，長岡市，三条市，柏崎市，新発田市，…となっておここで，subtotal関数の第1引数である9は，集計方り規則性が読み取れず，同一市町村内の中学校の掲法として合計値を求める指定である．第2引数は集載順も中学校一覧3)とは必ずしも一致しない．

計範囲を指定する．セルc16，c31も同様に関数式を入力する．そして，合計を求めるセルc35には次の３．２関数式を入力する：

データベース作成

表計算ソフトウェアで新規ファイルを作成し，中学校一覧 3)をシート SL としてコピーする．次に，=subtotal(9, c2:c34)

このファイルに中学校別生徒数 4)をシート名 SN としてコピーする．シート SL に新たに生徒数フィー

すると，subtotal関数によって小計が求められていルド（G 列）を設け，シート SN より検索することるセル（c2，c16，c31）を自動的に省いた合計値がとする．ここで，両シートの間で中学校名の検索・計算される．

照合を行うための一意なキーとなるフィールドが存- 75 -

入学志願者増加を目指す広報活動のためのオープンデータを活用したデータ分析

図-3

中学校学校別学級数・生徒数（抜粋）4)

在しないため，それぞれのシートにおいて検索・照やや煩雑ではあるが，市町村ごとに先頭行のセルへ合用フィールドを新たに作成する必要がある．検手入力または他のシートからコピーを行い，次行以索・照合キーは，市町村名と中学校名を結合した文降へオートフィルによってコピーする．

字列として作成する．

■シート SN における検索・照合キー作成■シート SL における検索・照合キー作成

検索・検索・照合キーを新たなフィールド（X 列）に作成する．

照合キーを新たなフィールド（H 列）に作成する．

前述の作業で市町村フィールド（Y 列）が作成済み

データベースの先頭行（3 行目）のセル h3 に次の関であるので，キーを作成したい先頭行（21 行目）

数式を入力する：のセル x21 に次の関数式を入力する：=if(right(b3,1)="",=if(y21="", "", y21&a21&"中学校")

left(b3,len(b3)-1)&c3, b3&c3)
すなわち，Y 列に市町村名が記入されていないかど
すなわち，市町村名（B 列）の右端から 1 文字目をうかを if 関数により場合分けする．真（無記入）right(b3,1)によって取り出し，それが全角空白かならばキーを生成しない．偽（市町村名あり）ならどうかを if 関数により場合分けする．真（全角空白）ならば市町村名（B 列）の右端 1 文字分を取りば Y 列と A 列を結合し，「中学校」を加えたキーを生成する．最後に 22 行目～310 行目へオートフィル除いた文字列を left(b3,len(b3)-1)によって切りによって関数式をコピーすることで，中学校名が書出して，中学校名（C 列）を結合する．偽（全角空かれた全ての行に検索・照合キーが作成される．

白ではない）ならば市町村名（B 列）と中学校名■シート SL への生徒数フィールド作成（C 列）を単純に結合する．最後に 4 行目以降へオの作業で，シート SL とシート SN の各行（レコーートフィルによって関数式をコピーすることで，全ド）間で一意な対応付けを行うための検索・照合キてのレコードに検索・照合キーが作成される（２．

ーが作成されている．これを利用してシート SN の３参照）．

生徒数（総数）を，シート SL に作成した生徒数フ■シート SN への市町村フィールド作成

各行に市ここまでィールド（G 列）の各セルへコピーしてくればよい．

町村を識別するフィールドが存在しないため，これしかし，２．２で用いた vlookup 関数は，対象範囲を新たなフィールド（Y 列）に作成する．図-3 の A列を調べると，市町村立中学校は 20 行目～310 行目の左端の列をキーとしなければならず，汎用的ではに入力されており，空行と市町村名の後に，その市のオリジナルデータ部分に検索・照合キーのための町村の中学校名が列挙されている．しかし，3 文字列を挿入することも考えられるが，次年度以降のデ以下の市町村名には，見た目の体裁を整えるためにータ更新における作業性が低下する．そこで，デー文字間に空白文字が挿入されている．このため，関タベースの先頭行（2 行目）のセル g3 に次の関数式

数を組み合わせて，この市町村名を自動的に以降のを入力する：

ない．vlookup 関数の仕様に合わせて，シート SN行へ入力することは非効率的と考えられる．そこで，- 76 -

高橋 章・上村 健二・佐藤 拓史・佐藤 秀一=index(SN!$T:$X, match(h3, SN!$X:$X, 0), 1)

表-1

OC 参加者と志願者数（平成 27 年度）

区分
全体
春季訪問先

ここで index 関数は第 1 引数で指定されるセル範囲から，第 2 引数で指定される行番号と，第 3 引数で指定される列番号のセルの値を取り出す．すなわち，
シート SN で検索・照合キーが入力されているセル
範囲 SN!$X:$X から，h3 セルと一致する行番号をmatch 関数で検索する．そして，セル範囲 SN!$T:$Xの 1 列目となる生徒数（総数）の値を取得する．
３．３

OC 参加者
416
35

志願者
295
26

志願率71%
74%

ープンキャンパスへの参加があった．さらにその60%となる12校から，入学試験への出願があった．
これまで個別に集計し，記録として残すだけであったデータ群から，新たな付加価値を創造するデータマイニングが実現できたといえよう．

春季中学校訪問の実施

前節のデータベースに過去数年間の高校説明会への参加（派遣）状況，OC への参加人数，入試における志願者数，中学校訪問の実績を加え，各中学校と本校との関係性を調べた．すると，平成 25～26
年度の過去 2 年間志願者がゼロの県内市町村立中学校が 85 校あり，うち 31 校は全校生徒数が 180 名以上と比較的規模の大きな中学校であることがわかった．そこで，12 市町村から比較的生徒数の多い 20校を選定し，平成 27 年の 6 月～7 月にかけて，校長と副校長による春季中学校訪問を実施した．
その結果，春季訪問先の 75%となる 15 校から 35名が OC に参加した．そして，春季訪問先の 60%となる 12 校から 26 名の志願者があった．OC 参加者に対する志願者数の割合を志願率として，全体と比較すると表-1 となる．これまで本校への志願者のいなかった中学校へ，効果的に PR 活動を行うことができたことが推察される．

データ処理を定型処理化することは，労力の軽減やミスの防止という大きな意義を持つと考えられるが，ややもすると作業内容がブラックボックス化し，保守管理作業が形骸化することにつながりかねない．
より戦略的かつ効率的にPR活動を推進するための指針となる科学的なデータ分析を目指し，PDCAを意識してデータベースやデータ処理方法の改善活動を継続する必要がある．
謝辞：中学校向け広報活動に実際に携わっていただ
いた渡邉校長をはじめ広報戦略室メンバーおよび学生課・入試係の皆さま，オープンキャンパスや中学校訪問にご協力いただいた教職員の皆さまに感謝します．また，総合情報処理センタースタッフの皆さ
まにはデータ処理について多くのアドバイスをいただきました．
参考文献・URL
1) イアン・エアーズ: その数学が戦略を決める，文藝春秋，
2010.

４．まとめ

2) 新潟県：統計ボックス・オープンデータ，
http://www.pref.niigata.lg.jp/tokei_od.html

長岡高専広報戦略室における入学志願者増加を目

3) 新潟県：市町村教育委員会・学校一覧，

指す活動を推進するために，表計算ソフトウェアのhttp://www.pref.niigata.lg.jp/gimukyoiku/

関数を利用したデータベース管理機能を活用する取り組みを実施した．新潟県がWebで公開しているオ

1196007390231.html
4) 新潟県：平成 27 年度学校基本調査関連データ，中学校ープンデータのうち，公立中学校の一覧表を利用す

学校別学級数・生徒数，ることで，オープンキャンパスの受付簿作成の労力

http://www.pref.niigata.lg.jp/kyoikusomu/を一部軽減し，オンタイムでの集計を実現できた．

1356826766000.html

公立中学校の一覧表に，在籍生徒数のオープンデ5) 高橋慈子: Excel2013 全機能 Bible，技術評論社，2013.

ータを組み合わせ，さらに長岡高専広報戦略室における中学校向けPR活動によって派生した活動実績のデータを加えることで，データベースを作成した．
このデータベースをもとに分析を行い，過去2年間連続でオープンキャンパスへの参加者がなく，入学志願者もいなかった中学校を20校抽出し，春季に訪問活動を行ったところ，その75%となる15校からオ- 77 -(2016. 10. 3 受付)