[[20181019221431]] 『excel 集計方法を教えてください。』(daisy) ページの最後に飛ぶ

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

 

『excel 集計方法を教えてください。』(daisy)

excelの集計方法を教えてください。

A列からC列まで
アメリカ 10/1 1000
イギリス 10/1 1000
フランス 10/1 1000
スペイン 10/1 1000
アメリカ 10/2 1000
フランス 10/2 1000
アメリカ 10/3 1000
イギリス 10/3 1000

このようなデータを
アメリカ 10/1、10/2、10/3 3000
イギリス 10/1、10/3  2000
フランス 10/1、10/2  2000
スペイン 10/1  1000

Aでグループ
B列には、日付を連結
C列には、合計を出力したいのです。

何か良い方法はありませんでしょうか。
初心者のため、簡単にできる方法を教えていただけると助かります。
よろしくお願いいたします。

< 使用 Excel:Excel2016、使用 OS:Windows10 >


回答ではありません。

B列とC列は逆のほうが見やすいのではありませんか。

(マナ) 2018/10/19(金) 22:57


 >簡単にできる方法を

 サロンのVBAの方に質問していたみたいだけど、
 マクロは、初心者向きではないです。
 あからさまに初心者を表に出して、マクロ作ってくれってことですよね。

(BJ) 2018/10/19(金) 23:39


一般操作のほうでも質問していましたが
わたしは、関数は得意ではないので
 Option Explicit

 Sub test()
    Dim dic As Object
    Dim w()
    Dim r As Range
    Dim c As Range
    Dim 国 As String
    Dim 日付 As String
    Dim n As Long

    Set dic = CreateObject("scripting.dictionary")

    Set r = Range("a1").CurrentRegion.Columns(1).Cells
    ReDim w(1 To r.Count, 1 To 3)

    For Each c In r
        国 = c.Value
        日付 = Format(c.Offset(, 1).Value, "m/d")

        If Not dic.exists(国) Then
            n = dic.Count + 1
            w(n, 1) = 国
            w(n, 2) = "'" & 日付
             dic(国) = n
        Else
            n = dic(国)
            w(n, 2) = w(n, 2) & "、" & 日付
        End If
        w(n, 3) = w(n, 3) + c.Offset(, 2).Value
    Next

    With Range("F1")
        .CurrentRegion.ClearContents
        .Resize(dic.Count, 3).Value = w
    End With

 End Sub

(マナ) 2018/10/20(土) 00:22


すみませんでした。
そのようなつもりではなかったのですが。
Sumifやcountif vlookで色々試していたのです。
それでも上手く行かなかったため、質問したのです。

ありがとうございました。
(daisy) 2018/10/20(土) 07:01


vbaができないので、ピポットテーブルなどでなにか方法はありませんでしょうか。
ご存知の方がいらっしゃいましたら、教えてください。
関数でできましたらうれしいです。
(daisy) 2018/10/20(土) 10:31

 マナさんから「B列とC列は逆のほうが見やすいのではないか」との提案がありましたよね?

 私もそう思うので、まず、その提案に対する見解を示して頂けませんか?

(半平太) 2018/10/20(土) 10:38


すみませんでした。
確かにその列のほうがよいかと思いました。
日付も多くても3つなので、あまり気にせずにいました。
本当にすみませんでした。
(daisy) 2018/10/20(土) 11:28

 >日付も多くても3つなので、

 少ないので、ほっとします。

 以下、I〜K列を作業列にする案。

 (1) F1セル =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$9)/(MATCH($A$1:$A$9,$A$1:$A$9,0)=ROW($A$1:$A$9)),ROW(A1))),"")
 (2) G1セル =IF(F1="","",SUMIF(A:A,F1,C:C))
 (3) H1セル =CONCAT(I1:K1)

 (4) I1セル =IF($F1="","",IFERROR(TEXT(AGGREGATE(15,6,$B$1:$B$9/($A$1:$A$9=$F1),COLUMN(A1)),"m/d "),""))
   I1セルの右にコピー(K列まで)

 (5) F1:K1を選択して、下方へフィルコピー

(半平太) 2018/10/20(土) 12:09


できました!
本当にありがとうございました。

(daisy) 2018/10/20(土) 12:58


 半平太さんの式で(3)に関しては、CONCATが使える環境ならTEXTJOINも使える筈なので、H列は作業列なしで以下でも出来ます。

 H1 =TEXTJOIN("、",1,INDEX(TEXT((A$1:A$100=F1)*B$1:B$100,"m/d;;"),0),"")

 区切り文字がスペースで良いなら、CONCATでも出来ます。

 H1 =TRIM(CONCAT(INDEX(TEXT((A$1:A$100=F1)*B$1:B$100,"m/d ;;"),0)))

 文末のスペースを除去しなくて良いならTRIMは必要ありません。

(sy) 2018/10/20(土) 13:10


本当にありがとうございました。

今日付が10/1となっているのですが、
日付でない場合もあるので、そのままの値を連結するようにするには、
どのようにすればよいでしょうか。
区切り文字はスペースで大丈夫です。
よろしくお願いいたします。
(daisy) 2018/10/20(土) 13:25


 >日付でない場合もあるので、そのままの値を連結するようにするには、 

 具体的にどんなデータなんですか?

 1. 数値(今のままだと日付に変化するのでまずい)
 2. 文字列(簡単そう)
 3. エラー(これは難しい・・多分)

(半平太) 2018/10/20(土) 15:06


メッセージをありがとうございます。

データは、正式には2018.10.20または、301020の二つの書式です。

2か所からデータをもらうのですが、
一方は、2018.10.20という書式で日付が統一されており
もう一方は、301020書式です。

私が必要なのは、10月20日ということが分かればいいのです。
1020でも10/20でも10.20でも2018.10.20でも301020でも何でもよいので、
ひとつの国のグループで日付を連結させて合計額を知りたいのです。

(daisy) 2018/10/20(土) 15:23


 >データは、正式には2018.10.20または、301020の二つの書式です。 
 >1020でも10/20でも10.20でも2018.10.20でも301020でも何でもよいので、

 1.データはすべて文字型ですね?

 2.なら、ドット(.)を削除して、右から4文字切り取ればいいんじゃないですか?

 syさんの式をこんな風に修正したらどうでしょう?

 H1セル =TEXTJOIN("、",1,INDEX(REPT(RIGHT(SUBSTITUTE(B$1:B$100,".",""),4),A$1:A$100=F1),0))

(半平太) 2018/10/20(土) 16:26


できました!!!
何度もありがとうございました。
sumif やcountifを組み合わせて考えてばかりいましたが、
こんな方法があるのですね。
本当にご教授感謝いたします。
貴重なお時間をありがとうございました。
(daisy) 2018/10/20(土) 16:48

すみません。
F1に書かれた関数の意味がどうしても理解できません。
結果は、正しいです。
教えていただけないでしょうか。
(daisy) 2018/10/21(日) 22:52

AGGREGATEの使用例をみても、どうしても理解できません。
(daisy) 2018/10/22(月) 06:17

 分解して考えてみてください。

 (1) E1セル =ROW()
 (2) F1セル =MATCH($A$1:$A$9,$A$1:$A$9,)
 (3) G1セル =E1=F1
 (4) H1セル =E1/G1
 (5) I1セル =AGGREGATE(15,6,$H$1:$H$8,E1)
 (6) J1セル =INDEX(A:A,I1)
 (7) K1セル =IFERROR(J1,"")

 1行目の数式を下にコピー

  行 ____A____ _____B_____ __C__ _D_ _E_ _F_ __G__ ___H___ __I__ ____J____ ____K____
   1 アメリカ  2018.10.24  1000        1   1 TRUE        1     1 アメリカ  アメリカ 
   2 イギリス  2018.10.20  1000        2   2 TRUE        2     2 イギリス  イギリス 
   3 アメリカ      301020  1000        3   1 FALSE #DIV/0!     4 スペイン  スペイン 
   4 スペイン      301012  1000        4   4 TRUE        4     6 フランス  フランス 
   5 アメリカ      301022  1000        5   1 FALSE #DIV/0! #NUM! #NUM!              
   6 フランス      301022  1000        6   6 TRUE        6 #NUM! #NUM!              
   7 アメリカ  2018.10.20  1000        7   1 FALSE #DIV/0! #NUM! #NUM!              
   8 イギリス  2018.10.31  1000        8   2 FALSE #DIV/0! #NUM! #NUM!              

(半平太) 2018/10/22(月) 16:14


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.