神エクセル問題が世に知れて、大分経ちます。世間には膨大な神エクセル帳票が、在ると思われます。今回は内輪で使う、神エクセル申請書を想定し、特に批判の多い、１マス１文字入力のエクセル方眼紙入力欄の改修を中心に、データ入力の効率化を行い、並行して不便さの指摘が多い、データ再利用性の向上も図りました。※改修作業は、Excel2016上にて行っています。
神エクセルについて
神エクセルとは、自由に罫線を引けるよう、予めセルが方形状に設定された（いわゆるエクセル方眼紙）テンプレートを使用し、複雑な罫線を多用し、情報集約に偏向した、帳票全般を指す用語の様です。神エクセル帳票は、データの配置や印刷など、見栄え重視です。なので、データ抽出や集計など、帳票内データの再利用性が著しく低い事が問題点として、あちこちで指摘されています。
神エクセル対応の現状ついて
話題になった当時から神エクセルは、そのデータの再利用性の低さから、入力したデータの使いまわしができない等、あちこちのサイトで酷評されてた様です。もちろんそれらサイトでは、代替案や改善案も示されておりました。だいたいは、神エクセルやめてWeb入力フォームなど、データ登録アプリ経由で、必要な情報を集める形式への移行を提案してたみたいです。
そのせいか、最近の官公庁サイトを見回ると、神エクセルは「（各種）申請書ダウンロード」サイトにPDF化して、相変わらず生存確認できる傍ら、一部申請（施設予約等）が、Webアプリ化されていて、2本立てになってる事が多いようです（図1）。電子申請への移行期なんでしょうか？
例として安城市（図１赤枠部分）のサイトでも、申請書ダウンロードと施設予約・電子申請の２本立てで、申請書ダウンロードボタンをクリックで、PDF化各種申請書のダウンロードサイトへ、施設予約・電子申請ボタンクリックで、各種電子申請サイトへの外部リンクまとめサイトへ飛びます。
神エクセル申請書の準備と改修方法
神エクセルへの風当たりは相変わらず強いので、特定官公庁の申請書を修正対象に挙げるのは気が引けます。なので、様々な官公庁サイトから集めた、各種申請書から工夫のあるパーツを寄せ集めて、それっぽい神エクセル申請書を準備することにしました。
改修する申請書の用途と背景の想定
工夫のあるパーツを集めて作ると言っても、神エクセル！素のままでは、修正箇所多数になってしまいます。また、広く一般に使われる申請書の類は、考慮すべき箇所が多数あり、改修もきつくなります。なので、修正元にする神エクセル申請書の、用途や背景の想定を以下の様に絞りました。
- 官公庁の勤務者内で使われる、内輪の申請書を想定
- メールと窓口申請が混在していて、メール申請に比重が移りつつある状況
- ワークフロー等を通して、上長の承認を得る、稟議的な習慣が残る自治体で使用
- 毎週受付ける申請数は10件程度
- 申請から受理、結果通知まで、一週間程度の期間を設けている
以上を踏まえて、市庁舎内で使う情報基盤システムを利用する為、ユーザIDの登録を申請する用途を想定しました。具体的には、架空のユーザID登録申請書として体裁を整えることにしました。
申請書の改修方法と帳票全体のブラッシュアップ
以上の経緯から、改修元の神エクセル申請書として、図2の様なユーザID登録申請書を用意しました。図2#1が、未入力時の申請書フォームで、薄オレンジ色のセルが入力欄です。修正箇所が多いと論点がぼやけるので、図2#2で黄色く強調表示した、入力欄の使い勝手の向上と、各入力欄のデータ再利用性を考慮し、今回の神エクセル申請書の改修作業を行いました。
残りの項目については、大塚商会ホームページのコラム、役立つ！総務マガジンの特集：Excel申請書マスター、第31～34回の連載が良かったので、これら記事を参考に、帳票全体をブラッシュアップしました。詳細について、以下のボックスにざっくりまとめました。
- エクセルの[セルのロック]＆[シートの保護]機能を使い、入力欄以外のセルをロック
- 入力欄のセルを、薄オレンジに着色して入力域を明確にする
- [データの入力規則]を活用して、入力値に制限を設ける
- [データの入力規則]の入力値エラー時、理由を説明するエラーメッセージを設定
- 選択式で済む項目の場合、ドロップダウンリストを活用して、入力作業を省力化
- フォントで入っていたラジオボタン、チェックボックスは、VBコントロールに置換
- ラジオボタン、チェックボックスの選択で、状態変化すべき所は、連動して表示が切替わる様にした
ここまでの紆余曲折を経て、以下のような改修元のユーザID登録申請書を準備しました。
情報基盤システムユーザーID登録申請・元.xlsx (924 ダウンロード )神エクセル申請書の改修と活用
改修元のユーザID登録申請書（神エクセル申請書）が準備出来たので、前述の黄色く強調表示したピックアップ箇所と、入力欄のデータ再利用性を中心に、以下の改修方針で利便性の向上を図りました。
- １マス１文字入力形式のエクセル方眼紙入力欄の改修
- 連携入力が必要な為、放置された手書き入力欄をリスト選択式入力欄へ改修する
- 各入力欄のデータ再利用性を考慮した改修
- フォーマット変更が煩わしい結合セルには手を付けない
１マス１文字入力形式入力欄の改修
申請書の希望ユーザIDの入力欄は、エクセル方眼紙のようなマス目に、1文字ずつ入力する形式です。このような入力欄は、データを活用できない、典型的な神エクセルの代表例です。あちこちのサイトで取り上げられる事が多い為、改修項目としてピックアップし、表1に改修方法をまとめました。
改修方法
| 項目 | 改修前 | 改修後 |
|---|---|---|
| ユーザID入力欄 |  |  |
| セル結合 | １マス１文字入力 | 入力欄の全セルを結合 |
| データの入力規則※1 | 特に設定なし。ユーザIDの作成条件は以下の通り ・半角英小文字と数字を使用し、3文字以上8文字 以内 ・記号は、ハイフン、アンダーバーのみ使用可能 | 数式欄に判定式※2を入力して入力規則外のデータ入力を弾く。判定式の仕様は左記の通り |
| メールアドレス表示欄 | 入力された「ユーザID」を表示し、ユーザID未入力の場合「希望ユーザID」と表示します | 入力された「ユーザID」を表示し、ユーザID未入力の場合「希望ユーザID」と表示します |
| 表示欄の数式 | =IF(D13=””,”希望ユーザID”,D13&F13&H13&J13&L13&N13&P13&R13) | =IF(D13=””,”希望ユーザID”,D13) |
- 希望ユーザID入力欄を選択
- メニュー[データ]→[データの入力規則]を選択
- データの入力規則ポップアップ画面表示（図3）→ [設定]タブを選択
- 数式欄に判定式を入力し、OKボタン押下
※2補足説明：表1-データの入力規則の条件を実装の為、作成した判定式は以下の通り
=IF(LEN(D13)<=8,IF(LEN(D13)>=3,(ISNUMBER(SUMPRODUCT(SEARCH(MID(D13,ROW(INDIRECT(“1:”&LEN(D13))),1),”0123456789abcdefghijklmnopqrstuvwxyz-_”)))),FALSE),FALSE)
改修後の動作確認
改修後の希望するユーザID入力欄と、発行予定メールアドレス表示欄の動作を、表2にまとめました。
| No | 動作説明 | 画面表示 |
|---|---|---|
| ① | 希望するユーザID入力欄へ異常値を入力してフォーカス移動 |  |
| ② | 入力値異常のポップアップが表示される |  |
| ③ | 希望するユーザ入力欄にフォーカスが戻る |  |
| ④ | 希望するユーザID入力欄未入力時 |  |
| ⑤ | 発行予定メールアドレス表示欄に、 “希望ユーザID@city.kisaragi.lg.jp”と表示 |  |
| ⑥ | 希望するユーザID入力欄へ希望ユーザIDを入力 |  |
| ⑦ | 発行予定メールアドレス表示欄に、 “abcd1234@city.kisaragi.lg.jp”と表示 |  |
連動ドロップダウンリストによる入力欄の改修
各自治体の申請書からパーツを集める上で、月日入力や県名入力などを単体のリスト入力で、入力ミス防止と効率化を図る工夫はよく見かけました。しかし、互いに連携する項目の入力効率化を図ったものは見られませんでした。なので、今回は所属部課の入力欄を、改修項目としてピックアップしました。
改修方法
今回の改修は、Office Hackというサイトの記事「エクセルのプルダウンメニュー（ドロップダウンリスト）を連動する」を参考に、連動ドロップダウンリストを使用して、互いに連携する所属部課項目の入力効率化を図りました。まず、新規シート[ドロップダウン一覧]を追加し、連動ドロップダウンリストの参照元になる所属部課マトリクス（部課構成は架空のものです）を作成しました（図4）。
後は、[新規ID登録申請書]シートに戻り、 所属（部）、所属（課）入力欄から、図4の所属部課マトリクスを、連動して参照するように設定します。設定方法の詳細について、表3にまとめました。
| No | 設定方法 | 画面表示 |
|---|---|---|
| ① | 連携の親となる所属部のプルダウンリスト選択により、その部に属する課を連携表示するプルダウンリストを設定する。 1.図4：所属部課マトリクスの所属部範囲 （B3～H3）を選択 2.[数式]タブ→[名前の定義]を選択する 3.「新しい名前」ダイアログボックスが表示 4.名前欄に「所属部」と入力 5.「OK」ボタンを押下 |  |
| ② | シートに戻り所属部範囲（B3～H3）に名前が設定されたことを確認 |  |
| ③ | 図4：所属部課マトリクス全体（B3～H9）を選択する |  |
| ④ | 1.[数式]タブ→[選択範囲から作成]を選択する 2.「選択範囲から名前を作成」ダイアログボックス が表示される 3.[上端行]にチェックを入れる 4.【OK】ボタンを押下 |  |
| ⑤ | [新規ID登録申請書]シートに戻り、連動ドロップダウンリストの親側になる所属（部）入力欄を選択 |  |
| ⑥ | 1.[データ]タブ→[データの入力規則]を選択する 2.「データの入力規則」ダイアログボックス が表示される 3. [設定]タブを選択する 4.「入力値の種類」で[リスト]を選択 5.「元の値」の参照範囲に[=所属部]と入力 ※②で所属部と命名した範囲 6.【OK】ボタンを押下 |  |
| ⑦ | 連動ドロップダウンリストの子側になる所属（課）入力欄を選択 |  |
| ⑧ | 1.[データ]タブ→[データの入力規則]を選択する 2.「データの入力規則」ダイアログボックス が表示される 3. [設定]タブを選択する 4.「入力値の種類」で[リスト]を選択 5.「元の値」の参照範囲に[=INDIRECT($H$6)]と 入力 ※$H$6は親リスト所属（部）入力欄の座標 ※INDIRECT関数は「INDIRECT(参照文字列)」の 書式で構成され、指定される文字列への参照を返す →①～⑧を踏まえて親子連動するドロップダウン リストにより図4：所属部課マトリクスの共有 参照が実現する 6.【OK】ボタンを押下 |  |
改修後の動作確認
連動ドロップダウンリストに改修後の所属（部）、所属（課）入力欄の動作を、表4にまとめました。
| No | 動作説明 | 画面表示 |
|---|---|---|
| ① | 1.[新規ID登録申請書]シートを選択 2.連動ドロップダウンリスト親側の、 所属（部）入力欄を選択 3.親ドロップダウンリストを展開する 4.総務企画部を選択する |  |
| ② | 1.連動ドロップダウンリスト子側の、 所属（課）入力欄を選択 2.子ドロップダウンリストを展開する |  |
| ③ | 右図、所属部課マトリックスで見ると、 ①-3.親ドロップダウンリストは、 マトリックスヘッダの赤枠部分から、 リストが作成されている事が分かる。 ③-2.子ドロップダウンリストは、 右図、所属部課マトリックス青枠の 課名リストであり、①-4.で親ドロ ップダウンから選んだ、総務企画部 に連携して所属課が表示されている ことが分かる |  |
改修後の微調整
以上の通り、連動ドロップダウンリストは便利な機能ですが、使い勝手と見栄えに難のある部分がある為、表5で問題点を確認の上、さらに微調整をして、もう少しだけ使いやすくしました。
| No | 設定方法 | 画面表示 |
|---|---|---|
| ① | 1.[新規ID登録申請書]シートを選択 2.連動ドロップダウンリスト親側の、 所属（部）入力欄を選択 3.親ドロップダウンリストを展開する 4.市民文化部を選択する |  |
| ② | 1.連動ドロップダウンリスト子側の、 所属（課）入力欄を選択 2.子ドロップダウンリストを展開する 3.リスト中の空行を選択する |  |
| ③ | 所属（課）入力欄に空行が設定されて しまう事が確認できる |  |
| ④ | 右図、所属部課マトリックスで見ると、 ①-3.親ドロップダウンリストは、 マトリックスヘッダの赤枠部分から、 市民文化部している事が分かる。 ③-2.子ドロップダウンリストは、 右図、所属部課マトリックス青枠の 市民文化部の課名リストになるので、 オレンジで強調した空行部分も、読込ま れてしまっている事が確認できる |  |
| ⑤ | 申請書のドロップダウンリストとして、 不要な空行部分を選択、右クリック メニュー→[削除]から削除画面を 表示し、削除する |  |
| ⑥ | 右図、所属部課マトリックス青枠の 市民文化部の課名リストが必要行のみ になった事を確認 |  |
| ⑦ | 1.[新規ID登録申請書]シートに戻る 2.子ドロップダウンリストの所属（課） 入力欄を選択 3.子ドロップダウンリストを展開し、 必要行のみ表示される事を確認 4.リストから任意の課を選択する |  |
| ⑧ | 所属（課）入力欄 に選択した課が 表示されることを確認 |  |
以上を踏まえて、所属部課マトリクス（図5）から不要な空行を全て削除する
各入力欄のデータ再利用性を考慮した改修
以上、難儀な神エクセル申請書の、使えない入力欄や無駄な手入力の解消と、その他瑣末な改修を行い、図6の体裁が整いました。帳票の型が決まったので、肝心のデータが使えない状態を改修します。
その関連の文献を見ると、各入力欄のデータを、ロジック的に連結して抽出する等、データを一行に固めて引き抜く手法がよく取られます。この事は、最終的に帳票データを、1レコードにまとめてDB登録するので、理に適っていると思います。
改修方法
今回は昔、お世話になった会社のやり方に倣って、帳票の欄外に、申請書入力欄の値をセル参照させた申請書データ抽出欄を設け（図7）登録先のDBレイアウトに沿って並べる改修を行いました。
表6は、申請書データ抽出欄（図7）の各カラム名と、セル参照するユーザID登録申請書の各入力欄（図8）の対応表です。
| № | 図7上段抽出欄 | 図8#1入力欄 | № | 図7下段抽出欄 | 図8#1入力欄 |
| 1 | 誓約 | 情報セキュリティ誓約 ※チェックボックス | 11 | email_add | 発行予定メールアドレス |
| 2 | user_id | 希望するユーザID | 12 | access_lv | 利用資格 ※ラジオボタン |
| 3 | uname_kj | 漢字氏名 | 13 | option_jyuki | 追加利用システム-住基 ※チェックボックス |
| 4 | uname_kana | フリガナ | 14 | option_dev | 追加利用システム-開発 ※チェックボックス |
| 5 | eｍp_num | 職員番号 | 15 | remarks | 備考 |
| 6 | department | 所属（部） | № | 図7下段抽出欄 | 図8#2入力欄 |
| 7 | division | 所属（課） | 16 | application_num | 申請管理番号 |
| 8 | unit | 所属（係その他） | セル参照による値取得は | よく使われる手段の為、特に詳細解説なし | |
| 9 | extension_num | 連絡先（内線） | 参照例：№2 user_id | 図7user_id抽出欄記入式「=D13」 | |
| 10 | birthday | 生年月日（西暦） | №3～11、15～16参照 | 図7抽出欄に、同様な記入式を設定 |
※但し、チェックボックス・ラジオボタンは、選択値を直接データ抽出欄に出力する
図8は、改修後のユーザID登録申請書の全体キャプチャです。各入力欄の位置が分かりやすいように、シートの外枠も含めてキャプチャしています。
改修後のデータ処理
改修後は、申請書データ抽出欄のデータを、使い易くするため表7のようなデータ処理を行いました。
| № | 説明 | 処理方法 |
|---|---|---|
| 1 | 抽出データの取得について：VBAの範囲指定 行コピーで取得することにしました。図5、 図7より、左記のような記述で実装しました。 | 記述例：図7抽出欄を以下、赤色の範囲指定で取得する Sheets(“新規ID登録申請書”).Range(“AL31:BA31“).Select ※抽出欄を１行に収める限り、帳票の入力欄がどの様に増減しても、 赤色範囲を変更するだけで、帳票入力欄の全値を取得できます。 |
| 2 | 表6-№1：ユーザID登録申請の前提条件 が、[情報セキュリティに関する誓約]なの で、誓約チェックボックスのON/OFFを、 図7誓約抽出欄にTrue/False出力する。 ※[情報セキュリティに関する誓約]への 同意有無は前提条件として取得する。 誓約抽出欄の取得値がFalseの場合、 以降の処理は停止される。 | ①誓約チェックボックスを右クリックし、メニューから[コントロール の書式設定]をクリック→「コントロールの書式設定」を表示  ②図7誓約抽出欄の座標（AL31）を指定してOKボタン押下 |
| 3 | 2と同様に表6-№13、14チェックボックスの ON/OFFを、図7抽出欄にTrue/False出力する。 | 2と同様に以下のように座標指定 ・図7option_jyuki抽出欄の座標（AX31） ・図7option_dev抽出欄の座標（AY31） |
| 4 | 表6-№12：access_lv抽出欄は、利用資格 ラジオボタンの選択値を、図7access_lv 抽出欄に出力する | ①利用資格ラジオボタンを右クリックし、メニューから[コント ロールの書式設定]をクリック→「コントロールの書式設定」を表示  ②図7access_lv抽出欄の座標（AW31）を指定してOKボタン押下 |
| 5 | 表6-№12：access_lv抽出欄（利用資格 ラジオボタンの選択値）の値と利用資格 ラジオボタン選択の対応は右記の通り ※その他の手入力欄の扱いについて、特に 参考例が無かった為、取って付けた仕様に なっています | 1：常勤職員 2：非常勤職員 3：臨時職員 4：協力会社 5：その他 ※図7access_lv抽出欄 ＝5の場合、帳票入力欄（AH18）の 値を取得する |
| 6 | 表6-№6、№7：所属（部）,（課）連動ドロ ップダウンリストの操作により、あり得ない 組合せが入力された場合のエラー判定を、 帳票欄外のセルに実装し、データ取得時に そのセルのエラー判定値を参照する ※所属部→課と連動ドロップダウンリストで 入力した後、所属部の選択を変えると、 部→課の親子関係が崩れる | ①帳票欄外のセルAM16に以下の判定式を設定し、判定セルとする =IFERROR(IF(COUNTIF(INDIRECT(H16),S16),S16,”ERR”),”ERR”) ※H16、S16は、所属（部）,（課）入力欄の座標（図8#1） ※ALは部課選択状態を見やすくする為の所属（部）入力値参照(=H16) ②所属部-課入力が、順当な組合せの場合、判定セル(AM16)に 所属課名が表示される 例：経済企画部-情報管理課→図5に照らして順当な組合せ  ③所属部-課入力が、あり得ない組合せの場合、判定セル(AM16)に [ERR]と表示される 例：市民文化部-情報管理課→図5に照らしてあり得ない組合せ  ④図7division抽出欄取得時、判定セル(AM16)を参照する |
| 7 | 表6-№10：生年月日入力欄は、年月日をバラ バラに入力するので、帳票欄外のセルに、一旦、 年月日[yyyy/m/d]書式で出力する 表6-№10：birthday抽出欄では、上記セルの、 年月日の出力をセル参照して取得する | ①帳票欄外のセルAL17に以下の連結式を設定する =W17 & “/” & AB17 & “/” & AG17  ②図7birthday抽出欄では、セルAL17の出力値をセル参照 (=AL17) し、生年月日を抽出する |
| 8 | 表6-№10：生年月日入力欄は、データの 入力規則やドロップダウン入力で、ある 程度、入力制限を設けているが、不正値の 入力は避けられない。 表6-№10：生年月日入力欄に、不正値が 入力された場合のエラー判定を、帳票欄外の セルに実装し、データ取得時に、そのセルの エラー判定値を参照する | ①帳票欄外のセルAM17に以下の判定式を設定し、判定セルとする =IFERROR(WEEKNUM(AL17),”ERR”) ※AL17は、生年月日入力欄の[yyyy/m/d]書式出力値（表7-7） ※WEEKNUMは、指定日付が当年の第何週目に当るか返す関数 ワークシート関数には、IsDate関数の様に、日付有効性を判定する 関数が無い為、IFERRORと組合わせて、日付判定に使います ②生年月日入力欄が、正常日付の場合、判定セル(AM17)に 生年月日が当年の第何週目になるか表示される  ③生年月日入力欄が、無効な日付の場合、判定セル(AM17)に [ERR]と表示される  ④図7birthday抽出欄取得時、判定セル(AM17)を参照する |
改修神エクセル：ユーザID登録申請書
此処迄の経緯を踏まえて神エクセルを改修し、以下の様なユーザID登録申請書に仕上げました。
情報基盤システムユーザーID登録申請・改.xlsx (906 ダウンロード )まとめ
世間には、膨大な神エクセル帳票が在ると思われます。今回は、ごく内輪で使う申請書を想定して、特に批判される、１マス１文字入力のエクセル方眼紙入力欄を改修しました。また、連動ドロップダウンリストを使って、手書き入力欄をリスト選択式に改修しました。これらは、沢山ある文献に頼って、定石通りに改修するだけで、確実に効率化を図れました。
また、神エクセル帳票に関して、結合セルの弊害についての批判もよく見かけます。今回は帳票入力欄のデータ再利用性を向上の為、帳票欄外にデータ抽出欄を設ける過程で、結合セルの先頭座標を、セル参照先に指定する事で、支障なく入力値を収集できた（図7，8）ので、特に対応しませんでした。
今回のような改修は、あくまで内輪の狭い範囲で使う、現役の神エクセル帳票に限って有効な、小さくまとめて回す改善方法だと思われます。帳票に記入するユーザが、業務効率化の一環なのだろうと忖度し、欄外のデータ抽出欄などを弄らずに使ってくれて、改修後の帳票に記入されたデータの収集を、考えていれば良い時のみ、通用するのだと思います。
例えば、不特定多数で頻繁に使う帳票を、帳票様入力フォームのWebアプリ運用を考えるのが普通だと思います。また、過去に提出された帳票の山から、必要データの抽出を求められたりした場合、データ抽出バッチ等を、かっちり作り込まないと、なかなか要求を満たせないと思われます。
関連記事
参考文献
- 奥村晴彦(2013年7月12日) 三重大学(Mie University) “「ネ申Excel」問題 (PDF)” 2021年8月29日閲覧
- 井ノ上 陽一「EX-IT」”ネ申エクセルの問題点。ネット申込の最適解はフォーム。” 2021年8月29日閲覧
- 渋屋 隆一(2015–)「中小企業のデータ経営」”神エクセル（ネ申エクセル）の弊害とその解決法” 2021年8月29日閲覧
- “安城市ホームページ” 2021年8月29日閲覧
- 株式会社大塚商会「役立つ！総務マガジン」”特集：Excel申請書マスター” 第31～34回 2021年8月29日閲覧
- 株式会社Kailua(2014-)「Office Hack」 “エクセルのプルダウンメニュー（ドロップダウンリスト）を連動する” 2021年8月29日閲覧
- 「エクセリューション」”エクセル方眼紙から文字列を一発で連結抽出する関数 #神エクセル” 2021年8月29日閲覧
- 株式会社インプレス「窓の杜:いまさら聞けないExcelの使い方講座」”【Excel】ドロップダウンリストの選択肢が多すぎ！エクセルで2段階のドロップダウンリストで表示項目を絞り込むテクニック” 2021年8月29日閲覧
- 株式会社技術評論社(2007-)「新刊ピックアップ」”「神Excel」問題を解決するには” 2021年8月29日閲覧
- ExtendOffice.com(2009-) “Excelに特殊文字が入力されないようにするにはどうすればよいですか？” 2021年8月29日閲覧
- Tipsfound「Excel VBA」”VBA 行をコピーする” 2021年8月29日閲覧
コメント