Excelデータがうまく作成できず困っています【急いでいます】

Question

閲覧ありがとうございます。当方、最近事務職に異動になった者です。
Excelデータを作成・提出するよう指示を受けましたが、なにぶんにもエクセル初心者の為
なかなかうまく行かず困っています。

下記の元データがあります
　　A　　　　　　B　　　　C　　　　D　　　　E　　　　　F　　　　　　G　　　　　H
１ 施設No.　   請求月     氏名　　入金日　　入金額　H26/入金回数　H26/入金額　H26/最終入金日
2　0062710   H26/04    須藤   H26/09/25  1,500		
3   0062710   H26/05　 須藤　H26/11/05  1,500		
4　0062710   H26/06　 須藤　H26/11/05  1,500		
5   0062710   H26/08    須藤　H27/05/08  1,500		
6   0062710   H26/08    須藤　H27/06/23  1,500	
7   0062710   H26/08　 阿部　H27/08/20  1,500		
8   0062710   H26/08 　阿部　H27/08/20  1,500		
9   0062710   H26/08    阿部　H28/05/10  1,500		
10 0062710   H26/08    阿部　H28/06/24  1,500		
11 0062710   H26/08 　阿部　H28/06/24  1,500		
		
	
提出データ
　　A　　　B　　　　　C　　　　　　　　　D　　　　　　　　E　　　　　　　F
１施設No.　氏名　　H26/入金回数　　H26/入金金額　　H26/最終入金日　H27/入金回数･････
H28年11月末までの各年度のデータが横に表示される様にしたいです。
その後、28年度末の分までのデータの追加がされます。

同じ施設Noに退居･入居のに伴い2人の名前がある時もあり、その施設は当然VLOOKUPでは抽出できません。その上、年度ごとの入金回数、入金金額、その年度の最終入金日も抽出しなくてはいけません。

①　一旦、データとなるシートに入金回数・入金金額を抽出出来たのですが入金回数抽出で、同じ日　　　に2回以上あると1回とカウントされてしまい困っています。式にはSUMPRODAUCT関数を使い　　　ました。
②　年度毎の入金回数・入金金額を取りあえず出せたので、その式を流用してその年度の最終入金日を
　　抽出できないかアレコレやってみたのですがうまくいきません。
③　取りあえず、抽出した回数と入金額を提出データに表示させようとしたのですがうまく行きませ　　　ん･･･。人によって表示されないところがあります。INDEXとMATCH関数を使用しました。

　　１）全部で8,000人のデータがあります。元データの各年度の入金回数・金額・最終入金日の上　　　　　と下の行は空白行が多いです。その為読み込みエラーが出るのでしょうか？

　　２）提出シートは途中、集計の為に行を挟んであります。
　　　　その為に読み込みエラーが出るのでしょうか？
　　　　集計行を挟んだ下の行が表示されなくなってしまったりします。

　以上、大変申し訳ないのですが、ご教示いただきたくお願いいたします。　
　サイトでそれらしい式を探してきたりしてやっとここまで来たのですが
　もうこれ以上はお手上げ状態です。
　よろしくお願いいたします。

tatsu99 · Accepted Answer

No6です。以下のマクロを標準モジュールに登録してください。マクロ「集計表作成」を実行するとsheet3に提出用データが作成されます。（sheet3は空のシートを用意しておくこと） ---------------------------------------------------------------------- Option Explicit Public Const シート名1 As String = "Sheet1" '元データのシート名 Public Const シート名2 As String = "Sheet3" '提出データのシート名 Public Const 開始年度 As Long = 26 '提出データの開始年度（平成の年度）(現在 H26) Public Const 集計年度数 As Long = 3 '提出データの集計対象の年度の数(現在 3年度分)(H26,H27,H28) Public Sub 集計表作成() Dim sh1, sh2 As Worksheet Dim row1 As Long 'sheet1の行番号 Dim row2 As Long 'sheet2の行番号 Dim col2 As Long 'sheet2の列番号 Dim maxrow1 As Long 'sheet1の最大行番号 Dim maxrow2 As Long 'sheet2の最大行番号 Dim dicT As Object '連想配列 Dim key As String Dim yyyy As Long Dim mm As Long Dim i As Long Dim nendo As Long '相対年度（H26年度を1とする） Dim errorFlag As Boolean Dim errormsg As String errorFlag = False Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義 Set sh1 = Worksheets(シート名1) Set sh2 = Worksheets(シート名2) sh2.Cells.Clear 'Sheet2クリア sh2.Cells(1, 1).Value = "施設No." sh2.Cells(1, 2).Value = "氏名" For i = 1 To 集計年度数 sh2.Cells(1, i * 3).Value = "H" & 開始年度 + i - 1 & "/入金回数" sh2.Cells(1, i * 3 + 1).Value = "H" & 開始年度 + i - 1 & "/入金金額" sh2.Cells(1, i * 3 + 2).Value = "H" & 開始年度 + i - 1 & "/最終入金日" Next maxrow2 = 1 maxrow1 = sh1.Cells(Rows.Count, 1).End(xlUp).row ' 最終行を求める '2行～最終行まで繰り返す For row1 = 2 To maxrow1 '空白行はスキップする If sh1.Cells(row1, 1).Value = "" Then GoTo CONTINUE99 '施設＋氏名でキーを作成 key = sh1.Cells(row1, 1).Value & "|" & sh1.Cells(row1, 3).Value If dicT.exists(key) Then '当該キーが既存のキーなら、そのSheet2用行数を取得 row2 = dicT(key) Else '新規のキーなら、Sheet2の行数をカウントアップし、施設、氏名を作成 maxrow2 = maxrow2 + 1 dicT(key) = maxrow2 row2 = maxrow2 sh2.Cells(row2, 1).Value = sh1.Cells(row1, 1).Value sh2.Cells(row2, 2).Value = sh1.Cells(row1, 3).Value End If '入金日の年、月を取得 yyyy = Year(sh1.Cells(row1, 4).Value) mm = Month(sh1.Cells(row1, 4).Value) 'H26を1とする相対年度を取得 nendo = yyyy - 1989 - 開始年度 + 2 '1月～3月は前年度扱いとする If mm >= 1 And mm <= 3 Then nendo = nendo - 1 End If If nendo < 1 Or nendo > 集計年度数 Then 'H26年度～H28年度以外はスキップする(1度のみ表示) If errorFlag = False Then errormsg = row1 & "行施設|氏名=" & key & " 処理対象外入金日=" & sh1.Cells(row1, 4).Text & vbLf errormsg = errormsg & "このデータをスキップします。このメッセージは１回のみ表示されます。" MsgBox (errormsg) errorFlag = True End If GoTo CONTINUE99 End If col2 = nendo * 3 '入金回数 sh2.Cells(row2, col2).Value = sh2.Cells(row2, col2).Value + 1 '入金額 sh2.Cells(row2, col2 + 1).Value = sh2.Cells(row2, col2 + 1).Value + sh1.Cells(row1, 5).Value sh2.Cells(row2, col2 + 1).NumberFormatLocal = "#,##0" '最終入金日 If sh1.Cells(row1, 4).Value > sh2.Cells(row2, col2 + 2).Value Then sh2.Cells(row2, col2 + 2).Value = sh1.Cells(row1, 4).Value sh2.Cells(row2, col2 + 2).NumberFormatLocal = "gee/mm/dd" End If CONTINUE99: Next End Sub -------------------------------------------------------------- 尚、あなたのほうでこのマクロを改造する場合は、以下の行を修正してください。１）Public Const シート名1 As String = "Sheet1" '元データのシート名元データのシート名を○○にする場合は、"Sheet1"を"○○"に変えてください。２）Public Const シート名2 As String = "Sheet3" '提出データのシート名提出用データのシート名を変える場合は、"Sheet3"を"▲▲"のように変えてください。３）Public Const 開始年度 As Long = 26 '提出データの開始年度（平成の年度）(現在 H26) もし、来年になって、集計開始年度をH27から開始する場合は、この数字を27に変えてください。４）Public Const 集計年度数 As Long = 3 '提出データの集計対象の年度の数(現在 3年度分)(H26,H27,H28) もし、来年になって、4年度分（H26,H27,H28,H29）にする場合は、これを４にしてください。その場合、開始年度は26のままで構いません。不明点があれば、補足してください。

tatsu99 · Answer

マクロで提出データを作成しても良いならマクロの提供は可能です。その場合は、その旨、補足ください。
その際、幾つか質問がありますので、補足をお願いいたします。
１）元データのD列の入金日付は、セルの書式設定のユーザー定義　"[$-411]ge/m/d"　を使用していると理解していますが
それであっていますか。(添付の図を参照）
２）H26/4/1からH27/3/31の入金が提出データの"H26"の欄に該当するということでよいですか。
３）H26/3/31以前のデータはありますか。あるとすれば、何件程度ありますか。
(大量にあれば、エラーを表示せずにスキップします。原則、ないなら、エラーを表示してスキップします）

tom04 · Answer

何度もごめんなさい。

お判りだと思いますが、No.4で記載した余談の部分は

＞E～G列は
ではなく
＞F～H列の間違いでした。m(_ _)m

tom04 · Answer

続けてお邪魔します。

＞①　施設番号と名前の重複チェックは白い部分が出てこず連番で最終行まで行ってしまいます。
とは作業列1のコトでしょうか？
ん～～～
数式は前回通りになっているでしょうか？
行の絶対参照「$」マークは前回の数式通りちゃんと入っていますか？
ない場合、補足のようになる可能性があります。
今一度確認してみてください。

＞②　年度振り分けのところは年度はH26/4月～H2７/3月までで1年カウントなので、!月～3月の部分が
　　翌年に反映されてしまいます。
について要するに「年度」で表示したいわけですね？前回の数式は単に1～12月までの「平成」の年になります。

作業列2（J2の数式）の数式を
=IF(A2="","",TEXT(EDATE(D2,-3),"e")*1)
としてみてください。
おそらく今年の4月1日～来年の3月31日までは「28」と表示されるはずです。

※　余談ですが、別シートに表示させるのであれば元データのE～G列は不要のような気がします。

※　＞最初のシート１に2行足すところから躓いてしまいました。
この件に関しては敢えて触れませんが、解決済みですよね？

とりあえずはこれで試してみてください。m(_ _)m

tom04 · Answer

No.2です。

細かい検証をしていませんでした。
Sheet2のE2セルの数式を
=IF($A2="","",IF(C2>0,MAX(IF((Sheet1!$A$1:$A$10000=$A2)*(Sheet1!$C$1:$C$10000=$B2)*(Sheet1!$J$1:$J$10000=C$1),Sheet1!$D$1:$D$10000)),""))
に変更してください。
配列数式ですので、やはりCtrl＋Shift＋Enterで確定です。

おそらく他のセルは大丈夫だと思います。
どうも失礼しました。m(_ _)m

tom04 · Answer

こんばんは！

無理やりやってみました。
↓の画像のように元データはSheet1にあり、Sheet2に表示するとします。
Sheet1のD列にはシリアル値が入っているという前提です。

尚、下準備をこまめにする必要があります。

まずSheet1に作業用の列を2列設けます。
作業列1（これは「施設No」と「氏名」を重複なしに抽出するため）のI2セルに
=IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2)=1,ROW(),"")
という数式を入れます。

次に作業列2のJ2セルに
=IF(A2="","",TEXT(D2,"e")*1)
という数式を入れ、I2・J2セルを範囲指定　→　J2セルのフィルハンドルでこれ以上データはない！というくらいまで下へコピー！
（J2セルのフィルハンドルでダブルクリックでも構いません）

次にSheet2のC1～E1セルの表示形式を設定します。
C1セルの表示形式をユーザー定義から
"H"0"入金回数"

同じくD1セルの表示形式をユーザー定義から
"H"0"入金金額"

さらにE1セルの表示形式をユーザー定義から
"H"0"最終入金日"

としておき、C1～E1セルを範囲指定　→　E1セルのフィルハンドルで右へ3列ずつ好きなだけコピーしておきます。
そしてC1・D1・E1にはそれぞれ　26　と数値のみを入力すれば↓の画像のような表示になります。
F列以降も同様に　27　や　28　を入力していきます。

A2セルに
=IFERROR(INDEX(Sheet1!$A:$C,SMALL(Sheet1!$I:$I,ROW(A1)),MATCH(A$1,Sheet1!$A$1:$C$1,0)),"")
という数式を入れ隣りのB2セルまでフィル＆コピー！

C2セルに
=IF($A2="","",COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$C:$C,$B2,Sheet1!$J:$J,C$1))

D2セルに
=IF($A2="","",SUMIFS(Sheet1!$E:$E,Sheet1!$A:$A,$A2,Sheet1!$C:$C,$B2,Sheet1!$J:$J,D$1))

E2セルに
=IF($A2="","",MAX(IF(Sheet1!$C$1:$C$10000=$B2,Sheet1!$D$1:$D$10000)))
E2セルだけが配列数式になりますのでCtrl＋Shift＋Enterで確定！

C2～E2セルを範囲指定　→　E2セルのフィルハンドルで右へ3列ずつコピー！

各列の表示形式は適宜変更してください。

最後にA2～最終列の2行目を範囲指定　→　フィルハンドルで下へコピー！
これで画像のような感じになります。

※　データが8000行位になるみたいなので極力配列数式は使用したくなかったのですが
「最終日入金日」列のみ配列数式にしてしまいました。m(_ _)m

銀鱗 · Answer

急いでいるという人に勧めるのは気が引けるのですが、
(´・ω・`)っ「ピボットテーブル」

使い方、知っているなら良いんですけど…。
勧めるの気が引けるんです。
１時間くらいピボットテーブルについて調べてみてください。

Excelデータがうまく作成できず困っています【急いでいます】

No6です。

マクロで提出データを作成しても良いならマクロの提供は可能です。

何度もごめんなさい。

続けてお邪魔します。

No.2です。

こんばんは！

急いでいるという人に勧めるのは気が引けるのですが、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング