えいじあんのOfficeざんまい: ピボットテーブルで在庫管理③～データ範囲（名前の定義）

ピボットテーブルで在庫管理②～データ入力からのつづき

毎日の在庫の動きを入力し、データを蓄積していく体制が整いました。

あとは、そのデータを使い集計するだけです。

ピボットテーブルは、指定したデータ範囲から、さまざまな角度で集計できる優れものの機能です。

今回は、ピボットテーブルの作成に入る前に、設定しておくと便利な機能を解説します。

上記でも説明したように、ピボットテーブルは指定したデータ範囲からさまざまな集計をする機能です。このデータ範囲が曲者です。

データは毎日追加または削除されたりもするでしょう。

そう、増えたり減ったりします。

そういった動きをピボットテーブル側は認識してくれません。つまり、最新の情報を得るためには、データが追加や削除などの変更があるたび、データ範囲を指定し直す作業が必要になります。

この作業を忘れると間違った情報を社員やお客様に提供しかねません。

そこで、関数を使いデータの動きに合わせて範囲が自動で変更されるようにします。

使う関数は、以前当ブログでも解説したOFFSET関数（「使える関数～OFFSET関数」参照）です。

引数は次のとおりです。

OFFSET(①基準,②行数,③列数,④高さ,⑤幅)

前回の解説では、①基準から、②行、③列数を指定し、セル位置を特定し、そのセルに入力された値を獲得するところまで解説しました。

今回はさらに、④高さと⑤幅を利用し、データ範囲を獲得します。

例えばこんな引数「OFFSET(①A1,②2,③2,④4,⑤4)」を指定すると、図の色が付いている範囲が選択されます。

まず①基準値セル「A1」から②２行目、③２列目のセル（赤い部分）が特定されます。

そして特定されたセル（赤い部分）から④高さ４行で⑤幅が４列の範囲が選択されます。

このOFFSET関数の特性を活かし、データ範囲を獲得します。

さてこの関数をどこに入力するのか、という問題がありますが、Excelには「名前の定義」というのがあり、データ範囲に名前を付けて管理できる機能があります。

ここに、データ範囲の名前と参照範囲を指定しておき、後にピボットテーブルで利用します。

「データ」シートに切り替え、「数式」タブの「定義された名前」グループから「名前の定義」をクリックします。（Office2007での解説になります。）

※Office2003では「挿入」メニューから「名前」→「定義」

「新しい名前」ダイアログボックスが表示されます。名前の欄に「在庫データ範囲」と入力します。（任意でかまいません）

参照範囲の欄に移り、「＝OFFSET（」まで入力します。

在庫データを確認します。（※ピボットテーブルはフィールド名も含めてデータ範囲とします。）

基準セルは「A1」になります。セル「A1」をクリックします。「,」で区切りながら指定していきます。

今回は特定のセルからデータ範囲を指定する必要がないので、②行と③列は「０」になります。

=OFFSET（データ!$A$1,0,0,

基準セルが「A1」で、行列が「０」指定なので基準値「A1」からすべてが始まります。

④の高さですが、これは行数の指定になります。

ここはデータの増減により変化します。

こういう場合は、COUNT関数を使い、入力されているデータ数をカウントすることで、常に最新のデータ数（行数）を獲得するようにします。

フィールドも含めてカウントするので、COUNTA関数を使います。（※COUNTA関数は数値の他、文字もカウントできます。）

「COUNTA（」と入力し、列番「A」をクリックします。