Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちらをご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

569 / 13645 ﾂﾘｰ

←次へ　｜　前へ→

▼

【79618】指定期間でログインしたユーザーを知りたい ... amatsuno 18/2/14(水) 16:19

【79619】Re:指定期間でログインしたユーザーを知り... 亀マスター 18/2/14(水) 21:03

【79620】Re:指定期間でログインしたユーザーを知り... amatsuno 18/2/15(木) 13:36

【79627】Re:指定期間でログインしたユーザーを知り... amatsuno 18/2/20(火) 16:36

【79633】Re:指定期間でログインしたユーザーを知り... 亀マスター 18/2/21(水) 22:38

【79640】Re:指定期間でログインしたユーザーを知り... amatsuno 18/2/23(金) 15:53

【79646】Re:指定期間でログインしたユーザーを知り... 亀マスター 18/2/23(金) 22:55

【79650】Re:指定期間でログインしたユーザーを知り... amatsuno 18/2/26(月) 16:07

【79651】Re:指定期間でログインしたユーザーを知り... 亀マスター 18/2/26(月) 18:53

【79654】Re:指定期間でログインしたユーザーを知り... amatsuno 18/2/27(火) 17:56

【79641】Re:指定期間でログインしたユーザーを知り... amatsuno 18/2/23(金) 15:55

【79642】Re:指定期間でログインしたユーザーを知り... amatsuno 18/2/23(金) 15:59


	【79618】指定期間でログインしたユーザーを知りた...

amatsuno

- 18/2/14(水) 16:19 -


	引用なし

	パスワード

指定期間内にログインしたユーザの取得を実施したい

指定した期間内にログインしたユーザの情報を取得したいと思っています。
以下の方針で取得する想定です。

使用ファイル：
master.csv　⇒　全体のマスタ情報が格納されている
daily.csv　⇒　直近60日程度の情報が格納されている

check.xlsm　⇒　VBAを設定するファイル
　　　　　　　　シート「master」「daily」「daily2」「daily3」「match」「BTN」がある

手順：
1.master.csvを読み込み、全体のマスタ情報を取得する
2.daily.csv情報を読み込み、直近のログイン情報を取得する
3.読み込んだdaily情報から、指定期間（2018/1/1～2018/2/1）のレコード（アドレス）を抽出する
4.抽出した情報から、第4カラムが「FALSE」のもののみを抽出する
5.上記の4.で抽出した情報とmaster情報を紐付ける

想定では、test12@kkkk.onmicrosoft.comが出力されると思っていましたが、
何も抽出されませんでした。

どこが間違っていたのでしょうか？

お分かりになられる方、よろしくお願いいたします

コードは、以下のように作成しました

Option Explicit

Sub CSV_MATCH()
　　'指定期間内のログイン情報を抽出する
　　
　　Dim hida As Long
　　Dim migi As Long
　　Dim cols1 As Long
　　Dim cols2 As String
　　　　Dim cols4 As String
　　Dim cols3 As Long
　　Dim stdate As String
　　Dim eddate As String
　　Dim ws1 As Worksheet
　　Dim ws2 As Worksheet
　　Dim ws3 As Worksheet
　　Dim ws4 As Worksheet
　　Dim ws5 As Worksheet
　　Dim ws6 As Worksheet
　　
　　Set ws1 = Worksheets("master")
　　Set ws2 = Worksheets("daily")
　　Set ws3 = Worksheets("match")
　　Set ws4 = Worksheets("BTN")
　　Set ws5 = Worksheets("daily2")
　　Set ws6 = Worksheets("daily3")
　　
　　With Sheets("master")
　　cols1 = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Column
　　End With
　　
'　 With Sheets("daily")
'　 cols2 = .UsedRange.Cells(.UsedRange.Rows.Count,1).Column
'　 End With
　　
　　'日付範囲を指定した日次情報の貼り付け先(daily2)を空白にする
　　ws5.Cells.Clear
　　ws6.Cells.Clear
　　ws2.Activate
　　

　　ActiveSheet.AutoFilterMode = False
　　
　　'抽出する日次情報の範囲を指定する
　　stdate = InputBox("ﾃﾞｰﾀ抽出開始日を入力してください。（2018/1/1形式で入力）")
　　eddate = InputBox("ﾃﾞｰﾀ抽出終了日を入力してください。（2018/1/1形式で入力）")
　　
　　'日付範囲を指定した日次情報をdaily2へ貼り付ける
　　Range("A1").AutoFilter Field:=1, Criteria1:=">=" & stdate, Operator:=xlAnd, Criteria2:="<=" & eddate
　　ws2.Range("A1").CurrentRegion.Copy ws5.Range("A1")
　　ws2.AutoFilterMode = False

　　'ログイン情報の貼り付け先（match）を空白にする
　　ws3.Cells.Clear
　　
　　ws5.Activate
　　　　
　　Dim i, LastRow As Long
　　　 LastRow = Cells(Rows.Count, 1).End(xlUp).Row
　　　 For i = 1 To LastRow
　　　 If Cells(i, 4) = False Then
　　　 Rows(i).Copy Sheets("daily3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
　　　 End If
　　　 Next i

　　With Sheets("daily3")
　　cols3 = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Column
　　End With

　　　　　　　　　　
　　 '日付情報のメールアドレスとマスタ情報のメールアドレスが一致したレコードを、matchに貼り付ける
　　For migi = 1 To cols1
　　　　　 Application.StatusBar = "処理実行中．．．．(現在 " & migi & "件)"
　　　　For hida = 1 To cols3
　　　　　　If ws6.Range("B" & migi).Value = ws1.Range("D" & hida).Value Then
　　　　　　　　ws3.Range("A" & hida).Value = ws1.Range("B" & hida).Value
　　　　　　　　ws3.Range("B" & hida).Value = ws1.Range("D" & hida).Value
　　　　　　　　ws3.Range("C" & hida).Value = ws1.Range("C" & hida).Value
　　　　　　　　ws3.Range("D" & hida).Value = ws1.Range("A" & hida).Value
　　　　　　　　Exit For
　　　　　　End If
　　　　Next
　　　　ws3.Range("H" & hida).Value = migi
　　　　ws3.Range("I" & hida).Value = hida
　　Next
　　
　　　　Application.StatusBar = "処理完了．．．．(全 " & migi - 1 & "件)"
　　　　
　 '抽出した情報を部署単位で並び替える
　　ws3.Activate
　　ws3.Range("A:K").Sort Key1:=Range("D1"), order1:=xlAscending
　　　　
　 '初期画面に戻る
　　ws4.Activate

End Sub

daily.csv

Date,Name,Display, Deleted
2018/2/7,test3@kkkk.onmicrosoft.com,テスト 3,FALSE
2018/2/7,test4@kkkk.onmicrosoft.com,テスト 4,FALSE
2018/2/7,test5@kkkk.onmicrosoft.com,テスト 5,FALSE
2018/2/7,test6@kkkk.onmicrosoft.com,テスト 6,FALSE
2018/2/7,test7@kkkk.onmicrosoft.com,テスト 7,FALSE
2018/2/7,test8@kkkk.onmicrosoft.com,テスト 8,FALSE
2018/2/7,test9@kkkk.onmicrosoft.com,テスト 9,FALSE
2018/2/7,test10@kkkk.onmicrosoft.com,テスト 10,FALSE
2018/1/9,test11@kkkk.onmicrosoft.com,テスト 11,TRUE
2018/1/9,test12@kkkk.onmicrosoft.com,テスト 12,FALSE

master.csv

COL1,COL2,COL3,mail,COL4
Mod,TMTM43045,KM4,ssstest3@kkkk.onmicrosoft.com,1043045
Mod,aaTMTM43046,KM5,tsssest3@kkkk.onmicrosoft.com,1043046
Mod,aaTMTM43047,KM6,ssstest3@kkkk.onmicrosoft.com,1043047
Mod,aaTMTM43048,KM7,ssstest3@kkkk.onmicrosoft.com,1043048
Mod,aaTMTM43049,KM8,ssstest3@kkkk.onmicrosoft.com,1043049
Mod,aaTMTM43050,KM4,test3@kkkk.onmicrosoft.com,1043050
Mod,aaTMTM43056,KM4,test9@kkkk.onmicrosoft.com,1043056
Mod,aaTMTM43045,KM4,test10@kkkk.onmicrosoft.com,1043045
Mod,aaTMTM43046,KM5,test11@kkkk.onmicrosoft.com,1043046
Mod,aaTMTM43047,KM6,test12@kkkk.onmicrosoft.com,1043047
Mod,aaTMTM43048,KM7,test13@kkkk.onmicrosoft.com,1043048


	【79619】Re:指定期間でログインしたユーザーを知...

亀マスター - 18/2/14(水) 21:03 -


	引用なし

	パスワード

col1とcol3の値に問題があると思います。
いずれも値が1になっているので、Forループからすぐに抜けてしまうようです。

　　　　cols1 = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Column
　　　　cols3 = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Column

これはいずれもColumnがRowの誤りでしょう。
CellsプロパティでColumnを1に指定しているのですから、そこから得られたセルのColumnを取得すれば1にしかなりません。

それと、これを修正しても問題が残っています。
ループに入ってからの
　　　　If ws6.Range("B" & migi).Value = ws1.Range("D" & hida).Value Then
ですが、migiはmaster（ws1）の行数、hidaはdaily3（ws6）の行数を表しているのですから、ここでは逆でしょう。

同じく、
　　　　ws3.Range("A" & hida).Value = ws1.Range("B" & hida).Value
　　　　ws3.Range("B" & hida).Value = ws1.Range("D" & hida).Value
　　　　ws3.Range("C" & hida).Value = ws1.Range("C" & hida).Value
　　　　ws3.Range("D" & hida).Value = ws1.Range("A" & hida).Value
は、いずれも＝の右側の式でhidaをmigiに修正する必要があると思われます。

以上で、私が手元で試してみた範囲では動作しました。

なお、動作には支障がないのですが、変数名は直した方がいいですね。ws1とかcols1とかhidaとか、何を表しているのかわかりません。私がコードを読む際にも、結構読みにくかったですよ。
ws1ならsh_masterのような、中身がわかる変数名にするか、いっそのこと変数を使わずにWorksheets("master")だけでもいいのではないでしょうか。

それに、シートの選択方法が、ws1のような変数を使ったり、Worksheet("シート名")になっていたり、ActiveSheetになっていたりで、やはりどのシートを指定しているのかがわかりにくいです。特にActiveSheetは手前のコードを読んでどこのシートがアクティブになっているのかを調べないとわかりませんし、そもそもそのシートをアクティブにする処理が余計です。

あと、サンプル程度のデータ量ならこれでもすぐ終わると思いますが、件数が多くなってくるとセルの読み書きの回数が多くなり、処理に時間がかかるようになりますよ。
Variant型変数にセル範囲を代入して配列として扱う方法がありますので、余裕があれば勉強してみてはどうでしょうか。
ht tp://officetanaka.net/excel/vba/speed/s11.htm


	【79620】Re:指定期間でログインしたユーザーを知...

amatsuno - 18/2/15(木) 13:36 -


	引用なし

	パスワード

ありがとうございます。

上記の方法で達成できました。

変数名は修正いたします。

件数については、
おそらくmaster側が10000件程度で、
daily側が6000件程度になりそうなので、
考慮の価値がありそうです


	【79627】Re:指定期間でログインしたユーザーを知...

amatsuno - 18/2/20(火) 16:36 -


	引用なし

	パスワード

▼亀マスターさん：

データ量が多量のため、
変数に格納する方法を試したいと思っています。
（各csvは20000件と40000件です）
　ws_masterが40000件
　ws_daily2が20000件

ただ、配列に格納して実施したいのですが、

格納したい当該の箇所は、取得前と取得後の抽出対象列が異なっています。
どのように並び替えればいいのでしょうか？

　　Dim daily_num As Long
　　Dim master_num As Long
　　
　　Dim count_master As Long
　　Dim count_daily As Long

　　Dim ws_master As Worksheet
　　Dim ws_match As Worksheet

　　Dim mail_match As String

　　 Dim ary_cell As Variant　' ←配列予定の変数

　　For master_num = 1 To count_master
　　　
　　　 For daily_num = 1 To count_daily
　　　　　　　　　mail_match = ws_master.Range("D" & master_num)
　　　　　　　
　　　　　　If ws_daily2.Range("B" & daily_num).Value = mail_match Then
　　　　　　　　ws_match.Range("A" & daily_num).Value = ws_master.Range("B" & master_num).Value
　　　　　　　　ws_match.Range("B" & daily_num).Value = ws_master.Range("D" & master_num).Value
　　　　　　　　ws_match.Range("C" & daily_num).Value = ws_master.Range("G" & master_num).Value
　　　　　　　　ws_match.Range("D" & daily_num).Value = ws_master.Range("I" & master_num).Value
　　　　　　　　ws_match.Range("E" & daily_num).Value = ws_master.Range("L" & master_num).Value
　　　　　　　　ws_match.Range("F" & daily_num).Value = ws_master.Range("M" & master_num).Value
　　　　　　　　ws_match.Range("G" & daily_num).Value = ws_master.Range("N" & master_num).Value
　　　　　　　　Exit For
　　　　　　End If
　　　　Next

>col1とcol3の値に問題があると思います。
>いずれも値が1になっているので、Forループからすぐに抜けてしまうようです。
>
>　　　　cols1 = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Column
>　　　　cols3 = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Column
>
>これはいずれもColumnがRowの誤りでしょう。
>CellsプロパティでColumnを1に指定しているのですから、そこから得られたセルのColumnを取得すれば1にしかなりません。
>
>それと、これを修正しても問題が残っています。
>ループに入ってからの
>　　　　If ws6.Range("B" & migi).Value = ws1.Range("D" & hida).Value Then
>ですが、migiはmaster（ws1）の行数、hidaはdaily3（ws6）の行数を表しているのですから、ここでは逆でしょう。
>
>同じく、
>　　　　ws3.Range("A" & hida).Value = ws1.Range("B" & hida).Value
>　　　　ws3.Range("B" & hida).Value = ws1.Range("D" & hida).Value
>　　　　ws3.Range("C" & hida).Value = ws1.Range("C" & hida).Value
>　　　　ws3.Range("D" & hida).Value = ws1.Range("A" & hida).Value
>は、いずれも＝の右側の式でhidaをmigiに修正する必要があると思われます。
>
>以上で、私が手元で試してみた範囲では動作しました。
>
>
>なお、動作には支障がないのですが、変数名は直した方がいいですね。ws1とかcols1とかhidaとか、何を表しているのかわかりません。私がコードを読む際にも、結構読みにくかったですよ。
>ws1ならsh_masterのような、中身がわかる変数名にするか、いっそのこと変数を使わずにWorksheets("master")だけでもいいのではないでしょうか。
>
>それに、シートの選択方法が、ws1のような変数を使ったり、Worksheet("シート名")になっていたり、ActiveSheetになっていたりで、やはりどのシートを指定しているのかがわかりにくいです。特にActiveSheetは手前のコードを読んでどこのシートがアクティブになっているのかを調べないとわかりませんし、そもそもそのシートをアクティブにする処理が余計です。
>
>あと、サンプル程度のデータ量ならこれでもすぐ終わると思いますが、件数が多くなってくるとセルの読み書きの回数が多くなり、処理に時間がかかるようになりますよ。
>Variant型変数にセル範囲を代入して配列として扱う方法がありますので、余裕があれば勉強してみてはどうでしょうか。
>ht tp://officetanaka.net/excel/vba/speed/s11.htm


	【79633】Re:指定期間でログインしたユーザーを知...

亀マスター - 18/2/21(水) 22:38 -


	引用なし

	パスワード

回答が遅くなって済みません。

>格納したい当該の箇所は、取得前と取得後の抽出対象列が異なっています。
抽出元のシートでA列だった情報を抽出先のシートでB列に入れるというようなことでしょうか？
でしたら、既に作成されている二重ループの部分でそれは問題なくできているので、大丈夫ですよ。
そういう意図でないということでしたら連絡ください。

今回、配列にすべきはループの中で何度も読み取りあるいは書き込みをしているシートで、具体的にはmaster、daily3、matchの3つのシートです。
そこで、これらのシートのデータを格納する変数を
arr_master、arr_daily3、arr_matchとして、以下のようにします。
以前に提示していただいたコードをベースに修正しています。（変更していない部分は示していません）

Dim arr_master As Variant
Dim arr_daily3 As Variant
Dim arr_match As Variant
Dim i As Long, j As Long

'元々のコードで　Worksheets("daily2").Activate　から始まる部分ですが、
'ここでもセルの読み書きが発生しています。
'ただ、ここはエクセルのオートフィルを使えばループを回さなくても簡単にできるので
'このように修正しています。
With ws_daily2.UsedRange
　　.AutoFilter field:=4, Criteria1:=False
　　.Copy ws_daily3.Range("A1")
End With
Worksheets("daily2").AutoFilterMode = False

'daily3シートのデータを配列に格納
arr_daily3 = Worksheets("daily3").UsedRange

'matchシートのデータ（この時点では空白）を配列に格納
'（行数はdaily3と同じにする）
With Worksheets("match")
　　arr_match = .Range(.Cells(1, "A"), .Cells(UBound(arr_daily3), "D"))
End With

'masterシートのデータを配列に格納
arr_master = Worksheets("master").UsedRange

'daily3シートのデータでmasterシートに該当するものを見つけて情報を転記
'（ここからセルを直接操作するのではなく配列を使っている）
For i = 2 To UBound(arr_daily3)
　　Application.StatusBar = "処理実行中．．．．(現在 " & i & "件)"
　　For j = 2 To UBound(arr_master)
　　　　If arr_daily3(i, 2) = arr_master(j, 4) Then
　　　　　　arr_match(i, 1) = arr_master(j, 2)
　　　　　　arr_match(i, 2) = arr_master(j, 4)
　　　　　　arr_match(i, 3) = arr_master(j, 3)
　　　　　　arr_match(i, 4) = arr_master(j, 1)
　　　　　　Exit For
　　　　End If
　　Next j
Next i

'matchシートに見出しをセット
arr_match(1, 1) = arr_master(1, 2)
arr_match(1, 2) = arr_master(1, 4)
arr_match(1, 3) = arr_master(1, 3)
arr_match(1, 4) = arr_master(1, 1)

'（見出し列の分とNextでカウントが1つ増える分、マイナスは2にしています）
Application.StatusBar = "処理完了．．．．(全 " & i - 2 & "件)"

'matchシートに配列場のデータを転記
With Worksheets("match")
　　.Range(.Cells(1, "A"), .Cells(UBound(arr_daily3), "D")) = arr_match
End With

'（matchシートをアクティブにする操作を省いているので、代わりにこのような書き方にしています）
With Worksheets("match")
　　.Range("A:D").Sort key1:=.Range("D1"), Order1:=xlAscending
End With

※提示していただいたコードで
If ws_daily2.Range("B" & daily_num).Value = mail_match Then
とありましたが、ws_daily3ではないでしょうか？（以前のものではそうでしたので）


	【79640】Re:指定期間でログインしたユーザーを知...

amatsuno - 18/2/23(金) 15:53 -


	引用なし

	パスワード

▼亀マスターさん：
お答えを元にコードを修正したのですが、
目的の件数の取得ができませんでした。

いかに、現状のコードを記載いたしますので、
誤っている点がありましたら教えていただけませんでしょうか

現在、配列を使用していない状態のときのVBAと配列を使用した状態のVBAで件数が半分以下になっていますので、おそらく抽出条件で間違っているのかと思います

出力期間は
2018/1/1 開始
2018/2/1 終了
で実施しています

Option Explicit

Sub CSV_MATCH()
　　'指定期間内のログイン情報を抽出する
　
　 Debug.Print Time
　　
　　Dim daily_num As Long
　　Dim master_num As Long
　　Dim daily_num2 As Long
　　Dim master_num2 As Long
　　
　　Dim count_master As Long
　　Dim count_master2 As Long
　　Dim count_daily As Long
　　Dim count_daily2 As Long
　　
　　Dim stdate As String
　　Dim eddate As String
　　Dim ary_cell() As Variant
　　Dim ary_cell2() As Variant
　　Dim mail_match As String
　　Dim mail_match2 As String
　　Dim mail_match3 As String
　　Dim mail_match4 As String
　　
　　
　　Dim arr_master As Variant
　　Dim arr_daily_o365_tmp As Variant
　　Dim arr_daily_o365_master As Variant
　　Dim arr_i As Long
　　Dim arr_j As Long

　　Dim ws_master As Worksheet
　　Dim ws_daily As Worksheet
　　Dim ws_match As Worksheet
　　Dim ws_btn As Worksheet
　　Dim ws_daily_tmp As Worksheet
　　Dim ws_daily_o365_tmp As Worksheet
　　Dim ws_daily_o365_master As Worksheet
　　
　　Set ws_master = Worksheets("マスタCSV")
　　Set ws_daily = Worksheets("取り込みCSV")
　　Set ws_match = Worksheets("期間内利用ユーザ")
　　Set ws_btn = Worksheets("ボタン")
　　Set ws_daily_tmp = Worksheets("利用ユーザ一時保管")　⇒元はdaily2
　　Set ws_daily_o365_tmp = Worksheets("O365契約ユーザ") ⇒元はdaily3
　　Set ws_daily_o365_master = Worksheets("O365ユーザマスタ情報") ⇒元はmatch2
　　　　
　　With Sheets("マスタCSV")
　　count_master = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Row
　　End With
　　
　
　　With Sheets("利用ユーザ一時保管")
　　count_daily = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Row
　　End With

　　'ここからはws_dailyでの作業
　　ws_daily.Activate
　　
　　'オートフィルタがかかっていない状態にする
　　ActiveSheet.AutoFilterMode = False
　　
　　'抽出する日次情報の範囲を指定する
　　stdate = InputBox("ﾃﾞｰﾀ抽出開始日を入力してください。（2018/1/1形式で入力）")
　　eddate = InputBox("ﾃﾞｰﾀ抽出終了日を入力してください。（2018/1/1形式で入力）")
　　

'=======(この範囲内は後日修正箇所 start)====================================
　　'日付範囲を指定したws_dailyの情報をws_daily_tmpへ貼り付ける
　　Range("A1").AutoFilter field:=6, Criteria1:=">=" & stdate, Operator:=xlAnd, Criteria2:="<=" & eddate
　　ws_daily.Range("A1").CurrentRegion.Copy ws_daily_tmp.Range("A1")
　　ws_daily.AutoFilterMode = False

　　'ws_daily_tmpに記載のメールアドレスとws_masterに記載のメールアドレスと付き合わせる
　　For master_num = 1 To count_master
　　　
　　　 For daily_num = 1 To count_daily
　　　 mail_match = ws_master.Range("D" & master_num)
　　　 mail_match2 = ws_daily_tmp.Range("B" & daily_num)
　　　　　　If mail_match2 = mail_match Then
　　　　　　　　ws_match.Range("A" & daily_num).Value = ws_master.Range("B" & master_num).Value
　　　　　　　　ws_match.Range("B" & daily_num).Value = ws_master.Range("D" & master_num).Value
　　　　　　　　ws_match.Range("C" & daily_num).Value = ws_master.Range("G" & master_num).Value
　　　　　　　　ws_match.Range("D" & daily_num).Value = ws_master.Range("I" & master_num).Value
　　　　　　　　ws_match.Range("E" & daily_num).Value = ws_master.Range("L" & master_num).Value
　　　　　　　　ws_match.Range("F" & daily_num).Value = ws_master.Range("M" & master_num).Value
　　　　　　　　ws_match.Range("G" & daily_num).Value = ws_master.Range("N" & master_num).Value
　　　　　　　　Exit For
　　　　　　End If
　　　　Next
　　Next
　　　　

　 'ここからはws_matchでの作業
　　ws_match.Activate
　　
　 '抽出した情報を部署単位で並び替える
　　ws_match.Range("A:K").Sort Key1:=Range("D1"), order1:=xlAscending
　　　　

'=======(この範囲内は後日修正箇所 end)====================================

　　'ここからはシートws_dailyでの作業
　　ws_daily.Activate

　　'1.ws_daily内で「D列がfalse」をws_daily_o365_tmpに貼り付ける
　　With ws_daily.UsedRange
　　　　.AutoFilter field:=4, Criteria1:=False
　　　　.Copy ws_daily_o365_tmp.Range("A1")
　　End With
　　ws_daily.AutoFilterMode = False

　　'2.ws_daily内で「D列が指定期間内」をws_daily_o365_tmpに貼り付ける
　　Range("A1").AutoFilter field:=6, Criteria1:=">=" & stdate, Operator:=xlAnd, Criteria2:="<=" & eddate
　　Rows(1).Hidden = True
　　ws_daily.Range("A1").CurrentRegion.Copy ws_daily_o365_tmp.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
　　ws_daily.AutoFilterMode = False
　　Rows(1).Hidden = False
　　
　　'1.と2.で重複したレコードを削除する
　　ws_daily_o365_tmp.Range("A:K").RemoveDuplicates Columns:=Array(2), Header:=xlNo
　　　　
　　'タイトル行を削除する
　　ws_daily_o365_tmp.Rows(1).Delete
　　
　　'メールアドレスでソートする
　　'ws_daily_o365_tmp.Range("A:K").Sort Key1:=Range("B1"), order1:=xlAscending
　　
　　With Worksheets("O365契約ユーザ")
　　　　.Range("A:N").Sort Key1:=.Range("B1"), order1:=xlAscending
　　End With
　　
　　'count_master2はAD情報のレコード件数
　　With Sheets("マスタCSV")
　　count_master2 = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Row
　　End With
　　
　　'count_daily2はws_daily_o365_tmpのレコード件数
　　With Sheets("O365契約ユーザ")
　　count_daily2 = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Row
　　End With
　　
　　
　　'ws_daily_o365_tmpのデータを配列に格納
　　arr_daily_o365_tmp = Worksheets("O365契約ユーザ").UsedRange
　　　　
　　'ws_daily_o365_masterのデータ（この時点では空白）を配列に格納
　　'（行数はarr_daily_o365_tmpと同じにする）
　　With Worksheets("O365ユーザマスタ情報")
　　　　arr_daily_o365_master = .Range(.Cells(1, "A"), .Cells(UBound(arr_daily_o365_tmp), "N"))
　　End With

　　'masterシートのデータを配列に格納
　　arr_master = Worksheets("マスタCSV").UsedRange
　

　　'daily3シートのデータでmasterシートに該当するものを見つけて情報を転記
　　'（ここからセルを直接操作するのではなく配列を使っている）
　　For arr_i = 1 To UBound(arr_daily_o365_tmp)
　　　　Application.StatusBar = "処理実行中．．．．(現在 " & arr_i & "件)"
　　　　For arr_j = 1 To UBound(arr_daily_o365_master)
　　　　　　If arr_daily_o365_tmp(arr_i, 2) = arr_master(arr_j, 4) Then
　　　　　　　　arr_daily_o365_master(arr_i, 1) = arr_master(arr_j, 2)
　　　　　　　　arr_daily_o365_master(arr_i, 2) = arr_master(arr_j, 4)
　　　　　　　　arr_daily_o365_master(arr_i, 3) = arr_master(arr_j, 7)
　　　　　　　　arr_daily_o365_master(arr_i, 4) = arr_master(arr_j, 9)
　　　　　　　　arr_daily_o365_master(arr_i, 5) = arr_master(arr_j, 12)
　　　　　　　　arr_daily_o365_master(arr_i, 6) = arr_master(arr_j, 13)
　　　　　　　　arr_daily_o365_master(arr_i, 7) = arr_master(arr_j, 14)
　　　　　　　　Exit For
　　　　　　End If
　　　　Next arr_j
　　Next arr_i
　　　　
　
　　Application.StatusBar = "処理完了．．．．(全 " & arr_i & "件)"
　　
　　With Worksheets("O365ユーザマスタ情報")
　　　　.Range(.Cells(1, "A"), .Cells(UBound(arr_daily_o365_tmp), "N")) = arr_daily_o365_master
　　End With

　　ws_daily_o365_master.Range("A:N").RemoveDuplicates Columns:=Array(2), Header:=xlNo

　　With Worksheets("O365ユーザマスタ情報")
　　　　.Range("A:N").Sort Key1:=.Range("D1"), order1:=xlAscending
　　End With
　　

'　 For master_num2 = 1 To count_master2
'　　　For daily_num2 = 1 To count_daily2
'　　　mail_match3 = ws_master.Range("D" & master_num2)
'　　　mail_match4 = ws_daily_o365_tmp.Range("B" & daily_num2)
'　　　　　 If mail_match4 = mail_match3 Then
'　　　　　　If ws_daily_o365.Range("B" & daily_num2).Value = ws_master.Range("D" & master_num2).Value Then
'　　　　　　　 ws_daily_o365_master.Range("A" & daily_num2).Value = ws_master.Range("B" & master_num2).Value
'　　　　　　　 ws_daily_o365_master.Range("B" & daily_num2).Value = ws_master.Range("D" & master_num2).Value
'　　　　　　　 ws_daily_o365_master.Range("C" & daily_num2).Value = ws_master.Range("G" & master_num2).Value
'　　　　　　　 ws_daily_o365_master.Range("D" & daily_num2).Value = ws_master.Range("I" & master_num2).Value
'　　　　　　　 ws_daily_o365_master.Range("E" & daily_num2).Value = ws_master.Range("L" & master_num2).Value
'　　　　　　　 ws_daily_o365_master.Range("F" & daily_num2).Value = ws_master.Range("M" & master_num2).Value
'　　　　　　　 ws_daily_o365_master.Range("G" & daily_num2).Value = ws_master.Range("N" & master_num2).Value
'　　　　　　　 Exit For
'　　　　　 End If
'　　　 Next
'　 Next

　 '初期画面に戻る
　　ws_btn.Activate
　　Debug.Print Time
　　　　
End Sub


	【79641】Re:指定期間でログインしたユーザーを知...

amatsuno - 18/2/23(金) 15:55 -


	引用なし

	パスワード

（取り込みCSV）シート「ws_daily」に貼り付け

col-1,col-2,col-3,col-4,col-5,col-6,col-7,col-8,col-9,col-10,col-11
2018/2/14,test14@mailtest.co.jp,,FALSE,,2018/1/9,58,275,275,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test17@mailtest.co.jp,,FALSE,,2018/1/9,102,1469,1523,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test20@mailtest.co.jp,,FALSE,,2018/1/29,127,930,887,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test23@mailtest.co.jp,,FALSE,,2018/1/9,10,89,71,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test26@mailtest.co.jp,,FALSE,,2018/1/29,138,695,630,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test29@mailtest.co.jp,,FALSE,,2018/1/9,0,46,42,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test32@mailtest.co.jp,,TRUE,,2018/1/9,7,390,193,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test35@mailtest.co.jp,,FALSE,,2018/1/29,76,550,494,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test38@mailtest.co.jp,,FALSE,,2018/1/9,81,1000,983,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test41@mailtest.co.jp,,FALSE,,2018/1/9,193,1647,1449,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test44@mailtest.co.jp,,FALSE,,2018/1/9,249,1312,1002,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test47@mailtest.co.jp,,FALSE,,2018/1/9,566,1227,1375,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test50@mailtest.co.jp,,TRUE,,2018/1/9,134,785,610,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test53@mailtest.co.jp,,FALSE,,2018/1/29,35,207,213,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test56@mailtest.co.jp,,FALSE,,2018/1/9,184,558,384,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test59@mailtest.co.jp,,TRUE,,2017/12/11,228,923,564,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test62@mailtest.co.jp,,TRUE,,2018/1/9,124,563,561,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test65@mailtest.co.jp,,TRUE,,2017/12/11,124,687,408,OFFICE 365 ENTERPRISE E4,30
2018/2/14,test68@mailtest.co.jp,,FALSE,,2018/1/9,1,170,65,OFFICE 365 ENTERPRISE E4,30


	【79642】Re:指定期間でログインしたユーザーを知...

amatsuno - 18/2/23(金) 15:59 -


	引用なし

	パスワード

（マスタCSV）シート「ws_master」に貼り付け

Mcol-1,Mcol-2,Mcol-3,Mcol-4,Mcol-5,Mcol-6,Mcol-7,Mcol-8,Mcol-9,Mcol-10,Mcol-11,Mcol-12,Mcol-13,Mcol-14,Mcol-15
Mod,TST43045,TST4,test11@mailtest.co.jp,1043045,1.00E+12,100,10012,1001214,100121024,10012102420,株式会社テスト社名,,114出力部署テスト,出力部署テスト名称正式名部
Mod,TST43046,TST5,test12@mailtest.co.jp,1043046,1.00E+12,100,10012,1001210,100121024,10012102420,株式会社テスト社名,,110出力部署テスト,出力部署テスト名称正式名部
Mod,TST43047,TST6,test13@mailtest.co.jp,1043047,1.00E+12,100,10012,1001211,100121024,10012102420,株式会社テスト社名,,111出力部署テスト,出力部署テスト名称正式名部
Mod,TST43048,TST7,test14@mailtest.co.jp,1043048,1.00E+12,100,10012,1001211,100121024,10012102420,株式会社テスト社名,,111出力部署テスト,出力部署テスト名称正式名部
Mod,TST43049,TST8,test15@mailtest.co.jp,1043049,1.00E+12,100,10012,1001212,100121024,10012102420,株式会社テスト社名,,112出力部署テスト,出力部署テスト名称正式名部
Mod,TST43050,TST4,test16@mailtest.co.jp,1043050,1.00E+12,100,10012,1001212,100121024,10012102420,株式会社テスト社名,,112出力部署テスト,出力部署テスト名称正式名部
Mod,TST43051,TST5,test17@mailtest.co.jp,1043051,1.00E+12,100,10012,1001213,100121024,10012102420,株式会社テスト社名,,113出力部署テスト,出力部署テスト名称正式名部
Mod,TST43052,TST6,test18@mailtest.co.jp,1043052,1.00E+12,100,10012,1001211,100121024,10012102420,株式会社テスト社名,,111出力部署テスト,出力部署テスト名称正式名部
Mod,TST43053,TST7,test19@mailtest.co.jp,1043053,1.00E+12,100,10012,1001213,100121024,10012102420,株式会社テスト社名,,113出力部署テスト,出力部署テスト名称正式名部
Mod,TST43054,TST8,test20@mailtest.co.jp,1043054,1.00E+12,100,10012,1001214,100121024,10012102420,株式会社テスト社名,,114出力部署テスト,出力部署テスト名称正式名部
Mod,TST43055,TST4,test21@mailtest.co.jp,1043055,1.00E+12,100,10012,1001214,100121024,10012102420,株式会社テスト社名,,114出力部署テスト,出力部署テスト名称正式名部
Mod,TST43056,TST4,test22@mailtest.co.jp,1043056,1.00E+12,100,10012,1001215,100121024,10012102420,株式会社テスト社名,,115出力部署テスト,出力部署テスト名称正式名部
Mod,TST43045,TST4,test23@mailtest.co.jp,1043045,1.00E+12,200,10012,2001214,100121024,10012102420,AA株式会社テスト社名,,214出力部署テスト,出力部署テスト名称正式名部
Mod,TST43046,TST5,test24@mailtest.co.jp,1043046,1.00E+12,200,10012,2001210,100121024,10012102420,AA株式会社テスト社名,,210出力部署テスト,出力部署テスト名称正式名部
Mod,TST43047,TST6,test25@mailtest.co.jp,1043047,1.00E+12,200,10012,2001211,100121024,10012102420,AA株式会社テスト社名,,211出力部署テスト,出力部署テスト名称正式名部
Mod,TST43048,TST7,test26@mailtest.co.jp,1043048,1.00E+12,200,10012,2001211,100121024,10012102420,AA株式会社テスト社名,,211出力部署テスト,出力部署テスト名称正式名部
Mod,TST43049,TST8,test27@mailtest.co.jp,1043049,1.00E+12,200,10012,2001212,100121024,10012102420,AA株式会社テスト社名,,212出力部署テスト,出力部署テスト名称正式名部
Mod,TST43050,TST4,test28@mailtest.co.jp,1043050,1.00E+12,200,10012,2001212,100121024,10012102420,AA株式会社テスト社名,,212出力部署テスト,出力部署テスト名称正式名部
Mod,TST43051,TST5,test29@mailtest.co.jp,1043051,1.00E+12,200,10012,2001213,100121024,10012102420,AA株式会社テスト社名,,213出力部署テスト,出力部署テスト名称正式名部
Mod,TST43052,TST6,test30@mailtest.co.jp,1043052,1.00E+12,200,10012,2001211,100121024,10012102420,AA株式会社テスト社名,,211出力部署テスト,出力部署テスト名称正式名部
Mod,TST43053,TST7,test31@mailtest.co.jp,1043053,1.00E+12,200,10012,2001213,100121024,10012102420,AA株式会社テスト社名,,213出力部署テスト,出力部署テスト名称正式名部
Mod,TST43054,TST8,test32@mailtest.co.jp,1043054,1.00E+12,200,10012,2001214,100121024,10012102420,AA株式会社テスト社名,,214出力部署テスト,出力部署テスト名称正式名部
Mod,TST43055,TST4,test33@mailtest.co.jp,1043055,1.00E+12,200,10012,2001214,100121024,10012102420,AA株式会社テスト社名,,214出力部署テスト,出力部署テスト名称正式名部
Mod,TST43056,TST4,test34@mailtest.co.jp,1043056,1.00E+12,200,10012,2001215,100121024,10012102420,AA株式会社テスト社名,,215出力部署テスト,出力部署テスト名称正式名部
Mod,TST43056,TST4,test35@mailtest.co.jp,1043056,1.00E+12,100,10012,2001210,100121024,10012102420,AA株式会社テスト社名,,215出力部署テスト,出力部署テスト名称正式名部
Mod,TST43046,TST5,test36@mailtest.co.jp,1043046,1.00E+12,100,10012,1001210,100121024,10012102420,株式会社テスト社名,,210出力部署テスト,出力部署テスト名称正式名部
Mod,TST43046,TST5,test37@mailtest.co.jp,1043046,1.00E+12,100,10012,1001210,100121024,10012102420,株式会社テスト社名,,210出力部署テスト,出力部署テスト名称正式名部
Mod,TST43045,TST4,test38@mailtest.co.jp,1043045,1.00E+12,100,10012,1001214,100121024,10012102420,株式会社テスト社名,,114出力部署テスト,出力部署テスト名称正式名部
Mod,TST43046,TST5,test39@mailtest.co.jp,1043046,1.00E+12,100,10012,1001210,100121024,10012102420,株式会社テスト社名,,110出力部署テスト,出力部署テスト名称正式名部
Mod,TST43047,TST6,test40@mailtest.co.jp,1043047,1.00E+12,100,10012,1001211,100121024,10012102420,株式会社テスト社名,,111出力部署テスト,出力部署テスト名称正式名部
Mod,TST43048,TST7,test41@mailtest.co.jp,1043048,1.00E+12,100,10012,1001211,100121024,10012102420,株式会社テスト社名,,111出力部署テスト,出力部署テスト名称正式名部
Mod,TST43049,TST8,test42@mailtest.co.jp,1043049,1.00E+12,100,10012,1001212,100121024,10012102420,株式会社テスト社名,,112出力部署テスト,出力部署テスト名称正式名部
Mod,TST43050,TST4,test43@mailtest.co.jp,1043050,1.00E+12,100,10012,1001212,100121024,10012102420,株式会社テスト社名,,112出力部署テスト,出力部署テスト名称正式名部
Mod,TST43051,TST5,test44@mailtest.co.jp,1043051,1.00E+12,100,10012,1001213,100121024,10012102420,株式会社テスト社名,,113出力部署テスト,出力部署テスト名称正式名部
Mod,TST43052,TST6,test45@mailtest.co.jp,1043052,1.00E+12,100,10012,1001211,100121024,10012102420,株式会社テスト社名,,111出力部署テスト,出力部署テスト名称正式名部
Mod,TST43053,TST7,test46@mailtest.co.jp,1043053,1.00E+12,100,10012,1001213,100121024,10012102420,株式会社テスト社名,,113出力部署テスト,出力部署テスト名称正式名部
Mod,TST43054,TST8,test47@mailtest.co.jp,1043054,1.00E+12,100,10012,1001214,100121024,10012102420,株式会社テスト社名,,114出力部署テスト,出力部署テスト名称正式名部
Mod,TST43055,TST4,test48@mailtest.co.jp,1043055,1.00E+12,100,10012,1001214,100121024,10012102420,株式会社テスト社名,,114出力部署テスト,出力部署テスト名称正式名部
Mod,TST43056,TST4,test49@mailtest.co.jp,1043056,1.00E+12,100,10012,1001215,100121024,10012102420,株式会社テスト社名,,115出力部署テスト,出力部署テスト名称正式名部


	【79646】Re:指定期間でログインしたユーザーを知...

亀マスター - 18/2/23(金) 22:55 -


	引用なし

	パスワード

思っていた件数にならないとのことですが、具体的にはどのシートで何件抽出されるのが狙いでしょうか。

私が手元で試したところ、提示していただいたサンプルデータでは、「期間内利用ユーザ」シートに12件＋13行目にタイトル行が入った状態になりました。
「期間内利用ユーザ」シートには、指定された期間に該当し、「利用ユーザ一時保管」シートで4列目がFALSEになっているものを抽出するのだと思いますが、その件がおかしいということであれば、「マスタCSV」シートにないメールアドレスが「取り込みCSV」にあるからだと思います。

その他、新しく追加した「O365契約ユーザ」と「O365ユーザマスタ情報」ではどのような情報が抽出されることを想定しているのでしょうか。


	【79650】Re:指定期間でログインしたユーザーを知...

amatsuno - 18/2/26(月) 16:07 -


	引用なし

	パスワード

▼亀マスターさん：
>思っていた件数にならないとのことですが、具体的にはどのシートで何件抽出されるのが狙いでしょうか。

　⇒csvの情報はそれぞれ40000件と20000件なのですが、
　　「期間内利用ユーザ」で8000件程度出力されるはずが、2000件程度になっていました
（ただ、csvのレコード数が少ないときには総定数が抽出されましたので、PCスペックのほうが原因のような気がしています。投稿文字量の制限でデータをすべて記載でなくで申し訳ございません）

>その他、新しく追加した「O365契約ユーザ」と「O365ユーザマスタ情報」ではどのような情報が抽出されることを想定しているのでしょうか。

　⇒基本はまったく同一の処理を実施しています（そのため、質問では1つで記載させていただきました）

使用方法は以下の構成で考えています

期間内利用ユーザの量　÷　O365ユーザマスタ情報　×　100 =使用%

期間内利用ユーザの量　⇒　期間内にツールを使用した人数
O365ユーザマスタ情報の量　⇒　マスタに登録されている全人数

「利用ユーザ一時保管」と「O365契約ユーザ」はそれぞれ、
「取り込みCSV」から指定期間内の利用ユーザのみを抽出した結果を出力していて、
「期間内利用ユーザ」と「O365ユーザマスタ情報」で、それらをマスタから抽出しています

以上となります


	【79651】Re:指定期間でログインしたユーザーを知...

亀マスター - 18/2/26(月) 18:53 -


	引用なし

	パスワード

>csvのレコード数が少ないときには総定数が抽出されましたので、PCスペックのほうが原因のような気がしています
この手のコードでPCスペックが低いからと誤作動を起こすというのはあまり考えられませんね。絶対とは言えませんが。

現状では私にはすぐにわからないのですが、どこの段階でおかしくなっているのか、情報収集してみてはどうでしょうか。

例えば、処理件数に影響しそうな
count_daily
count_master
あたりが狙った値になっているのかを確認する。
（ブレイクポイントを設定するか、Stopステートメントで止めてローカルウインドで確認する、あるいはDebug.Printでイミディエイトウインドに出力する）

あと、最終的に出力される「期間内利用ユーザ」シートだけでなく、一時処理に使っている「利用ユーザ一時保管」が狙った件数になっているか、「取り込みCSV」シートの処理で.AutoFilterMode = Falseをしないで、フィルターがかかった状態が狙った件数になっているのか確認する。

などを試してみてください。


	【79654】Re:指定期間でログインしたユーザーを知...

amatsuno - 18/2/27(火) 17:56 -


	引用なし

	パスワード

▼亀マスターさん：

ありがとうございます。
Debug.Print
を使用して何箇所か確認してみます


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

569 / 13645 ﾂﾘｰ

←次へ　｜　前へ→

ページ：

┃

記事番号：

(SS)C-BOARD v3.8 is Free