☰

如何識別最多四個姓名的清單中的兩個姓名案例

tag-icon • tag-icon microsoft-excel worksheet-function
如何識別最多四個姓名的清單中的兩個姓名案例

這個問題是之前問題的一個分支：Excel 公式 - 姓氏由多個部分組成的人員的姓名分隔公式：。

情境

我有一份包含 20,000 多個聯絡人的列表，其中只有一個「全名」列。我需要為名字和姓氏建立兩列。然而，這些名稱並不都符合這個簡單的模式。此清單包括以下名稱：

David Batte
Guy-Mael Jacobe de Naurois
Jean-Marc Lioutier
Thibaud Le Seguillon
Mrs. Manuela Junghaehnel
Hussain Mohammed Dipu Kabir

由於前面的問題，我透過剝離適合特定模式的名稱集合來解決這個問題，我可以對其應用適當的解析規則。相對少量的模式將使我能夠自動解析清單的大部分內容，從而使其餘部分成為更易於管理的任務。

這個問題

全名清單的很大一部分只包含名字和姓氏，很容易解析。我這個問題的目標是識別那些有兩個名字的名字，以便將它們與清單的其餘部分分開。

答案1

有多種方法可以識別清單中的名稱僅包含兩個組成部分名稱（例如名字和姓氏）的情況。此條件由分隔組件名稱的空格定義。

方法一

這個問題是前一個問題的分支（Excel 公式 - 姓氏由多個部分組成的人員的姓名分隔公式：），我們已經在評論中討論過識別單一名稱的情況，例如名字或姓氏。如果您已經從清單中刪除了這些名稱，那麼每個條目都保證至少有兩個名稱，您可以在此處使用類似的技術並在此基礎上進行建置。

這種方法尋找一個空間，然後從那裡開始尋找另一個空間。如果它可以找到第二個空格，則該條目具有兩個以上的組件名稱。如果條目位於 A1 中，則基本公式為：

=FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)

其結果將是一個數字（下一個空格的位置）或#VALUE！如果沒有的話就會出錯。價值！錯誤很容易發現並識別兩個名稱的條目。請注意，它還將識別單一名稱條目，這就是為什麼此方法僅在您已刪除這些條目時才有效。

您可以透過測試錯誤條件的結果，然後針對兩個名字的情況顯示一條文字訊息或顯示空白，從而使此操作更具視覺吸引力。將上面的公式包含在 IF 測試中將得到以下公式：

=IF(ISERROR(FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)),"Just 2","")

方法二

另一種方法是計算空格數。實際上，您可以使用此方法來尋找任何特定數量的空格，因此只需更改目標計數即可讓您找到單一名稱條目。這種方法透過消除所有空格並查看字串縮小了多少個字元來計算空格數。長度比較產生空格數。您可以用 IF 測試對其進行包裝，以對結果數字執行您想要的操作。標記兩個名稱條目（即單一空格）的公式如下所示：

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,"Just 2","")

答案2

我將留下一個更簡單的方法可能不是最佳的20000 行。

  • 使用資料 -> 文字到列按空格分割名稱
  • 名稱將分為四列 A、B、C 和 D
  • 點擊過濾器並blanks在​​ C 列中過濾
  • 您將只有 A 和 B 中顯示的兩個單字名稱。
  • 您可以使用以下方式加入名稱=TEXTJOIN(" ",TRUE,A1:B1)

答案3

我想建議兩種可能的方法，第一種是非VBA，另一種是VBA。

方法一：

在此輸入影像描述

怎麼運作的：

  • 在儲存格中輸入此公式H2以建立輔助值（識別名稱的各個部分）並填寫。

=LEN(G2)-LEN(SUBSTITUTE(G2," ",""))

  • 2在單元格中寫入標準H1。

  • 在 Cell 中輸入此數組公式I2，完成Ctrl+Shift+Enter並將其填寫下來。

    {=IFERROR(INDEX($G$2:$G$14,SMALL(IF(H$1=$H$2:$H$14,ROW($G$2:$G$14)- MIN(ROW($G$2:$G$14))+1,""), ROW()-1)),"")}

注意

  • 將儲存格中的條件變更H1為1或 ，3您可以獲得兩個或三個以上零件名稱的清單。

在此輸入影像描述

在此輸入影像描述

方法二：

由於您要處理超過 20000 行，因此 VBA（巨集）將是完成這項工作的最快方法。

怎麼運作的：

  • 右鍵點擊工作表標籤和彈出式選單中的點擊檢視程式碼取得 VB 編輯器。

  • 複製貼上將此程式碼作為標準模組，最後跑步宏。

    Sub DeleteMorethan2()

Dim r As Integer, c As Integer, space_count As Integer

For r = 2 To 15
space_count = 0

For c = 1 To Len(Range("A" & r))

   If Mid(Range("A" & r), c, 1) = " " Then space_count = space_count + 1
   If space_count > 1 Then Range("A" & r).ClearContents

  Next
Next

End Sub

你得到這個：

在此輸入影像描述

注意

  • 此程式碼正在處理Rows 2 to 15，透過編輯此部分For r = 2 To 15，您可以包含更多行。
  • 使用的 Column 是A，也是可編輯的。

此外，我想建議一個陣列公式透過刪除空白行來重新建立姓名清單。

在此輸入影像描述

  • 在 Cell 中輸入此數組公式B2，完成Ctrl+Shift+Enter並填寫它。

    {=IFERROR(INDEX(A$2:A$14,SMALL(IF(A$2:A$14<>"",ROW(A$2:A$14)-ROW(A$2)+1),ROWS(B$2:B2))),"")}

根據需要調整公式中的儲存格引用。

相關內容