【完全無料】スプレッドシートで顧客データベースを作成してみた！
完全無料のスプレッドシートを用いてどれだけのDBを作ることができるか？？
ということで、顧客情報のデータベースを作成してみました！
結果として、
・難しい操作なし。
・取り出したいデータを指定可能。
・詳細データへのリンクあり
・オンラインでどこでも管理可能。
・検索機能も付く
⇒これだけできて無料！！！
の、素晴らしいものができてしまったので、、、公開したいと思います。
正直、10万円以上の価値があると思います。
是非参考にしてみてください！！
サンプルファイルダウンロード
サンプルファイルのダウンロードは下記から行ってください。
そのままですと閲覧のみ可能になっていますので、
ファイル→コピーを作成で、自分のスプレッドシートにコピーを作成してください。
できることを画像で説明！
まずは、できることを画像を用いて説明していきたいと思います。
①まずは、各々の担当者が、スプレッドシートで同じフォーマットを用いて情報を管理する。
※サンプルでは、下記のようなデータを使用しています。
②上記のファイルをグーグルドライブの特定のフォルダに保存
③ファルダ内にあるスプレッドシートの情報を1クリックですべて吸い出し、DBを作成。
④QUERY関数を用いて、DBから特定の情報を検索可能。
となります。
このDBのすごいところ
このDBのすごいところは、ありすぎて困ってしまうのですが、
①抜き出したい情報(セル)を簡単に変更可能
②DBのリンクから簡単に詳細情報に飛べる
③フォルダ内の全ファイル＋全シートを検索するので、シートが多いファイルでも検索可能
④フォルダIDを変えれば様々なDBを作成可能
⑤様々な検索方法が可能
などなど完全無料でできます。。
本当にすごいですグーグルさん。スプレッドシートさん。
ということで簡単に説明していきたいと思います。
①抜き出したい情報(セル)を簡単に変更可能
まずは、抜き出したい情報(セル)を簡単に変更可能なことです。
顧客DBでシートから名前の一覧を取ってきたい場合、
下記画像の通り、顧客DBの「参照セル」に対象のセルを入力するだけです。
下記例では、名前のデータが「セルD6」に入力されていますので、
参照セルにD6を入力。畑利治さんが、一覧に表示されていることがわかります。
同様に、「D1」が担当「D5」が名前(カナ)と、
このセルを変えるだけでとってきたい内容を瞬時に変えることが可能です。
フォーマットを変更したいとき、違うデータベースを作りたいときにも、
非常に簡単に作成することが可能かと思います。
②DBのリンクから簡単に詳細情報に飛べる
次はDBのリンクから簡単に詳細情報に飛べる点です。
顧客DBには対象ファイルへのリンクが作成されますので、
対象のデータにワンクリックで飛ぶことができます。
その人独自の詳細な情報などは、千差万別ですので、
DBだけで管理するのは難しいですが、この機能より簡単に詳細情報を見ることができます。
③フォルダ内の全ファイル＋全シートを検索できる
・1ファイルで1人の顧客データを持つ人もいれば、
・1ファイルに多数のシートをもって顧客データを管理する人
もいると思います。
また、年度ごとにフォルダ管理したい人もいると思います。
それらの情報を一度に検索し、DBにできるのがこのシステムになります。
先ほどのリンク機能もありますので、ファイルにはすぐにアクセス可能ですので、
煩雑になりがちな情報を一括に集約することができます。
④フォルダIDを変えれば様々なDBを作成可能
フォルダのIDを変えることで簡単に違うDBを作成することができます。
フォーマットを変更し、参照セルを変え、フォルダIDを変更
の3ステップで簡単に新しいDBを作成することができますので、
だれでも自由に作成することができます。
⑤様々な検索方法が可能
まとめたDBから簡単に検索(絞り込み)が可能です。
対象となる項目ごとに、
文字列 ⇒ 頭文字の一致、末尾の一致、含む
数字、日付 ⇒ 以上、以下、未満、より大きい、等しい、等しくない
で絞り込みを行うことができます。
下記画像では、担当者に「B」が含まれ、年齢が30歳より大きい人のデータを抜き出しました。
使用した関数、GASのコードなど。
順次更新していきます。
まとめ
スプレッドシートを用いてデータベースを作成していきましたが、
正直言ってここまでの完成度のデータベースを作成できるとは思っていませんでした。。
以前会社で、データベースシステムの改修を
ベンダーさんに頼んだら構築で500万円以上、改修で200万以上
取られましたが、正直それ以上の価値があるシステムになっています。
特にシステムにとらわれず、自分で自由に変更、更新できるところが
かなりユーザーには嬉しい内容ではないかな？と思います。
エクセルではできないことが多々ありますし、
これから5Gで処理速度が追い付いてくると、
すべてスプレッドシートに置き換わる日も遠くないかもしれませんね。
各種DBの作成依頼あればお気持ち程度のお金で承りますので、
コメント欄(メールアドレス必須)に書き込んでいただければと思います！
コメント欄
始めまして
すごくわかりやすいシートでしたので顧客管理のシステムとして
運用をしてく思いました！！
こんにちわ！大変便利なものをありがとうございます。
顧客管理DBとても便利そうで使用させて頂きたいです。
コピー作成して別フォルダに移動したのですが、情報更新ボタンを押したのち、シート情報より右がすべて空白になってしまいました。。ファイルの情報はうまく更新されております。
うまく下記が読みとってくれていないのかと思いますが、
=IFERROR(IMPORTRANGE($G8,"'" & $E8 & "'!"&H$4),"")
どのように紐づければ良いでしょうか。
ご連絡ありがとうございます。
IMPORTRANGE
を使用するに際して、参照元のフォルダの閲覧権限が必要になりますので、
たぶんその設定だと思います。
参照するフォルダ全体で一気に設定できたはずですので、、一度確認していただいてよろしいでしょうか？
質問に丁寧にご対応頂きありがとうございます！
このツール丁寧に使用させて頂きます。
資料を拝見し素晴らしいと感じています。もし可能であるなら一度打ち合わせさせていただけませんでしょうか。
すみません、返信遅れました！
打合せは可能ですが、少し昔に書いた記事なので、
思い出すのに時間はかかるかもしれません。。。。笑
それでもいいのであれば、お手伝いできるかと思います！
初めてまして、データベースとして凄くいいものを発見して喜んでおります。
数点お伺いしたいことがございます。
・リンクURLをそれぞれの顧客シートに飛ばすことは可能でしょうか？
・フォルダの情報のフォルダ名、SS、写真などが必要がない場合はどうすれば良いでしょうか？
ご回答よろしくお願い致します。
ご質問ありがとうございます！
・リンクURLをそれぞれの顧客シートに飛ばすことは可能でしょうか？
⇒ シートごとにIDを持っていますので、たぶんできるのでは無いかな？と思います。
時間のある時に、確認させてもらいます。
・フォルダの情報のフォルダ名、SS、写真などが必要がない場合はどうすれば良いでしょうか？
⇒ フォルダ情報に関しては、計算式上必要なので列をグループ化する、非表示にするなどで対処していただきたいです。
写真は
30行目の "=image(\"https://drive.google.com/uc?id="+file.getId()+"\")", を "",
に変更してもらえたらと思います！
ご返信ありがとうございます。
それぞれのシートに飛ばせることができるとありがたいです。
よろしくお願い致します。
フォルダ情報は非表示にすることにしました。
追加で申し訳ありませんが、、、
更新際に、内容は変更は更新されている状態ですが、シートの順番が変わらないことは出来ますでしょうか？
シート名のA社からU社まで更新かけても順番が変わらないようにすることは可能でしょうか？
（もしよろしければメールでのやり取りが可能でしたら、メールの方でお願いできればと思っております。）
シートの方を共有させて頂きました。
・リンクURLをそれぞれの顧客シートに飛ばすことは可能でしょうか？
⇒ シートごとにIDを持っていますので、たぶんできるのでは無いかな？と思います。
時間のある時に、確認させてもらいます。
上記内容はいかがでしょうか？
個別でリンクを飛ばせた方が大変便利ですのでお願い致します。
はじめまして、顧客管理表の作成依頼をさせていただきたいのですがもし可能でしたら一度内容のお打ち合わせをさせていただけたらありがたいです。
宜しくお願い致します。
承知致しました！
別途メールさせて頂きます！
初めまして。
すごくわかりやすく顧客管理として活用したいと思いました。
一度ご相談させていただきたいです。
よろしくお願いいたします。
承知しました！
別途メールさせて頂きます！
検索より失礼いたします！
大変便利で使用させていただこうと考えているのですがフォルダ名の直下はどのように取得しているものなのでしょうか？
変更・設定方法などございましたらご教示いただけますと幸いです。
Aynさん
コメントありがとうございます。
ざっと説明すると、下記のとおりですが、
分かりますかね？？？
Ⅰ，フォルダーの直下は簡単で、下記の通り、
・getFiles()を用いてフォルダ内のファイルを一括取得
・while(files.hasNext()で取り出していく
形です。
// フォルダ内のファイルを一括取得
var files = DriveApp.getFolderById(DriveId).getFiles();
// 配列を宣言
while(files.hasNext()) {
//一括取得したファイルの中から順に一つ取り出す
var file = files.next();
*ファイルごとに行いたいことを書く***************************
}
Ⅱ，1階層下の場合、それに加えて、
・.getFolders(); でドライブ内のフォルダーを一括取得、
その後、Ⅰのフォルダー直下の動きを加えていく形になります。
var folders = DriveApp.getFolderById(DriveId).getFolders();
while(folders.hasNext()) {
var folder=folders.next();
var DriveId2=folder.getId();
// フォルダ内のファイルを一括取得
var files = DriveApp.getFolderById(DriveId2).getFiles();
// 配列を宣言
while(files.hasNext()) {
//一括取得したファイルの中から順に一つ取り出す
var file = files.next();
*ファイルごとに行いたいことを書く***************************
}
}
ご返信ありがとうございます✨
シートを編集して使用してみたのですが、検索シートの方で検索をかけると項目名の部分にもデータが入ってしまい抽出したデータが1つ少なくカウントされる状態になってしまいます。
項目名：TEL080-2555-5555 のように一番目のデータが項目の部分に続けて入っております。
=QUERY(data!$G$7:$Y$1000,$G$8,-1)
こちらは変更しておりません。
何か原因がお分かりでしたらお教えいただきたいです。
よろしくお願い致します。
Aynさん
コメントありがとうございます。
=QUERY(data!$G$7:$Y$1000,$G$8,-1)
↓
=QUERY(data!$G$7:$Y$1000,$G$8,1)
でどうですかね？
-1だと、見出しを自動判定、
1だと1行目に見出しがあると指定できます。
-1しか知りませんでした,、、
ありがとうございました！
一点不明点があり、ご教示いただきたいのですが、DBを更新した際に、項目が一部
[ファイル名]→[顧客管理シート(○○)]
[シート名]→[○○]
[リンク]→[ファイル名と同じファイルのURL]
となってしまいました。
項目に反映されてしまったファイルも一覧に表示されている状態です。
解決方法や原因がお分かりでしたらご協力をお願い致します。
コメントありがとうございます。
検索対象となるファイル(=フォルダに保管しているファイル）の名前は、
[ファイル名]→[顧客管理シート(○○)]
ではないという理解でよろしいでしょうか？
文字化けするような特殊な名前でもないでしょうか？
そうでないとしたら、解決策、原因が不明ですので、
作成中のファイルをblueinfinity4ever@gmail.comまで共有していただければ、
確認させていただきます。
DB使用させていただいているのですがシート名とリンク先が間違っているものが多数あります。
公開用フォルダを確認しましたがそちらも宮脇澪さんのリンクを押すと大前美由紀さんの顧客管理シートに飛びます。
私だけなのかは不明ですが解決方法をご教示いただけますと幸いです。
よろしくお願い致します
お忙しいところ恐れ入ります。素晴らしいものを拝見させていただきました。
サンプルは指定フォルダ中の全てのファイルから取得したシートを「全て書き出している」形になっておりますが、こちらを「特定の文字列を含むシートのみ書き出し」といった具合にはできますでしょうか。
ご教示いただけますと幸いです。よろしくお願いいたします。
返信遅れ申し訳ございません。
・シート名に含まれている単語で除外する ⇒ これはプログラムで可能
・検索シート側で、該当のセルにある単語があれば除外する ⇒ これは、query関数で可能
のような形で対応可能かと思います！
お世話になっております。突然のご連絡、申し訳ございません
公開いただいているシートを参考にさせていただいて、顧客管理シートができました！誠にありがとうございます。
1点ご相談なのですが、
「タブ：date」の顧客リスト一覧の自動反映箇所（=IFERROR(IMPORTRANGE($G13,"'" & $E13 & "'!"&P$4),"")があり、そのまた右側には手動で記載する列も追加して運用したい、と考えているのですが、
個別の顧客管理シートのタブを増やすと、
リストが上から更新されてしまい、手動入力箇所がずれてしまいます。。
解決方法を教えていただけると助かります。
GWで返信遅くなり申し訳ございません。
ファイルの情報の順序を定めることができないので、手動入力欄がずれてしまうと思います。
顧客管理シートの方で、手動入力欄を作って連動させるしかないというのが現状です。
申し訳ないです。。。