スプレッドシートのデータベース化｜AIでアプリを作る前に、表を分けてつなぎ方を決める

この記事は、スプレッドシートの表をデータベースとして使える形に分ける手順を扱います。どのツールへ移すかの選び方はエクセル業務のシステム化で、ノーコードで自作するかの判断は業務アプリをノーコードで作る前に決める4つで扱っています。

「スプレッドシートをデータベース化したい」という相談の多くは、台帳が重くなった、集計のたびに表記ゆれを直している、AIでアプリにしたいが何から頼めばいいか分からない、のどれかから始まります。どれも原因は同じで、1枚の表に性質の違う情報が混ざっていることです。この記事では、よくある受注台帳を例に、表を分けてつなぐところまでを順に説明します。

スプレッドシートのデータベース化とは、表を分けてIDでつなぐこと

見た目が同じ表でも、人が読むための帳票と、並べ替え・集計・アプリの元データに使える表は別物です。データベースとして使える表は、次の4つの条件を満たしています。

• 1行＝1件：1行に入るのは受注1件、取引先1社のように、同じ単位の1件だけです。1行に商品を3つ並べる形はここで外れます
• 1列＝1種類：「備考」に電話番号や納期を混ぜず、1つの列には1種類の値だけを入れます
• 見出しは1行だけ：結合セル、途中の小計行、空行を入れません。並べ替えや絞り込みをしても崩れない形にします
• 同じ情報は1か所：取引先の電話番号は取引先の表にだけ書き、受注の表からはIDで呼び出します

スプレッドシートをデータベース化するときに表を分けるのは、取引先の電話番号のような同じ情報を1か所にだけ書くためです。分けた表どうしは、名前ではなく取引先IDのような番号でつなぎます。表と表のつながりを図にしたものは、システム開発ではER図と呼ばれます。専門の記法を覚えなくても、四角と線で書ければ用は足ります。

1枚の受注台帳は、4つの表に分かれる

中小企業でよく見るのは、1行に取引先名・電話番号・担当者・受注日・商品1〜3と数量・請求済みかどうか・備考を並べた受注台帳です。1社あたりの受注が少ないうちは困りませんが、次の3つが起き始めます。

• 4つ目の商品が入らず、「商品4」の列を足すか、備考に書くかが人によって分かれる
• 同じ会社が「(株)〇〇」「株式会社〇〇」「〇〇」で並び、取引先別の集計が合わない
• 取引先の電話番号が変わったとき、過去の行のどこまで直したかが分からなくなる

受注台帳で商品が列に入りきらない・同じ会社の表記がばらつく・電話番号の直し漏れが出るという3つの問題は、取引先・商品・取引の情報が1行に同居していることから起きます。分けると次の図のようになります。

受注明細を受注から分けるのは、1回の受注に商品がいくつ入っても行を足すだけで済むようにするためです。「商品4」の列を足す必要はなくなります。取引先の担当者が複数いて入れ替わる会社なら、担当者を取引先から分けて5つ目の表にします。どこまで分けるかは、あとで何の単位で集計したいかで決めます。

AIにアプリを作らせる前に、誰が・何で・どの表とつながるかを決める

表を分けたら、アプリにする前に表ごとに3つのことを決めます。誰が入力するか、何を使って入力するか、どの表とつながるかです。表ごとに誰が入力するか・何で入力するか・どの表とつながるかを決めずに、AIへ「受注管理アプリを作って」と頼むと、AIは今の1枚の台帳をそのまま画面にしやすくなります。そのまま使い始め、あとから「取引先別に集計したい」「1回の受注に商品を5つ入れたい」となったとき、直すのは画面ではなく表の形です。表を変えると、画面・計算・通知がまとめて作り直しになります。修正を頼むたびに別の場所が崩れる状態は、ここから始まります。

決める内容は、次のような表1枚で足ります。

アプリ化の前に決める表では、取引先などの「上書きしてよい表」と、受注などの「追記だけにする表」を分けます。受注を上書きで運用すると、いつ何が変わったかが残りません。取引先の表を誰でも書き換えられる状態にすると、同じ会社が2行できます。取引先マスタの責任者設計で書いた、作成・変更・重複解消を誰が持つかという話は、誰が取引先の情報を入れるかを決めることです。

スプレッドシートをデータベース化する5つの手順

作業は、いま使っている台帳のコピーで行います。元の台帳を動かしながら作り替えると、途中で入力された行がどちらにも入らなくなります。

1. 今の台帳をコピーし、列を3色に塗る：取引先の情報、商品の情報、その取引で決まる情報に分けて色を付けます。元の台帳には手を付けません
2. 色ごとに別のシートへ切り出し、IDを振る：取引先はC001、商品はP001のように、名前とは別の番号を1列目に置きます。名前は変わりますが、IDは変えません
3. 受注の表からは名前でなくIDで参照する：受注の表には取引先IDだけを入れ、社名はXLOOKUPで表示します。社名が変わっても直すのは取引先の表の1行だけです
4. 入力はプルダウンで選ばせる：取引先IDの列にプルダウンを設定し、手入力をやめます。一覧から選ぶだけになるので、取引先名の書き方が人によって変わることはなくなります
5. 集計する表は別に作る：月次や担当者別の集計はQUERY関数で別のシートに出し、入力する表には数式を置きません

受注の表で取引先IDを選ばせるプルダウンは、Googleスプレッドシートの「データの入力規則」で、取引先の表のID列を範囲に指定すれば作れます。取引先が増えても、取引先の表に1行足せば選択肢に出ます。集計には、SQLに近い書き方で別の表から行を抜き出せるQUERY関数を使えます。集計を入力する表の中に置かないのは、行を足すたびに数式の範囲がずれる事故を避けるためです。Googleスプレッドシートには、範囲を「テーブル」に変換する機能もあり、列ごとに数値・日付などの型を設定して入力の誤りを防げます。ただし、テーブルに変換しても1枚の台帳が複数の表に分かれるわけではないので、表を分けてIDでつなぐ作業は別に必要です。数式が増えて表が重くなった場合の直し方はスプレッドシートが重いを「設計」で直すにまとめています。

見る表と元データの表を混同すると、移行のときに余計な作業が増えます。以前支援した営業会社では、ノーコードのデータベースに11のテーブルが並んでいましたが、中身のある表は担当者と取引先の2つだけで、残り9つは同じデータを条件で絞った表示でした。経緯はツール移行の前にやるべき「データ構造の棚卸し」に書いています。スプレッドシートでも、シートが20枚あるから20の表があるとは限りません。

データベース化の効果は、作業時間と表記ゆれの数で測る

作り替える前に、2週間分の数字を取っておきます。作り替えたあとに同じ数え方で2週間測り、比べます。見るのは次の3つです。

• 月次集計にかかった時間：表記ゆれの修正やコピーの貼り付けも含めて、集計を始めてから数字が確定するまでを計ります
• 同じ取引先の重複：取引先名の列をUNIQUE関数で抜き出し、同じ会社が何通りの書き方で入っているかを数えます
• 「どれが最新か」の確認：社内で「この電話番号は最新ですか」「どのファイルが正しいですか」と聞かれた回数を記録します

集計時間が変わらないなら、集計の表がまだ入力する表の中に残っていないかを見直します。重複が減らないなら、プルダウンを使わずに手入力できる経路（別のファイル、コピー＆貼り付け）が残っています。営業担当ごとに分かれていた表を1つにまとめ、週次集計を手作業から外した例は毎週月曜に2時間かけて営業数字を手集計している会社へで紹介しています。

スプレッドシートのまま使える範囲と、移す目安

表を分けてIDでつなげば、スプレッドシートのままでもかなりの業務を回せます。Googleスプレッドシートは1ファイルに1,000万セルまで入るため、中小企業の受注台帳で容量が先に尽きることはあまりありません。先に限界が来るのは容量ではなく、権限と同時更新です。

• 見せ分け：シートを非表示にしても、閲覧できる人は中身にアクセスできます。Googleのヘルプでも、保護はセキュリティ対策ではないと明記されています。原価や個人情報を相手によって隠したいなら、スプレッドシートの外に出す時期です
• 同時更新：同じセルを営業と事務が続けて直すと、後から入れた値が残り、前の値は変更履歴を開かないと分かりません。受注ごとに誰がいつ何を変えたかを画面で追う必要があるなら、項目単位で履歴を残すアプリが向きます
• 承認：スプレッドシートでは範囲を保護して「承認済み」の列を編集できる人を絞れます。Google Driveにはファイル単位の承認依頼もあります。ただし、受注の行ごとに値引きや与信の承認順を強制し、差し戻しの履歴を管理する用途とは別です。行ごとの承認を経てから確定させる流れが必要なら、ワークフロー機能のあるアプリが向きます

見せ分け・同時更新・承認のどれかが必要になってアプリへ移る場合でも、分けた表と「誰が・何で・どの表とつながるか」をまとめた1枚は、kintoneやノーコードツール、AIに作らせる場合の最初の資料としてそのまま使えます。移す先の選び方はエクセル業務のシステム化で、仕組み全体を誰がどう回すかから決めたい場合は業務設計とはで整理しています。顧客管理の表に絞った崩れ方は顧客管理のスプレッドシートが3ヶ月で崩れる理由が近い内容です。

一次情報・公式情報

• Google ドライブ ヘルプ「Google ドライブに保管可能なファイル」（スプレッドシートは1,000万セルまたは18,278列まで）
• Google ドキュメント エディタ ヘルプ「シートを保護する、非表示にする、編集する」（範囲を保護して編集できるユーザーを制限できる、保護はセキュリティ対策ではない、閲覧者は非表示のシートにアクセスできる）
• Google ドキュメント エディタ ヘルプ「テーブルを使用する」（範囲をテーブルに変換し、列の型を設定できる）
• Google ドキュメント エディタ ヘルプ「セル内にプルダウン リストを作成する」
• Google ドキュメント エディタ ヘルプ「QUERY 関数」

製品の仕様は変更されます。最新の情報は公表元でご確認ください。

スプレッドシートのデータベース化のよくある質問