[[20171031212501]]　『実数を算出したい』（石川）　ページの最後に飛ぶ

『実数を算出したい』（石川）

実数の出し方について教えてください。
以下のようにSheet1、Sheet2があります。
Sheet1は実際には以下の列があります。
いずれも4行目から値が入っています。
FY・FZ
GI・GJ
GS・GT
HC・HD
HM・HN
HW・HX
IG・IH
IQ・IR
JA・JB
JK・JL

Sheet1

  A・・・・・FY        FZ
4 2017/4/5   鈴木一郎  Ｉ県   
5 2017/4/26  大谷正平  Ｔ県
6 2017/5/5   山田花子  Ｇ県
7 2017/5/20  大谷正平  Ｔ県
8 2017/6/4   鈴木一郎  Ｉ県
9 2017/6/29  金田正和  О県
・
・
・

Sheet1のA列と上記の列を参照させて、月ごとの実数を出したいと思っています。
以下のB列には、4月であれば、実際には「2017/4/1」というデータが入っています。その形で3月まであります。
5行目の県名は値が入っています。
この月と県名をSheet1から参照させて、以下のように実数（延べではなく）を出したいのです。例えば、4月の中で、「鈴木一郎　Ｉ県」は何度か出てきますが、これを延べ数ではなく、実数で出したいと考えています。
Sheet1のA列の日付は、実際には各月ごと20日以上あります。

Sheet2

  B    C    D    E    F
5      Ｉ県 Ｔ県 Ｇ県 О県
6 4月  1    1    0    0
7 5月  0    1    1    0    
8 6月  1    0    0    1
・
・
・
分かりにくい説明ですみません。前回も私の説明不足でご迷惑をおかけしました。今回は、できるだけ詳しく書いたつもりです。
どうぞよろしくお願いします。

それから、Sheet1のA列から月ごとの件数を求めたいです。
4月は何件、5月は何件・・・

以上、どうぞよろしくお願いします。

＜使用 Excel：Excel2013、使用 OS：Windows8 ＞

 ＞Sheet1のA列の日付は、実際には各月ごと20日以上あります。

 やたら横に長い表ですけど、Sheet1は最終的に何行ぐらいになるんですか？
「県」は何種類あるんですか？　47？

 Sheet1に作業列を使ってもよければ

 ■Sheet1
 どの列が空いているのかわかりませんが、GA列を作業列とします。

 GA4 =IF(A4="","",IF(SUMPRODUCT((MONTH($A$4:A4)=MONTH(A4))*($FY$4:FY4=FY4)*($FZ$4:FZ4=FZ4))=1,MONTH(A4),""))

 下へコピー

 ■Sheet2
 Sheet1は500行で収まるものとして

 C6 =COUNTIFS(Sheet1!$FZ$4:$FZ$500,C$5,Sheet1!$GA$4:$GA$500,MONTH($B6))

 右と下へコピー

 ＞それから、Sheet1のA列から月ごとの件数を求めたいです。

 これはA列だけを見ればいいってことですか？
 それをSheet2のどこに出すんですか？

 参考まで
（笑） 2017/10/31(火) 23:08

 Sub main()
    Dim dic As Object, r As Range, c As Range, d As Range, k As Variant
    Set dic = CreateObject("scripting.dictionary")
    Sheets("Sheet2").Cells.ClearContents
    For Each k In Array("FY:FY", "GI:GI", "GS:GS", "HC:HC", "HM:HM", "HW:HW", "IG:IG", "IQ:IQ", "JA:JA", "JK:JK")
        If WorksheetFunction.CountA(Sheets("Sheet1").Range(k)) Then
            For Each c In Sheets("Sheet1").Range(k).SpecialCells(xlCellTypeConstants)
                With Sheets("Sheet2")
                For Each d In c
                    Set r = .Rows(1).Find(d.Offset(, 1).Value)
                    If r Is Nothing Then .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Value = d.Offset(, 1).Value
                    If dic(Month(d.EntireRow.Cells(1).Value) & vbLf & d.Value & vbLf & d.Offset(, 1).Value) = False Then
                        .Cells(Month(d.EntireRow.Cells(1).Value), .Rows(1).Find(d.Offset(, 1).Value).Column).Value = _
                        Val(.Cells(Month(d.EntireRow.Cells(1).Value), .Rows(1).Find(d.Offset(, 1).Value).Column).Value) + 1
                        .Cells(Month(d.EntireRow.Cells(1).Value), 1) = Month(d.EntireRow.Cells(1).Value) & "月"
                        dic(Month(d.EntireRow.Cells(1).Value) & vbLf & d.Value & vbLf & d.Offset(, 1).Value) = True
                    End If
                Next d
                End With
            Next c
        End If
    Next k
End Sub
（mm） 2017/11/01(水) 10:44

 GI・GJ以下の列にも「名前」と「県名」が入ってるってことですか？
 質問と何の関係があるのかと思ってました。

 関数の回答はスキップしてください。
 すみません。
（笑） 2017/11/01(水) 12:42

集計元シートに作業列を1列追加して、=DATE(YEAR(A1),MONTH(A1),1) のように式を埋める事で、集計先の年月日と同じ値にできます。こうしておけば、COUNTIFS関数で集計できるかと思いますよ。（10ペアに分かれているので、10個のCOUNTIFSを行って、これを合計する必要がありますが、足し算するだけなので、簡単ですよね）
（???） 2017/11/01(水) 14:42

皆さん、有難うございます
質問への回答など遅くなってすみません

笑さんへの回答

 ＞やたら横に長い表ですけど、Sheet1は最終的に何行ぐらいになるんですか？
　 「県」は何種類あるんですか？　47？

Shee1は単純に365日×10年と考えていました（実際には土日や祝日等が入ります）
県名は８種類しかありません

 ＞これはA列だけを見ればいいってことですか？
　 それをSheet2のどこに出すんですか？

４月が何日ということでＡ列だけみて出したいです
これをSheet2のC6,C8,C10,C12,C14,C16,C18,C20,C22,C24,C26,C28です
最初に説明させていただいたC列に1列挿入してこの列をつくりたいと考えています

Sheet2

  B    C    D    E    F    G
5           Ｉ県 Ｔ県 Ｇ県 О県
6 4月  25   1    1    0    0
7 5月  24   0    1    1    0    
8 6月  26   1    0    0    1
・
・
・

mmさんへの質問
これはマクロでだすということですね
10列の列番号が入っているなというぐらいでよく理解できずすいません
ありがとうございます

???さんへの質問
COUNTIFSを10個つかう場合、累計になりませんか
実数を出す場合もこの関数でやれるでしょうか
10列の中に、例えば4月の方で名前と県名が同じ方は何回出てこようと１としかカウントしないようにしたいのです

皆さん、どうも有難うございます

（石川） 2017/11/02(木) 07:16

実数って、重複除外した数の意味だったのですね（一般的にはそんな意味はないと思いますが）。そうなると、数式より、マクロを使う方が良いと思います。（数式だと、もうひとつ中間の表を用意して、日付と人と県に一致するものがあれば、何件あっても1とする数式を埋めておき、これの合計値を出力先から参照する、とか？）

マクロのほうが簡単にできそうですが、その場合は、まずご自身でmmさんが作って下さったコードを手がかりに、考えてみてください。マクロを組んだことが無いから誰か作って…、ではなく、今回作るために調べるのです。コーディングとは、時間のかかる作業なのだ、ということをもっと実感してください。

そして、この集計も、2列*10に分けずに2列（日付も入れると3列）にまとまってさえいれば、DB扱いすればかなり楽に求められそう。元の表のレイアウトが悪いと思います。しかし、現状のシートはレイアウトを変えられないだろうから、必要な情報だけ別のシートに転記とレイアウト変更をしてから、そっちを元に集計をかけるのも良さそう。
（???） 2017/11/02(木) 09:33

???さん、有難うございます
mmさんから教えてもらいましたマクロを自分なりに勉強してみます
またお力添えをお願いいたします
（石川） 2017/11/02(木) 12:51

すでに適切な回答がありますが、念のため。
　
まず、こうした場合は適切な（標準的な）表にすることが第一です。
10年間などということは考えないことです。
2年目の日付列はどこですか？1年目の日付を兼用などできないじゃないですか。
365日ならべておいて、飛び飛びに入力するんですか？
　
横に並べて良いことは余りないです。
3列を、縦に、何年でも並べていくべきですね。
　
集計のためのExcelの色々な道具をまず会得することです。
マクロは後回しです。失礼ながらもっと先でするべきことです。
まずはピボットテーブルを調べたらいかがですか？
　
「ピボットテーブルで日付をグループ化」
https://www.becoolusers.com/excel/pivottable-group.html
などが、日付データを月ごとにグループ化する方法を説明しています。
　
月、所在地ごとの「氏名」の「データ個数」では支障ありますか？
例を見る限り、それで間に合いそうな感じですが。
　
まずは、Excelに備わった色々な機能を使いこなすことを目標にされたらどうですか？

（γ） 2017/11/03(金) 09:07

コメント返信：

[ 一覧(最新更新順) ]

[[20171031212501]] 『実数を算出したい』（石川） ページの最後に飛ぶ

[[20171031212501]]　『実数を算出したい』（石川）　ページの最後に飛ぶ