十大最近记录 [英] Ten Nearest Records

问题描述

目前，我用下面的公式捕捉到预测结束日期（读作交易日）为最近10个已完成交易。

Currently, I am using the following formula to capture the Forecast Close Date (read as "Deal Date") for the 10 most recent closed deals.

{=LARGE(IF(All_ JV_Forecast__2[Stage]="Closed Affirmative - Contract Signed",All_ JV_Forecast__2[Forecast Close Date],""),{1;2;3;4;5;6;7;8;9;10})}

我想适应这个公式捕捉到的十个最亲密的交易，即。十大交易有最接近今天预测结束日期。

I would like to adapt this formula to capture the ten closest deals—i.e. the ten deals that have a Forecast Close Date nearest to today.

请注意，一些交易已经预测关闭在过去的日期。所以，我专注于那些预测关闭日期比现在更大，但最近的10。我需要这些根据接近今天订购。

Note that some deals have Forecast Close Dates in the past. So I’m focused on the 10 that have Forecast Close Dates greater than today but are the nearest. And I need these ordered according to proximity to today.

我在这里难倒，任何想法？

I’m stumped here, any thoughts?

推荐答案

的<一个href=\"https://support.office.com/en-us/article/AGGREGATE-function-43B9278E-6AA7-4F17-92B6-E19993FA26DF\"相对=nofollow>汇总功能应该能够处理这个问题。

The AGGREGATE function should be able to handle this.

=AGGREGATE(15, 6, ajvf2[stage]/(ajvf2[stage]>B$1), ROW(1:1))

我已经重新命名表ajvf2减少混淆。

I've renamed your table to ajvf2 to reduce obfuscation.

这篇关于十大最近记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！