跳到主要內容

 如何計算Excel中連續正/負數的最大數量？

如果您有一個包含正負數字的數據列表，現在，如下面的屏幕截圖所示，您要計算連續正負數字的最大數目，那麼如何在Excel中處理此任務？

用數組公式計算連續正負數的最大數量

用數組公式計算連續正負數的最大數量

 

 

要獲得連續的正數和負數的最大數目，請應用以下數組公式：

計算連續正數的最大數量：

在要獲取結果的單元格中輸入以下公式：

=MAX(FREQUENCY(IF(A2:A20>0,ROW(A2:A20)),IF(A2:A20<=0,ROW(A2:A20))))，然後按 Ctrl + Shift + Enter 鍵，您將根據需要獲得正確的結果，請參見屏幕截圖：

計算連續負數的最大數量：

在要獲取結果的單元格中輸入以下公式：

=MAX(FREQUENCY(IF(A2:A20<0,ROW(A2:A20)),IF(A2:A20>=0,ROW(A2:A20))))，然後按 Ctrl + Shift + Enter 同時輸入密鑰，您將根據需要獲得結果，請參見屏幕截圖：

備註：在以上公式中， A2：A20 是要使用的單元格範圍的列表。

 

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！

 

Comments (20)
Rated 5 out of 5 · 1 ratings
Siddhardha
Siddhardha
This comment was minimized by the moderator on the site
PPPPPPPQQQQPPPPQQPPQQPPPPPP

I have a data like above. I have applied the formula that you have suggested. It was working fine. Answer was 7.

={MAX(FREQUENCY(IF(A4:AA4=P, COLUMN(A4:AA4)),IF(A4:AA4<>P, COLUMN(A4:AA4))))}
But now my requirement is this formula should consider only the last 10 cells from the formula applied cells. and the answer should come as 6.
Is it Possible.
Please suggest a way
Reply
0
0
Naveen
Naveen
This comment was minimized by the moderator on the site
Hi,
This was very useful. Thank You very much !!
A question, what if in the data array I want to ignore blanks or zeros. Like the if the next number after blank or zero is positive it should add in frequency

Thanks for your help
Reply
0
0
skyyang
skyyang     Naveen
This comment was minimized by the moderator on the site
Hello, Naveen
To solve your problem, please try the below formula:
=MAX(FREQUENCY(IF(A2:A20>=0,ROW(A2:A20)),IF(A2:A20<0,ROW(A2:A20))))

After copying and pasting the formula, please remember to press Ctrl + Shift + Enter keys together to get the correct result.
Please try, hope it can help you!
Reply
Report
0
0
손님
손님
This comment was minimized by the moderator on the site
안녕하세요. 감사합니다. 데이터 필터된 상황의 경우에도 연속 손실이나 이익 수를 계산할 수 있는지 궁금합니다. 그리고 데이터 필터된 상횡의 평균 연속손실 수를 계산하는 방법은 있습니까?
Reply
0
0
Rated 5 out of 5
Will
Will
This comment was minimized by the moderator on the site
Wonderful, thank you! I'm using your formula now, but I have a question: Is there any way to further filter the data so that it only counts consecutive positive/negative cells IF they share a value in a different column? For example, if, using your example above, in Column B (next to your Number List in Column A) the cells were filled with either the letter 'A' or 'B' in random order, what formula would you use to determine the max number of positive cells that are ALSO consecutive A's in Column B?
Reply
0
0
An
An
This comment was minimized by the moderator on the site
It's amazing, just wondering if there a way to highlight consecutives?
Reply
0
0
Pablo Nunez
Pablo Nunez
This comment was minimized by the moderator on the site
I get an #Value output if there are only a streak of positive numbers only. Also, I get the same error, if there is only negative streak of numbers. It works fine when there are at least 1 positive and at least 1 negative number on the column. Does anybody knows how to fix this issue. I am using open office.Which uses the same commands, except for the " ; " instead of " , " as a separator.
Reply
0
0
skyyang
skyyang     Pablo Nunez
This comment was minimized by the moderator on the site
Hello, Pablo,
The formulas work well in my wotksheet when there are a list of positive or negative numbers, do you press Ctrl + Shift + Enter keys together to get the result?
View Attachments (1 / 5)
Reply
Report
0
0
Adrien
Adrien
This comment was minimized by the moderator on the site
Hello,
Tank you for this post.I'm trying to get the average number of consecutive negative number from a row.I've been trying to do it myself but I can't figure it out.
Would you mind helping me ?
Thanks !
Reply
0
0
Finansal Modeller
Finansal Modeller
This comment was minimized by the moderator on the site
So, usefull context. I was jus making a video for my Youtube Channel. When i googeled some key word, i came across with your formula. First of all, thank you. And secondly i will give reference to this website for the formulas explanation. I have not so many subscriber, but it may give your website a littele bit more traffic. Thank you and have nice day.
Reply
0
0
Joan
Joan
This comment was minimized by the moderator on the site
Hi, thank you very much for the information! It's very useful :) I wondering if is posible to add up the Max Number of consecutive negatives? I will apreciate a lot if you can help me. Thank you!!
Reply
0
0
skyyang
skyyang     Joan
This comment was minimized by the moderator on the site
Hi, Joan,
To solve your problem, please do with the following array formula:

Sum max number of consecutive positive numbers: (Note: D2 is the cell of the consecutive positive numbers you have counted by the using the formula in this article, please press Ctrl + Shift + Enter keys together)
=MAX((COUNTIF(OFFSET(A2:A18,ROW(A2:A18)-ROW(A2),0,D2),">=0")=D2)*SUBTOTAL(9,OFFSET($A$2:$A$18,ROW(A2:A18)-ROW($A$2),0,D2)))

Sum max number of consecutive negative numbers: (Note: E2 is the cell of the consecutive negative numbers you have counted by the using the formula in this article, please press Ctrl + Shift + Enter keys together)
=MIN((COUNTIF(OFFSET(A2:A18,ROW(A2:A18)-ROW(A2),0,E2),"<0")=E2)*SUBTOTAL(9,OFFSET($A$2:$A$18,ROW(A2:A18)-ROW($A$2),0,E2)))

Please try, hope it can help you!
View Attachments (1 / 5)
Reply
Report
0
0
Abdul Latif
Abdul Latif
This comment was minimized by the moderator on the site
how do you count consecutive occurrences of two different values or numbers
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations