使用数组公式检查类似的合同是否在过去12个月内结束 [英] Use an array formula to check if a similar contract ended in the last 12 months

问题描述

我目前正在排查我撰写的报告电子表格。我所写的逻辑是不行的，所以我以为我会在这里找到一些帮助。

I'm currently just troubleshooting a report spreadsheet I've written. The logic I had written isn't working, so I thought I'd see if I could ask for some help here.

本质上，我有一份合同清单，产品列表，结束和开始日期列表以及各种其他信息。我需要知道使用此信息的续订产品。

Essentially, I have a list of contracts, a list of products, a list of end and start dates, and various other pieces of information. I need to know if a product of a renewal using this information.

示例数据：

Product   Company Number    Start of Contract        End of contract     Contract ID     Include in this month's report
Fax            1234             10.09.2013              10.09.2014           1                      No
Fax            1234             10.09.2014              10.09.2015           2                      No     
Box            5678             11.01.2014              30.04.2015           3                      No
Box            5678             01.05.2015              11.01.2016           4                     Yes
Fax            5678             01.05.2015              01.05.2016           5                     Yes
Cup            9876             03.05.2015              03.05.2016           6                     Yes

我想做的是使用这些数据来处理文件是否ñ以现有文件的产品，新产品或更新为前提。

What I want to do is work out using this data whether the file is new to product, new to business, or renewal of an existing file.

本月报告中是否包含合同背后的逻辑很简单 - 只需检查开始日期是否报告中其他地方要求的月份。

The logic behind whether the contract should be included in this month's report is simple - simply check if Start date is the month requested elsewhere in the report.

在上面给出的示例中，

• 合同ID为1,3和6新鲜事物


• ID的2和4是续订


• Id 5是新产品，与公司的现有业务

如果有新的产品文件，我已经制定了本月的基本工作方式，因为公司号码不会是唯一的，但产品会。我想让这个更好，因为如果这样做，它会以False的形式返回到产品随后更新的第一次。

I've worked out the basic way of working out for this month if there is a new to product file, because the company number won't be unique, but the product will. I'd like to make this better though, because if this done as is, it will return as "False" for the first occurrence of where the product has subsequently been renewed.

我需要做的是找到一种方法来检查与我们之前的合同是否与上市产品相关，与全新客户签订的合同以及合同与我们之前的合同是不同的产品。实质上，我需要以下答案的逻辑！

What I need to do is find a way of checking if there is a previous contract with us that was with the product listed, contracts with completely new clients, and contracts where there IS a previous contract with us that WASN'T with the same product. In essence, I need the logic for the below answers please!

结果：

Contract ID     Renewal?        New to Product?         New to Business?
    1              No                  No                     Yes
    2             Yes                  No                     No
    3              No                  No                     Yes
    4             Yes                  No                     No
    5              No                 Yes                     No
    6              No                  No                     Yes

到目前为止，我有一些帮助列，如过去12个月内结束，数字的协议。但是，对于三个最终结果的逻辑，我一直在转过头来。

I've got a few helper columns so far, like "ended in last 12 months", and "number of agreements". but I keep getting turned around when it comes to the logic for the three final results!

推荐答案

续订 - 有一个实例相同的产品和公司开始日期较早

Renewal - there is an instance of the same product and company with an earlier start date

=COUNTIFS(A$2:A$7,A2,B$2:B$7,B2,D$2:D$7,"<"&D2)>0

到产品 - 没有相同的产品和公司的开始日期较早，但还有一个其他产品的实例相同的公司
与较早的开始日期

New to product - there is no instance of the same product & company with an earlier start date, but there is an instance of another product & same company with an earlier start date

=AND(COUNTIFS(A$2:A$7,A2,B$2:B$7,B2,D$2:D$7,"<"&D2)=0,COUNTIFS(A$2:A$7,"<>"&A2,B$2:B$7,B2,D$2:D$7,"<"&D2)>0)

没有与之前开始日期相同的公司的实例

New to business - there is no instance of the same company with an earlier start date

=COUNTIFS($B$2:$B$7,B2,$D$2:$D$7,"<"&D2)=0

这篇关于使用数组公式检查类似的合同是否在过去12个月内结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！