如何计算利润增加？ [英] how to count increasing profit streak?
问题描述
如果您希望这个公式是动态的，即在另一年添加新列后，您希望公式正常工作，请考虑以下内容。
在列K 输入一些虚拟字符，说 x ，然后在单元格中输入以下公式L2
= IFERROR（COLUMN（K2）-COLUMN（INDEX（B2：K2， （9.99E + 307，IF（B2：K2> A2：J2 = FALSE，1，）））） - 1,0）
和单元格M2
= IFERROR（COLUMN（K2）列（INDEX（B2：K2，MATCH（9.99E + 307，IF（B2：K2> = A2：J2 = FALSE，1， ）））） - 1,0 ）
这两个公式都是数组公式。根据需要拖动/复制。见图片供参考。
现在，当你选择列K 并插入新列，公式将相应更改。
图像中的公式（公式栏）
编辑： 避免计数零的条件 0-0-0-0
对于稳定利润年数在中使用以下公式单元格M2
= IFERROR（COLUMN（J2）-COLUMN（INDEX（B2：J2，，MATCH（9.99E + 307，IF（（（ B2：J2> = A2：I2）*（B2：J2？0））= 0,1，））））））0）
这是一个数组公式。
VBA解决方案：
Option Explicit
Sub Demo（）
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastRow As Long，lastCol As Long，rIndex As Long，cIndex As Long
Dim increaseCnt As Long，stableCnt As Long
Dim ws As Worksheet
Dim isSteady As Boolean，isZero As Boolean
设置ws = ThisWorkbook.Worksheets（Sheet2） '更改为您的数据表
与ws
lastRow = .Cells（.Rows.Count，A）。End（xlUp）.Row最后一行数据使用列A
lastCol = .Cells（2，.Columns.Count）.End（xlToLeft）.Column'最后一列，数据使用行2
increaseCnt = 0
stableCnt = 0
isSteady = False
对于rIndex = 2要lastRow循环通过行2到最后一行
对于cIndex = lastCol到2 Step -1'循环到最后一列列2
如果.Cells（rIndex，cIndex）<> NA然后'检查NA
如果.Cells（rIndex，cIndex）<> 0然后'cheeck为0
如果.Cells（rIndex，cIndex）= .Cells（rIndex，cIndex - 1）然后'比较细胞的稳定计数
steadyCnt = steadyCnt + 1'增量steadyCnt
isSteady = True'set steady flag true
ElseIf .Cells（rIndex，cIndex）> .Cells（rIndex，cIndex - 1）然后'比较单元格增加计数
如果Not isSteady然后
increaseCnt = increaseCnt + 1'increment increaseCnt
steadyCnt = steadyCnt + 1'increment stableCnt
ElseIf .Cells（rIndex，cIndex）< 0然后'检查单元格是0
steadyCnt = steadyCnt + 1'增量stableCnt
结束如果
Else
退出为'退出循环
结束如果
Else
退出为'退出循环
结束如果
结束如果
下一个cIndex
.Cells（rIndex，lastCol + 2）= increaseCnt'显示增加C $ $ $ b .Cells（rIndex，lastCol + 3）= steadyCnt'显示stableCnt
increaseCnt = 0
stableCnt = 0
isSteady = False
下一个rIndex
结束
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
查看图片供参考。
I want to count how many years a company has increased the profit from now and in a continuing streak.
In the picture it has to count until 2014 because the profits have not been increased relative to 2013. For steady profits, it has to count until 2012 because in 2011 the profits were higher than after that. So these are the two calculations that should be done. Import is that it shouldn't count rows of years where the profits were 0 - 0 - 0 - 0 (=steady, but 0 is no profit at all).
So it's like quality criteria to see how well a company is doing at its business. So if there is one interruption then everything before is irrelevant.
And the formulas should change dynamically, so if there will be data for 2018, 2019 ... then it should automatically count from the newest data. (there will be space between the formula column and the column for the latest year for more years)
Explanation for this new question: I needed a more flexible and complex solution for my problem and wanted to differentiate that in this question. So this may help others with similar problems.
To calculate Years of Increases enter the following formula in Cell L2
=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>A2:I2=FALSE,1,"")))),0)
and to calculate Years of Steady Profits enter below formula in Cell M2
=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>=A2:I2=FALSE,1,"")))),0)
Both the above formulas are array formula so commit by pressing Ctrl+Shift+Enter. Drag/Copy down as required. See image for reference.
In case you want this formula to be dynamic i.e. after adding new column for another year you want formula to work correctly then consider the following.
In Column K enter some dummy character say x and then enter the following formula in Cell L2
=IFERROR(COLUMN(K2)-COLUMN(INDEX(B2:K2,,MATCH(9.99E+307,IF(B2:K2>A2:J2=FALSE,1,""))))-1,0)
and in Cell M2
=IFERROR(COLUMN(K2)-COLUMN(INDEX(B2:K2,,MATCH(9.99E+307,IF(B2:K2>=A2:J2=FALSE,1,""))))-1,0)
Both formulas are array formula. Drag/Copy down as required. See image for reference.
Now when you select Column K and insert new column, formulas will change accordingly.
Notice formula (formula bar) in the image below.
EDIT : Avoid counting streak of zeros 0-0-0-0
For Years of Steady Profits use following formula in Cell M2
=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(((B2:J2>=A2:I2)*(B2:J2<>0))=0,1,"")))),0)
This is an array formula.
VBA Solution :
Option Explicit
Sub Demo()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastRow As Long, lastCol As Long, rIndex As Long, cIndex As Long
Dim increaseCnt As Long, steadyCnt As Long
Dim ws As Worksheet
Dim isSteady As Boolean, isZero As Boolean
Set ws = ThisWorkbook.Worksheets("Sheet2") 'change to your data sheet
With ws
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'last row with data using Column A
lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column 'last column with data using Row 2
increaseCnt = 0
steadyCnt = 0
isSteady = False
For rIndex = 2 To lastRow 'loop through row 2 to last row
For cIndex = lastCol To 2 Step -1 'loop through last column to column 2
If .Cells(rIndex, cIndex) <> "NA" Then 'check for NA
If .Cells(rIndex, cIndex) <> 0 Then 'cheeck for 0
If .Cells(rIndex, cIndex) = .Cells(rIndex, cIndex - 1) Then 'compare cells for steady count
steadyCnt = steadyCnt + 1 'increment steadyCnt
isSteady = True 'set steady flag true
ElseIf .Cells(rIndex, cIndex) > .Cells(rIndex, cIndex - 1) Then 'compare cells for increase count
If Not isSteady Then
increaseCnt = increaseCnt + 1 'increment increaseCnt
steadyCnt = steadyCnt + 1 'increment steadyCnt
ElseIf .Cells(rIndex, cIndex) <> 0 Then 'check for cell is 0
steadyCnt = steadyCnt + 1 'increment steadyCnt
End If
Else
Exit For 'exit for loop
End If
Else
Exit For 'exit for loop
End If
End If
Next cIndex
.Cells(rIndex, lastCol + 2) = increaseCnt 'display increaseCnt
.Cells(rIndex, lastCol + 3) = steadyCnt 'display steadyCnt
increaseCnt = 0
steadyCnt = 0
isSteady = False
Next rIndex
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
See image for reference.
这篇关于如何计算利润增加？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
