[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『類似した値を検索したい』（みっひー）
'09/1/1 100
'09/1/2 130
'09/1/3 185
'09/1/4 110
・
・
・
という昨年度の実績のデータがあり
他方で
'10/1/1 101
'10/1/2 103
'10/1/3 200
・
・
という今年度のデータがあるのですが，類似するデータをすばやく検索して表示させるにはどうしたらよいのでしょうか。
「各日付ごとに，（年度は無視して）１週間以内の幅で，差が〇〇以内の実績があればそれを表示する」というふうにしたいのですけれど。
たとえば，今年の７月１３日に１５０の実績が上がったとして，昨年の７月６日から７月２０日までの間に，１４５〜１５５くらいの実績が上がったところはないかな〜，という感じです。
７月１３日の分だけならむりやりｉｆとかの関数を並べて何とかなるかもと思うのですが，これを今年の１月１日から全部の日についてやるとなると初心者の私にはお手上げです(^_^;)。
エクセルは２００３です。
Pivotで行に日付、列にデータ項目、データにデータを個数等でセットして、 列のところで見たいデータを選択する（たとえば145から155のみ選択する）と、 該当日付がわかるのですが、いかがですか？
（こだぬき）
（みっひー）
回答ではありません、
データの日付は昇順になっているのでしょうか? また、日付に抜けている日は無いのでしょうか
>（年度は無視して）１週間以内の幅で データは複数年分あるのでしょうか? １週間とは 厳密に考えるのでしょうか ・該当日が存在しない(2/29)の場合どのような範囲になるのでしょう ・前年度の場合 各日付の372前から358日前の範程度でいいのでしょうか
>類似するデータをすばやく検索して表示させるにはどうしたらよいのでしょうか 有り、無しがわかればいいのでしょうか 該当日が複数の場合、すべての日にちを表示するということでしょうか
質問ばかりですいません、質問の返事によって回答も変わりますのであしからず
参考までに =SUMPRODUCT((A1:A10>各日付-372)*(A1:A10<各日付-358)*(B1:B10>実績-5)*(B1:B10<実績+5)) でおおよその該当日数が出ます
By しげちゃん
>Pivotで行に日付、列にデータ項目、データにデータを個数等でセットして、 >列のところで見たいデータを選択する（たとえば145から155のみ選択する）と、 >該当日付がわかるのですが、いかがですか？
Pivotで設定した日付を週単位で見るというのは？ http://www.relief.jp/itnote/archives/002672.php
（うっかりさん）
ウェブ環境のない（もうすぐ出来るかも・・;）職場に異動してきて、、 仕事の合間に時間があったので、トライしてみました。。だけど、もう見てないかなぁーー? バックアップをとってから実行してみて。。
<Sheet1>シート・・・検索対象範囲（過去の実績分） ｜ A ｜ B ｜ 01｜日付 ｜実績｜ 02｜2009/9/1｜ 107｜ 03｜2009/9/2｜ 108｜ 04｜2009/9/3｜ 105｜ 05｜2009/9/4｜ 136｜ 06｜2009/9/5｜ 145｜ 07｜2009/9/6｜ 141｜ 08｜2009/9/7｜ 116｜ 09｜2009/9/8｜ 131｜ 10｜2009/9/9｜ 107｜
<2010data>シート・・・（現在のシート）検索日または実績を選択し、「ダブルクリック」する。。 ｜ A ｜ B ｜ 01｜日付 ｜実績｜ 02｜2010/9/1｜ 107｜ 03｜2010/9/2｜ 108｜ 04｜2010/9/3｜ 105｜ 05｜2010/9/4｜ 136｜ 06｜2010/9/5｜ 145｜ 07｜2010/9/6｜ 141｜ 08｜2010/9/7｜ 116｜
<検索>シート・・・検索条件を手入力し、抽出する。。 ｜ A ｜ B ｜ C ｜ D ｜ 01｜開始年 ｜2009｜日付 ｜実績｜ 02｜終了年 ｜2011｜2009/9/4 ｜ 136｜ 03｜開始日付｜9/3 ｜2009/9/6 ｜ 141｜ 04｜終了日付｜9/17｜2009/9/10｜ 139｜ 05｜基準値 ｜ 139｜2009/9/16｜ 139｜ 06｜増減 ｜ 5｜2009/9/17｜ 138｜ 07｜ ｜ ｜2010/9/8 ｜ 135｜ 08｜ ｜ ｜2011/9/6 ｜ 134｜
<<下準備>> ↓
1.<Sheet1>と<2010data>の日付データが→ '09/1/1 となっていると、「シリアル値」でなく「文字列」なので、
月をまたがった時などの、日付の計算が出来ません。。
2.上記の2つのシートで、それぞれ日付データの入っている "A列" を列ごと選択して、
メニューの「データ」→「区切り位置」→そのまま「次へ」→そのまま「完了」をクリックすると、「シリアル値」になります。
3.日付データの表題(A1)を "日付" と入力する。。
4.右隣の数値の表題(B1)を "実績" と入力する。。
<<運用>> 1.<2010data>の画面で、下記の「準備」マクロを実行します。 2.<2010data>の右側に<検索>のシートが作成されます。 3.<検索>B1〜B6を直接入力して、「検索実行」マクロを実行すると、その結果がこのシートに反映されます。
ここで、みっひーさんの希望する「仕様」どおりに運用するなら、、
※ <検索>シートが出来上がった状態で
※ <2010data>の "A列またはB列" のデータ内を「ダブルクリック」すると、、
<検索>B1〜B6には、、
"年"は<Sheet1>のすべてのデータの範囲で、→つまり、ここには過去何年分もデータを蓄積できる。。
"日付"は"＋−7日"を指定、
"基準値"は「ダブルクリック」した値、
"増減"は "5"
と条件を設定して、抽出を実行します。。
下記のコードをデータの入っている<2010data>のシートモジュールに貼り付けて、、
↓
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim myYear1 As Long
Dim myYear2 As Long
If Not Intersect(Target, Range("A:B")) Is Nothing Then
Cancel = True
With Sheets("Sheet1")
myYear1 = WorksheetFunction.Min(.Range("A:A"))
myYear2 = WorksheetFunction.Max(.Range("A:A"))
End With
With Worksheets("検索")
.Range("B1").Value = Year(myYear1)
.Range("B2").Value = Year(myYear2)
.Range("B3").Value = Month(Cells(Target.Row, 1).Value - 7) & "/" & Day(Cells(Target.Row, 1).Value - 7)
.Range("B4").Value = Month(Cells(Target.Row, 1).Value + 7) & "/" & Day(Cells(Target.Row, 1).Value + 7)
.Range("B5").Value = Cells(Target.Row, 2).Value
.Range("B6").Value = 5
End With
End If
Call 検索実行
End Sub
さらに、下記のコードを標準モジュールに貼り付けて、、
↓
Sub 準備()
Dim myWS As Worksheet
For Each myWS In Worksheets
If myWS.Name = "検索" Then
Application.Goto Reference:=Worksheets("検索").Range("B1"), scroll:=False
Exit Sub
End If
Next myWS
Worksheets.Add After:=ActiveSheet
ActiveSheet.Name = "検索"
Range("A1:A6").Value = WorksheetFunction.Transpose([{"開始年","終了年","開始日付","終了日付","基準値","増減"}])
Range("C1:L1").Value = [{"日付","実績","","日付","日付","実績","実績","","日付","実績"}]
Range("B3:B4").NumberFormatLocal = "@"
Range("F:L").EntireColumn.Hidden = True
Range("B6").Value = 5
Range("B1").Select
End Sub
Sub 検索実行()
Dim i As Byte
Dim myFind1 As Byte, myFind2 As Byte
Dim myYear1 As Integer, myYear2 As Integer
Dim myDay1 As String, myDay2 As String
Worksheets("検索").Activate
myYear1 = Range("B1").Value
myYear2 = Range("B2").Value
myDay1 = Range("B3").Value
myDay2 = Range("B4").Value
myFind1 = WorksheetFunction.Find("/", myDay1)
myFind2 = WorksheetFunction.Find("/", myDay2)
If Range("C2").Value <> "" Then
Range(Range("C2"), Range("C" & Rows.Count).End(xlUp)).Resize(, 2).Value = ""
End If
Range("H2").Value = ">=" & Range("B5").Value - Range("B6").Value
Range("I2").Value = "<=" & Range("B5").Value + Range("B6").Value
For i = 1 To myYear2 - myYear1 + 1
Range("F2").Value = ">=" & myYear1 + i - 1 & "/" & Mid(myDay1, 1, myFind1 - 1) & "/" & Right(myDay1, Len(myDay1) - myFind1)
Range("G2").Value = "<=" & myYear1 + i - 1 & "/" & Mid(myDay2, 1, myFind2 - 1) & "/" & Right(myDay2, Len(myDay2) - myFind2)
Sheets("Sheet1").Columns("A:B").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("F1:I2"), CopyToRange:=Columns("K:L"), Unique:=False
If Range("K2").Value <> "" Then
Range(Range("K2"), Range("K" & Rows.Count).End(xlUp).Resize(, 2)).Copy Cells(Rows.Count, 3).End(xlUp).Offset(1)
End If
Next i
Range("B5").Select
End Sub
↑
もし、過去のデータの入っているシート名が "Sheet1" でなく他の名称だったら、ここから上に8行目の
Sheets("Sheet1").Columns("A:B")→ Sheets("その名称").Columns("A:B")に変更!!
^^^^^^ ^^^^^^^^
さらに、最初のシートモジュールの上から6行目の With Sheets("Sheet1")→ With Sheets("その名称")に変更!!
すべて貼り付けたら、「準備」マクロから実行してみて。。 ^^^^^^ ^^^^^^^^
(kei)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.