[已解决]分割数据后求斜率

jzht

如附件，以e1数值分割A列数据，分割点取与e1数值最接近数的单元格，然后分别求A、B列数据的斜率1，斜率2，A列数据可出现附件中表1至表4的4中情况，分割点上下都有数据，分割点上面无数据，分割点下面无数据，分割点上下都无数据（只有分割点所在的1行数据）。数据区域中前2行和最后3行为固定项目，表1来说就是a1:b2和a13:b15,该数据不属于计算斜率数据。
现 想分别在H8、H9输入函数公式，分别求出斜率1、斜率2。

最佳答案

月排行榜 / 总排行榜

cbg2008

2014-6-13 19:37

1. =LINEST(OFFSET(B2,MATCH(1=1,A3:A22<=E1,),,COUNT(A:A)-MATCH(1=1,A3:A22<=E1,)+1),OFFSET(A2,MATCH(1=1,A3:A22<=E1,),,COUNT(A:A)-MATCH(1=1,A3:A22<=E1,)+1),,FALSE)
   
2. =LINEST(OFFSET(B3,,,MATCH(1=1,A3:A22<=E1,)),OFFSET(A3,,,MATCH(1=1,A3:A22<=E1,)),,FALSE)

复制代码

表1为什么不包含第12行数据呢？ 新建 Microsoft Office Excel 工作表 (3).zip (10.62 KB, 下载次数: 1)

2014-6-13 19:37 上传

点击文件名下载附件

跳转到最佳答案楼层

cbg2008

1. =LINEST(OFFSET(B2,MATCH(1=1,A3:A22<=E1,),,COUNT(A:A)-MATCH(1=1,A3:A22<=E1,)+1),OFFSET(A2,MATCH(1=1,A3:A22<=E1,),,COUNT(A:A)-MATCH(1=1,A3:A22<=E1,)+1),,FALSE)
   
2. =LINEST(OFFSET(B3,,,MATCH(1=1,A3:A22<=E1,)),OFFSET(A3,,,MATCH(1=1,A3:A22<=E1,)),,FALSE)

复制代码

表1为什么不包含第12行数据呢？ 新建 Microsoft Office Excel 工作表 (3).zip (10.62 KB, 下载次数: 1)

2014-6-13 19:37 上传

点击文件名下载附件

jzht

哦，感谢楼上的回答，表一应该是包含12行的数据的，我搞错了。
楼上的回复，经验证，可以达到目的，再次感谢。

jzht

帖子中你回复的最佳答案，虽然基本达到目的，但我验证，如果最近数比指定数大时就不能达到目的了，譬如帖子中附件e1为35.5时，最接近数应为A4=36，此时用你的答案就达不到目的了，求帮修改下公式达到目的，要求一个函数公式做到，最近数可以比指定数大，也可以比指定数小

cbg2008

jzht 发表于 2014-6-16 17:47
帖子中你回复的最佳答案，虽然基本达到目的，但我验证，如果最近数比指定数大时就不能达到目的了，譬如帖子 ...

那35的时候近似数为什么是34，而不是36呢

jzht

当初也想过这个情况，最终决定如果有两个绝对值一样的就选个小的而已

cbg2008

jzht 发表于 2014-6-16 20:34
当初也想过这个情况，最终决定如果有两个绝对值一样的就选个小的而已

1. =LINEST(OFFSET(B2,LOOKUP(1,0/(MIN(ABS(IFERROR(E1-A3:A12,9^9)))=ABS(E1-A3:A12)),ROW(1:10)),,COUNT(A:A)-LOOKUP(1,0/(MIN(ABS(IFERROR(E1-A3:A12,9^9)))=ABS(E1-A3:A12)),ROW(1:10))+1),OFFSET(A2,LOOKUP(1,0/(MIN(ABS(IFERROR(E1-A3:A12,9^9)))=ABS(E1-A3:A12)),ROW(1:10)),,COUNT(A:A)-LOOKUP(1,0/(MIN(ABS(IFERROR(E1-A3:A12,9^9)))=ABS(E1-A3:A12)),ROW(1:10))+1),,FALSE)
   
2. =LINEST(OFFSET(B3,,,LOOKUP(1,0/(MIN(ABS(IFERROR(E1-A3:A12,9^9)))=ABS(E1-A3:A12)),ROW(1:10))),OFFSET(A3,,,LOOKUP(1,0/(MIN(ABS(IFERROR(E1-A3:A12,9^9)))=ABS(E1-A3:A12)),ROW(1:10))),,FALSE)

复制代码