Excel でデータベースを作成する

内容

データベース (DB) について言及するとき、最初に頭に浮かぶのはもちろん、SQL、Oracle、1C、または少なくとも Access などのあらゆる種類の専門用語です。もちろん、これらは非常に強力な (そしてほとんどの場合高価な) プログラムであり、大量のデータを扱う大規模で複雑な企業の作業を自動化できます。問題は、そのような力が単に必要ない場合があることです。あなたのビジネスは小規模で、比較的単純なビジネスプロセスを使用しているかもしれませんが、それを自動化したい場合もあります。そして、これが多くの場合、存続の問題となるのは小規模企業の場合です。

まず、TORを定式化しましょう。ほとんどの場合、経理用のデータベース (例: 従来の販売) は次のことができるはずです。

続ける 商品（価格）、完了した取引、および顧客に関する情報をテーブルに表示し、これらのテーブルを相互にリンクします
快適な 入力フォーム データ (ドロップダウンリストなど)
一部のデータを自動的に入力する 印刷されたフォーム （支払い、請求など）
必要なものを発行する レポート マネージャーの観点からビジネスプロセス全体を制御する

Microsoft Excel は、これらすべてを少しの労力で処理できます。これを実装してみましょう。

ステップ 1. テーブル形式の初期データ

製品、販売、および顧客に関する情報を XNUMX つのテーブルに保存します (同じシートでも別のシートでもかまいません)。将来それについて考えないように、それらを自動サイズで「スマートテーブル」に変えることが基本的に重要です。これはコマンドで行われます 表としてフォーマットする タブ ホーム (ホーム — テーブルとしてフォーマット). 次に表示されるタブで コンストラクタ （設計） フィールドでテーブルにわかりやすい名前を付ける テーブル名 後で使用する場合:

合計で、XNUMX つの「スマートテーブル」を取得する必要があります。

表には追加の明確なデータが含まれている場合があることに注意してください。たとえば、私たちの価格各製品のカテゴリ (製品グループ、パッケージ、重量など) に関する追加情報と、表 クライアント — それぞれの都市と地域 (住所、TIN、銀行の詳細など)。

表営業完了した取引を入力するために後で使用されます。

ステップ 2. データ入力フォームを作成する

もちろん、販売データを緑のテーブルに直接入力することもできます営業、しかし、これは必ずしも便利ではなく、「人的要因」によるエラーやタイプミスの出現を伴います. したがって、次のような別のシートにデータを入力するための特別なフォームを作成することをお勧めします。

セル B3 で、更新された現在の日時を取得するには、関数を使用します。 TDATA （今）. 時間が必要ない場合は、代わりに TDATA 機能を適用できます TODAY （今日）.

セル B11 で、スマートテーブルの XNUMX 列目にある選択した製品の価格を見つけます。価格関数を使用する VPR （VLOOKUP）. 以前に遭遇したことがない場合は、まずここでビデオを読んで見てください。

セル B7 には、価格表の製品を含むドロップダウンリストが必要です。このために、コマンドを使用できます データ – データ検証（データ検証）、制約として指定 リスト（リスト） 次に、フィールドに入力します ソース（ソース） コラムへのリンク名前私たちのスマートテーブルから価格:

同様に、クライアントのドロップダウンリストが作成されますが、ソースはより狭くなります。

=INDIRECT(“顧客[クライアント]”)

演算 間接（間接） 残念ながら、Excel は [ソース] フィールドのスマートテーブルへの直接リンクを認識しないためです。しかし、関数で「ラップ」された同じリンク間接同時に、それは非常にうまく機能します (これについての詳細は、コンテンツを含むドロップダウンリストの作成に関する記事にありました)。

ステップ 3. 販売エントリマクロの追加

フォームに入力したら、入力したデータをテーブルの最後に追加する必要があります営業. 単純なリンクを使用して、フォームのすぐ下に追加する行を作成します。

それらの。セル A20 には =B3 へのリンクがあり、セル B20 には =B7 へのリンクがあります。

次に、生成された文字列をコピーして Sales テーブルに追加する 2 行の基本マクロを追加しましょう。これを行うには、組み合わせを押します Altキー+ F11 またはボタン ビジュアルベーシック タブ 開発者（デベロッパー）. このタブが表示されていない場合は、最初に設定で有効にしてください ファイル – オプション – リボン設定 (ファイル - オプション - リボンのカスタマイズ). 開いた Visual Basic エディターウィンドウで、メニューから新しい空のモジュールを挿入します。 インサート – モジュール そこにマクロコードを入力します。

Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'フォームからデータ行をコピー n = Worksheets("Sales").Range("A100000").End(xlUp) . Row 'テーブルの最後の行の番号を決定します。 Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues ' 次の空行に貼り付け Worksheets("Input Form").Range("B5,B7,B9"). ClearContents ' クリア エンド サブフォーム

これで、ドロップダウンリストを使用して、作成したマクロを実行するボタンをフォームに追加できます。 インセット タブ 開発者 (開発者 — 挿入 — ボタン):

描画した後、マウスの左ボタンを押したままにすると、Excel はどのマクロを割り当てる必要があるかを尋ねてきます - マクロを選択してください 追加_販売. ボタンを右クリックしてコマンドを選択すると、ボタンのテキストを変更できます。 テキストを変更する.

フォームに入力した後、ボタンをクリックするだけで、入力したデータが自動的にテーブルに追加されます。営業、その後、フォームがクリアされて新しい取引が入力されます。

ステップ 4 テーブルのリンク

レポートを作成する前に、テーブルをリンクして、後で地域、顧客、またはカテゴリ別に売上をすばやく計算できるようにします。以前のバージョンの Excel では、これにはいくつかの関数を使用する必要がありました。 VPR （VLOOKUP） 価格、カテゴリ、顧客、都市などをテーブルに代入するため営業. これには、私たちの時間と労力が必要であり、多くの Excel リソースを「消費」します。 Excel 2013 以降では、テーブル間のリレーションシップを設定するだけで、すべてをより簡単に実装できます。

これを行うには、タブで 且つ（日にち） クリック 関係 (関係). 表示されるウィンドウで、ボタンをクリックします。 創造する（新しい） ドロップダウンリストから、それらを関連付けるテーブルと列の名前を選択します。

重要な点: テーブルはこの順序で指定する必要があります。つまり、リンクされたテーブル (価格) をキー列に含めてはなりません (名前 ) 重複した製品 (表で発生) 営業. つまり、関連付けられたテーブルは、次を使用してデータを検索するテーブルである必要があります。 VPRそれが使用された場合。

もちろん、テーブルも同様に接続されています営業テーブル付き クライアント 共通列による顧客:

リンクを設定したら、リンクを管理するためのウィンドウを閉じることができます。この手順を繰り返す必要はありません。

ステップ 5. 要約を使用してレポートを作成します

ここで、売上を分析し、プロセスのダイナミクスを追跡するために、たとえば、ピボットテーブルを使用してある種のレポートを作成してみましょう。アクティブセルをテーブルに設定営業リボンのタブを選択します 挿入 – ピボットテーブル (挿入 — ピボットテーブル). 開いたウィンドウで、Excel はデータソース (テーブルなど) について尋ねてきます。営業) およびレポートをアップロードする場所 (できれば新しいシート):

重要な点は、チェックボックスを有効にする必要があることです このデータをデータモデルに追加します (データモデルにデータを追加) ウィンドウの下部にあるをクリックして、現在のテーブルだけでなく、すべてのリレーションシップを使用してレポートを作成する必要があることを Excel が認識できるようにします。

をクリックした後 OK ウィンドウの右半分にパネルが表示されます ピボットテーブルフィールドリンクをクリックする場所 すべて現在のものだけでなく、本にあるすべての「スマートテーブル」を一度に見ることができます。次に、従来のピボットテーブルと同様に、関連するテーブルから必要なフィールドを領域にドラッグするだけです。 フィルタ, 行, ストルブツォフ or 価値観 – Excel は、必要なレポートをシートに即座に作成します。

ピボットテーブルを右クリックしてコマンドを選択し、(ソースデータが変更されたときに) 定期的に更新する必要があることを忘れないでください。 更新して保存（更新）、それは自動的に行うことができないためです。

また、集計の任意のセルを選択してボタンを押すと、 ピボットグラフ (ピボットチャート) タブ 分析（分析） or 計測パラメータ (オプション) その中で計算された結果をすばやく視覚化できます。

ステップ 6. 印刷物に記入する

データベースのもう XNUMX つの典型的なタスクは、さまざまな印刷されたフォームとフォーム (請求書、請求書、行為など) の自動入力です。私はすでにこれを行う方法のXNUMXつについて書きました。ここでは、たとえば、アカウント番号でフォームに記入することを実装します。

セル C2 にユーザーが数値 (表の行番号) を入力するとします。営業、実際）、必要なデータは、すでにおなじみの関数を使用してプルされます VPR （VLOOKUP） および機能 INDEX （索引）.

VLOOKUP 関数を使用して値を検索および参照する方法
VLOOKUP を INDEX および MATCH 関数に置き換える方法
テーブルからのデータを使用したフォームおよびフォームの自動入力
ピボットテーブルを使用したレポートの作成