Re: [問題] EXCEL抓取最接近日期的資料

看板Office作者JieJuen (David)時間17年前 (2009/02/03 15:34)推噓3(3推 0噓 16→)

留言19則, 2人參與討論串2/4 (看更多)

有數種方法以下先用一個需要排序但公式較簡單的方法 A B C D E 公司代號購併日期 100 02/03/2009 100|02/03/2009 60 51 200 12/15/2009 200|12/15/2009 300 07/26/2009 300|07/26/2009 C2 =A2&"|"&B2 假設股價資料如下 G H I J 公司代號日期股價 100 02/01/2009 55 100|02/01/2009 100 02/02/2009 60 100|02/02/2009 100 02/03/2009 34 100|02/03/2009 100 02/04/2009 51 100|02/04/2009 200 . . 200|. 200 . . 200|. J2 =G2&"|"&H2 需依J欄排序，即按公司代號排再按日期排前1天股價 D2 =INDEX(I:I,MATCH(C2,J:J)-COUNTIF(J:J,C2)) 後1天股價 F2 =INDEX(I:I,MATCH(C2,J:J)+1) 其中MATCH(C2,J:J)會找到當天的位置若當天不存在找到前一筆詳見說明檔若不排序使用陣列公式以Ctrl+Shift+Enter結尾前一天日期 C10 =MAX(IF(IF($G$2:$G$7=$A10,$H$2:$H$7)<$B10,$H$2:$H$7)) 股價 D10 =MAX(IF($G$2:$G$7=$A10,IF($H$2:$H$7=C10,$I$2:$I$7))) 後一天日期 E10 =MIN(IF(IF($G$2:$G$7=$A10,$H$2:$H$7)>$B10,$H$2:$H$7)) 股價 F10 =MAX(IF($G$2:$G$7=$A10,IF($H$2:$H$7=E10,$I$2:$I$7))) 股價是依找到的日期去找股價如果同一天有兩個以上的股價找最高的(故用max) 但不應有兩個以上同一天的股價(同公司) 所以就是會找到該天的股價 http://2y.drivehq.com/p/LocateTarget.xls ※ 引述《sshaq (sshaq)》之銘言： : (若是和其他不同軟體互動之問題請記得一併填寫) : 軟體:EXCEL : 版本:2007 : 由於論文上的需要必須要整理出最接近公司宣告購併那天的前後兩天股價 : 例如:A公司在02/03/2009宣告購併 : 那我就要抓出A公司 02/02/2009與02/04/2009這兩天的股價 : 但如果02/02/2009沒資料的話就往前1天還是沒有的的話再往前一天 : 直到有股價資料為止而02/04/2009也是往後推直到有資料為止 : 但是比較麻煩的是因為公司的數目很多一個一個找要很久 : 我的EXCEL有兩個工作表 : 第一個是每間公司宣告購併的日期 : 第二個是所有公司每天的股價 : 例如:(第一個工作表) (第二個工作表) : 公司代號購併日期公司代號日期股價 : 100 02/03/2009 100 02/01/2009 55 : 200 12/15/2009 100 02/02/2009 60 : 300 07/26/2009 100 02/03/2009 34 : . . 100 02/04/2009 51 : . . 200 . . : . . 200 . . : 所以以公司代號為100的公司為例因為其購併日期是02/03/2009 : 所以要從第二個工作表中先找出公司代號為100的公司 : 然後再把02/03/2009前後兩天的股價抓出來 : 也就是把02/02/2009的60 與02/04/2009的51抓出來 : 不曉得有什麼辦法可以解決這個問題謝謝 -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 218.164.48.84

推

sshaq

02/03 16:23, , 1^F

02/03 16:23, 1^F

推

sshaq

02/03 16:30, , 2^F

02/03 16:30, 2^F

→

sshaq

02/03 16:33, , 3^F

02/03 16:33, 3^F