- 複数のエクセル(アプリ)で管理されているデータを、一つにまとめて集計する機能を持つアプリ
- 数値の合計や小計、最大値、最小値、平均もコレクトアプリ内で計算できる
- 不要な項目を非表示にしたり、特定の期間のみ表示するなど、効率的な集計ができる
複数の店舗の売上台帳のデータから売上管理表を作成する業務を考えてみましょう。例えば、エクセルで作成された店舗別の売上台帳の明細データから、店舗別、商品別に集計された売上管理表を作成するとします。どのようにすればいいでしょうか。
まず、エクセルの売上台帳データを集めなければなりません。メールでやりとりするか、ファイルサーバに直接置いてもらうなどで、ひとつのフォルダー配下に集めます。次に、集めた売上台帳をひとつのエクセルファイルに結合します。最後に結合された売上台帳データを元に舗別、商品別に集計された売上管理表を作成します。
実現方法はいろいろありますが、お薦めの方法は下記です。
集められた売上台帳のエクセルファイルを一つのシート内のテーブルにまとめるにはだいたい3つの方法が考えられます。
まずは地道にコピペ。直感的で前提知識がいらないので店舗数が少なくかつ1回きりか頻度が少なければこの方法が一番楽です。一方、店舗数が多い場合や、繰り返し実施する場合は面倒です。とはいっても、多くの人は地道にコピペしているのではないでしょうか。
次に、VBAで自動化です。結合だけなら20行程度のコードで実現でき、ネット上でサンプルコードも手に入るので、VBAを知っていれば簡単です。一方、VBAを知らない人にはハードルが高いでしょう。VBAはいろいろな処理を追加して改善していけるのがメリットですが、逆に複雑になり他人が維持できない原因にもなります。
お薦めはExcelの一機能のPower Queryを使うことです。Power Queryはエクセルシートに外部のさまざまなデータソースから必要なデータを取り込むための機能です。設定だけで取り込めるので特別な知識は必要ありません。ただ、あまり知られていないのか活用する人は少ないようです。エクセル上部メニューの[データ]-[新しいクエリー]から呼び出すことができます。
今回の場合、所定のフォルダー配下のエクセルファイルを取り込むように設定すれば店舗が増えた場合でも追加分も含めて自動で取り込まれます。また、取り込み時にフィルターをかければ、集計行やその他不要な行を排除することもできます。
売上管理表を作成するには、ピボットテーブルで作成するか、計算式で作成するかですが、これは圧倒的にピボットテーブルがお薦めです。
計算式で作成する場合、SUMIF()やSUMIFS()などの関数を使えば集計することができます。しかし、店舗や商品が増えて大きな表になれば集計条件をそれぞれのセルに設定していくのは非常に手間がかかります。さらに、新しい商品が増える度に表を修正してかなければなりません。計算式にミスが混入するリスクも大きく、集計表の信頼性が安定しません。計算式が有利なのは指定されたレイアウトに集計値を埋め込まなければならないような限られた場合だけしょう。
ピボットテーブルであれば、集計項目を決めるだけで簡単に売上管理表が完成します。さらに、ドリルダウンで明細を確認する、集計単位を自由に変更してさまざまな見方をするなど、さまざまな視点で分析できます。例えば、店舗別製品別の売上管理表を製品別店舗別というように集計の順番を変えることも簡単です。
エクセル(Excel)を使っていてもピボットテーブルは使い慣れていないという人が多いようですが、一度覚えてしまえば作成方法は簡単です。ピボットテーブルはエクセルで最も強力で役に立つ機能です。ぜひ活用しましょう。
ここに、売上を管理するWebアプリが2つあります。
楽々Webデータベースを使って、この営業売上台帳をコレクトアプリで集計できるようにしてみよう。
アプリが2つあるので、
1つずつ閲覧しなくてはならない
データのダウンロードも
それぞれする必要がある
集計はエクセルに張り付けて
作業する必要がある
売上の入力はこれまで通りでOK
各アプリからデータを
ダウンロードする必要がない
集計データをアプリで確認できる
だけでなく、PDF・エクセル形式で
ダウンロードも可能
ここでは、個別のデータを入力する2つのアプリ「営業1課売上台帳」、「営業2課売上台帳」は既に作成済みとします。
アプリの作り方はこちらを参照。
まとめて見られるようにしたいアプリのうちの1つを、ベースアプリとして設定します。
参照ボタンをクリックすると、作成済みのアプリが表示されるので、ここでは「営業1課売上台帳」を選択します。
コレクトアプリ上で表示する必要のないデータは非表示にします。
ここでは、「営業2課売上台帳」を読み込みます。
参照ボタンからそれぞれの項目を指定していきます。
最後に作成をクリックすると、アプリが作成されます。
コレクトアプリの編集画面から、設定>集計設定を開きます。
合計・小計を設定する画面に遷移するので、それぞれ設定を行います。
合計の設定
小計の設定
最後に適用をクリックすると反映されます。
コレクトアプリの編集画面から検索設定を開き、検索方法を指定して更新します。
集計したデータをもとに帳票が出力できるように設定します。事前に、帳票出力用のエクセルを作成しておきます。
作成したエクセルを帳票として指定します。ダウンロードメニュータイトルなども適宜指定し、登録ボタンをクリックします。
それぞれに更新される営業1課と2課のデータを、コレクトアプリ「営業部売上集計」で検索し活用することができます。
閲覧したい期間を設定の上、検索できます。
集計結果をPDFやエクセル形式でダウンロードすることができます。
PDFで出力した場合
エクセルで出力した場合
より詳しい説明は以下のページをご覧ください。