エクセルVBA

エクセル:集計関連の操作

エクセルのちょっとしたテクニックを紹介します。


エクセルでSUM関数を使った合計行のある表にフィルタを設定し、データを抽出した場合、合計欄には、抽出されたデータだけでなく対象となるセル範囲に含まれるすべてのデータの合計が表示されます。そのため、抽出したデータのみの合計を求めたい場合は別途計算が必要になるのですが、SUM関数の代わりにSUBTOTAL関数を使うと、抽出されたデータのみの合計を合計欄に表示させることができます。


(1)合計を表示するセルを選択。
(2)選択したセルに、数式 =SUBTOTAL(9,集計範囲)を入力します。
(例)セルB2からB100が集計範囲の場合、=SUBTOTAL(9,B2:B100)と入力。

(3)オートフィルタを使ってデータを抽出すると、抽出されたデータのみの合計が表示されます。

SUBTOTAL関数では、リストの集計に使用する関数を1〜11の番号で指定します。なお、Excel2003では、条件によって1〜11以外に101〜111の番号を指定することも可能です。1と101は関数は同じですが、非表示となっているセルは、集計しません。

リストの集計に使用する関数は以下の通りです。
1:AVERAGE関数,2:COUNT関数,3:COUNTA関数,4:MAX関数,5:MIN関数,6:PRODUCT関数,7:STDEV 関数,8:STDEVP 関数,9:SUM関数,10:VAR関数,11:VARP関数


エクセルの集計テクニック


エクセルのちょっとしたテクニックを紹介します。


エクセル2002からオートSUMボタンは「平均値」や「データの個数」、「最大値」や「最小値」といった値も求められるようになりました。


(1)数式の結果を表示したいセルを選択します。
(2)[標準]ツールバーにあるオートSUMボタン右側の[▼]ボタンをクリックします。
(3)表示されたメニューから求めたい値を選択します。
  なお、各メニューを選択すると、それぞれの値を求める関数が挿入されます。
   「合計」→ SUM関数
   「平均」→ AVERAGE関数
   「データの個数」→ COUNT関数
   「最大値」→ MAX関数
   「最小値」→ MIN関数

(4)引数となるセル範囲を指定し、[Enter]キーを押すと、求められた値が表示されます。


※上記(3)で表示されるメニューにある「その他の機能」を選択すると、[関数の挿入]ダイアログが表示され、その他の関数を選択することができます。


エクセルのオートフィルタ機能を使用すると、条件に合ったデータの抽出はもちろん、条件と異なるデータの抽出もできます。例えばタスク管理などで、完了以外のデータを抽出し、未完了のタスクを確認するといったことが簡単にできます。


(1)データ範囲内のセルを選択し、[データ]メニューの[フィルタ]から[オートフィルタ]を選択します。
(2)オートフィルタ機能が有効になり、列見出しのセルの右側に[▼]ボタン(オートフィルタ矢印)が表示されます。


(3)抽出条件の対象となる列の[オートフィルタ矢印]をクリックし、[(オプション)...]を選択します。
(4)表示された[オートフィルタ オプション]ダイアログで抽出条件を指定し、[OK]ボタンをクリックします。
(例)「完了」以外のデータを抽出する場合、左側のボックスから「完了」、右側のボックスから「と等しくない」を選択します。


(5)指定した条件により抽出されたデータのみが表示されます。


※抽出されている項目のオートフィルタ矢印は、青色で表示されます。


エクセルの書籍


条件に合ったデータの合計を求めるには「SUMIF関数」を使いますが、この関数と同じ結果を求められるものとして「条件付き合計式ウィザード」があります。
これは、ウィザードの指示に従って必要項目を入力すると、結果を求めるための数式が自動的に作成されるアドイン機能ですが、複数の条件を指定する場合にはウィザードを使った方が簡単にできるので使ってみて下さい。

(1)[ツール]メニューから[アドイン...]を選択し、表示された[アドイン]ダイアログで[」条件付き合計式ウィザード]のチェックボックスをオンにして、[OK]ボタンをクリックします。これで、「条件付き合計式ウィザード」が使えるようになります。
※環境によってはアプリケーションCDによるインストールが必要になりますので、状況に応じて表示されるセットアッププログラムの手順に従って操作してください。

(2)[ツール]メニューの[ウィザード]から[条件付き合計式...]を選択し、条件付き合計式ウィザードを起動します。

(3)[ステップ1/4]で、合計を求める値を含むリスト(タイトル(列ラベル)を含む)範囲を指定し、[次へ >]ボタンをクリックします。

(4)[ステップ2/4]で、合計を求める値がある列、合計の対象となる条件を指定し、[条件の追加]ボタンをクリックします。複数の条件を指定する場合は続けて他の条件を指定し、[条件の追加]ボタンをクリックして条件を追加します。条件の指定が終わったら[次へ >]ボタンをクリックします。

(5)[ステップ3/4]で、[単一のセルに数式だけをコピーする]が選択されていることを確認し、[次へ >]ボタンをクリックします。

(6)[ステップ4/4]で、数式をコピーするセルを選択し、[完了]ボタンをクリックすると、選択したセルに条件付き合計式がコピーされ、条件に合った合計の値が表示されます。

セルには、数式だけでなく指定した条件もコピーすることができます。その場合、(5)で[数式と条件値をコピーする]を選択し、続いて表示される[条件付き合計式ウィザード]で条件や数式をコピーするセルをそれぞれ選択します。

エクセル
SUMIF関数は、その名の通りSUM関数IF関数の機能を併せ持ったもの、つまり、条件にあったデータのみ集計する関数です。

(1)集計結果を表示したいセルを選択。

(2)選択したセルに数式 =SUMIF(範囲,検索条件,合計範囲)と入力。

例)B1:B5に数値が入力されていて、B6セルにマイナスの値だけを合計する場合、
B6セルに =SUMIF(B1:B5,"<0",B1:B5)と入力して[Enter]キーを押す

上の「範囲」は検索条件に当てはまるかどうかを調べたいセル範囲を指定します。「検索条件」は検索条件を入力し、「合計範囲」は実際に計算対象にしたい(合計したい)セル範囲を指定します。また「検索条件」にはワイルドカードも使えます。

エクセル
小計と合計のある表では、それぞれの欄にSUM関数を入力しなければなりませんが、あらかじめ小計欄と合計欄を選択し、オートSUMボタンをクリックすると、小計と合計を一度に入力することができます。


(1)[Ctrl]キーを押しながら、小計欄を選択。
(2)[標準]ツールバーの[オートSUM]ボタンをクリックします。
(3)小計欄にはデータを合計するSUM関数が入力されます。


(4)[Ctrl]キーを押しながら、合計欄を選択。
(5)[標準]ツールバーの[オートSUM]ボタンをクリックします。
(6)合計欄には小計欄を合計するSUM関数が挿入されます。


※小計欄と合計欄を選択する際、選択する順序や方法を間違えると正しい数式が入力されません。

エクセルの書籍
合計を表示する関数ですが、エクセルで最もポピュラーな関数かもしれません。


(1)合計を表示するセルを選択します。

(2)選択したセルに数式 =SUM(範囲)と入力します。
例)B1からB5までの合計をB6に表示する場合、B6に=SUM("B1:B5")と入力します。


※[オートSUM]ボタンを使用するときは、B1からB5までドラッグして[エンター]ボタンを押します。また、複数の範囲の合計を計算するときは、数式=SUM(範囲1,範囲2,・・・)とカンマで区切って入力します。
エクセル
〜ちょっとしたテクニック〜
Add to Google My Yahoo!に追加 ブックマークに追加する
Author

エステリア

エクセルの基本から小技までちょっとしたテクニックを随時提供中

ご案内
●役に立ったと思った方は●

人気blogランキング

1日1回のクリックでこのブログが育ちます。応援よろしくお願いします。
----------------------------------------------
●運営ポリシー●

エクセルのいろんな機能を随時紹介していきます。
トラバ、コメント大歓迎です。内容を確認の上、掲載します。アダルトサイトからのトラバ、コメントはご遠慮下さい。
楽天市場