Excel: Match and Index based on range

https://stackoverflow.com/questions/64164736/excel-match-and-index-based-on-range

【原帖】

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相对简单些。具体到这个问题，可以这样写：

SPL很擅长处理xls/csv上的运算，这里：http://www.raqsoft.com/p/script-over-csv-xls 有更多资源。