[[20161018212833]] 『2つ以上の条件に合致したセルの数値を合計する』(ひでぼー) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『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年8月を「16.08」と入力しています。) 契約金額
集計欄で担当者の月毎の契約金額の合計を自動計算できないかと考えています。
良いお知恵を拝借できればと存じます。
よろしくお願いします。
(ひでぼー) 2016/10/19(水) 09:10

 で、ピボットテーブルではどんな点が希望に沿わないのだろうか?
(ねむねむ) 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


Sub main()
    '処理対象シートは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.