[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『集計方法を教えてください』（yukkey）
下期の受注が顧客名、商品名、金額でリストがあるのですが
同じ顧客からの下期の受注合計と３種類の商品別合計の
値を知りたいのですがどうすればよいのですか？
しかも商品はさらにライセンス、保守に分かれています。
データメニューの【フィルタ】、【集計】、【ピボットテーブル】などで、調べますが、
一番簡単な操作で、データメニューの【フィルタ】⇒【オートフィルタ】で説明します。
仮に下の表の例で、E2に =subtotal(9,d:d)と入力します。
次にA3:D3を選択し、データメニューの【フィルタ】⇒【オートフィルタ】をONにすると、
各標題にリスト選択ボタン▼が表示されます。
A B C D E
1 集計額
2 160,000←=subtotal(9,d:d)
3 顧客名 商品名 備考 金額
4 ○○ △▼ abc-12 ライセンス20,000
5 ○● ▼△ abb-01 保守 10,000
6 ●○ △△ abd-01 保守 10,000
7 ○● ▼△ abc-13 ライセンス20,000
8 ○● ▼△ abb-02 保守 10,000
9 ●○ △△ abd-02 保守 10,000
10 ○● ▼△ abc-12 ライセンス20,000
11 ○● ▼△ abb-01 保守 10,000
12 ○○ △▼ abb-01 ライセンス20,000
13 ○○ △▼ abb-01 保守 10,000
14 ○○ △▼ abd-01 ライセンス20,000
顧客名で○○ △▼を指定すると、次の表になり、表示されているデータの集計金額が求められます。
A B C D E
1 集計額
2 70,000
3 顧客名 商品名 備考 金額
4 ○○ △▼ abc-12 ライセンス20,000
12 ○○ △▼ abb-01 ライセンス20,000
13 ○○ △▼ abb-01 保守 10,000
14 ○○ △▼ abd-01 ライセンス20,000
更に備考のライセンスを選択すると、集計金額が求められます。
A B C D E
1 集計額
2 60,000
3 顧客名 商品名 備考 金額
4 ○○ △▼ abc-12 ライセンス20,000
12 ○○ △▼ abb-01 ライセンス20,000
14 ○○ △▼ abd-01 ライセンス20,000
元に戻すには指定した各フィルタリストの「すべて」を指定すれば、すべてのデータが表示されます。
要領が判れば、各顧客名を選択し、商品名で絞れば、求めたい金額を知ることが出来ます。
更にデータメニューの【集計】や【ピボットテーブル】などでも試してみて下さい。
（シニア？）
すごいです。感動しました！！
ちなみに=subtotal(9,d:d)の９ってなんですか？
あと、この集計結果を別シートに自動的にいれることは
可能ですか？
顧客名 商品A 商品B 商品C
○○ △▼ 集計額 集計額 集計額
○● ▼△ 集計額 集計額 集計額
=subtotal(9,d:d)はＤ列の合計を求めます。=subtotal(1,d:d)は平均を求めます。説明は次の通りです。
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 関数
次の表の顧客別各商品の合計金額を求めるには
Ｂ16セルに =SUM(IF($A$2:$A$12=$A16,IF($B$2:$B$12=B$15,$C$2:$C$12,0)))の式を入力します。
確定する時に、CtrlとShiftを押しながらEnterキーを押すと{}で囲んだ配列数式になります。
{=SUM(IF($A$2:$A$12=$A16,IF($B$2:$B$12=B$15,$C$2:$C$12,0)))}
Enterで確定すると、#value! とエラー値が返されます。
このようになった時は、ファンクションキー（Ｆ２）を押してから、Ctrl+Shift+Enterで確定して下さい。
この式をB17:B18にコピー、更にC16:D18にコピーすれば求まります。
A B C D
1 顧客名 商品名 金額
2 ●○ △△ 商品C 10,000
3 ●○ △△ 商品C 10,000
4 ●○ △△ 商品C 20,000
5 ○○ △▼ 商品A 20,000
6 ○○ △▼ 商品A 20,000
7 ○○ △▼ 商品A 10,000
8 ○○ △▼ 商品B 20,000
9 ○● ▼△ 商品B 10,000
10 ○● ▼△ 商品A 20,000
11 ○● ▼△ 商品B 10,000
12 ○● ▼△ 商品B 10,000
13
14
15 顧客名 商品A 商品B 商品C
16 ●○ △△ 0 0 40,000
17 ○○ △▼ 50,000 20,000 0
18 ○● ▼△ 20,000 30,000 0
以上の例で確認して出来れば、自作の表に応用して下さい。
（シニア？）
関数の件、わかりました。ありがとうございます。
顧客別商品の集計ですが、金額がすべて"0"になってしまします。
計算式の理解としては、「もしリストの顧客列の顧客が●○ △△で
リストの商品列が商品Aだったら、リストの金額をたしなさい」
ということですよね？そうなると、
集計表の商品A(C16)には=SUM(IF($B$2:$B$12=$B16,IF($C$2:$C$12=C$15,$D2:$D$12,0)))
商品B(D16)には=SUM(IF($B$2:$B$12=$B16,IF($C$2:$C$12=D$15,$D2:$D$12,0)))
商品C(E16)には=SUM(IF($B$2:$B$12=$B16,IF($C$2:$C$12=E$15,$D2:$D$12,0)))
でよろしいのでしょうか？
A列は空で、元の表はB1:D12であり、集計表の範囲がB15:E18になっていれば、
正しく入力されています。
元表の顧客名と集計表の顧客名は一致していますか？●○ △△の前後に空白文字が入っていませんか？
●○__△△、●○＿△△姓と名の空白が半角二文字空白と､全角一文字空白では別人になってしまいます。
もう一度確認して下さい。
（シニア？）
ようやくできました。ありがとうございました。
ただ、完成した式のセルをコピーすると中の式の数字も１つずつずれてしまします。
式だけのコピーをすると{}で囲んだ配列数式がコピーできないのですが
どうしたらいいですか？
C16の式が{=SUM(IF($B$2:$B$12=$B16,IF($C$2:$C$12=C$15,$D2:$D$12,0)))}となっていれば、
配列数式でコピー貼り付けされます。
コピーすれば相対的に変化するように設定してあります。
顧客名参照は$B16で列固定ですから、行は相対で変化します。
商品名参照はC$15で行固定ですから、列は相対で変化します。
もう一度完成するまで挑戦して下さい。
(シニア？)
不思議です。今日はできました。
エクセルってすごいですね。上司は簡単にいろんな数字を出せといいますが、周りにエクセルを使いこなせる人がいなく困っていました。意外にも早く解決し私の評価も上がったのでは？
ほんとうに助かりました。ありがとうございました。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.