☰

尋找特定行中第二次或第三次出現的值 - excel

tag-icon • tag-icon microsoft-excel worksheet-function
尋找特定行中第二次或第三次出現的值 - excel

我試圖取得連續出現多次的日期的列位置。

範例資料：
資料範例.

以下公式正確標識了大多數列位置，但如果存在重複項，則始終傳回第一次出現的位置。

公式 ：
公式

先感謝您。

答案1

以下公式將n-th在一組符合和不匹配的儲存格中選擇匹配項。它可以適應各種來源資料佈局，稍後會詳細介紹。它是簡單的，因為它根本不提供任何錯誤檢查，甚至是您的IF(FL2<>0部分，因為它具有廣泛的用途，但您可以輕鬆添加此類檢查以適合您的具體情況：

=FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN("¢",FALSE,FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(AW2:CW2),4),ROW(),""),AW2:CW2=B2),ROW(),""),"¢","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner["&C2&"]")

它的設定假設它將在一行的 A 列中與 B 列中的值和n-thC 列中的值相符。

LET()用於將所有變數放在一個易於編輯的位置的版本是：

=LET(
 RangeToExamine, AW2:CW2,  Delimiter, "¢",  ItemToMatch, B2,  InstanceToMatch,  C2,
 FILTERXML("<Outer><Inner>"
 &SUBSTITUTE(TEXTJOIN(Delimiter,FALSE,
 FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(RangeToExamine),4),ROW(),""),
 RangeToExamine=ItemToMatch),
 ROW(),""),  Delimiter,"</Inner><Inner>") & "</Inner></Outer>",
 "/Outer/Inner["&InstanceToMatch&"]"))

這就是LET()為了：使公式更容易編輯。

那麼，它是做什麼的。首先，雖然您的佈局可能是單行，但我不確定，所以我將其佈局放置在任何行上。這是透過在函數ROW()內部使用該函數ADDRESS()以及稍後在刪除行號的函數中使用該函數來解決的SUBSTITUTE()（因此您只有列，這就是您想要的結果）。如果需要，您可以在兩個位置放置數字“1”，以確保不會發生行方向活動。LET()如果您希望能夠在各處進行修改，您甚至可以在 中新增一個子句。我沒有在這裡，因為它會將LET()線滾動到這裡的兩行，這會讓事情變得混亂。順便說一句，這就是將 NUMBER 列轉換為 LETTER 列的方法，而不是所有那些有趣的公式MOD()（甚至更糟）。不需要 UDF。

因此，ADDRESS()使用Spill功能來建立一個由正在檢查的範圍內的所有單元格位址組成的陣列。FILTER()然後檢查匹配的目標範圍並列出相應的單元格位址。 （再次強調，不要設定錯誤！）SUBSTITUTE()然後刪除行號（如果進行了更改，則刪除常數），這樣 Excel 現在就有一個僅包含匹配項所在列的列字母標籤的數組。

我應該快速提及的是，在其他情況下，我在這裡使用的分隔符號不一定總是好的分隔符號。但由於它沒有連接到底層數據，而是連接到地址數據，因此它只需要是在返回的地址中從未使用過的東西，ADDRESS()因此它不會在這裡失敗。不過，在其他地方使用「很少使用的字元」方法，您可能需要選擇在字元對應表中發現的奇怪內容。

接下來，TEXTJOIN()將地址數組轉換為字串，並SUBSTITUTE()用該</Inner><Inner>字串替換該分隔符，同時在前後添加字串，以將迄今為止的結果轉換為可接受的 XML。幾乎可以使用任何東西來建構 XML，只要它至少有一個較高層級的包裝以及至少一個最低層級的包裝TEXTJOIN()字串的每個部分。我喜歡將其用於<Outer>包含所有內容的更高級別以及<Inner>工作級別。

我第一次在某個地方看到這個，解釋的方式很糟糕，但去了Chandoo.org看看他是否對此有建議，因為他的解釋（幾乎）總是非常清晰和有用。它非常方便，所以我必須給予信任和感謝！

最後，FILTERXML()做最後一件事。它允許您選擇要傳回的符合實例。在 Chandoo 的提示中，他以這種方式分解句子，但正如您所看到的，它可以分解您獲得的任何內容或變成字串，包括柱狀資料。在這種情況下，如果您想要第三個實例，您可以添加[3]一些東西。

然而，Excel 在這方面做得更好。首先，您可以使用數字實例，例如第三個實例的 [3]，但您也可以使用它[last()]來查找最後一個實例，而不必也查找其基數。但是，令人驚訝的是，該函數真正閃耀的地方（因為它似乎不是針對這種事情）是您可以「建立」該子句，或者換句話說，您可以建立一個包含變數資料作為一部分的字符串，然後它將使您的公式具有響應能力，而不必在公式中輸入並根據需要進行更改。這意味著您的用戶可以使用單元格中的條目而不是編輯公式來獲得該功能。或者，如果需要，您可以使其反映其他公式的結果。

最後一部分發生在公式格式化版本的最後一行。您給出「XML 路徑」（本例為「/Outer/Inner」），後面跟著一組方括號（「[ ]」），其中包含實例編號或表達式。

在一般使用中，我建議檢查錯誤以查找：

  1. 您要配對的項目不在正在檢查的範圍內
  2. 您想要的實例數量高於匹配總數：您想要#6，但只有四個存在...
  3. 類似地，如果你想從最後一個實例中找到第 n 個實例，它也存在：如果你想從「last()」中傳回 6 個實例，那麼至少還有其他 6 個實例...

如果資料比字母和數字更加多樣化，請注意選擇的分隔符號。

如果您想要儲存格位址而不僅僅是列，請刪除將SUBSTITUTE()​​行從完整位址中剝離出來的部分。

好吧，在一定程度上抱歉：它是一個通用解決方案，而不是完全適合您的帖子，但我不確定您的確切用途，因此這也可能會有所幫助，並且任何編輯都很容易看到。這裡的經驗表明，帖子經常遺漏有用的信息，然後發文者必須至少再問一個問題才能得到他需要的答案。試圖縮短您完成該過程的任何需要。

答案2

您在尋找 COUNTIF 公式嗎？ =IF(A2<>0,COUNTIF($A$2:A2,A2),0)

在此輸入影像描述

相關內容