[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『関数で月別の使用量の集計を行いたい』（やまめ）
初めて質問させていただきます。
現在顧客別に一月分の電気の使用量の集計を行っており、関数を使用して集計を試みていますがうまくいきません。
恐れ入りますがご教授お願いいたします。
A B C D E F
1 顧客番号 顧客名 日付 時間コード 使用量（全量） 使用量（部分）
2 0111111111 A社 20170301 1 35
3 0111111111 A社 20170301 2 33
表が見づらくて申し訳ございません。
上記の表はA列から顧客番号、顧客名、日付（yyyymmdd形式）、時間コード（24時間を30分ごとに区切り番号を振っています）、使用量（全量）、使用量（部分）という順番に並んでいます。
使用量（部分）は自社で発電を行っており一部のみ電気を使用している顧客です。
行いたいことですが顧客番号ごとに時間コード別に使用量の集計を行いたいのです。
ですが条件があり
・土日の使用量は平日と別に時間コード別に集計を行う
・使用量部分に顧客については使用量部分のみ集計を行う
というものです。
私で現在行ったことは
1：日付横に列を挿入しをyyyy/mm/dd形式に直しさらに列を挿入、曜日を入れる（集計しやすいように曜日コードをweekday関数で入れました。
2：使用量部分の顧客にはif関数で1という数字を立てるようにしました
集計は時間コードごとの合計なのでsumifsを使用するのだろうと思い
=sumfs(使用量（全量）,曜日コード,7以外,曜日コード,6以外,使用量部分の顧客に1を立てた部分,使用量部分を除く,時間コード,1）
という式を作成しましたがvalueになりました。
集計の方法について教えていただきたいです。
なにとぞよろしくお願いいたします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
（γ） 2017/05/07(日) 11:34
集計結果ですが下記のようにできたらいいなと考えています。
顧客番号 0111111111 0123123111
1 123 205
2 110
3 105
顧客番号の下の数字は時刻コードになります。
まず平日分の使用量を集計しその下に土日分の使用量を時刻コードごとに集計できたらいいなあと思います。
よろしくお願いいたします。
（やまめ） 2017/05/07(日) 11:40
（γ） 2017/05/07(日) 11:54
このようなレイアウトにしたいです。
よろしくお願いいたします。
（やまめ） 2017/05/07(日) 12:12
こんなふうにピボットで作成可能と思われる。 顧客番号を縦にならべたほうが良いのかもしれない。 もちろん計算式でもできると思いますが、 集計のための道具があるのですから、それを使えばよいと思う。 各月だけでなく、上下半期合計、年間合計など、色々な用途に耐えうると思う。
【元データ】
A列 B C D E F G H
1 顧客番号 顧客名 日付 時間コード 使用量(全量) 使用量(部分)合計 区分
2 A100 A社 2017/3/1 1 10 10 平日
3 A100 A社 2017/3/1 2 11 11 平日
4 A100 A社 2017/3/1 3 12 12 平日
5 A100 A社 2017/3/1 4 13 13 平日
6 A100 A社 2017/3/2 1 14 14 平日
7 A100 A社 2017/3/2 2 15 15 平日
8 A100 A社 2017/3/2 3 16 16 平日
9 A100 A社 2017/3/2 4 17 17 平日
10 A100 A社 2017/3/4 1 20 20 土日
11 A100 A社 2017/3/4 2 21 21 土日
12 A100 A社 2017/3/4 3 22 22 土日
13 A100 A社 2017/3/4 4 23 23 土日
14 C100 C社 2017/3/5 1 30 30 土日
15 C100 C社 2017/3/5 2 31 31 土日
16 C100 C社 2017/3/5 3 32 32 土日
17 C100 C社 2017/3/5 4 33 33 土日
18 C100 C社 2017/3/6 1 40 40 平日
19 C100 C社 2017/3/6 2 41 41 平日
20 C100 C社 2017/3/6 3 42 42 平日
21 C100 C社 2017/3/6 4 43 43 平日
G2:= E2 + F2
H2:=IF(WEEKDAY(C2,2)>5,"土日","平日")
【ピボットテーブル】
A列 B C D
1 顧客番号
2 区分 時間コード A100 C100
3 平日 1 24 40
4 2 26 41
5 3 28 42
6 4 30 43
7 土日 1 20 30
8 2 21 31
9 3 22 32
10 4 23 33
11 総計 194 292
（γ） 2017/05/07(日) 13:15
あくまでコメントにあげたレイアウトで集計したいのです。
せっかく考えてくださったのに申し訳ございません。
（やまめ） 2017/05/07(日) 13:31
現在例示されている元データでは、A社しか書かれていませんし、
F列 使用量（部分） が空欄
例示されている元データと、例示されている集計データも数値が対応していないので、
「どういうパターンが有るか」「どう集計したいか」が、回答者には伝わりにくいです。
文章でキチンと伝えられないような場合は、
元データ例は、ある程度の分量を書いて、
「パターンをある程度網羅する」「元→集計の数値が対応する例」で書いたほうが良いと思います。
（tata） 2017/05/07(日) 13:47
A列 B C D E F G
顧客番号 顧客名 日付 日付シリアル値 時刻コード 使用量全量 使用量部分
t0411411111111102200000 A 20170301 42795 1 92
t0411411111111102200000 A 20170301 42795 2 87
t0411411111111102200000 A 20170301 42795 3 85
t0411411111111102200000 A 20170301 42795 4 85
t0411411111111102200000 A 20170301 42795 5 84
t0411411111111102200000 A 20170301 42795 6 87
t0411411111111102200000 A 20170302 42796 1 87
t0411411111111102200000 A 20170302 42796 2 84
t0411411111111102200000 A 20170302 42796 3 81
t0411411111111102200000 A 20170302 42796 4 82
t0411411111111102200000 A 20170302 42796 5 82
t0411411111111102200000 A 20170302 42796 6 79
t0411333333331102200000 B 20170301 42795 1 86
t0411333333331102200000 B 20170301 42795 2 85
t0411333333331102200000 B 20170301 42795 3 86
t0411333333331102200000 B 20170301 42795 4 87
t0411333333331102200000 B 20170301 42795 5 87
t0411333333331102200000 B 20170301 42795 6 86
t0411333333331102200000 B 20170302 42796 1 57
t0411333333331102200000 B 20170302 42796 2 55
t0411333333331102200000 B 20170302 42796 3 56
t0411333333331102200000 B 20170302 42796 4 55
t0411333333331102200000 B 20170302 42796 5 55
t0411333333331102200000 B 20170302 42796 6 56
t0424444453331102200000 C 20170301 42795 1 11 0
t0424444453331102200000 C 20170301 42795 2 10 0
t0424444453331102200000 C 20170301 42795 3 8 0
t0424444453331102200000 C 20170301 42795 4 7 0
t0424444453331102200000 C 20170301 42795 5 7 0
t0424444453331102200000 C 20170301 42795 6 6 0
t0424444453331102200000 C 20170302 42796 1 24 0
t0424444453331102200000 C 20170302 42796 2 17 0
t0424444453331102200000 C 20170302 42796 3 13 0
t0424444453331102200000 C 20170302 42796 4 14 0
t0424444453331102200000 C 20170302 42796 5 13 0
t0424444453331102200000 C 20170302 42796 6 14 0
A列が顧客番号です（頭にTが入っていますが本当は入っていません。桁数が多いと文字化けするのでわざと入れて文字列にしています）
Yさんに返信いただいた、
1.一つの顧客に全量と部分の両方が現れることがあるのですか？
＞使用量には部分で使用している場合、使用量全量と使用量部分に数値がそれぞれ表示されます。
部分しか使用していない場合はその使用量部分を集計したいのです。
2.貴方の集計表で全量と部分はどのように扱われているのですか？
＞集計表ではすべてまとめています。
全量のみであれば全量を集計、部分を使用しているのであれば部分を集計したいのです。
集計しようとしている表は下記です。
顧客番号 t0411411111111102200000 t0411333333331102200000 t0424444453331102200000
平日時刻コード
1 178 143 0
2 171 140 0
3 166 142 0
4 167 142 0
5 166 142 0
6 166 142 0
7 169 0
8 169 0
9
10
11
12
13
14
15
16
17
18
takaさんのコメントで書かれていた
＞「どういうパターンが有るか」「どう集計したいか」
ですがどう集計したいかは上記のような表に平日と土日をわけて集計したいのです。
この表を集計したい意図ですが、
集計後顧客ごとに使用料金を出したいからです。
朝・昼・夜と土日で時間帯と曜日ごとに単価が異なるため顧客番号ごとに時刻コードで集計を行いたかったのです。
・使用量部分が空欄の顧客は全量の列で集計
・使用量部分に数値が入っている顧客は部分の列で集計
このような説明で大丈夫でしょうか？
不足があれば教えてください。
今回の件で人に説明しなれていないことがわかりました。
長文で申し訳ございませんがよろしくお願いいたします。
（やまめ） 2017/05/07(日) 15:46
外出から戻りました。
>Q1: 1.一つの顧客に全量と部分の両方が現れることがあるのですか？ >A1: 使用量には部分で使用している場合、使用量全量と使用量部分に数値がそれぞれ表示されます。 > 部分しか使用していない場合はその使用量部分を集計したいのです。
それぞれの有無で場合を分けると３つのケースがありますね。
使用量全量 使用量部分
case1 10 0
case2 20 30
case3 0 40
「使用量部分がなければ、使用量全量を集計対象とする 使用量部分があれば、 使用量部分を集計対象とする」 という説明がありましたが、 そうすると case2 の 20 は無視してよいのですね？ 10,30,40 だけを集計するということですね？
（Case2の場合は、2件のデータが作成されるということなら、 そのように説明いただかないとわかりません。 追加説明いただいたものは、部分使用があるケースがないので、 判断できません。）
もしそうならば、 私の提示したシート例にある「合計」項目が ・今は、単純な合計にしていますが、 ・上記の「」で囲った部分を式に表したものに変更 すれば、計算は可能だと思います。
私は、それでは不適当のように思いますし、 全量と部分使用では単位料金が違うだろうから、 両方を別々に集計しなければならないような気がしていますが、 部外者ですから分かりません。
頑張って下さい。
（γ） 2017/05/07(日) 17:20
γさん(Yさんではなくガンマさん)もおっしゃっているように、
＞1.一つの顧客に全量と部分の両方が現れることがあるのですか？
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |顧客番号 |顧客名|日付 |日付シリアル値|時刻コード|使用量全量|使用量部分 |t0424444453331102200000|C |20170302| 42796| 6| 14| 21 ↑こんな風に、F列、G列に同時に数字が入る事があるのか、
それとも、
|t0424444453331102200000|C |20170302| 42796| 4| 0| 21 ↑「部分使用の顧客」ならば必ずG列にのみ数値が入り、F列は空、or 0 なのか、
|t0424444453331102200000|C |20170302| 42796| 3| 13| 0 |t0424444453331102200000|C |20170302| 42796| 5| 17| 25 ↑こんな風に、C社でも、部分使用が有る場合、無い場合が入り交じるのか、 入り交じった場合の計算はどうしたいのか、
等、どういうパターンが存在するのかわかりにくいです。
私が口をさしはさんだ、
＞ある程度の分量を書いて、 「パターンをある程度網羅する」
というのは、単純に量を増やすという話ではなく、
「いろんな場合」を書いておくということです。
例えば、
＞使用量には部分で使用している場合、使用量全量と使用量部分に数値がそれぞれ表示されます。
＞部分しか使用していない場合はその使用量部分を集計したいのです。
・使用量には部分で使用している場合
・部分では全く使用していない場合
・部分しか使用していない場合
が有るのでしょうか？
ならば、それらのパターンを含む例を例示する方が、回答がつきやすく、速い解決となるかと思います。
（今のやまめさんの再提示だと、「使用量部分」の列は、「空白」か「0」しか入っていないため、「よくわからない」のです）
（tata） 2017/05/07(日) 17:21
（tata） 2017/05/07(日) 17:50
tataさんのおっしゃることわかりました。顧客1件あたりの量を増やすのではなくパターンということですね。
使用量部分について説明が足りず申し訳ございません。
tataさんの作成した表をお借りして説明させていただきます。
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |顧客番号 |顧客名|日付 |日付シリアル値|時刻コード|使用量全量|使用量部分 |t0424444453331102200000|C |20170302| 42796| 6| 51| 21
使用量全量というのはすべてこちらから送っている電気の量
使用量部分については使用量全量から自社でまかなわれている部分、他社とあわせて契約している場合他社分を差し引いた量
になります。
そのため（説明不足でしたが）使用量全量＞使用量部分になります。
使用量部分になにかしら数字が入っている顧客の場合、使用量全量（F列）で自社でまかなっている量（他社から送っている電気の量）+こちらから送っている電気が表記されています。
（自社でまかなっている量（他社分）は同じ数字になります。上記の表で自社でまかなっている量が30の場合51-30＝21がこちらから送られた電気になりますのでこのG列を集計したいです）
また集計後の単価については顧客ごとに違います。
ですが朝・昼・夜（土日）といった時間帯（何時から何時までが朝、何時から何時が昼といったもの）はどの顧客も共通しています。
γさんのピボットテーブル部分が関数で行えればよいのですが・・・可能なのでしょうか
（やまめ） 2017/05/07(日) 18:10
γさんの提示されたように、シート例にある「合計」項目を 「使用量部分がなければ、使用量全量を集計対象とする 使用量部分があれば、 使用量部分を集計対象とする」 ようにF列、G列から自動算出する数式にして、「部分有無判定後使用量」とし、
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H]
[1] |顧客番号 |顧客名 |日付 |日付シリアル値|時刻コード|使用量全量|使用量部分|部分有無判定後使用量
[2] |t04114 |A |20170301| 42795| 1| 92| | 92
[3] |t04114 |A |20170301| 42795| 2| 87| | 87
[4] |t04113 |B |20170302| 42796| 5| 55| | 55
[5] |t04113 |B |20170302| 42796| 6| 56| | 56
[6] |t04244 |C |20170301| 42795| 1| 11| 0| 0
[7] |t04244 |C |20170302| 42796| 6| 51| 21| 21
ピボットテーブルで以下の集計形状
[10]|合計 / 部分有無判定後使用量|列ラベル| [11]|行ラベル |t04114 |t04113 |t04244 [12]| 1| 92| | 0 [13]| 2| 87| | [14]| 5| | 55| [15]| 6| | 56| 21 ではダメなんです？
…横からしゃしゃり出てあんまり引っ掻き回すのも何ですが…
（tata） 2017/05/07(日) 18:17
集計形状、
・1行目：顧客番号
・A列：時刻コード
・"平日"
で集計したいなら、
B2 = SUMIFS(元データ[部分有無判定後使用量],元データ[顧客番号],B$1,元データ[時刻コード],$A2,元データ[区分],"平日")
でどうでしょう。
…月ごとに、みたいな話もあるので、ピボットテーブル使ったほうが楽だと思うんだけどなぁ。
（tata） 2017/05/07(日) 18:56
＞1：日付横に…シリアル値…weekday関数
＞2：使用量部分の顧客にはif関数で1という数字を立てる
以下のような元データと仮定して、
元データ シート
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I]
[1] |顧客番号|顧客名|日付 |日付シリアル値|曜日|時刻コード|使用量(全量)|使用量(部分)|部分フラグ
[2] |A100 |A社 |20170301| 42795 | 3 | 1| 10| |
[3] |A100 |A社 |20170301| 42795 | 3 | 2| 11| |
[4] |A100 |A社 |20170302| 42796 | 4 | 2| 15| |
[5] |A100 |A社 |20170302| 42796 | 4 | 4| 17| |
[6] |A100 |A社 |20170304| 42798 | 6 | 3| 22| |
[7] |A100 |A社 |20170304| 42798 | 6 | 4| 23| |
[8] |C100 |C社 |20170305| 42799 | 7 | 1| 60| 30| 1
[9] |C100 |C社 |20170305| 42799 | 7 | 2| 61| 31| 1
[10]|C100 |C社 |20170305| 42799 | 7 | 4| 63| 33| 1
[11]|C100 |C社 |20170306| 42800 | 1 | 1| 64| 40| 1
[12]|C100 |C社 |20170306| 42800 | 1 | 3| 66| 42| 1
[13]|C100 |C社 |20170306| 42800 | 1 | 4| 67| 43| 1
以下のように集計したいとする。
集計後 シート
|[A] |[B] |[C]
[1]|顧客番号|A100|C100
[2]|平日 | |
[3]| 1| 10| 40
[4]| 2| 26| 0
[5]| 3| 0| 42
[6]| 4| 17| 43
[7]| 5| 0| 0
[8]|… | |
[9]| 48| 0| 0
=sumfs(使用量（全量）,曜日コード,7以外,曜日コード,6以外,使用量部分の顧客に1を立てた部分,使用量部分を除く,時間コード,1）
という提示で「こういうことをやりたいのだろうと思われることを推測」しながら、「あくまでコメントにあげたレイアウトで集計したいのです。」ということで、新たな作業列も作らず、同様の立式(なるべく元の式を活かす)をするのであれば、
B3 = SUMIFS(元テーブル[使用量(全量)],元テーブル[顧客番号],集計後!B$1,元テーブル[曜日],"<>7",元テーブル[曜日],"<>6",元テーブル[部分フラグ],"<>1",元テーブル[時刻コード],集計後!$A3)+SUMIFS(元テーブル[使用量(部分)],元テーブル[顧客番号],集計後!B$1,元テーブル[曜日],"<>7",元テーブル[曜日],"<>6",元テーブル[部分フラグ],1,元テーブル[時刻コード],集計後!$A3)
つまり、
B3 = SUMIFS(元データ!$G$2:$G$13,元データ!$A$2:$A$13,集計後!B$1,元データ!$E$2:$E$13,"<>7",元データ!$E$2:$E$13,"<>6",元データ!$I$2:$I$13,"<>1",元データ!$F$2:$F$13,集計後!$A3)+SUMIFS(元データ!$H$2:$H$13,元データ!$A$2:$A$13,集計後!B$1,元データ!$E$2:$E$13,"<>7",元データ!$E$2:$E$13,"<>6",元データ!$I$2:$I$13,1,元データ!$F$2:$F$13,集計後!$A3) で出来るとは思いますが、ムダに長い式になり、ちょっとした変更でもメンテナンス性が悪いと思うので、あんまりおすすめしません。
曜日の判定を「7以外and6以外」ではなく、「5以下」にするとか、
あんまり詳しくないのでわからないですがSUMPRODUCT関数やand or {}等用いれば
もう少し式をスッキリさせることも出来るかもしれないですが、いずれにせよ理解しにくい式になると思います。
（tata） 2017/05/08(月) 19:49
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.