提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
注册账户
x

or

如何计算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 是您想要使用的单元格范围的列表。



推荐的Excel生产力工具

Kutools for Excel帮助您提前完成工作,并从人群中脱颖而出

  • 超过300强大的高级功能,专为1500工作场景设计,通过70%提高生产力,让您有更多时间照顾家庭和享受生活。
  • 不再需要记忆公式和VBA代码,从现在起让你的大脑休息一下。
  • 成为3分钟的Excel专家,复杂和重复的操作可以在几秒钟内完成,
  • 每天减少成千上万的键盘和鼠标操作,现在告别职业病。
  • 110,000高效人才和300 +世界知名公司的选择。
  • 60-day full功能免费试用。 60天退款保证。 2多年的免费升级和支持。

将选项卡式浏览和编辑带到Microsoft Office,远比浏览器的选项卡强大

  • Office选项卡专为Word,Excel,PowerPoint和其他Office应用程序设计:Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ryan · 5 months ago
    How do you do this with multiple rows? say A1:A10 and C1:C10 ???
    • To post as a guest, your comment is unpublished.
      skyyang · 4 months ago
      Hello, Ryan,
      Sorry for that, this formula is only applied to single column or row.
      If you have other good ideals, please comment here.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Ivan · 11 months ago
    Not working! Gives the max number of positive results not consecutive ones
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hi, Ivan,
      The above formula only can help you to count the max number of the positive or negative numbers, it can't extract the consecutive cells.
      If you have the good ways, please comment here.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Leandro · 1 years ago
    Excelente! Funcionou perfeitamente. E se quisermos somar os números de cada intervalo, como ficaria?
  • To post as a guest, your comment is unpublished.
    Shailendra Agrawal · 1 years ago
    I want the consecutive positive numbers from last cell of one column in which data is entered. I will have many column i.e. A,B,C,D,E,F etc
  • To post as a guest, your comment is unpublished.
    Shailendra Agrawal · 1 years ago
    I want the consecutive positive numbers from last of one column. I will have many column i.e. A,B,C,D,E,F etc