題 生成以逗號分隔的單元格內容列表,不包括空格


要連接某些四個單元格,我會使用:

=CONCATENATE(A2,",",C2",",D2,",",F2)

這樣就可以......

  • A2 =“馬修”
  • C2 =“馬克”
  • D2 =“盧克”
  • F2 =“約翰”

會導致 Matthew,Mark,Luke,John

但我們遇到的問題有......

  • A2 =“耶穌”
  • C2 =“”
  • D2 =“瑪麗”
  • F2 =“約瑟夫”

這將導致 Jesus,,Mary,Joseph

這裡,額外的逗號是不受歡迎的。有沒有辦法優雅地處理這個,以便所有非空白單元格都包含在逗號分隔列表中,同時避免在某些單元格為空時添加不必要的逗號?

當然,這可以通過相當數量的嵌套IF完成,但我真的希望盡可能避免這種情況。可以使用本機Excel函數,或者可能是數組公式嗎?或者是否必須訴諸於VB Script這樣的事情?


4
2017-09-19 19:24


起源


這是VBA解決方案,但我也期待著非VBA解決方案。接受的答案盡可能適用於您的示例 看這裡 - nixda


答案:


如你所說,嵌套的IF不是你的一杯茶,但如果沒有@nixda指出的VBA解決方案,我看不到任何其他方法。

對於我提出的解決方案,我假設您的數據如下:

 |    A    |
-+---------+
1| Matthew |
2| Mark    |
3| Luke    |
4| John    |

我有一個解決方案,做了你想要的,但成了 “埋”。我在閱讀了你最近的回答中留下的評論(特別是關於你這個事實的評論)之後就明白了 總是 有第一個細胞填充)。這允許不存在嵌套,因為第一個條件(在下面的推理中)總是假的。

=CONCATENATE(A1;IF(ISBLANK(A2);"";"," & A2);IF(ISBLANK(A3);"";"," & A3);IF(ISBLANK(A4);"";"," & A4))

你可以隨時放棄 CONCATENATE 然後用@Scott的答案指出,用&符號代替公式。在這一點上,它只是一個美容問題。

= A1  IF(ISBLANK(A2);“”;“,”和A2)  IF(ISBLANK(A3);“”;“,”和A3)  IF(ISBLANK(A4);“”;“,”和A4)

第一個值總是寫,所以唯一需要的是檢查是否 活性 單元格為空,如果不是,則在其後面添加逗號。

這樣你就不需要任何輔助單元,它只需要一個函數。

我還寫了一個更精細的版本,因為我假設了一般術語(即可能存在你沒有填充第一個單元格的情況)。它只需要一個2級嵌套,因為最多需要檢查3個條件:

  • 是細胞之前的 活性 細胞空了?
  • 是個 活性 細胞空了?
  • 以上都不是真的嗎? (違約條件)

假設您從頭開始推理,所需的格式如下:

  • 如果是,那麼  在單元格數據之前放置一個逗號。如果不是,繼續。
  • 如果是,那麼  放置任何東西(替代解決方案是放置單元格)。
  • 如果都不為真,請在單元格數據後面添加逗號。

因此,這是我使用的公式。我假設所有數據都在一列中,連續使用更改一些公式和範圍。

=CONCATENATE(A1;IF(ROWS(A1:A1)=COUNTBLANK(A1:A1);A2; IF(ISBLANK(A2);"";"," & A2));IF(ROWS(A1:A2)=COUNTBLANK(A1:A2);A3; IF(ISBLANK(A3);"";"," & A3));IF(ROWS(A1:A3)=COUNTBLANK(A1:A3);A4; IF(ISBLANK(A4);"";"," & A4)))

ROWS 計算任何給定間隔中的行數。如果它等於後面的空白單元格數 活性細胞,然後它意味著背後的所有細胞 活性 單元格為空,因此之前不應放置逗號。

間隔時間 ROWS 和 COUNTBLANK 是細胞 背後 該 活性 細胞。


4
2017-09-19 22:21



Excel的葡萄牙語版本是否使用分號來分隔函數參數?它通常是英文的逗號。 - Iszi
在葡語國家/地區的區域首選項(在Windows中)使Excel使用分號作為參數分隔符(因為我們使用逗號表示小數點)。如果它困擾你我可以編輯我的答案。 - Doktoro Reichard


在Excel 2016中,您可以使用 TEXTJOIN

=TEXTJOIN(delimiter,ignore_blanks?,values_to_be_joined)

因此,在您的情況下,您將使用此公式:

=TEXTJOIN(",",TRUE,A2,C2,D2,F2)

3
2018-04-21 13:07



雖然這可能會回答這個問題,但請考慮添加解釋以使其更易理解。這實際上回答了這個問題。 - Vylix
這是迄今為止最好,最有效的答案 - Vylix


如果您願意使用一些“幫助”單元格作為中間值,您可能會喜歡這樣:

設置單元格 AA2 (要么 Sheet2!A2,或者你想把它放在哪裡)

=IF(A2="", "", A2&",")

(哪裡 X & ÿ 只是一種較短的說法 CONCATENATE(X, ÿ)),並設置單元格 AC2AD2,和 AF2 與此類似。組 AZ2 至

=AA2 & AC2 & AD2 & AF2

然後你的最終結果是

=IF(AZ2="", "", LEFT(AZ2, LEN(AZ2)-1))

說明:

AA - AF 單元格將逗號附加到非空值。所以,用你的例子,

Matthew         Mark            Luke            John

會導致

Matthew,        Mark,           Luke,           John,

Jesus                           Mary            Joseph

會導致

Jesus,                          Mary,           Joseph,

(注意Column中沒有逗號 C)。

AZ2 是上面簡單的連接: Jesus,Mary,Joseph,, 所以我們之後刪除了額外的逗號 Jesus 但最後加了一個。 LEFT(AZ2, LEN(AZ2)-1) 是全部 AZ2 除了最後一個字符。 我們需要測試是否 AZ2 一片空白 如果所有四個輸入單元都為空,則避免出錯。


2
2017-09-20 01:14



關於“使用一些'輔助'細胞”解決方案的好處是通常可以將'輔助細胞'的公式直接寫入目標細胞的公式中。我接受了你的想法並將它們整合到一個公式中。 =CONCATENATE(S2,IF(U2="","",", "&U2),IF(V2="","",", "&V2),IF(W2="","",", "&W2)) 我忘記提到的一件事是我列表中的第一個單元格(本例中為S2)將是 強制性。所以,通過接受第一個細胞會 總是 填好後,我們可以將逗號移到每個後續字符串的前面,並避免在結尾處留下多餘的逗號。 - Iszi
注意:雖然此解決方案仍需要IF語句,但請注意我的原意是避免 嵌套 國際單項體育聯合會。這裡,IF不需要嵌套,並且不需要實際重複 - 每個單元只需要連接一個IF語句。因此,除非有更好的解決方案涉及某種循環或數組,否則這絕對是一個可接受的答案。 - Iszi


我認為你正在尋找一個簡單的解決方案而不是提高你的技能。

如果允許,您可以使用加載項(即您有權安裝加載項)。 ASAP公用事業 是我強烈推薦的非商業用途的免費產品。

以下是您可以使用其中一項功能執行的操作

此實用程序合併選定列中的數據。對於選擇中的每一行,相鄰列中的數據將為   連接到行中的第一個單元格。

您可以指定以下內容:在單元格之間放置的分隔符   值跳過空單元格使用值中的值,公式或格式化值   細胞。

Screenshot


2
2017-10-12 16:02





如果你想要一個通用的解決方案,最好的答案是涉及VBA。

如果您的示例數據完全接近您要解決的完整問題,則只需執行以下操作即可刪除雙逗號:

=SUBSTITUTE(CONCATENATE(A2,",",C2",",D2,",",F2),",,",",")

只有當你只有一個寬值的間隙時,上述內容才有效。你需要嵌套的substitute()公式來處理更大的差距。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,",",C2,",",D2,",",F2),",,,,,,,,,,,,,,,,",","),",,,,,,,,",","),",,,,",","),",,,",","),",,",",")

以上內容最多可減少398個連續逗號。它還會減少399以上的許多值,但399本身和其他幾個將導致多個逗號。


1
2017-09-20 17:55



最後一個公式是一個很大的嵌套...如果以下是正確的,你將8個逗號替換為1,然後將4個替換為1 3 到1,然後是2到1。我理解背後的原因 3 (它是1之後的下一個奇數)除非你要減少那些潛在的398個逗號 SUBSTITUTE 搜索字符串中的所有單獨匹配項。 - Doktoro Reichard
SUBSTITUTE 確實取代所有比賽。 398(24 * 16 + 14)逗號減少到38個逗號,38(4 * 8 + 6)到10,10(2 * 4 + 2)到4,4(1 * 3 + 1)到2和2到1。 - Dane
好惡。嵌套的SUBSTITUTE實際上並不比嵌套的IF好多少。 - Iszi
@iszi,你要處理多少個空白單元格?只有在想要處理很多時才需要嵌套。如果您不想嵌套任何東西,那麼您將需要一個用戶定義的公式。 - Dane
@Dane我不是想避免“嵌套任何東西”,但我確實想避免大量的多層嵌套,特別是在嵌套在同一個函數中時。我正在使用的工作表現在只有4個需要連接的單元格。第一個單元格是強制性的 - 所以如果它是空的,那麼需要先修復一些東西 - 但其他三個單元格可以填充或空白。 - Iszi


我假設您的數據有標題並在A2中開始。

  1. 轉到任何備用列,我將在此示例中使用B.
  2. 在Cell B2中輸入簡單= A2
  3. 在單元格B3中輸入= IF(A3 =“”,B2,CONCATENATE(B2,“,”,A3)
  4. 將B列從B3(不是B2!)複製到包含A列數據的最後一行

Voila,你的逗號分隔列表位於B列的最後一個單元格中:)

我來到這裡尋找解決方案,沒有找​​到,所以提出了上述問題。我的數據有1000行,因此手動添加到連接不是一個選項。


1
2017-10-08 12:54



到目前為止最簡單的解決方案。謝謝 :) - Karlth


只需將Concatenate()與if()結合在一個數組函數中:

=arrayformula(concatenate(if(not(isempty(a2:f2)),a2:f2&", ","")))

此函數將在列表的末尾留下逗號,因此如果您需要避免這種情況,並且您確切知道列表末尾的位置(在您的示例中,列f),請單獨處理該列表:

=arrayformula(concatenate(if(not(isempty(a2:d2)),a2:d2&", ","")))&if(not(isempty(f2)),f2,"")

乾杯!


0
2017-09-30 05:53



第一個公式給出 #名稱? 錯誤 - Prasanna
這似乎是Google Spreadsheets公式。數組公式由Excel中的擊鍵條目指定,而不是由工作表函數指定。 - Excellll