[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『基準日の前後の近い日にちを取得する方法について』（N-work）
以下の様なA列とB列が1,000程度ある「Sheet01」のデータから
「Sheet02」のB/C/E/F/Gの列に
以下の条件の数値を拾うための良い方法はないでしょうか？
現状は手作業で取得しておりまして時間的にも侭ならぬ状況です。
お力添えのほど宜しく御願い致します。
B列 ： A列の数値が同じであってD列の数値から過去に遡って2番目に近い数値
C列 ： A列の数値が同じであってD列の数値から過去に遡って1番目に近い数値
E列 ： A列の数値が同じであってD列と同じ数値
F列 ： A列の数値が同じであってD列の数値から未来に進んで1番目に近い数値
G列 ： A列の数値が同じであってD列の数値から未来に進んで2番目に近い数値
--------------------------------------
※ 「Sheet01」のA列B列、「Sheet02」のA列D列は最初から入力されてます。
--------------------------------------
Sheet01
Ａ列 Ｂ列 1131 20160301 1131 20160401 1131 20110401 1131 20170401 1131 20160201 1131 20160101 1131 20150101 2136 20160731 2136 20160802 2136 20160630 2136 20160630 2136 20160530 3333 20160101 3333 20150102 3333 20160102 7536 20150901 7536 20150801 7536 20150731 9999 20170901
Sheet02
Ａ列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 Ｆ列 Ｇ列 （2番目に近い） (1番近い) ■基準■ 同日 1番近い 2番目に近い 1131 20160101 20160201 20160301 20160301 20160401 20170401 2136 20160530 20160630 20160631 20160731 20160802 3333 20150102 20151231 20160101 20160102 7536 20150731 20150801 20150901 20150901 9999 20160901 20170901
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
こんばんわ。
多分以下の式で行けると思います。 Sheet01のデータが100行までにしています。 もっと多ければ調整して下さい。 でも配列を扱ってるので、参照範囲が大きすぎると重くなります。
B2 =IF(C2="","",IFERROR(AGGREGATE(14,6,1/((Sheet01!A$1:A$100=A2)*(Sheet01!B$1:B$100<C2))*Sheet01!B$1:B$100,1),"")) C2 =IFERROR(AGGREGATE(14,6,1/((Sheet01!A$1:A$100=A2)*(Sheet01!B$1:B$100<D2))*Sheet01!B$1:B$100,1),"") E2 =IF(COUNTIFS(Sheet01!A:A,A2,Sheet01!B:B,D2),D2,"") F2 =IFERROR(AGGREGATE(15,6,1/((Sheet01!A$1:A$100=A2)*(Sheet01!B$1:B$100>D2))*Sheet01!B$1:B$100,1),"") G2 =IFERROR(AGGREGATE(15,6,1/((Sheet01!A$1:A$100=A2)*(Sheet01!B$1:B$100>F2))*Sheet01!B$1:B$100,1),"") それぞれ下にオートフィル
数式にゴミが混ざってたので修正しました。(8:17)
（sy） 2016/08/23(火) 01:03
syさんの数式、凄いですね。 集計関数を使えば、こんな複雑な条件でも書けてしまうとは…。 私はマクロ例を書いてみます。マクロでも結構複雑です。
Sub test()
Dim AR As Object
Dim wk1 As Worksheet
Dim wk2 As Worksheet
Dim i As Long
Dim j As Long
Dim iMax As Long
Dim vw As Variant
Set AR = CreateObject("System.Collections.ArrayList")
Set wk1 = Sheets("Sheet01")
Set wk2 = Sheets("Sheet02")
iMax = wk1.Cells(wk1.Rows.Count, "A").End(xlUp).Row
For i = 2 To wk2.Cells(wk2.Rows.Count, "A").End(xlUp).Row
AR.Clear
vw = wk2.Cells(i, "A").Value
For j = 1 To iMax
If vw = wk1.Cells(j, "A").Value Then
AR.Add wk1.Cells(j, "B").Value
End If
Next j
AR.Sort
vw = wk2.Cells(i, "D").Value
For j = 0 To AR.Count - 1
If vw <= AR(j) Then
If 1 < j Then
wk2.Cells(i, "B").Value = AR(j - 2)
End If
If 0 < j Then
wk2.Cells(i, "C").Value = AR(j - 1)
End If
If vw = AR(j) Then
wk2.Cells(i, "E").Value = AR(j)
If j < AR.Count - 1 Then
wk2.Cells(i, "F").Value = AR(j + 1)
End If
If j < AR.Count - 2 Then
wk2.Cells(i, "G").Value = AR(j + 2)
End If
Else
wk2.Cells(i, "F").Value = AR(j)
If j < AR.Count - 1 Then
wk2.Cells(i, "G").Value = AR(j + 1)
End If
End If
Exit For
End If
Next j
Next i
Set AR = Nothing
End Sub
（???） 2016/08/23(火) 10:42
syさんの関数を使ったところ即終わりました。
有難いと思うと同時に手作業でやっていたことが泣けてきました。
本音をいうと返信がないんじゃないかと思ってましたが、
凄い関数をご紹介頂けて本当に嬉しいです。
重ねて御礼申し上げます。
（NｰWork） 2016/08/23(火) 14:46
To ???さん
2010からAGGREGATE関数が使えるようになって非常に便利ですよね。 エラーも無視できるし、欲を言えば第4引数が必要ない集計方法の時も範囲だけでなく配列が使用出来たら良いなと思う時はありますけど。
（sy） 2016/08/23(火) 21:05
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.