いろいろな関数 6

今回は「串刺し算」と[SUBTOTAL] 関数です。

「串刺し算」

串刺し算やデータベース化するにしても、同一様式(フォーム)にして使う必要があります。

1.同じ様式で集計する方法

以下、下段の表3Sheetをコピーして、使用した場合で説明します。

  1. ExcelのシートSheet1,2,3,4を選択、グループ化しておいて「3支店の売上表」様式の「4月」「5月」「6月」& 「集計」表を[数値未入力]で作成し、シート名も付けておきます。
  2. グループを解除して「各月」にデータを記入。Sheet「集計」を開き、個別に結果を表示したい場合は、 [B4]〜[D6]内の任意のセルを選択して[fx]ボタンを押します。
  3. 数学/三角からSUM関数を選び数式パレットの[数値1]欄に、シート参照とセル参照で記入します。
  4. 始めにシート「4月」をクリック⇒次にShiftキーを押しながらシート「6月」をクリックすると [数値1]欄に「 4月:6月 」と表示されます。
  5. 串刺し算する、例えば[B4]セルをクリックして[OK]ボタンです。
  6. [Enter]キーを押します。 今まで、専らセル参照で記入してきましたが、関数を10〜20と扱う内に数式バーでいくらでも記入、修正ができるようになると思います。
    その場合数式バーに直接、例えば「 =SUM('4月:6月'!B5)とか=SUM('4月:6月'!C4) 」と記入します。

    (記号では、合計算で[4月から6月]分ををアポストロフィで囲み(半角英数文字列の場合アポストロフィは不用らしい)、[!]で3枚のシートの[D5]セルについて演算しなさいの意味らしいです。MSヘルプその他いくら調べても演算子と思われる[']と[!]の解説が見つかりませんでしたので「らしい」で表現しました。)

    場合場合で「セル参照」に頼らず「直記入」した方が早い場合もあります。併用すると効率が上がります。
  7. この入力を終えたセルの右下隅の「フィルハンドル」を掴んで、横にオートフィル、縦にオートフィルすると、 各セルごとに回答が得られ串刺し算が完成します。

2.店舗ごとに売上高を一気に集計する方法

例えば、茨城店計、栃木店計、群馬店計といった場合です。

  1. ExcelのシートSheet1,2、3を選択、グループ化しておいて「某社3支店の売上表」様式の「四月」「5月」「6月」表を [数値未入力]で作成し、シート名も付けておきます。
  2. グループを解除して「各月」に売上額を記入。Sheet「集計」を開き、結果を入力するセル (茨城店行の「E4」セル)を選択[fx]ボタンを押します。
  3. 数学/三角からSUM関数を選び数式パレットの[数値1]欄に、シート参照とセル参照で記入します。
  4. 始めにシート「4月」をクリック⇒次に「5月」を跨いでShiftキーを押しながらシート「6月」をクリックすると [数値1]欄に「 4月:6月 」と表示されます。
  5. セル茨城店の「商品A金額」をクリック次いでShiftキーを押しながら「商品C金額」をクリックし、 串刺し算するセル範囲を指定します。
  6. [OK]ボタンを押します。
  7. [Enter]キーを押します。「42347」の結果が得られました。
  8. 書式は「 =SUM('4月:6月'!B4:D4) 」です。
  9. 栃木店についても、同様です。また、茨城店の[B4:D4]を[B5:D5]に替え
    表示セルに「 =SUM('4月:6月'!B5:D5) 」の式で貼り付けます。
  10. 群馬についても、同様です。どちらを選ばれても構いません。
  11. 若し、仮に集計シートの「茨城の合計欄」に「串刺し集計を行ったとしますと、 「F4」の右下隅の「フィルハンドル」を掴んで、下にドラグアンドドロップ(オートフィル機能)すると、「栃木店」「群馬店」も即座にだせます。 集計シートの場合は、Σ(オートサム)で集計できるので、その必要はなさそうですが
    (注:このシリーズでは[Σ]を使わず[fx]ボタンからの操作で説明しております。)

3.商品ごとに売上高を一気に集計する方法

例えば、商品A計、商品B計、商品C計といった場合です。

  1. ExcelのシートSheet1,2、3を選択、グループ化しておいて「某社3支店の売上表」様式の「四月」「5月」「6月」表を [数値未入力]で作成し、シート名も付けておきます。(上と同様です)
  2. グループを解除して「各月」に売上額を記入。Sheet「集計」を開き、結果を入力するセル 商品A列の合計欄「B7」セル)を選択[fx]ボタンを押します。
  3. 数学/三角からSUM関数を選び数式パレットの[数値1]欄に、シート参照とセル参照で記入します。
  4. 始めにシート「4月」をクリック⇒次に「5月」を跨いでShiftキーを押しながらシート「6月」をクリックすると [数値1]欄に「 4月:6月 」と表示されます。
  5. 次にセル「商品A」列の「茨城店金額」をクリック次いでShiftキーを押しながら「群馬店金額」をクリックし、串刺し算するセル範囲を指定します。
  6. [OK]ボタンを押します
  7. [Enter]キーを押します。「50631」の結果が得られました。
  8. 書式は「 =SUM('4月:6月'!B4:B6) 」です。
  9. 「商品B」「商品C」に付いても同様です。
  10. また、「B7」セルの右下隅の「フィルハンドル」を掴んで、右にコピー(オートフィル機能)しても回答が得られます。

4.第一四半期全店舗総売上高を一気に集計する方法

  1. ExcelのシートSheet1,2、3を選択、グループ化しておいて「某社3支店の売上表」様式の 「4月」「5月」「6月」表を[数値未入力]で作成し、シート名も付けておきます。
  2. グループを解除して「各月」に売上額を記入します。(ここまでは、上と同じです。)
  3. 結果を入力するセルを選択します。下表で、Sheetを「4月」「5月」「6月」を使う場合の「総合計」のセルは「E7」です。
  4. [fx]ボタンを押します。
  5. 数学/三角からSUM関数を選び数式パレットの[数値1]欄に、シート参照とセル参照で記入します。
  6. 始めにシート「4月」をクリック⇒次に「5月」を跨いでShiftキーを押しながらシート「6月」をクリックすると[数値1]欄に「 4月:6月 」と表示されます。これで、全セルが開いたことになります。
  7. 次にセル茨城店の「商品A金額」セルをクリック次いでShiftキーを押しながら群馬店の 「商品C金額」セルをクリックします。表の金額欄全体が選択されました。
  8. [OK]ボタンを押します。
  9. [Enter]キーを押します。「E7」セル欄に「142221」と表示されました。
  10. 書式は「 =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件のデータを入力する。
リストをデータベースとして利用する場合、列をフィールド、列の見出しを、フィールド名、行をレコードと言います。

変化を見るには「オートカルク」機能があります。
また、簡単な集計機能もあります。
オートカルクとは (私はオートカルテと解釈しています。)
数値の入力された複数セルを選択することで、その計算結果をステータスバーに表示させる機能。
ステータスバー(コマンドバー)の中央右寄り2番目の窪みに「合計(SUM)」「平均(AVERAGE)」「データの個数(COUNT)」「数値の個数(COUNTA)」「最大値(MAX)」「最小値(MIN)」の6っつを表示します。なを、初期設定は合計になっています。

ステータスバーのオートカルク エリアを右クリックすればショートカットメニューから表示内容を指定することができます。

下表をコピーして、新しいEXCELに貼り付けてください。
オートフィルタを使用します。

  1. 表(データを抽出したい)の中の適当なセルをクリックします。
  2. メニューバーの[データ(D)]をクリックします。
  3. ドロップダウン・メニューの [フィルタ(F)]のサブメニュー[オートフィルタ(F)]をクリックします。
    これで、各項目(リストと言います。)に「オートフィルタ矢印▼」が表示されます。
    これをクリックし、リストから選択すると、選択されたアイテムと同じデータのみを表示させることができます。
  4. 例えば、性別▼印をクリックして、男性を選択します。
    男性5名が抽出されました。
  5. 例えば国語の合計を出します。Σボタンで結構ですから、点数下に合計を出して下さい。
  6. 今度は性別▼印をクリックして、女性を抽出します。驚いたことには、もう既に合計ができているのです。
  7. その合計セルをクリックして、数式バーを見て下さい。指定しないにもかかわらず[SUBTOTAL]関数が使われているのです。
  8. 複数条件でデータを表示することもできます、
    オートフィルタ表示の状態で、さらに別のフイールドでオートフィルタの条件を選択すると、両方の条件を満たすレコードだけが表示されます。
  9. メニューの「トップテン」では上位10位、下位10位の指定や%のような表示もできます。
  10. また、「オプション」を指定すると、細かい条件を指定して、データを表示することができます。
  11. 表を元通りに表示するには、「青色の▼印」を押し(すべて)を選択します。
  12. フィルタの解除:データ⇒フィルタ⇒オートフィルタを再度選択すれば解除されます。
  13. 表として連続してある行は、全部抽出対象になります。
  14. 今回は[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

 

トップへ戻る     いろいろな関数目次へ戻る  
いろいろな関数5−3へ戻る     いろいろな関数6−2へ進む

inserted by FC2 system