|
excelperfect 引言：本文的练习整理自chandoo.org。多练习，这是我们从小就在使用的学习方法。在练习的过程中，认真思考，不断尝试，以此来磨练自己的公式与函数应用技能，也让研究Excel的大脑时刻保持着良好的状态。同时，想想自己怎么解决这个问题，看看别人又是怎样解决的，从而快速提高Excel公式应用水平。 本文提供的公式很奇怪，所使用的函数也不常用，但对于数据分析来说值得研究。 本次的练习是：示例数据如下图1所示。
图1 第3行是投资额，第5行是投资回收的累计现金流。根据第5行的数据求投资回收期。 要求不能使用易失性函数。 请写下你的公式。 注：投资回收期的计算公式： =n + (ABS(a)/b) 其中，n代表出现第一个正的累计现金流之前的年份；a代表最后一个负的累计现金流值；b代表第一个正的累计现金流对应年份的投资额。 因此，对于本示例来说，应该是： =2+(ABS(C5)/D3) =2+(ABS(-65900)/95830) =2.687676 解决方案 公式1： =LOOKUP(0,A5:F5,A2:F2-A5:F5/B3:F3) 公式2： =PERCENTRANK(A5:F5,,20)*MAX(A2:F2) 公式3：数组公式。 =FORECAST(,IF((LOOKUP(,A5:F5,A2:F2)-A2:F2+0.5)^2<1,A2:F2),A5:F5) 公式4：数组公式。 =PERCENTRANK.INC(PROB(TRANSPOSE(COLUMN(A3:F3))*{1,0,0,0},TRANSPOSE(A3:F3)*{1,0,0,0}+{0,9,-9,1}^99,1,TRANSPOSE(COLUMN(A3:F3))),0,6)*F2 或者： =PERCENTRANK.INC(SUMIF(A2:F2,'<='&A2:F2,A3:F3),0,6)*F2 或者： =PERCENTILE.INC(A2:F2,PERCENTRANK.INC(SUMIF(A2:F2,'<='&A2:F2,A3:F3),0,20)) |
|
|
