エクセル方眼紙（列の幅を細かくそろえ、セルを結合して枠を作る作り方）は、印刷したときの見た目は整います。その代わりに、並べ替え・集計・別の表への転記を、手作業でしかできない表になります。Microsoft のサポートにも、Excel は結合されたセルを含む列のデータを並べ替えない、と書かれています。
直し方の基本は、入力する表と、印刷して見せる様式を分けることです。入力は「1 行に 1 件、1 セルに 1 データ、結合なし」の一覧表で行い、印刷用の様式はその一覧表から値を引いてくるだけにします。以下では、総務省が統計表のために定めた「機械判読可能なデータ」のルールを物差しにして、自分の表を点検する表と、既存の方眼紙を直す手順を整理します。
方眼紙が集計を止める理由
Excel が扱っているのは、見た目ではなく、セルという升目に何が入っているかです。人は罫線や結合を見て「この 3 行は同じ取引先の話だ」と読み取れますが、Excel には、結合されたセルの左上にだけ値があり、残りは空、としか見えていません。
Microsoft のサポートによると、セルを結合すると、表示されるのは左上のセルの内容だけで、ほかのセルの内容は削除されます。つまり結合は「見た目をまとめる」操作ではなく、データを 1 つに減らす操作です。
総務省の「統計表における機械判読可能なデータ作成に関する表記方法」（2020 年 12 月 18 日、統計企画会議申合せ）は、セルを結合した場合に起きることとして、並べ替えができない（エラーになる）、グラフにできない、範囲を選択しにくい、コピーして貼り付けられない、を挙げています。
ピボットテーブルも同じ前提で動きます。Microsoft のサポートは、ピボットテーブルの元データの条件として、データを列で整理すること、空の行や列がないこと、すべての列に空白でない見出しが 1 行であること、1 つの列に日付とテキストを混在させないこと、を挙げています。方眼紙の様式は、このどれにも引っかかりやすい作りです。
その結果、月末の集計のたびに、様式から数字を拾って別の表に打ち直す作業が生まれます。表を作った人の手間ではなく、それを使う全員の手間として、毎月繰り返されます。
自分の表を点検するチェックリスト
総務省のルールのうち、社内の一覧表にもそのまま当てはまる項目を抜き出しました。1 つでも当てはまれば、その表は自動の集計や転記に向いていません。
| 点検すること | 当てはまる例 | 直す形 |
|---|---|---|
| 1 セルに 1 データか | 「4月 120／5月 135」を 1 セルに入れている | 月ごとに列か行を分ける |
| 数値に文字が混ざっていないか | 「1,200円」「▲300」「約50」と打っている | 数値だけを入れ、単位は見出しか別セルに書く |
| セルを結合していないか | 取引先名のセルを縦に 3 行結合している | 結合を解除し、3 行とも取引先名を入れる |
| スペースや改行で見た目を整えていないか | 字下げのために先頭に空白を入れている | 空白と改行を消し、階層は別の列で表す |
| 同じ項目名を省略していないか | 2 行目以降の取引先名を空欄にしている | すべての行に入れる |
| 単位を書いているか | 「金額」だけで円か千円か分からない | 「金額（千円）」のように書く |
| 空白の行や列で表が分断されていないか | 見やすさのために 1 行おきに空行を入れている | 空行と空列を消す |
| 1 シートに表が 1 つか | 同じシートに集計表と明細表が並んでいる | 1 つの表を 1 シートに分ける |
数値に文字が混ざる問題について、総務省のルールは、「円」や「▲」やカンマを文字として入れると Excel では文字列として扱われ、関数で計算できない（エラーになる）ほか、並べ替えも正確にできない場合がある、と説明しています。見た目の「円」や桁区切りは、セルの書式設定で表示だけ付ければ、値は数値のまま残せます。
既存の方眼紙を直す手順
一度に全部を作り直す必要はありません。次の順に進めると、元の様式を壊さずに移せます。
1. 元のファイルを複製する
作業はコピーで行います。元の様式を参照している数式やほかのファイルがあると、直した瞬間に崩れるためです。
2. 結合セルを探す
Ctrl＋F で「検索と置換」を開き、「オプション」→「書式」→「配置」タブで「セルを結合する」にチェックを入れて OK、「すべて検索」を押すと、結合されたセルが一覧で出ます（Microsoft のサポート「結合セルを検索する」の手順）。
3. 結合を解除し、空いたセルを埋める
結合セルを選び、「ホーム」タブの「セルを結合して中央揃え」の横の矢印から「セル結合の解除」を選びます。値は左上のセルに残り、ほかは空欄になります。
空欄を上の値で埋めるには、範囲を選んで Ctrl＋G（「ジャンプ」）→「セル選択」→「空白セル」で空欄だけを選び、= と入力して上のセルをクリックし、Ctrl＋Enter を押します。Ctrl＋Enter は、選んだすべてのセルに同じ入力をするキーです。埋めたあとは、列をコピーして「値」で貼り付け、数式を値に直しておきます。
4. 1 行 1 件の一覧表に組み直す
1 行目に見出しを 1 行だけ置き、2 行目から 1 件ずつ並べます。見出しを 2 段にしたり、途中に小計の行を挟んだりはしません。小計はあとでピボットテーブルや関数で出せます。
5. テーブルにする
一覧表のどこかを選んで Ctrl＋T を押し、「先頭行をテーブルの見出しとして使用する」を確かめて OK を押します。Microsoft のサポートによると、テーブルにすると、行を足したときに範囲が自動で広がり、すべての列でフィルターと並べ替えが使えるようになります。ピボットテーブルの元にしておけば、テーブルに足した行は自動でピボットテーブルに含まれます。
6. 印刷用の様式は別シートにする
提出や回覧のための様式は、別のシートに残して構いません。ただし、様式のセルには直接打ち込まず、一覧表から VLOOKUP などの関数で値を引いてくる形にします。入力は常に一覧表の 1 か所で済み、様式は何枚でも同じ中身で作れます。
見出しを複数の列の中央に置きたいときは、結合の代わりに、Ctrl＋1 の「セルの書式設定」→「配置」タブ→「横位置」で「選択範囲内で中央」を選びます。見た目は結合と同じで、セルは分かれたままです。
用紙の幅に収めたいときは、「ページ レイアウト」タブの「ページ設定」から、横を 1 ページに合わせ、縦は空欄にします（Microsoft のサポート「Excel で 1 ページに合わせる」）。列の幅を 1 本ずつ詰めて収める必要はありません。
様式が「方眼紙」で決まっているとき
取引先や役所、社内の規程で様式が決まっていて、自分では変えられないこともあります。ここは、個人でできることと、組織の問題を分けて考えます。
個人でできること。 入力は自分用の一覧表で行い、決められた様式へは関数で流し込みます。様式の側は「印刷するための紙」と割り切り、データの置き場にしません。これだけでも、転記の打ち間違いと二重入力はなくなります。
組織で決めること。 社内の様式なら、決めた部署に「入力用の一覧表を別に配ってほしい」と提案できます。根拠には、国の統計表でさえ、印刷用の表とは別に元のデータを機械で読める形で出すことにしている、という総務省のルールが使えます。提案するときは「見づらい」ではなく、「毎月の集計に何人が何時間かけているか」を数字で示すと通りやすくなります。数字での伝え方はあいまいな言葉を数字に置き換えるルールを参照してください。
直すときの落とし穴
結合を解除すると値が消えたように見える。 解除後は左上のセルにしか値がありません。手順 3 の空欄埋めを飛ばすと、並べ替えた瞬間に行と取引先名の対応が崩れます。並べ替えは、空欄を埋めてからにします。
ほかの人の数式やマクロが元の位置を見ている。 様式のセルの位置を前提にした数式やマクロは、行や列を動かすと別のセルを読みにいきます。直す前に、そのファイルを使っている人に一声かけ、しばらくは旧い様式も残して並行で使います。
数式の結果を値として渡してしまう。 総務省のルールは、並べ替えなどで数式の値が正しく表示されなくなる場合があるとして、公表する表は値にするよう求めています。社内で使う一覧表は数式のままでよいのですが、外に渡すときは値に直してから渡します。
見た目を整える目的を忘れない。 方眼紙は、紙で配って読んでもらうという目的には合っていました。問題は、その様式をデータの入れ物にも兼ねさせたことです。見やすさを捨てるのではなく、役割を分けます。何のための表かを先に決める考え方は目的と手段を分けて考えるで扱っています。
業務効率・IT のほかの記事はこちらの一覧にあります。
まとめ
- 方眼紙の問題は見た目ではなく、結合セルや空行が並べ替え・集計・転記を止めること
- 入力は「1 行 1 件・1 セル 1 データ・結合なし」の一覧表、印刷は別シートの様式に分ける
- 結合セルは Ctrl＋F の書式検索で探し、解除したら Ctrl＋G と Ctrl＋Enter で空欄を埋める
- 一覧表は Ctrl＋T でテーブルにし、見出しの中央寄せは「選択範囲内で中央」で行う
- 様式を変えられないときは、一覧表から関数で流し込む
