• PCソフトコーナー
• My Free-style PC

エクセルのオート機能　後編

エクセルには、手間を省き作業を楽にする便利なオート機能が備わっています。
　このエクセルのオート機能（後編）では、集計で多く使われるオートSUMなど数式の入力、データの抽出を補助するオート機能を紹介します。

　なお、このファイルは「My Free-style PC」の「PCソフトコーナー」のファイルです。検索エンジン経由で、このページを訪問いただいたのであれば、サイト内の他のファイルも是非ご覧ください。

★　目　次

2　数式の入力、データの抽出を補助するオート機能

2-1　オートSUM

集計のために最も便利な機能はオートSUMです。
　オートSUMでは、集計値を表示するセルを選択し、セルをツールバーの「Σ」ボタンをクリックし、Enterキーを押すだけと簡単であり、極めて便利です。

　右の表では、果物の購入価格を合算しています。

　はじめに表の形をつくり、B列のB2〜B5のセルに、りんご〜バナナの価格を入力します。

　この後、合計欄のB6セルを選択し、ツールバー上の「Σ」ボタンを押すとオートSUMにより、B6セルに =SUM(B2:B5) と数式が入力され、合計額が表示されます。

　このSUM(B2:B5)のB2:B5は引数であり、B2〜B5間のセルを参照範囲としていることを表しています。

　そしてSUMは集計関数ですから、＝SUM(B2:B5)で＝B2＋B3＋B4＋B5と4つのセルに入力されている数値を足しており、答えがB6セルに「2000」と表示されています。

　基本的には、オートSUMを使うときは、先に集計範囲のセルに数値が入力されていることが前提となっています。

下の表は、同じ表を行と列を入れ替えており、集計は横方向となりますが、この場合も同じようにオートSUMが使えます 。

このようにオートSUMでは、行方向への集計も、列方向への集計も、いずれも使えますが、右の表のように行と列がどちらも集計できる場合は、縦方向の列の集計が優先されます。

つまり、この表の場合は、E5セルを選択して「Σ」ボタンを押すと「=SUM(E2:E4)」と数式が入力され、E2〜E4のセルの値の合計値が表示されます。

さらに単純な表ではなく複雑になってくると、何せ自動で集計式を入力するため思わぬ結果となる場合もあり、集計の向きや集計範囲に気をつける必要があります。

　少し確認してみると、エクセルのオートSUMは、概ね次のようなルールに従っています。

　１　オートSUMは、対象セルのすぐ上のセルから、列方向に縦に遡って、連続するセルの数値を
　　集計、あるいは対象セルのすぐ左横のセルから、行方向に横に遡って、連続するセルの数値
　　を集計します。

　２　すぐ上のセル、またはすぐ左横のセルに数値が入って無くても、数値の入っているセルまで
　　遡り、その数値が入っているセルから連続して数値が入っている複数のセルのかたまりを集計
　　します。

　３　対象セルのすぐ上のセルにも、左横のセルにも数値が入っている場合は、列方向、つまり縦に
　　集計されます。

　４　数値が並んでいる中に、文字やブランクが含まれると、その文字やブランクの手前までの連続
　　する数値を集計ます。


　５　小計と総計のように階層を区別して、集計の向きと集計範囲が変更されます。


　６　縦方向の集計が優先されるものの、すぐ左横のセルに数値が入っていて、すぐ上のセルが
　　ブランクの場合やSUM関数が既に入力された小計のようなセルの場合は、横方向に集計され
　　ます。


　複数の列を集計する表の事例として、下の表は列を増やした表です。
　数値を入力するセルが全て数値で埋まっていれば、縦方向の集計は問題ないのですが、空白セルや文字・記号が入力されているセルがあると、想定外の式が入力されてしまうことがあります。

一つずつ見ていくと、表のB6セルとC6セルの数式は想定どおりの数式が入力されており、集計の向きは縦方向で正しく、集計範囲も正常です。

　しかしD6のセルの数式は、本来D2〜D5を集計してほしいのに、集計範囲がD4〜D5に狭められています。これはD3セルに「不要」と文字が入力されているため、D3より上の行は、数値が入っている連続セルとみなしていないためです。

　また、E6セルの数式は、「=SUM(B6:D6)」と横方向に集計し、想定外の数式となっています。
　これは、E6のセルのすぐ上のE5セルがブランクで左隣のD6セルに数値があるため、オートSUMが横に集計するセルと勘違いしているように思われます。しかし勘違いや誤りではなくて、そう扱うルールとなっているのです。

　F6、G6のセルは、集計方向は良くても、集計範囲が狭められています。
　C列のように、C6のすぐ上の隣接セルがブランクでも、さらに上のセルから正しく集計できるのに、途中がブランクであれば、その下のセルまでしか集計しないことは一見不思議に思えます。

　しかし、これも連続する数値の入っているセルの１グループのみ集計範囲とするため、当然のことです。

　H6のセルは、H列のH2〜H5に数値が入力されていないため、「=SUM(F6:G6)」と横方向に集計する式となっていますが、集計範囲はF6とG6に限られています。これはE6セルに横方向の集計数式が既に入っているため、その右隣のセルからの範囲を集計するためです。

　右の表は、前の表の行と列を入れ替えた表であり、横方向に集計する表です。

　F2とF3のセルの数式は、前の表と同じように正常です。

　しかし、F4のセルの数式は、「=SUM(F2:F3)」となっており、すぐ左横のセルに数値が入力されていても横方向に集計せず 、縦方向にF2とF3を集計しています。

　これは、縦方向の集計を優先するためであり、数式が入っていて数値が計算されているセルであっても、すぐ上に２つあれば、縦方向に２つ足してしまうためです。

　つまり、この例では空白も多いのですが、仮に全てのセルに数値が入力されていても、横方向に2行集計した次は縦方向にその集計値を２つ足し、さらに横方向に2行集計した後、縦方向にその集計値を２つ足すというを繰り返します。

　よってF5とF6のセルは、横方向に集計され、F7のセルは縦方向にF5とF6のセルを足しています。なおF6のセルは、C6とD6のセルが空白のため集計方向は良くても、集計範囲が狭められています。
　
　F8のセルの数式は、B8〜E8のセルが全て空白であるため、縦方向にF7とF4を足す数式となっています。

　縦方向にF2〜F7は全て数値が入っているのに、なぜF7とF4のみを足すのか一見不思議に思われますが、F4はF2とF3が足された小計、F7はF5とF6が足された小計で、小計を2つ足してF8に総合計が集計されている形となっています。

　つまり、オートSUMでは、数値が直接入力されたセル、数式が入力されていて集計するセル、さらに集計されたセルを集計しているセルを区別して取り扱っています。

　ビギナーの方には少し難しい説明となりましたが、マトリックスの表、つまり複数の列または行でオートSUMを使う場合は、次の2点に注意すれば、ほとんどのケースで問題がないでしょう。

　１　同じ行または同じ列の複数のセルを一括して選択し、「Σ」ボタンを押すと、集計する方向を
　　間違えることはない。

　２　集計範囲は、遡って集計したい範囲の先頭のセルに数値が入っていることを確認する。

　なお集計方向や範囲が正しいかどうかは、念のために数式を確認し、意図しない集計範囲の場合はEnterキーを押し数式を確定する前に、正しい集計範囲の複数セルをマウスで選択すると簡単に修正できます。

　右の表では、F列のF2〜F8の複数のセルを一括して選択し「Σ」ボタンを押しています。

　その結果、F2〜F8のセルの数式が、すべて横方向に正しい範囲（B列〜E列）で集計する式となっています。

　つまり、りんご〜バナナの合計が、どの行も正しく計算されています。

＜SUM関数以外の関数のオート機能の利用＞

　オートSUMの「Σ」ボタンの右横に矢印ボタン「▼」があります。
　この「▼」をクリックすると、「合計」（＝オートSUM）のほか、「平均」、「データの個数」、「最大値」、「最小値」、「その他の機能｣が選べるようになっています。

　うち「その他の機能｣は、関数を選択できるだけのことですが、「平均」、「データの個数」、「最大値」、「最小値」については、オートSUMと同じように扱えて便利です。

　下の表は、「Σ」ボタンの右横の「▼」をクリックし、「平均」、「データの個数」、「最大値」、「最小値」をそれぞれクリックしたときの結果です。

　

　F2セルでは、「平均」をクリックしており、AVERAGE関数が自動的に挿入され、B2〜E2セルを対象範囲として、その値の平均値が「35」と計算されています。

　F4セルでは、「データの個数」をクリックしており、COUNT関数が自動的に挿入され、B4〜E4セルを対象範囲として、その範囲のデータが入力されているセルの数をカウントし、「4」と表示しています。

　F6セルでは、「最大値」をクリックしており、MAX関数が自動的に挿入され、B6〜E6セルを対象範囲として、その範囲内の最大値としてC6セルの値「50」が表示されています。

　F8セルでは、「最小値」をクリックしており、MIN関数が自動的に挿入され、B8〜E8セルを対象範囲として、その範囲内の最小値としてD8セルの値「20」が表示されています。

2-2　オートカルク

オートカルクは、集計や計算のための数式をセルに入力するためのものではありませんが、選択範囲の集計値などの結果を数式を入力せずに確認することができる機能です。

　つまり、データの入力中などに、ちょっと確認したいときに、面倒なことをしなくても複数のセルを選択するだけで解かることが便利であり、エクセルのウィンドウのステータスバー上に結果が表示されます。

　下の画面は、B2〜B9までのセルを選択しています。

　

　この左側の画面では、選択範囲の数値の合計が、エクセルのウィンドウの最下段のステータスバー上に「161.3」と表示されています。また右側の画面では、選択範囲の数値の平均が「20.165」と表示されています

　ステータスバー上を右クリックして表示されるホップアップメニューの「平均」、「データの個数」、「数値の個数」、「最大値」、「最小値」の中からクリックし選択すると、「合計」以外の表示に切り替えることができ、上の右側の例では「平均」を選んで表示させています。

　なお「数値の個数」は文字を含みませんが、「データの個数」は文字を含みます。

　オートカルクは、あくまで検証のためのものですが、入力忘れや桁違いなどの入力ミスのチェックや、複雑な数式の場合、あらかじめ単純な結果を確認しておくことで数式の誤りを見つける手掛かりとなるなど、工夫して使うと良いでしょう。

2-3　オートフィルタ

エクセルでは、同じ列に同種のデータを並べた整然とした表を「リスト」と呼びます。
　実は、古くからコンピュータ用語として、「データベース」という言葉があります。

　経理の伝票や統計の調査票のように、何千何万と同じ個表を整理するときに、ルールを定め統一した様式で入力し、デジタルデータとして保存し、集計や分析の元表としていますが、この元となるデータの集合体である表（テーブル）がデータベースです。

　また最近は、このデータの集合体とその管理システムを含めてデータベースと呼ぶことが一般的ですが、昔から汎用コンピュータの世界では、元となるデータベースと集計や分析のプログラムとを分け、プログラムがデータベースを参照して結果を出しています。

　余談はさておき、エクセルのリストは、データベース的な使い方ができる形をした表です。
　つまりリストは、下の表のように、表頭に「年月日」、「区分」、「商品名」、「単価」、「数量」、「金額」、「購入店舗」、「支払方法」という見出し（項目）を付け、その下の行からは個々のデータを一行ずつ入力するスタイルとなっています。

　そして、この個々の一行のデータを「レコード」と呼び、必要なレコードのみ抽出する便利なオートフィルタ機能が使えるようになっています。

　オートフィルタは、リスト内の任意のセルをクリックし（下の表ではA1セルを選択）、メニューバーの「データ」→「フィルタ」→「オートフィルタ」をクリックすると、表頭の見出し行の各項目に「▼」が現れ準備完了です。

　

　このリストの1行目の「年月日」〜「支払方法」までの各項目の「▼」をクリックすると、それぞれの入力データの一覧がプルダウンメニューで表示されるため、その中から選んでクリックすると抽出できます。

　上の表で、商品名（C1セル）の「▼」をクリックし、プルダウンメニューで表示される中から「ガソリン」を選択して抽出したものが下の表です。

　

　抽出すると商品名（C1セル）の「▼」が「▼」と青色に変わり、2行、6行、9行、14行、16行、19行の行番号が青色で表示されており、オートフィルタが使われていることを分かりやすくしています。

　この表の項目うち、「単価」、「数量」、「金額」は数値、その他の項目は文字が入力されており、数値は足したり引いたり計算できますが、文字は同じ文字かどうか比較ができても計算はできず、大きさを比べることができません。

　数値の場合、大きさを比べられるため、「▼」をクリックし表示される「トップテン」のレコードを抽出することができます。金額などの数値では、同じ値を抽出しても意味がないことが多いため、「トップテン」の方が役に立ちます。

　元のリストから、金額（F1セル）の「▼」をクリックし、「トップテン」を選ぶと右の画面が現れます。

　ここで、上位（数値の大きいもの）、下位（数値の小さいもの）のどちらかを選び、抽出件数を選び「OK」をクリックすると抽出できます。

　なお右上の画像の右端が「項目」となっており、普通に「トップテン」を抽出するためにはこのままで良いのですが、数値が割合（％）の場合は、パーセントを選択することもできます。

　下の表では、金額の大きいものを7件抽出しており、右上の画像の「10」件（初期値）を「7」件と変更して「OK」をクリックしています。

　

　抽出したリストを元のリストに戻す方法は、上の表では金額（F1セル）の青色の「▼」をクリックし、表示されるプルダウンメニューの中から「（すべて）」をクリックすれば戻ります。

　前に「ガソリン」だけ抽出した表では、せっかく抽出したので合計額が知りたいことでしょう。
　既に「オートSUM」と「オートカルク」を説明しましたので、復習を兼ねて下の表で説明します。
　

　F列の抽出されたF2〜F19セルを一括選択すると、「オートカルク」により、ステータスバーに「合計=35940」と表示されており確認できます。

　またF20セルをクリックして、ツールバー「Σ」ボタンを押すと「オートSUM」により、「35490」と合計金額が集計されます。いずれも抽出されたレコードのみ集計され、オートフィルタが適用されていても使えることは便利なことです。

　ついでにリストの使い方の基礎知識として説明すると、大きなリストとなったときにウィンドウ枠を固定すると便利です。

　「ウィンドウ枠の固定」は、上のリストの例では、1行目のみ見出しですから、その下の2行目の行番号2をクリックして、行全体を選択し、メニューバーの「ウィンドウ」→「ウィンドウ枠の固定」を順にクリックすることで可能です。

　これで行が多くなってスクロールしても、一行目の「年月日」〜「支払方法」の見出しの項目は、画面上に常に残って表示されているため、随分使い勝手がよくなります。

　なおリストではなく一般の横見出しの表でも、例えばA列が見出しとすると、B列全体を選択し、メニューバーの「ウィンドウ」→「ウィンドウ枠の固定」を順にクリックすることにより、同じようにウィンドウ枠を固定できます。

　また縦横見出しの場合、例えば1行目もA列も見出しであれば、B2セルを選択し、メニューバーの「ウィンドウ」→「ウィンドウ枠の固定」を順にクリックすることにより、上部一行目と左部A列のウィンドウ枠を両方とも固定することができます。

　同じようにB3セルを選択し、メニューバーの「ウィンドウ」→「ウィンドウ枠の固定」を順にクリックする上部一行目と二行目と左部A列のウィンドウ枠を固定できます。いずれも簡単な操作で便利ですから、是非マスターしてください。

　なお「リストの並べ替え」については、「エクセル簡単テクニック後編」で説明しています。


＜おわりに＞

　「エクセルのオート機能　前編」では、文字の入力、表の作成を補助するオート機能について説明しており、まだ目を通されてないのであれば続けてご覧ください。

★　My Free-style PC サイト内関連ファイルのご案内

(平成19年1月1日　当初執筆）
ＰＣソフトコーナー カウンタ　　
My Free-style PC 総合カウンタ　

• My Free-style PC
• PCソフトコーナー