CONCEPT — EXCEL TO DATABASE
Excelをデータベース化する手順:崩れた表の直し方と限界の目安
この記事は、見た目を整えるために作られたExcelの表を、1行に1件の記録が並び、IDの列で別の表とつながる形に直す手順をまとめたものです。結合したセル、1つのセルに2つの商品、途中に入った小計の行は、人が読むには便利でも、集計や取り込みのたびに手作業を生みます。データベースの形の3つの決まり、直す前に見つける崩れ、直す10の手順、直した表をExcelの中で使う機能、Excelのまま使える限界の目安を、図と説明用の仮想例で説明します。
Excelのデータベース化で直すもの:見せるための表を、記録を貯める表に分ける
Excelの表には、会議で配るために見出しを結合し小計を入れた見せるための表と、1行目に項目名を置き2行目から1行に1件ずつ書く記録を貯めるための表があります。Excelのデータベース化では、記録を貯める表を作り、見せるための表はその記録からピボットやグラフで作り直します。
データベース化という言葉の意味や、データベースとExcelの表の違いは、別の記事で説明しています。この記事では、Excelの中で表を直す手順に絞って書きます。
- データベース化の意味とExcelの表との違い → できること・デメリット・部署ごとの使いどころもこちら
- 今のExcelの列名と、1行に何が入っているかを書いて相談する → 例:受注一覧、1行に1つの注文、商品は1つのセルに複数
データベースの形の3つの決まり
冒頭の図の右側が、直したあとの形です。次の3つを守ると、ほとんどの表は集計や取り込みに使えるようになります。
- 1行に1件:1つの行には、1回の受注の1つの商品のように、1件だけを書く。1つのセルにも1つの値だけを入れる。
- IDの列を足す:受注ID、顧客IDのように、行と相手を番号で指せる列を作る。名前で指すと、書き方の違いで別の相手になります。
- くり返す名前は別表に分ける:顧客名や商品名を明細の行ごとに書かず、顧客の表、商品の表に1回だけ書き、明細からはIDで参照する。
公的な資料でも、同じ考え方が示されています。各府省が公表する統計表について、統計企画会議申合せの表記方法(令和2年12月18日、総務省のサイトに掲載)は、1つのセルに1つのデータを入れること、1件のデータは横1行で書き、セルの結合をしないことを挙げています。1つのセルに複数のデータがあると、計算や並べ替えに手作業が必要になるためです。
直す前に見つける6つの崩れ
直し始める前に、表の中の崩れを数えます。説明用の仮想例として、冒頭の図の左側の表には、次のうち4つが入っています。
- 結合したセル:見出しや、同じ顧客の複数の行をまとめるために結合している。
- 1つのセルに複数の値:「りんご3、みかん2」のように、商品と数量を1つのセルに書いている。
- 小計や空の行:明細の途中に小計の行や、区切りの空の行が入っている。
- 横に並んだ月:4月、5月、6月を列として横に並べ、月が増えるたびに列を足している。
- 色だけで表した意味:「黄色は未入金」のように、意味をセルの色だけで表している。色は並べ替えや取り込みで消えます。
- 同じ相手の書き方の違い:「㈱サンプル」「サンプル(株)」が別の行になっている。
崩れが多い表ほど、直す前に元のファイルを残すことが大切です。直した表の数字が元の表の合計と一致するかを、最後に確かめるためです。
直す手順(説明用の仮想例)
冒頭の図の受注の表を例に、直す手順を順に書きます。どの表でも、この順で進めると手戻りが少なくなります。
- 元のファイルを複製する:直すのはコピーのほう。元の合計を控えておく。
- 見出しを1行にする:2段の見出しは「受注_日付」のように1つの名前にまとめる。
- 結合を解除し、空いたセルを埋める:解除して空いたセルに、上の行と同じ値を入れる。
- 小計の行と空の行を消す:小計は、あとでピボットで作り直す。
- 1つのセルの複数の値を行に分ける:「りんご3、みかん2」を、りんご・3の行と、みかん・2の行に分ける。
- 横に並んだ月を縦に直す:月の列を「月」と「数量」の2列にし、行を増やす。ExcelのPower Queryには、列を行に直す操作(列のピボット解除)があります。
- IDの列を足す:受注ごとに受注ID、相手ごとに顧客IDを振る。
- 顧客と商品を別の表に分ける:顧客の表に顧客IDと正式な社名を1回だけ書き、明細には顧客IDだけを残す。
- 入力規則を付ける:顧客IDや商品は一覧から選ぶ形にし、日付の列は日付だけを受け付ける。
- テーブルに変換する:範囲を選んでテーブルにし、名前を付ける。
最後に、直した明細の数量と金額の合計が、元の表の合計と一致するかを確かめます。一致しないときは、小計の行を消し忘れたか、1つのセルの複数の値を分け忘れていることが多いです。
- 直したい表を1枚選び、列名を送って概算を聞く → 行数と、毎月その表を使っている人数を書いてください
直した表をExcelの中で使う:テーブル・ピボット・XLOOKUP
形が直れば、Excelの機能でそのまま集計や検索ができます。
- テーブル:Microsoftのサポートページは、関連するデータの管理と分析を容易にするために、セル範囲をExcelのテーブルに変換できると説明しています。テーブルではすべての列でフィルターが使え、行を足すと範囲が自動で広がります。
- ピボットテーブル:明細から、顧客別や月別の合計を作る。見せるための表は、ここで作り直します。
- 並べ替えとフィルター:テーブルにした明細を、日付順に並べ替えたり、顧客IDや商品で絞り込んだりする。小計の行が無いので、並べ替えても行が混ざりません。
- XLOOKUP:明細の顧客IDから、顧客の表の社名を引いて表示する。
- 入力規則:顧客の表の顧客IDを一覧にして、明細ではその中から選ばせる。
この形になっていれば、後から別のデータベースやSaaSへ移すときも、明細の表と顧客の表をそのまま取り込めます。形を直す作業は、移すかどうかを決める前にやっておいて損がありません。
Excelのまま使える限界の目安:同時編集・行数・履歴・権限・連携
形を直した表は、しばらくExcelのまま使えます。次の5つの観点のどれかで苦しくなったら、別の仕組みへ移すことを考えます。
- 同時編集:Microsoftのサポートページは、共同編集はOneDriveまたはSharePointに保存されたドキュメントで利用できると案内しています。社内の共有フォルダにブックを置いたまま複数人で書き込んでいて、上書きや消えた行に気づけないなら、保存場所を変えるか、別の仕組みへ移す時期です。古い「共有ブック」の機能は、Microsoftが多くの制限があるため共同編集に置き換えたと説明しています。
- 行数:Excelのワークシートの上限は、Microsoftの仕様で1,048,576行、16,384列です。上限に届く前でも、開く・再計算する・フィルターをかけるたびに待たされるようになったら、明細を年ごとにブックへ分けるか、移す先を考えます。
- 履歴:誰がいつどのセルを何から何に変えたかを、行ごとに後から確かめる必要がある場合、Excelのブックだけでは追いにくくなります。請求や在庫のように、値が変わった理由を説明する場面がある表が当てはまります。
- 権限:営業には連絡先、経理には請求の列、というように、人ごとに見てよい列や直してよい行を分けたい場合です。Excelでは、ブックを分けて渡すか、シートを保護するかになり、ブックが増えるほど中身がずれていきます。
- 連携:ほかのシステムと毎日やり取りするため、CSVの書き出しと取り込みが毎回の手作業になっている場合です。
5つのうち1つだけが当てはまるなら、Excelのまま運用の決まりで補えることもあります。2つ以上が同時に当てはまる表は、移す先を比べ始める目安です。
移す先は、業務の型が決まっていれば業務SaaS、台帳の形が独自で現場で項目を調整したいならkintoneのようなノーコードのデータベース、ほかのシステムとつなぐならデータベースが候補です。
- 5つのうちどれが当てはまるかを書いて、Excelのままか移すかを相談する → どの表から手を付けるかを一緒に決める場合は、TOC分析 5万円(2時間)で今の表を2〜3個持ってきていただき、2時間で決めます
- スプレッドシートのデータベース化と、その限界 → 限界の6つのサイン
- Excel運用を残す条件 →
- kintoneとExcel台帳の使い分け →
- ExcelとSaaSをつなぐ手順 →
列の決まりを、作った人のPCから台帳へ
直した表が数か月で崩れるのは、列の決まりが、直した人の頭の中にしかないからです。sento.groupでは、表を直すときに、次の1枚を一緒に作ります。
- 列ごとに1行:列名、意味、入れてよい値、入力する人。
- IDの振り方:次の番号を誰が振るか、どこを見れば最後の番号が分かるか。
- 列を足すときの決まり:列を足してよい人と、足したときに直す集計の一覧。
- 列の行き先:その列の値が、どの集計・帳票・ほかのシステムへ渡るか。
この1枚を、表と同じ共有フォルダに置きます。担当者が替わっても、新しい担当者は1枚を読めば、同じ形で記録を書き足せます。
入力する人と列の行き先を並べて書くと、受注明細の顧客IDを営業が入れ、月末に経理が請求書へ出す、という1本の流れとして読めます。IPOAの入力・処理・出力・担当を、表の列の単位で台帳に書き残す形です。
よくある質問
Q. Excelの表をデータベースの形に直すと、何が変わりますか?
並べ替えやフィルター、ピボットで、小計の行や結合セルを気にせずに集計できるようになります。顧客や商品をIDで指すので、書き方の違いで同じ相手が別の行に分かれることもなくなります。会議で配る表は、直した記録からピボットで作り直します。
Q. データベース化にはAccessなどの専用ソフトが必要ですか?
必ずしも必要ありません。まずExcelの中で表の形を直し、テーブルとピボットで使えます。複数人の同時更新や履歴、権限が要るようになったら、専用の仕組みへ移します。
Q. 結合セルはなぜ避けたほうがよいのですか?
結合したセルは、並べ替えやフィルター、ピボットで正しく扱えず、2行目以降が空として扱われることがあるためです。見出しの見た目は、印刷用の表で整えます。
Q. Excelは何行まで使えますか?
Microsoftの仕様では、ワークシートは1,048,576行、16,384列までです。ただ、上限に届く前に、同時編集や履歴の問題で運用が苦しくなることが多いです。
Q. Excelを複数人で同時に編集してもよいですか?
Microsoftのサポートページは、共同編集はOneDriveまたはSharePointに保存されたドキュメントで利用できると案内しています。保存場所をそろえたうえで、同時に書き込む人が増え、誰がいつ変えたかの履歴や人ごとの権限も要るようになったら、ノーコードDBなどへ移すことを考えます。
Q. 費用はどのくらいですか?
直す表の数、移す先の仕組み、つなぐシステムで変わるため、個別にお見積りします。
表を1枚、データベースの形に直す
毎月使っている表を1つ選び、列名と、1行に何が入っているかを書いて送ってください。直す手順と、Excelのままか移すかの判断を一緒に決めます。直したい表が複数あり、どれから手を付けるかを決めたい場合は、TOC分析 5万円(2時間)で一緒に決めます。