[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『基準日の前後の近い日にちを取得する方法について』(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
A列 B列 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
A列 B列 C列 D列 E列 F列 G列 (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.