[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『2つ以上の条件に合致したセルの数値を合計する』(ひでぼー)
数字のデータを条件別に集計したいのです。
担当者 契約月 金額
鈴木 08 100
鈴木 09 110
鈴木 08 120
佐藤 08 90
田中 09 80
佐藤 08 100
田中 08 120
佐藤 07 100
の表から下のように集計したい
鈴木 07 0
鈴木 08 220
鈴木 09 110
佐藤 07 100
佐藤 08 190
佐藤 09 0
田中 07 0
田中 08 120
田中 09 80
< 使用 Excel:Excel2016、使用 OS:Windows7 >
交通整理です。
集計イメージの1列目と2列目は、あらかじめ記載されているのですか? それとも、元イメージから作り出すのですか?
(β) 2016/10/18(火) 22:17
ピボットテーブルはどうか?
行ラベルに担当者と契約月、値に金額。 契約月のフィールドの設定のレイアウトと印刷タブのデータのないアイテムを表示するにチェック、ピボットテーブルを 右クリックのピボットテーブルオプションのレイアウトと書式タブの空白セルに表示する値に0、でどうか。
あとはピボットテーブルオプションの表示タブで従来のピボットテーブルレイアウトを使用する、 担当者のフィールドの設定のレイアウトと印刷タブでアイテムのラベルを繰り返す、など。
(ねむねむ) 2016/10/18(火) 22:43
To ねむねむさん
もし、集計イメージの1列目、2列目も作り出すという要件であれば、アップされたレイアウトから想像するに 各人のデータとして、全体で登場する 7,8,9 の各月を表示し、取り扱いのない月には 0 と表示させたいのかなと。
(β) 2016/10/19(水) 08:23
質問追加。
2列目の月は数字ですか? それとも、日付型の値で表示書式で mm にしてあるのですか?
(β) 2016/10/19(水) 08:26
βさん >全体で登場する 7,8,9 の各月を表示し、取り扱いのない月には 0 と表示させたい を行うのが >レイアウトと印刷タブのデータのないアイテムを表示するにチェック、ピボットテーブルを >右クリックのピボットテーブルオプションのレイアウトと書式タブの空白セルに表示する値に0、でどうか。 だが。
さらに各行に氏名を表示させるのが >アイテムのラベルを繰り返す になる。 (ねむねむ) 2016/10/19(水) 08:44
To ねむねむさん
ごめんなさい。コメントをほとんど、読んでいませんでした。
(β) 2016/10/19(水) 08:56
で、ピボットテーブルではどんな点が希望に沿わないのだろうか? (ねむねむ) 2016/10/19(水) 09:49
ねむねむさんの方式でよろしいのではないですか?
「など」とコメントしておられるように、さらに、小計をなくしたり総計をなくしたり、表形式の表示にしたり。 そうすると以下の結果になりますよ。
|[A] |[B] |[C] [1] |担当者|契約月|合計 / 金額 [2] |佐藤 | 7| 100 [3] |佐藤 | 8| 190 [4] |佐藤 | 9| 0 [5] |田中 | 7| 0 [6] |田中 | 8| 120 [7] |田中 | 9| 80 [8] |鈴木 | 7| 0 [9] |鈴木 | 8| 220 [10]|鈴木 | 9| 110
(β) 2016/10/19(水) 10:02
'処理対象シートはSheet1 Dim sht As Worksheet, c As Range, dic1, dic2, k1, k2 Set dic1 = CreateObject("Scripting.Dictionary") Set dic2 = CreateObject("Scripting.Dictionary") Sheets("Sheet1").Copy after:=Sheets("Sheet1") Set sht = ActiveSheet With sht.Sort .SortFields.Clear .SortFields.Add Key:=Range("B:B") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers .SetRange Range("A2:C" & Rows.Count) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With For Each c In sht.UsedRange.Columns("B").Cells If c.Offset(1).Value <> "" Then dic1(c.Offset(1).Value) = True dic2(c.Offset(1, -1).Value) = True End If Next c Set c = sht.Range("A2") sht.Range("A2:C" & Rows.Count).ClearContents For Each k2 In dic2.keys For Each k1 In dic1.keys c.Resize(, 3).Value = Array(k2, k1, "=SUMIFS(Sheet1!C:C,Sheet1!A:A,A" & c.Row & ",Sheet1!B:B,B" & c.Row & ")") Set c = c.Offset(1) Next k1 Next k2 End Sub (mm) 2016/10/19(水) 10:05
質問者さんからのレスがありませんが、やはりピボット利用を強く推奨します。
元データの変更、手打ちされるのか、どこかからどさっとコピペされるのかわかりませんが 「何もしなくても自動で」ということは、あきらめて(?)
・できあがったピボットのどこかを選択 ・ピボットツール選択リボンの分析タブのデータソースの変更 で、元ネタの領域を指定する
これだけで完了しますので。
仮にマクロで対応するとしても、最初のピボットでの作表までは操作で、好きな形に仕立て上げておいて、 元ネタが変更になったら以下のコードを走らせればOKです。
元ネタが Sheet1のA1から始まる、ピボットテーブルは SHeet2 に展開してある。Sheet2には、ピボットテーブルは これ1つしかないという前提で。
Sub Sample()
Sheets("Sheet2").PivotTables(1).ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion.Address(External:=True, ReferenceStyle:=xlR1C1))
End Sub
(β) 2016/10/20(木) 10:45
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.