関数
今回は「串刺し算」と[SUBTOTAL] 関数です。
「串刺し算」
串刺し算やデータベース化するにしても、同一様式(フォーム)にして使う必要があります。
1.同じ様式で集計する方法
以下、下段の表3Sheetをコピーして、使用した場合で説明します。
- ExcelのシートSheet1,2,3,4を選択、グループ化しておいて「3支店の売上表」様式の「4月売上」「5月売上」「6月売上」&
「集計」表を[数値未入力]で作成し、シート名も付けておきます。
下記コピーの場合は「数値既入力」になってしまいますが、ご自分で作成する場合は、未入力がよろしいかと思います。
未入力の場合は「グループを解除して「各月」にデータを記入することになります。
(シートのグループの解除法)
(シート上を右クリックして、WinXPでは、「作業グループの解除」です。)
- グループを解除して「各月」にデータを記入します。
これから、串刺し算に入ります。
- Sheetの見出し「集計」をクリックします。
- 左上部の空白セル「ここでは、B4セル」(集計の回答を求めたいセル)をクリックします。
- 標準ツールバーのΣボタンをクリック(WinXPでは、右側の▼をクリックして、後合計をクリック)します。
- 画面下に目を移し、シートの見出しの[4月売上]をクリックします。
- 次いで、Shiftキーを押しながら、[6月売上]をクリックします。(4月売上から6月売上までグループ化しました。)
- シート見出し4月売上の画面になっていて、数値で埋まっていますが、構わずに、ここでは、[B4セル]をクリックします
- [Enter]キーを押して完了です。
- 後は、フィルハンドル機能で、下にドラッグまた項目数だけ、右にドラッグ、更に項目毎に下にドラッグすれば、
集計表は完成します。
今まで、専らセル参照で記入してきましたが、関数を10〜20と扱う内に数式バーでいくらでも記入、修正ができるようになると思います。
その場合数式バーに直接、例えば「 =SUM('4月売上:6月売上'!B5)とか=SUM('4月売上:6月売上'!C4) 」と記入します。
(記号では、合計算で[4月売上から6月売上]分ををアポストロフィで囲み(半角英数文字列の場合アポストロフィは不用らしい)、[!]で3枚のシートの[D5]セルについて演算しなさいの意味らしいです。MSヘルプその他いくら調べても演算子と思われる[']と[!]の解説が見つかりませんでしたので「らしい」で表現しました。)
場合場合で「セル参照」に頼らず「直記入」した方が早い場合もあります。併用すると効率が上がります。
この入力を終えたセルの右下隅の「フィルハンドル」を掴んで、横にオートフィル、縦にオートフィルすると、
各セルごとに回答が得られ串刺し算が完成します。
2.店舗ごとに売上高を一気に集計する方法
例えば、茨城店計、栃木店計、群馬店計といった場合です。
- ExcelのシートSheet1,2、3を選択、グループ化しておいて「某社3支店の売上表」様式の「四月売上」「5月売上」「6月売上」表を
[数値未入力](ここでは、既入力になってます。)で作成し、シート名も付けておきます。
- グループを解除して「各月」に売上額を記入。Sheet「集計」を開き、結果を入力するセル
(茨城店行の「E4」セル)を選択[fx]ボタンを押します。(今度は、関数パレット使用です。)
- 数学/三角からSUM関数を選び数式パレットの[数値1]欄に、シート参照とセル参照で記入します。
- 始めにシート「4月売上」をクリック⇒次に「5月売上」を跨いでShiftキーを押しながらシート「6月売上」をクリックすると
[数値1]欄に「 4月売上:6月売上 」と表示されます。
- セル茨城店の「商品A金額」をクリック次いでShiftキーを押しながら「商品C金額」をクリックし、
串刺し算するセル範囲を指定します。
- [OK]ボタンを押します。
- [Enter]キーを押します。「42347」の結果が得られました。
- 書式は「 =SUM('4月売上:6月売上'!B4:D4) 」です。
- 栃木店についても、同様です。また、茨城店の[B4:D4]を[B5:D5]に替え
表示セルに「 =SUM('4月売上:6月売上'!B5:D5) 」の式で貼り付けます。
- 群馬についても、同様です。どちらを選ばれても構いません。
- 若し、仮に集計シートの「茨城の合計欄」に「串刺し集計を行ったとしますと、
「F4」の右下隅の「フィルハンドル」を掴んで、下にドラグアンドドロップ(オートフィル機能)すると、「栃木店」「群馬店」も即座にだせます。
集計シートの場合は、Σ(オートサム)で集計できるので、その必要はなさそうですが
(注:ここでは、[Σ]を使わず[fx]ボタンからの操作で説明しております。)
3.商品ごとに売上高を一気に集計する方法
例えば、商品A計、商品B計、商品C計といった場合です。
- ExcelのシートSheet1,2、3を選択、グループ化しておいて「某社3支店の売上表」様式の「四月売上」「5月売上」「6月売上」表を
[数値未入力]で作成し、シート名も付けておきます。(上と同様です)
- グループを解除して「各月」に売上額を記入。Sheet「集計」を開き、結果を入力するセル
商品A列の合計欄「B7」セル)を選択[fx]ボタンを押します。
- 数学/三角からSUM関数を選び数式パレットの[数値1]欄に、シート参照とセル参照で記入します。
- 始めにシート「4月売上」をクリック⇒次に「5月売上」を跨いでShiftキーを押しながらシート「6月売上」をクリックすると
[数値1]欄に「 4月売上:6月売上 」と表示されます。
- 次にセル「商品A」列の「茨城店金額」をクリック次いでShiftキーを押しながら「群馬店金額」をクリックし、串刺し算するセル範囲を指定します。
- [OK]ボタンを押します。
- [Enter]キーを押します。「50631」の結果が得られました。
- 書式は「 =SUM('4月売上:6月売上'!B4:B6) 」です。
- 「商品B」「商品C」に付いても同様です。
- また、「B7」セルの右下隅の「フィルハンドル」を掴んで、右にコピー(オートフィル機能)しても回答が得られます。
4.第一四半期全店舗総売上高を一気に集計する方法
- ExcelのシートSheet1,2、3を選択、グループ化しておいて「某社3支店の売上表」様式の
「4月売上」「5月売上」「6月売上」表を[数値未入力]で作成し、シート名も付けておきます。
- グループを解除して「各月」に売上額を記入します。(ここまでは、上と同じです。)
- 結果を入力するセルを選択します。下表で、Sheetを
「4月売上」「5月売上」「6月売上」を使う場合の「総合計」のセルは「E7」です。
- [fx]ボタンを押します。
- 数学/三角からSUM関数を選び数式パレットの[数値1]欄に、シート参照とセル参照で記入します。
- 始めにシート「4月売上」をクリック⇒次に「5月売上」を跨いでShiftキーを押しながらシート
「6月売上」をクリックすると[数値1]欄に「 4月:6月 」と表示されます。これで、全セルが開いたことになります。
- 次にセル茨城店の「商品A金額」セルをクリック次いでShiftキーを押しながら群馬店の
「商品C金額」セルをクリックします。表の金額欄全体が選択されました。
- [OK]ボタンを押します。
- [Enter]キーを押します。「E7」セル欄に「142221」と表示されました。
- 書式は「 =SUM('4月売上:6月売上'!B4:D6) 」です。
下表をコピーしてSheet1名称替え「4月売上」のセル[A1]に貼り付けてご使用ください。
いろはチエン商店 第1四半期売上表 |
4月売上 |
|
|
|
|
|
商品A |
商品B |
商品C |
合計 |
茨城店 |
1111 |
2000 |
5000 |
|
栃木店 |
2222 |
3000 |
6000 |
|
群馬店 |
3333 |
4000 |
7000 |
|
合計 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
下表をコピーしてSheet2名称替え「5月」のセル[A1]に貼り付けご使用ください。
いろはチエン商店 第1四半期売上表 |
5月売上 |
|
|
|
|
|
商品A |
商品B |
商品C |
合計 |
茨城店 |
9876 |
1590 |
7410 |
|
栃木店 |
8765 |
7530 |
8520 |
|
群馬店 |
7654 |
2580 |
9630 |
|
合計 |
|
|
|
|
|
|
|
|
|
下表をコピーしてSheet3名称替え「6月」のセル[A1]に貼り付けご使用ください。
いろはチエン商店 第1四半期売上表 |
6月売上 |
|
|
|
|
|
商品A |
商品B |
商品C |
合計 |
茨城店 |
3240 |
4530 |
7590 |
|
栃木店 |
6570 |
7860 |
4560 |
|
群馬店 |
7860 |
1260 |
1530 |
|
合計 |
|
|
|
|
|
|
|
|
|
SUBTOTAL関数使用の[引数*]とは |
|
|
|
|
引数:9 (SUM関数使用=合計) |
17670 |
13650 |
13680 |
|
引数:4 (MAX関数使用=最大値) |
7860 |
7860 |
7590 |
|
引数:1 (AVERAGE関数使用=アベ) |
5890 |
4550 |
4560 |
|
引数:5 (MIN関数使用=最小値) |
3240 |
1260 |
1530 |
|
引数:2 (COUNT関数使用=数値の個数) |
3 |
3 |
3 |
|
引数:3 (COUNTA関数使用=空白除く個数) |
3 |
3 |
3 |
|
引数:6 (PRODUCT関数使用=積の計算) |
21286800 |
(左は範囲指定 [B4:B5] の積にしました。) |
引数の番号によって「集計方法」を
選択します。 11個有るうち主な
7個を挙げました。 |
|
|
|
|
|
|
|
|
|
下表をコピーしてSheet4の名称替え「集計」のセル[A1]に貼り付けご使用ください。
いろはチエン商店 第1四半期売上表 |
|
集計 |
下のカラー部分は「シート4月〜6月」までの「串刺し」積算です。 |
|
商品A |
商品B |
商品C |
合計 |
|
茨城店 |
14227 |
8120 |
20000 |
|
|
栃木店 |
17557 |
18390 |
19080 |
|
|
群馬店 |
18847 |
7840 |
18160 |
|
|
合計 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
引数:9 (SUM関数使用) |
50631 |
34350 |
57240 |
|
|
引数:4 (MAX関数使用) |
18847 |
18390 |
20000 |
|
|
引数:1 (AVERAGE関数使用) |
16877 |
11450 |
19080 |
|
|
|
|
|
|
142221 |
|
|
|
|
|
|
|
[SUBTOTAL] 関数
[SUBTOTAL]関数は、合計や平均などの集計方法を1〜11までの数字で指定して集計を行う関数です。
また、[SUBTOTAL]関数は、リスト(データベース)から集計値を求める関数です。リストでオートフィルターを実行する場合はこのSUBTOTAL関数を使う。ということになっています。
書式:「 SUBTOTAL(集計方法, 範囲1, 範囲2,...) 」です。
方法を指定して集計します。
集計方法
リストの集計に使用する関数を、1 〜 11 の番号で指定します。
集計方法 関数
1 AVERAGE 関数
2 COUNT 関数
3 COUNTA 関数
4 MAX 関数
5 MIN 関数
6 PRODUCT 関数
7 STDEV 関数
8 STDEVP 関数
9 SUM 関数
10 VAR 関数
11 VARP 関数
になっています。主要関数の用例に就いては、上記「シート6月」をご覧下さい」
範囲1, 範囲2,
集計するリストの範囲を 1 〜 29 個まで指定します。
解説
範囲 内に他の集計値が挿入されている場合、ネストされている集計値は、計算の重複を防ぐために無視されます。表示されているデータのみを対象に集計します。
オートフィルタでリストを抽出した結果として非表示になっている行は無視されます。このため、抽出されたリストに表示されているデータだけを集計することができます。
参照先に 3-D 参照が含まれている場合は、エラー値 #VALUE! が返されます。
使用例
小計のSUBTOTAL(9,C2:C4) では、SUM 関数[9]を使用してセル範囲 C2:C4 の集計値を返します。
年間合計でも[SUBTOTAL]関数を使い、「 =SUBTOTAL(9,C2:C17) 」とします。
期別[A1]セル 支店名[B1] 売上高[C1]
第1四半期 東京支店 31,230,000
第1四半期 大阪支店 22,340,000
第1四半期 福岡支店 13,450,000
小計 67,020,000 =SUBTOTAL(9,C2:C4)
第2四半期 東京支店 41,350,000
第2四半期 大阪支店 32,460,000
第2四半期 福岡支店 23,570,000
小計 97,380,000 =SUBTOTAL(9,C6:C8)
第3四半期 東京支店 31,470,000
第3四半期 大阪支店 22,580,000
第3四半期 福岡支店 13,690,000
小計 67,740,000 =SUBTOTAL(9,C10:C12)
第4四半期 東京支店 40,460,000
第4四半期 大阪支店 30,570,000
第4四半期 福岡支店 20,680,000
小計 91,710,000 =SUBTOTAL(9,C14:C16)
年間合計 323,850,000 =SUBTOTAL(9,C2:C17)
下図は、オートフィルタを設定し、支店名▼で「福岡支店」を抽出し、四半期の平均を求めた図です。
用語:
- オートフィルタとは
- 列(フィールド)ごとに簡単な条件を指定して条件に合う行(レコード)のみを表示させることができます。。
- データベースとは
- 有る決まりに基づいてデータを蓄積したもの。
「リスト」を対象として、データを検索、並べ替え、集計などのデータベース処理ができることになっています。
- リストとは
- 同じ種類のデータを列ごとにまとめて入力した表。
1行に1件のデータを入力する。
リストをデータベースとして利用する場合、列をフィールド、列の見出しを、フィールド名、行をレコードと言います。
- 変化を見るには「オートカルク」機能があります。
また、簡単な集計機能もあります。
- オートカルクとは
- (WinXPになって、なくなり、代わりにΣボタンの中に組み込まれました。)
- 数値の入力された複数セルを選択することで、その計算結果をステータスバーに表示させる機能。
- ステータスバー(コマンドバー)の中央右寄り2番目の窪みに「合計(SUM)」「平均(AVERAGE)」「データの個数(COUNT)」「数値の個数(COUNTA)」「最大値(MAX)」「最小値(MIN)」の6っつを表示します。なを、初期設定は合計になっています。
ステータスバーのオートカルク エリアを右クリックすればショートカットメニューから表示内容を指定することができます。
下表をコピーして、新しいEXCELに貼り付けてください。
オートフィルタを使用します。
- 表(データを抽出したい)の中の適当なセルをクリックします。
- メニューバーの[データ(D)]をクリックします。
- ドロップダウン・メニューの [フィルタ(F)]のサブメニュー[オートフィルタ(F)]をクリックします。
これで、各項目(リストと言います。)に「オートフィルタ矢印▼」が表示されます。
これをクリックし、リストから選択すると、選択されたアイテムと同じデータのみを表示させることができます。
- 例えば、性別▼印をクリックして、男性を選択します。
男性5名が抽出されました。
- 例えば国語の合計を出します。Σボタンで結構ですから、点数下に合計を出して下さい。
- 今度は性別▼印をクリックして、女性を抽出します。驚いたことには、もう既に合計ができているのです。
- その合計セルをクリックして、数式バーを見て下さい。指定しないにもかかわらず[SUBTOTAL]関数が使われているのです。
- 複数条件でデータを表示することもできます、
オートフィルタ表示の状態で、さらに別のフイールドでオートフィルタの条件を選択すると、両方の条件を満たすレコードだけが表示されます。
- メニューの「トップテン」では上位10位、下位10位の指定や%のような表示もできます。
- また、「オプション」を指定すると、細かい条件を指定して、データを表示することができます。
- 表を元通りに表示するには、「青色の▼印」を押し(すべて)を選択します。
- フィルタの解除:データ⇒フィルタ⇒オートフィルタを再度選択すれば解除されます。
- 表として連続してある行は、全部抽出対象になります。
- 今回は[SUBTOTAL]関数と「オートフィルタ」の関係の説明です。
氏名 |
性別 |
国語 |
数学 |
英語 |
合計点 |
山田太郎 |
男性 |
80 |
90 |
80 |
250 |
川田花子 |
女性 |
75 |
70 |
70 |
215 |
草田一郎 |
男性 |
60 |
85 |
65 |
210 |
木田一美 |
女性 |
50 |
80 |
80 |
210 |
上田春子 |
女性 |
65 |
75 |
80 |
220 |
中田夏男 |
男性 |
60 |
55 |
95 |
210 |
下田秋子 |
女性 |
65 |
70 |
65 |
200 |
天野和雄 |
男性 |
65 |
70 |
65 |
200 |
地脇智子 |
女性 |
60 |
60 |
60 |
180 |
人見賢治 |
男性 |
65 |
65 |
70 |
200 |
|
|
|
|
|
|
トップへ戻る