在 Excel 中查询每个区间并排序

【原帖】

I am stumped with the following problem and not sure how to accomplish it in excel. Here is an example of the data:

 A            B

 1 Date        Stock_Return

 2 Jan-95     -5.2%

 3 Feb-95     2.1%

 4 Mar-95     3.7%

 5 Apr-95     6.9%

 6 May-95     6.5%

 7 Jun-95    -5.6%

 8 Jul-95     6.6%

 9 Aug-95     6.2%

What I would like is to have the dates returned which fall within a certain return range and sorted from low to high.

For example:

 1        2       3      4      5

   Below -7%        0        0       0      0      0

  -7% to -5%        Jun-95   Jan-95  0      0      0

  -5% to -3%        0        0       0      0      0

  -3% to 0%         0        0       0      0      0

  0% to 3%          Feb-95   0       0      0      0

  3% to 5%          Mar-95   0       0      0      0

  5% to 7%          Aug-95   May-95  Jul-95 Apr-95 0

I thought Index and Match might make the most sense but when I drag across columns it doesn’t work. Any help is very much appreciated.

【回答】

这是个过程性计算问题：设计多个连续的区间，依次（循环）取每个区间，按区间查询原数据，对查询结果排序，将排序结果累积到一个二维表里。

前2个答复很接近正解了，但都差一点，都卡在排序问题上。之所以这么巧合，是因为前2答复都用试图用公式解决问题，而公式不适合过程较多的计算。

脚本语言才适合。

能够方便操作Excel的脚本语言，除了VB for  Excel之外，还有Python  pandas和esProc SPL。VB面向程序员，pandas安装部署有点复杂，SPL相对简单些。具体到这个问题，可以这样写：

	A	B	C	D
1	=file("D:\data.xlsx").xlsimport@t()
2	=[-inf(),-0.07,-0.05,-0.03,0,0.03,0.05,0.07]
3	=create(range,1,2,3,4,5)			/空结果集
4	for A2.len()-1	=A2(#A4)	=A2(#A4+1)	/取每个区间
5		=A1.select(Stock_Return>B4 &&   Stock_Return<=C4).sort(Stock_Return)		/查询并排序
6		=A3.record([B4/ "to" /C4]|B5.(Date))		/追加到结果集
7	=file("D:\result.xlsx").xlsexport@t(A3)

SPL很擅长处理xls/csv上的运算，这里：» Might Script on File - RAQSOFT 有更多资源。