☰

Excel - 一次尋找多個不同欄位中的多個數據

tag-icon • tag-icon microsoft-excel worksheet-function
Excel - 一次尋找多個不同欄位中的多個數據

我目前手頭上有一個電子表格，其中列出了合約。我需要知道合約是否是續約、產品新合約或公司新合約。我目前正在努力解決背後的邏輯，希望得到任何幫助！

由於報告軟體不會詢問各種類型的合同，因此我得到的資訊比我想要的要少。

除了起始日期和截止日期之外，每個客戶都有一個唯一的標識符，每個產品都有一個指定的名稱，每個合約都有一個唯一的編號以及當前的「狀態」。對於多個不同版本的合約，我還新增了「最新版本」一欄

樣本：

CompanyNumber   Product     Contract Number     Version     Status   Latest version?   Valid From      Valid To
12345              A            9876A-BA           1        Active        Yes             1/1/14       31/12/15

我需要做的是為New Customer、New to product、 And產生額外的「是/否」列Renewal。

這樣我就可以更輕鬆地為這三列建立每月報告，只需在資料透視表中進行篩選即可。

我已經出於其他原因生成了幾個額外的列，如下所示

Start date in this month? - End Date In This Month - Expires in next three months?  - More than one version? - Existing Client > 365 days - Latest version?

我目前續約的邏輯是先檢查具有相同公司編號的另一個條目是否有去年的結束日期，然後檢查合約的最新版本是否從本月開始並且是最新版本，就像這樣

If
   unique company number = no, find if any duplicate result ended in the last year
                              AND
   Contract Starts this month AND this entry is the latest version 
= True, "YES"
  False = "NO"

但我不知道如何做到這一點！任何幫助是極大的讚賞。

非常感謝

編輯。

經過一天的努力，我已經走到這一步了。我認為它有效，但我還沒有100%

我開始嘗試將其作為if 函數，使用嵌套的and、countif（唯一公司編號）>1、countif(product)>1、當前和最新版本函數，如下所示：=IF(AND(COUNTIF(B: B,B10) )>1,COUNTIF(C:C,C10)>1,(S10=「是」),AA10=1),1,0)

答案為 1 或 0，因此我可以輕鬆地在資料透視表中使用結果。

如果有人想到更好的答案請告訴我:) -

第二次編輯

那個不起作用，所以我做了更多研究，發現了這個公式，但它不太起作用。

我決定建立一個「幫助程式」列，以便我們可以計算每個公司編號的每個產品的實例，但它計算的是公司編號的每個實例，而不僅僅是交叉引用產品的實例。

=SUM(IF(C:C=C28,IF(B:B=B28,1,0),0))

C:C我儲存產品編號的列在哪裡，B是唯一的公司參考。

答案1

很多時候我會使用 concatenate 來組合資料元素，然後使用資料透視表來計算組合資料元素出現的次數，或者您可以使用 count if。因此，例如，如果您試圖找出公司（公司編號）是否是產品的新產品，那麼您可以使用公式建立輔助列，=Concatenate(Company Number, Product)在您的範例中該公式將為您提供值12345A。然後，您可以使用countif資料透視表來查看 1) 該公司是否是該產品線的新公司 2) 開始日期（有效日期）等。

相關內容